Databázové systémy I přednášky

Databázové systémy I přednášky

Vostrovský Václav [email protected] PEF - přízemí č. 15 Po 12:00 - 13:30 - 75% docházky na cvičeních - musíme chodit připraveni - 2 testy v průběhu cvičení (teorie a SQL) - max 10 bodů, minimum 5 bodů, pro zápočet z obou minimálně 10 bodů - 1 oprava (např. pokud 2. test: 3 body a oprava 7 bodů - celkem z 2. testu: (3 + 7)/2 = 5 bodů) - skripta: Vytváření databází v Oracle - zkouška: písemná část + ústní dozkoušení (SQL dotazy a základní teoretické termíny) - dvě přednášky v lednu jsou předtermín 1. cvičení: seznámení s náležitostmi udílení zápočtu MS ACCESS: založení a naplnění tabulky, třídění, dotazy 2. cvičení: MS ACCESS: složitější dotazy, obrazovka, sestava 3. cvičení: ORACLE - SQL * PLUS založení a naplnění tabulky INDEX, DELETE, DROP TABLE, UPDATE 4. cvičení: ORACLE - SQL*PLUS třídění, dotazy (selekce, projekce, agregované údaje), práce s více tabulkami 5. cvičení: ORACLE - SQL*PLUS složitější dotazy (výběry.... 13. cvičení: test 14. cvičení: oprava testu Přednášená témata: - základní pojmy db technologie - relační datový model - dotazovací jazyk SQL - modelování reálného světa (ER) - datová normalizace - SŘBD Oracle - ochrana dat Aspekty pro vymezení přístupů ke zpracování dat: - stupeň nezávislosti programů od způsobu jejich uložení - míra vzájemné integrovanosti organizace dat Členění dosavadních přístupů ke zpracování dat: - konvenční přístupy: - agendové zpracování dat - integrované zpracování dat - databázové přístupy: - relačně databázové zpracování dat - objektové zpracování dat Agendové zpracování - představuje první pokus řešit problematiku zpracování hromadných dat: - vedení samostatných, vzájemně oddělených a rozsahem malých úloh - agend - každá agenda má vlastní soubory, které nejsou propojeny - míra integrovanosti velmi nízká Redundance dat: narůstající objem dat vícenásobné zpracování dat Nevýhody agendového zpracování: - obtížná uživatelská flexibilita - pro každou novou uživatelskou funkci je třeba přepsat dotyčný program nebo nový napsat - každý program obsahuje přesný popis souborů - problém konzistence redundantních dat - pokud se změní vlastnost objektu, musí být přepsána ve všech výskytech - obtížná flexibilita datové základy - pro každou změnu struktur záznamu nutno přeprogramovat související programy - obtížná kontrolovatelná datová redundance - omezené možnosti sdílení stejných dat více aplikacemi

-1-

Christy


- nízká bezpečnost IS - obtížná dosažitelnost evidovaných dat - ke zpracovávaným souborům musí existovat odpovídající aplikační programy - obtížná ochrana dat proti zneužití - komplikované zajištění integrity dat - zpracovávaná data musí odpovídat vlastnostem popisovaných objektů reálného světa - Nutnost uplatnění jiné filozofie ve zpracování dat Požadavky kladené na zpracování hromadných dat - zpracování ekonomických agend se týká úloh: - výběry pomocí určitého algoritmu, jehož základem je dotaz uživatele - aktualizace evidovaných dat - záznamy ze vstupního souboru jsou modifikovány záznamy ze změnového souboru Princip aktualizace ekonomických agend kmenový soubor + pohybový soubor -> aktualizace -> nový kmenový soubor Princip databázového zpracování aplikační program 1, 2, 3, 4 -> datový zdroj Předpoklady databázových dotazovacích jazyků - musí obsahovat příkazy pro definici nových dat - jazyk pro definici dat DDL (data definicion language) - musí obsahovat příkazy pro vkládání nových vět, tvorbu dotazů a aktualizaci dat - jazyk pro manipulaci dat DML (data manipulation language) - musí obsahovat příkazy pro řízení přístupových práv uživatelů a pro řízení transakcí - jazyk DCL (data control language) Moderní SŘBD disponují prostředky vizuálního programování - příkazy jsou konstruovány (generovány) na základě zvolených grafických symbolů v uživatelském rozhraní Členění dotazovacích jazyků: - procedurální jazyky - nutno zadat algoritmus pro získání požadované odpovědi - COBOL, FORTRAN - neprocedurální jazyky - v principu jednodušší, pouze se specifikují podmínky pro požadovanou odpověď 1. jazyk SQL (Struktured Query Language) kopíruje princip kladení otázek v přirozeném jazyce 2. jazyk QBE (Query By Example) pro zadávání dotazů pomocí grafických symbolů zapisovaných do návrhových formulářů Relačně úplný dotazovací jazyk: = umožňuje definovat všechny operace relační algebry

Databázové dotazovací jazyky: Dotazovací jazyk SQL - 70. léta laboratoře IBM - deklarativní dotazovací jazyk založený na n-ticovém relačním kalkulu - deklarativní - příkazy definují „co“ se má provést a nikoliv „jak“ se to má provést - dotazovací - jazyk specializovaný pro manipulaci s daty v databázi pomocí rozhraní SŘBD (nelze ale např. programovat uživatelské rozhraní - nutno kombinovat s jiným vyšším programovacím jazykem) Zlomem ve vývoji SQL se stala jeho standardizace organizací ANSI

Syntaxe příkazů SQL Symbol [] {} | <> ... ‘

Význam nepovinné části říkazu povinná volba jedné z uvedených možností oddělení variant, mezi kterými je možné volit parametr, za který je třeba dosadit konkrétní hodnotu konstrukce uvedená v předchozí závorce se může opakovat označení konstant typu řetězec znaků (‘Josef‘)

-2-

Christy


uživatelské jméno: student1 až 9 heslo: student přihl. řetězec: ORA9 quit - odhlášení

Relační datový model - vznik: 1969, E. F. Codd, laboratoře IBM, DB2 - aspekty relačního datového modelu: - hodnoty v tabulkách musí být atomické - neměla by být dále dělitelná - hodnoty musí být skalární (pouze jeden rozměr) - hodnoty ve sloupcích jsou prvky dotyčných domén - datový typ vymezuje jaká data budou vkládána - práce s tabulkami využívá operací výrokové logiky - výběry, projekce, spojování tabulek - primární klíče pro identifikaci řádek mezi sebou - pro propojování tabulek navzájem - cizí klíče Zavádění IS do organizace - instituce - v cizině je správce databáze v managementu, je více respektován - u nás - není respektován, musí se obhajovat, vysvětlovat Křivka „humbuku“ - zájem, popularita, očekávání od nových aplikací - ze začátku byl obrovský, pak zjistili, že pokud se dostatečně nepřizpůsobili, tak se to nevyplatilo Relační datový model - v tabulkách lze definovat podmnožiny řádek (operace selekce) a podmnožiny sloupců (projekce) - pro práci s více tabulkami - operace spojení Základní pojmy relační databázové teorie: - datové soubory chápány jako množiny - dvourozměrné tabulky = RELACE - jednotlivé řádky = n-tice sledované problematiky - jména sloupců = atributy (vlastnosti) - obor přípustných hodnot v sloupcích - doména - každá věta (record) = souhrn všech údajů o jednom objektu dané problémové domény - sloupce tabulky reprezentují jednotlivé vlastnosti (atributy) objektů - všechny záznamy v rámci 1 tabulky - stejná struktura - záznam - množina položek (pole - fields) = množina vlastnosti sledovaných objektů doprava - zvířata - zaměstnanci - zásoby - ZP - účetnictví - mzdy => vše sdílené databázemi DBS Podmínky relačnosti tabulky - všechny hodnoty v tabulce musí být elementární (nedělitelné) - pozice sloupců je nevýznamná (pořadí lze měnit) - data se neztrácí - pozice řádků je nevýznamná (pořadí lze měnit) - sloupce musí být homogenní (obor hodnot tohoto sloupce musí být stejný) - každý sloupce musí být jednoznačně pojmenován - každý sloupec musí být jednoznačně rozlišen (primární, sekundární klíč) Relační algebra - operace PROJEKCE - výběr požadovaných sloupců, výsledkem je relace o p-sloupcích vzniká z původní o nsloupcích), p < n - operace SELEKCE - výběr požadovaných řádků, výsledkem je relace o r-řádcích vzniklá z původní o m-řádcích, r < m - operace spojení JOIN (na rovnost, nerovnost, vnější) - spojením dvou relací vzniká třetí, která obsahuje všechny kombinace vyhovující zadané podmínce Coddova pravidla pro relační model 1. pravidlo SŘDB - data spravována pouze pomocí relačních operací

