2  Ontwerpen met ERD’s

Belangrijk

Je leest de versie die nog in ontwikkeling is. Dit hoofdstuk is nog een concept; er kan nog veel veranderen.

IntroLeerdoelen

Na het bestuderen van dit hoofdstuk kun je:

• in een beschrijving van een organisatie de entiteiten, attributen en relaties herkennen;
• voor elke relatie de minimale en maximale kardinaliteit bepalen;
• een ERD tekenen en lezen in de kraaienpootnotatie;
• een veel-op-veel-relatie oplossen met een koppelentiteit;
• een ERD volgens vaste regels omzetten naar tabellen met primaire en vreemde sleutels.

In Hoofdstuk 1 heb je gezien dat het ontwerp van de webshop uit vier tabellen bestaat. In dit hoofdstuk leer je hoe je zelf tot zo’n ontwerp komt. Het hulpmiddel daarbij is het entity-relationship-diagram, kortweg ERD.

2.1 De bouwstenen van een ERD

Een ERD bestaat uit drie soorten bouwstenen.

Entiteiten

Een entiteit is “ding” (zaak, persoon, gebeurtenis, …) waarover je gegevens wilt vastleggen. Voorbeelden: Klant, Product, Order. De namen worden in het enkelvoud gegeven, omdat strikt genomen een entiteit een type object beschrijft waarvan je de gegevens wilt vastleggen. Dit is een veelgebruikte conventie, maar geen universele verplichting. Een concrete klant, zoals Jansen, is een instantie van dat type.

Attributen

Attributen zijn de eigenschappen van een entiteit, zoals de naam en woonplaats van een klant of de prijs van een product of de datum van een bestelling. Elke instantie heeft voor elk attribuut één waarde.

Eén attribuut, of een combinatie van attributen, krijgt een bijzondere rol: de sleutel (identificatie). De waarde van de sleutel is voor elke instantie verschillend, zodat je elke instantie ondubbelzinnig kunt aanwijzen. Voor een klant is dat het klantnummer, voor een product het productnummer.

Relaties

Relaties beschrijven hoe entiteiten met elkaar verbonden zijn, zoals een klant plaatst een order.

2.2 Stap 1: entiteiten vinden

Een ontwerp begint bijna altijd met een beschrijving van de werkelijkheid: een gesprek met de opdrachtgever, een procesbeschrijving of een casustekst. Lees de volgende beschrijving van de webshop.

Beschrijving webshop

De webshop verkoopt koffie en koffieaccessoires. Van elke klant wordt een klantnummer, de naam en de woonplaats bijgehouden. Van elk product worden een productnummer, een omschrijving en de actuele prijs vastgelegd. Een klant kan bestellingen plaatsen. Elke bestelling krijgt een uniek ordernummer en een datum. In één bestelling kunnen meerdere producten voorkomen, elk met een eigen aantal.

Een praktische werkwijze is om de zelfstandige naamwoorden in de tekst te markeren. Dat zijn de kandidaat-entiteiten. Daarna stel je bij elk woord de vraag: willen we hierover meerdere gegevens vastleggen, en zijn er meerdere instanties van?

• Klant: ja, er zijn veel klanten en van elke klant leg je nummer, naam en woonplaats vast. Entiteit.
• Product: ja. Entiteit.
• Bestelling: ja, met ordernummer en datum. Entiteit (we noemen hem Order).
• Naam, woonplaats, prijs, datum: dit zijn eigenschappen van iets anders. Attributen.
• Webshop: er is maar één webshop en dat is de organisatie zelf. Geen entiteit.

Of iets een entiteit of een attribuut is, hangt af van wat de organisatie wil vastleggen. Woonplaats is hier een attribuut van Klant. Wil de webshop per woonplaats ook de verzendkosten en de bezorgdienst bijhouden, dan wordt Woonplaats een eigen entiteit met eigen attributen.

Vuistregel: heeft het eigen eigenschappen die je wilt bewaren, dan is het een entiteit.

2.3 Stap 2: attributen en sleutels bepalen

