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:
