9. diel - PostgreSQL: Modely databázy a väzby

V minulom dieli seriálu tutoriálov o PostgreSQL databáze sme si ukázali rôzne typy join a zoznámili sme sa s FOREIGN KEY. Dnes sa zmienime o rôznych modeloch databázy, jednotlivých väzbách medzi tabuľkami a budeme pokračovať v jednoduchom redakčnom systéme.

V siedmom dieli tutoriálu ste mohli vidieť konceptuálny model vytváranej databázy. Konceptuálne modelovanie predstavuje tú najvyššiu vrstvu abstrakcie pri modelovaní databázy. Z konceptuálneho modelu sa dozviete, aké tabuľky majú v databáze existovať a aké sú medzi nimi väzby.

O niečo podrobnejšie úroveň modelovanie predstavuje logický model. Z logického modelu sa dozviete navyše, aké stĺpce daná tabuľka obsahuje, či sú dáta v týchto stĺpcoch povinná alebo nie a trebárs aj to, ktorý stĺpec obsahuje primárne kľúče.

Najpodrobnejšie informácie získate z fyzického modelu. Tento model je už závislý na vybrané databázu, pretože jednotlivým stĺpcom priraďuje dátové typy a ich dĺžku či presnosť.

Obrázok ukazuje, ako by vo fyzickom modeli mohla vyzerať časť našej databázy. Tento fyzický model je určený pre Oracle databázu (používa dátové typy špecifické pre Oracle ako napr. VARCHAR2).

Súhrn

• Fyzický model je najpodrobnejšie, avšak je platný len pre určitú databázu. Poskytuje veľké množstvo informácií, avšak u väčšieho modelu sa môže stať neprehľadným.
• Logický model poskytuje informácie nielen o tabuľkách a ich väzbách, ale tiež o stĺpcoch v týchto tabuľkách a predstavuje kompromis medzi fyzickým a konceptuálnym modelom.
• Konceptuálny model dáva všeobecný prehľad o tom, ako daná databáza vyzerá - aké obsahuje tabuľky a aké sú medzi nimi väzby, avšak nie je príliš podrobný.

Každý z týchto modelov má svoje opodstatnenie a každý z nich sa viac hodí pre určité situácie a pre iné zase menej.

Logický model

V tejto časti sa bližšie zoznámime s logickým modelom tak, aby sme ho dokázali prečítať a použiť pre tvorbu nášho jednoduchého redakčného systému.

Modifikátory atribútov

• # Označuje stĺpec obsahujúci primárne kľúče
• * Označuje povinný stĺpec, hodnota nesmie byť NULL
• o označuje nepovinný stĺpec, hodnota v tomto stĺpci môže byť NULL

Väzby

Prerušovaná čiara znamená nepovinnú väzbu a plná čiara väzbu povinnú.

Väzba 1: 1

Entita_1 musí mať väzbu na jednu entitu_2 a entita_2 môže mať väzbu na jednu entitu_1.

Väzba 1: N

Entita_3 môže mať väzbu na jednu alebo viac entit_4 a entita_4 musí mať väzbu na jednu entitu_3.

Väzba M: N

Entita_5 môže mať väzbu na jednu alebo viac entit_6 a entita_6 môže mať väzbu na jednu alebo viac entit_5.

Tabuľka uzivatel

Stĺpec uzivatel_id obsahuje primárny kľúč (a je tým pádom povinný - NOT NULL). Stĺpec Prezdivka môže byť nepovinný na rozdiel od stĺpcov mail a heslo. Ak budeme mať užívateľa, určite budeme požadovať minimálne ich email a samozrejme heslo. Preto tieto stĺpce budú mať obmedzenia (CONSTRAINT) NOT NULL a databázy nám postráži, že každý záznam v tejto tabuľke bude obsahovať hodnotu v tomto stĺpci.

DROP TABLE IF EXISTS uzivatel CASCADE;

CREATE TABLE uzivatel
(
  uzivatel_id serial NOT NULL,
  prezdivka character varying(155),
  email character varying(155) NOT NULL,
  heslo character varying(255) NOT NULL,

  PRIMARY KEY (uzivatel_id)
);

Ak je stĺpec označený ako PRIMARY KEY, je automaticky tiež NOT NULL. Nič sa ale nestane, keď to v SQL príkazu pri vytváraní tabuľky znovu uvedieme.

Tabuľka clanek

Stĺpec clanek_id obsahuje primárne kľúče (v modeli označený #) a je zároveň povinný (v modeli označený *). Pri článku budeme požadovať minimálne titulok a obsah. V modeli sú teda označené znakom * a my im pridáme obmedzenia NOT NULL. Ďalším povinným atribútom bude uzivatel_id, čo je odkaz (FOREIGN KEY) na záznam v tabuľke uzivatel.

Zvyšné stĺpce budú môcť obsahovať NULL hodnotu.

Väzba medzi tabuľkou clanek a uzivatel nám hovorí: Užívateľ môže písať (byť autorom) jeden alebo viac článkov a článok musí byť napísaný jedným užívateľom.

DROP TABLE IF EXISTS clanek;

CREATE TABLE clanek
(
  clanek_id serial NOT NULL,
  uzivatel_id integer NOT NULL,
  popis character varying(155),
  url character varying(155),
  klicova_slova character varying(155),
  titulek character varying(155) NOT NULL,
  obsah text NOT NULL,
  publikovano timestamp,

  PRIMARY KEY (clanek_id),
  FOREIGN KEY (uzivatel_id) REFERENCES uzivatel(uzivatel_id)
);

Obmedzenie NOT NULL na stĺpci uzivatel_id zabezpečí, že v tomto stĺpci musí byť pre každý záznam hodnota. Obmedzenie FOREIGN KEY zas kontroluje, že hodnota v stĺpci uzivatel_id musí existovať v tabuľke uzivatel, v stĺpci uzivatel_id.

Teraz teda máme dve tabuľky, kedy tabuľka clanek má väzbu na tabuľku uzivatel. Pri vytváraní tabuľky uzivatel ste si možno všimli príkazu

DROP TABLE IF EXISTS uzivatel CASCADE;

Tento príkaz spôsobí, že sa zmaže (DROP TABLE) tabuľka uzivatel, ale iba v prípade, že takáto tabuľka existuje (IF EXISTS). Zároveň, ak na túto tabuľku odkazujú ďalšie databázové objekty (v našom prípade FOREIGN KEY v tabuľke clanek), budú tieto objekty (závislosti) takisto vymazané (CASCADE). Ak teda máte vytvorené obidve tabuľky skúste vykonať nasledujúci príkaz.

DROP TABLE IF EXISTS uzivatel;

Príkaz skončí chybou.

ERROR:  cannot drop table uzivatel because other objects depend on it
DETAIL:  constraint clanek_uzivatel_id_fkey on table clanek depends on table uzivatel
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


********** Error **********

ERROR: cannot drop table uzivatel because other objects depend on it
SQL state: 2BP01
Detail: constraint clanek_uzivatel_id_fkey on table clanek depends on table uzivatel
Hint: Use DROP ... CASCADE to drop the dependent objects too.

Popis chyby nám hovorí, že nie je možné vymazať / zahodiť (DROP) tabuľku uzivatel, pretože v databáze existujú objekty, ktoré na nej závisí. Je priamo uvedené, že tabuľka clanek závisí na tabuľke uzivatel. V časti hint (rada) je odporúčané použiť DROP s CASCADE.

DROP TABLE IF EXISTS uzivatel CASCADE;

Teraz prebehlo všetko v poriadku, bez chýb. Tabuľka uzivatel bola zmazaná, ale tabuľka clanek nie. Možno sa pýtate, ako je to možné. Veď tabuľka článok mala väzbu / závislosť (FOREIGN KEY) na tabuľku uzivatel, ktorá by toto nemala dovoliť. Ak sa pozriete na tabuľku clanek pozorne, zistíte, že tabuľka síce nebola zmazaná, ale bolo vymazané obmedzenia, ktoré zabezpečovalo väzbu na tabuľku uzivatel. Jednoducho povedané bol zmazaný FOREIGN KEY.

pred

po

Ak nechceme používať CASCADE pri mazaní tabuliek, musíme najskôr zmazať (dropnout) tabuľku clanek a potom až tabuľku uzivatel (presne v tomto poradí).

DROP TABLE IF EXISTS clanek;
DROP TABLE IF EXISTS uzivatel;

Vyššie uvedené príkazy dropnuly obe tabuľky. Vytvorte si teda obe tabuľky znova. Tabuľky si naplňte dátami zo súboru uzivatel_clanek_da­ta.sql, ktorý nájdete v prílohe.

V budúcom dieli vytvoríme v našej databáze ďalšej tabuľky a bližšie sa zoznámime s väzbou M: N.

 

Mal si s čímkoľvek problém? Stiahni si vzorovú aplikáciu nižšie a porovnaj ju so svojím projektom, chybu tak ľahko nájdeš.