Consider the following relation about students:

      STUDENT(studentID,firstname,lastname,major,credits,status)

The functional dependencies:

Answer the following questions:

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

    This is 2NF because it is 1NF (it is a valid relational schema) and the left side of each FD is either a key or a non-key, so it is not a proper subset of a key. It is not 3NF because credits is not a superkey and status is not part of a key.

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

    Address the 3NF violation by decomposing into new relations containing the attributes in the problematic FD with the primary key being those attributes on the left side of the FD.

      STUDENT(studentID,firstname,lastname,major,credits)
      CREDITS(credits,status)
    

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

Common problems: