OBCHODNÍ AKADEMIE ORLOVÁ

OBCHODNÍ AKADEMIE ORLOVÁ STŘEDNÍ ODBORNÁ ŠKOLA BLATNÁ

RELAČNÍ DATABÁZE − JAZYK SQL UČEBNÍ TEXT PRO DISTANČNÍ FORMU VZDĚLÁVÁNÍ

MGR. PAVEL KOZLOVSKÝ

ORLOVÁ 2006

Relační databáze − jazyk SQL - Studijní opora

1

Cíle předmětu Po prostudování textu budete znát: Naučíte se používat nejčastěji používaný jazyk pro práci s databázemi.

Získáte: Dovednosti pro tvorbu serverové části databázových aplikací.

Budete schopni: Vytvářet nové tabulky. Ukládat, měnit a mazat data. Získávat informace z databází pomocí SQL dotazů. Navrhovat strukturu databází.

Čas potřebný k prostudování učiva předmětu: +, hodin


2

Obsah Předmluva Kapitola /. Opakování pojmů z matematiky Kapitola 1. Relační databázový systém Kapitola 3. Množinové operace v SQL Kapitola 5. Normální formy Kapitola ,. Gramatická pravidla Kapitola 7. Tabulky Kapitola +. Vkládání dat Kapitola 9. Mazání dat Kapitola :. Úprava dat Kapitola /<. Dotazy Kapitola //. Souvislý příklad Kapitola /1. Samostatná práce Závěr


3

Předmluva Studijní opora Relační databáze − jazyk SQL vznikla v létě roku 1<<7 na základě zkušeností z výuky databázových systémů na Střední odborné škole v Blatné. Přestože text je primárně určen pro distanční formu vzdělávání, je možné jej využít i jako učebnici pro denní studium nebo dokonce samostudium. Cílem tohoto textu je seznámit čtenáře se základy relačních databázových systémů prostřednictvím jednoduchých příkladů v jazyce SQL.

Co je třeba znát Studijní opora Relační databáze a jazyk SQL navazuje na studijní oporu Úvod do databázových systémů. Předpokládají se tedy znalosti databázových systémů na uživatelské úrovni. Studium této opory lze tedy doporučit až po prostudování předchozí opory. Dále se předpokládají znalosti základů matematiky, zejména pak kapitoly výroková a predikátová logika a základy teorie množin včetně teorie relací, která je základem teorie relačních databází. Protože serverové databázové nástroje jsou v drtivé většině v angličtině, je znalost angličtiny alespoň na úrovni čtení odborného textu nutná a předpokládá se. Odborná slovní zásoba je v textu vyznačena včetně výslovnosti a na konci publikace je zařazen oboustranný slovníček.

Kapitoly Opakování pojmů z matematiky je náplní první kapitoly. Protože se znalost těchto pojmů předpokládá ze studia matematiky, je tato kapitola stručným souhrnem pojmů používaných v této opoře. Důraz je kladen na definice pojmů, způsob zápisu a používané symboly, neboť v symbolech a definicích často bývají drobné rozdíly mezi různými publikacemi. Druhá kapitola obsahuje teorii relačních databázových systémů. Jsou zde uvedena Coddova pravidla se stručným vysvětlením. Třetí kapitola ilustruje těsnou vazbu mezi relačními databázovými systémy a matematickou teorií množin. Úlohy, jejichž „ruční“ řešení je známo z matematiky, jsou řešeny pomocí databázového serveru a jazyka SQL. Tuto kapitolu je vhodné studovat dvakrát. Poprvé jako motivaci k čemu jsou databázové systémy dobré a podruhé až po prostudování pravidel jazyka SQL v dalších kapitolách. Zásady tvorby databází v podobě normálních forem jsou tématem čtvrté kapitoly. Stěžejním pojmem je funkční závislost čili funkce. K pochopení je nutná znalost realizace funkce v databázovém systému z předchozí kapitoly. Studium této kapitoly je možné odložit až na samý závěr studia této opory. Před samotný výklad jazyka SQL je zařazena krátká kapitola o způsobu zápisu gramatických pravidel, který používáme v dalším textu. Vlastní výklad jazyka SQL začíná v šesté kapitole tvorbou databázových tabulek. Součástí kapitoly je přehled datových typů jazyka SQL. Velká pozornost je věnována omezením sloupců a tabulek, které zajišťují integritu databázového systému. Zápis dat, čili jak vkládat, mazat a upravovat data v databázovém systému je obsahem dalších tří kapitol. Čtení dat pomocí SQL dotazů je náplní desáté kapitoly. Součástí kapitoly je i uložení dotazu Relační databáze − jazyk SQL - Studijní opora

4

ve formě pohledu a velké množství řešených příkladů. Předposlední kapitola obsahuje souvislý příklad a jeho řešení. Závěrečná kapitola obsahuje zadání samostatné práce, pochopitelně bez řešení.

Poděkování Rád bych na tomto místě poděkoval ing. Šromkové, RNDr. Štěrbové a dalším kolegům z Obchodní akademie v Orlové za ohromný kus práce, který odvedli v rámci projektu jehož součástí je i tvorba této studijní opory. Velký dík patří i studentům Střední odborné školy v Blatné za jejich trpělivost při hodinách, na kterých často zakusili na vlastní kůži části (a ne vždy zcela připravené) této studijní opory. Svými dotazy a nápady však přispěli velkou měrou k tomuto dílu, proto jim ještě jednou děkuji. Poděkování patří i vedení Střední odborné školy v Blatné v čele s RNDr. Pavlíkem za vstřícný přístup, kdykoliv ho bylo zapotřebí. V neposlední řadě děkuji své ženě a dětem za podporu po celou dobu tvorby opory. Bez jejich ochoty snášet moje ne vždy příjemné nálady během dlouhého vysedávání u počítače by tato publikace nikdy nevznikla. Pavel Kozlovský


5

Kapitola D. Opakování pojmů z matematiky V této kapitole se dozvíte: Zopakujete si výrokovou a predikátovou logiku. Zopakujete si základy teorie množin.

Klíčová slova této kapitoly: Výrok Predikát Logická spojka Kvantifikátor Množina Sjednocení Průnik Uspořádaná dvojice Kartézský součin Relace Funkce

Čas potřebný k prostudování učiva kapitoly: , hodin

Průvodce studiem. Trochu matematiky nikdy neuškodí. Dejte si však dobrý pozor na zákeřné paznačky, aby na Vás nedělali: „B∪B∪B∪“ a „H∧H∧H∧“. To si přece vůbec nezasloužíte. A kdyby bylo opravdu nejhůř, vemte si na pomoc studijní oporu Matematika - − jak ta s nimi uměla zatočit. Však si to jistě ještě dobře pamatujete.

V této kapitole si zopakujeme základní matematické pojmy. Věnujte pozornost použitým symbolům, protože se může mírně lišit od značení, které znáte z matematiky. Zároveň si upřesníme některé definice. Protože se jedná o opakování, není v této kapitole žádný výklad. V případě potřeby nahlédněte prosím do studijní opory matematiky nebo do učebnic matematiky pro střední školy.


6

Výrok Výrok je tvrzení (věta), o kterém má smysl říci, je-li pravdivé nebo nepravdivé.

Pravdivostní hodnota p(…) Pravdivostní hodnota nepravdivého výroku je <. Pravdivostní hodnota pravdivého výroku je /. Je-li výrok A pravdivý, píšeme: p(A)=/. Je-li výrok B nepravdivý, píšeme p(B)=<.

Negace ¬… Pravdivostní hodnoty negovaného výroku ¬A jsou uvedeny v tabulce. p(A) < /

p(¬A) / <

Logické spojky …∧ ∧…, …∨ ∨… a …⇒ ⇒… Pravdivostní hodnoty jsou uvedeny v tabulce. p(A)

p(B)

< < / /

< / < /

p(A∧ ∧B) < < < /

p(A∨ ∨B) < / / /

p(A⇒ ⇒B) / / < /

Predikát φ(…) Predikát neboli výroková funkce je výraz obsahující proměnné. Dosadíme-li za tyto proměnné konkrétní hodnoty, dostaneme výrok.

Obecný kvantifikátor ∀…: φ(…) Pro všechna x platí φ(x).

Existenční kvantifikátor ∃…: φ(…) Existuje alespoň jedno x, pro které platí φ(x).

Kvantifikátor jedinečnosti ∃?…: φ(…) ∃?x:φ(x)≝∀x:∀y:φ(x)∧φ(y)⇒x=y Existuje nejvýše jedno x, pro které platí φ(x).

Kvantifikátor ∃! Relační databáze − jazyk SQL - Studijní opora

7

∃!x:φ(x)≝∃x:φ(x)∧∃?x:φ(x) Existuje právě jedno x, pro které platí φ(x).

Množina {…, …, … …} Soubor prkvků. Zápis {<, /, 1, 3} čteme: „množina prvků <, /, 1 a 3“.

Prvek …∈ ∈… Znakem ∈ označujeme vztah „je prvkem (elementem)“.

Rovnost …=… A=B≝∀x:x∈A⇔x∈B

Sjednocení …∪ ∪… A∪B≝{x|x∈A∨x∈B}

Průnik …∩… A∩B≝{x|x∈A∧x∈B}

Rozdíl … − … A−B≝{x|x∈A∧x∉B}

Podmnožina …⊂ ⊂… A⊂B≝∀x:x∈A⇒x∈B

Uspořádaná dvojice […, …] [a,b]=[x,y]⇔a=x∧b=y

Kartézský součin …×… A×B≝{[x,y]|x∈A∧y∈B}

Relace R: …→… R je relace≝∃A:∃B:R⊂A×B

Být v relaci …R… xRy≝[x,y]∈R


8

Inverzní relace R−D R−D≝{[x,y]|yRx}

Definiční obor D(…) D(R)≝{x|∃y:xRy}

Obor hodnot H(…) H(R)≝D(R−/)

Složená relace …○… R○S≝{[x,z]|∃y:xRy∧ySz}

Funkce F: …→… F je funkce≝F je relace ∧ ∀x∃?y:xFy Funkce je relace, pro kterou platí, že k libovolnému x existuje nejvýše jedno y, které je s ním v relaci.

Funkce F:A→B Čteme: „F je funkce A do B“. F:A→B≝F je funkce ∧ D(F)=A ∧ H(F)⊂B

Hodnota funkce F(…) y=F(x)⇔xFy

Hodnota funkce x.F Místo F(x) může psát x.F: x.F≝F(x)

Prostá funkce F je prostá funkce≝F je funkce ∧ F−/ je funkce

Shrnutí kapitoly. Základním matematickým pojmem je množina, kterou chápeme jako soubor prvků. S množinami lze provádět operace. Základními operacemi jsou sjednocení, průnik a kartézský součin. Sjednocení množin je množina všech prvků, které jsou prvky alespoň Relační databáze − jazyk SQL - Studijní opora

9

jedné ze sjednocovaných množin. Průnikem souboru množin nazýváme množinu obsahující všechny prvky, které jsou prvky všech množin souboru. Kartézský součin dvou množin je množina všech uspořádaných dvojic, jejichž první prvek je prvkem první množiny a druhý druhé. Z hlediska relačních databází je nejdůležitějším pojmem relace definovaná jako libovolná podmnožina kartézského součinu. Zvláštním druhem relací jsou funkce, pro které platí, že každý prvek definičního oboru je v relaci nejvýše s jedním prvkem oboru hodnot. O tomto prvku můžeme říci, že je přiřazen.


10

Kapitola \. Relační databázový systém V této kapitole se dozvíte: Základní pravidla relačních databázových systémů.

Klíčová slova této kapitoly: Tabulka Relace SQL

Čas potřebný k prostudování učiva kapitoly: 3 hodiny

Průvodce studiem. Lidé sláva! Narodila se relační databáze. Jaká bude až vyroste? Jaký osud na ni čeká? Po sudičkách není nikde ani vidu ani slechu, ale nad kolébkou stojí moudrý muž a praví…

Seznámíme se nyní se základními principy relačních databázových systémů.

Relační databázový systém Teorii relačních databázových systémů formuloval Dr. E.F.Codd v roce /:+<. Citujme z [/] str.2: a str.33:

Podmínky minimální relačnosti D. Všechny údaje v databázi jsou uloženy v tabulkách. To znamená za prvé, že v databázi musí být nějaké tabulky, do kterých je možné ukládat data. A za druhé, že není možné ukládat data někam jinam. \. Fyzická struktura údajů a jejich uložení je nezávislé a úplně od uživatelů odstíněné, to znamená, že neexistují nějaké uživateli viditelné přístupové cesty (včetně indexů). Data z tabulek jsou obvykle uložena na disku počítače v nějakých souborech. S těmito soubory může přímo pracovat pouze databázový systém. Uživateli není přístup k těmto souborům dovolen. Jediný možný přístup k datům je tedy prostřednictvím databázového systému. _. Pro práci s údaji v databázi předpokládáme existenci databázového jazyku, který umožňuje realizovat minimální operace selekce, restrikce, projekce a spojení. Tímto jazykem je jazyk SQL. Relační databáze − jazyk SQL - Studijní opora

11

Dvanáct pravidel pro relační databázové systémy D. Údaje v relační databázi musí být reprezentovány explicitně na logické úrovni pomocí relačních tabulek. Tabulka v relačním databázovém systému představuje relaci, tak jak ji známe z matematiky. Připomeňme si, že relace je množina uspořádaných n−tic. Tomu odpovídající relační tabulka má n sloupců a jednotlivé řádky odpovídají uspořádaným n−ticím hodnot uvedených v jednotlivých sloupcích. \. Údaje uložené v relační databázi musí být přístupné kombinací názvu tabulky, názvu sloupce a hodnoty primárního klíče. V síťovém nebo distribuovaném databázovém prostředí obvykle udáváme i název databáze. Toto je velmi zajímavé. Nezáleží totiž vůbec na pořadí sloupců a řádků v relačních tabulkách. Sloupce nejsou identifikovány pořadím, ale výhradně svými názvy. K identifikaci řádku se používá primární klíč, což je obvykle hodnota v předem vybraném sloupci. Často je touto hodnotou číslo. Řádky tedy nejsou očíslované samy od sebe, ale sloupec s číslem musí být součástí tabulky. _. Musí existovat indikátor chybějící hodnoty, anebo hodnoty, kterou neznáme (rozdílný od čísla nula a prázdného řetězce). Tento indikátor označujeme NULL. V praxi je běžné, že některé hodnoty neznáme. Buď proto, že vůbec nemají smysl, nebo je neznáme v době, kdy zapisujeme řádek do tabulky, a hodnotu chceme doplnit později. c. Databáze musí umožňovat autorizovaným uživatelům přístup nejen k údajům, ale i k jejich popisům (metadatům) pomocí stejného databázového jazyka. Tímto požadujeme, aby existovala nějaká „databáze o databázi“, která bude obsahovat informace o tom, jaké jsou v databázi tabulky, kolik mají sloupců, jak se sloupce jmenují a jakého jsou datového typu. Tyto údaje obecně nazýváme metadata, čili data o datech. d. Databázový jazyk musí být jednoduchý a uživatelsky přívětivý, přičemž musí umožňovat interaktivní i programový režim. Kromě toho musí umožňovat definici údajů, entitních omezení, manipulaci s údaji, definici transakcí a definici přístupových práv. Jak jednoduché, že? e. Relační databázový systém musí poskytovat způsob definování pohledů a musí umožňovat pro tyto pohledy povolení či zakázání vkládání či rušení řádků nebo aktualizaci sloupců v základních tabulkách, nad kterými je pohled vytvořen. Pohledy jsou uložené dotazy, kterými lze získat odpovědi na rozličné otázky. Například: „Kterého zboží se prodalo v předešlém měsíci nejvíce?“ Odpověď je samozřejmě závislá na datech, která jsou v danou chvíli uložena v tabulkách. f. Relační databázový systém musí umožňovat množinové operace s celými tabulkami nejen při vyhledávání, ale i při vkládání, aktualizaci a rušení dat. Tento požadavek by nás meměl překvapit. Tabulka je přece relací a relace je množinou. Proč tedy nevyužít sílu, kterou nám matematika se svými sjednoceními, průniky a kartézskými součiny dává? g. Aplikační logika nesmí vyžadovat modifikaci v případě změny interního ukložení nebo metody přístupu k údajům. Tento bod definuje fyzickou datovou nezávislost. Relační databáze − jazyk SQL - Studijní opora

