Databázové Aplikace

1. DatabázovéAplikace Slidy k přednášce NDBI026 KSI MFF UK http://www.ms.mff.cuni.cz/~kopecky/vyuka/dbapl/ Verze 13.10.02.12.00

2. Literatura Manuálové stránky Oracle 11g Dostupné odkudkoli http://www.oracle.com/pls/db112/homepage Dostupné z domény .mff.cuni.cz http://tirpitz.ms.mff.../intra/oracle/doc/ora1120doc/ Dostupné odkudkoli http://www.orafaq.org/faq.htm http://www.orafaq.org/faq2.htm DBI026 -DB Aplikace - MFF UK

3. Literatura, pokrač. Manuálové stránky MS SQL Dostupné odkudkoli http://technet.microsoft.com/en-us/library/bb545450.aspx DBI026 -DB Aplikace - MFF UK

4. Literatura, pokrač. D. Quass, J. Widom. R. Goldman, K. Haas, Q. Luo, J. McHugh, S. Nestorov, A. Rajaraman, H. Rivero, S. Abiteboul, J. Ullman, and J. Wiener: LORE: A Lightweight Object REpository for Semistructured Data, http://www-db.stanford.edu/lore/pubs/lore-demo.pdf, http://www-db.stanford.edu/lore Roy Goldman, Jennifer Widom: DataGuides: Enabling Query Formulation and Optimalization in Semistructured Databases, http://www-db.stanford.edu/lore/pubs/dataguide.pdf DBI026 -DB Aplikace - MFF UK

5. O čem to bude? Předpoklady Databázové systémy Základy SQL SELECT příkazy Návrh schémat Normální formy Vlastnosti transakcí, serializovatelnost, Co se dozvím Praktický návrh aplikací Na co si dát pozor Při vytváření relačních schémat Při psaní SQL příkazů Optimalizace odezvy Indexy Plány provádění Ve víceuživatelském provozu Zamykání Transakční zpracování Zabezpečení dat Přednáška č. 1 DBI026 -DB Aplikace - MFF UK

6. Co zde bude Relační model dosud nejrozšířenější a nejpoužívanější Optimalizace dotazů volba indexů a vhodné formulace dotazu může ovlivnit výsledný čas provádění dotazu i o několik řádů Víceuživatelský provoz nevhodně navržená aplikace může způsobovat nekorektní průběh datových manipulací a neočekávané výsledky DBI026 -DB Aplikace - MFF UK

7. Co zde bude, pokrač. Procedurální rozšíření Triggery pro hlídání korektnosti aktualizací Uložené procedury a funkce Objektové rozšíření Uživatelské datové typy Hnízděné tabulky … DBI026 -DB Aplikace - MFF UK

8. Na čem se cvičí? DB systém Oracle 11g Objektově relační databáze Podpora provádění kódu v jazycích PL/SQL Java C/C++ (obecně jakákoli .dll/.so knihovna) Podpora XML, multimédií a dalších rysů DB MS SQL 2008 R2 Objektově relační databáze Podpora provádění kódu v jazycích T-SQL C# Podpora XML, textu a dalších rysů DBI026 -DB Aplikace - MFF UK

9. Problémy ANSI/ISO norem SQLs jejich (ne)dodržováním SQL-78 SQL-92 ANSI/SQL2;ISO/IEC 9075:1992 Entry, Intermediate, Full SQL-99ANSI/ISO/IEC 9075:1999 SQL-2003ISO/IEC 9075:2003 … 78 92 99 DBI026 -DB Aplikace - MFF UK

10. Problémy ANSI/ISO norem SQLs jejich (ne)dodržováním Jednotlivé databázové servery ne vždy dodržují ANSI normu Obvykle pouze SQL-92 Entry Řada nepřenositelných rozšíření navíc Ne všechny rysy implementovány dle ANSI S novými verzemi se kompatibilita zlepšuje Často nyní existují obě syntaxe zároveň Běžný SQL-92 kompatibilní server 78 92 99 DBI026 -DB Aplikace - MFF UK

11. Problémy ANSI/ISO norem SQLs jejich (ne)dodržováním Čím více se při vývoji aplikace využijí rysy vyšší než SQL-92 Entry Tím menší je šance, že aplikace bude provozuschopná i na jiné databázi Části vyšších verzí jsou často řešeny pouze proprietárně a nepřenositelně Před začátkem vývoje je proto obvykle nutné zvolit cílovou platformu Její změna v prostředku vývoje nebo po nasazení aplikace je velmi drahá Běžný SQL-92 kompatibilní server 78 92 99 DBI026 -DB Aplikace - MFF UK

