Telefonní databáze

V této kapitole vás provedu návrhem trochu „složitější“ databáze. Pokusím se ukázat, nad čím vším byste se během návrhu databáze měli zamyslet.
Navrženou databázi pak budu využívat i v dalších lekcích.

Půjde hodně o povídací kapitolu a jedinou novou věcí z SQL, kterou se naučíte, bude, jak vytvořit unikátní či primární klíč z více jak jednoho sloupce.

Všechny příkazy pro vytvoření databáze jsem pro vás uložil do souboru psql10.sql.

Návrh telefonní databáze

Než začnete nějakou databázi vytvářet, je třeba se zamyslet nad tím, co všechno od ní budete požadovat a k čemu má sloužit. To vám může v budoucnu ušetřit spoustu práce.

Navrhováním tabulek vlastně modelujete reálný svět. Model je zdejdnodušený pohled na skutečné objekty z reálného světa. Model by měl obsahovat pouze to, co vás o reálném světě skutečně zajímá. Nemusíte ukládat informaci o tom, jakou barvu očí má váš zákazník – ledaže byste navrhovali databázi pro očního chirurga.

V této kapitole se pokusím navrhnout databázi kontaktů s telefonními čísli pro rize soukromé, domácí použití. Vědět, pro jaký účel je databáze navrhována, je klíčové. Z toho se pak odvozuje, jaké údaje budou v „modelu“ potřeba a jaké jsou zbytečné.

Jak by tedy měla vypadat taková telefonní databáze? Nejdřív vás možná napadne, že si uděláte jednu tabulku, kde budete mít jméno, telefonní číslo a možná nějakou poznámku ke kontaktu.
Co byste ještě mohli potřebovat dál? Například by vás mohlo zajímat, jakého operátora dané číslo využívá. Taky vás může zajímat, kolik vás bude stát hovor za minutu na dané číslo. Pak si možná budete chtít do databáze uložit nějaké další informace o člověku, kterému číslo patří, abyste si za čas nelámali hlavu nad tím, co že je to za člověka.

Pro zjednodušení budu předpokládat, že telefonní čísla, která budete ukládat, budou jen z české republiky, proto se nebudu zabývat předvolbami typu "+420". Taky mě nebude zajímat kolik stojí poslání SMS, ani to, že někteří operátoři účtují po sekundách a někteří po minutách.

Diagram tabulek

Diagram tabulek

Z výše uvedených předpokladů budu vycházet při návrhu databáze. Vytvořím celkem 5 tabulek (kdo by se toho nadál :-), které můžete vidět na obrázku.

Na začátku skriptu se sluší tabulky smazat (pokud existují). A to v opačném pořadí, než se budou vytvářet (kvůli referencím mezi tabulkami).

DROP TABLE IF EXISTS telefon;
DROP TABLE IF EXISTS kontakt;
DROP TABLE IF EXISTS tarif;
DROP TABLE IF EXISTS predvolba;
DROP TABLE IF EXISTS operator;

Tabulka kontakt

Základem telefonní databáze bude jistě tabulka telefonů. Je ale dobré trochu předvídat potřeby budoucnosti. Je možné, že si někdy v budoucnu budete chtít uchovávat další informace, které se budou týkat stejných lidí, které budete mít uložené v tabulce „telefonů“. Pak budete potřebovat takovéto informace propojit. Proto bude lepší, když budou informace o lidech oddělené (v jiné tabulce) než informace o telefonech.

Výhodou rozdělení kontaktů a telefonů do dvou tabulek je i to, že když budete zadávat ke kontaktu více telefonů, nemusíte všechny inormace o něm duplikovat v každém řádku. Z tabulky telefonů se prostě odkážete do tabulky kontaktů (všimněte si šipky z tabulky telefonů do tabulky kontaktů na obrázku výše).

Tabulku jsem pojmenoval kontakt, protože člověk je příliš obecný název. Proč je název v jednotém čísle a ne v množném? V zásadě je to jedno, jestli budete používat jednotné nebo množné číslo. Důležité je, abyste byli v tomto konzistentní a používali pro všechny tabulky buď jen jednotné, nebo jen množné číslo.

