Relationele databases [1, 1 ed.] 9789492231499, 9789492231741 [PDF]

Zitiervorschau

Inhoud

Introductie tot de cursus 1 2

3

4 5

Plaats en functie van de cursus 7 Inhoud van de cursus 7 2.1 Leerdoelen 7 2.2 Opbouw van de cursus 8 2.3 Leermiddelen 9 Aanwijzingen voor het bestuderen van de cursus 10 3.1 Opbouw van een leereenheid 10 3.2 Installeren van de software 11 3.3 Literatuur en relevante websites 11 Cursussite en studiebegeleiding 11 Tentaminering 12

6

Introductie tot de cursus

Voordat u begint met het bestuderen van de cursus Relationele databases, willen wij u in deze introductie informeren over de bedoeling van de cursus, de opzet van het cursusmateriaal en de werkwijze die wij van u verwachten. 1

Plaats en functie van de cursus

De cursus Relationele databases is een cursus in het begin van de bacheloropleidingen Informatica en Informatiekunde, met een studielast van 140 uur (5 EC). Doelgroepen

De cursus is een verplicht onderdeel van alle informaticadiplomaprogramma’s. Verder is deze cursus geschikt als losse cursus voor diegenen die een degelijke introductie willen op het gebied van databases, of die praktische vaardigheden willen opdoen in het werken met relationele databases en SQL.

Voorkennis

Deze cursus vereist als noodzakelijke voorkennis twee leereenheden van Inleiding informatica (IB0102): leereenheid 11 ‘Relationele databases: structuur’ en leereenheid 12 ‘Relationele databases: regels’. Als u niet eerst Inleiding informatica hebt gevolgd, kunt u deze twee leereenheden downloaden van de cursussite van Relationele databases (zie paragraaf 4). Het bestuderen hiervan is niet inbegrepen in de studielast. 2

Inhoud van de cursus

2.1

LEERDOELEN

De leerdoelen omschrijven welke kennis, inzichten en vaardigheden u zich door het bestuderen van de cursus zou moeten eigen maken. Na het bestuderen van deze cursus wordt verwacht dat u – gegeven een beschrijving van een te modelleren situatie kunt beredeneren of een gegeven relationele structuur een goede structuur is, en deze kunt verbeteren (normaliseren) indien nodig en wenselijk – vaardig bent met SQL (vooral DML en DDL), en in het bijzonder de volgende SQL-elementen correct kunt gebruiken: inner join, left outer join, count(…), group by, having, (al dan niet gecorreleerde) subselects en views – SQL-problemen systematisch aanpakt – weet wat de taak van de optimizer is en – in grote lijnen – hoe die deze taak uitvoert, en dit kunt uitleggen aan de hand van een queryplan en indexen – de beginselen van concurrency kent, en weet wat transacties zijn, waarvoor ze nodig zijn, hoe ze elkaar in de weg kunnen zitten en wat u daartegen kunt doen

– weet waarvoor exceptions, stored procedures en triggers gebruikt kunnen worden, en eenvoudige triggers en stored procedures kunt schrijven en testen – inzicht hebt in relationele metadata (de data dictionary). Veel van de praktische oefeningen waarmee aan deze leerdoelen wordt gewerkt, worden uitgevoerd met behulp van het relationeel databasemanagementsysteem Firebird. Het SQL-dialect van Firebird sluit vrij nauw aan bij de ANSI/ISO-standaard SQL3. De taal SQL heeft een belangrijk aandeel in deze cursus, en zeker niet alleen om ermee te leren werken: het dient ook als ‘kapstok’ om essentiële aspecten van relationele databases (en van databases in het algemeen) aan op te hangen. 2.2

OPBOUW VAN DE CURSUS

De cursus bestaat uit dertien leereenheden, gegroepeerd in vijf blokken, die verdeeld zijn over twee cursusdelen (de cursusboeken). Blok 1 ‘Verkenning’ 1 leereenheid

Blok 1 ‘Verkenning’ omvat leereenheid 1 ‘Inleiding’, waarin een overzicht wordt gegeven van het vakgebied ‘relationele databases’. De onderwerpen van de volgende blokken komen hierin in vogelvlucht aan de orde, maar ook wordt juist aandacht geschonken aan onderwerpen die verderop niet worden behandeld, zoals databasearchitecturen.

Blok 2 ‘Theorie’ 2 leereenheden

Blok 2 ‘Theorie’ omvat twee leereenheden. Leereenheid 2 ‘Nulls, strings en logica’ en leereenheid 3 ‘Normalisatie’ behandelen relationele databases vanuit een meer theoretisch perspectief, hoewel hiermee juist ook licht wordt geworpen op een aantal praktische kwesties. Onder meer worden behandeld: nulls (‘ontbrekende waarden’), driewaardige logica en normalisatie (relationele structuurtransformaties).

Blok 3 ‘Bevragen’ 4 leereenheden

In blok 3 ‘Bevragen’ wordt in vier leereenheden de relationele theorie van blok 2 uitgediept en praktisch toegepast bij het leren van de DML-subtaal (Data Manipulation Language) van SQL. Leereenheid 4 ‘Informatie uit één tabel’, leereenheid 5 ‘Informatie uit meerdere tabellen: joins’, leereenheid 6 ‘Statistische informatie’ en leereenheid 7 ‘Subselects en views’ gaan over het bevragen van een relationele database met het SQL-select-statement.

Blok 4 ‘Wijzigen en beheren’ 2 leereenheden

Blok 4 ‘Wijzigen en beheren’ bevat twee leereenheden. – Leereenheid 8 ‘Wijzigen van een database-inhoud’ behandelt het wijzigen van de inhoud van een relationele database met de SQLcommando’s insert, update en delete. – Leereenheid 9 ‘Definitie van gegevensstructuren’ gaat over het aanmaken of wijzigen van databasestructuren met de DDL-subtaal (Data Definition Language) van SQL.

Blok 5 ‘Verdieping’ 4 leereenheden

Blok 5 ‘Verdieping’ bevat vier leereenheden over uiteenlopende onderwerpen:  Leereenheid 10 ‘Query-optimalisatie’ gaat over de vraag hoe het gebruik van tijd en geheugen binnen de perken kan worden gehouden.  Leereenheid 11 ‘Transacties en concurrency’ gaat over samengestelde acties die als één geheel door een database worden afgehandeld. Voor een multi-useromgeving wordt behandeld hoe wordt gezorgd dat gebruikers elkaar niet in de weg zitten.

 In leereenheid 12 ‘Triggers en stored procedures’ wordt geïllustreerd hoe databaseregels waarvoor SQL ontoereikend is, met programmaatjes in een ‘triggertaal’ kunnen worden afgedwongen. – In leereenheid 13 ‘De data dictionary’ wordt getoond hoe structuurinformatie over een relationele database (metagegevens) zelf ook weer relationeel wordt opgeslagen.

Geschatte studielast

Practicum

Een belangrijk onderdeel van de cursus is het practicum, waarin een nieuwe voorbeelddatabase wordt geïntroduceerd, die wordt gebruikt om met alle onderwerpen uit de cursus extra te oefenen. In het rooster op de cursussite vindt u aanwijzingen over wanneer u welk deel van het practicum moet uitvoeren.

Bijlagen voorbeelddatabases

Elk cursusdeel bevat achterin een bijlage met populatiediagrammen van de in dat deel gebruikte voorbeelddatabases.

Tentamenvoorbereiding

Voor de tentamenvoorbereiding kunt u gebruikmaken van een eindtoets en enkele voorbeeldtentamens op de cursussite. Deze bestrijken zeker niet alle mogelijke tentamenonderwerpen, maar geven een goede indruk van aard en niveau van mogelijke vragen. Bij elke leereenheid staat de geschatte studielast vermeld. Daarmee bedoelen we de tijd die nodig is om die leereenheid eenmaal grondig in zijn geheel te bestuderen en daarbij alle opgaven uit te werken en te controleren, inclusief de zelftoets. De totale geschatte studielast van de dertien leereenheden komt op 78 uur. Voor de acht online bijeenkomsten (zie paragraaf 4) hebt u ongeveer 16 uur nodig, voor het practicum 10 uur, en voor het maken van het tentamen 3 uur. Dat brengt het totaal voor alle essentiële onderdelen van de cursus samen op 107 uur. Dan blijven nog over: oriëntatie op de cursus en de cursussite, deelnemen aan de discussiegroep (8 uur), installatie en verkennen VirtualBox met SQL-omgeving (5 uur), uitwerken en controleren eindtoets en voorbeeldtentamen (6 uur) en tentamenvoorbereiding (14 uur). 2.3

LEERMIDDELEN

Het cursusmateriaal bestaat uit een set van twee cursusboeken, en – via de cursussite – cursussoftware en aanvullend materiaal zoals de eindtoets en voorbeeldtentamens. Cursusboeken De cursusboeken omvatten in principe de volledige tentamenstof. Raadpleeg de cursussite voor eventuele facultatieve onderdelen of voor aanvullingen. Software De software bestaat uit een VirtualBox met daarin de open source SQLdatabaseserver Firebird, een SQL-omgeving, en alle voorbeelddatabases uit de cursus inclusief hun create- en insert-scripts. Hierdoor is het mogelijk alle code uit de cursus snel en eenvoudig uit te proberen en aan te passen. Er kan naar hartenlust geëxperimenteerd worden, want de voorbeelddatabases zijn eenvoudig weer in hun oorspronkelijke staat terug te brengen.

3

Aanwijzingen voor het bestuderen van de cursus

3.1

OPBOUW VAN EEN LEEREENHEID

Leereenheid

Een blok is verdeeld in leereenheden. Dit zijn afgeronde delen van de stof die u meestal in een of twee dagdelen kunt bestuderen. Elke leereenheid wordt voorafgegaan door een inhoudsopgave en bestaat verder uit een introductie, een leerkern, een zelftoets en een terugkoppeling.

Introductie Leerdoelen

De introductie van een leereenheid geeft kort aan wat de inhoud van de leereenheid is en schept een kader waarin de leerinhouden beter begrepen kunnen worden. In de introductie vindt u de leerdoelen. Zij stellen u in staat na te gaan welke kennis, inzichten en vaardigheden u zich eigen moet maken door bestudering van de leereenheid.

Leerkern

De leerkern van een leereenheid bevat de feitelijke leerstof.

Opgave

Op bepaalde plaatsen in de leerkern treft u opgaven aan. Werk bij voorkeur elke opgave uit tijdens het bestuderen van de leereenheid. Vergelijk uw antwoord met het antwoord in de ‘terugkoppeling’ aan het eind van de leereenheid. Probeer de opgaven zoveel mogelijk op eigen kracht te maken. Blijf echter nooit te lang ‘ploeteren’ en raadpleeg tijdig de uitwerkingen. Markeer lastige opgaven om deze in een later stadium alsnog zelfstandig te maken.

Samenvatting

Direct na de leerkern staat een samenvatting waarin de belangrijkste zaken uit de leerkern nog eens worden opgesomd.

Zelftoets

De zelftoets van een leereenheid is oorspronkelijk bedoeld om u te helpen beoordelen of u, na bestudering van de leerkern, de leerdoelen inderdaad hebt bereikt. In deze cursus werkt het echter iets anders. In bijna alle zelftoetsen in deze cursus gebruiken we de voorbeelddatabase ‘Orderdatabase’. Over sommige onderwerpen zijn veel vragen te stellen aan de hand van de Orderdatabase, over andere onderwerpen minder. Ook zijn er onderwerpen die juist het best aan de hand van de Orderdatabase kunnen worden uitgelegd; daar ‘misbruiken’ we de zelftoets soms dan ook voor. In het algemeen geldt voor deze cursus: de grootte van een zelftoets zegt niet noodzakelijk iets over het belang of de moeilijkheidsgraad van het onderwerp. Het soort vragen en de moeilijkheidsgraad van de vragen zijn wel representatief voor wat we uiteindelijk van u verwachten.

Terugkoppeling

De terugkoppeling bevat uitwerkingen van de opgaven en van de zelftoets. Twijfel niet te snel aan uw eigen uitwerking: een opgave heeft vaak meerdere goede uitwerkingen. U kunt eenvoudig in de SQL-omgeving nagaan of uw uitwerking onder alle omstandigheden hetzelfde resultaat geeft als de standaarduitwerking.

Kernbegrippen

Elke pagina heeft aan de linkerkant twee margekolommen. In de meest linkse kolom treft u kernbegrippen aan. Deze kolom maakt het gemakkelijk om bepaalde tekstonderdelen snel terug te vinden.

Studeeraanwijzingen

Dit is een voorbeeld van een studeeraanwijzing.

Grijsteksten

In de tweede margekolom staan korte studeeraanwijzingen. Dat kunnen toelichtingen zijn, aanwijzingen hoe de tekst gelezen moet worden of verwijzingen. Sommige teksten worden afgedrukt op een grijze achtergrond. Veelal bevatten deze een inhoudelijk ‘terzijde’ of een aanvulling op de hoofdtekst. 3.2

INSTALLEREN VAN DE SOFTWARE

Voor het practicum moet u beschikken over een computer waarop u de VirtualBox kunt installeren. Voor communicatie met medestudenten en docenten en voor het downloaden van de software hebt u een internetverbinding nodig. De software kunt u downloaden via de cursussite. Ook vindt u daar een handleiding voor het gebruik van de SQL-omgeving. Veel oefenen!

Het is van groot belang om veel in de SQL-omgeving te oefenen. Niet alleen zult u daardoor makkelijker syntactisch correcte query’s schrijven, maar het helpt u ook om te begrijpen hoe een query precies werkt, en waarom hij misschien niet doet wat u verwacht. U kunt een query bijvoorbeeld ‘in stappen’ uitvoeren, door sommige regels in commentaar te zetten en die dan stuk voor stuk uit het commentaar te halen. 3.3

LITERATUUR EN RELEVANTE WEBSITES

Aanbevolen literatuur We noemen één standaardwerk uit het overweldigende aanbod: – Elmasri, R.E. en S.B. Navathe, Fundamentals of database systems. Benjamin/Cummings, 6e druk, 2011. Voor eventuele websites en verdere bronnen: zie de cursussite. 4

Cursussite

Begeleiding

