DB_H5_Logisch_Ontwerpl

download report

Transcript DB_H5_Logisch_Ontwerpl

Database, Document and Content Management
Relationele databases:
Logisch databaseontwerp
Hoofdstuk 5
1
Vakgroep Telecommunicatie en Informatieverwerking
Overzicht
• Omzetting van een EER-diagram
• Normalisatie
• Denormalisatie
2
Vakgroep Telecommunicatie en Informatieverwerking
Overzicht
• Omzetting van een EER-diagram
– Overzicht
– Het omzettingsalgoritme voor relationele
databases
– Aanmaken van gedragspecificaties
– Case studie
• Normalisatie
• Denormalisatie
3
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
• Overzicht
informatievergaring
conceptueel
ontwerp
databasemodelonafhankelijk
logisch
ontwerp
dbmsonafhankelijk
fysieke
ontwerp
• domeinanalyse
• functionele analyse
• behoefteanalyse
• conceptueel model
(bijvoorbeeld EER-diagram)
• functionele beschrijving
• logisch databaseschema
(bijvoorbeeld relationeel)
• gedragspecificaties
• DDL-scripts
• implementatie van gedrag
4
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
conceptueel relationeel
databaseontwerp
EER-diagram
functionele
beschrijvingen
reverse
engineering
logisch relationeel
databaseontwerp
relationeel
databaseschema
5
gedragspecificaties
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
• Het omzettingsalgoritme voor relationele
databases
9 stappen:
1) Omzetting van reguliere entiteittypes
2) Omzetting van zwakke entiteittypes
3) Omzetting van specialisaties en generalisaties
4) Omzetting van categorieën
5) Omzetting van binaire 1:1 relatietypes
6) Omzetting van binaire 1:N relatietypes
7) Omzetting van binaire M:N relatietypes
8) Omzetting van meerwaardige attributen
9) Omzetting van n-aire relatietypes waarbij n>2
6
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 1: Omzetting van reguliere entiteittypes
Voor elk regulier entiteittype uit het diagram wordt een relationele
basisrelatie aangemaakt.
 Kies een relatienaam.
 Alle enkelvoudige, enkelwaardige, niet-afgeleide attributen van het
entiteittype worden toegevoegd aan het schema van de basisrelatie.
 Voor alle samengestelde attributen worden enkel de enkelvoudige,
enkelwaardige, niet-afgeleide componentattributen toegevoegd.
 Kies een primaire sleutel.
Opmerking: Indien het entiteittype geen sleutel heeft omdat het
betrokken is in een specialisatie, generalisatie of categorie, wordt de
primaire sleutel van de basisrelatie gedefinieerd in stap 3 of 4.
7
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Voornaam
Achternaam
Naam
Geboren
Artiest
Gestorven
Leeftijd
Artiest (Voornaam:varchar,
Achternaam:varchar,
omzetting
Geboren:integer,
Gestorven:integer)
Primaire sleutel: {Voornaam, Achternaam, Geboren}
visualisatie
Tabel Artiest
Voornaam: Achternaam: Geboren: Gestorven:
integer
integer
varchar
varchar
8
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 2: Omzetting van zwakke entiteittypes
Voor elk zwak entiteittype uit het diagram wordt een relationele
basisrelatie aangemaakt.
 Kies een relatienaam.
 Alle enkelvoudige, enkelwaardige, niet-afgeleide attributen van het zwak
entiteittype worden toegevoegd aan het schema van de basisrelatie.
 Voor alle samengestelde attributen worden enkel de enkelvoudige,
enkelwaardige, niet-afgeleide componentattributen toegevoegd.
 Voeg vreemde sleutel(s) toe. Beschouw daartoe de primaire sleutels van
de basisrelaties die corresponderen met de identificerende entiteittypes.
 Voeg de enkelvoudige, enkelwaardige, niet-afgeleide (componenten
van de) attributen van de identificerende relatietypes toe.
 Kies een primaire sleutel.
9
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Voornaam
Achternaam
Schilderij (SNaam:varchar,
Naam
Geboren
Voornaam:varchar,
Achternaam:varchar,
Geboren:integer,
Artiest
omzetting
Waarde:real,
1
Leeftijd
Periode:integer)
Gestorven
Primaire sleutel:
maakt
Periode
{SNaam, Achternaam, Voornaam, Geboren}
SNaam
Waarde
Vreemde sleutel:
N
{Achternaam, Voornaam, Geboren}
Schilderij
verwijst naar Artiest
10
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
visualisatie
Tabel Artiest
Voornaam: Achternaam: Geboren: Gestorven:
integer
integer
varchar
varchar
Tabel Schilderij
Snaam: Voornaam: Achternaam: Geboren: Waarde: Periode:
integer
varchar varchar
integer
real
varchar
11
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 3: Omzetting van specialisaties en
generalisaties
• Enkelvoudige overerving
Stel dat de specialisatie of generalisatie m subtypes en
1 supertype heeft.
 Optie 3A: Meerdere basisrelaties, voor het supertype en de