Tabulka kontakt obsahuje nejvíce sloupečků, ale její návrh je velmi jednoduchý. Obashuje základní informace o kontaktu (jméno, příjmení, priorita, pohlaví, adrsesa kontaktu, věk a poznámka). Kontakt nemusí být jen váš známý, může to být nějaký obchodní kontakt, proto obsahuje dva sloupečky, kam si můžete zapsat z jaké firmy člověka znáte a nějakou poznámku o dané firmě.

  • id je primární klíč, na který se bude odkazovat tabulka telefon.
  • jmeno, prijmeni a poznamka asi nemusím vysvětlovat. Rozhodl jsem se je udělat povinné (na obrázku označeno hvězdičkou *). Myslím, že není potřeba rozlišovat mezi tím, kdy třeba příjmení neznám, nebo nemám žádnou poznámku a tím, kdy je poznámka nebo příjmení prázdné (obsahuje prázdný řetězec). Sice tak příjdu o informaci jestli osoba příjmení nemá (třeba Madona), nebo ho neznám, ale to mě u mé soukromé databáze netrápí a ušetřím si starosti s hodnotou NULL. Stejně tak nepotřebuju rozlyšovat u poznámky, jestli je prázdná, nebo jí neznám (co by to vůbec znamenalo?).
  • pohlavi, adresa a vek jsou asi taky jasné. Nechal jsem je nepovinné, protože je jednak nemusím vždycky znát, nebo mě nemusí zajímat. (Pravda, sloupec adresa by také mohl být povinný a nemusel bych rozlišovat mezi tím, kdy je adresa prázdná a kdy je NULL, stejně jako u příjmení nebo poznámky. A asi by to bylo i lepší – konzistentnější rozhodnutí :-) V zájmu příkladů pro výuku se mi ale hodí, aby mohla být adresa NULL.)
  • priorita určuje nakolik mám daného člověka rád (3 = nejlepší přítel, 0 = defaulně bez priority, -3 = nepřítel na život a na smrt atd.). Pokud sem se pro žádnou prioritu nerozhodl, je 0, takže hodnota NULL by znamenala totéž. Mít dvě hodnoty pro totéž nevede nikdy k ničemu dobrému, proto je NULL zakázané (priorita je povinná).
  • Sloupce firma_nazev a firma_poznamka se hodí pro kontakty, které znám z nějakého firmy, obchodu, úřadu atp. Pochopitelně jsou nepovinné. U firmy chci vědět, jestli je člověk z nějaké firmy nebo ne, proto může být firma_nazev NULL. A když už může být NULL název firmy, měla by být NULL i poznámka k firmě. Vypadalo by dost divně, kdyby byla firma NULL, ale poznámka ne.

Výsledné SQL pro vytvoření tabulky kontakt vypadá takto:

CREATE TABLE kontakt (
    id SERIAL PRIMARY KEY NOT NULL,
    jmeno VARCHAR(20) DEFAULT '' NOT NULL,
    prijmeni VARCHAR(20) DEFAULT '' NOT NULL,
    priorita INTEGER NOT NULL DEFAULT 0 CHECK (priorita >= -3 AND priorita <= 3),
    pohlavi BOOLEAN DEFAULT NULL,
    adresa VARCHAR(100) DEFAULT NULL,
    vek INTEGER DEFAULT NULL CHECK (vek > 0),
    poznamka TEXT DEFAULT '' NOT NULL,
    firma_nazev VARCHAR(50) DEFAULT NULL,
    firma_poznamka TEXT DEFAULT NULL
);

Všimněte si integritních omezení, která se starají o to, aby se nedostali do databáze omylem nějaké blbosti.

Proč jsou sloupce s datovým typem VARCHAR tak dlouhé, jak jsou? Jednoduše kvalifikovaný odhad :-). Někdo používá v takovém případě vždy VARCHAR(255) (což je max. velikost tohoto datového typu v MySQL). Zpomalení způsobené takto velkým datovým typem je, s dnešními počítači, většinou zanedbatelné, takže proč ne. Snad jen, že přijdete o „nápovědu“ o tom, co asi bude sloupeček obsahovat za hodnoty.

Pokud vám kvalifikovaný odhad nestačí (u profiesionálních databází by neměl), není zase tak těžké si vygooglit, že nejdelší české příjmení má 17 znaků (Červenokostelecký) a nejdelší jméno z českého kalendáře má 11 znaků. Pravda, pořád hrozí, že bude mít někdo nějaké cizokrajné jméno či příjmení, na které bude 20 znaků málo.

Tabulka operator

Zatím by to mohlo vypadat, jako by nám stačila tabulky kontakt a telefon. Pojďme si to ale trošku zkomplikovat – teda zjednodušit.