12. Problémy ANSI/ISO norem SQLs jejich (ne)dodržováním Co v případě, že server nechce SQL příkaz přijmout? Je příkaz nekorektní, nebo server normu nepodporuje? Validátory SQL http://developer.mimer.se/validator/ DBI026 -DB Aplikace - MFF UK

13. Úvod do SQL SELECT příkazVestavěné funkce a operátory

14. SELECT příkaz SELECT [DISTINCT]výraz1 [[AS]c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BY výraz_g1 [, …][HAVING podmínka_s]][ORDER BY výraz_o1 [, …]] DBI026 -DB Aplikace - MFF UK

15. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT]výraz1 [[AS] c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Nejprve se zkombinují data ze všech zdrojů (tabulek, pohledů, poddotazů) Pokud jsou odděleny čárkami, provede se kartézský součin ANSI SQL-92zavádí spojeníJOIN ON, NATURAL JOIN, OUTER JOIN, … DBI026 -DB Aplikace - MFF UK

16. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT] výraz1 [[AS] c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Vyřadí se vzniklé řádky, které nevyhovují podmínce DBI026 -DB Aplikace - MFF UK

17. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT] výraz1 [[AS] c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Zbylé řádky se seskupí do skupin se stejnými hodnotami uvedených výrazů(SORT/HASH) Každá skupina obsahuje atomické sloupce s hodnotami uvedených výrazů a množinové sloupce se skupinami ostatních hodnot sloupců DBI026 -DB Aplikace - MFF UK

18. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT] výraz1 [[AS] c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Vyřadí se skupiny, nevyhovující podmínce DBI026 -DB Aplikace - MFF UK

19. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT] výraz1 [[AS] c_alias1][, …]FROM zdroj1 [[AS] t_alias1] [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Výsledky se setřídí podle požadavků DBI026 -DB Aplikace - MFF UK

20. SELECT příkaz - logické vykonání (bez uvažování optimalizace dotazu) SELECT [DISTINCT]výraz [[AS]alias][, …]FROM zdroj1 t_alias1 [, …][WHERE podmínka_ř][GROUP BYvýraz_g1[, …][HAVING podmínka_s]][ORDER BYvýraz_o1[, …]] Vygeneruje se výstup s požadovanými hodnotami V případě DISTINCT se vyřadí duplicity(SORT/HASH) DBI026 -DB Aplikace - MFF UK

21. Co hlídat při psaní SELECT příkazu Klauzule GROUP BY setřídí před vytvořením skupin všechny řádky dle výrazů v klauzuli, v lepším případě nejdříve řádky podle hash hodnoty výrazů rozdělí do menších přihrádek Seskupovat by se měl co nejmenší možný počet řádek Pokud je možné řádky odfiltrovat pomocí WHERE, je výsledek efektivnější, než následné odstraňování celých skupin DBI026 -DB Aplikace - MFF UK

22. Co hlídat při psaní SELECT příkazu SELECT Ulice, COUNT(*)FROM ObyvatelWHERE Mesto='Brno'GROUP BY Mesto, Ulice; Setřídí se jen 370 tis. záznamů obyvatel Brna SELECT Ulice, COUNT(*) FROM Obyvatel GROUP BY Mesto, UliceHAVING Mesto='Brno'; Všech 10mil. záznamů se setřídí (zahešuje), ponechají se jen skupiny z Brna DBI026 -DB Aplikace - MFF UK

23. Co hlídat při psaní SELECT příkazu Klauzule DISTINCT třídí (hešuje) výsledné záznamy (ještě před operací ORDER BY),aby našla duplicitní záznamy Pokud to jde,je vhodné se bez DISTINCT obejít Klauzule ORDER BY by měla být použita, jen v nutných případech Není příliš vhodné ji používat v definicích pohledů, nad kterými se dále dělají další dotazy DBI026 -DB Aplikace - MFF UK

24. Přehled software pro cvičení

25. Oracle @ MFF Linux Servertirpitz.ms.mff.cuni.cz Databáze jedenact (11.x) Klienti Linux Laboratoř UW1, UW2 SQL Developer Laboratoř US1, US2(u-pl*.ms.mff…) SQL Developer jedenact tirpitz.ms.mff… TCP/IP UW1,2 u-pl*.ms.mff… DBI026 -DB Aplikace - MFF UK

26. MS SQL @ MFF Laboratoř UW1, UW2 Klienti Win32 MS SQL Server 2008 UW1,2 DBI026 -DB Aplikace - MFF UK

27. Relační model SQL Vytváření tabulek a pohledůIntegritní omezeníAktualizace dat

28. Vytváření tabulek CREATE TABLE jm_tab (jm_sloupce typ [(velikost)] [omezení],…,omezení_řádky,…); CREATE TABLE Osoba (rc character(11) CONSTRAINT Osoba_PK PRIMARY KEY,jmeno character(50) NOT NULL); DBI026 -DB Aplikace - MFF UK

29. Reprezentace textových dat SQL-92 rozlišuje dvoje kódování znaků Důvodem je podpora UTF-8 (UTF-16) Možnost ukládání znaků libovolného jazyka Neefektivní vícebajtová reprezentace znaků pro znaky národních abeced Globální znaková sada, UTF (ne nutně) Národní znaková sada pro texty v jednom konkrétním jazyce - českém, slovenském, … CP-1250, ISO-8859-2, … DBI026 -DB Aplikace - MFF UK

30. Reprezentace textových dat SQL-92 dále rozlišuje dvojí reprezentaci řetězců Pevná délka, zprava doplněno mezerami Snadnější aktualizace dat Méně efektivní reprezentace Proměnná délka, ukládají se použité znaky plus délka řetězce Efektivnější reprezentace Komplikovanější aktualizace, nová hodnota může zabrat jiný počet bajtů DBI026 -DB Aplikace - MFF UK

31. Datové typy dle ANSI SQL-921) Textové CHARACTER(n) text v pevné délce n znakůCHARACTER VARYING(n)CHAR VARYING(n)text v proměnné délce max. n znaků NATIONAL CHARACTER(n)text v pevné délce n znaků v národní abeceděNATIONAL CHARACTER VARYING(n)NATIONAL CHAR VARYING(n)NCHAR VARYING(n)text v proměnné délce max. n znaků v národní abecedě DBI026 -DB Aplikace - MFF UK

