Benak.NET

Při modelování určitých situací dospějeme k závěru, že jednosloupcový primární klíč je pro naše potřeby nedostatečný. Nabízí se tu možnost složeného primárního klíče, tedy klíče skládajícího se z několika sloupců, jejichž kombinace hodnot je jedinečná a lze podle ní daný záznam jednoznačně identifikovat. Složené klíče nám umožní vytvářet číselníkové položky podobné např. NUTS, bývalé JKSO, případně současné SKP. Rovněž nám poměrně výrazně usnadní skládání SQL dotazů ale především nám umožní vytvořit velmi silná integritní omezení, která zamezí nesmyslným kombinacím dat.

Dejme tomu, že potřebujeme popsat vztah mezi modely vozidel a výrobky. Každý model vozidla se skládá ze značky, názvu modelu, motoru a převodovky. Dále platí pravidlo, že konkrétní výrobek lze použít pouze s konkrétním typem vozidla.

E-R diagram

Data

Značka

----+------------
 id |   nazev
----+------------
  1 | Škoda
  2 | Volkswagen
  3 | Seat

Model

----+--------+---------
 id | znacka |  nazev  
----+--------+---------
  1 |      1 | Fabia
  2 |      3 | Toledo
  3 |      1 | Felicia
  4 |      2 | Polo

Převodovka

----+-------+----------
 id | model | oznaceni
----+-------+----------
  1 |     1 | AAA
  2 |     1 | BBB
  3 |     3 | CCC

Motor

----+-------+----------+-----+-------+-------
 id | model | oznaceni | typ | objem | vykon
----+-------+----------+-----+-------+-------
  1 |     1 | AA1      | L   |   1.4 |    44
  2 |     1 | AA2      | D   |   1.4 |    50
  3 |     2 | BB1      | L   |   1.6 |    75
  4 |     3 | CC1      | L   |   1.8 |    75
  5 |     4 | DD1      | L   |   1.8 |    96

Vozidlo

----+-------+------------+-------
 id | model | prevodovka | motor
----+-------+------------+-------
  1 |     1 |          3 |     1
  2 |     1 |          1 |     5
  3 |     3 |          3 |     4
  4 |     1 |          2 |     2

Výrobek

----+----------------------------
 id |           nazev            
----+----------------------------
  1 | Utahovák na klínové řemeny
  2 | Stahovák na převodovku
  3 | Aretační trn

Vozidlo_výrobek

---------+---------
 vozidlo | vyrobek 
---------+---------
       1 |       1
       2 |       1
       3 |       1
       4 |       1
       1 |       2
       2 |       2
       3 |       2
       4 |       2
       1 |       3
       2 |       3
       3 |       3
       4 |       3

Pozornému čtenáři jistě neuniklo, že díky nedokonalým integritním omezením jsem v jednom případě přiřadil Fabii převodovku z Felicie a ve druhém případě Fabii motor z VW Pola.

SELECT z.nazev, mo.nazev, v.nazev
FROM vozidlo_vyrobek vv
INNER JOIN vozidlo v ON vv.vozidlo=v.id
INNER JOIN model mo ON mo.id=vv.model
INNER JOIN znacka z ON mo.znacka=z.id;

Skladníci mají rádi čísla která se snadno pamatují a lze podle nich snadno identifikovat, pro které značky, modely a motory lze daný výrobek použít.
Zkusíme tedy použít složené primární klíče.

E-R diagram

Tento relační model je dostatečně robustní a zamezí vytváření nevhodných kombinací motorů a převodovek.

Tabulky

Značka

Tabulka Značka má jednoduchý primární klíč id a název značky, který se v případě vícejazyčných aplikací nemění.

-- Značka vozidel
CREATE TABLE znacka (
     id INTEGER,
     nazev VARCHAR(32)
     UNIQUE (nazev)
);
ALTER TABLE znacka ADD PRIMARY KEY (id);
ALTER TABLE znacka ADD UNIQUE (nazev);

Data

----+------------
 id |    nazev    
----+------------
  1 | Škoda
  2 | Volkswagen
  3 | Seat

Model

Tabulka Model má již složený primární klíč složený ze značky a id modelu, přičemž sloupec značka se odkazuje na sloupec id v tabulce Značka. Sloupec název slouží pro uložení názvu modelu, který se ani v případě vícejazyčných aplikací nemění. Model vozidla je jednoznačně identifikován značkou a svým identifikátorem, který je v rámci konkrétní značky jedinečný, ovšem není jedinečný v celé tabulce.