Pro operátory jsem navrhl extra tabulku. Bude obsahovat jen umělý klíč (id) a jméno operátora.

Jméno by samo o sobě asi mohlo být primárním klíčem. Operátorů budou jednotky a né statisíce, takže s výkonem byste si starosti dělat nemuseli. Ale co když se nějaký operátor přejmenuje (někdo ho koupí)? Díky primárnímu klíči stačí upravit jen sloupeček jmeno v tabulce operátora a ne ve všech tabulkách, které se na operátora odkazují.
Nicméně asi nebude od vědci udělat sloupeček jmeno unikátní. Jméno zůstává přirozeným primárním klíčem.

Důvod proč jsem nenechal operátora součástí tabulky telefon je asi zřejmý z diagramu výše. Bude se na něj odkazovat více tabulek. Mít jméno operátora v tabulce telefonů i tarifů, při přejmenování by se muselo jméno opravovat na více místech. A pak taky, kdyby ste se rozhodli že chcete ukládat další informace o operátorovi, kam byste je dali? Do tabulky telefonů nebo tarifů?

SQL pro vytvoření tabulky operator je velmi jednoduché:

CREATE TABLE operator (
    id SERIAL PRIMARY KEY NOT NULL,
    jmeno VARCHAR(20) NOT NULL UNIQUE
);

Tabulka predvolba

Tabulka předvoleb bude obsahovat předvolby a referenci do tabulky operátorů. Předvolby tentokrát nechám jako primární klíč. Předvolba se bude těžko měnit jako jméno společnosti. Navíc je krátká, má vždy max. 3 čísla, takže se vyplatí jí udělat typu CHAR, se kterým se pracuje poměrně rychle. Maximálně (teoreticky) můžete mít 1000 předvoleb, takže o nějakém zrychlování zavedením umělého klíče taky nemůže být řeč. Zavedením umělého klíče by se ani neušetřilo moc místa.

CREATE TABLE predvolba (
    predvolba CHAR(3) NOT NULL PRIMARY KEY CHECK(LENGTH(predvolba) >= 1),
    operator_id INTEGER REFERENCES operator(id) NOT NULL
);

Napadá vás, proč je předvolba typu CHAR a ne INTEGER? Správná odpověď je – ze sémantických důvodů – předvolba není číslo, nebudete s ní dělat žádné matematické operace (násobit jí, sčítat atd). Telefonní číslo je číslo asi jako rodné číslo nebo číslo občanky – vlastně to číslo není :-).

Tabulka tarif (unikátní klíče)

V této tabulce se bude udržovat informace o tarifu. Stejný tarif bude mít mnoho telefonních čísel, takže, aby se informace neduplikovali, budou ve vlastní tabulce.

Nejde o tabulku tarifů, které vám nabízí váš operátor. Jde o tarif, za který voláte na dané telefonní číslo. I v rámci jednoho operátora můžete volat za různé ceny (můžete mít zvýhodněné volání v rámci firmy, existují různá „drahá“ telefonní čísla atp.).

Všechny tarify jsou tarify vašeho operátora (se kterým voláte), ale vztahují se k nějakému operátorovi (do kterého voláte). Jeden tarif může znamenat různé ceny podle operátora, ne kterého voláte (jiná cena je pro vašeho operátora, jiná na pevnou linku, jiná na ostatní mobilní operátory). Název tarifu proto není unikátní, ale název tarifu + operátor už ano.

Je možné, že nebudete znát cenu tarifu, proto je cena nepovinná. Stojí za zvážení, jestli takový tarif bez ceny má cenu ukládat do databáze. Kdybych se rozhodl že ne, udělal bych cenu povinnou položkou.

Tarif bude mít umělý klíč id. Bude se na něj snadněji odkazovat, než na id operátora + jméno. Ale dvojice sloupců operator_id a jmeno by měla být unikátní. Omezení (constraint) UNIQUE nad více než jedním sloupcem se musí definovat na úrovni tabulky (a ne na úrovni sloupce), takže SQL pro vytvoření tabulky tarif bude vypadat takto:

CREATE TABLE tarif (
    id SERIAL PRIMARY KEY NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NULL,
    nazev VARCHAR(20) NOT NULL,
    cena NUMERIC(3, 1) NULL CHECK(cena >= 0),
    UNIQUE (operator_id, nazev)
);

