Databázy, časť 2 Model vzťahu medzi entitami Spontánny • divoký • a • koláč

Kto je spontánny, kto je divoký a predovšetkým: kde je koláč?

V prvej časti série sme zistili, že používanie databáz je dobrá vec, pretože vývojár už nemusí riešiť základné funkcie ukladania údajov. Už sme však videli, že rozumné modelovanie údajov je nevyhnutné. Alebo inými slovami:

Skôr ako môže začať zmysluplný návrh databázy, je nevyhnutné mať jasno v tej časti reálneho sveta, ktorá sa má mapovať. Na tento účel sa odporúča model entity-relationship zavedený Peterom Chenom v roku 1976, ktorý popisuje entity, teda veci v reálnom svete, ich vlastnosti a vzťahy medzi nimi. V tomto článku sa ponoríme do základov tohto modelu. V učebnici od Kempera a Eicklera sa o modeli vzťahov medzi entitami hovorí v kapitole 2.

Pretože skutočné ukladanie dát v bežnom DBMS je založené na relačnom modeli, t. J. V tabuľkovej forme, musíme sa tiež zaoberať tým, ako previesť model vzťahu entít do relačného modelu. V mnohých prípadoch je to celkom jednoduché a vedie to k dobrým databázovým schémam, ktoré neobsahujú problémy, ako je prepúšťanie, zdôraznené v prvom článku série.

Model E/R

Aby sme mohli modelovať časť skutočného sveta, ktorá nás zaujíma, musíme si uvedomiť nasledujúce body:

Aké veci existujú v skutočnom svete?
Aké sú ich vlastnosti?
Ako spolu súvisia?

Veci spomínané v skutočnom svete sa v modeli vzťahov medzi entitami (alebo skrátene E/R model) nazývajú entity a vlastnosti sa nazývajú atribúty.

Množina podobných entít sa tiež označuje ako typ entity. Podobne sa hovorí aj o typoch vzťahov, ak sa myslí všeobecný vzťah medzi dvoma typmi entít.

Aby sme to objasnili, vráťme sa napríklad k prvej časti: zoznamu adries.

Pokiaľ ide o tri práve spomenuté otázky, môžeme urobiť nasledujúce pozorovania: Veci v skutočnom svete, ktoré patria do nášho zoznamu adries, sú predovšetkým ľudia. Ľudia žijú v bytoch a byty sú miestami. Charakteristiky osôb, ktoré sú relevantné pre náš zoznam adries, sú priezviská a krstné mená. Ulica a číslo domu sú majetkom bytu a miesto má názov miesta a PSČ.

V notácii Chen sú typy entít označené obdĺžnikmi a typy vzťahov diamantmi. Atribúty sú v diagrame vzťahov entít zvyčajne reprezentované elipsami. Ak existujú atribúty vhodné na jednoznačnú identifikáciu jednotlivej entity, možno to označiť podčiarknutím. Takéto atribúty sa nazývajú kľúčové atribúty. V našom príklade je poštové smerovacie číslo miesta kľúčovým atribútom, pretože miesto je jasne určené, ak je poštové smerovacie číslo známe.

Schéma E/R pre zoznam adries

Vzťahy medzi typmi entít možno charakterizovať ešte presnejšie. Ako sme diskutovali v našom príklade v minulej časti, jedna osoba môže mať aj niekoľko bytov. V jednom byte môže bývať niekoľko ľudí. Jedná sa o takzvaný vzťah N: M: entita môže byť spojená s ľubovoľným počtom entít na druhej strane a naopak.

Na druhej strane, vzťah medzi domovom a lokalitou je vzťah 1: N: Na jednom mieste môže byť niekoľko apartmánov, ale byt je vždy len na jednom mieste.

Implementácia v tabuľkách

Relačné systémy správy databáz (RDBMS) ukladajú údaje do tabuliek. Pre praktické použitie teda musíme zodpovedajúcim spôsobom implementovať model E/R. Je vidieť, že to, čo chceme ukladať, sú vlastnosti entít. Z hľadiska tabuliek sa scvrkáva na atribúty, ktoré sa stávajú stĺpcami tabuľky. Pre každý typ entity sa vytvorí samostatná tabuľka.

Typy entít

