2. You\'ve been asked to help fix a legacy database where there might be some is

Question

2. 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 key and t denotes a foreign key in the legacy database. (20 points) primary T Bear BeariD BC, CA. ID, OR, WA Ursus anericanus anyeeps CO.NM. TX UT Canada, OR, WA, CA, ID, MT, co, UT, WY, NM, TX. AR, MO, LA AL. MSFL GA NC. VA, W PA, NY, NI DE. VT.NIL Calrfeenia Hack bear A. OR black bear black bear FL GA black Iear MS, TX West Mexico Nack bear black bear l Nack bear Iland black bear BC Normal Form Explanation T Food Salmon edar Bark 8Rooes Normal Form Explanation

Explanation / Answer

1)T_Bear:

Normal Form: Currently not in 1NF .

Explanation:Multiple values in a single row.To normalize it in 1NF form we have to write seperate row for each single value of BearRange corresponding to BearID.After the corrected table it will be automatically in 3NF also.

2)T_Food:

Normal Form:It is already in 3NF.

Explanation:It satisfies the condition of 1NF having single values in row.

It also satisfies condition of 2NF as no non-prime member is dependent on proper subset of candidate key.

It also satisfies condition of 3NF as there is no transititve dependency in the table.

3)T_Incident:

Normal Form:2NF

Explanation:Table is currently in 2NF not in 3NF as there is transititve dependency of non-prime attribute(Region) on super key(IncidentId,BearId,InciType).So to convert in into 3NF break table in 2 parts.One table containing (IncidentId,BearId,InciType,Date,Location) and other containing (Location,Region).

4)T-Diet:Currently in 1NF .

Explanation: Table is not in 2NF as {CaloriesM,CaloriesF->BearID}, BearID which is proper subset of candidiate key. So to make it in 2NF form we need to create 2 tables .One table will have {BearID,CaloriesM,CaloriesF} and other table willl have {BearID,FoodID,Date,EndDate}.

After making it into 2NF both tables will be in 3NF form also.

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.