-3-

Christy


2. pravidlo informační - data reprezentována na logické úrovni jako hodnoty relačních tabulkách 3. pravidlo přístupu - každý údaj logicky dosažitelný pomocí kombinace názvu tabulky, sloupce a hodnoty primárního klíče 4. pravidlo zpracovatelnosti neznámých hodnot - ke každé neznámé hodnotě lze dojít prostřednictvím jiným známých hodnot 5. pravidlo relačního katalogu - popis celé databáze je na logické úrovni reprezentována jako relační systémový katalog 6. pravidlo pro jazyk - pro komunikaci se SŘDB - definici dat (DDL) - integritní omezení (DCL) - manipulaci s daty (DML) 7. pravidlo pohledů - SŘDB musí umožňovat konstrukci pohledů 8. pravidlo operací - všechny relační operace pracují s tabulkami jako s celky 9. pravidlo fyzické a logické nezávislosti dat 10. pravidlo nezávislosti dat na integritních omezeních - výsledky operací musí být nezávislé na změnách IO

Dotazovací jazyk SQL - příkazy pro definici dat - definice tabulky v SQL - CREATE TABLE <jméno-tabulky> - ALTER TABLE - DROP TABLE - help STARTUP - nápověda Vytvoření relace STUDENT student(cind, jmeno, bydliste, datnar, stip) CREATE TABLE student 2 (cind CHAR(6) NOT NULL, 3 jmeno VARCHAR(12), 4 bydliste VARCHAR(10), 5 datnar DATE, 6 stip NUMBER(4)); Tabulka vytvořena INSERT INTO student 2 VALUES (‘111/99’,’Karel Novák’,’Praha !’,’10.1.1980’,3500); 1 řádka vytvořena výpis obsahu relace STUDENT SELECT * FROM student; STUDENT - pouze studenty 3 ročníku -> STUDENT3 INSERT INTO student3 2 SELECT * FROM student 3 WHERE rocnik=’3’; 2 řádek vytvořeno SELECT * FROM student3 Doplnění položky ROCNIK do relace STUDENT ALTER TABLE student ADD rocnik CHAR(1); Tabulka změněna

-4-

Christy


Výpis struktury tabulky DESCRIBE student; Modifikování položky BYDLIŠTĚ v relaci STUDENT ALTER TABLE student MODIFY bydliste VARCHAR2(15); Tabulka změněna DESCRIBE student; Oprava dat v položce STIPENDIUM v relaci STUDENT UPDATE student SET stip=1000 2 WHERE cind=’222/01’; 1 řádka aktualizována SELECT * FROM student; Oprava dat v položce STIPENDIUM v relaci STUDENT UPDATE student SET stip=stip*2 2 WHERE ROCNIK>=’#’; 2 řádek aktualizováno SELECT * FROM student; Přejmenování relace STUDENT na STUDENTI RENAME student TO studenti; Tabulka prejmenována SELECT * FROM studenti; Zrušení relace STUDENT - budou zrušeny i případné indexové soubory k dané relaci a budou nepoužitelné všechny pohledy a uložené dotazy týkající se této tabulky!!! DROP TABLE student; Tabulka zrušena SELECT * FROM student; Přejmenování sloupce STIP na STIPENDIUM ALTER TABLE student ADD stipendium NUMBER(4); Tabulka změněna DESC student; - nyní je třeba zkopírovat hodnoty z původního sloupce do nového sloupce za všechny řádky UPDATE student SET stipendium=stip; 4 řádek aktualizováno SELECT * FROM student; - jako poslední krok je nutno zrušit původní sloupec, tj. STIP ALTER TABLE student DROP COLUMN stip; Tabulka změněna SELECT * FROM student; Zrušení záznamu v relaci STUDENT DELETE FROM student WHERE cind=’444/02’; 1 řádka vymazána SELECT * FROM student;

-5-

Christy


Výhody požívání jazyk SQL - snížení ceny na zaškolení pracovníků - tvůrci a uživatelé aplikací mohou snadněji přecházet od jednoho databázového prostředku k jinému - přenositelnost vytvořené aplikace - aplikace vytvořená v konkrétním databázovém prostředku může být provozovaná v jiném prostředku beze změny, což umožňuje odladit danou aplikaci na PC a výsledek pak přenést na vyšší kategorii počítačů - délka života aplikace se zvyšuje - v případě nutnosti je možno přejít na jiný databázový prostředek a prodloužit tak životnost dané aplikace - společný přístup k datům v heterogenním prostředí - distribuované databáze provozované pod různým SŘDB Příkazy jazyka pro manipulaci dat DML - SELECT - základem dotazovacího jazyk SQL je příkaz SELECT Význam jednotlivých klauzulí příkazu SELECT GROUP BY - obsahuje definice výrazů, podle kterých budou řádky seskupeny HAVING - omezuje souhrnné řádky definované pomocí GROUP BY, která splňují zadanou podmínku ORDER BY - umožňuje setřídění řádků ve výsledku dle zadaných kritérií - klíčové slovo DISTINCT odstraňuje duplicitní řádky výběru PROJEKCE - výběr sloupců SELECT jmeno, bydliste FROM student; Modifikovaný text v záhlaví vybraných sloupců SELECT jmeno AS “Jméno studenta” FROM student; - problém činí délka vybíraného sloupce, která současně vymezuje délku nového (výstižnějšího) ALIAS pojmenování Výpis modifikovaných (agregovaných) údajů SELECT jmeno, bydliste, stipendium*1.2 2 AS “Stipendium v SK“ FROM student; SELEKCE - výběr řádků (specifikováním podmínky) SELECT * FROM student 2 WHERE stipendium>2000; SELEKCE - odstranění duplicitních řádků SELECT bydliste FROM student; SELECT DISTINCT bydliste FROM student; SELEKCE - predikáty ve výběrové podmínce SELECT * FROM student WHERE stipendium 2 BETWEEN 5000 AND 7000; Výběry na podkladě testu náležitosti do množiny hodnot SELEKCT jmeno, bydliste FROM student 2 WHERE bydliste IN 3 (‘Praha 1’,’Praha 3’,’Praha 9’); Výběry na podkladě částečné specifikace textu % (procento) - specifikuje žádný, jeden nebo několik libovolných znaků (obdoba hvězdičky v MS-DOS) _ (podtržítko) - vyjdřuje právě jeden libovolný znak (obdoba otazníku v MS-DOS) SELECT jmeno FROM student 2 WHERE jmeno LIKE ‘K%’; Výběry na podkladě částečné specifikace textu SELECT jmeno FROM student 2 WHERE jmeno LIKE ‘%N%’ OR jmeno LIKE ‘%D%’; Výběr s relačním operáteorem pro nerovnost (!=) SELECT * FROM student WHERE ročnik!=’3’;