Cursussite en studiebegeleiding

Cursussite Na inschrijving krijgt u toegang tot de cursussite in yOUlearn. Hier vindt u de software, errata, een discussiegroep, informatie over tentamens en aanmelden, de eindtoets en een voorbeeldtentamen, het rooster van de online begeleiding en eventueel nieuws daarover, en een aanbevolen weekplanning voor het bestuderen van de cursus. Voor toegang tot yOUlearn moet u beschikken over een gebruikersnaam (uw studentnummer) en een wachtwoord. Het wachtwoord en het juiste webadres worden u verstrekt bij uw eerste aanmelding voor een cursus die van yOUlearn gebruikmaakt. Planning De cursus wordt eenmaal per jaar begeleid, in kwartiel 3 (periode februari-april). Dit gebeurt in de vorm van acht online bijeenkomsten, die zijn opgenomen in een rooster dat u in tien weken (de looptijd van een kwartiel) door de gehele cursus loodst, met in de elfde week het tentamen. Deze uitgebreide planning vindt u op de cursussite. De begeleidingsdata worden ook bekendgemaakt via de OU-website en in (e-)Modulair, het tijdschrift voor studenten van de Open Universiteit.

5

Schriftelijk tentamen Op tijd aanmelden voor tentamen

Tentaminering

De cursus wordt afgesloten met een geheim regulier schriftelijk tentamen van 3 uur, dat bestaat uit 8 à 12 open vragen. De tentamendata vindt u op de cursussite, in de studiegids of in (e-)Modulair. Zorg ervoor dat u zich tijdig aanmeldt voor het tentamen waaraan u wilt deelnemen! U mag de ‘schone’ cursusboeken gebruiken tijdens het tentamen (voor de precieze betekenis van ‘schoon’ verwijzen we u naar de cursussite – kort gezegd betekent dit dat u de cursustekst wel mag onderstrepen en markeren, maar er niets bij mag schrijven). Dit betekent dat de gestelde vragen niet louter reproduceerbare kennis zullen toetsen, maar dat van u verlangd wordt dat u de opgedane kennis, inzichten en vaardigheden kunt toepassen op een nieuwe casus. Zoals eerder gezegd vindt u op de cursussite een eindtoets en een voorbeeldtentamen, om u een idee te geven van wat we van u verwachten.

Blok 1

Verkenning

Inhoud leereenheid 1

Relationele databases: inleiding 15

Introductie Leerkern 1

17

Databasesystemen en hun gebruikers 17 Wat is een database? 17 Voorbeelddatabase: OpenSchool 18 Databasemanagementsysteem 21 Applicaties en gebruikers 22 Databasetalen 24 Transacties 29 Transactionele systemen 30 Datawarehouses en OLAP 31 Voordelen en beperkingen van relationele databasesystemen Architecturen van databasesystemen 33 2.1 Servers, clients en tiers 33 2.2 1-tiersystemen 34 2.3 2-tiersystemen 34 2.4 3-tiersystemen 35 2.5 Databases en internet 36 1.1 1.2 1.3 1.4 1.5 1.6 1.7 1.8 1.9

2

Samenvatting Zelftoets

37

38

Terugkoppeling

41

Uitwerking van de zelftoets

41

14

31

Leereenheid 1

Relationele databases: inleiding

INTRODUCTIE

Wie schrijft die blijft. Van oudsher vinden mensen het belangrijk dingen vast te leggen. Figuur 1.1 toont een archaïsch, in spijkerschrift beschreven prisma uit het oude Mesopotamië, de Sumerische Koningslijst. Deze lijst is waarschijnlijk kort voor 2000 v.Chr. opgesteld en bevat een opsomming van koningen, dynastieën en hun regeringsduur.

FIGUUR 1.1

De Sumerische Koningslijst (Ashmolean Museum, Oxford)

In de loop van de tijd zijn de mogelijkheden om dingen vast te leggen gestaag toegenomen. De uitvinding van de boekdrukkunst is een mijlpaal. We fotograferen, filmen en leggen geluid vast op cd’s. We slaan enorme hoeveelheden gegevens op en archief na archief wordt gevuld. Maar vullen alleen is niet genoeg. Het is belangrijk archieven of gegevensbestanden goed te structureren, zodat het relatief gemakkelijk is ze te raadplegen. Deze cursus gaat over relationele databases. Dat zijn gegevensverzamelingen waarin informatie is opgeslagen in tabellen. In de cursus Inleiding Informatica (IB0102) hebt u de structuur en regels van een relationele database bestudeerd. U weet dus al waarom we kiezen voor bepaalde tabellen, hoe de tabellen in een database met elkaar samenhangen, en u kunt omgaan met allerlei soorten regels die in een relationele database gelden, zoals uniciteitsregels, referentiële integriteit en refererendeactieregels. Die structuur en regels vormen samen het hart van een relationele database: daar zit de essentie van een databasesysteem, namelijk de gegevens waar het om gaat (de database). Maar een databasesysteem is meer dan dat: er is een soort schil om de database heen gebouwd, die ervoor zorgt dat er makkelijk met de gegevens gewerkt kan worden, zowel door eindgebruikers (zoals de klant van een webwinkel, die wil weten of een bepaald artikel nog op voorraad is) als door de beheerder van de database. In deze leereenheid kijken we naar wat er allemaal komt kijken bij een (relationeel) databasesysteem. De rest van deze cursus gebruiken we om ons te verdiepen in het omgaan met de gegevens in een relationele database: hoe krijgen we er de informatie uit die we nodig hebben, hoe vullen we de database met gegevens, hoe onderhouden we die? LEERDOELEN

Na het bestuderen van deze leereenheid wordt verwacht dat u – de begrippen (relationele) database, (r)dbms en (relationeel) databasesysteem kunt toelichten – de vier belangrijkste taken van een dbms kunt noemen – de soorten gebruikers van databasesystemen kunt kenschetsen – de functies van de DML, de DDL, de DAL en de DCL kunt noemen – kunt aangeven wat een datawarehouse is en wat wordt bedoeld met de begrippen OLTP en OLAP – kunt ingaan op de begrippen 1-tier-, 2-tier- en 3-tiersysteem en clientserver. Deze leereenheid heeft een studielast van 6 uur. Belangrijk

Dit is voornamelijk een leesleereenheid, bedoeld om u een gevoel te geven voor de context waarin databases worden gebruikt. Er staan daarom geen opgaven in de leerkern. Er is echter wel een zelftoets, die we gebruiken om:  een voorbeelddatabase te (her)introduceren die we in de cursus Inleiding informatica opgebouwd hebben en die we in deze cursus Relationele databases in vrijwel iedere zelftoets weer zullen gebruiken: de Orderdatabase

 de structuur en regels van een relationele database weer in de vingers te krijgen. Ook worden in de zelftoets en de terugkoppeling daarvan nog enkele wetenswaardigheden en afspraken geïntroduceerd, omdat de Orderdatabase zich goed leent voor die onderwerpen.

LEERKERN 1

Databasesystemen en hun gebruikers

In ons dagelijks leven komen we op allerlei manieren in aanraking met databases. Zo zijn de boeken in een bibliotheek en de lezers van die boeken geregistreerd in een database. Een moderne supermarkt houdt de voorraden bij met behulp van een database, onder andere op basis van de producten die de kassa’s passeren. Allerlei persoonlijke gegevens zijn opgeslagen in de databases van belastingdiensten, energiemaatschappijen, verzekeringsmaatschappijen, enzovoort. Maar wat is een database nu precies, wie werken ermee en hoe gaat dat? Laten we beginnen met de eerste vraag. 1.1

WAT IS EEN DATABASE?

Er zijn allerlei definities van het begrip database in omloop. Wij zullen de volgende, compacte definitie hanteren. Database

Relationele database

Definitie

Een database is een verzameling gegevens die voor langere tijd zijn opgeslagen en waaruit een computerprogramma (snel) voor verschillende toepassingen de gewenste gegevens kan selecteren of afleiden. In de definitie van een database is niet vastgelegd hoe de gegevens in een database zijn opgeslagen en hoe die gegevens gerepresenteerd kunnen worden. Ongeacht de technische details van de opslag moeten gebruikers kunnen werken met een overzichtelijk beeld van de gegevens. In relationele databases bijvoorbeeld is dat het beeld van ‘opslag in tabellen’. Die tabellen worden ook wel relaties genoemd, vandaar de naam ‘relationele database’. Voor veel toepassingen is die tabelstructuur erg geschikt, vandaar dat momenteel de meeste databases relationeel zijn. Grafisch wordt een database vaak als een ‘koekblik’ afgebeeld, zie figuur 1.2. In Inleiding informatica hebben we de inhoud van ‘relationele koekblikken’ al enigszins in detail bekeken. Later in deze cursus zal blijken dat er nog meer in en achter zit dan alleen de tabellen en hun verbanden en regels zoals we die nu kennen.

FIGUUR 1.2

Grafische weergave van database

1.2

VOORBEELDDATABASE: OPENSCHOOL

In de cursus Inleiding informatica hebt u al kennisgemaakt met voorbeelden van relationele databases. Een daarvan, de Open Schooldatabase, gebruiken we door deze hele cursus heen ook, maar dan in een uitgebreide versie. Daarom geven we hier de volledige beschrijving van die uitgebreide database, en herhalen we aan de hand daarvan meteen de belangrijkste kenmerken (structuur en regels) van een relationele database in het algemeen. In leereenheid 3 ‘Normalisatie’ gaan we dan nog wat dieper in op de structuur. Strokendiagram Populatiediagram

VOORBEELD 1.1: relationele database van Open School

Figuur 1.3 toont het strokendiagram van een relationele voorbeelddatabase, en in figuur 1.3 ziet u een bijbehorend populatiediagram. Deze database is ontworpen ten behoeve van de informatievoorziening van een scholingsinstituut, de Open School.

FIGUUR 1.3

Identificatie Uniek

Verwijzing

Strokendiagram OpenSchool-database

Toelichting  De zeven tabellen horen bij zeven ‘soorten van dingen’ die in verband met de informatievoorziening van de Open School van belang worden geacht.  Ieder ‘ding’ van een bepaalde ‘soort’ moet op de een of andere manier te identificeren zijn. Zo heeft iedere docent een uniek acroniem (afkorting: acr), ofwel: bij één acroniem hoort precies één docent.  Een tabel is een verzameling rijen (zie figuur 1.4), waarbij we verzameling in de wiskundige betekenis bedoelen. Dat wil zeggen dat een tabel niet twee dezelfde rijen kan bevatten, en dat de volgorde van de rijen in een tabel er niet toe doet.  De tabellen staan niet los van elkaar. Zo kan bij elke rij op één na van de tabel Student precies één corresponderende Docent-rij worden gevonden, via gegevens in de mentorkolom van Student en die in de acr-kolom van Docent. We spreken van een verwijzing, aangegeven met een (verticale) pijl. Specifieker: een verwijzing via gelijkheid van waarden.

Referentiële integriteit Beperkingsregel Ouder en kind Tekenconventie: ouder boven kind

Bij elke verwijzing geldt: als er aan de kant waar de pijl vandaan komt een waarde is ingevuld, dan moet die waarde voorkomen aan de kant waar de pijl naartoe wijst. Dit heet referentiële integriteit, en is een fundamentele beperkingsregel van iedere relationele database. Bij de verwijzing van Student.mentor naar Docent.acr heeft de tabel Student de rol van kind(tabel) en Docent de rol van ouder(tabel).  We houden ons zoveel mogelijk aan de volgende afspraak: we laten verwijspijlen omhoog wijzen. Ofwel: we tekenen de oudertabel boven de kindtabel, als dat kan.

FIGUUR 1.4

Voorbeeldpopulatie OpenSchool-database

 Van studenten is een uniek nummer (nr) opgenomen en daarnaast hun naam en eventueel een mentor. Een mentor is een docent. Merk op dat studenten alleen geïdentificeerd kunnen worden met hun nr, niet met hun naam! Docenten daarentegen hebben wel een unieke naam.

Verplichte kolom Optionele kolom

Recursieve verwijzing Primaire sleutel Alternatieve sleutel Uniciteitsregel p a a

Veel-veel-associatie

Eén-veel-associatie

Brede primaire sleutel Afgeleide kolom /

Brede verwijssleutel

Iedere student moet een studentnummer hebben (kolom Student.nr is verplicht), maar van de student met studentnummer 4 is geen mentorinformatie opgenomen (kolom Student.mentor is optioneel). Dit laatste kan twee dingen betekenen: student 4 heeft geen mentor of de mentor van student 4 is binnen het systeem niet bekend.  Van docenten is een unieke code vastgelegd: een acroniem (afgekort: acr). Verder heeft iedere docent een unieke naam, en kan een docent een vervanger hebben. Aangezien de vervanger ook een docent is, heeft de tabel Docent hiervoor een recursieve verwijzing gekregen. De kolom acr is gekozen als primaire sleutel van de tabel Docent, de kolom naam is een alternatieve sleutel. Zulke uniciteitsregels worden weergegeven met horizontale tweepuntige pijlen boven de betrokken kolommen. Hierbij geeft een p aan dat de betreffende unieke kolom(combinatie) is gekozen als primaire sleutel, en een a betekent dat het een alternatieve sleutel is geworden.  Van cursussen is een unieke code vastgelegd en daarnaast de cursusnaam (uiteraard ook uniek) en, niet verplicht, de examinator. De examinator is weer een docent en wordt dus vastgelegd via een acroniem. Verder heeft de tabel Cursus een kolom uren (voor de studiebelasting in uren) en een kolom credits (voor de zwaarte van een cursus, uitgedrukt in ECTS-credits).  De tabel Voorkenniseis heeft twee verwijssleutels naar de tabel Cursus, waarmee een veel-veel-associatie tussen Cursus en zichzelf wordt gemodelleerd: een cursus kan meerdere cursussen als voorkennis eisen, en andersom kan een cursus voor meerdere cursussen voorkennis zijn. Beide verwijzingen van Voorkenniseis naar Cursus staan voor één-veelassociaties (van boven naar beneden gelezen): bij één cursus kunnen veel voorkenniseisen horen, en één cursus kan voorkennis zijn voor veel cursussen.  Ook tussen Docent en Cursus bestaat een veel-veel-associatie, gemodelleerd in de gemeenschappelijke kindtabel Begeleider.  Van een inschrijving is vastgelegd om welke student het gaat en om welke cursus. Dat geeft een combinatie van een studentnummer en een cursuscode. Door zo’n combinatie wordt een inschrijving uniek vastgelegd (geïdentificeerd): de tabel Inschrijving heeft een brede (primaire) sleutel (of samengestelde sleutel). Daarnaast is er een datumkolom voor de inschrijfdatum, en een afgeleide kolom cijfer. Dat cijfer wordt automatisch bepaald (zie het volgende punt), zoals aangegeven door de / voor de kolomnaam in figuur 1.3 en de grijze achtergrond van de kolom in figuur 1.4. Verder kunnen studenten voor een cursus vrijstelling aanvragen. Zodra daarover een beslissing is genomen, kan er een J (‘ja’) of N (‘nee’) in kolom vrijstelling worden geplaatst.  Aan Inschrijving is de kindtabel Tentamen toegevoegd, met een brede verwijssleutel (of samengestelde verwijssleutel). 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.

