9 Werken met meer tabellen (zie ook query’s) 9.1 Inleiding werkwijze je moet begrijpen waarom in de praktijk een databank meestal opgebouwd wordt met verschillende tabellen die aan elkaar gekoppeld worden. je moet relaties kunnen leggen tussen tabellen, de relaties kunnen wijzigen en verwijderen je moet de relaties kunnen opvragen Voorbeeld: Databank Fitter 2000. Klik op de knop
De volgende relaties tussen velden zijn mogelijk: Één-op-één relatie: 1 waarde van een veld in een tabel komt slechts een keer voor in een andere tabel Één-op-veel relatie: 1 waarde van een veld in een tabel kan meer keren voorkomen in een andere tabel 9.2
Relaties leggen tussen tabellen
9.2.1 Normalisatie Een Access-database kan je definiëren als de geïnformatiseerde realisatie van een toepassing of een project. Een toepassing of project is een globale doelstelling binnen een organisatie. Zo kan je het maken van rapporten binnen een school, het voorraadbeheer in een bedrijf en het bijhouden van voetbaluitslagen met de klassering toepassingen of projecten noemen. De basis van een database wordt uiteraard gevormd door gegevens. In Access zitten deze in tabellen. Deze kunnen echter op velerlei manieren gecombineerd en/of bewerkt worden in query's, formulieren en rapporten. Tabellen zijn namelijk meestal geen geïsoleerde gegevensverzamelingen. In een database hou je uiteraard de gegevens bij die je nodig hebt. Nu komt het er op aan ook niet méér te doen dan dat. Zo vermijd je best om één gegeven op meerdere plaatsen te stockeren of gegevens te bewaren die van andere kunnen afgeleid worden. Om tot een efficiënte opbouw van een database te komen wordt uitgegaan van bestaande documenten. Stel je dus in ons geval voor dat het beheer der handboeken op school (=onze toepassing) moet geautomatiseerd worden. Je verzamelt de bestaande documenten in verband met het handboekenbeheer: boekenlijsten per klas, bestellijsten per uitgeverij, voorraadfiches, etcetera. Elk van deze documenten moet vervolgens genormaliseerd worden. Dit betekent dat je vastlegt hoe de gegevens van het document in een database zullen worden bijgehouden. Dit hoef je niet met de natte vinger te doen. Je kan de regels van CODD volgen. Met de regels van CODD normaliseer je een document. Dit wil zeggen: je legt vast in welke tabel(len) de gegevens van het document terechtkomen. De Access 2000
33
gegevens van één document kunnen inderdaad in meerdere tabellen opgeslagen zijn! Tussen deze tabellen bestaan dan relaties. Nadat alle documenten genormaliseerd zijn moeten de bekomen tabellen geïntegreerd worden. Dit betekent gewoon dat je alle overlappingen moet wegwerken. Vooraf enkele definities: o
Elementaire gegevens zijn gegevens die niet meer kunnen of moeten gesplitst worden. Naargelang de informatiebehoefte kan bijvoorbeeld de naam van een persoon gesplitst worden in voornaam en familienaam of als één gegeven behandeld worden.
o
Procesgegevens zijn deze die uit andere kunnen afgeleid worden. Bijvoorbeeld de prijs inclusief BTW van een product wanneer de prijs exclusief en de BTW-voet gegeven zijn.
o
Herhalende deelverzamelingen: items die meerdere waarden kunnen krijgen op een document zijn herhalende deelverzamelingen of 'repeating groups'.
o
Functionele afhankelijkheid: een gegeven is functioneel afhankelijk van een ander wanneer je door toepassing van een functie op het eerste steeds bij het andere uitkomt. Het begrip 'functie' komt uit de wiskunde en betekent zoveel als 'een bepaalde relatievorm'. Enkele voorbeelden: functie
afhankelijk gegeven
90°
sinus
1
5 BI
klastitularis
A. De Schrijver
9000
woonplaats
Gent
België
hoofdstad
Brussel
Regels van CODD a. Inventariseer alle elementaire gegevens. b. Verwijder alle procesgegevens. c. Kies een primaire sleutel. d. Zonder herhalende deelverzamelingen af en voeg er de primaire sleutel van de originele gegevensreeks bij. Deze items worden in de lijst met elementaire gegevens reeds met een sterretje aangeduid. Deze moeten nu een afzonderlijke reeks vormen omdat uiteraard niet vast te leggen is hoe dikwijls deze gegevens op hetzelfde document herhaald zullen worden. In termen van tabelstructuur uitgedrukt: je weet niet hoeveel velden je moet aanmaken. De punten c en d moeten herhaald worden tot er geen herhalende groepen meer zijn. De gegevens staan dan in de eerste normaalvorm. x
Verwijder de items die slechts afhankelijk zijn van een deel van de sleutel en plaats ze samen met het deel van de sleutel waarvan ze afhankelijk zijn in een nieuwe gegevensreeks. Pas daarop de regels toe vanaf de eerste normaalvorm.
De gegevens staan dan in de tweede normaalvorm. x
Access 2000
Verwijder de attributen die afhankelijk zijn van andere niet-sleutel attributen en plaats ze samen met het deel van het attribuut waarvan ze afhankelijk zijn in een nieuwe gegevensreeks. Pas daarop de regels 34
toe vanaf de eerste normaalvorm. De gegevens staan dan in de derde normaalvorm. De gegevensreeksen krijgen nu een naam en overal waar een herhalende groep of een functioneel afhankelijk gegeven is afgesplitst worden relaties gelegd. opmerking: Soms zijn er meerdere mogelijkheden om een primaire sleutel te kiezen. Het verloop van het normalisatieproces kan dan naargelang de gekozen sleutel verschillen, doch je komt in elk geval bij hetzelfde eindresultaat terecht. 9.2.2
Een-op-veel relaties: Voorbeeld 1
Per uitgeverij wordt een lijst bijgehouden van de boeken die door de school kunnen besteld worden. Deze ziet er als volgt uit:
Het document wordt als volgt genormaliseerd: 1e normaalvorm (1NV) x
inventariseer alle elementaire gegevens
* * * * * *
naam adres postcode gemeente boeknummer vakcode isbn titel auteur eenh_prijs
x
verwijder alle procesgegevens. In ons voorbeeld zijn er geen procesgegevens.
x
kies een primaire sleutel: de naam van de uitgeverij zou een goede keuze kunnen zijn. Je kan eventueel de uitgeverijen ook een nummer toekennen. In de lijst met elementaire gegevens wordt de primaire sleutel onderstreept.
Access 2000
35
x
* * * * * * x
uitgeverijnummer naam adres postcode gemeente boeknummer vakcode isbn titel auteur eenh_prijs
zonder herhalende deelverzamelingen af; Wanneer herhalende deelverzamelingen afgezonderd zijn moet natuurlijk de band met de originele gegevensreeks blijven bestaan. Daarom wordt de primaire sleutel bij de herhalende groep gevoegd. Dit proces moet herhaald worden tot er geen herhalende groepen meer zijn. uitgeverijnummer naam adres postcode gemeente
boeknummer uitgeverijnummer vakcode isbn titel auteur eenh_prijs
De gegevens staan nu in de eerste normaalvorm. 2e normaalvorm (2NV) x
Indien er geen samengestelde primaire sleutels zijn is hier niets te doen.
3e normaalvorm (3NV) x
Het gegeven 'gemeente' is functioneel afhankelijk van het gegeven 'postcode'. 'gemeente' wordt daarom overgebracht naar een nieuwe reeks samen met 'postcode' opnieuw om de band met de originele reeks niet te verliezen. In de nieuwe reeks neem je 'postcode' als primaire sleutel. uitgeverijnummer naam adres postcode
postcode gemeente
boeknummer uitgeverijnummer vakcode isbn titel auteur eenh_prijs
De gegevens staan nu in de derde normaalvorm. 9.2.3
Aanmaken relaties in Access
Je kan het resultaat nu in Access implementeren. De gegevensreeksen worden tabellen. Ieder gegeven wordt dus een veld waarvan je bij de creatie van de tabelstructuur de eigenschappen moet instellen (alleszins het gegeventype). Je geeft vervolgens elke tabel een naam. Het resultaat ziet er zo uit: tblUitgeverijen Veldnaam uitgeverijnr Access 2000
Type Auto-num.
tblPostnummers Veldnaam postcode
Type Tekst
tblBoeken Veldnaam boeknummer
Type Auto-num. 36
naam adres postcode
Tekst Tekst Tekst
gemeente
Tekst
uitgeverijnr vakcode isbn titel auteur eenh_prijs
numeriek Tekst Tekst Tekst Tekst numeriek
Op de lijst die je eigenlijk wilde maken staan gegevens die uit de 3 tabellen afkomstig zijn. Het komt er dus op aan om een verbinding tussen deze tabellen aan te leggen. Zo kan dan de gemeente waarin een uitgeverij is gevestigd opgezocht worden aan de hand van het postnummer. Er wordt dan in de tabel 'tblPostnummers' gezocht naar het postnummer dat bij een bepaalde uitgeverij in de tabel 'tblUitgeverijen' staat. De combinatie van een postnummer en een gemeente wordt dus maar één keer opgeslagen in je database. Dit is erg handig wanneer er tikfouten te verbeteren zijn of wanneer een postnummer verandert. De nodige gegevens worden dus 'samengeraapt' om het gewenste document samen te stellen. Zo ver ben je echter nog niet. Eerst de relaties... is de knop om in het relatiescherm te komen. Druk op de rechtermuisknop en kies 'Tabel weergeven'. Selecteer de tabellen waartussen relaties te leggen zijn. In dit voorbeeld dus tblUitgeverijen, tblPostnummers en tblBoeken.
De primaire sleutels zijn vetgedrukt. Je kan de tabellen verplaatsen door te slepen in de titelbalk (zoals een venster in Windows). Je sleept nu het veld 'uitgeverijnr' uit 'tblUitgeverijen' naar het veld uitgeverijnr' uit 'tblBoeken'. Bij het loslaten van de muisknop zie je dit scherm:
Hier geef je een aantal kenmerken van de gelegde relatie op. Access 2000
37
x
de door jou aangeduide velden waartussen de relatie moet gelegd worden zijn reeds ingevuld linksboven in het venster. Je kan desgewenst verbeteringen aanbrengen. Deze velden mogen maar moeten niet dezelfde naam hebben. Ze moeten uiteraard wel van hetzelfde type zijn. Voor numerieke velden moet ook het subtype gelijk zijn!
x
linksonder zie je het type relatie. In dit voorbeeld 'één-op-veel' of '1:n'. Dit betekent dat de waarde voor het veld 'Uitgeverijnr' in de tabel 'tblUitgeverijen' slecht éénmaal kan/mag voorkomen terwijl dezelfde waarde in de tabel 'tblBoeken' meermaals kan voorkomen. De tabellen waartussen een relatie wordt gelegd worden 'primaire' (tblUitgeverijen) en 'secundaire' (tblBoeken) tabel genoemd. De tabel aan de `een'-kant van twee gerelateerde tabellen in een één-op-veel-relatie is de primaire tabel. Een primaire tabel moet een primaire sleutel hebben en elke record moet uniek zijn.
x
dwing steeds referentiële integriteit af ! Hiermee maak je immers gebruik van een bijzonder nuttige mogelijkheid die in Access geboden om aan invoercontrole te doen. 'referre' is een Latijns werkwoord dat o.a. 'verwijzen' kan betekenen. Het voltooid deelwoord ervan is 'relatum'. Zo zie je dat 'refereren' en 'relatie' verwante woorden zijn. Integriteit versta je hier best als correctheid of geldigheid. Door het afdwingen van referentiële integriteit is het onmogelijk om in het gerelateerde veld van de secundaire tabel waarden te gebruiken die in de primaire tabel niet voorkomen. Je kan dus bij het invoeren/wijzigen van boekgegevens niet verwijzen naar een uitgeverij die niet bestaat. Doe je dit toch dan krijg je een passende foutboodschap.
x
'gerelateerde velden trapsgewijs verwijderen/bijwerken' betekent dat alle records in de secundaire tabel automatisch verwijderd/gewijzigd worden wanneer in de primaire tabel een record wordt verwijderd of wanneer het gerelateerde veld wordt gewijzigd.
Je klikt op 'Maken' en de relatie wordt visueel voorgesteld zoals hieronder.
Verklaring: x
je kan slechts één uitgeverij bijvoorbeeld het nummer 7 geven, doch de school kan meerdere boeken bij deze uitgeverij bestellen.
x
de zwarte blokjes aan de uiteinden van het relatielijntje geven aan dat voor deze relatie referentiële integriteit is afgedwongen.
x
het veld in de primaire tabel moet de primaire sleutel zijn of een unieke index.
x
het veld 'Uitgeverijnr' in 'tblBoeken' (de secundaire tabel) wordt een refererende sleutel genoemd omdat het verwijst naar de primaire sleutel in de primaire tabel.
9.2.4
Joins
Je kan terugkeren naar het venster met de eigenschappen van de relatie door op het lijntje te dubbelklikken. Je kan dan o.a. het 'jointype' instellen. Wanneer een relatie gelegd wordt tussen twee tabellen kunnen de gegevens uit die tabellen samengebracht worden in een query. Hierop kan je dan een formulier of rapport baseren. De vraag is echter welke records uit die beide tabellen moeten opgenomen worden in de query. Je hebt -toegepast op ons voorbeeld- de volgende mogelijkheden: Access 2000
38
