PEDNKA 3 Normalizace databze Metodologie nvrhu databze Metodologie

PŘEDNÁŠKA 3 Normalizace databáze

Metodologie návrhu databáze Metodologie návrhu se skládá ze stádií, které vedou návrháře různými technikami návrhu. Stádia pomáhají návrháři plánování, správě, kontrole a zhodnocení vývojových projektů. Fáze návrhu databáze 1. Zjišťování faktů (rozhovory, dotazníky, atd. ) 2. Konceptuální návrh databáze 3. Logický návrh databáze 4. Fyzický návrh databáze

Konceptuální návrh databáze Proces vytvoření modelu dat používaných v organizaci bez jakýchkoli úvah o fyzické implementaci. Ve této fázi vytváříme model dat bez úvah o tom, jaký bude podkladový model dat (např. relační datový model) nebo jiných úvah o fyzické implementaci. Konceptuální model dat identifikuje důležité entity a relace, které je třeba reprezentovat v databázi. Používá se tzv. ER modelování. Konceptuální fáze návrhu databáze je zdrojem informací pro logickou fázi návrhu.

Základ ER modelování Entita Základním konceptem ER modelování je entita, která představuje množinu objektů „skutečného světa“, které mají shodné vlastnosti. Každá entita je zobrazena jako obdélník označený jménem entity. Relace je množina spojení mezi zúčastněnými entitami. Relace je zobrazena jako čára spojující související entity, označená jménem relace. Obvykle se relace označuje pomocí slovesa: Obor studia obsahuje Student

Logický návrh databáze Proces vytvoření modelu dat používaných organizací, který je už založen na specifickém modelu dat, ale nezávislý na konkrétním DBMS a jiných úvahách o fyzické implementaci. V případě námi probíraného relačního modelu tedy již reprezentujeme důležité entity a relace jako množinu relačních tabulek. Opět se používá technik ER modelování, tentokráte již s atributy. Logický návrh databáze je zdrojem informací pro fázi fyzického návrhu databáze. V této fázi se struktura každé tabulky zkontroluje pomocí NORMALIZACE.

Fyzický návrh databáze Rozhodujeme, jak fyzicky implementovat logický návrh v prostředí cílového DBMS. Rozdělen do následujících kroků: 1. Převedení logických tabulek do fyzických podkladových tabulek cílového DBMS. 2. Volba indexů. 3. Návrh uživatelských pohledů. 4. Bezpečnostní opatření pro ochranu dat. 5. Monitorování systému ve zkušebním provozu a ladění výkonu.

Normalizace databáze Hlavním cílem technik normalizace databáze, je vyhnout se vzniku datových anomálií a vytvoření sady tabulek s minimální redundancí! Existují 3 druhy datových anomálií: 1. 2. 3. Anomálie vkládání Anomálie odstraňování Anomálie modifikace

Anomálie vkládání Tato situace může nastat v případě, kdy jsou chybně v jedné tabulce směšovány atributy dvou různých entit. Uvažujme následující 2 entity. • Lektor • Kurz Tabulka by vypadala třeba následovně: kurz_id kurz_nazev l_jmeno l_prijmeni l_titul 10 Pletení Anna Šišková Bc. 12 Vaření Jára Cimrman Prof. 14 Astronomie Venuše Česká Dr. Anomálie spočívá v problému, že bychom mohli zaznamenat pouze toho lektora, který vede nějaký kurz.

Anomálie odstraňování Označujeme tak situaci, kdy odstranění dat o jedné konkrétní entitě způsobí nežádoucí ztrátu dat, která charakterizuje jinou entitu. Tak např. odstraněním jediného kurzu, který vede Jára Cimrman, bychom ztratili také všechny informace o Lektorovi. Tabulka: kurz_id kurz_nazev l_jmeno l_prijmeni l_titul narozeni 10 Pletení Anna Šišková Bc. 1969 12 Vaření Jára Cimrman Prof. 0000 14 Astronomie Venuše Česká Dr. 1955 15 Věštectví Venuše Česká Dr. 1955

Anomálie modifikace Anomálie při aktualizaci znamená, že při aktualizaci jediné datové hodnoty je potřeba aktualizovat současně několik řádků dat. Tak například na fakultě X by probíhalo celkem n kurzů a tomu by odpovídali příslušných n řádků v dané tabulce. V případě změny adresy fakulty by se tato hodnota musela změnit ve všech odpovídajících záznamech. Tabulka: kurz_id kurz_nazev fakulta adresa 10 Databáze PDF adresa 11 Tvorba WWW PDF adresa 12 Anatomie LF adresa 1 133 Religionistika CMF adresa 2

