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

Autor, Název akce
Databázové objekty
Daniel Klimeš, Databázové systémy a SQL
•Nejvýznamnější databázové objekty
• Tabulky (tables)
• Pohledy (views)
• Indexy (indexes)
• Sekvence (sequences)
• Procedury (procedures)
• Funkce (functions)
• Triggery (triggers)
Informace o objektech jsou uloženy v metadatech (systémových datech) databáze
Přístup k nim je databázově specifický
ORACLE
• systémové tabulky – uživatelům pouze pro čtení
• metatabulka o metatabulkách – DICTIONARY
• tabulky USER_XXX – objekty vytvořené uživatelem
• tabulky ALL_XXX – objekty přístupné uživateli
• tabulky DBA_XXX – všechny objekty databáze – přístupné jen administrátorovi
•

Autor, Název akce
Hierarchie objektů
Daniel Klimeš, Databázové systémy a SQL
•ORACLE
• Na serveru je 1 databáze
• Každý uživatel má
   automaticky své schéma
• Uživatel vytváří objekty
   ve svém schématu
•PostgreSQL
• Na serveru je N databází
• V každé databázi je jedno
  výchozí schéma public
• V každé databázi je možné
  vytvářet další schémata
• Uživatel vytváří objekty
   v libovolném schématu,
   defaultně v public
•
•Schéma = sada databázových objektů patřící
                   obvykle jednomu  projektu / podprojektu
• Odkaz na objekt ve schématu: schema.objekt
   např.: student.patients

Autor, Název akce
Tabulky
Daniel Klimeš, Databázové systémy a SQL
• Metatabulky USER_TABLES, ALL_TABLES, DBA_TABLES
• sloupec table_name
• Metatabulka TAB
• sloupec tname, tabtype
• Sloupce tabulky - USER_TAB_COLUMNS
• table_name, column_name, data_type
• PostgreSQL (ANSI standard)
• information_schema.tables
• information_schema.columns
•
DDL příkazy pro manipulaci s tabulkami
• CREATE TABLE
• DROP TABLE
• ALTER TABLE
• RENAME TABLE

Autor, Název akce
Pohledy (VIEWS)
Daniel Klimeš, Databázové systémy a SQL
•Pohled = uložený SQL dotaz
•Pracuje se s ním stejně jako s tabulkou
•Ve většině případů je možný pouze SELECT
•
• CREATE VIEW v_ukazka AS
      SELECT ps.patient_id, study_name FROM patient_study ps, studies s
      WHERE ps.study_id = s.stud y_id
SELECT study_name, count(*) FROM v_ukazka
GROUP BY study_name
DDL pro pohledy:
CREATE OR REPLACE VIEW AS
DROP VIEW
ORACLE metadata
• user_views, tab
PostgreSQL/ANSI
• information_schema.views

Autor, Název akce
Indexes
Daniel Klimeš, Databázové systémy a SQL
http://t2.gstatic.com/images?q=tbn:ANd9GcS9N-4620UX6QGkL1BjwS17HbDWd-gotYpYiNoSHszQetpztSOBZg
• Indexy jsou obdobou kartotéky
• Umožňují rychlejší vyhledávání záznamů ve velkých tabulkách
• Urychlují SELECT dotazy, zpomalují INSERT, UPDATE, DELETE
• Indexy se vytváří nad jedním nebo více sloupci tabulky
• Standardně nad primárním klíčem a cizími klíči
• Dále nad sloupci, které se často používají za WHERE
•DDL pro indexy
• CREATE INDEX
• DROP INDEX
• ALTER INDEX
ORACLE metadata
user_indexes
ind

Autor, Název akce
Sekvence
Daniel Klimeš, Databázové systémy a SQL
•Sekvence generují za všech okolností unikátní čísla – posloupnost
• Použití pro primární klíče při insertech nových řádků
•
• SELECT jmeno_sekv.NEXTVAL from DUAL
• SELECT jmeno_sekv.CURRVAL from DUAL
•
• Každé zavolání NEXTVAL vrátí další číslo v posloupnosti bez ohledu
 na transakce
Při neúspěšném použití vygenerovaného ID vznikají “díry” v posloupnosti
•ORACLE DDL
• CREATE SEQUENCE
• DROP SEQUENCE
• ALTER SEQUENCE
• ORACLE metadata
• user_sequences
•PostgreSQL/ANSI
• information_schema.sequences

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) – QUESTION_ID + agregační funkce
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, study_id = 3
•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
Cvičení
Daniel Klimeš, Databázové systémy a SQL
•Vypište formuláře (cluster_id = 65 , study_id = 3),
  které nemají vyplněny otázky question_id 161, 27
   ve tvaru patient_id, header_uid
SELECT eh.patient_id, eh.header_uid
FROM   event_header eh
WHERE  eh.study_id = 3 AND eh.cluster_id = 65
AND  NOT EXISTS
    (SELECT * FROM subheader_eav es
        WHERE eh.header_uid = es.header_uid AND es.question_id = 161
    )
AND NOT  EXISTS
    (SELECT * FROM  subheader_eav es2
        WHERE  eh.header_uid = es2.header_uid AND  es2.question_id = 27
    )

Autor, Název akce
Domácí úkol
Daniel Klimeš, Databázové systémy a SQL
• V PosgreSQL databázi importujte další skript pg2 a pg3.sql
•  Vyzkoušejte dnešní příklady