DATABASE ontwerpen

2011 ROC ter AA T&T Cees Loomans

[DATABASE ONTWERPEN] Wat moet je weten over database ontwerpen. Wat heb je nodig, wat moet je doen. Wat is het resultaat?

DATABASE ontwerpen

1 januari 2011

Inhoud Introductie ............................................................................................................................................... 4 Enkele basisconcepten ............................................................................................................................ 4 Databasesysteem .................................................................................................................................... 6 Hardware ............................................................................................................................................. 6 Data ..................................................................................................................................................... 8 Software ............................................................................................................................................ 11 Databasemanagementsysteem ..................................................................................................... 11 Toepassingsprogramma 's ............................................................................................................. 11 Gebruikers ......................................................................................................................................... 12 Data-administrator (DA) ................................................................................................................ 12 Database-Administrator (DBA) ...................................................................................................... 12 Toepassingsontwikkelaar (TO) ...................................................................................................... 12 Eindgebruiker (EU)......................................................................................................................... 12 Database management systeem ....................................................................................................... 13 Hoofdfunctionaliteit ...................................................................................................................... 13 Architectuur................................................................................................................................... 16 Vragen deel 1a....................................................................................................................................... 24 Relationele database ............................................................................................................................. 25 Voordelen en mogelijkheden van relationele databases .................................................................. 27 Klantcontactsysteem ......................................................................................................................... 28 Tabellen en de primaire sleutel ......................................................................................................... 29 Gegevens koppelen ........................................................................................................................... 31 De een op veel relatie ....................................................................................................................... 32 De veel op veel relatie ....................................................................................................................... 33 De een op een relatie ........................................................................................................................ 34 Database normaliseren ..................................................................................................................... 36 De eerste normaalvorm (1NF) ........................................................................................................... 37

De derde normaalvorm (3NF) ........................................................................................................... 39 Het definitieve database-ontwerp .................................................................................................... 41 Nog een voorbeeld: webwinkel......................................................................................................... 42 Conclusie ........................................................................................................................................... 42 Het ontwerp proces van een database. ................................................................................................ 44

Hoofdstuk: Introductie

De tweede normaalvorm (2NF) ......................................................................................................... 38

2

DATABASE ontwerpen

1 januari 2011

Scope ................................................................................................................................................. 46 Logisch Ontwerp ................................................................................................................................ 46 0-de normaal vorm :

Onderken alle data-elementen (scope)................................................... 46

1-de normaal vorm: Zoek de sleutel waarmee alle data op te zoeken is .................................. 47 2-de normaal vorm: Maak alle data afhankelijk van de gehele sleutel. ........................................... 47 3-de normaal vorm:

Maak alle data afhankelijk van niets dan de sleutel. .................................. 48

Maak een ERD ................................................................................................................................... 49 De Technische Optimalisatie: ................................................................................................................ 51 Het doel ............................................................................................................................................. 51 Optimalisatiestappen ........................................................................................................................ 51 Ranges ........................................................................................................................................... 52 Denormaliseer ............................................................................................................................... 53 Combineer tabellen ....................................................................................................................... 53 Sla afgeleide gegevens op ............................................................................................................. 54 Voeg indexen toe........................................................................................................................... 54 Sorteer ........................................................................................................................................... 55 Implementatie ....................................................................................................................................... 56 Maak tabel ......................................................................................................................................... 56 Extra kolom........................................................................................................................................ 56 Maak Index ........................................................................................................................................ 56 Verklarende woordenlijst ...................................................................................................................... 58

Hoofdstuk: Introductie

Vragen deel 1b ...................................................................................................................................... 62

3

DATABASE ontwerpen

1 januari 2011

Introductie In deze cursus leer je een database ontwerpen. Een database is een verzameling gegevens die bij elkaar horen. Databasesystemen zijn programma's die deze gegevens opslaan en beschikbaar maken. Er is dus een verschil tussen een database en een databasesysteem. De database is de verzameling gegevens en regels om deze gegevens op te vragen. Een databasesysteem is het geheel van programma’s en hardware waarmee de gegevens uit de database kunnen worden opgevraagd, veranderd of worden gewist. Veel computerprogramma's maken gebruik van een databasesysteem om gegevens te beheren. Je maakt zelf waarschijnlijk dagelijks gebruik van databasesystemen. Als je geld opneemt bij de pinautomaat raadpleeg je, via het databasesysteem van de bank, de database van de bank. Als je websites op internet bezoekt raadpleeg je een database waar (o.a) paginateksten in op zijn geslagen, als je een nieuw paspoort aanvraagt wordt ongetwijfeld een database met persoonsgegevens geraadpleegd, etc. Vaak wordt met een database ook het database systeem bedoeld. Alleen als het nodig is zullen we het verschil aangeven. In deze cursus relationele database ontwerpen leer je zelf een database ontwerpen volgens het relationeel model. Het relationeel model bestaat uit een aantal aanbevelingen die je toe kan passen op het ontwerp van je database. Het beschrijft hoe gegevens logisch en efficiënt georganiseerd kunnen worden in tabellen en hoe gegevens uit verschillende tabellen gekoppeld kunnen worden. De meeste databases zijn tegenwoordig relationeel. Als leidraad voor deze cursus is de ontwikkeling van een eenvoudige database voor een klant contactsysteem genomen. We gaan daartoe tabellen ontwerpen voor het opslaan van klantgegevens en 'contactmomenten'. Om de principes in deze cursus wat te verhelderen zijn er voorbeelden opgenomen.

Computersystemen worden tegenwoordig gebruikt voor de automatisering van allerlei aspecten van menselijke activiteit. Een belangrijke activiteit, die niet meer is weg te denken in de huidige maatschappij, is het automatisch beheren van gegevens met het oog op latere verwerking en hergebruik. In eenvoudige gevallen komt dit louter neer op het bijhouden van gegevens voor later onderzoek. In meer geavanceerde toepassingen mondt dit uit in complexe taken voor de ondersteuning van diverse activiteiten binnen een onderneming. Een goed (of slecht?) voorbeeld hiervan is het zoekgedrag van de gebruikers van Google dat o.a. gebruikt wordt voor het gericht toezenden van advertenties (waarmee Google zijn geld verdient)

Hoofdstuk: Introductie

Enkele basisconcepten

4

DATABASE ontwerpen

1 januari 2011

Naast het doorzoeken, zijn het toevoegen, verwijderen, aanpassen en consistent houden van de gegevens typisch basistaken die voorkomen bij gegevens beheer. We maken hierbij duidelijk onderscheid tussen ‘data’ (of gegevens) en ‘informatie’. Met data bedoelen we ‘gegeven feiten’. Zo zijn cijfers, getallen, symbolen, karakters en woorden die je dagelijks tegenkomt verschillende vormen van data. Informatie verwijst daarnaast ook naar de betekenis van de gegevens: Je beschikt ook over de betekenis die een bepaalde gebruiker of groep van gebruikers toekent aan die data. Hebben we het over informatiebeheer dan is het verschil tussen ‘data’ en ‘informatie’ belangrijk. De huidige computersystemen kunnen bijna allemaal alleen maar op een efficiënte manier data beheren. Dit leidt dan ook rechtstreeks naar de term ‘database’ met de volgende omschrijving: Een database is een verzameling van persistente data Persistent geeft aan dat de gegevens gedurende een bepaalde tijd worden opgeslagen in een permanent geheugen van een systeem. Het gaat niet verloren bij het uitschakelen van het systeem. De gegevens blijven meestal opgeslagen tot ze expliciet, door tussenkomst van een gebruiker of toepassing worden gewist. Wat betreft het beheer van de betekenis van de gegevens, met andere woorden het beheren van informatie of de semantiek , is de beschikbare technologie momenteel nog ontoereikend. Onderzoek richt zich tegenwoordig op het komen tot semantisch rijkere gegevensmodellen met als doel systemen te ontwikkelen die volwaardige ‘informatiebases’ in plaats van ‘databases’ kunnen beheren.

Hoofdstuk:

Een computersysteem voor het beheer van databases wordt een databasesysteem genoemd.

5

DATABASE ontwerpen

1 januari 2011

Databasesysteem In een databasesysteem onderscheiden we globaal genomen drie hoofdcomponenten: de hardware, de data (gegevens) en de software. Daarnaast is het belangrijk om ook de gebruikers van het systeem te beschouwen. In de figuur hiernaast zijn deze schematisch voorgesteld.

Hardware Hoewel het zeker niet de bedoeling is om in dit dictaat veel aandacht te besteden aan hardwareaspecten, bespreken we toch in het kort de belangrijkste hardwarecomponenten binnen een databasesysteem. Essentieel zijn de central processing unit (CPU) en het computergeheugen waarin de database wordt opgeslagen. De CPU kan fysiek bestaan uit één of meer processoren en zorgt voor het uitvoeren van de software-instructies. Om redenen van onder meer efficiëntie en implementeerbaarheid is het geheugen van een computer hiërarchisch georganiseerd. Daarbij onderscheiden we twee hoofdcategorieën: Primair geheugen. Geheugens uit deze categorie zijn rechtstreeks aanspreekbaar door de CPU. Het primaire geheugen staat fysiek het dichtst bij de CPU en is daarom het snelst toegankelijk. De geheugencapaciteit is echter beperkt. Tot het primaire geheugen behoren:



Statisch RAM -geheugen (Random Access Memory). Statisch RAM -geheugen wordt ook wel het cachegeheugen genoemd en wordt door de CPU in hoofdzaak gebruikt om de uitvoering van programma's te versnellen. Statisch RAM-geheugen is het snelste, doch tegelijk het duurste geheugen binnen een databasesysteem . Dynamisch RAM-geheugen. Dit geheugen is het belangrijkste werkgeheugen voor de CPU en noemen we daarom ook wel het hoofdgeheugen. We gebruiken het voor de tijdelijke opslag van data en programmacode. Het voordeel van dynamisch RAM-geheugen is de lagere kostprijs. Nadeel is echter de tragere werking vergeleken met statisch RAM-geheugen.

Vaak worden er in het primaire geheugen minstens twee zogenaamde databasebuffers opgezet. Terwijl de inhoud van de ene buffer wordt ingelezen of weggeschreven, kan de CPU tegelijkertijd (parallel) de data uit de andere buffer verwerken. Een direct gevolg hiervan is dat de hoeveelheid data die per lees- of schrijfoperatie wordt overgedragen van en naar het primaire geheugen gelijk is aan de grootte van een databasebuffer. Het primaire geheugen heeft een tijdelijk karakter. Hierdoor is het niet bedoeld als hoofdopslagmedium voor databases. Daar een dynamisch RAM -geheugen van 8 tot 16 Gigabytes op één enkel computersysteem geen uitzondering meer is, bestaan er databases die integraal in het

Hoofdstuk: /Databasesysteem



6

DATABASE ontwerpen

1 januari 2011

RAM-geheugen zijn opgeslagen. Vaak gaat het daarbij om toepassingen waarbij extreem hoge overdrachtsnelheden zijn vereist, wat bijvoorbeeld het geval is bij databases voor telefooncentrales. Uiteraard dienen bij dergelijke databases zeker veiligheidsvoorzieningen tegen dataverlies te worden getroffen, zoals het bijhouden van een kopie van de database in een permanent geheugen. Algemeen geldt dat hoe meer dynamisch RAM -geheugen je tot je beschikking hebt en hoe meer werkruimte (voor databasebuffers) je hierin voor het databasesysteem reserveert, hoe beter de prestaties van het databasesysteem zullen zijn. Een speciale vorm van geheugen dat sommigen eveneens tot het primaire geheugen rekenen, is het zogenaamde flashgeheugen, waarbij EEPROM-technologie (Electrically Erasable Programmabie Read -Only Memory) wordt gebruikt. Voorbeelden van (kleine) databases op flashgeheugen zijn de datacollecties in mp3spelers, digitale fototoestellen, pda's en camera's. Secundair geheugen. Gegevensdragers uit deze categorie hebben gewoonlijk een veel grotere opslagcapaciteit, zijn goedkoper, maar hebben een veel tragere gegevenstoegang. Data in het secundaire geheugen kan niet rechtstreeks worden verwerkt door de cpu, maar moeten hiertoe eerst naar een databasebuffer worden gekopieerd. Deze datastromen zijn gevisualiseerd in figuur hiernaast. Tot het secundaire geheugen behoren: De magneetschijfgeheugens. Deze geheugens zijn opgebouwd uit één of meer roterende schijven die langs één of twee zijden beschrijfbaar zijn. Elke schijf is door de fabrikant onderverdeeld in cirkels die we tracks noemen. Bij verschillende schijven vormen de tracks die zich op een gelijke afstand van het middelpunt van de schijf bevinden een cilinder. De tracks zijn op hun beurt onderverdeeld in sectoren. Bij het formatteren, verdeelt het besturingssysteem, bijvoorbeeld Windows, Linux of Unix, de sectoren onder in pagina's van gelijke grootte. Elke pagina bestaat uit een vast aantal sectoren. De databasebuffers uit het primaire geheugen worden even groot gekozen als de grootte van een pagina. Daardoor wordt per lees- en schrijfoperatie één pagina gekopieerd. Om een pagina te kunnen lezen of beschrijven moet deze vooraf worden gelokaliseerd. Hierbij moet rekening worden gehouden met drie tijdspannes: de tijd die nodig is om de lees/schrijfkoppen van het opslagmedium te verplaatsen naar de juiste track of cilinder, de tijd die nodig is om de schijf te roteren tot aan het begin van de eerste sector van de betreffende pagina en de tijd die nodig is om de data over te brengen. De eerste twee tijdspannes duren vaak veel langer dan de derde en vormen een prestatie-'bottleneck'. Om prestatieredenen is het belangrijk dat we de data in de schijfbestanden zodanig organiseren dat het aantal paginatransfers minimaal wordt gehouden. Bij de bestandsorganisatie maken we een onderscheid tussen primaire en secundaire bestandsorganisatie. Primaire bestandsorganisatie heeft betrekking op de manier waarop

Hoofdstuk: /Databasesysteem



7

DATABASE ontwerpen

1 januari 2011

