CMIS564: Database Design (Dr. Bordoloi) Homework Assignment: Normalization (10 Points) Your answers to the assignment must be word-processed. Handwritten answers will be accepted with a penalty of 10%. Q: Design a set of 3NF relations to support the generation of the sample report shown below. The report reflects the activities of a construction company that manages several building projects. Each project has its own (unique) project number, name, employees assigned to the project, and so on. Each employee has an (unique) employee number, name, and job classification such as engineer, computer technician, and so on. The company charges its clients by billing the hours spent on each contract. The hourly billing rate (Charge/hr) is dependent on the employee's position (e.g., an hour's worth of computer technician time is billed at a different rate than an hour's worth of engineer time). Requirements: a. Indicate the primary key and the foreign key(s) of each relation clearly in your answer. b. Non-indication or incorrect indication of PKs and FKs will result in a loss of 1 point for each PK and each FK in each table. c. Do NOT make up any fields of your own to suit your answer. Sample Report: PROJ PROJECT EMPLOYEE EMPLOYEE JOB CHARGE/ HOURS TOTAL NUM NAME NUMBER NAME CLASS HOUR BILLED CHARGE Hurricane 101 John News Elect. Eng. 65 13 845 102 David Senior Comm. Tech 60 16 960 104 Anne Ramoras Comm. Tech. 60 19 1, 104 Subtotals 2,945 2 Coast 101 John News Elect. Eng. 65 15 975 103 June Arbough Biol. Eng. 55 17 935 Subtotals 1, 190 3 Satellite 104 Anne Ramoras Comm. Tech. 50 18 1,080 102 David Senior Comm. Tech. 60 14 840 Subtotals 1,920 Total 6, 775