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