Confused about third normal form: a table having more than one candidate key

Question

I was studying normalization and trying to implement it on some examples. I was doing third normal form, which from what I understand is: "A relation is in 3NF if it's in 2NF and doesn't have any transitive dependencies".

I got stuck on an example, a table having more than one candidate key. How do we normalize that kind of table to 3NF?

VIN     | Make |  Model  |  Year  | OwnerID  |Owner
----------------------------------------------------
11a     |Toyota| Corolla | 1988   | 11245    | John
11b     |Nissan| Caor    | 1988   | 12458    | Peter
11c     |BMW   | GMC     | 1956   | 15487    | Anne  

Here VIN is the primary key and clearly make, model, ownerID owner are candidate keys, which would have a transitive relation to each other and year. How do I break this down to 3NF?

Answer 1

Be careful when you infer dependencies from your example data. You say that make, model, ownerId are "clearly" candiate keys but that seems far from clear to me. Wouldn't you expect at some point that you might have more than one car of the same make and/or model? Couldn't it be the case that an owner might own more than one vehicle? You must consider what dependencies you actually want to enforce or you expect to be the case for all possible (correct) populations of data in the business domain. A dependency is a function of the business requirements and the reality being modelled, not just the current population of data.

With only your attribute names to go on I'd guess that the dependency OwnerId -> Owner might hold. If it happens that OwnerId is not a candidate key then that would be an example of a transitive dependency in violation of 3NF: Owner depends on a non-key attribute (OwnerId).

Answer 2

If you want to strictly hold to the normalization and it's normal forms, you would have to start with 1NF. You might create separate tables for Make,Model,Owner and rename current one into Car. Then you can add foreign keys respectively.

To uphold 2NF you can wonder to grab the year attribute from the VIN, which it contains as a model year number (does not necessarily have to be the same as the actual year of making).

3NF states that table columns should contain only columns that are fully dependent upon primary key, which would've been ok now.