Databázové systémy

1. Databázové systémy Návrh struktury - normalizace

2. Definice a motivace Normalizace - proces návrhu databáze založený na teorii závislostí - dekompozice dat na jednotlivé tabulky a určení vztahů mezi nimi • problémy návrhu relačního schématu • redundance – ukládání stejné informace víckrát na různých místech (zvyšuje prostorové nároky) • mohou nastat aktualizační anomálie (insert/delete/update) • při vložení dat příslušejících jedné entitě je potřeba zároveň vložit data i o jiné entitě • při vymazání dat příslušejících jedné entitě je potřeba vymazat data patřící jiné entitě • pokud se změní jedna kopie redundantních dat, je třeba změnit i ostatní kopie, jinak se databáze stane nekonzistentní • řešení – normalizace DB schématu využitím funkčních závislostí Motivace k normalizaci:

3. Normalizace relačního schématu • jediný způsob – dekompozice na více schémat (tabulek) • případně nejdříve sloučení více „nenormálních“ schémat a pak dekompozice • přístupy podle různých kritérií • zachování integrity dat • tzv. bezztrátovost • tzv. pokrytí závislostí • požadavek na normální formy (3NF nebo BCNF) • ručně nebo algoritmicky

4. Příklad nenormovaného schématu 1) Funkce určuje hodinovou mzdu – přesto se mzda opakuje – redundance. 2) Smažeme-li záznam o zaměstnanci 6, ztratíme rovněž informaci o mzdě lektora. 3) Změníme-li mzdu funkce „účetní“, musíme tak učinit na třech místech.

5. Jak se to může stát? • ručním návrhem tabulky (nedoporučuji) • špatně/neoptimálně navržený ER diagram • např. zbytečně mnoho atributů v entitě vede na 2 tabulkyOsoba(RČ, jméno, příjmení, ..., sériové číslo)Mobil(sériové číslo, model, výrobce, ...)

6. Jak se to může stát? Redundance hodnot atributu Výrobce, Model, Made in, AtestKde se stala chyba?Entita Mobil skrývá další entity – Výrobce, Model, případně další... Jak to spravit? Dvě možnosti: 1) upravit přímo ER diagram (více entit) 2) upravit už hotová schémata relací (viz dále)

7. Funkční závislost Funkční závislostí rozumíme vztah mezi atributy jedné relace. Populací relace R budeme rozumět hodnoty atributů relace v daném čase. Nechť A a B jsou atributy relace R, budeme říkat, že atribut B funkčně závisí na atributu A, jestliže pro všechny populace relace R platí pro libovolnou n-tici u,v elementem R: u.A=v.A => u.B=v.B, označení A->B. Klient(r_cislo,jmeno,prijmeni,ulice,mesto) Hodnota rodneho cisla jednoznacne určuje hodnoty dalších atributů r_cislo→(jmeno,prijmeni,ulice,mesto) jmeno→(prijmeni,ulice,mesto) Plná funkční závislost Atribut je funkčně závislý na celém složeném atributu a ne jen na některé jeho části.

8. Tranzitivní závislost na klíči • Tranzitivní závislost je taková závislost, mezi minimálně dvěma atributy a klíčem, kde jeden atribut je funkčně závislý na klíči a druhý atribut je funkčně závislý na prvním atributu: závislost A → B taková, že A → klíč(tj. A není klíč ani nadklíč)tj. obdržíme tranzitivitu klíč → A → B • Samo o sobě poukazuje na hrozbu redundance, neboť z definice FZ jako zobrazení: • unikátní hodnoty klíče se zobrazí do stejně nebo méně unikátních hodnot A a ty se zobrazí do stejně nebo méně unikátních hodnot B Příklad v 2NF: PSČ → Město → Stát žádná redundancestřední redundancevysoká redundance

9. Normalizace - postupná dekompozice dat – transformace tabulky do vhodnějšího tvaru Potřebné vlastnosti dekompozice • zachování závislostí - původní závislosti musí být zachovány • odstranění opakování - redundance • bezztrátovost při zpětném spojení Bezztrátová dekompozice- spojení tabulek, které vzniknou dekompozicí musí dát přesně původní tabulku