Refererendeactieregel Restricted delete

rd

Cascading delete

cd

Restricted update Cascading update

ru cu Bedrijfsregel

 Bij iedere verwijzing horen twee refererende-actieregels: een deleteregel en een updateregel. De default deleteregel is de restricted delete (rd): hierbij wordt het verwijderen van een ouderrij tegengehouden als er nog corresponderende kindrijen bij bestaan. Dit is een direct gevolg van de referentiële integriteitsregel. Een alternatief is de cascading delete (cd): bij een poging tot verwijderen van een ouderrij wordt geprobeerd alle corresponderende kindrijen ook te verwijderen. Van de updateregel zijn ook twee varianten: de restricted update (ru) en de cascading update (cu). 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 voorkenniscursussen een voldoende of vrijstelling heeft. In leereenheid 12 ‘Triggers en stored procedures’ gaan we daadwerkelijk bedrijfsregels implementeren. 1.3

Databasemanagementsysteem Relationeel databasemanagementsysteem

dbms rdbms

Databasesysteem

DATABASEMANAGEMENTSYSTEEM

De gegevens in de database worden beheerd door een speciaal programma. Dat programma heet het databasemanagementsysteem (afgekort: dbms). Een dbms voor een relationele database wordt, meer specifiek, relationeel databasemanagementsysteem genoemd (afgekort: rdbms). De combinatie van een database en een databasemanagementsysteem noemen we een databasesysteem. Het zal duidelijk zijn dat we met een relationeel databasesysteem de combinatie aanduiden van een rdbms en een relationele database. Zie figuur 1.5.

FIGUUR 1.5 Opmerking

Databasesysteem

De term ‘database’ wordt vaak slordig gebruikt; niet zelden wordt er een dbms of een heel databasesysteem mee aangeduid. Een dbms heeft velerlei taken. We noemen de vier belangrijkste:

Opdrachtafhandeling

Beveiliging

 opdrachtafhandeling: alleen via het dbms kan een gebruiker iets met de database doen, zoals het bevragen van de database, het opslaan en verwijderen van gegevens en het creëren van tabellen. Een opdracht wordt ook wel statement genoemd.  beveiliging van de database, zodat onbevoegden geen toegang kunnen krijgen en de gegevens niet verloren gaan bij storingen.

Integriteitscontrole

Concurrency control

 integriteitscontrole, dat is de controle op regels en beperkingen waar databasegegevens aan moeten voldoen. Twee voorbeelden bij de Open School-database: een ingevoerde datum moet een bestaande datum zijn en de mentorcode van een student moet overeenkomen met die van een bestaande docent.  concurrency control: wanneer verschillende gebruikers gelijktijdig met een database werken, moet het dbms ervoor zorgen dat de werkzaamheden van die gebruikers in goede banen worden geleid en ze elkaar niet in de weg zitten. Het mooie van een dbms is dat applicaties geen weet hoeven te hebben van de fysieke bestandsstructuur. Dit wordt aangeduid met fysieke gegevensonafhankelijkheid. 1.4

APPLICATIES EN GEBRUIKERS

Het softwaregereedschap waarvan een gebruiker zich bedient, wordt applicatie of toepassingsprogramma genoemd. Verschillende typen gebruikers bedienen zich daarbij vaak van verschillende applicaties. Vaak ook gebruiken ze dezelfde applicatie maar hebben ze daar verschillende rechten op. Na inloggen met een gebruikersnaam en wachtwoord krijgen ze dan een gebruikersmenu en schermen die zijn toegesneden op hun taken en bevoegdheden. Op die manier lijkt het alsof ze een eigen applicatie hebben, terwijl dit in werkelijkheid niet het geval is. Door middel van inloggegevens creëren we een soort van virtuele gebruikers binnen het databasesysteem. In deze cursus zullen we het woord ‘gebruiker’ soms in de betekenis van ‘persoon’ gebruiken, maar veel vaker in de meer technische virtuele betekenis. De gebruikersgegevens (gebruikersnaam en wachtwoord) worden opgeslagen in de database. Zie figuur 1.6.

FIGUUR 1.6

Dbms als intermediair tussen gebruikers en applicaties enerzijds en database anderzijds

We noemen nu de belangrijkste typen gebruikers. Eindgebruikers Allereerst noemen we de eindgebruikers. Dat zijn de personen die de database bevragen, wijzigingen doorvoeren of rapporten genereren, in het algemeen via een eindgebruikersapplicatie. Eindgebruikersapplicaties zijn bijna altijd applicaties met een grafische user interface, die al dan niet gebruikmaken van een internetbrowser.

Eindgebruiker

Database administrator

DBA

Databaseontwerper

Zie de cursus Model-driven development

Database administrators Vervolgens noemen we de databasebeheerders of database administrators (DBA). Een DBA is verantwoordelijk voor het beheer van het dbms en de database(s), het verlenen van rechten aan gebruikers, het uitvoeren van structuurwijzigingen, enzovoort. De DBA is ook het aanspreekpunt bij problemen op het gebied van veiligheid of bij slechte prestaties van het systeem. Databaseontwerpers Een derde groep wordt gevormd door de databaseontwerpers. Zij zijn verantwoordelijk voor het identificeren van de gegevens die moeten worden opgeslagen en voor het kiezen van geschikte structuren voor het representeren en opslaan van de gegevens. Databaseontwerpers communiceren hiertoe met de toekomstige databasegebruikers om begrip te krijgen van hun behoeften en een databaseontwerp te maken dat bij die behoeften past. Vaak wordt de informatiebehoefte niet direct in databasetermen geformuleerd, maar op een abstracter niveau. Men spreekt dan van informatiemodellering. Applicatieontwikkelaars Als laatste noemen we de applicatieontwikkelaars. Dit zijn de personen die op basis van een bestaand databaseontwerp een applicatie ontwerpen en bouwen. We moeten hierbij denken aan schermen voor verschillende typen eindgebruikers met bijbehorende keuzemenu’s.

Applicatieontwikkelaar

Systeemontwikkelaar

Meer en meer ziet men dat databaseontwerpers en applicatieontwikkelaars dezelfde personen zijn: systeemontwikkelaars. Dit wordt mogelijk gemaakt door de krachtige ontwikkelhulpmiddelen van tegenwoordig. Zo kan men bij ‘model-driven development’ met alleen een informatiemodel al een prototypeapplicatie genereren, met niet meer dan een druk op de knop. Uw gebruikersrol in deze cursus

De gebruikersrol die u in deze cursus zelf vervult, zal dicht in de buurt liggen van die van DBA. U maakt daarbij voornamelijk gebruik van de relationele databasetaal SQL (zie paragraaf 1.5).

1.5

Databasetaal SQL

DDL DML DAL DCL

Spreek uit ’S-Q-L’ of als het Engelse ‘sequel’

DATABASETALEN

Een gebruiker moet kunnen communiceren met het dbms van een database. Als een gebruiker bijvoorbeeld bepaalde gegevens wil opvragen, dan moet daartoe een verzoek worden gericht aan het dbms. Zo’n verzoek moet worden gesteld in een databasetaal. De meest gebruikte databasetaal is SQL (Structured Query Language). Subtalen Een relationele databasetaal bevat de volgende subtalen voor deeltaken.  de Data Definition Language (DDL) voor het definiëren van structuren en het vastleggen van regels  de Data Manipulation Language (DML) voor het opvragen of wijzigen van gegevens  de Data Authorization Language (DAL) voor de autorisatie, dat wil zeggen het regelen van de gebruikerstoegang tot de gegevens  de Data Control Language (DCL) voor beheertaken met betrekking tot de ‘performance’, dat wil zeggen de prestaties van het systeem op het gebied van snelheid en geheugengebruik. Opmerking Men komt ook afwijkende indelingen tegen; zo wordt de DAL veelal tot de DDL gerekend. Zie tabel 1.1 voor een overzicht van de vier subtalen, de daarmee uitgevoerde taken en specifieke voorbeelden daarvan in een relationele omgeving. TABEL 1.1

Data definition language

Databaseschema

Subtalen van een relationele databasetaal

subtaal

taken op gebied van ...

voorbeelden (relationeel)

DDL

structuur

creëren van tabellen wijzigen van tabelstructuren verwijderen van tabellen

regels

constraints (beperkingsregels) gedragsregels

DML

operaties

opvragen van gegevens uit één of meer tabellen toevoegen van rijen verwijderen van rijen wijzigen van rijen

DAL

toegangsregels

toekennen van rechten op tabellen afnemen van rechten op tabellen

DCL

performance

‘indexen’ op tabellen, voor snelle toegang

Data Definition Language (DDL) Als het ontwerp van een database klaar is en er is een dbms gekozen, is het tijd voor het implementeren van de database. Dat doen we door aan het dbms aan te geven hoe de database er uit moet komen te zien. De gewenste structuur en de regels die moeten gelden, beschrijven we in een databaseschema. Dit doen we met behulp van de definitietaal Data Definition Language (DDL). Een strokendiagram is een grafische weergave van een deel van een databaseschema.

In een databaseschema leggen we allereerst de structuur van de database vast. Zo wordt in een relationeel databaseschema vastgelegd wat de namen zijn van de tabellen en hun kolommen en welke typen gegevens deze kolommen mogen bevatten. In een databaseschema leggen we ook vast aan welke regels de gegevens zijn gebonden. Zoals u in tabel 1.1 kunt zien, maken we daarbij onderscheid tussen twee soorten regels. Beperkingsregel Constraint

Om te beginnen zijn er de beperkingsregels ofwel constraints. Dat zijn regels die aangeven welke waarden en waardencombinaties in een database mogen voorkomen. Ze geven met andere woorden aan in welke toestand een database mag verkeren. Je zou kunnen zeggen: constraints leggen vast wat er op een ‘foto’ (momentopname) van de database mag staan en wat niet. Beperkingsregels worden ook statische regels genoemd. Vervolgens zijn er de gedragsregels. Dit soort regels beschrijven hoe de gegevens in een database in de tijd mogen veranderen. Gedragsregels geven aan welke toestandsveranderingen zijn toegestaan. Het zijn regels die weergeven wat er al dan niet op een ‘film’ van de database mag staan. Gedragsregels worden ook dynamische regels genoemd.

Gedragsregel

VOORBEELD 1.2 create-script van deel OpenSchool

Ter illustratie formuleren we een script (een aantal bij elkaar horende SQL-opdrachten, opgeslagen in een bestand met extensie .sql) voor de Open School-database uit figuur 1.3. In die figuur zien we de tabel met de naam Docent. In het databaseschema is de structuur van die tabel, inclusief de recursieve verwijzing, vastgelegd. Een script hiervoor, geformuleerd in de DDL van databasetaal SQL, zou er als volgt uit kunnen zien: create table Docent (acr char(3) naam varchar(20) vervanger char(3), primary key (acr), unique(naam) );

not null, not null,

alter table Docent add foreign key (vervanger) references Docent(acr) on update cascade; Opdracht

Engels: statement

Null

;

Bij het uitvoeren van het script leidt de create-opdracht ertoe dat een tabelbeschrijving wordt opgenomen in een databaseschema en de tabel daadwerkelijk wordt gecreëerd. Volgens de opdrachtformulering gaat het om een tabel met de naam Docent, die is opgebouwd uit drie kolommen met de namen acr, naam en vervanger. Met char(3) en varchar(20) wordt aangeduid welke waarden er in de kolommen mogen worden opgenomen: tekst van precies 3 respectievelijk maximaal 20 tekens. De not null achter de eerste twee kolommen geeft aan dat de waarden in die kolommen niet null (leeg) mogen zijn; de kolommen acr en naam zijn dus verplicht, terwijl de kolom vervanger optioneel is. Ook de uniciteitsregels die gelden voor de tabel worden in het create table-statement vastgelegd: er is een primaire sleutel van één kolom (acr) en een alternatieve sleutel van één kolom (naam). De ; na het sluithaakje van het create table-statement geeft aan dat de opdracht daar eindigt.

Uit het eropvolgende alter table-statement blijkt dat we toch nog niet klaar waren: de verwijzing van Docent.vervanger naar Docent.acr moet nog aangelegd worden. Omdat het hier om een recursieve verwijzing gaat, kan dit niet meteen in het create table-statement, omdat we dan zouden moeten verwijzen naar een tabel die nog niet is gecreëerd. De verwijzing krijgt als refererende-actieregels een cascading update en (default) een restricted delete. In eerste instantie is de tabel leeg, dat wil zeggen zonder rijen. In leereenheid 2 ‘Nulls, strings en logica’ bekijken we het fenomeen null nader. Met relationele databaseschema’s en de DDL maken we uitgebreid kennis in leereenheid 9 ‘Definitie van gegevensstructuren’. In leereenheid 13 ‘De data dictionary’ zien we ook hoe databaseschema’s intern worden opgeslagen. Data Manipulation Language (DML) Voor het manipuleren van de gegevens in een database maken we gebruik van de Data Manipulation Language (DML). Het manipuleren van gegevens heeft betrekking op een viertal zaken: – opvragen van gegevens – toevoegen van gegevens – wijzigen van gegevens – verwijderen van gegevens.

