Telefonní databáze

• Návrh telefonní databáze
  • Tabulka kontakt
  • Tabulka operator
  • Tabulka predvolba
  • Tabulka tarif (unikátní sloupce)
  • Tabulka telefon (složený primární klíč)
• Využití databáze
  • Vyplnění tabulek hodnotami
  • Výběr hodnot z tabulek
  • Vytvoření pohledu do databáze
• Závěr
• MySQL
• SQLite
• Oracle

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.

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).

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:

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.

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é:

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.

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:

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:

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ů).

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ě:

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.)

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.

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

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

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

Výběr hodnot z tabulek

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

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.

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:

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).

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

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 ||:

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í?)

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

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.

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í.