Assignment 5

Due date: Wednesday, Nov. 20, 11:59PM.
NOTES:
This assignment is meant to give you practice with Relational Database Design and Normalization.

Problem 1

Solve Exercise 8.29 page 373, except for item f, in the textbook.

Problem 2

Show that AB → D is in the closure of the functional dependencies {AB → C, CE → D, A → E}

Problem 3

Consider the relation R(A, B, C, D, E) with the following dependencies AB → C, CD → E, DE → B.
Is AB a candidate key of R? If not, is ABD? Explain your answer thoroughly.

Problem 4

Prove or disprove the following inference rules for functional dependencies. "F ⇒ X → Y" stands for the functional dependency X → Y can be inferred from the set of functional dependencies F. Give step by step justification to each of your answers using Amstrong's Axioms.
  1. {W → Y, X → Z} ⇒ WX → Y
  2. {X → Y, X → W, WY → Z} ⇒ X → Z
  3. {X → Y, XY → Z} ⇒ X → Z

Problem 4

Consider a relation R = (A, B, C, D, E). You are given the following dependencies: A → B, BC → E, and ED → A.
  1. List all keys for R.
  2. Is R in 3NF?
  3. Is R in BCNF?

Problem 5

Consider the following relation R = (A, B, C, D). For each of the following sets of FDs, do the following:
  1. Identify the candidate key(s) for R.
  2. Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
  3. If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies.
  1. C → D, C → A, B → C
  2. ABC → D, D → A

Deliverables

  1. a document with the solutions to the above problems.
Upload the document in blackboard.