subtypes.
Bij deze optie blijven alle bestaande basisrelaties behouden.
Voeg de attributen van de primaire sleutel van het supertype
als primaire sleutel toe aan de basisrelaties van alle subtypes.
Deze attributen vormen in elk van deze basisrelaties tevens
een vreemde sleutel.
Deze optie werkt voor elke specialisatie of generalisatie.
12
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Waarde
ID
Kunstwerk
d
Verftype
Schilderij
omzetting
Gewicht
Beeldhouw
Optie 3A
Kunstwerk (ID:char(3), Waarde:real)
Primaire sleutel: {ID}
Schilderij (ID:char(3), Verftype:varchar)
Primaire sleutel: {ID}
Vreemde sleutel: {ID} verwijst naar Kunstwerk
Beeldhouw (ID:char(3), Gewicht:real)
Primaire sleutel: {ID}
Vreemde sleutel: {ID} verwijst naar Kunstwerk
visualisatie
Tabel Kunstwerk
Tabel Schilderij
Tabel Beeldhouw
ID:
char(3)
ID:
Verftype:
char(3) varchar
ID:
Gewicht:
char(3) real
13
Waarde:
real
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
 Optie 3B: Meerdere basisrelaties, enkel voor de subtypes.
Bij deze optie worden alle attributen van het supertype toegevoegd
aan de basisrelaties van alle subtypes. De primaire sleutel van de
basisrelatie van het supertype wordt de primaire sleutel in elk van de
basisrelaties van de subtypes. Na de toevoeging wordt de basisrelatie
van het supertype verwijderd.
Deze optie werkt alleen voor totale specialisaties en generalisaties
met disjuncte subtypes.
14
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Waarde
ID
Voorbeeld:
Kunstwerk
d
Verftype
Schilderij
Gewicht
Beeldhouw
omzetting
Optie 3B
Schilderij (ID:char(3), Waarde:real, Verftype:varchar)
Primaire sleutel: {ID}
Beeldhouw (ID:char(3), Waarde:real, Gewicht:real)
Primaire sleutel: {ID}
visualisatie
15
Tabel Schilderij
Tabel Beeldhouw
ID:
char(3)
ID:
Waarde: Gewicht:
char(3) real
real
Waarde: Verftype:
real
varchar
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
 Optie 3C: Eén enkele basisrelatie met één typeattribuut.
Bij deze optie worden alle attributen van de basisrelaties van alle
subtypes toegevoegd aan de basisrelatie van het supertype.
Daarenboven wordt er nog één extra typeattribuut toegevoegd,
waarvan de waarde aangeeft of en tot welk subtype een bepaald
tuple behoort. Na de toevoeging worden de basisrelaties van de
subtypes verwijderd. De primaire sleutel van de basisrelatie van het
supertype blijft ongewijzigd.
Deze optie werkt alleen voor generalisatie of specialisaties met
disjuncte subtypes, ongeacht of de specialisatie of generalisatie totaal
of partieel is.
16
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Waarde
ID
Kunstwerk
Verftype
Schilderij
d
Gewicht
Beeldhouw
omzetting
Optie 3C
Kunstwerk (ID:char(3), Waarde:real, Gewicht:real,
Verftype:varchar, Typekunstwerk:char(1))
Primaire sleutel: {ID}
visualisatie
Tabel Kunstwerk
ID:
Waarde: Gewicht: Verftype: Typekunstwerk:
char(3) real
varchar
char(1)
real
17
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
 Optie 3D: Eén enkele basisrelatie met meerdere typeattributen.
Bij deze optie worden opnieuw alle attributen van de basisrelaties van
alle subtypes toegevoegd aan de basisrelatie van het supertype. Nu
worden er m typeattributen toegevoegd waarbij elk typeattribuut
correspondeert met een subtype en aangeeft of een bepaald tuple al
dan niet tot dat subtype behoort. Na de toevoeging worden de
basisrelaties van de subtypes verwijderd. De primaire sleutel van de
basisrelatie van het supertype blijft ongewijzigd.
Deze optie werkt voor elke specialisatie of generalisatie.
18
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Waarde
Voorbeeld:
ID
Kunstwerk
Verftype
Schilderij
d
Gewicht
Beeldhouw
omzetting
Optie 3D
Kunstwerk (ID:char(3), Waarde:real, Gewicht:real,
Verftype:varchar, Schilderij:boolean, Beelhouw: boolean)
Primaire sleutel: {ID}
visualisatie
Tabel Kunstwerk
ID:
Waarde: Gewicht: Verftype: Schilderij: Beeldhouw:
char(3) real
varchar
boolean boolean
real
19
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
• Meervoudige overerving
 Optie 3A’: Meerdere basisrelaties, voor de supertypes en het
