Q4-2

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. (c) For each of the following pairs of projections, state whether it gives anon-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, HoursWorked) (4 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)