|
The following table contains how many hours employees spent on projects during a particular period. The table is only in first normal form
because of all the partial dependencies
Columns: 10, Rows: 15
| EID | FNAME | LNAME | ADDRESS | CITY | ZIP | PID | QTR | PROJNAME | EHRS |
|---|
| 1 | 1 | Mary | Jacobs | P O Box 12022 | Honolulu | 96828 | 1000 | 2019 Q3 | Product Plan | 30.00 |
| 2 | 1 | Mary | Jacobs | P O Box 12022 | Honolulu | 96828 | 1200 | 2019 Q3 | Tax Preparation | 25.00 |
| 3 | 1 | Mary | Jacobs | P O Box 12022 | Honolulu | 96828 | 1300 | 2019 Q4 | Product Plan | 35.00 |
| 4 | 2 | Rosalie | Jackson | PO Box 1707 | Aiea | 96701 | 1200 | 2019 Q3 | Tax Preparation | 20.00 |
| 5 | 4 | Tom | Caruthers | PO Box 215 | Honolulu | 96810 | 1100 | 2019 Q3 | Portfolio Analysis | 40.00 |
| 6 | 4 | Tom | Caruthers | PO Box 215 | Honolulu | 96810 | 1200 | 2019 Q3 | Tax Preparation | 45.00 |
| 7 | 4 | Tom | Caruthers | PO Box 215 | Honolulu | 96810 | 1400 | 2019 Q4 | Portfolio Analysis | 15.00 |
| 8 | 5 | Heather | Jones | PO Box 2908 | Ewa Beach | 96706 | 1200 | 2019 Q3 | Tax Preparation | 40.00 |
| 9 | 5 | Heather | Jones | PO Box 2908 | Ewa Beach | 96706 | 1400 | 2019 Q4 | Portfolio Analysis | 10.00 |
| 10 | 6 | Mary | Abernathy | PO Box 2999 | Aiea | 96701 | 1100 | 2019 Q3 | Portfolio Analysis | 45.00 |
| 11 | 6 | Mary | Abernathy | PO Box 2999 | Aiea | 96701 | 1400 | 2019 Q4 | Portfolio Analysis | 27.00 |
| 12 | 8 | Tom | Jackson | PO Box 75457 | Kapolei | 96707 | 1000 | 2019 Q3 | Product Plan | 75.00 |
| 13 | 8 | Tom | Jackson | PO Box 75457 | Kapolei | 96707 | 1300 | 2019 Q4 | Product Plan | 80.00 |
| 14 | 10 | Ken | Numoto | PO Box 1793 | Honolulu | 96806 | 1000 | 2019 Q3 | Product Plan | 55.00 |
| 15 | 10 | Ken | Numoto | PO Box 1793 | Honolulu | 96806 | 1300 | 2019 Q4 | Product Plan | 50.00 |
Employees each have an ID (EID), first and last name (FNAME, LNAME), and an address, city, and zip code (ADDRESS, CITY, ZIP).
Projects have a project ID (PID), quarter (QTR) and name (PROJNAME).
Employees worked projects for a certain number of hours (EHRS).
Assignment:
- Create an InnoDB database on your server named JOBS exactly.
- Import the above table into JOBS
- Name the table "ORIGINAL"
- Set PROJNAME to varchar(100), ADDRESS to varchar(50), FNAME, LNAME, CITY, QTR to varchar(25), ZIP to varchar(10), EHRS to decimal(9,2) and EID, PID to int().
- Make sure you specify the primary key correctly
- Decompose the table into a set of BCNF tables in JOBS. Name your tables EMPLOYEE, PROJECT, EPHOURS and ZIPCITY
- Make sure you specify the foreign keys correctly.
- Make sure to populate the new tables from the original table.
When you are through, confirm you created your database correctly using the autograder.
|