Relationele databases: het relationele model

Een klantnaam, een besteldatum en een prijs zijn gegevens: vastgelegde feiten. Een database is een geordende verzameling samenhangende gegevens. Een databasemanagementsysteem, of DBMS, is de software die die verzameling opslaat en beheert. PostgreSQL is een DBMS; de database store van een winkel is één verzameling die het beheert.

Sla elk gegeven op waar het hoort

Stel dat een spreadsheet het telefoonnummer van Maria Garcia bij alle 14 verkopen aan haar herhaalt. Als haar telefoonnummer verandert, zijn 14 aanpassingen nodig. Eén overgeslagen cel leidt tot tegenstrijdige gegevens. In een relationele database bevat customers Maria's gegevens één keer, onder customer_id 1. Elke verkoop legt die identificatiewaarde vast. Als je haar telefoonnummer in één klantrij bijwerkt, leveren latere zoekopdrachten één actueel nummer op.

Een relationele database ordent gegevens in tabellen. “Relationeel” komt van relatie, de wiskundige naam voor een tabel. E. F. Codd introduceerde het model in 1970. De centrale scheiding is nuttig: gebruikers beschrijven via tabellen en waarden welke gegevens ze willen; het DBMS regelt hoe die gegevens op schijf worden gevonden.

Regels en gedeelde toegang

Een tabel heeft benoemde kolommen, gegevenstypen en integriteitsregels. Een integriteitsregel is een regel die het DBMS controleert, zoals “een bestelling moet naar een bestaande klant verwijzen”. Deze controles beschermen gedeelde gegevens wanneer meerdere toepassingen dezelfde database gebruiken. De handhaving van typen verschilt: PostgreSQL weigert tekst zoals 'twelve' in een numerieke prijskolom, terwijl gewone SQLite-tabellen flexibele typen hebben.

Een DBMS beheert ook gelijktijdige gebruikers, rechten, back-ups en herstel. Twee medewerkers kunnen met gedeelde gegevens werken zonder afzonderlijke kopieën van een spreadsheet uit te wisselen. Rechten kunnen een rapportagegebruiker verkoopgegevens laten lezen en tegelijk beperken wie ze mag wijzigen. Een proces voor back-ups en herstel helpt gegevens na een storing terug te zetten.

Houd een groep wijzigingen bij elkaar

Een transactie behandelt meerdere wijzigingen als één geheel: alle worden opgeslagen of geen ervan. Om € 200 tussen rekeningen over te boeken, moet het bedrag van de betaalrekening worden afgetrokken en bij de spaarrekening worden opgeteld. Als een storing de overboeking onderbreekt voordat die is voltooid, kan het DBMS de onvoltooide transactie terugdraaien. Als alleen de afschrijving wordt opgeslagen, blijft een onjuist saldo over.

Een spreadsheet blijft nuttig voor kleine berekeningen en flexibele indelingen. Een database biedt een vastgelegde structuur voor gedeelde gegevens en regels. Een Excel-werkblad heeft een vaste limiet van 1.048.576 rijen; databases beheren regelmatig veel grotere verzamelingen, afhankelijk van opslag, hardware en productlimieten.

Verschillende gegevens en verschillende taken

Gestructureerde gegevens passen in benoemde kolommen die alle rijen delen. Semigestructureerde gegevens, zoals JSON-documenten, kunnen in verschillende records verschillende velden hebben. Ongestructureerde gegevens, zoals audio of vrije e-mailteksten, missen die vaste tabelindeling. Organisaties combineren relationele databases vaak met documentopslag, sleutel-waardeopslag of bestandsopslag.

Ook de werklast is van belang. Een transactioneel systeem, ook OLTP genoemd, registreert dagelijkse gebeurtenissen via veel kleine lees- en schrijfbewerkingen: een bestelling, een betaling, een adreswijziging. Een analytisch systeem, of OLAP, leest veel opgeslagen records om vragen te beantwoorden zoals “Hoe verschilde de maandelijkse verkoop per regio?” Beide kunnen relationele tabellen en sleutels gebruiken.

SQL, afkorting van Structured Query Language, is een taal. PostgreSQL, MySQL, SQLite, Microsoft SQL Server en Oracle Database zijn producten die deze taal gebruiken. SQL Server is één DBMS, geen andere naam voor SQL zelf.

Hoe een toepassing de gegevens bereikt

In een client-serversysteem beheert een serverprogramma de database. Een client, zoals een website, rapportagetool of query-editor, stuurt verzoeken naar die server. PostgreSQL, MySQL, SQL Server en Oracle werken meestal op deze manier. Met tools zoals psql, pgAdmin, DBeaver en SQL Server Management Studio kunnen mensen databases inspecteren en verzoeken versturen.

Een ingebedde engine draait binnen een toepassing. SQLite gebruikt een bibliotheek en een gewoon databasebestand, zoals store.db, zonder aparte databaseserver. Het komt veel voor in telefoons, browsers en desktopapps. Bij een beheerde clouddienst, zoals Azure SQL Database, Amazon RDS of Google Cloud SQL, beheert een aanbieder de databaseservers. “Beheerd” beschrijft wie de dienst beheert; het vervangt tabellen en sleutels niet door een ander model.

Eén DBMS-installatie kan meerdere databases beheren, zoals store en payroll. Een verbinding selecteert de te gebruiken database, afhankelijk van de rechten.

Geef aan welk resultaat je wilt

SQL is declaratief: het beschrijft het gewenste resultaat, terwijl het DBMS kiest hoe het dat verkrijgt. Lees deze instructie:

SELECT first_name, city
FROM customers
WHERE state = 'TX';

De instructie vraagt om voornamen en woonplaatsen uit klantrijen waarvan de staat TX is. Ze zegt niets over welke schijfpagina eerst moet worden gelezen. Trefwoorden in hoofdletters maken de instructie gemakkelijker te overzien; select en SELECT betekenen hetzelfde. Een puntkomma beëindigt normaal gesproken een instructie.

