GROUP2Assignment3.pdf

CSD 2206 Database Design & SQL 1 Assignment 3 (Weight: 3%) GROUP 2 Submission: ✓ Use Excel Sheet to work with tables ✓ The deadline/requirements of the submission will be provided by the instructor 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: Create an un-normalized tabular format (flat spreadsheet-like table) from the report format

CSD 2206 Database Design & SQL 2 project_id project _name employ ee_id employee_name job_class charge_ho ur hours_b illed 15 Evergre en 103 June E. Arbough Electrical Engineer $85.50 23.8 101 John G. News Database Designer $105.00 19.4 105 Alice K. Johnson * Database Designer $105.00 35.7 106 William Smithfield Programmer $35.75 12.6 102 David H. Senior Systems Analyst $96.75 23.8 18 Amber Wave 114 Annelise Jones Applicatio ns Designer $48.10 25.6 118 James J. Frommer General Support $18.36 45.3 104 Anne K. Ramoras * Systems Analyst $96.75 32.4 112 Darlene M. Smithson DDS Analyst $45.95 45.0 22 Rolling Tide 105 Alice K. Johnson Database Designer $105.00 65.7 104 Anne K. Ramoras Systems Analyst $96.75 48.4 113 Delbert K. Joenbrood Applicatio ns Designer $48.10 23.6 111 Geoff B. Wabash Clerical Support $26.87 22.0 106 William Smithfield Programmer $35.75 12.8 25 Starfli ght 107 Maria D. Alonzo Programmer $35.75 25.6 115 Travis B. Bawangi Systems Analyst $96.75 45.8 101 John G. News * Database Designer $105.00 56.3 114 Annelise Jones Applicatio ns Designer $48.10 33.1 108 Ralph B. Washington Systems Analyst $96.75 23.6 118 James J. Frommer General Support $18.36 30.5 112 Darlene M. Smithson DDS Analyst $45.95 41.4

CSD 2206 Database Design & SQL 3 1. Convert to 1NF: a. Each cell contains a single value b. Identify the primary key Answer - PROJECT ASSIGNMENT ( project_id , project_name, employee_id , employee_name, job_class, charge_hour, hours_billed ) 2. Convert to 2NF: a. Identify the partial dependencies Answer - Partial dependencies: project_id → project_name employee_id → employee_name , job_class , charge_hour b. Convert tables to 2NF (table form and relational schema) by eliminating partial dependencies

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help