CSCV 460 Assignment: 

Data Normalization 1. (30 Points) Figure 1: Two relation schemas suffering from update anomalies Give one insertion anomaly, one update anomaly, and one deletion anomaly that could happen in the EMP PROJ and EMP DEPT re- lations of Figure 1 and Figure 2. 1 Figure 2: Sample states for EMP DEPT and EMP PROJ 2 2. (30 Points) Consider the following relation for art works displayed in an art gallery: ARTWORK(Title, ArtistName, Type, ListPrice, ArtistAffil, Dealer) ArtistAffil refers to the affiliation of the artist. Suppose the following dependencies exist: Title -> Dealer, Type Type -> ListPrice ArtistName -> ArtistAffil (a) What is a candidate key of this relation? Explain your answer. (b) Apply normalization until your relations are in 3NF. State the reasons behind each decomposition. 3. (20 Points) Consider the following relation: R (Doctor#, Patient#, Date, Diagnosis, Treat code, Charge) In this relation, a tuple describes a visit of a patient to a doctor along with a treatment code and daily charge. Assume that diagnosis is determined (uniquely) for each patient by a doctor on a specific date. Assume that each treatment code has a fixed charge (regardless of patient). Hints: From the question’s text, we can infer the following functional dependencies: {Doctor#, Patient#, Date}->{Diagnosis, Treat code, Charge} {Treat code}->{Charge} Is this relation in 2NF? Justify your answer and decompose if neces- sary. Then argue whether further normalization to 3NF is necessary, and if so, perform it. 3 NOTES: Please turn in your completed work to D2L Assignments folder Data Normalization. 4