Fall 2007

Review the following:

- Lectures through Sep 18 (ends with relational algebra).
- Readings from textbook:
- Ch 1 (introduction)
- Ch 2 (E/R data model)
- Ch 3 (relational model) - you can skip sec 3.5.4, sec 3.6.5, and sec 3.7.3-3.7.5
- Ch 5 (relational algebra) - you can skip sec 5.2.10 and sec 5.5.

- Homeworks 1-3 and their solutions (gone over in class).
- "Sample" exam from previous semester, available here.

The format of the exam will be some short answers and some "work-out" problems similar to the homeworks. Here are some suggestions for things to know:

- E/R diagrams:
- definition, notation, and use of entity set, attribute, relationship (many-many, many-one, etc.), roles, keys, weak entity sets
- design guidelines
- how to create an E/R diagram for a simple application

- relational data model:
- definitions of relation, schema, database
- why relational model is reasonable/important
- how to convert an E/R diagram to relations using the algorithm: first convert entity sets and relationships to relations according to the rules, then combine relations according to the rules
- the 3 ways to convert subclasses and their pros and cons
- definition of functional dependency (FD), key, superkey
- how to compute the closure of a set of attributes, given a set of FDs
- how to calculate all additional FDs that hold when you are given an initial set of FDs (apply the closure algorithm to all subsets of attributes)
- how to find all FDs that hold in a schema that is the result of a decomposition (calculate all FDs that hold in the original schema and then take the FDs that only involve attributes of the new schema)
- definition of various anomalies that can occur with a poorly defined relation; normalization is a process of changing the relation to avoid these anomalies
- definition of Boyce-Codd Normal Form (BCNF)
- how to convert a relation into BCNF given a set of FDs
- definition of third normal form (3NF), and its advantage (and disadvantage) over BCNF
- definition of a prime attribute
- how to convert a relation into 3NF
- definition of a multivalued dependency (MVD); given some tuples in a database and an MVD, determine which other tuples must also be in the database

- Relational algebra:
- definitions of the operators: union, intersection, difference, selection, projection, product, theta-join, natural join, renaming; duplicate elimination, sorting, extended projection, grouping and aggregation, outerjoins (dangling tuples).
- combining operators and the 3 notations (sequences of assignments, expressions with several operators, expression trees)
- sets vs. bags (multisets) and the pros and cons of each