Datab´ azov´ e syst´ emy
Norm´ aln´ı formy, referenˇ cn´ı integrita, n´ avrh datab´ aze, interakce s ostatn´ımi jazyky Petr Krajˇca
Katedra informatiky Univerzita Palack´eho v Olomouci
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
1 / 33
Motivace pro normalizaci SCHOOL
DEAN
DEPT
HEAD
ID
COURSE
YEAR
SCI SCI SCI SCI SCI SCI SCI SCI
Adams Adams Adams Adams Adams Adams Adams Adams
AF AF AF CS CS CS CS CS
Black Black Black Davis Davis Davis Davis Davis
7 8 8 3 3 6 6 6
QOPT1 LASR1 LASR1 ALMA1 ALMA1 DATA1 DATA1 PAPR1
2012 2012 2013 2012 2013 2012 2013 2012
Probl´ emy redundance dat (zbyteˇcn´a duplikace hodnot) anom´ alie spojen´ a s v´ ymazem dat (v´ymaz kurz˚ u katedry ,,odstran´ı vedouc´ıho”) anom´ alie spojen´ a s aktualizac´ı hodnot (zmˇena vedouc´ıho katedry na v´ıc m´ıstech)
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
2 / 33
Opakov´an´ı: 1. NF A table is in first normal form (1NF)—equivalently, such a table is normalized—if and only if it’s a direct and faithful representation of some relation. — C.J.Date Prvn´ı norm´ aln´ı forma (1NF) Tabulka je v prvn´ı norm´aln´ı formˇe, pokud splˇ nuje vˇsechny n´asleduj´ıc´ı podm´ınky: 1
neuvaˇzuje se ˇ z´ adn´ e uspoˇr´ ad´ an´ı ˇr´ adk˚ u (shora-dol˚ u)
2
neuvaˇzuje se ˇ z´ adn´ e uspoˇr´ ad´ an´ı sloupc˚ u (zleva-doprava)
3
v tabulce se nevyskytuj´ı duplicitn´ı ˇr´ adky
4
v kaˇzd´em vnitˇrn´ı poli tabulky je pr´ avˇ e jedna hodnota dan´ eho typu
5
vˇsechny atributy jsou regul´ arn´ı (neobsahuj´ı ˇz´adnou skrytou informaci, kter´a je dostupn´a pouze prostˇrednictv´ım speci´aln´ıch funkc´ı)
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
3 / 33
2. NF Mˇejme relaˇcn´ı sch´ema R, teorii Γ a mnoˇzinu kl´ıˇc˚ u K1 , . . . , Kn R vzhledem k Γ. Pak relaˇcn´ım sch´ema R je ve druh´ e norm´ aln´ı formˇ e (2.NF) vzhledem k Γ, jestliˇze 1
je v 1. NF,
2
pokud pro kaˇzd´y atribut y ∈ R takov´y, ˇze y 6∈ Ki pro vˇsechna i = 1, . . . , n, plat´ı Γ 6|= K ⇒ {y}, pro vˇsechny K ⊂ Ki , kde i = 1, . . . , n.
Pozn´ amka ˇ adn´y atribut, kter´y nen´ı souˇc´ast´ı kl´ıˇce, nen´ı z´avisl´y na ˇc´asti nˇekter´eho Jin´ymi slovy: Z´ kl´ıˇce. Jinak: Kaˇzd´y atribut, kter´y nen´ı souˇc´ast´ı kl´ıˇce, je z´avisl´y na vˇsech kl´ıˇc´ıch.
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
4 / 33
Pˇr´ıklad INVOICE
DATE
ITEM
PRICE
2014001 2014001 2014001 2014002 2014002
2014-01-30 2014-01-30 2014-01-30 2014-02-20 2014-02-20
Apples Bananas Coconut Apples Bananas
123 100 345 300 500
Funkˇ cn´ı z´ avislosti {INVOICE} ⇒ {DATE} {INVOICE, ITEM} ⇒ {PRICE} INVOICE INVOICE
DATE
2014001 2014002
2014-01-30 2014-02-20
Petr Krajˇ ca (UP)
2014001 ./ 2014001 2014001 2014002 2014002
KMI/YDATA: Pˇredn´ aˇska V.
ITEM
PRICE
Apples Bananas Coconut Apples Bananas
123 100 345 300 500 12. prosinec, 2014
5 / 33
3. NF Mˇejme relaˇcn´ı sch´ema R a teorii Γ. Pak R je ve tˇret´ı norm´ aln´ı formˇ e vzhledem k Γ, pokud R je v 2.NF vzhledem k Γ a ˇz´adn´y nekl´ıˇcov´y atribut y ∈ R nen´ı tranzitivnˇe z´avisl´y na nˇejak´em nadkl´ıˇci relaˇcn´ıho sch´ematu R. Funkˇcn´ı z´avislost A ⇒ C v teorii Γ je tranzitivn´ı, pokud existuje B takov´e, ˇze Γ |= A ⇒ B a Γ |= B ⇒ C, pˇriˇcemˇz Γ 6|= B ⇒ A. Jin´ymi slovy: Atribut C je tranzitivnˇe z´avisl´y na A, pokud existuje B takov´e, ˇze A ⇒ B a B ⇒ C. Pozn´ amky: Nekl´ıˇcov´e atributy z´avis´ı pouze na kl´ıˇci. Moˇzn´a interpretace: Kaˇzd´y nekl´ıˇcov´y atribut mus´ı reprezentovat pouze fakt o kl´ıˇc´ı, cel´em kl´ıˇci a niˇcem jin´em.
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
6 / 33
Pˇr´ıklad ITEM
DEPT ID
DEPT NAME
PRICE
1 2 3 4 5
1 2 1 1 2
books clothes books books clothes
123 100 345 300 500
Funkˇ cn´ı z´ avislosti {ITEM} ⇒ {DEPT ID} {DEPT ID} ⇒ {DEPT NAME} tranzitivn´ı z´avislost {ITEM} ⇒ {DEPT ID} ⇒ {DEPT NAME}
Petr Krajˇ ca (UP)
ITEM
DEPT ID
PRICE
1 2 3 4 5
1 2 1 1 2
123 100 345 300 500
DEPT ID
./ 1 2
KMI/YDATA: Pˇredn´ aˇska V.
DEPT NAME books clothes 12. prosinec, 2014
7 / 33
Anom´alie v 3NF Uvaˇzujme relaˇcn´ı sch´ema {ID DODAVATELE, NAZEV DODAVATELE, PSC, MESTO} s funkˇcn´ımi z´avislostmi: {ID DODAVATELE} ⇒ {NAZEV DODAVATELE}, {ID DODAVATELE} ⇒ {MESTO}, {ID DODAVATELE} ⇒ {PSC}, {NAZEV DODAVATELE} ⇒ {ID DODAVATELE}, {NAZEV DODAVATELE} ⇒ {MESTO}, {NAZEV DODAVATELE} ⇒ {PSC}, kandid´atn´ımi kl´ıˇci jsou {ID DODAVATELE}, {NAZEV DODAVATELE} probl´em: odstranˇen´ım ˇr´adk˚ u s konkr´etn´ım dodavatelem ztrat´ıme informaci o jeho ID a mˇestˇe moˇzn´a dekompozice {ID DODAVATELE, NAZEV DODAVATELE} a {ID DODAVATELE, PSC, MESTO}
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
8 / 33
Boyce-Coddova norm´aln´ı forma (BCNF) Mˇejme relaˇcn´ı sch´ema R a teorii Γ. Pak R je v BCNF vzhledem k Γ, pokud pro kaˇzdou netrivi´aln´ı A ⇒ B ∈ Γ plat´ı, ˇze Γ |= A ⇒ R. Pozn´ amka: Moˇzn´a interpretace: Atribut mus´ı reprezentovat pouze fakt o kl´ıˇc´ı, cel´em kl´ıˇci, a niˇcem jin´em. Normalizace pomoc´ı dekompozice Pokud nen´ı R v BCNF vzhledem k Γ, pak 1 vezmeme netrivi´ aln´ı A ⇒ B ∈ Γ takovou, ˇze Γ 6|= A ⇒ R 2 poloˇ z´ıme R1 = A ∪ B a Γ1 = {C ∩ R1 ⇒ D ∩ R1 | C ∩ R1 ⇒ D ∈ Γ} 3 poloˇ z´ıme R2 = A ∪ (R\B) a Γ2 = {C ∩ R2 ⇒ D ∩ R2 | C ∩ R2 ⇒ D ∈ Γ} 4 proces se pokus´ ıme opakovat pro R1 a Γ1 , pokud nen´ı v BCNF a analogicky pro R2 a Γ2 Pozn´ amka: kaˇzd´e sch´ema ve 3NF je z´aroveˇ n v BCNF; obr´acen´e tvrzen´ı neplat´ı BCNF nemus´ı b´yt dosaˇziteln´a Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
9 / 33
Pˇr´ıklad: Sch´ema, kter´e nen´ı v BCNF Uvaˇzujme relaˇcn´ı sch´ema: R = {SCHOOL, DEAN, DEPT, HEAD, ID, COURSE, YEAR} a teorii Γ popisuj´ıc´ı z´avislosti mezi atributy: Γ = {{DEPT} ⇒ {HEAD, SCHOOL, DEAN}, {SCHOOL} ⇒ {DEAN}, {COURSE, YEAR} ⇒ {ID}, {ID, YEAR} ⇒ {DEPT}}. Sch´ema R nen´ı v BCNF vzhledem k Γ, protoˇze (napˇr´ıklad): {DEPT} ⇒ {HEAD, SCHOOL, DEAN} ∈ Γ, ale [{DEPT}]Γ = {SCHOOL, DEAN, DEPT, HEAD} = 6 R, to jest Γ 6|= {DEPT} ⇒ R, nebo: {SCHOOL} ⇒ {DEAN} ∈ Γ, ale [{SCHOOL}]Γ = {SCHOOL, DEAN} = 6 R, to jest Γ 6|= {SCHOOL} ⇒ R.
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
10 / 33
Pˇr´ıklad: Normalizace pomoc´ı dekompozice R = {SCHOOL, DEAN, DEPT, HEAD, ID, COURSE, YEAR} Γ= {{DEPT} ⇒ {HEAD, SCHOOL, DEAN}, . . .} (viz pˇredchoz´ı pˇr´ıklad) R1 = {SCHOOL, DEAN, DEPT, HEAD} Γ1 = {{DEPT} ⇒ {HEAD, SCHOOL, DEAN}, {SCHOOL} ⇒ {DEAN}} R11 = {SCHOOL, DEAN} Γ11 = {{SCHOOL} ⇒ {DEAN}} R12 = {SCHOOL, DEPT, HEAD} Γ12 = {{DEPT} ⇒ {HEAD, SCHOOL}, {SCHOOL} ⇒ {}}
R2 = {DEPT, ID, COURSE, YEAR} Γ2 = {{DEPT} ⇒ {}, {COURSE, YEAR} ⇒ {ID}, {ID, YEAR} ⇒ {DEPT}} R21 = {DEPT, ID, YEAR} Γ21 = {{DEPT} ⇒ {}, {ID, YEAR} ⇒ {DEPT}} R22 = {ID, COURSE, YEAR} Γ22 = {{COURSE, YEAR} ⇒ {ID}, {ID, YEAR} ⇒ {}}
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
11 / 33
Rozklad SCHOOL
DEAN
DEPT
HEAD
ID
COURSE
YEAR
SCI SCI SCI SCI SCI SCI SCI SCI
Adams Adams Adams Adams Adams Adams Adams Adams
AF AF AF CS CS CS CS CS
Black Black Black Davis Davis Davis Davis Davis
7 8 8 3 3 6 6 6
QOPT1 LASR1 LASR1 ALMA1 ALMA1 DATA1 DATA1 PAPR1
2012 2012 2013 2012 2013 2012 2013 2012
DEPT SCHOOL
DEPT
HEAD
SCI SCI
AF CS
Black Davis
Petr Krajˇ ca (UP)
AF AF ./ AF CS CS CS CS
ID
YEAR
7 8 8 3 3 6 6
2012 2012 2013 2012 2013 2012 2013
KMI/YDATA: Pˇredn´ aˇska V.
=
ID 7 8 8 ./ 3 3 6 6 6
SCHOOL
DEAN
SCI
Adams
COURSE
YEAR
QOPT1 LASR1 LASR1 ALMA1 ALMA1 DATA1 DATA1 PAPR1
2012 2012 2013 2012 2013 2012 2013 2012
./
12. prosinec, 2014
12 / 33
Referenˇcn´ı integritn´ı omezen´ı Hodnoty atribut˚ u jedn´e relace se mus´ı nach´azet jako hodnoty (jin´ych) atribut˚ u jin´e relace. Mˇejme relaˇcn´ı promˇenn´e R typu R a S typu S. Referenˇcn´ı integritn´ı omezen´ı je v´yraz ve tvaru ρf (πR0 (R)) ⊆ πS 0 (S), kde R0 ⊆ R a S 0 ⊆ S. Omezen´ı ρf (πR0 (R)) ⊆ πS 0 (S) je splnˇeno v D, pokud pro kaˇzdou r ∈ RD plat´ı, ˇze existuje s ∈ SD tak, ˇze r(f (y)) = s(y) pro kaˇzd´y atribut y ∈ S 0 . Pˇr´ıklady S – studenti, P – pˇredmˇety, Z – student m´a zapsan´y pˇredmˇet ρID←STUDENT ID (πSTUDENT ID (Z)) ⊆ πID (S) ρID←COURSE ID (πCOURSE ID (Z)) ⊆ πID (P)
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
13 / 33
Referenˇcn´ı integritn´ı omezen´ı v SQL SQL (ˇc´asteˇcnˇe) implementuje pomoc´ı ciz´ıch kl´ıˇc˚ u pro ρf (πR0 (R)) ⊆ πS 0 (S) je R0 je ciz´ı kl´ıˇ c relaˇcn´ı promˇenn´e R, kter´y se odkazuje na atributy S 0 promˇenn´e S nutn´y pˇredpoklad: S 0 mus´ı b´yt kl´ıˇc v S (PRIMARY KEY nebo UNIQUE)
SQL: jednoatributov´y ciz´ı kl´ıˇc (sloupcov´e omezen´ı): REFERENCES jm´ eno-tabulky ( atribut ) v´ıceatributov´y ciz´ı kl´ıˇc (omezen´ı v r´amci cel´e tabulky): FOREIGN KEY (r-atribut1, r-atribut2 , ...) REFERENCES jm´ eno-tabulky (s-atribut1, s-atribut2, ...)
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
14 / 33
Pˇr´ıklad CREATE TABLE student ( id NUMERIC NOT NULL PRIMARY KEY, name VARCHAR NOT NULL, major VARCHAR NOT NULL); CREATE TABLE course ( name VARCHAR NOT NULL, ver NUMERIC NOT NULL, PRIMARY KEY (name, ver)); CREATE TABLE enrolled ( student_id NUMERIC NOT NULL REFERENCES student (id), c_name VARCHAR NOT NULL, c_ver NUMERIC NOT NULL, FOREIGN KEY (c_name, c_ver) REFERENCES course (name, ver), year NUMERIC NOT NULL, PRIMARY KEY (student_id, c_name, c_ver, year));
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
15 / 33
Chov´an´ı ciz´ıch kl´ıˇc˚ u v SQL Pˇredpoklad je d´ano ρy←x (π{x} (R)) ⊆ π{y} (S) modifikace promˇenn´e R, kter´e konˇ c´ı chybou: vloˇzen´ı hodnoty x do R, kter´a se nenach´az´ı mezi hodnotami y z S pˇr´ıklad: vloˇzen´ı v´ysledku zkouˇsky pro ID, kter´e nepatˇr´ı ˇz´adn´emu studentovi jako v pˇredchoz´ım pˇr´ıpadˇe, pro UPDATE pˇr´ıklad: snaha modifikovat ID na hodnotu, kter´a nepatˇr´ı ˇz´adn´emu studentovi
modifikace promˇenn´e S, kdy je moˇzn´e specifikovat chov´ an´ı pokus smazat z S n-tici s, kde s(y) se st´ale pouˇz´ıv´a v R pˇr´ıklad: smaz´an´ı studenta, kter´y m´a st´ale z´aznamy o zkouˇsce jako v pˇredchoz´ım pˇr´ıpadˇe, pro UPDATE m´ısto DELETE pˇr´ıklad: modifikace ID studenta, kter´y m´a z´aznamy o zkouˇsce
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
16 / 33
Moˇzn´e reakce na poruˇsen´ı referenˇcn´ı integrity (1/2) NO ACTION implicitn´ı chov´an´ı – okamˇzit´e zastaven´ı, nahl´aˇsen´ı chyby RESTRICT: nahl´aˇsen´ı chyby – bez moˇznosti odloˇzen´ı kontroly (do konce transakce) CASCADE: kask´adov´an´ı – nedojde k chybˇe, ale zmˇena se propaguje do tabulek, ve kter´ych je hodnota pˇr´ıtomna jako ciz´ı kl´ıˇc: pˇri DELETE se smaˇzou odpov´ıdaj´ıc´ı n-tice pˇri UPDATE se adekv´atnˇe zmˇen´ı hodnoty
SET NULL: smaz´an´ı hodnoty z n-tic – hodnoty v tabulk´ach s ciz´ım kl´ıˇcem budou nedefinovan´e (nebezpeˇcn´e) SET DEFAULT: nastaven´ı na implicitn´ı hodnotu – pokud m´a atribut d´anu implicitn´ı hodnotu pomoc´ı DEFAULT
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
17 / 33
Moˇzn´e reakce na poruˇsen´ı referenˇcn´ı integrity (2/2) CREATE TABLE enrolled ( student_id NUMERIC NOT NULL REFERENCES student (id) ON UPDATE CASCADE ON DELETE CASCADE, course_id NUMERIC NOT NULL REFERENCES course (id) ON DELETE RESTRICT ON UPDATE CASCADE, year NUMERIC NOT NULL, PRIMARY KEY (student_id, course_id, year)); /* odstrani i radky z tabulky enrolled, kde je student_id = 666 */ DELETE FROM student WHERE id = 666; /* aktualizuje i radky v tabulce enrolled se student_id = 666 */ UPDATE student SET id = 777 WHERE id = 666; DROP TABLE student; -- chyba DROP TABLE student RESTRICT; -- chyba DROP TABLE student CASCADE; -- odstrani omezeni, ne tabulky Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
18 / 33
Konceptu´aln´ı modelov´an´ı, ER model entita – odpov´ıd´a objekt˚ um re´aln´eho svˇeta (osoba, vˇec) siln´y entitn´ı typ – existuje nez´avisle na jin´em entitn´ım typu slab´y entitn´ı typ – existence z´avis´ı na jin´e entitn´ım typu
vlastnost – popisuje entity (jm´eno, barva, . . . ) vztah (relace, relationship) – vazba mezi dvˇema a v´ıce entitami (pracovn´ık-pracoviˇstˇe) kardinality 1:1, 1:N, M:N
Vztah k relaˇ cn´ımu modelu entity odpov´ıdaj´ı relac´ım i vztahy odpov´ıdaj´ı relac´ım (ciz´ı kl´ıˇce) vlastnosti nemus´ı odpov´ıdat atribut˚ um Pozn´ amka pˇri n´avrhu DB moˇzn´e vyuˇz´ıt ER model spoleˇcnˇe s ER diagramem (viz skripta) moˇzn´e pouˇz´ıt i UML
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
19 / 33
Pˇrirozen´e, umˇel´e a n´ahradn´ı kl´ıˇce (1/2) Pˇrirozen´ y kl´ıˇ c podmnoˇzina atribut˚ u v´yhody: jiˇz existuj´ı v datab´azi moˇzn´e ovˇeˇrit v˚ uˇci re´aln´e entitˇe
nev´yhody: ˇ m˚ uˇze se uk´azat, ˇze zvolen´y kl´ıˇc nen´ı jednoznaˇcn´y (napˇr. 2 lid´e a jedno RC) m˚ uˇze doj´ıt ke zmˇenˇe (napˇr. jm´ena) – nutn´a propagace zmˇen do ciz´ıch kl´ıˇc˚ u mohou b´yt rozs´ahl´e – dopad na v´ykon, psan´ı sloˇzit´ych dotaz˚ u
Umˇ el´ y kl´ıˇ c pˇridan´y atribut, kter´y nen´ı pˇr´ımo vlasnost´ı entity uˇzivatel k nˇemu m´a pˇr´ıstup je s´am o sobˇe ovˇeˇriteln´y, pˇridˇelen externˇe (napˇr. ISBN, EAN) v´yhody: efektivnˇejˇs´ı operace neˇz s pˇr´ırozen´ym kl´ıˇcem nemˇenn´e
nev´yhody vyˇzaduje zp˚ usob, jak unik´atnˇe pˇriˇradit hodnotu Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
20 / 33
Pˇrirozen´e, umˇel´e a n´ahradn´ı kl´ıˇce (2/2) N´ ahradn´ı kl´ıˇ c syst´emem generovan´y kl´ıˇc (ˇcasto posloupnost pˇrirozen´ych ˇc´ısel) uˇzivatel by jej nikdy nemˇel vidˇet (intern´ı souˇc´ast syst´emu) v´yhody: efektivn´ı prov´adˇen´ı dotaz˚ u; u ´spora m´ısta ˇ umoˇzn ˇuje pruˇznˇeji reagovat na neˇcekan´e situace (napˇr. dva lid´e stejn´e RC) nemˇenn´e
nev´yhody: moˇznost zavl´ect duplicity do pˇrirozen´ych kl´ıˇc˚ u probl´em, pokud je potˇreba exportovat/sd´ılet data nezapad´a u ´plnˇe do relaˇcn´ıho modelu
N´ ahradn´ı kl´ıˇ c v SQL CREATE TABLE foo (bar INTEGER AUTO_INCREMENT, -- MySQL baz SERIAL, -- PostgresSQL qux int IDENTITY(1,1)); -- MSSQL Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
21 / 33
Procedur´aln´ı programov´an´ı na stranˇe datab´aze SQL deklarativn´ı jazyk sloˇzit´e vyj´adˇrit nˇekter´e operace moˇznost vytv´aˇret vlastn´ı procedury a funkce forma abstrakce a optimalizace (data z˚ ust´avaj´ı v DBMS) CREATE PROCEDURE ... CREATE FUNCTION ... vlastnosti jazyk˚ u pro procedur´aln´ı programov´an´ı se liˇs´ı mezi DBMS (Oracle – PL/SQL, PostgresSQL – PL/pgSQL, MSSQL – T-SQL, MySQL – SQL) k´ od je ˇcasto nepˇrenositeln´y mezi DBMS Triggery procedury proveden´e pˇri aktualizaci tabulky (INSERT, UDPATE, DELETE) bud’ na u ´rovni jednotliv´ych ˇr´adk˚ u nebo cel´e tabulky moˇznost ˇc´ıst obsah mˇenˇen´ych ˇr´adk˚ u moˇznost nastavit proveden´ı pˇred nebo po zmˇenˇe Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
22 / 33
Prostˇredky pro spr´avu datab´aze (1/3) kaˇzd´y DBMS ˇreˇs´ı jinak vˇzdy pouˇzijte dokumentaci Aktualizace tabulek ALTER TABLE foo ADD column_bar int; ALTER TABLE foo REMOVE column_bar; ALTER TABLE foo ADD CONSTRAINT bar_constraint CHECK (bar > 0); ... DROP TABLE foo; DROP VIEW bar;
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
23 / 33
Prostˇredky pro spr´avu datab´aze (2/3) Spr´ ava opr´ avnˇ en´ı uˇzivatel´e, role (skupiny) db. objekty jsou vlastnˇeny uˇzivateli/rolemi a mohou k nim m´ıt opr´avnˇen´ı MySQL CREATE USER ’joe’@’localhost’ IDENTIFIED BY ’nbusr123’; -- prideli vsechna opravneni ke vsem tabulkam v mojedb GRANT ALL PRIVILEGES ON mojedb.* TO ’joe’@’localhost’; -- prideli opravneni provest SELECT nad tabulkou foo GRANT SELECT PRIVILEGES ON mojedb.foo TO ’joe’@’localhost’; -- odebere opravneni mazat zaznamy v tabulce foo REVOKE DELETE PRIVILEGES ON mojedb.foo FROM ’joe’@’localhost’;
PostgresSQL CREATE USER joe PASSWORD ’nbusr123’; GRANT ALL PRIVILEGES ON DATABASE mojedb TO joe; GRANT SELECT PRIVILEGES ON foo TO joe; REVOKE DELETE PRIVILEGES ON foo FROM joe; Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
24 / 33
Prostˇredky pro spr´avu datab´aze (3/3) Aktualizace statistik DBMS si udrˇzuje statistiky o datech (velikost, nejˇcastˇejˇs´ı hodnoty) a podle nich vytv´aˇr´ı prov´adˇec´ı pl´an mˇely by se udrˇzovat pr˚ ubˇeˇznˇe (nemus´ı vˇzdy odpov´ıdat realitˇe) vynucen´a aktualizace: MySQL: ANALYZE TABLE foo; PostgresSQL: ANALYZE foo; Uvolnˇ en´ı m´ısta DBMS obvykle data nemaˇze pˇr´ımo (pomal´a operace), pouze pˇrid´a pˇr´ıznak, ˇze dan´y ˇr´adek je odstranˇen´y k uvolnˇen´ı m´ısta by mˇelo doch´azet pr˚ ubˇeˇznˇe po d´avkach (podle nastaven´ı) vynucen´e uvolnˇen´ı m´ısta: MySQL: OPTIMIZE TABLE foo; PostgresSQL: VACUUM foo; Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
25 / 33
Spolupr´ace SQL s jin´ymi jazyky Embedded datab´ aze knihovna, souˇc´ast procesu pˇr´ım´y pˇr´ıstup, vol´an´ı funkc´ı ˇcasto jednouˇzivatelsk´e napˇr. SQLite, Apache Derby, FireBird, SQL Server Compact, . . . Architektura Client/Server DBMS samostatn´y proces komunikace typicky pˇres TCP/IP (pˇrip. unixov´e sockety, jin´a API) v´ıceuˇzivatelsk´y pˇr´ıstup moˇznost ˇsk´alov´an´ı na stranˇe klienta je vyˇzadov´an ovladaˇc pro pˇr´ıstup k DB Obecn´ y pˇr´ıstup programovac´ı jazyky poskytuj´ı standardn´ı prostˇredky pro pˇr´ıstup k DBMS mohou poskytovat pˇr´ıstup k embedded i vzd´alen´ym DBMS napˇr. Java – JDBC, C# – ADO.NET, PHP – PDO, vlastn´ı API Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
26 / 33
Spolupr´ace SQL s Java (JDBC) (1/3) JDBC – standardn´ı rozhran´ı v Javˇe obvykle je potˇreba pˇribalit k projektu JDBC ovladaˇc (soubor *.jar dod´avan´y v´yrobcem DBMS) URL pro pˇripojen´ı k db: jdbc:
://:<port>/?<dodatecne-parametery> pˇr´ıklady: jdbc:postgresql://localhost/mojedb jdbc:mysql://example.com:3306/foobase pˇripojen´ı: String connectionURL = "jdbc:postgresql://localhost/mojedb"; Connection con = DriverManager.getConnection(connectionURL, "joe", "nbusr123");
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
27 / 33
Spolupr´ace SQL s Java (JDBC) (2/3) // vytvori db prikaz try (Statement stmt = con.createStatement()) { // provede s timto prikazem dotaz stmt.executeQuery("SELECT * FROM employees"); try (ResultSet results = stmt.getResultSet()) { // iteruje pres vsechny radky vysledku while (results.next()) { // vybere hodnotu v prvnim a druhem sloupci // (sloupce jsou indexovany od 1!) System.out.println(results.getString(1)); System.out.println(results.getInt(2)); // vybere hodnotu podle nazvu sloupce System.out.println(results.getDouble("salary")); } } }
Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
28 / 33
Spolupr´ace SQL s Java (JDBC) (3/3) Aktualizace stmt.executeUpdate("UPDATE employee SET salary = salary * 1.1"); Pˇripraven´ e dotazy optimalizace zpracov´an´ı dotazu pˇred´av´an´ı parametr˚ u PreparedStatement empStmt = con.prepareStatement("SELECT * FROM employees WHERE name = ?"); PreparedStatement insertStmt = con.prepareStatement("INSERT INT employees (name, age, salary)" + "VALUES (?, ?, ?)"); empStmt.setString(1, "Alice"); empStmt.executeQuery(); insertStmt.setString(1, "Xavier"); insertStmt.setInt(2, 40); insertStmt.setDouble(3, 24000.0); insertStmt.executeUpdate(); Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
29 / 33
Spolupr´ace SQL s C# (ADO.NET)
string conStr = "Server=localhost;Database=mojedb;User Id=joe; Password=nbusr123"; using (SqlConnection con = new SqlConnection(conStr)) { string queryString = "SELECT * FROM employee WHERE name = @name"; SqlCommand command = new SqlCommand(queryString, con); command.Parameters.AddWithValue("@name", "Bob"); try { connection.Open(); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("\t{0}\t{1}\t{2}", reader[0], reader[1], reader["salary"]); } reader.Close(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
30 / 33
Spolupr´ace SQL s PHP (pˇr´ım´y pˇr´ıstup) PHP obsahuje API pro pˇr´ım´e pˇripojen´ı k nejbˇeˇznˇejˇs´ım datab´az´ım (MySQL, PostgreSQL, . . . ) moˇzn´e vyuˇz´ıt vˇsech vlasnost´ı DBMS API jsou si podobn´a ale nekompatibiln´ı nepˇrenositeln´ e ˇreˇsen´ı! Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
31 / 33
Spolupr´ace SQL s PHP (PDO) univerz´aln´ı rozhran´ı pro r˚ uzn´e DB query(’SELECT * FROM employees’); while($row = $result->fetch(PDO::FETCH_ASSOC)) { echo "Name: {$row[’name’]}, Salary: {$row[’salary’]}\n"; } // pripravene dotazy $stmt = $db->prepare(’SELECT * FROM employees WHERE name = :name’); $params = array(’:name’ => ’Alice’); $result = $stmt->execute($params); $row = $result->fetch(PDO:FETCH_ASSOC); ?> Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
32 / 33
SQL injection attack pokud je login foo a heslo bar ⇒funguje dobˇre pokud je login foo’ or true) -- a heslo bar ⇒m´ame probl´em protoˇze: SELECT * FROM users WHERE (user = ’foo’ or true) -- pass=’...’) pˇres funkce DB se lze dostat k dalˇs´ım citliv´ym informac´ım ˇreˇsen´ı ⇒pokud to API umoˇzn ˇuje parametrizovan´e dotazy (JDBC, ADO, PDO) nebo d˚ usledn´a kontrola vstup˚ u podobn´y probl´em nastane kdekoliv, kde pouˇzijeme eval Petr Krajˇ ca (UP)
KMI/YDATA: Pˇredn´ aˇska V.
12. prosinec, 2014
33 / 33
