programy z cvičení

ako vícehodnotový atribut (použijte implementaci polem). Atribut id_regalu v tabulce kniha je cizí klíč odkazující na tabulku regal, tento cizí klíč bude mít nastaveno povinné členství knihy ve vztahu s regálem. Primární klíče implementujte datovým typem integer, pro ostatní atributy použijte varchar.
CREATE TABLE regal(id_regalu INT PRIMARY KEY,
barva VARCHAR(20));
CREATE TABLE kniha(
id_knihy INT PRIMARY KEY,
nazev VARCHAR(20),
zanr VARCHAR(20),
autori varchar[],
id_regalu INT NOT NULL REFERENCES regal(id_regalu));
Do tabulek vložte nějaká data (nejprve do regálu, pak do knihy, jinak bude porušena referenční integrita). Určitě mějte aspoň tyto barvy regálu: modrý, zelený. V každé z těchto barev mějte přiřazenu aspoň jednu knihu.
INSERT INTO regal VALUES(‘01‘,‘red‘);
INSERT INTO regal VALUES(‘02‘,‘green‘);
INSERT INTO regal VALUES(‘03‘,‘blue‘);
INSERT INTO regal VALUES(‘04‘,‘modry‘);
INSERT INTO regal VALUES(‘05‘,‘zeleny‘);
INSERT INTO kniha VALUES(‘01‘,‘nazev1‘,‘zanr1‘,‘{“jmeno“, “prijmeni“}‘, ‘01‘);
INSERT INTO kniha VALUES(‘02‘,‘nazev2‘,‘zanr2‘,ARRAY[‘jmeno1‘,‘prijmeni1‘], ‘02‘);
K některé knize přidejte další dva autory, jednoho na začátek a druhého na konec seznamu (funkce array_append, array_prepend, array_length). Následně tyto autory zase odeberte (jak ze začátku, tak z konce). Nakonec všechny autory u této knihy vymažte.
UPDATE kniha SET
autori= ARRAY_APPEND(autori,‘jmeno11‘) WHERE id_knihy=01 ;
UPDATE kniha SET
autori= ARRAY_PREPEND(‘prijmeni12‘,autori) WHERE id_knihy=01 ;
UPDATE kniha SET
autori = autori[1:0] WHERE id_knihy=01 ;
Chceme ještě udat rozměr regálu, definujte tedy nový kompozitní datový typ VySiHl se třemi komponentami vyska, sirka, hloubka, všecny budou typu real. K tabulce regal přidejte další sloupec rozmery datového typu VySiHl. Vložte vhodná data v cm. Následně u modrého regálu zvětšte výšku o 20cm (pozor, pokud se chcete v klauzuli update odvolávat na starou hodnotu atributu, musíte název atributu, tj. rozmery, za rovnítkem ozávorkovat, jinak to překladač pochopí jako tabulku).
Vytvořte jednoduchý překladač pro anglické uživatele. Napište příkaz select, který bude při výběru knihy překládat žánr: román=novel, básně=poems, povídky=short stories, sci-fi=sci-fi, vojenské=military, pro ostatní žánry vrátí „unknown genre“.
Přidejte k tabulce regal sloupec kapacita (integer), který bude mít definováno integritní omezení, že musí být větší než nula. Toto IO je splněno, i když je ve sloupci NULL. Když nyní příkazem select vypíšete všechny záznamy, bude ve sloupci kapacita všude NULL. Upravte příkaz select tak, aby pro NULL ve sloupci kapacita vypsal -1.
cv. 9 Rozšíření SQL 2
Použijte tabulky s daty z minulého cvičení (knihy v regálech).
Vytvořte pohled „modre“, který bude uchovávat knihy z modrého regálu. Použijte klauzuli CREATE OR REPLACE. Zkuste aktualizovat pohled, tj. vložit do něj nějaká data. Zkuste v tabulce knihy přeřadit nějakou knihu do modrého regalu a sledujte, co se děje s pohledem.
Pokuste se smazat nějaký regál, na který odkazuje aspoň jedna kniha. Pokud jste na začátku deklarovali foreign key bez dalších specifikací, nemělo by se to podařit. Změňte nyní specifikaci cizího klíče v tabulce kniha tak, aby se při vymazání příslušného regálu vymazaly i záznamy o všech knihách v něm. Nápověda: musíte nejprve odebrat IO cizího klíče (název najdete v popisu tabulky kniha, bude to něco jako "kniha_id_regal_fkey") – viz nápověda k příkazu ALTER TABLE. Následně musíte přidat nové tabulkové IO, které bude obsahovat patřičnou klauzuli.
Vyzkoušejte funkčnost transakcí. Vytvořte transakci, která bude obsahovat dva příkazy: prvním budete měnit barvu modrého regálu na pomněnkovou, druhým se pokusíte vložit do tabulky kniha záznam, který bude odkazovat na neexistující regál (id_regálu, které v tabulce regal neexistuje). Po zadání příkazu COMMIT zkontrolujte, jakou barvu má modrý regál.
Představte si, že se často stává, že vkládáte novou knihu ještě předtím, než vložíte regál, v němž bude kniha uložena. Přitom se nechcete vzdát omezení, které zajišťuje povinné členství knihy ve vztahu s regálem (cizí klíč je NOT NULL). Vyřešte situaci pomocí transakce, která bude obsahovat dva příkazy: prvním vkládáme knihu do dosud neexistujícího regálu, druhým vkládáme nový odpovídající regál. Aby vše fungovalo, musíte ještě nastavit patřičnou vlastnost cizího klíče, která zajistí kontrolu referenční integrity až na konci transakce (nikoli na konci insertu samotného).
Vytvořte novou tabulku police, která bude dědit vlastnosti tabulky regal a navíc bude mít ještě atribut nosnost. Do tabulky vložte nějaká data a to tak, aby některá police byla zelená. Nyní vypište všechny regály a police, které mají zelenou barvu. Dále vypište jen zelené regály bez polic.
cv. 10 Procedurální rozšíření SQL 1
Použijte tabulky s daty z minulého cvičení (knihy v regálech).
Napište SQL funkce, které u zadané knihy (id) přidají zadaného autora na konec nebo začátek seznamu autorů.
CREATE FUNCTION updateKniha(integer) RETURNS VOID
AS $$
UPDATE kniha SET
autori= ARRAY_APPEND(autori,‘nove_prijmeni‘) WHERE id_knihy=$1 ;
$$ LANGUAGE SQL;
Napište SQL funkce, které u zadané knihy (id) odeberou autora z konce nebo začátku seznamu autorů. Dbejte na to, aby funkce správně fungovaly i pro okrajové situace (prázdný seznam autorů apod.).
CREATE FUNCTION deleteKnihaE(integer) RETURNS VOID
AS $$
UPDATE kniha SET
autori = autori[1: (SELECT array_length(autori,1) FROM kniha WHERE Id_knihy=$1)]
WHERE id_knihy=$1 ;
$$ LANGUAGE SQL;
DROP FUNCTION deleteKnihaE(integer);
SELECT deleteKnihaE(01);
Napište SQL funkci, která u zadané knihy (id) odeberou autora ze zadaným indexem (tj. pořadím v seznamu). Dbejte na to, aby funkce správně fungovala i pro okrajové situace (prázdný seznam autorů apod.). Použijte operátor zřetězení.
Prozkoumejte chování funkcí unnest(pole) a generate_subscripts(pole,dim), které jsouce zavolány na pole, rozvinou dané pole v tabulku, respektive vrátí tabulku platných indexů pro dané pole. Vyzkoušejte, jak funkce pracují, pokud jsou obě zapsány za klauzulí SELECT.
Napište SQL funkci autoriTbl, která pro zadanou knihu vrátí tabulku, v níž bude každému autorovi odpovídat řádek se třemi údaji: jméno autora, délka jména autora, index autora v poli. Vyzkoušejte v dotazu jednak za klauzulí SELECT, jednak za klauzulí FROM.
Napište SQL funkci, která zajistí, že se ze seznamu autorů u zadané knihy odstraní autor s druhým nejkratším jménem.
cv. 11 Procedurální rozšíření SQL 2
1. Použijte tabulky s daty z minulého cvičení (knihy v regálech). 2. Pro zprovoznění jazyka plpgsql je patrně nutné napsat příkaz
CREATE LANGUAGE plpgsql;
3. Napište funkci knihado1nf, která vytvoří novou tabulku kniha2, do níž převede tabulku kniha do 1NF: rozbalí seznam autorů tak, že každý autor bude mít svůj sloupec, počet „autorských“ sloupců bude roven délce nejdelšího pole autoři v tabulce. Případné nevyužité hodnoty budou NULL.
Nápověda: * vytvořte kopii tabulky příkazem
CREATE TABLE kniha2 AS TABLE kniha
* k nové tabulce přidejte postupně tolik "autorských" sloupců, kolik je třeba * procházejte záznamy tabulky kniha a pro každou knihu nakopírujte její autory z pole autori do samostatných sloupců v nové tabulce * využijete především dynamické SQL příkazy a FOR cykly Další úkol: Pracujte na Vaší semestrální práci. Vyjděte z funkční analýzy a implementujte nad tabulkami pomocí procedurálních PL/pgSQL zbývající identifikované funkce.
cv. 12 Další dotazy
Použijte tabulky regal, kniha apod. Data nastavte tak, abyste měli aspoň jeden regál, na který neodkazuje žádná kniha. Dále mějte aspoň tři knihy, které mají různé autory, ale stejné názvy. V SQL (bez procedurálního rozšíření) napište následující dotazy:
Vypište jednotlivé regály (id) s počtem knih v nich. Pokud je regál prázdný, vypíše se nula.
Vypište jednotlivé regály (id), pokud je regál prázdný, vypíše se slovo „volný“, jinak se vypíše slovo „obsazený“. Nápověda: použijte podmínku CASE.
Vypište id prázdných regálů. Dotaz realizujte nejméně třemi různými způsoby (způsobů je v PostgreSQL nejméně pět).
Vytvořte pohled „poloprázdné“, který bude obsahovat regály (sloupce s názvy id_regalu, kapacita, pocet_knih), v nichž je uskladněno méně knih, než je polovina jejich kapacity. Pohled tedy bude obsahovat i prázdné regály. Nápověda: použití jakékoli agregační funkce předpokládá, že kromě ní bude za klauzulí SELECT pouze sloupec, podle nějž se tabulka seskupuje. Jestliže chceme mít takové sloupce dva (id_regalu a kapacita), je nutné tabulku seskupit podle dvou sloupců – GROUP BY id_regalu, kapacita.
Vypište tři relativně nejprázdnější regály (tj. regály, které mají nejmenší procento obsazenosti), vč. procenta obsazenosti. Nápověda: vytvoříte nově vypočítávaný sloupec, který pojmenujete (např. „procento“) a budete podle něj řadit.
Vytvořte pohled „dvojice“, který bude uchovávat seznam všech dvojic knih, které mají stejný název, ale různé autory. V seznamu bude název, autoři první knihy, autoři druhé knihy. Fakt, že za každou takovou dvojici knih budou v seznamu dva řádky s opačným pořadím autorů, není na škodu: např. pro knihy Nesmrtelnost od Borgese a od Kundery se objeví řádky (Nesmrtelnost, Borges, Kundera) a (Nesmrtelnost, Kundera, Borges). Nápověda: využijte spojení tabulky sama se sebou.
Pro pokročilé: vezměte pohled „dvojice“ a napište nad ním dotaz, který bude eliminovat dvojí zobrazení jednoho názvu knihy: např. pro knihy Nesmrtelnost od Borgese a od Kundery se objeví jediný řádek (Nesmrtelnost, Borges, Kundera). Nápověda: využijte jednoznačné uspořádání autorů a klíčové slovo DISTINCT.
cv. 13 Procedurální rozšíření SQL 3
Implementujte ISA hierarchii. Mějme entitní nadtyp OSOBA (atributy rodné číslo, jméno, příjmení) a entitní podtypy STUDENT (navíc atribut ročník) a UČITEL (navíc atribut plat). Realizujte třemi tabulkami: OSOBA(rč, jméno, příjmení), STUDENT(rč, ročník) a UČITEL(rč, plat). Pamatujte, že ISA hierarchie znamená pokrytí množiny, tj. entitní podtypy beze zbytku pokrývají entitní nadtyp a navíc jsou vzájemně disjunktní. Tedy osoba je vždy buď student nebo učitel, musí být právě jedno z toho.
Pomocí triggerů ošetřete co nejvíc variant operací nad tabulkami:
pokus o INSERT do tabulky OSOBA nebude povolen, protože by nebylo určeno, zda je osoba student nebo učitel
předchozí trigger a funkci odstraňte (DROP TRIGGER...)
vytvořte systém triggerů následovně
při INSERTu do OSOBY kontrolovat, zda je dané rč přítomno buď ve studentovi nebo v učiteli a pokud ano, vloží se údaje i do osoby, jinak se oznámí chyba (raise exception...) a INSERT se neprovede
při INSERTu do STUDENTA nebo UČITELE se nejprve zkontroluje, zda dané rč není přítomno v osobě, pokud ano, ohlásí se chyba a zápis se neprovede. Pokud dané rč ještě není, provede se následně INSERT do tabulky OSOBA. Tento bod vede na kombinaci row-before a row-after triggeru.
DELETE nad tabulkou OSOBA provede i DELETE v odpovídajícím podtypu
DELETE nad tabulkou STUDENT/UČITEL provede i DELETE v OSOBĚ. Proveďte tak, že pokud se nepodaří DELETE v OSOBĚ, neprovede se ani v ent. podtypu
UPDATE rodného čísla v OSOBĚ se propaguje i do entitního podtypu
UPDATE rodného čísla ve STUDENTOVI/UČITELI se provede i v OSOBĚ, tím bude současně ošetřena unikátnost rodného čísla. Proveďte tak, že pokud se nepodaří UPDATE v OSOBĚ, neprovede se ani v ent. podtypu
Příklady dotazů:
- vypište jména a příjmení všech studentů/učitelů
- vypište jména a příjmení všech osob, s příznakem, zda jsou učitel nebo student