12

Nelekejte se pojmu aplikační logika. Jde jen o obyčejný program, který běží na klientském počítači, tedy přímo u uživatele databáze. Požaduje se, stručně řečeno, aby nebylo třeba měnit uživatelské programy při změně uložení dat na databázovém serveru. Například by klientskému programu mělo být úplně jedno, jestli je na serveru každá tabulka uložena v samostatném souboru, nebo všechny tabulky společně v jednom souboru. h. Aplikační logika nesmí vyžadovat modifikaci v případě změn základních tabulek nevyvolávajících ztrátu informace (zrušení nebo přidání sloupce do tabulky). Tento bod definuje logickou datovou nezávislost. Změna sloupců v tabulce je velice častou úpravou databáze, protože uživatelé téměř vždy požadují, aby již hotový systém uměl ještě „něco“, nějakou − jak obvykle říkají − maličkost. Pokud jsme s tím při psaní klientských programů počítali, není přidání sloupce do tabulky žádný problém. Pokud ne, nezbývá než všechny klientské programy přepsat nebo alespoň překontrolovat, zda stále ještě fungují. To je však nutná daň za porušení :. pravidla. Di. Aplikační logika nesmí vyžadovat modifikaci v případě změn integritních omezení definovaných pomocí databázového jazyka a uložených v katalogu dat. Jednoduše řečeno: do věcí, které se nastavují na serveru, klientské aplikaci nic není. Aplikace se může spolehnout jen na to, že na databázovém serveru jsou tabulky se sloupci, které potřebuje a na nic víc. DD. Aplikační logika nesmí vyžadovat modifikaci v případě, když jsou data distribuovaná na různých počítačích. Potřebujeme-li, ať už z jakýchkoliv důvodů, umístit data na více serverů, dodržení toho pravidla nám to umožní bez toho, abychom museli přepisovat zdrojové kódy uživatelských programů. D\. Když má databázový systém nízkoúrovňový (procedurální) programovací jazyk, tomuto jazyku nesmí být umožňeno rušit nebo měnit omezení definovaná databázovým jazykem. Jazyk SQL je sám o sobě neprocedurální. To znamená, že říkáme co se má udělat, ale neříkáme jak. To je vnitřní záležitostí serveru. Někdy je však vhodné i serveru říci jak, a proto má většina databázových serverů nejaké procedurální rozšíření. Toto rozšíření však musí zůstat nadstavbou jazyka SQL a nesmí jej nijak obcházet při přístupu k datům a definici struktury databáze.

Otázky. Je třeba přepisovat kód klientské aplikace, pokud do databáze přidáme několik sloupců? Je možné přistupovat z klientské aplikace přímo k souborům, ve kterých jsou uložena data na databázovém serveru? Jak označujeme nevyplněnou hodnotu? Záleží na pořadí sloupců a řádků v relačních tabulkách?

Shrnutí kapitoly. Data ukládáme do tabulek. Protože tabulka je relace, zacházíme s ní jako s množinou. Vůbec nezáleží na pořadí řádek. Duplicitní řádky jsou ignorovány. K datům nepřistupujeme Relační databáze − jazyk SQL - Studijní opora

13

přímo, ale prostřednictvím relačního databázového systému. Relační databázový systém ovládáme příkazy v jazyce SQL.


14

Kapitola _. Množinové operace v SQL V této kapitole se dozvíte: Jak pomocí relačního databázového systému a jazyka SQL řešit matematické úlohy z teorie množin.

Čas potřebný k prostudování učiva kapitoly: /< hodin

Průvodce studiem. Matematika je relační databázi souzena. Nedá se nic dělat − musí do školy. Co jen jsou ty množiny, průniky, uspořádané dvojice, relace a funkce zač? Jistě jí rádi pomůžete, aby se všechno rychle naučila a nedělala při tom moc chyb. A prosím Vás, mějte trpělivost, když to té Vaší databázi nepůjde hned napoprvé. Je přece jenom ještě malá.

Pro úvodní seznámení s jazykem SQL si ukážeme, jakým způsobem lze v tomto jazyce provádět základní matematické operace s množinami.

Množina Množina je základním pojmem matematické teorie množin. V relačním databázovém systému pro množiny vytváříme tabulky. Prvky množiny představují jednotlivé řádky tabulky. Pro jednoduchost úplně postačí tabulka s jedním sloupcem, jehož hodnoty označují prvky. Zadáním datového typu sloupce stanovíme, že tabulka představuje podmnožinu množiny hodnot daného datového typu. Pro jednoduchost budeme používat datový typ int – integer [intidžǝr] s celočíselnými hodnotami − přesněji dostatečně velký interval celých čísel (ℤ).

Příklad. Vytvořte tabulku množiny A≝{/,1,3}.

Vytvoření tabulky Neprve definujeme tabulku A jakožto podmnožinu ℤ (A⊂ℤ). Čili pro všechny její prvky X∈A platí, že X∈ℤ. Vytvoříme tabulku A s jedním sloupcem X typu int. K tomu můžeme použít buď příkaz create table jazyka SQL (samotný jazyk SQL bude vysvětlen až v následujících kapitolách), nebo uživatelsky příjemnější prostředí Vašeho databázového systému. CREATE TABLE A(X INT)

Do tabulky A vložíme řádek s hodnotou D ve sloupci X. Tím říkáme, že číslo / je prvkem množiny A: D∈ ∈A. Opět můžete použít SQL příkaz insert. Relační databáze − jazyk SQL - Studijní opora

15

INSERT INTO A(X) VALUES(D)

Co nyní obsahuje tabulka A? Matematicky můžeme psát: A=?. Obsah tabulky vypíšeme příkazem select. SELECT X FROM A

X / Což můžeme číst: A= {D} .

Vložení dalších hodnot Do tabulky A vložíme další hodnoty. Matematicky tím vlastně říkáme, že 1∈A ∧ 3∈A ∧ 5∈A ∧ 1∈A ∧ /∈A INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(_) INSERT INTO A(X) VALUES(c) INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(D)

Vypíšeme obsah tabulky: SELECT X FROM A

X / 1 3 5 1 / Což znamená, že A = {/, 1, 3, 5, 1, /} . Z matematiky ovšem víme, že každý prvek je v množině „pouze jednou“. Nelze nijak rozlišit množiny {/, 1, 3, 5, 1, /} a {/, 1, 3, 5} od sebe. Dokonce nezáleží ani na pořadí prvků v množině, takže i zápis {5, 3, 1, /} představuje stále tu samou množinu A. A = {/, 1, 3, 5, 1, /} = {/, 1, 3, 5} = {5, 3, 1, /} Uvědomte si podstatný rozdíl mezi relační (množinovou) tabulkou a obyčejnou tabulkou. V obyčejné tabulce (na papíře nebo v tabulkovém procesoru) záleží na pořadí řádků a řádky se mohou opakovat (duplicita). V relační tabulce na pořadí řádků vůbec nezáleží a navíc by se neměly opakovat. Výskyt duplicitních řádků nemá žádný význam. Je tedy úplně jedno, kolikrát je daný řádek v tabulce uveden. Záleží jen na tom, zda je v tabulce aspoň jednou nebo tam vůbec není.

Ostranění duplicity Relační databáze − jazyk SQL - Studijní opora

16

Ke zkrácení výpisu použijeme klíčové slovo distinct, které způsobí vynechání duplicitních řádků: SELECT DISTINCT X FROM A

X / 1 3 5 Opakování duplicitních řádků lze zabránit už při definici tabulky nastavením primárního – primary [praimǝri] klíče. Vytvořme znovu tabulku A: CREATE TABLE A(X INT NOT NULL PRIMARY KEY)

Chyba na řádku -: There is already an object named 'A' in the database. Bohužel se to nepovedlo. Chybové hlášení ze serveru říká, že tabulku A nelze vytvořit, protože na serveru již existuje. Musíme ji nejprve smazat příkazem DROP a teprve potom vytvořit: DROP TABLE A CREATE TABLE A(X INT NOT NULL PRIMARY KEY)

A nyní vložíme data stejně jako před tím: INSERT INTO A(X) VALUES(D) INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(_) INSERT INTO A(X) VALUES(c) INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(D)

Chyba na řádku <: Violation of PRIMARY KEY constraint 'PK__A__CDEF-CGC'. Cannot insert duplicate key in object 'dbo.A'. Chyba na řádku <: The statement has been terminated. Chyba na řádku G: Violation of PRIMARY KEY constraint 'PK__A__CDEF-CGC'. Cannot insert duplicate key in object 'dbo.A'. Chyba na řádku G: The statement has been terminated. Na straně serveru nyní došlo k chybám. Některé řádky nebylo množné vložit, protože došlo k duplicitě v primárním klíči. Ale to je přesně to, čeho jsme chtěli dosáhnout. Do nové tabulky není možné vkládat stejný řádek vícekrát než jednou. Podíváme se, co je v tabulce: SELECT X FROM A


17

X / 1 3 5 Pokud se Vám nelíbí automaticky generovaný název omezení – constraint [kǝnˡstreint], můžete použít vlastní: DROP TABLE A CREATE TABLE A(X INT NOT NULL CONSTRAINT PrimarniKlicTabulkyA PRIMARY KEY) INSERT INTO A(X) VALUES(D) INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(_) INSERT INTO A(X) VALUES(c) INSERT INTO A(X) VALUES(\) INSERT INTO A(X) VALUES(D)

Chyba na řádku D: Violation of PRIMARY KEY constraint 'PrimarniKlicTabulkyA'. Cannot insert duplicate key in object 'dbo.A'. Chyba na řádku D: The statement has been terminated. Chyba na řádku H: Violation of PRIMARY KEY constraint 'PrimarniKlicTabulkyA'. Cannot insert duplicate key in object 'dbo.A'. Chyba na řádku H: The statement has been terminated.

Množinové operace Nejprve si vytvořme ještě druhou tabulku B: CREATE TABLE B(Y INT NOT NULL PRIMARY KEY) INSERT INTO B(Y) VALUES(_) INSERT INTO B(Y) VALUES(c) INSERT INTO B(Y) VALUES(d) INSERT INTO B(Y) VALUES(e)

A= {/, 1, 3, 5} B= {3, 5, ,, 7}

Sjednocení Jak určit sjednocení – union [juˡniǝn] množin A a B? A∪B=? Z matematiky si zopakujme, že A∪B= {/, 1, 3, 5} ∪ {3, 5, ,, 7} = {/, 1, 3, 5, ,, 7} A nyní zkusíme totéž na databázovém serveru:


18

SELECT X FROM A UNION SELECT Y FROM B

X / 1 3 5 , 7

Průnik Obdobně můžeme vytvořit průnik – intersection [intǝˡsekšn] množin A a B: A∩B=? A∩B= {/, 1, 3, 5} ∩ {3, 5, ,, 7} = {3, 5} SELECT X FROM A INTERSECT SELECT Y FROM B

X 3 5

Rozdíl Rozdíl množin A a B je dle matematické definice množina všech prvků množiny A s výjimkou – exception [ikˡsepšn] prvků množiny B: A−B=? A−B= {/, 1, 3, 5} − {3, 5, ,, 7} = {/, 1} SELECT X FROM A EXCEPT SELECT Y FROM B

X / 1

Kartézský součin Kartézský součin, někdy též zvaný součin křížový – cross [kros], je základem matematické teorie relací. Prvky kartézského součinu množin A a B jsou všechny uspořádané dvojice, jejichž první člen je prvkem množiny A a druhý člen je prvkem množiny B. A×B=? A×B= {/, 1, 3, 5} × {3, 5, ,, 7} = {[/, 3], [1, 3], [3, 3], [5, 3], [/, 5], [1, 5], [3, 5], [5, 5], [/, ,], [1, ,], [3, ,], [5, ,], [/, 7], [1, 7], [3, 7], [5, 7]} SELECT X, Y FROM A CROSS JOIN B


19

X / 1 3 5 / 1 3 5 / 1 3 5 / 1 3 5

Y 3 3 3 3 5 5 5 5 , , , , 7 7 7 7

Množina uspořádaných dvojic je reprezentována tabulkou, která má dva sloupce. Řádky této tabulky tvoří jednotlivé uspořádané dvojice. Hodnota prvního členu je uvedena v prvním sloupci a druhého ve druhém. Pozor však na to, že tak jako v relačních tabulkách nezáleží na pořadí řádků, nezáleží ani na pořadí sloupců. Sloupce jsou jednoznačně určeny svými jmény.

Podmnožina Dalším z důležitých pojmů je zajisté podmnožina. Přidejme tabulku C, která je podmnožinou tabulky A: C⊂A CREATE TABLE C ( Z INT NOT NULL CONSTRAINT PrimarniKlicC PRIMARY KEY CONSTRAINT CJePodmnozinouA FOREIGN KEY REFERENCES A(X) )

Fakt, že C⊂A, je zajištěn cizím – foreign [forǝn] klíčem. Vyzkoušíme to vložením následujících hodnot: INSERT INTO C(Z) VALUES(\) INSERT INTO C(Z) VALUES(_) INSERT INTO C(Z) VALUES(c) INSERT INTO C(Z) VALUES(d) INSERT INTO C(Z) VALUES(\)

Chyba na řádku F: The INSERT statement conflicted with the FOREIGN KEY constraint "CJePodmnozinouA". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\PAVEL\DOKUMENTY\VISUAL STUDIO SCC<\WEBSITES\WEBSITE-\APP_DATA\DATABASE.MDF", table "dbo.A", column 'X'. Relační databáze − jazyk SQL - Studijní opora

20

Chyba na řádku F: The statement has been terminated. Chyba na řádku <: Violation of PRIMARY KEY constraint 'PrimarniKlicC'. Cannot insert duplicate key in object 'dbo.C'. Chyba na řádku <: The statement has been terminated. Z chybových hlášení z databázového serveru je zřejmé, že do tabulky C nelze vkládat hodnoty, které nejsou uloženy v tabulce A. Ale to přesně odpovídá matematické definici podmnožiny. Jak tedy tabulka C vypadá? SELECT Z FROM C

Z 1 3 5 C= {1, 3, 5}

Relace Průvodce studiem. Jste dobří. Dočíst až sem je pořádný výkon! Určitě jste už hrozně zvědaví, jak je to s těmi relacemi v relačních databázových systémech. Dopřejte si však napřed trochu odpočinku, následující text je fakt hustej. Co takhle jednu kávičku?

Relace R:A→B je podmnožina kartézského součinu: R⊂A×B. Relační − a zde je slovo relační opravdu namístě − tabulka relace R obsahuje dva sloupce, abychom do ní mohli ukládat uspořádané dvojice z A×B. Hodnoty ve sloupci X (první člen uspořádané dvojice) musí být z tabulky (množiny) A. To zajistíme cizím klíčem RXMusiBytVAX. Obdobně hodnoty ve sloupci Y musí být z tabulky B. Opět zajistíme cizím klíčem RYMusiBytVBY. Nakonec přidáme primární klíč, který je dvousloupcový. Duplicita celého řádku (hodnoty v obou sloupcích se opakují) není povolena. Je však možné opakovat hodnotu v jednotlivých sloupcích s tím, že hodnoty v druhém sloupci se musí od sebe lišit. CREATE TABLE R ( X INT NOT NULL CONSTRAINT RXMusiBytVAX FOREIGN KEY REFERENCES A(X), Y INT NOT NULL CONSTRAINT RYMusiBytVBY FOREIGN KEY REFERENCES B(Y), CONSTRAINT PrimarniKlicR PRIMARY KEY (X, Y) )

Pokusíme-li se vložit do tabulky R následující dvojice, zjistíme, že je možné vkládat pouze ty, které jsou v A×B, a to ještě pouze jednou. INSERT INTO R(X, Y) VALUES(D, D)


21

INSERT INTO R(X, Y) VALUES(D, \) INSERT INTO R(X, Y) VALUES(D, _) INSERT INTO R(X, Y) VALUES(\, _) INSERT INTO R(X, Y) VALUES(_, _) INSERT INTO R(X, Y) VALUES(_, c) INSERT INTO R(X, Y) VALUES(_, d) INSERT INTO R(X, Y) VALUES(d, d) INSERT INTO R(X, Y) VALUES(D, _)

Chyba na řádku -: The INSERT statement conflicted with the FOREIGN KEY constraint "RYMusiBytVBY". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\PAVEL\DOKUMENTY\VISUAL STUDIO SCC<\WEBSITES\WEBSITE-\APP_DATA\DATABASE.MDF", table "dbo.B", column 'Y'. Chyba na řádku -: The statement has been terminated. Chyba na řádku S: The INSERT statement conflicted with the FOREIGN KEY constraint "RYMusiBytVBY". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\PAVEL\DOKUMENTY\VISUAL STUDIO SCC<\WEBSITES\WEBSITE-\APP_DATA\DATABASE.MDF", table "dbo.B", column 'Y'. Chyba na řádku S: The statement has been terminated. Chyba na řádku H: The INSERT statement conflicted with the FOREIGN KEY constraint "RXMusiBytVAX". The conflict occurred in database "C:\DOCUMENTS AND SETTINGS\PAVEL\DOKUMENTY\VISUAL STUDIO SCC<\WEBSITES\WEBSITE-\APP_DATA\DATABASE.MDF", table "dbo.A", column 'X'. Chyba na řádku H: The statement has been terminated. Chyba na řádku E: Violation of PRIMARY KEY constraint 'PrimarniKlicR'. Cannot insert duplicate key in object 'dbo.R'. Chyba na řádku E: The statement has been terminated. Do tabulky R se podařilo vložit tyto dvojice: SELECT X, Y FROM R

X / 1 3 3 3

Y 3 3 3 5 ,

Tabulka R tedy představuje relaci R= {[/, 3], [1, 3], [3, 3], [3, 5], [3, ,]}

Inverzní relace Inverzní relaci získáme záměnou sloupců X a Y. Relační databáze − jazyk SQL - Studijní opora

22

R−/=? SELECT Y, X FROM R

Y 3 3 3 5 ,

X / 1 3 3 3 R−/= {[3, /], [3, 1], [3, 3], [5, 3], [,, 3]}

Definiční obor Definiční obor získáme výpisem hodnot ze sloupce X s potlačením duplicit klíčovým slovem distinct. D(R)=? SELECT DISTINCT X FROM R

X / 1 3 D(R)= {/, 1, 3}

Obor hodnot Obor hodnot získáme výpisem sloupce Y opět s potlačením duplicit. H(R)=? SELECT DISTINCT Y FROM R

Y 3 5 , H(R)= {3, 5, ,}

Složená relace Abychom mohli skládat relace, vytvořme si ještě jednu relaci S:B→C: CREATE TABLE S (


23

Y INT NOT NULL CONSTRAINT SYMusiBytVBY FOREIGN KEY REFERENCES B(Y), Z INT NOT NULL CONSTRAINT SZMusiBytVCZ FOREIGN KEY REFERENCES C(Z), CONSTRAINT PrimarniKlicS PRIMARY KEY (Y, Z) )

INSERT INTO S(Y, Z) VALUES(_, \) INSERT INTO S(Y, Z) VALUES(_, _) INSERT INTO S(Y, Z) VALUES(c, _) INSERT INTO S(Y, Z) VALUES(c, c) INSERT INTO S(Y, Z) VALUES(e, _) INSERT INTO S(Y, Z) VALUES(e, c)

SELECT Y, Z FROM S

Y 3 3 5 5 7 7

Z 1 3 3 5 3 5 S= {[3, 1], [3, 3], [5, 3], [5, 5], [7, 3], [7, 5]}

A nyní slíbené skládání relací: R○S=? Nejprve krátké opakování z matematiky: R○S= {[/, 3], [1, 3], [3, 3], [3, 5], [3, ,]} ○ {[3, 1], [3, 3], [5, 3], [5, 5], [7, 3], [7, 5]} = {[/, 1], [/, 3], [1, 1], [1, 3], [3, 1], [3, 3], [3, 5]} a potom totéž na SQL serveru: SELECT DISTINCT X, Z FROM R INNER JOIN S ON R.Y=S.Y

X / / 1 1 3 3 3

Z 1 3 1 3 1 3 5

Funkce Zkusme nyní vytvořit tabulku F velice podobnou tabluce S. Rozdíl je jen nepatrný, ale velmi důležitý. Najdete jej? Relační databáze − jazyk SQL - Studijní opora

24

CREATE TABLE F ( Y INT NOT NULL CONSTRAINT FYMusiBytVBY FOREIGN KEY REFERENCES B(Y), Z INT NOT NULL CONSTRAINT FZMusiBytVCZ FOREIGN KEY REFERENCES C(Z), CONSTRAINT PrimarniKlicF PRIMARY KEY (Y) )

Rozdíl se projeví i při vkládání dat: INSERT INTO F(Y, Z) VALUES(_, \) INSERT INTO F(Y, Z) VALUES(_, _) INSERT INTO F(Y, Z) VALUES(c, _) INSERT INTO F(Y, Z) VALUES(c, c) INSERT INTO F(Y, Z) VALUES(e, _) INSERT INTO F(Y, Z) VALUES(e, c)

Chyba na řádku S: Violation of PRIMARY KEY constraint 'PrimarniKlicF'. Cannot insert duplicate key in object 'dbo.F'. Chyba na řádku S: The statement has been terminated. Chyba na řádku F: Violation of PRIMARY KEY constraint 'PrimarniKlicF'. Cannot insert duplicate key in object 'dbo.F'. Chyba na řádku F: The statement has been terminated. Chyba na řádku G: Violation of PRIMARY KEY constraint 'PrimarniKlicF'. Cannot insert duplicate key in object 'dbo.F'. Chyba na řádku G: The statement has been terminated. Ano, změna se týká primárního klíče. Zatímco primárním klíčem tabulky S je dvojice [Y, Z], primárním klíčem tabulky F je samotný sloupec Y. Hodnoty v tomto sloupci se tedy nemohou opakovat, jinými slovy: každé hodnotě Y může být přiřazena nejvýše jedna hodnota Z. To je ale přesně definiční vlastnost funkce. Tabulka F je tedy tabulkou funkce F:B→C. SELECT Y, Z FROM F

Y 3 5 7

Z 1 3 3 F= {[3, 1], [5, 3], [7, 3]}

Dvojici [3, 3] nejde vložit, protože [3, 1]∈F a hodnotě 3 nemůžeme přiřadit dvě různé hodnoty. Totéž platí i pro dvojice [5, 5] a [7, 5]. Databázový server nedovolí tyto dvojice uložit a tím je zajištěno, že relace F je funkcí. Prohlédněme si nyní pozorně tabulky B a F: SELECT Y FROM B

Y Relační databáze − jazyk SQL - Studijní opora

25

3 5 , 7 SELECT Y, Z FROM F

Y 3 5 7

Z 1 3 3

Pokud bychom přidali do tabulky B ještě jeden sloupec, mohli bychom tabulku F zrušit. Pro nový sloupec se nabízí název Z, ale vhodnější bude jej pojmenovat podle funkce F: ALTER TABLE B ADD F INT NULL FOREIGN KEY REFERENCES C(Z)

Hodnoty ve sloupci F zadáme příkazem UPDATE: UPDATE B SET F=\ WHERE Y=_ UPDATE B SET F=_ WHERE Y=c UPDATE B SET F=_ WHERE Y=e

SELECT Y, F FROM B

Y 3 5 , 7

F 1 3 3

V řádku Y=, je sloupec F prázdný, přesněji má hodnotu NULL, protože hodnota , není v definičním oboru funkce F. Tabulku funkce F získáme z tabulky B takto: SELECT Y, F AS Z FROM B WHERE F IS NOT NULL

Y 3 5 7

Z 1 3 3

Do tabulky B můžeme přidat i další funkce. Například G. ALTER TABLE B ADD G INT NULL FOREIGN KEY REFERENCES C(Z)

Hodnoty ve sloupci G zadáme opět příkazem UPDATE: UPDATE B SET G=\ WHERE Y=_


26

UPDATE B SET G=c WHERE Y=c UPDATE B SET G=c WHERE Y=d

SELECT Y, F, G FROM B

Y 3 5 , 7

F 1 3

G 1 5 5

3

Prostá funkce Je-li funkce zvláštní relací, pak prostá fukce je ještě více zvláštní. Zkusme vytvořit novou funkci H tabulkou podobně jako funkci F. Drobná změna, které si jistě všimnete, zajistí, že H bude funkcí prostou. CREATE TABLE H ( Y INT NOT NULL CONSTRAINT HYMusiBytVBY FOREIGN KEY REFERENCES B(Y), Z INT NOT NULL CONSTRAINT HZMusiBytVCZ FOREIGN KEY REFERENCES C(Z) CONSTRAINT HZMusiBytJedinecne UNIQUE, CONSTRAINT PrimarniKlicH PRIMARY KEY (Y) )

Rozdíl se opět projeví při vkládání dat: INSERT INTO H(Y, Z) VALUES(_, \) INSERT INTO H(Y, Z) VALUES(c, _) INSERT INTO H(Y, Z) VALUES(e, _)

Chyba na řádku V: Violation of UNIQUE KEY constraint 'HZMusiBytJedinecne'. Cannot insert duplicate key in object 'dbo.H'. Chyba na řádku V: The statement has been terminated. Dvojici [7, 3] nelze vložit, protože hodnota 3 je již přiřazena hodnotě 5 a nelze ji přiřadit podruhé. Přesně podle definice prosté funkce. SELECT Y, Z FROM H

Y 3 5

Z 1 3 H= {[3, 1], [5, 3]}

Nakonec po sobě uklidíme. Na pořadí, ve kterém rušíme tabulky, záleží, protože mezi tabulkami jsou vazby realizované cizími klíči (foreign key). DROP TABLE H


27

DROP TABLE F DROP TABLE S DROP TABLE R DROP TABLE B DROP TABLE C DROP TABLE A

Shrnutí kapitoly. Množinu prvků vytvoříme příkazem CREATE TABLE. Pomocí SELECT … UNION … a SELECT … INTERSECTION … vypočteme sjednocení a průnik. Pro výpočet kartézského součinu použijeme SELECT … CROSS JOIN …. Pomocí cizího klíče (foreign key) zajistíme, že jedna množina bude vždy podmnožinou jiné. Relaci ukládáme do databáze jako tabulku s více sloupci. Definiční obor a obor hodnot stanovíme cizím klíčem. Vhodnou volbou primárního klíče můžeme dosáhnout toho, že daná relace bude funkcí. Přidáme-li index, může být tato funkce dokonce prostá.


28

Kapitola c. Normální formy V této kapitole se dozvíte: Jak navrhovat tabulky relačních databázových systémů. Jak se vyhnout některým problémům.

Klíčová slova této kapitoly: Atribut Schéma relace Funkční závislost Klíč Normální forma

Čas potřebný k prostudování učiva kapitoly: , hodin

Průvodce studiem. To to uteklo. Relační databáze pěkně vyrostla. Všechno si pamatuje. Nic sama od sebe nezapomíná, jenom když se jí řekne. Ale co to? Občas se chová velice podivně. To jsou ale způsoby! Je na čase přihlásit relační databázi do tanečních. A co se v tanečních databáze učí? No přece pět databázových tanců − první, druhou, třetí, čtvrtou a pátou normální formu. Zatančete si také. Smím prosit?

V této kapitole si vysvětlíme základní principy návrhu databázového systému. Nejprve nás čekají nezbytné definice a trocha teorie. Nenechte se odradit. Praktické příklady na konci kapitoly snad budou srozumitelnější.

Atributy Sloupcům databázových tabulek budeme říkat atributy. Jména atributů budeme označovat velkými písmeny ze začátku abecedy A, B, C… Hodnoty atributů budeme označovat malými písmeny a, b, c… Množiny jmen atributů budeme označovat velkými písmeny z konce abecedy X, Y, Z, U, V… Množina přípustných hodnot atributů je dána doménou (datovým typem) atributu, kterou budeme značit D(A).

Schéma relace Relační databáze − jazyk SQL - Studijní opora

29

Záhlaví relační tabulky, které obsahuje jméno tabulky a jména sloupců, budeme nazývat schéma relace a zapisovat R(A, B, … C). Platí: R(A,B,…C)⊂D(A)×D(B)×…×D(C) Je-li X={A, B,…C} množina jmen atributů, můžeme místo R(A, B,…C) psát stručně R(X).

Řádky tabulky Prvky relace (uspořádané n−tice hodnot) představují řádky relační tabulky. Je-li r řádek tabulky R, můžeme hodnotu ve sloupci A (neboli hodnotu atributu A) označit A(r) nebo r.A. Pro jednotlivé řádky r∈R platí: r=[A(r),B(r),…C(r)]=[r.A,r.B,…r.C]∈R(A,B,…C) tedy: r.A∈D(A)∧r.B∈D(B)∧…∧r.C∈D(C)

Funkční závislost …→… X→Y≝∀R∀x∈R(X)∃?y∈R(Y):x∪y∈R(X∪Y) Hodnoty atributů z Y jsou (funkčně) závislé na hodnotách atributů z X. To znamená, že ke každé n−tici hodnot atributů z X existuje nejvýše jedna n−tice hodnot atributů z Y.

Příklad. Funkční závislost PSČ → Obec znamená, že ke každému PSČ existuje nejvýše jedna obec.

Triviální funkční závislost ∀X∀Y:Y⊂X⇒X→Y

Příklad. {Jméno, Příjmení} → {Jméno}. Pokud nehrozí nedorozumění, můžeme složené závorky vynechat: Jméno, Příjmení → Jméno.

Tranzitivní funkční závislost ∀X∀Y∀Z:X→Y∧Y→Z⇒X→Z

Příklad. Auto → Majitel ∧ Majitel → Adresa ⇒ Auto → Adresa.

Kompozice a dekompozice funkčních závislostí Relační databáze − jazyk SQL - Studijní opora

30

∀X∀Y∀Z:X→Y∧X→Z⇔X→Y∪Z

Příklad. RodnéČíslo → Jméno ∧ RodnéČíslo → Příjmení ⇔ RodnéČíslo → Jméno, Příjmení

Klíč Y je klíč R(X)≝Y→X∧∀Z⊊Y:Z↛X Množina atributů Y je klíčem schématu relace R(X), jestliže X je funkčně závislé na Y a zároveň X není funkčně závislé na žádné vlastní podmnožině Y.

Klíčový atribut A je klíčový atribut v R(X)≝∃Y:A∈Y∧Y je klíč R(X) Klíčový atribut je atribut, který není prvkem nějakého klíče.

Neklíčový atribut A je neklíčový atribut v R(X)≝A∈X∧¬A je klíčový atribut v R(X) Neklíčový atribut je atribut, který není klíčový.

Tranzitivně závislý atribut A je atribut v R(X) tranzitivně závislý na Y≝ ∃Z:A∈X−Y−Z∧Y→Z→A∧Z↛Y

D. normální forma Všechny atributy jsou dále nedělitelné (atomické). R(X)∈/NF

Příklad. CREATE TABLE Student ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(\d) NULL, Predmety NVARCHAR(\d) NULL, Znamky NVARCHAR(Di) NULL ); INSERT INTO Student(Cislo, Jmeno, Predmety, Znamky) VALUES(D, 'Jan Nový', 'DAT, MAT', '\, D'); INSERT INTO Student(Cislo, Jmeno, Predmety, Znamky) VALUES(\, 'Milan Svoboda', 'ANG, DAT', 'D, _'); INSERT INTO Student(Cislo, Jmeno, Predmety, Znamky) VALUES(_, 'Matěj Opička', 'ANG', 'c'); SELECT * FROM Student; DROP TABLE Student;

Cislo

Jmeno


Predmety

Znamky 31

/ 1 3

Jan Nový Milan Svoboda Matěj Opička

DAT, MAT ANG, DAT ANG

1, / /, 3 5

Řešením je rozdělení tabulky na více sloupců. CREATE TABLE Student ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL, PredmetD CHAR(_) NULL, ZnamkaD INT NULL, Predmet\ CHAR(_) NULL, Znamka\ INT NULL ); INSERT INTO Student(Cislo, Jmeno, Prijmeni, PredmetD, ZnamkaD, Predmet\, Znamka\) VALUES(D, 'Jan', 'Nový', 'DAT', \, 'MAT', D); INSERT INTO Student(Cislo, Jmeno, Prijmeni, PredmetD, ZnamkaD, Predmet\, Znamka\) VALUES(\, 'Milan', 'Svoboda', 'ANG', D, 'DAT', _); INSERT INTO Student(Cislo, Jmeno, Prijmeni, PredmetD, ZnamkaD) VALUES(_, 'Matěj', 'Opička', 'ANG', c); SELECT * FROM Student; DROP TABLE Student;

Cislo / 1 3

Jmeno Jan Milan Matěj

Prijmeni Nový Svoboda Opička

PredmetD DAT ANG ANG

ZnamkaD 1 / 5

Predmet\ MAT DAT

Znamka\ / 3

Tohle už je lepší, ale pořád to není ono. Co když má někdo více známek než dvě? Příliš velké množství sloupců navíc neúměrně zesložiťuje kód programu. Řešení nabízejí další normální formy.

\. normální forma Každý neklíčový atribut musí být závislý na celém klíči. R(X)∈\NF≝R(X)∈/NF∧∀A∈X∀Y:Y je klíč R(X)⇒∀Z⊊Y:Z↛A

Příklad. CREATE TABLE Klasifikace ( CisloStudenta INT NOT NULL, JmenoStudenta NVARCHAR(Di) NULL, PrijmeniStudenta NVARCHAR(Dd) NULL, KodPredmetu CHAR(_) NOT NULL, NazevPredmetu NVARCHAR(\i) NULL, CisloUcitele INT NULL, JmenoUcitele NVARCHAR(Di) NULL, PrijmeniUcitele NVARCHAR(Dd) NULL, Znamka INT NULL, CONSTRAINT PrimarniKlicKlasifikace PRIMARY KEY(CisloStudenta, KodPredmetu) ); INSERT INTO Klasifikace(CisloStudenta, JmenoStudenta, PrijmeniStudenta, KodPredmetu, NazevPredmetu, CisloUcitele, JmenoUcitele, PrijmeniUcitele, Znamka) VALUES(D, 'Jan', 'Nový', 'DAT', 'Databázové systémy', _, 'Karel', 'Dvořák', \);


32

INSERT INTO Klasifikace(CisloStudenta, JmenoStudenta, PrijmeniStudenta, KodPredmetu, NazevPredmetu, CisloUcitele, JmenoUcitele, PrijmeniUcitele, Znamka) VALUES(D, 'Jan', 'Nový', 'MAT', 'Matematika', _, 'Karel', 'Dvořák', D); INSERT INTO Klasifikace(CisloStudenta, JmenoStudenta, PrijmeniStudenta, KodPredmetu, NazevPredmetu, CisloUcitele, JmenoUcitele, PrijmeniUcitele, Znamka) VALUES(\, 'Milan', 'Svoboda', 'DAT', 'Databázové systémy', _, 'Karel', 'Dvořák', _); INSERT INTO Klasifikace(CisloStudenta, JmenoStudenta, PrijmeniStudenta, KodPredmetu, NazevPredmetu, CisloUcitele, JmenoUcitele, PrijmeniUcitele, Znamka) VALUES(\, 'Milan', 'Svoboda', 'ANG', 'Anglický jazyk', \, 'Adam', 'Dlouhý', D); INSERT INTO Klasifikace(CisloStudenta, JmenoStudenta, PrijmeniStudenta, KodPredmetu, NazevPredmetu, CisloUcitele, JmenoUcitele, PrijmeniUcitele, Znamka) VALUES(_, 'Matěj', 'Opička', 'ANG', 'Anglický jazyk', \, 'Adam', 'Dlouhý', c); SELECT * FROM Klasifikace; DROP TABLE Klasifikace; CisloStudenta

JmenoStudenta

PrijmeniStudenta

KodPredmetu

JmenoUcitele

PrijmeniUcitele

/

Jan

Nový

DAT

Databázové systémy

NazevPredmetu

CisloUcitele 3

Karel

Dvořák

Znamka 1

/

Jan

Nový

MAT

Matematika

3

Karel

Dvořák

/

1

Milan

Svoboda

ANG

Anglický jazyk

1

Adam

Dlouhý

/

1

Milan

Svoboda

DAT

Databázové systémy

3

Karel

Dvořák

3

3

Matěj

Opička

ANG

Anglický jazyk

1

Adam

Dlouhý

5

Mezi atributy najdeme následující závislosti: CisloStudenta → JmenoStudenta, PrijmeniStudenta KodPredmetu → NazevPredmetu, CisloUcitele CisloUcitele → JmenoUcitele, PrijmeniUcitele CisloStudenta, KodPredmetu → Znamka Primárním klíčem je dvojice CisloStudenta, KodPredmetu. Některé neklíčové atributy (např. JmenoStudenta) závisejí pouze části klíče. Platí CisloStudenta → JmenoStudenta ∧ KodPredmetu ↛ JmenoStudenta Tabulka Klasifikace tedy nesplňuje podmínky druhé normální formy. Praktickým důsledkem je to, že do tabulky je nutné zapisovat jméno studenta (např. Milan) vícekrát. To přináší řadu komplikací. Co dělat, když uvedená jména nejsou stejná? Kam uložit jméno studenta, který ještě nemá zapsán žádný předmět (např. Ondřej Nezbeda)? Řešením je rozložení tabulky do několika samostatných tabulek. CREATE TABLE Student ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL, ) CREATE TABLE Predmet ( Kod CHAR(_) NOT NULL PRIMARY KEY, Nazev NVARCHAR(\i) NULL, CisloUcitele INT NULL, JmenoUcitele NVARCHAR(Di) NULL, PrijmeniUcitele NVARCHAR(Dd) NULL, ) CREATE TABLE Klasifikace ( CisloStudenta INT NOT NULL FOREIGN KEY REFERENCES Student(Cislo), KodPredmetu CHAR(_) NOT NULL FOREIGN KEY REFERENCES Predmet(Kod), Znamka INT NULL, CONSTRAINT PrimarniKlicKlasifikace PRIMARY KEY(CisloStudenta, KodPredmetu)


33

); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(D, 'Jan', 'Nový'); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(\, 'Milan', 'Svoboda'); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(_, 'Matěj', 'Opička'); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(c, 'Ondřej', 'Nezbeda'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('DAT', 'Databázové systémy', _, 'Karel', 'Dvořák'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('MAT', 'Matematika', _, 'Karel', 'Dvořák'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('ANG', 'Anglický jazyk', \, 'Adam', 'Dlouhý'); INSERT INTO Klasifikace(CisloStudenta, KodPredmetu, Znamka) VALUES(D, 'DAT', \); INSERT INTO Klasifikace(CisloStudenta, KodPredmetu, Znamka) VALUES(D, 'MAT', D); INSERT INTO Klasifikace(CisloStudenta, KodPredmetu, Znamka) VALUES(\, 'DAT', _); INSERT INTO Klasifikace(CisloStudenta, KodPredmetu, Znamka) VALUES(\, 'ANG', D); INSERT INTO Klasifikace(CisloStudenta, KodPredmetu, Znamka) VALUES(_, 'ANG', c);

SELECT * FROM Student;

Cislo / 1 3 5

Jmeno Jan Milan Matěj Ondřej

Prijmeni Nový Svoboda Opička Nezbeda

SELECT * FROM Predmet;

Kod ANG DAT MAT

Nazev Anglický jazyk Databázové systémy Matematika

CisloUcitele 1 3 3

JmenoUcitele Adam Karel Karel

PrijmeniUcitele Dlouhý Dvořák Dvořák

SELECT * FROM Klasifikace;

CisloStudenta / / 1 1 3

KodPredmetu DAT MAT ANG DAT ANG

Znamka 1 / / 3 5

DROP TABLE Klasifikace; DROP TABLE Predmet; DROP TABLE Student;

_. normální forma Žádný neklíčový atribut není tranzitivně závislý na klíči. R(X)∈_NF ≝ R(X)∈1NF ∧ ¬∃A∃Y:A je neklíčový atribut v R(X) ∧ Y je klíč v R(X) ∧ A je atribut v R(X) tranzitivně závislý na Y Relační databáze − jazyk SQL - Studijní opora

34

Třetí normální formu si vysvětlíme na tabulce předmětů z předešlého příkladu.

Příklad. CREATE TABLE Predmet ( Kod CHAR(_) NOT NULL PRIMARY KEY, Nazev NVARCHAR(\i) NULL, CisloUcitele INT NULL, JmenoUcitele NVARCHAR(Di) NULL, PrijmeniUcitele NVARCHAR(Dd) NULL, ) INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('DAT', 'Databázové systémy', _, 'Karel', 'Dvořák'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('MAT', 'Matematika', _, 'Karel', 'Dvořák'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele, JmenoUcitele, PrijmeniUcitele) VALUES('ANG', 'Anglický jazyk', \, 'Adam', 'Dlouhý'); SELECT * FROM Predmet; DROP TABLE Predmet;

Kod ANG DAT MAT


CisloUcitele 1 3 3

JmenoUcitele Adam Karel Karel

PrijmeniUcitele Dlouhý Dvořák Dvořák

Opět vidíme duplicitu v zadávání jména učitele (Karel Dvořák je uveden dvakrát). Tabulka sice je v druhé normální formě (jméno závisí na celém klíči), ale potíž je v tom, že tranzitivně přes číslo učitele: Kod → CisloUcitele → JmenoUcitele, PrijmeniUcitele Řešením problému je opět rozdělení tabulky na více částí: CREATE TABLE Ucitel ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL, ) CREATE TABLE Predmet ( Kod CHAR(_) NOT NULL PRIMARY KEY, Nazev NVARCHAR(\i) NULL, CisloUcitele INT NULL FOREIGN KEY REFERENCES Ucitel(Cislo), ) INSERT INTO Ucitel(Cislo, Jmeno, Prijmeni) VALUES(D, 'Hana', 'Nováková'); INSERT INTO Ucitel(Cislo, Jmeno, Prijmeni) VALUES(\, 'Adam', 'Dlouhý'); INSERT INTO Ucitel(Cislo, Jmeno, Prijmeni) VALUES(_, 'Karel', 'Dvořák'); INSERT INTO Predmet(Kod, Nazev, CisloUcitele) VALUES('DAT', 'Databázové systémy', _); INSERT INTO Predmet(Kod, Nazev, CisloUcitele) VALUES('MAT', 'Matematika', _); INSERT INTO Predmet(Kod, Nazev, CisloUcitele) VALUES('ANG', 'Anglický jazyk', \);

SELECT * FROM Ucitel;

Cislo / 1

Jmeno Hana Adam

Prijmeni Nováková Dlouhý


35

3

Karel

Dvořák

SELECT * FROM Predmet;

Kod ANG DAT MAT


CisloUcitele 1 3 3

DROP TABLE Predmet; DROP TABLE Ucitel;

c. normální forma Každá tabulka popisuje jen jeden fakt nebo souvislost.

Příklad. Porušením čtvrté normální formy je následující tabulka: CREATE TABLE Jmena ( Cislo INT NOT NULL PRIMARY KEY, JmenoStudenta NVARCHAR(Di) NULL, PrijmeniStudenta NVARCHAR(Dd) NULL, JmenoUcitele NVARCHAR(Di) NULL, PrijmeniUcitele NVARCHAR(Dd) NULL ); INSERT INTO Jmena(Cislo, JmenoStudenta, PrijmeniStudenta, JmenoUcitele, PrijmeniUcitele) VALUES(D, 'Jan', 'Nový', 'Hana', 'Nováková'); INSERT INTO Jmena(Cislo, JmenoStudenta, PrijmeniStudenta, JmenoUcitele, PrijmeniUcitele) VALUES(\, 'Milan', 'Svoboda', 'Adam', 'Dlouhý'); INSERT INTO Jmena(Cislo, JmenoStudenta, PrijmeniStudenta, JmenoUcitele, PrijmeniUcitele) VALUES(_, 'Matěj', 'Opička', 'Karel', 'Dvořák'); INSERT INTO Jmena(Cislo, JmenoStudenta, PrijmeniStudenta, JmenoUcitele, PrijmeniUcitele) VALUES(c, 'Ondřej', 'Nezbeda', NULL, NULL); SELECT * FROM Jmena; DROP TABLE Jmena;

Cislo / 1 3 5

JmenoStudenta Jan Milan Matěj Ondřej

PrijmeniStudenta Nový Svoboda Opička Nezbeda

JmenoUcitele Hana Adam Karel

PrijmeniUcitele Nováková Dlouhý Dvořák

Je zajímavé, že tato tabulka splňuje třetí normální formu, ale je značně nepraktická.

d. normální forma Do žádné tabulky nelze přidat další sloupec bez porušení předchozích pravidel. Teoreticky by bylo možné, aby každá funkční závislost měla svou vlastní tabulku. Dokonce by mohla být i ve čtvrté normální formě. Aby tomu tak nebylo a tabulek bylo přiměřené množství, je tu pátá normální forma. Relační databáze − jazyk SQL - Studijní opora

36

Příklad. CREATE TABLE Jmena ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, ); CREATE TABLE Prijmeni ( Cislo INT NOT NULL PRIMARY KEY, Prijmeni NVARCHAR(Dd) NULL ); INSERT INTO Jmena(Cislo, Jmeno) VALUES(D, 'Jan'); INSERT INTO Jmena(Cislo, Jmeno) VALUES(\, 'Milan'); INSERT INTO Jmena(Cislo, Jmeno) VALUES(_, 'Matěj'); INSERT INTO Jmena(Cislo, Jmeno) VALUES(c, 'Ondřej'); INSERT INTO Prijmeni(Cislo, Prijmeni) VALUES(D, 'Nový'); INSERT INTO Prijmeni(Cislo, Prijmeni) VALUES(\, 'Svoboda'); INSERT INTO Prijmeni(Cislo, Prijmeni) VALUES(_, 'Opička'); INSERT INTO Prijmeni(Cislo, Prijmeni) VALUES(c, 'Nezbeda');

SELECT * FROM Jmena;

Cislo / 1 3 5


SELECT * FROM Prijmeni;

Cislo / 1 3 5


DROP TABLE Jmena; DROP TABLE Prijmeni;

Vše je ve čtvrté normální formě, ale vidíme, že je to z praktického hlediska poněduk nepohodlné. Řešením samozřejmě je jen jedna tabulka: CREATE TABLE Jmena ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL ); INSERT INTO Jmena(Cislo, Jmeno, Prijmeni) VALUES(D, 'Jan', 'Nový'); INSERT INTO Jmena(Cislo, Jmeno, Prijmeni) VALUES(\, 'Milan', 'Svoboda'); INSERT INTO Jmena(Cislo, Jmeno, Prijmeni) VALUES(_, 'Matěj', 'Opička'); INSERT INTO Jmena(Cislo, Jmeno, Prijmeni) VALUES(c, 'Ondřej', 'Nezbeda'); SELECT * FROM Jmena; DROP TABLE Jmena;


37

Cislo / 1 3 5



Příklad. Navrhněte databázi s funkčními závislostmi: CisloStudenta → JmenoStudenta, PrijmeniStudenta KodPredmetu → NazevPredmetu, CisloUcitele CisloUcitele → JmenoUcitele, PrijmeniUcitele CisloStudenta, KodPredmetu → Znamka

Řešení Řešením jsou následující relační schémata: Student(Cislo, Jmeno, Prijmeni) Predmet(Kod, Nazev, Ucitel) Ucitel(Cislo, Jmeno, Prijmeni) Klasifikace(Student, Predmet, Znamka) Primární klíče jsou vyznačeny tučně.

Korespondenční úkol. Navrhněte databázi s funkčními závislostmi: Trida → Patro Predmet, Student → Znamka Predmet → Nazev Trida → PocetStudentu Student → Prijmeni Ucitel → Jmeno Student → Jmeno Ucitel → Prijmeni Student → Trida Predmet, Trida → Ucitel Relační databáze − jazyk SQL - Studijní opora

38

Shrnutí kapitoly. Při návrhu databáze je vhodné dodržovat normální formy. /. normální forma požaduje, aby hodnoty atributů (sloupců) byly atomické. V databázích ve 1. normální formě neklíčové atributy závisejí na celém klíči. Je-li tato závislost přímá, máte databázi dokonce v 3. normální formě. Existují ještě další normální formy, jejich nasazení v praxi však může být problematické.


39

Kapitola d. Gramatická pravidla V této kapitole se dozvíte: Jak správně číst gramatická pravidla v následujících kapitolách.

Klíčová slova této kapitoly: Gramatika Produkční pravidlo Symboly

Čas potřebný k prostudování učiva kapitoly: 1 hodiny

Průvodce studiem. Umíte pozdravit? Jistěže ano, ale umíte také nějak popsat, jak má takový pozdrav vypadat? Seznámíme se nyní zápisem gramatických pravidel.

Než se pustíme do výkladu jazyka SQL, vysvětlíme si způsob zápisu gramatických pravidel, použitý v dalších kapitolách. Zkusme zapsat, jak se píše pozdrav. pozdrav → přívlastek část dne ( , oslovení )< – / ! Toto je produkční pravidlo, čili jakýsi výrobní postup na „výrobu“ pozdravů. Symbol „pozdrav“ je proto zvýrazněn. Šipka „→“ znamená, že symbol uvedený před šipkou se nahrazuje symboly za šipkou. Pozdrav tedy můžeme vyrobit tak, že si vezmeme symbol „pozdrav“ a pak jej podle pravidla nahradíme přívlastkem, částí dne, čárkou, oslovením a vykřičníkem. Čárka a vykřičník jsou terminálové symboly čili koncové. Pro ně už žádná pravidla nemáme a píší se přímo do textu. Terminálové symboly jsou v pravidlech zvýrazněny. Přívlastek, část dne a oslovení jsou neterminálové symboly. To znamená, že proces výroby těchto objektů není ještě dokončen a musíme pokračovat pomocí dalších pravidel. Výrobní proces je ukončen tehdy, když už máme jen terminálové symboly. Exponent M−N znamená, že daná část se může M−krát až N−krát opakovat. Exponent <−/ u závorky tedy znamená, obsah závorky je nepovinný. Píšeme jej <−krát (tedy nepíšeme) až jednou.


40

přívlastek → dobrý | hezký | krásný Svislá čára „|“ znamená „nebo“. Místo symbolu „přívlastek“ píšeme „dobrý“, nebo „hezký“, nebo „krásný“ část dne → den | večer oslovení → přívlastek < – N jméno Exponent <−N u přívlastku znamená, že můžeme napsat libovolné množství přívlastků nebo taky žádný. jméno → Ondřeji | Matěji

Příklad. Podle výše uvedených pravidel sestavte tyto pozdravy: Dobrý večer! Hezký den, dobrý Ondřeji! Dobrý den, Matěji! Krásný den, hezký krásný dobrý Ondřeji!

Řešení. K sestavení pozdravu vezmeme symbol „pozdrav“: pozdrav Nyní nahradíme pozdrav podle pravidla: pozdrav → přívlastek část dne

!

Potom nahradíme přívlastek: přívlastek část dne

! → Dobrý část dne

!

A nakonec část dne: Dobrý část dne

! → Dobrý večer !

Protože teď už máme jen samé terminálové symboly, je pozdrav hotový. Zkusíme stručně ještě poslední pozdrav: pozdrav → přívlastek část dne


, oslovení ! → Krásný část dne

, oslovení ! →

41

Krásný den , oslovení ! → Krásný den , přívlastek přívlastek přívlastek jméno

! → Krásný den , hezký přívlastek přívlastek jméno

den , hezký krásný přívlastek jméno dobrý jméno

! → Krásný

! → Krásný den , hezký krásný

! → Krásný den , hezký krásný dobrý Ondřeji !

Úkol. Najděte postup výroby zbývajících pozdravů.

Úkol. Napište další tři pozdravy, které lze vyrobit podle zadaných pravidel.

Úkol k zamyšlení. Změňte pravidla tak, aby bylo možné psát i „Dobré ráno!“ a „Dobrou noc!“

Shrnutí kapitoly. Pomocí produkčních pravidel popisujeme gramatická pravidla jazyka. Terminálové symboly zapisujeme přímo, na neterminálové aplikujeme produční pravidla.


42

Kapitola e. Tabulky V této kapitole se dozvíte: Jak vytvářet a upravovat databázové tabulky. Které datové typy jsou k dispozici v jazyce SQL. Jak zabránit zadávání nesmyslných hodnot do databáze nastavením omezení.

Klíčová slova této kapitoly: Tabulka Sloupec Datový typ Omezení

Čas potřebný k prostudování učiva kapitoly: 7 hodin

Průvodce studiem. Relační databáze si postavila hlavu, že bude nadále hovořit pouze jakýmsi jazykem SQL. Ten však málokdo ovládá, takže databáze často zlobí, a dělá neplechu. Nyní máte jedinečnou příležitost se databázový jazyk SQL pořádně naučit a databázi její lumpárny navždy zatrhnout. Ať se Vám to podaří.

V relačním databázovém systému se data ukládají do relačních tabulek. Proto výklad jazyka SQL začneme příkazem pro vytvoření tabulky.

Vytvoření tabulky create table → CREATE

TABLE

jméno tabulky

( člen tabulky ( , člen tabulky

)< – N ) Tabulky – table [teibl] vytváříme – create [kriˡeit] příkazem create table, jehož struktura se velmi podobá schématu relace.

Příklad. Ze schématu relace Student(Číslo, Jméno, Příjmení) vytvoříme odpovídající relační tabulku příkazem Relační databáze − jazyk SQL - Studijní opora

43

CREATE TABLE Student ( Cislo INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL )

Použité schéma je zvýrazněno žlutě.

Identifikátory jméno tabulky → identifikátor Každá tabulka v databázi musí mít své jedinečné jméno (identifikátor – identifier [aiˡdentifaiǝr]). Pro identifikátory platí následující obecná pravidla. identifikátor → písmeno ( písmeno | číslice )< – N Identifikátor musí začínat písmenem, za kterým můžou následovat další písmena a číslice. Některé databázové systémy umožňují používat i jiné znaky (např. podtržítko), ale protože mezi nimi panuje značná rozmanitost, doporučuji používat jen písmena a číslice. písmeno → malé písmeno | velké písmeno Písmena dělíme na malá a velká. malé písmeno → a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z Malá písmena jsou uvedena výše. Některé databázové systémy sice umožňují používat i písmena jiných abeced (např. česká písmena s háčky a čárkami), ale rozhodně to nedoporučuji, protože to obvykle přinese více komplikací než užitku. velké písmeno → A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z Pro velká písmena platí v zásadě totéž co pro malá. Některé databázové systémy nerozlišují mezi velkými a malými písmeny. V některých systémech je možné nastavit, zda mají velká a malá písmena rozlišovat. číslice → i | D | \ | _ | c | d | e | f | g | h Všeobecně známé arabské desítkové číslice uvádíme jen pro úplnost.

Sloupce


44

člen tabulky → sloupec | omezení Členem tabulky mohou být sloupce – column [kolǝm] a omezení – constraint [kǝnˡstreint]. sloupec → jméno sloupce datový typ omezení sloupce < – N Definice sloupce se skládá z jména sloupce a datového typu, za kterým mohou následovat omezení. jméno sloupce → identifikátor Jméno sloupce je identifikátor jedinečný v rámci tabulky. Je sice možné, aby v různých tabulkách byly sloupce se stejným názvem a rozdílným významem. Obvyklé a užitečné však je, aby jedno jméno mělo jen jeden význam. Stejně tak není vhodné nazvat sloupec stejně jako tabulku. Databázovým systémům to obvykle nevadí, ale problémy mohou nastat například v některých systémech generujících klientské aplikace.

Datové typy datový typ → číselný datový typ | celočíselný datový typ | znakový datový typ | časový datový typ Datové typy dělíme na číselné, celočíselné, znakové a časové.

Číselné datové typy číselný datový typ → DECIMAL | FLOAT | DOUBLE | REAL | MONEY | SMALLMONEY Číselné datové typy používáme k ukládání reálných – real [riǝl] čísel (ℝ). Datový typ decimal je určen pro výpočty s desetinnými čísly – decimal [desǝml] v desítkové soustavě. Pro počítače je ale mnohem výhodnější počítat s čísly s plovoucí – floating [flǝutiŋ] čárkou v dvojkové soustavě. K tomu jsou určeny typy float, double a real, které se liší přesností uložených čísel. Pro finanční operace, kde je důležitá vysoká přesnost výpočtů v desítkové soustavě použijeme typy money a smallmoney.

Celočíselné datové typy celočíselný datový typ → BIT | TINYINT | SMALLINT | INT Celočíselné datové typy používáme k ukládání celých čísel (ℤ). Liší se rozsahem povolených hodnot. Datový typ bit je reprezentován jedním bitem a může tedy nabývat pouze hodnot < a /. Protože v jazyce SQL není žádný logický datový typ, používá se místo něj typ bit s tím, že < znamená logickou <, tedy nepravdu, a / znamená logickou /, čili pravdu. Datový typ tinyint je osmibitové číslo (/ byte) bez znaménka. Jeho rozsah je tedy od < do Relační databáze − jazyk SQL - Studijní opora

45

1,,. Datový typ smallint je šestnáctibitové číslo se znaménkem. Jeho rozsah je od −1/, do 1/,−/, tedy od −31 7+9 do 31 +7+. Nejpoužívanějším typem je dvaatřicetibitový int opět se znaménkem. Jeho rozsah je od −13/ do 13/−/, tedy od −1 /5+ 593 759 do 1 /5+ 593 75+. S nástupem čtyřiašedesátibitových procesorů se začínají prosazovat i rozsáhlejší datové typy.

Znakové datové typy znakový datový typ → ( CHAR | NCHAR | VARCHAR | NVARCHAR ) ( délka

)

délka → číslice / – N Znakové datové typy se používají k ukládání textů složených ze znaků – character [kærǝktǝr]. Datový typ char ukládá texty s pevnou délkou v osmibitové znakové sadě (ASCII). Datový typ nchar ukládá texty s pevnou délkou v šestnáctibitové znakové sadě (Unicode). Datový typ varchar ukládá texty s proměnnou délkou v osmibitové znakové sadě (ASCII). Datový typ nvarchar ukládá texty s proměnnou délkou v šestnáctibitové znakové sadě (Unicode). Při ukládání kratších textů do sloupců s pevnou délkou bývá zvykem doplnit text na požadovanou délku mezerami. Při použití osmibitové znakové sady je třeba dát dobrý pozor na volbu stejné kódové stránky na klientských počítačích. Osmibitová znaková sada tedy není vhodná do globálních vícejazyčných systémů.

Časové datové typy časový datový typ → DATE | DATETIME | SMALLDATETIME | TIMESTAMP Časové datové typy slouží k ukládání data – date [deit] a času – time [taim]. Jednotlivé typy se liší rozsahem a přesností. Navíc jsou velké rozdíly mezi různými platformami.

Úkol. Vyhledejte v dokumentaci k Vašemu databázovému systému, jaké časové typy používá, jaký mají rozsah a jakou přesnost.

Průvodce studiem. Jde Vám to výborně, takže si malou chvilku odpočiňte než se opět pustíte do SQL. Čekají na Vás omezení. Ne, nebojte se, nikdo Vás v ničem omezovat nebude. Naopak. Vy budete omezovat, co se do databáze smí zapsat a co se nesmí. Databázový systém je především systém, čili pořádek. Není přece možné, aby si každý psal do databáze co chce.


46

Omezení sloupce omezení sloupce → ( CONSTRAINT jméno omezení )< – / ( NOT < – / NULL | DEFAULT

hodnota | PRIMARY

REFERENCES jméno tabulky

KEY | UNIQUE | FOREIGN KEY

( jméno sloupce ) )

Omezení – constraint [kǝnˡstreint] kladou podmínky na hodnoty ukládané do tabulky. Pokud nejsou tyto podmínky splněny, databázový systém neumožní data uložit a ohlásí chybu. Omezení je možné pojmenovat jménem omezení uvedeným za klíčovým slovem constraint. Omezení not null zakazuje vložení prázdné či neplatné – null [nal] hodnoty. V praxi to tedy znamená, že vyplnění sloupce s omezením not null je povinné. Opakem je omezení null, které umožňuje nechat sloupec nevyplněný. Není-li zadáno null ani not null, použije se výchozí omezení, které se však může v různých databázových systémech lišit a někdy je možné jej dokonce nastavit na každém serveru jinak. Je tedy vhodné ke každému sloupci vždy uvést jedno z omezení null a not null. Je-li sloupec primárním – primary [praimǝri] klíčem – key [ki:], použijeme omezení primary key. Sloupec musí být zároveň omezen omezením null. Primární klíč slouží k identifikaci řádku tabulky. Všechny ostatní sloupce jsou na něm funkčně závislé (vazba N:/). Praktickým důsledkem nastavení primárního klíče je, že se hodnoty uvedené ve sloupci primárního klíče nesmí opakovat. Většina databázových systémů vytváří automaticky k primárnímu klíči index, který umožňuje rychlé vyhledávání řádků podle primárního klíče. Některé systémy dokonce fyzicky ukládají řádky v pořadí daném primárním klíčem. Toto omezení lze použít jen na jednosloupcové primární klíče. Obsahuje-li primární klíč více sloupců, je nutné použít omezení tabulky místo omezení sloupce. Omezením unique požadujeme, aby hodnoty ve sloupci byly jedinečné – unique [juˡni:k]. Stejná hodnota se tedy nesmí opakovat na více řádcích. Omezení unique se často používá k realizaci prosté funkční závislosti (vazba /:/). Důležitým omezením je cizí – foreign [forǝn] klíč – key [ki:], kterým realizujeme vazbu mezi tabulkami. Někteří autoři (bohužel často sami tvůrci databázových systémů) ve snaze přiblížit se běžnému uživateli, pro kterého zůstala matematická teorie množin nepochopitelnou záhadou, této vazbě říkají relace (anglicky relationship) a hovoří o relacích mezi tabulkami. Tím však dochází k značnému zmatení pojmů, protože tabulky samy o sobě představují relace (v matematickém slova smyslu). Hovořit o relacích mezi relacemi je pak poněkud nešťastné. Dokonce se můžete setkat s poměrně často rozšířeným názorem, že pokud lze tabulky nějakým způsobem propojovat (tj. vytvářet mezi nimi relace), je daný systém systémem relačním. Tento mylný názor je však v přímém rozporu s prvním pravidlem pro relační databázové systémy formulovaným E.F.Coddem. Cizí klíč je omezení hodnot v daném sloupci na hodnoty uvedené v jiném sloupci obvykle jiné tabulky. Tento sloupec musí být klíčem (často primárním) v této tabulce. Proto se nazývá cizím klíčem, tedy klíčem v jiné (cizí) tabulce. K nastavení cizího klíče se používá omezení foreign key references s odkazem – reference [refǝrǝns] na sloupec (jméno sloupce) v cizí tabulce (jméno tabulky).

Omezení tabulky omezení → ( CONSTRAINT jméno omezení )< – / ( PRIMARY Relační databáze − jazyk SQL - Studijní opora

KEY

( 47

jméno sloupce ( , jméno sloupce )< – N ) | FOREIGN KEY jméno sloupce )< – N ) REFERENCES jméno tabulky jméno sloupce )< – N ) | CHECK