Vytvořením omezení UNIQUE se vytvoří i index, který DBMS využívá pro hlídání unikátnosti. Tento index má své jméno, které mu vymyslí DBMS, nebo mu ho můžete explicitně určit:

rimmer1=> CREATE TABLE tarif (
    id SERIAL PRIMARY KEY NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NULL,
    nazev VARCHAR(20) NOT NULL,
    cena NUMERIC(3, 1) NULL CHECK(cena >= 0),
    CONSTRAINT operator_nazev_uix UNIQUE (operator_id, nazev)
);
NOTICE:  CREATE TABLE will create implicit sequence "tarif_id_seq" for serial column "tarif.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tarif_pkey" for table "tarif"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "operator_nazev_uix" for table "tarif"
CREATE TABLE

Tabulka telefon (složený primární klíč)

Konečně se dostane na tabulku telefon. Tabulka obsahuje předvolbu a zbytek čísla (telefon), povinný odkaz na operátora, nepovinný odkaz na tarif (ne vždy ho budu znát, tak jej nevyžaduji), nepovinný odkaz na kontakt (můžu mít uložen telefon bez kontaktu – třeba na požárníky nebo na erotickou linku :-). Přidal jsem ještě poznámku, která se může hodit pro telefony bez kontaktu, nebo pro kontakty s více telefony (je to telefon domů, do práce, nebo jaký teda?).

Poznámka může mít až 100 znaků, což by mělo na bližší identifikaci telefonu stačit (kvalifikovaný odhad :-). Pro takovou domácí databázi by asi nevadilo použít i typ TEXT, ikdyž je s ním práce o malinko pomalejší (zas tak moc telefonů mít doma určitě nebudete). Jiná záležitost by to ale byla při navrhování databáze pro velkou (nadnárodní) firmu. Tam už by stálo za zvážení, zda je rychlost důležitá, zda omezit max. délku poznámky (která většinou bude stejně prázdná). Takové rozhodnutí by už ste ale neměli dělat sami, ale po poradě s klientem (opravdu mu bude stačit 100 znaků, nebo raději obětuje trochu toho místa a rychlosti?)

Při definici tabulky telefon bude jako primární klíč sloužit dvojice sloupců predvolba a telefon. Na tabulku se odnikud nebudu odkazovat, takže umělý klíč (id) by byl zbytečný. Stejně jako když jsem vytvářel unikátního klíče nad více sloupci v tabulkce tarif, i pro primární klíč nad více sloupci platí, že se musí definovat na úrovní tabulky (nemůžete ho definovat na úrovni sloupce, když se to týká dvou sloupců).

CREATE TABLE telefon (
    predvolba CHAR(3) NOT NULL REFERENCES predvolba(predvolba),
    telefon VARCHAR(6) NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NOT NULL,
    tarif_id INTEGER REFERENCES tarif(id) DEFAULT NULL,
    kontakt_id INTEGER REFERENCES kontakt(id) DEFAULT NULL,
    poznamka VARCHAR(100) NOT NULL DEFAULT '',
    PRIMARY KEY(predvolba, telefon)
);

Primární klíč musí být vždy unikátní. Takže při vytvoření primárního klíče se vytvoří i index, který DBMS využívá pro kontrolu unikátnosti. Indexu vytvoří DBMS jméno automaticky, nebo ho můžete určit explicitně:

