[15 pts] Please normalize the following table which has insert, update and delet

Question

[15 pts] Please normalize the following table which has insert, update and delete hazards to a more hazard free 3NF set of related tables. a. Show steps for breaking up the table below into multiple tables in order to achieve 1. 3NF Explain in your own words what is meant by the "lossless join” principle for normalization. If we load the same information into your 3NF schema can we show that with a well- composed query, you can produce a report with equivalent information found in the original table? b. c. cNo CR76 CR76 CR56 CR56 CR56 cName John Kay John Kay Aline Stewart PG4 Aline StewartPG36 Aline Stewart PG16 pAddr 6 Lawrence Street 5 Novar Drive 6 Lawrence Street 2 Manor Road 5 Novar Drive finish Rent oNo oName pNo PG4 PG16 start 7/1/12 8/31/13 350 CO40 Tina Murphy 9/1/1450 CO93 Tony Shavw 9/1/11 6/10/12 350 C040 Tina Murphy 10/1/12 12/1/13 375 CO93 Tony Shaw 11/1/148/10/15 450 CO93 Tony Shaw

Explanation / Answer

1.

a. Normalization to 3NF

The table is in 1NF as their are are atomic attributes in table

2NF

Functional dependencies

cNo -> cName

pNo -> pAddr

pNo -> Rent

oNo -> oName

cNo,pNo -> start

cNo,pNo -> finish

Tables in 3NF

C(cNo,cName)

P(pNo,pAddr)

O(oNo,oName)

CP(cNo,pNo,Ono,start,finish)

where underlined are primary keys

b) Lossless Joins

The normalized table should be able to use joins to store the data of original table without loosing any data

c)

Select C.cNo,cName,P.pNo,pAddr,start,finish,Rent,O.oNo,oName from C inner join CP on C.cNo = CP.cNo inner join P on P.pNo = CP.pNo inner join O on CP.oNo = O.oNo;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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