Umíme to s Delphi: 76. díl – databáze v Delphi, navrhujeme databázi

V minulém dílu seriálu jsme se seznámili se základními myšlenkami, kterými je protknuta tvorba databázových aplikací v Delphi. Kromě toho jsme si ukázali, jak tyto myšlenky podporují nástroje v integrovaném prostředí Delphi (v IDE). Nyní bychom sice mohli začít programovat „opravdovou“ databázovou aplikaci, ale pouze pod podmínkou, že rozumíme pojmu „databáze“ a víme, jak takovou databázi navrhnout.

Tento seriál je určen zájemcům o programování v Delphi. Hned na úvod tedy musím zdůraznit, že následující odstavce zabývající se návrhem databází budou velmi stručné a budou obsahovat jen zcela nezbytné údaje. Pokud jste s databázemi nikdy nepracovali, doporučuji přečíst některou z publikací, které se věnují přímo databázím.

Co je databáze?

Databáze, jak hovoří jedna z mnoha definic, je množina souborů a jejich popisů, které jsou navzájem v určitém (logickém) vztahu a jsou spravované systémem řízení báze dat. Tím jsme databázi popsali pomocí pojmu „systém řízení báze dat“, což znamená, že nezasvěcenému čtenáři je taková definice úplně k ničemu. Co je tedy ten slavný systém řízení báze dat?

Systém řízení báze dat, který se často označuje jen zkratkou SŘBD (nebo DBMS – Data Base Management System), je programový systém (přesněji databázový server a okolní programové vybavení, které může zahrnovat i různé vývojové nástroje). Tento systém zajišťuje následující akce a činnosti:

• definování struktury dat;
• ukládání dat;
• výběr dat;
• opravu dat;
• komunikaci mezi uživatelem a systémem

Už tedy víme, že Oracle, Access, Interbase, FoxPro a další pojmy neoznačují „databáze“ (to je nepřesné), ale „systémy řízení báze dat“. Databáze je zkrátka jen báze dat, tedy nikoliv okolní programové vybavení.

Systém řízení báze dat jako celek poskytuje například prostředky pro definování dat, pro ukládání, změnu, vymazání a vyhledání dat, dále zajišťuje bezpečnost systému a správu přístupových práv. SŘBD umožňuje také sdílený přístup více uživatelů a obnovu systému po chybě.

Chceme-li získat z databáze data splňující nějakou podmínku, použijeme tzv. dotaz. Možnost klást dotazy na informace uchovávané v databázi je zřejmě jedním z nejsilnějších motivů uživatele pro pořízení databázového systému.

Databázový dotaz by měl být nezávislý na jazykovém vyjádření. Tentýž dotaz je možné realizovat různými prostředky v různých SŘBD (můžete si to představit jako u programovacích jazyků: ve většině lze vyjádřit požadavek na desetinásobné zopakování akce A, nicméně syntaxe cyklu for se napříč programovacími jazyky liší). Dotaz by také neměl být závislý na tom, jak je databáze fyzicky uspořádaná (dotaz na pátý záznam apod.).

Příklad databázového dotazu (vyjádřený prostřednictvím přirozeného jazyka):

Z tabulky Studenti vyber jména studentů, kteří nikdy nedostali horší známku než jedničku.

Dotazovací jazyk (zjednodušeně řečeno) umožňuje uživateli klást databázové dotazy. Zapíšeme-li v dotazovacím jazyku nějaký výraz (při respektování pravidel a syntaxe daného jazyka), dotazovací jazyk převede tento výraz na databázový dotaz.

Jazyk SQL (Structured Query Language, strukturovaný dotazovací jazyk) je snad nejtypičtějším zástupcem současného dotazovacího jazyka. Koncepce jazyka SQL pochází již z roku 1974, původní verze se nazývala SEQUEL. V poslední době je tento jazyk „standardní výbavou“ všech nejpoužívanějších databázových systémů.

Příkazy jazyka SQL se dělí do dvou množin:

• množina příkazů pro definici dat (tedy Data Definition Language),
• množina příkazů pro manipulaci s daty (Data Manipulation Language).

SQL je tzv. neprocedurálním jazykem, tedy pracuje s množinou záznamů a určuje, jaká data vybrat. Pomocí tohoto jazyka tedy říkáme "co chceme udělat", nikoli "jak to chceme udělat". Existují ovšem různá rozšíření jazyka SQL (která jsou různá podle používaného databázového systému), jež jsou procedurální, tedy která zpracovávají data záznam po záznamu a určují, jak data vybrat. Příkladem takového rozšíření budiž PL/SQL (Procedural Language for Structured Query Language) implementovaný v systémech Oracle.

Výuka jazyka SQL se již zcela vymyká tomuto seriálu, navíc na internetu lze najít nespočet seriálů a textů zabývajících se výukou SQL, například zde na Živě. (o knihách nemluvě). Speciálně v Delphi však lze vytvářet poměrně sofistikované databázové aplikace i v případě, že SQL vůbec neumíte, neboť řada komponent (typu Table) po programátorovi žádný dotaz nevyžaduje; ten nastaví své požadavky pomocí vlastností a Delphi vytvoří potřebný dotaz pro přístup do databáze interně.

Jak jsou data v databázích uspořádána?

Databáze můžeme popisovat několika způsoby (tzv. modely databáze). Nejčastějším je tzv. relační model, který lze matematicky vyjádřit soustavou relací. Za autora relačního modelu je oprávněně považován dr. Codd.

Mnohem názorněji jej ale můžeme vyjádřit soustavou tabulek. Tabulky jsou spojeny tzv. relacemi, takže relační model popisuje nejen data, ale také vztahy mezi nimi. Databáze je tedy množina všech (logicky uspořádaných) dat a vztahů mezi nimi. Databáze se skládá z několika tabulek (teoreticky i z jediné tabulky). Tabulka je tvořena řádky a sloupci. Řádek si lze představit jako větu nebo záznam – je to prostě jeden kompletní databázový údaj (informace o jednom zákazníkovi apod.). Sloupce tabulky obsahují atributy téhož typu (např. jeden ze sloupců obsahuje všechna jména, další všechna příjmení apod.)

Příklad databázové tabulky (dejme tomu, že název tabulky je WOMEN):

Jméno	Telefon	Barva vlasů	Věk	Svodobná
Janička	601123321	blond	22	Ano
Evička	601254582	kaštanové	23	Ne
Zdenička	601425875	zelené	14	Ano
Stánička	601254123	NULL	80	Ne

Z této tabulky můžeme pomocí dotazů získávat data splňující požadované podmínky. Uvedeme si několik příkladů:

Dotaz: SELECT * FROM WOMEN;

Význam: vrátí všechny záznamy z tabulky WOMEN

Výsledek:

Janička	601123321	blond	22	Ano
Evička	601254582	kaštanové	23	Ne
Zdenička	601425875	zelené	14	Ano
Stánička	601254123	NULL	80	Ne

Dotaz: SELECT jmeno FROM WOMEN WHERE vek < 25;

Význam: vrátí jména všech žen mladších 25 let

Výsledek:

Janička

Evička

Zdenička

Dotaz: SELECT jmeno, vek, telefon FROM WOMEN WHERE svobodna=ano ORDER BY vek DESC;

Význam: vrátí jméno, věk a telefonní číslo těch žen, které jsou svobodné. Výpis seřadí sestupně podle věku.

Výsledek:

Janička	601123321	22
Zdenička	601425875	14

Navrhujeme rozsáhlejší databáze

Dosud jsme se zabývali situací, v níž databáze obsahuje jedinou tabulku. Upřímně řečeno, taková situace je poměrně unikátní. Obvykle je databáze složena z více tabulek (typicky z velmi mnoha) a teprve jejich vhodná struktura činí databázi databází. Pro návrh databáze s více tabulkami platí celá řada pravidel (či spíše doporučení). Není možné (ani účelné) se zde těmito poučkami podrobně zabývat, odkazuji opět na specializované materiály.