-6-

Christy


Výběry na podkladě hodnoty datumu SELECT * FROM student; SELECT * FROM student WHERE (sysdate-datnar)>7300; - každé smysluplné datum si přepočítává na pořadová čísla od začátku n. l. Třídění výstupů SELECT * FROM student ORDER BY bydliste ASC,rocnik DESC; Práce s více tabulkami - v tomto případě jde o operaci relační algebry - spojení (tj. spojení těch záznamů, které spolu „korespontují“) - pokud vybíráme stejnojmenné sloupce z těchto tabulek, je nutno použít úplnou identifikaci položek v tzv. „tečkové notaci“, tj. <jméno-tabulky>.<jméno-sloupce>. SELECT * FROM prospech; cind datumzk cpred

znamka

student

prospech

spojení

jmeno, cpred, datumzk, znamka SELECT jmeno AS “Jméno studenta “, 2 datumzk AS “Datum zkoušky”, 3 cpred AD “Číslo předmětu”, 4 znamka 5 FROM student, prospech 6 WHERE student.cind=prospech.cind; STUDENT: cind jmeno 111/99 Karel Novák 222/01 Jiří Dvořák 333/01 Karel Franěk 444/02 Josef Havlát PROSPECH: cind datumzk 111/99 222/01 444/02 výsledek: jméno suden Karel Novák Jiří Dvořák Josef Havlát

bydliste Praha 1 Praha 9 Kolín 13 Praha 1 cpred

datum zk

datnar 10.01.80 24.07.84

znam

číslo předm

znamka

Postup zpracování jednoduchého dotazu SELECT - relační databázový stroj ORACLE vyhodnocuje dotaz SELECT v následujícím pořadí: 1. zpracuje klauzuli FROM, tj. výběr požadované zdrojové tabulky či tabulek; 2. zpracuje klauzuli WHERE, tj. jsou procházeny jednotivé řádky (záznamy) jeden za druhým a vybírány pouze ty, které splňují výběrovou podmínku 3. realizuje klauzuli SELECT, tj. výběr požadovaných sloupců z tabulky, je-li uvedena klauzule DISTINCT, budou odstraněny duplicitní řádky 4. byl-li spoecifikován požadavek setřídění výběru, bude vykonána klauzule ORDER BY Práce s více tabulkami SELECT * FROM prospech; SELECT * FROM predmety; SELECT jmeno,datumzk,prospech.cpred,nazev,znamka 2 FROM student,prospech,predmety 3 WHERE student.cind=prospech.cind AND prospech.cpred=predmety.cpred;

-7-

Christy


SELECT jmeno,datumzk,prospech,cpred,nazev,katedra,znamka 2 FROM student,prospech,predmety 3 WHERE student.cind=prospech.cind 4 AND prospech.cpred=premety.cpred 5 AND katedra=’KII’; Práce se skupinami příkazů - přehled nejpoužívanějších funkcí: COUNT(*) - počet řádků ve skupině COUNT(<jmeno-sloupce>) - počet hodnot ve sloupci - počítají se pouze platné hodnoty (nikoliv NULL) SUM(<jméno-sloupce>) - součet hodnot v specifikovaném numerickém poli MIN(<jméno-sloupce>) - nejmenší hodnota pole ve sloupci MAX(<jméno-sloupce>) - největší hodnota ve sloupci AVG(<jméno-sloupce>) - průměr. hodnota polí v čísel. sloupci Zjištění četnosti v rámci stanoveného kritéria SELECT COUNT(*) AS “Počet studentů z Prahy 1” 2 FROM student 3 WHERE bydliste=’Praha 1’; SELECT * FROM prospech; SELECT COUNT(DISTINCT cind) FROM prospech; SELECT * FROM student; SELECT rocnik,COUNT(*) FROM student 2 GROUP BY rocnik; Zjištění maximálních hodnot SELECT * FROM student; SELECT MAX(stip) FROM student; Zjištění prům. stipendií za jednotlivé ročníky SELECT rocnik, AVG(stip) FROM student GROUP BY rocnik; SELECT rocnik, AVG(stip) FROM STUDENT WHERE stip>0 GROUP BY rocnik; Zjištění jména studenta s maximálním stipendiem SELECT * FROM student; CHYBNĚ: SELECT jmeno, max(stip) FROM student; SELECT jmeno,stip FROM student 2 WHERE stip=(SELECT max(stip) FROM student); PROČ? 1. 2. zjištění jména pro stip=max(stip) max(stip) Zjištění jména studenta, který je nejstarší SELECT * FROM student; SELECT jmeno,datnar FROM student 2 WHERE datnar=(SELECT min(datnar) FROM student); Pohledy (view) - k definování externí úrovně popisu dat slouží v jazyce SQL tzv. pohledy (view) - pohled je tabulka, která není fyzicky uložená v databáz, ale vzniká vždy při použití daného pohledu jako výsledek určitého příkazu SELECT - pohledy se používají pro zpřístupnění jen určité - podle toho, z kolika tabulek pohledy vznikají, lze je rozdělit na: - jednoduché pohledy - jsou vytvořeny z údajů jedné tabulky a neobsahují žádné funkce či skupiny dat - komplextní pohledy - jsou vytvořeny z údajů více tabulek a mohou obsahovat funkce a skupiny dat definice pohledu:

-8-

Christy


CREATE VIEW <jméno pohledu> [<seznam nových jmen sloupců>] AS zrušení pohledu: DROP VIEW <jméno_pohledu> Vytvoření pohledu STIPENDISTE CREATE VIEW stipendiste 2 (Cindex,Jmeno_stud,Vyse_stipendia) AS 3 SELECT cind,jmeno,stip 4 FROM student 5 WHERE stip>0; SELECT * FROM stipendiste; UPDATE student SET stip=stip/2 2 WHERE cind=‘111/99‘; SELECT * FROM stipendiste; Zrušení pohledu STIPENDISTE DROP VIEW stipendiste: Používání SQL přináší tyto výhody: - snížení ceny na zaškolení pracovníků - vůrci a uživatelé aplikací mohou snadněji přecházet od jednoho databázového prostředku k jinému - přenositelnost vytvořené aplikace - aplikace vytvořená v konkrétním databázovém prostředku může být provozovaná v jiném prostředku beze změny, což umožňuje odladit danou aplikaci na PC a výsledek pak přenést na vyšší kategorii počítačů - délka života aplikace se zvyšuje - v případě nutnosti je možno přejít na jiný databázový prostředek a prodloužit tak životnost dané aplikace - společný přístup k datům v heterogenním prostředí - distribuované databáze provozované pod různým SŘDB Zjištění integrity dat - relační databázový stroj musí obsahovat mechanismy k zabezpečení báze dat pře případným úmyslným i neúmyslným poškozením a před zneužitím dat - takováto ochrana báze dat je realizována jako zajištění integrity báze dat. Při realizaci databázového systému je nutno zachytit všechna pravidla, pomocí kterých databázový stroj zajistí správnost a věrohodnost uložených dat = tzv. datovou integritu - tato pravidla vymezující nezbytnou korektnost uložených dat a rozhodující o proveditelnosti aktualizačních operací = tzv. integritní omezení (integrity constraints) Zajištění integrity dat - v klasických databázových systémech byla tato pravidla součástí databázových aplikací. Nevýhodné pro client-server architektury databázových systémů Důvody: - opakované programování integritních algoritmů = sít nadbytečně zatěžující komunikace mezi aplikací a serverem při jejich provádění Závěr: - integrita databáze musí být převážně garantována prostředky databázového server, tj. vlastní realizace pravidel garantujících Druhy integritních omezení Entitní integrita: tj. dodržení jednoznačné identifikace každého řádku dotyčné relační tabulky. Každá relační tabulka proto musí mít určen primární klíč (primary key) jako minimální množinu atributů, jejichž hodnoty společně identifikují každý řádek této tabulky - zabezpečení entitní integrity databáze znamená nepřipustit uložení žádného řádku, ve kterém je hodnota položky představující klíč či některou z jeho komponent nenaplněná nebo vzhledem k řádkům již uloženým duplicitní Realizace entitní integrity CERATE TABLE prednasejici

