Normalisatie is een database-ontwerptechniek die wordt gebruikt om een relationele databasetabel te ontwerpen tot een hogere normale vorm. Het proces is progressief en een hoger niveau van databasenormalisatie kan niet worden bereikt tenzij aan de vorige niveaus is voldaan.
Dat betekent dat het hebben van gegevens in niet-genormaliseerde vorm (de minst genormaliseerde) en het streven naar het hoogste niveau niveau van normalisatie, de eerste stap zou zijn om naleving van de eerste normaalvorm te verzekeren, de tweede stap zou zijn om ervoor te zorgen dat aan de tweede normaalvorm wordt voldaan, enzovoort, in de hierboven genoemde volgorde, totdat de gegevens voldoen aan de zesde normaalvorm.
Het is echter vermeldenswaard dat normale vormen buiten 4NF voornamelijk van academisch belang zijn, aangezien de problemen die ze bestaan om op te lossen zelden in de praktijk verschijnen.
Houd er rekening mee dat de gegevens in het volgende voorbeeld opzettelijk ontworpen om de meeste normale vormen tegen te spreken. In het echte leven is het heel goed mogelijk om een aantal normalisatiestappen over te slaan, omdat de tabel niets bevat dat in tegenspraak is met de gegeven normale vorm. Het komt ook vaak voor dat het oplossen van een overtreding van een normale vorm ook een overtreding van een hogere normale vorm in het proces oplost. Er is ook één tabel gekozen voor normalisatie bij elke stap, wat betekent dat er aan het einde van dit voorbeeldproces nog steeds enkele tabellen zijn die niet voldoen aan de hoogste normaalvorm.
Initiële dataEdit
Laat een databasetabel met de volgende structuur:
| Titel | Auteur | Auteur Nationaliteit | Formaat | Prijs | Onderwerp | Pages | Thickness | Publisher | Land van uitgever | Publicatietype | Genre-ID | Genre |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Begin van MySQL Database-ontwerp en -optimalisatie | Chad Russell | Amerikaans | Hardcover | 49,99 | MySQL,
Database, Ontwerp |
520 | Dik | Apress | VS | E-book | 1 | Tutorial |
We gaan er in dit voorbeeld van uit dat elk boek maar één auteur heeft.
Voldoen aan 1NFEdit
Om aan 1NF te voldoen, moeten de waarden in elke kolom van een tabel atomair zijn. In de initiële tabel bevat Subject een set subject-waarden, wat betekent dat het niet voldoet.
Een manier om de 1NF te bereiken is door de dupliciteiten in meerdere kolommen te scheiden met behulp van herhalende groepen. Subject:
| Titel | Formaat | Auteur | Nationaliteit van auteur | Prijs | Onderwerp 1 | Onderwerp 2 | Onderwerp 3 | Pagina’s | Dikte | Uitgever | Land van uitgever | Genre-ID | Genre-naam |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Beginnen met MySQL-databaseontwerp en -optimalisatie | Hardcover | Chad Russell | Amerikaans | 49,99 | MySQL | Database | Ontwerp | 520 | Dik | Apress | VS | 1 | Handleiding |
Hoewel de tabel nu formeel voldoet aan de 1NF (is atomair), ligt het probleem met deze oplossing voor de hand – als een boek heeft meer dan drie onderwerpen, het kan niet aan de database worden toegevoegd zonder de structuur ervan te wijzigen.
Om het probleem op een elegantere manier op te lossen, is het nodig om entiteiten in de tabel te identificeren en ze te scheiden in hun eigen respectievelijke tabellen. In dit geval zou het resulteren in de tabellen Boek, Onderwerp en Uitgever:
| Titel | Formaat | Auteur | Auteur Nationaliteit | Prijs | Pagina’s | Dikte | Genre-ID | Genre-naam | Uitgevers-ID |
|---|---|---|---|---|---|---|---|---|---|
| Begin van MySQL Databaseontwerp en -optimalisatie | Hardcover | Chad Russell | Amerikaans | 49.99 | 520 | Dik | 1 | Tutorial | 1 |
|
|
Het simpelweg scheiden van de initiële gegevens in meerdere tabellen zou de verbinding tussen de gegevens verbreken. Dat betekent dat de relaties tussen de nieuw geïntroduceerde tabellen moeten worden bepaald. Merk op dat de kolom met uitgevers-ID in de tabel van het boek een externe sleutel is die een veel-op-een-relatie tussen een boek en een uitgever realiseert.
Een boek kan voor veel onderwerpen passen, maar ook voor een onderwerp komen overeen met veel boeken. Dat betekent dat er ook een veel-op-veel-relatie moet worden gedefinieerd, bereikt door een koppelingstabel te maken:
|
In plaats van één tabel in niet-genormaliseerde vorm, zijn er nu 4 tabellen die voldoen aan de 1NF.
Voldoet aan 2NFEdit
De boekentabel heeft één kandidaatsleutel (die daarom de primaire sleutel is), de samengestelde sleutel {Titel, Formaat}. Beschouw het volgende tabelfragment:
| Titel | Formaat | Auteur | Auteur Nationaliteit | Prijs | Pagina’s | Dikte | Genre-ID | Genre-naam | Uitgever-ID |
|---|---|---|---|---|---|---|---|---|---|
| Beginnen met MySQL-databaseontwerp en -optimalisatie | Hardcover | Chad Russell | Amerikaans | 49,99 | 520 | Dik | 1 | Tutorial | 1 |
| Beginnen met MySQL-databaseontwerp en -optimalisatie | E-book | Chad Russell | Amerikaans | 22.34 | 520 | Dik | 1 | Tutorial | 1 |
| Het relationele model voor databasebeheer: versie 2 | E-book | EFCodd | Brits | 13,88 | 538 | Dik | 2 | Populaire wetenschap | 2 |
| Het relationele model voor database Ma beheer: versie 2 | Paperback | EFCodd | Brits | 39,99 | 538 | Dik | 2 | Populaire wetenschap | 2 |
Alle attributen die geen deel uitmaken van de kandidaatsleutel zijn afhankelijk van de titel, maar alleen de prijs is ook afhankelijk van het formaat. Om aan 2NF te voldoen en dupliciteiten te verwijderen, moet elk niet-kandidaat-sleutelattribuut afhankelijk zijn van de hele kandidaatsleutel, niet alleen een deel ervan.
Om deze tabel te normaliseren, moet u van {Titel} een (eenvoudige) kandidaatsleutel maken (de primaire sleutel) zodat elk niet-kandidaat-sleutelattribuut afhankelijk is van de hele kandidaatsleutel, en verwijder Price in een aparte tabel zodat de afhankelijkheid van het formaat behouden kan blijven:
|
|
Nu, de Boekentafel voldoet aan 2NF.
Bevredigend 3NFEdit
De Boekentafel heeft nog steeds een transitieve functionele afhankelijkheid ({Auteur Nationaliteit} is afhankelijk van {Auteur}, die afhankelijk is van {Titel }). Een vergelijkbare overtreding bestaat voor genre ({Genre Name} is afhankelijk van {Genre ID}, die afhankelijk is van {Title}). Daarom bevindt de boekentafel zich niet in 3NF. Om het in 3NF te maken, gebruiken we de volgende tabelstructuur, waarbij we de transitieve functionele afhankelijkheden elimineren door {Author Nationality} en {Genre Name} in hun eigen respectievelijke tabellen te plaatsen:
| Titel | Auteur | Pages | Dikte | Genre-ID | Uitgever-ID |
|---|---|---|---|---|---|
| Beginnen met MySQL-databaseontwerp en Optimalisatie | Chad Russell | 520 | Dik | 1 | 1 |
| Het relationele model voor databasebeheer: versie 2 | EFCodd | 538 | Thick | 2 | 2 |
|
| Auteur | Auteur Nationaliteit |
|---|---|
| Chad Russell | Amerikaans |
| EFCodd | Brits |
| Gen re ID | Genre Name |
|---|---|
| 1 | Tutorial |
| 2 | Populaire wetenschap |
Bevredigend EKNFEdit
De elementaire sleutel normaalvorm (EKNF) valt strikt tussen 3NF en BCNF en wordt niet veel besproken in de literatuur. Het is bedoeld “om de meest opvallende kwaliteiten van zowel 3NF als BCNF vast te leggen” terwijl de problemen van beide worden vermeden (namelijk dat 3NF “te vergevingsgezind” is en BCNF “vatbaar is voor computationele complexiteit”). Aangezien het zelden wordt genoemd in de literatuur, het is niet opgenomen in dit voorbeeld.
Bevredigend 4NFEdit
Stel dat de database eigendom is van een franchise van een boekhandelaar die verschillende franchisenemers heeft die winkels op verschillende locaties bezitten.En daarom besloot de winkelier een tabel toe te voegen die gegevens bevat over de beschikbaarheid van de boeken op verschillende locaties:
| Franchisenemer-ID | Titel | Locatie |
|---|---|---|
| 1 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Californië |
| 1 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Florida |
| 1 | Begin van MySQL-databaseontwerp en -optimalisatie | Texas |
| 1 | Het relationele model voor databasebeheer: versie 2 | Californië |
| 1 | Het relationele model voor databasebeheer: versie 2 | Florida |
| 1 | Het relationele model voor databasebeheer: versie 2 | Texas |
| 2 | Begin van MySQL-databaseontwerp en -optimalisatie | Californië |
| 2 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Florida |
| 2 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Texas |
| 2 | Het relationele model voor databasebeheer: versie 2 | Californië |
| 2 | Het relationele model voor databasebeheer: versie 2 | Florida |
| 2 | Het relationele model voor databasebeheer: versie 2 | Texas |
| 3 | Begin van MySQL-databaseontwerp en optimalisatie | Texas |
Aangezien deze tabelstructuur uit een samengestelde primaire sleutel bestaat, bevat deze geen niet-sleutelattributen en staat deze al in BCNF (en voldoet daarom ook aan alle voorgaande normale vormen). Als we echter aannemen dat alle beschikbare boeken in elk gebied worden aangeboden, kunnen we opmerken dat de titel niet ondubbelzinnig aan een bepaalde locatie is gebonden en daarom voldoet de tabel niet aan 4NF.
Dat betekent dat, om aan de vierde normaalvorm te voldoen, moet deze tabel ook worden ontleed:
|
|
||||||||||||
| Franchisenemer-ID | Locatie | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Californië | ||||||||||||
| 1 | Florida | ||||||||||||
| 1 | Texas | ||||||||||||
| 2 | Californië | ||||||||||||
| 2 | Florida | ||||||||||||
| 2 | Texas | ||||||||||||
| 3 | Texas |
Nu wordt elk record ondubbelzinnig geïdentificeerd door een superkey, daarom is aan 4NF voldaan.
Voldoet aan ETNFEdit
Stel dat de franchisenemers ook boeken bij verschillende leveranciers kunnen bestellen. Laat de relatie ook onderhevig zijn aan de volgende beperking:
- Als een bepaalde leverancier een bepaalde titel levert
- en de titel wordt geleverd aan de franchisenemer
- en de franchisenemer wordt beleverd door de leverancier,
- dan levert de leverancier de titel aan de franchisenemer.
| Leverancier-ID | Titel | Franchisenemer-ID |
|---|---|---|
| 1 | Beginnen met MySQL-databaseontwerp en -optimalisatie | 1 |
| 2 | Het relationele model voor databasebeheer: versie 2 | 2 |
| 3 | SQL leren | 3 |
Deze tabel is in 4NF, maar de leverancier-ID is gelijk aan de samenvoeging van de projecties: {{Supplier ID, Book}, {Book, Franchisenemer ID}, {Franchisee ID, Supplier ID KAART}}.Geen enkel onderdeel van die join-afhankelijkheid is een superkey (de enige superkey is de volledige kop), dus de tabel voldoet niet aan de ETNF en kan verder worden ontleed:
|
|
|
De ontleding zorgt voor ETNF-conformiteit.
Voldoet aan 5NFEdit
Om een tafel te vinden die niet voldoet aan de 5NF, is meestal nodig om de gegevens grondig te onderzoeken. Stel dat de tabel uit het 4NF-voorbeeld met een kleine wijziging in gegevens en laten we onderzoeken of deze voldoet aan 5NF:
| Franchisenemer-ID | Titel | Locatie |
|---|---|---|
| 1 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Californië |
| 1 | SQL leren | Californië |
| 1 | Het relationele model voor databasebeheer: versie 2 | Texas |
| 2 | Het relationele model voor databasebeheer: versie 2 | Californië |
Als we deze tabel ontleden, verlagen we redundanties en krijgen we de volgende twee tabellen:
|
|
Wat gebeurt er als we proberen aan deze tafels deel te nemen? De zoekopdracht zou de volgende gegevens retourneren:
| Franchisenemer-ID | Titel | Locatie |
|---|---|---|
| 1 | Beginnen met MySQL-databaseontwerp en Optimalisatie | Californië |
| 1 | SQL leren | Californië |
| 1 | Het relationele model voor databasebeheer: versie 2 | Californië |
| 1 | Het relationele model voor databasebeheer: versie 2 | Texas |
| 1 | SQL leren | Texas |
| 1 | Beginnen met MySQL-databaseontwerp en -optimalisatie | Texas |
| 2 | Het relationele model voor databasebeheer: versie 2 | Californië |
Blijkbaar retourneert de JOIN drie rijen meer dan zou moeten – laten we proberen er een toe te voegen ther tabel om de relatie te verduidelijken.We eindigen met drie afzonderlijke tabellen:
|
|
|
Wat zal de JOIN nu teruggeven? Het is eigenlijk niet mogelijk om aan deze drie tafels deel te nemen. Dat betekent dat het niet mogelijk was om de Franchisenemer – Boeklocatie zonder gegevensverlies te ontbinden, daarom voldoet de tabel al aan 5NF.
CJ Date heeft betoogd dat alleen een database in 5NF echt “genormaliseerd” is.
Bevredigende DKNFEdit
Laten we eens kijken naar de boekentabel uit eerdere voorbeelden en kijken of deze voldoet aan de normale vorm van de Domain-key:
| Titel | Pagina’s | Dikte | Genre-ID | Uitgever-ID | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Beginnen met MySQL-databaseontwerp en -optimalisatie | 520 | Thick | 1 | 1 | ||||||||||||||||||||||||||
| Het relationele model voor databasebeheer: versie 2 | 538 | Dik | 2 | 2 | ||||||||||||||||||||||||||
| SQL leren | 338 | Slank | 1 | 3 | ||||||||||||||||||||||||||
| SQL-kookboek | 636 | Dik | 1 | 3 |
|
|
Op die manier is de inbreuk op de domeinintegriteit geëlimineerd en staat de tabel in DKNF.
Bevredigend 6NFEdit
Een eenvoudige en intuïtieve definitie van de zesde normaalvorm is dat “een tabel in 6NF staat als de rij de primaire sleutel bevat, en hoogstens één ander attribuut”.
Dat betekent bijvoorbeeld de Publisher-tabel die is ontworpen tijdens het maken van de 1NF
| Publisher_ID | Naam | Land |
|---|---|---|
| 1 | Apress | VS |
moet verder worden opgesplitst in twee tabellen:
|
|
Het voor de hand liggende nadeel van 6NF is de wildgroei aan tabellen die nodig zijn om de informatie over een enkele entiteit weer te geven. Als een tabel in 5NF één primaire sleutelkolom en N-attributen heeft, zijn er N-tabellen nodig die dezelfde informatie vertegenwoordigen in 6NF; updates voor meerdere velden van één conceptueel record vereisen updates van meerdere tabellen; en inserts en deletes vereisen op dezelfde manier bewerkingen in meerdere tabellen. Om deze reden mag 6NF in databases die bedoeld zijn om te voldoen aan de behoeften van online transactieverwerking, niet worden gebruikt.
Echter, in datawarehouses, die geen interactieve updates toestaan en die gespecialiseerd zijn voor snelle query’s op grote datavolumes gebruiken bepaalde DBMS’en een interne 6NF-representatie – bekend als een Columnar-gegevensopslag. In situaties waarin het aantal unieke waarden van een kolom veel kleiner is dan het aantal rijen in de tabel, zorgt kolomgeoriënteerde opslag voor aanzienlijke ruimtebesparingen door datacompressie. Kolomopslag maakt ook een snelle uitvoering van bereikquery’s mogelijk (bijv. Toon alle records waar een bepaalde kolom tussen X en Y, of kleiner dan X is).
In al deze gevallen doet de databaseontwerper dat echter niet 6NF-normalisatie handmatig moeten uitvoeren door afzonderlijke tabellen te maken. Sommige DBMS’en die gespecialiseerd zijn voor warehousing, zoals Sybase IQ, gebruiken standaard kolomopslag, maar de ontwerper ziet nog steeds slechts één tabel met meerdere kolommen. Met andere DBMS’en, zoals Microsoft SQL Server 2012 en hoger, kunt u een “columnstore-index” voor een bepaalde tabel specificeren.
