Relationele databases: regels

Leereenheid 3 Relationele databases: regels

Leereenheid 3

Relationele databases: regels

INTRODUCTIE

De vorige leereenheid ging over goede en slechte relationele structuren. Een goede structuur heeft als kenmerken: – geen herhalende groepen – geen ongecontroleerde redundantie – standaardisatie van gegevens wordt afgedwongen waar dat gewenst is – elke ‘soort ding’ in de bijbehorende werkelijkheid heeft zijn eigen tabel. In deze leereenheid gaan we het hebben over de regels die voor die structuren gelden: – regels die dingen verbieden of verplichten: beperkingsregels – regels die zorgen dat acties worden ondernomen of juist worden tegengehouden: gedragsregels. Beperkingsregels zijn voorlopig het belangrijkst; er zijn verschillende typen, die in deze leereenheid worden behandeld. Van de gedragsregels komt in deze leereenheid één belangrijk type aan de orde. LEERDOELEN

Na het bestuderen van deze leereenheid wordt verwacht dat u – binnen een relationele structuur de volgende typen beperkingsregel kunt interpreteren: het verplicht zijn van een kolom, uniciteit van een kolom of kolomcombinatie, primaire sleutel, alternatieve sleutel, kandidaatsleutel, verwijssleutel en referentiële-integriteitsregel – weet hoe deze regels worden genoteerd in een strokendiagram – inziet hoe uniciteitsregels zinvol kunnen worden gecombineerd – voorbeelden kunt geven van structuren met recursieve verwijzingen – voorbeelden kunt geven van structuren met niet-sleutelverwijzingen – verwijzingen tussen tabellen kunt uitdrukken in termen van multipliciteit en deze kunt noteren in een multipliciteitendiagram – voorbeelden kunt noemen van bijzondere beperkingsregels – kunt aangeven waarom een tabel soms een kunstmatige primaire sleutel krijgt – kunt aangeven waarom er gedragsregels moeten bestaan van het type ‘refererende-actieregel’ – het effect van elk type refererende-actieregel kunt omschrijven en illustreren aan een voorbeeld – voorbeelden kunt noemen van bijzondere gedragsregels – via een voorbeeld kunt illustreren wat de betekenis en het belang is van het begrip transactie – het belang onderkent van strakke tekenen naamgevingsconventies. De studielast van deze leereenheid bedraagt 6 uur.

89

Open Universiteit

Databases

LEERKERN

Voorbeelddatabases In de paragrafen 3.1 en 3.2 gaan we uit van de Toetjesboek-database van leereenheid 2. In de loop van de leereenheid wordt deze op enkele details aangepast. In figuur 3.1 herhalen we de voorbeeldpopulatie. Eenheid naam liter stuks deciliter gram kilogram eetlepel theelepel

Product naam ijs kiwano slagroom suiker tequila aardbeien pernod peper mango zure room banaan

Gerecht naam

energiePP bereidingstijd bereidingswijze

Coupe Kiwano Glace Terrace Mango Plus Plus

431 403 131

20 5 8

Schil ... Neem ... Snijd ...

eenheid

energiePE

liter stuks deciliter gram eetlepel gram eetlepel

1600 40 336 4 30 0.25 35

stuks deciliter stuks

80 195 40

Ingredient gerecht Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Glace Terrace Glace Terrace Glace Terrace Glace Terrace Mango Plus Plus Mango Plus Plus Mango Plus Plus

FIGUUR 3.1 1

Referentiëleintegriteitsregel

product ijs kiwano slagroom suiker tequila ijs aardbeien pernod peper mango aardbeien zure room

hoeveelheidPP 0.15 0.5 0.3 10 1 0.2 50 2 0.5 50 0.4

Voorbeeldpopulatie Toetjesboek

Beperkingsregels

De tabellen zijn onderling verbonden via verwijzingen op basis van gelijke waarden. De belangrijkste beperkingsregel heeft betrekking op die verwijzingen. Het is de regel die loze verwijzingen (verwijzingen naar niks) verbiedt. Bijvoorbeeld: een maateenheid ‘scheutje’ bij een product, terwijl die niet in de gestandaardiseerde Eenheid-tabel voorkomt. Deze beperkingsregel heet de referentiële-integriteitsregel. Het is een belangrijke regel, omdat verwijzingen als het ware het cement van de database vormen. Zonder deze verwijzingen zou de database uiteenvallen in losse tabellen die niet gecombineerd kunnen worden. Verderop wordt de referentiële-integriteitsregel uitgebreid behandeld. Maar er zijn meer soorten beperkingsregels; daar gaan we het eerst over hebben.

90


1.1

Verplichtewaarderegel

OPTIONELE EN VERPLICHTE KOLOMMEN

Elk gerecht moet een naam hebben. Dat is immers dé manier om een gerecht aan te duiden. We zeggen: voor de kolom Gerecht.naam geldt een verplichte-waarderegel. Kortweg, hij is verplicht. Vanzelfsprekend zijn ook Product.naam en Eenheid.naam verplicht. Ook de kolommen Ingredient.gerecht en Ingredient.product zijn verplicht. Laten we immers één van die twee in een rij oningevuld, dan is het onmogelijk betekenis te hechten aan de wel ingevulde gegevens in die rij. Voor alle andere kolommen geldt dat het weglaten van een waarde géén problemen geeft met de interpretatie van de overblijvende gegevens in de rij. Voor die kolommen moet beslist worden of het verplichte kolommen moeten worden of niet. Dat is een ontwerpkeuze.

Optionele kolom null

Een niet-verplichte kolom heet optioneel. Een optionele kolom mag lege cellen bevatten. De ‘inhoud’ van een lege cel heet een null. Dat lijkt wat vreemd en dat is het ook: hoe kan ‘niks’ een naam hebben? Eigenlijk is null niet meer dan een indicator, die aangeeft dat de cel leeg is. Null’s zullen ons in deze cursus nog een hoop hoofdbrekens kosten. Alleen de kolommen die absoluut moeten worden ingevuld, worden verplicht gemaakt. Zo’n kolom is bereidingswijze. Want wat moeten we met een gerecht zonder bereidingswijze? In de Toetjesboek-database zijn de meeste kolommen verplicht, alleen de volgende zijn optioneel: − Gerecht.energiePP; deze kolom is optioneel in verband met ingrediënten met onbekende hoeveelheid, maar tevens vanwege het éérst invoeren van ingrediënten en het pas daarna (automatisch) berekenen van energiePP – Ingredient.hoeveelheidPP: een ingrediënt in een gerecht mag een onbepaalde hoeveelheid per persoon hebben – Product.eenheid: niet elk ingrediënt heeft een vaste maateenheid – Product.energiePE: een product zonder maateenheid (zie vorige punt) kent ook geen energie per maateenheid. De voorbeeldpopulatie illustreert dit: in Glace Terrace zit wel peper, maar een hoeveelheid is niet gegeven. Dat zal dan wel ‘naar smaak’ zijn. En van peper is geen eenheidnaam vermeld en evenmin een energie per eenheid. In een strokenschema geven we een optionele kolom aan met o. De kolommen zonder o zijn dus verplicht, zie figuur 3.2. De gebruiker moet ervoor zorgen dat die kolommen in elke rij een waarde bevatten. Doet de gebruiker dit niet, dan zal het databasemanagementsysteem (rdbms) via de applicatie (het Toetjesboek-programma) een foutmelding geven.

91

Open Universiteit

Databases

Gerecht /energiePP° bereidingstijd bereidingswijze

naam

Eenheid naam

naam

eenheid°

Product energiePE°

Ingredient gerecht product hoeveelheidPP° FIGUUR 3.2

1.2

Uniciteitsregel

Strokendiagram voor Toetjesboek (versie 2: optionele kolommen)

UNICITEIT

Behalve dat de kolom naam in de tabel Gerecht verplicht is, moeten de waarden in die kolom ook uniek zijn: verschillende gerechten moeten verschillende namen hebben. Dit is vanzelfsprekend, maar de noodzaak wordt nog eens onderstreept door het feit dat de gerechtnamen in andere tabellen worden gebruikt om naar steeds één gerecht te verwijzen. Ook voor Product.naam geldt zo’n uniciteitsregel: verschillende producten hebben verschillende namen. Evenzo geldt in Eenheid een uniciteitsregel voor de naamkolom. Ook in de tabel Ingredient geldt een uniciteitsregel, echter niet voor één losse kolom; niet voor gerecht, omdat één gerecht meerdere producten kan bevatten; niet voor product, omdat één ingrediënt in meerdere gerechten kan voorkomen; niet voor hoeveelheid, omdat bij meerdere gerecht-product-combinaties natuurlijk best dezelfde hoeveelheid kan horen. Wel uniek zijn de combinaties (gerecht, product) in Ingredient. Zo’n combinatie bepaalt immers precies één rij in deze tabel, ofwel één ingrediëntregel.

Smalle en brede uniciteitsregel

Uniciteitsregels over één kolom heten smalle uniciteitsregels. Die over twee of meer kolommen heten brede uniciteitsregels. In een tabelschema geven we uniciteitsregels aan door middel van tweepuntige pijlen, zie figuur 3.3. Het eisen van uniciteit voor één kolom (een smalle pijl) is strenger dan het eisen van uniciteit voor een combinatie van diezelfde kolom en nog één of meer andere kolommen (een brede pijl). Bijvoorbeeld, uit de uniciteitsregel voor Gerecht.naam volgt als afgeleide regel dat ook de waardencombinaties in de kolomcombinatie (naam, energiePP) verschillend zijn. Dit is echter een zwakkere regel, want het omgekeerde geldt niet.

92


naam

Gerecht /energiePP° bereidingstijd bereidingswijze Eenheid naam

naam

Product eenheid° energiePE°

Ingredient gerecht product hoeveelheidPP° Strokendiagram voor Toetjesboek (versie 3: uniciteitsregels)

FIGUUR 3.3

Conventie

Algemeen wordt als conventie aangehouden dat alleen de meest strenge uniciteitsregels als zodanig worden benoemd en als uniciteitspijl worden getekend. In een Toetjesboek-strokendiagram zal daarom wel een uniciteitspijl voor Gerecht.naam worden getekend maar geen brede pijl bij de combinatie (naam, energiePP), ook al is die combinatie uniek (zie figuur 3.4). Tekenen we ergens toch een brede pijl, zoals bij Ingredient, dan zullen we hiermee (vanwege genoemde conventie) in het algemeen aanduiden dat geen uniciteitsregel geldt voor de afzonderlijke kolommen of voor smallere kolomcombinaties.


naam FIGUUR 3.4 1.3

Illustratieve populatie

Uniciteitsregels: alleen minimale unieke combinaties

ILLUSTRATIEVE POPULATIES

Een illustratieve populatie bij een bepaalde regel is een populatie die zo goed mogelijk illustreert wat wel en wat niet mag met betrekking tot die regel. Als voorbeeld nemen we een uniciteitsregel over twee kolommen. Een illustratieve populatie hierbij zal twee dingen illustreren: 1 de uniciteit van de waardencombinaties voor die twee kolommen 2 het feit dat voor de afzonderlijke kolommen geen uniciteit geldt. Zie figuur 3.5 voor een minimaal gekozen illustratieve populatie bij de brede uniciteitsregel voor Ingredient.

gerecht

product

Ingredient hoeveelheidPP°

Coupe Kiwano Coupe Kiwano Glace Terrace

ijs kiwano ijs

0.15 0.5 0.2

FIGUUR 3.5

93

Minimale illustratieve populatie voor brede uniciteitsregel

Open Universiteit

Databases

Illustratieve populaties spelen vaak een verhelderende ‘illustratieve’ rol bij het vaststellen van de juiste regels. Vooral bij uniciteitsregels. 1.4

Unieke identificatie van een rij

IDENTIFICATIE EN VERWIJZING

Een kolom of een kolomcombinatie die verplicht is en bovendien uniek, is geschikt om te gebruiken als unieke identificatie van een rij. Immers, in elke rij is dan de kolomwaarde (of de combinatie) ingevuld en alle waarden (of combinaties) zijn verschillend. Zodoende kunnen we de drie kolommen met een smalle uniciteitspijl in figuur 3.3 gebruiken om de rijen in respectievelijk Gerecht, Product en Eenheid uniek te identificeren. Die kolommen zijn immers ook verplicht. Evenzo is de kolomcombinatie (gerecht, product) in Ingredient geschikt om de rijen in Ingredient uniek te identificeren. In de voorbeeldpopulatie ziet u hoe een kolomwaarde van Product.naam, bijvoorbeeld ‘ijs’, in een andere tabel (Ingredient) wordt gebruikt om een verwijzing te realiseren juiste rij in Product. De waarden in Ingredient.product zijn als het ware ‘sleuteltjes’ die op een ‘slotje’ passen. De waarden in Product.naam zijn de ‘slotjes’. Deze zijn allemaal verschillend; van een ‘sleuteltje’ kunnen meerdere exemplaren bestaan. Zie figuur 3.6, waarin dit voor twee ‘slotjes’ (de waarden ‘ijs’ en ‘kiwano’ in Product.naam) is geïllustreerd.

Verwijzing

Product naam ijs kiwano slagroom suiker tequila aardbeien pernod peper mango zure room banaan

eenheid

energiePE


1600 40 336 4 30 0.25 35


80 195 40



FIGUUR 3.6

94

hoeveelheidPP

enz.

0.15 0.5 0.3 10 1 0.2 50 2 0.5 50 0.4

‘Sleuteltjes’ (verwijssleutelwaarden) die op unieke ‘slotjes’ (primaire-sleutelwaarden) passen


Logisch adres Fysiek adres

In het algemeen geldt: in een relationele database kan een kolom of kolomcombinatie die uniek identificerend is (dus met ‘slotjes’), worden gebruikt om vanuit een andere tabel verwijzingen te realiseren (via ‘sleuteltjes’). Zo’n waarde of waardencombinatie fungeert dan als een soort ‘adres’ van een rij. We spreken van logische adressen van de rijen, ter onderscheiding van de fysieke geheugenadressen. 1.5

PRIMAIRE SLEUTELS EN VERWIJSSLEUTELS

Een kolom of kolomcombinatie met ‘slotjes’ heet een primaire sleutel. Een correspondende kolom of kolomcombinatie met ‘sleuteltjes’ heet een verwijssleutel. We formuleren dat iets preciezer. Primaire sleutel

Een primaire sleutel van een tabel is een kolom of kolomcombinatie waarvoor geldt: 1 hij is verplicht 2 hij is uniek 3 in het geval van een kolomcombinatie: deze is zo ‘zuinig’ mogelijk gekozen 4 de kolomwaarden (of -waardencombinaties) zijn aangewezen als logische rij-adressen voor verwijzingen. Ter illustratie van de zuinigheidseis (3): de combinatie (naam, bereidingstijd) in Gerecht voldoet aan 1 en 2, maar niet aan 3. Dit geldt ook voor de combinatie (naam, energiePP), ook al is energiePP niet verplicht. De zuinigheidseis houdt direct verband met het zo smal mogelijk tekenen van uniciteitspijlen (zie figuur 3.4).

Verwijssleutel

Een verwijssleutel is een kolom of kolomcombinatie waarvan de waarden (of waardencombinaties) naar rijen in een andere tabel verwijzen, via de primaire sleutel daarvan. Een primaire sleutel is – ondanks de naam – het ‘slotje’. Verwijssleutels zijn de ‘sleuteltjes’ die op zo’n slotje moeten passen. Primaire sleutels geven we aan met een ‘p’ bij de uniciteitspijl. Een verwijzing tekenen we in een strokendiagram als een verticale pijl, lopend van de verwijssleutel naar de primaire sleutel, zie figuur 3.7.

p naam

Gerecht /energiePP° bereidingstijd bereidingswijze Eenheid p naam p naam


Ingredient p gerecht product hoeveelheidPP° FIGUUR 3.7

95

Strokendiagram voor Toetjesboek (versie 4: primaire sleutels)

Open Universiteit

Databases

Opmerkingen − Ook de tabel Ingredient heeft een primaire sleutel, ook al wordt daar niet naar verwezen. Het is echter denkbaar dat de database in de toekomst wordt uitgebreid met een tabel die er wel verwijzingen naar heeft. Dat zal dan met een brede verwijssleutel moeten; het sleuteltje moet immers op het slotje passen. − De primaire sleutel van Ingredient bestaat uit twee afzonderlijke verwijssleutels, één naar Gerecht en één naar Product. Hiermee wordt precies uitgedrukt dat een rij in Ingredient één gerecht koppelt aan één product. − Een brede primaire sleutel mag, vanuit conceptueel standpunt bezien, best een null bevatten, als hij maar niet alleen null’s bevat. Vrijwel elk rdbms eist echter dat primaire-sleutelkolommen geen null’s bevatten (verplichte kolommen). − Een verwijssleutel mag best optioneel zijn en is in dit opzicht vergelijkbaar met een ‘gewone’ kolomwaarde. Zie echter het vorige punt voor verwijssleutels die deel uitmaken van een brede primaire sleutel. 1.6

KANDIDAATSLEUTELS EN ALTERNATIEVE SLEUTELS

De term ‘primaire sleutel’ suggereert dat er ook zoiets mogelijk is als een ‘niet-primaire sleutel’. Dit is inderdaad juist. Met het gegeven voorbeeld is dat echter niet te illustreren, omdat elke tabel maar één kolom of kolomcombinatie heeft die aan de eisen 1, 2 en 3 voldoet. Bij gebrek aan andere kandidaten worden deze dus vanzelf aangewezen om vanuit andere tabellen naar te verwijzen (eis 4).

Kandidaatsleutel Alternatieve sleutel

In paragraaf 1.10 wordt de databasestructuur gewijzigd, zodanig dat de tabellen Gerecht en Product beide twee kandidaat-primaire-sleutels (eisen 1, 2 en 3) krijgen. Zo’n kandidaat-primaire-sleutel heet kortweg kandidaatsleutel. De gekozen kandidaten (één per tabel) worden dan primaire sleutel (eis 4); de afgevallen kandidaten (alleen eisen 1, 2 en 3) heten alternatieve sleutel. Elke tabel heeft minstens één kandidaatsleutel. Immers, de combinatie van alle kolommen is verplicht (er zijn geen lege rijen mogelijk) én uniek (omdat een tabel – als relatie – een verzameling is). Door nu zoveel mogelijk kolommen weg te laten, zodanig dat de eisen 1 en 2 nog steeds gelden, ontstaat een zo zuinig mogelijk gekozen combinatie. Sleutels en herhalende groepen In de meeste boeken over relationele databases worden herhalende groepen zo snel mogelijk ‘in de ban’ gedaan, waarna de relationele theorie, waaronder die over uniciteitsregels en sleutels, volledig wordt ontvouwd voor genormaliseerde structuren, dus zonder herhalende groepen. Echt nodig is dat niet en vaak betekent het zelfs een gemiste kans om iets goed uit te leggen. Wij hebben ons deze beperking dan ook niet opgelegd, en zullen daarvan regelmatig profijt hebben. In dit boek komt u dan ook regelmatig uniciteitsregels en sleutels tegen in tabellen mét een herhalende groep.

Naamgeving sleutelkolommen We houden er in dit boek vrij strakke naamgevingsconventies op na. Dat schept eenheid en duidelijkheid en leidt ook tot strakke SQL-code.

96


De kolomnaam van een smalle primaire sleutel luidt meestal ’nr’, ‘code’ of ‘naam’. Een verwijssleutelkolom heeft meestal dezelfde naam als de corresponderende tabel, maar dan met een kleine letter. Deze conventie voor verwijssleutels kan een misverstand opleveren: dat de verwijzing vanzelf ontstaat door de naamgeving. Dat is niet het geval! We moeten daar apart SQL-code voor schrijven, zoals we zullen zien wanneer we SQL/DDL behandelen. 1.7

REFERENTIËLE INTEGRITEITSREGEL

Elke waarde in Product.eenheid wordt geacht te verwijzen naar precies één rij in Eenheid, via de primaire sleutel van die tabel (dat is de enige kolom, in dit geval). Dan moet die waarde daar wel voorkomen natuurlijk. Bevat Product.eenheid ergens ‘theelepel’ en komt dit niet voor in Eenheid.naam, dan hebben we een probleem: een verwijzing naar niks. Dan wordt er als het ware gemeten in de niet-bestaande eenheid ‘theelepel’. De ‘referentiële-integriteitsregel’ verbiedt dergelijke loze verwijzingen.

Referentiëleintegriteitsregel

Referentiële-integriteitsregel De referentiële-integriteitsregel luidt: elke waarde van een verwijssleutel moet voorkomen als waarde van de bijbehorende primaire sleutel (referentie = verwijzing; integriteit = het voldoen aan beperkingsregels). Informeel zegt de regel: als we ergens een sleuteltje hebben liggen, moet er (aan de andere kant van de verwijspijl) een slotje zijn waar dat sleuteltje op past. Naar een brede primaire sleutel kan alleen worden verwezen met een brede verwijssleutel: als het slotje breed is, moet ook het sleuteltje breed zijn.

Ouder Kind

De rollen die twee tabellen hebben ten opzichte van een verwijzing, worden vaak aangeduid met de termen ouder en kind (Engels: parent en child). De tabel met de verwijssleutel heet het kind; de tabel waarheen verwezen wordt, heet de ouder, zie figuur 3.8.

Eenheid p naam p naam FIGUUR 3.8

eenheid°

Product energiePE°

oudertabel

kindtabel

Ouder-kind-combinatie (ten opzichte van verwijzing)

Merk op dat bij een rij van de kindtabel ofwel geen enkele rij ofwel precies één rij van de oudertabel hoort. De mogelijkheid ‘geen enkele rij’ kan zich alleen voordoen wanneer de verwijzing optioneel is, zoals in figuur 3.8. Bij een verwijzing vanuit een verplichte kolom is er altijd precies één ouderrij bij elke kindrij. Omgekeerd: bij één rij van de oudertabel kunnen nul, één of meer rijen van de kindtabel horen. Dit kan per rij verschillen.

97

Open Universiteit

Databases

Tekenconventie In deze cursus houden we ons aan de volgende tekenconventie: in structuurdiagrammen wordt de ‘ouder’ indien mogelijk hoger dan het ‘kind’ getekend (zie opgave 3.4 voor een uitzonderingssituatie). Associatie

Een ouder-kindrelatie wordt ook wel associatie genoemd. We prefereren de term ‘associatie’ boven ‘relatie’ omdat, zoals we hebben gezien, de term ‘relatie’ in de relationele theorie al een andere betekenis heeft, namelijk die van ‘tabel’ (in wiskundige zin).

Verwijssleutels vormen het ‘cement’ van de database. Het bewaken van de referentiële-integriteitsregel is daarom een fundamentele taak van het rdbms, die op vele momenten actueel is. Uitgaande van twee tabellen met een ouder-kind-associatie moet het rdbms in de volgende gevallen zorg dragen voor een juiste correspondentie tussen de verwijssleutel (in de kindtabel) en de primaire sleutel (in de oudertabel): – bij een poging een rij in de kindtabel toe te voegen:

Gecontroleerd moet worden of de verwijssleutelwaarde voorkomt als primaire-sleutelwaarde in de oudertabel.

– bij een poging een verwijssleutelwaarde in de kindtabel te wijzigen:

Gecontroleerd moet worden of de nieuwe verwijssleutelwaarde bestaat als primairesleutelwaarde in de oudertabel.

– bij een poging een primairesleutelwaarde in de oudertabel te wijzigen:

Moeten de corresponderende verwijssleutelwaarden dezelfde wijziging ondergaan, zodat de correspondentie in stand blijft?

– bij een poging tot verwijderen van Moet dit worden toegestaan? En zo ja: wat een ouderrij: moet met de kindrijen gebeuren, zodat deze niet als ‘weeskind’ (met ‘naar niets’ verwijzende sleutelwaarden) achterblijven?

Let op het woord ‘poging’: soms zal de actie geen doorgang kunnen vinden, omdat de vereiste correspondentie niet gehandhaafd of tot stand gebracht kan worden. Met name de laatste twee kwesties zijn nadere studie waard, omdat het antwoord niet eenduidig is. In paragraaf 2, die gewijd is aan ‘gedragsregels’, zullen we hier nader op ingaan. 1.8

MULTIPLICITEITSREGELS

Bij een verwijzing is het meestal belangrijk vast te leggen hoeveel rijen van de oudertabel bij één rij van de kindtabel kunnen horen. En omgekeerd, hoeveel rijen van de kindtabel bij één rij van de oudertabel kunnen horen. Zo geldt voor de verwijzing van Product naar Eenheid: – bij één Product-rij horen nul of eenEenheid-rijen – bij één Eenheid-rij horen nul of meer Product-rijen.

98


Multipliciteitsregel Kardinaliteitsregel

Hierbij betekent ‘nul of meer’: willekeurig veel. Dit type ‘aantallenregels’ worden multipliciteitsregels genoemd, of ook wel kardinaliteitsregels, naar de wiskundige term ‘kardinaliteit’ voor het aantal elementen van een verzameling. De multipliciteitsregels bij de andere verwijzingen zijn als volgt: – bij één Ingredient-rij hoort precies één Product-rij – bij één Product-rij horen dus nul of meer Ingredient-rijen en: – bij één Ingredient-rij hoort precies één Gerecht-rij – bij één Gerecht-rij horen één of meer Ingredient-rijen.

Multipliciteitendiagram

Al deze regels worden geïllustreerd in de voorbeeldpopulatie. In figuur 3.9 worden ze grafisch weergegeven, in een zogenaamd multipliciteitendiagram. Merk op dat we de multipliciteiten steeds moeten aflezen ‘aan de andere kant’ van het lijntje, lezend van de ene tabel naar de andere.

Eenheid bij één product horen 0 of 1 eenheden bij één eenheid horen 0 of meer producten

Gerecht

Product 0 of 1 precies 1 0 of meer

Ingredient

FIGUUR 3.9

1 of meer

Multipliciteitendiagram van Reijnders’ Toetjesboek

Merk op dat we gerechten met nul ingrediënten toestaan. Dit is een ontwerpkeuze waar heel wat over te zeggen is. In de volgende paragraaf gaan we daar nader op in. Een multipliciteitendiagram geeft de structuur van een database overzichtelijk weer, zonder details van de kolommen. Merk op dat elke verwijzing een 1-kant (0-of-1) en een veel-kant (0-of-meer, dan wel 1-ofmeer) heeft. De 1-kant staat aan de kant van de oudertabel; de veel-kant aan de kant van de kindtabel. Dit komt overeen met de pijlnotatie in een strokendiagram: de pijl loopt van veel naar 1. Zie ook figuur 3.10.

Eenheid p naam p naam FIGUUR 3.10

99

eenheid°

Product energiePE°

Een verwijzing en multipliciteitsregels

Eenheid

Product

Open Universiteit

Databases

1.9

EEN ‘KIP-EI’-PROBLEEM

Stel, we gaan in het databaseontwerp uit van de regel dat een gerecht minstens één ingrediënt moet hebben. Zo gek is dat niet, immers wat heb je aan een gerecht zonder ingrediënten? In het multipliciteitendiagram krijgen we dan aan de ‘meer’-kant een één-of-meer regel. In het strokendiagram moeten we een extra regel moeten toevoegen. Omdat daar geen grafische notatie voor bestaat, doen we dat in tekst. Zie figuur 3.11. p naam


p Ingredient gerecht product hoeveelheidPP°

Gerecht

Ingredient

extra regel: elk gerecht heeft minstens één ingrediënt FIGUUR 3.11

‘Kip-ei’-probleem

De 1-of-meer-multipliciteitsregel (‘kraaiepootje met streepje’), in combinatie met een precies-1 regel (dubbel streepje) aan de andere kant, is problematisch. Er is hier een soort ‘kip-ei’-probleem. Want voegen we als eerste een Gerecht-rij toe, dan overtreden we de 1-of-meer-regel. Maar beginnen we met een Ingredient-rij, dan is niet voldaan aan de precies-1regel. En stel dat het ons toch is gelukt: met verwijderen hebben we net zo’n probleem. Want de ‘kip’ (een Gerecht-rij) kunnen we niet verwijderen zolang deze nog een ‘ei’ (Ingredient-rij) bezit, maar hoe beroven we de kip van haar laatste ei? Een beperking van de meest gangbare SQL-dialecten zet dit hele probleem nog in een ander licht: het blijkt daarin namelijk onmogelijk de 1of-meer-regel te implementeren. Anders gezegd: het blijkt niet zonder meer mogelijk af te dwingen dat een ouderrij minstens één kindrij heeft. Eén-veel-regel implementeren in applicatie

Al met al betekent dit dat we in de databasepraktijk altijd 0-of meermultipliciteiten hebben en daarmee het kip-ei-probleem omzeilen. Voor wie per se een één-of-meer-regel wil implementeren, bestaan wel mogelijkheden. Een oplossing is de regel niet door het rdbms maar door de applicatieprogrammatuur te laten bewaken. Zo kunnen we ons voorstellen dat de gebruiker het gerechtenscherm pas kan sluiten wanneer het gerecht een ingrediënt bevat en een passende foutmelding krijgt wanneer hij dat probeert bij een gerecht zonder ingrediënt. In paragraaf 2.1 zal overigens blijken dat we het rdbms opdracht kunnen geven om bij het verwijderen van een Gerecht-rij in één klap (als het ware tegelijkertijd) ook alle bijbehorende Ingredient-rijen te verwijderen: een zogenaamde cascading delete.

OPGAVE 3.1

Ga in figuur 3.7 nog eens na welke ‘sleuteltjeskolommen’ naar welke ‘slotjeskolommen’ verwijzen.

100


a Welke tabellen hebben geen verwijzing (‘sleuteltje’) naar een andere tabel? b Welke tabel heeft twee verwijzingen naar andere tabellen? c Naar welke tabel wordt vanuit geen enkele andere tabel verwezen? OPGAVE 3.2

Welk van de multipliciteitendiagrammen a, b of c in figuur 3.12 is, voor wat betreft de multipliciteiten, equivalent met het strokendiagram in figuur d? a

b

c

Rubriek

Rubriek

Rubriek

Boek

Boek

Boek

d

p code

p boeknr FIGUUR 3.12

titel

Rubriek omschrijving

Boek rubriek°

Welk multipliciteitendiagram past bij het strokendiagram?

OPGAVE 3.3

Dezelfde vraag als in opgave 3.2, nu met een verplichte kolom Boek.rubriek. OPGAVE 3.4

Een grootwinkelbedrijf heeft een aantal filialen. Werknemers kunnen werkzaam zijn bij niet meer dan één filiaal. Elk filiaal heeft onder normale omstandigheden één regiomanager, maar deze functie kan ook vacant zijn. Een regiomanager kan bij één van zijn of haar filialen werkzaam zijn (maar ook op het hoofdkantoor). De bedrijfsdatabase bevat onder meer de tabellen Werknemer en Filiaal. Figuur 3.13 geeft hiervan de structuurdiagrammen, met een kleine voorbeeldpopulatie. Alleen enkele belangrijke kolommen zijn weergegeven. Ter onderscheiding zijn in het multipliciteitendiagram de associaties van een naam voorzien: ‘een werknemer is werkzaam bij geen enkel of bij één filiaal’, en ‘een filiaal heeft maximaal één werknemer als regiomanager’. Vraag: hoe zit het in deze structuur met de kwalificaties ‘ouder’ en ‘kind’?

101

Open Universiteit

Databases

a

b

p

Filiaal regiomanager°

nr

plaats

1 2 3

Amsterdam Rotterdam Haarlem

p nr 123 124 128 130 135

naam Dolstra de Boer Mier Bakker Cupido

FIGUUR 3.13

130 124 130

Filiaal is werkzaam bij

Werknemer filiaal° 1 2 2

heeft als regiomanager

Werknemer

3

Tabellen met verwijzingen over en weer 1.10

BIJZONDERE BEPERKINGSREGELS

Standaard beperkingsregels

De beperkingsregels die in de vorige paragrafen zijn behandeld, worden standaard beperkingsregels genoemd. We komen deze in elke relationele database tegen, omdat ze nauw samenhangen met de relationele structuur als zodanig. In een strokendiagram zijn dit soort regels in één oogopslag af te lezen, uit de horizontale en verticale pijlen en door het ontbreken van een ‘o’ bij verplichte kolommen. De toetjesdatabase moet nog wel aan meer voorwaarden voldoen, ‘specifieke toetjesregels’, waarvoor geen grafische notatie bestaat. Dit soort regels zullen we soms in natuurlijke taal aan de diagrammen toevoegen.

Bijzondere beperkingsregels

Voorbeelden van zulke bijzondere beperkingsregels zijn: – in Ingredient mag hoeveelheidPP alleen oningevuld zijn als de energiewaarde van het product ook niet is ingevuld (zoals bij peper) of 0 is – in Ingredient moet hoeveelheidPP oningevuld zijn als van het ingrediënt (in Product) geen eenheid gegeven is. De achtergrond van de eerste regel is dat het Toetjesboek automatisch de energiewaarde (energiePP) van een gerecht moet berekenen. Daarvoor moet de hoeveelheid van elk ingrediënt bekend zijn, behalve van ingrediënten die niets bijdragen aan die energiewaarde. De tweede regel geldt omdat een hoeveelheid geen betekenis heeft als er geen maateenheid is. Nog enkele voorbeelden van bijzondere beperkingsregels: – een hoeveelheid is altijd groter dan 0 – een energiewaarde is altijd groter dan 0 of gelijk aan 0. 1.11

HET BEWAKEN VAN BEPERKINGSREGELS

De standaard beperkingsregels (verplichte kolommen, uniciteitsregels, primaire sleutels, referentiële integriteit bij verwijssleutels) worden afgedwongen door ze op te nemen in de structuurdefinitie van tabellen. Deze structuurdefinitie wordt in de vorm van opdrachten, geformuleerd in de gegevenstaal SQL, aan het rdbms meegedeeld. Hoe dit gaat, zien we in leereenheid 4 ‘Communiceren met een relationele database’. Ook enkele eenvoudige bijzondere beperkingsregels kunnen op deze wijze aan het rdbms kenbaar worden gemaakt, bijvoorbeeld de regels

102


die een minimum- of maximumwaarde voor een kolom aangeven. Ingewikkelder beperkingsregels, en ook allerlei actief gedrag van de database (denk aan het automatisch berekenen van de energie per persoon van een gerecht), moeten worden gerealiseerd door aanvullend programmeerwerk. Zie paragraaf 2.4 van leereenheid 2, over triggers. 1.12

Kunstmatige sleutel

Alternatieve sleutel

KUNSTMATIGE SLEUTELS

De twee verwijssleutels Ingredient.gerecht en Ingredient.product bevatten vrij lange tekstvelden. Het kost het rdbms relatief veel tijd om daar de corresponderende primaire sleutels in Gerecht respectievelijk Product bij te zoeken. Ook nemen ze nogal wat geheugenruimte in beslag. In plaats van zulke lange tekstvelden worden daarom vaak numerieke codes gekozen als primaire sleutel en verwijssleutel. In dit geval: kunstmatige gerechtnummers en productnummers. Voor de eindgebruiker hebben deze kunstmatige sleutels geen betekenis; in de applicatie moeten ze daarom onzichtbaar blijven. En het is het rdbms zelf dat nieuwe gerechten en producten zo’n kunstmatig nummertje geeft, volautomatisch. Tenminste, wanneer bij de bouw van de database daarvoor de juiste voorzieningen zijn getroffen. Hoe dat gaat, zullen we later zien, in leereenheid 13. Zie figuur 3.14 voor een strokendiagram van Reijnders’ Toetjesboek met kunstmatige sleutels. Beide kolommen met kunstmatige nummers heten ‘id’. De oorspronkelijke primaire sleutels, Gerecht.naam en Product.naam, hebben nu de status van alternatieve sleutel gekregen, zie paragraaf 1.4.

p id

Gerecht a naam /energiePP° bereidingstijd bereidingswijze Eenheid p naam p id

a naam

eenheid°

Product energiePE°

Ingredient p gerecht product hoeveelheidPP° FIGUUR 3.14

Strokendiagram met kunstmatige sleutels

Figuur 3.15 geeft een voorbeeldpopulatie. De kunstmatige verwijsnummers hoeven niet vanaf 1 te lopen en in de praktijk zullen ze vaak ook niet allemaal opeenvolgend zijn. Dat komt bijvoorbeeld doordat er ook wel eens gerechten en producten verwijderd worden; hun nummertje wordt dan niet opnieuw gebruikt. Maar het maakt allemaal niets uit: het zijn kunstmatige nummers, die altijd op de achtergrond blijven, onzichtbaar voor de gebruikers van Reijnders’ Toetjesboek.

103

Open Universiteit

Databases

Eenheid naam liter stuks deciliter gram kilogram eetlepel theelepel

Product

Gerecht id 1 2 3

naam Coupe Kiwano Glace Terrace Mango Plus Plus

energiePP bereidingstijd bereidingswijze 431 403 131

20 5 8


id

naam

1 3 4 5 6 7 10 12 13 14 15

ijs kiwano slagroom suiker tequila aardbeien pernod peper mango zure room banaan

eenheid energiePE liter stuks deciliter gram eetlepel gram eetlepel

1600 40 336 4 30 0.25 35


80 195 40

Ingredient gerecht 1 1 1 1 1 2 2 2 2 3 3 3

FIGUUR 3.15

product hoeveelheidPP 1 3 4 5 6 1 7 10 12 13 7 14

0.15 0.5 0.3 10 1 0.2 50 2 0.5 50 0.4

Toetjesboek-database met kunstmatige sleutels

Deze versie van het Toetjesboek is in de Boekverkenner beschikbaar onder de naam ToetjesboekKS (‘met kunstmatige sleutels). Omdat ook in Nederlandse teksten vaak de Engelse benamingen van sleutels gebruikt worden, geven we tot slot van deze paragraaf de Engelse benamingen van de verschillende soorten sleutels en bovendien een veelgebruikt Nederlands synoniem voor ‘verwijssleutel’: Primary key Foreign key Vreemde sleutel Alternate key

primaire sleutel = verwijssleutel = alternatieve sleutel = 2

Actie in reactie op gebeurtenissen

Gedrag

primary key foreign key alternate key

=

vreemde sleutel

Gedragsregels

Via SQL of door aanvullend programmeerwerk kan worden gezorgd dat het rdbms acties onderneemt, in antwoord op bepaalde gebeurtenissen (Engels: events). Zo’n gebeurtenis kan zijn: een verandering van de database-inhoud of een poging daartoe. Ook het aanbreken van een bepaald tijdstip kan een ‘gebeurtenis’ zijn die het startsein is voor een actie van het rdbms. Processen die leiden tot veranderingen in de database, worden gedrag genoemd.

104


Gedragsregel

In deze paragraaf gaat het om voorgeprogrammeerd gedrag in relatie met dreigende overtredingen van beperkingsregels. We spreken van gedragsregels. De belangrijkste zijn de ‘refererende actieregels’, die verband houden met het handhaven van referentiële integriteit bij het invoegen, verwijderen of wijzigen van een rij. 2.1

REFERERENDE ACTIEREGELS

De referentiële integriteitsregel, die eist dat bij elke verwijssleutelwaarde (kind) een ouderrij bestaat, roept in twee gevallen problemen op: – bij het verwijderen van een ouderrij – bij het wijzigen van de primaire-sleutelwaarde in een ouderrij.

Deleteregel Updateregel

Refererende actieregel

In het eerste geval kunnen er ‘verweesde’ kinderen achterblijven: rijen in de kindtabel met een loze verwijzing. In het tweede geval heeft een rij in de oudertabel zijn logisch adres (primaire-sleutelwaarde) veranderd, terwijl eventuele kindrijen nog het oude adres (verwijssleutelwaarde) hebben. Om deze problemen bij voorbaat op te lossen, wordt voor elke verwijzing een deleteregel en een updateregel gespecificeerd. Ze drukken uit of er iets mag gebeuren, en zo ja, wat er moet gebeuren bij een poging tot deleten van een rij of een poging tot updaten van een primaire sleutel, beide in de oudertabel. De deleteregels en de updateregels heten samen de refererende actieregels. Deleteregels Er zijn drie deleteregels. Ze zeggen wat er moet gebeuren bij een poging tot deleten van een ouderrij. Voor elke verwijzing moet één van deze regels worden gespecificeerd.

Restricted delete Cascading delete

Nullifying delete

– Restricted delete: een poging tot deleten van een ouderrij mislukt wanneer er één of meer corresponderende kindrijen bestaan. – Cascading delete: bij een poging tot deleten van een ouderrij zal een poging tot deleten van alle corresponderende kindrijen worden ondernomen. Is er een (andere) regel die dat tegenhoudt, dan gaat het deleten niet door. – Nullifying delete: bij een poging tot deleten van een ouderrij wordt gepoogd verwijzingen in eventuele kindrijen op null te zetten. Is er een (andere) regel die dat tegenhoudt, dan gaat het deleten niet door. De restricted-deleteregel houdt een verbod op ‘oudermoord’ in. Wat wel mag, is van onderaf beginnen: eerst de kinderen verwijderen en dan hun ouder. Van de drie deleteregels komt deze regel het meeste voor. Bij de meeste verwijzingen kunnen we dus niet zomaar een ouderrij verwijderen wanneer er nog één of meer rijen bestaan in de corresponderende kindtabel, zie figuur 3.16. Zoals al eerder opgemerkt, geldt deze regel automatisch wanneer géén van de andere is gespecificeerd. Hij is dan een direct gevolg van de referentiële-integriteitsregel, die altijd geldt.

105

Open Universiteit

Databases

Product naam

eenheid

ijs kiwano slagroom suiker tequila aardbeien pernod peper mango zure room banaan

energiePE


1600 40 336 4 30 0.25 35


80 195 40

poging tot delete van ouderrij ...

rd

Ingredient gerecht

product

Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Glace Terrace Glace Terrace Glace Terrace Glace Terrace Mango Plus Plus Mango Plus Plus Mango Plus Plus

hoeveelheidPP

ijs kiwano slagroom suiker tequila ijs aardbeien pernod peper mango aardbeien zure room

0.15 0.5 0.3 10 1 0.2 50 2

... mislukt wanneer er nog één of meer corresponderende kindrijen bestaan.

0.5 50 0.4

Effect van restricted delete (rd)

FIGUUR 3.16

Wilt u het effect van een restricted delete omzeilen, dan zult u eerst welbewust de kindrijen van een ouderrij moeten verwijderen. Het product ‘aardbeien’ kunt u dus pas verwijderen, wanneer u eerst alle Ingredient-rijen met ‘aardbeien’ erin hebt verwijderd. De cascading-deleteregel wordt doorgaans gespecificeerd voor verwijzingen zoals van Ingredient naar Gerecht. Een gebruiker die een Gerecht-rij wil verwijderen, zal ook de corresponderende Ingredient-rijen willen verwijderen, want zonder bijbehorend gerecht hebben deze geen bestaansreden. Zie figuur 3.17. Gerecht naam


Coupe Kiwano Glace Terrace Mango Plus Plus

431 403 131

20 5 8


poging tot delete van ouderrij ...

cd


FIGUUR 3.17

106


hoeveelheidPP 0.15 0.5 0.3 10 1 0.2 50 2 0.5 50 0.4

Effect van cascading delete (cd)

... heeft poging tot delete van corresponderende kindrijen tot gevolg. Deze poging slaagt, in dit geval.


Wanneer een te verwijderen kindrij zelf ouder is van een kleinkindrij, kan de verwijzing vanuit het kleinkind naar het kind de verwijdering alsnog tegenhouden, bijvoorbeeld wanneer daarvoor een restricted delete geldt! De nullifying-deleteregel komt in de praktijk weinig voor en wordt door veel rdbms’en niet ondersteund. Updateregels Er zijn drie updateregels. Ze zeggen wat er moet gebeuren bij een poging om een primaire-sleutelwaarde in een ouderrij te wijzigen. Voor elke verwijzing moet één van deze regels worden gespecificeerd. Restricted update

Cascading update

Nullifying update

– Restricted update: een poging tot updaten van de primaire-sleutelwaarde in een ouderrij mislukt wanneer er één of meer corresponderende kindrijen bestaan. – Cascading update: bij een poging tot update van een primaire-sleutelwaarde in een ouderrij, wordt een poging tot updaten ondernomen van de verwijssleutelwaarden in alle corresponderende kindrijen. Is er een (andere) regel die dat tegenhoudt, dan gaat het updaten niet door. – Nullifying update: bij een poging tot updaten van een primairesleutelwaarde in een ouderrij, wordt gepoogd verwijzingen daarnaar in corresponderende kindrijen op null te zetten. Is er een (andere) regel die dat tegenhoudt, dan gaat het updaten niet door. Van de updateregels ligt de cascading update het meest voor de hand: een verandering van een primaire sleutelwaarde (bijvoorbeeld om een spelfout te herstellen) zullen we vrijwel altijd willen laten doorwerken in de verwijzingen, om zodoende de correspondentie in stand te houden. Zie figuur 3.18. Het is zoiets als het doorgeven van een adreswijziging aan al je relaties; die wijzigen dan dit adres in hun eigen adresboekjes. Gerecht naam


Coupe Kiwano Coupe Reijnders Glace Terrace Mango Plus Plus

419 403 105

20 5 8


poging tot update van primaire sleutel ...

cu

Ingredient gerecht

Coupe Reijnders Coupe Reijnders Coupe Reijnders Coupe Reijnders

FIGUUR 3.18

Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Coupe Kiwano Glace Terrace Glace Terrace Glace Terrace Glace Terrace Mango Plus Plus Mango Plus Plus Mango Plus Plus


hoeveelheidPP 0.4 2 2.5 30 4 0.2 50 2 1 200 1.5

... heeft poging tot update van verwijzende sleutelwaarden tot gevolg. Deze poging slaagt, in dit geval.

Effect van cascading update (cu)

Cascading update: ‘zoek-en-vervang’ Een primaire-sleutelwaarde is het ‘single point of definition’ van een rijidentificatie. Alle verwijssleutelwaarden die naar die rij verwijzen, dienen er exact aan gelijk te zijn en te blijven. Dit wordt bereikt met de cascading update, die functioneert als een soort ‘zoek-en-vervang’ voor verwijssleutelwaarden bij

107

Open Universiteit

Databases

verandering van een primaire-sleutelwaarde. Het ligt voor de hand om, gegeven een primaire sleutel, ofwel voor alle verwijzingen ernaar een cascading update te specificeren, ofwel voor geen enkele. Immers een ‘halve zoek-en-vervang’ leidt tot een niet-consistente database.

Aan de nullifying update is in de praktijk weinig behoefte en door veel rdbms’en wordt hij niet ondersteund. In figuur 3.19 is voor elke verwijssleutel één deleteregel (rd of cd) en één updateregel (steeds cu) gespecificeerd. Merk op dat voor de verwijzing van Ingredient naar Gerecht een cascading delete geldt, maar voor de overige verwijzingen een restricted delete. Dit betekent dat bij het verwijderen van een gerecht door de gebruiker de bijbehorende Ingredient-rijen automatisch meeverwijderd worden. Maar wanneer de gebruiker probeert een product te verwijderen, wordt dit tegengehouden zolang er nog gerechten zijn waarin dit product als ingrediënt voorkomt. Ook een Eenheid-rij kan niet zomaar verwijderd worden; dat kan alleen wanneer geen enkel product meer in die eenheid wordt gemeten.


p naam

Eenheid p naam cd, cu

rd, cu

p naam


rd, cu

Ingredient p gerecht product hoeveelheidPP° volgnr FIGUUR 3.19

Strokendiagram met deleteregels en updateregels

De delete- en updateregels worden gespecificeerd voor elke verwijssleutel afzonderlijk. In strokendiagrammen houden we de restricted delete en de cascading update als default aan, omdat die het meeste voorkomen. Voor figuur 3.19 betekent dit alle regels behalve de cascading update (cd) mogen worden weggelaten. In SQL moeten we de cascading update wel specificeren, omdat daar de beide restricted varianten default zijn. 2.2

BIJZONDERE GEDRAGSREGELS

De refererende acties moeten bij elke verwijzing worden gespecificeerd, en zijn daarom een type regel dat in elke database voorkomt: het zijn standaard gedragsregels. Maar er zijn meestal ook gedragsregels die specifiek zijn voor de database. Deze noemen we bijzondere gedragsregels. Een voorbeeld van een bijzondere gedragsregel voor Reijnders’ Toetjesboek is de volgende.

108


‘De energie per persoon van een gerecht is afleidbaar uit de energie per eenheid van de producten en de hoeveelheden per persoon van de ingrediënten in het gerecht. Berekeningsvoorschrift: alle bij het gerecht horende hoeveelheden per persoon vermenigvuldigen met de bijbehorende energiewaarden per eenheid en deze producten optellen.’ Deze regel moet automatisch in werking treden nadat een nieuw gerecht met alle ingrediëntinformatie is ingevoerd en de gebruiker heeft aangegeven dat de informatie correct is ingevoerd en definitief mag worden vastgelegd. Maar ook moet de regel automatisch in werking treden wanneer er later nog een ingrediënt wordt toegevoegd of wordt verwijderd. En ook wanneer een van de gegevens uit het berekeningsvoorschrift wordt gewijzigd. 2.3

TRANSACTIES

Een opvraging verandert niets aan de inhoud van een database. De volgende elementaire acties doen dat wel: – invoeren van een nieuwe rij – verwijderen van een rij – wijzigen van een celinhoud binnen een rij. Vaak staan deze niet op zichzelf en moeten meerdere acties worden gebundeld om een zinvol resultaat te krijgen. We hebben hier al enkele voorbeelden van gezien.

Transactie

Commit

VOORBEELD 3.1

Om een nieuwe Gerecht-rij in te voeren, moeten we ook één of meer Ingredient-rijen invoeren. Wanneer zo’n Ingredient-rij een nieuw product bevat, moet ook een nieuwe Product-rij worden ingevoerd.

VOORBEELD 3.2

Wanneer de energie per eenheid van een product wordt gewijzigd, moet ook (automatisch) de energie per persoon van de gerechten waarin dat product voorkomt, worden gewijzigd.

VOORBEELD 3.3

Wanneer we een Gerecht-rij verwijderen, zijn we verplicht om behalve de Gerecht-rij zelf ook de bijbehorende Ingredient-rijen te verwijderen. Anders blijven we immers zitten met Ingredient-rijen die ‘naar niks’ verwijzen, in strijd met de regel voor verwijssleutels. Dit wordt gerealiseerd door de cascading-deleteregel uit paragraaf 2.1.

