1. Relační databázový model

1. ● ●

●

Relační databázový model Poprvé představen 1969 (Dr. Edgar F. Codd) – IBM Založeno na ● Teorii množin ● Predikátové logice prvního řádu Umožňuje ● vysoký stupeň nezávislosti dat ● základ pro zvládnutí sémantiky dat ● použití množinově orientovaných jazyků

Pojmy relačního modelu ● Relace – tabulka se sloupci a řádky (proto relační model) ● Atribut – pojmenovaný sloupec relace ● Datová n­tice – řádek relace ● Doména – množina přípustných hodnot pro jeden nebo více atributů ● Relační databáze – kolekce normalizovaných tabulek Vlastnosti relace ● Má jméno, které ji odlišuje od všech ostatních ● Každý člen n­tice obsahuje právě jednu hodnotu ● Každý atribut má jedinečné jméno ● Všechny hodnoty atributu jsou ze stejné domény ● Pořadí atributů je nevýznamné ● Neexistují duplicitní n­tice ● Pořadí n­tic je nevýznamné Klíče ● ● ●

Atribut, nebo skupina atributů, která jednoznačně definuje n­tici. Superklíč – atribut, nebo skupina atributů, která jednoznačně definuje n­tici Kandidátní klíč – superklíč obsahující minimální počet atributů k jednoznačné identifikaci n­tice ● Primární klíč – zvolený kandidátní klíč, který definuje n­tici ● Cizí klíč – atribut, nebo skupina atributů, která odpovídá kandidátnímu klíči v jiné nebo stejné relaci

Další pojmy relačního modelu ● NULL – představuje hodnotu, která je prázdná, neplatná nebo neznámá ● Entitní integrita – každá n­tice musí mít plně vyplněný primární klíč ● Referenční integrita – existuje­li cizí klíč, musí být prázdný, nebo plně odpovídat kandidátnímu klíči v jiné tabulce 2.

Co je to relační databázový systém

Sada nástrojů pro efektivní a spolehlivé ukládání dat a pro manipulaci s nimi

Databáze ­ data a nástroje zajišťující jejich ukládání a manipulaci s těmito daty

Databázový server – soubor programových prostředků určených pro práci s daty, včetně organizace a realizace přístupu klientů k těmto datům Database (báze dat, databáze) uspořádaná množina dat ve formě záznamů, které jsou navzájem v určitém vztahu a jsou přístupné s pomocí systému řízení báze dat uživatelé databáze k ní mohou přistupovat (pracovat s ní) dvěma způsoby: • operace čtení (předchází výběr – SELECT) • operace zápisu (INSERT, UPDATE, DELETE) Database Management System – DBMS (systém řízení báze dat – SŘBD) • programový systém umožňující vytvoření, údržbu a použití báze dat • řídicí systém databáze, který sídlí mezi vlastní fyzickou vrstvou (daty) a uživatelem. Díky této vrstvě nemusí uživatel při práci s databází vědět naprosto nic o její skutečné fyzické podobě a způsobu, jakým jsou data uložena a udržována. Database application – (databázová aplikace) program, který umožňuje uživatelům přístup k datům v databázi prostřednictvím formulářů pro zadávání dat, formulářů pro zadávání dotazů a sestav

funkce DBMS základní funkce: přenášení (načítání) dat z místa jejich uložení (např. z pevného disku) do místa jejich zpracování (operační paměť) a zpět. Podmínkou efektivnosti této procedury je strukturování dat v místě jejich uložení (např. na záznamy a položky). 1. definování a redefinování dat v databázi (data definition) – organizace datových souborů (vytváření a změny datových struktur) 2. vytváření obsahu databáze – aktualizace datových souborů (vkládání dat, změny, aktualizace dat) (data transformation) 3. výběr a výstup (prezentování, zobrazování, prohlížení) dat z databáze (data presentation) 4. tvorba vstupních formulářů (obrazovek, pohledů) a výstupních sestav 5. kontrola integrity dat (data integrity) – poskytuje metodu nebo metody pro definování a zajištění správnosti dat 6. kontrola přístupových práv (určuje, kdo a jak může přistupovat k datům) (security management) 7. zpravidla obsahuje i programovací jazyk pro vytváření vlastních aplikací (programming

interface) klasické množinové operace u množinových operací (kromě kartézského součinu) se předpokládá, že obě tabulky, které do operace vstupují jako operandy, mají tyto vlastnosti: • jsou stejného stupně, tj. mají stejný počet sloupců • každý i­tý sloupec z obou tabulek je definován na stejné doméně (tj. položky jsou v obou tabulkách definovány ve stejném pořadí a jako položky stejného typu) sjednocení: vytvoří novou tabulku, která obsahuje všechny řádky obou výchozích tabulek průnik: vytvoří tabulku, která bude obsahovat pouze totožné řádky z obou relací množinový rozdíl: vytvoří tabulku, ve které budou všechny řádky první vstupní tabulky kromě těch, které se vyskytují i v druhé tabulce symetrický rozdíl: vytvoří tabulku, ve které budou všechny řádky obou tabulek s výjimkou těch, které se vyskytují v obou tabulkách kartézský součin: vytvoří novou tabulku tak, že spojuje řádky z obou tabulek systémem každý s každým. Počet sloupců výsledné tabulky je součet počtu sloupců obou vstupních tabulek. Počet řádků je součin počtu řádků obou vstupních tabulek. 3.

