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...

Disk management: Demonstrate understanding of how disks store and
transfer pages of data by being able to assess and make cost estimates
for algorithms based on on disk and memory usage.

Buffer pool manager: Be able to explain the need for a buffer pool
manager vs. what OS does with specific situations. Be able to describe
if a buffer pool helps or doesn't help in specific situations. Be able
to describe various replacement algorithms (LRU, MRU, clock) and
predict which pages would be replaced under particular access
patterns. Be able to describe a pattern that causes bad things for an
arbitrary replacement algorithm. Be able to explain and/or utilize why
and how dirty bit and pinning is used, and how pinning differs from
locking.

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 or across an entire heap file, again being able
to explain tradeoffs. For any of the page structures we've discussed,
be able to discuss reason for individual items on the page.

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, ISA, 1-to-many vs. many-to-many vs. 1-to-1
relationships). Be able to convert an E-R diagram to the relational
model.

Relational model: Be able to define the relational model and explain
its strengths in contrast with other models. Be able to define and/or
identify aspects of the relational model such as relations, candidate
keys, primary keys, superkeys, foreign keys). Be able to show awareness of
what's a part of the relational model, and what's not.

Relational algebra: Be able to write queries as specified in
relational algebra using selection, projetion, set operators, cross
product, join, and relational division). Demonstrate understanding of
relational division, both in what it does and how it is
constructed. Be able to interpret relational algebra queries provided.

Relational calculus: Be able to interpret a straightforward tuple relational
calculus query. Be able define and show an example of an unsafe
query. Be able to define and show an example of transitive closure.

SQL: Be able to produce or interpret the results of SELECT/FROM/WHERE
SQL queries. Be able to predict the output of queries where knowledge
of the cross-product / selection / projection definition is likely to
be useful.

Indexing: Be able to describe alternative file organizations to the
heap file, and quantitatively describe advantages and
disadvantages. Be able to define and quantitatively assess merit of
indexing strategies such as primary vs. secondary indexes, clustered
indexes, dense vs. sparse indexes, built-in vs. external vs. external
with list indexes (in book as Alternatives 1, 2, and 3).

-------------------------
Not going to test:

Creating tables in SQL. It's too easy (just look up the syntax).

SQL that uses subqueries, ANY/ALL, GROUP BY, ORDER BY, etc., since you
haven't done the SQL assignment yet. But I would happily ask a test
question on the hardest SQL query I could come up with that uses a
basic SELECT/FROM/WHERE framework (see above).