Bazy danych 4. Algebra relacji P. F. Góra th-if.uj.pl/zfs/gora
description
Transcript of Bazy danych 4. Algebra relacji P. F. Góra th-if.uj.pl/zfs/gora
Bazy danych
4. Algebra relacjiP. F. Góra
http://th-www.if.uj.edu.pl/zfs/gora/
semestr letni 2006/07
Bazy danych - wykład 4 2
Relacyjne języki zapytań(Relational Query Languages)
• Pozwalają na manipulacje danymi i pobieranie danych z bazy
• Mają mocne podstawy teoretyczne (algebra relacji!)
• Pozwalają na znaczną optymalizację• Nie są zwykłymi językami programowania,
przeznaczonymi do skomplikowanych obliczeń• Pozwalają użytkownikom zdefiniować co chcą
osiągnąć, nie zaś jak to trzeba obliczyć (Non-operational, declarative)
Choć w w praktyce na ogół zawierają pokaźny zestaw „niebazodanowych”
funkcji
Bazy danych - wykład 4 3
• Zrozumienie algebry relacji jest konieczne dla zrozumienia i prawidłowego posługiwania się SQL
• Zapytania odnoszą się do wystąpień (instancji) tabel. Wynikiem zapytań też są wystąpienia (instancje) tabel.– Schematy tabel wejściowych zapytania są
ustalone.– Schematy tabel wyjściowych zapytania są
określone przez definicje języka zapytań.• Zapytanie odnosi się do konkretnego
wystąpienia tabeli (lub tabel) o ustalonym schemacie.
Bazy danych - wykład 4 4
Przykładowe wsytąpienia tabel w
pewnej bazie
Sid Imię Rating Wiek
22 Daniel 7 45
31 Leon 8 55
58 Rysiek 10 35
Sid Bid Dzień
22 101 10.07.2007
58 103 11.08.2007
Rezerwacje R1
Sid Imię Rating Wiek
28 Jurek 9 35
31 Leon 8 55
44 Henio 5 35
58 Rysiek 10 35
Bid Nazwa Kolor
101 Szperacz Niebieska
103 Ścigacz czerwona
Łódki B1
Żeglarze S1
Żeglarze S2
Anglojęzyczna wersja tego przykładu jest dostępna w co najmniej dwu niezależnych
miejscach w sieci…
Bazy danych - wykład 4 5
Podstawowe operacje• Działania teoriomnogościowe:
• Suma mnogościowa (unia) • Przecięcie (iloczyn) zbiorów • Różnica zbiorów
• Iloczyn kartezjański • Rzutowanie • Selekcja • Przemianowanie • Złączenie• Dzielenie /
Nie jest to podstawowa
operacja, ale bardzo użyteczny „skrót”
Technicznie rzecz biorąc, to też nie jest podstawowa
operacja, ale występuje w praktyce tak często, że jest osobno implementowana
Wynikiem każdej operacji jest tabela (relacja), można więc tworzyć operacje złożone.
Algebra relacji jest domknięta!
Bazy danych - wykład 4 6
Operacje teoriomnogościowe
• Schematy obu tabel (relacji) wejściowych muszą mieć identyczne zbiory atrybutów
• Zanim zostanie obliczona suma mnogościowa, przecięcie lub różnica zbiorów, należy uporządkować atrybuty obu tabel tak, aby kolejnośc atrybutów była taka sama.
Bazy danych - wykład 4 7
Suma mnogościowa
R S — zbiór krotek, z których każda należy do R lub do S (lub do obu jednocześnie)
Sid Imię Rating Wiek
22 Daniel 7 45
28 Jurek 9 35
31 Leon 8 55
44 Henio 5 35
58 Rysiek 10 35
S1 S2
Ponieważ jest to zbiór, kolejność krotek nie
ma znaczenia.
Bazy danych - wykład 4 8
Przecięcie mnogościoweR S — zbiór krotek, z których każda należy jednocześnie do R i S
Sid Imię Rating Wiek
31 Leon 8 55
58 Rysiek 10 35
S1 S2
Różnica mnogościowaR - S — zbiór tych krotek z R, które nie należą do S
Sid Imię Rating Wiek
22 Daniel 7 45
S1 – S2
Bazy danych - wykład 4 9
Uwaga na wielozbiory!Tabele (relacje) w modelu relacyjny powinny być zbiorami (krotki nie mogą się powtarzać), ale niekiedy nie są — jeśli dopuszczamy powtórzenia krotek, czyli zbiory zastępujemy wielozbiorami, zmieniają się definicje operacji mnogościowych.
Suma R S — krotka w wyniku występuje tyle razy, ile występuje w R plus tyle razy, ile występuje w S. Uwaga: jeśli nawet R i S są zbiorami, R S może być wielozbiorem!
Iloczyn R S — krotka w wyniku występuje tyle razy, ile wynosi minimum jej wystąpień w R i S.
Różnica R–S — krotka w wyniku występuje tyle razy, ile występuje ona w R minus tyle razy, ile występuje ona w S, ale nie mniej niż 0 razy.
Bazy danych - wykład 4 10
Przykład:
R = {A,B,B}, S = {A,B,C,C}
R S = {A,A,B,B,B,C,C}
R S = {A,B}
R–S = {B}
Bazy danych - wykład 4 11
Uwaga na wielozbiory (cd)!Tabele (relacje) w modelu relacyjny powinny być
zbiorami (krotki nie mogą się powtarzać), ale niekiedy nie są.
W dobrze zaprojektowanej relacyjnej bazie danych tabele muszą być zbiorami. Kiedy mogą pojawiać się
wielozbiory?
Wielozbiory w dobrze zaprojektowanych relacyjnych bazach danych pojawiają się (i to dość często) jako tabele wynikowe pewnych zapytań. Tabele te mogą
być tabelami wejściowymi kolejnych zapytań…
Bazy danych - wykład 4 12
Selekcja C(R)
• Wybierz z tabeli R tylko te wiersze, które spełniają warunek wyboru C.– W warunku wyboru mogą pojawiać się
operatory logiczne!• Schemat wyjściowej relacji jest taki sam, jak
relacje wejściowej.• Jeśli R jest zbiorem, nie ma duplikatów.
– W praktyce duplikaty niekiedy się pojawiająW SQL rozróżnienie SELECT vs SELECT
DISTINCT.
Bazy danych - wykład 4 13
Przykład
Sid Imię Rating Wiek
28 Jurek 9 35
31 Leon 8 55
44 Henio 5 35
58 Rysiek 10 35
S2
Sid Imię Rating Wiek
28 Jurek 9 35
58 Rysiek 10 35
Rating > 8(S2)
Wybierz tylko te wiersze z tabeli S2, dla
których Rating > 8
Bazy danych - wykład 4 14
Rzutowanie A1,A2,…(R)
• Utwórz nową relację, która zawiera tylko te kolumny relacji R, które wymienione są na liście rzutowania A1,A2,…
• Schemat rejacji wyjściowej zawiera tylko kolumny występujące na liście rzutowania.
• W formalizmie matematycznym operator rzutowania eliminuje duplikaty– W praktyce (SQL) eliminowania
duplikatów trzeba zażądać explicite.
Bazy danych - wykład 4 15
Przykład
Sid Imię Rating Wiek
28 Jurek 9 35
31 Leon 8 55
44 Henio 5 35
58 Rysiek 10 35
S2
Imię Rating
Jurek 9
Leon 8
Henio 5
Rysiek 10
Imię,Rating(S2)
Wiek
35
55
Wiek(S2)Usunięto duplikaty!
Wybierz tylko kolumny Imię, Rating z tabeli S2
Bazy danych - wykład 4 16
Składanie operatorów
Relacja wyjściowa jednego zapytania może stać się relacją wejściową kolejnego zapytania — powstaje
operator złożony.
Sid Imię Rating Wiek
28 Jurek 9 35
31 Leon 8 55
44 Henio 5 35
58 Rysiek 10 35
S2
Imię Rating
Jurek 9
Rysiek 10
Imię,Rating (Rating > 8(S2))
Bazy danych - wykład 4 17
Przemianowanie S(A1,A2,…,An)(R)
W wyniku operacji S(A1,A2,…,An)(R) z relacji R otrzymujemy
relację S, mającą tyle samo atrybutów, co R. Nowymi nazwami atrybutów stają się A1, A2, …, An. Kolejność atrybutów zostaje
zachowana.
Jeśli chcemy tylko zmienić nazwę samej relacji, bez zmiany
nazw atrybutów, piszemy S(R) .
Bazy danych - wykład 4 18
Jak zapamiętać te oznaczenia?
— sigma — select — pi — project — rho — rename
Bazy danych - wykład 4 19
Iloczyn kartezjański• R S — każda krotka (wiersz) z R zostaje połączona z
każdą krotką (wierszem) S• Schemat wyniku ma po jednym atrybucie (kolmnie) na
każdy atrybut R i po jednym atrybucie na każdy atrybut S. Nazwy atrybutów są, o ile to możliwe, dziedziczone.
(Sid) Imię Rating Wiek (Sid) Bid Dzień 22 Daniel 7 45 22 101 10.07.2005 22 Daniel 7 45 58 103 11.08.2005 31 Leon 8 55 22 101 10.07.2005 31 Leon 8 55 58 103 11.08.2005 58 Rysiek 10 35 22 101 10.07.2005 58 Rysiek 10 35 58 103 11.08.2005
S1 R1
Konflikt między nazwami kolumn, który trzeba rozwiązać przez przemianowanie
Bazy danych - wykład 4 20
Złączenie warunkowe (złączenie theta)
• Z iloczynu kartezjańskiego R S wybieramy tylko te krotki, które spełniają warunek C.
• (Na ogół) mniej krotek niż w iloczynie kartezjańskim.• Schemat wyniku taki, jak schemat iloczynu kartezjańskiego.
)( SRcScR
(Sid) Imię Rating Wiek (Sid) Bid Dzień 22 Daniel 7 45 58 103 11.08.2005 31 Leon 8 55 58 103 11.08.2005
11
Sid.1Sid.1RS
RS
Bazy danych - wykład 4 21
Złączenie równościowe(equi-join)
• Złączenie warunkowe, w którym warunek C zawiera same równości.
• Schemat wyniku podobny do schematu iloczynu kartezjańskiego, ale zawiera tylko jedno wystąpienie każdej kolumny, dla której zażądano równości.
Sid Imię Rating Wiek Bid Dzień 22 Daniel 7 45 101 10.07.2005 58 Rysiek 10 35 103 11.08.2005
11SidRS
Złączenie naturalne — złączenie równościowe, dla którego zażądano równości we wszystkich
wspólnych kolumnach.
Bazy danych - wykład 4 22
Ważna uwaga
Złączenie jest definiowane jako podzbiór iloczynu kartezjańskiego tabel.
Nie oznacza to jednak, że złączenie jest w praktyce realizowane przez RDBMS w ten
sposób, iż najpierw tworzy się iloczyn kartezjański, a później wybiera z niego krotki
spełniające warunek złączenia.
Bazy danych - wykład 4 23
Dzielenie• Nie jest prymitywem, ale jest użyteczne w zapytaniach
typu Znajdź żeglarzy, którzy zarezerwowali wszystkie łódki.
• Niech A ma dwa pola, x, y, i niech B ma tylko jedno pole y:– A/B =
– czyli A/B zawiera wszystkie krotki x (żeglarzy) takie, że każdej krotce y (łódce) z B odpowiada krotka xy w A.
– Lub też: Jeśli zbiór wartości y (łódek), stowarzyszonych z wartością x (żeglarz) w A, zawiera wszystkie wartości y z B, x należy do A/B.
• Tak x, jak i y, mogą obejmować więcej niż jedno pole.
AyxByx ),(:
Bazy danych - wykład 4 24
Przykłady dzielenia
sno pnos1 p1s1 p2s1 p3s1 p4s2 p1s2 p2s3 p2s4 p2s4 p4
pnop2
pnop2p4
pnop1p2p4
snos1s2s3s4
snos1s4
snos1
A
B1B2
B3
A/B1 A/B2 A/B3
Bazy danych - wykład 4 25
Wyrażanie A/B poprzez podstawowe operatory
• Idea: W celu obliczenia A/B, znajdźmy wszystkie wartości x, które nie są „wykluczone” przez jakieś y B.
• x jest „wykluczone”, jeśli po dołączeniu y dostaniemy krotkę xyA
„Wykluczone” wartości x:
x x A B A(( ( ) ) )
A/B: x A( ) „wykluczone” wartości x
Bazy danych - wykład 4 26
Znajdź imiona żeglarzy, którzy zarezerwowali łódkę nr 103
• Rozw. 1:
• Rozw. 2:
• Rozw. 3:
)Żeglarze)Rezerwacje((103BidImię
))ŻeglarzeRezerwacje((103BidImię
2Imię
12Tmp
103Bid1Tmp
Tmp
ŻeglarzeTmp
Rezerwacje
Bazy danych - wykład 4 27
PodsumowanieModel relacyjny ma ściśłe, formalnie
zdefiniowane reguły zadawania zapytań, proste, ale potężne.
Algebra relacji jest bardzo użyteczna do reprezentowania planów wykonania zapytań.
Jedno zapytanie zazwyczaj można zrealizować na kilka sposobów. Optymalizator dobrego RDBMS powinien wybrać sposób najlepszy, ale niekiedy trzeba to zrobić ręcznie.