6.8. Relace mezi tabulkami

6.8.1. Opakování - knihovna - tvorba, vkládání, hledání v tabulce

• CREATE TABLE název_tabulky (název_sloupce TYP_DAT, ...); -- diagram_create, diagram_sloupce
• BEGIN TRANSACTION; ... COMMIT; -- bezpečné provední příkazů
• CREATE INDEX index_name ON table_name ( column_name ); -- diagram
• INSERT INTO tabulka (sloupec1, ...) VALUES (hodnota, ...); -- diagram
• SELECT seznam_sloupců_pro_výstup FROM jméno_tabulky LIMIT počet_řádků OFFSET od_řádku; -- diagram1, 2
  • SELECT seznam_sloupců FROM jméno_tabulky WHERE podmínka ORDER BY sloupec; -- filtrování podle podmínky např. sloupec like 'B%', řazení podle zvoleného sloupce

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db06.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db06	FileZilla
2	Otevřte v textovém editoru soubor skolni_knihovna.sql a ověřte správnost příkazů a kódování češtiny (musí být UTF-8)	PSPad
3	Přidejte příkazy pro začátek transakce (BEGIN TRANSACTION;) a konec transakce (COMMIT;) a soubor uložte
4	Spusťte příkazový řádek, přejděte v něm do složky \temp\db06, přepněte se do kódové stránky 65001	cmd, cd, chcp
5	Vytvořte ve složce db06 novou databázi db06.sqlite spuštěním: c:\programy\sqlite\sqlite3 db06.sqlite	cmd
6	Ověřte příkazem .databases správnost cesty c:\temp\db06\db06.sqlite	sqlite3
7	Vytvořte novou tabulku načtením souboru skolni_knihovna.sql	.read
8	Vytvořte index pro sloupec autor příkazem: CREATE INDEX index_autor ON knihovna (autor);
9	Vypište knihy spisovatelů Steinbecka a Dreisera seřazené podle autora (nastavte si režim .mode column, .headers on)	select

• databázová schémata se vyjadřují pomocí diagramů UML (Unified Modeling Language) - příklad schématu aktuální tabulky knihovna:

• do tabulky je nutno doplnit evidenci výpůjček čtenářů - přidáme sloupce ctenar a datum_vypujcky
• ALTER TABLE - diagram - přidání sloupce
• UPDATE table_name SET column_name=new_value [, ...] WHERE výraz; -  diagram - změna hodnoty sloupce

10	Přidejte do tabulky knihovna sloupce pro čtenáře a datum výpůjčky podle schématu uml opakováním příkazu: ALTER TABLE knihovna ADD COLUMN xxx text;	sqlite3
11	Nasimulujte evidenci vypůjčení knih autora "Defoe" vložením hodnot čtenáře a datumu (jako čtenáře použijte vlastní jméno bez diakritiky, datum uveďte ve tvaru RRRR-MM-DD): UPDATE knihovna SET ctenar = 'xxx' WHERE autor='yyy'; UPDATE knihovna SET datum_vypujcky = 'RRRR-MM-DD' WHERE autor='yyy';
12	Vypište si vlastní výpůjčky	select

6.8.2. Relace 1:N

• Pokud je u čtenáře potřeba evidovat další údaje (bydliště, poplatek, čárový kód), je nutno změnit strukturu databáze:
  • pro knihy použít původní tabulku - nyní pojmenovanou knihy
  • pro čtenáře vytvořit novou tabulku ctenari
  • výpůjčky evidovat pomocí relací (vztahů, vazeb, linků, odkazů) mezi tabulkou knihy a tabulkou ctenari

Základní typ relace mezi tabulkami:

• 1:N - relace mezi 1 řádkem a více řádky jiné tabulky - obr.
  • např. jeden čtenář má půjčeno více knih (nebo také žádnou nebo jen jednu)
  • další příklady u hudební knihovny - interpret : alba, album : skladby
  • také se používá pojmenování rodič : děti

• relace mezi tabulkami se provádí prostřednictvím primárních klíčů
• použití klíčů (čísel řádků) místo hodnot má výhodu, že se nenaruší vazby na knihy např. při změně jména čtenáře

UML schéma nových tabulek pro evidenci výpůjček včetně relace 1:N:

odkazu do jiné tabulky - zde sloupci id_ctenar v tabulce knihy - se říká foreign key (cizí klíč)

13	Otevřte v textovém editoru soubor skolni_knihovna.sql - nahraďte v příkazech název tabulky z knihovna na knihy, přidejte sloupec id_ctenar a soubor uložte jako knihy.sql	PSPad - knihy.sql
14	Upravte ve stejném souboru příkazy pro vytvoření tabulky ctenari (viz schéma výše), která bude obsahovat tři vymyšlené čtenáře - soubor uložte jako ctenari.sql	PSPad -ctenari.sql
15	Vytvořte v databázi db06.sqlite nové tabulky načtením souborů knihy.sql a ctenari.sql, ověřte existenci těchto tabulek a jejich hodnot	.read, select
16	Evidujte prvnímu čtenáři (id_ctenar=1) výpůjčku všech knih od autora Dickens: UPDATE knihy SET id_ctenar = 1 WHERE autor='yyy';
17	Vypište si výpůjčky tohoto prvního čtenáře	select
18	Podobně zaevidujte výpůjčky ostatním dvěma čtenářům (autory vyberte sami)	update

• Pokud je třeba evidovat konkrétní knihu, použije se v podmínce id_knihy
• Pokud známe jen jméno čtenáře, je třeba nejdříve najít id_ctenar v tabulce ctenari a potom nalezené id_ctenar použít v dotazu do tabulky knihy
• Do tabulky knihy by bylo možno přidat sloupec datum_vypujcky pro evidenci dne výpůjčky