Zatiaľ máme tabuľku pre ľudí, v ktorej sú zadané priezviská a krstné mená, tabuľku pre byty so stĺpcami pre ulice a čísla domov a tabuľku pre mestá so stĺpcami pre PSČ a názov miesta. Jednotlivé osoby, byty a miesta, t. J. Jednotlivé subjekty, zodpovedajú riadkom v tabuľkách.

1: N vzťahy

Teraz je ešte potrebné zmapovať vzťahy. V prípade apartmánov a miest je mapovanie vzťahu priame. Pretože byt sa môže nachádzať iba na maximálne jednom mieste a poštové smerovacie číslo ho jasne definuje, stačí do tabuľky pre byty zahrnúť ďalší stĺpec pre PSČ. Hodnota v tomto stĺpci potom slúži ako odkaz na umiestnenie. Názov miesta by sa naopak nemal kopírovať do tabuľky apartmánov, inak by sa vytvorila nadbytočnosť: Skutočnosť, že určité PSČ patrí určitému miestu, je už uvedená v tabuľke Miesto a inde by sa nemala opakovať.

Ilustrácia vzťahu 1: N medzi bytmi a miestami je taká nekomplikovaná, pretože kľúč s PSČ je už k dispozícii a tento kľúč je možné použiť priamo ako referenciu v tabuľke Apartmány, pretože byt môže byť iba na jednom mieste. môcť. Stĺpec PSČ v tabuľke Mesto sa tiež označuje ako primárny kľúč (PK). Primárny kľúč jednoznačne identifikuje riadok v tabuľke. V tabuľke Apartmán sa PSČ stane cudzím kľúčom (FK). Poštové smerovacie číslo nie je majetkom samotného bytu, ale miesta, kde sa byt nachádza. Z tohto dôvodu sa v modeli E/R nezaznamenáva PSČ ako atribút bytu! Stĺpec cudzieho kľúča sa používa iba na nadviazanie vzťahu medzi dvoma entitami.

Je dôležité, aby nadviazanie vzťahu prostredníctvom jednoduchého stĺpca cudzieho kľúča fungovalo, iba ak cudzí kľúč zabalíme na stranu N v prípade vzťahov 1: N. Pretože na jednom mieste je niekoľko bytov, nestačil by na znázornenie vzťahu jeden stĺpec v tabuľke miest!

Umelé kľúče

V prípade vzťahu medzi ľuďmi a domácnosťami je preklad vzťahu do tabuliek trochu komplikovanejší. V prvom rade sme v modeli E/R nezaznamenali žiadne vlastnosti, ktoré by jednoznačne identifikovali ľudí alebo byty. V prvej časti sme už uvažovali o možnosti jednoduchého priradenia čísel na tento účel. Potom môžeme rozlíšiť dvoch ľudí s rovnakým menom, ktorí sú stále rôznymi ľuďmi, ak im priradíme rôzne čísla. Takéto číslo je tiež známe ako umelý kľúč alebo náhradný kľúč. Nie je skutočným majetkom subjektu, ale pridávame ho sami a slúži iba na jednoznačnú identifikáciu.

Podľa poštového smerovacieho čísla miesta by sa dalo samozrejme namietať, že je to vlastne tiež umelý kľúč, pretože poštové smerovacie číslo bolo vyrobené ľuďmi a pridelené ľubovoľne. Generovanie poštového smerovacieho čísla je však mimo našu kontrolu, ako to špecifikovala iná agentúra (konkrétne pošta). Z tohto dôvodu by sa PSČ mohlo považovať za prirodzený kľúč. O umelom kľúči by sa dalo hovoriť, iba ak by sme tento stĺpec kľúča pridali dodatočne a ten by pravdepodobne nebol zahrnutý v pôvodnom E/R diagrame. Mimochodom, nie je potrebné, aby sme pri vkladaní údajov kockovali konkrétnu hodnotu umelého kľúča. DBMS môže vygenerovať hodnotu pre samotný umelý kľúč. To má výhodu napríklad v tom, že ak existuje viac operácií vkladania súčasne, potom dvaja používatelia nemôžu zvoliť rovnakú hodnotu, čo by viedlo k chybám.

N: M vzťahy

