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?
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).