DATABASE ontwerpen

2011
ROC ter AA T&T
Cees Loomans
[DATABASE ONTWERPEN]
Wat moet je weten over database ontwerpen. Wat heb je nodig, wat moet je doen. Wat is het
resultaat?
DATABASE ontwerpen
1 januari 2011
Inhoud
Introductie ............................................................................................................................................... 4
Enkele basisconcepten ............................................................................................................................ 4
Databasesysteem .................................................................................................................................... 6
Hardware ............................................................................................................................................. 6
Data ..................................................................................................................................................... 8
Software ............................................................................................................................................ 11
Databasemanagementsysteem ..................................................................................................... 11
Toepassingsprogramma 's ............................................................................................................. 11
Gebruikers ......................................................................................................................................... 12
Data-administrator (DA) ................................................................................................................ 12
Database-Administrator (DBA) ...................................................................................................... 12
Toepassingsontwikkelaar (TO) ...................................................................................................... 12
Eindgebruiker (EU)......................................................................................................................... 12
Database management systeem ....................................................................................................... 13
Hoofdfunctionaliteit ...................................................................................................................... 13
Architectuur................................................................................................................................... 16
Vragen deel 1a....................................................................................................................................... 24
Relationele database ............................................................................................................................. 25
Voordelen en mogelijkheden van relationele databases .................................................................. 27
Klantcontactsysteem ......................................................................................................................... 28
Tabellen en de primaire sleutel ......................................................................................................... 29
Gegevens koppelen ........................................................................................................................... 31
De een op veel relatie ....................................................................................................................... 32
De veel op veel relatie ....................................................................................................................... 33
De een op een relatie ........................................................................................................................ 34
Database normaliseren ..................................................................................................................... 36
De eerste normaalvorm (1NF) ........................................................................................................... 37
De derde normaalvorm (3NF) ........................................................................................................... 39
Het definitieve database-ontwerp .................................................................................................... 41
Nog een voorbeeld: webwinkel......................................................................................................... 42
Conclusie ........................................................................................................................................... 42
Het ontwerp proces van een database. ................................................................................................ 44
Hoofdstuk: Introductie
De tweede normaalvorm (2NF) ......................................................................................................... 38
2
DATABASE ontwerpen
1 januari 2011
Scope ................................................................................................................................................. 46
Logisch Ontwerp ................................................................................................................................ 46
0-de normaal vorm :
Onderken alle data-elementen (scope)................................................... 46
1-de normaal vorm: Zoek de sleutel waarmee alle data op te zoeken is .................................. 47
2-de normaal vorm: Maak alle data afhankelijk van de gehele sleutel. ........................................... 47
3-de normaal vorm:
Maak alle data afhankelijk van niets dan de sleutel. .................................. 48
Maak een ERD ................................................................................................................................... 49
De Technische Optimalisatie: ................................................................................................................ 51
Het doel ............................................................................................................................................. 51
Optimalisatiestappen ........................................................................................................................ 51
Ranges ........................................................................................................................................... 52
Denormaliseer ............................................................................................................................... 53
Combineer tabellen ....................................................................................................................... 53
Sla afgeleide gegevens op ............................................................................................................. 54
Voeg indexen toe........................................................................................................................... 54
Sorteer ........................................................................................................................................... 55
Implementatie ....................................................................................................................................... 56
Maak tabel ......................................................................................................................................... 56
Extra kolom........................................................................................................................................ 56
Maak Index ........................................................................................................................................ 56
Verklarende woordenlijst ...................................................................................................................... 58
Hoofdstuk: Introductie
Vragen deel 1b ...................................................................................................................................... 62
3
DATABASE ontwerpen
1 januari 2011
Introductie
In deze cursus leer je een database ontwerpen.
Een database is een verzameling gegevens die bij elkaar horen. Databasesystemen zijn programma's
die deze gegevens opslaan en beschikbaar maken. Er is dus een verschil tussen een database en een
databasesysteem.
De database is de verzameling gegevens en regels om deze gegevens op te vragen.
Een databasesysteem is het geheel van programma’s en hardware waarmee de gegevens uit de
database kunnen worden opgevraagd, veranderd of worden gewist.
Veel computerprogramma's maken gebruik van een databasesysteem om gegevens te beheren. Je
maakt zelf waarschijnlijk dagelijks gebruik van databasesystemen. Als je geld opneemt bij de
pinautomaat raadpleeg je, via het databasesysteem van de bank, de database van de bank. Als je
websites op internet bezoekt raadpleeg je een database waar (o.a) paginateksten in op zijn geslagen,
als je een nieuw paspoort aanvraagt wordt ongetwijfeld een database met persoonsgegevens
geraadpleegd, etc.
Vaak wordt met een database ook het database systeem bedoeld. Alleen als het nodig is zullen we
het verschil aangeven.
In deze cursus relationele database ontwerpen leer je zelf een database ontwerpen volgens het
relationeel model.
Het relationeel model bestaat uit een aantal aanbevelingen die je toe kan passen op het ontwerp van
je database. Het beschrijft hoe gegevens logisch en efficiënt georganiseerd kunnen worden in
tabellen en hoe gegevens uit verschillende tabellen gekoppeld kunnen worden. De meeste databases
zijn tegenwoordig relationeel.
Als leidraad voor deze cursus is de ontwikkeling van een eenvoudige database voor een klant
contactsysteem genomen. We gaan daartoe tabellen ontwerpen voor het opslaan van klantgegevens
en 'contactmomenten'.
Om de principes in deze cursus wat te verhelderen zijn er voorbeelden opgenomen.
Computersystemen worden tegenwoordig gebruikt voor de automatisering van allerlei aspecten van
menselijke activiteit. Een belangrijke activiteit, die niet meer is weg te denken in de huidige
maatschappij, is het automatisch beheren van gegevens met het oog op latere verwerking en
hergebruik. In eenvoudige gevallen komt dit louter neer op het bijhouden van gegevens voor later
onderzoek. In meer geavanceerde toepassingen mondt dit uit in complexe taken voor de
ondersteuning van diverse activiteiten binnen een onderneming. Een goed (of slecht?) voorbeeld
hiervan is het zoekgedrag van de gebruikers van Google dat o.a. gebruikt wordt voor het gericht
toezenden van advertenties (waarmee Google zijn geld verdient)
Hoofdstuk: Introductie
Enkele basisconcepten
4
DATABASE ontwerpen
1 januari 2011
Naast het doorzoeken, zijn het toevoegen, verwijderen, aanpassen en consistent houden van de
gegevens typisch basistaken die voorkomen bij gegevens beheer. We maken hierbij duidelijk
onderscheid tussen ‘data’ (of gegevens) en ‘informatie’.
Met data bedoelen we ‘gegeven feiten’. Zo zijn cijfers, getallen, symbolen, karakters en woorden die
je dagelijks tegenkomt verschillende vormen van data.
Informatie verwijst daarnaast ook naar de betekenis van de gegevens: Je beschikt ook over de
betekenis die een bepaalde gebruiker of groep van gebruikers toekent aan die data.
Hebben we het over informatiebeheer dan is het verschil tussen ‘data’ en ‘informatie’ belangrijk. De
huidige computersystemen kunnen bijna allemaal alleen maar op een efficiënte manier data
beheren. Dit leidt dan ook rechtstreeks naar de term ‘database’ met de volgende omschrijving:
Een database is een verzameling van persistente data
Persistent geeft aan dat de gegevens gedurende een bepaalde tijd worden opgeslagen in een
permanent geheugen van een systeem. Het gaat niet verloren bij het uitschakelen van het systeem.
De gegevens blijven meestal opgeslagen tot ze expliciet, door tussenkomst van een gebruiker of
toepassing worden gewist.
Wat betreft het beheer van de betekenis van de gegevens, met andere woorden het beheren van
informatie of de semantiek , is de beschikbare technologie momenteel nog ontoereikend.
Onderzoek richt zich tegenwoordig op het komen tot semantisch rijkere gegevensmodellen met als
doel systemen te ontwikkelen die volwaardige ‘informatiebases’ in plaats van ‘databases’ kunnen
beheren.
Hoofdstuk:
Een computersysteem voor het beheer van databases wordt een databasesysteem genoemd.
5
DATABASE ontwerpen
1 januari 2011
Databasesysteem
In een databasesysteem
onderscheiden we globaal genomen
drie hoofdcomponenten: de
hardware, de data (gegevens) en de
software. Daarnaast is het belangrijk
om ook de gebruikers van het systeem
te beschouwen. In de figuur hiernaast
zijn deze schematisch voorgesteld.
Hardware
Hoewel het zeker niet de bedoeling is om in dit dictaat veel aandacht te besteden aan
hardwareaspecten, bespreken we toch in het kort de belangrijkste hardwarecomponenten binnen
een databasesysteem.
Essentieel zijn de central processing unit (CPU) en het computergeheugen waarin de database wordt
opgeslagen. De CPU kan fysiek bestaan uit één of meer processoren en zorgt voor het uitvoeren van
de software-instructies. Om redenen van onder meer efficiëntie en implementeerbaarheid is het
geheugen van een computer hiërarchisch georganiseerd. Daarbij onderscheiden we twee
hoofdcategorieën:
Primair geheugen. Geheugens uit deze categorie zijn rechtstreeks aanspreekbaar door de CPU. Het
primaire geheugen staat fysiek het dichtst bij de CPU en is daarom het snelst toegankelijk. De
geheugencapaciteit is echter beperkt.
Tot het primaire geheugen behoren:

Statisch RAM -geheugen (Random Access Memory). Statisch RAM -geheugen wordt ook wel
het cachegeheugen genoemd en wordt door de CPU in hoofdzaak gebruikt om de uitvoering
van programma's te versnellen. Statisch RAM-geheugen is het snelste, doch tegelijk het
duurste geheugen binnen een databasesysteem .
Dynamisch RAM-geheugen. Dit geheugen is het belangrijkste werkgeheugen voor de CPU en
noemen we daarom ook wel het hoofdgeheugen. We gebruiken het voor de tijdelijke opslag
van data en programmacode. Het voordeel van dynamisch RAM-geheugen is de lagere
kostprijs. Nadeel is echter de tragere werking vergeleken met statisch RAM-geheugen.
Vaak worden er in het primaire geheugen minstens twee zogenaamde databasebuffers opgezet.
Terwijl de inhoud van de ene buffer wordt ingelezen of weggeschreven, kan de CPU tegelijkertijd
(parallel) de data uit de andere buffer verwerken. Een direct gevolg hiervan is dat de hoeveelheid
data die per lees- of schrijfoperatie wordt overgedragen van en naar het primaire geheugen gelijk is
aan de grootte van een databasebuffer.
Het primaire geheugen heeft een tijdelijk karakter. Hierdoor is het niet bedoeld als
hoofdopslagmedium voor databases. Daar een dynamisch RAM -geheugen van 8 tot 16 Gigabytes op
één enkel computersysteem geen uitzondering meer is, bestaan er databases die integraal in het
Hoofdstuk: /Databasesysteem

6
DATABASE ontwerpen
1 januari 2011
RAM-geheugen zijn opgeslagen. Vaak gaat het daarbij om toepassingen waarbij extreem hoge
overdrachtsnelheden zijn vereist, wat bijvoorbeeld het geval is bij databases voor telefooncentrales.
Uiteraard dienen bij dergelijke databases zeker veiligheidsvoorzieningen tegen dataverlies te worden
getroffen, zoals het bijhouden van een kopie van de database in een permanent geheugen.
Algemeen geldt dat hoe meer dynamisch RAM -geheugen je tot je beschikking hebt en hoe meer
werkruimte (voor databasebuffers) je hierin voor het databasesysteem reserveert, hoe beter de
prestaties van het databasesysteem zullen zijn.
Een speciale vorm van geheugen dat sommigen eveneens tot het primaire geheugen rekenen, is het
zogenaamde flashgeheugen, waarbij EEPROM-technologie (Electrically Erasable Programmabie Read
-Only Memory) wordt gebruikt. Voorbeelden van (kleine)
databases op flashgeheugen zijn de datacollecties in mp3spelers,
digitale fototoestellen, pda's en camera's.
Secundair geheugen. Gegevensdragers uit deze categorie hebben
gewoonlijk een veel grotere opslagcapaciteit, zijn goedkoper, maar
hebben een veel tragere gegevenstoegang. Data in het secundaire
geheugen kan niet rechtstreeks worden verwerkt door de cpu,
maar moeten hiertoe eerst naar een databasebuffer worden
gekopieerd. Deze datastromen zijn gevisualiseerd in figuur
hiernaast.
Tot het secundaire geheugen behoren:
De magneetschijfgeheugens. Deze geheugens zijn
opgebouwd uit één of meer roterende schijven die langs
één of twee zijden beschrijfbaar zijn. Elke schijf is door de fabrikant onderverdeeld in cirkels
die we tracks noemen. Bij verschillende schijven vormen de tracks die zich op een gelijke
afstand van het middelpunt van de schijf bevinden een cilinder. De tracks zijn op hun beurt
onderverdeeld in sectoren.
Bij het formatteren, verdeelt het besturingssysteem, bijvoorbeeld Windows, Linux of Unix, de
sectoren onder in pagina's van gelijke grootte. Elke pagina bestaat uit een vast aantal
sectoren. De databasebuffers uit het primaire geheugen worden even groot gekozen als de
grootte van een pagina. Daardoor wordt per lees- en schrijfoperatie één pagina gekopieerd.
Om een pagina te kunnen lezen of beschrijven moet deze vooraf worden gelokaliseerd.
Hierbij moet rekening worden gehouden met drie tijdspannes: de tijd die nodig is om de
lees/schrijfkoppen van het opslagmedium te verplaatsen naar de juiste track of cilinder, de
tijd die nodig is om de schijf te roteren tot aan het begin van de eerste sector van de
betreffende pagina en de tijd die nodig is om de data over te brengen. De eerste twee
tijdspannes duren vaak veel langer dan de derde en vormen een prestatie-'bottleneck'. Om
prestatieredenen is het belangrijk dat we de data in de schijfbestanden zodanig organiseren
dat het aantal paginatransfers minimaal wordt gehouden.
Bij de bestandsorganisatie maken we een onderscheid tussen primaire en secundaire
bestandsorganisatie. Primaire bestandsorganisatie heeft betrekking op de manier waarop
Hoofdstuk: /Databasesysteem