Návrhové fáze vývoje dtb. Systému Konceptuální návrh databáze V první fázi se prostřednictvím konzultací s uživateli a zadavateli systému formulují a shromažďují přesné požadavky na to, co vše má být v databázi uloženo. Z takto získaných informací se vytvoří konceptuální model, který je výsledkem první fáze návrhu databáze.

Konceptuální datový model popisuje data na abstraktní úrovni nezávisle na jejich fyzickém uložení. Proces tvorby konceptuálního modelu se nazývá konceptuální modelování. Jeho výsledkem je konceptuální model znázorněný jako konceptuální schéma nebo diagram, který má co nejvýstižněji zachycovat pohled člověka na danou část reálného světa. Mezi nejznámější konceptuální datové modely patří E­R model. Pro konceptuální návrh jsou podstatné tyto úkoly: ● Krok 1 – Identifikace entit (entity). ● Krok 2 – Identifikace relací (relation). ● Krok 3 – Identifikace atributů (attribute) a spojení atributů s entitami a relacemi. ● Krok 4 – Nalezení domén atributů (attribute domain). ● Krok 5 – Vyhledání unikátních identifikátorů (unique identifier).

● ●

Krok 6 – Kontrola redundance (redundancy check) v modelu. Krok 7 – Posouzení zda model podporuje uživatelské transakce.

Business pravidla (business rules) ● Business pravidla nám definují vše co se týká chování aplikace ● Tato pravidla dělíme do dvou kategorií ● Procedurální pravidla (procedural business rules) ● Tato pravidla vyžadují dodatečné programování a není možné je podchytit pomocí struktury databáze ● „Nové záznamy může provádět pouze uživatel s rolí administrátor“ ● Strukturální pravidla (structural business rules) ● Tato pravidla je možné podchytit již ve struktuře databázového modelu (pomocí klíčů, omezení, atd..) ● „Objednávku je možné vystavit pouze registrovaným uživatelům“ ● Všechna business pravidla, která není možné zahrnout do konceptuálního modelu, je třeba dokumentovat ● Pokud bychom pravidla nedokumentovali, mohlo by se na ně v průběhu vývoje zapomenout ● „Žádný zaměstnanec jehož přesčas přesáhne 10 hodin týdně, bude vyplácen 1.5 násobkem hodinové mzdy.“ ● „Zákazníci, kteří budou opožděni se splatností o více než 90 dnů, nebudou moci vytvořit novou objednávku.“ 4.

Normální formy – 0.NF

●

0.NF (nultá normální forma): Tabulka je v nulté normální formě právě tehdy, existuje­li alespoň jedno pole, které obsahuje více než jednu hodnotu. ● Příklad: uvedená tabulka je v nulté normální formě.

● ●

1.NF (první normální forma): Tabulka je v první normální formě, jestliže lze do každého pole dosadit pouze jednoduchý datový typ (atributy jsou dále nedělitelné, tzv. atomické a tentýž atribut se neopakuje ve stejné tabulce). ● Příklad: uvedenou tabulku je potřeba rozložit (dekomponovat) tak, aby splňovala podmínky pro první normální formu. Tabulka v nulté normální formě neumožňuje klást efektivní dotazy na její obsah. Naproti tomu je­li tabulka alespoň v první normální formě, je možné klást efektivní dotazy na data v ní obsažená.

●

2.NF (druhá normální forma): Tabulka je ve druhé normální formě, jestliže je v první NF

a navíc platí, že existuje klíč a všechna neklíčová pole jsou funkcí celého klíče (a tedy ne jen jeho částí). Příklad: Primární klíč tabulky je tvořen sloupci Produkt a Dodavatel. Uvážíme­li nyní neklíčový sloupec TelDodavatele, tak zjistíme, že závisí pouze na části primárního klíče – Dodavatel. Tabulka tak v této podobě druhé normální formy nedosáhne. Jedná se v podstatě o problém, že se pokoušíme v jedné tabulce zachytit dvě různé entity – Produkt a Dodavatel. Takový model povede k redundancím a následným problémům při údržbě systému. Zde lze problém řešit jednoduchým oddělením obou entit do samostatných tabulek.

●

3.NF (třetí normální forma): Tabulka je ve třetí normální formě, jestliže každý neklíčový atribut není transitivně závislý na žádném klíči schématu neboli, je­li ve druhé normální formě a zároveň neexistuje jediná závislost neklíčových sloupců tabulky. Příklad z předchozího snímku s primárním klíčem produkt však už nesplňuje 3.NF, neboť sloupec TelDodavatele je tranzitivně závislý na klíči tvořeném sloupcem Produkt prostřednictvím sloupce Dodavatel. Provedením dekompozice na tabulku Dodavatelů zajistíme splnění 3.NF.

5.

Logický návrh databáze

●

Z konceptuálního modelu se dostáváme do prostředí relačních databází ● Dosud nevíme konkrétní cílovou platformu ● Dochází ke změně terminologie

