Holland Casino Eredivisie

Holland Casino Eredivisie

bladzijde 1


1

De formulering van het probleem

Om aan te tonen welke ongekende mogelijkheden de Jet Engine biedt en om S(impel)QL aan een grondige test te onderwerpen is geprobeerd een database te maken, bedoeld om de voetbaluitslagen in op te nemen zoals de KNVB die op haar website publiceert. Daarbij is uitgegaan van de Holland Casino Eredivisie van het voetbalseizoen 2004/05 waarbij de wedstrijduitslagen verwerkt zijn tot en met 10 februari 2005. Niet omdat dit zomaar een datum is, maar omdat de stand op die datum nog een punt van zorg openbaart omdat er iets bijzonders aan de hand is. De stand van de competitie op 10 februari volgens de KNVB

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

Elftal PSV AZ Ajax Feyenoord Vitesse FC Utrecht SC Heerenveen Roda JC RKC Waalwijk NAC FC Twente FC Groningen Willem II NEC ADO Den Haag RBC Roosendaal De Graafschap FC Den Bosch

G 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

W 16 15 12 10 10 9 9 8 7 7 7 7 7 4 5 3 2 2

GL 3 4 5 5 3 5 5 5 6 6 4 4 4 7 4 2 5 3

V 1 1 3 5 7 6 6 7 7 7 9 9 9 9 11 15 13 15

Punten 51 49 41 35 33 32 32 29 27 27 25 25 25 19 19 11 11 9

DPV 54 49 44 50 34 25 34 36 26 28 24 28 25 22 23 21 21 11

DPT 12 14 19 27 27 21 33 31 27 40 26 36 33 28 31 51 51 48

We willen deze stand zelf kunnen maken aan de hand van de wekelijkse uitslagen van gespeelde wedstrijden. Die uitslagen – weer is de bron de KNVB – zijn in de volgende vorm gegoten:


Datum 06.02.05 06.02.05 06.02.05 06.02.05 05.02.05 05.02.05 04.02.05 04.02.05 …

bladzijde 2

Thuis RKC Waalwijk - 1 FC Utrecht - 1 Ajax - 1 Vitesse - 1 SC Heerenveen - 1 ADO Den Haag - 1 Willem II - 1 De Graafschap - 1 …

Uit FC Groningen - 1 NAC - 1 FC Twente - 1 Roda JC - 1 AZ - 1 FC Den Bosch - 1 NEC - 1 Feyenoord - 1 …

Uitslag 3-0 2-0 1-2 3-0 1-3 2-0 3-1 2-7 …

De tabel is niet zo geschikt als hij op het eerste gezicht lijkt. Het gebruikte datumformat is voor sql ongeschikt en de laatste kolom is bij voorkeur in tweeën te splitsen. Bovendien kunnen we de aanduiding dat het om het eerste elftal gaat wel missen. Het omzetten van de oorspronkelijk gepubliceerde tabel naar een voor ons doel geschikt format is een kwestie van ETL (extraction, transformation en loading). Laten we ons daarmee hier onze hersens niet pijnigen. We doen net alsof de data van de gespeelde wedstrijden aangeleverd worden als:

datum 06-02-05 06-02-05 06-02-05 06-02-05 05-02-05 05-02-05 04-02-05 04-02-05 02-02-05 30-01-05 …

thuis RKC Waalwijk FC Utrecht Ajax Vitesse SC Heerenveen ADO Den Haag Willem II De Graafschap PSV NEC …

uit FC Groningen NAC FC Twente Roda JC AZ FC Den Bosch NEC Feyenoord RBC Roosendaal FC Utrecht …

dpv 3 2 1 3 1 2 3 2 4 0 …

dpt 0 0 2 0 3 0 1 7 1 0 ...

De opdracht die we ons in deze kleine en simpel ogende casus stellen is tweeledig. We willen • een database realiseren die alle valkuilen tot inconsistentie van gegevens vermijdt en • op een willekeurige datum de stand van de competitie kunnen produceren Alle uitslagen tot en met 10 februari staan in het script wedstrijd.sql. Voordat we dat script op onze database los kunnen laten moeten we eerst de database maken. Dat is met behulp van S(impel)Ql een peulenschil. We besluiten de database naar de Holland Casino Eredivisie te vernoemen en gaan vervolgens denken over een zodanig veilig ontwerp dat aan het eerste aspect van de opdracht voldaan is. Daarna proberen we ook het tweede deel van de opdracht te realiseren waarbij we de regel hanteren dat elke gewonnen wedstrijd drie wedstrijdpunten en elke gelijkgespeelde wedstrijd één wedstrijdpunt oplevert. Verloren wedstrijden dragen niet bij aan het totaal.


2

bladzijde 3

De bedrijfsregels op een rijtje

Het lijkt zo simpel; maar om straks fouten bij invoer van de rest van de gespeelde wedstrijden te voorkomen is meer nodig dan op het eerste gezicht vermoed kan worden. Er schuilen behoorlijk wat addertjes onder het gras. We beginnen met het formuleren van een aantal bedrijfsregels die de KNVB hanteert en die we – om in de komende paragrafen gemakkelijk naar deze regels te kunnen verwijzen – van een nummering voorzien hebben. B.1 B.2 B.3 B.4 B.5 B.6 B.7

De speeldata moeten binnen het seizoen vallen. Dat wil voor het seizoen 2004/05 zeggen tussen 13-08-2004 en 22-05-2005. Alleen clubs uit de eredivisie mogen als thuis- of uitploeg worden opgenomen. Een club speelt maar één keer tegen elke tegenstander een thuiswedstrijd. Een club speelt maar één keer tegen elke tegenstander een uitwedstrijd (overbodige regel die volledig wordt afgedekt door de vorige!). Een club kan op één dag slechts één wedstrijd spelen. Voor het aantal doelpunten (zowel voor als tegen) mag nooit een negatief getal genoteerd worden. Alle gegevens zijn noodzakelijk; er mag geen enkel gegeven ontbreken.

