Umíme to s Delphi: 90. díl – databáze Interbase: vytváříme tabulky

Dnešní díl seriálu je opět věnován databázové platformě Interbise/Firebird. Prozkoumáme společně praktický problém – navrhneme jednoduchou mzdovou agendu, převedeme ji do databázových tabulek a na závěr spustíme databázový skript, který tabulky vytvoří na serveru.
Po kratší odmlce, za níž se omlouvám (státnice jsou státnice), vás opět vítám u nekonečného seriálu věnovaného programování v Delphi. Minulý díl se týkal databázové platformy Interbase/Firebird. Naučil vás nainstalovat příslušný databázový server, ukázal vám jeden z možných administračních nástrojů (konkrétně IBConsole, která se dodává jako integrální součást platformy Interbase/Firebird) a skončil vytvořením první databáze nazvané Platy.

Dnes budeme pokračovat slíbeným návrhem databáze a vytvořením příslušných databázových tabulek. Vzhledem k tomu, že nesmyslných databází jsme již v tomto seriálu vytvořili několik, pokusíme se dnes navrhnout databázi, která bude mít alespoň trochu praktický význam – vyvineme část personální agendy firmy, která bude uchovávat data a informace o zaměstnancích, jejich profesích, příslušných odděleních a samozřejmě o jejich platech.

Situace bude následující (představme si třeba fiktivní firmu zabývající se opravami automobilů, jejíž strukturu si dále popíšeme):

  • z hlediska mzdového účetnictví je podstatné, že firma se dělí na oddělení (např. dílna, marketing, informační technologie, logistika, svařovna apod.)
  • každý zaměstnanec pracuje právě v jednom oddělení (neexistují zaměstnanci, kteří by pracovali ve dvou odděleních zároveň),
  • každý zaměstnanec se specializuje právě na jednu profesi (neexistují zaměstnanci, kteří by byli placeni za vykonávání dvou profesí – dejme tomu, že i kdyby se takový „všeuměl“ vyskytl, bude administrativně zařazen do jediné, převažující profese),
  • každý zaměstnanec dostává plat složený z několika složek:
    • základní mzda
    • osobní ohodnocení (podle pracovní morálky, podle přístupu k zákazníkům a podle loajality k šéfovi)
    • rizikový příplatek (některá oddělení jsou zvýhodněna bonifikací, typicky náročné provozy typu svařovna)
    • profesní příplatek (některé profese jsou zvýhodněny bonifikací, typicky úzce specializované obory)

Není podstatné, nakolik odpovídá popsaná situace realitě v některé existující společnosti. Důležité bude tuto realitu „vymodelovat“ do databáze tak, aby se žádné pravidlo „neztratilo“ a aby byla databáze „normální“, tzn. aby neobsahovala zbytečné duplicity a aby splňovala pravidlo „jedna akce reálného života se promítne pouze do jedné operace s databází“ (např. změní-li se zaměstnanci příjmení, provede se v databázi jen jediná změnová operace, ne „překopání“ tří tabulek).

Navrhujeme strukturu databáze

Jak jsem již několikráte opakoval v předchozích dílech, tento seriál má za cíl naučit váženého čtenáře programovat v Delphi. Jeho cílem není výuka návrhu databází. Bohužel (nebo bohudík) tyto dvě činnosti jsou úzce provázané (přinejmenším při programování databázových aplikací :-))

Ani dnes nezabrousíme do návrhu databáze příliš hluboko. Váhám ovšem, neodradí-li tento přístup čtenáře-začátečníky, kteří by sice rádi v Delphi psali databázové aplikace, ale protože jim chybí základy v databázové problematice, ztratí odvahu a hodí flintu do žita. Pokud tedy nabudete přesvědčení, že základy návrhu databází byste si rádi přečetli i v tomto seriálu, vyjádřete se prosím v diskusním fóru nebo mě kontaktujte na emailové adrese – v takovém případě věnujeme některý z příštích dílů obecně databázím a jejich návrhu.

Nyní se tedy pojďme bez dlouhých úvodů ukázat, jak vypadá databáze splňující uvedené požadavky – viz obrázek:

Na tomto obrázku vidíme logický návrh databáze, tedy návrh ukazující pouze tabulky (relace), vazby, atributy a jejich význam. Není důležité, jakým způsobem byl vytvořen zrovna tento obrázek (byl k tomu použit jeden z rozšířených nástrojů kategorie CASE – Computer Aided Software Engineering). Pro další text článku nebude potřeba, abyste takový obrázek sami vytvářeli – je uveden jen proto, abyste názorně viděli vytvořené tabulky.

Vidíme tedy, že jsme navrhli tři tabulky – Zaměstnanec, Profese a Oddělení. V každé z tabulek je pro lepší orientaci červeně uveden primární klíč – bude se vždy jednat o jedinečný identifikátor (přidělený systémem, nebudeme se o něj vůbec starat), podle kterého bude každý záznam přístupný. V tabulce Zaměstnanec navíc nalézáme dva tzv. cizí klíče. Ty slouží k modelování vazeb mezi tabulkou Zaměstnanci a zbylými dvěma tabulkami.