Uvedeme jen opravdu základní informace.

Jak už bylo uvedeno, řádek (který bývá též nazýván záznamem) je kombinace sloupcových hodnot v tabulce. Jinak řečeno: jeden řádek obsahuje jeden údaj z každého sloupce. Každý řádek by měl být jednoznačně identifikovatelný pomocí tzv. primárního klíče (viz dále).

Sloupec je naproti tomu množina údajů jednoho typu. Jinak řečeno: jeden sloupec obsahuje jeden údaj z jednoho řádku. Sloupce (nazývané též atributy) mají své názvy a obsahují hodnoty stejného datového typu.

Primární klíč je sloupec (případně kombinace více sloupců), který slouží k jednoznačné identifikaci každého řádku tabulky. Hodnota primárního klíče musí být v rámci tabulky jedinečná. Primární klíč musí být vždycky uveden (nemůže být NULL).

Cizí klíč je sloupec (případně kombinace sloupců), který je primárním klíčem v jiné tabulce.

Všechny uvedené pojmy by si jistě zasloužily podrobnější popis. Máme však prostor nejvýše pro malý příklad:

Tabulka ZAMESTNANCI

ID zaměstnance	Příjmení	Plat	Oddělení
1	Kadlec	10000	1
2	Novotný	15000	2
3	Moučka	100000	3
4	Jokl	20000	2

Tabulka ODDELENI

ID oddělení	Název	Průměrný plat
1	Literární	10000
2	Vývoj	17500
3	Vedení	100000

V uvedených tabulkách platí:

• sloupec ID zaměstnance je primárním klíčem v tabulce ZAMESTNANCI
• sloupec Oddělení je cizím klíčem v tabulce ZAMESTNANCI
• sloupec ID oddělení je primárním klíčem v tabulce ODDELENI

Než si ukážeme příklady SQL dotazů týkajících se těchto dvou tabulek, vysvětlíme si tzv. relace, neboli vztahy mezi tabulkami.

Relace (vztahy)

Relacemi naznačujeme vztahy mezi entitami (tabulkami) v databázi. Mezi tabulkami A a B je možné vytvořit v zásadě tři typy relací:

• relace 1:1 (čteme „relace jedna k jedné): každý řádek tabulky A má vztah právě s nejvýše jedním řádkem tabulky B. Tento vztah může být povinný či volitelný (může existovat či nemusí). Příkladem takového vztahu mohou být třeba tabulky UZIVATELE (obsahující jen ID uživatele, jeho přihlašovací jméno a heslo) a PODROBNOSTI (obsahující příjmení, věk, adresu a email uživatele).
• relace 1:N (čteme „relace jedna k en“): každý řádek tabulky A má vztah s žádným, jedním nebo více řádky tabulky B (vztah může být opět povínný či volitelný). Tento vztah neplatí naopak: jeden řádek tabulky B má vztah nejvýše k jednomu řádku tabulky A. Příkladem takového vztahu je naše situace uvedená výše: jeden zaměstnanec může pracovat nejvýše v jednom oddělení, naproti tomu v témže oddělení může pracovat více zaměstnanců.
• relace M:N (čteme „relace em k en“): každý řádek tabulky A může má vztah s žádným, jedním nebo více řádky tabulky B (opět povinný či volitelný), stejně tak každý řádek B má vztah s 0, 1 nebo více řádky tabulky A. Příkladem tohoto vztahu je třeba vztah zákazník – výrobek: jeden zákazník si může koupit více výrobků, stejně tak jeden výrobek může být zakoupen více zákazníky. Je nutné podotknout, že z tohoto vztahu obvykle nemáme v databázích příliš velkou radost, neboť jej většinou nelze přímo modelovat tabulkami a musíme si vypomoci definicí další, třetí tabulky. Jedním dechem všem dodejme, že i přesto je vztah M:N velmi častým.

