SQL – tvorba tabulek

Druhý díl budu věnovat převážně konkrétní tvorbě databázových tabulek v jazyce SQL. V souvislosti s tvorbou tabulek rozšířím množinu základních pojmů o další, které se používají v jazyce SQL. Dále rozvedu pojem vazby mezi entitami a budu jej aplikovat na tabulky v databázi. Přesně si řekneme, jak se vazby mezi entitami (tabulkami) realizují v jazyce SQL. Doplním informace o datových typech, se kterými budeme v SQL pracovat.

Dříve než se pustíte do četby tohoto článku, doporučuji si přečíst díl první, který se věnuje základům databází.

Jednoduchá tabulka

Jak jsme si už řekli, tabulka nám popisuje nějakou entitu. Skládá se ze sloupců, kterým říkáme atributy a volíme takové vlastnosti, které nás o dané entitě zajímají. Představme si, že máme svou firmu, ve které nám pracují zaměstnanci. O těchto lidech si chceme vést základní osobní údaje. Zajímá nás jméno, příjmení, rodné číslo, adresa (ulice, číslo, město, psč), stav, dosažené vzdělání a zda-li vlastní řidičský průkaz skupiny B.

Datové typy sloupců

Nejprve se podívejme, jak budou vypadat hodnoty jednotlivých položek. Většina položek bude obyčejný text, číslo domu a psč budou celá kladná čísla, položka, zda-li člověk vlastní řidičský průkaz, bude mít logickou hodnotu A nebo N. Následující přehled uvádí základní datové typy v jazyce SQL:

INTEGER celé číslo (-2³¹-1 až 2³¹) SMALLINT celé číslo (-32768 až 32767) NUMERIC(m,n) deset. číslo (m – počet plat. číslic, n – počet des. míst) FLOAT(n) reálné číslo (max. 38 des. míst, n – počet platných číslic) CHAR(n) řetězec znaků do max. délky 255 VARCHAR(n) řetězec znaků promměnné délky (maximální délka je větší, než u typu CHAR) (n udává počet znaků) DATE datum TIME čas

Datové typy jsou místem, kde se mohou různé databázové systémy od tohoto standardu lišit. Např. systém Oracle pro celá čísla používá pouze typ NUMBER(m,n), pro řetězce VARCHAR2(n) nebo LONG. Logický datový typ není v SQL definován, ale většina databázových systémů jej definován má. Nejčastěji se používá BOOLEAN nebo BOOL.

Integritní omezení

Už při návrhu tabulek můžeme rozhodnout o určitých omezeních na jednotlivé položky. Hodnoty do našich vytvořených tabulek budou zpravidla vkládat koncoví uživatelé (sekretářky, technici, správci). Na úrovni SQL serveru lze zajistit některé požadavky, které jsou na jednotlivé položky kladeny. Např. můžeme požadovat, aby rodné číslo bylo vždy zadáno (tzn., že SQL server nedovolí vložit záznam s prázdným rodným číslem). Dále můžeme chtít, aby nějaká položka měla jedinečnou hodnotu (většinou takovou položkou bude pak primární klíč), nebo měla nějakou hodnotu defaultní, v případě, že ji uživatel nezadal. Takovým omezením, která se hlídají a ošetřují na úrovni serveru, se říká integritní. Jaká integritní omezení máme k dispozici?

DEFAULT <hodnota> – použije se uvedená hodnota, pokud uživatel žádnou nezadal NOT NULL – hodnota musí být povinně zadána PRIMARY KEY – primární klíč (jeho hodnota musí být jedinečná) UNIQUE – jedinečná hodnota (unikátní)

Integritních omezeních je více, postupně se podle potřeby zmíním i o dalších.

Vytvoření tabulky

Základním příkazem pro vytvoření databázové tabulky je příkaz CREATE TABLE. Jeho syntaxe je následující:

CREATE TABLE jméno_tabulky (jméno_sloupce typ [integritní omezení], … … )

Výraz v hranatých závorkách znamená, že je nepovinný. Na některé sloupce nemusíme chtít dát žádné integritní omezení. Vraťme se k našemu příkladu. Kromě vyjmenovaných sloupců si zavedeme i sloupec ČÍSLO. Každý zaměstnanec bude mít své jedinečné osobní číslo, které si zvolíme za primární klíč. Příkaz pro vytvoření naší tabulky ZAMĚSTNANEC bude vypadat následovně:

CREATE TABLE zaměstnanec (číslo INTEGER PRIMARY KEY, jméno VARCHAR(10), příjmení VARCHAR(20), rodné_číslo VARCHAR(11) NOT NULL, adresa_ulice VARCHAR(20), adresa_číslo INTEGER, adresa_město VARCHAR(30), adresa_psč NUMERIC(5,0), stav VARCHAR(10), vzdělání VARCHAR(10), řidičský_p CHAR(1) DEFAULT ‚N‘)

Ve většině systémech máme i druhou možnost, jak zapsat primární klíč. To využijeme zejména v případech, kdy jako primární klíč volíme např. dvojici klíčů. Alternativní zápis spočívá v tom, že množinu primárních klíčů uvedeme až v závěru příkazu CREATE TABLE. Předchozí příklad by mohl být ekvivalentně zapsán takto:

CREATE TABLE zaměstnanec (číslo INTEGER, jméno VARCHAR(10), … vzdělání VARCHAR(10), řidičský_p CHAR(1) DEFAULT ‚N‘, PRIMARY KEY (číslo))

Kdyby primárním klíčem byla trojice (číslo, jméno, příjmení), zapsali bychom jednoduše:

… řidičský_p CHAR(1) DEFAULT ‚N‘, PRIMARY KEY (číslo, jméno, příjmení))

Vazby mezi tabulkami

V praxi těžko budeme mít systém, jehož databáze by obsahovala takovou jednoduchou tabulku. Naše firma bude mít určitě různá oddělení, do kterých budou naši zaměstnanci rozděleni, budou plnit různé funkce, od kterých se budou odvíjet jejich platy, apod. Je jasné, že všechny tyto údaje nemůžeme vměstnat do jediné tabulky (viz správný návrh databázových tabulek – 1. díl). Pro evidenci všech těchto údajů budeme tabulek potřebovat více. Navrhněme si tedy následující tabulky. Tabulka ZAMĚSTNANEC bude obsahovat sloupce ČÍSLO, JMÉNO, PŘÍJMENÍ, ROD_ČÍSLO, ČÍS_ODD a FUNKCE. Druhá tabulka ODDĚLENÍ bude obsahovat ČÍSLO a NÁZEV. Třetí tabulka se bude jmenovat PLATY, která bude mít sloupce ČÍSLO, FUNKCE a PLAT. Pro lepší pochopení následujícího výkladu si představme, že v tabulkách máme následující údaje:

ZAMĚSTNANEC: ČÍSLO JMÉNO PŘÍJMENÍ ROD_ČÍSLO ČÍS_ODD FUNKCE 1 jan novák 751015/2352 10 127 2 petr nový 780401/4421 15 121 3 jan nováček 650906/1566 10 156 4 jiří novotný 740205/3566 20 127 ODDĚLENÍ: ČÍSLO NÁZEV 10 studovna 15 centrála 20 počítačový sál PLATY: ČÍSLO FUNKCE PLAT 121 vedoucí 21500 127 technik 15000 156 správce 17500

Nyní se podrobněji podíváme, jaké informace nám tyto tři tabulky poskytují. Podíváme-li se na první tabulku ZAMĚSTNANEC, na řádek s číslem zaměstnance 1, vidíme, že jeho funkce ve firmě má číslo 127. Z tabulky PLATY okamžitě vyčteme, že funkce s číslem 127 je technik. Dále v prvním řádku tabulky ZAMĚSTNANEC vidíme, že Jan Novák pracuje na oddělení číslo 10. Z tabulky ODDĚLENÍ zjístíme, že oddělení číslo 10 je studovna.

Reprezentace vazeb 1:N (1:1)