Al deze voorbeelden geven een opeenvolging van databaseacties te zien die van nature bij elkaar horen: doen we het één, dan moet ook het ander gebeuren. Zo’n opeenvolging heet een transactie. Midden in een transactie hoeft niet aan alle regels te zijn voldaan, maar na afloop wel. Tijdens een transactie bestaat veelal een ongewenste situatie. Na afloop is alles in orde en kunnen we aan het rdbms opdracht geven de veranderingen definitief te maken (Engels: commit). Zie figuur 3.20.

109

Open Universiteit

Databases

voltooide transactie

toestand na vorige commitmoment FIGUUR 3.20

toestand na nieuw commitmoment

Transactie: de acties tussen twee commitmomenten

Het committen wordt in de meeste gevallen vanuit de applicatie geregeld, door het versturen van een speciale SQL-opdracht (commit) naar het rdbms. Dit is al dan niet het gevolg van een bewuste actie van de gebruiker, bijvoorbeeld het aanklikken van een speciaal knopje: de commit-knop of save-knop. Wordt op dat moment niet aan alle regels voldaan, dan wordt de commit niet uitgevoerd en wordt teruggekeerd naar de toestand aan het begin van de transactie. Alle wijzigingen worden dan teruggedraaid (Engels: rollback), zie figuur 3.21. Ook de gebruiker zelf kan de lopende transactie annuleren, bijvoorbeeld door een speciale rollback- of ‘undo’-button aan te klikken. In dit geval wordt het SQL-statement rollback naar het rdbms verstuurd.

Rollback

FIGUUR 3.21

Rollback na onvoltooide transactie of expliciete rollbackopdracht

Korte transacties Transacties moeten zo kort mogelijk worden gemaakt. Een van de redenen daarvoor is de mogelijkheid informatie kwijt te raken wanneer tussentijds het systeem uitvalt door bijvoorbeeld een stroomstoring. In zo’n geval wordt de transactie geannuleerd (rollback) en moeten we opnieuw beginnen. Een andere reden is dat gebruikers nu eenmaal fouten maken en transacties daardoor soms niet kunnen worden voltooid. Ook in die gevallen vindt een rollback plaats. Tot slot komt het bij lange transacties nogal eens voor dat het systeem een foutmelding geeft, maar dat de gebruiker niet meer kan achterhalen op welke actie die foutmelding precies betrekking heeft.

In latere leereenheden komen we uitgebreid op transacties terug, onder meer op de problematiek van concurrente transacties: transacties van meerdere gebruikers die elkaar in de tijd overlappen. OPGAVE 3.5

Voorspel het effect van elke hierna genoemde actie, uitgaande van de populatie van figuur 3.1 en de structuurdiagrammen van figuren 3.7 en 3.19. Als dat effect alleen een foutmelding is, vertel dan welke databaseregel de oorzaak is van die foutmelding. Beschouw elke actie op zichzelf.

110


a Poging tot toevoegen van een nieuwe Ingredient-rij (‘Coupe Kiwano’, ‘vanille’, null). b Poging tot toevoegen van een nieuwe Ingredient-rij (‘Coupe Kiwano’, ‘suiker’, 12). c Poging tot toevoegen van een nieuwe Product-rij (‘tjindang’, ‘koffielepel’, 2). d Poging tot toevoegen van een nieuwe Eenheid-rij (‘slagroom’). e Poging tot verwijderen van de Product-rij (‘kiwano’, ‘stuks’, 40). f Poging tot verwijderen van de Product-rij (‘banaan’, ‘stuks’, 40). g poging tot verwijderen van de Gerechtrij (‘Mango Plus Plus’, 131, 8, ‘Snijd ...’). h Poging tot wijzigen van ‘kiwano’ in ‘chiwano’ in de kolom Product.productnaam. i Poging tot wijzigen van de eenheidnaam van aardbeien: ‘gram’ wordt ‘pond’. j Poging tot wijzigen van de bereidingstijd van Coupe Kiwano in null. 3

Meer over uniciteitsregels

Van alle regels waaraan een relationele database moet voldoen, zijn de uniciteitsregels het meest prominent. De structuur van een database (denk aan sleutels) is er direct mee verbonden. Het vaststellen van de juiste uniciteitsregels is daarom essentieel. Hoe bepalen we de juistheid van een voorgestelde uniciteitsregel? In deze paragraaf onderzoeken we welke uniciteitsregels in principe mogelijk zijn en hoe ze kunnen samengaan. De verschillende mogelijkheden zullen we illustreren aan de hand van tabel Inschrijving van de voorbeelddatabase uit leereenheid 1: de Open School. 3.1 Zie paragraaf 1.1 van leereenheid 1

INSCHRIJVINGEN BIJ DE OPEN SCHOOL

We hebben gezien dat de Open School allerlei cursussen aanbiedt, zoals Inleiding informatica (code II), Databases (code DB) en Informatiemodelleren (code IM). Studenten kunnen zich daarvoor inschrijven. In figuur 3.22 ziet u een strokendiagram met twee voorbeeldinschrijvingen. De eerste kolom bevat studentnummers, waardoor studenten uniek worden geïdentificeerd, de tweede kolom bevat de vakcodes waardoor vakken uniek worden geïdentificeerd, de derde kolom bevat de datum waarop de inschrijving heeft plaatsgevonden. welke uniciteitsregel(s)?

Inschrijving

FIGUUR 3.22

111

student

cursus

datum

11 22

DB IM

18-mrt-2012 26-jan-2012

Tabel Inschrijving: welke uniciteitsregel(s)?

Open Universiteit

Databases

Het diagram bevat bewust geen uniciteitspijl, omdat we alle mogelijke uniciteitsregels en combinaties daarvan − zelfs de meest onwaarschijnlijke − zullen onderzoeken. De informatie in de twee rijen laat zich aldus verwoorden: “student 1 heeft zich ingeschreven voor cursus DB op 18-mrt-2012” “student 2 heeft zich ingeschreven voor cursus IM op 26-jan-2012” Om erachter te komen welke uniciteitsregels gelden, moeten allerlei vragen worden beantwoord, zoals: zou student 1 zich ook mogen inschrijven voor cursus IM op nog een tweede dag? Of: zou student 1 zich op 18-mrt-2012 voor nog een tweede vak mogen inschrijven? Of: mag nog een tweede student zich op 18-mrt-2012 inschrijven voor de cursus DB? Vanuit de praktijk liggen bepaalde antwoorden misschien weinig voor de hand. Hier echter is het ons te doen om een systematisch onderzoek van wat in principe mogelijk is aan eisen en regels. Daarbij gaat het om mogelijke uniciteitsregels over één kolom (zie 1, 2 en 3 in figuur 3.23), over twee kolommen (4, 5 en 6) en over drie kolommen (7). ?

1 Is student uniek?

student

cursus

datum

student

cursus

datum

student

cursus

datum

student

cursus

datum

4 Is het zo dat student en cursus elk apart niet uniek zijn, maar de combinatie (student, cursus) wel?

student

cursus

datum

5 Is het zo dat student en datum elk apart niet uniek zijn, maar de combinatie (student, datum) wel?

student

cursus

datum

6 Is het zo dat cursus en datum elk apart niet uniek zijn, maar de combinatie (cursus, datum) wel?

student

cursus

datum

7 Zijn de combinaties (student, cursus), (student, datum) en (cursus, datum) geen van drieën uniek?

?

2 Is cursus uniek?

? ?

?

?

?

FIGUUR 3.23 Conventie

3 Is datum uniek?

Uniciteitsregels over drie kolommen: zeven vragen

Deze zeven regels zijn niet onafhankelijk van elkaar, omdat we uitgaan van de volgende conventie: wanneer van twee regels de ene regel strenger is dan de andere regel, formuleren we alleen de strengste regel. Formuleren we bijvoorbeeld regel 4, dan geven we daarmee impliciet aan dat de regels 1 en 2 niet gelden. En formuleren we regel 7, dan geven we daarmee aan dat alle andere regels niet gelden. Immers: wanneer we een pijl versmallen, verbiedt hij méér en wordt de regel strenger. In het algemeen impliceert een brede pijl dat alle smallere over één of meer van de betrokken kolommen niet gelden, zie figuur 3.24.

112


student

cursus

datum

student

cursus

datum

student

cursus

datum

student

cursus

datum

FIGUUR 3.24

Een brede uniciteitsregel impliceert niet-uniciteit van de afzonderlijke kolommen of van smallere combinaties

Het antwoord op de ‘zeven vragen’ hangt af van de ‘bedrijfsregels’ die voor de Open School gelden, en is niet zonder meer een vanzelfsprekend ‘ja’ of ‘nee’. In figuur 3.25 geven we voor vier van de zeven regels aan wat een ‘ja’ zou inhouden, met steeds een kleine illustratieve voorbeeldpopulatie.

student 1 2 3 4

student 1 1 3 3

student 1 1 3 3

cursus DB DB IM DB

cursus IM II IM II

cursus IM IM IM II

1 Als deze regel geldt, mag elke student maar één inschrijving doen (voor één cursus op één dag). Meerdere inschrijvingen voor dezelfde cursus of op dezelfde dag zijn toegestaan, zelfs voor dezelfde cursus én dag.

datum 18-mrt-2012 18-mrt-2012 18-mrt-2012 26-jan-2012

4 Als deze regel geldt, mag een student zich voor een cursus maar één keer (op één bepaalde datum) inschrijven en niet nog eens voor dezelfde cursus op een andere datum. Een student mag zich inschrijven voor verschillende cursussen. Voor één cursus mogen zich meerdere studenten inschrijven.

datum 18-mrt-2012 18-mrt-2012 18-mrt-2012 26-jan-2012

5 Als deze regel geldt, mag een student zich op een bepaalde datum maar voor één cursus inschrijven. Een student mag zich echter meer dan één keer inschrijven, d.w.z. met meerdere cursus/datum-combinaties. Ook mogen op één datum meerdere inschrijvingen plaatsvinden, d.w.z. met meerdere student/cursus-combinaties.

datum 18-mrt-2012 26-jan-2012 18-mrt-2012 26-jan-2012

7 Als deze 'regel' geldt, geldt in feite geen enkele regel: student 1 1 1 3

cursus DB DB IM DB

datum

Studenten mogen zich voor dezelfde cursus meermalen inschrijven, op verschillende data. Studenten mogen zich op dezelfde datum meermalen inschrijven, voor verschillende cursussen. Voor een cursus mogen zich op één datum gegeven meerdere studenten inschrijven.

18-mrt-2012 26-jan-2012 18-mrt-2012 18-mrt-2012

FIGUUR 3.25 3.2

Vier van de zeven uniciteitsregels over drie kolommen

COMBINATIES VAN UNICITEITSREGELS

In de vorige paragraaf hebben we gezien dat er zeven verschillende uniciteitsregels over drie kolommen mogelijk zijn. Deze regels kunnen gecombineerd voorkomen, voor zover er geen uniciteitspijlen zijn die helemaal over een andere heen liggen, zie figuur 3.24.

113

Open Universiteit

Databases

Figuur 3.26 geeft voor enkele mogelijke combinaties een illustratieve voorbeeldpopulatie. Er zijn enkele onwaarschijnlijke combinaties bij, zoals b, waarbij elke kolom uniek is (en waarbij de Open School waarschijnlijk snel failliet zal gaan). De populaties illustreren de vrijheid die er nog is binnen de gegeven regels. Zo illustreert de populatie van c dat de combinatie (student, datum) niet uniek is en de kolom cursus evenmin. En de populatie van d illustreert dat elke kolom apart niet uniek is. student 1 2 3

student 1 2 3

student 1 1 3 5

student 1 1 3 4

student 1 2 3

cursus DB IM II

cursus DB IM II

cursus DB IM DB IM

cursus DB IM IM DB

cursus DB DB IM

datum 18-mrt-2012 18-mrt-2012 26-jan-2012

datum 18-mrt-2012 13-feb-2012 26-jan-2012

datum 18-mrt-2012 18-mrt-2012 15-apr-2012 13-feb-2012

datum

a Elke student mag maar één inschrijving doen (voor een bepaalde cursus op een bepaalde datum). Voor elke cursus mag maar één inschrijving plaatsvinden (door een bepaalde student op een bepaalde datum). Op één datum mogen echter meerdere inschrijvingen plaatsvinden. b Hetzelfde als a, met één verschil: Op elke datum mag maar één inschrijving plaatsvinden.

c Een student mag zich voor een cursus maar één keer inschrijven (op een bepaalde datum). Per cursus mag op één dag maar één inschrijving plaatsvinden. Student, cursus en datum, elk apart, mogen in meerdere inschrijvingen voorkomen. Ook mag een student zich op dezelfde dag voor meerdere cursussen inschrijven. d Hetzelfde als c, met één verschil: Een student mag zich per dag maar voor één cursus inschrijven.

