You have:
R (ABCDEFGH)
AB-->D
B-->C
B-->E
A-->H
H-->G
I'm not sure how you resulted in the decompositions but the first thing to do usually is to find the candidate keys if the relations. Here there is only one, the ABF
.
Then you identify that the relation is not in 2NF, as there are dependencies on parts of candidate keys and not the whole of them (this part seems to have been done it quite well.) The partial dependencies (on parts of the candidate keys) are these and you only missed the last one, that can be derived from the others:
AB-->D
B-->C
B-->E
A-->H
A-->G -- missed this one
So (leaving the first question for the end), the decompositions to:
R (ABCDEFGH)
-- splitting because of the AB -> D, fine
R*(ABCEFGH) R1 (ABD)
-- splitting because of the B -> C, fine
R**(ABEFGH) R2 (BC)
-- splitting because of the B -> E, fine
R***(ABFGH) R3 (BE)
are rather fine, except some minor details like the D
reappearing in the R
relation.
The next part which is your second question regarding the A->H
and H-G
was problematic due to your missing of the implied A->G
dependency. It should be:
-- splitting because of the A -> HG
R****(ABF) R4 (AHG)
We have now resulted in 5 relations, all in 2NF:
R**** (ABF)
R1 (ABD) -- AB-> D
R2 (BC) -- B -> C
R3 (BE) -- B -> E
R4 (AHG) -- A -> HG , H -> G
In fact all are in 3NF except for R4
. So, we have to split that into two relations:
R4* (AH) -- A -> H
R5 (HG) -- H -> G
We now have 6 relations (R****, R1, R2, R3, R4*, R5) which are all in 3NF and BCNF as well. In fact they are all in 4, 5 and 6NF.
Regarding your first question, you could have used one split into (BCE)
instead of the two (BC)
and (BE)
and it would have been perfectly fine. Your final result would be five relations (instead of six), all in 3NF - and all the way up to BCNF, 4NF, 5NF, 6NF (except for that (BCE)
which is in 5NF.)
Best Answer
To answer the question about transitive dependency:
If
modelName
is unique then it is a candidate key, so there is no transitive dependency. The table is at least in 3NF.