Herken de taak van de instructie

Sommige boeken plaatsen SELECT in een aparte groep voor gegevensopvraagtaal. Het nuttige onderscheid is de werkelijke taak van de instructie: een kolom toevoegen verandert de structuur; een klant toevoegen verandert de gegevens.

SQL werd in 1986 een ANSI-standaard en is internationaal gestandaardiseerd als ISO/IEC 9075. Producten voegen hun eigen dialecten toe. Een limiet van tien rijen schrijf je als LIMIT 10 in SQLite, PostgreSQL en MySQL, TOP 10 in SQL Server of FETCH FIRST 10 ROWS ONLY in standaard-SQL en Oracle.

Lees tekst en andere databaseobjecten

Tekstliterals gebruiken enkele aanhalingstekens: 'TX'. Standaard-SQL gebruikt dubbele aanhalingstekens voor identificatoren, zoals kolomnamen. Een apostrof binnen tekst wordt verdubbeld: 'O''Brien' stelt O'Brien voor.

Een view is een opgeslagen query die als een tabel wordt gebruikt; een gewone view heeft zelf geen opgeslagen rijen. Een index is een opzoekstructuur die kan helpen om snel rijen te vinden. De meeste systemen maken automatisch een index op primaire sleutels. Opgeslagen procedures en functies zijn opgeslagen code die je met hun naam kunt uitvoeren.

Een tabel slaat gegevens op over één soort zaken. Elke rij in customers beschrijft één klant; elke kolom bevat één eigenschap. Op hun snijpunt staat een waarde.

Lees één record van links naar rechts

Hier zijn enkele klantkolommen:

Zoek customer_id 4 en lees dan city: Wei woont in Seattle. Om te vinden wie in Denver woont, lees je de naam in de rij van Denver: James Wilson. Een naastgelegen rij beschrijft een andere klant.

De volledige tabel heeft acht kolommen: customer_id, first_name, last_name, email, phone, city, state en zip_code. Het telefoonnummer van James is NULL: de waarde ontbreekt, maar zijn rij heeft nog steeds de kolom voor het telefoonnummer.

Koppel de begrippen

Een tupel is een hele rij, niet één cel. Een attribuut benoemt een eigenschap die alle rijen delen. “Record 4” kan een identificatiewaarde of een positie in de weergave betekenen; controleer welke, want posities kunnen veranderen.

Tel de onderdelen

De graad telt kolommen; de tabelkardinaliteit telt rijen. De volledige tabel heeft graad 8 en kardinaliteit 4. De weergave hierboven toont zes kolommen zonder de graad van de tabel te veranderen.

Als je een klant toevoegt, stijgt de kardinaliteit naar 5; de graad blijft 8. Als je birth_date toevoegt, stijgt de graad naar 9 en krijgt elke rij een plaats voor die waarde, ook als die ontbreekt. Een nieuwe klant voegt een rij toe, geen kolom.

Relatiekardinaliteit, zoals een-op-veel, beschrijft toegestane koppelingen in plaats van het aantal rijen van een tabel.

Lees gekwalificeerde namen

Kolomnamen zijn uniek binnen een tabel. orders.customer_id en customers.customer_id onderscheiden gelijknamige kolommen in verschillende tabellen: de punt scheidt tabel- en kolomnamen.

snake_case gebruikt woorden in kleine letters, gescheiden door underscores, zoals in zip_code. Namen met spaties moeten in SQL tussen aanhalingstekens staan. Tabelnamen in het enkelvoud of meervoud kunnen beide werken; gebruik een consequente afspraak.

Een relationele tabel heeft een logische structuur die verder gaat dan haar rechthoekige uiterlijk. In het formele model is ze een verzameling rijen. Verzamelingen bevatten geen dubbele elementen en hebben geen volgorde. Kolommen worden herkend aan hun naam, dus als je een kolom in een weergave verplaatst, verandert het gegeven dat ze vertegenwoordigt niet.

Identiteit komt niet voort uit positie

Een DBMS kan rijen in een andere volgorde teruggeven na wijzigingen, verwijderingen of een verandering in de manier waarop het de opslag leest. “De derde rij” is geen blijvende manier om een klant te identificeren. customer_id 3 identificeert Aisha nog steeds wanneer haar rij als eerste verschijnt. Een query die een bepaalde volgorde nodig heeft, vraagt daar met ORDER BY om.

SQL-tabellen verschillen van formele relaties: zonder primaire sleutel of een andere geschikte uniciteitsregel kunnen ze identieke rijen bevatten. Twee kopieën van ('Coffee mug', 12.50) bieden in die kolommen geen onderscheidende waarde. Een primaire sleutel geeft elke rij een unieke identificatiewaarde en voorkomt volledig dubbele rijen.

Eén kolom, één domein

Een domein is de verzameling waarden die in een kolom zijn toegestaan. Een hoeveelheid heeft een type voor gehele getallen en kan ook een regel nodig hebben die bepaalt dat ze positief moet zijn. Een staatcode is tekst, maar niet elke tekstwaarde is een geldige staatcode. Gegevenstypen en integriteitsregels beschrijven de toegestane waarden aan het DBMS.

Alle rijen delen dezelfde kolommen. Eén bestelling kan niet alleen voor zichzelf een extra kolom voor een cadeaubericht krijgen. De tabel kan voor elke bestelling een kolom gift_note hebben, met NULL waar geen bericht is vastgelegd. Optionele gegevens hebben nog steeds een vaste plaats.

Houd afzonderlijke waarden gescheiden