18-mrt-2012 26-jan-2012 18-mrt-2012 26-jan-2012

datum 18-mrt-2012 26-jan-2012 18-mrt-2012

FIGUUR 3.26

e Elk student mag zich maar één keer ergens voor inschrijven (voor één cursus op één datum). En voor elke cursus maar per dag maar één inschrijving plaatsvinden. Voor een cursus mag echter meerdere inschrijvingen plaatsvinden. Op mogen op één dag meerdere inschrijvingen plaatsvinden.

Enkele combinaties van uniciteitsregels over drie kolommen

Uniciteitsregels in de praktijk In de praktijk is er meestal maar een beperkt aantal kolommen dat onder een uniciteitsregel valt. Bovendien zijn verreweg de meeste uniciteitsregels één, twee of drie kolommen breed. De overige kolommen vertegenwoordigen niet-unieke, ‘gewone’ eigenschappen. De in deze paragraaf behandelde uniciteitsregels en hun combinaties geven daarom een aardige indruk van wat we in de praktijk kunnen tegenkomen.

114


OPGAVE 3.6

We willen van elke student van de Open School registreren via welke communicatiemiddelen, met bijbehorend adres, de student bereikbaar is. Communicatiemiddelen zijn bijvoorbeeld: telefoon, e-mail of een internetadres. Figuur 3.27 geeft een tabel Communicatieregel met twee rijen, die elk een ‘communicatiefeit’ bevatten. Deze feiten hebben de volgende verwoordingen: “student 1 heeft telefoon-adres 020-1234567” “student 9 heeft e-mailadres [email protected]”

student

Communicatieregel communicatiemiddel adres

1 9 FIGUUR 3.27

telefoon e-mail

020-1234567 [email protected]

Tabel Communicatieregel

Geef de juiste uniciteitsregel(s) voor de tabel Communicatieregel, met een zo klein mogelijke, maar illustratieve voorbeeldpopulatie. 4

Meer over verwijzingsregels

In paragraaf 1 zijn verwijssleutels behandeld, als een speciaal soort regel: de referentiële-integriteitsregel. Zo’n regel houdt een verbod in op ‘loze verwijzingen’. Ofwel: elke verwijzende waarde hoort naar precies één primaire-sleutelwaarde te verwijzen. In deze paragraaf gaan we dieper op het fenomeen ‘verwijzing’ in. Achtereenvolgens kijken we naar: 1 verwijssleutels van meer dan één kolom breed 2 verwijzingen van een tabel naar zichzelf 3 meerdere verwijzingen tussen dezelfde tabellen 4 verwijzingen waarbij geen sleutels betrokken zijn. Ter illustratie van 1 t/m 3 gebruiken we een uitgebreidere versie van de Open School-database, die we hiertoe stap voor stap uitbreiden. 4.1

Samengestelde primaire sleutel

EEN SAMENGESTELDE SLEUTEL

Een primaire sleutel kan samengesteld zijn, dat wil zeggen: meer dan één kolom omvatten. De kolomwaarden vormen unieke combinaties, die als logische adressen van de rijen dienst doen. Een verwijzing naar een samengestelde primaire sleutel is zelf ook samengesteld en vormt dus een samengestelde verwijssleutel.

VOORBEELD 3.4

Tentamens op de Open School Een student die zich bij de Open School heeft ingeschreven voor een cursus verwerft daarmee het recht om tentamens af te leggen. Elk tentamen hoort bij één inschrijving, dat wil zeggen bij één combinatie van een student en een cursus. Een student mag voor een cursus meerdere keren tentamen doen, dus bij één inschrijving kunnen

115

Open Universiteit

Databases

meerdere tentamens horen. Deze worden van elkaar onderscheiden door een volgnummer. Van elk tentamen wordt natuurlijk een tentamendatum vastgelegd. Later zullen we de mogelijkheid toevoegen om behaalde cijfers en vrijstellingen op te nemen. Figuur 3.28 geeft het relevante deel van de nieuwe structuur: een strokendigram (a) en een voorbeeldpopulatie (figuur b). a

b Inschrijving

p student

cursus

student

cursus p

datum Tentamen volgnr datum

Inschrijving student cursus 1 1 1 2 2 3 4 4

II DB IM II IM II II DB

datum 12-jan-2012 18-mrt-2012 20-jun-2012 12-jan-2012 26-jan-2012 16-jan-2012 20-jan-2012 29-feb-2012

Tentamen student cursus volgnr 1 1 2 2 4

FIGUUR 3.28

II DB IM IM DB

1 1 1 2 1

datum 17-apr-2012 15-jun-2012 06-apr-2012 11-jun-2012 26-jun-2012

Verwijzing over een brede sleutel

Toelichting − In tabel Tentamen is de kolomcombinatie (student, cursus) niet uniek: een student mag immers meer dan één keer tentamen doen voor dezelfde cursus. Evenmin zijn de combinaties (student, volgnr) en (cursus, volgnr) uniek, zoals de voorbeeldpopulatie illustreert. Conclusie: er gelden over deze drie kolommen geen uniciteitsregels die twee kolommen breed zijn. De meest strenge regel is dus de uniciteitsregel over drie kolommen, zoals die in het strokendiagram is aangegeven. Deze combinatie wordt de primaire sleutel. − De combinatie (student, cursus) in Tentamen wijst naar de bijbehorende rij in tabel Inschrijving. We zien hier een voorbeeld van een samengestelde verwijssleutel, ook wel ‘brede verwijssleutel’ genoemd. 4.2

Recursieve verwijzing

EEN RECURSIEVE VERWIJZING

In voorbeelden tot nu toe waren bij elke verwijzing twee verschillende tabellen betrokken. Er kunnen echter ook verwijzingen zijn van een tabel naar zichzelf: recursieve verwijzingen.

116


VOORBEELD 3.5

Docenten die docenten vervangen Een voorbeeld ontlenen we weer aan de Open School, waar veel docenten een vaste vervanger hebben. Zie figuur 3.29.

a

p acr

DAT COD BAC

FIGUUR 3.29

Docent naam

C.Date E.Codd C.Bachman

vervanger°

COD DAT

b

vervanger

Docent Regel: Een docent mag niet zichzelf vervangen

Recursieve structuur: strokendiagram met voorbeeldpopulatie (a) en multipliciteitendiagram (b)

Toelichting − Tabel Docent is uitgebreid met een extra, optionele kolom waarin de vervanger (als die er is) wordt aangegeven, met diens acroniem. Deze kolom wijst naar de tabel Docent zelf, zoals altijd via de primaire sleutel. Vandaar dat er een verwijzingspijl loopt van Docent.vervanger naar Docent.acr: een recursieve verwijzing. − Ook in het multipliciteitendiagram (figuur b) is Docent met zichzelf verbonden. Zo lezen we: ‘een docent kan nul of één vervanger hebben’. Omgekeerd geldt dat een docent voor willekeurig veel (nul, één of meer) collega-docenten als vervanger mag optreden. In de voorbeeldpopulatie is dat aantal echter 0 of 1.

Ouder- en kindrollen bij een recursieve verwijzing Ook bij een recursieve verwijzing hebben we een oudertabel (waar de verwijzing naartoe gaat) en een kindtabel (waar de verwijzing vandaan gaat). Dat is twee keer dezelfde tabel, echter in verschillende rollen. In voorbeeld 3.5 is de oudertabel de tabel Docent in de rol van ‘vervanger’. De kindtabel is de tabel Docent in de rol ‘vervangen worden’.

Virtuele tabel

Wanneer we twee exemplaren van de tabel Docent zouden maken, één in de rol van ouder (naam: Vervanger) en een in de rol van kind (neutrale naam: Docent), dan zou dit het plaatje van figuur 3.30 opleveren. Hieraan ziet u dat een recursieve verwijzing niets bijzonders is. Er bestaat maar één fysieke databasetabel Docent, de beide exemplaren kunt u als virtuele (denkbeeldige) tabellen opvatten. Elk exemplaar is identiek aan Docent, ook al gebruiken we het vanuit één specifiek gezichtspunt (zoals uitgedrukt door de naam).

117

Open Universiteit

Databases

Vervanger acr

naam

vervanger

DAT COD BAC


COD DAT

zelfde tabel

Docent acr

naam

vervanger

DAT COD BAC


COD

FIGUUR 3.30

Tabelalias

DAT

Recursieve verwijzing als gewone verwijzing tussen twee virtuele exemplaren van Docent

Een specifieke naam voor een tabelexemplaar wordt een alias of tabelalias genoemd. Een tabelalias wordt zó gekozen dat deze de rol uitdrukt die een specifiek tabelexemplaar vervult in een bepaald probleem. Aliassen zullen we veelvuldig gebruiken in SQL. 4.3

EEN VEEL-VEEL-ASSOCIATIE TUSSEN EEN TABEL EN ZICHZELF

Een recursieve verwijzing zorgt voor een ouder-kindrelatie tussen een tabel en zichzelf. We kunnen ook zeggen: hij realiseert een één-op-veelassociatie tussen een tabel en zichzelf. Zo zagen we in voorbeeld 3.5 de vervanger-associatie tussen tabel Docent en zichzelf. Veel-veel-associatie

We bekijken nu een nieuw soort associatie tussen een tabel en zichzelf: een veel-veel-associatie. Omdat verwijzingen altijd één-veel-associaties opleveren, is daar een extra tabel voor nodig. Zie voorbeeld 3.6.

VOORBEELD 3.6

Cursussen die voorkennis zijn voor andere cursussen Een cursus kan andere cursussen als verplichte voorkennis eisen. Een voorbeeld is de cursus Databases (DB), die de cursussen Inleiding informatica (II) en Discrete wiskunde (DW) als voorkennis eist. Omgekeerd kan een cursus zelf verplichte voorkennis zijn voor andere cursussen. Zo is Databases zelf voorkennis voor de cursussen Informatiemodellering (IM) en Business intelligence (BI). Aldus krijgen we de volgende vier ‘voorkenniseis-feiten’: “cursus DB eist als voorkennis cursus II” “cursus DB eist als voorkennis cursus DW” “cursus IM eist als voorkennis cursus DB” “cursus SW eist als voorkennis cursus DB” Deze feiten kunnen we niet onderbrengen in de tabel Cursus, omdat een cursuscode in elke der beide ‘cursuscodekolommen’ van het feitenrijtje kennelijk meervoudig kan voorkomen. Er is dan ook een aparte tabel nodig; zie de tabel Voorkenniseis in figuur 3.31, met strokendiagram (figuur a) en voorbeeldpopulatie (figuur b). Merk op dat Cursus en

118


Voorkenniseis op twee manieren een ouder-kindpaar vormen, via elk der twee verwijzingen.

a

Cursus

p code

naam

b

Cursus

examinator°

cd

cursus

Voorkenniseis p voorkennis

code

naam

examinator

II DW DB IM SW

Inleiding informatica Discrete wiskunde Databases Informatiemodelleren Semantic web

BAC DAT COD DAT

Voorkenniseis cursus voorkennis DB DB IM SW

FIGUUR 3.31

II DW DB DB

Veel-veel-associatie tussen Cursus en zichzelf via extra tabel

Omdat Voorkenniseis.cursus en Voorkenniseis.voorkennis geen van beide uniek zijn, zien we een brede primaire sleutel. Deleteregels Het leek ons redelijk dat een cursus die voorkennis is voor één of meer andere cursussen niet zomaar verwijderd kan worden. Vandaar een restricted delete voor de verwijzende sleutel bij Voorkenniseis.cursus. Echter, bij een (voorgenomen) verwijdering van een cursus X ligt het voor de hand ook alle voorkennisfeiten te willen verwijderen van het type “cursus ... is voorkennis voor cursus X”. Vandaar een cascading delete voor de verwijzende sleutel bij Voorkenniseis.voorkennis. 4.4

EEN VEEL-VEEL-ASSOCIATIE TUSSEN EEN TABEL EN EEN ANDERE TABEL

Ook tussen twee verschillende tabellen kan een veel-veel-associatie bestaan. Een mooi voorbeeld is de ‘begeleidingsassociatie’ tussen Cursus en Docent: een cursus kan door meerdere docenten worden begeleid, terwijl omgekeerd een docent meerdere cursussen kan begeleiden. Van beide kanten gezien gaat het om een meervoudig kenmerk. Zo’n ‘meervoud’ (cursussen, docenten) kan noch als Docent-attribuut noch als Cursus-attribuut worden opgeslagen. De oplossing is ook nu een extra tabel, zie tabel Begeleider in figuur 3.32. Elke Begeleider-rij is een combinatie van een docent en een door die docent begeleide cursus.

119

Open Universiteit

Databases

Docent

p acr

naam

p code

Cursus naam

examinator°

cd

Begeleider

p

docent cursus

Veel-veel-associatie tussen Docent en Cursus via extra tabel

