CPSC 343 Review Information — Exam 3

Exam 3 is a written in-class (one hour) exam covering logical and physical database design — ER/EER-to-relational mapping, functional dependencies and normalization, and implementing a schema with SQL DDL — and data modification with SQL. Expect two kinds of questions:

• short answer questions about concepts — definitions, how a construct or rule behaves, and when to use one approach or mechanism over another
• applying the techniques, as in the homeworks and project — mapping an ER/EER diagram to a relational schema, finding candidate keys and normal forms and decomposing a relation, choosing data types and constraints for a schema and reading CREATE TABLE statements, writing data modification statements, and predicting the effect of a modification on given data

Short answers should be short — generally 1-3 sentences, or 1-2 sentences per part for a question with several parts. A list rather than complete sentences is fine where appropriate. Time is limited, so don't write more than necessary!

The topics covered on the exam are listed below. The items under "Self check" are study goals — examples of the kinds of things you should be able to do rather than actual exam questions or a complete list of what the exam might ask. As a starting point for studying, try to explain each topic without notes (including an example of your own where appropriate) and then answer the associated self check items. As a pacing guide, you should be able to start writing answers for definition/recall questions right away and "apply the technique" questions (map a diagram fragment, find the keys of a relation, write a statement) after a minute or two of thinking.

For practice applying the techniques, see the "Example question types" and "Practice" sections at the end.

The Relational Model

Topics:

• Terminology: relation, tuple, attribute, domain, relation schema, database schema
• Relations as sets
• Types of keys: superkey, candidate key, primary key, foreign key
• Integrity constraints: domain, NOT NULL, entity integrity, referential integrity
• Notation for relational schemas

Self check:

• Define relation, tuple, attribute, domain, and schema.
• Describe what makes two tables the same relation or different relations.
• Distinguish superkey, candidate key, and primary key, including how many of each a relation can have.
• Explain what a foreign key is.
• Explain what the referential integrity constraint requires.
• Explain what the entity integrity constraint requires.
• Explain when two tuples may have the same value for an attribute and when they may not.

ER/EER → Relational Mapping

Topics:

• The mapping process and its steps
• Mapping entity types, including composite attributes
• Mapping weak entity types
• Mapping binary relationships (1:1, 1:N, M:N)
• The merged relation, foreign key, and cross-reference approaches for mapping binary relationships and their tradeoffs
• Enforcing cardinality and participation constraints through the mapping
• Mapping multivalued attributes
• Mapping n-ary relationships
• Mapping specialization
• The multiple relations (superclass and subclass, subclass only) and single relation (single type attribute, multiple type attributes) approaches for specialization and their tradeoffs
• Mapping category (union) types
• Constraints the relational schema can't capture

Self check:

