In studying normal forms, we first defined BCNF: A relation is in BCNF if every nontrivial functional dependency for that relation has a superkey on its left-hand side. We later explored a closely related but more complex definition, which we termed third normal form (3NF). Third normal form is more general than BCNF: That is, all BCNF relations are in 3NF, but some 3NF relations are not in BCNF.

1. Explain the deficiency of BCNF that led to the definition of 3NF.

2. Describe how the definition of 3NF differs from the definition of BCNF.

Suppose we have a relation R(a, b, c, d, e) whose keys are {a, b} and {c, d}. Explain why each of the following FDs is consistent with third normal form.

1. a b c → d
2. a c e → b c
3. a c e → e

Suppose we have the relation

books(author, affiliation, title, type, price)

with the functional dependencies

author title → type price
type → price
author → affiliation

Identify which of the functional dependencies violate 3NF, and synthesize the relation into 3NF relations.

Draw an entity-relationship diagram for a database of used vehicles, including each vehicle's VIN (identification number unique to each vehicle), year of manufacture, make, model, color, type (car/truck/minivan/etc.), seats (number), buyer name, and buyer phone number. Knowing just the make and model is enough to determine the vehicle's type and the number of seats, and each buyer (who may purchase multiple vehicles) has just one phone number.

Draw an entity-relationship diagram for a manufacturer's database to track client orders. The manufacturer carries several lines of products. Each order can involve several products, and there is a quantity and price associated with each product ordered. The manufacturer tracks client names and telephone numbers. (You need not bother indicating keys.)

Draw an entity-relationship diagram for a court database. A case is initiated by a plaintiff, who names the defendant and the plaintiff's lawyers. Later, after the defendant is notified of the case, the defendant names lawyers representing that side. Then a judge is assigned to the case. (You need not bother with indicating attributes or keys.)

Explain what is meant by the term weak entity set, and give an example.

In designing a database for automotive sales, we create the following entity-relationship diagram. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Suppose we were writing a Web site to manage paper submissions and reviews for a variety of conferences hosted by different organizations. We design the following entity-relationship diagram. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Below is an entity-relationship diagram for a bug database. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Suppose our company runs several projects, each involving several teams of workers, as indicated in the below entity-relationship diagram. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Below is an entity-relationship diagram for tracking home sales. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Below is an entity-relationship diagram for tracking and discussing bug reports for a collection of software packages. Convert it into a corresponding set of relational schemas, underlining the primary-key attributes for each schema.

Below is an entity-relationship diagram for a course schedule where each section can have any number of instructors. Convert it into a corresponding set of relational schemas, underlying the primary-key attributes for each schema.