Van ERD naar SQL

Van ERD naar SQL#

Een goed ERD is het resultaat van de ontwerpfase. Daarna is het tijd voor de realisatie: het fysieke ontwerp. In deze fase ga je de tabellen en de relaties tussen deze tabellen daadwerkelijk in SQL-code omzetten.

Notitie

Het diagram in deze verdieping is in een oudere stijl getekend, met entiteitnamen in hoofdletters, * voor verplichte en o voor optionele attributen (zie Meer over entiteiten en attributen).

Bekijk het onderstaande ERD.

ERD van een elektronische leeromgeving

Controleer eerst twee dingen:

  • Zit er geen redundantie (dubbel opgeslagen gegevens) meer in?

  • Zijn alle meer-meerrelaties opgelost?

Wanneer je antwoord op beide vragen ja is, dan kun je doorgaan met het fysieke ontwerp.

Om hier SQL-code van te maken, ga je als volgt te werk:

  1. Elke entiteit wordt een tabel.

  2. Identificeer de primaire sleutels (de UID). Deze geef je dikgedrukt weer.

  3. Identificeer de vreemde sleutels (foreign keys: verwijzingen naar andere primaire sleutels). Deze geef je weer tussen < en >.

  4. Dit levert je een relationeel model op.

  5. Het relationele model zet je om in SQL-DDL.

Stap 1 tot en met 4#

Het bovenstaande ERD geeft je het volgende relationele model:

  • leerling(ll_nr, naam, geboorte_datum, foto, klas)

  • volgt(<ll_nr>, <vakcode>)

  • vak(vakcode, naam)

  • opdracht(opdrachtcode, titel, opdrachttekst, deadline, <vakcode>)

  • maakt(<opdrachtcode>, <ll_nr>, URL, inleverdatum, inlevertijd, punt)

Merk op dat:

  • de tabel maakt een primaire sleutel heeft die bestaat uit twee vreemde sleutels.

  • er nog niet aangegeven staat welke attributen optioneel of verplicht zijn.

Stap 5#

Een relationeel model kun je eenvoudig omzetten naar SQL. Let op dat je nu dus een keuze maakt voor het soort database: wij kiezen voor een relationele database met SQL, maar andere opties zijn er ook, zoals NoSQL-oplossingen.

Tot nu toe ben je vooral bekend met SQL-DML. Dit staat voor SQL Data Manipulation Language. Met dit deel van SQL kun je gegevens opvragen, veranderen, invoeren of verwijderen uit een database. Je kent dit als de SELECT ... FROM-statements.

Het deel van SQL waar je nu in gaat werken heet SQL-DDL. Dit staat voor SQL Data Definition Language. Kort gezegd kun je met deze SQL-statements tabelstructuren definiëren en aanpassen. Het relationele model is uitstekend geschikt om naar SQL-DDL te vertalen. De entiteit leerling vertaalt zich naar het volgende SQL-DDL statement:

CREATE TABLE leerling (
    ll_nr           CHAR(6)     NOT NULL,
    naam            VARCHAR(20) NOT NULL,
    geboorte_datum  DATE        NOT NULL,
    foto            BLOB,
    klas            VARCHAR(5)  NOT NULL,
    PRIMARY KEY(ll_nr)
);

Je vertelt de database letterlijk om een tabel aan te maken met de naam leerling, die bestaat uit de velden ll_nr, naam, geboorte_datum, foto en klas. Het veld ll_nr is bovendien de primaire sleutel. Elk veld geef je ook een passend type. Voor een volledig overzicht van toegestane types in SQL, bekijk dit overzicht. Met NOT NULL geef je aan dat een veld verplicht is en dat NULL niet toegestaan is als waarde. Met andere woorden: het veld mag niet leeg zijn. Uit je ERD is al duidelijk welke waarden verplicht of optioneel zijn. Deze informatie kun je dus overnemen in je SQL-DDL.

Een tabel met vreemde sleutels, zoals maakt, vertaal je op vergelijkbare wijze. Er komen alleen twee vreemde sleutels bij in de specificatie.

CREATE TABLE maakt (
    opdrachtcode    INTEGER     NOT NULL,
    ll_nr           CHAR(6)     NOT NULL,
    url             VARCHAR(50),
    inleverdatum    DATE,
    inlevertijd     TIME,
    punt            NUMERIC(3,1),
    PRIMARY KEY(opdrachtcode, ll_nr),
    FOREIGN KEY(opdrachtcode) REFERENCES opdracht,
    FOREIGN KEY(ll_nr) REFERENCES leerling
);

Net zoals je aangeeft welke velden de primaire sleutel vormen, zo geef je aan welke velden een vreemde sleutel vormen; hiermee maak je aan de database duidelijk welke relaties er in je tabelstructuur zitten. Bij een FOREIGN KEY geef je niet alleen een veld uit die tabel aan, maar ook een verwijzing naar een andere tabel. Dit doe je met het keyword REFERENCES.

Belangrijk

Een vreemde sleutel moet precies hetzelfde type hebben als de primaire sleutel waarnaar hij verwijst!

Indien een vreemde sleutel dezelfde naam heeft als een al bestaand veld in de tabel, hernoem dan de vreemde sleutel. Een naam kan in een tabel maar één keer gebruikt worden.

Laatste belangrijke opmerking: elk SQL-statement sluit je af met een ;. Dat geldt eigenlijk ook voor queries in SQL-DML, maar daar wordt meestal niet zo streng op gelet ;-)

Opdrachten#

Oefening 16

Maak de CREATE TABLE definities voor de tabellen volgt, vak en opdracht uit het voorbeeld hierboven.

Oefening 17

Bekijk het volgende ERD.

ERD van een fietsenwinkel

Opmerkingen:

  • Type, merk, naam en producent zijn korte stukjes tekst als “Gazelle”, “City Explorer”, “Terrain beast”

  • De staat is: goed, redelijk of slecht

  • De prijs heeft twee cijfers achter de komma

  • Het veld nieuw geeft aan of een fiets nieuw is of tweedehands.

a) Verwijder indien nodig meer-meerrelaties.

b) Maak een relationeel model van dit ERD.

c) Zet dit relationele model om naar SQL-DDL.