Späť v našom príklade najskôr pridáme stĺpec pre umelý primárny kľúč do tabuľky pre osoby a do tabuľky pre byty, nazývame ich „PNr“ pre číslo osoby a „WNr“ pre číslo bytu . Teraz môžeme pomocou týchto klávesov zobraziť vzťah medzi osobou a jej domovom. Rovnako ako v prípade vzťahu jeden na viacerých však nemôžeme jednoducho nastaviť stĺpec ako cudzí kľúč na obidvoch stranách. Osoba môže mať podľa nášho koncepčného modelu viac bytov, takže sa tu nemôžeme zaobísť s jediným stĺpcom. Podľa nášho modelu však v jednom byte môže bývať niekoľko ľudí, takže v tabuľke Apartmán by nepomohla ani kolónka cudzieho kľúča.

Riešením je vytvorenie samostatnej tabuľky pre „životy“ typu vzťahu N: M. V tejto takzvanej tabuľke vzťahov sa pre každý vzťah medzi osobou a bytom vytvorí riadok. Stĺpce tabuľky vzťahov sú stĺpce cudzích kľúčov pre všetky typy entít zapojených do typu vzťahu. V našom príklade potrebujeme tabuľku „žije“ so stĺpcami „PNr“ a „WNr“. Napríklad ak osoba s číslom 2 teraz žije v byte s číslom 1, zadáme túto kombináciu do riadku v tabuľke vzťahov.

Zložený primárny kľúč

Nepotrebujeme umelý primárny kľúč pre samotnú tabuľku vzťahov. Pretože je však kombinácia čísla osoby a čísla bytu v tabuľke vzťahov jedinečná, možno ju použiť ako zložený primárny kľúč. (Koniec koncov, jedna osoba môže bývať v rôznych bytoch a rôzni ľudia v jednom byte, ale skutočnosť, že osoba býva v tom istom byte niekoľkokrát, by nebola rozumnou skutočnosťou, a preto by nemala byť uvedená v tabuľke.)

Tabuľky pre databázu adries

Atribúty typu vzťahu

Mimochodom, typy vzťahov môžu mať aj vlastnosti. Ako rozšírenie príkladu nášho zoznamu adries zvážime v krátkosti E/R diagram, ktorý modeluje výrobky a objednávky. Jedna osoba môže odoslať niekoľko objednávkových formulárov, objednávku však môže uskutočniť vždy len jedna osoba. Do každej objednávky je možné zahrnúť viac produktov. Produkt je samozrejme možné objednať aj niekoľkokrát.

Ak má byť určitý výrobok zahrnutý do objednávky viackrát - napr. Zákazník si objedná päť ružových zubných kefiek naraz - je pre objednávku charakteristické množstvo objednávky! Nakoniec by mohli existovať ďalší zákazníci, ktorí si chcú objednať iba jednu zubnú kefku a rovnaká objednávka, ktorá obsahuje päť zubných kefiek, by mohla obsahovať aj jednu žltú gumenú kačicu. Množstvo objednávky teda nemôže byť ani vlastnosťou produktu ako takého, ani objednávkového formulára, ale, ako už bolo povedané, vlastnosťou vzťahu medzi produktom a objednávkou.

Model E/R prostredníctvom objednávok

Rovnako ako v prípade typov entít, atribút je implementovaný ako stĺpec v tabuľke. Pretože typ vzťahu „obsahuje“ je typ vzťahu N: M, pre prevod je aj tak potrebná tabuľka vzťahov, ku ktorej sa jednoducho pridá stĺpec pre množstvo objednávky. Kombinácia dvoch cudzích kľúčov zostáva primárnym kľúčom tabuľky vzťahov. Jedna objednávka nemôže obsahovať dvakrát ten istý produkt s rôznymi množstvami. Z koncepčného hľadiska by to tiež nemalo zmysel.

Tabuľky pre objednávky a výrobky; všimnite si stĺpec "suma"

metóda

Poďme si teda zhrnúť kroky, ktoré nás od modelu E/R dostali k tabuľkám:

Pre každý Typ entity: Vytvorte tabuľku
Pre každý atribút: Vytvorte stĺpec
Na akýkoľvek stôl bez prírodného kľúč: Pridajte umelý kľúč
Pre každý Typ vzťahu 1: N: Pridajte stĺpec cudzieho kľúča na stranu N.
Pre každého N: M vzťah: Pridajte tabuľku vzťahov so stĺpcami cudzích kľúčov pre typy entít zapojených do vzťahu a urobte z nich zložený primárny kľúč; poznačte si všetky existujúce atribúty typu vzťahu

