Database Design

In deze les gaan we oefenen met het maken van een datamodel. Binnen software development is het bedenken van een datamodel een belangrijk onderdeel. Het kan worden gezien als het fundament waarop de applicatie wordt gebouwd. Als het datamodel niet goed is dan is het fundament niet juist. Aanpassingen van het datamodel leiden vaak tot ingrijpende en lastige wijzigingen in de code.

Een goede applicatie begint bij een goed datamodel!

Inleiding

In deze les ga je oefenen met het maken van een datamodel, dit heet een ERD.

Het ERD, het Entity Relation Diagram, is een schematische weergave van het datamodel.

Een ERD is een schematische weergave van entiteiten, attributen, relaties en sleutels.

Entiteiten

Dit zijn dingen, mensen, of gebeurtenissen waarover je iets wilt vastleggen. Bijvoorbeeld studenten (mensen) en ziektemeldingen (gebeurtenissen).

Attributen

De attributen zijn de eigenschappen van de entiteiten. Bijvoorbeeld de naam van een student of de datum van een ziektemelding.

Relaties

Relaties de verbinding tussen de entiteiten. Deze kun je herkennen doordat je een zin kan maken die begint met iedere of elk en de zin bevat een werkwoord. In de zin moet je ook iets zeggen over de hoeveelheid. Elke relatie kan je van twee kanten beschrijven, voorbeeld:

- iedere student heeft zich nul. één of meer keren ziek gemeld.
- iedere ziektemelding hoort bij precies één student

Sleutels

Sleutels zijn attributen die nodig zijn voor om de relatie te maken. Elke entiteit heeft een uniek id. Voor de eenvoud noemen we die altijd id (type integer). Dit heet de primary key. De foreign key is een verwijzing naar een primary key uit een andere entiteit.

Hoe maak je een ERD?

Elke opgave bestaat uit een verhaaltje dit wordt ook wel een case genoemd. Lees de case goed door en bepaal van welke entiteiten je gegevens wilt vastleggen. Bepaal van alle entiteiten wat je wilt vastleggen, dit zijn de attributen. Bepaal van alle attributen het datatype (integer, float, date, time, datetime of boolean). Vervolgens bepaal je de relatie tussen de entiteiten en bepaal je de primary keys en foreign keys.

Dus de stappen zijn:

Bepaal van welke entiteiten je gegevens wilt vastleggen in de database.
Bepaal van elke entiteit welke gegevens je wilt vastleggen, dit zijn de attributen en bepaal het datatype per attribuut.
Bepaal de relaties tussen de entiteiten
Bepaal de PK's en FK's.

De datamodellen worden gemaakt in Lucichart, een datamodel kun je in PDF exporteren en op je eigen systeem bewaren.

Om een ERD in Lucichart te maken, maak je een gratis account en zoek je de ERD-template "Database ER Diagram", zie plaatje hierboven.

Naming conventions

Het database design (het ERD) wordt in het Engels gemaakt. Entiteiten en Attributen worden via de Snake Case (in lower case) benoemd, bijvoorbeeld:

Entiteit:  user
Attribuut 1: user_name
Attribuut 2: user_login_count
Attribuut 3: last_login_date
Attribuut 4: password

Let ook op dat entiteiten enkelvoud zijn. Dus bijvoorbeeld user en niet users, of leerlingen niet leerlingen.

Voorbeeld ERD

Dit diagram is in Lucichart gemaakt. Herken je de entiteiten en de attributen? Wat kun je van de relatie zeggen? Wat wordt er vastgelegd in deze database? Herken je de foreign key? Waar verwijst de foreign key naar?

De normalisatie regels

Als je niet in een keer de entiteiten herkent kun je ook alle gegevens in één tabel zetten. Vervolgens ga je deze tabel dan net zo lang splitsen totdat aan de onderstaande regels is voldaan.

Denk je wel alle entiteiten te herkennen, controleer dan ook of je aan aan alle regels vodoet.

Elk veld bevat één gegeven.
Gegevens uit één kolom betekenen hetzelfde.
Elke regel is uniek.
Kolommen mogen niet herhaald worden (veld1, veld2, veld3,…).
“zo min mogelijk” dubbele data (strings), aanpassingen hoef je maar één keer te doen.
Aanpassen van gegevens kan door één veld aan te passen.(ook wel; elke table gaat over één ding).

De regels kan je ook samenvatten: ze zorgen ervoor dat: elk veld op elke regel kan veranderen zonder dat je dan ook allemaal andere gegevens moet wijzigen

Als je jouw database model (jouw tabellen) niet aan alle regels voldoen dan moet je gegevens splitsen. Daarvoor heb je drie normalisatie technieken.

De normalisatie technieken

Kolommen splitsen

Splits een kolom in twee of meer kolommen.

Tabel splitsen (1:N)

Splits een tabel in twee tabellen en verbind de tabellen met door met de foreign key (FK) te verwijzen naar de primary key (PK) van de andere tabel.