Bij elke entiteit noteer je de attributen. Let daarbij op drie regels.

1. Elk attribuut bevat één waarde. Een klant met twee telefoonnummers krijgt niet één attribuut Telefoonnummers met een lijst erin. Gaat het om een vast, klein aantal, gebruik dan aparte attributen (TelefoonVast, TelefoonMobiel). Kan het er een onbeperkt aantal zijn, maak er dan een eigen entiteit van.
2. Sla geen afgeleide gegevens op. Het totaalbedrag van een order kun je altijd berekenen uit aantallen en prijzen. Wie het ook nog als attribuut opslaat, loopt het risico dat de twee waarden niet meer overeenkomen.
3. Kies een sleutel die uniek en stabiel is. Een naam is geen goede sleutel: er zijn meerdere klanten die Jansen heten. Een e-mailadres is uniek, maar kan veranderen. Een nummer dat de organisatie zelf toekent, zoals een klantnummer, is meestal de beste keuze.

OpmerkingNatuurlijke en kunstmatige sleutels

Een natuurlijke sleutel bestaat al in de werkelijkheid, zoals een ISBN voor een boek of een kenteken voor een auto. Een kunstmatige sleutel (ook wel surrogaatsleutel) wordt speciaal voor de database bedacht, bijvoorbeeld een oplopend volgnummer. Kunstmatige sleutels zijn in de praktijk vaak handiger, omdat ze nooit veranderen.

2.4 Stap 3: relaties bepalen

Relaties vind je in de werkwoorden van de beschrijving: een klant plaatst een bestelling, in een bestelling komen producten voor.

Een relatie tussen twee entiteiten heeft een kardinaliteit: hoeveel exemplaren van de ene entiteit horen bij hoeveel exemplaren van de andere.

2.4.1 Kardinaliteit

De kardinaliteit bestaat uit twee delen: een minimum en een maximum.

Maximale kardinaliteit

Hoeveel instanties kunnen er ten hoogste bij één instantie aan de andere kant horen: één of veel?

Minimale kardinaliteit

Hoeveel instanties moeten er ten minste bij één instantie aan de andere kant horen: nul (de relatie is optioneel) of één (de relatie is verplicht)?

Je bepaalt de kardinaliteit door voor elke relatie vier vragen te stellen, twee in elke richting. Voor de relatie tussen Klant en Order:

De vier vragen voor de relatie tussen Klant en Order

Vraag	Antwoord
Vraag naar maximum: Hoeveel orders kan één klant ten hoogste plaatsen?	Veel
Vraag naar minimum: Hoeveel orders moet één klant ten minste geplaatst hebben?	Nul: een nieuwe klant heeft nog niets besteld
Vraag naar maximum: Bij hoeveel klanten kan één order ten hoogste horen?	Eén
Vraag naar minimum: Bij hoeveel klanten moet één order ten minste horen?	Eén: een order zonder klant bestaat niet

Kijk je alleen naar de maxima, dan kun je drie soorten relaties onderscheiden.

Eén-op-één (1:1)

Bij één instantie aan de ene kant hoort ten hoogste één instantie aan de andere kant. Voorbeeld: een order leidt tot ten hoogste één factuur, en een factuur hoort bij precies één order.

Dit komt relatief zelden voor en duidt er meestal op dat de twee tabellen samengevoegd zouden kunnen worden, tenzij er een specifieke reden is om ze gescheiden te houden (beveiliging, prestaties, optionele gegevens).

Eén-op-veel (1:N)

Bij één instantie aan de ene kant kunnen veel instanties aan de andere kant horen, maar niet andersom. Voorbeeld: een klant plaatst veel orders, een order hoort bij één klant.

Dit is verreweg het meest voorkomende type.

Veel-op-veel (N:M)

In beide richtingen kunnen veel instanties samenhangen. Voorbeeld: een order bevat veel producten, en een product komt in veel orders voor.

Relationele databases kunnen dit niet rechtstreeks weergeven. Je hebt een koppelingstabel nodig die tussen de twee entiteiten komt te staan.

