Consider the following relation about students:
The functional dependencies:
Answer the following questions:
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.
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.
This is now both 3NF and BCNF because for all FDs, the left side is a superkey.
A non-dependency-preserving decomposition. This means that after decomposing relations, there is at least one FD which is no longer covered by a single relation.
The link between a student and their credits has been lost.
This is not dependency-preserving as the first FD is no longer fully covered by a single relation. It also doesn't fix the 3NF problem.
Decomposing to satisfy 3NF but then not considering BCNF.
Incorrect application of the definitions of the various normal forms.