Q4-1

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, RatePerHour, HoursWorked) A table representing the relation is shown in Figure 2. Contract Building StartDate Trade Rate Per Hour Hours Worked Contractor Code Customer Name Contractor Name JLB2 14/2/10 MsBig Waters Waters Plumber 65 10 JLB2 14/2/10 MsBig Sparks Electrician 60 8 JLB2 14/2/10 MsBig Mortar BrickLayer 20 20 TheHub 3/3/10 Waters Plumber 65 15 MrBigger MrBigger TheHub 3/3/10 Tight Plasterer 40 12 2 3 4 8 8 8 8 S WHall 13/3/10 MsBiggest Electrician 60 18 Lightning Tight WHall 13/3/10 MsBiggest Plasterer 40 15 JLB2 30/3/10 MsBig Mortar Bricklayer 20 10 Sparks Electrician 60 1 TheHub 14/2/10 MrBigger JLB2 10/6/10 C1 MsBig Figure 2 The relation Contract represented by a table 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 Per Hour FD5: (Building, StartDate, ContractorCode) H Hours Worked (a) Using the data above, show how an amendment anomaly might arise. (2 marks) (b) From the list of functional dependencies above and using the property of transitivity, derive an additional FD with ContractorCode as determinant. (2 marks)