subtype.
Alle attributen van de primaire sleutels van de basisrelaties van
alle supertypes worden als vreemde sleutel toegevoegd aan de
basisrelatie van het subtype. Kies een geschikte primaire
sleutel.
 Optie 3B’: Eén enkele basisrelatie.
Alle attributen van de basisrelaties van alle supertypes worden
toegevoegd aan de basisrelatie van het subtype. Kies een
geschikte primaire sleutel. Verwijder de basisrelaties van de
supertypes.
20
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 4: Omzetting van categorieën
Voor elke categorie uit het diagram wordt de verwantschap tussen
de categorie en haar supertypes gemodelleerd:
 De supertypes van de categorie zullen meestal verschillende sleutels
hebben. Als dit het geval is, kan een nieuw sleutelattribuut –dat
fungeert als surrogaatsleutel– worden toegevoegd aan de basisrelatie
van de categorie. In het andere geval wordt het sleutelattribuut van
de supertypes toegevoegd aan de basisrelatie van de categorie.
 De primaire sleutel van de categorie wordt als vreemde sleutel
toegevoegd aan de basisrelaties van alle supertypes van de
categorie.
Merk op dat de controle of een entiteit van de categorie slechts voorkomt als
entiteit van juist één van de supertypes niet automatisch wordt afgedwongen
door deze omzetting.
21
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Naam
Voorbeeld:
Geboren
omzetting
Belastingnr
Naam
Naam
Voornaam
Plaats
Bedrijf
Persoon
‘particulier’
‘bedrijf’
U
Museum
‘museum’
Plaats
Thema
 Soort
Soort
Eigenaar (EigenaarID:char(3), Soort:varchar) Eigenaar
Primaire sleutel: {EigenaarID}
Persoon (Naam:varchar, Voornaam:varchar, Geboren:date, EigenaarID:char(3))
Primaire sleutel: {Naam, Voornaam, Geboren}
Vreemde sleutel: {EigenaarID} verwijst naar Eigenaar
Bedrijf (Naam:varchar, Plaats:varchar, Belastingnr:integer, EigenaarID:char(3))
Primaire sleutel: {Naam, Plaats}
Vreemde sleutel: {EigenaarID} verwijst naar Eigenaar
Museum (Naam:varchar, Plaats:varchar, Thema:varchar, EigenaarID:char(3))
Primaire sleutel: {Naam, Plaats}
Vreemde sleutel: {EigenaarID} verwijst naar Eigenaar
22
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
visualisatie
Tabel Eigenaar
EigenaarID: Soort:
char(3)
varchar
Tabel Persoon
Naam: Voornaam: Geboren: EigenaarID:
char(3)
varchar varchar
integer
Tabel Bedrijf
Naam: Plaats: Belastingnr: EigenaarID:
char(3)
varchar varchar integer
Tabel Museum
Naam: Plaats: Thema: EigenaarID:
varchar varchar varchar char(3)
23
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 5: Omzetting van binaire 1:1 relatietypes
 Optie 5A: Samenvoegen van beide basisrelaties.
Bij deze optie worden beide basisrelaties samengevoegd:
- Kies een basisrelatie waarvan het entiteittype totaal participeert
in het binair relatietype. Voeg alle attributen van de andere
basisrelatie toe aan de gekozen basisrelatie.
- Verwijder de andere basisrelatie.
- Voeg de enkelvoudige, enkelwaardige, niet-afgeleide
(componenten van de) attributen van het relatietype toe aan de
basisrelatie.
- De primaire sleutel van de basisrelatie blijft ongewijzigd.
Deze optie is enkel geschikt wanneer minstens één van de entiteittypes
totaal participeert in het relatietype.
24
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Faculteit
Rector
Ambtstermijn
Optie 5A
1
Rector (Naam:varchar, Faculteit:varchar,
omzetting
Univ_naam:varchar, Plaats:varchar, Ambtstermijn:integer)
bestuurt
Primaire sleutel: {Naam}
visualisatie
1
Universiteit
Naam
25
Plaats
Tabel Rector
Naam: Faculteit: Univ_naam: Plaats: Ambtstermijn:
varchar varchar varchar
varchar integer
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
 Optie 5B: Behoud van beide basisrelaties, geen extra basisrelatie.
