This is intended to give you a sense of what I think is important from the course so far, and what I will be thinking of when creating the exam.

Here are some disclaimers. This is not a contract. I may have inadvertently left something off this list that ends up in an exam question. I make no guarantees that the exam will be 100% limited to items listed below. Moreover, I will not be able to test all of this material given the time limitations of the exam. I will have to pick and choose some subset of it.

You are permitted one 8.5 x 11 sheet of paper with notes (both sides) for use as a reference during the exam.

Here are the specifics: Students should be able to...

Relational model: Be able to define and/or identify aspects of the relational model such as relations, candidate keys, primary keys, superkeys, foreign keys.

Relational algebra: Be able to write queries as specified in relational algebra using selection, projection, set operators, Cartesian product, join, rename, assignment, and aggregation. Be able to interpret relational algebra queries provided.

SQL: Be able to produce or interpret the results of SELECT / FROM / WHERE / GROUP BY / ORDER BY / HAVING / INNER/OUTER JOIN queries. Be able to predict the output of queries where knowledge of the Cartesian-product / selection / projection definition is likely to be useful. Be able to create and use tables with primary keys, foreign keys, integers, reals, and text. Be able to interpret and construct queries with subqueries. Use and interpret NULL correctly.

Entity-Relationship model: Be able to construct an E-R diagram for a specific scenario provided. Be able to appropriately use each aspect of the E-R model (entity sets, relationship sets, keys, key constraints, participation constraints, weak entities, aggregation, ternary relationships, inheritance, 1-to-many vs. many-to-many vs. 1-to-1 relationships). Be able to convert an E-R diagram to the relational model. Consider tradeoffs in different relational implementations of a particular E-R design. Because of the extreme variability in standards for how these are drawn, I will not ask you to produce E-R shapes completely from scratch. However, I could give you a key mapping shapes to concepts and ask you draw a diagram, or I could answer you to interpret a diagram using the same notation that the textbook uses.

Database design theory: Be able to identify the tradeoffs in normalized and denormalized relation schemas. Be able to efine functional dependencies, and show understanding of what they mean in specific relation instances. Be able to define BCNF and 3NF, and be able to interpret the various criteria within those definitions. Be able to define closure of a set of dependencies. Be able to determine whether a relation is in BCNF and/or 3NF, and alernatively be able to decompose a relation into BCNF. Be able to define a lossless-join decomposition vs. a dependency preserving decomposition, explain the desirability of each, and how these two forms of decomposition relate to BCNF and 3NF.

Disk management: Demonstrate understanding of how disks store and evaluate tradeoffs in cost, speed, and capacity.

Page organization: Be able to describe a variety of page organizations with fixed and variable length records, and be able to explain tradeoffs by using each. Be able to describe how free space is handled on an individual page, again being able to explain tradeoffs.