Tabel splitsen met koppeltabel (N:M)

Als je een tabel splitst en de relatie is een many to many relatie (N:M) dan heb je een koppeltabel nodig om de twee tabellen te verbinden. In de koppel tabel zitten de FK's die verwijzen naar de PK's van de twee tabellen.

Opgave 1

Lees de tekst door en beantwoord de volgende vragen.

Hoeveel normalisatieregels zijn er?
Hoeveel normalisatie technieken zijn er?
Waarvoor dient een koppeltabel?
Tabellen zijn aan elkaar gekoppeld, wat/hoe zorg je voor deze koppeling?
Zet je voor- en achternaam in één kolom, in twee kolommen of in twee tabellen?
Met welk programma maak jij in jouw examen een ERD?
Een tabel heet "ExamenUitslagen", is dit een juiste naam? Weet jij een betere naam?
Waarom een goed database-ontwerp belangrijk?

Opgave 2

Hieronder zie je een tabel met gegevens over gespeelde voetbalwedstrijden.

Op elke regel staat het thuis-land en gast-land.
De score van biede landen staat in de volgende twee kolommen.
De winnaar is 0,1 of 2. Bij 0 is er geen winnaar, bij 1 is het thuis-land winnaar en bij 2 is het gast-land winnaar.
De laatste twee kolommen laten het aantal kaarten (rode- en gele-) en het aantal penalties zien.

Deze opgave staat in de Excel sheet.

a) Aan welke normalisatieregels voldoet deze tabel niet?
b) Verander de bovenstaade tabel zodat deze wel aan alle normalistie-regels voldoet.

Opgave 3

Kopieer de onderstaande tabel naar Excel (Copy/Paste, of gebruik de Excel Sheet) en normaliseer de gegevens. In de tabel staan studenten die één of meer cursussen volgens. Iedere student heeft een coach en de coach is bereikbaar via het telefoonnummer.

Gebruik de drie normalisatie technieken (kolommen en tabellen splitsen) zodat je aan de zes normalisatieregels voldoet.

Splits de gegevens in aparte kolommen en/of tabellen zodat je aan alle regels voldoet.

Student	Cursus	Coach	telefoon_coach
Kevin Drum	Digitale Vaardigheden, Rekenen, PHP	Ayoub	06 1221 3268
Murvin Drake	PHP, JavaScript	Ayoub	06 1221 3268
John Jones, 1234	C++	Ayoub	06 1221 3268
Sally-Jane Jones	HTML, Pyton	Ayoub	06 1221 3268
David (getrouwd)	Rekenen, PHP	Ayoub	06 1221 3268
Murvin Drake	PHP, JavaScript	Samina	06 2100 3485
Murvin Drake	C++	Samina	06 2100 3485

Opgave 4

In een bibliotheek wil men bijhouden welke klanten welk boek van welke periode tot periode hebben geleend.

Elke klant kan meerdere boeken gelijktijdig lenen. De bibliiotheek heeft soms meerdere exemplaren van één title. Verder wil men de klant een whatsapp kunnen sturen twee dagen voor het verstrijken van de inleverdatum.

Maak een databaseontwerp.

Begin met de onderstaande gegevens (copy paste naar Excel of gebruik de Excel sheet).

klant	boek_id	boek ISBN	Titel	uitgeleend	uitleentermijn	ingeleverd
Caris Villa	234189	817525766-0	De boze wollf	09/06/2021	21
Caris Villa	234288	817333566-1	De lastige wereld	07/05/2021	21	14/05/2021
Caris Villa	134585	817525766-0	Op reis met JJ	28/04/2021	21	29/04/2021
Caris Villa	232269	817528866-3	Het grote feest	04/05/2021	21	25/05/2021
Ayana Tucker	232270	817528866-4	Het grote feest	29/05/2021	35	07/06/2021
Shola Greig	134585	817525766-0	Op reis met JJ	28/05/2021	21

Zet je databaseontwerp in Lucichart.

Opgave 5a

Een recycle bedrijf wil bijhouden uit welke verkoopbare onderdelen een aparaat bestaat. Zo bestaat een mobieltje uit een lithium-ion batterij, scherm en moederbord. Een draadloze koptelefoon bestaat uit een lithium-ion batterij en magneten.
Van elk aparaat wil men een inkoopprijs vasstellen en van alle onderdelen wil men een verkoopprijs bepalen.

Maak een database in Lucichart

Opgave 5b

De inkooprijs van elk aparaat is een richtprijs. Als van een telefoon het scherm bijvoorbeeld is gebarsten dan is deze minder waard.
Het recycle bedrijf heeft een aantal inkopers. Elke inkoper bepaald de werkelijke prijs voor een ingekocht product.

Je wilt dus deze data ook vastleggen in de database van opgave 3a.

