Classroom Exercise: Normalization

1 / 3

# Classroom Exercise: Normalization - PowerPoint PPT Presentation

Classroom Exercise: Normalization. Consider the relation R(A,B,C,D) with these given FDs: AB -&gt; C C -&gt; D D -&gt; 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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Classroom Exercise: Normalization' - seth

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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.
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.
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