Activity: 3NF and BCNF

(no submission)

Purpose: Understand the concepts of normalization; apply the concepts to refine database structures; use 3NF to decompose relation; be able to recognize if the decomposed relations are in 3NF or BCNF

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with another student in this course.

Consider a relation Stocks(B, O, I, S, Q, D), whose attributes may be thought of informally as broker, office (of the broker), investor, stock, quantity (of the stock owned by the investor), and dividend (of the stock). Let the set of FDs for Stocks be 

FDs = { S -> D, I -> B, IS -> Q, B -> O }
  1. List all the superkeys for the Stocks relation
  2. Verify that the given set of FDs is a minimal basis
  3. Use 3NF, decompose the given Stocks relation into proper relations — show your steps
  4. Verify that the decomposed relations are in 3NF
    • If they are not, indicate all the 3NF violations
    • If they are, explain or discuss to show that the decomposed relations are in 3NF
  5. Are the decomposed relations (above) in BCNF?
    • If they are not, indicate all the BCNF violations
    • If they are, explain or discuss to show that they are in BCNF