2.5 ERD tekenen

In een ERD teken je een entiteit als een rechthoek. En een relatie wordt weergegeven met een lijn tussen twee entiteiten.

Voor het weergeven van de kardinaliteit worden speciale symbolen aan beide uiteinden van de lijn gebruikt. Hiervoor bestaan verschillende manieren. Zo kun je een één-op-veel (1:N) relatie als volgt weergeven:

┌────────┐       ┌─────────┐
│ Klant  │1─────N│  Order  │
└────────┘       └─────────┘

2.5.1 Kraaienpootnotatie

In dit boek wordt de kraaienpootnotatie gebruikt omdat die in de praktijk het meest wordt gebruikt en omdat ze in één oogopslag zowel het minimum als het maximum laat zien.

De kraaienpoot zelf is het symbool voor veel. Bij invoer via het toetsenbord kun je de accolades } of { gebruiken. Speciale software voor het tekenen van ERD’s gebruikt (of iets wat er op lijkt).

Aan elk uiteinde van een relatielijn staan twee symbolen:

• het symbool het dichtst bij de entiteit geeft het maximum aan: een streepje betekent één, een kraaienpoot betekent veel;
• het symbool het dichtst bij de lijn geeft het minimum aan: een rondje betekent nul, een streepje betekent één.

erDiagram
direction LR
    Klant ||--o{ Order : plaatst

Figuur 2.1: Eén-op-veel-relatie tussen Klant en Order

BelangrijkEen relatie lezen

Een relatie lees je in twee richtingen, telkens beginnend bij één instantie. Neem de relatie tussen Klant en Order (Figuur 2.1).

• Van links naar rechts: Eén klant plaatst nul of meer orders. (Lees de symbolen bij Order.)
• Van rechts naar links: Eén order wordt geplaatst door precies één klant. (Lees de symbolen bij Klant.)

De vier combinaties van symbolen in de kraaienpootnotatie

Symbolen bij de entiteit	Betekenis	Symbolen (links / rechts)
streepje, streepje	precies één	|| / ||
rondje, streepje	nul of één	|o / o|
streepje, kraaienpoot	één of meer	}| / |{
rondje, kraaienpoot	nul of meer	}o / o{

Figuur 2.2 laat de vier combinaties zien. Bekijk telkens het uiteinde aan de rechterkant.

erDiagram
    A ||--|| B : "precies één B"
    A ||--o| C : "nul of één C"
    A ||--|{ D : "één of meer D"
    A ||--o{ E : "nul of meer E"

Figuur 2.2: De vier combinaties van symbolen. Het uiteinde bij de rechter entiteit geeft aan hoeveel instanties daarvan bij één A horen.

OpmerkingAccess notaties

Microsoft Access gebruikt in het relatievenster een eigen notatie: 1 en ∞, zonder minimum.

TipERD software

Er zijn diverse programma’s waarmee je ERD’s kunt tekenen. Hierna een aantal gratis en geschikte tools.

draw.io: Zowel online en als desktopprogramma beschikbaar en geschikt voor het tekenen van ERD’s. Je kunt tabellen, attributen, sleutels en relaties zelf plaatsen. Het programma is bovendien bruikbaar voor andere diagrammen.

ERDPlus: Alleen online beschikbaar, gericht op het leren van ER-diagrammen en databaseschema’s. Daardoor kan het voor beginners een gerichte leeromgeving zijn.

Mermaid: Een op JavaScript gebaseerde tool voor diagrammen en visualisatie die gebruikmaakt van tekst en opmaak (gebaseerd op Markdown) om verschillende soorten diagrammen te genereren en dynamisch te wijzigen in plaats van traditionele tekenprogramma’s. Kort gezegd: je schrijft code/tekst, en Mermaid tekent het diagram voor je. De ERD’s in dit boek zijn hiermee gemaakt.

2.6 Stap 4: veel-op-veel-relaties oplossen

Een veel-op-veel-relatie kun je niet rechtstreeks in een relationele database opslaan. Probeer het maar: waar zou je vastleggen welke producten in order 1001 zitten? Een kolom ProductNr in de ordertabel kan maar één product bevatten, en een kolom OrderNr in de producttabel maar één order.

De oplossing is een koppelentiteit (tussentabel), een nieuwe entiteit die tussen de twee oorspronkelijke entiteiten in komt. De veel-op-veel-relatie wordt zo vervangen door twee eén-op-veel-relaties (Figuur 2.3). Bij de webshop heet de koppelentiteit OrderRegel: elke regel op een order is één product in een bepaald aantal.

erDiagram
    Order ||--|{ OrderRegel : bevat
    Product ||--o{ OrderRegel : "komt voor in"

Figuur 2.3: De veel-op-veel-relatie opgelost met de koppelentiteit OrderRegel

Let op hoe de kardinaliteiten zijn overgegaan. Een order bevat één of meer orderregels, een product komt voor in nul of meer orderregels, en elke orderregel hoort bij precies één order en precies één product. De veel-kanten staan nu allebei bij de koppelentiteit.

Een koppelentiteit is vaak de plek voor gegevens die bij de combinatie horen en niet bij een van beide entiteiten afzonderlijk. Het Aantal is daar een voorbeeld van: het is geen eigenschap van de order en ook niet van het product, maar van dit product in deze order.

BelangrijkPrijs op het moment van bestellen

De actuele prijs is een attribuut van Product. Maar als de prijs volgend jaar stijgt, verandert daarmee ook het bedrag van alle oude orders, en dat is niet de bedoeling. Wil de webshop weten wat de klant destijds heeft betaald, dan hoort er een tweede attribuut Verkoopprijs in OrderRegel. Dit is geen dubbele opslag: het zijn twee verschillende feiten, de prijs nu en de prijs toen.

2.7 Stap 5: van ERD naar tabellen

Als het ERD klaar is, zet je het om naar tabellen. Dat gaat volgens vaste regels. We noteren de tabellen als relationeel schema: de naam van de tabel met de kolommen tussen haakjes. De primaire sleutel is onderstreept; een vreemde sleutel is cursief.

Regel 1: elke entiteit wordt een tabel. De attributen worden kolommen en de sleutel wordt de primaire sleutel.

Regel 2: bij een eén-op-veel-relatie neem je de primaire sleutel van de één-kant op als vreemde sleutel in de veel-kant. De ordertabel krijgt dus een kolom KlantNr. Andersom kan niet: een klant heeft meerdere orders en een kolom kan maar één ordernummer bevatten.

Regel 3: een koppelentiteit krijgt de primaire sleutels van beide entiteiten als vreemde sleutel. Samen vormen die de primaire sleutel van de koppeltabel: dezelfde combinatie van order en product mag maar één keer voorkomen. Je kunt de koppeltabel ook een eigen kunstmatige sleutel geven, zoals een regelnummer.

Regel 4: bij een eén-op-één-relatie neem je de vreemde sleutel op aan de optionele kant. De factuurtabel krijgt een kolom OrderNr, niet andersom: niet elke order heeft een factuur, dus anders zou die kolom vaak leeg blijven. Zijn beide kanten verplicht, overweeg dan of de twee entiteiten niet beter één tabel kunnen vormen.

Regel 5: het minimum bepaalt of de vreemde sleutel leeg mag zijn. Is de relatie aan de één-kant verplicht (een order hoort bij precies één klant), dan moet de vreemde sleutel altijd ingevuld zijn. Is die optioneel (nul of één), dan mag hij leeg blijven.

Toegepast op de webshop levert dit het volgende schema op:

• Klant(KlantNr, Naam, Woonplaats)
• Product(ProductNr, Omschrijving, Prijs)
• Order(OrderNr, Datum, KlantNr)
• OrderRegel(OrderNr, ProductNr, Aantal, Verkoopprijs)

In het ERD kun je de attributen en sleutels ook direct opnemen (Figuur 2.4). Hier is de primaire sleutel aangegeven met PK (Primary Key) en een vreemde sleutel met FK (Foreign Key).

erDiagram
    Klant ||--o{ Order : plaatst
    Order ||--|{ OrderRegel : bevat
    Product ||--o{ OrderRegel : "komt voor in"
    Klant {
        string KlantNr PK
        string Naam
        string Woonplaats
    }
    Order {
        int OrderNr PK
        date Datum
        string KlantNr FK
    }
    OrderRegel {
        int OrderNr PK, FK
        string ProductNr PK, FK
        int Aantal
        decimal Verkoopprijs
    }
    Product {
        string ProductNr PK
        string Omschrijving
        decimal Prijs
    }

Figuur 2.4: Het volledige ERD van de webshop met attributen en sleutels

2.8 Het stappenplan samengevat

1. Entiteiten: markeer de zelfstandige naamwoorden en bepaal welke daarvan entiteiten zijn.
2. Attributen en sleutels: geef elke entiteit haar attributen en kies een unieke, stabiele sleutel.
3. Relaties: markeer de werkwoorden en bepaal met de vier vragen voor elke relatie het minimum en het maximum aan beide kanten.
4. Veel-op-veel oplossen: vervang elke veel-op-veel-relatie door een koppelentiteit met twee eén-op-veel-relaties.
5. Omzetten naar tabellen: pas de omzettingsregels toe en noteer het relationele schema.

Controleer het resultaat ten slotte met de voorbeeldgegevens: kun je elke situatie uit de beschrijving vastleggen, zonder dat gegevens dubbel worden opgeslagen? In Hoofdstuk 3 leer je een systematische manier om dat te toetsen.

2.9 Veelgemaakte fouten

• Attributen als entiteit zien. Bijvoorbeeld een aparte tabel maken voor Adres, want dit is meestal een attribuut van een persoon of een bedrijf, tenzij je adresgeschiedenis wilt bijhouden.
• Vergeten van tussentabellen bij N:M relaties.
• De vreemde sleutel aan de verkeerde kant van een eén-op-veel-relatie zetten.
• Een rapport of overzicht als entiteit opnemen, zoals Omzetoverzicht. Dat is uitvoer die je uit de gegevens berekent.
• Afgeleide attributen opslaan, zoals Totaalbedrag of Leeftijd (sla in plaats daarvan de geboortedatum op).

2.10 Oefeningen

2.10.1 Oefening 1: relaties lezen

Beschrijf elk van de drie relaties in Figuur 2.4 in twee zinnen, één voor elke leesrichting.

- Eén klant plaatst nul of meer orders. Eén order wordt geplaatst door precies één klant.

- Eén order bevat één of meer orderregels. Eén orderregel hoort bij precies één order.

- Eén product komt voor in nul of meer orderregels. Eén orderregel betreft precies één product.

2.10.2 Oefening 2: kardinaliteit bepalen

Bepaal voor elk van de volgende relaties op een hogeschool het minimum en maximum aan beide kanten. Maak dan een ERD schema.

1. Student en studentenpas: elke student krijgt één pas; een pas is van één student. Een nieuwe student heeft nog geen pas.
2. Afdeling en medewerker: elke medewerker werkt bij precies één afdeling; een afdeling heeft minstens één medewerker.
3. Docent en vak: elk vak heeft precies één coördinerend docent; een docent coördineert nul of meer vakken.
4. Student en vak: een student schrijft zich in voor nul of meer vakken; voor een vak schrijven zich nul of meer studenten in.

1. Eén-op-één. Een student heeft nul of één pas, een pas hoort bij precies één student.

erDiagram
    direction LR
    Student ||--o| Studentenpas : heeft

2. Eén-op-veel. Een afdeling heeft één of meer medewerkers, een medewerker hoort bij precies één afdeling.

erDiagram
    direction LR
    Afdeling ||--|{ Medewerker : "heeft in dienst"

3. Eén-op-veel. Een docent coördineert nul of meer vakken, een vak heeft precies één coördinator.

erDiagram
    direction LR
    Docent ||--o{ Vak : coördineert

4. Veel-op-veel.

erDiagram
    direction LR
    Student }o--o{ Vak : "schrijft zich in voor"

Deze veel-op-veel relatie kun je oplossen met een koppelentiteit Inschrijving.

erDiagram
    direction LR
    Student ||--o{ Inschrijving : doet
    Vak ||--o{ Inschrijving : heeft

2.10.3 Oefening 3: fietsverhuur

Lees de volgende beschrijving en maak een ERD en een relationeel schema.

Een fietsverhuurbedrijf verhuurt fietsen aan klanten. Van elke klant worden een klantnummer, naam en telefoonnummer vastgelegd. Elke fiets heeft een uniek fietsnummer, een type (stadsfiets, e-bike, tandem) en een dagprijs. Bij een verhuur wordt een verhuurnummer, een begindatum en een einddatum geregistreerd. Eén klant kan bij een verhuur meerdere fietsen tegelijk huren, bijvoorbeeld voor het hele gezin. Natuurlijk wordt een fiets in de loop van de tijd vaak verhuurd.

Entiteiten: Klant, Fiets en Verhuur. Tussen Verhuur en Fiets bestaat een veel-op-veel-relatie, die wordt opgelost met de koppelentiteit VerhuurRegel.

erDiagram
    Klant ||--o{ Verhuur : sluit
    Verhuur ||--|{ VerhuurRegel : omvat
    Fiets ||--o{ VerhuurRegel : "wordt verhuurd in"
    Klant {
        int KlantNr PK
        string Naam
        string Telefoon
    }
    Verhuur {
        int VerhuurNr PK
        date Begindatum
        date Einddatum
        int KlantNr FK
    }
    VerhuurRegel {
        int VerhuurNr PK, FK
        int FietsNr PK, FK
        decimal Dagprijs
    }
    Fiets {
        int FietsNr PK
        string Type
        decimal Dagprijs
    }

Relationeel schema:

• Klant(KlantNr, Naam, Telefoon)
• Fiets(FietsNr, Type, Dagprijs)
• Verhuur(VerhuurNr, Begindatum, Einddatum, KlantNr)
• VerhuurRegel(VerhuurNr, FietsNr, Dagprijs)

De dagprijs staat twee keer in het schema, net als de prijs bij de webshop: in Fiets de actuele prijs, in VerhuurRegel de prijs die bij deze verhuur gold. Wie aanneemt dat de prijs nooit verandert, kan het attribuut in VerhuurRegel weglaten.

Een goede variant is een aparte entiteit FietsType met daarin de dagprijs, als alle fietsen van hetzelfde type dezelfde prijs hebben.

2.10.4 Oefening 4: wat is er mis?

Een student heeft het volgende ERD gemaakt voor een webshop. Noem minstens vier fouten en geef aan hoe je ze herstelt.

erDiagram
  direction LR
    Klanten ||--o{ Order : plaatst
    Order }o--|{ Product : bevat
    Klanten {
        string Naam PK
        string Telefoonnummers
    }
    Order {
        int OrderNr PK
        date Datum
        decimal Totaalbedrag
    }
    Product {
        string ProductNr PK
        string Omschrijving
        decimal Prijs
        int OrderNr FK
    }

1. Klanten staat in het meervoud; dat moet Klant zijn.

2. Naam is geen geschikte sleutel, want namen zijn niet uniek. Voeg een klantnummer toe als sleutel.

3. Telefoonnummers bevat meerdere waarden in één attribuut. Gebruik één attribuut Telefoon, een vast aantal aparte attributen, of een eigen entiteit.

4. Totaalbedrag is afgeleid uit aantallen en prijzen en hoort niet opgeslagen te worden.

5. Order heeft geen vreemde sleutel KlantNr, terwijl de relatie met Klant dat vereist.

6. De veel-op-veel-relatie tussen Order en Product is niet opgelost. De vreemde sleutel OrderNr in Product is een poging daartoe, maar daarmee kan elk product maar in één order voorkomen. Voeg de koppelentiteit OrderRegel toe met OrderNr, ProductNr en Aantal, en verwijder OrderNr uit Product.