● Mapování konceptuálního modelu do logického ● Entity se stanou tabulkami ● Pojmenování z entity se změní z jednotného čísla na množné ● Atributy se stanou sloupci tabulky ● Je třeba definovat datové typy ● Relace se stanou cizími klíči

● ● ● ●

Relace se stávají cizími klíči Nepovinné relace se stávají cizími klíči, které jsou označeny jako nepovinné. Přenositelné/nepřenositelné relace je třeba řešit programově Relace se stane cizím klíč a tento cizí klíč se stane součástí primárního klíče dceřiné tabulky ● Relace M:M se musí řešit pomocí průnikové tabulky Integrita dat Integrita dat = fakt, že data věrně (tj. přesně a konzistentně) zobrazují reálný stav, který popisují. Základním předpokladem je kvalitně navržená datová základna, která omezí duplicity dat (ty jsou vždy vysokým rizikem pro vznik nekonzistencí). Důvody vzniků nekonzistencí ●  nedostatečná aktualizace dat data se stávají neaktuálními tj. nelze přidat/zrušit řádek nebo jej aktualizovat ●  vložení nesprávných hodnot Vložená data neodpovídají realitě a tím znehodnocují celkovou informační hodnotu ●  referenční integrita Při rušení řádku v jedné tabulce může nastat situace, kdy v jiné tabulce zůstanou informace, které se k tomuto řádku vztahují (odkazují) Zajištění integrity dat Integrita dat se zajišťuje vytvářením omezení. Omezení (constraint) – objekt v relační databázi, který klade určitá pravidla na vkládaná data do určitého sloupce tabulky Integritní omezení může být definováno nad jedním nebo více sloupci. Nad jedním sloupcem/sloupci může být definováno více integritních omezení. Pro každý sloupec je tedy nezbytné definovat seznam omezení která dovolí, aby mohla být zadávána data ●  pouze určitých typů (např. číselná), ●  pouze určitých délek (např. 12 znaků), ●  pouze určitých hodnot (výčet hodnot, interval, …), ●  jedinečná v rámci tabulky, ●  odkazující na jinou tabulku, ●  uvedená (NOT NULL). Realizace omezení: 1) Volba správného datového typu sloupce řeší omezení

●  pouze určitých typů (např. číselná) ●  pouze určitých délek (např. 12 znaků) 2) Kontrola vztahů s jinou tabulkou se řeší použitím cizích klíčů (FOREIGN KEY), které se odkazují na primární sloupec v odkazované tabulce 3) Kontrola jedinečnosti UNIQUE 4) Kontrola uvedení dat NOT NULL 5) Jednoznačná identifikace řádku v tabulce pomocí primárního klíče PRIMARY KEY – tj. omezení UNIQUE a NOT NULL současně 5) Kontrola vkládaných hodnot klauzulí CHECK 6) Integrita s využitím triggerů

6. Databázový jazyk SQL ● ● ● ● ● ●

Definici dat (struktury a organizace dat a vztahů mezi nimi)  Získávání dat  Manipulaci s daty  Řízení přístupu  Sdílení dat  Integrita dat

Standardní příkazy dělíme do skupin: 1. Příkazy pro definici dat (DDL) ● CREATE – vytváření nových objektů. ● ALTER – změny existujících objektů. ● DROP – odstraňování objektů. 2. Příkazy pro manipulaci s daty (DML) ● SELECT – vybírá data z databáze ● INSERT – vkládá do databáze nová data. ● UPDATE – mění data v databázi (editace). ● DELETE – odstraňuje data (záznamy) z databáze.

3. Příkazy pro řízení dat (DCL) ● GRANT – příkaz pro přidělení oprávnění uživateli k určitým objektům. ● REVOKE – příkaz pro odnětí práv uživateli. ● ALTER USER – změna uživatele ● DROP USER – odstranění  uživatele 4.  Ostatní příkazy Do této skupiny patří příkazy pro správu databáze. Pomocí nich lze nastavovat systémové

parametry (kódování znaků, způsob řazení, formáty data a času apod.). Tato skupina není standardizována a konkrétní syntaxe příkazů je závislá na databázovém systému.

Číselné datové typy (výběr) DECIMAL (, ) FLOAT () REAL () INTEGER Celé číslo v rozsahu – 2 147 483 648 až 2 147 483 647. SMALLINT Celé číslo v rozsahu – 32 768 až  32 767. Znakové datové typy CHAR () Maximální délka bývá 255 znaků. VARCHAR () Maximální délka bývá delší než u typu CHAR. Oba datové typy mohou být ve variantě s národními znaky jako NCHAR či NVARCHAR. Datové typy pro datum a čas DATE ­ Vyjádření datumu. TIME ­ Vyjádření času. TIMESTAMP ­ Struktura obsahující datum a čas. INTERVAL ­ Časové intervaly. Čas a časové razítko mohou být doplněny i o informace o časové zóně: TIMESTAMP(přesnost) WITH TIME ZONE, TIME WITH TIME ZONE Číselné datové typy NUMBER (, ) Desetinné číslo s celkovým počtem  číslic a s počtem  číslic za desetinnou čárkou. Znakové datové typy CHAR () Pevné délky, maximální délka 2000 znaků, default je 1. VARCHAR2 () Proměnlivé délky, maximální délka je 4000, default je 1. LONG Řetězec znaků proměnlivé délky, max. 2 GB. Datové typy Oracle DATE Vyjádření konkrétního datumu a času s přesností na sekundy, rozsah je od 1.1.4712 př.n.l. do 31.12.9999 TIMESTAMP ()Struktura obsahující datum a čas s požadovanou přesností INTERVAL YEAR () TO MONTH K vyjádření delšího časového intervalu. INTERVAL DAY () TO SECOND () K vyjádření relativně kratšího časového intervalu. Datové typy pro objemná data (LOB) BLOB ­ binární objekty do 4GB CLOB – textové (CHAR) objekty do 4GB NCLOB ­ textové objekty v národních sadách do 4GB

