Normalizace databáze - Database normalization

tento článek vyžaduje pozornost odborníka na databáze. Viz diskusní stránka pro detaily. Databáze WikiProject může pomoci při náboru odborníka. (Březen 2018)

Normalizace databáze je proces strukturování a relační databáze^{[je zapotřebí objasnění ]} v souladu s řadou tzv normální formy za účelem snížení redundance dat a zlepšovat se integrita dat. Poprvé to navrhl Edgar F. Codd jako součást jeho relační model.

Normalizace zahrnuje organizaci sloupce (atributy) a tabulky (vztahy) databáze, aby se zajistilo, že jejich závislosti jsou řádně vynuceny omezeními integrity databáze. Toho je dosaženo uplatněním některých formálních pravidel buď procesem syntéza (vytvoření nového návrhu databáze) nebo rozklad (vylepšení stávajícího návrhu databáze).

Cíle

Základním cílem první normální formy definované Coddem v roce 1970 bylo umožnit dotazování a manipulaci s daty pomocí „univerzálního datového jazyka“ založeného na logika prvního řádu.^[1] (SQL je příkladem takového datového subjazyka, i když ten, který Codd považoval za vážně chybný.^[2])

Cody normalizace nad 1NF (první normální forma) uvedl Codd takto:

1. Chcete-li osvobodit shromažďování vztahů od nežádoucích závislostí vložení, aktualizace a odstranění.
2. Snižovat potřebu restrukturalizace shromažďování vztahů se zaváděním nových typů dat a prodlužovat tak životnost aplikačních programů.
3. Aby byl relační model pro uživatele informativní.
4. Aby byla kolekce vztahů neutrální vůči statistikám dotazů, kde se tyto statistiky mohou časem měnit.

— E.F.Codd, „Další normalizace relačního modelu databáze“^[3]

An aktualizovat anomálie. Zaměstnanec 519 je zobrazen jako mající různé adresy v různých záznamech.

An anomálie vložení. Dokud nebude přidělen nový člen fakulty Dr. Newsome k výuce alespoň jednoho kurzu, nelze jeho podrobnosti zaznamenat.

A anomálie delece. Všechny informace o Dr. Giddensovi budou ztraceny, pokud dočasně přestane být zařazován do jakéhokoli kurzu.

Při pokusu o úpravu (aktualizaci, vložení do nebo odstranění z) relace mohou ve vztazích, které nebyly dostatečně normalizovány, vzniknout následující nežádoucí vedlejší účinky:

• Aktualizovat anomálii. Stejné informace lze vyjádřit na více řádcích; aktualizace relace proto mohou vést k logickým nesrovnalostem. Například každý záznam ve vztahu „Dovednosti zaměstnanců“ může obsahovat ID zaměstnance, adresu zaměstnance a dovednost; může tedy být nutné použít změnu adresy konkrétního zaměstnance na více záznamů (jeden pro každou dovednost). Pokud je aktualizace úspěšná jen částečně - adresa zaměstnance se aktualizuje u některých záznamů, u jiných nikoli - pak je relace ponechána v nekonzistentním stavu. Relace konkrétně poskytuje protichůdné odpovědi na otázku, jaká je adresa tohoto konkrétního zaměstnance. Tento jev se nazývá anomálie aktualizace.
• Anomálie vložení. Existují okolnosti, za kterých nelze určitá fakta vůbec zaznamenat. Například každý záznam ve vztahu „Fakulta a jejich kurzy“ může obsahovat ID fakulty, název fakulty, datum pronájmu fakulty a kód kurzu. Proto můžeme zaznamenat podrobnosti o každém členovi fakulty, který vyučuje alespoň jeden kurz, ale nemůžeme zaznamenat nově najatého člena fakulty, který dosud nebyl přidělen k výuce žádných kurzů, kromě nastavení kódu kurzu na null. Tento jev je znám jako anomálie inzerce.
• Anomálie smazání. Za určitých okolností vyžaduje vymazání údajů představujících určité skutečnosti vymazání údajů představujících úplně jiná fakta. Vztah „Fakulta a jejich kurzy“ popsaný v předchozím příkladu trpí tímto typem anomálie, protože pokud člen fakulty dočasně přestane být přiřazován k jakémukoli kurzu, musíme efektivně odstranit poslední ze záznamů, na kterých se tento člen fakulty objevuje. také odstranění člena fakulty, pokud nenastavíme null kód kurzu. Tento jev je znám jako anomálie delece.

