Design databáze RNDr. Ondřej Zýka
1
Obsah
• • • •
Shromáždění business požadavků Konceptuální model Logický model Fyzický model
2
Obsah databáze • Tabulky • Sloupce • Relace • Indexy
• Logika • Triggery • Procedury
• Zabezpečení • Práva k objektům • Chybová hlášení 3
Obsah databáze • Databáze slouží jako model reality • Popisuje realitu na úrovni detailu, který vyžadují uživatelé • Informace o entitách • Informace o vazbách mezi entitami
• Nabízí operace, které vyžadují uživatelé • Operacích nad daty • Zabezpečení dat
4
Návrh databáze • Čtyři kroky • • • •
Shromáždění business požadavků Konceptuální model Logický model Fyzický model
• Modelování od začátku (z nuly) • Nutnost začlenění stávajícího stavu nebo okolních stavu okolních systémů (prostředí) 5
Shromáždění business požadavků • Cíle • Pochopit business doménu
• Prostředky • • • •
Interview Studium a dokumentace systémů Spolupráce s experty v business oblasti Informace o organizační struktůře a další dokumenty
• Výstup • Data-flow diagram • Seznam požadavků • Dokument popisující doménu
6
Datové toky
7
Data-flow diagram • Data flow-diagram popisuje • • • • • •
S jakými daty se pracuje Kdo data vytváří Kdo data jak zpracovává (modifikuje) Kde jsou data uložena Kdo data používá Interface na úrovni dat
8
Data-flow diagram • • • • •
Datové toky na různých úrovních granularity Podnikové toky dat Toky dat na technické úrovni Popisy ETL procesů Popisy integračních procesů
9
Data-flow diagram • Ověření • • • •
Všechna data jsou definována Persistentní data jsou uložena Každá data mají zdroj Pro každá data existuje odběratel
• Často vyžadováno předpisy • Například Payment Card Industry (PCI) Data Security Standard vyžaduje přesný popis • kam všude se přenášejí čísla kreditních karet a další atributy • kdo k nim má přístup.
10
DFD příklad Decomposition Level 2 1.1.1
CUSTOMER
databáze/ soubor
CUSTOMER_ORDER
Place Orders [BOOK_ORDER]
Book Supplier
+ 1.1.2 Receive Orders
+
Book Shipment Invoice
[SHIPMENT]
funkce
1.1.3 Process Invoices
+
Book Supplier
datová entita/ business object
vstup/výstup
[PAYMENT] Book Supplier
11
Konceptuální model • Cíle • Určit entity a jejich atributy, u atributů určit jejich hodnoty a vlastnosti, u entit určit jejich potenciální klíče (identifikátory) a identifikovat vazby mezi entitami.
• Výstupy • ER diagram
12
Konceptuální model - příklad
13
Definice • Entity – objekty o kterých hovoří business požadavky, mají instance • Atributy – charakteristiky, které musí nebo mohou být známy o entitách • Data value – hodnoty, které nabývají atributy • Klíče (identifikátory) – skupiny atributů, které identifikují jednotlivé instance entit • Relationship – vazby mezi entitami 14
Potenciální klíče • • • •
Identifikují jednotlivé instance entit Neobsahují hodnoty null Jsou stabilní Vždy se dá uvažovat umělý klíč
15
Relace • Vazba mezi dvěma nebo více entitami • U relací je důležité • Kardinalita relace • Jméno relace • Role relace (popis)
• Notace realcí
16
Notace Mnoho standardů a dodavatelských specifik. • • • • • • • •
UML – notace dle standardu UML, použita ve všechny nástroje podporující UML. Information Engineering – standard používaný v mnoha nástrojích. Existuje několik verzí. Obecně, entity jsou obdélníka a relace jsou linky s různými zakončeními. IDEF1X – standardní notace pro modelování relací a entit. Symboly označují kombinaci volitelnosti a kardinality entity. Barker – Vytvořena Richardem Barkerem. Používaná zejména case nastroji Oracle. Speciální notace pro dědičnost, vlastní notace pro násobnost a speciální značky pro atributy. Filtered IE – pouze v Embarcadero. Nezobrazuje cizí klíče. Entity/Relationship – Sybase specific, Entity/Relationship je speciální verze IE notace. Merise – používá asociace místo relací. Crow's Feed – Jedna z verzí IE notace, tuto notaci používá FSLDM.
17
Násobnost a volitelnost CAR
CAR
CAR
CAR
Nejvíce jedno auto
Právě jedno auto
Žádné nebo libovolný počet aut,
Nejméně jedno auto 18
Entity and Attribute Notation E/R UML NATURAL PERSON «id» Person ID Current first name Current middle name [0..1] Current last name Birth date [0..1] Gender [0..1] Identification number [0..1] /Age
Barker/Ellis
Identifying attribute symbol
NATURAL PERSON Mandatory attribute symbol
Optional attribute symbol
# PERSON ID CURRENT FIRST NAME o CURRENT MIDDLE NAME CURRENT LAST NAME o BIRTH DATE o GENDER o IDENTIFICATION NUMBER (AGE)
Computed (derived) attribute
19
Cardinality (=Multiplicity) and Association Notation Mapping
Allowed multiplicities
N/A substituted by
Not Used Used
Not Used because not needed
20
Pravidla pro čtení Reading direction
Assertion 2: "Each LINE ITEM must be part of exactly one ORDER "
LINE ITEM Line number Quantity Price Delivery date
ORDER part of 0..*
1 Order number Order date composed of
Assertion 2: "Each ORDER may be composed of one or more LINE ITEMS "
21
Pravidla pro čtení
22
Závislost • Jedna entita záleží na druhé a je jí identifikována • Partner musí mít vztah právě k jednomu zaměstnanci. ZAMĚSTNANEC LAST NAME FIRST NAME PHONE NUMBER ADDRESS
PARTNER LAST NAME FIRST NAME PHONE NUMBER ADDRESS
23
Dědičnost ZAMĚSTNANEC EMPLOYEE EMP_LAST_NAME EMP_FIRST_NAME EMP_PHONE_NUMBER EMP_HIRE_DATE EMP_STATUS
OBCHODNIK SALES PERSON
EMP_YTD_SALES
24
N-arní relace EMPLOYEE
COURSE
EMPLOYEECOURSE-ROLE
ROLE
25
Relace s atributy
EMPLOYEE EMPLOYEE ID EMPLOYEE LAST NAME EMPLOYEE FIRST NAME EMPLOYEE PHONE
manage
WORK GROUP WORK GROUP ID WORK GROUP NAME WORK GROUP PROJECT
START DATE
26
Entity-Relationship Diagram • Zápis entit • Jméno • Popis – definici, příklady a protipříklady • Klíče
• Zápis atributů • • • •
Jméno Popis – definice, příslušnost k entitě, domain, omezení, rozsah Kardinalita Zda je to odvozený atribut
• Zápis relací • • • •
Kardinalita, povinnost Role na obou stranách Popis definice Ke kterým entitám se váže
• Grafická representace
27
Formální ověření E/R diagramu • Mezi každými dvěma entitami je maximálně jedna relace. • Neexistuje cyklická závislost. • Entity s realcí typu 1:1 zřejmě budou tvořit pouze jednu entitu. • Žádná entita nemá atribut, který je kandidátním klíčem jiné entity. • Nepřímé relace jsou asi zbytečné.
28
Vytvoření logického modelu • Cíle • Vytvořit platformově nezávislý logický datový model
• Postup • Převést entity na tabulky • Rozhodnout, jak se bude pracovat se složitými atributy • Rozhodnout, jak se bude pracovat s atributy nabývajícími více hodnot • Výběr primárního klíče • Převést binární relace (závislosti) typu 1:n na cizí klíče • Vyřešit n-ární relace a relace typu n:n • Rozhodnout o způsobe reprezentace subtypů • Normalizace modelu
• Výstup • Logický datový model
29
Převod entit na tabulky • Jméno entity -> Jméno tabulky • Atribut -> Sloupec • Instance -> Řádek • V tabulce nemají sloupce ani řádky žádné speciální uspořádání. • Tabulka nemůže mít dva stejné řádky. • Tabulka nemůže mít dva sloupce stejného jména. • V průsečíku řádku a sloupce musí být právě jedna hodnota (může to být null).
30
Převod entit na tabulky
BOOK BOOK BOOK ID TITLE CATEGORY QUANTITY SOLD
AUTH_ID ------7749 1100 7749 8568 8568 0123
ID TITLE -- -------------60 New Poems 61 Killing Time 62 Cats in Love 70 The Snail 67 All in the Day 63 Look Here
CATEGORY --------Poetry Fiction Essays Fiction Biography Drama
QUANTITY -------4500 5000 1000 1000 500 500
31
První normální forma • Tabulka je v první normální formě, když každý sloupec obsahuje právě jeden atomický datový typ, který již nemá vnitřní strukturu z pohledu businessu. • Pro rozhodnutí o primárních a cizích klíčích musí být tabulka v prvním normálním tvaru.
32
Strukturované atributy • Atributy obsahující více než jednu informaci PUBLISHER PUB_ID -----2233 1100 4680
NAME ----Aris Totum Felix
PHONE -------232-5555 456-0000 555-1212
MAILING_ADDRESS ----------------------------------50 Temple Place Orlando FL 09327 143 Seaborg Seattle WA 96505 1 Info Plaza Mountain View CA 94501
33
Atributy nabývající více hodnot
CUSTOMER NAME ----------Max Brown Ted Green Joy Gray Tia Puce Molly Brown
PHONE#1 -------555-1415 444-0607 333-2121 222-0005 555-1414
PHONE#2 -------null 111-9999 333-2001 null null
PHONE#3 -------null null 546-1313 null null
ADDRESS ---------------423 5th Street 231 Ash Avenue P.O. Box 9876 700 8th St, #2 444 Forest Drive
34
Atributy nabývající více hodnot CUSTOMER NAME ADDRESS
CUSTOMER NAME ---------Max Brown Ted Green Joy Gray Tia Puce Molly Brown
ADDRESS ----------------423 5th Street 231 Ash Avenue P.O. Box 9876 700 8th St, #2 444 Forest Drive
PHONE NAME EXTENSION
PHONE NAME ---------Max Brown Ted Green Ted Green Joy Gray Joy Gray Joy Gray Tia Puce Molly Brown
EXTENSION --------555-1415 444-0607 111-9999 333-2121 333-2001 546-1313 222-0005 555-1414
35
Výběr primárního klíče • Primární klíč je skupina sloupců které identifikují jednotlivé řádky tabulky. • Tabulka má pouze jeden primární klíč, ten může obsahovat více sloupců.
36
Kritéria pro výběr primárního klíče • Primární klíč: • musí mít vždy definovanou hodnotu (not null), • musí mít stálou hodnotu (během celého životního cyklu řádku), • musí být co možná nejmenší, • nesmí obsahovat žádné zakódované informace, • musí být přístupný pro všechny uživatele.
• Umělý klíč • Nevýhody: přidává sloupec do tabulky, hodnoty nemají význam pro uživatele 37
Převod binární relace 1:N na cizí klíč AUTHOR
AUTHOR
AUTHOR ID LAST NAME FIRST NAME
AUTHOR ID LAST NAME FIRST NAME
PHONE NUMBER ADDRESS
PHONE NUMBER ADDRESS
PICTURE PICTURE ID FORMAT BYTESIZE PIXEL WIDTH PIXEL HEIGHT
PICTURE AUTHOR ID PICTURE ID FORMAT BYTESIZE PIXEL WIDTH PIXEL HEIGHT
38
Převod závislosti na cizí klíč AUTHOR
AUTHOR
AUTHOR ID
AUTHOR ID
LAST NAME
LAST NAME
FIRST NAME
FIRST NAME
PHONE NUMBER
PHONE NUMBER
ADDRESS
ADDRESS
PICTURE PICTURE ID FORMAT BYTESIZE PIXEL WIDTH PIXEL HEIGHT
PICTURE AUTHOR ID PICTURE ID FORMAT BYTESIZE PIXEL WIDTH PIXEL HEIGHT
39
Relace typu n:n AUTHOR AUTHOR ID LAST NAME FIRST NAME PHONE NUMBER ADDRESS
AUTHOR AUTHOR ID LAST NAME FIRST NAME PHONE NUMBER ADDRESS
BOOK BOOK ID TITLE CATEGORY QUANTITY SOLD
AUTHOR-BOOK
BOOK BOOK ID TITLE CATEGORY QUANTITY SOLD
Associative entity
40
Relace typu n:n AUTHOR AUTHOR ID LAST NAME FIRST NAME PHONE NUMBER ADDRESS
AUTHOR-BOOK AUTHOR ID BOOK ID
BOOK BOOK ID TITLE CATEGORY QUANTITY SOLD
41
Vyřešit n-arní relace EMPLOYEE
COURSE
EMPLOYEECOURSE-ROLE EMPLOYEE ID
COURSE ID ROLE ID
ROLE
42
Rekursivní relace
EMPLOYEE EMPLOYEE ID EMPLOYEE LAST NAME EMPLOYEE FIRST NAME EMPLOYEE HIRE DATE EMPLOYEE STATUS
supervises
43
Rekursivní relace
EMPLOYEE EMPLOYEE ID EMPLOYEE LAST NAME EMPLOYEE FIRST NAME EMPLOYEE HIRE DATE EMPLOYEE STATUS SUPERVISOR ID
Renamed foreign key
44
Rozhodnutí o reprezentaci dědičnosti
• Několik možností • V jedné tabulce: L-schéma • Ve více tabulkách
45
Normalizace • Změna logického modelu • Změna je bezeztrátová • Za normalizací je teorie relací
• Výhody • Snížení redundancí • Zjednodušení správy dat
• Snížení nutnosti používat null hodnoty • NULL hodnoty způsobují problémy při sumarizaci dat 46
Další výhody normalizace • Možnost přesnějšího vyjádření business pravidel datovým modelem. • Normalizace většinou odhalí další entity a pravidla. • Umožňuje konstrukci velkých projektů spolehlivým a opakovatelným způsobem. • Plně normalizovaný model se dá použít jako benchmark zda denormalizace přináší nějakou výhodu. • Datové stroje jsou připraveny pracovat s normalizovanými modely.
47
Hierarchie Normálních forem 1. Normální forma 2. Normální forma 3. Normální forma Boyce/Codd normální forma
4. Normální forma 5. Normální forma
48
Funkční závislost • Mějme dva sloupce A, B • Jestliže platí pro kterékoliv dva řádky že pokud se shoduje hodnota ve sloupci A, potom už se shoduje hodnota i ve sloupci B, říkáme • A funkčně určuje B • B je funkčně závislé na A
• Tvrzení musí platit pro všechny možné (i budoucí) hodnoty • Definice platí jak pro sloupce, tak pro množiny sloupců.
49
Funkční závislost
CATALOG
INVENTORY CATALOG ------1234 2345 6543 8765 1357 0098 4444
PRODUCT CODE -----------2 3 5 3 13 4 7
COLOR ------blue yellow orange yellow yellow orange red
PRODUCT CODE
PRODUCT CODE COLOR
Determinant Dependent
50
První a druhá normální forma • Tabulka je v první normální formě, když všechny sloupce jsou atomické – obsahují jenom jeden typ hodnot. • Tabulka je v druhé normální formě pokud je v první normální formě a každý sloupec, který není součástí primárního klíče je funkčně závislý na celém primárním klíči.
51
2NF - příklad MAYOR STATE_CODE CITY MAYOR_NAME STATE_NAME
STATE_CODE
STATE_NAME
STATE_CODE + CITY
MAYOR_NAME
STATE_CODE
STATE_NAME
CITY
MAYOR_NAME
52
2NF - příklad A
C
B
D
A A B
C
D
MAYOR STATE_CODE CITY MAYOR_NAME
STATE STATE_CODE STATE NAME
53
Třetí normální forma Tabulka je ve třetí normální formě pokud je v druhé normální formě a každý sloupec, který není součástí primárního klíče je přímo funkčně závislý na klíči a ničem jiném.
54
3NF - příklad 1.
2.
3.
A
A
B
B
CATALOG-PRODUCT CODE CATALOG ------1234 2345 6543 8765 1357 0098 4444
PRODUCT CODE -----------2 3 5 3 13 4 7
C
B
C
PRODUCT CODE-COLOR PRODUCT CODE -----------2 3 5 13 4 7
COLOR ------blue yellow orange yellow orange red 55
Boyce / Codd normální forma • Tabulka je v BCNF právě když pro každou netriviální funkční závislost X → Y, je X superklíč, což znamená, že X je kandidátní klíč nebo jeho nadmnožina. • Tabulky, které je potřeba normalizovat do BCNF splňují kritéria: • Mají několik kandidátních klíčů. • Kandidátní klíče jsou složené. • Složené klíče mají alespoň jeden sloupec společný.
• Existují případy, kdy se BCDN nedá dosáhnout.
56
Vyšší normální formy • Definice založená na vícehodnotové závislosti. • Málo používané. • Je-li tabulka v 3NF žádný z kandidátních klíčů není složený, je již v 5NF a tedy i v 4NF.
57
Vytvoření fyzického modelu • Cíle • Vytvořit fyzický model s ohledem na specifika aplikace a použitý typ databáze, použitý hardware
• Postup • • • • • • • • •
Tabulky (jmenné konvence) Datové typy Vytvoření procesní matice Rozhodnutí o struktuře tabulek Rozhodnutí o primárním klíči (indexu) Implementace business pravidel - constraints Indexy, partitioning, Denormalizace, uložení redundantních dat, spojení tabulek Fyzické uložení tabulek
• Výstup • Fyzický datový model • Implementační skripty
58
Tabulky (jmenné konvence) • • • •
Case sensitive/case insensitive servery Omezení délky jména tabulek (Oracle 30 znaků) Omezení délky jmen sloupců Omezení na jmenné prostory (tabulky, view, indexy, procedury, …) • Porozumění modelu • • • •
Datové tabulky Číselníky Logy Uživatelské tabulky
• Natural join 59
Datové typy • Domain – uživatelské datové typy • + lepší porozumění • + zajištění konzistence • - nároky na údržbu
• Char, varchar, nvarchar, … • Numerické datové typy • int, tinyint, bigint, numeric(p,s), …
• Datum – rozsah, přesnost, způsob práce • Timestamp – je to čas nebo není
• Binary, image, text, memo, … • Boolean - raději "column_name" CHAR(1) default 'A' not null constraint CKC_check_name check ("column_name" in ('A','Y'))
• Identity, Autoincrement • NULL, Not null, Default value • Speciální datové typy 60
Procesní matice • Způsob jak zaznamenat požadavky jednotlivých obchodních procesů na data • • • •
Create/Update/Modify/Read Frekvece použití Požadovaná doba odezvy (service level) Algoritmy
• Sloupce, joiny, where podminky
61
Procesní matice - příklad
62
Rozhodnutí o struktuře tabulek • • • •
Cíle: minimalizovat velikost Použít optimální přístupové metody Standardní tabulka • Insert, Delete, Update, Scan, Index
• Index-organized tables (Clustered index) • • • •
+ menší + rychlejší přístup po indexu - náročnější insert, delete (update) Oracle – novější implementace, podpora 7x24 63
Rozhodnutí o primárním klíči • Terminologie • • • •
Klíč Index Primární klíč Primární index
• Požadavky na primární klíč podle procesní matice • Joiny • Možnost vytvořit foreign key pro jiné indexy než primární • Jiné omezení datového serveru 64
Implementace business pravidel Domain integrity - Check • Na úrovni sloupce • Null/Not null • Default • Check (format)
• Na úrovni řádku
• Entity integrity - primary key • implementováno unikátním indexem na not null sloupcích • záznam v katalogu
• Unikátnost hodnot • implementováno unikátním indexem
65
Referenční integrita • Implementace foreign key • • • •
Deklarativní definice Použití triggerů Použití uložených procedur Kód aplikace
• Co se stane když • Primární klíč se přidá/změní/zruší • Cizí klíč se přidá/změní/zruší
66
Příklad
zakaznik obchodnik obchodnik_id int
name varbinary(255) diskount numeric(5,3) ...
zakaznik_id obchodnik_id jmeno adresa ...
int int varchar(255) varchar(255)
67
Možné typy referenční integrity - příklad
68
Důsledky použití integritních omezení • + zaručují konzistentní model • - zvyšují výpočetní složitost • i v případě, kdy nedochází ke změnám (Oracle not null)
• - komplikují údržbu • povolení/zakázání ověřování integritních omezení
69
Indexy • Typy indexů • B-tree • Clustered/nonclustered
• Bitmapové indexy
70
Přístupové metody • Standardní index • Procházení indexu • Scan nejnižší úrovně • Dotaz pokrytý indexem
• Clustered index • Procházení indexu • Scan dat od nalezeného sloupce
71
B-tree index
72
Clustered index
73
Použití indexů • Select • Pokrývající query
• Group by • Order by • Join • Nepoužívat indexy pro malé tabulky • Selektivita indexů 74
Denormalizace • Partitioning • Horizontální • Vertikální
• Uložení vypočtených hodnot • Spojení tabulek
75
Horizontální rozdělení tabulek • Přístupy pouze na část tabulky • Příklady: • Aktivní a neaktivní položky • Historické záznamy
• Možnosti • Rozdělení tabulek • Přidání tabulky (duplicitní záznamy)
• Synchronizace • Table partitioning • Triggery • Aplikační logika
76
Vertikální rozdělení tabulek • Přístupy pouze na některé sloupce tabulky • Příklady: • Bloby, obrázky, popisy
• Možnosti • Rozdělení tabulek • Přidání tabulky (duplicitní záznamy) • Vytvoření indexu
• Synchronizace • Triggery • Aplikační logika
77
Uložení vypočtených dat • Přidání sloupce • Hodnoty jsou závislé na jiných hodnotách řádku • Hodnoty odpovídají primárnímu klíči (a libovolných datech)
• Přidání tabulky • Synchronizace • Trigerry • Uložené procedury • Aplikační logika
• Nutno zavést procedury pro údržbu 78
Eliminace nákladných joinů • Neustálé dotahování hodnot z číselníků • Omezení datových serverů (Sybase třicet tabulek v jednom joinu) • Suptype/supertype vazba • Možnosti • Redundantní data • Spojení tabulek
79
Fyzické uložení tabulek • Cíl • Distribuce zátěže na co nejvíce fyzických disků
• Rozložení tabulek/indexů na disky • Možnosti datových serverů (tablespace, segment) • Možnosti hardware (SAN, NAS, diskové pole) • Pouze RAID 1+0 • Vazba na počet procesorů • Možnosti paralelního zpracování datového serveru 80
Doporučená literatura • Typy modelů http://www.1keydata.com/datawarehousing/c oncepts.html • Steven Feuerstein: Ideas for Oracle PL/SQL Naming Conventions and Coding Standards (http://www.ToadWorld.com/SF/standards) • Microsoft SQL Server 2000 Unleashed (2nd Edition) by Ray Rankins, Paul Jensen, and Paul T. Bertucci (Paperback - 18 Dec 2002)
81
