You’ve been asked to help fix a legacy database where there might be some issues causing data integrity problems. For each table below identify the normal form of the data shown and explain how you made your determination. If there are other problems evident in the table that you would need to fix to reach 3NF, note these as well. In the table headings, * denotes primary key and † denotes a foreign key in the legacy database. (20 points)
T_Bear
| BearID* | BearSubSpecies | BearCommon | BearRange |
| 1 | Ursus americanus altifrontalis | Olympic black bear | BC, CA, ID, OR, WA |
| 2 | Ursus americanus amblyceps | New Mexico black bear | AZ, CO, NM, TX, UT |
| 3 | Ursus americanus americanus | Eastern black bear | 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 |
| 5 | Ursus americanus californiensis | California black bear | CA, OR |
| 6 | Ursus americanus carlottae | Haida Gwaii black bear | AK, BC |
| 7 | Ursus americanus cinnamomum | Cinnamon bear | ID, MT, OR, UT, WA, WY |
| 10 | Ursus americanus emmonsii | Glacier Bear | AK |
| 11 | Ursus americanus eremicus | Mexican black bear | N.M., Tx |
| 12 | Ursus americanus floridanus | Florida black bear | AL, FL, GA |
| 13 | Ursus americanus hamiltoni | Newfoundland black bear | NL |
| 14 | Ursus americanus kermodei | Spirit bear | BC |
| 15 | Ursus americanus luteolus | Louisiana black bear | LA, MS, TX |
| 17 | Ursus americanus machetes | West Mexico black bear | N. Mex. |
| 18 | Ursus americanus perniger | Kenai black bear | AK |
| 19 | Ursus americanus pugnax | Dall black bear | AK |
| 20 | Ursus americanus vancouveri | Vancouver Island black bear | BC |
Normal Form:
Explanation:
T_Food
| FoodID* | Description | Availability |
| 1 | Grubs | 5 |
| 2 | Salmon | 3 |
| 4 | Trout | 2 |
| 5 | Cedar Bark | 6 |
| 6 | Grass | 9 |
| 8 | Roots | 7 |
| 9 | Honey | 4 |
| 10 | Bees | 4 |
Normal Form:
Explanation:
T_Incident
| IncidentID* | IBearID† | InciType† | Date | Location | Region |
| 1 | 3 | 2 | 31-Dec-14 | BC | Northwest |
| 2 | 3 | 2 | 23-Feb-15 | NC | Southeast |
| 3 | 3 | 2 | 19-Mar-15 | PA | Midatlantic |
| 4 | 6 | 2 | 3-Apr-15 | AK | Northwest |
| 5 | 14 | 4 | 5-May-15 | BC | Northwest |
| 6 | 3 | 5 | 9-May-15 | AB | Northwest |
| 7 | 3 | 2 | 11-Jun-15 | WY | Mountain |
| 8 | 1 | 3 | 20-Jul-15 | WA | Northwest |
| 9 | 3 | 2 | 8-Sep-15 | OK | Cerntal |
| 10 | 10 | 4 | 12-Nov-15 | AK | Northwest |
| 11 | 3 | 2 | 15-Nov-15 | NJ | Midatlantic |
Normal Form:
Explanation:
T_Diet
| BearID* | FoodID* | Date | EndDate | CaloriesM | CaloriesF |
| 3 | 5 | March | May | 15000 | 13000 |
| 3 | 7 | April | June | 15000 | 13000 |
| 1 | 5 | April | May | 14000 | 12000 |
| 3 | 2 | July | September | 15000 | 13000 |
| 2 | 2 | September | September | 12000 | 11000 |
| 3 | 4 | May | September | 15000 | 13000 |
| 6 | 2 | October | October | 16000 | 12000 |
| 1 | 8 | June | July | 14000 | 12000 |
| 2 | 8 | May | June | 12000 | 11000 |
| 14 | 5 | April | May | 15000 | 12000 |
| 14 | 9 | June | August | 15000 | 12000 |
| 20 | 5 | March | June | 13000 | 13000 |
| 1 | 9 | June | July | 14000 | 12000 |
Normal Form:
Explanation:
Reviews
There are no reviews yet.