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 miasta

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

(a) c r e a t e

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