Složené klíče

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

vozidla-1.png

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.

  1. SELECT z.nazev, mo.nazev, v.nazev
  2. FROM vozidlo_vyrobek vv
  3. INNER JOIN vozidlo v ON vv.vozidlo=v.id
  4. INNER JOIN model mo ON mo.id=vv.model
  5. 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

vozidla-2

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

  1. -- Značka vozidel
  2. CREATE TABLE znacka (
  3. id INTEGER,
  4. nazev VARCHAR(32)
  5. UNIQUE (nazev)
  6. );
  7. ALTER TABLE znacka ADD PRIMARY KEY (id);
  8. 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.

  1. -- Modely vozidel
  2. CREATE TABLE model (
  3. znacka INTEGER,
  4. id INTEGER,
  5. nazev VARCHAR(64)
  6. );
  7. ALTER TABLE model ADD FOREIGN KEY (znacka)
  8. REFERENCES znacka(id) ON DELETE CASCADE ON UPDATE CASCADE;
  9. ALTER TABLE model ADD PRIMARY KEY (znacka, id);
  10. 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.

  1. SELECT z.nazev AS znacka, m.nazev AS model FROM model AS m
  2. 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

  1. -- Převodovky vozidel
  2. CREATE TABLE prevodovka (
  3. znacka INTEGER,
  4. model INTEGER,
  5. id INTEGER,
  6. oznaceni VARCHAR(16)
  7. );
  8. ALTER TABLE prevodovka ADD FOREIGN KEY (znacka, model)
  9. REFERENCES model(znacka, id) ON DELETE CASCADE ON UPDATE CASCADE;
  10. ALTER TABLE prevodovka ADD PRIMARY KEY (znacka, model, id);
  11. 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.

  1. SELECT z.nazev AS znacka, p.oznaceni AS prevodovka FROM prevodovka AS p
  2. INNER JOIN znacka AS z ON z.id=p.znacka
  3. 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.

  1. SELECT z.nazev AS znacka, p.oznaceni AS prevodovka FROM prevodovka AS p
  2. INNER JOIN model AS m ON p.model = m.id
  3. INNER JOIN znacka AS z ON m.znacka = z.id
  4. 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.

  1. -- Motory vozidel
  2. CREATE TABLE motor (
  3. znacka INTEGER,
  4. model INTEGER,
  5. id INTEGER,
  6. oznaceni VARCHAR(16),
  7. typ CHAR(2),
  8. objem DECIMAL(3,1),
  9. vykon DECIMAL(3)
  10. );
  11. ALTER TABLE motor ADD FOREIGN KEY (znacka, model)
  12. REFERENCES model (znacka, id)
  13. ON DELETE CASCADE ON UPDATE CASCADE;
  14. 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.

  1. -- Konkrétní model vozidla s určitým motorem a převodovkou
  2. CREATE TABLE vozidlo (
  3. znacka INTEGER,
  4. model INTEGER,
  5. motor INTEGER,
  6. prevodovka INTEGER
  7. );
  8. ALTER TABLE vozidlo ADD FOREIGN KEY (znacka, model, motor)
  9. REFERENCES motor (znacka, model, id)
  10. ON DELETE CASCADE ON UPDATE CASCADE;
  11. ALTER TABLE vozidlo ADD FOREIGN KEY (znacka, model, prevodovka)
  12. REFERENCES prevodovka (znacka, model, id)
  13. ON DELETE CASCADE ON UPDATE CASCADE;
  14. 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

  1. -- Tabulka výrobků
  2. CREATE TABLE vyrobek (
  3. id INTEGER,
  4. nazev VARCHAR(64)
  5. );
  6. 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.

  1. -- Spojení výrobků s vozidly
  2. CREATE TABLE vozidlo_vyrobek (
  3. znacka INTEGER,
  4. model INTEGER,
  5. motor INTEGER,
  6. prevodovka INTEGER,
  7. vyrobek INTEGER,
  8. );
  9. ALTER TABLE vozidlo_vyrobek ADD FOREIGN KEY (znacka, model, motor, prevodovka)
  10. REFERENCES vozidlo (znacka, model, motor, prevodovka)
  11. ON DELETE CASCADE ON UPDATE CASCADE;
  12. ALTER TABLE vozidlo_vyrobek ADD FOREIGN KEY (vyrobek)
  13. REFERENCES vyrobek (id)
  14. 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.

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

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

  1. SELECT v.nazev AS vyrobek FROM vyrobek AS v
  2. INNER JOIN vozidlo_vyrobek AS vv ON vv.vozidlo = v.id
  3. INNER JOIN vozidlo AS vo ON vo.id = vv.vozidlo
  4. INNER JOIN model AS m ON m.id = vo.model
  5. INNER JOIN znacka AS z ON z.id = m.znacka
  6. 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.

Poslat nový komentář

Obsah tohoto pole je soukromý a nebude veřejně zobrazen.
  • Allowed HTML tags: <img> <a> <em> <strong> <cite> <code> <var> <abbr> <acronym> <kbd> <ul> <ol> <li> <dl> <dt> <dd> <blockcode> <h1> <h2> <h3> <h4> <h5> <table> <tbody> <thead> <tfoot> <tr> <td> <th> <p>
  • Řádky a odstavce se zalomí automaticky.
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>.
  • You may link to Gallery2 items on this site using a special syntax.

Více informací o možnostech formátování

CAPTCHA
Je mi to moc líto, ale kvůli spamovacím robotům jsem musel zavést toto nepopulární opatření
Image CAPTCHA
Copy the characters (respecting upper/lower case) from the image.