Normalizace databáze/cs
Normalizace databáze je v informatice označení procesu zodpovědného za transformaci relační databáze tak, aby splňovala sadu podmínek – tzv. normálních forem. Předmětem těchto podmínek je především upravení tabulek do vhodnějšího stavu zamezením nekonzistencí, zvýšením integrity dat, odstraněním duplicit a zamezením potenciálních problému při manipulaci s daty v databázi.
Contents
Charakteristika
Normální formy
Přestože existuje řada normálních forem, v praxi se za normalizovanou databázi považuje taková, která splňuje alespoň první tři normální formy. Předmětem zkoumání prvních čtyř forem je vztah neklíčových atributů na primárním klíči, předmětem zkoumání posledních dvou normálních forem jsou vztahy uvnitř složených primárních klíčů. Důležité také je, že každá z normálních forem obsahuje a vyžaduje splnění všech pravidel obsažených ve formách předchozích. U všech příkladů níže platí pravidlo, že podtržené názvy atributů představují primární klíč.
Nultá normální forma (0NF)
První normální forma (1NF)
První normální forma říká, že všechny atributy tabulky musí být atomické, tedy dále nedělitelné.
Klasickým příkladem tabulky porušující první normální formu bývá nejčastěji problém s telefonními čísly, kdy naším cílem je umožnit evidovat pro každou osobu dvě různá telefonní čísla, jak lze vidět v tabulce 1:
| ID Osoby | Jméno | Telefonní číslo |
|---|---|---|
| 1 | Petr Novák | +420 111 222 333 |
| 2 | Jarmil Hnízdo | +420 123 123 123, +420 123 123 124 |
Tato tabulka ovšem porušuje první normální formu, jelikož sloupec (atribut) telefonní číslo není atomický. Zjevným řešením této situace by mohlo být přidání druhého sloupce pro telefonní číslo, abychom zajistili splnění pravidla atomických atributů:
| ID Osoby | Jméno | Telefonní číslo 1 | Telefonní číslo 2 |
|---|---|---|---|
| 1 | Petr Novák | +420 111 222 333 | |
| 2 | Jarmil Hnízdo | +420 123 123 123 | +420 123 123 124 |
Přestože je tato tabulka formálně správná a již neporušuje pravidlo první normální formy, její návrh je stále přinejmenším problémový. Jak bychom například postupovali, kdybychom po delším využívání zjistili, že u některých osob nám nestačí evidovat čísla dvě, ale potřebujeme přidat i čísla další? Rozšiřování tabulky o další sloupce telefonních čísel není často v praxi realizovatelné a také se označuje za špatný návrh. Správným řešením je vytvoření nové tabulky a odstraněním sloupce telefonních čísel z tabulky osob, jak lze vidět na příkladu níže:
| ID Osoby | Jméno |
|---|---|
| 1 | Petr Novák |
| 2 | Jarmil Hnízdo |
| ID Kontaktu | ID Osoby | Telefonní číslo |
|---|---|---|
| 1 | 1 | +420 111 222 333 |
| 2 | 2 | +420 123 123 123 |
| 3 | 2 | +420 123 123 124 |
V tomto případě je tedy první normální forma splněna a rozdělením tabulek jsme umožnili bezproblémové přidávání libovolného počtu telefonních čísel pro každou osobu. Všimněme si, že tabulka s kontakty obsahuje navíc kromě primárního klíče také cízí klíč, který nám zajišťuje svázání telefonního čísla se správnou osobou.
Druhá normální forma (2NF)
Druhá normální forma požaduje, aby tabulka byla v první normální formě, a navíc aby každý neklíčový atribut byl plně závislý na každém kandidátním klíči (neklíčovým atributem rozumíme atribut, který není součástí žádného kandidátního klíče). Druhá normální forma klade důraz především na odstranění možných duplicit v záznamech. Uveďme si příklad, kdy máme tabulku evidující následující informace o kurzech. Všimněme si především toho, že tabulka má složený primární klíč {ID Kurzu, ID Semestru}:
| ID Kurzu | ID Semestru | Počet míst | Jméno kurzu |
|---|---|---|---|
| IT101 | ls 2017 | 100 | Programování |
| IT101 | zs 2017 | 100 | Programování |
| IT102 | ls 2017 | 200 | Databáze |
| IT102 | zs 2017 | 150 | Databáze |
| IT103 | zs 2017 | 120 | Web design |
Tato tabulka porušuje druhou normální formu, jelikož sloupec Jméno kurzu není plně závislý na celém primárním klíči. Jméno kurzu je zajisté závislé na sloupci ID Kurzu, ovšem není již závislé na sloupci ID Semestru. V takovéto tabulce navíc dochází k redundanci dat, jak lze vidět na opakujících se jménech každého kurzu. Při vypisování již existujících kurzů v budoucnu (v nových semestrech) by docházelo k neustálému opakování tohoto těchto záznamů. Způsob, jakým tento problém vyřešiti je dekompozice tabulky a zajištění, že všechny neklíčové atributy dané tabulky budou závislé na celém klíči. Možným řešením může být například následující rozdělení tabulky:
| ID Kurzu | ID Semestru | Počet míst |
|---|---|---|
| IT101 | ls 2017 | 100 |
| IT101 | zs 2017 | 100 |
| IT102 | ls 2017 | 200 |
| IT102 | zs 2017 | 150 |
| IT103 | zs 2017 | 120 |
| ID Kurzu | Jméno kurzu |
|---|---|
| IT101 | Programování |
| IT102 | Databáze |
| IT103 | Web design |
Při následující dekompozici tabulky máme zajištěné splnění druhé normální formy. Jediný neklíčový atribut v původní tabulce – Počet míst je již závislý na celém složeném primárním klíči, obdobně jako jediný neklíčový atribut Jméno kurzu v tabulce Kurz, který je již závislý na jediném primárním klíči nově vytvořené tabulky.
Třetí normální forma (3NF)
Tabulka je ve třetí normální formě, pokud je ve druhé a pokud neobsahuje tranzitivní závislosti. [1]
Funkční závislost v databázích chápeme jako vztah mezi atributy, kdy tvrzení, že atribut Y je funkčně závislý na atributu X značíme jako X -> Y. Tato závislost zajišťuje, že dva řádky mající stejnou hodnotu atributu X budou mít vždy stejnou hodnotu atributu Y. Tranzitivní závislost pak chápeme jako vztah mezi třemi atributy (X, Y, Z), kdy atribut Y je funkčně závislý na atributu X, atribut Z je funkčně závislý na atributu Y, a proto lze implikovat, že atribut Z je také funkčně závislý na atributu X. Tento vztah značíme jako:
pokud Failed to parse (MathML with SVG or PNG fallback (recommended for modern browsers and accessibility tools): Invalid response ("Math extension cannot connect to Restbase.") from server "https://en.wikipedia.org/api/rest_v1/":): {\displaystyle X \rightarrow Y \land Y \rightarrow Z \Rightarrow X \rightarrow Z}
Nejlépe však tuto normální formu lze pochopit na vypovídajícím příkladu. Mějme následující tabulku evidující jednotlivé filmy, jejich žánry a délku jejich stopáže:
| ID Filmu | ID Žánru | Název žánru | Délka filmu |
|---|---|---|---|
| 1 | 3 | Dokumentární | 01:20:00 |
| 2 | 2 | Akční | 01:05:00 |
| 3 | 1 | Komedie | 01:50:00 |
| 4 | 2 | Akční | 01:10:00 |
| 5 | 3 | Dokumentární | 01:11:00 |
Na první pohled není s tabulkou nic v nepořádku. Tabulka splňuje druhou normální formu, všechny neklíčové atributy jsou závislé na celém primárním klíči (tedy v tomto případě na atributu ID Filmu) Přesto tato tabulka nesplňuje pravidla určená třetí normální formou. V tabulce výše vidíme, že primární klíč identifikující každý film rozhoduje o tom, jaký žánr bude zvolen, tedy jinými slovy atribut ID Žánru je funkčně závislý na atributu ID Filmu (ID Filmu → ID Žánru). Další závislost, kterou můžeme identifikovat je vztah identifikačního čísla žánru a názvu daného žánru, jinými slovy atribut Název žánru je funkčně závislý na atributu ID žánru (ID Žánru → Název žánru). Jak již z definice tranzitivní závislosti víme, z těchto dvou vztahů plyne i vztah třetí, určující funkční závislost atributu Název žánru na primárním klíči ID Filmu. Způsob, jakým lze třetí normální formu uspokojit je dekompozice tabulky, díky čemuž dosáhneme odstranění oné tranzitivní závislosti. Pro příklad výše by dekompozice vypadala následovně:
| ID Filmu | ID Žánru | Délka filmu |
|---|---|---|
| 1 | 3 | 01:20:00 |
| 2 | 2 | 01:05:00 |
| 3 | 1 | 01:50:00 |
| 4 | 2 | 01:10:00 |
| 5 | 3 | 01:11:00 |
| ID Žánru | Název žánru |
|---|---|
| 1 | Komedie |
| 2 | Akční |
| 3 | Dokumentární |
Rozkladem původní tabulky nyní dostáváme dvě tabulky, které již neobsahují žádné tranzitivní závislosti. Navíc jsme díky naplnění třetí normální formy snížili redundanci dat v databázi.
Boyceho-Coddova normální forma (BCNF)
viz. článek zde
Čtvrtá normální forma (4NF)
Čtvrtá normální forma je dalším krokem po uplatnění Boyceho-Coddovy normální formy. Jak již bylo nastíněno v počátku, druhá, třetí a Boyceho-Coddova normální forma řeší funkční závislosti jednotlivých atributů, kdežto čtvrtá a pátá normální forma zkoumá vztahy složených primárních klíčů. Dle jedné z definic navíc od splnění všech předešlých normálních forem umožňuje rozlišení a oddělení nezávislých vícehodnotových atributů vytvářejících složený primární klíč. [2]
Ukažme si opět na příkladu, mějme následující tabulku evidující záznamy o dodavatelích, dodávaných produktech a pobočkách, kde nabízejí své produkty:
| Dodavatel | Produkt | Pobočka |
|---|---|---|
| Dodavatel 01 | Lyže | Praha |
| Dodavatel 01 | Lyže | Liberec |
| Dodavatel 01 | Lyže | Pardubice |
| Dodavatel 01 | Lyžařské brýle | Praha |
| Dodavatel 01 | Lyžařské brýle | Liberec |
| Dodavatel 01 | Lyžařské brýle | Pardubice |
| Dodavatel 02 | Lyže | Praha |
| Dodavatel 02 | Lyže | Liberec |
| Dodavatel 02 | Lyže | Pardubice |
| Dodavatel 02 | Lyžařské brýle | Praha |
| Dodavatel 02 | Lyžařské brýle | Liberec |
| Dodavatel 02 | Lyžařské brýle | Pardubice |
| Dodavatel 03 | Lyže | Pardubice |
Stejně jako předchozí normální formy, i čtvrtá normální forma se snaží zamezit zbytečné redundanci dat. Množství záznamů v tabulce výše bylo zvoleno především proto, aby bylo možné ilustrovat, že i tato normální forma napomáhá ke snížení redundantních dat.
Jelikož tabulka neobsahuje žádné neklíčové atributy (je tvořena pouze složeným primárním klíčem {Dodavatel, Produkt, Pobočka}), máme zajištěné splnění všech předchozích normálních forem. Tato tabulka však nesplňuje pravidla určená čtvrtou normální formou. Vezmeme-li v úvahu, že všichni dodavatelé poskytují všechny své produkty na všech svých pobočkách (a proč by také neposkytovali), zjistíme, že složený klíč je bezpochyby tvořen z nezávislých dat, kdy produkt není vázán na určitou pobočku. Z toho důvodu je porušena čtvrtá normální forma a pro její splnění je zapotřebí provést dekompozici následující tabulky, která by vypadala následovně:
| Dodavatel | Produkt |
|---|---|
| Dodavatel 01 | Lyže |
| Dodavatel 01 | Lyžařské brýle |
| Dodavatel 02 | Lyže |
| Dodavatel 02 | Lyžařské brýle |
| Dodavatel 03 | Lyžařské brýle |
| Dodavatel | Pobočka |
|---|---|
| Dodavatel 01 | Praha |
| Dodavatel 01 | Liberec |
| Dodavatel 01 | Pardubice |
| Dodavatel 02 | Praha |
| Dodavatel 02 | Liberec |
| Dodavatel 02 | Pardubice |
| Dodavatel 03 | Pardubice |
Po dekompozici tabulky již máme splnění čtvrté normální formy zajištěné.
Pátá normální forma (5NF)
Uspokojení páté normální formy tkví ve splnění čtvrté normální formy a navíc požaduje, aby tabulku nebylo možné dále bezeztrátově rozdělovat. Ve všech předchozích normálních formách nedocházelo dekompozicí tabulek ke ztrátě dat [3], ovšem upravíme-li příklad a situaci ze čtvrté normální formy, kdy Dodavatelé již neposkytují všechny své produkty na každé své pobočce, ale pouze některé z nich, při rozkladu dojde ke ztrátě informací. Mějme tedy následující upravenou tabulku z minulého příkladu:
| Dodavatel | Produkt | Pobočka |
|---|---|---|
| Dodavatel 01 | Lyže | Praha |
| Dodavatel 01 | Lyžařské brýle | Praha |
| Dodavatel 01 | Rukavice | Pardubice |
| Dodavatel 02 | Rukavice | Praha |
Když se pokusíme o dekompozici tabulky, stejně jako při uspokojování čtvrté normální formy, dostaneme následující:
| Dodavatel | Produkt |
|---|---|
| Dodavatel 01 | Lyže |
| Dodavatel 01 | Lyžařské brýle |
| Dodavatel 01 | Rukavice |
| Dodavatel 02 | Rukavice |
| Dodavatel | Produkt |
|---|---|
| Dodavatel 01 | Praha |
| Dodavatel 01 | Pardubice |
| Dodavatel 02 | Praha |
Zajisté jsme dekompozicí pomohli snížit redundanci dat v naší databázi (která by byla zjevná především u rozsáhlých databází s velkým počtem záznamů), ovšem ve výsledku jsme ztratili důležité informace o závislosti dodavatelů – produktů – poboček. Při aplikaci některého z možných spojení tabulek, např. přirozeného spojování (NATURAL JOIN) dostaneme následující výsledky:
| Dodavatel | Produkt | Pobočka |
|---|---|---|
| Dodavatel 01 | Lyže | Praha |
| Dodavatel 01 | Lyžařské brýle | Praha |
| Dodavatel 01 | Rukavice | Praha |
| Dodavatel 01 | Rukavice | Pardubice |
| Dodavatel 01 | Lyžařské brýle | Pardubice |
| Dodavatel 01 | Lyže | Pardubice |
| Dodavatel 02 | Rukavice | Praha |
Všimněme si, že dle obdržených výsledků vrácených spojením tabulek Dodavatel-Produkt a Dodavatel-Pobočka došlo k vytvoření tří nových záznamů, které tvrdí, že např. Dodavatel 01 prodává Lyžařské brýle v Pardubicích, což ovšem v porovnání s původní tabulkou víme, že není pravda. Uvažujme tedy o dekompozici původní tabulky na tři tabulky oproti dvěma původním. Tedy kromě tabulek Dodavatel-Produkt a Dodavatel-Pobočka přidáme ještě tabulku Pobočka-Produkt evidující vztah mezi produkty a jejich dostupností na jednotlivých pobočkách. Mějme tedy navíc následující tabulku:
| Pobočka | Produkt |
|---|---|
| Praha | Lyže |
| Praha | Lyžařské brýle |
| Praha | Rukavice |
| Pardubice | Rukavice |
Problém však nastává ve chvíli, kdy se pokusíme tyto tři tabulky spojit a dostat tak původní data. Po chvíli zkoušení zjistíme, že se nám takové spojení realizovat nepodaří a že není způsob, jak bychom dekompozicí původní tabulky mohli provést bezeztrátově. Tedy lze konstatovat, že tabulka Dodavatel-Produkt-Pobočka již nelze dále bezeztrátově rozložit (je tedy v 5NF), a proto se musíme spokojit s redundancí dat původní tabulky pro zajištění, že nepřijdeme o žádná potenciálně důležitá data.
Víme už tedy o jaké tabulce můžeme konstatovat, že je v páté normální formě. Jak by však vypadala tabulka, která pátou normální formu porušuje? Upravíme-li záznamy původní tabulky a určíme-li, že se v Praze dodávají všechny produkty, ale v Pardubicích pouze lyžařské brýle (důvod této diskriminace ponecháme záhadou), bude nová tabulka vypadat následovně:
| Dodavatel | Produkt | Pobočka |
|---|---|---|
| Dodavatel 01 | Lyže | Praha |
| Dodavatel 01 | Lyžařské brýle | Praha |
| Dodavatel 01 | Lyžařské brýle | Pardubice |
| Dodavatel 02 | Lyže | Praha |
| Dodavatel 02 | Lyžařské brýle | Praha |
Budeme-li postupovat stejně jako při pokusu o dekompozici bezeztrátově nerozdělitelné tabulky výše, dostaneme následující tři tabulky:
| Dodavatel | Produkt |
|---|---|
| Dodavatel 01 | Lyže |
| Dodavatel 01 | Lyžařské brýle |
| Dodavatel 02 | Lyže |
| Dodavatel 02 | Lyžařské brýle |
| Dodavatel | Produkt |
|---|---|
| Dodavatel 01 | Praha |
| Dodavatel 01 | Pardubice |
| Dodavatel 02 | Praha |
| Pobočka | Produkt |
|---|---|
| Praha | Lyže |
| Praha | Lyžařské brýle |
| Pardubice | Lyžařské brýle |
Při pokusu o jejich opětovné spojení, zjistíme, že se nám v tomto případě podařila bezeztrátová dekompozice a výsledkem naší selekce je totožná tabulka s tabulkou před rozdělením. Po spojení těchto tabulek tedy získáváme:
| Dodavatel | Produkt | Pobočka |
|---|---|---|
| Dodavatel 01 | Lyže | Praha |
| Dodavatel 01 | Lyžařské brýle | Praha |
| Dodavatel 01 | Lyžařské brýle | Pardubice |
| Dodavatel 02 | Lyže | Praha |
| Dodavatel 02 | Lyžařské brýle | Praha |
Jelikož se nám podařilo opětovně spojit tři rozdělené tabulky a získat tak nezměněnou původní tabulku, v tomto případě lze konstatovat, že tabulka nesplňuje pravidla stanovená pátou normální formou. Srovnáme-li oba příklady této normální formy, uvědomíme si, že hlavním rozdílem bylo uvedení rozšiřujících informací, které nejsou ihned zjevné při pohledu na samotnou tabulku. Identifikování tabulek, které porušují pátou normální formu bývá v praxi velmi složité, a navíc reálný přínos její aplikace bývá často nezřetelný. Pátá normální forma patří mezi formy založené spíše na teoretickém zázemí, postrádajíc reálně dosažitelné výsledky při aplikaci v praxi.
