3

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Feek
  • 297
  • 3
  • 15
  • 2
    *"some non-prime attributes (firstName, lastName)"* Those are prime attributes. – Mike Sherrill 'Cat Recall' Nov 18 '16 at 11:46
  • they are non-prime if they're not a part of the key, correct? Or are they non-prime if they're not part of the candidate key? – Feek Nov 18 '16 at 11:53
  • 4
    PKs don't matter. CKs do. They are prime because they are in a CK. – philipxy Nov 18 '16 at 11:54
  • Just re-read the definition that I have in my notes and it makes much more sense now after your answer. Thank you. – Feek Nov 18 '16 at 11:55
  • 1
    One doesn't work with databases too long to learn that (FirstName, LastName) isn't even close to a candidate key. Wait til you hit your second "Joe Smith" or "Sally Jones" or "John Brown" and you'll learn it real fast. – TommCatt Nov 19 '16 at 04:43
  • 2
    I was using it as an example. My database has a CK that is something like firstName, lastName, phoneNumber. I was trying to think of a hypothetical circumstance that would suffice for my question. – Feek Nov 19 '16 at 04:53

1 Answers1

5

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83