Bij deze optie blijven beide basisrelaties behouden:
- Kies een basisrelatie. Indien mogelijk moet worden gekozen
voor de basisrelatie van een entiteittype dat totaal participeert in
het relatietype. Voeg de primaire sleutel van de andere
basisrelatie als vreemde sleutel toe aan de gekozen basisrelatie.
- Voeg de enkelvoudige, enkelwaardige, niet-afgeleide
(componenten van de) attributen van het relatietype toe aan de
basisrelatie.
Deze optie is altijd bruikbaar.
26
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Faculteit
Rector
1
Ambtstermijn
omzetting
bestuurt
1
Universiteit
Naam
Optie 5B
Universiteit (Univ_naam:varchar, Plaats:varchar)
Primaire sleutel: {Univ_naam}
Rector (Naam:varchar, Faculteit:varchar,
Ambtstermijn:integer, Univ_naam:varchar)
Primaire sleutel: {Naam}
Vreemde sleutel: {Univ_naam} verwijst naar Universiteit
Plaats
visualisatie
27
Tabel Universiteit
Tabel Rector
Univ_naam: Plaats:
varchar
varchar
Naam: Faculteit: Ambtstermijn: Univ_naam:
varchar varchar integer
varchar
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
 Optie 5C: Behoud van beide basisrelaties, met extra basisrelatie.
Bij deze optie wordt een extra basisrelatie ingevoerd om het
relatietype te modelleren.
- Kies een gepaste relatienaam.
- Voeg de primaire sleutels van beide basisrelaties als vreemde
sleutels toe.
- Voeg de enkelvoudige, enkelwaardige, niet-afgeleide
(componenten van de) attributen van het relatietype toe.
- Als primaire sleutel kan één van de vreemde sleutels worden
gekozen.
Ook deze optie is altijd bruikbaar. Er wordt echter een extra
basisrelatie aangemaakt.
28
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Faculteit
Rector
1
Ambtstermijn
omzetting
bestuurt
1
Universiteit
Naam
29
Plaats
Optie 5C
Universiteit (Univ_naam:varchar, Plaats:varchar)
Primaire sleutel: {Univ_naam}
Rector (Naam:varchar, Faculteit:varchar)
Primaire sleutel: {Naam}
Bestuurders (Naam:varchar, Univ_naam:varchar,
Ambtstermijn:integer)
Primaire sleutel: {Univ_naam}
Vreemde sleutel: {Naam} verwijst naar Rector
Vreemde sleutel: {Univ_Naam} verwijst naar Universiteit
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
visualisatie
30
Tabel Universiteit
Tabel Rector
Tabel Bestuurders
Univ_naam: Plaats:
varchar
varchar
Naam: Faculteit:
varchar varchar
Naam: Univ_naam: Ambtstermijn:
varchar varchar
integer
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 6: Omzetting van binaire 1:N relatietypes
In deze stap worden alle binaire ‘één-op-meerdere’-relatietypes
omgezet.
 Kies de basisrelatie van het entiteittype met kardinaliteit ‘meerdere’
(de N-zijde). Voeg de primaire sleutel van de andere basisrelatie als
vreemde sleutel toe aan de gekozen basisrelatie.
 Voeg de enkelvoudige, enkelwaardige, niet-afgeleide (componenten
van de) attributen van het relatietype toe aan de gekozen basisrelatie.
31
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Nummer
Titel
Auteur
Boek
omzetting
N
ontleent
visualisatie
Tabel Lezer
1
Naam: Adres:
varchar varchar
Lezer
Naam
Boek (Nummer:integer, Titel:varchar, Auteur:varchar,
Naam:varchar)
Primaire sleutel: {Nummer}
Vreemde sleutel: {Naam} verwijst naar Lezer
Adres
Tabel Boek
Nummer: Titel:
Integer varchar
32
Auteur: Naam:
varchar varchar
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 7: Omzetting van binaire M:N relatietypes
In deze stap worden alle binaire ‘meerdere-op-meerdere’-relatietypes
omgezet.
 Maak een nieuwe basisrelatie aan. Kies een gepaste relatienaam.
 Voeg de primaire sleutels van de basisrelaties van de betrokken
entiteittypes als vreemde sleutels toe aan de nieuwe basisrelatie.
 Voeg de enkelvoudige, enkelwaardige, niet-afgeleide (componenten
van de) attributen van het relatietype toe aan de nieuwe basisrelatie.
 De primaire sleutel van de nieuwe basisrelatie bestaat uit de
samenvoeging van de attributen van beide vreemde sleutels.
33
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Richting
Jaar
Nummer
Student
N
omzetting
volgt
Cursusbezetting(Nummer:integer, Code:char(4))
Primaire sleutel: {Nummer, Code}
Vreemde sleutel: {Nummer} verwijst naar Student
Vreemde sleutel: {Code} verwijst naar Cursus
visualisatie
M
Cursus
Code
Naam
Tabel Student
Tabel Cursus
Nummer: Naam: Richting: Jaar:
Integer varchar varchar integer
Code:
char(4)
Naam: Omschr:
varchar varchar
Omschr.
Tabel Cursusbezetting
Nummer: Code:
Integer char(4)
34
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 8: Omzetting van meerwaardige
attributen
Voor elk niet afgeleid, meerwaardig attribuut wordt een nieuwe
basisrelatie aangemaakt.
 Kies een gepaste relatienaam.
 Beschouw de basisrelatie van het entiteittype of relatietype waarbij