rimmer1=> CREATE TABLE telefon (
    predvolba CHAR(3) NOT NULL REFERENCES predvolba(predvolba),
    telefon VARCHAR(6) NOT NULL,
    operator_id INTEGER REFERENCES operator(id) NOT NULL,
    tarif_id INTEGER REFERENCES tarif(id) DEFAULT NULL,
    kontakt_id INTEGER REFERENCES kontakt(id) DEFAULT NULL,
    poznamka VARCHAR(100) NOT NULL DEFAULT '',
    CONSTRAINT telefon_pk PRIMARY KEY(predvolba, telefon)
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "telefon_pk" for table "telefon"
CREATE TABLE

V našem případě je unikátní dvojice predvolba + telefon. To znamená, že se předvolba i telefon mohou v tabulce libovolněkrát opakovat, ale dvojice predvolba + telefon musí být unikátní. Což je přesně to, co se od tabulky telefonů očekává.

Využití databáze

Vyplnění tabulek hodnotami

Než budu databázi používat v příkladech, musím do ní vložit nějaké hodnoty. Začnu od nejjednoduššího - tabulky operátorů.

Předvoleb je dneska už tolik, že se vám snadno stane, že narazíte na předvolbu, u které nebudete vědět, ke kterému operátoru patří. A protože je u předvolby povinný odkaz na operátora, vytvořím si tzv. Neznámého operátora, který mi dovolí uložit předvolbu do databáze do doby než zjistím, kterému operátoru předvolba vlastně patří.

Jasně, taky jsem nemusel dělat v tabulce předvoleb odkaz na operátora povinný. Ale zase tak často novou neznámou předvolbu ukládat nebudu. A když už jí uložím, tak jen dočasně, než zjistím, komu patří (to je kvalifikovaný odhad :-). Povinnost mít vyplněného operátora (to, že nesmí být NULL) mi ale na druhou stranu velmi zjednoduší SELECTy! (Nebudu muset dávat speciální pozor na řádky s NULL hodnotou u operator_id.)

INSERT INTO operator(jmeno) VALUES ('?'); -- id=1
INSERT INTO operator(jmeno) VALUES ('Pevná linka'); -- id=2
INSERT INTO operator(jmeno) VALUES ('O2'); -- id=3
INSERT INTO operator(jmeno) VALUES ('T-Mobil'); -- id=4
INSERT INTO operator(jmeno) VALUES ('Vodafone'); -- id=5
INSERT INTO operator(jmeno) VALUES ('U:fon'); -- id=6

Teď se můžete vyplnit tabulka předvoleb (která se odkazuje na operátory). Předvoleb je hodně, tak nebudu vypisovat všechny INSERTy, najdete je ve skriptu psql10.sql.

INSERT INTO predvolba VALUES (312,2);
INSERT INTO predvolba VALUES (318,2);
INSERT INTO predvolba VALUES (601,3);
INSERT INTO predvolba VALUES (602,3);
...
INSERT INTO predvolba VALUES (799,1);
INSERT INTO predvolba VALUES (908,1);

Ještě něco do tabulky kontaktů:

INSERT INTO kontakt(jmeno,prijmeni,priorita,adresa) VALUES ('Pavel','Drevokocur', 0,'Na nožičkách 3, Praha 4, 13000');
INSERT INTO kontakt(jmeno,prijmeni,priorita,adresa) VALUES ('Petr','Bílek', 0,'Na balkáně 70, Praha 3, 13004');
INSERT INTO kontakt(jmeno,prijmeni,priorita) VALUES ('Tomas','Drevokocur', 1);
INSERT INTO kontakt(jmeno,prijmeni,priorita) VALUES ('Lukas','Drevokocur',-3);
INSERT INTO kontakt(jmeno,prijmeni,priorita) VALUES ('Milos','Drevokocur', 3);
INSERT INTO kontakt(jmeno,prijmeni,priorita,pohlavi) VALUES ('Pavla', 'Drevokocurova', 3, false);
INSERT INTO kontakt(jmeno,prijmeni,priorita,pohlavi) VALUES ('Jana',  'Drevokocurova', 0, false);
INSERT INTO kontakt(jmeno,prijmeni,priorita,pohlavi,vek,firma_nazev,firma_poznamka) VALUEs ('Tomáš','Baťa',3,true,70,'Baťovy závody','Vyrábí boty');
INSERT INTO kontakt(jmeno,prijmeni,priorita,pohlavi,vek,firma_nazev,firma_poznamka) VALUEs ('Jan','Baťa',2,true,40,'Baťovy závody','Vyrábí boty');

Ještě vložím pár příkladů tarifů:

INSERT INTO tarif(operator_id,nazev,cena) VALUES (1, 'Za 30', NULL); -- id 1
INSERT INTO tarif(operator_id,nazev,cena) VALUES (1, 'Za 60', NULL); -- id 2
INSERT INTO tarif(operator_id,nazev,cena) VALUES (1, 'Za 90', NULL); -- id 3
INSERT INTO tarif(operator_id,nazev,cena) VALUES (2, 'Pevna', 10); -- id 4
INSERT INTO tarif(operator_id,nazev,cena) VALUES (3, 'O2 zaklad',9); -- id 5
INSERT INTO tarif(operator_id,nazev,cena) VALUES (3, 'O2 kamarad',6); -- id 6
INSERT INTO tarif(operator_id,nazev,cena) VALUES (4, 'zaklad', 8); -- id 7
INSERT INTO tarif(operator_id,nazev,cena) VALUES (4, 'kamarad', 5); -- id 8
INSERT INTO tarif(operator_id,nazev,cena) VALUES (5, 'Vodafone zaklad', 3.5); -- id 9
INSERT INTO tarif(operator_id,nazev,cena) VALUES (5, 'Vodafone kamarad', 0); -- id 10
INSERT INTO tarif(operator_id,nazev,cena) VALUES (6, 'zaklad', 7); -- id 11
INSERT INTO tarif(operator_id,nazev,cena) VALUES (6, 'kamarad', 5); -- id 12

A teprve teď, po té dlouhé a strastiplné cestě, se mohou začít ukládat telefonní čísla.

INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '737',4, 7, 1);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '608',5, 10,  2);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '776',5, 9,  4);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id, poznamka) VALUES ('555555', '312', 2, 4, 4, 'Telefon domu');
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id) VALUES ('555555', '604',4, 7, 6);
INSERT INTO telefon (telefon,predvolba,operator_id, tarif_id, kontakt_id, poznamka) VALUES ('300300','908',1,1, NULL, 'Půjčka za 30/min');

