Bazy Danych – egzamin poprawkowy, 2012 – rozwi ˛

azania

1

Zadania

1. (20p) Stwórz diagram ER dla nast˛epuj ˛

acego opisu bazy danych. W szczegól-

no´sci oznacz słabe encje, klucze, rodzaje zwi ˛

azków (czy wiele do jednego,

integralno´s´c referencyjn ˛

a).

Szpitale posiadaj ˛

a nazw˛e i adres. Sale w szpitalach posiadaj ˛

a numery. Os-

oby posiadaj ˛

a imi˛e, nazwisko oraz numer pesel. Niektóre osoby to pacjenci.

Niektóre osoby to lekarze. Lekarze posiadaj ˛

a specjalizaje, pacjenci posiadaj ˛

a

wiek. Ka˙zdy lekarz pracuje w dokładnie jednym szpitalu. Ka˙zdy pacjent
przechodzi w szpitalu przynajmniej jedn ˛

a konsultacj˛e z lekarzem. Konsul-

tacja taka ma unikaln ˛

a dat˛e. Ka˙zdy pacjent znajduje si˛e w jednej z sal szpi-

tala.

2. (10p) Przekształ´c stworzony w poprzednim zadaniu diagram ER na projekt

relacyjny. W przypadku trudno´sci prosz˛e o stworzenie projektu relacyjnego
na podstawie samego opisu w zadaniu powy˙zej.

3. (10p) W relacji R(A, B, C, D, E, F ) wyst˛epuj ˛

a nast˛epuj ˛

ace zale˙zno´sci funkcyjne:

AC → B, BD → F , F → C, F → E. Wyznacz wszystkie klucze w relacji
R. Wska˙z, które klucze s ˛

a minimalne.

4. Mamy dane tabele Zwierzeta(id, gatunek, nr_klatki), Klatki(numer, max_liczba),

Pozwolenia(nazwisko, gatunek), Opiekunowie(nazwisko,nr_klatki).

Klucze relacji s ˛

a podkre´slone. max_liczba okre´sla maksymaln ˛

a liczb˛e zwierz ˛

at

w klatce (bez rozró˙znienia na gatunki). Pozwolenia okre´sla kto mo˙ze opiekowa´c
si˛e danym gatunkiem zwierz ˛

at a Opiekunowie okre´sla, kto jest odpowiedzialny

za zwierz˛eta w danej klatce. W jednej klatce mog ˛

a znajdowa´c si˛e zwierz˛eta

ró˙znych gatunków, wiele osób mo˙ze opiekowa´c si˛e t ˛

a sam ˛

a klatk ˛

a.

Uwaga. Liczba gatunków nie jest ograniczona. Je´sli jednak, kto´s b˛edzie
potrafił poprawnie napisa´c cz˛e´s´c z kwerend dla ustalonej liczby gatunków
(’goryl’, ’mysz’, ’kot’) to otrzyma połow˛e punktów.

Napisz zapytania SQL dla:

(a) (3p) list˛e klatek, w których przebywaj ˛

a jednocze´snie koty i myszy,

(b) (3p) lista klatek, które nie s ˛

a zapełnione (liczba zwierz ˛

at jest mniejsza

od maksymalnej),

1

(c) (3p) lista osób i zwierz ˛

at, którymi dana osoba mogłaby si˛e opiekowa´c,

gdy˙z ma odpowiednie pozwolenie,

(d) (3p) liste osób i klatek, takich ˙ze w danej klatce znajduj ˛

a si˛e zwierz˛eta,

którymi mogłaby opiekowa´c si˛e dana osoba,

(e) (3p) usunie z tabeli Opiekunowie wszystkie wiersze, dla których os-

oba nie posiada pozwolenia na opiekowanie si˛e ˙zadnym gatunkiem
zwierz˛ecia, które znajduje si˛e w danej klatce.

5. (15p) Napisz program PL/SQL, który zmniejszy o 1 warto´s´c max_liczba we

wszystkich klatkach, w których znajduj ˛

