functional dependencies in databases

Question

I'm really confused at determine functional dependencies and need to confirm that I have all my dependencies list. I have a table.

From the following table, i found:

My question here is that, did i correctly list all of possible functional dependencies here? Also (If I'm right), i see that model --> make, Do i still need to model, color --> make, etc..Also since make, msrp --> model, year , do I still need model, year --> make, msrp in the list?

Answer 1

Functional dependencies hold for all time.

When you try to derive FDs from data, you're liable to derive FDs that hold only by coincidence. The FD make, color -> model means "When I see a Honda that's red, I know it's an Accord." As far as the data you're given is concerned, that's true. But you and I know that, if we wait for more data, we're certain to find a red Honda Civic. The FD make, color -> model holds for this sample data, but it doesn't hold for all time.

To determine whether you've found all the FDs based on sample data, you have to test every possible combination of attributes. (Or, better yet, write a program to do it.) I didn't try to find all of them. You seem to be on the right track.

Also (If I'm right), i see that model --> make, Do i still need to model, color --> make, etc.

If you're doing this for school, your professor probably wants to see a) all the possible FDs (so, include model, color --> make) and b) all the FDs after you've removed the reducible ones (so, remove model, color --> make).

Also since make, msrp --> model, year , do I still need model, year --> make, msrp in the list?

Yes. Normalization through BCNF depends on determing a) all the candidate keys, and b) all the prime and nonprime attributes. (A prime attribute is an attribute that's part of any candidate key.) The set of FDs {AB->CD} gives you one candidate key, AB, and two nonprime attributes, C, D. The set of FDs {AB->CD, CD->AB} gives you two candidate keys and zero nonprime attributes.