Data manipulation language

Merk op dat bij drie van deze operaties de inhoud van de database wordt veranderd. Alleen bij de eerste operatie (opvragen) blijft de inhoud ongewijzigd. Query

‘Query’ in beperkte betekenis

Retrieval VOORBEELD 1.3

Een opdracht in DML heet een query. Een query is dus een opdracht aan een databasesysteem om gegevens te leveren of om de gegevensinhoud van de database aan te passen. Soms wordt de term ‘query’ in de beperkte betekenis gebruikt van ‘opdracht om gegevens te leveren’ (een ‘opvraging’ of, in de Engelse terminologie, retrieval). Dit kan wel eens verwarrend zijn. We kijken nogmaals naar de Open School-database (figuren 1.3 en 1.4). De volgende serie opdrachten, geformuleerd in de DML van de databasetaal SQL, vult de tabel Student. insert insert insert insert

into into into into

Student Student Student Student

values values values values

(1, (2, (3, (4,

'Berk', 'Tack', 'Bos', 'Eik',

'DAT'); 'DAT'); 'COD'); null );

Voorwaarde voor het uitvoeren van deze opdrachten is dat de tabel Docent al gevuld is, en dat de acroniemen ‘DAT’ en ‘COD’ daarin daadwerkelijk voorkomen. Als dat niet het geval is krijgen we een foutmelding vanwege schending van de referentiële integriteit.

VOORBEELD 1.4

We kijken weer naar de Open School-database (figuur 1.3). De volgende opdracht, geformuleerd in de DML van de databasetaal SQL, vraagt cursusgegevens op. select from where order by

Commentaar

--

-----

3 1 2 4

De getallen 1 t/m 4 geven aan in welke volgorde de regels van dit statement verwerkt worden. Ze staan ‘in commentaar’, dat wordt voorafgegaan door --. Commentaar is bedoeld voor de menselijke lezer en wordt door het rdbms genegeerd. We volgen de nummers 1 t/m 4 om te zien wat de opdracht beoogt te doen. De tweede regel van de opdracht geeft aan dat de gegevens moeten komen uit de tabel met de naam Cursus. De derde regel geeft aan dat we alleen rijen willen zien waarin in de kolom credits een getal groter dan of gelijk aan 4 is ingevuld. De eerste regel geeft aan dat er van de geselecteerde rijen drie kolommen moeten worden afgebeeld: code, naam en examinator. De vierde regel geeft aan dat de rijen van de resultaattabel moeten worden geordend op oplopende cursuscode. Het rdbms zal reageren met het afbeelden van de volgende tabel: CODE ====== DB DW IM SW

Weergave null in resultaat:

code, naam, examinator Cursus credits >= 4 code;

NAAM ==================== Databases Discrete wiskunde Informatiemodelleren Semantic web

EXAMINATOR ========== COD DAT DAT

We zien het meteen als een waarde in een optionele kolom niet is ingevuld: op die plek zet het rdbms de aanduiding neer in het resultaat. Een groot deel van deze cursus is gewijd aan het opstellen van goede query’s. In de leereenheden 4 t/m 7 behandelen we het opvragen van gegevens, en dan vooral in het geval dat de informatie uit meerdere tabellen moet komen. In leereenheid 8 behandelen we het toevoegen, wijzigen en verwijderen van gegevens.

Data authorization language Toegangsregels

Data Authorization Language Om toegangsregels vast te leggen maken we gebruik van de Data authorization language (DAL, vaak gerekend tot de DDL). Dergelijke regels beschrijven wat de verschillende gebruikers met de gegevens mogen doen. Met die regels kan een DBA bijvoorbeeld aangeven dat een bepaalde gebruiker de gegevens in een bepaalde tabel alleen mag raadplegen, maar een andere gebruiker die gegevens zowel mag raadplegen als wijzigen.

In een database die in de praktijk wordt gebruikt, en zeker als er meerdere gebruikers kunnen zijn, die ook nog eens verschillende rechten hebben, is het van groot belang de autorisatie goed te regelen. Hoe dat precies in zijn werk gaat verschilt per rdbms. Het is een kwestie van opzoeken in de documentatie hoe het precies geregeld is en dat dan toepassen. In deze cursus gaan wij hier niet verder op in. Data Control Language

Data Control Language Wanneer we ons (in de rol van DBA) druk gaan maken om de prestaties van het systeem, komt de Data Control Language (DCL) in beeld. Bij ‘prestaties’ moeten we dan denken aan ‘zo snel mogelijk’ en ‘zo min mogelijk geheugen’. Hiervoor kan het nodig zijn de manier waarop de gegevens fysiek zijn opgeslagen of worden benaderd, te veranderen. Voorbeelden komen volop aan bod in leereenheid 10 ‘Queryoptimalisatie’. Dialecten en standaards In de loop der tijd heeft SQL de status verworven van ‘universele gegevenstaal’ voor rdbms’en. SQL is een min of meer levende taal. Succesvolle fabrikanten van rdbms’en hebben bijgedragen aan de ontwikkeling, óf de ontwikkeling juist afgeremd. Want als een fabrikant veel heeft geïnvesteerd in de eigen variant, ziet die niet graag dat de taal zich heel anders ontwikkelt. Belangrijke fabrikanten zijn: IBM (met onder meer het rdbms DB2), Oracle (met Oracle) en Microsoft (met SQL Server).

Dialect

SQL-standaard

Firebird

Bij elk rdbms hoort een eigen SQL-dialect, al zijn de verschillen soms marginaal. Verschillende organisaties en bedrijven hebben geprobeerd de ontwikkeling van SQL te sturen door het formuleren van standaards. De eerste was ANSI (American National Standards Institute) en later kwam daar ISO (International Standards Organization) bij. Bij deze cursus maken we gebruik van het rdbms Firebird, een open source databaseproduct. Daardoor hebben we te maken met FirebirdSQL. Dit sluit nauw aan bij de belangrijkste ANSI/ISO-standaards, zeker voor de basisopdrachten. Wanneer SQL-opdrachten in deze cursus niet in Firebird kunnen worden uitgevoerd, is dat via commentaar in de SQLcode aangegeven. De ANSI/ISO-standaards van SQL De eerste (ANSI-)standaard van SQL dateert van 1986. Het jaar daarop volgde adoptie door ISO en sindsdien zijn ANSI en ISO gezamenlijk opgetrokken in het verder ontwikkelen van de standaard. De standaard werd in het begin bewust mager gehouden en nogal wat zaken werden ‘leverancierafhankelijk’ verklaard. Zelfs primaire sleutels en referentiële integriteit (verwijssleutels) werden nog niet beschreven. Men beperkte zich tot wat de belangrijkste dialecten gemeenschappelijk hadden. Zo wilde men het leveranciers gemakkelijker maken zich openlijk aan SQL te conformeren, een slimme manier om de standaard enig gezag te verlenen.

Een belangrijke uitbreiding vond plaats in 1992, met SQL2 (ook wel SQL92 genoemd). Deze standaard ging veel verder, maar werd daardoor minder breed gedragen. In zekere zin dreigde het zelf een dialect naast de andere dialecten te worden. Om dit te ondervangen, heeft men verschillende ‘levels of conformance’ bedacht. Latere standaards zijn: SQL:1999 (ook SQL3 genoemd), SQL:2003, SQL:2006 en SQL:2008, allemaal lijvige boekwerken waarin ANSO/ISO sturing probeerde te geven aan de nieuwste ontwikkelingen. Van die ontwikkelingen noemen we hier triggers (te behandelen in leereenheid 12) en het gebruik van XML in combinatie met SQL. Een groot deel van de SQL-standaards is overigens gewijd aan embedded SQL, een taaluitbreiding bedoeld voor het opnemen van SQL-statements binnen procedurele (3GL) programmeertalen zoals COBOL, Pascal of C++. Embedded SQL wordt steeds minder gebruikt en valt buiten het bestek van deze cursus. De belangrijkste marktpartijen – waaronder Oracle en IBM – hebben mede hun stempel gedrukt op de ontwikkeling van de ANSI/ISO-standaards. Er is geen sprake geweest van leverancieronafhankelijk ontwikkelen van een ‘mooie’ taal. Mede daardoor is er heel wat op SQL aan te merken, wat we in deze cursus ook regelmatig zullen doen. We hopen dat de lezer hierdoor leert om vanaf een wat hoger standpunt de theorie achter SQL en de praktijk van het gebruik ervan te bezien. De keuze van een dialect is in wezen niet belangrijk. Wie het echt snapt, stapt moeiteloos over van het ene naar het andere. 1.6

Transactie

TRANSACTIES

Een transactie is een opeenvolging van databaseactiviteiten die bij elkaar horen en als eenheid worden verwerkt, om te waarborgen dat de database steeds in een toelaatbare toestand verkeert. Een voorbeeld van een transactie is een internetbetaling, inclusief het checken van de kredietwaardigheid van de klant bij de opgegeven creditcardmaatschappij. Een ander voorbeeld is het reserveren van een vliegtuigstoel door een reisbureau. De transactie betreft hier het raadplegen van een database, resulterend in een eventuele bevestiging dat de gevraagde stoel vrij is en daarna mogelijk het vastleggen van de reservering. Wordt zo’n transactie afgebroken, dan wordt hij in zijn geheel teruggeschroefd. Hij wordt dus niet half uitgevoerd en later afgemaakt. Het transactiemechanisme van het dbms houdt de transacties van verschillende gebruikers goed gescheiden en zorgt ervoor dat gelijktijdige transacties elkaar niet in de weg zitten. Het zou bijvoorbeeld ontoelaatbaar zijn wanneer tijdens één transactie wordt geconstateerd dat een vliegtuigstoel vrij is, deze vervolgens wordt gereserveerd, maar daarna blijkt dat die stoel pal daarvoor toch nog door een andere gebruiker is weggekaapt. Voor en na een transactie moet een database in een toelaatbare toestand verkeren. In hoeverre de database tijdens een transactie in een ‘verboden’ toestand mag verkeren, is afhankelijk van het dbms.

VOORBEELD 1.5

Veronderstel dat we de Open School-database van figuur 1.3 moeten wijzigen, omdat docent Bachman weggaat. We kunnen niet zomaar de rij van Bachman uit de tabel Docent verwijderen: Bachman wordt genoemd in twee van de vier kindtabellen van Docent (namelijk in Cursus.examinator en in Begeleider.docent), en op de bijbehorende verwijzingen zit een restricted delete. Stel nu dat we weten dat de taken van Bachman als examinator én als begeleider van II worden overgenomen door Date, en dat Bachman gewoon wordt geschrapt als een van de begeleiders van DB. Dan kunnen we de wijzigingen doorvoeren met de volgende transactie (we geven steeds in commentaar aan wat de bedoeling van een opdracht is): -- vervang examinator van cursus II door DAT update Cursus set examinator = 'DAT' where code = 'II'; -- verwijder de rij van BAC als begeleider van DB uit Begeleider delete from Begeleider where docent = 'BAC' and cursus = 'DB'; -- vervang BAC als begeleider van II door DAT update Begeleider set docent = 'DAT' where docent = 'BAC' and cursus = 'II'; -- verwijder de rij van docent BAC uit Docent delete from Docent where acr = 'BAC'; commit;

Het belangrijkste is dat de vier opdrachten allemaal of geen van alle worden uitgevoerd. Als geen van de opdrachten een foutmelding geeft, wordt door het uitvoeren van de commit het resultaat vastgelegd in de database. Als een van de opdrachten wel een foutmelding geeft, zorgt het transactiemechanisme ervoor dat alles wat in deze transactie is gebeurd wordt teruggedraaid (rollback). Script vs. transactie

Merk op dat er een duidelijke overeenkomst is tussen een script en een transactie: beide bestaan uit een aantal bij elkaar horende opdrachten, die allemaal moeten worden uitgevoerd. Een script kan echter meerdere transacties bevatten, door tussendoor te committen. In leereenheid 11 ‘Transacties en concurrency’ gaan we hier dieper op in. 1.7

TRANSACTIONELE SYSTEMEN

Transactionele systemen

Veel databases ondersteunen de dagelijkse, operationele processen. Hierin worden de dagelijkse veranderingen van de bedrijfswerkelijkheid bijgehouden (nieuwe klanten en bestellingen, adreswijzigingen, enzovoort). Dit soort systemen heten transactionele systemen.

OLTP

Sommige daarvan zijn er specifiek op gericht grote aantallen transacties snel te verwerken. Een illustratief voorbeeld is de verwerking van pincodetransacties. Zulke systemen worden OLTP-systemen (online

transaction processing) genoemd. De laatste jaren zien we de neiging om het begrip OLTP-systeem op te rekken; dan worden ook andere transactionele systemen ertoe gerekend.

Batchverwerking

Bij transactionele systemen worden de opdrachten van gebruikers aan het dbms vaak direct uitgevoerd. Maar het kan ook voorkomen dat opdrachten worden opgespaard en in batches (= ‘groepen’) worden verwerkt. Een voorbeeld van batchverwerking is nachtelijke verwerking door een bankdatabasesysteem van overdag verzamelde overschrijvingen. 1.8

Datawarehouse

DATAWAREHOUSES EN OLAP

Het komt vaak voor dat een bedrijf naast één of meer transactionele systemen ook een datawarehouse (letterlijk: gegevenspakhuis) heeft. Zo’n datawarehouse is een grote historiedatabase, die regelmatig batchgewijs wordt bijgewerkt vanuit de verschillende transactionele systemen van het bedrijf, meestal na statistische bewerking. Een datawarehouse kan erg groot zijn, omdat er doorgaans gegevens over lange perioden in worden bewaard. Datawarehouses worden gebruikt voor het verkrijgen van managementinformatie en meer in het algemeen informatie ter ondersteuning van beleidsbeslissingen. Denk bijvoorbeeld aan analyse van het koopgedrag van klanten, verbetering van het assortiment, identificatie van doelgroepen of het voorspellen van kooptrends.

OLAP

