Normalizace relačních databází
 x   TIP: Přetáhni ikonu na hlavní panel pro připnutí webu
Reklama

Normalizace relačních databázíNormalizace relačních databází

 

Normalizace relačních databází

Google       Google       23. 7. 2008       49 495×

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“.

Reklama
Reklama

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):

Název Text Datum vydání Štítky
Normalizace relačních databází 19. 7. 2008 relační databáze, databáze
Úvod do SQL 5. 7. 2008 relační databáze, databáze, sql
SQL a OQL 10. 7. 2008 relační databáze, databáze, objektová databáze, sql, oql

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.

Název Text Datum vydání
Normalizace relačních databází 19. 7. 2008
Úvod do SQL 5. 7. 2008
SQL a OQL 10. 7. 2008

Název článku Štítek
Normalizace relačních databází relační databáze
Normalizace relačních databází databáze
Úvod do SQL relační databáze
Úvod do SQL databáze
Úvod do SQL sql

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):

Název fotky Album Popis alba Popis fotky Fotka
Na vrcholku světa Prázdniny 2008 Fotky z prázdnin 2008 Já na nejvyšším kopci v okolí. 00100101101011…
Procházka lesem Prázdniny 2008 Fotky z prázdnin 2008 Když se člověk nudí a potřebuje si oddychnout. 01010101010110…
Spící klokani ZOO Praha – červenec 2008 Zvířata ze ZOO Praha na návštěvě o prázdninách 2008. Ti se mají… 00101001011010…

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.

Název fotky Album Popis fotky Fotka
Na vrcholku světa Prázdniny 2008 Já na nejvyšším kopci v okolí. 00100101101011…
Procházka lesem Prázdniny 2008 Když se člověk nudí a potřebuje si oddychnout. 01010101010110…
Spící klokani ZOO Praha – červenec 2008 Ti se mají… 00101001011010…

Album Popis alba
Prázdniny 2008 Fotky z prázdnin 2008
ZOO Praha – červenec 2008 Zvířata ze ZOO Praha na návštěvě o prázdninách 2008.

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ářů:

ID komentáře Komentář Autor Autorův e-mail Autorovy stránky
1 Vážně skvělý stránečky. CMUK. sexy17ka sexy17ka@seznam.cz http://sexy17ka.blog.cz
5 Taky jsem na tomhle kopci byl. Je to tam pěkný Honza Jelimán honza@jeliman.cz http://honza.jeliman.cz
20 Změnil jsem názor, je to díra. Honza Jelimán honza@jeliman.cz http://honza.jeliman.cz
21 To předtím je falešný Honza. Mně se tam vážně líbí. Honza Jelimán honza@jeliman.cz http://honza.jeliman.cz
28 http://porn.com SPAMBOT spambot@porn.com http://porn.com/spambot

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.)

ID komentáře Komentář Autor
1 Vážně skvělý stránečky. CMUK. sexy17ka
5 Taky jsem na tomhle kopci byl. Je to tam pěkný. Honza Jelimán

Autor E-mail Stránky Heslo
sexy17ka sexy17ka@seznam.cz http://sexy17ka.blog.cz 123456
Honza Jelimán honza@jeliman.cz http://honza.jeliman.cz moje tajné heslo

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ě:

Název prodejny Ulice Město PSČ Telefon
Toalpap – Černokostelecká Černokostelecká Praha 10 100 00 222111333
Toalpap Žižkova Jihlava 586 01 111222333
Toalpap – Vrátkovská Vrátkovská Praha 10 100 00 222333111
Toalpap – Letní Letní Jihlava 586 05 111333222

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.

Název prodejny Ulice PSČ Telefon
Toalpap – Černokostelecká Černokostelecká 100 00 222111333
Toalpap Žižkova 586 01 111222333
Toalpap – Vrátkovská Vrátkovská 100 00 222333111
Toalpap – Letní Letní 586 05 111333222

Město PSČ
Praha 10 100 00
Jihlava 586 01
Jihlava 586 05

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ě:

Název článku Autor Sekce
Normalizace relačních databází Jakub Kulhan Databáze
Zásobníkové jazyky Honza Jelimán Programování
Jak se upravit na flám sexy17ka Vzhled
Jak se upravit na flám Honza Jelimán Vzhled

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.

Název článku Autor
Normalizace relačních databází Jakub Kulhan
Zásobníkové jazyky Honza Jelimán
Jak se upravit na flám sexy17ka
Jak se upravit na flám Honza Jelimán

Název článku Sekce
Normalizace relačních databází Databáze
Zásobníkové jazyky Programování
Jak se upravit na flám Vzhled

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.

Prodejce Země Výrobek
Jan Jelimán Německo Extra-hrubý papír
Jan Jelimán Japonsko Extra-hrubý papír
Jan Jelimán Indie Příruční toaletní papír
Evženie Novotná Indie Příruční toaletní papír
Evženie Novotná Rusko Mrazuvzdorný papír
Lukáš Drbal Japonsko Mluvicí papír
Evženie Novotná Japonsko Příruční toaletní papír

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.

×Odeslání článku na tvůj Kindle

Zadej svůj Kindle e-mail a my ti pošleme článek na tvůj Kindle.
Musíš mít povolený příjem obsahu do svého Kindle z naší e-mailové adresy kindle@programujte.com.

E-mailová adresa (např. novak@kindle.com):

TIP: Pokud chceš dostávat naše články každé ráno do svého Kindle, koukni do sekce Články do Kindle.

Hlasování bylo ukončeno    
0 hlasů
Google
(fotka) Jakub KulhanAutor momentálně studuje na osmiletém gymnáziu v Kralupech nad Vltavou. Programování se věnuje od 11 let, kdy ho poprvé uchvátila možnost "mít vlastní stránky". Nakrátko poté objevil PHP a už se to s ním "vezlo". Webové aplikace zůstaly jeho hlavní doménou, ale ve svém volném čase probádává nejrůznější zákoutí světa programování, programovacích jazyků a všeho kolem nich.
Web    

Nové články

Obrázek ke článku Hackerský kongres přiveze v září do Prahy špičky světové kryptoanarchie

Hackerský kongres přiveze v září do Prahy špičky světové kryptoanarchie

Hackerský kongres HCPP16 pořádá od 30. září do 2. října nezisková organizace Paralelní Polis již potřetí, a to ve stejnojmenném bitcoinovém prostoru v pražských Holešovicích. Letos přiveze na třídenní konferenci přes 40 většinou zahraničních speakerů – lídrů z oblastí technologií, decentralizované ekonomiky, politických umění a aktivismu. Náměty jejich přednášek budou také hacking, kryptoměny, věda, svoboda nebo kryptoanarchie.

Reklama
Reklama
Obrázek ke článku ICT PRO školení zaměřené nejenom na ICT

ICT PRO školení zaměřené nejenom na ICT

Dovolte, abychom se představili. Jsme zaměstnanci společnosti ICT Pro, profesionálové v oblasti poskytování komplexních ICT služeb. Neboli služeb spojených s informačními a komunikačními technologiemi, které dnes - ve 21. století - tvoří  nedílnou součást běžného provozu všech moderních firem.

loadingtransparent (function() { var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true; po.src = 'https://apis.google.com/js/plusone.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })();
Hostujeme u Českého hostingu       ISSN 1801-1586       ⇡ Nahoru Webtea.cz logo © 20032016 Programujte.com
Zasadilo a pěstuje Webtea.cz, šéfredaktor Lukáš Churý