Normalizace

Nebojte se, nebudeme se bavit o násilném potlačení Pražského jara 1968 armádami Varšavské smlouvy. Normalizace databáze je proces, při kterém se tabulky převedou do takového tvaru, aby vyhověli tzv. normálním formám. Normální formy jsou pravidla, jak správně navrhnout tabulky, aby se zamezilo různým anomáliím při spravování databáze, jak o jsou třeba duplicity, nebo nekonzistentní data. Zní to hrozně vědecky, ale vlastně jsou to jen takové „jednoduché“ návody, jak si ulehčit život.

Normální formy

Normalizace databáze patří čistě do databázové teorie. Pro tentokrát tedy můžete zapomenout na PostgreSQL, MySQL či jiné DBMS. Vlastně můžete zapomenout na celé SQL, normalizace se týká čistě návrhu tabulek.

Existuje 6 normálních forem (NF), které jsou očíslované od 1 do 5. Boyce-Coddova normální forma (BCNF) má číslo 3.5 (protože je hodně podobná 3NF).
Platí, že aby byla tabulka v nějaké NF, musí být i ve všech předešlích NF (tak to bylo definováno a tak to prostě je).

Nejdůležitější jsou první 3NF, možná ještě ta 3.5, takže se budu věnovat hlavně jejich vysvětlování.

Diagram tabulek

Návrh telefonní databáze

Pokud by vás NF zaujaly natolik, že byste se chtěli dozvědět i jejich přesné matematické definice, historii, nebo něco více o 4NF a 5NF, doporučuji se podívat na anglickou wikipedii.

NF budu vysvětlovat na návrhu databáze z kapitoli o telefonní databázi, viz diagram.

1NF

Atributy obsahují pouze atomické hodnoty a každá hodnota obsahuje pouze jednu hodnotu.

Atributem se, zjednodušeně řečeno, myslí sloupeček (Dle teorie sloupeček obsahuje atribut objektu, který je reprezentován tabulkou).

1NF je na pochopení asi nejjednodušší. V příkladu ji porušuje tabulka kontakt. Sloupeček adresa totiž neobsahuje atomickou (dále nedělitelnou) hodnotu – adresu můžete rozdělit na město, městksou část, ulici, číslo ulice a PSČ (našli by se i další věci, co by se k adrese mohli hodit, ale o to tu teď nejde).

Diagram tabulek

Telefonní databáze 1NF

Ačkoliv na takové to domácí použití ničemu nevadí, že mám adresu jako jeden sloupeček, přináší porušení 1NF několik ošklivých praktických problémů. Jak třeba zjistíte, kolik lidí pochází z Prahy 1? Mohli byste zkusit použít operátor LIKE, nebo regulární výrazy, ale výsledek by byl nejistý (tak to může být v adrese napsáno cokoliv a jakkoliv). A taky by to bylo dost pomalý.

Řešení je vidět na obrázku – sloupec adresa jsem rozdělil na sloupce adresa_mesto, adresa_mestska_cast, adresa_ulice, adresa_ulice_cislo, adresa_psc.

Pravda je, že původní návrh (s jedním sloupečkem adresa) by se vám pro soukromé účely asi používal snadněji. Čas od času i profesionálové porušují normálni formy při návrhu databáze, ale většinou se to krutě nevyplatí. Pokud chcete porušit NF, musíte k tomu mít sakra dobrý důvod a dokázat si to obhájit.

Ta část NF, která se zmiňuje o pouze jedné hodnotě, znamená, že byste neměli například do sloupečku firma_nazev vkládat názvy dvou firem (oddělené třeba středníkem), pokud někdo náhodou tak moc pracuje. Ono to k tomuto řešení svádí, když v návrhu nepočítáte s tím, že by někdo mohl pracovat ve více firmách a dodatečně přemýšlíte, jak to vyřešit …). Problém je stejný jako v předchozím příkladě – jak vyhledat lidi z jedné firmy? Jak odebrat ze sloupečku jednu firmu, ale další tam nechat?
Místo toho byste měli vytvořit extra tabulku pro firmy a odkazovat se z této tabulky na kontakt. Problém vyřešen.