Výběr hodnot z tabulek

Všechny tabulky jsou vytvořeny, data vložena, takže si můžeme začít hrát :-).

rimmer1=> SELECT prijmeni, jmeno, CONCAT(predvolba,'/', telefon) AS telefon
FROM telefon, kontakt WHERE kontakt_id = kontakt.id;
   prijmeni    | jmeno |  telefon  
---------------+-------+------------
 Drevokocur    | Pavel | 737/555555
 Bílek         | Petr  | 608/555555
 Drevokocur    | Lukas | 776/555555
 Drevokocur    | Lukas | 312/555555
 Drevokocurova | Pavla | 604/555555
(5 rows)

Ale ejhle, jen 5 řádek? V databází je přece 6 telefonních čísel a 9 kontaktů! Jenomže ne každý kontakt má telefon a ne každý telefon má kontakt. O tom, jak do výsledku předešlého dotazu zahrnout i kontakty bez telefonu, nebo telefony bez kontaktu, bude následující kapitola :-).

Jak již bylo zmíněno v kapitole SELECT I a VIEW, v případě že máte ve dvou tabulkách sloupce stejného jména, použijete pro jejich rozlišení tzv. tečkovou notaci. Například sloupec poznamka je v tabulce telefon i kontakt.

rimmer1=> SELECT prijmeni, jmeno, predvolba, telefon, telefon.poznamka
FROM kontakt, telefon
WHERE kontakt_id = kontakt.id AND prijmeni = 'Drevokocur' AND jmeno = 'Lukas';
   prijmeni    | jmeno | predvolba | telefon |   poznamka  
---------------+-------+-----------+---------+--------------
 Drevokocur    | Lukas | 776       | 555555  |
 Drevokocur    | Lukas | 312       | 555555  | Telefon domu
(2 rows)

To by myslím jako ukázka stačilo. Za domácí úkol si vyzkoušejte další SELECTy, přidejte například do výsledku informace o ceně volání (z tarifu), název operátora atd.

Vytvoření pohledu do databáze

Data máme rozdělené do mnoha tabulek. Aby se s nimi dalo dobře pracovat, tak je teď pro změnu zase spojíme. Nejzajímavější určitě bude spojení tabulek kontakt a telefon. Proto se bude hodit vytvořit pohled na spojení těchto dvou tabulek.

Vytvořím jej pomocí příkazu CREATE VIEW. Pohled setřídím podle příjmení a vyberu do něj jen ty sloupce, které mě budou zajímat nejvíce. Pohled by mohl vypadat takto:

rimmer1=> SELECT prijmeni, jmeno, telefon, predvolba, telefon.poznamka AS "O telefonu",
priorita, pohlavi, vek, adresa, kontakt.poznamka
FROM kontakt, telefon
WHERE kontakt_id = id
ORDER BY prijmeni;
   prijmeni    | jmeno | telefon | predvolba |  O telefonu  | priorita | pohlavi | vek |             adresa             | poznamka
---------------+-------+---------+-----------+--------------+----------+---------+-----+--------------------------------+----------
 Bílek         | Petr  | 555555  | 608       |              |        0 |         |     | Na balkáně 70, Praha 3, 13004  |
 Drevokocur    | Pavel | 555555  | 737       |              |        0 |         |     | Na nožičkách 3, Praha 4, 13000 |
 Drevokocur    | Lukas | 555555  | 776       |              |       -3 |         |     |                                |
 Drevokocur    | Lukas | 555555  | 312       | Telefon domu |       -3 |         |     |                                |
 Drevokocurova | Pavla | 555555  | 604       |              |        3 | f       |     |                                |
