## CPSC 310/603: Review for Exam 1 Fall 2007

You may bring one 8.5-by-11 inch sheet of paper with your own notes on it to the exam. You may write on both sides.

Review the following:

• Lectures through Sep 18 (ends with relational algebra).

• 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.
The chapter summaries indicate the most important concepts.

• 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