9. díl - PostgreSQL: Modely databáze a vazby

PostgreSQL PostgreSQL: Modely databáze a vazby

V minulém dílu seriálu tutoriálů o PostgreSQL databázi jsme si ukázali různé typy JOINů a seznámili jsme se s FOREIGN KEY. Dnes se zmíníme o různých modelech databáze, jednotlivých vazbách mezi tabulkami a budeme pokračovat v jednoduchém redakčním systému.

V sedmém dílu tutoriálu jste mohli vidět konceptuální model vytvářené databáze. Konceptuální modelování představuje tu nejvyšší vrstvu abstrakce při modelování databáze. Z konceptuálního modelu se dozvíte, jaké tabulky mají v databázi existovat a jaké jsou mezi nimi vazby.

konceptuální model

O něco podrobnější úroveň modelování představuje logický model. Z logického modelu se dozvíte navíc, jaké sloupce daná tabulka obsahuje, zda jsou data v těchto sloupcích povinná či nikoliv a třeba i to, který sloupec obsahuje primární klíče.

logický model

Nejpodrobnější informace získáte z fyzického modelu. Tento model je již závislý na vybrané databázi, protože jednotlivým sloupcům přiřazuje datové typy a jejich délku či přesnost.

fyzický model

Obrázek ukazuje, jak by ve fyzickém modelu mohla vypadat část naší databáze. Tento fyzický model je určen pro Oracle databázi (používá datové typy specifické pro Oracle jako např. VARCHAR2).

Souhrn

  • Fyzický model je nejpodrobnější, avšak je platný pouze pro určitou databázi. Poskytuje velké množství informací, avšak u většího modelu se může stát nepřehledným.
  • Logický model poskytuje informace nejen o tabulkách a jejich vazbách, ale též o sloupcích v těchto tabulkách a představuje kompromis mezi fyzickým a konceptuálním modelem.
  • Konceptuální model dává obecný přehled o tom, jak daná databáze vypadá - jaké obsahuje tabulky a jaké jsou mezi nimi vazby, avšak není příliš podrobný.

Každý z těchto modelů má své opodstatnění a každý z nich se více hodí pro určité situace a pro jiné zase méně.

Logický model

V této části se blíže seznámíme s logickým modelem tak, abychom jej dokázali přečíst a použít pro tvorbu našeho jednoduchého redakčního systému.

Modifikátory atributů

  • # označuje sloupec obsahující primární klíče
  • * označuje povinný sloupec, hodnota nesmí být NULL
  • o označuje nepovinný sloupec, hodnota v tomto sloupci může být NULL

Vazby

Přerušovaná čára znamená nepovinnou vazbu a plná čára vazbu povinnou.

Vazba 1:1
vazba 1:1

Entita_1 musí mít vazbu na jednu entitu_2 a entita_2 může mít vazbu na jednu entitu_1.

Vazba 1:N
vazba 1:N

Entita_3 může mít vazbu na jednu nebo více entit_4 a entita_4 musí mít vazbu na jednu entitu_3.

Vazba M:N
vazba M:N

Entita_5 může mít vazbu na jednu nebo více entit_6 a entita_6 může mít vazbu na jednu nebo více entit_5.

Tabulka uzivatel

logický model tabulky uzivatel

Sloupec uzivatel_id obsahuje primární klíč (a je tím pádem povinný - NOT NULL). Sloupec prezdivka může být nepovinný na rozdíl od sloupců email a heslo. Pokud budeme mít uživatele, určitě budeme požadovat minimálně jejich email a samozřejmě heslo. Proto tyto sloupce budou mít omezení (constraint) NOT NULL a databáze nám ohlídá, že každý záznam v této tabulce bude obsahovat hodnotu v tomto sloupci.

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

Pokud je sloupec označen jako PRIMARY KEY, je automaticky též NOT NULL. Nic se ale nestane, když to v SQL příkazu při vytváření tabulky znovu uvedeme.

