Normalisatie in SQL: een complete gids van 1NF tot en met 5NF

TL;DR

• Normalisatie organiseert relationele databasetabellen om redundantie te elimineren en de dataintegriteit te beschermen
• 1NF: Elke cel bevat één enkel atomair waarde; elke rij is uniek identificeerbaar
• 2NF: Verwijder partiële afhankelijkheden—niet-sleutelkolommen moeten afhankelijk zijn van de volledige primaire sleutel
• 3NF: Verwijder transitieve afhankelijkheden—niet-sleutelkolommen zijn alleen afhankelijk van de primaire sleutel, niet van andere niet-sleutelkolommen
• BCNF, 4NF, 5NF: Pak functionele, multivalued- en join-afhankelijkheden aan voor complexe schema’s
• De meeste productiedatabases hebben genoeg aan normalisatie tot 3NF; hogere vormen gelden voor gespecialiseerde use-cases

Wat is normalisatie in SQL?

Normalisatie is in deze context het proces van het organiseren van data binnen een database (relationele database) om data-anomalieën, zoals redundantie, te elimineren.

Eenvoudiger gezegd: het houdt in dat je een grote, complexe tabel opsplitst in kleinere en eenvoudigere tabellen, terwijl je de relaties tussen de data behoudt.

Normalisatie wordt veel gebruikt bij het werken met grote datasets.

Laten we kort kijken naar enkele scenario’s waarin normalisatie vaak wordt toegepast.

Dataintegriteit

Stel je een database voor met klantinformatie. Zonder normalisatie zouden we, als een klant zijn of haar leeftijd wijzigt, dit op meerdere plekken moeten updaten, wat de kans op inconsistenties vergroot. Door de data te normaliseren, kun je aparte tabellen hebben die gekoppeld zijn via een unieke identificator, zodat de data accuraat en consistent blijft.

Efficiënt query’en

Neem een complexe database met meerdere gerelateerde tabellen die redundante informatie opslaan. In dit scenario worden queries met joins ingewikkelder en zwaarder. Normalisatie helpt het query’en te vereenvoudigen door data op te splitsen in kleinere tabellen, waarbij elke tabel alleen relevante informatie bevat en de behoefte aan complexe joins afneemt.

Opslagoptimalisatie

Een groot probleem met redundante data is dat het onnodige opslagruimte inneemt. Als we bijvoorbeeld dezelfde productdetails in elk orderrecord opslaan, leidt dat tot duplicatie. Met normalisatie kun je redundantie elimineren door data over aparte tabellen te verdelen.

Waarom is normalisatie in SQL belangrijk?

Normalisatie speelt een cruciale rol in databaseontwerp. Hier zijn een aantal redenen waarom het essentieel is:

• Vermindert redundantie:Redundantieis wanneer dezelfde informatie meerdere keren wordt opgeslagen, en een goede manier om dit te vermijden is door data op te splitsen in kleinere tabellen.
• Verbetert query-prestaties: Je kunt sneller queries uitvoeren op kleinere, genormaliseerde tabellen.
• Minimaliseert update-anomalieën: Met genormaliseerde tabellen kun je data eenvoudig bijwerken zonder andere records te beïnvloeden.
• Verhoogt dataintegriteit: Het zorgt ervoor dat data consistent en accuraat blijft.

Waardoor ontstaat de behoefte aan normalisatie?

Als een tabel niet goed genormaliseerd is en datar edundantie bevat, neemt deze niet alleen extra opslagruimte in, maar wordt het ook moeilijker om de database te beheren en bij te werken.

Er zijn verschillende factoren die de behoefte aan normalisatie aanjagen, van dataredundantie (zoals hierboven besproken) tot moeilijkheden bij het beheren van relaties. Laten we er meteen induiken:

• Invoeg-, verwijder- en update-anomalieën: Elke vorm van wijziging in een tabel kan, als die niet zorgvuldig wordt afgehandeld, tot fouten of inconsistenties in andere tabellen leiden. Deze wijzigingen kunnen het toevoegen van nieuwe data, het bijwerken van data of het verwijderen van records zijn, wat kan resulteren in onbedoeld dataverlies.
• Moeilijkheid bij het beheren van relaties: Het wordt uitdagender om complexe relaties te onderhouden in een ongenormaliseerde structuur.
• Andere factoren die de behoefte aan normalisatie stimuleren zijn partiële afhankelijkheden en transitieve afhankelijkheden, waarbij partiële afhankelijkheden kunnen leiden tot dataredundantie en update-anomalieën, en transitieve afhankelijkheden tot data-anomalieën. We gaan in de volgende secties bekijken hoe je met deze afhankelijkheden kunt omgaan om database-normalisatie te waarborgen.

Verschillende typen database-normalisatie

Tot nu toe hebben we gekeken wat normalisatie in SQL is, waarom normalisatie in SQL belangrijk is en waardoor de behoefte aan normalisatie ontstaat. Database-normalisatie bestaat inverschillende vormen, elk met een hoger niveau van data-organisatie.

In deze sectie bespreken we kort de verschillende normalisatieniveaus en gaan we er in de volgende sectie dieper op in.

Afbeelding door auteur

Snelle vergelijking van normaalvormen

Eerste normaalvorm (1NF)

Dit normalisatieniveau zorgt ervoor dat elke kolom in je data alleen atomische waarden bevat. Atomische waarden betekenen hier dat elke invoer in een kolom ondeelbaar is. Het is alsof je zegt dat elke cel in een spreadsheet slechts één stukje informatie mag bevatten.1NF waarborgt de atomiciteit van data, met in elke kolomcel slechts één waarde en elke kolom met een unieke naam.

Tweede normaalvorm (2NF)

Elimineert partiële afhankelijkheden door te verzekeren dat niet-sleutelattributen alleen afhankelijk zijn van de primaire sleutel. Dit betekent in essentie dat er een directe relatie moet zijn tussen elke kolom en de primaire sleutel, en niet tussen andere kolommen onderling.

Derde normaalvorm (3NF)

Verwijdert transitieve afhankelijkheden door te waarborgen dat niet-sleutelattributen alleen afhankelijk zijn van de primaire sleutel. Dit normalisatieniveau bouwt voort op 2NF.

Boyce-Codd-normaalvorm (BCNF)

Dit is een strengere versie van 3NF die aanvullende anomalieën aanpakt. Op dit normalisatieniveau is elke determinant een kandidaatsleutel.

Vierde normaalvorm (4NF)

Dit is een normalisatieniveau dat voortbouwt op BCNF door om te gaan met multivalued-afhankelijkheden.

Vijfde normaalvorm (5NF)

5NF is het hoogste normalisatieniveau dat join-afhankelijkheden aanpakt. Het wordt gebruikt in specifieke scenario’s om redundantie verder te minimaliseren door een tabel op te splitsen in kleinere tabellen.

Database-normalisatie met voorbeelden uit de praktijk

We hebben alle normalisatieniveaus al uitgelicht. Laten we elk daarvan nu dieper verkennen met voorbeelden en uitleg.

Normalisatie in de eerste normaalvorm (1NF)

1NF zorgt ervoor dat elke kolomcel slechts atomische waarden bevat. Stel je een bibliotheekdatabase voor met een tabel die boekinformatie opslaat (title, author, genre en borrowed_by). Als de tabel niet is genormaliseerd, kan borrowed_by een lijst met namen van leners bevatten, gescheiden door komma’s. Dit schendt 1NF, omdat één cel meerdere waarden bevat. De onderstaande tabel is een goede weergave van een tabel die 1NF schendt, zoals eerder beschreven.

De oplossing?

In 1NF maken we een aparte tabel voor leners en koppelen we die aan de boektabel. Deze tabellen kunnen ofwel worden gekoppeld met de foreign key in de leentabel of met een aparte koppelings tabel. De aanpak met de foreign key in de borrowers-tabel houdt in dat je een foreign key-kolom toevoegt aan de borrowers-tabel die verwijst naar de primaire sleutel van de books-tabel. Dit dwingt een relatie tussen de tabellen af en zorgt voor dataconsistentie.

Een weergave hiervan vind je hieronder:

Hier is de SQL om deze genormaliseerde tabellen te maken:

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    author VARCHAR(100),
    genre VARCHAR(50)
);

CREATE TABLE borrowers (
    borrower_id INT PRIMARY KEY,
    name VARCHAR(100),
    book_id INT,
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Books-tabel

Borrowers-tabel

Tweede normaalvorm (2NF)

Dit normalisatieniveau bouwt, zoals al beschreven, voort op 1NF door te zorgen dat er geen partiële afhankelijkheden van de primaire sleutel zijn. Simpeler gezegd: alle niet-sleutelattributen moeten afhankelijk zijn van de volledige primaire sleutel en niet slechts van een deel ervan.

Met de geïmplementeerde 1NF hebben we al twee aparte tabellen (zie de sectie over 1NF).

Stel nu dat we deze tabellen willen koppelen om uitleningen vast te leggen. De eerste aanpak zou kunnen zijn om simpelweg een borrower_id-kolom toe te voegen aan de books-tabel, zoals hieronder:

Dit lijkt misschien een oplossing, maar het schendt 2NF omdat borrower_id slechts gedeeltelijk afhankelijk is van book_id. Een boek kan meerdere leners hebben, maar één borrower_id kan in deze structuur slechts aan één boek gekoppeld zijn. Dit creëert een partiële afhankelijkheid.

De oplossing?

We moeten de many-to-many-relatie tussen boeken en leners realiseren om 2NF te bereiken. Dit kan door een aparte tabel in te voeren:

Book_borrowings-tabel

Deze tabel legt een duidelijke relatie tussen boeken en leners vast. De book_id en borrower_id fungeren als foreign keys en verwijzen naar de primaire sleutels in hun respectieve tabellen. Deze aanpak zorgt ervoor dat borrower_id afhankelijk is van de volledige primaire sleutel (book_id) van de books-tabel, in overeenstemming met 2NF.

Derde normaalvorm (3NF)

3NF bouwt voort op 2NF door transitieve afhankelijkheden te elimineren. Een transitieve afhankelijkheid ontstaat wanneer een niet-sleutelattribuut afhankelijk is van een ander niet-sleutelattribuut, dat op zijn beurt afhankelijk is van de primaire sleutel. Het leunt op de transitiviteitswet.

Na 2NF hebben we drie tabellen in onze bibliotheekdatabase:

Books-tabel

Borrowers-tabel

Book_borrowings-tabel

De 2NF-structuur lijkt efficiënt, maar er kan een verborgen afhankelijkheid zijn. Stel dat we een due_date-kolom toevoegen aan de books-tabel. Dit lijkt op het eerste gezicht logisch, maar het creëert een transitieve afhankelijkheid waarbij:

• De due_date-kolom afhankelijk is van borrowing_id (een niet-sleutelattribuut) uit de book_borrowings-tabel.
• Borrowing_id op zijn beurt afhankelijk is van book_id (de primaire sleutel) van de books-tabel.

De implicatie is dat due_date afhankelijk is van een tussenliggend niet-sleutelattribuut (borrowing_id) in plaats van rechtstreeks van de primaire sleutel (book_id). Dit schendt 3NF.

De oplossing?

We kunnen de due_date-kolom verplaatsen naar de meest geschikte tabel door de book_borrowings-tabel uit te breiden met de kolommen due_date en returned_date.

Hieronder staat de bijgewerkte tabel:

Door de due_date-kolom in de book_borrowings-tabel te plaatsen, hebben we de transitieve afhankelijkheid succesvol geëlimineerd.

Dit betekent dat due_date nu direct afhankelijk is van de gecombineerde relatie tussen book_id en borrower_id. In deze context fungeren book_id en borrower_id als een samengestelde foreign key die samen de primaire sleutel van de book_borrowings-tabel vormen.

Boyce-Codd-normaalvorm (BCNF)

BCNF is gebaseerd op functionele afhankelijkheden die alle kandidaatsleutels in een relatie in ogenschouw nemen.

Functionele afhankelijkheden (FD) definiëren relaties tussen attributen binnen een relationele database. Een FD stelt dat de waarde van de ene kolom de waarde van een andere gerelateerde kolom bepaalt. FD’s zijn erg belangrijk omdat ze het normalisatieproces sturen door afhankelijkheden te identificeren en ervoor te zorgen dat data passend over tabellen wordt verdeeld.

BCNF is een strengere versie van 3NF. Het zorgt ervoor dat elke determinant (een set attributen die een rij uniek identificeert) in een tabel een kandidaatsleutel is (een minimale set attributen die een rij uniek identificeert). De essentie is dat alle determinanten als primaire sleutel zouden moeten kunnen dienen.

Het zorgt ervoor dat elke functionele afhankelijkheid (FD) een superkey als determinant heeft. Met andere woorden, als X —> Y (X bepaalt Y) geldt, dan moet X een kandidaatsleutel (superkey) van de relatie zijn. Merk op dat X en Y kolommen in een datatabel zijn.

Voortbouwend op 3NF hebben we drie tabellen:

Books-tabel

Borrowers-tabel

Book_borrowings-tabel

Hoewel de 3NF-structuur goed is, kan er een verborgen determinant zijn in de book_borrowings-tabel. Aangenomen dat één lener niet tegelijkertijd hetzelfde boek twee keer kan lenen, identificeert de combinatie van book_id en borrower_id samen een leenrecord uniek.

Deze structuur schendt BCNF omdat de gecombineerde set (book_id en borrower_id) niet de primaire sleutel van de tabel is (die is enkel borrowing_id).

De oplossing?

Om BCNF te bereiken, kunnen we de book_borrowings-tabel ofwel opdelen in twee aparte tabellen of de gecombineerde attributenset tot primaire sleutel maken.

1. Aanpak 1 (tabel decomponeren): In deze aanpak decomponeren we de book_borrowings-tabel in aparte tabellen:

• • Een tabel met borrowing_id als primaire sleutel, borrowed_date, due_date en returned_date.
  • Nog een aparte tabel om boeken en leners te koppelen, met book_id als foreign key, borrower_id als foreign key en eventueel aanvullende attributen die specifiek zijn voor het leenevent.

2. Aanpak 2 (maak de gecombineerde attributenset de primaire sleutel): We kunnen overwegen om van book_id en borrower_id een samengestelde primaire sleutel te maken om leenrecords uniek te identificeren. Het probleem met deze aanpak is dat dit niet werkt als een lener hetzelfde boek meerdere keren kan lenen.

Uiteindelijk hangt je keuze tussen deze opties af van je specifieke databehoeften en hoe je leenrelaties wilt modelleren.

Vierde normaalvorm (4NF)

4NF gaat over multivalued-afhankelijkheden. Een multivalued-afhankelijkheid bestaat wanneer één attribuut meerdere afhankelijke attributen kan hebben, en deze afhankelijke attributen onafhankelijk zijn van de primaire sleutel. Het is best complex, maar we verkennen het dieper met een voorbeeld.

Het bibliotheekvoorbeeld dat we tot nu toe gebruikten, is op dit normalisatieniveau niet toepasbaar. 4NF is typisch van toepassing op situaties waarin één attribuut meerdere afhankelijke attributen kan hebben die niet direct aan de primaire sleutel gerelateerd zijn.

Laten we een ander scenario gebruiken. Stel je een database voor die informatie over publicaties opslaat. We bekijken een “Publications”-tabel met kolommen title, author, publication_year en keywords.

De bovenstaande tabelstructuur schendt 4NF omdat:

• De keywords-kolom een multivalued-afhankelijkheid heeft van de primaire sleutel publication_id. Dit betekent dat een publicatie meerdere trefwoorden kan hebben, en deze trefwoorden onafhankelijk zijn van de unieke identificator van de publicatie.

De oplossing?

We kunnen een aparte tabel maken.

Publication_keywords-tabel

De nieuw aangemaakte tabel (Publication_keywords) legt een many-to-many-relatie vast tussen publicaties en trefwoorden. Elke publicatie kan meerdere trefwoorden hebben die zijn gekoppeld via publication_id, een foreign key, en elk trefwoord kan aan meerdere publicaties verbonden zijn.

Hiermee hebben we de multivalued-afhankelijkheid geëlimineerd en 4NF bereikt.

Vijfde normaalvorm (5NF)

5NF is de meest complexe vorm van normalisatie die join-afhankelijkheden elimineert. Dit is een situatie waarin data uit meerdere tabellen moet worden gejoint om een specifieke query te beantwoorden, zelfs wanneer die tabellen al in 4NF zijn.

Eenvoudiger gezegd zorgt 5NF ervoor dat er geen extra informatie kan worden afgeleid door tabellen samen te voegen die niet al beschikbaar was in de afzonderlijke tabellen.

Join-afhankelijkheden komen minder snel voor wanneer tabellen al genormaliseerd zijn (in 3NF of 4NF), vandaar de uitdaging om een helder en eenduidig voorbeeld voor 5NF te geven.

Laten we echter kijken naar een scenario waarin 5NF relevant kan zijn:

Stel je een universiteitsdatabase voor met genormaliseerde tabellen voor “Courses” en “Enrollments”.

Courses-tabel

Enrollments-tabel

Aangenomen dat deze tabellen al in 3NF of 4NF zijn, kan er een join-afhankelijkheid bestaan afhankelijk van hoe data is opgeslagen. Stel dat een cursus een vereiste heeft die is opgeslagen in de “Courses”-tabel als de kolom “prerequisite_course_id”.

Dit lijkt in eerste instantie efficiënt. Overweeg echter een query die de ingeschreven cursussen van een student en de bijbehorende vereisten moet ophalen. In dit scenario moet je de “Courses”- en “Enrollments”-tabellen joinen en vervolgens mogelijk nogmaals de “Courses”-tabel joinen om de vereiste-informatie op te halen.

De oplossing?

Om de join-afhankelijkheid mogelijk te elimineren en 5NF te bereiken, kunnen we een aparte “Course Prerequisites”-tabel introduceren:

Course_prerequisite-tabel

Deze aanpak scheidt de vereiste-informatie en maakt het mogelijk om ingeschreven cursussen en hun vereisten efficiënt op te halen in één join tussen de “Enrollments”- en “Course_prerequisites”-tabellen.

Let op: We gaan ervan uit dat een student maar één vereiste per cursus heeft.

5NF is een zeer complexe en zeldzame vorm van normalisatie, dus als je net begint met leren over data, kom je dit misschien niet snel tegen. Toch is het waardevolle kennis die je voorbereidt wanneer je met complexe databases te maken krijgt.

Normalisatie vs. denormalisatie

Hoewel normalisatie redundantie vermindert en de dataintegriteit verbetert, zijn er gevallen waarin denormalisatie de betere keuze is. Denormalisatie introduceert bewust redundantie om de leesprestaties voor specifieke workloads te verbeteren.

In de praktijk gebruiken de meeste productiedatabases een combinatie van beide aanpakken. Begin met een volledig genormaliseerd schema en denormaliseer vervolgens selectief waar de queryprestaties daarom vragen.

Bouw je SQL-vaardigheden uit

Als je dit leest: gefeliciteerd dat je tot het einde bent gebleven. Het was een mooie reis langs wat normalisatie in SQL is, waarom het belangrijk is, waardoor de behoefte eraan ontstaat en de verschillende typen database-normalisatie. De scenario’s die we gebruikten bij de uitleg van de verschillende typen normalisatie helpen je om het volledig te begrijpen en deze kennis toe te passen in je leertraject.

Normalisatie is een basisvaardigheid voor iedereen die aan een data-gerelateerde carrière begint. Door deze principes te begrijpen, ben je klaar om efficiënte en goed georganiseerde databases te bouwen.

Leren is heel belangrijk in de datasector, en om je SQL-vaardigheden te verbeteren, hebben we enkele resources voor je.

• Cursus Creating PostgreSQL Databases
• Normalisatie in DBMS: een complete gids met SQL-voorbeelden
• Data modeling uitgelegd: technieken, voorbeelden en best practices
• SQL-queryvoorbeelden en tutorial
• Denormalisatie in databases: wanneer en hoe gebruik je het