Database Design – Checking for BCNF Through Functional Dependencies

database-designdenormalizationnormalization

Okay so I know that for BCNF, when listing all non-trivial FD's that everything to the left has to be a candidate key.

So I have this relation:

Person(Id, TFN, Name, Phone)

Where Id and TFN(Like SSN) are both Primary Keys.

So the minimal FDs I have is:

ID->TFN
ID->Name
ID->Phone 
(TFN->ID is redundant right?)

So it looks to be in BCNF but I was thinking, does Phone->Name? Because Phone would be unique and point to a Name too, along with that a person could have more than 1 phone number, is that the right approach? Given that is true, it would not be in BCNF because the left Phone is an attribute correct? Or at the very least would Phone be considered a candidate key? But if it is a candidate key, it is on the left and therefore still BCNF.

I'm going around in circles with this thought process..

So if that is all correct then the way to decompose the Person relation would be to remove phone and make a new relation

PersonPhone(PhoneNo, id*)

Where id is a foreign key on Person. Then that would make Person BCNF?

Thanks

Best Answer

First of all, you say:

(TFN → ID is redundant right?)

This dependency is not redundant, since otherwise you do not know that TFN is a key, and you lose an essential information. So you must include it in the set of functional dependencies.

For phone, if each phone is uniquely associated to a person, and each person can have only one phone, then you have these functional dependencies:

ID → TFN
ID → Name
ID → Phone 
TFN → ID
Phone → ID

There are three keys, ID, Phone and TFN, and the relation is in BCNF.

However, if each person can have more than one phone, than the situation is completely different. You have this set of functional dependencies:

ID → TFN
ID → Name
TFN → ID
Phone → ID

the key now is only Phone, and the relation has redundancies (since for each phone related to the same person, you have to repeat the ID, TFN, and Name of that person). The relation is not in BCNF, and to bring it in such form you have to decompose it in the two relations:

R1(ID, TFN, Name)
R2(Phone, ID)

the first with the dependencies:

ID → TFN
ID → Name
TFN → ID

and the second one with the dependency:

Phone → ID

so that no dependency is lost with the decomposition.

Related Question