Tabulka clanek

logický model tabulky clanek

Sloupec clanek_id obsahuje primární klíče (v modelu označen #) a je zároveň povinný (v modelu označen *). U článku budeme požadovat minimálně titulek a obsah. V modelu jsou tedy označeny znakem * a my jim přidáme omezení NOT NULL. Dalším povinným atributem bude uzivatel_id, což je odkaz (FOREIGN KEY) na záznam v tabulce uzivatel.

Zbylé sloupce budou moci obsahovat NULL hodnotu.

vazba mezi tabulkou uzivatel a clanek

Vazba mezi tabulkou clanek a uzivatel nám říká: Uživatel může psát (být autorem) jeden nebo více článků a článek musí být napsán jedním uživatelem.

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

Omezení NOT NULL na sloupci uzivatel_id zajistí, že v tomto sloupci musí být pro každý záznam hodnota. Omezení FOREIGN KEY zas kontroluje, že hodnota ve sloupci uzivatel_id musí existovat v tabulce uzivatel, ve sloupci uzivatel_id.

Nyní tedy máme dvě tabulky, kdy tabulka clanek má vazbu na tabulku uzivatel. Při vytváření tabulky uzivatel jste si možná všimli příkazu

DROP TABLE IF EXISTS uzivatel CASCADE;

Tento příkaz způsobí, že se smaže (DROP TABLE) tabulka uzivatel, ale pouze v případě, že taková tabulka existuje (IF EXISTS). Zároveň, pokud na tuto tabulku odkazují další databázové objekty (v našem případě FOREIGN KEY v tabulce clanek), budou tyto objekty (závislosti) také smazány (CASCADE). Pokud tedy máte vytvořeny obě tabulky zkuste provést následující příkaz.

DROP TABLE IF EXISTS uzivatel;

Pří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 říká, že není možné vymazat/zahodit (DROP) tabulku uzivatel, protože v databázi existují objekty, které na ní závisí. Je přímo uvedeno, že tabulka clanek závisí na tabulce uzivatel. V části hint (rada) je doporučeno použít DROP s CASCADE.

DROP TABLE IF EXISTS uzivatel CASCADE;

Nyní proběhlo vše v pořádku, bez chyb. Tabulka uzivatel byla smazána, avšak tabulka clanek nikoliv. Možná se ptáte, jak je to možné. Vždyť tabulka článek měla vazbu/závislost (FOREIGN KEY) na tabulku uzivatel, která by toto neměla dovolit. Pokud se podíváte na tabulku clanek pozorně, zjistíte, že tabulka sice nebyla smazána, ale bylo smazáno omezení, které zajišťovalo vazbu na tabulku uzivatel. Jednoduše řečeno byl smazán FOREIGN KEY.

Před

tabulka utivatel s omezením

Po

tabulka utivatel bez omezení

Pokud nechceme používat CASCADE při mazání tabulek, musíme nejdříve smazat (dropnout) tabulku clanek a poté teprve tabulku uzivatel (přesně v tomto pořadí).

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

Výše uvedené příkazy dropnuly obě tabulky. Vytvořte si tedy obě tabulky znovu. Tabulky si naplňte daty ze souboru uzivatel_clanek_da­ta.sql, který naleznete v příloze.

V příštím dílu vytvoříme v naší databázi další tabulky a blíže se seznámíme s vazbou M:N.


 

Stáhnout

Staženo 11x (2.82 kB)
Aplikace je včetně zdrojových kódů v jazyce PostgreSQL

 

  Aktivity (1)

Článek pro vás napsal vita
Avatar
vita

Jak se ti líbí článek?
Celkem (1 hlasů) :
55555


 



 

 

Komentáře

Avatar
tomas.haubert:

Super seriál, pěkně od základu, krok po kroku, věcně a jednoduše. Tak to má být ;) těším se na pokračování.

 
Odpovědět 28.8.2015 12:13
Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 1 zpráv z 1.