het meerwaardig attribuut voorkomt. Voeg de primaire sleutel van
deze basisrelatie als vreemde sleutel toe aan de nieuwe basisrelatie.
 Voeg de nodige attributen toe om één waarde voor het meerwaardig
attribuut te kunnen opslaan.
 De primaire sleutel wordt verkregen door alle attributen samen te
voegen en in het geval van de modellering van een samengesteld
meerwaardig attribuut te controleren of geen van de attributen
overbodig is.
35
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Richting
Jaar
Nummer
Student
Talen
omzetting Talenkennis_Student (Nummer: integer, Taal:varchar)
Primaire sleutel: {Nummer,Taal}
Vreemde sleutel: {Nummer} verwijst naar Student
visualisatie
36
Tabel Student
Tabel Talenkennis_Student
Nummer: Naam: Richting: Jaar:
Integer varchar varchar integer
Nummer: Taal:
integer
varchar
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Richting
Jaar
Nummer
Student
Talen
Taal
Spreekvaard.
Talenkennis_Student (Nummer: integer, Taal:varchar,
omzetting
Spreekvaard.:char(1), Schrijfvaard.:char(1))
Primaire sleutel: {Nummer,Taal}
SchrijfVreemde sleutel: {Nummer} verwijst naar Student
vaard.
visualisatie
37
Tabel Student
Tabel Talenkennis_Student
Nummer: Naam: Richting: Jaar:
Integer varchar varchar integer
Nummer: Taal:
Spreekvaard.: Schrijfvaard.:
integer
varchar char(1)
char(1)
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
– Stap 9: Omzetting van n-aire relatietypes (n>2)
!
38
Voor elk n-air relatietype waarbij n>2, wordt een nieuwe basisrelatie
aangemaakt.
 Kies een gepaste relatienaam.
 Voeg de primaire sleutels van de basisrelaties van de betrokken
entiteittypes als vreemde sleutels toe.
 Voeg de enkelvoudige, enkelwaardige, niet-afgeleide (componenten
van de) attributen van het relatietype toe.
 De primaire sleutel van de nieuwe basisrelatie bestaat uit de