Normalizační formy Při normalizaci se postupuje různými fázemi: 1. 2. 3. 4. 5. 6. První normalizovanou formou. Druhou normalizovanou formou. Třetí normalizovanou formou. Boyce Coddovou normalizovanou formou. Čtvrtou normalizovanou formou. Pátou normalizovanou formou. Normalizace se zaměřuje na: • Eliminaci nadbytečnosti (redundance) dat. • Usnadnění změny dat a tedy zabránění vzniku anomálií při této činnosti. • Ulehčení zavádění omezení referenční integrity. • Vytváření snadno pochopitelné struktury, která se úzce podobá situaci reprezentované daty a umožňuje další růst.

První normální forma (1 NF) Tabulka se nachází v 1 NF pokud: • Má definován primární klíč. • Jsou eleminovány sloupce se stejným obsahem. • Každý průsečík sloupce a záznamu obsahuje jen jedinou hodnotu. Příklad. Uvažujme tabulku OSOBY: Jmeno Prijmeni Bydliste Telefony Petr Bříza Praha 125789654; 602589875 Roman Brtník Olomouc 369852147; 357951456 Alena Nová Brno 546789123; 123456789 Tabulka není v 1 NF, protože nemá PK a atribut telefony je vícehodnotový.

Špatné řešení Jmeno Prijmeni Bydliste Telefon_1 Telefon_2 Petr Bříza Praha 125789654 602589875 Roman Brtník Olomouc 369852147 357951456 Alena Nová Brno 546789123 123456789 Odstranili jsme sice vícehodnotové pole Telefony, ale rozdělením na Telefon_1 a Telefon_2 nerespektujeme požadavek eliminace sloupců se stejným obsahem! Tabulka tak stále není v 1. normální formě. Navíc stále není definován primární klíč!

Správné řešení Nová tabulka OSOBY: 1: N Osoba_id Jmeno Prijmeni Bydliste 1 Petr Bříza Praha 2 Roman Brtník Olomouc 3 Alena Nová Brno Nová tabulka TELEFONY: Osoba_id 1 1 2 2 3 3 telefon 125789654 601258987 369852147 357951456 546789123 123456789 Co je PK v tabulce TELEFONY?

Druhá normální forma (2 NF) Tabulka se nachází v 2 NF pokud: • Tabulka se nachází v 1 NF. • Nezahrnuje žádné částečné závislosti (kdy nějaký atribut závisí jen na části primárního klíče) resp. každý neklíčový atribut je plně závislý na primárním klíči. Prakticky tedy platí, že pokud je tabulka v 1 NF a nemá složený primární klíč, tak je automaticky také v 2 NF. Příklad. Uvažujme tabulku SKLAD: Složený PK Nazev Vyrobce Telefon Cena Mnozstvi Čokoláda Milka +420123456789 30 Kč 1200 Oplatky Orion +420987654321 20 Kč 1500 Bonbóny Milka +420123456789 18 Kč 1700 Čokoláda Orion +420987654321 25 Kč 2000

Druhá normální forma (2 NF) Složený PK Nazev Vyrobce Telefon Cena Mnozstvi Čokoláda Milka +420123456789 30 Kč 1200 Oplatky Orion +420987654321 20 Kč 1500 Bonbóny Milka +420123456789 18 Kč 1700 Čokoláda Orion +420987654321 25 Kč 2000 Tabulka je v 1 NF. Má PK, nemá sloupce se stejným obsahem a neobsahuje vícehodnotové atributy. Tabulka není v 2 NF. Atribut Telefon je závislý jen na části PK a to na atributu Vyrobce. Ostatní neklíčové atributy jsou v pořádku, protože závisejí vždy na celém PK. Kupříkladu atribut cena. Všimněte si, že nemůže být závislá jen na poli Nazev nebo Vyrobce. Protože jedna z těchto hodnot je vždy různá.

Správné řešení Nová tabulka ZBOZI: Nazev Vyrobce_ID Cena Mnozstvi Čokoláda 1 30 Kč 1200 Oplatky 2 20 Kč 1500 Bonbóny 1 18 Kč 1700 Čokoláda 2 25 Kč 2000 Nová tabulka VYROBCE: 1: N Vyrobce_ID Vyrobce Telefon 1 Milka +420123456789 2 Orion +420987654321

Třetí normální forma (3 NF) Tabulka se nachází v 3 NF pokud: • Tabulka se nachází v 2 NF. • Neobsahuje žádné tranzitivní neboli přechodné závislosti (tj. když nějaký neklíčový atribut závisí na primárním klíči prostřednictvím jiného neklíčového atributu). Jiné vyjádření téhož říká, že všechny neklíčové atributy jsou navzájem nezávislé, ostatní musíme odstranit (přesunout do samostatné tabulky). Příklad. Uvažujme tabulku ZAMESTNANCI: ID Jmeno Prijmeni Funkce Mesto PSC 1 Jan Malina Grafik Olomouc 772 00 2 Jiří Novák Programátor Prostějov 654 00 3 Andrea SIkorová CEO Olomouc 771 40 4 Petra Nováková Účetní Prostějov 654 00

