Databáze I

Přednáška 2

Transformace E -R modelu do relačního modelu

(speciality)

• zaměříme se na dva případy z předmětu Analýza a modelování dat– reprezentace entitního podtypu

• hierarchie ISA

– reprezentace výlu čných vztah ů

• a ještě– reprezentace n-árního vztahu (n>2) a vztahu

mezi entitami téhož entitního typu

Reprezentace entitního podtypu

OSOBA

RČ JMÉNO

ŽENAMUŽ

DELKA_PRAXEVĚK

• 3 možnosti transformace

1. Jedna relace

• relace obsahující atributy zdroje relace a všech podtypů, speciální atribut rozlišující podtyp, event. cizí klíče ze vztahů 1:N, 1:1

OSOBA(RČ,JMÉNO,TYP_OS, VĚK, DELKA_PRAXE)

– jedna z hodnot atributů VĚK nebo DÉLKA_PRAXE budou u každého prvku relace (záznamu) mít hodnotu NULL

2. Relace pouze pro podtypy

• relace pro každý podtyp, obsahující atributy podtypu a atributy zdroje hierarchie (nadtypu) včetně atributu odpovídající klíči zdroje

MUŽ(RČ,JMÉNO,VĚK)ŽENA(RČ,JMÉNO,DELKA_PRAXE)

3. Relace pro všechny typyv hierarchii

• relace pro každý nadtyp a podtyp, obsahující klíče zdroje a pouze vlastní atributy

OSOBA(RČ,JMÉNO)MUŽ(RČ,VĚK)

ŽENA(RČ,DELKA_PRAXE)

Reprezentace slabého entitního typu

FILM(JMÉNO)KOPIE(ČÍSLO_KOPIE,JMÉNO)

FILM(0,n)(1,1)

KOPIE JE_OD

ČÍSLO_KOPIE JMÉNO

Reprezentace výlu čného vztahu

• 2 možnosti transformace

• nejprve obecně

1. Pomocí spole čných domén

• pouze, mají-li identifikační klíče K2 a K3 stejnou doménu

E1(K1,ID,K,A)E2(K2,B)E3(K3,C)

– ID je atribut rozlišující typ vztahu, K je atribut pro hodnoty typu K2, K3

2. Pomocí cizích klí čů

• nemají-li K2 a K3 společné domény, je nutné mít v E1 dva cizí klíče

E1(K1,K2,K3,A)E2(K2,B)E3(K3,C)

– v relaci E1 nesmí být prvek (v tabulce řádek), který by měl současně neprázdnou hodnotu atributu K2 a K3 (jeden z nich musí být NULL), toto je nutné zajistit speciálním integritním omezením (prakticky triggerem)

Příklad

VSTUPENKA(CISLO,JMENO_F,NAZEV,JMENO_F1,STATUS)PREDSTAVENI(JMENO_F,NAZEV,DATUM)

FILMECEK(JMENO_F)

Příklad

Poznámka:

• PREDSTAVENI je slabý entitní typ, identifikačně závislý na entitním typu KINO, proto je klíčem relace dvojice (JMENO_F,NAZEV)

Reprezentace více -árního vztahu

SLUŽBA

ZÁKAZNÍK

ČERPÁ

PROVOZOVNA IDS

ČP

IDZ

DATUM

Reprezentace více -árního vztahu

ZÁKAZNÍK(IDZ)PROVOZOVNA(ČP)

SLUŽBA(IDS)

ČERPÁ(IDZ,ČP,IDS,DATUM)

Reprezentace více -árního vztahu

ZBOŽÍOBCHODNÍK

DODÁVÁ

KAT_ČIDO

DODÁVÁ(IDO_KDO,IDO_KOMU,KAT_Č)

JMÉNO NÁZEV

IDO JMÉNO

1 Potkan & syn

2 Návnada, nástraha, past

OBCHODNÍK

KAT_Č Název1 Pastička

2 Jed na potkany

ZBOŽÍ

DODÁVÁ

IDO_KDO IDO_KOMU KAT_ Č1 2 1

Reprezentace binárního vztahu mezi entitami stejného typu

DÍL

JE_SOUČÁSTÍ

ID_D

DÍL(ID_D,NÁZEV,ID_PATŘÍ)

NÁZEV

1SE_SKLÁDÁ

N

• vztah 1:N – do relace DÍL přidám cizí klíč:

DÍL

ID_D NÁZEV ID_PATŘÍ1 Čerpadlo NULL

2 Hřídel 1

3 Lopatkové kolo 1

4 Ložisko 6202 1

Relační algebra

• množina operací, které přiřazují relaci (relacím) jinou relaci– důsledek: výsledek jedné operace může být

argumentem další operace

• na DB relace aplikujeme základní operace:– kartézský sou čin (x)– sjednocení (U)– rozdíl (-)– projekce– selekce– přirozené a théta – spojení

Proč relační algebra?• relační algebra jako matematický kalkul

je silný prostředky pro manipulaci dat– je základem dotazovacího jazyka SQL

• jsou vypracovány vhodné metody návrhu schémat relační databáze– normální formy relací– databáze mají pak „dobré“ vlastnosti

Projekce relace R se schématem R(A) na schéma B, B ⊂ A– značení R[B]– výsledkem je relace se schématem B– z relace vybere atributy podle schématu B– vymaže duplicitní n-tice

• tabulkově:– vybere sloupce podle seznamu B– vymaže duplicitní řádky

Příklad:ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ):521006/5678 Josef Novák541212/1234 Josef Houska320612/1234 Franta Kuldanů

• projekce na množinu atributů JMÉNO:ČTENÁŘ[JMÉNO]JosefFranta

Selekce relace R se schématem R(A) podle podmínky ∅– značení R(∅)– výsledkem je relace se schématem A– z relace vybere n-tice, které splňují

podmínku ∅, ∅ je boolský výraz– ve výrazu je možné použít logické spojky

and, or, …

• tabulkově:– vybere řádky, které splňují podmínku ∅– vymaže duplicitní řádky

Příklad:ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ):521006/5678 Josef Novák541212/1234 Josef Houska320612/1234 Franta Kuldanů

• selekce dle jména:ČTENÁŘ(JMÉNO=″Franta″)

320612/1234 Franta Kuldanů

Přirozené spojení relace R se schématem R(A) a relace S se schématem S(B)– značení R*S– výsledkem je relace se schématem A∪B– u∈R*S iff u[A]∈R(A) a u[B]∈S(B)– spojím přes co největší množinu společných

atributů (přirozené spojení)

• tabulkově:– spojí řádky, které mají shodné hodnoty

společných sloupců

Příklad:KNIHA(AUTOR, TITUL, ISBN)EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK, ISBN)Němcová Babička 80-85190-38-9Jirásek U nás 80-11111-22-3

1 105 12.1.1990 80-85190-38-92 100 20.3.1990 80-85190-38-93 103 21.3.1990 80-11111-22-3

– největší množina společných atributů je ISBN– výsledek operace spojení bude mít atributy:– PŘÍR_Č, CENA, D_NÁK, ISBN, AUTOR, TITUL

– spojím každý řádek relace KNIHA s každým řádkem relace EXEMPLÁŘ, do výsledku dám jenom ty, které mají shodnou hodnotu atributu ISBN; atribut ISBN je ve výsledné relaci pouze jednou (vyplývá z operace sjednocení atributů)

KNIHA*EXEMPLÁŘ

1 105 12.1.1990 80-85190-38-9 Němcová Babička2 100 20.3.1990 80-85190-38-9 Němcová Babička3 103 21.3.1990 80-11111-22-3 Jirásek U nás

Θ spojení relace R se schématem R(A) a relace S se schématem S(B)– značení R[D Θ F]S– výsledkem je relace se schématem A∪B– Θ je podmínka spojení, D ∈ A, F ∈ B– u∈R*S iff u[A]∈R(A) a u[B]∈S(B) a

u[R.D] Θ u[S.F]– spojím n- tice, které splňují danou podmínku

• tabulkově:– spojím řádky, u kterých je splněna podmínka Θ

Příklad:

Relace R:

A1 A2 A3

1 A 2

2 X 6

Relace S:

B1 B2 B3

4 8 Ahoj

5 4 Zdar

Relace R[A3<B2]S:

A1 A2 A3 B1 B2 B3

1 A 2 4 8 Ahoj

1 A 2 5 4 Zdar

2 X 6 4 8 Ahoj

Příklad: – Jak zapíši přirozené spojení relací KNIHA

a EXEMPLÁŘ pomocí Θ spojení?

KNIHA(AUTOR, TITUL, ISBN)EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK, ISBN)