Inkoper	Datum	Aparaat	Standaard	Inkoop
Jan	10/May	Espresso aparaat	25,00	27,00
Jan	12/May	koptelefoon	6,00	7,00
Jan	12/May	koptelefoon	6,00	5,00
Jan	12/May	iPhone 6	50,00	45,00
Mimi	12/May	iPhone 6	50,00	40,00
Mimi	13/May	Laptop Lenovo G7800	80,00	85,00
Mimi	13/May	Espresso aparaat	25,00	30,00

Zorg ervoor dat deze data in de juiste tabellen kan worden opgeslagen. Maak het volledige datamodel dat aan alle normalisatieregels voldoet.

De tabel staat ook in de Excel sheet.

Voorbeeld 6

Bij autohandel “Krakkemik” staan er auto's van verschillende merken op het terrein. In hun database staat bijvoorbeeld dat er; een rode Opel Astra uit 1998 voor 1500 euro te koop is, een groene Ford Escort uit 2002 voor 2000 euro en een Peugeot waarvan de prijs 4000 euro is.

Herken jij de entiteit? Van wat wordt hier gegevens vastgelegd?

En wat zijn de attributen van de entiteit? Benoem alle attributen.

Maak het ERD in Lucichart.

opgave 7, ToDo App

Maak een datamodel (ERD) voor een ToDo applicatie. In de Todo applicatie wil je de naam van een ToDo item kunnen vastleggen. Van elk ToDo item wil je de titel kunnen vastleggen, bijvoorbeeld "paspoort verlengen". Verder wil je van elk ToDo item vastleggen wanneer hij is aangemaakt en of hij afgerond is.

Verder wil je de ToDo items opdelen in categorieën. Bijvoorbeeld, de categorie "boodschappen", "privé" of "werk". Van elke categorie wil je vastleggen wanneer deze voor het laatst gewijzigd is.

Maak het ERD in Lucichart.

opgave 8, Restaurant en Corona

In verband met Corona wil het restaurant de "Pittige Stake", een applicatie laten ontwikkelen waarin ze kunnen vastleggen welke klanten bij elkaar aan de tafel hebben gezeten. Op die manier kunnen ze als iemand positief op Corona is getest, bepalen bij wie deze persoon aan tafel heeft gezeten en kunnen deze tafelgenoten worden gevraagd om zich ook te laten testen.

Van elke klant dient de voor- en achternaam en zijn telefoonnummer te worden vastgelegd. Verder moet er worden vastgelegd aan welke tafel de persoon zat en wanneer dit was (datum en tijd). De tafels in het restaurant zijn genummerd van 1 t/m 6 en alleen gasten aan dezelfde tafel dienen te worden gewaarschuwd als een tafelgenoot positief wordt getest op Corona.

Maak het ERD in Lucichart.

opgave 9, Covid-19 test

Het RIVM wil een eenvoudige database waarin ze kunnen zien welke persoon wanneer is getest en wat de uitslag was (positief of negatief). Van alle geteste hoeft alleen de voor- en achternaam en het e-mailadres te worden vastgelegd.

Het RIVM wil ook graag weten of de geteste persoon de Corona app heeft geactiveerd. Als dat zo is dan kunnen ze namelijk via de app andere mensen op de hoogte stellen als zij langere tijd bij een positief getest persoon in de buurt zijn geweest.

Maak het ERD in Lucichart.

opgave 10, Fietsenmaker Snelle Jelle

Fietsenmaker Snelle Jelle wil na een reparatiebeurt zijn klanten per SMS of Whatspapp op de hoogte stellen dat de reparatie klaar is. In dit bericht wil hij ook vertellen hoe hoog de reparatiekosten zijn.

Omdat de veel klanten meer dan één fiets hebben, wil hij van de fietsen ook wat kenmerken vastleggen. Hij wil het merk, model, type en kleur kunnen vastleggen.

Van elke reparatiebeurt wil hij verder vastleggen wanneer het onderhoud plaatsvond, hoe lang de reparatie duurde, wat er is uitgevoerd en de prijs.

Maak het ERD in Lucichart.

opgave 11, Eagle Dev **

Maak het gedeelte van het datamodel van EagleDev waarin van de studenten wordt vastgelegd welke module ze hebben uitgevoerd. Van de studenten wil je de voor- en achternaam, hun email en studentennummer vastleggen en van de modules wil je de naam, de omschrijving, de status en het niveau vastleggen. De status is open, bezig of klaar. En het niveau is opgedeeld in 12 levels, 1 tot en met 12.

Maak het ERD in Lucichart.

opgave 12, garagebedrijf

Garagebedrijf Sneller B.V. wil graag automatisch een orderbon kunnen afdrukken. Op de orderbon moeten de klantgegevens staan en er moet op staan welke artikelen de klant heeft besteld.

Verder wil het garage bedrijf ook vastleggen welke type auto de klant heeft, wat de uitvoering is, wat het kenteken is, wat het bouwjaar is en wat de kleur is. Een klant kan meer dan één auto bezitten.

Maak een database ontwerp (ERD) in Lucichart.