Databázové systémy Cvičení 1
Databázová technologie • databázová technologie – zabývá se řízením velkého množství perzistentních, spolehlivých a sdílených dat
• databáze – soubor informací (znaky, čísla, diagramy, ...), jejichž systematická struktura umožňuje vyhledávat tyto informace pomocí počítače
• velké množství – nevejdou se do operační paměti
• perzistentní – přetrvávající (na diskových pamětech)
• spolehlivé – odolné proti výpadkům systému • integrita – zajištění konzistence • zajištěno např. pomocí transakcí
– odolné proti neoprávněnému přístupu (bezpečnost - security) • autentizace a autorizace
Další vlastnosti dat v databázích • neredundance – prvky v databázi se zbytečně neopakují – ne vždy lze dosáhnout
• nezávislost – programy přistupující k datům jsou nezávislé na tom, jak jsou data uložena
Databázový systém • DBS - databázový systém
DBS = SŘBD + DB • SŘBD - Systém řízení báze dat – software
• DB - databáze – vlastní data
Databázový systém • SŘBD (DBMS) – česky: systém řízení báze dat – anglicky: Database Management System – databázový software (DB stroj, DB engine) • IBM DB2, Oracle, Informix, MS SQL Server, MySQL, SyBase, PostgreSQL
Základní témata db technologie • organizace dat, souborů, metody přístupu • paralelní přístup (sdílení)
OS distribuované systémy
• transakční zpracování • konzistence dat • optimalizace dotazů • databázové jazyky • projektování dat (modelování)
speciální pro DB technologie
Poznámky: • optimalizace dotazů – snaha o co nejefektivnější zpracování dotazů (prohození operací, ... )
• konzistence dat - logická správnost – data odpovídají realitě, např. v DB není zaznamenáno datum 32.13.2005
DBS • počátky DB systémů – aplikace programovány „na míru“
• zlom v polovině 60. let a v 70. letech odtržení dat a programu • základní paradigma databázové technologie – existence dat je nezávislá na programech, které s ní pracují
Uživatelé databáze • správce dat • programátor • uživatel – pokročilý uživatel, který umí třeba i SQL
• naivní uživatel – zejména dnes, kdy jsou k dispozici názorná webová grafická rozhraní
Modelování • model – souhrn pojmů (vztahů) popisujících reálný svět
• příklad: – matematický model • např. soustavy lineárních rovnic, diferenciálních rovnic, atd. První vlak vyjíždí ze stanice A průměrnou rychlostí 60 km/h, druhý vlak proti němu ze stanice B vzdálené 100 km průměrnou rychlostí 80 km/h o 30 minut později. Vypočtěte vzdálenost od stanice A, kde se vlaky setkají.
• • • •
s = 100 km, v1 = 60 km/h, v2 = 80 km/h ujetá dráha 1. vlaku s1, ujetá dráha 2. vlaku s2 čas jízdy 1. vlaku t1, čas jízdy 2. vlaku t2 matematický model - soustava lineárních rovnic
s = s1 + s2 , s1 = v1t1 , s2 = v2t2 t2 = t1 - 0,5 s = v1t1 + v2(t1-0,5)
Modelování v databázových systémech Připomeňme si: • základní paradigma databázové technologie – existence dat je nezávislá na programech, které s ní pracují
Co k realizaci paradigma potřebujeme? – „univerzální“ (obecný) SŘBD – konceptuální model • platformově nezávislý model
– databázový model • výstupem databázového modelování je schéma konkrétní databáze
– jazyk pro definici dat (JDD) • DDL – Data Definition Language • aneb musíme nějak SŘBD sdělit, jak bude databáze vypadat, tj. popsat formálním jazykem schéma db
– jazyk pro manipulaci dat (JML) • DML – Data Manipulation Langage • s daty se musí manipulovat – (vkládat – mazat, vyhledávat., …
– součástí jazyka bývá i řízení transakcí – dnes tuto úlohu zastává dotazovací jazyk SQL (Structured Query Language)
Druhy modelů používané v DBS 1. konceptuální model – model vytvořený na základě úplného odproštění od implementace – účel modelu: srozumitelnost i pro laiky v oboru výpočetní techniky •
na přípravě schématu DB se mohou podílet i uživatel (zákazník), pro nějž se DB tvoří (ostatní řídicí pracovníci)
– u konceptuálního modelu by měl platit princip 100% •
postihnout v modelu všechny požadavky uživatele
2. databázový model – DB model, který bere v úvahu implementaci – modely: •
síťový – data v modelu provázána tak, aby je bylo možné efektivně zpracovat v souborech se sekvenčním přístupem (páska)
• •
hierarchický relační – DB modelována (a realizována) pomocí provázaných relací (tabulek)
• • •
objektově-relační, objektově orientovaný databáze XML, NoSQL, grafové, …
Historie databází Síťový model • 1965 – konference CODASYL (Conference on Data System Languages) • výbor DataBase Task Group • tvorba síťového modelu a koncepce DB systému
– vznikaly SŘBD pro sálové počítače – IDMS, IDS (Integrated Data Store od General Electric) – postupná aktualizace síťového modelu 1971, 1973, 1981, 1984, 1987
Historie databází Hierarchický model • nemá oficiální standard • první SŘBD byl IMS (Information Management System) od IBM a North American Aviation (Rockwell) v 60. letech – dodnes prý existuje verze 13 podporovaná IBM pro sálové počítače
Historie databází Relační a objektový model • první teoretický článek r. 1970 E.F.Codda – zdůraznil logický pohled na data
• 10 trval vývoj k reálně použitelné relační technologii • objektové databáze – 1989 • objektově – relační databáze –konec 90. let – SQL 99
Konceptuální modely E-R model • vytvořen Peterem Chenem v r.1976 • E-R znamená: Entity – Relationship: – The Entity Relationship Model: Toward a Unified View of Data
• česky: model entit a vztahů • definuje grafické zobrazení pomocí ustálených elementů
E-R model
E-R model • E-R model používá k modelování reálného světa tyto základní pojmy (konstrukty) – entita • slabý entitní typ
– vztah – atribut – identifikátor – kardinalita – povinnost – ISA vztah
• entita – zpravidla modeluje objekt reálného světa (např. čtenář), měla by být samostatně vyčlenitelná, identifikovatelná, odlišitelná od jiných entit, schopná nezávislé existence – je popsána pomocí hodnot svých vlastností – ve větách zpravidla vystupuje jako podmět – identifikována jménem (čtenář) – př.: čtenář s vlastnostmi: • jméno: Vít • příjmení: Fábera • datum narození: 5.5.1975
– entitní typ • abstrakce popisující typ entit • čtenář s vlastnostmi jméno, příjmení, datum nar. • Vít Fábera, 5.5.1975 je výskyt, nebo též instance entity
– často se pojmy entita, entitní typ zaměňují, pod pojmem entita se myslí ve skutečnosti entitní typ
• vztah – vyjadřuje vztah mezi dvěma nebo více entitami (je to prvek kartézského součinu mezi entitami) – vyjádřen slovesem, přísudkem – např. vztah mezi entitami čtenář, exemplář: • čtenář si vypůjčil exemplář
• atribut – učeně: funkce f definovaná na množině entit nebo vztahů s funkčními hodnotami z množiny hodnot (z univerza)
– srozumitelněji: vlastnosti entit a vztahů
• příklad: – možné atributy • u entity čtenář: – jméno, příjmení, rodné číslo, …
• u vztahu si vypůjčil: – datum výpůjčky
– atribut jméno: univerzum – množina všech řetězců představujících reálná jména
atribut příjmení
2 entity: František
Kuldanů
320612/1234
Josef
Novák
521006/5678
zde přiřazuje funkce atributu příjmení entitě č. 2 hodnotu Novák
• pro záznam E-R modelu existuje ustálená grafická notace v Chenově modelu – entity – obdélník – vztahy – čtverec „postavený na vrchol“ – atributy – „kolečko“, ovál
• grafickou notaci si budeme ukazovat na příkladu databáze knihovny • knihovna je každému srozumitelná
• existují varianty grafického zobrazení „směrem“ k UML – entity – obdélník – vztahy – čára – atributy – zapisují se do obdélníku k entitě stejně jako atributy třídy v objektově orientovaném přístupu
entita
atribut
RČ JMÉNO
ČTENÁŘ
PSČ
SI_REZERVOVAL
SI_VYPŮJČIL
DATUM_REZ
DATUM_VÝP
KNIHA
EXEMPLÁŘ JE_OD PŘÍR_Č
CENA
D_NÁK
(přírůstkový katalog)
TITUL AUTOR ISBN
vztah
(autorský katalog)
entita
atribut
RČ
ČTENÁŘ
JMÉNO PSČ
SI_VYPŮJČIL
SI_REZERVOVAL DATUM_REZ
DATUM_VÝP
KNIHA
EXEMPLÁŘ JE_OD PŘÍR_Č
CENA
D_NÁK
(přírůstkový katalog)
TITUL AUTOR ISBN
vztah (autorský katalog)
Poznámka č. 1: • metodologické zásady – pojmenování entity • podstatné jméno v jednotném čísle (např. KLIENT, ČTENÁŘ, UČITEL)
– pojmenování vztahu • sloveso (např. MÁ, JE_OD)
Poznámka č. 2: • v realizované databázi bude zaznamenáno: – u každého exempláře pouze přírůstkové číslo, cena a datum nákupu a nějakým způsobem (podle typu databázového modelu) vztah (odkaz) na příslušnou knihu
Poznámka č. 3: • teoreticky v databázi dle tohoto schématu může mít kniha pouze jednoho autora – prakticky bychom zapsali do políčka Autor více jmen např. oddělených čárkou, což může ale způsobovat obtíže při hledání, výpisu všech exemplářů od daného autora apod. – řešení 1. zavedení vícehodnotového atributu (oborem hodnot je množina) 2. zavedení nové entity AUTOR a vztahu NAPSAL mezi entitami KNIHA a AUTOR – nejlepší řešení
NAPSAL
KNIHA
AUTOR
JMÉNO
PŘÍJMENÍ
TITUL
ISBN
Nástroje pro tvorbu modelů E-R • lze je kreslit v ruce i v jakémkoliv grafickém editoru • existují lepší nástroje (case nástroje) – Microsoft Visio – Enterprise Architect – Oracle SQL Developer Data Modeler – Case Studio (Quest Software) – a další datové modeláře …
Integritní omezení (IO) • tvrzení o objektech (neboli pravidla) – čtenář nesmí mít půjčeno více než 10 knih – čtenář si nesmí rezervovat exemplář, jestliže je exemplář v knihovně – zásoba součástky nesmí klesnout pod 5 ks – minimální zůstatková částka na účtu je 500 Kč – RČ je klíčem entity ČTENÁŘ • Klíč – atribut jednoznačně identifikující entity, v E-R modelu označen podtržením
Kardinalita vztahu • typ integritního omezení, omezuje počet výskytů entit v jednotlivých vztazích • kardinalita: (1:N) • jedna entita může být zapojena pouze 1x, ostatní libovolným počtem
(M:N) • libovolný počet výskytů u entit
(1:1) • každá entita může být zapojena ve vztahu pouze jedenkrát
RČ JMÉNO
ČTENÁŘ
PSČ
1
M SI_REZERVOVAL
SI_VYPŮJČIL
DATUM_REZ
DATUM_VÝP
N
N
EXEMPLÁŘ
N
1
KNIHA
JE_OD PŘÍR_Č
CENA
D_NÁK
TITUL AUTOR ISBN
SI_VYPUJCIL (1:N) – čtenář si může vypůjčit více exemplářů, exemplář může být vypůjčen pouze jedním čtenářem 1
N Babička přír. číslo 100
Josef Novák RČ: 521006/5678
František Kuldanů RČ: 320612/1234
U nás přír. číslo 58 Hřbitovní kvítí přír. číslo 1005 Babička přír. číslo 101
SI_REZERVOVAL (M:N) – čtenář si může rezervovat více knih (titulů), jedna kniha může být rezervována v daném okamžiku více čtenáři M N Babička ISBN: 80-85190-38-9 Josef Novák RČ: 521006/5678 Učebnice jazyka C ISBN: 80-85828-02-2
František Kuldanů RČ: 320612/1234
Bylo nás pět ISBN: 80-00-00076-8 Divá Bára ISBN: 9788000005027
• příklad vztahu 1:1 – občan – karta zdravotní pojišťovny • každý občan má jen jednu kartu zdravotní pojišťovny, karta je určena pouze pro jednoho občana
Kardinalita vztahu typu min-max • jemnější popis kardinality pro binární vztahy • specifikuje minimální a maximální výskyt entity ve vztahu
• zápis: R(E1(min,max),E2(min,max)) vztah
entita
entita
• příklad: SI_VYPUJCIL(ČTENÁŘ(0,n),EXEMPLÁŘ(0,1)) – čtenář nemusí mít vypůjčen žádný exemplář, může mít vypůjčeno více exemplářů; exemplář nemusí být vypůjčen, může být vypůjčen pouze jedním čtenářem
• příklad: JE_OD(EXEMPLÁŘ(1,1),KNIHA(0,n)) – exemplář musí být od nějaké knihy (musí být zapojen povinně do vztahu) a musí být maximálně od jedné knihy; kniha nemusí mít v knihovně žádný exemplář (všechny jsou zničené nebo ukradené, ale kniha zůstává v autorském katalogu, protože se plánuje nákup dalších výtisků) a může mít více exemplářů
Otázka: Jakou kardinalitu by měl vztah NAPSAL mezi entitami KNIHA a AUTOR?
RČ JMÉNO
ČTENÁŘ
PSČ
(0,n)
(0,m) SI_REZERVOVAL
SI_VYPŮJČIL
DATUM_REZ
DATUM_VÝP
(0,1)
(0,n)
EXEMPLÁŘ
(1,1)
(0,n)
KNIHA
JE_OD PŘÍR_Č
CENA
D_NÁK
TITUL AUTOR ISBN
Poznámky: – entita EXEMPLÁŘ má povinný výskyt ve vztahu (exemplář nemůže existovat bez knihy) – exemplář je existenčně závislý na knize
EXEMPLÁŘ
(1,1)
JE_OD
(0,n)
KNIHA
• n-ární vztahy – vztah může být definován i mezi větším počtem entit, event. entita může být zastoupena ve vztahu vícekrát
DODÁVÁ
OBCHODNÍK
ZBOŽÍ
• postihuje situaci, kdy obchodník dodává zboží jinému obchodníkovi • arita vztahu = 3 • v příkladu s knihovnou jsme měli pouze binární vztahy
• atribut může být i složený, neatomický – adresa (ulice, č.p., místo, PSČ)
Slabý entitní typ – některé entity nemohou být jednoznačně identifikovány svým klíčem, potřebují k identifikaci klíč jiné entity – jsou to tzv. slabé entitní typy a jsou identifikačně závislé na jiném entitním typu – značí se obdélníkem s dvojitou čarou KOPIE
ČÍSLO_KOPIE
(1,1)
JE_OD
(0,n)
FILM
JMÉNO
Slabý entitní typ • Příklad: – v kině máme tři kopie filmu Amadeus s čísly kopie 1,2,3 a dvě kopie filmu Piano s čísly 1,2 – k jednoznačné identifikaci kopie tedy nestačí její číslo, je nutné připojit identifikaci filmu, od kterého je příslušná kopie
Databázové modely Relační model dat • tvůrce: Codd 1970 • DB modelována pomocí relací (= tabulek), logicky provázaných – formální abstrakce nejjednodušších souborů
• efektivně použitelný pro soubory s přímým přístupem (diskové paměti) • většina současných DBS jsou relační, resp. objektově relační – definujeme-li novou databázi, zadáváme relační schéma (transformace E-R model → relační schéma)
Relační model dat • E.F. Codd si všiml souvislosti mezi tabulkami a matematickým pojmem relace • matematický aparát relační algebra a kalkul vypracovaný nad relacemi dal teoretický základ pro dotazy a JMD • naplňoval ideje pro databáze: – oddělení dat chápaných jako relace od implementace – při manipulaci se nezajímáme o přístupové mechanismy k datům – pro omezení redundance jsou definovány tzv. normální formy relací
Relace •
mat.: podmnožina kartézského součinu
A = {X, Y}, B = {1,2,3} kartézský součin: A×B A × B = {(X,1),(X,2),(X,3),(Y,1),(Y,2),(Y,3)}
relace R: R⊂A×B R = {(X,1),(Y,1)}
A = {1, 2, 3}, B = {1,2,3} •
kartézský součin:
A×B={(1,1),(1,2),(1,3),(2,1),(2,2),(2,3),(3,1),(3,2),(3, 3)} •
binární relace „menší“ <: < = {(1,2),(1,3),(2,3)}
•
píšeme: (1,2) ∈ <, resp. 1 < 2
Relace R nad množinou atributů A = {A1:D1,…,An:Dn} je podmnožina kartézského součinu D1 × D2 × … × Dn • Ai jsou jména atributů • Di jsou domény • n je arita relace • R(A1:D1,…,An:Dn) je schéma relace • prvky relace jsou n-tice (a1,a2,…,an) • ai je hodnota atributu Ai z domény Di
Příklad: • množina atributů – {RČ, JMÉNO, PŘÍJMENÍ}
• domény – množina všech platných rodných čísel – množina všech řetězců (platných jmen) – množin všech řetězců (platných příjmení)
• schéma relace ČTENÁŘ: ČTENÁŘ(RČ:STRING(11), JMÉNO:STRING(80), PŘÍJMENÍ:STRING(80))
Příklad: – zkrácený zápis, bez domén ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ)
Prvky relace ČTENÁŘ jsou trojice
Tabulková terminologie • • • • •
relace schéma relace arita relace jméno atributu n – tice
= = = = =
tabulka záhlaví tabulky počet sloupců jméno sloupce řádek tabulky
• příklad konkrétní relace ČTENÁŘ – zde dvouprvková (obsahuje dvě trojice, dva řádky):
Příklad konkrétní relace ČTENÁŘ • zde dvouprvkové (obsahuje dvě trojice, dva řádky): 320612/1234 František Kuldanů 521006/5678 Josef Novák
Schéma relační databáze • je dvojice (R,I), kde R je množina schémat relací (R1,…,Rk) a I je množina integritních omezení • integritní omezení lze definovat jako podmínky, např: Atribut rodné číslo má tvar RRMMDD/XXXX, kde DD >= 01 a DD < 31 MM >= 01 a MM < 12 nebo MM>= 51 atd.
Schéma relační databáze • přípustná relační databáze se schématem (R,I) je množina relací R1,…,Rk takových, že jejich prvky vyhovují integritním omezením
Kandidát na klíč • množina atributů relace K: 1. hodnoty K identifikují prvky relace 2. neexistuje K´⊂ K, že K´ splňuje 1 (minimalita, tj. žádný atribut v K není nadbytečný)
•
primární klíč (PRIMARY KEY) – jeden vybraný kandidát na klíč
Poznámka: v relacích často zavádíme „umělý“ primární klíč – např. pořadové číslo
Transformace E-R modelu do relačního modelu • entita – vytvoříme relaci se shodnými atributy jako má entita – zvolíme primární klíč (nutné, pokud je entita zapojena ve vztahu)
• vztah M:N – vytvoříme relaci s atributy daného vztahu, přidáme atributy odpovídající primárním klíčům entit, které jsou ve vztahu zapojeny
– atribut odpovídající primárnímu klíči entity se nazývá cizí klíč (FOREIGN KEY) 1:N – buď: stejným způsobem jako vztah M:N – nebo: u entity, která má maximální výskyt ve vztahu roven 1, přidáme do její relace atribut odpovídající primárnímu klíči druhé relace (vhodné pro povinný výskyt ve vztahu) 1:1 – u jedné entity přidáme atribut odpovídající primárnímu klíči relace druhé entity
RČ JMÉNO
ČTENÁŘ
PŘÍJMENÍ
1
M SI_REZERVOVAL
SI_VYPŮJČIL
DATUM_REZ
DATUM_VÝP
N
N
EXEMPLÁŘ
N
1
KNIHA
JE_OD PŘÍR_Č
CENA
D_NÁK
TITUL AUTOR ISBN
RČ JMÉNO
ČTENÁŘ
PSČ
(0,n)
(0,m) SI_REZERVOVAL
SI_VYPŮJČIL
DATUM_REZ
DATUM_VÝP
(0,1)
(0,n)
EXEMPLÁŘ
(1,1)
(0,n)
KNIHA
JE_OD PŘÍR_Č
CENA
D_NÁK
TITUL AUTOR ISBN
Příklad: Relační schéma DB knihovny ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ) KNIHA(ISBN, TITUL, AUTOR) EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK) JE_OD(PŘÍR_Č, ISBN) cizí klíče SI_VYPŮJČIL(RČ, PŘÍR_Č, DATUM_VÝP) SI_REZERVOVAL(RČ, ISBN, DATUM_REZ)
• eventuální realizace vztahu JE_OD: – nebude relace JE_OD, místo ní bude mít relace EXEMPLÁŘ podobu: EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK, ISBN) cizí klíč
• eventuální realizace vztahu SI_VYPŮJČIL: – místo relace SI_VYPŮJČIL bude relace: EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK,RČ)
Poznámka: – pokud není exemplář vypůjčen, hodnota atributu RČ není v příslušném záznamu definována • co bude ve skutečnosti uloženo v DB? – hodnota NULL, reprezentující prázdnou hodnotu
– toto není vhodné řešení, pro realizaci vztahu je zde lépe mít samostatnou relaci
ČTENÁŘ RČ
JMÉNO
PŘÍJMENÍ
320612/1234
František
Kuldanů
521006/5678
Josef
Novák
KNIHA ISBN
TITUL
AUTOR
80-11111-22-3
U nás
A. Jirásek
80-85190-38-9
Babička
B. Němcová
EXEMPLÁŘ PŘÍR_Č
CENA
D_NÁK
1
100
25.3.1990
2
100
25.3.1990
3
150
26.3.1990
JE_OD PŘÍR_Č
ISBN
1
80-85190-38-9
2
80-85190-38-9
3
80-11111-22-3
SI_VYPŮJČIL RČ
PŘÍR_Č
DAT
320612/1234
1
10.11.2005
521006/5678
2
20.11.2005
Realizace vztahů 1:N bez relací SI_VYPŮJČIL, JE_OD: ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ) KNIHA(ISBN, TITUL, AUTOR) EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK,ISBN,RČ,DATUM_VÝP) SI_REZERVOVAL(RČ, ISBN, DATUM_REZ)
ČTENÁŘ RČ
JMÉNO
PŘÍJMENÍ
320612/1234
František
Kuldanů
521006/5678
Josef
Novák
KNIHA ISBN
TITUL
AUTOR
80-11111-22-3
U nás
A. Jirásek
80-85190-38-9
Babička
B. Němcová
EXEMPLÁŘ PŘÍR_Č
CENA
D_NÁK
ISBN
RČ
DATUM_VÝP
1
100
25.3.1990
80-85190-38-9
320612/1234
10.11.2005
2
100
25.3.1990
80-85190-38-9
521006/5678
20.11.2005
3
150
26.3.1990
80-11111-22-3