samenvoeging van de attributen van alle vreemde sleutels, tenzij er
entiteittypes voorkomen waarvoor de kardinaliteitrestrictie 1 is. In
dat geval moet bijkomend worden gecontroleerd of voldaan is aan
de irreducibiliteitvoorwaarde en kan het voorkomen dat vreemde
sleutels die afkomstig zijn van deze basisrelaties uit de primaire
sleutel moeten worden verwijderd.
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeeld:
Naam
Periode
Schilderij
Nummer
Plaats
Planning(Naam:varchar,
Nummer:char(3),
omzetting
Code:char(8),
planBewaking:boolean)
ning
Bewaking
Primaire sleutel: {Nummer, Code}
N
Vreemde sleutel: {Naam} verwijst naar Schilderij
Vreemde sleutel: {Nummer} verwijst naar Plaats
Tentoonstelling
Vreemde sleutel: {Code} verwijst naar Tentoonstelling
1
Code
39
1
Naam
Thema
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
visualisatie
Tabel Planning
Naam: Nummer: Code: Bewaking:
varchar char(3) char(8) boolean
40
Tabel Schilderij
Tabel Plaats
Tabel Tentoonstelling
Naam: Periode:
varchar integer
Nummer:
integer
Code: Naam: Thema:
char(8) varchar varchar
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
• Aanmaken van gedragspecificaties
Omzetten van functionele beschrijvingen.
– Alternatieve sleutels
Afdwingen van uniciteit.
– Integriteitrestricties
Afdwingen van beperkingen.
– Stored procedures
– Triggers
41
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Voorbeelden:
1. Controle_periode (IN Periode IN Artiest.Geboren IN
Artiest.Gestorven OUT Status) EXCEPTIE (OnmogelijkePeriode)
–operatie om te controleren of de periode van het schilderij wel valt
binnen de leefperiode van de schilder van het schilderij.
Deze functionele beschrijving kan worden omgezet naar een
integriteitrestrictie met de volgende specificatie:
CREATE ASSERTION Controle_periode
CHECK ( NOT EXISTS ( SELECT * FROM Schilderij, Artiest
WHERE Schilderij.Artiest = Artiest.A_ID AND
( Schilderij.Periode < Artiest.Geboren OR
Schilderij.Periode > Artiest.Gestorven ) ) );
42
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
2. Ouderdom (IN Periode OUT Ouderdom) EXCEPTIE
(PeriodeOnbekend)
–operatie om de ouderdom van een schilderij te bepalen. Daartoe
wordt de periode van het schilderij vergeleken met de huidige datum.
Deze kan worden omgezet naar de stored procedure:
CREATE PROCEDURE Bepaal_Ouderdom
(Periode IN integer, Leeftijd OUT integer) AS BEGIN
IF Periode IS NULL THEN raise PeriodeOnbekend
ELSE
Leeftijd = CurrentYear() – Periode;
ENDIF;
END;
43
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
• Case studie:
Database voor een jeugdvereniging
geb.datum
naam
voornaam
geslacht
nummer
leeftijd
lidnummer
N
lid
van
minimum
1
leeftijd
M
1
Groep
postcode
betaald
schrijft
in
leider
van
1
straat
adres
Lid
N
M
voor
N
Activiteit
maximum
kost
naam
44
tijdstip
nummer
omschrijving
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Omzetting:
Stap 1: Omzetting van reguliere entiteittypes.
Deze stap resulteert in de aanmaak van de basisrelaties ‘Lid’, ‘Groep’ en ‘Activiteit’:
(1.0) Lid (lidnummer:integer, voornaam:varchar,
naam:varchar, geslacht:char(1), geb_datum:date,
straat:varchar, huisnr:smallint, postcode:varchar)
Primaire sleutel: {lidnummer}
(2.0) Groep (naam:varchar, min_leeftijd:smallint,
max_leeftijd:smallint)
Primaire sleutel: {naam}
(3.0) Activiteit(nummer:integer, omschrijving:varchar,
kost:real, tijdstip:timestamp)
Primaire sleutel: {nummer}
Stap 2: Omzetting van zwakke entiteittypes.
Er komen geen zwakke entiteittypes voor in het diagram.
45
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Stap 3: Omzetting van specialisaties en generalisaties.
Er komen geen specialisaties of generalisaties voor in het diagram.
Stap 4: Omzetting van categorieën.
Er komen geen categorieën voor in het diagram.
Stap 5: Omzetting van binaire 1:1 relatietypes.
Er is één binair 1:1 relatietype ‘leider van’. Er wordt gekozen voor optie 5B. De
stap resulteert in de volgende aanpassing van de basisrelatie ‘Groep’:
(2.1) Groep (naam:varchar, min_leeftijd:smallint,
max_leeftijd:smallint, leider:integer)
Primaire sleutel: {naam}
Vreemde sleutel: {leider} verwijst naar Lid
46
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Stap 6: Omzetting van binaire 1:N relatietypes.
Er is één binair 1:N relatietype ‘lid van’. De gebruikelijke omzetting resulteert in de
volgende aanpassing van de basisrelatie ‘Lid’ van het entiteittype aan de N-zijde:
(1.1) Lid (lidnummer:integer, voornaam:varchar,
voornaam:varchar,geslacht:char(1), geb_datum:date,
straat:varchar, huisnr:smallint, postcode:varchar,
groep:varchar)
Primaire sleutel: {lidnummer}
Vreemde sleutel: {groep} verwijst naar Groep
47
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Stap 7: Omzetting van binaire M:N relatietypes.
Er komen twee binaire M:N relatietypes voor, namelijk ‘voor’ en ‘schrijft in’.
Omzetting resulteert in twee nieuwe basisrelaties ‘Planning’ en ‘Inschrijving’:
(4.0) Planning (groepnaam:varchar, activiteitnummer:integer)
Primaire sleutel: {groepnaam,activiteitnummer}
Vreemde sleutel: {groepnaam} verwijst naar Groep
Vreemde sleutel: {activiteitnummer} verwijst naar
Activiteit
(5.0) Inschrijving (activiteitnummer:integer,
lidnummer:integer, betaald:boolean)
Primaire sleutel: {activiteitnummer, lidnummer}
Vreemde sleutel: {activiteitnummer} verwijst naar
Activiteit
Vreemde sleutel: {lidnummer} verwijst naar Lid
48
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Stap 8: Omzetting van meerwaardige attributen.
Er komen geen meerwaardige attributen voor in het diagram.
Stap 9: Omzetting van n-aire relatietypes waarbij n>2.
Er komen geen n-aire relatietypes met n>2 voor in het diagram.
49
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Resulterend relationeel databaseschema:
Lid (lidnummer:integer, naam:varchar, voornaam:varchar,
geslacht:char(1), geb_datum:date, straat:varchar,
huisnr:smallint, postcode:varchar, groep:varchar)
Primaire sleutel: {lidnummer}
Vreemde sleutel: {groep} verwijst naar Groep
Groep (naam:varchar, min_leeftijd:smallint,
max_leeftijd:smallint, leider:integer)
Primaire sleutel: {naam}
Vreemde sleutel: {leider} verwijst naar Lid
Activiteit(nummer:integer, omschrijving:varchar,
kost:real, tijdstip:timestamp)
Primaire sleutel: {nummer}
50
Vakgroep Telecommunicatie en Informatieverwerking
Omzetting van een EER-diagram
Planning (groepnaam:varchar, activiteitnummer:integer)
Primaire sleutel: {groepnaam,activiteitnummer}
Vreemde sleutel: {groepnaam} verwijst naar Groep
Vreemde sleutel: {activiteitnummer} verwijst naar Activiteit
Inschrijving (activiteitnummer:integer, lidnummer:integer,
betaald: boolean)
Primaire sleutel: {activiteitnummer, lidnummer}
Vreemde sleutel: {activiteitnummer} verwijst naar Activiteit
Vreemde sleutel: {lidnummer} verwijst naar Lid
51
Vakgroep Telecommunicatie en Informatieverwerking
Overzicht
• Omzetting van een EER-diagram
• Normalisatie
• Denormalisatie
52
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
• Probleemstelling
– Overtollige dataopslag
– Update-anomalieën
– Dataverlies
53
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Tabel Schilderij
S_ID:
Titel:
char(3) varchar
54
primaire sleutel = {S_ID}
Periode: Naam:
integer
varchar
Geboren:
integer
Gestorven:
integer
1840
1926
1834
1917
S01
Vissershuis
1882
S02
De balletles
1872
Monet
Degas
S03
Mona Lisa
1499
Da Vinci
1452
1519
S05
Na het bad
1883
Degas
1832
1917
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
• Het normalisatieproces
1NF  2NF  3NF  BCNF  4NF  5NF
– Functionele afhankelijkheid
Een verzameling van attributen Y is functioneel afhankelijk van
een verzameling van attributen X als de waarden van de attributen
van Y op elk moment uniek worden vastgelegd door de waarden
van de attributen van X.
Als de attribuutwaarden van X gekend zijn, zijn daardoor ook de
attribuutwaarden van Y gekend.
X wordt de determinant van de functionele afhankelijkheid genoemd.
55
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– Irreducibele functionele afhankelijkheid
Een verzameling van attributen Y is irreducibel functioneel
afhankelijk van een verzameling van attributen X (genoteerd
als X→Y) als Y functioneel afhankelijk is van X en er bovendien
geldt dat:
1. X en Y disjunct zijn en dus geen gemeenschappelijke
attributen hebben.
2. Er geen echte deelverzameling X’  X van X bestaat, zodat Y
functioneel afhankelijk is van X’.
– Functioneel afhankelijkheidsdiagram
56
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– Voorbeeld
Tabel Schilderij
S_ID:
Titel:
char(3) varchar
kandidaatsleutels = {S_ID} en {Titel, Naam}
Periode: Naam:
integer
varchar
Geboren:
integer
Gestorven:
integer
Voor de attributen van bovenstaand relatie bestaan de volgende
irreducibele functionele afhankelijkheden:
{S_ID} → {Titel}
{S_ID} → {Periode}
{S_ID} → {Naam}
{S_ID} → {Geboren}
{S_ID} → {Gestorven}
{Titel, Naam} → {S_ID}
{Titel, Naam} → {Periode}
{Naam} → {Geboren}
{Naam} → {Gestorven}
57
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– De eerste normaalvorm (1NF)
Een relatie staat in eerste normaalvorm indien de datatypes van
al zijn attributen atomair zijn.
58
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Voorbeeld (in SQL3):
Artiest (A_ID:char(3), Naam:varchar, Voornaam:varchar,
Verblijfplaats:varchar array [5])
Primaire sleutel: {A_ID}
Door het meerwaardig karakter van het datatype ‘varchar array [5]’
staat de relatie ‘Artiest’ niet in eerste normaalvorm. Bij de eerste
normalisatiestap moet het worden opgesplitst. Dit kan als volgt:
Artiest (A_ID:char(3), Naam:varchar, Voornaam:varchar)
Primaire sleutel: {A_ID}
VerblijfplaatsArtiest (A_ID:char(3), Verblijfplaats:varchar)
Primaire sleutel: {A_ID, Verblijfplaats}
Vreemde sleutel: {A_ID} verwijst naar Artiest
59
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– De tweede normaalvorm (2NF)
Een relatie staat in tweede normaalvorm indien ze in eerste
normaalvorm staat en elk attribuut van de relatie, dat geen deel
uitmaakt van een kandidaatsleutel, irreducibel functioneel
afhankelijk is van elke kandidaatsleutel van de relatie.
• Er mogen geen attributen voorkomen die irreducibel functioneel
afhankelijk zijn van een deel van de kandidaatsleutel
• Tweede normaalvorm kan worden bekomen door afsplitsing van de
probleemattributen
60
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Voorbeeld:
Schilderij (S_ID:char(3), Titel:varchar, Periode:integer,
Naam:varchar, Geboren:integer, Gestorven:integer)
primaire sleutel {S_ID} en alternatieve sleutel {Titel, Naam}.
Omdat zowel {Geboren} als {Gestorven} irreducibel functioneel
afhankelijk zijn van {Naam} (dus niet van de kandidaatsleutel
{Titel, Naam}), staat de relatie niet in tweede normaalvorm.
Afsplitsing van de probleemattributen levert de volgende relaties op:
Artiest (Naam:varchar,Geboren:integer, Gestorven:integer)
primaire sleutel: {Naam}
Schilderij (S_ID:char(3), Titel:varchar, Periode:integer, Naam:varchar)
primaire sleutel: {S_ID}
vreemde sleutel: {Naam} verwijst naar Artiest
61
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– De derde normaalvorm (3NF)
Een irreducibele functionele afhankelijkheid X→Z is transitief,
als er een verzameling van attributen Y bestaat, die geen
kandidaatsleutel of deelverzameling van een kandidaatsleutel is,
waarvoor er geldt dat X→Y en Y→Z.
Een relatie staat in derde normaalvorm indien ze in tweede
normaalvorm staat en elk attribuut van de relatie, dat geen deel
uitmaakt van een kandidaatsleutel, niet transitief irreducibel
functioneel afhankelijk is van een kandidaatsleutel van de relatie.
62
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Voorbeeld: Beschouw de volgende variant van de relatie ‘Schilderij’:
Schilderij (S_ID:char(3), Titel:varchar, Periode:integer, Naam:varchar,
Eigenaar:varchar, Land:varchar)
Kandidaatsleutels: {S_ID} en {Titel, Naam}.
De afhankelijkheid {S_ID} → {Land} is transitief vanwege de verzameling
{Eigenaar}, die geen (deel van een) kandidaatsleutel is, waarvoor
{S_ID} → {Eigenaar} en {Eigenaar} → {Land}
Deze relatie staat in tweede, maar niet in derde normaalvorm.
Afsplitsing van het attribuut ‘Land’ levert de volgende relaties op:
Eigenaar (Eigenaar:varchar, Land:varchar)
Primaire sleutel: {Eigenaar}
Schilderij (S_ID:char(3), Titel:varchar, Periode:integer, Naam:varchar,
Eigenaar:varchar)
Primaire sleutel: {S_ID}
Vreemde sleutel: {Eigenaar} verwijst naar Eigenaar
63
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
– De “Boyce-Codd”-normaalvorm (BCNF)
Een relatie staat in “Boyce-Codd”-normaalvorm indien ze in
eerste normaalvorm staat en elke determinant een
kandidaatsleutel is van de relatie.
64
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Voorbeeld: Beschouw de relatie:
Kunstbezit (A_ID:char(3), Artiestnaam:varchar, Eigenaar:varchar,
Aantal:integer)
Kandidaatsleutels: {Artiestnaam, Eigenaar} en {A_ID, Eigenaar}
De relatie staat niet in “Boyce-Codd”-normaalvorm omwille van de
irreducibele functionele afhankelijkheden
{A_ID} → {Artiestnaam} en {Artiestnaam} → {A_ID}
waarbij {A_ID} en {Artiestnaam} geen kandidaatsleutel zijn.
Dit heeft voor gevolg dat indien een eigenaar meerdere werken van
dezelfde artiest bezit, de verwantschap tussen ‘A_ID’ en
‘Artiestnaam’ meerdere keren en dus redundant wordt opgeslagen.
65
Vakgroep Telecommunicatie en Informatieverwerking
Normalisatie
Voorbeeld (vervolg):
Afsplitsing van de probleemattributen kan op de twee onderstaande
manieren:
Kunstenaar (A_ID:char(3), Artiestnaam:varchar)
Primaire sleutel: {A_ID}, alternatieve sleutel: {Artiestnaam}
Kunstbezit (A_ID:char(3), Eigenaar:varchar, Aantal:integer)
Primaire sleutel: {A_ID, Eigenaar}
Vreemde sleutel: {A_ID} verwijst naar Kunstenaar
of
Kunstenaar (A_ID:char(3), Artiestnaam:varchar)
Primaire sleutel: {Artiestnaam}, alternatieve sleutel: {A_ID}
Kunstbezit (Artiestnaam:varchar, Eigenaar:varchar, Aantal:integer)
Primaire sleutel: {Artiestnaam Eigenaar}
Vreemde sleutel: {Artiestnaam} verwijst naar Kunstenaar
66
Vakgroep Telecommunicatie en Informatieverwerking
Overzicht
• Omzetting van een EER-diagram
• Normalisatie
• Denormalisatie
67
Vakgroep Telecommunicatie en Informatieverwerking
Denormalisatie
• Voordelen
• Nadelen
68
Vakgroep Telecommunicatie en Informatieverwerking