(5 rows)

Teď to ještě chce zobrazit jméno operátora. Všiměte si, že operátor telefonu se dá zjistit dvěma cestami. Buď přímo z operator_id z tabulky telefon, nebo přes tabulku predvolba. To ukazuje, že nebyla databáze navržena úplně košer, protože k takovéto duplicitě dat by nemělo nikdy dojít. K tomu se ještě vrátím.

Spojení tabulky operator přímo s tabulkou telefon by bylo jednodušší, proto to udělám (ze cvičných důvodů) oklikou přes tabulku predvolba (později stejně operator_id z tabulky telefon vykopnu, z důvodů popsaných výše).

CREATE VIEW telefonni_seznam AS
    SELECT prijmeni, kontakt.jmeno, telefon.predvolba, telefon,
    operator.jmeno as "Operator", telefon.poznamka AS "O telefonu",
    priorita, pohlavi, vek, adresa,
    kontakt.poznamka
    FROM kontakt, telefon, predvolba, operator
    WHERE kontakt_id = kontakt.id AND predvolba.predvolba = telefon.predvolba AND predvolba.operator_id = operator.id
    ORDER BY prijmeni, jmeno;

Teď už se může pracovat s telefonním seznamem jako s jednou tabulkou:

rimmer1=> SELECT prijmeni, jmeno, CONCAT(predvolba,'/',telefon) as telefon, "O telefonu", "Operator"
FROM telefonni_seznam ORDER BY "Operator" DESC;
   prijmeni    | jmeno |  telefon   |  O telefonu  |  Operator  
---------------+-------+------------+--------------+-------------
 Bílek         | Petr  | 608/555555 |              | Vodafone
 Drevokocur    | Lukas | 776/555555 |              | Vodafone
 Drevokocur    | Pavel | 737/555555 |              | T-Mobil
 Drevokocurova | Pavla | 604/555555 |              | T-Mobil
 Drevokocur    | Lukas | 312/555555 | Telefon domu | Pevná linka
(5 rows)

Všiměte si, že název sloupečku Operator musí být v uvozovkách. Jinak se Postgres tváří, jako by pohled sloupeček "Operator" neměl (ikdyž víme, že má). Je to proto, že při vytváření pohledu byl název sloupečku definován v uvozovkách. Kdyby byl definován bez uvozovek, pak by se v SELECTu taky musel použít bez uvozovek. No není to na hlavu? (Je to na hlavu. V ostatních DBMS tento problém není, vyjma Oracle, který k tomu ale má důvod).

Za domácí úkol můžete přidat do pohledu ještě informace o tarifech.

Závěr

V této kapitole jsem připravil tabulky a data pro další kapitoly. Ukázal jsem, jak vytvořit unikátní index nebo primární klíč nad více jak jedním sloupečkem.

Návrh databáze není dokonalý. Obsahuje několik „nedokonalostí“, o kterých bude řeč v kapitole o normalizaci databáze. Už jsem se zmínil o tom, že není dobré, aby bylo možné zjistit operátora telefonního čísla dvěmi cestami. Napadají vás další chybičky v návrhu, které by vám mohli v budoucnu znepříjemnit používání databáze?

MySQL/MariaDB

Jediné novinky v této lekci byly vytvoření unikátního a primárního klíče na více sloupcích. To se v MySQL dělá stejně jako v PostgreSQL. Rozdíly oproti PostgreSQL v SQL příkazech z této lekce byli již popsány v předchozích lekcích.

V MySQL je také bug, který znemožňuje použití defaultní hodnoty s datovým typem TEXT. Existuje sice určité nastavení MySQL, které použití defaultní hodnoty povoluje, ale na to se nedá moc spoléhat. Proto jsem ve skryptu pro MySQL odstranil z tabulky kontakt defaultní hodnotu pro sloupeček poznamka a přidal do insertů do tabulky kontakt sloupec poznamka s hodnotou ''.

SQL skript pro MySQL najdete tady: psql10-mysql.sql.

Ještě připomenu, že je nutné definovat cizí klíč (foreign key) na úrovni tabulky (protože na úrovni sloupce nefunguje), nefunkčnost integritního omezení CHECK (ač jej lze definovat na konci definice sloupce), používání AUTO_INCREMENT místo sekvencí, potřeba definice typu tabulky (Engine), znakové sady (CHARSET) a COLLATE (způsob řazení znaků).

