Cvičení předmětu KIV/DBM1
13. cvičení, cvičící: Ing. Tomáš Kotouč

Procvičování příkladů ke zkoušce

• Příklad 1 .. Navrhněte E-R-A model pro systém umožňující vystavovat účet v restauraci. Účet nám bude obsahovat jméno a příjmení a adresu zákazníka, datum návštěvy a kdo ho obsluhoval, jednotlivé položky útraty s názvem, cenou za jednotku a počtem jednotek. Předpokládejme, že se jedná o dobrou restauraci, kterou navštěvují stálí hosté a položky zboží, že jsou také stálé, takže ani jedno nechceme neustále opisovat.
  • Máme tedy vést samostatně zákazníky. Tabulka ZAKAZNICI nám bude obsahovat položky JMENO, PRIJMENI, ULICE_CISLO, OBEC, PSC. Protože na stejné adrese se nám stejné jméno a příjmení může vyskytovat vícekrát (např. otec a syn se stejným jménem) přidáme ještě položku DATUM_NAROZENI a trojice JMENO-PRIJMENI-DATUM_NAROZENI nám bude tvořit klíč této tabulky.
  • Samostatně máme vést také informace o zboží např. v tabulce ZBOZI s atributy NAZEV_ZBOZI a CENA_ZA_JEDNOTKU. O položce NAZEV můžeme předpokládat, že bude jedinečná a tak se stane klíčem této tabulky.
  • Je třeba evidovat návštevu zákazníka v restauraci. Tu budeme evidovat v tabulce NAVSTEVA s položkami DATUM_NAVSTEVY, OBSLUHOVAL a položkami, které mi jednoznačně určují zákazníka, tedy JMENO, PRIJMENI a DATUM_NAROZENI. Klíčem v této tabulce bude spojení zákazník a datum, tedy atributy JMENO, PRIJMENI, DATUM_NAROZENI a DATUM_NAVSTEVY.
  • Ještě nám zbývá evidovat položky, které zákazník spotřeboval. Zákazník v daný den může spotřebovat více zboží a dané zboží může být konzumováno více zákazníky, takže se jedná o rozklad vazby M:N mezi tabulkami NAVSTEVA a ZBOZI. Tabulku, která tímto rokladem vznikne nazveme např. SPOTREBA a bude mít položky určující zákazníka v daném dni, tedy klíč tabulky NAVSTEVA a položky určující zboží, tedy klíč z tabulky ZBOZI a ještě položku určující spotřebované množství jednotek = POCET. Klíčem zde bude spojení atributů tvořících klíč v tabulce NAVSTEVA a tabulce ZBOZI.
  Takže výsledek je:
  • ZAKAZNICI (JMENO, PRIJMENI, DATUM_NAROZENI, ULICE_CISLO, OBEC, PSC).
  • ZBOZI (NAZEV_ZBOZI, CENA_ZA_JEDNOTKU)
  • NAVSTEVA (DATUM_NAVSTEVY, OBSLUHOVAL, JMENO, PRIJMENI, DATUM_NAROZENI).
  • SPOTREBA (DATUM_NAVSTEVY, JMENO, PRIJMENI, DATUM_NAROZENI, NAZEV_ZBOZI, POCET).
  • Tabulka NAVSTEVA je s tabulkou ZAKAZNIK propojena přes atributy JMENO, PRIJMENI a DATUM_NAROZENI.
  • Tabulka SPOTREBA je s tabulkou NAVSTEVA propojena přes atributy JMENO, PRIJMENI, DATUM_NAROZENI a DATUM_NAVSTEVY.
  • Tabulka SPOTREBA je s tabulkou ZBOZI propojena přes atribut NAZEV_ZBOZI.

• Příklad 2 .. Mějme relaci R[Jmeno, Prijmeni, Misto, Narozen]:
  
  

  Jmeno	Prijmeni	Misto	Narozen
  Anna	Nováková	Plzeň	1970
  Jan	Holub	Plzeň	1980
  Martin	Knedlík	Olomouc	1965
  Martin	Holub	Brno	1975
  Jan	Holub	Olomouc	1980

  Proveďte projekci R[Prijmeni, Narozen]:
  Takže naše nová tabulka má obsahovat pouze sloupce Prijmeni a Narozen. Nestačí však jen odstranit zbylé sloupce, ale i zkontrolovat, zda některý záznam v nové tabulce nebude uveden vícekrát. V našem případě by se zde dvakrát opakoval záznam Holub - 1980, takže ten v nové tabulce uvedeme jen jednou:
  
  

  Prijmeni	Narozen
  Nováková	1970
  Holub	1980
  Knedlík	1965
  Holub	1975

