Bazy Danych – egzamin poprawkowy, 2012 – rozwiązania 1
Zadania
1. (20p) Stwórz diagram ER dla następującego opisu bazy danych. W szczegól-ności oznacz słabe encje, klucze, rodzaje związków (czy wiele do jednego, integralność referencyjną).
Szpitale posiadają nazwę i adres. Sale w szpitalach posiadają numery. Osoby posiadają imię, nazwisko oraz numer pesel. Niektóre osoby to pacjenci.
Niektóre osoby to lekarze. Lekarze posiadają specjalizaje, pacjenci posiadają wiek. Każdy lekarz pracuje w dokładnie jednym szpitalu. Każdy pacjent przechodzi w szpitalu przynajmniej jedną konsultację z lekarzem. Konsultacja taka ma unikalną datę. Każdy pacjent znajduje się w jednej z sal szpitala.
2. (10p) Przekształć stworzony w poprzednim zadaniu diagram ER na projekt relacyjny. W przypadku trudności proszę o stworzenie projektu relacyjnego na podstawie samego opisu w zadaniu powyżej.
3. (10p) W relacji R( A, B, C, D, E, F ) występują następujące zależności funkcyjne: AC → B, BD → F , F → C, F → E. Wyznacz wszystkie klucze w relacji R. Wskaż, które klucze są minimalne.
4. Mamy dane tabele Zwierzeta(id, gatunek, nr_klatki), Klatki(numer, max_liczba), Pozwolenia(nazwisko, gatunek), Opiekunowie(nazwisko,nr_klatki).
Klucze relacji są podkreślone. max_liczba określa maksymalną liczbę zwierząt w klatce (bez rozróżnienia na gatunki). Pozwolenia określa kto może opiekować się danym gatunkiem zwierząt a Opiekunowie określa, kto jest odpowiedzialny za zwierzęta w danej klatce. W jednej klatce mogą znajdować się zwierzęta różnych gatunków, wiele osób może opiekować się tą samą klatką.
Uwaga. Liczba gatunków nie jest ograniczona. Jeśli jednak, ktoś będzie potrafił poprawnie napisać część z kwerend dla ustalonej liczby gatunków (’goryl’, ’mysz’, ’kot’) to otrzyma połowę punktów.
Napisz zapytania SQL dla:
(a) (3p) listę klatek, w których przebywają jednocześnie koty i myszy, (b) (3p) lista klatek, które nie są zapełnione (liczba zwierząt jest mniejsza od maksymalnej),
1
(c) (3p) lista osób i zwierząt, którymi dana osoba mogłaby się opiekować, gdyż ma odpowiednie pozwolenie,
(d) (3p) liste osób i klatek, takich że w danej klatce znajdują się zwierzęta, którymi mogłaby opiekować się dana osoba,
(e) (3p) usunie z tabeli Opiekunowie wszystkie wiersze, dla których osoba nie posiada pozwolenia na opiekowanie się żadnym gatunkiem zwierzęcia, które znajduje się w danej klatce.
5. (15p) Napisz program PL/SQL, który zmniejszy o 1 wartość max_liczba we wszystkich klatkach, w których znajdują się zwierzę z gatunku ’goryl’, a zwiększy o 1 wartość max_liczba we wszystkich klatkach, w których występuje zwierzę z gatunku ’mysz’ (jeśli w klatce występuje zarówno goryl jak i mysz, to wartość max_liczba nie powinna się zmienić).
6. (15p) Napisz wyzwalacz, który po usunięciu pozwolenia na opiekę nad danym gatunkiem, usunie z tabeli Opiekunowie wszystkie rekordy, dla których osoba nie może opiekować się żadnym gatunkiem występującym w klatce.
7. Rozważmy relacje R(A,B) oraz S(B,C).
(a) (10p) Napisz równoważne zapytanie bez zagnieżdżonego 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ższe zapytanie, wiedząc, że B jest kluczem w S.
2
Rozwiązania
Poniżej zaprezentowano przykładowe rozwiązania części zadań. Nie zawsze są to rozwiązania optymalne.
1. Poniżej przykładowy diagram. Encja Sale jest słabą encją gdyż różne sale w różnych szpitalach mają te same numery. Dlatego część klucza dla encji Sale jest brana z klucza encji Szpitale poprzez relację w. Encja Osoby dzieli się na Lekarze i Pacjenci jednak część atrybutów jest wspólnych dla obu tych grup.
2
nazwa
adres
Szpitale
w
Sale
pracuje w
imie
nazwisko
Osoby
pesel
leży na
ISA
Lekarze
Pacjenci
specjalizacja
wiek
konsultuje
data
3
2. Przekładając powyższy diagram na relacje w bazie danych na początku tworzymy relację Szpitale(nazwa, adres). Następnie, tworzymy relację Sale(numer_sali, nazwa_szpitala). Zamiast jednej relacji Osoby możemy stworzyć dwie relacje: Lekarze(pesel, imie, nazwisko, specjalizacja, nazwa_szpitala) oraz Pacjenci(pesel, imie, nazwisko, wiek, numer_sali, nazwa_szpitala). Ponieważ pracuje w oraz leży na były funkcyjne, możemy nie tworzyć dla nich odrębnych relacji a wszystkie istotne informacje umieszczamy w powyższych dwóch relacjach (bez redundancji informacji). Na koniec tworzymy relację opisują konsultuje jako: Konsultacja(pesel_lekarza, pesel_pacjenta, data).
3. W skład każdego klucza muszą wchodzic atrybuty A i D one same nie pozwalają wyznaczyć jeszcze wartości pozostałych atrybutów. Jednym z kluczy minimalnych jest zbiór A, B, D. Znając B i D wyznaczymy wartość F. Znając F wyznaczymy wartość C i E. W ten sposób wyznaczyliśmy wartość każdego atrybutu w relacji.
Inny klucz minimalny to zbiór A, C, D. Znając A i C wyznaczymy wartość B a znając B możemy już wyznaczyć wartość każdego 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ę spróbować napisać tą procedurę używając tylko jednego kursora ale za to z parametrem, którym będzie nazwa gatunku.
4
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ższy wyzwalacz nie zawiera opcji "for each row". Dlaczego?
5
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