DUM Název projektu Číslo projektu Číslo a název šablony klíčové aktivity Tematická oblast - téma Označení materiálu (přílohy)
Inovace ŠVP na OA a JŠ Třebíč CZ.1.07/1.5.00/34.0143 III/2 Inovace a zkvalitnění výuky prostřednictvím ICT Access II 13 RelaceN:N 1
Relace
Relace typu N:N
V relaci typu N:N odpovídá jednomu záznamu v tabulce A více záznamů v tabulce B a naopak jednomu záznamu v tabulce B více záznamů v tabulce A (někdy se používá označení M:N). To je možné pouze pomocí definice třetí tabulky (nazývané spojovací tabulka), jejíž primární klíč se skládá ze dvou polí – cizích z tabulek A a B. Relace N:N jsou ve skutečnosti dvě relace 1:N s třetí tabulkou. V relaci N:N jsou například tabulky Dodavatelé a Výrobky a tato relace je definována vytvořením dvou relací 1:N s tabulkou Objednávky. V tabulkách je potřeba mít vytvořen primární a cizí klíč: Primární klíč - jedno nebo více polí (sloupců), jejichž hodnota nebo kombinace hodnot jedinečným způsobem identifikuje každý záznam v tabulce. Primární klíč nepovoluje hodnoty Null a musí mít vždy jedinečný index. Slouží k vytvoření relace tabulky na cizí klíče v jiných tabulkách. Cizí klíč - jedno nebo více polí (sloupců) tabulky odkazující na jedno nebo více polí primárního klíče jiné tabulky. Cizí klíč vyjadřuje, jak spolu tabulky souvisejí.
Propojení si ukážeme na třech tabulkách z databázového souboru Objednavky.mdb.
Dodavatelé Číslo dodavatele Firma Adresa Město 1 Extrudo Bečice, s.r.o. Bečice 7 Týn nad Vltavou 2 DANONE, a.s. Konopišťská 905 Benešov 3 Intersnack, a.s. Jindrova 25 Praha 5
1
Objednávky Datum Text KódDodavatel KódVýrobek 1.9.2013 jogurt 2 D1 1.9.2013 smetana 2 D1 1.9.2013 chips 3 I1 2.9.2013 zelenina 1 E1 2.9.2013 sýr 1 E2 2.9.2013 lupeto 3 I4 3.9.2013 kaše 1 E4 3.9.2013 tvaroh 2 D2 3.9.2013 pom bar 3 I5
Výrobky Kód výrobku D1 D2 D3 D4 E1 E2 E3 E4 E5 E6 E7 I1 I2 I3 I4 I5
Název výrobku Jednotková cena Jednotky na skladě Activia bílá 12 226 Activia tvarohová – jahoda 13 1229 Kysaná smetana klasik 21 1331 Jogurt Prince-frutáci 11 1234 Jarní zelenina 150 g 20 117 3 druhy sýra 150 g 22 253 Bisquit perníkový polomáčený 180 g 45 120 Kaše jáhlová snídaňová 500 g 110 120 Kaše rýžová s jogurtem 500 g 120 215 BIO Tyčka kukuřičná natur 15 2276 CRISPINS BIO tyčka žitná 50 g 20 334 Bohemia Chips horská sůl 24 322 Bohemia chips solené 21 486 Brambůrky Grande 25 224 Lupeto snack solené 31 235 Pom bar – bramborové lupínky 34 439
Propojení – relaci provedeme dvakrát. 1. přes primární klíč = pole Číslo dodavatele z tabulky Dodavatelé a cizí klíč = pole KódDodavatel z tabulky Objednávky. 2. přes primární klíč = pole Kód výrobku z tabulky ýrobky a cizí klíč = pole KódVýrobek z tabulky Objednávky. Vidíme, že tabulka Objednávky má dvě relace (každá s jednou ze zbývajících tabulek). Při vytváření relace mezi tabulkami není nutné, aby pole, která jsou v relaci, měla shodné názvy. Pole v relaci však musí mít shodný typ dat.
2
Definování relace typu 1:N 1. Zavřete všechny otevřené tabulky. Relaci mezi otevřenými tabulkami nelze vytvořit ani upravit. 2. Přejděte do okna Databáze. 3. Klepněte na tlačítko Relace či vyberte volbu Nástroje - relace. 4. Pokud jste ještě v databázi nedefinovali žádné relace, zobrazí se automaticky dialogové okno Zobrazit tabulku. 5. Poklepejte na názvy všech tří požadovaných tabulek a poté zavřete dialogové okno Zobrazit tabulku. Chcete-li vytvořit relaci v rámci téže tabulky, přidejte tuto tabulku dvakrát. 6. Přetáhněte požadované pole z jedné tabulky na související pole v druhé tabulce. Přetahujte vždy pole s primárním klíčem na propojovací tabulku s cizím klíčem. Zobrazí se dialogové okno Upravit relace. Zkontrolujte názvy polí zobrazené v obou sloupcích. V případě potřeby proveďte požadované změny. V případě potřeby nastavte možnosti relace. ( Referenční integrita - pravidla, jejichž dodržení při přidávání, aktualizaci nebo odstraňování záznamů zachová definované relace mezi tabulkami). 7. Klepnutím na tlačítko Vytvořit potvrdíte vytvoření relace.
3
Výhody propojení tabulek si ukážeme na následujících dotazech (formuláře a sestavy si vytvoříme v příští lekci)
Vytvořte následující dotazy a uložte je pod příslušným názvem
1. Objednávky – vypíše všechny objednávky jak s výrobky tak s dodavateli a jejich adresami 2. Počet – vypočítá kolik výrobků bylo objednáno od každého z dodavatelů (využijte sekupení) 3. Počet s dodavatelem – zobrazí počet objednaných výrobků od dodavetelů i s jejich názvem 4. DPH – zobrazí objenávky s názvem výrobku, cenou a novou položkou DPH jako 20% z ceny výrobku 5. Zisk Extrudo – vypočte možný zisk za prodané zboží (= v objednávce) od firmy Extrudo (zisk je 15% z ceny výrobku) 6. Vytvor – vytvoří novou tabulku s názvem Intersnack, která bude obsahovat pouze objednané zboží od firmy Intersnack, a.s. 7. Intersnack – zobrazí pouze objednávky pro firmu Intersnack 8. Danone sleva – sníží u výrobků od Danone cenu o 10%. 9. Levné – vytvoří novou tabulku s levnými výrobky (cena do 20Kč) i s dodavateli 10. Smaz – odstraň z tabulky Objednávky objednávky od firmy Danone (vyzkoušejte varianty relací s a bez referenční integrity) 4
Př. ad 1:
5