Není tedy pochyb, že všechny tři tabulky spolu nějakým způsobem souvisí. Všechny tři nám dohromady uchovávají všechny informace, které potřebujeme. Primárními klíči v každé tabulce našeho příkladu je vždy sloupec ČÍSLO. Všimněte si, že hodnoty v tomto sloupci jsou v rámci každé tabulky jedinečné. Mezi těmito třemi tabulkami existují celkem dvě vazby. První vazbu můžeme najít v první tabulce ve sloupci ČÍS_ODD, jehož hodnoty se odkazují do tabulky ODDĚLENÍ. Druhá vazba je dána v první tabulce sloupcem FUNKCE, jehož hodnoty se odkazují do tabulky PLATY. Pokud tedy chceme vytvořit správně všechny tři tabulky, musíme SQL serveru při jejich zakládání o těchto vazbách říct.
Sloupec ČÍS_ODD v tabulce ZAMĚSTNANEC se nazývá cizí klíč. Cizí klíč nám (spolu s primárním klíčem) zprostředkovává vazbu mezi tabulkami. Jeho hodnota pak odpovídá v jiné tabulce (která je odkazovaná) hodnotě primárního klíče. Druhým cizím klíčem v tabulce ZAMĚSTNANEC je sloupec FUNKCE. Ten odkazuje na hodnoty sloupce ČÍSLO v tabulce PLATY.

Obě vazby jsou typu 1:N. V našem příkladu to znamená, že jeden zaměstnanec pracuje právě na jednom oddělení, ale na jednom oddělení pracuje více zaměstnanců (odtud tedy poměr oddělení:zaměstnanec = 1:N). Dále jeden zaměstnanec vykonává jednu funkci (ale jedna funkce může být vykonávána více zaměstnanci, např. můžeme mít ve firmě 10 programátorů).

Jak tedy pomocí příkazů jazyka SQL vytvořit tyto tři tabulky se správnou „provázaností“? Cizí klíč je speciální případ integritního omezení, neboť hodnotu položky samotné nám vůbec neomezuje, pouze popisuje vazbu mezi tabulkami. Syntaxe zapisu cizího klíče je následujicí:

FOREIGN KEY (jména sloupců) REFERENCES jméno_odkazované_tabulky (její primární klíče)

Na chvíli se zastavme nad postupem, kterou tabulku vytvořit jako první, kterou jako druhou a kterou jako poslední. Kdybychom se nejprve pokusili vytvořit tabulku ZAMĚSTNANEC, server by nám nahlásil chybu (není to úplně pravda, některé databázové systémy by nám umožnily vytvořit tabulku ZAMĚSTNANEC jako první). Odkazovali bychom totiž na tabulku, která ještě v systému neexistuje. Takže správný postup spočívá nejprve ve vytvoření tabulek, na které se budeme odkazovat a nakonec vytvoříme tabulky, které se odkazují na jiné. Je nutné, aby zúčastněné sloupce měly stejný datový typ. Následuje zápis v SQL pro vytvoření uvedených tří tabulek: (včetně správného pořadí vytvoření)

CREATE TABLE oddělení (číslo INTEGER, název VARCHAR(20), PRIMARY KEY (číslo)) CREATE TABLE platy (číslo INTEGER, funkce VARCHAR(10), plat FLOAT(10) DEFAULT 8000, PRIMARY KEY (číslo)) CREATE TABLE zaměstnanec (číslo INTEGER, jméno VARCHAR(10), příjmení VARCHAR(20), rodné_číslo VARCHAR(11) NOT NULL, čís_odd INTEGER, funkce INTEGER, PRIMARY KEY (číslo), FOREIGN KEY (čís_odd) REFERENCES oddělení (číslo), FOREIGN KEY (funkce) REFERENCES platy (číslo))

Vazbu 1:1, i když je speciálním případem vazby 1:N, nemusíme reprezentovat pomocí cizích klíčů. Vezměme si např. člověka a jeho rodné číslo. Zde evidentně nastává vazba 1:1, neboť každý člověk má právě jedno rodné číslo a jedno rodné číslo náleží právě jednomu člověku. Tuto vlastnost jsem schovali rovnou do tabulky, tj. rodné číslo je přímo jedním z atributů zaměstnance a není třeba pro rodné číslo vytvářet samostatnou tabulku, která by měla sloupce ČÍSLO_ZAM a ROD_ČÍS. Pokud byste takovou tabulku vytvořili, chyba by to nebyla, ale je to zbytečné.

Vazba M:N