Een atomaire waarde is één waarde voor het doel van de kolom. Een telefooncel met “512-555-0147, 512-555-0199” bevat een lijst van twee nummers. Het wordt lastig om slechts één nummer te vinden, te controleren of te verwijderen. Een tabel customer_phones kan elk telefoonnummer een eigen rij geven, waarbij customer_id aangeeft van wie het nummer is.

Atomair betekent niet dat elke tekstwaarde in de kleinste stukjes moet worden opgesplitst. Een stadsnaam kan uit meerdere woorden bestaan en toch één stadswaarde blijven. De vraag is of de cel één gegeven van het soort bevat dat de kolom vertegenwoordigt, of een verzameling die afzonderlijk moet worden verwerkt.

Bekijk een export uit een spreadsheet kritisch

Een werkblad met verkopen kan halverwege een herhaalde kop bevatten, onderaan een rij met het label “TOTAAL” en een prijs die is geschreven als “12,50 (aanbieding)”. Dit zijn nuttige indelingskeuzes voor een persoon, maar ze mengen verschillende soorten inhoud in dezelfde kolommen.

Om het als een relationele tabel te lezen:

1. Beschouw de kolomnamen als kop, niet als verkooprecords.
2. Laat elke gegevensrij over één verkoop gaan; bereken het totaal op basis van die rijen.
3. Sla de numerieke prijs apart van een aanbiedingsaanduiding op.
4. Geef elke verkoop een sleutel zodat de rij kan worden geïdentificeerd.

Een totaalrij tussen verkopen kan als een nieuwe verkoop worden geteld of opnieuw bij het totaal worden opgeteld. Een aanduiding in een numerieke prijs verhindert gewone numerieke bewerkingen.

Logische tabellen en fysieke opslag

Een tabel is de logische weergave voor de gebruiker. Het DBMS kan de gegevens opslaan in bestanden, pagina's en indexen die zijn ingericht voor efficiënte toegang. Gegevensonafhankelijkheid betekent dat wijzigingen in die fysieke indeling de tabelnamen, kolombetekenissen of verzoeken van toepassingen niet hoeven te veranderen.

Een gegevenstype vertelt een DBMS wat voor soort waarde een kolom bevat en hoe het daarmee moet werken. Getallen optellen, tekst vergelijken en datums ordenen zijn verschillende bewerkingen. Een vertrouwde typenaam kan zich ook verschillend gedragen in verschillende databaseproducten.

Getallen: geheel, exact en benaderend

In PostgreSQL, MySQL en SQL Server loopt INTEGER van −2.147.483.648 tot 2.147.483.647. BIGINT reikt tot 9.223.372.036.854.775.807; de opslag voor gehele getallen van SQLite kan dat bereik ook halen.

Bij decimalen is precisie het totale aantal cijfers en schaal het aantal achter de komma. DECIMAL(8, 2) laat zes cijfers voor de komma over: het maximum is 999999,99. De prijs 12,50 past; 1234567,00 niet. PostgreSQL, MySQL, SQL Server en Oracle bieden exacte decimale opslag. SQLite accepteert deze declaratie maar dwingt de precisie of schaal niet af en kan breuken opslaan als binaire drijvendekommagetallen.

Drijvendekommaweergave benadert veel decimale breuken. Met dubbele precisie levert het optellen van 0,1 en 0,2 de uitkomst 0,30000000000000004 op. SQLite behandelt deze decimale literals als drijvendekommagetallen. PostgreSQL behandelt decimale literals zonder expliciet type als exact numeriek, zodat dezelfde geschreven berekening daar exact kan zijn; expliciete waarden met dubbele precisie laten de benadering zien.

Tekst, datums en indicatoren

CHAR(n) is tekst met een vaste lengte; PostgreSQL en SQL Server vullen kortere waarden aan met spaties. VARCHAR(n) staat wisselende lengtes tot een limiet toe. SQLite negeert beide lengte-instellingen en vult niet aan. TEXT bevat lange tekst in SQLite, PostgreSQL en MySQL. SQL Server gebruikt hiervoor VARCHAR(MAX) of NVARCHAR(MAX); het oudere type TEXT is verouderd.

Een DATE bevat een kalenderdatum, een TIME een tijdstip en standaard-SQL TIMESTAMP een datum en tijd. Schrijf datums in de volgorde jaar-maand-dag, zoals '2026-03-02', om verwarring tussen 2 maart en 3 februari te voorkomen. Datums met een datumtype hebben een interne opslagnotatie; de toepassing kiest de weergavenotatie. SQLite heeft geen specifiek datumtype en slaat gewoonlijk ISO-datumtekst op. SQL Server gebruikt DATETIME2 voor datum en tijd: zijn TIMESTAMP betekent ROWVERSION, een binaire versieteller.

Een BOOLEAN stelt TRUE of FALSE voor, of NULL als de waarde ontbreekt. PostgreSQL heeft een Booleaans type; SQL Server gebruikt BIT, MySQL behandelt BOOLEAN als een klein geheel getal en SQLite slaat de indicatoren op als 1 en 0. Binaire typen zoals BLOB of BYTEA van PostgreSQL slaan bytes op, waaronder kleine afbeeldingen. Grotere bestanden worden vaak apart opgeslagen, met een pad of URL in een tabel.

Typeaffiniteit van SQLite

In gewone SQLite-tabellen is een gedeclareerd type een voorkeur die typeaffiniteit heet. Een kolom INTEGER zet '42' om naar het getal 42, maar kan 'abc' als tekst behouden. Een SQLite-tabel STRICT weigert tekst die niet kan worden omgezet naar een geldig geheel getal.

Begin bij wat een waarde betekent en hoe die wordt gebruikt. Een ZIP-code bevat cijfers, maar niemand telt ZIP-codes op om een betekenisvol totaal te krijgen. Een hoeveelheid bevat ook cijfers en hoeveelheden optellen is wel nuttig. Alleen het uiterlijk is een slechte leidraad voor de typekeuze.

