The test covers the first part of the course, The Game (relational) & I. Design.

Both topics covered in the readings in the textbook and in class and the lecture slides are fair game, but with an emphasis on the class lectures and slides. (The lectures and the readings do cover the very same materials.) The readings covered are indicated in the topic list below.

0. Basics and Overview [Ch 1]
   • evolution of database systems
   • what functions database systems provide
1. 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
2. conceptual modelling [Ch 4: §1–6 (& Ch 7: §1.1 & 1.2)]
   1. entity/relationship model
      • entity sets, relationship sets, & attributes
      • multiway relationsips
      • multiplicity in relationships
      • “recursive” relationships and roles
      • subclasses (“isa”)
   2. design principles
      1. fidelity / faithfulness
      2. brevity: avoiding redundancy
      3. simplicity
      4. naturalness
   3. constraints in the E/R model
      • keys!
      • referential integrity
   4. weak entity sets
   5. from E/R diagrams to relational designs
   6. converting subclass structures to relations
      • from entity sets to relations (tables)
      • from E/R relationships to relations
      • combining relations
      • handling weak entity sets
      • 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 (& Ch 2: §4.8)]
   1. keys & functional dependencies [Ch 3: §1–2]
      • keys, superkeys, & functional dependencies (FDs)
      • reasoning about FDs
   2. the normal forms [design theory slidedeck] [Ch 3: §3 & §5]
      • anomalies
      • what each normal form protects against
      • how to test a relation for a normal form
   3. decomposition [Ch 3: §3 & §4 & Ch 2: §4.8 (natural join)]
   4. synthesis [Ch 3: §5]

Go through the reading and the examples in the textbook. Consider the problems at the end of each chapter and work some of them. Go through the exercises I have provided.

Exercises

Some exercises outside of the lecture notes that we have done in class: exercises (PDF); and exercises w/ answers (PDF).

Caution: Note the E/R is Wisconsin style in these, not Stanford style!

Past Tests

• test #1 from winter 2019: test (PDF), test w/ answers (PDF)
• midterm from winter 2018: midterm (PDF), midterm w/ answers (PDF)
  (Note that there was one midterm for this class, so it covered more material.)
• test #1 from fall 2016: test (PDF), test w/ answers (PDF)

• test #1 from fall 2015: test (PDF), test w/ answers (PDF).
• test #1 from winter 2014: test (PDF), test w/ answers (PDF).
• test #1 from winter 2013: test (PDF), test w/ answers (PDF)
• test #1 from winter 2011: test (PDF), test w/ answers (PDF)
• test #1 from winter 2009: test (PDF), test w/ answers (PDF)
• midterm test from winter 2005: test (PDF), test w/ answers (PDF)
  (Note that there was one midterm for this class, so it covered more material.)

The test will be closed-note, closed-book. You may bring a calculator, but I cannot imagine that you would need one.

There will be space on the text packet for writing answers. I will bring extra paper, in case anyone needs it, to attach.

The test will be for the class lecture time, so 75 minutes.