Database-ontwerp

Database-ontwerp
op basis van een SQL-database
HA-1611-01
Boekhouden-informatica, Informaticabeheer
Marc De Wandel, Ria Van Eysendeyk
Werkgroep Handel
2009
Vlaams Verbond van het Katholiek Secundair Onderwijs
Guimardstraat 1, 1040 Brussel
Vlaams Verbond van het Katholiek Secundair Onderwijs
Guimardstraat 1, 1040 Brussel
Database-ontwerp
op basis van een SQL-database
HA-1611-01
Boekhouden-informatica, Informaticabeheer
Marc De Wandel, Ria Van Eysendeyk
Werkgroep Handel
2009
http://handel.vvkso.net
D/2009/7841/067
Alle rechten voorbehouden. Behoudens de uitdrukkelijk bij wet bepaalde uitzonderingen mag niets uit deze
uitgave worden verveelvoudigd, opgeslagen in een geautomatiseerd gegevensbestand of openbaar gemaakt,
op welke wijze ook, zonder de uitdrukkelijke voorafgaande en schriftelijke toestemming van het VVKSO.
Inhoud
1
Werken met data ..................................................................................................... 7
1.1
1.2
1.3
1.4
1.5
Databases ................................................................................................................. 7
Databasesystemen ................................................................................................... 7
Stappen bij het ontwerpen van een database .......................................................... 8
Datamodel................................................................................................................. 9
De data dictionary ................................................................................................... 10
2
Het Entiteit-Relatie model .................................................................................... 11
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
Entiteiten ................................................................................................................. 11
Relatie ..................................................................................................................... 11
Cardinaliteit ............................................................................................................. 12
Optionaliteit ............................................................................................................. 14
Attributen................................................................................................................. 14
Voorbeelden............................................................................................................ 16
Uitgewerkt voorbeeld .............................................................................................. 18
Oefeningen ............................................................................................................. 21
3
Een relationele databank ..................................................................................... 23
3.1
3.2
3.2.1
3.2.2
3.2.3
3.2.4
3.2.5
3.3
3.4
3.5
3.6
3.7
3.8
3.9
3.10
3.11
3.12
3.12.1
3.12.2
3.12.3
3.12.4
3.12.5
3.12.6
3.12.7
3.13
Tabellen .................................................................................................................. 23
Sleutels ................................................................................................................... 23
Primaire sleutel ....................................................................................................... 23
Kandidaatsleutel ..................................................................................................... 24
Alternatieve sleutel ................................................................................................. 24
Surrogaatsleutel ...................................................................................................... 24
Refererende sleutel ................................................................................................ 24
Namen van velden .................................................................................................. 25
Koppelingen tussen tabellen................................................................................... 25
Het normalisatieproces ........................................................................................... 26
Normalisatievormen ................................................................................................ 27
Niet genormaliseerde gegevensgroepen................................................................ 27
Repeterende groepen: NF1 .................................................................................... 28
Partiële functionele afhankelijkheid: NF2 ............................................................... 33
Transitieve functionele afhankelijkheid: NF3 .......................................................... 35
Overzicht ................................................................................................................. 37
Voorbeelden............................................................................................................ 38
Oefening 1: facturatie ............................................................................................. 38
Oefening 2: artikelfiche ........................................................................................... 40
Oefening 3: abonnementen .................................................................................... 41
Oefening 4: stages .................................................................................................. 41
Oefening 5: projecten ............................................................................................. 42
Oefening 6: cursussen ............................................................................................ 42
Oefening 7: computerpark ...................................................................................... 43
Oefeningen ............................................................................................................. 45
4
Omzetting datamodel (ERD) naar databaseontwerp (tabellen)........................ 46
4.1
4.1.1
4.2
4.2.1
4.2.2
4.2.3
4.2.4
4.3
4.3.1
4.3.2
4.3.3
4.3.4
Entiteiten ................................................................................................................. 46
Zelfstandige entiteiten ............................................................................................. 46
Relaties zonder attributen ....................................................................................... 46
Optionaliteit ............................................................................................................. 46
Één-op-één relatie .................................................................................................. 46
Eén-op-veel relatie .................................................................................................. 47
Veel-op-veel relatie ................................................................................................. 48
Relaties met attributen ............................................................................................ 48
Eén-op-één relatie .................................................................................................. 48
Eén-op-veel relatie .................................................................................................. 49
Veel-op-veel relatie ................................................................................................. 49
Overzicht ................................................................................................................. 50
4.4
4.5
Uitgewerkt voorbeeld .............................................................................................. 51
Oefeningen .............................................................................................................. 54
5
Een database implementeren .............................................................................. 55
5.1
5.2
5.3
5.4
5.4.1
5.5
5.6
5.7
5.7.1
5.7.2
5.8
5.8.1
5.8.2
5.9
Een SQL-databank aanspreken in Visual Web Developer ..................................... 55
Een SQL-databank aanmaken in VWD Express 2008 ........................................... 57
Relaties tussen tabellen .......................................................................................... 59
Table designer ........................................................................................................ 60
Diagrammen ............................................................................................................ 62
Check constraints.................................................................................................... 63
Standaardwaarden .................................................................................................. 64
Indexen.................................................................................................................... 65
Inleiding ................................................................................................................... 65
Een index aanmaken .............................................................................................. 65
Tabelaanpassingen ................................................................................................. 66
Rijen aanpassen ..................................................................................................... 66
Primaire sleutel ....................................................................................................... 66
Oefeningen .............................................................................................................. 66
6
Speciale technieken .............................................................................................. 67
6.1
6.1.1
6.1.2
6.1.3
6.1.4
6.2
6.2.1
6.2.2
Join-eigenschappen ................................................................................................ 67
Een tabel koppelen met zichzelf ............................................................................. 67
Verbinding op meer dan 1 veld ............................................................................... 67
Meer dan 2 tabellen gebruiken ............................................................................... 68
Oefeningen .............................................................................................................. 69
Niet-genormaliseerde gegevensgroepen normaliseren .......................................... 71
Gegevens worden in rijen herhaald ........................................................................ 71
Gegevens worden in kolommen herhaald .............................................................. 74
7
Bijlagen .................................................................................................................. 75
7.1
7.2
7.2.1
7.2.2
7.3
Bijlage 1. Een bestaande Access-databank converteren naar een SQL-databank 75
Bijlage 2. Korte samenvatting van Sql-commando’s .............................................. 79
Selectiequery’s ........................................................................................................ 79
Actiequeries ............................................................................................................ 80
Bijlage 3. Lijst met belangrijkste types .................................................................... 81
1
Werken met data
•
•
•
•
1.1
Het begrip database
Soorten database managementsystemen
Het begrip datamodel
Datadictionary
Databases
Wat is een database?
Meestal speelt het voor langere tijd bewaren van gegevens een belangrijke rol in informatiesystemen. Denk bijvoorbeeld aan de loonadministratie in een bedrijf.
Welke gegevens moeten hiervoor onder andere bijgehouden worden?
Als deze gegevens op een gestructureerde, digitale manier bijgehouden worden, is het mogelijk de
verwerking van die gegevens, bijvoorbeeld tot jaarlijkse loonfiches voor alle werknemers, te automatiseren. Dat bespaart hopen werk!
Keerzijde van de medaille: een degelijke structuur opbouwen om de gegevens te bewaren, kost
soms de spreekwoordelijke bloed, zweet en tranen. Het is gedeeltelijk een wetenschap en gedeeltelijk een kunst. Het wetenschappelijke deel is het onderwerp van de volgende hoofdstukken: je leert
hoe je, door het gebruik van bepaalde technieken, tot een werkbare structuur komt.
Zo'n gestructureerde digitaal bewaarde verzameling gegevens, opgezet om in een bepaalde informatiebehoefte te voorzien, wordt een database of gegevensbank genoemd.
Wat is data?
Algemeen wordt een onderscheid gemaakt tussen data en informatie.
Data (of gegevens): ruwe feiten of observaties met betrekking tot fysieke fenomenen (bv. een klant)
en bedrijfstransacties (bv. een verkoop). Data zijn objectieve meetwaarden van de karakteristieken
van ‘entiteiten’ (mensen, plaatsen en gebeurtenissen).
Informatie: data in een betekenisvolle en nuttige context voor specifieke eindgebruikers. Informatie
is het resultaat van een value-added process (d.i. data processing).
1.2
Databasesystemen
Een databasesysteem is een verzameling van elementen waarmee we een database kunnen exploiteren (Vandenbulcke et al. p. 19).
In een database worden gegevens digitaal bijgehouden. Je hebt dus software nodig om de gegevens in te brengen en te beheren. De software die het werken met een database mogelijk maakt is
het database managementsysteem (DBMS). Het databasemanagement is een onderdeel van een
databasesysteem. Het geheel van database en DBMS is dan het databasesysteem (DBS).
Relationele gegevensbanken vormen thans het meest verspreide systeem voor het beheer van
persistente (te bewaren) gegevens. Andere, oudere vormen van gegevensbeheer zijn onder meer:
hiërarchische databases en netwerkdatabases. Een recente ontwikkeling is de opkomst van objectgeoriënteerde databases, ook wel object bases genoemd. Momenteel werken naar schatting
wereldwijd 80 % van de databases relationeel. In 1970 legde Edgard F. CODD de basis voor het
relationele gegevensmodel.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 7
Ze zijn gebaseerd op het Relationeel model (hierover later meer). In een RDBMS of Relationele
Database Managementsystemen worden gegevens typisch bijgehouden in tabellen (rijen en
kolommen).
RDBMSen zijn zeer geschikt voor applicaties met eenvoudige data en uitgebreide bevragingsmogelijkheden, de typische administratieve applicaties. Enkel eenvoudige datatypes (numeriek, tekst …)
kunnen bijgehouden worden. Een gebruiker kan zelf geen datatypes toevoegen. Een grote troef
voor RDBMSen is dat ze allen bevraagd kunnen worden met een standaardtaal: SQL (Structured
Query Language).
MS-Access, Oracle, MySql en Microsoft SQL Server, zijn voorbeelden van RDBMS-en. In de
cursus wordt gebruik gemaakt van Sql-databases.
In een relationeel databanksysteem worden gegevens bijgehouden in tabellen (Engels Æ relations)
waartussen relaties (Engels Æ relationships) kunnen liggen. Die relaties verbinden de gegevens in
de verschillende tabellen.
De tabellen bestaan uit rijen (records of tupels) en kolommen (velden). In elke rij staan de gegevens
van één item, bijvoorbeeld een product. Elk kenmerk komt overeen met een kolom, bijvoorbeeld de
productnaam.
De grote vraag bij het opmaken van een database voor zo’n relationeel systeem is natuurlijk: welke
tabellen heb ik nodig en wat is hun inhoud?
1.3
Stappen bij het ontwerpen van een database
Personen die betrokken zijn bij het ontwerp van een database zijn: database ontwerpers, database
administrators (verantwoordelijk voor het onderhoud van en de aanpassingen aan databasessystemen) en eindgebruikers.
Het motto voor het ontwerpen van een database is zeker: 'Bezint voor je begint'. Een slecht ontworpen database kan een firma letterlijk duur te staan komen. Databaseontwerpers - voor grote databases is dit teamwerk- worden goed betaald. Maar voor wat, hoort wat natuurlijk! De verantwoordelijkheid is groot en de job niet altijd even gemakkelijk.
Het ontwerp van een database verloopt in verschillende stappen die hieronder schematisch weergegeven worden.
Pagina 8
Database-ontwerp
Analyse van de informatiebehoefte
Bepalen van het domein. Benoemen van de entiteiten
Opstellen en documenteren van het datamodel
Keuze van het DBMS
Opstellen, invoeren en documenteren van het dataontwerp
Invoer data
Aan de hand van de analyse van de informatiebehoefte kan een datamodel (zie verder) opgesteld
worden. Als dit gebeurd is, kan de structuur bepaald worden waarin de gegevens zullen bewaard
worden: het DBMS waarin gewerkt wordt, wordt gekozen. Het datamodel moet vertaald worden naar
een dataontwerp (dataschema) dat kan ingevoerd worden in het DBMS. Als het dataontwerp geïmplementeerd is, kunnen uiteindelijk de data ingevoerd worden.
1.4
Datamodel
Wat is een model?
Een model is een abstracte voorstelling van (een deel van) de reële wereld. Dit wil zeggen dat de
maker van een model bepaalde essentiële kenmerken van de realiteit isoleert, en alle andere kenmerken als bijkomstig achterwege laat.
Een gegevensmodel of datamodel is een abstracte voorstelling van een informatiesysteem, waarbij alle dynamische aspecten (generatie, transformatie en verwerking van gegevens) achterwege
blijven en slechts de statische informatiestructuren, hun beperkingen en hun onderlinge verbanden
van belang zijn.
Een datamodel gaat over de structuur van gegevens, niet over de inhoudelijke opvulling. Een gegevensmodel kan specifiëren dat een bedrijf de namen en adressen van zijn werknemers opslaat.
Het zegt niet dat er een werknemer bestaat met de naam Peeters en evenmin dat de namen en
adressen worden verkregen via de personeelsdienst. Het kan wel bepalen dat ieder adres een postnummer moet bevatten.
Een procesmodel brengt de dynamische aspecten in beeld. Een gegevensstroomdiagram (Data
Flow Diagram, DFD) is hiervan een voorbeeld. Het toont op een visuele manier waar de data gegeneerd en verwerkt worden en waarheen ze getransporteerd worden.
Een gegevensmodel wordt gebruikt voor het structureren van een database terwijl een gegevensstroomschema eerder gebruikt wordt voor het programmeren.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 9
Waarom modelleren?
Een gegevensmodel moet een duidelijke, éénduidige voorstelling geven van de gegevens en hun
onderlinge verbanden. Het moet voldoende visueel zijn zodat het kan dienen als communicatiemiddel tussen de verschillende belanghebbenden van het informatiesysteem:
-
ontwerpers;
analisten;
gebruikers;
beheerders;
...
Pas als alle betrokken partijen het eens zijn over het model, kan er verder ontwikkeld worden.
Welk model gebruiken?
Er worden heel wat verschillende datamodellen gebruikt. Het Entiteit-Relatie (Entity-Relationschip)
Model is een van de bekendste traditionele modellen. Er bestaan verschillende 'dialecten' van, ze
verschillen hoofdzakelijk in notatie.
Een voordeel van het gebruik van een algemeen bekend model is dat de samenwerking tussen de
verschillende mensen die betrokken zijn bij het ontwerp, vlot kan verlopen. Geen extra trainingen
nodig: iedereen kan het model interpreteren en er zijn/haar zegje over hebben.
In het volgende hoofdstuk maak je kennis met een vereenvoudigde vorm van het Entiteit-Relatie
Model.
1.5
De data dictionary
Een datadictionary is een database waarin gegevens over een databasesysteem worden opgeslagen. Het is dus een gereserveerde ruimte in een database die wordt gebruikt om informatie op te
slaan over de database zelf.
Hij bevat dus gegevens over gegevens, die nuttig kunnen zijn bij het werken met een database.
Onder andere volgende informatie kan steeds verstrekt worden:
•
•
•
•
welke gegevens zijn er beschikbaar?
hoe zijn ze opgeslagen en gedefinieerd?
wie mag welke gegevens gebruiken?
…
Een datadictionary bevordert eenduidigheid in een databasesysteem. Door het gebruik van een
datadirectionary verloopt een automatiseringsproces efficiënter. De praktijk wijst op een tijdsbesparing van 10 à 15%.
Een datadirectionary kan manueel of geautomatiseerd worden bijgehouden. Er zijn verschillende
datadirectionarysystemen op de merkt (bv. Oracle Data Dictionary, DB2, Interbase, SAP R/3 Basic
System ABAP Data Dictionary, Microsoft SQL server, …).
Pagina 10
Database-ontwerp
2
Het Entiteit-Relatie model
•
•
•
•
Entiteiten en relaties
Cardinaliteit
Optionaliteit
Attributen
In dit hoofdstuk maak je kennis met een datamodel dat zeer geschikt is voor het ontwerpen van een
database: het Entiteit-Relatie Model (Entity Relationship Model), afgekort ER-model. Het is in
1976 – in informaticaland héél lang gelden - door P. Chen geïntroduceerd. Sindsdien zijn er verschillende varianten ontwikkeld.
In het Entiteit-Relatie model wordt gebruik gemaakt van:
-
entiteiten;
-
attributen;
-
relaties (relationships).
2.1
Entiteiten
Een entiteit is een object (een tastbaar iets), een concept, iets dat je kunt identificeren en waarvan
je gegevens wilt bijhouden: auto, persoon, magazijn, bankrekening, computer, een lied, ...
Een entiteit bestaat uit:
-
een unieke naam;
-
een definitie die objectief en ondubbelzinnig aangeeft, welke objecten door de entiteit worden
gemodelleerd (en soms even belangrijk: welke niet);
-
een lijst van attributen (zie verder).
Voorbeeld
Naam
werknemer
Definitie
een natuurlijke persoon die in loondienst is van de firma; personen die slechts occasioneel voor de firma werken en personen die via een interim-kantoor tewerkgesteld
zijn, worden niet als werknemers beschouwd.
Attributen
naam, adres, telefoonnummer, geboortedatum, datum-in-dienstname, …
Voorstelling
Een entiteit wordt voorgesteld door een rechthoek.
Werknemer
2.2
Relatie
Een relatie is een verband tussen twee of meer entiteiten dat model staat voor een overeenkomstige band tussen de reële objecten zoals “een werknemer werkt in een bedrijf”. “Werkt in” is hier de
relatie tussen de entiteiten werknemer en bedrijf. Een relatie bestaat dus uit:
-
een naam, uniek binnen het beschouwde diagram;
-
een lijst van betrokken entiteiten;
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 11
-
een definitie die objectief en ondubbelzinnig aangeeft, in welke omstandigheden precies het
veronderstelde verband aan- of afwezig is tussen de gespecificeerde entiteiten;
-
een lijst van attributen (zie verder).
Voorbeeld
Naam
Werkt in
Deelnemende entiteiten
werknemer, afdeling
Definitie
een werknemer werkt in een afdeling als hij verantwoording verschuldigd is aan het hoofd van die afdeling
Attributen
aantal werkuren
Voorstelling
Een relatie wordt voorgesteld als een verbindingslijn tussen de betrokken entiteiten.
Werknemer
Werkt in
Afdeling
De naam van de relatie kan al dan niet vermeld worden. Het pijltje is hier enkel een hulpmiddel bij
het lezen van het diagram van links naar rechts: werknemer werkt in afdeling.
Het pijltje kun je tevens in de andere richting tekenen:
Werknemer
2.3
Geeft werk aan
Afdeling
Cardinaliteit
De cardinaliteit van een relatie geeft aan, hoeveel instanties van een entiteit maximaal in relatie
kunnen staan met één instantie van de andere entiteit. Daarbij zijn de toegelaten waarden: één of
veel.
Voorbeeld
Als je de relatie 'persoon is ondergeschikte van manager' bekijkt, dan geldt waarschijnlijk het volgende:
-
iedere werknemer heeft (is ondergeschikt aan) hoogstens één manager (één-cardinaliteit):
Werknemer
-
Manager
een manager kan veel (ondergeschikte) werknemers hebben (veel-cardinaliteit):
Werknemer
Pagina 12
Manager
Database-ontwerp
In een gegevensstructuurdiagram geven we de cardinaliteit aan door een markering van de lijnen
(relaties) op de plaats waar ze de rechthoeken (entiteiten) bereiken:
-
als de instantie van de entiteit uniek is (d.w.z. hoogstens één), plaats je een dwarsstreepje op
de lijn vlakbij de entiteit;
-
als de instantie van de entiteit verscheidene malen kan voorkomen (d.w.z. hoogstens veel),
plaats je een kraaienpoot op de lijn tegen de entiteit.
Werknemer
Manager
Voor een relatie tussen twee entiteiten zijn er dus, wat de cardinaliteit betreft, de volgende mogelijkheden: één-op-één, één-op-veel en veel-op-veel. Een één-op-één-relatie wordt ook 1:1 genoteerd,
een één-op-veel-relatie 1:n (lees: één op n) en een veel-op-veel-relatie m:n (lees: m op n).
Om de cardinaliteit te bepalen, stel je een vraag in de vorm:
-
kan een werknemer meer dan één manager hebben?
-
kan een manager meer dan één werknemer hebben?
Bij een positief antwoord: cardinaliteit veel, anders één.
I.v.m. de cardinaliteit kan je bijgevolg 3 soorten relaties hebben.
Voorbeeld:
Eén-op-één relatie:
Klas
Onderwijzer
Iedere klas heeft maximaal 1 onderwijzer en iedere onderwijzer heeft maximaal 1 klas. Deze situatie
komt veel voor in het lager onderwijs.
Eén-op-veel relatie:
Klas
Onderwijzer
Iedere klas heeft maximaal 1 onderwijzer en iedere onderwijzer kan een aantal klassen hebben.
Deze situatie komt soms voor in het lager onderwijs als het aantal leerlingen zeer klein is.
Veel-op-veel relatie:
Klas
Leraar
Iedere klas kan een aantal leraars hebben en iedere leraar kan een aantal klassen hebben. Dit is de
situatie in het secundair onderwijs.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 13
2.4
Optionaliteit
De optionaliteit van een relatie geeft aan, hoeveel instanties van een entiteit minimaal in relatie
moeten staan met één instantie van de andere entiteit. Daarbij zijn de toegelaten waarden: nul of
één.
Voorbeeld
Als je in een database de entiteiten ‘ambtenaar' en 'huwelijkscontract' beschouwt, met daartussen
de relatie 'handtekent', dan gelden de volgende vaststellingen omtrent de optionaliteit:
-
een ambtenaar handtekent niet noodzakelijk een huwelijkscontract;
-
ieder huwelijkscontract moet gehantekend worden door een ambtenaar.
In een gegevensstructuurdiagram geven we de optionaliteit aan door het aanbrengen van een symbool op de lijnen (relaties) dicht bij de rechthoeken (entiteiten), naast de tekens voor cardinaliteit:
-
als de instantie van de entiteit verplicht is (d.w.z. minstens één), plaats je een dwarsstreepje op
de lijn;
-
als de instantie van de desbetreffende entiteit optioneel is (d.w.z. minstens nul), plaats je een
cirkeltje op de lijn.
handtekent
ambtenaar
huwelijkscontract
Het verplichte karakter van een relatie is een zeer strenge eis. Als een relatie verplicht is, wil dat
zeggen dat je geen enkele situatie kunt modelleren waarin de relatie niet voorkomt, zelfs niet tijdelijk.
Om de optionaliteit te bepalen, stel je een vraag in de vorm:
-
moet een ambtenaar minstens één huwelijkscontract ondertekenen?
-
moet een huwelijkscontract minstens door één ambtenaar ondertekend zijn?
Bij een positief antwoord: optionaliteit één, anders nul.
Samengevat geeft de cardinaliteit aan hoeveel instanties van een entiteit maximaal bij een instantie
van de andere entiteit kunnen horen (maximaal 1 of meerdere), terwijl de optionaliteit het minimum
aantal instanties van een entiteit aangeeft die horen bij een instantie van de andere entiteit (0 is
mogelijk of altijd minstens 1).
2.5
Attributen
Iedere entiteit en relatie is meestal voorzien van een verzameling attributen. Een attribuut is een
eigenschap die met de instanties van de entiteit of de relatie geassocieerd wordt. Entiteiten hebben
bijna altijd attributen. Relaties kunnen gemakkelijk voorkomen zonder attributen. Zo heeft een werknemer o.a. een voornaam, een familienaam, een woonplaats …
•
Samengestelde gegevens splitsen
Je wenst bijvoorbeeld gegevens op te slaan van personen zodat deze telefonisch of per brief kunnen gecontacteerd worden. Als attributen van de persoon heb je dan nodig: naam, adres en telefoonnummer. Je zou dus kunnen opteren voor drie attributen. Dit is echter niet handig. Als je bijvoorbeeld een adresetiket wilt samenstellen, heb je straat, postnummer en gemeente apart nodig.
Pagina 14
Database-ontwerp
De gegevens naam, adres en telefoonnummer zijn samengestelde gegevens: naam bevat zowel
voor- als familienaam, adres bevat straat, huisnummer, postnummer en gemeente en telefoonnummer bevat zonenummer en eigenlijke nummer. Als je de deelgegevens van een samengesteld gegeven afzonderlijk gebruikt, is het beter ze te beschouwen als afzonderlijke attributen. Zo splits je
naam waarschijnlijk best op in de attributen voornaam en familienaam. Adres kan gesplitst worden
in straat, postnummer en gemeente. Meestal zal het geen zin hebben om straatnaam en straatnummer afzonderlijk op te splitsen, gewoon omdat ze niet afzonderlijk gebruikt worden. In dit geval
mag je het samengesteld attribuut onveranderd laten staan in het ERD.
•
Procesgegevens
Een procesgegeven of berekend gegeven is een gegeven waarvan de waarde bepaald wordt door
de waarde van één of meer andere gegevens.
In een groothandel worden bijvoorbeeld artikelgegevens bijgehouden, o.a. naam, inkoopprijs, en
hoeveelheid in voorraad. Bij het interventariseren wil men, per artikel, de waarde van de voorraad
kennen. Je zou hiervoor een attribuut WaardeVoorraad kunnen voorzien waarbij WaardeVoorraad =
Inkoopprijs * HoeveelheidInVoorraad. Dit betekent echter dat om de integriteit van de database te
behouden bij elke verandering van de voorraad of de inkoopprijs ook de waarde van dit attribuut
moet aangepast worden. Daarom worden procesgegevens meestal niet opgenomen in de database.
Een ander voorbeeld is het bijhouden van de geboortedatum en de leeftijd van een persoon. De
leeftijd van de loper kan immers steeds worden afgeleid uit de geboortedatum.
Procesgegevens worden berekend op het moment dat ze nodig zijn (tijdens het proces).
Een attribuut bestaat uit de volgende elementen:
-
een naam, uniek binnen de collectie attributen van dezelfde entiteit of relatie;
-
een entiteit of relatie waarvan het attribuut een kenmerk is;
-
een domein, dit is de collectie van alle mogelijke waarden die het kenmerk kan aannemen;
-
een definitie die éénduidig de interpretatie van de gekozen waarde uit het domein aangeeft;
-
het al dan niet verplichte karakter van het attribuut: moet elke instantie van de gegeven entiteit
of relatie over deze eigenschap beschikken, of is dit optioneel, met andere woorden, modelleer
je ook instanties waarvoor deze eigenschap irrelevant of onbekend is.
Voorbeeld
Naam
personen ten laste
Entiteit
Werknemer
Domein
de natuurlijke getallen, inclusief 0
Definitie
het aantal personen dat fiscaal ten laste van de medewerker is, volgens opgave
door de medewerker zelf
Verplicht
Ja
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 15
Voorstelling
De attributen worden bij in de rechthoek geschreven. In diagrammen worden ze meestal weggelaten
om de overzichtelijkheid niet te schaden.
Werknemer
WerknemerID
Naam
Adres
Telefoonnummer
Datum-in-dienst
…
Zorg er voor dat bij de attributen van een entiteit een attribuut voorkomt dat de entiteit uniek identificeert (als later de ERD wordt omgezet naar een tabel zal dergelijk attribuut gebruikt worden als primaire sleutel).
De moeilijkste stap bij het opstellen van een ERD is het identificeren van de attributen die bij de
relaties horen.
Voorbeeld:
De gegevens van lopers aan meerdere loopnummers (100 m, 200 m, 400 m, …) worden bijgehouden. De lopers kunnen telkens een andere plaats behalen. Om het resultaat van een loper te bepalen, dien je dus steeds bij te vermelden welke loopnummer bedoeld wordt.
neemt deel aan
Loper
Loopnummer
behaalde plaats
De vraag wat de behaalde plaats is, is niet opportuun indien je niet weet over welke loper het gaat.
Het attribuut “Behaalde plaats” hoort dus thuis bij de relatie en niet bij de entiteit Loper of de entiteit
Loopnummer.
2.6
Voorbeelden
a) Datamodel van een taxibedrijf
1)
Chauffeur
Taxi
2)
Chauffeur
Taxi
3)
Chauffeur
Taxi
4)
Chauffeur
Taxi
Welke uitspraken horen bij welke ERD-diagram? Er zijn meerdere ERD-diagramama’s mogelijk bij
een bepaalde uitspraak.
Pagina 16
Database-ontwerp
Er kunnen taxi’s zonder chauffeur bestaan: …………………………….
Een taxi kan bestuurd worden door meerdere chauffeurs: …………………………….
Een chauffeur kan meerdere taxi’s besturen: …………………………….
Een taxi wordt maar door 1 chauffeur bestuurd: …………………………….
Een chauffeur rijdt maar met 1 taxi: …………………………….
b) Datamodel van een schooladministratie
Een school wenst een databank die bijhoudt hoeveel lesuren de leerling in elke richting van elk vak
krijgt.
Bij een richting hoort een uniek Id en een omschrijving (bv. Boekhouden-Informatica). Elk vak krijgt
krijgt eveneens een uniek Id en een vaknaam. Bij een bepaalde richting horen er steeds meerdere
vakken. Er wordt minimaal 1 vak gegeven per richting!
Er zijn uiteraard verschillende klassen die een klascode hebben (bv. 4HB). Er zijn op school richtingen waarvoor er meerdere klassen (kunnen) zijn zoals 4 Handel A en 4 Handel B. Zijn er geen inschrijvingen voor een bepaalde kals dan wordt die klas natuurlijk niet ingericht.
c) Datamodel van een plaatselijke vereniging
Bij een plaatselijke vrouwenvereniging wil men informatie over de leden zoals naam, adres, … in
een databank opslaan.Ook wil men in die databank gegevens van de kinderen van de leden (naam
en geboortedatum kind) opslaan. Elk lid en elk kind krijgen een uniek Id (de vaders kunnen uiteraard
zich niet inschrijven als lid).
d) Datamodel van een verkoop
Klant
Order
Verkoper
Product
Volgens dit datamodel gelden de volgende regels:
-
een klant die nog geen order heeft geplaatst, kan bestaan;
-
een verkoper die nog niets heeft verkocht, kan bestaan;
-
een product dat nog niet verkocht is kan bestaan;
-
een order van een bestaand product moet via een bestaande verkoper door een bestaande
klant gedaan worden.
Welke attributen zou je aan elke entiteit toekennen?
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 17
2.7
Uitgewerkt voorbeeld
Datamodel voor Computerpark
Er zijn 6 belangrijke entiteiten: Computer, Randapparaat, Software, Verbruiksproduct, Licentie
en Leverancier. Met randapparaat wordt een uitwendig randapparaat bedoeld, bijvoorbeeld: hub,
printer, modem, scanner …
De relaties tussen de entiteiten Levertx, Heeft, Draait op, Is verbonden met, Connecteert worden
getoond in het volgende diagram samen met hun cardinaliteit en optionaliteit. (In deze oplossing
wordt verondersteld dat de computers in netwerk verbonden zijn via een hub, d.w.z. de computers
zijn onderling niet rechtstreeks verbonden.)
Bestudeer het volgende diagram aandachtig. Ga, wat de cardinaliteit en de optionaliteit betreft, na wat deze precies betekenen. Waarom is bijvoorbeeld de relatie tussen Leverancier
en Verbruiksproduct anders dan tussen Leverancier en Computer of Leverancier en
Randapparaat?
Per entiteit worden volgende attributen bewaard. De domeinen worden niet aangegeven omdat ze
vrij evident zijn.
Computer
Naam
Definitie
ComputerID
identificatie van de computer, naam waaronder de PC gekend is, bijvoorbeeld PC01, Joske ...
Ja
Serienummer
serienummer gegeven door producent
Ja
Merk
merknaam of 'wit product'
Ja
Type
naam van het type zoals gekend bij producent
Pagina 18
Verplicht
Database-ontwerp
Lokaal
locatie waar toestel zich bevindt, bijvoorbeeld: B11, Directiebureel ...
Ja
Functie
bijvoorbeeld: lescomputer, labocomputer, server, internetserver ...
Ja
Processor
type en klokfrequentie
Ja
Harde schijf
merk, type, capaciteit … van alle ingebouwde schijven (samengesteld gegeven)
Diskettestation
merk, type … (samengesteld gegeven)
CD/DVD
merk en type van ingebouwd CD of DVD-station (samengesteld gegeven)
RAM
aanwezige hoeveelheid RAM
RAMtype
Type
RAMUitTeBreiden
uitbreidingsmogelijkheid
Beeldscherm
merk, type … (samengesteld gegeven)
Klavier
merk, type … (samengesteld gegeven)
Muis
merk, type … (samengesteld gegeven)
Geluidskaart
merk, type … (samengesteld gegeven)
Netwerkkaart
merk, type … (samengesteld gegeven)
ISATotaal
totaal aantal ISA-uitbreidingsgleuven
ISABezet
aantal bezette ISA-uitbreidingsgleuven
PCITotaal
totaal aantal PCI-uitbreidingsgleuven
PCIBezet
aantal bezette PCI-uitbreidingsgleuven
BIOS
naam
Moederkaart
naam
Chipset
naam
Extras
soort, merk en type van extra ingebouwde onderdelen, bijvoorbeeld zipdrive … (samengesteld gegeven)
Opmerkingen
algemene opmerkingen i.v.m. het toestel
Randapparaat
Naam
Definitie
RandapparaatID
identificatienummer van het specifieke randapparaat
Ja
Functie
functie, bijvoorbeeld: poort, netwerkkaart, modem, printer
Ja
Merk
naamproducent
Ja
Type
naam van het type zoals gekend bij producent
Ja
Beschrijving
voornaamste karakteristieken
Doel
waarvoor het randapparaat gebruikt wordt, bijvoorbeeld:
randapparaat, reserve, didactisch materiaal
VVKSO
Werkgroep Handel
Verplicht
Database-ontwerp - pagina 19
Software
Naam
Definitie
NaamSoftware
inclusief versienummer, bijvoorbeeld MS Word 9.0
Producent
bijvoorbeeld Microsoft
AantalCD
aantal installatie cd's
LocatieCD
aanduiding waar de cd’s bewaard worden
Verplicht
Ja
Licentie
Naam
Definitie
Verplicht
Licentienummer
of ‘freeware’, gegevens 'shareware' indien dit het geval is
Ja
Soort licentie
schoollicentie, klaslicentie, netwerk ...
Ja
Registratiedatum
datum waarop licentie geregistreerd is
AantalLicenties
aantal toestellen of gebruikers waarvoor licentie geldt
Verbruiksproduct
Naam
Definitie
Verplicht
Soort
papier, toner voor HPLaser6 ...
Merk
naam producent
Type
type naam zoals gekend door producent
Beschrijving
voornaamste karakteristieken
Voorraad
hoeveelheid stuks in voorraad
Ja
Bestelpunt
minimumvoorraad
Ja
Leveringstermijn
gemiddelde leveringstermijn in dagen
In bestelling
Ja/nee
Ja
Leverancier
Naam
Definitie
Firmanaam
naam van de firma
Contactpersoon
naam van de contactpersoon bij de firma
Klantnummer
klantnummer zoals gekend bij leverancier
Telnr
Telefoonnummer
Faxnr
Faxnummer
E-mailadres
e-mailadres van contactpersoon
Adres
adres van de firma
Verplicht
Ja
De attributen die bij de relaties Levertx en Draait op horen worden in de volgende tabellen weergegeven. De relaties Is verbonden met en Connecteert hebben geen attributen.
Pagina 20
Database-ontwerp
Levert
Naam
Definitie
Verplicht
Leveringsdatum
datum waarop goederen geleverd werden
Ja
Aankoopprijs
prijs per stuk
Ja
Opmerking
opmerking i.v.m. de levering
Draait op
Naam
Definitie
begindatum
datum installatie
einddatum
datum verwijdering
opmerking
opmerking, bijvoorbeeld Service Release die geïnstalleerd is,
optionele componenten
2.8
Verplicht
Oefeningen
Ontwerp voor de beschreven omgevingen een datamodel. Volg bij het ontwerpen de volgende stappen:
-
bepaal de entiteiten;
-
bepaal de relaties;
-
bepaal de cardinaliteit van de relaties;
-
bepaal de optionaliteit van de relaties;
-
bepaal de attributen van de entiteiten en de relaties.
1
Ontwerp een datamodel voor een puntenboek van een leraar. Je moet zowel de punten voor
gewone toetsen als voor proefwerken opslaan. Op het einde van de maand moet er op basis
van de databank een puntenlijst afgedrukt worden met de punten van dagelijks werk voor het
maandrapport en op het einde van het trimester de punten voor dagelijks werk en proefwerk.
De leraar kan meerdere vakken geven en kan in meerdere klassen komen.
2
Ontwerp een datamodel voor een databank voor de schoolbibliotheek. Voor elk boek wordt
bijgehouden welk type het is (boek, tijdschrift, naslagwerk). Elk werk heeft uiteraard een naam
(titel) en is voorzien van een etiket met een uniek nummer. Een gebruiker moet een werk
kunnen opzoeken op basis van een trefwoord. Een werk kan aan meerdere trefwoorden voldoen. Boeken kunnen uitgeleend worden. Je moet kunnen zien of het werk nog aanwezig is
of uitgeleend. Indien het uitgeleend is, heeft de persoon het recht om het werk te reserveren.
Je moet natuurlijk weten aan wie het is uitgeleend omdat de leners het boek maar één maand
mogen bijhouden. Op elk moment moet een lijst kunnen afgedrukt worden van de ontleende
en gereserveerde boeken.
3
Een sportclub (atletiek) wenst van al zijn leden een aantal gegevens bij te houden zoals hun
lidnummer, naam, adres, postnummer, gemeente, telefoonnummer en welke beste tijden ze
behaalden op de loopnummers 100 m, 200 m en 400 m en de datum waarop die beste tijden
gelopen werden.
4
Een kapper wil voor elk bezoek van zijn klanten bijhouden welke bewerkingen hij heeft uitgevoerd (zoals knippen, wassen & gebruikte producten, kleuren & gebruikte producten) zodat hij
later een historiek kan samenstellen per klant.
5
Een huisarts wil een databank met volgende gegevens: patiënten, gezinsgegevens (gezinshoofd, gezinsleden …), adres, telefoon, mutualiteit, bezoeken per gezinslid, diagnose per bezoek, voorgeschreven geneesmiddelen.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 21
6
De firma La Linea werkt met vertegenwoordigers die kleding verkopen in een bepaald verkoopgebied. Een klant bestelt altijd via dezelfde vertegenwoordiger. De databank moet zeker
volgende zaken bevatten:
-
vertegenwoordigers: vertegenwoordigersnummer, naam en adresgegevens, totale omzet
(verdienste) en commissiepercentage;
-
klanten: klantnummer, naam, adres, saldo, kredietlimiet. Elke klant heeft een vertegenwoordiger maar nooit meer dan één;
-
kledingsstukken: artikelnummer, omschrijving, prijs, aantal in voorraad;
-
bestellingen: bestelnummer, klantgegevens, vertegenwoordiger, bestelde artikelen met
aantal en prijs.
Bijkomende vereisten
-
een bestelling wordt alleen aanvaard indien de klant geregistreerd is;
-
per bestelling is er hoogstens één klant;
-
klanten kunnen een korting krijgen: de te betalen prijs kan dus lager zijn dan de prijs in het
artikelenbestand;
-
indien de prijs van een artikel verhoogt, betaalt de klant nooit meer dan afgesproken bij de
bestelling.
Pagina 22
Database-ontwerp
3
Een relationele databank
•
•
•
3.1
Het relationeel model
Primaire en refererende sleutels
Het normalisatieproces
Tabellen
Gegevens kunnen gegroepeerd worden omdat ze om de een of andere reden bij elkaar horen (cfr.
entiteiten in het entiteit-relatie datamodel). Zo een groep noem je een objecttype. In een relationele
databank worden objecttypes geïmplementeerd d.m.v. tabellen met twee dimensies: rijen en kolommen. Per object wordt één rij gebruikt en per gegeven (attribuut) één kolom.
Deze tabellen worden relaties (relations) genoemd. Dit is een term uit de wiskunde, niet te verwarren met de relaties (relationships) in het datamodel. Een database zal meestal uit meerdere tabellen
bestaan die onderling met elkaar in verband staan.
Voorbeeld: patientgegevens
De attributen van een patiënt-objecttype zijn voornaam, familienaam, geboortedatum, lengte en
gewicht. Ze vormen de kolommen of velden van de tabel.
De (gegevens van) verschillende patiënten vormen de verschillende rijen of records in de tabel.
Een tabel in een relationele database moet aan bepaalde voorwaarden voldoen:
•
•
•
twee rijen mogen nooit helemaal hetzelfde zijn (geen duplicaten);
de volgorde van de rijen en kolommen mogen geen intrinsieke betekenis hebben, d.w.z. het
verplaatsen van rijen en/of kolommen heeft geen invloed op de betekenis van de bewaarde gegevens;
elke cel dient atomaire gegevens te bevatten, d.w.z. er mag maar een waarde van het attribuut
in voorkomen. In een cel waarin een lengte aangegeven wordt, mag maar 1 lengte staan en niet
bijvoorbeeld de resultaten van opeenvolgende metingen.
3.2
Sleutels
3.2.1
Primaire sleutel
Elke rij is uniek en kan bijgevolg geïdentificeerd worden door de waarde in een kolom of een combinatie van kolommen. Deze kolom of combinatie van kolommen noemt men primaire sleutel (PS).
Als de primaire sleutel uit meerdere kolommen bestaat, spreekt men van een samengestelde sleutel. M.a.w. 2 verschillende rijen in een tabel mogen nooit in de primaire sleutel dezelfde waarde
hebben en in elke rij moet de primaire sleutel steeds 1 waarde hebben. Primaire sleutels mogen
bijgevolg nooit de waarde Null hebben, wat betekent dat de primaire sleutel altijd moet ingevuld
worden, nooit leeg of onbekend mag zijn!
De primaire sleutel van een tabel speelt een belangrijke rol in een relationele database.
Besteed dus steeds voldoende zorg aan de keuze ervan!
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 23
3.2.2
Kandidaatsleutel
Het kan voorkomen dat er meerdere kolommen of combinaties van kolommen de rij identificeren. Er
zijn dan meerdere kandidaatsleutels. Eén ervan wordt gekozen tot primaire sleutel (primary key).
In de tabel tblPatientgegevens is er eigenlijk geen kandidaatsleutel. Theoretisch, hoewel zeer onwaarschijnlijk, is het mogelijk dat er twee personen met dezelfde naam zijn, die op dezelfde dag
geboren zijn en dan nog dezelfde lengte en hetzelfde gewicht hebben. In dergelijke gevallen voeg je
een veld toe, meestal met een numerieke waarde, dat als primaire sleutel fungeert.
3.2.3
Alternatieve sleutel
Kandidaatsleutels die geen primaire sleutel zijn geworden, worden alternatieve sleutels genoemd.
De waarde van een alternatieve sleutel mag wel Null zijn. Bijvoorbeeld telefoonnummer in een tabel
tblKlanten. Het begrip kandidaatsleutel is dus een verzamelnaalm voor alle primaire en alternatieve
sleutels.
3.2.4
Surrogaatsleutel
In de praktijk kies je best een extra kolom als primaire sleutel zoals PatientID, d.w.z. een kolom die
niet overeenkomt met een eigenschap van een entiteit, maar die enkel dient om de rij te identificeren. Als je een kolom neemt die overeenkomt met een eigenschap van een attribuut, dan kan dit in
de loop van de tijd veranderen.
Stel dat je als primaire sleutel een telefoonnummer zou nemen. In theorie biedt dit een unieke identificatie van bijvoorbeeld een klant. Als echter de telefoonmaatschappij om een of andere reden de
nummers aanpast, bijvoorbeeld zonenummer 089 wordt gesplitst in 089 en 011 zal de applicatie
overal waar deze waarde als primaire sleutel en refererende sleutel (zie volgende paragraaf) gebruikt wordt, moeten aangepast worden. Dat vraagt heel wat meer werk dan enkel de gegevens in
één kolom aanpassen.
Een sleutel zoals PatientID die niet overeenkomt met een eigenschap van een entiteit noemt men
een surrogaatsleutel.
3.2.5
Refererende sleutel
Een refererende of vreemde sleutel is een geheel van attibuuttypen van een relatie waarvoor geldt:
de attribuuttypen verwijzen naar de primaire sleutel uit een andere tabel. Refererende sleutels dienen juist om het verband tussen twee relaties (tabellen) weer te geven.
Bij onvoldoende kennis van de werking met sql-databanken: zie hoofdstuk 5.
Beschouw volgende view in ArtemisSQL.mdf:
Pagina 24
Database-ontwerp
In de tabel tblOrders is Klantnummer de refererende sleutel. Uiteindelijk zullen tabellen met
elkaar in verband gebracht worden via het toevoegen van vreemde sleutels.
Bekijk de inhoud van tabel tblKlanten. Wat is het laatste klantnummer? Voeg een record toe in
tabel tblOrders met als klantnummer 275. Er volgt:
3.3
Namen van velden
Ieder veld behoort een duidelijke, binnen de tabel unieke naam te krijgen, waarmee het zich van alle
andere gegevens onderscheidt. Hoewel het in de meeste pakketten voor gegevensbeheer, zoals
met sql-bestanden mogelijk is om een naam te kiezen die bestaat uit meerdere woorden, kun je
misschien beter de woorden aan elkaar schrijven en ieder nieuw woord met een hoofdletter laten
beginnen.
Overdrijf niet in de lengte van de naam: 25 karakters zou ruim voldoende moeten zijn.
Voorbeelden van zinvolle naamgeving: WaardeVoorraad, BetalingPerCheck enz.
Een tabel kan bijvoorbeeld weergeven worden als:
Patiëntgegevens(PatiëntID, Voornaam, Familienaam, Geboortedatum, Lengte, Gewicht)
waarbij de primaire sleutel onderstreept wordt.
3.4
Koppelingen tussen tabellen
Verschillende tabellen in een relationele database kunnen gegevens bevatten die onderling gekoppeld zijn, die dus samen een grotere gegevensgroep vormen. Bijvoorbeeld: een tabel bevat gegevens van ouders en een andere tabel bevat gegevens van kinderen. Er moet een mechanisme bestaan om aan te geven welke ouders bij welke kinderen horen.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 25
Een dergelijke koppeling wordt in het Nederlands ook een relatie genoemd (Engels: relationship). Dit
kan tot verwarring leiden. In hetgeen volgt wordt de term relatie gebruikt om een koppeling tussen
tabellen aan te geven.
In een relationeel ontwerp wordt een relatie aangegeven door een lijn tussen de twee tabellen die de
koppelende velden verbindt. Het veld in de ‘kind’-tabel dat de koppeling legt met de (primaire sleutel) van ‘ouder’-tabel is de refererende sleutel of vreemde sleutel (foreign key).
3.5
Ouder
Kind
OuderID
Familienaam
Voornaam
Straat
postnummer
Gemeente
KindID
Voornaam
Leeftijd
Geslacht
OuderIDREF
Het normalisatieproces
Alle tabellen zijn niet zo maar te gebruiken in een relationele database. In paragraaf 3.1 werden
reeds 3 voorwaarden opgesomd waaraan voldaan moet zijn, maar er zijn er nog. De bijkomende
voorwaarden hebben hoofdzakelijk te maken met het consistent houden van de database, d.w.z.
de database mag geen tegenstrijdige gegevens bevatten. Zulke tegenstrijdige gegevens kunnen
ontstaan als eenzelfde gegeven, bijvoorbeeld het adres van een persoon, meerdere keren bijgehouden wordt. Het adres kan dan bijvoorbeeld op een plaats aangepast worden, maar dezelfde aanpassing wordt vergeten op de andere plaats.
Gelukkig bestaan er regels waarmee je een tabel kunt aanpassen om tot een goede structuur te
komen. Dit proces wordt normalisatie genoemd.
Normalisatie is een bepaalde herschikking van de gegevens en gegevensgroepen, waarbij soms
nieuwe groepen worden gevormd door gegevens uit bestaande groepen af te zonderen. Uiteindelijk
wil je tot de volgende situatie komen:
Alle attributen van een groep zijn functioneel afhankelijk van de volledige primaire sleutel
en tussen attributen onderling bestaan geen functionele afhankelijkheden.
Een gegeven is functioneel afhankelijk van een ander als dit gegeven gekend is als het andere
gegeven gekend is. Bijvoorbeeld in het rijksregister zijn de gegevens naam en woonplaats functioneel afhankelijk van het rijksregisternummer. Als je het rijksregisternummer van iemand kent, dan
kun je zijn naam en woonplaats vinden. Door het gebruik in volgende paragrafen wordt het begrip
wellicht duidelijker.
Het doel van normalisatie is het vermijden van redundantie dit betekent dat bepaalde informatie
tegelijkertijd op meer dan één plaats wordt voorgesteld. De voornaamste nadelen van redundantie
zijn:
-
risico's van inconsistentie: indien dezelfde soort informatie op meer dan één plaats tegelijk
aanwezig is, dan kunnen de verschillende plaatsen van informatieopslag elkaar tegenspreken;
-
moeilijker gegevensonderhoud: sommige wijzigingen moeten op verschillende plaatsen tegelijk worden doorgevoerd.
Een basisidee van de relationele structuur is dat gegevens worden opgesplitst
in verschillende tabellen die met elkaar verbonden zijn.
Op die manier wordt redundantie en inconsistentie vermeden.
Nochtans is redundantie soms nuttig. Zo kan een overdreven genormaliseerd gegevensbeheersysteem veel tijd vragen om gegevens op te zoeken, door het grote aantal verschillende gegevensgroepen.
Pagina 26
Database-ontwerp
Je gaat hoe dan ook de normalisatie steeds volledig doorvoeren en achteraf bepaalde redundanties
eventueel opnieuw invoeren, op voorwaarde dat je daarvoor een goede reden kunnen opgeven.
3.6
Normalisatievormen
Een normaalvorm (NV of NF – Normal Form) is een stap in het normalisatieproces. Elke normaalvorm stelt bepaalde eisen aan de manier waarop de gegevens zijn opgeslagen (zoals eisen aan de
geldende functionele afhankelijkheden).
Er zijn 6 normaalvormen. Hier worden er maar 3 besproken omdat ze meestal volstaan om tot een
efficiënte database te komen:
-
NF1: verwijder de zich herhalende deelverzamelingen (repeterende groepen).
-
NF2: verwijder de attributen die functioneel afhankelijk zijn van slechts een deel van de sleutel.
-
NF3: verwijder de attributen die functioneel afhankelijk zijn van andere attributen.
Met 'verwijderen' wordt bedoeld in een afzonderlijke gegevensgroep onderbrengen, niet weglaten uit
het gegevensmodel!
3.7
Niet genormaliseerde gegevensgroepen
Onderstaande tabel tblBestellingen bevat gegevens over meubelen en de leverancier ervan. Het is
eigenlijk niets anders dan een losse verzameling van gegevens. Een dergelijke gegevensgroep is
een gegevensgroep in de nulde normaalvorm (0NF). Het is de ‘naieve’ benadering om gegevens
te structureren: de structuur van de database bootst de verschijningsvorm van de gegevens in de
werkelijkheid na. Bij het ontwerp werd enkel gekeken naar de bestelling. De tabel wordt aanzien als
een lijst van bestellingen.
Naam
Kleur
TAFEL
Wit
Prijs
445,00
Aantal
2
Naam
Tel
Straat
Postc
Plaats
IDEA
03 156 84 95
Boomsesteenweg 185
2610
WILRIJK
03 156 84 96
STOEL
Wit
198,00
8
FIEREMANS
054 20 60 80
Assesteenweg
1740
TERNAT
TAFEL
Zwart
495,00
6
JANSSENS
02 256 15 23
Zeypstraat 15
2800
MECHELEN
TAFEL
Zwart
495,00
6
JANSSENS
02 256 15 23
Zeypstraat 15
2800
MECHELEN
STOEL
Bruin
124,00
10
JANSENS
02 256 15 32
Zeipstraat 15
2000
MECHELEN
KAST
Zwart
544,00
5
FIEREMANS
02 132 54 98
Assesteenweg 121
1740
TERNAT
054 20 60 80
Ninoofsesteenweg 156
Wit
ROOSDAAL
Bruin
BED
KAST
Grijs
397,00
9
LINEA
03 189 78 91
Boomsesteenweg 185
2610
WILRIJK
Wit
422,00
1
BRICO
02 563 89 78
Hoogstraat 210
1000
BRUSSEL
Bruin
745,00
4
JANSSENS
02 256 15 23
Zeipstraat 15
2800
MECHELEN
Bekijk de adresgegevens van leverancier Janssens. Wat stel je vast?
Twee rijen zijn volledig identiek. Wat denk je hierover?
Sommige artikelen bestaan in meerdere kleuren. Welke? Mag dit in een tabel van een relationele database zo bewaard worden? Verklaar.
Wordt er op andere plaatsen nog gezondigd tegen dezelfde regel? Waar?
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 27
Het is duidelijk dat deze gegevensgroep niet voldoet om als tabel in een relationele omgeving gebruikt te worden.
Herlees nog eens de voorwaarden waaraan een tabel in een relationele databank moet voldoen
(paragraaf 3.1).
Aan welke voorwaarden wordt in deze gegevensgroep niet voldaan?
Bij een relationele tabel hoort ook een primaire sleutel.
Wat zou je in deze tabel als primaire sleutel kunnen gebruiken?
3.8
Repeterende groepen: NF1
NF1: iedere tabel die voldoet aan de minimale eisen die aan een relatie (tabel) worden gesteld
is automatisch in NF1.
Praktisch komt het er op neer dat er een primaire sleutel moet bestaan en dat alle cellen atomair
zijn. Een RDBMS zoals MS Access, SQL Server, MySQL laat enkel atomaire cellen toe. Wel is het
in veel RDBMS-sen mogelijk om zonder een primaire sleutel te werken.
In volgende voorbeelden wordt uitgelegd welke problemen kunnen opduiken als een tabel niet in
NF1 is en hoe je een gegeven tabel in NF1 kunt zetten.
Voorbeeld 1
In een bedrijf worden de volgende personeelsgegevens bijgehouden:
Persnr
Naam
Straat
Postnummer
Gemeente
Taal
10051
Devos
Dorpsplein 1
8000
Brugge
Nederlands
Frans
Engels
20089
Vandevelde
Centrumlaan 5
9000
Gent
Nederlands
Duits
…
…
…
…
…
…
Het veld Taal bevat de talen gekend door de personeelsleden.
Wat is de primaire sleutel van deze gegevensgroep?
Waarom kan deze gegevensgroep niet als tabel gebruikt worden in een relationele database?
De talenkennis van de personeelsleden moet op een andere manier bewaard worden.
Welke problemen kun je verwachten als je diverse velden voorziet (Taal1, Taal2, Taal3, ...)
om dan in elk veld een gekende taal te noteren?
Welke problemen treden op als je per personeelslid zoveel rijen voorziet als er gekende talen
zijn?
Een gegeven of een combinatie van gegevens die per entiteit meerdere keren voorkomt noemt men
een repeterende groep.
Pagina 28
Database-ontwerp
De oplossing van de aangehaalde problemen vereist het verwijderen van de repeterende groep (hier
het gegeven Taal) uit de oorspronkelijke gegevensgroep en het creëren van een nieuwe gegevensgroep met de gegevens van de repeterende groep. Er wordt een scheiding gemaakt van gegevens
WAARBIJ MEN ER MOET VOOR ZORGEN OM GEEN INFORMATIE TE VERLIEZEN!
Een volgende opsplitsing zou dan ook verkeerd zijn:
Persnr
Naam
Straat
Postnummer
Gemeente
10051
Devos
Dorpsplein 1
8000
Brugge
20089
Vandevelde
Centrumlaan 5
9000
Gent
…
…
…
…
…
en
Taal
Nederlands
Frans
Engels
Nederlands
Duits
…
Waarom is dit geen goed voorstel en volgend voorstel wel?
Persnr
Naam
Straat
Postnummer
Gemeente
10051
Devos
Dorpsplein 1
8000
Brugge
20089
Vandevelde
Centrumlaan 5
9000
Gent
…
…
…
…
…
en
Persnr
Taal
10051
Nederlands
10051
Frans
10051
Engels
20089
Nederlands
20089
Duits
…
…
Om geen gegevensverlies te hebben moet je dus de repeterende groep uit de oorspronkelijke gegevensgroep verwijderen en in een aparte gegevensgroep zetten, waarbij je de primaire sleutel herhaalt.
Wat is een primaire sleutel voor de tweede gegevensgroep?
Bovenstaande gegevensgroepen staan in de eerste normaalvorm (1NF).
Het is mogelijk dat er meerdere repeterende groepen voorkomen. Die moeten allemaal in aparte
gegevensgroepen bijgehouden worden.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 29
De zo ontstane gegevensgroepen zijn gerelateerd. De relatie wordt gelegd door het primaire sleutelveld van de eerste gegevensgroep die herhaald wordt in de tweede gegevensgroep. In de tweede
gegevensgroep kan dat sleutelveld meerdere keren dezelfde waarde bevatten. Het kan dus zeker
geen primair sleutelveld voor de tweede gegevensgroep vormen. Men spreekt van een refererende
sleutel of foreign key. In het bovenstaande voorbeeld is Persnr de primaire sleutel van de eerste
gegevensgroep en de refererende sleutel van de tweede gegevensgroep.
Voorbeeld 2
Bij het normaliseren hoef je niet onmiddellijk de gegevens in tabelvorm weer te geven. Meestal is dit
zelfs niet mogelijk omdat er te veel gegevens zijn. Je kunt de gegevens ook onder elkaar weergeven
zoals in volgend voorbeeld. (De primaire sleutel wordt onderstreept.)
9
nulde normaalvorm
personeelsnummer
naam
adresgegevens (straat, postnummer, gemeente)
talenkennis (taal, mondelinge kennis, schriftelijke kennis)
behaalde diploma's (naam, jaartal)
9
eerste normaalvorm
personeelsnummer
naam
adresgegevens (straat, postnummer, gemeente)
en
personeelsnummer
taal
mondelinge kennis
schriftelijke kennis
en
personeelsnummer
diplomanaam
jaartal
Personeelsnummer is de primaire sleutel van de eerste gegevensgroep en de refererende sleutel
van de twee andere.
Voorbeeld 3
9
nulde normaalvorm
lidnummer
naam
adresgegevens (straat, postnummer, gemeente)
kinderen (naam, leeftijd, gevolgde lessen)
9
eerste normaalvorm
Pagina 30
Database-ontwerp
Stap 1
lidnummer
naam
adresgegevens (straat, postnummer, gemeente)
en
lidnummer
naamkind
leeftijd
gevolgde lessen
Stap 2
lidnummer
naam
adresgegevens (straat, postnummer, gemeente)
en
lidnummer
naamkind
leeftijd
en
lidnummer
naamkind
gevolgde les
Voor de eenvoud kun je aan de tweede gegevensgroep een speciaal sleutelveld toevoegen dat kan
dienen als primaire sleutel voor de gegevensgroep. Je krijgt dan iets als het volgende.
9
eerste normaalvorm
lidnummer
naam
adresgegevens (straat, postnummer, gemeente)
en
kindnummer
lidnummer
naamkind
leeftijd
en
kindnummer
gevolgde les
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 31
Voorbeeld 4
Hieronder zie je een tabel met namen van personen en de softwarepakketten die ze geven:
Persnum
P10
P11
P13
P16
P19
Naam
Marc
Ria
Annie
Guy
Gerry
Software
Powershell, Asp.Net
Excel, Word, Access
Word, Access
Asp.Net, Word, Vb.Net
Vb.Net, Asp.Net, Powershell
Strikt genomen is deze tabel in de eerste normaalvorm, maar er wordt gezondigd tegen de geest
van deze normaalvorm omdat er meerdere betekenisvolle gegevens in een cel staan. Het is moeilijk
om uit een dergelijke tabel alle personeelsleden te filteren die Word geven.
De repeterende groep wordt gevormd door de gebruikte Software. Een omzetting naar de eerste
normaalvorm geeft het volgende resultaat:
Persnum
P10
P11
P13
P16
P19
Naam
Marc
Ria
Annie
Guy
Gerry
en
Persnum
P10
P10
P11
P11
P11
P13
P13
…
Software
Powershell
Asp.Net
Excel
Word
Access
Word
Access
…
Misschien heb je het probleem van het softwaregebruik als volgt opgelost.
Persnum
P10
P11
P13
P16
P19
Naam
Marc
Ria
Annie
Guy
Gerry
Powershell Asp.Net
x
x
x
x
x
Word
Access
x
x
x
x
x
Vb.Net
Excel
x
x
x
Schijnbaar is er hier geen sprake van herhalende groepen. In elke cel staat immers hoogstens één
vermelding nl. een x als de persoon het betreffende programma gebruikt.
Dergelijke structuur heeft enkele belangrijke nadelen.
-
Veel velden zijn leeg maar nemen toch plaats in op de schijf.
-
Wanneer een pakket niet meer gebruikt wordt, is het bij alle records leeg maar het is nog aanwezig. Je kunt het slechts verwijderen wanneer je de structuur van de tabel wijzigt. Dit is een activiteit die de gewone gebruiker niet zal uitvoeren. Wanneer er een nieuw pakket wordt toegevoegd moet je eveneens de tabelstructuur aanpassen. Wanneer je echter met een gesplitste tabel werkt, dan heb je slechts records te verwijderen of toe te voegen. De tabelstructuur verandert hierdoor niet, wel het aantal rijen in de tabel.
Pagina 32
Database-ontwerp
3.9
Partiële functionele afhankelijkheid: NF2
Een relatie is in 2 NF als alle attributen die niet opgenomen zijn in de PS
van de gehele PS afhankelijk zijn.
Praktisch kan dit alleen problemen opleveren als de primaire sleutel een samengestelde sleutel is.
Voorbeeld 1
In een onderneming waar software op maat van de klant ontwikkeld wordt, wordt elke opdracht gezien als een project dat geïdentificeerd wordt door een projectnummer en naam. Analisten, programmeurs en andere medewerkers worden toegewezen aan één of meerdere projecten. Om de kosten
te kunnen bepalen, wordt per medewerker bijgehouden hoelang hij aan een project gewerkt heeft.
9
nulde normaalvorm
personeelsnummer
adresgegevens (straat, postnummer, gemeente)
uurloon
projecten (projectnummer, projectnaam, klantnaam, werkduur)
9
eerste normaalvorm
personeelsnummer
adresgegevens (straat, postnummer, gemeente)
uurloon
en
personeelsnummer
projectnummer
projectnaam
klantnaam
werkduur
Kenmerkend voor een primaire sleutel is dat hij een rij volledig identificeert. Dit wil zeggen: als de
primaire sleutel gekend is, dan kun je de waarden van alle andere velden vinden. Men zegt dat alle
velden functioneel afhankelijk (f.a.) zijn van de primaire sleutel (veld X is functioneel afhankelijk
van veld Y als de waarde van veld X vastligt als de waarde van veld Y gegeven is).
Voorbeeld
Als we de klantID kennen dan kennen we tevens de klantnaam, klantadres, … Bij een klantID hoort
immers ondubbelzinnig een klantnaam, een klantadres, … De klantnaam is dus functioneel afhankelijk van de klantID. Het omgekeerde geldt niet: het klantID is niet functioneel afhankelijk van de
klantnaam. Bij een klantnaam kunnen er meerdere klantID’s horen. De klantnaam Piet Pieters of Jan
Janssens kan meerdere keren voorkomen in de tabel zodat Piet Pieters niet ondubbelzinnig kan
zeggen welk klantID er mee overeenkomt.
Als de primaire sleutel een samengestelde sleutel is, m.a.w. uit meerdere velden bestaat, dan is het
mogelijk dat kennis van een deel van de sleutel voldoende is om de waarde van een of meer velden
te vinden. In het bovenstaande voorbeeld, tweede tabel, is dit het geval voor de velden projectnaam
en klantnaam. Als het projectnummer gekend is, dan kan men de waarde van deze beide velden
vinden. Men zegt dat deze velden partieel functioneel afhankelijk zijn van de primaire sleutel (of
afhankelijk zijn van een deel van de sleutel).
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 33
In de tweede stap in de normalisering moeten velden die partieel functioneel afhankelijk zijn uit de
oorspronkelijke gegevensgroep verwijderd worden en samen met het deel van de sleutel waarvan
ze afhankelijk zijn in een nieuwe gegevensgroep geplaatst worden.
9
tweede normaalvorm
personeelsnummer
adresgegevens (straat, postnummer, gemeente)
uurloon
en
personeelsnummer
projectnummer
werkduur
en
projectnummer
projectnaam
klantnaam
Als deze stap niet gezet wordt, zal de database redundante gegevens bevatten, d.w.z. dezelfde
gegevens komen meerdere malen voor (zie voorbeeld 2). Inderdaad zal in bovenstaand voorbeeld,
voor elk personeelslid dat aan een bepaald project toegewezen wordt zowel de projectnaam als de
klantnaam herhaald worden.
Merk op dat door het afzonderen van de projectgegevens de primaire sleutel van de nieuwe gegevensgroep (projectnummer) aanwezig blijft als refererende sleutel in de oorspronkelijke gegevensgroep.
De gegevens van de medewerkers zitten samen in de gegevensgroep. De gegevens van het project
zitten samen in een andere gegevensgroep. De koppeling tussen deze gegevensgroepen namelijk
welke werknemers werken hoelang voor welk project, zit in een aparte gegevensgroep met een
samengestelde sleutel.
Voorbeeld 2
Een groothandelaar koopt bepaalde artikelen bij meerdere leveranciers.
9
nulde normaalvorm
artikelnummer
artikelnaam
artikelcategorie
leveranciersnummer
aantal
levtel
9
eerste normaalvorm
artikelnummer
artikelnaam
artikelcategorie
Pagina 34
Database-ontwerp
en
artikelnummer
leveranciersnummer
aantal
levtel
Als de stap naar de tweede normaalvorm (2NF) niet gedaan wordt, kan de tweede gegevensgroep
er als volgt uitzien.
Levnum
L1
L1
L1
L2
L2
L3
L3
L4
LevTel
235
235
235
999
999
800
800
600
Artnum
A1
A2
A3
A2
A4
A1
A5
A6
Aantal
10
12
14
10
5
13
10
5
Welke gegevens worden nodeloos herhaald?
Hoeveel keer moet je het telefoonnummer veranderen als het telefoonnummer van L1 verandert?
Wat gebeurt er indien artikel A6 gewist wordt (niet aangekocht)?
Kun je het telefoonnummer van een leverancier toevoegen als hij nog geen artikel geleverd
heeft?
Welke velden zijn functioneel afhankelijk van slechts een deel van de sleutel?
Breng bovenstaande gegevensgroep in tweede normaalvorm.
3.10
Transitieve functionele afhankelijkheid: NF3
Een relatie is in 3 NF als de relatie in 2 NF is en geen transitieve afhankelijkheden bevat
Wat dit precies betekent, kom je te weten in de volgende voorbeelden.
Voorbeeld
In een boekenwinkel houdt men van alle boeken volgende gegevens bij:
Boekcode
Categoriecode
CategorieOms
Titel
BK1276
K36
Kinderen 3-6 jaar
Samson op reis
VW0058
K03
Kinderen 0-3 jaar
Dieren kijken
HJ1000
K36
Kinderen 3-6 jaar
Samson is ziek
…
…
…
…
Auteur
Verkprijs
14,50
2,45
14,50
…
…
Wat vind je zelf (intuïtief) van dit voorstel voor de gegevensbestandstructuur? Leg uit met
voorbeeld(en) waarom je dit voorstel goed of slecht vindt.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 35
In dit voorbeeld is de categorieomschrijving (CategorieOms) gekend als de categoriecode gekend is.
Dit veld is dus functioneel afhankelijk van een ander veld dan het primaire sleutelveld. Men spreekt
in dat geval van transitieve functionele afhankelijkheid.
In de derde stap van de normalisering moeten velden die transitief functioneel afhankelijk zijn uit de
oorspronkelijke gegevensgroep verwijderd worden en samen met het veld waarvan ze afhangen in
een nieuwe gegevensgroep geplaatst worden.
Boekcode
CategorieCode
Titel
BK1276
K36
Samson op reis
VW0058
K03
Dieren kijken
HJ1000
K36
Samson is ziek
…
…
…
Auteur
Verkprijs
14,50
2,45
14,50
…
…
en
CategorieCode
CategorieOms
K36
Kinderen 3-6 jaar
K03
Kinderen 0-3 jaar
…
…
Merk op: CategorieCode is de primaire sleutel van de nieuwe gegevensgroep en de refererende
sleutel in de oorspronkelijke groep.
De werkwijze is dus analoog als bij de tweede normalisatiestap.
Pagina 36
Database-ontwerp
3.11
Overzicht
Om tot een goede structuur van je relationele database te komen maak je volgende stappen.
9
Inventariseer alle elementaire gegevens.
9
Duid de sleutel(s) aan.
9
Verwijder alle procesgegevens.
9
Zet de gegevensgroepen in eerste normaalvorm.
Werkwijze:
- geef de deelverzameling aan die een herhaald aantal keren voorkomt.
- herhaal de sleutel van de oorspronkelijke groep samen met de gegevens van de zich herhalende deelverzameling als nieuwe groep.
- verwijder de zich herhalende deelverzameling uit de oorspronkelijke groep.
9
Zet de gegevensgroepen in tweede normaalvorm.
Geef de attributen aan die niet functioneel afhankelijk zijn van de volledige sleutel.
Vorm een aparte groep voor ieder deel van de sleutel waarvan attributen functioneel afhankelijk
zijn.
Neem in iedere groep de attributen met het bijbehorende sleuteldeel op.
Verwijder deze attributen uit de oorspronkelijke groep.
9
Zet de gegevensgroepen in derde normaalvorm.
Geef de attributen aan die ook functioneel afhankelijk zijn van andere attributen.
Vorm een aparte groep voor ieder attribuut of combinatie van attributen, waar andere attributen
functioneel afhankelijk van zijn.
Neem in iedere groep de attributen met bijbehorende sleutel op.
Verwijder de attributen van de nieuwe groep(en) uit de oorspronkelijke groep.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 37
3.12
Voorbeelden
3.12.1
Oefening 1: facturatie
In het kader van de automatisering van het bedrijf Elza moet de administratie van volgende facturen
geautomatiseerd worden:
N.V. Elza
Factuurnummer: 51
Veldstraat 63
Klant: 29
9000 Gent
Firma Verstraete
Vredelaan 45
8520 Kurrne
Geleverd:
Datum: 25/01/2008
Artikelnummer
Omschrijving
Aantal
Prijs
A0456
C1200
C0002
Video S148
Cd-speler P4
Cd-speler V3
1
3
1
399,00
249,50
259,35
Totaal: 1406,85
1. Inventariseer de gegevens.
2. Duid de sleutel aan: deze wordt onderstreept.
3. Verwijder de procesgegevens.
FACTUREN
Factuurnummer
Klnummer
Klnaam
Kladres
Klpostnr
Klgemeente
Factuurdatum
Artikelnummer
Omschrijving
Aantal
herhalende deelverzameling
Prijs
Totaal -> procesgegeven verwijderen
1° normaalvorm: verwijder de zich herhalende deelverzamelingen
Op de factuur komen de gegevens Artikelnummer, Omschrijving, Aantal en Prijs meerdere keren
voor. Per factuur is het aantal gekochte artikelen variabel. Probleem: voor hoeveel artikelen moet er
plaats (kolommen) voorzien zijn in de tabel? Wat gebeurt er als er teveel of te weinig plaats voorzien
wordt?
Pagina 38
Database-ontwerp
Deze vier gegevens vormen een zich herhalende deelverzameling. Deze groep wordt uit de tabel
verwijderd en in een aparte tabel geplaatst: tblBestellingen.
Werkwijze:
1. Duid de zich herhalende deelverzameling aan.
2. Maak een nieuwe tabel met de sleutel uit de oorspronkelijke tabel en de zich herhalende deelverzameling.
3. Duid een bijkomende sleutel aan in de nieuwe tabel.
4. Verwijder de zich herhalende deelverzameling uit de oorspronkelijke tabel.
1e normaalvorm:
FACTUREN (tblFacturen)
BESTELLING (tblBestelling)
Factuurnummer
Klnummer
Klnaam
Kladres
Klpostnr
Klgemeente
Factuurdatum
Factuurnummer
Artikelnummer
Omschrijving
Aantal
Prijs
Merk op: als er in een zich herhalende deelverzameling een andere zich herhalende deelverzameling voorkomt, dan moet deze werkwijze nog eens herhaald worden.
2° normaalvorm: verwijder de attributen die slechts afhankelijk zijn van een deel van de sleutel
In de tabel tblBestelling zijn de gegevens Omschrijving en Prijs enkel afhankelijk van een deel van
de sleutel (Artikelnummer) en niet van de volledige sleutel. Daarom worden ze uit de tabel verwijderd en in een aparte tabel opgenomen.
Werkwijze:
1. Duid de attributen aan die slechts afhankelijk zijn van een deel van de sleutel.
2. Stel een aparte tabel op voor ieder deel van de sleutel waarvan attributen volledig afhankelijk
zijn.
3. Duid in de nieuwe tabellen een sleutel aan.
4. Verwijder de attributen van de nieuwe groep(en) uit de oorspronkelijke groep.
2e normaalvorm:
FACTUREN (tblFacturen)
BESTELLING (tblBestelling)
Factuurnummer
Klnummer
Klnaam
Kladres
Klpostnr
Klgemeente
Factuurdatum
Factuurnummer
Artikelnummer
Aantal
ARTIKELEN (tblArtikelen)
Artikelnummer
Omschrijving
Prijs
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 39
Er zijn nu al drie afzonderlijke tabellen. De tabel tblArtikelen kan nu ook bij andere facturen gebruikt
worden: het opnemen van het artikelnummer zal voldoende zijn om de bijkomende gegevens op te
vragen.
3° normaalvorm: verwijder de attributen die verborgen afhankelijk zijn van de sleutel
(die dus ook nog afhankelijk zijn van andere attributen)
In de tabel tblFacturen zijn de gegevens Klnaam, Kladres, Klpostnr en Klgemeente verborgen afhankelijk van de sleutel via het attribuut Klnummer: bij elk klantnummer hoort slechts één naam,
adres, gemeente en postnummer.
Werkwijze:
1. Geef de attributen aan die verborgen afhankelijk zijn van de sleutel.
2. Vorm een aparte groep voor ieder attribuut of combinatie attributen waar andere attributen afhankelijk van zijn.
3. Duid in deze nieuwe tabellen de sleutel aan.
4. Verwijder de attributen van de nieuwe groep(en) uit de oorspronkelijke groep.
3e normaalvorm:
FACTUREN (tblFacturen)
BESTELLING (tblBestelling)
Factuurnummer
Klnummer
Factuurdatum
Factuurnummer
Artikelnummer
Aantal
ARTIKELEN (tblArtikelen)
KLANTEN (tblKlanten)
Artikelnummer
Omschrijving
Prijs
Klnummer
Klnaam
Kladres
Klpostnr
Klgemeente
Verkorte schrijfwijze:
Naam van de tabel (sleutel, gegeven1, gegeven2, …)
In ons voorbeeld geeft dit:
tblFacturen(Factuurnummer, Klnummer, Factuurdatum)
tblBestelling(Factuurnummer, Artikelnummer, Aantal)
tblArtikelen(Artikelnummer, Omschrijving, Prijs)
tblKlanten(Klnummer, Klnaam, Kladres, Klpostnr, Klgemeente)
3.12.2
Oefening 2: artikelfiche
In een magazijn houdt men per artikel een fiche bij. Daarop staat naast het aantal in voorraad ook
informatie over de bestellingen die reeds geplaatst zijn. Dergelijke fiche ziet er uit zoals hieronder
afgebeeld. Ontwerp tabellen in de derde normaalvorm waarmee dergelijke fiches kunnen worden
bijgehouden.
Pagina 40
Database-ontwerp
Artikelnummer: 425 Strijkijzer
Hoeveelheid in magazijn: 15
Geplaatste bestellingen:
Ordernummer
Leverancier
Leveringsdatum
5891
6004
6147
Debo & zoon
Vanassche
Vanhee
25/02/09
01/03/09
15/03/097
Aantal
9
25
8
Bestelde hoeveelheid: 42
3.12.3
Oefening 3: abonnementen
“Lezen is gezond” is een uitgeverij van meerdere weekbladen. Als een abonnee niet tijdig betaalt,
stuurt de administratie een aanmaning volgens onderstaand model. Ontwerp tabellen in de derde
normaalvorm om deze administratie te automatiseren.
Uitgeverij “Lezen is gezond”, Antwerpen
Rekeningnummer: 001-2345678-03
Abonnementsnummer: 25897
Abonneenummer: 57645
De Heer/Mevrouw Pieters
Stationsstraat 99
9000 Gent
Geachte abonnee,
Er is gebleken dat u uw abonnementsgeld voor het blad “Dag iedereen” nog niet hebt betaald.
Wij vragen u beleefd het verschuldigde bedrag van € 34,99 zo snel mogelijk te betalen.
Gelieve bij uw betaling de nummers te vermelden die u bovenaan de brief vindt.
Hoogachtend,
3.12.4
Oefening 4: stages
Bij het organiseren van de stages op een school wordt op de administratie gebruik gemaakt van het
volgende overzicht. Ontwerp tabellen in de derde normaalvorm waarmee onderstaand overzicht kan
worden aangelegd.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 41
Overzicht van stagiaires per bedrijf
Bedrijfsgegevens
Nummer
Naam
Adres
Plaats
Contactpersoon
Geplaatste leerlingen
Nummer
88048
88005
88103
88074
3.12.5
Naam
Steven Devos
Katrien Adams
Ann Verhenne
Geert Naert
Klas
6a
6a
6c
6b
Periode
1
1
2
2
Oefening 5: projecten
In een softwarebureau zijn er steeds een groot aantal lopende projecten. Het is niet uitgesloten dat
eenzelfde medewerker tegelijkertijd aan verschillende projecten meewerkt.
In het bedrijf zijn er slechts 2 chefs: Jan is de chef van de afdeling Analyse en Piet is chef van de
afdeling Programmering.
Men wil steeds een goed overzicht van de zaken behouden. Ontwerp tabellen in de derde normaalvorm om deze administratie te automatiseren.
PROJECTOVERZICHT
Project
001 Vdb
Budget
1200
002 Tra
800
003 Ptz
1000
3.12.6
Medewerker
014 Paul
009 Petra
010 Tom
009 Petra
014 Paul
012 Ria
Afdeling
Analyse
Programmering
Analyse
Programmering
Analyse
Programmering
Chef
Jan
Piet
Jan
Piet
Jan
Piet
Uren
50
80
120
200
45
80
Oefening 6: cursussen
In een bepaald bedrijf worden voor de werknemers cursussen georganiseerd.
Van elke werknemer worden een aantal gegevens bijgehouden: een uniek nummer, de persoonlijke
gegevens, de afdeling waar hij werkt een zijn jaarsalaris. Het bedrijf heeft een achttal afdelingen met
elk een eigen baas. Deze afdelingen zijn gevestigd in verschillende plaatsen.
Bij de cursusadministratie moeten alle gegevens over de cursisten steeds beschikbaar zijn.
Van elke cursus wordt de officiële naam, de naam van de leraar, de startdatum en de duur van de
cursus bijgehouden.
Als een werknemer een cursus met succes beëindigd heeft, krijgt hij hiervoor een diploma. Op dat
diploma komen enkele persoonlijke gegevens van de werknemer, zijn afdeling en de datum waarop
hij de cursus heeft beëindigd.
Ontwerp tabellen in de derde normaalvorm om deze administratie te automatiseren.
Pagina 42
Database-ontwerp
3.12.7
Oefening 7: computerpark
Beschouw als gegevensgroep de attributen behorende bij de entiteit Computer in het voorbeeld
Computerpark.
Nulde normaalvorm
De definities worden weergegeven voor de duidelijkheid. ComputerID fungeert als primaire sleutel.
Naam
Definitie
ComputerID
identificatie van de computer, naam waaronder de PC gekend is, bijvoorbeeld
PC01, Joske ...
Serienummer
serienummer gegeven door producent
Merk
merknaam of 'wit product'
Type
naam van het type zoals gekend bij producent
Lokaal
locatie waar toestel zich bevindt, bijvoorbeeld: B11, Directiebureel ...
Functie
specifieke functie, bijvoorbeeld: lescomputer, labocomputer, server, internetserver
...
Processor
type met klokfrequentie
Harde schijf
merk, type, capaciteit … van alle ingebouwde schijven (samengesteld gegeven)
Diskettestation
merk, type… (samengesteld gegeven)
CD/DVD
merk en type van ingebouwd CD of DVD-station (samengesteld gegeven)
RAM
aanwezige hoeveelheid RAM
RAMtype
Type
RAMUitTeBreiden
uitbreidingsmogelijkheid
Beeldscherm
merk, type… (samengesteld gegeven)
Klavier
merk, type… (samengesteld gegeven)
Muis
merk, type… (samengesteld gegeven)
Geluidskaart
merk, type… (samengesteld gegeven)
Netwerkkaart
merk, type… (samengesteld gegeven)
ISATotaal
totaal aantal ISA-uitbreidingsgleuven
ISABezet
aantal bezette ISA-uitbreidingsgleuven
PCITotaal
totaal aantal PCI-uitbreidingsgleuven
PCIBezet
aantal bezette PCI-uitbreidingsgleuven
BIOS
naam
Moederkaart
naam
Chipset
naam
Extra’s
soort, merk en type van extra ingebouwde onderdelen, bijvoorbeeld, zipdrive,
cd-writer… (samengesteld gegeven)
Opmerkingen
algemene opmerkingen i.v.m. het toestel
Eerste normaalvorm
Afhankelijk van het computerpark dat beschreven wordt, bevat de gegevensgroep verschillende
repeterende groepen. In sommige computerparken is het bijvoorbeeld mogelijk dat een computer
meerdere netwerkkaarten bevat, meerdere diskettestations enz. Hier wordt verondersteld dat een
toestel meerdere harde schijven kan hebben en meerdere extra's. Dit leidt tot volgende splitsing.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 43
Computer
Extra
HardeSchijf
ComputerID
Serienummer
Merk
Type
Lokaal
Functie
Processor
Diskettestation
CD/DVD
RAM
RAMtype
RAMUitTeBreiden
Beeldscherm
Klavier
Muis
Geluidskaart
Netwerkkaart
ISATotaal
ISABezet
PCITotaal
PCIBezet
BIOS
Moederkaart
Chipset
Opmerkingen
ExtraID
ComputerID
Type
Beschrijving
Merk
HardeSchijfID
ComputerID
Type
Capaciteit
Snelheid
Merk
Tweede normaalvorm
Enkel de nieuwe gegevensgroepen moeten bekeken worden, want zij hebben een samengestelde
sleutel. In beide gevallen hangen alle niet sleutelattributen af van het eerste deel van de sleutel. Dit
leidt tot volgde splitsingen.
ExtraComputer
HardeSchijfComputer
ExtraID
ComputerID
HardeSchijfID
ComputerID
Extra
HardeSchijf
ExtraID
Type
Beschrijving
Merk
HardeSchijfID
Type
Capaciteit
Snelheid
Merk
Derde normaalvorm
Diskettestation, CD/DVD, Beeldscherm, Klavier, Muis, Geluidskaart en Netwerkkaart zijn samengestelde gegevens. Bovendien is het zo dat als het identificerend gegeven bekend is. Dit leidt
tot volgde splitsingen.
Computer
ComputerID
Serienummer
Merk
Type
Lokaal
Functie
Processor
DiskettestationID
CD/DVD_ID
RAM
Pagina 44
Diskettestation
DiskettestationID
Merk
Type
Snelheid
ComputerID
CD/DVD
CD/DVD_ID
Merk
Type
Speed
Beeldscherm
BeeldschermID
Merk
Type
KlavierID
KlavierID
Merk
Type
Database-ontwerp
RAMtype
RAMUitTeBreiden
BeeldschermID
KlavierID
MuisID
GeluidskaartID
NetwerkkaartID
ISATotaal
ISABezet
PCITotaal
PCIBezet
BIOS
Moederkaart
Chipset
Opmerkingen
Energiesparend
Ergonomisch
Muis
MuisID
Merk
Type
Soort
Geluidskaart
GeluidskaartID
Merk
Type
AantalBit
Driver
Netwerkkaart
NetwerkkaartID
Merk
Type
Driver
De andere gegevensgroepen van Computerpark zijn in de derde normaalvorm.
3.13
1
Oefeningen
Verbind de termen in kolom 1 met de synoniemen in kolom 2.
Veld
Rij
Record
Kolom
Attribuut
Relatie
Tabel
Instantie van entiteit
Kenmerk
2
Geef een voorbeeld van een unieke sleutel die door volgende organisaties of instellingen
wordt gebruikt om iemand te identificeren:
Verzekeringsmaatschappij - Financiële instelling – Mutualiteit – Sportvereniging
3
Onderstreep in de eerste rij (veldnamen) van volgende tabel de primaire sleutel.
NUMMER
P10
P11
P40
P50
P60
NAAM
Legrand
Legrand
De Bolle
Vervaet
Legrand
VOORNAAM
Pieter
Annemie
Annemie
Sandra
Pieter
GEBOORTEDATUM
1990-11-11
1990-11-11
1991-01-15
1990-10-18
1967-05-20
LENGTE
174
168
172
168
174
GEWICHT
60
56
58
56
80
Kun je ook een kandidaatsleutel vinden?
4
Bepaal een mogelijke structuur voor personeelsgegevens van een onderneming. In de databank met personeelsgegevens wordt naast de gegevens van de werknemers (naam, datum
indiensttreding, afdeling, brutoloon) ook de naam en de geboortedatum van de kinderen bijgehouden.
5
Normaliseer de gegevensgroepen die je hebt gevonden als resultaat van de oefeningen uit
het vorige hoofdstuk.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 45
4
Omzetting datamodel (ERD) naar databaseontwerp (tabellen)
•
Omzetting van entiteiten
•
Omzetting van relaties
Een entity-relationship diagram kun je omzetten naar de verschillende soorten DBMS. In dit hoofdstuk wordt de omzetting naar een RDBMS besproken. Het resultaat van de omzetting wordt het
databaseontwerp genoemd.
Een relationele database ontwerpen wil zeggen de tabellen en hun onderlinge relaties definiëren en
wel zo dat de database geen overtollige noch tegenstrijdige gegevens zal bevatten.
De gegevens moeten steeds genormaliseerd worden. Je mag dit doen op het niveau van het datamodel, d.w.z. het datamodel verfijnen tot de attributen die bij de entiteiten of de relaties horen, een
genormaliseerde groep vormen, of op het niveau van het database ontwerp, d.w.z. eerst een omzetting doen naar gerelateerde tabellen en dan de tabellen normaliseren.
4.1
Entiteiten
4.1.1
Zelfstandige entiteiten
Als je een Entiteit-Relatie model hebt opgemaakt kun je in eerste instantie de entiteiten vertalen in
tabellen. De entiteiten worden tabellen, de attributen worden velden. Indien dit nog niet gebeurd is,
moet je nog een normalisatie doorvoeren.
4.2
Relaties zonder attributen
4.2.1
Optionaliteit
De optionaliteit van een relatie wordt omgezet tot een eventuele beperking (constraint) van de tabel.
Als de optionalteit 1 is, is de entiteit vereist, wat meestal in de RDBMS zal leiden tot het vereist stellen van de refererende sleutel in de tabel coresponderend met de andere entiteit.
4.2.2
Één-op-één relatie
Voorbeeld
relatie:
gebruikt
entiteiten:
Bureautafel(BureautafelID, Locatie, Type…) en
Werknemer(WerknemerID, Familienaam, Voornaam…)
definitie:
Administratieve werknemers krijgen een vaste bureautafel toegewezen op kantoor.
Deze bureautafel moet met niemand gedeeld worden en iedereen krijgt slechts één
bureautafel. Er kunnen werknemers zijn zonder bureautafel en bureautafels die niet
gebruikt worden door werknemers.
Werknemer
Bureautafel
gebruikt
Pagina 46
Database-ontwerp
De relatie Gebruikt wordt in het relationele ontwerp gebracht door tussen de tabellen Werknemer en
Bureautafel een relatie (koppeling) te leggen. Dit gebeurt door aan de tabel Bureautafel de primaire
sleutel (WerknemerID) van de tabel Werknemer toe te voegen of omgekeerd. Het toegevoegde veld
is de refererende sleutel.
Van elke entiteit wordt een tabel gemaakt. Hierbij neem je in één van de beide tabellen de primaire
sleutel op van de andere tabel. De tabellen in het relationele ontwerp worden dus:
Werknemer
Bureautafel
WerknemerID
Familienaam
Voornaam
…
1
BureautafelID
Locatie
Type
1 …
WerknemerID
of
Werknemer
Bureautafel
WerknemerID
Familienaam
Voornaam
…
BureautafelID
1
BureautafelID
1 Locatie
Type
…
In principe zijn beide oplossingen gelijkwaardig. Welke van de twee uiteindelijk praktisch het beste
is, hangt af van de bevraging van de databank. Als daarbij meestal uitgegaan wordt van de werknemers (wat waarschijnlijk het geval is), dan is de eerste oplossing de beste.
Je ziet dat iedere tabel een primaire sleutel heeft. In de eerste oplossing is WerknemerID de refererende sleutel, in de tweede oplossing is BureautafelID de refererende sleutel.
Opmerking
In sommige gevallen kunnen de tabellen coresponderend met de entiteiten waartussen de één-opéén relatie zonder attributen ligt, teruggebracht worden tot één tabel. Dit kan enkel als tenminste in
één van de tabellen geen enkel attribuut als primaire sleutel kan fungeren, anders krijg je immers
transitieve afhankelijkheid.
Het is vooral de optionaliteit die zal bepalen of het werken met één enkele tabel zinvol is. Veel lege
velden bijhouden heeft geen zin.
4.2.3
Eén-op-veel relatie
Beschouw dezelfde relatie als hierboven maar waarbij het mogelijk is dat een werknemer meerdere
bureautafels toegewezen krijgt.
Bureautafel
Werknemer
gebruikt
De omzetting gebeurt op dezelfde manier als bij een één-op-één relatie maar nu wordt de primaire
sleutel van de tabel langs de één-kant toegevoegd als refererende sleutel aan de tabel aan de veelkant.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 47
Werknemer
Bureautafel
WerknemerID
Familienaam
Voornaam
…
1
BureautafelID
Locatie
Type
∞ …
WerknemerID
De één- en de veel-kant worden respectievelijk aangeduid door de symbolen 1 en ∞.
4.2.4
Veel-op-veel relatie
Beschouw weer dezelfde relatie maar breid ze verder uit door toe te laten dat eenzelfde bureautafel
gedeeld wordt door verschillende werknemers.
Werknemer
Bureautafel
gebruikt
Om deze relatie te vertalen naar een relationeel ontwerp, creëer je nog een extra tabel naast de
tabellen die de entiteiten voorstellen. Deze derde tabel geef je als attributen de primaire sleutel van
beide tabellen (deze tabel bevat dus enkel de primaire sleutels uit beide tabellen). Ze vormen samen
de primaire sleutel van de nieuwe tabel en elk apart een refererende sleutel voor een entiteit-tabel.
Iedere entiteit-tabel heeft een één-op-veel relatie met de bijkomende derde tabel.
Werknemer
WerknemerBureautafel
WerknemerID
Familienaam
Voornaam
…
∞
1
WerknemerID
BureautafelID
4.3
Relaties met attributen
4.3.1
Eén-op-één relatie
Bureautafel
∞
1
BureautafelID
Locatie
Type
…
Voorbeeld
relatie:
Huwt
entiteiten:
Man(ManID, Familienaam, Voornaam…) en
Vrouw(VrouwID, Familienaam…)
definitie:
Een man huwt een vrouw. Een man kan maar één vrouw huwen en omgekeerd. Niet
elke man en niet elke vrouw huwt.
attributen:
Huwelijksdatum, NaamGetuige1, NaamGetuige2
Man
huwt
Vrouw
De relatie wordt vertaald in een bijkomende tabel.
Pagina 48
Database-ontwerp
Man
Huwelijk
ManID
Familienaam
Voornaam
…
1
Huwelijksdatum
NaamGetuige1
NaamGetuige2
1 VrouwID
ManID
Vrouw
VrouwID
Familienaam
Voornaam
…
1
1
De derde tabel bevat de primaire sleutels van de twee andere tabellen. Vanwege de één-op-één
relatie kan, als primaire sleutel van de tabel die met de relatie overeenkomt, de primaire sleutel van
één van beide andere tabellen gekozen worden. De attributen van de relatie worden toegevoegd als
gewone velden in de derde tabel.
Beide relaties zijnvan het type één-op-één.
4.3.2
Eén-op-veel relatie
Voorbeeld
relatie:
Schrijft in
entiteiten:
Persoon(PersoonID, Familienaam, Voornaam, …) en
Cursus(CursusID, Naam, Lesgever, Klaslokaal, …)
definitie:
Een persoon schrijft in voor een cursus. Een persoon kan en moet inschrijven voor
één cursus.
attributen:
Inschrijvingsdatum, Inschrijvingsgeld
Persoon
Cursus
schrijft in
Verklaar de optionaliteit van deze relatie.
Naast de entiteiten, wordt de relatie zelf ook vertaald in een tabel. Deze tabel krijgt als attributen de
attributen van de relatie en de primaire sleutels van de entiteit-tabellen.
Persoon
Inschrijving
PersoonID
Familienaam
Voornaam
…
1
1
Inschrijvingsdatum
Inschrijvingsgeld
PersoonID
CursusID
Cursus
1
∞
CursusID
Naam
Lesgever
…
Als primaire sleutel van de nieuwe tabel kan de primaire sleutel van de tabel gekoppeld via de éénop-één relatie gebruikt worden.
Verklaar.
4.3.3
Veel-op-veel relatie
Beschouw dezelfde relatie waarbij het nu toegestaan is dat een persoon zich inschrijft voor meerdere cursussen.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 49
Persoon
Cursus
schrijft in
Persoon
Inschrijving
PersoonID
Familienaam
Voornaam
…
1
∞
Inschrijvingsdatum
Inschrijvingsgeld
PersoonID
∞
CursusID
Cursus
1
CursusID
Naam
Lesgever
Klaslokaal
…
Welke verschillen zijn er met de één-op-veel relatie?
De twee entiteiten worden elk omgezet naar een tabel. Er wordt opnieuw gebruik gemakt van
een derde tabel die de primaire sleutels bevat van beide tabellen plus de attributen van de relatie.
Hoe de primaire sleutel bepalen in de derde tabel? Zoek een functionele afhankelijkheid van de
vorm X is f.a. van Y, of X is f.a. van de combinatie {Y,Z}. Als je geen enkele functionele afhankelijkheid vindt, voeg dan zelf een nieuw veld toe dat je primaire sleutel maakt.
Zoals je opmerkt zijn de velden Inschrijvingsdatum en Inschrijvingsgeld functioneel afhankelijk
van de combinatie {PersoonsID, CursusID}. Daarom mag je de combinatie PersoonID en CursusID instellen als primaire sleutel in de derde tabel.
4.3.4
Overzicht
Relaties zonder attributen
Eén-op-een relatie
•
tabellen (entiteiten)
•
PS van ene tabel (keuze) toevoegen als RS aan andere tabel
•
in sommige gevallen terugbrengen tot één tabel
Eén-op-veel relatie
•
tabellen (entiteiten)
•
PS van de één-kant toevoegen als RS aan de veel-kant
Veel-op-veel relatie
•
tabellen (entiteiten & nieuwe tabel)
•
in de nieuwe tabel opnemen van PS van de 2 entiteit-tabellen, 2 sleutels vormen samen PS
nieuwe tabel
Relaties met attributen
Eén-op-een relatie
•
tabellen (entiteiten & relatie)
•
PS van de entiteit-tabellen toevoegen aan nieuwe tabel als RS samen met de attributen van de
relatie
Pagina 50
Database-ontwerp
•
PS van de nieuwe tabel: één van de PS van de entiteit-tabellen (keuze)
Eén-op-veel relatie
•
tabellen (entiteiten & relatie)
•
PS van de entiteit-tabellen toevoegen aan nieuwe tabel als RS samen met de attributen van de
relatie
•
PS van de nieuwe tabel: PS van de entiteit-tabel aan de één-kant
Veel-op-veel relatie
•
tabellen (entiteiten & relatie)
•
PS van de entiteit-tabellen toevoegen aan nieuwe tabel als RS samen met de attributen van de
relatie
•
PS van de nieuwe tabel: samengestelde sleutel – PS van de entiteit-tabellen
4.4
Uitgewerkt voorbeeld
De gegevensgroepen behorende bij de entiteiten in het datamodel voor Computerpark zijn reeds
genormaliseerd in vorig hoofdstuk. Alle resulterende gegevensgroepen zullen omgezet worden in
een tabel. Dit leidt tot de volgende tabellen: Randapparaat, Software, Licentie, Verbruiksproduct,
Leverancier, Computer, HardeSchijf, Extra, HardeSchijfComputer, ExtraComputer, Diskettestation, CD_DVD, Beeldscherm, Klavier, Muis, Geluidskaart, Netwerkkaart.
De relaties tussen deze tabellen zijn af te leiden uit het datamodel of volgen uit de normalisering.
Het datamodel bevat drie veel-op-veel relaties en vijf relaties met attributen nl. Levertx en Draait
op. Deze moeten nog omgezet worden naar tabellen en relaties voor het relationeel model.
Is verbonden met
Veel-op-veel relatie zonder attributen.
Computer
ComputerID
…
Verbinding
∞ ComputerID
RandapparaatID
1
Randapparaat
∞
1
RandapparaatID
…
Levertx
Het gaat hier om 4 verschillende relaties die verschillende entiteiten verbinden. Drie van de relaties
zijn één-op-veel en de vierde is veel-op-veel.
Elke relatie heeft dezelfde attributen.
Naam
Definitie
Leveringsdatum
datum waarop goederen geleverd werden
Ja
Aankoopprijs
prijs per stuk
Ja
Opmerking
opmerking i.v.m. de levering
VVKSO
Werkgroep Handel
Verplicht
Database-ontwerp - pagina 51
Levert1, levering computers (één-op-veel)
De relatie wordt vertaald in een tabel: LeveringComputer.
Computer
ComputerID
…
LeveringComputer
1
Leveringsdatum
Aankoopprijs
1 Opmerking
LeverancierID
ComputerID
Leverancier
LeverancierID
1 …
∞
Als meerdere computers bij dezelfde levering aan dezelfde prijs geleverd worden, krijg je dikwijls
dezelfde waarden voor de attributen en de tabel LeveringComputer. In dat geval kun je aan de levering een identificatie toekennen en pas je de tabellen aan.
Computer
ComputerID
LeveringID
…
LeveringComputer
∞
1
LeveringID
Leveringsdatum
Aankoopprijs
Opmerking
LeverancierID
Leverancier
LeverancierID
1 …
∞
Hoe voorzie je deze situatie in het datamodel?
Vertaal het aangepaste datamodel naar een databaseontwerp.
Bekom je hetzelfde resultaat?
Levert2: levering randapparaten (één-op-veel)
Idem.
Levert3: levering software (één-op-veel)
De relatie wordt vertaald in een tabel LeveringSoftware.
Software
SoftwareID
…
LeveringSoftware
1
Leveringsdatum
Aankoopprijs
1 Opmerking
LeverancierID
SoftwareID
Leverancier
LeverancierID
1 …
∞
Tussen de tabellen Software en LeveringSoftware bestaat een één-op-één relatie. Deze kan in
sommige gevallen teruggebracht worden tot één tabel.
Omdat een bepaald softwarepakket (installatie cd's) slechts in één exemplaar en door één leverancier geleverd wordt, plaatsen we de attributen van de levering bij in de tabel Software.
Is dit een goed idee als er veel freeware gebruikt wordt? Verklaar je antwoord.
Pagina 52
Database-ontwerp
Software
Leverancier
SoftwareID
…
LeveranciersID
Leveringsdatum
Aankoopprijs
Opmerking
LeverancierID
1 …
∞
Levert4: levering verbruiksproducten
Hier moet er met een aparte tabel gewerkt worden omdat het om een veel-op-veel relatie gaat.
Verbruiksproduct
LeveringVerbruiksProd
VerbruiksproductID 1
…
LeveringID
Leveringsdatum
Aankoopprijs
Opmerking
∞ LeverancierID
VerbruiksproductID
Leverancier
LeverancierID
1 …
∞
In principe vormen LeverancierID, VerbruikproductID en Leveringsdatum samen een samengestelde primaire sleutel. Hier is de voorkeur gegeven aan een surrogaatsleutel LeveringsID.
Draait op
Deze relatie is een veel-op-veel relatie. Er zal dus met een bijkomende tabel moeten gewerkt worden. De attributen van Draait op zijn:
Naam
Definitie
Begindatum
datum installatie
Einddatum
datum verwijdering
Opmerking
opmerking, bijvoorbeeld Service Release die geïnstalleerd is, optionele
componenten
Computer
ComputerID
LeveringID
…
DraaitOp
1
∞ ComputerID
SoftwareID
Begindatum
Einddatum
Opmerking
Software
∞
SoftwareID
1 …
Opmerking: mogelijk bevatten alle computers in hetzelfde lokaal ook dezelfde software.
Is in dit geval deze oplossing een goede oplossing? Verklaar je antwoord.
Pas het datamodel en het dataontwerp aan aan deze situatie.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 53
Connecteert
Connecteert is een speciale relatie omdat ze een entiteit verbindt met zichzelf. Ook hier is een supplementaire tabel nodig.
Randapparaat
RandapparaatID
…
Connectie
1
RandapparaatID1
∞ RandapparaatID2
Vanwege het speciale karakter van tabel Connectie zal er steeds bijzondere aandacht moeten besteed worden aan query's en filters die hiermee gemaakt worden.
4.5
Oefeningen
Zet de datamodellen van de oefeningen uit het vorige hoofdstuk om naar databaseontwerpen voor
een RDBMS.
Pagina 54
Database-ontwerp
5
Een database implementeren
•
•
•
5.1
Componenten van de database
Gegevenstypes in Sql
Column Properties
Een SQL-databank aanspreken in Visual Web Developer
Microsoft SQL Server 2008 is de grote broer van MS Access. Je kunt er grote client-server toepassingen mee maken. Raadpleeg onderstaande website voor uitgebreide informatie.
http://technet.microsoft.com/en-us/sqlserver/default.aspx
Je hoeft niet over de volledige Microsoft SQL Server toepassing te beschikken om toch met SQLdatabases te kunnen werken. De Microsoft SQL Server 2008 Express Edition kun je gratis te downloaden:
http://www.microsoft.com/express/sql/default.aspx
Je kunt tijdens de installatie van Visual Basic Express Edition 2008 of Web Developer Express
Edition 2008 aanklikken of Microsoft SQL Server Express Edition 2008 mee dient geïnstalleerd
te worden.
Start Visual Web Developer Express Edition 2008 (VWD) op.
Open de Database Explorer via het menu View. Kies in de knoppenbalk van Database
Explorer voor de knop Connect to Database.
De SQL Server staat standaard ingesteld.
Klik op Change… indien je een Accessdatabase wilt koppelen.
Zoek via de Browse-knop de SQL-database
SportclubSQL.mdf op.
Klik eventueel op de knop Test Connection.
Er verschijnt:
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 55
Objecten voor het maken van een databaseverbinding:
Namespace
Type
Database
System.Data.Odbc
OdbcConnection
Alle databases die Odbc ondersteunen (verouderd)
System.Data.OleDb
OleDbConnection
Alle databases die OleDb ondersteunen (Access)
System.Data.OracleClient
OracleConnection
Oracle
System.Data.SqlClient
SqlConnection
SQL Server
Open de databank SportclubSQL.mdf.
In de Database Explorer verschijnen
de componenten van de de database. Dubbelklik op de verzameling
Tables.
Componenten van de database:
Map
Verklaring
Database Diagrams
Databasediagrammen voegen op zich niets toe aan een database, maar
zijn er om de tabellen en de relaties tussen de tabellen te visualiseren.
Tables
De tabellen in de database.
Views
Een view is een weergave van gegevens in de database, een soort virtuele tabel. De view kan gegevens uit 1 of meerdere tabellen weergeven.
Stored procedures
Dit zijn functies die je kunt uitvoeren. Dit kan zijn om gegevens op te vragen, toe te voegen, te wijzigen of te verwijderen. Parameters zijn toegelaten. Stored Procedures worden veel gebruikt om een toepassing niet
direct op tabellen te laten uitvoeren. Daardoor is de onderliggende tabelstructuur gemakkelijker te veranderen, omdat je alleen maar de Stored
Procedures dient te veranderen en de code van de toeassing kan blijven.
Werken met Stored Procedures is ook veiliger, omdat ze eerst kunnen
controleren of hetgeen de toepassing wil doen wel toegelaten is.
Functions
Kunnen in een view of stored procedure gebruikt worden. Functies kun je
niet direct vanuit een toepassing gebruiken, maar zijn er om de sql-code
van views en stored procedures te vereenvoudigen en er voor te zorgen
dat je niet steeds dezelfde code op verschillende plaatsen dient te schrijven.
Synonyms
Synoniemen voor objecten (tabellen, views, stored procedures) in de
database. Synoniemen maken het mogelijk om de objecten te herbenoemen zonder dat toepassingen hier last van ondervinden.
Types
Standaard zijn er een aantal types die kolommen in een database kunnen
hebben. Je kunt ook types zef definiëren via de map Types.
Pagina 56
Database-ontwerp
Sql-server biedt de mogelijkheid om .NET functies aan te roepen in een
query of stored procedure, zodat je functies in VB of C# kunt maken in
plaats van in Sql. Als je een functie wilt gebruiken moet je de assembly
waarin deze zit registreren in Sql-server. Dat doe je in de map Assemblies
Assemblies
Bekijk de inhoud van de tabel tblLeden
via snelmenu, Show Table Data.
Bekijk de structuur van tblLeden (of dubbelklik op de tabel).
Test of je veranderingen kunt aanbrengen in de velden van tblLeden.
5.2
Een SQL-databank aanmaken in VWD Express 2008
Creëer volgende sql-database PatientenSQL.mdf, ontwerp de structuur van de tabel
tblPatiëntgegevens en plaats er volgende gegevens in:
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 57
Werkwijze:
•
•
•
Maak de website Patienten aan. Er wordt onder meer een map App_Data aangemaakt.
Open in de website het snelmenu van de map App_Data en kies Add New Item…
Kies SQL Server Database en geef deze de naam PatientenSQL.mdf.
Indien de database aangemaakt wordt in de map App_Data is deze automatisch gekoppeld
aan de website.
Je kunt ook het snelmenu van het project Patienten opvragen en kiezen voor Add New Item.
Er volgt dan:
Bemerk dat ook de database SportclubSQL.mdf beschikbaar blijft in de nieuwe website. Dit
is het gevolg van het feit dat deze database niet werd geplaatst in de eigen App_Data-map.
Het is zeer belangrijk dat je goed nadenkt voor je de structuur vastlegt. Later wijzigingen aanbrengen, kan behoorlijk ingewikkeld zijn en bovendien soms leiden tot gegevensverlies!
•
Kies het snelmenu van Tables en selecteer Add New Table en maak volgende structuur aan (zie
bijlage 3 voor een overzicht van de belangrijkste types in SQL):
Pagina 58
Database-ontwerp
•
Enkel de velden Geboortedatum, Lengte en Gewicht mogen leeg gelaten worden. De lengte is
een geheel getal (smallint), het gewicht kan een decimaal gedeelte bevatten (float).
•
Plaats van het veld PatientID de Column Properties (Is Identity) op Yes zodat dit veld ‘autonummering’ wordt. De Identity Increment geeft aan dat de waarde voor iedere nieuwe rij met 1
verhoogd wordt en de Identity Seed geeft aan dat de teller bij 1 begint. Maak van PatientID de
sleutel en sla de tabel als tblPatientgegevens op.
Let steeds op het volgende:
- gebruik betekenisvolle veldnamen. Uit de naam van een veld moet je de inhoud ervan kunnen afleiden;
- vermijd te lange veldnamen en gebruik zo weinig mogelijk spaties. Dit laatste is vooral van
belang als je vaak in expressies of code naar objecten verwijst.
•
Voer bovenstaande records in.
Vul voor de onderstaande velden het gekozen gegevenstype in en ga na waarom die keuze
gemaakt werd. Gebruik eventueel de helpfunctie voor meer uitleg over de verschillende
types (Help, Search, trefwoord: types in sql).
Veldnaam
Gegevenstype
Werknemer-ID
BrutoWedde
Auto
InDienst
Foto
Bijzonderheden
Gebruik steeds het gepaste gegevenstype. Een postnummer is bijvoorbeeld van het tekentype, ook al bevat het enkel cijfers, omdat je er niet mee rekent.
5.3
Relaties tussen tabellen
De gegevens in de verschillende tabellen kunnen naar elkaar verwijzen via een gemeenschappelijk
veld. Beide velden dienen van hetzelfde type te zijn (benaming mag verschillen). Bij referentiële
integriteit kan een record van de tabel aan de veelkant maar ingevuld worden als er een overeenkomstige sleutel is in de tabel aan de éénkant. Zodoende wordt aan invoercontrole gedaan en wordt
verhinderd dat onbetrouwbare gegevens in een tabel worden opgeslagen wat betekent:
•
•
•
niet-bestaande referenties kunnen dan niet ontstaan;
gegevens waarnaar verwezen wordt kunnen niet verwijderd worden;
sleutelwaarden die veranderen kunnen eventueel (trapsgewijs) aangepast worden.
Relaties kun je maken met de Table designer of met behulp van een diagram.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 59
5.4
Table designer
Klik in de werkbalk Table Designer op het pictogram Relationships.
Als oefening werken we met de databank SportclubSQL.mdf en worden relaties gelegd tussen de
tabellen tblLeden en tblPostnummers, tussen tblLeden en tblInschrijvingen en tussen tblInschrijvingen en tblSporttakken.
Gemeenschappelijk veld tussen de tabellen tblPostnummers en tblLeden:
Primary key table
Foreign key table
tblPostnummers
tblLeden
Key: Stad
Refererende sleutel: Gemeente
Je vertrekt van de tabel met de refererende sleutel (foreign key) om de relatie te leggen met
een primaire sleutel in een andere tabel.
Kies het snelmenu van tabel tblLeden en kies voor Open Table Definition of Show Tabel Data.
Kies in Table Designer de knop Relationships
.
Het dialoogvenster Foreign Key Relationships verschijnt.
Kies Add.
Elke relatie krijgt een naam. Je kunt deze zelf geven of de door het systeem gegeven naam aanvaarden.
Om de relatie in te stellen (of te wijzigen) gebruik je de knop
Pagina 60
bij Tables And Colums Specification.
Database-ontwerp
Vul de gegevens in zoals hierboven en klik OK. Kies Close. Er wordt gevraagd om de wijzigingen te bewaren.
Zoek het gemeenschappelijk veld tussen de andere tabellen.
Primary key table
Foreign key table
tblLeden
tblInschrijvingen
Key:
Refererende sleutel:
Primary key table
Key:
Foreign key table
Refererende sleutel:
Leg de ontbrekende relaties.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 61
5.4.1
Diagrammen
Relaties tussen tabellen kunnen ook gelegd worden door gebruik te maken van een diagram.
Om dit te kunnen uitvoeren wissen we eerst de relaties die werden gemaakt via Designer
Table.
Selecteer het onderdeel Database Diagrams van
SportclubSQL.mdf en kies Add New Diagram in het
snelmenu.
Er verschijnt:
Voeg alle tabellen toe. De relaties die al bestaan worden in het diagram door verbindingslijnen
voorgesteld.
Je legt een relatie tussen twee tabellen door de primaire sleutel van één tabel te slepen naar een
refererende sleutel van een andere tabel (om bestaande relaties te bewerken: selecteer de relatie
en druk op F4.)
Leg de relaties tussen de tabellen zoals hierboven.
Primaire sleutel
Refererende sleutel
tblPostnummers(Stad)
tblLeden(Gemeente)
tblLeden(Nummer)
tblInschrijvingen(Lidnr)
tblSporttakken(Taknr)
tblInschrijvingen(Taknr)
Om relaties te bewerken kies je in het snelmenu van een afgebeelde tabel: Relationships...
Pagina 62
Database-ontwerp
Bij het sluiten van het relatievenster bevestig je de veranderingen. Daarna wordt de naam gevraagd
van het diagram.
Na klikken op OK, volgt:
5.5
Check constraints
Om domein integriteit te ondersteunen maakt SQL server onder meer gebruik van check constraints.
In PatientenSQL.mdf, tabel tblPatientgegevens wil men de maximale lengte beperken tot 250 (cm)
en het maximaal gewicht tot 200 kg. Voor beide velden mogen uiteraard geen negatieve waarden
ingevoerd worden.
Klik op het pictogram Manage Check Constraints in Table Designer van tblPatientgegevens.
Klik op Add.
Voeg de beperking toe via Expression:
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 63
Leg de beperkingen op voor Lengte en Gewicht. Test een beperking uit. Er volgt voor een
foutief ingevoerde lengte:
In uitdrukkingen zoals Postnummer >= 1000 And Postnummer <=9999 wordt een karakterstring (Postnummer) vergeleken met een gehele waarde. Sql laat het gebruik van een impliciete omzetting van het karaktertype toe.
5.6
Standaardwaarden
Standaardwaarden (defaults) vormen een toevoeging om data integriteit te ondersteunen.
We beschouwen database SportclubSQL.mdf en geven een standaardwaarde aan de velden
Gemeente en Geslacht van tblLeden. Je doet dit in de Table Designer.
De meeste leden komen uit Ieper en zijn van het mannelijk geslacht. Deze gegevens plaatsen we
als standaardwaarden.
Voer eerst een check constraint in tblLeden voor het veld Geslacht: enkel V of M zijn toegestaan.
Klik op het pictogram Manage Check Constraints in Table Designer van tblLeden.
Selecteer het veld Gemeente en vul voor Default Value or Binding de standaardwaarde in.
Pagina 64
Database-ontwerp
Selecteer het veld Geslacht en vul de standaardwaarde M in.
Stel in tblInschrijvingen de standaardwaarde voor het veld Betaald in op False.
5.7
Indexen
5.7.1
Inleiding
Een index levert een opgeslagen structuur. SQL Express 2008 bepaalt zelf wanneer en welke index
gebruikt zal worden voor het selecteren van gegevens. Door het maken van indexen in tabellen kun
je de snelheid van zoekopdrachten in de tabel spectaculair verhogen. Indexen nemen uiteraard
ruimte in op opslagmedia en bij het toevoegen en verwijderen van records moeten de indexen ook
bijgewerkt worden. Een index vertraagt dit soort van opdrachten. Er moet dus altijd een afweging
gebeuren tussen voor- en nadelen van de ingreep.
SQL server kent twee soorten indexen: clustered en non-clustered. Een geclusterde index zorgt
ervoor dat de rijen in de tabel fysiek geordend worden volgens de index. Er kan dus in een tabel
slechts één geclusterde index bestaan. Geef je een tabel een primaire sleutel, dan wordt automatisch een geclusterde index voor deze sleutel aangemaakt.
Een niet-geclusterde index kun je maken voor elk veld of combinatie van velden. Bij voorkeur kies je
een index voor velden waarvoor veel selectieopdrachten bestaan.
In een niet-geclusterde index voor een heap (een tabel zonder geclusterde index) wordt verwezen
naar een record door zijn rijnummer in de tabel. In het andere geval wordt verwezen naar de sleutelwaarde in de geclusterde index.
5.7.2
Een index aanmaken
We maken een niet-geclusterde index aan op het veld Gemeente voor de tabel tblLeden van database SportclubSQL.mdf.
Selecteer het veld Gemeente. Klik op het pictogram Manage Indexes and Keys in Table
Designer van tblLeden.
Klik op Add.
Vul de gegevens in zoals hierboven.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 65
5.8
Tabelaanpassingen
5.8.1
Rijen aanpassen
•
Velden of rijen verwijderen
Selecteer de rij en
-
•
druk op de Delete-toets
snelmenu en Delete Column
Velden of rijen verplaatsen
Selecteer de rij met de rijknop. Sleep dan de geselecteerde rij naar de gewenste positie.
•
Velden of rijen invoegen
Selecteer de rij waarvoor je een veld wenst in te voegen en
-
5.8.2
•
Snelmenu en kies voor Insert Column
Primaire sleutel
Eén veld of meerdere velden als primaire sleutel instellen:
-
selecteer het veld of de meerdere velden
kies het snelmenu en klik op Set primary Key
-
klik op het pictogram
Table Designer.
of
van de werkbalk
Het kiezen van een primaire sleutel heeft volgende
voordelen:
−
je kunt nooit twee records met dezelfde waarde voor
dat veld (of combinatie van velden) tegenkomen;
−
het sorteren en opzoeken volgens dit veld gaat aanzienlijk sneller;
−
de tabel wordt gerangschikt volgens de waarde van
de primaire sleutel.
5.9
Oefeningen
1
Hoeveel primaire sleutels kan een tabel hebben?
2
Een primaire sleutel bestaat uit meerdere velden. Hoe kun je die primaire sleutel toekennen?
Hoe ga je praktisch te werk als deze velden niet naast elkaar liggen?
Pagina 66
Database-ontwerp
6
Speciale technieken
6.1
Join-eigenschappen
6.1.1
Een tabel koppelen met zichzelf
Beschouw de database ArtemisSQL.mdf.
Bij het koppelen van een tabel met zichzelf maak je gebruik van een Alias.
Voer de volgende SELECT-instructie uit.
SELECT TOP (100) PERCENT dbo.tblWerknemers.Familienaam,
dbo.tblWerknemers.Voornaam, tblWerknemers_1.Familienaam AS Expr1,
tblWerknemers_1.Voornaam AS Expr2
FROM dbo.tblWerknemers INNER JOIN dbo.tblWerknemers AS tblWerknemers_1
ON dbo.tblWerknemers.BrutoWedde < tblWerknemers_1.BrutoWedde
ORDER BY dbo.tblWerknemers.Familienaam, dbo.tblWerknemers.Voornaam
Je bent hier natuurlijk verplicht volledige kolomnamen te gebruiken omdat er verwarring mogelijk is.
Wat ‘vergelijkt’ deze SELECT-instructie?
Bewaar de view:
6.1.2
Verbinding op meer dan 1 veld
Maak een view ZelfdeStraatView van de klanten die in dezelfde straat wonen.
Dezelfde straatnamen kunnen in meerdere gemeenten voorkomen. Er moet dus een verbinding
gemaakt worden zowel op Straat als op Gemeente. Om dubbele gegevens te vermijden zorgen we
ervoor dat - per rij - de eerste naam alfabetisch kleiner is dan de tweede naam.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 67
De volgende SQL-instructie toont het gevraagde:
SELECT TOP (100) PERCENT dbo.tblKlanten.Naam, dbo.tblKlanten.Straat,
dbo.tblKlanten.Gemeente, tblKlanten_1.Naam AS Expr1, tblKlanten_1.Straat
AS Expr2, tblKlanten_1.Gemeente AS Expr3
FROM dbo.tblKlanten INNER JOIN dbo.tblKlanten AS tblKlanten_1
ON dbo.tblKlanten.Gemeente = tblKlanten_1.Gemeente AND
dbo.tblKlanten.Straat = tblKlanten_1.Straat AND dbo.tblKlanten.Naam <
tblKlanten_1.Naam
ORDER BY dbo.tblKlanten.Naam
6.1.3
Meer dan 2 tabellen gebruiken
Maak de view KostprijsPerOrderView die de kostprijs van de producten per order weergeeft.
De INNER JOIN’s zijn genest.
Pas de SELECT-instructie aan zo dat per order de klantnaam getoond wordt en het factuurbedrag weergegeven wordt. Hou hierbij rekening met een winstpercentage van 33 % en de
korting. Rangschik volgens de klantnaam.
Voeg de tabel tblKlanten bij via Add Table.
Pagina 68
Database-ontwerp
Bewaar als FactuurbedragPerOrderView.
6.1.4
Oefeningen
Vooraf: beschouw Stamboom.mdb. Zet de database om in de Sql-databank StamboomSQL.mdf.
Om een migratie naar SQL Server 2008 uit te voeren kun je volgende tool downloaden:
http://www.microsoft.com/downloads/details.aspx?FamilyId=133B59C2‐C89C‐4641‐BEBB‐
6D04476EC1BA&displaylang=en Na registratie kun je de migratie uitvoeren. De data komt steeds terecht in de DATA-map van Microsoft SQL server.
Vergeet de queries niet mee te migreren.
Inhoud van tblFamilieleden (via snelmenu van de tabel, Show Table Data):
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 69
Verklaring: de vader van Abeels Aldegonde is persoon met ID 2 d.i. Abeels Alex. Haar moeder is
Martens Mia.
1. Maak een view ZelfdeStraatView van familieleden die in dezelfde straat wonen:
Dezelfde straatnamen kunnen in meerdere gemeenten voorkomen (zie Keizerlei in Antwerpen en
Tongeren). Er moet dus een verbinding gemaakt worden zowel op Straat als op Gemeente.
Kun je er voor zorgen dat de dubbele namen verdwijnen? Je hoeft slecht 1 kleine aanpassing
te doen!
Oplossing:
Verdere uitwerking
Maak nu StamboomView die in de eerste kolom de namen plaatst van alle familieleden uit de tabel.
Als de vader of moeder voorkomt in de tabel wordt de naam afgedrukt.
Hints:
- Voeg in de view de tabel tblFamilieleden driemaal toe en koppel de tabellen zodat je een relatie
kunt leggen voor de gegevens van de vader en een relatie voor de gegevens van de moeder.
- Pas ook de join-eigenschappen aan.
Pagina 70
Database-ontwerp
Resultaat:
6.2
Niet-genormaliseerde gegevensgroepen normaliseren
6.2.1
Gegevens worden in rijen herhaald
Beschouw de eerder besproken tabel tblBestellingen van Sql-database AankopenSQL.mdf.
De tabel bevat nu geen duplicaten meer en elke cel is atomair.
We werken NV1 uit. In de records komen gegevens voor die in rijen worden herhaald. De
gegevens die herhaald worden dienen we te splitsen in twee tabellen. We leggen een relatie tussen
beide tabellen aan de hand van een gemeenschappelijk veld.
Hoe de tabel splitsen in twee tabellen (er zijn andere mogelijkheden)?
Stap 1: maak een selectiequery met daarin de velden waarvan de inhoud in de rijen wordt herhaald
(NaamLev, Tel, Straat, Postc en Plaats). Ga naar de eigenschappen van de query en vraag enkel
de unieke waarden op (= Distinct Values). Sorteer eventueel op NaamLev.
We bekomen de Sql-opdracht:
SELECT DISTINCT NaamLev, Tel, Straat, Postc, Plaats
FROM tblBestellingen
ORDER BY NaamLev
Verander de selectiequery via Change Type in een tabelmaakquery Make Table… Geef de aan te
maken tabel een gepaste naam, in ons geval tblLeveranciers.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 71
Deze tabel wordt de tabel aan de éénzijde. Voer de query uit
Controleer de inhoud van de aangemaakte tabel (snelmenu van tblLeveranciers, Show Table Data):
Stap 2: ga naar het tabelontwerp van tblLeveranciers (dubbelklik op de tabel) en voeg een Autonummeringsveld Id toe. Verander de Column Properties (Is Identity) in onderdeel Identity Specification in Yes. Increment 1 betekent dat de stapwaarde 1 is, Seed betekent startwaarde 1.
Maak dit veld sleutelveld.
Stap 3: ga naar het oorspronkelijke tabelontwerp en voeg een veld toe (bv LevNummer) van het
type numeriek int (voor verdere types: zie bijlage).
Stap 4: ontwerp een nieuwe query gebaseerd op beide tabellen.
Leg in het queryscherm een relatie tussen een veld uit de tabel aan de éénzijde en een veld uit de
oorspronkelijke tabel waarvan de inhoud overeenstemt. In de één-tabel moet de inhoud van dit veld
uniek zijn. Plaats nu het veld LevNummer in het queryrooster. Dit veld is nog leeg. Wijzig de query in
een bijwerkquery (Update).
Pagina 72
Database-ontwerp
Er verschijnt:
en vul bij New Value het volgende in: Id. Voer de query uit. Er verschijnt:
Test of het veld Nummer van tblBestellingen de correcte leveranciersnummers bevatten.
Stap 5: verwijder in de hoofdtabel tblBestellingen alle velden (behalve het veld LevNummer) die
voorkomen in tblLeveranciers. Het veld LevNummer mag niet worden verwijderd omdat het nodig is
om de relatie tussen de twee tabellen te garanderen.
Stap 6: leg een relatie tussen beide tabellen via de knop Relationships.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 73
6.2.2
Gegevens worden in kolommen herhaald
We werken de normalisatie uit aan de hand van database ElzaSQL.mdf. Hierin vind je de
niet genormaliseerde tabel tblNietGenormaliseerd. In deze tabel komen meerdere kolommen voor
die worden herhaald. Deze kolommen hebben inhoudelijk dezelfde gegevens. We splitsen bijgevolg
tblFacturen in twee tabellen.
Een tabel splitsen in twee tabellen kan als volgt: (er zijn andere mogelijkheden):
Stap 1: in tblNIetGenormaliseerd moet een veld met unieke waarden (sleutelveld) aanwezig zijn om
later een relatie te kunnen leggen. Indien er nog geen sleutel is, voeg je een nieuw autonummeringsveld toe en maak je van dit veld een sleutelveld. Vermits tabel tblNietGenormaliseerd reeds een
sleutelveld heeft, namelijk Factuurnummer hoef je hier verder niets te doen.
Stap 2: kopieer de tabel tblNietGenormaliseerd (via de actiequery Make Table… en geef het vb. de
naam tblFacturen. In het tabelontwerp van deze nieuwe tabel verwijder je alle kolommen die meerdere malen voorkomen. De tabel aan de éénzijde is nu in orde volgens NV1.
Stap 3: maak in het databasevenster een kopie van de tblNietGenormaliseerd en wijzig de naam in
een gepaste naam, bv. tblBestellingBlok1. Ga naar het tabelontwerp van deze tabel en verwijder alle
kolommen behalve het sleutelveld en het eerste blok van de herhalende gegevens.
Wijzig de instellingen van het sleutelveld (geen sleutelveld, geen autonummering, niet geïndexeerd).
Het ontwerp van de tabel aan de veelzijde is nu in orde.
Stap 4: maak in het databankoverzicht opnieuw een kopie van de hoofdtabel en wijzig de naam in
een gepaste naam, bv. tblBestellingBlok2. Ga naar het tabelontwerp en verwijder alle kolommen
behalve het sleutelveld en het tweede blok van de herhalende gegevens.
Wijzig desnoods de veldnamen in tblBestellingBlok2 zodat ze identiek zijn aan de veldnamen die
gebruikt worden in de tblBestellingBlok1 (dit vergemakkelijkt de te maken toevoegquery maar is niet
noodzakelijk).
Maak een toevoegquery waarin alle overgebleven velden staan en voeg deze records toe aan
tblBestellingBlok1. Voer de query uit.
Herhaal stap vier indien er nog herhalende blokken voorkomen in de hoofdtabel (derde blok, vierde
blok).
Stap 5: verwijder de oorspronkelijke tabel en tblBestellingBlok2 t/m tblBestellingBlok4. Behoud
tblFacturen en tblBestellingBlok1. Wijzig de naam tblBestellingBlok1 in tblBestelling.
Verwijder in tblBestelling de records waarvan enkel het vroegere sleutelveld is ingevuld (met behulp
van een verwijderquery).
Stap 6: leg een relatie tussen de tabellen tblFacturen en tblBestelling.
Hoe moet het nu verder voor tblArtikelen en tblKlanten? Leg alle relaties tussen de 4 tabellen.
Pagina 74
Database-ontwerp
7
Bijlagen
7.1
Bijlage 1. Een bestaande Access-databank converteren
naar een SQL-databank
Je kunt een MS Access database transformeren naar een Microsoft SQL Server database met behulp van de Wizard Upsize van MS Access 2007
Ook in Access 2003 kun je bestaande Access-bestanden converteren naar SQL-databanken.
Open de databank Sportclub.mdb in Microsoft Office 2003. Via de menukeuze Extra, Databasehulpprogramma’s, Wizard Upsize start je de wizard voor deze conversie. Volg deze wizard.
De SQL databank krijgt standaard de naam van de Access-databank gevolgd door SQL.
In onderstaande nota’s wordt de volledige werkwijze voor MS Access 2007 overlopen.
Open in Microsoft Access 2007 de databank Sportclub.mdb. Activeer de menu-optie Hulpmiddelen voor databases en vervolgens SQL Server.
De Wizard Upsize verschijnt.
Kies Nieuwe database maken en klik Volgende.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 75
Zoals je bemerkt krijgt de nieuwe SQL-databank standaard de naam van de Access-databank gevolgd door SQL. Klik op Volgende.
Exporteer alle tabellen en klik op Volgende.
Klik op de knop Volgende.
Pagina 76
Database-ontwerp
Kies Nieuwe Access-client/servertoepassing maken en klik op Volgende.
Met deze laatste twee instellingen vraag je een Access-Project te maken en dat bestand te
openen. In een Access-Project kan een SQL-databank worden aangesproken en bewerkt.
Hierin maakt Access een verbinding met de databank op de SQL Server.
Er volgt nog:
Bij deze convertie komt het vaak voor dat aan het ontwerp van een tabel een veld met naam
upsize_ts van het type TimeStamp wordt toegevoegd. Je kunt dit veld verwijderen omdat het
geen relevante informatie bevat.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 77
Bij geslaagde conversie zie je:
Pagina 78
Database-ontwerp
7.2
Bijlage 2. Korte samenvatting van Sql-commando’s
7.2.1
Selectiequery’s
<SELECT-instructie> ::=
<SELECT-component>
<FROM-component>
[ <WHERE-component> ]
[ <GROUP-BY-component>
[ <HAVING-component> ] ]
[ <ORDER-BY-component> ]
SELECT ...
FROM ...
In de SELECT-component wordt gespecificeerd welke kolommen worden getoond. Elke SELECTinstructie bestaat minstens uit 2 componenten: SELECT en FROM. De andere componenten zijn
optioneel. Na SELECT worden de velden opgesomd, na FROM worden de tabellen vermeld waar de
velden zich bevinden.
SELECT ...
FROM ...
ORDER BY ...
ORDER BY sorteert de rijen op basis van een veld of velden (ASCending of DESCending).
SELECT ...
FROM ...
WHERE ...
WHERE selecteert de rijen met een voorwaarde
SELECT ...
FROM ...
GROUP BY ...
HAVING ...
De GROUP BY-component groepeert de rijen op basis van gelijke waarden in kolommen (velden).
Rijen worden gegroepeerd als ze in de betreffende kolom dezelfde waarde hebben.
De component HAVING is enigszins vergelijkbaar met de WHERE-component. Het verschil is dat de
WHERE-component betrekking heeft op het tussenresultaat van de FROM-component en HAVING
op het gegroepeerd tussenresultaat van de GROUP BY-component. Het effect is wel hetzelfde, ook
in de HAVING-component worden rijen met behulp van een voorwaarde geselecteerd.
De volgorde van de componenten staat vast. Een GROUP BY-component mag nooit vóór een
WHERE- of FROM-component staan en de ORDER BY-component is (bij gebruik) altijd de laatste.
Een HAVING-component mag alleen gebruikt worden als een GROUP BY-component gebruikt
wordt.
VVKSO
Werkgroep Handel
Database-ontwerp - pagina 79
7.2.2
Actiequeries
Een record toevoegen aan de tabel: INSERT INTO
Syntax 1:
INSERT INTO <tabelnaam>
VALUES (<waarde1>, <waarde2>, ...)
Indien je echter niet alle velden direct een waarde wenst te geven dan kun je volgende syntax gebruiken .
Syntax 2:
INSERT INTO <tabelnaam> (<veldnaam1>, <veldnaam2>, ...)
VALUES (<waarde1>, <waarde2>, ...)
De integriteitregels moeten toestaan dat er een aantal velden niet ingevuld worden,
Een nieuwe tabel maken en de inhoud van een andere tabel er in kopiëren: SELECT INTO
Wanneer er gewerkt wordt met autonummeringsvelden (op SQL Server noemt dit type Identity), dan
geef je bij het toevoegen van een nieuw record nooit een waarde mee met dit veld.
SELECT … INTO
FROM
Een record wijzigen in de tabel: UPDATE
De UPDATE-instructie laat toe om met 1 instructie gegevens binnen 1 of meerdere records te wijzigen.
Syntax:
UPDATE <tabelnaam>
SET <veldnaam1> = <waarde1>, <veldnaam2> = <waarde2>, ...
WHERE <voorwaarde>
Deze instructie wordt hoofdzakelijk gebruikt voor back-up doeleinden of om tabellen (al dan niet in
hun geheel) van de ene database naar de andere database te kopiëren. In zijn eenvoudigste vorm
kopieert de instructie dus een volledige tabel.
Een record verwijderen uit een tabel: DELETE FROM
Syntax:
DELETE FROM <tabelnaam>
WHERE <voorwaarde>
Het is mogelijk, mits het aanpassen van de voorwaarde, meerdere records in 1 keer te wissen. Wil
je alle records uit een tabel wissen, laat dan de WHERE-clausule weg.
Pagina 80
Database-ontwerp
7.3
Bijlage 3. Lijst met belangrijkste types
SQL Data Type
Access Data Type/Vb
bigint
Omschrijving
Geheel getal tussen
-9 223 372 036 854 775 808 (-2^63) en
9 223 372 036 854 775 807 (2^63 - 1)
bit
Boolean
0 of 1 (True of False)
money, small money
Currency
Getal in geldeenheid
datetime, smalldatetime
DateTime
Datum en tijd van 1 januari 1753 tot 31 december 9999 (met een nauwkeurigheid van
3.33 milliseconden (datetime)
Datum en tijd van 1 januari 1900 tot 6 juni
2079 met nauwkeurigheid van 1 minuut
(smalldatetime)
float
Single of Double
Vlottende precisie getallen met bereik:
-1.79E + 308 tot -2.23E - 308,
0
en 2.23E + 308 tot 1.79E + 308
int
Long
Geheel getal tussen
-2 147 483 648 (-2^31) en
2 147 483 647 (2^32 - 1)
ncharvar
String (16-bitkarakterset)
String van variabele lengte. De maximale
grootte moet bepaald worden bij het definiëren van de kolom (nvarchar(max))
uniqueidentifier
Guid (ReplicationID)
Sleutel ook wel Globaly Unique Identifier
(Guid) genoemd. Bedoeld om rijen uniek te
identificeren
varbinary
Array van byte
Binaire data zoals afbeeldingen en bestanden
varchar
String (8-bitskarakterset)
String van variabele lengte. De maximale
grootte moet bepaald worden bij het definiëren van de kolom. Maximum is 8000 tekens
tinyint
Byte
Geheel getal van 0 t/m 255
smallint
Integer
Geheel getal tussen -32 768 (-2^15) en
32 767 (2^15 -1)
real
Single
Decimaal getal met bereik: -3.40E + 38 tot
-1.18E - 38, 0 en 1.18E - 38 tot 3.40E + 38
decimal of numeric
Decimal
Decimaal getal met bepaalde nauwkeurigheid
gelegen tussen -10^38 +1 through 10^38 –1
char
Char
Vaste lengte (geen unicode) met maximum
lengte van 8000 tekens
timestamp
VVKSO
Werkgroep Handel
Een uniek getal dat geüpdated wordt telkens
een rij wordt veranderd
Database-ontwerp - pagina 81
text
Variabele lengte (geen unicode) met een
maximum lengte van 2^31 - 1
(2 147 483 647) tekens
image
Binaire gegevens met variabele lengte met
een maximum lengte van 2^31 - 1
(2 147 483 647) bytes
nchar
Gegevens met vaste lengte (unicode) met
een maximum lengte van 4,000 tekens
ntext
Memo
Gegevens met variabele lengte (unicode) met
maximum lengte van 2^30 - 1
(1 073 741 823) tekens
De types ntext, text en image zijn verouderd en zullen in de toekomst wegvallen. Gebruik
als alternatief de types nvartext(MAX), varchar(MAX) en varbinary.
Pagina 82
Database-ontwerp