Při rozšiřování struktury databáze minimalizujte redesign

Plně normalizovaná databáze umožňuje její strukturu rozšířit tak, aby vyhovovala novým typům dat, aniž by se příliš změnila stávající struktura. Výsledkem je, že aplikace interagující s databází jsou ovlivněny minimálně.

Normalizované vztahy a vztah mezi jedním normalizovaným vztahem a druhým odrážejí koncepty reálného světa a jejich vzájemné vztahy.

Příklad

Dotazování a manipulace s daty v rámci datové struktury, která není normalizovaná, jako je například následující reprezentace transakcí s kreditními kartami zákazníků mimo 1NF, vyžaduje větší složitost, než je skutečně nutné:

Každému zákazníkovi odpovídá „opakující se skupina“ transakcí. Automatizované vyhodnocení jakéhokoli dotazu týkajícího se transakcí zákazníků by tedy široce zahrnovalo dvě fáze:

1. Rozbalení jedné nebo více skupin transakcí zákazníků, které umožňují zkoumání jednotlivých transakcí ve skupině, a
2. Odvození výsledku dotazu na základě výsledků první fáze

Například za účelem zjištění peněžní částky všech transakcí, ke kterým došlo v říjnu 2003 u všech zákazníků, by systém musel vědět, že musí nejprve rozbalit Transakce skupina každého zákazníka, poté sečtěte Množství všech takto získaných transakcí, pokud datum transakce klesá v říjnu 2003.

Jedním z důležitých poznatků Codda bylo, že lze snížit strukturální složitost. Snížená strukturální složitost dává uživatelům, aplikacím a databázovým systémům větší sílu a flexibilitu při formulování a vyhodnocování dotazů. Normalizovanější ekvivalent výše uvedené struktury může vypadat takto:

V upravené struktuře je primární klíč je {Cust. ID} v prvním vztahu, {Cust. ID, Tr. ID} ve druhém vztahu.

Nyní každý řádek představuje samostatnou transakci kreditní kartou a DBMS může získat zajímavou odpověď jednoduše vyhledáním všech řádků s datem klesajícím v říjnu a sečtením jejich částek. Datová struktura staví všechny hodnoty na stejnou úroveň a každou vystavuje přímo DBMS, takže se každá může potenciálně účastnit přímo dotazů; zatímco v předchozí situaci byly některé hodnoty vloženy do struktur nižší úrovně, které musely být zpracovány speciálně. Normalizovaný design se tedy hodí ke zpracování dotazů pro všeobecné účely, zatímco normalizovaný design nikoli. Normalizovaná verze také umožňuje uživateli změnit jméno zákazníka na jednom místě a chrání před chybami, které vzniknou, pokud je jméno zákazníka chybně napsáno v některých záznamech.

Normální formy

Codd představil koncept normalizace a to, co je nyní známé jako první normální forma (1NF) v roce 1970.^[4] Codd dále definoval druhá normální forma (2NF) a třetí normální forma (3NF) v roce 1971,^[5] a Codd a Raymond F. Boyce definoval Boyce-Codd normální forma (BCNF) v roce 1974.^[6]

Neformálně je relační relace databáze často popisována jako „normalizovaná“, pokud splňuje třetí normální formu.^[7] Většina vztahů 3NF neobsahuje žádné anomálie vložení, aktualizace a odstranění.

Normální formy (od nejméně normalizovaných po nejvíce normalizované) jsou:

	UNF (1970)	1NF (1970)	2NF (1971)	3NF (1971)	EKNF (1982)	BCNF (1974)	4NF (1977)	ETNF (2012)	5NF (1979)	DKNF (1981)	6NF (2003)
Primární klíč (žádný duplikát n-tice )
Žádné opakující se skupiny
Atomové sloupce (buňky mají jednu hodnotu)[8]
Každý netriviální funkční závislost buď nezačíná správnou podmnožinou a klíč kandidáta nebo končí na hlavní atribut (žádné částečné funkční závislosti jiných než primárních atributů na kandidátských klíčích)[8]
Každá netriviální funkční závislost začíná a superklíč nebo končí primárním atributem (č tranzitivní funkční závislosti non-prime atributů na kandidátských klíčích)[8]
Každá netriviální funkční závislost začíná superklíčem nebo končí znakem základní prvočíslo atribut[8]											N / A
Každá netriviální funkční závislost začíná superklíčem[8]											N / A
Každý netriviální vícehodnotová závislost začíná superklíčem[8]											N / A
Každý připojit závislost má superklíčovou komponentu[9]											N / A
Každá závislost na spojení má pouze komponenty superkey[8]											N / A
Každé omezení je důsledkem omezení domén a klíčových omezení[8]											N / A
Každá závislost na připojení je triviální[8]

Příklad normalizace krok za krokem

Normalizace je technika návrhu databáze, která se používá k návrhu a relační databáze tabulka až do vyšší normální formy.^[10] Proces je progresivní a vyšší úrovně normalizace databáze nelze dosáhnout, dokud nebudou splněny předchozí úrovně.^[11]

To znamená, že mít data v nenormalizovaná forma (nejméně normalizovaný) a s cílem dosáhnout nejvyšší úrovně normalizace by prvním krokem bylo zajistit dodržování první normální forma, druhým krokem by bylo zajistit druhá normální forma je spokojen, a tak dále v pořadí uvedeném výše, dokud data neodpovídají šestá normální forma.

Stojí však za zmínku, že normální formy jdou dál 4NF jsou hlavně akademického zájmu, protože problémy, které existují k řešení, se v praxi objevují jen zřídka.^[12]

Upozorňujeme, že data v následujícím příkladu byla záměrně navržena tak, aby byla v rozporu s většinou běžných forem. V reálném životě je docela možné být schopen přeskočit některé normalizační kroky, protože tabulka neobsahuje nic, co by odporovalo dané normální formě. Také se běžně stává, že oprava narušení jedné normální formy také opraví narušení vyšší normální formy v procesu. Pro každý krok byla pro normalizaci vybrána také jedna tabulka, což znamená, že na konci tohoto příkladu mohou stále existovat některé tabulky, které nesplňují nejvyšší normální formu.

Počáteční údaje

Nechte databázovou tabulku s následující strukturou:^[11]

V tomto příkladu předpokládáme, že každá kniha má pouze jednoho autora.

Uspokojující 1NF

K uspokojení 1NF musí být hodnoty v každém sloupci tabulky atomické. V úvodní tabulce Předmět obsahuje sadu hodnot předmětu, což znamená, že není v souladu.

Jedním ze způsobů, jak dosáhnout 1NF, by bylo oddělit duplicity do více sloupců pomocí opakujících se skupin Předmět:

Ačkoli nyní tabulka formálně vyhovuje 1NF (je atomová), problém s tímto řešením je zřejmý - pokud má kniha více než tři předměty, nelze ji přidat do databáze bez změny její struktury.

Abychom problém vyřešili elegantnějším způsobem, je nutné identifikovat entity zastoupené v tabulce a rozdělit je do vlastních příslušných tabulek. V tomto případě by to mělo za následek Rezervovat, Předmět a Vydavatel tabulky:^[11]

Pouhé rozdělení počátečních dat do více tabulek by přerušilo spojení mezi daty. To znamená, že je třeba určit vztahy mezi nově zavedenými tabulkami. Všimněte si, že ID vydavatele sloupec v tabulce knihy je a cizí klíč realizovat více ku jedné vztah mezi knihou a vydavatelem.

Do knihy se vejde mnoho předmětů, stejně jako předmět může odpovídat mnoha knihám. To znamená také a mnoho k mnoha je třeba definovat vztah, kterého lze dosáhnout vytvořením a tabulka odkazů:^[11]

Místo jednoho stolu nenormalizovaná forma, nyní existují 4 tabulky vyhovující 1NF.

Uspokojující 2NF

The Rezervovat stůl má jednu klíč kandidáta (což je tedy primární klíč ), složený klíč {Název, formát}.^[13] Zvažte následující fragment tabulky:

Na všech atributech, které nejsou součástí kandidátského klíče, záleží Titul, ale pouze Cena záleží také na Formát. Tomu se přizpůsobit 2NF a odstranit duplicity, každý atribut nekandidátského klíče musí záviset na celém kandidátském klíči, nejen na jeho části.