-9-

Christy


2 3 4

(evcped CHAR(3) NOT NULL PRIMARY KEY, jmeno CHAR(12) datnar DATE);

INSERT INTO prednasejici VALUES(‚19e‘,‘Karel Novák‘,’25.3.1951‘); INSERT INTO prednasejici VALUES(‚23E‘,‘Josef Malík‘,’13.4.1963‘); INSERT INTO prednasejici VALUES(‚19E‘,‘Martin Kalaš‘,’13.4.1963‘); Doménová integrita Doména = množina všech přípustných hodnot určitého daného atributu (sloupce) relační tabulky - omezení doménové integrity je realizováno pravidlem definujícím tyto platné hodnoty, přičemž doména není totéž co datový typ daného atributu. K zachování takovéto doménové integrity databáze je nutné zabezpečit, aby každá hodnota obsažená v databázi byla pouze z množiny hodnot pro daný sloupec přípustných. Přípustnost hodnot atributu může být dokonce dána hodnotami jiného atributu i z jiných tabulek databáze Zajištění doménové integrity CERATE TABLE prednasejici 2 (evcped CHAR(3) NOT NULL PRIMARY KEY, 3 jmeno CHAR(12), 4 datnar DATE, 5 plat number(5) CHECK (plat BETWEEN 7000 AND 50000)); INSERT INTO prednasejici VALUES(‚19e‘,‘Karel Novák‘,’25.3.1951‘,7000); INSERT INTO prednasejici VALUES(‚19e‘,‘Josef Malík‘,’25.3.1951‘,55000); Referenční integrita - referenční integrita databáze garantuje korektnost vztahů mezi logicky souvisejícími tabulkami. Vazby mezi logicky nadřízenými a podřízenými záznamy v databázi jsou vytvářeny doplněním jednoznačné identifikace řádků logicky nadřízené tabulky do logicky podřízené tabulky pomocí tzv. cizího klíče (foreign key) - cizím klíčem v dokumentujícím příkladu je položka CPED v tabulce PREDMETY. Doplnění primárního klíče tabulky přednášejících do tabulky předmětů je realizací vztahu vyjadřujícího, který učitel garantuje jaké předměty Realizace referenční integrity CREATE TABLE predmety 2 (cpred CHAR(5), 3 nazev CHAR(15), 4 katedra CHAR(3), 5 evcped CHAR(3), 6 FOREIGN KEY (evcped) REFERENCES prednasejici(evcped)); INSERT INTO predmety VALUES (‚E1210‘,‘Expertní systémy‘ Realizace integritních omezení - deklarativní realizace - je explicitně definována („deklarováno“) jako součást databázového schématu a představuje způsob specifikace IO jako přímé součásti definice struktury databáze, tj. rozšíření příkazu definujícího struturu relační tabulky o klauzule umožňující deklaraci jednotlivých omezení - procedurální realizace - je založena na využití databázových procedur, které jsou realizovány prostřednictvím definic speciálních procedur, tzv. database triggers (spouští), které se vyvolávají („spouštějí“) při každém vkládání, aktualizaci nebo odstanění záznamu - akce nutné k udržení integrity databáze jsou v tomo 1. zakážu takovýto předmět nabízet 2. kaskádovitě dovedu změny z jedné tabulky do druhé - změny promítnu do podřízených tabulek 3. pomocí null hodnot (číslo garantujícího pedagoga budou samé nuly)

Datové modelování Coddova pravidla pro relační model 11. Pravidlo nezávislosti dat na distribuci - výsledek operací nesmí být ovlivněny konkrétním umístěním dat v distribuovaných databázích

- 10 -

Christy


12. Pravidlo nenarušitelnosti SŘDB - žádný uživatel nesmí obcházet nebo narušovat rozhraní SŘDB Fyzická nezávislost dat - aplikace musí být izolovány od změn fyzické datové struktury, změna aplikace nesmí způsobit nutnou změnu paměového uložení struktur a opačně Logická nezávislost dat - aplikační pohled je izolován od změn ve schématu databáze (změna struktury nevynucuje změnu programu) Třístupnová architektura báze dat 3 úrovně abstrakce při pohledu na evidovaná data: - fyzická úroveň - nejnižší úroveň, popisuje jak jsou data fyzicky uloženy, pracuje s ní programátor SŘDB - konceptuální úroveň - popisuje strukturu dat DB a jejich vzájemné vztahy, tzv. schéma - modeluje reálný svět a vytváří jeho interní model pomocí logické a fyzické struktury dat, pracuje s ní tvůrce DB, správce DB - uživatelská úroveň - popisuje pro konkrétního uživatele pouze tu část DB, na kterou má pravomoc, tzv. subschéma, jejich počet = počet uživatelů Datový model - souhrn pravidel pro reprezentaci logické organizace dat v databázi Datové modelování Vícestupňová architektura DBS: - úroveň: externí schéma - popisuje datové struktury pro koncového uživatele (pohledy) = podmnožina konceptuálního schéma) - úroveň: konceptuální schéma - popisuje jednotlivé datové struktury a jejich vzájemné vazby - model - úroveň: interní schéma - vlstní popis implementace datových sturkur v implementačním prostředí Konceptuální datové modelování (Chenův E-R model): - konceptuální datový model - struktura dat obecně bez ohledu na DBS - logický datový model - konkretizuje model na daný DBS - datový model v definičním jazyku - výsledný pro rutinní zpracování Principy E-E modelování: - orientace na objekty - pracujeme s objekty RIDIC, VOZIDLO nikoliv s identifikacemi - funkcionální podstata vztahů - vztahy mezi objekty jsou definovány jako funkce, např. MA_PRIDELEN - ISA-hierarchie - pro práci s nadtypy a podtypy - hierarchický mechanismus pro konstrukci objektů Úrovně objektů v E-R modelování STUDENT

Entitní množina

STUDENT

atribut

Cislo_indexu

Jmeno Rocnik Datum_nar

ABSOLVOVAL

vztahová množina

Způsoby konstruování datového modelu - "zdola nahoru" - východiskem je univerzální rleace (U) obsahující všechny atribut řešené problémové domény, následuje specifikování funkčních závislostí mezi jednotlivými atributy. Např. U(Cislo_indexu, Jmeno, Rocnik, Datum_nar, Znamka, Katedra, Cislo_ped, Jmeno_ped, Datum_zkousky, Cislo_predmetu, Nazev_predmetu, Fakulta...) Cislo_indexu -> Jmeno, Rocnik, Datum_nar Cislo_predmetu -> Nazev_predmetu Cislo_indexu, Cislo_predmetu -> Datum_zkousky, Znamka, Cislo_pedagoga

- 11 -

Christy


Cislo_ped -> Jmeno_ped - tento postup přímo přechází na logickou úroveň -> pouze pro vytváření modelů s max. 25 atributy - "zhora dolu" - nejprve jsou specifikovány struktury entitních množin a následně funkční závislosti mezi nimi Jenotlivé kroky modelování "shora dolů": 1. krok: Specifikace entitních množin - východiskem je verbální popis STUDENT PEDAGOG

PŘEDMĚT KATEDRA