( podmínka

( jméno sloupce ( ,

( jméno sloupce ( ,

) )

Omezení tabulky se používají zejména pro klíče – key [ki:] obsahující více sloupců, jejichž jména se uvádějí do závorky a oddělují čárkou. V případě cizího klíče je nutné uvést i stejný počet sloupců z tabulky, na kterou se tímto klíčem odkazujeme. Dále je možné definovat libovolnou kontrolu – check [ček] pomocí zadané podmínky, která musí být splněna pro všechny řádky v tabulce.

Zrušení tabulky drop table → DROP TABLE

jméno tabulky

Příkazem drop table vynecháme – drop [drop] čili zrušíme celou tabulku. Na rozdíl od příkazu delete, který maže pouze řádky, je zrušena skutečně celá tabulka. Může se stát, že databázový server odmítne tabulku zrušit. Nejčastější příčinou bývají odkazy z jiných tabulek (cizí klíče). Proto je třeba nejprve odstranit cizí klíče a pak teprve tabulku.

Příklad. Vymažte tabulku Student. DROP TABLE Student

Změna tabulky alter table → ALTER

TABLE

jméno tabulky ( ADD sloupec | ADD omezeni |

DROP COLUMN jméno sloupce | DROP CONSTRAINT jméno omezení ) Změnit – alter [oltǝr] strukturu tabulky můžeme příkazem alter table. Nejčastější změnou je přidat – add [æd] nový sloupec – column [kolǝm]. Uživatelé databázového systému totiž často požadují doplnění dalších funkcí k již hotovému systému.

Příklad. Doplňte rozdělení studentů do tříd. Do tabulky Student přidáme sloupec Trida: ALTER TABLE Student ADD Trida CHAR(\) NULL

Přidání sloupce je většinou bezproblémové. Jedinou výjimku tvoří vkládání povinných sloupců (s omezením not null). V případě, že v tabulce už jsou nějaké řádky, což obvykle Relační databáze − jazyk SQL - Studijní opora

48

jsou, je nový sloupec vyplněn výchozími – default [diˡfo:lt] hodnotami, tedy sloupec obvykle bývá prázdný (obsahuje hodnoty null). Pokud jsou ovšem hodnoty null zakázané, není možné takový sloupec vložit. Co s tím? Řešením je zadat nějakou hodnotu jako výchozí: ALTER TABLE Student ADD Trida CHAR(\) NOT NULL DEFAULT 'DA'

Do tabulky lze přidat i omezení, např.: ALTER TABLE Klasifikace ADD CONSTRAINT MezeZnamky CHECK (Znamka BETWEEN D AND d)

Zde je však situace poněkud komplikovanější. V tabulce totiž mohou být nějaká data, která jsou v rozporu s novým omezením. Databázový server zajišťuje, že všechna data v databázi jsou v souladu s omezeními. Nové omezení lze tedy do databáze přidat jen tehdy, jsou-li data v databázi s ním v souladu. Obvykle je před přidáním omezení do databáze nutné upravit data v tabulce nebo je smazat. Sloupec či omezení lze zrušit uvedením klíčového slova drop a jména sloupce nebo omezení, např.: ALTER TABLE Student DROP COLUMN Trida ALTER TABLE Klasifikace DROP CONSTRAINT MezeZnamky

Při rušení sloupce může nastat podobná situace jako při rušení tabulky. Pokud se totiž na sloupec odkazuje jiná tabulka pomocí cizího klíče, nelze takový sloupec zrušit. Řešením je opět zrušit nejprve onen cizí klíč a teprve potom sloupec. Ke zrušení sloupce nebo omezení je nutné znát jeho jméno. Se sloupci není problém, protože každý sloupec své jméno má. Potíž může nastat u omezení, protože je lze zadávat beze jména a jméno mu dá databázový systém automaticky. Bohužel toto jméno se špatně zjišťuje a navíc může být na různých serverech různé. Silně doporučuji při zadávání libovolného omezení vždy uvést jeho jméno. Nikdy totiž dopředu nevíte, kdy budete potřebovat toto omezení zrušit.

Shrnutí kapitoly. Tabulky vytváříme příkazem CREATE TABLE. Každý sloupec tabulky má jedinečné jméno. Údaje v jednom sloupci jsou stejného datového typu. Hodnoty, které lze vkládat do databáze, je možné omezovat. Mezi základní omezení patří primární klíč, cizí klíč a omezení klauzulí CHECK.


49

Kapitola f. Vkládání dat V této kapitole se dozvíte: Jak vkládat data do databáze.

Klíčová slova této kapitoly: Příkaz insert

Čas potřebný k prostudování učiva kapitoly: / hodina

Průvodce studiem. A co takhle číst, psát a počítat? No jistě že Vy už to dávno umíte, ale co relační databáze? Abychom měli co číst, nejdříve ji naučíme psát. A taky gumovat a přepisovat.

Máme-li již vytvořené tabulky, potřebujeme do nich nějak dostat data. K tomu slouží příkaz pro vkládání dat – příkaz INSERT. insert → INSERT VALUES

INTO jméno tabulky

( jméno sloupce ( , jméno sloupce )< – N ) (

( hodnota ( , hodnota )< – N ) | dotaz )

Nové hodnoty – value [vælju:] vložíme – insert [insǝ:t] do – into [intu] tabulky příkazem insert. Kromě jména tabulky musíme uvádět i jména slupců, do kterých budeme zapisovat, protože obecně sloupce v tabulce nemají určené pořadí, ale pouze jména. Vkládaných hodnot musí být stejně jako sloupců a jejich datové typy musí odpovídat typům jednotlivých sloupců. Vložit lze i více řádků najednou požitím dotazu, jehož výsledek (odpověď) se vloží do tabulky. hodnota → číslo | ' text ' Číselné hodnoty píšeme arabskými číslicemi, před záporné hodnoty píšeme mínus, desetinná čísla oddělujeme tečkou. Nikdy nepoužíváme mezeru k oddělování tisíců. Textové hodnoty píšeme mezi apostrofy ('). Obvyklé uvozovky (") se v jazyce SQL používají k zápisu identifikátorů, které obsahují neobvyklé znaky (např. mezery).

Příklad. Vložte studenty do tabulky. INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(D, 'Jan', 'Nový'); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(\, 'Milan', 'Svoboda');


50

INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(_, 'Matěj', 'Opička'); INSERT INTO Student(Cislo, Jmeno, Prijmeni) VALUES(c, 'Ondřej', 'Nezbeda');

Příklad. Vložte zákazníky s dluhem větším než /<<<< Kč do seznamu dlužníků. INSERT INTO Dluznik(Cislo) SELECT Cislo FROM Zakaznik WHERE Dluh>Diiii;

Úkol. Vytvořte tabulku měst a vložte do ní /< měst z Vašeho okolí. V tabulce uveďte PSČ a přibližný počet obyvatel.

Shrnutí kapitoly. Data do tabulky vkládáme příkazem insert. Najednou lze vložit buď jeden řádek s hodnotami uvedenými v klauzuli VALUES nebo i více řádek najednou pomocí příkazu SELECT, který si podrobně vysvětlíme v dalších kapitolách.


51

Kapitola g. Mazání dat V této kapitole se dozvíte: Jak odstranit data z databáze.

Klíčová slova této kapitoly: Příkaz delete Podmínka

Čas potřebný k prostudování učiva kapitoly: / hodina Opačnou operací ke vkládání dat je jejich mazání. V jazyce SQL se k tomu používá příkaz delete.

Příkaz delete delete → DELETE

FROM jméno tabulky ( WHERE podmínka )< – /

Příkazem delete můžeme smazat – delete [diˡli:t] řádky ze zadané tabulky. Buď všechny nebo, uvedeme-li nějakou podmínku, jen ty, které splňují uvedenou podmínku.

Podmínky podmínka → výraz ( = | <> | < | > | <= | >= ) výraz | NOT podmínka | podmínka ( AND | OR ) podmínka | ( podmínka

)

Typickou podmínkou je porovnání hodnot dvou výrazů. Nejpoužívanějším výrazem bývá jméno sloupce. Můžeme porovnávat na rovnost „=“, nerovnost „<>“, menší než „<“, větší než „>“, menší nebo rovno „<=“ a větší nebo rovno „>=“. Podmínky můžeme spojovat logickými spojkami. Negaci vyjádříme klíčovým slovem not, pro logické a použijeme and a pro logické nebo or. Pořadí operací můžeme upravit uzavřením do kulatých závorek.

Příklad. Smažte dlužníky, jejichž dluh je menší než /<<< Kč. DELETE FROM Dluznik WHERE Dluh
Otázky. Jaký je rozdíl mezi příkazy drop a delete? Relační databáze − jazyk SQL - Studijní opora

52

Úkol. Smažte města s PSČ od 393 << do 397 ::.

Shrnutí kapitoly. Data mažeme příkazem delete. V klauzuli WHERE lze uvést podmínku, které řádky mají být smazány. Není-li klauzule where uvedena, relační databázový systém smaže všechny řádky dané tabulky. Prázdná tabulka v databázi zůstane! Pro její úplné odstranění je třeba použít příkaz DROP TABLE.


53

Kapitola h. Úprava dat V této kapitole se dozvíte: Jak změnit data uložená v databázi. Jak provádět výpočty.

Klíčová slova této kapitoly: Příkaz update Výraz

Čas potřebný k prostudování učiva kapitoly: 1 hodiny Někdy je třeba údaje uložené v databázi změnit. Příkaz update zvládá nejen to, ale navíc i výpočty různých hodnot.

Příkaz update update → UPDATE jméno tabulky

SET

jméno sloupce =

výraz ( , jméno sloupce =

výraz )< – N ( WHERE podmínka )< – / Hodnoty ve sloupcích tabulky je možné aktualizovat – update [apˡdeit] příkazem update. Pro každý měněný sloupec je nutné uvést výraz jehož hodnota se do sloupce uloží. Příkaz update upraví celý sloupec najednou. Nechceme-li upravovat všechny řádky, uvedeme podmínku určující, které řádky se mají upravit.

Výrazy výraz → jméno sloupce | výraz ( + | − | * | / ) výraz | ( výraz ) | ( select ) | funkce ( výraz ) Základním výrazem je sloupec. Aritmetické operace používají běžné operátory plus „+“, minus „−“, krát „*“ a děleno „/“. Pořadí prováděných operací je možné upravit pomocí kulatých závorek, které je možné do sebe vnořovat. Jako výraz lze použít i příkaz select uzavřený do kulatých závorek. Odpověď by měla obsahovat jeden řádek s jedním sloupcem, aby bylo jasně dané, co je hodnotou takového výrazu. Dále je možné ve výrazech použít řadu běžně dostupných funkcí.

Funkce Relační databáze − jazyk SQL - Studijní opora

54

funkce → ABS | ACOS | ASIN | ATAN | CEILING | COS | EXP | FLOOR | LOG | LOGDi | POWER | ROUND | SIGN | SIN | TAN Funkce ABS(x) značí absolutní hodnotu |x|. Funkce ASIN(x), ACOS(x) a ATAN(x) jsou inverze goniometrických funkcí SIN(x), COS(x) a TAN(x). Funkce CEILING(x) zaokrouhluje nahoru (ke stropu – ceiling [si:liŋ]), FLOOR(x) dolů (k podlaze – floor [flo:r]). Mocninu ex vypočteme funkcí EXP(x). Pro přirozený logaritmus použijeme LOG(x), pro desítkový LOG/<(x). Obecnou mocninu xy vypočteme funkcí POWER(x,y). K zaokrouhlení – round [raund] na n desetinných míst použijeme funkci ROUND(x,n). Funkce SIGN(x) vrací znaménko – sign [sain].

Příklad. Změňte cenu výrobku s kódem 735/1, na /75, Kč. UPDATE Ceny SET Cena = Decd WHERE Kod = e_cD\d;

Příklad. Vypočtěte ceny s DPH. UPDATE Ceny SET CenaSDPH = CenaBezDPH * (D+SazbaDPH*i.0D);

Úkol. Vytvořte databázi dřevěných kostiček. Kostičky mají tvar kvádru s hranami a, b, c. Vložte do databáze /< kostiček. Vypočtěte objem a povrch kostiček.

Úkol. Vytvořte databázi zaměstnanců. Evidujte výši platu a rok nástupu do zaměstnání. Vložte do databáze /< zaměstnanců. Zaměstnancům, kteří jsou v zaměstnání aspoň 1 roky, zvyšte plat o ,%.

Shrnutí kapitoly. Příkazem update měníme data v databázi. V jeho klauzuli SET jsou uvedeny vzorce pro výpočet nových hodnot ve sloupcích. Je možné přepočítat i více sloupců najednou. Přepočítávané řádky je možné omezit podmínkou uvedenou v klauzuli WHERE.


55

Kapitola Di. Dotazy V této kapitole se dozvíte: Jak získat data z databáze. Co všechno lze zjistit pomocí dotazů. Jak si dotaz uložit k dalšímu použití.

Klíčová slova této kapitoly: Příkaz select Pohled

Čas potřebný k prostudování učiva kapitoly: 1< hodin

Průvodce studiem. Už jste to také slyšeli? Že ne? Tak já Vám to povím, ale nikomu ani muk! Relační databáze je totiž strašně zvědavá. Opravdu − pořád se na něco ptá. Však taky dotazy patří k tomu nejlepšímu, co je v SQL k vidění. Kam se na ně hrabou ostatní příkazy. Jen se podívejte jak jim to sluší! Pokaždé mají na sobě něco nového. Kdepak dotazy, to jsou přes módu praví experti. A kdy se stanete expertem Vy? Určitě hned po přečtení následujících řádek. Věřím Vám a držím všechny palce.

Příkazy uvedené v předchozích kapitolách (insert, update a delete) do databáze pouze zapisovaly. Nyní se podrobně podíváme na příkaz select, který dokáže z databáze číst.

Dotazy dotaz → select (( UNION | INTERSECT | EXCEPT ) select )< – N Data z databáze získáváme pomocí příkazu select. Odpověď databázového systému má formu tabulky. Tabulku v relačních databázových systémech chápeme jako relaci. A protože relace je množinou má smysl s ní provádět množinové operace. Databázový dotaz je tedy výraz složený z příkazů select a množinových operací, kterými jsou sjednocení „∪“ – union [juˡniǝn], průnik „∩“ – intersection [intǝˡsekšn] a rozdíl „−“ čili výjimka – exception [ikˡsepšn].

Příkaz select select → SELECT

řádky sloupce FROM tabulky


where < – / group by < – / order by 56

<–/

Příkaz select je jedinou možností, jak vybrat – select [siˡlekt] data z – from [from] databáze. Je však značně komplikovaný, protože musí umožňovat provádění operací s relacemi, jako je například jejich skládání. Příkaz select začíná klíčovým slovem select, za kterým může následovat omezení počtu řádků – row [rǝu] v odpovědi. Povinnou částí je seznam sloupců – column [kolǝm]. Za klíčovým slovem from následuje tabulka, ze které vybíráme data. Můžeme uvést i více tabulek s využitím některé z relačních operací. Další části jsou nepovinné. Klíčovým slovem where lze uvést podmínku pro výběr řádků do odpovědi. Seskupení umožňující výpočet souhrnných hodnot za více řádků je uvozeno klíčovými slovy group by. Pomocí klauzule order by můžeme stanovit pořadí řádků v odpovědi. řádky → ( TOP

výraz )< – / DISTINCT < – /

Počet řádků v odpovědi lze omezit pomocí klíčového slova top a uvedením počtu horních – top [top] řádek, které chceme zobrazit. Je třeba si uvědomit, že pokud není stanoveno pořadí řádek klauzulí order by (viz dále), není nijak určeno, které konkrétní řádky budou vybrány. Klíčové slovo distinct zajistí výběr odlišných – distinct [diˡstiŋkt] řádek v odpovědi. Tím můžeme zmenšit velikost odpovědi, ale obvykle za cenu časově delšího zpracování dotazu. Opakující se řádky budou tedy z odpovědi odstraněny. Protože z matematického hlediska je tabulka množinou řádek, nemá vícenásobné uvedení stejné řádky žádný význam. sloupce → výraz ( AS

jméno sloupce )< – / ( , výraz ( AS

jméno sloupce )< – / )< – N

K určení sloupce v odpovědi je třeba zadat zejména výraz, jehož hodnoty budou ve sloupci uvedeny. Nejčastějším výrazem je pouhý název sloupce. Ne-li jasné, z které tabulky sloupec pochází, je možné před ním uvést jméno tabulky oddělené tečkou. Pomocí klíčového slova as je možné zadat jméno sloupce tak jak – as [æz] bude uvedeno v odpovědi. Toho využijeme, pokud chceme přejmenovat sloupec. Je-li výraz sloupce složitější než jméno sloupce, je vhodné výsledný sloupec nějak pojmenovat. Odpověď může obsahovat více sloupců. Jejich definice oddělujeme čárkou. tabulky → spojené tabulky (( , | CROSS JOIN ) spojené tabulky )< – N Nejjednodušší operací, kterou lze s databázovými tabulkami provést, je kartézský součin neboli křížové – cross [kros] spojení – join [džoin]. K zápisu použijeme klíčová slova cross join nebo jen čárku. Výsledek bude obsahovat (připomeňte si definici kartézského součinu) všechny možné uspořádané dvojice řádek z obou tabulek. spojené tabulky → tabulka (( INNER |( LEFT | RIGHT | FULL ) OUTER < – / ) JOIN

tabulka

ON podmínka )< – N

Protože tabulky v relačním databázovém systému představují relace, je možné tyto relace také skládat. Připomeňte si proto definici složené relace. Relační databáze − jazyk SQL - Studijní opora

57

V zápise vnitřního – inner [inǝr] spojení – join [džoin] použijeme mezi tabulkami klíčová slova inner join a připojíme spojovací podmínku za klíčové slovo on. Typickou podmínkou je požadavek rovnosti hodnot dvou sloupců, po jednom z každé tabulky. Těmito „spojovacími“ sloupci bývá nejčastěji cizí klíč z jedné tabulky a primární klíč z tabulky druhé. Nad rámec přísně matematického pojetí skládání relací je možné použít i vnější – outer [autǝr] spojení – join [džoin]. V tomto případě jsou do výsledku zahrnuty i ty řádky tabulky, ke kterým neexistuje odpovídající řádek ve druhé tabulce. Neexistující řádek zůstane prázdný (přesněji bude vyplněn hodnotami null). K dispozici jsou tři varianty vnějšího spojení. Left outer join zahrne do odpovědi všechny řádky z první (levé – left [left]) tabulky a jim odpovídající řádky z tabulky druhé. Obdobně right outer join zahrne všechny řádky z druhé (pravé – right [rait]) tabulky a jim odpovídající řádky z tabulky první. Poněkud problematické je použití třetí varianty plného – full [ful] vnějšího – outer [autǝr] spojení – join [džoin]. V tomto případě jsou do odpovědi zahrnuty všechny řádky z obou tabulek. Odpovídající si řádky jsou uvedeny společně, ostatní samostatně. Protože ve všech sloupcích odpovědi se mohou vyskytnout prázdné hodnoty null, nemůže mít odpověď nějaký klíč. Tudíž není příliš jasné, jaký vůbec má taková odpověď smysl. tabulka →( jméno tabulky | jméno pohledu | ( dotaz

) )( AS

jméno tabulky )< – /

Na místě tabulky je možné použít buď skutečnou tabulku uvedením jejího jména, nebo nějaký dotaz. Pokud je takový dotaz uložen jako pohled (viz příkaz create view dále v textu), stačí uvést jeho jméno. Dotaz je možné uvést i přímo do kulatých závorek, hovoříme pak o vnořeném neboli zahnízděném (nested) dotazu. Pomocí klíčového slova as lze zadat název, kterým se budeme na tabulku odkazovat. To je nutné nejen u zahnízděných dotazů v závorkách, ale třeba i tehdy, když chceme použít jednu tabulku vícekrát. where → WHERE podmínka Podmínkou where můžeme omezit výběr na řádky, kde – where [weǝr] je splněna podmínka. Je-li podmínka uvedená za klíčovým slovem where splněna, bude řádek do odpovědi zahrnut. Řádky, pro které není podmínka splněna, nebudou součástí odpovědi. Chceme-li vybrat konkrétní řádek, jako podmínku stanovíme rovnost sloupce primárního klíče a odpovídající hodnoty. group by → GROUP BY

jméno sloupce ( , jméno sloupce )< – N ( HAVING

podmínka )< – / Klauzule group by se používá k seskupení – group [gru:p] řádků. Je třeba uvést jméno sloupce popřípadě sloupců, podle kterých se bude seskupovat. Skupinu tvoří řádky, které mají stejné hodnoty v uvedených sloupcích. Ve výrazech pro sloupce odpovědi na začátku příkazu select je možné používat jen sloupce podle, kterých se sekupuje. Ostatní sloupce mohou být uvedeny pouze jako argumenty agregačních funkcí, kterými jsou sum pro součet – sum [sam], count pro počet – count [kaunt], avg pro průměr – average [ævǝridž], min pro nejnižší – minimum [minimǝm] Relační databáze − jazyk SQL - Studijní opora

58

hodnotu a max pro největší – maximum [mæksimǝm] hodnotu. Skupiny, které mají být zahrnuty do odpovědi, můžeme omezit podmínkou having. V odpovědi se objeví pouze skupiny mající – having [hæviŋ] splněnu danou podmínku. Tato podmínka často obsahuje agregované hodnoty. výraz DESC < – / ( , výraz DESC < – / )< – N

order by → ORDER BY

Ačkoliv pořadí – order [o:dǝr] řádků jakožto prvků relační tabulky nemá z matematického hlediska žádný význam, je možné řádky v odpovědi seřadit užitím klauzule order by následované výrazem či výrazy, podle kterých se bude řadit. Nejčastějším výrazem je jméno některého ze sloupců, většinou klíčového. Chceme-li získat data v klesajícím – descending [diˡsendiŋ] pořadí, připíšeme klíčové slovo desc.

Pohledy create view → CREATE

VIEW

jméno pohledu

AS

select

jméno pohledu → identifikátor Pohledy – view [vju:] jsou uložené dotazy – query [kwiǝri], které vytvoříme příkazem create view s uvedením jména pohledu a dotazu v podobě příkazu select. S pohledy se v relačním databázovém systému pracuje stejně jako s tabulkami. Aktualizace dat je však možná pouze pro nejjednodušší dotazy, které používají jen jednu tabulku a jednoduchou podmínku where. Pohledy založené na složitějších dotazech je možné používat jen pro čtení.

Zrušení pohledu drop view → DROP VIEW

jméno pohledu

Pohled zrušíme uvedením příkazu drop view se jménem pohledu.

Průvodce studiem. Dotazů je škoda na to, abychom je drželi doma. Musí do společnosti, aby se mohly předvést v plné kráse. Tak se rychle připravte, vyrážíme na módní přehlídku nejnovějších databázových modelů. Jako první uvidíte…

Příklad. Zjistěte co obsahuje tabulka Ceny. SELECT TOP Di * FROM Ceny Komodita

Kod

APC

731,

Nazev APC PowerChute PLUS pro Novell NetWare


Dealer

EndUser

VaseCena

Skladem

/++<,7<<<

1<73,+1<<

1<<,,<:7<

X

PartNo AP:<<3

Zaruka 7 Mesíc(ů)

Status Vyprodej

DPH /:

SNC <,<<<<

59

SMC

73:1

APC

75+9

APC

SMC ARCNET 9−BIT BOOTROM FOR NW EU

NETWARE 1./ PROM KIT

7 Měsíc(ů)

1:,<,999<

AP:<<5

/:9,,7<<<

/:1:,19<<

7+<,5<<<

+9<,59<<

+,9,575<

/1/:3,7<<<

/5/:,,,1<<

/3+:,,/37<

1/35,<<<<

15<1,9<<<

135:,<5<<

/,1<<<

/,95<<

/,+/1<

APC PowerChute PLUS pro SCO, Unix, Sun, Solaris, AIX, U

17<,,9<<<

3<3+,/7<<

75+:

APC PowerChute pro Apple

/+<5,<<<<

APC

7595

APC kabel komunikační šedý, Win NT, Novell

APC

77/,

APC SmartCell

SYM

9,+1

WINFAX PRO /<.< W:,/:9/NT/1<<< RET

9,+3

WINFAX PRO /<.< W:,/:9/NT/ 1<<< UPG

9,+5

WINFAX PRO /<.< W:,/:9/NT/ 1<<< Xgrade

/7<<,9<<<

/9
9,+7

WINFAX PRO /<.< W:,/:9/NT/ 1<<< MLP , user

:33+,<<<<

/<,//,5<<<

SYM SYM SYM

/19<,1<<<

/55<,95<<

/:

<,<<<<

7 Mesíc(ů)

/:

<,<<<<

AP:<
7 Mesíc(ů)

/:

<,<<<<

:5<−<<1<

15 Měsíc(ů)

/:

1,<<<<

SMARTCELLW

15 Měsíc(ů)

/:

/<<,<<<<

/1−<<−<1,+<−IN

7 Mesíc(ů)

/:

<,<<<<

/1−<<−<1,+5−IN

7 Mesíc(ů)

/:

<,<<<<

/+7/,,79<

/1−<<−:/++5−IN

7 Mesíc(ů)

/:

<,<<<<

/<1+7,,1<<

/1−<<−:/+73−IN

7 Mesíc(ů)

/:

<,<<<<

/5<9,+/1<

X

Vyprodej

Chceme-li vypsat všechny sloupce, použijeme hvězdičku. Protože nevíme, kolik řádků v tabulce je, raději omezíme počet řádků odpovědi na deset.

Příklad. Kolik je v tabulce Ceny řádek? SELECT COUNT(*) AS PocetRadek FROM Ceny

PocetRadek 19,:9 U agregační funkce count (počet) je jedno, který sloupec počítáme, takže stačí uvést jednoduše hvězdičku.

Příklad. Kolik je komodit? SELECT COUNT(DISTINCT Komodita) AS PocetKomodit FROM Ceny

PocetKomodit :5 Protože chceme každou komoditu počítat jen jednou, uvedeme před ní klíčové slovo distinct.

Příklad. Vypište všechny komodity. SELECT DISTINCT Komodita FROM Ceny

{EUT , CID , KAB , WDC , GVC , UMX , LGD , 3CM , BLK , CRE , HPC , SOD , FAG , 3MM , OLY , NIK , MIC , MIM , OEM , IBP , ACP , VIA , IBM , INW , PAE , TMI , HPE , CPU , SYM , BEN , KON , WNF , LGH , IMA , WEL , CRL , MAX , ZYX , HPN , HPP , FUJ , VER , CAN , ASR , IOM , SOM , TEA , SAM , TOS , EPS , KOM , USR , MTX , APC , BSH , YUS , SMC , XER , SOL , SIE , PRO , GBT , SIM , VIE , COA , ATI , Relační databáze − jazyk SQL - Studijní opora

60

SON , KIN , NEC , HPS , CDI , DAE , LG , MSI , NEO , ADA , NOV , BAT , DLI , ASU , LEX , SAG , GSC , CPR , GSM , OKI , MIN , SEA , PHI , MIO , SOV , MCR , PCO , HER } Opět chceme vypsat každou komoditu jen jednou, takže použijeme distinct.

Příklad. Najděte /< komodit s největším počtem položek. SELECT TOP Di Komodita,COUNT(*) AS PocetPolozek FROM Ceny GROUP BY Komodita ORDER BY PocetPolozek DESC

Komodita COA SYM MIM HPE CPR EPS OLY SOD HPP XER

PocetPolozek 117/ 11<: 1/,5 /91/ /5++ /<:+ 9:: 9,: 9/, +:9

Seskupíme (group by) podle komodit, spočteme položky (count) a seřadíme podle počtu (order by).

Příklad. Zjistěte název nejdražší položky na skladě. SELECT TOP D Nazev FROM Ceny WHERE Skladem='X' ORDER BY VaseCena DESC

Nazev SQL Svr Ent Edtn 1<<, Win31 Eng CD/DVD / ProcLic Vybereme pouze položky skladem (where), seřadíme (order by) je podle ceny v obráceném pořadí od největší k nejmenší (desc) a zobrazíme první z nich (top /).

Příklad. Která komodita obsahuje nejvíce položek v ceně od /<<<< do /1<<< Kč? SELECT TOP D Komodita FROM Ceny


61

WHERE VaseCena>=Diiii AND VaseCena<=D\iii GROUP BY Komodita ORDER BY COUNT(*)

Komodita ASU Vybereme položky z daného cenového intervalu (where), seskupíme (group by) podle komodity, seřadíme podle počtu (order by count) a zobrazíme první z nich (top /).

Příklad. Zjistěte kód nejdražší novinky na skladě. SELECT TOP D Kod FROM Ceny WHERE Status='Novinka' AND Skladem='X' ORDER BY VaseCena DESC

Kod 1/155 Vybereme (where) novinky na skladě, seřadíme (order by) je podle ceny a vybereme první (top /).

Příklad. Zjistěte kód, název a cenu deseti nejlevnějších faxů. SELECT TOP Di Kod,Nazev,VaseCena FROM Ceny WHERE Nazev LIKE '%fax%' ORDER BY VaseCena

Kod 93915, /:,1<+ /5<915 /:,7:3 /:,7:/ /7+57< 5:7<7 /:,7:1 /7+573 /7+575

Nazev 1−port T//E/ IP communications digital voice/fax module Sagem Phonefax inkoustový film pro 3,DS modem Mr.Conect Conexant Voice,PCI faxmodem ,7k Panasonic FAX Náhradní film KX−FA,+E Panasonic FAX Náhradní film KX−FA,5E Philips FAX Cartridge Colour pro IPF /3///+5 WINFAX PRO /<.< WIN:,/:9/NT/W1K/XP UPG G Panasonic FAX Náhradní film KX−FA,,A−E Philips FAX Inkoustový film pro PPF 55//5+/ Philips FAX Inkoustový film pro PPF ,3//,+/

VaseCena //,<<<< /::,<<<< 159,7/7< 5++,3:1< 5+:,3:1< 5::,<<<< ,7/,//1< ,+<,,19< ,++,<,7< ,++,<,7<

Vybereme (where) faxy, seřadíme (order by) je podle ceny a zobrazíme prvních deset (top /<).

Příklad. Relační databáze − jazyk SQL - Studijní opora

62

Kterých deset komodit obsahuje nejméně položek a kolik? SELECT TOP Di Komodita,COUNT(*) AS PocetPolozek FROM Ceny GROUP BY Komodita ORDER BY PocetPolozek

Komodita CID 3MM USR DAE UMX GVC FAG YUS SMC VIA

PocetPolozek / / / / 1 1 3 3 3 5

Seskupíme (group by) podle komodity, určíme počet (count) položek, seřadíme (order by) podle počtu položek a vyberem prvních deset (top /<).

Příklad. Připravte podklady pro ceník zboží komodity „GSC“ včetně ceny s DPH. SELECT Kod,Nazev,VaseCena AS CenaBezDPH,VaseCena*(Dii+DPH)/Dii AS CenaSDPH FROM Ceny WHERE Komodita='GSC'

Kod /55
Nazev Twist kupon 5<<,− Kč Twist kupon 7<<,− Kč Twist kupon /<<<,− Kč Go Voucher Eurocard 1,<,− Kč Go Voucher Eurocard 3,<,− Kč Go Voucher Eurocard ,,<,− Kč Go Voucher Eurocard :,<,− Kč Vodafone kupon ,<< Kč Vodafone kupon /1<< Kč Telefonní karta TRICK 1<<,− Kč

CenaBezDPH 335,/1<< ,<<,79<< 933,9<<< 1<9,:95< 1:1,5<9< 5,9,:79< +:1,<99< 5/+,1,7< /<<<,37<< /7,,,55<

CenaSDPH 3:+,7<19 ,:,,9<:1 ::1,111< 159,7:<: 35+,:7,, ,57,/+/: :51,,95+ 5:7,,357 //:<,5195 /:7,::+3

Vybereme (where) zboží komodity „GSC“ a vypočteme cenu s DPH podle vzorce.

Úkol. Zjistěte kód nejlevnější položky ve výprodeji na skladě.


63

Úkol. Najděte , komodit s největším počtem novinek.

Úkol. Kolik položek je na skladě?

Úkol. Která komodita obsahuje nejvíce top produktů?

Úkol. Zjistěte název nejdražší novinky.

Shrnutí kapitoly. Data z databáze vybíráme příkazem select. Můžeme vybrat jeden či více sloupců, případně výrazy pro výpočet výstupních hodnot. Klauzule FROM obsahuje jména tabulek, ze kterých chceme vybírat data. Tabulky lze spojit vnitřním (inner), vnějším (outer) nebo křížovým (cross) spojením (join). Podmínkou v klauzuli WHERE určujeme, které řádky se mají objevit na výstupu. K výpočtu souhrných hodnot používáme agregační funkce (count, sum, avg, min, max). V kombinaci s klauzulí GROUP BY můžeme tyto hodnoty určovat za různé skupiny řádků. Výsledek je možné ještě filtrovat podmínkou uvedenou v klauzuli HAVING. Pořadí řádků na výstupu je možné ovlivnit klauzulí ORDER BY. Příkaz select je možné uložit jako pohled (view) a dále používat jako tabulku.


64

Kapitola DD. Souvislý příklad V této kapitole se dozvíte: Jak vyřešit složitější databázovou úlohu.


Průvodce studiem. Gratuluji. Už umíte všechno. Nevěříte? Tak si vyzkoušejte následující příklad a uvidíte. Určitě se Vám to povede!

A nyní nás čeká praktický příklad tvorby databázového systému.

Příklad. Vytvořte SQL databázi pro evidenci úvazků učitelů ve škole.

Tabulky Navrhněte databázi v 3. normální formě pro ukládání následujících údajů: • HlavniPredmet – kód hlavního předmětu učitele, funkční závislost: (Ucitel → HlavniPredmet), př. MAT, ELE, … • Hodiny – počet hodin úvazku učitele připadajících na třídu a předmět, funkční závislost: (Trida, Predmet → Ucitel, Hodiny), př. 1, 3, … • HodinyCelkem – celkový počet hodin vyučovaných ve třídě, funkční závislost: (Trida → HodinyCelkem), př. 3<, 31, … • HodinyCelkem – celkový počet hodin učitele, funkční závislost: (Ucitel → HodinyCelkem), př. 1<, 1/, … • Jmeno – jméno učitele, funkční závislost: (Ucitel → Jmeno), př. Luboš, Eva, … • Nazev – slovní pojmenování předmětu, funkční závislost: (Predmet → Nazev), př. Tělesná výchova, Číslicová technika, … • Nazev – označení třídy, funkční závislost: (Trida → Nazev), př. 1.A, 3.A, … • Prijmeni – příjmení učitele, funkční závislost: (Ucitel → Prijmeni), př. Novák, Sudá, … • Predmet – kód předmětu, funkčně nezávislý, př. CJL, PGV, … • PredmetyCelkem – celkový počet předmětů vyučovaných ve třídě, funkční závislost: (Trida → PredmetyCelkem, př. /1, /5, … Relační databáze − jazyk SQL - Studijní opora

65

• Trida – pořadové číslo třídy, funkčně nezávislé, př. ,,, 13, … • Ucitel – pořadové číslo učitele, funkčně nezávislé, př. /<31, 93+, …

Omezení Zadejte následující omezení: • Celkový počet hodin omezte na nejvýše 33 pro třídu a 1/ pro učitele • Celkový počet předmětů ve třídě omezte na nejvýše /1 • Hodiny úvazku omezte na nejméně /

Pohledy Naprogramujte tyto pohledy: • SeznamUcitelu – rozšiřující tabulku učitelů o sloupec PredmetyCelkem obsahující počet předmětů vyučovaných učitelem. Předmět vyučovaný ve více třídách se započítává jen jednou. • PredmetyTridy – vracející seznam předmětů obsahující pořadové číslo třídy, plný název předmětu, jméno a příjmení učitele a počet hodin. • UvazekUcitele – vracející seznam předmětů obsahující pořadové číslo učitele, název třídy, plný název předmětu a počet hodin. • VyukaPredmetu – vracející seznam tříd obsahující kód předmětu, název třídy, jméno a příjmení učitele a počet hodin.

Řešení CREATE TABLE Predmety ( Predmet CHAR(_) NOT NULL PRIMARY KEY, Nazev NVARCHAR(\i) NULL ) CREATE TABLE Ucitele ( Ucitel INT NOT NULL PRIMARY KEY, Jmeno NVARCHAR(Di) NULL, Prijmeni NVARCHAR(Dd) NULL, HlavniPredmet CHAR(_) NULL FOREIGN KEY REFERENCES Predmety(Predmet), HodinyCelkem INT NULL, CONSTRAINT UciteleHodinyCheck CHECK HodinyCelkem <= \D ) CREATE TABLE Tridy ( Trida INT NOT NULL PRIMARY KEY, Nazev NVARCHAR(d) NULL, HodinyCelkem INT NULL, PredmetyCelkem INT NULL, CONSTRAINT TridyHodinyCheck CHECK HodinyCelkem <= __, CONSTRAINT TridyPredmetyCheck CHECK PredmetyCelkem <= D\ ) CREATE TABLE Uvazky ( Trida NOT NULL FOREIGN KEY REFERENCES Tridy(Trida), Predmet NOT NULL FOREIGN KEY REFERENCES Predmety(Predmet), CONSTRAINT UvazkyPK PRIMARY KEY(Trida, Predmet),


66

Ucitel INT NULL FOREIGN KEY REFERENCES Ucitele(Ucitel), Hodiny INT NULL, CONSTRAINT UvazkyHodinyCheck CHECK Hodiny >= D ) CREATE VIEW Pocet AS SELECT Ucitel, COUNT(DISTINCT Predmet) AS PocetPredmetu FROM Uvazky GROUP BY Ucitel CREATE VIEW SeznamUcitelu AS SELECT Ucitele.Ucitel, Jmeno, Prijmeni, HlavniPredmet, HodinyCelkem, PredmetyCelkem FROM Ucitele LEFT OUTER JOIN Pocet ON Ucitele.Ucitel = Pocet.Ucitel CREATE VIEW PredmetyTridy AS SELECT Trida, Predmety.Nazev AS NazevPredmetu, Jmeno, Prijmeni, Hodiny FROM Uvazky INNER JOIN Predmety ON Predmety.Predmet = Uvazky.Predmet INNER JOIN Ucitele ON Ucitele.Ucitel = Uvazky.Ucitel CREATE VIEW UvazekUcitele AS SELECT Ucitel, Tridy.Nazev AS NazevTridy, Predmety.Nazev AS NazevPredmetu, Hodiny FROM Uvazky INNER JOIN Tridy ON Tridy.Trida = Uvazky.Trida INNER JOIN Predmety ON Predmety.Predmet = Uvazky.Predmet CREATE VIEW VyukaPredmetu AS SELECT Predmet, Nazev, Jmeno, Prijmeni, Hodiny FROM Uvazky INNER JOIN Tridy ON Tridy.Trida = Uvazky.Trida INNER JOIN Ucitele ON Ucitele.Ucitel = Uvazky.Ucitel

Shrnutí kapitoly. Ze zadané analýzy funkčních závislotí by navržena struktura databáze. Pomocí příkazů CREATE TABLE vznikly potřebné tabulky včetně omezení kladených na zadávané hodnoty. Požadované dotazy vznikly uložením vhodných SQL dotazů.


67

Kapitola D\. Samostatná práce V této kapitole se dozvíte: Jaké je zadání Vaší samostatné práce.


Průvodce studiem. A je to tady. Po úspěšném zvládnutí souvislého příkladu na Vás čeká závěrečná samostatná práce. S chutí do toho − půl je hotovo. Přeji Vám chuti dvě, abyste měli práci hotovou celou.

A nyní na vás čeká samostaná práce.

Korespondenční úkol. Vytvořte SQL databázi pro evidenci knihovny.

Tabulky Navrhněte databázi v 3. normální formě dle zadané analýzy funkčních závislostí: … • Autor – evidenční číslo autora, funkční závislost: (Kniha → Autor), př. 33, 77 … • CelkovaCena – součet cen knih, které má čtenář zapůjčeny, funkční závislost: (Ctenar → CelkovaCena), př. /1<< Kč, 517 Kč … • Cena – cena výtisku, funkční závislost: (PrirustkoveCislo → Cena), př. 15: Kč, /9: Kč … • Ctenar – evidenční číslo čtenáře, který má zapůjčen daný výtisk knihy, funkční závislost: (PrirustkoveCislo → Ctenar), př. 53, 99 … • DatumZakoupeni – datum zakoupení výtisku, funkční závislost: (PrirustkoveCislo → DatumZakoupeni), př. 13.3.2<<5, /9.6.2<<3 … • Jazyk – jazyk, kterým autor píše, funkční závislost: (Autor → Jazyk), př. česky, anglicky … • Jmeno – jméno autora knihy, funkční závislost: (Autor → Jmeno), př. Karel, Bedřich … • Jmeno – jméno čtenáře, funkční závislost: (Ctenar → Jmeno), př. Jan, Tomáš … • Kniha – číslo knihy, funkční závislost: (PrirustkoveCislo → Kniha), př. 313, ,53 … • Nazev – titul knihy, funkční závislost: (Kniha → Nazev), př. Pán Prstenů, Hobit • PocetVytisku – počet výtisků dané knihy v knihovně, funkční závislost: (Kniha → PocetVytisku), př. 3, /, /< … Relační databáze − jazyk SQL - Studijní opora

68

• Prijmeni – příjmení čtenáře, funkční závislost: (Ctenar → Prijmeni), př. Roubal, Hřebík … • Prijmeni – příjmení autora knihy, funkční závislost: (Autor → Prijmeni), př. Čapek, Hrabal … • PrirustkoveCislo – pořadové číslo zakoupeného výtisku knihy, funkčně nezávislé, př. 3517, 3//, … • RokVydani – rok, kdy byla kniha vydána, funkční závislost: (Kniha → RokVydani), př. /::,, 1<<3 …

Omezení Nastavte následující omezení: … • Počet výtisků jedné knihy v knihovně může být nejvýše /, • Celková cena knih zapůjčených jedním čtenářem může být nejvýše 1,<
Pohledy Naprogramujte tyto pohledy: … • Novinky – seznam pěti nejnovějších knih v knihovně. Rozhodující je datum zakoupení prvního výtisku knihy, zakoupení dalších výtisků nemá na zařazení do novinek vliv. • PrehledCtenaru – seznam čtenářů rozšířený o sloupec PocetKnih obsahující počet knih, které má daný čtenář právě vypůjčené

Shrnutí kapitoly. Dle zadané analýzy funkčních závislostí navrhněte databázi ve 3. normální formě. Vytvořte pohledy dle zadání.


69

Závěr Relační databázové systémy jsou založeny na matematické teorii množin. Základními operacemi s množinami jsou sjednocení, průnik a kartézský součin. Databázová tabulka je tabulkou matematické relace či spíše ještě častěji tabulkou matematické funkce. Teoretické základy relačních databázových systémů vytvořil E.F.Codd v roce /:+<. V jazyce SQL lze snadno realizovat základní množinové operace. Při návrhu databází je vhodné dodržovat zásady vyjádřené normálními formami. K výkladu gramatiky jazyka SQL se používají produkční pravidla. Tabulky včetně sloupců a omezení vytváříme příkazem CREATE TABLE. K vkládání, změně a mazání dat používáme příkazy INSERT, UPDATE a DELETE. Příkaz SELECT je mocným nástrojem pro výběr dat z databáze. Umožňuje vybírat data dle zadaných kritérií z jedné či více tabulek, počítat souhrnné údaje za více řádků a seřadit výsledek. Praktické využití relačního databázového systému je náplní souvislého příkladu a koneckonců i závěrečné samostatné práce.

Průvodce studiem. Může Vám to připadat neuvěřitelné, ale opravdu jste to zvládli. Doufám že čas, který jste strávili nad touto publikací, nebude pro Vás nikdy časem ztraceným. Ať už budete databáze vyvíjet nebo je jen užívat, přeji Vám, aby obsahovaly co nejméně chyb, pokud možno žádné. O totéž jsem se snažil při psaní tohoto textu. Pokud Vám přece jen nějaká chyba způsobí problémy, věřte, že mne to velmi mrzí a předem se omlouvám. A protože studium vlastně nikdy nekončí, třeba zase někdy na shledanou.

Anglicko-český slovníček add alter as average ceiling column constraint count create cross date decimal default delete descending distinct drop exception floating floor foreign

[æd] [oltǝr] [æz] [ævǝridž] [si:liŋ] [kolǝm] [kǝnˡstreint] [kaunt] [kriˡeit] [kros] [deit] [desǝml] [diˡfo:lt] [diˡli:t] [diˡsendiŋ] [diˡstiŋkt] [drop] [ikˡsepšn] [flǝutiŋ] [flo:r] [forǝn]


přidat změnit jako průměr strop sloupec omezení počítat tvořit kříž datum desetinné číslo výchozí smazat klesající odlišný vynechat výjimka plovoucí podlaha cizí 70

from full group having character check identifier inner insert integer intersection into join key left maximum minimum null order outer primary query real reference right round row select sign sum table time top union unique update value view where

[from] [ful] [gru:p] [hæviŋ] [kærǝktǝr] [ček] [aiˡdentifaiǝr] [inǝr] [insǝ:t] [intidžǝr] [intǝˡsekšn] [intu] [džoin] [ki:] [left] [mæksimǝm] [minimǝm] [nal] [o:dǝr] [autǝr] [praimǝri] [kwiǝri] [riǝl] [refǝrǝns] [rait] [raund] [rǝu] [siˡlekt] [sain] [sam] [teibl] [taim] [top] [juˡniǝn] [juˡni:k] [apˡdeit] [vælju:] [vju:] [weǝr]

z plný seskupit mající znak kontrola identifikátor vnitřní vložit celé číslo průnik do spojit klíč levý nejvyšší nejnižší neplatný pořadí vnější primární dotaz reálný odkaz pravý zaokrouhlení řádek vybrat znaménko součet tabulka čas horní sjednocení jedinečný aktualizovat hodnota pohled kde

Česko-anglický slovníček aktualizovat celé číslo

update integer


[apˡdeit] [intidžǝr] 71

cizí čas datum desetinné číslo do dotaz hodnota horní identifikátor jako jedinečný kde klesající klíč kontrola kříž levý mající nejnižší nejvyšší neplatný odkaz odlišný omezení plný plovoucí počítat podlaha

foreign time date decimal into query value top identifier as unique where descending key check cross left having minimum maximum null reference distinct constraint full floating count floor

pohled pořadí pravý primární průměr průnik přidat reálný řádek seskupit sjednocení sloupec smazat součet spojit strop tabulka tvořit

view order right primary average intersection add real row group union column delete sum join ceiling table create


[forǝn] [taim] [deit] [desǝml] [intu] [kwiǝri] [vælju:] [top] [aiˡdentifaiǝr] [æz] [juˡni:k] [weǝr] [diˡsendiŋ] [ki:] [ček] [kros] [left] [hæviŋ] [minimǝm] [mæksimǝm] [nal] [refǝrǝns] [diˡstiŋkt] [kǝnˡstreint] [ful] [flǝutiŋ] [kaunt] [flo:r] [vju:] [o:dǝr] [rait] [praimǝri] [ævǝridž] [intǝˡsekšn] [æd] [riǝl] [rǝu] [gru:p] [juˡniǝn] [kolǝm] [diˡli:t] [sam] [džoin] [si:liŋ] [teibl] [kriˡeit] 72

vložit vnější

insert outer

vnitřní

inner

vybrat výchozí výjimka vynechat z zaokrouhlení změnit

select default exception drop from round alter

znak znaménko

character sign


[insǝ:t] [autǝr] [inǝr] [siˡlekt] [diˡfo:lt] [ikˡsepšn] [drop] [from] [raund] [oltǝr] [kærǝktǝr] [sain]

73

Doporučená a použitá literatura [/] Luboslav Lacko: SQL Hotová řešení, Computer Press, 1<<3 [1] Alfred Tarski: Úvod do logiky, Academia, /:77 [3] Jaroslav Pokorný, Ivan Halaška: Databázové systémy, Karolinum, /::9 [5] Josef Fronek: Anglicko−český slovník, LEDA

Ochranné známky Slova jako Microsoft, Windows, Visual Studio a pod. mohou být registrovanými obchodními značkami ® nebo obchodními značkami ™ svých vlastníků.


74

OBCHODNÍ AKADEMIE ORLOVÁ

Recommend Documents