32. Datové typy dle ANSI SQL-921) Textové Konstanty se uzavírají do apostrofů Apostrof se v konstantách zdvojuje DBI026 -DB Aplikace - MFF UK

33. Datové typy dle ANSI SQL-922) Číselné NUMERIC(p[,s])obecný číselný typ na p platných míst, s cifer za desetinnou čárkouINTEGER, INT, SMALLINTcelé čísloFLOAT(b)reálné číslo v b-bitové přesnosti reprezentaceREALreálné čísloDOUBLE PRECISIONreálné číslo ve dvojnásobné přesnosti DBI026 -DB Aplikace - MFF UK

34. Datové typy dle ANSI SQL-923) Datumové DATEdatum (YYYY-MM-DD ) s přesností na dnyTIMEčas (HH:MM.SS.MMMM) s přesností minimálně na sekundyTIMESTAMPdatum plus čas (YYYY-MM-DD HH:MM.SS.MMMM)TIME[STAMP](p) WITH TIMEZONEp udává přesnost sekund, čásová zóna se udává na konci ve tvaru +HH:MM, resp. –HH:MM DBI026 -DB Aplikace - MFF UK

35. Datové typy dle ANSI SQL-923) Datumové Konstanty se uzavírají do apostrofův uvedeném formátu DBI026 -DB Aplikace - MFF UK

36. Datové typy dle ANSI SQL-92vs. reálná DB Databázové servery Ne vždy podporují všechny uvedené typy Nemusí tyto typy podporovat nativně,pouze si „přeloží“ název typu na podobný nativně podporovaný typ DBI026 -DB Aplikace - MFF UK

37. Datové typy dle ANSI SQL-92vs. Oracle SQL CHARACTER(n) CHARACTER VARYING(n)CHAR VARYING(n) NATIONAL CHARACTER(n) NATIONAL CHARACTER VARYING(n)NATIONAL CHAR VARYING(n)NCHAR VARYING(n) NUMERIC(p,s) INTEGER, INT, SMALLINT FLOAT(b)DOUBLE PRECISIONREAL CHAR(n) VARCHAR2(n) VARCHAR2(n) NCHAR(n) NVARCHAR2(n) NVARCHAR2(n) NVARCHAR2(n) NUMBER(p,s) NUMBER(38) NUMBERNUMBERNUMBER DBI026 -DB Aplikace - MFF UK

38. Datové typy dle ANSI SQL-92vs. Oracle SQL DATE Přesnost ukládání na sekundy, tj. splňuje požadavky SQL-92 na TIMESTAMP Základní (americký) formát DD-MON-YYnapř. 01-JAN-2006 VARCHAR2(velikost), //doporučenoVARCHAR(velikost) Řetězec s proměnlivou délkou velikost max. 4000 znaků (dop. max. 2000 znaků) DBI026 -DB Aplikace - MFF UK

