Assignment 5
Due date: Wednesday, Nov. 20, 11:59PM.
NOTES:
- Start early!
- This is an individual assignment; no groups allowed.
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.
- {W → Y, X → Z} ⇒ WX → Y
- {X → Y, X → W, WY → Z} ⇒ X → Z
- {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.
- List all keys for R.
- Is R in 3NF?
- 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:
- Identify the candidate key(s) for R.
- Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
- If R is not in BCNF, decompose it into a set of BCNF relations that preserve the dependencies.
- C → D, C → A, B → C
- ABC → D, D → A
Deliverables
- a document with the solutions to the above problems.
Upload the document in blackboard.