Consider the following relation about students and courses:

      COURSE(dept,number,studentID,firstname,lastname,instructor,time,room,grade)

The functional dependencies:

Answer the following questions:

  1. What normal form is this relation in? Explain your answer.

    This is 1NF (it is a valid relational schema). The FD dept,number → instructor has non-key attributes (instructor) on the right side and the left side (dept,number) is a proper subset of the primary key so it is not 2NF.

  2. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

    The first three FDs all violate 2NF, so decompose into new relations containing the attributes in each FD with the primary key being those attributes on the left side of the FD:

      INSTRUCTOR(dept,number,instructor)
      LOCATION(dept,number,time,room)
      STUDENT(studentID,firstname,lastname)
      GRADE(dept,number,studentID,grade)
    

    The last relation is what is left in COURSE after dealing with the three problematic FDs, with a name change to better reflect what remains. (INSTRUCTOR and LOCATION could be combined without breaking 2NF because they have the same primary key.)

    This is now both 3NF and BCNF because for all FDs, the left side is a superkey.

Common problems: