- Stahuj zápisky z přednášek a ostatní studijní materiály
- Zapisuj si jen kvalitní vyučující (obsáhlá databáze referencí)
- Nastav si své předměty a buď stále v obraze
- Zapoj se svojí aktivitou do soutěže o ceny
- Založ si svůj profil, aby tě tví spolužáci mohli najít
- Najdi své přátele podle místa kde bydlíš nebo školy kterou studuješ
- Diskutuj ve skupinách o tématech, které tě zajímají
Studijní materiály
Zjednodušená ukázka:
Stáhnout celý tento materiálDatabázové systémyDBI025 Antonín Říha
KSI MFF UK
http://www.ms.mff.cuni.cz/~riha/dbs1.htm
Databázový systém =
databáze
systém řízení databáze
správce (administrátor) databáze
Ryan N. and Smith D. :
Database systems engineering.
Int. Thomson Computer Press, London, 1995:
Databáze je logicky uspořádaná (integrovaná) kolekce navzájem souvisejících dat. Představuje model nějaké oblasti zájmu, často nazývané univerzem diskurzu. Obsah kolekce je takový, že podporuje ty rysy univerza diskurzu, které jsou relevantní pro definovanou množinu účelů (sadu aplikací).
Databáze je sebevysvětlující, protože data jsou uchovávána společně s popisy, známými jako metadata.
Systém řízení databáze je obecný softwarový systém, který řídí sdílený přístup k databázi, a poskytuje mechanizmy pomáhající zajistit bezpečnost a integritu uložených dat.
Základní důvod existence
sdílení dat
zajištění bezespornosti
znovuvyužitelnost dat
snížení celkového objemu dat
Uživatelské (externí)
schéma Uživatelské (externí)
schéma Konceptuální schéma Interní (fyzické)
schéma Uživatelské (externí)
schéma Uživatelské (externí)
schéma Logické schéma Interní (fyzické)
schéma Konceptuální schéma Databázové systémy pro formátovaná data dnes ještě většinou - relační systémy
jedna z úspěšných aplikací teorie
relace = podmnožina kartézského součinu
relace = abstrakce ze souborů resp. tabulek
Přínosy relační technologie nezávislost na implementaci
neutrálnost vůči přístupu
jednoduchost
neprocedurální jazyky Návrh databází- konceptuální modelování Chen P.: The Entity-Relationship Model - Towards a Unified View of Data. ACM TODS, Vol. 1, No. 1, March 1976.
datová analýza
účast uživatele na návrhu systému
popř. charakteristické dotazy a charakteristické aktualizace
Entitní typ Entitní typ s atributem Entitní typ s identifikátorem Entitní typ s víceatributovým identifikátorem (0,1) Entitní typ s nepovinným
atributem (1,n) Entitní typ s vícehodno-tovým atributem Entitní typ se složeným atributem
Vztahový typ Typ binárního vztahu různá formalizace dle potřeb osoba město osoba bydlí město Typ binárního vztahu s kardinalitami (1,1) (0,n) Další možnosti kardinalit:
(0,1), (1,n), zřídka (m,n)
Povinné a nepovinné členství ve vztahu
Identifikátor vztahu E1 je slabý entitní typ, identifikačně závislý na E2 – jeho identifikátor je - je to tzv. smíšený identifikátor
identifikační závislost implikuje existenční závislost (0,n) (1,1) E1 E2 A1 Id2 (0,n) (0,n) Pracovník Úkol P_Ú (0,n) (1,1) Pracovník plní Úkol
(0,n) (1,1) je plněn P_Ú
náhrada vztahu se složitou kardinalitou: P_Ú = tzv. průnikový typ Výrobek id_výrobku cena materiál (1,n) cena id_výrobku Výrobek id_výrobku materiál Složení náhrada vícehodno-tového atributu entity učitel kurz (0,n) (1,n) u_k max_st semestr (1,n) vícehodnotový atribut vztahu nahradíme samostatným entitním typem (vztah učitel: kurz ovšem zůstane zachován) : IdU IdK IdU IdK semestr nabídka rekurzívní binární vztahy: r_č zaměstnanec z_v podř (0,1) nadř (1,n) (0,n) (0,n) (0,n) kurz termín místo IdK budova místnost den hodina ktm ternární vztah: rekurzívní ternární vztah: r_č osoba rodina dítě (0,1) muž (0,n) žena (0,n) osoba_na_fakultě Rč student učitel zaměstnanec (t,e) další možnosti podtypů: (p,o),(t,o),(p,e)
vznik hierarchie: generalizace,specializace ISA-vztah, dědičnost: E0 E1 E2 R1 R2 E3 E4 A01 A02 A11 A21 (x,y) E0 R1 R2 E3 E4 A01 A02 A11 A21 (0,y) (0,1) (0,1) Atype E1 E2 R11 R2 E3 E4 A01 A02 A11 A21 (x,y) R12 A01 A02 E0 E1 E2 R1 R2 E3 E4 A01 A02 A11 A21 (x,y) RG1 RG2 (0,1) (0,1) (1,1) (1,1) Základní pravidla převodu ER -> RDM:
silný entitní typ –> tabulka; identifikátor –> klíč
slabý entitní typ –> tabulka; identifikátor smíšený popř. externí -> klíč
vztahový typ – obecně – opět tabulka; klíč = identifikátory zúčastněných entitních typů – ale lze „ušetřit“ (1,1) (1,1) auto osoba IdA IdO A_O AtrA AtrO A_O AtrO….. IdO AtrA….. IdA AtrO….. IdO AtrA….. IdA A_O 1. 2. (1,1) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO IdO AtrA….. IdA auto (0,1) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto IdO IdA A_O (0,n) (1,1) auto osoba IdA IdO A_O AtrA AtrO osoba IdA AtrO….. IdO AtrA….. IdA auto všechna auta – kvůli atributům (1,n) také (0,n) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto IdO IdA A_O (1,n) také (0,n) (0,n) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto | | IdA|IdO A_O (1,n) (1,n) Ternární vztah – obecný případ – všechny kardinality (0,n):
3 tabulky pro zúčastněné entitní typy a
tabulka pro vztah – s klíčem=sjednocení klíčů
a35 (1,n) (1,n) (1,1) (0,n) a2 E1
E3 E2 E4 E5 R35 id1 a3 id4 a5 (1,n) (1,1) (t,e) R24 (1,1) R45
Id2
Id1 a12
(1,1)
(1,n)
(1,n)
E1
E2 R12 Oddělení Zaměstnanec Úřad (1,n) (1,n) (1,1) (1,1) IdZ
X
Y
Z
(1,n)
(1,1)
b Příklad relace:
Jestliže M1={1,2,...,9}, M2={a,b,...,z}, M3={1.5,7.2,..}, pak 5-ární relace nad M1, M2, M1, M2, M3 je ( každá ) podmnožina kartézského součinu M1xM2xM1xM2xM3 , tedy množina uspořádaných pětic, např.{ , , … }.
Algebraický pojem relace - modifikován podle tabulek a podle datového typu záznam, tj. místo číslování sloupců tabulky ( složek kartézského součinu ) - jak tomu bylo na začátku i v relačním datovém modelu - se používá selektorů, kterým se zde říká atributová jména.
Každá n-tice ( řádek ) je místo např. < 1, z, 2, q, 7.2 > chápána jako > či ještě lépe {,,,,}, tj. jako množina uspořádaných dvojic –
tedy funkce. Přizpůsobení jde ještě dále - budeme rozlišovat "deklaraci typu" a " hodnotu daného typu" - tj. v databázové terminologii schéma n-ární relace a n-ární relaci nad schématem.
Schéma n-ární relace je konečná množina tzv. atributů, např. S = { ( A1 : M1), ( A2: M2), ... , ( An: Mn) }, kde Ai jsou atributová jména a Mi domény.
(N-ární) relace nad schématem S (nebo instance relace) je {t; t = { < A1 , m1>, < A2, m2>, ... , < An, mn> }, miÎMi , i=1,...,n}.
Relační schémata však musíme upřesnit tzv. závislostmi. Závislosti jsou obecně formule predikátové logiky prvního řádu vypovídající o přípustných hodnotách instancí nad daným schématem, níže se však omezíme na tzv. funkční závislosti.
Nechť A= { A1,...,An } je relační schéma, X,Y podmnožiny A. Řekneme, že Y funkčně závisí na X, značíme X->Y, jestliže pro každou instanci r nad A platí:
t1,t2 r: (t1[X]= t2[X] t1[Y]= t2[Y]). Nechť A={ A1,...,An } je relační schéma.
Nadklíčem schématu A rozumíme každou podmnožinu množiny A, na níž A funkčně závisí.
Klíčem schématu A je takový nadklíč schématu A, jehož žádná vlastní podmnožina není nadklíčem A. Každý atribut, který je obsažen v alespoň jednom potenciálním klíči, se nazývá klíčový, ostatní atributy jsou neklíčové atributy.
Relační schéma je v první normální formě (1NF), jestliže všem atributovým jménům jsou jako domény přiřazeny jednoduché datové typy (hodnoty atributů jsou v rámci databázového systému vnitřně nedělitelné).
Relační schéma A je ve třetí normální formě, jestliže pro každou funkční závislost Y->x, kde Y je podmnožina A a x atribut z A, platí alespoň jedna z následujících podmínek:
1)závislost je triviální, tj. atribut x je obsažen v Y
2)Y je nadklíč schématu A
3)x je klíčový atribut.
Databázové systémyDBI025 Antonín Říha
KSI MFF UK
http://www.ms.mff.cuni.cz/~riha/dbs1.htm
Základní pravidla převodu ER -> RDM:
silný entitní typ –> tabulka; identifikátor –> klíč
slabý entitní typ –> tabulka; identifikátor smíšený popř. externí -> klíč
vztahový typ – obecně – opět tabulka; klíč = identifikátory zúčastněných entitních typů – ale lze „ušetřit“ (1,1) (1,1) auto osoba IdA IdO A_O AtrA AtrO A_O AtrO….. IdO AtrA….. IdA AtrO….. IdO AtrA….. IdA A_O 1. 2. (1,1) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO IdO AtrA….. IdA auto (0,1) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto IdO IdA A_O (0,n) (1,1) auto osoba IdA IdO A_O AtrA AtrO osoba IdA AtrO….. IdO AtrA….. IdA auto všechna auta – kvůli atributům (1,n) také (0,n) (0,1) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto IdO IdA A_O (1,n) také (0,n) (0,n) auto osoba IdA IdO A_O AtrA AtrO osoba AtrO….. IdO AtrA….. IdA auto | | IdA|IdO A_O (1,n) (1,n) Ternární vztah
obecný případ – všechny kardinality (0,n):
3 tabulky pro zúčastněné entitní typy a
tabulka pro vztah – s klíčem=sjednocení klíčů (0,n) (0,n) (0,n) kurz termín místo IdK budova místnost den hodina ktm ternární vztah: odpovídá zjednodušené podobě rozvrhu:
seznam přednášek – IdK např. DBI025
seznam termínů – vlastně dny x vyuč. hodiny
seznam míst – např . M1=budova+místnost
a rozvrh – Út 11-12 M1 DBI025
dostáváme tedy 4 tabulky
kde v rozvrhu: < místo,termín> je klíč (0,n) (0,n) (0,1) muž žena dítě IdM IdD IdŽ mdž ternární vztah: zde v ternárním vztahu je klíčem IdD (1,n) (1,n) (1,n) pracovník podnik úkol IdP IdÚ IdPo PÚP ternární vztah: zde je klíčem v PÚP
a35 (1,n) (1,n) (1,1) (0,n) a2 E1
E3 E2 E4 E5 R35 id1 a3 id4 a5 (1,n) (1,1) (t,e) R24 (1,1) R45 [E1(id1),] E2(id1, a2), E3(id1, a3, id4, a5),
E4(id4, id1), E5(id4, a5), R35(id1, a35) E0 E1 E2 R1 R2 E3 E4 A01 A02 A11 A21 (0,1) E0(A01,A02,Atype,A11,A21,A31,A41), E3(A31,A32), E4(A41,A42)
Atype=1 iff (A11 is not null)and(A21 is null)
E0.A41 is not null implies Atype=2 A31 A32 A42 A41 (0,n) (1,1) (0,n) Id2 Id1 a12
(1,1)
(1,n)
(1,n) E1
E2 R12 Id2 Id1
(1,1)
(1,n)
E2 R12 a12
(1,1) EA A12
(1,n) E1 Oddělení Zaměstnanec Úřad (1,n) (1,n) (1,1) (1,1) IdZ
X
Y
Z
(1,n)
(1,1)
b základní tvar definice tabulky je
CREATE TABLE jméno_tabulky(
jméno_sloupce1 datový_typ,
jméno_sloupce2 datový_typ,
.......)
datové typy jsou např. integer, smallint,decimal,
character, varchar, money, serial, float, datetime,
date, interval atd.
integritní omezení:
DEFAULT
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
REFERENCES CREATE TABLE all_candy
(candy_num SERIAL PRIMARY KEY,
candy_maker CHAR(25));
CREATE TABLE hard_candy
(candy_num INT,
candy_flavor CHAR(20) DEFAULT ‘fruit’,
FOREIGN KEY (candy_num) REFERENCES all_candy
ON DELETE CASCADE)
SET NULL| SET DEFAULT| NO ACTION CREATE TABLE my_accounts (
chk_id SERIAL PRIMARY KEY,
acct1 MONEY CHECK (acct1 BETWEEN 0 AND 99999),
acct2 MONEY CHECK (acct2 BETWEEN 0 AND 99999))
CREATE TABLE my_accounts(
chk_id SERIAL PRIMARY KEY,
acct1 MONEY,
acct2 MONEY,
CHECK (0 < acct1 AND acct1 < 99999),
CHECK (0 < acct2 AND acct2 < 99999),
CHECK (acct1 > acct2)) CHECK(jménofALL(SELECT jménof FROM film WHERE režisér=’Troška’))
ALTER TABLE items
ADD (item_weight DECIMAL(6,2)
DEFAULT 2.0 NOT NULL
BEFORE total_price)
ALTER TABLE items ADD CONSTRAINT
CHECK (unit_price < total_price)
U (učitel) nad schématem {KU,PŘ,KA}, kde KU...kód učitele, PŘ...příjmení, KA…katedra
P (předmět) nad schématem {KP,NP,TV,PH}, kde KP...kód předmětu, NP...název předmětu, TV...typ výuky, PH...počet hodin
R (rozvrh) nad schématem {KU,KP,DT,HZ,PO}, kde DT...den v týdnu, HZ...hodina začátku,PO...posluchárna Příjmení (všech) učitelů select PŘ from U popř. select t.PŘ from U t,
Příjmení učitelů katedry algebry
select PŘ from U where KA='Alg',
Názvy vyučovaných předmětů
select NP from P,R where P.KP=R.KP
select NP from P where exists (select * from R where P.KP=R.KP)
Učitelé, kteří nic neučí
select * from U where not exists ( select * from R where KU=U.KU)
(select KU from U) except (select KU from R)
Předměty vyučované KSI i KSVI
select KP from R x where exists (select KU from U where (KA='KSI') and (KU=x.KU)) and exists (select * from R y where (y.KP=x.KP) and exists (select KU from U where (KA='KSVI') and (KU=y.KU))
select KP from R x where KU in ( select KU from U where KA='KSI' )
and exists ( select * from R where KP=x.KP and KU in ( select KU from U where KA='KSVI' ) )
(select KP from R where KU in ( select KU from U where KA='KSI' ))
intersect
(select KP from R where KU in ( select KU from U where KA='KSVI' ))
Kódy učitelů, kteří učí každý den ( z rozvrhu )
select X.KU from R x
where not exists
( select * from R y
where not exists ( select * from R
where (DT=y.DT) and (KU=x.KU )))
Kódy učitelů, kteří učí každý den, kdy učí Novák (kód 358)
select x.KU from R x where not exists
( select * from R y where y.KU='358'
and not exists
( select * from R
where (DT=y.DT)and (KU=x.KU) ) )
Spoje (" přidání jedné hrany")
(select * from SPOJ) union (select t.ZAC,s.KON from SPOJ t, SPOJ s where t.KON=s.ZAC)
popř. select t.ZAC,s.KON from SPOJ t, SPOJ s where t.ZAC=s.ZAC& t.KON=s.KON Ú t.KON=s.ZAC)
v klauzuli SELECT jsme brali zatím buď * nebo seznam jmen atributů
ve FROM seznam jmen relací - nejčastěji jednoprvkový; dále při víceprvkovém - kartézský součin (nutno doplnit na ( přirozené ) spojení) ve WHERE boolský výraz sestávající z podmínek pro selekci: nejčastěji porovnání hodnot atributů nebo porovnání hodnoty atributu s konstantou,
dále [not]exists+vnořený dotaz, což odpovídá výrazu s "in" nebo "not in"
minimální dotaz v SQL musí obsahovat SELECT a FROM - odpovídá to projekci
postupně budeme doplňovat další možnosti a nakonec syntax shrneme odpověď na dotaz zapsaný v SQL na rozdíl od teorie není množina –
pokud to chceme, musíme např.
místo select PŘ from U
napsat select distinct PŘ from U agregační funkce
např. max,min,avg
maximální, minimální resp. průměrná hodnota atributu
pro tabulku ZAM s atributy ČZ,PLAT,ČV -
select max(PLAT) from ZAM
select max(PLAT), min(PLAT), avg(PLAT) from ZAM nelze psát select ČZ, max(PLAT) from ZAM
a očekávat, že dostaneme u maximálního platu číslo zaměstnance, který ten maximální plat má
tento příkaz je syntakticky nesprávný, nelze použít ani na tabulku o jednom řádku musíme psát
select ČZ, PLAT from ZAM where PLAT = (select max(PLAT) from ZAM)
! nelze psát
select ČZ, PLAT from ZAM where PLAT = max (select PLAT from ZAM))
další přípustný zápis
select ČZ, PLAT from ZAM where PLAT >= all (select PLAT from ZAM)
jako "exists" , tak také "all" se podobá kvantifikátoru, má však množinový význam c< >all (select..) je ekvivalentní "c not in (select ....)" a také příslušné podmínce s "not exists„
také call(select..) implikují "c not in (select ....) "
"c = all ( select ....)" by bylo true pouze pokud by select dávalo jen hodnoty c nejčastější použití je "c>=all (select ...)" místo funkce max
další ekvivalence jsou in = some = any count - počet řádků popř. počet různých hodnot atributu
použití v klauzuli SELECT např.
select count (*) from ZAM
což počítá počet řádků tabulky ZAM další možnost je
select count (distinct A) from TAB
v popisech se píše také
select count (A) from TAB
ale ne všude je to akceptováno
v podmínkách
not exists … 0 = (select count(*) ...)
exists … 0 < (select count(*) ...)
podobně jako pro výše uvedené agregační funkce nelze ani pro count použít zápis
count (select ...), ale vždy jen select count ... Další dosud nepovolená konstrukce je porovnání množin ve WHERE, tj. podmínka tvaru
např. (select ....) = (select ...). Ačkoli normy to dovolují, není to povoleno např. ani v Oracle9
Operandy v podmínkách však mohou být select dotazy s jednoprvkovým výsledkem, např. získaným agregační funkcí – ale i "obyčejným" select. select max(PLAT), min(PLAT), avg(PLAT) from ZAM group by ČV
select ČV, max(PLAT), min(PLAT), avg(PLAT) from ZAM group by ČV nelze psát select ČZ from ZAM group by ČV
lze psát select ČZ, max(PLAT) from ZAM group by ČZ ale to ovšem není zajímavé
select ČV from ZAM group by ČV je ekvivalentní zápisu select distinct ČV from ZAM Kódy učitelů, kteří učí každý den, pro který je rozvrh:
select KU from R group by KU
having count( distinct DT ) =
( select count( distinct DT ) from R ) select rč from Návštěva , Lékař where Návštěva.Člic=Lékař.Člic group by rč having count (distinct Návštěva.Člic)=1 and min(jm_lékaře)='Čermák'
KINO(názevk,vedoucí, kapacita), FILM(jménof,rež,rok,země), PROG(jménof, názevk, datum)
select názevk from FILM,PROG where FILM.jménof=PROG.jménof & FILM.rež='XY'
select názevk from PROG where exists (select jménof from FILM where FILM.jménof=PROG.jménof & FILM.rež='XY')
select názevk from PROG where názevf in (select jménof from FILM where FILM.rež='XY') select názevk from PROG where názevf = some (select jménof from FILM where FILM.rež='XY')
select názevk from PROG where 0 < (select count (*) from FILM where (FILM.jménof=PROG.jménof)& (FILM.rež='XY')) select názevk from KINO k where 0 = (select count(*) from FILM, PROG where rež='XY' & FILM.jménof=PROG.jménof & názevk=k.názevk)
select názevk from KINO k where 0 = (select count(*) from FILM, PROG where rež'XY' & FILM.jménof=PROG.jménof & názevk=k.názevk) select názevk k from PROG where not exists (select * from FILM where rež='XY' and not exists (select * from PROG where názevk=k.názevk & jménof=film.jménof))
select jménof from PROG group by jménof having count(distinct názevk) = (select count(distinct názevk) from PROG) select jménof,sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof having sum(kapacita)>= all (select jménof,sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof ) O(bjednávka)(čís_o, rodč, …), Z(ákazník)(rodč, …)
select rodč, count(distinct čís_o) from O x group by rodč having count(*) > all (select count(distinct čís_o) from O where rodčx.rodč group by rodč)
za dotaz lze napsat order by
což znamená uspořádání dle jednoho či více atributů vzestupně či sestupně
lze i různě pro různé atributy; ASC je default;
Databázové systémyDBI025 Antonín Říha
KSI MFF UK
http://www.ms.mff.cuni.cz/~riha/dbs1.htm
http://www.w3schools.com/sql/default.asp - SQL tutorial
http://sqlcourse.com/ - online course
http://www-dbs.inf.ethz.ch/~isk/WS_97/syntax_diagramme/ - syntax - Prof. Dr. H.-J. Schek – ETH Zurich
http://developer.mimer.com/va
Vloženo: 24.04.2009
Velikost: 1,03 MB
Komentáře
Tento materiál neobsahuje žádné komentáře.
Copyright 2024 unium.cz