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?
![[tabulky]](psql10um.jpg)
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.).
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.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);
CREATE TABLE operator (id SERIAL PRIMARY KEY NOT NULL, jmeno VARCHAR(20));
CREATE TABLE predvolba (predvolba CHAR(3) NOT NULL PRIMARY KEY CHECK (predvolba != ''), operator_id INTEGER REFERENCES operator(id));
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?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 '');
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.
A tabulku lidí: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);
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);
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 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)
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)
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í!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)
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)