- specifikovaným objektům (entitním množinám) se přiděli vhodné jméno 2. krok: Specifikace vztahů - důležitá kardinalita vztahu 1:1 1:N M:N Povinnost vztahu: přerušovaná spojnice - nepovinnost vztahu

ABSOLVUJE

STUDENT

PEDAGOG

GARANTUJE

PŘEDMĚT

JE_ČLENEM

KATEDRA

3. krok: přiřazení primárních klíčů Cislo_ind ABSOLVUJE

STUDENT Cislo_ped

PEDAGOG

Cislo_predmetu GARANTUJE

PŘEDMĚT Cislo_katedry

JE_ČLENEM

KATEDRA

4. krok: Transformace modelu do logické struktury - konceptuální datový model je rozpracován do logické struktury "předběžných" relací. Tyto relace ovlivněy kardinalitou vztahů:

- 12 -

Christy


- vztah 1:1 - je-li účast obou entitních množin ve vztahu povinná, postačí pro realizaci pouze jedna relace, jinak NULL hodnoty v primárních klíčích - vztah 1:N - nejčastější, řešení minimálně 2 relacemi - vztah M:N 5. krok: Doplnění zbývajících atributů do předběžných relací Cislo_ind

Datum_zkoušky

Rocnik Jméno_studenta

Známka

ABSOLVUJE

STUDENT Cislo_ped Jméno_ped


PEDAGOG

PŘEDMĚT Katedra

JE_ČLENEM Od

Název_predm

Cislo_katedry

Fakulta

KATEDRA

Do

6. krok: Prověření modelu z hlediska normalizace - strukturální správnost konzistence datového modelu CÍLE DATOVÉ NORMALIZACE: - umožnění reprezentace každé relace v DB - optimalizace algoritmů vyhledávání - zjednodušení aktualizace a rušení vět = postupný reverzibilní proces nahrazování dané množiny relací relacemi, které mají jednodušší strukturu Vlastnosti datové normalizace: - umožňuje ověření správnosti navrženého modelu - slouží k dekompozičnímu návrhu tabulek s minimální redundancí dat - není deterministická (více správných řešení) - lze ji kombinovat s ostatními technikami (dekompozice a syntéza) Způsoby konstruování datového modelu Chybný návrh: Evidence prospěchu Jméno žáka Datum Zkoušení J. Dvořák 10.9,15.10,29.10

Známka 1,3,3

Chybný návrh: Jméno_žáka Datum_Zk1 Známka1 .. .. Datum_Zk n Známka n J. Dvořák 10.9. 1 .. .. 29.10 3 Způsoby konstruování datového modelu - datová normalizace 1 NF

2 NF

3 NF

Přínosy datové normalizace: - zabránění vzniku duplicitní dat (redundancí) - šetří kapacitu média - zjednodušuje aktualizaci a vyhledávání dat Dekomponované relace možno opět funkčně zvolit (operace JOIN)

- 13 -

Christy


1. NF: relace nesmí obsahovat násobná data Prospěch (Cislo_stud, Jmeno, Adresa, Datum_zkousky, Znamka) 0001A Dvořák Kolín 22 10.9.02 4 0002A Kalaš Čáslav 5 10.9.02 1 0001A Dvořák Kolín 22 17.9.02 2 0002A Kalaš Čáslav 5 21.9.02 2 0001A Dvořák Kolín 22 1.10.02 2 Po normalizaci: STUDENTI

Cislo_stud

Jmeno

Adresa

ZNÁMKY

Cislo_stud

Datum_zkousky

Známka

2. NF: Všechna neklíčová data relace musí funkčně záviset na celém primárním klíči

Cislo_stud Cpredm

Datum_zkousky

Znamka

Název_predm Pedagog

Po normalizaci: ZKOUSKY

Cislo_stud

C_predm Datum_zkousky Znamka

PREDMETY

C_predm Nazev_predm

Pedagog

Funkční závislost: Datový prvek B záznamu je funkčně závislý -> ke každé hodnotě A náleží nejvýše jedna hodnota B > A identifikuje B Spojení tabulky sama se sebeou SELECT a.jmeno, b.jmeno 2 FROM student a, student b 3 WHERE a.jmeno < b.jmeno Vnější spojení tabulek - vnější spojování tabulek se používá i pro výpis, záznamů, které nestpňují spojovací kriterium, symbolem operátora je v tomto případě (+) - operátor se umístí na tu stranu příslušné tabulky, kde mohou potencionálně chybět informace SELECT student.cind, jmeno, dat.stzkousky, znamka 2 FROM student, statnice 3 WHERE student.cind = statnice.cind (+); SELECT student.cind, jmeno FROM student 2 WHERE student.cind NOT IN 3 (SELECT statnice.cind FROM statnice); Způsoby konstruování datového modelu - datová normalizace 3. NF - všechna neklíčová data musí záviset pouze na klíčových donotách a nikoliv mezi sebou Příklad:

Cislo_pedagoga

Jméno_pedagoga Datnar Cislo_kvalifikace Nazve_kvalifikace

Pedagog

Cislo_pedagoga

Jméno_pedagoga

Kvalifikace

Cislo_kvalifikace

Nazev_kvalifikace

Datnar

- 14 -

Christy


7. krok: přiřazení domén jednotlivým atributům - v rámci tohoto kroku je třeba stanovit charakteristiky jednotlivých atributů - tj. domén: - datový typ - délka - rozsah - přípustné hodnoty - formát (maska) - jedinečnost (primární klíč) - přípustnost "NULL" hodnoty Datový typ: CHAR a VARCHAR2 - datové typy CHAR a VARCHAR2 - znakové - mohou obsahovat data skládající se z písmen, číslic a jiných znaků CHAR (délka) - pro uložení řeězce o pevné délce, paramter délka je povinný <1;2000> VARCHAR2 (délka) - pro uložení řetězce proměnné délky, paramter délka je povinný <1;4000> Příklad: Město = Kolín 1

2

3

4

5

MĚSTO CHAR(10)

K

o

l

í

n

MĚSTO VARCHAR2(10)

K

o

l

í

n

6

7

8

9 10

8. krok: stanovení pravidel pro rušení, vkládání a modifikování dat - jde o upřesnění vazeb mezi jednotlivými atributy navrženého modelu (především podmínky) 9. krok: analýza budoucího vývoje modelu - domény - posouzení předpokládaných změn domén, primárních klíčů kardinality vztahu, integritních omezení atd. závěr: nutno koordinovat týmovou práci, vyloučit intuitivní postupy - důsledné dodržení postupu tvorby logického modelu minimalizuje výskyt nenormalizovaných relací a s tím spojených problémů 5. krok: Doplnění zbývajících atributů do předběžných relací Cislo_ind

Datum_zkoušky Rocnik Jméno_studenta Cislo-ind

Známka

ABSOLVUJE



PEDAGOG

PŘEDMĚT Katedra

JE_ČLENEM Od

Název_predm

Do

Cislo_katedry

Fakulta

KATEDRA FAKULTA

- 15 -

Christy


Datum_zkoušky Cislo_ind

Rocnik Jméno_studenta

Známka ABSOLVUJE



PEDAGOG

PŘEDMĚT Katedra

JE_ČLENEM Od

Název_predm

Cislo_katedry

Fakulta

KATEDRA

Do

