Post on 19-Jan-2015
description
functionele afhankelijkheden
en normalisatie – deel 1
Katrien Verbert
katrien.verbert@cs.kuleuven.be
inhoud
• informele richtlijnen voor het ontwerp van een gegevensbank
• een voorbeeld van normalisatie
• functionele afhankelijkheden
• normaalvormen (1e, 2e, 3e, Boyce-Codd)
ontwerp van relationele
gegevensbanken
• verschillende benaderingen:
1. via hoogniveau modellering:
• werkelijkheid analyseren
• ER-schema bouwen
• ER-schema omzetten naar relationeel gegevensbankschema
2. meteen een relationeel gegevensschema bouwen
• informele richtlijnen
• technieken, algoritmes
– belangrijk begrip: normaliseren
• belangrijke punten:
– minimale redundantie, maximale performantie, ...
waarom minder goed ontwerp?
informele richtlijnen
1. Ontwerp een relatieschema zo dat zijn betekenis
gemakkelijk verklaard kan worden
– betekenis van attributen moet duidelijk zijn
– betekenis van relatie moet duidelijk zijn
informele richtlijnen
2. Ontwerp een relatieschema zo dat redundantie vermeden wordt
en geen toevoeg-, weglaat- of wijziging-anomalieën voorkomen
redundante informatie
problemen bij toevoegen
( "toevoeg-anomalieën”)
• nieuwe werknemer toevoegen
– nieuwe gegevens moeten consistent zijn met bestaande
departement gegevens
– null waarden als geen dept info voorhanden is
• hoe een departement toevoegen dat nog geen
werknemers heeft?
EName SSN Bdate Address Dnumber Dname Dmgr
EMP_DEPT
weglaat-anomalieën
• laatste werknemer uit een departement weg alle info
over dat departement ook weg!
EName SSN Bdate Address Dnumber Dname Dmgr
EMP_DEPT
wijziging-anomalieën
• bv. manager van dept 5 wijzigen wijziging moet in
alle tupels van EMP_DEPT gebeuren, anders wordt
gegevensbank inconsistent
EName SSN Bdate Address Dnumber Dname Dmgr
EMP_DEPT
informele richtlijnen
3. Vermijd zoveel mogelijk attributen waarvan de waarden
nul kunnen zijn
– nul = niet van toepassing / onbekend / nog niet geregistreerd
informele richtlijnen
4. Ontwerp relatieschema's zo dat ze na een join met = op
attributen die primaire of verwijssleutels zijn, geen
onechte tupels opleveren
slechte voorstelling van de EMP_PROJ relatie: door twee relaties EMP_LOCS en EMP_PROJ1
resultaat van natuurlijke join op de tuples boven de stippellijn: er worden onechte tupels ingevoerd
voorbeeld van normalisatie
• "Kaartenbak"-voorbeeld
– context: "open universiteit"
• Kaarten met gegevens over:
– StudentNr, Naam, Cursus
– Cursus =
• Code, Titel, InschrijvingsDatum, StudiecentrumCode, StudiecentrumNaam
• een student kan meerdere cursussen volgen
– Cursus is een meerwaardig samengesteld attribuut
• niet toegelaten in relationeel model!
• weghalen van meerwaardige / samengestelde attributen
"eerste normaalvorm"
• Bekijk gegevens: welke kunnen aanleiding geven tot
anomalieën?
– waar is er redundantie?
– m.a.w. welke gegevens volgen uit andere?
• Identificatie van afhankelijkheden
• Herwerken van relatieschema's
– 2e en 3e normaalvorm
Zij gegeven een relatieschema R = {A1, A2, ..., An} en attributenverzamelingen X en Y. We zeggen dat Y functioneel afhankelijk is van X, genoteerd X Y, als en slechts als 1. X 2. mogelijke extensies r van R: t1, t2 r en t1[X] = t2[X] t1[Y] = t2[Y]
Merk op:
"voor alle mogelijke extensies" (= eigenschap van relatieSCHEMA)
hangt af van betekenis van de relatie!
uit die betekenis volgen nl. bepaalde restricties waaraan de extensies moeten voldoen
functionele afhankelijkheden
• Definitie
doel van functionele afhankelijkheden
• expliciet beschrijven van bepaalde restricties waaraan
de relatie steeds moet voldoen
• vb: relatie EMP_PROJ
– SSN ENAME
– PNUMBER { PNAME, PLOCATION }
– { SSN,PNUMBER } HOURS
• notatie in schema's: via pijlen, bv:
SSN PNUMBER HOURS ENAME PNAME PLOCATION
TEXT COURSE ? kan niet besloten worden uit de extensie
maar niet TEACHER COURSE
want een tegenvoorbeeld is in de extensie aanwezig
afleidingsregels voor functionele
afhankelijkheden
• een verzameling functionele afhankelijkheden van R
kan nieuwe functionele afhankelijkheden impliceren
– bv. A B en B C impliceert A C
– Notatie: { A B, B C } |= A C
• afleidingsregels voor functionele afhankelijkheden
– laten toe alle functionele afhankelijkheden te vinden die door
een verzameling F van functionele afhankelijkheden
geïmpliceerd worden
• sluiting van F, genoteerd F+,
– is de verzameling van alle f.a. die door F geïmpliceerd worden
voorbeeld
• F={Ssn{Ename, Bdate, Address, Dnumber},
Dnumber {Dname, Dmgr_ssn}}
• Functionele afhankelijkheden afgeleid uit F
– Ssn {Dname, Dmgr_ssn}
– Ssn Ssn
– Dnumber Dname
basis afleidingsregels
• FD1. reflexiviteitsregel: Y X X Y
• FD2. uitbreidingsregel: { X Y } |= XZ YZ
• FD3. transitiviteitsregel: { X Y, Y Z } |= X Z
basisregels zijn correct en volledig
• correct (of "gezond", Eng. "sound")
– alle afhankelijkheden die afgeleid kunnen worden, worden ook
werkelijk geïmpliceerd
• volledig
– alle afhankelijkheden die geïmpliceerd worden, kunnen ook met
de regels afgeleid worden
Bewijs FD2: X Y betekent: t1, t2 : t1[X] = t2[X] t1[Y] = t2[Y]. Voor eender welke 2 tupels t1, t2 waarvoor geldt t1[XZ] = t2[XZ], geldt: t1[XZ] = t2[XZ] t1[X] = t2[X] en t1[Z] = t2[Z] t1[Y] = t2[Y] en t1[Z] = t2[Z] (uit X Y) t1[YZ] = t2[YZ] We hebben dus t1, t2 : t1[XZ] = t2[XZ] t1[YZ] = t2[YZ] wat equivalent is met XZ YZ.
bewijs van correctheid
– FD1 en FD3: bewijs als oefening
– FD2: { X Y } |= XZ YZ
W
X Y Z
X W
Z Uit
volgt:
extra afleidingsregels
• FD4: decompositieregel
{ X YZ } |= X Y
• FD5: verenigingsregel
{ X Y, X Z } |= { X YZ }
• FD6: pseudo-transitiviteitsregel
{ X Y, WY Z } |= WX Z
bewijs FD4-6
• ofwel via definitie, ofwel via FD1-3 (die immers correct
en volledig zijn)
• vb: bewijs FD4: { X YZ } |= { X Y }
– 1: X YZ (geg)
– 2: uit FD1 volgt YZ Y
– uit (1) en (2) via FD3: X Y
hoe alle functionele afhankelijkheden
in een relatieschema vinden?
• uit betekenis van relaties en attributen: verzameling
afhankelijkheden F
– onvermijdelijk: afhankelijkheden volgen uit betekenis
• via afleidingsregels worden alle functionele
afhankelijkheden bekomen die uit F volgen: F+
• hoe:
– voor elke verzameling attributen X, die links in een funct.
afhankelijkheid van F voorkomt, bepaal XF+
• XF+ ("sluiting van X t.o.v. F"):
– = verzameling van alle attributen die funct. bepaald zijn door X
• algoritme:
– voor elke X Y F, bepaal XF+ met de afleidingsregels
XF+ := X
herhaal oldXF
+ := XF+
voor elke Y Z in F : als Y XF
+ dan XF+ := XF
+ Z tot (oldXF
+ = XF+)
XF+ soms genoteerd X+ (indien geen verwarring m.b.t. F
mogelijk is)
• Algoritme: bepaal XF+, sluiting van X onder F
Voorbeeld: EMP_PROJ
• F = { SSN ENAME,
PNUMBER { PNAME, PLOCATION },
{ SSN, PNUMBER } HOURS }
• Sluitingen t.o.v. F zijn dan:
– { SSN }+ = { SSN, ENAME }
– { PNUMBER }+ = { PNUMBER, PNAME, PLOCATION }
– { SSN, PNUMBER }+ = { SSN, PNUMBER, ENAME, PNAME, LOCATION, HOURS}
oefening
• G={Ssn {Ename, Bdate, Address, Dnumber},
Dnumber {Dname, Dmgr_ssn}}
• {Ssn}+?
• {Dnumber}+? XF
+ := X herhaal oldXF
+ := XF+
voor elke Y Z in F : als Y XF
+ dan XF+ := XF
+ Z tot (oldXF
+ = XF+)
oplossing
• G={Ssn {Ename, Bdate, Address, Dnumber},
Dnumber {Dname, Dmgr_ssn}}
• {Ssn}+={Ssn, Ename, Bdate, Address, Dnumber,
Dname, Dmgr_ssn}
• {Dnumber}+={Dnumber, Dname, Dmgr_ssn}
overdekking, equivalentie
• Zij E en F verzamelingen f.a. van R
• E wordt overdekt door F a.s.a. E F+
– alle f.a. in E volgen uit F via afleidingsregels
– automatisch ook E+ F+
• E en F zijn equivalent a.s.a. E+ = F+
• Nagaan of F E overdekt:
– voor alle X Y in E: bepaal XF+ en controleer of Y XF
+
oefening
• Relatie R{A,B,C,D,E,F,G}
• Met afhankelijkheden
– A B
– BC DE
– AEF G
• Sluiting {A,C,F}+?
• Kan ACFDF afgeleid worden uit deze verzameling?
oefening
• Overdekt F de verzameling E?
E = {A BC, D AE}
F = {A B, AB C, D AC, D E}
Nagaan of F E overdekt:
voor alle X Y in E: bepaal XF+ en controleer of Y XF
+
oplossing
• Overdekt F de verzameling E?
E = {A BC, D AE}
F = {A B, AB C, D AC, D E}
voor alle X Y in E: bepaal XF+ en controleer of Y XF
+
• A BC
– AF+={A, B, C}
– BC {A, B, C}
• D AE
– DF+={D, A, C, E, B}
– AE {D, A, C, E, B}
oefening
• Overdekt E de verzameling F?
E = {A BC, D AE}
F = {A B, AB C, D AC, D E}
voor alle X Y in F: bepaal XE+ en controleer of Y XE
+
• A B
– A+E={A, B, C}
– B {A, B, C}
• AB C
– AB+E={A, B, C}
– C {A, B, C}
• D AC
– D+E= {D, A, E, B, C}
– AC {D, A, E, B, C}
• D E
– D+E={D, E, A, C, B}
– E {D, E, A, C, B}
minimale verzameling functionele
afhankelijkheden
• F is een minimale verzameling a.s.a. er geen
equivalente verzameling G is te vormen door het
weglaten van
– een afhankelijkheid uit F
– of een attribuut uit de rechterkant van een afhankelijkheid in F
– of een attribuut uit de linkerkant van een afhankelijkheid in F
• E is een minimale overdekking van F a.s.a. E F
overdekt en minimaal is
stelling
• een verzameling f.a. F is minimaal als
– rechterlid van elke afhankelijkheid een singleton is
• geen attribuut kan weggelaten worden uit rechterlid
– en geen enkele strikte deelverzameling van F equivalent is met
F
• geen afhankelijkheid kan weggelaten worden
– en voor geen enkele X A van F bestaat er een echte
deelverzameling Z X waarvoor F \ { X A } { Z A }
equivalent is met F
• geen attr. kan weggelaten worden uit linkerlid
G := F voor elke X A1A2...An in G (met n > 1) : G := G \ {X A1A2...An} {X A1, ..., X An} voor elke X A in G : voor elke B X : X' := X \ {B} G' := G \ {X A} {X' A} als B X’G’
+ dan G := G' voor elke X A in G : G' := G \ {X A} als A XG'
+ dan G := G’ {G is een minimale overdekking van F}
– Algoritme voor berekening van een minimale overdekking G
van F:
voorbeeld
• E: {BA, DA, ABD}
• Stap 1 = OK
Voorbeeld: stap 2
• E: {BA, DA, ABD}
• ABD : Vervangen door BD?
– F={BA, DA, BD}
– A B+F?
– B+F={B, A, D} vervanging OK!
• E: {BA, DA, BD}
voorbeeld: stap 3
• E: {BA, DA, BD}
• BA : Weglaten?
– E’= {DA, BD}
– A B+E’?
– B+E’={B, D, A} weglating OK!
• E: {DA, BD}
oefening
• Afhankelijkheden
– {M, S} {P, C}
– {S} {Y}
– {M} {N}
– {N,Y} {P}
• Bereken minimale verzameling
oplossing stap 1
• Afhankelijkheden
– {M, S} {C}
– {M, S} {P}
– {S} {Y}
– {M} {N}
– {N,Y} {P}
• Welke afhankelijkheden kunnen weggelaten worden?
oplossing
• minimale verzameling afhankelijkheden G
– {M, S} {C}
– {S} {Y}
– {M} {N}
– {N,Y} {P}
• Want {M,S} P afleidbaar uit G
– {M,S}G+={M, S, C, Y, N, P}
– P {M,S}G+
normaalvormen
• afhankelijkheden in een relatieschema kunnen oorzaak
zijn van
– redundantie van gegevens in een extensie
– problemen bij het onderhoud
• daarom:
– indeling van relatieschema’s in groepen op grond van soorten
afhankelijkheden: NORMAALVORMEN
– hoe hoger de normaalvorm, des te minder afhankelijkheden
zich kunnen voordoen
normaalvormen
• een normaalvorm legt bepaalde eisen op aan relaties
• normalisatie
= relatie in een bepaalde normaalvorm brengen
methode: decompositie
• relatieschema's die niet voldoen aan de eisen opdelen in kleinere
relatieschema's die wel voldoen
• een goede decompositie van een schema
– bevat dezelfde informatie
– bevat geen onnodige relaties
– is efficiënt te onderhouden
Def: Een verzameling relatieschema's (SR) = {SR1 , SR2 , ..., SRk} (k > 1) is een decompositie van SR a.s.a. UR = UR1 UR2 ... URk .
• Notatie: voor een relatie R noteren we
– het relatieschema : SR
– de attributenverzameling van SR : UR
– een verz. afhankelijkheden van SR : FR
– SR = < UR, FR >
• Definitie: decompositie
voorbeelden
• SR met UR = ABCD
– 1(SR) = {SR1 , SR2 } met UR1 = ABC, UR2 = BCD
– 2(SR) = {SR1 , SR2 } met UR1 = AB, UR2 = CD
– 3(SR) = {SR1, SR2, SR3} met UR1 = AB, UR2 = BC, UR3 = AD
– 4(SR) = {SR1, SR2, SR3} met UR1=AB, UR2=BCD, UR3=ABC
• 2:
– disjuncte attribuutverzamelingen verband tussen bepaalde
gegevens gaat verloren
• 4:
– bevat redundante gegevens: UR1 UR3
• afhankelijkheden in een relatieschema kunnen oorzaak
zijn van
– redundantie
– problemen bij onderhoud (anomalieën)
• als er dergelijke afhankelijkheden zijn in een schema:
– via decompositie verwijderen
• normaalvormen leggen afwezigheid van
afhankelijkheden in relatieschema's op
• normaliseren
= voor elke relatie in de gegevensbank een
decompositie bepalen zo dat alle resulterende relaties
in een bepaalde normaalvorm zijn
• Opeenvolgende normaalvormen:
– 1NF: eerste normaalvorm
– 2NF: tweede normaalvorm
– 3NF: derde normaalvorm
– BCNF: Boyce-Codd normaalvorm
– 4NF: vierde normaalvorm
– 5NF: vijfde normaalvorm
– DKNF: domain-key normaalvorm
• Elke NF speciaal geval van vorige
Def: K is een supersleutel voor een relatie R met schema SR = <U,F> a.s.a. K U en K U F+
Def: K is een sleutel of kandidaatsleutel voor een relatie R met schema SR = <U,F> a.s.a. K een supersleutel is voor R en geen enkele echte deelverzameling van K een supersleutel is voor R
Def: een attribuut A is een sleutelattribuut voor een relatie R met schema SR = <U,F> a.s.a.
er bestaat een sleutel K voor R waarvoor geldt A K
Een supersleutel determineert elk attribuut in R;
of nog: voor een supersleutel K geldt KF+ = U
als aanloop tot normaalvormen: enkele
definities
• primaire sleutel
– = één uit de kandidaatsleutels gekozen sleutel
– attributen in primaire sleutel mogen nooit nul zijn
• alternatieve sleutel
– = een kandidaatsleutel die niet als primaire sleutel gekozen is
voorbeeld
• Supersleutel EMPLOYEE
– {Ssn, Ename}, {Ssn, Ename, Bdate}
– Elke verzameling van attributen die Ssn bevat=supersleutel
• Sleutel
– {Ssn}
nulde normaalvorm
Een relatieschema SR = <U,F> is in de nulde normaalvorm : er zijn geen voorwaarden opgelegd aan de attributen (m.a.w. ze mogen samengesteld of meerwaardig zijn)
Def: Een relatieschema SR = <U,F> is in de eerste normaalvorm a.s.a. het domein van elk attribuut van U enkelvoudig is.
eerste normaalvorm
– m.a.w. geen verzamelingen of tupels als waarde van een attribuut
toegelaten
– relatie in 1NF brengen :
• samengesteld attribuut in meerdere attributen opsplitsen
• voor meerwaardig attribuut meerdere tupels voorzien of nieuwe relatie creëren met
zelfde sleutel
– nieuwe gegevensbank bevat zelfde informatie als oospronkelijke
– Merk op: er is geen enkele eis gesteld aan de verzameling functionele
afhankelijkheden F
DLOCATIONS: meerwaardig attribuut: niet in 1 NF daarom: ofwel - splitsen in 2 relaties (zoals in voorbeeld vroeger reeds is gebeurd) - een tupel voor elke locatie: primaire sleutel vergroot en redundantie neemt toe: zie (c)
samengestelde attributen die zelf meerwaardig zijn (= geneste relaties) : zijn ook niet toegelaten normalisatie:
geneste attributen naar
een nieuwe relatie (+ primaire sleutel)
Def: Een relatieschema SR = <U,F> is in de tweede normaalvorm a.s.a. voor geen enkel niet-sleutelattribuut A van U geldt dat A partieel functioneel afhankelijk is van een kandidaatsleutel van R.
Def: Als X Y, dan zeggen we dat Y partieel functioneel afhankelijk is van X indien er een Z X bestaat zodat Z Y; anders noemen we Y volledig functioneel afhankelijk van X.
m.a.w.: voor elk niet-sleutel-attribuut moet de hele primaire sleutel nodig zijn om het te determineren
tweede normaalvorm
• Voornamelijk van historisch belang, als aanloop tot 3NF
voorbeeld 2
• sleutel is StudNr,CursCode
• CursNaam echter volledig bepaald door CursCode
StudNr CursCode CursNaam InschrDatum SCCode SCNaam
methode 1NF 2NF
• Gegeven SR1 = < UR1, FR1 > in 1NF
• Voor elk attribuut A dat partieel functioneel afhankelijk is
van een kandidaatsleutel K:
– zoek een subset K' in K waarvan A volledig functioneel
afhankelijk is
– Elimineer A uit UR1
– Maak een nieuw relatieschema SR2 met attributenverzameling
UR2 = K' A
voorbeeld
StudNr CursCode CursNaam InschrDatum SCCode SCNaam
StudNr CursCode InschrDatum SCCode SCNaam
CursCode CursNaam
oefening
Car Date_Sold Salesman Commission Discount
CAR_SALE
2NF?
DERDE NORMAALVORM
transitieve functionele afhankelijkheden
Def: Een functionele afhankelijkheid X->Y is een transitieve functionele afhankelijkheid a.s.a. er een Z bestaat zodat volgende 3 voorwaarden voldaan zijn: 1. Z is volledig en niet-triviaal functioneel afhankelijk van X 2. Z is geen (echte of onechte) deelverzameling van een kandidaatsleutel 3. Y is niet-triviaal functioneel afhankelijk van Z We zeggen dat Y transitief functioneel afhankelijk is van X via Z.
Def: Een functionele afhankelijkheid X Y is een triviale functionele afhankelijkheid a.s.a. Y X.
X Z Y
voorbeeld
• SR < UR, FR >
met UR = A B C en FR = { A B, B C }
met een mogelijke extensie:
A B C
A1 b1 c1
a2 b1 c1
a3 b2 c2
• A C is een transitieve functionele afhankelijkheid
• A is een kandidaatsleutel van R
• C is transitief afhankelijk van de kandidaatsleutel A via B
derde normaalvorm
Alternatieve definitie:
Def: Een 2NF-relatieschema SR = <U,F> is in de derde normaalvorm a.s.a. voor geen enkel niet-sleutelattribuut A van U geldt: A is transitief functioneel afhankelijk van een of andere kandidaatsleutel van de relatie R.
Def: Een 1NF-relatieschema SR = < U,F> is in de derde normaalvorm a.s.a. voor elke niet-triviale functionele afhankelijkheid X A van F+ geldt: A is een sleutelattribuut of X is een supersleutel voor R.
voorbeeld
• SCNaam is volledig bepaald door SCCode
StudNr CursCode InschrDatum SCCode SCNaam
methode 2NF 3NF
• Gegeven SR1 = < UR1,FR1 > in 2NF
• Voor elk niet-sleutelattribuut A dat transitief functioneel
afhankelijk is van een kandidaatsleutel via Z:
– Elimineer A uit UR1
– Maak een nieuw relatieschema SR2 met attributenverzameling
UR2 = Z A
voorbeeld
StudNr CursCode InschrDatum SCCode SCNaam
StudNr CursCode InschrDatum SCCode
SCCode SCNaam
oefening
Car Date_Sold Salesman Commission Discount
CAR_SALE
2NF? 3NF?
Def: Een 3NF-relatieschema SR = <U,F> is in Boyce-Codd normaalvorm a.s.a. voor geen enkel sleutelattribuut A van U geldt: A is partieel of transitief functioneel afhankelijk van een of andere kandidaatsleutel van R.
Def: Een 1NF-relatieschema SR = <U,F> is in Boyce-Codd normaalvorm a.s.a. voor elke niet-triviale functionele afhankelijkheid X A in F+ geldt dat X een supersleutel is.
Of nog:
boyce-codd normaalvorm
• Bij 3NF zijn binnen sleutels nog functionele afhankelijkheden
toegestaan
• Deze wegwerken BCNF
methode 3NF BCNF
• zoals 1NF 2NF 3NF, maar met sleutelattributen
• BCNF ideaal m.b.t. functionele afhankelijkheden?
– ja:
• alle ongewenste functionele afhankelijkheden zijn weg
– helaas:
• niet steeds bereikbaar zonder andere problemen te
creëren...
• sommige f.a. worden moeilijker te controleren
• zie ook verder
voorbeeld
• stel dat inschrijvingsdatum eenduidig CursCode bepaalt
– bij voorbeeld omdat inschrijvingen voor cursussen enkel
gedurende bepaalde dagen kunnen (die verschillen voor elke
cursus)
• onderstaand schema is in 3NF, niet in BCNF
StudNr CursCode InschrDatum SCCode
opsplitsing naar BCNF
Redundantie is verwijderd;
maar: de f.a. StudNr, CursCode InschrDatum, SCCode
wordt moeilijker te controleren!
StudNr CursCode InschrDatum SCCode
InschrDatum CursusCode StudNr InschrDatum SCCode
in 3NF, niet in BCNF
FD2 gaat verloren in deze BCNF normalisatie
FD5: oppervlakte van de
loten bepaalt
COUNTY
VRAGEN?