Normalizace relačních databází

S relačními databázemi se při vývoji aplikací setkáváme velice často, jelikož se povětšinou jedná o nejlepší možnost uložení a spravování dat aplikace. Dnes se podíváme, co se skrývá pod jejich normalizací a tzv. „normálními formami“.

Relační databáze vyházejí z modelu, kdy jsou data uložena v tzv. „relacích“. Od svých předchůdkyň založených na síťovém a hierarchickém modelu se liší tím, že jsou budovány na pevném teoretickém základě. Prvním, kdo se definicemi relací a relačních databází zabýval, byl matematik E. F. Codd při práci v laboratořích IBM v San José. Pojem relace tedy pochází z teorie relačních databází, ale běžně je spíše používán pojem tabulka. Data jsou tedy uspořádána v tabulkách. Tabulka je dvourozměrná datová struktura složená ze záhlaví, kde jsou definovány sloupce (atributy), a samotných řádků (prvků), což jsou n-tice hodnot. V každém sloupci mohou být pouze hodnoty stejného druhu. Řádek, někdy nazýván jako záznam, reprezentuje jeden objekt. Průsečík řádku a sloupce se nazývá pole.

Aby mohl být každý řádek relace přesně identifikován, musí být v relaci definován tzv. „primární klíč“. Primární klíč je jedno, či více polí, která jsou pro každý řádek jedinečná (neboli žádné dva řádky nemohou mít všechna pole v primárním klíči společná). V případě, že je v klíči obsaženo více polí, jedná se o primární klíč složený. Kromě primárního klíče může relace také obsahovat tzv. „cizí klíče“. Cizí klíč je opět jedno, či více polí, která ovšem neslouží k identifikaci řádku samotného, ale k vytvoření spojení mezi jiným řádkem jiné, a dokonce i stejné relace.

Nyní se již můžeme dostat k samotné normalizaci. Co to vlastně normalizace je? Je to proces, při kterém se relace rozkládají za účelem jednodušší práce s daty, jejich lepší manipulace, zabránění redundance dat (dat, která se zbytečně opakují) a lepší konzistence dat (což dosti souvisí s tou redundancí). Normalizace ale není proces, který by zároveň znamenal navýšení výkonu databáze. Také to není úloha, která by byla deterministická, takže existuje více „správných“ řešení.

Normální formy jsou pak pravidla, která by data v relaci měla splňovat. Čím vyšší normální forma, tím lepší a jednodušší by práce s daty, jejich vybíráním a aktualizacemi měla být. Formy jdou postupně od nižších k vyšším, kdy každá vyšší v sobě zahrnuje formy nižší. Co je také zajímavé, je fakt, že pokud jsou v relaci použity pouze jednoduché klíče, dochází k tzv. „splývání normálních forem“. (Viz následující obrázek. Při použití jednoduchých klíčů „mizí“ přerušované čáry, a tak například relace, která je v 3NF, je automaticky i v 5NF).

Nultá a první normální forma (0NF a 1NF)

Je vidět, že nultá forma se nám ani do obrázku s definicemi normálních forem nedostala. Je to tím, že v nulté normální formě se relace vyskytne tehdy, jsou-li data v nějakém poli dále dělitelná (nejsou atomická), což se docela kříží s relačním modelem, který praví, že data v jednom poli již dále dělitelná být nesmí. A teď nějaké definice:

Relace je v nulté normální formě, existuje-li takové její pole, které obsahuje více než jednu hodnotu.

Není-li relace v nulté normální formě, je alespoň v první normální formě.

Lepší bude asi dát nějaký příklad. A jelikož se v dnešní době na Internetu stalo velice populární záležitostí „štítkování“, uveďme si příklad třebas s ním. Řekněme, že máme relaci, ve které uchováváme články. Každý článek má nějaký svůj Název, Text (nebude v příkladech vypisován), Datum vydání a seznam štítků. Prvním nápadem na vytvoření tabulky by mohlo být něco následovného (podtržené názvy sloupců tvoří primární klíč, tato konvence bude zachována po zbytek článku):

Jak je vidět, ve sloupci Štítky je štítků hned několik. Krom toho, že to porušuje princip relačního modelu, což by nemuselo být až tak hrozné, bude zde problém, jak například zjistit podobné články k jinému článku. První řešením by mohlo být udělat několik sloupců pro štítky. Ovšem to by problém s vybíráním podobných článků zas moc dobře nevyřešilo. Řekněme, že bude deset sloupců pro štítky a budeme mít deset štítků, k nimž máme podobné články zjistit. A teď napište všechny ty podmínky… Obecně rozšiřování tabulek do šířky není nejlepší metoda. Mnohem lepší bude rozložit relaci na dvě menší relace. Při rozkládání je potřeba myslet na to, že musí jít nějakým způsobem zpátky získat data z původní relace, což při rozložení této relace na dvě (relace Články se sloupci Název, Text a Datum vydání a relace Štítky se sloupci Název článku a Štítek) splňuje.

Druhá normální forma (2NF)