een database fysiek wordt opgeslagen. De data kan daarbij bijvoorbeeld al dan niet geordend worden. Secundaire bestandsorganisatie heeft tot doel de toegang tot de fysiek opgeslagen database verder te optimaliseren door extra data over bijvoorbeeld de locatie van gegevens en verbanden tussen gegevens bij te houden. Indexen zijn voorbeelden hiervan, we bespreken ze indien nodig later nog. Magneetschijfgeheugens worden momenteel het meest gebruikt als secundair geheugenmedium voor databases. De cd-rom en dvd-geheugens. Dit zijn recentere opslagmedia. In configuraties waarbij verschillende van dergelijke geheugens worden samengebracht in een soort 'jukebox' systeem kunnen grote opslagcapaciteiten worden bereikt. Door hun minder goede schrijffaciliteiten zijn cd's en dvd's eerder geschikt voor de opslag van databases die enkel moeten kunnen worden geraadpleegd. Voorbeelden zijn digitale telefoongidsen, digitale archieven, geografische databases en digitale woordenboeken en encyclopedieën. Een belangrijk minpunt is de beperkte duurzaamheid van zelf beschreven cd's en dvd's, waardoor we hun bruikbaarheid voor kritische toepassingen op zijn minst in twijfel moeten trekken. De magneetbandgeheugens. Magneetbandgeheugens worden in hoofdzaak gebruikt voor archivering en back-up. Omwille van hun fysieke karakteristieken werken magneetbandgeheugens alleen efficiënt bij sequentiële toegang (in volgorde van fysieke opslag) tot de data. Ook hier kan een soort 'jukebox' -systeem worden gebruikt om de opslagcapaciteit te vergroten. Een voorbeeld van een database op magneetbandgeheugen is het EOS-archief (Earth Observation Satellite) van de NASA. Sommige auteurs categoriseren magneetbandgeheugens als tertiair geheugen.

Data Centraal in een databasesysteem staan de databases. Een database wordt meestal in het geheugen gestructureerd opgeslagen als een collectie van records. Elk record is opgebouwd uit één of meer velden die data kunnen bevatten. Deze velden zijn vastgelegd in het recordtype van het record en worden elk gekarakteriseerd door een naam en een datatype. Analoog als bij programmeertalen legt het datatype de toegelaten waarden en operatoren voor het veld vast. Naast velddefinities heeft een recordtype ook een naam. Doorgaans bevat een database verschillende records die gegroepeerd zijn

Hoofdstuk: /Databasesysteem

In volgende figuur stellen we de hiërarchie van het computergeheugen voor. De magneetbandgeheugens zijn hierin apart vermeld als tertiair geheugen. Aan de top van de hiërarchie staat het snellere, duurdere geheugen met doorgaans kleinere opslagcapaciteit. De basis wordt gevormd door het tragere, minder snel toegankelijk geheugen met een grotere opslagcapaciteit.

8

DATABASE ontwerpen

1 januari 2011

volgens diverse recordtypes. In het secundaire geheugen worden de records opgeslagen in bestanden. Meestal bevat een bestand een aantal records. Hoewel dit geen vereiste is, kunnen er in hetzelfde bestand records van verschillende recordtypes zijn opgeslagen. In de praktijk kan het aantal records in een database variëren van enkele tot miljoenen. Daarnaast kan een databasesysteem tegelijkertijd instaan voor het beheer van meerdere databases. Voorbeeld De figuur op de volgende bladzijde bevat een voorstelling van de recordtypes en records uit een voorbeelddatabase voor schilderkunst. De database bestaat uit een collectie van records van drie recordtypes: 'Schilderij', 'Artiest' en 'Eigenaar'. Omwille van de leesbaarheid zijn de records geordend per recordtype en worden de veldnamen voorafgaand aan de records herhaald. De gebruikte datatypes zijn:



CHAR(n) voor het modelleren van karakter strings van lengte n. INTEGER voor het modelleren van gehele getallen (die geïnterpreteerd worden als jaartallen). REAL voor het modelleren van reële getallen.

Besturingssystemen zoals MVS, Windows, Linux en Unix, bieden geen oplossing voor het vastleggen en het behandelen van de soms complexe verbanden die kunnen bestaan tussen de data in een bestand. Als ze voorkomen, zijn dergelijke verwantschappen en verbanden echter zeer belangrijk voor de gebruikers en toepassingsprogramma's: ze verduidelijken de semantiek (betekenis) van de verschillende entiteiten (beschreven dingen) uit het toepassingsdomein. Databases moeten hierom zodanig zijn opgebouwd, dat alle verbanden die voorkomen tussen data op een efficiënte manier, liefst zonder verlies aan semantiek worden weergegeven. Dubbele opslag, met andere woorden overtollige data, moeten we daarbij zo veel mogelijk vermijden. Een database zien we als een geïntegreerd geheel. Data die in de reële wereld samenhangen, brengen we bij

Hoofdstuk: /Databasesysteem

 

9

DATABASE ontwerpen

1 januari 2011

voorkeur onder in dezelfde database en de verbanden tussen deze data geven we daarbij zo goed mogelijk weer. Zo wordt bijvoorbeeld het verband tussen een schilderij en een schilder in de voorbeelddatabase voor schilderkunst weergegeven door middel van het veld 'Artiest' van het recordtype 'Schilderij' en het veld 'Naam' van het recordtype 'Artiest'. Een 'Schilderij' -record met waarde 'Monet' voor het veld 'Artiest' wordt hierbij gerelateerd met het 'Artiest' - record met dezelfde veldwaarde voor 'Naam'. In plaats van te werken met aparte datacollecties voor schilderijen en artiesten, worden beide geïntegreerd in één database.

Hoofdstuk: /Databasesysteem

Het geïntegreerd zijn van de data is een belangrijk kenmerk van elke database.

1 0

DATABASE ontwerpen

1 januari 2011

Software Om de hardware te kunnen aansturen en databases te kunnen opzetten, beheren en efficiënt te gebruiken, zijn computerprogramma's nodig. De belangrijkste software in een databasesysteem is het databasemanagementsysteem. Het databasemanagementsysteem (dbms) is de softwarecomponent van een databasesysteem die instaat voor het beheer van de databases. Voorbeelden zijn de opensource-systemen MySQL en Postgress en de commerciële systemen DB2, SQL Server, Oracle, Sybase en MS Access. Naast het dbms zijn er vaak nog andere softwarecomponenten die deel uitmaken van het databasesysteem. Denk daarbij aan allerhande (in huis ontwikkelde) toepassingsprogramma's, tools, communicatiesoftware en software voor datawarehousing, data-analyse of automatische rapportgeneratie. Databasemanagementsysteem Het databasemanagementsysteem is bedoeld voor het beheer van de databases en schermt daarvoor de gebruikers af van de technische details van hardware en bestandsorganisatie. Alle interactie met een database gebeurt via het dbms: als een gebruiker data wil opzoeken, toevoegen, wijzigen of verwijderen, voeren we dit in het dbms in, dat vervolgens zorgt voor de correcte afhandeling van de gevraagde operatie. Bij een zoekopdracht toont het dbms op een overzichtelijke manier de resultaten aan de gebruiker.

Hoofdstuk: /Databasesysteem

Toepassingsprogramma 's Je kunt op indirecte wijze werken met een database via toepassingssoftware. De toepassingssoftware zorgt eerst voor de verbinding met het dbms en de toegang tot de relevante databases. Daarna zal het de gebruikersacties die betrekking hebben op de database vertalen naar database operaties, die dan voor de verwerking worden doorgestuurd naar het dbms. Bij een online toepassing verwerkt het dbms de opdrachten realtime. Bij een batch-toepassing worden de opdrachten op een meer geschikt tijdstip verwerkt, bijvoorbeeld ’s nachts of tijdens het weekend wanneer het systeem minder druk bezet is. Na uitvoering van de operaties stuurt het dbms eventuele resultaten door naar het toepassingsprogramma, dat de resultaten toont aan de gebruiker. Uiteraard dienen ook eventuele fouten te worden opgevangen.

1 1

DATABASE ontwerpen

1 januari 2011

Gebruikers Met een gebruiker bedoelen we een persoon die op een of andere manier interactie met het databasesysteem heeft. Als er verschillende gebruikers zijn, worden er meestal gebruikersprofielen gemaakt. De beheerder kan dan bepalen wie welke bevoegdheden heeft. De belangrijkste gebruikerprofielen zijn: Data-administrator, database-administrator, toepassingsontwikkelaar en eindgebruiker. Sommige van deze gebruikersprofielen zijn standaard vastgelegd in het dbms. De meeste systemen staan het toe om eigen gebruikersprofielen te maken. Data-administrator (DA) Dit profiel is bedoeld voor personen die in een onderneming centraal verantwoordelijk zijn voor de data en deze hebben meestal een hogere kader functie in de onderneming. Hij of Zij beslist welke data, in welk formaat, in welke database wordt opgenomen en hij of zij is ook verantwoordelijk voor de registratie van deze informatie. Verder legt hij of zij de bedrijfsstrategie vast voor de toegang, de gebruikersprofielen, de beveiliging, verwerking en communicatie van de data en de prioriteiten ingeval van toegangsconflicten. Hij of zij speelt een grote rol bij het database ontwerp. De functie is van het grootste belang bij grote conversies of aanpassingen aan de databases, zoals die rond het millenium (rond het jaar 2000) en die bij de invoering van de euro. Database-Administrator (DBA) Het database administrator profiel is voor die personen die technisch verantwoordelijk zijn voor de implementatie en onderhoud van de databases. Uiteraard overlegt hij of zij daarvoor met de DataAdminstrator en volgt hun beslissingen strikt op. Ook zorgt hij voor het herstellen van de databases na het falen ervan (backup en restore), de consistentie van de databases, het monitoren van de prestaties en het goed functioneren van het database systeem. In grote ondernemingen wordt de Database-Administrator taak meestal over meerdere personen verdeeld.

Eindgebruiker (EU) De eindgebruikers zijn de mensen die sporadisch of voor de uitvoering van hun dagelijkse taken werken met de database. Afhankelijk van de manier waarop de interactie met de database gebeurt, maken we een onderscheid tussen gewone eindgebruikers en geavanceerde eindgebruikers.  Gewone eindgebruiker. De gewone eindgebruiker werkt op een vrij eenvoudige manier met de database. Meestal gebeurt dit via een toepassingsprogramma, waarbij de eindgebruiker volledig van de databasestructuur is afgeschermd. In sommige gevallen kan de interactie plaatsvinden via heel eenvoudige instructies die door de eindgebruiker zelf worden

Hoofdstuk: /Databasesysteem

Toepassingsontwikkelaar (TO) De toepassingsontwikkelaar zorgt voor de ontwikkeling van de (in huis ontwikkelde) toepassingsprogramma’s die het toelaten om op een interactieve, gebruikersvriendelijke manier te werken met het databasesysteem. Duidelijk is dat hij of zij zal moeten overleggen met de DatabaseAdministrator, om de structuur en implementatie van de databases en de gebruikte parameterinstellingen voor primaire en secundaire bestandsorganisatie te kennen, en om een optimale interactie met de database te kunnen realiseren. Voor het uittesten van de ontwikkelde software moet de toepassingsontwikkelaar beschikken over een werkkopie van de databases.

1 2

DATABASE ontwerpen

1 januari 2011

ingevoerd en die moeten zijn opgebouwd volgens de grammatica van de gebruikte databasetaal. Door het minder complexe, meestal voorgeprogrammeerde, karakter is de interactie van een gewone eindgebruiker gewoonlijk vrij goed voorspelbaar. Bij de optimalisatie van het databasesysteem moet deze voorspelbaarheid door de DBA optimaal worden benut.  Geavanceerde eindgebruiker. De geavanceerde eindgebruiker werkt vrijwel altijd met de database via complexere instructies uit de databasetaal of via verfijnde dataanalysesoftware. Typerend zijn het minder routinematige gebruik en de enorme variaties in de vraagstelling. Vaak zal de complexiteit van de vraagstelling het benutten van alle mogelijkheden vereisen. Dit alles maakt dat de interactie vrijwel niet te voorspellen is.

Database management systeem In deze paragraaf bespreken we in detail de belangrijkste softwarecomponent van een databasesysteem, namelijk het databasemanagementsysteem (dbms). Eerst staan we stil bij de functionaliteit van een dbms. Daarna lichten we de architectuur kort toe. Hoofdfunctionaliteit De belangrijkste taken van een dbms zijn de definitie, manipulatie en constructie van databases. Databasedefinitie Bij de definitie van een database moeten zowel de databasestructuur als de semantische regels voor de database worden vastgelegd. Semantische regels zijn nodig om de integriteit van de database te helpen garanderen. Integriteit slaat op de correctheid van de data in de database. Een database modelleert immers informatie en moet op elk moment een zo goed mogelijke weergave zijn van de realiteit. De data moeten een consistent geheel vormen.

Incorrectheid van data kan ook optreden als er geen redundantie meer mogelijk is. Als bijvoorbeeld voor het geboortejaar ('Geboren') een waarde '2900' wordt opgegeven, of als er voor een schilderij een schilder wordt opgegeven die niet voorkomt in de database, of als het geboortejaar ('Geboren') van een artiest zijn sterfjaar ('Gestorven') niet voorafgaat. Dergelijke vormen van incorrectheid kunnen worden voorkomen door semantische regels vast te leggen voor de database. Zo kan bijvoorbeeld de geldigheid van een geboortejaar worden gecontroleerd met de regels 'Geboren=heden-12' 'Geboren= Gestorven-12'

Hoofdstuk: /Databasesysteem

Als het mogelijk is, moet de database-integriteit worden gecontroleerd door het dbms. De database moet daarom zo zijn ontworpen dat er geen tegenstrijdige data in kunnen voorkomen. Deze zijn immers een potentiële bron van inconsistentie. Als bijvoorbeeld in de schilderkunstdatabase ook in het recordtype 'Schilderij' het geboortejaar van de schilder zou worden bijgehouden, bestaat het risico dat bij een eventuele correctie niet alle kopieën worden gecorrigeerd, met als gevolg dat de database inconsistent wordt doordat ze conflicterende gegevens bevat.

1 3

DATABASE ontwerpen

1 januari 2011

Hierbij is 'heden' het huidig jaartal en er wordt aangenomen dat geen schilderijen in de database mogen worden opgenomen van artiesten die jonger zijn dan 12 jaar. Het dbms moet erop toezien dat databases steeds voldoen aan de opgelegde semantische regels. Niet elke vorm van in correctheid wordt gecontroleerd door het dbms. Als we bijvoorbeeld een verkeerd geboortejaar invoeren, bijvoorbeeld 1861 in plaats van 1860, kan het systeem dit niet controleren. Mede hierom is een autorisatiesysteem met gebruikersprofielen vereist: slechts gebruikers of gebruikersgroepen die verantwoordelijk zijn voor de inhoud van de database mogen data in de database invoeren, aanpassen of verwijderen. Het is immers bekend dat gebruikers minder nauwkeurig werken naarmate ze minder verantwoordelijk zijn voor de data, of ze er minder profijt van hebben dat de database correct is. Uiteraard kan een autorisatiesysteem niet uitsluiten dat ook deze personen fouten maken. Databasemanipulatie Voor de manipulatie van een database moeten de noodzakelijke operatoren worden gecreëerd voor het efficiënt toevoegen, verwijderen en aanpassen van data en het efficiënt doorzoeken van databases. De primaire en secundaire bestandsorganisatie moeten op een optimale datamanipulatie zijn afgesteld. Dit vereist de nodige prestatie-monitoring en expertise van de DBA. Een dbms kan uitgerust zijn met een databasemanipulatietaal. De grammatica van deze taal schrijft voor hoe instructies voor databasemanipulatie moeten zijn opgebouwd. Gebruikers kunnen de taal interactief inzetten voor het wijzigen of doorzoeken van de database. Daarnaast kunnen taalconstructies worden ingebed in programma code of kunnen er communicatieprotocollen zijn aangemaakt die het mogelijk maken om vanuit (in huis ontwikkelde) toepassingsprogramma's te werken met het dbms en de databases. De voorschriften voor de definitie en de manipulatie van databases zijn vastgelegd in een zogenoemd data(base)model.