V celém návrhu jsou dvě vazby:

  • Vazba Zaměstnanec má profesi, která je typu 1:N (jeden zaměstnanec vykonává jen jednu profesi, zatímco jednu profesi může vykonávat více pracovníků). Tato vazba je povinná na straně profese – každý zaměstnanec musí mít nějakou profesi, a nepovinná na straně zaměstnance – můžeme mít v systému definované profese, které momentálně nikdo nevykonává.
  • Vazba Zaměstnanec pracuje v oddělení, která je také typu 1:N (jeden zaměstnanec pracuje jen v jednom oddělení, zatímco v jednom oddělení může pracovat více zaměstnanců). Na tomto místě bych rád zodpověděl typickou začátečnickou otázku – není důležité, zda se tak děje zároveň (v jeden okamžik), tedy zda v oddělení „sedí“ najednou více lidí, ale obecně zda jedno oddělení může čítat více zaměstnanců. Takový je princip vazeb – nezajímá nás dění v jednom okamžiku, ale obecně v průběhu času. O povinnosti výskytu platí analogie s předchozí vazbou – zaměstnanec musí někde pracovat, ale teoreticky může být definováno oddělení, kde zatím nikdo není.

Poznámka: později uvidíme, že tento návrh databáze by bylo možné v určitých ohledech vylepšit – vzhledem ke složitosti „světa“, který modeluje, především pak vzhledem k obtížnému výpočtu mzdy z několika příplatků, by se vyplatilo model určitým způsobem rozšířit.

Než se pustíme do vytvoření databáze v IBConsole, uvedeme si ještě fyzický pohled na datový model. Z tohoto pohledu bude patrné, jaké jsou typy jednotlivých atributů:

Z fyzického pohledu je vidět, že:

  • textové údaje jsou typu Varchar(X), kde X označuje maximální délku řetězce (počet znaků),
  • číselné údaje jsou typicky celočíselné (Numeric X, 0), kde X je maximální počet cifer; jen příplatky budou uváděny jako koeficienty, proto je použito reálného typu (Float)
  • všechny údaje jsou povinné (NN = NOT NULL), některé musí být jednoznačné (U = UNIQUE), identifikátory jsou primárními klíči (PK = PRIMARY KEY) a identifikátor profese a oddělení jsou cizími klíči v tabulce Zamestnanec (PFK = PRIMARY FOREIGN KEY),
  • v názvech atributů a tabulek nepoužíváme ani diakritiku, ani podtržítka, ani jiná písmena než malá.

Tolik tedy k databázi, nyní ji společně vytvoříme v nástroji IBConsole.

Pracujeme s IBConsole

V minulém díle jsme si ukázali, jak spustit IBConsole a vytvořit v ní (prázdnou) databázi Platy. Spusťte tedy IBConsole a připojte se k databázovému serveru (klepněte pravým tlačítkem na Local Server, zvolte Login a zadejte heslo masterkey).

Vidíme, že databáze Platy je již vytvořená, a to (v našem případě) v souboru C:\!\platy.gdb. Připojíme se k ní – klepněte na název databáze (Platy) pravým tlačítkem a zvolte Connect.

Co nás nyní čeká? Přenesení návrhu (který v podstatě existuje pouze v naší hlavě a na obrázku uvedeném výše), tedy databázových tabulek, do této – již vytvořené – databáze.

Poznámka: při „vážném“ návrhu databází se níže popsané činnosti typicky nemusí provádět, neboť pracujeme-li v rozumném CASE nástroji, stačí navrhnout strukturu databáze a všechny další činnosti spočívající ve fyzickém návrhu a vytvoření tabulek udělá CASE nástroj sám. Následující „ruční“ postup provádíme tehdy, nemáme-li k dispozici nástroj, který by to udělal za nás.

Pokud se po připojení k databázi podíváte na obsah IBConsole, možná se polekáte množství údajů, které se v databázi skrývají, viz obrázek. Není však žádný důvod k panice – většinu z možností v této jednoduché databázi vůbec nepoužijeme.

Čemu se však v žádném případě nevyhneme, je vytvoření databázových tabulek. Bohužel, nástroj IBConsole neumožňuje vytvářet tabulky (a vůbec pracovat s databází) v interaktivním grafickém režimu. Co to znamená? Vytvoření tabulky nelze provést v nějakém dialogovém okně, v němž pouze klikáním myší vybíráme potřebné atributy, jejich typy a vlastnosti. Používáme-li IBConsole, je nutné k vytvoření tabulek použít jazyka SQL.

Pokud tento jazyk dobře neovládáte, doporučil bych vám jednu ze dvou následujících cest:

  • zvolit pro administraci databáze jiný nástroj, který grafické administrační rozhraní má (např. výborný IBExpert). V něm můžete snadno vytvořit tabulky, aniž máte valné znalosti SQL.
  • jazyk SQL se naučit. Problémem výše uvedené první cesty totiž je, že i když obejdete neznalost SQL pořízením grafické utility, patrně vaše práce nepovede ke zdárnému cíli, a to především proto, že databáze Firebird/Interbase jsou založeny na SQL a při následném vytváření aplikací v Delphi budeme získávat údaje z databáze právě prostřednictvím jazyka SQL.

