Looking for help of this question!! Thanks for any help!! You’ve been asked to h
ID: 3698381 • Letter: L
Question
Looking for help of this question!!
Thanks for any help!!
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:
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
Explanation / Answer
T_bear:
Normalization: The table is not in the first normal form in the firat place.
Explanation : Since first normal form requires that each attributes of the table should have atomic values and nonmultivalued attributes are allowed. But the column bearrangebin the table allow multivalued attribute, so this table doesn't follow the first normal form.
T_food
Normalization : 1NF
Explanation : Since the table doesn't have any attriattri which are multivalued it is in the 1NF. But it is not in the secsec normal form. The candidate key for the table are the foodid and description and the availability column is thus a non prime attribute. Ho we However, depending upon the entire candidate key, it is just dependent on a part of the candidate key(food I'd). BeacaBe of this partiap dependency, the table is not in 2 NF.
T_incident
Normalization: The table is in 2NF.
Explanation : As explained in the above case, there are no partial dependenicie. However, to reach upto 3NF,it has to get the transitive dependencies removed. In the table the candidate key is the incident_I'd. However the attribute region is a non prime attribute being dependent on another non prime attribute called location which is the transitive dependency. By removing it the table willwbe reduced to the 3NF.
T_diet
Normalization : The table is in the 3NF.
Explanation : As explained the transitive dependency above, there is no such case here. But the table is still not in BCNF. Because using the food_id, one can find the caloriesf and chaloriesm. Also, using bear I'd one can get the date and the end date. However, bear I'd and foof I'd are not the candidate keys in itself.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.