This course provides an introduction to the fundamental concepts of database management, including aspects of data models, database languages, and database design. At the end of this course, a student will be able to understand and apply the fundamental concepts required for the use and design of database management systems.
Lectures: Mon, 10:00-13:00PM and Fri, 10:00-13:00 online (Zoom link available in Moodle)
Office Hours: Mon, 13:00 online (Zoom link available in Moodle)
The course will rely mainly on the following textbooks.
Introduction, course administrivia, early database management systems, relational database systems, overview of a DBMS, outline of database system studies.
Readings:
Overview of data models, basics of the relational model (relation, schema, attributes, tuples), value-based references, the null value, integrity constraints, keys, primary keys, foreign keys, referential constraints.
Readings:
Algebraic query language, what is an algebra, overview of relational algebra (set operations on relations, projection, selection, cartesian product, natural joins, theta-joins, combining operations to form queries, naming and renaming, relationships among operations), constraints on relations (referential itegrity constraints, key constraints, additional constraints), relational operations on bags (union, intersection, and difference of bags, projection, selection, product, joins), extended operators of relational algebra (duplicate elimination, aggregation operators, grouping, the grouping operator, extending the projection operator, the sorting operator, outerjoins).
Readings:
Simple queries in SQL (projection, selection, comparison of strings, pattern matching, dates and times, NULL values and comparisons involving NULL, ordering output), queries involving more than one relation (products, joins, disambiguating attributes, tuple variables, interpreting multirelation queries, union, intersection, difference), subqueries (subqueries that produce scalar values, conditions involving relations and tuples, correlated subqueries, subqueries in FROM clauses, join expressions, natural joins, outerjoins), full-relation operations (eliminating duplicates, duplicates in unions/intersections/differences, grouping and aggregation, aggregation operators, grouping and aggregation with NULLs, HAVING clause).
Readings:
Database modifications (insert, delete, update), keys and foreign keys, constraints on attributes and tuples, modification of constraints, assertions, triggers, virtual views, modifying views, materialized views, indexes, database tuning.
Readings:
The SQL environment, the SQL/host-language interface, stored procedures, Using a call-level interface, JDBC.
Readings:
SQL injection, first-order SQL injection, second-order SQL injection, SQL injection solutions, mitigating the impact of SQL injection attacks.
Readings:
The Entity/Relationship (ER) model, design principles, constraints in the E/R model, weak entity sets, from E/R diagrams to relational designs.
Readings:
Functional dependencies, schema decomposition, normal forms.
Readings:
Transactions, ACID properties, COMMIT, ROLLBACK, isolation levels.
Readings:
Structured, unstructured, semi-structured data, What is NOSQL, NOSQL taxonomy.
Readings:
Wrap-up of the class.