Het dbms moet ervoor zorgen dat de aangemaakte databases efficiënt worden bijgehouden in het geheugen, zonder dat de eindgebruikers en toepassingsontwikkelaars zich hoeven te bekommeren over de fysieke opslagdetails. Hiertoe moet het dbms faciliteiten bieden voor databaseconstructie die het mogelijk maken om een gepaste primaire bestandsorganisatie op te zetten. De databasestructuur die is vastgelegd bij de databasedefinitie moet daarbij worden vertaald naar een recordstructuur . Het volledige beheer van de fysieke opslag valt onder de controle van het dbms. Vaak zal de databaseconstructie via een gepaste parameterinstelling door de DBA kunnen worden geconfigureerd. Andere functionaliteit Naast de hiervoor vermelde hoofdtaken kan een dbms verder nog een oplossing bieden voor het delen van dezelfde data, de beveiliging van de data, een optimaal systeemgedrag en administratie en controle.

Hoofdstuk: /Databasesysteem

Databaseconstructie

1 4

DATABASE ontwerpen

1 januari 2011

Delen van dezelfde data Gebruikers of software moeten dezelfde data kunnen delen en eventueel tegelijkertijd kunnen aanpassen of doorzoeken. Hiertoe moet een dbms zijn uitgerust met faciliteiten voor simultane toegang tot de database. Het delen van een database moet volledig transparant zijn voor de gebruikers en toepassingsprogramma's. Dit wil zeggen dat een gebruiker of software steeds op een geïsoleerde manier met de database moet werken, net alsof er geen gedeelde toegang tot de database bestaat. Het efficiënt delen van databases houdt ook in dat een dbms toegangsconflicten moet kunnen verhinderen. Een klassiek voorbeeld van een toegangsconflict is het probleem van dubbele boeking, die kan optreden wanneer twee gebruikers tegelijkertijd dezelfde plaats in een theater, trein of vliegtuig boeken. Beveiliging van de data Een dbms moet in staat zijn om de data die het beheert te beschermen tegen ongeoorloofd gebruik en tegen fysiek falen van het databasesysteem. Als de database toegankelijk is voor verschillende gebruikers, kan het gebeuren dat niet iedereen toegang mag hebben tot de volledige database. Bij ongeoorloofde toegang kunnen gebruikers, al dan niet opzettelijk, schadelijke acties uitvoeren op de database. Het dbms moet de data kunnen afschermen en beveiligen tegen ongeoorloofde manipulatie en gebruik. Een beveiligingsmethode kan gebaseerd zijn op een autorisatiesysteem met wachtwoordbeveiliging en gebruikersprofielen. Om te voorkomen dat de inhoud van een database via het rechtstreeks af scannen van het geheugen zou kunnen worden gelezen, kan de data versleuteld worden weggeschreven. Merk op dat de hiervoor besproken semantische regels voor de integriteit van de database kunnen worden gezien als een middel om de data te beveiligen tegen schadelijke operaties door geoorloofd gebruik: incorrecte gegevens dienen daarbij zo veel mogelijk te worden gedetecteerd en geweerd.

Regelmatig een back-up maken van de database is belangrijk voor het herstelproces, maar is op zich onvoldoende omdat een beschadigde database bij voorkeur moet worden hersteld tot haar toestand net vóór het falen en niet tot haar toestand net vóór de meest recentste back-up. Optimaal systeemgedrag Voorgaande faciliteiten moeten worden gerealiseerd zonder veel nadeel voor de gebruikers: een dbms moet in staat zijn om grote hoeveelheden data te beheren en daarbij de gebruikers nog redelijke toegangstijden garanderen. Een belangrijk deel van de techniek in een dbms is daarom gericht op het optimaliseren van de datatoegang. Een efficiënte secundaire bestandsorganisatie is daarbij noodzakelijk. Veelal zal de secundaire bestandsorganisatie via een gepaste

Hoofdstuk: /Databasesysteem

Het fysiek falen van een databasesysteem kan bijvoorbeeld een externe oorzaak hebben, zoals stroomuitval of veroorzaakt worden door een hardwaredefect, bijvoorbeeld een defect opslagmedium. De beveiligingsfaciliteiten van een dbms moeten in geval van fysiek falen het verlies aan data zo veel mogelijk beperken.

1 5

DATABASE ontwerpen

1 januari 2011

parameterinstelling door de DBA kunnen worden geconfigureerd. Een ander aspect is de optimalisatie van de uitvoering van de instructies van de gebruikers. Verschillende gebruikers die het systeem tegelijkertijd gebruiken, hebben soms conflicterende prestatie-eisen die onmogelijk allemaal tegelijk kunnen worden gerealiseerd. Zo kan de vraag voor directe analyse resultaten door een manager het systeem kortstondig belasten, wat tijdelijk funest kan zijn voor de prestatie van bijvoorbeeld de dataregistratie bij de productie. Wanneer dergelijke conflicten zich voordoen, moet de DBA - steeds in overleg met de DA - gepast kunnen ingrijpen en het meest geschikte compromis voor de onderneming uitwerken. Administratie en controle Een dbms moet de gebruikers - dikwijls de DBA's - voldoende faciliteiten bieden voor systeemadministratie en systeemcontrole. Tot de belangrijkste administratie- en controletaken behoren onder meer: het aanpassen van de secundaire bestandsorganisatie aan de actuele noden van de onderneming, het bijhouden en analyseren van prestatiestatistieken, het beheer van gebruikersprofielen en -accounts, het aanmaken van back-ups, het aanleggen van een logbestand voor beveiligingsdoeleinden (hierbij worden de acties van gebruikers in meer of minder detail geregistreerd) en het onderhoud van het databasesysteem.

De hedendaagse databasemanagement systemen zijn vrijwel allemaal gebouwd volgens een 'drielagen' -architectuur die geïnspireerd is op de architectuur die is voorgesteld door de ANSII SPARC dbms-studiegroep . Deze architectuur zie je in naast liggende figuur. Dit betekent echter niet dat er geen andere architecturen bestaan en worden gebruikt. De drie lagen van deze structuur noemen we respectievelijk de interne laag, de logische laag en de externe laag. De interne laag De interne laag (ook opslaglaag genoemd) staat het dichtst bij de fysieke opslag van de database. In deze laag beschrijven we de databaserecords die eerder werden geïntroduceerd. Daarbij besteden we onder meer aandacht aan structuur, manier van opslag en toegangspaden. Databaserecords worden georganiseerd in bestanden. De structuur van de records wordt vastgelegd in recordtypes,

Hoofdstuk: /Databasesysteem

Architectuur De implementatie van een dbms gebeurt volgens een architectuur die bestaat uit verschillende lagen, gaande van de fysieke gegevensopslag naar de logische entiteiten (bijvoorbeeld tabellen en objecten) die door de toepassing worden gebruikt. Zoals kort werd aangegeven in het historische overzicht is de belangrijkste reden voor een 'meerlagen' -architectuur het verkrijgen van dataonafhankelijkheid.

1 6

DATABASE ontwerpen

1 januari 2011

de manier van opslag wordt bepaald door de keuze van de primaire bestandsorganisatie en de toegangspaden worden mede bepaald door de keuze van de secundaire bestandsorganisatie. Records van hetzelfde recordtype kunnen een vaste of variabele lengte hebben. Een variabele lengte kan onder meer te wijten zijn aan de volgende feiten: 

Eén of meer recordvelden hebben een datatype met domeinwaarden van een variabele lengte. Zo zou het veld 'Naam' van het recordtype: RECORDTYPE Artiest (Naam:CHAR(30); Voornaam:CHAR(20); Geboren:INTEGER; Gestorven:INTEGER) uit het voorbeeld van de schildersdatabase een geassocieerd datatype VARCHAR kunnen hebben, waarvan de domeinwaarden bestaan uit een variabel aantal karakters (in plaats van het datatype CHAR ( 30) , met domeinwaarden van exact dertig karakters).





Eén of meer recordvelden zijn meerwaardig. Een domeinwaarde van een meerwaardig recordveld is een collectie die kan bestaan uit een vast of variabel aantal elementen. Bijvoorbeeld, het recordtype 'Artiest' zou een meerwaardig veld 'Hobby' kunnen hebben met geassocieerd datatype SET (VARCHAR) , waarvan elke waarde een verzameling van hobby's voorstelt. Eén of meer velden van het recordtype zijn optioneel. Het is niet verplicht om een waarde op te geven voor optionele velden. Zo zou bijvoorbeeld kunnen worden vastgelegd dat de velden 'Geboren' en 'Gestorven' van het recordtype 'Artiest' optioneel zijn.

Voor een recordtype met records van vaste lengte wordt ook de recordlengte weggeschreven in de file header. Het dbms kan dan de eerste byte van een recordveld lokaliseren op basis van de relatieve positie van deze byte ten opzichte van de startbyte van het record. Een voordeel van het werken

Hoofdstuk: /Databasesysteem

Om de records van een bestand te beschrijven, kan vooraan in het bestand een zogenoemde file header worden opgenomen, waarin onder meer de manier van opslag van de voorkomende recordtypes is beschreven. Elk record in het bestand kan dan worden voorafgegaan door een recordtype-indicator die verwijst naar de van toepassing zijnde beschrijving.

1 7

DATABASE ontwerpen

1 januari 2011

met records van vaste lengte is hierom de vereenvoudigde recordtoegang. Een nadeel is het inefficiënte gebruik van het geheugen: bij te grote veldlengtes heeft men ongebruikte ruimte, bij te kleine veldlengtes is er niet genoeg ruimte voor de dataopslag met informatieverlies als gevolg. De manier van opslag bij dit voorbeeld werd voor records van het recordtype 'Artiest' geïllustreerd in de volgende figuur (a) . Voor de unieke identificatie van de records werd elk recordtype uitgebreid met een prefix-veld. Dit prefix -veld is vastgelegd door de gekozen primaire bestandsorganisatie. Voor een betere toegankelijkheid werd verder voor elk recordtype een index gedefinieerd. In zijn eenvoudigste vorm kan een index worden gezien als een geordende lijst van koppels van de vorm: < veldwaarde,referentie> Hierbij is veldwaarde een waarde die voorkomt in het veld waarover de index is gedefinieerd. Referentie is een pointer naar de geheugenplaats waar een record met die veldwaarde is opgeslagen. Deze pointer kan bijvoorbeeld het adres (track/cilinder, sector) zijn van de pagina waarin het record zich bevindt. De elementen – koppels- van de index zijn geordend op basis van hun veldwaarde. Dit laat het toe om snel, volgens het betreffende recordveld, te navigeren door de records. Indexen zijn een voorbeeld van technieken die deel uitmaken van de secundaire bestandsorganisatie. Bij een recordtype met records van variabele lengte is er een bijkomende techniek nodig om het einde van de recordvelden aan te duiden. Een bruikbare techniek, die gebaseerd is op het gebruik van separatorkarakters zie je in figuur hierboven (b). Daarbij hebben we de volgende, alternatieve definitie voor het recordtype 'Artiest' gebruikt:

Hierbij hebben de velden 'Naam', 'Voornaam' en 'Hobby' nu een variabele lengte, is 'Hobby' ook een meerwaardig veld en zijn de velden 'Geboren' en 'Gestorven' optioneel. Omdat er optionele velden zijn, werd elke veldwaarde in het record gemerkt met een veldnaamindicator (Vo, V1 ,V2 ,V3 ,V4 en Vs). Verder plaatsen we aan het einde van elk veld een separatorkarakter: voor het laatste veld van het record is dit een 'einderecord' -indicator, voor alle andere velden is dit een 'einde_veld' -indicator. Voor de beschrijving van de meerwaardige velden is er ook een derde soort separatorkarakter, de 'einde_waarde' -indicator, nodig om de respectieve waarden voor het veld van elkaar te kunnen onderscheiden. De separatorkarakters kiezen we zodanig dat ze niet als karakter voorkomen in een toegelaten waarde van een datatype dat door het dbms wordt ondersteund. Variaties en alternatieve beschrijvingstechnieken zijn mogelijk. Zoals is aangegeven in een eerdere figuur ligt de interne laag het dichtst bij de fysieke opslag. Opslagdetails over de primaire en secundaire bestandsorganisatie, die doorgaans onder het beheer

Hoofdstuk: /Databasesysteem

RECORDTYPE Artiest (Naam: VARCHAR ; Voornaam: VARCHAR ; Hobby:SET(VARCHAR); OPTIONAL Geboren:INTEGER; OPTIONAL Gestorven:INTEGER)

1 8

DATABASE ontwerpen

1 januari 2011