Vazba M:N se realizuje opět pomocí cizích klíčů. Je tu ale jedno rozšíření oproti předchozímu případu. Vezměme si situaci, že každý zaměstnanec může vykonávat obecně více funkcí (a jedna funkce může být vykonávána více zaměstnanci). Máme tedy tabulku ZAMĚSTNANEC se sloupci JMÉNO, PŘÍJMENÍ a ROD_ČÍS. Druhou tabulku nazvěme FUNKCE, která bude mít sloupce ČÍSLO a NÁZEV. K realizaci této vazby nám ovšem nebudou tyto dvě tabulky stačit. Potřebujeme ještě jednu, nazvěme si ji VÝKON_FUNKCE. Ta bude obsahovat dva atributy – prvním bude zaměstnanec a druhým bude funkce, kterou vykonává. Do takové tabulky jsme schopni zanést informace, že jeden zaměstnanec vykonává více funkcí (bude v tabulce VÝKON_FUNKCE uveden na více řádcích) a zároveň budeme schopni vyčíst, jací lidé vykonávají konkrétní funkci. Oba atributy v této pomocné tabulce budou reprezentovány pomocí čísla zaměstnance a čísla funkce. Mějme tyto tabulky:

ZAMĚSTNANEC: ČÍSLO JMÉNO PŘÍJMENÍ ROD_ČÍSLO 1 jan novák 751015/2352 2 petr nový 780401/4421 3 jan nováček 650906/1566 4 jiří novotný 740205/3566 VÝKON_FUNKCE: ČÍSLO_ZAM ČÍSLO_FUN 1 127 2 121 3 156 4 127 2 127 FUNKCE: ČÍSLO NÁZEV 121 vedoucí 127 technik 156 správce

Podívejme se na obsah těchto tabulek, jaké informace nám sdělují. V tabulce VÝKON_FUNKCE na druhém řádku vyčteme, že zaměstnanec číslo 2 (Petr Nový) vykonává funkci číslo 121 (vedoucí) a na posledním řádku vyčteme dále, že tentýž zaměstnanec vykonává také funkci číslo 127 (technik). Konkrétně zaměstnanec Petr Nový vykonává v naší firmě vedoucího a je zároveň technikem. Když se na tabulku podíváme z druhého pohledu, zjístíme, že funkci číslo 127 (technik) vykonávájí celkem 3 lidé, a to zaměstnanec číslo 1, 2 a 4 (jejich jména bychom vyčetli z tabulky ZAMĚSTNANEC).

Sloupec ČÍSLO_ZAM, v tabulce VÝKON_FUNKCE, je cizím klíčem a odkazuje do tabulky ZAMĚSTNANEC, sloupec ČÍSLO_FUN je cizím klíčem odkazujícím do tabulky FUNKCE. Jak je to s primárním klíčem v tabulce VÝKON_FUNKCE? Jedinou možnost, kterou máme, je za primární klíč zvolit oba sloupce, tj. dvojici (ČÍSLO_ZAM, ČÍSLO_FUN). Samozřejmě bychom tabulku VÝKON_FUNKCE mohli rozšířit o sloupce např. DATUM_OD, kde by na každém řádku pro zaměstnance a funkci byla informace, kdy začal zaměstnanec příslušnou funkci vykonávat. SQL příkazy pro vytvoření těchto tří tabulek jsou tyto:

CREATE TABLE zaměstnanec (číslo INTEGER, jméno VARCHAR(10), příjmení VARCHAR(20), rodné_číslo VARCHAR(11) NOT NULL, PRIMARY KEY (číslo)) CREATE TABLE funkce (číslo INTEGER, název VARCHAR(10), PRIMARY KEY (číslo)) CREATE TABLE výkon_funkce (číslo_zam INTEGER, číslo_fun INTEGER, PRIMARY KEY (číslo_zam, číslo_fun), FOREIGN KEY (číslo_zam) REFERENCES zaměstnanec (číslo), FOREIGN KEY (číslo_fun) REFERENCES funkce (číslo))

Možná jste si všimli, že sloupce ČÍSLO_ZAM a ČÍSLO_FUN tvoří ve dvojici primární klíč a zároveň jsou cizími klíči. Chyba to samozřejmě není, obecně v SQL neexistuje žádný důvod, proč by sloupec, který tvoří primární klíč, nemohl být zároveň cizím klíčem.

To by o základní tvorbě tabulek v SQL bylo zhruba vše. Možná jsem zapomněl jenom úvést, že při psaní SQL příkazů většinou nezáleží na velikosti písmen.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.