1
select
*
from
S
where
not exists(
select
P#
from
P
where
[WEIGHT]
=
17
and
not exists(
select
*
from
SPJ
where
SPJ
.
S#
=
S
.
S#
and
SPJ
.
P#
=
P
.
P#
)
)
select
*
from
S
where
not exists(
select
P#
from
P
where
[WEIGHT]
=
17
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
)
Komentarz do wykładu Lester MacCann (1)
A counting we will go
2
Komentarz do wykładu Lester MacCann (2)
A counting we will go
Zbędne złączenie z
tabelką P
3
Komentarz do wykładu Lester MacCann (3)
A counting we will go
select
*
from
S
where
not exists(
select
P#
from
P
where
[WEIGHT]
=
17
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
)
select
*
from
S
where
not exists(
select
P#
from
P
where
[WEIGHT]
=
17
except
select
P
.
P#
from
SPJ
,
P
where
SPJ
.
S#
=
S
.
S#
and
SPJ
.
P#
=
P
.
P#
and
P
.
[WEIGHT]
=
17
)
Teraz OK, jest to bo próba
ograniczenia rozmiaru 2go
operandu
4
Komentarz do wykładu Lester MacCann (4)
A counting we will go
Implementacja operatora except za pomocą left anti semi join
select
*
from
S
where
not exists(
select
*
from
P
left outer join
SPJ
on
SPJ
.
P#
=
P
.
P#
and
SPJ
.
S#
=
S
.
S#
where
SPJ
.
P#
is null and
[WEIGHT]
=
17
)
Dzielenie relacyjne z jednym zagnieżdżonym selektem! Duży postęp.
5
Komentarz do wykładu Lester MacCann (4a)
A counting we will go
W zwolnionym tempie:
select
*
from
P
left outer join
SPJ
on
SPJ
.
P#
=
P
.
P#
and
SPJ
.
S#
=
'S5'
where
P
.
[WEIGHT]
=
17
P#
PNAME
COLOR
WEIGHT
CITY
S#
P#
J#
QTY
P2
Bold
Green
17.00
Paris
S5
P2
J2
200
P2
Bold
Green
17.00
Paris
S5
P2
J4
100
P3
Screw
Blue
17.00
Rome
S5
P3
J4
200
select
*
from
P
left outer join
SPJ
on
SPJ
.
P#
=
P
.
P#
and
SPJ
.
S#
=
'S1'
where
P
.
[WEIGHT]
=
17
P#
PNAME
COLOR
WEIGHT
CITY
S#
P#
J#
QTY
P2
Bold
Green
17.00
Paris
NULL
NULL
NULL
NULL
P3
Screw
Blue
17.00
Rome
NULL
NULL
NULL
NULL
Jeżeli S należy do reszty z dzielenia to pojawia się NULL.
6
Komentarz do wykładu Lester MacCann (4b)
A counting we will go
select
*
from
Customers
where
not exists(
select
t2
.
ProductID
from
CEP t1
,
CEP t2
where
t1
.
CustomerID
=
Customers
.
CustomerID
and
t1
.
EmployeeID
=
t2
.
EmployeeID
and
not exists (
select
*
from
CEP t3
where
t3
.
CustomerID
=
Customers
.
CustomerID
and
t3
.
ProductID
=
t2
.
ProductID
and
t3
.
EmployeeID
=
t1
.
EmployeeID
)
)
Pierwsze rozwiązanie miało 6 selektów. (klienci którzy u obsługujących ich
pracowników kupują wszystko co ci pracownicy sprzedają)
Dotychczas: widok CEP oraz zastąpienie exists operatorem złączenia.
7
Komentarz do wykładu Lester MacCann (4c)
A counting we will go
select
*
from
Customers
where
not exists(
select
t2
.
ProductID
from
CEP t1
,
CEP t2
where
t1
.
CustomerID
=
Customers
.
CustomerID
and
t1
.
EmployeeID
=
t2
.
EmployeeID
except
select
t3
.
ProductID
from
CEP t3
where
t3
.
CustomerID
=
Customers
.
CustomerID
)
Krok 1szy zastąpienie podwójnej negacji sprawdzeniem zawierania się zbiorów.
i.e. A
⊆
B
⇔
|A-B|=0
W dalszym ciągu 2 klauzule select.
8
Komentarz do wykładu Lester MacCann (4d)
A counting we will go
select
*
from
Customers
where
not exists(
select
t2
.
ProductID
from
CEP t1
join
CEP t2
on
t1
.
CustomerID
=
Customers
.
CustomerID
and
t1
.
EmployeeID
=
t2
.
EmployeeID
left outer join
CEP t3
on
t3
.
ProductID
=
t2
.
ProductID
and
t3
.
CustomerID
=
Customers
.
CustomerID
where
t3
.
CustomerID
is NULL
)
Już tylko 2 klauzule select. Pamiętamy oryginalnie było 6.
Zadania:
•
wyeliminować ten ostatni operator exists za pomocą outer(anti)join
•
na serwerze aplikacji z wydajnym dostępem do tabel, zaimplementować
samodzielnie proceduralne znajdowanie przedmiotowego zbioru wynikowego.
9
select
*
from
Customers c
where
not exists
(
select
*
from
Employees e
where
exists
(
select
*
from
Orders o
where
o
.
EmployeeID
=
e
.
EmployeeID
and
o
.
CustomerID
=
c
.
CustomerID
)
and
exists (
select
*
from
Products
where
exists (
select
*
from
Orders o
,
[Order Details] od
where
o
.
OrderID
=
od
.
OrderID
and
o
.
EmployeeID
=
e
.
EmployeeID
and
od
.
ProductID
=
Products
.
ProductID
)
and
not exists(
select
*
from
Orders o
,
[Order Details] od
where
o
.
OrderID
=
od
.
OrderID
and
o
.
EmployeeID
=
e
.
EmployeeID
and
c
.
CustomerID
=
o
.
CustomerID
and
od
.
ProductID
=
Products
.
ProductID
)))
select
*
from
Customers
where
not exists(
select
t2
.
ProductID
from
CEP t1
join
CEP t2
on
t1
.
CustomerID
=
Customers
.
CustomerID
and
t1
.
EmployeeID
=
t2
.
EmployeeID
left outer join
CEP t3
on
t3
.
ProductID
=
t2
.
ProductID
and
t3
.
CustomerID
=
Customers
.
CustomerID
where
t3
.
CustomerID
is NULL)
Podwójna negacja, bez wyrażeń
tablicowych, dosłowne użycie
operatorów exists zamiast złączeń.
Tłumaczenie z „języka naturalnego”
Operator except w języku SQL – różnica zbiorów wierszy/ wyrażeń tablicowych
A – B
≡
select * from A
except
select * from B
Równoważna konstrukcja z użyciem exists:
select * from A
where not exists(
select * from B
where
A.a1=B.a1
…
A.an=B.an
)
10
Operator except w języku SQL – różnica zbiorów wierszy/ wyrażeń tablicowych
select OrderID from Orders
except
select EmployeeID from Employees
Bez sensu logicznie ale poprawne składniowo/operacyjnie:
Wymagania SQL dla argumentów operacji na zbiorach:
•
Równa liczba argumentów w wyrażeniach tablicowych
•
Wyrażenia „join compatible” tj. dające się porównać operatorem =
Wniosek: zawsze istnieje równoważ na konstrukcja z exists
exists jest starsze („bardziej standardowe”) niż except
na egzaminie prowadzący może zażądać rozwiązania
bez określonych konstrukcji
11
Dzielenie relacyjne w Wisconsin (1)
α÷β ≡ π
A-B
(
α
) –
π
A-B
(
π
A-B
(
α
)
× β
-
α
)
select A-B from
α
except
select A-B from
( select
α
.[A-B],
β
.B from
α
,
β
except
select * from
α
)
To samo z użyciem notacji SQL i wykorzystaniem operatora except:
Problemy: użyto except, gorsza sprawa – użyto from listen subquery
12
select A-B from
α
except
select A-B from
( select
α
.[A-B],
β
.B from
α
,
β
except
select * from
α
)
Dzielenie relacyjne w Wisconsin (2)
Transformacja automatyczna do exists:
select A-B from
α
where not exists
(
select * from
( select
α
.[A-B],
β
.B from
α
,
β
except
select * from
α
) t
where
t.[A-B]=
α
.[A-B]
)
13
select A-B from
α
where not exists
(
select * from
( select
α
.[A-B],
β
.B from
α
,
β
except
select * from
α
) t
where
t.[A-B]=
α
.[A-B]
)
Dzielenie relacyjne w Wisconsin (3)
select A-B from
α
where not exists
(
select * from
( select
α
1.[A-B],
β
.B from
α
1,
β
not exists
(
select * from
α α
2
where
α
2.[A-B]=
α
1.[A-B]
and
α
2.B =
β
.B
)
) t
where
t.[A-B]=
α
.[A-B]
)
14
select A-B from
α
where not exists
(
select * from
( select
α
1.[A-B],
β
.B from
α α
1,
β
where
not exists
(
select * from
α α
2
where
α
2.[A-B]=
α
1.[A-B]
and
α
2.B =
β
.B )
) t
where
t.[A-B]=
α
.[A-B]
)
Dzielenie relacyjne w Wisconsin (3)
select A-B from
α
where not exists
(
select
α
1.[A-B],
β
.B from
α α
1,
β
where
not exists
(
select * from
α α
2
where
α
2.[A-B]=
α
1.[A-B]
and
α
2.B =
β
.B
) and
α
1.[A-B]=
α
.[A-B]
)
Tzw. migracja predykatu (przeniesienie warunku do wyrażenia w klauzuli from)
15
Migracja predykatu (było na 1szym wykładzie)
select * from (select * from t) where
ϕ
(x)
Można zapisać jako:
select * from t where
ϕ
(x)
W ogólności
select * from (select * from t where
ϕ
1
(x)) where
ϕ
2
(x)
Jest równoważne
select * from t where
ϕ
1
(x) and
ϕ
2
(x)
W efekcie pokazaliśmy, że zapis y except i not exists są równoważne
16
17
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
from
P
select
COUNT
(*)
from
J
where
CITY
=
‘London'
Omówienie zdania 8 z zestawu lab6
Dwa komponenty rozwiązania
18
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
,
(
select
COUNT
(*)
from
J
where
CITY
=
'london'
)
[london jobs]
from
P
Omówienie zdania 8 z zestawu lab6
Dwa komponenty rozwiązania połączone w jedno query
19
p#
London Jobs
consuming p#
london jobs
P1
0
2
P2
0
2
P3
2
2
P4
0
2
P5
2
2
P6
1
2
Omówienie zdania 8 z zestawu lab6
Jak formatować sprawozdania?
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
,
(
select
COUNT
(*)
from
J
where
CITY
=
'london'
)
[london jobs]
from
P
20
select
p#
from
(
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
,
(
select
COUNT
(*)
from
J
where
CITY
=
'london‘
)
[london jobs]
from
P
)
t
where
t
.
[london jobs]
=
t
.
[London Jobs consuming p#]
Omówienie zdania 8 z zestawu lab6
Łopatologicznie – from listen subquery i wybór wierszy
21
Omówienie zdania 8 z zestawu lab6
Łopatologicznie – from listen subquery i wybór wierszy (cd)
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
,
(
select
COUNT
(*)
from
J
where
CITY
=
'london'
)
[london jobs]
from
P
where
[London Jobs consuming p#]
=
[london jobs]
Nie wolno tak, mimo że logiczne:
22
Omówienie zdania 8 z zestawu lab6
Łopatologicznie – from listen subquery i wybór wierszy (cd)
pola z klauzuli select
nie wolno
23
Omówienie zdania 8 z zestawu lab6
Łopatologicznie – from listen subquery i wybór wierszy (cd)
select
p#
from
P
where
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
) =
(
select
COUNT
(*)
from
J
where
CITY
=
'London'
)
select
p#
,
(
select
count
(
distinct
J
.
J#
)
from
SPJ
,
J
where
SPJ
.
P#
=
P
.
P#
and
SPJ
.
J#
=
J
.
J#
and
J
.
CITY
=
'London'
)
[London Jobs consuming p#]
,
(
select
COUNT
(*)
from
J
where
CITY
=
'london'
)
[london jobs]
from
P
where
[London Jobs consuming p#]
=
[london jobs]
Nie wolno:
Tak jest OK:
24
Podsumowanie tej części
Dzielenie relacyjne
Możliwe 2 rodzaje zadań:
•
względnie proste wtedy określona zostanie technika rozwiązania, ew. zostaną
nałożone pewne ograniczenia (e.g. nie używać except czy intersect)
•
bardziej złożone dzielenie relacyjne (lub zadania dające się sprowadzić do
dzielenia); wtedy swoboda w używaniu konstrukcji SQL
•
wykorzystanie CTE lub widoków w zadaniach dot. bazy northwind
•
wykorzystanie CTE lub widoków w zadaniach dot. bazy northwind i przekształcenie
wyniku do postaci korzystającej tylko tabelek bazowych
Dzielenie relacyjne - dokończenie
Dotychczas:
•
wykład wstępny poświecony SQL, proste przykłady
•
omówienie notacji opartych na podwójnej negacji i zliczaniu zbiorów wynikowych
•
Uogólnione dzielenie relacyjne (Todd, Date) – zadanie polegające na pokazaniu, że
dzielenie relacyjne jest szczególnym przypadkiem definicji uogólnionych
•
wykład dotyczący nauczania dzielenia relacyjnego Lester McCann: rachunek
relacyjny, operator różnicy zbiorów (except), podwójna negacja zliczanie i zwieranie
się zbiorów
Dalej:
•
Omówienie zadania 3 z zestawu laboratoryjnego 6
•
W sumie 6 różnych zapisów
•
Dokładne dzielenie relacyjne
•
Nowe zagadnienie - dokładne dzielenie relacyjne w wersji (Todd)
25
-- pary s,j: s# dostarcza wszystko co j# u
Ŝ
ywa
-- ale niekoniecznie do j#
-- por. uogólnione dzielenie relacyjne w wersji Todd'a
select
J#
,
S#
from
J
,
S
where
not exists
(
select
*
from
SPJ
where
SPJ
.
J#
=
J
.
J#
and
not exists
(
select
*
from
SPJ SPJ1
where
SPJ1
.
P#
=
SPJ
.
P#
and
SPJ1
.
S#
=
S
.
S#
)
)
Podwójna negacja – kwantyfikator ogólny zastąpiony zanegowanym exists
26
27
-- pozostałe notacje (w sumie 3 z McCann + 2 warianty)
--z rachunku relacyjnego por. Lester McCann
-- uwaga: mo
Ŝ
e by
ć
inna kolejno
ść
wierszy w zbiorze wynikowym!
select
J#
,
S#
from
S
,
J
except
select
J#
,
S#
from
(
select
S
.
S#
,
P#
,
J#
from
SPJ
,
S
except
select
SPJ1
.
S#
,
SPJ2
.
P#
,
SPJ2
.
J#
from
SPJ SPJ1
,
SPJ SPJ2
where
SPJ1
.
P#
=
SPJ2
.
P#
)
t
Rachunek relacyjny i użycie operatora except, dla dzielenia uogólnionego
Dostawcy dostarczają
Wszystkie wykorzystywane części
28
Zliczanie – 1sza postać
--zliczanie zbiorów wynikowych z grupowaniem
--notacja wg Lester McCann
select
J
.
J#
,
S
.
S#
from
J
,
S
,
SPJ
where
SPJ
.
J#
=
J
.
J#
group by
J
.
J#
,
S
.
S#
having
COUNT
(
distinct
P#
)=
(
select
COUNT
(
distinct
SPJ1
.
P#
)
from
SPJ SPJ1
,
SPJ SPJ2
where
SPJ1
.
P#
=
SPJ2
.
P#
and
SPJ1
.
S#
=
S
.
S#
and
SPJ2
.
J#
=
J
.
J#
)
29
--jeszcze raz wg McCann ale teraz odwrócenie zapyta
ń
:
--tj. głównego i podzapytania
select
SPJ1
.
J#
,
SPJ2
.
S#
from
SPJ SPJ1
,
SPJ SPJ2
where
SPJ1
.
P#
=
SPJ2
.
P#
group by
SPJ1
.
J#
,
SPJ2
.
S#
having
COUNT
(
distinct
SPJ1
.
P#
) =
(
select
COUNT
(
distinct
P#
)
from
SPJ
where
SPJ
.
J#
=
SPJ1
.
J#
)
Zliczanie – cd.
30
--zliczanie zbior
ó
w wynikowych z grupowaniem
--notacja podana na moim wykładzie
--przy pewnych zało
Ŝ
eniach (jakich?)
--równowa
Ŝ
no
ść
skorelowanych zapyta
ń
i grupowania
select
J#
,
S#
from
J
,
S
where
(
select
COUNT
(
distinct
P#
)
from
SPJ
where
SPJ
.
J#
=
J
.
J#
)=
(
select
COUNT
(
distinct
SPJ1
.
P#
)
from
SPJ SPJ1
,
SPJ SPJ2
where
SPJ1
.
P#
=
SPJ2
.
P#
and
SPJ1
.
S#
=
S
.
S#
and
SPJ2
.
J#
=
J
.
J#
)
Zliczanie – wersja z wykładu
31
--zawieranie si
ę
zbiorów (McCann)
--chyba najprostsza notacja
-- po wyrzuceniu except dostaje si
ę
rozwi
ą
zanie
-- z podwójn
ą
negacj
ą
--(por. moje uzupełnienie do wykładu McCann'a)
select
J#
,
S#
from
J
,
S
where
not exists
(
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
)
Zawieranie się zbiorów – tj. mniejszy odjąć większy równy pustemu
32
Zadanie 6.3a – do następnego sprawozdania
1) Zapytanie zmodyfikowane, pary s# i j# takie że s# dostarcza do j# wszystko co j#
używa
2) Dokładne dzielenie relacyjne : s# dostarcza dokładnie to co J# używa ( w omawianych
przykładach mógł dostarczać więcej części.
3) Jako wariant zadania 2, dzielenie dokładne z zastrzeżeniem z p.1, tj. dostarcza
dokładnie to co j# używa i dostarcza to do j# (choć może też do innych odbiorców)
4) Ponieważ jest aż 6 notacji z poprzednich slajdów – można podać aż 3*6=18 zapytań
SQL tylko dot. wariantów jednego zadania:)
33
select
J#
,
S#
from
J
,
S
where
not exists
(
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
) and
not exists
(
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
except
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
)
Zadanie 6.3a – dokładne dzielenie, notacja z zawieraniem
Kiedy dwa zbiory są równe?
A
⊆
B czyli A-B=
∅
A
⊇
B czyli B-A=
∅
Skoro A
⊆
B oraz A
⊇
B więc A=B
A
B
34
Zadanie 6.3a c.d. – s# ma dostarczać do j# wszystko co j# używa
select
J#
,
S#
from
J
,
S
where
not exists
(
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
and
SPJ
.
J#
=
J
.
J#
)
Zbiór B nieco pomniejszony
bo dodatkowy warunek.
35
Zadanie 6.3a c.d. – s# ma dostarczać do j# tylko to wszystko co j# używa
Można to zrobić w prosty
„mechaniczny” sposób:
•
za punkt wyjścia przyjąć
poprzednie rozwiązanie
•
skorzystać z różnicy z
zamienionymi argumentami
select
J#
,
S#
from
J
,
S
where
not exists
(
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
except
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
and
SPJ
.
J#
=
J
.
J#
)
and
not exists
(
select
P#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
and
SPJ
.
J#
=
J
.
J#
except
select
P#
from
SPJ
where
SPJ
.
J#
=
J
.
J#
)
36
Zadanie 6.10 dostawcy dostarczający wszystkie części do wszystkich odbiorców
/*
zadanie 10
rozwi
ą
zanie wg. McCann
wyprowadzenie z rachunku relacyjnego
*/
select
S#
from
S
except
select
S#
from
(
--pewien zbiór w którym je
Ŝ
eli S# dostarcza P# do to wszystkich
select
S#
,
P#
,
J
.
J#
from
SPJ
,
J
except
--zobaczmy co z niego zostanie
select
S#
,
P#
,
J#
from
SPJ
)
t
37
Zadanie 6.10 cd.
/*
zadanie 10 rozwi
ą
zanie z pustym podzbiorem
*/
select
S#
from
S
where
not exists
(
select
S#
,
P#
,
J
.
J#
from
SPJ
,
J
where
SPJ
.
S#
=
S
.
S#
except
select
S#
,
P#
,
J#
from
SPJ
where
SPJ
.
S#
=
S
.
S#
)
38
Zadanie 6.10 – zbiór wynikowy
•
Tylko s2 dostarcza p3 do wszystkich odbiorców (a dostarcza jeszcze inne części)
•
Zbiór wynikowy jest pusty
•
Zadanie: dodać do spj wiersze tak, żeby chociaż jeden dostawca spełniał warunek.
•
Ponieważ ma być zachowana zawartość posłużyć się transakcją:
begin transaction
-- ró
Ŝ
ne polecenia insert delete update
rollback transaction
-- przywrócenie stanu sprzed begin transaction
39
begin transaction
insert into
SPJ
(
S#
,
P#
,
J#
,
QTY
)
select
S#
,
P#
,
J
.
J#
,
100
from
SPJ
,
J
where
S#
=
'S1'
and
not exists
(
select
*
from
SPJ SPJ1
where
SPJ1
.
S#
=
SPJ
.
S#
and
SPJ1
.
P#
=
SPJ
.
P#
and
SPJ1
.
J#
=
J
.
J#
)
-- wykona
ć
select z zadania 10
rollback transaction
-- jeszcze raz ten select
Zadanie 6.10 – zbiór wynikowy, cd.
40
select
S#
from
S
where
exists
(
select
*
from
P
where
not exists
(
select
*
from
J
where
not exists
(
select
*
from
SPJ SPJ1
where
SPJ1
.
J#
=
J
.
J#
and
SPJ1
.
P#
=
P
.
P#
and
SPJ1
.
S#
=
S
.
S#
)
)
)
Zadanie 6.9 – w książce 6.43 dostawcy dostarczający jakąś część do wszystkich
/*zadanie 9
rozwi
ą
zanie na negacjach
exists dostawcy dla których
istnieje cz
ęść
taka
Ŝ
e nie istnieje projekt
do której
ten dostawca jej
nie dostarcza
por. Date 6.43
*/
41
/*
rozwi
ą
zanie w ksi
ąŜ
ce jest długie
teraz skrót,
korzystamy z techniki zast
ę
powania exists
*/
select distinct
S#
from
SPJ
where
not exists
(
select
*
from
J
where
not exists
(
select
*
from
SPJ SPJ1
where
SPJ1
.
P#
=
SPJ
.
P#
and
SPJ1
.
J#
=
J
.
J#
and
SPJ1
.
S#
=
SPJ
.
S#
)
)
Zadanie 6.9 cd.
42
Lab 6 Zadanie Northwind 3 (1)
select
EmployeeID
,
CustomerId
from
Customers
,
Employees e
where
not exists
(
select
*
from
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
where
o
.
CustomerID
=
Customers
.
CustomerID
and
not exists
(
select
*
from
[Order Details] od1
join
Orders o1
on
o1
.
OrderID
=
od1
.
OrderID
where
od1
.
ProductID
=
od
.
ProductID
and
e
.
EmployeeID
=
o1
.
EmployeeID
)
)
Podwójna negacja:
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.
Sens: zarząd firmy może szukać desygnowanych przedstawicieli dla relacji z określonymi klientami
43
Lab 6 Northwind 3 (2)
Rachunek relacyjny :
select
EmployeeID
,
CustomerId
from
Customers
,
Employees
except
select
EmployeeID
,
CustomerId
from
(
select
e
.
EmployeeId
,
ProductID
,
CustomerID
from
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
cross join
Employees e
except
select
o1
.
EmployeeId
,
od
.
ProductID
,
o
.
CustomerID
from
[Order Details] od1
join
Orders o1
on
o1
.
OrderID
=
od1
.
OrderID
,
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
where
od1
.
ProductID
=
od
.
ProductID
)
t
44
•
Koniec dzielenia relacyjnego (jeszcze kilka plansz)
•
Pojawi się dopiero na egzaminie.
•
Pozostaje tylko do ułożenie pozostałych 3*5=15 notacji rozwiązań. (2 z tych
15 jeszcze będą)
•
Oczywiście można rozwiązać tylko wybrane przypadki. Przy dobrym
zrozumieniu rozwiązania podstawowego i pokazanych technik, czynność
mechaniczna.
•
Jeżeli wykona się ćwiczenie w laboratorium: pomoc prowadzącego + dot.
stopień
Koniec
45
Lab 6 Northwind 3 (przedostatni raz)
select
EmployeeID
,
CustomerId
from
Customers
,
Employees e
where
not exists
(
select
*
from
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
where
o
.
CustomerID
=
Customers
.
CustomerID
and
not exists
(
select
*
from
[Order Details] od1
join
Orders o1
on
o1
.
OrderID
=
od1
.
OrderID
where
od1
.
ProductID
=
od
.
ProductID
and
e
.
EmployeeID
=
o1
.
EmployeeID
and
Customers.CustomerId= o1.CustomerId
)
)
Modyfikacja zadania 6 Northwind 3 – ma dostarczać do zadanego klienta
Dodatkowy warunek
46
Lab 6 Northwind 3 (ostatni raz)
To samo za pomocą except – rachunek relacyjny
select
EmployeeID
,
CustomerId
from
Customers
,
Employees
except
select
EmployeeID
,
CustomerId
from
(
select
e
.
EmployeeId
,
ProductID
,
CustomerID
from
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
cross join
Employees e
except
select
o1
.
EmployeeId
,
od
.
ProductID
,
o
.
CustomerID
from
[Order Details] od1
join
Orders o1
on
o1
.
OrderID
=
od1
.
OrderID
,
[Order Details] od
join
Orders o
on
o
.
OrderID
=
od
.
OrderID
where
od1
.
ProductID
=
od
.
ProductID
)
t
(tutaj sprzedaje wszystko co Customer kupuje ale niekoniecznie jemu)
select
EmployeeID
,
CustomerID
from
Employees
,
Customers
except
select
EmployeeID
,
CustomerID
from
(
select
e
.
EmployeeID
,
CustomerID
,
ProductID
from
Orders o
join
[Order Details] od
on
o
.
OrderID
=
od
.
OrderID
cross join
Employees e
except
select
EmployeeID
,
CustomerID
,
ProductID
from
Orders o
join
[Order Details] od
on
o
.
OrderID
=
od
.
OrderID
)
t
(tutaj sprzedaje wszystko co Customer kupuje ale wła
ś
nie jemu)
Ciekawostka: dodatkowy warunek w podwójnej negacji a tutaj krótsze zapytanie
47
Temat pracy inżynierskiej (1)
•
Oprogramowanie edukacyjne.
•
Weryfikacja poprawności
zapytania SQL.
•
Sprawdzenie poprawności
poprzez porównanie zbiorów
wynikowych, warunek
konieczny, niewystarczający
•
Zapytania parametryzowane
Ten sam zbiór wynikowy
Parametryzowanie: zapytanie może być poprawne tylko dla określonych wartości parametrów
48
Temat pracy inżynierskiej (2)
Parametryzowanie: znaleźć dostawców którzy nie dostarczają P2
select
S#
from
S
where
S#
<>(
select distinct
S#
from
SPJ
where
P#
=
'P2'
)
Zadziała dla 1 elementowego zbioru dostawców, dlatego zadanie powinno brzmieć:
znaleźć dostawców którzy nie dostarczają określonej parametrem części
Trudniejsze:
Pytanie SQL jednak zawierają parametry jako stałe:
Program wykrywający te parametry i testujący dla różnych wartości.
Ogólne zasady dla dzielenia relacyjnego 1a
A/B= C
⇔
C= max {Z |Z
⊗
B
⊂
A}
Tj. r
∈
Z
⊗
C ⇒ r
∈
A
)
,
(
)
,
(
,
,
x
r
y
x
r
y
A
y
C
x
B
r
A
y
C
x
B
r
=
∃
¬
∃
¬
⇔
=
∃
∀
∈
∈
∈
∈
∈
∈
Podwójna negacja exists
Definicja, dla relacji A B wynikiem dzielenia jest taka relacja C że:
Reszta z dzielenia:
R=A-(A/B)
⊗
B ,
co jeśli R=
∅
?(zależność wielowartościowa)
49
50
Ogólne zasady dla dzielenia relacyjnego 1b
)
,
(
)
,
(
,
,
x
r
y
x
r
y
A
y
C
x
B
r
A
y
C
x
B
r
=
∃
¬
∃
¬
⇔
=
∃
∀
∈
∈
∈
∈
∈
∈
Podwójna negacja exists
A/B= C
⇔
C= max {Z |Z
⊗
B
⊂
A}
Analogia do dzielenia liczb:
A/B=C
⇔
C= max {Z |Z * B <A}
Mnożenie oraz zwykła relacja porządkująca zamiast iloczynu kartezjańskiego i relacji
zawierania
Ogólne zasady dla dzielenia relacyjnego 2
Zliczanie jako metoda sprawdzenia czy zbiory są równe.
Równa liczność jest warunkiem koniecznym ale niewystarczającym
Warunkiem wystarczającym jest ten sam lub silniejszy predykat (klauzula where)
A =B jeżeli
A={x|
ϕ
(x)},B={x|
φ
(x)} |A|=|B| oraz
ϕ
⇒
φ
. (na pewno jest |A|≤|B| )
Liczba wierszy spełniających predykat jest równa liczbie oczekiwanej.
Jeżeli podzbiór ma liczbę elementów równą nadzbiorowi to znaczy że jest mu równy.
51
Ogólne zasady dla dzielenia relacyjnego 3
Równość zbiorów
Przykład
Zadanie znaleźć pracowników którzy dostarczających te same produkty
Znaleźć pracowników dostarczających te same kategorie produktów.
Sprawdzanie równości zbiorów wynikowych:
1. A=B
⇔
(A
∪
B)- (A
∩
B) =0 (zapisać ten fakt przy użyciu SQL-owych działań na
zbiorach formułując odpowiednie zapytania zagnieżdżone)
2. 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 (B
⊂
A)
3. Wariant p2. korzystający ze zliczania zamiast z podwójnej negacji.
52
Ogólne zasady dla dzielenia relacyjnego 4
Uogólnienia dzielenia relacyjnego:
1) Definicja podstawowa E.F. Codd "A relational Model of large Shared Data Banks"
2) Uogólniona definicja Stephen Todd A{X,Y} i B{Y,Z} A/B zawiera takie wiersze X,Z że
{X,Y} występuje w A dla wszystkich {Y,Z} – prościej rozłożyć relację Y,Z na sumę
względem kolumny Z, wykonać dzielenie i z powrotem zsumować wynik. Gotowe
rozwiązanie zadań typu (kupują u niego wszystko co on sprzedaje)
3) Relacja uogólniona A divide B per AB , definicja (p. Date )
A.X where forall B exists AB {A.X=AB.X and B.Y=AB.Y}
4) Co to znaczy "uogólniona definicja"? ☺
4a) Pokazać że podstawowa definicja dzielenia relacyjnego jest szczególnym
przypadkiem definicji podanych w punktach 2 i 3.
53
54
Ogólne zasady dla dzielenia relacyjnego 4
chillout
2) Uogólniona definicja Stephen Todd A{X,Y} i B{Y,Z} A/B zawiera takie wiersze X,Z że {X,Y}
występuje w A dla wszystkich {Y,Z} – prościej rozłożyć relację Y,Z na sumę względem
kolumny Z, wykonać dzielenie i z powrotem zsumować wynik. Gotowe rozwiązanie
zadań typu (kupują u niego wszystko co on sprzedaje)
{X,Y} – X kupuje produkt Y // X=Customer Y=Product
{Y,Z} – Z sprzedaje Y
// Z=Employee
3) Relacja uogólniona A divide B per AB , definicja (p. Date )
A.X where forall B exists AB {A.X=AB.X and B.Y=AB.Y}
np. A=S B=P AB=SPJ
(AB
÷
B) A
55
Ogólne zasady dla dzielenia relacyjnego 4
Symbol Operatora złączenia
⊳⊲