4.
Základy relačních databází, logická úroveň návrhu Když před desítkami let doktor E. F. Codd zavedl pojem relační databáze, pohlíželo se na tabulky jako na relace, se kterými se daly provádět různé operace. Z matematického hlediska to byl kartézský součin, selekce a projekce. Každá relační databáze vychází z těchto základů a musí mít v sobě zabudovánu podporu pro relační algebru, nad kterou se v jazyce SQL konstruují dotazy. Relační db. se používají v různých firemních aplikací např. na správu objednávek, databázi zaměstnanců atd. Ještě hojnější využijí relačních db. najdeme u Internetových aplikací, ve spolupráci se skriptovacími jazyky Php, Asp apod. Relační databáze je složená z řady tabulek, jejichž sloupce mohou být vázány na sloupce v ostatních souvisejících tabulkách, takto propojená datová pole jsou na sebe určitým způsobem závislá, mají mezi sebou nějaký logický vztah. Základní pojmy: entita – cokoliv, o čem potřebujeme v systému uchovávat informace atributy – určité sledované skutečnosti týkající se dané entity domény – popisují typ dat, obor hodnot = spojení datového typu a validačního pravidla vztahy – jisté asociace mezi entitami, nemusí mezi určitými entitami existovat Účastník – entita zapojená v určitém vztahu Stupeň vztahu – počet účastníků (binární, ternární, …) (výrobce – prodává – zákazníkovi – výrobky) Vazby mezi tabulkami (kardinalita): 1) Tabulky nejsou v relacích – jsou v nich nesouvisející údaje. 2) Mezi tabulkami je vztah 1:1 – jednomu záznamu v jedné tabulce, odpovídá přesně 1 záznam v tabulce druhé. 3) Mezi tabulkami je relace 1:N – jednomu záznamu v jedné tabulce, odpovídá více záznamů v druhé tabulce. Je to nejpoužívanější typ relace.1 zákazník, může mít N objednávek. 1 typ zboží, může být obsažen v N objednávkách. 4) Mezi tabulkami je relace M:N – více záznamům v jedné tabulce odpovídá více záznamů v tabulce druhé. V běžných databázových systémech nelze tuto relaci vytvořit přímo, proto se mezi takové tabulky vkládá ještě navíc jedna pomocná tabulka, která umožní vytvořit dvě relace 1:N a tím zajistit, že první dvě tabulky budou v požadované relaci M:N. Parcialita/totalita vztahu – povinnost/nepovinnost existence role příslušné entity vztahu vztah jednostranně parciální – zaměstnanec musí náležet k 1 pojišťovně, ale pojišťovna nemusí mít v evidenci žádného zaměstnance vztah oboustranně parciální – zaměstnanec nenáleží k žádné pojišťovně, pojišťovna nemusí mít žádného zaměstnance v evidenci Diagramy entit a vztahů Pro návrh a zápis vztahů mezi jednotlivými entitami naší databáze byl vytvořen model E-R diagramů. Tento model byl zaveden a poprvé použit panem Peterem Pin Shan Chenem v roce 1976. Mluvil o diagramech entit a vztahů (Entity Relationship Diagrams), který se brzy rozšířil a stal se obecně uznávaným standardem. Entity se v těchto diagramech popisují
pomocí obdélníků, vztahy se znázorňují pomocí kosočtverců, popř. atributy se zobrazují pomocí elips (oválů) – potom ovšem mluvíme o ERA diagramu (Entity Relationship Atribut). E-R diagram je založen na jednoduchém pravidle spočívajícím ve splnění čtyř podmínek: 1. Fakta vyjádříme pomocí tabulek v 5 NF 2. Entity (vyjádřeny tabulkami) pojmenujeme výstižným podstatným jménem v jednotném čísle 3. Mezi entitami vytvoříme relace typu 1:N a výstižně je pojmenujeme slovesem nebo předložkou 4. Čteme-li slova označující první entitu, relaci od ní a druhou entitu ve směru od N k 1, pak musí takto sestavená věta dávat smysl. Splněním těchto pravidel získáme diagramem velice dokonalou analýzu problému. Příkladem může být zobrazený diagram mezi třemi entitami Clovek, Bydliste a Posta.
E-R diagram Entita Clovek je dána tabulkou jednotlivých lidí v 5. NF s přímým přístupem podle unikátního rodného čísla. Entita Bydliste je opět zavedena tabulkou v 5. NF a představuje zde trvalé bydliště. Entita Posta představuje tabulku pošt v 5. NF s unikátním poštovním směrovacím číslem. Přitom v jednom trvalém bydlišti může bydlet několik lidí, ale jeden člověk má právě jednou trvalé bydliště. Proto je relace mezi entitou člověk a bydliště typu N:1 a velmi výstižně se dá charakterizovat slovesem MA. Relaci nelze číst v obráceném směru. Věta "Bydliště má člověka" neodpovídá realitě, protože jedno trvalé bydliště může být obsazeno více lidmi. Obdobně je to se vztahem entit BYDLISTE a POSTA. Bydliště MA příslušnou poštu, na kterou je třeba odesílat dopisy a nikoli obráceně. Na obrázku je vidět, že analýza byla provedena správně, protože existují jednoznačné názvy entit a relací, které mají správný směr a dávají z hlediska českého jazyka smysl. Navíc je zřejmá hierarchická struktura pošta – bydliště – lidé. ER diagramy mohou nabývat mnohem komplikovanějších stavů než v předchozím jednoduchém příkladě. Diagram může mít tvar hvězdicového uspořádání, mohou být zavedeny vícenásobné relace, rekurentní relace (entity se mohou odkazovat samy na sebe), rekurentní vícenásobné relace atd. Normalizace Normalizace ER modelu je sada pravidel, jak byste měli postupovat při transformaci struktury entit a relací ER modelu na strukturu fyzického uspořádání tabulek a relací v databázi. Proč normalizovat? Normalizace je odstranění redundantních(opakujících) se dat, omezení složitosti (rozloženít složité relace na dvojrozměrné tabulky) a zabránění tzv. aktualizačním anomáliím (např. abychom smazáním všech knih autora nepřišli o data o autorovi). Což by mělo vést k databázi přehlednější, rozšiřitelnější a výkonnější. Normalizace by měla vést k vzniku tabulek, které lze snadno udržovat a efektivně se na ně dotazovat. Normalizované schéma musí zachovat všechny závislosti původního schémat a relace musí zachovat původní data, což znamená, že se musíme pomocí přirozeného spojení dostat k původním datům.
Normální formy: 1.NF – První normální forma 2.NF – Druhá normální forma 3.NF – Třetí normální forma 4.NF – Čtvrtá normální forma 5.NF – Pátá normální forma
1. normální forma (1.NF) Relace je v první normální formě, pokud každý její atribut obsahuje jen atomické hodnoty. Tedy hodnoty z pohledu databáze již dále nedělitelné. Například v relaci obsahující data o nějáké osobě budeme chtít mít více telefonních čísel: Jméno Jan
Přijmení Adresa Novák Havlíčkova 2 Praha 3
Telefony 125789654;601258987;789456123
Petr
Kovář
Svatoplukova 15 Brno
369852147;357951456;963852741
Pavel
Pavel
Papalášova 25 Kocourkov
546789123;123456789;987456123
ID_osoby 1 1 1 Aby tabulka byla v 1NF musíme buďto rozdělit atribut telefon do více 2 atributů (pouze za předpokladu, že jsme si jisti, že se množství telefonních 2 čísel nezvýší), nebo oddělit telefoní čísla do samostatné tabulky, což já 2 3 osobně preferuji, protože je to podstatně flexibilnější řešení: 3 3 ID Jméno Příjmení Adresa 1 Jan Novák Havlíčkova 2 Praha 3 2 Petr Kovář Svatoplukova 15 Brno 3 Pavel Pavel Papalášova 25 Kocourkov S takovouto tabulkou by byla spousta problémů, například by se dost špatně prováděli změny čísel, případně vyhledávání podle telefonního čísla.
Cislo 125789654 601258987 789456123 369852147 357951456 963852741 546789123 123456789 987456123
2.normální forma (2.NF) Relace se nachází v druhé normální formě, je v první normální formě a každý neklíčový atribut je plně závislý na primárním klíči, a to na celém klíči a nejen na nějaké jeho podmnožině. Zní to poněkud složitě, ale nic na tom není, opět pomůže příklad: V tabulce zboží v obchodě bude název zboží, výrobce, telefon na výrobce, cena zboží a množství na skladě.
Název Mléčná čokoláda Oříšková čokoláda
Výrobce Milka Milka
Telefon Výrobce +420123456789 +420123456789
Cena 30Kč 30Kč
Množství 2500 2800
Tyčinka milkyway
Milka
+420123456789
10Kč
7000
Mléčná čokoláda
Orion
+420987654321
25Kč
5800
Oříšková horalka
Horalka
+420897654321
7Kč
4560
Klíčem této relace je kombinace atributů Název a Výrobce. Telefon výrobce ovšem není závislí na celém klíči, ale pouze na atributu výrobce. To by vedlo k aktualizační anomálii a to k té, že pokud by se vymazali veškeré výrobky od výrobce Milka, ztratilo by se telefoní číslo na výrobce Milka, což není zrovna žádané. Řešením je opět rozpad na dvě tabulky: Název Mléčná čokoláda Oříšková čokoláda Tyčinka milkyway Mléčná čokoláda Oříšková horalka
Výrobce_ID 1 1 1 2 3
Cena 30Kč 30Kč 10Kč 25Kč 7Kč
Množství 2500 2800 7000 5800 4560
Vyrobce_ID 1 2 3
Vyrobce Milka Orion Horalka
Telefon +420123456789 +420987654321 +420897654321
3.normální forma (3.NF) V této formě se nachází tabulka, splňuje-li předchází dvě formy a žádný z jejich atributů není tranzitivně závislí na klíči. Jiné vyjádření téhož říká, že relace je v 3.NF, pokud je ve 2.NF a všechny neklíčové atributy jsou navzájem nezávislé. Opět definice, která zní nesrozumitelně, ale její použití je vlastně jednoduché. Tranzitivní závoslot je taková závislost, mexi 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. Koukám, že jsem tomo opět moc nepomohl, takže nejlepší bude příklad: ekněme, že firma chce uchovávat informace o zaměstnancích, takže vytvříme relaci Zaměstnanec s atriubuty r.č. (primarní klíč), Jméno, Příjmení, Město, PSČ, Funkce a Plat, zbytek adresy vynecháme, protože pro příklad není důležitý. Zaměstnanec r.č Jméno Příjmení 1 Jack Smith 2 Franta Vomáčka 3 Pepa František 4 Pavel Novák 5 Petr Koukal 6 Honza Novák
Město PSČ Funkce Jihlava 58601 CEO Praha10 10000 Senior Software Architect Plzeň 10000 Senior Software Architect Kocourkov 99999 Junior Developer Praha10 12345 Database Designer Plzeň 12345 Junior Developer
Plat 150000 80000 80000 30000 75000 30000
Z této tabulky je vidět kromě závislosti všech atributů na klíči ještě závislost PSČ a Města a závislost Platu na Funkci. Aby jsme si to ukázali pomocí obou vyjádření definic. Závislost r.č -> Město -> PSČ je tranzitivní závislost PSČ na klíči, stejně tak závislost r.č. -> Funkce ->Plat . Pochopitelnější je asi druhé vyjáření, podle něj jsou závislosti Město -> PSČ a Funkce ->Plat přesně ty, které porušují sousloví: „všechny neklíčové atributy jsou navzájem nezávislé“. Řešením problému je opět rozpad na více relací, v tomto případě dokonce na 3, protože jsme 3.NF porušily rovnou dvakrát.
Funkce Funkce_ID 1 2 3 4
Funkce CEO Senior Software Architect Database Designer Junior Developer
Plat 150000 80000 75000 30000
Čtvrtá normální forma (4.NF) Tabulka je ve čtvrté normální formě, je-li v BCNF a popisuje pouze příčinnou souvislost (jeden fakt). Sice jednoduché vyjádření bez složitých definic, ale poněkud nicneříkající, takže zkusíme jinou definici: „Relace je ve čtvté normální formě, pokud je v Boyce/Coddově normální formě, a navíc všechny vícehodnotové závislosti jsou zároveň funkčními závislostmi z kandidátních klíčů (zjednodušeně: v jedné relaci se nesmí spojovat nezávislé opakované skupiny). “ Pátá normální forma (5.NF) Relace je v páté normální formě, pokud je ve čtvrté a není možné do ní přidat další atribut (skupinu atributů) tak, aby se vlivem skrytých závislostí rozpadla na několik dílčích relací. Závěr Normalizovat je určitě potřeba a čím složitější databáze a čím více dat, tím více je potřeba normalizovat. Ale i tady platí všeho s mírou. Například u příkladu u 3.NF by firma s několika desítkami zaměstnanců asi neměla potřebu dávat PSČ do další tabulky a bylo by to zbytečné.
Ale v tabulce zákazníků některého z mobilních operátorů s milióny zákazníků to už význam určitě má. U vyšších normálních forem jsem neuvedl příklady a to proto, že se špatně vymíšlí a hledají, ale hlavně si myslím, že pokud se člověk dostane k návrhu takové databáze, kde by je mohl porušit, tak je tak zkušený, že problém rozloží již ve fázi konceptuálního modelu a navrhne již normalizované relace.