Ako budeme podrobnejšie analyzovať neskôr, tento postupný prístup nás priviedol k návrhu databázy z tabuliek, ktoré neobsahujú žiadne redundancie.

Primárny a cudzí kľúč

Pre primárny a cudzí kľúč (PK a FK) môžeme uviesť nasledujúce body:

Hodnota PK musí byť jedinečná a musí označovať riadok v tabuľke
Ak sa PK skladá z niekoľkých stĺpcov, kombinácia hodnôt v stĺpcoch musí byť jedinečná
FK vytvára vzťah k inej tabuľke
Stĺpec FK v jednej tabuľke súvisí so stĺpcom v inej tabuľke (zvyčajne je to stĺpec PK)
Pre stĺpec FK sú povolené iba hodnoty obsiahnuté v stĺpci, ktorého sa týka

Úrovne návrhu

V našom príklade sme práve pracovali na dvoch rôznych úrovniach dizajnu. Na koncepčnej úrovni sme založili model E/R. Ako čisto koncepčný model je úplne nezávislý od databáz! Mohli by sme tiež použiť model E/R na vymodelovanie vecí, ktoré potom nechceme implementovať do databázy. Napriek tomu je to pre náš účel užitočné, pretože nám pripravuje cestu k dobrej schéme databázy.

Implementácia do databázovej schémy v relačnom modeli prebieha na implementačnej úrovni.

Stále existuje fyzická úroveň návrhu databázy, ale keďže fyzický prístup k údajom, ako je popísaný v prvej časti, je abstrahovaný od DBMS, nemusíme sa ním najskôr zaoberať.

Relačný model

Pretože existuje model vzťahu medzi entitami a relačný model a tieto pojmy sú si podobné, existuje riziko zámeny! Pretože model E/R a relačný model sú dve úplne odlišné veci. Ako sme práve videli, hrajú sa dokonca na rôznych úrovniach dizajnu! Anglické slovo „relationship“ nie je preložené do nemčiny ako „Relation“, ale ako „Relationship“. Relačný model je pomenovaný podľa vzťahov v matematike. Ako už bolo opísané v prvej časti, relačný model pre databázy siaha až k eseji Edgara F. Codda z roku 1972.

Vzťahy v matematike

V matematike je vzťah definovaný ako podmnožina karteziánskeho súčinu n iných množín D1, ... Dn, ktoré sa tiež nazývajú domény alebo rozsahy hodnôt. Kartézsky súčin alebo krížový súčin D1 ×… × Dn označuje množinu všetkých možných párových kombinácií prvkov od D1 do Dn. Vzťah R je teda: R ⊆ D1 ×… × Dn

Uvažujme o vzťahu R ⊆ D1 × D2, kde D1 je množina všetkých päťmiestnych celých čísel a D2 je množina všetkých možných reťazcov. D1 × D2 sú potom všetky možné kombinácie päťciferných čísel a ľubovoľných znakových reťazcov. Zoznam poštových smerovacích čísel s priradenými názvami miest, ako je napríklad naša tabuľka miest, je jeho podmnožinou!

Pre naše účely teda môžeme vzťah chápať ako tabuľku (vrátane jej obsahu). (Uvidíme neskôr, že ide o zjednodušenie a že existujú podrobnosti, v ktorých sa tabuľky v DBMS a vzťahy líšia.)

Zhrnutie

V druhom článku v sérii o databázach sme sa zaoberali dizajnom databázy. Spoznali sme model vzťahu medzi entitami ako koncepčný model, pomocou ktorého je možné mapovať veci v reálnom svete s ich vlastnosťami a vzájomným vzťahom. Tiež sme videli, ako je možné model entitového vzťahu previesť na relačný model pozostávajúci z tabuliek. Táto transformácia je nevyhnutná, pretože DBMS ukladá údaje do tabuliek a nemôže priamo robiť nič s modelmi E/R.

výhľad

V ďalšej časti uvedieme model relačnej databázy, ktorý sme dnes pre náš zoznam adries vytvorili, do praxe v reálnom DBMS. Je tiež popísané, ako je možné vytvárať tabuľky v DBMS s SQL. V nasledujúcej, ale jednej časti, sa prehĺbi E/R model a prediskutujú sa ďalšie typy vzťahov a ich implementácia do relačného modelu.