Voor het raadplegen van datawarehouses bestaan verschillende typen programma’s. Eén van die typen heet OLAP, ofwel online analytical processing. OLAP-programmatuur stelt de gebruiker in staat gemakkelijk en selectief informatie in beeld te krijgen vanuit verschillende gezichtspunten of ‘dimensies’, zoals tijd (dagen, weken, maanden, jaren, ...) of plaats (vestiging, stad, land, werelddeel, ...). Een gebruiker kan rapportages opvragen en daarmee statistische waarden herleiden tot onderliggende detailniveaus, waarbij het mogelijk is heen en weer te springen tussen grove en meer verfijnde statistieken (‘drill up’ en ‘drill down’). OLAP 1.9

en datawarehouses vallen buiten het bestek van deze cursus. VOORDELEN EN BEPERKINGEN VAN RELATIONELE DATABASESYSTEMEN

We sommen kort de belangrijkste voordelen van relationele systemen op, en een paar beperkingen. Voordelen van relationele systemen Relationele systemen hebben een aantal voordelen. – Eenvoud: relationele databases zijn eenvoudig van vorm. Dat maakt dat ze eenvoudig zijn te gebruiken en te beheren. – Alle logisch bestaande verwijzingen kunnen worden benut, daar deze worden gerealiseerd via waardevergelijkingen en niet afhankelijk zijn van fysiek gerealiseerde pointers. Dat betekent dat we gerust aan het dbms kunnen vragen om alle studenten met dezelfde achternaam als een

Logische gegevensonafhankelijkheid

docent. Het feit dat er geen verwijzing aanwezig is tussen Student.naam en Docent.naam betekent niet dat we de waarden in die kolommen niet mogen vergelijken. Het feit dat er wél een verwijzing aanwezig is tussen Student.mentor en Docent.acr betekent ten eerste dat de databaseontwerper verwacht dat die verwijzing nodig of nuttig is, en ten tweede dat daar iets extra’s geregeld is: controle op referentiële integriteit. – Logische gegevensonafhankelijkheid: het gebruik van de gegevens is onafhankelijk van de logische gegevensstructuur. Anders gezegd: een applicatie merkt niets van veranderingen in de logische structuur voor zover die de ’eigen gegevens’ ongemoeid laten. Dit is bijvoorbeeld het geval wanneer een tabel wordt toegevoegd, of wanneer een kolom die door de applicatie niet wordt gebruikt, wordt verwijderd. Deze eigenschap is nog net wat krachtiger dan de fysieke gegevensonafhankelijkheid, die een kenmerk is van alle databasesystemen. Beperkingen van relationele systemen Tegenover de onmiskenbare voordelen staan de volgende beperkingen. – Relationele systemen zijn niet zo geschikt voor het vormgeven van complexe structuren, zoals we die bijvoorbeeld aantreffen bij GIS (geografische informatiesystemen), CAD (computer aided design) en multimedia (toepassingen voor beelden, tekst, taal, documenten en video). Er is daarom ook onderzoek gedaan naar systemen die dit soort structuren wel (of beter) aankunnen. We noemen de objectgeoriënteerde (OO) databasesystemen, en het opslaan van gegevens in XML-documenten of in relationele databases in combinatie met XML, maar we gaan daar in deze cursus verder niet op in. – Relationele systemen hebben ten opzichte van hun voorgangers ook wat moeten inleveren: die voorgangers (de hiërarchische database en de netwerkdatabase) gebruikten pointers om de verschillende ‘records’ aan elkaar te knopen. Een record is hierbij zoiets als een ‘ding’ van een bepaalde ‘soort’. In een database die bedoeld was om snel alle mentorstudenten van een docent te vinden, had iedere mentorstudent het fysieke geheugenadres van zijn mentor bij zich. Tegenover die efficiënte directe pointerverwijzingen staan de arbeidsintensieve verwijzingen op basis van waardenvergelijking bij de relationele database. De doorbraak van relationele systemen kwam dan ook pas in de jaren ’80, toen de hardware snel genoeg was geworden om de performance van query’s waarbij meerdere grote tabellen waren betrokken binnen aanvaardbare grenzen te brengen. Oorsprong van relationele databasesystemen In 1970 publiceerde Edgar F. Codd een artikel in Communications of the ACM met de titel ’A relational model for large shared data banks’ (als pdf-document te vinden met de zoekterm ‘codd 1970 pdf’). Dit beroemde artikel vormt het startpunt voor de relationele databases. De publicatie van Codd had veel onderzoek tot gevolg. Dat leidde ertoe dat IBM in 1974 naar buiten kwam met een prototype van een relationele database genaamd System/R. In de jaren daarna ontwikkelden ze daar een vraagtaal bij die ze SEQUEL (Structured English query language) noemden, later verkort tot SQL (Structured query language).

Een groep ingenieurs realiseerde zich de potentie van relationele systemen en vormde het bedrijf Relational Software. In 1979 kwam dit met het eerste commerciële rdbms, met SQL als vraagtaal. Ze noemden dit product Oracle. Meer dan de helft van alle opgeslagen relationele gegevens wordt momenteel beheerd door Oracle-systemen. De vraagtaal SQL wordt tegenwoordig toegepast in bijna alle commerciële relationele systemen. Als opvolger van System/R bracht IBM het product SQL/Data System (SQL/DS) op de markt en later Database 2 (DB 2). Met het gewicht van IBM achter het product werd de IBM-versie van SQL de de facto standaard. Er is ook een aantal opeenvolgende standaards ontwikkeld door ANSI (American National Standards Institute) en ISO (International Organization for Standardization). Deze standaards zijn voor een belangrijk deel gebaseerd op de SQL-versie van IBM. Alle belangrijke commerciële databaseproducenten voegen zich in meer of mindere mate naar die standaards, zoals Microsoft SQL Server, Oracle, Informix, Sybase en natuurlijk IBM DB2. 2

Architecturen van databasesystemen

In deze paragraaf nemen we een ander aspect van databasesystemen onder de loep: de manier waarop een databasesysteem en eindgebruikersapplicaties met elkaar samenwerken en de plaatsen waar ze zich bevinden. We spreken van de architectuur van het systeem.

Architectuur

2.1

CLIENTS, SERVERS EN TIERS

In de jaren tachtig verschenen de eerste netwerken met personal computers. In zo’n netwerk was dan een relatief krachtige computer opgenomen die werd aangeduid als de server. De pc’s, aangeduid als clients, konden gebruikmaken van de diensten van de servercomputer. Dat leverde allerlei voordelen voor gebruikers, bijvoorbeeld omdat ze daardoor konden werken met relatief grote applicatieprogramma’s.

Server

Client-server

In de jaren daarna zien we dat steeds meer client-serversystemen worden gebouwd, dat wil zeggen: systemen met als basis een onderscheid tussen clients en servers. En daarbij zien we ook dat de ideeën rond die architectuur zich verder ontwikkelen. Client-server: softwarerollen Let op!

Een zuivere benadering van de client-serverarchitectuur gaat niet uit van een fysieke hardwareconfiguratie, maar van een bepaalde rolverdeling tussen programma’s, dat wil zeggen: tussen softwarecomponenten. Daarbij verleent het ene programma (de server, of het ‘serverprogramma’) diensten aan een ander programma dat om die diensten vraagt (de client, of het ‘clientprogramma’).

Ook op het terrein van de databasesystemen zien we dat in de jaren na 1980 de overstap wordt gemaakt naar client-serverarchitecturen. In de eerste databasesystemen met die architectuur communiceerden de client en de server direct met elkaar. Dergelijke systemen worden nog steeds gebruikt, maar voor grote systemen met veel gebruikers is een dergelijke configuratie veelal minder geschikt, want er kunnen dan problemen ontstaan met onder meer de prestaties.

Die problemen resulteren erin dat we steeds meer systemen zien verschijnen waarbij de communicatie via een extra softwarelaag verloopt. We kunnen ons dan voorstellen dat de softwarearchitectuur is opgebouwd uit drie lagen. Tier

Spreek uit met ‘ie’.

Die softwarelagen worden aangeduid met de Engelse term tier. We kunnen dus spreken van een 3-tiersysteem. De drie softwarelagen van een 3-tiersysteem zijn de client-tier, de server-tier en daar tussenin de middle tier. Een middle tier heeft een dubbele rol, want deze is server voor de client-tier en client voor de server-tier. Systemen kunnen we classificeren op basis van het aantal tiers van de softwarearchitectuur: 1-tier-, 2-tier- en 3-tiersystemen of zelfs n-tiersystemen met nog grotere n. 2.2

1-TIERSYSTEMEN

In de jaren zestig en zeventig waren databases ondergebracht op grote computers (mainframes), met daarop alle software voor het werken met die databases. Aan die mainframes was een aantal ’lege’, niet-intelligente terminals gekoppeld, die niets anders deden dan het opvangen en doorgeven van toetsaanslagen. Alle intelligentie bevond zich in de centrale computers. Dergelijke systemen beschikten over een zogeheten 1-tierarchitectuur. Dat wil zeggen dat alle software was ondergebracht in één laag en dat het niet mogelijk was clientsoftware en serversoftware te onderscheiden. 2.3

Gecentraliseerd client-serversysteem

2-TIERSYSTEMEN

Mainframes worden nog steeds gebruikt, maar de databasesoftware daarvoor heeft nu meestal een client-serverarchitectuur: een 2-tier systeem met de server op het mainframe zelf en de clientsoftware op bijvoorbeeld een aantal pc’s of vergelijkbare computers. Veel van de grotere databasesystemen hebben een vergelijkbare 2-tierarchitectuur, maar dan met een ‘gewone’ servercomputer in plaats van een mainframe (zie figuur 1.7). De server is meestal met de clients verbonden door een netwerkverbinding. De gebruikers sturen via de clientsoftware opdrachten naar de server. De server voert de opdrachten uit: bijvoorbeeld het wijzigen van een adres of het terugsturen van een overzicht met gegevens.

FIGUUR 1.7

Client-serverarchitectuur met pc’s en centrale databaseserver

Bij veel kleine systemen draaien client en server op één machine, bijvoorbeeld op een pc. Wanneer u de opgaven en voorbeelden in deze cursus uitprobeert, zal dat waarschijnlijk op één machine gebeuren: een 2-tiersysteem met client en server in één kastje. Voor een client-serversysteem zijn drie taken te onderscheiden: gegevenspresentatie, gegevensverwerking en gegevensbeheer. Zie figuur 1.8.

FIGUUR 1.8

Drie taken bij client-serversystemen

De client is primair verantwoordelijk voor de gegevenspresentatie, dat wil zeggen: de afhandeling van de communicatie tussen gebruiker en systeem. De server is primair verantwoordelijk voor het gegevensbeheer, waarbij moet worden gedacht aan veilige opslag (met alleen toegang voor geautoriseerde gebruikers) en aan het bewaken van een aantal basale databaseregels die direct met de databasestructuur te maken hebben. Voor relationele systemen wordt het gegevensbeheer uitgevoerd door een rdbms.

Bedrijfsregel

De verantwoordelijkheid voor de gegevensverwerking kan worden verdeeld over de client en de server. Het gaat daarbij om het op de juiste manier verwerken van opdrachten. Een belangrijk element daarbij is controle op bedrijfsregels. Een eenvoudig voorbeeld van zo’n bedrijfsregel is de juiste vorm van een postcode. Deze controle kan ofwel direct bij invoer door de clientsoftware gebeuren ofwel later door het rdbms op de server. In het eerste geval gebeurt het snel en zonder netwerkverkeer, in het tweede geval gebeurt het door gecentraliseerde software, wat óók zo zijn voordelen heeft. 2.4

Middle tier Middleware

3-TIERSYSTEMEN

Bij 3-tier systemen is er een extra laag tussengevoegd. Die extra laag wordt aangeduid als middle tier en bevindt zich tussen de clientlaag en de serverlaag. De software op de middle tier wordt aangeduid als middleware. Door de programmatuur voor gegevensverwerking (waaronder controle op bedrijfsregels) voor belangrijke onderdelen of mogelijk zelfs in haar geheel in de middle tier te concentreren, ontstaat een systeem dat flexibeler en beter te onderhouden is dat betere prestaties levert.

Er zijn 3-tiersystemen in allerlei verschillende vormen, met verschillende soorten middleware. In figuur 1.9 hebben we een configuratie geschetst met een ‘business rule engine’, dat is software die voor alle clients de gegevensstromen controleert op het voldoen aan de bedrijfsregels.

FIGUUR 1.9

Applicatieserver

Systeem met business rule engine

Als we een systeem hebben waarvoor geldt dat de clients te maken hebben met eenzelfde applicatie, dan ligt het voor de hand om die applicatie (voor het grootste gedeelte) onder te brengen op de middle tier. Die tier wordt dan applicatieserver genoemd. Het werken met een applicatieserver heeft onder meer als voordelen dat de clients met minder software toekunnen en dat de applicatie gemakkelijker kan worden onderhouden. Applicatieservers zijn gemeengoed geworden in relatie met het internet. Browsers fungeren daarbij als client. 2.5

DATABASES EN INTERNET

Figuur 1.10 toont een eenvoudige architectuur voor het gebruik van databases via internet. Voor de communicatie via internet beschikt de eindgebruiker over clientsoftware in de vorm van een internetbrowser, zoals Firefox of Internet Explorer.

FIGUUR 1.10

Webserver Webapplicatieserver

3-tier-architectuur in internetomgeving

We zien dat in de middle tier een webserver is opgenomen (een server die webpagina’s levert), naast een webapplicatieserver (een server die één of meer applicaties runt). Zuiver gesproken is hier zelfs sprake van een 4tier architectuur.

SAMENVATTING Paragraaf 1