2NF

Tabulka (relace) musí být v 1NF a každý neklíčový atribut tabulky musí být plně závislý na celém primárním klíči.

2NF, ač se to třeba nezdá, je vlastně taky jednoduché pravidlo. Už víte, že PK se může skládat z více sloupečků. Proto se v 2N hovoří o celém primárním klíči. Neklíčový atribut je prostě sloupeček, který není součástí PK. Nedá se rovnou napsat, že jde o sloupeček, který není PK právě proto, že PK se může skládat z více sloupečků (proto zní 2NF hrozně učeně).

V příkladu porušuje 2NF tabulka telefon. Primární klíč se skládá z předvolby a telefonu. Neklíčový atribut operator_id je ovšem závislý pouze na předvolbě. Pro každou stejnou předvolbu musí být stejný operátor (to je ta závislost).*

* Tento příklad byl psán v době, kdy nebylo možné odejít se stejným číslem k jinému operátorovi. Dnes už můžete mít telefony se stejnou předvolbou ale s jiným operátorem. Pro teď prosím předstírejme, že to tak není a že je operátor závislý na předvolbě (a vice versa).

Tato závislost může vést k akutalizační anomálii – můžete změnit operátora u některých řádků s určitou předvolbou, ale u jiných řádků ne.

Ostatní atributy jsou už závislé na celém PK. Předvolba, ani zbytek čísla (telefon) vám nestačí k tomu, abyste určili, kterému kontaktu, nebo kterému tarifu telefon patří.

Řešením pouršení 2NF je vytvoření extra tabulky pro závislost mezi částí PK a závislým atributem. V příkladu už taková tabulka je – predvolba je relací mezi předvolbou a operátorem. Takže vlastně stačí sloupeček operator_id z tabulky telefon smazat a je vystaráno.

Diagram tabulek

Telefonní databáze 2NF

Teď ale zpět do reality. Závislost mezi předvolbou a operátorem už neexistuje. Takže co s tím? Sloupeček operator_id v tabulce telefon ponechám, abych si mohl udržovat informaci o tom, který operátor dané číslo obsluhuje. Takovou informaci ale nebudu mít asi vždycky k dispozici, takže si ho změním na nepovinný.

Zajímavější je to s relací predvolba. Tato relace měla říkat, jaká předvolba patří jakému operátorovi. Protože tato relace už neexistuje, zdá se tabulka predvolba zbytečná. Protože existují jen určitá čísla jako předvolba, mohl bych si ponechat tabulku predvolba pouze se sloupečkem predvolba.
Díky odkazování se z tabulky telefon do tabulky predvolba bude zajištěno, že se do tabulky telefon nedostane nějaká neexistující předvolba.
Ale vlastně, proč si neudržovat informaci o tom, kterému operátorovi byly původně předvolby přiřazeny? Takže si ponechám i sloupeček operator_id, jen už nebudu tvrdit, že se jedná o operátora, který obsluhuje všechny telefony s těmito předvolbami, ale o operátora, který vydává telefony s těmito předvolbami (a obsluhuje většinu z nich). Hlavní je umět si to okecat :-).

3NF

Tabulka (relace) musí být v 2NF a všechny neklíčové atributy vzájemně nezávislé.

Protože je tabulka v 2NF, všechny atributy jsou závislé na celém PK. Může se ovšem stát, že jsou závislé mezi sebou a tím porušují 3NF. V Příkladu to porušují sloupce firma_nazev a firma_poznamka z tabulky kontakt. Firma je závislá na PK tabulky, protože nás zajímá název firmy, ve které daný kontakt pracuje – na základě kontaktu (člověka) se dá jednoznačně říct, jaká firma má v sloupečku firma_nazev být.
Stejně tak to platí, ač to nemusí být úplně zřejmé, i pro firma_poznamka. Stačí mi znát kontakt a vím, co by mělo být v poznámce – tak nějak díky tomu, že vím, v jaké firmě kontakt pracuje. A ejhle, máme tu nějakou další závislost. Ze sloupečku firma_nazev můžu jednoznačeně říct, co má být ve sloupečku firma_poznamka.