FIGUUR 3.32

Deleteregels Wanneer een cursus wordt verwijderd, hoeft die ook niet meer begeleid te worden. Vandaar de cascading delete voor de verwijzing van Begeleider naar Cursus. Voor beide verwijzingen naar Docent geldt echter een restricted delete: een docent mag pas verwijderd worden wanneer hij geen taken meer heeft. 4.5

NIET-SLEUTELVERWIJZINGEN

Sleutels bieden volgens de relationele theorie dé manier om de rijen van een tabel logisch te adresseren. Het logische adres van een rij is de primaire-sleutelwaarde, die men elders kan gebruiken in de vorm van een verwijssleutelwaarde. In de praktijk zijn er ook andere manieren om een rij logisch te adresseren, zoals voorbeeld 3.7 illustreert.

VOORBEELD 3.7

Het strokendiagram van figuur 3.33 toont een structuur met een intervalverwijzing.

p naam

Plaats beginpostcode eindpostcode

p nr FIGUUR 3.33

...

postcode

Constraints: - beginpostcode <= eindpostcode - disjuncte intervallen

Cursist huisnr

Intervalverwijzing

Aan Student zijn twee kolommen toegevoegd: postcode en huisnr. Van de overige kolommen is alleen de primaire sleutel getoond. Er is uitgegaan van de Nederlandse postcodesystematiek. De studentplaatsnaam wordt gevonden via de postcode (als deze is geregistreerd), in een Plaats-tabel met de gestandaardiseerde plaatsnamen en het postcode-interval van elke plaats. We nemen hier aan dat elke plaats

120


precies één ‘eigen’ postcode-interval heeft en één unieke plaatsnaam (te bereiken door aan de naam zo nodig een provinciecode toe te voegen). Ter illustratie geven we in figuur 3.34 een kleine voorbeeldpopulatie.

Plaats naam

beginpostcode eindpostcode

Amsterdam Utrecht

1000AA 3200AA

1199ZZ 3800ZZ

Cursist nr

...

postcode

1 2 3 4 5

... ... ... ... ...

1018KL

FIGUUR 3.34

3345PZ 1045AF 1079LN

Voorbeeldpopulatie bij intervalverwijzing postcodes

De postcode van student 4 ligt tussen die van student 1 en die van student 5 in. Wanneer we weten dat student 1 en student 5 beide in Amsterdam wonen, kunnen we concluderen dat student 4 ook in Amsterdam woont. Verwijzende woonplaatssleutels in de studententabel zouden dus tot redundantie leiden. De gegeven structuur, waarbij de postcodesystematiek in de database zelf wordt opgenomen, is veel efficiënter en vrij van redundantie. Niet-sleutelverwijzing

Een verwijzing die op een andere manier tot stand komt dan via gelijkheid van sleutelwaarden, noemen we een niet-sleutelverwijzing. Meestal gaat het om een intervalverwijzing. In diagrammen geven we een niet-sleutelverwijzing aan met een stippellijn. Merk op dat beginpostcode en eindpostcode in Plaats beide verplicht en uniek zijn, en dus eigenlijk elk een alternatieve sleutel vormen. Het heeft echter niet zoveel zin deze te vermelden, omdat er een veel sterkere regel geldt: de postcode-intervallen zijn disjunct, dat wil zeggen dat twee intervallen niets gemeenschappelijk hebben. Het is deze sterkere regel die maakt dat er werkelijk van een verwijzing sprake kan zijn.

OPGAVE 3.7

Een cursusgroep van de Open School heeft een ‘alarmlijn’, waarmee studenten elkaar dringende boodschappen doorgeven, zoals het uitvallen van een les. De werking is simpel: één persoon (de contactpersoon van de groep) start de lijn en belt één of meer medestudenten. Die bellen op hun beurt ook één of meer anderen, enzovoort. Elke betrokkene, behalve degene die de lijn start, wordt door één ander gebeld. Het belschema staat vast en is aan iedereen bekend, zie voor een voorbeeld figuur 3.35.

121

Open Universiteit

Databases

1. Inge Berk 020-1324356 06-13579864

2. Max Tack 020-4662806 3. Wim Bos 020-4408966

5. Sofie van Esch 020-4662806

4. Iris van Eik 020-1234567

6. Pim Hazelaar 06-31558901

7 . Cees in 't Woud 035-1706201

8. Anna Roos 020-5330762

FIGUUR 3.35

Belschema alarmlijn

Breng de naamgegevens en de ‘wie belt wie?’-gegevens van figuur 3.35 onder in een (volledig genormaliseerde) tabel Student, met kolommen voor voornaam, voorvoegsel en achternaam. Maak een strokendiagram. De telefoonnummers zelf mogen buiten beschouwing blijven, omdat deze al zijn opgenomen in de tabel Communicatieregel van opgave 3.6. De OpenSchool-database

5

Tot slot van deze leereenheid voegen we een deel van de veranderingen aan de Open School-database samen. Ook voegen we er nog een enkel detail aan toe, zie figuur 3.36. We geven de database nu een technische naam: OpenSchool. Docent

p acr

p nr

naam

naam

vervanger°

p code

Student mentor°

Cursus naam uren credits examinator° cd

cd

Begeleider p docent cursus

122

cursus

Voorkenniseis p voorkennis

Inschrijving

p

FIGUUR 3.36

cd

student

cursus

student

cursus p

datum

/cijfer° vrijstelling°

Tentamen volgnr datum cijfer°

Strokendiagram OpenSchool-database


ECTS = European Credit Transfer System

Toelichting − Het hart van het diagram wordt nog steeds gevormd door de vier tabellen Docent, Student, Cursus en Inschrijving. − Tabel Docent heeft er een kolom ‘vervanger’ bijgekregen, met bijbehorende recursieve verwijzing (zie paragraaf 4.2). − Aan Cursus is een kolom uren toegevoegd (voor de studiebelasting in uren) en een kolom credits (voor de zwaarte van een cursus, uitgedrukt in ECTS-credits). Ook is er als ‘meervoudig kind’ de tabel Voorkenniseis bijgekomen, met twee verwijzende sleutels (zie paragraaf 4.3). − Aan Inschrijving is de kindtabel Tentamen toegevoegd, met een verwijzing over een tweekolomssleutel (zie paragraaf 4.1). Tot slot hebben Docent en Cursus een gemeenschappelijke kindtabel Begeleider gekregen (zie paragraaf 4.4). Cijfers en vrijstellingen De tabellen Inschrijving en Tentamen bevatten informatie over cijfers en vrijstellingen. Deze kunnen we het beste toelichten aan de hand van een voorbeeldpopulatie; zie figuur 3.37.

FIGUUR 3.37

123

Voorbeeldpopulatie OpenSchool-database

Open Universiteit

Databases

Een Tentamen-rij wordt ingevoerd zodra een student zich voor dat tentamen heeft ingeschreven. Er is dan nog geen cijfer bekend, vandaar dat cijfer optioneel is. Elk tentamencijfer dat wordt ingevoerd, leidt tot berekening (of herberekening) van het bijbehorende cijfer in Inschrijving.cijfer, als het maximum van de corresponderende tentamencijfers. U kunt dit in de voorbeeldpopulatie nagaan. Studenten kunnen voor een cursus vrijstelling aanvragen. Zodra daarover een beslissing is genomen, leidt dit tot een J (‘ja’) of N (‘nee’) in kolom Inschrijving.vrijstelling. Er geldt de volgende bedrijfsregel, die we later als databaseconstraint kunnen implementeren: Een student mag zich pas inschrijven voor een tentamen als hij voor de voorkennisvakken een voldoende heeft of vrijstelling. Uren en credits

Opmerking: officieel is er een correspondentie tussen de studiebelasting in uren en de waardering van een cursus in ECTS-credits: 1 ECTS = 28 uur. In de tabel zijn de waarden onafhankelijk en gelden zij slechts bij benadering.

SAMENVATTING Introductie

Een relationele database kent behalve een structuurdefinitie ook regels. Er zijn twee soorten regels: − beperkingsregels: deze geven, in de vorm van een gebod of een verbod, aan wat allemaal mag of moet worden ingevuld als populatie − gedragsregels: deze zeggen welke acties onder bepaalde voorwaarden moeten worden ondernomen, ofwel ze houden een actie juist tegen.

Paragraaf 1

De belangrijkste typen beperkingsregels zijn de standaard beperkingsregels, die in elke relationele database voorkomen. Standaard beperkingsregels verplichte-waarderegel voor kolom

Elke rij in die kolom moet een waarde bevatten. Een niet-ingevulde waarde heet een null. Deze regel zegt dus dat null’s niet zijn toegestaan.

uniciteitsregel voor kolom

Elke waarde in die kolom mag maar één keer voorkomen.

uniciteitsregel voor kolomcombinatie

Elke waardencombinatie in die kolomcombinatie mag maar één keer voorkomen, terwijl deze beperking niet geldt voor de afzonderlijke kolommen of ‘smallere’ kolomcombinaties.

kandidaatsleutel

Zo zuinig mogelijk gekozen supersleutel, in de zin dat na het verwijderen van een kolom niet meer aan de supersleuteleis wordt voldaan. Elke tabel heeft minstens één kandidaatsleutel.

primaire sleutel

Kandidaatsleutel die is gekozen om verwijzingen op te baseren.

alternatieve sleutel

Kandidaatsleutel die niet de primaire sleutel is.

124


referentiële-integriteitsregel

Als een kolom (of kolomcombinatie) een verwijssleutel vormt, moet elke waarde (respectievelijk waardencombinatie) voorkomen als primaire-sleutelwaarde in de doeltabel. Volgens deze regel zijn ‘loze verwijzingen’ niet toegestaan.

multipliciteitsregel

Regel die, voor een ouder-kind-tabelcombinatie, een minimum en/of een maximum aangeeft voor: – het aantal kindrijen bij één ouderrij – het aantal ouderrijen bij één kindrij. Deze aantallen worden grafisch genoteerd in een multipliciteitendiagram.

Buiten deze standaard beperkingsregels zijn er de bijzondere beperkingsregels, waarvoor geen standaardnotatie bestaat, en die daarom in tekst aan een structuurdiagram worden toegevoegd. Paragraaf 2

Gedragsregels hebben meestal een specifiek karakter. Er is één standaardtype, dat voor elke verwijzing (expliciet of impliciet) moet worden gespecificeerd: de refererende-actieregels. Refererende-actieregels deleteregels

updateregels

125

Deze specificeren voor de kindtabel wat er met een rij moet gebeuren wanneer wordt gepoogd de bijbehorende ouderrij te verwijderen. Er zijn drie mogelijkheden. restricted delete

verwijderen wordt tegengehouden zolang er nog een kindrij bestaat

cascading delete

de poging heeft tot gevolg dat wordt gepoogd ook alle kindrijen te verwijderen

nullifying delete

de poging heeft tot gevolg dat wordt gepoogd de verwijzende kolomwaarden null te maken

Deze specificeren wat er gebeurt bij een poging tot updaten van een primaire-sleutelwaarde. Er zijn drie mogelijkheden. restricted update

update wordt tegengehouden zolang er nog een of meer kindrijen bestaan met een verwijzing naar die primairesleutelwaarde

cascading update

de poging heeft tot gevolg dat wordt gepoogd ook alle verwijzende kolomwaarden op dezelfde manier te updaten

nullifying update

de poging heeft tot gevolg dat wordt gepoogd de verwijzende kolomwaarden null te maken

Open Universiteit

Databases

Gedragsregels zijn nauw verbonden met transacties. Een transactie is een reeks databaseacties tussen twee commitmomenten (momenten waarop alle wijzigingen definitief worden gemaakt). Een transactie wordt ofwel in zijn geheel, ofwel niet uitgevoerd. Een uniciteitsregel over een kolom of kolomcombinatie houdt een verbod in op het meervoudig voorkomen van een kolomwaarde respectievelijk waardencombinatie. De conventie is om alleen de meest strenge regels te benoemen. Bij grafische weergave door een uniciteitspijl betekent dit dat de pijl zo smal mogelijk getekend wordt. Hierdoor is het onmogelijk dat een uniciteitspijl helemaal over een andere, smallere, uniciteitspijl heen ligt.

Paragraaf 3

Over drie kolommen zijn zeven uniciteitsregels mogelijk: drie over één kolom, drie over twee kolommen en één over alle drie de kolommen. Deze kunnen op allerlei manieren worden gecombineerd. Over vier kolommen is het aantal mogelijke regels veel groter. Een verwijssleutel is meer dan alleen een kolom of kolomcombinatie; het is een verbodsregel. Het verbod in kwestie is een verbod op loze verwijzingen: de referentiële-integriteitsregel. Wanneer verwezen wordt naar een samengestelde primaire sleutel, is de verwijssleutel eveneens samengesteld. Een bijzonder geval van een verwijssleutel is een recursieve verwijzing: een verwijzing naar rijen van de eigen tabel. Een verwijssleutel is gebaseerd op sleutelwaardengelijkheid: die van de verwijssleutelwaarde zelf en die van de primaire sleutel waarnaar wordt verwezen. Een verwijzing kan echter ook op een andere manier tot stand komen. Een voorbeeld van zo’n niet-sleutelverwijzing is een intervalverwijzing.