Stel vijf vragen

1. Wat zijn gebruikelijke waarden en welke extreme waarden moeten passen?
2. Worden de waarden gebruikt om te rekenen?
3. Moeten ze exact zijn of is een benadering acceptabel?
4. Hoe moeten ze worden gesorteerd en vergeleken?
5. Welke grootte past bij realistische toekomstige waarden?

Geld en metingen

Geld vereist exacte waarden. In PostgreSQL, MySQL en SQL Server kan een prijs DECIMAL(8, 2) gebruiken. Drie artikelen van € 12,50 kosten precies € 37,50. Binaire drijvendekommaopslag kan na herhaald optellen kleine fouten achterlaten, waardoor exacte vergelijkingen onbetrouwbaar worden. In SQLite levert de declaratie DECIMAL geen exacte decimale opslag op; gebruik gehele centen voor exacte bedragen, zodat € 12,50 wordt opgeslagen als 1250.

Een meting zoals gewicht kan REAL gebruiken als kleine afrondingsverschillen acceptabel zijn. Kies de eenheid als onderdeel van de betekenis van de kolom: weight_lb laat in de naam zien dat het om ponden gaat. Een numeriek type alleen legt die eenheid niet vast.

Ook de valuta moet duidelijk zijn. Als een tabel verschillende valuta's bevat, voeg dan currency_code toe, met bijvoorbeeld USD, EUR of JPY. Het aantal decimalen verschilt per valuta en bedragen in verschillende valuta's moeten worden omgerekend voordat je ze kunt optellen.

Codes en namen zijn tekst

Gebruik tekst voor ZIP-codes, telefoonnummers, rekeningnummers en voorraadcodes. De ZIP-code van Boston '02108' verliest de eerste nul als je die omzet naar een geheel getal. VARCHAR(10) kan ook ZIP+4 bevatten, zoals '02108-1234'. Telefoonnummers kunnen een plusteken, spaties en koppeltekens bevatten. Deze tekens maken deel uit van de schrijfwijze van een identificatiewaarde, het zijn geen rekentekens.

Laat ruimte voor echte namen en adressen. “Christopher Montgomery” heeft 22 tekens, dus VARCHAR(10) is te kort. PostgreSQL en SQL Server weigeren de naam; MySQL weigert die in de strikte modus en kan die anders afkappen. SQLite slaat de naam op omdat gedeclareerde tekstlengtes worden genegeerd. Een ruim veld met variabele lengte voorkomt dat gewone namen worden geweigerd, zonder voor elke rij de maximale lengte te reserveren.

Sorteren onthult een verkeerde keuze

Bij het sorteren van tekst worden tekens vergeleken. Getallen die als tekst zijn opgeslagen, kunnen in de volgorde '1', '10', '2', '25', '3' verschijnen. Een numerieke kolom sorteert die waarden numeriek. Als je voor elk veld tekst kiest, verschuif je het controle- en conversiewerk naar latere berekeningen.

Datums hebben een vergelijkbare valkuil. Datumtekst in Amerikaanse notatie plaatst '12/01/2025' na '03/02/2026' in tekstvolgorde, hoewel december 2025 eerder is. Gebruik een datumtype, of in SQLite consequent opgemaakte ISO-tekst, zodat de kalendervolgorde behouden blijft.

Bekijk de typen van een product samen

Een product kan product_id INTEGER, sku VARCHAR(20), name VARCHAR(100), price DECIMAL(8, 2), weight_lb REAL, in_stock BOOLEAN en added_on DATE gebruiken. Elk type volgt uit een functie: identificatie, opslag van een code, een naam, exact geld, een meting, een indicator of een datum. Sla voor SQLite de prijs op als gehele centen en de datum als ISO-tekst.

NULL markeert een ontbrekende waarde. Het verschilt van nul, lege tekst '' en de tekst 'NULL'. Een bekende korting van 0 betekent geen korting; een korting die NULL is, betekent dat het bedrag niet is vastgelegd. Een kolom die NULL toestaat, laat ontbrekende gegevens toe, terwijl NOT NULL een waarde vereist.

Ontbrekend heeft meer dan één betekenis

Van James Wilson is geen telefoonnummer vastgelegd. Bestelling 102 heeft geen verzenddatum omdat die nog niet is verzonden. Bij een bedrijf als klant is een achternaam mogelijk niet van toepassing. Alle gevallen kunnen met NULL worden weergegeven, maar NULL legt niet vast welke reden geldt. Een gegevenswoordenboek moet de betekenis per kolom vermelden.

Lege tekst is een aanwezige tekstwaarde in PostgreSQL en SQLite. Oracle behandelt lege tekst momenteel als NULL. Querytools kunnen beide weergeven als een cel die leeg lijkt, dus alleen aan het uiterlijk kun je ze niet onderscheiden.

Vergelijkingen kunnen onbekend zijn

SQL-vergelijkingen hebben drie mogelijke resultaten: TRUE, FALSE en UNKNOWN. Een vergelijking met NULL, waaronder NULL = NULL, is UNKNOWN. Van twee ontbrekende gegevens is niet bekend of ze dezelfde waarde hebben.

Een WHERE-clausule behoudt alleen resultaten die TRUE zijn. Lees deze voorwaarden:

• phone IS NULL vindt ontbrekende telefoonnummers.
• phone IS NOT NULL vindt vastgelegde telefoonnummers.
• phone = NULL levert UNKNOWN op en vindt geen rijen.

Het ontkennen van een vergelijking levert een ontbrekende waarde niet alsnog op. Voeg Daniel Brown, van wie de staat NULL is, toe aan de vier klanten van de winkel. De voorwaarde state <> 'TX' behoudt CO, MA en WA: drie rijen. Daniel valt af omdat de vergelijking van zijn staat UNKNOWN is. Om ook ontbrekende staten mee te nemen, is de voorwaarde state <> 'TX' OR state IS NULL.

