Consider this relation schema with 7 attributes (P is abbreviation for patient a
ID: 3829270 • Letter: C
Question
Consider this relation schema with 7 attributes (P is abbreviation for patient and D is abbreviation for doctor) Visit = (Date, Pno, Pname, Dno, Dname, Diagnosis, Cost) and this set of 4 functional dependencies F = {Pno rightarrow Pname, Dno rightarrow Dname, Diagnosis rightarrow Cost, (Date, Pno) rightarrow (Dno, Diagnosis)} 1. Find a key of V by discovering a set of attributes whose attribute closure is all the attributes of V. 2. Is C in BCNF (under F)? Why? 3. Is V_1 = (Date, Pno. Pname) in BCNF? Why? 4. Is V_2 = (Date, Dno, Dname, Diagnosis, Cost) in BCNF? Why? 5. Is V_1 and V_2 a lossless-join decomposition (under F)? Why? 6. Using the normalization algorithm, find a lossless-join decomposition of V into BCNF relation schemas (under F).Explanation / Answer
1 - The prime attribute is (date,pno) from these two all the attributes of v can be obtained.
2-V is not in bcnf bcoz the pno ->name shows the partial dependency from prime attribute (date,pno) and hence not in 2nf so can not be in bcnf
3-again V1 is not in bcnf becoz the relation pno ->name shows partial dependency.
4-Here the prime attriute is only date....but it is also not in bcnf due to presence of transitivity as date -> dno and dno-> dname showing transitivity hence not in 3nf so not in bcnf