39. Integritní omezení [CONSTRAINT jméno] definice_omezení[INITIALLY {DEFERRED|IMMEDIATE}][[NOT] DEFERRABLE] Pokud omezení není pojmenované, dostane obvykle nicneříkající jméno(V Oracle např. SYS_Cnnnnnn). Doporučuje se proto omezení pojmenovávat Jednotlivá sloupcová omezení se od sebe oddělují mezerou DBI026 -DB Aplikace - MFF UK

40. Integritní omezení NULL, resp. NOT NULL Sloupec může, resp. nemůže mít nedefinovanou hodnotu NULL. UNIQUE Sloupec musí mít všechny hodnoty různé. Obvykle se používá volnější interpretace:Sloupec musí mít všechny definované hodnoty různé. PRIMARY KEY Sloupec tvoří primární klíč tabulky, je automaticky chápán jako NOT NULL a UNIQUE. DBI026 -DB Aplikace - MFF UK

41. Integritní omezení CHECK (podmínka) Hodnota sloupce musí splňovat podmínku. REFERENCES tabulka(sloupec)[ON DELETE {CASCADE|SET NULL}] Hodnota odkazuje na primární klíč, nebo kandidátní klíč (UNIQUE sloupec) uvedené tabulky S klauzulí ON DELETE je povoleno mazání nadřízené řádky. Pokud je smazána, podřízená řádka je smazána rovněž, nebo je je hodnota ve sloupci nastavena na NULL DBI026 -DB Aplikace - MFF UK

42. Integritní omezení DEFAULT hodnota Defaultní hodnota, pokud není uvedeno jinak Standardně je DEFAULT NULL DBI026 -DB Aplikace - MFF UK

43. Integritní omezení Příklad CREATE TABLE Osoba( RC CHARACTER(11) CONSTRAINT Osoba_PK PRIMARY KEY, JMENO CHAR VARYING(30) CONSTRAINT Osoba_U_Jmeno UNIQUE NOT NULL, EMAILCHAR VARYING(30) CONSTRAINT Osoba_C_Email CHECK (EMAIL LIKE '_%@_%._%'); DBI026 -DB Aplikace - MFF UK

44. Datový slovník v Oracle / MS SQL Informace o tabulkách jsou v Oracle uloženy v pohledech USER_TABLES USER_TAB_COLUMNS USER_CONSTRAINTS • Informace o tabulkách jsou v MS SQL uloženy v pohledech • INFORMATION_SCHEMA.TABLES • INFORMATION_SCHEMA.COLUMNS • INFORMATION_SCHEMA.TABLE_CONSTRAINTS DBI026 -DB Aplikace - MFF UK

45. Řádková integritní omezení Mohou kontrolovat vzájemný vztah více sloupců jednoho řádku CHECK (zacatek <= konec) Mohou definovat vícesloupcové primární a kandidátní a cizí klíče PRIMARY KEY (zacatek, konec) FOREIGN KEY (zacatek, konec)REFERENCES Rodic (x, y) DBI026 -DB Aplikace - MFF UK

46. Atributy integritních omezení ENABLED / DISABLED Omezení je aktivní a platnost se ověřuje ALTER TABLE tabulka {ENABLE|DISABLE} CONSTRAINT omezení; DEFERRED / IMMEDIATE Ověření se odloží na konec transakce, jinak se ověřuje okamžitě DEFERRABLE / NOT DEFERRABLE Ověření je / není možné odkládat na konec transakce DBI026 -DB Aplikace - MFF UK

47. Na co si dát při návrhu pozor Hlídat na úrovni databáze všechny manipulace s daty, které ohlídat jdou Cokoli jde zadat uživatelem špatně, bude zadáno špatně Integritní omezení, triggery Později je čištění nekonzistentních dat namáhavé a opravy často nemožné Lépe ohlídat vše centrálně, než v každé aplikaci zvlášť DBI026 -DB Aplikace - MFF UK

48. Na co si dát při návrhu pozor Hlídat unikátnost vkládaných dat Každá tabulka by měla mít primární klíč I v případě, kdy je primární klíč uměle vytvořený, jednotlivé instance (řádky) obvykle mívají svůj přirozený jedno- či vícesloupcový identifikátor, který by měl být definován jako alternativní klíč tabulky (UNIQUE) Nezřídka existuje více alternativních klíčů DBI026 -DB Aplikace - MFF UK

49. #INCLUDE dbacv_01.ppt INSERT/UPDATE/DELETE Vestavěné funkce

50. Změna schématu aplikace Přidávání a odebíránísloupců a omezení Změna definicesloupců a omezení Přednáška č. 2