Vlaamse Dienst voor Arbeidsbemiddeling en Beroepsopleiding
ONTWERP VAN RELATIONELE DATABANKEN Basisprogramma Opleidingen informatica
2/70 Deze cursus is eigendom van de VDAB©
Inhoudsopgave 1.1
Gegevens en informatie .............................................................................6
1.2
Wat is een gegevensverzameling?.............................................................6
2
BESTANDEN................................................................................................... 7 2.1
Bestanden, records en velden ....................................................................7
2.2
Bestandsorganisatie ...................................................................................7
2.2.1
Een sequentiële bestandsorganisatie..................................................7
2.2.2
Een directe bestandsorganisatie .........................................................7
2.2.3
Een index sequentiële bestandsorganisatie ........................................8
2.2.4
Keuze van een organisatievorm ..........................................................8
2.3
Bewerkingen op de gegevens ....................................................................8
2.3.1
Bewerkingen op records......................................................................8
2.3.2
Bewerkingen op de bestanden in hun geheel......................................9
2.4 3
Oefeningen .................................................................................................9 DATABASES EN DATABASE MANAGEMENT SYSTEMEN ..................... 10
3.1
Redundantie en inconsistentie..................................................................10
3.2
Data abstractie .........................................................................................11
3.2.1
Het conceptuele schema ...................................................................12
3.2.2
Het fysieke schema ...........................................................................12
3.2.3
Het externe schema ..........................................................................12
3.2.4
Data abstractie ..................................................................................13
4
DE VERSCHILLENDE STAPPEN BIJ HET ONTWERPEN VAN EEN DATABASE ................................................................................................... 14 4.1
Stap 1: Analyse van de informatiebehoeften ............................................14
4.2
Stap 2: Conceptueel database ontwerp....................................................14
4.3
Stap 3: Logisch database ontwerp ...........................................................14
4.4
Stap 4: Verdere verfijning van het schema ...............................................14
4.5
Stap 5: Fysiek database ontwerp .............................................................15
4.6
Stap 6: Ontwerp van de beveiliging ..........................................................15
4.7
Opmerkingen ............................................................................................15
5
DE INFORMATIEBEHOEFTEN..................................................................... 16 5.1
Hoe bepaal je de informatiebehoeften......................................................16
5.2
Oefeningen ...............................................................................................17 Begrippen rond databases
3/70 5.2.1
Een autoverhuurbedrijf ......................................................................17
5.2.2
Opleidingen bureautica......................................................................18
6
HET ENTITY RELATIONSHIP MODEL......................................................... 21 6.1
Entiteiten ..................................................................................................21
6.2
Entiteittype................................................................................................21
6.3
Attributen ..................................................................................................22
6.3.1
Atomaire attributen. ...........................................................................22
6.3.2
Meerwaardige attributen of repeterende attributen............................22
6.3.3
Samengestelde attributen..................................................................23
6.3.4
Procesattributen ................................................................................23
6.3.5
Het domein van een attribuut ............................................................23
6.4
Primaire sleutel (primary key) ...................................................................23
6.5
Associaties ...............................................................................................24
6.5.1
Kardinaliteit en optionaliteit van een associatie .................................25
6.5.2
Recursieve associaties......................................................................27
6.5.3
Attributen van een associatie ............................................................27
6.6
De voorbeelden samengevat....................................................................28
6.6.1
Beschrijving .......................................................................................28
6.6.2
Entiteiten ...........................................................................................28
6.6.3
Associaties zoeken............................................................................28
6.6.4
Een eerste E/R diagram ....................................................................29
6.6.5
De cardinaliteiten bepalen .................................................................29
6.6.6
Attributen ...........................................................................................30
6.7
Zwakke entiteittypes .................................................................................30
6.8
Hiërarchieën van entiteiten .......................................................................30
6.9
Een entiteit of een attribuut?.....................................................................32
6.10
Oefeningen ...............................................................................................33
6.10.1
Een factuur ........................................................................................33
6.10.2
Associaties, kardinaliteit en optionaliteit ............................................33
6.10.3
Hiërarchische structuren....................................................................34
6.10.4
Recursieve associatie........................................................................34
6.10.5
Ziekenhuis .........................................................................................34
6.10.6
Domein ..............................................................................................34
7
LOGISCH DATABASEONTWERP................................................................ 35 7.1
Het hiërarchische gegevensmodel ...........................................................35
Begrippen rond databases
4/70 7.2
Het netwerk gegevensmodel ....................................................................36
7.3
Het relationeel gegevensmodel ................................................................37
7.3.1
De omzetting van entiteiten en attributen ..........................................37
7.3.2
De omzetting van een 1:n associatie.................................................38
7.3.3
De omzetting van een m:n associatie................................................39
7.3.4
De omzetting van een associatie met eigen attributen ......................39
7.3.5
Integriteit van de gegevens ...............................................................40
7.4
Oefeningen ...............................................................................................41
7.4.1
Film ...................................................................................................41
7.4.2
Bibliotheek.........................................................................................41
8
VERDERE VERFIJNING VAN HET SCHEMA: NORMALISATIE................. 42 8.1
Problemen met redundantie en inconsistentie..........................................42
8.1.1
Een illustratie.....................................................................................42
8.1.2
Problemen .........................................................................................42
8.1.3
Gevolgen ...........................................................................................42
8.2
De normalisatieprocedure ........................................................................43
8.2.1
Voorbereiding tot normalisatie...........................................................43
8.2.2
Eerste normalisatiestap .....................................................................46
8.2.3
Tweede normalisatiestap...................................................................47
8.2.4
Derde normalisatiestap......................................................................48
8.3
Een tweede voorbeeld, een offerte...........................................................49
8.3.1
Na de eerste normalisatiestap...........................................................50
8.3.2
Na de tweede normalisatiestap .........................................................50
8.3.3
Na de derde normalisatiestap............................................................50
8.4
Integratie van genormaliseerde gegevensgroepen...................................51
8.4.1
Homoniemen en synoniemen............................................................51
8.4.2
Samenvoegen van entiteiten uit de verschillende informatiebehoeften 51
8.4.3
Definitieve naamgeving en samenstelling .........................................52
8.5
Bepalen van de relaties ............................................................................52
8.6
Oefeningen ...............................................................................................54
8.6.1
Projectbeheer ....................................................................................54
8.6.2
Ziekenfonds.......................................................................................54
8.6.3
Bestellingen en pickinglist .................................................................54
8.6.4
Distributiebedrijf van kantoorbenodigheden ......................................55
8.6.5
Overboekingen en rekeningoverzichten ............................................57 Begrippen rond databases
5/70 9
HET FYSIEKE SCHEMA ............................................................................... 58 9.1
Toegangspaden........................................................................................58
9.2
Indexen.....................................................................................................59
9.2.1
Wat is een index? ..............................................................................59
9.2.2
Welke relaties krijgen een index?......................................................60
9.2.3
Welke attributen krijgen een index? ..................................................60
9.3 10
Oefeningen ...............................................................................................60 DE ROL VAN SQL......................................................................................... 61
10.1
Wat is SQL? .............................................................................................61
10.2
Onderdelen van SQL................................................................................61
10.2.1
De data definition language (DDL) ....................................................61
10.2.2
De data manipulation language (DML) ..............................................62
10.2.3
Embedded en dynamische SQL........................................................62
10.2.4
Triggers .............................................................................................62
10.2.5
Opdrachten die met beveiliging te maken hebben ............................63
10.2.6
Opdrachten i.v.m. transaction management......................................63
10.2.7
Opdrachten rond een client server architectuur.................................63
11
AANDACHTSPUNTEN BIJ HET GEBRUIK VAN EEN DBMS ..................... 64
11.1
Beveiliging ................................................................................................64
11.1.1
DAC...................................................................................................64
11.1.2
MAC ..................................................................................................65
11.2
Gelijktijdige benadering van gegevens .....................................................65
11.3
Transactiebeheer......................................................................................65
11.3.1
Wat is een transactie .........................................................................65
11.3.2
ACID..................................................................................................66
11.3.3
Technieken om ACID te ondersteunen .............................................67
12
EVOLUTIES................................................................................................... 68
12.1
12.1.1
De architectuur ..................................................................................68
12.1.2
XML ...................................................................................................68
12.1.3
Tekstdocumenten..............................................................................69
12.2 13
Web databases.........................................................................................68
Object database systemen .......................................................................69 COLOFON ..................................................................................................... 70
INLEIDING Begrippen rond databases
6/70
1.1
Gegevens en informatie
Wil een bedrijf op een behoorlijke manier functioneren, dan moet het over de nodige informatie kunnen beschikken. Aan communicatiemiddelen om aan gegevens te geraken is er vandaag de dag geen gebrek, maar de kunst bestaat erin binnen die gegevens op tijd correcte informatie te vinden. Om gegevens om te zetten in informatie is een interpretatie nodig. Dit creëert een behoefte aan hulpmiddelen om gegevens op te slaan en te beheren en om op een snelle en efficiënte manier nuttige informatie uit die gegevens te destilleren. In de praktijk bestaat er een groot verschil tussen de manier waarop een werknemer in een bedrijf de gegevens waarneemt en de manier waarop ze uiteindelijk opgeslagen worden in het informatiesysteem. Deze cursus licht toe hoe gegevens, zoals we die waarnemen in de werkelijke wereld, kunnen omgezet worden in gegevens die opgeslagen kunnen worden in een computer en hoe die op een efficiënte manier kunnen geraadpleegd worden.
1.2
Wat is een gegevensverzameling?
Een gegevensverzameling bevat een aantal items (getallen, tekst, codes, tekeningen…) die logisch gezien bij elkaar horen. Een manager van een groot bedrijf moet veel vergaderen, regelmatig op zakenreis, enz. Om de afspraken bij te houden noteert hij die in een agenda. De agenda is een gegevensverzameling. De secretaris van een vereniging moet naar aanleiding van elke activiteit uitnodigingen versturen naar de verschillende leden van de vereniging. De lijst met de adresgegevens van alle leden is een gegevensverzameling. Een kruidenier houdt zijn kosten en inkomsten bij in een kasboek. Het kasboek is een gegevensverzameling.
Begrippen rond databases
7/70
2 BESTANDEN 2.1
Bestanden, records en velden
Oorspronkelijk werd een gegevensverzameling opgeslagen in een computer, in de vorm van een bestand. De gegevens over de klanten van een firma komen dan b.v. in een bestand klanten. Per klant zijn daarin de naam, de voornaam, het adres, de postcode en de woonplaats terug te vinden. Men noemt in deze context elke klant een record en elk stukje informatie over een klant zoals b.v. zijn woonplaats een veld. Elk record van één bestand is opgebouwd uit dezelfde velden. Elk veld heeft een vaste lengte, zo kunnen er b.v. in elk record van het bestand klanten 50 karakters voorzien zijn voor de naam van de klant, 4 karakters voor de postcode, enz. Hoe gegevens over een bepaalde klant kunnen opgevraagd worden, hangt samen met de bestandsorganisatie.
2.2
Bestandsorganisatie
2.2.1 Een sequentiële bestandsorganisatie In een sequentiële bestandsorganisatie zijn alle records na elkaar opgeslagen en kunnen alleen opgevraagd worden in de volgorde waarin ze opgeslagen zijn. Concreet betekent dit dat om een record te vinden, het bestand record per record moet doorlopen worden tot aan het gewenste record. Onderhoud van de data gebeurt als volgt: •
Nieuwe records komen altijd achteraan
•
Verwijderde records veroorzaken een gat in het bestand.
•
Een record wijzigen kan alleen door het bestand over te schrijven naar een nieuw bestand tot aan het te wijzigen record, dan de bijgewerkte gegevens voor het te wijzigen record toe te voegen aan het nieuwe bestand en daarna het vervolg van het oorspronkelijke bestand verder over te schrijven naar het nieuwe bestand. Ten slotte krijgt het nieuwe bestand de naam van het oorspronkelijke bestand.
2.2.2 Een directe bestandsorganisatie Bij een directe bestandsorganisatie bestaat er een direct verband tussen de plaats van een record op het opslagmedium en één van de velden van het record. Dit veld moet het record uniek kunnen identificeren en wordt de sleutel van het record genoemd. Een directe bestandsorganisatie is alleen te realiseren op een adresseerbaar geheugenmedium zoals een magneetschijf. Het verband tussen de sleutel en de fysieke plaats van het record wordt berekend op basis van een hashfunctie. Als twee verschillende sleutels eenzelfde resultaat voor die hashfunctie opleveren wordt een collisionfunctie gebruikt om een nieuwe locatie te bepalen. Een record wordt onmiddellijk teruggevonden op basis van zijn sleutelwaarde Begrippen rond databases
8/70 Op het gebied van onderhoud betekent dit: •
Een nieuw record wordt geplaatst op basis van de waarde van zijn sleutel
•
De plaats van een gewist record wordt onmiddellijk vrijgegeven.
2.2.3 Een index sequentiële bestandsorganisatie Een index sequentieel bestand combineert de mogelijkheden van een sequentieel en van een direct bestand. Het bestand kan sequentieel doorlopen worden, records kunnen ook rechtstreeks teruggevonden worden op basis van een sleutelwaarde. Deze bestandsorganisatie biedt ook de mogelijkheid een record op te sporen op basis van een bepaalde sleutel en daarna het bestand verder sequentieel te doorlopen.
2.2.4 Keuze van een organisatievorm De keuze van de organisatievorm wordt mee bepaald door •
De omvang van de gegevensverzameling
•
Het gebruik van de gegevensverzameling (invoerbestand, uitvoerbestand, invoer/uitvoerbestand, procesbestand)
•
Het opslagmedium (tape, schijf)
2.3
Bewerkingen op de gegevens
De gegevens moeten niet alleen opgeslagen worden in de computer. Er zal ook de nodige programmatuur nodig zijn om met de gegevens te werken. Deze programmatuur wordt zuiver in functie van het bestand in kwestie geschreven. Het programma benadert het bestand als een opeenvolgende reeks tekens en brengt zelf structuur in deze tekens. Dit maakt dat gegevensbestand en programma onlosmakelijk met elkaar verbonden zijn.
2.3.1 Bewerkingen op records 2.3.1.1
Toevoegen
Gegevens moeten ingevoerd en opgeslagen worden. 2.3.1.2
Raadplegen
Bepaalde gegevens moeten opgezocht en getoond worden. 2.3.1.3
Onderhouden
De gegevens moeten up to date en betrouwbaar gehouden worden. •
Nieuwe gegevens moeten ingevoerd worden
•
Bestaande gegevens moeten aangepast worden
•
Verouderde gegevens moeten verwijderd worden
Begrippen rond databases
9/70
2.3.2 Bewerkingen op de bestanden in hun geheel 2.3.2.1
Tonen
De volledige inhoud van het bestand tonen op een gebruikersvriendelijke manier. 2.3.2.2
Sorteren
De volledige inhoud van het bestand tonen in een bepaalde volgorde: b.v. een bestand met gegevens over klanten, alfabetisch op naam van de klant of een bestand met gegevens over personeelsleden in volgorde van indiensttreding, enz. 2.3.2.3
Samenvoegen
Soms moeten de gegevens uit twee bestanden samengevoegd worden. Dit kan door de bestanden gewoon na elkaar te plaatsen. Men spreekt van concatenatie. De volgorde van de records in het samengevoegde bestand kan ook bepaald worden op basis van de inhoud van een bepaald veld. Men spreekt van mergen. 2.3.2.4
Kopiëren
Dit gebeurt vooral in het kader van het maken van reservebestanden uit veiligheidsoverwegingen. 2.3.2.5
Reorganiseren
Afhankelijk van de bestandsorganisatie kunnen er nadat een aantal toevoegingen en verwijderingen van records gebeurd zijn, gaten ontstaan in het bestand. Al die gaten nemen extra opslagruimte in beslag. Het bestand wordt bij een reorganisatie herschreven op een ander opslagmedium en eventueel teruggeplaatst. Op die manier wordt het bestand terug een geheel. 2.3.2.6
Verwijderen
Bestanden die niet meer nodig zijn verwijderen om geheugenruimte te winnen.
2.4
Oefeningen
1. Noem enkele gegevensverzamelingen die je zou kunnen tegenkomen in een autoverhuurbedrijf. 2. Een bestand wordt opgeslagen op tape. Welke bestandsorganisaties komt hiervoor in aanmerking. 3. In een bibliotheek wordt een lijst bijgehouden van de aangekochte boeken. Geef een voorbeeld van een record dat in die lijst zou kunnen voorkomen. Bedenk ook geschikte veldnamen. 4. Inschrijvingen voor opleidingen i.v.m. office pakketten moeten geregistreerd worden. Hoe zou een record er kunnen uitzien? Bedenk geschikte veldnamen.
Begrippen rond databases
10/70
3 DATABASES EN DATABASE MANAGEMENT SYSTEMEN Een andere manier om een gegevensverzameling op te slaan in een computer is in de vorm van een database. Een database is een verzameling gegevens die je op een gestructureerde manier kunt benaderen. Denk aan een grote archiefkast waarin alles netjes is opgeborgen, zodat je een bepaald dossier gemakkelijk kunt vinden. Belangrijk is dat de gegevens in een database zodanig zijn opgeslagen dat deze gegevens optimaal doorzoekbaar zijn. Een database management systeem of DBMS is software die het mogelijk maakt de gegevens in een database te onderhouden (invoeren van nieuwe gegevens, verwijderen van verouderde gegevens, wijzigen van bestaande gegevens) en de gewenste informatie op een efficiënte en snelle manier op te vragen.
3.1
Redundantie en inconsistentie
Een belangrijk kenmerk van een database is dat een database geïntegreerd is. Dit betekent dat de gegevens in een database door meerdere programma’s kunnen gebruikt worden. Dit staat in contrast met het opslaan van gegevens in bestanden. Bij een klassieke verwerking van gegevens via bestanden wordt per toepassing een aangepast gegevensbestand gedefinieerd. Een voorbeeld: Het secretariaat in een opleidingscentrum moet een overzicht kunnen maken van cursisten en hun behaalde resultaten. Een bestand wordt aangemaakt met deze gegevens evenals bijbehorende software om een overzicht van de resultaten af te drukken en nieuwe cursisten en resultaten toe te voegen. Bij de keuze van de bestandsorganisatie wordt rekening gehouden met de manier waarop het bestand zal gebruikt worden. De boekhouding van het centrum die het betalen van de inschrijvingsgelden opvolgt heeft dan weer een bestand nodig met daarin de gegevens van de cursisten, hoeveel het inschrijvingsgeld bedraagt en of het al betaald is. De boekhouding wil overzichten kunnen maken van het bedrag dat de cursisten moeten betalen en welke cursisten nog moeten betalen. Ook hier wordt een aangepaste bestandsorganisatie gekozen en aangepaste programmatuur geschreven. Zowel het secretariaat als de boekhouding slaan data i.v.m. cursisten op. Allebei hebben ze enerzijds informatie nodig over de cursisten die de andere afdeling ook nodig heeft (naam en adresgegevens) en anderzijds informatie die de andere afdeling niet nodig heeft (secretariaat: resultaten, boekhouding: te betalen bedragen). Als deze gegevens in een database worden opgeslagen, wordt elk gegeven slechts één keer opgeslagen. De naam en adresgegevens zullen dus slechts één keer opgeslagen worden. De software haalt voor de verschillende gebruikers aangepaste informatie op. Dit voorkomt dat gegevens dubbel opgeslagen worden of anders gezegd, dit voorkomt redundantie. Begrippen rond databases
11/70 Redundantie veroorzaakt extra werk, vraagt meer geheugenruimte, en geeft bovendien aanleiding tot fouten. Immers, als gegevens veranderen moeten de wijzigingen niet op één plaats doorgevoerd worden, maar op elke locatie waar diezelfde gegevens opgeslagen zijn. Zo moet een adreswijziging van een cursist in de hierboven beschreven situatie met bestanden, niet alleen doorgevoerd worden in het bestand van het secretariaat, maar ook nog eens in het bestand van de boekhouding. Als een aanpassing niet doorgegeven of vergeten wordt op één van beide afdelingen, geeft dat aanleiding tot tegenstrijdige of inconsistente gegevens. Databases brengen dan weer andere problemen mee. Omdat de gegevens slechts één keer opgeslagen zijn, krijgt elke gebruiker te maken met dezelfde gegevensstructuur. Elke gebruiker wordt er bijvoorbeeld mee geconfronteerd dat het afleveradres van klanten genoteerd wordt, ook die afdelingen die geen boodschap hebben aan het afleveradres. Om dit te vermijden, krijgen gebruikers geen directe toegang tot de gegevens in een database. Zij kunnen de gegevens alleen benaderen via een database management systeem. Het DBMS moet ervoor zorgen dat elke gebruiker de opgeslagen gegevens op een voor hem nuttige en efficiënte manier kan bekijken.
3.2
Data abstractie
Data abstractie heeft te maken met een strikte scheiding tussen de programmatuur en het opslaan van de gegevens. Bij het opslaan van gegevens in bestanden wordt de structuur van de bestanden ingebed in het programma. Dit betekent dat een wijziging aan de structuur van een gegevensbestand meteen ook een aanpassing van de bijbehorende programmatuur vraagt. De structuur van een database is echter opgeslagen in de database zelf en bijgevolg volledig gescheiden van de software die de toegang tot de gegevens verzorgt. We zeggen dat de data en de programmatuur onafhankelijk zijn. Om die onafhankelijkheid tussen programmatuur en data te bereiken worden de gegevens in een DBMS op drie verschillende niveaus van abstractie beschreven: het conceptuele, het fysieke en het externe schema.
Begrippen rond databases
12/70
extern schema
extern schema
extern schema
Conceptueel schema
Fysiek schema
Het externe en het conceptuele schema worden gedefinieerd via een DDL (data definition language).
3.2.1 Het conceptuele schema Het conceptuele schema, ook wel het logische schema genoemd, beschrijft de gegevens in functie van het datamodel (zie ook Hoofdstuk 7: Logisch databaseontwerp). Het bepaalt welke gegevens voor de hele organisatie opgeslagen worden en hoe ze gestructureerd worden. In een relationeel model zou dit er voor cursisten die zich inschrijven voor bepaalde opleidingen als volgt kunnen uitzien: Cursist(cursistid:string, naam:string, geboortedatum:datum) Opleiding(opleidingid:string, naam:string, aantal uur: integer) Inschrijving(cursistid:string, opleidingid:string)
3.2.2 Het fysieke schema Het fysieke schema beschrijft hoe de relaties beschreven in het conceptuele schema effectief opgeslagen zullen worden. Welke toegangspaden moeten gevolgd worden om tot de gewenste informatie te komen? Welke indexen kunnen de verwerking van een bevraging bevorderen? Kan het clusteren van gegevens (het fysiek bij elkaar opslaan van gegevens uit gerelateerde tabellen) de prestaties verbeteren?
3.2.3 Het externe schema Dank zij de externe schema’s wordt de toegang tot de data aangepast aan de individuele gebruikers. Elke database heeft maar één conceptueel schema en één fysiek schema, maar meerdere externe schema’s. Elk extern schema is aangepast aan een bepaalde groep gebruikers.
Begrippen rond databases
13/70 Een extern schema zal uit een aantal views en relaties bestaan gebaseerd op het conceptuele schema, maar de records in de views worden niet opgeslagen. Alleen de definitie van een view wordt opgeslagen, het resultaat wordt telkens opnieuw bepaald. Dit voorkomt redundantie en de bijbehorende mogelijke inconsistenties en zorgt er ook voor dat wijzigingen aan gegevens meteen merkbaar zijn in de resultaten van een view.
3.2.4 Data abstractie De verschillende hierboven beschreven schema’s schermen de programmatuur af van wijzigingen in de structuur van de gegevens en de manier waarop ze opgeslagen worden. De externe schema’s kunnen wijzigingen in het conceptuele schema opvangen. Men spreekt van logische data onafhankelijkheid. Het conceptuele schema kan dan weer wijzigingen in het fysieke schema opvangen. Men spreekt van fysieke data onafhankelijkheid. Gezien de voordelen van het gebruik van databases t.o.v. het werken met bestanden, zal in het vervolg van de cursus vooral bekeken worden hoe gegevens gestructureerd worden om ze op te slaan in een database. Dit is trouwens ook de opslagvorm die tegenwoordig het meest gebruikt wordt.
Begrippen rond databases
14/70
4 DE VERSCHILLENDE STAPPEN BIJ HET ONTWERPEN VAN EEN DATABASE Welke verschillende stappen worden doorlopen om gegevens zoals ze waargenomen worden in het dagelijkse leven om te zetten naar gegevens die kunnen opgeslagen worden in een database en die kunnen beheerd worden met een DBMS?
4.1
Stap 1: Analyse van de informatiebehoeften
Een eerste stap bij het ontwerpen van een database bestaat erin na te gaan wat een gebruiker verwacht van de database. De gebruiker beschrijft op een informele manier welke informatie hij via de database wil kunnen verkrijgen. Een vertrekpunt kan de huidige situatie zijn met daaraan gekoppeld welke veranderingen (verbeteringen) verwacht worden en welke nieuwe elementen moeten toegevoegd worden. Ook overzichten en formulieren die de gebruiker hanteert kunnen bijdragen tot een bepaling van de informatiebehoeften. Hieruit leidt de ontwerper af welke gegevens zullen opgeslagen worden, waar die gegevens vandaan kunnen komen en welke toepassingen moeten ontwikkeld worden om de gewenste informatie op te vragen. Er wordt ook al nagegaan welk type operatie het meest van toepassing zal zijn in de database (gegevens aanpassen, informatie opvragen, …) en welke prestaties er verwacht worden (om welke hoeveelheden data gaat het?, binnen welke tijdspanne moet de informatie ter beschikking zijn?)
4.2
Stap 2: Conceptueel database ontwerp
De informatie, verzameld in de vorige stap wordt omgezet in een meer formele beschrijving van de gegevens: welke gegevens zullen opgeslagen worden, aan welke voorwaarden moeten die gegevens voldoen,… Een manier van voorstellen die hierbij dikwijls gebruikt wordt, is het ER model (entity relationship model).
4.3
Stap 3: Logisch database ontwerp
Er wordt een DBMS gekozen om het ontwerp te implementeren. Het conceptuele model wordt vertaald in een database schema dat past in het datamodel van het gekozen DBMS. Momenteel is een relationeel database model erg populair en daar wordt in deze cursus dan ook het meest uitgebreid op in gegaan. Andere modellen die in het verleden nogal gebruikt werden zijn het hiërarchische model en het netwerkmodel. Tegenwoordig komen aansluitend bij de evolutie naar programmeren in object georiënteerde talen ook meer en meer object georiënteerde databases en object relationele database systemen in gebruik.
4.4
Stap 4: Verdere verfijning van het schema
In deze verdere verfijning worden de relaties in het schema bepaald en wordt ook nagegaan of het schema geen problemen kan opleveren zoals redundantie, inconsistenties … Daar waar de vorige stappen eerder op een intuïtieve basis Begrippen rond databases
15/70 werden uitgevoerd, bestaat voor deze stap een vaste techniek, normaliseren genoemd.
4.5
Stap 5: Fysiek database ontwerp
In deze stap worden nog verdere wijzigingen doorgevoerd zoals het aanmaken van indexen, eventueel het samenvoegen van tabellen,… om de belasting van het systeem te minimaliseren en de prestaties te maximaliseren.
4.6
Stap 6: Ontwerp van de beveiliging
Ten slotte worden verschillende types gebruikers gedefinieerd op basis van wat ze zullen doen met de database. Per type gebruiker wordt bepaald tot welk deel van de database het toegang moet krijgen en tot welk deel van de database het geen toegang mag hebben.
4.7
Opmerkingen
In de praktijk zal bij het ontwerp van een database dikwijls wel gestart worden met het uitvoeren van deze zes stappen. Het resultaat zal echter telkens opnieuw moeten getoetst worden aan de eisen van de gebruikers en aan de realiteit. Dit kan betekenen dat een aantal stappen opnieuw moeten bekeken worden. Ook het ontwikkelen van de applicaties in het DBMS kan aanleiding geven tot het opnieuw uitvoeren van een aantal stappen. In dit hoofdstuk werd een algemeen overzicht van de verschillende stappen gegeven. In de volgende hoofdstukken komen deze stappen wat uitgebreider aan bod.
Begrippen rond databases
16/70
5 DE INFORMATIEBEHOEFTEN 5.1
Hoe bepaal je de informatiebehoeften
De informatiebehoeften bepalen gebeurt grotendeels op basis van gesprekken met de klant. Daaruit moet blijken welke gegevens er ter beschikking zijn en hoe die gegevens bekomen worden en ook welke overzichten, rapporten op basis van die gegevens moeten opgemaakt worden. Bij het bepalen van de informatiebehoeften gaan we uit van de formulieren, rapporten en lijsten die binnen het bedrijf gebruikt worden. Een voorbeeld In een bedrijf worden de bestellingen van klanten geregistreerd op een bestelbon. Bestelbonnummer: Klantnummer: Klantnaam: Adres:
165
Datum:
Tel.:
98765 Piet Pieters Pieperseweg 10 9876 - Bommerskonte 09/876 54 32
Leveringsdatum:
04/05/1997
Artikelnr 735 856 1142
Omschrijving Keizerskroon - rood Klimroos - Swan Lake Dahlia's
24/04/1997
Aantal 20 15 30
Eenheidsprijs 3 10 5
Op basis van die bestelbonnen worden voor het magazijn pickinglists gemaakt. De magazijnier gebruikt die pickinglists om de bestellingen van de klanten klaar te maken.
Begrippen rond databases
17/70 Pickinglistnummer :
165
Leveringsdatum :
04/05/1997
Klantgegevens Nr. Naam 98765 Piet Pieters
Artikelgegevens Nr. Omschrijving 735 Keizerskroon - rood 856 Klimroos - Swan Lake 1142 Dahlia's
TOTAAL 12345 Jans Janssen
530 680 736 1142 1302
Krokussen - gemengd Tulpen - botanische Keizerskroon - geel Dahlia's Gladiolen
TOTAAL
Aantal 20 15 30 65 50 35 5 50 50 190
Uit deze beschrijving komen twee informatiebehoeften naar voren: Bestelbonnen Picking lists
5.2
Oefeningen
5.2.1 Een autoverhuurbedrijf Een autoverhuurbedrijf met verschillende filialen verspreid over heel België, wil overgaan tot automatisering van zijn gegevens. Naar aanleiding van elke verhuring wordt een huurovereenkomst afgesloten. Elke huurovereenkomst krijgt een uniek nummer. Uit de huurovereenkomst blijkt o.a. in welk filiaal de auto gehuurd werd, welke auto er juist gehuurd werd, wie de auto huurt en over welk rijbewijs die persoon beschikt, op welke datum de reservatie gebeurde, gedurende welke periode de huurovereenkomst geldig is, in welk filiaal de auto terug binnen gebracht wordt en wanneer.
Begrippen rond databases
18/70 AUTOVERHUUR HUUROVEREENKOMST 6956798 Datum reservering: 05-09-2001 Filiaalgegevens:
Klantnummer: 0078 Adres: Steenstraat 5, 2018 Antwerpen Telefoon: 03 111 22 33 Nummer rijbewijs: K 866 065 Model: Opel Corsa Te huren van: 07-09-2006 Afhaaltijd na 08.00 uur Te betalen: € 225,Terugbrengen in filiaal: 53 Tankinhoud: Vol
Filiaal ID: 56 J. Janssens Dorpsstraat 55 2140 Borgerhout Klantnaam: Peeters
Categorie: ABE Kenteken: AAA123 tot en met: 09-09-2006 Terugbrengtijd voor 19.00 uur Terugbrengstatus: op tijd Kilometerstand: 345678
Bij een reservatie wordt nagegaan aan de hand van een vlootlijst welke auto’s er ter beschikking zijn en hoeveel de huurprijs bedraagt. AUTOVERHUUR VLOOTOVERZICHT FILIAAL: 56 Leenautonr 1 7 16 17
Model Opel Astra Renault 88 Ford Kia Ford Kia
Kenteken ABC123 DEF456 GHI789 ABC234
Onderhoud 12500 15000 0 0
Verhuur Uitgeleend Op Voorraad In onderhoud Op voorraad
Dagtarief € 75 € 80 € 90 € 90
Op regelmatige basis organiseert het bedrijf een reclamecampagne en stuurt dan een reclamefolder naar al zijn klanten met een overzicht van de promoties en de periode waarin ze van toepassing zijn. Bepaal de informatiebehoeften van dit autoverhuurbedrijf.
5.2.2 Opleidingen bureautica Bepaal de informatiebehoeften voor de hieronder beschreven situatie: De VDAB heeft een opleidingsdienst voor eigen personeel. Elk jaar krijgen alle personeelsleden een brochure waarin de opleidingen Bureautica staan die zij kunnen volgen. De personeelsleden krijgen ook een inschrijvingsformulier waarop zij de opleidingen kunnen noteren die ze wensen te volgen. Zij moeten daarop naast hun persoonsgegevens, de inschrijvingsdatum, de afdeling waarop ze werken en de locatie waar ze werken opgeven voor welke opleidingen zij zich inschrijven. Hieronder een voorbeeld van dergelijk inschrijvingsformulier.
Begrippen rond databases
19/70 Inschrijvingsformulier Bureautica opleidingen voor VDAB personeel Persoonsgegevens: Naam:
Coppens
Inschrijvingsdatum:
Voornaam:
Robin
15/2/2006
Adres:
Dorp 6
Postcode:
1745
Gemeente:
Opwijk Vestigingsgegevens:
Afdeling: Administratie
Training & Opleiding Bouw
Verantwoordelijke: De Paepe
Adres: Meulebroekstraat 52 Postcode: 9220 Gemeente: Hamme (O.-Vl.)
Ik wens mij in te schrijven voor volgende opleidingen: Opleidingscode:
Opleidingsnaam:
5
Inleiding Word
8
Vervolmaking Word
9
PowerPoint
Het inschrijvingsformulier bezorgen ze aan de opleidingsdienst. De opleidingsdienst stelt een planning samen op basis van de binnengekomen inschrijvingsformulieren. Uit deze planning blijkt waar en wanneer een bepaalde opleiding kan doorgaan, wie er ingeschreven is voor de opleiding en of die kandidaten al uitgenodigd zijn, al bevestigd of geannuleerd hebben of gevraagd hebben de inschrijving te verplaatsen naar een latere datum.
Begrippen rond databases
20/70 Planning opleidingen voor VDAB personeel Opleidingscode: 5 Opleidingsnaam: Inleiding Word Sessie: 1 Datums: 13/10/2003 - 14/10/2003 - 20/10/2003 Deze opleiding gaat door te: Training & Opleiding Bureautica/Informatica F.Rooseveltplaats 12 2000 Antwerpen Uitgenodigd (U) - Bevestigd (B) - Annulatie (A) – Later(L) Pnr. 6 17 58 32 50 29 76 91 83 81
Naam Boelens Danny Coppens Robin Daelmans Karolien De Ridder Francine Delens Marc Lissens Anne Meert Piet Plasmans Erwin Van Der Straeten Verhaeghen An
Adres Gravenstraat 23 Dorp 6 Hoge Akker 19 St Janstraat 47 Populierenlaan Kerkstraat 2 Oosthoek 23 Binneweg 2 Zeedijk 37 Straat 5
PN 9240 1745 9200 1785 9200 9200 9240 9255 8670 9200
Gemeente Zele Opwijk Dendermon Merchtem Dendermon Dendermon Zele Buggenhout Koksijde Dendermon
U X X X X X X X X X X
B X X X X X X X
A
L
X
X
De personeelsleden krijgen vervolgens een uitnodiging voor de opleiding. In deze brief wordt de inschrijving bevestigd en gemeld op welke dagen en op welke locatie de opleiding doorgaat. Onderaan de brief zit een antwoordstrookje waarmee ze hun deelname kunnen bevestigen of annuleren. Op de laatste dag van de opleiding krijgen de personeelsleden ook nog een evaluatieblad waarop zij een beoordeling over de opleiding geven. De brochure die de personeelsleden krijgen en de inschrijvingsformulieren wil de opdrachtgever behouden. Het is niet de bedoeling dat de personeelsleden inschrijven voor een opleiding via Internet. Niet alle personeelsleden hebben immers een PC. Het opzet is hier dat je de inschrijvingsformulieren invoert in de PC. Het plannen van opleidingen, de uitnodigingen, aanwezigheidslijsten en evaluatieformulieren zou dan geautomatiseerd kunnen gebeuren.
Begrippen rond databases
21/70
6 HET ENTITY RELATIONSHIP MODEL In paragraaf 4.2 werd het entity relationship model (ER model) aangehaald als een methode om de op een informele manier bekomen informatiebehoeften in een meer formeel kleedje te gieten. Het resultaat is een gemakkelijk begrijpbaar diagram dat ter ondersteuning van de communicatie met de klant kan gebruikt worden. Een bijkomend voordeel is dat het diagram op een eenvoudige manier om te zetten is in een relationele database. Het entity relationship (E/R) model bekijkt de echte wereld als een constructie van entiteiten (objecten) en associaties (relaties) tussen die entiteiten.
6.1
Entiteiten
Een entiteitwaarde is een betekenisvolle eenheid in een informatiebehoefte. Een entiteitwaarde komt overeen met een object in de beschreven omgeving, een object dat moet te onderscheiden zijn van andere objecten en waarover informatie moet bijgehouden worden. Zo komt een mier in een mierenhoop niet in aanmerking als entiteit als we de ene mier niet van een andere kunnen onderscheiden. Binnen een bedrijf kan een bepaalde werknemer wel overeen komen met een entiteit. Over een entiteit worden gegevens opgeslagen. Voor een werknemer kan dat zijn: 12002, Peeters, Jan, Dreef 85 2000 Antwerpen, 1 mei 1998, inkoop. De opgesomde data worden de attribuutwaarden van de entiteitwaarde genoemd.
6.2
Entiteittype
Dikwijls is er in een omgeving sprake van een verzameling gelijkaardige entiteiten, dat wordt dan een entiteittype genoemd. We onderscheiden vijf soorten entiteittypes: rollen (docent, werknemer), gebeurtenissen (cursus, vergadering), locaties (leslokaal), voorwerpen (computer, boek) en concepten (organisatie, geldmarkt). Gelijkaardig betekent in deze context entiteiten waarvan we dezelfde attributen willen opslaan. Elk entiteittype krijgt een naam, b.v. werknemer. Jan Peeters uit ons voorbeeld wordt dan een entiteitwaarde of entiteit van het entiteittype werknemer. Er bestaan geen uniforme afspraken i.v.m. het voorstellen van entiteiten in een E/R diagram. Een mogelijke voorstelling (die aansluit bij de UML notatie) van een entiteittype in een E/R diagram:
Begrippen rond databases
22/70 werknemer persnr achternaam voornaam adres afdeling in dienst Indien het entiteittype voorkomt in een context waar de attributen minder belangrijk zijn, kan je ook een verkorte voorstelling gebruiken. werknemer
6.3
Attributen
De gegevens die i.v.m. een entiteit worden opgeslagen, worden attributen genoemd. Er bestaat een grote diversiteit aan attributen in een E/R gegevensmodel. Naast de naam van het attribuut kan eventueel ook het type vermeld worden. werknemer persnr: integer achternaam: string voornaam: string straat: string afdeling: string in dienst: datum
6.3.1 Atomaire attributen. Een atomair attribuut van een entiteit kan per entiteit ten hoogste één enkele waarde hebben. Zo is achternaam een atomair attribuut van een werknemer omdat elke werknemer ten hoogste één achternaam heeft en een achternaam niet verder op te delen is. In een E/R diagram wordt ervan uitgegaan dat een attribuut atomair is als er geen verdere aanduiding bijkomt.
6.3.2 Meerwaardige attributen of repeterende attributen In tegenstelling tot atomaire attributen zijn er ook repeterende attributen: zij kunnen per entiteit meerdere waarden krijgen. Zo kan een werknemer deelnemen aan meerdere projecten binnen een bedrijf. Project wordt dan een repeterend attribuut.
Begrippen rond databases
23/70 werknemer Achternaam Project [0…*] Achternaam is een atomair attribuut, project is een meerwaardig attribuut, elke werknemer kan betrokken zijn bij meerdere projecten met een minimale waarde gelijk aan 0. Dit betekent dat niet elke werknemer moet deelnemen aan één of ander project. Als een hele groep attributen repetitief is, dan spreken we van een repeterende groep.
6.3.3 Samengestelde attributen Een samengesteld attribuut koppelt aan een entiteit een lijst van waarden. Die lijst heeft een bepaalde volgorde en lengte. De adresgegevens van een werknemer is een voorbeeld van een samengesteld attribuut omdat het op zijn beurt uit vier waarden bestaat, nl. straat, huisnummer, postcode, woonplaats. werknemer
adres
Achternaam: string Adresgegevens: adres Project [0…*]: string
straat:string huisnummer: integer postcode gemeente
6.3.4 Procesattributen De waarde van een procesattribuut is het resultaat van een berekening. Zo is de anciënniteit van een werknemer een procesgegeven. Zij kan immers berekend worden op basis van de datum van indiensttreding. In de meeste gevallen zal een procesattribuut niet effectief opgenomen worden in de database.
6.3.5 Het domein van een attribuut Bij elk attribuut hoort een verzameling mogelijke waarden voor dat attribuut. Dit wordt het domein van het attribuut genoemd. Voor de datum van indiensttreding kan het domein b.v. beperkt worden tot alle geldige datums die vallen tussen de datum van oprichting van het bedrijf en de datum van vandaag.
6.4
Primaire sleutel (primary key)
Een sleutel is een minimale verzameling attributen die een entiteitwaarde uniek identificeert binnen een entiteittype. Een sleutel moet dus aan twee voorwaarden voldoen: • een entiteitwaarde identificeren Begrippen rond databases
24/70 •
uit zo weinig mogelijk attributen bestaan.
Het kan gebeuren dat meerdere verzamelingen van attributen als sleutel in aanmerking komen. Elke dergelijke verzameling is dan een kandidaat sleutel. In het voorbeeld van de werknemers is het attribuut personeelsnr een kandidaat sleutel. Elke werknemer heeft immers een uniek personeelsnummer. De combinatie personeelsnr, indienst identificeert ook een enkele werknemer, maar is geen kandidaat sleutel omdat het aantal attributen niet minimaal is. Een achternaam is dan weer geen kandidaat sleutel omdat verschillende werknemers dezelfde achternaam kunnen hebben. Binnen elke entiteit zal één van de kandidaat sleutels effectief als sleutel gekozen worden. Dit wordt dan de primaire sleutel en die wordt in het schema aangeduid door de naam van het attribuut te onderlijnen. werknemer Persnr: integer achternaam: string voornaam: string straat: string afdeling: string in dienst: datum
6.5
Associaties
Associaties of relaties definiëren een verband tussen de verschillende entiteittypes. Om verwarring te vermijden met het begrip relaties in een relationele database wordt in deze syllabus in de context van E/R diagrammen systematisch over associaties gesproken. Bekijken we eventjes opnieuw het entiteittype werknemer. Het kan zijn dat het volstaat te weten voor welke afdeling een werknemer werkt en aan welke projecten hij meewerkt. In sommige gevallen zal het echter ook nodig zijn over die afdelingen of over die projecten zelf informatie op te slaan. Voor een afdeling kan dat zijn de locatie, het afdelingshoofd, enz. Voor een project kan dat zijn een begindatum, een voorziene einddatum, een effectieve einddatum, een budget, de projectverantwoordelijke, … In dat geval volstaat het niet afdeling op te nemen als attribuut van een werknemer, maar wordt elke afdeling een entiteit op zichzelf van het entiteittype afdeling. Het feit dat Jan Peeters op de afdeling inkoop werkt wordt dan uitgedrukt als een associatie tussen de entiteit Jan Peeters en de entiteit Inkoop. In een E/R diagram worden de associaties tussen de verschillende entiteittypes als volgt voorgesteld:
Begrippen rond databases
25/70 Werkt in
werknemer
afdeling
Werkt mee aan
project
6.5.1 Kardinaliteit en optionaliteit van een associatie Op basis van het aantal entiteiten (objecten, elementen) dat in elk entiteittype betrokken is bij de associatie, spreken we over de kardinaliteit en de optionaliteit van een associatie. Kardinaliteit betekent in deze context het maximaal, optionaliteit het minimaal aantal entiteiten dat betrokken kan zijn bij de associatie. Anders gezegd, kardinaliteit gaat over het aantal pijlen dat vertrekt of aankomt, optionaliteit of het al dan niet verplicht is dat er een pijl vertrekt uit of aankomt in elke entiteit. Op basis van kardinaliteit onderscheiden we drie gevallen: • • •
1-op-1-associatie 1-op-n-associatie of n-op-1associatie m-op-n-associatie
6.5.1.1
1-op-1-associatie
Met één entiteit uit de ene verzameling komt maximaal één entiteit uit de andere verzameling overeen.
Een getal bij de associatie aan de kant van het entiteittype geeft de cardinaliteit van de associatie aan. werknemer
werkstation gebruikt 1
1
Begrippen rond databases
26/70 Dit wordt gelezen als: elke werknemer kan slecht op één werkstation aanmelden (1), elk werkstation is toegewezen aan juist één werknemer (1). De optionaliteit van de associatie bepaalt of het ook mogelijk is dat er entiteiten in één van de verzamelingen zitten die niet bij de associatie betrokken zijn. Als er ook werknemers zijn die niet op de computer werken levert dit het volgende diagram: gebruikt 0,1
werknemer
1
werkstation
Interpretatie: elke werknemer werkt op hoogstens 1 (0 of 1) computer. Op elke computer werkt juist 1 werknemer. In termen van relaties zoals die in de wiskunde gebruikt worden: in de verzameling met werknemers vertrekken uit elke werknemer 1 of 0 pijlen, in de verzameling met werkstations komt in elk werkstation juist één pijl aan. 6.5.1.2
1-op-n-associatie of n-op-1associatie
Met één entiteitwaarde uit de ene verzameling komen één of meerdere entiteitwaarden uit de andere verzameling overeen.
1:n associaties komen veel voor. De associatie “…werkt in…” tussen werknemers en afdelingen kan een 1:n associatie zijn als we ervan uitgaan dat een werknemer slechts voor één afdeling (1) kan werken. In elke afdeling kunnen uiteraard meerdere werknemers werken (1...*). werknemer
afdeling werkt in 1 *
1
De optionaliteit bepaalt ook hier of elke entiteit van een entiteittype bij de associatie betrokken is. In het voorbeeld van de werknemers en de afdelingen, kan het b.v. dat de algemene directeur niet aan een bepaalde afdeling verbonden wordt, in dat geval staat er aan de kant van afdeling 0,1. 6.5.1.3
m-op-n-associatie
Met één of meerdere entiteitwaarden uit de ene verzameling komen één of meerdere entiteitwaarden uit de andere verzameling overeen.
Begrippen rond databases
27/70
Een voorbeeld Binnen een bedrijf lopen projecten. Aan elk project neemt ten minste één werknemer deel. Er kunnen ook meerdere werknemers bij een project betrokken zijn. Omgekeerd hoeft een werknemer niet bij een project betrokken te zijn, maar kan hij net zo goed aan meerdere projecten meewerken.
6.5.2 Recursieve associaties Een associatie kan entiteiten van een type relateren aan entiteiten van hetzelfde type. Dit noemt men een recursieve associatie. Een voorbeeld van een recursieve associatie in de verzameling werknemers: “…is chef van…”. Is chef van
0...1
werknemer
6.5.3 Attributen van een associatie Een associatie kan op haar beurt attributen hebben, b.v. als het ook nodig is te weten sinds wanneer een werknemer op een bepaalde afdeling werkt, dan hoort dit attribuut niet bij de werknemer en evenmin bij de afdeling. Het attribuut hoort bij de associatie.
werknemer
1…*
Werkt in
sinds: datum
Begrippen rond databases
1
afdeling
28/70
6.6
De voorbeelden samengevat
6.6.1 Beschrijving Een bedrijf bestaat uit meerdere afdelingen. Elke afdeling heeft een afdelingshoofd en ten minste één werknemer. Elke werknemer wordt toegewezen aan ten minste één, maar misschien ook aan meerdere afdelingen. Binnen het bedrijf worden projecten uitgevoerd. Met elk project is ten minste één werknemer verbonden. Een werknemer kan aan geen enkel project toegewezen zijn, b.v. als hij met vakantie is. Elke afdeling, elk project, elke werknemer en elk afdelingshoofd heeft een naam.
6.6.2 Entiteiten •
Afdeling
•
Werknemer
•
Afdelingshoofd
•
Project
6.6.3 Associaties zoeken Om de associaties tussen de entiteiten te vinden kan een tabel van pas komen. Zowel de kolom- als de rijhoofdingen bestaan uit de verschillende entiteiten. Afdeling
Werknemer stelt te werk
Afdeling Werknemer
is toegewezen aan Afdelingshoofd leidt Project
Afdelingshoofd Project wordt geleid door werkt aan
wordt mee uitgewerkt door
Begrippen rond databases
29/70
6.6.4 Een eerste E/R diagram Geleid door
afdeling
afdelingshoofd
Stelt te werk Werkt aan
werknemer
project
6.6.5 De cardinaliteiten bepalen •
Elke afdeling heeft juist één afdelingshoofd
•
Een afdelingshoofd leidt juist één afdeling
•
Elke afdeling stel ten minste één werknemer te werk
•
Elke werknemer is toegewezen aan ten minste één afdeling
•
Aan elk project werkt ten minste één werknemer
•
Een werknemer neemt deel aan 0 of meer projecten
Weergave in het diagram afdeling
afdelingshoofd
geleid door 1
1
werkt voor
1…*
1…*
werknemer
project
werkt aan 1…*
0…*
Begrippen rond databases
30/70
6.6.6 Attributen afdeling afdelingsnaam
6.7
werknemer
afdelingshoofd
personeelsnr naam
personeelsnr naam
project projectnr projectnaam
Zwakke entiteittypes
Een zwakke entiteit is een entiteit die aan de hand van haar eigen attributen niet uniek kan geïdentificeerd worden. Zij moet daarom voor haar primaire sleutel zowel een beroep doen op haar eigen attributen als op die van een ander entiteittype waarmee ze verbonden is. Familieleden van een werknemer kan een voorbeeld zijn van een zwakke entiteit. Stel dat een werknemer een hospitalisatieverzekering kan afsluiten, ook voor zijn familieleden. Naar aanleiding daarvan moeten de naam, de leeftijd en de relatie met de werknemer van elk betrokken familielid gekend zijn. Deze gegevens hebben op zichzelf geen zin, maar krijgen pas een betekenis in combinatie met de werknemer. De werknemer wordt dan de identificerende eigenaar van de zwakke entiteit. In een E/R diagram krijgt een zwakke entiteit een dubbele rand. familielid
6.8
Hiërarchieën van entiteiten
Een hiërarchie van entiteiten is een gestructureerde verzameling van entiteiten. De entiteiten in de verzameling hebben enerzijds een aantal attributen gemeenschappelijk, anderzijds verschillen ze ook in een aantal attributen. Een voorbeeld Alle werknemers, zowel arbeiders als bedienden, hebben een personeelsnummer, een naam, een geboortedatum, een datum van indiensttreding, … Dit zijn de gemeenschappelijke attributen. Van arbeiders wordt verder geregistreerd hoeveel uur ze wekelijks werken en hoeveel ze verdienen per uur. Van bedienden wordt een maandloon opgeslagen. Dit zijn de attributen waarin ze verschillen. Alle gemeenschappelijke attributen komen bij het supertype, in ons voorbeeld werknemer. Alle attributen die verschillen worden opgenomen in het subtype, in ons voorbeeld arbeider en bediende.
Begrippen rond databases
31/70 werknemer persnr naam geboortedatum indienst …
arbeider
bediende
uurloon
maandloon
aantal uur
Elk subtype neemt ook de primaire sleutel van het supertype over. Subtypes die als enig attribuut die primaire sleutel hebben, worden verwijderd uit het diagram. Er bestaat een 1-op-1 associatie tussen het supertype en het subtype. In het voorbeeld van de werknemer zijn de subtypes disjunct. Subtypes kunnen echter ook een doorsnede hebben. Een ander voorbeeld Binnen een opleidingsinstelling zouden we als supertype de entiteit persoon kunnen hebben. Student, docent en staf komen dan in aanmerking als subtypes. Iemand kan echter tegelijkertijd docent zijn en deel uitmaken van de staf. Dit is een voorbeeld van niet disjuncte subtypes.
Begrippen rond databases
32/70 persoon
student
staf
docent
Nog enkele spelregels i.v.m. overerving: Elk entiteitwaarde van een supertype moet ook in een subtype terug te vinden zijn. Een subtype kan maar bij één supertype horen. Hiërarchieën mogen wel genest worden, m.a.w. een subtype kan op zijn beurt supertype zijn voor een ander subtype.
6.9
Een entiteit of een attribuut?
Het is niet altijd duidelijk of een gegeven een entiteit of een attribuut moet worden. Enkele vuistregels bij het maken van een keuze: De adresgegevens van een werknemer. Voorzien we een attribuut adres bij de entiteit werknemer? Of maken we een aparte entiteit adres en een associatie “…heeft als adres…” tussen de entiteiten werknemer en adres? Overwegingen die hierbij een rol spelen: Heeft elke werknemer slechts één adres of kan het zijn dat er meerdere adressen van een werknemer moeten geregistreerd worden? In het eerste geval zal het eerder aangewezen zijn adres als een attribuut te beschouwen, in het tweede geval wordt adres een aparte entiteit. Is de structuur van het attribuut belangrijk? Zullen we ook al eens werknemers op basis van postcode of land willen selecteren? Zo ja, is het ook aangewezen een apart attribuut adres te voorzien.
Begrippen rond databases
33/70
6.10 Oefeningen 6.10.1
Een factuur
Je ontwerpt een entiteittype factuur op basis van de hieronder afgebeelde factuur. Groothandel ABC Dreef 55 1234 Destad Nummer Naam: Adres: Woonplaats: Tel :
Factuurnr: 080350ST Datum: 31/03/2008 5678 Piet Peeters Park 70 1234 Destad 03 123 45 67
Artikelnr
Omschrijving
I2200 D3000 P962
Notebook Desktops Printer
Aantal
Prijs
Bedrag
5 12 1
799 399 145
3995,00 4788,00 145,00
Subtotaal Korting Totaal excl BTW 21% BTW Totaal
8.928,00 100,00 9.028,00 1895,88 10.923,88
Geef voor dat entiteittype factuur: •
Een vijftal atomaire attributen
•
Drie procesattributen
•
Drie meerwaardige attributen
•
Een primaire sleutel
6.10.2
Associaties, kardinaliteit en optionaliteit
Er worden telkens twee entiteittypes gegeven. Geef een beschrijving van een mogelijke associatie tussen die entiteittypes en bespreek ook de cardinaliteit en de optionaliteit van deze associaties. bankbediende
klant
driewieler
wiel
film
regisseur
Begrippen rond databases
34/70
6.10.3
Hiërarchische structuren
Bedenk een mogelijke hiërarchische structuur van entiteittypes die te maken hebben met voertuigen.
6.10.4
Recursieve associatie
Geef een ander voorbeeld van een recursieve associatie dan dat van paragraaf 6.5.2.
6.10.5
Ziekenhuis
Bedenk een vijftal entiteittypes die je in een ziekenhuisomgeving kunt tegenkomen. Vermeld bij elk entiteittype of het om een rol, een gebeurtenis, een locatie, een voorwerp of een concept gaat. Bedenk ook een aantal zinvolle attributen voor elk van deze entiteiten. Kies een primaire sleutel voor elk entiteittype. Stel deze entiteittypes en hun eventuele associaties voor in een E/R diagram.
6.10.6
Domein
Bepaal het domein voor een attribuut postcode in België.
Begrippen rond databases
35/70
7 LOGISCH DATABASEONTWERP Klassieke gegevensmodellen binnen een DBMS zijn • Het hiërarchische model •
Het netwerk model
•
Het relationele model.
Het verschil tussen de drie modellen die hier besproken worden, gaat vooral over de wijze waarop associaties tussen entiteiten worden vastgelegd. De nadruk wordt gelegd op het relationele model, omdat dat vandaag nog veel gebruikt wordt. De beschrijving van het hiërarchische en van het netwerkmodel zijn eerder volledigheidshalve toegevoegd. Verderop in de cursus wordt ook nog ingegaan op object georiënteerde databases en object relationele databases die dan weer nauw aansluiten bij object georiënteerde programmeertalen.
7.1
Het hiërarchische gegevensmodel
Dit model dateert van het midden van de jaren 60 en is geïnspireerd op de hiërarchische structuur die dikwijls gehanteerd wordt om situaties beter te begrijpen. Denk hierbij b.v. aan de schema’s die ontwikkeld werden om de planten en dierenwereld verder in te delen of aan de indeling van de talen. Elk entiteittype uit het E/R model komt in een hiërarchische database overeen met een recordtype. Per entiteitwaarde wordt een record gecreëerd. Elk record heeft attributen. Omdat 1:n associaties veel voorkomen binnen een gegevensmodel, is voor het hiërarchische model als uitgangspunt genomen dat deze associaties gemakkelijk gemodelleerd moeten kunnen worden. De 1 entiteit komt aan de top van de hiërarchie en daaraan worden de geassocieerde entiteiten opgehangen. Dit noemt men een vader / zoon relatie. De 1 entiteit is hierin de vader, de records aan de andere kant zijn de zonen. operatieafdeling
peeters janssens
gynaecologie
orthopedie
symens kaiblinger
verelst
martens dielman dhooghe
In een voorbeeld van werknemers die op een afdeling werken zou dat er als volgt kunnen uitzien: Een 1:n associatie houdt in dat een werknemer niet op meerdere afdelingen kan werken.
Begrippen rond databases
36/70 Bij een n:m associatie, zoals een werknemer die meewerkt aan meerdere projecten moet een entiteit gekozen worden om bovenaan in de structuur te zetten. Je kunt de structuur dan opbouwen uitgaande van de werknemers en aan elke werknemer alle afdelingen koppelen waarvoor hij werkt of je kunt de structuur opbouwen uitgaande van de projecten en aan elk project een lijst koppelen van de werknemers die eraan mee werken. Nadelen van dit model zijn: Een werknemer wordt op een heel andere manier benaderd dan een afdeling. Een afdeling kan men direct terugvinden, om een werknemer terug te vinden moet men eerst de afdeling opsporen waarop de werknemer werkt om daarna de werknemer zelf te zoeken. Een nieuwe werknemer kan niet zonder meer toegevoegd worden. Eerst moet er een afdeling zijn waaraan de werknemer kan gekoppeld worden. Bij n:m associaties treedt redundantie op. Als aan elke medewerker de projecten gekoppeld worden waaraan hij deelneemt, dan zullen de gegevens van een project teruggevonden worden bij elke werknemer die eraan meewerkt. Worden de werknemers opgeslagen per project, dan zullen de gegevens van een werknemer teruggevonden worden bij elk project waaraan hij meewerkt.
7.2
Het netwerk gegevensmodel
Het netwerk gegevensmodel dateert van het einde van de jaren 60, was vooral populair in de jaren 80 en neemt een aantal nadelen van het hiërarchische model weg. Ook hier komt elk entiteittype overeen met een recordtype en elke entiteitwaarde met een record. Alle records worden echter afzonderlijk opgeslagen. In de beide andere modellen is dit niet het geval: in het hiërarchische model worden de records opgeslagen als één groep per gekozen parent, in het relationele model (zie verder) in groepen per soort. Een 1:n associatie wordt omgezet in een verzamelingtype. Elk verzamelingtype heeft drie kenmerken: een naam, een eigenaar en leden. Verschillen tussen een verzameling uit de wiskunde en een verzameling uit het netwerkmodel: •
In de wiskunde zijn alle elementen van een verzameling gelijkwaardig, in het netwerkmodel is er een bijzonder element, nl. de eigenaar.
•
Verder zijn de elementen van een verzameling niet geordend, de leden van een verzameling in het netwerkmodel staan wel in een bepaalde volgorde.
Om die volgorde te definiëren verwijst een pointer van de eigenaar naar het eerste lid, bij het eerste lid verwijst de pointer dan naar het tweede lid, bij het tweede naar het derde enz. De pointer van het laatste lid verwijst terug naar de eigenaar. Denken we aan het voorbeeld met de werknemers en de afdelingen. De naam van het verzamelingtype zou dan kunnen zijn werkt in, de eigenaar één of andere afdeling en de leden alle werknemers die in het departement werken. Er geldt wel als bijkomende beperking, omdat het een 1:n associatie is, dat elke werknemer maar als lid van één afdeling kan teruggevonden worden.
Begrippen rond databases
37/70 Om een m:n relatie voor te stellen, wordt tussen de elementen van de twee entiteiten een extra record gedefinieerd dat de link tussen de twee elementen legt. Bekijken we de volgende n:m relatie tussen werknemers en projecten: Gaan we uit van een situatie met vier werknemers W1, … W4 en drie projecten P1, …, P3 Veronderstel dat werknemers deelnemen aan de projecten op de volgende manier: (W1,P2) = B1
(W2,P1) =B2 (W3,P2) = B5 (W2,P2) =B3 (W3,P3) = B6 (W2,P3) = B4 Als we dit gegroepeerd per project bekijken levert dat:
(W4,P3) = B7
(W2, P1) = B2
(W1,P2) = B1 (W2,P3,) = B4 (W2,P2) = B3 (W4,P3) = B7 (W3,P2) = B6 Er worden dan zeven tussenliggende records gecreëerd die b.v. als naam … is betrokken bij … (B) kunnen krijgen met bijbehorende verwijzingen: W1 verwijst naar B1, B1 verwijst terug naar W1
P1 verwijst naar B2 en B2 verwijst terug naar P1
W2 verwijst naar B2, B2 verwijst naar B3, B3 naar B4 en B4 terug naar W2
P2 verwijst naar B1, B1 verwijst naar B3, B3 verwijst naar B6 en B6 verwijst terug naar P2
W3 verwijst naar B5, B5 naar B6 en B6 terug naar W3
P3 verwijst naar B4, B4 naar B7 en B7 terug naar P3
W4 verwijst naar B7 en B7 terug naar W4 Dit vangt het probleem van de redundantie bij het hiërarchisch model op, een belangrijk nadeel is echter dat het zoeken in een netwerkdatabase bepaald niet eenvoudig is.
7.3
Het relationeel gegevensmodel
7.3.1 De omzetting van entiteiten en attributen Dit model werd rond 1970 ontwikkeld door E.F. Codd. De bedoeling was vooral de complexiteit van de programma’s voor hiërarchische en netwerkdatabases te verminderen. Een belangrijk probleem bij deze modellen: de gegevens zijn alleen via vooraf gedefinieerde paden toegankelijk. In een relationeel model ontstaat een vrije keuze van toegang door het definiëren van joins. In het relationeel gegevensmodel wordt elk entiteittype omgezet in een tabel. Dergelijke tabel wordt ook wel een relatie genoemd en mag in die context niet verward worden met een relatie of associatie uit het E/R model 1. De attributen die horen bij het entiteittype worden teruggevonden als kolomhoofdingen in de tabel.
1
Dit is de reden dat in deze syllabus in de context van E/R diagrammen systematisch over associaties gesproken werd.
Begrippen rond databases
38/70 Omzetting van het entiteittype werknemer: werknemer persnr achternaam voornaam adres afdeling in dienst Persnr Anaam Vnaam Adres In dienst Afdeling Elke rij in de tabel correspondeert met een entiteit en wordt in deze context ook wel een tupel genoemd. Persnr 12002
Anaam Peeters
Vnaam Jan
Adres Dreef 85 2000 Antwerpen
In dienst 1 mei 1998
Afdeling inkoop
7.3.2 De omzetting van een 1:n associatie De vertaling van een 1:n associatie gebeurt door de primaire sleutel van het entiteittype aan de 1-kant van de associatie op te nemen als attribuut in het entiteittype aan de veelkant van de associatie. afdeling
werknemer persnr naam adres geboortedatum indienst …
werkt in
1…*
afdelingsID afdelingsnaam afdelingshoofd locatie …
1
Dit E/R diagram wordt op de volgende manier omgezet naar relaties is een relationele database: De tabel werknemer krijgt als kolommen de attributen van werknemer en het afdelingid van de afdeling waarvoor hij werkt. Persnr
Naam
Adres
Geboortedatum
Indienst
AfdelingsID
Een tabel afdeling met als kolommen de attibuten van de het entiteittype afdeling. AfdelingsID
Afdelingsnaam
Afdelingshoofd
Locatie
AfdelingID in de tabel werknemer wordt een foreign key genoemd. Een foreign key (vreemde sleutel) is de verbindende schakel tussen twee tabellen. Met een waarde uit een rij van de ene tabel kun je in een andere tabel de juiste rij met gerelateerde gegevens opzoeken. De ene tabel geeft als het ware de sleutel voor de andere, 'vreemde' tabel.
Begrippen rond databases
39/70
7.3.3 De omzetting van een m:n associatie Een m:n associatie kan niet rechtstreeks vertaald worden in een relationele database, maar wordt omgezet naar twee 1:n associaties. De associatie tussen de entiteittypes werknemer en project kan een n:m associatie zijn. Immers een werknemer kan deelnemen aan meerdere projecten en aan een project kunnen meerdere werknemers deelnemen. In een relationele database zal dit aanleiding geven tot drie tabellen of relaties: een tabel werknemer met als kolommen de attributen van een werknemer, een tabel project met als kolommen de attributen van een project en een tabel medewerker van een project met als kolommen de primaire sleutel van werknemer en de primaire sleutel van project. project
werknemer persnr naam adres geboortedatum indienst …
1…*
neemt deel aan
1…*
projectid projectnaam projectleider budget …
wordt omgezet in een relatie werknemer Persnr
Naam
Adres
Geboortedatum Indienst
en een relatie project Projectid
Projectnaam
Projectleider
budget
En een relatie medewerker aan een project Projectid
persnr
7.3.4 De omzetting van een associatie met eigen attributen Een associatie met eigen attributen in een E/R diagram wordt omgezet in een aparte tabel (relatie). Deze relatie krijgt volgende kolommen • De primaire sleutel van elke entiteit die betrokken is bij de associatie •
De attributen die de associatie beschrijven
De combinatie van primaire sleutels van de betrokken entiteiten wordt een kandidaatsleutel voor de relatie.
Begrippen rond databases
40/70 Het E/R diagram van een werknemer die verbonden is aan een afdeling: afdeling
werknemer persnr naam adres geboortedatum indienst …
werkt in
1…*
1
afdelingID afdelingsnaam afdelingshoofd locatie …
sinds
wordt vertaald naar volgende tabel of relatie: persnr
afdelingID
sinds
7.3.5 Integriteit van de gegevens Een degelijk DBMS zal ook controles uitvoeren die de integriteit van de gegevens garanderen. 7.3.5.1
Entiteit integriteit
Voor elke entiteitwaarde moet een waarde voor de primaire sleutel bestaan die uniek is en niet null. Dit is een rechtstreeks gevolg van het feit dat de primaire sleutel de entiteit uniek moet identificeren. 7.3.5.2
Referentiële integriteit
Zoals vermeld in paragraaf “7.3.2 De omzetting van een 1:n associatie”, creëert een foreign key een associatie tussen twee entiteiten. De entiteit met de foreign key is de afhankelijke entiteit, de entiteit met de primaire sleutel de parent entiteit. Om zinloze verwijzingen te vermijden, moet elke vreemde sleutel (foreign key) verwijzen naar een primaire sleutel van een bestaande entiteit in het parent entiteittype. Dit betekent dat een aantal controles moeten uitgevoerd worden zowel bij het toevoegen van records aan de afhankelijke tabel als bij het verwijderen van records uit de parent tabel. Bij het toevoegen van een record aan de afhankelijke relatie zal het systeem controleren of de waarde die als foreign key gebruikt wordt, wel degelijk voorkomt als primaire sleutel van een record in de parent tabel.
Begrippen rond databases
41/70 Bij het verwijderen van een record uit de parent tabel zal het systeem controleren of er geen records meer aanwezig zijn in de afhankelijke tabel waarin de waarde van de primaire sleutel van het verwijderde record als foreign key gebruikt wordt. Bij de werknemers en de afdelingen komt het erop neer dat de waarde die voor afdelingsID ingevuld wordt in het record van een werknemer alleen dan aanvaard wordt als die waarde ook voorkomt als primaire sleutel van een record in de tabel afdelingen en als een afdeling verwijderd wordt uit de tabel afdelingen zal dat alleen lukken als de afdelingsID van die afdeling bij geen enkele werknemer voorkomt als foreign key.
7.4
Oefeningen
7.4.1 Film Het is de bedoeling een database te ontwikkelen met gegevens over films. Er wordt gedacht aan een entiteit film en een entiteit acteur. Welke associatie kan er bestaan tussen deze twee entiteiten. Bespreek de kardinaliteit en de optionaliteit van deze associatie. Hoe zal die associatie vertaald worden in een relationele database?
7.4.2 Bibliotheek In een bibliotheek maken o.a. de volgende drie tabellen deel uit van een relationele database: Een tabel met de gegevens van de lezers zoals hun naam en adresgegevens. Elke lezer wordt geïdentificeerd via een lezersnummer. Een tabel met gegeven i.v.m. de boeken zoals het ISBN nummer, de titel , de auteur, de uitgever, enz. Elk boek wordt geïdentificeerd via een boeknummer. Een tabel met ontleningen waarin geregistreerd wordt welke lezer welk boek ontleent evenals de startdatum van de ontlening en een datum waarop het boek terug binnen moet gebracht worden. Welke relaties zullen er bestaan tussen deze drie tabellen, vermeld de naam van de sleutelvelden en de kardinaliteit. Formuleer wat het betekent als er referentiële integriteit wordt afgedwongen voor deze associaties.
Begrippen rond databases
42/70
8 VERDERE VERFIJNING VAN HET SCHEMA: NORMALISATIE Het is belangrijk dat in een database geen redundanties en geen inconsistenties optreden. Een techniek die ontwikkeld werd door Codd om dit te vermijden in een relationele database is het normaliseren van de gegevens.
8.1
Problemen met redundantie en inconsistentie
Met de stappen die we tot nog toe ondernomen hebben, kan er nog altijd redundantie optreden in onze tabellen (relaties). Redundantie geeft aanleiding tot allerlei problemen: Het in beslag nemen van overbodige opslagruimte en het verbruiken van mankracht voor het invoeren van die dubbele gegevens. Problemen bij het wijzigen van gegevens. Alle kopieën van de gegevens moeten gelijktijdig aangepast worden of dit geeft aanleiding tot inconsistenties. Problemen bij het verwijderen van gegevens. Het verwijderen van bepaalde gegevens kan meteen het ongewild verwijderen van andere data tot gevolg hebben. Problemen bij het toevoegen van gegevens. Sommige gegevens zullen pas kunnen toegevoegd worden als eerst andere niet gerelateerde gegevens toegevoegd zijn.
8.1.1 Een illustratie Veronderstel dat we informatie ivm met bestellingen in de database willen opslaan en dat we de gegevens als volgt structureren: Klantnaam
Klantadres
Jan Jansens
Dreef 20 2000 Antwerpen Laar 50 1000 Brussel Plein 70 9000 Gent Laar 50 1000 Brussel Markt 34 2000 Antwerpen
Peter Peeters Elke Dhooghe Peter Peeters Sim Pauwels
Besteldatum 9/12/2005
Artikel Emmer
Hoeveel heid 20
EenheidsPrijs 2,5
Totaal 50
10/12/2005
Dweil
50
1,5
75
15/12/2005
Borstel
25
2,0
50
10/12/2005
Spons
40
1,5
60
20/12/2005
Dweil
30
1,5
45
8.1.2 Problemen Er is redundantie: zowel de adresgegevens van Peter Peeters (en meer algemeen van elke klant die bestellingen plaatst) als de gegevens over dweilen (en meer algemeen over alle artikelen die door meerdere klanten besteld worden), komen meerdere keren voor.
8.1.3 Gevolgen Als Peter Peeters verhuist, moet zijn adres meerdere keren aangepast worden. Dit betekent niet alleen onnodig werk, maar geeft ook aanleiding tot fouten. Immers als
Begrippen rond databases
43/70 ergens een wijziging over het hoofd gezien wordt, is het onmogelijk achteraf op basis van de tabel nog te achterhalen wat nu het juiste adres is van de klant. Zo vind je ook eenzelfde artikel op verschillende plaatsen terug met analoge problemen bij b.v. een prijsverandering. Het is onmogelijk de prijs van een nieuw artikel op te slaan als nog geen enkele klant dat artikel besteld heeft. Als je een klant wilt verwijderen en hij is de enige die tot nog toe een bepaald artikel besteld heeft, dan is meteen ook de informatie over het artikel weg.
8.2
De normalisatieprocedure
8.2.1 Voorbereiding tot normalisatie 8.2.1.1
Procedure
•
Maak een lijst van alle attributen van een document (of lijst) dat het resultaat is van een proces. Voorbeelden: documenten, lijsten op papier of scherm, invulformulieren op het scherm. We werken document per document, scherm per scherm. Pas na de derde normaalvorm voegen we alle resultaten samen tot één structuur.
•
Controleer of er homoniemen of synoniemen voorkomen en pas gekozen namen aan indien nodig. Een homoniem is eenzelfde woord dat naar twee verschillende begrippen verwijst. vb. Het kan voorkomen, dat een meisje met een aardig voorkomen kan voorkomen, dat ze moet voorkomen. Het woord voorkomen is vier keer gebruikt maar telkens in een andere betekenis. Synoniemen zijn twee verschillende woorden die hetzelfde betekenen.
vb. klant debiteur •
Schrap alle procesattributen, m.a.w. alle attributen die het resultaat zijn van een berekening aan de hand van andere attributen. Schrap ook alle attributen met een vaste waarde, zoals de naam en het adres van het leverende bedrijf op een factuur.
•
Kies één attribuut als sleutel waarvan de andere attributen afhankelijk zijn. Meestal zal dit een attribuut zijn dat een document, voorwerp, persoon, …. Identificeert.
•
Duid repeterende groepen aan, dit zijn verzamelingen van attributen die meerdere keren voorkomen t.o.v. de sleutel.
Begrippen rond databases
44/70 8.2.1.2
Voorbeeld: een factuur
Groothandel ABC Dreef 55 1234 Destad Nummer Naam: Adres: Postcode: Tel :
Factuurnr: 080350ST Datum: 31/03/2008 5678 Piet Peeters Park 70 1234 Destad 03 123 45 67
Nummer
Omschrijving
I2200 D3000 P962
Notebook Desktops Printer
Aantal
Prijs
Bedrag
5 12 1
799 399 145
3995,00 4788,00 145,00
Subtotaal Korting Totaal excl BTW 21% BTW Totaal
8.928,00 100,00 9.028,00 1895,88 10.923,88
Inventarisatie van de gegevens: factuurnummer factuurdatum nummer naam adres postcode gemeente nummer omschrijving aantal eenheidsprijs bedrag subtotaal korting totaal_excl_btw btw_percentage totaal Er zijn geen synoniemen, maar wel homoniemen. Nummer wordt nl. twee keer gebruikt in twee verschillende betekenissen. Het eerste nummer betekent klantnummer, het tweede artikelnummer. We wijzigen de namen van de attributen en het probleem is opgelost. factuurnummer factuurdatum Begrippen rond databases
45/70 klantnummer naam adres postcode gemeente artikelnummer omschrijving aantal eenheidsprijs bedrag subtotaal korting totaal_excl_btw btw_percentage totaal Verwijder alle procesattributen en alle attributen die een vaste waarde hebben, uit de lijst. factuurnummer factuurdatum klantnummer naam adres postcode gemeente artikelnummer omschrijving aantal eenheidsprijs korting Een factuur wordt gekenmerkt door het factuurnummer, dus factuurnummer wordt de sleutel. De sleutel of primary key identificeert de factuur. Met elke waarde van de sleutel komt slechts één factuur overeen. Duid ook repeterende groepen aan. De lay-out van een document of lijst helpt vaak om de repeterende groepen te ontdekken. Op een FACTUUR komen de attributen artikelnummer, omschrijving, aantal en eenheidsprijs meerdere keren voor. Dit zijn de attributen van de FACTUURLIJN. Zij herhalen ten opzichte van de sleutel factuurnummer.
Begrippen rond databases
46/70 De attributenlijst ziet er nu als volgt uit: factuurnummer factuurdatum klantnummer naam adres postcode gemeente artikelnummer omschrijving aantal eenheidsprijs korting De gegevens staan in de nulde normaalvorm. Een andere notatie die ook gebruikt wordt om de normaalvormen weer te geven: FACTUUR (factuurnummer, factuurdatum, klantnummer, naam, adres, postcode, gemeente, RG[artikelnummer, omschrijving, aantal, eenheidsprijs], korting) RG staat hierin voor repeterende groep. Het normalisatie proces bestaat vanaf hier uit 3 normalisatiestappen, waarbij stap voor stap de verschillende gegevensgroepen (ook wel entiteiten genoemd) met bijbehorende sleutelkenmerken en afhankelijke gegevenskenmerken worden bepaald. Opmerking In dit voorbeeld wordt uitgegaan van een vast BTW percentage, het BTW percentage zou ook per artikel kunnen verschillen en wordt in dat geval op elke factuurregel herhaald en verdwijnt niet uit de lijst met attributen. Een factuurregel zou er dan als volgt kunnen uitzien: D3000
Desktops
12
399
21%
5793,48
8.2.2 Eerste normalisatiestap 8.2.2.1
Procedure
Isoleer repeterende groepen in een aparte entiteit en zoek een primaire sleutel voor die entiteit. De primaire sleutel zal bestaan uit de primaire sleutel van de oorspronkelijke entiteit gecombineerd met één of meerdere identificerende attributen in de repeterende groep. Herhaal stap 1 totdat er geen attributen meer zijn die een herhaald aantal keer voorkomen. 8.2.2.2
Voorbeeld
De FACTUURLIJN is een repeterende groep en moet bijgevolg afgescheiden worden van de rest van de FACTUUR. We herhalen de sleutel en groeperen daaronder alle de repeterende attributen. Uit de repeterende groep kiezen we een tweede sleutel (artikelnummer) die samen met het factuurnummer de samengestelde sleutel wordt voor de nieuwe groep. De repeterende attributen worden uit de eerste groep geschrapt. Begrippen rond databases
47/70 factuurnummer factuurdatum klantnummer naam adres postcode gemeente artikelnummer omschrijving aantal eenheidsprijs btwpercentage wordt dan FACTUUR factuurnummer
FACTUURLIJN factuurnummer
factuurdatum
artikelnummer
klantnummer
omschrijving
naam
aantal
adres
eenheidsprijs
postcode
btwpercentage
gemeente korting Er zijn geen repeterende groepen meer, dus de gegevens staan nu in de eerste normaalvorm.
8.2.3 Tweede normalisatiestap 8.2.3.1
Procedure
In deze normaalvorm richten we ons tot de groepen met een samengestelde sleutel. Onderzoek welke attributen niet functioneel afhankelijk zijn van de samengestelde sleutel en breng deze attributen onder bij een 'eigen' sleutel. Herhaal deze stap voor alle groepen met een samengestelde sleutel. 8.2.3.2
Voorbeeld
We hebben één groep met een samengestelde sleutel nl. de FACTUURLIJN met als sleutel factuurnummer en artikelnummer. De attributen omschrijving en eenheidsprijs zijn alleen afhankelijk van de sleutel artikelnummer en niet van factuurnummer. Deze attributen worden samen met de sleutel artikelnummer in een aparte groep geplaatst. Deze groep krijgt ook een naam, in het voorbeeld kan ARTIKEL een geschikte naam zijn.
Begrippen rond databases
48/70 De groepen zien er dan zo uit : FACTUUR FACTUURLIJN ARTIKEL Factuurnummer artikelnummer factuurnummer factuurdatum artikelnummer omschrijving klantnummer aantal eenheidsprijs naam btwpercentage adres postcode gemeente korting De gegevens staan nu in de tweede normaalvorm.
8.2.4 Derde normalisatiestap 8.2.4.1
Procedure
Nu rest ons nog de laatste stap, de derde normaalvorm. Die vinden we door de attributen te verwijderen die functioneel afhankelijk zijn van nietsleutelattributen. De nieuwe groep zal bestaan uit de afhankelijke attributen samen met het attribuut waarvan ze afhankelijk zijn. Het heeft alleen zin entiteiten te bekijken die meer dan één niet-sleutelattribuut hebben. 8.2.4.2
Voorbeeld
De kenmerken naam, adres, postcode en gemeente zijn afhankelijk van het attribuut klantnummer. Het klantnummer laten we staan in de groep maar de andere kenmerken plaatsen we apart in een groep. In de groep ARTIKEL komt de genoemde afhankelijkheid niet voor. De omschrijving en de prijs zijn niet van elkaar afhankelijk. Een omschrijving kan meer dan één keer voorkomen met verschillende prijzen en vice versa. In de groep FACTUUR komen we de besproken afhankelijkheid wel tegen. De attributen naam, adres, postcode en gemeente zijn afhankelijk van het attribuut klantnummer. Bij iedere klantnummer vinden we immers precies één waarde voor naam, adres, postcode en gemeente. We nemen de afhankelijk attributen apart en vormen hiermee een nieuwe groep KLANT. In de groep FACTUUR blijft het attribuut klantnummer bestaan waarvan de andere afhankelijk zijn. Het attribuut klantnummer vormt de link tussen de groepen FACTUUR en KLANT. Het attribuut klantnummer uit FACTUUR is de foreign key die verwijst naar de primaire sleutel in de groep KLANT. Foreign key’s onderstrepen we met een gestippelde lijn. Het resultaat wordt dan FACTUUR factuurnummer factuurdatum klantnummer
FACTUURLIJN factuurnummer artikelnummer aantal
ARTIKEL artikelnummer omschrijving eenheidsprijs btwpercentage
Begrippen rond databases
49/70 KLANT klantnummer naam adres postcode gemeente korting De gegevens staan nu in de derde normaalvorm.
8.3
Een tweede voorbeeld, een offerte
In een bedrijf worden niet alleen facturen gemaakt, maar ook offertes. We vertrekken deze keer van een offerte. Een offerte lijkt qua opbouw natuurlijk erg op een factuur Groothandel ABC Dreef 55 1234 Destad Nummer Naam: Adres: Postcode: Tel :
Offertenummer: 1245 Datum: 01/03/2008 5678 Jan Janssens Dreef 100 1234 Destad 03 765 43 21
Artikelnr
Omschrijving
I2200 D3000 P962
Notebook Desktops Printer
Aantal eenheidsprijs 5 12 1
Subtotaal Korting Totaal excl BTW Deze offerte is één maand na de offertedatum geldig. Gegevens offertenummer offertedatum klantnummer naam adres postcode gemeente artikelnummer omschrijving Begrippen rond databases
799 399 145
Bedrag 3995,00 4788,00 145,00 8.928,00 100,00 9.028,00
50/70 aantal eenheidsprijs som_exclusief_btw korting totaal_exclusief_btw Homoniemen en synoniemen verwijderen, berekende velden verwijderen en sleutel kiezen. offertenummer offertedatum klantnummer naam adres postcode gemeente artikelnummer omschrijving aantal eenheidsprijs korting
8.3.1 Na de eerste normalisatiestap OFFERTE offertenummer offertedatum klantnummer naam adres postcode gemeente korting
OFFERTELIJN offertenummer artikelnummer omschrijving aantal eenheidsprijs
8.3.2 Na de tweede normalisatiestap OFFERTE offertenummer offertedatum korting klantnummer naam adres postcode gemeente korting
OFFERTELIJN offertenummer artikelnummer aantal
ARTIKEL artikelnummer omschrijving eenheidsprijs
8.3.3 Na de derde normalisatiestap OFFERTE
OFFERTELIJN
ARTIKEL
Begrippen rond databases
51/70 offertenummer offertedatum korting klantnummer
offertenummer artikelnummer aantal
artikelnummer omschrijving eenheidsprijs
KLANT klantnummer naam adres postcode gemeente
8.4
Integratie van genormaliseerde gegevensgroepen
Het doel van het integreren van de verschillende informatiebehoeften is eigenlijk hetzelfde als het doel van het normaliseren op zich. Het belangrijkste is dat er geen overtollige en dubbele gegevens meer voorkomen. Dit om het onderhoud en gebruik van de gegevens adequaat te houden.
8.4.1 Homoniemen en synoniemen Controleer eerst op homoniemen en synoniemen in de twee groepen. Zo komt het attribuut aantal zowel voor bij factuurregel als bij offerteregel. We passen de naam aan en spreken bij factuur over aantalfactuur en bij offerte over aantalofferte om verwarring te voorkomen. Hetzelfde probleem stelt zich bij korting.
8.4.2 Samenvoegen van entiteiten uit de verschillende informatiebehoeften Kunnen er entiteiten uit de twee genormaliseerde informatiebehoeften samengevoegd worden? ARTIKEL(FACTUUR)
ARTIKEL(OFFERTE)
artikelnummer omschrijving eenheidsprijs btwpercentage
Artikelnummer Omschrijving Eenheidsprijs
KLANT(FACTUUR)
KLANT(OFFERTE)
wordt dan ... artikelnummer omschrijving eenheidsprijs btwpercentage
klantnummer Klantnummer klantnummer naam Naam naam adres Adres adres postcode Postcode postcode gemeente Gemeente gemeente Na samenvoeging geeft dit de volgende entiteiten en attributen FACTUUR
FACTUURREGEL
ARTIKEL
Begrippen rond databases
52/70 factuurnummer factuurdatum klantnummer kortingfactuur
Factuurnummer artikelnummer aantalfactuur
artikelnummer omschrijving eenheidsprijs btwpercentage
KLANT
OFFERTE
OFFERTEREGEL
klantnummer naam adres postcode gemeente
offertenummer offertedatum klantnummer kortingofferte
offertenummer artikelnummer aantalofferte
8.4.3 Definitieve naamgeving en samenstelling
8.5
FACTUUR factuurnummer factuurdatum klantnummer kortingfactuur
FACTUURLIJN factuurnummer artikelnummer aantalfactuur
ARTIKEL artikelnummer omschrijving eenheidsprijs btwpercentage
KLANT klantnummer naam adres postcode gemeente
OFFERTE offertenummer offertedatum klantnummer kortingofferte
OFFERTELIJN offertenummer artikelnummer aantalofferte
Bepalen van de relaties
De entiteittypes staan niet los van elkaar. Er bestaan associaties tussen bepaalde types op basis van de foreign keys. Klantnummer vormt de link tussen de groepen KLANT en FACTUUR en de groepen KLANT en OFFERTE. Klantnummer is de sleutel in de groep KLANT en een foreign key in de groepen FACTUUR en OFFERTE. Het gaat hier telkens om een 1:n relatie.
Begrippen rond databases
53/70 We stellen dit voor in een gegevensstructuurdiagram
Tips bij het bepalen van de relaties: Als een primary key van entiteit1 deel is van een samengestelde sleutel van entiteit2, dan is er een 1…n associatie tussen entiteit1 en entiteit2. b.v. factuur en factuurlijn Als een primary key van entiteit1 voorkomt als attribuut in entiteit2 dan is er een 1…n associatie tussen entiteit1 en entiteit2. b.v. klant en factuur
Begrippen rond databases
54/70
8.6
Oefeningen
8.6.1 Projectbeheer Op aanvraag wil men op de informaticafdeling een overzicht krijgen van de gepresteerde arbeid per project. Normaliseer deze informatiebehoefte. Maak ook een gegevensstructuurdiagram. Projectoverzicht Project
Medewerker
Nr
Omschrijving Budget Persnr
Naam Afdeling Chef
101
Boekhouding
Jan
AN10
Bert
58
204
Paul
PR05
Rob
114
109
Rita
PR15
Geert
124
Jef
AN10
Bert
109
Rita
PR15
Geert
234
Fred
PR05
Rob
105
2000 123
Aankoop
3250 324
Uren
86 157 98
8.6.2 Ziekenfonds Een centrale registratie van een ziekenhuis heeft voor het ziekenfonds de volgende jaaroverzichten nodig. Een specialist is aan precies één afdeling verbonden. Per periode kan een medicijn meer dan één keer worden voorgeschreven. Normaliseer deze informatiebehoefte. Maak ook een gegevensstructuurdiagram. Patientnr
4773
Naam
Peeters P.
Adres
Dorpstraat 45
Ziekenfondsnr 448833756
Gemeente 2000 Antwerpen Specialist Afdeling
Periode
Medicijn
Datum
Jannsens
12/01/2003 – 15/02/2003
Valium
12/01/2003
Hexafro
30/01/2003 23/07/2003
Neuro
Janssens
Neuro
23/07/2003 – 01/09/2003
Valium
Smits
Chirirg
21/12/2003
Antibiotica 23/12/2003
8.6.3 Bestellingen en pickinglist Normaliseer de informatiebehoeften uit paragraaf 5.1 Integreer daarna ook de genormaliseerde informatiebehoeften en maak een gegevensstructuurdiagram. Begrippen rond databases
55/70
8.6.4 Distributiebedrijf van kantoorbenodigheden De klanten plaatsen aankooporders. De aankooporders worden verzameld en er worden dan magazijnfiches opgesteld zodat de magazijnier weet waar hij de producten in de magazijnen moet ophalen. Als de magazijnier producten wil bijbestellen kan hij de leverancierslijsten raadplegen. Normaliseer en integreer deze informatiebehoeften Een inkooporder voor de inkoper
De magazijnfiches voor de magazijnier
Leveranciersinformatie voor de magazijnier
INKOOPORDER Ordernr. 8254
Datum: 12/09/2003
LEVERANCIER: 523 BURO Industrieweg 1 9000 Gent Artikelnr
Omschrijving
Aantal
Prijs
Cd542g
Bureaustoel ZXFashion
6
110,00
Cg452e
Bureau
4
215,10
Totaalbedrag 325,10 Leveringsdatum: 5/10/2003 MAGAZIJNFICHE Artikelnr Cd542g
Leveranciersnr 523
Omschrijving : Bureaustoel ZXFashion Voorraad: 12
Magazijnrek: A12 Schap: 2
Bestellingen Ordernr
Leveringsdatum
Aantal
8254
5/10/2003
6
215
7/10/2003
3
Begrippen rond databases
56/70 LEVERANCIERSINFORMATIE Leverancier: 523 BURO Industrieweg 1 – 9000 Gent Artikelen Artikelnr
Omschrijving
Levertijd
Cd542g
Bureaustoel ZXFashion
2 weken
BhY365
Bureaulamp Flash
Uit voorraad
Cg452e
Bureau
3 weken
Begrippen rond databases
57/70
8.6.5 Overboekingen en rekeningoverzichten De twee informatiebehoeften zijn hieronder afgebeeld.
Normaliseer en integreer deze informatiebehoeften. Maak ook een gegevensstructuurdiagram.
Begrippen rond databases
58/70
9 HET FYSIEKE SCHEMA 9.1
Toegangspaden
Een toegangspadanalyse onderzoekt per informatiebehoefte bij welke entiteit de toegang plaats vindt of met andere woorden met welke entiteit we beginnen, in welke volgorde andere entiteiten erbij betrokken worden, welke attributen er telkens nodig zijn en via welke attributen we van de ene entiteit naar de andere springen. Een voorbeeld De toegangspadanalyse om tot een factuur en een offerte te komen zoals beschreven in 8.2 en 8.3. Entiteit
Attribuut
Informatiebehoefte Factuur
Factuur
Factuurregel
Artikel
Klant
Offerte
Offerteregel
Offerte
Factuurnr
⊗
Factuurdatum
X
Klantnummer
X
Kortingfactuur
X
Factuurnr
X
Artikelnr
X
Aantalartikel
X
Artikelnummer
X
X
Omschrijving
X
X
Eenheidsprijs
X
X
BTW percentage
X
X
Klantnummer
X
X
Naam
X
X
Adres
X
X
Postcode
X
X
Gemeente
X
x
Offertenummer
⊗
Offertedatum
X
Klantnummer
X
Kortingofferte
X
Offertenummer
X
Artikelnummer
X
aantalofferte
X
Het attribuut waarvan we vertrekken wordt omcirkeld.
Begrippen rond databases
59/70 Pijlen duiden aan via welke attributen andere entiteiten opgenomen worden in de informatiebehoefte.
9.2
Indexen
Tijdens het fysieke databaseontwerp wordt ook nagegaan welke indexen er moeten aangemaakt worden.
9.2.1 Wat is een index? Een index kan het zoeken in een tabel aanzienlijk versnellen en lijkt op een index achter in een boek. Op basis van een zoekwoord kun je de juiste nummers vinden van rijen, binnen een tabel. Een index in een relationele database is een een (deel)relatie waarin een tabel op volgorde van een aangegeven kolom (of meerdere kolommen) is gesorteerd. Het doel van een dergelijke index is om de zoeksnelheid te vergroten. Een voorbeeld Een tabel met de dieren uit de zoo Nummer
Dier
Kooinummer
1
Olifant
5
2
Aap
7
3
Zebra
1
4
Giraf
4
Een index op dier zou er als volgt uitzien Dier in alfabetische volgorde
Nummer van het overeenkomstig record in de oorspronkelijke tabel
Aap
2
Giraf
4
Olifant
1
Zebra
3
Als een beer moet gevonden worden zonder dat er een index op dier bestaat, moet het systeem heel de tabel doorlopen alvorens het kan laten weten dat er geen beer is. Als een beer moet gevonden worden met een index op dier moet het systeem alleen de eerste twee records in de index doornemen. Een beer had immers voor de giraf moeten staan. Een index kan dus zoekopdrachten in belangrijke mate versnellen en dat is belangrijk in databases met veel records. Sommige databases hebben miljarden gegevens, en het is wenselijk dat een enkele zoekopdracht er niet toe leidt dat van elk van deze gegevens wordt geëvalueerd of het aan de opgegegeven criteria voldoet. Daarvoor zijn indexen gemaakt. Het aanmaken van een index heeft ook een nadeel: bij elke verandering in de tabel, moet ook de index worden aangepast. Daarom is het belangrijk niet zo maar op
Begrippen rond databases
60/70 elke tabel of elke kolom in een tabel een index te definiëren, maar eerst eens na te denken of het aanmaken van een index ook rendeert.
9.2.2 Welke relaties krijgen een index? Het heeft geen zin een index op een relatie te definiëren als dit niet nuttig is in functie van één of andere selectie of sortering die regelmatig moet gebeuren. Ook met de selectie component van update opdrachten dient rekening gehouden te worden.
9.2.3 Welke attributen krijgen een index? Attributen die regelmatig gebruikt worden bij het formuleren van criteria of waarop regelmatig moet gesorteerd worden, komen in aanmerking voor een index. Ook op een attribuut dat betrokken is in een veel gebruikte relatie kan het nuttig zijn een index te definiëren. Een index op een attribuut waarvan het domein uit weinig verschillende waarden bestaat zal dan weer minder rendement opleveren.
9.3
Oefeningen
Maak een toegangspadanalyse voor elk van de oefeningen uit het vorige hoofdstuk.
Begrippen rond databases
61/70
10 DE ROL VAN SQL 10.1 Wat is SQL? SQL is de meest gebruikte taal om met een database te communiceren. Zo kan je vanuit een programma via een SQL statement enerzijds informatie opvragen uit een database en anderzijds de inhoud van de database aanpassen. SQL beschikt over opdrachten om in de database een tabel aan te maken of te verwijderen, een record aan te passen, aan te maken of te verwijderen en ga zo maar door. SQL is een vrij logische taal en sluit aan bij de Engels taal. SQL evolueert mee met de veranderende behoeften in de databasewereld. Dit blijkt uit de verschillende ANSI/ISO standaards die in de loop der jaren werden vastgelegd. Jaar
Naam
Alias
Kenmerken
1986
SQL-86
SQL-87
Gepubliceerd door ANSI. Bekrachtigd door ISO in 1987
1989
SQL-89
1992
SQL-92
SQL2
Een belangrijke aanpassing
1999
SQL: 1999
SQL3
matching van reguliere expressies, recursieve query’s, triggers, niet scalaire types en beperkte object georiënteerde mogelijkheden.
2003
SQL: 2003
Beperkte aanpassingen
XML verwante eigenschappen, window functies, autonumerieke waarden
Niet alle producten ondersteunen alle mogelijkheden vastgelegd in deze standaards, maar de meeste ondersteunen wel de belangrijkste afspraken.
10.2 Onderdelen van SQL De SQL opdrachten kunnen onderverdeeld worden in een aantal deelverzamelingen naargelang de rol die ze spelen: 2
10.2.1
De data definition language (DDL)
Deze deelverzameling van SQL opdrachten maakt het mogelijk een definitie van een tabel of view te creëren, te verwijderen en aan te passen. Verder voorziet zij ook in de nodige opdrachten om integriteit constraints vast te leggen en om toegangsrechten te bepalen. Enkele sleutelwoorden uit de DDL: CREATE TABLE … PRIMARY KEY … FOREIGN KEY …, CREATE VIEW,…
2
Voor een meer gedetailleerde uitleg over het opbouwen van SQL opdrachten wordt naar de cursus SQL verwezen.
Begrippen rond databases
62/70
10.2.2
De data manipulation language (DML)
In deze deelverzameling van SQL vinden we de nodig opdrachten terug om query’s op te stellen en records toe te voegen, te verwijderen en aan te passen. Enkele sleutelwoorden van de DML: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …, INSERT … INTO … VALUES …, UPDATE … SET …, enz.
10.2.3
Embedded en dynamische SQL
Een relationeel DBMS voorziet in een interactieve interface waar een gebruiker rechtstreeks SQL opdrachten kan invoeren en waar SQL als een taal op zichzelf gebruikt wordt. Deze benadering volstaat zolang de uit te voeren taak volledig met SQL opdrachten kan gerealiseerd worden. In de praktijk doen zich ook situaties voor die enerzijds de grotere flexibiliteit van een programmeertaal vereisen maar anderzijds de opdrachten van SQL om data te manipuleren. Om dergelijke situaties het hoofd te bieden, definieert SQL ook opdrachten die van uit een gast taal zoals C of Java kunnen uitgevoerd worden. Men spreekt dan van embedded SQL. In sommige gevallen is de samenstelling van het SQL statement ook nog afhankelijk van invoer van de gebruiker. Dynamic SQL maakt het mogelijk dat programma’s een SQL statement opstellen en uitvoeren tijdens de uitvoering van het programma (at run time).
10.2.4
Triggers
Sinds de standaard van 1999 ondersteunt SQL ook triggers. Een trigger veroorzaakt dat het DBMS bepaalde acties uitvoert zodra veranderingen aan de database een toestand, bepaald door de trigger, veroorzaken. Een database met een geassocieerde verzameling triggers wordt een actieve database genoemd. De beschrijving van een trigger bestaat uit drie delen: •
Een gebeurtenis: een wijziging van de database die de trigger in gang zet.
•
Een voorwaarde: een query of test die uitgevoerd wordt zodra de trigger geactiveerd wordt.
•
Een actie: een procedure die uitgevoerd wordt als de trigger geactiveerd wordt en de voorwaarde de waarde true oplevert; een query levert de waarde true als de resultaatset niet leeg is..
Afhankelijk van de actie die aan een trigger gekoppeld is, kan het belangrijk zijn dat die actie uitgevoerd wordt voor of nadat er bepaalde wijzigingen doorgevoerd zijn.
Begrippen rond databases
63/70 Een voorbeeld CREATE TRIGGER WHEN
incr count AFTER INSERT ON Students
(new.age < 18)
gebeurtenis voorwaarde, new verwijst naar het nieuw ingevoerd tupel
FOR EACH ROW
de actie
BEGIN
count := count + 1; END
10.2.5
Opdrachten die met beveiliging te maken hebben
SQL voorziet ook in een mechanisme om op basis van gebruikers de toegang tot tabellen en views te controleren. Opdrachten zoals GRANT en REVOKE gaan dan een rol spelen. (zie ook 11.1 Beveiliging)
10.2.6
Opdrachten i.v.m. transaction management
Er zijn in SQL ook opdrachten voorzien die een gebruiker toelaten te bepalen hoe de uitvoering van een transactie moet gebeuren. Hier horen sleutelwoorden zoals START TRANSACTION, COMMIT en ROLLBACK thuis. (zie ook 11.3 Transactiebeheer)
10.2.7
Opdrachten rond een client server architectuur
Een andere reeks SQL opdrachten laten toe te controleren hoe een client toepassing een SQL database server kan benaderen en toegang krijgen tot de gegevens in de database over het netwerk. In een Client-Server systeem bestaan één of meerdere client processen en één of meerdere servers. Een client proces kan een query sturen naar eender welk server proces. Clients dragen de verantwoordelijkheid voor de user interface, servers beheren de data en voeren transacties uit.
Begrippen rond databases
64/70
11 AANDACHTSPUNTEN BIJ HET GEBRUIK VAN EEN DBMS 11.1 Beveiliging Een database van een onderneming bevat erg veel informatie. Het merendeel van de verschillende groepen gebruikers in een onderneming zal slechts toegang tot een beperkt deel van die informatie nodig hebben om zijn werk naar behoren te kunnen uitvoeren. Daarom is het nodig dat een DBMS ook middelen ter beschikking stelt om de toegang tot de informatie te beveiligen. Views kunnen hierbij een handig hulpmiddel vormen. De beveiliging van een database beoogt drie doelstellingen: •
Geheimhouding, ervoor zorgen dat gevoelige gegevens niet zomaar door iedereen kunnen geraadpleegd worden. Zo is het b.v. niet de bedoeling dat werknemers elkaars beoordelingen kunnen raadplegen.
•
Integriteit, niet iedereen mag zomaar alle gegevens kunnen wijzigen. Zo mag een werknemer zijn eigen loon niet kunnen aanpassen, hij mag het wel raadplegen.
•
Beschikbaarheid, gebruikers die bepaalde gegevens nodig hebben bij de uitoefening van hun job, moeten er ook wel degelijk bij kunnen.
In de database wereld wordt die beveiliging op twee manieren gerealiseerd: willekeurig toegangsbeheer (Discretionary Access Control of DAC) en gericht toegangsbeheer (Mandatory Access Control of MAC).
11.1.1
DAC
Toegang tot tabellen of views in de database wordt verleend op basis van de identiteit van de gebruiker en de groepen waar hij lid van is. Met toegang wordt hier ook verwezen naar het type toegangsrecht, lezen, schrijven, wijzigen. Een gebruiker die een tabel creëert beschikt over alle mogelijke rechten op die tabel, ook over het recht om rechten toe te kennen aan andere gebruikers. Een gebruiker die een nieuwe view creëert zal voor die view beschikken over de doorsnede van de rechten die hij heeft op alle tabellen en views waarop de view gebaseerd is. SQL ondersteunt deze vorm om toegang te verlenen via de GRANT en REVOKE opdracht. Rechten die via deze opdrachten kunnen verleend of ingetrokken worden zijn SELECT, INSERT, DELETE, REFERENCES, GRANT. Voor meer details wordt verwezen naar de SQL cursus. In SQL-92 beperkt het verlenen van rechten zich tot gebruikers. Volgens de SQL:1999 standaard kunnen ook rechten aan rollen en dus aan groepen gebruikers toegekend worden. DAC heeft een zwak punt als beveiligingsmethode, het is namelijk onderhevig aan Trojan Horse aanvallen: een gebruiker die geen toegang heeft tot de gegevens van een bepaalde tabel A kan een nieuwe tabel B creëren waar hij wel toegang toe heeft en die via een applicatie die wel aan de gegevens van tabel A kan vullen met de inhoud van tabel A. Zo kan hij via tabel B de inhoud van tabel A toch lezen. Begrippen rond databases
65/70
11.1.2
MAC
Mandatory Access Control (MAC) beveiligt informatie door een gevoeligheidslabel toe te kennen aan die informatie en een toegangsniveau aan de gebruikers. Zodra een gebruiker informatie probeert te benaderen worden de gevoeligheid van de informatie en het toegangsrecht van de gebruiker met elkaar vergeleken en op basis daarvan krijgt de gebruiker al dan niet toegang. De verantwoordelijkheid voor de beveiliging ligt hier dus in handen van de administrator en niet zozeer in handen van de verschillende gebruikers. Het toekennen van een gevoeligheidslabel kan zowel op het niveau van een tabel of view als op het niveau van bepaalde rijen of kolommen. Het gevolg is dat gebruikers met een verschillend toegangsniveau die eenzelfde tabel of view bekijken toch andere rijen en kolommen kunnen te zien krijgen. Over het algemeen is MAC een veiliger mechanisme dan DAC, maar scoort het wat minder op gebied van performantie.
11.2 Gelijktijdige benadering van gegevens Het gelijktijdig benaderen van dezelfde gegevens in een database door twee verschillende gebruikers of toepassingen kan aanleiding geven tot inconsistente resultaten. Een klassiek voorbeeld is twee bankbedienden die gelijktijdig eenzelfde rekening bijwerken naar aanleiding van twee verschillende geldtransacties. Bediende 1 en bediende 2 halen (m.a.w. kopiëren) allebei het huidige saldo op. Bediende 1 voert één geldtransactie uit en registreert het nieuwe saldo. Bediende 2 voert een andere geldtransactie uit en registreert zijn nieuw saldo en overschrijft daarbij het resultaat van bediende 1 zonder er rekening mee te houden. Om dergelijke fouten ten gevolge van onbeperkte toegang te voorkomen, moet een record onmiddellijk vergrendeld worden zodra het opgehaald wordt voor een bewerking. Iedereen die vanaf dan hetzelfde record probeert op te halen om het bij te werken moet de toegang geweigerd worden op basis van de vergrendeling. Eens het bijgewerkte record weggeschreven is of de bijwerking geannuleerd werd, wordt de vergrendeling opgeheven. Dit levert een garantie voor de consistentie van de gegevens.
11.3 Transactiebeheer 11.3.1
Wat is een transactie
Het doorvoeren van een wijziging van gegevens kan gepaard gaan met het uitvoeren van een lijst opdrachten die elk op hun beurt informatie lezen uit en schrijven in de database. Het is dan belangrijk dat er een garantie bestaat dat de database niet in een toestand achterblijft waar slechts een deel van de opdrachten of query’s uitgevoerd is. Dit zou immers corrupte data veroorzaken. Een voorbeeld: het uitvoeren van een betaling in de bankwereld. Het saldo van één rekening moet met een bepaald bedrag verminderd worden en het is belangrijk dat het saldo van een andere rekening met datzelfde bedrag verhoogd wordt. Als er een panne optreedt na het verminderen van het ene saldo, is het belangrijk dat na het herstellen van de panne de toestand in orde gebracht wordt, d.w.z. dat ofwel het saldo van de eerste rekening terug op zijn Begrippen rond databases
66/70 oorspronkelijke waarde komt ofwel het saldo van de tweede rekening alsnog aangepast wordt. Transacties moeten dergelijke situaties, waarin corrupte data kunnen ontstaan, opvangen en ervoor zorgen dat de integriteit van een database behouden blijft. Een transactie is dus een lijst acties en verloopt in drie fasen: 1. Het begin van een transactie wordt bepaald. 2. De verschillende acties (opdrachten) die deel uitmaken van de transactie worden uitgevoerd, zonder dat de resultaten zichtbaar zijn voor de buitenwereld, d.w.z. de wijzigingen worden slechts voorlopig opgeslagen. 3. De transactie wordt afgesloten (committed) en de aanpassingen worden zichtbaar voor de buitenwereld, maar slechts op voorwaarde dat alle opdrachten en handelingen in de transactie tot een goed einde gebracht zijn. Zo niet wordt de transactie geannuleerd en eindigt de database in dezelfde toestand als toen met de uitvoering van de transactie begonnen werd (rollback). Sommige systemen gebruiken de term LUW’s (Logical Units of Work) Databasesystemen die het gebruik van transacties ondersteunen, worden ook wel transactionele databasesystemen genoemd. Vandaag de dag is dat het geval voor de meeste databasesystemen, zoals Microsoft SQL Server, MySQL, Oracle, …)
11.3.2
ACID
Vier eigenschappen, kortweg ACID genoemd, kenmerken transacties. ACID staat voor Atomair (Atomicity), Consistent (Consistency), geïsoleerd (Isolation) en Duurzaam (Durability). Atomair refereert naar het feit dat ofwel alle taken uitgevoerd worden die deel uitmaken van een transactie, ofwel geen enkele. Consistent wijst erop dat de database zowel bij het begin als gedurende, als op het einde van een transactie in een integere toestand is. Het gaat hier dan zowel over de fysieke database (de interne structuur, de indexen, de records, …) als over de logische database (de inhoud is ten allen tijde geldig vanuit het standpunt van een applicatie). Concreet kan dit b.v. betekenen dat als één van de integriteitsregels in de database is dat een voorraad niet negatief mag worden, deze situatie zich voor de buitenwereld ook niet zal voordoen, zelfs niet in de loop van de uitvoering van de transactie. Geïsoleerd betekent dat elke transactie de illusie heeft dat zij de enige in uitvoering is. Geen enkele operatie behalve de transactie zelf zal de data in een tussenliggende situatie kunnen zien. In de praktijk komt het erop neer dat als er twee transacties T1 en T2 tegelijkertijd worden uitgevoerd, het eindresultaat hetzelfde is als bij de uitvoering van T1 gevolgd door de uitvoering van T2 of als bij de uitvoering van T2 gevolgd door de uitvoering van T1. Duurzaamheid garandeert dat eens een gebruiker bericht ontvangt dat de transactie met succes uitgevoerd is, de transactie definitief is en niet meer teruggedraaid wordt, zelfs bij een falen van een systeem.
Begrippen rond databases
67/70
11.3.3
Technieken om ACID te ondersteunen
11.3.3.1
WAL (Write ahead logging)
Wal is een populaire techniek om ACID te ondersteunen Alle wijzigingen aan de database komen eerst in een logbestand, weggeschreven op een betrouwbaar medium, alvorens ze effectief toegepast worden op de database. Zowel undo informatie als redo informatie wordt in dat bestand opgeslagen. Op basis van het logbestand kunnen de uitgevoerde opdrachten van een onderbroken transactie ongedaan gemaakt worden en de opdrachten van een comitted transactie herhaald. 11.3.3.2
Shadow paging
Een page is in deze context een eenheid van fysieke opslag ter grootte van 210 tot 215 bytes. Shadow paging is te vergelijken met de old master-new master batch processing techniek gebruikt in mainframe database systems. De uitvoer van elke batchuitvoering werd twee keer opgeslagen op aparte schijven. Het ene exemplaar werd gebruikt als backup, het andere als startpunt voor de volgende verwerking. Bij shadow paging gebeurt ongeveer hetzelfde, maar dan op het niveau van pages.
Begrippen rond databases
68/70
12 EVOLUTIES 12.1 Web databases De eerste generatie van sites op het Internet waren opgebouwd als een verzameling van HTML documenten. Vandaag maken de meeste sites echter gebruik van DBMS’en om op een snelle en betrouwbare manier antwoorden te leveren op vragen van gebruikers die binnen komen via het Internet. Dit stelt nieuwe eisen aan DBMS’en. Niet alleen moeten zij een groot aantal gelijktijdige aanvragen aankunnen, de aanvragen zelf hebben ook specifieke kenmerken. Er moet gewerkt worden met informatie opgeslagen in verzamelingen ongestructureerde documenten en in documenten gedeeltelijk gestructureerd door gebruik van HTML en XML. Verder zal een opzoekopdracht dikwijls gebaseerd zijn op het meegeven van trefwoorden en zich richten tot databases die verdeeld zitten over verschillende servers.
12.1.1
De architectuur
Het benaderen van een database via een webapplicatie kan op allerlei manieren georganiseerd zijn. In de meeste gevallen zijn zeker drie lagen terug te vinden: •
Een web browser vormt de eerste laag, de gebruiker maakt via de webbrowser duidelijk wat hij verwacht.
•
Een engine die gebruik maakt van een dynamische web technologie zoals CGI, PHP, Java Servlets of Active Server Pages werkt in het midden.
•
De database bevindt zich in de derde laag.
De web browser stuurt een aanvraag naar de middelste laag, die op haar beurt de aanvraag verwerkt en query’s en updates genereert voor de database en het antwoord van de database omzet in HTML formaat bruikbaar voor de webbrowser.
12.1.2
XML
XML gaat hier een alsmaar meer belangrijke rol spelen. Het is een standaard om de inhoud en structuur van een document te beschrijven, m.a.w. XML is een manier om data te beschrijven en een XML bestand kan net zoals een database meteen ook de data bevatten.. XML is gebaseerd op HTML (HyperText Markup Language ) en SGML (Standard Generalized Markup Language). Het is enerzijds eenvoudig genoeg om manipulatie van documenten op een gemakkelijke manier toe te laten (in tegenstelling tot SGML) en anderzijds krachtig genoeg om gebruikers toe te laten eigen document beschrijvingen te definiëren (dat in tegenstelling tot HTML). Het doel van XML is het delen van informatie tussen verschillende systemen, meer bepaald verschillende systemen verbonden via het Internet, te vergemakkelijken. De structuur van een XML document is minder rigide dan die van een relationele database. Toch is er voldoende structuur om bruikbare query’s op te stellen. Daarvoor kan o.a; XQuery gebruikt worden. Begrippen rond databases
69/70
12.1.3
Tekstdocumenten
Het veelvuldig werken met tekstdocumenten op het Internet bracht de nood aan technieken om snel bepaalde woorden of combinaties van woorden terug te vinden in teksten, in de belangstelling. Er wordt gewerkt met boolean query’s en ranked query’s. Een boolean query gaat uit van een lijst woorden opgebouwd uit verschillende delen die met elkaar verbonden zijn door de logische operator and. Elk deel is op zijn beurt opgebouwd uit een aantal woorden verbonden door de logische operator of. Die woorden zijn allemaal synoniemen. Elk deel stelt dus een bepaald begrip voor. Het komt erop neer dat we op zoek zijn naar documenten waarin de verschillende begrippen voorkomen. Er werden ook index technieken ontwikkeld om boolean query’s te versnellen: inverted files en signature files. Een ranked query bestaat ook uit een lijst woorden, maar in het resultaat wordt aan elk gevonden document een quotum meegegeven dat de relevantie van het antwoord in functie van de query weergeeft. Het quotum wordt bepaald aan de hand van allerlei criteria zoals welk percentage van de opgegeven trefwoorden voorkomen in het gevonden documenten, of trefwoorden voorkomen in de titel of alleen in het document zelf, etc.
12.2 Object database systemen Relationele database systemen ondersteunen het gebruik van gegevens waarvan de types slechts uit een beperkte verzameling komen: gehele getallen, datums, strings, …. In nogal wat hedendaagse toepassingsdomeinen moeten ook gegevens van een meer complex type kunnen verwerkt worden, zoals video, audio, .... Een mogelijkheid is hiervoor een beroep te doen op aparte bestanden of gespecialiseerde datastructuren. Denk hierbij aan Computer aided design and modeling (CAD/CAM), multimedia opslag, documenten beheer, … Een DBMS biedt echter veel extra comfort zoals controle op gelijktijdige toegang, herstel van de gegevens na een crash, ondersteuning van indexen, … Daarom worden er ook inspanningen geleverd om DBMS’en te ontwikkelen die meer ingewikkelde datatypes ondersteunen en dat brengt ons bij object database sytemen. Object database systemen kunnen onderverdeeld worden in twee groepen: object georiënteerde database systemen en object-relationele database systemen. In het eerste geval wordt DBMS functionaliteit toegevoegd aan een object georiënteerde programmeertaal en haar omgeving. In het tweede geval worden de types die standaard ter beschikking zijn in het databasesysteem uitgebreid met data structuren, de mogelijkheid zelf nieuwe types te definiëren en overerving.
Begrippen rond databases
70/70
13 COLOFON Sectorverantwoordelijke:
Ortaire Uyttersprot
Cursusverantwoordelijke:
Jean Smits
Didactiek:
Werkgroep basis
Lay-out:
Leyman Eugène
Medewerkers:
Martine Bracke Elise Van Reeth
Versie:
oktober 2006
Nummer dotatielijst:
Begrippen rond databases