Een database is een verzameling van gegevens die voor langere tijd zijn opgeslagen en waaruit een computerprogramma (snel) voor verschillende toepassingen de gewenste gegevens kan selecteren. Een databasemanagementsysteem (dbms) is een programma dat de gegevens in de database beheert. Een databasesysteem is de combinatie van een database en een databasemanagementsysteem. Taken van een dbms zijn:  opdrachtafhandeling (bij het bevragen van een database en het invoeren, wijzigen en verwijderen van gegevens)  gegevensbeveiliging (het dbms moet zorgen dat onbevoegden geen toegang krijgen tot de gegevens)  integriteitscontrole (controle op de regels en beperkingen waaraan de gegevens zijn onderworpen)  concurrency control (zorgen dat, wanneer verschillende gebruikers gelijktijdig met een database werken, deze elkaar niet in de weg zitten). De belangrijkste soorten gebruikers van een databasesysteem zijn:  eindgebruikers, die de database bevragen of muteren  database administrators (DBA’s), verantwoordelijk voor het beheer van het databasesysteem (rechtenverlening aan gebruikers, coördinatie en toezicht op het gebruik; tevens aanspreekpunt voor problemen op het gebied van veiligheid of bij slechte prestaties van het systeem) – databaseontwerpers: verantwoordelijk voor het identificeren van de gegevens die moeten worden opgeslagen, en voor het kiezen van geschikte structuren voor het representeren en opslaan ervan  applicatieontwikkelaars: ontwerpen en bouwen op basis van een databaseontwerp een applicatie (schermen voor verschillende typen eindgebruikers, met bijbehorende keuzemenu’s). Meer en meer ziet men dat databaseontwerpers en applicatieontwikkelaars dezelfde personen zijn: systeemontwikkelaars. Een relationele databasetaal bevat de volgende subtalen voor deeltaken.  Data Definition Language (DDL), voor het definiëren van een databaseschema, waarin databasestructuren en -regels zijn vastgelegd  Data Manipulation Language (DML), voor het opvragen of wijzigen van gegevens  Data Authorization Language (DAL), voor het regelen van de gebruikerstoegang tot de gegevens  Data Control Language (DCL), voor beheertaken met betrekking tot de ‘performance’ (prestaties m.b.t. snelheid en geheugengebruik). Beperkingsregels zijn regels die aangeven welke waarden en waardencombinaties in een database mogen voorkomen. Ze geven aan in welke toestand een database mag verkeren. Gedragsregels zijn regels die beschrijven hoe de gegevens in een database mogen veranderen. Ze geven aan welke toestandsveranderingen zijn toegestaan.

Een transactie is een opeenvolging van databaseactiviteiten die bij elkaar horen en als eenheid worden verwerkt, om te waarborgen dat de database steeds in een toelaatbare toestand verkeert. Databases kunnen naar het type transacties worden onderverdeeld in transactionele systemen en systemen voor analyse en ondersteuning van beleidsbeslissingen ( OLAPsystemen met datawarehouse). Paragraaf 2

Client-server is een belangrijke softwarearchitectuur, waarbij het een programma (in de rol van server) diensten verleent aan andere programma’s (clients) die diensten vragen. Bij databasesystemen met een client-serverarchitectuur treedt het dbms op als server en de applicatie als client. De client neemt de gegevenspresentatie voor zijn rekening en de server het gegevensbeheer. De gegevensverwerking (waaronder het bewaken van bedrijfregels) kan door de client geschieden, door de server of  via taakverdeling  door beide. Clients en server vormen verschillende architectuurlagen, tiers genaamd, die zich op verschillende computers mogen bevinden. Client-server is daarmee een 2-tierarchitectuur. Een oudere architectuur zag men bij mainframes met ‘domme’ terminals (1-tierarchitectuur). Nieuwere architecturen zijn de n-tierarchitecturen (n=3 of hoger). Ten opzichte van client-server hebben deze één of meer extra lagen met middleware. Zo is er middleware voor het bewaken van bedrijfsregels (business rule engine) of voor applicatiesoftware waar meerdere clients gebruik van maken (applicatieserver). In een internetomgeving hebben we clientsoftware in de vorm van een internetbrowser die via een webserver en een applicatieserver met een dbms en een database communiceert.

ZELFTOETS

We beschrijven de voorbeelddatabase die we in vrijwel iedere zelftoets in deze cursus gebruiken: de Orderdatabase van een zekere firma Reijnders. Het strokendiagram vindt u in figuur 1.11. De voorbeeldpopulatie vindt u achterin dit cursusdeel. Toelichting We bespreken alleen een paar opvallende kenmerken van de Orderdatabase. De rest spreekt vanzelf of is op te zoeken in Inleiding informatica.  Een klant kan zijn aangebracht door een andere klant; een klant kan meerdere nieuwe klanten aanbrengen.  De woorden ‘order’ en ‘Order’ zijn gereserveerde woorden in SQL, wat betekent dat we ze niet mogen gebruiken als tabel- of kolomnaam. We moeten dus een beetje creatief zijn bij het bedenken van een naam voor de tabel met orders. ‘Orders’ is een mogelijkheid, maar omdat we tabellen liefst een naam in het enkelvoud geven (iedere rij in die tabel beschrijft één order) hebben we voor ‘Order_’ gekozen.

Initieel afleidbaar

i

 Orderregel.bedrag en Order_.totaalbedrag zijn beide optioneel (  ) en afleidbaar ( / ). Order_.totaalbedrag kan te allen tijde worden berekend door de orderregelbedragen van die order bij elkaar op te tellen. Orderregel.bedrag daarentegen is alleen initieel afleidbaar ( i ): een orderregelbedrag klopt (als het eenmaal is uitgerekend) alleen zolang de verkoopprijs van het bijbehorende artikel gelijk blijft.  De ‘verwijzing’ van Orderregel.aantal naar Kortingsinterval is een zogenaamde intervalverwijzing, aangegeven door de stippelpijl. Als Orderregel.aantal tussen beginaantal en eindaantal ligt (die laatste alleen indien ingevuld) geldt de bijbehorende korting. Eigenlijk is de naam intervalverwijzing een beetje misleidend: het is geen echte verwijzing. Hij bestaat alleen in het hoofd van de gebruiker en op papier, het rdbms controleert niet vanzelf – wat bij een gewone verwijzing wel gebeurt – op referentiële integriteit. In leereenheid 5 zien we hoe we een intervalverwijzing gebruiken, en in leereenheid 12 hoe we toch een controle op ingevulde aantallen kunnen aanbrengen.  De ‘Regels Kortingsinterval en Orderregel’ die aan het strokendiagram in figuur 1.11 zijn toegevoegd, worden niet standaard gecontroleerd door het rdbms. Bovendien zijn ze specifiek voor de firma Reijnders: een ander bedrijf zou de regel kunnen hanteren dat aantallen tussen 23 en 32 een korting van 3% opleveren, en aantallen tussen 50 en 60 een korting van 5%. Dat mag ook, en kan op dezelfde manier worden opgeslagen in de database (met intervalverwijzing en een tabel Kortingsinterval), alleen met andere regels ernaast geschreven.

FIGUUR 1.11

Strokendiagram Orderdatabase

1

De klanten 2345 (Pauw) en 2346 (Pootjes) hebben een zekere Wiegerink als nieuwe klant aangebracht. Deze Wiegerink, die nr 3001 heeft gekregen, heeft op zijn beurt Bijpost (nr 3123) en De Groot (nr 3125) aangebracht. In figuur 1.12 is een poging gedaan deze situatie weer te geven. a Wat is er mis met de populatie in figuur 1.12? b Is het mogelijk de beschreven situatie correct weer te geven in een populatiediagram?

FIGUUR 1.12

Voorbeeldpopulatie Klant

2

a Geef een zo klein mogelijke voorbeeldpopulatie die illustreert wat wel en niet mag voorkomen in de kolommen order_, volgnr en artikel van Orderregel. b Welke eigenschap van de facturen van de firma Reijnders wordt gemodelleerd met de combinatie van primaire en alternatieve sleutel van Orderregel? Beschouw een factuur hierbij als een overzicht van één order van één klant.

3

Geef in ieder van de volgende situaties aan wat er precies gebeurt, steeds uitgaande van de populatie achterin dit cursusdeel: a we proberen de rij van order 5773 te verwijderen: delete from Order_ where nr = 5773;

b we proberen een nieuwe orderregel in te voeren:

insert into Orderregel values (5774, 3, 351, 1, null);

TERUGKOPPELING

Uitwerking van de zelftoets 1

a In de kolom aanbrenger is steeds niet de aanbrenger van de betreffende klant weergegeven, maar juist de nieuwe klant die door de betreffende klant is aangebracht. Volgens de definitie van de tabel Klant en de naam van de kolom aanbrenger betekent de eerste rij in figuur 1.12: “de klant met klantnr 2345 heeft de naam ‘Pauw’ en heeft als aanbrenger de klant met nummer 3001”. Ofwel: Wiegerink heeft Pauw aangebracht. Maar dat is niet volgens de omschreven situatie. Voor de beide andere rijen met ingevulde aanbrenger geldt hetzelfde. Bij de klanten met nrs 3123 en 3125 ontbreekt juist de aanbrenger (3001). b De beschreven situatie is niet volledig vast te leggen in de structuur van figuur 1.11. Daarin is immers bepaald dat iedere klant nul of één aanbrenger heeft, terwijl Wiegerink er twee heeft: Pauw en Pootjes. Pauw en Pootjes zullen dus onderling moeten uitmaken wie de eer krijgt de aanbrenger van Wiegerink te zijn. Als we even aannemen dat Pootjes ‘wint’, dan geeft figuur 1.13 de situatie correct weer en is hij bovendien in overeenstemming met het strokendiagram in figuur 1.11.

FIGUUR 1.13

2

a We vullen de voorbeeldpopulatie stap voor stap:  De eerste rij maakt niet uit, dus laten we daarvoor order_ = 11, volgnr = 1 en artikel = ‘a’ kiezen.  De twee brede sleutels geven onder andere aan dat de losse kolommen niet uniek zijn (omdat we alleen de strengste regels tekenen – zie Inleiding informatica/leereenheid 12). Ordernr 11 mag dus nogmaals voorkomen. We beginnen daarom de tweede rij met order_ = 11.  De primaire sleutel over (order_, volgnr) zegt dat volgnr nu niet 1 mag zijn. We kiezen dus volgnr = 2 op de tweede rij.  De alternatieve sleutel over (order_, artikel) zegt dat artikel in deze rij niet ‘a’ mag zijn; we kiezen dus artikel = ‘b’.  Het niet-uniek zijn van order_ hebben we nu gehad, dus laten we op de derde rij kijken naar het niet-uniek zijn van volgnr. We kiezen dus een volgnr dat er al is, zeg 1.  Vanwege de primaire sleutel mogen we nu niet order_ = 11 kiezen, we kiezen dus een nieuw ordernr: 22.  De alternatieve sleutel zegt nu niets over het artikel, dus we kunnen in principe ‘a’, ‘b’ of ‘c’ kiezen. Vanwege het niet-uniek zijn van de kolom artikel is het handig om ‘a’ of ‘b’ te kiezen. Vanwege het ontbreken van een uniciteitsregel over de combinatie (volgnr, artikel) is het handig om ‘a’ te kiezen, omdat we dan meteen laten zien dat (volgnr, artikel) inderdaad niet uniek is.

In figuur 1.14 ziet u het resultaat, dat illustreert dat alle getekende uniciteiten gelden en alle niet-getekende uniciteiten niet gelden.

FIGUUR 1.14

b Iedere factuur hoort bij één order. Op een factuur kunnen meerdere orderregels staan, die ieder een eigen volgnr hebben (dat uniek is binnen die factuur). Op een factuur kunnen meerdere artikelen voorkomen, maar ieder artikel maar op één orderregel (artikel is uniek binnen die factuur). 3

a Vanwege de verwijzing van Orderregel naar Order_ en de daaruit volgende referentiële integriteit wordt eerst gekeken of er ook orderregels bestaan bij order 5773. Dat is zo in deze populatie. Nu wordt gekeken naar de delete-regel die voor deze verwijzing is gespecificeerd: dat is een cascading delete, dus het rdbms gaat proberen om de bijbehorende orderregels ook te deleten. Vanwege de verwijzing van Klacht naar Orderregel moet dan eerst gekeken worden of er klachten zijn over die orderregels. Dat blijkt in deze populatie niet zo te zijn, dus kan het rdbms nu zonder problemen de rij van order 5773 uit de tabel Order_ verwijderen en de drie bijbehorende rijen uit de tabel Orderregel. NB Als er wel een klacht was geweest over een van de orderregels van order 5773, dan zou vanwege de restricted delete op de verwijzing van Klacht naar Orderregel het verwijderen van de rij van order 5773 zijn tegengehouden. b Deze insert wordt tegengehouden door de alternatieve sleutel van de tabel Orderregel: binnen één order mag ieder artikel maar eenmaal voorkomen.

Blok 2

Theorie

Inhoud leereenheid 2

Nulls, strings en logica 45

Introductie Leerkern 1

2 3

45

De aard van nulls 45 1.1 Weergave van nulls 46 1.2 Wat zijn nulls? 47 1.3 De SQL-constante null 47 1.4 Selecteren op null 48 1.5 Rekenen met null 49 1.6 Null, 0 en de lege string 49 1.7 Onbekend of niet van toepassing? 1.8 Persoonsnamen 53 Codd-relationaliteit 54 Logische algebra 56 3.1 Tweewaardige logica 56 3.2 Driewaardige logica 58

Samenvatting Zelftoets

61

62

Terugkoppeling 1 2

64

Uitwerking van de opgaven 64 Uitwerking van de zelftoets 65

44

52

Leereenheid 2

Nulls, strings en logica

INTRODUCTIE

Nulls zijn zoiets als niks, en van niks kun je heel lang wakker liggen. Ook beoefenaars van de relationele databasetheorie zijn er in het verleden door uit hun slaap gehouden. En ze hebben zich het hoofd gebroken over wat nulls precies zijn of zouden moeten zijn. Nulls zijn een indicator voor ‘geen waarde’, zoveel is wel duidelijk. Maar duidt dat erop dat de waarde alleen maar onbekend is en dat hij later misschien toch wordt ingevuld? Of betekent het dat een waarde op die plek helemaal niet van toepassing is? In de eerste paragraaf onderzoeken we de aard van nulls en zullen daarbij ontdekken dat de problematiek meer dan academisch is en heel praktische kanten heeft. In volgende paragrafen behandelen we aspecten van de relationele theorie die nauw met nulls in verband staan:  Codd-relationaliteit, over nulls en primaire sleutels  logische algebra, over het gebruik van nulls in logische expressies waardoor naast true en false een derde logische waarde unknown nodig is. LEERDOELEN