Nou was laatst op de radio te vernemen dat Feyenoord hoofdzakelijk tegen zichzelf liep te voetballen... Misschien moet je zo'n opmerking niet al te letterlijk nemen maar hij roept wel de vraag op of we niet als bedrijfsregel moeten formuleren dat de thuisclub een andere moet zijn dan de uitclub. Met andere woorden: moeten we niet uitsluiten dat de thuisspelende club dezelfde is als de uitspelende? Als we de situatie wat grondiger bekijken blijkt een dergelijke regel overbodig te zijn. De regel dat een club maar één wedstrijd per dag kan spelen laat zich splitsen in een drietal – op het eerste gezicht onnodig ingewikkeld geformuleerde – regels. B.5.1 Een club speelt per dag hooguit één keer thuis. B.5.2 Een club speelt per dag hoogstens één keer uit. B.5.3 Een club speelt op dezelfde dag nooit zowel een thuis- als een uitwedstrijd. En B.5.3 sluit nou precies een wedstrijd FC Groningen tegen FC Groningen uit. We gaan er gemakshalve aan voorbij dat er zich bijzondere situaties kunnen voordoen zoals wedstrijden die onderbroken worden en op een andere dag worden uitgespeeld of het bij wijze van strafmaatregel drie wedstrijdpunten in mindering brengen. Dergelijke situaties vragen om ad hoc oplossingen waar we hier geen boodschap aan hebben. Het zal blijken dat zonder al die bijzonderheden het probleem al groot genoeg is.


3

bladzijde 4

Alle begin is (niet) moeilijk

Het idee van een tabel begint met een kolomindeling waarbij we de kolommen een naam en een type toebedelen. Bovendien moeten we een naam voor de tabel bedenken. De keuze is gevallen op de tabelnaam wedstrijd terwijl de kolomnamen datum, thuis, uit, dpv en dpt voor de hand liggen na lezing van het voorafgaande. Als kolomtypen kiezen we – in dezelfde volgorde – DATE, TEXT(20), TEXT(20), INTEGER en INTEGER. En vervolgens dienen de beperkingsregels of constraints zich aan waarmee we de vastgestelde bedrijfsregels moeten afdekken. We hebben achtereenvolgens in sql de beschikking over constraints in de vorm van de PRIMARY KEY, de FOREIGN KEY, de NOT NULL-, de UNIQUE- en de CHECK-constraint. Met deze vijf zullen we de klus moeten klaren. Gewapend met de in jaren opgebouwde kennis komen we al snel tot een constructie waarmee aan het merendeel van de bedrijfregels is voldaan

