Tato stránka vyžaduje podporu CSS stylů

Semestrální práce z předmětu 36SQL

"Lázeňský ústav"


Oto Válek
letní semestr 2002/2003, cvičení Čt 16:15

1. Popis

Databáze modeluje situaci v jednoduchých lázních, zejména organizaci léčby - vyšetření a léčebných procedur. Sleduje hosty, odborné zaměstnance - lékaře a sestry a vybrané místnosti - pokoje a léčebny. Součástí je i číselník typů procedur, který je společný pro více entit.

2. Datový model

Datový model se skládá z 10 entit svázaných ISA hiearchií a 9 vztahů. Následuje přehled entit:

Osoba Eviduje se jméno, příjmení a datum narození. Primárním klíčem všech osob v systému je speciální číslo. Rodné číslo jím být nemůže kvůli zahraničním návštěvníkům.
Host U hostů je navíc nutné evidovat číslo jejich zdravotní pojišťovny a pohlaví. Může být ubytován v maximálně jednom pokoji a během pobytu se zúčastní většího počtu vyšetření.
Lékař Lékař provádí vyšetření pacientů a předepisuje jim procedury, ovšem zatím bez místa a sestry, zato ale specifikuje jejich typ a počet.
Sestra Sestra provádí procedury, které ji systém přidělí. Může ale provádět jen ty, pro které má kvalifikaci. Kvalifikací může mít několik, ale také žádnou.
Pokoj Na pokoji může být ubytováno několik hostů až do jeho kapacity. Relace ubytování je zde pouze informativní a nezaznamenává historii. Lze ale zavést požadavek stajného pohlaví hostů na jednom pokoji.
Léčebna Léčebna je vybavena pro vykonávání několika druhů procedur. V jednom čase se v ní může konat procedur více, a to až do počtu daného její kapacitou.
Vyšetření Vyšetření je provedeno právě jedním lékařem na právě jednom pacientovi. Výsledkem může být popis diagnózy a předepsání několika procedur s daným druhem, ale bez konkrétní doby.
Procedura Procedura musela být předepsána při určitém vyšetření a musí mít přiřazen druh. Pacient je jednoznačně přiřazen přes entitu Vyšetření. Atribut Provedena slouží pro potvrzení vykonání procedury.
Druh procedury Jednoduchý číselník typů procedur.
ER-model vytvořený programem ER modeller (odkazuje na větší verzi):

ER-model

3. Integritní omezení

Byla navržena na začátku semestru a jsou implementována v procedurálním rozšíření PL/SQL.
rodná čísla při vkládání osoby do databáze i editaci je vyžadováno, aby bylo rodné číslo dělitelné 11, je-li vyplňeno.
kapacita pokoje kontrola, že počet pacientů ubytovaných na pokoji nepřekročí jeho kapacitu
kapacita léčebny kontrola, že není kapacita léčebny překročena v jednom čase
kvalifikace sestry sestra musí mít kvalifikaci k dané proceduře, než databáze povolí zápis do rozvrhu procedur
vybavení léčebny podobně léčebna musí být k proceduře vybavena
pohlaví hostů jedná se o puritánské lázně, hosté na jednom pokoji musí být stejného pohlaví

4. Implementace

Byla navržena na začátku semestru a jsou implementována v procedurálním rozšíření PL/SQL.
rodná čísla Jednoduchý trigger nad tabulkou Osoba
kapacita pokoje Obsazení pokoje se mění zápisem do tabulky Ubytovani, kterou je zároveň nutno číst pro ověření integritního omezení. To vyvolává chybu ORA-04091: table is mutating, trigger/function may not see it.
Problém byl vyřešen sadou tří triggerů, kdy FOR EACH ROW připraví informace o tom, které pokoje je třeba ověřit, a uloží je do globální proměnné ve spec. package. Trigger AFTER INSERT OR UPDATE pak realizuje kontrolu, uloženou v této globální proměnné jako seznam (table) čisel pokojů. Tento trigger je postaven nad celou tabulkou, a chybu ORA-04091 nevyvolává.
kapacita léčebny Zde se vyskytl obdobný problém a řešení je obdobné. Jen konání procedury je identifikováno dvěma údaji - datem a místem, tak je třeba v pomocných tabulkách uchovávat oba.
kvalifikace sestry Trigger nad tabulkou Provedeni_p. Provede dotaz do tabulky Kvalifikace na kvalifikaci dané sestry rovnou druhu požadované procedury. Je-li vyvolána výjimka NO_DATA_FOUND, je převedena na chybu aplikace.
vybavení léčebny Obdobný trigger nad tabulkou Konani.
pohlaví hostů Opět jsou k ověření vyžadovány informace z tabulky Ubytovani, nad kterou je trigger postaven. Řešení se skládá ze tří triggerů jako u prvních dvou integritních omezení.

5. Soubory

Přiložené soubory:

lazne_cr.sql - SQL skript pro vytvoření tabulek
lazne_in.sql - SQL skript pro naplnění daty
lazne_ch.sql - SQL skript pro definici triggerů a procedur, obsahuje též testovací SQL příkazy vyvolávající chyby integrity
lazne_te.sql - SQL skript s těmito testovacími příkazy
lazne_te.log - a log jeho použití

lazne.zip - ZIP archiv projektu