a si˛e zwierz˛e z gatunku ’goryl’, a

zwi˛ekszy o 1 warto´s´c max_liczba we wszystkich klatkach, w których wys-
t˛epuje zwierz˛e z gatunku ’mysz’ (je´sli w klatce wyst˛epuje zarówno goryl jak
i mysz, to warto´s´c max_liczba nie powinna si˛e zmieni´c).

6. (15p) Napisz wyzwalacz, który po usuni˛eciu pozwolenia na opiek˛e nad danym

gatunkiem, usunie z tabeli Opiekunowie wszystkie rekordy, dla których os-
oba nie mo˙ze opiekowa´c si˛e ˙zadnym gatunkiem wyst˛epuj ˛

acym w klatce.

7. Rozwa˙zmy relacje R(A,B) oraz S(B,C).

(a) (10p) Napisz równowa˙zne zapytanie bez zagnie˙zd˙zonego select: select

R.A S.B, S.C from R, S where R.B=S.B and S.C <= all (select S1.C
from S S1 where S1.B= S.B) ;

(b) (5p) Zoptymalizuj powy˙zsze zapytanie, wiedz ˛

ac, ˙ze B jest kluczem w

S.

2

Rozwi ˛

azania

Poni˙zej zaprezentowano przykładowe rozwi ˛

azania cz˛e´sci zada´n. Nie zawsze s ˛

a to

rozwi ˛

azania optymalne.

1. Poni˙zej przykładowy diagram. Encja Sale jest słab ˛

a encj ˛

a gdy˙z ró˙zne sale w

ró˙znych szpitalach maj ˛

a te same numery. Dlatego cz˛e´s´c klucza dla encji Sale

jest brana z klucza encji Szpitale poprzez relacj˛e w. Encja Osoby dzieli si˛e
na Lekarze i Pacjenci jednak cz˛e´s´c atrybutów jest wspólnych dla obu tych
grup.

2

Szpitale

nazwa

adres

w

Sale

numer sali

imie

pracuje w

Osoby

nazwisko

pesel

le˙zy na

ISA

Lekarze

Pacjenci

wiek

specjalizacja

konsultuje

data

3

2. Przekładaj ˛

ac powy˙zszy diagram na relacje w bazie danych na pocz ˛

atku tworzymy

relacj˛e Szpitale(nazwa, adres). Nast˛epnie, tworzymy relacj˛e Sale(numer_sali,
nazwa_szpitala). Zamiast jednej relacji Osoby mo˙zemy stworzy´c dwie relacje:
Lekarze(pesel, imie, nazwisko, specjalizacja, nazwa_szpitala) oraz Pacjenci(pesel,
imie, nazwisko, wiek, numer_sali, nazwa_szpitala). Poniewa˙z pracuje w oraz
le˙zy na

były funkcyjne, mo˙zemy nie tworzy´c dla nich odr˛ebnych relacji a

wszystkie istotne informacje umieszczamy w powy˙zszych dwóch relacjach
(bez redundancji informacji). Na koniec tworzymy relacj˛e opisuj ˛

a konsultuje

jako: Konsultacja(pesel_lekarza, pesel_pacjenta, data).

3. W skład ka˙zdego klucza musz ˛

a wchodzic atrybuty A i D one same nie

pozwalaj ˛

a wyznaczy´c jeszcze warto´sci pozostałych atrybutów. Jednym z kluczy

minimalnych jest zbiór A, B, D. Znaj ˛

ac B i D wyznaczymy warto´s´c F. Zna-

j ˛

ac F wyznaczymy warto´s´c C i E. W ten sposób wyznaczyli´smy warto´s´c

ka˙zdego atrybutu w relacji.

Inny klucz minimalny to zbiór A, C, D. Znaj ˛

ac A i C wyznaczymy warto´s´c

B a znaj ˛

ac B mo˙zemy ju˙z wyznaczy´c warto´s´c ka˙zdego innego atrybutu jak

poprzednio.

