Page 1 of 3 Project Assignment 1 - Normalization Due 3/11 Order ID Order Date Cust ID Cust Cust FName LName Sales Rep ID Sales Sales Rep Rep Comm FName LName Class Comm Dept ID Rate Prod Quy Prod Priee Cust Phone SS 5- 1212 100 1/24/20 STOO John Smith 10 Alice Jones 0.1 10 2 $8.00 1 $65.00 1 2 $3.00 1 $4.25 817 SSS- 101 1/25/20A 120 Jane Adams 12 Greg Taylor B B 0.08 14 Tape 200 S1.75 100 S1.25 Trod Dept Prod Prod Cat Prod Cat Name ID Name ID Name Store RD B Sales 121 Hammer 1 Hand Tools Makita Power Power 228 Drill Tools # BD 480 Nails 4 Fasteners 1# BD 407 4 Fasteners Corp 3M Duet Sales 610 6 Miss 3M Masking 618 Tape Misc Acme Store Yard Measuring Sales 380 Stick Tools BD 121 Hammer 1 Hand Tools Schlage Door 535 Knob 5 Hardware Corp BD Sales 121 Hammer 1 Hand Tools Acme 123 Pry Rai 1 Hand Tools BD Store Power Power Sales229 Drill 2 Tools 3M Duet Tape 6 MING Aomo Yard Measuring 380 Stick 3 Tools 102 1/25/20J090 Tim Jones 14 Sara Day 0 0 10 2 $1.25 1 $7.00 S7.50 972- SSS- 103 1/26/20 B200 Ann Brown 8 Kay Price 0.05 14 50 $7.00 20 16.25 214 SSS- 1212 104 | 1/26/20 sioo John Smith 10 Alice Jones 0.1 10 1 1 $50.00 610) 200 $1.75 2 S1.25 535 5 Hardware 4 $7.50 1051/26/20 B200 072- 555 Ann Brown Kay Price Corp Sales 0.05 14 610 6 Misc 200 $1.75 123 1 Hand Tools 40 SS.00 106 1/27/20 G070 Kate Green 20 Bob Jackson B 0.08 10 More Sales 124 Door Knob 3M Duct Tape Acme Pry Bar Acme Hammer BID Power Drill Makita Power Drill 1 Hand Tools 1 S6.50 Power Tools 107 1/27/201090 Store les Tim Jones 14 Mara Day 7 0 0 10) 229 2 1 $50.00 108 1/27/20 SI 20 Nicole Web Sales Sims Micah Power Tools 22 Moore z 0 o 16 235 2 1 $65.00 Page 2 of 3 Additional Information: Home Improvement Warehouse is a store that sells home improvement products. All orders are picked up from the store. Each order must be sold to a specific customer. Each customer must have at least one order associated with him/her, and each customer is assigned a specific sales rep. Each sales rep belongs to a specific department, but it is possible to have a department with no employees. Each sales rep is assigned to a commission class and earns the same commission rate for each of his/her sales, regardless of the sale amount or the department to which he/she belongs. It is possible to have a commission class to which no employees belong. Product prices may vary from order-to-order. Each product belongs to a single category, but some categories may not have products that belong to them. PLEASE NOTE: This is a group project. Each group should consist of 3 5 students. Assignments must be printed, stapled, and have a cover sheet listing the full names of all group members. HAND-WRITTEN ASSIGNMENTS WILL NOT BE ACCEPTED. You may use Excel or PowerPoint, but it is recommended that you use Visio or another drawing/diagramming program to create the diagrams. Complete the following based on the information provided above and the data provided on the previous page: a. 1. FIRST NORMAL FORM (1NF) 15 points: Convert the table above to 1NF (double-click the table to access the data, eliminate repeating groups, then select and underline the appropriate PK). Choose an appropriate name for the table. b. Show the table structure format (list the table name followed by its attributes, in parentheses, with the primary key underlined). c. Create a dependency diagram for the table (primary key should be underlined). Page 1 of 3 Project Assignment 1 - Normalization Due 3/11 Order ID Order Date Cust ID Cust Cust FName LName Sales Rep ID Sales Sales Rep Rep Comm FName LName Class Comm Dept ID Rate Prod Quy Prod Priee Cust Phone SS 5- 1212 100 1/24/20 STOO John Smith 10 Alice Jones 0.1 10 2 $8.00 1 $65.00 1 2 $3.00 1 $4.25 817 SSS- 101 1/25/20A 120 Jane Adams 12 Greg Taylor B B 0.08 14 Tape 200 S1.75 100 S1.25 Trod Dept Prod Prod Cat Prod Cat Name ID Name ID Name Store RD B Sales 121 Hammer 1 Hand Tools Makita Power Power 228 Drill Tools # BD 480 Nails 4 Fasteners 1# BD 407 4 Fasteners Corp 3M Duet Sales 610 6 Miss 3M Masking 618 Tape Misc Acme Store Yard Measuring Sales 380 Stick Tools BD 121 Hammer 1 Hand Tools Schlage Door 535 Knob 5 Hardware Corp BD Sales 121 Hammer 1 Hand Tools Acme 123 Pry Rai 1 Hand Tools BD Store Power Power Sales229 Drill 2 Tools 3M Duet Tape 6 MING Aomo Yard Measuring 380 Stick 3 Tools 102 1/25/20J090 Tim Jones 14 Sara Day 0 0 10 2 $1.25 1 $7.00 S7.50 972- SSS- 103 1/26/20 B200 Ann Brown 8 Kay Price 0.05 14 50 $7.00 20 16.25 214 SSS- 1212 104 | 1/26/20 sioo John Smith 10 Alice Jones 0.1 10 1 1 $50.00 610) 200 $1.75 2 S1.25 535 5 Hardware 4 $7.50 1051/26/20 B200 072- 555 Ann Brown Kay Price Corp Sales 0.05 14 610 6 Misc 200 $1.75 123 1 Hand Tools 40 SS.00 106 1/27/20 G070 Kate Green 20 Bob Jackson B 0.08 10 More Sales 124 Door Knob 3M Duct Tape Acme Pry Bar Acme Hammer BID Power Drill Makita Power Drill 1 Hand Tools 1 S6.50 Power Tools 107 1/27/201090 Store les Tim Jones 14 Mara Day 7 0 0 10) 229 2 1 $50.00 108 1/27/20 SI 20 Nicole Web Sales Sims Micah Power Tools 22 Moore z 0 o 16 235 2 1 $65.00 Page 2 of 3 Additional Information: Home Improvement Warehouse is a store that sells home improvement products. All orders are picked up from the store. Each order must be sold to a specific customer. Each customer must have at least one order associated with him/her, and each customer is assigned a specific sales rep. Each sales rep belongs to a specific department, but it is possible to have a department with no employees. Each sales rep is assigned to a commission class and earns the same commission rate for each of his/her sales, regardless of the sale amount or the department to which he/she belongs. It is possible to have a commission class to which no employees belong. Product prices may vary from order-to-order. Each product belongs to a single category, but some categories may not have products that belong to them. PLEASE NOTE: This is a group project. Each group should consist of 3 5 students. Assignments must be printed, stapled, and have a cover sheet listing the full names of all group members. HAND-WRITTEN ASSIGNMENTS WILL NOT BE ACCEPTED. You may use Excel or PowerPoint, but it is recommended that you use Visio or another drawing/diagramming program to create the diagrams. Complete the following based on the information provided above and the data provided on the previous page: a. 1. FIRST NORMAL FORM (1NF) 15 points: Convert the table above to 1NF (double-click the table to access the data, eliminate repeating groups, then select and underline the appropriate PK). Choose an appropriate name for the table. b. Show the table structure format (list the table name followed by its attributes, in parentheses, with the primary key underlined). c. Create a dependency diagram for the table (primary key should be underlined)