Search code examples
databasedatabase-designdatabase-normalizationfunctional-dependencies3nf

Circular Dependencies in 3NF


I have a table:

Customer(username, firstName, lastName, age, gender, race)

username determines firstName, lastName, age, gender, race.

firstName, lastName can be used to uniquely identify a row in the table, so firstName, lastName determines username, age, gender, race.

Is the table in 3NF because some non-prime attributes (firstName, lastName) can be used to determine other attributes in the table, but the PK (username) can determine the non-prime attributes that are then used to determine other attributes?

This is not in BCNF because of that circular dependency.


Solution

  • Circularities are irrelevant, and it's in BCNF, and every lower NF. A definition for BCNF is, all the determinants of non-trivial FDs are superkeys. If there are multiple CKs (candidate keys), so be it. The CKs will detemine the attributes of other CKs, because a CK determines every attribute.

    PKs are irrelevant to normalization (and all other relational theory), CKs matter. A PK is just a CK you decided to call a PK.

    There are two CKs here, {username} and {firstName, lastName}. The prime attributes, ie those in the CKs, are username, firstName & lastName.