Question

1 Approved Answer

Posted on Jul 25, 2024

Paul Bunyan is the owner of a wooden toy distributor named Blue Ox Distribution. He has decided to store his data in an electronic, relational

Paul Bunyan is the owner of a wooden toy distributor named Blue Ox Distribution. He has decided to store his data in an electronic, relational database in order to ensure that it is current and accurate. Currently, he stores his data in a simple table, as shown below

image text in transcribed

Examine the sample data from the table. Using Figure 7.27 from the text as a template, identify the functional determinants and their associated dependencies. (25 points)
Using Figure 7.30 from the text as a template, convert the unnormalized table into First Normal Form (1NF). Explain the conversion process. HINT: When done, you will still have just a single table. (25 points)
Using Figure 7.32 from the text as a template, convert the 1NF table from Step 2 into Second Normal Form (2NF). Explain the conversion process. HINT: When done, you will now have three tables. (25 points)
Using Figure 7.34 from the text as a template, convert the 2NF table from Step 3 into Third Normal Form (3NF). Explain the conversion process. HINT: When done, you will now have five tables. (25 points)

25 ORDER ORDER ITEM DESCRIPTION CATEGORY QTY ON NUMBER RETAIL QUOTED CUSTOMER CUSTOMER SALES REP SALES REP SALES REP NUMBER JOATE NUMBER HAND ORDERED PRICE PRICE NUMBER NAME NUMBER FIRST NAME LAST NAME 51608 12.Oct 15 C033 Wood Block Set 4 pieces TOY 36 15 $9.49 586.99 126 Toys Galore 15 Rafael Campos $1610 12-Oct-15 KL78 Pick Up Sticks GME 110 $10.95 $10.95 1334 The Everything Shop 45 Hul Tian TRAO Tic Tac Toe GME 25 10 $13.99 $13.99 51613 13-Oct-15 0151 Classic Railway Set TOY 12 IS $107.95 $104.95 2:36 Johnson's Department Store 30 Meran Gradey 151614 13-Oct-15 FD11 Rocking Horse TOY 8 1 $124.95 $124.95 260 Brookings Direct 30 Megan Gradey 151617 15-Oct-15 NL89 Wood Block Set (62 pieces) TOY 32 4 $119.75 $115.99 586 Almondton General Store 45 Hui Tian TWAS Fire Engine TOY 30 13 $118.95 $116.95 $1619 15 Oct 15 011 Rocking Horse TOY 8 2 $124.95 $121.95 126 Toys Galore as Rafael Campos 51623 15-Oct-15 DR67 Giant Star Brain Teaser PZL 24 $ $31.95 $29.95 1586 Almondton General Store 45 Hui Tian FH24 Puzzle Gift Set PZL 65 12 $38.95 $36.95 JK034 Pentominoes Brain Teaser IPL 160 10 1514.95 $13.10 151625 16-Oct-15 MIO3 Zauberkasten Brain Teaser PZL 45 18 $45.79 $45.79 25 Unique Gifts 45 Tian Salesperson Number Salesperson Name Salesperson Number Commission Percentage Salesperson Number Year of Hire Salesperson Number Department Number Salesperson Number Manager Name Product Number Product Name Product Number Unit Price Department Number Manager Name Salesperson Number, Product Number Quantity Salesperson Number Product Number Manager Nome Product Name Urit Price Quantity 137 137 137 186 186 186 186 204 204 361 361 361 19440 24013 26722 16386 19440 21765 24013 21765 26722 16336 21765 26722 Salesperson Name Baker Baker Baker Adams Adams Adams Adams Dickens Dickens Carlyle Carlyle Carlyle SALESPERSON/PRODUCT table Commission Year of Department Percentage Hire Number 10 1995 73 10 1995 73 10 1995 73 15 2001 59 15 2001 59 15 2001 59 15 2001 59 10 1998 73 10 1998 73 2001 73 2001 73 20 2001 Scott Scott Scott Lopez Lopez Lopez Lopez Scott Scott Scott Scott Scott Hammer Saw Pliers Wrench Hammer Drill Saw Drill Pliers Wrench Drill Pliers 17.50 26.25 11.50 12.95 17.50 32.99 26.25 32.99 11.50 12.95 32.99 11.50 473 170 688 1475 2529 1962 3071 809 734 3729 3110 2738 20 20 73 SALESPERSON table Salesperson Number Salesperson Name Commission Percentage Year of Hire Department Number Manager Name 137 186 204 361 Baker Adams Dickens Carlyle 10 15 10 20 1995 2001 1998 2001 73 59 73 73 Scott Lopez Scott Scott Product Number PRODUCT table Product Name Unit Price 16386 19440 21765 24013 26722 Wrench Hammer Drill Saw Pliers 12.95 17.50 32.99 26.25 11.50 QUANTITY table Salesperson Number Product Number Quantity 19440 137 137 137 186 186 186 186 204 204 361 361 361 24013 26722 16386 19440 21765 24013 21765 26722 16386 21765 26722 473 170 688 1745 2529 1962 3071 809 734 3729 3110 2738 SALESPERSON Toble Salesperson Number Salesperson Name Commission Percentage Year of Hire Department Number 10 73 59 15 137 186 204 361 Baker Adams Dickens Carlyle 1995 2001 1998 2001 73 10 20 73 DEPARTMENT table Department Number Manager Name 59 Lopez Scott 73 PRODUCT table Product Number Product Name Unit Price 12.95 17.50 16386 19440 21765 24013 26722 Wrench Hammer Drill Saw Pliers 32.99 26,25 11.50 21 QUANTITY Toble Salesperson Product Number Number Quantity bil 137 137 137 186 186 186 186 204 204 361 361 361 19440 24013 26722 16386 19440 21765 24013 21765 26722 16386 21765 26722 473 170 688 1745 2529 1962 3071 809 734 3729 3110 2738 25 ORDER ORDER ITEM DESCRIPTION CATEGORY QTY ON NUMBER RETAIL QUOTED CUSTOMER CUSTOMER SALES REP SALES REP SALES REP NUMBER JOATE NUMBER HAND ORDERED PRICE PRICE NUMBER NAME NUMBER FIRST NAME LAST NAME 51608 12.Oct 15 C033 Wood Block Set 4 pieces TOY 36 15 $9.49 586.99 126 Toys Galore 15 Rafael Campos $1610 12-Oct-15 KL78 Pick Up Sticks GME 110 $10.95 $10.95 1334 The Everything Shop 45 Hul Tian TRAO Tic Tac Toe GME 25 10 $13.99 $13.99 51613 13-Oct-15 0151 Classic Railway Set TOY 12 IS $107.95 $104.95 2:36 Johnson's Department Store 30 Meran Gradey 151614 13-Oct-15 FD11 Rocking Horse TOY 8 1 $124.95 $124.95 260 Brookings Direct 30 Megan Gradey 151617 15-Oct-15 NL89 Wood Block Set (62 pieces) TOY 32 4 $119.75 $115.99 586 Almondton General Store 45 Hui Tian TWAS Fire Engine TOY 30 13 $118.95 $116.95 $1619 15 Oct 15 011 Rocking Horse TOY 8 2 $124.95 $121.95 126 Toys Galore as Rafael Campos 51623 15-Oct-15 DR67 Giant Star Brain Teaser PZL 24 $ $31.95 $29.95 1586 Almondton General Store 45 Hui Tian FH24 Puzzle Gift Set PZL 65 12 $38.95 $36.95 JK034 Pentominoes Brain Teaser IPL 160 10 1514.95 $13.10 151625 16-Oct-15 MIO3 Zauberkasten Brain Teaser PZL 45 18 $45.79 $45.79 25 Unique Gifts 45 Tian Salesperson Number Salesperson Name Salesperson Number Commission Percentage Salesperson Number Year of Hire Salesperson Number Department Number Salesperson Number Manager Name Product Number Product Name Product Number Unit Price Department Number Manager Name Salesperson Number, Product Number Quantity Salesperson Number Product Number Manager Nome Product Name Urit Price Quantity 137 137 137 186 186 186 186 204 204 361 361 361 19440 24013 26722 16386 19440 21765 24013 21765 26722 16336 21765 26722 Salesperson Name Baker Baker Baker Adams Adams Adams Adams Dickens Dickens Carlyle Carlyle Carlyle SALESPERSON/PRODUCT table Commission Year of Department Percentage Hire Number 10 1995 73 10 1995 73 10 1995 73 15 2001 59 15 2001 59 15 2001 59 15 2001 59 10 1998 73 10 1998 73 2001 73 2001 73 20 2001 Scott Scott Scott Lopez Lopez Lopez Lopez Scott Scott Scott Scott Scott Hammer Saw Pliers Wrench Hammer Drill Saw Drill Pliers Wrench Drill Pliers 17.50 26.25 11.50 12.95 17.50 32.99 26.25 32.99 11.50 12.95 32.99 11.50 473 170 688 1475 2529 1962 3071 809 734 3729 3110 2738 20 20 73 SALESPERSON table Salesperson Number Salesperson Name Commission Percentage Year of Hire Department Number Manager Name 137 186 204 361 Baker Adams Dickens Carlyle 10 15 10 20 1995 2001 1998 2001 73 59 73 73 Scott Lopez Scott Scott Product Number PRODUCT table Product Name Unit Price 16386 19440 21765 24013 26722 Wrench Hammer Drill Saw Pliers 12.95 17.50 32.99 26.25 11.50 QUANTITY table Salesperson Number Product Number Quantity 19440 137 137 137 186 186 186 186 204 204 361 361 361 24013 26722 16386 19440 21765 24013 21765 26722 16386 21765 26722 473 170 688 1745 2529 1962 3071 809 734 3729 3110 2738 SALESPERSON Toble Salesperson Number Salesperson Name Commission Percentage Year of Hire Department Number 10 73 59 15 137 186 204 361 Baker Adams Dickens Carlyle 1995 2001 1998 2001 73 10 20 73 DEPARTMENT table Department Number Manager Name 59 Lopez Scott 73 PRODUCT table Product Number Product Name Unit Price 12.95 17.50 16386 19440 21765 24013 26722 Wrench Hammer Drill Saw Pliers 32.99 26,25 11.50 21 QUANTITY Toble Salesperson Product Number Number Quantity bil 137 137 137 186 186 186 186 204 204 361 361 361 19440 24013 26722 16386 19440 21765 24013 21765 26722 16386 21765 26722 473 170 688 1745 2529 1962 3071 809 734 3729 3110 2738