– podmínka Θ bude rovnost hodnot atributů ISBN

KNIHA*EXEMPLÁŘ=

KNIHA[KNIHA.ISBN = EXEMPLÁŘ.ISBN]EXEMPLÁŘ

D FΘ

• operace relační algebry mohu aplikovat na stejnou relaci, tj. relaci mohu spojit i samu se sebou, provést jejich kartézský součin

• příklad Θ spojení relace DÍL se sebou:

DÍL(ID_D,NÁZEV,ID_PATŘÍ)

DÍL[ID_PATŘÍ=ID_D]DÍL

ID_D NÁZEV ID_PATŘÍ ID_D NÁZEV ID_PATŘÍ

1 Čerpadlo NULL

2 Hřídel 1 1 Čerpadlo NULL

3 Lopatkové kolo 1 1 Čerpadlo NULL

4 Ložisko 6202 1 1 Čerpadlo NULL

• užitečná je i operace přejmenování atributu nebo přejmenování relace

Kartézský sou čin x relace R se schématem R(A) a relace S se schématem S(B)– značení R x S– výsledkem je relace se schématem, které má

všechny atributy obou relací (společné atributy se vyskytují vícekrát)

– spojím každou n-tice s každou

• tabulkově:– spojím každý řádek s každým

Příklad:KNIHA(AUTOR, TITUL, ISBN)EXEMPLÁŘ(PŘÍR_Č, CENA, D_NÁK, ISBN)Němcová Babička 80-85190-38-9Jirásek U nás 80-11111-22-3

1 105 12.1.1990 80-85190-38-92 100 20.3.1990 80-85190-38-93 103 21.3.1990 80-11111-22-3

• výsledek operace kartézský součin

EXEMPLÁŘ x KNIHA

bude mít atributy:– použiji přejmenování atributů:

PŘÍR_Č, CENA, D_NÁK, ISBN_1, AUTOR, TITUL, ISBN_2

– použiji tečkovou notaci a názvy relací

PŘÍR_Č, CENA, D_NÁK, EXEMPLÁŘ.ISBN, AUTOR, TITUL, KNIHA.ISBN

– spojím každý řádek relace EXEMPLÁŘ s každým řádkem relace KNIHA

EXEMPLÁŘ x KNIHA

1 105 12.1.1990 80-85190-38-9 Němcová Babička 80-85190-38-9

1 105 12.1.1990 80-85190-38-9 Jirásek U nás 80-11111-22-3

2 100 20.3.1990 80-85190-38-9 Němcová Babička 80-85190-38-9

2 100 20.3.1990 80-85190-38-9 Jirásek U nás 80-11111-22-3

3 103 21.3.1990 80-11111-22-3 Němcová Babička 80-85190-38-9

3 103 21.3.1990 80-11111-22-3 Jirásek U nás 80-11111-22-3

• přirozené spojení lze zapsat pomocí kartézského součinu a operací selekce a projekce takto:

KNIHA * EXEMPLÁŘ=

(KNIHA x EXEMPLÁŘ)(KNIHA.ISBN=EXEMPLÁŘ.ISBN)

[PŘÍR_Č, CENA, D_NÁK, KNIHA.ISBN, AUTOR, TITUL]

Průnik, sjednocení, rozdíl• relace jsou množiny, lze tyto operace

aplikovat i na relace za předpokladu– schémata relací jsou stejná (tabulky mají stejné

sloupce)– domény jsou stejné

• průnik– u∈R∩S iff u∈R a u ∈ S– výsledkem jsou řádky, které jsou současně v

obou tabulkách

• sjednocení– u∈R U S iff u∈R nebo u∈S– data ze dvou tabulek sloučím do jedné

• rozdíl– u∈R - S iff u∈R a u∉S– vyberu řádky, které jsou v R a nejsou v S

RČ JMÉNO PŘÍJMENÍ

320612/1234 František Kuldanů

521006/5678 Josef Novák

ČTENÁŘ

ISBN TITUL AUTOR80-11111-22-3 U nás A. Jirásek

80-85190-38-9 Babička B. Němcová

KNIHA

EXEMPLÁŘ

25.3.19901002

D_NÁKCENAPŘÍR_Č

26.3.19901503

25.3.19901001

ISBN

80-85190-38-9

80-85190-38-9

80-11111-22-3

RČ PŘÍR_Č DAT