7.

Operace DDL

Create table CREATE TABLE  (<specifikace sloupce> [,  =  [, … ] ]) <specifikace sloupce> = {  [] |  } < omezení sloupce> = [CONSTRAINT  ]      { [NOT] NULL | [NOT NULL] UNIQUE | [NOT NULL] PRIMARY KEY | REFERENCES   [()] | CHECK (<podmínka>)} Omezující podmínky pro sloupce: NOT NULL  – hodnota ve sloupci nesmí být NULL UNIQUE  – jedinečnost = zakázány duplicity PRIMARY KEY – primární klíč (zakázány duplicity a NULL hodnoty) FOREIGN KEY – cizí klíč (odkazuje na sloupec jiné tabulky – podpora pro udržení integrity dat) CHECK – podmínka pro hodnoty ve sloupci (např. ID INTEGER CHECK ID BETWEEN 0 AND 100) DEFAULT – předdefinovaná hodnota (např. sloupec_1 INTEGER DEFAULT 50) Alter Table ALTER TABLE    ADD (<specifikace sloupců>) <specifikace sloupce> = {  [] |  } Příkaz se používá pro přidání sloupců či omezení do tabulky. ALTER TABLE dodavatele  ADD mesto  varchar2(50); ALTER TABLE dodavatele ADD (mesto varchar2(50), telefon varchar2(13) );

MODIFY  (<specifikace sloupce>) Příkaz se používá pro úpravu specifikace sloupců či omezení do tabulky. DROP COLUMN  Příkaz se používá pro odstranění sloupce z tabulky. RENAME COLUMN  Příkaz se používá pro přejmenování sloupce tabulky.

DROP TABLE   Odstraní z databáze tabulku s daným jménem, tj. všechny řádky tabulky, celou strukturu, všechny nad ní definované indexy a všechna případná omezení definovaná nad sloupci či celou tabulkou.

8.Omezení Klíče •Primární klíč – zvolený kandidátní klíč, který definuje n­tici

•Cizí klíč – atribut, nebo skupina atributů, která odpovídá kandidátnímu klíči v jiné nebo stejné relaci

Hodnota NULL •NULL – představuje hodnotu, která je prázdná, neplatná nebo neznámá

Unique • Jedná se o atribut nebo skupinu atributů, která jednoznačně identifikuje instanci entity mezi ostatními • Typy: –Jednoduché (simple UID) –Složené (compose UID) –Umělé (artifical UID)

–Kandidátní (cadidate UID)

­ Sekvence ­umožňují generovat jedinečné identifikátory, například hodnoty primárního klíče tabulek, ­databázová platforma Oracle používá tzv. sekvence, (v MySQL je např. volba autoincrement pro vybraný sloupec tabulky) ­při použití sekvence například pro generování primárního klíče je dobré si uvědomit, zda sekvenčně zvolený (umělý) klíč je správné řešení (někdy je vhodné použít již existující primární klíče např. z evidence obyvatel, nebo přirozené klíče). CREATE SEQUENCE seq_cyklus MINVALUE 2 MAXVALUE 5 CYCLE NOCACHE;   bude postupně generovat hodnoty 2, 3, 4, 5, 2, 3, 4, 5, 2 … CREATE SEQUENCE seq_st_inc START WITH 100 INCREMENT BY 200;   bude postupně generovat hodnoty 100, 300, 500, 700, 900, 1100, 1300

9. Operace DML ­Příkazy pro manipulaci s daty   (DML)   Data Manupation Language příkazy pro získání dat z databáze a pro jejich úpravy se označují zkráceně DML (data Manipulation Language) SELECT – vybírá data z databáze INSERT – vkládá do databáze nová data. INSERT INTO dodavatele   (dodavatel_id, nazev, zastoupeni, mesto)   VALUES (1, 'Acer','Acer Czech Republic s.r.o.','Praha 4'); UPDATE – mění data v databázi (editace).

UPDATE dodavatele SET nazev = 'HP' WHERE nazev = 'Compaq'; DELETE – odstraňuje data (záznamy) z databáze. DELETE FROM produkty   WHERE dodavatel_id = 7 or dodavatel_id > 10; EXPLAIN PLAN FOR – speciální příkaz, který zobrazuje postup zpracování SQL příkazu. Pomáhá uživateli optimalizovat příkazy tak, aby byly rychlejší.

