Specifically questions #4 & #5 MIS 314 Business Database Systems Homework 2 You have to normalize database tables to 3rd Normal form for the data

image text in transcribed Specifically questions #4 & #5

MIS 314 Business Database Systems Homework 2 You have to normalize database tables to 3rd Normal form for the data presented in the next page. Data for this assignment presents sales transaction details. Deliverables include the following: 1. Dependency diagram of table after converting to 1NF indicating all the dependencies 2. Dependency diagram for each table after converting to 2NF indicating all the dependencies 3. Dependency diagrams of each table after converting to 3NF indicating all the dependencies 4. Create normalized fully attributed Entity Relationship diagram in MS Visio showing all the entities, their relationships, and cardinalities (Add additional attributes to each table as appropriate; Make sure not to end up de-normalizing the tables in the process). 5. Answer these specific questions: How will you maintain historical accuracy of product price? b. Did you identify any derived attribute and if yes then, how did you handle it? a. Note: You need to upload to Moodle before the due date and time the following: MS Word document showing items 1, 2, 3, and 5 above (You could use Word features such as Insert Table, and Insert Shapes tools to draw the dependency diagram - A sample drawing is shown in page 3) MS Visio file showing item 4 above Note: This is an individual assignment. While it is okay to consult another student to clarify any doubt, any sharing of files and modifying computer files created by others and representing that work as one's own should be strictly avoided. Please refer to course policies in the syllabus on academic conduct and unauthorized collaboration. Order ID Customer ID Product ID Product Name Tax Sales Person Sales Person ID Name 3 Levering Total Customer Name Alfreds Futterkiste State IL Qty. 40 11011 ALFKI 7.25% 20 7.25% 7.25% 568.43 461.18 733.59 10643 Unit Price $13.25 $21.50 $45.60 $18.00 $12.00 $34.00 6 Suyama ALFKI Alfreds. Futterkiste IL 15 21 405.41 7.25% 7.25% 2 25.74 3 109.40 10692 4 Peacock ALFKI Alfreds. Futterkiste IL 20 $43.90 $34.00 7.25% 7.25% 7.25% 941,66 182.33 5 10952 1 Davolio, ALFKI Alfreds Futterkiste IL 16 $25.00 $45.60 $23.25 2 7.25% 7.25% 6.00% 429.00 97.81 73.94 10625 3 Levering ANATR Ana Trujillo Emparedados. MO 3 5 $14.00 $34.00 10 6.00% 6.00% 6.00% 74.20 360.40 339.20 44.52 MO 10 10759 10926 3 Leverling 4 Peacock ANATR ANATR Ana Trujillo Emparedados Ana Trujillo Emparedados 58 Escargots de Bourgogne 71 Elatemysost 28 Rssle Sauerkraut 39 Chartreuse verte 46 Spegesild 60 Camembert Pierrot 63 Vegie-spread 60 Camembert Pierrot 6 Boysenberry Spread 28 Rssle Sauerkraut 14 Tofu 42 Hokkien Fried Mee 60 Camembert Plerrot 32 Mascarpone Fabioli. 11 Quesa Cabrales 13 Konbu 19 Teatime Chocolate Biscuits 72 Mozzarella di Giovanni 69 Gudbrandsdalsost 70 Outback Lager 11 Quesa Cabrales 11 Quesa Cabrales 40 Boston Crab Meat 57 Ravioli Angelo 59 Raclette Courdavault 17 Alice Mutton 34 Sasquatch Ale 53 Perth Pasties MO 2 10 6.00% 6.00% 63.60 7 10 $32.00 $21.00 $6.00 $9.20 $34.80 $28.80 $12.00 $21.00 $21.00 $18.40 68.26 368.88 30.53 10308 7 King ANATR Ana Trujillo Emparedados. MO 1 5 6.00% 6.00% 6.00% 6.00% 7.25% 7.25% 7.25% 10365 ANTON IL 24 3 Leverling 4 Peacock Antonio Moreno Taqueda Antonio Moreno Taqueda 63.60 540.54 1,126.13 197.34 10535 ANTON IL 50 10 5 15 7.25% 7.25% 7.25% 104.57 884.81 752.90 10573 7 King ANTON $19.50 $55.00 $39.00 $14.00 $32.80 Antonio Moreno Taqueria IL 18 40 7.25% 600.60 879.45 25 7.25% MIS 314 Business Database Systems Homework 2 You have to normalize database tables to 3rd Normal form for the data presented in the next page. Data for this assignment presents sales transaction details. Deliverables include the following: 1. Dependency diagram of table after converting to 1NF indicating all the dependencies 2. Dependency diagram for each table after converting to 2NF indicating all the dependencies 3. Dependency diagrams of each table after converting to 3NF indicating all the dependencies 4. Create normalized fully attributed Entity Relationship diagram in MS Visio showing all the entities, their relationships, and cardinalities (Add additional attributes to each table as appropriate; Make sure not to end up de-normalizing the tables in the process). 5. Answer these specific questions: How will you maintain historical accuracy of product price? b. Did you identify any derived attribute and if yes then, how did you handle it? a. Note: You need to upload to Moodle before the due date and time the following: MS Word document showing items 1, 2, 3, and 5 above (You could use Word features such as Insert Table, and Insert Shapes tools to draw the dependency diagram - A sample drawing is shown in page 3) MS Visio file showing item 4 above Note: This is an individual assignment. While it is okay to consult another student to clarify any doubt, any sharing of files and modifying computer files created by others and representing that work as one's own should be strictly avoided. Please refer to course policies in the syllabus on academic conduct and unauthorized collaboration. Order ID Customer ID Product ID Product Name Tax Sales Person Sales Person ID Name 3 Levering Total Customer Name Alfreds Futterkiste State IL Qty. 40 11011 ALFKI 7.25% 20 7.25% 7.25% 568.43 461.18 733.59 10643 Unit Price $13.25 $21.50 $45.60 $18.00 $12.00 $34.00 6 Suyama ALFKI Alfreds. Futterkiste IL 15 21 405.41 7.25% 7.25% 2 25.74 3 109.40 10692 4 Peacock ALFKI Alfreds. Futterkiste IL 20 $43.90 $34.00 7.25% 7.25% 7.25% 941,66 182.33 5 10952 1 Davolio, ALFKI Alfreds Futterkiste IL 16 $25.00 $45.60 $23.25 2 7.25% 7.25% 6.00% 429.00 97.81 73.94 10625 3 Levering ANATR Ana Trujillo Emparedados. MO 3 5 $14.00 $34.00 10 6.00% 6.00% 6.00% 74.20 360.40 339.20 44.52 MO 10 10759 10926 3 Leverling 4 Peacock ANATR ANATR Ana Trujillo Emparedados Ana Trujillo Emparedados 58 Escargots de Bourgogne 71 Elatemysost 28 Rssle Sauerkraut 39 Chartreuse verte 46 Spegesild 60 Camembert Pierrot 63 Vegie-spread 60 Camembert Pierrot 6 Boysenberry Spread 28 Rssle Sauerkraut 14 Tofu 42 Hokkien Fried Mee 60 Camembert Plerrot 32 Mascarpone Fabioli. 11 Quesa Cabrales 13 Konbu 19 Teatime Chocolate Biscuits 72 Mozzarella di Giovanni 69 Gudbrandsdalsost 70 Outback Lager 11 Quesa Cabrales 11 Quesa Cabrales 40 Boston Crab Meat 57 Ravioli Angelo 59 Raclette Courdavault 17 Alice Mutton 34 Sasquatch Ale 53 Perth Pasties MO 2 10 6.00% 6.00% 63.60 7 10 $32.00 $21.00 $6.00 $9.20 $34.80 $28.80 $12.00 $21.00 $21.00 $18.40 68.26 368.88 30.53 10308 7 King ANATR Ana Trujillo Emparedados. MO 1 5 6.00% 6.00% 6.00% 6.00% 7.25% 7.25% 7.25% 10365 ANTON IL 24 3 Leverling 4 Peacock Antonio Moreno Taqueda Antonio Moreno Taqueda 63.60 540.54 1,126.13 197.34 10535 ANTON IL 50 10 5 15 7.25% 7.25% 7.25% 104.57 884.81 752.90 10573 7 King ANTON $19.50 $55.00 $39.00 $14.00 $32.80 Antonio Moreno Taqueria IL 18 40 7.25% 600.60 879.45 25 7.25%