CPSC 310/603: Review for Exam 1
Fall 2005

Review the following:

• Lectures through Sep 30 (ends with SQL).

• 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
  • Ch 5 (relational algebra) - you can skip sec 5.2.10 and sec 5.5.
  • Ch 6 (SQL) - you can skip sec 6.6.5 and sec 6.6.6
  The chapter summaries indicate the most important concepts.

• Homeworks 1-5 and their solutions.

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 comine 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)
  • definition of fourth normal form (4NF), and its advantages and disadvantages relative to BCNF and 3NF
  • how to convert a relation into 4NF in principle (but don't worry about how to determine which multivalued dependencies hold in the decomposed relations)

• 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

• SQL: You should know how to interpret queries that are given to you and how to write some queries. However, small syntax errors will not be counted off.
  • nature of SQL (specify what to do, not how to do it, leave how to the system)
  • General format for a query is:

    SELECT [list of attributes]
    FROM [list of relations]
    WHERE [condition]
    GROUP BY [grouping attributes]
    HAVING [condition about the groups to be included]
    ORDER BY [list of attributes]
    
    Only SELECT and FROM are mandatory; if HAVING is present, so must be GROUP BY.
    
    Some other keywords:
    * - all atributes
    AS - rename an attribute in SELECT; alias for a relation in FROM (optional)
    LIKE, % and _ for pattern matching in strings
    DESC, ASC - to sort in descending or ascending order
    
    Combining relations:
    [relation] UNION [relation]
    [relation] INTERSECT [relation]
    [relation] EXCEPT [relation]
    [relation] CROSS JOIN [relation]
    [relation] JOIN [relation] ON [condition]
    [relation] NATURAL JOIN [relation]
    [relation] NATURAL FULL/LEFT/RIGHT OUTER JOIN [relation]
    [relation] FULL/LEFT/RIGHT OUTER JOIN [relation] ON [condition]
    
    Conditions involving relations:
    EXISTS [relation]
    IN [relation]
    > ALL [relation] (and other comparison operators)
    > ANY [relation] (and other comparison operators)
    Also can use NOT in these
    
    DISTINCT - after SELECT to eliminate duplicates
    ALL - after UNION, INTERSECT, EXCEPT to keep duplicates
    
    Aggregation:
    SUM, AVG, MIN, MAX, COUNT - apply to an attribute in SELECT
    COUNT can also apply to *
    can put DISTINCT before attribute name
    

  • know the operational semantics, in terms of tuple variables ranging over tuples in particular relations
  • uses of NULL and the truth value UNKNOWN
  • database modifications to existing relations: INSERT, DELETE, UPDATE tuples
  • defining and modifying the schema: CREATE a table, DROP a table, ALTER a table by ADD-ing or DROP-ing attributes; use of DEFAULT
  • definition of view, table, and relation
  • CREATE VIEW, querying a view
  • issues with modifying a view