7
DATABASE ontwerpen
1 januari 2011
een database fysiek wordt opgeslagen. De data kan daarbij bijvoorbeeld al dan niet geordend
worden. Secundaire bestandsorganisatie heeft tot doel de toegang tot de fysiek opgeslagen
database verder te optimaliseren door extra data over bijvoorbeeld de locatie van gegevens
en verbanden tussen gegevens bij te houden. Indexen zijn voorbeelden hiervan, we
bespreken ze indien nodig later nog.
Magneetschijfgeheugens worden momenteel het meest gebruikt als secundair
geheugenmedium voor databases.
De cd-rom en dvd-geheugens. Dit zijn recentere opslagmedia. In configuraties waarbij
verschillende van dergelijke geheugens worden samengebracht in een soort 'jukebox' systeem kunnen grote opslagcapaciteiten worden bereikt. Door hun minder goede
schrijffaciliteiten zijn cd's en dvd's eerder geschikt voor de opslag van databases die enkel
moeten kunnen worden geraadpleegd. Voorbeelden zijn digitale telefoongidsen, digitale
archieven, geografische databases en digitale woordenboeken en encyclopedieën. Een
belangrijk minpunt is de beperkte duurzaamheid van zelf beschreven cd's en dvd's, waardoor
we hun bruikbaarheid voor kritische toepassingen op zijn minst in twijfel moeten trekken.
De magneetbandgeheugens. Magneetbandgeheugens worden in hoofdzaak gebruikt voor
archivering en back-up. Omwille van hun fysieke karakteristieken werken
magneetbandgeheugens alleen efficiënt bij sequentiële toegang (in volgorde van fysieke
opslag) tot de data. Ook hier kan een soort 'jukebox' -systeem worden gebruikt om de
opslagcapaciteit te vergroten. Een voorbeeld van een database op magneetbandgeheugen is
het EOS-archief (Earth Observation Satellite) van de NASA. Sommige auteurs categoriseren
magneetbandgeheugens als tertiair geheugen.
Data
Centraal in een databasesysteem staan de databases. Een database wordt meestal in het geheugen
gestructureerd opgeslagen als een collectie van records. Elk record is opgebouwd uit één of meer
velden die data kunnen bevatten. Deze velden zijn vastgelegd in het recordtype van het record en
worden elk gekarakteriseerd door een naam en een datatype. Analoog als bij programmeertalen legt
het datatype de toegelaten waarden en operatoren voor het veld vast. Naast velddefinities heeft een
recordtype ook een naam. Doorgaans bevat een database verschillende records die gegroepeerd zijn
Hoofdstuk: /Databasesysteem
In volgende figuur stellen we de hiërarchie van
het computergeheugen voor. De magneetbandgeheugens zijn hierin apart vermeld als
tertiair geheugen. Aan de top van de hiërarchie
staat het snellere, duurdere geheugen met
doorgaans kleinere opslagcapaciteit. De basis
wordt gevormd door het tragere, minder snel
toegankelijk geheugen met een grotere
opslagcapaciteit.
8
DATABASE ontwerpen
1 januari 2011
volgens diverse recordtypes. In het secundaire geheugen worden de records opgeslagen in
bestanden. Meestal bevat een bestand een aantal records. Hoewel dit geen vereiste is, kunnen er in
hetzelfde bestand records van verschillende recordtypes zijn opgeslagen.
In de praktijk kan het aantal records in een database variëren van enkele tot miljoenen. Daarnaast
kan een databasesysteem tegelijkertijd instaan voor het beheer van meerdere databases.
Voorbeeld
De figuur op de volgende bladzijde bevat een voorstelling van de recordtypes en records uit een
voorbeelddatabase voor schilderkunst. De database bestaat uit een collectie van records van drie
recordtypes: 'Schilderij', 'Artiest' en 'Eigenaar'. Omwille van de leesbaarheid zijn de records geordend
per recordtype en worden de veldnamen voorafgaand aan de records herhaald. De gebruikte
datatypes zijn:

CHAR(n) voor het modelleren van karakter strings van lengte n.
INTEGER voor het modelleren van gehele getallen (die geïnterpreteerd worden als
jaartallen).
REAL voor het modelleren van reële getallen.
Besturingssystemen zoals MVS, Windows, Linux en Unix, bieden geen oplossing voor het vastleggen
en het behandelen van de soms complexe verbanden die kunnen bestaan tussen de data in een
bestand. Als ze voorkomen, zijn dergelijke verwantschappen en verbanden echter zeer belangrijk
voor de gebruikers en toepassingsprogramma's: ze verduidelijken de semantiek (betekenis) van de
verschillende entiteiten (beschreven dingen) uit het toepassingsdomein.
Databases moeten hierom zodanig zijn opgebouwd, dat alle verbanden die voorkomen tussen data
op een efficiënte manier, liefst zonder verlies aan semantiek worden weergegeven. Dubbele opslag,
met andere woorden overtollige data, moeten we daarbij zo veel mogelijk vermijden. Een database
zien we als een geïntegreerd geheel. Data die in de reële wereld samenhangen, brengen we bij
Hoofdstuk: /Databasesysteem


9
DATABASE ontwerpen
1 januari 2011
voorkeur onder in dezelfde database en de verbanden tussen deze data geven we daarbij zo goed
mogelijk weer.
Zo wordt bijvoorbeeld het verband tussen een schilderij en een schilder in de voorbeelddatabase
voor schilderkunst weergegeven door middel van het veld 'Artiest' van het recordtype 'Schilderij' en
het veld 'Naam' van het recordtype 'Artiest'. Een 'Schilderij' -record met waarde 'Monet' voor het
veld 'Artiest' wordt hierbij gerelateerd met het 'Artiest' - record met dezelfde veldwaarde voor
'Naam'. In plaats van te werken met aparte datacollecties voor schilderijen en artiesten, worden
beide geïntegreerd in één database.
Hoofdstuk: /Databasesysteem
Het geïntegreerd zijn van de data is een belangrijk kenmerk van elke database.
1
0
DATABASE ontwerpen
1 januari 2011
Software
Om de hardware te kunnen aansturen en databases te kunnen opzetten, beheren en efficiënt te
gebruiken, zijn computerprogramma's nodig. De belangrijkste software in een databasesysteem is
het databasemanagementsysteem.
Het databasemanagementsysteem (dbms) is de softwarecomponent van een databasesysteem die
instaat voor het beheer van de databases.
Voorbeelden zijn de opensource-systemen MySQL en Postgress en de commerciële systemen DB2,
SQL Server, Oracle, Sybase en MS Access.
Naast het dbms zijn er vaak nog andere softwarecomponenten die deel uitmaken van het
databasesysteem. Denk daarbij aan allerhande (in huis ontwikkelde) toepassingsprogramma's, tools,
communicatiesoftware en software voor datawarehousing, data-analyse of automatische
rapportgeneratie.
Databasemanagementsysteem
Het databasemanagementsysteem is bedoeld voor het beheer van de databases en schermt
daarvoor de gebruikers af van de technische details van hardware en bestandsorganisatie. Alle
interactie met een database gebeurt via het dbms: als een gebruiker data wil opzoeken, toevoegen,
wijzigen of verwijderen, voeren we dit in het dbms in, dat vervolgens zorgt voor de correcte
afhandeling van de gevraagde operatie. Bij een zoekopdracht toont het dbms op een overzichtelijke
manier de resultaten aan de gebruiker.
Hoofdstuk: /Databasesysteem
Toepassingsprogramma 's
Je kunt op indirecte wijze werken met een database via toepassingssoftware. De toepassingssoftware zorgt eerst voor de verbinding met het dbms en de toegang tot de relevante
databases. Daarna zal het de gebruikersacties die betrekking hebben op de database vertalen naar
database operaties, die dan voor de verwerking worden doorgestuurd naar het dbms. Bij een online
toepassing verwerkt het dbms de opdrachten realtime. Bij een batch-toepassing worden de
opdrachten op een meer geschikt tijdstip verwerkt, bijvoorbeeld ’s nachts of tijdens het weekend
wanneer het systeem minder druk bezet is. Na uitvoering van de operaties stuurt het dbms
eventuele resultaten door naar het toepassingsprogramma, dat de resultaten toont aan de
gebruiker. Uiteraard dienen ook eventuele fouten te worden opgevangen.
1
1
DATABASE ontwerpen
1 januari 2011
Gebruikers
Met een gebruiker bedoelen we een persoon die op een of andere manier interactie met het
databasesysteem heeft. Als er verschillende gebruikers zijn, worden er meestal gebruikersprofielen
gemaakt. De beheerder kan dan bepalen wie welke bevoegdheden heeft.
De belangrijkste gebruikerprofielen zijn:
Data-administrator, database-administrator, toepassingsontwikkelaar en eindgebruiker. Sommige
van deze gebruikersprofielen zijn standaard vastgelegd in het dbms. De meeste systemen staan het
toe om eigen gebruikersprofielen te maken.
Data-administrator (DA)
Dit profiel is bedoeld voor personen die in een onderneming centraal verantwoordelijk zijn voor de
data en deze hebben meestal een hogere kader functie in de onderneming. Hij of Zij beslist welke
data, in welk formaat, in welke database wordt opgenomen en hij of zij is ook verantwoordelijk voor
de registratie van deze informatie. Verder legt hij of zij de bedrijfsstrategie vast voor de toegang, de
gebruikersprofielen, de beveiliging, verwerking en communicatie van de data en de prioriteiten
ingeval van toegangsconflicten. Hij of zij speelt een grote rol bij het database ontwerp. De functie is
van het grootste belang bij grote conversies of aanpassingen aan de databases, zoals die rond het
millenium (rond het jaar 2000) en die bij de invoering van de euro.
Database-Administrator (DBA)
Het database administrator profiel is voor die personen die technisch verantwoordelijk zijn voor de
implementatie en onderhoud van de databases. Uiteraard overlegt hij of zij daarvoor met de DataAdminstrator en volgt hun beslissingen strikt op. Ook zorgt hij voor het herstellen van de databases
na het falen ervan (backup en restore), de consistentie van de databases, het monitoren van de
prestaties en het goed functioneren van het database systeem. In grote ondernemingen wordt de
Database-Administrator taak meestal over meerdere personen verdeeld.
Eindgebruiker (EU)
De eindgebruikers zijn de mensen die sporadisch of voor de uitvoering van hun dagelijkse taken
werken met de database. Afhankelijk van de manier waarop de interactie met de database gebeurt,
maken we een onderscheid tussen gewone eindgebruikers en geavanceerde eindgebruikers.

Gewone eindgebruiker. De gewone eindgebruiker werkt op een vrij eenvoudige
manier met de database. Meestal gebeurt dit via een toepassingsprogramma, waarbij de
eindgebruiker volledig van de databasestructuur is afgeschermd. In sommige gevallen kan de
interactie plaatsvinden via heel eenvoudige instructies die door de eindgebruiker zelf worden
Hoofdstuk: /Databasesysteem
Toepassingsontwikkelaar (TO)
De toepassingsontwikkelaar zorgt voor de ontwikkeling van de (in huis ontwikkelde)
toepassingsprogramma’s die het toelaten om op een interactieve, gebruikersvriendelijke manier te
werken met het databasesysteem. Duidelijk is dat hij of zij zal moeten overleggen met de DatabaseAdministrator, om de structuur en implementatie van de databases en de gebruikte
parameterinstellingen voor primaire en secundaire bestandsorganisatie te kennen, en om een
optimale interactie met de database te kunnen realiseren. Voor het uittesten van de ontwikkelde
software moet de toepassingsontwikkelaar beschikken over een werkkopie van de databases.
1
2
DATABASE ontwerpen
1 januari 2011
ingevoerd en die moeten zijn opgebouwd volgens de grammatica van de gebruikte
databasetaal. Door het minder complexe, meestal voorgeprogrammeerde, karakter is de
interactie van een gewone eindgebruiker gewoonlijk vrij goed voorspelbaar. Bij de
optimalisatie van het databasesysteem moet deze voorspelbaarheid door de DBA optimaal
worden benut.

