1 / 3

Classroom Exercise: Normalization

Classroom Exercise: Normalization. Consider the relation R(A,B,C,D) with these given FDs: AB -> C C -> D D -> A Compute all nontrivial FDs that follow from these. Compute the key(s) for R. What are all the superkeys? What are all the BCNF violations? Decompose R into BCNF.

seth
Download Presentation

Classroom Exercise: Normalization

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Classroom Exercise: Normalization • Consider the relation R(A,B,C,D) with these given FDs: • AB -> C • C -> D • D -> A • Compute all nontrivial FDs that follow from these. • Compute the key(s) for R. • What are all the superkeys? • What are all the BCNF violations? • Decompose R into BCNF. • What are all the 3NF violations (before decomposing into BCNF)? • Decompose R into 3NF.

  2. Normalization Example • Consider relation R(name, SSN, BD, childName, childSSN, childBD, VIN, make) • Assume these FDs: • SSN -> name BD • childSSN -> childName childBD • VIN -> make • Therefore key is {SSN,childSSN,VIN} and all FDs violate BCNF. • Assume these MVDs: • SSN ->-> childSSN childName childBD • SSN ->-> VIN make • Thus all MVDs violate 4NF.

  3. Normalization Ex. cont'd • Decompose R using SSN ->-> childSSN childName childBD: • R1(SSN,childSSN,childName,childBD) • R2(SSN,name,BD,VIN,make) • Decompose R2 using SSN ->-> VIN make: • R2.1(SSN,VIN,make) • R2.2(SSN,name,BD) • Decompose R1 using childSSN -> childName childBD: • R1.1(childSSN,childName,childBD) • R1.2(SSN,childSSN) • Decompose R2.1 using VIN -> make: • R2.1.1(VIN,make) • R2.2.2(SSN,VIN) final set of relations

More Related