• State what relation(s) result from each mapping step, including identifying the primary key for each.
• Explain how a composite attribute and a multivalued attribute are each handled. Why can't a multivalued attribute simply be a column of the entity's relation?
• Explain which attributes of a weak entity type's relation are foreign keys and which form its primary key.
• Describe the merged relation, foreign key, and cross-reference approaches, and which are appropriate for 1:1, 1:N, and M:N relationships.
• Explain why, with the foreign key approach for a 1:N relationship, the foreign key goes on the N side.
• Explain where the relationship's own attributes go in each approach.
• Explain how the choice of primary key in the merged relation and cross-reference approaches determines which cardinality ratio is enforced.
• Explain how total participation can (or can't) be enforced with each approach.
• Which approach(es) avoid NULLs for partial participation?
• Explain why the merged relation approach requires total participation on both sides.
• Explain why an n-ary relationship can't be mapped with the foreign key approach and what constraints its mapping can't capture.
• Describe the four options for mapping a specialization and explain which ones can't handle overlapping specializations, which can't handle partial specializations, and the drawback(s) of each.
• Explain how a category type is mapped and when a surrogate key is needed.

Design Principles for Relational Schemas

Topics:

• Design guidelines for relational schemas
• Update anomalies: insertion, deletion, modification
• Spurious tuples

Self check:

• State the design guidelines for relational schemas and the problem each one avoids.
• Define insertion, deletion, and modification anomalies and give an example of each.
• Explain why eliminating repeated data matters.
• Explain what spurious tuples are, what causes them, and how to avoid them.

Functional Dependencies and Keys

Topics:

• Functional dependencies (FDs); trivial FDs
• Identifying the FDs of a relation
• Closure of a set of attributes
• Determining candidate keys
• Key vs. non-key attributes; proper subsets of a key

Self check:

• Explain what a functional dependency X → Y means. What does and doesn't it say about how the values of X and Y are related?
• Explain what sample data can and can't tell you about the FDs of a relation.
• Explain the relationship between functional dependencies and keys.
• Explain what a key attribute is and what a proper subset of a key is.

Normal Forms

Topics:

• 1NF, 2NF, 3NF, BCNF
• Relationship between the normal forms
• How far to normalize

Self check:

• State the definitions of 1NF, 2NF, 3NF, and BCNF.
• Describe in plain language what kind of dependency each of 2NF, 3NF, and BCNF prohibits.
• Explain what kind of problem each normal form prevents, using an example.
• Explain why a relation in BCNF is always in 3NF, and why the converse is not true.
• Explain why a relation whose candidate keys are all single attributes is automatically in 2NF.
• Explain why it is common to stop at 3NF or BCNF rather than normalizing further.

Decomposition

Topics:

• Decomposing a relation to fix a normal form violation
• Lossless join
• Dependency preservation
• Decomposition order
• Tradeoffs in choosing a decomposition

Self check:

• Describe how to decompose a relation to remove a violating FD, including identifying the primary key of each resulting relation.
• Explain what it means for a decomposition to be lossless and how to check it.
• Explain what it means for a decomposition to be dependency preserving and how to check it.
• Explain the options when a relation can't be decomposed into BCNF without losing a dependency, and the tradeoff of each.

Physical Implementation (DDL)

Topics:

• Reading CREATE TABLE statements
• Order of table creation
• MySQL data types and their uses: integer types (INT and its smaller and larger variants TINYINT, SMALLINT, MEDIUMINT, BIGINT), BOOLEAN; DECIMAL; floating point types (FLOAT, DOUBLE); CHAR, VARCHAR, TEXT; BINARY, VARBINARY, BLOB; DATE, TIME, DATETIME, TIMESTAMP, YEAR
  (you don't need to know the exact ranges of the integer types or the difference between FLOAT and DOUBLE, just when to use an integer type vs. DECIMAL vs. a floating point type)
• UNSIGNED
• Column and table constraints: PRIMARY KEY, FOREIGN KEY (including ON DELETE and ON UPDATE actions), NOT NULL, UNIQUE, DEFAULT, CHECK
• Single-column vs. composite keys and constraints
• AUTO_INCREMENT
• Changing constraints on an existing table
• Choosing a mechanism to enforce a requirement

Self check:

• Explain what determines the order in which tables can be created.
• Explain what the w in INT(w) and the p and s in DECIMAL(p,s) mean.
• Explain what UNSIGNED and AUTO_INCREMENT do and when to use them.
• Explain what PRIMARY KEY implies about a column and how it differs from UNIQUE.
• Explain the difference between a single UNIQUE (or PRIMARY KEY) constraint on several columns and a separate constraint on each column.
• Describe the requirements for a FOREIGN KEY and explain why naming the constraint is useful.
• Explain what a CHECK constraint can and can't express, and what happens when its condition involves NULL.
• Explain what DEFAULT supplies and what happens when an INSERT explicitly specifies NULL for a column with a default.
• Explain what happens when you add a constraint to a table whose existing rows violate it.
• Identify requirements that no declarative constraint can enforce and explain why not.

Data Modification

Topics:

• INSERT: single row, multiple rows, INSERT ... SELECT forms
• UPDATE
• DELETE
• Subqueries in the WHERE clause of UPDATE and DELETE
• LOAD DATA INFILE for bulk loading
• Modifications and integrity constraints
• Referential actions: RESTRICT / NO ACTION, CASCADE, SET NULL

Self check:

• Explain what values the unlisted columns get in an INSERT that names only some of the columns.
• Explain when the subquery in INSERT ... SELECT is evaluated relative to the insertion of rows and why that matters.
• Explain what value of a column is used on the right side of an assignment like SET hours = hours * 1.1.
• Explain which rows are counted as "updated" by an UPDATE.
• Explain what DELETE without a WHERE clause does (and doesn't) do.
• Explain what LOAD DATA INFILE is used for and when you would use it instead of INSERT.
• Explain what happens when a modification would violate an integrity constraint.
• Describe what RESTRICT, CASCADE, and SET NULL each do when a referenced parent row is deleted or its key is updated.
• Explain which modifications to the parent table and which to the child table can violate a foreign key constraint.
• Explain how a DELETE on one table can affect rows in other tables.

Example Question Types

Beyond the conceptual questions above, expect questions that ask you to apply your knowledge to a given diagram, schema, or data. For example:

• Map a given ER/EER diagram (or fragment) to a relational schema, identifying primary keys and foreign keys.
• Given several candidate mappings for a relationship or specialization, identify which are appropriate and explain what is wrong with the others.
• Identify the constraints from an ER/EER diagram that a given relational schema does not capture.
• Identify the update anomalies that can occur in a given relation.
• Given a decomposition and sample data, determine whether joining the relations produces spurious tuples.
• Given a description of what the attributes of a relation mean, write the FDs that hold.
• Compute the closure of a set of attributes and use it to determine whether the set is a superkey, a candidate key, or neither.
• Given a relation and its FDs, find all candidate keys.
• Given a relation and its FDs, determine which normal form(s) the relation is in and identify the FD(s) responsible for each violation.
• Decompose a relation into 3NF or BCNF, and determine whether the decomposition is lossless and dependency preserving.
• Choose an appropriate data type for a described attribute and justify the choice.
• Given a relational schema and a set of requirements, choose appropriate data types and constraints for each table.
• Given CREATE TABLE statements, describe the tables and constraints they define, or determine whether they meet a set of requirements.
• Given a list of requirements, identify how each should be enforced (primary key, UNIQUE, NOT NULL, DEFAULT, CHECK, foreign key, data type, or can't be enforced) and explain why other plausible mechanisms are not the best fit.
• Write INSERT and INSERT ... SELECT statements to add described data.
• Write UPDATE and DELETE statements to carry out a described change.
• Given a schema with constraints and sample data, determine whether a given INSERT, UPDATE, or DELETE is legal, and if not, which constraint it violates.
• Given sample data and a modification statement, determine which rows are affected, including rows affected through ON DELETE CASCADE or SET NULL.

Practice

• Work through the homework problems and the example problems from class (in the slides), including any you didn't complete the first time. Do each on paper first, then check your answer.
• Map the ER/EER diagrams from class and the homework, including trying the alternative approaches for relationships and specializations and explaining what each does and doesn't enforce.
• For normalization problems, go beyond the answer: check every decomposition for lossless join and dependency preservation, and try decomposing in a different order to see whether the result changes.
• Create the tables for a schema using MySQL Workbench's table editor, then read the CREATE TABLE statements it generates and test the constraints with modifications you expect to succeed and to fail.
• For each modification statement you write, be able to explain which rows it affects and which constraints could cause it to be rejected.