Databázové systémy
1
Obsah
1 Relační databáze, databázový server, tabulka...................................................................................3
2 Jazyk SQL a jeho vývoj. Příkazy DDL, DML, DCL.........................................................................5
3 Datové typy SQL a Oracle.................................................................................................................6
4 Příkaz Select – základní syntaxe, projekce, restrikce, aliasy, setřídění výsledku, klauzule
DISTINCT a DISTINCTROW.............................................................................................................9
5 Příkazy Insert, Update, Delete.........................................................................................................11
6 Hodnota NULL, operátory, výrazy, podmínky................................................................................11
7 Vnitřní a vnější spojení tabulek.......................................................................................................13
8 Souhrnné a skupinové dotazy, agregační funkce.............................................................................15
9 Vnořené dotazy, množinové operátory............................................................................................15
10 Funkce jazyka SQL. Práce s datovým typem DATE.....................................................................16
11 Fáze návrhu databáze, E-R diagramy...........................................................................................19
12 Normální formy, normalizace tabulek, dekompozice....................................................................21
13 Vytváření tabulek, integritní omezení, primární a cizí klíče..........................................................24
14 Pohledy a jejich význam, sekvence...............................................................................................26
15 Indexy – druhy indexů a jejich význam........................................................................................27
16 Zabezpečení a ochrana dat – uživatelské účty, systémová a objektová oprávnění, role...............30
17 Transakce, návratové body, automatické zamykání, konzistentní čtení........................................32
18 Přístup k databázi z vyššího programovacího jazyka. Bezpečnost a SQL injection.....................33
19 Jazyk PL/SQL, proměnné, syntaxe bloku.....................................................................................35
20 Řízení toku programu - podmínky, cykly, kurzory, záznamy, ošetření chyb.................................36
21 Procedury a funkce, balíčky..........................................................................................................40
22 Triggery pro DML příkazy nad tabulkami, Triggery pro databázové a klientské události............42
23 Export a import dat, práce s textovými soubory a XML dokumenty............................................44
24 Relační algebra. Závislosti............................................................................................................46
2
1 Relační databáze, databázový server, tabulka.
Relační databázový model
- datové prvky jsou organizovány v tabulkách
- každá tabulka reprezentuje entitu aplikace
- každý řádek je instance dané entity
- relace spojují řádky ve dvou tabulkách
- SQL tvoří jednotné uživatelské rozhraní pro přístup a práci s daty
př. kočky – entita; každá kočka – řádek; studenti- druhá entita, relace – student jako vlastník kočky
Co je to relační databáze
- Sada nástrojů pro efektivní a spolehlivé ukládání dat a pro manipulaci s nimi
Několik pojmů
Databáze - data a nástroje zajišťující jejich ukládání a manipulaci s těmito daty
Databázový server – soubor programových prostředků určených pro práci s daty, včetně organizace a
realizace přístupu klientů k těmto datům
Báze dat (databáze) - uspořádaná množina dat ve formě záznamů, které jsou navzájem v určitém vztahu a
jsou přístupné s pomocí systému řízení báze dat
uživatelé databáze k ní mohou přistupovat (pracovat s ní) dvěma způsoby13:
• operace čtení (předchází výběr – SELECT)
• operace zápisu (INSERT, UPDATE, DELETE)
Systém řízení báze dat – SŘBD
• programový systém umožňující vytvoření, údržbu a použití báze dat
• řídicí systém databáze, který sídlí mezi vlastní fyzickou vrstvou (daty) a uživatelem. Díky této vrstvě nemusí
uživatel při práci s databází vědět naprosto nic o její skutečné fyzické podobě a způsobu, jakým jsou data
uložena a udržována.
Databázová aplikace (database application)
program, který umožňuje uživatelům přístup k datům v databázi prostřednictvím
formulářů pro zadávání dat, formulářů pro zadávání dotazů a sestav
Funkce SŘBD
Základní funkce: přenášení (načítání) dat z místa jejich uložení (např. z pevného disku) do místa jejich zpracování
(operační paměť) a zpět. Podmínkou efektivnosti této procedury je strukturování dat v místě jejich uložení (např. na
záznamy a položky).
- definování a redefinování dat v databázi (data definition) - organizace datových souborů (vytváření a změny
datových struktur)
- vytváření obsahu databáze – aktualizace datových souborů (vkládání dat, změny, aktualizace dat)
- výběr a výstup (prezentování, zobrazování, prohlížení) dat z databáze (data display)
3
-
tvorba vstupních formulářů (obrazovek, pohledů) a výstupních sestav
kontrola integrity dat (data integrity) - poskytuje metodu nebo metody pro definování a zajištění správnosti dat
kontrola přístupových práv (určuje, kdo a jak může přistupovat k datům)
zpravidla obsahuje i programovací jazyk pro vytváření vlastních aplikací
Základní relační operace
Klasické množinové operace
u množinových operací (kromě kartézského součinu) se předpokládá, že obě tabulky, které do operace vstupují
jako operandy, mají tyto vlastnosti:
- jsou stejného stupně, tj. mají stejný počet sloupců
- každý i-tý sloupec z obou tabulek je definován na stejné doméně (tj. položky jsou v obou tabulkách
definovány ve stejném pořadí a jako položky stejného typu)
Sjednocení: vytvoří novou tabulku, která obsahuje všechny řádky obou výchozích tabulek
Průnik: vytvoří tabulku, která bude obsahovat pouze totožné řádky z obou relací
Množinový rozdíl: vytvoří tabulku, ve které budou všechny řádky první vstupní tabulky kromě těch, které se
vyskytují i v druhé tabulce
Symetrický rozdíl: vytvoří tabulku, ve které budou všechny řádky obou tabulek s výjimkou těch, které se
vyskytují v obou tabulkách
Kartézský součin: vytvoří novou tabulku tak, že spojuje řádky z obou tabulek systémem každý s každým.
Počet sloupců výsledné tabulky je součet počtu sloupců obou vstupních tabulek. Počet řádků je součin počtu
řádků obou vstupních tabulek.
Speciální relační operace
Projekce - výběr sloupců tabulky
vertikální podmnožina
Restrikce - výběr určité věty (záznamů, řádků) z tabulky na základě stanovené podmínky
horizontální podmnožina
Spojení tabulek - propojení tabulek, které obsahují alespoň jednu stejnou položku (resp. mají alespoň po jedné
položce založené na stejné doméně), výsledek zřetězení řádek dvou množin dat provedeného podle jednoho
nebo více zadaných pravidel
Příklad spojení:
Seznam čtenářů knihovny
a seznam měst,
v nichž tito čtenáři bydlí
(spojovací položkou je kód města)
4
Pravidla pro tabulkovou prezentaci relace
(zjednodušení rozmanitostí vztahů zkoumaných teorií relací na binární relace)
- každý řádek odpovídá jedné entitě relace
- pořadí řádků je nevýznamné
- žádné dva řádky nejsou stejné (tabulka neobsahuje duplicitní řádky)
- pořadí sloupců je nevýznamné
- význam každého sloupce je určen jménem atributu
- žádné dva názvy sloupců (atributy) nejsou stejné
- hodnoty ve sloupcích jsou atomické (skalární – nelze je dále rozkládat)
Relace – databázové tabulky
2 Jazyk SQL a jeho vývoj. Příkazy DDL, DML, DCL.
Databázový jazyk SQL
1974-75 výzkum v IBM ohledně možnosti využití relačních databází -> vytvoření sady příkazů -> jazyk SEQUEL
(Structured English Query Language)
1979 Relational Software (dnes Oracle Corporation) Oracle
Obdobně IBM SQL/DS, Progress, Informix, SyBase
Přejmenování SEQUEL -> SQL (Structured Query Language)
ANSI – standardizace SQL86 (v roce 1986)
V dalších letech se ukázalo, že SQL-86 obsahuje některé nedostatky a naopak v něm nejsou obsaženy některé důležité
prvky týkající se hlavně integrity databáze - nový standard SQL92 (v roce 1992, zkráceně SQL2)
Zatím nejnovějším standardem je SQL3 (SQL-99), který reaguje na potřeby nejmodernějších databází s objektovými
prvky.
Obecně u většiny systémů najdeme
základní implementaci dle standardů (SQL2)
rozšíření (nové funkce, ale způsobují komplikace při převodech)
Jazyk SQL slouží k
• Definici dat (struktury a organizace dat a vztahů mezi nimi)
• Získávání dat
• Manipulaci s daty
• Řízení přístupu
• Sdílení dat
5
•
Integrita dat
Úloha jazyka SQL
Jazyk SQL je:
•
Interaktivní dotazovací jazyk (pro uživatele)
•
Databázový programovací jazyk (začlenění SQL kódu do aplikačních programů)
•
Administrační databázový jazyk (správa databáze)
•
Jazyk aplikací typu klient/server
•
Jazyk pro přístup k datům na Internetu
•
Distribuovaný databázový jazyk (provoz distribuovaných databází)
•
Jazyk pro databázové brány (umožňuje jednomu druhu databázového systému komunikovat s jiným)
Popis jazyka SQL - Standardní příkazy dělíme do skupin:
- Příkazy pro definici dat – DDL
- Těmito příkazy se vytvářejí struktury databáze – tabulky, indexy, pohledy a další objekty. Vytvořené struktury
lze také upravovat, doplňovat a mazat. Tato skupina příkazů se nazývá zkráceně DDL – Data Definition
Language („jazyk pro definici dat“).
- CREATE – vytváření nových objektů.
- ALTER – změny existujících objektů.
- DROP – odstraňování objektů.
-
-
-
-
Příkazy pro manipulaci s daty – DML
příkazy pro získání dat z databáze a pro jejich úpravy se označují zkráceně DML (data Manipulation
Language)
- SELECT – vybírá data z databáze
- INSERT – vkládá do databáze nová data.
- UPDATE – mění data v databázi (editace).
- DELETE – odstraňuje data (záznamy) z databáze.
- EXPLAIN PLAN FOR – speciální příkaz, který zobrazuje postup zpracování SQL příkazu. Pomáhá
uživateli optimalizovat příkazy tak, aby byly rychlejší.
Příkazy pro řízení dat – DCL
Do této skupiny patří příkazy pro nastavování přístupových práv, řízení provozu a údržby databáze transakcí.
Označují se jako DCL – Data Control Language („jazyk pro ovládání dat“),
- GRANT – příkaz pro přidělení oprávnění uživateli k určitým objektům.
- REVOKE – příkaz pro odnětí práv uživateli.
- ALTER USER
- DROP USER
- Příkazy pro řízení dat – skupina příkazů pro řízení transakcí
- Označují se TCC – Transaction Control Commands („jazyk pro ovládání transakcí").
- COMMIT – potvrzení transakce.
- ROLLBACK – zrušení transakce, návrat do původního stavu.
- SAVEPOINT
- SET TRANSACTION
Ostatní příkazy
Do této skupiny patří příkazy pro správu databáze. Pomocí nich lze nastavovat systémové parametry (kódování
znaků, způsob řazení, formáty data a času apod.). Tato skupina není standardizována a konkrétní syntaxe
příkazů je závislá na databázovém systému.
3 Datové typy SQL a Oracle
Standardní datové typy SQL
Číselné datové typy
- DECIMAL (<celé číslo1>, <celé číslo2>)
Desetinné číslo s celkovým počtem <celé číslo1> číslic a s počtem <celé číslo2> za desetinnou
čárkou. Stejný typ je NUMERIC.
- FLOAT (<celé číslo>)
6
Desetinné číslo s plovoucí desetinnou tečkou. Parametr udává počet platných číslic (max. 38)
- REAL (<celé číslo>)
Desetinné číslo s plovoucí desetinnou tečkou. Parametr udává počet platných číslic (max. 18)
- INTEGER
Celé číslo v rozsahu – 2 147 483 648 až 2 147 483 647.
- SMALLINT
Celé číslo v rozsahu – 32 768 až 32 767.
Znakové datové typy
- CHAR (<celé číslo>)
Řetězec znaků pevné délky <celé číslo> znaků.
Maximální délka bývá 255 znaků.
- VARCHAR (<celé číslo>)
Řetězec znaků proměnlivé délky, maximálně <celé číslo> znaků.
Maximální délka bývá delší než u typu CHAR.
Datové typy pro datum a čas
- DATE
Vyjádření datumu.
- TIME
Vyjádření času.
- TIMESTAMP
Struktura obsahující datum a čas.
Datové typy Oracle
Číselné datové typy
- NUMBER (<celé číslo1>, <celé číslo2>)
Desetinné číslo s celkovým počtem <celé číslo1> číslic a s počtem <celé číslo2> číslic za desetinnou
čárkou.
Příklady:
NUMBER - číslo s pohyblivou řádovou čárkou
NUMBER(9) - celé číslo s přesností na 9 číslic
NUMBER(9,2) - desetinné číslo s pevnou řádovou čárkou s přesností na
číslic, z toho 2 za desetinnou čárkou (setiny)
NUMBER(7,-2) - desetinné číslo s pevnou řádovou čárkou s přesností na
počínaje 2. před desetinnou čárkou (zaokr. na stovky)
9
7 číslic
NUMBER(4,5) - desetinné číslo s pevnou řádovou čárkou s přesností na
číslice počínaje 5. za desetinnou čárkou
Znakové datové typy
- CHAR (<celé číslo>)
Řetězec znaků pevné délky <celé číslo> znaků.
Maximální délka 2000 znaků, default je 1.
- VARCHAR2 (<celé číslo>)
Řetězec znaků proměnlivé délky, maximálně <celé číslo> znaků.
Maximální délka je 4000 znaků, default je 1.
- LONG
Řetězec znaků proměnlivé délky, max. 2 GB.
Datové typy pro kalendářní a časové hodnoty
datum a čas
datový a časový interval
- Vnitřní reprezentace datumu v Oracle je číselná
(rozdíl jednoho dne odpovídá 1)
- Datum možno sčítat a odčítat.
4
Oba typy se skládají z polí YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR,
TIMEZONE_MINUTE
- DATE
7
-
Vyjádření konkrétního datumu a času s přesností na sekundy, rozsah je od 1.1.4712 př.n.l. do
31.12.9999
TIMESTAMP (<přesnost sekund>)
Struktura obsahující datum a čas s požadovanou přesností (až na miliontiny sekundy pro hodnotu
parametru <přesnost sekund>=6).
TIMESTAMP WITH TIME ZONE
Struktura jako TIMESTAMP doplněná o časový posun oproti UTC
-
INTERVAL YEAR (<rozsah roků>) TO MONTH
K vyjádření delšího časového intervalu.
- INTERVAL DAY (<rozsah dní>) TO SECOND (<přesnost sekund>)
K vyjádření relativně kratšího časového intervalu.
Datové typy pro objemná data - long object block (LOB)
- BLOB - binární objekty do 4GB
- CLOB – textové (CHAR) objekty do 4GB
- NCLOB - textové objekty v národních sadách do 4GB
Aktuální datum a čas v Oracle
Pro zjištění datumu a času můžeme použít několik funkcí
SYSDATE – vrací aktuální datum a čas jako typ date,
Pozor: obvykle se zobrazuje jen datum, ale hodnota obsahuje i čas, pro plné zobrazení je třeba použít
konverzní funkci např.
SELECT to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS') FROM dual;
LOCALTIMESTAMP vrací TIMESTAMP (v lokálním časovém pásmu)
SYSTIMESTAMP vrací TIMESTAMP WITH TIME ZONE (s uvedeným časovým posunem systému)
CURRENT_TIMESTAMP vrací TIMESTAMP WITH TIME ZONE (dle časového posunu nastaveného pro
danou session)
SELECT SESSIONTIMEZONE from dual;
ALTER SESSION SET TIME_ZONE = '+1:0';
Všechny hodnoty jsou odvezeny dle systémových hodin na dtb. serveru.
Práce s typy datum a čas v Oracle
Pro zjištění jedné hodnoty z hodnoty typu datum a čas je vhodná funkce EXTRACT.
Příklad použití:
SELECT
EXTRACT(year FROM systimestamp) EY,
EXTRACT(month FROM systimestamp) EM,
EXTRACT(day FROM systimestamp) ED,
EXTRACT(hour FROM systimestamp) EH,
EXTRACT(minute FROM systimestamp) EM,
EXTRACT(second FROM systimestamp) ES,
EXTRACT(timezone_hour FROM systimestamp) TH,
EXTRACT(timezone_minute FROM systimestamp) TM,
EXTRACT(timezone_region FROM systimestamp) TR,
EXTRACT(timezone_abbr FROM systimestamp) TA
FROM dual;
Posun datumu a času lze povést například takto:
SELECT current_timestamp + INTERVAL '10:30' MINUTE TO SECOND
FROM dual;
SELECT localtimestamp + INTERVAL '1' year(1) FROM dual;
8
SELECT sysdate + 1/24 FROM dual;
-- výsledek bude o hodinu posunut, nicméně čas není zobrazen
SELECT to_char(sysdate + 1/24, 'DD.MM.YYYY HH24:MI:SS') FROM dual;
-- v posledním příkladu je pro zobrazení času použita funkce to_char
Funkce pro zjištění aktuálního času
Next_day(datum, den_v_týdnu) - funkce vrací nové datum představující následující zadaný den v týdnu
Last_day(datum) - poslední den v aktuálním měsíci
Add_months(datum, n-měsíců) - posune datum o n měsíců
Months_between(datum1, datum2) - počet měsíců mezi 2 daty
4 Příkaz Select – základní syntaxe, projekce, restrikce, aliasy,
setřídění výsledku, klauzule DISTINCT a DISTINCTROW
Jak pochopit zápisy syntaxí
< par >
parametr, za který se dosazuje konkrétní hodnota
{A|B|C} povinná volba jedné z uvedených možností
|
oddělení variant
[ ]
nepovinná část
…
možnost opakování
’
označení konstant typu řetězec znaků
Syntaxe příkazu SELECT
SELECT <seznam výstupních sloupců>
FROM <seznam tabulek>
[WHERE <podmínka řádku>]
[GROUP BY <seznam výrazů seskupení>]
[HAVING <podmínka skupiny>]
[{UNION|UNION ALL|INTERSECT|MINUS} <příkaz SELECT>]
[ORDER BY <seznam kritérií třídění>]
<seznam výstupních sloupců> =[ALL|DISTINCT|DISTINCTROW]
{ * | <specifikace sloupce1> [, <specifikace sloupce2> [, …]] }
<specifikace sloupce> =
{ <název sloupce> |
< specifikace tabulky > . <název sloupce> |
< specifikace tabulky > . * |
výraz}
[[AS] <pojmenování sloupce> ]
< specifikace tabulky > =
{ <název tabulky> |
<pojmenování tabulky> |
<název pohledu> }
[<pojmenování tabulky>]
•
•
•
DISTINCT - ve výsledku nebudou duplicitní řádky, které mají stejné hodnoty ve vypisovaných
sloupcích
DISTINCTROW - ve výsledku nebudou duplicitní řádky, které mají stejné hodnoty ve všech
sloupcích bez ohledu na to, zda jsou vypisovány
ALL – ve výsledku budou všechny řádky bez ohledu na duplicity, defaultní nastavení
9
Příklad:
SELECT * FROM dodavatele WHERE mesto = 'Praha 4';
SELECT dodavatel_id, nazev, zastoupeni FROM dodavatele
WHERE dodavatel_id > 4;
Řazení řádků ve výsledku SELECT
SELECT <seznam výstupních sloupců>
FROM <seznam tabulek>
[WHERE <podmínka řádku>]
[GROUP BY <seznam výrazů seskupení>]
[HAVING <podmínka skupiny>]
[{UNION|UNION ALL|INTERSECT|MINUS} <příkaz SELECT>]
[ORDER BY <seznam kritérií třídění>]
<seznam kritérií třídění>= <kritérium třídění 1>
[<kritérium třídění 2> [, …]]
<kritérium třídění>= {<výraz> | <specifikace sloupce> | <pořadí sloupce>} }
[{ ASC | DESC }]
Ve výsledku budou řádky seřazeny dle požadavků uvedených za klauzulí ORDER BY:
ASC .. Vzestupně
DESC .. Sestupně
SELECT nazev FROM dodavateleWHERE mesto <> 'Ostrava'ORDER BY nazev DESC;
SELECT nazev, mesto FROM dodavatele WHERE mesto <> 'Ostrava'
ORDER BY 2 ASC,1 DESC;
SELECT mesto, zastoupeni FROM dodavatele WHERE nazev <> ' IBM'
ORDER BY mesto ASC, zastoupeni DESC ;
Aliasy – příklad
Příklad
SQL> SELECT prd.oznaceni AS Vyrobek, dod.Nazev Vyrobce
FROM Produkty prd, Dodavatele dod
WHERE dod.dodavatel_id = prd.dodavatel_id;
Vyrobce
Vyrobek
A600N
A2500H
A8JN-4P019M
Lifebook S2110
Asus
Asus
Asus
Fujitsu Siemens
Aliasy – kdy se bez nich neobejdeme ?
Příklad - spojení tabulky s tou samou tabulkou
Máme dánu tabulku:
Lide (Id, jmeno, prijmeni, narozen, pohlaví, Id_otce, Id_matky)
Zjistěte, kolik dětí má matku Boženu Malou?
SELECT COUNT(*) AS pocet_deti
FROM lide deti
JOIN lide rodice ON deti.Id_matky=rodice.Id
WHERE rodice.jmeno LIKE 'Božena' AND rodice.prijmeni LIKE 'Malá';
10
5 Příkazy Insert, Update, Delete
Syntaxe příkazu INSERT
INSERT INTO <název tabulky>
[(<název sloupce1> [, <název sloupce2> [, … ] ]) ]
{<příkaz SELECT> |
VALUES [(<výraz1> [, <výraz2> [, … ] ]) ] }
Nový (jeden) řádek přidáme do tabulky uvedením hodnot pro všechny sloupce v tabulce.Více řádků najednou
přidáme do tabulky pomocí vnořeného dotazu SELECT.
Příklad:
INSERT INTO dodavatele (dodavatel_id, nazev, zastoupeni, mesto)
VALUES (1, 'Acer','Acer Czech Republic s.r.o.','Praha 4');
INSERT INTO dodavatele (dodavatel_id, nazev, zastoupeni, mesto)
VALUES (2, 'Asus','ASUS Czech s.r.o.','Ostrava');
Syntaxe příkazu UPDATE
UPDATE <název tabulky> [<pojmenování tabulky>]
SET <název sloupce1> = <výraz1>
[, <název sloupce2> = <výraz2> [, … ] ]
[WHERE <podmínka řádku>]
Změna hodnot ve sloupcích u těch řádků tabulky, které splňují podmínku uvedenou za klauzulí WHERE.
Pokud není klauzule WHERE uvedena, bude provedena změna u VŠECH řádků tabulky.
Příklad:
UPDATE dodavatele SET nazev = 'HP' WHERE nazev = 'Compaq';
Syntaxe příkazu DELETE
DELETE FROM <název tabulky>
[WHERE <podmínka>]
Vymazání těch řádků tabulky, které splňují podmínku uvedenou za klauzulí WHERE.
Pokud není klauzule WHERE uvedena, budou odstraněny VŠECHNY řádky tabulky.
Příklad:
DELETE FROM produkty WHERE dodavatel_id = 7 or dodavatel_id > 10;
6 Hodnota NULL, operátory, výrazy, podmínky
Hodnota NULL??????
Aritmetické operátory
Umožňují na základě původních hodnot vypočítat hodnoty nové.
Sčítání +
(unární i binární)
Odčítání
(unární i binární)
Násobení
*
Dělení
/
Priorita
• Nejprve se vyhodnocují části výrazu uzavřené v závorkách
• Násobení a dělení mají přednost před sčítáním a odečítáním
• Operátory se stejnou prioritou se vyhodnocují zleva doprava
Operátor zřetězení
Umožňuje spojovat hodnoty ze sloupců a znakové řetězce
11
Zřetězení
||
Příklad
SELECT nazev, mesto || ' ' || zastoupeni AS pobocka FROM dodavatele;
Operátory pro porovnávání
=
rovnost
<>
nerovnost
<
menší než
>
větší než
<=
menší nebo rovno
>=
větší nebo rovno
[NOT] BETWEEN x AND y
[není] větší nebo rovno x a menší nebo rovno y
[NOT] IN
[ne] patří do množiny
ANY, SOME
porovnání hodnoty s každou hodnotou v seznamu nebo řádkem vnořeného dotazu, musí být
doplněn jedním z operátorů =, <,>, <=, >=. Výraz je pravdivý, pokud je pravdivý alespoň pro JEDNU položku
seznamu.
ALL porovnání hodnoty s každou hodnotou v seznamu nebo řádkem vnořeného dotazu, musí být doplněn
jedním z operátorů =, <,>, <=, >=. Výraz je pravdivý, pokud je pravdivý pro VŠECHNY položky seznamu.
[NOT] EXISTS ve vnořeném dotazu je vrácen alespoň jeden řádek
IS [NOT] NULL test na [ne] rovnost NULL
X [NOT] LIKE y
porovnání řetězce s maskou obsahující zástupné znaky
_
právě jeden libovolný znak
%
nula nebo více libovolných znaků
Příklady
SELECT oznaceni FROM produkty WHERE cena BETWEEN 20000 AND 30000
SELECT Produkty.oznaceni FROM Produkty, Dodavatele
WHERE dodavatele.dodavatel_id = produkty.dodavatel_id
AND dodavatel.nazev IN (‘UMAX’, ‘Asus’)
SELECT oznaceni FROM produkty WHERE cena = ANY (20000, 30000, 25000, 35000)
SELECT oznaceni FROM produkty WHERE cena>= ALL (SELECT cena FROM produkty)
SELECT …. LIKE
LIKE umožňuje využití zástupných znaků při definici podmínek za WHERE v SQL příkazech jako select,
insert, update, delete.
% nahrazuje libovolný řetězec libovolné délky (i nulové délky)
_ nahrazuje libovolný jeden znak
Příklady:
SELECT * FROM dodavatele WHERE mesto LIKE '%ra%';
SELECT * FROM dodavatele WHERE mesto NOT LIKE 'Pha%';
SELECT * FROM produkty WHERE oznaceni LIKE 'TravelMate 2__0';
SELECT oznaceni FROM produkty WHERE EXISTS (SELECT * FROM dodavatele
WHERE dodavatele.dodavatel_id = produkty.dodavatel_id);
SELECT nazev FROM dodavatele WHERE zastoupeni IS NULL;
12
SELECT nazev, oznaceni FROM dodavatele, produkty
WHERE dodavatele.dodavatel_id = produkty.dodavatel_id
AND oznaceni LIKE 'VAIO%';
Logické operátory
AND - a zároveň (vrací ANO, pokud oba operandy jsou zároveň ANO)
OR – nebo (vrací ANO, pokud alespoň jeden operand je ANO)
NOT - není pravda, že (vrací ANO, když následující operand je NE)
Výrazy
Výraz je skupina konstant, proměnných a funkcí spojených pomocí operátorů. Výsledkem je hodnota. Datový
typ je odvozen z datových typů jednotlivých prvků ve výrazu.
Automatická konverze datových typů
(například znaky na číslo při sčítání atd.)
Základními kameny výrazů jsou
•
•
•
•
•
Názvy sloupců
Textové konstanty
Číselné konstanty
Výsledky funkcí
Hodnota NULL
•
•
•
Uzavření do závorek
Unární (+/-) a binární operátory
Vnořené dotazy (SELECT ….)
7 Vnitřní a vnější spojení tabulek
Druhy spojení
(všeobecné) spojení (join):
spojení založené na libovolném typu vztahu mezi hodnotami propojovacích položek
ekvivalentní spojení (equi-join):
spojení založené na shodě hodnot v propojovacích položkách
spojení nerovností (non-equi-join):
spojení založené na nerovnosti hodnot v propojovacích položkách
Příklad spojení nerovností:
Seznam čtenářů knihovny a seznam měst, v nichž tito čtenáři nebydlí (spojovací položkou je kód města)
vnitřní spojení (inner join, exkluzívní spojení):
spojení, v němž jsou záznamy ze dvou tabulek kombinovány a přidávány k výsledkům dotazu pouze tehdy,
když k záznamům z první tabulky existují odpovídající záznamy v tabulce druhé
vnější spojení (outer join, inkluzívní spojení):
spojení, kdy je každý odpovídající záznam ze dvou tabulek kombinován do jednoho záznamu ve výsledku
dotazu. Není-li k záznamu z tabulky, která poskytuje všechny své záznamy, nalezen odpovídající záznam ve
druhé tabulce, je přesto zahrnut do výsledků dotazu s prázdnými poli v místech, kde nebyl nalezen
odpovídající záznam ve druhé tabulce.
samospojení (self–join, recursive join):
spojení, v němž jsou záznamy z tabulky kombinovány s jinými záznamy z téže tabulky
Spojení tabulek – syntaxe SELECT
13
Varianta 1 – vnitřní spojení
<seznam tabulek> =[ < specifikace tabulky1> [, <specifikace tabulky2> [, …]] }
Sloupce, přes které se realizuje spojení se uvádějí v podmínce za klíčovým slovem WHERE <podmínky
spojení> .
Příklady
SELECT dodavatele.nazev, produkty.oznaceni FROM dodavatele, produkty
WHERE dodavatele.dodavatel_id = produkty.dodavatel_id;
Varianta 2 – základní syntaxe pro vnitřní spojení
<seznam tabulek> = < specifikace tabulky1>
JOIN < specifikace tabulky2> ON < podmínky spojení>
[ JOIN < specifikace tabulky3> ON < podmínky spojení> [, … ] ]
Příklad
SELECT dodavatele.nazev, produkty.oznaceni FROM dodavatele
JOIN produkty ON dodavatele.dodavatel_id = produkty.dodavatel_id;
Vnitřní spojení INNER
INNER JOIN, JOIN
Do výsledku budou zahrnuty pouze ty řádky, pro které byla nalezena odpovídající hodnota v druhé
tabulce.
Vnější spojení OUTER
Ve výsledku budou i ty řádky, pro které nebyly nalezeny odpovídající hodnoty v druhé tabulce.
- Pravé (RIGHT JOIN) .. Ve výsledku budou všechny řádky z pravé (druhé tabulky). Nebyl-li nalezen
odpovídající řádek v levé tabulce, budou ve výsledku hodnoty NULL ve všech sloupcích z první
tabulky.
- Levé (LEFT JOIN) .. Ve výsledku budou všechny řádky z levé (první tabulky). Nebyl-li nalezen
odpovídající řádek v pravé tabulce, budou ve výsledku hodnoty NULL ve všech sloupcích z druhé
tabulky.
- Úplné (FULL JOIN) .. Ve výsledku budou všechny řádky z levé i pravé (první i druhé tabulky).
Nebyl-li nalezen odpovídající řádek v pravé tabulce, budou ve výsledku hodnoty NULL ve všech
sloupcích, pro něž nebyly nalezeny odpovídající hodnoty ve spojované tabulce.
Varianta 3 – obecný zápis syntaxe pro různé druhy spojení
<seznam tabulek> = < specifikace tabulky1>
[ { INNER | { LEFT | RIGHT | FULL } [OUTER] }] JOIN < specifikace tabulky2>
ON < podmínky spojení>
[ { INNER | { LEFT | RIGHT | FULL } [OUTER] }] JOIN < specifikace tabulky3>
ON < podmínky spojení> [, … ] ] ]
Příklad
SELECT dodavatele.nazev, produkty.oznaceni FROM dodavatele JOIN produkty
ON dodavatele.dodavatel_id = produkty.dodavatel_id;
Spojení tabulek
Příklad - úplné vnější spojení
SQL> SELECT Produkty.produkt_id, Dodavatele.Nazev FROM Produkty FULL OUTER JOIN Dodavatele ON
dodavatele.dodavatel_id = produkty.dodavatel_id;
Příklady využití vnějších spojení:
- Zobrazení všech dodavatelů, tedy i těch, kteří nedodávají žádný výrobek
- Zobrazení všech výrobků, tedy i těch, které nejsou přiřazeny žádnému dodavateli
- S využitím agregačních funkcí zobrazení počtu výrobků, které dodávají jednotliví dodavatelé (s vnějším
spojením se zobrazí i hodnoty 0 u těch dodavatelů, kteří žádný výrobek nedodávají,
v případě vnitřního spojení ve výsledku nebudou vůbec zahnuti)
14
8 Souhrnné a skupinové dotazy, agregační funkce
Souhrnné dotazy– syntaxe SELECT
Velice často nás nezajímají jednotlivé detaily uložené v konkrétních řádcích v jednotlivých tabulkách, ale
několik málo souhrnných informací, které sumarizují obsah databáze, například:
- Kolik zaměstnanců má společnost ?
- Jaké jsou tržby jednotlivých poboček společnosti ?
- Kolik lidí jede průměrně v jednom vlaku ?
- Jaká je průměrná délka života ?
- Jaká je průměrná mzda ?
- Kolik vstupenek se prodalo na jednotlivá utkání ?
- Jaká je nejvyšší a nejnižší vyplácená sociální dálka ?
- Kdo získal nejméně bodů z testu ?
Agregační funkce pracují nad množinou řádků, přičemž vrací právě jeden výsledek pro celou vstupní množinu
dat.
Tyto funkce pomocí matematických a statistických operací zpracovávají agregované hodnoty z celých sloupců
(není-li uvedeno jinak v podmínce).
- AVG ([DISTINCT] [ALL] výraz)aritmetický průměr
- COUNT ([DISTINCT] [ALL] výraz)
počet hodnot
- MAX (výraz)
maximum
- MIN (výraz)
minimum
- SUM ([DISTINCT] [ALL] výraz)
součet hodnot
Sloupcové funkce tedy ignorují hodnoty NULL !!!
9 Vnořené dotazy, množinové operátory
Vnořené dotazy
Vnořené dotazy (poddotazy) se mohou vyskytovat prakticky na libovolném místě hlavního dotazu. Výsledkem
vnořeného dotazu je obecně „virtuálního tabulka“ o několika řádcích a několika sloupcích (v určitých
případech pak jednosloupcová či jednořádková) nebo jen jedna jediná hodnota.
a) Vnořený dotaz v klauzuli FROM nebo JOIN – s výsledkem poddotazu se pracuje stejně jako při spojení s
tabulkou či pohledem, výsledek poddotazu je v tomto případě vhodné označit aliasem
b) Vnořený dotaz vracející jednu hodnotu v části WHERE – např. výsledek agregační funkce (SUM,
COUNT, MIN, MAX, AVG) se porovnává s hodnotou v určitém sloupci tabulky (nebo je vnořeným dotazem
vyhledána hodnota v určitém sloupci, kdy řádek tabulky je identifikován pomocí primárního klíče)
c) Použití vnořeného dotazu v části WHERE s využitím množinových operátorů IN, ANY, SOME, ALL
d) Využití výsledků vnořeného dotazu pomocí operací – UNION, INTERSECT, MINUS, …
e) Použití vnořeného dotazu v části definice výstupních sloupců dotazu, kde se prostřednictvím vnořeného
dotazu doplňují hodnoty na základě ostatních hodnot v daném řádku
SELECT (select c1 from t1 b where a.c1 = b.c1), c2 FROM t1 a WHERE <condition>
15
•
•
•
•
Jednořádkové jednosloupcové vnořené dotazy vrací jen jednu hodnotu
Jednořádkové vícesloupcové vnořené dotazy vrací jen jeden řádek, ale více sloupců (například jeden řádek
tabulky nebo výsledek souhrnného dotazu)
Víceřádkové jednosloupcové vnořené dotazy vrací více řádků, ale jen jediný sloupec (například seznam
všech různých hodnot v konkrétním sloupci)
Vícesloupcové víceřádkové vnořené dotazy, výsledkem je tabulka s více sloupci a více řádky (obecný
stav)
Vnořené dotazy musí být zapsány v závorkách (..). Nejdříve se provede vnořený dotaz a teprve nad jeho
výsledky se aplikuje hlavní dotaz.Vnořený dotaz může být umístěn do hlavního dotazu například:
• za klauzuli FROM
• za klauzuli WHERE
• za klauzuli HAVING
• případně v dalších částech (i v části bezprostředně za SELECT)
10 Funkce jazyka SQL. Práce s datovým typem DATE
Funkce
Funkce jsou programové bloky, které provádějí požadované operace například s číselnými, znakovými či
datovými hodnotami.
Obecně můžeme FUNKCE rozdělit na:
a) Jednořádkové funkce
• Funkce pro práci se znakovými řetězci
• Konverzní funkce
• Matematické funkce
• Funkce pro práci s datovými a časovými hodnotami
• Pokročilé funkce
• Funkce pro ošetření chyb
• Ostatní funkce
b) Agregační funkce
Funkce pro práci s řetězci
- Upper(řet) - konverze na velká písmena
- Lower(řet) - konverze na malá písmena
- Initcap(řet) - převede první písmeno každého slova na velké
- Lpad(řet1, délka, řet2) - doplnění řet1 zleva na požadovanou délku řet2
- Rpad(řet1, délka, řet2) - doplnění řet1 zprava na požadovanou délku řet2
- Concat(řet1, řet2) - spojení řetězců
- Length(řet) - vrací délku řetězce
- Substr(řet, pozice, délka) - výběr podmnožiny znaků z řet od pozice dané délky
- Chr(n) - znak odpovídající danému číslu
- Trim - odstraní zadané znaky zleva (případně zprava) řetězce
- Ltrim(řet1 [, řet2]) - odstraní z levé části řetězce1 všechny znaky, které obsahuje řětězec2
- Rtrim(řet1 [, řet2]) - odstraní z pravé části řetězce1 všechny znaky, které obsahuje řětězec2
- Translate(řet1, řet2 [, řet3]) - nahradí všechny výskyty každého znaku z druhého parametru v řet1
odpovídajícím znakem z řet3
- Replace(řet1, řet2 [, řet3]) - nahradí všechny výskyty řet2 v řet1 řet3
Matematické funkce
a) Goniometrické funkce - Sin(n), Cos(n), Tan(n), kde n je v radiánech
b) Inverzní goniometrické funkce - Asin(n), Acos(n), Atan(n)
16
c) Hyperbolické funkce - Sinh(n), Cosh(n), Tanh(n)
d) Logaritmické a exponenciální funkce - Log(základ, n), Ln(n), Exp(n)
e)
f)
Funkce pro úpravu a zaokrouhlování
Abs(n)
absolutní hodnota,
Sign(n)
informuje o znaménku čísla,
Round(n, [m]) zaokrouhlení na požadovaný počet desetinných míst,
Trunc(n, [m])
oříznutí čísla na požadovaný počet desetinných míst,
Floor(n)
největší celé číslo menší nebo rovno n,
Ceil(n)
nejmenší celé číslo větší nebo rovno n,
Ostatní matematické funkce
Power(m, n)
n-tá mocnina čísla m,
Sqrt(m)
druhá odmocnina čísla m,
Mod(dělenec, dělitel)
zbytek po dělení,
Bitand(m, n)
bitový součin
Agregační funkce
aritmetický průměr
AVG ([DISTINCT] [ALL] výraz)
počet hodnot
COUNT ([DISTINCT] [ALL] výraz)
maximum
MAX (výraz)
minimum
MIN (výraz)
součet hodnot
SUM ([DISTINCT] [ALL] výraz)
Konverzní funkce
- Bin_To_Num(posloupnost 0 a 1 oddělená čárkami) - převod binárního čísla na číslo desítkové soustavy
- To_Char(řet) - převádí řetězec do standardní znakové sady
- To_Char(number [, formátovací_řetězec, nsl_par]) - převádí datový typ NUMBER na typ VARCHAR2
- To_Char(datetime [, formátovací_řetězec, nsl_par]) - převádí datové typy na typ VARCHAR2
- To_Date(řet, formátovací_řetězec [, nsl_par])- převádí textové datové typy na datový typ DATE
- To_Number(řet, formátovací_řetězec [, nsl_par]) - převádí datový typ VARCHAR2 na číselný typ
NUMBER
nsl_par … parametr určující národní formát pro zápis data a času, čísel ..
Parametry formátovacího řetězce funkce TO_CHAR pro práci s datumem a časem
YYYY
4-číslicový formát roku
YYY, YY, Y
Poslední 3, 2 nebo 1 číslice roku
Q
Čtvrtletí (1, 2, 3, 4)
MM
Měsíc (01-12; JAN = 01)
MON
Měsíc vyjádřený 3 znaky
MONTH
Měsíc celým názvem velkými písmeny
RM
Měsíc vyjádřený římskými číslicemi (I-XII; JAN = I)
WW
Týden v roce (1-53)
W
Týden v rámci měsíce (1-5)
D
Den v týdnu (1-7)
DAY
Den v týdnu vyjádřený slovně velkými písmeny
DD
Den v měsíci (1-31)
DDD
Den v roce (1-366)
DY
Den v týdnu vyjádřený pomocí zkratky názvu dne
HH
Hodina (1-12)
HH12
Hodina vyjádřená ve 12-hodinovém formátu (1-12)
HH24
Hodina vyjádřená ve 24-hodinovém formátu (0-23)
MI
Minuta (0-59)
SS
Sekunda (0-59)
SSSSS
Sekundy od půlnoci (0-86399)
17
Konverzní funkce TO_CHAR
Příklady pro práci s datem a časem:
to_char(sysdate, 'yyyy/mm/dd');
'2003/07/09'
to_char(sysdate, 'Month DD, YYYY');
Říjen 16, 2006'
to_char(sysdate, 'Month DD, YYYY', 'NLS_DATE_LANGUAGE=American')
October 16, 2006
to_char(sysdate, 'Month DD, YYYY', 'NLS_DATE_LANGUAGE=Czech')
Říjen 16, 2006
Parametry formátovacího řetězce funkce TO_CHAR pro práci s čísly
9
Symbol pro číslo se stanoveným počtem číslic
(je-li číslo kratší, je doplněno mezerami a před záporným je znak mínus)
0
Číslo je na začátku nebo konci doplněné nulami
$
Na začátku čísla je znak $
B
Symbol zabrání vypsání čísla, pokud je jeho hodnota nula
D
Symbol pro určení oddělovače desetinných číslic
G
Symbol pro určení oddělovače tisíců
L
Symbol pro určení lokálního symbolu měny
,
Na příslušné pozici je čárka
.
Na příslušné pozici je tečka
V
číslo je zobrazené v exponenciálním formátu
RN
číslo je zobrazené velkými římskými číslicemi (do 3999)
FM
číslo je zobrazené bez úvodních mezer
EEEE
číslo ve vědeckém formátu
Konverzní funkce TO_CHAR
Příklady pro práci s čísly:
to_char(1210.73, '9999.9')
to_char(1210.73, '9,999.99')
to_char(1210.73, '$9,999.000')
to_char(21, '000099')
'1210.7'
'1,210.73'
'$1,210.730'
'000021'
Funkce zaokrouhlování data a času
Round(datum, zaokrouhlení)
zaokrouhlení data
datum
původní datum
zaokrouhlení
YEAR na celé roky
MONTH
na celé měsíce
DDD
na celé dny
DAY
první den v týdnu
HH
na celé hodiny
MI
na celé minuty
Trunc(datum, část)
ořezání data
(parametry stejné jako u Round)
Extract(část FROM datum)
vrací požadovanou část z data
(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)
11 Fáze návrhu databáze, E-R diagramy
Fáze návrhu databáze
18
V první fázi se prostřednictvím konzultací s uživateli a zadavateli systému formulují a shromažďují přesné
požadavky na to, co vše má být v databázi uloženo. Z takto získaných informací se vytvoří konceptuální
model, který je výsledkem první fáze.
Konceptuální datový model popisuje data na abstraktní úrovni nezávisle na jejich fyzickém uložení. Proces
tvorby konceptuálního modelu se nazývá konceptuální modelování.
Jeho výsledkem je konceptuální model znázorněný jako konceptuální schéma nebo diagram, který má co
nejvýstižněji zachycovat pohled člověka na danou část reálného světa. Mezi nejznámější konceptuální datové
modely patří E-R model. Při tvorbě konceptuálního datového modelu je nutné zajistit formulaci
– úkolů a
– cílů úkolů.
Formulace úkolu určuje, k čemu má databáze sloužit (účel).
Cíle úkolu jsou tvrzení, která reprezentují obecné úkony, které uživatel může s daty v databázi provádět. Z
tohoto důvodu je důležité, aby se kromě manažerů zadavatele podíleli na definování cílů úkolu i koncoví
uživatelé, neboť ti budou výsledek naší práce používat.
ER modelování lze metodologicky rozdělit do dvou kroků:
• v prvním kroku se definují nezávislé entity, vztahy a závislé entity, to se opakuje do té doby, než se
dospěje k souhlasu mezi zadavateli a analytiky;
• v druhém kroku se formulují atributy a klíče entit – klíčů může být v tomto stádiu i více.
E-R diagram
ER diagram byl zaveden a poprvé použit Peterem Chenem v roce 1976. Brzy došlo k jeho rozšíření a stal se
obecně uznávaným standardem. Diagram obsahuje typy entit a typy vztahů.
Entita představuje nezávisle existující objekt reálného světa.
Vztah je vazba mezi dvěma nebo více entitami.
Atribut je funkce přiřazující entitám či vztahům hodnotu, určuje vlastnost entity nebo vztahu.
Příklad ER diagramu
19
ER diagram – Chenův styl
ER diagram – „inženýrský“ styl
ER diagram – „zjednodušený“ styl
Pojmy z relací
• Identifikující relace je taková, kdy cizí klíč je součástí primárního klíče závislé (dceřiné) entity, znázorňují se
obvykle plnou čarou.
• Neidentifikující relace je taková, kdy cizí klíč je neklíčovým atributem závislé (dceřiné) entity, znázorňují se
obvykle čárkovanou čarou.
Závislé entity
• Závislé entity jsou takové, jejichž existence je závislá na jedné nebo více jiných entit
(např. entita řádek faktury je závislá na entitách
výrobek a faktura)
• Toto je důležité při fyzickém návrhu, kdy je třeba ošetřit situace, kdy má být odstraněn určitý výrobek nebo
faktura.
20
Příklad závislé entity, identifikujících a neidentifikujících relací
•
Entita učí je závislou entitou, obě relace k ní vedoucí jsou identifikující
Kardinalita vztahu
Vztah 1:1
Vztah 1:N
Vztah M:N
Unární vztahy
- Tabulka je spojena sama se sebou
- Příkladem takového stavu je model Zaměstnanec - Nadřízený (nadřízený je také jedním ze zaměstnanců a
může mít dalšího nadřízeného)
- V tomto případě tabulka obsahuje primární klíč i cizí klíč, který se odkazuje na tabulku samou
- Uvedený příklad demonstruje obrázek níže
Parcialita vztahu
Parcialita vyjadřuje povinnost či nepovinnost existence ve vztahu.
Vztah jednostranně parciální znamená, že například zaměstnanec musí náležet k jedné pojišťovně, pojišťovna
však nemusí mít v evidenci ani jednoho zaměstnance (ale může jich mít i více).
Vztah oboustranně parciální vyjadřuje, že zaměstnanec nemusí náležet k žádné (může náležet k jediné)
zdravotní pojišťovně a zdravotní pojišťovna nemusí mít v evidenci ani jednoho zaměstnance.
12 Normální formy, normalizace tabulek, dekompozice
Normální formy tabulek se používají pro lepší (systematické) návrhy databázových systémů pro efektivní ukládání dat a
minimalizace redundancí při zachování integrity a konzistence dat. Obecně platí, že čím je tabulka ve vyšší normální
formě, tím kvalitněji je tabulka navržena. Normalizace je postupná dekompozice tabulek do vhodnějšího tvaru, tak
aby:
- byla zachována bezztrátovost při zpětném spojení,
- byly zachovány závislosti,
21
-
•
bylo odstraněno opakování informací (tzv. redundance).
0.NF (nultá normální forma): Tabulka je v nulté normální formě právě tehdy, existuje-li alespoň jedno pole,
které obsahuje více než jednu hodnotu.
•
1.NF (první normální forma): Tabulka je v první normální formě, jestliže lze do každého pole dosadit pouze
jednoduchý datový typ (atributy jsou dále nedělitelné, tzv. atomické a tentýž atribut se neopakuje ve stejné
tabulce).
•
2.NF (druhá normální forma): Tabulka je ve druhé normální formě, jestliže je v první NF a navíc platí, že
existuje klíč a všechna neklíčová pole jsou funkcí celého klíče (a tedy ne jen jeho částí).
•
3.NF (třetí normální forma): Tabulka je ve třetí normální formě, jestliže každý neklíčový atribut není
transitivně závislý na žádném klíči schématu neboli, je-li ve druhé normální formě a zároveň neexistuje jediná
závislost neklíčových sloupců tabulky.
BCNF (Boyce-Coddova normální forma): Tabulka je v Boyce-Coddově normální formě, jestliže pro každou
netriviální závislost X-->Y platí, že X obsahuje klíč schématu R.
•
•
4.NF (čtvrtá normální forma): Tabulka je ve čtvrté normální formě,
je-li ve třetí a popisuje pouze příčinnou souvislost (jeden fakt).
•
5.NF (pátá normální forma): Tabulka je v páté normální formě, pokud je ve čtvrté a není možné do ní přidat
nový sloupec (skupinu sloupců) tak, aby se vlivem skrytých závislostí rozpadla na několik dílčích tabulek.
Normální formy - BCNF
• BCNF (Boyce-Coddova normální forma): Tabulka je v Boyce-Coddově normální formě, jestliže pro každou
netriviální závislost X-->Y platí, že X obsahuje klíč schématu R.
V tabulce může existovat několik kandidátních klíčů,
• kandidátní klíče mohou být složené,
• 3.NF připouští tranzitivní závislosti mezi klíčovými atributy,
• kandidátní klíče se mohou překrývat .
Pojem normálních forem se používá ve spojitosti s dobře navrženými tabulkami. Správně vytvořené tabulky splňují 4
základní normální formy.
0. normální forma
(nultá normální forma): Tabulka je v nulté normální formě právě tehdy, existuje-li alespoň jedno pole, které
obsahuje více než jednu hodnotu.
1. normální forma (1NF)
První, nejjednodušší, normální forma (značíme 1NF) říká, že všechny atributy jsou atomické, tj. dále již
nedělitelné (jinými slovy, hodnotou nesmí být relace). Mějme např. tabulku ADRESA, která bude mít sloupce
JMÉNO, PŘÍJMENÍ a BYDLIŠTĚ. Naplnění tabulky nechť odpovídá reálnému světu:
JMÉNO PŘÍJMENÍ
BYDLIŠTĚ
22
jan
petr
jan
novák
nový
nováček
Ostravská 16, Praha 16000
Svitavská 8, Brno 61400
Na bradlech 1147, Ostrava 79002
Pokud bychom v této tabulce chtěli vypsat všechny pracovníky, jejichž PSČ je rovno určité hodnotě, dostali
bychom se do potíží, neboť bychom to nemohli zjistit přímo a jednoduše. A to proto, že atribut BYDLIŠTĚ
není atomický, skládá se z několika částí: ULICE, ČÍSLO, MĚSTO a PSČ.
Správný návrh tabulky, který bude respektovat 1NF bude vypadat následovně:
JMÉNO PŘÍJMENÍ ULICE ČÍSLO MĚSTO PSČ
jan
novák
Ostravská 16
Praha 16000
petr
nový
Svitavská 8
Brno
61400
jan
nováček
Na bradlech 1147 Ostrava 79002
Obecně bychom se měli snažit, aby obsahem jedné databázové položky byla právě jedna hodnota (určitého
databázového typu).
2. normální forma (2NF)
Tabulka splňuje 2NF, právě když splňuje 1NF a navíc každý atribut, který není primárním klíčem je na
primárním klíči úplně závislý. To znamená, že se nesmí v řádku tabulky objevit položka, která by byla závislá
jen na části primárního klíče. Z definice vyplývá, že problém 2NF se týká jenom tabulek, kde volíme za
primární klíč více položek než jednu. Jinými slovy, pokud má tabulka jako primární klíč jenom jeden sloupec,
pak 2NF je splněna triviálně. Nechť máme tabulku PRACOVNÍK, která bude vypadat následovně (atribut
ČÍS_PRAC značí číslo pracoviště, kde daný pracovník pracuje, atribut NÁZEV_PRAC uvádí jméno daného
pracoviště):
ČÍSLO JMÉNO PŘÍJMENÍ ČÍS_PRAC NÁZEV_PRAC
1
jan
novák
10
studovna
2
petr
nový
15
centrála
3
jan
nováček
10
studovna
Jaký primární klíč zvolíme v této tabulce? Pokud zvolíme pouze ČÍSLO, je to špatně, neboť zcela určitě název
pracoviště, kde zaměstnanec pracuje, není závislý na číslu pracovníka. Takže za primární klíč musíme vzít
dvojici (ČÍSLO,ČIS_PRAC). Tím nám ovšem vznikl nový problém. Položky JMÉNO, PŘÍJMENÍ a
NÁZEV_PRAC nejsou úplně závislé na dvojici zvoleného primární klíče. Ať tedy děláme, co děláme, nejsme
schopni vybrat takový primární klíč, aby tabulka splňovala 2NF. Jak z tohoto problému ven? Obecně převedení
do tabulky, která již bude splňovat 2NF, znamená rozpad na dvě a více tabulek, kde každá už bude splňovat
2NF. Takovému "rozpadu" na více tabulek se odborně říká dekompozice relačního schématu.
Správně navržené tabulky splňující 2NF budou vypadat následovně (tabulka PRACOVNÍK a
PRACOVIŠTĚ):
ČÍSLO JMÉNO PŘÍJMENÍ ČIS_PRAC
1
jan
novák
10
2
petr
nový
15
3
jan
nováček
10
ČÍSLO NÁZEV
10
studovna
15
centrála
Dále si všimněte, že pokud tabulka nesplňuje 2NF, dochází často k redundanci. Konkrétně v původní tabulce
informace, že pracoviště číslo 10 se jmenuje "studovna", byla obsažena celkem dvakrát. Redundance je jev,
který obvykle nesplnění 2NF doprovází. O tom, že redundance je nežádoucí, netřeba pochybovat. Zkuste si
rozmyslet, jak byste postupovali v obou příkladech, kdyby ve vaší společnosti došlo ke změně názvu
pracoviště číslo 10 ze "studovna" na "klubovna".
3. normální forma (3NF)
Relační tabulky splňují třetí normální formu (3NF), jestliže splňují 2NF a žádný atribut, který není primárním
23
klíčem, není tranzitivně závislý na žádném klíči. Nejlépe to opět vysvětlí následující příklad. Mějme tabulku
PLATY, která bude vypadat takto:
ČÍSLO JMÉNO PŘÍJMENÍ FUNKCE PLAT
1
jan
novák
technik 15000
2
petr
nový
vedoucí 21500
3
jan
nováček
správce 17500
Pomineme zatím fakt, že tato tabulka nesplňuje ani 2NF, což je základní předpoklad pro 3NF. Chci zde jen
vysvětlit pojem tranzitivní závislost. Nebudeme přemýšlet, co je primární klíč, na první pohled vidíme, že
konkrétně atributy JMÉNO, PŘÍJMENÍ a FUNKCE závisí na atributu ČÍSLO (ten by nejspíš byl primárním
klíčem). Dále můžeme vidět, že atribut PLAT zřejmě je funkčně závislý na atributu FUNKCE a pokud
vezmeme v úvahu, že ČÍSLO->FUNKCE a FUNKCE->PLAT, dostaneme díky jevu nazývanému tranzitivita,
že ČÍSLO->PLAT. Postup, jak dostat tabulky do 3NF, je podobný jako v případě 2NF, tj. opět provedeme
dekompozici (tabulka FUNKCE a PLATY):
ČÍSLO JMÉNO PŘÍJMENÍ FUNKCE
1
jan
novák
technik
2
petr
nový
vedoucí
3
jan
nováček
správce
FUNKCE PLAT
technik
21500
vedoucí
17500
správce
15000
Z hlediska základních tří normálních forem, jsou tyto dvě tabulky již v pořádku. Z praktického hlediska je
vhodnější použít nějaký číselník funkcí, abychom splnili podmínku, že primární klíč v tabulkách má být co
nejkratší délky. Nejlepší zápis je tedy následující:
ČÍSLO JMÉNO PŘÍJMENÍ CIS_FUN
1
jan
novák
121
2
petr
nový
156
3
jan
nováček
127
ČÍSLO FUNKCE PLAT
121
technik 21500
156
vedoucí 17500
127
správce 15000
13 Vytváření tabulek, integritní omezení, primární a cizí klíče
Kandidátní klíč
• jednoznačně identifikuje řádek v tabulce
• atribut (případně množina atributů) se nazývá kandidátním klíčem, když má tyto dvě časově nezávislé
vlastnosti:
- Jednoznačnost identifikace řádku
- Minimalita (žádný atribut ve množině atributů není nadbytečný)
• každá tabulka má alespoň jeden kandidátní klíč,
• atribut, který je součástí kandidátního klíče se nazývá klíčový,
• je třeba skutečně ověřit, zda navržený kandidátní klíč bude skutečně jednoznačnou identifikací (po celou dobu
životnosti dané aplikace, rostoucího počtu řádků tabulky)I některé na první pohled jednoznačně vypadající
24
identifikátory mohou v praxi být nejednoznačné (například číslo vlaku na železnici, existují i duplicity rodných
čísel).
Primární klíč (Primary key, PK)
• jeden z kandidátních klíčů (vybraný), zbývající kandidátní klíče se nazývají alternativní (někdy také
sekundární).
• způsob výběru primárního klíče není v relačním modelu specifikován,
nicméně je třeba přihlížet k paměťovým a časovým nárokům při práci s klíčovými atributy
• primární klíč je základním prostředkem adresace n-tic v relačním modelu
Cizí klíč (Foreign key, FK)
• atribut se nazývá cizím klíčem, právě když splňuje tyto časově nezávislé vlastnosti:
– každá hodnota FK je buď plně zadaná nebo plně nezadaná (NULL),
– existuje tabulka T1 s primárním klíčem PK takovým,
že každá zadaná hodnota FK je identická s hodnotou PK
jediného řádku tabulky T1.
Pravidlo referenční integrity:
– DB nesmí obsahovat žádnou nesouhlasnou hodnotu cizího klíče,
– v praxi systém buď přímo podporuje cizí klíče nebo je nepodporuje a aplikace si referenční integritu
musí kontrolovat sama.
Integrita dat
Integrita dat = fakt, že data věrně (tj. přesně a konzistentně) zobrazují reálný stav, který popisují.
Základním předpokladem je kvalitně navržená datová základna, která omezí duplicity dat (ty jsou vždy
vysokým rizikem pro vznik nekonzistencí).
Důvody vzniků nekonzistencí
ktualizace dat
•
•
data se stávají neaktuálními
tj. nelze přidat/zrušit řádek nebo jej aktualizovat
vložení nesprávných hodnot
Vložená data neodpovídají realitě a tím znehodnocují celkovou informační hodnotu
referenční integrita
Při rušení řádku v jedné tabulce může nastat situace, kdy v jiné tabulce zůstanou informace,
které se k tomuto řádku vztahují (odkazují)
Zajištění integrity dat
Integrita dat se zajišťuje vytvářením omezení.
Omezení – objekt v relační databázi, který klade určitá pravidla na vkládaná data do určitého sloupce tabulky.
Integritní omezení může být definováno nad jedním nebo více sloupci.Nad jedním sloupcem/sloupci může být
definováno více integritních omezení.
Pro každý sloupec je tedy nezbytné definovat seznam omezení která dovolí, aby mohla být zadávána data
pouze určitých typů (např. číselná)
pouze určitých délek (např. 12 znaků)
pouze určitých hodnot (výčet hodnot, interval, …)
jedinečná v rámci tabulky
odkazující na jinou tabulku
uvedená
Realizace omezení
1) Volba správného datového typu sloupce řeší omezení
pouze určitých typů (např. číselná)
pouze určitých délek (např. 12 znaků)
2) Kontrola vztahů s jinou tabulkou se řeší použitím cizích klíčů
(FOREIGN KEY), které se odkazují na primární sloupec v odkazované tabulce
3) Kontrola jedinečnosti UNIQUE
25
4) Kontrola uvedení dat NOT NULL
5) Jednoznačná identifikace řádku v tabulce pomocí primárního klíče PRIMARY KEY – tj. omezení UNIQUE
a NOT NULL současně
6) Kontrola vkládaných hodnot klauzulí CHECK
7) Integrita s využitím triggerů
Správa omezení
Realizace omezení
Při vytváření tabulky příkazem CREATE TABLE
úpravou struktury tabulky příkazem ALTER TABLE
Nutno dodržet správné pořadí, tj. například před použitím cizího klíče musí být definován primární klíč v
tabulce, na níž se odkazuje.
Totéž platí pro příkazy DROP TABLE a podobně.
Stejný typ se týká i dat – nejdříve musí existovat řádek v tabulce rodičů a teprve poté je možné vložit/upravit
řádek v tabulce potomků.
14 Pohledy a jejich význam, sekvence
Pohledy
– umožní přístup jen k některým sloupcům tabulek
– možnost omezit přístupné řádky
– možnost skrýt skutečnou strukturu tabulek
Pohled je předpis pro získání podmnožiny dat z jedné či více tabulek.
Pohled obsahuje JEN předpis, NIKOLI data.
S pohledy se pracuje obdobně jako s vlastními tabulkami.
Určitá omezení souvisí například se změnou dat.
a) Jednoduché pohledy
- vytvořeny z dat jediné tabulky,
- neobsahují řádné funkce ani skupiny,
- můžeme v nich provádět i změny (nezakážeme-li to)
a) Komplexní pohledy
- jsou vytvořeny z dat z více databázových tabulek,
- mohou obsahovat funkce nebo skupiny,
- změnu dat umožňují jen ve velmi omezených případech
Pohledy – syntaxe
CREATE [OR REPLACE] VIEW název_pohledu [alias]
AS vnořený_dotaz [seznam_omezení];
Příklady omezení:
WITH READ ONLY – nelze vkládat, mazat ani měnit záznamy,
WITH CHECK OPTION – upravované či vkládané záznamy musí vyhovovat podmínce uvedené v
klausuli WHERE vnořeného dotazu.
DROP VIEW název_pohledu;
Pohledy – příklady 1
CREATE VIEW Pracovnici_pobocky AS SELECT Jmeno, Funkce, Pobocka
FROM Pracovnici WITH READ ONLY ;
Použití:
SELECT * FROM Pracovnici_pobocky;
26
Pohledy - význam
1) Zjednodušení konstrukce náročných dotazů,
2) Opakované využití vytvořeného pohledu na standardní použití
(minimalizace chyb, zrychlení práce, případné změny ovlivní například jen pohled),
3) Bezpečnost
– mohu definovat odlišná práva pro přístup k tabulce a pohledům (například omezení přístupu k citlivým
informacím).
Sekvence
- umožňují generovat jedinečné identifikátory, například hodnoty primárního klíče tabulek,
- databázová platforma Oracle používají tzv. sekvence, (v MySQL je např. volba autoincrement pro vybraný
sloupec tabulky)
- při použití sekvence například pro generování primárního klíče je dobré si uvědomit, zda sekvenčně zvolený
klíč je správné řešení (někdy je vhodné použít již existující primární klíče např. z evidence obyvatel atd.).
Syntaxe příkazu CREATE SEQUENCE
CREATE SEQUENCE <název sekvence>
[START WITH <číslo> ]
[INCREMENT BY <číslo> ]
[MAXVALUE <číslo> ]
[MINVALUE <číslo> ]
[CYCLE | NOCYCLE ]
[CACHE <číslo> | NOCACHE ]
START WITH počáteční hodnota generátoru, výchozí 1
INCREMENT BY
interval mezi dvěma za sebou generovanými čísly, výchozí 1
CYCLE
umožňuje opakované generování hodnot
MAXVALUE, MINVALUE maximální a minimální hodnota
CACHE
kolik hodnot se generuje do zásoby, výchozí 20
Použití SEQUENCE
Existují 2 pseudosloupce, které umožňují využití sekvence:
<název sekvence> . NEXTVAL
Vygeneruje a vrátí další hodnotu sekvence
<název sekvence> . CURRVAL
Vrátí aktuální hodnotu sekvence (ta musí být předtím aspoň jednou inicializována použitím <název sekvence> .
NEXTVAL)
15 Indexy – druhy indexů a jejich význam.
Fakt:
-
záznamy v tabulkách ukládány neuspořádaně, nejčastěji v pořadí,
v jakém byly do tabulky vkládány,
Bez indexů:
- záznamy hledáme od prvního záznamu postupně, dokud hledaný nenajdeme,
- v průměru vždy prohledáme asi polovinu záznamů, než najdeme jeden hledaný záznam
- tyto problémy řeší přidání dalšího objektu – INDEXu, ten obsahuje informace o tom, kde se jaký záznam
nachází (podle hodnoty v indexovaném sloupci).
S indexy:
- při hledání podle indexového sloupce stačí najít údaj v indexu a odtud získat ROWID (jedinečný identifikátor,
který databázový server přiřadí každému záznamu v tabulce) a podle něho najít požadovaný záznam,
- moderní dtb. servery obvykle používají stromovou strukturu indexů, například B - stromy,
27
-
průchod uspořádaným stromem je v průměru výrazně kratší než sekvenční hledání v neuspořádaném poli.
B – strom - je vyvážený strom.
Tato struktura je často používána v aplikacích, kdy není celá struktura uložena v paměti RAM, ale v nějaké sekundární
paměti, jako je pevný disk (například databáze). Protože přístup do tohoto typu paměti je náročný na čas (hlavně
vyhledání náhodné položky), snažíme se minimalizovat počet přístupů.
B-strom řádu n je takový strom, kde
• všechny listy (tj.uzly které nemají žádné potomky) jsou na stejné úrovní (ve stejné hloubce).
• kořen má nejvýše n potomků, spodní hranice není omezena.
• všechny uzly kromě kořene mají maximálně n a minimálně n/2 - 1 potomků
•
•
Pokud chceme vložit nebo smazat data (klíče) z uzlu, změní se tím počet potomků tohoto uzlu. Aby se dodržel
rozsah daný řádem stromu, vnitřní uzly se v případě potřeby rozdělují či slučují. Protože počet potomků
každého uzlu je omezený, není potřeba vyvažovat tento strom tak často jako jiné typy automaticky
vyvažovaných stromů. Jelikož strom je málokdy zcela zaplněný, musíme počítat s tím, že může docházet k
nevyužití veškeré obsazené paměti (naštěstí to většinou není překážkou).
Strom je vyvažován požadavkem aby byly všechny listy na stejné úrovni. Tato hloubka pozvolna roste s tím,
jak do stromu přidáváme další data, nebo klesá spolu s vymazáváním dat ze stromu.
Přístup pomocí adresy ROWID
- ROWID jsou fyzickými adresami dat
- Obsahuje informaci o
- Řádku vzhledem k souboru,
- Bloku,
- Řádku v bloku
- Relativní číslo souboru
- Hodnotu OBJECT_ID
- Adresa ROWID je nejrychlejší cestou k určitému řádku, k většímu množství řádků nebude však nejrychlejší
metodou
Indexy
Vlastnosti:
- zrychlení jen tehdy, pokud potřebujeme najít několik záznamů, pokud je cílem najít více než 5% záznamů,
bude obvykle rychlejší hledání bez použití indexu,
- zvýšení režie při vkládání a změně dat, protože kromě zápisu do tabulky je třeba zapsat indexový údaj na
přesně definované místo v indexu,
- nedoporučuje se používat indexy (kromě bitmapových indexů) nad malými tabulkami nebo nad sloupci s
nízkou variabilitou (např. sloupec pohlaví žena/muž),
-
-
odstraněním indexu zůstane tabulka nezměněná.
vytvoření indexu – CREATE [unique] INDEX jmeno ON tabulka (sloupec)
pokud je vytvořen index, v první řadě se prohledávají pouze ty sloupce, které jsou označeny indexy
indexy zrychlují výběr ale zpomalují vkládání
projevují se až při větším počtu dat
28
Syntaxe příkazu CREATE INDEX
Vytvoří index nad uvedeným sloupcem definované tabulky.
CREATE [BITMAP] INDEX <název indexu>
ON <název tabulky> (<název sloupce>)
BITMAP vytvoří bitmapový index - vhodné řešení pro sloupce s nízkou variabilitou. Lze si to představit tak,
že každé hodnotě ve sloupci odpovídá jeden bit kódu,tj. pro pohlaví jsou nutné 2 bity (muž, žena), pro rodinný
stav 4 bity (svobodný, ženatý, rozvedený, vdovec).
Příklad:
CREATE INDEX ix_jmeno ON ucitele(jmeno);
CREATE BITMAP INDEX ix_pohlavi ON ucitele(pohlavi);
Syntaxe příkazu DROP INDEX
Odstraní index, ale nikoli data v tabulce.
DROP INDEX <název indexu>
Příklad:
DROP INDEX ix_jmeno;
INDEXY a principy přístupových cest
Přístupové cesty jsou metodami, jejichž prostřednictvím může databáze Oracle získat data z tabulek.
Přístupové cesty:
- Úplné prohledávání
- Indexové přístupy (mnoho typů)
- Přímý přístup pomocí algoritmu hash nebo adresy ROWID
Bez indexů:
- záznamy v tabulkách ukládány neuspořádaně, nejčastěji v pořadí, v jakém byly do tabulky
vkládány
- záznamy hledáme od prvního záznamu postupně, dokud hledaný nenajdeme
- v průměru vždy prohledáme asi polovinu záznamů
- tyto problémy řeší přidání dalšího objektu – INDEXu, ten obsahuje informace o tom, kde se jaký
záznam nachází.
S indexy:
-
-
při hledání podle indexového sloupce stačí najít údaj v indexu a odtud získat rowID (jedinečný
identifikátor, který databázový server přiřadí každému záznamu v tabulce) a podle něho najít
požadovaný záznam
moderní dtb. servery obvykle používají stromovou strukturu indexů, například B- stromy
průchod uspořádaným stromem je v průměru výrazně kratší než sekvenční hledání v
neuspořádaném poli.
Vlastnosti:
- zrychlení jen tehdy, pokud potřebujeme najít několik záznamů, pokud je cílem najít více než 5%
záznamů, bude obvykle rychlejší hledání bez použití indexu
- zvýšení režie při vkládání a změně dat, protože kromě zápisu do tabulky je třeba zapsat indexový
údaj na přesně definované místo v indexu
- nedoporučuje se používat indexy (kromě bitmapových indexů) nad malými tabulkami nebo nad
sloupci s nízkou variabilitou (např. sloupec pohlaví žena/muž)
- odstraněním indexu zůstane tabulka nezměněná.
29
16 Zabezpečení a ochrana dat – uživatelské účty, systémová
a objektová oprávnění, role
Cíle, které je třeba vzít v úvahu při návrhu databázové aplikace z pohledu bezpečnosti:
• Důvěrnost (secrecy) - informace by neměly být přístupné neautorizovaným uživatelům.
• Integrita (integrity) - modifikovat data může jen autorizovaný uživatel.
• Dostupnost (availability) - autorizovaným uživatelům by nemělo být bráněno v přístupu.
– bezpečnostní politika - kdo co může s jakými daty dělat
– bezpečnostní mechanismy - zajištění bezpečnostní politiky
Bezpečnostní mechanismy
Pohledy
– umožní přístup jen k některým sloupcům tabulek
– možnost omezit přístupné řádky
– možnost skrýt skutečnou strukturu tabulek
Přístup k databázi
Pro přístup do databáze je nutné, aby uživatel měl v databázi zaregistrováno uživatelské jméno a odpovídající
heslo. Tuto registraci provádí správce databáze při registraci nového uživatele příkazem CREATE USER.
CREATE USER <user_name> IDENTIFIED BY <new_password>
Příklad
CREATE USER jnovak IDENTIFIED BY w25sqx
Příkaz vytvořil v databázi uživatele s názvem JNOVAK, který bude mít heslo "w25sqx".
Při vytvoření uživatele je možno zároveň přidělit default tablespace a temporary tablespace neboli implicitní a
dočasný tabulkový prostor.
CREATE USER <user_name>
IDENTIFIED BY <new_password>
DEFAULT TABLESPACE <def_tablespace>
TEMPORARY TABLESPACE <temp_tablespace>
Příklad
CREATE USER jnovak IDENTIFIED BY w25sqx
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
Příklad ukazuje vytvoření uživatele a přidělení tabulkových prostorů. Pokud tabulkové prostory nejsou určeny
nastaví se implicitní hodnoty.
Uživatel si může změnit heslo uvedeným příkazem. Stejně tak to za něj může provést administrátor.
ALTER USER <user_name> IDENTIFIED BY <new_password>;
Příklad
ALTER USER jnovak IDENTIFIED BY sdf45t;
Systémová přístupová práva
poskytují možnost operace na úrovni schématu uživatele nebo na úrovni celé databáze.
GRANT CREATE SESSION, CREATE TABLE TO jnovak;
Příkazem GRANT byla uživateli JNOVAK přidělena práva přístupu k databázi a vytváření tabulek.
REVOKE CREATE TABLE FROM jnovak;
Příkazem REVOKE bylo stejnému uživateli odebráno právo vytvářet tabulky. Uživatel musí mít minimálně
právo CREATE SESSION, aby se mohl přihlásit do databáze.
30
GRANT CREATE PROCEDURE TO jnovak WITH ADMIN OPTION;
Pokud bylo uživateli přiděleno právo např. CREATE PROCEDURE příkazem s klauzulí WITH ADMIN
OPTION, získává uživatel zároveň právo přidělovat právo CREATE PROCEDURE i jiným uživatelům.
SELECT * FROM user_sys_privs;
Informace ze systémového katalogu o systémových právech přidělených uživateli.
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE USER
CREATE VIEW
Přístupová práva k objektům
- poskytují uživateli možnost provádět databázové operace s určitými objekty jiného uživatele. Tyto práva
může poskytovat správce nebo vlastník objektu.
GRANT SELECT, INSERT, UPDATE, DELETE ON cisla TO jnovak;
Uživatel, který vlastní tabulku CISLA přidělil právo dotazu, vkládání, aktualizace a rušení záznamů nad touto
tabulkou uživateli JNOVAK.
SELECT * FROM pholy.cisla;
Uživatel s přidělenými právy pro dotaz nad tabulkou CISLA vlastníka(tedy schématu) PHOLY provedl dotaz v
této tabulce.
SELECT * FROM user_tab_privs;
SELECT * FROM user_tab_privs_made;
SELECT * FROM user_tab_privs_recd;
Informace ze systémového katalogu o všech, přidělených a přijatých objektových právech.
Objektová práva
Tabulky, pohledy:
ALTER
DELETE
INDEX
INSERT
UPDATE
SELECT
REFERENCES
…
Procedury, funkce:
EXECUTE
DEBUG
Vytváření rolí
Pro zjednodušení správy přístupových práv je možno použít prostředek nazvaný role.
Role je množina souvisejících práv, které může správce přidělit najednou uživatelům databáze a ostatním
rolím.
Pro vývoj aplikace jsou programátorovi nejčastěji přiděleny role CONNECT a RESOURCE.
GRANT CONNECT TO jnovak;
GRANT RESOURCE TO jnovak;
Přidělení rolí CONNECT a RESOURCE uživateli jnovak.
SELECT * FROM user_role_privs;
31
Vytvoření a přidělení rolí
CREATE ROLE sklad;
Příklad vytvoření role. Pro tento příkaz je nutno mít právo CREATE ROLE, které může přidělit správce.
GRANT SELECT, INSERT, UPDATE, DELETE ON prijem TO sklad;
GRANT SELECT, INSERT, UPDATE, DELETE ON vydej TO sklad;
Přidělení přístupových práv na tabulky PRIJEM a VYDEJ roli SKLAD.
GRANT sklad TO uživ1, uživ2, uživ3...;
Přidělení role SKLAD uživatelům.
SET ROLE sklad;
Aktivování role SKLAD a zrušení ostatních přístupových práv. Tento příkaz je možno použít např. při spuštění
aplikace.
TO PUBLIC
GRANT SELECT ON knihy TO public;
Přidělení přístupových práv na čtení tabulky knihy všem uživatelům.
17 Transakce, návratové body, automatické zamykání,
konzistentní čtení
Transakce je logická část, která obsahuje jeden nebo více příkazů SQL. Transakce je atomickou jednotkou.
Transakce je ukončena buď:
- jejím dokončením (COMMIT)
- vrácením zpět/odvoláním transakce (ROLLBACK bez uvedení názvu SAVEPOINTU)
- Uživatel ukončí spojení se serverem Oracle (transakce je potvrzena automaticky)
- Spojení uživatele se serverem se ukončí abnormálně – transakce se odroluje zpět
- explicitně, když je proveden příkaz DDL (CREATE, DROP, RENAME, ALTER), ukončí se předchozí, v
samostatné transakci se provede příkaz DDL
Transakce buď proběhne jako celek nebo se jako celek odvolá.
Cíl: zajištění konzistence dat v databázových tabulkách a nedělitelnost provedených změn, tj. princip VŠE NEBO NIC.
Příklady:
- není možné připustit provedení úpravy mzdy (inflační navýšení) jen u části zaměstnanců (než bylo
požadováno), (při vrácení transakce se mzda nikomu nenavýší)
- není možné strhnout platbu z účtu plátce a nezaúčtovat ji na účet příjemce (z důvodů např. zrušení účtu
příjemce, nesprávného účtu čísla příjemce, selhání techniky atd.).
(tj. při odvolání se peníze nestrhnou ani z účtu plátce)
Příklad bankovní transakce
Při zpracování transakce mohou nastat problémy:
• Nedostatek finančních prostředků na účtu odesílatele
• Nesprávné číslo účtu (odesílatele nebo příjemce)
• Jiné omezení na účtu (exekuce)
• HW problém
• Špatně sestavený SQL dotaz
Transakce - potvrzení
Potvrzení transakce (committing) znamená, že změny provedené transakcí se stávají trvalými.
32
Explicitní potvrzení – příkazem COMMIT
Implicitní potvrzení – po normálním ukončení nějaké aplikace nebo provedením DDL operace
Jakékoli příkazy DDL (např. create table či alter index) způsobí tedy ukončení aktivní transakce a implicitní
vytvoření nové transakce.
Změny provedené příkazy obsaženými v transakci jsou viditelné pro ostatní uživatele až od okamžiku
potvrzení transakce.
Transakce - vytvoření
Transakce je inicializována implicitně. Pokud je po dokončení transakce příkazem COMMIT následně vložen,
aktualizován či odstraněn alespoň jeden řádek, je tím implicitně vytvořena nová transakce.
Po zahájení transakce je jí přiřazen dostupný undo tablespace, kam se ukládají změny pro možnost provedení
rollbacku. Undo informace obsahují staré hodnoty dat, které byly SQL příkazem v rámci transakce změněny.
Statement-Level Rollback
Pokud během provádění SQL příkazu nastane nějaká chyba, všechny změny provedené příkazem jsou
odrolovány zpět. Toto se nazývá Statement-Level Rollback (rollback na úrovni příkazu).
Příklady takových chyb:
- Pokus o vložení řádku s duplicitní hodnotou primárního klíče
- Narušení referenční integrity
- Deadlock (pokus o současnou změnu shodných dat dvěma transakcemi)
Syntaktická chyba při parsingu neumožní spustit provádění příkazu, proto se nejedná o Statement-Level
Rollback.
Chyba při provádění příkazu nezpůsobí změny provedené předchozími příkazy v rámci dané transakce.
Použití návratových bodů:
• SAVEPOINT <bod návratu> - slouží pro označení místa
(například po provedení některých operací),
kam bychom se v případě potřeby mohli navrátit, slouží pro rozdělení velké transakce na menší části
• ROLLBACK TO <bod návratu>
– odvolání transakce, návrat do bodu návratu
18 Přístup k databázi z vyššího programovacího jazyka.
Bezpečnost a SQL injection
SQL dotazy
• SQL je jazyk pro komunikaci s relačním databázemi.
•
Existuje několik variant/verzí jazyka SQL - společnou vlastností všech verzí SQL je textová komunikace s
databází.
• Typickou jednotkou jazyka je dotaz, což je soubor příkazů pro databázi, která na dotaz odpoví množinou
výsledků.
•
Příkazy SQL mohou modifikovat strukturu databáze (Data Definition Language nebo jen DDL) nebo mohou
manipulovat s obsahem databáze (Data Manipulation Language nebo jen DML)…
SQL injection
V historii českého Internetu najdete řadu hacknutých webů díky bezpečnostní chybě v angličtině označované
jako SQL injection - jde o souhrnné označení pro nepříjemné bezpečnostní chyby, které umožní vsouvat do
SQL kódu (odtud SQL injection) internetové aplikace vlastní informace - pochopitelně takové informace,
které umožní změnit smysl původních SQL příkazů.
SQL injection napadnutelné weby najdete na internetu neustále. Řada tvůrců aplikací nevěnuje základní
pozornost těm nejobyčejnějším návykům a vypouští na veřejnost aplikace, které neměly nikdy opustit privátní
síť. Webové aplikace se málokdy podrobí testům na použitelnost, natož aby proběhl alespoň základní
bezpečnostní audit.
33
SQL injection – potenciální rizika
Možná rizika SQL injection lze vyjádřit například takto:
•
získání přístupu k datům, ke kterým přístup mít nemáte (tedy i citlivým, důvěrným, …)
•
možnost změny dat, která měla být určena jen pro čtení
•
možnost vstoupit do administračních částí internetové aplikace
•
možnost vyvolat příkazy SQL serveru, které umožní ovládnout stroj na kterém SQL server běží
•
možnost smazat nějaké tabulky nebo celou databázi
SQL injection – ochrana
SQL injection je riziková všude tam, kde autoři internetové aplikace zapomněli na základní bezpečnostní
pravidlo - veškeré vstupy do aplikace je nutné kontrolovat na povolené hodnoty/typy.
A je u plně jedno jestli jde o PHP/mySQL či ASP/MS-SQL
Potenciálně rizikové části internetové aplikace jsou všechna místa, kde něco vstupuje zvenčí.
•
Formuláře (POST/GET)
•
Parametry URI
•
HTTP/XML/SOAP komunikace (nezapomenout na Cookies)
•
"Importy" souborů
A k formulářům je vhodné dodat ještě jedno malé varování - řada webových aplikací používá "hidden" (skryté)
prvky. Tvůrci aplikace mají falešný pocit bezpečí, že tenhle prvek "není" vidět a ani "není" editovatelný.
Přitom stačí příslušnou stránku uložit jako HTML, libovolně si jí upravit a pak používat.
Jak se vyhnout SQL injection?
Existuje nespočet návodů jak se SQL Injection vyhnout - pomocí Google a zadání "SQL Injection" jich najdete
řadu - budete si tak moci i vybrat zda potřebujete návod pro Microsoft SQL, MySQL či pro "něco" jiného.
Principy jsou obdobné :
•
Textové vstupy prohnat náhradou ['] za [''] (neboli dvojici apostrofů). Jde o nejjednodušší způsob, který
znemožní použít ['] pro "ukončení" vytvářeného SQL dotazu.
• Textové vstupy prohnat odpovídající RegExp() transformací, která v něm ponechá pouze znaky, které v
něm mají být. Tímto způsobem je pochopitelně více než vhodné ošetřovat i vstupy, které se stanou později
výstupy - vyhnete se tak nebezpečí jinému, jménem XSS.
•
Netextové vstupy prohnat odpovídající typovou konverzí - tj. například pokud ?id=XXX má být integer,
tak použít odpovídající konverzí funkci - třeba int()
•
Vstupy s pevně daným malým výčtem opravdu testovat na to, zda danému výčtu odpovídají. Příkladem
může být testování na True/False, On/Off.
Uvedeným způsobem se vyhnete SQL Injection v okamžiku, kdy nechcete použít nějakou jinou metodu tvorby
SQL dotazů a zůstáváte u "operativní" klasiky sestavování SQL dotazů (způsobem naznačeným výše).
Pokud to není nutné (a popravdě, ono to ani není příliš vhodné), tak se můžete vydat dalšími cestami, které
úplně stejně zabrání SQL Injection.
Co více dělat proti SQL injection?
• více než vhodné je, aby účet pod kterým přistupuje internetová aplikace do SQL serveru by zcela určitě
neměl být účet systémového administrátora a měl by mít pouze nezbytná práva!
•
v úplně ideálním případě by měly existovat pouze uložené procedury a příslušný účet by měl mít práva
pouze pro jejich spouštění - tím se dá odstranit i případné nebezpečí z "podvrhnutých" lahůdek jako
"truncate table" či "delete * from" (popravdě i možnost vytvářet nové tabulky může být nebezpečná, stačí
je vytvářet tak dlouho a tak velké až ...).
Aplikace by neměla zobrazovat detailní informace o vyskytnuvší se chybě. Webová aplikace často nemá
potlačeno zobrazování detailních chyb a je tak možné vidět i části SQL kódu - což útočníkovi umožní snadno
se rozhodovat, jak SQL injection použít.
Co nehrozí (v MySQL ve spojení s PHP)
PHP nepovoluje vykonávání několika SQL dotazů v jednom volání funkce mysql_query(), takže tím odpadá
34
problém s řetězcem typu '; truncate tabulka; - provádí se vše, co se nachází před prvním středníkem mimo
hodnoty sloupců.
V MySQL neexistuje nic, co by mohlo zavolat externí aplikaci, jako je tomu například u Microsoftí alternativy
MSSQL, takže odpadá spousta dalších potenciálně nebezpečných dotazů.
Jak ošetřit vstupní formuláře a a z nich získaná data
Kontrola
-
použitím regulárních výrazů
- využitím konverzních funkcí
nahrazení rizikových znaků
-
kontrola na výčet hodnot
omezení délky vstupního pole formuláře
použití funkcí pro nahrazená nebezpečných znaků v textu
19 Jazyk PL/SQL, proměnné, syntaxe bloku
-
Hlavním omezením jazyka SQL je, že se jedná o neprocedurální jazyk
V praxi to znamená, že se příkazy jazyka SQL provádějí sekvenčně bez možnosti klasických programátorských
konstrukcí (cykly, podmínky, procedury, funkce, případně objektové programování)
Říkáme CO, nikoli JAK
Proto většina databázových platforem nabízí rozšíření umožňující naprogramovat i ty nejsložitější algoritmy
pro práci s daty
PL/SQL (Transaction Procesing Language)
Umožňuje deklarovat konstanty, proměnné, kurzory
Nabízí podporu dynamických deklarací
Podpora transakčního zpracování
Chybové stavy procesu je možné ošetřit pomocí výjimek
Podpora modularity (vkládání modulů i do sebe)
Podporuje dědičnost
Struktura jazyka PL/SQL
35
20 Řízení toku programu - podmínky, cykly, kurzory, záznamy,
ošetření chyb
Zápis podmínky
IF podmínka1
THEN
posloupnost_příkazů1
ELSIF podmínka2
THEN
posloupnost_příkazů2
ELSE
posloupnost_příkazů3
END IF;
Řízení toku programu
Příkaz CASE pro vícenásobné větvení programu
CASE
WHEN podmínka1 THEN posloupnost_příkazů1;
WHEN podmínka2 THEN posloupnost_příkazů2;
..
WHEN podmínkaN THEN posloupnost_příkazůN;
[ ELSE posloupnost_příkazůN+1; ]
END CASE;
Podmínka může být i například v_promenna BETWEEN 1 AND 5
Řízení toku programu - cykly
Jednoduchý cyklus LOOP
LOOP
posloupnost_příkazů
IF podmínka THEN
.. ukončuje se příkazem EXIT
END IF;
END LOOP;
nebo
LOOP
posloupnost_příkazů
EXIT WHEN podmínka;
END LOOP;
Cyklus FOR s čítačem
FOR počítadlo IN [REVERSE] Nejnižší_hodnota .. Nejvyšší_hodnota
LOOP
posloupnost_příkazů1
END LOOP;
Cyklus WHILE s podmínkou na začátku
WHILE podmínka
LOOP
posloupnost_příkazů
END LOOP;
Kurzory
Privátní pracovní oblasti, které jsou databázovým serverem vytvořeny pro každý příkaz SQL
- Implicitní kurzory jsou vytvářeny automaticky databázovým serverem, není nutné je otevírat, zavírat,
36
-
deklarovat nebo z něj načítat data,
Explicitní – deklarované programátorem
Základní kroky pro práci s explicitními kurzory
- Deklarace kurzoru
- Otevření kurzoru
- Výběr dat prostřednictvím kurzoru
- Uzavření kurzoru
Explicitní kurzory - syntaxe
- Deklarace kurzoru
CURSOR <název kurzoru> IS <příkaz SELECT>;
- Otevření kurzoru
OPEN <název kurzoru>;
- Výběr dat prostřednictvím kurzoru (opakovat v cyklu)
FETCH <název kurzoru> INTO <seznam proměnných>;
- Uzavření kurzoru
CLOSE <název kurzoru>;
Explicitní kurzory – testování stavu
Pro testování stavu kurzoru jsou k dispozici atributy
%ROWCOUNT
Zjištění pořadového čísla aktuálního záznamu
(pokud nebyl vybrán žádný, je hodnota 0)
%FOUND
Pokud poslední příkaz FETCH načetl nějaký záznam, má atribut hodnotu TRUE
Používá se pro zjišťování konce cyklu
%NOTFOUND
Používá se pro zjišťování konce cyklu
%ISOPEN
Pokud je kurzor otevřen, má hodnotu TRUE
Použití: <název kurzoru>%ROWCOUNT
Práce s explicitními kurzory
Příklad s využitím explicitního kurzoru
DECLARE
v_jmeno ucitel.jmeno%TYPE;
v_Id ucitel.Id%TYPE;
¨
CURSOR k1 IS
SELECT jmeno, Id FROM ucitel;
BEGIN
OPEN k1;
LOOP
FETCH k1 INTO v_jmeno, v_Id;
EXIT WHEN k1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Jméno ' || v_jmeno || ', Id ' || v_Id);
END LOOP;
CLOSE k1;
END;
Záznamy
Struktura typu záznam zapouzdřuje více položek i rozdílných datových typů.
Deklarace záznamu
DECLARE
TYPE <název proměnné typu záznam> IS RECORD
37
(
<název atributu> <datový typ>
[, <název atributu>
<datový typ> …]
);
Příklad
DECLARE
TYPE rec_ucitel IS RECORD
( jmeno ucitel.jmeno%TYPE;
Id
ucitel.Id%TYPE;
);
Nebo po zjednodušení jen
DECLARE
rec_ucitel
ucitel%ROWTYPE;
Práce s kurzory a záznamy
S využitím záznamů můžeme s kurzory pracovat mnohem efektivněji
Cyklus FOR s explicitním kurzorem
(kurzor v tomto případě nemusíme ani otevírat ani zavírat, dokonce ani cyklicky vybírat data pomocí příkazu
FETCH, všechny tyto úkony za nás provede server standardně)
Příklad
DECLARE
rec_ucitel
ucitel%ROWTYPE;
CURSOR k1 IS
SELECT jmeno, Id FROM ucitel;
BEGIN
FOR rec_ucitel IN k1
LOOP
DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id);
END LOOP;
END;
Práce s implicitními kurzory
a) Příkaz SELECT … INTO … FROM … musí vrátit alespoň jeden a nejvýše jeden řádek, počet sloupců musí
odpovídat počtu proměnných uvedených za klauzulí INTO včetně použitelnosti datových typů.
b) Následující příklad ukazuje využití implicitního kurzoru pro sady výsledků s omezeným počtem řádků
(řekněme méně než 100)
For x in (select … from … where …)
Loop
Process …
End loop;
BEGIN
FOR x IN (SELECT jmeno, Id FROM trpaslici)
loop
DBMS_OUTPUT.PUT_LINE('Jméno ' || x.jmeno || ', Id ' || x.Id);
END LOOP;
END;
Kurzory s parametry
Kurzor můžeme rozšířit o parametry, které budou dosazeny do dotazu až během otevření kurzoru
Deklarace explicitního kurzoru s parametrem
CURSOR <název kurzoru> [(<název parametru> <datový typ>, … )]
IS <příkaz SELECT>;
Příklad
DECLARE
38
rec_ucitel
ucitel%ROWTYPE;
CURSOR k1 (v_jmeno VARCHAR2) IS
SELECT jmeno, Id FROM ucitel WHERE jmeno LIKE (v_jmeno || '%');
BEGIN
FOR rec_ucitel IN k1 (‘Za’)
LOOP
DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id);
END LOOP;
FOR rec_ucitel IN k1 (‘Sm’)
LOOP
DBMS_OUTPUT.PUT_LINE('Jméno ' || rec_ucitel .jmeno || ', Id ' || rec_ucitel.Id);
END LOOP;
END;
Ošetření chyb
V zásadě se mohou v PL/SQL vyskytnout 2 druhy chyb:
Syntaktické – projeví se ještě v procesu kompilace (upozorní nás na ně překladač)
Logické
– projeví se až za běhu programu
Nejčastěji se vyskytují následující výjimky:
DUP_VAL_ON_INDEX - výskyt duplicitní hodnoty ve sloupci, který připouští jen jedinečné
hodnoty
INVALID_NUMBER - neplatné číslo nebo data nemohou být převedena na číslo
NO_DATA_FOUND - nebyly nalezeny žádné záznamy
TOO_MANY_ROWS - dotaz vrátil více než jeden záznam
VALUE_ERROR - problém s matematickou funkcí
ZERO_DIVIDE - dělení nulou
Všeobecná syntaxe pro zpracování výjimek:
EXCEPTION
WHEN <název výjimky> THEN <příkazy>;
[WHEN <název výjimky> THEN <příkazy>; …]
OTHERS THEN <příkazy>;
END;
Výjimku můžeme navodit nebo simulovat příkazem
RAISE <název výjimky>;
například
RAISE NO_DATA_FOUND;
Aktuální kód chyby vrací systémová funkce SQLCODE
a její textový popis systémová funkce SQLERRM,
takže při zpracování výjimky máme k dispozici tyto údaje.
Příklad
DECLARE
v_vysledek NUMBER(9,2);
BEGIN
v_vysledek := 5/0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' Chyba ');
DBMS_OUTPUT.PUT_LINE('Kód chyby:' || SQLCODE);
39
DBMS_OUTPUT.PUT_LINE('Popis chyby:' || SQLERRM);
END;
Definování vlastních výjimek
Máme možnost definovat i vlastní výjimky.
Pro vlastní výjimky je SQLCODE rovno 1 a SQLERRM vrací Text User-Defined Exception
Syntaxe
DECLARE
<název výjimky> EXCEPTION;
BEGIN
<příkazy>;
RAISE <název výjimky>;
EXCEPTION
WHEN <název výjimky> THEN <příkazy>;
END;
Příklad definice vlastní výjimky pro kontrolu počtu trpaslíků.
DECLARE
PRILIS_MNOHO_TRPASLIKU EXCEPTION;
v_pocet_trpasliku NUMBER;
BEGIN
select count(*) INTO v_pocet_trpasliku FROM trpaslici;
IF v_pocet_trpasliku > 7 THEN
RAISE PRILIS_MNOHO_TRPASLIKU;
END IF;
EXCEPTION
WHEN PRILIS_MNOHO_TRPASLIKU
THEN DBMS_OUTPUT.PUT_LINE('Trpaslíků může být maximálně sedm');
END;
21 Procedury a funkce, balíčky
Procedury
Procedura je posloupnost příkazů, které se provedou v okamžiku spuštění procedury.
Na základě vstupních parametrů jsou vráceny výsledky v podobě výstupních parametrů.
Syntaxe
CREATE [OR REPLACE] PROCEDURE <název procedury> [(<seznam parametrů>)] AS
Příklad definice procedury
CREATE [OR REPLACE] PROCEDURE zvyseni_mzdy (procento IN NUMBER) AS
BEGIN
UPDATE pracovnici SET mzda = mzda * (1+procento/100);
END;
Příklad spuštění procedury
EXECUTE zvyseni_mzdy(6);
nebo
EXEC zvyseni_mzdy(6);
nebo
BEGIN
zvyseni_mzdy(6);
END;
Funkce
Funkce na rozdíl od procedur dokáží vrátit nějakou hodnotu,
která je ve většině případů vypočítána v těle funkce.
Syntaxe
CREATE [OR REPLACE] FUNCTION <název funkce> [(<seznam parametrů>)]
RETURN <datový typ výsledku>
40
Příklad
CREATE [OR REPLACE] FUNCTION pocet_smen (Id_trp IN NUMBER)
RETURN NUMBER
AS
v_pocet NUMBER;
BEGIN
SELECT count(*) INTO v_pocet FROM tezby
WHERE Id_trpaslika=Id_trp AND skutecnost>0;
RETURN v_pocet ;
END;
Použití funkce
SELECT Jmeno, pocet_smen(Id) Pocet_smen FROM trpaslici;
Používání balíčků
Výhody balíčků
- Zvětšují obor názvů – může být použit stejný název procedury v různých balíčcích
- V jednom balíčku může být mnoho procedur, ale v datovém slovníku bude existovat pouze jeden
objekt – balíček, namísto jednoho objektu slovníku pro každou proceduru nebo funkci bez použití
balíčků
- Podporují zapouzdření, části kódu (podřízené rutiny), které nemají využití mimo balíček, jsou ukryty
v balíčku a mimo něj nejsou viditelné a jsem jediným, kdo je může zobrazit
- Podporují proměnné uchovávané po celou dobu relace - můžete mít proměnné, které si udrží své
hodnoty mezi jednotlivými voláními v databázi
- Podporují spouštěcí kód – tj. úsek kódu, který se provede při prvním odkazu na balíček v relaci, tj.
umožňuje automatické provedení složitého inicializačního kódu
- Umožňují seskupení souvisejících funkcí
Balíčky
Balíček má 2 části
- specifikaci balíčku (interface k aplikacím)
- tělo balíčku
Ve specifikaci jsou deklarovány typy, proměnné, konstanty, výjimky, kurzory a podprogramy pro použití.
Tělo úplně definuje kurzory a subprogramy – implementační detaily a privátní deklarace, které jsou neviditelné
z aplikace. Je možné změnit tělo balíčku bez změny specifikace a tím vlastně neovlivnit vazbu na další
aplikace. Programy volající balíček nemusí být rekompilovány při změně těla balíčku (tzv. balíčky přerušují
řetězec závislostí).
Struktura balíčků
Balíčky - syntaxe
CREATE PACKAGE name AS -- specification (visible part)
-- public type and item declarations
41
-- subprogram specifications
END [name];
CREATE PACKAGE BODY name AS -- body (hidden part)
-- private type and item declarations
-- subprogram bodies
[BEGIN
-- initialization statements]
END [name];
Veřejné a privátní elementy balíčků
Balíčky - odkazování
Referencing Package Contents
package_name.type_name
package_name.item_name
package_name.subprogram_name
22 Triggery pro DML příkazy nad tabulkami, Triggery pro
databázové a klientské události
Triggery
Trigger je uložená procedura, která se spouští za přesně definovaných událostí.
Můžeme tedy říci, že trigger je množina příkazů, které se automaticky provedou
v případě předem definované operace (INSERT, DELETE, UPDATE) s daty
(a to buď před či po vlastní operaci).
Použití:
- kontrola zadaných dat,
- zajištění referenční integrity.
Procedury, triggery, funkce jsou uložené přímo v databázi spolu s dalšími databázovými strukturami jako jsou
tabulky, pohledy, sekvence, indexy, …
To znamená, že do databáze se umísťují nejenom data, ale i aplikační logika pro jejich zpracování.To umožňuje
jednodušší distribuci, přispívá ke spolehlivosti. Procedury jsou uloženy v databázi v předkompilované podobě.
42
Použití:
- kontrola zadaných dat
- zajištění komplexní referenční integrity v databázi
- automatické generování odvozené hodnoty hodnot sloupců
- zamezení invalidním transakcím
- zajištění komplexní bezpečnostní autorizace
- implementace business pravidel
- zajištění logování událostí
- poskytování auditů
- zajištění synchronní replikace tabulek
- generování statistik přístupu k tabulkám
- modifikace dat v tabulce, když DML příkaz používá pohled
- publikace informací o událostech a příkazech do jiných aplikací
Zajištění integrity
Triggery by se měly využít pouze v případě, kdy:
není možné použít následujících integritních omezení:
- NOT NULL, UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DELETE CASCADE
- DELETE SET NULL
tabulky nejsou v jedné databázi atd.
Triggery – DML
Syntaxe
CREATE [OR REPLACE] TRIGGER <název triggeru>
{ BEFORE | AFTER } [INSTEAD OF]
{ INSERT | UPDATE | DELETE } ON <název tabulky>
[FOR EACH ROW [WHEN <podmínka provedení triggeru> ]]
BEGIN
… výkonná sekce
………………………….
END;
Musíme určit:
- akci, která trigger aktivuje,
- kdy se trigger aktivuje - před či po,
- zda se trigger spouští pro každý zpracovávaný řádek FOR EACH ROW nebo jen jednou,
- případně, zda trigger nahrazuje operaci, která jej spustila INSTEAD OF (pouze pro pohledy)
- více spouštěcích příkazů lze oddělit klazulí OR (např. INSERT OR DELETE)
43
BEGIN
:new.Uppername := UPPER(:new.Ename);
END;
Triggery – poznámky k použití
• Přílišné využití triggerů může způsobit nepřehlednost aplikace (jejich přehlížení)
• Nepoužívejte rekursivní triggery
• Pozor při použití kaskádních triggerů
• Nepoužívejte triggery tam, kde si můžete pomoci například omezeními (CHECK na vkládané hodnoty).
• Nedělejte kód triggerů delší než 32KB. Pokud by měl být delší, použijte procedur a funkcí.
• Není možné použít příkazy COMMIT, ROLLBACK nebo SAVEPOINT v těle triggeru. Protože DDL
příkazy způsobují implicitní COMMIT, nemohou být také proto použity v triggerech, s výjimkou příkazů
CREATE, ALTER, DROP TABLE a ALTER...COMPILE pro systémové triggery.
23 Export a import dat, práce s textovými soubory a XML
dokumenty
Import a export dat
Import a export dat používáme zejména pro výměnu dat s jinými aplikacemi.
Mezi často používané formáty patří:
1. SQL - textové soubory s jednotlivými SQL příkazy (skripty)
2. Textové soubory, např.
- s oddělovači sloupců a řádků (např. CSV Comma-separated values)
- s pevnou šířkou sloupců
3. XML soubory
4. Soubory tabulkových procesorů (např. Excel – XLS)
SQL developer nabízí export dat do souborů
Export dat z SQL Developeru
Můžeme exportovat:
• data z tabulky,
• výsledky dotazu nebo
• výstup reportu
buď
• do souboru nebo
• do schránky Windows.
Při exportu je možné definovat
• omezující podmínky či
• vybrat sloupce
Formát INSERT (SQL), tabulka Trpaslici
-- INSERTING into TRPASLICI
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (1,'Stistko',110,1980);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (2,'Kychal',115,1983);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (3,'Profa',120,1999);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (4,'Rypal',112,2001);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (5,'Brucoun',109,1976);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (6,'Stydlin',117,1984);
Insert into TRPASLICI (ID,JMENO,VYSKA,NAROZEN) values (7,'Smudla',108,1993);
Formát TEXT, tabulka Trpaslici
"ID"
"JMENO"
"1"
"Stistko"
"VYSKA"
"110"
"NAROZEN"
"1980"
44
"2"
"3"
"4"
"5"
"6"
"7"
"Kychal"
"Profa"
"Rypal"
"Brucoun„
"Stydlin"
"Smudla"
"115"
"120"
"112"
"109"
"1983"
"1999"
"2001"
"1976"
"117"
"108"
"1984"
"1993"
Formát CSV, tabulka Trpaslici
"ID","JMENO","VYSKA","NAROZEN"
"1","Stistko","110","1980"
"2","Kychal","115","1983"
"3","Profa","120","1999"
"4","Rypal","112","2001"
"5","Brucoun","109","1976"
"6","Stydlin","117","1984"
"7","Smudla","108","1993"
Formát XML, tabulka Trpaslici
<?xml version='1.0' encoding='null' ?>
<results>
<row>
<ID><![CDATA[1]]></ID>
<JMENO><![CDATA[Stistko]]></JMENO>
<VYSKA><![CDATA[110]]></VYSKA>
<NAROZEN><![CDATA[1980]]></NAROZEN>
</row>
<row>
<ID><![CDATA[2]]></ID>
<JMENO><![CDATA[Kychal]]></JMENO>
<VYSKA><![CDATA[115]]></VYSKA>
<NAROZEN><![CDATA[1983]]></NAROZEN>
</row>
</results>
Formát XLS, tabulka Trpaslici
Formát LOADER, tabulka Trpaslici
LOAD DATA
INFILE *
Truncate
INTO TABLE "TRPASLICI"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ID, JMENO, VYSKA, NAROZEN)
begindata
"1","Stistko","110","1980"
"2","Kychal","115","1983"
45
"3","Profa","120","1999"
"4","Rypal","112","2001"
"5","Brucoun","109","1976"
"6","Stydlin","117","1984"
"7","Smudla","108","1993"
Formát HTML, tabulka Trpaslici
<html>
<body>
<table>
<th>ID</th>
<th>JMENO</th>
<th>VYSKA</th>
<th>NAROZEN</th>
<tr>
<td>1</td>
<td>Stistko</td>
<td>110</td>
<td>1980</td>
</tr>
<tr>
<td>2</td>
<td>Kychal</td>
<td>115</td>
<td>1983</td>
</tr>
</table>
</body>
</html>
Import a export DDL
SQL Developer nabízí export SQL příkazů pro vytváření databázových objektů, v rámci jednoho typu objektů
je možné označit více objektů (tabulek, procedur, …) a uložit DDL příkazy, které se vztahují k vytváření těchto
objektů.
Kompletní export schématu v SQL Developeru
Volba Tools – Export DDL (and Data)
Umožňuje uložit DDL příkazy všech (nebo vybraných) databázových objektů v rámci schématu včetně
uložených dat (pokud nebude nastaveno filtrování)
Výsledný soubor je typu sql (text s SQL příkazy).
24 Relační algebra. Závislosti
Relační algebra
•
nejzákladnější prostředek pro práci s relacemi
• Operace: projekce, selekce, spojeni(JOIN), kartézský součin (to je cross join, ne?)
Projekce relace R s atributy A na množinu atributů B:
•
•
vytvoření relace schématu B, které vznikly odstraněním hodnot A-B
odstranění eventualních duplicitních záznamů
46
•
značení : R[B]
Selekce relace R s atributy A podle logické podmínky fi:
vytvoření relace s týmž schématem a ponechání prvků z původní relace splňující podmínku
• podmínka = logický výraz ve tvaru t1 (=|>|<|>=|<=) t2, kde t1, t2 jsou atributy, vyrazy,
konstanty
• znaceni: R(fi)
•
Spojení relací R a S se schématy A a B:
vytvoří minimální relaci se schématem AČB a s prvky, jejichž projekce na A je z relace
R a projekce na B je z relace S.
•
přirozené spojení R*S (spojení přes rovnost R[a = b]S) - výsledná relace obsahuje ty
záznamy, které se shodují v položkách, nad kterými se relace provádí.
• levé polospojení, pravé polospojení
• použití polospojení tam, kde není zaručena plná integrita dat
•
•
•
•
Operace projekce vybírá sloupce
Operace selekce vybírá řádky
Operace spojení spojuje 2 tabulky přes vybrané položky
přejmenování atributů
•
značení: t1->alias
Dotazovací jazyk, který umožňuje realizovat všechny operace relační algebry, se nazývá relačně úplný.
47
Download

Databázové systémy - Dokumenty Google – práce s textem