Na het bestuderen van deze leereenheid wordt verwacht dat u – voorbeelden kunt geven van null-problematiek – kunt aangeven welke problemen inherent zijn aan het relationele model van Codd – inzicht hebt in de driewaardige logica – weet welke mogelijkheden SQL kent om met nulls om te gaan. De studielast van deze leereenheid bedraagt 4 uur. Studeeraanwijzing

Voer zoveel mogelijk van de getoonde query’s uit in de meegeleverde SQL-omgeving, en bedenk en probeer gerust ook zelf varianten uit!

LEERKERN 1

De aard van nulls

In deze paragraaf gebruiken we tabel Inschrijving van de voorbeelddatabase OpenSchool, zie het populatiediagram van figuur 2.1. We zijn met name geïnteresseerd in de optionele kolommen cijfer en vrijstelling.

Tabel Inschrijving van de OpenSchool-database

FIGUUR 2.1 1.1

WEERGAVE VAN NULLS

In figuur 2.1, die we zelf hebben gemaakt, laten we de niet-ingevulde plekken echt leeg. Als we echter de inhoud van de tabel Inschrijving opvragen bij het rdbms, wordt in de resultaattabel iedere null weergegeven als , zie voorbeeld 2.1. VOORBEELD 2.1

We vragen de volledige tabel Inschrijving op, met de volgende opdracht: select * from Inschrijving;

select *

Met select * geven we aan dat we van iedere rij alle kolommen willen zien. Deze opdracht geeft de volgende resultaattabel: STUDENT ============ 1 1 1 1 2 2 2 3 4 4

CURSUS ====== II DW DB IM II DW IM II II DB

DATUM CIJFER VRIJSTELLING =========== ======= ============ 2012-01-12 7 N 2012-01-19 5 N 2012-03-18 8 N 2012-06-20 N 2012-01-12 J 2012-01-12 J 2012-01-26 5 N 2012-01-16 2012-01-20 J 2012-02-29 N

Later (in de Muziekdatabase in leereenheid 4) komen we ook weergaven van resultaattabellen tegen met lege plekken. Zo’n lege plek duidt op een lege string, dat is een tekst van lengte 0, zonder tekens. Wel/geen verschil null en lege string (dialectafhankelijk)

In Firebird is een lege string iets anders dan een null. Een lege string is een waarde, terwijl een null een speciale waarde is die eigenlijk een indicator is voor ‘geen waarde’. Het is belangrijk op dit verschil te letten in de resultaattabellen. Er zijn rdbms’en, waaronder Oracle, die dit onderscheid niet maken en een lege string gelijkstellen aan een null. In paragraaf 1.6 zullen we hier dieper op ingaan en zien dat het uit conceptueel oogpunt terecht is onderscheid te maken.

Vanuit presentatieoogpunt hoeven we ons geen zorgen te maken over het verschil tussen null en de lege string. Het gaat hier immers om een tool voor programmeurs en niet voor eindgebruikers. Voor programmeurs is een ‘mooie’ weergave minder belangrijk en kan het nuttig zijn het onderscheid te zien. 1.2

Null

WAT ZIJN NULLS?

Een null wordt vaak omschreven als ‘geen waarde maar een indicator voor het ontbreken van een waarde’. Het is alsof iemand in de kantlijn heeft geschreven ‘hier staat niets’. Dat lijkt simpel, maar is het niet. We noemden net al het verschil tussen null en de lege string (zie paragraaf 1.6), maar er zijn meer dingen om rekening mee te houden:  Met nulls wordt ook ‘gerekend’, met name in SQL. We krijgen bijvoorbeeld te maken met optellingen waarbij een of beide operanden null zijn. We moeten dan weten ‘wat er uitkomt’. Paragraaf 1.5 geeft een aantal voorbeelden.  Wanneer een databaseveld geen waarde heeft, is het belangrijk te weten waarom niet. Gaat het om een onbekend gegeven, dat misschien morgen wel bekend is? Of is het gegeven ‘niet van toepassing’? Dit is het onderwerp van paragraaf 1.7. In de paragrafen 1.3 en 1.4 kijken we eerst naar situaties waarin het voorkomen van nulls geen problemen oplevert. 1.3

DE SQL-CONSTANTE NULL

kent de constante null, zoals in het volgende insert-statement uit het insert-script van de OpenSchool-database (dat is het script waarmee de database zijn eerste inhoud krijgt): SQL

insert into Inschrijving values (1, 'IM', '2012-06-20', null, 'N' );

In voorbeeld 2.2 zien we een van de manieren waarop de constante null kan worden opgenomen in de select-clausule van een select-statement. VOORBEELD 2.2 (gebruik van nullconstante)

In een ‘normaal’ select-statement wordt rij voor rij de brontabel van de from-clausule doorlopen, en voor ieder van die rijen wordt dan ‘iets’ opgeleverd: normaal gesproken één of meer kolomwaarden. Zie voorbeeld 2.1 en voorbeeld 1.4. In plaats daarvan kunnen we echter ook voor iedere rij dezelfde constante laten afdrukken, zoals null: select null from Inschrijving;

Dit nutteloze maar leerzame statement geeft voor elke Inschrijving-rij (tien keer) een null als resultaat: CONSTANT ========

…

De constante null heeft net als een numerieke constante (zoals 3) of een tekstconstante (zoals ‘fiets’) een waarde. Die waarde is de null-waarde! Ook andere expressies kunnen de waarde null hebben, zoals de expressie null + 1. De waarde hiervan is weer null. Interpretatie: wanneer je bij een null-waarde (onbekend? niet van toepassing?) 1 optelt komt er weer null (onbekend? niet van toepassing?) uit. De precieze interpretatie hoort tot het domein van de gebruiker en is niet van belang voor de waarde zelf.

Null-waarde

Null is typeloos De null-waarde in SQL is in veel opzichten een gewone waarde. We zullen nog zien dat er net als voor getallen harde ‘rekenregels’ voor bestaan. In één opzicht is hij bijzonder: hij is typeloos. Er zijn dus geen aparte integer-nulls of varchar-nulls. Hulptabel voor ‘kladblokberekeningen’ Wanneer we, zoals in voorbeeld 2.2, de tabel Inschrijving gebruiken voor expressies (zoals null of null + 1) die niets met inschrijvingen te maken hebben, krijgen we de uitkomst even vaak als er rijen zijn. We zouden beter een tabel kunnen nemen met maar één rij. Elke Firebird-database heeft zo’n tabel, dat is de metatabel Rdb$database met informatie over de database zelf. Deze zullen we in het vervolg ‘misbruiken’ voor kladblokberekeningen. Een voorbeeld: select null + null from Rdb$database; 1.4

SELECTEREN OP NULL

is (not) null

Testen op een null-waarde kan met de clausules is null en is not null.

VOORBEELD 2.3 (selectie op nullwaarde)

Als volgt worden de inschrijvingen geselecteerd waarvoor geen cijfer is ingevuld: select * from Inschrijving where cijfer is null;

En zo krijgen we de inschrijvingen waarvoor wel een cijfer is ingevuld: select * from Inschrijving where cijfer is not null;

Merk op dat is not null een formulering is die dicht tegen de natuurlijke taal (Engels) aanligt. Vanuit de logica zou je verwachten: not (cijfer is null). Dat laatste mag ook, maar is minder gebruikelijk.

is gemodelleerd naar natuurlijke taal. Soms geeft dat ‘soepele’ formuleringen, maar de logische structuur is soms ver te zoeken. Dat geldt overigens ook al voor ‘cijfer is null’. SQL

OPGAVE 2.1

Voorspel het resultaat van het statement select * from Inschrijving where cijfer = null;

Controleer uw voorspelling en probeer het resultaat te verklaren. Let op!

Testen op null gebeurt dus altijd met is, en de constante null wordt dan altijd letterlijk in de query geschreven: cijfer is null. Het vergelijken van twee waarden anders dan de constante null gebeurt altijd met = , zoals in cijfer = 6 of laagsteCijfer = hoogsteCijfer. Het kan dan nog wel gebeuren dat cijfer, laagsteCijfer en/of hoogsteCijfer gelijk aan null blijken te zijn, maar we hebben hier niet de constante null gebruikt. En als cijfer inderdaad gelijk is aan null, dan levert cijfer = 6 als resultaat unknown op (zie paragraaf 3). 1.5

REKENEN MET NULL

Voorbeeld 2.4 illustreert het optellen van twee operanden waarvan er één null kan zijn. Als dat het geval is, is ook het resultaat null, zoals te verwachten is. VOORBEELD 2.4 (optellen met nulloperand)

select cijfer + 1 from Inschrijving;

Resultaat: ADD ============== 8 6 9

6



1.6

NULL, 0 EN DE LEGE STRING

De ‘ontdekking’ van het getal 0 betekende een doorbraak in het menselijk denken. Met wat overdrijving zouden we kunnen zeggen dat rekenen ineens wiskunde werd: een rekensysteem gekoppeld aan het tellen van tastbare dingen werd een calculus met abstracte aspecten. Nog steeds vereist het een mentale krachttoer om het getal 0 als een volwaardig getal ‘net als alle andere’ te accepteren. De gedachte dat 0 hetzelfde zou zijn als ‘niks’, blijft een valkuil met de nodige aantrekkingskracht.

Maar wat te denken van een lege string? Is dat hetzelfde als null, of is het een string als alle andere, zij het dan met lengte 0? Zie figuur 2.2.

FIGUUR 2.2

De lege string: string van lengte 0 of null?

Als het null is, is dat dan in de zin van ‘we weten de waarde niet’ of van ‘niet van toepassing’? En als een lege string een echte string is, kunnen we die als gebruiker dan ook invoeren in een applicatie? Want er moet dan toch verschil zijn tussen geheel open laten (een null) en invullen van die lege string? In voorbeeld 2.5 onderzoeken we dit, aan de hand van een variant van de Student-tabel uit de OpenSchool-database, Student1 genaamd. Zie figuur 2.3. Tabel Student1 maakt deel uit van de OpenSchool-database. Hij wordt alleen in deze paragraaf gebruikt en wordt niet getoond in de diagrammen.

FIGUUR 2.3

Tabel Student1

We hebben als vanzelfsprekend aangenomen dat de kolom voorvoegsel optioneel is, maar is het wel zo eenvoudig? Zouden we niet net zo goed kunnen zeggen dat iedereen een voorvoegsel heeft, maar dat dit voor sommigen een lege string is? VOORBEELD 2.5 (studentnamen OpenSchool)

Wanneer we zeggen: “Inge Berk heeft geen voorvoegsel”, is de consequentie dat we haar een null geven in de kolom voorvoegsel. In het script OpenSchoolInsert.sql vindt u inderdaad: insert into Student1 values (1, 'Inge', null, 'Berk', ‘DAT’);

Het statement: select nr, voornaam, voorvoegsel, naam from Student1 order by nr;

geeft de volgende, op studentnummer geordende naamlijst: NR ========= 1 2 3 4

Concateneren Concatenatie

||

VOORNAAM ======== Inge Max Max Iris

VOORVOEGSEL ===========

van

NAAM ====== Berk Tack Bos Eik

Willen we de namen niet in drie kolommen, maar met spaties aaneengeschreven, dan kunnen we dat bereiken door de kolomwaarden te concateneren (aan elkaar plakken). Daarvoor heeft SQL de concatenatieoperator ||. Het volgende statement lijkt dan geen slechte poging: select

nr, voornaam || ' ' || voorvoegsel || ' ' || naam from Student1 order by nr;

Het resultaat is echter niet wat we willen: NR ========= 1 2 3 4

CONCATENATION =============

Iris van Eik

Alle namen zonder voorvoegsel zijn null geworden. De oorzaak is dat concatenatie met een null een null geeft: 'Inge' || null

(onbekend!)

coalesce

null

(onbekend!)

Firebird vat de null op als ‘onbekende string’. Het resultaat is dan logisch: wanneer we een onbekende string ergens aan vastplakken, is het resultaat onbekend. Dit probleem is op te lossen door de null te converteren naar een lege string, met de null-vervangfunctie coalesce (zie bijlage 1 in deel 2): select

nr, voornaam || ' ' || coalesce(voorvoegsel, '') || ' ' || naam from Student1 order by nr;

Resultaat: NR ========= 1 2 3 4

CONCATENATION ============= Inge Berk Max Tack Max Bos Iris van Eik

Op een schoonheidsfoutje na (twee spaties bij namen met ‘leeg’ voorvoegsel), is dit precies wat we willen. Dat schoonheidsfoutje is er met handig gebruik van coalesce en concatenatie ook nog uit te halen: select

nr, voornaam || ' ' || coalesce (voorvoegsel || ' ', '') || naam from Student1 order by nr;

Waarom ging dit wel goed? Omdat in Firebird het vastplakken van een lege string aan een string weer diezelfde string geeft: 'Inge' || ''

'Inge'

Uit voorbeeld 2.5 blijkt dat, althans voor Firebird, een lege string iets anders is dan null. Wat het getal 0 is voor optellen, is de lege string voor concateneren. Conceptueel gezien is dit correct. Wees er echter op attent dat dit dialectafhankelijk is. In Oracle bijvoorbeeld is er géén verschil tussen null en de lege string. Daarbij gedraagt de Oracle-null zich bij concatenatie zoals de lege string bij Firebird. In Oracle zouden we dus in voorbeeld 2.5 de coalesce-functie niet nodig hebben. Uit het voorgaande kunnen we concluderen dat het in principe mogelijk is het voorvoegsel verplicht te maken en ingeval van ‘geen voorvoegsel’ een lege string in de database op te nemen. Bij het uitlezen van de Student1-tabel via een select-statement hoeven we dan niet de coalesce functie toe te passen. Maar hoe vullen we een lege string in? Direct met SQL is dat niet zo moeilijk, het insert-statement voor Inge Berk wordt dan: ‘’

insert into Student1 values (1, 'Inge', '', 'Berk', null);

Maar via een grafische applicatie? Als de gebruiker ‘niks’ invult, moet aan de database een lege string worden doorgegeven. Dat komt ook weer neer op het toepassen van een null-vervangfunctie. Nu echter aan de clientkant, door de applicatieprogrammatuur. ‘Nullologie’ Nulls zijn een bron van problemen voor SQL-programmeurs en voer voor ‘nullologen’ (met dank aan Date, die deze term introduceerde). Ter illustratie: terwijl Microsoft’s Ms Sql Server de lege string net zo opvat als Firebird (dus als een not-null, die bij concatenatie met een andere string die string zelf geeft), is bij Oracle een lege string een null, maar concatenatie van een lege string (en van elke andere null) met een string geeft ook daar die string zelf. 1.7