10. Příkaz select ­•Projekce – nám říká, které sloupce budeme zobrazovat •Restrikce (selekce) – nám říká, které řádky budeme zobrazovat ­ Klauzule WHERE •Klauzule WHERE – provádí v dotazu restrikci •Rozšiřuje syntaxi příkazu SELECT na: SELECT <sloupce> FROM  [WHERE podmínka(y)] •Také se používá u příkazů INSERT, UPDATE a DELETE ­ Distinct  •DISTINCT ­ ve výsledku nebudou duplicitní řádky, které mají stejné hodnoty ve vypisovaných sloupcích • •DISTINCTROW ­ ve výsledku nebudou duplicitní řádky, které mají stejné hodnoty ve všech sloupcích bez ohledu na to, zda jsou vypisovány • •ALL – ve výsledku budou všechny řádky bez ohledu na duplicity, defaultní nastavení

­ Operátory Umožňují na základě původních hodnot vypočítat hodnoty nové. Sčítání   +   (unární i binární) Odčítání  ­  (unární i binární) Násobení  * Dělení  / Priorita •Nejprve se vyhodnocují části výrazu uzavřené v závorkách •Násobení a dělení mají přednost před sčítáním a odečítáním •Operátory se stejnou prioritou se vyhodnocují zleva doprava Umožňuje spojovat hodnoty ze sloupců a znakové řetězce Zřetězení  ||

Operátory pro porovnání =  rovnost <>  nerovnost <  menší než >  větší než <=  menší nebo rovno >=  větší nebo rovno BETWEEN x AND y   [není] větší nebo rovno x a menší nebo rovno y [NOT] IN     [ne] patří do množiny ANY, SOME  porovnání hodnoty s každou hodnotou v seznamu nebo řádkem vnořeného dotazu, musí být doplněn jedním z operátorů =, <,>, <=, >=. Výraz je pravdivý, pokud je pravdivý alespoň pro JEDNU položku seznamu. ALL  porovnání hodnoty s každou hodnotou v seznamu nebo řádkem vnořeného dotazu, musí být doplněn jedním z operátorů =, <,>, <=, >=. Výraz je pravdivý, pokud je pravdivý pro VŠECHNY položky seznamu. [NOT] EXISTS  ve vnořeném dotazu je vrácen alespoň jeden řádek IS [NOT] NULL   test na [ne] rovnost NULL X [NOT] LIKE y  porovnání řetězce s maskou obsahující zástupné znaky   _    právě jeden libovolný znak   %   nula nebo více libovolných znaků

­ Logické operátory a hodnota NULL Všechny logické operátory mají stejnou prioritu. AND ­ && OR ­ | | NOT ­ ! Použití : v definicích za WHERE.Konkrétní příklad: Chceme vybrat sloupec skladem a hledáme ve sloupci typ 'šroubky', které mají ve sloupci velikost hodnotu 7 .Použijeme následující příkaz: SELECT skladem WHERE typ = 'šroubky' AND velikost ='7' anebo : SELECT skladem WHERE typ = 'šroubky' && velikost = '7'

11.Spojení tabulek Samospojení (self–join, recursive join): spojení, v němž jsou záznamy z tabulky kombinovány s jinými záznamy z téže tabulky Kartézský součin (cartesian product­join): Spojení bez podmínek. Záznamy z tabulek jsou kombinovány stylem každý s každým. Výsledná množina obsahuje MxN záznamů. Varianta kartézského součinu od Oracle

je cross­join. Přirozené spojení (natural­join): Spojení, kde si systém sám najde nejvhodnější sloupce, přes které bude spojovat

Vnitřní spojení INNER INNER JOIN, JOIN Do výsledku budou zahrnuty pouze ty řádky, pro které byla nalezena odpovídající hodnota v druhé tabulce. Příklady využití vnějších spojení: ­Zobrazení všech dodavatelů, tedy i těch, kteří nedodávají žádný výrobek ­ ­Zobrazení všech výrobků, tedy i těch, které nejsou přiřazeny žádnému dodavateli ­ ­S využitím agregačních funkcí zobrazení počtu výrobků, které dodávají jednotliví dodavatelé (s vnějším spojením se zobrazí i hodnoty 0 u těch dodavatelů, kteří žádný výrobek nedodávají, v případě vnitřního spojení ve výsledku nebudou vůbec zahnuti) Vnější spojení OUTER Ve výsledku budou i ty řádky, pro které nebyly nalezeny odpovídající hodnoty v druhé tabulce. ­ ­Pravé (RIGHT JOIN) .. Ve výsledku budou všechny řádky z pravé (druhé tabulky). Nebyl­li nalezen aspoň jeden odpovídající řádek v levé tabulce, budou ve výsledku hodnoty NULL ve všech sloupcích z první tabulky. ­ ­Levé (LEFT JOIN) .. Ve výsledku budou všechny řádky z levé (první tabulky). Nebyl­li nalezen aspoň jeden odpovídající řádek v pravé tabulce, budou ve výsledku hodnoty NULL ve všech sloupcích z druhé tabulky. ­ Úplné (FULL JOIN) .. Ve výsledku budou všechny řádky z levé i pravé (první i druhé tabulky).

