Kaºd∞ jen trochu chytr∞ nepo²ádník okamºit╪ odpoví své hrdé ANO. Navíc na p²ilepτenou dodá, pro koho je ten po²ádek urƒen. Pokud by byl opravdu chytr∞, bude rad╪ji mlƒet, nebo£ tím podle psychologà deklaruje svàj vysok∞ individualismus, a nikoli vysoké IQ.
Zvládají inteligenti chaos?
Posadíte-li do jedné kancelá²e nebo k jednomu projektu dva chytré zastánce chaosu na n╪kolik dnà, ani jeden z nich nebude hovo²it o ráji. Podobná situace vzniká i na SQL serveru. Pokud jsme jedin∞m individuem, které aktualizuje data, a vybiƒujeme své duτevní schopnosti na maximum, poda²í se nám udrºet v datech po²ádek a nikdo zvenku nepozná, ºe máme nepo²ádek ve struktu²e tabulek. Jednou vτak realizátora databáze màºe p²estat po²izování dat bavit, jeho chaos se dostane do rukou jin∞ch jedincà, kte²í ho pak uº nikdy nezvládnou, a to tím spíτ, ƒím jich bude více.
V p²edchozích dílech seriálu jsem se p²íliτ nestaral o zvládání chaosu, nebo£ v SQL DML k tomu nebyla p²íleºitost a v∞klad DDL jsem trochu odlehƒil pro snadn╪jτí porozum╪ní. Nyní nastal ƒas tvrdého zásahu proti chaosu v datech. Hlavní smysl takového poƒínání je v tom, aby s daty mohli pracovat i ti lidé, kte²í chaos prost╪ nezvládají. Vedlejτí efekty jsou t²i. Zabráníme p²edem rozvleklému reklamaƒnímu ²ízení p²i p²edávání systému. Dále zabráníme t╪m uºivatelàm systému, kte²í by cht╪li na naτem chaosu vyd╪lat prachy, aby tak uƒinili okamºit╪. V neposlední ²ad╪ tím p²evychováme svou chaotickou analytickou duτi.
SQL server se brání
A proto je tak prima, jak se to zpívá ve známé písni. V databázové teorii nalezneme základní definici databázového systému jako mnoºiny entit (tabulek) a integritních omezení. Integrita po ƒesku je celistvost neboli schopnost tvo²it jeden bezesporn∞ celek. Pokud má SQL server souƒasn╪ slouºit více klientàm, musí bu╘ vτem dovolit vτechno (a tak umoºnit dokonal∞ chaos), nebo odmítat vτechny rozporuplné poºadavky na zm╪nu dat (a tak tvrd╪ ochromit práci vτech bordelá²à). SQL server má obecné moºnosti ochrany celistvosti dat a vy je budete muset v souladu se svou anal∞zou konkrétn╪ naplnit. P²itom riskujete, ºe vás okolí bude kamenovat za to, ºe nelze vydat zboºí, které není na sklad╪, ºe nelze poslat dopis firm╪, jejíº adresa není známa, nebo ºe peníze nejdou poslat na úƒet, jehoº majitel není znám. Nebojte se jich. Stejní lidé vás odsoudí i v p²ípad╪, ºe se poda²í zadat prodej milionu kusà nákladního automobilu, ºe budou muset psát adresu podruhé i kvàli faktu²e nebo ºe dolary zmizí t²eba do Alp. Navíc bych se k nim v druhém p²ípad╪ p²idal i já. Pokud tedy plánujete, co vτechno chcete zakázat, nezabouchn╪te sami sob╪ zadní vrátka. Proto plánujte vºdy minimální poƒet integritních omezení, kter∞ jiº umoºσuje obranu proti chaosu. Bude--li omezení p²íliτ mnoho, bude v nich moºná také chaos, kter∞ neunesete. Lepτí heslo by moºná bylo: Chaos v SQL màºe vzniknout i bez integritních omezení - rozumn∞ analytik drºí nejen uºivatele, ale i sám sebe na uzd╪.
Doménová integrita
Doménovou integritu uº napàl znáte z CREATE TABLE. U kaºdého sloupce tabulky se màºeme vyjád²it o domén╪ (mnoºin╪ dovolen∞ch hodnot), v níº se musí nalézat zadávané hodnoty. Pokud JMENO nesmí b∞t NULL, CISLO_BOT nesmí b∞t menτí neº 40 a OKRES màºe b∞t pouze DC, UL nebo TP, staƒí napsat uvnit² p²íkazu CREATE TABLE t²i vnit²ní deklarace sloupcà:
JMENO VARCHAR(30) NOT NULL,
CISLO_BOT DECIMAL(2,0)
CHECK (CISLO_BOT>=40),
OKRES VARCHAR(2)
CHECK (SPZ IN("DC","UL","TP"));
Jen si uv╪domte, v kolika tabulkách se màºe vyskytnout rodné ƒíslo s povinn∞m lomítkem a nepovinnou poslední cifrou. Proto je lepτí nejprve vytvo²it tzv. doménu a v ní na jednom míst╪ hlídat pravidla zápisu. Na takovou doménu se pak màºeme odvolávat jejím jménem jako na nov∞ datov∞ typ, kter∞ vznikl omezením jiného datového typu. Jedin∞ háƒek je v tom, ºe p²i vytvá²ení domény nevíme, jak se budou jmenovat sloupce p²i konkrétním a rozmanitém pouºití v CREATE TABLE. Podobn╪ jako n╪kte²í muºi ²íkají vτem ºenám BERUµKO, aby se to nepletlo, budeme uvnit² domény naz∞vat její sloupec jako VALUE. Následuje vytvo²ení n╪kolika jednoduch∞ch domén:
CREATE DOMAIN SLOVO
AS VARCHAR(30) NOT NULL;
CREATE DOMAIN RODNE
AS VARCHAR(11)
CHECK (VALUE LIKE "______/___%");
CREATE DOMAIN NAZEV AS SLOVO CHECK (VALUE NOT LIKE " %");
CREATE DOMAIN MERITKO
AS DECIMAL(3,0) CHECK
(VALUE BETWEEN 30 AND 300);
První doména SLOVO je urƒena pro hlídání maximáln╪ t²icetiznakov∞ch neprázdn∞ch slov. Druhá doména RODNE hlídá jedenáctiznakov∞ text, zda po τesti znacích následuje lomítko a za ním t²i nebo ƒty²i znaky. Z toho automaticky plyne, ºe hodnota VALUE nemàºe b∞t NULL, nebo£ v∞sledek operátoru LIKE by nebyl YES. T²etí doména NAZEV vychází z domény SLOVO a up²esσuje, ºe VALUE nesmí zaƒínat mezerou. Podobn∞ trik se vám bude velmi ƒasto hodit. Poslední doména MERITKO bude prosp╪τná p²i ukládání hodnot v∞τky lidí v centimetrech. Máme-li domény p²ipraveny, staƒí je struƒn╪ a p²ehledn╪ pouºít p²i zajiτ£ování doménové integrity a zároveσ ke struƒn╪jτímu vytvá²ení tabulky. Následující tabulka CLOVEK sice jeτt╪ nemá klíƒ, ale jeτt╪ chvíli vydrºte do v∞kladu entitní integrity:
CREATE TABLE CLOVEK
(JMENO NAZEV, PRIJMENI NAZEV,
RC RODNE, VYSKA MERITKO);
Pokud n╪jakou doménu nepot²ebujeme, màºeme ji zlikvidovat p²íkazem
DROP DOMAIN MERITKO;
Také vám vadí, ºe n╪kte²í lidé mají stejné k²estní jméno jako p²íjmení? V království EXTRA s tím zatoƒili hned od zaƒátku, a pak si marn╪ lámali hlavu, jak pomocí domény, která zná jen tu svou VALUE, porovnat hodnoty ve dvou ràzn∞ch sloupcích. Pomocí domén to nejde. Druh∞ zpàsob realizace doménové integrity v SQL DDL spoƒívá v uvedení podmínky CHECK uvnit² CREATE TABLE, ale aº za definicí vτech sloupcà tabulky. Obecn╪ totiº platí, ºe integritní omezení, která nebyla napsána p²ímo p²i definici sloupcà, lze psát aº na konec p²íkazu CREATE TABLE. T∞ká se to nejen CHECK, ale i PRIMARY KEY, UNIQUE a FOREIGN KEY. Problém z EXTRA království lze ²eτit následovn╪:
CREATE TABLE EXTRA
(JMENO NAZEV, PRIJMENI NAZEV,
CHECK (JMENO<>PRIJMENI));
Entitní integrita
Entitní integritu také známe z poƒáteƒních dílà seriálu. Chceme-li zajistit jednoznaƒn∞ p²ístup k ²ádku v rámci jedné entity (tabulky), musí existovat jednoduch∞ nebo sloºen∞ klíƒ jako jeden sloupec tabulky nebo skupina sloupcà, které nab∞vají unikátních hodnot. P²i definici sloupce RC, kter∞ má b∞t unikátní, v tabulce CLOVEK staƒí kombinovat doménovou a entitní integritu uvnit² p²íkazu CREATE TABLE:
RC RODNE UNIQUE;
Pokud si vzpomeneme na unikátnost dodateƒn╪, umíme vytvo²it unikátní indexov∞ soubor p²íkazem
CREATE UNIQUE INDEX RCCLO
ON CLOVEK(RC);
Unikátní klíƒ má pro entitu (tabulku) dvojí v∞znam. Prvotní v∞znam pro tabulku v 5NF je v p²ímém p²ístupu k datàm. Pak téº hovo²íme o primárním klíƒi, anglicky PRIMARY KEY. Kaºdá tabulka v 5NF musí mít práv╪ jeden primární klíƒ. Druh∞ v∞znam unikátních klíƒà je v kontrolní funkci dalτích souvislostí mezi hodnotami v ràzn∞ch ²ádcích jedné tabulky. Pak pouºíváme tradiƒní název UNIQUE. Takov∞ch unikátních klíƒà màºe mít tabulka n╪kolik, tedy i ºádn∞. Pokud by v království EXTRA pouºívali kombinaci jména a p²íjmení k jednoznaƒné identifikaci osob, staƒí v CREATE TABLE p²idat PRIMARY KEY:
CREATE TABLE EXTRA
(JMENO NAZEV, PRIJMENI NAZEV,
CHECK (JMENO<>PRIJMENI),
PRIMARY KEY (JMENO, PRIJMENI));
V ²íτi SUPER musel mít jeτt╪ kaºd∞ obƒan tajnou p²ezdívku, nesouvisející ani se jménem, ani s p²íjmením, a navíc kombinace k²estního jména a p²ezdívky musela vést k jednoznaƒnému oslovení agenta. Zde proto vyuºijeme jak doménu NAZEV, tak t²i p²ídavné doménové integrity, jeden primární klíƒ a jedno unikátní omezení. Jde o skloubení doménové a entitní integrity:
CREATE TABLE SUPER
(JMENO NAZEV, PRIJMENI NAZEV,
PREZDIVKA NAZEV,
CHECK (JMENO<>PRIJMENI),
CHECK (JMENO<>PREZDIVKA),
CHECK (PREZDIVKA<>PRIJMENI),
PRIMARY KEY (JMENO, PRIJMENI),
UNIQUE (JMENO, PREZDIVKA));
Referenƒní integrita
Tabulka SUPER je evidentn╪ ƒíselníkem, kter∞ se na nic neodkazuje. Jinak tomu bude s tabulkou MZDA, ve které plánujeme sloupec RC z domény RODNE, jehoº hodnoty musí b∞t ve vztahu k hodnotám sloupce RC z domény RODNE v tabulce JEDINEC, která má RC jako PRIMARY KEY. Nejde o nic jiného neº o realizaci relace A : 1 MZDA PRO CLOVEKa. Tabulka JEDINEC je dokonalou databází zam╪stnancà v 5NF s pot²ebn∞mi osobními údaji. Tabulka MZDA, obsahující pouze rodné ƒíslo, m╪síc, rok a mzdové údaje, se pomocí rodného ƒísla spojuje s cizí tabulkou JEDINEC, na kterou se odkazuje. Práv╪ p²edstav╪ o omezeních p²i spojení tabulek se ²íká referenƒní integrita. Omezení tohoto typu pomáhá zajistit nekonfliktnost obsahu dvojice tabulek v rámci kaºdé jednotlivé relace. Nejlépe bude formulovat nejp²ísn╪jτí moºnou p²edstavu o nespornosti obsahu tabulek MZDA a JEDINEC. P²edevτím je zcela zbyteƒné hlídat referenƒní integritou ruτení jednotliv∞ch mezd, nebo£ tím, ºe n╪kdo n╪kdy nedostane mzdu, nevznikne rozpor v databázi. P²idávání nového zam╪stnance do tabulky JEDINEC staƒí hlídat pomocí doménové a entitní integrity a referenƒní integrita nehraje roli. P²idávání nové poloºky do tabulky MZDA je hlídáno referenƒní integritou. Existuje-li stejné rodné ƒíslo v tabulce JEDINEC, prob╪hne p²idání ²ádku bez problémà. Pokud stejné rodné ƒíslo v tabulce JEDINEC neexistuje, referenƒní integrita je povinna zajistit, aby nedoτlo ke sporu v datech. Z toho plyne, ºe nep²idá pom∞lenou poloºku do tabulky MZDA.
To je uºiteƒné pro zamezení v∞platy mrtv∞m duτím. Opaƒná situace nastává p²i ruτení zam╪stnancà v tabulce JEDINEC. Referenƒní integrita op╪t pomáhá hlídat následky naτeho poƒínání. Ruτíme-li zam╪stnance, kter∞ jeτt╪ nikdy nedostal v∞platu, je situace jasná a zruτení v tabulce JEDINEC prob╪hne. Na ruτení zam╪stnance, kter∞ jiº v∞platu alespoσ jednou dostal, màºeme nahlíºet ràzn╪ extrémn╪. Nejtvrdτí je takov∞ postup zakázat. Jedin╪ tak lze zamezit anonymit╪ p²íjmà b∞val∞ch zam╪stnancà. O dalτích mírn╪jτích moºnostech bude pojednáno dále. Na opravu rodného ƒísla v tabulce JEDINEC nebo v tabulce MZDA musíme pohlíºet jako na kombinaci ruτení a vytvá²ení. Zm╪na rodného ƒísla v tabulce JEDINEC je moºná, pokud dotyƒn∞ jeτt╪ nikdy nic nebral. Zm╪na rodného ƒísla v tabulce MZDA je moºná, pokud nové rodné ƒíslo existuje v tabulce JEDINEC. Realizace takto p²ísné referenƒní integrity se provede p²i vytvo²ení tabulky MZDA, kde na konec p²idáme
FOREIGN KEY(RC)
REFERENCES JEDINEC(RC);
Tím je definován vztah mezi sloupcem RC z naτí tabulky MZDA a sloupcem RC z cizí tabulky JEDINEC; jde o nejtvrdτí formu referenƒní integrity. Je-li v cizí tabulce UCET tvo²en primární klíƒ sloupci CU a CODE, p²edstavujícími ƒíslo úƒtu a kód banky, pak se z tabulky STAV, obsahující CUCTU, BANKA, DATUM, PRIJEM a VYDEJ, musíme odkazovat deklarací v posledním ²ádku CREATE TABLE:
FOREIGN KEY(CUCTU,BANKA)
REFERENCES UCET(CU,CODE);
Extrémní tvrdost není vºdy na míst╪. Co kdyº n╪komu spletou rodné ƒíslo v tabulce JEDINEC a zjistí se to aº po mnoha m╪sících vyplácení mzdy? Pak poºadavek zákazu zm╪ny RC v tabulce JEDINEC asi prakticky neobstojí. Lépe je si p²át, aby se to vτude p²eƒíslovalo samo. Následující deklarace v tabulce MZDA ji ƒiní mírn╪ nesvéprávnou, nebo£ zm╪na RC v cizí tabulce JEDINEC v ní nedobrovoln╪ vynutí zm╪ny RC. K tomu slouºí klauzule ON UPDATE CASCADE:
FOREIGN KEY(RC) REFERENCES
JEDINEC(RC) ON UPDATE CASCADE;
Pokud budeme jeτt╪ benevolentn╪jτí, je na míst╪ p²em∞τlet, jak umoºnit ruτení poloºek v tabulce JEDINEC. Tak t²eba firma v tabulce ZAKAZKA vede i RC zam╪stnance, kter∞ za ni odpovídá, a màºe se stát, ºe pracovník dá v∞pov╪╘. Pak zruτení pracovníka màºe b∞t dovoleno s tím, ºe na zakázce bude uvedena místo rodného ƒísla hodnota NULL. Tím zahladíme stopy po zam╪stnanci, ale jeho zakázky se neztratí. To se vyplatí i kvàli moºnosti vést evidenci zakázek, které visí ve vzduchu. Do tabulky ZAKAZKA musíme tedy napsat odkaz na tabulku JEDINEC, a to s dv╪ma klauzulemi ON UPDATE CASCADE a ON DELETE NULL:
FOREIGN KEY(RC) REFERENCES
JEDINEC(RC) ON UPDATE CASCADE ON DELETE NULL;
Nestojíme-li o zakázky, které zam╪stnanec m╪l na krku, provedeme i ruτení kaskádn╪:
FOREIGN KEY(RC) REFERENCES
JEDINEC(RC) ON UPDATE CASCADE ON DELETE CASCADE;
Oznamka - mal∞ projekt
Seriál o SQL se blíºí ke svému rozuzlení. Proto by neτkodilo zab∞vat se aº do konce seriálu jednou názornou úlohou od stadia anal∞zy p²es domény, tabulky, integrity a view aº po uloºené procedury, které budou p²edm╪tem jeho poslední ƒásti. Seznamku neboli seznamovací kancelá² analyzovat nebudeme. Mnohem více se toho dá nauƒit anal∞zou "oznamky", tj. instituce, kam se chodí splσovat ƒestná oznamovací povinnost za úplatu. Cílem mé anal∞zy je p²esv╪dƒit vás, ºe jádro oznamky tvo²í t²i tabulky: CLOVEK, CIN a UDANI. Pokud vám chybí tabulka UDAVAC, pak asi nevíte, ºe udavaƒ je také jenom (bohuºel) ƒlov╪k a lidé se mohou udávat vzájemn╪ u jedné a téºe instituce. Bylo by analytickou chybou to nedovolit. Kaºd∞ typ ƒinu má svàj název a cenu pro p²ípad b╪ºného udání. Udá-li konkrétní osoba jinou konkrétní osobu, ºe v urƒit∞ den spáchala sledování hodn∞ ƒin, màºe dostat i vyττí odm╪nu, jde--li o prominentního udavaƒe nebo prominentní ob╪£ udání. Z toho potom plyne, ºe u kaºdého ƒlov╪ka musí b∞t znám jeho koeficient udavaƒe a koeficient ob╪ti jako ƒísla v╪tτí nebo rovná jedné. Jedniƒky by se m╪ly samy doplσovat v p²ípad╪ neznám∞ch koeficientà. Odm╪na za jedno konkrétní udání je rovna souƒinu koeficientu udavaƒe, koeficientu ob╪ti a základní ceny ƒinu. Celková m╪síƒní odm╪na se t∞ká pouze udavaƒà a je rovna souƒtu cen za m╪síc. U kaºdé ob╪ti se naopak sleduje intenzita sledování ve finanƒních jednotkách. V∞konnost agentà, mohutnost ƒinà ƒi sledovanost ob╪tí lze sledovat nejen po stránce ekonomické, ale i statistické. Existují vτak n╪která zásadní omezení oznamovacího reºimu. Nikdo nesmí udávat sám sebe a navíc nesmí v jednom dni udat jinou konkrétní osobu v souvislosti s jedním typem ƒinu víc neº jedenkrát. Anonymní udání, udání neznámé osoby a udání nespecifikovaného ƒinu jsou zakázány. Anal∞za konƒí dalτími databázov∞mi omezeními spojen∞mi s konkrétním návrhem tabulek.
Tabulka CIN má sloupce CIC, NAZEVC a CENAC. Primárním klíƒem je CIC jako ƒíslo ƒinu. Název ƒinu NAZEVC musí b∞t unikátní a základní cena ƒinu CENAC nesmí b∞t záporná. Tabulka CLOVEK má jako primární klíƒ rodné ƒíslo osoby RC. Následují sloupce JMENO, PRIJMENI, KOEUD a KOEOB, kde poslední dva p²edstavují koeficient udavaƒe a koeficient ob╪ti. Poslední tabulka UDANI se odvolává na údaje z p²edchozích dvou tabulek. Obsahuje denní záznamy ve sloupcích DEN, RCUD, RCOB a CICINU.
Primární klíƒ je urƒen celou ƒtve²icí sloupcà. Není divu, kdyº tabulka UDANI je spojovací entitou v 5NF a zároveσ má umoºσovat opakované udání jin∞ den. Sloupce RCUD a RCOB nesou informaci o rodném ƒísle udavaƒe a ob╪ti, a proto musí b∞t cizími klíƒi pro dvojité spojení s tabulkou CLOVEK. Sloupec CICINU obsahuje ƒíslo oznámeného ƒinu a je cizím klíƒem pro spojení s tabulkou CIN. Sloupec DEN obsahuje pouze datum udání. Mezi tabulkami tedy existují t²i relace A : 1, a to UDANI POSKYTL CLOVEK, UDANI O CLOVEKu a UDANI O CINu. Anal∞za nep²edpokládá evidenci míst, kde byly ƒiny spáchány nebo hláτeny.
CREATE DOMAIN KOEFICIENT
AS DECIMAL(6,3) DEFAULT 1
CHECK (VALUE >=1);
CREATE DOMAIN PRACHY
AS DECIMAL(10,2) DEFAULT 0
CHECK (VALUE>=0);
CREATE DOMAIN CISLO
AS INTEGER CHECK (VALUE >0);
Dalτí dv╪ domény RODNE a NAZEV máme jiº hotové z p²edchozího textu. Nyní s vyuºitím doménové a entitní integrity vytvo²íme oba ƒíselníky CIN a CLOVEK:
