IS0-dictaat deel 1 Databases en database

IS0-dictaat deel 1
Databases en database-managementsystemen DBMS
Databases
Een database (gegevensbank) bevat over het algemeen zéér veel gegevens, van grote aantallen gegevenstypes,
waarbij op de een of andere manier een verband bestaat tussen die verschillende gegevenssoorten. Databases zijn
bijna altijd zodanig ontworpen, dat gegevens niet (of: zo weinig mogelijk) meervoudig worden opgeslagen.
Voor het werken met een database wordt database-management-software gebruikt.
Voorbeelden van databases zijn:
• Voor een bibliotheek worden in een gegevensbank de gegevens bijgehouden over boekencatalogus, leden,
exemplarenbezit, uitleningen (welk exemplaar aan welk lid), reserveringen (welk boek voor welk lid) e.d..
• Voor een bedrijf worden in een database product-, klant-, toeleverbedrijf-, inkoop-, verkoop-, magazijn- en
financiële gegevens bijgehouden.
• Bij een gemeente worden in database(s) gegevens bijgehouden over inwoners, paspoorten en rijbewijzen,
(honden)belasting, percelen, bebouwing, bestrating, leidingen, e.d..
Een belangrijk aspect bij het gebruiken van een gegevensbank is, dat er voorzorgsmaatregelen worden genomen op
het gebied van data-integriteit en van gegevensbeveiliging. Dit betekent respectievelijk, dat enerzijds moet worden
voorkomen dat er foutieve gegevens in de database komen (b.v. dat een niet-bestaand boek-exemplaar aan een nietbestaand bibliotheeklid is uitgeleend) en dat anderzijds moet worden voorkomen dat onbevoegden toegang hebben
tot niet voor hen bedoelde gegevens (b.v. dat werknemers hun eigen salarisschaal kunnen veranderen).
Op databases gebaseerde informatiesystemen zijn tegenwoordig van cruciaal belang voor bedrijven en organisaties.
Grote en middelgrote bedrijven zijn er dusdanig afhankelijk van geworden, dat de meesten het economisch níet
overleven als hun informatiesystemen gedurende enkele weken zouden uitvallen.
Elementen van een databasesysteem
Het is vaak belangrijk om je óók van een databasesysteem een model te vormen; een veel gebruikte mogelijkheid
daarvoor biedt ons het zogenaamde ANSI/SPARC-model, dat in een databasesysteem dríe niveau’s onderkent:
• het externe niveau, dat voor alle gebruikers verschillend kan zijn en afhangt van de wijze waarop zo’n
gebruiker tegen de gegevens in een organisatie aankijkt. Vaak omvat een gebruikersview slechts een deel
van de gegevens (en hun samenhang) die op het conceptuele niveau worden beschreven. De user interfaces
die de gebruiker van het systeem kent, zullen met zijn gebruikersmodel moeten corresponderen.
• het conceptuele niveau (ook wel: logische niveau), waarbij álle in de database aanwezige gegevens en hun
onderlinge samenhang worden beschreven, zonder dat daar de wijze waarop fysiek (lees: op intern niveau)
de gegevens op een opslagmedium zijn opgeslagen. Dit model vormt een basis voor het bepalen van:
• het interne niveau, waarin beschreven wordt hoe de gegevens op een efficiënte wijze worden opgeslagen,
zodanig dat ze bijvoorbeeld snel terug kunnen worden gevonden.
Het ontwikkelen van een databasesysteem begint in de praktijk met het vaststellen en analyseren van het externe
model, gevolgd door het opstellen van een conceptueel model en het vandaaruit afleiden van het interne niveau.
Om je eerst een idee te laten vormen van databasesystemen beginnen we in deze cursus met het interne niveau.
Basisgegevensstructuren
Bij heel veel soorten gegevens die binnen een organisatie van (vaak levens-) belang zijn, bestaat er een relatie tussen
de verschillende delen waaruit een gegevensrecord is opgebouwd.
Als voorbeelden geven we de mogelijke structuur en de inhoud van een klant-record:
klantnr naam voorletters
adres
postcode
plaats
telefoon
34521 Jansen J.K.L.
Annastraat 357 6515 XT
Nijmegen
024-3362718
KUN / Informatica / IS-0-cursus
1
en van een verkoop-record:
klantnr productnr
34521 8439A
bedrag leverdatum
135,50 15-09-2002
betaaldatum
In voorgaand gegevensrecord is het laatste gegevensveld leeg gelaten (er is blijkbaar nog niet betaald).
Ga voor jezelf na, dat een ènkel, geïsoleerd gegevensrecord-deel (zoals een losstaand bedrag) geen zinvolle
betekenis heeft.
Database Management Systemen (DBMS' s) zijn oorspronkelijk (vooral) ontwikkeld om te werken met gegevensre
cords die strak gedefinieerd zijn. Voor het boven aangeduide klant-record kan bijvoorbeeld als recorddefinitie
gelden, dat:
• het klantnummer een getal van (maximaal) 5 cijfers moet zijn;
• de klantnaam uit maximaal 12 tekens mag bestaan;
• in het voorletters-veld plaats gereserveerd is voor 10 tekens;
• verder mag: het adres-veld maximaal 18 tekens, het postcode-veld 7 tekens, het plaatsnaam-veld
12 tekens en het telefoon-veld maximaal 11 tekens bevatten.
Deze starre structuur met vaststaande veldlengten bleek nodig om de software efficiënt (= snel) te laten werken.
Al vrij snel bleek de noodzaak om ook minder gestructureerde gegevens te kunnen opslaan. Daarvoor werden met
een record zogenaamde memo-velden van onbepaalde omvang geassocieerd; daar konden dan bijvoorbeeld stukken
tekst zoals opmerkingen, voortgang van een project e.d. in worden opgeslagen.
Bij modernere software bestaat vaak de mogelijkheid om in een aan een star gegevensrecord geassocieerd speciaal
soort veld (een 'general field') ook plaatjes, geluid, filmpjes e.d. op te nemen.
Sinds eind tachtiger jaren wordt voor een groeiend aantal toepassingen gebruik gemaakt van 'objecten' . Deze
objecten beschikken behalve over gegevens (al dan niet in een gegevensrecord) ook over verwerkingsmethoden, die
voor verschillende objecttypen verschillend kunnen zijn. Bij het opslaan van zulke objecten in een gegevensbestand
worden dan naast de gegevens ook informatie opgeslagen over wat voor sóórt object dat wel is, zodat bij teruglezen
bekend is wat er voor operaties op de gegevens mogelijk zijn. Zo werken teken-programma' s (al dan niet
CAD/CAM) met opslag van objecten (lijnen, cirkels, gearceerde rechthoeken e.d.).
Soorten database-managementsystemen (DBMS’s)
Er bestaan meerdere soorten database-managementsystemen (DBMS’s). De begin zeventi ger jaren opgekomen
DBMS’s volgens het zoge naamde hiërarchische model of volgens het zogenaamde netwerk-model, worden
tegenwoordig voor de meeste toepassingen als ‘verouderd’ gezien (ze zijn vaak weliswaar ‘snel’, maar wel erg rigide
in hun structuur en daardoor moeilijk aan te passen aan veranderende organisatie-omstandigheden en -wensen; zie
voor een beschrijving van deze modellen de ‘bijlage 1’ bij dit collegedictaat).
Voor bedrijfsadministratieve systemen gebruikt men tegenwoordig hoofdzakelijk zogenaamde relationele databases
en bijbehorende relationele database-managementsystemen (RDBMS’s); deze zijn sinds het begin van de tachtiger
jaren sterk opgekomen. Een groot voordeel van dit soort databases is, dat hun structuur relatief gemakkelijk aan te
passen is aan veranderende bedrijfsomstandigheden; ze zijn dus vrij flexibel.
Relationele databases zijn opgebouwd uit meerdere, gerelateerde gegevenstabellen, waarin telkens intern
gestructureerde gegevensrecords zijn opgeslagen en waarbij de gegevensrecords uit die afzonderlijke
gegevenstabellen op een systematische en gestructureerde manier aan elkaar gekoppeld kunnen worden. Via de
onderlinge koppeling van gegevenstabellen is het opzoeken van gegevens relatief eenvoudig.
We richten ons hierna voornamelijk op het gebruik van dit soort DBMS' s en op de daarinbeschikbare datamanipulatie- en opvraagtaal SQL (Structured Query Language).
De sinds eind tachtiger jaren ontwikkelde object oriented database-managementsystemen (OODBMS' s) worden
toegepast in systemen waarbij gegevens met verschillende eigenschappen worden opgeslagen. Als voorbeeld
noemen we het opslaan van de vector graphics afbeeldingen uit CAD/CAM-systemen (Computer Aided Design /
Computer Aided Manufacturing).
KUN / Informatica / IS-0-cursus
2
Relationele database-management-systemen (RDBMS' s)
Zoals reeds vermeld, bevat een relationele database meerdere, gerelateerde gegevenstabellen, waarin telkens intern
gestructureerde gegevensrecords zijn opgeslagen en waarbij de gegevensrecords uit die afzonderlijke
gegevenstabellen op een systematische en gestructureerde manier aan elkaar gekoppeld kunnen worden.
De relationele databases vertonen dat aan-elkaar-gerelateerd-zijn van de gegevenstabellen zelfs op zo’n heldere
wijze, dat gegevensbewerkingen binnen deze systemen wiskundig geformuleerd kunnen worden.
Voor ons doel is het belangrijk, dat voor dit soort (R)DBMS’s een gemakke lijk te gebruiken data-manipulatie- en
opvraagtaal is ontworpen: SQL (Structured Query Language). SQL hoort bij de zogenaamde vierde generatie-talen
(4GL) en gebruikt een beperkt aantal, gemakkelijk aan te leren Engelse uitdrukkingen. Verderop gaan we uitvoerig
op de mogelijkheden en het gebruik van SQL in.
Het ontwerpen van de structuur van die afzonderlijke, binnen een database aan elkaar gerelateerde
gegevenstabellen is een kunst op zich (beoefend bij de zogenaamde informatie-modellering). Die structuur moet
zodanig zijn, dat binnen een record van de ene gegevenstabel steeds een (sleutel)gegeven gelijk is aan een
(sleutel)gegeven binnen een of meer records van een andere tabel; dit zonodig onder gebruikmaking van een
(indirecte) koppeling via andere gegevenstabellen. We kunnen via die sleutelgegevens bij wijze van spreken van
gegevens in de ene tabel naar gegevens van een andere tabel overspringen.
Bij die ontwerpprocedure moet ervoor gezorgd worden, dat de combinatie van tabelstructuren zodanig is, dat
gegevens ‘nooit’ meervoudig hoeven te worden opgeslagen. 1
Een voorbeeld van het in een relationele database opslaan van gegevens is te zien in bijgaande figuur, waar getoond
wordt hoe van de spelers van een sportvereniging de NAW-lidgegevens, de teamgegevens en de boete-gegevens in
afzonderlijke, gerelateerde gegevenstabellen zijn opgeslagen.
Spelers
Spnr Spelernaam
Boetes
Straatnaam
Plaatsnaam
Annastraat 24
Nijmegen
Heren
Nijmegen
Dames
1
K. Huisman
2
P.J. van Onderen Willemstraat 13
4
P. van Oosterom Past. Pietstraat 14Weurt
Heren
W. De Bom
Huilstraat 67
Wychen
Spelersnr
Bedrag
8 Datum
F.G. Habank
Vossendijk
288 Nijmegen
Heren
9
Zwanenveld 89-90Nijmegen
Jeugd1
Kasteel 4
Wychen
Jeugd1
Nijmegen
Heren
6
Boetenr
Teams
Teamnaam
Teamnaam
H. Flits
10
L. Willemsen
1
12/8/89
Klasse
Aanvoerder
12
M.E.P. Graag
12
25.00
Broerdijk 234
Dames
2
13 12/8/89
H. Nijboer
12Plein 44
5.00
Arnhem
Jeugd1
13
10De Groot
19 13/8/89
K.
34Huilstraat
100.00
63
Wychen
Dames
Jeugd2
Jeugd2
34
20 13/8/89
B.E.
20 Melmans 28Kasteel 6 25.00
Wychen
Jeugd2
25
23
Wychen
Jeugd1
Annastraat 24
Nijmegen
Jeugd1
Dames
Heren
16
24
W. De Bom
36
19/8/89
T. Huisman
6 Huilstraat 67
100.00
20/8/89
12
25
Snelweg 673
Lent
Dames
20/8/89
W.Jansen
42Wolvendijk1.00
123
Elst
Jeugd2
8
30 20/8/95
D. van Agt
12De Beurs 25.00
12
Groesbeek Jeugd1
9
32 2/9/95
K. Zwartjes
12Past. Pietstraat
5.00 2 Weurt
7
25
28
H. Fluit
25.00
Jeugd1
33
J. Mienen
Ekkersrijt 23
Eindhoven Heren
34
H. Blaak
Ekkersrijt 28
Eindhoven Jeugd2
36
D.F. Janssen
Groenstraat 45
Oss
Dames
42
R. Heymans
Dreef 24
Grave
Jeugd1
44
P. van Oss
Dreef 24
Oss
Jeugd1
Figuur 1 Voorbeeld van een relationele database met onderling gerelateerde gegevenstabellen
Er zijn tegenwoordig RDBMS’s in velerlei smaken en soorten verkrijg baar. Ze bestaan zowel voor mainframes (b.v.
IBM’s DB2), voo r mini-computers (zoals Oracle, Sybase, Ingres, etc.) en microcomputers (zoals dBase, FoxPro,
Paradox, Clipper, Oracle, Acces, SuperBase, etc.). De laatste jaren is het gebruik van SQL ook hoe langer hoe meer
1
Soms wordt echter net bewust zulke data-redundantie ingebouwd om bepaalde (zoek-)processen sneller te laten verlopen.
KUN / Informatica / IS-0-cursus
3
doorgedrongen tot RDBMS’s voor micro -computers (PC’s dus); wel is het SQL van RDBMS’s voor PC’s vaker
slechts ‘beperkt’ te gebruiken (d.w.z. een aantal SQL -opties zijn dan niet beschikbaar).
In ‘volledige’ SQL/RDBMS’s (op mainframes en mini’s) is het aspect van gegevensbeveiliging bijna altijd
ingebouwd. Een database-systeembeheerder kan daarbij via aanvullende SQL-opdrachten aan een gebruiker
toestemming verlenen om bijvoorbeeld gegevens in een (bepaalde) gegevenstabel te raadplegen, toe te voegen, te
wijzigen en/of te verwijderen. Iemand zonder zo' nautorisatie kan daardoor niet aan die gegevens komen.
Data-integriteit moet meestal worden afgedwongen via speciaal geschreven toepassingen voor het gebruikte DBMS.
Daardoor moet het bijvoorbeeld onmogelijk worden, dat iemand een postcode-waarde als ‘76 C9 H’ in de database
kan invoeren, of de gegevens van een bibliotheeklid kan verwijderen, terwijl de betreffende persoon nog een stapel
uitgeleende boeken thuis heeft of nog een boete moet betalen en bij verwijderde lidgegevens nooit meer te
achterhalen zou zijn.
SQL (Structured Query Language)
Het werken met gegevens in een database (of in een geïsoleerd gegevensbestand) is conceptueel gezien uiterst
eenvoudig. Er zijn immers maar vier soorten bewerkingen mogelijk: toevoegen, veranderen, verwijderen en
opzoeken van gegevens.
De taal SQL is zodanig opgezet dat deze vier operaties ermee uitgevoerd kunnen worden. Het is dus niet alleen een
opvraagtaal zoals het ‘query’-deel in de naam suggereert, maar ook een data-manipulatie-taal. Daarnaast bevat SQL
een aantal mogelijkheden voor gegevensbeveiliging en afdwingen van data-integriteit.
SQL hoort bij de zogenaamde vierde generatie-talen (4GL) en gebruikt een beperkt aantal, gemakkelijk aan te leren
Engelse uitdrukkingen. Hij is ontworpen voor gebruik met R(elationele) DBMS’s, waarbij dus koppelingen van
gegevens in de verschillende gegevenstabellen mogelijk is.
Verderop wordt in het tweede deel van dit collegedictaat uitvoerig ingegaan op SQL-1 (de oude SQL-standaard die
rond 1986 werd vastgelegd). We hebben in dit tweede deel grote stukken van het SQL1-manual opgenomen, zoals
dat meegeleverd werd bij een SQL-RDMS (ISQL/DBQ), dat (in de ogen van de huidige computergebruikers) wat
gebruiksonvriendelijk overkomt. 2
SQL-1 biedt voor het ‘beschermen’ van d e integriteit van de gegevens in een database maar weinig mogelijkheden.
SQL-2 (waarvan de standaard is vastgelegd in 1992) biedt ons wat integriteitsbewaking betreft veel meer: concepten
als ‘primary key’, ‘foreign key’ en ‘check’-mogelijkheden vormen enkele van de uitbreidingen van SQL1 naar
SQL2. Daarom is ter afsluting van dit dictaat een stuk over ‘data-integriteits-bewaking’ toegevoegd; mogelijkheden
in dat opzicht van SQL1 en SQL2 worden daarin naast elkaar gezet.
Î ‘Top down’-aanpak voor het opstellen van SQL-queries
Î
Voor een systematisch opstellen van een SQL-query moet je tijdens de analyse van een informatievraag steeds
beginnen met het nagaan van de volgende aspecten (in de hierna genoemde volgorde):
1. Uit welke gegevenstabel(len) komt/komen de gegevens?
2. In welke kolommen staan de gegevens die nodig zijn (lees meestal: die getoond moeten worden)?
3. Wat is het selectiecriterium voor de mee-te-tellen tabelregels (eventueel: join- of subquery-criterium)?
Later komen hier nog verfijnende vragen bij als:
• moet er gegevensgroepering plaatsvinden (zoja: is er een groepsselectiecriterium)?
• moeten er extreme waarden (minimum, maximum) bepaald worden?
• is er een ‘correlated subquerie’ nodig?
• en eventueel nog een paar andere aspecten.
Houd deze top down-aanpak steeds in de gaten wanneer je b.v. als studietaak een SQL-query moet formuleren.
2
Wij zullen het RDBMS uit de SQL1-manual (ISQL/DBQ) níet gebruiken, maar wél hetzij MS-Acces, hetzij Interbase (BDE)
KUN / Informatica / IS-0-cursus
4