-- Modely vozidel
CREATE TABLE model (
     znacka INTEGER,
     id INTEGER,
     nazev VARCHAR(64)
);
ALTER TABLE model ADD FOREIGN KEY (znacka)
REFERENCES znacka(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE model ADD PRIMARY KEY (znacka, id);
ALTER TABLE model ADD UNIQUE (znacka, nazev);

Data

--------+----+---------
 znacka | id |  nazev  
--------+----+---------
      1 |  1 | Fabia
      1 |  2 | Octavia
      1 |  3 | Felicia
      3 |  1 | Ibiza
      3 |  2 | Toledo
      2 |  4 | Polo

Díky této tabulce a jednoduchému spojení s tabulkou značek zjistíme, že např. kombinace 3-1 označuje Seat Ibizu.

SELECT z.nazev AS znacka, m.nazev AS model FROM model AS m
INNER JOIN znacka AS z ON z.id = m.znacka;

------------+---------
   znacka   |  model  
------------+---------
 Škoda      | Fabia   
 Škoda      | Octavia 
 Škoda      | Felicia 
 Seat       | Ibiza   
 Seat       | Toledo  
 Volkswagen | Polo    

Převodovka

-- Převodovky vozidel
CREATE TABLE prevodovka (
     znacka INTEGER,
     model INTEGER,
     id INTEGER,
     oznaceni VARCHAR(16)
);
ALTER TABLE prevodovka ADD FOREIGN KEY (znacka, model)
REFERENCES model(znacka, id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE prevodovka ADD PRIMARY KEY (znacka, model, id);
ALTER TABLE prevodovka ADD UNIQUE (znacka, model, oznaceni);

Data

--------+-------+----+----------
 znacka | model | id | oznaceni
--------+-------+----+----------
      1 |     1 |  0 |     NULL
      1 |     1 |  1 | 'AAA1'
      1 |     1 |  2 | 'AAA2'
      1 |     2 |  0 |     NULL
      1 |     2 |  1 | 'AAB1'
      1 |     2 |  2 | 'AAB2'
      1 |     3 |  0 |     NULL
      1 |     3 |  1 | 'ABB1'
      1 |     3 |  2 | 'ABB2'
      2 |     1 |  1 | 'BBB'
      2 |     2 |  1 | 'BBD'
      3 |     3 |  0 |     NULL
      3 |     1 |  1 | 'CCC'
      3 |     3 |  2 | 'CBB'
      3 |     3 |  3 | 'CCB'

Pro názornou ukázku vybereme všechny převodovky pro značku Škoda.

SELECT z.nazev AS znacka, p.oznaceni AS prevodovka FROM prevodovka AS p
INNER JOIN znacka AS z ON z.id=p.znacka
WHERE p.oznaceni IS NOT NULL AND z.nazev='Škoda';

--------+------------
 znacka | prevodovka 
--------+------------
 Škoda  | AAA1
 Škoda  | AAA2
 Škoda  | AAB1
 Škoda  | AAB2
 Škoda  | ABB1
 Škoda  | ABB2

Ve staré verzi tabulek by byl tento úkol trošku komplikovanější, ale zase ne tolik.

SELECT z.nazev AS znacka, p.oznaceni AS prevodovka FROM prevodovka AS p
INNER JOIN model AS m ON p.model = m.id
INNER JOIN znacka AS z ON m.znacka = z.id
WHERE z.nazev = 'Škoda';

Motor

Každý motor je určen pro konkrétní značku a model. Nepředpokládáme situaci, že by jeden typ motoru mohl být použit ve více modelech nebo dokonce ve více značkách.

-- Motory vozidel
CREATE TABLE motor (
     znacka INTEGER,
     model INTEGER,
     id INTEGER,
     oznaceni VARCHAR(16),
     typ CHAR(2),
     objem DECIMAL(3,1),
     vykon DECIMAL(3)
);
ALTER TABLE motor ADD FOREIGN KEY (znacka, model)
REFERENCES model (znacka, id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE motor ADD PRIMARY KEY (znacka, model, id);

Data

--------+-------+----+----------+------+-------+-------
 znacka | model | id | oznaceni |  typ | objem | vykon 
--------+-------+----+----------+------+-------+-------
      1 |     1 |  0 |     NULL | NULL |  NULL | NULL  
      1 |     1 |  1 | AA2      | D    |   1.4 |    50 
      1 |     1 |  2 | BB1      | L    |   1.6 |    75 
      1 |     3 |  0 |     NULL | NULL |  NULL | NULL
      1 |     3 |  1 | CC1      | L    |   1.8 |    75 
      1 |     3 |  2 | DD1      | L    |   1.8 |    96 

Vozidlo

Tato tabulka vytváří velmi silné integritní omezení, zamezující sestavení vozidla s převodovkou nebo motorem určeným pro jiný model vozidla, nebo dokonce značku.

Ve své podstatě se ani nemusíme odkazovat na tabulku model, konkrétní model vozidla je jednoznačně identifikováno kombinací motoru a převodovky.

-- Konkrétní model vozidla s určitým motorem a převodovkou
CREATE TABLE vozidlo (
     znacka INTEGER,
     model INTEGER,
     motor INTEGER,
     prevodovka INTEGER
);
ALTER TABLE vozidlo ADD FOREIGN KEY (znacka, model, motor)
REFERENCES motor (znacka, model, id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE vozidlo ADD FOREIGN KEY (znacka, model, prevodovka)
REFERENCES prevodovka (znacka, model, id)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE vozidlo ADD PRIMARY KEY (znacka, model, motor, prevodovka);

Data

--------+-------+-------+------------
 znacka | model | motor | prevodovka
--------+-------+-------+------------
      1 |     1 |     0 |          0 
      1 |     1 |     0 |          1
      1 |     1 |     0 |          2
      1 |     1 |     1 |          0
      1 |     1 |     2 |          0
      1 |     1 |     3 |          0
      1 |     1 |     1 |          2
      1 |     3 |     0 |          0
      1 |     3 |     0 |          1
      1 |     3 |     0 |          2
      1 |     3 |     0 |          3
      1 |     3 |     1 |          0
      1 |     3 |     2 |          0
      1 |     3 |     3 |          0
      1 |     3 |     1 |          1
      1 |     3 |     1 |          2

Na první pohled se zdá, že vznikne velké množství kombinací, ale pokud se zavede vhodná konvence označující např. model bez rozlišení převodovek, počet kombinací se rapidně sníží, takže např. z kódu 1-1-1-2 zjistíme, že se jedná o Škodu Fabii s motorem o objemu 1,4D s výkonem 50kW a převodovkou AAB, zatímco kód 1-1-1-0 označuje Škodu Fabii se stejným motorem, ale bez rozlišení převodovky. Kód 1-3-0-0 pak označuje Škodu Felicii bez rozlišení motoru a převodovky. Tomuto pravidlu bude vyhovovat velké množství výrobků a tím se zamezí velkému množství kombinací. Celou situaci lze ad absurdum dovést do krajností zavedením nulového modelu a nulové značky a následným přiřazením výrobku k této nulové značce.

Výrobek

-- Tabulka výrobků
CREATE TABLE vyrobek (
     id INTEGER,
     nazev VARCHAR(64)
);
ALTER TABLE vyrobek ADD PRIMARY KEY (id);

----+----------------------------
 id |          nazev
----+----------------------------
  1 | Utahovák na klínové řemeny
  2 | Stahovák na převodovku
  3 | Aretační trn

Vozidlo_výrobek

Tato tabulka přiřazuje výrobky ke konkrétním vozidlům, takže se okamžitě dozvíme, na které konkrétní modely lze konkrétní výrobek použít.

-- Spojení výrobků s vozidly
CREATE TABLE vozidlo_vyrobek (
     znacka INTEGER,
     model INTEGER,
     motor INTEGER,
     prevodovka INTEGER,
     vyrobek INTEGER,
);
ALTER TABLE vozidlo_vyrobek ADD FOREIGN KEY (znacka, model, motor, prevodovka)
REFERENCES vozidlo (znacka, model, motor, prevodovka)
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE vozidlo_vyrobek ADD FOREIGN KEY (vyrobek)
REFERENCES vyrobek (id)
ON DELETE CASCADE ON UPDATE CASCADE;

Data

--------+-------+-------+------------+---------
 znacka | model | motor | prevodovka | vyrobek
--------+-------+-------+------------+---------
      1 |     1 |     0 |          2 |       2
      1 |     3 |     0 |          0 |       2
      1 |     1 |     0 |          0 |       1
      1 |     3 |     0 |          0 |       1
      1 |     1 |     2 |          0 |       3
      1 |     1 |     3 |          0 |       3
      1 |     3 |     1 |          0 |       3
      1 |     3 |     3 |          0 |       3

A nyní názorný příklad, proč je tak na první pohled složitá tabulka výhodná. Řekněme, že potřebujeme seznam všech výrobků určených pro Škodovku.

SELECT v.nazev AS vyrobek FROM vyrobek AS v
INNER JOIN vozidlo_vyrobek AS vv ON v.id=vv.vyrobek
INNER JOIN znacka AS z ON z.id = vv.znacka
WHERE z.nazev = 'Škoda';

Podle předchozího schématu by byl dotaz trošku komplikovanější.

SELECT v.nazev AS vyrobek FROM vyrobek AS v
INNER JOIN vozidlo_vyrobek AS vv ON vv.vozidlo = v.id
INNER JOIN vozidlo AS vo ON vo.id = vv.vozidlo
INNER JOIN model AS m ON m.id = vo.model
INNER JOIN znacka AS z ON z.id = m.znacka
WHERE z.nazev = 'Škoda';

Závěr

Celý datový model by šel samozřejmě "scuknout" do třech tabulek, ale z nich bychom opravdu velmi obtížně zjišťovali informace, pro jaká vozidla je konkrétní výrobek vhodný nebo jaké výrobky jsou určené pro konkrétní typ vozidla. Základem dobrého datového modelu je velmi podrobná analýza vztahů mezi jednotlivými prvky systému.