Question 4 (26 marks) This question is based on Section 5 of Block 2. It assesses your ability to understand the significance of normal forms. A building firm contracts out a lot of its work to individual tradesmen. The firm uses the following relation as part of its accounting procedures: Contract(Building, StartDate, ContractorCode, CustomerName, ContractorName, Trade, Rate PerHour, Hours Worked) A table representing the relation is shown in Figure 3. Contract Building StartDate Trade Rate PerHour Hours Worked Contractor Code Contractor Name Waters JLB2 14/2/10 C1 65 10 JLB2 14/2/10 C2 60 8 Sparks Mortar Plumber Electrician BrickLayer Plumber JLB2 14/2/10 20 Customer Name MsBig MsBig MsBig MrBigger Mr Bigger MsBiggest MsBiggest MsBig 20 TheHub 3/3/10 C1 Waters 65 15 TheHub 3/3/10 C4 Tight Plasterer 40 12 WHall 13/3/10 C5 Electrician 60 18 WHall 13/3/10 C4 Lightning Tight Mortar 40 15 Plasterer Bricklayer JLB2 30/3/10 C3 20 10 Electrician 60 1 TheHub 14/2/10 C2 Mr Bigger JLB2 10/6/10 C1 MsBig Figure 3 The relation Contract represented by a table Sparks Waters Plumber 65 15 Some functional dependencies (FD) on the relation are as follows: FD1: Building CustomerName FD2: ContractorCode ContractorName FD3: ContractorCode > Trade FD4: Trade > Rate PerHour FD5: (Building, StartDate, ContractorCode) Hours Worked (a) Using the data above, show how an amendment anomaly might arise. (4 marks) (b) From the list of functional dependencies above and using the property of transitivity, derive an additional FD with ContractorCode as determinant. (4 marks) (c) For each of the following pairs of projections, state whether it gives a non-loss decomposition of the relation Contract. Justify your answers. (i) project Contract over (Building, ContractorCode, ContractorName, Trade, Rate PerHour, Hours Worked) project Contract over (Building, StartDate, CustomerName) (ii) project Contract over (ContractorCode, Trade) project Contract over (Building, StartDate, ContractorCode, CustomerName, ContractorName, RatePerHour, Hours Worked) (8 marks) (d) Is Contract in second normal form (2NF)? Fully justify your answer. If it is not, then decompose Contract into an equivalent set of relations that are in 2NF. Justify that the resulting relations are in 2NF. For each relation in your 2NF decomposition, state whether it is in third normal form (3NF) and if it is not, then decompose it into an equivalent set of 3NF relations. (10 marks)