Question

mar Given the following relation (assume a student is registered with only one de- partment) STUDENTDEPT: (sno, sname, saddr, age, dno, dname, dlocation): Specify a suitable set of main functional dependencies Decompose the relation STUDENTDEPT such that is satises third normal form. Show that this decomposition has (or doesn't have) the lossless join prop erty

Answer 1

"STUDENTDEPT (sno, sname, saddr, age, dno, dname, dlocation) Functional dependencies: - sno rightarrow sname sno rightarrow saddr sno rightarrow age sno rightarrow dno dno rightarrow dname dno rightarrow dlocation Dependency Diagram: - STUDENTDEPT sNo sNAME SADDR AGE DNO DNAME DLOCATION (ii) Decomposing STUDENT into 3NF: - * STUDENTDEPT is in 1NF as no multivalve attributes * It is also in 2NF as there is only key SNO, no partial dependency on that * But it is not in 3NF as there exist transitive dependency i.e., Sno rightarrow dno dno rightarrow dname To, bring ""STUDENTEDEPT"" into 3NF we decompose STUDENTDEPT as: STUDENT SNO SNAME SADDR AGE DNO DEPT DNO DNAME DLOCATION \r\n (iii) To show this decomposition has or(don�t have) lossless join property Suppose two relations have data as followe STUDENT sno sname saddr age dno 101 venky vja 12 1 102 siri blr 15 2 103 Jeeven sc 20 1"

for any doubts or clarifications pls do comment..