SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

Relational Design Theory

vic avatar
vic
15 diapositives · 301 Views · Aug 31, 2014

This article explores Boyce-Codd Normal Form (BCNF) in relational database design through decomposition. It discusses the characteristics of “mega” relations and the properties of data that drive decompositions, ensuring the final set of relations satisfies BCNF without losing information or introducing anomalies. We provide examples, highlight functional and multivalued dependencies, and explain the BCNF decomposition algorithm. Understanding BCNF helps achieve good relational design by ensuring that anomalies are removed and the original relation can be logically reconstructed.

Relational Design Theory - slide 1
01 / 15Relational Design Theory
1 / 17

Télécharger la présentation

Relational Design Theory
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  1. Relational Design Theory Boyce-Codd Normal Form

  2. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • Final set of relations satisfies normal form • No anomalies, no lost information • Functional dependencies  Boyce-Codd Normal Form • Multivalued dependences  Fourth Normal Form

  3. BCNF Decomposition of a relational schema

  4. BCNF Decomposition Example #1 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority)

  5. BCNF Decomposition Example #2 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority)

  6. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • “Good” decompositions only • Into “good” relations

  7. BCNF Boyce-Codd Normal Form Relation R with FDs is in BCNF if: For eachᾹ B,Ᾱis a key

  8. BCNF BCNF? Example #1 Student(SSN,sName,address, HScode,HSname,HScity,GPA,priority) SSN  sName,address,GPA GPA  priority HScode  HSname,HScity

  9. BCNF BCNF? Example #2 Apply(SSN,cName,state,date,major) SSN,cName,state  date,major

  10. BCNF Relational design by decomposition • “Mega” relations + properties of the data • System decomposes based on properties • “Good” decompositions only • Into “good” relations

  11. BCNF BCNF decomposition algorithm Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A  B that violates BCNF Decompose R’ into R1(A, B) and R2(A, rest) Compute FDs for R1 and R2 Compute keys for R1and R2

  12. BCNF BCNF Decomposition Example Student(SSN,sName,address, HScode,HSname,HScity, GPA,priority) SSNsName,address,GPA GPApriority HScodeHSname,HScity

  13. BCNF BCNF decomposition algorithm Input: relation R + FDs for R Output: decomposition of R into BCNF relations with “lossless join” Compute keys for R Repeat until all relations are in BCNF: Pick any R’ with A  B that violates BCNF Decompose R’ into R1(A, B) and R2(A, rest) Compute FDs for R1 and R2 Compute keys for R1and R2

  14. BCNF Does BCNF guarantee a good decomposition? • Removes anomalies? • Can logically reconstruct original relation? Too few or too many tuples?

  15. BCNF Does BCNF guarantee a good decomposition? • Removes anomalies? • Can logically reconstruct original relation? Too few or too many tuples? • Some shortcomings discussed in later video