Pro vytvoření naší databáze musíme tedy připravit seznam SQL příkazů, jejichž spuštěním dojde k provedení všech potřebných akcí. Tento seznam příkazů (tzv. SQL Script) následně spustíme v nástroji IBConsole.

Nechcete-li psát celý skript sami, můžete využít následujícího programového výpisu. Stačí, když jej zkopírujete a vložíte do IBConsole. V hlavní nabídce IBConsole zvolte Tools – Interactive SQL, otevře se nové okno a do jeho horní části vložte zkopírovaný skript – viz obrázek:

Pak zvolte Query – Execute (nebo klepněte na žlutou ikonu s bleskem). Dotaz se provede a v případě úspěšného dokončení se nevypíše žádná hláška. Příslušný SQL skript je uveden v následujícím výpisu:

Create table zamestnanec  (
idzamest Numeric(6,0) NOT NULL,
idprofese Numeric(6,0) NOT NULL,
idoddeleni Numeric(6,0) NOT NULL,
jmeno Varchar(20) NOT NULL,
prijmeni Varchar(30) NOT NULL,
kosile Numeric(5,0) NOT NULL,
zakladniplat Numeric(6,0) NOT NULL,
ohodnoceni Numeric(5,0) Default 0 NOT NULL,
Primary Key (idzamest)
);

Create table oddeleni  (
idoddeleni Numeric(6,0) NOT NULL,
nazev Varchar(40) NOT NULL,
oznaceni Varchar(10) NOT NULL,
riziko Float Default 0 NOT NULL,
Primary Key (idoddeleni)
);

Create table profese  (
idprofese Numeric(6,0) NOT NULL,
nazev Varchar(40) NOT NULL,
priplatek Float Default 0 NOT NULL,
Primary Key (idprofese)
);


create generator genzamestnanec;
create generator genoddeleni;
create generator genprofese;


create trigger create_zamestnanecno for zamestnanec
  before insert position 0
  as begin
    new.idzamest = gen_id(genzamestnanec, 1);
  end
^

create trigger create_oddelenino for oddeleni
  before insert position 0
  as begin
    new.idoddeleni = gen_id(genoddeleni, 1);
  end
^

create trigger create_profeseno for profese
  before insert position 0
  as begin
    new.idprofese = gen_id(genprofese, 1);
  end
^

set term ^;
set term ;^

Z výše uvedených důvodů (zřejmá nesouvislost jazyka SQL s vlastním Delphi) nebudu výpis nijak vysvětlovat. Ještě jednou vás však vyzvu – pokud máte zájem o jeho vysvětlení (resp. o základní informace týkající se návrhu databází), vyjádřete se v diskusi nebo mi pošlete mail (vkadlec@post.cz).

Důležité je, že v tomto okamžiku jsou databázové tabulky vytvořeny – a co víc, je zajištěno, že při přidání nové položky do kterékoliv ze tří tabulek bude automaticky vygenerován identifikátor, která bude položku zpřístupňovat (to je zajištěno spoluprací tzv. generátorů a triggerů).

Závěrem bych rád podotkl (především pro znalce), že databázové schéma vytvořené předchozím skriptem je poněkud zjednodušené, především proto, že se nezabývá udržením tzv. referenční integrity. Zjednodušeně řečeno to znamená, že např. v okamžiku, kdy smažeme některé oddělení, může zůstat v tabulce Zaměstnanec nějaký pracovník, jehož oddělení (reprezentované identifikátorem ID oddělení) již nebude existovat. V praxi je třeba takovým situacím zabránit, nicméně v našem ukázkovém případě si tím nebudeme komplikovat situaci.

Pokud se nyní podíváte do okna IBConsole, spatříte v položce Tables vytvořené tři tabulky, viz obrázek:

Poklepete-li na některou z nich, uvidíte její strukturu a na dalších záložkách v otevřeném okně si můžete prohlížet i její další vlastnosti, včetně dat (která tam dosud nejsou:)). Právě tímto oknem se budeme zabývat za týden, až se budeme učit přidávat do tabulek data.

A co příště?

Dnešní díl se pokusil demonstrovat, co následuje po vytvoření prázdné databáze – totiž navržení struktury tabulek a vazeb a jejich vytvoření na databázovém serveru. Nyní tedy máme vytvořenu databázi (Platy), která obsahuje tři tabulky (Zamestnanec, Profese a Oddeleni).

Tabulky dosud neobsahují žádná data. Data jsou samozřejmě nezbytnou podmínkou pro používání databáze, proto se vkládáním dat budeme zabývat příště. Vkládání dat je zároveň poslední činností, kterou musíme učinit, než se můžeme vrhnou přímo do Delphi. Jakmile budou v tabulkách potřebná data, budeme se společně učit pracovat s těmi komponentami Delphi, které umožňují přístup k databázím Firebird/Interbase.

    Diskuze (7) Další článek: Hackeři žádají od Microsoftu oficiální linux loader pro Xbox

    Témata článku: , , , , , , , , , , , , , , , , , , , , , , , , ,