Bazy Danych – egzamin 9 luty, 2012 – rozwiązania 1
Zadania
1. Stwórz diagram ER dla następującego opisu bazy danych drużyn i rozgry-wek lig regionalnych. W szczególności oznacz słabe encje, klucze, rodzaje związków (czy wiele do jednego, integralność referencyjną).
Każda drużyna posiada nazwę, która nie jest unikalna. Każda posiada siedzibę w pewnym mieście. Nazwy drużyn z jednego miasta nie mogą się pow-tarzać. Regiony charakteryzuje unikatowa nazwa. Każde miasto należy do jednego regionu. Miasta mogą mieć takie same nazwy lecz w danym re-gionie każde miasto ma unikatową nazwę. Trenerów charakteryzuje ich imię i nazwisko. Każdy trener trenuje najwyżej jedną drużynę (lub jest niezatrudniony) a każda drużyna posiada dokładnie jednego trenera. Zawodnika charakteryzuje imię i nazwisko. Każdy zawodnik gra w najwyżej jednej drużynie (lub jest niezatrudniony) a drużyna posiada wiele zawodników (i nie może istnieć drużyna bez zawodników). Poszczególne mecze odbywają się pomiędzy drużyną gospodarzy i gości, posiadają datę oraz wynik. W
danym dniu nie odbywaja się więcej niż jeden mecz danej drużyny.
C
D
B
C
3
3
4
4
2. Dane są relacje R( B, C):
i S( C, D)
3
7 Oblicz
3
3
5
2
1
null
null
1.
• R o
n S,
• R o
n R.C= S.C∨S.D<R.C S,
• R − ρT( B,C)( S).
3. Dane są relacje R( A, B, C) i S( B, C, D). Napisz wyrażenie algebry relacji równoważne następującemu zapytaniu SQL lub zapytanie SQL równoważne następującemu wyrażeniu algebry relacji:
(a) select R.A, S.D from R natural join S where R.B=S.C; (b) select A, B from R where B >= all (select C from R
R1 where
R1.C=R.A);
(c) πB,C ( R) − πB,C ( S), (d) πA,C( σA= D( R o n S)),
1
(e) σB>C( πB,C ( R) ∪ πB,C ( S)).
4. Przekształć diagram ER do projekt relacyjny. W relacjach podkreśl atrybuty wchodzące w skład klucza danej relacji.
Fabryki
nalezy do
Firmy
nazwa
produkuje
w
siedziba_w
Produkty
opis
Miasta
populacja
nazwa_produktu
nazwa_miasta
5. Mamy dane tabele Magazyn(id, nazwa, producent, ilosc, cena) oraz Producent(id,nazwa,adres). Magazyn opisuje produkty w magazynie, gdzie id jest unikatowym kluczem, producent jest kluczem obcym z tabeli Producent.
Atrybut id w tabeli Producent jest kluczem własnym. Nazwy towarów nie są unikatowe.
Napisz zapytania SQL dla:
(a) nazw producentow, którzy dostarczają do magazynu sałatę (czyli ’salata’
powinno być podciągiem nazwy towaru),
(b) producenta (id lub nazwa) i średniej ceny towarów przez niego dostar-czanych,
(c) nazwy towaru oraz jego minimalnej, maksymalnej i średniej ceny, (d) producentów (id lub nazwa), którzy nie dostarczyli żadnych towarów, 2
(e) producentów, towaru i jego ceny dla producentów, którzy dostarczają dany towar w najniższej cenie,
(f) nazwy towarów, które posiadają różne ceny, (g) usuń z tabeli Producent wszystkich producentów, którzy nie dostarczyli żadnego towaru do magazynu,
(h) wstaw do tabeli Producent, krotki z id producenta, którzy dostarczyli do magazynu towary, lecz nie ma ich w tabeli Producent (pozostałe atrybuty z wartościami null).
6. Napisz program PL/SQL, który dla powyższej bazy danych podniesie o 20%
cenę każdego towaru, producenta Luksusowy i obniży o 5% cenę każdego towaru producenta Tani
7. Dane są tabele Wartosci(liczba), Parzyste(liczba) i Nieparzyste(liczba).
(a) Napisz wyzwalacz, który przy wstawieniu lub modyfikacii tabeli Wartosci sprawdza, czy wstawiana liczba występuje w tabeli Parzyste (jeśli jest parzysta) lub Nieparzyste (w przeciwnym przypadku) i jeśli nie to ją wstawia w odpowiednią tabelę.
(b) Napisz wyzwalacz, dla tabeli Parzyste, który przy usuwaniu wartości z tej tabeli, usunie odpawiadające im wartości z tabeli Wartosci.
8. Rozważmy relacje R(A,B) oraz S(B,C).
(a) 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) Zoptymalizuj powyższe zapytanie, wiedząc, że B jest kluczem w S.
2
Rozwiązania
1. Przykładowy diagam ER. Oczywiście nie istnieje jedno rozwiązanie, które poprawnie odwzorowuje opis bazy danych. Np. mecze można reprezentować albo jako encję albo jako relację z atrybutami.
3
nazwa druzyny
nazwisko
imie
Miasta
w
Druzyny
trenuje
Trenerzy
1
lezy w
gospodarze
goscie
gra w
Zawodnicy
Regiony
Mecze
nazwisko
imie
nazwa regionu
data
wynik
2. W tym zadaniu podstawą trudnością, obok świadomości jak zachowują się operacje z algebry relacji, była znajomość zachowania się wartości null w porównaniach. Naturalne złączenie będzie zawierało dwie krotki (3 , 3 , 3) oraz (3 , 3 , 7). W drugim punkcie otrzymamy relację pustą a w trzecim relację o krotkach (4 , 4) oraz (1 , null).
3.
4.
5.
6.
7. Jeden z możliwych sposobów:
4
o r r e p l a c e
t r i g g e r
a k t u a l i z a c j a _ w a r t o s c i
a f t e r
i n s e r t
o r u p d a t e on e g z a m _ w a r t o s c i f o r e a c h row
d e c l a r e
i number ;
b e g i n
i f mod ( : new . l i c z b a , 2 ) = 0 t h e n s e l e c t
l i c z b a
i n t o
i
f r o m e g z a m _ p a r z y s t e p a r z
w h e r e p a r z . l i c z b a = : new . l i c z b a ; e l s e
s e l e c t
l i c z b a
i n t o
i
f r o m e g z a m _ n i e p a r z y s t e
n i e p a r z
w h e r e n i e p a r z . l i c z b a = : new . l i c z b a ; end i f ;
e x c e p t i o n
when n o _ d a t a _ f o u n d t h e n
i f mod ( : new . l i c z b a , 2 ) = 0 t h e n i n s e r t
i n t o
e g z a m _ p a r z y s t e v a l u e s ( : new . l i c z b a ) ; e l s e
i n s e r t
i n t o
e g z a m _ n i e p a r z y s t e v a l u e s ( : new . l i c z b a ) ; end i f ;
end ;
(b) c r e a t e
o r r e p l a c e
t r i g g e r
p a r z y s t e _ u s u w a n i e
a f t e r
d e l e t e on e g z a m _ p a r z y s t e
f o r e a c h row
b e g i n
d e l e t e f r o m e g z a m _ w a r t o s c i w h e r e l i c z b a = : o l d . l i c z b a ; end ;
8.
(a)
(b)
5