CPSC 310/603: Review for Exam 1
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.
  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