3. Relační model Relační model reprezentuje databázi jako soubor relací. Kaţdá relace představuje tabulku nebo soubor (ve smyslu soubor na nosiči dat). Příklad 3.1: Filmová databáze relace:
FILM REŢISÉR ŢÁNR SPOLEČNOST HEREC ROLE
Terminologie v relačním modelu relační model relační schéma relace (tabulka) řádek
n-tice (n-tuple, tuple)
sloupec
atribut
doména superklíč klíč primární klíč kandidátní klíč cizí klíč Relační schéma R označené jako R(A1, A2 , . . . . , An) je tvořeno jménem relace a seznamem atributů. Kaţdý atribut Ai zastupuje určitou doménu D, kterou značíme dom(Ai). Počet atributů n určuje tzv. stupeň relace. Nezáleţí na pořadí atributů ani řádků. Příklad 3.2: ŢÁNR(id_ţánr, název)
stupeň 2
REŢISÉR(id_reţisér, příjmení, jméno, stát)
stupeň 4
FILM(id_film, název, reţisér, společnost, rok, délka, přístupno, ţánr)
stupeň 8
Datový typ určující typy hodnot, kterých můţe atribut nabývat (které se tedy mohou objevit v konkrétním sloupci tabulky), se nazývá doména. Doména - mnoţina atomických hodnot (hodnot, které se dále nedělí, přesněji řečeno pro potřeby daného relačního modulu se tyto hodnoty uţ dále nedělí) Doména je tedy dána svým jménem, datovým typem a formátem, eventuelně můţe být doplněna o dodatečné informace ohledně např. měřící jednotky. Relace r (instance relace - tj. konkrétní výskyt relace) z relačního schématu R(A1, A2 , ..... , An) se značí také jako r(R) a je to mnoţina n-tic r = { t1 , t2 , . . . . , tm } . Kaţdá n-tice je uspořádaný seznam n hodnot t = < v1 , v2 , . . . . vn > , kde kaţdé vi , 1 i n, je prvkem domény dom(Ai) nebo je specielní hodnota null. Relace r(R) je podmnoţina kartézského součinu domén, které definují R r(R)
( dom(A1) x dom(A2) x . . . . x dom(An) )
Kartézský součin určuje všechny moţné kombinace hodnot z uvedených domén. Pokud označíme kardinalitu domény D jako D a předpokládáme, ţe všechny domény jsou konečné, potom celkový počet n-tic v kartézském produktu je dom(A1) * dom(A2)
* .... *
dom(An)
Použitá notace relační schéma R stupně n
R(A1, A2 , ..... , An)
n-tice t v relaci r(R)
t = < v1 , v2 , . . . . vn >, kde vi je hodnota odpovídající atributu Ai
hodnota atributu Ai
vi =t[Ai]
relační jména
R, S, Q , . . .
relační stavy
r, s, q, . . .
n-tice
t, u, v
kvalifikovaná jména atributů aktuální relační stav relační schéma
STUDENT.Jméno, STUDENT.Příjmení, ...
STUDENT
STUDENT(Jméno, Příjmení, ... )
Příklad 3.3: n-tice t=<“Jana Bláhová“, “765502/1234“, “Kaplice“,null, 21, 3, 12> viz relace STUDENT (Jméno, Rod. číslo, Adresa, Telefon, Věk, Ročník, Obor) t Jméno = “Jana Bláhová“ , t Ročník = 3
DB tabulka - vlastnosti má jednoznačné jméno charakterizuje jedinou entitu kaţdý sloupec (atribut) v tabulce má jednoznačné jméno všechny hodnoty daného sloupce jsou stejného typu nezáleţí na pořadí sloupců nezáleţí na pořadí řádků neobsahuje! duplicitní řádky všechny hodnoty jsou atomické má primární klíč
Operace s tabulkami mnoţinové operace selekce – podmnoţina řádků projekce – podmnoţiny sloupců operace spojení – kombinace více vyuţití operací výrokové logiky
Omezení – podmínky relačního modelu Podmínky pro domény – hodnota kaţdého atributu musí být atomická hodnota a musí být prvkem mnoţiny dom(A). Datové typy pro doménu zahrnují standardní číselné datové typy (celočíselné – short integer, integer, long integer, reálné typy jako float, double), dále řetězcové typy (řetězce pevné i proměnné délky), měnové, datumové, časové typy. Ostatní datové typy domén se definují jako podintervaly nebo výčtové typy výše uvedených typů. Klíčová omezení – relace je dána jako mnoţina n-tic (záznamů, vět). Dle definice mnoţiny jsou všechny její prvky různé tj. i pro všechny záznamy v relaci musí platit, ţe jsou navzájem různé neboli ţe neexistují dvě n-tice, které mají tutéţ kombinaci hodnot atributů. Obvykle existují různé podmnoţiny atributů SK, které mají tu vlastnost, ţe ţádné dva záznamy nemají stejnou kombinaci hodnot atributů tj. pro libovolné dvě n-tice t1 a t2 v relaci r (relační schéma R) platí, ţe t1 SK t2 SK . Kaţdá takováto podmnoţina atributů je označována jako superklíč relačního schématu R. Kaţdá relace má nejméně 1 superklíč - mnoţinu všech atributů. Superklíč můţe obsahovat redundantní atributy, nicméně rozumná koncepce klíče je pochopitelně klíč bez nadbytečných atributů. Jako klíč K relačního schématu R tedy označíme superklíč schématu R, který má tu vlastnost, ţe odebereme-li libovolný atribut A z klíče K, zbyde mnoţina atributů K’, která není superklíč. Tj. klíč je minimální superklíč - nemůţeme odebrat jediný atribut k zachování jednoznačnosti.
Příklad 3.4: Relace STUDENT (Stud_číslo, Rodné číslo, Jméno, Adresa, Ročník, Obor) Stud_číslo, Příjmení, Jméno, Ročník je superklíč, ne klíč Stud_číslo, Příjmení, Jméno je superklíč Stud_číslo, Příjmení je superklíč {Stud_číslo } je klíč Příjmení, Jméno, Ročník není superklíč, tedy ani klíč Rodné číslo je klíč
Klíč musí obsahovat jedinečné hodnoty v rámci celé tabulky nesmí obsahovat NULL nesmí to být vícesloţkové pole jeho hodnota nesmí způsobit narušení bezpečnosti a únik informací jeho hodnota není ani částečně, ani jako celek volitelná skládá se z nejmenšího moţného počtu polí jeho hodnota musí jednoznačně identifikovat hodnoty všech polí daného záznamu klíč je časový invariant (jeho hodnota se mění zcela výjimečně) Relační schéma můţe mít víc klíčů, který z nich bude označen jako primární je v podstatě libovolné, ostatní klíče se pak označují jako kandidátní.
Relační databázové schéma a integritní omezení Relační databázové schéma S je mnoţina všech relačních schémat S = R1, R2, . . . , Rm a mnoţina integritních podmínek ( omezení ) IO. Instance relační databáze DB ze schématu S je mnoţina relačních instancí DB = r1, r2, . . , rm , kde ri je instance Ri a splňuje podmínky integritních omezení.
Integrita dat Stanovení jistých pravidel na úrovni tabulek na úrovni polí na úrovni vztahů – RI na úrovni business pravidel na úrovni pohledů
Integrita na úrovni tabulek tabulka obsahuje jedinečné záznamy tabulka neobsahuje duplicitní pole vypočítaná pole vícehodnotová pole vícesloţková pole duplicitní záznamy tabulka má primární klíč
Integrita na úrovni polí Doménová integrita hodnoty kaţdého pole jsou platné, přesné hodnoty stejného typu jsou definovány stejně
Referenční integrita Podmínky specifikované pro vztah dvou relací - uţívá se pro zachování konzistence mezi záznamy dvou relací. Záznam relace R1, která je spojena s jinou relací R2, se musí odkazovat na existující (odpovídající) záznam v R2 Příklad 3.5: ŢÁK (Číslo, Jméno, Adresa, Třída )
TŘÍDA(Id třídy, Umístění, Třídní) Pojem cizí klíč se zavádí v souvislosti se vztahem dvou relací R1 a R2. Mnoţina atributů FK z relačního schématu R1 je cizím klíčem R1 , pokud splňuje následující dvě pravidla : 1. Atributy zahrnuté v FK mají tutéţ doménu jako primární klíč PK v R2 , FK se odkazuje na PK. 2. Hodnota FK v záznamu t1 z R1 se buďto vyskytuje jako hodnota PK v nějakém záznamu t2 v R2 nebo je NULL t1 FK = t2 PK
Příklad 3.6: Cizí klíče TŘÍDA (Číslo třídy, Třídní , Umístění) UČITEL (Číslo, Příjmení, Jméno, Kabinet ) MÍSTNOST (Číslo, Název, Kapacita, Typ) TYP (Číslo, Označení)
Bussiness pravidla Předchozí typy podmínek ovšem nezahrnují velké skupiny obecných podmínek – tzv. podmínek sémantické integrity, které mohou být specifikovány na relační databázi a mohou ovlivňovat uloţená data. vycházejí z poţadavků uţivatele, logiky aplikace souvisejí se způsobem pořizovaní a zpracování dat, schodem firmy … vymezení povolených hodnot v polích pomocí číselníků, matematických, logických, formátovacích pravidel vzájemné logické vazby polí vzájemné hierarchické vazby polí pravidla pro vztahy Příklad 3.7: Sportovní klub smlouva hráče je nejvýše na 3 roky v klubu smí působit nejvýše 5 cizinců Příklad 3.8: Firma kaţdý zaměstnanec je zařazen na právě jedno pracoviště plat zaměstnance musí být menší neţ plat vedoucího maximální počet hodin odpracovaných v týdnu < 56 hod roční počet přesčasů < 181 hod Příklad 3.9: Knihovna kniha je zařazena na jediné oddělení od kaţdé evidované knihy můţe být více výtisků délka výpůjčky knihy je 1 měsíc délka výpůjčky časopisu je 1 týden čtenář můţe mít půjčeno nejvýše 15 knih čtenář musí zaplatit roční poplatek nejpozději do 30 dnů po přihlášení
Potenciální chybové situace - aktualizační operace na relaci INSERT – můţe nastat
porušení podmínek na doménu
porušení klíčových podmínek
porušení entitních podmínek (klíč NULL)
porušení RI podmínek
porušení business pravidel
DELETE – můţe nastat
porušení RI podmínek
porušení business pravidel
MODIFY – jedná se vlastně o DELETE a INSERT, z toho vyplývají příslušné moţné porušení všech podmínek
Transformace ER model do relačního DB modelu entitní typ = tabulka její sloupce odpovídají atributům vztahový typ kardinalita 1:1 není nutná další speciální tabulka je-li účast na vztahu oboustranná, lze provést sloučení do jediné tabulky kardinalita 1:N, N:1 není nutná další speciální tabulka do tabulky na straně N je nutné zahrnout cizí klíč kardinalita M:N, N:1 nutná další speciální (průniková, asociační) tabulka průniková tabulka obsahuje cizí klíče do obou tabulek a eventuelně další atributy vztahu
Coddova pravidla 1. Pravidlo SŘBD: data spravována pouze pomocí relačních operací 2. Pravidlo informační: data reprezentována na logické úrovni jako hodnoty relačních tabulek 3. Pravidlo přístupu: kaţdý údaj logicky dosaţitelný pomocí kombinace názvu tabulky, sloupce a hodnoty primárního klíče 4. Pravidlo zpracovatelnosti neznámých hodnot: kaţdou neznámou hodnotu lze určit pomocí jiných známých hodnot 5. Pravidlo relačního katalogu: popis celé databáze je na logické úrovni reprezentován jako relační systémový katalog
6. Pravidlo pro jazyk: pro komunikaci se SŘBD:
jazyk pro definici dat (DDL)
jazyk pro manipulaci s daty (DML)
jazyk pro definici integritní omezení (DCL)
7. Pravidlo pohledů: SŘBD musí umoţňovat konstrukci pohledů 8. Pravidlo operací: všechny relační operace pracují s tabulkami jako s celky 9. Pravidlo fyzické a logické nezávislosti dat 10. Pravidlo nezávislosti dat na integritních omezeních: výsledky operací musí být nezávislé na změnách IO 11. Pravidlo nezávislosti dat na distribuci: výsledky operací nesmí být ovlivněny konkrétním umístěním dat v distribuovaných databázích 12. Pravidlo nenarušitelnosti SŘBD: ţádný uţivatel nesmí obcházet nebo narušovat rozhraní SŘBD
Příklady 3.10 – 3.16 Příklad 3.10: BANKA – část databáze ÚČET
Číslo účtu
Číslo klienta
124df45f12 0011245 125784h1g 0011245 13254o1k8 0011445
KLIENT Číslo klienta 0011245
Kód měny CZK USD GBP
Datum zaloţení 12.05.2008 17.02.2006 11.03.2007
Datum posled. pohybu 17.06.2010 25.09.2009 21.05.2010
Jméno klienta Dvořák Petr
Adresa Český Krumlov, Špičák 127
0011235
ALFA s. r. o.
Tábor, Praţská 1882
0011445
Metrostav a. s.
Praha, I. P. Pavlova 174
Zůstatek 28 000 5 000 125 000
Podpisový vzor
Petr Dvořák Peterka Svoboda Pavel
Příklad 3.11: KNIHOVNA – část databáze ČTENÁŘ
ŢÁNR
VÝTISK
Id čtenář 0001 0002 0003 0004
Příjmení
Jméno
Město
Ulice
Psč
Adamec Dvořáková Vávrová Suchánek
Petr Jana Alena Jakub
Český Krumlov Kaplice Besednice Kaplice
Fialková 5 Luční 7 Dlouhá 11 Náměstí 8
381 01 382 41 NULL 382 41
Id ţánr 01 02 03 04 05 06 07 08
Ţánr dětská literatura dobrodruţství sci-fi cestopis román thriller detektivky literatura faktu
Evidenční číslo 121212 121245 123456 124512
Datum pořízení 22-031-79-13/33 04.11.1980 80-204-0045-1 24.06.1990 80-208-0178-2 12.09.1992 80-205-0268-8 17.03.1993 Kniha
VÝPUJČKA Čtenář Výtisk 0001 0001 0002 0002
123456 124512 121245 121212
Datum výpůjčky 12.09.2010 12.09.2010 10.09.2010 10.09.2010
Cena 33,00 16,00 49,00 39,00
Datum vrácení
20.09. 2010
Datum registrace 25.09.96 11.02.97 17.08.97 15.05.97
Příklad 3.12: FIRMA
ZAMĚSTNANEC Id_zam
ODDĚLENÍ
Číslo
Příjmení Jméno Rod.číslo Adresa Oddělení
Název Vedoucí
Pracoviště
PRACOVIŠTĚ Číslo pracoviště Název Místo
PROJEKT
Číslo projektu Název projektu Pracoviště
PRACUJE_NA Projekt
DÍTĚ
Zaměstnanec
Zaměstnanec
Jméno
Počet hodin
Rodné číslo dítěte
Příklad 3.13: ZÁSILKOVÁ FIRMA ZÁKAZNÍK Číslo
SKLAD
CENÍK
Jméno Osoba
Číslo zboţí
Číslo zboţí
OBJEDNÁVKA
Adresa IČO DIČ
DPH
Mnoţství
Název Měr. jednotka
Cena za mj
Číslo Datum Zákazník Číslo účtu
POLOŢKA_OBJ Číslo obj Název zboţí Mnoţství
FAKTURA
Číslo faktury
Číslo obj Datum Splatnost
POLOŢKA_FAK Číslo fak Název zboţí Mnoţství Cena za mj
Příklad 3.14: ZOO ZVÍŘE
Číslo
Jméno Pohlaví Druh Narození Umístění
DRUH
Číslo druhu Číslo třídy Číslo řádu Název
TŘÍDA
Číslo třídy Název
ŘÁD
Číslo řádu Název
PAVILON
Číslo pavilonu
MÍSTO
Číslo Číslo pavilonu
ZAMĚSTNANEC Osobní číslo
Název
Vedoucí
Jméno Adresa
FUNKCE
Číslo funkce Název funkce
PEČUJE
Osobní číslo Číslo místa
Umístění
Rod. číslo Funkce
Příklad 3.15: MUZEUM EXPONÁT
Číslo
Název
Období Původ
MÍSTNOST
Číslo
Číslo budovy
BUDOVA
Číslo budovy
Adresa
OBDOBÍ
Číslo období
Název
EXPOZICE
Číslo expozice Název
UMÍSTĚNÍ
Číslo expozice Místnost
Patro
Stát
Místnost
Příklad 3.16: KNIHOVNA podrobněji ČTENÁŘ Příjmení
VÝPŮJČKA
Jméno Rod.číslo Adresa Datum Číslo průkazky
Výtisk
Vypůjčeno
REZERVACE Číslo knihy Datum
Vráceno Čtenář
KNIHA
Číslo knihy Název Ţánr
ŢÁNR
Číslo ţánru Název
VYDÁNÍ Číslo vydání Kniha
Rok vydání Nakladatelství
VÝTISK Evidenční číslo Vydání NAKLADATELSTVÍ Číslo
AUTOR
Číslo
Příjmení
AUTORSTVÍ Autor
Čtenář
Cena Stav Název Město
Jméno
Stát
Národnost Narození
Kniha
Literatura: [1] ELMASRI, R., NAVATHE, S., B. Fundamentals of Database Systems, 5th edition. Addison-Wesley, 2007. ISBN 978-03-213-6957-4. [2] SILBERSCHATZ, A., KORTH H. F., SUDARSHAN S. Database System Concepts, 5th edition, New York: McGraw-Hill, 2006. ISBN 978-0-07-295886-7 [3] CONOLLY, T., BEGG, C., HOLOWZAK R. Profesionální průvodce tvorbou databází. Praha: Computer Press, a. s., 2009. ISBN 978-80-251-2328-7. [4] HERNANDEZ, M., J. Návrh databází. Praha: Grada, 2006. ISBN 80-247-0900-7. [5] POKORNÝ, J. Databázová abeceda. Veletiny: Science, 1998, ISBN 80-86083-02-2. [6] POKORNÝ, J., HALAŠKA, I. Databázové systémy, 2. vydání. Praha Vydavatelství ČVUT, 2003, ISBN 80-01-02789-9.