Ostatni klucz minimalny to zbiór A, D, F.

4.

(a) s e l e c t Z1 . n r _ k l a t k i f r o m Z w i e r z e t a Z1 ,

Z w i e r z e t a Z2

w h e r e z1 . n r _ k l a t k i = z2 . n r _ k l a t k i

and z1 . g a t u n e k = ’ mysz ’ and z2 . g a t u n e k = ’ k o t ’ ;

(b) s e l e c t K . numer f r o m K l a t k i K

w h e r e k . m a x _ l i c z b a > ( s e l e c t

c o u n t ( ∗ )

f r o m z w i e r z e t a Z
w h e r e z . n r _ k l a t k i = K . numer ) ;

(c)

(d)

(e) d e l e t e f r o m o p i e k u n o w i e o

w h e r e n o t

e x i s t s

( s e l e c t z . i d f r o m z w i e r z e t a z ,

p o z w o l e n i a p

w h e r e o . n r _ k l a t k i = z . n r _ k l a t k i
and z . g a t u n e k =p . g a t u n e k
and p . n a z w i s k o =o . n a z w i s k o ) ;

5. Prosz˛e spróbowa´c napisa´c t ˛

a procedur˛e u˙zywaj ˛

ac tylko jednego kursora ale

za to z parametrem, którym b˛edzie nazwa gatunku.

4

d e c l a r e
c u r s o r c_mysz i s

s e l e c t k . numer , k . m a x _ l i c z b a f r o m k l a t k i k

w h e r e e x i s t s

( s e l e c t z . n r _ k l a t k i f r o m z w i e r z e t a z

w h e r e z . n r _ k l a t k i =k . numer

and z . g a t u n e k = ’ mysz ’ )

f o r u p d a t e o f k . m a x _ l i c z b a ;
c u r s o r

c _ g o r y l

i s

s e l e c t k . numer , k . m a x _ l i c z b a f r o m k l a t k i k

w h e r e e x i s t s

( s e l e c t z . n r _ k l a t k i f r o m z w i e r z e t a z

w h e r e z . n r _ k l a t k i =k . numer

and z . g a t u n e k = ’ g o r y l ’ )

f o r u p d a t e o f k . m a x _ l i c z b a ;

v_numer K l a t k i . numer%t y p e ;

v _ l i c z b a

K l a t k i . m a x _ l i c z b a%t y p e ;

b e g i n

o p e n c_mysz ;

l o o p

f e t c h c_mysz i n t o v_numer ,

v _ l i c z b a ;

e x i t when c_mysz%n o t f o u n d ;

u p d a t e K l a t k i

s e t

m a x _ l i c z b a = v _ l i c z b a +1

w h e r e c u r r e n t

o f c_mysz ;

end l o o p ;

c l o s e c_mysz ;

o p e n c _ g o r y l ;

l o o p

f e t c h

c _ g o r y l

i n t o v_numer ,

v _ l i c z b a ;

e x i t when c _ g o r y l%n o t f o u n d ;
i f

v _ l i c z b a >0 t h e n

u p d a t e K l a t k i

s e t

m a x _ l i c z b a = v _ l i c z b a −1

w h e r e c u r r e n t

o f c _ g o r y l ;

end i f ;

end l o o p ;

c l o s e

c _ g o r y l ;

end ;

6. Poni˙zszy wyzwalacz nie zawiera opcji "for each row". Dlaczego?

5

c r e a t e

o r r e p l a c e

t r i g g e r

u s u n _ o p i e k u n o w i e

a f t e r

d e l e t e on p o z w o l e n i a

b e g i n

d e l e t e f r o m o p i e k u n o w i e o w h e r e

n o t

e x i s t s

( s e l e c t Z . i d

f r o m z w i e r z e t a z ,

p o z w o l e n i a p

w h e r e p . n a z w i s k o =o . n a z w i s k o and

p . g a t u n e k = z . g a t u n e k and

z . n r _ k l a t k i = o . n r _ k l a t k i ) ;

end ;

6