ONTWERP VAN RELATIONELE DATABANKEN Basisprogramma

Vlaamse Dienst voor Arbeidsbemiddeling en Beroepsopleiding
ONTWERP VAN RELATIONELE
DATABANKEN
Basisprogramma
Opleidingen informatica
2/70
Deze cursus is eigendom van de VDAB©
Inhoudsopgave
1.1
Gegevens en informatie .............................................................................6
1.2
Wat is een gegevensverzameling?.............................................................6
2
BESTANDEN................................................................................................... 7
2.1
Bestanden, records en velden ....................................................................7
2.2
Bestandsorganisatie ...................................................................................7
2.2.1
Een sequentiële bestandsorganisatie..................................................7
2.2.2
Een directe bestandsorganisatie .........................................................7
2.2.3
Een index sequentiële bestandsorganisatie ........................................8
2.2.4
Keuze van een organisatievorm ..........................................................8
2.3
Bewerkingen op de gegevens ....................................................................8
2.3.1
Bewerkingen op records......................................................................8
2.3.2
Bewerkingen op de bestanden in hun geheel......................................9
2.4
3
Oefeningen .................................................................................................9
DATABASES EN DATABASE MANAGEMENT SYSTEMEN ..................... 10
3.1
Redundantie en inconsistentie..................................................................10
3.2
Data abstractie .........................................................................................11
3.2.1
Het conceptuele schema ...................................................................12
3.2.2
Het fysieke schema ...........................................................................12
3.2.3
Het externe schema ..........................................................................12
3.2.4
Data abstractie ..................................................................................13
4
DE VERSCHILLENDE STAPPEN BIJ HET ONTWERPEN VAN EEN
DATABASE ................................................................................................... 14
4.1
Stap 1: Analyse van de informatiebehoeften ............................................14
4.2
Stap 2: Conceptueel database ontwerp....................................................14
4.3
Stap 3: Logisch database ontwerp ...........................................................14
4.4
Stap 4: Verdere verfijning van het schema ...............................................14
4.5
Stap 5: Fysiek database ontwerp .............................................................15
4.6
Stap 6: Ontwerp van de beveiliging ..........................................................15
4.7
Opmerkingen ............................................................................................15
5
DE INFORMATIEBEHOEFTEN..................................................................... 16
5.1
Hoe bepaal je de informatiebehoeften......................................................16
5.2
Oefeningen ...............................................................................................17
Begrippen rond databases
3/70
5.2.1
Een autoverhuurbedrijf ......................................................................17
5.2.2
Opleidingen bureautica......................................................................18
6
HET ENTITY RELATIONSHIP MODEL......................................................... 21
6.1
Entiteiten ..................................................................................................21
6.2
Entiteittype................................................................................................21
6.3
Attributen ..................................................................................................22
6.3.1
Atomaire attributen. ...........................................................................22
6.3.2
Meerwaardige attributen of repeterende attributen............................22
6.3.3
Samengestelde attributen..................................................................23
6.3.4
Procesattributen ................................................................................23
6.3.5
Het domein van een attribuut ............................................................23
6.4
Primaire sleutel (primary key) ...................................................................23
6.5
Associaties ...............................................................................................24
6.5.1
Kardinaliteit en optionaliteit van een associatie .................................25
6.5.2
Recursieve associaties......................................................................27
6.5.3
Attributen van een associatie ............................................................27
6.6
De voorbeelden samengevat....................................................................28
6.6.1
Beschrijving .......................................................................................28
6.6.2
Entiteiten ...........................................................................................28
6.6.3
Associaties zoeken............................................................................28
6.6.4
Een eerste E/R diagram ....................................................................29
6.6.5
De cardinaliteiten bepalen .................................................................29
6.6.6
Attributen ...........................................................................................30
6.7
Zwakke entiteittypes .................................................................................30
6.8
Hiërarchieën van entiteiten .......................................................................30
6.9
Een entiteit of een attribuut?.....................................................................32
6.10
Oefeningen ...............................................................................................33
6.10.1
Een factuur ........................................................................................33
6.10.2
Associaties, kardinaliteit en optionaliteit ............................................33
6.10.3
Hiërarchische structuren....................................................................34
6.10.4
Recursieve associatie........................................................................34
6.10.5
Ziekenhuis .........................................................................................34
6.10.6
Domein ..............................................................................................34
7
LOGISCH DATABASEONTWERP................................................................ 35
7.1
Het hiërarchische gegevensmodel ...........................................................35
Begrippen rond databases
4/70
7.2
Het netwerk gegevensmodel ....................................................................36
7.3
Het relationeel gegevensmodel ................................................................37
7.3.1
De omzetting van entiteiten en attributen ..........................................37
7.3.2
De omzetting van een 1:n associatie.................................................38
7.3.3
De omzetting van een m:n associatie................................................39
7.3.4
De omzetting van een associatie met eigen attributen ......................39
7.3.5
Integriteit van de gegevens ...............................................................40
7.4
Oefeningen ...............................................................................................41
7.4.1
Film ...................................................................................................41
7.4.2
Bibliotheek.........................................................................................41
8
VERDERE VERFIJNING VAN HET SCHEMA: NORMALISATIE................. 42
8.1
Problemen met redundantie en inconsistentie..........................................42
8.1.1
Een illustratie.....................................................................................42
8.1.2
Problemen .........................................................................................42
8.1.3
Gevolgen ...........................................................................................42
8.2
De normalisatieprocedure ........................................................................43
8.2.1
Voorbereiding tot normalisatie...........................................................43
8.2.2
Eerste normalisatiestap .....................................................................46
8.2.3
Tweede normalisatiestap...................................................................47
8.2.4
Derde normalisatiestap......................................................................48
8.3
Een tweede voorbeeld, een offerte...........................................................49
8.3.1
Na de eerste normalisatiestap...........................................................50
8.3.2
Na de tweede normalisatiestap .........................................................50
8.3.3
Na de derde normalisatiestap............................................................50
8.4
Integratie van genormaliseerde gegevensgroepen...................................51
8.4.1
Homoniemen en synoniemen............................................................51
8.4.2
Samenvoegen van entiteiten uit de verschillende informatiebehoeften
51
8.4.3
Definitieve naamgeving en samenstelling .........................................52
8.5
Bepalen van de relaties ............................................................................52
8.6
Oefeningen ...............................................................................................54
8.6.1
Projectbeheer ....................................................................................54
8.6.2
Ziekenfonds.......................................................................................54
8.6.3
Bestellingen en pickinglist .................................................................54
8.6.4
Distributiebedrijf van kantoorbenodigheden ......................................55
8.6.5
Overboekingen en rekeningoverzichten ............................................57
Begrippen rond databases
5/70
9
HET FYSIEKE SCHEMA ............................................................................... 58
9.1
Toegangspaden........................................................................................58
9.2
Indexen.....................................................................................................59
9.2.1
Wat is een index? ..............................................................................59
9.2.2
Welke relaties krijgen een index?......................................................60
9.2.3
Welke attributen krijgen een index? ..................................................60
9.3
10
Oefeningen ...............................................................................................60
DE ROL VAN SQL......................................................................................... 61
10.1
Wat is SQL? .............................................................................................61
10.2
Onderdelen van SQL................................................................................61
10.2.1
De data definition language (DDL) ....................................................61
10.2.2
De data manipulation language (DML) ..............................................62
10.2.3
Embedded en dynamische SQL........................................................62
10.2.4
Triggers .............................................................................................62
10.2.5
Opdrachten die met beveiliging te maken hebben ............................63
10.2.6
Opdrachten i.v.m. transaction management......................................63
10.2.7
Opdrachten rond een client server architectuur.................................63
11
AANDACHTSPUNTEN BIJ HET GEBRUIK VAN EEN DBMS ..................... 64
11.1
Beveiliging ................................................................................................64
11.1.1
DAC...................................................................................................64
11.1.2
MAC ..................................................................................................65
11.2
Gelijktijdige benadering van gegevens .....................................................65
11.3
Transactiebeheer......................................................................................65
11.3.1
Wat is een transactie .........................................................................65
11.3.2
ACID..................................................................................................66
11.3.3
Technieken om ACID te ondersteunen .............................................67
12
EVOLUTIES................................................................................................... 68
12.1
12.1.1
De architectuur ..................................................................................68
12.1.2
XML ...................................................................................................68
12.1.3
Tekstdocumenten..............................................................................69
12.2
13
Web databases.........................................................................................68
Object database systemen .......................................................................69
COLOFON ..................................................................................................... 70
INLEIDING
Begrippen rond databases
6/70
1.1
Gegevens en informatie
Wil een bedrijf op een behoorlijke manier functioneren, dan moet het over de
nodige informatie kunnen beschikken.
Aan communicatiemiddelen om aan gegevens te geraken is er vandaag de dag
geen gebrek, maar de kunst bestaat erin binnen die gegevens op tijd correcte
informatie te vinden. Om gegevens om te zetten in informatie is een interpretatie
nodig.
Dit creëert een behoefte aan hulpmiddelen om gegevens op te slaan en te beheren
en om op een snelle en efficiënte manier nuttige informatie uit die gegevens te
destilleren.
In de praktijk bestaat er een groot verschil tussen de manier waarop een
werknemer in een bedrijf de gegevens waarneemt en de manier waarop ze
uiteindelijk opgeslagen worden in het informatiesysteem.
Deze cursus licht toe hoe gegevens, zoals we die waarnemen in de werkelijke
wereld, kunnen omgezet worden in gegevens die opgeslagen kunnen worden in
een computer en hoe die op een efficiënte manier kunnen geraadpleegd worden.
1.2
Wat is een gegevensverzameling?
Een gegevensverzameling bevat een aantal items (getallen, tekst, codes,
tekeningen…) die logisch gezien bij elkaar horen.
Een manager van een groot bedrijf moet veel vergaderen, regelmatig op zakenreis,
enz. Om de afspraken bij te houden noteert hij die in een agenda. De agenda is
een gegevensverzameling.
De secretaris van een vereniging moet naar aanleiding van elke activiteit
uitnodigingen versturen naar de verschillende leden van de vereniging. De lijst met
de adresgegevens van alle leden is een gegevensverzameling.
Een kruidenier houdt zijn kosten en inkomsten bij in een kasboek. Het kasboek is
een gegevensverzameling.
Begrippen rond databases
7/70
2 BESTANDEN
2.1
Bestanden, records en velden
Oorspronkelijk werd een gegevensverzameling opgeslagen in een computer, in de
vorm van een bestand.
De gegevens over de klanten van een firma komen dan b.v. in een bestand klanten.
Per klant zijn daarin de naam, de voornaam, het adres, de postcode en de
woonplaats terug te vinden.
Men noemt in deze context elke klant een record en elk stukje informatie over een
klant zoals b.v. zijn woonplaats een veld.
Elk record van één bestand is opgebouwd uit dezelfde velden. Elk veld heeft een
vaste lengte, zo kunnen er b.v. in elk record van het bestand klanten 50 karakters
voorzien zijn voor de naam van de klant, 4 karakters voor de postcode, enz.
Hoe gegevens over een bepaalde klant kunnen opgevraagd worden, hangt samen
met de bestandsorganisatie.
2.2
Bestandsorganisatie
2.2.1 Een sequentiële bestandsorganisatie
In een sequentiële bestandsorganisatie zijn alle records na elkaar opgeslagen en
kunnen alleen opgevraagd worden in de volgorde waarin ze opgeslagen zijn.
Concreet betekent dit dat om een record te vinden, het bestand record per record
moet doorlopen worden tot aan het gewenste record.
Onderhoud van de data gebeurt als volgt:
•
Nieuwe records komen altijd achteraan
•
Verwijderde records veroorzaken een gat in het bestand.
•
Een record wijzigen kan alleen door het bestand over te schrijven naar een
nieuw bestand tot aan het te wijzigen record, dan de bijgewerkte gegevens voor
het te wijzigen record toe te voegen aan het nieuwe bestand en daarna het
vervolg van het oorspronkelijke bestand verder over te schrijven naar het
nieuwe bestand. Ten slotte krijgt het nieuwe bestand de naam van het
oorspronkelijke bestand.
2.2.2 Een directe bestandsorganisatie
Bij een directe bestandsorganisatie bestaat er een direct verband tussen de plaats
van een record op het opslagmedium en één van de velden van het record. Dit veld
moet het record uniek kunnen identificeren en wordt de sleutel van het record
genoemd.
Een directe bestandsorganisatie is alleen te realiseren op een adresseerbaar
geheugenmedium zoals een magneetschijf. Het verband tussen de sleutel en de
fysieke plaats van het record wordt berekend op basis van een hashfunctie. Als
twee verschillende sleutels eenzelfde resultaat voor die hashfunctie opleveren
wordt een collisionfunctie gebruikt om een nieuwe locatie te bepalen.
Een record wordt onmiddellijk teruggevonden op basis van zijn sleutelwaarde
Begrippen rond databases
8/70
Op het gebied van onderhoud betekent dit:
•
Een nieuw record wordt geplaatst op basis van de waarde van zijn sleutel
•
De plaats van een gewist record wordt onmiddellijk vrijgegeven.
2.2.3 Een index sequentiële bestandsorganisatie
Een index sequentieel bestand combineert de mogelijkheden van een sequentieel
en van een direct bestand. Het bestand kan sequentieel doorlopen worden, records
kunnen ook rechtstreeks teruggevonden worden op basis van een sleutelwaarde.
Deze bestandsorganisatie biedt ook de mogelijkheid een record op te sporen op
basis van een bepaalde sleutel en daarna het bestand verder sequentieel te
doorlopen.
2.2.4 Keuze van een organisatievorm
De keuze van de organisatievorm wordt mee bepaald door
•
De omvang van de gegevensverzameling
•
Het gebruik van de gegevensverzameling (invoerbestand, uitvoerbestand,
invoer/uitvoerbestand, procesbestand)
•
Het opslagmedium (tape, schijf)
2.3
Bewerkingen op de gegevens
De gegevens moeten niet alleen opgeslagen worden in de computer. Er zal ook de
nodige programmatuur nodig zijn om met de gegevens te werken.
Deze programmatuur wordt zuiver in functie van het bestand in kwestie
geschreven. Het programma benadert het bestand als een opeenvolgende reeks
tekens en brengt zelf structuur in deze tekens. Dit maakt dat gegevensbestand en
programma onlosmakelijk met elkaar verbonden zijn.
2.3.1 Bewerkingen op records
2.3.1.1
Toevoegen
Gegevens moeten ingevoerd en opgeslagen worden.
2.3.1.2
Raadplegen
Bepaalde gegevens moeten opgezocht en getoond worden.
2.3.1.3
Onderhouden
De gegevens moeten up to date en betrouwbaar gehouden worden.
•
Nieuwe gegevens moeten ingevoerd worden
•
Bestaande gegevens moeten aangepast worden
•
Verouderde gegevens moeten verwijderd worden
Begrippen rond databases
9/70
2.3.2 Bewerkingen op de bestanden in hun geheel
2.3.2.1
Tonen
De volledige inhoud van het bestand tonen op een gebruikersvriendelijke manier.
2.3.2.2
Sorteren
De volledige inhoud van het bestand tonen in een bepaalde volgorde: b.v. een
bestand met gegevens over klanten, alfabetisch op naam van de klant of een
bestand met gegevens over personeelsleden in volgorde van indiensttreding, enz.
2.3.2.3
Samenvoegen
Soms moeten de gegevens uit twee bestanden samengevoegd worden.
Dit kan door de bestanden gewoon na elkaar te plaatsen. Men spreekt van
concatenatie.
De volgorde van de records in het samengevoegde bestand kan ook bepaald
worden op basis van de inhoud van een bepaald veld. Men spreekt van mergen.
2.3.2.4
Kopiëren
Dit gebeurt vooral in het kader van het maken van reservebestanden uit
veiligheidsoverwegingen.
2.3.2.5
Reorganiseren
Afhankelijk van de bestandsorganisatie kunnen er nadat een aantal toevoegingen
en verwijderingen van records gebeurd zijn, gaten ontstaan in het bestand. Al die
gaten nemen extra opslagruimte in beslag.
Het bestand wordt bij een reorganisatie herschreven op een ander opslagmedium
en eventueel teruggeplaatst. Op die manier wordt het bestand terug een geheel.
2.3.2.6
Verwijderen
Bestanden die niet meer nodig zijn verwijderen om geheugenruimte te winnen.
2.4
Oefeningen
1. Noem enkele gegevensverzamelingen die je zou kunnen tegenkomen in een
autoverhuurbedrijf.
2. Een bestand wordt opgeslagen op tape. Welke bestandsorganisaties komt
hiervoor in aanmerking.
3. In een bibliotheek wordt een lijst bijgehouden van de aangekochte boeken.
Geef een voorbeeld van een record dat in die lijst zou kunnen voorkomen.
Bedenk ook geschikte veldnamen.
4. Inschrijvingen voor opleidingen i.v.m. office pakketten moeten geregistreerd
worden. Hoe zou een record er kunnen uitzien? Bedenk geschikte
veldnamen.
Begrippen rond databases
10/70
3 DATABASES EN
DATABASE MANAGEMENT SYSTEMEN
Een andere manier om een gegevensverzameling op te slaan in een computer is in
de vorm van een database.
Een database is een verzameling gegevens die je op een gestructureerde manier
kunt benaderen. Denk aan een grote archiefkast waarin alles netjes is opgeborgen,
zodat je een bepaald dossier gemakkelijk kunt vinden. Belangrijk is dat de
gegevens in een database zodanig zijn opgeslagen dat deze gegevens optimaal
doorzoekbaar zijn.
Een database management systeem of DBMS is software die het mogelijk maakt
de gegevens in een database te onderhouden (invoeren van nieuwe gegevens,
verwijderen van verouderde gegevens, wijzigen van bestaande gegevens) en de
gewenste informatie op een efficiënte en snelle manier op te vragen.
3.1
Redundantie en inconsistentie
Een belangrijk kenmerk van een database is dat een database geïntegreerd is. Dit
betekent dat de gegevens in een database door meerdere programma’s kunnen
gebruikt worden.
Dit staat in contrast met het opslaan van gegevens in bestanden. Bij een klassieke
verwerking van gegevens via bestanden wordt per toepassing een aangepast
gegevensbestand gedefinieerd.
Een voorbeeld:
Het secretariaat in een opleidingscentrum moet een overzicht kunnen maken van
cursisten en hun behaalde resultaten. Een bestand wordt aangemaakt met deze
gegevens evenals bijbehorende software om een overzicht van de resultaten af te
drukken en nieuwe cursisten en resultaten toe te voegen. Bij de keuze van de
bestandsorganisatie wordt rekening gehouden met de manier waarop het bestand
zal gebruikt worden.
De boekhouding van het centrum die het betalen van de inschrijvingsgelden opvolgt
heeft dan weer een bestand nodig met daarin de gegevens van de cursisten,
hoeveel het inschrijvingsgeld bedraagt en of het al betaald is. De boekhouding wil
overzichten kunnen maken van het bedrag dat de cursisten moeten betalen en
welke cursisten nog moeten betalen. Ook hier wordt een aangepaste
bestandsorganisatie gekozen en aangepaste programmatuur geschreven.
Zowel het secretariaat als de boekhouding slaan data i.v.m. cursisten op. Allebei
hebben ze enerzijds informatie nodig over de cursisten die de andere afdeling ook
nodig heeft (naam en adresgegevens) en anderzijds informatie die de andere
afdeling niet nodig heeft (secretariaat: resultaten, boekhouding: te betalen
bedragen).
Als deze gegevens in een database worden opgeslagen, wordt elk gegeven slechts
één keer opgeslagen. De naam en adresgegevens zullen dus slechts één keer
opgeslagen worden. De software haalt voor de verschillende gebruikers
aangepaste informatie op.
Dit voorkomt dat gegevens dubbel opgeslagen worden of anders gezegd, dit
voorkomt redundantie.
Begrippen rond databases
11/70
Redundantie veroorzaakt extra werk, vraagt meer geheugenruimte, en geeft
bovendien aanleiding tot fouten. Immers, als gegevens veranderen moeten de
wijzigingen niet op één plaats doorgevoerd worden, maar op elke locatie waar
diezelfde gegevens opgeslagen zijn.
Zo moet een adreswijziging van een cursist in de hierboven beschreven situatie
met bestanden, niet alleen doorgevoerd worden in het bestand van het secretariaat,
maar ook nog eens in het bestand van de boekhouding. Als een aanpassing niet
doorgegeven of vergeten wordt op één van beide afdelingen, geeft dat aanleiding
tot tegenstrijdige of inconsistente gegevens.
Databases brengen dan weer andere problemen mee.
Omdat de gegevens slechts één keer opgeslagen zijn, krijgt elke gebruiker te
maken met dezelfde gegevensstructuur. Elke gebruiker wordt er bijvoorbeeld mee
geconfronteerd dat het afleveradres van klanten genoteerd wordt, ook die
afdelingen die geen boodschap hebben aan het afleveradres.
Om dit te vermijden, krijgen gebruikers geen directe toegang tot de gegevens in
een database. Zij kunnen de gegevens alleen benaderen via een database
management systeem.
Het DBMS moet ervoor zorgen dat elke gebruiker de opgeslagen gegevens op een
voor hem nuttige en efficiënte manier kan bekijken.
3.2
Data abstractie
Data abstractie heeft te maken met een strikte scheiding tussen de programmatuur
en het opslaan van de gegevens.
Bij het opslaan van gegevens in bestanden wordt de structuur van de bestanden
ingebed in het programma. Dit betekent dat een wijziging aan de structuur van een
gegevensbestand meteen ook een aanpassing van de bijbehorende
programmatuur vraagt.
De structuur van een database is echter opgeslagen in de database zelf en
bijgevolg volledig gescheiden van de software die de toegang tot de gegevens
verzorgt. We zeggen dat de data en de programmatuur onafhankelijk zijn.
Om die onafhankelijkheid tussen programmatuur en data te bereiken worden de
gegevens in een DBMS op drie verschillende niveaus van abstractie beschreven:
het conceptuele, het fysieke en het externe schema.
Begrippen rond databases
12/70
extern schema
extern schema
extern schema
Conceptueel schema
Fysiek schema
Het externe en het conceptuele schema worden gedefinieerd via een DDL (data
definition language).
3.2.1 Het conceptuele schema
Het conceptuele schema, ook wel het logische schema genoemd, beschrijft de
gegevens in functie van het datamodel (zie ook Hoofdstuk 7: Logisch
databaseontwerp). Het bepaalt welke gegevens voor de hele organisatie
opgeslagen worden en hoe ze gestructureerd worden.
In een relationeel model zou dit er voor cursisten die zich inschrijven voor bepaalde
opleidingen als volgt kunnen uitzien:
Cursist(cursistid:string, naam:string, geboortedatum:datum)
Opleiding(opleidingid:string, naam:string, aantal uur: integer)
Inschrijving(cursistid:string, opleidingid:string)
3.2.2 Het fysieke schema
Het fysieke schema beschrijft hoe de relaties beschreven in het conceptuele
schema effectief opgeslagen zullen worden.
Welke toegangspaden moeten gevolgd worden om tot de gewenste informatie te
komen? Welke indexen kunnen de verwerking van een bevraging bevorderen? Kan
het clusteren van gegevens (het fysiek bij elkaar opslaan van gegevens uit
gerelateerde tabellen) de prestaties verbeteren?
3.2.3 Het externe schema
Dank zij de externe schema’s wordt de toegang tot de data aangepast aan de
individuele gebruikers. Elke database heeft maar één conceptueel schema en één
fysiek schema, maar meerdere externe schema’s. Elk extern schema is aangepast
aan een bepaalde groep gebruikers.
Begrippen rond databases
13/70
Een extern schema zal uit een aantal views en relaties bestaan gebaseerd op het
conceptuele schema, maar de records in de views worden niet opgeslagen. Alleen
de definitie van een view wordt opgeslagen, het resultaat wordt telkens opnieuw
bepaald. Dit voorkomt redundantie en de bijbehorende mogelijke inconsistenties en
zorgt er ook voor dat wijzigingen aan gegevens meteen merkbaar zijn in de
resultaten van een view.
3.2.4 Data abstractie
De verschillende hierboven beschreven schema’s schermen de programmatuur af
van wijzigingen in de structuur van de gegevens en de manier waarop ze
opgeslagen worden.
De externe schema’s kunnen wijzigingen in het conceptuele schema opvangen.
Men spreekt van logische data onafhankelijkheid.
Het conceptuele schema kan dan weer wijzigingen in het fysieke schema
opvangen. Men spreekt van fysieke data onafhankelijkheid.
Gezien de voordelen van het gebruik van databases t.o.v. het werken met
bestanden, zal in het vervolg van de cursus vooral bekeken worden hoe gegevens
gestructureerd worden om ze op te slaan in een database. Dit is trouwens ook de
opslagvorm die tegenwoordig het meest gebruikt wordt.
Begrippen rond databases
14/70
4 DE VERSCHILLENDE STAPPEN BIJ HET ONTWERPEN
VAN EEN DATABASE
Welke verschillende stappen worden doorlopen om gegevens zoals ze
waargenomen worden in het dagelijkse leven om te zetten naar gegevens die
kunnen opgeslagen worden in een database en die kunnen beheerd worden met
een DBMS?
4.1
Stap 1: Analyse van de informatiebehoeften
Een eerste stap bij het ontwerpen van een database bestaat erin na te gaan wat
een gebruiker verwacht van de database.
De gebruiker beschrijft op een informele manier welke informatie hij via de
database wil kunnen verkrijgen. Een vertrekpunt kan de huidige situatie zijn met
daaraan gekoppeld welke veranderingen (verbeteringen) verwacht worden en
welke nieuwe elementen moeten toegevoegd worden. Ook overzichten en
formulieren die de gebruiker hanteert kunnen bijdragen tot een bepaling van de
informatiebehoeften.
Hieruit leidt de ontwerper af welke gegevens zullen opgeslagen worden, waar die
gegevens vandaan kunnen komen en welke toepassingen moeten ontwikkeld
worden om de gewenste informatie op te vragen.
Er wordt ook al nagegaan welk type operatie het meest van toepassing zal zijn in
de database (gegevens aanpassen, informatie opvragen, …) en welke prestaties er
verwacht worden (om welke hoeveelheden data gaat het?, binnen welke tijdspanne
moet de informatie ter beschikking zijn?)
4.2
Stap 2: Conceptueel database ontwerp
De informatie, verzameld in de vorige stap wordt omgezet in een meer formele
beschrijving van de gegevens: welke gegevens zullen opgeslagen worden, aan
welke voorwaarden moeten die gegevens voldoen,… Een manier van voorstellen
die hierbij dikwijls gebruikt wordt, is het ER model (entity relationship model).
4.3
Stap 3: Logisch database ontwerp
Er wordt een DBMS gekozen om het ontwerp te implementeren. Het conceptuele
model wordt vertaald in een database schema dat past in het datamodel van het
gekozen DBMS. Momenteel is een relationeel database model erg populair en daar
wordt in deze cursus dan ook het meest uitgebreid op in gegaan.
Andere modellen die in het verleden nogal gebruikt werden zijn het hiërarchische
model en het netwerkmodel.
Tegenwoordig komen aansluitend bij de evolutie naar programmeren in object
georiënteerde talen ook meer en meer object georiënteerde databases en object
relationele database systemen in gebruik.
4.4
Stap 4: Verdere verfijning van het schema
In deze verdere verfijning worden de relaties in het schema bepaald en wordt ook
nagegaan of het schema geen problemen kan opleveren zoals redundantie,
inconsistenties … Daar waar de vorige stappen eerder op een intuïtieve basis
Begrippen rond databases
15/70
werden uitgevoerd, bestaat voor deze stap een vaste techniek, normaliseren
genoemd.
4.5
Stap 5: Fysiek database ontwerp
In deze stap worden nog verdere wijzigingen doorgevoerd zoals het aanmaken van
indexen, eventueel het samenvoegen van tabellen,… om de belasting van het
systeem te minimaliseren en de prestaties te maximaliseren.
4.6
Stap 6: Ontwerp van de beveiliging
Ten slotte worden verschillende types gebruikers gedefinieerd op basis van wat ze
zullen doen met de database. Per type gebruiker wordt bepaald tot welk deel van
de database het toegang moet krijgen en tot welk deel van de database het geen
toegang mag hebben.
4.7
Opmerkingen
In de praktijk zal bij het ontwerp van een database dikwijls wel gestart worden met
het uitvoeren van deze zes stappen. Het resultaat zal echter telkens opnieuw
moeten getoetst worden aan de eisen van de gebruikers en aan de realiteit. Dit kan
betekenen dat een aantal stappen opnieuw moeten bekeken worden. Ook het
ontwikkelen van de applicaties in het DBMS kan aanleiding geven tot het opnieuw
uitvoeren van een aantal stappen.
In dit hoofdstuk werd een algemeen overzicht van de verschillende stappen
gegeven. In de volgende hoofdstukken komen deze stappen wat uitgebreider aan
bod.
Begrippen rond databases
16/70
5 DE INFORMATIEBEHOEFTEN
5.1
Hoe bepaal je de informatiebehoeften
De informatiebehoeften bepalen gebeurt grotendeels op basis van gesprekken met
de klant.
Daaruit moet blijken welke gegevens er ter beschikking zijn en hoe die gegevens
bekomen worden en ook welke overzichten, rapporten op basis van die gegevens
moeten opgemaakt worden.
Bij het bepalen van de informatiebehoeften gaan we uit van de formulieren,
rapporten en lijsten die binnen het bedrijf gebruikt worden.
Een voorbeeld
In een bedrijf worden de bestellingen van klanten geregistreerd op een bestelbon.
Bestelbonnummer:
Klantnummer:
Klantnaam:
Adres:
165
Datum:
Tel.:
98765
Piet Pieters
Pieperseweg 10
9876 - Bommerskonte
09/876 54 32
Leveringsdatum:
04/05/1997
Artikelnr
735
856
1142
Omschrijving
Keizerskroon - rood
Klimroos - Swan Lake
Dahlia's
24/04/1997
Aantal
20
15
30
Eenheidsprijs
3
10
5
Op basis van die bestelbonnen worden voor het magazijn pickinglists gemaakt. De
magazijnier gebruikt die pickinglists om de bestellingen van de klanten klaar te
maken.
Begrippen rond databases
17/70
Pickinglistnummer :
165
Leveringsdatum :
04/05/1997
Klantgegevens
Nr. Naam
98765 Piet Pieters
Artikelgegevens
Nr. Omschrijving
735 Keizerskroon - rood
856 Klimroos - Swan Lake
1142 Dahlia's
TOTAAL
12345 Jans Janssen
530
680
736
1142
1302
Krokussen - gemengd
Tulpen - botanische
Keizerskroon - geel
Dahlia's
Gladiolen
TOTAAL
Aantal
20
15
30
65
50
35
5
50
50
190
Uit deze beschrijving komen twee informatiebehoeften naar voren:
Bestelbonnen
Picking lists
5.2
Oefeningen
5.2.1 Een autoverhuurbedrijf
Een autoverhuurbedrijf met verschillende filialen verspreid over heel België, wil
overgaan tot automatisering van zijn gegevens.
Naar aanleiding van elke verhuring wordt een huurovereenkomst afgesloten. Elke
huurovereenkomst krijgt een uniek nummer. Uit de huurovereenkomst blijkt o.a. in
welk filiaal de auto gehuurd werd, welke auto er juist gehuurd werd, wie de auto
huurt en over welk rijbewijs die persoon beschikt, op welke datum de reservatie
gebeurde, gedurende welke periode de huurovereenkomst geldig is, in welk filiaal
de auto terug binnen gebracht wordt en wanneer.
Begrippen rond databases
18/70
AUTOVERHUUR HUUROVEREENKOMST 6956798
Datum reservering: 05-09-2001
Filiaalgegevens:
Klantnummer: 0078
Adres: Steenstraat 5, 2018 Antwerpen
Telefoon: 03 111 22 33
Nummer rijbewijs: K 866 065
Model: Opel Corsa
Te huren van: 07-09-2006
Afhaaltijd na 08.00 uur
Te betalen: € 225,Terugbrengen in filiaal: 53
Tankinhoud: Vol
Filiaal ID: 56
J. Janssens
Dorpsstraat 55
2140 Borgerhout
Klantnaam: Peeters
Categorie: ABE
Kenteken: AAA123
tot en met: 09-09-2006
Terugbrengtijd voor 19.00 uur
Terugbrengstatus: op tijd
Kilometerstand: 345678
Bij een reservatie wordt nagegaan aan de hand van een vlootlijst welke auto’s er ter
beschikking zijn en hoeveel de huurprijs bedraagt.
AUTOVERHUUR VLOOTOVERZICHT FILIAAL: 56
Leenautonr
1
7
16
17
Model
Opel Astra
Renault 88
Ford Kia
Ford Kia
Kenteken
ABC123
DEF456
GHI789
ABC234
Onderhoud
12500
15000
0
0
Verhuur
Uitgeleend
Op Voorraad
In onderhoud
Op voorraad
Dagtarief
€ 75
€ 80
€ 90
€ 90
Op regelmatige basis organiseert het bedrijf een reclamecampagne en stuurt dan
een reclamefolder naar al zijn klanten met een overzicht van de promoties en de
periode waarin ze van toepassing zijn.
Bepaal de informatiebehoeften van dit autoverhuurbedrijf.
5.2.2 Opleidingen bureautica
Bepaal de informatiebehoeften voor de hieronder beschreven situatie:
De VDAB heeft een opleidingsdienst voor eigen personeel. Elk jaar krijgen alle
personeelsleden een brochure waarin de opleidingen Bureautica staan die zij
kunnen volgen. De personeelsleden krijgen ook een inschrijvingsformulier waarop
zij de opleidingen kunnen noteren die ze wensen te volgen.
Zij moeten daarop naast hun persoonsgegevens, de inschrijvingsdatum, de afdeling
waarop ze werken en de locatie waar ze werken opgeven voor welke opleidingen
zij zich inschrijven. Hieronder een voorbeeld van dergelijk inschrijvingsformulier.
Begrippen rond databases
19/70
Inschrijvingsformulier Bureautica opleidingen voor VDAB personeel
Persoonsgegevens:
Naam:
Coppens
Inschrijvingsdatum:
Voornaam:
Robin
15/2/2006
Adres:
Dorp 6
Postcode:
1745
Gemeente:
Opwijk
Vestigingsgegevens:
Afdeling: Administratie
Training & Opleiding Bouw
Verantwoordelijke: De Paepe
Adres: Meulebroekstraat 52
Postcode: 9220
Gemeente: Hamme (O.-Vl.)
Ik wens mij in te schrijven voor volgende opleidingen:
Opleidingscode:
Opleidingsnaam:
5
Inleiding Word
8
Vervolmaking Word
9
PowerPoint
Het inschrijvingsformulier bezorgen ze aan de opleidingsdienst.
De opleidingsdienst stelt een planning samen op basis van de binnengekomen
inschrijvingsformulieren. Uit deze planning blijkt waar en wanneer een bepaalde
opleiding kan doorgaan, wie er ingeschreven is voor de opleiding en of die
kandidaten al uitgenodigd zijn, al bevestigd of geannuleerd hebben of gevraagd
hebben de inschrijving te verplaatsen naar een latere datum.
Begrippen rond databases
20/70
Planning opleidingen voor VDAB personeel
Opleidingscode: 5
Opleidingsnaam: Inleiding Word
Sessie: 1
Datums: 13/10/2003 - 14/10/2003 - 20/10/2003
Deze opleiding gaat door te:
Training & Opleiding Bureautica/Informatica
F.Rooseveltplaats 12
2000 Antwerpen
Uitgenodigd (U) - Bevestigd (B) - Annulatie (A) – Later(L)
Pnr.
6
17
58
32
50
29
76
91
83
81
Naam
Boelens Danny
Coppens Robin
Daelmans Karolien
De Ridder Francine
Delens Marc
Lissens Anne
Meert Piet
Plasmans Erwin
Van Der Straeten
Verhaeghen An
Adres
Gravenstraat 23
Dorp 6
Hoge Akker 19
St Janstraat 47
Populierenlaan
Kerkstraat 2
Oosthoek 23
Binneweg 2
Zeedijk 37
Straat 5
PN
9240
1745
9200
1785
9200
9200
9240
9255
8670
9200
Gemeente
Zele
Opwijk
Dendermon
Merchtem
Dendermon
Dendermon
Zele
Buggenhout
Koksijde
Dendermon
U
X
X
X
X
X
X
X
X
X
X
B
X
X
X
X
X
X
X
A
L
X
X
De personeelsleden krijgen vervolgens een uitnodiging voor de opleiding. In deze
brief wordt de inschrijving bevestigd en gemeld op welke dagen en op welke locatie
de opleiding doorgaat. Onderaan de brief zit een antwoordstrookje waarmee ze hun
deelname kunnen bevestigen of annuleren.
Op de laatste dag van de opleiding krijgen de personeelsleden ook nog een
evaluatieblad waarop zij een beoordeling over de opleiding geven.
De brochure die de personeelsleden krijgen en de inschrijvingsformulieren wil de
opdrachtgever behouden. Het is niet de bedoeling dat de personeelsleden
inschrijven voor een opleiding via Internet. Niet alle personeelsleden hebben
immers een PC. Het opzet is hier dat je de inschrijvingsformulieren invoert in de
PC. Het plannen van opleidingen, de uitnodigingen, aanwezigheidslijsten en
evaluatieformulieren zou dan geautomatiseerd kunnen gebeuren.
Begrippen rond databases
21/70
6 HET ENTITY RELATIONSHIP MODEL
In paragraaf 4.2 werd het entity relationship model (ER model) aangehaald als een
methode om de op een informele manier bekomen informatiebehoeften in een meer
formeel kleedje te gieten. Het resultaat is een gemakkelijk begrijpbaar diagram dat
ter ondersteuning van de communicatie met de klant kan gebruikt worden. Een
bijkomend voordeel is dat het diagram op een eenvoudige manier om te zetten is in
een relationele database.
Het entity relationship (E/R) model bekijkt de echte wereld als een constructie van
entiteiten (objecten) en associaties (relaties) tussen die entiteiten.
6.1
Entiteiten
Een entiteitwaarde is een betekenisvolle eenheid in een informatiebehoefte.
Een entiteitwaarde komt overeen met een object in de beschreven omgeving, een
object dat moet te onderscheiden zijn van andere objecten en waarover informatie
moet bijgehouden worden.
Zo komt een mier in een mierenhoop niet in aanmerking als entiteit als we de ene
mier niet van een andere kunnen onderscheiden.
Binnen een bedrijf kan een bepaalde werknemer wel overeen komen met een
entiteit.
Over een entiteit worden gegevens opgeslagen. Voor een werknemer kan dat zijn:
12002, Peeters, Jan, Dreef 85 2000 Antwerpen, 1 mei 1998, inkoop.
De opgesomde data worden de attribuutwaarden van de entiteitwaarde genoemd.
6.2
Entiteittype
Dikwijls is er in een omgeving sprake van een verzameling gelijkaardige entiteiten,
dat wordt dan een entiteittype genoemd.
We onderscheiden vijf soorten entiteittypes: rollen (docent, werknemer),
gebeurtenissen (cursus, vergadering), locaties (leslokaal), voorwerpen (computer,
boek) en concepten (organisatie, geldmarkt).
Gelijkaardig betekent in deze context entiteiten waarvan we dezelfde attributen
willen opslaan. Elk entiteittype krijgt een naam, b.v. werknemer.
Jan Peeters uit ons voorbeeld wordt dan een entiteitwaarde of entiteit van het
entiteittype werknemer.
Er bestaan geen uniforme afspraken i.v.m. het voorstellen van entiteiten in een E/R
diagram. Een mogelijke voorstelling (die aansluit bij de UML notatie) van een
entiteittype in een E/R diagram:
Begrippen rond databases
22/70
werknemer
persnr
achternaam
voornaam
adres
afdeling
in dienst
Indien het entiteittype voorkomt in een context waar de attributen minder belangrijk
zijn, kan je ook een verkorte voorstelling gebruiken.
werknemer
6.3
Attributen
De gegevens die i.v.m. een entiteit worden opgeslagen, worden attributen
genoemd. Er bestaat een grote diversiteit aan attributen in een E/R
gegevensmodel. Naast de naam van het attribuut kan eventueel ook het type
vermeld worden.
werknemer
persnr: integer
achternaam: string
voornaam: string
straat: string
afdeling: string
in dienst: datum
6.3.1 Atomaire attributen.
Een atomair attribuut van een entiteit kan per entiteit ten hoogste één enkele
waarde hebben. Zo is achternaam een atomair attribuut van een werknemer omdat
elke werknemer ten hoogste één achternaam heeft en een achternaam niet verder
op te delen is.
In een E/R diagram wordt ervan uitgegaan dat een attribuut atomair is als er geen
verdere aanduiding bijkomt.
6.3.2 Meerwaardige attributen of repeterende attributen
In tegenstelling tot atomaire attributen zijn er ook repeterende attributen: zij kunnen
per entiteit meerdere waarden krijgen.
Zo kan een werknemer deelnemen aan meerdere projecten binnen een bedrijf.
Project wordt dan een repeterend attribuut.
Begrippen rond databases
23/70
werknemer
Achternaam
Project [0…*]
Achternaam is een atomair attribuut, project is een meerwaardig attribuut, elke
werknemer kan betrokken zijn bij meerdere projecten met een minimale waarde
gelijk aan 0. Dit betekent dat niet elke werknemer moet deelnemen aan één of
ander project.
Als een hele groep attributen repetitief is, dan spreken we van een repeterende
groep.
6.3.3 Samengestelde attributen
Een samengesteld attribuut koppelt aan een entiteit een lijst van waarden. Die lijst
heeft een bepaalde volgorde en lengte. De adresgegevens van een werknemer is
een voorbeeld van een samengesteld attribuut omdat het op zijn beurt uit vier
waarden bestaat, nl. straat, huisnummer, postcode, woonplaats.
werknemer
adres
Achternaam: string
Adresgegevens: adres
Project [0…*]: string
straat:string
huisnummer: integer
postcode
gemeente
6.3.4 Procesattributen
De waarde van een procesattribuut is het resultaat van een berekening.
Zo is de anciënniteit van een werknemer een procesgegeven. Zij kan immers
berekend worden op basis van de datum van indiensttreding.
In de meeste gevallen zal een procesattribuut niet effectief opgenomen worden in
de database.
6.3.5 Het domein van een attribuut
Bij elk attribuut hoort een verzameling mogelijke waarden voor dat attribuut. Dit
wordt het domein van het attribuut genoemd.
Voor de datum van indiensttreding kan het domein b.v. beperkt worden tot alle
geldige datums die vallen tussen de datum van oprichting van het bedrijf en de
datum van vandaag.
6.4
Primaire sleutel (primary key)
Een sleutel is een minimale verzameling attributen die een entiteitwaarde uniek
identificeert binnen een entiteittype.
Een sleutel moet dus aan twee voorwaarden voldoen:
• een entiteitwaarde identificeren
Begrippen rond databases
24/70
•
uit zo weinig mogelijk attributen bestaan.
Het kan gebeuren dat meerdere verzamelingen van attributen als sleutel in
aanmerking komen. Elke dergelijke verzameling is dan een kandidaat sleutel.
In het voorbeeld van de werknemers is het attribuut personeelsnr een kandidaat
sleutel. Elke werknemer heeft immers een uniek personeelsnummer.
De combinatie personeelsnr, indienst identificeert ook een enkele werknemer, maar
is geen kandidaat sleutel omdat het aantal attributen niet minimaal is.
Een achternaam is dan weer geen kandidaat sleutel omdat verschillende
werknemers dezelfde achternaam kunnen hebben.
Binnen elke entiteit zal één van de kandidaat sleutels effectief als sleutel gekozen
worden. Dit wordt dan de primaire sleutel en die wordt in het schema aangeduid
door de naam van het attribuut te onderlijnen.
werknemer
Persnr: integer
achternaam: string
voornaam: string
straat: string
afdeling: string
in dienst: datum
6.5
Associaties
Associaties of relaties definiëren een verband tussen de verschillende entiteittypes.
Om verwarring te vermijden met het begrip relaties in een relationele database
wordt in deze syllabus in de context van E/R diagrammen systematisch over
associaties gesproken.
Bekijken we eventjes opnieuw het entiteittype werknemer. Het kan zijn dat het
volstaat te weten voor welke afdeling een werknemer werkt en aan welke projecten
hij meewerkt.
In sommige gevallen zal het echter ook nodig zijn over die afdelingen of over die
projecten zelf informatie op te slaan.
Voor een afdeling kan dat zijn de locatie, het afdelingshoofd, enz.
Voor een project kan dat zijn een begindatum, een voorziene einddatum, een
effectieve einddatum, een budget, de projectverantwoordelijke, …
In dat geval volstaat het niet afdeling op te nemen als attribuut van een werknemer,
maar wordt elke afdeling een entiteit op zichzelf van het entiteittype afdeling. Het
feit dat Jan Peeters op de afdeling inkoop werkt wordt dan uitgedrukt als een
associatie tussen de entiteit Jan Peeters en de entiteit Inkoop.
In een E/R diagram worden de associaties tussen de verschillende entiteittypes als
volgt voorgesteld:
Begrippen rond databases
25/70
Werkt in
werknemer
afdeling
Werkt mee aan
project
6.5.1 Kardinaliteit en optionaliteit van een associatie
Op basis van het aantal entiteiten (objecten, elementen) dat in elk entiteittype
betrokken is bij de associatie, spreken we over de kardinaliteit en de optionaliteit
van een associatie.
Kardinaliteit betekent in deze context het maximaal, optionaliteit het minimaal
aantal entiteiten dat betrokken kan zijn bij de associatie.
Anders gezegd, kardinaliteit gaat over het aantal pijlen dat vertrekt of aankomt,
optionaliteit of het al dan niet verplicht is dat er een pijl vertrekt uit of aankomt in
elke entiteit.
Op basis van kardinaliteit onderscheiden we drie gevallen:
•
•
•
1-op-1-associatie
1-op-n-associatie of n-op-1associatie
m-op-n-associatie
6.5.1.1
1-op-1-associatie
Met één entiteit uit de ene verzameling komt maximaal één entiteit uit de andere
verzameling overeen.
Een getal bij de associatie aan de kant van het entiteittype geeft de cardinaliteit van
de associatie aan.
werknemer
werkstation
gebruikt
1
1
Begrippen rond databases
26/70
Dit wordt gelezen als: elke werknemer kan slecht op één werkstation aanmelden
(1), elk werkstation is toegewezen aan juist één werknemer (1).
De optionaliteit van de associatie bepaalt of het ook mogelijk is dat er entiteiten in
één van de verzamelingen zitten die niet bij de associatie betrokken zijn. Als er ook
werknemers zijn die niet op de computer werken levert dit het volgende diagram:
gebruikt
0,1
werknemer
1
werkstation
Interpretatie: elke werknemer werkt op hoogstens 1 (0 of 1) computer. Op elke
computer werkt juist 1 werknemer.
In termen van relaties zoals die in de wiskunde gebruikt worden: in de verzameling
met werknemers vertrekken uit elke werknemer 1 of 0 pijlen, in de verzameling met
werkstations komt in elk werkstation juist één pijl aan.
6.5.1.2
1-op-n-associatie of n-op-1associatie
Met één entiteitwaarde uit de ene verzameling komen één of meerdere
entiteitwaarden uit de andere verzameling overeen.
1:n associaties komen veel voor. De associatie “…werkt in…” tussen werknemers
en afdelingen kan een 1:n associatie zijn als we ervan uitgaan dat een werknemer
slechts voor één afdeling (1) kan werken. In elke afdeling kunnen uiteraard
meerdere werknemers werken (1...*).
werknemer
afdeling
werkt in
1 *
1
De optionaliteit bepaalt ook hier of elke entiteit van een entiteittype bij de associatie
betrokken is. In het voorbeeld van de werknemers en de afdelingen, kan het b.v.
dat de algemene directeur niet aan een bepaalde afdeling verbonden wordt, in dat
geval staat er aan de kant van afdeling 0,1.
6.5.1.3
m-op-n-associatie
Met één of meerdere entiteitwaarden uit de ene verzameling komen één of
meerdere entiteitwaarden uit de andere verzameling overeen.
Begrippen rond databases
27/70
Een voorbeeld
Binnen een bedrijf lopen projecten. Aan elk project neemt ten minste één
werknemer deel. Er kunnen ook meerdere werknemers bij een project betrokken
zijn. Omgekeerd hoeft een werknemer niet bij een project betrokken te zijn, maar
kan hij net zo goed aan meerdere projecten meewerken.
6.5.2 Recursieve associaties
Een associatie kan entiteiten van een type relateren aan entiteiten van hetzelfde
type. Dit noemt men een recursieve associatie.
Een voorbeeld van een recursieve associatie in de verzameling werknemers: “…is
chef van…”.
Is chef van
0...1
werknemer
6.5.3 Attributen van een associatie
Een associatie kan op haar beurt attributen hebben, b.v. als het ook nodig is te
weten sinds wanneer een werknemer op een bepaalde afdeling werkt, dan hoort dit
attribuut niet bij de werknemer en evenmin bij de afdeling. Het attribuut hoort bij de
associatie.
werknemer
1…*
Werkt in
sinds: datum
Begrippen rond databases
1
afdeling
28/70
6.6
De voorbeelden samengevat
6.6.1 Beschrijving
Een bedrijf bestaat uit meerdere afdelingen.
Elke afdeling heeft een afdelingshoofd en ten minste één werknemer.
Elke werknemer wordt toegewezen aan ten minste één, maar misschien ook aan
meerdere afdelingen.
Binnen het bedrijf worden projecten uitgevoerd. Met elk project is ten minste één
werknemer verbonden. Een werknemer kan aan geen enkel project toegewezen
zijn, b.v. als hij met vakantie is.
Elke afdeling, elk project, elke werknemer en elk afdelingshoofd heeft een naam.
6.6.2 Entiteiten
•
Afdeling
•
Werknemer
•
Afdelingshoofd
•
Project
6.6.3 Associaties zoeken
Om de associaties tussen de entiteiten te vinden kan een tabel van pas komen.
Zowel de kolom- als de rijhoofdingen bestaan uit de verschillende entiteiten.
Afdeling
Werknemer
stelt te werk
Afdeling
Werknemer
is toegewezen
aan
Afdelingshoofd leidt
Project
Afdelingshoofd Project
wordt geleid
door
werkt aan
wordt mee
uitgewerkt
door
Begrippen rond databases
29/70
6.6.4 Een eerste E/R diagram
Geleid door
afdeling
afdelingshoofd
Stelt te werk
Werkt aan
werknemer
project
6.6.5 De cardinaliteiten bepalen
•
Elke afdeling heeft juist één afdelingshoofd
•
Een afdelingshoofd leidt juist één afdeling
•
Elke afdeling stel ten minste één werknemer te werk
•
Elke werknemer is toegewezen aan ten minste één afdeling
•
Aan elk project werkt ten minste één werknemer
•
Een werknemer neemt deel aan 0 of meer projecten
Weergave in het diagram
afdeling
afdelingshoofd
geleid door
1
1
werkt voor
1…*
1…*
werknemer
project
werkt aan
1…*
0…*
Begrippen rond databases
30/70
6.6.6 Attributen
afdeling
afdelingsnaam
6.7
werknemer
afdelingshoofd
personeelsnr
naam
personeelsnr
naam
project
projectnr
projectnaam
Zwakke entiteittypes
Een zwakke entiteit is een entiteit die aan de hand van haar eigen attributen niet
uniek kan geïdentificeerd worden. Zij moet daarom voor haar primaire sleutel zowel
een beroep doen op haar eigen attributen als op die van een ander entiteittype
waarmee ze verbonden is.
Familieleden van een werknemer kan een voorbeeld zijn van een zwakke entiteit.
Stel dat een werknemer een hospitalisatieverzekering kan afsluiten, ook voor zijn
familieleden.
Naar aanleiding daarvan moeten de naam, de leeftijd en de relatie met de
werknemer van elk betrokken familielid gekend zijn. Deze gegevens hebben op
zichzelf geen zin, maar krijgen pas een betekenis in combinatie met de werknemer.
De werknemer wordt dan de identificerende eigenaar van de zwakke entiteit.
In een E/R diagram krijgt een zwakke entiteit een dubbele rand.
familielid
6.8
Hiërarchieën van entiteiten
Een hiërarchie van entiteiten is een gestructureerde verzameling van entiteiten. De
entiteiten in de verzameling hebben enerzijds een aantal attributen
gemeenschappelijk, anderzijds verschillen ze ook in een aantal attributen.
Een voorbeeld
Alle werknemers, zowel arbeiders als bedienden, hebben een personeelsnummer,
een naam, een geboortedatum, een datum van indiensttreding, … Dit zijn de
gemeenschappelijke attributen.
Van arbeiders wordt verder geregistreerd hoeveel uur ze wekelijks werken en
hoeveel ze verdienen per uur.
Van bedienden wordt een maandloon opgeslagen.
Dit zijn de attributen waarin ze verschillen.
Alle gemeenschappelijke attributen komen bij het supertype, in ons voorbeeld
werknemer.
Alle attributen die verschillen worden opgenomen in het subtype, in ons voorbeeld
arbeider en bediende.
Begrippen rond databases
31/70
werknemer
persnr
naam
geboortedatum
indienst
…
arbeider
bediende
uurloon
maandloon
aantal uur
Elk subtype neemt ook de primaire sleutel van het supertype over. Subtypes die als
enig attribuut die primaire sleutel hebben, worden verwijderd uit het diagram. Er
bestaat een 1-op-1 associatie tussen het supertype en het subtype.
In het voorbeeld van de werknemer zijn de subtypes disjunct. Subtypes kunnen
echter ook een doorsnede hebben.
Een ander voorbeeld
Binnen een opleidingsinstelling zouden we als supertype de entiteit persoon
kunnen hebben. Student, docent en staf komen dan in aanmerking als subtypes.
Iemand kan echter tegelijkertijd docent zijn en deel uitmaken van de staf. Dit is een
voorbeeld van niet disjuncte subtypes.
Begrippen rond databases
32/70
persoon
student
staf
docent
Nog enkele spelregels i.v.m. overerving:
Elk entiteitwaarde van een supertype moet ook in een subtype terug te vinden zijn.
Een subtype kan maar bij één supertype horen. Hiërarchieën mogen wel genest
worden, m.a.w. een subtype kan op zijn beurt supertype zijn voor een ander
subtype.
6.9
Een entiteit of een attribuut?
Het is niet altijd duidelijk of een gegeven een entiteit of een attribuut moet worden.
Enkele vuistregels bij het maken van een keuze:
De adresgegevens van een werknemer.
Voorzien we een attribuut adres bij de entiteit werknemer?
Of maken we een aparte entiteit adres en een associatie “…heeft als adres…”
tussen de entiteiten werknemer en adres?
Overwegingen die hierbij een rol spelen:
Heeft elke werknemer slechts één adres of kan het zijn dat er meerdere adressen
van een werknemer moeten geregistreerd worden?
In het eerste geval zal het eerder aangewezen zijn adres als een attribuut te
beschouwen, in het tweede geval wordt adres een aparte entiteit.
Is de structuur van het attribuut belangrijk? Zullen we ook al eens werknemers op
basis van postcode of land willen selecteren? Zo ja, is het ook aangewezen een
apart attribuut adres te voorzien.
Begrippen rond databases
33/70
6.10 Oefeningen
6.10.1
Een factuur
Je ontwerpt een entiteittype factuur op basis van de hieronder afgebeelde factuur.
Groothandel ABC
Dreef 55
1234 Destad
Nummer
Naam:
Adres:
Woonplaats:
Tel :
Factuurnr: 080350ST
Datum: 31/03/2008
5678
Piet Peeters
Park 70
1234 Destad
03 123 45 67
Artikelnr
Omschrijving
I2200
D3000
P962
Notebook
Desktops
Printer
Aantal
Prijs
Bedrag
5
12
1
799
399
145
3995,00
4788,00
145,00
Subtotaal
Korting
Totaal excl BTW
21% BTW
Totaal
8.928,00
100,00
9.028,00
1895,88
10.923,88
Geef voor dat entiteittype factuur:
•
Een vijftal atomaire attributen
•
Drie procesattributen
•
Drie meerwaardige attributen
•
Een primaire sleutel
6.10.2
Associaties, kardinaliteit en optionaliteit
Er worden telkens twee entiteittypes gegeven. Geef een beschrijving van een
mogelijke associatie tussen die entiteittypes en bespreek ook de cardinaliteit en de
optionaliteit van deze associaties.
bankbediende
klant
driewieler
wiel
film
regisseur
Begrippen rond databases
34/70
6.10.3
Hiërarchische structuren
Bedenk een mogelijke hiërarchische structuur van entiteittypes die te maken
hebben met voertuigen.
6.10.4
Recursieve associatie
Geef een ander voorbeeld van een recursieve associatie dan dat van paragraaf
6.5.2.
6.10.5
Ziekenhuis
Bedenk een vijftal entiteittypes die je in een ziekenhuisomgeving kunt tegenkomen.
Vermeld bij elk entiteittype of het om een rol, een gebeurtenis, een locatie, een
voorwerp of een concept gaat.
Bedenk ook een aantal zinvolle attributen voor elk van deze entiteiten.
Kies een primaire sleutel voor elk entiteittype.
Stel deze entiteittypes en hun eventuele associaties voor in een E/R diagram.
6.10.6
Domein
Bepaal het domein voor een attribuut postcode in België.
Begrippen rond databases
35/70
7 LOGISCH DATABASEONTWERP
Klassieke gegevensmodellen binnen een DBMS zijn
• Het hiërarchische model
•
Het netwerk model
•
Het relationele model.
Het verschil tussen de drie modellen die hier besproken worden, gaat vooral over
de wijze waarop associaties tussen entiteiten worden vastgelegd. De nadruk wordt
gelegd op het relationele model, omdat dat vandaag nog veel gebruikt wordt. De
beschrijving van het hiërarchische en van het netwerkmodel zijn eerder
volledigheidshalve toegevoegd.
Verderop in de cursus wordt ook nog ingegaan op object georiënteerde databases
en object relationele databases die dan weer nauw aansluiten bij object
georiënteerde programmeertalen.
7.1
Het hiërarchische gegevensmodel
Dit model dateert van het midden van de jaren 60 en is geïnspireerd op de
hiërarchische structuur die dikwijls gehanteerd wordt om situaties beter te
begrijpen. Denk hierbij b.v. aan de schema’s die ontwikkeld werden om de planten
en dierenwereld verder in te delen of aan de indeling van de talen.
Elk entiteittype uit het E/R model komt in een hiërarchische database overeen met
een recordtype. Per entiteitwaarde wordt een record gecreëerd. Elk record heeft
attributen.
Omdat 1:n associaties veel voorkomen binnen een gegevensmodel, is voor het
hiërarchische model als uitgangspunt genomen dat deze associaties gemakkelijk
gemodelleerd moeten kunnen worden. De 1 entiteit komt aan de top van de
hiërarchie en daaraan worden de geassocieerde entiteiten opgehangen. Dit noemt
men een vader / zoon relatie. De 1 entiteit is hierin de vader, de records aan de
andere kant zijn de zonen.
operatieafdeling
peeters
janssens
gynaecologie
orthopedie
symens
kaiblinger
verelst
martens
dielman
dhooghe
In een voorbeeld van werknemers die op een afdeling werken zou dat er als volgt
kunnen uitzien:
Een 1:n associatie houdt in dat een werknemer niet op meerdere afdelingen kan
werken.
Begrippen rond databases
36/70
Bij een n:m associatie, zoals een werknemer die meewerkt aan meerdere projecten
moet een entiteit gekozen worden om bovenaan in de structuur te zetten.
Je kunt de structuur dan opbouwen uitgaande van de werknemers en aan elke
werknemer alle afdelingen koppelen waarvoor hij werkt of je kunt de structuur
opbouwen uitgaande van de projecten en aan elk project een lijst koppelen van de
werknemers die eraan mee werken.
Nadelen van dit model zijn:
Een werknemer wordt op een heel andere manier benaderd dan een afdeling. Een
afdeling kan men direct terugvinden, om een werknemer terug te vinden moet men
eerst de afdeling opsporen waarop de werknemer werkt om daarna de werknemer
zelf te zoeken.
Een nieuwe werknemer kan niet zonder meer toegevoegd worden. Eerst moet er
een afdeling zijn waaraan de werknemer kan gekoppeld worden.
Bij n:m associaties treedt redundantie op. Als aan elke medewerker de projecten
gekoppeld worden waaraan hij deelneemt, dan zullen de gegevens van een project
teruggevonden worden bij elke werknemer die eraan meewerkt. Worden de
werknemers opgeslagen per project, dan zullen de gegevens van een werknemer
teruggevonden worden bij elk project waaraan hij meewerkt.
7.2
Het netwerk gegevensmodel
Het netwerk gegevensmodel dateert van het einde van de jaren 60, was vooral
populair in de jaren 80 en neemt een aantal nadelen van het hiërarchische model
weg.
Ook hier komt elk entiteittype overeen met een recordtype en elke entiteitwaarde
met een record. Alle records worden echter afzonderlijk opgeslagen. In de beide
andere modellen is dit niet het geval: in het hiërarchische model worden de records
opgeslagen als één groep per gekozen parent, in het relationele model (zie verder)
in groepen per soort.
Een 1:n associatie wordt omgezet in een verzamelingtype. Elk verzamelingtype
heeft drie kenmerken: een naam, een eigenaar en leden.
Verschillen tussen een verzameling uit de wiskunde en een verzameling uit het
netwerkmodel:
•
In de wiskunde zijn alle elementen van een verzameling gelijkwaardig, in het
netwerkmodel is er een bijzonder element, nl. de eigenaar.
•
Verder zijn de elementen van een verzameling niet geordend, de leden van een
verzameling in het netwerkmodel staan wel in een bepaalde volgorde.
Om die volgorde te definiëren verwijst een pointer van de eigenaar naar het eerste
lid, bij het eerste lid verwijst de pointer dan naar het tweede lid, bij het tweede naar
het derde enz. De pointer van het laatste lid verwijst terug naar de eigenaar.
Denken we aan het voorbeeld met de werknemers en de afdelingen. De naam van
het verzamelingtype zou dan kunnen zijn werkt in, de eigenaar één of andere
afdeling en de leden alle werknemers die in het departement werken. Er geldt wel
als bijkomende beperking, omdat het een 1:n associatie is, dat elke werknemer
maar als lid van één afdeling kan teruggevonden worden.
Begrippen rond databases
37/70
Om een m:n relatie voor te stellen, wordt tussen de elementen van de twee
entiteiten een extra record gedefinieerd dat de link tussen de twee elementen legt.
Bekijken we de volgende n:m relatie tussen werknemers en projecten:
Gaan we uit van een situatie met vier werknemers W1, … W4 en drie projecten P1,
…, P3
Veronderstel dat werknemers deelnemen aan de projecten op de volgende manier:
(W1,P2) = B1
(W2,P1) =B2
(W3,P2) = B5
(W2,P2) =B3
(W3,P3) = B6
(W2,P3) = B4
Als we dit gegroepeerd per project bekijken levert dat:
(W4,P3) = B7
(W2, P1) = B2
(W1,P2) = B1
(W2,P3,) = B4
(W2,P2) = B3
(W4,P3) = B7
(W3,P2) = B6
Er worden dan zeven tussenliggende records gecreëerd die b.v. als naam … is
betrokken bij … (B) kunnen krijgen met bijbehorende verwijzingen:
W1 verwijst naar B1, B1 verwijst terug
naar W1
P1 verwijst naar B2 en B2 verwijst terug
naar P1
W2 verwijst naar B2, B2 verwijst naar
B3, B3 naar B4 en B4 terug naar W2
P2 verwijst naar B1, B1 verwijst naar
B3, B3 verwijst naar B6 en B6 verwijst
terug naar P2
W3 verwijst naar B5, B5 naar B6 en B6
terug naar W3
P3 verwijst naar B4, B4 naar B7 en B7
terug naar P3
W4 verwijst naar B7 en B7 terug naar
W4
Dit vangt het probleem van de redundantie bij het hiërarchisch model op, een
belangrijk nadeel is echter dat het zoeken in een netwerkdatabase bepaald niet
eenvoudig is.
7.3
Het relationeel gegevensmodel
7.3.1 De omzetting van entiteiten en attributen
Dit model werd rond 1970 ontwikkeld door E.F. Codd. De bedoeling was vooral de
complexiteit van de programma’s voor hiërarchische en netwerkdatabases te
verminderen. Een belangrijk probleem bij deze modellen: de gegevens zijn alleen
via vooraf gedefinieerde paden toegankelijk. In een relationeel model ontstaat een
vrije keuze van toegang door het definiëren van joins.
In het relationeel gegevensmodel wordt elk entiteittype omgezet in een tabel.
Dergelijke tabel wordt ook wel een relatie genoemd en mag in die context niet
verward worden met een relatie of associatie uit het E/R model 1. De attributen die
horen bij het entiteittype worden teruggevonden als kolomhoofdingen in de tabel.
1
Dit is de reden dat in deze syllabus in de context van E/R diagrammen systematisch over
associaties gesproken werd.
Begrippen rond databases
38/70
Omzetting van het entiteittype werknemer:
werknemer
persnr
achternaam
voornaam
adres
afdeling
in dienst
Persnr
Anaam
Vnaam
Adres
In dienst
Afdeling
Elke rij in de tabel correspondeert met een entiteit en wordt in deze context ook wel
een tupel genoemd.
Persnr
12002
Anaam
Peeters
Vnaam
Jan
Adres
Dreef 85 2000 Antwerpen
In dienst
1 mei 1998
Afdeling
inkoop
7.3.2 De omzetting van een 1:n associatie
De vertaling van een 1:n associatie gebeurt door de primaire sleutel van het
entiteittype aan de 1-kant van de associatie op te nemen als attribuut in het
entiteittype aan de veelkant van de associatie.
afdeling
werknemer
persnr
naam
adres
geboortedatum
indienst
…
werkt in
1…*
afdelingsID
afdelingsnaam
afdelingshoofd
locatie
…
1
Dit E/R diagram wordt op de volgende manier omgezet naar relaties is een
relationele database:
De tabel werknemer krijgt als kolommen de attributen van werknemer en het
afdelingid van de afdeling waarvoor hij werkt.
Persnr
Naam
Adres
Geboortedatum
Indienst
AfdelingsID
Een tabel afdeling met als kolommen de attibuten van de het entiteittype afdeling.
AfdelingsID
Afdelingsnaam
Afdelingshoofd
Locatie
AfdelingID in de tabel werknemer wordt een foreign key genoemd.
Een foreign key (vreemde sleutel) is de verbindende schakel tussen twee tabellen.
Met een waarde uit een rij van de ene tabel kun je in een andere tabel de juiste rij
met gerelateerde gegevens opzoeken.
De ene tabel geeft als het ware de sleutel voor de andere, 'vreemde' tabel.
Begrippen rond databases
39/70
7.3.3 De omzetting van een m:n associatie
Een m:n associatie kan niet rechtstreeks vertaald worden in een relationele
database, maar wordt omgezet naar twee 1:n associaties.
De associatie tussen de entiteittypes werknemer en project kan een n:m associatie
zijn. Immers een werknemer kan deelnemen aan meerdere projecten en aan een
project kunnen meerdere werknemers deelnemen. In een relationele database zal
dit aanleiding geven tot drie tabellen of relaties: een tabel werknemer met als
kolommen de attributen van een werknemer, een tabel project met als kolommen
de attributen van een project en een tabel medewerker van een project met als
kolommen de primaire sleutel van werknemer en de primaire sleutel van project.
project
werknemer
persnr
naam
adres
geboortedatum
indienst
…
1…*
neemt deel aan
1…*
projectid
projectnaam
projectleider
budget
…
wordt omgezet in
een relatie werknemer
Persnr
Naam
Adres
Geboortedatum Indienst
en een relatie project
Projectid
Projectnaam
Projectleider
budget
En een relatie medewerker aan een project
Projectid
persnr
7.3.4 De omzetting van een associatie met eigen attributen
Een associatie met eigen attributen in een E/R diagram wordt omgezet in een
aparte tabel (relatie).
Deze relatie krijgt volgende kolommen
• De primaire sleutel van elke entiteit die betrokken is bij de associatie
•
De attributen die de associatie beschrijven
De combinatie van primaire sleutels van de betrokken entiteiten wordt een
kandidaatsleutel voor de relatie.
Begrippen rond databases
40/70
Het E/R diagram van een werknemer die verbonden is aan een afdeling:
afdeling
werknemer
persnr
naam
adres
geboortedatum
indienst
…
werkt in
1…*
1
afdelingID
afdelingsnaam
afdelingshoofd
locatie
…
sinds
wordt vertaald naar volgende tabel of relatie:
persnr
afdelingID
sinds
7.3.5 Integriteit van de gegevens
Een degelijk DBMS zal ook controles uitvoeren die de integriteit van de gegevens
garanderen.
7.3.5.1
Entiteit integriteit
Voor elke entiteitwaarde moet een waarde voor de primaire sleutel bestaan die
uniek is en niet null.
Dit is een rechtstreeks gevolg van het feit dat de primaire sleutel de entiteit uniek
moet identificeren.
7.3.5.2
Referentiële integriteit
Zoals vermeld in paragraaf “7.3.2 De omzetting van een 1:n associatie”, creëert een
foreign key een associatie tussen twee entiteiten. De entiteit met de foreign key is
de afhankelijke entiteit, de entiteit met de primaire sleutel de parent entiteit.
Om zinloze verwijzingen te vermijden, moet elke vreemde sleutel (foreign key)
verwijzen naar een primaire sleutel van een bestaande entiteit in het parent
entiteittype.
Dit betekent dat een aantal controles moeten uitgevoerd worden zowel bij het
toevoegen van records aan de afhankelijke tabel als bij het verwijderen van records
uit de parent tabel.
Bij het toevoegen van een record aan de afhankelijke relatie zal het systeem
controleren of de waarde die als foreign key gebruikt wordt, wel degelijk voorkomt
als primaire sleutel van een record in de parent tabel.
Begrippen rond databases
41/70
Bij het verwijderen van een record uit de parent tabel zal het systeem controleren of
er geen records meer aanwezig zijn in de afhankelijke tabel waarin de waarde van
de primaire sleutel van het verwijderde record als foreign key gebruikt wordt.
Bij de werknemers en de afdelingen komt het erop neer dat de waarde die voor
afdelingsID ingevuld wordt in het record van een werknemer alleen dan aanvaard
wordt als die waarde ook voorkomt als primaire sleutel van een record in de tabel
afdelingen en als een afdeling verwijderd wordt uit de tabel afdelingen zal dat alleen
lukken als de afdelingsID van die afdeling bij geen enkele werknemer voorkomt als
foreign key.
7.4
Oefeningen
7.4.1 Film
Het is de bedoeling een database te ontwikkelen met gegevens over films. Er wordt
gedacht aan een entiteit film en een entiteit acteur.
Welke associatie kan er bestaan tussen deze twee entiteiten.
Bespreek de kardinaliteit en de optionaliteit van deze associatie.
Hoe zal die associatie vertaald worden in een relationele database?
7.4.2 Bibliotheek
In een bibliotheek maken o.a. de volgende drie tabellen deel uit van een relationele
database:
Een tabel met de gegevens van de lezers zoals hun naam en adresgegevens. Elke
lezer wordt geïdentificeerd via een lezersnummer.
Een tabel met gegeven i.v.m. de boeken zoals het ISBN nummer, de titel , de
auteur, de uitgever, enz. Elk boek wordt geïdentificeerd via een boeknummer.
Een tabel met ontleningen waarin geregistreerd wordt welke lezer welk boek
ontleent evenals de startdatum van de ontlening en een datum waarop het boek
terug binnen moet gebracht worden.
Welke relaties zullen er bestaan tussen deze drie tabellen, vermeld de naam van
de sleutelvelden en de kardinaliteit.
Formuleer wat het betekent als er referentiële integriteit wordt afgedwongen voor
deze associaties.
Begrippen rond databases
42/70
8 VERDERE VERFIJNING VAN HET SCHEMA:
NORMALISATIE
Het is belangrijk dat in een database geen redundanties en geen inconsistenties
optreden. Een techniek die ontwikkeld werd door Codd om dit te vermijden in een
relationele database is het normaliseren van de gegevens.
8.1
Problemen met redundantie en inconsistentie
Met de stappen die we tot nog toe ondernomen hebben, kan er nog altijd
redundantie optreden in onze tabellen (relaties).
Redundantie geeft aanleiding tot allerlei problemen:
Het in beslag nemen van overbodige opslagruimte en het verbruiken van
mankracht voor het invoeren van die dubbele gegevens.
Problemen bij het wijzigen van gegevens. Alle kopieën van de gegevens moeten
gelijktijdig aangepast worden of dit geeft aanleiding tot inconsistenties.
Problemen bij het verwijderen van gegevens. Het verwijderen van bepaalde
gegevens kan meteen het ongewild verwijderen van andere data tot gevolg
hebben.
Problemen bij het toevoegen van gegevens. Sommige gegevens zullen pas kunnen
toegevoegd worden als eerst andere niet gerelateerde gegevens toegevoegd zijn.
8.1.1 Een illustratie
Veronderstel dat we informatie ivm met bestellingen in de database willen opslaan
en dat we de gegevens als volgt structureren:
Klantnaam
Klantadres
Jan Jansens
Dreef 20
2000 Antwerpen
Laar 50
1000 Brussel
Plein 70
9000 Gent
Laar 50
1000 Brussel
Markt 34
2000 Antwerpen
Peter Peeters
Elke Dhooghe
Peter Peeters
Sim Pauwels
Besteldatum
9/12/2005
Artikel
Emmer
Hoeveel
heid
20
EenheidsPrijs
2,5
Totaal
50
10/12/2005
Dweil
50
1,5
75
15/12/2005
Borstel
25
2,0
50
10/12/2005
Spons
40
1,5
60
20/12/2005
Dweil
30
1,5
45
8.1.2 Problemen
Er is redundantie: zowel de adresgegevens van Peter Peeters (en meer algemeen
van elke klant die bestellingen plaatst) als de gegevens over dweilen (en meer
algemeen over alle artikelen die door meerdere klanten besteld worden), komen
meerdere keren voor.
8.1.3 Gevolgen
Als Peter Peeters verhuist, moet zijn adres meerdere keren aangepast worden. Dit
betekent niet alleen onnodig werk, maar geeft ook aanleiding tot fouten. Immers als
Begrippen rond databases
43/70
ergens een wijziging over het hoofd gezien wordt, is het onmogelijk achteraf op
basis van de tabel nog te achterhalen wat nu het juiste adres is van de klant.
Zo vind je ook eenzelfde artikel op verschillende plaatsen terug met analoge
problemen bij b.v. een prijsverandering.
Het is onmogelijk de prijs van een nieuw artikel op te slaan als nog geen enkele
klant dat artikel besteld heeft.
Als je een klant wilt verwijderen en hij is de enige die tot nog toe een bepaald artikel
besteld heeft, dan is meteen ook de informatie over het artikel weg.
8.2
De normalisatieprocedure
8.2.1 Voorbereiding tot normalisatie
8.2.1.1
Procedure
•
Maak een lijst van alle attributen van een document (of lijst) dat het resultaat
is van een proces. Voorbeelden: documenten, lijsten op papier of scherm,
invulformulieren op het scherm. We werken document per document,
scherm per scherm. Pas na de derde normaalvorm voegen we alle
resultaten samen tot één structuur.
•
Controleer of er homoniemen of synoniemen voorkomen en pas gekozen
namen aan indien nodig.
Een homoniem is eenzelfde woord dat naar twee verschillende begrippen
verwijst.
vb. Het kan voorkomen, dat een meisje met een aardig voorkomen kan
voorkomen, dat ze moet voorkomen.
Het woord voorkomen is vier keer gebruikt maar telkens in een andere
betekenis.
Synoniemen zijn twee verschillende woorden die hetzelfde betekenen.
vb. klant debiteur
•
Schrap alle procesattributen, m.a.w. alle attributen die het resultaat zijn van
een berekening aan de hand van andere attributen.
Schrap ook alle attributen met een vaste waarde, zoals de naam en het
adres van het leverende bedrijf op een factuur.
•
Kies één attribuut als sleutel waarvan de andere attributen afhankelijk zijn.
Meestal zal dit een attribuut zijn dat een document, voorwerp, persoon, ….
Identificeert.
•
Duid repeterende groepen aan, dit zijn verzamelingen van attributen die
meerdere keren voorkomen t.o.v. de sleutel.
Begrippen rond databases
44/70
8.2.1.2
Voorbeeld: een factuur
Groothandel ABC
Dreef 55
1234 Destad
Nummer
Naam:
Adres:
Postcode:
Tel :
Factuurnr: 080350ST
Datum: 31/03/2008
5678
Piet Peeters
Park 70
1234 Destad
03 123 45 67
Nummer
Omschrijving
I2200
D3000
P962
Notebook
Desktops
Printer
Aantal
Prijs
Bedrag
5
12
1
799
399
145
3995,00
4788,00
145,00
Subtotaal
Korting
Totaal excl BTW
21% BTW
Totaal
8.928,00
100,00
9.028,00
1895,88
10.923,88
Inventarisatie van de gegevens:
factuurnummer
factuurdatum
nummer
naam
adres
postcode
gemeente
nummer
omschrijving
aantal
eenheidsprijs
bedrag
subtotaal
korting
totaal_excl_btw
btw_percentage
totaal
Er zijn geen synoniemen, maar wel homoniemen. Nummer wordt nl. twee keer
gebruikt in twee verschillende betekenissen.
Het eerste nummer betekent klantnummer, het tweede artikelnummer.
We wijzigen de namen van de attributen en het probleem is opgelost.
factuurnummer
factuurdatum
Begrippen rond databases
45/70
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
aantal
eenheidsprijs
bedrag
subtotaal
korting
totaal_excl_btw
btw_percentage
totaal
Verwijder alle procesattributen en alle attributen die een vaste waarde hebben, uit
de lijst.
factuurnummer
factuurdatum
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
aantal
eenheidsprijs
korting
Een factuur wordt gekenmerkt door het factuurnummer, dus factuurnummer wordt
de sleutel. De sleutel of primary key identificeert de factuur. Met elke waarde van
de sleutel komt slechts één factuur overeen.
Duid ook repeterende groepen aan. De lay-out van een document of lijst helpt vaak
om de repeterende groepen te ontdekken.
Op een FACTUUR komen de attributen artikelnummer, omschrijving, aantal en
eenheidsprijs meerdere keren voor. Dit zijn de attributen van de FACTUURLIJN. Zij
herhalen ten opzichte van de sleutel factuurnummer.
Begrippen rond databases
46/70
De attributenlijst ziet er nu als volgt uit:
factuurnummer
factuurdatum
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
aantal
eenheidsprijs
korting
De gegevens staan in de nulde normaalvorm.
Een andere notatie die ook gebruikt wordt om de normaalvormen weer te geven:
FACTUUR (factuurnummer, factuurdatum, klantnummer, naam, adres, postcode,
gemeente, RG[artikelnummer, omschrijving, aantal, eenheidsprijs], korting)
RG staat hierin voor repeterende groep.
Het normalisatie proces bestaat vanaf hier uit 3 normalisatiestappen, waarbij stap
voor stap de verschillende gegevensgroepen (ook wel entiteiten genoemd) met
bijbehorende sleutelkenmerken en afhankelijke gegevenskenmerken worden
bepaald.
Opmerking
In dit voorbeeld wordt uitgegaan van een vast BTW percentage, het BTW
percentage zou ook per artikel kunnen verschillen en wordt in dat geval op elke
factuurregel herhaald en verdwijnt niet uit de lijst met attributen. Een factuurregel
zou er dan als volgt kunnen uitzien:
D3000
Desktops
12
399
21%
5793,48
8.2.2 Eerste normalisatiestap
8.2.2.1
Procedure
Isoleer repeterende groepen in een aparte entiteit en zoek een primaire sleutel voor
die entiteit. De primaire sleutel zal bestaan uit de primaire sleutel van de
oorspronkelijke entiteit gecombineerd met één of meerdere identificerende
attributen in de repeterende groep.
Herhaal stap 1 totdat er geen attributen meer zijn die een herhaald aantal keer
voorkomen.
8.2.2.2
Voorbeeld
De FACTUURLIJN is een repeterende groep en moet bijgevolg afgescheiden
worden van de rest van de FACTUUR.
We herhalen de sleutel en groeperen daaronder alle de repeterende attributen. Uit
de repeterende groep kiezen we een tweede sleutel (artikelnummer) die samen met
het factuurnummer de samengestelde sleutel wordt voor de nieuwe groep. De
repeterende attributen worden uit de eerste groep geschrapt.
Begrippen rond databases
47/70
factuurnummer
factuurdatum
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
aantal
eenheidsprijs
btwpercentage
wordt dan
FACTUUR
factuurnummer
FACTUURLIJN
factuurnummer
factuurdatum
artikelnummer
klantnummer
omschrijving
naam
aantal
adres
eenheidsprijs
postcode
btwpercentage
gemeente
korting
Er zijn geen repeterende groepen meer, dus de gegevens staan nu in de eerste
normaalvorm.
8.2.3 Tweede normalisatiestap
8.2.3.1
Procedure
In deze normaalvorm richten we ons tot de groepen met een samengestelde
sleutel. Onderzoek welke attributen niet functioneel afhankelijk zijn van de
samengestelde sleutel en breng deze attributen onder bij een 'eigen' sleutel.
Herhaal deze stap voor alle groepen met een samengestelde sleutel.
8.2.3.2
Voorbeeld
We hebben één groep met een samengestelde sleutel nl. de FACTUURLIJN met
als sleutel factuurnummer en artikelnummer.
De attributen omschrijving en eenheidsprijs zijn alleen afhankelijk van de sleutel
artikelnummer en niet van factuurnummer. Deze attributen worden samen met de
sleutel artikelnummer in een aparte groep geplaatst. Deze groep krijgt ook een
naam, in het voorbeeld kan ARTIKEL een geschikte naam zijn.
Begrippen rond databases
48/70
De groepen zien er dan zo uit :
FACTUUR
FACTUURLIJN
ARTIKEL
Factuurnummer
artikelnummer
factuurnummer
factuurdatum
artikelnummer
omschrijving
klantnummer
aantal
eenheidsprijs
naam
btwpercentage
adres
postcode
gemeente
korting
De gegevens staan nu in de tweede normaalvorm.
8.2.4 Derde normalisatiestap
8.2.4.1
Procedure
Nu rest ons nog de laatste stap, de derde normaalvorm. Die vinden we door de
attributen te verwijderen die functioneel afhankelijk zijn van nietsleutelattributen.
De nieuwe groep zal bestaan uit de afhankelijke attributen samen met het attribuut
waarvan ze afhankelijk zijn. Het heeft alleen zin entiteiten te bekijken die meer dan
één niet-sleutelattribuut hebben.
8.2.4.2
Voorbeeld
De kenmerken naam, adres, postcode en gemeente zijn afhankelijk van het
attribuut klantnummer. Het klantnummer laten we staan in de groep maar de
andere kenmerken plaatsen we apart in een groep.
In de groep ARTIKEL komt de genoemde afhankelijkheid niet voor. De
omschrijving en de prijs zijn niet van elkaar afhankelijk. Een omschrijving kan meer
dan één keer voorkomen met verschillende prijzen en vice versa.
In de groep FACTUUR komen we de besproken afhankelijkheid wel tegen. De
attributen naam, adres, postcode en gemeente zijn afhankelijk van het attribuut
klantnummer. Bij iedere klantnummer vinden we immers precies één waarde voor
naam, adres, postcode en gemeente.
We nemen de afhankelijk attributen apart en vormen hiermee een nieuwe groep
KLANT. In de groep FACTUUR blijft het attribuut klantnummer bestaan waarvan de
andere afhankelijk zijn. Het attribuut klantnummer vormt de link tussen de groepen
FACTUUR en KLANT. Het attribuut klantnummer uit FACTUUR is de foreign key
die verwijst naar de primaire sleutel in de groep KLANT. Foreign key’s
onderstrepen we met een gestippelde lijn.
Het resultaat wordt dan
FACTUUR
factuurnummer
factuurdatum
klantnummer
FACTUURLIJN
factuurnummer
artikelnummer
aantal
ARTIKEL
artikelnummer
omschrijving
eenheidsprijs
btwpercentage
Begrippen rond databases
49/70
KLANT
klantnummer
naam
adres
postcode
gemeente
korting
De gegevens staan nu in de derde normaalvorm.
8.3
Een tweede voorbeeld, een offerte
In een bedrijf worden niet alleen facturen gemaakt, maar ook offertes. We
vertrekken deze keer van een offerte. Een offerte lijkt qua opbouw natuurlijk erg op
een factuur
Groothandel ABC
Dreef 55
1234 Destad
Nummer
Naam:
Adres:
Postcode:
Tel :
Offertenummer: 1245
Datum: 01/03/2008
5678
Jan Janssens
Dreef 100
1234 Destad
03 765 43 21
Artikelnr
Omschrijving
I2200
D3000
P962
Notebook
Desktops
Printer
Aantal eenheidsprijs
5
12
1
Subtotaal
Korting
Totaal excl BTW
Deze offerte is één maand na de offertedatum geldig.
Gegevens
offertenummer
offertedatum
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
Begrippen rond databases
799
399
145
Bedrag
3995,00
4788,00
145,00
8.928,00
100,00
9.028,00
50/70
aantal
eenheidsprijs
som_exclusief_btw
korting
totaal_exclusief_btw
Homoniemen en synoniemen verwijderen, berekende velden verwijderen en sleutel
kiezen.
offertenummer
offertedatum
klantnummer
naam
adres
postcode
gemeente
artikelnummer
omschrijving
aantal
eenheidsprijs
korting
8.3.1 Na de eerste normalisatiestap
OFFERTE
offertenummer
offertedatum
klantnummer
naam
adres
postcode
gemeente
korting
OFFERTELIJN
offertenummer
artikelnummer
omschrijving
aantal
eenheidsprijs
8.3.2 Na de tweede normalisatiestap
OFFERTE
offertenummer
offertedatum
korting
klantnummer
naam
adres
postcode
gemeente
korting
OFFERTELIJN
offertenummer
artikelnummer
aantal
ARTIKEL
artikelnummer
omschrijving
eenheidsprijs
8.3.3 Na de derde normalisatiestap
OFFERTE
OFFERTELIJN
ARTIKEL
Begrippen rond databases
51/70
offertenummer
offertedatum
korting
klantnummer
offertenummer
artikelnummer
aantal
artikelnummer
omschrijving
eenheidsprijs
KLANT
klantnummer
naam
adres
postcode
gemeente
8.4
Integratie van genormaliseerde gegevensgroepen
Het doel van het integreren van de verschillende informatiebehoeften is eigenlijk
hetzelfde als het doel van het normaliseren op zich. Het belangrijkste is dat er geen
overtollige en dubbele gegevens meer voorkomen. Dit om het onderhoud en
gebruik van de gegevens adequaat te houden.
8.4.1 Homoniemen en synoniemen
Controleer eerst op homoniemen en synoniemen in de twee groepen.
Zo komt het attribuut aantal zowel voor bij factuurregel als bij offerteregel. We
passen de naam aan en spreken bij factuur over aantalfactuur en bij offerte over
aantalofferte om verwarring te voorkomen.
Hetzelfde probleem stelt zich bij korting.
8.4.2 Samenvoegen van entiteiten uit de verschillende
informatiebehoeften
Kunnen er entiteiten uit de twee genormaliseerde informatiebehoeften
samengevoegd worden?
ARTIKEL(FACTUUR)
ARTIKEL(OFFERTE)
artikelnummer
omschrijving
eenheidsprijs
btwpercentage
Artikelnummer
Omschrijving
Eenheidsprijs
KLANT(FACTUUR)
KLANT(OFFERTE)
wordt dan ...
artikelnummer
omschrijving
eenheidsprijs
btwpercentage
klantnummer
Klantnummer
klantnummer
naam
Naam
naam
adres
Adres
adres
postcode
Postcode
postcode
gemeente
Gemeente
gemeente
Na samenvoeging geeft dit de volgende entiteiten en attributen
FACTUUR
FACTUURREGEL
ARTIKEL
Begrippen rond databases
52/70
factuurnummer
factuurdatum
klantnummer
kortingfactuur
Factuurnummer
artikelnummer
aantalfactuur
artikelnummer
omschrijving
eenheidsprijs
btwpercentage
KLANT
OFFERTE
OFFERTEREGEL
klantnummer
naam
adres
postcode
gemeente
offertenummer
offertedatum
klantnummer
kortingofferte
offertenummer
artikelnummer
aantalofferte
8.4.3 Definitieve naamgeving en samenstelling
8.5
FACTUUR
factuurnummer
factuurdatum
klantnummer
kortingfactuur
FACTUURLIJN
factuurnummer
artikelnummer
aantalfactuur
ARTIKEL
artikelnummer
omschrijving
eenheidsprijs
btwpercentage
KLANT
klantnummer
naam
adres
postcode
gemeente
OFFERTE
offertenummer
offertedatum
klantnummer
kortingofferte
OFFERTELIJN
offertenummer
artikelnummer
aantalofferte
Bepalen van de relaties
De entiteittypes staan niet los van elkaar. Er bestaan associaties tussen bepaalde
types op basis van de foreign keys.
Klantnummer vormt de link tussen de groepen KLANT en FACTUUR en de
groepen KLANT en OFFERTE. Klantnummer is de sleutel in de groep KLANT en
een foreign key in de groepen FACTUUR en OFFERTE. Het gaat hier telkens om
een 1:n relatie.
Begrippen rond databases
53/70
We stellen dit voor in een gegevensstructuurdiagram
Tips bij het bepalen van de relaties:
Als een primary key van entiteit1 deel is van een samengestelde sleutel van
entiteit2, dan is er een 1…n associatie tussen entiteit1 en entiteit2.
b.v. factuur en factuurlijn
Als een primary key van entiteit1 voorkomt als attribuut in entiteit2 dan is er een
1…n associatie tussen entiteit1 en entiteit2.
b.v. klant en factuur
Begrippen rond databases
54/70
8.6
Oefeningen
8.6.1 Projectbeheer
Op aanvraag wil men op de informaticafdeling een overzicht krijgen van de
gepresteerde arbeid per project. Normaliseer deze informatiebehoefte. Maak ook
een gegevensstructuurdiagram.
Projectoverzicht
Project
Medewerker
Nr
Omschrijving Budget Persnr
Naam Afdeling Chef
101
Boekhouding
Jan
AN10
Bert
58
204
Paul
PR05
Rob
114
109
Rita
PR15
Geert
124
Jef
AN10
Bert
109
Rita
PR15
Geert
234
Fred
PR05
Rob
105
2000 123
Aankoop
3250 324
Uren
86
157
98
8.6.2 Ziekenfonds
Een centrale registratie van een ziekenhuis heeft voor het ziekenfonds de volgende
jaaroverzichten nodig. Een specialist is aan precies één afdeling verbonden. Per
periode kan een medicijn meer dan één keer worden voorgeschreven.
Normaliseer deze informatiebehoefte. Maak ook een gegevensstructuurdiagram.
Patientnr
4773
Naam
Peeters P.
Adres
Dorpstraat 45
Ziekenfondsnr 448833756
Gemeente 2000
Antwerpen
Specialist Afdeling
Periode
Medicijn
Datum
Jannsens
12/01/2003 –
15/02/2003
Valium
12/01/2003
Hexafro
30/01/2003
23/07/2003
Neuro
Janssens
Neuro
23/07/2003 –
01/09/2003
Valium
Smits
Chirirg
21/12/2003
Antibiotica 23/12/2003
8.6.3 Bestellingen en pickinglist
Normaliseer de informatiebehoeften uit paragraaf 5.1 Integreer daarna ook de
genormaliseerde informatiebehoeften en maak een gegevensstructuurdiagram.
Begrippen rond databases
55/70
8.6.4 Distributiebedrijf van kantoorbenodigheden
De klanten plaatsen aankooporders. De aankooporders worden verzameld en er
worden dan magazijnfiches opgesteld zodat de magazijnier weet waar hij de
producten in de magazijnen moet ophalen. Als de magazijnier producten wil
bijbestellen kan hij de leverancierslijsten raadplegen.
Normaliseer en integreer deze informatiebehoeften
ƒ Een inkooporder voor de inkoper
ƒ
De magazijnfiches voor de magazijnier
ƒ
Leveranciersinformatie voor de magazijnier
INKOOPORDER
Ordernr. 8254
Datum: 12/09/2003
LEVERANCIER: 523
BURO
Industrieweg 1
9000 Gent
Artikelnr
Omschrijving
Aantal
Prijs
Cd542g
Bureaustoel ZXFashion
6
110,00
Cg452e
Bureau
4
215,10
Totaalbedrag 325,10
Leveringsdatum: 5/10/2003
MAGAZIJNFICHE
Artikelnr Cd542g
Leveranciersnr 523
Omschrijving : Bureaustoel ZXFashion
Voorraad: 12
Magazijnrek: A12
Schap: 2
Bestellingen
Ordernr
Leveringsdatum
Aantal
8254
5/10/2003
6
215
7/10/2003
3
Begrippen rond databases
56/70
LEVERANCIERSINFORMATIE
Leverancier: 523 BURO Industrieweg 1 – 9000 Gent
Artikelen
Artikelnr
Omschrijving
Levertijd
Cd542g
Bureaustoel ZXFashion
2 weken
BhY365
Bureaulamp Flash
Uit voorraad
Cg452e
Bureau
3 weken
Begrippen rond databases
57/70
8.6.5 Overboekingen en rekeningoverzichten
De twee informatiebehoeften zijn hieronder afgebeeld.
Normaliseer en integreer deze informatiebehoeften. Maak ook een
gegevensstructuurdiagram.
Begrippen rond databases
58/70
9 HET FYSIEKE SCHEMA
9.1
Toegangspaden
Een toegangspadanalyse onderzoekt per informatiebehoefte bij welke entiteit de
toegang plaats vindt of met andere woorden met welke entiteit we beginnen, in
welke volgorde andere entiteiten erbij betrokken worden, welke attributen er telkens
nodig zijn en via welke attributen we van de ene entiteit naar de andere springen.
Een voorbeeld
De toegangspadanalyse om tot een factuur en een offerte te komen zoals
beschreven in 8.2 en 8.3.
Entiteit
Attribuut
Informatiebehoefte
Factuur
Factuur
Factuurregel
Artikel
Klant
Offerte
Offerteregel
Offerte
Factuurnr
⊗
Factuurdatum
X
Klantnummer
X
Kortingfactuur
X
Factuurnr
X
Artikelnr
X
Aantalartikel
X
Artikelnummer
X
X
Omschrijving
X
X
Eenheidsprijs
X
X
BTW percentage
X
X
Klantnummer
X
X
Naam
X
X
Adres
X
X
Postcode
X
X
Gemeente
X
x
Offertenummer
⊗
Offertedatum
X
Klantnummer
X
Kortingofferte
X
Offertenummer
X
Artikelnummer
X
aantalofferte
X
Het attribuut waarvan we vertrekken wordt omcirkeld.
Begrippen rond databases
59/70
Pijlen duiden aan via welke attributen andere entiteiten opgenomen worden in de
informatiebehoefte.
9.2
Indexen
Tijdens het fysieke databaseontwerp wordt ook nagegaan welke indexen er moeten
aangemaakt worden.
9.2.1 Wat is een index?
Een index kan het zoeken in een tabel aanzienlijk versnellen en lijkt op een index
achter in een boek. Op basis van een zoekwoord kun je de juiste nummers vinden
van rijen, binnen een tabel.
Een index in een relationele database is een een (deel)relatie waarin een tabel op
volgorde van een aangegeven kolom (of meerdere kolommen) is gesorteerd. Het
doel van een dergelijke index is om de zoeksnelheid te vergroten.
Een voorbeeld
Een tabel met de dieren uit de zoo
Nummer
Dier
Kooinummer
1
Olifant
5
2
Aap
7
3
Zebra
1
4
Giraf
4
Een index op dier zou er als volgt uitzien
Dier in alfabetische volgorde
Nummer van het overeenkomstig record
in de oorspronkelijke tabel
Aap
2
Giraf
4
Olifant
1
Zebra
3
Als een beer moet gevonden worden zonder dat er een index op dier bestaat, moet
het systeem heel de tabel doorlopen alvorens het kan laten weten dat er geen beer
is.
Als een beer moet gevonden worden met een index op dier moet het systeem
alleen de eerste twee records in de index doornemen. Een beer had immers voor
de giraf moeten staan.
Een index kan dus zoekopdrachten in belangrijke mate versnellen en dat is
belangrijk in databases met veel records. Sommige databases hebben miljarden
gegevens, en het is wenselijk dat een enkele zoekopdracht er niet toe leidt dat van
elk van deze gegevens wordt geëvalueerd of het aan de opgegegeven criteria
voldoet. Daarvoor zijn indexen gemaakt.
Het aanmaken van een index heeft ook een nadeel: bij elke verandering in de tabel,
moet ook de index worden aangepast. Daarom is het belangrijk niet zo maar op
Begrippen rond databases
60/70
elke tabel of elke kolom in een tabel een index te definiëren, maar eerst eens na te
denken of het aanmaken van een index ook rendeert.
9.2.2 Welke relaties krijgen een index?
Het heeft geen zin een index op een relatie te definiëren als dit niet nuttig is in
functie van één of andere selectie of sortering die regelmatig moet gebeuren. Ook
met de selectie component van update opdrachten dient rekening gehouden te
worden.
9.2.3 Welke attributen krijgen een index?
Attributen die regelmatig gebruikt worden bij het formuleren van criteria of waarop
regelmatig moet gesorteerd worden, komen in aanmerking voor een index. Ook op
een attribuut dat betrokken is in een veel gebruikte relatie kan het nuttig zijn een
index te definiëren. Een index op een attribuut waarvan het domein uit weinig
verschillende waarden bestaat zal dan weer minder rendement opleveren.
9.3
Oefeningen
Maak een toegangspadanalyse voor elk van de oefeningen uit het vorige hoofdstuk.
Begrippen rond databases
61/70
10 DE ROL VAN SQL
10.1 Wat is SQL?
SQL is de meest gebruikte taal om met een database te communiceren.
Zo kan je vanuit een programma via een SQL statement enerzijds informatie
opvragen uit een database en anderzijds de inhoud van de database aanpassen.
SQL beschikt over opdrachten om in de database een tabel aan te maken of te
verwijderen, een record aan te passen, aan te maken of te verwijderen en ga zo
maar door.
SQL is een vrij logische taal en sluit aan bij de Engels taal.
SQL evolueert mee met de veranderende behoeften in de databasewereld. Dit blijkt
uit de verschillende ANSI/ISO standaards die in de loop der jaren werden
vastgelegd.
Jaar
Naam
Alias
Kenmerken
1986
SQL-86
SQL-87
Gepubliceerd door ANSI. Bekrachtigd door ISO in
1987
1989
SQL-89
1992
SQL-92
SQL2
Een belangrijke aanpassing
1999
SQL: 1999
SQL3
matching van reguliere expressies, recursieve
query’s, triggers, niet scalaire types en beperkte
object georiënteerde mogelijkheden.
2003
SQL: 2003
Beperkte aanpassingen
XML verwante eigenschappen, window functies,
autonumerieke waarden
Niet alle producten ondersteunen alle mogelijkheden vastgelegd in deze
standaards, maar de meeste ondersteunen wel de belangrijkste afspraken.
10.2 Onderdelen van SQL
De SQL opdrachten kunnen onderverdeeld worden in een aantal
deelverzamelingen naargelang de rol die ze spelen: 2
10.2.1
De data definition language (DDL)
Deze deelverzameling van SQL opdrachten maakt het mogelijk een definitie van
een tabel of view te creëren, te verwijderen en aan te passen. Verder voorziet zij
ook in de nodige opdrachten om integriteit constraints vast te leggen en om
toegangsrechten te bepalen.
Enkele sleutelwoorden uit de DDL: CREATE TABLE … PRIMARY KEY … FOREIGN KEY …,
CREATE VIEW,…
2
Voor een meer gedetailleerde uitleg over het opbouwen van SQL opdrachten wordt naar de cursus
SQL verwezen.
Begrippen rond databases
62/70
10.2.2
De data manipulation language (DML)
In deze deelverzameling van SQL vinden we de nodig opdrachten terug om query’s
op te stellen en records toe te voegen, te verwijderen en aan te passen.
Enkele sleutelwoorden van de DML: SELECT … FROM … WHERE … GROUP BY …
HAVING … ORDER BY …, INSERT … INTO … VALUES …, UPDATE … SET …, enz.
10.2.3
Embedded en dynamische SQL
Een relationeel DBMS voorziet in een interactieve interface waar een gebruiker
rechtstreeks SQL opdrachten kan invoeren en waar SQL als een taal op zichzelf
gebruikt wordt.
Deze benadering volstaat zolang de uit te voeren taak volledig met SQL opdrachten
kan gerealiseerd worden. In de praktijk doen zich ook situaties voor die enerzijds de
grotere flexibiliteit van een programmeertaal vereisen maar anderzijds de
opdrachten van SQL om data te manipuleren. Om dergelijke situaties het hoofd te
bieden, definieert SQL ook opdrachten die van uit een gast taal zoals C of Java
kunnen uitgevoerd worden. Men spreekt dan van embedded SQL.
In sommige gevallen is de samenstelling van het SQL statement ook nog
afhankelijk van invoer van de gebruiker. Dynamic SQL maakt het mogelijk dat
programma’s een SQL statement opstellen en uitvoeren tijdens de uitvoering van
het programma (at run time).
10.2.4
Triggers
Sinds de standaard van 1999 ondersteunt SQL ook triggers. Een trigger
veroorzaakt dat het DBMS bepaalde acties uitvoert zodra veranderingen aan de
database een toestand, bepaald door de trigger, veroorzaken.
Een database met een geassocieerde verzameling triggers wordt een actieve
database genoemd.
De beschrijving van een trigger bestaat uit drie delen:
•
Een gebeurtenis: een wijziging van de database die de trigger in gang zet.
•
Een voorwaarde: een query of test die uitgevoerd wordt zodra de trigger
geactiveerd wordt.
•
Een actie: een procedure die uitgevoerd wordt als de trigger geactiveerd wordt
en de voorwaarde de waarde true oplevert; een query levert de waarde true als
de resultaatset niet leeg is..
Afhankelijk van de actie die aan een trigger gekoppeld is, kan het belangrijk zijn dat
die actie uitgevoerd wordt voor of nadat er bepaalde wijzigingen doorgevoerd zijn.
Begrippen rond databases
63/70
Een voorbeeld
CREATE TRIGGER
WHEN
incr count AFTER INSERT ON Students
(new.age < 18)
gebeurtenis
voorwaarde, new
verwijst naar het nieuw
ingevoerd tupel
FOR EACH ROW
de actie
BEGIN
count := count + 1;
END
10.2.5
Opdrachten die met beveiliging te maken hebben
SQL voorziet ook in een mechanisme om op basis van gebruikers de toegang tot
tabellen en views te controleren. Opdrachten zoals GRANT en REVOKE gaan dan een
rol spelen. (zie ook 11.1 Beveiliging)
10.2.6
Opdrachten i.v.m. transaction management
Er zijn in SQL ook opdrachten voorzien die een gebruiker toelaten te bepalen hoe
de uitvoering van een transactie moet gebeuren.
Hier horen sleutelwoorden zoals START TRANSACTION, COMMIT en ROLLBACK thuis.
(zie ook 11.3 Transactiebeheer)
10.2.7
Opdrachten rond een client server architectuur
Een andere reeks SQL opdrachten laten toe te controleren hoe een client
toepassing een SQL database server kan benaderen en toegang krijgen tot de
gegevens in de database over het netwerk.
In een Client-Server systeem bestaan één of meerdere client processen en één of
meerdere servers. Een client proces kan een query sturen naar eender welk server
proces. Clients dragen de verantwoordelijkheid voor de user interface, servers
beheren de data en voeren transacties uit.
Begrippen rond databases
64/70
11 AANDACHTSPUNTEN BIJ HET GEBRUIK VAN EEN
DBMS
11.1 Beveiliging
Een database van een onderneming bevat erg veel informatie. Het merendeel van
de verschillende groepen gebruikers in een onderneming zal slechts toegang tot
een beperkt deel van die informatie nodig hebben om zijn werk naar behoren te
kunnen uitvoeren. Daarom is het nodig dat een DBMS ook middelen ter
beschikking stelt om de toegang tot de informatie te beveiligen. Views kunnen
hierbij een handig hulpmiddel vormen.
De beveiliging van een database beoogt drie doelstellingen:
•
Geheimhouding, ervoor zorgen dat gevoelige gegevens niet zomaar door
iedereen kunnen geraadpleegd worden. Zo is het b.v. niet de bedoeling dat
werknemers elkaars beoordelingen kunnen raadplegen.
•
Integriteit, niet iedereen mag zomaar alle gegevens kunnen wijzigen.
Zo mag een werknemer zijn eigen loon niet kunnen aanpassen, hij mag het wel
raadplegen.
•
Beschikbaarheid, gebruikers die bepaalde gegevens nodig hebben bij de
uitoefening van hun job, moeten er ook wel degelijk bij kunnen.
In de database wereld wordt die beveiliging op twee manieren gerealiseerd:
willekeurig toegangsbeheer (Discretionary Access Control of DAC) en gericht
toegangsbeheer (Mandatory Access Control of MAC).
11.1.1
DAC
Toegang tot tabellen of views in de database wordt verleend op basis van de
identiteit van de gebruiker en de groepen waar hij lid van is. Met toegang wordt hier
ook verwezen naar het type toegangsrecht, lezen, schrijven, wijzigen.
Een gebruiker die een tabel creëert beschikt over alle mogelijke rechten op die
tabel, ook over het recht om rechten toe te kennen aan andere gebruikers.
Een gebruiker die een nieuwe view creëert zal voor die view beschikken over de
doorsnede van de rechten die hij heeft op alle tabellen en views waarop de view
gebaseerd is.
SQL ondersteunt deze vorm om toegang te verlenen via de GRANT en REVOKE
opdracht. Rechten die via deze opdrachten kunnen verleend of ingetrokken worden
zijn SELECT, INSERT, DELETE, REFERENCES, GRANT. Voor meer details wordt verwezen
naar de SQL cursus.
In SQL-92 beperkt het verlenen van rechten zich tot gebruikers. Volgens de
SQL:1999 standaard kunnen ook rechten aan rollen en dus aan groepen gebruikers
toegekend worden.
DAC heeft een zwak punt als beveiligingsmethode, het is namelijk onderhevig aan
Trojan Horse aanvallen: een gebruiker die geen toegang heeft tot de gegevens van
een bepaalde tabel A kan een nieuwe tabel B creëren waar hij wel toegang toe
heeft en die via een applicatie die wel aan de gegevens van tabel A kan vullen met
de inhoud van tabel A. Zo kan hij via tabel B de inhoud van tabel A toch lezen.
Begrippen rond databases
65/70
11.1.2
MAC
Mandatory Access Control (MAC) beveiligt informatie door een gevoeligheidslabel
toe te kennen aan die informatie en een toegangsniveau aan de gebruikers. Zodra
een gebruiker informatie probeert te benaderen worden de gevoeligheid van de
informatie en het toegangsrecht van de gebruiker met elkaar vergeleken en op
basis daarvan krijgt de gebruiker al dan niet toegang.
De verantwoordelijkheid voor de beveiliging ligt hier dus in handen van de
administrator en niet zozeer in handen van de verschillende gebruikers.
Het toekennen van een gevoeligheidslabel kan zowel op het niveau van een tabel
of view als op het niveau van bepaalde rijen of kolommen. Het gevolg is dat
gebruikers met een verschillend toegangsniveau die eenzelfde tabel of view
bekijken toch andere rijen en kolommen kunnen te zien krijgen.
Over het algemeen is MAC een veiliger mechanisme dan DAC, maar scoort het wat
minder op gebied van performantie.
11.2 Gelijktijdige benadering van gegevens
Het gelijktijdig benaderen van dezelfde gegevens in een database door twee
verschillende gebruikers of toepassingen kan aanleiding geven tot inconsistente
resultaten.
Een klassiek voorbeeld is twee bankbedienden die gelijktijdig eenzelfde rekening
bijwerken naar aanleiding van twee verschillende geldtransacties. Bediende 1 en
bediende 2 halen (m.a.w. kopiëren) allebei het huidige saldo op. Bediende 1 voert
één geldtransactie uit en registreert het nieuwe saldo. Bediende 2 voert een andere
geldtransactie uit en registreert zijn nieuw saldo en overschrijft daarbij het resultaat
van bediende 1 zonder er rekening mee te houden.
Om dergelijke fouten ten gevolge van onbeperkte toegang te voorkomen, moet een
record onmiddellijk vergrendeld worden zodra het opgehaald wordt voor een
bewerking. Iedereen die vanaf dan hetzelfde record probeert op te halen om het bij
te werken moet de toegang geweigerd worden op basis van de vergrendeling. Eens
het bijgewerkte record weggeschreven is of de bijwerking geannuleerd werd, wordt
de vergrendeling opgeheven. Dit levert een garantie voor de consistentie van de
gegevens.
11.3 Transactiebeheer
11.3.1
Wat is een transactie
Het doorvoeren van een wijziging van gegevens kan gepaard gaan met het
uitvoeren van een lijst opdrachten die elk op hun beurt informatie lezen uit en
schrijven in de database. Het is dan belangrijk dat er een garantie bestaat dat de
database niet in een toestand achterblijft waar slechts een deel van de opdrachten
of query’s uitgevoerd is. Dit zou immers corrupte data veroorzaken.
Een voorbeeld: het uitvoeren van een betaling in de bankwereld.
Het saldo van één rekening moet met een bepaald bedrag verminderd worden en
het is belangrijk dat het saldo van een andere rekening met datzelfde bedrag
verhoogd wordt. Als er een panne optreedt na het verminderen van het ene saldo,
is het belangrijk dat na het herstellen van de panne de toestand in orde gebracht
wordt, d.w.z. dat ofwel het saldo van de eerste rekening terug op zijn
Begrippen rond databases
66/70
oorspronkelijke waarde komt ofwel het saldo van de tweede rekening alsnog
aangepast wordt.
Transacties moeten dergelijke situaties, waarin corrupte data kunnen ontstaan,
opvangen en ervoor zorgen dat de integriteit van een database behouden blijft.
Een transactie is dus een lijst acties en verloopt in drie fasen:
1. Het begin van een transactie wordt bepaald.
2. De verschillende acties (opdrachten) die deel uitmaken van de transactie
worden uitgevoerd, zonder dat de resultaten zichtbaar zijn voor de
buitenwereld, d.w.z. de wijzigingen worden slechts voorlopig opgeslagen.
3. De transactie wordt afgesloten (committed) en de aanpassingen worden
zichtbaar voor de buitenwereld, maar slechts op voorwaarde dat alle
opdrachten en handelingen in de transactie tot een goed einde gebracht zijn.
Zo niet wordt de transactie geannuleerd en eindigt de database in dezelfde
toestand als toen met de uitvoering van de transactie begonnen werd
(rollback).
Sommige systemen gebruiken de term LUW’s (Logical Units of Work)
Databasesystemen die het gebruik van transacties ondersteunen, worden ook wel
transactionele databasesystemen genoemd. Vandaag de dag is dat het geval voor
de meeste databasesystemen, zoals Microsoft SQL Server, MySQL, Oracle, …)
11.3.2
ACID
Vier eigenschappen, kortweg ACID genoemd, kenmerken transacties. ACID staat
voor Atomair (Atomicity), Consistent (Consistency), geïsoleerd (Isolation) en
Duurzaam (Durability).
Atomair refereert naar het feit dat ofwel alle taken uitgevoerd worden die deel
uitmaken van een transactie, ofwel geen enkele.
Consistent wijst erop dat de database zowel bij het begin als gedurende, als op het
einde van een transactie in een integere toestand is. Het gaat hier dan zowel over
de fysieke database (de interne structuur, de indexen, de records, …) als over de
logische database (de inhoud is ten allen tijde geldig vanuit het standpunt van een
applicatie).
Concreet kan dit b.v. betekenen dat als één van de integriteitsregels in de database
is dat een voorraad niet negatief mag worden, deze situatie zich voor de
buitenwereld ook niet zal voordoen, zelfs niet in de loop van de uitvoering van de
transactie.
Geïsoleerd betekent dat elke transactie de illusie heeft dat zij de enige in uitvoering
is. Geen enkele operatie behalve de transactie zelf zal de data in een
tussenliggende situatie kunnen zien.
In de praktijk komt het erop neer dat als er twee transacties T1 en T2 tegelijkertijd
worden uitgevoerd, het eindresultaat hetzelfde is als bij de uitvoering van T1
gevolgd door de uitvoering van T2 of als bij de uitvoering van T2 gevolgd door de
uitvoering van T1.
Duurzaamheid garandeert dat eens een gebruiker bericht ontvangt dat de
transactie met succes uitgevoerd is, de transactie definitief is en niet meer
teruggedraaid wordt, zelfs bij een falen van een systeem.
Begrippen rond databases
67/70
11.3.3
Technieken om ACID te ondersteunen
11.3.3.1
WAL (Write ahead logging)
Wal is een populaire techniek om ACID te ondersteunen
Alle wijzigingen aan de database komen eerst in een logbestand, weggeschreven
op een betrouwbaar medium, alvorens ze effectief toegepast worden op de
database. Zowel undo informatie als redo informatie wordt in dat bestand
opgeslagen.
Op basis van het logbestand kunnen de uitgevoerde opdrachten van een
onderbroken transactie ongedaan gemaakt worden en de opdrachten van een
comitted transactie herhaald.
11.3.3.2
Shadow paging
Een page is in deze context een eenheid van fysieke opslag ter grootte van 210 tot
215 bytes.
Shadow paging is te vergelijken met de old master-new master batch processing
techniek gebruikt in mainframe database systems. De uitvoer van elke
batchuitvoering werd twee keer opgeslagen op aparte schijven. Het ene exemplaar
werd gebruikt als backup, het andere als startpunt voor de volgende verwerking. Bij
shadow paging gebeurt ongeveer hetzelfde, maar dan op het niveau van pages.
Begrippen rond databases
68/70
12 EVOLUTIES
12.1 Web databases
De eerste generatie van sites op het Internet waren opgebouwd als een
verzameling van HTML documenten.
Vandaag maken de meeste sites echter gebruik van DBMS’en om op een snelle en
betrouwbare manier antwoorden te leveren op vragen van gebruikers die binnen
komen via het Internet.
Dit stelt nieuwe eisen aan DBMS’en. Niet alleen moeten zij een groot aantal
gelijktijdige aanvragen aankunnen, de aanvragen zelf hebben ook specifieke
kenmerken.
Er moet gewerkt worden met informatie opgeslagen in verzamelingen
ongestructureerde documenten en in documenten gedeeltelijk gestructureerd door
gebruik van HTML en XML.
Verder zal een opzoekopdracht dikwijls gebaseerd zijn op het meegeven van
trefwoorden en zich richten tot databases die verdeeld zitten over verschillende
servers.
12.1.1
De architectuur
Het benaderen van een database via een webapplicatie kan op allerlei manieren
georganiseerd zijn. In de meeste gevallen zijn zeker drie lagen terug te vinden:
•
Een web browser vormt de eerste laag, de gebruiker maakt via de webbrowser
duidelijk wat hij verwacht.
•
Een engine die gebruik maakt van een dynamische web technologie zoals CGI,
PHP, Java Servlets of Active Server Pages werkt in het midden.
•
De database bevindt zich in de derde laag.
De web browser stuurt een aanvraag naar de middelste laag, die op haar beurt de
aanvraag verwerkt en query’s en updates genereert voor de database en het
antwoord van de database omzet in HTML formaat bruikbaar voor de webbrowser.
12.1.2
XML
XML gaat hier een alsmaar meer belangrijke rol spelen. Het is een standaard om
de inhoud en structuur van een document te beschrijven, m.a.w. XML is een manier
om data te beschrijven en een XML bestand kan net zoals een database meteen
ook de data bevatten..
XML is gebaseerd op HTML (HyperText Markup Language ) en SGML (Standard
Generalized Markup Language).
Het is enerzijds eenvoudig genoeg om manipulatie van documenten op een
gemakkelijke manier toe te laten (in tegenstelling tot SGML) en anderzijds krachtig
genoeg om gebruikers toe te laten eigen document beschrijvingen te definiëren (dat
in tegenstelling tot HTML). Het doel van XML is het delen van informatie tussen
verschillende systemen, meer bepaald verschillende systemen verbonden via het
Internet, te vergemakkelijken.
De structuur van een XML document is minder rigide dan die van een relationele
database. Toch is er voldoende structuur om bruikbare query’s op te stellen.
Daarvoor kan o.a; XQuery gebruikt worden.
Begrippen rond databases
69/70
12.1.3
Tekstdocumenten
Het veelvuldig werken met tekstdocumenten op het Internet bracht de nood aan
technieken om snel bepaalde woorden of combinaties van woorden terug te vinden
in teksten, in de belangstelling.
Er wordt gewerkt met boolean query’s en ranked query’s.
Een boolean query gaat uit van een lijst woorden opgebouwd uit verschillende
delen die met elkaar verbonden zijn door de logische operator and.
Elk deel is op zijn beurt opgebouwd uit een aantal woorden verbonden door de
logische operator of. Die woorden zijn allemaal synoniemen. Elk deel stelt dus een
bepaald begrip voor.
Het komt erop neer dat we op zoek zijn naar documenten waarin de verschillende
begrippen voorkomen.
Er werden ook index technieken ontwikkeld om boolean query’s te versnellen:
inverted files en signature files.
Een ranked query bestaat ook uit een lijst woorden, maar in het resultaat wordt aan
elk gevonden document een quotum meegegeven dat de relevantie van het
antwoord in functie van de query weergeeft. Het quotum wordt bepaald aan de
hand van allerlei criteria zoals welk percentage van de opgegeven trefwoorden
voorkomen in het gevonden documenten, of trefwoorden voorkomen in de titel of
alleen in het document zelf, etc.
12.2 Object database systemen
Relationele database systemen ondersteunen het gebruik van gegevens waarvan
de types slechts uit een beperkte verzameling komen: gehele getallen, datums,
strings, ….
In nogal wat hedendaagse toepassingsdomeinen moeten ook gegevens van een
meer complex type kunnen verwerkt worden, zoals video, audio, ....
Een mogelijkheid is hiervoor een beroep te doen op aparte bestanden of
gespecialiseerde datastructuren. Denk hierbij aan Computer aided design and
modeling (CAD/CAM), multimedia opslag, documenten beheer, …
Een DBMS biedt echter veel extra comfort zoals controle op gelijktijdige toegang,
herstel van de gegevens na een crash, ondersteuning van indexen, …
Daarom worden er ook inspanningen geleverd om DBMS’en te ontwikkelen die
meer ingewikkelde datatypes ondersteunen en dat brengt ons bij object database
sytemen.
Object database systemen kunnen onderverdeeld worden in twee groepen: object
georiënteerde database systemen en object-relationele database systemen.
In het eerste geval wordt DBMS functionaliteit toegevoegd aan een object
georiënteerde programmeertaal en haar omgeving.
In het tweede geval worden de types die standaard ter beschikking zijn in het
databasesysteem uitgebreid met data structuren, de mogelijkheid zelf nieuwe types
te definiëren en overerving.
Begrippen rond databases
70/70
13 COLOFON
Sectorverantwoordelijke:
Ortaire Uyttersprot
Cursusverantwoordelijke:
Jean Smits
Didactiek:
Werkgroep basis
Lay-out:
Leyman Eugène
Medewerkers:
Martine Bracke
Elise Van Reeth
Versie:
oktober 2006
Nummer dotatielijst:
Begrippen rond databases