Paragraaf 4

ZELFTOETS

1

Teken een strokendiagram van de Orderdatabase (zie zelftoets leereenheid 2), met de regels die daarvoor – volgens de gegeven beschrijving – gelden: verplichte-waarderegels (aangegeven door een optionaliteitsmarkering van de niet-verplichte kolommen), primaire sleutels en eventueel alternatieve sleutels. Geef ook aan welke refererende-actieregels (delete- en updateregels) naar uw mening zouden moeten gelden. In de volgende opgaven wordt de Orderdatabase stap voor stap gewijzigd en uitgebreid. Raadpleeg hierbij zéker de uitwerkingen, want die geven de juiste uitgangssituatie voor volgende leereenheden.

2

Elke orderregel van de Orderdatabase geven we een volgnummer, dat uniek is per order (zie figuur 3.38).

126


Orderregel order_ volgnr 5773 5773 5773 5774 5774 5793 5793

FIGUUR 3.38

1 2 3 1 2 1 2

artikel

aantal

bedrag

351 107 449 351 180 449 180

2 10 3 2 1 1 2

31.60 22.00 41.50 31.60 26.00 13.50 52.00

Tabel Orderregel

Welke consequentie heeft dit voor het strokendiagram? 3

Als onderdeel van een nieuwe marketingstrategie wordt voor klanten de mogelijkheid geopend andere klanten aan te brengen. Hieraan is een bonusregeling gekoppeld (die hier verder buiten beschouwing blijft). Welke wijziging moet de databasestructuur ondergaan om het aanbrengen van klanten te kunnen administreren? Teken die wijziging in het strokendiagram. Teken voor het gewijzigde deel ook een multipliciteitendiagram.

4

De directie besluit tot introductie van een klachtregistratiesysteem. Klachten zullen worden geregistreerd op het niveau van orderregels: elke klacht zal dus betrekking hebben op één orderregel, maar per orderregel kunnen door de klant meerdere klachten worden ingediend. Elke klacht krijgt een uniek klachtnummer. Tevens wordt geregistreerd of de klacht is afgehandeld. Geef in het strokendiagram aan hoe de databasestructuur moet worden uitgebreid.

5

Voor afname van grotere hoeveelheden van één artikel geldt een systeem van kwantumkorting. Bij afname van 10 t/m 49 stuks bijvoorbeeld geldt een korting van 5%. Het kortingspercentage is onafhankelijk van het artikel. Teken de noodzakelijke uitbreiding van het strokendiagram.

6

Een artikel kan behoren tot een artikelgroep. Artikelgroepen worden uniek geïdentificeerd door een code en tevens door een omschrijving. Verder moeten vanaf nu in plaats van de ‘prijs’ een aparte verkoopprijs en inkoopprijs worden geregistreerd. Tot slot moet van elk artikel de voorraad moet worden bijgehouden. Teken het bij deze aanpassingen horende fragment van het strokendiagram.

7

Teken een strokendiagram waarin alle ingrepen van de voorgaande opgaven zijn verwerkt. Opmerking: het resultaat is een logisch relationeel model. Het implementatiemodel kan hiervan nog gaan afwijken. Een kleine afwijking is voor ons van belang: ORDER (in hoofdletters of kleine letters) is een zogenaamd gereserveerd SQL-woord (een woord dat tot de vaste SQLtaalschat behoort). In sommige dialecten, waaronder Firebird, is het om die reden niet bruikbaar als tabelnaam of kolomnaam. Voeg om die reden aan de tabelnaam Order of kolomnaam order een underscore toe: Order_ respectievelijk order_.

127

Open Universiteit

Databases

TERUGKOPPELING 1

Uitwerking van de opgaven

3.1

a Gerecht en Eenheid b Ingredient c Ingredient

3.2

Omdat Boek.rubriek optioneel is, hoort bij een boek ofwel geen ofwel precies één rubriek. Dus: 0 of 1. Diagram c valt daardoor af. Omdat bij het strokendiagram geen extra regel gegeven is, is het toegestaan rubrieken op te nemen zonder dat in de database ook een boek van die rubriek is opgenomen. Dus: bij een rubriek horen 0 of meer (willekeurig veel) boeken. Ook diagram a valt daardoor af, zodat alleen diagram b in overeenstemming is met het multipliciteitendiagram.

3.3

Boek.rubriek is nu verplicht, zodat bij elk boek precies één rubriek hoort. Alleen diagram c klopt met het strokendiagram.

3.4

De kwalificaties ‘ouder’ en ‘kind’ gelden altijd relatief ten opzichte van een verwijzing. In figuur3.13 zijn Filiaal en Werknemer ouder respectievelijk kind ten opzichte van de verwijzing ‘is werkzaam bij’. Ten opzichte van de verwijzing ‘heeft als regiomanager’ is juist Filiaal het kind en Werknemer de ouder. Onze tekenconventie om de ‘ouder’ altijd boven het ‘kind’ te tekenen, kunnen we in dit soort situaties natuurlijk niet volhouden. Opmerking: een realistische database voor het grootwinkelbedrijf zou ten minste ook een tabel Regio bevatten, waardoor deze structuur niet precies zo zou voorkomen.

3.5

a Foutmelding vanwege (een dreigende overtreding van) de referentiële-integriteitsregel: vanille komt niet voor in Product.naam. b Foutmelding vanwege de primaire sleutel van Ingredient: de combinatie (‘Coupe Kiwano’, ‘suiker’) bestaat al. c Foutmelding vanwege de referentiële-integriteitsregel: ‘koffielepel’ bestaat niet in Eenheid.naam. d De rij wordt toegevoegd (het rdbms ‘weet’ niet, zoals wij, dat slagroom geen eenheid is). e Foutmelding vanwege de referentiële-integriteitsregel en de restricted delete bij de verwijzing van Ingredient naar Product. f De rij wordt verwijderd. g De rij wordt verwijderd, samen met alle bijbehorende Ingredientrijen, omdat voor de verwijzing van Ingredient naar Gerecht een cascading delete geldt. h De verandering wordt doorgevoerd, niet alleen in Product, maar ook in alle voorkomens van ‘kiwano’ in de kolom Ingredient.product, als gevolg van de cascading update die geldt voor de verwijzing van Ingredient naar Product. i Foutmelding vanwege de referentiële-integriteitsregel: ‘pond’ is geen bestaande eenheid. j Foutmelding omdat Gerecht.bereidingstijd verplicht is.

128


3.6

Er is één brede uniciteitsregel(zie figuur 3.39).

p

Communicatieregel

cursist communicatiemiddel

adres

FIGUUR 3.39

Deze houdt in dat smallere regels niet gelden. Er is namelijk geen reden voor bepaalde beperkingen, zoals de volgende voorbeeldpopulatie illustreert (zie figuur 3.40).

cursist 1 1 1 2

Communicatieregel adres

communicatiemiddel telefoon telefoon fax telefoon

020-1234567 06-13579864 020-1234567 020-1234567

FIGUUR 3.40

Toelichting De volgende beweringen geven aan waarom de drie mogelijke uniciteitsregels over twee kolommen niet gelden: – een student kan twee verschillende telefoonnummers hebben – een student kan een telefoonnummer delen met een andere student – een student kan één nummer hebben voor zowel telefoon als fax. 3.7

De wie-belt-wie-associatie is ‘één-op-veel’ en kan worden gerealiseerd met een recursieve verwijzing van Student naar zichzelf. Zie het strokendiagram van figuur 3.41a en de voorbeeldpopulatie van figuur 3.41b.

a

p nr

Student voornaam voorvoegsel° achternaam student_door_wie_gebeld°

nr

voornaam voorvoegsel achternaam student_door_wie_gebeld

1 2 3 4 5 6 7 8

Inge Max Wim Iris Sofie Pim Cees Anna

b

Student

FIGUUR 3.41

129

van van in 't

Berk Tack Bos Eik Esch Hazelaar Woud Roos

1 2 2 1 5 5 6

Open Universiteit

Databases

2 1

Uitwerking van de zelftoets

Zie figuur 3.42.

p nr

Klant naam

p nr

klant

p cd

nr

p order

artikel

datum

Order /totaalbedrag°

Artikel omschrijving verkoopprijs

a

Orderregel i aantal /bedrag°

FIGUUR 3.42

Voor de verwijzing van Orderregel naar Order hebben we een cascading delete gespecificeerd: omdat een order één is met zijn orderregels impliceert het (willen) verwijderen van een order het (willen) verwijderen van al zijn orderregels. 2

Orderregel krijgt twee, overlappende uniciteitsregels (zie figuur 3.43).

a

p order

volgnr

artikel

Orderregel i aantal /bedrag°

FIGUUR 3.43

Nu kunnen we kiezen welke we als primaire sleutel nemen, de ander wordt dan vanzelf alternatieve sleutel. De combinatie (order, volgnr) is onze favoriet voor de primaire sleutel. De reden is dat het bestaansrecht van een volgnummer helemaal is gelegen in de unieke identificatie van een orderregel binnen een order. De uniciteitsregel over (order, volgnr) zal daarom ook nooit vervallen. Daarentegen is best denkbaar dat geen uniciteitsregel geldt over (order, artikelnr): we zouden immers kunnen toelaten dat een order meerdere orderregels bevat over hetzelfde artikel. 3

Om het aanbrengen van klanten door klanten te kunnen administreren is een recursieve verwijzing nodig van Klant naar Klant. Figuur 3.44 geeft een strokendiagram (figuur a) en een multipliciteitendiagram (figuur b). a

b p nr

FIGUUR 3.44

130

naam

Klant aanbrenger°

aanbrenger

Klant


Als constraint zou nog toegevoegd kunnen worden: ‘een klant mag niet zichzelf aanbrengen’. 4

Zie figuur 3.45.

p order

a volgnr

p nr

order

volgnr

artikel

aantal

Orderregel i /bedrag°

Klacht behandeld

FIGUUR 3.45

5

Een ontwerpvraag is hier: als kortingspercentages veranderen, moeten dan de oude kortingspercentages (gebruikt voor bestaande orderregels) nog bekend blijven? Zo ja, dan is het gewenst Orderregel een extra kolom korting te geven, die ‘on insert’ automatisch wordt gevuld en daarna onveranderbaar is. We zullen van deze mogelijkheid afzien, het strokendiagram wordt dan als volgt uitgebreid (zie figuur 3.46).

Kortingsinterval beginaantal eindaantal

p nr

korting

a

volgnr

artikel

aantal

Regels: - beginaantal <= eindaantal - intervallen aaneensluitend vanaf 0

Orderregel /bedrag°

i

FIGUUR 3.46

Opmerkingen − Merk op dat voor Kortingsinterval geen sleutels zijn gedefinieerd. In plaats daarvan is een regel opgenomen die garandeert dat de intervalverwijzingen voor alle niet-negatieve aantallen (tot aan een zeker maximum) correct werken. Als die (zeer sterke) regel wordt afgedwongen − hoe, dat zien we later wel − heeft het geen zin om Kortingsinterval ook nog te controleren op een primaire sleutel voor beginaantal of een alternatieve sleutel voor eindaantal. Primaire en alternatieve sleutels zijn hier als regel te zwak. Alleen de sterkste regels hoeven te worden bewaakt. − Als gevolg van deze wijziging moet de regel voor de automatische berekening van de orderregelbedragen worden aangepast.

131

Open Universiteit

Databases

6

Zie figuur 3.47. Artikelgroep a omschrijving

p code p nr

a omschrijving

verkoopprijs inkoopprijs

Artikel voorraad

Fragment strokendiagram met Artikel en Artikelgroep

FIGUUR 3.47

7

artikelgroep°

Zie figuur 3.48. p nr

p nr

naam

Order_ klant p nr

datum /totaalbedrag°

p code

Artikelgroep a omschrijving

Artikel a omschrijving artikelgroep° verkoopprijs inkoopprijs voorraad

cd

Kortingsinterval beginaantal eindaantal° korting

a

p order_ volgnr

p nr

Klant aanbrenger°

artikel

Orderregel i aantal / bedrag°

Regels Kortingsinterval en Orderregel: - beginaantal <= eindaantal - intervallen aaneensluitend vanaf 0 - elk orderregelaantal binnen interval

Klacht order_ volgnr behandeld

FIGUUR 3.48

Volledig strokendiagram Orderdatabase

Zie de bijlage achterin dit deel voor een voorbeeldpopulatie.

132

Relationele databases: regels

Recommend Documents