Návrh datového modelu
Co nás dnes čeká Pohledy na data Konceptuální datové
modelování Entitně-relační schéma Prvky E-R schématu, konvence Maticové diagramy a ERDish věty Pokročilé prostředky E-R diagramů Převod do fyzického modelu DDL operace
Schéma databáze Výsledkem databázového
modelování není model,
ale schéma databáze V obecné rovině lze uvádět 3 pohledy na data
Vnější pohled – aplikační úroveň Konceptuální schéma – logika modelu Fyzické schéma – vlastní uložení dat
Pro datové modelování
je nutné pochopit a rozpoznat informace, to jak věci fungují
Konceptuální model To,
jak by to mohlo fungovat, lze nazvat konceptuálním modelem Vlastní implementace je pak fyzický model Co je konceptuální model přesněji:
Orientovaný na objekty (entity a vztahy mezi nimi) Neřeší vlastní implementaci Pokrývá současné potřeby s možností dalšího rozvoje
Výsledkem aplikace konceptuálního modelu je konceptuální schéma
Konceptuální modelování Přesně popisuje
potřeby uchování dat Podporuje diskusi – komunikaci, předchází chybám a nedorozuměním Definuje výchozí dokumentaci „ideálního systému“ de facto standardem pro jeho použití jsou Entitně Relační Diagramy (ERD)
Fyzický model databáze Vlastní návrh implementace v konkrétním
databázovém systému Obsahuje Tabulky, Struktury objektů a Integritní omezení Studuje (Kód_studenta: Int, Kód_Předmětu: Int)
Integritní omezení Jsou definována
na konceptuální i fyzické úrovni Vymezují korektní databázi Umožňují vkládat pouze data, která vyhovují předem definovaným kritériím
Např. Kód_studenta jednoznačně identifikuje jednotlivé záznamy tabulky Student, Kód_Předmětu určuje jednotlivé záznamy tabulky Předmět
Entity Představují objekty/osoby/věci, o kterých je třeba
uchovávat informace zaznamenávané podle skutečnosti Pojmenování souboru několika podobných objektů Lze přirovnat ke třídám objektů stejného typu FILM, ZÁKAZNÍK, ZAMĚSTNANEC, ZVÍŘE Instance je jeden případ výskytu entity
Entity Instance OSOBA VÝROBEK TYP VÝROBKU ZAMĚSTNÁNÍ KONCERT AUTO
Larry Ellison Pilsner Urquell Pivo Popelář Umakart v Roxy Porsche
Atributy
Blíže popisují vlastnosti entit i vztahů
Kvantifikují příslušnou entitu Kvalifikují příslušnou entity Zařazují a zpřesňují příslušnou entitu
Nabývají hodnot z dané domény Je jím právě jedna hodnota, číslo, řetězec znaků, datum, obrázek, zvuk,... příslušného datového typu Integritní omezení jsou pak přídavná pravidla pro zajištění souladu modelu s modelovanou realitou
Atributy – příklad OSOBA
jméno příjmení datum narození pohlaví e-mail
řetězec obsahující písmena řetězec obsahující písmena datum muž nebo žena řetězec v určitém formátu
Identifikátory
Co je to UID
Atribut nebo kombinace atributů jednoznačně odlišující jednu instanci od ostatních Jedinečný identifikátor právě jedné instance
Složený identifikátor
Kombinace více atributů, když jeden atribut k identifikaci nestačí Např. název ulice jednoznačně neidentifikuje konkrétní ulici, protože ulice se stejným názvem může být ve dvou různých městech. Ulice + Město už jednoznačná informace je. Lístek na koncert, jízdenka na SC vlak,...
Umělý a druhotný identifikátor
Umělý identifikátor
V situacích, kde není možné použít žádný z atributů, ani jejich kombinaci Vhodné pro identifikaci osob, RČ nemusí být vždy ideální. Můžeme identifikovat studenta jménem a příjmením? Z důvodu efektivity to bývá zpravidla celé číslo
Druhotný identifikátor
V situacích, kdy máme víc možností UID Např. nakupujete přes eBay? Identifikace přes e-mail, ale i uživatelské číslo Další kandidáti na UID jsou druhotné identifikátory
OBUV * barva * velikost * materiál * styl
ZÁKAZNÍK # ID (#) e-mail * jméno * příjmení
Vztahy Představují propojení
jednotlivých entit Vždy existují mezi dvěma entitami (entita může mít i vztah sama se sebou) Vztahy jsou vždy pojmenovány na obou svých stranách
STUDENT (entita) STUDUJE (vztah) PŘEDMĚT (entita) a PŘEDMĚT (entita) JE STUDOVÁN (vztah) STUDENTEM (entita)
Povinnost, neboli členství
Povinnost je determinována na základě instancí příslušných entit
Povinné členství = všechny výskyty členské entity musí být zapojeny do nějakého výskytu vztahu Nepovinné členství = jednotlivé výskyty členské entity mohou být zapojeny do nějakého výskytu vztahu
Stanovuje se na základě vhodných otázek, např.
Musí mít každý zaměstnanec práci/funkci? Musí být úkol splněn nějakým zaměstnancem?
Příklad členství Každý zaměstnanec MUSÍ
pracovat v oddělení. Každé oddělení MUSÍ zaměstnávat zaměstnance. Každý sudí MŮŽE rozhodovat
utkání. Každému utkání MUSÍ být přiřazen sudí. oddělení
Kardinalita
Popisuje, kolikrát se každá instance dané entity může účastnit daného typu vazby Např. jeden konkrétní student může studovat několik předmětů a jeden předmět může být studován několika studenty Rozlišujeme 3 druhy násobností:
1:1, 1:N M:N
Vazby M:N jsou později dekomponovány na tzv. průnikový typ entity, neboli asociační entitu, protože je nelze realizovat v relační databázi
Entity a členství
Entity zapisujme jako obdélníky se jménem v jednotném čísle psaným velkými písmeny Povinné atributy značíme „*“ Volitelné „“ Unikátní identifikátory označujeme „#“
Vztahy Vztahy kreslíme jako
linie spojující entity Linie mohou být čárkované (nepovinnost) nebo plné (povinnost) Tzv. vidlice ( ) představuje násobnost
Maticové diagramy Způsob pro identifikaci vztahů mezi entitami Jsou velmi vhodné
zejména pro rozsáhlejší
schémata Nepokrývají povinnosti ani násobnosti vazeb
Maticové diagramy - příklad Mějme následující entity – zákazník, země,
památka a následující zadání:
"Pracuji pro cestovní kanceláře. Uchovávám záznam ze zemí, které naši zákazníci navštívili a památek, které zákaznici viděli v každé zemi. Pomáhá nám přizpůsobit nabízené výlety."
zákazník zákazník země památka
země
památka
zákazník země
byla navštívena
památka
byla shlédnuta
země
památka
navštívil
viděl má
se nachází v
Maticové diagramy
zákazník
ERDish Popis vztahů
mezi entitami ve slovním vyjádření Pomáhá pro správné pochopení modelované úlohy i lidem mimo oblast IT Preventivní zamezení chyb
Jak na ERDish? Části ERDish vět:
Každý 2. Entita A 3. Členství (může/musí) 4. Jméno vazby 5. Kardinalita 6. Entita B a také opačným směrem 1.
Jak na ERDish? ZAMĚSTNANEC
zaměstnává pracuje v
Každý zaměstnanec (entita) musí (plná čára) pracovat v (jméno vazby) v právě jednom (jednoduchá čára) oddělení (název entity).
ODDĚLENÍ
Jak na ERDish? ZAMĚSTNANEC
zaměstnává pracuje v
Každé oddělení (entita) může (přerušovaná
čára) zaměstnávat (jméno vazby) jednoho nebo více (vidlice) zaměstnanců (název entity).
ODDĚLENÍ
ERDish Po přepsání:
Každý ZAMĚSTNANEC (entita A) musí pracovat v právě jednom ODDĚLENÍ (entita B).
Každé ODDĚLENÍ (entita B) může zaměstnávat jednoho nebo více ZAMĚSTNANCŮ (entita A).
Postup E-R modelování Seznam entit Určení atributů (* povinné/
o volitelné/ # unikátní)
Matice vazeb ERDish Konceptuální model (ER Diagram) Doplnění integritních omezení
Vytvořte ERD pro následující problém Firma má své zázemí v několika budovách. Budovy jsou vždy organizovány do vyšších celků nazývaných areály. Budovy mohou mít jedna nebo více podlaží s určitým označením. Celé podlaží patří vždy k určitému oddělení. Oddělení se může nacházet na více podlažích Na každém patře může být několik místností, každá místnost je určitého typu (dílna, sklad, učebna, kancelář, laboratoř, šatna). Každá místnost má přidělenu osobu, která je za ni zodpovědná. Každá osoba může pracovat v několika místnostech, ale pouze jedna z nich je primární. Každý člověk je zaměstnán alespoň v jednom oddělení na určité pracovní pozici.
Supertyp a subtyp Jde
reakci na reálné situace:
způsob platby (hotovost, kartou, převodem,...), časový údaj (okamžik, den, týden, měsíc, rok), zvířata (obratlovci, bezobratlí), uživatel systému (zaměstnanec, zákazník)
Instance supertypu se mohou lišit svými atributy
a/nebo vztahy. Příklad – platba, stejné jsou datum platby, částku, apod.
Jiný typ platby => jiné atributy
Supertyp a subtyp Logické rozdělení
entity na podcelky. Například když dílčí instance mají specifické vlastnosti (viz jednotlivé platby). Entitu označujeme jako supertyp, každý podcelek je pak subtyp.
Platba
Hotově Kartou PayPal
Subtyp Přebírá všechny atributy Přebírá všechny vztahy Může mít vlastní atributy
i vazby Je znázorněn uvnitř supertypu Nikdy nevzniká samostatně
Zvířata Obratlovci Bezobratlí
Zanoření (vhnízdění) subtypů V některých případech
je pro lepší názornost možné používat více úrovní zanoření. Je důležité, aby to bylo smysluplné!!!
Zvířata Obratlovci Ptáci
Plazi
Savci
Ostatní
Bezobratlí
Jak si to představit
Co s tím Subtyp je možné najít u každé entity Ne vždy je nutné jej použít Kdy jej použít vyplývá
modelujeme Identifikace subtypu
z reality, kterou
Je subtyp součástí supertypu? Vyčerpali jsme opravdu všechny možnosti? Náleží příklad instance do právě jednoho subtypu?
Přenositelnost u vztahů Povinnost
– styl bez
písně? Kardinalita – kolik písní je stejného stylu? A kolik stylů může mít jedna píseň? Můžeme změnit styl písně?
Nepřenositelné vztahy
Tzv. silné a slabé vazby
Obloukové vazby (Arcs) Pro vyjádření možnosti volby u vazeb Oblouk vždy
náleží ke konkrétní entitě Zahrnují dvě a více vazeb Entita může využívat i více obloukových vazeb Oblouková vazba má vždy stejnou násobnost Supertyp i subtyp mohou být znázorněny pomocí obloukových vazeb (arc)
Použití obloukových vazeb
Hierarchické vztahy Nastává v případě, kdy je instance dané entity ve
vztahu sama k sobě Příkladem je následující zadání
“Potřebujeme sledovat informace o našich zaměstnancích. Každý zaměstnanec má právě jednoho nařízeného, kromě prezidenta. Každý manažer může řídit několik zaměstnanců.”
Hierarchické struktury Typicky organizační schéma společnosti Pokrytí budov
včetně místností
Rekursivní struktury jiný způsob
zaznamenání hierarchií
jak?
Máme za úkol namodelovat zaměstnaneckou strukturu
Zkuste se zamyslet, který přístup je lepší a kdy?
Normální formy Vychází z návrhu
relačního schématu Při návrhu relačního schématu hledáme funkční závislosti mezi atributy „Funkční závislost“ lze definovat formálně X Y ↔ ∀t1,t2 ∈ r: t1∙X = t2∙X t1∙Y = t2∙Y Intuitivně však pojem závislosti chápeme (např. PSČ město) Normální formy se snaží zjednodušit návrh
1. normální forma (NF) Všem atributům jsou jako jejich domény přiřazeny
pouze atomické typy Např. adresa nebo celé jméno uživatele nebo emailové adresy nejsou atributy s atomickým typem, tedy celý návrh nesplňuje 1. normální formu Předpoklad 1. normální formy lze při návrhu obvykle snadno dodržet
První normální forma vyžaduje, aby neexistovaly vícehodnotové atributy. Proč není tato tabulka v 1. NF? Jak docílit, aby byla? ID_ZAKAZNIKA 101 102 103
JMENO Marta Josef Johan Gregor
PRIJMENI Nováková Koudelka Mendel
ZBOZI stůl, lampa postel, skříň, stůl židle
Nevhodné řešení ID_ZAKAZNIKA 101 102 103
JMENO Marta Josef Johan Gregor
PRIJMENI Nováková Koudelka Mendel
ZBOZI1 stůl postel židle
Tabulky splňují 1.NF ID_ZAKAZNIKA 101 102 103
JMENO Marta Josef Johan Gregor
PRIJMENI Nováková Koudelka Mendel
ZBOZI2 lampa skříň
ZBOZI3 stůl
ID_ZAKAZNIKA 101 101 102 102 102 103
ZBOZI stůl lampa postel skříň stůl židle
2. normální forma Pro splnění 2. NF musí být schéma v 1. NF Schéma nesmí obsahovat
žádné částečné závislosti neklíčových atributů na klíči Zavedením jednoduchých klíčů a splněním 1. NF lze jednoduše dosáhnout 2. NF
Příklad Program
Název_D
Jméno_H
Adresa
Datum
Městské
Pastorkyňa
Lidická 16
23.02.11
Městské
Tři mušketýři
Lidická 16
28.04.11
Národní
Tři mušketýři
Roosvelltova 3
04.04.11
Národní
Pastorkyňa
Roosvelltova 3
28.03.11
Národní
Mary Poppins
Roosvelltova 3
23.02.11
Redundance
Příklad - řešení Program Název_D Jméno_H
Adresář Název_D Adresa Městské Národní
Lidická 16 Roosvelltova 3
Datum
Městské
Pastorkyňa
23.02.11
Městské
Tři mušketýři
28.04.11
Národní
Tři mušketýři
04.04.11
Národní
Pastorkyňa
28.03.11
Národní
Mary Poppins
23.02.11
• Intuitivně dekompozicí, dojde k odstranění závislosti neklíčového atributu (Adresa) na podklíči (Název_D)
3. normální forma Pro splnění 3. NF musí být schéma v 2. NF Dále nesmí být žádný neklíčový atribut
tranzitivně
závislý na žádném klíči schématu Tedy např. existence atributů PSČ a města uvnitř relace uživatel s klíčem rodné číslo odporuje 3. NF, protože město funkčně závisí na PSČ Pro zavedení 3. NF je třeba vyrobit číselníky
Podniková pravidla Co je cílem konceptuálního modelování?
Uchování a poskytování informací Ve schématech uchováváme také integritní omezení (IO) Pro správné schéma databáze je nutné pokrýt i podniková pravidla
Procedurální, často spojené s časem Strukturální, jaká informace má být uložena
Podniková pravidla
Strukturované – uvádějí typy informací, jež je možno pro daný atribut uchovávat (jen čísla, časové údaje od 2. 1. 2012, primární klíč z jiné tabulky, ...) Procedurální – jedná se o pracovní postupy související s provozem systému (sumační údaje z prodeje se ukládají vždy prvního v každém měsíci) Strukturální podniková pravidla je možné téměř vždy možné zanést do ERD. Některá z procedurálních podnikových pravidel nelze do diagramu zaznamenat, ale i tak musí být zdokumentována.
Modelování historických dat Kdy musíme počítat s historií?
Budou data auditována? Podléhají data času? Musíme udržovat vývoj v čase a jak dlouho?
Co když budeme chtít sledovat
zaměstnanců?
výplaty
Půjčovna Jak namodelujme půjčovnu
šperků?
Půjčovna Jak namodelujme půjčovnu
šperků?
Modelování času Kdy použijeme datový
co je nové
typ DATE a den jako entitu?
entitě špatně? Nápověda 3NF
Omezení související s časem
Modelování vývoje ceny Nejen cen, ale také vývojových
ukazatelů Zavedeme si tzv. historickou entitu Co si uvědomit
Z Bc. na Ing., co je dobré vědět? Sleva u obchodních aplikacích jako e-shop Kam sledování ceny zařadíme? A co když zákazník vrací zboží?
Přenesená závislost Jaké atributy sledovat?
Čas u prodeje Čas platné ceny
Žurnálování Opomíjené při návrhu
schématu
databáze Používáme vždy, když uživatelé mají umožněno zásadně měnit data aplikace. Vytváření logu/žurnálu Uchováváme předchozí hodnotu, ale také kdo změnu provedl
Na společné procvičení Jsme taneční studio a nabízíme taneční kurzy pro dospělé. Kurzy jsou rozděleny podle náročnosti do několika kategorií. Pro jednotlivé kategorie jsou určeny tance, které jsou vyučovány. Jeden tanec se může vyskytnout ve více kategoriích (pouze v jiné náročnosti). Za jednotlivé kurzy jsou zodpovědní lektoři, kteří řídí výuku. Vždy je jeden hlavní a jeden pomocný. Hlavní a pomocný lektor musejí být opačného pohlaví. U jednotlivých kurzů evidujeme, který den v týdnu probíhají a v kolik hodin, kolik je lekcí a datum, kdy kurz začíná a kdy končí. Dále evidujeme naše tanečníky. O nich známe jméno, příjmení a u některých i email. Chceme vědět, do kterých kurzů chodí.
Převod ERD do fyzického modelu
Konceptuální
model (ER diagram) je převeden do fyzického modelu. Fyzický model bude implementován relační databází.
Změna terminologie Převod
z konceptuálního modelu na fyzický s sebou přináší i změnu terminologie:
Konceptuální entita instance atribut primární identifikátor sekundární identifikátor vztah
Fyzický tabulka řádek sloupec primární klíč unikátní klíč cizí klíč
Notace zápisu tabulek První
řádek obsahuje jméno tabulky Samotná tabulka popisující tabulku obsahuje tři sloupce: typ klíče (pk, uk, fk), povinnost (*, o) a název sloupce. Pro jména tabulek se užívají množná čísla. Jména sloupců zůstávají zachována kromě speciálních znaků (diakritiky). Mezery jsou nahrazeny podtržítkem.
Mapování vazeb Vztah vytváří
cizí klíč, který musí být zanesen do jednoho nebo i více sloupců tabulky. V závislosti na povinnosti vztahu je pak dáno, může-li odpovídající sloupec nabývat hodnotu NULL.
Problémy při převodu Povinnost
vazby se v rámci relační databáze dá vynutit pouze na jedné straně vazby (té, která se promítne jako nový sloupec). Nepřevoditelnou vazbu v relační databázi také nelze zachytit. Je potřeba použít nadstavbu v podobě dodatečného programování. Tyto skutečnosti je vždy vhodné zanést do dokumentace.
Mapování vazeb N:M
Mapování oblouků Při tomto mapování je nezbytné ohlídat, aby byl cizí klíč právě v jednom z odpovídajících sloupců. CHECK (pse_id IS NOT NULL AND phe_id IS NULL) OR (pse_id IS NULL AND phe_id IS NOT NULL)
Mapování supertypů a subtypů – jedna tabulka Vytváří se vždy pouze jedna tabulka nezávisle na počtu
subtypů. Tabulka bude obsahovat všechny sloupce odpovídající atributům supertypu s deklarovanými povinnostmi. Do tabulky budou zahrnuty také sloupce patřící jednotlivým subtypům. Tyto sloupce budou nepovinné. K těmto sloupcům se pak přidá ještě jeden, který bude povinný a bude specifikovat, o jaký subtyp se jedná.
Jedna tabulka – pokračování
Je potřeba
se ujistit, aby docházelo ke vkládání odpovídajících hodnot daným subtypům pomoci omezení CHECK.
Kdy použít jednu tabulku? Použití
jedné tabulky je běžná a flexibilní implementace. Je vhodné ji použít především když: Většina atributů je společných (nachází se v
supertypu) Většina vazeb je na úrovni supertypu Podniková pravidla jsou globálně aplikovatelná na subtypy
Mapování supertypů a subtypů – dvě tabulky Vytváří se
tabulka pro každý subtyp. Ve skutečnosti tak může vzniknout tabulek více. Všechny tabulky budou obsahovat sloupce odpovídající atributům supertypu a k tomu sloupce odpovídající atributům daného subtypu. Povinnost zůstane zachována podle ERD. Stejným způsobem se pak mapují i vztahy mezi entitami do cizích klíčů
Dvě tabulky – pokračování
Kdy použít dvě tabulky? Subtypy
mají pouze málo společného. Vyskytuje se málo atributů v supertypu a více v subtypech Většina vztahů je na úrovni subtypů Podniková pravidla se mezi subtypy výrazněji liší
Mapování supertypů a subtypů – oblouk Muže
dojít k úpravě ERD a následně se postupuje jako by se jednalo o obloukovou vazbu.
Data definition language
Datový slovník Vytváření, úprava a rušení nových objektů v datovém slovníku Objekty v datovém slovníku: tabulky, indexy, omezení, pohledy, sekvence, synonyma Obvykle se provádí rozdělení databáze na jednotlivá schémata (sessions), přičemž každé má svůj datový slovník RDBMS mají datový slovník přístupný jako zvláštní datového schéma
Tabulky (tables) představují relace vertikálně se dělí na řádky horizontálně se dělí na sloupce (tj. atributy) platí zde všechna pravidla o relacích a normálních
formách zmíněná dříve (zejména definice domény, integritní omezení, klíče, atomičnost atd.) „jedná se o jednorozměrný Excel“
Jak na tabulku v SQL CREATE TABLE jméno (relace) Relace je představována popisem atributů a integritních omezení navzájem oddělených čárkou Popis atributu: jméno typ [impl. hod.] [omezení] Implicitní hodnota je definovatelná pomocí fráze DEFAULT hodnota
Jak pojmenovat tabulky Základní pravidla pro pojmenovaní
shrnout na:
tabulek lze
Začínají písmenem Název musí mít mezi 1 a 30 znaky Obsahují pouze znaky A-Z, a-z, 0-9, _, $ a # Nesmí mít stejný název jako jiný objekt vlastněný stejným uživatelem Nesmí být tvořeno vyhrazenými klíčovými slovy Názvy nejsou Case-Sensitive Volte popisná jména
Základní datové typy
V jednotlivých DB systémech mohou být rozdíly v názvech. Zde uvedené platí pro dbs Oracle. Znakové typy: CHAR(n), VARCHAR2(n) Číselné typy: NUMBER(prec, scale), INT, DECIMAL, FLOAT, REAL, SMALLINT Typ datum a čas: DATE Velké objekty: LONG, LONG RAW, RAW(n) Rozsáhlé objekty: BLOB, CLOB, BFILE Vlastní definované typy Tabulkové a objektové typy
Definice integritního omezení Základní omezení označuje možnost existence
nedefinovaného stavu atributu (NULL, NOT NULL), implicitně je NULL Další omezení lze zavést jako doplňující podmínku k doméně CHECK (podmínka) Každému atributu lze definovat klíč (primární PRIMARY KEY, unikátní UNIQUE nebo cizí klíč REFERENCES) Pojmenované omez. CONSTRAINT jméno
Vícesloupcová integrita Představuje integritní omezení definované
jako
atribut relace Může být definována jako primární klíč PRIMARY KEY(sloupce), unikátní klíč UNIQUE(sloupce), podmínku CHECK(podmínka) nebo jako cizí klíč FOREIGN KEY(sloupce) REFERENCES Zápis cizího klíče je REFERENCES tabulka(sloupce) [ON DELETE CASCADE|SET NULL]
Příklad
Další příkazy DDL Rušení objektů:
DROP objekt název Modifikace struktury tabulky: ALTER TABLE jméno operace Podporované operace:
přejmenování, RENAME TO jméno přidání sloupců, ADD (definice relace) zrušení sloupců, DROP (sloupce) změna omezení sloupce, MODIFY (relace) přejmenování sloupce RENAME COLUMN jméno TO jméno
Přihlašovací údaje
Přihlašovací údaje Username: c##jmeno Password: pwjmeno Hostname: trust.mendelu.cz Port: 1521 SID: trust
https://akela.mendelu.cz/~xturcin0/naOstro/