Tohle samozřejmě platí, pokud se poznámka o firmě skutečně týká firmy. Pokud byste do poznámky psali třeba na jaké pozici kontakt pracuje, pak už ze sloupce firma_nazev nepoznáte, co by mělo být v sloupci firma_poznamka

Diagram tabulek

Telefonní databáze 3NF (pokus 1)

3NF také řeší problém akutalizační anomálie – mohli byste mít různé řádky se stejnou firmou, ale jinou poznámkou –. Což, jak jsem vysvělil v poznámce výše, nechcete. kdybyste to chtěli, nejednalo by se o porušení 3NF.

Obdobný problém je s adresou. Třeba městská část závisí na městě.

Problém s 3NF se řeší vytvořením extra tabulky. Vytvořím tabulky pro firmu a pro adresu a z tabulky kontakt se na ně budu odkazovat pomocí cizího klíče. Řešení můžete vidět na obrázku.

Všiměte si vedlejšího efektu normalizace. Přibyly nám tabulky, ale hlavně máme v databázi více integritních omezení, což zlepšuje konzistenci dat. Například jsem mohl určit, že když už zadávám firmu, požaduji její název (v předchozím návrhu jste mohli zadat poznámku k firmě, aniž byste zadali název firmy). Adresa má přesně daný formát a navíc vyžaduji, aby obsahovala alespoň město a ulici. Pořád ale platí, že ke kontaktu adresu ani firmu mít nemusím (adresa_id i firma_id jsou nepovinné).

Diagram tabulek

Telefonní databáze 3NF (pokus 2)

Další tabulka, která nesplňuje 3NF, je tabulka telefon. Sloupec operator_id se totiž dá odvodit ze sloupce tarif_id. Přes tarif_id se dostanete do tabulky tarif, kde je operator_id, který by měl být stejný, jako v tabulce telefon (nemůžete přece k telefonu přiřadit tarif, který se vztahuje na jiného operátora).

Nejjednodušší řešení by bylo odstranit sloupec tarif_id z tabulky telefon a zjišťovat id tarifu přes tabulku tarif. Jenomže odkaz do tabulky tarif není povinný. Kam byste pak uložili informaci o operátorovi, když nebudete znát tarif?

Další možností je nepoužívat umělý klíč tarif_id, ale místo něho přidat do tabulky telefon tarif_nazev. Odkazem do tabulky tarif by pak byla dvojice operator_id a tarif_nazev. Jestli si vzpomínáte, tak tato dvojice je v tabulce tarif unikátní – mohla by sloužit jako primární klíč místo uměle vytvořeného PK id. Takto se vám už nebude moci stát, že byste updatovali tarif_id v tabulce telefon, ale pořád jste se odkazovali na tarif pro jiného operátora. DBMS vám dokonce umožní vložit jen operator_id a tarif_nazev nechat NULL, ikdyž tvoří oba sloupce cizí klíč. Takže krásný sluníčkový den.

Blbý je, že tabulka může mít jen jeden PK, takže každá další tabulka, co se bude chtít odkazovat na tabulku tarif, bude muset používat tuto dvojici. Je na zvážení (stejně jako u tabulky adresa), zda se to prakticky vyplatí. Možná by stálo za úvahu vrátit se k prvnímu řešení. Udělat tarif povinný s tím, že pro každého operátora vložíte do tabulky tarif „neznámý tarif“. Cena u takového tarifu by byla NULL.

Všiměte si, že s tabulkou predvolba tento problém není. V tabulce predvolba znamená operator_id něco jiného než operator_id v tabulce telefon, takže je úplně vpořádku, když budou mít různou hodnotu.

3.5NF (BCNF)

Tabulka (relace) musí být v 3NF a všechny determinanty funkční závislosti jsou zároveň kandidátním klíčem.

Je tabulka adresa skutečně v 3NF? Je a není. Už číslo ulice porušuje 1NF, protože by mělo správně být rozdělené na číslo popisné a číslo orientační. Pro zjednodušení to teď ale zanedbám. Dělám si soukromou databázi, ne databázi pro poštovní úřad, takže mě takto rozdělené číslo nebude nikdy zajímat.
2NF tabulka neporušuje (teda porušuje, protože číslo ulice porušuje 1NF, ale víte co chci říct).