Jelikož se nám podařilo tak pěkně zvládnout první problém s články a se štítky, nebylo by od věci si na stránkách založit taky nějakou tu galerii. Bude se jednat o jednoduchou galerii, takže bude stačit jedna tabulka se vším potřebným. Jako primární klíč byly zvoleny sloupce Název fotky a Album, aby byla možnost mít více fotek se stejným názvem, ale v jiných albech. Dalšími atributy jsou pak Popis alba, Popis fotky a Fotka, kde je uložena samotná fotka v binární podobě.

Do galerie jsme uložili nějaké fotky (jelikož se do každého alba podařilo nasoukat již 50 fotek, toto je jen zkrácený výpis):

Tak si už můžeme prohlédnout nějaké to album. Co třeba Prázdniny 2008. Super, sjíždíme na první fotku, ale co to? Tady něco nesedí. V popisu alba je hrubka, až to bolí. Špatné. Ale to nic, naštěstí se to dá opravit. Avšak problém je ten, že budeme muset upravovat Popis alba u každé fotky zvlášť. Nemilé zjištění. A to všechno kvůli tomu, že sloupec Popis alba nezávisí na celém klíči, ale jen na jeho části – jen na Albu. Aby tabulka byla v 2NF, musí všechna data záviset na celém klíči. Jak je vidět, nesplnění 2NF může znamenat redundanci dat, jejich složitou změnu a možnost „UPDATOvých“ anomálií (např. pokud změníme Popis alba u jedné fotky ale u druhé ne, obě budou ve stejném albu, ale pro každou bude Popis alba jiný).

Naštěstí se to dá celkem snadno spravit rozpadnutím na dvě tabulky. Tabulka s fotkami zůstane, ale už v ní nebudeme mít sloupec Popis alba. Ten se přesune do nové tabulky Alba, která bude obsahovat primární klíč Album a sloupec Popis alba. Nyní se již popis alba dá změnit z jednoho místa.

Třetí normální forma (3NF)

Články hotovy, galerie také. Teď už se jen stačí kochat výsledkem práce. Nebo ne? Ne, nestačí. V e-mailové schránce totiž skončila zpráva, v níž se odesilatelka ptá, jestli by nebylo možno k fotkám a článkům přidávat komentáře. Je to sice práce navíc, která nebyla plánovaná, ale protože odesilatelka připojila svou fotku a nevypadá na ní špatně, proč ne. Přece to nemůže být tak těžké.

První, co přichází na mysl, je, jak udělat, aby byly komentáře jak pro články, tak pro fotky? Dvě tabulky? Jedna pro komentáře ke článkům a druhá pro komentáře k fotkám? Ne, to ne. Lepší budou tři tabulky. Jedna pro samotné komentáře se sloupci ID komentáře, Komentář, Autor, Autorův e-mail a Autorovy stránky a pak vazebná tabulky pro články (ID komentáře, Název článku) pro a fotky (ID komentáře, Název fotky, Album). Potěšeni z takového skvělého designu jsme stránky rovnou spustili. Zde jsou některé z komentářů:

Jak je vidět, komentáře měly úspěch. Ale jsou zde dva problémy. Uživatel se může vydávat za někoho jiného a spamboti jsou velice rádi, že si mohou někde rozházet své odkazy na různé stránky. Co s tím teď? Spamboti by se dali odstřihnout nějakou antispamovou ochranou, ale zase je zde chudák Honza Jelimán, který má buď rozdvojenou osobnost, nebo se za něj vydává někdo jiný? Takže volba padá na registraci. Ale jak to teď vyřešit? S tím nebylo počítáno.

Přidat sloupec pro heslo by asi nebylo to pravé ořechové, jelikož by se muselo zbytečně opakovat u každého řádku se stejným autorem. Problém by byl v tom, že heslo by nebylo závislé na Autorovi, což není klíčová hodnota (vznikla by závislost mezi dvěma neklíčovými hodnotami). Vybruslit z toho ven jde opět pomocí rozpadu na více tabulek. Komentáře se zbaví Autorova e-mailu a Autorových stránek, protože ty přejdou do tabulky Komentující, která bude mít kromě klíčového sloupce Autor navíc sloupec Heslo. (Spam a hádka s druhým Honzou Jelimánem, či druhým já Honzy Jelimána (což se však již nikdo nikdy nedozví) byla vymazána.)

U 3NF je zase vidět, že její nesplnění by zapříčinilo stejné problémy jako u 2NF. Navíc bychom člověka smazáním všech jeho komentářů připravili o identitu (neexistoval by žádný záznam s daty uživatele). Rozpad na dvě tabulka je opět nejjednodušší.

Boyce-Coddova normální forma (BCNF)

Možnost přidávat články, vlastní galerie, komentáře ke všemu – skvělá to věc. Proto na sebe vydání Portálu verze 1.0 nenechalo dlouho čekat. Mimo jiné si ho také pořídil ředitel jedné vlivné firmy se sítí prodejen toaletního papíru. Systém se mu velice líbil a jelikož databázoví analytici v jeho firmě byli v koncích s jedním problémem, zavolal nás, abychom mu s problémem pomohli.