­ Aliasy Tabulkové aliasy V řadě případů je dobré (nebo dokonce nutné) nahradit název tabulky aliasem – tedy zpravidla kratším názvem, což může být i jedno písmeno. Kromě toho, že je to kratší, daleko snadněji se pak změní název tabulky, protože to stačí upravit jen na jednom místě. V neposlední řadě pak může být zápis dotazu přehlednější, protože se nemusí stále opakovat (třeba i) dlouhý název tabulky. Vezměme tento příklad, kdy se obě tabulky několikrát v dotazu opakují. SELECT SUM(Objednavky.Castka), Ulice FROM Objednavky   INNER JOIN Zakaznici ON Objednavky.IdZakaznika = Zakaznici.ID   WHERE Zakaznici.Mesto = "Praha 8"   GROUP BY Zakaznici.Ulice

A takto to bude vypadat při použití aliasů – za název tabulky uvedeme jeho alias a dále se pak na něj odkazujeme. SELECT SUM(o.Castka), Ulice FROM Objednavky o   INNER JOIN Zakaznici z ON o.IdZakaznika = z.ID   WHERE z.Mesto = "Praha 8"   GROUP BY z.Ulice V případě, že bude třeba dotaz upravit na tabulku zahraniční objednávky, bude stačit jedna jediná změna: SELECT SUM(o.Castka), Ulice FROM ZahranicniObjednavky o   INNER JOIN Zakaznici z ON o.IdZakaznika = z.ID   WHERE z.Mesto = "Praha 8"   GROUP BY z.Ulice Sloupcové aliasy Jiné pojmenování sloupce je velmi užitečné při používání agregačních funkcí. Sloupec s výsledkem takové funkce může být pojmenován třeba Expr1000, což není zrovna pěkné. Pokud použijete dokonce funkcí více, stává se takový výsledek velmi nepřehledný.

Při použití aliasů je výsledek mnohem lepší. SELECT MIN(Castka) AS Minimum, MAX(Castka) AS Maximum, AVG(Castka) AS Prumer, STDEV(Castka) AS VybSmerOdch FROM Objednavky

Je třeba dát pozor ale na jednu trochu nepříjemnou věc – sloupcový alias nelze použít (na rozdíl od tabulkového) na dalších místech dotazu. Řazení výsledku dotazu dle oříznutého názvu takto realizovat nelze. SELECT TRIM(Nazev) AS OriznutyNazev FROM Produkty ORDER BY OriznutyNazev A musí se bohužel znovu zopakovat celá funkce. SELECT TRIM(Nazev) AS OriznutyNazev FROM Produkty ORDER BY TRIM(Nazev) Je tu ovšem jedna výjimka, a to v případě, že je alias použit v poddotazu. SELECT IdZakaznika, Soucet FROM (SELECT IdZakaznika, SUM(Castka) AS Soucet FROM Objednavky GROUP BY IdZakaznika) WHERE Soucet > 2000

Vysvětlení toho, proč to zde funguje, je snadné. Alias reálně vzniká až při vykonání dotazu. Zde se tedy provede nejprve vnitřní dotaz, který „vytvoří“ alias a ten pak existuje i pro vnější hlavní dotaz. V předchozím příkladu toto nebylo možné – chtěli jsme řadit podle něčeho, co ještě neexistuje.

12. Funkce Funkce jsou programové bloky, které provádějí požadované operace například s číselnými, znakovými či datovými hodnotami. Seznam lze najít např. na: http://www.techonthenet.com/oracle/functions/index.php Obecně je můžeme rozdělit: a) Jednořádkové funkce •Analytické •Konverzní Bin_To_Num(posloupnost 0 a 1 oddělená čárkami)   převod binárního čísla na číslo desítkové soustavy To_Char(řet)     převádí řetězec do standardní znakové sady To_Char(number [, formátovací_řetězec, nsl_par])    převádí datový typ NUMBER na typ VARCHAR2 To_Char(datetime [, formátovací_řetězec, nsl_par])   převádí datové typy na typ VARCHAR2 To_Date(řet, formátovací_řetězec [, nsl_par])   převádí textové datové typy na datový typ DATE To_Number(řet, formátovací_řetězec [, nsl_par])   převádí datový typ VARCHAR2 na číselný typ NUMBER nsl_par … parametr určující národní formát pro zápis data a času, čísel .. •Funkce pro práci s datem a časem •Matematické •Funkce pro práci s hodnotou NULL •Řetězcové funkce

•Objektové funkce •Funkce pro práci s formátem XML • b) Agregační funkce agregační_funkce ::= { COUNT | SUM | AVG | MIN | MAX } ([ ALL | DISTINCT ] výraz) | COUNT(*)