Referenční integrita

• Při smazání čtenáře, který má evidované výpůjčky nastane problém - relace od knihy nebude funkční - dojde k porušení referenční integrity (obr.)
• Programátor databázové aplikace se musí při mazání rodiče s dětmi rozhodnout mezi variantami (zjednodušeně):
  • rodiče s evidovanými dětmi nelze smazat (RESTRICT - vyskočí chybová hláška a operace se neprovede - nic se nesmaže)
  • nebo smaže rodiče i s vymazáním vazeb na děti (vymazáním hodnot foreign keys v tabulce dětí - SET NULL)
  • nebo smaže rodiče i s dětmi (celými řádky - CASCADE)
  • nebo smaže rodiče a děti nechá tak (s chybou - NO ACTION)
• SQLite nabízí podporu referenční integrity pomocí tzv. foreign keys - sloupec se v CREATE TABLE nadefinuje jako REFERENCES - diagram - ukládá se pak do něj kopie primárního klíče ze svazované tabulky - obr.
• Příklad schématu tabulky knihy (při pokusu o smazání čtenáře s výpůjčkou se mazání neprovede)

CREATE TABLE knihy ( id_knihy integer primary key, nazev text, autor text, signatura text, id_ctenar integer REFERENCES ctenari ON DELETE RESTRICT);

• podpora foreign keys se musí zapnout pomocí PRAGMA foreign_keys = 1; (z důvodu kompatibility se staršími verzemi je defaultně vypnutá)
• podobně se musí definovat akce při UPDATE dětské tabulky - další info viz http://www.sqlite.org/foreignkeys.html

6.8.3. Spojování tabulek svázaných relacemi - JOIN

• Problém při hledání výpůjček (dětí) je, že ve výpisu knih není uvedeno jméno čtenáře (rodiče), ale jen jeho id - řešením je spojování svázaných tabulek příkazem JOIN - join_clause, join_operator,  join_constraint
• SELECT ... FROM table1 JOIN table2 ON conditional_expression
  • např. SELECT * FROM knihy JOIN ctenari ON knihy.id_ctenar=ctenari.id_ctenar;
  • obr. - výpis řádků, které mají stejné hodnoty ve sloupcích a a c (množinově jde vlastně o průnik dvou sloupců - obr.)
  • jestliže spojované tabulky mají x a y sloupců (různých), pak výsledná tabulka bude mít x+y sloupců (pozn. to je rozdíl oproti UNION, který nepřidával sloupce, jen spojoval)
  • pozn. ve výsledku jsou zbytečně 2 stejné sloupce
• pokud mají tabulky stejné názvy svazovaných sloupců, pak lze příkaz zjednodušit na
  • SELECT ... FROM knihy JOIN ctenari USING ( id_ctenar );
  • společný sloupec id_ctenar se pak vypisuje jen jednou
• samozřejmě lze k příkazu přidávat podmínku WHERE, řazení ORDER apod.

19	Vypište všechny vypůjčené knihy včetně jmen čtenářů: SELECT * FROM knihy JOIN ctenari USING (id_ctenar);
20	Vypište název knih vypůjčených zvoleným čtenářem (v dotazu použijte jeho jméno): SELECT nazev FROM knihy JOIN ctenari USING (id_ctenar) WHERE jmeno='xxx';

• pomocí příkazu select lze vytvořit novou tabulku příkazem  
  CREATE TABLE název_tabulky AS SELECT ...; - schéma_create

21	Vytvořte tabulku pujcene_knihy, která bude vytvořena příkazem select z úkolu č. 19. Ověřte si poté příkazem .schema strukturu vytvořené tabulky a vypište si všechny řádky tabulky.
22	Vypište z tabulky pujcene_knihy název knih vypůjčených zvoleným čtenářem (v dotazu použijte jeho jméno - viz úkol. č. 20)

Další varianty JOIN - diagramy

• pokud je třeba ve výsledku vypsat i řádky z t1, které nemají odpovídající protihodnotu v t2, použije se LEFT OUTER JOIN
  • SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.a=t2.c;
  • obr. - neexistující hodnoty v nových řádcích jsou NULL
• NATURAL JOIN (automaticky porovnává stejně nazvané sloupce)
• CROSS JOIN (kartézský součin - obr.) - např. SELECT * FROM x CROSS JOIN y; - stejný význam má neuvedení JOIN: SELECT * FROM x, y;

6.8.4. Relace N:N

• Vrácení výpůjčky se realizuje vymazáním hodnoty id_ctenar u příslušné knihy - tím se ale ztratí informace o historii výpůjček
• Pokud by bylo potřeba evidovat v databázi evidovat i starší výpůjčky, je potřeba použít relaci N:N (kniha bude mít navázáno více čtenářů)
• N:N - oboustranně vícenásobné vzájemné relace se realizují pomocí dvou relací 1:N:1 - fyzicky pomocí třetí tabulky (bridge) - v knihovně např. vypujcky

• pozn. na schématu je vidět, že čtenář Jan Kuba má nevrácenou výpůjčku
• jiný př. skupiny : lidé - obr. (do nějaké skupiny patří lidé, lidé patří do různých skupin) - obr.

23	Vytvořte novou databázi knihovna2.sqlite, vytvořte v ní 3 výše uvedené tabulky (využijte knihy.sql, ctenari.sql) a zaevidujte alespoň 1 výpůjčku příkazem INSERT INTO vypujcky (názvy sloupců...) VALUES (hodnoty...);
24	Celou složku db06 uložte na fileserver	Filezilla