320612/1234 1 10.11.2005

521006/5678 3 20.11.2005

SI_VYPŮJČIL

Příklad:• jméno a příjmení čtenáře, kteří mají

vypůjčenu Babičku

(((((KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)[PŘÍR_Č])*SI_VYPŮJČIL)*ČTENÁŘ)[JMÉNO,PŘÍJMENÍ]

jinak((KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ*

SI_VYPŮJČIL*ČTENÁŘ)[JMÉNO,PŘÍJMENÍ]

• rozebereme výraz(((((KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)[PŘÍR_Č])*SI_VYPŮJČIL)*ČTENÁŘ)[JMÉNO,PŘÍJMENÍ]

KNIHA(TITUL=″Babička″)– je selekce, tj. z relace knihy vybereme n-tice, kde

atribut titul má hodnotu „Babička“; výsledkem je relace:

80-85190-38-9 Babička B. Němcová

KNIHA(TITUL=″Babička″)[ISBN]– zde je k selekci přidána projekce, tj. vybereme

pouze sloupec ISBN; výsledkem je relace, označme ji S:

80-85190-38-9

(KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)– je o spojení relace S (viz předchozí blána) s relací

EXEMPLÁŘ, tj. spojíme každý „řádek“ relace S(obsahuje pouze jeden řádek) s řádky relace EXEMPLÁŘ přes největší množinu společných atributů (zde ISBN); spojíme ty řádky, které mají v relaci S a EXEMPLÁŘ stejnou hodnotu atributu ISBN; výsledek je relace R:

PŘÍR_Č CENA D_NÁK ISBN1 100 25.3.1990 80-85190-38-9

2 100 25.3.1990 80-85190-38-9

(KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)[PŘÍR_Č]– je aplikace projekce na „modrou“ relaci R z předchozí

blány; vybere se pouze přírůstkové číslo a vymažou se duplicitní řádky; výsledek je relace P:

PŘÍR_Č12

((KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)[PŘÍR_Č])*SI_VYPŮJČIL– je spojení předcházející relace P s relací

SI_VYPŮJČIL, tj. spojí se řádky, které mají shodnou hodnotu atributu PŘÍR_Č; vznikne relace T

((((KNIHA(TITUL="Babička")[ISBN]*EXEMPLÁŘ)[PŘÍR_Č])*SI_VYPŮJČIL)*ČTENÁŘ– je spojení relace T s relací čtenář, tj. spojí se řádky, které mají shodnou hodnotu atributu RČ

RČ PŘÍR_Č DAT320612/1234 1 10.11.2005

RČ JMÉNO PŘÍJMENÍ PŘÍR_Č DAT

320612/1234 František Kuldanů 1 10.11.2005

(((((KNIHA(TITUL=″Babička″)[ISBN]*EXEMPLÁŘ)[PŘÍR_Č])*SI_VYPŮJČIL)*ČTENÁŘ)[JMÉNO,PŘÍJMENÍ]– nakonec se provede projekce na atributy JMÉNO,

PŘÍJMENÍ, tj. vyberou se tyto sloupce :

JMÉNO PŘÍJMENÍ

František Kuldanů

Referenční integrita

• mějme dvě relace R(A), S(B), společná množina atributů C⊂A, C⊂B

• referenční integrita: platíS[C] ⊂ R[C]

• příklad:ČTENÁŘ(RČ, JMÉNO, PŘÍJMENÍ)

SI_VYPŮJČIL(RČ, PŘÍR_Č, DAT_VYP)

cizí klí če

• referenční integrita:– aby byla splněna referenční integrita, musí

platit:

SI_VYPŮJČIL[RČ] ⊂ ČTENÁŘ[RČ]• srozumitelně:

– v tabulce SI_VYPŮJČIL („podřízené“) se nesmí v poli RČ vyskytnout žádná hodnota (žádné takové rodné číslo), která by nebyla v tabulce ČTENÁŘ („nadřízené“)

• cizí klíč RČ ve SI_VYPŮJČIL by jinak odkazoval na neexistujícího čtenáře v ČTENÁŘ

• databázové stroje umějí automaticky zajistit referenční integritu– při výmazu čtenáře se smažou všechny

jeho výpůjčky– při změně hodnoty primárního klíče

(rodného čísla) v tabulce ČTENÁŘ se automaticky změní i rodná čísla v tabulce SI_VYPŮJČIL