0

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

3
  • "along with that a person could have more than 1 phone number" - definetly brokes BCNF as well as 3NF provided person==ID or person==TFN. Yes, your decomposition is correct. Other possible decomposition PersonPhone(PhoneNo,TFN).
    – Serg
    May 25, 2016 at 7:46
  • 1
    There are situations where the same phone number reaches more than one person. For example, where two spouses are both in the database, and they have the same landline. You have to know the situation for your case. May 25, 2016 at 8:44
  • BCNF is when/iff every determinant of a non-trivial FD is a superkey, not CK. And "every" means all the FDs that hold, not just the ones in some cover or some that hold or the ones that hold when some in a list hold. It means all the FDs that hold when those in a given cover do. "the minimal FDs I have is" doesn't make sense, presumably you are trying to say that list of FDs is a minimal cover. It is important to quote & use definitions. Then ask 1 question about how you are first stuck.
    – philipxy
    Jan 19, 2021 at 20:09

1 Answer 1

0

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.

2
  • That makes so much sense, thanks! When I was saying TFN → ID I was thinking because we know ID and TFN are both primary keys, don't we say that ID->TFN and the reverse? But the reverse being implied since they're both PKs? Because of that I could also say that TFN->ID, Name, Phone but in minimal form I wouldn't have to say the implication that ID->TFN right? (or in the original case, TFN->ID) May 25, 2016 at 8:00
  • An attribute is a key if and only if it determines all the other attributes. So TFN → ID is equivalent to say that TFN is a key. We can omit the other dependencies TFN → Name, etc. since these are implied by TFN → ID and ID → Name, etc., and it is better to avoid derived dependencies when we reason about normal forms, so we use a canonical cover (or “minimum set of dependencies"). So the dependencies that I have listed in the aswer are always a canonical cover.
    – Renzo
    May 25, 2016 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.