Geavanceerde eindgebruiker. De geavanceerde eindgebruiker werkt vrijwel altijd met
de database via complexere instructies uit de databasetaal of via verfijnde dataanalysesoftware. Typerend zijn het minder routinematige gebruik en de enorme variaties in
de vraagstelling. Vaak zal de complexiteit van de vraagstelling het benutten van alle
mogelijkheden vereisen. Dit alles maakt dat de interactie vrijwel niet te voorspellen is.
Database management systeem
In deze paragraaf bespreken we in detail de belangrijkste softwarecomponent van een
databasesysteem, namelijk het databasemanagementsysteem (dbms). Eerst staan we stil bij de
functionaliteit van een dbms. Daarna lichten we de architectuur kort toe.
Hoofdfunctionaliteit
De belangrijkste taken van een dbms zijn de definitie, manipulatie en constructie van databases.
Databasedefinitie
Bij de definitie van een database moeten zowel de databasestructuur als de semantische regels voor
de database worden vastgelegd. Semantische regels zijn nodig om de integriteit van de database te
helpen garanderen. Integriteit slaat op de correctheid van de data in de database. Een database
modelleert immers informatie en moet op elk moment een zo goed mogelijke weergave zijn van de
realiteit. De data moeten een consistent geheel vormen.
Incorrectheid van data kan ook optreden als er geen redundantie meer mogelijk is. Als bijvoorbeeld
voor het geboortejaar ('Geboren') een waarde '2900' wordt opgegeven, of als er voor een schilderij
een schilder wordt opgegeven die niet voorkomt in de database, of als het geboortejaar ('Geboren')
van een artiest zijn sterfjaar ('Gestorven') niet voorafgaat. Dergelijke vormen van incorrectheid
kunnen worden voorkomen door semantische regels vast te leggen voor de database. Zo kan bijvoorbeeld de geldigheid van een geboortejaar worden gecontroleerd met de regels
'Geboren=heden-12'
'Geboren= Gestorven-12'
Hoofdstuk: /Databasesysteem
Als het mogelijk is, moet de database-integriteit worden gecontroleerd door het dbms. De database
moet daarom zo zijn ontworpen dat er geen tegenstrijdige data in kunnen voorkomen. Deze zijn
immers een potentiële bron van inconsistentie. Als bijvoorbeeld in de schilderkunstdatabase ook in
het recordtype 'Schilderij' het geboortejaar van de schilder zou worden bijgehouden, bestaat het
risico dat bij een eventuele correctie niet alle kopieën worden gecorrigeerd, met als gevolg dat de
database inconsistent wordt doordat ze conflicterende gegevens bevat.
1
3
DATABASE ontwerpen
1 januari 2011
Hierbij is 'heden' het huidig jaartal en er wordt aangenomen dat geen schilderijen in de database
mogen worden opgenomen van artiesten die jonger zijn dan 12 jaar. Het dbms moet erop toezien
dat databases steeds voldoen aan de opgelegde semantische regels.
Niet elke vorm van in correctheid wordt gecontroleerd door het dbms. Als we bijvoorbeeld een
verkeerd geboortejaar invoeren, bijvoorbeeld 1861 in plaats van 1860, kan het systeem dit niet
controleren. Mede hierom is een autorisatiesysteem met gebruikersprofielen vereist: slechts
gebruikers of gebruikersgroepen die verantwoordelijk zijn voor de inhoud van de database mogen
data in de database invoeren, aanpassen of verwijderen. Het is immers bekend dat gebruikers
minder nauwkeurig werken naarmate ze minder verantwoordelijk zijn voor de data, of ze er minder
profijt van hebben dat de database correct is. Uiteraard kan een autorisatiesysteem niet uitsluiten
dat ook deze personen fouten maken.
Databasemanipulatie
Voor de manipulatie van een database moeten de noodzakelijke operatoren worden gecreëerd voor
het efficiënt toevoegen, verwijderen en aanpassen van data en het efficiënt doorzoeken van
databases. De primaire en secundaire bestandsorganisatie moeten op een optimale datamanipulatie
zijn afgesteld. Dit vereist de nodige prestatie-monitoring en expertise van de DBA. Een dbms kan
uitgerust zijn met een databasemanipulatietaal. De grammatica van deze taal schrijft voor hoe
instructies voor databasemanipulatie moeten zijn opgebouwd. Gebruikers kunnen de taal interactief
inzetten voor het wijzigen of doorzoeken van de database. Daarnaast kunnen taalconstructies
worden ingebed in programma code of kunnen er communicatieprotocollen zijn aangemaakt die het
mogelijk maken om vanuit (in huis ontwikkelde) toepassingsprogramma's te werken met het dbms
en de databases.
De voorschriften voor de definitie en de manipulatie van databases zijn vastgelegd in een zogenoemd
data(base)model.
Het dbms moet ervoor zorgen dat de aangemaakte databases efficiënt worden bijgehouden in het
geheugen, zonder dat de eindgebruikers en toepassingsontwikkelaars zich hoeven te bekommeren
over de fysieke opslagdetails. Hiertoe moet het dbms faciliteiten bieden voor databaseconstructie
die het mogelijk maken om een gepaste primaire bestandsorganisatie op te zetten. De
databasestructuur die is vastgelegd bij de databasedefinitie moet daarbij worden vertaald naar een
recordstructuur . Het volledige beheer van de fysieke opslag valt onder de controle van het dbms.
Vaak zal de databaseconstructie via een gepaste parameterinstelling door de DBA kunnen worden
geconfigureerd.
Andere functionaliteit
Naast de hiervoor vermelde hoofdtaken kan een dbms verder nog een oplossing bieden voor het
delen van dezelfde data, de beveiliging van de data, een optimaal systeemgedrag en administratie en
controle.
Hoofdstuk: /Databasesysteem
Databaseconstructie
1
4
DATABASE ontwerpen
1 januari 2011
Delen van dezelfde data
Gebruikers of software moeten dezelfde data kunnen delen en eventueel tegelijkertijd kunnen
aanpassen of doorzoeken. Hiertoe moet een dbms zijn uitgerust met faciliteiten voor simultane
toegang tot de database. Het delen van een database moet volledig transparant zijn voor de
gebruikers en toepassingsprogramma's. Dit wil zeggen dat een gebruiker of software steeds op een
geïsoleerde manier met de database moet werken, net alsof er geen gedeelde toegang tot de
database bestaat.
Het efficiënt delen van databases houdt ook in dat een dbms toegangsconflicten moet kunnen
verhinderen. Een klassiek voorbeeld van een toegangsconflict is het probleem van dubbele boeking,
die kan optreden wanneer twee gebruikers tegelijkertijd dezelfde plaats in een theater, trein of
vliegtuig boeken.
Beveiliging van de data
Een dbms moet in staat zijn om de data die het beheert te beschermen tegen ongeoorloofd gebruik
en tegen fysiek falen van het databasesysteem.
Als de database toegankelijk is voor verschillende gebruikers, kan het gebeuren dat niet iedereen
toegang mag hebben tot de volledige database. Bij ongeoorloofde toegang kunnen gebruikers, al dan
niet opzettelijk, schadelijke acties uitvoeren op de database. Het dbms moet de data kunnen
afschermen en beveiligen tegen ongeoorloofde manipulatie en gebruik. Een beveiligingsmethode kan
gebaseerd zijn op een autorisatiesysteem met wachtwoordbeveiliging en gebruikersprofielen. Om te
voorkomen dat de inhoud van een database via het rechtstreeks af scannen van het geheugen zou
kunnen worden gelezen, kan de data versleuteld worden weggeschreven.
Merk op dat de hiervoor besproken semantische regels voor de integriteit van de database kunnen
worden gezien als een middel om de data te beveiligen tegen schadelijke operaties door geoorloofd
gebruik: incorrecte gegevens dienen daarbij zo veel mogelijk te worden gedetecteerd en geweerd.
Regelmatig een back-up maken van de database is belangrijk voor het herstelproces, maar is op zich
onvoldoende omdat een beschadigde database bij voorkeur moet worden hersteld tot haar toestand
net vóór het falen en niet tot haar toestand net vóór de meest recentste back-up.
Optimaal systeemgedrag
Voorgaande faciliteiten moeten worden gerealiseerd zonder veel nadeel voor de gebruikers: een
dbms moet in staat zijn om grote hoeveelheden data te beheren en daarbij de gebruikers nog
redelijke toegangstijden garanderen. Een belangrijk deel van de techniek in een dbms is daarom
gericht op het optimaliseren van de datatoegang. Een efficiënte secundaire bestandsorganisatie is
daarbij noodzakelijk. Veelal zal de secundaire bestandsorganisatie via een gepaste
Hoofdstuk: /Databasesysteem
Het fysiek falen van een databasesysteem kan bijvoorbeeld een externe oorzaak hebben, zoals
stroomuitval of veroorzaakt worden door een hardwaredefect, bijvoorbeeld een defect
opslagmedium. De beveiligingsfaciliteiten van een dbms moeten in geval van fysiek falen het verlies
aan data zo veel mogelijk beperken.
1
5
DATABASE ontwerpen
1 januari 2011
parameterinstelling door de DBA kunnen worden geconfigureerd. Een ander aspect is de
optimalisatie van de uitvoering van de instructies van de gebruikers.
Verschillende gebruikers die het systeem tegelijkertijd gebruiken, hebben soms conflicterende
prestatie-eisen die onmogelijk allemaal tegelijk kunnen worden gerealiseerd. Zo kan de vraag voor
directe analyse resultaten door een manager het systeem kortstondig belasten, wat tijdelijk funest
kan zijn voor de prestatie van bijvoorbeeld de dataregistratie bij de productie. Wanneer dergelijke
conflicten zich voordoen, moet de DBA - steeds in overleg met de DA - gepast kunnen ingrijpen en
het meest geschikte compromis voor de onderneming uitwerken.
Administratie en controle
Een dbms moet de gebruikers - dikwijls de DBA's - voldoende faciliteiten bieden voor
systeemadministratie en systeemcontrole.
Tot de belangrijkste administratie- en controletaken behoren onder meer: het aanpassen van de
secundaire bestandsorganisatie aan de actuele noden van de onderneming, het bijhouden en
analyseren van prestatiestatistieken, het beheer van gebruikersprofielen en -accounts, het aanmaken
van back-ups, het aanleggen van een logbestand voor beveiligingsdoeleinden (hierbij worden de
acties van gebruikers in meer of minder detail geregistreerd) en het onderhoud van het
databasesysteem.
De hedendaagse databasemanagement systemen zijn vrijwel
allemaal gebouwd volgens een 'drielagen' -architectuur die
geïnspireerd is op de architectuur die is voorgesteld door de
ANSII SPARC dbms-studiegroep . Deze architectuur zie je in naast
liggende figuur. Dit betekent echter niet dat er geen andere
architecturen bestaan en worden gebruikt.
De drie lagen van deze structuur noemen we respectievelijk de
interne laag, de logische laag en de externe laag.
De interne laag
De interne laag (ook opslaglaag genoemd) staat het dichtst bij de fysieke opslag van de database. In
deze laag beschrijven we de databaserecords die eerder werden geïntroduceerd. Daarbij besteden
we onder meer aandacht aan structuur, manier van opslag en toegangspaden. Databaserecords
worden georganiseerd in bestanden. De structuur van de records wordt vastgelegd in recordtypes,
Hoofdstuk: /Databasesysteem
Architectuur
De implementatie van een dbms gebeurt volgens een
architectuur die bestaat uit verschillende lagen, gaande van de
fysieke gegevensopslag naar de logische entiteiten (bijvoorbeeld
tabellen en objecten) die door de toepassing worden gebruikt.
Zoals kort werd aangegeven in het historische overzicht is de
belangrijkste reden voor een 'meerlagen' -architectuur het
verkrijgen van dataonafhankelijkheid.
1
6
DATABASE ontwerpen
1 januari 2011
de manier van opslag wordt bepaald door de keuze van de primaire bestandsorganisatie en de
toegangspaden worden mede bepaald door de keuze van de secundaire bestandsorganisatie.
Records van hetzelfde recordtype kunnen een vaste of variabele lengte hebben.
Een variabele lengte kan onder meer te wijten zijn aan de volgende feiten:

Eén of meer recordvelden hebben een datatype met domeinwaarden van een variabele
lengte. Zo zou het veld 'Naam' van het recordtype:
RECORDTYPE Artiest (Naam:CHAR(30); Voornaam:CHAR(20); Geboren:INTEGER;
Gestorven:INTEGER)
uit het voorbeeld van de schildersdatabase een geassocieerd datatype VARCHAR kunnen
hebben, waarvan de domeinwaarden bestaan uit een variabel aantal karakters (in plaats van
het datatype CHAR ( 30) , met domeinwaarden van exact dertig karakters).


