Informační systémy ve zdravotnictví

Skripta

Podmínky

Zápočet 45 bodů
Zkouška 55 bodů (písemná)

Test návrh databáze 10 bodů (min. 5bodů)
Test SQL 15 bodů (min. 7bodů)
Test funkční a dynamická 10 bodů (min. 5bodů)
Práce v NIS Akord 10 bodů (min. 5bodů)
Zkouška 55 bodů (min. 28bodů)

Studijní materiály

Ing. Petr Lukáš - prezentace z hodin zde.
dbedu.cs.vsb.cz - studijní materiály k databázovým předmětům.
http://158.196.157.193/other/INZ/ - přístup k materiálům ze školní sítě
Matroše z Barborky - přístup k materiálům mimo školní síť
Výuková videa
SQL Developer Data Modeler - zde je možno stáhnout data modeler od Oraclu. Bez registrace to z oficiálních stránek nestáhnete, ale dá se stahovat i odjinud, třeba z uloz.to, nebo dokonce snad i z dbedu. Po zapnutí 2 x kliknout v levé navigaci na Logical Model, zatrhnout visible a potvrdit. Tím se zobrazí Logický model do kterého je možné nakreslit tabulky a vazby mezi nimi. Datové typy atributů (sloupců) tabulky většinou vybíráme buď z předem definovaných domén, nebo z logických typů (integer, varchar ...) a mlžeme vybrat, zdali je povinný (mandatory). V logickém schématu je třeba tabulkám nastavit Primary UID (unique identifier), neboli primární klíč a v nastavení vazby (2x klik na ni) jej pak vybrat. Ikonou nahoře, nebo klikem pravým tlačítkem na Logical Model a výběrem Engineer to Relational vytvoříme z logického schématu relační. Dokumentace k datamodeleru - 1 lekce instalace a seznámení s prostředím a 2 lekce je rychlokurz vytvoření malé databáze pro knihovnu.
SQL Server Management Studio - odkaz na stažení, taky je třeba mít nainstalovaný SQL Server a k němu se připojovat, pokud se chcu k něčemu připojit doma. Nějáký pokec o tom co stáhnout. Návod na Management studio na itnetwork.cz.
VPN - jak nainstalovat VPN, případně tady.
w3schools.com/sql - úvod do SQL a jiných jazyků používaných pro tvorbu webových aplikací.
linuxsoft.cz - SQL.
Relační Databáze
Entity Relationship Model
SERVER K PŘIHLÁŠENÍ: dbsys.cs.vsb.cz\student. Heslo na mailu.

Poznámky

Data Definition Language (DDL) - create, alter, drop
Data Manipulation Language (DML) - select, insert, update, delete

Konzistence - informace jsou všude v databázi shodné. Např. opravím hodnotu na jednom místě, na druhém ji zapomenu opravit a data už nejsou konzistentní.
Integrita - data jsou aktuální. Plně integritní data jsou tedy konzistentní. Konzistentní data nemusí být integritní (všude stejné hodnoty, ale všude staré).

MySQL příkazy

• SHOW - slouží pro zobrazení informací o databázích a tabulkách. SHOW DATABASES - vypíše seznam databází. SHOW TABLES - vypíše seznam tabulek v aktuálně zvolené databázi. SHOW TABLES FROM jmeno_databaze - zobrazí seznam tabulek ze zvolené databáze. SHOW COLUMNS FROM jmeno_tabulky - vypíše info o sloupcích zadané tabulky do tabulky se sloupci Field, Type, Null, Key, Default, Extra. SHOW TABLE STATUS FROM jmeno_databaze - info o jednotlivých tabulkách zvolené databáze. SHOW KEYS FROM jmeno_tabulky, SHOW INDEX FROM jmeno_tabulky - info o klíčích a indexech tabulky.
• USE jmeno_databaze - vybere databázi se kterou chceme pracovat. SELECT DATABASE() - vypíše název zvolené databáze se kterou pracujeme.
• CONCAT() - spojování řetězců, např. SELECT CONCAT(date, ' ', time) AS DATETIME FROM shoutbox spojí date a time v jeden řetězec a oddělí je mezerou.
• UPPER(), LOWER(), SQRT(), AVG(), SUM(), MIN() - jak názvy napovídají, převod na velké a malé písmena, odmocnina, průměr, suma, minimum.

Datum a čas v Oracle

