time	9:00am–12:00pm
date	Wednesday 14 December
place	TC AVIVA

The exam is for three hours. It is closed notes and closed book.

The final exam is cumulative, so it covers all the topics we have covered in the course. These are as follows.

0. The Game: Basics and Overview
   1. What are database systems? [Ch 1]
      1. evolution of database systems
      2. what functions database systems provide
   2. Data Models (& Schemas) [Ch 2: §1–3]
      1. overview of data models
         1. what is a data model?
         2. the relational model, in brief
         3. the semi-structured model
      2. the relational model
         1. basics
         2. data independence
      3. defining relational schema in SQL
1. Design
   1. conceptual modelling [Ch 4: §1–4]
      1. entity/relationship model
         1. entity sets, relationship sets, & attributes
         2. multiway relationsips
         3. multiplicity in relationships
         4. “recursive” relationships and roles
         5. subclasses (“isa”)
      2. design principles
         1. fidelity / faithfulness
         2. brevity: avoiding redundancy
         3. simplicity
         4. naturalness
      3. constraints in the E/R model
         1. keys!
         2. referential integrity
      4. weak entity sets
   2. converting from E/R diagrams to relational schema [Ch 4: §5 & §6] & [Ch 7: §1.1 & 1.2]
      1. from entity sets to relations (tables)
      2. from E/R relationships to relations
      3. combining relations
      4. handling weak entity sets
      5. converting subclass structures to relations
      6. using foreign key contraints to enforce referential integrity [Ch 7: §1.1 & 1.2]
   3. design theory [Ch 3: §1, §2, §3.1, & §3.3]
      1. keys & functional dependencies
         1. keys, superkeys, & functional dependencies (FDs)
         2. reasoning about FDs
      2. the normal forms [design theory slidedeck]
         1. anomalies
         2. what each normal form protects against
         3. how to test a relation for a normal form
         4. decomposition [Ch 3: §3.2, §3.4 & §4]
            • lossless join decomposition
            • dependency preservation
         5. synthesis [Ch 3: §5]
2. Queries
   1. relational algebra [Ch 5: §1–3]
      1. relational algebra operators
      2. queries
   2. SQL [Ch 6: §1–5]
      1. syntax & conceptual model
      2. aggregate operators
      3. NULL values
      4. queries, queries, queries!
      5. data modification operations
   3. constraints & triggers [Ch 7]
      1. keys & foreign keys (review)
      2. check constraints: constraints on attributes and tuples
      3. handling constraints
      4. triggers
3. Applications
   1. application programming (& database connections) [Ch 9: §3,5–7]
      1. programming language / SQL interfaces
         • SQL/PL
         • embedded SQL (e.g., sqlj)
         • CLI (e.g., jdbc)
      2. cursors
   2. transaction management (& system overview) [Ch 6: §6] & [Ch 8: §1–3]
      1. database system overview
      2. transaction management & properties
         1. serializability
         2. properties
            • atomicity
            • consistency
            • isolation
            • durability
      3. transactions, views, & indexes

Note that the textbook readings associated with each topic is shown above. These readings plus the lecture slides constitute the materials. For the exam, generally only things that we covered both in the lectures and projects and in the readings will be tested. (I do reserve the right to test minor things from the readings.) Anything additional beyond this will not be.

For the Applications materials, we went into much less depth than the other sections. For these materials, you are expected to be familiar with the basic concepts; however, I do not expect the same fluency as with SQL and E/R.

We did discuss XML and XQuery and we had a lecture on data-warehousing in class. These topics are not covered by the exam. We also had the topic of database authorization listed in the syllabus; we did not cover it, however, so it is not covered by the exam.

These are the handwritten review-session notes in PDF.

There will likely be seven main questions of the style that you have seen on the tests and in the exercises. The likely composition is that one will be drawn from the General topic, two each from Design, Queries, and Applications.

The types of questions will be a mix, like on the tests: exercises, analysis, short answer, multiple choice, and true/false.

Remember that the exam is cumulative and covers all the topics of the course. So review design (ER), relational algebra, and such. The most important topic, arguably, of the course is SQL. Know your SQL.

The exam is closed-book and closed-note.

The final exam is worth 35% of your total grade.