Complete the following exercises, normalizing the specified schemas (solutions):

  1. Based on the following functional dependencies, show the resulting schemas in First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF) (adapted from Coronel and Morris 2017):

    \[\begin{align} A, B & \rightarrow C, D\\ A, C & \rightarrow B, D\\ C & \rightarrow B \end{align}\]
    First Normal Form (1NF):
    A,* B,* C, D
    Second Normal Form (2NF)
    Same as 1NF due to no partial key dependencies
    Third Normal Form (3NF)
    Same as 2NF due to no partial key dependencies
    • Note: Not in BCNF because \(C \rightarrow B\) is not trivial and \(C\) is not a candidate key.
    Boyce-Codd Normal Form (BCNF)
    A,* C,* D

    C,* B

  2. Normalize the following schema, with given constraints, to BCNF. (Exercise 8.21)

    • books (accessionno, isbn, title, author, publisher)
    • users (userid, name, deptid, deptname)

    where

    • accessionno \(\rightarrow\) isbn
    • isbn \(\rightarrow\) title
    • isbn \(\rightarrow\) publisher
    • isbn \(\rightarrow\) author
    • userid \(\rightarrow\) name
    • userid \(\rightarrow\) deptid
    • deptid \(\rightarrow\) deptname
    First Normal Form (1NF)
    books (accessionno, isbn, title, author, publisher)
    users (userid, name, deptid, deptname)
    Second Normal Form (2NF)
    Same as 1NF due to lack of partial key dependencies
    Third Normal Form (3NF)
    acquisitions (accessionno, isbn)
    books (isbn, title, publisher, author)
    users (name, deptid)
    departments (deptid, deptname)
    Boyce-Codd Normal Form (BCNF)
    Same as 3NF

  3. Consider the relation Courses (course, teacher, hour, room, student, grade). Let the set of functional dependencies be as follows:

    • course \(\rightarrow\) teacher (i.e., a course has a unique teacher)
    • hour, room \(\rightarrow\) course (i.e., only once course can meet in a given room as a given hour)
    • hour, teacher \(\rightarrow\) room (i.e., a teacher can be in only one room at a given hour)
    • hour, student \(\rightarrow\) room (i.e., a student can be in only one room at a given hour)
    • course, student \(\rightarrow\) grade (i.e., students get only one grade in a course)

    Decompose the relation into 3NF. Are any of the relations not in BCNF? (Exercise 3.5.2.c)

    Teaching
    course, teacher
    Course Schedule
    hour, room, course
    Teacher Schedule
    hour, teacher, room
    Student Schedule
    hour, student, room
    Grades
    course, student, grade

    None of the relations violate BCNF.

  4. Consider the relation Stocks (broker, office, investor, stock, quantity, dividend). Let the set of functional dependencies be as follows:

    • stock \(\rightarrow\) dividend
    • investor \(\rightarrow\) broker
    • investor, stock \(\rightarrow\) quantity
    • broker \(\rightarrow\) office

    Decompose the relation into 3NF. Are any of the relations not in BCNF? (Exercise 3.5.3.c)

    Stock Dividend
    stock, dividend
    Financial Advisor
    investor, broker
    Transaction
    investor, stock, quantity
    Broker
    broker, office

    None of the relations violate BCNF.

  5. Based on the following functional dependencies, show the resulting schemas in 1NF, 2NF, 3NF, and BCNF (adapted from Problem 6.8):

    \[\begin{align} A, B & \rightarrow C, D, E, F, G\\ A & \rightarrow D\\ C & \rightarrow B\\ E & \rightarrow G \end{align}\]
    First Normal Form (1NF):
    A,* B,* C, D, E, F, G
    Second Normal Form (2NF)
    A,* B,* C, E, F, G

    A,* D

    Third Normal Form (3NF)
    A,* B,* C, E, F
    • Note: Not in BCNF because \(C \rightarrow B\) is not trivial and \(C\) is not a candidate key.

    A,* D

    E,* G

    Boyce-Codd Normal Form (BCNF)
    A,* C,* E, F

    A,* D

    C,* B

    E,* G