Eén of meer recordvelden zijn meerwaardig. Een domeinwaarde van een meerwaardig
recordveld is een collectie die kan bestaan uit een vast of variabel aantal elementen.
Bijvoorbeeld, het recordtype 'Artiest' zou een meerwaardig veld 'Hobby' kunnen hebben met
geassocieerd datatype SET (VARCHAR) , waarvan elke waarde een verzameling van hobby's
voorstelt.
Eén of meer velden van het recordtype zijn optioneel. Het is niet verplicht om een waarde op
te geven voor optionele velden. Zo zou bijvoorbeeld kunnen worden vastgelegd dat de
velden 'Geboren' en 'Gestorven' van het recordtype 'Artiest' optioneel zijn.
Voor een recordtype met
records van vaste lengte
wordt ook de recordlengte
weggeschreven in de file
header. Het dbms kan dan
de eerste byte van een
recordveld lokaliseren op
basis van de relatieve
positie van deze byte ten
opzichte van de startbyte
van het record. Een
voordeel van het werken
Hoofdstuk: /Databasesysteem
Om de records van een bestand te beschrijven, kan vooraan in het bestand een zogenoemde file
header worden opgenomen, waarin onder meer de manier van opslag van de voorkomende
recordtypes is beschreven. Elk record in het bestand kan dan worden voorafgegaan door een
recordtype-indicator die
verwijst naar de van toepassing zijnde beschrijving.
1
7
DATABASE ontwerpen
1 januari 2011
met records van vaste lengte is hierom de vereenvoudigde recordtoegang. Een nadeel is het
inefficiënte gebruik van het geheugen: bij te grote veldlengtes heeft men ongebruikte ruimte, bij te
kleine veldlengtes is er niet genoeg ruimte voor de dataopslag met informatieverlies als gevolg.
De manier van opslag bij dit
voorbeeld werd voor records van
het recordtype 'Artiest'
geïllustreerd in de volgende
figuur (a) . Voor de unieke
identificatie van de records werd
elk recordtype uitgebreid met
een prefix-veld. Dit prefix -veld is
vastgelegd door de gekozen
primaire bestandsorganisatie.
Voor een betere toegankelijkheid werd verder voor elk recordtype een index gedefinieerd. In zijn
eenvoudigste vorm kan een index worden gezien als een geordende lijst van koppels van de vorm:
< veldwaarde,referentie>
Hierbij is veldwaarde een waarde die voorkomt in het veld waarover de index is gedefinieerd.
Referentie is een pointer naar de geheugenplaats waar een record met die veldwaarde is opgeslagen.
Deze pointer kan bijvoorbeeld het adres (track/cilinder, sector) zijn van de pagina waarin het record
zich bevindt. De elementen – koppels- van de index zijn geordend op basis van hun veldwaarde. Dit
laat het toe om snel, volgens het betreffende recordveld, te navigeren door de records. Indexen zijn
een voorbeeld van technieken die deel uitmaken van de secundaire bestandsorganisatie.
Bij een recordtype met records van variabele lengte is er een bijkomende techniek nodig om het
einde van de recordvelden aan te duiden. Een bruikbare techniek, die gebaseerd is op het gebruik
van separatorkarakters zie je in figuur hierboven (b). Daarbij hebben we de volgende, alternatieve
definitie voor het recordtype 'Artiest' gebruikt:
Hierbij hebben de velden 'Naam', 'Voornaam' en 'Hobby' nu een variabele lengte, is 'Hobby' ook een
meerwaardig veld en zijn de velden 'Geboren' en 'Gestorven' optioneel. Omdat er optionele velden
zijn, werd elke veldwaarde in het record gemerkt met een veldnaamindicator (Vo, V1 ,V2 ,V3 ,V4 en Vs).
Verder plaatsen we aan het einde van elk veld een separatorkarakter: voor het laatste veld van het
record is dit een 'einderecord' -indicator, voor alle andere velden is dit een 'einde_veld' -indicator.
Voor de beschrijving van de meerwaardige velden is er ook een derde soort separatorkarakter, de
'einde_waarde' -indicator, nodig om de respectieve waarden voor het veld van elkaar te kunnen
onderscheiden. De separatorkarakters kiezen we zodanig dat ze niet als karakter voorkomen in een
toegelaten waarde van een datatype dat door het dbms wordt ondersteund. Variaties en
alternatieve beschrijvingstechnieken zijn mogelijk.
Zoals is aangegeven in een eerdere figuur ligt de interne laag het dichtst bij de fysieke opslag.
Opslagdetails over de primaire en secundaire bestandsorganisatie, die doorgaans onder het beheer
Hoofdstuk: /Databasesysteem
RECORDTYPE Artiest (Naam: VARCHAR ; Voornaam: VARCHAR ; Hobby:SET(VARCHAR);
OPTIONAL Geboren:INTEGER; OPTIONAL Gestorven:INTEGER)
1
8
DATABASE ontwerpen
1 januari 2011
van het besturingssysteem vallen, worden niet beschreven in de interne laag. Zo wordt er
bijvoorbeeld niet aangegeven op welke fysieke pagina('s) een record is weggeschreven. Evenmin
wordt in de interne laag meer detail gegeven over hoe de toegang tot de fysieke pagina's wordt
geoptimaliseerd. Dergelijke fysieke opslagdetails zijn heel (besturings)systeemspecifiek en worden
buiten de architectuur van een dbms gehouden.
De logische laag
In de logische laag (ook de gemeenschappelijke laag genoemd) werken we met een abstracte
voorstelling van de database. Deze representatie vormt een gemeenschappelijke kijk op de volledige
database, zoals deze wordt gezien door alle gebruikers die geen toegangsbeperkingen tot de
database hebben.
De logische laag houdt alle fysieke opslagdetails verborgen, zodat alle aandacht kan gaan naar de
abstracte beschrijving van alle in de database voorkomende entiteiten, verwantschappen tussen
entiteiten, gebruikersgedefinieerde operatoren en integriteitsbeperkingen. De abstracte beschrijving
van de database dient te gebeuren volgens de voorschriften van het gebruikte databasemodel en
noemen we het databaseschema. Naast de operatoren voor de definitie van databaseschema's legt
het databasemodel de operatoren vast voor het manipuleren van databases. Op deze manier heeft
het databasemodel een zeer belangrijke impact op het soort toepassingen dat het dbms efficiënt kan
ondersteunen. Toepassingen moeten de mogelijkheden van het databasemodel optimaal benutten
en tegelijkertijd berusten in de beperkingen ervan.
De databaserecords uit voorbeeld van de schilderdatabase kunnen in de logische laag gedefinieerd
zijn zoals in een eerder figuur te zien, dat is:
RECORDTYPE Schilderij (ID:CHAR(3); Naam:CHAR(30); Artiest:CHAR(30); Periode: INTEGER;
Waarde:REAL; Eigenaar:CHAR(30) )
RECORDTYPE Artiest (Naam:CHAR(30); Voornaam:CHAR(20); Geboren:INTEGER; Gestorven:
INTEGER)
RECORDTYPE Eigenaar (Naam:CHAR(30); Plaats:CHAR(20); Land:CHAR(20) )
Hoofdstuk: /Databasesysteem
Naast de structurele aspecten, gedrag- en integriteitaspecten, behandelen we op het logische niveau
ook nog andere aspecten, zoals gemeenschappelijke faciliteiten voor beveiliging, falen en herstel en
het delen van de database door verschillende gebruikers.
1
9
DATABASE ontwerpen
1 januari 2011
De externe laag
Omdat een dbms tegelijkertijd door een aantal gebruikers gebruikt kan worden en elke individuele
eindgebruiker meestal slechts een kleine fractie van de database benut, is er ook een externe laag
(ook de individuele laag genoemd). Deze laag staat het dichtst bij de toepassingsontwikkelaars en
eindgebruikers.
In de externe laag worden de zogenoemde (externe) views op de database gedefinieerd. Een view is
een kijk op de database die bepaalt hoe de database wordt gezien door een beperkte groep van
eindgebruikers en toepassingsontwikkelaars: voor deze gebruikers lijkt het alsof de view de database
is. De manier waarop individuele gebruikers de database moeten of wensen waar te nemen, kan
immers verschillen van de manier waarop de database logisch is beschreven. Voor een
gebruikersgroep kunnen, afhankelijk van de noden en toegangsrechten, verschillende views zijn
gedefinieerd. Een view kan toegankelijk zijn voor verschillende gebruikersgroepen.
Een view kan onder meer worden aangemaakt voor beveiliging (afschermen van een deel van de
gegevens), voor het afleiden van nieuwe data uit de database (daarbij moet worden beschreven hoe
de nieuwe data kunnen worden verkregen) of voor dataconversie (om te voldoen aan de
gebruikerswensen).
Voorbeelden van views uit de externe laag voor de schilderdatabase uit het eerdere voorbeeld zijn:
In COBOL-code:
01 Tentoonstellingsitem.
02 ItemNaam PIC X(30).
02 Schilder PIC X(30).
De eerste view in dit voorbeeld is ontworpen voor ontwikkelaars die werken met de COBOLprogrammeertaal.
Het databaserecordtype 'Schilderij' wordt erdoor voorgesteld als 'Tentoonstellingsitem'. Alleen de
'Naam' - en 'Artiest' -velden van het recordtype zijn zichtbaar in de view, zij het onder een voor de
toepassing meer geschikte naam 'ItemNaam', respectievelijk 'Schilder'. De tweede en derde view zijn
ontworpen voor ontwikkelaars die werken met de programmeertaal Java. De tweede view is ook
gedefinieerd voor het recordtype 'Schilderij', daar waar de derde view is gedefinieerd over het
recordtype 'Artiest'. In de derde view is een nieuw afgeleid veld 'LeeftijdBijSterfte' geïntroduceerd,
waarvan de waarde bijvoorbeeld wordt berekend als het verschil 'Gestorven - Geboren' van het
sterftejaar en geboortejaar van de 'Artiest'. Dit nieuwe veld is weergegeven als operatie van de
Hoofdstuk: /Databasesysteem
In Java-code:
class Schilderij {
String Naam;
String Schilder;
}
class Schilder {
String Naam;
String Voornaam;
int LeeftijdBijSterfte( );
}
2
0
DATABASE ontwerpen
1 januari 2011
klasse. De Java-code voor deze operatie maakt deel uit van de implementatie van de klasse' Schilder'
en wordt niet getoond in het voorbeeld.
Niet alle views hoeven gespecificeerd te zijn in een programmeertaal. Als het databasemodel een
eigen DDL (Data Definition Language) heeft, die de definitie van views ondersteunt, gebruiken we
deze voor de specificatie. Een voorbeeld is de DDL van de SQL-standaardtaal voor relationele
databases.
Mappings
In de drie hiervoor beschreven lagen wordt enkel gewerkt met intermediaire beschrijvingen van data.
De feitelijke data bevinden zich in de fysieke opslag. Wanneer gebruikers of toepassingsprogramma's
werken met de database via de externe laag, moeten ze de aangevraagde operaties eerst vertalen
naar operaties in de onderliggende logische laag, daarna naar operaties in de interne laag en die ten
slotte omzetten naar operaties die inwerken op de fysieke opslag. Omgekeerd moeten de resultaten
van een interactie (bijvoorbeeld de resultaten van de zoekopdracht) via respectievelijk de interne
laag, de logische laag en de externe laag naar de gebruiker of het toepassingsprogramma worden
gecommuniceerd. Voor een gebruiker die werkt met de database via de logische laag, valt de
communicatie met de externe laag uiteraard weg. Een interactie via de interne laag is bij veel
databasesystemen niet toegestaan en anders voorbehouden aan de DBA.
De omzettingsprocessen van data en operaties tussen de drie lagen van de architectuur beschrijven
we aan de hand van mappings. Deze zijn in figuur waar de drie lagen structuur in wordt beschreven,
aangegeven met dubbele pijlen. Zoals hiervoor al werd vermeld, wordt de correspondentie tussen de
interne laag en de fysieke opslag buiten de architectuur gehouden, omdat deze te sterk
systeemafhankelijk is. Hierom wordt er in de architectuur geen mapping gemaakt tussen de interne
laag en de fysieke opslag. Uiteraard moet het dbms kunnen werken met de fysiek opgeslagen data,
wat interactie met het besturingssysteem vereist.
Voor elke view in de externe laag is er een 'externe/logische' -mapping gedefinieerd. Deze mapping
definieert de correspondentie tussen de view en de logische voorstelling van de database. Zoals je
ziet in voorbeeld,kunnen velden daarbij een andere naam of datatype krijgen, kunnen er nieuwe
afgeleide velden worden toegevoegd of kunnen er velden verborgen gehouden worden.
Tussen de logische laag en de interne laag is er juist één 'logische/interne' -mapping gedefinieerd.
Deze mapping definieert de correspondentie tussen de logische en de interne representatie van de
database. Opnieuw zijn verschillen mogelijk: extra interne velden, andere veldnamen, enzovoort (zie
de andere voorbeelden).
Voor de volledigheid vermelden we nog dat de meeste systemen het toelaten om views te definiëren
op basis van andere views, in plaats van op basis van de logische beschrijving van de database. In
voorkomend geval moeten er ook 'externe/externe' mappings worden aangemaakt.
Door het gebruik van een 'meerlagen' -architectuur, zoals we in de vorige paragraaf beschreven,
wordt dataonafhankelijkheid verkregen. Dit betekent concreet dat, door een gepaste aanpassing van
de mappings, de beschrijving van de data in een bepaalde laag wordt aangepast zonder dat dit een
impact heeft op de andere lagen. Het grote voordeel hiervan is een gemakkelijker onderhoud van het
databasesysteem. Er wordt een onderscheid gemaakt tussen fysieke en logische
dataonafhankelijkheid.
Fysieke dataonafhankelijkheid
Fysieke dataonafhankelijkheid betekent dat de interne beschrijving van de database en de
'logische/interne' -mapping kunnen worden aangepast zonder dat dit een gevolg heeft op de logische
Hoofdstuk: /Databasesysteem
Dataonafhankelijkheid
2
1
DATABASE ontwerpen
1 januari 2011
beschrijving van de database (en dus ook op de views die eventueel zijn gedefinieerd in de externe
laag).
Aanpassing van de interne beschrijving kan nuttig zijn wanneer de fysieke opslag moet worden
gereorganiseerd. Bijvoorbeeld, wanneer er ten gevolge van gewijzigde interactiepatronen van de
gebruikers wordt gekozen voor een nieuwe, meer efficiënte primaire bestandsorganisatie. Of
wanneer, om de prestatie te verbeteren,
de secundaire bestandsorganisatie moet worden aangepast door de creatie van bijkomende indexen.
Door de fysieke dataonafhankelijkheid worden de primaire en secundaire bestandsorganisatie
afgeschermd van de logische database. Zo komen gebruikers en toepassingsprogramma's niet
onnodig in contact met de fysieke opslagdetails en kan alle aandacht gaan naar de intuïtievere en dus
beter handelbare, abstracte databasestructuur en operatoren. In principe komt alleen de DBA in
contact met de fysieke opslagparameters.
Dankzij de fysieke dataonafhankelijkheid zijn toepassingsprogramma's immuun voor aanpassingen
aan de fysieke opslagstructuur en toegangsmechanismen. Dit biedt uiteraard enorme
onderhoudsvoordelen voor de toepassingsprogramma's.
Logische dataonafhankelijkheid
Logische dataonafhankelijkheid betekent dat de logische beschrijving van de database en de
'externe/logische' -mappings kunnen worden aangepast zonder dat dit een impact heeft op de
diverse views.
Aanpassingen aan de logische databasebeschrijving kunnen nodig zijn wanneer een recordtype moet
worden hernoemd, aangepast of verwijderd of wanneer een nieuw recordtype moet worden
aangemaakt.
Toepassingsprogramma's worden hierdoor ook immuun voor aanpassingen aan de logische
beschrijving van de database, wat opnieuw een onderhoudsvoordeel met zich meebrengt. De
logische databasebeschrijving kan (samen met de fysieke beschrijving) evolueren met de noden van
de onderneming, zonder dat daarbij steeds de bestaande toepassingsprogramma' s moeten worden
aangepast. De enige vereiste is het aanpassen van de 'externe/logische'-mappings.
Ter afsluiting van dit hoofdstuk staan we even stil bij de voor- en nadelen van het gebruik van
databasesystemen. Wanneer je te maken krijgt met een vraag naar gecomputeriseerde
gegevensopslag moet je deze voor- en nadelen grondig afwegen om tot de meest geschikte oplossing
te komen.
Het belangrijkste voordeel bij het kiezen voor het gebruik van een databasesysteem is dat de
systeemontwikkelaar zich niet hoeft te bekommeren om de implementatie van de dbms - faciliteiten
voor onder meer het delen van dezelfde data, beveiliging en integriteit. Alle aandacht kan hierdoor
worden gevestigd op de feitelijke structuur en functionaliteit van de toepassingen. Hoe meer dbms
faciliteiten vereist zijn bij het op te zetten systeem, hoe beter het is om te kiezen voor een
databasesysteem.
Het nadeel van een databasesysteem is dat sommige dbms-faciliteiten voor de toepassing overbodig
kunnen zijn en zo een zekere overlast en prestatieverlies met zich mee kunnen brengen. Hierdoor
kan het voordeliger zijn om te kiezen voor de implementatie van een eenvoudiger, weliswaar volledig
op maat ontworpen, opslagsysteem. Dit kan onder meer het geval zijn wanneer:
 het te ontwikkelen systeem eenvoudig is, weinig of geen dbms-faciliteiten vereist, volledig
omschreven is en met de tijd bijna niet zal evolueren. Men hoeft zich dan niet te
bekommeren over de installatie en configuratie van een complex dbms.
 de eisen (snelheid, compactheid van code en compactheid van opgeslagen data) aan het
systeem zo uitgebreid zijn, dat deze onmogelijk kunnen worden voldaan wanneer een
volwaardig dbms wordt gebruikt. Dit kan onder meer het geval zijn als we strenge eisen
Hoofdstuk: /Databasesysteem
Wanneer gebruik je een databasesysteem?
2
2
DATABASE ontwerpen

1 januari 2011
stellen aan de uitvoeringstijd (toepassingen in productieomgevingen, telecommunicatie
enzovoort) of als we strikte beperkingen opleggen aan de geheugencapaciteit (toepassingen
op mobiele devices).
men te kampen heeft met budgettaire beperkingen: commerciële databasesystemen zijn
meestal niet goedkoop en vragen vaak extra investeringen in zowel hardware, software als
training van de gebruikers. Zelfontwikkelde opslagsystemen vragen echter ook extra
investering zodat in voorkomend geval een grondig kosten/baten analyse van beide
scenario’s zeker aangewezen is. Een ‘open source’ dmbs kan een goed alternatief zijn. Let
wel dit laaste brengt ook de nodige kosten mee. Bijvoorbeeld er moet intern kennis over het
dmbs aanwezig zijn. Deze kennis zal betaald moeten worden.
Hoofdstuk: / Vragen deel 1a
Om deels tegemoet te kunnen komen aan voorgaande situaties bieden de meeste constructeurs van
databasesystemen ook afgeslankte versies van hun systemen aan.
2
3
DATABASE ontwerpen
1 januari 2011
Vragen deel 1a
Hoofdstuk: / Vragen deel 1a
1. Wat is het verschil tussen data (gegevens) en informatie? Waarom is het nuttig om binnen de
context van een database een onderscheid te maken tussen beiden?
2. Wat is een database, een databasesysteem en een databasemanagementsysteem?
3. Uit welke componenten bestaat een databasesysteem?
4. Schets de geheugen hiërarchie. Bespreek de rol van de verschillende geheugentypen die
hierin voorkomen met betrekking tot databases. Waarom zijn databasebuffers belangrijk?
5. Wat zijn database records? Welke types kan men onderscheiden? Hoe kunnen deze worden
beschreven?
6. Waartoe dienen datawarehousing en datamining? Waar staan deze ten opzichte van
databases?
7. Wat zijn gebruikersprofielen? Bespreek de taken van een data-administrator en een
database-administrator.
8. Bespreek databasedefinitie, databasemanipulatie en databasereconstructie.
9. Waarom is het noodzakelijk dat databases kunnen worden gedeeld? Wat zou er gebeuren als
dat niet kan?
10. Waarom is het noodzakelijk dat databases worden beveiligd?
11. Waarom is er een verschil tussen de interne laag van de drielagen architectuur en de fysieke
dataopslag? Wat is het nut van de interne laag?
12. Wat zijn indexen? Waartoe worden deze gebruikt in databases?
13. Wat is het nut van de logische laag en de externe laag van de drie lagen architectuur?
14. Waarop slaat dataonafhankelijkheid? Geef met een concreet voorbeeld aan wat het nut
hiervan is.
15. Waarmee moet men rekening houden bij de keuze om een toepassing wel of geen
databasesysteem te gebruiken?
16. Bedenk een realistische toepassing waarbij je zeker een zelfontwikkeld opslagsysteem zou
kiezen.
2
4
DATABASE ontwerpen
1 januari 2011
Relationele database
In een relationele database worden gegevens, voor de gebruikers, geordend in tabellen. Dit maakt
het onder andere mogelijk om snel specifieke gegevens te selecteren. Hieronder staan de gegevens
in een databasetabel.
Door in deze tabel naar een cursus_id te zoeken in de cursus_id-kolom kunnen snel de bijhorende
titel en categorie opgevraagd worden. Dat gaat veel sneller dan door een tekstbestand heen
wandelen totdat je de gewenste informatie gevonden hebt. Bij een relationele database kunnen
gegevens uit specifieke rijen, kolommen en zelfs uit verschillende tabellen gecombineerd opgevraagd
worden. Je kunt het cursus_id (de 'primaire sleutel') gebruiken om de cursus te koppelen aan
gegevens in andere tabellen. Hierover later meer.
Het concept van de relationele database waarin gegevens zijn geordend in tabellen is bedacht in de
jaren 70 door meneer Ted Codd. De relationele database is een zeer krachtige methode voor het
opslaan van gegevens gebleken. Tegenwoordig gebruiken heel veel programma's een relationele
database om gegevens in op te slaan.





Oracle. Oracle wordt in de regel gebruikt voor grotere professionele applicaties
Microsoft SQL server. Het professioneel RDBMS van Microsoft wordt gebruikt voor alle
soorten applicaties, van studentenapplicaties tot professionele applicaties met veel
gebruikers. MSSQL is alleen beschikbaar voor Windows.
Mysql community en onder zowel hobbyisten en beginners als professionelen wereldwijd.
Mysql is gratis (of beter gezegd 'vrij') verkrijgbaar.
IBM doet ook een grote duit in het zakje met verschillende databasesystemen, waarvan DB2
de populairste is.
Microsoft Access is een uitgekleed RDBMS. Het mist sommige functies van een professioneel
RDBMS en is bedoeld voor kantoor- en thuisgebruik.
Een database-ontwerp maak je niet voor een bepaald RDBMS. Het ontwerp van de database is
onafhankelijk van het databasesysteem, zolang je een relationeel databasesysteem (RDBMS)
gebruikt. Je zou de database die we in deze cursus ontwerpen op elk van bovengenoemde database-
Hoofdstuk: Relationele database
Tegenwoordig zijn er tal van verschillende Relationele Database Management Systemen (RDBMS) in
gebruik. Tot de toppers van de database-industrie behoren
2
5
DATABASE ontwerpen
1 januari 2011
Hoofdstuk: Relationele database
systemen kunnen maken. Je moet daarbij wel gebruik maken van de voor die database ontwikkelde
instrumenten (SQL dialecten enzovoort)
2
6
DATABASE ontwerpen
1 januari 2011
Voordelen en mogelijkheden van relationele databases
Naast het snel opzoeken van informatie heeft een relationele database nog een aantal voordelen.







Een relationele database kan gegevens in verschillende tabellen relateren door het gebruik
van sleutels. Daardoor kunnen gerelateerde gegevens uit verschillende tabellen tegelijk
opgevraagd worden.
Efficiënte opslag van gegevens. Gegevens worden maar één keer en op één plek zijn
opgeslagen. Dit zorgt ervoor dat een wijziging of verwijdering van informatie altijd maar op
één plek hoeft te gebeuren.
Met een relationele database kan je regels opstellen voor het soort gegevens dat in een veld
opgeslagen kan worden. Zo kun je onder andere datumvelden, tekstvelden en numerieke
velden aanmaken.
Integriteit van gegevens. Door het formuleren van de juiste relaties van tabellen kun je de
integriteit (correctheid, betrouwbaarheid) van de gegevens die zijn opgeslagen beter
garanderen.
De meeste relationele databasesystemen kennen een rechtenstructuur, waarmee aan
verschillende gebruikers verschillende rechten toegekend kunnen worden. Zo kun je het
recht hebben om gegevens uit de database te op te vragen, maar niet om nieuwe gegevens
in te voeren.
Relationele databases zijn geavanceerde programma's die geoptimaliseerd zijn voor
bepaalde taken, zoals bijvoorbeeld het zoeken door numerieke velden, data sorteren, etc.
Deze technologie maakt het mogelijk om zeer snel gegevens te zoeken en te presenteren.
Voor het bevragen van een relationele database is een krachtige 'query taal' beschikbaar met
de naam 'Structured Query Language' (SQL). Deze taal maakt het onder andere mogelijk om
zeer geavanceerde gegevensselecties te maken uit de database.
Het relationeel is een standaard, net als SQL, de taal om relationele database te bevragen.
Standaardisering maakt het mogelijk om gegevens uit de ene database vaak relatief
eenvoudig naar de andere database over te zetten.
Hoofdstuk: Relationele database

2
7
DATABASE ontwerpen
1 januari 2011
Klantcontactsysteem
Zoals gezegd is de leidraad van deze cursus relationele database ontwerpen een
'klantcontactsysteem'.
De eisen voor dit klanten systeem zijn:
In dit klantcontactsysteem willen we contactmomenten opslaan. Een contactmoment ontstaat elke
keer dat er tussen ons en een van onze klanten gecommuniceerd wordt via de telefoon, per e-mail of
per post. Een programma dat gebruik maakt van deze database kan de database gebruiken om
bijvoorbeeld een overzicht te genereren van de contactmomenten die er geweest zijn met een klant.
Of om klantgegevens op te vragen van een specifieke klant.
We willen voor het klantcontactsysteem een database ontwerpen waarin we klantgegevens en
'contactmomenten' op kunnen slaan. Klantgegevens zijn bijvoorbeeld naam, adres, telefoonnummer
en het e-mailadres. Een contactmoment is een beschrijving van elke keer dat er gecommuniceerd
wordt tussen het bedrijf en een klant.
Door een goed database-ontwerp te maken kunnen we een programma dat gebruik gaat maken van
de database ruime mogelijkheden geven voor bijvoorbeeld


Het opvragen van klantgegevens (met behulp van het SQL SELECT statement)
Het opvragen van alle contactmomenten van een klant
Het zoeken van contactmomenten op een bepaalde datum of een datuminterval.
Het aanpassen, invoeren en verwijderen van klantgegevens (met behulp van de SQL UPDATE
statements)
Het aanpassen, invoeren en verwijderen van contactmomenten
etc
Hoofdstuk: Relationele database




2
8
DATABASE ontwerpen
1 januari 2011
Tabellen en de primaire sleutel
Een relationele database bestaat uit tabellen. In deze tabellen zijn gegevens van dezelfde soort in
rijen of in vaktaal records opgeslagen. Je zag eerder al een voorbeeld van een tabel waarin wat
informatie over cursussen is opgeslagen. Die tabel bestaat uit 6 records die elk informatie over één
cursus bevatten. Alle cursussen zijn verschillend, maar van elke cursus is dezelfde informatie
opgeslagen.
Voor het klantcontactsysteem dat we willen maken ligt het voor de hand dat we een klanttabel
maken. Hieronder staat een voorbeeld van een tabel met klantinformatie.
Hoe kun je in deze tabel een klant selecteren? Je zou kunnen zoeken op de voornaam. Dat is geen
goed idee, want naarmate het aantal klanten in de tabel groeit wordt de kans groter dat er nog een
klant komt die Jan heet en dan kun je niet meer met zekerheid de juiste Jan selecteren. Je zou
kunnen zoeken op een combinatie, bijvoorbeeld de voornaam en achternaam. Maar hoe lang zou het
duren voordat zich een tweede Jan Jansen aanmeldt als klant?
Om klanten uniek te kunnen identificeren moet elke klant gekoppeld zijn aan een uniek stukje
informatie, bijvoorbeeld een klantnummer. Deze unieke informatie wordt de primaire sleutel
(primary key) genoemd. Een van de belangrijkste regels van het relationeel model is dat alle rijen in
een tabel uniek te identificeren zijn door middel van de primaire sleutel. Het cursus_id uit het eerste
tabelvoorbeeld op deze pagina is zo'n primaire sleutel.
Er zijn tal van voorbeelden te bedenken van nummers en codes uit het dagelijks leven die
waarschijnlijk kunnen dienen als primaire sleutel in een database.
Een bestelnummer
Een rekeningnummer
Een sofinummer
Een factuurnummer
Een klantnummer
Een productnummer
Wat hebben al deze codes gemeen?


Ze zijn allemaal uniek. Jouw rekeningnummer bestaat altijd maar één keer, net als een
factuurnummer, je sofinummer, etc.
Ze werken allemaal als toegangsweg naar meer informatie. Aan een factuurnummer is een
datum, een bedrag, etc gekoppeld. Aan een productnummer kan een productbeschrijving,
een plaatje, etc gekoppeld zijn.
De primaire sleutel wordt dus gebruikt om rijen in tabellen uniek te identificeren en om gegevens in
verschillende tabellen aan elkaar te koppelen. Primaire sleutels zijn zoals gezegd altijd uniek en
Hoofdstuk: Relationele database






2
9
DATABASE ontwerpen
1 januari 2011
mogen daarom maar 1 keer voorkomen in de kolom. Het is in onderstaande tabel dus niet mogelijk
om een nieuwe rij met klantnummer 1 toe te voegen. Geef je aan dat het klantnummer de primaire
sleutel is, dan zal het databasesysteem er (voor zover ik weet altijd) automatisch voor zorgen dat
klantnummers in die kolom uniek zijn.
Hoofdstuk: Relationele database
Je kunt aan deze afbeelding overigens niet zien dat het klantnummer de primaire sleutel is. In de
ontwerpweergave van de tabel is dat wel te zien.
3
0
DATABASE ontwerpen
1 januari 2011
Gegevens koppelen
In de klanttabel slaan we klantgegevens op, maar we moeten voor het klantcontactsysteem ook
contactmomenten opslaan. Een contactmoment ontstaat wanneer een klant belt, e-mailt of een brief
schrijft.
Je zou misschien bedenken om de datum en een beschrijving van het contactmoment in de
klanttabel op te slaan bij de juiste klant. Dat kan echter niet. Het klantnummer moet uniek zijn in de
klantnummerkolom. Er mag dus altijd maar 1 rij per klant zijn in de klanttabel. Je zou nog alle
contactmomenten in 1 cel kunnen opslaan op dezelfde rij als de klantgegevens (als een soort flat file
in 1 cel), maar dat is absoluut niet wenselijk, omdat het dan heel moeilijk wordt om door de
contactmomenten te zoeken. (Nb. later in deze cursus databases worden de regels voor goed
database-ontwerp op een rij gezet).
Conclusie: we moeten een nieuwe tabel maken voor contactmomenten. Eerst bepalen we wat we
allemaal van een contactmoment willen opslaan.






contactmoment_id (primaire sleutel)
klantnummer (zodat het contactmoment aan één klant gekoppeld is)
Datum en tijd
Een beschrijving van het contactmoment (Inhoud telefoongesprek, inhoud e-mail, inhoud
brief, etc)
Nam het bedrijf contact op met de klant of andersom?: inkomend_uitgaand
Communicatietype (telefoon, e-mail, fax): com_type
Op elke rij van de contactmomententabel staat informatie over een uniek contactmoment. Elk
contactmoment is uniek identificeerbaar door de primaire sleutel, het contactmoment_id (eerste
kolom). Bovendien is elk contactmoment gekoppeld aan een klant uit de klanttabel door middel van
het klantnummer in de tweede kolom.
Met behulp van het klantnummer kunnen bij elke klant in de klanttabel de contactmomenten
opgezocht worden en andersom bij elk contactmoment de betreffende klant uit de klanttabel. Er is
hier sprake van een '1 op veel' relatie tussen de klant en zijn of haar contactmomenten.
Nu je een voorbeeld gezien hebt van een database is het tijd om in te gaan op de relaties die kunnen
bestaan tussen tabellen. Het voorbeeld van de klanttabel en de contactmomententabel is een 'één
Hoofdstuk: Relationele database
Hieronder staat een conceptversie van de contactmomententabel. Later in deze cursus database
ontwerpen bekijken we onderstaande tabel nog eens kritisch aan de hand van de ontwerpregels voor
relationele databases.
3
1
DATABASE ontwerpen
1 januari 2011
op veel relatie'. Voor elke klant (1) kunnen meerdere (veel) contactmomenten opgeslagen zijn. Bij
het ontwerpen van relationele databases onderscheiden we 3 soorten relaties tussen tabellen.



De één op veel relatie
De veel op veel relatie
De één op één relatie
De een op veel relatie
Bij de één op veel relatie is 1 ding gekoppeld aan 0, 1 of meer andere dingen. Je zag al een voorbeeld
met klanten en contactmomenten. 1 klant kan 0, 1 of meerdere contactmomenten hebben en een
contactmoment is altijd gekoppeld aan 1 klant.
Hetzelfde geldt voor de relatie tussen moeders en hun kinderen. Elk kind heeft maar 1 moeder, maar
een moeder kan geen, één of meerdere kinderen hebben. De één op veel relatie modelleer je in een
relationele database als twee tabellen. Elke rij in tabel A correspondeert met 0, 1, of meerdere rijen
uit tabel B. (zie afbeelding).
De klanttabel speelt de rol van tabel A en de contactmomententabel die van tabel B. Elke rij in tabel
A (ofwel, elke klant) is gekoppeld aan 0,1 of meerdere rijen (ofwel, contactmomenten) in tabel B.
Het klantnummer is de primaire sleutel in tabel A, het klantnummer in tabel B wordt de vreemde
sleutel genoemd. Een vreemde sleutel is een veld in een tabel dat verwijst naar de primaire sleutel
van een andere tabel. Het klantnummer in onze database is de primaire sleutel in de klanttabel en in
andere tabellen is het klantnummer een vreemde sleutel.
Klantnummer is de primaire sleutel (ps) in de klanttabel
Hoofdstuk: Relationele database
tabel klant
3
2
DATABASE ontwerpen
1 januari 2011
tabel contactmoment
Klantnummer is de vreemde sleutel (vs) in de contactmomententabel. Hij verwijst naar het
klantnummer in de klanttabel. Het contactmoment_id is de primaire sleutel (ps) in de
contactmomententabel.
Het kan van belang zijn of de een op veel relatie “0 of meer” is of dat hij “1 of meer is”. Hier moet in
het database ontwerp mee rekening gehouden worden.
De veel op veel relatie
Tabel A en tabel B zijn beide tabellen met een primaire sleutel. Tabel A_B verbindt records uit tabel A
en B en heet een 'koppeltabel'. Een koppeltabel bestaat uit maar twee kolommen die allebei een
vreemde sleutel bevatten.
Een “vreemde sleutel” is een waarde in een tabel (die geen sleutel in deze tabel is) die in een andere
tabel wel sleutel is. De vreemde sleutel in de linkerkolom verwijst naar de primaire sleutel uit tabel A
en die in de rechterkolom naar de primaire sleutel uit tabel B. De primaire sleutel in koppeltabel A_B
is samengesteld uit de twee vreemde sleutels. Je ziet in het voorbeeld dan ook dat de combinatie van
de twee velden uniek moet zijn in koppeltabel A_B. In het klantcontactsysteem komt (vooralsnog)
Hoofdstuk: Relationele database
De veel op veel relatie is een relatie waarbij meerdere rijen uit tabel A gekoppeld kunnen zijn aan
meerdere rijen uit tabel B. Een voorbeeld is een school, waarbij docenten les geven aan studenten.
Elke docent geeft les aan 0, 1 of meer studenten. Andersom kan elke student les krijgen van 0, 1 of
meerdere docenten. Een ander voorbeeld is de relatie tussen biermerken en leveranciers. Elke
leverancier levert meerdere biermerken, maar elk biermerk kan ook door meerdere leveranciers
geleverd worden. De veel op veel relatie modelleer je met drie tabellen. Twee brontabellen en één
koppeltabel.
3
3
DATABASE ontwerpen
1 januari 2011
geen veel op veel relatie voor, daarom staat hieronder een voorbeeld met bieren en hun
leveranciers.
Bovenstaande tabellen koppelen bieren en leveranciers in een veel op veel relatie. Eén bier kan door
0, 1 of meerdere leveranciers geleverd worden. Gentse Tripel (157) wordt bijvoorbeeld geleverd door
Horeca Import NL (157, AC001), Jansen Horeca (157, AB899) en Petersen Drankenhandel (157,
AC009). Andersom levert Petersen Drankenhandel 3 bieren uit de bierentabel, te weten Gentse
Tripel (157, AC009), Uilenspiegel (158, AC009) en Jupiler (163, AC009).
Een ander goed gebruik is om in databasemodellen relaties te benoemen. Je zou bij de hierboven
afgebeelde relatie in een model de opmerkingen "levert" kunnen zetten. Elke rij in de tabel zegt
namelijk "bier X wordt geleverd door leverancier Y", of "Leverancier Y levert bier X". Het benoemen
van relaties maakt een databasemodel leesbaarder. Deze benoeming komt niet terug in de eigenlijke
database of in de software die gebruik maakt van de database.
De een op een relatie
De één op één relatie tussen tabellen komt niet heel vaak voor. Bij de één op één relatie heeft elke rij
in tabel A 0 of 1 corresponderende rij in tabel B. Deze relatie wordt weleens gebruikt om de
tekortkomingen van een databasesysteem te omzeilen door bijvoorbeeld een tabel op te delen om
prestatiewinst te behalen.
Hoofdstuk: Relationele database
Merk op dat in bovenstaande tabellen de primaire sleutelvelden blauw en onderstreept zijn
weergegeven. In modellen van relationele databases worden primaire sleutels vaak onderstreept.
Zoals je ziet is de primaire sleutel van de koppeltabel 'bier_leverancier' samengesteld uit twee
velden. Elke rij in de koppeltabel bestaat uit de combinatie van een bier_id met een leverancier_id.
Deze tabel bestaat zogezegd uit een samengestelde primaire sleutel. Primaire sleutels moeten uniek
zijn, dus de combinaties moeten uniek zijn in de tabel.
3
4
DATABASE ontwerpen
1 januari 2011
Merk op dat elke individuele tabel vol zit met 1 op 1 relaties: de relatie tussen de primaire sleutel en
de rest van de gegevens op die rij is een 1 op 1 relatie, of laat ik zeggen zou dat moeten zijn als je het
relationeel model helemaal volgt. Een voorbeeldje: in de biertabel op de vorige pagina is Gentse
Tripel in een 1 op 1 relatie gekoppeld aan de primaire sleutel 157. Dit is precies de reden dat 1 op 1
relaties tussen tabellen niet vaak voorkomen.
Hoofdstuk: Relationele database
Een relationeel database-ontwerp is zoals je hebt gelezen een verzameling van tabeldefinities,
waarin gegevens in verschillende tabellen gekoppeld zijn in relaties. Voor het kiezen van de juiste
relaties tussen gegevens formuleert het relationeel model een aantal 'normaalvormen'. In het
volgende hoofdstuk ga ik in op het doel van normaliseren en de taken die hierbij horen.
3
5
DATABASE ontwerpen
1 januari 2011
Database normaliseren
De regels voor goed relationeel database-ontwerp zijn samengevat in 5 'normaalvormen', waarbij de
eerste normaalvorm de laagste en de vijfde de hoogste (meest genormaliseerd) is. Deze
normaalvormen zijn richtlijnen voor het juist ontwerpen van een relationele database.
Normaliseren heeft een aantal doelen.



Flexibiliteit. De genormaliseerde structuur van de database zorgt ervoor dat gegevens op
veel verschillende manieren opgevraagd en bijgewerkt kunnen worden.
Integriteit. In een genormaliseerde database ben je gegevens zeer betrouwbaar opslaan.
In een genormaliseerde database worden gegevens maar op 1 plek opgeslagen. Als je data
wil invoeren, aanpassen of verwijderen hoef je dat dus maar op 1 plek te doen.
Het normaliseren van een database schijnt voor veel mensen taaie materie te zijn. Ik heb zelf ook die
ervaring gehad, hoewel dat eigenlijk onterecht is. Het normaliseren van een database komt eigenlijk
neer op het nastreven van de volgende zaken en die zijn met een beetje oefening en puzzelen vaak
redelijk gemakkelijk te realiseren.




Het verdelen van gegevens in logische samenhangende groepen.
Het minimaliseren van de hoeveelheid data die dubbel opgeslagen is, ofwel het voorkomen
van 'redundancy'.
De gegevens zo organiseren dat het aanpassen of verwijderen van een gegeven altijd maar
op één pek hoeft te gebeuren.
Gegevens zo organiseren dat ze snel en efficiënt op te vragen zijn.
Hoofdstuk: Relationele database
De meeste applicaties gebruiken databases die zijn genormaliseerd tot de 1ste, de 2de of de 3de
normaalvorm. De 4de en 5de normaalvorm zie je zelden. In deze cursus databases bespreek ik
daarom alleen de eerste, tweede en derde normaalvorm.
3
6
DATABASE ontwerpen
1 januari 2011
De eerste normaalvorm (1NF)
De eerste normaalvorm is een set basale ontwerpregels die op elke database van toepassing moeten
zijn. Een tabel is de representatie van een 'ding' uit het systeem dat je maakt. Bijvoorbeeld
bestellingen, een klanten, contactmomenten, producten, etc. Elke rij in de tabel is een uniek
exemplaar van dat 'ding'. We noemen zo’n rij ook wel een “tupel”. Een rij vertegenwoordigt
bijvoorbeeld 1 bestelling, 1 klant, 1 contactmoment, 1 bestelling, etc.


Atomiciteit: elk veld bevat maar één waarde. Een adres bijvoorbeeld hoor je op te slaan in
aparte velden voor de straatnaam, het huisnummer en de huisnummerextensie.
Elke tabel heeft een primaire sleutel: een zo klein mogelijk aantal velden dat een rij (record)
uniek identificeert.
De volgorde van de rijen in de tabel is onbelangrijk (als je wil weten in welke volgorde
bestellingen zijn binnengekomen moet je hiervoor een datum en tijd veld maken. Hiervoor
de rijvolgorde, in de database, gebruiken is niet de bedoeling)
Hoofdstuk: Relationele database

3
7
DATABASE ontwerpen
1 januari 2011
De tweede normaalvorm (2NF)
De tweede normaalvorm: het verwijderen van redundante gegevens



De database voldoet aan alle regels van de eerste normaalvorm.
Zo min mogelijk gegevens worden dubbel opgeslagen in de database.
De velden die geen primaire sleutel zijn, zijn afhankelijk van de primaire sleutel.
Je moet bij de tweede normaalvorm je tabellen goed inspecteren op gegevens die dubbel opgeslagen
worden in een kolom. Gegevens die dubbel opgeslagen worden komen in aanmerking voor
afsplitsing in een aparte tabel. Een mooi voorbeeld hiervan is het com_type veld in de
contactmomententabel. In die kolom worden de waardes 'telefoon', 'email' en 'brief' telkens
herhaald. Daar worden gegevens dus dubbel opgeslagen (redundancy) Afsplitsen is het devies. We
maken een nieuwe tabel voor 'communicatietypes'.
Op deze manier hoeft een wijziging van de waarde 'telefoon' naar 'telefonisch' maar op 1 plek te
gebeuren. Voor de inkomend_uitgaand kolom geldt ook dat gegevens dubbel opgeslagen worden.
Hierin worden 'inkomend' en 'uitgaand' steeds herhaald. Wat is echter de kans dat er ooit een
waarde bijkomt? 0. Want met 'inkomend' en 'uitgaand' zijn alle mogelijkheden gedekt. Wat is de
kans dat je de waardes 'inkomend' en 'uitgaand' in de toekomst wil wijzigen? Klein. Vandaar dat we
de herhaling van waardes in de inkomend_uitgaand kolom toestaan.
Merk overigens op dat er altijd wel een beetje herhaling van gegevens is. Het klantnummer (vreemde
sleutel) in de contactmomententabel wordt steeds dubbel opgeslagen, maar dat is herhaling die niet
te voorkomen is. We willen immers wel dat elk contactmoment aan een klant gekoppeld is.
Hoofdstuk: Relationele database
Aan elk contactmoment is een communicatietype gekoppeld door het com_type veld. Dit veld is een
vreemde sleutel. Het verwijst naar de primaire sleutel (com_type_id) van de communicatietypen
tabel.
3
8
DATABASE ontwerpen
1 januari 2011
De derde normaalvorm (3NF)
De derde normaalvorm: transitieve afhankelijkheden, een moeilijke term voor iets vrij eenvoudigs.


De database voldoet aan alle regels van de tweede normaalvorm.
Van een tabel die voldoet aan de derde normaalvorm zijn alle velden die geen primaire
sleutel zijn uitsluitend afhankelijk van de primaire sleutel.
Zie de volgende klantentabel van een voetbalschoenenhandel.
In bovenstaande tabel zijn niet alle gegevens uitsluitend afhankelijk van de primaire sleutel. Er zijn
nog andere relaties tussen de gegevens in een record: uit de postcode is de plaatsnaam en de
provincie af te leiden. Er bestaat een 'transitieve relatie' tussen deze gegevens.
Welnu, afsplitsen is zeker mogelijk en wordt in de praktijk ook weleens gedaan. Je kunt in de
klantentabel voor elke klant alleen de (eerste 4 cijfers van de) postcode opslaan en de bijhorende
gegevens (provincie, plaatsnaam) in een aparte tabel stoppen. Deze aparte postcode-plaatsnaamprovincie tabel kun je kopen.
De relatie tussen het totaalbedrag exclusief BTW en het totaalbedrag inclusief BTW is duidelijk. Het
bedrag inclusief BTW is altijd 19% hoger dan het bedrag exclusief BTW. De waardes van de twee
prijskolommen zijn uit elkaar af te leiden. In dit geval zou je én van de twee kolommen moeten
verwijderen. Het omrekenen tussen exclusief en inclusief BTW laat je over aan het programma dat
van de database gebruik maakt. Kort gezegd: je hoort nooit gegevens op te slaan die af te leiden zijn
Hoofdstuk: Relationele database
Een ander voorbeeld van de toepassing van de derde normaal is deze (veel te) eenvoudige
bestellingentabel van een Nederlandse webwinkel.
3
9
DATABASE ontwerpen
1 januari 2011
Hoofdstuk: Relationele database
uit andere gegevens in de tabel. In de praktijk wordt zeker bij kleinere applicaties de derde
normaalvorm niet altijd toegepast.
4
0
DATABASE ontwerpen
1 januari 2011
Het definitieve database-ontwerp
We passen voor onze database de normalisatieregels losjes toe. Hieronder staan de tabellen en hun
onderlinge relaties afgebeeld. (Afbeelding komt uit MS Access 2003).
In de database zijn klanten gekoppeld aan contactmomenten door middel van het klantnummer. Je
ziet daarom in de afbeelding een verbindingslijn tussen het klantnummer in de klanttabel en het
klantnummer in de contactmomententabel. In de klantabel is klantnummer de primaire sleutel (dik
gedrukt) en in de contactmomententabel is het de vreemde sleutel. Dezelfde koppeling bestaat
tussen de contactmomententabel en de communicatietypentabel.
Een tweede aanmerking is dat de postcode in één veld opgeslagen is, terwijl je de cijfers nog van de
letters zou moeten scheiden als je streng bent (atomiciteit). Aan de hand van de eerste 4 cijfers kun
je de plaatsnaam, de gemeente en de provincie afleiden. Wil je een postcodetabel aan de database
hangen, dan is het dus handig om de postcode te verdelen over twee velden. Voor onze doeleinden
is dit echter niet nodig.
Hoofdstuk: Relationele database
Het soort relatie staat ook in de afbeelding. 1 klant correspondeert met 0 tot oneindig veel
contactmomenten. Oneindig wordt weergegeven als een ∞ teken. Hetzelfde geldt voor de relatie
tussen contactmomenten en communicatietypes. 1 communicatietype is gekoppeld aan 0 tot
oneindig veel contactmomenten. Er is nog wel iets aan te merken op deze database. Hij voldoet niet
aan de derde normaalvorm. Er bestaat er een transitieve relatie tussen de postcode en de
woonplaats. In Nederland zijn deze uit elkaar af te leiden. Om deze cursus niet nog langer te maken
heb ik besloten hiermee vrede te nemen. Ook is het voor een eenvoudige applicatie niet nodig om
het af te splitsen. Je zou dan ook een volledige postcodetabel nodig hebben. Deze is tegen kosten te
verkrijgen (en die kosten zijn niet mals, zeker als je de tabel up to date wilt houden:
abonnementskosten)
4
1
DATABASE ontwerpen
1 januari 2011
Nog een voorbeeld: webwinkel
Om deze cursus database ontwerpen af te sluiten ontwerp ik hier een iets complexere database voor
een webwinkel. Webwinkels zijn systemen die over het algemeen producten kunnen tonen,
bestellingen kunnen opnemen en klantgegevens kunnen bijhouden. Dat is natuurlijk nog geen
complete webwinkel, maar de essentie van het systeem is daarmee wel beschreven.
Enkele belangrijke spelers in dit systeem zijn dus de klant, het product en de bestelling. Welke
relaties bestaan er tussen deze zaken? Tussen klant en bestelling bestaat een één op veel relatie. Een
klant kan 0, 1 of meerdere bestellingen plaatsen en andersom is een bestelling altijd gekoppeld aan
precies 1 klant.
Tussen bestelling en product bestaat een veel op veel relatie. Elke bestelling bestaat uit 1 of meer
producten en elk product kan onderdeel zijn van 0,1 of meer bestellingen.
Microsoft Access 2003 geeft de relaties weer zoals hierboven is afgebeeld. De relatie tussen klant en
bestelling is een een op veel relatie. Tussen bestelling en product bestaat een veel op veel relatie. De
tabel bestelling_product is een koppeltabel die ervoor zorgt dat elk product aan meerdere
bestellingen gekoppeld kan zijn en andersom dat elke bestelling uit meerdere producten kan
bestaan. Merk op dat bestelling en product beide een één op veel relatie met de koppeltabel
hebben.
In de producttabel is alleen de prijs exclusief BTW opgenomen. Als je de prijs exclusief BTW kan
opvragen kun je daar in een programma makkelijk de prijs inclusief BTW uit berekenen. In de
producttabel is uitsluitend informatie opgeslagen die uniek bij een product (product_id) hoort.
Merk ook op dat nergens een totaalbedrag voor de bestelling is opgenomen. Deze informatie is
berekenbaar (af te leiden) en hoeft dus niet apart opgeslagen te worden. Het totaalbedrag vind je
natuurlijk eenvoudig door de prijzen van alle producten die deel uitmaken van de bestelling op te
tellen.
Conclusie
Een relationele database in mooi gereedschap voor het efficiënt opslaan en snel opvragen en
bewerken van gegevens. Het is niet voor niets dat de relationele database zo immens populair is.
Hoofdstuk: Relationele database
Let ook op de gegevens die zijn opgeslagen. In de klanttabel zijn (enkele) klantgegevens opgeslagen.
"Da's logisch", zou Johan Cruijff zeggen en dat is het ook. In de bestellingentabel is het klantnummer
opgenomen, zodat elke bestelling aan 1 klant gekoppeld is. Bovendien zijn de datum en tijd
opgenomen. Dat zijn eigenschappen die bij de gehele bestelling horen.
4
2
DATABASE ontwerpen
1 januari 2011
In deze cursus database ontwerpen heb ik me puur gericht op het ontwerp van de database. Wil je
echt met een database aan de slag dan zijn de volgende websites en artikelen zeker de moeite
waard.


In de categorie database van Leren.nl vind je een groot aantal database-gerelateerde
artikelen en cursussen
In de categorie SQL van Leren.nl kun je veel lezen over SQL, de 'Structured Query Language'.
SQL is de taal die gebruikt wordt om databases te bevragen en bewerken. Een onmisbare taal
als je in de praktijk met databases aan de slag wil!
Bij Wikipedia vind je uitgebreide informatie over de geschiedenis van de database,
verschillende database-modellen, de interne werking van databases en de technische
mogelijkheden van databases.
Hoofdstuk: Relationele database

4
3
DATABASE ontwerpen
1 januari 2011
Het ontwerp proces van een database.
Problemen, een slechte Database
Hier onder ziet u een database met drie tabellen. Deze database is slecht ontworpen. Elke tabel heeft
een aantal problemen.
Tabel Abonnement
Plaats
Naam
Krant
Gouda
HJ Nootenboom Automatisering Gids Stam
Gouda
H.J Nootenboom Computable
Zevenhoven S Peuskens
Uitgever
VNU
Computable
Stam
Problemen van de tabel Abonnement
De uitgever van het blad Computable is ambigu (voor meerdere uitleg vatbaar). Voor de ene
lezer is het Stam, voor andere de VNU.
Één en dezelfde lezer staat met twee verschillende spellingswijzen in de administratie.
Bezorg Adres
Naam
Postcode Krant
HJ Nootenboom 2803 DA
Adres
Autom. Gids Groenhovenweg 43
Problemen van de tabel Bezorg Adres
De postcode 12345ABC is fout, maar er is geen mogelijkheid om de juistheid van een
postcode te controleren.
Het adres van één lezer staat 2x vermeld. Dat is niet alleen lastig bij verhuizen (dubbel
bijwerken), maar het is ook onbekend wat nu de juiste versie is.
Voor het abonnement van S Peuskens op Computable ontbreken de adres gegevens.
Fakturering gegevens
Prijs Geboortedatum Naam
75,00 30 aug 1964
Postcode
Plaats
Henk Jan Nootenboom 00000 HHH Juinen
Hoofdstuk: Het ontwerp proces van een database.
H.J. Nootenboom 12345ABC Computable Groenh. weg 43-II
4
4
DATABASE ontwerpen
0,00 01-04-1572
Stefan Peuskens
1 januari 2011
2435xh
Z. Hoven
Problemen van de tabel Fakturering gegevens
Er is géén relatie te leggen tussen facturering gegevens en abonnement. Het is onduidelijk
welke prijs voor welke krant geldt. Iemand kan een factuur krijgen, zonder abonnement, of
andersom. Er is geen goede verwijzing foreign key.
De postcode en plaatsnaam van Henk Jan Nootenboom zijn onzin. De factuur zal nooit
aankomen. Het is voor de computer onmogelijk om de adresgegevens uit een andere tabel te
halen, door de net weer andere schrijfwijze van de naam.
Stefan Peuskens krijgt wél een factuur van 0 gulden, maar zal geen krant ontvangen. Het
bezorgadres is immers niet bekend. Of heeft die € 0,- een speciale betekenis en wordt er
daarom géén factuur verstuurd en ook geen krant bezorgd?
De kolom geboortedatum is niet relevant, valt buiten de scope. De datum 01-04-1572 is niet
betrouwbaar, vast expres onjuist opgegeven.
Een slecht ontworpen database kent de volgende problemen:
Bij elkaar horende data staat verspreid over diverse tabellen. Een wijziging moet op vele
plaatsen doorgevoerd worden. Het is mogelijk dat informatie maar half aanwezig is, in de
ene tabel wel en in de andere tabel niet.
Data is inconsistent of ambigu (voor meerdere uitleg vatbaar).
De database is nodeloos ingewikkeld, met veel Kunst & Vliegwerk in elkaar geprutst. De
database-ontwerper heeft moeilijk gedaan, terwijl het makkelijk kan.
De database is traag, inflexibel, lastig uit te breiden en kan niet alle praktijksituaties aan.
Het ontwikkel proces voor een “goede” database bestaat uit een viertal stappen die doorlopen
moeten worden:




Scope
Logisch Ontwerp
Technische Optimalisatie
Implementatie
We zullen nu per onderdeel bespreken wat er bij komt kijken.
Hoofdstuk: Het ontwerp proces van een database.
De database heeft 'verborgen' informatie, bijvoorbeeld door de volgorde van rijen in een
tabel.
4
5
DATABASE ontwerpen
1 januari 2011
Scope
Eerst bepalen we de scope



Wat komt wel en wat komt niet in de database?
Alléén wat bekend en goed te beheren is. Vermijd foute en misschien verouderde info. Maak
bijvoorbeeld een interface met een ander systeem, waar die gegevens wel binnen de scope
vallen.
Hou scope zo klein mogelijk. Dat is lastig, want gebruikers nemen liever het zekere voor het
onzekere. Ze willen alles, om maar niks te missen. Scopeloze projecten hebben een grote
kans op mislukken, sterven in schoonheid.
Logisch Ontwerp
Het doel van een logisch database-ontwerp is een ontwerp waarbij voor elke tabel de wet van Codd
geldt:
De waardes in een row zijn afhankelijk van
de sleutel,
de hele sleutel
en niets dan de sleutel.
Zo waarlijk helpe mij Codd.
Door het toepassen van de normalisatie stappen: het Normaliseren komen we altijd tot een logisch
ontwerp.
De normalisatiestappen bestaan uit de volgende stappen:
o
een papieren administratie,
o
bestaande schermen,
o
of een gewenst schermontwerp.
2. Zet elk data-element op een geel plakbriefje. Plak ze allemaal naast elkaar.
3. Hou parameters (vluchtige informatie) en afgeleide informatie apart. Laat ze voorlopig
buiten beschouwing. Later, tijdens de technische optimalisatie, heeft u ze weer nodig.
Hoofdstuk: Het ontwerp proces van een database.
0-de normaal vorm : Onderken alle data-elementen (scope)
1. Inventariseer data-elementen, bijvoorbeeld door waardes te highlighten op bronnen zoals:
4
6
DATABASE ontwerpen
1 januari 2011
Pas op
Vermijd dubbele gegevens.
Hou de definitie van elk data-element éénduidig. Vermijd
ingewikkelde constructies zoals:
Indien in element abc de code 123 staat, dan heeft data-element def een
ghi en anders een jkl of is leeg, hetgeen betekent...
1-de normaal vorm: Zoek de sleutel waarmee alle data op te zoeken is
Zoek een overkoepelende sleutel (key).



Wat is die sleutel? Welke data-elementen maken elke rij (row) uniek? Welke waarden moet
je weten om voor alle andere data-elementen één en slechts één waarde te kunnen vinden?
Kies een key waarvan de waarde nooit verandert. (Of maak een key zelf)
Hou de key zo klein mogelijk. Maak de key niet groter dan nodig.
Hang de key data-elementen voorop. Zet een streep onder die plakbriefjes.
Wie het sofi# en de krantnaam weet kan de rest opzoeken.
Je hebt nu een heel pril database-ontwerp, met één tabel, waarbij elke waarde afhankelijk is van de
sleutel.
2-de normaal vorm: Maak alle data afhankelijk van de gehele sleutel.
Verwijder part key dependencies
Zijn er data-elementen die afhankelijk zijn van een deel van de eerste normaalvorm sleutel?
1. Maak hiervoor een nieuwe tabel, boven de oude. Teken een verbindingslijn tussen de oude
en nieuwe tabel, een many to one relatie.
2. Merk de relatie aan de many kant met een rode hoofdletter I van Identificerend.
3. De nieuwe tabel krijgt een kleinere sleutel. Maak nieuwe plakbriefjes voor de nieuwe key.
Laat de key in de oude tabel hangen.
4. Verhuis de afhankelijke data naar de nieuwe tabel.
5. Herhaal deze stappen voor overige part-key dependencies.
Hoofdstuk: Het ontwerp proces van een database.

4
7
DATABASE ontwerpen
1 januari 2011
Koppeltabel
Noot: In de oude tabel blijft géén data over. Toch heeft de combinatie van keys betekenis. Zo'n
koppeltabel maakt een many to many relatie mogelijk:
Één lezer kan meerdere abonnementen hebben op diverse kranten.
Op één krant zijn meerdere lezers geabonneerd.
Dit is nu al een redelijk database ontwerp, met drie tabellen en twee one-to-many
relaties. Met behulp van de Keys kun je gerelateerde informatie in andere tabellen
opzoeken.
1. In abonnement staat het sofi#.
2. Met het sofi# kun je in "Lezer" de juiste rij vinden.
3. In de rij van Lezer staat o.a. het huisadres en huisnummer.
3-de normaal vorm:
Maak alle data afhankelijk van niets dan de sleutel.
Verwijder non-key dependencies
In het voorbeeld zijn Straat en Plaats transitief afhankelijk van Postcode. Bij een gegeven postcode
hoort maar één straat en plaats.
Maak een nieuwe tabel voor deze afhankelijkheid.
1. Kopieer de nieuwe Key naar de nieuwe
tabel.
Hoofdstuk: Het ontwerp proces van een database.
Zijn er transitieve afhankelijkheden in de data te vinden, los van de key? Is het ene data-element op
te zoeken als je een ander weet? Dit is vaak de lastigste stap van normaliseren.
4
8
DATABASE ontwerpen
1 januari 2011
2. Markeer de key in de oude tabel met een *, als foreign key. (Dit is handig voor technische
optimalisatie, als u indexen gaat ontwerpen.)
3. Teken een one-to-many relatie tussen de nieuwe en oude tabel.
4. Verhuis de afhankelijke data-elementen naar de nieuwe tabel.
Maak een ERD
Hoe maak je het ERD.
1. Elke rij met plakbriefjes wordt een tabel.
2. Gebruik één vel papier per tabel.
3. Voeg de kleine plakbriefjes met data-elementen toe.
4. Teken one-to-many relaties altijd van boven naar beneden.
De tabellen onderin hebben meestal data over de core business, in dit geval abonnement. Dit zijn
meestal ook de grootste tabellen, met de meeste rijen.
Hoofdstuk: Het ontwerp proces van een database.
Na normaliseren tot de derde normaal vorm is het nu heel eenvoudig om een ERD te maken (Entity
Relationship Diagram, entiteiten model, een logisch database-ontwerp, gegevensmodel, UML
conceptueel model of een Bachman diagram) met 4 tabellen:
4
9
DATABASE ontwerpen
1 januari 2011
Test het logisch model
Zijn alle functies die het systeem nodig heeft mogelijk met dit logisch model?
Doorloop alle functies, aan de hand van de bronnen voor de nulde normaalvorm, die het systeem
aan moet kunnen.
Heeft de database alle benodigde data (nulde normaalvorm check)?
Is het mogelijk om alle vereiste waarden op te zoeken (eerste normaalvorm check)?
Zijn de functies compleet? Check dit met een CRUD matrix. Is er voor elke tabel ten minste één
functie die
o een row aanmaakt (Create),
o leest (Read)
o en verwijdert (Delete)?
Een CRUD matrix
Een CRUD matrix (Create, Read, Update, Delete) is een handig hulpmiddel voor de
compleetheidscontrole. Zet Tabellen op de ene as en functies op de andere.
Oprichting
C
Prijsverhoging
U
Opheffing
D
Gebied
Abonnement
D
Nieuwbouw
C
Straatnaam wijziging
U
Sloop
D
Aanmelden
Lezer
R
C
C
Verhuizing
U
Opzeggen
D
D
R
R
Print route voor bezorger
R
R
Tip: Begin de matrix in te vullen met de C's, van create. U heeft dan al snel een goede
compleetheidscontrole. De CRUD matrix dwingt u tot nadenken. Het is een goede voorbereiding voor
de volgende stap: De technische optimalisatie.
Hoofdstuk: Het ontwerp proces van een database.
Krant
5
0
DATABASE ontwerpen
1 januari 2011
De Technische Optimalisatie:
Het doel
Het logisch ontwerp is een werkend model. Je zou het ontwerp kunnen implementeren en het werkt,
zij het mogelijk wat traag.
Technische optimalisatie (physiek database design) concentreert zich op
Efficiency
Technische optimalisatie zorgt ervoor dat de belangrijkste functies een goede performance hebben.
Eenvoud
Een volledig logisch model kan lastig zijn om mee te werken. SQL queries kunnen bijvoorbeeld
ingewikkeld worden op een volledig logische database.
Het logisch ontwerp was vooral geleid door regels. Bij technische optimalisatie komt het meer aan op
creativiteit, inventiviteit en technisch vernuft
Optimalisatiestappen
De stappen:
i.
ii.
iii.
iv.
v.
vi.
Werk met ranges
Denormaliseer
Combineer tabellen
Sla afgeleide gegevens op
Voeg indexen toe
Sorteer (clustered index)
Bekijk één tabel tegelijk, voor alle functies. Ga pas naar een bovenliggende tabel als je alle
onderliggende gehad hebt.
Maak steeds een kosten-baten analyse. Elke optimalisatie heeft z'n prijs. Elke optimalisatie moet per
saldo voordeel opleveren.
Hoofdstuk: De Technische Optimalisatie:
Bij elk van deze acties geldt:
Werk bottom-up. Begin optimalisatie bij de corebusiness, de tabellen voor de high frequency
functies. Dit zijn de tabellen onderaan het model.
5
1
DATABASE ontwerpen
1 januari 2011
Ranges
Het kan soms handig zijn om met ranges te werken.
Postcode
Straat
Plaats
2803 DA
Groenhovenweg
Gouda
2803 DB
Groenhovenweg
Gouda
2803 DC
Groenhovenweg
Gouda
...
...
...
2803 DL
Groenhovenweg
Gouda
Dit zou met een range kunnen.
Begin Postcode
Eind Postcode
Straat
Plaats
2803 DA
2803 DL
Groenhovenweg
Gouda
Pas op: Ranges maken de programmatuur ingewikkelder. Toevoegen van een extra postcode kan
ingewikkeld worden:
Hoofdstuk: De Technische Optimalisatie:
Niets doen, de postcode valt al in een bestaande range?
Een nieuwe range maken?
De begin postcode van een bestaande range verlagen?
De eind postcode van een bestaande range verhogen?
Een bestaande range splitsen en een nieuwe range tussenvoegen?
5
2
DATABASE ontwerpen
1 januari 2011
Denormaliseer
Kijk of je een dure join in een high frequency functie kunt vermijden door
kolommen uit een bovenliggende tabel te de-normaliseren, kopiëren naar een
lagere tabel.
Bijvoorbeeld: Kopieer straatnaam & plaats naar lezer.
Nadeel van denormaliseren: Als de gegevens in de one tabel wijzigen moet alle
kopieën in de many ook bijgewerkt worden.
Voorbeeld: Als de straatnaam wijzigt moeten alle kopieën van de lezers in die straat ook bijgewerkt
worden.
Denormaliseer vooral data die nauwelijks wijzigt, zoals straatnaam. Een straat wijzigt zeer
zelden van naam.
Kijk vooral naar high frequency functies die veel gegevens lezen uit diverse tabellen. (Een lijst
van abonnementen, inclusief adres gegevens bijvoorbeeld).
Pas op voor schijnoptimalisatie. Als je 9 elementen denormaliseert maar één essentiële niet,
blijft de join nodig.
Denormaliseer niet als:
het per saldo een nadeel oplevert
de software onacceptabel complex zou worden.
Combineer tabellen
Het kan zijn dat na denormalisatie een tabel geen
bestaansrecht meer heeft.
Check of de tabel echt weg kan. Pas op: De bovenste tabel, 'Gebied' kan nog wel bestaansrecht
hebben, om een aantal redenen:
1. als je een gebied kunt hebben zonder lezers (een one zonder manies). Bijvoorbeeld als input
ondersteuning, een lijst met alle gebieden tonen bij invoeren van een nieuwe lezer.
2. als een gebied nodig is ter validatie of input ondersteuning,
bijvoorbeeld een lijst met geldige postcodes.
3. Als de tabel nog nodig is voor andere functionaliteit.
Hoofdstuk: De Technische Optimalisatie:
Een voorbeeld: Alle elementen uit de tabel Gebied zijn
gekopieerd naar lezer.
5
3
DATABASE ontwerpen
1 januari 2011
Sla afgeleide gegevens op
Een mooi voorbeeld van een nuttig afgeleid gegeven is een banksaldo. Dat zou je kunnen afleiden
aan de hand van alle bij- en afschrijvingen, sinds het beginsaldo 0. Dit is echter niet praktisch.
In het logisch ontwerp, bij de UNF, Unnormalised Form heeft u de afgeleide gegevens apart
gehouden. Deze kunt u nu nalopen.
Een voorbeeld van afgeleide gegevens zijn totalen en statistieken, bijvoorbeeld het aantal lezers in
een gebied.
Pas op met opslaan van afgeleide gegevens. Als er iets aan de brongegevens
verandert, moet het afgeleide gegeven mee veranderen.
Bij dynamische brongegevens is het beter om elke keer af te leiden.
Voeg indexen toe
Nut van een index
Bij grote tabellen kan een index de performance verbeteren. Een index kan
1. rows uniek maken met enkelvoudige of compound keys
2. Zoekacties versnellen op
o data waarden of Keys
o foreign keys, die u in TNF met een sterretje gemerkt hebt. Dit soort indexen kan
vooral joins erg versnellen.
o een combinatie van data waarden, keys en foreignkeys.
3. een tabel sorteren




is gesorteerd op key waarden, (die niet hetzelfde hoeven te zijn als
die van de tabel)
is klein, heeft slechts enkele kolommen van de tabel.
verwijst voor een keywaarde naar het juiste blok binnen de tabel.
maakt het lezen van een row sneller, als je de juiste zoekargumenten weet.
Tips



Zet het meest unieke datelement voorop in de index, het element dat de meest verschillende
waardes heeft. De index kan dan sneller de juiste bladzijde vinden. Liever postcode-land dan
land-postcode.
Hou indexen klein. Liever een index op alleen postcode, dan op postcode-land. Hoe kleiner
de index des te beter de response tijd.
Voor hoogfrequente leesfuncties (duizenden keren per dag) kan het verstandig zijn om juist
een erg uitgebreide index te maken zodat het systeem voor de leesfunctie de tabel niet eens
nodig heeft.
Hoofdstuk: De Technische Optimalisatie:
Hoe werkt een index?
Een index
5
4
DATABASE ontwerpen



1 januari 2011
Voor kleine tabellen is een index nadelig. Het systeem kan dan voor iedere functie beter de
hele tabel doorlopen. Een index zou alleen maar ophouden.
Het heeft geen zin om een index te maken voor een functie die toch de hele tabel doorgaat.
Het is dan efficiënter om zonder index heel de tabel door te lopen. U spaart dan bovendien
de extra index processing uit bij toevoegen, wijzigen en verwijderen.
Maak voor elke index een kosten-baten analyse. Weegt de snellere leesfunctie op tegen de
tragere toevoegen/wijzigen/verwijderen?
Hou rekening met de frequentie en zwaarte van de functies. Een lichte leesfunctie met een
frequentie van 10.000x per dag heeft meer gewicht dan een zware verwijder functie van 1x
per maand.
Een index vertraagt toevoegen, wijzigen en verwijderen. Niet alleen de tabel,
maar ook de index moet bijgewerkt worden.
Leg dus bij voorkeur een index op waarden waarmee wel veel gezocht wordt,
maar die niet veel veranderen. Liever een index op bankrekeningnummer dan
op saldo.

Ongesorteerd, het systeem moet kris-kras de hele tabel door om de rows
te vinden die bij elkaar horen.

Gesorteerd. Het systeem hoeft maar één blok op te halen. De rows staan bij
elkaar.
Zoek in de documentatie van uw RDBMS op clustered index.
Hoofdstuk: De Technische Optimalisatie:
Sorteer
Sommige RDBMS-sen hebben de mogelijkheid om tabellen te sorteren (naast natuurlijk indexen).
5
5
DATABASE ontwerpen
1 januari 2011
Implementatie
Elk RDBMS heeft zo z'n eigen manier om een database in te richten. Op Internet staat een overvloed
aan documentatie. Zoek bijvoorbeeld met Google (www.google.com) op "Oracle create database
documentation".
In het volgende deel gaat u databases en tabellen maken in een MySQL database. Tevens leert u de
onderliggende SQL syntax, die voor bijna alle RDBMSsen hetzelfde is.
Maak tabel
SQL syntax
Met één SQL statement maakt u de tabel en alle kolommen aan:
(
)
Voorbeeld
CREATE TABLE abonnement
( krantNaam VARCHAR (35) not null, sofiNummer INT not null )
Tips
Zet velden met een vaste lengte vooraan.
Zet het grootste varchar veld achteraan.
Hou de kolommen een beetje in een logische volgorde.
Extra kolom
Voorbeeld
ALTER TABLE abonnement ADD
proefAbonnement CHAR (1) not null
Maak Index
Bij technische optimalisatie heeft u indexen ontworpen. Hier ziet u hoe u zo'n
index kunt maken, eventueel met de optie cluster om de tabel te sorteren.
Hoofdstuk: Implementatie
Een bestaande tabel kunt u makkelijk uitbreiden met een extra kolom:
5
6
DATABASE ontwerpen
1 januari 2011
SQL syntax
De opties ASC (ascending, van laag naar hoog) en DESC (descending, van hoog naar laag) geven de
sortering van de index aan. Deze optie kunt u meestal weglaten.
Hoofdstuk:
Voorbeeld
CREATE UNIQUE INDEX abonnement_ID ON abonnement ( sofiNummer, krantNaam )
Dit maakt een index met de naam "abonnement_ID" voor de tabel abonnement. De combinatie van
sofiNummer en krantNaam wordt uniek, mag niet dubbel voorkomen.
5
7
DATABASE ontwerpen
1 januari 2011
Verklarende woordenlijst
Column
In één kolom (data-element) staan gegevens van dezelfde soort, bijvoorbeeld de kolom
postcode.
Compound Key
Een compound key (samengestelde sleutel) is een key die uit meerdere kolommen bestaat, omdat
één kolom niet uniek genoeg is.
Voorbeeld van een compound key: postcode & huisnummer samen identificeren een adres.
Database
Een database is een verzameling tabellen, met onderling gerelateerde gegevens.
Foreign Key
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
High Frequency Functie
Index
Een index in een database lijkt op een index achter in een boek.
Met een zoekwoord kun je de juiste bladzijde nummers vinden van rijen, binnen
een tabel.
Join
Bij een join combineert u kolommen uit verschillende tabellen tot één geheel.
U maakt bijvoorbeeld een overzicht van lezers, met de naam van de kranten waar ze op
geabbonneerd zijn.
Hoofdstuk: Verklarende woordenlijst
Een high frequency functie is een functie die zeer vaak gebruikt wordt, honderden of zelfs duizenden
keren per dag.
5
8
DATABASE ontwerpen
1 januari 2011
Key
Een key (sleutel) bestaat uit één of meerdere kolommen.
Wanneer je de juiste key waarde weet kun je de juiste rij vinden met de gewenste informatie. De key
is als een sleutel waarmee je toegang krijgt tot de juiste rij.
Een key is uniek. Een key waarde mag niet dubbel voorkomen in een tabel. Met een key vind je dus
hooguit één rij.
Many to Many relatie
Een many to many relatie komt veel voor. Een krant heeft bijvoorbeeld vele lezers
en een lezer leest vele kranten.
Een many-to-many relatie is onduidelijk. Many-to-many relaties zijn vaak een teken dat
nog nadere analyse vereist is.
Meestal kan de relatie een stuk duidelijker, door er een koppel tabel tussen te zetten,
aan de 'boven' of aan de 'onder' kant.
Many to One relatie
Een Many to One relatie is hetzelfde als one-to-many, maar dan van een ander
gezichtspunt.
Vele lezers wonen in één gebied.
Vele abonnementen kunnen van één en dezelfde lezer zijn.
Vele abonnementen zijn op één en dezelfde krant.
One to Many relatie
De meeste relaties tussen tabellen zijn one-to-many.
In één gebied kunnen vele lezers wonen.
Één lezer kan vele abonnementen hebben.
Één krant kan vele abonnementen hebben.
One to One relatie
Een één op één relatie is uitzonderlijk in databases.
Het kàn voorkomen, maar vaak is het een teken dat het database-ontwerp nog voor verbetering
vatbaar is.
Hoofdstuk: Verklarende woordenlijst
Voorbeeld:
5
9
DATABASE ontwerpen
1 januari 2011
RDBMS
Een Relational DataBase Management Systeem is software die
U in staat stelt om een database te implementeren met tabellen, kolommen, en indexen.
De Referentiële Integriteit tussen rows van diverse tabellen garandeert.
Automatisch indexen bijwerkt.
Een SQL zoekopdracht interpreteert en informatie uit diverse tabellen kan combineren.
Redundantie
Dubbel opslaan van gegevens, 'overbodige' copieën om het systeem sneller te maken.
Welke gegevens u dubbel op kunt slaan komt aan bod in het hoofdstuk Denormaliseren.
Referential Integrity
Referentiële Integriteit zorgt ervoor dat een foreign key waarde altijd naar een
bestaande row verwijst.
Een "dode" verwijzing zoals in het plaatje kan dan niet meer voorkomen.
Er zijn een paar mogelijke maatregelen die referentiële integriteit afdwingen.
Bij het maken of wijzigen van een rij controleert het systeem of de foreign keys wel geldige
waarden hebben.
Daarnaast moet de database-ontwerper een keuze maken voor een delete:
Je mag een rij in de one tabel pas weggooien als er geen gerelateerde many rows meer zijn.
Bij het verwijderen van een row in de one tabel gooit het RDBMS automatisch alle
gerelateerde gegevens in de many tabel weg. Dit heet een cascaded delete.
Row
Een rij (= tuple, entry) zijn gegevens die bij elkaar horen, bijvoorbeeld de gegevens van één
abonnement.
In één rij kunnen géén lijstjes staan.
Hoofdstuk: Verklarende woordenlijst
Bij het verwijderen van de laatste 'many' gooit het RDBMS automatisch de gerelateerde 'one' row
weg.
6
0
DATABASE ontwerpen
1 januari 2011
Tabel
Website
Bedrijf
www.roc-teraa.nl
ROC ter AA Helmond
www.willemwever.com Willem Wever
www.bol.com
Bol.com
Een tabel is een matrix met gegevens. Een tabel in een database lijkt op een eenvoudige
spreadsheet.
Hoofdstuk: Verklarende woordenlijst
Bovenstaand voorbeeldje heeft 3 rijen (rows) en 2 kolommen (columns).
6
1
DATABASE ontwerpen
1 januari 2011
Vragen deel 1b
Op de volgende bladzijden vindt U een aantal orderformulieren, inschrijfformulieren.
De opgave is om deze formulieren om te zetten in een database in de derde normaalvorm.
1.
2.
3.
4.
5.
6.
Bepaal eerst de scope (Het bereik) van te maken database
Maak de “nulde” normaal vorm
Zet dan het geheel in de eerste normaal vorm
Daarna in de tweede normaal vorm
En als laatste in de derde normaal vorm.
Maak het ERD diagram voor de database
Zorg voor een duidelijke uitwerking. Geef aan welke stappen je hebt gebruikt om van de ene in
de andere normaal vorm te komen. Doe het ook als het veel werk is.
Vaak werkt het op papier of met papierstickers makkelijker als op een computer.
Hoofdstuk: Vragen deel 1b
Lever de opdrachten in zoals de docent het heeft aangegeven.
62
DATABASE ontwerpen
1 januari 2011
Hoofdstuk: Vragen deel 1b
Opgave 1-1.
63
DATABASE ontwerpen
1 januari 2011
Hoofdstuk: Vragen deel 1b
Opgave 1-2.
64
DATABASE ontwerpen
1 januari 2011
Hoofdstuk: Vragen deel 1b
Opgave 1-3.
65
DATABASE ontwerpen
1 januari 2011
Opgave 2-1
Hoofdstuk: Vragen deel 1b
Let op! Twee pagina’s
66
DATABASE ontwerpen
1 januari 2011
Vervolg opgave 2-1
Opgave 2-2
Op het volgende webadres vind je een inschrijf formulier gebruik dat voor je opgave moet
gebruiken.
Hoofdstuk:
http://www.skon.nl/inschrijven/inschrijfformulier
67