13. Agregační funkce Agregační funkce pracují nad množinou řádků, přičemž vrací právě jeden výsledek pro celou vstupní množinu dat. Tyto funkce pomocí matematických a statistických operací zpracovávají agregované hodnoty z celých sloupců (není­li uvedeno jinak v podmínce). ­AVG ([DISTINCT] [ALL] výraz)  aritmetický průměr ­ ­COUNT ([DISTINCT] [ALL] výraz)  počet hodnot ­ ­MAX (výraz)  maximum ­ ­MIN (výraz)  minimum ­ ­SUM ([DISTINCT] [ALL] výraz)  součet hodnot ­ Skupinové dotazy Na rozdíl od souhrnných dotazů dochází k výpočtu agregačních funkcí pro konkrétní skupiny řádků. Nevzniká tedy na výstupu jeden jediný řádek, ale řádky pro jednotlivé skupiny. ­Kolik zaměstnanců mají jednotlivá oddělení společnosti ? ­Jaké jsou tržby poboček společnosti ? ­Kolik lidí jede průměrně v jednom vlaku na dané trase ? ­Jaká je průměrná délka života pro muže a ženy ? ­Jaká je průměrná mzda v různých sektorech ? ­Kolik vstupenek se prodalo na jednotlivá utkání ? ­Jaká je nejvyšší a nejnižší vyplácená sociální dálka v krajích ? ­Kdo získal nejméně bodů z testu v daném předmětu ? SELECT <sloupec_1>, <sloupec_2>, ... <sloupec_n>, FROM <seznam tabulek> WHERE <podmínky> GROUP BY <sloupec_1>, <sloupec_2>,  ... <sloupec_n>; V předchozích příkladech jsme vyhodnocovali hodnoty v celých sloupcích, nyní budeme vytvářet mezisoučty, které budou vztahovat k jednotlivým skupinám. Pod pojmem skupina můžeme chápat konkrétní pobočku společnosti, studovaný předmět, město, osobu, …

Všechny sloupce, které nejsou agregovány a jsou uvedeny za klíčovým slovem SELECT (definují skupiny, pro něž dochází k výpočtu agregačních funkcí) musí být uvedeny i za klauzulí GROUP BY. Dotazy obsahující klauzuli GROUP BY označujeme jako skupinové dotazy, neboť seskupují data ze zdrojových tabulek do skupin a pro každou z těchto skupin vytváří jediný souhrnný řádek. Sloupce uváděné za klauzulí GROUP BY označujeme jako seskupující sloupce.

­ Souhrnné dotazy Velice často nás nezajímají jednotlivé detaily uložené v konkrétních řádcích v jednotlivých tabulkách, ale několik málo souhrnných informací, které sumarizují obsah databáze, například: SELECT AVG(cena) FROM produkty;   průměrná hodnota ve sloupci cena v tabulce produkty,   počítají se jen z uvedených hodnot – tj. NOT NULL   SELECT MIN(cena) FROM produkty;   minimální (uvedená) hodnota ve sloupci cena v tabulce produkty   SELECT count(cena) FROM produkty;     počet nenulových hodnot ve sloupci cena v tabulce produkty   SELECT count(*) FROM produkty;   počet řádků tabulky produkty

14.Vnořené dotazy •Jednořádkové jednosloupcové vnořené dotazy vrací jen jednu hodnotu ● •Jednořádkové vícesloupcové vnořené dotazy vrací jen jeden řádek, ale více sloupců (například jeden řádek tabulky nebo výsledek souhrnného dotazu) •Víceřádkové jednosloupcové vnořené dotazy   vrací více řádků, ale jen jediný sloupec (například seznam všech různých hodnot v konkrétním sloupci) •Vícesloupcové víceřádkové vnořené dotazy   výsledkem je tabulka s více sloupci a více řádky (obecný stav) Vnořené dotazy musí být zapsány v závorkách:  (SELECT ….) Nejdříve se provede vnořený dotaz a teprve nad jeho výsledky se aplikuje hlavní dotaz. Vnořený dotaz může být umístěn do hlavního dotazu například: •za klauzuli FROM •za klauzuli WHERE

•za klauzuli HAVING •případně v dalších částech (i v části bezprostředně za SELECT)

­ Korelované x nekorelované Jedná se o poddotazy obsahující hodnotu vnějšího dotazu. Tato hodnota je dostupná pomocí aliasu. Nekorelovaný poddotaz je na vnějším dotazu nezávislý – vyhodnocuje se pouze jednou. Korelovaný poddotaz se vyhodnocuje opakovaně pro každý řádek vnějšího dotazu ­ Množinové operátory Existují situace, kdy potřebujeme spojit dohromady výsledky z více jak jednoho dotazu. Pro tuto situaci poskytuje jazyk SQL množinové operátory. Každý výsledek dotazu můžeme chápat jako množinu a na tuto množinu můžeme aplikovat operátory, díky kterým dosáhneme požadovaných výsledků. Množinové operátory nám pomohou řešit následující problémy: •Získat řádky z výsledků dvou a více dotazů. •Získat takové řádky z dotazu, které se nevyskytují ve výsledku jiného dotazu. •Získat řádky, které se vyskytují ve výsledcích všech dotazů. •Získat řádky z výsledku dotazu, které se nevyskytují ve výsledku dotazu jiného. K řešení těchto problémů disponuje jazyk SQL těmito množinovými operátory: •UNION ALL •UNION •MINUS •INTERSECT

