Zestaw zadań laboratoryjnych  

7.01.2013 zakończenie zadań dot. dzielenia relacyjnego 

Nr 6 

Dzielenie relacyjne, SQL, kwantyfikator ogólny 

Baza CJDate 

1)

 

Uruchomić przykłady podane na wykładzie. 

2)

 

Zapytania SQL: znaleźć projekty (J) które mają dostawy wszystkich części z Londynu 

3)

 

Znaleźć pary dostawca projekt (S-J) takie, że dostawca dostarcza wszystkie części 

wykorzystywane przez projekt 

4)

 

Pary dostawca projekt takie, że projekt wykorzystuje wszystkie części dostarczane przez 

danego dostawcę (por. Chris Date uwagi bibliograficzne 6.4 w polskim tłumaczeniu lub 7.4  

8th Edition w angielskojęzycznej) 

5)

 

Ułożyć zapytania z p. 3,4 w obydwu wersjach tj. podwójna negacja Exists oraz sprawdzanie 

liczności zbioru  

6)

 

W zadaniach 7 -11 można posłużyć się tylko jedną techniką – obydwa rozwiązania dla 

chętnych 

7)

 

Podaj numery (J#) projektów zaopatrywanych całkowicie przez dostawcę S1 (Date 6.41) 

8)

 

Podaj numery części (P#) dostarczanych do wszystkich odbiorców w Londynie (Date 6.42) 

9)

 

Podaj numery dostawców dostarczających tę samą część do wszystkich projektów (6.43) 

10)

 

Podaj numery dostawców dostarczających wszystkie te same części do wszystkich projektów 

11)

 

Podaj numery projektów zaopatrywanych przynajmniej w jedną część dostępną u dostawcy 

S1 (6.44) 

 

Baza Northwind 

1)

 

Znaleźć CustomerID takich klientów co kupują wszystko 

2)

 

Znaleźć ProductID produktów dostarczanych do wszystkich klientów 

3)

 

Zadania analogiczne do p.3 i 4 dla bazy CJDate znaleźć pary pracownik (Employee) klient 

(Customer) takie, że pracownik obsługuje sprzedaż wszystkich produktów kupowanych 

przez klienta 

4)

 

Takie pary pracownik klient że klient kupuje wszystko co dany pracownik sprzedaje 

5)

 

To samo dla kategorii 

6)

 

Rozważyć wykorzystanie CTE w celu uproszczenia, zastanowić się, jaka definicja CTE 

będzie najbardziej sensowna 

7)

 

Wybrane zadanie dot. bazy northwind rozwiązać za pomocą obydwu technik (negacji i 

zliczania) 

Zadanie dodatkowe czyli 7.01.2013 

Rozważyć następujący problem: (Date 6.48 tłumaczenie polskie) znaleźć pary dostawców 

dostarczających te same części. Można zapoznać się z rozwiązaniem  w książce. Wskazówka 

prowadzącego (znacznie prostsze rozwiązanie) . Skorzystać z rozwiązań zadań 3 oraz 4 dot. bazy 

CJDate. (UWAGA) instrukcja w zasadzie zawiera rozwiązanie – dlatego prowadzący zweryfikuje wyniki 

dzisiejszego laboratorium. 

a)

 

Sposób pierwszy skorzystać w faktu że 

A=B 

⇔

 (A

∪

B)- (A

∩

B) =0 

I zapisać ten fakt przy użyciu SQL-owych działań na zbiorach ) 

b)

 

Drugi sposób polega na tym żeby dwa razy skorzystać z podwójnej negacji w celu sprawdzenia 

czy A sprzedaje wszystko co B  (tj. czy A 

⊂

 B)oraz czy B sprzedaje wszystko co A , skorzystać z 

rozwiązania zadań 3 lub 4. 

c)

 

Można też rozważyć punkt b z wykorzystaniem zliczania 

Ułożyć podobny przykład dla bazy Northwind. 

 

a)

 

Pary klientów kupujących to samo  

b)

 

Pary sprzedawców, to samo 

c)

 

Oba warianty dla kategorii 

d)

 

Rozważyć różne zapisy z zadania dodatkowego dla bazy CJDate 

 

Wskazówka dot. zadania znaleźć takie pary dostawców , że SA dostarcza to wszystko co SB (por. 

zadanie 3 tam było S-J tutaj S-S) 

 

select

 sa

.

S#

,

sb

.

S# 

from

 S sa

,

 S sb 

where 

 

not

 

exists 

 

(

select

 

*

 

from

 SPJ spj1 

where

 spj1

.

S#

=

sb

.

S# 

and 

  

not

 

exists

 

(

select

 

*

 

from

 SPJ spj2 

where

 spj2

.

P#

=

spj1

.

P# 

and

 

spj2

.

S#

=

sa

.

S#

)) 

 

and

 sa

.

S#

<>

sb

.

S# 

 

 

e)

 

Dlaczego ostatni warunek (chodzi o 

and

 sa

.

S#

<>

sb

.

S#)

 ? Czy nie powinno być np.  

sa

.

S#

>

sb

.

S# (

nie powinno ale odpowiedź uzasadnić). 

f)

 

Rozważyć zapis ze wskazówki (po punkcie d) z wykorzystaniem notacji z wykładu 

„amerykańskiego” (Uniwersytet Wisconsin , Lester McCann) 

select

 sa

.

S#

,

sb

.

S# 

from

 S sa

,

 S sb 

where

 sa

.

S#

<>

sb

.

S# 

except 
select

 s1

,

s2 

from 

( 

  

select

 S

.

S# s1

,

SPJ

.

S# s2

,

SPJ

.

P# 

from

 S

,

SPJ 

  

except 

  

select

 SPJ

.

S#

,

S

.

S#

,

SPJ

.

P# 

from

 S

,

SPJ 

)

 t 

objaśnić powyższe zapytanie oraz wykorzystać w rozwiązaniu 

g)

 

Wykorzystać pozostałe 2 notacje z wykładu „amerykańskiego” (i.e.  zawieranie się zbiorów oraz 

zliczanie) 

h)

 

(

Dodatkowo

) zapisać rozwiązanie bezpośrednio korzystając z notacji z podwójnym except (c.f. 

rozwiązanie zadania 6.48 z książki. W książce opisano wyprowadzenie rozwiązania z rachunku 

relacyjnego (podobnie jak McCann) . Wskazówka (schemat do uzupełnienia) 

select

 sa

.

S#

,

sb

.

S# 

from

 S sa

,

 S sb 

where

 sa

.

S#

<>

sb

.

S# 

except 
select

 s1

,

s2 

from 

( 

  

-- s1,s2 oraz cz

ęść

 dostarczana przez jednego z nich 

  

except 

  

-- s1,s2 oraz cz

ęść

 dostarczana przez obydwu 

)

 t 

 

Uzasadnić powyższy schemat. Pokazać, że w istocie jest to pierwsza wskazówka (A

∪

B)- (A

∩

B) . 

i)

 

Rozważyć rozwiązanie w c# (np. na serwerze aplikacji z wykorzystaniem prostszych SQL) – 

powiedzmy „chwilowy zanik pomysłowości” –zamiast SQL pętle. Do zrobienia w lab. 

j)

 

Zadanie 8.11.48 z książki – pozostawione czytelnikowi (e.g. studentom) 

k)

 

Zadanie na przyszły tydzień będzie polegało na uzupełnieniu bazy danych w taki sposób by zbiór 

wynikowy był niepusty.