Tvorba návrhu relačnej databázy

Referenčné obmedzenie

Aby sme pochopili, ako relačný databázový systém zaisťuje platnosť relácií pomocou referenčných obmedzení, musíme si najprv vysvetliť, čo sú to tzv. cudzie kľúče.

V prípade, že pomocou tabuliek v relačnej databáze implementujeme reláciu typu jedna ak viacerým, potom do tabuľky na strane „viacerým“ musíme zapísať stĺpec alebo množinu stĺpcov, ktorý asociuje (priraďuje) príslušný riadok s riadkom rodičovskej tabuľky, teda tabuľky na strane jedna a ten práve nazývame cudzí kľúč. (foreign key, FK). Názov stĺpcov cudzieho kľúča sa preberajú z názvov stĺpcov v príslušnej “cudzej” tabuľky. V tabuľke Objednávky z obrázku 2.6 je tak napríklad stĺpec Číslo zamestnanca cudzím kľúčom z tabuľky Zamestnanci a stĺpec Kód zákazníka je cudzím kľúčom z tabuľky Zákazníci.

Vo väčšine relačných databáz musí byť cudzím kľúčom buď primárny kľúč rodičovskej tabuľky, alebo množina stĺpcov, pre ktoré je definovaný jedinečný index. Dôvodom je opäť efektivita činnosti databázy. Väčšina ľudí má najradšej, keď sa názov stĺpca cudzieho kľúča zhoduje s názvom zodpovedajúceho stĺpca primárneho kľúča rodičovskej tabuľky, ale opäť existujú aj opačné názory a to najmä preto, že so stĺpcami rovnakého názvu (podobného) sa v dotazovacom jazyku ťažšie pracuje.

Každá relácia medzi entinami z konceptuálneho návrhu sa v logickom návrhu premieta do referenčného obmedzenia. Pod pojmom referenčné obmedzenie alebo obmedzenie referenčnej integrity rozumieme také obmedzenie, ktoré zaisťuje reláciu medzi tabuľkami v relačnej databáze. Zaisťovať v tomto prípade znamená, že relačný databázový systém automaticky kontroluje, či ku každej hodnote cudzieho kľúča v závislej tabuľke existuje zodpovedajúca hodnota primárneho kľúča v rodičovskej (riadiacej) tabuľke.

Microsoft Access ponúka pre stĺpec s cudzím kľúčom jednu veľmi príjemnú funkciu, na ktorú je ale nutné si najprv zvyknúť. Pri definícii referenčného obmedzenia môžeme vyžiadať automatické prehľadávanie riadkov rodičovskej tabuľky. Na obrázku 2.6 má druhý stĺpec tabuľky označenie Kód zákazníka. Na obrázku 2.5 vidíme ale, že na pozícii druhého stĺpca je miesto kódu zákazníka jeho meno a názov stĺpca Zákazník. V prípade, že do stĺpca Zákazník v niektorom z riadkov klikneme myšou, objaví sa šípka rozbaľovacieho zoznamu, ktorý ponúka na výber rodiča vybraného riadku objednávok z platných zákazníkov (teda z tabuľky Zákazníci). Podobne i v stĺpci Číslo zamestnanca je v skutočnosti zobrazené meno zákazníka. Pre databázového užívateľa je táto funkcia veľmi príjemná a pohodlná a popri tom nedovoľuje priradiť k objednávke neexistujúceho (neplatného) zákazníka alebo zamestnanca. Súčasne sa s tým v tabuľke skrývajú skutočné hodnoty cudzieho kľúča.

Na nasledujúcom obrázku 2.7 je vyhľadávanie hodnôt stĺpcov v tabuľke Objednávky vypustené, tak že v stĺpcoch Číslo zamestnanca a Kód zákazníka vidíme skutočné kódové hodnoty cudzieho kľúča.

Obrázok 2.7: Tabuľka Objednávky so zobrazením cudzích kľúčov

Pri aktualizácii tabuľky Objednávky z obrázku 2.7 musí relačný databázový systém zaistiť platnosť referenčných obmedzení, ktoré sú nad touto tabuľkou definované. Pôvab databázových obmedzení spočíva v tom, že sú automatické a nedajú sa teda obísť – pokiaľ ich databázový administrátor nezablokuje alebo nevymaže. V rámci zaistenia referenčných obmedzení musí relačný databázový systém obsluhovať tieto konkrétne udalosti:

• V prípade, že sa pokúsime do závislej tabuľky (t.j. tabuľky cudzieho kľúča) vložiť záznam, ku ktorému neexistuje zodpovedajúci riadok rodičovskej tabuľky, musí v tomto prípade systém túto požiadavku na vloženie dát odmietnuť. Pokiaľ sa napríklad do tabuľky Objednávky pokúsime vložiť riadok s hodnotou Číslo zamestnanca 12345, pozrie sa databázový systém do tabuľky Zamestnanci a zistí, či je v nej definovaný zamestnanec s číslom 12345. Ak nie je, požiadavku na vloženie dát odmietne.
• Podobne sa správa databázový systém pri aktualizácii dát: ak sa pokúsime uskutočniť v závislej tabuľke aktualizáciu hodnoty cudzieho kľúča, pričom nová hodnota kľúča nie je v rodičovskej tabuľke doposiaľ definovaná, bude požiadavka na aktualizáciu odmietnutá. Ak sa napríklad pokúsime zmeniť Číslo zamestnanca v objednávke 10248 z 5 na 12345, pozrie sa databázový systém opäť do tabuľky Zamestnanci a zistí, či je v nej definovaný zamestnanec s číslom 12345, pokiaľ v nej nie je definovaný, požiadavka na zápis bude odmietnutá.
• Ak chceme odstrániť riadok rodičovskej tabuľky, ku ktorému existujú zviazané (závislé) riadky v jednej alebo vo viacerých závislých tabuľkách, musíme buď spolu s rodičovským riadkom odstrániť taktiež príslušné riadky v závislej tabuľke, alebo databázový systém musí zamietnuť celú požiadavku na odstránenie. Väčšina relačných databázových systémov ponúka aj prvú popísanú možnosť, teda automatické odstránenie závislých záznamov, ktoré sa nazýva kaskádové odstránenie.