SQLite

V SQLite se unikátní a primární klíč na více sloupcích také vytváří stejně jako v PostgreSQL. Změny oproti PostgreSQL z této lekce byli již probrány v předchozích lekcích.

SQL skript pro SQLite najdete tady: psql10-sqlite.sql.

Zmíním jen používání AUTOINCREMENT místo sekvencí, neexistenci true a false (místo nich se používá 1 a 0). SQLite také nezná funkci CONCAT, takže se místo ní používá operátor ||:

SELECT prijmeni, jmeno, predvolba || '/' || telefon AS telefon
FROM telefon, kontakt WHERE kontakt_id = kontakt.id;

V klauzuli ORDER BY musí být název sloupce jednoznačně identifikován, takže u sloupce jmeno, který se nachází ve více tabulkách, musí být pomocí tečkové notace jasně řečeno, z jaké je tabulky. (Ostatní DBMS „uhodnou“, že máme na mysli sloupeček, který je v klauzuli SELECT, kde je sloupec jmeno jednoznačně identifikován. Což asi není úplně dobře, třídit se dá i podle sloupců které nejsou v klauzuli SELECT, tak proč si to DBMS takto domýšlí?)

CREATE VIEW telefonni_seznam AS
    SELECT prijmeni, kontakt.jmeno, telefon.predvolba, telefon,
    operator.jmeno as "Operator", telefon.poznamka AS "O telefonu",
    priorita, pohlavi, vek, adresa,
    kontakt.poznamka
    FROM kontakt, telefon, predvolba, operator
    WHERE kontakt_id = kontakt.id AND
    predvolba.predvolba = telefon.predvolba AND predvolba.operator_id = operator.id
    ORDER BY prijmeni, kontakt.jmeno;

A ještě jednou problém s funkcí CONCAT:

SELECT prijmeni, jmeno, predvolba || '/' || telefon as telefon,
"O telefonu", operator
FROM telefonni_seznam ORDER BY "Operator" DESC;

Oracle

V Oracle se unikátní a primární klíč na více sloupcích také vytváří stejně jako v PostgreSQL. Změny oproti PostgreSQL z této lekce byli již probrány v předchozích lekcích.

SQL skript pro Oracle najdete tady: psql10oracle.sql.

Z rozdílů zmíním neexistenci IF EXISTS, nemožnost použít hodnotu sekvence jako DEFAULT (takže se musí explicitně psát v klauzuli INSERT), neexistenci typu BOOLEAN (ani hodnot true a false), datové typy VARCHAR2 a CLOB (namísto VARCHAR a TEXT), či nemožnost vložení prázdného řetězce '' (konvertuje se automaticky na hodnotu NULL).

V Oracle je problém s funkcí CONCAT, která může mít jen dva argumenty. Buď můžete použít operátor ||, nebo vnořený CONCAT.

SELECT prijmeni, jmeno, CONCAT(CONCAT(predvolba,'/'), telefon) AS telefon
FROM telefon, kontakt WHERE kontakt_id = kontakt.id;

SELECT prijmeni, jmeno, predvolba || '/' || telefon as telefon,
"O telefonu", "Operator" FROM telefonni_seznam ORDER BY "Operator" DESC;

V Oracle se převádějí jména sloupečků automaticky na velká písmena, pokud nepoužijete uvozovky, a navíc je Oracle case-sensitive (rozlišuje velikosti písmen). Proto, když se při vytváření pohledu telefonni_seznam definovalo jméno sloupečku "Operator", musíte tento identifikátor používat vždy s uvozovkami (jinak se převede na OPERATOR a to už není totéž co Operator).

Takto se Oracle chová nejen u pohledů, ale i u tabulek, takže, když nic jiného, jedná se alespoň o konzistentní chování.

Komentář Hlášení chyby
Created: 11.9.2005
Last updated: 10.9.2015
Tato stánka používá ke svému běhu cookies, díky kterým je možné monitorovat, co tu provádíte (ne že bych to bez cookies nezvládl). Také vás tu bude špehovat google analytics. Jestli si myslíte, že je to problém, vypněte si cookies ve vašem prohlížeči, nebo odejděte a už se nevracejte :-). Prohlížením tohoto webu souhlasíte s používáním cookies. Dozvědět se více..