Workshop | Database ontwerp

Leerdoelen

Inzicht in de opbouw van een relationele database.
Inzicht in de overwegingen bij het ontwerpen van een relationele database
een deugdelijke relationele database kunnen ontwerpen.

Wat was een database ook al weer?

Een database (of databank in het Nederlands) is een plek waar je gegevens bewaart.
Voorbeelden van databases zijn dus:

schrijfblokken
kasboeken
agenda's
e.d.

Het grote probleem met bewaren is het zo te bewaren dat je later het bewaarde weer terug kunt vinden

Database Management Systems (DBMS)

In de loop der jaren verschillende systemen bedacht om informatie zo slim mogelijk te bewaren. Doel is telkens om te zorgen dat gebruikers snel benodigde informatie kunnen terugvinden in een grote berg van andere informatie.

In een hierarchische database (v.a. jaren 60) worden gegevens opgeslagen in een groot document, dat hierarchisch is opgebouwd.

bijvoorbeeld:

School
1. Klas1
  1. Leerling Jan Klaasen
  2. Leerling Piet van Dam
  3. Leerling Klaas de Boer
  4. etc
2. Klas2
  1. Leerling Clark Kent
  2. Leerling Lois Lane
  3. Leerling Peter Pan
  4. etc.
3. etc.

Waar zou je een docent het beste in de boom kunnen plaatsen?

In een netwerk database ( v.a. jaren 70) is het bovenstaande concept als het ware uitgebreid met hyperlinks. Hierdoor kan dezelfde informatie op meerdere plaatsen tegelijk in de boom voorkomen en zijn complexe gegevensstructuren mogelijk. De structuur van de database werd keurig vastgelegd in een "schema". Alleen was het erg veel programmeerwerk om gegevens uit de database te halen.

netwerk database

Tegenwoordig worden beide bovenstaande type database systemen nog maar weinig gebruikt.

Voor speciale doeleinden zijn er:

XML databases voor opslag van XML documenten.
Object-oriented databases voor opslag van objecten uit object-georienteerde talen.

Maar verreweg het populairst zijn zogenaamde:

Relationele Database (R-DBMS)

In een relationele database worden lijsten bijgehouden van bij elkaar horende gegevens: de tabellen. Een database is een verzameling van één of meerder tabellen. Een school database zou kunnen bestaan uit een tabel met gegevens over studenten, een tabel met gegevens over leraren, een tabel met klassen, een tabel met lessen, een tabel met cijfers, en dergelijke.

school databse met drie tabellen

In een tabel staan bij elkaar horende gegevens op één regel. Een rij in een database tabel wordt een record genoemd. De gegevens van één student vormen dus één record. Een record bestaat uit verschillende velden. Als je een tabel ontwerpt moet je voor ieder veld aangeven wat de veldnaam is en wat voor soort gegevens er in komen: text, integers, datums, e.d.

inhoud studenten tabel