ONBEKEND OF NIET VAN TOEPASSING?

We hebben het gehad over de interpretatie van null als ‘onbekend’ of als ‘niet van toepassing’. Dat onderscheid lijkt mooier dan het is, zoals we zullen toelichten in het volgende voorbeeld.

VOORBEELD 2.6 (interpretatie van nulls)

De tabel Inschrijving heeft twee optionele kolommen: cijfer en vrijstelling. We nemen nu een voorbeeld van een niet-ingevuld cijfer: dat voor student 1 voor cursus IM. Zie figuur 2.4.

FIGUUR 2.4

Onbekend of niet van toepassing

Hoe moeten we deze null-waarde interpreteren? Omdat deze student geen vrijstelling heeft, houden we het erop dat het cijfer nog onbekend is (althans aan het systeem), maar dat er in de toekomst wel een cijfer verwacht wordt. Bij student 2 voor cursus II is een cijfer niet van toepassing, vanwege vrijstelling. Voor student 3 is ook geen cijfer ingevuld voor cursus II. Hier weten we echter niets over een vrijstelling. Dus is het onbekend of het cijfer onbekend is (op dit moment), of dat het niet van toepassing is. Semantisch moeras Codd heeft in zijn latere jaren pogingen gedaan om allerlei betekenisvarianten van ‘onbekend’, ‘niet van toepassing’, ‘onbekend of onbekend’, enzovoort, in een logisch systeem onder te brengen. Het leidde hem in een ‘semantisch moeras’. Wij gaan uit van het standpunt dat in de relationele theorie alleen plaats is voor ‘kale null-waarden’ en niet voor enige vorm van interpretatie. De interpretatie is iets voor de gebruiker, indien deze daar behoefte aan heeft. In paragraaf 3 ‘Logische algebra’ gaan we ‘rekenen’ met die niet-ingevulde waarden, los van enige interpretatie. 1.8

PERSOONSNAMEN

Personen hebben onder meer een voornaam, voorletters, een achternaam en eventueel een voorvoegsel. Om deze onafhankelijk van elkaar te kunnen manipuleren (denk aan naamlijsten, de aanhef van brieven, enzovoort), hoort voor elk apart een databasekolom te worden gereserveerd. Uit de tekst blijkt dat het werken met naamcomponenten nogal wat problemen kan opleveren, die te maken hebben met de wijze waarop nulls in het gebruikte SQL-dialect worden behandeld: – Hoe wordt een null behandeld bij concatenatie? – Is er onderscheid tussen een null en een lege string? – Is het wel zo vanzelfsprekend om ‘voorvoegsel’ optioneel te maken?

De SQL-query’s in deze cursus willen we zoveel mogelijk onafhankelijk houden van deze nogal technische en dialectafhankelijke problematiek. Dat is een van de redenen waarom we voor persoonsnamen in veel voorbeelden maar één kolom hebben gereserveerd. Zo vermijden we ook dat de query’s en resultaattabellen onnodig complex worden. We vertrouwen erop dat de lezer die het geleerde in de praktijk toepast, altijd aparte kolommen zal definiëren voor elke naamcomponent. 2

Codd-relationaliteit

Primaire sleutels centraal Codd heeft zijn theorie gebaseerd op primaire sleutels. In zijn klassiek geworden ‘normalisatietheorie’ (waarop we in leereenheid 3 uitgebreid ingaan) worden primaire sleutels en Alternatieve sleutels verschillend (asymmetrisch) behandeld, hoewel ze overeenkomstige eigenschappen hebben en het enige (maar belangrijke) verschil niet structureel is maar een vorm van uitverkiezing: de primaire sleutel is de kandidaatsleutel waarop verwijzingen worden gebaseerd. Ter illustratie van de asymmetrie: in de oorspronkelijke theorie van Codd kan het van de keuze van de primaire sleutel afhangen wat de hoogste ‘normaalvorm’ is waaraan een tabel voldoet. In veel moderne varianten van de normalisatietheorie is Codds asymmetrie opgeheven, doordat de theorie zich baseert op kandidaatsleutels in plaats van op primaire sleutels. Onomstreden is dit niet: er is veel te zeggen voor Codds uitgangspunt om alleen naar primaire sleutels te kijken en alternatieve sleutels te beschouwen als aanvullende constraints die niet bepalend zijn voor de juiste ‘genormaliseerde’ structuur. Kandidaatsleutels en SQL Opmerkelijk is hoe verschillende SQL-dialecten omgaan met het verschijnsel kandidaatsleutel. Noch de standaard noch enig dialect ondersteunen dit begip rechtstreeks. Alle ondersteunen ze primaire sleutels (via een primary key-clausule in het create table-statement). In veel dialecten zijn dit de enige sleutels waarop verwijzingen kunnen worden gebaseerd. In een references-clausule hoeft dan soms alleen de oudertabelnaam te worden opgegeven. In onder meer Oracle kunnen we een verwijssleutel ook naar een alternatieve sleutel laten verwijzen.

Codd-relationaliteit

Codd-relationaliteit De definitie van kandidaatsleutel (zie Inleiding informatica, leereenheid 12 ‘Relationele databases: regels’) impliceert voor elke rij een not-null-eis. Bij een brede sleutel, over twee of meer kolommen, impliceert dit dat in een rij niet alle sleutelkolomwaarden null mogen zijn. Conceptueel gezien is er echter niets op tegen dat een of zelfs meerdere kolomwaarden van een kandidaatsleutel null zijn, zolang er maar minstens één not-null is. En daarnaast moet natuurlijk voldaan zijn aan de eis van identificatie: de combinatie van sleutelwaarden moet uniek zijn. De theorie van Codd is echter strenger en gaat uit van een not-null-eis voor elke sleutelkolom. We duiden deze eis aan met Codd-relationaliteit.

VOORBEELD 2.7 (niet-Coddrelationele tabel)

Een muziekschool wil in een relationele databasetabel Instrument administreren welke docent de lessen voor een bepaald instrument coördineert. Zie figuur 2.5.

FIGUUR 2.5

Tabel Instrument is niet-Codd-relationeel

Alle instrumenten hebben een naam. Voor sommige is dat voldoende ter identificatie, zoals voor piano, gitaar en drums. Ook voor ‘fluit’ is dat voldoende (met fluit zonder meer wordt een sopraandwarsfluit aangeduid). Dit geldt analoog ook voor de viool. Bij de saxofoonfamilie echter wordt altijd een toonhoogte toegevoegd. Wat is nu de primaire sleutel? Er is maar één kandidaat: de combinatie van naam en toonhoogte. Als combinatie is deze not-null, hoewel toonhoogte niet hoeft te zijn ingevuld. De tabel Instrument is dus nietCodd-relationeel. Vrijwel alle commerciële SQL-databases gaan uit van Codd-relationaliteit. Vanuit praktisch oogpunt is daar veel voor te zeggen, omdat nulls vaak problemen geven. Niettemin laat de praktijk voorbeelden zien waarbij Codd-relationaliteit helemaal niet voor de hand ligt. In die gevallen moet er iets verzonnen worden, bijvoorbeeld een of andere defaultwaarde invullen op de plek die ‘eigenlijk’ null is. Het probleem kan worden omzeild door een kunstmatige primaire sleutel te introduceren. Deze is immers per definitie Codd-relationeel. De sleutel met de optionele kolom (of kolommen) wordt dan een alternatieve sleutel. Maar ook dat hoeft niet zonder slag of stoot te lukken: afdwingen van uniciteit gaat vaak ongevraagd gepaard met afdwingen van not-null. Het zou kunnen zijn dat het alleen lukt met aanvullend programmeerwerk. Na het bestuderen van leereenheid 12 (over triggers onder andere) kunt u elke situatie het hoofd bieden. Overigens is een alternatieve sleutel met een optionele kolom in Firebird geen probleem: kolommen waarop een unique constraint geldt mogen nulls bevatten. OPGAVE 2.2

Het rdbms van de muziekschool eist Codd-relationaliteit. Ziet u kans om de toonhoogte verplicht te maken of leidt dit tot onoverkomelijke problemen?

3

Logische algebra

In voorbeeld 1.5 hebben we ‘gerekend’ met de logische operator and. Het ging daarbij impliciet om het true (waar) zijn of false (onwaar) zijn van een selectieconditie, in relatie met het true of false zijn van de afzonderlijke condities. De regels voor logische operatoren, werkend op operanden die true zijn of false, vormen onderdeel van wat de logische algebra wordt genoemd. In deze paragraaf zullen we deze logische algebra uitbreiden met twee logische operatoren: or en not, en met een derde logische waarde: unknown. Aan de waarde unknown is behoefte omdat nulls in een database soms tot gevolg hebben dat een logische expressie een onbekende of onbepaalde uitkomst heeft.

Logische algebra

3.1

TWEEWAARDIGE LOGICA

De gewone logica met als enige logische constanten true en false (of hun equivalenten waar en onwaar) is een tweewaardige logica. Hierop werken drie logische operatoren: and, or en not, volgens de regels in de volgende waarheidstabellen: Waarheidstabel

not

not true not false

and

true true false false true true false false

or

  and and and and or or or or

false true true false true false true false true false

       

true false false false true true true false

De operator not heet de negatie ofwel ontkenning, de operator and heet de conjunctie en de operator or heet de disjunctie. We zien: – not keert de logische waarde van zijn operand om. – and levert true op als beide operanden true zijn, en anders false. – or levert false op als beide operanden false zijn, en anders true.

Negatie Conjunctie Disjunctie

Door combinatie van de operatoren and, or en not kunnen complexe logische expressies worden gevormd. Net als bij numeriek rekenen hebben we daarbij met prioriteit (voorrangsregels) van operatoren te maken: not heeft de hoogste prioriteit, daarna komt and en or heeft de laagste prioriteit. Via haakjes kan van de prioriteitsvolgorde worden afgeweken.

Prioriteit

VOORBEELD 2.8 (prioriteit van logische operatoren)

select student, cursus, cijfer, vrijstelling from Inschrijving where not (vrijstelling = 'J' or cijfer > 5);

Voor de geselecteerde rijen geldt dat het volgende níet het geval is: vrijstelling = ‘J’ en/of cijfer > 5 . Dat wil zeggen (uitgaande van rijen zonder nulls!): vrijstelling = 'N' en cijfer 5;

Resultaat: STUDENT ========= 1 1 1 1 2 4

CURSUS ====== II DW DB IM IM DB

CIJFER ====== 7 5 8

5

VRIJSTELLING ============ N N N N N N

Omdat not voorrang heeft op or, wordt nu geëist: vrijstelling = 'N' en/of cijfer > 5. Regels van De Morgan De logica kent allerlei regels of ‘wetten’. We noemen hier twee regels die voor de praktijk bijzonder belangrijk zijn: de regels van De Morgan. De ene regel heeft betrekking op de ontkenning van een conjunctie, de tweede op de ontkenning van een disjunctie:

Regels van De Morgan

not (conditie1 and conditie2) not (conditie1 or conditie2)

 

not conditie1 or not conditie2 not conditie1 and not conditie2

Het teken  is het teken voor logische equivalentie: de bewering links is alleen true als de bewering rechts true is, en omgekeerd.

Logische equivalentie VOORBEELD 2.9 (regels van De Morgan)

De volgende logische expressie (uit voorbeeld 2.8, eerste statement): where not(vrijstelling = 'J' or cijfer > 5)

is equivalent met: not vrijstelling = 'J' and not cijfer > 5

en dus ook met: vrijstelling = 'N' and cijfer 5;

Immers: null > 5 geeft geen true en geen false. De logische uitkomst van null > 5 is dus onbepaald, net als die van de ontkenning not (null > 5). De meest simpele manier om hiermee om te gaan is de introductie van een derde logische waarde: unknown. Dit geeft een driewaardige logica, met drie logische waarden: true, false en unknown.

Logische waarde ‘onbepaald’ unknown Driewaardige logica Belangrijk

Alléén rijen waarvoor de where-conditie true oplevert doorstaan de test. Dus: ‘where unknown’ heeft hetzelfde effect als ‘where false’. De werking van de operatoren not, and en or in de driewaardige logica is af te lezen uit de volgende waarheidstabellen, die een uitbreiding zijn van die van de tweewaardige logica:   

not

not true not false not unknown

and

true true true false false false unknown unknown unknown

and and and and and and and and and

true false unknown true false unknown true false unknown

        

true false unknown false false false unknown false unknown

or

true true true false false false unknown unknown unknown

or or or or or or or or or

true false unknown true false unknown true false unknown

        

true true true true false unknown true unknown unknown

false true unknown

Toelichting  Wanneer we niet weten of een bewering true is of false (een logische unknown), weten we evenmin of de ontkenning true is of false. Dit verklaart waarom ‘not unknown’ weer unknown oplevert.  Evenzo: wanneer we van twee beweringen weten dat er één true is, maar van de ander niet weten of zij true is of false, dan kunnen we van de samenstelling met and noch true noch false als uitkomst afleiden. Dit verklaart waarom ‘true and unknown’ als uitkomst unknown oplevert.  Wanneer we echter van een samenstelling met ‘or’ weten dat minstens een van de twee beweringen true is, dan weten we genoeg: de uitkomst is true, ook al is de logische waarde van de andere bewering onbekend of onbepaald. Dit verklaart waarom ‘true or unknown’ een true oplevert.  Ook van de overige samenstellingen met unknown is de logische uitkomst op deze manier te verklaren. Zodoende geeft het volgende statement: select student, cursus, cijfer, vrijstelling from Inschrijving where vrijstelling = 'J' or not cijfer 5 (ongeacht de waarde van vrijstelling en of die is ingevuld): STUDENT ========= 1 1 2 2 4

CURSUS ====== II DB II DW II

CIJFER ====== 7 8



VRIJSTELLING ============ N N J J J

Zijn zowel vrijstelling als cijfer niet ingevuld, dan staat er dus eigenlijk in de conditie: null = ‘J’ or not null