Place the following tables in BCNF / 4NF 1) c3 ? c4 c2 ? c7 c1 ? ? c5 (c1,c2) ?
ID: 3741770 • Letter: P
Question
Place the following tables in BCNF / 4NF
1)
c3 ? c4
c2 ? c7
c1 ? ? c5
(c1,c2) ? c6
2) Zoo database
Animal id uniquely identifies each individual animal at the zoo.
If you know an animal id, you know the animal type, feeding time, location, animal food, cleaning time, medicines, and vet.
Cleaning time and feeding times are done by location.
Each type of animal has its own assigned vet and assigned food.
Medicines, of course, are individualized by specific animal.
3) (Farmer) ? ?(Variety)
(Farmer) ?(Location)
(Variety) ?(HarvestDate)
4)
:7 8 7 6 5 4 4 3 2 1 cS-5- 13-14-367981 n| n| n add | d| e e e e2 1| 2 3 4 5 5 6 7 8Explanation / Answer
1.
Given relation R(c1,c2,c3,c4,c5,c6,c7) and functional dependencies are:
c3->c4
c2->c7
c1->>c5
c1,c2->c6
As (c1,c2,c3) derives all attributes so its a primary key.
To place the relation in BCNF and 4NF, we have to consider all
normal forms 1NF,2NF,3NF,BCNF,4NF.
1NF:
As all attributes are atomic, so it is in 1NF.
2NF:
As there is a partial dependency c1,c2->c6, so it is not in 2NF.
To place in 2NF, break R into following relations:
R1(c1,c2,c5,c6,c7)
R2(c3,c4)
3NF:
As relation R1 contins dependencies, c2->c7 and c1->c5,
where c1,c2 are not super keys and c5,c7 are not prime attributes.
So, it is not in 3NF.
Break it into following relations to place 3NF:
R1(c1,c5)
R3(c2,c7)
R4(c1,c2,c6,
R2(c3,c4)
BCNF:
Now the relations are in BCNF, for all dependencies X->Y, X is a super key.
4NF:
It is also in 4NF, as for all multivalued dependencies X->>Y,X is a super key.