Starší verze Správa médií

Poslední úpravy Index

OBSAH WEBU

ČTĚTE!

Toto je starší verze dokumentu!

Obsah

Relační datový model a jazyk SQL
Diskuze

Relační datový model a jazyk SQL

Relační datový model

Relační databáze

Názvosloví

relace = databázová tabulka
atributy = sloupce tabulky
hodnota atributu = hodnota n-tice
obor hodnot atributu = doména (v praxi se doméně říká též datový typ)
aktivní doména = aktuálně se nacházející hodnoty z domény (to, co je aktuálně v tabulce)
n-tice = řádky
„tabulka STUDENT se sloupci JMENO, PRIJMENI, …“ = „relace STUDENT s atributy JMENO, PRIJMENI, …“

Příklad

Nechť máme tabulku STUDENT(LOGIN, JMENO, PRIJMENI, ADRESA) asi takto:

LOGIN	JMENO	PRIJMENI	ADRESA
xcerny00	Petr	Černý	Brněnská 15 Vyškov
xnovak00	Jan	Novák	Cejl 9 Brno
xnovak01	Pavel	Novák	Cejl 9 Brno

domény atributů
D_LOGIN = {xnovak00, xnovak01, xbanan01, xchleba11, …}
D_JMENO = {Petr, Pavel, Josef, …}
D_PRIJMENI = {Grupa, Sadlo, Zendulka, …}
D_ADRESA = {Brněnská 15 Vyškov, Cejl 9 Brno, …}

relace = tabulka
R_STUDENT $\subset$ D_LOGIN x D_JMENO x D_PRIJMENI x D_ADRESA

neboli podmnožina kartézského součinu jednotlivých domén

schéma tabulky - záhlaví tabulky
tělo tabulky - její obsah,tj. řádky, tj. n-tice
domény mohou obsahovat pouze atomické hodnoty (vícehodnotové se převádí na skalární redundancí zbylých sloupců)

Definice

Nechť

jsou množiny atomických hodnot označované jako domény. Relace (databázová) na doménách D_1, D_2, ..., D_n

je dvojice R = (R, R^*), kde R = R( A_1:D_1, A_2:D_2, ..., A_n:D_n

) je schéma relace, kde $A_i (A_i \ne A_j, i \ne j)$ značí jméno atributu definovaného na doméně D_i a R^* $\subset$ D₁ x D₂ x … x D_n je tělo relace.

Počet atributů n relace se označuje stupeň (řád) relace, kardinalita těla relace m = |R^*| se označuje kardinalita relace

Zjednodušené se relace zapisuje R( A_1, A_2, ..., A_n ), třeba STUDENT(LOGIN, JMENO, PRIJMENI, ADRESA).

Neboli viz příklad

Relační databáze - data jsou v tabulkách organizovány v souladu s relačním modelem (relační model - data strukturována na základě relací, tj. tabulek)

Definice

Integritní omezení = omezení kladená na data

obecná omezení - musí platit vždy bez ohledu na aplikaci (např. PK, FK)
specifiká omezení - závislá na aplikace (maximální délka řetězce, maximální hodnota, …)

Definice

Kandidátní klíč (candidate key)

Atribut CK relace R se nazývá kandidátním klíčem, když splňuje tyto dvě časově nezávislé vlastnosti:

Hodnoty atributu CK v relaci R jsou unikátní (jednoznačné), tj. neexistují žádné dvě n-tice relace se stejnou hodnotou tohoto atributu.
Atribut CK je vzhledem k jednoznačnosti hodnot v R minimální (neredukovatelný), tj. je-li CK složeným atributem, nelze vypustit z něho žádnou složku, aniž by přestala být splněna unikátnost hodnot.

Hodnota atributu CK je unikátná, to zn., že se prvek z dané domény vyskytuje nejvýše jednou

Definice

Primární klíč (primary key)

Primárním klíčem je jeden z kandidátních klíčů (vybraný), zbývající kandidátní klíče se nazývají alternativní (někdy také sekundární).

Definice

Pravidlo integrity entit

U žádné složky primárního klíče nesmí chybět hodnota.

Definice

Cizí klíč (foreign key)

Atribut FK relace R2 se nazývá cizí klíč, právě když splňuje tyto časově nezávislé vlastnosti:

Každá hodnota FK je buď plně zadaná nebo plně nezadaná.
Existuje relace R1 s kandidátním klíčem CK takovým, že každá zadaná hodnota FK je identická s hodnotou CK nějaké n-tice relace R1

Neboli hodnota je hodnota nebo NULL, za druhé pro každou hodnota FK musí existovat příslušní PK. Jinak FK vytváří logické vazby mezi tabulkami/relacemi

Definice

Pravidlo referenční integrity

Relační databáze nesmí obsahovat žádnou nesouhlasnou hodnotu cizího klíče.

Definice

Schématem relační databáze nazýváme dvojici (R, I), kde R = {R1, R2, …, Rk,} je množina schémat relací a I = {I1, I2, …, Il} je množina integritních omezení. Někdy jsou lokální integritní omezení rozdělena mezi jednotlivá schémata, tj. R = {(R1, I1) , (R2, I2), …, (Rk, Ik)}

Neboli každý sloupce má svoje integritní omezení, tj. omezezení kladená na data (co může obsahovat, kde musí být hodnoty, …)

Pokud data v relaci splňují všechny integritní omezení, potom jsou data konzistentní

Relační Algebra

Definice

Relační algebra

Relační algebrou rozumíme dvojici RA = (R, O), kde nosičem R je množina relací a O je množina operací, která zahrnuje:

tradiční množinové operace (sjednocení, průnik, rozdíl, součin)
speciální relační operace, mezi které patří projekce, selekce (restrikce), spojení (přirozené).

Tabulka je množina n-tic, proto se každý záznam vyskytuje pouze jednou.

Definice

Operace relační algebry

Sjednocením relací R1 = (R, R1*) a R2 = (R, R2*) se schématem R je relace R1 union R2 = (R, R1* ∪ R2*).
Analogicky pro průnik (R1 intersect R2) a rozdíl (R1 minus R2).
Kartézským součinem relací R1 = (R1, R1*) a R2 = (R2, R2*) je relace R1 times R2 = ( (R1,R2), R1* x R2*).

Sjednocení je možné provést pouze pro tabulky se stejnými sloupci. Pokud po sjednocení dostaneme dva a více stejných řádek, bere se jenom jeden.

Definice

Projekce Projekce relace R = (R, R*) na atributy X, Y, …, Z je relace R[X, Y, …, Z] se schématem (X, Y, …, Z) a tělem zahrnujícím všechny n-tice t = (x, y, …, z) takové, že v R* existuje n-tice t’ s hodnotou atributu X rovnou x, Y rovnou y, … Z rovnou z.

Z dané tabulky vybereme jen některé sloupce

Definice

Selekce

Nechť θ je operátor porovnání dvou hodnot (<, >, <>, =, atd.). θ selekce (restrikce) relace R = (R, R*) na atributech X a Y je relace
R where X θ Y,
která má stejné schéma jako relace R a obsahuje všechny n-tice t ∈ R*, pro které platí x θ y, kde x je hodnota atributu X a y hodnota atributu Y v ntici t. Na místě atributu X, resp. Y může být konstanta. Pak jde o θ selekci na atributu X, resp. Y.

Podle definice máme jenom jednu podmínku. Selekci lze dále rozšířit o spojky and, or, not.

Vybíráme jen některé řádky z tabulky podle dané podmínky

Definice

Přirozené spojení (natural join)

Nechť R1 = (R1, R1*) je relace se schématem R1(X1,X2,..,Xm,Y1,Y2,…,Yn) a R2 relace se schématem R2(Y1,Y2, …,Yn, Z1,Z2,…,Zk). Uvažujme složené atributy X=(X1,X2,…,Xm), Y=(Y1,Y2,…,Yn) a Z=(Z1,Z2,…,Zk). Potom přirozené spojení relací R1 a R2 je relace R1 join R2 se schématem (X, Y, Z) a tělem zahrnujícím všechny n-tice t = (x, y, z) takové, že v R1* existuje n-tice t’ s hodnotou x atributu X a hodnotou y atributu Y a v R2* existuje n-tice t’’ s hodnotou y atributu Y a hodnotou z atributu Z.

Neboli spojujeme podle sloupce se stejným názvem a podle stejné hodnoty

Příklad

A	B	C
0	a	d
1	a	e
2	b	f

C	D	E
e	1	0
d	1	1
d	0	1

T1 join T2

A	B	C	D	E
0	a	d	1	1
0	a	d	0	1
1	a	e	1	0

minimální množina operací relační algebry - sjednocení, rozdíl, kartézský součin, projekce a selekce. Z nich lze vytvořit zbylé operace

relačně úplný jazyk - lze vyjádřit alespoň totéž co relačním jazykem

Jazyk SQL

SQL = structured query language, je standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích

Příkazy e mohou více či méně lišit pro konkrétní databázi

SQL příkazy se dělí na čtyři základní skupiny:

Příkazy pro definici dat (CREATE, ALTER, DROP, …)
Příkazy pro manipulaci s daty (SELECT, INSERT, UPDATE, DELETE, …)
Příkazy pro řízení přístupových práv (GRANT, REVOKE)
Příkazy pro řízení transakcí (START TRANSACTION, COMMIT, ROLLBACK)
Ostatní nebo speciální příkazy

Příkazy pro definici dat

Lze označit jako DDL – Data Definition Language

CREATE - slouží pro vytváření nových objektů

- například: tabulku, index, pohled, proceduru

Vytvoření pohledu

CREATE VIEW <nazev pohledu>
AS
<SELECT prikaz>

Vytvoření indexu

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX <nazev indexu> ON <nazev tabulky nebo pohledu>(<nazev sloupce> [ASC | DESC]
[,...n])

ALTER - slouží pro upravení objektů

DROP - pro smazání objektů

Příkazy pro manipulaci s daty

Slouží pro manipulaci s daty.

DELETE - maže data z tabulky

DELETE from users where (age < 13)

UPDATE - aktualizuje data

UPDATE users set city = 'Brno', wtf = 'FIT' where (surname = 'Novák')

INSERT - vkládáme nová data

INSERT into users (name, surname, age) values ('Jiri', 'Janda', 22)

SELECT - nejčastější příkaz

V příkazu vybíráme s jakými sloupci v jaké tabulce (tabulkách) budeme pracovat a dale podminku která řádky mají být zpracovány.
nejkomplikovanější příkaz, má spoustu volitelných podčástí

where - společný i pro ostatní kromě insertu. Obsahuje podmínky, mohou se spojovat and / or, používat závorky atd.
group by - Seskupení záznamů znamená, že shrneme informace několika záznamů do jednoho záznamu. K tomuto používáme GROUP BY, za kterým uvedeme název sloupce, podle kterého se mají záznamy seskupit (všechny záznamy, které mají stejnou hodnotu tohoto sloupce, se sloučí do jednoho záznamu).
having - je velmi podobná podmínce WHERE. Rozdíl je v tom, že podmínka WHERE omezuje ještě neseskupené záznamy a podmínka HAVING omezuje seskupené záznamy. Další rozdíl je, že podmínka HAVING se musí týkat sloupce, který má konstantní hodnotu a nebo agregační funkce. Př. (HAVING `pocet` > 5)
order by - specifikuje, podle čeho budou výstupní data seřazena - sloupec asc / desc
distinct Uvedením klíčového slova DISTINCT se eliminují duplicitní řádky „Ze kterých měst jsou klienti spořitelny?“ SELECT DISTINCT město FROM Klient

Transakce a prava jsme prakticky neprobírali.

Spojování tabulek - operace join

Slouží ke spojování výsledku dotazu SELECT ze dvou vstupních množin (typicky tabulek relační databáze).

Vnitřní spojení - Nejjednodušší variantou je tzv. INNER JOIN (pozn. INNER a OUTER není nutné používat a v podstatě se ani nedoporučuje používat - zbytečně prodlužují zápis). Ten odpovídá kartézskému součinu tabulek a aplikaci predikátu na tento součin.

obecné na základě podmínky (T1 JOIN T2 ON A<C)
na základě rovnosti (equijoin) (T1 JOIN T2 ON A=D)
přirozené (natural join) (T1 NATURAL JOIN T2)- Přirozené spojování (NATURAL

JOIN) je zvláštním případem vnitřního spojování, kde je spojovací podmínka realizována automaticky dle shodnosti názvů a datových typů ve spojovaných tabulkách. Pro nemožnost konkrétněji definovat ve spojovacích tabulkách, ale pouze strukturou, není tento typ tolik používán.

Vnější spojení - Vnější spojování (OUTER JOIN) generuje výstupní množinu omezenou o spojovací podmínky podobně jako vnitřní spojování, pokud však není nalezen vhodný řádek v druhé množině, je nenalezený řádek nahrazen hodnotami NULL. Dle typu vnějšího spojení mohou být doplňovány řádky z jedné nebo obou vstupních množin. Výsledkem vnějšího spojování jsou řádky naplněné hodnotami ze vstupních množin i částečně.

Částečné vnější spojování - Pro částečné vnější spojování jsou definována klíčová slova LEFT a RIGHT, která definují, která vstupní množina má zahrnuté všechny řádky v množině výstupní, tj. která vstupní množina není doplňována o NULL hodnoty

Lze použít i FULL OUTER JOIN.

SQL umožňuje v klauzuli FROM uvádět nejen jména tabulek, ale i tabulkové výrazy, resp. výraz spojení

Databázové pohledy

Pohled je virtuální databázová struktura, která může obsahovat data z nula až více tabulek.

V prohlížeči máte záložky, na ploše zástupce a v databázi? V databázi jsou pohledy. Z pohledů lze především vybírat data (k čemuž slouží nejčastěji), a někdy do nich lze i data vkládat. Databázové systémy by se bez pohledů obešly - podobně, jako byste se na ploše obešli bez zástupců nebo v prohlížeči bez záložek. Ale práce s nimi může být mnohem pohodlnější.

Mohou sloužit jako jiné pojmenování tabulek
Mohou z tabulky vybrat jen určité řádky (nebo sloupce)

· *Pohledy mohou obsahovat výrazy (jako jsou vestavěné funkce MySQL nebo uživatelsky definované funkce).

Mohou spojovat data z více tabulek
Pohledy se mohou odkazovat na další pohledy

Pohledy s klauzulemi DISTINCT, GROUP BY, HAVING, s agregačními funkcemi a spojující několik tabulek, umožňují jen čtení.

create view vwPracovnici as select * from pracovnici;

- Od tohoto okamžiku můžete pohled použít jako tabulku, ve smyslu:

select * from vwPracovnici;

create view vwPrumernyVek as select avg(vek) as prumer from pracovnici;

- Vrátí průměrný věk (výsledek bude obsahovat vždy jen jeden řádek). Neboli pohled může obsahovat agregační funkci.

Používáme pohledy, pokud

si tím subjektivně zjednodušíte práci
se stejný dotaz opakuje
potřebujete mít v dotazu SELECT nějaké výrazy

Procedury a triggery

Procedura - Uložená procedura je sada příkazů SQL, které jsou:

Uložené na serveru
Zkompilované pro rychlejší použití
Sada příkazů je provedena najednou
Může se podobat filozofii maker v OpenOffice.org writeru
Jestliže chce několik databázových klientů vykonávat stejnou (nebo hodně podobnou) práci, bude pro ně příjemné zjištění, že něco takového již server „umí“ díky uložené proceduře, kterou má k dispozici.
Přispívají k zabezpečení serveru. Nastaveny tak, že: 1. Smí spouštět pouze někdo, 2. Mohou samy kontrolovat počet, typ, velikost a některé jiné charakteristiky parametrů, jež jsou jim posílány
Nejdůležitější argument procedur - jsou obecně schopny běžet rychleji

Procedury mohou dělat to, co normální příkazy:

Vybírání dat
Vkládání, aktualizace, odstraňování dat
Vytváření, používání a rušení dočasných tabulek
Matematické a statistické výpočty

Příklad:

create procedure sp_vratradky (od int, do int)
begin
select * from software where id between od and do;
end

A zavolat pomocí příkazu:

call sp_vratradky 10, 20)

Triggery - Trigger je uložená procedura, která se spouští v souvislosti s provedením nějakého akčního dotazu na tabulce, nebo taky pouze ten spoustec, ktery ma navazanou uálost, a poté proceduru.

Česky se tomu někdy říká „spoušť“
Trigger je „uložená procedura“, to znamená, že uvnitř triggeru lze provádět většinu věcí, které umí uložené procedury. V triggeru lze mít například smyčku, podmínku, lokální proměnnou, matematický výpočet a podobně.
Spouští se „v souvislosti“ s akčním dotazem. Trigger se může spustit buď předtím nebo poté, co jsou změny v datech zapsány do databáze.
„Akční dotaz“ znamená, že trigger lze spustit při vkládání dat, při jejich aktualizaci nebo při odstraňování dat z databáze
Mají stejnou syntaxi jako uložené procedury, přesto se od nich v lecčems liší. Triggerům není možné předávat žádné vstupní parametry.
Triggery navíc narozdíl od uložených procedur nemohou vracet sadu záznamů
Pro MySQL platí, že se v triggeru nesmí objevit přinejmenším tyto příkazy: Příkazy ALTER, Příkazy pro řízení transakcí (START TRANSACTION, ROLLBACK, COMMIT), Volání procedur (CALL), Příkazy pro nastavování práv (GRANT, REVOKE) a některé další příkazy.

create trigger trSaveRows
before delete
on bezpecna
for each row
begin
insert into bezpecna_zaloha(id, jmeno, plat, cas_odstraneni,
uzivatel)
values (old.id, old.jmeno, old.plat, now(), user());
end;

EMBEDED SQL / dynamic SQL??