In het hoofdstuk Data Flow heb je geleerd om eisen aan
een werkplan om te zetten in een data flow diagram. Je hebt dan een overzicht gemaakt
van alle gegevens die voor processen nodig zijn en welke informatie moet worden geleverd.
In dit hoofdstuk gaan je leren hoe je de gegevens organiseert in een datamodel.
Garagebedrijf Nieuwenhuis zal daarbij onze kapstok zijn. Hoe slaan we al die gegevens over klanten, bestellingen,
medewerkers, producten op een nette manier op?
Zo als je straks zult zien wordt het proces van data modelleren ook ondersteund door een grafische techniek, het entiteit relatie diagram.
Het datamodel vormt de basis van het maken van de database.
Het is van groot belang eerst een volledig en efficiënt model
te maken voordat de database wordt gemaakt.
In heel veel gevallen kunnen verzamelde gegevens worden weergegeven in lijsten, b.v. een lijst met gegevens van
personen (leerlingen, docenten, personeel, leden van de vereniging, etc...), een lijst van producten in een webwinkel
(deurknoppen, telefoons, reizen, tatoo's), een lijst van bestelde producten door een klant, een lijst van metingen in een onderzoek, etc... Kortom waarvan kan je eigenlijk geen lijstjes maken? Omdat lijsten zo veel vaak voorkomen is
het niet verwonderlijk dat in de informatie technologie er speciale technieken voor de opslag van dit soort
gegevens zijn ontwikkeld. De verzamelnaam voor deze technieken is database techniek.
Database techniek
Database techniek is dus de techniek die zich bezighoudt met de opslag van gegevens die als lijsten kunnen
worden weergegeven. Zoals we bij "Big Data" al hebben gezien kan de hoeveelheid gegevens enorm zijn. Twee zaken zijn dan ook erg belangrijk:
De gegevens moeten efficiënt worden opgeslagen.
De gegevens moeten snel kunnen worden verwerkt.
Deze twee punten lijken hetzelfde te beogen. Efficiënter werken betekent toch ook sneller werken, niet waar?
Niet waar dus. Beschouw namelijk de volgende analogie: De monteur van de garage moet een reparatie aan een auto
verrichten. Hij heeft twee opties. 1) Hij kan de reparatie snel doen zodat de klant over een uur de auto weer
tot zijn beschikking heeft, maar hij weet ook dat dan de kwaliteit niet zo goed is en het probleem waarschijnlijk
op korte termijn weer terugkomt. 2) Hij kan wat grondiger te werk gaan, een onderdeel helemaal uit elkaar halen
er nieuwe onderdelen in plaatsen. Een klus waar hij toch wel wat meer tijd voor kwijt is, maar waarvan hij weet
dat het probleem zich, tot dat de auto door ouderdom op de schroothoop beland, niet meer zal voordoen.
Kortom snelheid en efficiëntie botsen hier. Soortgelijke problemen doen zich ook voor bij database techniek.
Voor bedrijven met heel veel gegevens (e.g. Google, Facebook, Apple. CERN, NASA) zijn beide punten heel
belangrijk. Met de huidige snelheid van computers is het tweede punt voor de meeste kleine- tot middelgrote
bedrijven niet zo belangrijk. De essentie ligt dan vooral bij het efficiënt opslaan van gegevens.
In dit hoofdstuk zetten we dit meest voorkomende punt dan ook centraal.
Efficiëntie
Efficiëntie van de opslag van data is in de meeste gevallen dus het belangrijkst. Nu is efficiëntie weer een heel brede
term, die nadere specificatie behoeft. Omdat er steeds grotere harde schijven komen is de opslagcapaciteit voor de
meeste bedrijven enorm groot. Gegevens over klanten, producten etc. zijn voornamelijk tekst en van tekst kan verschrikkelijk
veel worden opgeslagen op een schijf van 1 Terrabyte. Beeldmateriaal vergt veel meer opslagruimte, evenals de opslag
van bijvoorbeeld de metingen in een experiment bij de deeltjesversneller in het CERN. In deze laatste twee voorbeelden is het van belang de opgeslagen gegevens in omvang zo klein mogelijk te houden. Dat is één vorm van efficiëntie.
De vorm van efficiëntie waar wij ons hier verder mee bezig houden is een goed ontwerp van de dataopslag. Daar gelden de
volgende principes voor:
Het eerste principe is dat meervoudige opslag van dezelfde data (= redundante opslag) fout is,
niet alleen omdat het meer ruimte vergt, maar belangrijker is dat het de kans vergroot op fouten en inconsistenties
(= niet met elkaar overeenkomend). Een fout die gemaakt kan worden is dat niet alle gegevens worden verwijderd. Wordt
als fout niet op alle plaatsen een wijziging doorgevoerd, dan kan dat leiden tot de inconsistentie dat b.v. persoon A
volgens de dataopslag op twee verschillende adressen tegelijk woont.
Het tweede principe is dat van de volledigheid en de correctheid van de gegevens. Dit is natuurlijk noodzakelijk als
er rapporten moeten worden geleverd die aan de eisen van informatie voldoen.
De modellering die we hieronder aanbieden is die voor relationele databases, voor andere databases verschilt dit echter niet heel
veel.
In het proces van de analyse om tot een logisch model te komen zijn er twee soorten consultanten nodig:
De informatie-analist: Deze houdt zich vooral bezig met het ontwerpen van
de database.
De proces-analist Deze houdt zich vooral bezig houdt met welke processen
er in het bedrijf spelen en wat er op de verschillende gebruikersschermen
zichtbaar moet worden.
Bij kleine projecten is er één consultant die beide doet.
Elementen in een entiteit relatie diagram (ERD).
Relationeel model
In het modelleren van een relationele database moeten een aantal stappen worden gedaan. Je weet nog niet
wat een relationeel model is, maar daar kom je straks achter. We maken de stappen op basis van de casus "Garagebedrijf Nieuwenhuis",
die we al bij het hoofdstuk Data Flow zijn tegen gekomen.
Bepaal het doel van de database
Het is een goed idee om het doel van de database op papier te zetten.
Hoe moet de database worden gebruikt,
en wie gebruikt de database. Voor een zzp'er is de database klein en zal je
iets schrijven als:
De klantendatabase bevat een lijst van gegevens van klanten, met het doel om rapportages en mailings te
produceren. De zzp'er is de enige gebruiker.
Als de database ingewikkelder is, wat meestal al het geval is bij een bedrijf met meerdere werknemers,
dan zal de beschrijving meer zijn dan deze ene zin. De beschrijving zal makkelijk meer dan één paragraaf
gaan worden en moet in ieder geval bevatten wie, wat, en hoe de database wordt gebruikt.
Dit is eigenlijk een missie die het hele ontwerpproces centraal zal staan.
Dit deel van het werk wordt meestal gedaan door een proces-analist.
Voor garagebedrijf Nieuwenhuis hebben we deze stap al gedaan in het hoofdstuk Data Flow
Het vinden en organiseren van de benodigde gegevens
Wanneer je de vereiste gegevens zoekt en ordent, begin je met de bestaande gegevens. Het is bijvoorbeeld
mogelijk dat de inkooporders worden bijgehouden in een grootboek of klantgegevens worden bijgehouden op
papieren formulieren in een archiefkast. Verzamel die documenten en noteer elk type informatie die zij
bevatten (bijvoorbeeld elk vakje dat men moet invullen op een formulier). Als er geen bestaande
formulieren zijn, doe dan alsof er een formulier moet worden
ontworpen waarop de klantgegevens worden ingevuld. Welke gegevens moet men op dat formulier zetten?
Welke invoervakken zijn er nodig? Bepaal en noteer elk van deze items.
Wanneer deze lijst wordt opgesteld, hoeft deze niet direct perfect te zijn. Noteer in plaats daarvan
elk item dat het
ontwikkelteam te binnen schiet. Als iemand anders de database gebruikt, vraag die persoon dan eveneens
om ideeën. Je kunt de lijst later verder bijschaven.
Kijk vervolgens welke soorten rapporten of mailings er gemaakt moeten worden op basis van de database.
Bijvoorbeeld een rapport over de productverkoop per regio of een voorraadoverzicht waarin de voorraad
van elk product wordt aangegeven. Er moeten misschien ook standaardbrieven voor klanten worden
gegenereerd waarin een verkoopevenement wordt aangekondigd of een aanbieding wordt gedaan.
Ontwerp het rapport in gedachten en stel je voor hoe dit eruit zal zien.
Bedenk welke gegevens in het rapport moeten worden opgenomen. Noteer elk item.
Doe hetzelfde voor de standaardbrief en voor elk ander rapport dat waarschijnlijk wordt gemaakt.
Een belangrijk punt dat je hierbij moet onthouden, is dat elk stukje informatie moet worden opgesplitst
in de kleinste nuttige delen.
Als in het geval van een naam de achternaam afzonderlijk moet worden gebruikt,
moet je de naam in drieën delen: Voornaam, Tussenvoegsel en Achternaam.
Als men een rapport bijvoorbeeld wil sorteren op
achternaam, dient men de achternaam van de klanten afzonderlijk op te slaan. In het algemeen kan het
beste een apart veld worden gebruikt voor gegevens waarop men moet kunnen sorteren,
waarin men moet kunnen zoeken,
die in berekeningen worden gebruikt of waarover moet worden gerapporteerd.
Denk na over de vragen die de database moet beantwoorden. Bijvoorbeeld: hoeveel exemplaren van een
bepaald product zijn de afgelopen maand verkocht? Waar wonen de beste klanten van het bedrijf?
Wie is de leverancier van uw bestverkochte product? Door vooruit te lopen op dergelijke vragen,
kunt je beter bepalen welke aanvullende items je moet vastleggen.
Dit deel van het werk wordt meestal ook gedaan door een proces-analist.
Het data flow diagram uit hoofdstuk Data Flow voor garagebedrijf Nieuwenhuis is het resultaat van deze stap.
De gegevens opsplitsen naar entiteiten (objecten)
Gegevens horen bij objecten (dingen). Bijvoorbeeld er is de persoon (=object) met de naam Jan Drop(gegeven) die
woont in de "Zoutweg 7a"(gegeven) in de stad Snoepwinkel(gegeven) die is geboren op 01-01-2000gegeven),
of er is het product(=object) met de naam Kidcarrier(gegeven) van de categorie fietsen(gegeven), gemaakt door
fabrikant Batelle(gegeven) met een inkoopprijs van €995(gegeven). Waarom is in deze voorbeelden
b.v. inkoopprijs, fabrikant of categorie zelf geen object? De vraag hierbij is dan: moeten er van dit gegeven nog andere gegevens
worden vastgelegd? Zo ja dan kan ook het geven verwijzen naar een ander object. Bijvoorbeeld er is de
fabrikant (=object) met naam Batelle(gegeven) gevestigd te Rotterdam, Boergoense Vliet 13(gegeven), etc...
waarvan de contactgegevens in de database moeten worden opgeslagen. Objecten in het model zijn in ieder geval dingen waarvan
meer dan één eigenschap moet worden vastgelegd.
Het is niet altijd direct duidelijk wat de benodigde objecten zijn. Ook hier zal meerdere malen de analyse moeten
worden herhaald voordat de juiste objecten zijn bepaald.
In deze stap willen we dus alle noodzakelijke objecten waarvan we de gegevens willen vastleggen bepalen.
In de database taal heet de representatie van een object uit de echte wereld een entiteit.
B.v. Persoon is de naam van de entiteit die we gaan gebruiken voor het vastleggen van objecten
van het type persoon en kunnen we Product
als naam van de entiteit die we gaan gebruiken voor het vastleggen van objecten van het type product en Fabrikant
voor het vastleggen van de producenten van de producten.
De gegevens bij een object zijn specifieke waarden van eigenschappen van alle objecten van het zelfde type.
In database taal heet zo'n eigenschap een attribuut. B.v. "Jos Drop" is de naam van het persoon "Jos Drop"
en de "naam" van een persoon is een eigenschap die we voor alle personen die we in de database willen opnemen
graag willen weten en is "naam" een attribuut dat hoort bij de entiteit "Persoon". In het voorbeeld is
"naam" ook een attribuut bij de entiteit "Product".
Aandachtspunten
Een entiteit krijgt een enkelvoudige naam: b.v. "Persoon" en niet "personen".
Er zijn in één database geen entiteiten met overeenkomstige naam.
Er zijn geen attributen voor één entiteit met overeenkomstige naam.
Bij een andere entiteit kan dezelfde naam van een attribuut wel voorkomen.
Dit deel van het werk wordt meestal gedaan door de proces-analist en informatie-analist samen.
Een eerste aanzet tot entiteiten voor het garagebedrijf is gegeven in het onderstaande diagram (in blauw de entiteit en daaronder voorlopige attributen):
Er is een serieus probleem in dit diagram met de entiteit `order`. In één order kunnen meerdere producten aanwezig zijn en
door meerdere medewerkers worden afgehandeld. Ook moeten we nog over denken hoe we objecten zo kunnen opslaan dat
we ze ook eenvoudig weer uit de database terug te halen zijn. Bijvoorbeeld er kunnen best meerdere klanten zijn
die "Jan Smit" heten. Hoe weet je dat je met de juiste "Jan Smit" bezig bent en dit op een efficiënte manier
kan bepalen.
Het eerste probleem behandelen we in "De relaties bepalen tussen de entiteiten". Het tweede probleem in "Primaire sleutels opgeven".
De relaties bepalen tussen de entiteiten
Het bepalen van de relaties wordt gedaan door de proces-analist en informatie-analist samen.
Tussen objecten zijn er meestal relaties, b.v. "Een klant plaatst een order", "Een order bevat producten",
"Een medewerker behandelt een order". In de database moeten deze relaties terug te vinden zijn. In "Primaire sleutels opgeven"
behandelen we de wijze waarop relaties worden vastgelegd. Hier gaan we op de relaties zelf en introduceren we een
grafische weergave van de relaties tussen de entiteiten. Het zogenoemde entiteit relatie diagram (ERD).
In de legenda rechts staan alle componenten van een ERD. Het ERD bestaat uit de namen van entiteiten in een blokken
verbonden door lijnen met aan het blok verschillende tekens.
We geven per relatie type een voorbeeld.
Spreek uit "relatie type één op één". Een natuurlijk voorbeeld voor een één op één relatie type is nog niet
zo makkelijk type geven. Dit type wordt voornamelijk voor optimalisatie doeleinden gebruikt.
Bijvoorbeeld één student heeft een ov-studenten kaart. De student heeft specifieke persoonlijke
eigenschappen (naam, adres, geboortedatum, studie) evenals
de ov-studentenkaart (saldo, naam, verlooptijd). Hoewel de eigenschappen van de ov-kaart ook een eigenschap van de
leerling zou kunnen zijn kan er toch voor worden gekozen voor de verschillende entiteiten om een duidelijk onderscheid
te maken tussen student en kaart.
De relatie heeft ook gelijk gevolgen voor het maken van een applicatie rond een database. Als in de applicatie
de gegevens van een student worden ingevoerd moet er ook gelijk gegevens van de ov-studentenkaart van deze
student worden ingevoerd.
Spreek uit "relatie type één op nul of één". Als in het voorbeeld hierboven een student geen ov-studentenkaart
hoeft te hebben dan is er sprake van een één op nul of één relatie.
Als nu in de applicatie
de gegevens van een student worden ingevoerd hoeven er niet gelijk gegevens van een mogelijke
ov-studentenkaart van deze student worden ingevoerd.
Spreek uit "relatie type één op nul veel". We houden de voorbeelden even bij studenten.
Een student behaalt meerdere resultaten. Een resultaat heeft bijvoorbeeld als eigenschappen: vak, datum, cijfer, toetsnummer.
Een resultaat echter kan slechts voor één student van toepassing zijn.
Als nu in de applicatie
de gegevens van een student worden ingevoerd hoeft er niet gelijk een resultaat worden ingevoerd.
Spreek uit "relatie type één op veel". We houden de voorbeelden weer bij studenten.
Een student wordt pas bij een opleidingsinstituut ingeschreven als hij een studie gaat volgen.
Een student kan ook meerdere studies volgen.
Als nu in de applicatie de gegevens van een student worden ingevoerd moet er
gelijk vermeld worden welke studie de student gaat volgen.
Spreek uit "relatie nul veel op nul of één. Een student kan regelmatig worden gevraagd een enquête in te vullen.
De student kan dat echter ook anoniem doen zodat er dan geen koppeling is naar hem is.
Als nu in de applicatie de gegevens van een student worden ingevoerd hoeft er niet direct een enquête te worden afgenomen.
Entiteit relatie diagram voor garagebedrijf Nieuwenhuis. De veel op veel relatie tussen order en product en
en de veel op veel relatie tussen order en medewerker vormen een probleem voor het
maken van een rationele database. Dit probleem wordt in de volgende paragraaf opgelost.
Veel op veel relaties opdelen
Kijk eens naar de veel op veel relatie tussen de tabel Product en de tabel Order.
Eén order kan meer dan één product bevatten. Anderzijds kan één product voorkomen in veel orders.
De onderwerpen van de twee tabellen, order en product, hebben een veel-op-veel-relatie. Dit leidt echter tot een probleem. Om dit probleem te begrijpen, kijken we wat er zou gebeuren als we de relatie tussen de twee tabellen proberen te maken door bijvoorbeeld het veld product_id toe te voegen aan de tabel Order. Als we meer dan één product per order willen kunnen invoeren, moet de tabel Order meer dan één record per order bevatten. We zouden de ordergegevens moeten herhalen voor elke rij die betrekking heeft op één order, wat resulteert in een inefficiënt ontwerp, dat tot onjuiste gegevens kan leiden. We ondervinden hetzelfde probleem als we het veld Ordernummer in de tabel Product plaatsten: de tabel Product zou dan meer dan één record voor elk product bevatten. Hoe lossen we dit probleem op?
Het antwoord hierop is een verbindingsentiteit te maken, die één veel-op-veel-relatie splitst in twee één-op-veel-relaties. We voegen een uniek veld (zie primaire sleutels toevoegen) uit elk van de twee entiteiten toe aan de derde entiteit. Op die manier wordt in de tabel van de verbindingsentiteit elke instantie van de relatie vastgelegd en hoeven we nergens extra informatie op te slaan.
Entiteit relatie diagram voor garagebedrijf Nieuwenhuis. De veel op veel relatie tussen order en product en
en de veel op veel relatie tussen order en medewerker zijn om implementatie redenen opgesplitst in veel op
één relaties door tussen deze entiteiten een extra entiteit te plaatsen.
Primaire sleutels opgeven
In het opsplitsen van de veel op veel relaties hierboven hebben we gemeld dat we een uniek veld uit de tabellen
gingen toe voegen aan de verbindingstabel. Als we relaties tussen tabellen willen maken is het van belang dat
dat we dat op een unieke en efficiënte manier doen. Neem bijvoorbeeld de student "Jan Janssen" die we willen koppelen aan
de cursus "Ethiek in het dieronderzoek". We moeten dan in een tabel cursus aan een record met de cursusnaam
"Ethiek in het dieronderzoek" een attribuut toevoegen die naar "Jan Janssen" verwijst. We zouden de naam kunnen nemen,
maar wat als er twee verschillende studenten "Jan Janssen" heten, dan is de link dus niet uniek en zou er nog meer
informatie aan het record moeten worden toegevoegd. Dit is verre van efficiënt en bovendien foutgevoelig. Daarom
wordt bijna altijd één unieke attribuut aan een entiteit toegevoegd, dit noemen we een identificerende attribuut
, meestal bekend onder de afkorting id. In de database taal wordt dit unieke attribuut de primaire sleutel
genoemd. Beide "Jan Janssen"'s krijgen nu hun eigen id. Dit id wordt dan opgenomen als waarde van een
attribuut in een entiteit die een relatie heeft met de entiteit student.
Een entiteit kan echter ook meerdere primaire sleutels als attribuut bevatten. In een verbindingsentiteit
wordt namelijk de eenduidigheid van een record afgedwongen door een combinatie van de primaire sleutels van de omringende entiteiten.
Bijvoorbeeld in de entiteit "product in order" kunnen we de order_id (primaire sleutel order entiteit)
en de product_id (primaire sleutel product entiteit) als attributen toevoegen. Als we een record aan de
verbindingstabel toevoegen dan leggen de waarden van deze twee attributen de uniciteit volledig vast. Het kan niet zijn
dat een record met precies de zelfde twee waarden meerdere keren voorkomt. Verder is er niets nodig om de uniciteit
te bepalen. Natuurlijk kan het product_id wel in meerdere records voorkomen, maar alleen met een andere order_id.
Een complete definitie van de entiteiten voor het garagebedrijf is gegeven in het onderstaande diagram
(in geel de primaire sleutels die bepalen dat een ingevoerd gegeven uniek is in de database):
De klant, de medewerker, een order en een product hebben nu een eigen uniek nummer (id)
order medewerker en order product hebben een combinatie van primaire sleutels die er voor zorgt
dat een bepaald product uniek aan een bepaalde order wordt gekoppeld. In de database mag niet twee keer
dezelfde combinatie voorkomen.
De normalisatieregels toepassen
Als volgende stap in het ontwerp kun je de normalisatieregels voor gegevens
(soms eenvoudig normalisatieregels genoemd) toepassen. Je gebruikt deze regels om te
kijken of de structuur van de tabellen correct is.
Het toepassen van deze regels op het databaseontwerp wordt normaliseren of
normalisatie genoemd.
Normalisatie is het meest zinvol als alle gegevensitems zijn vertegenwoordigd en de
voorlopige versie van het ontwerp is voltooid. Deze regels helpt je ervoor te zorgen
dat je de gegevensitems in de juiste tabellen hebt gesplitst.
Normalisatie kan er echter niet voor zorgen dat je over alle juiste gegevensitems
beschikt. Je past de regels na elkaar toe, waarbij je er in elke stap voor zorgt
dat het ontwerp een van de zogeheten 'normaalvormen' bereikt.
Er bestaan vijf algemeen geaccepteerde normaalvormen:
de eerste normaalvorm tot en met de vijfde normaalvorm.
In deze cursus wordt nader ingegaan op de eerst drie vormen,
omdat alleen deze drie zijn vereist voor de meeste databaseontwerpen.
Eerste normaalvorm
De eerste normaalvorm schrijft voor dat er op elk snijpunt van een rij en
een kolom in de tabel slechts één waarde bestaat en nooit een lijst met waarden.
Je kunt bijvoorbeeld geen veld genaamd Prijs maken waarin je meer dan één prijs
plaatst. Als je elk snijpunt van een rij en een kolom als een cel beschouwt,
mag elke cel slechts één waarde bevatten.
Tweede normaalvorm
De tweede normaalvorm vereist dat elke niet-sleutelkolom volledig afhankelijk
is van de gehele primaire sleutel, en niet slechts van een deel van de sleutel.
Deze regel is van toepassing wanneer een primaire sleutel uit meer dan één
kolom bestaat. Stel, je hebt een tabel met de volgende kolommen waarin
Ordernummer en Product-id de primaire sleutel vormen:
Ordernummer (primaire sleutel)
Product-id (primaire sleutel)
Productnaam
Dit ontwerp schendt de tweede normaalvorm omdat Productnaam afhankelijk
is van Product-id, maar niet van Ordernummer, zodat Productnaam niet
afhankelijk is van de volledige primaire sleutel.
Je moet Productnaam uit de tabel verwijderen. Deze kolom hoort namelijk in
een andere tabel (Producten).
Derde normaalvorm
De derde normaalvorm vereist dat niet alleen elke niet-sleutelkolom afhankelijk
is van de gehele primaire sleutel, maar ook dat niet-sleutelkolommen
onafhankelijk zijn van elkaar. Een andere manier om dit te formuleren luidt
dat elke niet-sleutelkolom afhankelijk moet zijn van de primaire sleutel en
alleen van de primaire sleutel. Stel, je hebt een tabel met de volgende kolommen:
Product-id (primaire sleutel)
Naam
Adviesprijs
Korting
Stel dat Korting afhangt van Adviesprijs.
In dat geval schendt deze tabel de derde normaalvorm omdat een
niet-sleutelkolom, Korting, afhangt van een andere niet-sleutelkolom,
Adviesprijs. Onafhankelijkheid van kolommen betekent dat je elke
niet-sleutelkolom moet kunnen wijzigen zonder dat dit van
invloed is op andere kolommen.
Als je een waarde in het veld Adviesprijs wijzigt, zou Korting echter eveneens
veranderen, waardoor deze regel wordt geschonden.
In dit geval moet je Korting verplaatsen naar een andere tabel die
is gekoppeld aan Adviesprijs.