• Příklad 3 .. Mějme relaci R[Jmeno, Prijmeni, Misto, Narozen]. Předpokládejme, že data v znázorněné tabulce jsou reprezentativní pro určení závislosti (= na základě této ukázky záznamů lze určit, které atributy (sloupce) tvoří klíč tabulky (jednoznačně určují každý záznam)):
  
  

  Jmeno	Prijmeni	Misto	Narozen
  Anna	Nováková	Plzeň	1970
  Jan	Holub	Plzeň	1980
  Martin	Knedlík	Olomouc	1965
  Martin	Holub	Brno	1975
  Jan	Holub	Olomouc	1980

  V relaci R určete klíč:
  Jednoduše řečeno, klíčem nějaké tabulky bereme nejmenší množinu jejích atributů, které jednoznačně určují každý záznam.
  Začínáme tedy od jednotlivých sloupců a pokud nenajdeme žádný samostatný sloupec, který by mohl být klíčem, pokračujeme kombinací sloupců až po kombinaci všech sloupců:

  • Sloupec Jmeno sám o sobě nemůže být klíčem, protože se nám zde opakuje jméno Jan a jméno Martin.
  • Sloupec Prijmeni sám o sobě nemůže být klíčem, protože se nám zde opakuje příjmení Holub.
  • Sloupec Misto sám o sobě nemůže být klíčem, protože se nám zde opakuje město Plzeň a město Olomouc.
  • Sloupec Narozen sám o sobě nemůže být klíčem, protože se nám zde opakuje rok 1980.
  • Kombinace dvou sloupců Jmeno-Prijmeni nemůže být klíčem, protože se nám zde opakuje spojení Jan-Holub.
  • Kombinace dvou sloupců Jmeno-Rok nemůže být klíčem, protože se nám zde opakuje spojení Jan-1980.
  • Kombinace dvou sloupců Prijmeni-Rok nemůže být klíčem, protože se nám zde opakuje spojení Holub-1980. Toto bylo ukazováno u příkladu 2.
  • Kombinace dvou sloupců Jmeno-Misto MŮŽE být klíčem.
  • Kombinace dvou sloupců Prijmeni-Misto MŮŽE být klíčem.
  • Kombinace dvou sloupců Misto-Rok MŮŽE být klíčem.
  Protože jsme nalezli kombinaci dvou sloupců, které mohou tvořit klíč, s dalším hledáním už nepokračujeme. V praxi se takto sloupce většinou nehledají. Kombinace atributů, které budou tvořit klíč nám většinou vyplyne ze zadání úlohy (např. {rodné číslo} u osob nebo {číslo faktury, rok} u faktur apod.).

  V relaci R rozhodněte, zda se nachází v 1NF, 2NF a 3NF a proč:
  Dejme tomu, že si jako klíč vybereme atributy Jmeno-Misto.

  • V 1. normální formě se relace nachází, pokud žádný její atribut není zároveň relací (tabulkou). To není, takže naše relace se nachází v 1.NF.
  • V 2. normální formě se relace nachází, jestliže je v 1NF a jestliže každý atribut, který nepatří klíči relace R, silně závisí na klíči R. Například, pokud by u každého jména="Jan" bylo příjmení="Holub" a u každého jména="Martin" bylo příjmení="Knedlík" atd., potom by tabulka nebyla ve 2.NF a my bychom ji rozdělili na dvě, kde v první by zmizel sloupec Prijmeni a druhá tabulka by obsahovala jen sloupce Jmeno-Prijmeni a každou dvojici hodnoty jen jednou. V našem případě tomu tak není ani u atributu Prijmeni ani u atributu Narozen a zároveň je relace v 1.NF, takže naše relace se nachází v 2.NF.
  • V 3. normální formě se relace nachází, jestliže je v 2NF a jestliže žádný atribut, který není složkou klíče relace R, není tranzitivně závislý na klíči relace R. V našem případě nám atribut Prijmeni závisí na atributu Narozen, protože pro každou hodnotu atributu Narozen najdeme v atributu Prijmeni stejnou hodnotu (hodnota 1980-Holub). Přitom atribut Prijmeni není součástí klíče, ale silně závisí na klíči. Takže naše relace se NEnachází ve 3.NF. A došlo by k rozpadu na dvě tabulky, které by byli spojeny přes atribut Narozen:
    
    

    Jmeno Misto Narozen Anna Plzeň 1970 Jan Plzeň 1980 Martin Olomouc 1965 Martin Brno 1975 Jan Olomouc 1980

    N:1

    Narozen Prijmeni 1970 Nováková 1980 Holub 1965 Knedlík 1975 Holub

  Takže toto spojení dvou relací R (Jmeno, Misto, Narozen) a X (Narozen, Prijmeni) nám již splňuje 1.NF i 2.NF i 3.NF.

• Konzultace k semestrálním pracem.
• Odevzdávání semestrálních prací.