Does anyone can help my AISHW, which is due on 10/26. Thank you so much

MIS/ACC 3353 - Homework 3 - Logical Relations & Normalization 1. A manufacturer wants to build a database to keep track of the machines they have in their factory. They need to keep track of what date the machine was purchased, the manufacturer, the service provider that maintains the machine, a maintenance history (what date, which service), and the products that the machine produces (many products require going through multiple machines in the manufacturing process). For each product produced, it is important to know all the machines that are required in its production and how much time is required at each machine. Finally, the company needs to know the names of the employees that work in the factory as well as which employees are trained to work on which machines (several employees are trained for each machine and many of the employees are cross-trained across multiple machines) and the date of their training. a. Create a set of dependencies (using dependency notation, e.g., DogID DogName) that describes the database the company would need to create based on the description above. b. Convert those dependencies into a set of relations in 3NF and include all foreign key constraints. 2. Transform the following ERD into a set of relations in 3NF; list all foreign key constraints. 3. You've been asked to help fix a legacy database where there might be some issues causing data integrity problems. For each table below (a) identify the normal form of the data shown and explain how you made your determination and (b) identify any other problems evident in the table that you will need to fix. In the table headings, * denotes primary key and denotes a foreign key in the legacy database. T_Bear BearID* 1 2 3 BearSubSpecies Ursus americanus altifrontalis Ursus americanus amblyceps Ursus americanus americanus BearRange BC, CA, ID, OR, WA AZ, CO, NM, TX, UT Canada, OR, WA, CA, ID, MT, CO, UT, WY, AZ, NM, TX, AR, MO, LA, AL, MSFL, GA, SC, NC, VA, WV, PA, NY, NJ, DE, VT, NH, MA, CT, OK, MI, WI, MN Ursus americanus californiensis Ursus americanus carlottae Ursus americanus cinnamomum Ursus americanus emmonsii Ursus americanus eremicus Ursus americanus floridanus Ursus americanus hamiltoni Ursus americanus kermodei Ursus americanus luteolus Ursus americanus machetes Ursus americanus perniger Ursus americanus pugnax Ursus americanus vancouveri California black bear Haida Gwaii black bear Cinnamon bear CA, OR AK, BC ID, MT, OR, UT, WA, WY Glacier Bear Mexican black bear Florida black bear Newfoundland black bear Spirit bear Louisiana black bear West Mexico black bear Kenai black bear Dall black bear Vancouver Island black bear AK N.M., Tx AL, FL, GA NL BC LA, MS, TX N. Mex. AK AK BC 5 6 7 BearCommon Olympic black bear New Mexico black bear Eastern black bear 10 11 12 13 14 15 17 18 19 20 a. b. T_Incident IncidentID* IBearID InciType Date 1 3 2 31-Dec-14 2 3 2 23-Feb-15 3 3 2 19-Mar-15 4 6 2 3-Apr-15 5 14 4 5-May-15 6 3 5 9-May-15 7 3 2 11-Jun-15 8 1 3 20-Jul-15 9 3 2 8-Sep-15 10 10 4 12-Nov-15 11 3 2 15-Nov-15 a. b. Location BC NC PA AK BC AB WY WA OK AK NJ Region Northwest Southeast Midatlantic Northwest Northwest Northwest Mountain Northwest Cerntal Northwest Midatlantic T_Diet BearID* FoodID* 3 3 1 3 2 3 6 1 2 14 14 20 1 5 7 5 2 2 4 2 8 8 5 9 5 9 Date March April April July September May October June May April June March June EndDate CaloriesM CaloriesF May 15000 13000 June 15000 13000 May 14000 12000 September 15000 13000 September 12000 11000 September 15000 13000 October 16000 12000 July 14000 12000 June 12000 11000 May 15000 12000 August 15000 12000 June 13000 13000 July 14000 12000 a. b. T_Food FoodID* Description Availability 1Grubs 5 2Salmon 3 4Trout 2 5Cedar Bark 6 6Grass 9 8Roots 7 9Honey 4 10Bees 4 a. b. 4. Write a set of relations for the database tables above. Write the relations in 3NF, resolving any additional issues evident in the tables. 5. The table below contains sample data from a database. In discussing this data with users, we find that part numbers, but not descriptions, uniquely identify parts and that vendor names uniquely identify vendors. Using this information, answer the questions below. (30 points) Part No. 1234 1234 5678 5678 5678 Description Logic Chip Logic Chip Memory Chip Memory Chip Memory Chip Vendor Name Fast Chips Smart Chips Fast Chips Quality Chips Smart Chips Address Cupertino Phoenix Cupertino Austin Phoenix Unit Cost 10.00 8.00 3.00 2.00 5.00 a. In what normal form is the table as it is currently presented? Why? b. Using the table above, give specific examples of deletion, insertion, and modification anomalies, based on the data. c. Create the set of relations needed to represent the data in 3NF. MIS/ACC 3353 - Homework 3 - Logical Relations & Normalization 1. A manufacturer wants to build a database to keep track of the machines they have in their factory. They need to keep track of what date the machine was purchased, the manufacturer, the service provider that maintains the machine, a maintenance history (what date, which service), and the products that the machine produces (many products require going through multiple machines in the manufacturing process). For each product produced, it is important to know all the machines that are required in its production and how much time is required at each machine. Finally, the company needs to know the names of the employees that work in the factory as well as which employees are trained to work on which machines (several employees are trained for each machine and many of the employees are cross-trained across multiple machines) and the date of their training. a. Create a set of dependencies (using dependency notation, e.g., DogID DogName) that describes the database the company would need to create based on the description above. b. Convert those dependencies into a set of relations in 3NF and include all foreign key constraints. 2. Transform the following ERD into a set of relations in 3NF; list all foreign key constraints. 3. You've been asked to help fix a legacy database where there might be some issues causing data integrity problems. For each table below (a) identify the normal form of the data shown and explain how you made your determination and (b) identify any other problems evident in the table that you will need to fix. In the table headings, * denotes primary key and denotes a foreign key in the legacy database. T_Bear BearID* 1 2 3 BearSubSpecies Ursus americanus altifrontalis Ursus americanus amblyceps Ursus americanus americanus BearRange BC, CA, ID, OR, WA AZ, CO, NM, TX, UT Canada, OR, WA, CA, ID, MT, CO, UT, WY, AZ, NM, TX, AR, MO, LA, AL, MSFL, GA, SC, NC, VA, WV, PA, NY, NJ, DE, VT, NH, MA, CT, OK, MI, WI, MN Ursus americanus californiensis Ursus americanus carlottae Ursus americanus cinnamomum Ursus americanus emmonsii Ursus americanus eremicus Ursus americanus floridanus Ursus americanus hamiltoni Ursus americanus kermodei Ursus americanus luteolus Ursus americanus machetes Ursus americanus perniger Ursus americanus pugnax Ursus americanus vancouveri California black bear Haida Gwaii black bear Cinnamon bear CA, OR AK, BC ID, MT, OR, UT, WA, WY Glacier Bear Mexican black bear Florida black bear Newfoundland black bear Spirit bear Louisiana black bear West Mexico black bear Kenai black bear Dall black bear Vancouver Island black bear AK N.M., Tx AL, FL, GA NL BC LA, MS, TX N. Mex. AK AK BC 5 6 7 BearCommon Olympic black bear New Mexico black bear Eastern black bear 10 11 12 13 14 15 17 18 19 20 a. b. T_Incident IncidentID* IBearID InciType Date 1 3 2 31-Dec-14 2 3 2 23-Feb-15 3 3 2 19-Mar-15 4 6 2 3-Apr-15 5 14 4 5-May-15 6 3 5 9-May-15 7 3 2 11-Jun-15 8 1 3 20-Jul-15 9 3 2 8-Sep-15 10 10 4 12-Nov-15 11 3 2 15-Nov-15 a. b. Location BC NC PA AK BC AB WY WA OK AK NJ Region Northwest Southeast Midatlantic Northwest Northwest Northwest Mountain Northwest Cerntal Northwest Midatlantic T_Diet BearID* FoodID* 3 3 1 3 2 3 6 1 2 14 14 20 1 5 7 5 2 2 4 2 8 8 5 9 5 9 Date March April April July September May October June May April June March June EndDate CaloriesM CaloriesF May 15000 13000 June 15000 13000 May 14000 12000 September 15000 13000 September 12000 11000 September 15000 13000 October 16000 12000 July 14000 12000 June 12000 11000 May 15000 12000 August 15000 12000 June 13000 13000 July 14000 12000 a. b. T_Food FoodID* Description Availability 1Grubs 5 2Salmon 3 4Trout 2 5Cedar Bark 6 6Grass 9 8Roots 7 9Honey 4 10Bees 4 a. b. 4. Write a set of relations for the database tables above. Write the relations in 3NF, resolving any additional issues evident in the tables. 5. The table below contains sample data from a database. In discussing this data with users, we find that part numbers, but not descriptions, uniquely identify parts and that vendor names uniquely identify vendors. Using this information, answer the questions below. (30 points) Part No. 1234 1234 5678 5678 5678 Description Logic Chip Logic Chip Memory Chip Memory Chip Memory Chip Vendor Name Fast Chips Smart Chips Fast Chips Quality Chips Smart Chips Address Cupertino Phoenix Cupertino Austin Phoenix Unit Cost 10.00 8.00 3.00 2.00 5.00 a. In what normal form is the table as it is currently presented? Why? b. Using the table above, give specific examples of deletion, insertion, and modification anomalies, based on the data. c. Create the set of relations needed to represent the data in 3NF