Here is the table I am working with: Furniture (FID, Manufacturer Name, Model#,
ID: 3823267 • Letter: H
Question
Here is the table I am working with:
Furniture (FID, Manufacturer Name, Model#, Model Name, Description, I#, Step#, Text, Part#¸Number Used)
Functional Dependancies:
FID - Manufacturer Name, Model#, Model Name, Description, I#
I#, Step# - Text
I#, Step#, Part# -Number Used
Question: I think the key for the Furniture table is (FID, Step#, Part#). Prove that this key is unique using the functional dependencies above and the proof mechanism discussed in class.
I need some help compelting this proof, as I do not complety understand the reflexivity, augmentation, additivity, projectivity, transitivty, and pseado-transitivity methods. Please explain all your work so I can understand how to perform these proofs.
Thank you!
Explanation / Answer
Yes the primary key is (FID, Step#, Part#). Let's prove it now
For simplicity let's represent each attributes with some single alphabetical letter as follows
Actual name
Then the given FDs can be re-written as
1. A BCDEF
2. FG H
3. FGI J
--------------------------------------------------------------------------------------------------------------
Now we will divide the attributes into three sets. Left, Right, Middle.
Left - contains the attributes that appear only on the LHS of the FDs.
Right - contains the attributes that appear only on the RHS of the FDs.
Middle -contains the elements that appears both on LHS and RHS
From the given dependencies we can see that
A, I, G appears only on LHS
B,C,D,E,H,J appears only on RHS
F appears both on LHS and RHS
** Note : The elements which appear on the LHS only are for sure part of the primary key and the attributes that appear only on RHS will never be a part of key. The elements in the middle "may" become part of the key and this is what we have to find out.
Closure of (AIG)+
AIG AIG by reflexity property
AIG AIGBCDEF from FD1, A BCDEF
AIG AIGBCDEFH from FD2, FG H
AIG AIGBCDEFHJ from FD3, FGI J
We have got all the attributes on RHS. This means that we can determine all the other attributes, given A,I,G. Therefore, AIG or (FID, Step#, Part#) is a primary key.
------------------------------------------------------------------------------------------------------------
1. Reflexivity Rule : If A B, i.e, if B is a subset of A, then A B
Example : A A, B B, ABC B, AB AB, ABC A
2. Augmentation Rule : If A B, then AC BC, where C is any attribute added on both LHS and RHS
Example : ABC D, then ABCE DE
3. Transitivity Rule : If A B and B C, then A C.
If given A you can determine B, and given B you can determine C, then it implies that you can derive C from A.
4. Union : If A B and A C, then A BC
5. Decomposition : If A BC, then A B and A C
6. Pseudo Transitivity : If A B and BC D, then it can said that AC D
Actual name
Letter FID A Manufacturer Name B Model # C Model Name D Description E I# F Step# G Text H Part# I Number Used J