Combineer voorwaarden zorgvuldig

Denk bij UNKNOWN aan “kan TRUE of FALSE zijn”. Een resultaat staat pas vast als beide mogelijkheden dezelfde uitkomst geven.

Berekeningen en samenvattingen

Gewone rekenkundige bewerkingen met NULL geven NULL: 5 + NULL is NULL. Een regelprijs vermenigvuldigd met een ontbrekende hoeveelheid ontbreekt ook, die is geen nul. Tekst samenvoegen met || en NULL geeft NULL in PostgreSQL en SQLite; bij concatenatie behandelt Oracle NULL als lege tekst.

Aggregatiefuncties slaan NULL-invoer meestal over. Bij vijf klanten, onder wie James en Daniel zonder telefoonnummer, telt COUNT(*) alle vijf rijen. COUNT(phone) telt de drie vastgelegde telefoonnummers. Het gemiddelde van 4, NULL en 2 is 3, omdat de twee bekende getallen door twee worden gedeeld; als je NULL als nul behandelt, krijg je ten onrechte 2.

Gebruik NOT NULL voor gegevens die aanwezig moeten zijn, zoals een besteldatum. Dit dwingt geen positief getal of niet-lege tekst af: dat zijn aparte regels. Primaire sleutels vereisen een identificatie die niet NULL is; gewone SQLite-tabellen hebben een historische uitzondering voor sommige declaraties van primaire sleutels, dus expliciet NOT NULL is daar van belang.

Een sleutel identificeert een rij via haar waarden. Namen kunnen vaker voorkomen; klantidentificatiewaarden onderscheiden records zodat je ze kunt lezen, wijzigen of koppelen.

Toets uniciteit en minimaliteit

Een supersleutel is een kolom of combinatie die een rij uniek identificeert. Een kandidaatsleutel is een minimale supersleutel: als je een kolom verwijdert, vervalt die garantie. Kies één kandidaat als primaire sleutel; de andere zijn alternatieve sleutels.

Stel dat employee_id, ssn en email elk verplicht en uniek zijn. Elk is een kandidaatsleutel. (employee_id, department) is een supersleutel maar geen kandidaatsleutel: employee_id alleen identificeert de medewerker al. Een achternaam die vaker voorkomt, doorstaat de uniciteitstest niet.

Vier klanten met verschillende achternamen bewijzen niet dat er een sleutel is: een vijfde kan dezelfde naam hebben. Uniciteit moet gelden voor alle toegestane toekomstige gegevens. Een herhaling weerlegt een voorgestelde sleutel; een voorbeeld zonder herhalingen heeft nog steeds een ondersteunende bedrijfsregel nodig.

Lees de declaratie

Een tabel heeft maximaal één primaire sleutel. De waarden zijn uniek en de kolommen mogen geen NULL zijn. Stabiele waarden voorkomen herhaalde wijzigingen van verwijzingen. Zoek naar PRIMARY KEY: noch de naam noch de positie van een kolom declareert die als sleutel.

Als klant 1 bestaat, wordt een andere klant met sleutel 1 geweigerd. Een weggelaten sleutel heeft een standaardwaarde of gegenereerde waarde nodig. INTEGER PRIMARY KEY van SQLite genereert een geheel getal als de waarde wordt weggelaten of als NULL wordt opgegeven. Sommige andere declaraties van primaire sleutels in gewone SQLite-tabellen staan om historische redenen NULL toe; expliciet NOT NULL, een STRICT-tabel of de vorm met een gehele primaire sleutel sluit die mogelijkheid uit.

Lees een samengestelde sleutel

In enrollments(student_id, course_id, term, grade) kan een student meerdere cursussen volgen en er één in een andere periode opnieuw volgen. (student_id, course_id, term) identificeert één inschrijving: één samengestelde primaire sleutel, geen drie afzonderlijke primaire sleutels.

Afzonderlijke onderdelen mogen worden herhaald; de volledige combinatie niet. De meeste DBMS'en maken een index op de primaire sleutel om gegevens op te zoeken, maar de sleutel garandeert geen volgorde in queryresultaten.

Een natuurlijke sleutel komt uit gegevens uit de echte wereld, zoals het VIN van een voertuig, het ISBN van een boekuitgave of een staatcode. Een surrogaatsleutel bestaat alleen om een rij te identificeren, vaak als een gegenereerd geheel getal of een UUID.

Kies een identificatiewaarde die meegaat

Kies bij voorkeur waarden die uniek, aanwezig, stabiel, compact en veilig om te delen zijn. Namen komen vaker voor; telefoonnummers en e-mailadressen veranderen of worden gedeeld. Social Security-nummers zijn gevoelig en voor sommige klanten niet beschikbaar; als je ze in verwijzende tabellen gebruikt, verspreid je die informatie.

Met een gegenereerde customer_id behoudt Maria identificatiewaarde 1 wanneer haar contactgegevens veranderen. Een natuurlijke state_code, zoals TX, kan goed werken omdat die compact, gestandaardiseerd en nuttig in adresgegevens is.

Bescherm bedrijfsidentificaties afzonderlijk

Dezelfde persoon kan zich twee keer registreren en twee surrogaatsleutels krijgen. De identificatiewaarden onderscheiden rijen; ze sporen geen dubbele personen op.

Als een e-mailadres elk account moet identificeren, gebruik dan NOT NULL UNIQUE. Alleen UNIQUE staat meerdere NULL-waarden toe in PostgreSQL, SQLite en MySQL. SQL Server staat normaal gesproken één NULL toe bij een uniciteitsregel voor één kolom. Aanwezigheid en uniciteit zijn aparte regels.

