<-- PosgreSQL  -->

• Telefonní databáze
• Využití databáze
• Vytvoření pohledu do databáze

V této kapitole využijeme našich znalostí a vytvoříme relační telefonní databázi. Všechny příkazy pro vytvoření databáze jsem pro Vás uložil do souboru psql10.sql. Vzpomeňte na 6. kapitolu, kde jste již měli jeden příklad v souboru uložený.

Telefonní databáze

Návrh

Než začneme nějakou databázi vytvářet, je třeba se zamyslet nad tím, co všechno od ní můžeme požadovat. To nám může v budoucnu ušetřit spoustu práce. Jak by tedy měla vypadat telefonní databáze? Určitě Vás nejdříve napadne, že si uděláte jednu tabulku, kde budete mít telefonní číslo a jméno. Co by jste však mohli potřebovat dál? Například by Vás mohlo zajímat, jakého operátora dané číslo využívá, protože Vás volání na jiného operátora stojí víc peněz. Pak si možná budete také chtít do databáze uložit nějaké informace k člověku, kterému číslo patří, aby jste si za čas nelámali hlavu nad tím, co že je to za člověka.

Je možné že někdy v budoucnu si založíte jinou databázi, která se bude týkat stejných lidí, které budete mít již uložené v telefonní databázi. Pak budete potřebovat takovéto databáze propojit. Dále předpokládejme, že telefonní čísla, která budete ukládat budou jen z české republiky, proto se nebudu zabývat předvolbami typu "+420".

Z těchto předpokladů tedy budeme vycházet při vytváření naší databáze. Vytvoříme celkem 4 tabulky (Kdo by se toho nadál :-)). První bude obsahovat telefonní číslo, referenci do tabulky, kde budete mít uložené své přátele (obchodní partnery nebo co chcete) a telefonní předvolbu, která bude sloužit k určení operátora. Telefonní číslo bude uloženo včetně předvolby, abychom mohli určit tento sloupec jako sloupec s unikátními hodnotami. Telefonní předvolba bude primárním klíčem v druhé tabulce - tabulce telefonních předvoleb. Druhá tabulka bude obsahovat jen 2 sloupce: telefonní předvolbu a referenci do tabulky operátorů. Tabulka operátorů bude obsahovat primární klíč, název a operátora. (Mohli by jste ji třeba rozšířit o sloupce cen volání k ostatním operátorům). Tabulka "lidí" bude obsahovat samozřejmně primární klíč, jméno příjmení, a nějaké další informace, jako třeba adresu atd. Tyto informace by bylo možná vhodné uložit do jiné tabulky, ale nebudeme to zase moc komplikovat, že?

Tabulka "lidí"

Tuto tabulku jste si možná již vytvořili sami, máte tam uložené své přátele, obchodní partnery atd. Pokud ano, můžete využít své tabulky. Jediné, co budeme z této tabulky potřebovat je primární klíč, pomocí kterého přesně určíme, komu telefonní číslo patří (Nezapomeňte, více čísel může patřit jednomu člověku).

Tabulku pojmenuji lide a co bude obsahovat již vidíte v příkladu. (Priorita určuje nakolik mám daného člověka rád -3 = nepřítel na život a na smrt, 3 = nejlepší přítel atd.).

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

Poznámka: Místo věku je jistě inteligentnějžší použít datum narození. Také jsme se prohřešili proti pravidlu dávat primární klíč (id) jako první položku.

Tabulka operátorů

Tabulku operátorů bude obsahovat jen jméno operátora a primární klíč. Tato tabulka je tu ze dvou důvodů. Jednak je pracné psát k předvolbám celé jméno operátora, nehledě na možnost překlepu a také proto, že si k operátorům můžete udělat další tabulky než jenom předvolby, například tarify volání atp.

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

Tabulka předvoleb

Tabulka předvoleb bude obsahovat předvolby a referenci do tabulky operátorů. Předvolby je dobré mít jako primární klíč, protože při vyplňování tabulky telefonních čísel nemusíte zjišťovat jaké má předvolba číslo primárního klíče, ale prostě jí zapíšete. Nese to v sobě ovšem jisté riziko v případě, že se předvolby změní. To pak musíte změnit jednak tabulku předvoleb a jednak sloupec předvoleb v tabulce telefonních čísel.

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

Tabulka telefonnních čísel