DROP TABLE film_test CASCADE CONSTRAINTS ; CREATE TABLE film_test ( id_film INTEGER NOT NULL , datum_vydani DATE , delka_filmu TIMESTAMP(0) /* sekundy s nula desetinymi misty (asi) */ ) ; ALTER TABLE film_test ADD CONSTRAINT film_test_PK PRIMARY KEY ( id_film ); insert into film_test values (1, sysdate - 1, systimestamp); /* datum vcera, aktualni datum a cas */ insert into film_test values (2, '01.02.15', '01.01.01 02:15:25'); SELECT TO_CHAR (delka_filmu, 'hh:mi:ss') FROM film_test; /* vybere jenom cas z timestampu */ SELECT sysdate, systimestamp FROM dual; ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD'; /* zmena formatu data tak jako v mysql */ ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'HH:MI:SS'; /* zmena formatu timestampu date a time */

SQL příkazy z cvičení

uzivatel(login, rok_narozeni, mesto)
vyrobek(vID, jmeno, aktualni_cena)
koupil(login, vID, rok, cena)

1. Vypište všechny uživatele, kteří mají rok narození mezi lety 1980 a 1990, nebo mají sudý rok narození

SELECT   login                                  // vyber login
FROM     uzivatel                               // z tabulky uzivatel
WHERE    rok_narozeni BETWEEN 1980 AND 1990     // podminka intervalu roku narozeni
   OR    rok_narozeni%2=0                       // podminka sudeho roku

Nejprve jsme pomocí klauzule SELECT definovali, které sloupce chceme vybrat. V tomto případě nám stačí sloupec s loginem. Poté jsme povinnou klauzulí FROM zvolili tabulku, ze které chceme zvolené sloupce načítat. Za volitelnou klauzulí WHERE jsme definovali pomocí klauzule BETWEEN první podmínku oznamující, že rok narození je v uzavřeném intervalu <1980;1990> (pozn. pokud bychom chtěli vybrat všechno mimo tento interval, stačilo by napsat NOT BETWEEN). Podmínku jsme ještě dále rozšířili výběrem sudých roků narození, u nichž jsme využili, že po vydělení dvoujkou mají nulový zbytek.

2. Vypište, ve kterých letech byl alespoň jednou prodán výrobek se jménem lapma

SELECT   DISTINCT rok
FROM     koupil, vyrobek
WHERE    koupil.vID=vyrobek.vID
  AND    jmeno='lampa'
ORDER BY rok

Protože se v tabulce koupil nenalézá jméno výrobku, ale jenom jeho vID, můsíme provést kartézský součin (kombinace každého řádku s každým) tabulky koupil a tabulky vyrobek, ve které se už jméno výrobku nachází. My ovšem chceme jen ty kombinace, které spolu souvisí pomocí atributu vID tzn., že vybereme jen ty řádky, ve kterých se obě vID rovnají, protože nechceme vybrat kombinace koupeného výrobku s úplně všemi jinými výrobky, ale pouze kombinace koupeného výrobku přímo s tímto výrobkem. Teď už máme ve vybraných záznamech atribut jméno, a tak už jen zbývá vybrat ty řádky, které obsahují jméno 'lampa'. Protože lampa se mohla v jednom roce prodat vícekrát, mohli bychom mít jeden rok vypsaný vícekrát. Tomu zabráníme pomocí klauzule DISTINCT, která v tomto případě zobrazí každý rok maximálně jednou. Pro přehlednost jsou zde roky ještě seřazeny od nejnižšího po nejvyšší pomocí nepovinné klauzule ORDER BY, která defaultně třídí data vzestupně, takže je to to samé jako kdybych napsal ORDER BY rok ASC (ASC - Ascend=vzestupně, DESC=Descend - sestupně).

3. Vypište všechny dvojice (login, jmeno), kde uživatel s daným loginem je z Ostravy a provedl nákup předmětu s daným jmenem

SELECT   uzivatel.login,vyrobek.jmeno
FROM     koupil, vyrobek, uzivatel
WHERE    koupil.vID=vyrobek.vID
  AND    koupil.login=uzivatel.login
  AND    uzivatel.mesto='Ostrava'
  

Pomocí klauzule SELECT jsme definovali které sloupce chceme ze které tabulky vybrat. Kdybychom napsali pouze SELECT login, jmeno, tak by to nefungovalo, protože v kartézskem součinu se nám login i jmeno vyskytuje dvakrat protože oba tyto atributy jsou použity ve dvou tabulkách. V klauzuli WHERE nejprve vybereme kombinace kartezskeho součinu které spolu souvisí a poté už jen upřesníme, že cheme jen ty uživatele, kteří jsou z Ostravy.

4. Vypište všechny výrobky, které koupil někdo z města končicího na písmeno 'a' a seřaďte výsledek dle jmena výrobku sestupně

SELECT   DISTINCT vyrobek.jmeno
FROM     koupil, vyrobek, uzivatel
WHERE    koupil.vID=vyrobek.vID
  AND    koupil.login=uzivatel.login
  AND    mesto LIKE '%a'
ORDER BY vyrobek.jmeno DESC
  

Tento příklad se dost podobá tomu předchozímu, a tak ho nebudu moc rozebírat. Jedinou novinkou je zde použití nepovinné klauzule LIKE která porovnává, zda se textové řetězce shodují s uvedeným vzorem textového řetězce. Textový řetězec klasicky uzavřený v apostrofech začiná znakem %, který nahrazuje žádný nebo více jakýchkoli znaků. Protože jsme chtěli všechny města končicí na písmeno 'a', musíme za znak % ještě uvést toto písmeno, které ukončuje řetězec. Kdybychom chtěli všechna města začinající na písmeno 'O', napsali bychom do podmínky 'O%' (Začiná na O a po něm následuje libovolný počet znaků).

5. Vypište všechny výrobky, které si koupili oba uživatelé 'vinetu' i 'pepik'

SELECT    DISTINCT vyrobek.jmeno
FROM      koupil JOIN vyrobek ON koupil.vID=vyrobek.vID
WHERE     login='vinetu'
INTERSECT
SELECT    DISTINCT vyrobek.jmeno
FROM      koupil JOIN vyrobek ON koupil.vID=vyrobek.vID
WHERE     login='pepik'

Pro SŘBD nepodporující INTERSECT (např. MySQL):

SELECT    DISTINCT vyrobek.jmeno
FROM      koupil AS k1
          JOIN koupil AS k2 ON k1.vID=k2.vID  // dvojice vsech lidi, kteri si koupili to same
          JOIN vyrobek ON k1.vID=vyrobek.vID  // spojeni kvuli jmena vyrobku
WHERE     k1.login='vinetu'                   // co si koupil vientu
  AND     k2.login='pepik'                    // a zaroven to koupil i pepik

Za použití operátoru IN:

SELECT    *
FROM      vyrobek
WHERE     vID IN (select vID from koupil where login="vinetu")
  AND     vID IN (select vID from koupil where login="pepik")

6. Vypište všechny výrobky, které si koupil 'vinetu', ale ne 'pepik'

SELECT    DISTINCT vyrobek.jmeno
FROM      koupil JOIN vyrobek ON koupil.vID=vyrobek.vID
WHERE     login='vinetu'
EXCEPT
SELECT    DISTINCT vyrobek.jmeno
FROM      koupil JOIN vyrobek ON koupil.vID=vyrobek.vID
WHERE     login='pepik'

Použitím operátoru NOT IN:

SELECT    jmeno
FROM      vyrobek
WHERE     vID IN  (SELECT    vID
                  FROM      koupil
                  WHERE	login = 'vinetu')
          AND vID NOT IN(SELECT vID
                  FROM     koupil
                  WHERE	login = 'pepik')

Druhá možnost má daleko kratší zápis a podle mých testů ji server provede rychleji.

7. Nalezněte všechny výrobky, které si nikdo nekoupil

SELECT    *                                             // vnejsi prikaz
FROM      vyrobek
WHERE     vID NOT IN (SELECT DISTINCT vID FROM koupil)  // vnitrni prikaz

Nejprve vnitřním příkazem vyberu všechny vID z tabulky koupil, neboli všecky výrobky, které si někdo koupil. Pak vnějším příkazem z tabulky vyrobek vyberu všechny výrobky, ktere nejsou v tabulce koupenych vyrobku.

8. Vypište všechny výrobky, které si koupil 'vinetu' nebo 'pepik', ale které si nekoupili oba zároveň

V tomto příkaldu můžeme využít následujícího Množinového vzorce:
(A SJEDNOCENO S B) - (PRŮNIK A s B) = (A-B) SJEDNOCENO S (B-A), neboli:
(co si koupil jeden nebo druhy) bez toho (co si koupili oba zaroven) = (koupil vinetu ale ne pepik) sjednoceno s tim (co koupil pepik ale ne vinetu)
Jsou tedy dva způsoby jak pomocí UNION (sjednocení množin, OR), INTERCECT (průnik množin, AND) a EXCEPT (rozdíl množin, to co je v jedné ale ne v druhé) napsat tento SQL dotaz. Níže uvedený způsob je ten první ( (A OR B)-(A AND B) ).

(
  SELECT    *
  FROM      vyrobek, koupil, uzivatel
  WHERE     koupil.vID=vyrobek.vID
    AND     uzivatel.login=koupil.login
    AND     uzivatel.login = 'vinetu'
  UNION
  SELECT	  *
  FROM      vyrobek, koupil, uzivatel
  WHERE     koupil.vID=vyrobek.vID
    AND     uzivatel.login=koupil.login
    AND     uzivatel.login = 'pepik'
)
EXCEPT
(
  SELECT    *
  FROM      vyrobek, koupil, uzivatel
  WHERE     koupil.vID=vyrobek.vID
    AND     uzivatel.login=koupil.login
    AND     uzivatel.login = 'vinetu'
  INTERSECT
  SELECT	  *
  FROM      vyrobek, koupil, uzivatel
  WHERE     koupil.vID=vyrobek.vID
    AND     uzivatel.login=koupil.login
    AND     uzivatel.login = 'pepik'
)

4. Nalezněte všechny výrobky, které si nějáký uživatel koupil alespoň dvakrát

SELECT    DISTINCT vyrobek.jmeno
FROM      koupil AS k1   JOIN vyrobek ON k1.vID=vyrobek.vID
WHERE     EXISTS ( SELECT * FROM koupil AS k2
                   WHERE k1.vID=k2.vID
                   AND k1.login=k2.login
                   AND k1.rok !=k2.rok )

V tomto příkladě je využito toho, že si jeden výrobek nemohl koupit někdo ve stejném roce vícekrát, protože login, vID, rok tvoří primární jedinečný klíč tabulky koupil a nemůže se tedy ta stejná kombinace vyskytnout v tabulce vícekrát (v normálním životě si samozžejmě někdo může koupit jednu věc vícekrát za rok, jen tato tabulka je tak zrobená).

3. Vypište kolikrát byly v roce 2009 koupeny jednotlivé výrobky uživateli narozenými mezi lety 1980 a 1995

SELECT    vyrobek.jmeno, Count(vyrobek.jmeno) AS kolikrat_koupeno
FROM      koupil JOIN vyrobek ON koupil.vID=vyrobek.vID
WHERE     login IN (select login
                    from uzivatel
                    where rok_narozeni between 1980 AND 1995)
  AND     rok=2009
GROUP BY  vyrobek.jmeno

První písemka z 13.3.2014 7:15



Druhá písemka 29.4

1. Vypište jména her ve kterých hrál někdo z Kašpárkova divadla, kdo nemá rok narození mezi lety 1980 a 1990

SELECT    DISTINCT jmeno_hry
FROM      hra JOIN hraje on hra.id_hry=hraje.id_hry
WHERE     id_herce IN (SELECT id_herce FROM herec
                       WHERE (rok_narozeni NOT BETWEEN 1980 AND 1990)
                         AND jmeno_divadla='Kašpárkovo')

2. Vypište jména komedií ve kterých hráli Oskar a Hujer

(SELECT    jmeno_hry
 FROM      hraje JOIN hra ON hraje.id_hry=hra.id_hry
           JOIN herec ON hraje.id_herce=herec.id_herce
 WHERE     typ_hry='komedie' AND jmeno='Oskar')
INTERSECT
(SELECT    jmeno_hry
 FROM      hraje JOIN hra ON hraje.id_hry=hra.id_hry
           JOIN herec ON hraje.id_herce=herec.id_herce
 WHERE     typ_hry='komedie' AND jmeno='Hujer')

3. Vypište dvojice (jméno hry, největší počet představení odehraných na této hře jedním hercem)

SELECT    jmeno_hry, MAX(pocet_predstaveni)
FROM      hra JOIN hraje ON hraje.id_hry=hra.id_hry
GROUP BY  jmeno_hry