The lecture slides from each week will be posted here, accompanied by reading suggestions, and problem-solving exercises. Suggested order of learning: come to the lecture, read suggested book chapters, do homework exercises, come to the next lecture for questions and clarifications.
Database Management Systems (DBMS). DBMS evolution. DBMS architecture. Database vs. DBMS. Slides. Readings: Chapter 1.
Entity-Relationship (E/R) modeling. Multiway relationships. Slides. Readings: Chapter 4: section 4.1..
E/R refinements: keys, sub-classes and week entity sets. Slides. Multiplicity of multiway relationships. Slides. Readings: Chapter 4: sections 4.2. - 4.4.
Exercises: E/R diagrams.
From E/R Diagrams to relational schema. Converting subclass structures to relations. Slides. Summary: Slides. Readings: Chapter 4: sections 4.5, 4.6.
Exercises: Reading E/R and handling subclasses.
Physical implementation of relations. Slides. Readings: Chapter 2: sections 2.1 - 2.3.
Manipulating data: insert, update, delete. Slides.
Basic table constraints. Referential constraints. Value constraints on attributes and tuples. Modification of constraints. Slides. Readings: Chapter 7: sections 7.1.-7.4.
Exercises: Constraints.
Java Database Connectivity (JDBC) tutorial. Slides. SQLite driver. PostgreSQL driver. Pizza DB SQL scripts: for SQLite and for PostgreSQL. Demo: JAR with source code. Readings: Chapter 9: section 9.6.
Algebra of relations. Core and derived relational operators. Relational algebra on Bags. Slides. General approach to writing RA queries: Summary.
Exercises: RA queries and solutions.
Readings: Chapter 2: section 2.4, Chapter 5: sections 5.1 - 5.2.
Structured Query Language SQL. Select-From-Where queries. Slides. Grouping and aggregation. Slides. Set Operations. Slides. Database modifications with SQL. Slides. SQL: summary. Slides.
Exercises: SQL queries.
Readings: Chapter 6: sections 6.1 - 6.5.
Views. Slides. Dataset used in examples: dataset. Triggers. Slides. Triggers in PostgreSQL: examples.
Exercises: SQL queries with views.
Readings: Chapter 8: sections 8.1 - 8.2. Chapter 7: sections 7.4 - 7.5.
Concurrency in databases. Transactions. Anomalies related to concurrent execution of transactions. SQL support for transactions. Isolation levels. Slides.
Readings: Chapter 6: section 6.6.
Intro to indexes. Index selection. Slides.
Readings: Chapter 8: sections 8.3 - 8.4.
Introduction to normal forms. Slides.
Exercises: Normalization.
Design theory for relational databases. Boyce-Codd Normal Form (BCNF). Slides.
Properties of decompositions: lossless join, dependency preservation. Slides.
Exercises: Chase test for lossless decomposition.
Multi-valued dependencies. 4NF. Slides.
Readings: Chapter 3: all sections.
Semantic modeling. Data as graphs. RDF. NoSQL database systems classification. Slides.
Readings: article
NoSQL databases. Slides. Based on NoSQL distilled.