CREATE TABLE wedstrijd (datum DATE NOT NULL, thuis TEXT(20), uit TEXT(20), dpv INTEGER NOT NULL, dpt INTEGER NOT NULL, PRIMARY KEY (thuis, uit), UNIQUE (datum, thuis), UNIQUE (datum, uit), CHECK (dpt >= 0), CHECK (dpv >= 0), CHECK (datum BETWEEN #13-08-04# AND #22-05-05#)); script1.sql

Ga – als u het effect van dit script wilt proberen – na of u de datumconversie van S(impel)QL gebruikt. Omdat de achtste van de dertiende en de vijfde van de tweeëntwintigste toevallig niet geldig zijn zal Jet SQL aan deze data de juiste draai geven. Maar dat geldt zeker niet voor alle andere in dit datumformaat genoteerde data. De bedrijfsregels waaraan door dit script tegemoet gekomen wordt zijn B.1, B.3, B.4, B5.1, B.5.2, B.6 en B.7. Blijven B.2 en B.5.3 over.


4

bladzijde 5

Een kleine aanpassing

Nog een opmerking over de eerste bedrijfsregel. Als we in de wedstrijdentabel een wedstrijddatum invullen moet die – volgens het scriptje hierboven – liggen tussen de twee seizoenuitersten. Dat betekent dat we in maart alvast de uitslagen van april en mei kunnen invoeren. En dat is onzinnig. Dus proberen we het uit te sluiten door als bovengrens de systeemdatum op te nemen. We kunnen nog wel de wedstrijdtabel met terugwerkende kracht bijwerken maar geen voorschot op toekomstige wedstrijduitslagen nemen. Om de systeemdatum te kunnen gebruiken moeten we een klein uitstapje maken naar de VBA-functies waar Jet SQL uitstekend mee overweg kan. S(impel)QL trouwens ook, wat alleen al te zien is aan de manier waarop de syntax herkend wordt. Om gesjoemel met wedstrijden zoveel mogelijk tegen te gaan is een kleine aanpassing van het script nodig waarbij we de bovengrens van het seizoen vervangen door de huidige datum. VBA kent hiervoor de functie DATE().

DROP TABLE wedstrijd; CREATE TABLE wedstrijd (datum DATE NOT NULL, thuis TEXT(20), uit TEXT(20), dpv INTEGER NOT NULL, dpt INTEGER NOT NULL, PRIMARY KEY (thuis, uit), UNIQUE (datum, thuis), (datum, uit), UNIQUE CHECK (dpt >= 0), CHECK (dpv >= 0), (datum BETWEEN #13-08-04# AND DATE())); CHECK script2.sql

Om het geheel te kunnen testen is een script ontworpen om de wedstrijdtabel te vullen. In het script (zie volgende bladzijde) wordt gepoogd alle bedrijfsregels met voeten te treden. Slechts de eerste twee wedstijden verdienen te worden geaccepteerd; het zijn immers de echte uitslagen van onlangs gespeelde voetbalpartijtjes. Als we de zaak aan een test onderwerpen blijkt er nog een aantal onmogelijke wedstrijden door de mazen van de constraints te glippen. Daar gaan we in de komende paragrafen iets aan doen maar eerst volgt een minimale aanpassing waar we straks de vruchten van plukken.

Behalve van de VBA functie DATE() ontmoeten we in dit artikel nog twee van dergelijke functies in de vorm van CSTR(<arg>) en IIF(, <arg1>,<arg2>). CSTR (convert to string) promoveert zowat elk argument tot een tekst. Of we nu een datum of een getal als argument vermelden, het resultaat is steeds een weergave van het argument in tekstuele vorm. De functie IIF (immediate if) kennen we ongetwijfeld van MS Excel en heeft als functieresultaat de waarde van arg1 of arg2, afhankelijk of de voorwaarde bevestigend of ontkennend beantwoord wordt.


DELETE * FROM wedstrijd; INSERT INTO wedstrijd VALUES (#29-01-05#, 'FC Twente', 'Feyenoord', 0, 0); INSERT INTO wedstrijd VALUES (#23-01-05#, 'Feyenoord', 'Vitesse', 1, 2); INSERT INTO wedstrijd VALUES (#16-05-02#, 'AZ', 'Ajax', 0, 0); INSERT INTO wedstrijd VALUES (#16-05-05#, 'FC Heerenveen', 'PSV', 3, 0); INSERT INTO wedstrijd VALUES (#23-08-05#, 'FC Twente', 'AZ', 1, 1); INSERT INTO wedstrijd VALUES (#11-11-04#, 'Sparta', 'AZ', 2, 1); INSERT INTO wedstrijd VALUES (#14-10-04#, 'FC Twente', 'Vittese', 1, 1); INSERT INTO wedstrijd VALUES (#03-02-05#, 'FC Twente', 'Feyenoord', 1, 5); INSERT INTO wedstrijd VALUES (#29-01-05#, 'PSV', 'FC Twente', 1, 2); INSERT INTO wedstrijd VALUES (#29-01-05#, 'Feyenoord', 'PSV', 1, 0); INSERT INTO wedstrijd VALUES (#23-11-04#, 'NAC', 'NAC', 0, 1); INSERT INTO wedstrijd VALUES (#13-12-04#, 'Roda JC', 'RKC Waalwijk', 3, -1); INSERT INTO wedstrijd VALUES (#11-09-04#, 'FC Heerenveen', 'NEC', -6, 0); INSERT INTO wedstrijd VALUES (#10-10-04#, 'AZ', 'Willem II', 3, NULL); INSERT INTO wedstrijd VALUES (#06-09-04#, 'Roda JC', 'NAC', NULL, 1); INSERT INTO wedstrijd VALUES (#02-12-04#, 'De Graafschap', NULL, 3, 1); INSERT INTO wedstrijd VALUES (#18-08-04#, NULL, 'Feyenoord', 3, 1); INSERT INTO wedstrijd VALUES (NULL, 'ADO Den Haag', 'RBC Roosendaal', 1, 0);

bladzijde 6

-- correct -- correct -- B.1 -- B.1 DATE() -- B.1 -- B.2 -- B.2 -- B.3, B4 -- B.5.1 -- B.5.2 -- B.5.1, B.5.2 -- B.6 -- B.6 -- B.7 -- B.7 -- B.7 -- B.7 -- B.7 script3.sql

We zijn al een heel eind maar het moeilijkste deel moet nog komen!


5

bladzijde 7

Foutmeldingen

Bij de test die we natuurlijk met behulp van script3 uitvoeren komen een paar rijen ten onrechte in de wedstrijdentabel terecht. Veel meer tot tevredenheid stemt het dat de meeste door het script voorgestelde rijen als gevolg van de constraints die al zijn aangebracht worden geweerd uit de database. Minder fraai is de manier waarop we als gebruiker hiervan op de hoogte worden gesteld. Niet iedereen is in staat zinnen als "Een of meer waarden zijn strijdig met de validatieregel Check_2658B1D7_1DDC_4AD5 die is ingesteld voor wedstrijd." te waarderen. Het lezen – laat staan begrijpen – van hexadecimale aanduidingen is niet voor iedereen weggelegd. Wel aardig van het systeem is dat de wetmatigheid die we overtreden blijkbaar een CHECK-constraint is (maar dat zagen we ook al aan de veel vriendelijker aandoende term validatieregel). We kunnen voor constraints die we meegeven zelf een naam bedenken waarbij we waarschijnlijk nooit Check_2658B1D7_1DDC_4AD5 zullen kiezen. Het hoeft niet, maar foutmeldingen worden begrijpelijker. Bovendien – en daarmee lopen we een klein beetje vooruit op wat nog gaat komen – hebben we die naam soms echt nodig en moeten we hem zelfs intikken. Zelf een naam geven kan met het woordje CONSTRAINT, gevolgd door een zelf gekozen aanduiding. Het aangepaste script laat zien hoe dat moet.

DROP TABLE wedstrijd; CREATE TABLE wedstrijd (datum DATE NOT NULL, thuis TEXT(20), uit TEXT(20), dpv INTEGER NOT NULL, dpt INTEGER NOT NULL, CONSTRAINT c1 PRIMARY KEY (thuis, uit), CONSTRAINT c2 UNIQUE (datum, thuis), CONSTRAINT c3 UNIQUE (datum, uit), CONSTRAINT c4 CHECK (dpv >= 0), CONSTRAINT c5 CHECK (dpt >= 0), CONSTRAINT c6 CHECK (datum BETWEEN #13-08-04# AND DATE())); script4.sql

Opnieuw script3 uitvoeren levert al heel wat vriendelijker foutmeldingen. Eventueel kunnen ook de NOT NULL-constraints van een naam voorzien worden. Verboden is het niet maar de naam zal zelden nodig zijn en het script wordt er niet leesbaarder op.


6

bladzijde 8

Referentiële integriteit

We keren terug naar het eigenlijke probleem waar nog twee bedrijfsregels openstaan. Bedrijfsregel B2 is op zich niet moeilijk en laat zich naar keuze verwezenlijken door een CHECK-constraint of een FOREIGN KEY. In beide gevallen hebben we een extra tabel nodig waarin de namen van de elftallen staan die in de wedstrijdtabel mogen voorkomen. We noemen deze tabel eredivisie en laten hem precies uit één kolom club bestaan waarvoor we het type TEXT(20) kiezen. De enige kolom doet tevens dienst als sleutel (die hebben we nodig om in de wedstrijdentabel een FOREIGN KEY te definieren). Als we de keuze hebben tussen een FOREIGN KEY en een CHECK-constraint gaat de voorkeur sterk uit naar de eerste. Een foreign key is nou eenmaal als index gedefinieerd en een check-constraint niet. En juist die index zorgt voor de nodige snelheid. Omdat we de uitslagentabel laten verwijzen naar de eredivisieclubs moeten we als eerste de tabel met de namen van die clubs creëren. We hebben de tabel erg summier gehouden. De structuur van de tabel is dan ook allesbehalve ingewikkeld te noemen.

CREATE TABLE eredivisie (club TEXT(20), PRIMARY KEY (club)); script5a.sql

Met eenvoudige toevoegqueries laat de tabel zich vullen.

INSERT INTO eredivisie VALUES('ADO Den Haag'); INSERT INTO eredivisie VALUES('Ajax'); ... script5b.sql

Voor de overige zestien eredivisieclubs zijn overeenkomstige regels te bedenken. Om tikwerk te voorkomen is in een combinatie van beide scripts in de vorm van script5.sql voorzien. Nu terug naar de wedstrijdentabel en daarin met twee foreign keys afdwingen dat het om clubs uit de eredivisie gaat. Waarom we die foreign keys ON UPDATE CASCADE (trapsgewijs bijwerken) hebben gedefinieerd leggen we later uit. En waarschijnlijk ziet u ook niet waarom we de rijen van de eredivisietabel gedurende het lopende seizoen zouden willen bijwerken...


bladzijde 9

DROP TABLE wedstrijd; CREATE TABLE wedstrijd (datum DATE NOT NULL, thuis TEXT(20), uit TEXT(20), dpv INTEGER NOT NULL, dpt INTEGER NOT NULL, CONSTRAINT c1 PRIMARY KEY (thuis, uit), CONSTRAINT c2 UNIQUE (datum, thuis), CONSTRAINT c3 UNIQUE (datum, uit), CONSTRAINT c4 CHECK (dpv >= 0), CONSTRAINT c5 CHECK (dpt >= 0), CONSTRAINT c6 CHECK (datum BETWEEN #13-08-04# AND DATE()), CONSTRAINT c7 FOREIGN KEY (thuis) REFERENCES eredivisie(club) ON UPDATE CASCADE, CONSTRAINT c8 FOREIGN KEY (uit) REFERENCES eredivisie(club) ON UPDATE CASCADE); script6.sql

Natuurlijk probeert u weer of de onmogelijke uitslagen van script3 geaccepteerd worden.

Het zijn inderdaad minder foute uitslagen dan voorheen: de wedstrijden van Sparta en Vittese zijn geweigerd! We schieten al een heel eind op maar zitten nog steeds met de vijfde bedrijfsregel in onze maag. We zijn nog steeds in staat een club als Feyenoord twee keer op dezelfde dag te laten voetballen.


7

bladzijde 10

Vervolmaking

Natuurlijk hebben we het moeilijkste tot het laatst bewaard. Maar misschien valt het wel mee. In Jet SQL is het mogelijk om check-constraints gegevens op allerlei manieren te laten valideren. Bij andere database management systemen zijn dit soort constraints vaak minder flexibel en mag bijvoorbeeld de validatie alleen betrekking hebben op de gegevens in de rij die op dat moment bewerkt wordt. Op die manier hebben we ook de constraints c4, c5 en c6 ingezet. Op het moment van validatie zijn alleen gegevens uit de rij zelf betrokken. Jet SQL kan echter op dat moment ook andere gegevens raadplegen. Uit de tabel zelf waarin de validatieregel een rol vervult, maar ook uit andere tabellen of zelfs verenigingen van tabellen (joins). We hebben daar in de vorige paragraaf al een beetje op gezinspeeld toen we beweerden dat de referentiële integriteit ook met een check-constraint kan worden afgewongen. Hoe dat in zijn werk gaat ziet u in het volgende fragment waarbij we weer de wedstrijdtabel als voorbeeld nemen.

... CONSTRAINT c7 CHECK (thuis IN (SELECT club FROM eredivisie)), ...

Er is hier sprake van een subquery waarbij de constructie eigenlijk hetzelfde is als bij gebruik van een subquery in de where clause van een normale selectquery. De haakjes (één paar voor de subquery en één paar voor de check-constraint) vertroebelen de zaak een beetje. Maar daar is overheen te komen. Het wordt moeilijker als de subquery een selectie betreft op de tabel die we nou net aan het creëren zijn. Als de validatie uitsluitend op één kolom betrekking heeft valt nog wel iets te bereiken. We worden gedwongen onze toevlucht tot een list te zoeken als dat niet het geval is. In onze wedstrijdentabel proberen we te voorkomen dat we een club een thuiswedstrijd laten spelen op dezelfde datum waarop die club ook al een uitwedstrijd heeft afgewerkt. (In de definitieve versie moeten we ook voorkomen dat een club een uitwedstrijd krijgt toebedeeld als op dezelfde datum al een thuiswedstrijd gespeeld is!) Intuïtief (iedereen heeft klompen) voelen we aan dat we een subquery krijgen die zoiets op moet leveren als

SELECT uit, datum FROM wedstrijd;

en dat dan de combinatie (thuis, datum) daarin niet voor mag komen. Twee kolommen, dus moeten we een list verzinnen. We maken van twee kolommen één. Klein probleem daarbij is wel dat de kolommen uit en datum van een verschillend type zijn. Maar daar hebben we de oplossing al voor aangestipt. We passen een VBA-functie toe om de twee kolommen aaneen te smeden.


bladzijde 11

Script7 bevat de twee benodigde constraints de we voor de afwisseling a1 en a2 gedoopt hebben.

DROP TABLE wedstrijd; CREATE TABLE wedstrijd (datum DATE NOT NULL, thuis TEXT(20), uit TEXT(20), dpv INTEGER NOT NULL, dpt INTEGER NOT NULL, CONSTRAINT c1 PRIMARY KEY (thuis, uit), CONSTRAINT c2 UNIQUE (datum, thuis), CONSTRAINT c3 UNIQUE (datum, uit), CONSTRAINT c4 CHECK (dpv >= 0), CONSTRAINT c5 CHECK (dpt >= 0), CONSTRAINT c6 CHECK (datum BETWEEN #13-08-04# AND DATE()), CONSTRAINT c7 FOREIGN KEY (thuis) REFERENCES eredivisie(club) ON UPDATE CASCADE, CONSTRAINT c8 FOREIGN KEY (uit) REFERENCES eredivisie(club) ON UPDATE CASCADE, CONSTRAINT a1 CHECK (thuis + CSTR(datum) NOT IN (SELECT uit + CSTR(datum) FROM wedstrijd)), CONSTRAINT a2 CHECK (uit + CSTR(datum) NOT IN (SELECT thuis + CSTR(datum) FROM wedstrijd))); script7.sql

Eindelijk lijkt het erop dat we het eerste deel van de opdracht die we onszelf stelden naar tevredenheid hebben afgerond. Nogmaals uitproberen van script2 levert perfect de lijst van de twee geoorloofde wedstrijden op. Ons systeem (of eigenlijk systeempje) houdt zich keurig aan alle bedrijfsregels.


8

bladzijde 12

Tenslotte

We hebben nog twee kleine verrassingen voor u in petto. Of liever: het systeem eentje en wij eentje. U wist al dat u de tabel eredivisie pas te verwijderen is als eerst de tabel wedstrijd overboord gegooid is? Dat komt, omdat zolang als de tabel wedstrijd bestaat, de tabel eredivisie aanwezig moet zijn vanwege de in wedstrijd bestaande foreign keys. Maar probeert u met

DROP TABLE wedstrijd;

de tabel wedstrijd te verwijderen, zal database op een tamelijk kryptische manier hiertegen protesteren.

"De DDL kan in deze tabel niet worden uitgevoerd, omdat er door voorwaarde van tabel naar wordt verwezen"

De tabel wedstrijd heeft de tabel wedstrijd nodig en dus moet eerst de tabel wedstrijd verwijderd worden vóórdat de tabel wedstrijd verwijderd kan worden. Een verhaal van de kip en het ei, dat alleen is op te lossen door eerst de beperkingen die door de constraints a1 en a2 zijn opgelegd weg te nemen met

ALTER TABLE wedstrijd DROP CONSTRAINT a1; ALTER TABLE wedstrijd DROP CONSTRAINT a2;

In het voorlopig definitieve script (knvb1.sql) hebben we deze regels opgenomen. Dat brengt ons bij de tweede verrassing. U hebt zich ongetwijfeld afgevraagd waarom we bij de definitie van de twee foreign keys zonodig ON UPDATE CASCADE moesten toevoegen. Met de foreign keys voorkomen we dat we uitslagen allerlei clubs als Sparta, Emmen of zelfs FC Stormvogels aan de wedstrijdentabel kunnen toevoegen. Zelfs tikfouten als Feijenoord of Ajaks worden afgestraft. Maar wat we niet kunnen voorkomen zijn schrijffouten als FEYenoord en aJaX. In het onderstaande script (script8.sql) is opzettelijk een groot aantal van dit soort fouten geslopen.


bladzijde 13

DELETE FROM wedstrijd; INSERT INTO wedstrijd VALUES (#06-02-05#, 'RKC WaAlwiJK', 'fc groningen', 3, 0); INSERT INTO wedstrijd VALUES (#06-02-05#, 'fC UtRecht', 'Nac', 2, 0); INSERT INTO wedstrijd VALUES (#06-02-05#, 'AjaX', 'fc TwENte', 1, 2); INSERT INTO wedstrijd VALUES (#06-02-05#, 'ViTESse', 'roda jc', 3, 0); INSERT INTO wedstrijd VALUES (#30-01-05#, 'ajax', 'ADO Den Haag', 0, 0); INSERT INTO wedstrijd VALUES (#30-01-05#, 'NAC', 'SC heerenveen', 1, 1); INSERT INTO wedstrijd VALUES (#30-01-05#, 'RKC Waalwijk', 'psv', 1, 4); INSERT INTO wedstrijd VALUES (#29-01-05#, 'Vitesse', 'RBC roosendaal', 1, 3); ... script8.sql

Het resultaat van dit script – het zijn allemaal uitslagen uit de werkelijkheid – is ronduit bedroevend:

Ondanks alle voorzorgsmaatregelen zal dit overzicht nauwelijks een schoonheidsprijs verdienen. De Jet Engine is niet erg gevoelig voor wat betreft het gebruik van hoofdletters en kleine letters. Natuurlijk is het zo, dat niemand het in zijn hoofd haalt zoveel vergissingen te maken al we in script8 begaan hebben. Maar door te overdrijven wordt het effect van een op het eerste gezicht nauwelijks iets uithalend sql-statement misschien nog duidelijker. We doelen hier op:


bladzijde 14

UPDATE eredivisie SET club = club;

Voer de opdracht uit en bekijk daarna de lijst van uitslagen in de wedstrijdentabel nog maar een keer. U zult verbaasd zijn... Het complete script om de database te creëren staat in knvb1.sql. Zowel de eredivisie- als wedstrijdentabel worden gemaakt en gevuld met respectievelijk de achttien clubs en de inmiddels 180 gespeelde wedstrijden. In het tweede deel van dit artikel zoeken we een antwoord op de vraag of – en vooral op welke manier – het mogelijk is stand van de competitie aan de hand van deze wedstrijden op te vragen.


9

bladzijde 15

Winst, gelijkspel of verlies

We beginnen onze zoektocht door de wedstrijden met een eenvoudig experiment waarbij we voor de thuisspelende clubs vier kolommen genereren (het worden er straks wel meer). De kolommen die deze query oplevert voorzien we van de opschriften club, g (van gewonnen), gl (van gelijkgespeeld) en v (van verloren). De functie IIF is eerder uitgelegd.

SELECT thuis AS club, IIF(dpv > dpt, 1, 0) AS w, IIF(dpv = dpt, 1, 0) AS gl, IIF(dpv < dpt, 1, 0) AS v FROM wedstrijd;

Het resultaat is niet zo heel erg overrompelend al gaat het in totaal om 180 rijen.

Het resultaat van de bovenste rij is dat van de wedstrijd tussen RKC Waalwijk en FC Groningen (gewonnen door de Waalwijkers). Logisch dat die winstpartij voor RKC Waalwijk een 1 oplevert in de kolom w en een 0 in de kolommen gl en v. Maar dan moet FC Groningen als gevolg van deze wedstrijd ergens de combinatie 0, 0, 1 toebedeeld krijgen, wat niet het geval is – althans niet als gevolg van het bedoelde voetbaltreffen. FC Groningen was daarbij de uitclub en die hebben we in de selectie niet meegenomen. Een paar kleine aanpassingen in de formulering van de query zijn voldoende om de resultaten van de uitspelende clubs te laten zien.

SELECT uit AS club, IIF(dpv < dpt, 1, 0) AS w, IIF(dpv = dpt, 1, 0) AS gl, IIF(dpv > dpt, 1, 0) AS v FROM wedstrijd;

U kunt natuurlijk ook in plaats van de groter-dan- en kleiner- dan-tekens te verwisselen een paar nullen omruilen tegen enen en omgekeerd. De letters w en v verwisselen is ook een mogelijkheid, maar dan komt de volgorde van de kolommen van de selectie voor de thuisspelende clubs niet meer overeen met die van de selectie voor de uitspelende elftallen.


bladzijde 16

Beide queries breiden we uit met het aantal doelpunten dat het betreffende elftal scoorde en om zijn oren kreeg. Met het oog op de komende paragrafen knopen we ook de wedstrijddatum eraan vast. De queries worden dan:

SELECT thuis AS club, IIF(dpv > dpt, 1, 0) AS w, IIF(dpv = dpt, 1, 0) AS gl, IIF(dpv < dpt, 1, 0) AS v, dpv, dpt, datum FROM wedstrijd; script11.sql

en

SELECT uit AS club, IIF(dpv < dpt, 1, 0) AS w, IIF(dpv = dpt, 1, 0) AS gl, IIF(dpv > dpt, 1, 0) AS v, dpt, dpv, datum FROM wedstrijd; script12.sql

Nee, het is geen vergissing dat de oorspronkelijk kolommen dpt en dpv in de tweede query in omgekeerde volgorde staan. De kolom dpv van de tabel wedstrijd bevat nou eenmaal de tegendoelpunten van de uitspelende club terwijl het aantal goals dat het bezoekende elftal maakte in de kolom dpt van de wedstrijdtabel te lezen valt... U begrijpt al waar we naar toe willen? Door beide tabellen aan elkaar te plakken komen we al een heel eind.

SELECT thuis AS club, IIF(dpv > dpt, 1, IIF(dpv = dpt, 1, IIF(dpv < dpt, 1, dpv, dpt, datum FROM wedstrijd UNION ALL SELECT uit, IIF(dpv < dpt, 1, IIF(dpv = dpt, 1, IIF(dpv > dpt, 1, dpt, dpv, datum FROM wedstrijd;

0) AS w, 0) AS gl, 0) AS v,

0), 0), 0),

script13.sql

Een paar opmerkingen. Gebruik liever geen UNION maar een UNION ALL. De eerste werkt vertragend omdat daarbij de rijen vooraf gesorteerd moeten worden om vervolgens dubbele rijen te negeren. Voor dat laatste hoeven we hier trouwens niet zo bang te zijn. Omdat we bij


bladzijde 17

elke rij de speeldatum vermeld hebben kan eenzelfde rij nooit twee keer voorkomen. En als u niet begrijpt waarom dat zo is, moet u het eerste deel van dit verhaal nog maar eens goed lezen. Een tweede opmerking betreft het achterwege laten van de kolomaliassen in de uitselectie. Die hebben daar geen enkele zin. Een UNION betrekt de kolomopschiften uitsluitend van de eerste query.


bladzijde 18

10 Teleurstelling Het resultaat dat we vonden bij de vorige query (script13.sql) brengt de oplossing van ons probleem een grote stap dichterbij. Vooral, als we ook nog eens de datum aan het resultaat vastknopen. Het ligt voor de hand om het resultaat als view op te slaan om op die view nieuwe selecties te laten plaatsvinden.

CREATE VIEW hulptabel AS SELECT thuis AS club, IIF(dpv > dpt, 1, IIF(dpv = dpt, 1, IIF(dpv < dpt, 1, dpv, dpt, datum FROM wedstrijd UNION ALL SELECT uit, IIF(dpv < dpt, 1, IIF(dpv = dpt, 1, IIF(dpv > dpt, 1, dpt, dpv, datum wedstrijd; FROM

0) AS w, 0) AS gl, 0) AS v,

0), 0), 0),

script14.sql

Wat bij de Jet Engine de volgende reactie tweegbrengt:

"Samenvoegingen zijn niet toegestaan in een subquery"

Met andere woorden: het idee van de union kan naar de prullenbak. Een beetje database management systeem draait daar echt zijn hand niet voor om. U begrijpt nu waarschijnlijk het kopje van deze paragraaf!


bladzijde 19

11 De ontdekking van een wondermiddel Jet SQL is amper gedocumenteerd. Veel moet dan ook door experimenteren worden uitgedokterd. Eén van die vage begrippen uit Jet SQL is het fenomeen procedure, niet te verwarren met de term stored procedure die in de databasewereld een vertrouwde klank heeft. Een procedure blijkt van alles te kunnen zijn. Hij accepteert willekeurige queries – dus niet alleen selectiequeries zoals een view. Hij slikt parameters (parametergebruik in een view is niet toegestaan). Een procedure vindt het prima als er iets over de volgorde van rijen gezegd wordt (kom in een view niet aanzetten met een ORDER BY). En hij verwerkt ook nog eens UNIONS (iets dat nou net bij een view onmogelijk gebleken is). Er zijn nog veel meer verschillen maar dit betoog heeft een ander doel dan het bieden van een cursus Jet SQL. Omdat de meesten niet vertrouwd zijn met het begrip procedure geven we een voorbeeld hoe we zo'n alleskunner kunen maken. We doen dat aan de hand van de misschien wel merkwaardigste query die we tot nu toe in dit hele verhaal hebben uitgevoerd en die we gebruikten om rare schrijfwijzen te herstellen. We doelen op

UPDATE eredivisie SET club = club;

Deze query laat zich eenvoudig tot procedure promoveren.

CREATE PROCEDURE corrigeer AS UPDATE eredivisie SET club = club; script15.sql

Vanaf nu hoeft u de correctiequery nooit meer in te tikken maar kunt u volstaan met

EXECUTE corrigeer;

U mag EXECUTE bovendien verbasteren tot EXEC. Handig?


bladzijde 20

Misschien verbaast de volgende query u nu niet meer:

CREATE PROCEDURE hulptabel SELECT thuis AS club, IIF(dpv > dpt, 1, IIF(dpv = dpt, 1, IIF(dpv < dpt, 1, dpv, dpt, datum FROM wedstrijd UNION ALL SELECT uit, IIF(dpv < dpt, 1, IIF(dpv = dpt, 1, IIF(dpv > dpt, 1, dpt, dpv, datum FROM wedstrijd;

AS 0) AS w, 0) AS gl, 0) AS v,

0), 0), 0),

script16.sql

Script16 is een kleine variant op script14. Alleen het woordje VIEW is vervangen door PROCEDURE. We krijgen de inhoud van de hulptabel niet alleen te zien met EXECUTE hulptabel maar ook

SELECT * FROM hulptabel;

openbaart het resultaat van onze eerdere hersenspinsels.

Een procedure die een resultaat in tabelvorm oplevert kan gewoon als tabel aangesproken worden. We kunnen onze ogen nauwelijks geloven, maar de volgede query slaagt echt:


bladzijde 21

CREATE VIEW mosterd_na_de_maaltijd AS SELECT * FROM hulptabel;

Nu we de smaak van procedures te pakken hebben is onze behoefte aan bovenstaande instructie niet meer zo groot.


bladzijde 22

12 De stand We zijn nog maar één procedure verwijderd van het moment waarop we de competitiestand in beeld kunnen brengen. We zouden met een view ook een heel eind komen maar dan moeten we – omdat volgorde een wezenlijk kenmerk van de tabel op de allereerste bladzijde is – een of andere vorm van groeperen toepassen (GROUP BY). Het is immers niet toegestaan ORDER BY in de definitie van zo'n view op te nemen. Bij een procedure mag dat wel (we verbazen ons nergens meer over). Kijken we naar de bedoelde stand dan ontwaren we – afgezien van de kolom die de plaats in de stand aangeeft – achtereenvolgens de naam van het elftal, het aantal gespeelde wedstrijden, het aantal gewonnen wedstrijden, het aantal gelijkgespeelde wedstrijden, het aantal verloren westrijden, het totaal aan wedstrijdpunten, het totaal aan zelf gemaakte doelpunten en het totaal aan tegengoals. Dat is in SQL niet zo moeilijk voor elkaar te krijgen aan de hand van onze hulptabel.

club, COUNT(*), SUM(w), SUM(gl), SUM(v), SUM(3 * w + gl), SUM(dpv), SUM(dpt) FROM hulptabel GROUP BY club; SELECT


bladzijde 23

Vóórdat we de vorige query tot procedure – een view komt voor dit werk ook in aanmerking – verheffen geven we nog een kolomalias mee zodat de tabel 'leesbaarder' wordt. Bovendien is er nog wel iets over de volgorde op te merken al zullen de supporters van ADO Den Haag ingenomen zijn met de bovenste plaats van de ranglijst.

club, COUNT(*) AS g, SUM(w) AS w, SUM(gl) AS gl, SUM(v) AS v, SUM(3 * w + gl) AS punten, SUM(dpv) AS dpv, SUM(dpt) AS dpt FROM hulptabel GROUP BY club ORDER BY SUM(3 * w + gl) DESC, SUM(dpt) - SUM(dpv); SELECT

script17.sql

In de in script17 geformuleerde query wordt de volgorde bepaald door het aantal westrijdpunten en bij gelijk aantal wedstrijdpunten wordt ook nog eens gerangschikt op doelsaldo (de omgekeerde volgorde van het totale aantal doelpunten vóór verminderd met het totale aantal tegendoelpunten). Vergelijken met de echte stand (zie de eerste bladzijde) brengt een klein verschil aan het licht tussen de plaats van Willem II en FC Groningen. Beide ploegen hebben evenveel wedstrijdpunten en een gelijk doelsaldo. Hetzelfde fenomeen valt waar te nemen bij RBC Roosendaal en De Graafschap. Welke regel de KNVB in zo'n geval hanteert is niet geheel duidelijk maar als het daarbij gaat om welk van de twee de meeste doelpunten scoorde is dat eenvoudig in script17 te verwerken. Gewoon door als derde sorteercriterium dit aantal te vermelden (SUM(dpv) DESC), ofschoon dit ons bij het koppel RBC Roosendaal - De Graafschap geen snars verder brengt. Van de query in scipt17 kan geen view gemaakt worden vanwege de opgegeven sortering. Een view accepteert nu eenmaal geen ORDER BY. We nemen onze toevlucht tot een procedure.

CREATE PROCEDURE stand AS SELECT club, COUNT(*) AS g, SUM(w) AS w, SUM(gl) AS gl, SUM(v) AS v, SUM(3 * w + gl) AS punten, SUM(dpv) AS dpv, SUM(dpt) AS dpt FROM hulptabel GROUP BY club ORDER BY SUM(3 * w + gl) DESC, SUM(dpt) - SUM(dpv); stand.sql


bladzijde 24

Vanaf nu kunnen we – als we de wedstrijdentabel bijgewerkt hebben – op elk moment de stand van de competitie opvragen met

EXECUTE stand;

of met

SELECT * FROM stand;

En ofschoon we toch echt het object stand als procedure gedefinieerd hebben herkent de Jet Engine deze procedure als view. Vraag de metadata maar op. Terwijl we heus geprobeerd hebben stand als view te definiëren en toen met de mededeling afgescheept werden dat hetgeen we wilden allemaal te ingewikkeld was met de letterlijke boodschap

Alleen eenvoudige SELECT-query's zijn toegestaan in VIEWS


bladzijde 25

13 Op elke gewenste datum? We hebben nog niet helemaal aan het door ons gestelde doel beantwoord. We wilden immers de stand van de competitie op elke gewenste datum kunnen zien. In de wetenschap dat we bij procedures parameters kunnen verwerken is dit niet zo moeilijk. Pas nu blijkt waarom we de wedstrijddatums in onze hulptabel hebben meegenomen. Tot dusverr hebben we hier nog geen gebruik van gemaakt.

CREATE PROCEDURE stand_per AS club, SELECT COUNT(*) AS g, SUM(w) AS w, SUM(gl) AS gl, SUM(v) AS v, SUM(3 * w + gl) AS punten, SUM(dpv) AS dpv, SUM(dpt) AS dpt FROM hulptabel WHERE datum <= x GROUP BY club ORDER BY SUM(3 * w + gl) DESC, SUM(dpt) - SUM(dpv); stand_per.sql

In de definitie van de procedure is één regel toegevoegd waarmee alle wedstrijden van na een bepaalde datum (hier x, maar elke naam is natuurlijk goed) worden uitgesloten. Hoe u de waarde van x opgeeft blijkt uit de volgende formulering waarin geïnformeerd wordt naar de stand op 3 september:

EXECUTE stand_per #03-09-2004#;

Het enige wat nu nog ontbreekt zijn de plaatsnummers als eerste kolom van de tabel. Ook daar valt nog wel wat aan te doen. De manier waarop we dat voor elkaar gekregen hebben vereist enige inzicht in het begrip self join.


bladzijde 26

14 Finishing touch De plaatsnummers zijn een probleem apart. We gaan ervan er gemakshalve vanuit dat twee clubs die evenveel punten en een even hoog doelsaldo hebben gelijkgeplaatst zijn. Op 10 februari 2005 – onze testdatum – is dat maar liefst twee keer het geval. Zowel FC Groningen en Willem II als RBC Roosendaal en De Graafschap bezetten dezelfde plaats in de rangorde (tenzij we andere criteria erbij betrekken). We volstaan met het aanreiken van de oplossing met de kanttekening dat we als uitgangspunt gebruikt hebben dat in de vaderlandse competitie het doelsaldo altijd kleiner zal zijn dan duizend keer het aantal wedstrijdpunten (behalve in het geval het laatste nul is). Bij twijfel verhoogt u de vermenigingsvuldigingfactor... Een vleugje inner join in combinatie met union all doet de rest. Je moet alleen op het idee komen.

CREATE PROCEDURE stand2_per AS SELECT count(*) + 1 AS nr, a.club, a.g, a.w, a.gl, a.v, a.punten, a.dpv, a.dpt FROM stand_per a, stand_per b WHERE 1000 * a.punten + a.dpv - a.dpt < 1000 * b.punten + b.dpv - b.dpt GROUP BY a.club, a.g, a.w, a.gl, a.v, a.punten, a.dpv, a.dpt UNION ALL SELECT 1, club, g, w, gl, v, punten, dpv, dpt FROM stand_per WHERE 1000 * punten + dpv - dpt = (SELECT MAX(1000 * punten + dpv - dpt) FROM stand_per) ORDER BY nr; stand2_per.sql

Onderstaand overzicht toont het resultaat van

EXECUTE stand2_per #10-02-05#;

Nog niet overtuigd van de mogelijkheden van Jet SQL? Kijk dan maar eens naar de posities van FC Groningen, Willem II, RBC Roosendaal en De Graafschap!


bladzijde 27

Voor de oplettende lezer: het is een beetje zonde om de procedure stand_per in bovenstaande definitie te gebruiken. Daarin is immers voorzien in een volgordebepaling die we in stand2_per overschrijven. Maar om stand_per zonder volgordebepaling te herschrijven vonden we net iets teveel werk. En bent u slechts geïnteresseerd in de stand na alle wedstrijden vervangt u stand_per gewoon door stand. En als iemand opmerkt dat het misschien mooier is om de stand te laten zien als


bladzijde 28

laten we het helemaal aan hem- of haarzelf over om de oplossing te vinden. Wij vinden het zo wel welletjes. Wat we gerealiseerd hebben is een robuuste database met twee tabellen die alle regels van de KNVB respecteert. En passant is een procedure ontwikkeld die onvolkomenheden in de manier waarop namen van elftallen geschreven kunnen worden op een letterlijk verfrissende wijze corrigeert. Daarnaast is de database voorzien van een aantal procedures waarmee we de stand van de competitie op elk gewenst moment kunnen reproduceren. Hopelijk is met dit voorbeeld voldoende aangetoond dat Jet SQL misschien meer respect verdient dan het soms krijgt. Laat deze casus in ieder geval een uitdaging zijn voor ontwikkelaars die zweren bij andere database management systemen.

Leiden, Toon Kuipers

Holland Casino Eredivisie

Recommend Documents