Konečně se dostáváme k tabulce telefonních čísel. První sloupec bude telefon. Jak již bylo řečeno, telefon bude včetně telefonní předvolby, abychom mohli tento sloupec označit jako sloupec unikátních hodnot. Tím zabráníme vložení jednoho čísla vícekrát. Následuje telefonní předvolba, která slouží k určení operátora, kterého "číslo" využívá. Použití této předvolby v sobě nese stejné riziko jako v předchozím případě: při změně telefonní předvolby budete muset změnit tyto dva sloupce! Jinou možností by bylo neukládat číslo s telefonní předvolbou, pak by jste však nemohli určit tento sloupec jako unikátní, protože dvě čísla s různou předvolbou můžou být stejná. Bohužel neumíme (zatím) nijak určit jako unikátní dvojici sloupců číslo + předvolba*. Dále je samozřejmě v tabulce telefony reference do tabulky lide.

CREATE TABLE telefony (telefon VARCHAR(20) NOT NULL UNIQUE,
predvolba CHAR(3) REFERENCES predvolba(predvolba) NOT NULL,
lide_id INTEGER REFERENCES lide(id), poznamka varchar(50) DEFAULT '');

Poznámka může být například "číslo domů", "do práce" atp. Napadlo Vás, když jste se poprvé zamysleli nad telefonní databází, co vše je třeba uvážit a co vše v této databázi bude?

*Unikátnost dvou sloupců.

Využití databáze

Vyplnění tabulek hodnotami

Než budeme databázi používat, musíme do ní vložit hodnoty. Začneme od nejjednoduššího - tabulky operátorů. Předvoleb je 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ří. Proto je zde Neznámý operátor "?", který Vám dovolí takovou předvolbu uložit do doby než zjistíte, kterému operátoru patří.

INSERT INTO operator(jmeno) VALUES ('Neznámý operátor'); -- id=1
INSERT INTO operator(jmeno) VALUES ('Pevná linka');  -- id=2
INSERT INTO operator(jmeno) VALUES ('Eurotel'); -- id=3
INSERT INTO operator(jmeno) VALUES ('T-Mobil'); -- id=4
INSERT INTO operator(jmeno) VALUES ('Oskar'); -- id=5

Teď můžete vyplnit tabulku předvoleb. Nelze uložit hodnoty do tabulky předvoleb a poté do tabulky operátorů, neboť se v tabulce predvoleb do tabulky operátorů odkazujeme.

INSERT INTO predvolba VALUES (607,1);
INSERT INTO predvolba VALUES (312,2);
...
INSERT INTO predvolba VALUES (602,3);
INSERT INTO predvolba VALUES (606,3);
...
INSERT INTO predvolba VALUES (603,4);
INSERT INTO predvolba VALUES (604,4);
...
INSERT INTO predvolba VALUES (608,5);

A tabulku lidí:

INSERT INTO lide(jmeno,prijmeni,priorita) VALUES ('Pavel','Drevokocur',0);
INSERT INTO lide(jmeno,prijmeni,priorita) VALUES ('Tomas','Drevokocur',1);
INSERT INTO lide(jmeno,prijmeni,priorita) VALUES ('Lukas','Drevokocur',-3);
INSERT INTO lide(jmeno,prijmeni,priorita) VALUES ('Jan','Drevokocur',0);
INSERT INTO lide(jmeno,prijmeni,priorita) VALUES ('Milos','Drevokocur',3);
INSERT INTO lide(jmeno,prijmeni,priorita,pohlavi)
VALUES ('Pavla','Drevokocurova',3,false);
INSERT INTO lide(jmeno,prijmeni,priorita,pohlavi)
VALUES ('Jana','Drevokocurova',0,false);

Teprve teď, po té dlouhé a strastiplné cestě, můžeme začít ukládat telefonní čísla.

INSERT INTO telefony VALUES ('737555555','737', 1);
INSERT INTO telefony VALUES ('608555555','608', 2);
INSERT INTO telefony VALUES ('776555555','776', 4);
INSERT INTO telefony VALUES ('312555555','312', 4, 'Telefon domu');
INSERT INTO telefony VALUES ('604555555','604', 6);

Výběr hodnot z tabulek

Nyní již můžeme z tabulky získávat informace, které nás zajmají.

rimmer1=> SELECT prijmeni,jmeno,telefon FROM telefony,lide WHERE lide_id=id;
   prijmeni    | jmeno |  telefon
---------------+-------+-----------
 Drevokocur    | Pavel | 737555555
 Drevokocur    | Tomas | 608555555
 Drevokocur    | Jan   | 776555555
 Drevokocur    | Jan   | 312555555
 Drevokocurova | Pavla | 604555555