Chcete-li normalizovat tuto tabulku, proveďte {Titul} (jednoduchý) klíč kandidáta (primární klíč), takže každý atribut jiného než kandidátského klíče závisí na celém kandidátském klíči, a remove Cena do samostatné tabulky tak, aby její závislost na Formát lze uchovat:

Nyní Rezervovat tabulka odpovídá 2NF.

Uspokojující 3NF

The Rezervovat tabulka má stále přechodnou funkční závislost ({Author Nationality} je závislá na {Author}, která je závislá na {Title}). Podobné narušení existuje pro žánr ({Genre Name} je závislý na {Genre ID}, který je závislý na {Title}). Proto je Rezervovat tabulka není v 3NF. Abychom to udělali v 3NF, použijme následující strukturu tabulky, čímž odstraníme přechodné funkční závislosti umístěním {Author Nationality} a {Genre Name} do jejich vlastních příslušných tabulek:

Uspokojující EKNF

Normální forma elementárního klíče (EKNF) spadá striktně mezi 3NF a BCNF a není v literatuře příliš diskutována. Je to zamýšleno „Zachytit hlavní vlastnosti 3NF a BCNF“ a zároveň se vyhnout problémům obou (jmenovitě to, že 3NF je „příliš odpouštějící“ a BCNF je „náchylný k výpočetní složitosti“). Protože je v literatuře zmiňován jen zřídka, není v tomto příkladu zahrnut.^[14]

Uspokojující 4NF

Předpokládejme, že databázi vlastní franšíza maloobchodů s knihami, která má několik franšízantů, kteří vlastní obchody na různých místech. A proto se prodejce rozhodl přidat tabulku, která obsahuje údaje o dostupnosti knih na různých místech:

Protože tato struktura tabulky se skládá z složený primární klíč, neobsahuje žádné neklíčové atributy a je již v BCNF (a proto také splňuje všechny předchozí normální formy ). Pokud však předpokládáme, že všechny dostupné knihy jsou nabízeny v každé oblasti, můžeme si všimnout, že Titul není jednoznačně vázán na určitý Umístění a proto tabulka nesplňuje 4NF.

To znamená, že k uspokojení čtvrtá normální forma, je třeba rozložit i tuto tabulku:

Nyní je každý záznam jednoznačně identifikován a superklíč, proto 4NF je spokojen.^[15]

Uspokojující ETNF

Předpokládejme, že franšízanti mohou také objednávat knihy od různých dodavatelů. Nechť vztah také podléhá následujícímu omezení:

• Pokud jistý dodavatel dodává určité titul
• a titul je dodáván do franšízant
• a franšízant je dodáván společností dodavatel,
• pak dodavatel dodává titul do franšízant.^[16]

Tato tabulka je v 4NF, ale ID dodavatele se rovná spojení jeho projekcí: {{Supplier ID, Book}, {Book, Franchisee ID}, {Franchisee ID, Supplier ID}}. Žádná součást této závislosti na spojení není a superklíč (jediným superklíč je celý nadpis), takže tabulka nesplňuje ETNF a lze jej dále rozložit:^[16]

Vzniká rozklad ETNF dodržování.

Uspokojující 5NF

Zjistit stůl, který nesplňuje 5NF, je obvykle nutné údaje důkladně prozkoumat. Předpokládejme tabulku z Příklad 4NF s malou úpravou dat a prozkoumejme, zda to vyhovuje 5NF:

Pokud tuto tabulku rozložíme, snížíme nadbytečnost a dostaneme následující dvě tabulky:

Co se stane, když se pokusíme připojit k těmto tabulkám? Dotaz vrátí následující data:

Zdá se, že JOIN vrací další tři řádky, než by měl - zkusme přidat další tabulku k objasnění vztahu. Nakonec skončíme se třemi samostatnými tabulkami:

Co se nyní vrátí JOIN? Ve skutečnosti není možné se k těmto třem stolům připojit. To znamená, že nebylo možné rozložit Franchisant - umístění knihy bez ztráty dat, proto tabulka již vyhovuje 5NF.^[15]

C.J. Date tvrdil, že pouze databáze v 5NF je skutečně „normalizovaná“.^[17]

Uspokojující DKNF