15. Index Vlastnosti: ­ ­zrychlení jen tehdy, pokud potřebujeme najít několik záznamů, pokud je cílem najít více než 5% záznamů, bude obvykle rychlejší hledání bez použití indexu, ­ ­zvýšení režie při vkládání a změně dat (INSERT, UPDATE, DELETE), protože kromě zápisu do tabulky je třeba zapsat indexový údaj na přesně definované místo v indexu, ­ ­nedoporučuje se používat indexy (kromě bitmapových indexů) nad tabulkami obsahujícími malé množství řádků (řádově desítky) nebo nad sloupci s nízkou variabilitou (např. sloupec pohlaví žena/muž), ­ odstraněním indexu zůstane tabulka nezměněná.

­ Použití, vytvoření Vytvoří index nad uvedeným sloupcem definované tabulky. CREATE [BITMAP] INDEX  ON  () ­ BITMAP  vytvoří bitmapový index ­ vhodné řešení pro sloupce s nízkou variabilitou. Lze si to představit tak, že každé hodnotě ve sloupci odpovídá jeden bit kódu,    tj. pro pohlaví jsou nutné 2 bity (muž, žena), pro rodinný stav 4 bity (svobodný, ženatý, rozvedený, vdovec). Příklad:   CREATE INDEX ix_uc_prijmeni ON ucitele(prijmeni);   CREATE INDEX ix_st_prijmeni_jmeno ON studenti (prijmeni, jmeno);   CREATE BITMAP INDEX ix_pohlavi ON ucitele(pohlavi);

­ Metody prohledávání ­Přímý přístup pomocí adresy ROWID nebo algoritmu hash Typické použití pro přístup k tabulce po jejím prohledání pomocí některého typu indexu při použití dotazu typu:   SELECT  *  FROM nazev_tabulky WHERE indexovany_sloupec = hodnota; Databáze provede tyto kroky: 1.Prohledá index u indexovaného sloupce 2.Načte hodnotu ROWID řádku, na který odkazuje 3.Podle hodnoty ROWID přistoupí k tabulce a načte konkrétní řádek 4.Kroky 2) a 3) opakuje, pokud více řádku splňuje danou podmínku (v případě, kdy indexovaný sloupec není unikátní) Použití tohoto přístupu lze i přímo z dotazu, pokud známe ROWID řádku, příklad:   SELECT * FROM studenti WHERE ROWID='AAAVlyAAFAAAGQ3AAG' ; ROWID lze zjistit například příkazem : SELECT ROWID FROM studenti; ­Indexové přístupy (např. prohledávání indexu) ­Jde pravděpodobně o nejoblíbenější přístupovou metodu ­Bloky na nejnižší úrovni stromu (listy) obsahují všechny indexované klíče a adresu ROWID, odkazující na konkrétní řádek ­Vnitřní bloky (větví) slouží k navigaci strukturou indexu ­Listové uzly indexu jsou ve skutečnosti dvojitě propojeným seznamem. Jakmile zjistíme, kde začít, je prohledání seřazených hodnot (prohledání rozsahů indexů) snadné (lze pokračovat v dalších listech, dokud nenarazíme na hodnotu vyšší než zadaná) ­Všechny listové bloky by měly být na stejné úrovni stromu, ­Většina indexů bude mít výšku 2 až 3, tj. pro vyhledání klíče bude třeba 2 nebo 3 vstupně­výstupních operací ­Úplné prohledávání (nutný průchod všemi řádky tabulky)

­ Bitmapový index Klasické použití bitmapových indexů, jak již bylo uvedeno v úvodu, spočívá v jejich nasazení v úlohách zpracovávajících velká množství statických dat. Výhodou bitmapových indexů je to, že na rozdíl od klasických indexů je není třeba neindexovat. V praxi je důležitým kritériem při rozhodování, zda v určité situaci použít klasické indexování (textové) nebo indexování bitmapové, četnost výskytu různých hodnot dané vlastnosti/sloupce v tabulce. Pro každou různou hodnotu vlastnosti se totiž vytváří nové pole hodnot true/false. Mějme například tabulku Osoba se sloupci RodneCislo, Jmeno, Prijmeni a Pohlavi. Z definice tabulky je zřejmé, že rodné číslo bude pro každou osobu jiné (odhlédneme-li pro zjednodušení od duplicit). Jméno a Příjmení bude mít poměrně velký rozptyl hodnot, avšak pohlaví bude nabývat pouze hodnot M nebo F. Z toho plyne že indexovat bitmapově má smysl pouze nad sloupcem Pohlavi. V tomto případě se bez ohledu na počet záznamů v tabulce vytvoří pouze dvě bitmapová pole, jedno pro podmínku "osoba je M(ale)", druhé pro podmínku "osoba je F(emale)".

Tabulka Osoba Další výhodou bitmapových indexů je možnost jejich efektivního komprimování. V praxi totiž dochází k velice častému opakování dlouhých úseků složených buď z binárních nul nebo jedniček. Oproti textovým indexům, kde každý znak je reprezentován buď jedním nebo dvěma bajty (slovy) se každých N binárních jedniček či nul převede na jeden bajt.