Celý problém tkví v tom, že lidé z Toalpapu (jméno té firmy) se chtěli pochlubit svým konkurentům, kde všude v České republice již jejich prodejny byly. Ale poté zjistili, že v databázi se nachází už jen údaje o prodejnách, které jsou stále v provozu. Všechny města, ve kterých byly prodejny zrušeny (většinou kvůli vyrabování místními občany při tzv. „záchodových krizích“), již v databázi nejsou. Relace vypadá následovně:

Jak je vidět, relace splňuje 1NF, protože všechny údaje jsou atomické. Splňuje i 2NF, protože všechna neklíčová data závisí na celém primárním klíči. Neklíčová data jsou taky mezi sebou nezávislá, takže je tabulka i v 3NF. Všechno vypadá dobře, ale pořád je tu případ, kdy se vymazáním všech prodejen z jednoho města ztratí informace o celém městě. Je to kvůli tomu, že není splněna BCNF.

Abychom jí vyhověli, bude potřeba relaci rozložit na menší relace. V tomto případě bude nejlepší kombinace relace prodejny s atributy Název prodejny, Ulice, PSČ, Telefon a relace Města s atributy Město a PSČ. Teď se nám již nemůže stát, že bychom se smazáním všech prodejem z jednoho města zbavili města jako celku.

K porušení BNCF dochází jen ve specifických případech a většinou, pokud se podaří relaci upravit do 3NF, je i v BCNF.

Čtvrtá normální forma (4NF)

Práce na upravení relací u Toalpapu byla vysilující, ale podařilo se. Teď by to chtělo nějaký odpočinek, proto se opět uchýlíme k našemu Portálu. Jelikož dnes je velice v módě, aby byl web sociální, aby byl obsah tvořen lidmi, stálo by za to zvážit zřízení verze pro více uživatelů najednou. Autory můžeme použít z komentářů a tabulku pro články již také máme. Teď by ale bylo lepší, aby mohl být portál nějak členěn do sekcí, přeci jenom víc autorů bude znamenat více článků, a kdyby byly články jen tak „na hromádce“, nemuselo by to dělat dobrotu. Štítkování je super, ale už ho máme, takže sekce. Řekněme, že jeden článek může být ve více sekcích a zároveň může mít více autorů.

Relace sdružující autory, články a sekce dohromady by mohla vypadat následovně:

Vypadá to obstojně. Relace se nám vešla do 1NF, 2NF, 3NF i BCNF. Ale co taková 4NF? To už ne. Proč? Autor a Sekce totiž spolu žádnou závislost nemají a klíč podle 4NF nesmí být tvořen z nezávislých dat. Taky je vidět, že pokud má článek více autorů, či by byl umístěn do více sekcí, zbytečně by nám v relaci vznikala data redundantní. A navíc toto uspořádání dává falešný dojem, že spolu Autor a sekce nějak souvisí. Opět to jde vyřešit rozpadem na dvě relace.

Pátá normální forma (5NF)

Portálu teď jsou dvě verze – Portál a Portál MU. Skvělý úspěch. Hned, jak se vedení Toalpapu dozvědělo o verzi MU, umístilo ho na své stránky. A dalo svým zaměstnancům možnost, aby tam umisťovali svůj obsah. Databázovým analytikům z Toalpapu se líbilo, jak jsme vyřešili problém s Autory a Sekcemi, a jelikož se jim vyskytl v jejich databázi další problém, komu prvnímu asi zavolali?

Problém byl následující, opět jim zmizela některá potřebná data. Tentokrát se ale jednalo o věc dokonce na nadnárodní úrovni, proto je problém daleko větší. Toalpap rozšířil své toaletní papíry po celém světě hlavně díky výborné síti prodejců. Každý prodejce se pohybuje v jedné a více zemích a prodává tam jeden a více výrobků. Navíc v každé zemi je oblíben nějaký výrobek (který tam daný prodejce, resp. daní prodejci, samozřejmě prodává, resp. prodávají). Je to trochu zamotané, ale tabulka by mohla udělat více jasno.

Problém nastal tehdy, když Jan Jelimán (hned nám je jasnější, odkud se ředitel Toalpapu dozvěděl o skvělém Portálu verze 1.0) opustil firmu. Krom toho, že se ztratili veškeré kontakty s Německem a znalost o Němcích a Japoncích jako o vyznavačích Extra hrubého papíru, taky chudák Evženie musí teď létat z Japonska přes Rusko do Indie a není jasné, jak dlouho to může vydržet.

Relace sice splňuje všechny normální formy až do 4NF, ale i tak se mohou některé data „ztratit“. Je jasné, že řešením bude relaci převést do 5NF. Pokud bychom však rozložili relaci na tři menší – Prodává se sloupci Prodejce a Výrobek, Prodává v zemi se sloupci Prodejce a Země a relaci Oblíbený výrobek se sloupci Země a Výrobek –, byl by tu problém, že bychom již nebyli schopni dostat původní data. Jak to teď vyřešit? Ponecháme obojí. Takže se nám z jedné tabulky stanou čtyři. Víme, kdo co a kde prodává, a i když odstraníme z původní tabulky některé údaje, stále budou dohledatelné pomocí tabulek zbývajících.