Pozrime sa na tabuľky Objednávky a Rozpis objednávok: pokiaľ odstraňujeme z databázovej tabuľky Obednávka nejakú objednávku, má zmysel odstrániť s objednávkou v tom istom kroku i všetky ostatné (jednotlivé) riadky tejto objednávky (tabuľka Rozpis objednávok). V prípade tabuľky Zamestnanci toto správanie požadovať nebudeme: naopak, ak zadáme napríklad odstránenie zamestnanca číslo 5 z tabuľky Zamestnanci (napríklad preto, že už v spoločnosti nepracuje), musí relačný databázový systém skontrolovať, či sa v tabuľke Objednávky nenachádzajú nejaké riadky zadané týmto zamestnancom, a pokiaľ tu tieto riadky sú, odstránenie zamestnanca odmietne. Zo všeobecného hľadiska by automatické odstránenie všetkých objednávok vybavených bývalým zamestnancom rozhodne nemalo zmysel.       

Obrázok 2.8: Vzťah medzi tabuľkami Objednávky a Rozpis objednávok

Vo väčšine relačných databázových systémoch definujeme referenčné obmedzenie pomocou zvláštneho príkazu jazyka SQL (je to jazyk, pomocou ktorého komunikujeme s relačnou databázou). Viacerý z výrobcov databázových systémov ponúkajú pre definíciu databázových objektov, napr. referenčných obmedzení, vhodné ovládacie prvky grafického užívateľského rozhrania (GUI). V Oracle a v SQL Server sa tieto ovládacie prvky či panely nachádzajú v nástroji Enterprise Manager (resp. Management Studio v SQL Serveri 2005), zatiaľ čo v Microsoft Access sú priam súčasťou aplikácie. Na nasledujúcom obrázku 2.8 vidíme okno Relácie v ktorom definujeme práve referenčné obmedzenie

Obrázok 2.8 Panel Relácie v Microsoft Access

Pre zjednodušenie je pritom na obrázku 2.8 znázornená len tabuľka Objednávky a k nej dve rodičovské tabuľky, Zamestnanci a Zákazníci. Referenčné obmedzenia sú v tomto prípade vyjadrené čiarami, zakončenými silnejšou líniou a číslicou jedna pri rodičovskej tabuľke, respektíve symbolom nekonečna pri závislej tabuľke. Definovať tieto obmedzenia je veľmi jednoduché: stačí potiahnuť myšou za názov primárneho kľúča z rodičovskej tabuľky na názov cudzieho kľúča v závislej tabuľke. Potom sa následne zobrazí dialóg Upraviť relácie, v ktorom môžeme zmeniť možnosti definovaného referenčného obmedzenia (Obrázok 2.9).

Obrázok 2.9 Dialóg Upraviť relácie v MS Access

Pri hornom okraji dialógu Upraviť relácie sú uvedené názvy obidvoch tabuliek – na ľavej strane je rodičovská tabuľka a na pravej strane “súvisiaca”, teda závislá tabuľka (toto vám napovie aj popis v spodnej časti dialógového okna). Pod názvom každej z tabuliek môžete vybrať názvy stĺpcov, z ktorých sa skladá primárny a cudzí kľúč – na obrázku 2.9 je to stĺpec Kód Zákazníka, ktorý je primárnym kľúčom v tabuľke Zákazníci a cudzím kľúčom v tabuľke Objednávky. Zaškrtávacie políčka majú tento význam:

• Zaistiť referenčnú integritu. Pokiaľ je toto políčko zaškrtnuté, bude MS Access zaisťovať toto obmedzenie. Ako náhle zaškrtnutie zrušíme, nebude systém platnosť relácie zaisťovať.
• Aktualizácia súvisiacich polí v kaskáde. Zaškrtnutie tohto políčka znamená, že sa pri akejkoľvek aktualizácii hodnoty primárneho kľúča v rodičovskej tabuľke, automatiky zrealizuje zodpovedajúca aktualizácia hodnôt cudzích kľúčov vo všetkých zviazaných záznamoch. Aktualizácia primárneho kľúča je ovšem dosť málo realizovaná operácia.
• Odstránenie súvisiacich polí v kaskáde. Toto políčko znamená, že sa pri odstránení riadku v rodičovskej tabuľke automaticky odstráni taktiež kaskáda všetkých záznamov v závislej tabuľke. V tomto prípade je potrebné byť opatrný: niekedy je táto funkcia veľmi užitočná, napríklad pri už pred tým popísaného relačného obmedzenia medzi tabuľkami Objednávky a Rozpis objednávok, zatiaľ čo niekedy môže viesť k nežiaducej a dokonca katastrofálnej strate dát – napríklad pri odstraňovaní zamestnanca by sa z databázy automaticky odstránili i všetky objednávky, ktoré tento zamestnanec prace vybavil.