Het verplichte, unieke e-mailadres is een alternatieve sleutel. Als het verandert, blijft het klantnummer als surrogaatsleutel in bestaande bestellingen ongewijzigd. Als een primaire sleutel waarnaar wordt verwezen verandert, moeten de verwijzingen vanuit de kindtabel ook consistent blijven.

Herken de syntaxis voor gegenereerde sleutels

PostgreSQL heeft ook de oudere vorm SERIAL. Verwijderingen, mislukte invoegingen en geannuleerd werk kunnen gaten achterlaten. Identificatiewaarden 1, 2 en 4 duiden drie rijen aan, geen vier. Hernummeren om gaten te sluiten kan verwijzingen verbreken.

Herken UUID's

Een UUID is een identificatiewaarde van 128 bits, meestal geschreven als 36 tekens met koppeltekens: 7c9e6679-7425-40de-944b-e07fc1f90ae7. Computers kunnen ze onafhankelijk genereren met een zeer klein risico op dubbele waarden, wat nuttig is wanneer records van verschillende plaatsen worden samengevoegd.

UUID's nemen meer ruimte in en zijn moeilijker te lezen dan kleine gehele getallen. Kies op basis van hoe records worden gemaakt en waarnaar wordt verwezen. Beide soorten sleutels zijn labels: hun maximale waarde is geen aantal rijen en hun volgorde hoeft niet het tijdstip van gebeurtenissen weer te geven.

Een refererende sleutel is een kolom of groep kolommen die verwijst naar een primaire sleutel of een andere geschikte unieke sleutel. De tabel met de verwijzing is de kindtabel; de tabel waarnaar wordt verwezen is de oudertabel. Een refererende sleutel die niet NULL is, moet overeenkomen met een bestaande sleutel in de oudertabel wanneer de integriteitsregel wordt gehandhaafd.

Volg een waarde tussen tabellen

orders.customer_id verwijst naar customers.customer_id. Bestelling 102 wijst naar klant 2, James Wilson. Bestellingen 101 en 103 wijzen allebei naar klant 1, Maria Garcia. Herhaling is toegestaan in de refererende sleutel; de sleutel waarnaar in de oudertabel wordt verwezen, is uniek.

Deze regel is referentiële integriteit. Een bestelling voor klant 99 wordt geweigerd als klant 99 niet bestaat. Anders zou het een verweesde rij zijn: een rij in de kindtabel zonder overeenkomende rij in de oudertabel. Een naam alleen maakt geen integriteitsregel. Een refererende sleutel met de naam placed_by kan nog steeds naar customer_id verwijzen; de gedeclareerde verwijzing maakt de koppeling, met overeenkomende typen voor de sleutelkolommen.

Optioneel of verplicht

Een coupon_id die NULL toestaat, kan NULL zijn wanneer een bestelling geen kortingsbon heeft. Een customer_id die als NOT NULL is gedeclareerd, vereist dat elke bestelling een klantverwijzing heeft, en de refererende sleutel vereist dat die klant bestaat. Deze twee integriteitsregels werken samen: de ene vereist een waarde en de andere controleert de verwijzing.

Refererende sleutels hoeven niet naar een andere tabel te verwijzen. In employees(employee_id, name, manager_id) kan manager_id verwijzen naar employee_id in dezelfde tabel. De verwijzing naar een leidinggevende kan voor de algemeen directeur NULL zijn.

Een wijziging in de oudertabel vereist een regel

Bij de standaardactie kan klant 1 niet worden verwijderd zolang bestellingen 101 en 103 nog naar haar verwijzen. Cascadering van een bestelling naar haar regels kan zinvol zijn omdat die regels bij die bestelling horen. ON UPDATE geeft het overeenkomstige gedrag aan wanneer een sleutel in de oudertabel verandert. Cascadering betekent niet dat het verwijderen van een rij in de kindtabel de bijbehorende rij in de oudertabel verwijdert.

Samengestelde verwijzingen en handhaving

Als een orderregel wordt geïdentificeerd door (order_id, product_id), heeft een verzending die naar die regel verwijst beide kolommen nodig. De combinatie moet overeenkomen met een bestaande regel. Afzonderlijk een overeenkomst voor een bestelling en een product vinden is onvoldoende: dat product kan nooit in die bestelling zijn voorgekomen. Een verplichte samengestelde verwijzing declareert al haar kolommen als NOT NULL.

PostgreSQL, SQL Server, Oracle en de gebruikelijke InnoDB-engine van MySQL handhaven gedeclareerde refererende sleutels. Schakel in SQLite voor elke verbinding de handhaving van refererende sleutels in met PRAGMA foreign_keys = ON; en controleer of die is ingeschakeld voordat je erop vertrouwt. Een gedeclareerde maar uitgeschakelde integriteitsregel kan de rijen niet beschermen. Refererende sleutels combineren ook niet zelf queryresultaten; een query moet de koppeling expliciet volgen, meestal via een join.

Om een relatie te bepalen, stel je twee vragen: “Hoeveel rijen daar kunnen aan één rij hier worden gekoppeld?” en vervolgens de omgekeerde vraag. Neem zowel het minimum als het maximum mee. De huidige voorbeeldgegevens laten zien wat er is gebeurd; de integriteitsregels beschrijven wat is toegestaan.

Eén klant, veel bestellingen

Een klant kan nul of meer bestellingen plaatsen. Elke bestelling in de winkel hoort bij precies één klant omdat orders.customer_id een refererende sleutel is die niet NULL is. Dit is een-op-veel. De refererende sleutel hoort in orders, de veel-kant: elke bestelling slaat één klantverwijzing op.

Maria heeft bestellingen 101 en 103; James heeft bestelling 102. Aisha en Wei hebben er geen. Dat zij niet in orders voorkomen, is geldig. Een refererende sleutel vereist voor elke rij in de kindtabel een rij in de oudertabel, maar vereist niet dat elke rij in de oudertabel een rij in de kindtabel heeft.

