FAKULTA INFORMATIKY, MASARYKOVA UNIVERSITA V BRNĚ Slide k přednášce PV003 - Architektura relačních databází Milan Drášil, únor 2004 Souborově orientované systémy: Datový sklad File system Aplikační vybavení Výhody: - optimalizace datových struktur vzhledem k řešené problematice - menší systémové nároky Nevýhody: - aplikační závislost - obtížné zabezpečení konzistence dat - obtížná realizace konkurentních operací (zamykání souborů) - obtížná čitelnost - dokumentovatelnost datového modelu - téměř nemožný transakční přístup pro operaci s daty - obtížné řízení přístupových práv Databázově orientované systémy: File system Datový sklad RDBMS databázový server Aplikační vybavení (*.exe,*.cgi,*.php, *.asp ...) Výhody: - aplikační „nezávislost" - snadné zabezpečení konzistence dat - snadná realizace konkurentních operací - snadná čitelnost - dokumentovatelnost datového modelu - standardizovaná data umožňují i standardní vývoj IS, strukturovanou analýzu problematiky (vývoj pomocí prostředků CASE), od konceptuálního datového modelu je přechod do fyzického DM takřka automatizovatelný. - neprocedurální přístup k datum (tj. neříkám „jak" to chci, ale „co" chci) Nevýhody: - obtížná implementace nestandardních přístupových technik - obtížná implementace komplikovanějších datových struktur, je nutné je normalizovat do tabulek a to může zpomalit přístup k datům - neprocedurální přístup k datum Relační databáze (Codd červen 1970, ACM Journal, Communications of ACM): - datové struktury jsou n-ární relace - relace je reprezentována tabulkou, výčtem pravdivých n-tic relace - nad relacemi jsou proveditelné operace (relační algebra) a) projekce b) selekce c) násobení d) sjednocení e) průnik Požadavky na jazyk relační databáze - vytváření, modifikace a rušení relací - dotazy nad tabulkami tj. implementace relační algebry - vkládání, změna, odstranění řádku v tabulce - garance konzistence dat - řízení přístupových práv Krátká historie SQL: - IBM se věnovala vývoji jazyka, který by "lidským" způsobem zabezpečil operace nad relacemi, vznikl jazyk SEQUEL (Structured English Query Language) - Z SEQUEL (už se angličtině asi moc nepodobal) později vznikl jazyk SQL Structured Query Language dnes všeobecně uznáván za standard pro komunikaci s relačními databázemi. - Jsou kodifikovány standardy SQL (ANSI, ISO/IEC) Vývoj v komerčních firmách jde vývoj (pochopitelně!) rychleji, než práce standardizačních komisí => univerzální standard neexistuje jednotlivé implementace se liší (ORACLE, MS-SQL, INFORMIX, DB2) Části jazyka SQL - Definiční část - Data Definition Language - Manipulační část - Data Manipulation Language - Řízení transakcí - Transaction Control Procedurální nadstavby - Transact SQL (MS-SQL,Sybase) - PL/SQL (Procedural Language/SQL, ORACLE) Souborový přístup k datům: FILE *inf; inf=f open (...) ; while( ) { f seek (inf,...) ; f read (inf,...) ; } Embedded SQL: Sprintf(sqlStmt,"select jmeno , prijmeni from ..."); EXEC SQL PREPARE STOl FROM sqlStmt; EXEC SQL DECLARE CSTOl CURSOR FOR STOl; EXEC SQL DESCRIBE SELECT LIST FOR STOl INTO selda; EXEC SQL OPEN CURSOR CSTOl; EXEC SQL WHENEVER NOT FOUND GOTO QUERY_FINISHED; while(..) { EXEC SQL FETCH CSTOl USING DESCRIPTOR selda; } QUERY_FINISHED: EXEC SQL CLOSE CURSOR CSTOl; Lexikální konvence SQL: Příkaz jazyka SQL může být víceřádkový mohou být použity tabelátory. Tedy příkaz SELECT ENAME, SAL*12, MONTHS_BETWEEN (HIREDATE,SYSDATE) FROM EMP; a příkaz SELECT ENAME, SAL * 12, MONTHS_BETWEEN( HIREDATE, SYSDATE ) FROM EMP; jsou ekvivalentní. Velká a malá písmena nejsou podstatná v rezervovaných slovech jazyka SQL a identifikátorech. Tedy příkaz: SELECT ename, sal * 12, month_between( HIREDATE, SYSDATE ) FROM emp; je ekvivalentní s předchozími příkazy. Základní elementy jazyka SQL: - Konstanty (101/ text' ," ' něco j iného' ' ') - Integer (5803042157) - Number (580304.2157) - Datové typy (int, number (m, n) , date, varchar(n),long, long raw) - null speciální hodnota pro prázdnou hodnotu - Komentáře (/* */) - Objekty databázového schématu (tabulky, pohledy, indexy, sekvence,...) Z uvedeného vyplývá, že příkazy jazyka jsou závislé na zadaném databázovém schématu, tedy jeden příkaz SQL může být syntakticky správný v jednom schématu a v jiném nikoli. Například dotaz na tabulku je syntakticky špatně, když ve schématu tabulka daného jména neexistuje. DDL - Data Definition Language Vytváření tabulek příkaz create table CREATE TABLE scott.emp ( empno NUMBER, ename VARCHAR2(10) ); CREATE TABLE emp ( empno NUMBER CONSTRAINT pk_emp PRIMARY KEY, ename VARCHAR2(10) CONSTRAINT nn_ename NOT NULL CONSTRAINT upper_ename CHECK (ename = UPPER(ename)), job VARCHAR2(9), mgr NUMBER CONSTRAINT fk_mgr REFERENCES scott.emp(empno), hiredtDATE DEFAULT SYSDATE, sal NUMBER(10,2) CONSTRAINT ck_sal CHECK (sal > 500), comm NUMBER(9,0) DEFAULT NULL, deptno NUMBER (2) CONSTRAINT nn_deptno NOT NULL CONSTRAINT fk_deptno REFERENCES scott.dept(deptno) ); Modifikace tabulek - příkaz alter table Přidání sloupce: ALTER TABLE emp ADD ssn varchar2(32); Změna typu sloupce: ALTER TABLE emp modify date_of_birth (26); Odebrání sloupce: ALTER TABLE emp DROP COLUMN date_of_birth; Inteqritní omezení: Primární klíč: ALTER TABLE EMP ADD CONSTRAINT pk_emp PRIMARY KEY (empno,deptno); Cizí klíč: ALTER TABLE emp ADD CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES Scott.dept(deptno); Přehled integritních omezení: NOT NULL Vyplnění sloupce je povinné UNIQUE Sloupec (sloupce) má unikátní hodnoty v celé tabulce PRIMARY KEY Primární klíč tabulky REFERENCES Referenční integrita, hodnota sloupce je hodnotou primárního klíče jiné (stejné) tabulky CHECK Kontrola vloženého řádku Indexování tabulek příkaz - create index Index je uspořádaný seznam všech hodnot jednoho nebo více sloupců: - rychlý přístupu k řádkům tabulek - přístupu do tabulek v po řadě podle uspořádání CREATE INDEX emp_idxl ON emp (ename, job); není totéž, co CREATE INDEX emp_idxl ON emp (job, ename); Pro získání jednoznačné hodnoty typu INT (celé číslo) slouží tzv. sekvence. Obyčejně jsou využívány v těch situacích, kde neexistuje objektivní primární klíč v relační tabulce. Hodnota sekvence je generována nezávisle na transakčním zpracování. Ke každé sekvenci přistupujeme pomocí pseudosloupců: CURRVAL vrací současný stav sekvence NEXTVAL vrací následný stav sekvence CREATE SEQUENCE SEQl; CREATE SEQUEMCE SEQl START WITH 32 INCREMENT BY 100; Příkaz drop: DROP typ_objektu jméno_objektu Odstraní Objekt z datového schématu. Např. drop public synonym si; odstraní ze schématu synonymum s1 Klauzule cascade constraints odstraní intergritní omezení související s touto tabulkou. DROP TABLE OKRES CASCADE CONSTRAINTS; odstraní i integritní omezení poi_obec_fkoi Synonyma: CREATE PUBLIC SYNONYM TI FOR TABULKA1; CREATE PUBLIC SYNONYM TABULKA1 FOR U1.TABULKA1; D M L - Data Manipulation Language Vkládání řádků do tabulek Příkaz INSERT: INSERT INTO tabulka (sloupec x f sloupec2,..., sloupecn) VALUES (hodnotalf hodnota2,..., hodnotan) Pořadí sloupců nemusí odpovídat pořadí v definici tabulky a nemusí být všechny. INSERT INTO tabulka VALUES (hodnotai, hodnota2,..., hodnotan) Pořadí sloupců musí odpovídat pořadí v definici tabulky, nedoporučuje se - změna struktury tabulky, přidání sloupců vynucuje změnu všech aplikací, které takový insert používají. Při příkazu insert se kontrolují všechna integritní omezení na tabulce. V případě, že není dodána hodnota a v definici tabulky je použita default klausule, potom je dosazena příslušná hodnota z default klausule. Sloupce které jsou primárním nebo unikátním klíčem jsou vždy indexovány, kontrola je rychlá. Kontrola referenční integrity - sloupce, na které odkazuje referenční integrita jsou buď primární, nebo unikátní klíče, proto je kontrola referenční integrity rychlá. Změna hodnot v řádcích tabulky Příkaz UPDATE: UPDATE tabulka SET sloupeci=hodnotai, sloupecn= hodnotan Změní hodnoty na všech řádcích UPDATE tabulka SET sloupec!=hodnotalf sloupecn= hodnotan WHERE logická_podmínka např. WHERE (VEK>40) and (VZDELANI='MUNľ ) Při příkazu update se kontrolují všechna dotčená integritní omezení na tabulce. Při změně hodnoty sloupce, který je primárním nebo unikátním klíčem je kontrola rychlá, sloupce jsou indexovány. Při změně hodnoty sloupce, na který odkazuje jiná tabulka cizím klíčem je kontrolována korektnost této operace, tedy prochází se "detailová" tabulka a kontroluje se výskyt staré hodnoty, v případě jeho nalezení operace končí chybou. Z toho plyne nutnost vytvořit indexy na každý cizí klíč! Odstranění řádků z tabulky Příkaz DELETE: DELETE FROM tabulka Odstraní vše! DELETE FROM tabulka WHERE podmínka Při mazání řádku z tabulky, na kterou odkazuje jiná tabulka cizím klíčem je kontrolována korektnost této operace, tedy prochází se "detailová" tabulka a kontroluje se výskyt mazané hodnoty, v případě jeho nalezení operace končí chybou. Další důvod, proč vytvářet index na každý cizí klíč! on delete klausule cascade - při odstranění řádků z nadřízené tabulky (al) se odstraní i řádky z tabulky podřízené (bl). create table al (i int primary key) ; create table bl (i int references al(i) on delete cascade); set null - při odstranění řádků z nadřízené tabulky (al) se odstraní je nastavena hodnota cizích klíčů podřízené tabulky (bl) na hodnotu null. create table al (i int primary key) ; create table bl (i int references al(i) on delete set null); Výběr z tabulek (vytváření relací) Jednoduché příkazy select: select all SLl, SL2 from TABULKA; Sloupce lze v rámci příkazu select přejmenovat: select SLl A,SL2 B from TABULKA; Výstup lze uspořádat (při velkých tabulkách je nutné na sloupce vytvořit index): select SLl A,SL2 B from TABULKA order by SLl; select SLl A,SL2 B from TABULKA order by SLl DESC; Fráze distinct neopakuje stejné řádky) select distinct SLl A,SL2 B from TABULKA; select SL1,SL2 from TABULKA where SLl ='BRNO' and SL2>0; select SL1,SL2 from TABULKA where upper(SLl)='BRNO'; Spojování tabulek (join) - násobení a selekce : Jméno, Příjmení, Okres, Obec, "Část obce", "Číslo popisné' select OS.JMÉNO OS.PŘÍJMENÍ OK.NÁZEV OB.NÁZEV CO.NÁZEV OS.ADRESA_CPOP from P01_OKRES OK, P01_OBEC OB, P01_CAST_OBCE CO, P01_OPSUB OS where OS.ID =58342157 AND OS.ADRESA_OKRES=OK.ID AND OS.ADRESA_OBEC =OB.ID AND OS.ADRESA COBCE=CO.ID ID NUMBERÍ4) NÁZEV VARCHAR2(64) P01_KAT_UZ ID NUMBERB) NÁZEV VARCHAR2(64) ID_OBEC NUMBER(5) ID= ID OBEC ID »ADRESA OKRES ID = SJM PARTNER2 ID = SJM PARTNER! P01_OPSUB NUMBER NUMBER(8) NUMBER(10) NUMBER NUMBER VARCHAR2(128) VARCHAR2(64) VARCHAR2(16) VARCHAR2(16) ID ICO RC SJM_PARTNER1 SJM_PARTNER2 PŘÍJMENÍ JMÉNO TITUL_PRED TITUL_ZA ADRESA_OKRES NUMBER(4) ADRESAJOBEC NUMBER(5) ADRESA_COBCE NUMBER(5) ADRESA_CPOP NUMBER(5) ID= IDJOPSUB P01 VLASTNI ID KU ID LV ID OPSUB PODILJDITATEL PODÍL JMENOVATEL NUMBER P01_PARCELA ID KU NU^ BER(6) ID_KU= ID_KU PARC CIS NUMBERÍ4) PAR POD NUMBERÍ3) DRUH POZ NUMBER(2) ID_LV = CIS_LV VÝMERA NU^ CIS LV NU^ BER(9) BER(5) ID_KU= ID_KU ID LV= ID LV P01_OBEC ID NUMBER(5) " NÁZEV VARCHAR2(64) ID_OKRES NUMBER(5) =ADRESA OBEC ID »ADRESA COBCE ID= ID_OBEC P01 CAST OBCE ID NUMBER(5) NÁZEV VARCHAR2(64) ID_OBEC NUMBER(5) NUMBERÍ6) NUMBER(5) NUMBER NUMBER ID = ID_CAST_OBCE ID_KU = LV_KU| ID_LV = CIS_LV ID KU NUMBER(6) ID LV NUMBERÍ5) P01 BUDOVA TYP CISLA VARCHAR2m CISLO DOMOVNÍ NUMBERÍ5) NUMBER(5) ALL(3701,3801,3201); 3) Příslušnost k množině select * from P01_OPSUB where ADRESA_OBEC IN (select ID from P01_OBEC where počet_obyv>2000); 4) Rozsahový dotaz select * from P01_OPSUB where RC BETWEEN 5800000000 AND 5899999999; 5) NULL test select * from P01_OPSUB Where TITUL_PRED IS NOT NULL; 6) Existence v poddotazu select * from P01_OPSUB A Where exists ( select NULL from P01_OBEC B where B.ID=A.ADRESA_OBEC AND B.ID_OKRES<>A.ADRESA_OBEC ); 7) Srovnání řetězců select * from P01_OPSUB where PRIJMENI LIKE 'Nov%'; 8) Logická kombinace 1) - 7) select id,nod2 from tp_hrany where user_name=user and task_id=taskid and nodl=curr_node and id <> curr_edge and switch=l and exists (select null from tp_uzly where user_name=user and task_id=taskid and tp_uzly.id=tp_hrany.nod2) ; Množinové operace nad relacemi: Sjednocení: select ... union [all] select... Průnik: select ... intersect select... Diference: select ... minus select... Každý select je formálně tabulka select * from ( select JMENO, PRIJMENI FROM ... ORDER BY PRIJMENI||RC ) WHERE PRIJMENIIIRC BETWEEN 'xxxx' AND 'yyyy'; Uložené příkazy select = view create view jmeno as select ... S objekty typu view se v DM L zachází: select : stejně jako s tabulkami update : všechny sloupce jsou jednoznačně přiřazeny key-preserved tabulkám - tj. takovým tabulkám jejichž každý klíč je zároveň klíčem view, příkaz mění řádky právě jedné tabulky, delete : řádky view odkazují na právě jednu key-preserved tabulku, z ní jsou řádky vymazány insert : nesmí se explicitně nebo implicitně odvolávat na sloupce náležící non-key-preserved tabulce, všechny vkládané sloupce náleží právě jedné key-preserved tabulce CREATE TABLE ODDĚLENI ( ID INT PRIMARY KEY, NÁZEV VARCHAR2(256) ); CREATE TABLE PRACOVNÍK ( ID INT PRIMARY KEY, JMÉNO VARCHAR2(32), PŘÍJMENÍ VARCHAR2(32), ID_ODD INT, CONSTRAINT PFK1 FOREIGN KEY (ID_ODD) REFERENCES ODDĚLENI(ID) ); CREATE VIEW PRAC_EXT AS SELECT A. ID ID_PRAC, A.PRIJMENI PRIJMENI, A.JMENO JMENO, B.ID ID_ODD, B.NAZEV NAZ_ODD FROM PRACOVNÍK A, ODDELENÍ B WHERE A.ID_ODD=B.ID; 1) Které sloupce z tohoto VIEW jdou vkládat? 2) Které sloupce z tohoto VIEW jdou měnit? 3) Lze z tohoto VIEW mazat (DELETE), co se stane při? Jeden až několik pracovníků ze stejné oblasti má přidělen účet a může vidět jen svou oblast: CREATE TABLE PVP_ _PRACOVNIK ( ID_PRACOVNIK NUMBER NOT NULL, OBLAST VARCHAR2 (8) NOT NULL, ORG_JEDN_HR VARCHAR2 (10) NOT NULL, HARMONOGRAM VARCHAR2 (9) NOT NULL, USER_NAME VARCHAR2 (16) r příjmení VARCHAR2 (50) NOT NULL, JMÉNO VARCHAR2 (25) NOT NULL, TARIFNI_TRIDA VARCHAR2 (4), TARIFNI_STUPEN VARCHAR2 (2), DATUM_NASTUPU DATE NOT NULL, DATUM_VYSTUPU DATE, CREATE OR REPLACE VIEW U_PVP_PRACOVNIK select * from PVP_PRACOVNIK WHERE AKTUALNI='A' AND OBLAST IN (SELECT OBLAST FROM PVP_PRACOVNIK WHERE USER_NAME=USER ) AS Materializované pohledy Jsou uložené výsledky dotazů (select), narozdíl od view výsledky jsou skutečně fyzicky uloženy. Je možnost výsledky dotazu obnovovat. create materialized view vi REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1/1440 as select ... refresh metoda obnovy fast pohled musí mít primární klíč, musí exitsovat materialized view log na detailové tabulce complete provedeni celého dotazu znovu force server vybere rychlejší metodu start with . . next interval obnovy Uživatelsky definované datové typy (APT) create type Point as object ( x number, y number ) create type Points as varray (10000) of Point; create type LineString as object ( NumPoints int, Vertexes Points ) create table Streets ( id int, geom LineString, constraint Streets_pk primary key (id) ) insert into Streets (id,geom) values (1, Linestring(3, Points( Point(0 Point(2000, Point(2020, ) ) ) 0), 123), 13460) Obecně není možné select geom from Streets, Ne všechny typy klientských rozhraní podporují ADT. Jednou z možností je podpora pomocí XML: select xmlelement("ROW",geom).getStringVal() from Streets resp: select xmlelement("ROW",geom).getClobVal() from Streets vrátí: 3 0 0 2000 123 2020 13460 Vzhledem, tomu, že v moderních vývojových prostředí klientský aplikací (C++, C# .NET) je implementována masivní podpora parsingu XML, jedná se o poměrně silný a univerzální prostředek. Další možnosti XML: Metoda xmlforest select xmlElement("ROW", xmlforest(ID,GEOM) ) .GetStringValO from streets XmlAgg() Select xmlagg( xmlElement( "ROW", xmlforest(ID,GEOM) ) ).GetStringVal() from streets Poznámka: ADT nelze indexovat. XML - metoda Getciobvai () je řádově pomalejší, než GetStringVal () - a ta funguje jenom do 4KB. Outer join Outer join vrací všechny řádky, které vyhovují podmínce a takové řádky z jedné tabulky, které ji nesplňují. Sloupce, resp. výrazy v příkazu select z ostatních tabulek jsou v těchto případech vraceny jako null. Provádí se (+) operátorem ve where klausuli. create table ti (i int); create table t2 (i int); insert into ti values (1); ..(2); ..(3); insert into t2 values (2); ..(3); ..(4); SELECT ti. i il,t2.i i2 FROM ti,t2 WHERE tl.i=t2.i SELECT ti.i il,t2.i i2 II 12 FROM ti,t2 ------ WHERE tl.i=t2.i (+) 1 2 2 3 3 SELECT ti.i il,t2.i i2 II 12 FROM ti,t2 ------ WHERE tl.i(+)=t2.i 2 2 3 3 4 Hierarchické dotazy: Vyberou podstrom ze stromové struktury v tabulce. create table HI ( ID INT, PARENT INT, CONSTRAINT HI_FK01 FOREIGN KEY (PARENT) REFERENCES HI(ID); ); INSERT INTO HI VALUES (l,null); INSERT INTO HI VALUES (2,1); INSERT INTO HI VALUES (3,1); INSERT INTO HI VALUES (4,3); INSERT INTO HI VALUES (5,3); connect by klausule definuje relaci ve stromu start with klausule Příklad: select level,id,parent from hi connect by prior ID=PARENT start with id=3; LEVEL ID PARENT 13 1 2 4 3 2 5 3 Skupinové funkce - funkce založené na více řádcích Pokud není uvedena group by klausule potom je výsledek funkce aplikován na celý, select AVG ( [DISTINCT | ALL] expr) - průměr Z expr select AVG(PLAT) from ZAMESTNANCI where VEK between 25 and 30; COUNT ({* | [DISTINCT | ALL] expr })- počet řádků, kterých je expr not null select count(*) from P01_VL vrátí počet řádků z tabulky poi_vl Další skupinové funkce: MAX([DISTINCT|ALL] expr) MIN([DISTINCT|ALL] expr) STDDEV([DISTINCT|ALL] expr) SUM([DISTINCT|ALL] n) group by klausule Použitím group by klausule jsou podle výrazu v této klausuli agregovány řádky výsledku select PŘÍJMENÍ from P01_OPSUB group by PŘÍJMENÍ; Seznam sloupců v select příkazu, který obsahuje group by může obsahovat pouze: - Konstanty - skupinové funkce - výrazy, které jsou identické s výrazy v group by - výrazy, které jsou založeny na předešlých výrazech select rtrim(upper(PŘÍJMENÍ),40)|| y-y\|count(*) from P01_OPSUB group by PŘÍJMENÍ; having klausule Používá se k omezení výstupu na základě skupinových funkcí select rtrim(upper (PŘÍJMENÍ) ,40) | | y-\ count (*) from P01_OPSUB group by PŘÍJMENÍ having count(*)>2; 1) Obsahuje-li select WHERE klausuli zpracují se pouze řádky které vyhovují WHERE 2) Obsahuje-li select GROUP BY klausuli, vytvářejí se skupiny podle výrazů group by 3) Obsahuje-li select HAVING klausuli, potom jsou vyřazeny ty skupiny, které podmínku having nesplňují Optimalizace příkazů Exekuční plán: delete from plan_table; commit; explain plan SET STATEMENT_ID = 'Adresa Ol1 INTO plan_table for select a.NAZEV Okres, b.NAZEV Obec, c.NAZEV Ulice, d.CIS_OR COR from u_okresy A, u_obce B, u_ulice C, u_adresy D where b.kodok=a.kodok and c.iczuj=b.iczuj and d.kodul=c.kodul and d.PRIZNAK_ulice=c.priznak and b.nazev like 'Brno%' and c.NAZEV like 'Podlesná%' SELECT LPAD(' ' , 2*(LEVEL-1))||operation operation, options, object_name,object_type FROM plan_table START WITH id = 0 AND statement_id = 'Adresa 0 CONNECT BY PRIOR id = parent_id AND statement_ = 'Adresa 01'; SELECT STATEMENT NESTED LOOPS NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID U_OBCE TABLE INDEX RANGE SCAN IX_U_OBCE INDEX TABLE ACCESS FULL U_ULICE TABLE TABLE ACCESS BY INDEX ROWID U_OKRESY TABLE INDEX UNIQUE SCAN PK_U_OKRESY INDEX (UNIQUE) INDEX RANGE SCAN IX_U_ADRESY_2 INDEX Přístup k tabulce: FULL INDEX RANGE SCAN UNIQUE SCAN Operace pro Join (spojení): NESTED LOOPS MERGE JOIN SORT JOIN HASH JOIN Řízení přístupu - HINT {DELETE I INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ /*+ INDEX(jméno_indexu) */ - vynutí použití indexu CREATE INDEX P01_OPSUB_I2 ON P01_OPSUB(ADRESA_OKRES); CREATE INDEX P01_OPSUB_Il ON P01_OPSUB(PŘÍJMENÍ); CREATE INDEX P01_OKRES_Il ON P01_OKRES(NÁZEV); select /*+ INDEX(P01_OKRES_I1) */ a.prijmeni, b.název from p01_okres b, p01_opsub a where a.ADRESA_OKRES=b.ID AND B.NAZEV='KROMĚŘÍ Ž' AND A.PŘÍJMENÍ = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (RANGE SCAN) OF P01_OKRES_Il (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_Il (NON-UNIQUE) /*+ ORDERED */ Spojení (JOIN) tabulek probíhá v pořadí podle FROM klausule. select /*+ ORDERED */ a.příjmení, b.název from p01_opsub a, p01_okres b where b.ID=a. ADRESA_OKRES AND B.NAZEV='KROMĚŘÍ Ž' AND A.PŘÍJMENÍ = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_Il (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (UNIQUE SCAN) OF P01_OKRES_PK (UNIQUE) select /*+ ORDERED */ a.příjmení, b.nazev from p01_okres b, p01_opsub a where b.ID=a.ADRESA_OKRES AND B.NAZEV='KROMIŘÍ Ž' AND A.PŘÍJMENÍ = 'NOVÁK' SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_OKRES INDEX (RANGE SCAN) OF P01_OKRES_Il (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_OPSUB INDEX (RANGE SCAN) OF P01_OPSUB_Il (NON-UNIQUE) EXPLAIN PLAN FOR select KU.NAZEV, PA.PARC_TYP, PA.PARC_CIS, PA.PAR_POD, VL.PODIL_CITATEL||'/'||VL.PODIL_JMENOVATEL from P01_VLASTNI VL, P01_PARCELA PA, P01_KAT_UZ KU where VL.ID_OPSUB=1 AND VL.ID_KU =PA.ID_KU AND VL.ID_LV =PA.CIS_LV AND PA.ID_KU =KU.ID; SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS NESTED LOOPS TABLE ACCESS (FULL) OF P01_KAT_UZ TABLE ACCESS (BY INDEX ROWID) OF P01_PARCELA INDEX (RANGE SCAN) OF P01_PARCELA_PK (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_VLASTNI INDEX (UNIQUE SCAN) OF P01_VLASTNI_PK (UNIQUE) create index P01_VLASTNI_il on P01_VLASTNI(ID_OPSUB); EXPLAIN PLAN FOR select KU.NAZEV, PA.PARC_TYP, PA.PARC_CIS, PA.PAR_POD, VL.PODIL_CITATEL||'/'||VL.PODIL_JMENOVATEL from P01_VLASTNI VL, P01_PARCELA PA, P01_KAT_UZ KU where VL.ID_OPSUB=1 AND VL.ID_KU =PA.ID_KU AND VL.ID_LV =PA.CIS_LV AND PA.ID_KU =KU.ID; SELECT STATEMENT Optimizer=CHOOSE NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY INDEX ROWID) OF P01_VLASTNI INDEX (RANGE SCAN) OF P01_VLASTNI_Il (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_PARCELA INDEX (RANGE SCAN) OF P01_PARCELA_PK (UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF P01_KAT_UZ INDEX (UNIQUE SCAN) OF P01_KAT_UZ_PK (UNIQUE) TCC - Transaction Control Commands Transakce - je posloupnost D M L příkazů, které převedou datové schéma z jednoho konzistentního stavu do druhého ACID A - Atomic celá se provede, nebo odvolá C - Consistent na konci není porušeno žádné omezení I - Isolated operace jsou izolovány od ostatních t-cí D - Durale po ukončení transakce jsou data trvale uložena commit - Potvrzení změn D M L od počátku transakce. rollback [to savepoint] -Odvolá změny od počátku transakce/ savepoitnt savepoint - Stanoví místo po které lze provést rollback set transaction read write - default nastavení transakcí set transaction read only - nastaví transakci tak, že nejsou povoleny příkazy insert, update, delete a select s klausulí for update . Musí být prvním příkazem transakce Úrovně izolace SET TRANSACTION ISOLATION LEVEL SERIALIZABLE - Úroveň izolace podle normy SQL92. V případě že se transakce změní něco, co je změněno jinou nepotvrzenou transakcí, která začala dříve, potom transakce končí chybou. SET TRANSACTION ISOLATION LEVEL READ COMMITTED - default chování ORACLE. V případě, že transakce požaduje zámek na řádky, které jsou drženy jinou transakcí, potom transakce čeká na uvolnění, potom DML příkaz provede. SELECT ...... FOR UPDATE [NOWAIT]; Uzamkne vybrané řádky/sloupce pro aktuální transakci až do commit nebo rollback. LOCK TABLE lock mode MODE [NOWAIT]; row share - Zakazuje exclusive lock, jinak nechává povolené konkurentní aktivity na tabulce exclusive - Výhradní právo na tabulku pro transakci, mimo select zakazuje cokoli. share - Zakazuje update tabulky CREATE TABLE II ( I INT, C VARCHAR2(64), CONSTRAINT Il_PK PRIMARY KEY (I) ); INSERT INTO II VALUES (1, AAA); COMMIT; READ COMMITED SE #1 - UPDATE II SET C='B' SE #2 - UPDATE II SET C='C' SE #1 - COMMIT; SE #2 - COMMIT; WHERE 1=1;[OK] WHERE 1=1;[OK - čeká] [OK] [OK] SELECT * FROM II I______C 1 C SERIÁLIZABLE SE #1 - UPDATE II SET C='B' WHERE 1=1;[OK] SE #2 - UPDATE II SET C='C' WHERE 1=1;[OK - čeká] SE #1 - COMMIT; [OK] SE #2 - havaruje SELECT * FROM II I c 1 B Integritní omezení initially deferred kontrolují se až v okamžiku commit transakce. Příklad: povinná vazba 1:1 CREATE TABLE TI ( I INT PRIMARY KEY ); CREATE TABLE T2 ( I INT PRIMARY KEY ); ALTER TABLE Tl ADD CONSTRAINT T1_FK1 FOREIGN KEY (I) REFERENCES T2(I); ALTER TABLE T2 ADD CONSTRAINT T2_FK1 FOREIGN KEY (I) REFERENCES T1(I) INITIALLY DEFERRED; Proběhne: INSERT INTO T2 VALUES (2); INSERT INTO Tl VALUES (2); COMMIT; Havaruje: INSERT INTO Tl VALUES (3); INSERT INTO T2 VALUES (3); COMMIT; Deadlock: CREATE TABLE II ( I INT, C VARCHAR2 (64) , CONSTRAINT Il_PK PRIMARY KEY ); (D INSERT INTO II VALUES (1, AAA); INSERT INTO II VALUES (2,ABA); COMMIT; SE #1 - UPDATE II SET C='C' WHERE 1=1;[OK] SE #2 - UPDATE II SET C='D' WHERE 1=2;[OK] SE #1 - UPDATE II SET C='E' WHERE 1=2;[OK - čeká] SE #2 - UPDATE II SET C='F' WHERE 1=1;[OK - čeká] se #1 - během čekání na prostředek došlo k deadlocku SE #1 - COMMIT; SE #2 - COMMIT; SELECT * FROM II I______C 1______F 2 D Strategie transakcí z klientských aplikací. Optimistický a pesimistický přístup transakcí Organizace rollback segmentů (snímek je příliš starý) Jazyk - PL/SQL Je součástí databázového stroje. Je procedurální jazyk, tak jak je pojem procedurálního jazyka běžně chápán. Je strukturován do bloků, tj. funkce a procedury jsou logické bloky, které mohou obsahovat bloky atd. Příkazy: řídící příkazy jazyka PL/SQL, při řazen í-výrazy, SQL příkazy DML. [DECLARE — declarations] BEGIN — statements [EXCEPTION — handlers] END; Deklarace: Kolik_mi_zbyva_penez NUMBER(6); skutečné BOOLEAN; Datový typ taJbuiJfca%ROWTYPE odpovídá struktuře tabulky. Datový typ tabulka. si o upec%ROWTYP e odpovídá typu sloupce v tabulce JM P01_OPSPUB.JMENO%TYPE; OBSUB%ROWTYPE; Přiřazení, výrazy: tax := price * tax_rate; bonus := current_salary * 0.10; amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3)); valid := FALSE; into fráze: SELECT sal*0.10 INTO bonus FROM emp WHERE empno = emp_id; Kursorv: DECLARE CURSOR cl IS SELECT empno, ename, job FROM emp WHERE deptno = 20; Ovládání kursorů: 1) Analogie k souborovému přístupu: OPEN, FETCH, CLOSE OPEN CI; FETCH CI into a,b,c; CLOSE CI; 2) For cykly pro kursory: DECLARE CURSOR cl IS SELECT ename, sal, hiredate, deptno FROM emp; BEGIN FOR emp_rec IN cl LOOP salary_total := salary_total + emp_rec.sal; END LOOP; Použití ROWTYPE pro kursory: DECLARE CURSOR cl IS SELECT ename, sal, hiredate, job FROM emp; emp_rec cl%ROWTYPE; Řídící příkazy: IF-THEN-ELSE IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds'); END IF; FOR-LOOP FOR i IN 1..order_qty LOOP UPDATE sales SET custno = customer_id WHERE serial_num = serial_num_seq.NEXTVAL; END LOOP; WHILE-LOOP WHILE salary < 4000 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; Asynchronní ošetření chyb: begin select ...... into a,b,c; EXCEPTION WHEN NO_DATA_FOUND THEN — process error end; Funkce a procedury: CREATE OR REPLACE PROCEDURE [FUNCTION] jmeno ( pari IN VARCHAR2, par2 OUT INT ) [RETURN VARCHAR2] IS vari VARCHAR2(1); BEGIN RETURN [varl]; END jmeno; / Funkce lze použít v DML příkazech například: SELECT moje_funkce(43) FROM DUAL; SELECT moje_funkce(SL3+SL2); DELETE FROM TABl WHERE SLl=moje_funkce(SL3+SL2); Procedury spouštíme v rámci PL/SQL bloku: Begin moje_procedura(argument,......); end; Balíky- Package: CREATE PACKAGE name AS — public type and item declarations — subprogram specifications END [name]; CREATE PACKAGE BODY name IS — private type and item declarations — subprogram bodies END [name]; CREATE PACKAGE STEMIG AS C_MASTER_NAME VARCHAR2(16): FUNCTION TO_NUMEXT (x in char) FUNCTION ANG (XI IN NUMBER,Yl X2 IN NUMBER,Y2 RETURN NUMBER; END STEMIG; CREATE PACKAGE BODY STEMIG IS FUNCTION TO_NUMEXT (x in char) RETURN number IS R number; BEGIN R:=TO_NUMBER(x); return(R); exception when VALUE_ERROR THEN return(NULL); END; ='S3'; RETURN number; IN NUMBER, NUMBER) END STEMIG; Dynamické SQL příkazy: Jsou dotazy jejichž konečný tvar vzniká až při běhu programu. EXECUTE IMMEDIATE sql_stmt := 'INSERT INTO dept VALUES (:1, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; OPEN-FOR DECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN sqlStmt= 'SELECT ename, sal FROM emp WHERE sal > : OPEN emp_cv FOR sqlStmt USING my_sal; END; LOOP FETCH emp_cv INTO my_ename, my_sal; EXIT WHEN emp_cv%NOTFOUND; END LOOP; Triqqery: PL/SQL bloky, které jsou přidruženy k tabulkám. Události které spouští triggery: INSERT,UPDATE,DELETE Typy triggerů: STATEMENT ROW BEFORE Trigger je spuštěn jednou před provedením příkazu Trigger je spuštěn jednou před modifikací každého řádku AFTER Trigger je spuštěn jednou po provedení příkazu Trigger je spuštěn jednou po modifikaci každého řádku :NEW a :OLD proměnné v řádkovém triggeru odkazují na nové resp. staré hodnoty modifikovaného řádku. Logické proměnné v každém řádkovém triggeru: inserting - true jestliže trigger je spuštěn INSERT deleting - true jestliže trigger je spuštěn DELETE updating - true jestliže trigger je spuštěn UPDATE UPDATING (column_name) modifikuje Sloupec PL/SQL bloky nesmí obsahovat příkazy řízení transakcí (commit, rollback, ...) Triggery by neměly "šifrovat" data tedy by neměly obsahovat bloky typu: if UPDATING('STAV_KONTA') and JMENO_MAJITELE_UCTU='Drášil' and :NEW.STAV_KONTA < :OLD.STAV_KONTA THEN :NEW.STAV_KONTA := :OLD.STAV_KONTA; end if; Kódování zdrojových kódů balíků, těl balíků, procedur, funkcí - vznikne zašifrovaný zdrojový text (doporučuji -nikdy nepoužívat, programátoři svoje zdroje většinou šifrují dostatečně): WRAP INAME=input_file [ONAME=output_file] CREATE TRIGGER audit_trigger BEFORE INSERT OR DELETE OR UPDATE ON nejaka_tabulka FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO audit_table VALUES (USER||' is inserting'||' new key: '|| :new.key); :NEW.USER_NAME=USER; ELSIF DELETING THEN INSERT INTO audit_table VALUES (USER||' is deleting'||' old key: '|| :old.key); ELSIF UPDATING('FORMULA') THEN INSERT INTO audit_table VALUES (USER||' is updating'||' old formula: '|| :old.formula||' new formula: ' || :new.formula); ELSIF UPDATING THEN IF :OLD.USER_NAME<>USER THEN RAISE_APPLICATION_ERROR('-20000', 'Přistup k řádku odmitnuť) END_IF; INSERT INTO audit_table VALUES (USERU' is updating' | | ' old key: ' || :old.key||' new key: ' || :new.key); END IF; END; Administrace přístupových práv Role jsou seznamy práv: CREATE ROLE jméno; GRANT [system_prlv l role,..."[ TO [user|role|PUBLIC]; Příklady systémových práv: ALTER ANY TABLE, CREATE ANY SEQUENCE, CREATE PROCEDURE, SELECT ANY TABLE ... GRANT [object_prlv| ALL (column,...) ,... ] ON schema.object TO [user/role/PUBLIC] Příklady práv k objektům: ALTER,EXECUTE,INSERT,READ,SELECT,UPDATE Práva na tabulky končí na úrovni sloupců, pro práva na řádky tabulek musíme použít techniku triggerů. Zrušení práv REVOKE [prlv] from [user/role/PUBLIC] Postup administrace: 1) Vytvoříme DB schéma master uživatele. 2) Vytvoříme PUBLIC synonyma pro každý objekt. 3) Stanovíme role pro přístup k objektům, podle typů uživatelů. 4) Rolím přidělíme práva pro jednotlivé objekty. 5) Každý nový uživatel systému nevlastní žádné objekty, "vidí" je prostřednictvím veřejných synonym. 6) Správce systému přidělí potřebné role každému uživateli. PRO*C Deklarační část: EXEC SQL BEGIN DECLARE SECTION; VARCHAR DBuser[80]; VARCHAR DBpswd[20]; VARCHAR sql_stmt[8192]; EXEC SQL END DECLARE SECTION; SQLDA *selda; int i; Výkonná část: EXEC SQL WHENEVER SQLERROR do gsSqlError(); střepy(DBuser.arr,"TEST@GB001"); střepy(DBpswd.arr,"TEST") ; DBuser.len=strlen(DBuser.arr); DBpswd.len=strlen(DBpswd.arr); printf("connect\n"); EXEC SQL CONNECT :DBuser IDENTIFIED BY :DBpswd; srintf(stmtP,"DROP TABLE AUDIT"); střepy(sql_stmt.arr,stmtP); sql_stmt.len=strlen(sql_stmt.arr); EXEC SQL PREPARE STMT FROM :sql_stmt; EXEC SQL EXECUTE STMT; PRO*C překladač: střepy(sql_stmt.arr,stmtP) ; sql_stmt.len=strlen(sql_stmt.arr); /* EXEC SQL PREPARE STMT FROM :sql_stmt; */ { struct sqlexd sqlstm; sqlstm.sqlvsn = 10; sqlstm.sqhstv[0] = (void *)&sql_stmt; sqlstm.sqlest = (unsigned char *)&sqlca; sqlstm.sqlety = (unsigned short)256; sqlstm.occurs = (unsigned int )0; sqlstm.sqhstl[0] = (unsigned int )8194; sqlstm.sqhsts[0] = ( int )0; sqlstm.sqindv[0] = ( void *)0; sqlstm.sqinds[0] = ( int )0; sqlstm.sqharm[0] = (unsigned int )0; sqlstm.sqadto[0] = (unsigned short )0; sqlstm.sqtdso[0] = (unsigned short )0; sqlstm.sqphsv = sqlstm.sqhstv; sqlstm.sqphsl = sqlstm.sqhstl; sqlstm.sqphss = sqlstm.sqhsts; sqlstm.sqpind = sqlstm.sqindv; sqlstm.sqpins = sqlstm.sqinds; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); if (sqlca.sqlcode < 0) gsSqlError(); } /* EXEC SQL EXECUTE STMT; */ {struct sqlexd sqlstm; sqlstm.sqlvsn = 10; sqlstm.arrsiz = 4; sqlstm.sqladtp = &sqladt; sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn); if (sqlca.sqlcode < 0) gsSqlError(); } Dynamické příkazy v PRO*C Metoda 1 Příkaz vrací pouze úspěch/neúspěch a nepoužívá hostitelské proměnné. char dyn_stmt[132]; EXEC SQL WHENEVER SQLERROR do gsSqlError(); for (;;) { printf("Enter SQL statement: "); gets(dyn_stmt); if (dyn_stmt[0] == '\0') break; EXEC SQL EXECUTE IMMEDIATE :dyn_stmt; } Metoda 2 Příkaz vrací pouze úspěch/neúspěch a může používat hostitelské proměnné. EXEC SQL PREPARE sql_stmt FROM :delete_stmt; for (;;) { printf("Co chceš vymazat?"); gets(temp); emp_number = atoi(temp); if (emp_number == 0) break; EXEC SQL EXECUTE sql_stmt USING :emp_number; } Metoda 3 SELECT dotaz, který vrací více řádků, pevný počet sloupců do hostitelských proměnných. PREPARE statement_name FROM { :host_string | string_literal }; DECLARE cursor_name CURSOR FOR statement_name; OPEN cursor_name [USING host_variable_list]; FETCH cursor_name INTO host_variable_list; CLOSE cursor_name; EXEC SQL PREPARE SI FOR 'SELECT RC,JMÉNO,PŘÍJMENÍ FROM P01_OPSUB WHERE PŘÍJMENÍ LIKE ":A1%" '; EXEC SQL DECLARE CI CURSOR FOR SI; gets(temp); EXEC SQL OPEN CI USING :temp; for (;;) { EXEC SQL FETCH CI INTO :re,:jméno,:prijmeni; if ( ...) break; } Metoda 4 SELECT dotazy, který vrací více řádků, proměnný počet sloupců do vnitřních (C) proměnných. Je nutné použít popis těchto proměnných v deskriptoru - struktura SELDA. EXEC SQL PREPARE statement_name FROM { :host_string | string_literal }; EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name INTO bind_descriptor_name; ... doplnení BIND deskriptoru ... EXEC SQL OPEN cursor_name [USING DESCRIPTOR bind_descriptor_name]; EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name INTO select_descriptor_name; EXEC SQL FETCH cursor_name USING DESCRIPTOR select_descriptor_name; EXEC SQL CLOSE cursor_name; tdefine ALLOCLEN 1024; SQLDA *selda; char values[20][ALLOCLEN]; selda=sqlald(l,30,30); gets(sql_stmt.arr) sql_stmt.len=strlen(sql_stmt.arr); EXEC SQL PREPARE stmt FROM :sql_stmt; EXEC SQL DECLARE query_curs CURSOR FOR stmt; EXEC SQL OPEN query_curs; EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO selda; if(selda->F)>20 { return( 'SELECT vraci vie nez 20 sloupců'); } for(i=0;iF;++i) { selda->V[i]=(char *) value[i]; selda->L[i]=ALLOCLEN; selda->T[i]=DB_CHAR; } while () { EXEC SQL FETCH query_curs00 USING DESCRIPTOR selda; for(i=0;iF;++i) printf('%s',value[i]); } EXEC SQL CLOSE query_curs00; ... Návrh DB rozhraní: nativeCode char *dbConnect /* <= SUCCESS, or ptr to error string */ ( char *user, /* user name */ char *pswd, /* password */ char ♦service, /* service */ long r *sessionId /* session ID */ nativeCode char *dbDisconnect /* <= SUCCESS, or ptr to error string*/ ( void ); nativeCode char *gsProcessSQL /* <= SUCCESS, or ptr to error string*/ ( char *stmtP, /* =>non SELECT SQL */ long *rowsProcessed /* <= rows processed or NULL if not required */ ); typedef struct { int nColumns; // numer of cols in descripotor char **name; // ptr to array with col names char **value; // ptr to array with values to int *type; // type of values in descriptor long *allocLength; // lengths of buffers //(**value) long *actLength; // actual lengths fetched int *nullVal; // TRUE if NULL } DB_sqldaT; nativeCode char *dblnsert /* <= SUCCESS, or ptr to error string */ ( char *tableName, DB_sqldaT *values ); nativeCode char *dbUpdate /* <= SUCCESS, or ptr to error string */ ( long *rowsProcessed, // <= rows processed char *tableName,// => table name DB_sqldaT *values, // => values char *whereClause // whereClause //or NULL ); nativeCode char *dbOpenCursor /* <= SUCCESS, or ptr to error string*/ ( int *query_idP, // <= query ID char *selectStmtP,// => full select int nValues // => number of ); nativeCode char *dbFetch /* <= SUCCESS, or ptr to error string */ ( int query_id, // => query ID DBsqldaT *values // <= values ); nativeCode char *gsCloseCursor /* <= SUCCESS, or ptr to error string */ ( int query_id // => query id. ); nativeCode int gsGetNCols ( char *selectStmtP // => full select ); Normalizace a SQL Nultá normální forma - žádné omezení (někdy se uvádí nutnost existence alespoň jednoho atributu, který může obsahovat více než jednu hodnotu, někdy se uvádí "entity jsou reprezentovány tabulkami, jejich atributy sloupci"). První normální forma - všechny atributy tabulky jsou již dále nedělitelné, atomické. PARCELA KU# TYP# CISLO# PODLOMENI# VLASTNICI 523641 1 231 2 ID1,ID2,ID3 ... VLASTNÍK ID# JMÉNO ... 5803042751 - nelze zaručit konzistenci databáze pomocí referenční integrity (lze ji však zajistit pomocí thggerů) - nelze efektivně indexovat - komplikované neefektivní SQL dotazy (i když jsou v principu možné) function vlast (VLASTNICI IN VARCHAR2,PORADÍ IN INT) return int; /* vrací jedno ID z řetězce poradí) */ select ... from PARCELA A, VLASTNÍK B where vlast(A.VLASTNICI,1)=B.ID union all select ... from PARCELA A, VLASTNÍK B where vlast(A.VLASTNICI,2)=B.ID ... - Problém vymezení domén - je "rodné číslo" doména nebo se skládá ze den, mesic, rok, pohlaví , podlomeni ...? Zásadně vždy dodržet!!! Druhá normální forma - obsahuje primární klíč a každý neklíčový atribut je plně závislý na všech atributech tvořící primární klíč. OBEC ID_OKRES# ID_OBEC# POCET_OBYV_OBEC POCET_OBYV_OKRES ... 3702 1 398456 1456024 (není v 2. normální formě - pocet_obyv_okres je závislý na části klíče signalizuje existenci entity "okres") V zásadě není bezpodmínečně nutné dodržet (někdy kvůli výkonnosti opravdu nebývá dodržena - v některých případech se vyhneme join operaci), musíme dát pozor na: - existenci entit, jejichž existenci signalizuje podklíč denormalizovaných tabulek, který způsobuje porušení 2. Normální formy. - zaručení konzistence atributů v denormalizované tabulce pomocí thggerů OKRES ID_OKRES# POCET_OBYV_OKRES ... 3702 1456024 Někdy se jedná o netriviální systém thggerů viz. uvedený příklad: a) Změna počtu obyvatel v tabulce obec vyvolá trigger, který přepočítá POCET_OBYV_OKRES v tabulce OKRES. b) Změna počtu obyvatel v tabulce okres se musí zpětně promítnout do tabulky obec . Uvedené nelze provádět řádkovými triggery - tabulka je měněna a nelze v ní provádět update a select!!! Třetí normální forma - hodnoty atributů nejsou (funkčně) závislé na hodnotách jiných atributů. vlastník ID# JMÉNO příjmení RODNE_CISLO POHLAVÍ ... 1 Drášil Milan 5803042751 M 2 Drášilová Dominika 6552104531 Z (není v 3. Normální formě 3. cifra sloupce rodne_cislo je závislá na sloupci pohlaví) vlastník ID# ... POHLAVÍ ROK_N MESIC_A DEN_N RC 1 M 58 03 04 2751 2 Z 65 02 10 4531 U rozsáhlejších systémů takřka nelze dodržet - 3. Normální forma zakazuje redundanci dat. Ta bývá někdy i užitečná -rodné číslo může sloužit i ke kontrole správnosti pořízení data narození a pohlaví. - redundanci můžeme s klidným svědomím povolit, musíme však prostředky databáze zajistit její konsistenci (triggery,integritní omezení) alter table VLASTNÍK ADD constraint VLASTNIK_CH1 check ( (POHLAVÍ in ('M1,'Z')) AND ( ((POHLAVI='M') AND (SUBSTR(RC,3,1) IN (,0,,,ľ)) ) OR ((POHLAVI='Z') AND (SUBSTR(RC,3,1) IN ('S^'ö')) ) ) )