Pojďme se podívat na Rezervovat tabulka z předchozích příkladů a zjistěte, zda vyhovuje Normální forma klíče domény:

Logicky Tloušťka je dán počtem stránek. To znamená, že záleží na Stránky což není klíč. Uveďme příklad konvence, který říká, že kniha do 350 stran je považována za „štíhlou“ a kniha přes 350 stran je považována za „silnou“.

Tato konvence je technicky omezení, ale není ani omezením domény, ani omezením klíče; proto se nemůžeme spoléhat na omezení domény a klíčová omezení, abychom udrželi integritu dat.

Jinými slovy - nic nám nebrání uvést například výraz „Tlustý“ pro knihu, která má pouze 50 stránek - a tím se tabulka porušuje DKNF.

Abychom to vyřešili, můžeme vytvořit tabulku obsahující výčet, který definuje Tloušťka a odeberte tento sloupec z původní tabulky:

Tímto způsobem bylo odstraněno narušení integrity domény a tabulka je v DKNF.

Uspokojující 6NF

Jednoduchá a intuitivní definice šestá normální forma je to "je stůl 6NF když řádek obsahuje primární klíč a nejvýše jeden další atribut ".^[18]

To znamená například Vydavatel stůl navržen zatímco vytvoření 1NF

je třeba dále rozložit na dvě tabulky:

Zjevnou nevýhodou 6NF je množení tabulek potřebných k reprezentaci informací o jedné entitě. Pokud má tabulka v 5NF jeden sloupec primárního klíče a N atributů, bude představování stejných informací v 6NF vyžadovat N tabulek; aktualizace více polí k jednomu koncepčnímu záznamu budou vyžadovat aktualizace více tabulek; a vkládání a mazání bude podobně vyžadovat operace napříč více tabulkami. Z tohoto důvodu v databázích, které mají sloužit Zpracování online transakcí potřeby, 6NF by neměl být používán.

Nicméně, v datové sklady, které neumožňují interaktivní aktualizace a které se specializují na rychlý dotaz na velké objemy dat, používají některé DBMS interní reprezentaci 6NF - známou jako Sloupcový datový sklad. V situacích, kdy je počet jedinečných hodnot sloupce mnohem menší než počet řádků v tabulce, umožňují úložiště orientované na sloupce významné úspory prostoru díky kompresi dat. Sloupcové úložiště také umožňuje rychlé provádění dotazů na rozsah (např. Zobrazit všechny záznamy, kde je konkrétní sloupec mezi X a Y nebo menší než X).

Ve všech těchto případech však návrhář databáze nemusí provádět normalizaci 6NF ručně vytvořením samostatných tabulek. Některé DBMS, které se specializují na skladování, jako např Sybase IQ, ve výchozím nastavení použít sloupcové úložiště, ale návrhář stále vidí pouze jednu vícesloupcovou tabulku. Jiné systémy DBMS, například Microsoft SQL Server 2012 a novější, umožňují určit „index úložiště sloupců“ pro konkrétní tabulku.^[19]

Viz také

• Denormalizace
• Refaktorování databáze

• Bezztrátový rozklad rozkladu

Poznámky a odkazy

Další čtení

• Date, C. J. (1999), Úvod do databázových systémů (8. vydání). Addison-Wesley Longman. ISBN  0-321-19784-4.
• Kent, W. (1983) Jednoduchý průvodce pěti normálními formami v teorii relační databáze „Komunikace ACM, sv. 26, s. 120–125
• H.-J. Schek, P. Pistor datové struktury pro integrovanou správu databází a systém získávání informací

externí odkazy

• Kent, William (únor 1983). „Jednoduchý průvodce pěti normálními formami v teorii relační databáze“. Komunikace ACM. 26 (2): 120–125. doi:10.1145/358024.358054. S2CID  9195704.
• Základy normalizace databáze Mike Chapple (About.com)
• Úvod do normalizace databáze, Část 2
• Úvod do normalizace databáze Mike Hillyer.
• Výukový program pro první 3 normální formuláře Fred Coulson
• Popis základů normalizace databáze od společnosti Microsoft
• Normalizace v DBMS od Chaitanyi (beginnersbook.com)
• Podrobný průvodce normalizací databáze
• ETNF - základní n-tice normální formy