Co s relací M:N?

Ukažme si nyní typické řešení vztahu M:N. Dejme tomu, že máme následující situaci: jako šéf závodní jídelny máme k dispozici několik kuchařů, ale každý z nich je expertem na některá jídla:

• Franta umí svíčkovou, řízky a rizoto
• Josef umí řízky, krupičnou kaši a kuřecí prso
• Standa umí řízky a kuřecí prso
• Jindra umí čaj a kávu

Jak tuto situaci modelovat v databázi? První myšlenkou, na níž však rychle zapomeňme, je splácat cosi takového:

Kuchař	Jídlo 1	Jídlo 2	Jídlo 3
Franta	svíčková	řízky	rizoto
Josef	řízky	krupičná kaše	kuřecí prso
Standa	řízky	kuřecí prso
Jindra	čaj	káva

Tento návrh je zcela špatný: co by se stalo, kdyby se náhodou jeden ze tří umělců naučil ještě zmrzlinový pohár? Kam bychom jej uložili? Navíc slovo „řízky“ je v databázi uvedeno třikrát: zbytečné plýtvání místem. Dále: co když se rozhodneme oživit podnik a přejmenovat „kuřecí prso“ na „prso pana šéfkuchaře“? Budeme měnit spoustu údajů. Jak si vypíšeme všechny kuchaře, kteří umí vařit rizoto? Dalších důvodů bychom našli řadu.

Přejdeme rovnou ke správnému řešení. Situace je taková, že kuchaři a jídla tvoří vztah M:N, neboť jeden kuchař umí více jídel, ale jedno jídlo může být připravováno více kuchaři. Vhodný návrh bude vypadat tak, že tento vztah převedeme na třetí tabulku:

Tabulka JIDLA

ID jídla	Jídlo	Cena
1	svíčková	50
2	řízky	60
3	rizoto	40
4	krupičná kaše	20
5	kuřecí prso	60
6	čaj	10
7	káva	10

Tabulka KUCHARI

ID kuchaře	Jméno	Plat
1	Franta	15000
2	Josef	15000
3	Standa	10000
4	Pepa	5000

Tabulka UMI_UVARIT

ID kuchare	ID jidla
1	1
1	2
1	3
2	2
2	4
2	5
3	2
3	5
4	6
4	7

Ač se tento návrh může jevit jako neefektivní, je nutné si uvědomit, že je relativně flexibilní (změnu jména kuchaře nebo jídla lze provést snadno a na jediném místě, pokud se někdo naučí další jídla, přidání opět ovlivní jen jednu tabulku), tabulku kuchařů, resp. jídel můžeme využít i jinde (pro plán služeb, resp. pro jídelní lístek), každá tabulka obsahuje pouze informace o jednom typu objektů apod. Pokud nevěříte, že takhle může vypadat rozumný návrh databáze, nemohu vás než odkázat na odbornou literaturu.

Na závěr

V dnešním dílu jsme si velmi stručně popsali, co se skrývá pod pojmem „navrhovat databáze“. Už víme, jak jsou data v databázích uspořádány. Rád bych zdůraznil, že se jedná o logické uspořádání: data jsou uložena v tabulkách, řádcích a sloupcích. O fyzickém uložení dat nic nevíme a vůbec nás nezajímá: jedním ze základních důvodů, proč používat databáze, je skutečnost, že se vývojář vůbec nestará o fyzické uložení dat (je nám lhostejné, zda systém řízení báze dat ukládá všechny tabulky do jednoho souboru či každou zvlášť apod.).

Pokud víme, jaká je logická struktura údajů v databázích, není nic jednoduššího, než se pokusit navrhnout svou vlastní. Je ovšem nutné dodržovat některá základní pravidla, bez nichž přinese navržená databáze spíše problémy než užitek. S podrobnými pravidly je možné seznámit se v odborné literatuře, na jejich důkladný popis bohužel nemáme dostatek prostoru (a navíc by ani nezapadal do tohoto seriálu).