Een-op-een vereist ook uniciteit

Laat in user_profiles(user_id, bio, photo_url) de user_id zowel de primaire sleutel als een refererende sleutel naar users zijn. Elk profiel verwijst naar één gebruiker en een gebruiker kan maximaal één profiel hebben omdat de profielsleutel niet herhaald kan worden. Het is mogelijk dat een gebruiker nog geen profiel heeft.

Een refererende sleutel zonder uniciteit zou meerdere profielen voor dezelfde gebruiker toestaan en zo een-op-veel opleveren. De uniciteitsregel verandert het maximum. Zulke aparte profieltabellen kunnen optionele of gevoelige gegevens apart van het hoofdrecord van de gebruiker bewaren.

Veel-op-veel gebruikt een tabel met paren

Een bestelling kan meerdere producten bevatten en een product kan in meerdere bestellingen voorkomen. Een koppeltabel, ook wel verbindingstabel of associatieve tabel genoemd, legt elk paar vast:

order_id verwijst naar orders; product_id verwijst naar products. De samengestelde primaire sleutel (order_id, product_id) staat hetzelfde product in verschillende bestellingen toe, maar voorkomt dat hetzelfde paar twee keer voorkomt. Twee een-op-veelkoppelingen vormen samen de veel-op-veelrelatie.

quantity en unit_price horen bij het paar. Ze beschrijven hoeveel van dat product in die bestelling is verkocht en welke prijs toen in rekening is gebracht. Een actuele productprijs kan later veranderen; die mag het historische bedrag niet overschrijven.

Voor bestelling 101 zijn de twee regelbedragen twee mokken van € 12,50, samen € 25,00, en één oplader van € 8,99. Samen kosten ze € 33,99. De hoeveelheid geeft aan hoeveel mokken zijn gekocht; twee dubbele mokregels opslaan zou in strijd zijn met de gekozen sleutel van deze tabel.

Vermijd lijsten en vaste plaatsen

Een cel product_ids met “11,12” verbergt meerdere verwijzingen binnen één tekstwaarde. Kolommen zoals product1, product2 en product3 leggen een vaste grens op en laten ongebruikte plaatsen over. Een koppeltabel voegt een rij toe voor elk extra paar en laat het DBMS elke verwijzing afzonderlijk controleren.

Een zelfverwijzende relatie gebruikt hetzelfde patroon binnen één tabel: veel medewerkers kunnen via manager_id naar dezelfde leidinggevende verwijzen. De leidinggevende is ook een medewerkersrij. Lees elke koppeling als een rol, zodat de identiteit van de medewerker en de verwijzing naar de leidinggevende gescheiden blijven.

Een schema beschrijft tabellen, kolommen, gegevenstypen en integriteitsregels. Een databasetoestand bestaat uit de gegevens die op een bepaald moment zijn opgeslagen. Een bestelling toevoegen verandert de databasetoestand; een nieuwe kolom toevoegen verandert het schema. Het schema vertelt wat kan worden opgeslagen, terwijl de rijen vertellen wat nu is opgeslagen.

Lees een korte beschrijving

customers(customer_id, first_name, last_name, email, phone, city, state, zip_code) geeft een tabel en haar kolommen weer. In notities worden primaire sleutels soms onderstreept of met PK aangeduid en verwijzingen met FK. Zo geeft orders(order_id PK, customer_id FK → customers, order_date, shipped_date) de belangrijkste sleutel en de klantkoppeling aan.

Lees een volledige tabeldefinitie

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  order_date DATE NOT NULL,
  shipped_date DATE
);

Lees één kolom tegelijk. order_id is de primaire sleutel als geheel getal. customer_id is verplicht en moet naar een klant verwijzen. order_date is verplicht. shipped_date mag NULL zijn. Deze definitie geeft de toegestane structuur, geen lijst van bestaande bestellingen.

Integriteitsregels beantwoorden verschillende vragen:

Een standaardwaarde vervangt geen opgegeven ongeldige waarde. Als status de regel DEFAULT 'pending' heeft, levert het weglaten van de status 'pending' op; een opgegeven waarde moet nog steeds aan de andere regels van de kolom voldoen.