(5 rows)

Jak již bylo zmíněno v kapitole 7 SELECT, v případě že máme ve dvou tabulkách sloupce stejného jména, použijeme tvz. tečkovou notaci. V našem případě máme sloupec poznamka v tabulce telefony i lide.

rimmer1=> SELECT prijmeni,jmeno,telefon,telefony.poznamka FROM telefony,lide
rimmer1-> WHERE lide_id = id;
   prijmeni    | jmeno |  telefon  |   poznamka
---------------+-------+-----------+--------------
 Drevokocur    | Pavel | 737555555 |
 Drevokocur    | Tomas | 608555555 |
 Drevokocur    | Jan   | 776555555 |
 Drevokocur    | Jan   | 312555555 | Telefon domu
 Drevokocurova | Pavla | 604555555 |
(5 rows)

Vytvoření pohledu do databáze

Spojení tabulek lide a telefony nás bude zajímat jistě velmi často. Proto se nám bude hodit pohled do spojení těchto dvou tabulek. Vytvoříme jej pomocí příkazu CREATE VIEW. Pohled setřídíme podle příjmení a vybereme do něj jen ty sloupce, které by nás mohli někdy zajímat. Pohled by tedy mohl vypadat takto.

rimmer1=> SELECT prijmeni, jmeno, telefon, predvolba, telefony.poznamka AS
rimmer1-> "O telefonu", priorita, pohlavi, vek, adresa, lide.poznamka
rimmer1-> FROM lide,telefony WHERE lide_id = id ORDER BY prijmeni;
   prijmeni    | jmeno |  telefon  | predvolba |  O telefonu  | priorita | pohlavi | vek | adresa | poznamka
---------------+-------+-----------+-----------+--------------+----------+---------+-----+--------+----------
 Drevokocur    | Pavel | 737555555 | 737       |              |        0 | t       |     |        |
 Drevokocur    | Tomas | 608555555 | 608       |              |        1 | t       |     |        |
 Drevokocur    | Jan   | 776555555 | 776       |              |        0 | t       |     |        |
 Drevokocur    | Jan   | 312555555 | 312       | Telefon domu |        0 | t       |     |        |
 Drevokocurova | Pavla | 604555555 | 604       |              |        3 | f       |     |        |
(5 rows)

Teď už to chce jenom místo předvolby (predvolba) mít v tomto výběru jméno operátora a bude to perfektní!

V tabulce telefony máme sloupec s předvolbou, která je referencí do tabulky predvolba. V tabulce predvolba máme referenci do tabulky operator a až tam je jméno operátora. Proto musíme nejdříve zjistit primární klíč operátora z tabulky predvolba přídáním této tabulky do SELECTu (nezapomeňte na podmínku WHERE telefony.predvolba=predvolba.predvolba, abychom vybrali správnou podmnožinu kartézkého součinu těchto tabulek).

Přidáním tabulky operator získáme jméno operátora (nezapomeňte na podmínku WHERE operator_id=operator.id ... skuste si takový SELECT bez této podmínky :-)).

rimmer1=> CREATE VIEW telefonni_seznam AS
rimmer1-> SELECT prijmeni, lide.jmeno, telefon, operator.jmeno AS Operator,
rimmer1-> telefony.poznamka AS "O telefonu", priorita, pohlavi, vek, adresa,
rimmer1-> lide.poznamka FROM lide,telefony,predvolba,operator
rimmer1-> WHERE lide_id=lide.id AND telefony.predvolba=predvolba.predvolba
rimmer1-> AND operator_id=operator.id ORDER BY prijmeni;
CREATE

Teď můžete jednoduše pracovat s telefonním seznamem:

rimmer1=> SELECT prijmeni, jmeno, telefon, "O telefonu", Operator
rimmer1-> FROM telefonni_seznam ORDER BY Operator DESC;
   prijmeni    | jmeno |  telefon  |  O telefonu  |  operator
---------------+-------+-----------+--------------+-------------
 Drevokocur    | Pavel | 737555555 |              | T-Mobil
 Drevokocurova | Pavla | 604555555 |              | T-Mobil
 Drevokocur    | Jan   | 312555555 | Telefon domu | Pevná linka
 Drevokocur    | Tomas | 608555555 |              | Oskar
 Drevokocur    | Jan   | 776555555 |              | Eurotel
(5 rows)

<-- PostgreSQL  -->