- přidám Cislo_ped k PŘEDMĚT Stuktury výsledných tabulek: PEDAGOG (Cislo-ped, Jméno_ped) Koncepce SŘDB Oracle - databázová technologie představuje unifikovaný soubor pojmů, prostředků a technik pro vytváření informačních systémů - hlavní nástroj SŘDB Oracle je relační db stroj - zajišťuje správu všech dat uložených v databázi - relační db stroj pracuje na počítači řízeném hostitelským OS, v rámci počítačové sítě se označuje jako hostitelský uzel Struktura databáze: - soubory s kódem programu - hostitelské db soubory - protokolační soubory - pro obnovení databáze po havárii - řídící soubory - malé binární soubory spjaté s danou databází s důležitými informacemi (např. informace o mezivýsledcích sejmutých v tzv. bodech návratu) Vnitřní členění databáze: - logickou strukturu databáze popisuje systémový katalog, tvz. slovník dat Databáze se člení na: - datový segment - segment pro dočasné objekty - indexový segment - rollback segment - pro každou probíhající transakci se v pracovní oblasti těchto segmentů vede deník transakcí Segmenty se člení na oblasti skládající se z datových bloků s následující strukturou: - záhlaví a adresář záznamů nacházející se v daném bloku - volný prostor a prostor obsazený záznamy Pohledy do slovníku dat: - běžnému uživateli nejsou k dispozici tabulky se systémovými informacemi, ale pouze pohledy na ně. K tomu lze použít standardní příkazy: Např: Výpis struktury všech objektů, které vlastní uživatel DESC USER OBJECTS Např: Výpis struktury všech objektů všech uživatelů: DESC ALL OBJECTS

- 16 -

Christy


Např. Výpis struktury tabulky STUDENT: DESC ALL OBJECTS Datové typy ORACLE: - CHAR(n) - VARCHAR(a) - NUMBER(p,s) - DATE - (DD-MON-YY např. 10-JAN-03) - LONG - řetězec znaků proměnné délky 1-2 GB - RAW(ln) - binární data o proměnné délce týkající konkrétního záznamu tabulky - LONGRAW - shodný význam jako prodchozí, délka až 2 GB - ROWID - binární údaj (pseudosloupec je součástí tabulky) obsahuje informace o daném DB souboru, o bloku v rámci tohoto souboru a o záznamu v rámci tohoto SQL*PLUS: - prostředek pro interaktivní nebo dávkovou komunikaci koncového uživatele s db strojem ORACLE Příkaz lze zadávat: - v základním režimu - ukončovat středníkem, je ukládán do vyrovnávací paměti - tzv. příkazový buffer - řídící příkaz - pro řízení SQL*PLUS a formátování odpovědí na dotazy nejčastější: INPUT - převádí dialog do režimu vkládání skupiny řádků do bufferu - bloky PL/SQL - celý blok se ukládá do příkazového bufferu a režim vkládání se ukončuje tečkou nebo prázdným řádkem Řízení dialogu uživatele s SQL*PLUS: SET PAGESIZE 24 SET PAUSE ON - odezvy budou členěny po obrazovkách s 24 stránkami a zobrazování bude zastavovat po obrazovkách a čekat na stisk libovolné klávesy CLEAR BUFFER INPUT 1 SELECT * FROM prednasejic;i 2 RUN 1 SELECT * FROM prednasejic; SQL*PLUS: měření doby provádění příkazu TIMING START SELECT * FROM prednasejici; TIMING STOP SQL*PLUS: editace obsahu příkazového řádku LIST - vypíše obsah bufferu, přičemž aktuální řádek je předznačen hvězdičkou LIST CLEAR BUFFER - vymaže veškerý obsah příkazového bufferu CLEAR BUFFER LIST DEL - vymaže aktuální řádek LIST DEL LIST RUN CHANGE /starý/nový - v aktuálním řádku bude nalezen první výskyt "starého" zankového řetězce a nahrazen "novým" znakovým řetezcem LIST CHANGE /Praha 1/Praha 9 RUN

- 17 -

Christy


INPUT textový řetězec - vloží za aktuální řádek nový řádek SELECT * FROM student; LIST INPUT WHERE rocnik='3' RUN / - odešle obsah příkazového bufferu ke zpracování LIST / SAVE jméno souboru - uloží obsah bufferu do textového souboru zadaného jména. pokud na disku stejnojmenný soubor již existuje, lze jej přepsat atributem REPALACE příkazu SAVE CLEAR BUFFER INPUT 1 SELECT * FROM predmety 2 SAVE vypis REPLACE START vypis SQL*PLUS: Formátování výstupů - způsob podtržení - změna tvaru podtržení pomocí požadovaného znaku lze realizovat příkazem SET UNDERLINE znak SET UNDERLINE = SELECT * FROM student; SQL*PLUS: Tvar výstupních sestav TTITLE CENTER 'Vypis studentů' BTITLE CENTER 'Určeno pro proděkany fakulty' SELECT * FROM student; - platnost příkazů TTITLE a BTITLE je nutno ukončit příkazem: TTITLE OFF nebo BTTITLE OFF. V opačném případě se jejich funkčnost přenáší do dalších akcí SQL*PLUS: Formátování sloupců SHOW parametr - příkaz pro zobrazení aktuální hodnoty systémové proměnné: SHOW BTITLE SELECT * FROM predmety; CLEAR BUFFER INPUT 1 COLUMN nazev HEADING "Název předmětu" 2 COLUMN katedra HEADING "Garantující katedra" 3 SELECT nazev, katedra FROM predmety 4 SAVE vypis1 START vpis1 SQL*PLUS: editace příkazového souboru START vypis LIST GET vypis LIST 1 CHANGE /zamestnance/pracovnika/ SAVE vypis REPLACE START vypis SQL*PLUS: formátování výstupů CLEAR BUFFER INPUT 1 COLUMN jmeno HEADING 'Jméno přednášeícího' FORMAT A20 2 COLUMN plat HEADING 'Výše platu' FORMAT 999999,99 3 TTITLE 'Výpis platů přednášejících||****************************** 4 SELECT jmeno, plat FROM prednasejici

- 18 -

Christy


5 SAVE vypisplatu REPLACE START vypisplatu CLEAR COLUMNS; TTITLE OFF; SELECT * FROM prednasejici; SQL*PLUS: JOIN relace sama na sebe SELECT * FROM prednasejici; Příklad: Vypište všechny možné dvojice přednášejících pro možný zástup v případě absence SELECT a.jmeno AS "1. přednášejicí", b.jmeno AS "2. přednášející" 2 FROM prednasejici a, prednasejici b 3 WHERE a.jmeno>b.jemno;

SQL*PLUS: nejčastější chyby v testu 1) Vnořený příkaz: SELECT * FROM student; CHYBNĚ: SELECT jmeno,min(stip) FROM student; 1) Jméno studenta s nejnižším stipendiem SELECT jmeno,stip FROM student 1 WHERE stip=(SELECT min(stip) FROM student); PROČ: 2. 1. zjištění jména pro stip=min(stip) min(stip) 2) UPDATE UPDATE student SET stip=stip/2 1 WHERE stip>5000; CHYBNĚ: UPDATE student SET stip/2 1 WHERE stip>5000; 3) Pohled ze dvou tabulek PEDAGOG(Cped,Jmeno,Datnar,Katedra) PREDMET(Kodpred,Nazev,Cped) CREATE VIEW vypis 1 (Jmeno,Datnar,Nazev) AS 2 SELECT jmeno,datnar,nazev 3 FROM pedagog,predmet 4 WHERE predagog.cped=předmět.cped; SELECT * FROM vypis; CHYBNĚ: CREATE VIEW vypis 1 (Jmeno,Datnar,Nazev) AS 2 SELECT jmeno,datnar 3 FROM pedagog,predmet 4 WHERE pedagog.cped=předmět.cped; 4) Počty předmětů garantované jednotlivými pedagogy, kteří garantují více jak 3 předměty PEDAGOG(Cped,Jmeno,Datnar,Katedra) PREDMET(Kodpred,Nazev,Cped) SELECT cped,COUNT(*) FROM predmet 1 GROUP BY cped HAVING count(*)>3; CHYBNĚ: SELECT cped,COUNT(*) FROM predmet 1 GROUP BY cped WHERE count(*)>3; SELECT cped,COUNT(*) FROM predmet 1 GROUP BY jmeno WHERE

