Databanken Marc De Caluwé, 2005-2009
Inhoudsopgave 1. INLEIDING......................................................................................................................................4 1.1. GEGEVENS.............................................................................................................................4 1.2. DATABASES...........................................................................................................................5 1.3. DATABASES GEBRUIKEN: SQL.........................................................................................7 1.4. GEGEVENSMODELLEN........................................................................................................7 1.5. EEN VOORBEELD..................................................................................................................8 1.6. SAMENVATTING.................................................................................................................11 1.7. DATABASES ONTWERPEN...............................................................................................11 2. GEGEVENSMODELLERING EN INFORMATIEANALYSE....................................................12 2.1. GEGEVENSMODELLERING BINNEN EEN PROJECT....................................................12 2.2. DE TAAL VAN GEGEVENSMODELLERING...................................................................12 2.2.1. Probleemafbakening: de Universe of Discourse.............................................................12 2.2.2. Object, kenmerk en domein............................................................................................13 2.2.3. Type en individu.............................................................................................................14 2.2.4. Feit..................................................................................................................................15 2.2.5. Gegevensdefinitie en gegeven.........................................................................................15 2.2.6. Gegeven en gegevensdrager............................................................................................15 2.2.7. Besluit: naamgeving is essentieel....................................................................................16 2.3. INFORMATIEANALYSE.....................................................................................................16 2.3.1. Vier vragen......................................................................................................................17 2.3.2. Beginnen.........................................................................................................................17 2.4. HOE BEGINNEN?.................................................................................................................18 2.4.1. Beginnen met feiten........................................................................................................18 2.4.2. Beginnen met teksten......................................................................................................19 2.4.3. Beginnen met formulieren, lijsten of bestanden..............................................................19 2.5. OPGAVEN.............................................................................................................................20 2.6. SAMENVATTING.................................................................................................................21 3. TOP-DOWN ONTWERPEN.........................................................................................................22 3.1. ENTITY – RELATIONSHIP DIAGRAM..............................................................................22 3.1.1. Soorten relaties................................................................................................................22 3.1.2. Levenscyclus...................................................................................................................24 3.2. EEN ERD GAAT ENKEL OVER GEGEVENS....................................................................24 3.3. ERD VOOR DISCUSSIE EN DOCUMENTATIE................................................................25 3.4. ATTRIBUTEN.......................................................................................................................26 3.5. CASE TOOLS........................................................................................................................27 3.6. BETEKENISRELATIES IN EEN GEGEVENSMODEL......................................................27 3.6.1. Specialisatie en generalisatie...........................................................................................28 3.6.2. Aggregatie of compositie................................................................................................30 3.6.3. Associatie........................................................................................................................30 3.7. ROLLEN VAN ENTITEITTYPEN BINNEN EEN MODEL................................................31 3.8. TIJD IN EEN ERD.................................................................................................................31 3.9. ZELF EEN ERD TEKENEN..................................................................................................32 3.10. OPGAVEN...........................................................................................................................33 4. BOTTOM-UP ONTWERPEN.......................................................................................................35 4.1. FUNCTIONELE AFHANKELIJKHEID................................................................................35 4.2. ROL VAN ATTRIBUTEN.....................................................................................................36 Databanken
1
4.3. SLEUTELS.............................................................................................................................37 4.3.1. Identificerende sleutel.....................................................................................................37 4.3.2. Kandidaatsleutel..............................................................................................................37 4.3.3. Vreemde sleutel...............................................................................................................38 4.3.4. Code of naam..................................................................................................................38 4.3.5. Sleutels en semantiek......................................................................................................39 4.4. REDUNDANTIE....................................................................................................................39 4.5. INTEGRITEIT VAN GEGEVENS........................................................................................40 4.6. VAN ERD NAAR TABEL.....................................................................................................40 4.6.1. Eén op één relaties mappen.............................................................................................41 4.6.2. Eén op één/nul relaties mappen.......................................................................................41 4.6.3. Eén/nul op één/nul relaties mappen................................................................................42 4.6.4. Eén op veel relaties mappen............................................................................................42 4.6.5. Veel op veel relaties mappen..........................................................................................42 4.6.6. Parallelle relaties mappen...............................................................................................42 4.6.7. Recursieve relaties mappen.............................................................................................43 4.6.8. Specialisaties mappen.....................................................................................................43 4.7. NORMALISEREN.................................................................................................................44 4.7.1. Nulde normaalvorm (0NV).............................................................................................44 4.7.2. Eerste normaalvorm (1NV).............................................................................................45 4.7.3. Tweede normaalvorm (2NV)..........................................................................................46 4.7.4. Derde normaalvorm (3NV).............................................................................................46 4.7.5. Boyce-Codd normaalvorm (BCNV)...............................................................................47 4.7.6. Terug naar het ERD model.............................................................................................49 4.8. DENORMALISATIE.............................................................................................................49 4.9. OPGAVEN.............................................................................................................................50 5. RELATIONELE DATABASES EN HUN EIGENSCHAPPEN...................................................52 5.1. VERZAMELINGEN..............................................................................................................52 5.2. RELATIES EN TUPLES........................................................................................................53 5.3. RELATIONELE BEWERKINGEN.......................................................................................53 5.4. RELATIONELE BEWERKINGEN IN SQL..........................................................................54 5.4.1. Unie, intersectie en verschil............................................................................................55 5.4.2. Selectie en projectie........................................................................................................55 5.4.3. Product en join................................................................................................................55 5.4.4. Andere bewerkingen.......................................................................................................56 5.5. VERDERE EISEN VOOR EEN DBMS................................................................................57 5.5.1. Performance....................................................................................................................57 5.5.2. Transacties.......................................................................................................................58 5.5.3. Transacties en geldigheidsregels.....................................................................................59 5.5.4. Gelijktijdigheid...............................................................................................................59 5.5.5. Beveiliging......................................................................................................................61 5.5.6. Recovery..........................................................................................................................61 5.6. DE DATA-DICTIONARY.....................................................................................................62 5.6.1. Metagegevens..................................................................................................................62 5.6.2. Metadata in uitgebreidere zin..........................................................................................63 5.6.3. Metadefinitie en datamanipulatie....................................................................................63 5.7. OPGAVEN.............................................................................................................................64 6. STRUCTURED QUERY LANGUAGE VRAAGTAAL..............................................................65 6.1. DE MEEST EENVOUDIGE SELECT SYNTAX.................................................................66 6.2. GEGEVENS UIT EEN TABEL: FILTERS............................................................................67 6.2.1. Eenvoudige vergelijkingsoperatoren...............................................................................67 6.2.2. Meer geavanceerde operatoren........................................................................................68 6.2.3. Logische operatoren........................................................................................................69 Databanken
2
6.2.4. DISTINCT.......................................................................................................................70 6.2.5. IN.....................................................................................................................................71 6.2.6. CONTAINING................................................................................................................71 6.3. SORTEREN, RANGSCHIKKEN VAN GEGEVENS...........................................................71 6.4. AGGREGAATSFUNCTIES EN GROEPERING..................................................................72 6.4.1. De aggregaatsfuncties.....................................................................................................72 6.4.2. De GROUP BY clausule.................................................................................................73 6.4.3. De HAVING clausule.....................................................................................................73 6.5. GEGEVENS UIT MEERDERE TABELLEN........................................................................74 6.5.1. Traditionele JOIN............................................................................................................74 6.5.2. Moderne JOIN.................................................................................................................75 6.5.3. OUTER JOIN..................................................................................................................75 6.5.4. FULL OUTER JOIN.......................................................................................................76 6.5.5. Aliassen...........................................................................................................................76 6.5.6. Self joins.........................................................................................................................77 6.6. VIEWS....................................................................................................................................77 6.7. SUBQUERIES EN VERZAMELBEWERKINGEN..............................................................78 6.7.1. ANY en ALL...................................................................................................................78 6.7.2. IN en NOT IN voor subqueries.......................................................................................79 6.7.3. EXISTS...........................................................................................................................80 6.7.4. Verzamelbewerkingen: UNION......................................................................................80 6.8. OPGAVEN.............................................................................................................................81 7. SQL MANIPULATIETAAL..........................................................................................................85 7.1. HET INSERT COMMANDO.................................................................................................85 7.1.1. INSERT rij per rij............................................................................................................85 7.1.2. INSERT vanuit een query...............................................................................................85 7.1.3. INSERT en automatische velden....................................................................................86 7.1.4. INSERT en defaults........................................................................................................87 7.1.5. INSERT en triggers.........................................................................................................87 7.2. HET UPDATE COMMANDO...............................................................................................88 7.2.1. De SET clausule..............................................................................................................89 7.2.2. Waardes switchen tussen twee velden............................................................................89 7.2.3. UPDATE en triggers.......................................................................................................89 7.3. HET DELETE COMMANDO...............................................................................................90 7.4. INSERT, UPDATE EN DELETE MET VIEWS...................................................................90 7.5. OPGAVEN.............................................................................................................................91 8. SQL DEFINITIETAAL..................................................................................................................92 8.1. HET CREATE COMMANDO...............................................................................................92 8.1.1. Een tabel creëren.............................................................................................................92 8.1.2. Datatypes.........................................................................................................................93 8.1.3. Additionele tabelinformatie............................................................................................93 8.1.4. Ander gebruik van CREATE..........................................................................................94 8.2. HET ALTER COMMANDO..................................................................................................95 8.2.1. Een tabel wijzigen...........................................................................................................95 8.2.2. Wanneer ALTER niet volstaat........................................................................................96 8.2.3. Ander gebruik van ALTER.............................................................................................96 8.3. HET DROP COMMANDO....................................................................................................97 8.4. OPGAVEN.............................................................................................................................97 REFERENTIES..................................................................................................................................98
Databanken
3
1. INLEIDING
1.1. GEGEVENS Een database heet in het Nederlands databank of gegevensbank: men slaat er blijkbaar gegevens in op. Wat zijn gegevens? Meestal wordt een onderscheid gemaakt tussen gegevens, kennis en informatie. Zonder bijkomende commentaar stelt de getallenreeks 12, 14, 15 enkel een verzameling gegevens voor. Indien we er aan toevoegen dat het hier gaat om de gemiddelde temperatuur van de maanden februari, maart en april, dan stelt deze reeks een hoeveelheid informatie voor. Of iets ja dan neen informatie wordt hangt dus ook af van de toehoorder. Voor iemand die geen enkel idee heeft wat het woord temperatuur betekent, is de geciteerde getallenreeks nog steeds geen informatie. Kennis is een bundeling van informatie in een bepaalde samenhang. In het dagelijkse taalgebruik worden de woorden gegevens en informatie in de praktijk nogal eens als synoniemen gebruikt. We leven tegenwoordig in een informatiemaatschappij. Deze term wijst erop dat mensen steeds meer met informatie werken in plaats van met hun handen. Een tweede betekenis is dat allerlei gegevens gemakkelijk en overvloedig beschikbaar zijn. Het probleem is: weten waar je moet zoeken. Documentatie en ontsluiting van kennis zijn daarmee van groot belang geworden. En het is dus ook erg belangrijk geworden om databases te kunnen ontwerpen, bouwen en gebruiken. Alle gegevens komen ergens vandaan. Ze worden in het leven geroepen door mensen of instanties die er belang bij hebben die gegevens te bezitten of te verhandelen. Gegevens ontstaan, bestaan en vergaan. Ze kennen een levenscyclus. Om te beginnen moet iemand op het idee komen iets als een gegeven te beschouwen. Vervolgens worden gegevens in de een of andere vorm onthouden. De gegevens dienen daartoe geformaliseerd te worden: worden ze opgeslagen als figuur, als tekst, als veld in een databank,...? Elk van deze weergave vormen heeft voor- en nadelen. Deze keuze hangt dan ook af van de vraag voor wie en met welk doel deze gegevens toegankelijk moeten zijn. Eenmaal geformaliseerd worden gegevens voor kortere of langere tijd bewaard. Hoe lang zal afhangen van het type gegevens. Worden gegevens voor langere tijd bewaard, dan verouderen ze, ze verliezen aan nauwkeurigheid. Gegevens bewaren die verouderd zijn kost tijd en geld: het is daarom van belang om bij het aanmaken van gegevens al te bepalen hoe lang ze moeten meegaan en wat er moet gedaan worden tegen het verouderen. Elk gegeven dat in een computer wordt opgeslagen kent een zogeheten datatype. Dat betekent dat het gegeven slechts waarden kan aannemen uit een bepaalde verzameling waarden, het domein genoemd. Klassieke datatypes voor databanken zijn rijen karakters, getallen, datum, geld, blob (binary large object). Nieuwere types zijn beeld, geluid, video. Het datatype documenteert de gegevens en geeft de mogelijkheid ze op kwaliteit te controleren. Zo zal een datum-type enkel en alleen een geldige datum aanvaarden als invoer. Naast datatype worden de gegevens in een database ook gedocumenteerd met een naam voor elk gegeven. Dit soort gegevens-over-de-gegevens worden metagegevens Databanken
4
genoemd. De metagegevens vormen samen de data-dictionary.
1.2. DATABASES Een database is in algemene zin een verzameling gegevens die bij elkaar horen. In engere zin wordt hieraan als voorwaarden toegevoegd dat de gegevens elektronisch dienen te zijn opgeslagen en dat ze als een geheel benaderd en beheerd moeten kunnen worden. De softwareproducten om databases mee te bouwen heten database-pakketten. De kern van een database-pakket is het database-management-systeem, afgekort DBMS. Daarnaast bevat zo'n pakket nog allerlei hulpprogramma's om het leven van de gebruikers te veraangenamen. Gelijksoortige gegevens worden in de database in één tabel gestopt. Een element uit zo'n tabel noemen we een record of rij. Indien de tabel bijvoorbeeld adressen bevat noemen we één adres van de tabel een record. Het gebruik van een database kan vergeleken worden met een verzameling kaartenbakken, maar uiteraard met veel flexibeler mogelijkheden van ontsluiting. Het is gemakkelijk gegevens te transporteren, een deel van de gegevens uit de database te lichten, en ze af te drukken of af te beelden. Een opdracht om gegevens uit een database op te vragen noemt men een query (verzoek). Een belangrijk voordeel van databanken is dat vele gebruikers tegelijk de gegevens in een database kunnen benaderen. De taak van een databank is een brug te slaan tussen drie dingen: de mensen die met de gegevens willen werken, de gegevens in hun correcte samenhang, en de computergeheugens waarop die gegevens zijn opgeslagen. In de architectuur van een database-pakket zijn deze drie niveaus terug te vinden: het gebruikersniveau omvat de hulpmiddelen die de toegang van de gebruikers tot de gegevens regelen. Het conceptueel niveau is de data-dictionary die onder meer het gegevensmodel bevat. Het derde niveau wordt fysiek niveau genoemd. Het omvat hulpmiddelen om de gegevens op te slaan in één of meer computergeheugens en om te zorgen dat ze snel te benaderen blijven. Bij een goed database-management-systeem zijn deze drie niveaus onafhankelijk van elkaar. Men noemt dit gegevensonafhankelijkheid en het betekent het volgende. Een database heeft altijd één enkel gegevensmodel. Is dit onafhankelijk van het gebruikersniveau, dan kunnen verschillende gebruikers een verschillend gedeelte van de database benaderen. Dat is vaak van belang als bijvoorbeeld een deel van de gegevens vertrouwelijk van aard is. Ook tussen gegevensmodel en opslagmedia is onafhankelijkheid gewenst. Die maakt het mogelijk dat gegevens uit een gegevensmodel verspreid worden opgeslagen in een willekeurig aantal computers. De database kan worden gedistribueerd over verschillende computers. Om databases te bouwen heeft men software nodig die te koop is onder de algemene naam database-pakket. De kern van zo'n pakket is het database-management-systeem. Er bestaan vele honderden database-pakketten. Vele daarvan zijn gespecialiseerd voor bepaalde soorten toepassingen, of draaien alleen op computers van een bepaalde leverancier. Enkele tientallen pakketten zijn voor allerlei toepassingen te gebruiken en draaien op veel van de meest gangbare computers. Op deze laatste pakketten gaan we hier iets verder in. Deze pakketten verschillen in vele opzichten maar ze hebben wel één iets gezamenlijk: het zijn allemaal relationele database-pakketten. Kort gezegd komt dat hierop neer dat de gegevens de vorm hebben van gekoppelde tabellen. De tabellen zitten zo in elkaar dat elke rij de gegevens van één ding bevat, en elke kolom gegevens van Databanken
5
eenzelfde type. Grofweg vallen er pakketten in drie maten te onderscheiden: ✔ De kleine pakketten, vaak kaartenbak-pakket genoemd, bedoeld voor slechts één gebruiker en doorgaans voor slechts één type gegevens (één of enkele tabellen). Bijvoorbeeld een adressenbestand. Voorbeelden: PC-file, Cardbox, Cardfile,... ✔ De middencategorie: pakketten die doorgaans klein begonnen zijn maar uitbreidingen hebben gekregen voor gebruik in netwerken en voor het koppelen van tabellen. Het database-management-systeem is vaak niet erg krachtig, de data-dictionary beperkt. Ze zijn goed om eenvoudige databases mee te bouwen, zonder dat veel voorkennis nodig is. Voorbeelden: Microsoft Access, Paradox, dBase,... ✔ De zwaardere pakketten die voor tientallen tot honderden gebruikers tegelijk geschikt zijn. Deze kennen meestal uitgebreide mogelijkheden. Hun data-dictionary kan op dezelfde manier worden benaderd als de gewone gegevens. Voor leken zijn deze pakketten vaak niet dadelijk te gebruiken en ze stellen dikwijls hogere eisen aan de hardware. Voorbeelden: Oracle, Informix, Sybase, Interbase/Firebird, mySQL,... De pakketten uit de middencategorie worden vaak gebruikt om eenpersoons-databases te bouwen. Ze zijn als zodanig nauwelijks te vergelijken met de zwaardere databases omdat juist het gelijktijdig toegang bieden tot de gegevens aan vele gebruikers een database tot een belangrijke meerwaarde maakt voor een organisatie. Er zijn twee groepen mensen betrokken bij de bouw van een database: degenen die de database zullen gebruiken en degenen die hem bouwen. Dit zijn meestal niet dezelfde mensen, zeker niet bij grote databases in het bedrijfsleven. In een onderzoekssituatie kan het zijn dat een onderzoeker zijn eigen database ontwerpt en bouwt. Ook voor eenpersoons-databases op een PC geldt dit. Als een database eenmaal bestaat komen er andere rollen bij. Bij grote databases zijn er personen die speciaal belast zijn met het in werking houden van de database. Om specifiek de data-dictionary te bewaken, zodat de kwaliteit van de gegevens op niveau blijft, kan er een gegevensbeheerder zijn. Is de functie meer gericht op de opslagstructuren en het draaiend houden van de software, dan spreekt men van een database-administrator. Gezien vanuit een organisatie die met een database werkt, zijn er een aantal kwaliteitseisen te formuleren voor zo'n database. We moeten daarbij bedenken dat een database in een organisatie een deel is van een groter geheel. Om zo'n database heen draaien toepassingsprogramma's, bvb voor gegevensinvoer, en er zijn regels voor de omgang: welke personeelsleden moeten zorgen voor het toevoegen van gegevens, welke voor het uitdraaien van overzichten ten behoeve van klanten, en zo meer. Vanuit het perspectief van de gebruikers kunnen we volgende eisen onderkennen: ✔ Betrouwbaar: de gegevens moeten juist zijn, en up-to-date. ✔ Volledig: het moet niet nodig zijn om, behalve in de database, ook nog op andere plaatsen naar een gegeven te zoeken. ✔ Efficiënt: een gebruiker moet niet onnodig behoeven te wachten. ✔ Begrijpelijk: de gegevens moeten voldoen aan vooraf bepaalde eisen voor begrijpelijkheid. Voor bepaalde specialistische doeleinden kan het zijn dat gebruikers scholing nodig hebben, maar in het algemeen zullen ze zonder scholing in hun eigen taal met de gegevens en met de applicaties om moeten kunnen gaan. Vanuit het perspectief van ontwerpers en bouwers kunnen we volgende eisen onderkennen: ✔ Testbaar: dit punt geldt met name voor applicaties op een database. Ze moeten goed te debuggen zijn, ofwel moeten er hulpmiddelen zijn om de software te ontdoen van fouten. Databanken
6
Aanpasbaar: er kunnen altijd wijzigingen in gegevensstructuren en applicaties nodig zijn. Het mag niet onnodig moeilijk zijn deze wijzigingen aan te brengen. Vanuit het perspectief van de organisatie als geheel tenslotte: ✔ Apparatuur-onafhankelijk: organisaties kopen nieuwe computers, of reorganiseren zichzelf met de regelmaat van de klok. Databases moeten daartegen bestand zijn. Het database-management-systeem moet op allerlei hardware en onder allerlei besturingssystemen kunnen draaien. ✔ Organisatie-onafhankelijk: wanneer er fusies of samenwerkingsverbanden ontstaan worden dikwijls gegevens gedeeld tussen de betrokken organisaties. Het is dan handig wanneer gegevensdefinities overeenstemmen. In veel bedrijfstakken heeft men dan ook zogeheten referentie-informatiemodellen opgesteld. Deze modellen zijn standaardgegevensmodellen voor de desbetreffende bedrijfstak en kunnen door elke organisatie worden gebruikt als basis voor de gegevensmodellen achter hun eigen databases. Merk op dat de drie soorten eisen niet op hetzelfde moment gelden: voor een gebruiker moet de database vandaag goed werken, voor een ontwerper, bouwer of beheerder moet hij morgen nog goed werken, en voor de organisatie moet hij gedurende een aantal jaren goed werken. ✔
1.3. DATABASES GEBRUIKEN: SQL U heeft inmiddels een idee van wat een database is en wat men ermee kan doen. Maar hoe geeft men nu opdrachten aan een database? Hoe voert een gebruiker gegevens in, hoe vraagt hij ze op, hoe onderhoudt een administrator de gegevensstructuur? Er bestaan daarvoor verschillende mogelijkheden. Vooral de eenvoudiger pakketten werken daarvoor dikwijls met keuzemenu's die een gebruiker zonder noemenswaardige voorkennis kan bedienen. Ook zijn er soms opdrachttalen die bij een bepaald pakket horen. Er is echter één taal die in vrijwel alle databasepakketten kan worden gebruikt en die we de wereldstandaard voor databasetalen kunnen noemen: SQL. SQL staat voor Structured Query Language. In het volgende hoofdstuk gaan we in op de belangrijkste SQL mogelijkheden. SQL is een gestandaardiseerde taal, maar elk pakket heeft zijn eigen dialect. Meestal ondersteunen deze dialecten de belangrijkste SQL standaardmogelijkheden, en bieden ze daarnaast een aantal specifieke mogelijkheden voor het desbetreffende pakket. Om u alvast een idee te geven van hoe SQL eruitziet, enkele voorbeelden: ✔ Om de tabel PERSOON met de velden NAAM en EMAIL aan te maken in een database gebruiken we iets als: CREATE TABLE PERSOON (NAAM CHARACTER(30), EMAIL CHARACTER(100)); ✔ Om in deze tabel één nieuw gegeven in te voeren gebruiken we iets als: INSERT INTO PERSOON (NAAM, EMAIL) VALUES ('Marc De Caluwé', '
[email protected]'); ✔ Om de gegevens op te vragen die in de tabel PERSOON zitten: SELECT * FROM PERSOON;
1.4. GEGEVENSMODELLEN We hebben het tot nu toe gehad over gegevens , databases en SQL. Op één belangrijke vraag zijn we echter nog niet ingegaan: hoe komt men van een probleemsituatie tot een werkende database? Databanken
7
Een database van enige omvang is in de praktijk altijd een onderdeel van een groter geheel, dat men dikwijls informatiesysteem noemt. Hieronder worden niet alleen de software en de gegevensverzamelingen verstaan, maar ook de benodigde hardware, de mensen die met de database moeten werken en de procedures volgens dewelke die mensen werken. Het ontwikkelen van zo'n informatiesysteem gebeurt doorgaans in het kader van een project. Aan zo'n project werken twee groepen mensen samen: degenen die de software maken (ontwerpers of bouwers) en de gebruikers. Enkele activiteiten zijn in zo'n project altijd te herkennen: ✔ Start van het project. Allereerst wordt afgesproken wie deel uitmaakt van het projectteam en hoeveel tijd en geld ermee gemoeid zijn. Het projectteam bakent dan het probleem af in een projectplan, zodat men weet wat wel en niet onder het project valt. ✔ Analyse van het probleem door ontwerpers en gebruikers. Het resultaat is een serie modellen van bijvoorbeeld relevante gegevens en processen, en van de context van het probleem. Deze modellen vullen elkaar aan en vormen samen een 'kenmodel' van het probleem. Men weet nu hoe het probleem in elkaar zit. ✔ Het ontwerp van elk onderdeel van de software. Het resultaat is een serie ontwerpen, bijvoorbeeld datamodel, mens/computer-dialogen en algoritmen. Algoritmen leggen de besturingsstructuur vast, bijvoorbeeld van rekenprocessen. Dialogen leggen de mogelijkheden voor gebruikers vast. Bij een database is vooral de gegevensmodellering van belang, die het gegevensmodel of datamodel oplevert. Samengevat levert de ontwerpfase een 'maakmodel' op van de te bouwen software. Dat is te vergelijken met de maquette van een gebouw: toekomstige gebruikers kunnen aan het ontwerp zien hoe de software er uit komt te zien, en als de gebruikers het anders willen, kunnen de ontwerpen deze wensen nog verwerken in het ontwerp. ✔ De bouw van elk onderdeel van de software. Resultaat is: werkende databases en/of programmatuur. Komen er nu nog ontwerpfouten aan het licht, dan wordt wijzigen duur en tijdrovend. Afhankelijk van de gevolgde methode zullen deze activiteiten als stappen te herkennen zijn of samengevoegd worden. De laatste drie activiteiten kunnen ook een aantal malen cyclisch doorlopen worden. Documenten die uit analyse en ontwerp komen, worden gedurende het project gebruikt voor communicatie tussen de betrokkenen en als basis voor de bouw. Een gegevensmodel legt de hoofdstructuur van de gegevens vast: welke tabellen komen er en welke kolommen hebben ze? En hoe staan ze in verband met elkaar? Eenmaal een database gebouwd is en er blijkt dat er in de hoofdstructuur belangrijke fouten zitten, dan kost het gauw erg veel geld om deze nog recht te zetten. Een gegevensmodel doet twee dingen tegelijkertijd: beschrijven en begrenzen. Het is te vergelijken met een geheugen dat alleen bepaalde vooraf gedefinieerde typen kan onthouden. Net als bij elk ander geheugen, zoals dat van een mens, geldt: wat het gegevensmodel niet kan bevatten kan het ook niet onthouden. Het is dan ook belangrijk bij het ontwerp om na te gaan wat kan bestaan, niet wat zou moeten bestaan. Indien men volgens het laatste ontwerpt zou al gauw blijken dat het model niet in staat zal zijn de realiteit te bevatten.
1.5. EEN VOORBEELD We bekijken het voorbeeld van een headhunterbureau: het bemiddelt tussen Databanken
8
hooggekwalificeerde werkzoekenden en organisaties op zoek naar personeel. De werking ervan is als volgt: het bureau contracteert een aantal headhunters, ieder bekend binnen een bepaald circuit, die discreet in de gaten houden welke mensen er eventueel wel van job zouden willen veranderen. Organisaties met vacatures bellen het bureau, waarna dat de geschikte headhunters aan het werk zet. Wanneer personen gevonden worden brengt het bureau beide partijen met elkaar in contact. Het bureau krijgt hiervoor een vergoeding van de organisatie. Bij de start van het bedrijf is er niets geautomatiseerd. Het bureau houdt een lijst bij van headhunters en welke bedrijven binnen het circuit van de headhunter vallen. Deze lijst bevat naam en telefoonnummer van de headhunter plus voor elk bedrijf de naam, de branche en de jaaromzet. Elk bedrijf wordt door slechts één headhunter opgevolgd. Na enige tijd blijkt dat telefoonnummers nogal eens wijzigen, dat de bedrijven gevolgd door een headhunter ook nogal eens wijzigen, en dat bedrijven zelf soms verhuizen of fuseren. Een database dringt zich op. Een medewerker van het bureau maakt de volgende tabel aan: Naam
Hans Koppens
Lieve Desitter
Louis Dewaele
Telefoonnr
3235674389
32472345612
3214567890
Org1
Verre Reizen
Devos Dankers
Biocontruct
toerisme
voeding
bouw
300
1780
460
Org2
Fly away
Graankorrel
-
Branche2
toerisme
voeding
-
740
540
-
Org3
-
Tafel dek je
-
Branche3
-
voeding
-
Omzet3
-
760
-
Branche1 Omzet1
Omzet2
Al tijdens de invoer van de tabel blijken er enkele bezwaren: vele headhunters volgen slechts één of twee organisaties, waardoor er veel witruimte in het bestand blijft. Na enige tijd komt echter een groter tekort aan het licht: een headhunter neemt er een vierde organisatie bij, maar de tabel voldoet daar niet voor. Bovendien groeit de tabel snel en wil men na enige tijd een overzicht van alle organisaties waarvoor het bureau over een headhunter beschikt. Het blijkt dat het erg moeilijk is dit te bekomen. Het bovenstaande bestand wordt vervangen door het volgende: Organisatie
Branche
Omzet
Headhunter
Telefoonnr
Verre Reizen
toerisme
300
Hans Koppens
3235674389
Devos Dankers
voeding
1780
Lieve Desitter
32472345612
Bioconstruct
bouw
460
Louis Dewaele
3214567890
Fly away
toerisme
740
Hans Koppens
3235674389
Graankorrel
voeding
540
Lieve Desitter
32472345612
Tafel dek je
voeding
760
Lieve Desitter
32472345612
Met dit bestand zijn de gemelde problemen opgelost. Headhunters kunnen nu zoveel Databanken
9
organisaties volgen als ze willen, er is geen witruimte meer, en het is gemakkelijk een overzicht te krijgen van de organisaties waarmee gewerkt wordt. Na enige tijd blijkt echter dat ook dit bestand een aantal problemen kent: ✔ Headhuntergegevens worden op verschillende plaatsen bewaard. Dat heeft als belangrijk nadeel dat wanneer het telefoonnummer van een headhunter verandert, dat moet gewijzigd worden op verschillende plaatsen. Fouten zijn op deze manier praktisch onvermijdelijk. ✔ Wanneer een headhunter een bijkomende organisatie opvolgt, moeten zijn gegevens opnieuw ingevoerd worden. ✔ Het blijkt moeilijker een overzicht te krijgen van alle headhunters waarmee gewerkt wordt. ✔ Indien men headhunters wil opslaan die tijdelijk geen enkele organisatie volgen, moet men een lege organisatie invoeren, wat niet erg elegant is. De medewerker die dit tweede voorstel heeft gedaan, wordt geconfronteerd met deze tekortkomingen. Hij komt tot het besef dat er eigenlijk twee soorten gegevens zijn, die best afzonderlijke worden opgeslagen: headhunters en organisaties. Als dan bij elke organisatie wordt aangeduid door welke headhunter ze wordt gevolgd, zijn alle gemelde problemen opgelost. Hij komt tot het volgende voorstel: Headhunter
Naam
Telefoonnr
1
Hans Koppens
3235674389
2
Lieve Desitter
32472345612
3
Louis Dewaele
3214567890
Organisatie
Branche
Omzet
Headhunter
Verre Reizen
toerisme
300
1
Devos Dankers
voeding
1780
2
Bioconstruct
bouw
460
3
Fly away
toerisme
740
1
Graankorrel
voeding
540
2
Tafel dek je
voeding
760
2
Na verloop van tijd blijkt dat deze structuur inderdaad alle problemen oplost. We zagen dat het niet goed is meermaals hetzelfde gegeven op te slaan. Dat wordt in deze structuur vermeden. Om daartoe te komen werd een extra veld ingevoerd: elke headhunter kreeg een nummer dat enkel en alleen wordt gebruikt als unieke verwijzing naar die headhunter. Er is geen enkele nood om deze verwijzing ooit te moeten wijzigen. De gegevens zelf van die headhunter, die wellicht wel gewijzigd dienen te worden, worden slecht éénmaal opgeslagen. Opgave: Wat gebeurt er indien het bureau van strategie verandert en toelaat dat een bedrijf door meer dan één headhunter wordt opgevolgd? Voldoen de tabellen nog steeds? Waarom wel of waarom niet? Indien niet, hoe kunnen we ze aanpassen dat ze terug voldoen?
Databanken
10
1.6. SAMENVATTING We hebben kennis gemaakt met databases. Hoofdzaken daarbij waren: ✔ Een database is een verzameling met elkaar samenhangende, al of niet ware beweringen, opgeslagen op een computer en te benaderen via daartoe bestemde software. ✔ Er kunnen vele gebruikers tegelijk met dezelfde database werken. ✔ De software, het database-management-systeem, vertaalt tussen de gegevens en wat mensen met die gegevens willen doen enerzijds, en tussen gegevens en hoe ze in de computer zijn opgeslagen anderzijds. Daardoor kunnen verschillende gebruikers tot verschillende delen van de database toegang hebben, en kan de database over verschillende computers worden opgeslagen. ✔ Alle gegevens in een database kunnen met elkaar in verband gebracht worden. ✔ Er is een data-dictionary, een soort geautomatiseerd naslagwerk met een overzicht over alle gebruikers, gegevens en geheugens. We hebben ook nog gezien dat het ontwerpen en bouwen van een database een kwestie is van projectmatig groepswerk, en dat het ontwerpen van een gegevensmodel daarbij een centrale plaats inneemt. Voor het gebruiken en beheren van een database is SQL de wereldstandaardtaal.
1.7. DATABASES ONTWERPEN We gaan in de volgende hoofdstukken dieper in op het concreet ontwerpen van een database. In hoofdstuk 2 zien we hoe we de gegevensmodellering uitvoeren. Om hiertoe in staat te zijn is het nodig om een aantal begrippen nauwkeurig te hanteren. Vaak worden er termen voor gebruikt die in het dagelijks leven of in andere vakgebieden een andere betekenis dragen. We beginnen dan ook met uitleg over de taal van gegevensmodellering. Daarna volgt de theorie van de middelste twee stappen: analyse en ontwerp. We behandelen daaronder twee ontwerpstijlen: beginnend bij de grote lijnen (top-down) in hoofdstuk 3 of beginnend bij de details (bottom-up) in hoofdstuk 4. In hoofdstuk 5 gaan we dieper in op het begrip relationele database, en wat het verondersteld wordt te kunnen. De laatste drie hoofdstukken zijn gewijd aan het gebruik van SQL.
Databanken
11
2. GEGEVENSMODELLERING EN INFORMATIEANALYSE We gaan in dit hoofdstuk dieper in op gegevensmodellering. Om hiertoe in staat te zijn is het nodig om een aantal begrippen nauwkeurig te hanteren. Vaak worden er termen voor gebruikt die in het dagelijks leven of in andere vakgebieden een andere betekenis dragen. We beginnen dan ook met uitleg over de taal van gegevensmodellering.
2.1. GEGEVENSMODELLERING BINNEN EEN PROJECT Zoals we reeds besproken hebben wordt een informatiseringsproject gestart met de probleemafbakening, daarna de analyse, dan het ontwerp en tenslotte de bouw. Zowel in de analyse- als in de ontwerpfase kunnen gegevensmodellen gemaakt worden. In een project van beperkte omvang, bijvoorbeeld wanneer iemand voor zichzelf een database opzet, zal men die twee fasen niet scheiden en in dat geval is er dan ook slechts één enkel gegevensmodel. Is er sprake van een `bestaande situatie' of zijn er bij het projecrt veel mensen betrokken, dan zullen er vaak een aantal gegevensmodellen na elkaar worden gemaakt. De eerste daarvan zijn dan meer bedoeld om de informatiebehoeften in kaart te brengen, de laatste om het uiteindelijke ontwerp weer te geven. Er zijn veel verschillende manieren om een informatiseringsproject in te richten. Men noemt ze wel System Development Methodologies (systeemontwikkelmethoden). Eén ervan is bijvoorbeeld SDM, waarvoor er inmiddels twee opvolgers zijn: IAD en LAD voor het evolutionair dan wel lineair ontwikkelen van informatiesystemen. IAD staat voor Iterative Application Development en LAD voor Linear Application Development. Over beide methodieken is een toegankelijk boek in het Nederlands voorhanden (zie Tolido, 1996 en Fokkinga et al. 1996). Wat ook de gekozen methode is, in alle gevallen is het zo dat meer aandacht voor het gegevensmodel in een vroeg stadium van de analyse, zichzelf steeds terugverdient in de loop van het project. Een zorgvuldig opgestelde, robuuste gegevensstructuur is een cruciale factor in de meeste systeemontwikkeltrajecten. Zelfs al gaat het over een kleiner persoonlijk project waarvoor geen veelzijdige systeemontwikkelmethode wordt gevolgd, dan nog blijft het gegevensmodelleren van erg groot belang.
2.2. DE TAAL VAN GEGEVENSMODELLERING 2.2.1. Probleemafbakening: de Universe of Discourse Een project begint met de afbakening van het probleem. In plaats van het woord 'probleem' wordt hier vaak het begrip Universe of Discourse gebruikt. Het Universe of Discourse is datgene waarvan men afspreekt dat het binnen de afbakening van het probleem valt. Het is dus het onderwerp waarvoor een informatiesysteem gebouwd wordt. Het middel om een Universe of Discourse af te bakenen is overleg, vandaar de naam. In eerste instantie zijn gesprekken of vergaderingen nodig om vast te stellen wat voor project er ongeveer moet worden gestart, en wie de kosten op zich neemt. Vervolgens zullen workshops met de betrokken personen (ontwikkelaars, toekomstige gebruikers, hun bazen, specialisten in relevante vakgebieden) worden gehouden om de veranderingsbehoeften, informatiebehoeften en daarmee de gewenste afbakening nauwkeuriger te bepalen. Het resultaat van dit alles vormt een schriftelijke definitie van Databanken
12
eisen. De term Universe of Discourse legt er de nadruk op dat alle betrokkenen het eens dienen te worden over welke dingen er binnen het project vallen. Het Universe of Discourse is de basis voor de gegevensmodellering. Als een gegevensmodel een adequate afbeelding van het Universe of Discourse vormt, kan dit gegevensmodel ook vragen over dit Universe of Discourse correct beantwoorden die tijdens de ontwerpfase nog niet aan de orde waren. Voorts is een databaseontwerp in principe in allerlei typen software en op allerlei computers te bouwen. De investering in een goed doordacht gegevensmodel verdient zichzelf dus terug.
2.2.2. Object, kenmerk en domein Er zijn vele manieren om de werkelijkheid uit het Universe of Discourse te modelleren. Bij de meeste werkwijzen beschrijft men de waargenomen werkelijkheid als een verzameling dingen, objecten genoemd, waarover men iets wil vastleggen. Elk object heeft een aantal kenmerken: de gegevens die men over het object wil weten. Elk object behoort to een objectsoort of objecttype. De objectsoort is te definiëren door de kenmerken van de objecten van die soort op te schrijven. Objecten van die soort hebben voor elk van die kenmerken een waarde. In het voorbeeld van hoofdtsuk 1 zijn headhunter en organisatie twee objectsoorten. Headhunternaam en telefoonnummer zijn kenmerken van objectsoort headhunter; naam, branche en omzet zijn kenmerken van de objectsoort organisatie. 'Hans Koppens' is één waarde van het kenmerk headhunternaam. Om als object te worden weerhouden moet aan twee voorwaarden worden voldaan: ✔ Elke representatie van het object kan op een of andere manier uniek geidentificeerd worden. Er moet dus steeds een verschil kunnen gemaakt worden tussen de afzonderlijke representaties van het objecttype. Bijvoorbeeld bij het object headhunter doordat ze elk een uniek nummer hebben. ✔ Elke representatie van het objecttype speelt een belangrijke rol in het door ons te ontwerpen systeem. Het systeem kan zijn werk niet doen als de representaties van het objecttype niet geraadpleegd kunnen worden. In veel systemen zijn de objecttypen de voorstelling van materiële zaken in de reële wereld (headhunter, klant, factuur,...). Een object kan echter ook niet-stoffelijke zaken betreffen (planning, tijdschema,...). Bovendien kan dezelfde materiële werkelijkheid in het systeem als verschillende objecten te voorschijn komen. Zo kan dezelfde persoon bijvoorbeeld zowel werknemer zijn van een organisatie als klant van die organisatie. Deze structurering van de werkelijkheid in objecten en kenmerken keert terug in gegevensmodellen en in de opslagstructuur van gegevens. In het voorbeeld van hoofdstuk 1 bijvoorbeeld zouden we de objectsoorten in de database terugvinden als tabellen en de kenmerken van deze objectsoorten als kolomhoofden. De objecten zijn te vinden als rijen in de tabellen. In zo'n rij staat voor elk object voor elk kenmerk een waarde. Om een zo groot mogelijke duidelijkheid na te streven verdient het aanbeveling om bij de naamgeving van de tabellen en kolomhoofden zoveel mogelijk unieke namen te gebruiken. Zo zou het in het voorbeeld van hoofdstuk 1 wellicht beter geweest zijn om in de tabel headhunter de kolomnaam headhunternaam te gebruiken ipv kortweg naam. Een domein is de verzameling waarden die een kenmerk kan aannemen. Het kenmerk telefoonnummer bijvoorbeeld is gedefiniëerd op het domein {telefoonnummers}, dat dan alle mogelijke telefoonummers bevat. In de regel is het een goede gewoonte om kenmerken van verschillende objecten die op eenzelfde domein gedefiniëerd zijn, dezelfde naam te geven. In het voorbeeld van hoofdstuk 1 deden we dat bijvoorbeeld met Databanken
13
het unieke nummer dat we aan elke headhunter gaven. We gaven het daar de naam 'headhunter' maar hadden het wellicht beter een naam gegeven als 'ID' om verwarring met de tabelnaam 'headhunter' te vermijden. In dat geval ging het over een kenmerk dat de koppeling verzekert tussen de twee tabellen, en dringt eenzelfde naam zich ook vanuit dat oogpunt op. Maar ook voor kenmerken die niet aan elkaar gelinkt zijn maar wel op eenzelfde domein gedefiniëerd zijn verdient het aanbeveling eenzelfde naam, of minstens een naam met dezelfde stam te gebruiken.
2.2.3. Type en individu In de omgangstaal wordt zelden onderscheid gemaakt tussen type en individu. Bij gegevensmodellering is dat onderscheid echter wezenlijk. Bijvoorbeeld: een boom is een ding, de oude eik ook. Gaan we nauwkeuriger kijken, dan zien we dat de oude eik slechts één van de elementen is van de verzameling bomen, anders gezegd: een individu van type boom. In een gegevensbestand is een verzameling van elementen terug te vinden als een bestand van records. In een bestand van alle monumentale bomen in een gemeente zou één van die records 'de oude eik' kunnen betreffen. Exemplaar
Soort
Ligging
Kaartcoördinaten
de parkboom
rode beuk
stadspark
E23
de huilbom
treurwilg
gracht
D26
de oude eik
zomereik
stadspark
E24
de nieuwe eik
zomereik
gemeentehuis
A40
Bovenstaande tabel zouden we de naam 'boom' kunnen geven. Een ander bestand met dezelfde naam is ook denkbaar: Genus
Species
Nl-naam
Max-hoogte
Quercus
robur
zomereik
20
Fagus
sylvatica
beuk
25
Castanea
sativa
tamme kastanje
12
Bij nader toezien zouden we de tweede tabel echter beter de naam 'boomsoort' geven ipv 'boom'. De rijen in die tabel zijn immers geen individuele bomen maar boomsoorten. De moraal is dat een term zoals 'boom', die zowel een verzameling typen als een verzameling individuen kan aanduiden, geen goede naam is om in een gegevensmodel te gebruiken als naam voor een objecttype. Het is dus steeds van belang goed na te denken over de naamgeving om verwarring of misleiding zoveel mogelijk te vermijden. In onderstaande tabel worden de verschillende namen die in de verschillende stadia van een informatiseringsproject worden gebruikt nog eens samengevat. Universe of Discourse
gegevensmodel
Relationele meta-model
database
opslagstructuur
object
entiteittype
relatie
tabel
bestand
element
entiteit
tuple
rij
record
kenmerk
attribuut
attribuut
kolom
veld
waarde
attribuutwaarde
attribuutwaarde
veldwaarde
inhoud
Databanken
14
2.2.4. Feit Een feit is de eenheid van gegevens in een gegevensmodel. De attribuutwaarden die uiteindelijk in een database worden opgeslagen, dienen om feiten over het Universe of Discourse vast te leggen. Een feit is, in deze betekenis, eigenlijk een bewering over het Universe of Discourse. Er zijn in een gegevensmodel verschillende soorten feiten. Gaan we terug naar ons voorbeeld van hoofdstuk 1 dan kunnen we daarover onder andere de volgende beweringen doen: ✔ 'Hans Koppens is een headhunter. Deze bewering duidt erop dat er een individu van type 'headhunter' bestaat. ✔ 'Hans Koppens heeft telefoonnummer 3235674389'. Deze bewering kent aan een al bestaand individu van type 'headhunter' een waarde toe voor een van de kenmerken. ✔ 'Hans Koppens volgt Verre Reizen'. Deze bewering legt een verband tussen een individu van type 'headhunter' en een individu van type 'organisatie'.
2.2.5. Gegevensdefinitie en gegeven Nog een onderscheid dat van groot belang is bij het werken met gegevensmodellen is dat tussen de beschrijving van de gegevens in termen van objecttypen, kenmerken en dergelijke aan de ene kant, tegenover de waarden van de gegevens aan de andere kant. Korter geformuleerd: tussen gegevensdefinitie en gegevens. Laten we even teugkeren naar het voorbeeld van de monumentale bomen in een gemeente. 'Boom' was hier de naam van het bestand, dus een gegevensdefinitie. Maar 'boom' zou ook een veldwaarde kunnen zijn, dus een gegeven. Als we bijvoorbeeld in een plantkundig onderzoek een verzameling groeivormen definiëren, zou 'boom' een van de groeivormen kunnen zijn. In de computer zou men dan een bestand 'groeivorm' kunnen aantreffen, waarin één van de records de groeivorm 'boom' beschrijft. 'Boom' doet dan dienst als waarde van het kenmerk 'naam-groeivorm'. naam-groeivorm
hoogte
levensduur
eenjarig kruid
0-3 m
1 jr
overblijvend kruid
0-3 m
2-4 jr
heester
0-3 m
> 10 jr
boom
>3m
> 10 jr
2.2.6. Gegeven en gegevensdrager De verschijningsvorm van een gegeven, anders gezegd de gegevensdrager, kan bijvoorbeeld papier zijn, maar ook een diskette of een CD-ROM. Een gegeven dat altijd in een bepaalde verschijningsvorm optreedt, wordt nog wel eens met die verschijningsvorm vereenzelvigd of zelfs verward. Men zegt bijvoorbeeld: `Ik heb een bon gekregen' terwijl het niet om die bon gaat, maar om de gegevens: een overtreding en een bijbehorende boete. Misschien verdwijnen papieren bonnen ooit, maar overtredingen en boetes daarvoor zullen blijven. In een gegevensmodel gaat het om de betekenis van de gegevens, niet om de verschijningsvorm. Of ze op papier, diskette of een webpagina staan is dus niet van Databanken
15
belang. Zo is, om op het bomenvoorbeeld terug te komen, 'boom' een heel ander type object dan 'bomenlijst'. Een bomenlijst is immers niet meer dan een stapel papier. Het gebeurt maar heel zelden dat men een gegevensmodel van bomenlijsten, of - in het algemeen - van gegevensdragers, nodig heeft.
2.2.7. Besluit: naamgeving is essentieel Wat vooral belangrijk is om te onthouden is hoe wezenlijk een goede naamgeving van de objecten en kenmerken in een gegevensmodel is. Slechte namen duiden vaak op een onvolledig begrip van het Universe of Discourse bij de ontwerper en leiden bij andere projectleden tot misverstanden. Enkele tips bij het geven van namen zijn: ✔ Wees consequent in het hanteren van afkortingssystemen. Afkorten is tegenwoordig vaak niet meer nodig. Doe het alleen wanneer het nodig is. ✔ Baseer de naam van een attribuut op het domein van dat attribuut, tenzij dit zou leiden tot dubbele namen binnen een entiteittype. Ook dan nog is het goed om namen met dezelde stam te gebruiken. Stel dat we bijvoorbeeld een domein 'telefoonnummer' hebben. We willen bij het entiteittype 'persoon' een thuisnummer en een mobiel nummer opslaan. We kunnen dan de attribuutnamen 'thuistelefoonnummer' en 'mobieltelefoonnummer' gebruiken. ✔ Gebruik geen dubbelzinnige namen, zoals 'boom' wanneer 'boomsoort' wordt bedoeld. Het leidt bijna zeker tot verwarring bij een aantal van de projectleden. ✔ Gebruik telbare begrippen (bijvoorbeeld niet `flora' maar `plantensoort') voor de namen van entiteittypen. ✔ Geef entiteittypen niet dezelfde naam als hun attributen. ✔ Verwar de gegevensdrager niet met het gegeven zelf. ✔ Kies namen die de toekomstige gebruikers begrijpen. Ga dit met hen na. Dit punt, dat wel eens strijdig kan zijn met de andere punten, is het allerbelangrijkste.
2.3. INFORMATIEANALYSE Informatieanalyse en ontwerp worden soms gescheiden, soms verweven. Wat het beste is hangt van een groot aantal zaken af, waaronder de aard van het project en de gewoonten van de ontwerper. Hoe het ook zij, eerst moet de ontwerper in kaart brengen wat er allemaal aan gegevens nodig is, voordat de structuur van die gegevens duidelijk kan worden. Ditzelfde geldt voor de processen en de interfaces van een informatiesysteem. Het in kaart brengen noemen we 'informatieanalyse', het structureren 'ontwerp'. Analyse en ontwerp van de gegevens worden samen ook wel 'gegevensmodellering' genoemd. Een goede reden om de informatieanalyse niet te beschouwen als een aparte fase is de volgende. De informatieanalyse levert niet echt een eindproduct op. Pas na het ontwerp is er een afgebakend, duidelijk eindproduct, namelijk het genormaliseerde gegevensmodel met bijbehorende precieze data-dictionary. Omwille van de duidelijkheid zullen we in wat volgt de informatieanalyse en het ontwerp (zie daarvoor hoofdstukken 3 en 4) apart behandelen.
Databanken
16
2.3.1. Vier vragen De informatieanalyse stelt vier vragen waarop een antwoord moet komen: ✔ Welke informatie zal de database moeten kunnen leveren? Dat is de vraag naar het eindproduct van de database, inclusief de kwaliteit daarvan. Hoe dikwijls, hoe snel, op welke plaatsen moeten die gegevens worden geleverd? Ook de vraag wie dat moet doen hoort hierbij. ✔ Welke gegevens moet de database bevatten om in de vastgestelde informatiebehoefte te kunnen voorzien? Dit is de vraag naar het basismateriaal, de feiten die in de database moeten worden vastgelegd. In veel gevallen zijn deze gelijk aan de gevraagde gegevens uit het vorige punt, maar dat is niet altijd zo. In onderzoeksdatabases of in beslissingsondersteunende systemen hebben gebruikers allerlei geaggregeerde overzichtsgegevens nodig. In Executive Information Systems (EIS), een type beslissingsondersteunend systeem voor managers, tapt het systeem gegevens af uit een database met elementaire feiten over de bedrijfsvoering die op de werkvloer worden verzameld, om er overzichtsstatistieken van te kunnen maken over productiviteit, verkoopcijfers en dergelijke. ✔ Welke gegevens zijn er beschikbaar? Dit is de vraag of, hoe en tegen welke kosten de benodigde basisgegevens te verkrijgen zijn. Het komt nogal eens voor dat de benodigde gegevens er domweg niet zijn, zodat men zijn doelen moet bijstellen of eerst iets moet bedenken om de gegevens te verzamelen. Bij onderzoeksprojecten is het bijvoorbeeld veel voorkomend dat relevante gegevens volledig ontbreken. Als er inderdaad gegevens nodig zijn die niet - of slechts tegen hoge kosten - kunnen worden verkregen, zal de projectleiding hierover moeten besluiten voordat de ontwerpers verder kunnen gaan. ✔ Hoe is het verband tussen de benodigde gegevens? Deze vraag luidt: Welke objecten met welke kenmerken vallen er te onderscheiden, en hoe hangen ze samen? Het antwoord is een eerste voorlopig gegevensmodel, en wel één dat dient als hulpmiddel in de discussie tussen ontwerper en probleemhebbers. Hierbij is het belangrijk te modelleren wat er zou kunnen gebeuren, niet wat er zou moeten gebeuren. Immers, alleen wat in het model is opgenomen, kan later door het informatiesysteem worden voortgebracht. Inwat volgt behandelen we deze vierde vraag omdat het veruit de lastigste is. Dit is immers de stap waarbij de ontwerper de vertaling moet maken van 'realiteit' naar 'data'. Het voornaamste doel van de informatieanalyse is om niets over het hoofd te zien. Het resulterende model is een kenmodel van het Universe of Discourse, nog geen maakmodel voor de database.
2.3.2. Beginnen Alle begin is moeilijk, zo is het ook bij het maken van een gegevensmodel. Er zijn twee strategieën mogelijk. Men kan beginnen met de grote lijnen: top-down werken. Of men kan ook andersom werken, beginnend bij de attributen waarvan men weet dat ze nodig zijn. Deze aanpak heet bottom-up. Beide methoden hebben hun voor- en nadelen. Bij de top-down aanpak ziet men het duidelijkst de grote lijn, hetgeen een logisch vervolg is op de afbakening van het Universe of Discourse. Bij bottom-up is de kans op slordigheidsfouten kleiner, maar deze aanpak leidt soms tot afdwalen van de oorspronkelijke vraag, en tot een grotere afstand tussen ontwerper en gebruiker. In de praktijk werkt een ontwerper meestal in eerste instantie top-down en wordt de bottom-up aanpak gebruikt om de puntjes op de i te zetten. Databanken
17
Een beginnend ontwerper, die van start gaat met een informatieanalyse, heeft het extra moeilijk. Hij mist de intuïtie en ervaring om een eerste grof model op te stellen; tegelijkertijd weet hij nog onvoldoende over de kenmerken die nodig zijn om bottom-up te kunnen werken. Er zijn dan drie mogelijkheden: beginnen bij feiten over het Universe of Discourse die men uit interviews heeft afgeleid, bij teksten erover, of bij formulieren en bestanden die al bekend zijn en waarvan de inhoud in de database terecht moet komen. De analyse maakt dus gebruik van interviews en tekstanalyses. In zowel mondelinge als schriftelijke communicatie spelen "zinnen" een hoofdrol. Hoe wordt nu de analyse van zo'n zin uitgevoerd? Stel dat de volgende zin relevant is in het te analyseren informatiegebied: "Docent Paul verzorgt de vakken Databases en Analyse". Over het onderwerp van de zin, docent Paul wordt een uitspraak gedaan, namelijk dat hij de vakken databases en analyse verzorgt. Een dergelijke uitspraak noemen we een predikaat. Dit predikaat bevat een werkwoordsvorm (verzorgt) en een lijdend voorwerp (databases en analyse). Als we specifieke waarden van onderwerp en lijdend voorwerp nu abstraheren komen we tot een uitspraak als: docenten verzorgen vakken. Hierin ontdekken we twee mogelijke entiteiten en een relatie.
2.4. HOE BEGINNEN? Voor de concrete analyse kan men vanaf verschillende elementen vertrekken. Een eerste mogelijkheid is te beginnen met feiten, die men uit interviews haalt. Een tweede mogelijkheid is te starten vanaf bestaande teksten. En een derde mogelijkheid tenslotte is te beginnen met formulieren, lijsten of bestanden. Het spreekt voor zich dat vaak een combinatie van bovenstaande de meest volledige informatie zal geven over het informatiegebied.
2.4.1. Beginnen met feiten Het zoeken naar feiten, in de database-technische betekenis van het woord, kan een goede start bieden. Een mogelijkheid om hier vorm aan te geven kan er in bestaan een lijst op te stellen met allerhande verschillende soorten feiten die de database zal moeten weten. Voor het voorbeeld van hoofdstuk 1 zouden dat bijvoorbeeld kunnen zijn (zie ook hoger): ✔ Hans Koppens heeft telefoonnummer 3235674389. ✔ Verre Reizen heeft een omzet van 300. ✔ Hans Koppens is een headhunter die Verre Reizen opvolgt. Uit elk van deze feiten kunnen kenmerken (eigenlijk kenmerktypes) of entiteittypen worden afgeleid. Elk zelfstandig naamwoord kan een entiteittype of een kenmerk worden. Elke naam of elk nummer kan een identificerend kenmerk worden van een bijbehorend entiteittype. Het samen in een zin voorkomen van zaken betekent dat ze in verband staan met elkaar: misschien is het één een kenmerk van het ander, of zijn het twee entiteittypen waartussen een verband bestaat. Werkt men top-down, dan zal men op zoek gaan naar entiteittypen. Werkt men bottomup, dan zoekt men naar kenmerken en blijkt pas later welke kenmerken samen in een entiteittype terechtkomen. Het verschil tussen entiteittype en kenmerk kent u al: een entiteittype is iets waarover men één of meer zaken wil weten, en die zaken die men over een entiteittype wil weten zijn de kenmerken. Of u iets ziet als entiteittype of als kenmerk Databanken
18
hangt dus af van wat u wilt weten. Laten we eens nagaan welke entiteittypen en kenmerken in het voorbeeld hierboven te herkennen zijn. ✔ Hans Koppens is de naam van een headhunter: we hebben dus een entiteittype 'headhunter' met kenmerk 'naam'. Een tweede kenmerk is 'telefoonnummer'. Een entiteittype 'telefoonnummer' lijkt niet zinvol: de vraag dringt zich dan meteen op 'waarvan?' Van die headhunter natuurlijk! Dat geeft aan dat het telefoonnummer op zichzelf in dit geval geen bestaansrecht heeft, maar een kenmerk is van 'headhunter'. ✔ Op dezelfde manier kunnen we een entiteittype 'organisatie' met kenmerken 'naam' en 'omzet' detecteren. ✔ In de derde zin worden twee dingen aan elkaar gekoppeld: een headhunter en een organisatie. Dit is een koppeling tussen een headhunter en een organisatie. Wanneer we op deze manier alle genoteerde feiten overlopen kunnen we tot een vrij volledig beeld komen van alle entiteittypes en kenmerken. Uiteraard zal het zo bekomen model nog veranderen. Het is wel meer een houvast omop te kunnen voortbouwen dan een doel op zich.
2.4.2. Beginnen met teksten Het kan voorkomen dat u niet ver genoeg komt met het verzamelen van feiten, of dat er geen gebruiker te vinden is die begrijpt wat u bedoelt wanneer u om `feiten' vraagt. Teksten kunnen dan een alternatief zijn. Vaak zijn er wel teksten voorhanden die het Universe of Discourse beschrijven. Ook kan men gewoon met gebruikers over het probleemgebied praten. Ook deze teksten en uitspraken bevatten feiten die gebruikt kunnen worden om entiteittypen en kenmerken te vinden. Wel is er dikwijls nog een vertaalslag nodig om een stuk tekst om te zetten naar database-feiten. Staat er bijvoorbeeld in een document 'dat men de beschikking heeft over een lijst met alle personeel van de onderneming' dan betekent dat niet dat er een entiteittype 'personeellijst' moet komen. Personeellijst is in dit geval niet meer dan de gegevensdrager voor entiteiten van type 'personeelslid'. Meestal is het een goed idee de teksten te herleiden tot een aantal eenvoudige predikaten: dit wordt decompositie genoemd.
2.4.3. Beginnen met formulieren, lijsten of bestanden Soms heeft de ontwerper aan het begin van een informatieanalyse al meer houvast, namelijk als er al formele gegevensstructuren bestaan die in de database moeten worden overgenomen. Het kan dat er lijsten met gegevens voorhanden zijn (bijvoorbeeld wekelijkse bestellijst), dat er bepaalde formulieren voorhanden zijn (orders),.... Zulke formulieren of bestanden hebben meestal de structuur van één entiteittype met een aantal kenmerken, namelijk de velden in het formulier of bestand. Als er herhaalde regels in een lijst staan, hebben die meestal betrekking op een eigen entiteittype dat een veel-op-één verband heeft met het andere entiteittype. Een orderformulier bijvoorbeeld geeft aan dat er een entiteit van type 'order' bestaat, maar zal meestal ook een aantal lijnen bestelde artikelen bevatten, wat erop wijst dat er een entiteit van type 'bestelartikel' bestaat date een veel-op-één relatie heeft met 'order'. Een gevaar van deze aanpak is dat men verkeerde gegevensstructuren uit het verleden zou kunnen afleiden uit deze bestaande lijsten. Het moet dus altijd met een kritisch oog gebeuren.
Databanken
19
2.5. OPGAVEN 1. Bekijk het voorbeeld in hoofdstuk 1. Hoeveel objectsoorten, objecten en kenmerkwaarden bevat deze database? Op hoeveel domeinen zijn de kenmerken uit het voorbeeld gedefiniëerd? 2. Het is meestal wenselijk om attributen die op hetzelfde domein zijn gedefiniëerd ook dezelfde naam te geven. Welk attribuut zou je in het voorbeeld van hoofdstuk 1 kunnen toevoegen waar je dat mee doet? En wat zou je doen indien je van de headhunters ook mobiel telefoonnummer en telefoon op het werk zou willen bijhouden, naast de telefoon thuis? 3. Bedenk een situatie waarin "auto" een verzameling typen is, één waarin het een verzameling individuen is, en één waarin het een waarde is van een kenmerk. Geef in alle drie de gevallen aan hoe men dit kan opslaan in een computer in termen van bestanden en records. 4. In een magazijn liggen duizenden artikelen opgeslagen. De artikelen zijn onderverdeeld in soorten en iedere soort heeft een nummer en een naam (bijvoorbeeld nummer 342789 met naam 'wasknijper'). Van iedere soort zijn er één of meerdere aanwezig in het magazijn. Welke objecten zie je hier met welke attributen? 5. Hieronder ziet u de naam van enkele tabellen met hun kolommen. Het gaat om de database voor de ledenadministratie van een sportclub. Het gegevensmodel erachter klopt, maar de namen zijn niet erg gelukkig gekozen. a) Welke tekortkomingen zie je? b) Welke problemen zullen die geven in het gebruik? ETIKETTEN (anr, str, hnr, pc, wp) LEDENLIJST (naam, voornaam, geboren, nr, teamnr, adres) TEAM (teamno, categorie, poule, naam-t, naam_c) CATAGO (code, max, min) 6. Hoe zou jij de naamgeving doen in bovenstaande model? 7. In een bedrijf is de dienst "werving personeel" verantwoordelijk voor het aanwerven van personeel. Hieronder volgt een beschrijving van wat ze doen. Herleid de tekst tot een aantal predikaten (decompositie). "Voor vacatures die vanuit het bedrijf worden aangemeld, moeten advertentieteksten worden opgesteld en deze moeten in geschikte kranten en tijdschriften worden geplaatst. Op grond van binnenkomende sollicitatiebrieven krijgt een aantal sollicitanten een uitnodiging voor een gesprek. Een klein deel wordt voor een tweede gesprek uitgenodigd. Psychologische tests worden indien nodig door erkende bureaus afgenomen en eventuele referenties worden nagetrokken. Hierna kan een kandidaat een aanbod worden gedaan. Als dat aanbod wordt geaccepteerd, kan de indiensttreding worden geregeld."
Databanken
20
2.6. SAMENVATTING In dit hoofdstuk zagen we de twee eerste stappen die dienen te worden uitgevoerd bij het ontwerpen van een database: de probleemafbakening en de analyse. Om de probleemafbakening, de Universe of Discourse, beter te kunnen beschrijven hebben we een aantal termen gedefiniëerd. Bij de analyse zagen we dat vier vragen centraal staan: welke informatie dient geleverd te worden, welke gegevens hebben we daarvoor nodig, welke gegevens zijn er beschikbaar en hoe staan de beschikbare gegevens in relatie tot elkaar. We hebben dan ook uitgelegd hoe we concreet aan de analyse kunnen beginnen: aan de hand van feiten (interviews), door middel van teksten of met behulp van formulieren, lijsten en bestanden. In de volgende twee hoofdstukken gaan we dieper in op de derde te nemen stap: de ontwerpfase.
Databanken
21
3. TOP-DOWN ONTWERPEN Als de informatieanalyse is afgerond, of in ieder geval voldoende ver is gevorderd, kan het ontwerp beginnen. Top-down ontwerpen houdt in dat men allerlei details nog niet in ogenschouw neemt om zich beter te kunnen concentreren op de hoofdzaak. De belangrijkste entiteittypen en hun onderlinge structuur zijn dus het eerste onderwerp van aandacht. Om die weer te geven voldoet tekst niet goed. Een gegevensmodel van enige omvang is in tekstvorm slecht te overzien. Met name de verbanden die er tussen entiteiten bestaan, zijn in tekst niet op overzichtelijke wijze weer te geven. Een visuele notatie voldoet hiertoe beter; hiervan zijn er zeer veel ontwikkeld. Wij zullen een techniek behandelen die zeer algemeen gehanteerd wordt: het Entity-Relationship diagram(afgekort ERD). Voor dit type diagram bestaan allerlei namen, maar ERD is de meest gebruikelijke.
3.1. ENTITY – RELATIONSHIP DIAGRAM Het ERD is een grafische manier voor het modelleren van gegevens. Welke elementen bevat zo'n ERD en wat is de preciese betekenis ervan? In hoofdzaak zijn er twee elementen: ✔ Entities: dat zijn entiteittypen of tabellen. Ze komen overeen met wat we in hoofdstuk 2 objecten noemden. Ze worden weergegeven door rechthoeken met daarin de naam van het entiteittype en soms een opsomming van de attributen. ✔ Relationships: dat zijn de verbanden tussen de entiteittypen. Ze worden weergegeven door lijnen tussen de verbonden entiteittypen. Er kunnen langs deze lijnen allerlei symbolen staan, en soms tekst. Een entiteit zouden we kunnen definiëren als een onderscheidbaar object waarover informatie moet worden bijgehouden. Entiteiten waarover dezelfde informatie wordt bijgehouden worden entiteiten van hetzelfde type genoemd. Entiteiten kunnen concreet zijn (personen, studenten,...) maar ook abstract (organisatie, ontwikkelingsmethode,...).
3.1.1. Soorten relaties Een ERD geeft geen processen weer en evenmin zit er een volgorde in. Elke lijn tussen twee entiteittypen betreft een verband dat naar weerszijden kan worden gelezen. We verduidelijken een en ander aan de hand van een voorbeeld. Aan de universiteit wordt bijgehouden op welk kotadres de studenten elk jaar verblijven. We hebben in dat geval twee entiteiten: student en kot. Tussen deze entiteiten zouden de volgende relaties kunnen bestaan:
Bovenstaande is een 1/1 verband: het betekent dat elke student in een eigen kot verblijft en dat er geen lege koten zijn. Als we zo'n verband verifiëren aan de realiteit, zien we al gauw dat dergelijk verband niet klopt. Zo zullen er elk jaar opnieuw een aantal koten zijn die niet door een student bewoond worden.
Databanken
22
Dat modelleren we als volgt:
We noemen dit een 0.1/1 verband: het betekent dat elke student in een eigen kot verblijft maar dat er bovendien ook koten kunnen zijn waar geen enkele student verblijft. Ook dit verband klopt niet: er zijn immmers ook studenten die samen op één kot verblijven. Dat modelleren we als volgt:
Het is een 0.n/1 verband: het betekent dat in één kot, geen, één of meerdere studenten kunnen verblijven. Dit lijkt het model te zijn dat de realiteit weerspiegelt. Alhoewel er hier nog steeds één mogelijkheid niet voorzien wordt. Een student kan gedurende het jaar van kot veranderen. Dat kunnen we als volgt modelleren:
In bovenstaande verband verblijft in één kot nog steeds geen, één of meerdere studenten, maar bovendien kan één student gedurende één jaar in verschillende koten verblijven. De vorm van de symbolen spreekt voor zich. De tekst die langs de lijn staat, vormt - als het goed is - samen met de namen van de entiteittypen en met de symbolen voor de aantallen een goed lopende zin. Zo'n zin is eigenlijk een omschrijving van een bepaald type feit. Om het verband ook de andere kant op te begrijpen moet men de passieve vorm van die tekst gebruiken. Zo kunnen we de voorlaatste zin lezen als: nul, één of meer studenten verblijven op één kot. Omgekeerd wordt dat: op één kot wordt door nul, één of meerdere studenten verbleven. Merk op dat het niet uitmaakt of een lijn recht, krom of hoekig is, en dat evenmin de plaats waar een entiteittype is getekend er voor de betekenis toe doet. Op die manier kan je dus met de vorm van de lijnen en de plaats van de rechthoeken spelen om een ERD overzichtelijk te maken Het is wel van groot belang om de tekst langs de lijnen goed te kiezen, zodanig dat het voor de lezer ondubbelzinnig duidelijk is wat het verband betekent en hoe het precies ligt. Merk hiertoe bijvoorbeeld op dat we in het vierde verband de tekst hebben gewijzigd om daar aan te voldoen. Er bestaan zeer veel andere notaties voor ERD. We gebruiken hier de notatie die in de klassediagramma's in UML wordt gebruikt, omdat we in de cursus analyse voornamelijk op UML zullen ingaan. Op die manier dien je je daar niet aan nieuwe symbolen aan te passen. Een ERD heeft echter meestal een iets andere voorstellingswijze. Bovendien is een ERD niet zomaar om te zetten naar een UML klassediagram; ERD dient in de eerste plaats voor datamodelering, UML voor applicatiemodellering. Omdat er zoveel verschillende notaties zijn is het altijd goed om bij een ERD dat je ergens tegenkomt, te beginnen met na te gaan wat de symbolen betekenen, ook al komen ze je vertrouwd voor. En het spreekt dat je dus ook zelf steeds de nodige uitleg geeft bij een ERD dat je tekent. Omschrijf duidelijk de betekenis van symbolen die worden gebruikt. Databanken
23
3.1.2. Levenscyclus Naast het feit dat een entiteit onderscheidbaar is en dat we er informatie over willen bijhouden, heeft een entiteit ook een levenscyclus. Aan een hogeschool heeft een student bijvoorbeeld een levenscyclus: ✔ hij wordt ingeschreven = het begin van het leven van de entiteit "student"; ✔ hij loopt colleges, doet examens,... (de verschillende levensstatussen); ✔ hij verlaat de hogeschool = het einde van het leven van de entiteit "student". Ook docenten hebben een levenscyclus: ze worden aangenomen, verzorgen lessen,... en op een zeker moment verlaten ook zij de hogeschool.
3.2. EEN ERD GAAT ENKEL OVER GEGEVENS In de informatieanalyse dienen meestal verschillende zaken te worden bekeken. Enerzijds dienen de gegevens te worden gemodelleerd waarvoor we ERD kunnen gebruiken. Er dienen meestal ook processen of functies te worden gemodelleerd. In derde instantie dient ook het tijdsafhankelijke gedrag van het systeem te worden vastgelegd. Voor het modelleren van functies of processen werd vroeger nogal eens gebruik gemaakt van DFD's (Data Flow Diagrams). Wie meer wil weten over DFD's raad ik het boek "Gestructureerde analyse" van Edward Yourdon (1989) aan. We komen er in de cursus analyse ook heel kort op terug. Een ERD lijkt op het eerste gezicht wel iets op een DFD. In beide staan immers gegevenselementen: entiteittypen in een ERD, stores in een DFD. Een DFD dient in de eerste plaats echter om processen te modelleren. Een ERD bevat geen processen. Naargelang de aard van het systeem kunnen de drie vermelde modelleringen meer of minder belangrijk zijn. Voor een systeem dat bijvoorbeeld alleen aan opslag van gegevens doet, kan het ERD wellicht volstaan. Voor systemen die voornamelijk bestaan uit functies zonder noemenswaardige gegevens, kan een ERD overbodig zijn. Men kan ERD en DFD tegelijk opstellen, maar ook vanuit het één het ander afleiden. Samen vormen ze een afbeelding van zowel de gegevens- als de processtructuur van datgene wat ze beschrijven. Het probleem met stores in een DFD is dat deze wel gegevens bevatten, maar dat die niet noodzakelijkerwijze goed gestructureerd zijn. Een store kan in termen van gegevensstructuren van alles zijn, bijvoorbeeld: ✔ een entiteit met al zijn attributen (eigenlijk: een verzameling entiteiten van het betreffende type); ✔ een entiteit in een bepaalde levensfase, waardoor bijvoorbeeld sommige attributen kunnen ontbreken; ✔ een view op een entiteit (en een andere store kan een andere view op diezelfde entiteit zijn); ✔ een samenvoeging van meer dan één entiteit (in relationele-database-jargon noemen we dat een join). Voor wie volgens Yourdons aanpak werkt, is het aan te bevelen om stores waar mogelijk overeen te laten komen met entiteittypen uit een genormaliseerd gegevensmodel. Om dit te bereiken is het dus meestal aangewezen éérst het ERD te maken en daarna pas het DFD. Op die manier kan men ervoor zorgen dat de stores in het DFD zo goed als mogelijk samenvallen met de entities in het ERD.
Databanken
24
3.3. ERD VOOR DISCUSSIE EN DOCUMENTATIE Een ERD kan men gebruiken tijdens de analyse, tijdens het ontwerp en tijdens de bouw. Tijdens de analyse kent men nog niet alle attributen en alle betekenisrelaties: die moeten nu juist door middel van die analyse worden ontdekt. Een ERD uit die fase zal dan ook onvolledig zijn. Het doel ervan is om de discussie tussen ontwerpers en materiedeskundigen te ondersteunen. Wij zullen zo'n ERD een ERD voor discussie noemen. In de ontwerp- en de bouwfase is het gegevensmodel al veel gedetailleerder ingevuld. De diagrammen in deze fases zijn met name voor de ontwerpers van nut om de structuur van het model of van de database te kunnen overzien. Ze zijn vooral te gebruiken voor documentatie van het gegevensmodel of de database. De schrijfwijze is in deze fases ook vaak veranderd: dikwijls zet men langs de lijnen geen betekenisrelaties meer maar verwijzende attributen. Zo'n ERD noemen we een ERD voor documentatie. Vaak evolueert een ERD tijdens een project. Eerst wordt het gebruikt voor discussie, later om het ontwerp te documenteren. Na afloop van het project worden er dan twee ERD's bewaard: één om het uiteindelijke gegevensmodel te documenteren, en één dat de database beschrijft indien deze afwijkt van het ontwerp. Wanneer een ERD tijdens de analysefase wordt gebruikt, is het gegevensmodel nog niet op orde. Het diagram zal dienen als hulpmiddel bij het overleg tussen ontwerpers en toekomstige gebruikers. Het is echter echter niet altijd vanzelfsprekend dat gebruikers deze diagrammen gemakkelijk kunnen verstaan. Nochtans is dat wel een vereiste. Automatiseerders hebben immers meestal niet veel vakkennis in andere disciplines. Het is dus van groot belang dat de gebruikers de ERD's kritisch kunnen lezen en fouten aan kunnen duiden. De gebruikers zijn immers de enige experts omtrent de betekenisrelaties die in het ERD staan getekend. Het is dan ook een goed idee om in de analysefase de lijnen tussen de entiteittypen te voorzien van betekenisvolle werkwoorden, compleet met de juiste voorzetsels, die de verbanden een naam geven. Dit is tevens een controle op het diagram: is er geen goed lopende tekst te vinden dan is de betreffende relatie verdacht, of misschien heeft het betreffende entiteittype geen goed gekozen naam. Een andere goede reden om verbanden een naam te geven is dat er soms tussen twee dezelfde entiteittypen meer dan één verband moet worden vastgelegd. Bijvoorbeeld: bij een studie naar de verbetering van de watervoorziening in een ontwikkelingsland wil men ten eerste weten op het grondgebied van welk dorp een waterput ligt: een één-op-nul/één/veel verband. Daarnaast wil men vastleggen uit welke dorpen de gebruikers van de waterputten afkomstig zijn: een veel-op-veel verband. Zie figuur.
Databanken
25
Tijdens de ontwerpfase zal men dan meestal de betekenisvolle werkwoorden langs de lijnen vervangen door de verwijzende attributen. De keuze voor een van beide schrijfwijzen hangt af van het doel dat de opsteller van het diagram heeft. Is het ERD een discussiehulpmiddel voor communicatie met gebruikers tijdens de analyse of tijdens het ontwerp, dan zijn betekenis dragende werkwoorden geschikt. Sterker nog: ze zijn dan meestal onmisbaar. De betekenis van verbanden verhuist in de loop van het ontwerp vaak van de lijnen naar nieuwe entiteittypen. Zo zouden we in ons voorbeeld van studenten en koten in de loop der tijd kunnen vaststellen dat het verblijf op zich als een associatief entiteitstype dient te worden bezien. We krijgen dan iets als:
Om het nieuwe entiteittype de juiste naam en attributen te kunnen geven is de tekst 'verblijft in één jaar op' onontbeerlijk. Later in een project krijgt het ontwerp vastere vormen en gaat men er vaak toe over om de verwijzende attributen (foreign keys) langs de lijnen te zetten: de vermeldingen student# en kot# in bovenstaande figuur. Zo geeft men de structuur van het model weer in het diagram.
3.4. ATTRIBUTEN De informatie die van een entiteit wordt bijgehouden, wordt door middel van attributen weergegeven. Een attribuut heeft géén eigen levenscyclus. Zolang een entiteit bestaat, kan een attribuut een waarde aannemen. Zo heeft een student bijvoorbeeld een geboortejaar, een jaar van aankomst, een studierichting en een woonplaats. Deze attributen zeggen iets over de student die ze beschrijven, ze zijn aan die student gekoppeld. Ze kennen echter geen eigen leven. Als een student de hogeschool verlaat is deze entiteit gestorven: ze is van geen belang meer voor de school; en met de entiteit hebben ook de attributen ervan hun betekenis verloren. Soms is de waarde die een attribuut toekent aan een entiteit onbekend of niet van toepassing. Zo kan het voorkomen dat het telefoonnummer van een student niet bekend is. Een ander voorbeeld is het attribuut eindwerk_onderwerp. Sommige studenten hoeven helemaal geen eindwerk te maken: voor hen is dat attribuut niet van toepassing. Deze waarden kunnen weergegeven worden door wat we een NULL-waarde noemen. Sommige attributen mogen nooit NULL zijn: zodra de entiteit bestaat dienen zij een waarde aan te nemen. Soms kunnen entiteiten van alle andere entiteiten van hetzelfde type onderscheiden worden door één enkel attribuut. Een student kan bijvoorbeeld geïdentificeerd worden aan de hand van zijn studentennummer. Studentennummer is een kandidaatsleutel voor de entiteit student. Soms zijn enkele attributen nodig om een entiteit te identificeren. Stel dat we bijvoorbeeld de entiteit examen hebben met de attributen vak, studentnummer, datum en cijfer. Deze vier attributen identificeren de entiteit examen. Ook zonder het attribuut cijfer wordt de entiteit nog steeds geidentificeerd. Zonder één van de andere echter weten we niet meer zeker over welke entiteit we precies spreken (in de veronderstelling dat een student herexamens kan afleggen). We noemen de attributen vak, studentnummer, datum dan Databanken
26
een kandidaatsleutel. Een minimale verzameling attributen die gezamelijk de entiteiten van een zeker type identificeren, noemt men een kandidaatsleutel. De meeste entiteittypen hebben precies één kandidaatsleutel, maar sommige hebben er twee of meer. Het is gebruikelijk per entiteittvpe één primaire sleutel (primary key) te kiezen; de andere kandidaatsleutels worden alternatieve sleutels genoemd.
3.5. CASE TOOLS Voor het maken van ERD's bestaan er gespecialiseerde pakketten. Met deze pakketten kunnen allerlei typen diagrammen en overzichten gemaakt worden die nuttig zijn bij een informatiseringsproject. Zulke pakketten noemen we CASE-tools wat staat voor Computer Aided Systems Engineering. CASE-tools zijn bijzonder praktisch voor het documenteren van een ontwerp. De eenvoudigste CASE-tools zijn eigenlijk niet meer dan gespecialiseerde tekenpakketten. De meeste E-R diagrammen in dit boek zijn getekend met het pakket .... dat een eenvoudig tekenpakket is voor data flow diagrams en ERD's. In sommige pakketten kan men zelf de schrijfwijze voor een ERD kiezen, andere houden zich aan een vaste schrijfwijze. Er bestaan CASE-tools die meer kunnen dan alleen tekenen. Ze kunnen biivoorbeeld de samenhang bewaken tussen ERD's en andere typen diagrammen uit hetzelfde project, of ze kunnen automatisch een overzicht genereren van de data-dictionary die hoort bij een ERD. Nog grotere pakketten kunnen zelfs een gegevensmodel normaliseren of ze kunnen programmacode voor schermformulieren genereren op basis van een gegevensmodel. Datasebase-leveranciers bieden vaak ook CASE-tools. Er is soms een spanningsveld tussen de meer geavanceerde functies van een CASE-tool en het werk van een ontwerper. In de vroege fasen van een project zijn de diagrammen en de data-dictionary meestal nog niet juist en wil een ontwerper soms toch iets tekenen, al strookt bijvoorbeeld een ERD niet met een ander diagram dat ermee samenhangt. De CASE-tool kan hem dan hinderen met foutmeldingen. Ook legt een CASE-tool beperkingen op ten aanzien van de mogelijke schrijfwijze. Standaardiseren op een bepaalde CASE-tool kan een hulpmiddel zijn om de bruikbaarheid van de documentatie van programmatuur in een organisatie te verbeteren. Zo zullen ontwerpers elkaars diagrammen beter kunnen begrijpen dan wanneer ieder zijn of haar eigen schrijfwijze hanteert. Geen enkele CASE-tool kan echter een Universe of Discourse afbakenen of omzetten in een gegevensmodel. Dit blijft mensenwerk.
3.6. BETEKENISRELATIES IN EEN GEGEVENSMODEL In de werkelijkheid hangen gegevens op diverse manieren met elkaar samen. Een objecttype kan een nauwkeuriger beschrijving van een ander objecttype zijn. Een objecttype kan een deel zijn van een ander objecttype. Een objecttype kan een associatie leggen tussen twee andere objecttypen. Al dit soort verbanden noemt men semantische abstracties, en wanneer men deze verbanden in kaart brengt tussen meer dan twee objecttypen, ontstaan abstractiebomen. We onderscheiden er drie.
Databanken
27
3.6.1. Specialisatie en generalisatie De eerste soort abstractie is de specialisatie/generalisatieboom. Een specialisatie of subtype is een nauwkeuriger beschrijving van een generalisatie of supertype. Tussen de twee bestaat een één-op-nul/één verband. Een specialisatieboom tekent men in een ERD als volgt:
In de specialisatiehiërarchie van de figuur is "dier" de wortel van de hiërarchie. "Zoogdier" en "reptiel" zijn de takken. "Hond", "paard", "krokodil" en "slang" zijn de bladeren. In bovenstaande figuur is "reptiel" een subtype van "dier". Een "krokodil" en een "slang" zijn subtypes van "reptiel". Op iedere plaats waar een "dier" gebruikt wordt, kan altijd ook een willekeurig subtype gebruikt worden. Dat betekent met andere woorden: een "reptiel" is een "dier", een "krokodil" en een "slang" zijn beiden een "reptiel". Hierboven wordt de UML voorstellingswijze gebruikt voor specialisatie. In een ERD kan bovenstaande ook als volgt voorgesteld worden:
Naast extra attributen kan een subtype ook extra relaties hebben met andere entiteiten. Nemen we bijvoorbeeld terug onze student. Een student in zijn laatste jaar heeft ook het attribuut eindwerk_onderwerp. We kunnen dan de entiteit "afstudeerder" met dat attribuut zien als een subtype van de entiteit "student". Elke student die een eindwerk maakt wordt bovendien begeleidt door een docent: we zien dus dat het subtype "afstudeerder" een speciefieke relatie heeft met het entiteittype "docent". We kunnen dat als volgt weergeven:
Databanken
28
Zoals we zien kan enkel de entiteit van het subtype "afstudeerder" de relatie "studeert af bij" hebben met de entiteit "docent". De entiteit van het supertype "student" kan daarnaast echter nog andere relaties hebben met de entiteit "docent", bijvoorbeeld de relatie "krijgt les van" zoals hierboven afgebeeld. Door het invoeren van een subtype kan men dus vermijden dat er te veel attributen vookomen die niet van toepassing zijn (en die dus de waarde NULL hebben). Soms kunnen er bij één supertype verschillende subtypes voorkomen. Zo zouden we bijvoorbeeld de entiteittypes "student" en "docent" kunnen zien als subtypes van het entiteitsupertype "persoon". Tussen die verschillende subtypes kunnen dan verschillende soorten relaties bestaan. Vooreerst kunnen we covering subtypes onderscheiden. Dat zou voor ons voorbeeld betekenen dat elke persoon in ons model òf een "student" is òf een "docent". Wanneer dat niet het geval is, dus wanneer er naast "student" en "docent" ook entiteiten van het type "persoon" kunnen zijn die geen van beide subtypes zij, spreken we van niet-covering subtypes. In een diagram kunnen we dat als volgt aangeven:
Daarnaast kunnen we ook nog een onderscheid maken tussen wederzijds uitsluitende subtypes en overlappende subtypes. Wanneer "student" en "docent" wederzijds uitsluitend zijn, betekent dat dat een "persoon" `niet tegelijkertijd èn ook " student" kan zijn èn "docent". Wanneer het om overlappende subtypes gaat kan het wél zo zijn dat één "persoon" bovendien "student" is èn "docent". We duiden dat aan als volgt:
In de meeste modellen zullen "student" en "docent" niet-covering zijn voor de entiteit "persoon": er bestaan immers ook laboranten, administratieve medewerkers,... Ze zullen meestal ook wederzijds uitsluitend zijn, tenzij voor modellen waarbij bepaalde studenten toch al bepaalde vakken zouden kunnen doceren. Het model net hierboven links zal dus waarschijnlijk het correcte zijn.
Databanken
29
3.6.2. Aggregatie of compositie Een tweede soort abstractie noemen we een structuurboom of een aggregatieboom. In UML (waar we uiteraard over klassen spreken en niet over entiteiten), wordt dan nog onderscheid gemaakt tussen een aggregatie en een compositie. Een aggregatie is daar een speciaal soort associatie die aangeeft dat een of meer klassen 'onderdeel zijn van' een andere klasse. Een compositie is er duidelijker gedefiniëerd: hier mag een onderdeel altijd maar tot één geheel behoren, en de levensduur van een deelobject moet altijd kleiner dan of gelijk zijn aan de levensduur van het gehele object. In een ERD maakt men dat onderscheid niet. Bij een aggregatie is elk element opgebouwd uit de elementen die eronder hangen. Men vindt ze bijvoorbeeld in
gegevensstructuren met producten en onderdelen. Een "auto" is opgebouwd op een manier zoals weergegeven in de figuur hierboven. De kenmerken van "auto" en "koetswerk" zijn zelf weer samengesteld. Dit is een voorbeeld van een structuurboom. Het verband tussen het geheel en de delen kan één-op-één zijn of één-op-veel: een carosserie kan één of meer zetels hebben. Bij een één-op-veel verband in een structuurboom, waarbij het entiteittype aan de veelzijde een deel beschrijft van dat aan de één-zijde, noemt men dit deel vaak een karakteristiek entiteittype. De voorstelling ervan in een ERD gebeurt zoals hieronder aangeduid in het voorbeeld (er kunnen hierover verschillende voosrtellingswijzen gevonden worden in de literatuur). Een fiets bestaat hier uit twee wielen en een frame.
3.6.3. Associatie Een derde soort abstractie noemen we een associatieboom. Een associatie tussen twee entiteittypen is een koppeling tussen die twee entiteittypen die als een apart entiteittype wordt beschouwd. Men noemt dit een associatief entiteittvpe. Een associatief entiteittype is vaak een contract of gebeurtenis: een order, lidmaatschap, bemiddeling, verblijf,... Het is vaak een abstractie, en daardoor moeilijker vindbaar. We zagen hoger reeds het volgende voorbeeld. Databanken
30
De associatie "verblijf" is hier een associatief entiteittype tussen student en kot. Tussen twee entiteittypen zijn dikwijls verschillende associaties denkbaar. Het is daarom van belang een associatief entiteittype een naam te geven die het verband zo goed mogelijk aanduidt. Tussen "student" en "kot" is bijvoorbeeld het vermelde "verblijf" mogelijk. Maar het zou ook "verhuist naar" kunnen zijn, "beoordeelt",...
3.7. ROLLEN VAN ENTITEITTYPEN BINNEN EEN MODEL In een model spelen entiteittypen een bepaalde rol ten opzichte van elkaar. Wat dit betreft kunnen we drie categorieën entiteittypen onderscheiden: kernentiteittypen, karakteristieke entiteittypen en associatieve entiteittypen. De laatste twee werden reeds vermeld in het bovenstaande: een karakteristiek entiteittype staat aan de 'blad'-zijde van een verband in een structuurboom, een associatief entiteittype wordt gebruikt als koppeling tussen twee andere entiteiten. De kernentiteittypen beschrijven meestal gemakkelijk onderscheidbare objecten, zoals studenten en koten in bovenstaande figuur. Kernentiteittypen krijgen bij het modelleren meestal een sleutel van één attribuut. Ze leven vaak lang en komen terecht aan de éénzijde van verbanden. De grens tussen deze drie categorieën is niet altijd even hard. Het gaat immers over betekenisrelaties, en daarover valt altijd te twisten. Vooral als het over minder concrete entiteiten gaat. Stel bijvoorbeeld dat een student een kot dient te beoordelen aan de hand van een lijst met waardepunten. Een "waardepunt" zou in dit model een karakteristiek entiteittype van "beoordeling" kunnen zijn. Maar indien de waardepunten worden toegekend op basis van waargenomen tekortkomingen, zou "waardepunt" ook een associatief entiteittype kunnen zijn tussen "beoordeling" en "waarneming". Bij een model dat nog niet volledig in orde is, kan het zijn dat de keuze nog niet te maken is. Er bestaan synoniemen voor deze rollen van entiteittypen. Voor kernentiteittypen gebruikt men soms het woord lookup tables, omdat ze vaak als 'naslag' dienen. Voor associatieve entiteittypen vindt men wel intersectie-entiteittypen. Een minder gelukkig gekozen naam, want een associatie heeft niets te maken heeft met de intersectie tussen twee verzamelingen. Bij een intersectie tussen twee entiteiten eou men gemeenschappelijke elementen verwachten, wat bij een associatief entiteittype geenszins het geval hoeft te zijn.
3.8. TIJD IN EEN ERD Een ERD is een structuurweergave. Dat betekent dat tijdsaspecten er slecht in tot uitdrukking komen. Men moet bij een ERD duidelijk maken of het een momentopname afbeeldt, dan wel een periode. De voorbeelden die je tot hiertoe zag waren steeds momentopnames. Om dat aan te duiden hebben we tot dusverre de volgende oplossing gebruikt: de werkwoordsvorm waarmee het verband wordt beschreven is in de onvoltooid tegenwoordige tijd (OTT). Indien het over een historie gaat zouden we de voltooid Databanken
31
tegenwoordige tijd (VTT) kunnen gebruiken, zoals in de figuur hieronder.
Het gebruik van OTT of VTT naargelang de situatie werkt wel, maar ze heeft het nadeel dat ze nogal onopvallend is. Ze wordt ook niet algemeen gebruikt. Ze is bovendien ook niet toepasbaar op ERD's zonder tekst langs de lijnen. Het verdient dus aanbeveling om ook in de toelichting bij het diagram de tijdsspanne van geldigheid te vermelden. Niet onderkennen of er een momentopname dan wel een historie moet worden bijgehouden is een veelvoorkomende bron van fouten in gegevensmodellen. Een manier om de tijd wel expliciet in een ERD weer te geven is om één of meer tijdseenheden te beschouwen als entiteittype. Een "verblijf" zou dan bijvoorbeeld een associatie zijn tussen een "student" en een "tijdsspanne". In gevallen waariin veel gegevens per tijdsspanne moeten worden bijgehouden kan deze aanpak erg verhelderend zijn.
3.9. ZELF EEN ERD TEKENEN In de voorgaande paragrafen hebt u met veel verschillende aspecten van ERD's kennis gemaakt, vanuit het idee dat u vaker als lezer dan als opsteller met zo'n diagram te maken zult hebben. Wat nu als u zelf een ERD moet gaan tekenen? Het belangrijkste advies luidt: 'Wees zorgvuldig'. Wat de tekenwijze betreft betekent dit dat u de taal moet kennen van uw eigen diagrammen, en dat u die taal ook aan de lezers duidelijk moet maken. Het mag niet voorkomen dat u in een ERD een symbool gebruikt waarvan u de lezer niet uitlegt wat het betekent. Wat de betekenis van het diagram betreft: wees hierin zo precies mogelijk. Gebruikt u tekst langs de lijnen, dan moet het diagram zich laten lezen als een verzameling beweringen. Stel dat u voor het ontwerp van een onderzoeksdatabase een verband aangeeft tussen een proefveld en een gewas. Zet dan niet langs de lijn "proefveld" heeft "gewas" maar "proefveld" is teeltplaats geweest voor "gewas" of "gewas" is geteeld op "proefveld". Deze laatste formulering helpt u om een eventueel associatief entiteittype tussen "proefveld" en "gewas" te vinden. Soms zal u niet vrij zijn in de keuze van de tekenwijze. Maar welke tekenwijze u ook gebruikt, vijf zaken horen bij elk diagram, als onderschrift of in de tekst: ✔ Het onderwerp van de figuur: datgene waarvan het een afbeelding is. Is het de weerslag van een workshop, of van uw eigen denkproces? Geeft het een ontwerpmodel, of een database-structuur weer? Van welk Universe of Discourse? ✔ Een legende waaruit blijkt wat de symbolen betekenen. ✔ De tijdsspanne waarop het diagram slaat: momentopname of historie? Dit kan soms per entiteittype of per verband verschillen. ✔ Het doel en de status van de figuur: voor wie bestemd? Voor discussie of als documentatie? ✔ Een data-dictionary, waarin van alle entiteittypen, attributen en verbanden zo nodig de betekenis wordt toegelicht.
Databanken
32
3.10. OPGAVEN 1. Bij het opzetten van een lijst met monumentale panden in een gemeente onderscheidt men de objecten "straat" en "pand". Teken een ERD van het Universe of Discourse. 2. Teken een ERD van het model dat we in hoofdstuk 1 uitwerkten (headhunters en organisaties). Hoe zou dit ERD eruitzien in de discussiefase? En hoe in de documentatiefase? 3. Wat is er aan de hand met de sleutels van twee tabellen waartussen een specialisatie relatie bestaat? Neem als voorbeeld de objecten "persoon" en "student" in het hieronder afgebeelde ERD.
4. We keren nogmaals terug naar ons voorbeeld van hoofdstuk 1. Er blijken toch nog problemen op te duiken. Eén van de headhunters komt namelijk bij een organisatie werken die al wordt gevolgd door een andere headhunter. Deze organisatie zou nu dus door twee headhunters kunnen gevolgd worden. Ook zou een headhunter die werkt bij een organisatie die hij tevens volgt, wel eens tot belangenvermenging kunnen leiden. We zouden dat in het gegevensmodel willen zien, maar dat kan echter het dienstverband niet weergeven. Teken in een ERD het gegevensmodel dat nodig is om deze wijzigingen te kunnen doorvoeren. 5. Een sportbond organiseert wedstrijden tussen teams. Bij die wedstrijden speelt het ene team uit en het andere thuis. Stel dat de uitslag van een wedstrijd er niet toe doet, maar dat men wel wil bijhouden welk team uit en welk team thuis speelt. Teken hiervoor het ERD. Als men nu ook de uitslag wil bijhouden. Hoe ziet het ERD er dan uit? Hoe zou je in het ERD feiten van het type "een zekere scheidsrechter leidt de wedstrijd tussen de twee teams" modelleren? 6. In een bibliotheekdatabase werden artikelen en tijdschriften opgenomen. Een artikel kan in verschillende tijdschriften verschijnen. Daarnaast zijn er auteurs en onderwerpen. Iedere auteur kan verschillende artikels schrijven en een artikel kan meer dan één auteur hebben. Een artikel hoort bij slechts één onderwerp. Geef het ERD. 7. Een bedrijf ontwerpt een informatiesysteem ter ondersteuning van de functie "personeel". Van de werknemers worden vastgelegd de naam, de geboortedatum, de kamer op kantoor waar ze werken en de cursussen die ze gevolgd hebben met het jaar waarin de cursus werd gevolgd. De afdeling waartoe een kamer behoort, wordt ook vastgelegd. De cursussen hebben een cursusnummer en -naam. Voor sommige cursussen moet door de werknemers een eindwerk worden gemaakt. De titel van dat eindwerk, de datum van inlevering en het behaalde cijfer worden vastgelegd. Geregeld worden examens afgenomen over de stof van een cursus. De resultaten van de kandidaten en de datum van het examen worden ook opgenomen. Geef het ERD met attributenlijst.
Databanken
33
8. Een reisbureau organiseert rechtstreekse (charter)vluchten van Brussel naar vakantieoorden en terug. Een vlucht vindt plaats op een zeker tijdstip met een vliegtuig van een zeker type en verbindt de plaats van vertrek met de bestemming. Afhankelijk van de afstand en de populariteit van de vakantiebestemming (de hoeveelheid passagiers) wordt gekozen voor een bepaald type vliegtuig. Mocht een vliegtuig uitvallen, dan kan een ander vliegtuig van hetzelfde type de vlucht overnemen. Teken het ERD. 9. In een bepaalde regio heeft een vereniging van makelaars op zich genomen een informatiesysteem voor de verkopen van huizen op te zetten. Een potentiële verkoper meldt zijn huis aan bij een makelaar die als zijn vertegenwoordiger optreedt en die het huis in het systeem opneemt. Een potentiële koper meldt zich eveneens bij een makelaar (mogelijk een andere) en kan een bod op een huis uitbrengen. Als koper en verkoper het eens worden, vindt de verkoop plaats. Neem aan dat "transactie" een entiteittype is. Teken het ERD. 10. Een lijnbusbedrijf heeft een aantal bussen. Elke bus doet steeds éénzelfde route. Op sommige routes wordt meer dan één bus ingezet. Elke route passeert door een aantal dorpen. Aan elk deel van een route zijn één of enkele chauffeurs toegewezen. Een deel van een route gaat door een deel van de dorpen van één route; in sommige gevallen door alle dorpen. In sommige dorpen is er een garage waar de bussen kunnen gestationeerd worden. Elke bus wordt gekenmerkt door de nummerplaat en kan een verschillend aantal passagiers vervoeren, omdat er zowel kleinere als grotere bussen in bedrijf zijn. Elke route wordt gekenmerkt door een routenummer en men beschikt over informatie over het gemiddelde aantal passagiers per dag en per route. Chauffeurs hebben een werknemersnummer, een naam en adres, en soms ook een telefoonnummer. Teken het ERD. 11. Een verhuurmaatschappij van huizen is opgedeeld in een aantal kantoren. Elk kantoor regelt de verhuur van een aantal huizen. Om de verhuur daadwerkelijk op te volgen wordt het huis daarna toegewezen aan een medewerker van het kantoor. De meeste huizen zijn dus toegewezen aan een medewerker van het kantoor, maar op elk moment zijn er ook nog een aantal die wachten op toewijzing aan een medewerker. Teken het ERD. 12. Een makelaarsbureau verkoopt eigendommen: • Het bureau heeft een aantal verkoopkantoren. Elk kantoor heeft een locatie en uniek nummer. • Medewerkers zijn toegewezen aan één kantoor. Ze hebben een uniek nummer en een naam. • Per kantoor is er één manager. De manager is één van de medewerkers van dat kantoor. • Het bureau heeft een lijst van eigendommen te koop. Elk eigendom heeft een uniek nummer en een adres. • Elke eigendom wordt toegewezen aan één enkel kantoor. • Elke eigendom behoort aan één of meerdere eigenaars. Een eigenaar heeft een id en een naam. Wanneer er meerdere eigenaars zijn, houdt men bij voor welk percentage men eigenaar is.
Databanken
34
4. BOTTOM-UP ONTWERPEN Bij het top-down modelleren van gegevens ging het om de verbanden tussen entiteittypen. Pas gaandeweg kwamen alle attributen op hun plaats terecht. Bij bottom-up modelleren werkt men precies andersom. Alle attributen worden bij elkaar gebracht en op een grote hoop gegooid. Vervolgens worden de attributen in groepjes bij elkaar gebracht volgens bepaalde regels. De bekendste techniek hiervoor is normalisatie. Die groepjes attributen die de normalisatie oplevert zijn dan de entiteittypen. In de praktijk weet men aan het begin van een project meestal nog onvoldoende over het Universe of Discourse om bottom-up te kunnen werken. Men zal eerst top-down een voorlopig model maken. Daarna heeft men dan de keuze tussen top-down of bottom-up verder gaan.
4.1. FUNCTIONELE AFHANKELIJKHEID Voordat men een gegevensmodel kan normaliseren, moet men de functionele afhankelijkheden kennen die er bestaan tussen de verschillende attributen. Wat is functionele afhankelijkheid? Het begrip is ontleend aan de wiskunde. Wanneer variabele y een functie is van variabele x - vaak geschreven als y = f(x)) – dan is y functioneel afhankelijk van x. Anders gezegd: bij elke x hoort precies één y. Het omgekeerde hoeft niet waar te zijn: bij één y kunnen geen of verschillende x-en behoren. Een voorbeeld hiervan is de parabool. In de tabel hieronder wordt het begrip functionele afhankelijkheid toegepast in een voorbeeld waarbij je je dadelijk een database kunt voorstellen. Artikelnummer
Prijs
0
10
1
7
2
3
3
7
4
9
5
7
6
10
Zowel in de grafiek als in de tabel zien we dat elke waarde van x slechts éénmaal voorkomt. De stap van wiskunde naar database is klein. Vervang x en y door "artikelnummer" en "prijs", en we verkrijgen bovenstaande tabel, wat een tabel in een database zou kunnen zijn. Ook hier staan geen dubbele artikelnummers in de tabel, maar eenzelfde prijs kan wél verschillende keren voorkomen. Zo zijn er in bovenstaande tabel bijvoorbeeld drie artikels met dezelfde prijs 7 en twee artikels met de prijs 10. We kunnen dan zeggen dat het artikelnummer de prijs determineert. Vaak wordt dat opgeschreven als "artikelnummer" -> "prijs". Er is in een databasetabel normaal esproken één kolom (of een groep kolommen) die de andere kolommen determineert. Dat noemen we de determinant van de tabel. De functionele afhankelijkheden tussen kolommen worden bepaald door het Universe of Databanken
35
Discourse. In de database van hoofdstuk 1 bijvoorbeeld geldt dat elke headhunter één en slechts één telefoonnummer thuis kan hebben, dus "headhunter" -> "telefoonnr". "Telefoonnr" is functioneel afhankelijk van "headhunter". Dit zal in werkelijkheid misschien niet kloppen: een headhunter zou best een tweede telefoonnummer kunnen hebben, bijvoorbeeld een geheim nummer voor privé-gebruik. Maar het headhunterbureau is daarin niet geïnteresseerd, en dus voldoet de gekozen functionele afhankelijkheid. Als er nooit meer dan één headhunter op hetzelfde adres woont is "headhunternaam" tevens afhankelijk van "telefoonnr". We zien dat een functionele afhankelijkheid neerkomt op een veel-op-één verband tussen twee kolommen. Per waarde van het afhankelijke attribuut kunnen één of vele waarden van de determinant voorkomen, per waarde van de determinant kan er slechts één waarde van het afhankelijke attribuut zijn. Het opsporen van functionele afhankelijkheden tussen attributen is niet altijd zo eenvoudig. In ons simpel voorbeeld van hoofdstuk 1 zie je al een aantal moeilijke beslissingen zoals het aantal headhunters per bedrijf en het aantal headhunters per huisnummer. Deze beslissingen zijn nochtans van groot belang voor de structuur van de database. Om deze beslissingen goed en doordacht te kunnen nemen is het vereist te beschikken over een goede kennis van de bedoeling achter het gegevensmodel, het Universe of Discourse dus.
4.2. ROL VAN ATTRIBUTEN Er zijn binnen een gegevensmodel drie mogelijke rollen voor een attribuut, die aanleiding kunnen zijn om het in het model op te nemen: identificeren, beschrijven, verwijzen. We verklaren ons nader. Allereerst kan het attribuut een entiteit vertegenwoordigen: identificeren. Nemen we het voorbeeld van de student in het vorige hoofdstuk. Zodra je als student wordt ingeschreven zou men je bijvoorbeeld een uniek nummer kunnen geven. Elke waarde van het attribuut "studentennummer" vertegenwoordigt dan het feit dat 'de student met dat nummer bestaat'. Zo'n attribuut kan determinant zijn van andere attributen. Het is dan ook voor die attributen identificerend. Het "studentennummer" identificeert bijvoorbeeld de geboortedatum en het kotadres van de student, omdat elke student slechts één keer geboren is en slechts op één kot tegelijkertijd verblijft. In de tweede plaats kan een attribuut een bestaande entiteit nader beschrijven. Het zegt dan iets als: 'de entiteit waarbij ik hoor heeft kenmerk X'. De geboortedatum van een student is zo'n beschrijvend attribuut. 'Op 4 april 1986 geboren' is geen feit op zich; het zegt pas iets als men weet dat het Marjolein was die op die datum werd geboren. Een attribuut als geboortedatum is niet identificerend maar louter beschrijvend. Men neemt het op omdat men de leeftijd van een student van belang vindt. Soms is het uiteraard mogelijk dat een attribuut beide rollen verenigt; we zouden bij onze studenten misschien kunnen opteren om de naam van de student te gebruiken als identificerend attribuut. En uiteraard is de naam ook beschrijvend. De derde mogelijke rol is dat het attribuut een verwijzing aanlegt tussen twee entiteittypen: verwijzen. Dat is bijvoorbeeld het geval met student# in het voorbeeld onder 3.6.3.: het verwijst van de entiteit "student" naar de entiteit "verblijf". De entiteit "verblijf" heeft dat verwijzend attribuut nodig om te weten over welke student het gaat.
Databanken
36
4.3. SLEUTELS Bij databases wordt over verschillende soorten sleutels gesproken: primaire sleutels, vreemde sleutels, kandidaatsleutels en alternerende sleutels. We overlopen de betekenis en de rol die ze spelen.
4.3.1. Identificerende sleutel Idealiter correspondeert elk object uit de werkelijkheid met één entiteit in het informatiemodel en deze correspondeert dan op zijn beurt met één rij in een tabel van de database. Bij het modelleren van gegevens tracht men meestal eveneens elke entiteit uniek te definiëren. De combinatie van attributen die een entiteit uniek definieert, noemt men de identificerende sleutel van deze entiteit, of kortweg de sleutel. Het voorvoegsel identificerend duidt erop dat men de entiteit herkent aan de waarde van de sleutel. In het Engels spreekt men van primary key. Om mensen in geautomatiseerde bestanden uniek te identificeren heeft men kunstmatige identificerende sleutels ingevoerd, bijvoorbeeld RIZIV-nummers, nummers van paspoorten en identiteitsbewijzen, studentennummers,... De naam is immers niet voldoende om mensen uniek te identificeren. In het algemeen krijgen kernentiteittypen (zie 3.7.) bij het modelleren een sleutel van één attribuut, vaak met dezelfde naam als de zaken die ze identificeren, zoals bijvoorbeeld "studentnummer" voor de entiteit "student". Bij associatieve en karakteristieke entiteittypen daarentegen is de sleutel doorgaans samengesteld uit meer dan één attribuut. In een klein aantal gevallen kan het goed zijn om voor zo'n entiteittype een nieuw attribuut in het leven te roepen, dat opnieuw naar de de naam van het entiteittype verwijst. We doen dat enkel en alleen wanneer er een goede reden voor is, bijvoorbeeld wanneer het nieuwe attribuut een betekenis heeft voor de gebruikers. Zo zou bijvoorbeeld een reservatie van een kamer in een hotel wellicht volstaan met een combinatie van klantnummer, kamernummer en datum als sleutel. Omdat hotelmensen echter gewoon zijn om over een reservatie te spreken, en het wellicht vaak gebruiken om zaken op te zoeken, kan het nuttig zijn om hier een "reservatienummer" in het leven te roepen. Vaak wordt een vaste schrijfwijze gebruikt om identificerende sleutels aan te duiden. Ze worden onderstreept. Bestaat een identificerende sleutel uit meer dan één attribuut, dan wordt er een streep gezet onder elk van die attributen. In een goed ontworpen gegevensmodel zijn alle niet tot de sleutel behorende attributen ('niet-sleutelattributen') van een entiteittype functioneel afhankelijk van de identificerende sleutel. De identificerende sleutel is dus een determinant van de tabel. Normalisatie, dat verderop wordt behandeld, is een van de manieren om dit te bereiken.
4.3.2. Kandidaatsleutel Zijn er meer determinanten in een zelfde tabel, bijvoorbeeld zowel een unieke naam als een unieke code, dan zijn die alle kandidaat om tot sleutel te worden gekozen. Wanneer er dus meer dan één identificerende combinatie van attributen te vinden is noemen we ze allen kandidaatsleutels (candidate keys). Voor elke entiteit kiezen we dan één identificerende primaire sleutel (primary key); de andere kandidaatsleutels noemen we alternatieve sleutels (alternate keys). Databanken
37
Neem het voorbeeld van de chimpansees die verblijven in de apenkooi in de zoo van Antwerpen. Alle chimpansees hebben een eigen unieke naam gekregen, maar daarnaast ook een identificerend nummer: Aapnaam
Aap#
Moeder#
Geslacht
Geboortedatum
Spin
SP
-
V
23/10/1987
Jimmy
JI
-
V
16/4/1984
Jonas
JO
JI
M
22/9/1994
Sophie
SO
SP
V
6/4/1996
Welke kandidaatsleutels hebben we hier? "Aapnaam" en "aap#" zijn allebei determinant van alle andere attributen en dus kandidaatsleutel. "Moeder#" en "geboortedatum" zijn weliswaar uniek in de tabel, maar dat is toeval. Er zouden best meer kinderen van Jimmy kunnen zijn, en evenzeer zouden er best twee apen op dezelfde datum geboren kunnen zijn. Het geslacht is niet uniek in de tabel - er zijn drie vrouwtjes - en kan dus geen sleutel worden. We moeten dus "aapnaam" of "aap#" kiezen als identificerende sleutel. Het is een goede gewoonte om in diagrammen ook alternatieve sleutels een apart lettertype mee te geven (bijvoorbeeld vetgedrukt). Indien de duidelijkheid van je diagram dat tenminste toestaat. Wanneer bijvoorbeeld ook de alternatieve sleutels een combinatie zijn van verschillende attributen kan het zijn dat het diagram zo overladen geraakt dat het wellicht beter is van deze notatie af e zien.
4.3.3. Vreemde sleutel Primaire sleutels zijn belangrijk omdat ze in het gegevensmodel het middel vormen om associaties te implementeren. Dat betekent dat de associaties tussen de entiteiten door middel van primaire sleutels worden gerealiseerd. Een vreemde sleutel nu is een verwijzend attribuut, dat in een andere tabel als kandidaat- of identificerend sleutelattribuut aannwezig is. Het woord vreemde sleutel is een ongelukkige vertaling van het Engelse foreign key. Beter was iets als 'sleutel elders' geweest. Stel dat we de entiteiten "docent" en "vak" hebben. In de entiteit "vak" wordt aangeduid door welke docent het wordt gegeven door de primaire sleutel "docentid" van de entiteit "docent" er in op te nemen. Het attribuut "docentid" in de entiteit "vak" is op dat moment een vreemde sleutel. Vreemde sleutels worden in het diagram vaak cursief afgedrukt.
4.3.4. Code of naam Vaak zie je dat voor de identificatie van entiteiten een code of id wordt ingevoerd. Zo zouden we in het voorbeeld van hoofdstuk 1 vlug geneigd zijn om een headhuntercode in te voeren als primaire sleutel. Er kleven aan het gebruik van betekenisloze codes echter ook een aantal nadelen, die zich openbaren bij het gebruik van de database. Mensen maken bij het invoeren van codes eerder fouten dan wanneer het om namen gaat. In winkels waar verkoopsters artikelcodes moeten intoetsen, kost het een nieuwe verkoopster vaak enkele weken om de codes van de meest verkochte artikelen uit haar hoofd te leren. In het algemeen kunnen we dus zeggen dat het beter is géén code in te voeren tenzij het echt nodig is (bijvoorbeeld namen kunnen meermaals voorkomen voor andere personen) Databanken
38
of indien er gemotiveerde redenen voor zijn (bijvoorbeeld het reservatienummer in ons voorbeeld van hotelreservaties). De historische reden ervoor - het besparen van geheugenruimte in de computer - is inmiddels ook grotendeels achterhaald.
4.3.5. Sleutels en semantiek De betekenisrelaties tussen entiteittypen zijn terug te vinden in hun sleutelattributen: In een specialisatieboom hebben supertype en subtype meestal dezelfde sleutel, zoals bij "student" en "afstudeerder": beiden hebben hun "studentennummer" als identificerend attribuut. In een structuurboom met een één-op-veel verband tussen moeder-entiteittype en kinderen is de sleutel van de moeder een deel van de sleutel van het kind (het karakteristieke entiteittype). Een karakteristiek entiteittype krijgt meestal een samengestelde sleutel met een attribuut meer dan het moeder-entiteittype. Zo zou bijvoorbeeld het wiel uit 3.6.2. geidentificeerd kunnen worden door het identificerend attribuut van "fiets" samen met een attribuut van "wiel". Bij een associatief entiteittype is de samenvoeging van de sleutels van de samenstellende entiteittypen een kandidaat-sleutel. Niet zelden wordt er een nieuwe sleutel bedacht voor zo'n associatief entiteittype: bijvoorbeeld ons reservatienummer voor een associatie tussen een klant en een hotelkamer in een hoteladministratie. Maar strikt noodzakelijk is zo'n nieuw coderingsstelsel niet, en het kan soms zelfs meer onduidelijkheid scheppen. Het nieuwe attribuut draagt immers dezelfde betekenis als de kandidaatsleutel waarvoor het een alternatief is.
4.4. REDUNDANTIE Worden er in een gegevensmodel attributen opgenomen bij een entiteittype waar ze intuïtief niet thuishoren, dan blijkt dat uit het feit dat deze attributen niet functioneel afhankelijk zijn van de sleutel van het entiteittype. Soms is zelfs niet uit te maken wat de sleutel zou moeten zijn. Het gevolg is dat in een database die gemaakt is op grond van een dergelijk gegevensmodel feiten meermaals zullen moeten worden opgeslagen. De eerste probeersels bij het vinden van een oplossing voor het headhunter probleem in de database van hoofdstuk 1, zijn daar een goed voorbeeld van. Immers, sommige attributen in deze eerste tabellen horen bij een organisatie (branche, omzet), andere bij een headhunter (telefoonnummer). Je ziet dan ook dat de gegevens van één headhunter of organisatie meermaals worden opgeslagen, met als gevolg dat het telefoonnummer van een headhunter meermaals dient te worden gewijzigd als dat telefoonnummer verandert. Het verschillende keren opslaan van hetzelfde feit heet redundantie ('overtolligheid', 'overbodigheid'). Redundante opslag van gegevens dient, behoudens erg goede redenen, vermeden te worden. Bij wijziging van gegevens is er immers altijd de noodzaak het redundante feit op meer dan één plaats tegelijk te wijzigen, gepaard aan het risico dat dat niet gebeurt en dat de database tegenstrijdige gegevens gaat bevatten. Redundantie kan men verwijderen door een gegevensmodel te normaliseren. Ook in een genormaliseerd gegevensmodel worden bepaalde attribuutwaarden meermaals opgeslagen, zoals "headhunterid" in ons headhunter voorbeeld. Dat is echter geen redundantie omdat niet hetzelfde feit herhaald opgeslagen wordt. Bij wijziging van een telefoonnummer bijvoorbeeld, hoeft altijd slechts in één rij iets veranderd te worden. Databanken
39
"Headhunterid" speelt in de tabel "headhunter" een andere rol dan in de tabel "organisatie". In het eerste identificeert het de headhunter, in het tweede verwijst het als vreemde sleutel naar een headhunter die de organisatie volgt. Als er meer dan één kandidaatsleutel in een entiteittype bestaat, is dat in feite ook een vorm van redundantie. Het is een bijkomend argument om geen nieuwe sleutelattributen te verzinnen voor associatieve entiteittypen.
4.5. INTEGRITEIT VAN GEGEVENS Een gegevensmodel geeft de structuur weer van de gegevens die nodig zullen zijn in een database. Alles wat er in die database kan bestaan, wordt in het model weergegeven. Het omgekeerde is ook waar: wat niet in het model staat, kan ook niet in de database worden bewaard. In onze database uit hoofdstuk 1 bijvoorbeeld kan niet worden weergegeven bij welke organisatie een headhunter zelf werkt. Het is mogelijk om in een gegevensmodel nog veel gedetailleerder aan te geven wat er wel en niet kan. Dit noemt men het aangeven van geldigheidsregels voor de gegevens. Als men dit doet kan men later in de database de kwaliteit van de gegevens des te beter bewaken. Men spreekt dan van de integriteit van de database; een database met nietintegere gegevens wordt corrupt genoemd. De meest voorkomende reden waarom databases corrupt raken, is dat de gegevens niet worden aangepast aan een veranderende werkelijkheid. Adressenbestanden bijvoorbeeld die niet worden up-to-date gehouden, verouderen snel. Maar het is ook mogelijk dat er strijdigheden binnen in de database ontstaan, bijvoorbeeld omdat eenzelfde feit op twee plaatsen verschillend wordt weergegeven, of omdat er gegevens in staan die vanuit het model gezien al niet kunnen kloppen. Zou bijvoorbeeld in een database een geboortedatum zitten die in de toekomst ligt, dan kan dat nooit juist zijn. Een geldigheidsregel wordt in het Engels integrity constraint (van constrain, inperken) genoemd. Het is een regel die de waarden van een bepaald veld in een databank inperkt. Regels die dienen om de geldigheid van data controleren en meer structuur brengen in de database noemt men entiteitsregels (entity integrity). De verschillende sleutels die we zagen leggen ook bepaalde integriteitsvoorwaarden op. Elke primaire sleutel moet bijvoorbeeld een unieke waarde hebben. En aangezien vreemde sleutels relaties bij elkaar houden, is het van groot belang dat ze inderdaad overeenkomen met de waarde van de sleutel waar de vreemde sleutel naar verwijst. Deze regels noemen we referentieregels (referential integrity).
4.6. VAN ERD NAAR TABEL Elk entiteittype in een ERD komt in de databank in een tabel terecht. De attributen worden de kolommen en de individuele entiteiten worden de rijen van de tabel. Stel dat we bijvoorbeeld de entiteit "student" hebben met de attributen naam, adres, nummer en geboortedatum. Het nummer van de student is de primary key. In een ERD wordt dat zoals links afgebeeld. Als tabel kunnen we deze zelfde entiteit schrijven als: student (nummer, naam, adres, geboortedatum)
Databanken
40
Hierin is student de tabelnaam en tussen haakjes zien we de attributen (de kolommen van de tabel). De primary key wordt onderstreept. De techniek om van ERD naar de tabelvorm in de database over te gaan, wordt ook wel mapping genoemd.
4.6.1. Eén op één relaties mappen In dit geval is het dikwijls mogelijk om één entiteit op te nemen in de andere entieit zodat de twee entiteiten naar één tabel gemapt worden. Of in het ERD naar één hoofdentiteit. Welke van de twee we dan opnemen in de andere hangt af van de relatieve belangrijkheid van beide entiteiten (meer attributen, betere sleutel, duidelijker naam,....). Het resultaat is dan één entiteit waarin we alle attributen terugvinden van de twee vroegere entiteiten. De sleutel van de tweede entiteit wordt een alternatieve sleutel in de hoofdentiteit. Indien bepaalde attributen in beide entiteiten voorkomen wordt het dubbel uiteraard verwijderd. In sommige gevallen is het beter de twee entiteiten niet samen te brengen. Bijvoorbeeld wanneer de twee entieiten ook in de echte wereld twee duidelijk gescheiden dingen vertegenwoodigen. Of wanneer de entiteiten in heel andere relaties met derde entiteiten participeren. Of als laatste mogelijkheid vanuit databasetechnisch perspectief: wanneer snelheid primeert of wanneer het updaten van rijen volgens een volledig andere regelmaat plaatsvindt. Wanneer de twee entiteiten apart worden gehouden, dan moeten we de relatie tussen hen representeren door middel van een vreemde sleutel. De identificerende sleutel van de eerste entiteit wordt gebruikt als vreemde sleutel in de tweede entiteit. Een voorbeeld. We hebben twee entiteittypes: medewerker, met de attributen naam en rrnummer, en contract met de attributen contractnummer, start, einde, salaris en functie. Elke medewerker moet één contract hebben want anders is het geen medewerker. Elke contract moet één medewerker hebben want anders is er geen contract nodig. We hebben dus een één op één relatie tussen de twee entiteiten. We hebben dan de drie volgende mappingsmogelijkheden: medewerker (rrnummer, naam, contractnummer, start, einde, functie, salaris) medewerker (rrnummer, naam, contractnummer) contract (contractnummer, start, einde, functie, salaris) medewerker (rrnummer, naam) contract (contractnummer, start, einde, functie, salaris, rrnummer)
4.6.2. Eén op één/nul relaties mappen In dergelijk gevallen is het beter de twee entiteiten apart te houden aangezien we een hele reeks NULL-waarden zouden creëren indien we ze zouden samenvoegen. Stel dat we in bovenvermeld voorbeeld wel medewerkers kunnen hebben zonder contract. Er zijn dan twee mappingsmogelijkgheden, namelijk de twee laatste. Het is dan best te opteren voor de derde mapping zodat enkel en alleen een rij in de tabel contract wordt opgenomen wanneer er inderdaad een contract bestaat voor de desbetreffende medewerker. Indien we zouden mappen volgens de tweede mogelijkheid, zouden we een hele reeks rijen in de tabel medewerker hebben waar de vreemde sleutel contractnummer NULL is. medewerker (rrnummer, naam) contract (contractnummer, start, einde, functie, salaris, rrnummer) Databanken
41
4.6.3. Eén/nul op één/nul relaties mappen In dergelijke gevallen zijn we verplicht om de twee entiteiten apart te houden aangezien we anders nooit een identificerende sleutel zouden kunnen kiezen. Neem het voorbeeld van medewerkers die een auto (met attributen nummerplaat, kleur, type,...) gebruiken. Elke medewerker kan maximaal één auto gebruiken, en elke auto kan geleend worden door maximaal één medewerker. Wat zou er gebeuren indien we de twee entiteiten zouden samenvoegen? Bekijk daartoe onderstaande tabel: medewerkerauto (rrnummer, naam, nummerplaat, kleur, type)
Indien we rrnummer zouden gebruiken als primary key, dan zouden alle auto's die niet werden uitgeleend geen sleutel meer hebben. Indien we nummerplaat als primary key zouden gebruiken dan zou elke medewerker die geen auto gebruikt, geen sleutel meer hebben. Een samengestelde sleutel zou ook niet werken want er zouden NULL-waarden in kunnen voorkomen. Om de relatie te mappen voegen we dus aan één van de entiteiten de sleutel van de andere entiteit toe als vreemde sleutel.
4.6.4. Eén op veel relaties mappen Om dergelijk relatie te mappen wordt de primary key van de één-zijde toegevoegd als vreemde sleutel aan de veel-zijde. Neem bijvoorbeeld studenten die lessen volgen. Eén les wordt door meerdere studenten gevolgd. Wanneer student de attributen rrnummer en naam heeft, en les de attributen lesnummer, naam en belangrijkheid, dan mappen we dat als: les (lesnummer, naam, belangrijkheid) student (rrnummer, naam, lesnummer)
Wanneer een entiteittype in meer dan één dergelijke relatie voorkomt, wordt voor elk van die relaties een extra vreemde sleutel opgenomen.
4.6.5. Veel op veel relaties mappen Dergelijk relaties worden gemapt door een extra tabel op te nemen die de identificerende sleutels van beide tabellen opneemt. Deze twee sleutels samen zijn de primaire sleutel van deze "hulp"tabel. Indien studenten bijvoorbeeld vele lessen volgen, krijgen we: les (lesnummer, naam, belangrijkheid) volg (lesnummer, rrnummer) student (rrnummer, naam)
4.6.6. Parallelle relaties mappen Dergelijke relaties komen voor wanneer tussen twee entiteiten meer dan één relatie bestaat. In het voorbeeld van hoofdstuk 1 hadden we bijvoorbeeld de headhunter die enerzijds een organisatie kon volgen, maar anderzijds ook de mogelijkheid dat hij werkte voor de organisatie. De mapping gebeurt voor beide relaties op de manier zoals hierboven uitgelegd. Het resultaat is dat meer dan één vreemde sleutel wordt gecreëerd. In ons voorbeeld zouden we bij headhunter een vreemde sleutel organisatie opnemen om de relatie "werkt bij" te mappen, en bij organisatie een vreemde sleutel headhunter om de relatie "volgt" te mappen (ervan uitgaande dat elke organisatie door één headhunter wordt gevolgd). Wanneer de twee sleutels die we toevoegen in dezelfde tabel zouden komen dan geven Databanken
42
we ze de naam van de rol die ze er spelen.
4.6.7. Recursieve relaties mappen Nemen we bijvoorbeeld het klassieke voorbeeld waarbij één medewerker verschillende andere medewerkers kan leiden (de leider noemen we de manager). Elke medewerker heeft een rrnummer waardoor hij wordt geidentificeeerd. Om de relatie "leiden" te mappen voegen we een managernummer toe als vreemde sleutel: dit is het rrnummer van de manager. Om duidelijk te maken wat het is en om dubbele attribuutnamen te vermijden geven we het uiteraard een andere naam. Deze naam dient zo precies mogelijk aan te geven waar het voor gebruikt wordt. We krijgen dan de tabel: medewerker (rrnummer, managernummer, naam)
De vreemde sleutel die we toevoegen is dus de identificerende sleutel die we een andere naam geven. Dus ook in recursieve relaties blijven alle hogervermelde regels geldig, maar de naam van de vreemde sleutels dient te worden aangepast omdat we anders attributen met dezelfde naam zouden hebben.
4.6.8. Specialisaties mappen Er zijn drie manieren om een specialisatie te mappen. Welke de beste is hangt af van de omstandigheden. Enkel de eerste methode is een echte afspiegeling van de specialisatiegeneralisatie relatie. Indien één van de twee andere de voorkeur wegdraagt, betekent het ofwel dat de relatie beter niet als specialisatie was weergegeven in het ERD, ofwel dat er heel goede redenen moeten zijn om af te wijken van de normale mapping. Neem het voorbeeld van medewerkers opnieuw. Stel dat we in het ERD de superklasse medewerker terugvinden met de subklasses manager, secretaris en verkoper. Wanneer het om echte subklasses gaat moeten we nieuwe attributen terugvinden bij die klasses: medewerker (rrnummer, naam) manager (rrnummer, bonus) secretaris (rrnummer, steno) verkoper (rrnummer, verkoopsgebied, auto)
Om redenenen van efficiëntie of performantie zouden we kunnen opteren voor de volgende mapping: manager (rrnummer, naam, bonus) secretaris (rrnummer, naam, steno) verkoper (rrnummer, naam, verkoopsgebied, auto)
Op die manier worden vele joins vermeden indien de hoofdinformatie bij medewerker (naam) vaak nodig is wanneer we ook de bijkomende informatie uit één subklasse nodig hebben, en we tegelijkertijd niet vaak nood hebben aan medewerkers uit verschilende subklasses. Deze mapping kan bovendien enkel gebruikt worden op voorwaarde dat er geen relaties zijn tussen derde entiteiten en de superklasse, en wanneer er geen overlappende subklasses zijn. Wanneer er wel overlapping bestaat tussen de verschillende subklasses dient het vermeden te worden omdat dan redundantie ontstaat. Indien we joins willen vermijden wanneer we ook vaak informatie over medewerkers van verschillende subklasses nodig hebben, kunnen we de volgende mapping toepassen: Databanken
43
manager (rrnummer, naam, bonus, steno, verkoopsgebied, auto)
Het is zeker géén goed idee indien er relaties zijn tussen subklasses en derde klasses. In het andere geval heeft het enkel als nadeel dat er veel lege velden bewaard worden. Indien er tenslotte geen nieuwe attributen zouden opduiken bij de subklasse, kunnen we het mappen zoals een recursieve relatie. In de feiten betekent dat dan dat het inderdaad over een recursieve relatie gaat en niet over een subklasse. Bijvoorbeeld: medewerker (rrnummer, managernummer, secretarisnummer, naam)
4.7. NORMALISEREN Normaliseren is een techniek om een gegevensmodel te ontdoen van redundantie. De basis voor het normaliseren vormen de functionele afhankelijkheden die tussen de attributen bestaan. Het verwijderen van redundantie voorkomt fouten bij invoer, aanpassing en verwijdering van gegevens, aangezien elk gegeven slechts op één plaats wordt bijgehouden. Soms zal blijken dat fouten in het opgestelde ERD tot gevolg hebben dat het handhaven van de functionele afhankelijkheden moeilijk wordt. Door normalisatie toe te passen worden deze fouten ontdekt en weggewerkt. Het oorspronkelijke ERD wordt aangepast De klassieke normalisatieprocedure verloopt via een aantal stappen, de zogenoemde normaalvormen. Men onderscheidt de 0de, 1ste, 2de en 3de normaalvorm. Des te hoger de normaalvorm, des te meer redundantie er verwijderd werd uit het model. Een hogere normaalvorm houdt in dat het model ook aan de lagere normaalvormen voldoet. Er bestaan nog hogere normaalvormen (4de, 5de en 6de) maar deze worden minder gebruikt. We gaan er in de cursus niet op in. Wel bekijken we nog de Boyce-Codd normaalvorm die met een speciale vorm van redundantie rekening houdt.
4.7.1. Nulde normaalvorm (0NV) Neem een gegevensmodel dat is voortgekomen uit een informatieanalyse. Bepaal de functionele afhankelijkheden. Definieer voor elk entiteittype sleutels: dit zijn de determinanten van de functionele afhankelijkheidsrelaties. Kies de sleutels zo dat alle attributen in een entiteittype ervan afhangen. Het gegevensmodel mag nog erg ruw zijn: er mogen bijvoorbeeld attributen of groepen attributen zijn die meermaals voorkomen. Men noemt dat dan herhaalde groepen attributen. Wel moeten alle attributen bekend zijn. Wat zijn dergelijke herhaalde groepen attributen? Bekijk daartoe de onderstaande tabel van headhunters:
hhnaam
telefoonnr
organisatie orgnaam branche vertegenwoordiger Hans Koppens 3235674389 Verre Reizen toerisme ALLTOURIST Fly Away toerisme ALLTOURIST Tafel dek je voeding VCN Lieve Desitter 32472345612 Devos Dankers voeding VCN Graankorrel voeding VCN Tafel dek je voeding VCN Louis Dewaele 3214567890 Bioconstruct bouw De Bouw
omzet 300 740 760 1780 540 760 460
In wezen is deze tabel niets anders dan de eerste tabel die we ook al in hoofdstuk 1 tegenkwamen, enkel de presentatie is anders. En we hebben er een veld vertegenwoordiger aan toegevoegd. Als we de tabel bekijken zoals ze hier staat, zien we Databanken
44
dat bij elke headhunter er één of meerdere rijen (orgnaam, branche, vertegenwoordiger, omzet) kunnen zijn. We noemen dat de herhaalde rijen. De sleutel van bovenstaande tabel is headhunternaam. De functionele afhankelijkheden zijn gekend namelijk: ➔ hhnaam → telefoonnr; ➔ orgnaam → branche, omzet; ➔ branche → vertegenwoordiger. Een model zoals dit bevindt zich in nulde normaalvorm (wordt soms ook genoemd: nietgenormaliseerd). Het wordt ook als volgt voorgesteld: headhunter (hhnaam, telefoonnr, (orgnaam, branche, vertegenwoordiger, omzet)) hhnaam → telefoonnr orgnaam → branche, omzet branche → vertegenwoordiger
Een model waarin herhaalde groepen attributen voorkomen kent een aantal anomalieën. Neem bijvoorbeeld het hierboven getekende model. Als we dit model in één database tabel willen stoppen, moeten we de gegevens (headhunter, telefoonnummer) voor elke organisatie die door dezelfde headhunbter gevolgd wordt, herhalen. Elke headhunter komt dan meerdere malen voor, namelijk voor elke organisatie die hij volgt. ✔ Onze identificerende sleutel moet in dit geval zowel headhunterid als organisatieid bevatten. Dat heeft als gevolg dat we géén headhunter kunnen invoeren zonder organisatie en omgekeerd, aangezien een primary key geen NULL-waarden mag bevatten. Dat wordt de insertion anomaly genoemd. ✔ Als de naam van één headhunter dient gewijzigd te worden, dient dat te gebeuren voor elke organisatie die hij volgt. Dat wordt de update anomaly genoemd. ✔ In het geval ons contract voor een bepaalde organisatie zou aflopen en we het record ervan verwijderen, zou het gevolg ervan kunnen zijn dat we ongewild ook de gegevens over een headhunter verliezen. Dat noemen we de deletion annomaly.
4.7.2. Eerste normaalvorm (1NV) In de eerste normaalvorm worden de herhaalde groepen verwijderd. Een tabel is in eerste normaalvorm als en alléén als het geen herhaalde attributen of groepen van attributen bevat. Om dat te bereiken gaan we als volgt te werk. 1. Bepaal de sleutel van klasse A (met herhaalde groepen). 2. Breng elke herhaalde groep onder in een aparte klasse B, samen met deze sleutel (van klasse A). De herhaalde groep verdwijnt uiteraard uit klasse A. 3. De sleutel van de nieuwe klasse B is de sleutel van A + één of meer attributen van de herhaalde groep. Welke deze attributen moeten zijn wordt bepaald door de fundtionele afhankelijkheden: alle niet-sleutelattributen moeten afhankelijk zijn van de gekozen sleutel. Het resultaat is een model dat zich in eerste normaalvorm bevindt. Afhankelijk van het gekozen voorbeeld zullen één of meerdere van de hierboven vermelde anomalieën verdwenen zijn. Als we deze normalisatiestap toepassen voor de tabel onder 4.7.1, doen we het volgende: 1. De sleutel van de klasse headhunter is hhnaam. 2. De herhaalde groep (orgnaam,branche,vertegenwoordiger,omzet) gaat naar de klasse hhorg, samen met hhnaam. De herhaalde groep verdwijnt uit headhunter. 3. De sleutel van de klasse hhorg wordt hhnaam, orgnaam. We krijgen dan het volgende model: headhunter (hhnaam, telefoonnummer) hhorg (hhnaam, orgnaam, branche, vertegenwoordiger, omzet)
Databanken
45
4.7.3. Tweede normaalvorm (2NV) Een model is in tweede normaalvorm als en alleen als: ✔ het zich in eerste normaalvorm bevindt; ✔ als elke niet-sleutelattribuut functioneel afhankelijk is van de volledige sleutel. Hoe gaan we te werk om dat te bereiken: 1. Bepaal de sleutel van klasse A (in eerste normaalvorm). 2. Bepaal alle functionele afhankelijkheden. 3. Zoek de niet-sleutel attributen die functioneel afhankelijk zijn van slechts een deel van deze sleutel (van klasse A). Dergelijke attributen kunnen enkel bestaan als de sleutel van A een samengestelde sleutel is. 4. Breng deze niet-sleutel attributen onder in een nieuwe klasse B samen met het deel van de sleutel waarvan ze afhankelijk zijn. Dat deel van de sleutel van klasse A wordt de nieuwe sleutel van klasse B. De niet-sleutel attributen die je naar klasse B brengt verdwijnen uiteraard uit klasse A. Het resultaat is een gegevensmodel waarbij alle niet-sleutelattributen afhankelijk zijn van de gehele sleutel. Wanneer bepaalde attributen functioneel afhankelijk zijn van een deel van de sleutel, dan wordt dat ook benoemd als zijnde een partial key dependency (PKD). Passen we dat toe voor ons voorbeeld uit paragraaf 4.7.2. Ons model zag er in eerste normaalvorm als volgt uit: headhunter (hhnaam, telefoonnr) hhorg (hhnaam, orgnaam, branche, vertegenwoordiger, omzet)
De functionele afhankelijkheden zijn de volgende: hhnaam → telefoonnr orgnaam → branche, omzet branche → vertegenwoordiger
De klasse headhunter is al in 2NV want er is een enkelvoudige sleutel. De klasse hhorg heeft wel een samengestelde sleutel. En er zijn inderdaad niet-sleutel attributen die afhankelijk zijn van slecht een deel van de sleutel: branche, vertegenwoordiger, omzet zijn functioneel afhankelijk van orgnaam alleen. Om naar tweede normaalvorm te gaan doen we dan het volgende: 1. De sleutel van hhorg is hhnaam, orgnaam. 2. We stoppen de attributen branche, vertegenwoordiger, omzet in een nieuwe klasse organisatie samen met het deel van de sleutel waarvan ze afhankelijk zijn: orgnaam. 3. orgnaam wordt de sleutel van organisatie en branche, vertegenwoordiger, omzet verdwijnen uit hhorg. headhunter (hhnaam, telefoonnr) hhorg (hhnaam, orgnaam) organisatie (orgnaam, branche, vertegenwoordiger, omzet)
4.7.4. Derde normaalvorm (3NV) De derde normaalvorm is nog stricter en verwijdert bijna alle redundante gegevens. Een model bevindt zich in derde normaalvorm als en alleen als: ✔ het zich in tweede normaalvorm bevindt; ✔ er geen transitieve afhankelijkheden voorkomen. Een transitieve afhankelijkheid komt voor wanneer een niet-sleutelattribuut functioneel afhankelijk is van een ander nietsleutelattribuut. Databanken
46
Een transitieve afhankelijkheid kan dus alleen voorkomen als er meer dan één nietsleutelattribuut is. Hoe brengen we een model in derde normaalvorm: 1. Bepaal de sleutel van klasse A (in tweede normaalvorm). 2. Bepaal alle functionele afhankelijkheden. 3. Breng alle niet-sleutelattributen die functioneel afhankelijk zijn van éénzelfde nietsleutelattribuut onder in een nieuwe klasse B, samen met het niet-sleutel attribuut waarvan ze afhankelijk zijn. Dat niet-sleutel attribuut waarvan ze afhankelijk zijn wordt de sleutel van klasse B. Deze niet-sleutel attributen verdwijnen uiteraard uit klasse A. 4. De sleutel van klasse B blijft tevens staan in klasse A. Bekijken we terug ons voorbeeld. De klassen headhunter en hhorg hebben slechts één of geen niet-sleutel attribuut. We weten dus bij voorbaat dat ze zeker in derde normaalvorm zijn. De klasse organisatie daarentegen heeft wel meer dan één niet-sleutel attribuut en is dus mogelijkerwijze niet in derde normaalvorm. En er is inderdaad een niet-sleutel attribuut dat functioneel afhankelijk is van een ander niet-sleutel attribuut nl. branche → vertegenwoordiger. We brengen het model dan in derde normaalvorm op de volgende wijze: 1. We stoppen vertegenwoordiger samen met branche in een nieuwe klasse die we bijvoorbeeld branchevert noemen. 2. branche wordt de sleutel in branchevert en blijft tevens staan in de klasse organisatie. headhunter (hhnaam, telefoonnr) hhorg (hhnaam, orgnaam) organisatie (orgnaam, branche, omzet) branchevert (branche, vertegenwoordiger)
Ontstaat er bij één van de normalisatiestappen een tabel die dezelfde (kandidaat-)sleutel heeft als een al bestaande tabel, dan moeten die twee tabellen samengevoegd worden. Er zijn nog strengere normaalvormen, waarbij ook afhankelijkheden tussen de attributen van samengestelde sleutels tot aparte klassen leiden. In de praktijk voldoet 3NV echter meestal goed. Een model dat zich in derde normaalvorm bevindt kunnen we ook nog omschrijven als: alle niet-sleutelattributen hangen af van de sleutel (1NV), de gehele sleutel (2NV) en niets dan de sleutel (3NV).
4.7.5. Boyce-Codd normaalvorm (BCNV) De Boyce-Codd normaalvorm is gebaseerd op het begrip determinant. Een determinant is een enkelvoudig of samengesteld attribuut waarvan minstens één ander attribuut afhankelijk is. Een model bevindt zich in Boyce-Codd normaalvorm als en alleen als elke determinant een kandidaatsleutel is. Wanneer een model meer dan één determinant heeft, kunnen anomalieën optreden zelfs wanneer het model zich in derde normaalvorm bevindt. De derde normaalvorm behandelt immers geen gevallen waarin er overlappende determinanten bestaan, meer bepaald wanneer er twee determinanten zijn die elk eenzelfde attribuut bevatten. Stel dat het model R 4 attributen a, b, c en d bevat; b en d zijn afhankelijk van a,c en b is ook afhankelijk van a,d. Zowel a,c als a,d zijn dus determinant. De eerste determinant is bovendien ook een kandidaatsleutel, want alle andere attributen zijn ervan afhankelijk. De tweede determinant echter is géén kandidaatsleutel want c is niet afhankelijk van a,d.b. Databanken
47
Het model R bevindt zich dus niet in Boyce-Codd normaalvorm. We verduidelijken een en ander door middel van een voorbeeld. Bekijk de onderstaande tabel. Het gaat over een dieetkliniek waar elke patient vier afspraken heeft. Patient nummer
Patient naam
Afspraak id
Tijd
Dokter
1
Jan
0
09:00
Vandenbroeck
2
Frank
0
09:00
Delanghe
3
Luk
1
10:00
Vandenbroeck
4
Willem
0
13:00
Delanghe
5
Robert
1
14:00
Vandenbroeck
Op de eerste afspraak worden ze gewogen, op de tweede onderzocht, op de derde wordt het overtollige gewicht verwijderd en op de vierde wordt hun maag verkleind. Niet alle patiënten hebben elke afspraak nodig. Als de naam van de patient begint met een letter voor de "P" dan hebben ze afspraken in de voormiddag, anders in de namiddag. De eerste afspraak op een voormiddag is om 9 uur, de tweede om 10 uur etc. In de namiddag beginnen de afspraken om 13 uur, daarna elk uur. Ons model ziet er uit als: DB (patientnummer, patientnaam, afspraakid, tijd, dokter)
Welke determinanten hebben we hier? Patientnummer is determinant voor patientnaam. Patientnummer en afspraakid zijn determinant voor tijd, dokter. Tijd is determinant voor afspraakid. Of: patientnummer -> patientnaam patientnummer,afspraakid -> tijd,dokter tijd -> afspraakid
Als primary key zouden we kunnen kiezen: patientnummer,afspraakid of patientnummer,tijd. Nemen we vooreerst patientnummer,afspraakid. DB (patientnummer, patientnaam, afspraakid, tijd, dokter)
Het model voldoet reeds aan de eerste normaalvorm. Om het aan de tweede normaalvorm te laten voldoen wijzigen we het in: DB (patientnummer, afspraakid, tijd, dokter) R1 (patientnummer, patientnaam)
Het model voldoet nu ook aan de derde normaalvorm. Voldoet het aan BCNF? Neen, want tiid is een determinant maar is zeker géén kandidaatsleutel. We moeten het model dus wijzigen als volgt: DB (patientnummer, tijd, dokter) R1 (patientnummer, patientnaam) R2 (tijd, afspraakid) Nu is ook BCNF voldaan. Hadden we patientnummer,tijd gekozen als primary key bij de start, dan had het model in
de tweede normaalvorm ook voldaan aan BCNF. Je kan dat zelf uitwerken. Zoals je dan zal merken kan je op verschillende manieren hetzelfde resultaat verkrijgen. Op voorhand zeggen welke weg de gemakkelijkste is, is echter niet mogelijk.
Databanken
48
4.7.6. Terug naar het ERD model Bij het normaliseren ontstaan er nieuwe entiteittypen. Je moet dan ook het model dat je had vòòr normalisatie aanpassen aan de normalisatie. De nieuwe entiteittypen hebben dikwijls identificerende sleutels die samengesteld zijn uit meer dan één attribuut. Er moeten voor deze nieuwe entiteittypen namen worden gevonden. Daarbij is de identificerende sleutel richtinggevend. Zodra men de functionele afhankelijkheden kent, is normaliseren niets meer of minder dan het volgen van een recept. Men kan dan ook via top-down methoden vaak sneller tot een genormaliseerd gegevensmodel komen. Wanneer men gebruik maakt van kennis over het Universe of Discnurse, kan men in een gegevensmodel uit de analysefase zonder te normaliseren al kern-, karakteristieke en associatieve entiteittypen onderscheiden. Op die manier doe je door een goed inzicht in het model datgene wat bij het normaliseren op puur syntactische wijze gebeurt. De kracht van normalisatie is echter dat deze werkwijze dwingt tot volledigheid. En ze is goed bruikbaar als controlemiddel op een top-down gemaakt gegevensmodel. Er bestaan CASE-tools die kunnen normaliseren wanneer men ze als invoer de attributen en alle functionele afhankelijkheden geeft .
4.8. DENORMALISATIE Het normaliseren leidt ertoe dat er behoorlijk wat aparte entiteiten kunnen ontstaan. Al deze entiteiten zullen als aparte tabel in de databank verschijnen, en dus ook op een min of meer aparte manier in het fysieke geheugen worden opgeslagen. Wanneer veelvuldig opvragingen op de databank voorkomen waarbij veel van deze verschillende tabellen dienen gebruikt te worden, moeten al deze fysieke geheugens worden aangesproken. Dat kan leiden tot performantieproblemen met de databank. Dit treedt met name op wanneer er veel joins dienen gebruikt te worden (zie verder in hoofdstuk 6 van deze cursus). In deze gevallen kan het soms verstandig zijn over te gaan tot denormalisatie. Denormalisatie is het optimaliseren van de performantie van de databank door opzettelijk redundantie in de databank in te voeren. Dat kan op verscheidene manieren. De bij voorkeur te gebruiken manier is de databank genormaliseerd te houden, maar toe te laten dat er tegelijkertijd redundante informatie wordt opgeslagen om een betere performantie te halen. Punt is dan uiteraard dat ervoor gezorgd wordt dat alle redundante informatie steeds consistent gehouden wordt. Deze methode wordt in een grotere DBMS vaak aangeboden in de vorm van views (indexed views in MS SQL; materialized views in Oracle). Meestal zijn er hier wel beperkingen aan, afhankelijk van het gebruikte DBMS. We komen hier in hoofdstuk 6 van deze cursus nog op terug. De tweede manier is effectief de data niet genormaliseerd op te slaan. Dit kan inderdaad de opvragingen op de databank vele malen sneller maken, maar met een zware kost. Het wordt dan immers de taak van de databank ontwerper om ervoor te zorgen dat alle data consistent blijft. Meestal tracht mlen dat te doen door het opleggen van allerlei extra constraints (geldigheidsregels). Deze regels zorgen er dan voor dat alle redundante informatie in de databank consistent gehouden wordt bij alle operaties die informatie in de databank uodaten of nieuwe informatie toevoegen. Voor het voorbeeld van de headhunters uit hoofdstuk 1 zou je bijvoorbeeld een regel kunnen opleggen die wordt uitgevoerd zodra ergens het telefoonnummer van een Databanken
49
headhunter wordt gewijzigd. Deze regel zou dan alle records kunnen opzoeken waar dezelfde headhunter voorkomt, en ook daar de nieuwe gegevens wegschrijven. Wat dus als gevolg heeft dat alle schrijf en wijzigacties merkelijk trager zullen verlopen dan vóór de denormalisatie. Dat is een typisch gevolg van denormaisatie: opvraging zullen merkelijk performanter verlopen, toevoegingen en wijzigingen merkelijk trager. Meestal wordt alleen voor deze tweede manier de term denormalisatie gebruikt. We stippen tenslotte nog aan dat denormalisatie niet hetzelfde is als het gegevensmodel niet normaliseren. Tot denormalisatie wordt enkel en alleen op een bewuste manier overgegaan, nadat men eerst het model volledig genormaliseerd heeft.
4.9. OPGAVEN 1. Bedenk een voorbeeld van een vreemde sleutel die naar de eigen relatie verwijst. 2. Gegeven is de relatie R (a, b, c, d). Geef voor de onderstaande gevallen de normaalvorm waarin R zich bevindt. a) a,b -> c ; a,b -> d ; c -> d. b) a,b -> d ; a -> c. c) a,b -> c ; a,b -> d. d) a,b,c -> d ; d -> a. e) a,b,c -> d ; d -> a ; d -> b ; d -> c. f) a -> b ; a -> c ; a -> d ; b -> a ; b -> c. g) a -> b ; a -> c ; a -> d ; b -> c ; b -> d. h) a -> b ; a -> c ; a -> d ; b -> a ; c -> d. 3. Gegeven is de relatie R (a, b, c, d, e) en de volgende functionele afhankelijkheden: a > b ; a -> d ; b,c -> e ; c -> e ; c -> a ; c -> b. In welke normaalvorm is het model? Indien R niet in BNCF is, transformeer het dan tot BNCF. 4. Gegeven is de relatie R (a, b, c, d, e, f) met de functionele afhankelijkheden: a,b -> c,d,e,f ; a -> d ; d -> e ; d -> f. Normaliseer naar BCNF. 5. Een groot detailhandelsbedrijf X betrekt goederen van diverse kleine leveranciers. Bedrijf X geeft hiertoe bestelbonnen uit die naar de leveranciers worden verstuurd. De bestelbonnen zien er zo uit: Leveranciernummer: Adres: Postcode: Woonplaats: Artikel Stukprijs ... ... ... ... ... ... ... ... Subtotaal:
Bonnummer: Leveringsdatum:
Aantal ... ... ... ...
Prijs ... ... ... ...
Artikel Stukprijs ... ... ... ... ... ... ... ... Overgedragen: Totaal:
Aantal ... ... ... ...
Prijs ... ... ... ...
De volgende aannames zijn er: (1) Op een bon staan 1 tot 30 bestellingen van verschillende artikelen. Databanken
50
(2) Bonnummers worden eenmalig uitgegeven en dan naar verschillende leveranciers gestuurd met ingevulde artikelnummers en aantallen. (3) De leverancier stuurt de bon terug naar bedrijf X met ingevulde prijzen en leverdatum, en levert alle artikelen van de bon in één keer op de leveringsdatum. (4) X kiest dan op basis van leveringsdatum en prijs de leverancier die de bestelling mag leveren. Enkel de gekozen bestelling wordt in de databank bijgehouden. (5) De stukprijzen variëren tussen leveranciers; bij elke nieuwe bon kan elke leverancier bovendien een andere prijs opgeven. (6) Er woont nooit meer dan 1 leverancier op één adres. Maak van dit model een genormaliseerde gegevensstructuur. Begin met vooreerst alle attributen in één tabel te stoppen als volgt: bestelling (bonnummer, artikelnummer, stukprijs, aantal, prijs, leveranciersnummer, adres, postcode, woonplaats, leveringsdatum)
a) Spoor functionele afhankelijkheden op. b) Welke kandidaat-sleutels zijn er? Kies een identificerende sleutel. c) Normaliseer en leg bij elke stap uit waarom het model beter is dan het vorige. 6. Bekijken we een eenvoudige video bibliotheek. Elke video heeft een titel, een regisseur en een uniek serienummer. Uitleners hebben een naam, adres en lidnummer. Van elke video bestaat er maar één exemplaar. De databank bevat de verhuur van video's aan uitleners. Het model dat we krijgen ziet er als volgt uit: verhuur (titel, regisseur, serienummer, naam, adres, lidnummer, datum) a) Spoor functionele afhankelijkheden op. b) Welke kandidaat-sleutels zijn er? Kies een identificerende sleutel. c) Normaliseer en leg bij elke stap uit waarom het model beter is dan het vorige. 7. Normaliseer het volgende model. Het model geeft het rapport weer voor één student. Elke student kan één of meerdere hoofdvakken volgen en elk hoofdvak bestaat uit een aantal vakken waarvoor er punten worden gegeven. rapport (studentnummer, studentnaam, (hoofdvak, mentor, (vaknummer, vaktitel, leraar, lokaal, punten)))
De afhankelijkheden zijn de volgende: studentnummer -> studentnaam vaknummer -> vaktitel,leraar leraar -> lokaal studentnummer, vaknummer, hoofdvak -> punten studentnummer, hoofdvak -> mentor mentor -> hoofdvak
Databanken
51
5. RELATIONELE DATABASES EN HUN EIGENSCHAPPEN Vrijwel alle databases noemen zich tegenwoordig relationeel. In hoofdstuk 1 zagen we dat dat betekent dat de gegevens in dergelijke databases worden bewaard als tabellen. Maar er zit meer achter. Het relationele model is een taal voor het werken met gegevens. Het stoelt op een tak van de wiskunde: de verzamelingenleer, en in het bijzonder de leer der relaties. Het gaat dan over het wiskundig begrip relatie, wat altijd als een tabel weer te geven is - en welke dus niet dezelfde zijn als de relaties die we zagen tussen de entiteiten in het ERD. In een database moeten gegevens altijd eerst gedefiniëerd worden vooraleer ze kunnen gebruikt worden. Gegevens definiëren is gemakkelijk in relationele databases. De allergrootste kracht van relationele databases is echter het gebruiksgemak, dat te danken is aan de veelzijdigheid van de SQL-opdracht select. In wat volgt zullen we zien welk stuk wiskunde de basis is voor deze opdracht. Maar de database moet nog meer kunnen. Geen enkel model, relationeel of niet, helpt tegen stroomstoringen of computer-inbrekers om maar iets te noemen. Ook tegen dergelijke zaken moet onze database op een of andere manier voorzien zijn. De data die in de database gestockeerd wordt is immers vaak een van de waardevolste eigendommen van de organisatie waarin ze thuishoort.
5.1. VERZAMELINGEN Een verzameling is een ongeordende serie gelijksoortige elementen. De elementen van een verzameling kunnen enkelvoudig of samengesteld zijn. Op verzamelingen kunnen een aantal wiskundige bewerkingen uitgevoerd worden. De belangrijkste voor ons zijn: unie, intersectie, verschil en product. Unie, intersectie en verschil werken alleen op verzamelingen waarvan de elementen van hetzelfde type zijn. Stel dat we twee verzamelingen hebben. Verzameling A bevat de elementen 1, 2 en 3, wat geschreven wordt als A = {1,2,3}. De verzameling B = {1,4,5}. De unie van A en B (ook wel vereniging genoemd en geschreven als A υ B) bevat alle elementen die in A, in B of in allebei voorkomen. A υ B = {1,2,3,4,5}. De intersectie (ook wel doorsnede genoemd en geschreven als A ∩ B) bevat de elementen die zowel in A als in B voorkomen. A ∩ B = {1}. Het verschil A minus B, geschreven als A – B, bevat de elementen die wel in A maar niet in B voorkomen. A – B = {2,3}. In tegenstelling tot unie en intersectie is het verschil assymetrisch: A – B ≠ B – A. Een duidelijke en gangbare weergave van deze bewerkingen op verzamelingen met elementen van hetzelfde type vormt het Venn-diagram. In dergelijk diagram wordt elke verzameling getekend als een cirkel of ellips die al zijn elementen omsluit. De drie behandelde bewerkingen hebben verzamelingen nodig met elementen van hetzelfde type en leveren als resultaat weer een verzameling met elementen van dat type. Om het product van twee verzamelingen te maken hoeven de elementen niet van hetzelfde type te zijn. Het product is op zijn beurt een verzameling met elementen van nog een ander type. Het bevat namelijk alle geordende paren waarvan het eerste deel element is van de eerste, en het tweede deel element is van de tweede verzameling. Als we bijvoorbeeld verzameling A vermenigvuldigen met verzameling C = {x,y} dan is de productverzameling: A x C = {(1,x),(2,x),(3,x),(1,y),(2,y),(3,y)}. Het product A x B x C zal als elementen geen paren, maar drietallen hebben. Het aantal elementen van de productverzameling is het product van het aantal elementen van de verzamelingen. Het zo Databanken
52
gedfiniëerde product is uiteraard iets anders dan een rekenkundig product van getallen. Men noemt het ook wel het Cartesisch product.
5.2. RELATIES EN TUPLES Een relatie is een deelverzameling van het product van een aantal verzamelingen. De relatie {(2,y),(1,y),(1,x)} bijvoorbeeld is een relatie tussen de verzamelingen A en C. Wat is nu het verband van dergelijke relatie met een tabel? Stel dat we een entiteittype "student" hebben met de attributen nummer, naam en geslacht. We hebben dan bijvoorbeeld de verzamelingen: nummer = {1,2,3}, naam = {Jan, Mieke,Frank) en tenslotte geslacht = {M,V}. Het product van deze drie verzamelingen is: nummer x naam x geslacht = {1,Jan,M} {2,Jan,M} {3,Jan,M} {1,Jan,V} {2,Jan,V} {3,Jan,V} {1,Mieke,M} {2,Mieke,M} {3,Mieke,M} {1,Mieke,V} {2,Mieke,V} {3,Mieke,V} {1,Frank,M} {2,Frank,M} {3,Frank,M} {1,Frank,V} {2,Frank,V} {3,Frank,V} Een deelverzameling van dit product is bijvoorbeeld: bestaande elementen = {1,Jan,M} {2,Mieke,V} {3,Frank,M} Deze deelverzameling is volgens bovenstaande definitie een relatie tussen nummer, naam en geslacht. Als we er betekenis aan toekennen zien we dat deze relatie zou kunnen overeenkomen met de waarden van een tabel in een database. Een tuple is een element van een relatie, ofwel een rij in een tabel. Het woord is afgeleid uit de Engelse woorden quintuple (vijftal), sextuple (zestal),... Een vertaling zou "zoveeltal" kunnen zijn, maar wordt niet gebruikt. Als we de hierboven gedefinieerde relatie in een tabel weergeven krijgen we het volgende: nummer
naam
geslacht
1
Jan
M
2
Mieke
V
3
Frank
M
De rij {1,Jan,M} is het eerste tuple, de rij {2,Mieke,V} het tweede tuple en {3,Frank,M} het derde tuple van de relatie "persoon". De relatie bevat de attributen "nummer", "naam" en "geslacht".
5.3. RELATIONELE BEWERKINGEN Omdat relaties verzamelingen zijn, kunnen de bewerkingen unie, intersectie, verschil en product erop toegepast worden. Daarnaast zijn er ook bewerkingen die specifiek zijn voor relaties: selectie, projectie en join. Selectie en projectie zijn bewerkingen die veel op elkaar lijken. Bij selectie selecteert men bepaalde tuples uit een relatie, met als resultaat een nieuwe relatie met evenveel attributen. Bij projectie selecteert men bepaalde attributen, met als resultaat een nieuwe relatie met minder attributen. Join is een hoogst belangrijke bewerking in een relationele database. Een join is namelijk een koppeling van relaties op grond van de waarden in gemeenschappelijke attributen. Een join is een deelverzameling van het product van de gejoinde relaties. De mogelijkheid Databanken
53
op elk gewenst ogenblik willekeurige tabellen met elkaar te koppelen, is een van de grote troeven van relationele databases. In de jaren zeventig werden er talen ontwikkeld die de zeven vermelde bewerkingen expliciet bevatten. Deze talen waren gebaseerd op de relationele algebra. Laten we bij wijze van voorbeeld een projectie van de relatie 'persoon' op attribuut 'naam' beschouwen, gecombineerd met een selectie van alleen de mannelijke personen, of in gewone taal: geef alle namen van mannelijke personen'. In een vraagtaal uit de relationele algebra werd dit geschreven als: project (select geslacht = 'M' from persoon) over naam In de loop der jaren hebben deze talen het moeten afleggen tegen talen uit de relationele logica. Deze talen, waartoe ook SQL behoort, kunnen hetzelfde als de algebra, maar zijn voor de meeste mensen gemakkelijker te begrijpen. Opvragingen krijgen in de relationele logica een standaardvorm, bestaande uit een doelgroep gevolgd door voorwaarden. Een ander woord voor voorwaarde is predicaat; men spreekt ook wel van predicattenlogica. De algemene vorm is: select <doelgroep> where
De doelgroep bevat de kolommen en tabellen waaruit de gegevens moeten komen. De voorwaarden maken uit welke gegevens uit de doelgroep worden geselecteerd. 'Geef alle namen van mannelijke personen' wordt in de relationele logica: select naam from persoon where geslacht = 'M' Je ziet dadelijk gelijkenis én verschil tussen deze formulering en de formulering uit de algebra-versie. Deze syntaxis biedt in sommige opzichten minder mogelijkheden dan de relationele algebra. Het feit dat de relationele logica ten opzichte van de algebra een betere begrijpelijkheid biedt, is echter doorslaggevend gebleken. In talen die gebaseerd zijn op de logica zijn de relationele bewerkingen niet zonder meer terug te vinden, in tegenstelling tot de talen die uit de relationele algebra afkomstig zijn. We zullen een van deze talen, SQL, nader bekijken om te zien hoe deze bewerkingen geformuleerd kunnen worden.
5.4. RELATIONELE BEWERKINGEN IN SQL SQL is een vraagtaal gebaseerd op de relationele logica. Hoe kunnen we nu de vier bewerkingen voor verzamelingen en de drie bewerkingen op relaties in SQL uitvoeren? Het gaat daarbij alleen om de opvragingsopdrachten. De vele andere mogelijkheden die SQL bevat om gegevens te definiëren en te bewerken, komen hier niet aan bod. We zullen ze in de volgende hoofdstukken zien, waar we een volledig overzicht van SQL zullen hebben. Hier gaan we enkel in op de theoretische achtergrond van relationele databases, waarvan we duidelijke kenmerken kunnen terugvinden in SQL. Er zijn zeer veel versies van SQL in omloop. Weliswaar bestaat er een standaard, maar elke database-leverancier heeft toch zijn eigen SQL, met eigen beperkingen en uitbreidingen. Op dit ogenblik is de standaard SQL:2008. Belangrijke voorlopers waren SQL92 (ook wel SQL 2 genoemd), SQL99 (SQL3) en SQL:2003. Een volledig SQL kent alle bovengenoemde bewerkingen. We zullen ze met enkele voorbeelden trachten duidelijk te maken. Daarbij gebruiken we weer de termen tabel, rij en kolom in plaats van relatie, tuple en attribuut.
Databanken
54
5.4.1. Unie, intersectie en verschil Voor unie, intersectie en verschil kent SQL de opdrachten union, intersect en minus. Deze drie opdrachten werken op twee tabellen met kolommen van hetzelfde datatype. Een voorbeeld van unie is: `geef de namen van alle personen met het nummer 1 en/of het nummer 3'. In SQL schrijven we dat met union als volgt: select naam from persoon where nummer=1 union select naam from persoon where nummer=3 Een intersectie, bijvoorbeeld 'geef de namen van de personen met de naam Jan én van het geslacht M' schrijven we met intersect dan als: select naam from persoon where naam='Jan' intersect select naam from persoon where geslacht='M' Om een verschil op te vragen, bijvoorbeeld 'geef de namen van alle mannelijk personen die niet de naam Jan hebben' ziet er met minus als volgt uit: select naam from persoon where geslacht='M' minus select naam from persoon where naam='Jan' De vierde verzamelingenbewerking - product - kent geen rechtstreekse operator in SQL. We behandelen het product verder.
5.4.2. Selectie en projectie Bij selectie en projectie worden er rijen respectievelijk kolommen uit een tabel geselecteerd. Een selectie op tabel persoon is bijvoorbeeld 'geef de namen van alle mannelijke personen'. De selectie levert een where-voorwaarde op in de selectopdracht. select naam from persoon where geslacht='M' Projectie is het beperken van het aantal kolommen. Een projectie op tabel persoon is bijvoorbeeld 'geef naam en nummer van alle personen': select naam, nummer from persoon Hoewel de namen dus anders doen vermoeden, staan achter het woord select de kolommen waarover SQL projecteert, achter het woord where de voorwaarden waarop SQL rijen selecteert.
5.4.3. Product en join Product en join worden in SQL op dezelfde wijze weergegeven. De vraag naar het gehele product van twee tabellen is meestal zinloos. Stel dat we bijvoorbeeld naast de hoger verrmelde tabel persoon, een tweede tabel "verblijf" hebben die er als volgt uitziet:
Databanken
kot
persoon
van
tot
1
1
1/10/2005
2
2
1/10/2004
30/6/2005
3
3
1/10/2004
30/6/2005
4
2
1/10/2005
5
3
1/10/2005 55
Van de tabellen persoon en verblijf is het product alle combinaties van persoonsgegevens en verblijfsgegevens naast elkaar, ongeacht of de persoons- en verblijfsgegevens dezelfde persoon betreffen. Dat is uiteraard weinig zinvol. Dat product kan in SQL als volgt geschreven worden: select naam, nummer, geslacht, kot, persoon, van, tot from persoon, verblijf Het resultaat zouden 3x5 = 15 rijen zijn waarvan er 5 met zinvolle informatie. Wanneer men een voorwaarde oplegt aan een kolom die in beide tabellen voorkomt, zodat alleen bepaalde rijen uit de ene tabel gekoppeld worden aan bepaalde rijen uit de andere tabel, ontstaat een join. Een join is dus een selectie uit het product van twee tabellen. Een zinvolle join van persoon met verblijf is 'laat van alle personen zien op welk kot ze hebben verbleven': select naam, nummer, geslacht, kot, persoon, van, tot from persoon, verblijf where persoon.nummer=verblijf.persoon We krijgen dan inderdaad de correcte verblijfsgegevens. Natuurlijk zal men in de praktijk slechts uit één van de gejoinde tabellen de sleutel opvragen: ofwel persoon.nummer ofwel verblijf.persoon dus. De voorwaarde 'where persoon.nummer=verblijf.persoon' noemt men de joinvoorwaarde. Bijna alle joins die in de praktijk voorkomen, hebben als voorwaarde een gelijkheid (=) tussen kolommen. Men noemt zo'n join dan ook een natural join of ook wel equal join. Het vergeten van joinvoorwaarden in SQL-opdrachten is een vrij veel voorkomende fout, die tot onzinnige resultaten kan leiden wanneer rijen gekoppeld worden die niets met eIkaar te maken hebben. Het verdient dan ook aanbeveling om expliciet de join te gebruiken. Bovenstaande wordt dan: select naam, nummer, geslacht, kot, persoon, van, tot from persoon join verblijf on persoon.nummer=verblijf.persoon
5.4.4. Andere bewerkingen Op de meeste plaatsen in een select-opdracht zijn naast de genoemde bewerkingen ook de bekende rekenkundige bewerkingen mogelijk: optellen, aftrekken, vermenigvuldigen, delen, machtsverheffen, worteltrekken, goniometrische bewerkingen, gemiddelde, statistische bewerkingen,.... Daarnaast zijn ook bewerkingen op letterreeksen mogelijk zoals afkappen, letters zoeken en zeer vele andere. Ook kan er gerekend worden met tijdstippen. Afhankelijk ven het gebruikte SQL-dialect zullen min of meerdere van dergelijke mogelijkheden aanwezig zijn. Edgar Codd is de geestelijke vader van het relationele model. Hij publiceerde in 1970 het artikel waar het allemaal mee begon. In de jaren 70 en 80 formuleerde hij een aantal eisen waaraan een databasesysteem moest voldoen om zichzelf 'relationeel' te mogen noemen. Deze eisen betroffen in hoofdzaak de gegevensstructuur (louter tabellen, geen pointers), de mogelijkheden voor gegevensmanipulatie (alle bewerkingen uit de relationele algebra leveren), en de mogelijkheden voor integriteitsbewaking (de data-dictionary ondersteunt entity integrity en referential integrity). Zeer veel systemen voldeden toen niet aan deze eisen, maar dat is nu anders. De grotere pakketten voldoen aan praktisch al Codd's eisen. Kleinere pakketten zoals Access, laten hier en daar nog steken vallen. SQL is inmiddels de wereldstandaardtaal om relationele databases mee te benaderen. De kleinere PC-databasepakketten bieden meestal de mogelijkheid om via invulformulieren SQL opdrachten te definiëren. Om echt met een database te kunnen werken is een grondige kennis van SQL echter onontbeerlijk. Databanken
56
5.5. VERDERE EISEN VOOR EEN DBMS We hebben gezien dat het relationele model een krachtige en vooral flexibele taal is om gegevensmodellen in weer te geven. Maar een database-management-systeem moet nog meer kunnen. Geen enkel database-metamodel helpt tegen stroomstoringen of computerinbrekers, om maar iets te noemen. In wat volgt geven we een korte opsomming van de basistaken voor elk DBMS op dat vlak. Het DBMS grijpt in op alle niveaus en interfaces van de architectuur van de database zoals u die kent uit figuur 1.1. Voor elke gebruikersopdracht moet het DBMS vertalingen uitvoeren van gebruikersniveau via conceptueel niveau naar opslagniveau en weer terug. Stel dat een gebruiker een opdracht geeft aan de database in een vraagtaal. Er gebeurt dan achtereenvolgens dit: 1. De gebruiker typt de opdracht in. 2. Het gebruikersprogramma geeft de opdracht door aan de vraagtaal. 3. De vraagtaal vangt de opdracht op en interpreteert hem. 4. Is de opdracht syntactisch correct, dan vergelijkt het DBMS hem met het externe schema van de gebruiker. Hierbij is 'schema' databasejargon voor 'model'. 5. Levert 4 geen fouten op, dan vertaalt het DMBS de opdracht naar het conceptuele schema. 6. Het DBMS vergelijkt de opdracht met het conceptuele schema. 7. Indien er geen fout optreedt, vertaalt het DBMS de opdracht naar het interne schema. 8. Het DBMS voert de opdracht uit op de fysieke database. Hier vinden dus lees- of schrijfopdrachten op fysieke gegevensbestanden plaats. 9. Het DBMS vertaalt het resultaat van de opdracht naar een conceptueel en daarna naar een extern schema en geeft het door aan het programma dat de opdracht gaf. Dit programma zorgt voor een geschikte weergave van het resultaat aan de gebruiker. Indien er bij één van de stappen een fout optreedt, zal het DBMS een foutcode teruggeven. De gebruiker zal hetzij deze code, dan wel een melding te zien krijgen. Het toepassingsprogramma of de database-vraagtaal zorgt voor de vertaling van de foutcode naar een meer begrijpelijke vorm. Opdrachten aan de database die in een toepassingsprogramma verwerkt zijn kunnen worden gecompileerd. In dat geval worden bij het compileren de stappen 1 tot en met 7 uitgevoerd en wordt de opdracht bewaard in een vorm die geschikt is om de fysieke database te benaderen. Het DBMS wacht met het uitvoeren van stap 8 en 9 tot een gebruiker het toepassingsprogramma laat uitvoeren. Compileren geeft snelheidswinst wanneer een opdracht herhaald moet worden uitgevoerd.
5.5.1. Performance De performance van een database is de snelheid waarmee hij opdrachten afhandelt. Er zijn allerlei manieren om de performance te meten. Voor het vergelijken van databasepakketten ontwikkelt men wel zogeheten performance benchmarks. Een benchmark is een verzameling opdrachten die samen een representatieve belasting vormen. Eén enkele opdracht geeft geen duidelijk beeld van de performance, omdat elk databasepakket zijn sterke en zwakke punten heeft; de opdracht kan toevallig extreem goed of slecht voor de dag komen. Het DBMS tracht zijn eigen performance te optimaliseren. De component die hiermee belast is heet de query optimizer. Een opdracht (in het Engels: query = verzoek) uit een toepassing kan meestal op diverse manieren naar het interne schema vertaald worden. De query optimizer bevat vuistregels om een zo snel mogelijk werkende vertaling te vinden. Wanneer er bijvoorbeeld een query is met twee voorwaarden, moet de query Databanken
57
optimizer kiezen welke voorwaarde hij het eerst zal behandelen. Stel dat we in ons headhuntermodel bijvoorbeeld de twee voorwaarden hebben: bedrijven gevolgd door headhunter X (voorwaarde 1) die de letter u in hun naam hebben (voorwaarde 2). De tweede voorwaarde betekent dat alle rijen moeten langsgelopen worden om te kijken of in de bedrijfsnaam de letter u voorkomt. Elke bedrijfsnaam moet worden gelezen en letter voor letter vergeleken met u. De eerste voorwaarde is uiteraard veel simpeler en zal het aantal af te lopen rijen erg beperken. Een goed werkende query optimizer zal dus eerst de eerste voorwaarde en dan pas de tweede voorwaarde uitvoeren. Het is duidelijk dat een query optimizer niet in alle situaties de feitelijk snelste oplossing zal kiezen. Er zijn dus situaties waarin applicatieprogrammeurs de query optimizer een handje zullen moeten helpen om een betere performance te verkrijgen. Het manipuleren van gegevensstructuur en programmatuur van een database met het doel de performance te verbeteren, behoort tot de taken van de database-administrator. Deze kan opdrachten afstemmen op de query optimizer. Tevens kan hij de opslagstructuur van de gegevens aan laten sluiten bij de meest voorkomende opdrachten. Rijen die vaak tegelijk benodigd zijn, kan hij bij elkaar opslaan, zodat ze snel uit het achtergrondgeheugen kunnen worden ingelezen (zie ook hoger).
5.5.2. Transacties Dikwijls vormt een aantal bewerkingen op een database samen een logisch geheel. Dit logische geheel heet een transactie. Een geijkt voorbeeld is een financiële transactie: een bedrag wordt afgeboekt van rekening A en bijgeboekt op rekening B. Stel dat er zo'n transactie plaatsvindt op het volgende bestand. rekening
saldo
A
110
B
30
Nemen we bijvoorbeeld aan dat 20 Euro wordt overgeboekt van rekening A naar rekening B. Het programma dat de overboekingen regelt zal voor deze transactie drie dingen moeten doen: eerst moet gekeken worden of het bedrag bij A afgeboekt mag worden. In ons voorbeeld is er voldoende geld aanwezig dus wordt het bedrag inderdaad afgeboekt. Het bestand komt er na de afboeking even als volgt uit te zien: rekening
saldo
A
90
B
30
Tenslotte wordt de 20 Euro op rekening B bijgeboekt. Het resultaat is dan: rekening
saldo
A
90
B
50
Het is duidelijk waar een mogelijk probleem kan opduiken. Stel dat er iets misgaat met het programma op het ogenblik dat het geld al wel van A is afgeboekt, maar nog niet bij B is bijgeboekt. Wat dan? De gangbare oplossing is dat het DBMS de transactie in eerste instantie uitvoert op een kopie van de data. Deze kopie heet de log of de redo log. Pas als de transactie geheel is afgerond, wordt de database echt gewijzigd. Het DBMS moet daartoe opdrachten kennen die aangeven welke opdrachten samen een Databanken
58
transactie vormen. SQL kent hiervoor de opdracht commit (van to commit = iets doen dat niet meer ongedaan kan worden gemaakt). Komt in een serie opdrachten commit voor, dan worden alle wijzigingen vanaf de vorige commit, die zolang in een kopie werden bijgehouden, in de echte database weggeschreven. Voor ons voorbeeld zouden we dan iets als volgt krijgen: ➔ commit; ➔ lees saldo A en controleer of het toereikend is; ➔ boek bedrag af bij A; ➔ lees saldo B; ➔ boek bedrag bij bij B; ➔ commit. De tegenhanger van commit is rollback. Met deze opdracht draagt SQL het DBMS op de transactie in zijn geheel terug te draaien. De opdracht rollback zal men bijvoorbeeld aantreffen in routines voor foutafhandeling. Een transactie kan alles zijn wat een programmeur als transactie wenst te beschouwen. Er hoeft geen sprake te zijn van zo'n sterke samenhang als in het voorbeeld.
5.5.3. Transacties en geldigheidsregels Het transactiemechanisme biedt aanknopingspunten om geldigheidsregels te bewaken. Wijzigingen die tijdens een transactie plaatsvinden, kunnen soms de integriteit tijdelijk verstoren, zoals we zagen. Het is in zo'n geval niet zinvol om na elke wijziging de integriteit te controleren maar beter om te wachten tot het eind van de transactie. Treedt er bij de controle een fout aan het licht, dan kan de transactie worden teruggedraaid. Controle van de integriteit na elke wijziging (dus na elke SQL update- of insert-opdracht in een programma) heet onmiddellijke integriteitscontrole (in het Engels immediate integrity checking). Controle na afloop van een transactie wordt uitgestelde integriteitscontrole (deferred integrity checking) genoemd. Men kan in toepassingsprogramma's vaak aangeven op welke van deze manieren men wil werken. Bij transacties die uit meer dan één SQL-opdracht bestaan, zoals in de voorbeelden hierboven, is uitgestelde controle het meest geschikt. Men kan er ook voor kiezen om de integriteit helemaal niet te controleren tijdens het draaien van het toepassingsprogramma, maar daarmee wachten tot een zelfgekozen tijdstip, bijvoorbeeld wekelijks. In dat geval zal men bijvoorbeeld een apart programma draaien, dat een foutenlijst produceert. Men noemt deze methode integriteitscontrole achteraf De afweging tussen per transactie controleren enerzijds en achteraf controleren anderzijds heeft te maken met de mogelijkheden van het databasepakket en de computer. Integriteitscontroles vertragen de werking van een programma namelijk vaak merkbaar; voor elke controle zijn leesopdrachten op de database nodig. Voor het controleren van de belangrijke referentiële integriteit zijn leesopdrachten op verschillende tabellen tegelijk nodig en die zijn tijdrovend. Controle achteraf, wat op een rustig tijdstip kan plaatsvinden is niet zo veilig maar drukt niet op de performance van de toepassingen.
5.5.4. Gelijktijdigheid Wanneer verschillende toepassingen tegelijk dezelfde bestanden benaderen, is er sprake van gelijktijdigheid, in het Engels concurrency. De mogelijkheid van gelijktijdigheid is een van de sterke punten van databasepakketten, tenminste van die uit de 'zware' categorie. Het is echter voor een DBMS een zware taak om te voorkomen dat de integriteit van de gegevens door gelijktijdig opererende programmas niet wordt verstoord. Bekijken we terug Databanken
59
het voorbeeld uit de vorige paragraaf. Stel dat niet alleen toepassing X 20 Euro van A naar B overmaakt, maar dat tegelijkertijd een andere toepassing Y 40 Euro van C naar A overmaakt. Als er voor de start van de overboeking 80 Euro op rekening C staat, is de gewenste eindtoestand dan: rekening
saldo
A
130
B
50
C
40
Voordat er een bedrag wordt afgeboekt moet het programma het saldo inlezen en controleren. Ga ervan uit dat de volgorde van de opdrachten als volgt is: toepassing X tijdstip 0 tijdstip 1 tijdstip 2 tijdstip 3 tijdstip 4 tijdstip 5 tijdstip 6 tijdstip 7
toepassing Y lees saldo C : 80 boek 40 af van saldo C : 40
lees saldo A : 110 lees saldo A : 110 boek 40 bij op saldo A : 150 boek 20 af van saldo A : 90 lees saldo B : 30 boek 20 bij op saldo B : 50
Het resultaat van de bovenstaande bewerkingen zou zijn: rekening
saldo
A
90
B
50
C
40
Het saldo van A is 40 Euro te laag, omdat de wijziging die toepassing Y heeft aangebracht verloren is gegaan. Er zijn verschillende varianten van dit probleem. Bij meer dan twee gelijktijdige toepassingen neemt het probleem bovendien sterk in complexiteit toe. Het DBMS kan er op diverse manieren mee om gaan. Gebruikelijk is de methode locking: zodra een toepassing wijzigingen aanbrengt op een bestand, wordt dit bestand onbenaderbaar gemaakt voor andere toepassingen. Deze moeten wachten tot het eerste programma met het bestand klaar is. In het voorbeeld zou toepassing X hebben moeten wachten tot toepassing Y het bestand had vrijgegeven. Locking is veilig, maar heeft als keerzijde dat het ten koste kan gaan van de performantie. Om dit bezwaar te verminderen kan locking ook op het niveau van één enkele tuple gebeuren, of zelfs op het niveau van één attribuutwaarde binnen een tuple. In het voorbeeld had dan toepassing Y moeten wachten tot toepassing X met het saldo van A klaar was. Wanneer verschillende toepassingen eenzelfde bestand gebruiken maar elke toepassing uitsluitend gegevens opvraagt, is er geen gevaar voor integriteitsverlies. Het DBMS zal gelijktijdige opvraging in dat geval dan ook wel toelaten, mits elk van de gelijktijdige toepassingen aan het begin van de transactie meldt dat er alleen opgevraagd zal worden. Er is een situatie waarin locking tot een eindeloze wachttoestand leidt. Dit wordt deadlock of - nog dramatischer - deadly embrace genoemd. Het ontstaat wanneer toepassingen wederzijds op elkaars bestanden wachten. Het eenvoudigste geval behelst twee toepassingen die elk in twee bestanden wijzigingen aanbrengen, op de volgende manier: Databanken
60
tijdstip 0 tijdstip 1 tijdstip 2
toepassing X open bestand 1 : succes bestand 1 wordt gelocked open bestand 2 : lukt niet wacht tot Y het vrijgeeft
tijdstip 3
toepassing Y open bestand 2 : succes bestand 2 wordt gelocked open bestand 1 : lukt niet wacht tot X het vrijgeeft
Om uit de dodelijke omhelzing te geraken is er slechts één oplossing: alle wachtende transacties, op één na, moeten worden teruggedraaid. In het voorbeeld zal toepassing Y worden teruggedraaid.
5.5.5. Beveiliging Met beveiliging van databases bedoelt men bescherming tegen opzettelijk misbruik. Men kan een database op allerlei manieren beveiligen. Zonder volledig te willen zijn onderscheiden we in volgorde van oplopende strengheid: 1. Niet beveiligen. De database is door iedereen publiekelijk te benaderen, bijvoorbeeld via het internet of andere elektronische netwerken. 2. Niet beveiligen, maar toegankelijkheid beperken doordat de database op een standalone computersysteem draait. 3. Beveiligen doordat de gebruiker iets moet onthouden, meestal een wachtwoord. Er moet ingelogd worden. 4. Beveiligen doordat de gebruiker iets moet bezitten, zoals een diskette, een pasje met code of gewoon een sleutel van een ruimte. 5. Beveiligen door de gegevens versleuteld op te slaan: encryptie ('geheimschrift'). Om de gegevens te kunnen lezen moet men de sleutel kennen die de gegevens ontcijferd. 6. Beveiligen door een combinatie van bovenstaande methodes toe te passen. Wachtwoorden blijken in de praktijk een eerder beperkte beveiliging te leveren. Vaak zwerven wachtwoorden als publieke geheimen door afdelingen. Of je kan ze vinden op post-it stickertjes op computerschermen. De fantasie van de personen die de wachtwoorden moeten verzinnen laat soms ook te wensen over. Er bestaan top 10's van veelgebruikte wachtwoorden. Gebruikersnamen worden dikwijls tevens als wachtwoord gebruikt. Kortom, een creatieve hacker komt al snel een geldig wachtwoord tegen. Beveiligingsmethoden via wachtwoorden, die vaak meegeleverd worden met databasepakketten, zijn dus niet méér te vertrouwen dan de mensen die ermee werken. Indien een degelijke beveiliging gewenst wordt zijn paswoorden meestal onvoldoende. Encryptie speelt in beveiliging een steeds belangrijker rol. Uiteraard hangt alles van hoe waardevol de gegevens zijn, en hoelang men ze wil beveiligen. Want uiteindelijk zijn de meeste beveiligingen wel te kraken, op voorwaarde dat men er voldoende computerkracht en tijd in stopt.
5.5.6. Recovery Het woord recovery staat voor herstel van gegevens na rampen. Zo mag een stroomstoring halverwege een transactie geen gegevensverlies tot gevolg hebben. Recovery vindt dus plaats bij feitelijke vernietiging van gegevens, ongeacht of er opzet in het spel was of niet. Een DBMS kan zichzelf slechts tot op zekere hoogte beveiligen tegen dataverlies. Het is bijvoorbeeld niet in staat de gegevens te redden als de hardware het begeeft, bijvoorbeeld door het vastlopen van de leeskop van een harde schijf. Het staat ook machteloos tegen Databanken
61
computervirussen. Om beschermd te zijn tegen dergelijke rampen moeten de gegevens, inclusief de data-dictionary-gegevens, op meer dan één plaats gescheiden aanwezig zijn. Men kan bijvoorbeeld een kopie van het geheugen in een ander gebouw bewaren, of een kopie van de gehele database gereed houden op een ander computersysteem, zodat het andere systeem bij rampen direct kan invallen. Vele problemen kan het DBMS zelf wel oplossen. De meeste storingen beperken zich ertoe dat toepassingsprogramma's of stroomstoringen het DBMS ontregelen. Bij opnieuw opstarten van het systeem moeten er dan gegevens bewaard zijn over de toestand van de database ten tijde van de storing. Het DBMS leest deze bestanden en is daardoor in staat de gegevens te herstellen.
5.6. DE DATA-DICTIONARY Om zijn werk te kunnen doen heeft het database-management-systeem een schat aan gegevens nodig. Dit zijn niet de gebruikersgegevens maar de gegevens over die gegevens: metagegevens. Metagegevens liggen opgeslagen in het inwendig archief van de database: de data-dictionary. In deze paragraaf behandelen we welke metagegevens een database nodig heeft en hoe deze gegevens zijn aan te maken en te wijzigen.
5.6.1. Metagegevens Metagegevens beschrijven de overige gegevens in de database. Ze worden opgeslagen in de data-dictionary. Niet alle databasepakketten hebben een even uitgebreide datadictionary. Sterker nog, de kwaliteit van de data-dictionary is een belangrijk onderscheidend kenmerk tussen pakketten. Er zijn metagegevens over alle drie niveaus van de database-architectuur: de gebruikers, het gegevensmodel en de opslagstructuur. Centraal staat het gegevensmodel, dus de tabellen, kolommen en integriteitsregels. Metagegevens over kolommen die in een data-dictionary zouden kunnen voorkomen, zijn allereerst van elke kolom de volgende: ✔ de naam van de kolom; ✔ de tabel waarin de kolom voorkomt; ✔ het datatype, ofwel de domeindefinitie (liefst zo precies mogelijk, dus bijvoorbeeld niet "een letter" of "character (1)", maar {'M','m','V','d'}; ✔ andere geldigheidsregels, bijvoorbeeld of de kolom deel uitmaakt van een kandidaatsleutel, identificerende sleutel of foreign key; ✔ een toelichting op de betekenis, in de vorm van een korte tekst; ✔ een overzicht van de zichtbaarheid: welke toepassingen of databasegebruikers ✔ mogen de kolom lezen, welke wijzigen? Per tabel zijn er in elk geval: ✔ de gebruiker die de tabel heeft gecreëerd; ✔ een overzicht van het geheugengebruik van de tabel. Verder zijn er nog andere data-dictionary-gegevens die niet per kolom of per tabel gelden, bijvoorbeeld: ✔ een overzicht van gebruikers van de database; ✔ een overzicht van views voor elke gebruiker, met vermelding van de SQL-opdracht waarop de view is gebaseerd; ✔ een lijst van de tabellen en kolommen die toegankelijk zijn voor elke gebruiker; ✔ een lijst met privileges per gebruiker; privileges kunnen bijvoorbeeld zijn: 'mag uitsluitend opvragen en niets wijzigen', 'mag wel gewone tabellen wijzigen maar geen data-dictionary-gegevens' of 'mag alles' (dit laatste privilege is alleen voor de Databanken
62
database-administrator weggelegd); ✔ een overzicht van toepassingsprogramma's op de database; ✔ een overzicht van de opslagstructuur in het achtergrondgeheugen waarin staat welke delen van de schijf bezet zijn door welke tabellen, door welke gebruikers, en dergelijke. Sommige pakketten bieden dit alles en zelfs meer, andere bieden slechts een klein deel ervan. Zoals de gebruikersgegevens moeten ook de metagegevens vastgelegd worden en op de harddisk opgeslagen worden. In principe kunnen metagegevens dus net zo behandeld worden als gewone gegevens. Bij een relationele database betekent dit dat ook de metagegevens in tabellen en kolommen gestructureerd zijn en met de normale vraagtaal (SQL) benaderd kunnen worden. Men spreekt dan van een geïntegreerde data-dictionary. In PC-pakketten of oudere databasepakketten is de data-dictionary veelal niet geïntegreerd.
5.6.2. Metadata in uitgebreidere zin Metadata betekent 'data over data'. Alle gegevens in de data-dictionary zijn metadata. De laatste tijd is het woord metadata echter een iets andere betekenis gaan krijgen bij beheerders en gebruikers van onderzoeksgegevens. Men bedoelt er nu mee: allerlei gegevens over herkomst, aard en kwaliteit van gegevens. Metadata in die betekenis heeft betrekking op de externe integriteit van gegevens, zoals we die in hoofdstuk 2 hebben gedefinieerd. Het bijhouden van dit soort metadata is een nuttig hulpmiddel voor gebruikers die willen weten hoe bruikbaar de gegevens voor hen zijn. Soms spreekt men meer over een repository wanneer men dit soort metagegevens bedoelt. Van elke gegevensverzameling kan men dergelijke metadata als volgt indelen. ✔ Identificatie: Wie is de maker van de dataset? Wie is de contactpersoon? Met welk doel zijn de gegevens verzameld? Welke tijdsperiode bestrijken ze? Waar zijn ze te vinden? Welke naam dragen ze? In welke vorm zijn ze beschikbaar? ✔ Gegevenskwaliteit: Voor welke doelen zijn de gegevens bruikbaar? Hoe precies zijn ze? Hoe betrouwbaar? Uit welke bronnen zijn ze verkregen? Wat is de geldigheidsduur? ✔ Gegevenstype: Dit betreft de domeinen waaruit de gegevens putten. Men kan dit specialiseren afhankelijk van de discipline waartoe de gegevens behoren. ✔ Gegevensmodel: Dit is - zoals we weten - de structuur van de gegevens in termen van entiteittypen, attributen en verbanden. ✔ Distributie: Hoe en waar en onder welke voorwaarden zijn de gegevens te verkrijgen? Zijn de gegevens gepubliceerd? Zo ja, onder welke naam? ✔ Metadata referentie-info: De metadata kennen hun meta-metadata: wie heeft de metadata opgesteld en wanneer zijn ze bijgewerkt?
5.6.3. Metadefinitie en datamanipulatie Vanouds maakt men onderscheid tussen talen voor het behandelen van gewone gegevens – datamanipulatietalen - en talen voor het behandelen van metagegevens datadefinitietalen. SQL verenigt beide functies in één taal, wat uiteraard gemakkelijk is voor de administrator of de gebruikers die toegang hebben tot de metadata. Een geïntegreerde data-dictionary zou SQL insert-opdrachten kunnen toelaten om nieuwe metagegevens toe te voegen aan een data-dictionary-tabel. Dit zou echter een hachelijke Databanken
63
onderneming worden omdat de gebruiker dan precies moet bijhouden in welke datadictionary-bestanden hij wijzigingen moet aanbrengen. Zo zijn er voor het toevoegen van een 'gewone' tabel veranderingen in diverse data-dictionary-tabellen nodig: de tabel met alle tabellen, de tabel met alle kolommen, de tabel met alle privileges, enzovoort. SQL voorziet daarom een set specifieke datadefinitie commando's zoals bijvoorbeelde de opdracht create. Dit commando is veel veiligeren handiger voor de gebruiker. Met create table kan de gebruiker in één opdracht alle noodzakelijke wijzigingen in de data-dictionary aanbrengen die nodig zijn voor de aanmaak van één nieuwe tabel. Bij een geïntegreerde data-dictionary verschillen gewone gegevens en metagegevens dus niet in de manier van opvragen, maar wel in de manier van toevoegen. Datamanipulatie-opdrachten die we al kennen zijn select en insert. De tegenhanger van insert is delete. Datadefinitie-opdrachten zijn create en drop voor het aanmaken respectievelijk verwijderen van bijvoorbeeld tabellen, grant en revoke voor het toekennen en herroepen van privileges. Alleen de opdracht select behoort tot zowel de datamanipulatie- als de datadefinitietaal. De tabel hieronder toont hoe de vier databasebewerkingen (opvragen, toevoegen, wijzigen en verwijderen) in SQL heten bij gewone en bij metagegevens. opdracht
gewone gegevens
metagegevens
opvragen
select
select
toevoegen
insert
create - grant
wijzigen
update
alter
verwijderen
delete
drop
5.7. OPGAVEN 1. Zou het mogelijk zijn om een tabel met zichzelf te joinen? Zou het zin kunnen hebben? 2. Als een aantal transacties tegelijk dezelfde tabel bevragen maar niet wijzigen, is er geen lock nodig. Indien één van deze transacties ook wijzigingen aanbrengt, is er dan wel een lock nodig op de tabel? Waarom? 3. Leg uit waarom redundantie voor een DBMS noodzakelijk is voor recovery. Is het eenzelfde soort redundantie als de redundantie waar we in hoofdstuk 4 van spraken? Wat is het verschil?
Databanken
64
6. STRUCTURED QUERY LANGUAGE VRAAGTAAL Om te communiceren met een database hebben we een taal nodig. SQL is een internationale standaard taal voor die communicatie. De standaard is gebasseerd op een origneel IBM concept. Sinds het ontstaan van SQL kunnen er een aantal grote mijlpalen onderscheiden worden: SQL89 (ook wel SQL1 genoemd, gepubliceerd in 1989), SQL92 (SQL2, 1992) en SQL99 (SQL3, publicatie vanaf 1999). De laatste versie ervan is SQL:2008. Het is een standaard maar tegelijkertijd moeten we vaststellen dat niet alle DBMS de standaard volledig volgen. Bovendien kennen de meeste DBMS uitbreidingen op de standaard. Het is onmogelijk een overzicht te geven van alle mogelijkheden van alle verschillende SQL implementaties. We beperken ons tot die delen van de taal die in elk behoorlijk DBMS zouden moeten voorkomen. SQL kan structuren creëren, wijzigen en verwijderen. Het is dus een datadefinitietaal. SQL kan ook data toevoegen aan, wijzigen en verwijderen uit structuren. Het is dus ook een datamanipulatietaal. SQL kan bovendien ook gegevens opvragen en weergeven uit structuren. Het is dus ook een vraagtaal. SQL is geen programmeertaal. In wat volgt zullen we op basis van een voorbeeld de belangrijkste SQL commando's bekijken uit de vraagtaal. In hoofdstuk 7 bekijken we het gedeelte datamanipulatietaal, en in hoofdstuk 8 het gedeelte datadefinitietaal. We gebruiken doorheen de hoofdstukken één voorbeeld. Het voorbeeld omvat twee tabellen: chauffeur en wagen, met respectievelijk de gegevens die je vindt in de onderstaande tabellen. De primary key van chauffeur is Naam, die van wagen is Nummerplaat. De primary key Naam wordt in de tabel wagen gebruikt als foreign key in Eigenaar. Naam
Geboortedatum
Jan Peeters
11 januari 1980
Tom Peeters
23 maart 1981
Tom Jansen
3 december 1986
David Davidse
13 juni 1983
Nummerplaat
Merk
Kleur
Prijs
Eigenaar
611 AAA
Ford
Rood
12000
Jan Peeters
111 BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
555 GHT
Fiat
Groen
6000
Tom Jansen
C04 BF5
Smart
Blauw
13000
De oefeningen achteraan de tekst zijn gebaseerd op een iets uitgebreider voorbeeld dat je vindt op pagina 85 en 86.
Databanken
65
6.1. DE MEEST EENVOUDIGE SELECT SYNTAX Met het commando SELECT kunnen één of meerdere rijen van een tabel gelezen worden. De syntax voor het meest eenvoudige SELECT statement is als volgt: SELECT column FROM tablename; Bovenstaande commando zou alle rijen van de tabel met naam tablename weergeven, maar enkel voor de gespecifiëerde kolom column. Indien je meer dan één kolom wil ophalen, kan je de verschillende namen van die kolommen naast elkaar zetten, gescheiden door een komma: SELECT column1, column2, column3 FROM tablename; Als ja alle kolommen van desbetreffende tabel wil weergeven, kan je dat als volgt doen: SELECT * FROM tablename; Indien we bijvoorbeeld alle gegevens van de tabel WAGEN willen weergeven, kunnen we dat doen met: SELECT * FROM wagen; We krijgen dan de ganse tabel WAGEN te zien zoals je ze hoger kan zien. Indien we enkel de nummerplaten willen weergeven, doen we het volgende: SELECT nummerplaat FROM wagen; We krijgen dan een lijst met alleen de nummerplaten van alle wagens. Om een lijst te krijgen met bijvoorbeeld kleur en eigenaar van alle wagens, gebruiken we: SELECT kleur, eigenaar FROM wagen; Je kan in SQL zoveel extra spaties of linefeeds toevoegen als je wenst, zonder dat dat iets verandert aan de betekenis van je SQL statement. Zoals je wellicht ook al zult gemerkt hebben heb ik in de bovenstaande statements hoofdletters gebruikt voor de door SQL gereserveerde woorden, en kleine letters voor de namen van de velden en tabellen. Nochtans heb ik in de database zelf de velden en tabellen met hoofdletters gedefiniëerd. Toch geeft dat geen enkel probleem omdat SQL hoofdletterongevoelig is. Volgende statements leveren dan ook allemaal hetzelfde resultaat: SELECT nummerplaat FROM wagen; SELECT NUMMERPLAAT FROM WAGEN; select NUMMERPLAAT from select nummerplaat from wagen;
WAGEN;
Elk SQL statement dient te worden afgesloten met een ;. Aan dat teken herkent SQL dat één statement is beëindigd en dat dus een volgende kan beginnen. In sommige interfaces kan je het weglaten omdat het interface zelf het zal toevoegen. Maar het is beter om er van meet af aan een goede gewoonte van te maken om de ; in te tikken. Soms kan het nuttig zijn commentaar aan je SQL statements toe te voegen. Het is een stuk tekst dat door de database niet mag geïnterpreteerd worden maar dat betekenis kan hebben voor de lezer. Je kan dat doen door gebruiken te maken van het teken --. SELECT kleur, eigenaar FROM wagen ; Databanken
-- neem velden kleur en eigenaar -- van tabel wagen
66
Dergelijk commentaar mag enkel in het SQL zelf voorkomen. Sommige interfaces zullen toelaten om commentaar te schrijven tussen twee SQL-statements. Maar dat is dan een kwaliteit van het interface en niet van SQL zelf. Zie bijvoorbeeld het verschil tussen FlameRobin en het met Firebird meegeleverde isql (op linux: isql-fb) programma.
6.2. GEGEVENS UIT EEN TABEL: FILTERS Alle rijen van een tabel weergeven kan soms nodig zijn, maar als we tabellen hebben met duizenden of zelfs miljoenen rijen gegevens, dan zou dergelijke query wel eens erg veel tijd in beslag kunnen nemen. In dergelijke gevallen zullen we bijna altijd met filters gaan werken: op die manier kunnen we dan specifiëren welke rijen van de tabel we precies willen weergeven. In SQL gebruiken we daartoe het gereserveerde woord WHERE. Dergelijk SQL statement zal er dus algemeen gesproken uitzien als: SELECT column FROM tablename WHERE rule; Het gedeelte rule dat volgt na het woord WHERE, is het gedeelte dat voor elke rij van de tabel wordt gecontroleerd. Op basis van die controle beslist SQL dan of desbetreffende rij ja dan neen in het weer te geven resultaat wordt opgenomen. Als de gehele rule waar is wordt de rij opgenomen, indien ze onwaar is niet. Het gedeelte rule kan erg ingewikkeld zijn. De eenvoudigste rule is één enkele gelijkheidstest, zoals bijvoorbeeld in het volgende statement: SELECT nummerplaat FROM wagen WHERE kleur='Rood'; waarmee we het volgende resultaat krijgen: Nummerplaat 611 AAA
Merk vooreerst op dat om op tekstvelden te zoeken de tekst tussen enkele aanhalingstekens dient te staan. Bovendien zijn zoekopdrachten zoals bovenstaande, waar op een tekst wordt gezocht, wél hoofdlettergevoelig, itt de SQL syntax zelf. Indien we in bovenstaand statement rood zouden hebben gebruikt, zouden we een lege lijst gekregen hebben. Het veld waarop gezocht wordt tenslotte dient niet voor te komen in de weer te geven velden. In bovenstaand voorbeeld filteren we op kleur maar tonen we enkel het nummerplaat veld. Niets belet echter om ook het veld kleur te tonen met: SELECT nummerplaat, kleur FROM wagen WHERE kleur='Rood'; Nummerplaat
Kleur
611 AAA
Rood
6.2.1. Eenvoudige vergelijkingsoperatoren SQL ondersteunt de meeste vergelijkingsoperatoren voor gebruik in een WHERE clausule. Alle volgende operatoren kunnen gebruikt worden: = != <> Databanken
WHERE kleur='Rood' WHERE kleur!='Rood' WHERE kleur='Rood'
kleur gelijk aan Rood kleur een andere kleur dan Rood hetzelfde als != 67
> >= < <=
WHERE WHERE WHERE WHERE
prijs groter dan 10000 prijs groter dan of gelijk aan 10000 prijs kleiner dan 10000 prijs kleiner dan of gelijk aan 10000
prijs>10000 prijs>=10000 prijs<10000 prijs<=10000
Voor getallen dienen geen aanhalingstekens gebruikt te worden. Het mag wel. Datums verdienen wat meer uitleg. Wanneer datums worden vergeleken heeft groter dan de betekenis van "meer in de toekomst" en kleiner dan "meer in het verleden". Alle operatoren werken dus ook met datums. Bovendien verdient de syntax van een datum wat meer aandacht aangezien de manier waarop een datum wordt weergegeven kan variëren naargelang de database. Wat in de meeste databases werkt is het volgende: SELECT * FROM chauffeur WHERE geboortedatum='11 Jan 1980'; Bovenstaande syntax verdient de voorkeur. In Firebird werken ook de volgende uitdrukkingen. Merk op dat maand en dag van plaats wisselen naargelang de syntax: SELECT SELECT SELECT SELECT SELECT SELECT ...
* * * * * *
FROM FROM FROM FROM FROM FROM
chauffeur chauffeur chauffeur chauffeur chauffeur chauffeur
WHERE WHERE WHERE WHERE WHERE WHERE
geboortedatum='11 Jan 80'; geboortedatum='11-Jan-80'; geboortedatum='11-Jan-1980'; geboortedatum='01-11-80'; geboortedatum='01/11/1980'; geboortedatum='11.01.80';
Let ook op om een jaartal met slechts twee cijfers in te voeren. In Firebird werkt bovenstaande goed omdat het voor de bepaling van het jaartal vertrekt van de huidige datum en kijkt welke er het dichtst bijligt. Aangezien 1980 dichter bij 2005 ligt dan 2080, werkt de uitdrukking hier correct. In sommige andere databases wordt echter de huidige datum als vast referentiepunt genomen om te bepalen in welke eeuw een datum valt. Alle datums met twee cijfers vallen momenteel dan in de 21e eeuw. Het verdient daarom aanbeveling om steeds met 4 cijfers te werken voor het jaartal.
6.2.2. Meer geavanceerde operatoren Met BETWEEN kan je een range opgeven waarbinnen een getal of een datum moet vallen. Onderstaande uitdrukkingen geven alle wagens waarvan de prijs tussen 13000 en 22000 ligt; of alle chauffeurs met een geboortedatum tussen 23 maart 1981 en 23 april 1981. SELECT * FROM wagen WHERE prijs BETWEEN 13000 AND 22000; SELECT * FROM chauffeur WHERE geboortedatum BETWEEN '23 Mar 1981' AND '23 Apr 1981'; Merk op dat de twee opgegeven waarden als geldig beschouwd worden: je zou ze dus kunnen schrijven als een >= én <=. De NULL waarde duidt erop dat iets geen echte waarde heeft. Gewone operatoren zullen dan ook falen indien ze gebruikt worden voor dergelijke waarde. Om te testen op NULL waarden heeft SQL de speciale operator IS. Volgende commando haalt alle rijen uit de tabel wagen waar de eigenaar respectievelijk NULL of niet NULL is: SELECT * FROM wagen WHERE eigenaar IS NULL; SELECT * FROM wagen WHERE eigenaar IS NOT NULL; Wanneer je met strings werkt wil je soms testen op deelstrings in plaats van op de volledige waarde van een veld. Met de LIKE operator kan je dat bereiken. Zonder Databanken
68
speciale karakters te gebruiken is LIKE identiek aan de gelijkheidsoperator. Met LIKE kan je echter twee speciale karakers gebruiken: % en _. Ze komen overeen met de wildcard karakters * en ? uit DOS. Het _ karakter staat voor één karakter met willekeurige waarde. Het % karakter staat voor een willekeurig aantal willekeurige karakters. Om enkele voorbeelden te geven: naam naam naam naam naam naam naam
LIKE LIKE LIKE LIKE LIKE LIKE LIKE
'Tom Peeters' 'Tom P_eters' '___ Peeters' '% Peeters' '% P%' 'Tom %' '%'
geeft geeft geeft geeft geeft geeft geeft
enkel Tom Peeters Tom Peeters en Tom Pieters Tom Peeters, Jan Peeters,... Tom Peeters, Marc Peeters,... Tom Peeters, Elke Panda,... Tom Peeters, Tom Jansen,... alle namen behalve NULL
Als je bijvoorbeeld alle wagens wenst weer te geven waarvan het merk met een F begint, voer je de volgende opdracht uit: SELECT merk FROM wagen WHERE merk LIKE 'F%';
6.2.3. Logische operatoren Wanneer je meer dan één voorwaarde wil toevoegen aan een WHERE clause, gebruik je een logische operator. SQL kent de logische operatoren NOT, AND en OR. Men kan bovendien haakjes gebruiken in uitdrukkingen. Met AND kunnen verschillende uitdrukkingen aan elkaar gekoppeld worden, op zo'n manier dat alle uitdrukkingen waar moeten zijn om de gehele uitdrukking waar te maken. Stel dat we bijvoorbeeld alle blauwe wagens willen opzoeken die het cijfer 5 in de nummerplaat hebben. De onderstaande uitdrukking: SELECT * FROM wagen WHERE kleur='Blauw' AND nummerplaat LIKE '%5%';
geeft dan het gewenste resultaat: 155 BDE
Mercedes
Blauw
22000
C04 BF5
Smart
Blauw
13000
Tom Peeters
Indien we niet alleen de blauwe wagens zouden zoeken met een 5 in de nummerplaat, maar ook die met een 1, dan moeten we de OR operator gebruiken. De nummerplaat moet immers óf een 5 óf een 1 bevatten. De query SELECT * FROM wagen WHERE kleur='Blauw' AND (nummerplaat LIKE '%5%' OR nummerplaat LIKE '%1%');
geeft dan het volgende resultaat: 111BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
C04 BF5
Smart
Blauw
13000
Met de NOT operator kan je het tegengestelde bekomen van gelijk welke uitdrukking. Wanneer je bijvoorbeeld alle wagens wenst te bekomen die niet blauw zijn of geen 5 of 1 in hun nummerplaat hebben, kan je dat bekomen met: SELECT * FROM wagen WHERE NOT (kleur='Blauw' AND (nummerplaat LIKE '%5%' OR nummerplaat LIKE '%1%')); Databanken
69
Indien je daarentegen alle wagens zou willen die wél een 5 of 1 in de nummerplaat hebben maar niet blauw zijn, zou je dat bekomen met: SELECT * FROM wagen WHERE NOT kleur='Blauw' AND (nummerplaat LIKE '%5%' OR nummerplaat LIKE '%1%');
wat hetzelfde is als de eenvoudiger uitdrukking: SELECT * FROM wagen WHERE kleur!='Blauw' AND (nummerplaat LIKE '%5%' OR nummerplaat LIKE '%1%');
Zoals je al kon zien kan je door haakjes te gebruiken de volgorde van uitvoering van de operatoren opleggen. Indien je geen haakjes gebruikt worden de operatoren in deze volgorde uitgevoerd: NOT wordt eerst uitgevoerd, dan AND en vervolgens OR. Het spreekt voor zich dat we volledig foutieve resultaten kunnen verkrijgen indien we haakjes niet oordeelkundig gebruiken. De uitdrukking om alle blauwe wagens met een 5 of een 1 in het nummerplaat te vinden zou bijvoorbeeld foutief zijn indien we de haakjes zouden weglaten. De uitdrukking SELECT * FROM wagen WHERE kleur='Blauw' AND nummerplaat LIKE '%5%' OR nummerplaat LIKE '%1%';
levert immers het volgende foutieve resultaat op 611AAA
Ford
Rood
12000
Jan Peeters
111BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
C04 BF5
Smart
Blauw
13000
6.2.4. DISTINCT Stel dat je alle kleuren wil ophalen met dewelke er een wagen in de database zit. Je zou dat met het volgende statement kunnen bereiken SELECT kleur FROM wagen; Op die manier krijg je inderdaad alle kleuren van alle wagens. Maar dat betekent voor onze concrete gegevens dat we drie keer de kleur Blauw krijgen terwijl we die eigenlijk maar één keer willen hebben. Dat kunnen we vermijden door het woord DISTINCT te gebruiken. Dit woord wordt onmiddellijk na SELECT geplaatst en het zorgt ervoor dat wanneer de opvraging twee of meerdere keren identiek hetzelfde resultaat oplevert, dat slechts éénmaal wordt weergegeven. Met volgende opvraging zouden we inderdaad het correcte resultaat krijgen: SELECT DISTINCT kleur FROM wagen; Blauw Groen Rood
Opgelet: DISTINCT geldt voor de voilledige combinatie van geselecteerde velden. De uitdrukking hieronder levert in het resultaat dan ook nog steeds drie keer de kleur blauw. SELECT DISTINCT kleur, merk FROM wagen;
Databanken
70
6.2.5. IN Met IN kan je nagaan of een waarde in een lijst van waardes zit. We kunnen bijvoorbeeld alle wagens van het merk Skoda en van het merk Smart bekomen met: SELECT * FROM wagen WHERE merk='Skoda' OR merk='Smart'; wat we ook kunnen schrijven als: SELECT * FROM wagen WHERE merk IN ('Skoda','Smart');
6.2.6. CONTAINING Met CONTAINS kan je resultaten bekomen die je ook met LIKE zou kunnen bereiken. Zo leveren de volgende twee uitdrukkingen hetzelfde resultaat: SELECT * FROM wagen WHERE merk LIKE '%od%'; SELECT * FROM wagen WHERE merk CONTAINING 'od';
6.3. SORTEREN, RANGSCHIKKEN VAN GEGEVENS Wanneer een SQL commando wordt uitgevoerd is de volgorde waarin de gegevens worden weergegeven niet altijd gemakkelijk voorspelbaar. Die volgorde is immers afhankelijk van de manier waarop de database werd geschreven, meer bepaald van de manier waarop de query optimizer werkt. In sommige gevallen is dat geen probleem, maar soms zou het handig zijn indien we de gegevens in een volgorde kunnen weergeven die ze gemakkelijker leesbaar maakt (bijvoorbeeld alfabetisch). Dat kan bereikt worden met de clausule ORDER BY. Indien we bijvoorbeeld alle merken van wagens alfabetisch willen rangschikken, kunnen we dat als volgt doen: SELECT merk FROM wagen ORDER BY merk; Een identieke uitdrukking is de volgende, waarbij ASC staat voor ascending. SELECT merk FROM wagen ORDER BY merk ASC; Om de tabel in de omgekeerde volgorde te bekomen gebruiken we de afkorting DESC voor decending. SELECT merk FROM wagen ORDER BY merk DESC; In de ORDER BY clausule kan bovendien meer dan één kolom gespecifiëerd worden. Indien we bijvoorbeeld alle wagen willen sorteren, oplopend volgens kleur en daarna aflopend volgens merk, doen we dat met: SELECT * FROM wagen ORDER BY kleur ASC, merk DESC;
Databanken
C04 BF5
Smart
Blauw
13000
111 BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
555 GHT
Fiat
Groen
6000
Tom Jansen
611 AAA
Ford
Rood
12000
Jan Peeters
71
6.4. AGGREGAATSFUNCTIES EN GROEPERING In SQL bestaan er ook operatoren die resultaten geven die gebaseerd zijn op statistieken van een groep van waarden in een veld. Zo bestaan er onder meer de operatoren "geef het maximum van een aantal waarden" of "geef de gemiddelde waarde van een aantal waarden". Zulke functies worden aggregaatsfuncties genoemd. In combinatie met de GROUP BY en de HAVING clausules kan men met deze functies op eens snelle manier overzichtgegevens uit de database halen.
6.4.1. De aggregaatsfuncties Met de functie AVG (van average) bereken je het gemiddele van een aantal veldwaarden. Indien je bijvoorbeeld de gemiddelde prijs wenst te bepalen van alle wagens in de database, dan doe doe je dat met: SELECT AVG(prijs) FROM wagen; AVG 12800
De gemiddelde prijs van enkel de blauwe wagens bepaal je op dezelfde manier met: SELECT AVG(prijs) FROM wagen WHERE kleur='Blauw'; AVG 15333
Met SUM bereken je de som van alle veldwaarden. SELECT SUM(prijs) FROM wagen WHERE kleur='Blauw'; SUM 46000
De functies MAX en MIN halen respectievelijk de maximale en de minimale waarde uit een reeks van veldwaarden. SELECT MAX(prijs), MIN(prijs) FROM wagen; MAX
MIN
22000
6000
Met COUNT bepaal je hoeveel rijen een bepaald query resultaat telt met een waarde die niet gelijk is aan NULL. Om bijvoorbeeld het aantal rijen in prijs te tellen gebruik je: SELECT COUNT(prijs) FROM wagen; COUNT 5
Met onderstaande query bepaal je dat aantal voor de rij Eigenaar SELECT COUNT(eigenaar) FROM wagen; COUNT 4 Databanken
72
Wil je het aantal rijen in een tabel bepalen onafgezien van het aantal NULL waarden: SELECT COUNT(*) FROM wagen; Met COUNT DISTINCT tenslotte tel je niet het aantal niet NULL waarden in een veld, maar het aantal unieke veldwaarden. De syntax is enigszins speciaal: SELECT COUNT(DISTINCT kleur) FROM wagen; COUNT 3
Op dezelfde manier levert onderstaande query hetzelfde resultaat op, wat aantoont dat ook hier NULL waarden niet als unieke waarde worden meegeteld. SELECT COUNT(DISTINCT eigenaar) FROM wagen; COUNT 3
6.4.2. De GROUP BY clausule Wanneer je statistieken wil bepalen voor groepen, ontbreekt er iets met alleen de aggregaatsfuncties. Indien je bijvoorbeeld de maximale kost van een wagen per kleur wil bepalen, zou je geneigd zijn te denken dat dat kan met onderstaande vraag SELECT kleur, MAX(prijs) FROM wagen; Jammer genoeg geeft deze query de error "Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause". Je dient inderdaad voor alle aggregaatsfuncties in de query te bekijken over welke kolommen ze zullen gegroepeerd worden. In ons geval wordt de functie MAX gegroepeerd over kleur. Om dat duidelijk te maken dien je GROUP BY te gebruiken als volgt SELECT kleur, MAX(prijs) FROM wagen GROUP BY kleur; wat het correcte resultaat oplevert Kleur
MAX
Rood
12000
Blauw
22000
Groen
6000
In de meeste gevallen betekent dat dat je alle velden in het SELECT statement waarop geen aggregaatsfunctie gebruikt wordt, gebruikt in de GROUP BY clausule. Ook volgende query wordt echter zonder problemen uitgevoerd met hetzelfde resultaat (zonder kleur): SELECT MAX(prijs) FROM wagen GROUP BY kleur;
6.4.3. De HAVING clausule Aggregaatsfuncties worden door de database bijna altijd op het einde van het query proces uitgevoerd. Daarom is het eerder moeilijk om iets als "welke eigenaars hebben meer dan één wagen" te bepalen. We zouden wellicht geneigd zijn iets als SELECT eigenaar FROM wagen WHERE COUNT(eigenaar)>1; Databanken
73
te schrijven, maar dan krijgen we de foutmelding "cannot use an aggregate function in a WHERE clause, use HAVING instead". HAVING werkt inderdaad op een identieke manier als WHERE, maar met het grote verschil dat het zeer laat in het query proces wordt opgeroepen, en dat in tegenstelling tot WHERE dat zeer vroeg in het query proces wordt opgeroepen. Volgende twee queries zouden beide onze vraag beantwoorden SELECT eigenaar FROM wagen GROUP BY eigenaar HAVING COUNT(eigenaar)>1; SELECT eigenaar, COUNT(eigenaar) FROM wagen GROUP BY eigenaar HAVING COUNT(eigenaar)>1; De tweede query geeft als extra veld het aantal wagens dat de eigenaar heeft (dus steeds groter dan 1 in dit geval): Eigenaar
COUNT
Jan Peeters
2
6.5. GEGEVENS UIT MEERDERE TABELLEN Queries die gegevens ophalen uit meer dan één tabel zijn perfect mogelijk in SQL, maar vragen enige aandacht omdat het erg gemakkelijk is zulke queries te schrijven die uitsluitend onzin produceren. Het basisconcept is nochtans eenvoudig: indien je gegevens uit meer dan één tabel wil halen, moeten al deze tabellen vermeld worden in de FROM clausule. Als we bijvoorbeeld voor elke wagen in de database de eigenaar willen kennen en zijn geboortedatum zou de volgende query dergelijk resultaat opleveren: SELECT nummerplaat,naam,geboortedatum FROM wagen,chauffeur; Het resultaat van deze query is echter foutief omdat de database niet weet hoe het de twee tabellen aan elkaar moet koppelen. Bovenstaande query koppelt gewoon elke rij uit wagen met elke rij uit chauffeur. Het is duidelijk dat dat niet is wat we wensen. We zijn immers enkel geïnteresseerd in die combinaties van rijen waar de eigenaar in tabel wagen hetzelfde is als de naam in tabel chauffeur. Als we dat zeggen in termen die we reeds kennen: WHERE eigenaar=naam. Of met nog andere woorden: enkel in die rijen die beantwoorden aan de FOREIGN KEY regel die we hadden opgelegd. Belangrijke opmerking: Indien sommige van de tabellen in de query gelijke veldnamen bevatten, kan steeds naar het correcte veld verwezen worden door de veldnaam te laten voorafgaan door de tabelnaam en een punt. De volgende query SELECT wagen.nummerplaat, chauffeur.naam, chauffeur.geboortedatum FROM wagen,chauffeur; is dus hetzelfde als de hoger vermeldde query.
6.5.1. Traditionele JOIN Om de correcte oplossing te vinden - voor elke wagen in de database de eigenaar en zijn geboortedatum - moeten we een join gebruiken, meer bepaald een INNER JOIN. Dergelijke join kan op twee manieren geschreven worden: de traditionele manier en de moderne manier. De laatste is aan te bevelen; ze wordt aangeraden in de nieuwere SQL Databanken
74
standaarden. In de eerste SQL standaard werd de traditionele manier gebruikt. Men mag ze ook nu nog gebruiken om redenen van compatibiliteit. Om ons correcte resultaat te verkrijgen volgens die traditioinele manier, schrijven we: SELECT nummerplaat,naam,geboortedatum FROM wagen,chauffeur WHERE eigenaar=naam; Zoals je kan zien levert deze query inderdaad het correcte resultaat op: Nummerplaat
Naam
Geboortedatum
611 AAA
Jan Peeters
11.01.1980
111 BBB
Jan Peeters
11.01.1980
155 BDE
Tom Peeters
23.03.1981
555 GHT
Tom Jansen
03.12.1986
6.5.2. Moderne JOIN Om hetzelfde resultaat te bekomen op de moderne manier, dien je een van de twee volgende statements te gebruiken: SELECT nummerplaat,naam,geboortedatum FROM wagen JOIN chauffeur ON eigenaar=naam; SELECT nummerplaat,naam,geboortedatum FROM wagen INNER JOIN chauffeur ON eigenaar=naam; Bovenstaande queries leveren exact hetzelfde resultaat op als de traditionele join schrijfwijze.
6.5.3. OUTER JOIN Zoals je in het bovenstaande voorbeeld kon opmerken, werden bij de join de rijen waarbij eigenaar een NULL-waarde had, niet weergegeven. Meestal is dat wat gewenst wordt, maar soms kan het nodig zijn dat ook dergelijke rijen weergegeven worden. Dat kan bereikt worden met een outer join. In modern SQL spreken we dan van LEFT JOIN en RIGHT JOIN. Het eerste wordt gebruikt als van de tabel links van LEFT JOIN alle waarden dienen te worden weergegeven, ook indien er geen corresponderende rij is in de tabel aan de rechterkant. RIGHT JOIN wordt gebruikt als van de tabel die rechts van die uitdrukking staat alle waarden dienen te worden weergegeven, ook idien er geen corresponderende rij is in de tabel aan de linkerkant. Volgende query levert dus het bedoelde resultaat: SELECT nummerplaat,naam,geboortedatum FROM wagen LEFT JOIN chauffeur ON eigenaar=naam; Nummerplaat
Naam
Geboortedatum
611 AAA
Jan Peeters
11.01.1980
111 BBB
Jan Peeters
11.01.1980
155 BDE
Tom Peeters
23.03.1981
555 GHT
Tom Jansen
03.12.1986
C04 BF5
Databanken
75
Zoals je ziet in het resultaat worden alle gegevens waar geen corresponderende rij voor gevonden wordt, opgevuld met NULL-waarden. Eenzelfde resultaat zouden we krijgen met de volgende query SELECT nummerplaat,naam,geboortedatum FROM chauffeur RIGHT JOIN wagen ON naam=eigenaar;
6.5.4. FULL OUTER JOIN Indien we nu alle rijen willen weergeven waarvoor geen corresponderende rij gevonden wordt, en dat voor zowel de tabel aan de rechter- als aan de linkerant, dan gebruiken we een FULL OUTER JOIN kortweg ook FULL JOIN genoemd. Met de FULL OUTER JOIN op wagen en chaufeur krijgen we volgend resultaat: SELECT nummerplaat,naam,geboortedatum FROM wagen FULL JOIN chauffeur ON eigenaar=naam; Nummerplaat
Naam
Geboortedatum
611 AAA
Jan Peeters
11.01.1980
111 BBB
Jan Peeters
11.01.1980
155 BDE
Tom Peeters
23.03.1981
555 GHT
Tom Jansen
03.12.1986
David Davidse
13.06.1983
C04 BF5
6.5.5. Aliassen Soms kunnen join expressies erg lang worden, wanneer er bijvoorbeeld veel tabellen gekoppeld worden en als die tabellen ook nog eens lange namen hebben. SQL laat je dan toe om het intikken van de join te vereenvoudigen door gebruik te maken van aliassen. De tabel krijgt dan een andere naam - een alias - voor de duurtijd van de query. In plaats van SELECT wagen.eigenaar FROM wagen WHERE wagen.kleur='Rood'; kunnen we dan schrijven wat volgt. Beide leveren hetzelfde resultaat op. SELECT c.eigenaar FROM wagen c WHERE c.kleur='Rood'; Eigenaar Jan Peeters
Aliassen kunnen ook gebruikt worden bij veldnamen. Volgende query geeft bijvoorbeeld hetzelfde resultaat als de hoger vermelde, maar de naam van het veld wordt renner in plaats van eigenaar: SELECT c.eigenaar renner FROM wagen c WHERE c.kleur='Rood'; Renner Jan Peeters
Databanken
76
6.5.6. Self joins Self-joins of equijoins zijn joins waarbij we dezelfde tabel meer dan één keer willen gebruiken, maar elke keer met een ander doel. Stel dat we bijvoorbeeld willen weten welke chauffeurs met een wagen rijden die dezelfde kleur heeft als Tom Peeters. De eigenaars van dergelijke wagens zouden we kunnen vinden door eerst de kleur van die wagen te bepalen met SELECT kleur FROM wagen WHERE eigenaar='Tom Peeters'; Kleur Blauw
en daarna op basis van die kleur te de volgende query te gebruiken: SELECT eigenaar FROM wagen WHERE kleur='Blauw' AND eigenaar!='Tom Peeters' AND eigenaar IS NOT NULL; Eigenaar Jan Peeters
Met een JOIN kunnen we dat in één query schrijven als volgt: SELECT b.eigenaar FROM wagen a JOIN wagen b ON a.kleur=b.kleur WHERE a.eigenaar='Tom Peeters' AND a.eigenaar<>b.eigenaar AND b.eigenaar IS NOT NULL;
6.6. VIEWS Een VIEW is een afgeleide tabel. Soms wanneer je langere queries schrijft, kan het voorkomen dat dezelfde rules meer dan één keer geschreven worden in dezelfde query. In programmeertalen zou je in dergelijk geval met een subroutine werken. In SQL kan je daarvoor een VIEW gebruiken. Een VIEW kan gecreëerd worden in een database, en elke VIEW representeert één enkele SQL query. Eens het VIEW aangemaakt is, kan het gebruikt worden als een andere tabel die werkelijk zou bestaan, zonder dat je nog dient te weten op welke query het VIEW gebaseerd is. De inhoud van het VIEW blijft wel altijd identiek aan het resultaat dat zou bekomen worden door de achterliggende query uit te voeren. Stel dat we een query wensen waarmee we wensen te bepalen hoeveel chauffeurs en hoeveel wagens er zijn in de database. Je zou daartoe de twee volgende queries moeten uitvoeren: SELECT COUNT(*)FROM chauffeur; SELECT COUNT(*) FROM wagen; Als we beide queries nu in een VIEW stoppen met CREATE VIEW cnt1 (chauffeurs) AS SELECT COUNT(*)FROM chauffeur; CREATE VIEW cnt2 (wagens) AS SELECT COUNT(*) FROM wagen; dan verkrijgen we met de volgende query het gewenste resultaat
Databanken
77
SELECT chauffeurs,wagens FROM cnt1,cnt2; Kleur Blauw
Eens je het VIEW niet meer nodig hebt kan je het verwijderen met: DROP VIEW cnt DROP VIEW cnt2;
6.7. SUBQUERIES EN VERZAMELBEWERKINGEN Eén select kan ingenest worden in een ander, zodat bijvoorbeeld het resulaat van de geneste query kan gebruikt worden in de WHERE clausule van de hoofdquery. We noemen zulke queries ingeneste queries of subqueries. De meeste subqueries zijn beperkt omdat ze slechts één attribuut als resultaat mogen hebben, wat betekent dat ze slechts één attribuut in de SELECT clausule mogen hebben. Sommlige databanken bieden ook subqueries met meerdere attributen aan..Subqueries kunnen vaak gebruikt worden in de plaats van een self-join of een view. Ze zijn vaak beter te begrijpen. Stel dat we willen weten wie in de database jonger is dan Jan Peeters. Met twee achtereenvolgende queries zouden we eerst de geboortedatum kunnen bepalen van Jan Peeters, om daarna de chauffeurs te bepalen meet een geboortedatum groter dan die geboortedatum: SELECT geboortedatum FROM chauffeur WHERE naam='Jan Peeters'; SELECT naam FROM chauffeur WHERE geboortedatum>'11 Jan 1980'; In één query zouden we dat kunnen schrijven als: SELECT naam FROM chauffeur WHERE geboortedatum > (SELECT geboortedatum FROM chauffeur WHERE naam='Jan Peeters'); Naam Tom Peeters Tom Jansen
Dat werkt inderdaad correct. Er ontstaat echter een probleem wanneer de subquery meer dan één resultaat zou leveren, wat het geval zou zijn indien er twee chauffeurs zouden zijn met de naam Jan Peeters.
6.7.1. ANY en ALL Met ANY en ALL kunnen we werken met subqueries die meer dan één rij als resultaat leveren. Met ANY moet er minstens één rij zijn die voldoet aan de clausule, om de clausule als waar te evalueren. Met ALL moeten alle rijen voldoen aan de clausule om ze als waar te evalueren. Stel: we willen weten welke wagens dezelfde kleur hebben als de wagen van Jan Peeters. Jan Peeters bezit echter twee wagens, de ene blauw en de andere rood. We willen dus weten welke wagens blauw óf rood zijn. Met een subquery kunnen we dat als volgt Databanken
78
schrijven: SELECT * FROM wagen WHERE kleur = ANY (SELECT kleur FROM wagen WHERE eigenaar='Jan Peeters'); wat inderdaad het goede resultaat levert: Nummerplaat
Merk
Kleur
Prijs
Eigenaar
611 AAA
Ford
Rood
12000
Jan Peeters
111 BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
C04 BF5
Smart
Blauw
13000
Indien we alle wagens willen weergeven die een prijs hebben die hoger is dan alle wagens van Jan Peeters, doen we dat als volgt: SELECT * FROM wagen WHERE prijs > ALL (SELECT prijs FROM wagen WHERE eigenaar='Jan Peeters'); wat inderdaad het correcte resultaat oplevert: Nummerplaat
Merk
Kleur
Prijs
Eigenaar
155 BDE
Mercedes
Blauw
22000
Tom Peeters
C04 BF5
Smart
Blauw
13000
6.7.2. IN en NOT IN voor subqueries Zoals we hoger reeds zagen kan IN gebruikt met een uitdrukking zoals ('Blauw','Rood'). Op dezelde manier kan IN ook gebruikt worden op subqueries. Stel dat we bijvoorbeeld willen weten welke wagens dezelfde kleur hebben als één van Jan Peeters' wagens: SELECT * FROM wagen WHERE kleur IN (SELECT kleur FROM wagen WHERE eigenaar='Jan Peeters'); wat inderdaad het correcte resultaat oplevert: Nummerplaat
Merk
Kleur
Prijs
Eigenaar
611 AAA
Ford
Rood
12000
Jan Peeters
111 BBB
Skoda
Blauw
11000
Jan Peeters
155 BDE
Mercedes
Blauw
22000
Tom Peeters
C04 BF5
Smart
Blauw
13000
Of als we alle wagens willen hebben die niet één van die kleuren hebben, gebruiken we: SELECT * FROM wagen WHERE kleur NOT IN (SELECT kleur FROM wagen WHERE eigenaar='Jan Peeters');
Databanken
Nummerplaat
Merk
Kleur
Prijs
Eigenaar
555 GHT
Fiat
Groen
6000
Tom Jansen
79
6.7.3. EXISTS In bijna alle gevallen wanneer je een unieke waarde van iets zoekt, kan je het vinden door gebruik te maken van EXISTS of NOT EXISTS. De EXISTS operator levert waar op als de subquery waarop hij wordt toegepast minstens één rij geeft, en onwaar als de subquery geen rij geeft. NOT EXISTS doet het tegenovergestelde. Stel we willen alle kleuren hebben die slechts éénmaal gebruikt zijn in de database: SELECT kleur FROM wagen a WHERE NOT EXISTS (SELECT kleur FROM wagen b WHERE a.kleur = b.kleur AND a.nummerplaat <> b.nummerplaat); Kleur Rood Groen
We kunnen inderdaad de tabel uit de hoofdquery gebruiken in de subquery want wat er gebeurt is het volgende: 1. de query neemt de eerst rij van tabel a; 2. de subquery wordt uitgevoerd, en er wordt besloten of de kleur voldoet aan de voorwaarde; 3. de query neemt de tweede rij van tabel a; 4. ... Als we alle eigenaars willen die meer dan één wagen bezitten kunnen we dat doen met: SELECT DISTINCT eigenaar FROM wagen a WHERE EXISTS (SELECT eigenaar FROM wagen b WHERE a.eigenaar = b.eigenaar AND a.nummerplaat <> b.nummerplaat); Eigenaar Jan Peeters
6.7.4. Verzamelbewerkingen: UNION Soms is het nuttig om de resultaten van twee queries samen te voegen tot één resultaat. Dit noemen we een UNION. Een UNION kan alleen werken als elke query die wordt samengevoegd hetzelfde aantal kolommen heeft, die van hetzelfde type zijn. Stel dat we alle chauffeurs willen weergeven met het aantal wagens dat ze bezitten. Met de query SELECT naam,COUNT(*) FROM chauffeur JOIN wagen ON naam=eigenaar GROUP BY naam; hebben we alle chauffeurs met hun aantal wagen behalve de chauffers die géén wagen bezitten. Met de query SELECT naam,0 FROM chauffeur WHERE naam NOT IN (SELECT eigenaar FROM wagen WHERE eigenaar IS NOT NULL); hebben we ook die chauffeurs te pakken zodat de query SELECT naam,COUNT(*) FROM chauffeur JOIN wagen ON naam=eigenaar GROUP BY naam UNION Databanken
80
SELECT naam,0 FROM chauffeur WHERE naam NOT IN (SELECT eigenaar FROM wagen WHERE eigenaar IS NOT NULL); het correcte resultaat oplevert: Naam
COUNT
David Davidse
0
Jan Peeters
2
Tom Jansen
1
Tom Peeters
1
6.8. OPGAVEN Voor de opgaven gebruiken we de COMPANY database op de bladzijden 83 en 84. 1. Geef de namen van de dependents. 2. Geef naam en locatie van de projecten. 3. Geef FNAME en LNAME van alle mannen in departement nr 4. 4. Geef namen (alfabetisch) van alle mannen met een salaris groter dan 30000. 5. Geef SSN van alle personen geboren na 1 januari 1978 of met een salaris hoger dan 40000, gerangschikt op salaris van hoog naar laag. 6. Geef de namen (alfabetisch) van alle dependents die echtgenoot zijn. 7. Geef de namen van alle dependents die zoon of dochter zijn. 8. Geef de geboortedatum van de employee John Smith. 9. Geef de namen van de personen die geen supervisor hebben. 10. Geef namen (alfabetisch) van de dependents van employee Wong. 11. Geef de naam van de manager van het departement Administration. 12. Geef de namen van de employees die werken voor het departement research of headquarters, gerangschikt volgens geboortedatum (oudste bovenaan). 13. Geef de namen van projecten gecontroleerd door het departement Administration. 14. Geef de namen van de employees die werken op het project Newbenefits. 15. Geef de namen van de vrouwelijke employees in het departement Research. 16. Geef de supervisor van de employee Jabbar.
Databanken
81
17. Geef de namen van de employees met als supervisor James Borg. 18. Geef alle employees van het departement research of headquarters. 19. Geef de employees die werken op ProductX én op ProductY. 20. Geef de namen van de employees die op geen enkel project werken. 21. Geef de namen van de managers met minstens één dependent. 22. Geef het minimum, het maximum en het gemiddelde salaris van alle employees. 23. Geef het minimum, het maximum en het gemiddelde salaris van alle employees van elk departement. 24. Geef het gemiddelde salaris van de employees die werken op ProductX. 25. Geef het aantal verschillende salarissen dat wordt uitbetaald. 26. Geef het totaal aantal uren gewerkt aan project met nummer 3. 27. Geef het totaal aantal uren gewerkt door employee Smith. 28. Geef de namen van de employees met minstens twee dependents. 29. Geef per departement het aantal employees, de totale kost aan salaris en het gemiddelde salaris, gerangschikt van duurste naar goedkoopste departement. 30. Geef voor elk project het projectnummer, de projectnaam en het aantal personen dat voor het project werkt, gerangschikt volgens het aantal gewerkte uren per project. 31. Geef per geslacht het aantal employees en hun gemiddeld salaris. 32. Geef het aantal locaties per departement. 33. Geef het totaal aantal gewerkte uren per projectnummer. 34. Geef het totaal aantal uren per employee gerangschikt volgens geslacht en uren. 35. Geef het aantal dependents per employee. 36. Maak een view van alle employees die op een project werken. Het view bevat de namen van de employees, de projectnaam en het aantal gewerkte uren. 37. Maak een view met het aantal employees per department.
Databanken
82
FNAME
MI
LNAME
SSN
BDATE
ADDRESS
SEX SALARY SUPERSSN DNO
John
B
Smith
123456789
9/1/1975
731 Fondren, Houston, TX
M
30000
333445555
5
Franklin
T
Wong
333445555
8/12/1965
638 Voss, Houston, TX
M
40000
888665555
5
Alicia
J
Zelaya
999887777
19/7/1978
3321 Castle, Spring, TX
F
25000
987654321
4
Jennifer
S
Wallace
987654321
20/6/1951
291 Berry, Bellaire, TX
F
43000
888665555
4
Ramesh
K
Narayan
666884444
15/9/1972
975 Fire Oak, Humble, TX
M
38000
333445555
5
Joyce
A
English
453453453
31/7/1982
5631 Rice, Houston, TX
F
25000
333445555
5
Ahmed
V
Jabbar
987987987
29/3/1979
980 Dallas, Houston, TX
M
25000
987654321
4
James
E
Borg
888665555 10/11/1947
450 Stone, Houston, TX
M
55000
1
Tabel EMPLOYEE DNAME
DNUMBER
MGRSSN
MGRSTARTDATE
Research
5
333445555
22/5/1998
Administration
4
987654321
1/1/2005
Headquarters
1
888665555
19/6/1991
Tabel DEPARTMENT DNUMBER
DLOCATION
1
Houston
4
Stafford
5
Bellaire
5
Sugarland
5
Houston
Tabel DEPT_LOCATIONS ESSN
DEPENDENT_NAME
SEX
BDATE
RELATIONSHIP
333445555
Alice
F
5/4/1996
DAUGHTER
333445555
Theodore
M
25/10/1993
SON
333445555
Joy
F
3/5/1968
SPOUSE
987654321
Abner
M
29/2/1952
SPOUSE
123456789
Michael
M
1/1/1998
SON
123456789
Alice
F
31/12/1998
DAUGHTER
123456789
Elizabeth
F
5/5/1977
SPOUSE
Tabel DEPENDENT
Databanken
83
PNAME
PNUMBER
PLOCATION
DNUM
ProductX
1
Bellaire
5
ProductY
2
Sugarland
5
ProductZ
3
Houston
5
Computerization
10
Stafford
4
Reorganization
20
Houston
1
Newbenefits
30
Stafford
4
Tabel PROJECT PNO
HOURS
123456789
1
32.5
123456789
2
7.5
666884444
3
40.0
453453453
1
20.0
453453453
2
20.0
333445555
2
10.0
333445555
3
10.0
333445555
10
10.0
333445555
20
10.0
999887777
30
30.0
999887777
10
10.0
987987987
10
35.0
987987987
30
5.0
987654321
30
20.0
987654321
20
15.0
888665555
20
ESSN
Tabel WORKS_ON
Databanken
84
7. SQL MANIPULATIETAAL In dit hoofdstuk bekijken we het gedeelte datamanipulatietaal van SQL. We kunnen er data mee toevoegen, wijzigen en verwijderen uit een database. De basiscommando's zijn: ✔ INSERT voor het toevoegen van gegevens. ✔ DELETE voor het verwjderen van gegevens. ✔ UPDATE voor het wijzigen van gegevens.
7.1. HET INSERT COMMANDO INSERT wordt gebruikt voor de invoer van gegevens in een tabel. Je kan slechts aan één tabel tegelijkertijd data toevoegen. Er zijn twee algemene syntaxes voor het commando: met de eerste voer je rij per rij een aantal waarden toe, met de tweede kan je data uit één tabel ophalen om ze in een tweede te stoppen.
7.1.1. INSERT rij per rij Met het INSERT commando kan je één rij data toevoegen aan een tabel. De algemene syntax is als volgt: INSERT INTO table [(columns)] VALUES (value_list); Het gedeelte tussen rechte haken geeft alle namen van de velden (of kolommen) die we willen toevoegen. Het kan worden weggelaten op voorwaarde dat voor elk veld van de tabel een waarde wordt toegevoegd. Ze dienen dan te worden ingevoerd in dezelfde volgorde als deze waar de tabel mee gecreëerd werd. Indien we bijvoorbeeld een rij zouden willen toevoegen aan onze chauffeur tabel zouden we dat kunnen doen op één van de volgende twee manieren: INSERT INTO chauffeur VALUES ('Jan Deconinck', '30 Nov 1979'); INSERT INTO chauffeur (naam, geboortedatum) VALUES ('Els Degroot','18 May 1983'); Omdat je dikwijls toch voor alle velden een waarde invoert met een INSERT commando, hoef je in principe dan niet de veldnamen mee te geven. Meestal verdient het echter de voorkeur het toch te doen, aangezien de volgorde wel strict dient gerespecteerd te worden. De minste fout daarin kan niet alleen soms niet uitgevoerd worden – je commando zou dan een foutboodschap opleveren - maar zou er bovendien kunnen toe leiden dat foutieve invoer wordt aanvaard, wat nog erger is.
7.1.2. INSERT vanuit een query De tweede syntax laat je toe om data vanuit een andere tabel (of een aantal tabellen) op te halen en ze toe te voegen aan een nieuwe tabel. De meest algemene syntax is als volgt: INSERT INTO table1 [(columns)] SELECT matching_columns FROM table2; Het SELECT statement dient slecht aan twee voorwaarden te voldoen: het dient een geldig Databanken
85
statement te zijn, en de kolomtypes die worden opgehaald moeten exact overeenkomen met de kolomtypes van de tabel waaraan je de data wil toevoegen. Stel dat we bijvoorbeeld een tabel zouden willen maken met de namen van alle chauffeurs en hun favoriete kleur. De tabel bevat enkel een naam en een een kleur. Met volgende syntax zou je deze nieuwe tabel kunnen vullen met gegevens: INSERT INTO FAVKLEUR (naam, favkleur) SELECT naam, kleur FROM chauffeur JOIN wagen ON naam=eigenaar; Het spreekt voor zich dat de zo gecreëerde data in ons voorbeeld niet zo heel zinvol is. Dergelijke queries kunnen echter wel erg zinvol zijn wanneer je de data in een database wil herorganiseren of wanneer je data uit één toepassing wil gebruiken in een andere.
7.1.3. INSERT en automatische velden Soms kan het handig zijn om met een automatisch genummerd veld te werken als unieke primary key. Bijvoorbeeld wanneer je tabel niet echt ergens een gegarandeerd uniek iets bevat. De SQL standaard heeft daar lange tijd niets voor voorgeschreven; momenteel worden verschillende mogelijkheden geboden. Traditioneel boden verschillende databases hiervoor verschillende oplossingen aan. Een aantal databases (zoals bijvoorbeeld Access, SQL server, Sybase en MySQL) bieden een auto-increment veld aan – soms identity genoemd. Wanneer deze oplossing zonder meer wordt toegepast, heeft dat het nadeel dat dergelijk veld niet kan gebruikt worden binnen transacties omdat je pas weet welk nieuw nummer je hebt gekregen nadat de transactie werd uitgevoerd. Indien je binnen één transactie aan verschillende tabellen data wil toevoegen waarbij hetzelfde unieke nummer dient te worden gebruikt, heb je een probleem. Je moet dan immers eerst de data in de eerste tabel met het automatich nummer invoeren, dat nummer ophalen, om dan de data aan de andere tabellen toe te voegen. Met het risico dat foutieve invoer aan die tabellen leidt tot corruptie in de database. Sommige databases bieden hiervoor een andere oplossing: een sequence (Oracle, Firebird – vroeger generator genoemd in Firebird) die buiten elke transactie gegenereerd worden. In Firebird (vanaf versie 2.1) gebruik je dan: INSERT INTO tbl (ID,...) VALUES (NEXT VALUE FOR sequence,..); In oudere versies van Firebird diende je eerst met SELECT GEN_ID(naamgenerator,1) FROM RDB$DATABASE; een gegarandeerd unieke waarde op te halen (dit statement wordt immers altijd buiten elke transactie uitgevoerd) dat je daarna in opeenvolgende INSERT statements kon gebruiken. Met een TRIGGER (PSQL) kan je er bovendien voor zorgen dat, wanneer het unieke nummer niet wordt meegegeven bij de aanmaak van het record, je het automatisch aanmaakt. In Firebird met een uniek "autoincrement" veld ID werkt dat dan als volgt: CREATE TRIGGER naamtrigger FOR table ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (new.ID IS NULL) THEN new.ID = GEN_ID(naamgenerator,1); END Op die manier heb je een combinatie van beide voordelen: enerzijds een autoincrement Databanken
86
veld, anderzijds de mogelijkheid je unieke nummer op te halen wanneer je het zelf wenst, zodat je het ook binnen transacties kan gebruiken. Let echter op wanneer je een toepassing wil schrijven die voor verschillende DBMS dienen te werken, of die je gemakkelijk wil kunnen porteren. Je kan er dan best voor kiezen geen van deze oplossingen te gebruiken en bijvoorbeeld een trigger te gebruiken (die door alle grote database ondersteund worden) die een unieke waarde gaat ophalen bijvoorbeeld op basis van een MAX select statement. Zowel het begrip IDENTITY als SEQUENCE of GENERATOR vind je terug in de SQL standaard.
7.1.4. INSERT en defaults Wanneer bepaalde velden met een default waarde gedefiniëerd zijn, werkt deze default waarde alléén bij een INSERT en op voorwaarde dat de naam van het veld niet gebruikt wordt binnen je INSERT commando. Ook NULL waarden worden niet vervangen door de default waarde. Indien bijvoorbeeld in de tabel wagen het default merk 'Onbekend' is en de default kleur eveneens 'Onbekend', dan gebruikt het volgende statement inderdaad de default waarden: INSERT INTO wagen (nummerplaat) VALUES ('AAA456'); Het volgende zal proberen NULL waarden in te voeren: INSERT INTO wagen (nummerplaat, merk, kleur) VALUES ('AAA456', NULL, NULL);
7.1.5. INSERT en triggers De meeste grote databases kennen een uitbreiding op SQL die, afhankelijk van de database, een andere naam kan hebben: procedural SQL (kortweg PSQL genoemd). Met PSQL wordt het mogelijk te “programmeren” in SQL. PSQL ondersteunt o.a. triggers. Triggers kunnen bijvoorbeeld gebruikt worden om bepaalde velden 'automatisch' te bevolken. Ze gaan verder dan default-waarden omdat het met een trigger mogelijk is dataintegriteit in een hoger stadium te bewaken. Ze kunnen bijvoorbeeld zo geschreven worden dat ze de velden bevolken met default-waarden of berekende waarden bij het ontbreken ervan. Of ze kunnen bepaalde bewerkingen op de data toepassen. Zo kan je bijvoorbeeld een trigger schrijven waarmee je alle data in één veld naar hoofdletters omzet. Of de eerste letter naar hoofdletter en de volgende naar kleine. Bvb. als volgt: CREATE TRIGGER trgnaam1 FOR table ACTIVE BEFORE INSERT POSITION 0 AS begin IF (new.NAME IS NULL) THEN EXIT; IF (strlen(new.NAME)>1) THEN new.NAME=upper(substring(new.NAME FROM 1 FOR 1)) || lower(substring(new.NAME FROM 2)); ELSE new.NAME=upper(new.NAME); end Bovenstaande enkel als een eenvoudig voorbeeld van een trigger. Bepaalde functies Databanken
87
(zoals substring, lower, upper,..) kunnen database gebonden zijn. Ook de syntax van de trigger zelf kan enigszins afwijken – zo is de syntax van MS SQL Server/Sybase bijvoorbeeld heel erg anders dan bovenstaande. Toch bieden de belangrijkste databases wel gelijkaardige functionaliteit, zodat omzetting van de ene naar de andere bij doordacht gebruik wel kan meevallen. Triggers (en procedures: zie verder) zijn voornamelijk interessant wanneer je via verschillende applicaties dezelfde data wil aanspreken. Sommige database bieden ook een vorm van "berekende" velden. Het zijn velden waarvoor je geen waarde kan invoeren, maar waarvoor de data berekend wordt op basis van andere velden. Een concept dat vrij algemeen gebruikt wordt zijn checks. Ze controleren eveneens de data in een bepaalde kolom op de integriteit ervan. Zo zou je bijvoorbeeld een check kunnen hebben op de invoer van een geboortejaar, als je enkel een jaar wil accepteren tussen 1900 en 2000. Je hebt er mee de mogelijkheid om op een eenvoudige manier de invoer van een bepaald veld te controleren. Sommige databases bieden ook domains (domein) – o.a. Interbase/Firebird en PostgreSQL. Ze maken het mogelijk voor de databank beheerder om userdata types te definiëren, waarop bovendien allerlei integriteits-controles mogelijk zijn. Ze hebben het voordeel dat ze niet tabel-gebonden zijn. Een domain in Firebird bijvoorbeeld kan checks bevatten en een default-waarde. Triggers, procedures, checks en domains worden allemaal expliciet beschreven in de SQL standaard.
7.2. HET UPDATE COMMANDO UPDATE wordt gebruikt om de waarden van velden in bestaande rijen te wijzigen. Zoals bij INSERT is het niet mogelijk de waarden van velden in verschillende tabellen in één commando te wijzigen. Het meest eenvoudige UPDATE commando ziet er als volgt uit: UPDATE table SET columnname=...; Indien we bijvoorbeeld de kleur van de wagen naar Blauw willen wijzigen, schrijven we: UPDATE wagen SET kleur='Blauw'; In dit geval wordt de kleur voor alle wagens naar Blauw gezet. Dergelijke UPDATE wordt zelden gebruikt omdat het zelden voorkomt dat je alle rijen in een tabel naar dezelfde waarde wil wijzigen – tenzij bijvoorbeeld bij kolommen die gedeeltelijk afhangen van een ander veld in de tabel, of die om een of andere reden op gepaste momenten worden berekend op basis van andere velden. Een UPDATE wordt bijna altijd gebruikt samen met een WHERE clausule: UPDATE table SET columnname1=... WHERE columnname2=...; Indien we bijvoorbeeld de kleur van de wagen van Tom Jansen naar Blauw willen wijzigen, schrijven we: UPDATE wagen SET kleur='Blauw' WHERE eigenaar='Tom Jansen'; De twee kolommen kunnen ook dezelfde zijn. Indien we bijvoorbeeld de kleur van alle Databanken
88
groene wagens naar Blauw willen wijzigen, schrijven we: UPDATE wagen SET kleur='Blauw' WHERE kleur='Groen';
7.2.1. De SET clausule De SET clausule kan meer dan één veld tegelijkertijd wijzigen, en wel als volgt: SET columnname1=value [, columnname2=value] De nieuwe waarden moeten van het correcte type zijn. Wanneer een een veld NULL waarden kan bevatten kan het volgende geschreven worden: SET columnname=NULL De value in bovenstaande statement dient één van de volgende te zijn: ✔ Een constante waarde (bijvoorbeeld 99): SET columnname=99; ✔ De naam van een ander veld binnen dezelfde tabel, op voorwaarde dat het van hetzelfde type is: SET columnname1=columnname2; ✔ Een expressie: bijvoorbeeld iets als SET columnname=columnname+10; ✔ Een bepaalde server-gerelateerde waarde; bijvoorbeeld de huidige datum of tijd; ✔ Een SQL functie aanroep; of eventueel een user defined function aanroep indien bestaand; bijvoorbeeld SET columnname=UPPER(columnname).
7.2.2. Waardes switchen tussen twee velden Wanneer je twee kolommen wil wisselen dien je een derde kolom van hetzelfde type te gebruiken. Het volgende commando UPDATE table SET column1=column2, column2=column1; zou er enkel toe leiden dat de waarden van de oorspronkelijke tweede kolom naar zowel de eerste als de tweede worden gekopiëerd. Het volgende levert wel een correct resultaat op: UPDATE table SET column3=column1, column1=column2, column2=column3; Bij het wisselen van numerieke waarden kan je dit soms omzeilen door gebruik te maken door bewerkingen toe te passen, bijvoorbeeld: UPDATE table SET column2=column2 + column1, column1=column2 – column1, column2=column2 - column1; Test steeds voor je dergelijk UPDATE uitvoert!
7.2.3. UPDATE en triggers Net zoals bij INSERT kunnen ook voor UPDATE triggers geschreven worden. Indien we terugkeren naar ons voorbeeld met hoofdletters: het zal in dergelijk geval duidelijk zijn dat niet alleen bij insert, maar ook bij update, de eerste letter naar een hoofdletter dient gewijzigd. We hebben dan een tweede trigger nodig die hetzelfde doet, maar dan bij update:
Databanken
89
CREATE TRIGGER trgnaam2 FOR table ACTIVE BEFORE UPDATE POSITION 0 AS ... De trigger is dezelfde als deze bij INSERT, maar wordt in dit geval BEFORE UPDATE aangeroepen ipv BEFORE INSERT. Je kan nhier de mogelijk gebruiken BEFORE INSERT OR UPDATE. Ofaje zou een stored procedure kunnen schrijven die je in de twee gevallen oproept. CREATE PROCEDURE zetnaam (NAME VARCHAR(50)) RETURNS (RETURNNAME VARCHAR(50)) AS begin IF (NAME IS NULL) THEN EXIT; IF (strlen(NAME)>1) THEN RETURNNAME=upper(substring(NAME FROM 1 FOR 1)) || lower(substring(NAME FROM 2)); ELSE RETURNNAME=upper(NAME); end CREATE TRIGGER trgnaam1 FOR table ACTIVE BEFORE INSERT POSITION 0 AS begin EXECUTE zetnaam(new.NAME); end
Een stored procedure kan i.t.t. een trigger (die rechtstreeks toegang heeft tot de tabelvelden) parameters accepteren en returnwaarden teruggeven. Opgelet: in nieuwere versies van Firebird kan je bovenstaande eenvoudiger verwezenlijken met: CREATE TRIGGER trgnaam2 FOR table ACTIVE BEFORE UPDATE OR INSERT POSITION 0 AS ...
7.3. HET DELETE COMMANDO DELETE wordt gebruikt voor het verwijderen van ganse rijen uit een tabel. Ook met DELETE kunnen slechts rijen in één tabel tegelijkertijd verwijderd worden. De syntax is als volgt: DELETE FROM table WHERE clausule; Het kan ook zonder de WHERE clausule gebruikt worden: alle rijen van de tabel worden dan verwijderd.
7.4. INSERT, UPDATE EN DELETE MET VIEWS Om te kunnen antwoorden op de vraag of INSERT, UPDATE en DELETE ook gebruikt kunnen worden op views, dienen we eerst te weten wanneer een view precies wordt Databanken
90
gemaakt. Wanneer wordt de inhoud van het view berekend? Theoretisch zijn er hiervoor twee mogelijkheden: ✔ wanneer het view gecreëerd wordt; ✔ wanneer het wordt opgevraagd. Als het eerste zou gebeuren, zou het uitgesloten zijn dat de commando's uit de manipulatietaal zouden werken op een view. In het tweede geval zou het wel mogelijk zijn. Sommige database laten toe dat de gebruiker definiëert wanneer het view gematerialiseerd wordt. De meeste doen dat echter wanneer je het opvraagt, zodat je steeds alle wijzigingen aan de tabel kan zien. Betekent dat nu dat je dus steeds INSERT, UPDATE en DELETE kan gebruiken op een view? Niet altijd nee. Er is moet tenminste aan één belangrijke voorwaarde worden voldaan: van alle tabellen waarop het view gebaseerd is moet de primary key deel uitmaken van dat view. Anders kan het view immers niet weten welke rijen dienen te worden aangepast. Daarnaast zijn er bijkomende voorwaarden: zo moeten alle velden die niet tot de subset van het view behoren NULL waarden of default-waarden hebben.
7.5. OPGAVEN Ook voor deze opgaven gebruiken we de COMPANY database op de bladzijden 83 en 84. Schrijf de comando's waarmee de gegevens aan de database COMPANY kunnen toegevoegd worden. 1. Wijzig het geslacht M naar m. 2. Geef iedereen een loonopslag van 10%. 3. Geef iedereen die minder verdient dan 40000 een loonopslag van 10%, de anderen een loonopslag van 5%. 4. Voeg voor iedereen een extra rij in de tabel WORKS_ON in voor projectnummer 30 en met 0 aantal gewerkte uren. 5. Verwijder de net toegevoegde rijen. 6. Voeg aan de tabel EMPLOYEE drie velden toe: STREET, CITY en STATELETTERS (via Flamerobin GUI of met de syntax uit hoofdstuk 8). Vul ze met de gewenste gegevens: gebruik daarvoor de ingebouwde functies POSITION, SUBSTRING en TRIM (of eventueel andere naar keuze). Zie voor meer documentatie wiki.firebirdsql.org. 7. Geef het veld FNAME de naam LASTNAME en het veld LNAME de naam FIRSTNAME. Verwissel daarna de waarden uit de twee kolommen.
Databanken
91
8. SQL DEFINITIETAAL In dit hoofdstuk bekijken we het gedeelte datadefinitietaal van SQL. We kunnen er het schema van de database mee definiëren. Anders gezegd: we kunnen er de metadata van de database mee creëren, wijzigen of verwijderen. De basiscommando's zijn: ✔ CREATE voor de nieuwe creatie van metagegevens. ✔ DROP voor het verwjderen van metagegevens. ✔ ALTER voor het wijzigen van metagegevens.
8.1. HET CREATE COMMANDO CREATE wordt gebruikt voor de creatie van een aantal zaken. In alle DBMS kan je er tabellen en indexen mee creëren. Vaak kan je er ook domeinen, triggers, procedures, generators/sequences,... mee creëren, tot zelfs de database zelf.
8.1.1. Een tabel creëren Met CREATE TABLE kan je een nieuwe tabel aanmaken. De structuur ervan is vrij eenvoudig: het bevat de tabelnaam, de veldnamen van de nieuwe tabel, en het datatype voor elk van die veldnamen. De syntax is als volgt: CREATE TABLE tablename ( fieldname1 type1 additionalinfo1, ... fieldnameN typeN additionalinfoN, additionaltableinfo); Afhankelijk van de DBMS kunnen er meer of minder datatypes zijn. De meest voorkomende zijn INTEGER, DECIMAL, REAL of FLOAT, VARCHAR, CHAR en DATE. Zie volgende paragraaf voor meer informatie hierover. Zoals je merkt kan er bij elk veld nog additionele informatie worden gegeven: ✔ NOT NULL voor velden die geen NULL waarden mogen bevatten. ✔ PRIMARY KEY voor een veld dat de primary key is. ✔ Default-waarden met DEFAULT. ✔ GENERATED ALWAYS AS voor berekende velden (vanaf SQL-2003) – in vroegere versies van Firebird COMPUTED BY (). ✔ REFERENCES voor de definitie van een foreign key. Met A INTEGER REFERENCES B(C) is A een foreign key voor de primary key C van tabel B. Bovendien kan er ook nog additionele tabelinformatie worden opgegeven, nodig wanneer er sleutels voorkomen met meervoudige attributen. Volgende is een geldige tabelcreatie voor de tabel wagen die we hoger tegenkwamen: CREATE TABLE wagen ( nummerplaat CHAR(6) NOT NULL PRIMARY KEY, merk VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, kleur VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, prijs INTEGER NOT NULL, eigenaar VARCHAR(30) REFERENCES chauffeur(naam)); Databanken
92
8.1.2. Datatypes Afhankelijk van de DBMS kunnen er meer of minder datatypes zijn. De meest voorkomende zijn de volgende: ✔ INTEGER: nummers zonder decimaal punt. Vaak zijn er verschillende INTEGER types voorhanden: SMALLINT (-32768 tot 32767), INTEGER (-2147483648 tot 2147483647) en BIGINT (-263 tot 263-1). ✔ DECIMAL: nummers met decimaal punt. Ze worden gedefiniëerd als DECIMAL(p,s) waarbij p het totale aantal cijfers is en s het aantal cijfers na de komma. Zo kan DECIMAL(4,2) de volgende getallen bevatten: 0.43 ; 34.00 ; 23.78 ; 1.18 ; ... Wanneer je probeert 23.78342 op te slaan, wordt in feite 23.78 opgeslagen. Intern worden ze naargelang hun definitie als een soort integer opgeslagen. Soms wordt ook het type NUMERIC hiervoor gebruikt. ✔ REAL: nummers met een decimaal punt. Soms FLOAT en DOUBLE PRECISION genoemd. Het eerste voor een 32 bit getal, het tweede voor een 64 bit. De precisie gebruikt voor de opslag van de getallen hangt af het getal zelf. In dezelfde kolom kan dus zowel 23.78 als 23.78342 worden opgeslagen. ✔ VARCHAR voor de opslag van strings met variabele lengte. Een VARCHAR(5) kan dus zowel '' als 'AAAA' als 'AAAAA' bevatten. 'AAAAAA' kan er niet mee worden opgeslagen. Soms wordt ook CHAR VARYING of CHARACTER VARYING gebruikt. ✔ CHAR voor de opslag van strings met vaste lengte. Alle strings van het type CHAR(5) zijn exact 5 karakters lang, bijvoorbeeld ' ', 'AAAA ' of 'AAAAA'. Wanneer je er een kortere string in opslaat wordt de string met spaties verlengd tot de juiste lengte. Soms ook CHARACTER genoemd. ✔ DATE voor een datum. De standaard schrijft voor de opslag van een datum en tijd de volgende datatypes voor: TIMESTAMP voor datum+tijd, DATE voor datum alleen en TIME voor tijd alleen. ✔ BIT vor de opslag van een aantal bits. Met BIT(2) kan '00', '01', '10' of '11' worden opgeslagen. Wordt niet altijd ondersteund. ✔ Sinds SQL99 bestaat er ook een BOOL of BOOLEAN datatype: true of false.
8.1.3. Additionele tabelinformatie Wanneer er een sleutel voorkomt over meervoudige attributen, kan deze niet gedefinieerd worden zoals we hierboven zagen. We kunnen dan additionele informatie toevoegen na de definitie van de laatste kolom, en wel als volgt: ✔ PRIMARY KEY(column1, column2,...) voor de definitie van de primaire sleutel. ✔ FOREIGN KEY (column1, column2,...) REFERENCES othertable voor de definitie van een vreemde sleutel. We hadden de tabel wagen dus ook als volgt kunnen creëren: CREATE TABLE wagen ( nummerplaat CHAR(6) NOT NULL, merk VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, kleur VARCHAR(10) DEFAULT 'Onbekend' NOT NULL, prijs INTEGER NOT NULL, eigenaar VARCHAR(30), PRIMARY KEY(nummerplaat), FOREIGN KEY (eigenaar) REFERENCES chauffeur); Databanken
93
8.1.4. Ander gebruik van CREATE CREATE kan nog in heel wat andere gevallen gebruikt worden. Hieronder een korte opsomming van de mogelijkheden, met telkens aanduiding in hoever het in de SQL standaard is opgenomen. We geven steeds de meest gebruikelijke syntax. De delen tussen rechte haken zijn optioneel. Delen gescheiden door een verticale rechte streep kunnen niet samen voorkomen. CREATE INDEX bestaat niet in de SQL standard, alhoewel zowat alle DBMS het ondersteunen. De meest gebruikelijke syntax is als volgt: CREATE [UNIQUE] [ ASC[ENDING] | [DESC[ENDING] ] INDEX indexname ON tablename (col1 [,col2,...]); CREATE DOMAIN is gedefinieerd in standaard SQL. De meest eenvoudige syntax is als volgt: CREATE DOMAIN domainname [AS] datatype [DEFAULT value] [NOT NULL] [CHECK search-condition]; Voor de search-condition in het optionele CHECK gedeelte zijn er een ganse reeks mogelijkheden. Een CHECK constraint kan ook op het veld van een tabel gebruikt worden: zie de syntax daarvoor verder onder het ALTER TABLE commando. De belangrijkste mogelijkheden voor een CHECK constraint zijn de volgende: search-condition = VALUE operator value | VALUE [NOT] BETWEEN value1 AND value2 | VALUE [NOT] LIKE value | VALUE [NOT] IN (value1[, value2,...]) | VALUE IS [NOT] NULL Er kunnen bovendien met behulp van AND en/of OR expressies gebouwd worden. Volgende is bijvoorbeeld een geldige CHECK constraint: CHECK ((VALUE IS NULL) OR (VALUE>10 AND VALUE<100)); CREATE TRIGGER en CREATE PROCEDURE zijn beide gedefinieerd in standaard PSQL. In hoofdstuk 7 vind je een eenvoudig voorbeeld van een trigger en een procedure. Het leidt te ver om de volledige syntax ervan op te nemen in deze cursus. CREATE SCHEMA en CREATE DATABASE worden vaak beide voor hetzelfde doel gebruikt. Het eerste is wel gedefinieerd in de standaard, het tweede niet. De meest eenvoudige syntax is als volgt: CREATE DATABASE | SCHEMA 'file-specification' USER 'gebruiker' PASSWORD 'paswoord'; In Firebird kunnen met CREATE ROLE rollen gedefiniëerd worden. Rollen kunnen toegekend worden aan een groep gebruikers, waarvoor dan dezelfde rechten toegekend zijn. Aangezien de implementatie van de beveiliging van de database aan de databaseconstructeurs wordt overgelaten, wordt hierover niets gedefiniëerd in de standaard. De syntax voor de creatie van een rol is als volgt:: CREATE ROLE rolnaam; Zoals in hoofdstuk 6 vermeld wordt CREATE ook gebruikt voor de creatie van een view, Databanken
94
wat ook vermeld wordt in de SQL standaard. Daarnaast zijn er vaak nog commando's aanwezig voor de creatie van functies en sequences/generators. De creatie van een generator in Interbase/Firebird gebeurt als volgt: CREATE GENERATOR generatornaam;
8.2. HET ALTER COMMANDO Met ALTER kan je bestaande definities in de database wijzigen. Zoals het CREATE commando wordt het gebruikt voor een ganse reeks zaken. En zoals bij dat commando kan je er in alle DBMS tabellen, indexen, domeinen, triggers, procedures, generators en/of sequences,... mee wijzigen.
8.2.1. Een tabel wijzigen Met ALTER TABLE kan je een bestaande tabel wijzigen. In tegenstelling tot het CREATE TABLE commando is dit commando veel gevariëerder, aangezien het gebruikt kan worden voor een ganse reeks wijzigingen. Je kan er velden of constraints mee toevoegen, wijzigen of verwijderen. Vooraleer je een bestaande tabel met data gaat wijzigen verdient het steeds aanbeveling om een backup te nemen van de gegevens. Soms zal het ook nodig zijn bepaalde afhankelijkheden van andere tabellen te verwijderen. We vernoemen hieronder de belangrijkste wijzigingen die je kan doorvoeren. Om een veld toe te voegen aan een tabel gebruiken we het volgende commando. De terminologie is volledig gelijk aan deze die we gebruiken bij de creatie van een tabel: ALTER TABLE tablename ADD fieldname type additionalinfo; We kunnen ook bestaande velden wijzigen: de veldnaam kan gewijzigd worden of de positie van het veld kan gewijzigd worden. In een aantal gevallen kan het datatype van een veld ook gewijzigd worden: de meeste wijzigingen van een niet-karakter datatype naar een karakter-type zijn mogelijk. We geven drie voorbeelden. Met het eerste wordt de naam van het veld id gewijzigd naar nr, het tweede wijzigt de positie van dit veld, en het derde wijzigt het datatype. ALTER TABLE tablename ALTER COLUMN id TO nr; ALTER TABLE tablename ALTER COLUMN nr POSITION 4; ALTER TABLE tablename ALTER COLUMN nr TYPE VARCHAR(20); Wijzigingen van een datatype waar gegevens mee verloren worden zijn meestal niet mogelijk. Een veld verwijderen uit een tabel doe je als volgt: ALTER TABLE tablename DROP field1 [, field2,...]; Velden kunnen niet verwijderd worden indien: ✔ ze deel uitmaken van een unieke index, een primaire of een vreemde sleutel; ✔ er een CHECK constraint op bestaat; ✔ ze gebruikt worden in een view, trigger of procedure.
Databanken
95
Nieuwe constraints kunnen eveneens toegevoegd worden met ALTER TABLE. De volgende voorbeelden voegen respectievelijk een primaire sleutel, een unieke index en een CHECK toe: ALTER TABLE table ADD [CONSTRAINT naam] PRIMARY KEY (field1 [, field2,...]; ALTER TABLE table ADD [CONSTRAINT naam] FOREIGN KEY (field1 [, field2,...]) REFERENCES othertable; ALTER TABLE table ADD [CONSTRAINT naam] UNIQUE (field1 [, field2,...]; ALTER TABLE table ADD [CONSTRAINT naam] CHECK search-condition; Voor meer informatie over de search-condition verwijzen we naar paragraaf 8.1.4. Constraints kunnen ook verwijderd worden. Een primaire sleutel kan slechts verwijderd worden indien er geen vreemde sleutels naar verwijzen. De syntax is als volgt: ALTER TABLE tablename DROP CONSTRAINT naam;
8.2.2. Wanneer ALTER niet volstaat Soms is het onmogelijk een gewenste wijziging aan te brengen met het ALTER commando. Bijvoorbeeld bij wijziging van het datatype van een kolom. Je kan dan toch het gewenste resultaat bereiken door de volgende stappen uit te voeren: 1. Voeg eerst een nieuw tijdelijk veld toe met de gewenste eigenschappen maar met een andere naam. 2. Gebruik het UPDATE commando om de gegevens van het oude veld over te brengen naar het nieuwe. Gebruik eventueel omzettingsfuncties zoals CAST. 3. Na controle op de gekopiëerde data kan je het oude veld verwijderen. 4. Geef het tijdelijke veld dan originele naam.
8.2.3. Ander gebruik van ALTER Net zoals CREATE kan ook ALTER nog in heel wat andere gevallen gebruikt worden. Hieronder enkele voorbeelden. ALTER INDEX heeft meestal niet veel betekenis aangezien een index meestal wordt verwijderd of gecreëerd. Een aantal databases bieden de mogelijkheid een index te (des)activeren als volgt: ALTER INDEX indexname INACTIVE | ACTIVE; Een domain kan met ALTER DOMAIN ongeveer op dezelfde manier gewijzigd worden als een veld. De naam of het datatype kunnen gewijzigd worden, of een defaultwaarde of CHECK constraint kunnen verwijderd of gewijzigd worden. Bijvoorbeeld: ALTER DOMAIN domainname TYPE newtype; De ALTER TRIGGER en ALTER PROCEDURE worden gebruikt zoals de CREATE varianten, met slechts één belangrijk verschil: bij triggers vervalt het FOR table commandogedeelte. ALTER SCHEMA of ALTER DATABASE worden soms gebruikt om de fysieke plaats vande database te wijzigen, bijvoorbeeld wanneer extra bestanden dienen te worden toegevoegd (voor databases die over verscheidene bestanden kunnen verspreid worden). Databanken
96
8.3. HET DROP COMMANDO Met DROP kan je bestaande definities uit de database verwijderen. Zoals het CREATE en ALTER commando wordt het gebruikt voor een ganse reeks zaken. De syntax ervan is meestal erg eenvoudig. Met DROP DROP DROP DROP DROP DROP
TABLE naam; DOMAIN naam; INDEX naam; TRIGGER naam; PROCEDURE naam; VIEW naam;
worden respectievelijke een tabel, domein, index, trigger, procedure en view met de naam naam verwijderd. Met DROP DATABASE; wordt de ganse database verwijderd.
8.4. OPGAVEN Ook voor deze opgaven gebruiken we de COMPANY database op de bladzijden 82 en 83. 1. Schrijf alle commando's om de database te creëren. 2. Wijzig het veld SALARY van een numeriek veld naar een karakterveld. 3. Wijzig het veld SSN van een CHAR(9) naar een numeriek veld en het veld LNAME van VARCHAR(30) naar VARCHAR(25). 4. Maak van ADDRESS een berekend veld. 5. Voeg aan de tabel EMPLOYEE de velden PHONE en EMAIL toe van het type VARCHAR(15) en VARCHAR(200). 6. Indexeer beide nieuwe velden. Het veld EMAIL moet uniek zijn. 7. Voor de liefhebbers: schrijf triggers die nodig zijn om het veld EMAIL correct te formatteren: steeds met een @ erin. Vooraan een aantal karakters en achteraan eveneens met één punt erin. 8. Voeg een veld toe EMAILOK (ja/nee veld). Indien men probeert een foutief emailadres op te slaan, zet je dit veld op nee, standaard staat het op ja.
Databanken
97
REFERENTIES Cursusnota's van collega Kris van Baelen Absolute Beginer's Guide to Databases Petersen Que; 1st edition (2002) ISBN: 0-7897-2569-X Fundamentals of Database Systems Elmasri & Navathe Addison Wesley; 4th edition (July 23, 2003) ISBN: 0-3211-2226-7 Gestructureerde Analyse Yourdon Academic Service; 1991 ISBN: 90-623-3559-4 SQL In a Nutshell Kline, Kline & Hunt O'Reilly Media, Inc.; 2nd edition (2004) ISBN: 0-5960-0481-8 Basiscursus SQL Dugour Academic Service; 2de herziene druk (2003) ISBN: 90-395-2179-4 Het SQL Leerboek Rick van der Lans Academic Service; 5de verbeterde druk (2004) ISBN: 90-395-2248-0 Learning SQL Beaulieu O'Reilly Media, Inc.; 1st edition (2005) ISBN: 0-5960-0727-2 SQL The Complete Reference Groff & Weinberg Osborne McGraw-Hill; 2nd edition (2002) ISBN: 0-0722-2559-9 The Firebird Book Borrie Apress; 2004 ISBN: 1-59059-279-4
Databanken
98
