Department of Computer Sciences

Instructor Name: Dr$.$ MaramAjlan $/$ Ms$.$ Roohi Jan

Dated: ${14}^{th}$ May $2024$

Question $2$: Functional Dependencies and normalization $[3$ marks $]$

Given below is InstantCover relation

InstantCover $($InsuranceID$,$ ContractNo, HoursPerWeek, EmpName, HotelNo, HotelLocation$)$

The following are the functional dependencies that hold in the InstantCover relation

InsuranceID, ContractNo $$ HoursPerWeek

InsuranceID $$ EmpName

ContractNo $$ HotelNo,HotelLocation

HotelNo $$ HotelLocation

a$.[0\mathrm{.}5$ mark$]$ Draw the functional dependency diagram of the InstantCover relation.

b$.[0\mathrm{.}5$ mark$]$ Write one insertion and modification anomaly in InstantCover relation.

c$.[1\mathrm{.}5$ marks$]$ Determine the Normal Form of the relation InstantCover $($explain why$)$ and, if necessary, bring it into a set of $2NF,3NNF$ and BCNF relations.

d$.[0\mathrm{.}5$ marks$]$ Show the linking between foreign key and primary key in $3$NF relations.