10. Normální formy - definují požadavky na vlastnosti schématu tabulky z pohledu závislosti mezi atributy Hierarchie normálních forem • 1.NF až 3.NF • BCNF – (Boyce-Coddova) • 4.NF a 5.NF PLATÍ: n-tá normální forma musí splňovat podmínky n-1 normální formy a ještě něco navíc.

11. První normální forma (1NF) Každý atribut schématu relace je elementárního typu a je nestrukturovaný. Výskyt neatomických položek se obvykle řeší oddělením složených nebo opakujících se položek do nové relace. (1NF je základní podmínka „plochosti databáze“ – tabulka je opravdu dvourozměrné pole, ne např. skrytý strom nebo graf ) Tabulka je v první normální formě, právě když všechny její atributy jsou atomické, tj. dále již nedělitelné.

12. Příklad – 1NF (1) Kniha Správný návrh , který bude respektovat 1NF bude vypadat následovně: Autor Kniha Nakladatel

13. klíčový(é) atribut(y) neklíčový atribut klíč Druhá normální forma (2NF) Tabulka je ve druhé normální formě, právě když splňuje 1NF a všechny neklíčové položky jsou závislé na celém (složeném) primárním klíči. (Tzn. problém 2NF se týká jenom tabulek, kde volíme za primární klíč více položek než jednu.) • neexistují částečné závislosti neklíčových atributů na klíči

14. Příklad – 2NF (1) Firma, DB Server všechno Firma  Sídlo není ve 2NF, protože Sídlo závisí na části klíče (Firma) důsledek: redundance hodnot Sídla

15. Příklad – 2NF (2) Firma, DB Server všechno Firma  Sídlo obě schémata jsou ve 2NF

16. Třetí normální forma (3NF) • Žádný neklíčový atribut není tranzitivně závislý na žádném klíči • Přesněji, v daném schématu R(A, F) platí alespoň jedna z podmínek pro každou závislost X a (kde X  A, a A) • závislost je triviální • X je nadklíč • a je částí klíče Tabulka je ve třetí normální formě, jestliže je v 2NF a všechny položky jsou přímo závislé na primárním klíči. Neexistuje žádný neklíčový atribut, který je tranzitivně závislý na některém kandidátním klíči. K porušení tohoto požadavku dojde, jestliže v tabulce existují položky, které jsou závislé na položkách, které jsou dále závislé na primárním klíči (tranzitivní závislost).

17. Příklad – 3NF (1) Firma všechno PSČ  Sídlo je ve 2NF, není ve 3NF (tranzitivní závislost Sídla na klíči přes PSČ) důsledek: redundance hodnot Sídla

18. Příklad – 3NF (2) Firma všechno PSČ všechno obě schémata jsou ve 3NF

19. Boyce-Coddova normální forma (BCFN) Boyce/Coddova normální forma se pokládá za variaci třetí 3NF. Relace se nachází v BCNF, jestliže pro každou netriviální závislost X -> Y platí, že X je nadmnožinou nějakého klíče schématu R. K tomu, aby byla porušena BCNF musí být splněno několik podmínek a to poměrně specifických: • Relace musí mít více kandidátních klíčů • Minimálně 2 kandidátní klíče musí být složené z více atributů • Některé složené kandidátní klíče musí mít společný atribut. Boyce/Coddova normální forma v podstatě říká, že mezi kandidátními klíči nesmí být žádná funkční závislost.

20. Příklad – BCNF (1) Pilot, Den  všechno Letadlo, Den všechno Destinace Pilot je ve 3NF, není v BCNF (Pilot závisí na Destinaci, což není nadklíč) důsledek: redundance hodnot Pilot

21. Příklad – BCNF (2) Destinace PilotLetadlo, Den všechno nyní jsou obě schémata v BCNF

22. Další normální formy • 4NF – multizávislosti - 1 hodnotě atributu odpovídá množina hodnot jiného atributu Schéma relace R je ve 4.NF, jestliže pro každou netriviální multizávislost X->>platí, že X je nadmnožinou nějakého klíče schématu R. • 5NF Vychází z principu, že relaci můžeme dekomponovat na více tabulek a samozřejmě dekompozice musí být bezztrátová. Relace se nachází v 5.NF, pokud neobsahuje závislost podle spojení. Pokud tam závislost je, měli bychom ji rozložit.