- 19 -

Christy


5) Zajištění referenční integrity STUDENT(Cind,Jmeno,obor,datumvolby,koddp) DIPLPRACE(Koddp,Nazev) CREATE TABLE student 1 (cind CHAR(4) PRIMARY KEY, 2 jmeno CHAR(15), 3 obor CHAR(3), 4 datumvolby DATE, 5 koddp CHAR(3), 6 FOREIGN KEY (koddp) REFERENCES diplprace(koddp)); CHYBNĚ: CREATE TABLE student 1 (cind CHAR(4) PRIMARY KEY, 2 jmeno CHAR(15), 3 obor CHAR(3), 4 datumvolby DATE, 5 FOREIGN KEY (koddp) REFERENCES diplprace(koddp)); také chybějící primární klíč SQL*PLUS: příkazové dávky Příkazové soubory a dávkové zpracování příkazový buffer může obsahovat pouze jeden (poslední příkaz) vhodnější: příkazový soubor, lze jej založit: - v základním režimu zadat konkrétní SQL příkaz a pak uložit obsah bufferu příkazem SAVE - v základním režimu zadat příkaz INPUT (přepnutí do režimu vkládání příkazů) a ukončit vkládání lze odesláním prázdného řádku Neopomenout: - vyprázdnit buffer (CLEAR BUFFER) - překontrolovat momentální stav SET - platnost TTITLE a BTITLE ukončit - stav SHOW parametr (TTITLE OFF nebo BTITLE OFF) SQL*PLUS: návrat k uložené příkazové dávce GET vypisplatu možno editovat (CHANGE) možno startovat SQL*PLUS: parametrická dávka - sada příkazů pro opakované použití CLEAR BUFFER INPUT 1 DEFINE titul1='PŘEHLED PŘEDNÁŠEJÍCÍCH ZA JEDNOTLIVÉ KATEDRY' 2 DEFINE titul2='======================================' 3 TTITLE LEFT titul1 RIGHT 'Strana: ' SQL.PNO SKIP1 LEFT titul2 4 SET UNDERLINE * 5 COLUMN jmeno HEADING 'Jméno predasejiciho' FORMAT A20 6 COLUMN datnar HEADING 'Datum narozeni' FORMAT A15 7 COLUMN katedra HEADING 'Název katedry' FORMAT A15 8 PROMPT Zadej název požadované katedry 9 ACCEPT pozadkat CHAR 10 SELECT jmeno, datnar, katedra FROM prednasejici 11 WHERE katedra=&pozadkat 12 SAVE vypiskated REPLACE - spuštění parametrické dávky s parametrem KII START vypiskated 'KII' - spuštění parametrické dávky s parametrem KIT START vypiskated 'KIT'

- 20 -

Christy


SQL*PLUS: CREATING REPORTS - BREAK ON BREAK ON column; BREAK ON PAGE; BREAK ON REPORT; BREAK ON ..... SKIP a; BREAK ON ..... PAGE; SQL*PLUS: CREATING REPORTS - SKIP n SELECT * FROM prednasejici; BREAK ON katedra SKIP2; SELECT * FROM prednasejici ORDER BY katedra; SQL*PLUS: CREATING REPORTS - COMPUTE BREAK ON REPORT; COMPUTE COUNT OF evcped ON REPORT; SELECT * FROM prednasejici; BREAK ON katedra SKIP2; COMPUTE SUM OF plat ON katedra; SELECT katedra,jmeno,plat FROM prednasejici ORDER BY katedra; CLEAR COMPUTE; CLEAR BREAK; SQL*PLUS: CREATING REPORTS - COL n TTITLE COL 10 'VYPIS INTERNICH PEDAGOGU' SELECT * FROM prednasejici; SQL*PLUS: CREATING REPORTS - PAGESIZE SET PAGESIZE 30 SET LINESIZE 30 INPUT 1 SET UNDERLINE = 2 CLEAR COMPUTE 3 CLEAR COLUMN 4 CLEAR BREAK 5 TTITLE COL 5 'VÝPIS PŘEDNÁŠEJÍCÍCH ZA JEDNOTLIVÉ KATEDRY' SKIP1 COL 5 '*************' 6 COLUMN jmeno HEADING 'Jméno přednášejícího' FORMAT A21 7 COLUMN datnar HEADING 'Datum narození' FORMAT A14 8 COLUMN plat HEADING 'Výše platu' 9 COLUMN katedra HEADING 'Katedra' FORMAT A7 10 BREAK ON katedra SKIP 2 11 COMPUTE AVG OF plat ON katedra 12 SELECT katedra,jmeno,datnar,plat 13 FROM prednasejici 14 ORDER BY katedra 15 SAVE vypis REPLACE SQL*PLUS: CREATING REPORTS - BREAK ON start vypis

Uplatnění substitučních parametrů - výpis studentů na podkladě obsluhou zadaného jména a adresy: INPUT 2 SELECT * FROM student 3 WHERE jmeno='&1' AND adresa='&2' 4 SAVE TEST

- 21 -

Christy


