### CS 4250: Database Management Systems

#### Fall 2016 - Problem Set 3

#### Due Friday, 11/18/2016, at midnight

This is an individual assignment. **All work must be your own. **
You should not look at any other student's work (in whole or in part, on paper
or on screen), nor allow anyone else to look at yours, during the course of this assignment.

You may email a plain text, MS Word or PDF document to the instructor
with subject line "cs4250,hw3" (by midnight), or turn in a typed hardcopy
(during class time on 11/??).

Consider a relation R with attributes ABCDE. You
are given the following dependencies B -> DE, E -> A, AB -> D.

- Attribute closure of A?
- Attribute closure of B?
- Attribute closure of E?
- Attribute closure of AB?
- Attribute closure of BC?
- Identify which, if any, of the above are candidate keys.

Consider a relation R with attributes ABCDE. You
are given the following dependencies AB -> C, D -> AB.

- List all (candidate) keys for R.

- Is R in 3NF? Why or why not?

- Is R in BCNF? Why or why not?

Consider a relation R with attributes ABCDE. You
are given the following dependencies AB -> C, AB -> D, AB -> E, E -> B, D -> A.

- List all (candidate) keys for R.

- Is R in 3NF? Why or why not?

- Is R in BCNF? Why or why not?

- Suppose you are given a relation R(A, B, C, D).
For each of the following sets of FDs, assume they are the only dependencies that
hold for R. Do the following: a) identify the candidate key(s) for R. b) State
whether or not the proposed decomposition of R into smaller relations is a good
decomposition and briefly explain why or why not.

- A -> BC, B -> D; decompose into ABC and BD.
- Candidate keys:
- Analysis:

- A -> D, D -> C; decompose into AD and BC.
- Candidate keys:
- Analysis:

- A -> C, BC -> D; decompose into ACD and BC.
- Candidate keys:
- Analysis:

- Suppose you are given a relation R with attributes ABCDEF. You are given
functional dependencies A -> D, BC -> F, and D -> C. Using the decomposition techniques
described in class, decompose this relation into relations in "good" normal forms.
For each of the new relations you create, identify the normal form it is in.
If you
choose not to decompose the relations into the best normal form we discussed, explain
why you made that choice. If there are any interesting issues or potential problems
with your decomposition, explain what they are.