Question 4 20 Marks Question Based Section 5 Block 2 Assesses Understanding Advantages Nor Q43821739
Question 4 (20 marks)
This question is based on Section 5 of Block 2. It assesses yourunderstanding of the advantages of normalisation and how relationswhich are not in a particular normal form may be non-lossdecomposed into relations which satisfy that normal form.
The relation Vaccine is intended to record information aboutinfants and their vaccinations: when a particular infant had aparticular vaccination; where the vaccination took place; whoadministered the vaccine. The following gives the relationalheading:
Vaccine (VaccineCode, InfantId, Date, InfantName,InfantAddress,
MedicalCentreCode, MedicalCentreName, MedicalCentreAddress,NurseId,
NurseName)
Besides the functional dependencies with the primary key as theirdeterminant, Vaccine has the following non-trivial functionaldependencies:
FD1: InfantId InfantName
FD2: InfantId InfantAddress
FD3: MedicalCentreCode MedicalCentreName
FD4: MedicalCentreCode MedicalCentreAddress
FD5: NurseId NurseName
(a) Give examples to demonstrate that Vaccine does not preventinsertion or amendment anomalies. (4 marks)
(b) Justify the statement that Vaccine is not in second normal form(2NF). Write down headings of the relations in 2NF that will resultfrom a non-loss decomposition of Vaccine. Primary keys of theserelations should be underlined. (6 marks)
(c) Are each of the relations resulting from the decomposition inpart (b) in third normal form? Justify your answer.
For each relation that is not in third normal form, write down theheadings of the relations in third normal form that result from anon-loss decomposition of that relation. Again, primary keys shouldbe underlined. (10 marks
Expert Answer
Answer to Question 4 (20 marks) This question is based on Section 5 of Block 2. It assesses your understanding of the advantages o…
OR