CS 430: Database Management Systems


Due Date - 11:59PM Sunday, February 25th

NO LATE PERIOD


Homework Assignment 3

Each question is worth 25 points.

  1. Consider a relation R with five attributes ABCDE. 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?

  2. Consider the following collection of relations and dependencies. Assume that each relation is obtained through decomposition from a relation with attributes ABCDEFGHI and that all the known dependencies over relation ABCDEFGHI are listed for each question. (The questions are independent of each other.)

    For each (sub)relation: (1) State the strongest normal form that the relation is in. (2) If it is not in BCNF, decompose it into a collection of BCNF relations.

    1. R1(A,C,B,D,E), A → B, C → D
    2. R2(A,B,F), AC → E, B → F
    3. R3(A,D,G), D → G, G → H
    4. R4(D,C,H,G), A → I, I → A
    5. R5(A,I,C,E)

  3. Suppose that we pick the following three tuples from a legal instance of a relation S (S has 100 tuples in total). Relation S has the following schema:

    (A : integer, B : integer, C : integer).

    The three tuples are: (1,2,3), (4,2,3), and (5,3,3).

    1. Which of the following dependencies can you infer does not hold over S?
      1. A → B,
      2. BC → A,
      3. C → B
    2. Can you identify any dependencies that hold over S?

  4. Suppose that we pick the following four tuples from a legal relation S (S has 100 tuples in total). S has the following schema:

    (A: integer, B: integer, C: integer)

    The four tuples are: (1,2,3), (4,2,3), (5,3,3) and (5,3,4).

    Which of the following functional (→)can you infer does not hold over relation S?

    1. A → B
    2. BC → A
    3. A → C
    4. AC → B

    These homework solutions must be checked into Canvas in .pdf format


    Copyright © 2016: Colorado State University for CS 430. All rights reserved.