Om gegevens later beter terug te kunnen vinden krijgt ieder record een veld met een unieke sleutel: de primary key. Deze primary key is uniek voor het hele record en met de primary key op zak kun je dus alle bijbehorende gegevens vinden. Er mogen nooit twee records voorkomen met dezelfde primary key. De velden voornaam, achternaam en geboortedatum zijn niet geschikt als primary key, omdat er meerdere studenten kunnen zijn met dezelfde voornaam, met dezelfde achternaam of met dezelfde geboortedatum. Primary keys zijn bij voorkeur betekenisloze nummertjes (ID's), die (op verzoek) door het database systeem kunnen worden aangemaakt zodra je een record toevoegt. Voorbeelden zijn je studentennummer, je sofi-nummer of een productcode.

Waarom is het beter als een primary key geen betekenis heeft?

In een relationele database worden de gegevens in verschillende tabellen ook aan elkaar gekoppeld door gebruik te maken van deze primary key. In een van de tabellen maak je een veld dat verwijst naar de primary key uit de andere tabel. In het bovenstaande voorbeeld is het veld 'class' waarschijnlijk een verwijzing naar de primary key van de 'classes' tabel. Als je wil weten wie de mentor is van een bepaalde student, dan kijk je eerst in het veld 'class' en zoek je vervolgens in de tabel 'classes' naar het record met als primary key "CMD1K". Het verwijzende veld wordt een foreign key genoemd.

Er zijn verschillende relationele database systemen (server programma's) op de markt:

MS Access	Onderdeel van MS Office. Alleen voor beperkt gebruik.
MySql	Open source database systeem voor algemeen gebruik.
MS SqlServer	Professioneel maar duur database systeem.
Oracle	Erg duur database systeem voor zeer grote databases.

Bij de keuze voor een bepaalde database systeem wordt gelet op:

performance (hoe snel kun je een speld in een hooiberg vinden
beveiliging (hoe zorg je dat een gebruiker alleen de gegevens kan zien of wijzigen die deze gebruiker mag zien)
transacties (hoe zorg je dat je gegevens blijven kloppen als er halverwege een wijziging iets fout gaat)
prijs

SQL

Alle relationele database systemen hebben met elkaar gemeen dat ze SQL (Structured Query Language) begrijpen. SQL is een standaard taaltje, waarmee je

gegevens uit de database kan halen (Query the database): SELECT
gegevens in de database kunt wijzigen: INSERT, UPDATE, DELETE
de database kunt aanpassen: CREATE, ALTER

Op het gebruik van SQL komen we volgende week terug.

Database ontwerp

Goed databaseontwerp (het logisch en efficient ordenen van de te bewaren gegevens) is van essentieel belang. Zodra een database gevuld is met data zijn fouten in het originele ontwerp vaak niet meer mogelijk - en als het nog wel mogelijk is kost migratie door de vele afhankelijkheden erg veel tijd en geld.

Stappenplan:

Bepaal het doel en de scope van je database.

Wat voor soort gegevens moeten er bewaard worden? Hiertoe is het handig in normale taal te beschrijven waar de database over moet gaan. Onderstreep alle zelfstandige naamwoorden in je tekst (de onderstreepte woorden zijn je mogelijk entiteiten). Bijvoorbeeld:

Op een school zitten studenten. Deze studenten zijn ingedeeld in klassen. Elke klas heeft een mentor en meerdere docenten geven les aan één of meerdere klassen. Het schooljaar is opgebouwd uit 4 seizoenen, per seizoen krijgen de studenten een kwartaalcijfer. Dit kwartaalcijfer is het gemiddelde van de tentamens die student gemaakt heeft voor theorielessen, of indien het een praktijkles betreft dan wordt beoordeeld op de aanwezigheid.

Maar ook andere vragen zijn relevant: Wie gaat de database gebruiken? Worden er ook andere databases gebruikt? Moeten alle gegevens in 1 database of in een aantal kleinere databases? Moet het een centrale database worden, een gedistribueerde of een combinatie daarvan?
Verzamel de gegevens die bewaard moeten worden.

Je kan kijken naar de belangrijkste woorden (entiteiten) die je onderstreept hebt. Wat moet je over deze entiteiten weten / bewaren? Bij automatisering van bestaande bedrijfsprocessen kun je ook gebruik maken van bestaande formulieren of briefsjablonen.
Bepaal welke tabellen er moeten komen en gegevens in welke tabel komen.

Dit groeperen van gegevens moet met zorg gebeuren. Overleg met de gebruikers en met mensen met ervaring in databaseontwerp.
Bepaal welke velden (kolommen) er moeten komen en in welke kolom welke gegevens moeten worden bewaard

Bewaar geen gecombineerde gegevens. Deel gegevens op in de kleinst mogelijke eenheid. Het is beter aanspreekvorm, voornaam, middle name, tussenvoegsels, achternaam en titels apart op te slaan, dan in 1 naam veld. Waarom?

Bewaar geen berekende gegevens, zoals (sub)totalen. Waarom?
Bepaal voor elke tabel de unieke sleutel (primary key).

Elke tabel moet een kolom of combinatie van kolommen hebben, waarmee je een rij (record) in je tabel uniek kunt identificeren. Dit kan (zie boven) een bestaand veld (kolom) zijn, een combinatie van velden of een apart toe te voegen nummer zijn.

Bepaal de relaties tussen de tabellen:

Kardinaliteit

Tussen tabellen kunnen verschillende soorten relaties bestaan:

one to one (1:1)

1 op 1 relatie

studentNr	firstName	lastName	gender
01234567	Jan	Klaasen	m
01234568	Piet	van Dam	v

laptopID	merk	type	serienummer	studentNr
357	Fujitsu-Siemens	Amilo	3538860001	01234567
358	Fujitsu-Siemens	Amilo	3538860327	01234568

Als er een dirrecte relatie bestaat tussen één record in de ene tabel en precies één record in een andere tabel, is sprake van een een-op-een relatie: één student heeft altijd één laptop en één laptop is altijd van één student.

Je moet je bij een een op een relatie wel altijd afvragen of het zinvol is om de gegevens in twee tabellen te bewaren of dat ze net zo goed samengevoegd kunnen worden. Soms blijkt namelijk dat de ene entiteit eigenlijk een eigenschap is van de andere entiteit. Als je hebt besloten dat het wel zinvol is om de gegevens in twee verschillende tabellen te bewaren, omdat het twee duidelijk gescheiden entiteiten zijn, moet je nog wel bepalen in welke tabel je een foreign key plaatst naar de primary key uit de andere tabel.

ERD tabellen student en laptop

In het bovenstaande Data Model of Entity Relation Diagram (ERD) staat

bovenaan in het grijs de naam van de tabel,
daaronder onderstreept het veld dat de primary key van de tabel is,
daaronder de namen van de overige velden (kolommen van de tabel) onder elkaar. Van elk veld wordt aangegeven wat voor soort veld het is (text, getal, datum) en of het verplicht is of niet (vet gedrukt of niet),
de relatie tussen de twee tabellen wordt aangegeven door een pijl, die altijd loopt van een foreign key naar een primary key. Aan het begin van de pijl staat een cijfer dat aangeeft hoeveel foreign keys naar deze primary key wijzen. Bij deze pijl staat een 1, zodat precies één laptop verwijst naar één student.

one to many (1:n)

1 op n relatie

Als er een relatie bestaat tussen een record in de ene tabel en meerdere records in een andere tabel, is sprake van een een-op-veel relatie: één klas bestaat uit meerdere studenten, maar één student zit altijd in één klas.

ERD van tabellen klas en student

Er zijn dus meerdere records in de studententabel, waarbij het foreign key veld 'klasID' verwijst naar dezelfde primary key in de Klas tabel. De pijl wijst altijd van veel naar één.

klasID	opleiding	voltijd	leerjaar	klasletter
34	CMD	V	2	E
35	CMD	D	2	A

studentNr	firstName	lastName	gender	klasID
01234567	Jan	Klaasen	m	34
01234568	Piet	van Dam	v	34
01234569	Klaas	de Bruin	m	35

Klassen zonder leerlingen bestaan niet . Er zitten dus altijd één of meer studenten in een klas. Dit wordt aangegeven door "1..*" bij de pijl.

ERD van tabellen student en stad

Andersom bekeken heb je natuurlijk ook veel-op-een relaties. Veel studenten wonen in dezelfde stad, maar een student woont slechts in een stad tegelijk. Er zijn ook steden waar geen studenten wonen. Dit wordt aangegeven door het sterretje: in iedere stad wonen 0 of meer studenten.

studentNr	firstName	lastName	gender	woonplaats
01234567	Jan	Klaasen	m	502
01234568	Piet	van Dam	v	501
01234569	Klaas	de Bruin	m	501

stadID	naam
501	Rotterdam
502	Schiedam

Je zou je kunnen afvragen waarom je de woonplaats niet gewoon in de studenten tabel zou schrijven in plaats van een nummertje dat je eerst moet opzoeken in een zogenaamde lookup-tabel.

Wat zijn de voor- en nadelen van het gebruik van een lookup tabel?

many to many (m:n)

n op m relatie

Vaak heb je ook te maken met een veel-op-veel relatie. Een klas krijgt les van meerder docenten en een docent geeft les aan meerder klassen. Dit levert database technisch een probleem op: je kunt niet in de klas tabel een foreign key opnemen naar meerdere docenten tegelijk en andersom ook niet.

ERD van tabellen klas en docent

Dit wordt opgelost door een speciale tussentabel te maken waarin alle voorkomende combinaties worden genoteerd.

klasID	opleiding	voltijd	leerjaar	klasletter
34	CMD	V	2	E
35	CMD	D	2	A
36

klasID	docentID
34	12346
34	12349
35	12345
36	12345

docentID	docentCode	voorNaam	achternaam
12345	KleHC	Roos	Groenewegen
12346	MobaM	Michiel	Mobach
12347

Als je alle bovenstaande tabellen uit de schooldatabase combineert in een ERD krijg je zoiets:

ERD van school databse

opdracht 1:

maak een lijstje waar je alle bovenstaande relaties verklaart.

verklaar de kardinaliteit? wat voor soort relatie is het?
verklaar het cijfer dat bij de pijl staat.
welke foreign key verwijst naar welke primary key en waarom niet andersom?

relatie	student	stad	verklaring
kardinaliteit	0-n	1	Veel studenten wonen in dezelfde stad, maar een student woont slechts in een stad tegelijk.
cijfer	*		In een stad wonen 0 of meer studenten
foreign key	student.woonplaats	stad.stadID	student.woonplaats is de foreign key die verwijst naar de primary key stad.stadID. Als ik de pijl andersom zou zetten en in de stad tabel een studentennummer zou bewaren, zou er in iedere stad maar een student kunnen wonen

Maak een datbase met testdata om ontwerpfouten op te sporen

werknemer	naam	geboortedatum	afdelingscode	afdelingsnaam	budget	baasnummer	aantal_ondergeschikten
10	Boonstra	19-4-56	A16	Inkoop	10.000	14	0
14	de Jong	1-7-65	A16	Inkoop	10.000	-	2
20	de Vries	21-4-58	A16	Inkoop	10.000	14	0
60	Winter	12-12-61	A16	Algemeen	1.000.000	60	0

Wat is er allemaal mis met deze tabel?

foutieve studenten tabel en deze?

Opdracht 2: ERD Bank

Elke rekening bij de bank heeft klantgegevens (referentienr, naam, adres, status), een rekeningnummer, bedrag (balance)
Er zijn 2 soorten rekeningen: spaarrekening (deposit) en betaalrekening (current)
Klanten mogen meerdere rekeningen hebben.
Een rekeningnummer is uniek.
Renkeningen kunnen door klanten gedeeld worden
Elke klant heeft een uniek referentienummer
Elke rekening hoort bij 1 afdeling van de bank
Afdelingsgegevens van de bank (naam, adres, manager)
De naam van de bankafdeling is uniek

Teken aan de hand van de bovenstaande specificaties een data model (ERD) voor de database van de bank.

controleren met normalisatie-regels:

Normaliseren

Eerste NormaalVorm (1NF)

In elke cel mag slechts 1 waarde staan. Splits de velden op in meerdere kolommen of maak een aparte (lookup)tabel.

werknemer_id	naam	taal	functie
1	Jan Klaasen	Nederlands, Engels	Manager Amsterdam
2	John Doe	English, French	CEO, London
3	Lulletje Rozewater	Nederlands	Manager Lutjebroek

normaliseer bovenstaande tabel...

Tweede NormaalVorm (2NF)

Een veld dat niet tot de primaire sleutel behoort, moet (functioneel) afhankelijk zijn van de hele primaire sleutel en niet van een deel daarvan.

ZWEMBAD (plaatsnaam, naam_bad, aantal_inwoners, lengte_bassin)

normaliseer bovenstaande tabel...
Derde NormaalVorm (3NF)

Velden die niet tot de primaire sleutel behoren, mogen niet (functioneel) afhankelijk zijn van elkaar (van een ander niet tot de primaire sleutel behorend veld).

WERKNEMER (werknemernummer, naam, afdelingscode, afdelingsnaam)

normaliseer bovenstaande tabel...

Opdracht 3: ERD bibliotheek

De plaatselijke bibliotheek komt naar je toe en vraagt je een een computersysteem voor ze te maken. Ze vertellen je dat ze in totaal ongeveer10.000 boeken hebben. Ze voeren zo'n 8.000 verschillende titels: van populaire titels hebben ze meerdere copijen. In de database moet worden bijgehouden welke klant welk boek heeft geleend op welke datum, zodat ze kunnen nazien welke boeken in huis zijn of welke klant te laat is. Daarnaast willen ze bezoekers de mogelijkheid bieden op een publieke computer de collectie te doorzoeken op zoekterm.

Ontwerp voor deze bibliotheek een database en teken het data model (ERD) in Microsoft Visio. Je bent vrij de tabellen, velden en relaties zo te kiezen als jou het beste lijkt, maar beargumenteer wel je keuzes.

Microsoft Visio is voor velen het handigst op database diagrammen in te tekenen. Onder Database>Options>Drivers> kun je de database instellen waarvoor je ontwerpt. Als je dit achterwege laat krijg je de datatypen van MS Access te zien. Helaas wordt de ODBC driver voor MySql niet standaard meegeleverd, waardoor je de MySql datatypen niet ziet. Deze ODBC driver (MyOdbc) kun je downloaden van MySql, waarna je de MySql datatypen wel kunt gebruiken
Onder Database>Options>Document>Table kun je aanvinken dat ook ook het data type moet worden getoond (hoeft niet)
Onder Database>Options>Document>Relationship kun je Cardinality aanvinken zodat er een cijfer bij de pijl wordt gezet. (wel belangrijk voor deze opdracht)