Combineer regels voor een orderregel

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders (order_id),
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(8, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

De primaire sleutel onderaan gebruikt twee kolommen. Een tweede regel voor dezelfde bestelling en hetzelfde product wordt geweigerd. Hoeveelheid 0 wordt door de controle geweigerd. Een ontbrekende hoeveelheid voldoet niet aan NOT NULL: de controle alleen zou NULL toestaan omdat NULL > 0 UNKNOWN is, niet FALSE. Expliciet NOT NULL op beide sleuteldelen voorkomt ook de historische uitzondering van SQLite voor samengestelde sleutels die NULL toestaan. Gebruik in SQLite gehele centen voor een exacte unit_price; de decimale declaratie alleen biedt geen exacte opslag.

Zoek het schema en de betekenissen

In de SQLite-opdrachtregel toont .schema orders de definitie; in psql van PostgreSQL beschrijft \d orders die. INFORMATION_SCHEMA-views tonen structurele metadata in PostgreSQL, MySQL en SQL Server. SQLite vermeldt objecten in sqlite_schema en grafische tools tonen tabellen en kolommen in een boomstructuur.

“Schema” is ook de naam voor een naamruimte binnen systemen zoals PostgreSQL en SQL Server. sales.orders betekent de tabel orders in het schema sales. Een gegevenswoordenboek geeft betekenissen die de structuur niet kan uitdrukken, zoals de valuta van een prijs of de vraag of een ontbrekende verzenddatum “niet verzonden” betekent. Om rijen te zien, leest een query zoals SELECT * FROM orders; de huidige databasetoestand.

Een schemadiagram tekent elke tabel als een vak. De titel noemt de tabel; de inhoud vermeldt kolommen en vaak typen. PK markeert primaire sleutels en FK markeert refererende sleutels. Relatielijnen tonen welke refererende sleutel naar welke sleutel in de oudertabel verwijst.

Bepaal eerst het soort lijn

Sommige diagrammen gebruiken een pijl van een refererende sleutel naar de sleutel waarnaar die verwijst. Een pijl van orders.customer_id naar customers.customer_id beschrijft de richting van de verwijzing. Andere tools gebruiken symbolen aan de uiteinden om te beschrijven hoeveel rijen kunnen worden gekoppeld. Controleer de legenda van het diagram: een pijlrichting en de omvang van een relatie geven verschillende informatie.

Lees kraaienpootsymbolen

In kraaienpootnotatie betekent een streepje één, een cirkel nul en een poot met drie vertakkingen veel. Elk lijnuiteinde geeft een minimum en maximum aan. Het symbool dat het dichtst bij de tabel staat, geeft het maximum; het andere geeft het minimum.

Als je vanuit één tabel leest, kijk je naar de symbolen naast de andere tabel. Die symbolen beschrijven hoeveel rijen daar bij je ene startrij kunnen passen.

Doorloop klanten en bestellingen

De lijn van de winkel heeft symbolen voor precies één naast customers en symbolen voor nul of meer naast orders.

1. Begin met één klant. Kijk naar het uiteinde bij bestellingen: de klant kan nul of meer bestellingen hebben.
2. Begin met één bestelling. Kijk naar het uiteinde bij klanten: de bestelling hoort bij precies één klant.
3. Controleer de definitie: de refererende sleutel die niet NULL is, staat in orders, dus dat is de veel-kant en elke bestelling heeft een geldige klant nodig.

De kraaienpoot staat naast de tabel met de herhaalde refererende sleutel. Als je vanuit diezelfde tabel naar het nabije uiteinde kijkt, draai je de betekenis om. Een klantverwijzing die NULL toestaat, zou het uiteinde bij klanten veranderen naar nul of één; dat zou niet elke klant tot één bestelling beperken.

Herken een koppeltabel

De winkel heeft een-op-veelkoppelingen van orders naar order_items en van products naar order_items. Beide veel-uiteinden komen samen bij order_items. De twee refererende sleutels vormen de primaire sleutel. Door deze structuur kan een bestelling meerdere producten bevatten en kan een product in meerdere bestellingen voorkomen.

Het diagram beschrijft toegestane koppelingen, niet de huidige aantallen. Het staat toe dat Maria meerdere bestellingen heeft en Aisha geen. Om te weten hoeveel ieder er werkelijk heeft, lees je de rijen. Een minimum van één van ouder naar kind kan een aanvullende bedrijfsregel uitdrukken; een refererende sleutel op zichzelf vereist niet dat een rij in de oudertabel rijen in de kindtabel heeft.

Herken verwante diagramstijlen

Een entiteit-relatiediagram dat tijdens het ontwerp wordt gemaakt, kan bedrijfsentiteiten tonen voordat tabellen bestaan. Diagrammen in Chen-notatie gebruiken ovalen voor attributen en ruiten voor relaties. Een schemadiagram toont de werkelijke tabellen en hun gedeclareerde sleutels. Beide helpen om structuur over te brengen, maar hun symbolen moet je volgens hun eigen legenda lezen.

Zoek elk gevraagd gegeven en volg de sleutels die het met het startrecord verbinden. Namen kunnen vaker voorkomen of veranderen; zoek in plaats daarvan overeenkomende sleutelwaarden.

Vind de klant van een bestelling

“Waar woont de klant die bestelling 102 heeft geplaatst?” volgt dit pad:

1. Zoek bestelling 102 op via orders.order_id.
2. Lees de bijbehorende customer_id: 2.
3. Zoek klant 2 op via customers.customer_id.
4. Lees customers.city: Denver.

Bestelnummer 102 is niet de klantidentificatie. SQL-joins maken deze koppelingen op basis van sleutels automatisch; weergaveposities verbinden de records niet.

Vind producten en een historisch totaal

Bestelling 101 heeft deze rijen in order_items. Volg elke product_id naar products voor de naam:

Gebruik de vastgelegde regelprijs voor het in rekening gebrachte bedrag:

• Twee mokken van € 12,50 kosten € 25,00.
• Eén oplader van € 8,99 kost € 8,99.
• Totaal: € 33,99.

De huidige productprijs kan de kosten van een oude bestelling onjuist weergeven. Bestelling 102 bevat één lamp van € 34,00; bestelling 103 bevat vier mokken van € 12,50, samen € 50,00.

Interpreteer ontbrekende waarden en regels

De verzenddatum van bestelling 102 is NULL, hier gedocumenteerd als “niet verzonden”. Test die met IS NULL, niet met een vergelijking op gelijkheid aan NULL.

Lees elke relevante integriteitsregel wanneer je een wijziging beoordeelt:

• Een regel voor bestelling 104 wordt geweigerd als die bestelling niet bestaat.
• Een andere regel met sleutel (101, 11) wordt geweigerd omdat het paar bestaat.
• Hoeveelheid 0 wordt geweigerd door de controle op een positieve hoeveelheid.
• Het standaardgedrag van de refererende sleutel blokkeert het verwijderen van product 11 zolang regels ernaar verwijzen.

Een ongebruikte sleutel maakt een rij niet geldig als de bijbehorende verwijzing ontbreekt.

Bepaal de nieuwe rijen

Als een terugkerende klant twee verschillende bestaande producten koopt, voegt dat één rij toe aan orders en twee rijen aan order_items. De klant en producten bestaan al. Een nieuw product aanbieden voegt één rij toe aan products, zonder dat daarvoor een bestelling nodig is.