Nejzajímavější je problém 3NF. Totiž, nedá se náhodou z městské části jednoznačně říct, v jakém jsme městě? A z ulice v jaké jsme městské části?

Z názvu městské části Praha 1 snadno poznáte, že jste v Praze. Kdybychom si u městské části zaznamenávali i GPS souřadnice, tak i pro stejně se jmenující městské části z různých měst můžeme jednoznačně říct, do jakého města patří (obdobně to platí i pro ulice). Zůstaňme ale při tom, že názvy ulic a městských částí nejsou jedinečné, takže z nich nepoznáme, kam patří. 3NF tak neporušujeme.

Co ale PSČ? Z atributů (město, ulice) lze PSČ zjistit. Je tu tedy závislost (město, ulice) → PSČ.

Zdálo by se, že se porušuje 3NF, protože PSČ není součástí PK a je závislý na něčem, co není PK. Jenže 3NF říká, že všechny neklíčové atributy musí být vzájemě nezávislé. PSČ je ale klíčový atribut (je částí kandidátního klíče).

Tabulka adresa má následující kandidátní klíče (tj. atributy, nebo skupiny atributů, které by mohli sloužit jako PK. Tabulka může mít více kandidátních klíčů, ze kterých se pak jeden libovolně vybere jako PK):

(město, ulice, cislo_ulice) → (mestska_cast, psc)
(PSČ, ulice, cislo_ulice) → (mestska_cast, město)

V tabulce jsou tedy 2 kandidátní klíče – 2 skupiny atributů, které budou v tabulce vždy unikátní (zapomeňte teď na umělý klíč id). Všechny atributy, které jsou součástí některého z kandidátních klíčů, jsou klíčové atributy. Není tedy možné, aby porušili 3NF.

A od toho je tu právě BCNF, která je trochu silnější variantou 3NF. Rozdíl mezi BCNF a 3NF je natolik formální, že si lidi často myslí, že tabulka porušuje 3NF, když ve skutečnosti porušuje BCNF.

Determinanty funkční závislosti v tabulce adresa jsou:

(mesto, ulice, cislo_ulice) → (mestska_cast, psc)
(PSC, ulice, cislo_ulice) → (mestska_cast, město)
(město, ulice) → (PSČ)
(PSC) → (město)

To co je na levé straně determinuje (jednoznačně určuje) to, co je na pravé straně. Ovšem poslední dvě závislosti porušují BCNF – determinanty nejsou kandidátním klíčem. Tyto dvojice nejsou pro tabulku adresa unikátní. Nejde ovšem o porušení 3NF, protože PSČ i město nejsou neklíčové atributy.

Tabulky v BCNF

Telefonní databáze v BCNF

Řešením je, jak jinak, rozdělením tabulky adresa do více tabulek. Tabulka pošta bude obsahovat závislost (PSC) → (město), v tabulce adresa zbytek. Viz diagram.

Teď už nemůže dojít k anomálii, kdy byste měli stejné PSČ pro různá města. Ale musel jsem změnit v tabulce adresa psc na povinný atribut. Je otázka k zamyšlení, zda to stojí za to. A jestli taková anomálie natolik nebezpečná, že se vyplatí přidávat další tabulku. (Obvykle ano :-).

Pokud umíte trochu anglicky, doporučuju se podívat ještě na příklad na anglické wikipedii o Boyce–Codd normal form. Najdete tam další jednoduchý příklad a taky se dozvíte, že ne vždy lze beztrestně dosáhnout BCNF.

4NF

Tabulka (relace) musí být v BCNF a musí obsahovat pouze příčinné souvislosti (mezi klíčem a atributy).

Příklad a vysvětlení najdete na anglické wikipedii Fourth normal form.

5NF

Tabulku (relaci) již není možno bezestrátově rozložit.

Příklad a vysvětlení najdete na anglické wikipedii Fifth normal form.

Komentář Hlášení chyby
Created: 16.1.2014
Last updated: 12.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..