Třetí normální forma (3 NF) ID Jmeno Prijmeni Funkce Mesto PSC 1 Jan Malina Grafik Olomouc 772 00 2 Jiří Novák Programátor Prostějov 654 00 3 Andrea Sikorová CEO Olomouc 772 00 4 Petra Nováková Účetní Prostějov 654 00 Tabulka je v 2 NF. Nemá totiž složený PK. PK je samotný sloupec ID. Tabulka není v 2 NF. Atribut PSC je v takto navržené tabulce přechodný (svázaný s neklíčovým atributem Mesto). Tabulku je potřeba upravit (rozdělit).

Správné řešení Nová tabulka ZAMESTNANCI: ID Jmeno Prijmeni Mesto_ID Funkce 1 Jan Malina 1 Grafik 2 Jiří Novák 3 Programátor 3 Andrea Sikorová 2 CEO 4 Petra Nováková 3 Účetní Nová tabulka MESTA: 1: N Mesto_ID Mesto PSC 1 Olomouc 772 00 2 Olomouc 771 40 3 Prostějov 654 00 Toto řešení je principiálně správné, nicméně pro uvedený příklad nemusí být v praxi nejvýhodnější. Záleží na více faktorech (velikost tabulky, relace s jinými entitami a dalšími. )

Samostatně splňte úkoly cvičení, které jsou dostupné v sekci CVIČENÍ v systému Courseware. Cvičení 3 – normalizace tabulek.

Další normální formy Níže uvedené normální formy se v praxi používají jen výjimečně. Nicméně základ jejich aplikace byste měli znát. V některých publikacích se dokonce objevují ještě další, ale ty probírat nebudeme. Měli byste znát: • Boyce-Coddova normální forma • Čtvrtá normální forma • Pátá normální forma

Boyce-Coddova normální forma Tabulka se nachází v BCNF pokud: • Nachází se v 3 NF. • Každý determinant je kandidátním klíčem. Vysvětlení: • Determinant - atribut, který určuje hodnotu jiného atributu. • Kandidátní klíč – jak už víme, kandidátní klíč je sloupec nebo skupina sloupců, které jednoznačně identifikují každý záznam tabulky.

Boyce-Coddova normální forma Uvažujme níže navrženou tabulku STUDIUM: Student Kurz Instruktor Jitka Nosková Freeware Josef Minarčík Markéta Hrubá Freeware Jiří Dostál Ondřej Dvořák Tvorba WWW Milan Klement Ondřej Dvořák Freeware Josef Minarčík V této tabulce si zvolíme za PK dvojici atributů Student a Kurz. Ta nám jasně určí Instruktora. Je v této tabulce determinant? Ano je, protože Instruktor jasně určuje Kurz. Podle BCNF tedy musí být Instruktor kandidátním klíčem. A to není, protože sám nedokáže identifikovat záznam. Tabulka tak musí být rozdělena.

Správné řešení Nová tabulka Student_Instruktor: Co bude primárním a cizím klíčem v této tabulce? Student Jitka Nosková Markéta Hrubá Ondřej Dvořák Instruktor Josef Minarčík Jiří Dostál Milan Klement Josef Minarčík Nová tabulka Instruktor_Kurz: Co bude PK v této tabulce? Instruktor Josef Minarčík Jiří Dostál Milan Klement Kurz Freeware Tvorba WWW

Čtvrtá normální forma (4 NF) Tabulka se nachází v 4 NF pokud: • Je v BCNF. • Neobsahuje více než jednu vícehodnotovou závislost. Vysvětlení: Vícehodnotová závislost existuje mezi dvěma atributy, když každé hodnotě prvního atributu odpovídá jedna nebo více přiřazených hodnot druhého atributu.

Čtvrtá normální forma (4 NF) Uvažujme tabulku STUDIUM: Student Schopnosti Jazyky Jitka Nosková Ovládání PC Angličtina Jitka Nosková Management Španělština Ondřej Dvořák Ovládání PC Angličtina Ondřej Dvořák Ovládání PC Francouzština Ondřej Dvořák Management Angličtina Co je v tabulce PK? Tabulka není v 4 NF protože obsahuje 2 vícehodnotové závislosti. 1. Student má více schopností 2. Student ovládá více jazyků Tabulka obsahuje nadbytečná data. Např. skutečnost že O. Dvořák ovládá PC je uloženo vícekrát.

Správné řešení Nová tabulka Student_Schopnosti: Student Jitka Nosková Ondřej Dvořák Schopnosti Ovládání PC Management Nová tabulka Student_Jazyky: Student Jitka Nosková Ondřej Dvořák Jazyky Angličtina Španělština Angličtina Francouzština

Zapamatujte si • Fáze návrhu databáze • Datové anomálie – vkládání, modifikace, odstraňování • Normalizace databáze • Aplikace normálních forem • 1 NF, 2 NF, 3 NF, BCNF, 4 NF, 5 NF (více ve skriptech) • Determinant • Nezapomeňte na cvičení v příloze distančních skript a cvičné příklady v sekci COURSEWARE.