van het besturingssysteem vallen, worden niet beschreven in de interne laag. Zo wordt er bijvoorbeeld niet aangegeven op welke fysieke pagina('s) een record is weggeschreven. Evenmin wordt in de interne laag meer detail gegeven over hoe de toegang tot de fysieke pagina's wordt geoptimaliseerd. Dergelijke fysieke opslagdetails zijn heel (besturings)systeemspecifiek en worden buiten de architectuur van een dbms gehouden. De logische laag In de logische laag (ook de gemeenschappelijke laag genoemd) werken we met een abstracte voorstelling van de database. Deze representatie vormt een gemeenschappelijke kijk op de volledige database, zoals deze wordt gezien door alle gebruikers die geen toegangsbeperkingen tot de database hebben. De logische laag houdt alle fysieke opslagdetails verborgen, zodat alle aandacht kan gaan naar de abstracte beschrijving van alle in de database voorkomende entiteiten, verwantschappen tussen entiteiten, gebruikersgedefinieerde operatoren en integriteitsbeperkingen. De abstracte beschrijving van de database dient te gebeuren volgens de voorschriften van het gebruikte databasemodel en noemen we het databaseschema. Naast de operatoren voor de definitie van databaseschema's legt het databasemodel de operatoren vast voor het manipuleren van databases. Op deze manier heeft het databasemodel een zeer belangrijke impact op het soort toepassingen dat het dbms efficiënt kan ondersteunen. Toepassingen moeten de mogelijkheden van het databasemodel optimaal benutten en tegelijkertijd berusten in de beperkingen ervan. De databaserecords uit voorbeeld van de schilderdatabase kunnen in de logische laag gedefinieerd zijn zoals in een eerder figuur te zien, dat is: RECORDTYPE Schilderij (ID:CHAR(3); Naam:CHAR(30); Artiest:CHAR(30); Periode: INTEGER; Waarde:REAL; Eigenaar:CHAR(30) ) RECORDTYPE Artiest (Naam:CHAR(30); Voornaam:CHAR(20); Geboren:INTEGER; Gestorven: INTEGER) RECORDTYPE Eigenaar (Naam:CHAR(30); Plaats:CHAR(20); Land:CHAR(20) )

Hoofdstuk: /Databasesysteem

Naast de structurele aspecten, gedrag- en integriteitaspecten, behandelen we op het logische niveau ook nog andere aspecten, zoals gemeenschappelijke faciliteiten voor beveiliging, falen en herstel en het delen van de database door verschillende gebruikers.

1 9

DATABASE ontwerpen

1 januari 2011

De externe laag Omdat een dbms tegelijkertijd door een aantal gebruikers gebruikt kan worden en elke individuele eindgebruiker meestal slechts een kleine fractie van de database benut, is er ook een externe laag (ook de individuele laag genoemd). Deze laag staat het dichtst bij de toepassingsontwikkelaars en eindgebruikers. In de externe laag worden de zogenoemde (externe) views op de database gedefinieerd. Een view is een kijk op de database die bepaalt hoe de database wordt gezien door een beperkte groep van eindgebruikers en toepassingsontwikkelaars: voor deze gebruikers lijkt het alsof de view de database is. De manier waarop individuele gebruikers de database moeten of wensen waar te nemen, kan immers verschillen van de manier waarop de database logisch is beschreven. Voor een gebruikersgroep kunnen, afhankelijk van de noden en toegangsrechten, verschillende views zijn gedefinieerd. Een view kan toegankelijk zijn voor verschillende gebruikersgroepen. Een view kan onder meer worden aangemaakt voor beveiliging (afschermen van een deel van de gegevens), voor het afleiden van nieuwe data uit de database (daarbij moet worden beschreven hoe de nieuwe data kunnen worden verkregen) of voor dataconversie (om te voldoen aan de gebruikerswensen). Voorbeelden van views uit de externe laag voor de schilderdatabase uit het eerdere voorbeeld zijn: In COBOL-code: 01 Tentoonstellingsitem. 02 ItemNaam PIC X(30). 02 Schilder PIC X(30).

De eerste view in dit voorbeeld is ontworpen voor ontwikkelaars die werken met de COBOLprogrammeertaal. Het databaserecordtype 'Schilderij' wordt erdoor voorgesteld als 'Tentoonstellingsitem'. Alleen de 'Naam' - en 'Artiest' -velden van het recordtype zijn zichtbaar in de view, zij het onder een voor de toepassing meer geschikte naam 'ItemNaam', respectievelijk 'Schilder'. De tweede en derde view zijn ontworpen voor ontwikkelaars die werken met de programmeertaal Java. De tweede view is ook gedefinieerd voor het recordtype 'Schilderij', daar waar de derde view is gedefinieerd over het recordtype 'Artiest'. In de derde view is een nieuw afgeleid veld 'LeeftijdBijSterfte' geïntroduceerd, waarvan de waarde bijvoorbeeld wordt berekend als het verschil 'Gestorven - Geboren' van het sterftejaar en geboortejaar van de 'Artiest'. Dit nieuwe veld is weergegeven als operatie van de

Hoofdstuk: /Databasesysteem

In Java-code: class Schilderij { String Naam; String Schilder; } class Schilder { String Naam; String Voornaam; int LeeftijdBijSterfte( ); }

2 0

DATABASE ontwerpen

1 januari 2011

klasse. De Java-code voor deze operatie maakt deel uit van de implementatie van de klasse' Schilder' en wordt niet getoond in het voorbeeld. Niet alle views hoeven gespecificeerd te zijn in een programmeertaal. Als het databasemodel een eigen DDL (Data Definition Language) heeft, die de definitie van views ondersteunt, gebruiken we deze voor de specificatie. Een voorbeeld is de DDL van de SQL-standaardtaal voor relationele databases. Mappings In de drie hiervoor beschreven lagen wordt enkel gewerkt met intermediaire beschrijvingen van data. De feitelijke data bevinden zich in de fysieke opslag. Wanneer gebruikers of toepassingsprogramma's werken met de database via de externe laag, moeten ze de aangevraagde operaties eerst vertalen naar operaties in de onderliggende logische laag, daarna naar operaties in de interne laag en die ten slotte omzetten naar operaties die inwerken op de fysieke opslag. Omgekeerd moeten de resultaten van een interactie (bijvoorbeeld de resultaten van de zoekopdracht) via respectievelijk de interne laag, de logische laag en de externe laag naar de gebruiker of het toepassingsprogramma worden gecommuniceerd. Voor een gebruiker die werkt met de database via de logische laag, valt de communicatie met de externe laag uiteraard weg. Een interactie via de interne laag is bij veel databasesystemen niet toegestaan en anders voorbehouden aan de DBA. De omzettingsprocessen van data en operaties tussen de drie lagen van de architectuur beschrijven we aan de hand van mappings. Deze zijn in figuur waar de drie lagen structuur in wordt beschreven, aangegeven met dubbele pijlen. Zoals hiervoor al werd vermeld, wordt de correspondentie tussen de interne laag en de fysieke opslag buiten de architectuur gehouden, omdat deze te sterk systeemafhankelijk is. Hierom wordt er in de architectuur geen mapping gemaakt tussen de interne laag en de fysieke opslag. Uiteraard moet het dbms kunnen werken met de fysiek opgeslagen data, wat interactie met het besturingssysteem vereist. Voor elke view in de externe laag is er een 'externe/logische' -mapping gedefinieerd. Deze mapping definieert de correspondentie tussen de view en de logische voorstelling van de database. Zoals je ziet in voorbeeld,kunnen velden daarbij een andere naam of datatype krijgen, kunnen er nieuwe afgeleide velden worden toegevoegd of kunnen er velden verborgen gehouden worden. Tussen de logische laag en de interne laag is er juist één 'logische/interne' -mapping gedefinieerd. Deze mapping definieert de correspondentie tussen de logische en de interne representatie van de database. Opnieuw zijn verschillen mogelijk: extra interne velden, andere veldnamen, enzovoort (zie de andere voorbeelden). Voor de volledigheid vermelden we nog dat de meeste systemen het toelaten om views te definiëren op basis van andere views, in plaats van op basis van de logische beschrijving van de database. In voorkomend geval moeten er ook 'externe/externe' mappings worden aangemaakt.

Door het gebruik van een 'meerlagen' -architectuur, zoals we in de vorige paragraaf beschreven, wordt dataonafhankelijkheid verkregen. Dit betekent concreet dat, door een gepaste aanpassing van de mappings, de beschrijving van de data in een bepaalde laag wordt aangepast zonder dat dit een impact heeft op de andere lagen. Het grote voordeel hiervan is een gemakkelijker onderhoud van het databasesysteem. Er wordt een onderscheid gemaakt tussen fysieke en logische dataonafhankelijkheid. Fysieke dataonafhankelijkheid Fysieke dataonafhankelijkheid betekent dat de interne beschrijving van de database en de 'logische/interne' -mapping kunnen worden aangepast zonder dat dit een gevolg heeft op de logische

Hoofdstuk: /Databasesysteem

Dataonafhankelijkheid

2 1

DATABASE ontwerpen

1 januari 2011

beschrijving van de database (en dus ook op de views die eventueel zijn gedefinieerd in de externe laag). Aanpassing van de interne beschrijving kan nuttig zijn wanneer de fysieke opslag moet worden gereorganiseerd. Bijvoorbeeld, wanneer er ten gevolge van gewijzigde interactiepatronen van de gebruikers wordt gekozen voor een nieuwe, meer efficiënte primaire bestandsorganisatie. Of wanneer, om de prestatie te verbeteren, de secundaire bestandsorganisatie moet worden aangepast door de creatie van bijkomende indexen. Door de fysieke dataonafhankelijkheid worden de primaire en secundaire bestandsorganisatie afgeschermd van de logische database. Zo komen gebruikers en toepassingsprogramma's niet onnodig in contact met de fysieke opslagdetails en kan alle aandacht gaan naar de intuïtievere en dus beter handelbare, abstracte databasestructuur en operatoren. In principe komt alleen de DBA in contact met de fysieke opslagparameters. Dankzij de fysieke dataonafhankelijkheid zijn toepassingsprogramma's immuun voor aanpassingen aan de fysieke opslagstructuur en toegangsmechanismen. Dit biedt uiteraard enorme onderhoudsvoordelen voor de toepassingsprogramma's. Logische dataonafhankelijkheid Logische dataonafhankelijkheid betekent dat de logische beschrijving van de database en de 'externe/logische' -mappings kunnen worden aangepast zonder dat dit een impact heeft op de diverse views. Aanpassingen aan de logische databasebeschrijving kunnen nodig zijn wanneer een recordtype moet worden hernoemd, aangepast of verwijderd of wanneer een nieuw recordtype moet worden aangemaakt. Toepassingsprogramma's worden hierdoor ook immuun voor aanpassingen aan de logische beschrijving van de database, wat opnieuw een onderhoudsvoordeel met zich meebrengt. De logische databasebeschrijving kan (samen met de fysieke beschrijving) evolueren met de noden van de onderneming, zonder dat daarbij steeds de bestaande toepassingsprogramma' s moeten worden aangepast. De enige vereiste is het aanpassen van de 'externe/logische'-mappings.

Ter afsluiting van dit hoofdstuk staan we even stil bij de voor- en nadelen van het gebruik van databasesystemen. Wanneer je te maken krijgt met een vraag naar gecomputeriseerde gegevensopslag moet je deze voor- en nadelen grondig afwegen om tot de meest geschikte oplossing te komen. Het belangrijkste voordeel bij het kiezen voor het gebruik van een databasesysteem is dat de systeemontwikkelaar zich niet hoeft te bekommeren om de implementatie van de dbms - faciliteiten voor onder meer het delen van dezelfde data, beveiliging en integriteit. Alle aandacht kan hierdoor worden gevestigd op de feitelijke structuur en functionaliteit van de toepassingen. Hoe meer dbms faciliteiten vereist zijn bij het op te zetten systeem, hoe beter het is om te kiezen voor een databasesysteem. Het nadeel van een databasesysteem is dat sommige dbms-faciliteiten voor de toepassing overbodig kunnen zijn en zo een zekere overlast en prestatieverlies met zich mee kunnen brengen. Hierdoor kan het voordeliger zijn om te kiezen voor de implementatie van een eenvoudiger, weliswaar volledig op maat ontworpen, opslagsysteem. Dit kan onder meer het geval zijn wanneer:  het te ontwikkelen systeem eenvoudig is, weinig of geen dbms-faciliteiten vereist, volledig omschreven is en met de tijd bijna niet zal evolueren. Men hoeft zich dan niet te bekommeren over de installatie en configuratie van een complex dbms.  de eisen (snelheid, compactheid van code en compactheid van opgeslagen data) aan het systeem zo uitgebreid zijn, dat deze onmogelijk kunnen worden voldaan wanneer een volwaardig dbms wordt gebruikt. Dit kan onder meer het geval zijn als we strenge eisen

Hoofdstuk: /Databasesysteem

Wanneer gebruik je een databasesysteem?

2 2

DATABASE ontwerpen



1 januari 2011

stellen aan de uitvoeringstijd (toepassingen in productieomgevingen, telecommunicatie enzovoort) of als we strikte beperkingen opleggen aan de geheugencapaciteit (toepassingen op mobiele devices). men te kampen heeft met budgettaire beperkingen: commerciële databasesystemen zijn meestal niet goedkoop en vragen vaak extra investeringen in zowel hardware, software als training van de gebruikers. Zelfontwikkelde opslagsystemen vragen echter ook extra investering zodat in voorkomend geval een grondig kosten/baten analyse van beide scenario’s zeker aangewezen is. Een ‘open source’ dmbs kan een goed alternatief zijn. Let wel dit laaste brengt ook de nodige kosten mee. Bijvoorbeeld er moet intern kennis over het dmbs aanwezig zijn. Deze kennis zal betaald moeten worden.

Hoofdstuk: / Vragen deel 1a

Om deels tegemoet te kunnen komen aan voorgaande situaties bieden de meeste constructeurs van databasesystemen ook afgeslankte versies van hun systemen aan.

2 3

DATABASE ontwerpen

1 januari 2011

Vragen deel 1a

Hoofdstuk: / Vragen deel 1a

1. Wat is het verschil tussen data (gegevens) en informatie? Waarom is het nuttig om binnen de context van een database een onderscheid te maken tussen beiden? 2. Wat is een database, een databasesysteem en een databasemanagementsysteem? 3. Uit welke componenten bestaat een databasesysteem? 4. Schets de geheugen hiërarchie. Bespreek de rol van de verschillende geheugentypen die hierin voorkomen met betrekking tot databases. Waarom zijn databasebuffers belangrijk? 5. Wat zijn database records? Welke types kan men onderscheiden? Hoe kunnen deze worden beschreven? 6. Waartoe dienen datawarehousing en datamining? Waar staan deze ten opzichte van databases? 7. Wat zijn gebruikersprofielen? Bespreek de taken van een data-administrator en een database-administrator. 8. Bespreek databasedefinitie, databasemanipulatie en databasereconstructie. 9. Waarom is het noodzakelijk dat databases kunnen worden gedeeld? Wat zou er gebeuren als dat niet kan? 10. Waarom is het noodzakelijk dat databases worden beveiligd? 11. Waarom is er een verschil tussen de interne laag van de drielagen architectuur en de fysieke dataopslag? Wat is het nut van de interne laag? 12. Wat zijn indexen? Waartoe worden deze gebruikt in databases? 13. Wat is het nut van de logische laag en de externe laag van de drie lagen architectuur? 14. Waarop slaat dataonafhankelijkheid? Geef met een concreet voorbeeld aan wat het nut hiervan is. 15. Waarmee moet men rekening houden bij de keuze om een toepassing wel of geen databasesysteem te gebruiken? 16. Bedenk een realistische toepassing waarbij je zeker een zelfontwikkeld opslagsysteem zou kiezen.

2 4

DATABASE ontwerpen

1 januari 2011

Relationele database In een relationele database worden gegevens, voor de gebruikers, geordend in tabellen. Dit maakt het onder andere mogelijk om snel specifieke gegevens te selecteren. Hieronder staan de gegevens in een databasetabel.

Door in deze tabel naar een cursus_id te zoeken in de cursus_id-kolom kunnen snel de bijhorende titel en categorie opgevraagd worden. Dat gaat veel sneller dan door een tekstbestand heen wandelen totdat je de gewenste informatie gevonden hebt. Bij een relationele database kunnen gegevens uit specifieke rijen, kolommen en zelfs uit verschillende tabellen gecombineerd opgevraagd worden. Je kunt het cursus_id (de 'primaire sleutel') gebruiken om de cursus te koppelen aan gegevens in andere tabellen. Hierover later meer. Het concept van de relationele database waarin gegevens zijn geordend in tabellen is bedacht in de jaren 70 door meneer Ted Codd. De relationele database is een zeer krachtige methode voor het opslaan van gegevens gebleken. Tegenwoordig gebruiken heel veel programma's een relationele database om gegevens in op te slaan.

 

  

Oracle. Oracle wordt in de regel gebruikt voor grotere professionele applicaties Microsoft SQL server. Het professioneel RDBMS van Microsoft wordt gebruikt voor alle soorten applicaties, van studentenapplicaties tot professionele applicaties met veel gebruikers. MSSQL is alleen beschikbaar voor Windows. Mysql community en onder zowel hobbyisten en beginners als professionelen wereldwijd. Mysql is gratis (of beter gezegd 'vrij') verkrijgbaar. IBM doet ook een grote duit in het zakje met verschillende databasesystemen, waarvan DB2 de populairste is. Microsoft Access is een uitgekleed RDBMS. Het mist sommige functies van een professioneel RDBMS en is bedoeld voor kantoor- en thuisgebruik.

Een database-ontwerp maak je niet voor een bepaald RDBMS. Het ontwerp van de database is onafhankelijk van het databasesysteem, zolang je een relationeel databasesysteem (RDBMS) gebruikt. Je zou de database die we in deze cursus ontwerpen op elk van bovengenoemde database-

Hoofdstuk: Relationele database

Tegenwoordig zijn er tal van verschillende Relationele Database Management Systemen (RDBMS) in gebruik. Tot de toppers van de database-industrie behoren

2 5

DATABASE ontwerpen

1 januari 2011

Hoofdstuk: Relationele database

systemen kunnen maken. Je moet daarbij wel gebruik maken van de voor die database ontwikkelde instrumenten (SQL dialecten enzovoort)

2 6

DATABASE ontwerpen

1 januari 2011

Voordelen en mogelijkheden van relationele databases Naast het snel opzoeken van informatie heeft een relationele database nog een aantal voordelen.















Een relationele database kan gegevens in verschillende tabellen relateren door het gebruik van sleutels. Daardoor kunnen gerelateerde gegevens uit verschillende tabellen tegelijk opgevraagd worden. Efficiënte opslag van gegevens. Gegevens worden maar één keer en op één plek zijn opgeslagen. Dit zorgt ervoor dat een wijziging of verwijdering van informatie altijd maar op één plek hoeft te gebeuren. Met een relationele database kan je regels opstellen voor het soort gegevens dat in een veld opgeslagen kan worden. Zo kun je onder andere datumvelden, tekstvelden en numerieke velden aanmaken. Integriteit van gegevens. Door het formuleren van de juiste relaties van tabellen kun je de integriteit (correctheid, betrouwbaarheid) van de gegevens die zijn opgeslagen beter garanderen. De meeste relationele databasesystemen kennen een rechtenstructuur, waarmee aan verschillende gebruikers verschillende rechten toegekend kunnen worden. Zo kun je het recht hebben om gegevens uit de database te op te vragen, maar niet om nieuwe gegevens in te voeren. Relationele databases zijn geavanceerde programma's die geoptimaliseerd zijn voor bepaalde taken, zoals bijvoorbeeld het zoeken door numerieke velden, data sorteren, etc. Deze technologie maakt het mogelijk om zeer snel gegevens te zoeken en te presenteren. Voor het bevragen van een relationele database is een krachtige 'query taal' beschikbaar met de naam 'Structured Query Language' (SQL). Deze taal maakt het onder andere mogelijk om zeer geavanceerde gegevensselecties te maken uit de database. Het relationeel is een standaard, net als SQL, de taal om relationele database te bevragen. Standaardisering maakt het mogelijk om gegevens uit de ene database vaak relatief eenvoudig naar de andere database over te zetten.

Hoofdstuk: Relationele database



2 7

DATABASE ontwerpen

1 januari 2011

Klantcontactsysteem Zoals gezegd is de leidraad van deze cursus relationele database ontwerpen een 'klantcontactsysteem'. De eisen voor dit klanten systeem zijn: In dit klantcontactsysteem willen we contactmomenten opslaan. Een contactmoment ontstaat elke keer dat er tussen ons en een van onze klanten gecommuniceerd wordt via de telefoon, per e-mail of per post. Een programma dat gebruik maakt van deze database kan de database gebruiken om bijvoorbeeld een overzicht te genereren van de contactmomenten die er geweest zijn met een klant. Of om klantgegevens op te vragen van een specifieke klant. We willen voor het klantcontactsysteem een database ontwerpen waarin we klantgegevens en 'contactmomenten' op kunnen slaan. Klantgegevens zijn bijvoorbeeld naam, adres, telefoonnummer en het e-mailadres. Een contactmoment is een beschrijving van elke keer dat er gecommuniceerd wordt tussen het bedrijf en een klant. Door een goed database-ontwerp te maken kunnen we een programma dat gebruik gaat maken van de database ruime mogelijkheden geven voor bijvoorbeeld

 

Het opvragen van klantgegevens (met behulp van het SQL SELECT statement) Het opvragen van alle contactmomenten van een klant Het zoeken van contactmomenten op een bepaalde datum of een datuminterval. Het aanpassen, invoeren en verwijderen van klantgegevens (met behulp van de SQL UPDATE statements) Het aanpassen, invoeren en verwijderen van contactmomenten etc

Hoofdstuk: Relationele database

   

2 8

DATABASE ontwerpen

1 januari 2011

Tabellen en de primaire sleutel Een relationele database bestaat uit tabellen. In deze tabellen zijn gegevens van dezelfde soort in rijen of in vaktaal records opgeslagen. Je zag eerder al een voorbeeld van een tabel waarin wat informatie over cursussen is opgeslagen. Die tabel bestaat uit 6 records die elk informatie over één cursus bevatten. Alle cursussen zijn verschillend, maar van elke cursus is dezelfde informatie opgeslagen.

Voor het klantcontactsysteem dat we willen maken ligt het voor de hand dat we een klanttabel maken. Hieronder staat een voorbeeld van een tabel met klantinformatie.

Hoe kun je in deze tabel een klant selecteren? Je zou kunnen zoeken op de voornaam. Dat is geen goed idee, want naarmate het aantal klanten in de tabel groeit wordt de kans groter dat er nog een klant komt die Jan heet en dan kun je niet meer met zekerheid de juiste Jan selecteren. Je zou kunnen zoeken op een combinatie, bijvoorbeeld de voornaam en achternaam. Maar hoe lang zou het duren voordat zich een tweede Jan Jansen aanmeldt als klant? Om klanten uniek te kunnen identificeren moet elke klant gekoppeld zijn aan een uniek stukje informatie, bijvoorbeeld een klantnummer. Deze unieke informatie wordt de primaire sleutel (primary key) genoemd. Een van de belangrijkste regels van het relationeel model is dat alle rijen in een tabel uniek te identificeren zijn door middel van de primaire sleutel. Het cursus_id uit het eerste tabelvoorbeeld op deze pagina is zo'n primaire sleutel. Er zijn tal van voorbeelden te bedenken van nummers en codes uit het dagelijks leven die waarschijnlijk kunnen dienen als primaire sleutel in een database. Een bestelnummer Een rekeningnummer Een sofinummer Een factuurnummer Een klantnummer Een productnummer

Wat hebben al deze codes gemeen?  

Ze zijn allemaal uniek. Jouw rekeningnummer bestaat altijd maar één keer, net als een factuurnummer, je sofinummer, etc. Ze werken allemaal als toegangsweg naar meer informatie. Aan een factuurnummer is een datum, een bedrag, etc gekoppeld. Aan een productnummer kan een productbeschrijving, een plaatje, etc gekoppeld zijn.

De primaire sleutel wordt dus gebruikt om rijen in tabellen uniek te identificeren en om gegevens in verschillende tabellen aan elkaar te koppelen. Primaire sleutels zijn zoals gezegd altijd uniek en

Hoofdstuk: Relationele database

     

2 9

DATABASE ontwerpen

1 januari 2011

mogen daarom maar 1 keer voorkomen in de kolom. Het is in onderstaande tabel dus niet mogelijk om een nieuwe rij met klantnummer 1 toe te voegen. Geef je aan dat het klantnummer de primaire sleutel is, dan zal het databasesysteem er (voor zover ik weet altijd) automatisch voor zorgen dat klantnummers in die kolom uniek zijn.

Hoofdstuk: Relationele database

Je kunt aan deze afbeelding overigens niet zien dat het klantnummer de primaire sleutel is. In de ontwerpweergave van de tabel is dat wel te zien.

3 0

DATABASE ontwerpen

1 januari 2011

Gegevens koppelen In de klanttabel slaan we klantgegevens op, maar we moeten voor het klantcontactsysteem ook contactmomenten opslaan. Een contactmoment ontstaat wanneer een klant belt, e-mailt of een brief schrijft. Je zou misschien bedenken om de datum en een beschrijving van het contactmoment in de klanttabel op te slaan bij de juiste klant. Dat kan echter niet. Het klantnummer moet uniek zijn in de klantnummerkolom. Er mag dus altijd maar 1 rij per klant zijn in de klanttabel. Je zou nog alle contactmomenten in 1 cel kunnen opslaan op dezelfde rij als de klantgegevens (als een soort flat file in 1 cel), maar dat is absoluut niet wenselijk, omdat het dan heel moeilijk wordt om door de contactmomenten te zoeken. (Nb. later in deze cursus databases worden de regels voor goed database-ontwerp op een rij gezet). Conclusie: we moeten een nieuwe tabel maken voor contactmomenten. Eerst bepalen we wat we allemaal van een contactmoment willen opslaan.      

contactmoment_id (primaire sleutel) klantnummer (zodat het contactmoment aan één klant gekoppeld is) Datum en tijd Een beschrijving van het contactmoment (Inhoud telefoongesprek, inhoud e-mail, inhoud brief, etc) Nam het bedrijf contact op met de klant of andersom?: inkomend_uitgaand Communicatietype (telefoon, e-mail, fax): com_type

Op elke rij van de contactmomententabel staat informatie over een uniek contactmoment. Elk contactmoment is uniek identificeerbaar door de primaire sleutel, het contactmoment_id (eerste kolom). Bovendien is elk contactmoment gekoppeld aan een klant uit de klanttabel door middel van het klantnummer in de tweede kolom. Met behulp van het klantnummer kunnen bij elke klant in de klanttabel de contactmomenten opgezocht worden en andersom bij elk contactmoment de betreffende klant uit de klanttabel. Er is hier sprake van een '1 op veel' relatie tussen de klant en zijn of haar contactmomenten. Nu je een voorbeeld gezien hebt van een database is het tijd om in te gaan op de relaties die kunnen bestaan tussen tabellen. Het voorbeeld van de klanttabel en de contactmomententabel is een 'één

Hoofdstuk: Relationele database

Hieronder staat een conceptversie van de contactmomententabel. Later in deze cursus database ontwerpen bekijken we onderstaande tabel nog eens kritisch aan de hand van de ontwerpregels voor relationele databases.

3 1

DATABASE ontwerpen

1 januari 2011

op veel relatie'. Voor elke klant (1) kunnen meerdere (veel) contactmomenten opgeslagen zijn. Bij het ontwerpen van relationele databases onderscheiden we 3 soorten relaties tussen tabellen.   

De één op veel relatie De veel op veel relatie De één op één relatie

De een op veel relatie

Bij de één op veel relatie is 1 ding gekoppeld aan 0, 1 of meer andere dingen. Je zag al een voorbeeld met klanten en contactmomenten. 1 klant kan 0, 1 of meerdere contactmomenten hebben en een contactmoment is altijd gekoppeld aan 1 klant. Hetzelfde geldt voor de relatie tussen moeders en hun kinderen. Elk kind heeft maar 1 moeder, maar een moeder kan geen, één of meerdere kinderen hebben. De één op veel relatie modelleer je in een relationele database als twee tabellen. Elke rij in tabel A correspondeert met 0, 1, of meerdere rijen uit tabel B. (zie afbeelding). De klanttabel speelt de rol van tabel A en de contactmomententabel die van tabel B. Elke rij in tabel A (ofwel, elke klant) is gekoppeld aan 0,1 of meerdere rijen (ofwel, contactmomenten) in tabel B. Het klantnummer is de primaire sleutel in tabel A, het klantnummer in tabel B wordt de vreemde sleutel genoemd. Een vreemde sleutel is een veld in een tabel dat verwijst naar de primaire sleutel van een andere tabel. Het klantnummer in onze database is de primaire sleutel in de klanttabel en in andere tabellen is het klantnummer een vreemde sleutel.

Klantnummer is de primaire sleutel (ps) in de klanttabel

Hoofdstuk: Relationele database

tabel klant

3 2

DATABASE ontwerpen

1 januari 2011

tabel contactmoment

Klantnummer is de vreemde sleutel (vs) in de contactmomententabel. Hij verwijst naar het klantnummer in de klanttabel. Het contactmoment_id is de primaire sleutel (ps) in de contactmomententabel. Het kan van belang zijn of de een op veel relatie “0 of meer” is of dat hij “1 of meer is”. Hier moet in het database ontwerp mee rekening gehouden worden.

De veel op veel relatie

Tabel A en tabel B zijn beide tabellen met een primaire sleutel. Tabel A_B verbindt records uit tabel A en B en heet een 'koppeltabel'. Een koppeltabel bestaat uit maar twee kolommen die allebei een vreemde sleutel bevatten. Een “vreemde sleutel” is een waarde in een tabel (die geen sleutel in deze tabel is) die in een andere tabel wel sleutel is. De vreemde sleutel in de linkerkolom verwijst naar de primaire sleutel uit tabel A en die in de rechterkolom naar de primaire sleutel uit tabel B. De primaire sleutel in koppeltabel A_B is samengesteld uit de twee vreemde sleutels. Je ziet in het voorbeeld dan ook dat de combinatie van de twee velden uniek moet zijn in koppeltabel A_B. In het klantcontactsysteem komt (vooralsnog)

Hoofdstuk: Relationele database

De veel op veel relatie is een relatie waarbij meerdere rijen uit tabel A gekoppeld kunnen zijn aan meerdere rijen uit tabel B. Een voorbeeld is een school, waarbij docenten les geven aan studenten. Elke docent geeft les aan 0, 1 of meer studenten. Andersom kan elke student les krijgen van 0, 1 of meerdere docenten. Een ander voorbeeld is de relatie tussen biermerken en leveranciers. Elke leverancier levert meerdere biermerken, maar elk biermerk kan ook door meerdere leveranciers geleverd worden. De veel op veel relatie modelleer je met drie tabellen. Twee brontabellen en één koppeltabel.

3 3

DATABASE ontwerpen

1 januari 2011

geen veel op veel relatie voor, daarom staat hieronder een voorbeeld met bieren en hun leveranciers.

Bovenstaande tabellen koppelen bieren en leveranciers in een veel op veel relatie. Eén bier kan door 0, 1 of meerdere leveranciers geleverd worden. Gentse Tripel (157) wordt bijvoorbeeld geleverd door Horeca Import NL (157, AC001), Jansen Horeca (157, AB899) en Petersen Drankenhandel (157, AC009). Andersom levert Petersen Drankenhandel 3 bieren uit de bierentabel, te weten Gentse Tripel (157, AC009), Uilenspiegel (158, AC009) en Jupiler (163, AC009).

Een ander goed gebruik is om in databasemodellen relaties te benoemen. Je zou bij de hierboven afgebeelde relatie in een model de opmerkingen "levert" kunnen zetten. Elke rij in de tabel zegt namelijk "bier X wordt geleverd door leverancier Y", of "Leverancier Y levert bier X". Het benoemen van relaties maakt een databasemodel leesbaarder. Deze benoeming komt niet terug in de eigenlijke database of in de software die gebruik maakt van de database.

De een op een relatie De één op één relatie tussen tabellen komt niet heel vaak voor. Bij de één op één relatie heeft elke rij in tabel A 0 of 1 corresponderende rij in tabel B. Deze relatie wordt weleens gebruikt om de tekortkomingen van een databasesysteem te omzeilen door bijvoorbeeld een tabel op te delen om prestatiewinst te behalen.

Hoofdstuk: Relationele database

Merk op dat in bovenstaande tabellen de primaire sleutelvelden blauw en onderstreept zijn weergegeven. In modellen van relationele databases worden primaire sleutels vaak onderstreept. Zoals je ziet is de primaire sleutel van de koppeltabel 'bier_leverancier' samengesteld uit twee velden. Elke rij in de koppeltabel bestaat uit de combinatie van een bier_id met een leverancier_id. Deze tabel bestaat zogezegd uit een samengestelde primaire sleutel. Primaire sleutels moeten uniek zijn, dus de combinaties moeten uniek zijn in de tabel.

3 4

DATABASE ontwerpen

1 januari 2011

Merk op dat elke individuele tabel vol zit met 1 op 1 relaties: de relatie tussen de primaire sleutel en de rest van de gegevens op die rij is een 1 op 1 relatie, of laat ik zeggen zou dat moeten zijn als je het relationeel model helemaal volgt. Een voorbeeldje: in de biertabel op de vorige pagina is Gentse Tripel in een 1 op 1 relatie gekoppeld aan de primaire sleutel 157. Dit is precies de reden dat 1 op 1 relaties tussen tabellen niet vaak voorkomen.

Hoofdstuk: Relationele database

Een relationeel database-ontwerp is zoals je hebt gelezen een verzameling van tabeldefinities, waarin gegevens in verschillende tabellen gekoppeld zijn in relaties. Voor het kiezen van de juiste relaties tussen gegevens formuleert het relationeel model een aantal 'normaalvormen'. In het volgende hoofdstuk ga ik in op het doel van normaliseren en de taken die hierbij horen.

3 5

DATABASE ontwerpen

1 januari 2011

Database normaliseren De regels voor goed relationeel database-ontwerp zijn samengevat in 5 'normaalvormen', waarbij de eerste normaalvorm de laagste en de vijfde de hoogste (meest genormaliseerd) is. Deze normaalvormen zijn richtlijnen voor het juist ontwerpen van een relationele database. Normaliseren heeft een aantal doelen.   

Flexibiliteit. De genormaliseerde structuur van de database zorgt ervoor dat gegevens op veel verschillende manieren opgevraagd en bijgewerkt kunnen worden. Integriteit. In een genormaliseerde database ben je gegevens zeer betrouwbaar opslaan. In een genormaliseerde database worden gegevens maar op 1 plek opgeslagen. Als je data wil invoeren, aanpassen of verwijderen hoef je dat dus maar op 1 plek te doen.

Het normaliseren van een database schijnt voor veel mensen taaie materie te zijn. Ik heb zelf ook die ervaring gehad, hoewel dat eigenlijk onterecht is. Het normaliseren van een database komt eigenlijk neer op het nastreven van de volgende zaken en die zijn met een beetje oefening en puzzelen vaak redelijk gemakkelijk te realiseren.    

Het verdelen van gegevens in logische samenhangende groepen. Het minimaliseren van de hoeveelheid data die dubbel opgeslagen is, ofwel het voorkomen van 'redundancy'. De gegevens zo organiseren dat het aanpassen of verwijderen van een gegeven altijd maar op één pek hoeft te gebeuren. Gegevens zo organiseren dat ze snel en efficiënt op te vragen zijn.

Hoofdstuk: Relationele database

De meeste applicaties gebruiken databases die zijn genormaliseerd tot de 1ste, de 2de of de 3de normaalvorm. De 4de en 5de normaalvorm zie je zelden. In deze cursus databases bespreek ik daarom alleen de eerste, tweede en derde normaalvorm.

3 6

DATABASE ontwerpen

1 januari 2011

De eerste normaalvorm (1NF) De eerste normaalvorm is een set basale ontwerpregels die op elke database van toepassing moeten zijn. Een tabel is de representatie van een 'ding' uit het systeem dat je maakt. Bijvoorbeeld bestellingen, een klanten, contactmomenten, producten, etc. Elke rij in de tabel is een uniek exemplaar van dat 'ding'. We noemen zo’n rij ook wel een “tupel”. Een rij vertegenwoordigt bijvoorbeeld 1 bestelling, 1 klant, 1 contactmoment, 1 bestelling, etc.

 

Atomiciteit: elk veld bevat maar één waarde. Een adres bijvoorbeeld hoor je op te slaan in aparte velden voor de straatnaam, het huisnummer en de huisnummerextensie. Elke tabel heeft een primaire sleutel: een zo klein mogelijk aantal velden dat een rij (record) uniek identificeert. De volgorde van de rijen in de tabel is onbelangrijk (als je wil weten in welke volgorde bestellingen zijn binnengekomen moet je hiervoor een datum en tijd veld maken. Hiervoor de rijvolgorde, in de database, gebruiken is niet de bedoeling)

Hoofdstuk: Relationele database



3 7

DATABASE ontwerpen

1 januari 2011

De tweede normaalvorm (2NF) De tweede normaalvorm: het verwijderen van redundante gegevens   

De database voldoet aan alle regels van de eerste normaalvorm. Zo min mogelijk gegevens worden dubbel opgeslagen in de database. De velden die geen primaire sleutel zijn, zijn afhankelijk van de primaire sleutel.

Je moet bij de tweede normaalvorm je tabellen goed inspecteren op gegevens die dubbel opgeslagen worden in een kolom. Gegevens die dubbel opgeslagen worden komen in aanmerking voor afsplitsing in een aparte tabel. Een mooi voorbeeld hiervan is het com_type veld in de contactmomententabel. In die kolom worden de waardes 'telefoon', 'email' en 'brief' telkens herhaald. Daar worden gegevens dus dubbel opgeslagen (redundancy) Afsplitsen is het devies. We maken een nieuwe tabel voor 'communicatietypes'.

Op deze manier hoeft een wijziging van de waarde 'telefoon' naar 'telefonisch' maar op 1 plek te gebeuren. Voor de inkomend_uitgaand kolom geldt ook dat gegevens dubbel opgeslagen worden. Hierin worden 'inkomend' en 'uitgaand' steeds herhaald. Wat is echter de kans dat er ooit een waarde bijkomt? 0. Want met 'inkomend' en 'uitgaand' zijn alle mogelijkheden gedekt. Wat is de kans dat je de waardes 'inkomend' en 'uitgaand' in de toekomst wil wijzigen? Klein. Vandaar dat we de herhaling van waardes in de inkomend_uitgaand kolom toestaan. Merk overigens op dat er altijd wel een beetje herhaling van gegevens is. Het klantnummer (vreemde sleutel) in de contactmomententabel wordt steeds dubbel opgeslagen, maar dat is herhaling die niet te voorkomen is. We willen immers wel dat elk contactmoment aan een klant gekoppeld is.

Hoofdstuk: Relationele database

Aan elk contactmoment is een communicatietype gekoppeld door het com_type veld. Dit veld is een vreemde sleutel. Het verwijst naar de primaire sleutel (com_type_id) van de communicatietypen tabel.

3 8

DATABASE ontwerpen

1 januari 2011

De derde normaalvorm (3NF) De derde normaalvorm: transitieve afhankelijkheden, een moeilijke term voor iets vrij eenvoudigs.  

De database voldoet aan alle regels van de tweede normaalvorm. Van een tabel die voldoet aan de derde normaalvorm zijn alle velden die geen primaire sleutel zijn uitsluitend afhankelijk van de primaire sleutel.

Zie de volgende klantentabel van een voetbalschoenenhandel.

In bovenstaande tabel zijn niet alle gegevens uitsluitend afhankelijk van de primaire sleutel. Er zijn nog andere relaties tussen de gegevens in een record: uit de postcode is de plaatsnaam en de provincie af te leiden. Er bestaat een 'transitieve relatie' tussen deze gegevens. Welnu, afsplitsen is zeker mogelijk en wordt in de praktijk ook weleens gedaan. Je kunt in de klantentabel voor elke klant alleen de (eerste 4 cijfers van de) postcode opslaan en de bijhorende gegevens (provincie, plaatsnaam) in een aparte tabel stoppen. Deze aparte postcode-plaatsnaamprovincie tabel kun je kopen.

De relatie tussen het totaalbedrag exclusief BTW en het totaalbedrag inclusief BTW is duidelijk. Het bedrag inclusief BTW is altijd 19% hoger dan het bedrag exclusief BTW. De waardes van de twee prijskolommen zijn uit elkaar af te leiden. In dit geval zou je én van de twee kolommen moeten verwijderen. Het omrekenen tussen exclusief en inclusief BTW laat je over aan het programma dat van de database gebruik maakt. Kort gezegd: je hoort nooit gegevens op te slaan die af te leiden zijn

Hoofdstuk: Relationele database

Een ander voorbeeld van de toepassing van de derde normaal is deze (veel te) eenvoudige bestellingentabel van een Nederlandse webwinkel.

3 9

DATABASE ontwerpen

1 januari 2011

Hoofdstuk: Relationele database

uit andere gegevens in de tabel. In de praktijk wordt zeker bij kleinere applicaties de derde normaalvorm niet altijd toegepast.

4 0

DATABASE ontwerpen

1 januari 2011

Het definitieve database-ontwerp We passen voor onze database de normalisatieregels losjes toe. Hieronder staan de tabellen en hun onderlinge relaties afgebeeld. (Afbeelding komt uit MS Access 2003).

In de database zijn klanten gekoppeld aan contactmomenten door middel van het klantnummer. Je ziet daarom in de afbeelding een verbindingslijn tussen het klantnummer in de klanttabel en het klantnummer in de contactmomententabel. In de klantabel is klantnummer de primaire sleutel (dik gedrukt) en in de contactmomententabel is het de vreemde sleutel. Dezelfde koppeling bestaat tussen de contactmomententabel en de communicatietypentabel.

Een tweede aanmerking is dat de postcode in één veld opgeslagen is, terwijl je de cijfers nog van de letters zou moeten scheiden als je streng bent (atomiciteit). Aan de hand van de eerste 4 cijfers kun je de plaatsnaam, de gemeente en de provincie afleiden. Wil je een postcodetabel aan de database hangen, dan is het dus handig om de postcode te verdelen over twee velden. Voor onze doeleinden is dit echter niet nodig.

Hoofdstuk: Relationele database

Het soort relatie staat ook in de afbeelding. 1 klant correspondeert met 0 tot oneindig veel contactmomenten. Oneindig wordt weergegeven als een ∞ teken. Hetzelfde geldt voor de relatie tussen contactmomenten en communicatietypes. 1 communicatietype is gekoppeld aan 0 tot oneindig veel contactmomenten. Er is nog wel iets aan te merken op deze database. Hij voldoet niet aan de derde normaalvorm. Er bestaat er een transitieve relatie tussen de postcode en de woonplaats. In Nederland zijn deze uit elkaar af te leiden. Om deze cursus niet nog langer te maken heb ik besloten hiermee vrede te nemen. Ook is het voor een eenvoudige applicatie niet nodig om het af te splitsen. Je zou dan ook een volledige postcodetabel nodig hebben. Deze is tegen kosten te verkrijgen (en die kosten zijn niet mals, zeker als je de tabel up to date wilt houden: abonnementskosten)

4 1

DATABASE ontwerpen

1 januari 2011

Nog een voorbeeld: webwinkel Om deze cursus database ontwerpen af te sluiten ontwerp ik hier een iets complexere database voor een webwinkel. Webwinkels zijn systemen die over het algemeen producten kunnen tonen, bestellingen kunnen opnemen en klantgegevens kunnen bijhouden. Dat is natuurlijk nog geen complete webwinkel, maar de essentie van het systeem is daarmee wel beschreven. Enkele belangrijke spelers in dit systeem zijn dus de klant, het product en de bestelling. Welke relaties bestaan er tussen deze zaken? Tussen klant en bestelling bestaat een één op veel relatie. Een klant kan 0, 1 of meerdere bestellingen plaatsen en andersom is een bestelling altijd gekoppeld aan precies 1 klant. Tussen bestelling en product bestaat een veel op veel relatie. Elke bestelling bestaat uit 1 of meer producten en elk product kan onderdeel zijn van 0,1 of meer bestellingen.

Microsoft Access 2003 geeft de relaties weer zoals hierboven is afgebeeld. De relatie tussen klant en bestelling is een een op veel relatie. Tussen bestelling en product bestaat een veel op veel relatie. De tabel bestelling_product is een koppeltabel die ervoor zorgt dat elk product aan meerdere bestellingen gekoppeld kan zijn en andersom dat elke bestelling uit meerdere producten kan bestaan. Merk op dat bestelling en product beide een één op veel relatie met de koppeltabel hebben.

In de producttabel is alleen de prijs exclusief BTW opgenomen. Als je de prijs exclusief BTW kan opvragen kun je daar in een programma makkelijk de prijs inclusief BTW uit berekenen. In de producttabel is uitsluitend informatie opgeslagen die uniek bij een product (product_id) hoort. Merk ook op dat nergens een totaalbedrag voor de bestelling is opgenomen. Deze informatie is berekenbaar (af te leiden) en hoeft dus niet apart opgeslagen te worden. Het totaalbedrag vind je natuurlijk eenvoudig door de prijzen van alle producten die deel uitmaken van de bestelling op te tellen.

Conclusie Een relationele database in mooi gereedschap voor het efficiënt opslaan en snel opvragen en bewerken van gegevens. Het is niet voor niets dat de relationele database zo immens populair is.

Hoofdstuk: Relationele database

Let ook op de gegevens die zijn opgeslagen. In de klanttabel zijn (enkele) klantgegevens opgeslagen. "Da's logisch", zou Johan Cruijff zeggen en dat is het ook. In de bestellingentabel is het klantnummer opgenomen, zodat elke bestelling aan 1 klant gekoppeld is. Bovendien zijn de datum en tijd opgenomen. Dat zijn eigenschappen die bij de gehele bestelling horen.

4 2

DATABASE ontwerpen

1 januari 2011

In deze cursus database ontwerpen heb ik me puur gericht op het ontwerp van de database. Wil je echt met een database aan de slag dan zijn de volgende websites en artikelen zeker de moeite waard.





In de categorie database van Leren.nl vind je een groot aantal database-gerelateerde artikelen en cursussen In de categorie SQL van Leren.nl kun je veel lezen over SQL, de 'Structured Query Language'. SQL is de taal die gebruikt wordt om databases te bevragen en bewerken. Een onmisbare taal als je in de praktijk met databases aan de slag wil! Bij Wikipedia vind je uitgebreide informatie over de geschiedenis van de database, verschillende database-modellen, de interne werking van databases en de technische mogelijkheden van databases.

Hoofdstuk: Relationele database



4 3

DATABASE ontwerpen

1 januari 2011

Het ontwerp proces van een database. Problemen, een slechte Database Hier onder ziet u een database met drie tabellen. Deze database is slecht ontworpen. Elke tabel heeft een aantal problemen. Tabel Abonnement Plaats

Naam

Krant

Gouda

HJ Nootenboom Automatisering Gids Stam

Gouda

H.J Nootenboom Computable

Zevenhoven S Peuskens

Uitgever

VNU

Computable

Stam

Problemen van de tabel Abonnement De uitgever van het blad Computable is ambigu (voor meerdere uitleg vatbaar). Voor de ene lezer is het Stam, voor andere de VNU. Één en dezelfde lezer staat met twee verschillende spellingswijzen in de administratie. Bezorg Adres Naam

Postcode Krant

HJ Nootenboom 2803 DA

Adres

Autom. Gids Groenhovenweg 43

Problemen van de tabel Bezorg Adres De postcode 12345ABC is fout, maar er is geen mogelijkheid om de juistheid van een postcode te controleren. Het adres van één lezer staat 2x vermeld. Dat is niet alleen lastig bij verhuizen (dubbel bijwerken), maar het is ook onbekend wat nu de juiste versie is. Voor het abonnement van S Peuskens op Computable ontbreken de adres gegevens. Fakturering gegevens Prijs Geboortedatum Naam 75,00 30 aug 1964

Postcode

Plaats

Henk Jan Nootenboom 00000 HHH Juinen

Hoofdstuk: Het ontwerp proces van een database.

H.J. Nootenboom 12345ABC Computable Groenh. weg 43-II

4 4

DATABASE ontwerpen 0,00 01-04-1572

Stefan Peuskens

1 januari 2011 2435xh

Z. Hoven

Problemen van de tabel Fakturering gegevens Er is géén relatie te leggen tussen facturering gegevens en abonnement. Het is onduidelijk welke prijs voor welke krant geldt. Iemand kan een factuur krijgen, zonder abonnement, of andersom. Er is geen goede verwijzing foreign key. De postcode en plaatsnaam van Henk Jan Nootenboom zijn onzin. De factuur zal nooit aankomen. Het is voor de computer onmogelijk om de adresgegevens uit een andere tabel te halen, door de net weer andere schrijfwijze van de naam. Stefan Peuskens krijgt wél een factuur van 0 gulden, maar zal geen krant ontvangen. Het bezorgadres is immers niet bekend. Of heeft die € 0,- een speciale betekenis en wordt er daarom géén factuur verstuurd en ook geen krant bezorgd? De kolom geboortedatum is niet relevant, valt buiten de scope. De datum 01-04-1572 is niet betrouwbaar, vast expres onjuist opgegeven. Een slecht ontworpen database kent de volgende problemen: Bij elkaar horende data staat verspreid over diverse tabellen. Een wijziging moet op vele plaatsen doorgevoerd worden. Het is mogelijk dat informatie maar half aanwezig is, in de ene tabel wel en in de andere tabel niet. Data is inconsistent of ambigu (voor meerdere uitleg vatbaar). De database is nodeloos ingewikkeld, met veel Kunst & Vliegwerk in elkaar geprutst. De database-ontwerper heeft moeilijk gedaan, terwijl het makkelijk kan.

De database is traag, inflexibel, lastig uit te breiden en kan niet alle praktijksituaties aan. Het ontwikkel proces voor een “goede” database bestaat uit een viertal stappen die doorlopen moeten worden:    

Scope Logisch Ontwerp Technische Optimalisatie Implementatie

We zullen nu per onderdeel bespreken wat er bij komt kijken.

Hoofdstuk: Het ontwerp proces van een database.

De database heeft 'verborgen' informatie, bijvoorbeeld door de volgorde van rijen in een tabel.

4 5

DATABASE ontwerpen

1 januari 2011

Scope Eerst bepalen we de scope  



Wat komt wel en wat komt niet in de database? Alléén wat bekend en goed te beheren is. Vermijd foute en misschien verouderde info. Maak bijvoorbeeld een interface met een ander systeem, waar die gegevens wel binnen de scope vallen. Hou scope zo klein mogelijk. Dat is lastig, want gebruikers nemen liever het zekere voor het onzekere. Ze willen alles, om maar niks te missen. Scopeloze projecten hebben een grote kans op mislukken, sterven in schoonheid.

Logisch Ontwerp Het doel van een logisch database-ontwerp is een ontwerp waarbij voor elke tabel de wet van Codd geldt: De waardes in een row zijn afhankelijk van de sleutel, de hele sleutel en niets dan de sleutel. Zo waarlijk helpe mij Codd. Door het toepassen van de normalisatie stappen: het Normaliseren komen we altijd tot een logisch ontwerp. De normalisatiestappen bestaan uit de volgende stappen:

o

een papieren administratie,

o

bestaande schermen,

o

of een gewenst schermontwerp.

2. Zet elk data-element op een geel plakbriefje. Plak ze allemaal naast elkaar. 3. Hou parameters (vluchtige informatie) en afgeleide informatie apart. Laat ze voorlopig buiten beschouwing. Later, tijdens de technische optimalisatie, heeft u ze weer nodig.

Hoofdstuk: Het ontwerp proces van een database.

0-de normaal vorm : Onderken alle data-elementen (scope) 1. Inventariseer data-elementen, bijvoorbeeld door waardes te highlighten op bronnen zoals:

4 6

DATABASE ontwerpen

1 januari 2011

Pas op Vermijd dubbele gegevens. Hou de definitie van elk data-element éénduidig. Vermijd ingewikkelde constructies zoals: Indien in element abc de code 123 staat, dan heeft data-element def een ghi en anders een jkl of is leeg, hetgeen betekent... 1-de normaal vorm: Zoek de sleutel waarmee alle data op te zoeken is

Zoek een overkoepelende sleutel (key).

  

Wat is die sleutel? Welke data-elementen maken elke rij (row) uniek? Welke waarden moet je weten om voor alle andere data-elementen één en slechts één waarde te kunnen vinden? Kies een key waarvan de waarde nooit verandert. (Of maak een key zelf) Hou de key zo klein mogelijk. Maak de key niet groter dan nodig. Hang de key data-elementen voorop. Zet een streep onder die plakbriefjes.

Wie het sofi# en de krantnaam weet kan de rest opzoeken. Je hebt nu een heel pril database-ontwerp, met één tabel, waarbij elke waarde afhankelijk is van de sleutel.

2-de normaal vorm: Maak alle data afhankelijk van de gehele sleutel. Verwijder part key dependencies Zijn er data-elementen die afhankelijk zijn van een deel van de eerste normaalvorm sleutel? 1. Maak hiervoor een nieuwe tabel, boven de oude. Teken een verbindingslijn tussen de oude en nieuwe tabel, een many to one relatie. 2. Merk de relatie aan de many kant met een rode hoofdletter I van Identificerend. 3. De nieuwe tabel krijgt een kleinere sleutel. Maak nieuwe plakbriefjes voor de nieuwe key. Laat de key in de oude tabel hangen. 4. Verhuis de afhankelijke data naar de nieuwe tabel. 5. Herhaal deze stappen voor overige part-key dependencies.

Hoofdstuk: Het ontwerp proces van een database.



4 7

DATABASE ontwerpen

1 januari 2011

Koppeltabel Noot: In de oude tabel blijft géén data over. Toch heeft de combinatie van keys betekenis. Zo'n koppeltabel maakt een many to many relatie mogelijk: Één lezer kan meerdere abonnementen hebben op diverse kranten. Op één krant zijn meerdere lezers geabonneerd. Dit is nu al een redelijk database ontwerp, met drie tabellen en twee one-to-many relaties. Met behulp van de Keys kun je gerelateerde informatie in andere tabellen opzoeken. 1. In abonnement staat het sofi#. 2. Met het sofi# kun je in "Lezer" de juiste rij vinden. 3. In de rij van Lezer staat o.a. het huisadres en huisnummer.

3-de normaal vorm:

Maak alle data afhankelijk van niets dan de sleutel.

Verwijder non-key dependencies

In het voorbeeld zijn Straat en Plaats transitief afhankelijk van Postcode. Bij een gegeven postcode hoort maar één straat en plaats. Maak een nieuwe tabel voor deze afhankelijkheid. 1. Kopieer de nieuwe Key naar de nieuwe tabel.

Hoofdstuk: Het ontwerp proces van een database.

Zijn er transitieve afhankelijkheden in de data te vinden, los van de key? Is het ene data-element op te zoeken als je een ander weet? Dit is vaak de lastigste stap van normaliseren.

4 8

DATABASE ontwerpen

1 januari 2011

2. Markeer de key in de oude tabel met een *, als foreign key. (Dit is handig voor technische optimalisatie, als u indexen gaat ontwerpen.) 3. Teken een one-to-many relatie tussen de nieuwe en oude tabel. 4. Verhuis de afhankelijke data-elementen naar de nieuwe tabel.

Maak een ERD

Hoe maak je het ERD. 1. Elke rij met plakbriefjes wordt een tabel. 2. Gebruik één vel papier per tabel. 3. Voeg de kleine plakbriefjes met data-elementen toe. 4. Teken one-to-many relaties altijd van boven naar beneden. De tabellen onderin hebben meestal data over de core business, in dit geval abonnement. Dit zijn meestal ook de grootste tabellen, met de meeste rijen.

Hoofdstuk: Het ontwerp proces van een database.

Na normaliseren tot de derde normaal vorm is het nu heel eenvoudig om een ERD te maken (Entity Relationship Diagram, entiteiten model, een logisch database-ontwerp, gegevensmodel, UML conceptueel model of een Bachman diagram) met 4 tabellen:

4 9

DATABASE ontwerpen

1 januari 2011

Test het logisch model Zijn alle functies die het systeem nodig heeft mogelijk met dit logisch model? Doorloop alle functies, aan de hand van de bronnen voor de nulde normaalvorm, die het systeem aan moet kunnen. Heeft de database alle benodigde data (nulde normaalvorm check)? Is het mogelijk om alle vereiste waarden op te zoeken (eerste normaalvorm check)? Zijn de functies compleet? Check dit met een CRUD matrix. Is er voor elke tabel ten minste één functie die o een row aanmaakt (Create), o leest (Read) o en verwijdert (Delete)? Een CRUD matrix Een CRUD matrix (Create, Read, Update, Delete) is een handig hulpmiddel voor de compleetheidscontrole. Zet Tabellen op de ene as en functies op de andere.

Oprichting

C

Prijsverhoging

U

Opheffing

D

Gebied

Abonnement

D

Nieuwbouw

C

Straatnaam wijziging

U

Sloop

D

Aanmelden

Lezer

R

C

C

Verhuizing

U

Opzeggen

D

D

R

R

Print route voor bezorger

R

R

Tip: Begin de matrix in te vullen met de C's, van create. U heeft dan al snel een goede compleetheidscontrole. De CRUD matrix dwingt u tot nadenken. Het is een goede voorbereiding voor de volgende stap: De technische optimalisatie.

Hoofdstuk: Het ontwerp proces van een database.

Krant

5 0

DATABASE ontwerpen

1 januari 2011

De Technische Optimalisatie: Het doel Het logisch ontwerp is een werkend model. Je zou het ontwerp kunnen implementeren en het werkt, zij het mogelijk wat traag. Technische optimalisatie (physiek database design) concentreert zich op Efficiency Technische optimalisatie zorgt ervoor dat de belangrijkste functies een goede performance hebben. Eenvoud Een volledig logisch model kan lastig zijn om mee te werken. SQL queries kunnen bijvoorbeeld ingewikkeld worden op een volledig logische database. Het logisch ontwerp was vooral geleid door regels. Bij technische optimalisatie komt het meer aan op creativiteit, inventiviteit en technisch vernuft

Optimalisatiestappen De stappen: i. ii. iii. iv. v. vi.

Werk met ranges Denormaliseer Combineer tabellen Sla afgeleide gegevens op Voeg indexen toe Sorteer (clustered index)

Bekijk één tabel tegelijk, voor alle functies. Ga pas naar een bovenliggende tabel als je alle onderliggende gehad hebt. Maak steeds een kosten-baten analyse. Elke optimalisatie heeft z'n prijs. Elke optimalisatie moet per saldo voordeel opleveren.

Hoofdstuk: De Technische Optimalisatie:

Bij elk van deze acties geldt: Werk bottom-up. Begin optimalisatie bij de corebusiness, de tabellen voor de high frequency functies. Dit zijn de tabellen onderaan het model.

5 1

DATABASE ontwerpen

1 januari 2011

Ranges Het kan soms handig zijn om met ranges te werken. Postcode

Straat

Plaats

2803 DA

Groenhovenweg

Gouda

2803 DB

Groenhovenweg

Gouda

2803 DC

Groenhovenweg

Gouda

...

...

...

2803 DL

Groenhovenweg

Gouda

Dit zou met een range kunnen. Begin Postcode

Eind Postcode

Straat

Plaats

2803 DA

2803 DL

Groenhovenweg

Gouda

Pas op: Ranges maken de programmatuur ingewikkelder. Toevoegen van een extra postcode kan ingewikkeld worden:

Hoofdstuk: De Technische Optimalisatie:

Niets doen, de postcode valt al in een bestaande range? Een nieuwe range maken? De begin postcode van een bestaande range verlagen? De eind postcode van een bestaande range verhogen? Een bestaande range splitsen en een nieuwe range tussenvoegen?

5 2

DATABASE ontwerpen

1 januari 2011

Denormaliseer Kijk of je een dure join in een high frequency functie kunt vermijden door kolommen uit een bovenliggende tabel te de-normaliseren, kopiëren naar een lagere tabel. Bijvoorbeeld: Kopieer straatnaam & plaats naar lezer. Nadeel van denormaliseren: Als de gegevens in de one tabel wijzigen moet alle kopieën in de many ook bijgewerkt worden. Voorbeeld: Als de straatnaam wijzigt moeten alle kopieën van de lezers in die straat ook bijgewerkt worden. Denormaliseer vooral data die nauwelijks wijzigt, zoals straatnaam. Een straat wijzigt zeer zelden van naam. Kijk vooral naar high frequency functies die veel gegevens lezen uit diverse tabellen. (Een lijst van abonnementen, inclusief adres gegevens bijvoorbeeld). Pas op voor schijnoptimalisatie. Als je 9 elementen denormaliseert maar één essentiële niet, blijft de join nodig. Denormaliseer niet als: het per saldo een nadeel oplevert de software onacceptabel complex zou worden.

Combineer tabellen Het kan zijn dat na denormalisatie een tabel geen bestaansrecht meer heeft.

Check of de tabel echt weg kan. Pas op: De bovenste tabel, 'Gebied' kan nog wel bestaansrecht hebben, om een aantal redenen: 1. als je een gebied kunt hebben zonder lezers (een one zonder manies). Bijvoorbeeld als input ondersteuning, een lijst met alle gebieden tonen bij invoeren van een nieuwe lezer. 2. als een gebied nodig is ter validatie of input ondersteuning, bijvoorbeeld een lijst met geldige postcodes. 3. Als de tabel nog nodig is voor andere functionaliteit.

Hoofdstuk: De Technische Optimalisatie:

Een voorbeeld: Alle elementen uit de tabel Gebied zijn gekopieerd naar lezer.

5 3

DATABASE ontwerpen

1 januari 2011

Sla afgeleide gegevens op Een mooi voorbeeld van een nuttig afgeleid gegeven is een banksaldo. Dat zou je kunnen afleiden aan de hand van alle bij- en afschrijvingen, sinds het beginsaldo 0. Dit is echter niet praktisch. In het logisch ontwerp, bij de UNF, Unnormalised Form heeft u de afgeleide gegevens apart gehouden. Deze kunt u nu nalopen. Een voorbeeld van afgeleide gegevens zijn totalen en statistieken, bijvoorbeeld het aantal lezers in een gebied. Pas op met opslaan van afgeleide gegevens. Als er iets aan de brongegevens verandert, moet het afgeleide gegeven mee veranderen. Bij dynamische brongegevens is het beter om elke keer af te leiden.

Voeg indexen toe Nut van een index Bij grote tabellen kan een index de performance verbeteren. Een index kan 1. rows uniek maken met enkelvoudige of compound keys 2. Zoekacties versnellen op o data waarden of Keys o foreign keys, die u in TNF met een sterretje gemerkt hebt. Dit soort indexen kan vooral joins erg versnellen. o een combinatie van data waarden, keys en foreignkeys. 3. een tabel sorteren

   

is gesorteerd op key waarden, (die niet hetzelfde hoeven te zijn als die van de tabel) is klein, heeft slechts enkele kolommen van de tabel. verwijst voor een keywaarde naar het juiste blok binnen de tabel. maakt het lezen van een row sneller, als je de juiste zoekargumenten weet.

Tips 

 

Zet het meest unieke datelement voorop in de index, het element dat de meest verschillende waardes heeft. De index kan dan sneller de juiste bladzijde vinden. Liever postcode-land dan land-postcode. Hou indexen klein. Liever een index op alleen postcode, dan op postcode-land. Hoe kleiner de index des te beter de response tijd. Voor hoogfrequente leesfuncties (duizenden keren per dag) kan het verstandig zijn om juist een erg uitgebreide index te maken zodat het systeem voor de leesfunctie de tabel niet eens nodig heeft.

Hoofdstuk: De Technische Optimalisatie:

Hoe werkt een index? Een index

5 4

DATABASE ontwerpen   

1 januari 2011

Voor kleine tabellen is een index nadelig. Het systeem kan dan voor iedere functie beter de hele tabel doorlopen. Een index zou alleen maar ophouden. Het heeft geen zin om een index te maken voor een functie die toch de hele tabel doorgaat. Het is dan efficiënter om zonder index heel de tabel door te lopen. U spaart dan bovendien de extra index processing uit bij toevoegen, wijzigen en verwijderen. Maak voor elke index een kosten-baten analyse. Weegt de snellere leesfunctie op tegen de tragere toevoegen/wijzigen/verwijderen? Hou rekening met de frequentie en zwaarte van de functies. Een lichte leesfunctie met een frequentie van 10.000x per dag heeft meer gewicht dan een zware verwijder functie van 1x per maand.

Een index vertraagt toevoegen, wijzigen en verwijderen. Niet alleen de tabel, maar ook de index moet bijgewerkt worden. Leg dus bij voorkeur een index op waarden waarmee wel veel gezocht wordt, maar die niet veel veranderen. Liever een index op bankrekeningnummer dan op saldo.



Ongesorteerd, het systeem moet kris-kras de hele tabel door om de rows te vinden die bij elkaar horen.



Gesorteerd. Het systeem hoeft maar één blok op te halen. De rows staan bij elkaar.

Zoek in de documentatie van uw RDBMS op clustered index.

Hoofdstuk: De Technische Optimalisatie:

Sorteer Sommige RDBMS-sen hebben de mogelijkheid om tabellen te sorteren (naast natuurlijk indexen).

5 5

DATABASE ontwerpen

1 januari 2011

Implementatie Elk RDBMS heeft zo z'n eigen manier om een database in te richten. Op Internet staat een overvloed aan documentatie. Zoek bijvoorbeeld met Google (www.google.com) op "Oracle create database documentation". In het volgende deel gaat u databases en tabellen maken in een MySQL database. Tevens leert u de onderliggende SQL syntax, die voor bijna alle RDBMSsen hetzelfde is.

Maak tabel SQL syntax Met één SQL statement maakt u de tabel en alle kolommen aan:

( )

Voorbeeld CREATE TABLE abonnement ( krantNaam VARCHAR (35) not null, sofiNummer INT not null ) Tips Zet velden met een vaste lengte vooraan. Zet het grootste varchar veld achteraan. Hou de kolommen een beetje in een logische volgorde.

Extra kolom

Voorbeeld ALTER TABLE abonnement ADD proefAbonnement CHAR (1) not null

Maak Index Bij technische optimalisatie heeft u indexen ontworpen. Hier ziet u hoe u zo'n index kunt maken, eventueel met de optie cluster om de tabel te sorteren.

Hoofdstuk: Implementatie

Een bestaande tabel kunt u makkelijk uitbreiden met een extra kolom:

5 6

DATABASE ontwerpen

1 januari 2011

SQL syntax

De opties ASC (ascending, van laag naar hoog) en DESC (descending, van hoog naar laag) geven de sortering van de index aan. Deze optie kunt u meestal weglaten.

Hoofdstuk:

Voorbeeld CREATE UNIQUE INDEX abonnement_ID ON abonnement ( sofiNummer, krantNaam ) Dit maakt een index met de naam "abonnement_ID" voor de tabel abonnement. De combinatie van sofiNummer en krantNaam wordt uniek, mag niet dubbel voorkomen.

5 7

DATABASE ontwerpen

1 januari 2011

Verklarende woordenlijst Column In één kolom (data-element) staan gegevens van dezelfde soort, bijvoorbeeld de kolom postcode. Compound Key Een compound key (samengestelde sleutel) is een key die uit meerdere kolommen bestaat, omdat één kolom niet uniek genoeg is. Voorbeeld van een compound key: postcode & huisnummer samen identificeren een adres. Database Een database is een verzameling tabellen, met onderling gerelateerde gegevens.

Foreign Key Een foreign key (vreemde sleutel) is de verbindende schakel tussen twee tabellen. Met een waarde uit een rij van de ene tabel kun je in een andere tabel de juiste rij met gerelateerde gegevens opzoeken. De ene tabel geeft als het ware de sleutel voor de andere, 'vreemde' tabel High Frequency Functie

Index Een index in een database lijkt op een index achter in een boek. Met een zoekwoord kun je de juiste bladzijde nummers vinden van rijen, binnen een tabel. Join Bij een join combineert u kolommen uit verschillende tabellen tot één geheel. U maakt bijvoorbeeld een overzicht van lezers, met de naam van de kranten waar ze op geabbonneerd zijn.

Hoofdstuk: Verklarende woordenlijst

Een high frequency functie is een functie die zeer vaak gebruikt wordt, honderden of zelfs duizenden keren per dag.

5 8

DATABASE ontwerpen

1 januari 2011

Key Een key (sleutel) bestaat uit één of meerdere kolommen. Wanneer je de juiste key waarde weet kun je de juiste rij vinden met de gewenste informatie. De key is als een sleutel waarmee je toegang krijgt tot de juiste rij. Een key is uniek. Een key waarde mag niet dubbel voorkomen in een tabel. Met een key vind je dus hooguit één rij. Many to Many relatie Een many to many relatie komt veel voor. Een krant heeft bijvoorbeeld vele lezers en een lezer leest vele kranten. Een many-to-many relatie is onduidelijk. Many-to-many relaties zijn vaak een teken dat nog nadere analyse vereist is. Meestal kan de relatie een stuk duidelijker, door er een koppel tabel tussen te zetten, aan de 'boven' of aan de 'onder' kant. Many to One relatie Een Many to One relatie is hetzelfde als one-to-many, maar dan van een ander gezichtspunt. Vele lezers wonen in één gebied. Vele abonnementen kunnen van één en dezelfde lezer zijn. Vele abonnementen zijn op één en dezelfde krant. One to Many relatie De meeste relaties tussen tabellen zijn one-to-many.

In één gebied kunnen vele lezers wonen. Één lezer kan vele abonnementen hebben. Één krant kan vele abonnementen hebben. One to One relatie Een één op één relatie is uitzonderlijk in databases. Het kàn voorkomen, maar vaak is het een teken dat het database-ontwerp nog voor verbetering vatbaar is.

Hoofdstuk: Verklarende woordenlijst

Voorbeeld:

5 9

DATABASE ontwerpen

1 januari 2011

RDBMS Een Relational DataBase Management Systeem is software die U in staat stelt om een database te implementeren met tabellen, kolommen, en indexen. De Referentiële Integriteit tussen rows van diverse tabellen garandeert. Automatisch indexen bijwerkt. Een SQL zoekopdracht interpreteert en informatie uit diverse tabellen kan combineren. Redundantie Dubbel opslaan van gegevens, 'overbodige' copieën om het systeem sneller te maken. Welke gegevens u dubbel op kunt slaan komt aan bod in het hoofdstuk Denormaliseren. Referential Integrity Referentiële Integriteit zorgt ervoor dat een foreign key waarde altijd naar een bestaande row verwijst. Een "dode" verwijzing zoals in het plaatje kan dan niet meer voorkomen. Er zijn een paar mogelijke maatregelen die referentiële integriteit afdwingen. Bij het maken of wijzigen van een rij controleert het systeem of de foreign keys wel geldige waarden hebben. Daarnaast moet de database-ontwerper een keuze maken voor een delete: Je mag een rij in de one tabel pas weggooien als er geen gerelateerde many rows meer zijn. Bij het verwijderen van een row in de one tabel gooit het RDBMS automatisch alle gerelateerde gegevens in de many tabel weg. Dit heet een cascaded delete.

Row Een rij (= tuple, entry) zijn gegevens die bij elkaar horen, bijvoorbeeld de gegevens van één abonnement. In één rij kunnen géén lijstjes staan.

Hoofdstuk: Verklarende woordenlijst

Bij het verwijderen van de laatste 'many' gooit het RDBMS automatisch de gerelateerde 'one' row weg.

6 0

DATABASE ontwerpen

1 januari 2011

Tabel Website

Bedrijf

www.roc-teraa.nl

ROC ter AA Helmond

www.willemwever.com Willem Wever www.bol.com

Bol.com

Een tabel is een matrix met gegevens. Een tabel in een database lijkt op een eenvoudige spreadsheet.

Hoofdstuk: Verklarende woordenlijst

Bovenstaand voorbeeldje heeft 3 rijen (rows) en 2 kolommen (columns).

6 1

DATABASE ontwerpen

1 januari 2011

Vragen deel 1b Op de volgende bladzijden vindt U een aantal orderformulieren, inschrijfformulieren. De opgave is om deze formulieren om te zetten in een database in de derde normaalvorm. 1. 2. 3. 4. 5. 6.

Bepaal eerst de scope (Het bereik) van te maken database Maak de “nulde” normaal vorm Zet dan het geheel in de eerste normaal vorm Daarna in de tweede normaal vorm En als laatste in de derde normaal vorm. Maak het ERD diagram voor de database

Zorg voor een duidelijke uitwerking. Geef aan welke stappen je hebt gebruikt om van de ene in de andere normaal vorm te komen. Doe het ook als het veel werk is. Vaak werkt het op papier of met papierstickers makkelijker als op een computer.

Hoofdstuk: Vragen deel 1b

Lever de opdrachten in zoals de docent het heeft aangegeven.

62

DATABASE ontwerpen

1 januari 2011

Hoofdstuk: Vragen deel 1b

Opgave 1-1.

63

DATABASE ontwerpen

1 januari 2011

Hoofdstuk: Vragen deel 1b

Opgave 1-2.

64

DATABASE ontwerpen

1 januari 2011

Hoofdstuk: Vragen deel 1b

Opgave 1-3.

65

DATABASE ontwerpen

1 januari 2011

Opgave 2-1

Hoofdstuk: Vragen deel 1b

Let op! Twee pagina’s

66

DATABASE ontwerpen

1 januari 2011

Vervolg opgave 2-1

Opgave 2-2 Op het volgende webadres vind je een inschrijf formulier gebruik dat voor je opgave moet

gebruiken.

Hoofdstuk:

http://www.skon.nl/inschrijven/inschrijfformulier

67