SET VERIFY OFF START TEST Novák Praha 1 "Spoolování" výsledku do textového souboru Spool TEMP Clear Columns Clear Breaks Clear Computers Column CIND Heading 'CISLO INDEXU' format A14 Column JMENO Heading 'BYDLISTE' format A20 Break On ADRESA Skip 1 On Report Compute COUNT of CIND ON ADRESA Compute COUNT of CIND On Report Set PageSize 30 Set LineSize 50 Ttitle Left 'INFORMACE O STUDENTECH' right 'Strana: ' Format 99 SQL.PNO Skip 2 Btitle CENTER 'Urceno pouze pro studijní oddeleni' Select CIND,JMENO,ADRESA From STUDENT Order By ADRESA Spool Out - program bude uložen pod názvem TEST.SQL a lze jej odstartovat příkazem START TEST. Požadovaný výpis studentů lze nalézt (tisknout a prohlížet libovolným editorem) v souboru TEMP.LST. Tisk hodnoty sloupce v záhlaví VÝPISU TTITLE LEFT 'Bydliste: ' Bydl SKIP 2; BREAK ON adresa SKIP PAGE; SELECT adresa,jmeno,cind FROM student ORDER BY adresa; COLUMN adresa NEW_VALUE Bydl; - Bydl představuje paměťovou proměnnou naplňovanou hodnotami z položky ADRESA příkazem COLUMN ADRESA NEW_VALUE Bydl; - výpis bude stránkovat podle položky ADRESA, přičemž v záhlaví výpisu se objeví konkrétní hodnota této položky Výpočet dílších a celkových agregací Příkazy: BREAK ON adresa; COMPUTE COUNT OF cind ON adresa; SELECT adresa,jmeno,cind FROM student ORDER BY adresa; - za každou skupinu studentů se stejnou adresou se zobrazí počet studentů se shodným bydlištěm. Platnost příkazu COMPUTE zrušíte odesláním příkazu: CLEAR COMPUTES; Počet studentů za celý soubor: BREAK ON REPORT; COMPUTE COUNT OF cind ON REPORT; SELECT adresa,jmeno,cind FROM student ORDER BY adresa; Výhody používání SQL - snížení ceny na zaškolení pracovníků - tvůrci a uživatelé aplikací mohou snadněji přecházet od jednoho SŘDB k jinému - přenositelnost vytvořené aplikace - aplikace vytvořená v konkrétním SŘDB může být provozovaná v jiném prostředku beze změny - vhodné odladit danou aplikaci na personálním počítači a výsledek pak přenést na jinou kategorii počítačů - délka života aplikace se zvyšuje - v případě nutnosti je možno přejít na jiný databázový prostředek a prodloužit tak životnost dané aplikace - společný přístup k datům v heterogenním prostředí - distribuované databáze provozované pod různým SŘDB Nedostatky - nevýhody SQL - normalizací ANSI dodržují jen někteří producenti - problematická konstrukce výrazu SELECT FROM WHERE projekce (selekce (kartézský součin) relace1 ... relacen)je nevhodná, lépe: selekce (projekce(relace) - nelze definovat vlastní funkce - problematická existence WHERE a HAVING

- 22 -

Christy


SELECT * FROM student 2 WHERE stipendium>2000; SELECT rocnik,COUNT(*) FROM student 2 GROUP BY rocnik HAVING rocnik<'3'; - SQL slouží pro komunikaci se SŘDB, neobsahuje prostředky běžného programování - nelogická duplicita konstruktů: CREATE TABLE prednasejici 2 (evcped CHAR(3) NOT NULL PRIMARY KEY, 3 jmeno CHAR(12), 4 datnar DATE, 5 plat number(5) CHECK (plat BETWEEN 7000 AND 50000)); CREAT TABLE predmety 2 (cpred CHAR(5), 3 nazev CHAR(15), 4 katedra CHAR(3); 5 evcped CHAR(3); 6 FOREIGN KEY (evcped) ... Dotazovací jazyky - SQL Systémové informace - informace o struktuře databáze (definované tabulky, pohledy, indexy atd.) jsou uloženy stejným způsobem jako vlastní data, tj. ve formě tabulky. Takovéto uložení je výhodné, protože informace o databázi je možno běžně vybírat pomocí příkazu SELECT... Příklad: výpis jména objektu, vlastníka a typu objektu SELECT tname,creator,tablety FROM catalog; Oprávnění přístupu k datům - nejlépe řešit na základě identifikace uživatele, kterého postupně povyšujeme v jeho pravomocích - autor tabulky má automaticky všechna oprávnění pro všechny operace s ní GRANT {ALL|<seznam-oprávnění>} ON <jméno-tabulky> TO {PUBLIC |<seznam-uživatelů>} dílčí oprávnění: SELECT - povoluje pouze čtení dat z tabulky INSERT - povoluje vkládání dat do tabulky UPDATE - povoluje oprava dat v tabulce DELETE - povoluje rušení dat z tabulky ALL - povoluje veškerá oprávnění Příklad: GRANT SELECT, UPDATE ON student TO referent1; - zpětné odnětí přidělených práv - příkaz REVOKE REVOKE {ALL|<seznam-oprávnění>} ON <jméno-tabulky> FROM {PUBLIC|<seznam-uživatelů>} Příklad: REVOKE UPDATE ON student TO refernt1; - přidělování oprávnění uživatelským skupinám - tzv. role - nutno vytvořit katalog rolí a jim přidělit potřebná oprávnění Příklad: - založení role PRODEKAN se všemi právy pro tabulku STUDENT a přidělit ji Prof.Prokešovi CREATE ROLE prodekan; GRANT ALL ON student TO prodekan; GRANT prodekan TO Prokes; Transakční zpracování - transakční zpracování zajišťuje konzistence dat v existujících tabulkách Možnosti: Příkazy ROLLBACK COMMIT SAVEPOINT COMMIT - Potvrzení platnosti transakce COMMIT

- 23 -

Christy


ROLLBACK - příkaz odvolávající všechny nepotvrzené změny, přičemž odvolat transakci může pouze ten uživatel, který operaci provedl SELECT * FROM exemplar; UPDATE exemplar SET cena=cena*0.5; SELECT * FROM exemplar; ROLLBACK; SELECT * FROM exemplar; - příkaz ROLLBACK je možno použít i pro případ omylem vymazaného záznamu Příklad: - vymaže v tabulce REZERV záznam s číslem čtenáře J60 a následně toto zrušení příkazu odvolejte SELECT * FROM rezerv; DELETE FROM rezerv WHERE c_ct='J60'; SELECT * FROM rezerv; ROLLBACK; SELECT * FROM rezerv; Vytvoření návratových bodů - předchozí varianty popisují způsob odvolání všech nepotvrzených změn od posledního příkazu COMMIT nebo od posledního regulérního ukončení konzolové aplikace - v případě neregulérního ukončení se provede automatický ROLLBACK. Tento příkaz však odvlává všechny doposud nepotvrzené změny vykonané uživatleme Alternativa: - pomocí píkazu SAVEPOINT vytvořit body návratu pro případné odvolání transakce k určitému bodu SAVEPOINT vyrazeni; DELETE FROM ctenar WHERE c_ct='H32'; UPDATE ctenar SET dat_nar='07.07.77' WHERE c_ct='H31'; SELECT * FROM ctenar; ROLLBACK TO vyrazeni; SELECT * FROM ctenar;

PHP a databáze: PHP - meziplatformní skriptovací jazyk, doplněk HTML zpracovávaný na straně serveru - instrukce PHP načítá a provádí server, nikdy se nedostanou až k prohlížeči. Server WWW nahrazuje kód jazyka PHP obsahem, který generuje - skript v PHP = textový soubor s příponou .php nebo .html - kód PHP je od kódu HTML oddělen speciální znaky - server určí způsob jeho interpretace prohlížeči - pro označení začátku se používá kombinace znaků: PHP srovnání s HTML a JavaScript: - jazyku HTML chybí akce a dynamika - důvod vzniku JavaScript - JavaScript pracuje na straně prohlížeče a proto nemůže měnit data nebo pracovat s databázemi - vznik PHP Princip PHP: PHP - programovací jazyk vstuvek, které se dají vkládat do obyčejných HTML souborů. Symbolicky zapsáno: html zdroj, pokračování html zdroje - soubory s takovými vsuvkami se pojmenovávají příponou *.php. Při požadavku na PHP stránku server prochází soubor, vsuvky programově vyhodnoscuje a klientovi odesílá už čisté HTML - na výstup dorazí: html zdroj, výsledek PHP vsuvky porkačování thml zdroje Syntaxe jazyka PHP: 1. V názvech proměnných se rozlišují velká a malá písmena 2. Jednotlivé příkazy se ukončují středníkem 3. Komentář // komentář 4. Názvy proměnných začínají symbolem dolaru ($) 5. Proměnné jsou beztypové, mohou obsahovat hodnoty libovolného typu. Jazyk proměnnou nedeklaruje automaticky, když se do ní poprvé vkládá hodnota. Př. $vozidlo="auto";

- 24 -

Christy


Datové typy v PHP: - celá čísla ($počet=16;) - reálná čísla ($cislo=0.25;) - řetězce ($jmeno="Jan";) - pole ($pol[0]="auto";) ($pol[1]="kolo";) - objekty - složený datový typ obsahující libovolný počet proměnných a funkcí Echo - zobrazení údaje v prohlížeči Např. echo "vítáme tě návštěvníku"; PHP a databáze MySQL: " while ($row=mysql_fetch_array($result)) echo "". $row["jmeno"]. "". $row ["prijmeni"]. ""; echo ""; mysql_free_result ($result); mysql_close(); ?> - zobrazí vybrané věty ve formě tabulky realizovaný skript vygeneruje HTML kód pro tabulku: ... atd.

Jan Klouček

Jiří Kostelka

Josef Machek
Zkouškový test: 10-12 otázek na papíře - stručná odpověď - povolená syntaxe příkazů - interpretace příkazů - minimum 5 splněných otázek - máme právo být ústně zkoušeni - otázky tak jak jsou vypíchnuty ve skriptech a takové jaké se můžou objevit u státnic

- 25 -

Christy

Databázové systémy I přednášky

Recommend Documents