Bluewater Construction Company Consider the simplified database activities of a construction company that manages several building projects. Each project has its own project number, name, assigned employees, and so on. Each employee has an employee number, name, and job classification, such as engineer or computer technician. The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent on the employee's position. For example, one hour of computer technician time is billed at a different rate than one hour of engineer time. Periodically, a report is generated that contains the information displayed in Figure 1. Complete the following: 1. Create an un-normalized tabular format (flat spreadsheet-like table) from the report format 2. Convert to 1NF: a. Each cell contains a single value b. Identify the primary key 3. Convert to 2NF: a. Identify the partial dependencies

b. Convert tables to 2NF (table form and relational schema) by eliminating partial dependencies 4. Convert to 3NF: a. Identify the transitive dependencies b. Convert tables to 3NF (table form and relational schema) by eliminating transitive dependencies

PROJECT PROJECT NUMBER NAME 15 Evergreen HOURS BILLED EMPLOYEE NUMBER 103 101 105 106 102 EMPLOYEE NAME June E. Arbough John G. News Alice K. Johnson William Smithfield David Il. Senior CHARGE HOUR $ 85.50 $105.00 $105.00 $ 35.775 $ 96.75 35.7 12.6 23.8 18 Amber Wave 118 104 112 Annelise Jones James I. Frommer Anne K. Ramovas Darlene M. Smithson S 48.10 $ 18.36 $ 96.75 S45.95 25.6 45.3 32.4 45.0 22 Rolling Tide 105 104 113 65.7 JOB CLASS Elec. Engineer Database Designer Database Designer Programmer Systems Analyse Subtotal Applications Designer General Support Systems Analyst DSS Analyst Subtotal Database Designer Systems Analyst Applications Designer Clerical Support Programmer Subtotal Programmer Systems Analyu Database Designer Applications Designer System Analyx General Support DSS Analyst Subtotal Alice K. Johnson Anne K. Ramoras Delbert K. Joenbrood Geoff B. Wabash William Smithfield $105.00 S 96.75 $48.10 S 26.87 S 35.75 TOTAL CHARGE $ 2,034.90 $ 2,037.00 S 3,748.50 $ 450.45 S 2,302.65 S10,573.50 S 1,163.26 $ 831.79 $ 3,134.70 $ 2,067.75 $ 7,265.52 $ 6,998.50 $ 4,682.70 $ 1,135.16 $ 591.14 S 457.60 $13,765.10 915.20 $ 4,431.15 $ 5,911.50 $ 1,592.11 $ 2,283.30 55998 $ 1,902.33 $17.595.57 22.0 12.8 106 25 Starlight 107 115 101 25.6 45.8 56.3 Mana D. Alonzo Trava B. Bawang John G. News - Annelise Jones Ralph B. Washington James Frommer Darlene M. Smithson $ 96.75 $105.00 $ 48.10 $ 96.75 $ 18.36 S 45.95 108 118 30.5 Total $49,199.00