Autor, Název akce
Databázové systémy a SQL
Lekce 5
Daniel Klimeš

Autor, Název akce
TRIALDB – uložení dat
Daniel Klimeš, Databázové systémy a SQL
Uložení vlastních dat v TrialDB není klasický relační datový model
Generalizovaný model – EAV model – Entity – Attribute - Value
Entita
Atribut
Hodnota
Pacient
Otázka
Hodnota
PacientXY
Hmotnost
84
PacientXY
Výška
186
V datovém modelu TrialDB – rozpracované pro jednotlivé datové typy
ID pacienta
Hmotnost
Výška
PacientXY
84
186
•Klasický datový model
EAV model

Autor, Název akce
TRIALDB – datový model
Daniel Klimeš, Databázové systémy a SQL
export_patients.jpg
Hlavička formuláře
Hlavička skupiny
Vlastní data

Autor, Název akce
Tabulka EVENT_HEADER
Daniel Klimeš, Databázové systémy a SQL
•Tabulka EVENT_HEADER – 1 řádek = 1 vyplněný formulář
•HEADER_UID – primární klíč, generovaný
• STUDY_ID – klíč ke studii/registru
• PATIENT_ID – klíč k pacientovi
• PHASE_ID – klíč k fázi
• CLUSTER_ID – klíč k popisu formuláře
• DATE_COLLECTED – datum vyplnění formuláře
• DATETIME_LAST_MODIFIED
     datum poslední změny dat ve formuláři

Autor, Název akce
Tabulka EVENT_SUBHEADER
Daniel Klimeš, Databázové systémy a SQL
• EVENT_SUBHEADER – 1 řádek = 1 vyplněná skupina otázek
• SUBHEADER_ID – primární klíč, generovaný
• HEADER_UID – klíč k vyplněnému formuláři
(EVENT_HEADER)
• QUESTION_GROUP_ID – klíč k popisu skupiny otázek
• REPEAT_INSTANCE
       pořadové číslo vyplněné skupiny na formuláři
Neopakující se skupina – repeat instance vždy 0
Opakující se skupina
repeat instance = řádek tabulky= =řádek v EVENT_SUBHEADER
RI = 1
RI = 2

Autor, Název akce
Tabulky EAV_XXX
Daniel Klimeš, Databázové systémy a SQL
•EAV_XXX – 1 řádek = 1 vložená hodnota
• SUBHEADER_ID + QUESTION_ID
   složený primární klíč
• SUBHEADER_ID klíč ke skupině (EVENT_SUBHEADER)
• QUESTION_ID – klíč k definici otázky
• DATETIME – datum a čas vyplnění
• MVBS – údaje o přesnosti či chybějící hodnotě
• VALUE – vlastní vyplněná hodnota

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
• Kolik vyplněných desetinných čísel obsahuje registr study_id = 3
• tabulky EAV_REAL, EVENT_HEADER, EVENT_SUBHEADER
•Spojte uvedené tabulky dle klíčů – vnitřní spojení
SELECT * FROM event_header eh, event_subheader es, eav_real er
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
• Přidejte podmínku na konkrétní studii a spočítejte řádky
 SELECT COUNT(*) FROM event_header eh, event_subheader es, eav_real er
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
AND eh.study_id = 3

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Kolik těchto hodnot je záporných?
SELECT COUNT(*) FROM event_header eh, event_subheader es, eav_real er
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
AND eh.study_id = 3 AND er.value < 0
•Kolik je to unikátních otázek (question_id)
SELECT COUNT(distinct question_id)
FROM event_header eh, event_subheader es, eav_real er
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
AND eh.study_id = 3 AND er.value < 0

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Jaké jsou průměrné hodnoty a směrodatná hodnota jednotlivých otázek?
 (vynechte záporné hodnoty)
SELECT question_id, AVG(value), STDDEV(value), MIN(value), MAX(value)
FROM event_header eh, event_subheader es, eav_real er
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
AND eh.study_id = 3 and er.value > 0
GROUP BY question_id
•Doplňte k seznamu název otázky
SELECT q.question_id, q.question_description, AVG(value), STDDEV(value)
 FROM event_header eh, event_subheader es, eav_real er, questions q
WHERE eh.header_uid = es.header_uid AND es.subheader_id = er.subheader_id
AND eh.study_id = 3 and er.value > 0 AND er.question_id = q.question_id
GROUP BY q.question_id, q.question_description

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Vypište hodnoty otázky question_id = 161 (PATIENT_ID, VALUE)
  pro všechny založené formuláře cluster_id = 65
•Kolik je formulářů cluster_id = 65 , study_id = 3?
SELECT COUNT(*) FROM event_header WHERE study_id = 3 AND cluster_id = 65
1) SELECT eh.patient_id, er.value
FROM event_header eh LEFT JOIN
      (event_subheader es INNER JOIN eav_real er
       ON es.subheader_id = er.subheader_id AND er.question_id = 161)
     ON eh.header_uid = es.header_uid
WHERE  eh.study_id = 3 AND eh.cluster_id = 65;
2) SELECT eh.patient_id, es.value
FROM event_header eh LEFT JOIN
      (SELECT es.header_uid, er.value from EVENT_SUBHEADER es, eav_real er
       WHERE es.subheader_id = er.subheader_id AND er.question_id = 161) es
     ON eh.header_uid = es.header_uid
WHERE  eh.study_id = 3 AND eh.cluster_id = 65;

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Vytvořte z vnořeného dotazu VIEW a přepište předchozí dotaz s jeho použitím
CREATE VIEW subheader_eav
as
SELECT es.header_uid, er.value FROM event_subheader es, eav_real er
       WHERE es.subheader_id = er.subheader_id AND er.question_id = 161
SELECT eh.patient_id, es.value
FROM event_header eh LEFT JOIN subheader_eav es
     ON eh.header_uid = es.header_uid
WHERE  eh.study_id = 3 AND eh.cluster_id = 65

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
Lepší varianta umožňující využití pro libovolnou otázku
CREATE OR REPLACE VIEW subheader_eav
as
SELECT es.header_uid, er.question_id, er.value
FROM event_subheader es, eav_real er
       WHERE es.subheader_id = er.subheader_id
SELECT eh.patient_id, es.value
FROM event_header eh LEFT JOIN subheader_eav es
     ON eh.header_uid = es.header_uid AND es.question_id = 161
WHERE  eh.study_id = 3 AND eh.cluster_id = 65

Autor, Název akce
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Vypište hodnoty dvou otázek (161, 27) ve tvaru patient_id, value1, value2
 pro všechny existující formuláře cluster_id = 65 , study_id = 3
SELECT eh.patient_id, es.value, es2.value
FROM event_header eh
     LEFT JOIN subheader_eav es
       ON eh.header_uid = es.header_uid and es.question_id = 161
     LEFT JOIN subheader_eav es2
       ON eh.header_uid = es2.header_uid and  es2.question_id = 27
WHERE  eh.study_id = 3 AND eh.cluster_id = 65
ORACLE varianta:
SELECT eh.patient_id, es.value, es2.value
FROM event_header eh, subheader_eav es, subheader_eav es2
       WHERE eh.header_uid = es.header_uid(+) and es.question_id(+) = 161
       AND eh.header_uid = es2.header_uid(+) and  es2.question_id(+) = 27
AND  eh.study_id = 3 AND eh.cluster_id = 65

Autor, Název akce
FIREBIRD – testovací databáze
Tabulka Pacienti
•ID
•Jmeno
•Datum_narozeni
•Pohlavi
Tabulka Vysetreni
•ID_vysetreni
•ID
•Datum_vysetreni
•Typ_vysetreni
• Vysledek
Minulé cvičení:
1.Ověřte, zda ID_vysetreni v tabulce vysetreni je unikátní
2.Vypište id_vysetreni z tabulky vysetreni, která jsou duplicitní
3.Ověřte, zda každé vyšetření má rodičovský záznam v tabulce pacienti
Daniel Klimeš, Databázové systémy a SQL
1.SELECT count(*), count(distinct id_vysetreni) FROM vysetreni
   počty se neshodují=>není unikátní
2.SELECT id_vysetreni FROM vysetreni
GROUP BY id_vysetreni HAVING count(*) > 1
3.SELECT count(*) FROM vysetreni v
WHERE NOT EXISTS (SELECT * FROM pacienti p WHERE p.id = v.id)

Autor, Název akce
FIREBIRD – testovací databáze
Tabulka Pacienti
•ID
•Jmeno
•Datum_narozeni
•Pohlavi
Tabulka Vysetreni
•ID_vysetreni
•ID
•Datum_vysetreni
•Typ_vysetreni
• Vysledek
Cvičení:
1.Vypište pro všechny pacienty výsledky vyšetření typu 2 a 9
ve tvaru ID, jmeno, vysledek typu 2, vysledek typu 9
1.
Daniel Klimeš, Databázové systémy a SQL