Analýza a modelování dat 3. přednáška. Helena Palovská

Analýza a modelování dat 3. přednáška Helena Palovská

Historie databázových modelů

Relační model dat ●

Codd, E.F. (1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM

●

1975 IBM system R

●

1979 Oracle V2

●

1983 IBM DB2

●

Mnoho diskusí mezi puristy a pragmatiky...

Relační model – idea ●

●

Databáze uchovává fakta, nebo tvrzení považovaná za fakta. Např.: –

"Jan" je křestní jméno.

–

"Novák" je příjmení.

–

"123456789" je telefonní číslo.

–

Člověk s křestním jménem "Jan" a příjmením "Novák" má telefon "123456789".

Matematické značky místo vět

●

–

Křestní_jméno("Jan")

–

Příjmení("Novák")

–

Telefonní_číslo("123456789")

–

Má_telefon("Jan","Novák","123456789")

Umoňují efektivněji uvažovat nad větami a jejich kombinacemi jakožto nad fakty.

Abstrakce „vět“ – relace ●

x,x,z,w … proměnné –

Křestní_jméno(x)

–

Příjmení(y)

–

Telefonní_číslo(z)

–

Má_telefon(x,y,z)

–

Telefonní_operátor(w)

–

Poskytuje_spojení_s(w,z)

unární relace

ternární relace unární relace binární relace

Lze chápat i jako logické operátory...

Unární relace vs. domény –

Křestní_jméno(x)

Co to znamená ? Buď: –

„X je smysluplné křestní jméno.“ ●

„Křestní_jméno“ znamená doménu.

nebo: –

„V oblasti, o kterou se zajímáme, existuje člověk s křestním jménem X.“ ●

„Křestní jméno“ znamená unární relaci.

Konstrukce matematické relace ●

Máme nějaké domény D1, D2, ..., Dn

●

Vytvoříme kartézský součin D1 x D2x … x Dn –

●

(každé di je prvek Di)

Nějaká podmnožina D1 x D2x … x Dn ≡ relace –

●

n-tice (d1, d2, ..., dn)

tj. nějaká množina n-tic

Může mít smysl či význam, např.: –

Kapitán, první důstojník, druhý důstojník, lodivod, kormidelník ..., tvoří posádku lodi.

Databázová relace ●

Názvy pro jednotlivé pozice v n-tici (alà role): –

Posádka_lodi(kapitán:x, první_důstojník:y, druhý_důstojník:z, …)

–

x je z domény lidí s kapitánskou licencí, y a z jsou z domény lodních důstojníků, ...

Více-ární „relace“ ●

●

Zákazník(id:i, jmeno:j, prijmeni:p, adresa:a, telefon:t) Interpretace: –

Existuje zákazník s id=..., jménem=..., atd.

–

„id“, „jmeno“, „prijmeni“, „adresa“, „telefon“ jsou názvy atributů

–

atribut „jmeno“ má doménu Křestní_jména, atribut „telefon“ má doménu Telefony...

Relační model ●

Používá relace i „relace“ –

jako formální konstrukce

–

tím umožňuje použít formální aparát ● ●

relační algebru relační kalkul

Relační algebra ●

Spojení – JOIN

●

Projekce – výběr jen některých z atributů

●

Selekce – výběr jen některých z n-tic –

na základě podmínky, podmínek

●

Sjednocení množin

●

Průnik množin

●

Rozdíl množin

●

Kartézský součin množin

Formulace požadavků na data ●

Který dotaz nelze formulovat pomocí relační algebry? –

●

● ●

zkuste jako cvičení

Odvozené relace definujeme jako výsledky operací relační algebry... Jazyk manipulace pomocí relační algebry... Informační princip (E.F.Codd): Všechny informace jsou reprezentovány jako datové hodnoty ve vztazích (=relacích).

SQL a relační algebra ●

Se odlišují –

v relaci mohou být duplicitní n-tice

–

NULL hodnoty (v různém významu?)

–

je možno využít pořadí sloupců

–

je možno specifikovat pořadí řádků

–

agregace

–

LEFT/RIGT join

–

...

Výhody relační algebry (ideálního „SQL“) ●

Jednoduchý jazyk na logické úrovni –

fyzické operace jsou zapouzdřeny, v zodpovědnosti SŘBD ●

● ●

změny ve fyzické realizaci neovlivní sémantiku příkazů jazyka

Vyjadřovací síla jazyka Neprocedurální dotazovací i manipulační jazyk

Nevýhody relačního modelu ●

●

Není nikde dokonale implementován, různé SŘBD mají různé verze SQL SQL (nebo dokonce relační algebra či kalkul) jsou pro leckoho obtížné

Jak navrhnout relační databázi? ●

ER model → relační schéma –

transformace je rutinní postup ●

●

viz např. http://krokodata.vse.cz/DM/Mapovani

„Zdola nahoru“ na základě funkcionálních závislostí

Funkcionální závislosti ●

●

Atributy: přednáška, učitel, místnost, hodina, student, známka Platné závislosti (v daném semestru): –

každou přednášku přednáší jediný učitel

–

každý student má z každé přednášky jedinou známku

–

v jedné místnosti se v jedné vyučovací hodině koná jediná přednáška

Funkcionální závislosti

●

–

každý učitel učí v jedné vyučovací hodině v jediné místnosti

–

každý student má na svém rozvrhu v jedné vyučovací hodině jedinou místnost

Symbolicky P→U, PS→Z, HM→P, HU→M, HS→M

(Tyto konkrétní závislosti jsou neodvoditelné žádná z ostatních.) Detailně viz.

http://en.wikipedia.org/wiki/Functional_dependency

Multizávislost ●

●

Všechny kombinace (hodina,místnost), do kterých je umístěna výuka nějaké přednášky, má na svém rozvrhu každý student, který má zapsanou tuto přednášku. Polopaticky: –

●

v kontextu jedné přednášky obsahuje rozvrh kombinace studentů a rozvrhových okének způsobem „každý s každým“

Symbolicky: –

PHM, a také PS (v kontextu P→U,PS→Z)

http://en.wikipedia.org/wiki/Multivalued_dependency

Návrh dekompozicí ●

Známe závislosti P→U, PS→Z, HM→P, HU→M, HS→M 3 1

●

2

Multizávislost PHM, a také PS 3

●

Postupně navrhujeme relace: –

Přednáší(P,U)

–

Byl_ohodnocen(S,P,Z)

–

Se_vyučuje(P,H,M)

–

„mezi relacemi“ zbylo omezení HS→M

Návrh dekompozicí ●

●

Jiné pořadí –

Byl_ohodnocen(P,S,Z)

–

Přednáší(P,U)

–

Je_podle_rozvrhu_v(S,H,M)

–

Má_na_rozvrhu(S,H,P)

–

„mezi relacemi“ zbylo HM→P a PH→M

Závěr: Při dekompozici podle závislostí volí analytik „rozumné“ pořadí.

Návrh syntézou ●

●

●

●

Vytvoříme soustavu závislostí vzájemně nedovoditelných jedna z ostatních. Seskupíme závislosti podle společných levých stran. Pro každou skupinu navrhneme relaci, bude mít klíč tvořen tou společnou levou stranou. Př.: P→U, PS→Z, HM→P, HU→M, HS→M –

dá se dokázat HMHU

–

Výsledek: (P,S,Z),(H,S,M),(U,H,M,P)

Normalizace databáze ●

Vyhnout se aktualizačním anomáliím

●

Mít stabilnější databázové schéma –

●

Mít pravdivější a srozumitelnější databázové schéma –

●

při rozšiřování světa zájmu

pro bezchybné dotazy a manipulace

Mít svobodu pro všemožné dotazování –

neztížit některé pohledy na data

Normální formy ●

1NF E.F.Codd 1970 (atributy=atomy)

●

2NF E.F.Codd 1971 (vždy na celém klíči)

●

3NF E.F.Codd 1971 (žádná tranzitivní závislost)

●

BCNF E.F.Codd,R.F.Boyce 1974 (žádná netriviální závislost) –

Směrovací_čísla(Ulice, PSČ, Město)

●

4NF R.Fagin 1977 (žádná netriviální multizávislost)

●

5NF W.Kent 1983 –

„obchodní zástupce prodává typ výrobků značky“

Normální formy ●

6NF Ch.Date,H.Darven,N.Lorentzos 2002 –

●

...?

o temporalitě

ER → RDB schéma ●

●

Důkladná informační analýza zkušeným analytikem, rutinní transformace ⇒

●

normalizovaná databáze?

●

Skoro vždy...

●

A jestli někdo udělal chybu? – Kontrola utilitami hledajícími závislosti... (náročné!)

Nevýhoda relačního modelu ●

●

Normalizovaná databáze láme data do mnoha tabulek. Řešení: –

vědomá a záměrná denormalizace ● ●

–

urychlit některá vyhledávání urychlit některé výpočty

je to kompromis jedněch provozních požadavků (některých dotazů) vůči jiným (jiným dotazům, manipulacím)

PREDMET STUDENT

ZAPIS

os_cis ...

os_cis id_kurz znamka

KURZ

id_kurz id_predmet obdobi den hodina mistnost ucitel Kandidátní klíče pro relaci KURZ: AK1=(odbobi,den,hodina,mistnost) AK2=(obdobi,den, hodina, ucitel)

id_predmet ...

MUZE_UCIT id_predmet os_cis

UCITEL os_cis ...

Omezení pro studenta: TRIGGER BEFORE INSERT,UPDATE(os_cis,id_kurz) na ZAPIS podmínka pro ukončení transakce: EXISTS (select * from KURZ join ZAPIS using(id_kurz) where os_cis=new.os_cis and obdobi=(select obdobi from KURZ where id_kurz=new.id_obdobi) and den=(select den from KURZ where id_kurz=new.id_den) and hodina=(select hodina from KURZ where id_kurz=new.id_hodina) and new.id_kurz<>id_kurz))

PREDMET STUDENT

ZAPIS

KURZ

os_cis ...

os_cis id_kurz obdobi den hodina znamka

id_kurz id_predmet obdobi den hodina mistnost ucitel

id_predmet ...

MUZE_UCIT id_predmet os_cis

UCITEL os_cis ...

Kandidátní klíče pro relaci KURZ: AK1=(odbobi,den,hodina,mistnost) AK2=(obdobi,den, hodina, ucitel) Kandidátní klíč pro relaci ZAPIS: AK=(obdobi,den, hodina, os_cis) Integrita odvozených dat mezi ZAPIS, KURZ: TRIGGER before INSERT,UPDATE(id_kurz) new.obdobi:=select obdobi from KURZ where id_kurz=new.id_kurz; new.den:=select den from KURZ where id_kurz=new.id_kurz; new.hodina:=select hodina from KURZ where id_kurz=new.id_kurz;