CS 4250: Database Management Systems

Spring 2015 - Problem Set 3

Due Friday, 4/24/2015, at 4 pm

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", or turn in a typed hardcopy.

  1. Consider a relation R with attributes ABCD. You are given the following dependencies BC -> D, A -> B, C -> A.

    1. Attribute closure of A?

    2. Attribute closure of B?

    3. Attribute closure of AC?

    4. Identify which, if any, of the above are candidate keys.

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

    1. List all (candidate) keys for R.

    2. Is R in 3NF? Why or why not?

    3. Is R in BCNF? Why or why not?

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

    1. List all (candidate) keys for R.

    2. Is R in 3NF? Why or why not?

    3. Is R in BCNF? Why or why not?

  4. Suppose that we have the following three tuples in a legal instance of a relation schema S with three attributes A, B and C:
    attribute namesprimary_keyA B C
    tuple 118 1 3
    tuple 224 1 4
    tuple 334 2 5

    1. For each of the following dependencies, can you infer whether or not it holds over schema S? Why or why not?
      1. A -> C

      2. C -> A

    2. Can you identify any functional dependencies that hold over S? If so, please name one.

  5. 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.

    1. A -> B, C -> D; decompose into AC and BCD.
      1. Candidate keys:
      2. Analysis:

    2. AB -> C, C -> D, C -> A; decompose into CD and ABC.
      1. Candidate keys:
      2. Analysis:

    3. AB -> C, B -> D; decompose into ABC and BD.
      1. Candidate keys:
      2. Analysis:

    4. A -> B, B -> C, C -> D; decompose into AB and CD.
      1. Candidate keys:
      2. Analysis: