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 (
, ) Desetinné číslo s celkovým počtem číslic a s počtem za desetinnou čárkou. Stejný typ je NUMERIC. - FLOAT ()
6
Desetinné číslo s plovoucí desetinnou tečkou. Parametr udává počet platných číslic (max. 38) - REAL () 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 () Řetězec znaků pevné délky znaků. Maximální délka bývá 255 znaků. - VARCHAR () Řetězec znaků proměnlivé délky, maximálně 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 (, ) Desetinné číslo s celkovým počtem číslic a s počtem čí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 () Řetězec znaků pevné délky znaků. Maximální délka 2000 znaků, default je 1. - VARCHAR2 () Řetězec znaků proměnlivé délky, maximálně 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 () Struktura obsahující datum a čas s požadovanou přesností (až na miliontiny sekundy pro hodnotu parametru =6). TIMESTAMP WITH TIME ZONE Struktura jako TIMESTAMP doplněná o časový posun oproti UTC
-
INTERVAL YEAR () TO MONTH K vyjádření delšího časového intervalu. - INTERVAL DAY () TO SECOND () 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} ] [ORDER BY <seznam kritérií třídění>] <seznam výstupních sloupců> =[ALL|DISTINCT|DISTINCTROW] { * | <specifikace sloupce1> [, <specifikace sloupce2> [, …]] } <specifikace sloupce> = { | < specifikace tabulky > . | < specifikace tabulky > . * | výraz} [[AS] <pojmenování sloupce> ] < specifikace tabulky > = { | <pojmenování tabulky> | } [<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} ] [ORDER BY <seznam kritérií třídění>] <seznam kritérií třídění>= [ [, …]] = { | <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 [( [, [, … ] ]) ] { | VALUES [( [, [, … ] ]) ] } 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 [<pojmenování tabulky>] SET = [, = [, … ] ] [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 [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
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 [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: . NEXTVAL Vygeneruje a vrátí další hodnotu sekvence . CURRVAL Vrátí aktuální hodnotu sekvence (ta musí být předtím aspoň jednou inicializována použitím . 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 ON () 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 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 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 DEFAULT TABLESPACE <def_tablespace> TEMPORARY 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 ; 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 - 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 – 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 IS ; - Otevření kurzoru OPEN ; - Výběr dat prostřednictvím kurzoru (opakovat v cyklu) FETCH INTO <seznam proměnných>; - Uzavření kurzoru CLOSE ; 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í: %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 IS RECORD
37
(
[, …]
); 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 [( , … )] IS ; 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 THEN ; [WHEN THEN ; …] OTHERS THEN ; END; Výjimku můžeme navodit nebo simulovat příkazem RAISE ; 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 EXCEPTION; BEGIN ; RAISE ; EXCEPTION WHEN THEN ; 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 [(<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 [(<seznam parametrů>)] RETURN
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 { BEFORE | AFTER } [INSTEAD OF] { INSERT | UPDATE | DELETE } ON [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 <JMENO>
<JMENO>
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 | ID | JMENO | VYSKA | NAROZEN | | 1 | Stistko | 110 | 1980 |
| 2 | Kychal | 115 | 1983 |
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
