Algebra relacyjna

Przykłady:

• Projektowanie systemów relacyjnych baz danych – R. M. Riordan

• Northwind.mdb

Instrukcja SELECT

SELECT <ListaPól>

FROM <ListaZestawówRekordów>

<RodzajŁączenia> JOIN <Warunek>

WHERE <KryteriaWyboru>

GROUP BY <ListaPólGrupujących>

HAVING <KryteriaWyboru>

ORDER BY <ListaPólPorządkujących>

Logika trójwartościowa

Logika trójwartościowa

Operatory relacyjne• Podstawowe operatory relacyjne:

– wyboru,

– rzutu,

– łączenia,

– dzielenia

• Pierwsze dwa wpływają na tylko jeden zestaw rekordów.

• Operator łączenia jest chyba najbardziej fundamentalny w modelu relacyjnym, gdyż definiuje sposób łączenia ze sobą dwóch zestawów rekordów.

• Ostatni operator, dzielenia, jest rzadko używany, ale stanowi czasem wygodną metodę ustalenia, które rekordy w jednym zestawie rekordów pasują do wszystkich rekordów drugiego zestawu.

• Wszystkie te operatory są implementowane za pomocą jakiejś formy instrukcji SQL-a SELECT.

• Można je łączyć w dowolny sposób, mając jedynie na uwadze systemowe ograniczenia dotyczące długości i złożoności instrukcji.

Operatory relacyjne

wybór

Operator wyboru zwraca tylko te rekordy, które spełniają podane kryteria. Jest on implementowany przy użyciu klauzuli WHERE instrukcji SELECT.

SELECT * FROM Pracownicy

WHERE Nazwisko = "Davolio";

Kryteria wyboru podane w klauzuli WHERE mogą być dowolnie skomplikowane. Wyrażenia logiczne mogą zawierać operatory AND i OR. Wyrażenie jest obliczane dla każdego rekordu zestawu i jeśli wynikiem jest wartość True, rekord jest dołączany do zestawu wynikowego. Jeśli wyrażenie zwraca dla danego rekordu wartość False lub Null, nie jest on dołączany.

Rzut zwraca tylko podzbiór pól oryginalnego zestawu rekordów. SQL realizuje tę prostą operację przy użyciu pozycji <ListaPól> w instrukcji SELECT, która powoduje włączenie do wynikowego zestawu rekordów tylko pól podanych na tej liście.

SELECT Nazwisko, Imię, TelefonWewnętrzny

FROM Pracownicy

ORDER BY Nazwisko, Imię;

Jedyne, co wykonuje klauzula ORDER BY, to sortowanie danych. W tym przypadku spis będzie posortowany alfabetycznie według pola Nazwisko, a w drugiej kolejności według pola Imię.

Operatory relacyjne

rzut

• Operacje łączenia są najbardziej typowymi operacjami relacyjnymi.

• Są one z pewnością najistotniejsze dla modelu - nie byłoby możliwe dekomponowanie danych na wiele relacji, gdyby nie można było ich odpowiednio połączyć ze sobą.

• Dokładnie to robi właśnie operator łączenia. Łączy on ze sobą zestawy rekordów na podstawie porównania jednego lub więcej wspólnych pól.

• Łączenie jest implementowane za pomocą klauzuli JOIN instrukcji SELECT.

• Istnieją różne kategorie łączenia w zależności od rodzaju stosowanego porównania pól i sposobu traktowania wyniku tego porównania.

Operatory relacyjne

łączenie

• Gdy łączenie jest dokonywane na bazie równości, mówimy o równołączeniu (ang. equi-join). W operacji równołączenia zwracane są tylko te rekordy, których podane pola mają równe wartości.

• Jest to typowy przypadek łączenia tabel uzyskanych w wyniku procesu normalizacji. IDZamówienia jest kluczem głównym tabeli Zamówienia i jednocześnie kluczem obcym tabeli Opisy zamówień.

• Aby ponownie połączyć te tabele (i w konsekwencji doprowadzić do denormalizacji), należy użyć następującej instrukcji:

Operatory relacyjne – łączenie

RÓWNOŁĄCZENIE

Operatory relacyjne – łączenie

RÓWNOŁĄCZENIE

SELECT Zamówienia.IDZamówienia, Zamówienia.IDKlienta,[Opisy Zamówień].IDProduktu

FROM Zamówienia

INNER JOIN [Opisy zamówień] ON Zamówienia.IDZamówienia = [Opisy zamówień].IDZamówienia WHERE (((Zamówienia.IDZamówienia)=10248));

• Szczególnym przypadkiem równołączenia jest łączenie naturalne. Aby operacja łączenia była łączeniem naturalnym, musi spełniać następujące warunki:

– Operatorem porównania musi być równość.

– W łączeniu muszą uczestniczyć wszystkie pola wspólne.

– Jeden i tylko jeden zbiór pól wspólnych musi być włączony do wynikowego zestawu rekordów.

• Zasadniczo nie ma niczego magicznego w łączeniach naturalnych. Nie działają one w jakiś szczególny sposób, a aparat bazy danych nie oferuje żadnej specjalnej ich obsługi. Jest to po prostu najbardziej typowa forma łączenia. Tak typowa, że aż otrzymała własną nazwę.

Operatory relacyjne – łączenie

ŁĄCZENIE NATURALNE

• Technicznie wszystkie łączenia są łączeniami typu theta.

• Zwyczajowo jednak, jeśli operatorem porównania jest operator równości, łączenie nazywane jest równołączeniem albo po prostu łączeniem.

• Łączenie oparte na jakimkolwiek innym operatorze porównania (o, >, >=, <, <=) jest łączeniem theta.

• Łączenia theta zdarzają się w praktyce bardzo rzadko, ale mogą być użyteczne przy rozwiązywaniu niektórych problemów.

• Problemy te dotyczą głównie szukania rekordów zawierających na przykład wartość większą od średniej lub sumy w całym zestawie lub rekordów o wartości należącej do jakiegoś przedziału.

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

Utworzono dwa rzuty, jeden zawierający średnią liczbę towarów sprzedanych w ramach każdej kategorii, a drugi zawierający całkowite liczby sprzedanych produktów.

ŚrednieZProduktówKategorii

ProduktyOgółem

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

Podana niżej instrukcja SELECT, oparta na operatorze porównania >, zwraca spis najlepiej sprzedających się produktów w każdej kategorii:

SELECT DISTINCTROW ŚrednieZProduktówKategorii.NazwaKategorii,

ProduktyOgółem.NazwaProduktu

FROM ŚrednieZProduktówKategorii

INNER JOIN ProduktyOgółem

ON ŚrednieZProduktówKategorii.IDKategorii = ProduktyOgółem.IDKategorii

AND ProduktyOgółem.SprzedażCałkowita > [ŚrednieZProduktówKategorii].[SprzedażŚrednia];

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

• Lub za pomocą klauzuli WHERE:

SELECT DISTINCTROW ŚrednieZProduktówKategorii.NazwaKategorii,

ProduktyOgółem.NazwaProduktu

FROM ŚrednieZProduktówKategorii

INNER JOIN ProduktyOgółem

ON ŚrednieZProduktówKategorii.IDKategorii = ProduktyOgółem.IDKategorii

WHERE (((ProduktyOgółem.SprzedażCałkowita)>[ŚrednieZProduktówKategorii].[SprzedażŚrednia])) ;

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

• Technicznie wszystkie łączenia, również równołączenia i łączenia naturalne, można wyrazić za pomocą operatora wyboru.

• W przypadku łączeń theta taka forma jest niemal zawsze najlepsza, gdyż aparaty baz danych lepiej optymalizują jej wykonanie.

Operatory relacyjne – łączenie

ŁĄCZENIE THETA

• Wszystkie omawiane dotąd łączenia były łączeniami wewnętrznymi, czyli łączeniami zwracającymi tylko te rekordy, w których warunek łączenia daje wartość True.

• To nie jest to samo, co zwrócenie rekordów, w których podane pola pasują do siebie, chociaż zwykle w ten właśnie sposób opisuje się łączenie wewnętrzne. „Pasują" sugeruje równość, a jak wiadomo, nie wszystkie łączenia oparte są na równości.

Operatory relacyjne – łączenie

ŁĄCZENIE ZEWNĘTRZNE

• Algebra relacyjna udostępnia jeszcze jeden rodzaj łączenia - łączenie zewnętrzne.

• Łączenie zewnętrzne zwraca wszystkie rekordy zwracane przez sprzężenie wewnętrzne plus wszystkie rekordy jednego lub obu pozostałych zestawów rekordów.

• Brakujące wartości („niedopasowane") są zastępowane wartością Null.

• Łączenia zewnętrzne są kwalifikowane jako lewe, prawe lub pełne, w zależności od tego, które dodatkowe rekordy mają być dołączone.

Operatory relacyjne – łączenie

ŁĄCZENIE ZEWNĘTRZNE

• obie kolejne instrukcje zwracają wszystkie rekordy X i tylko te rekordy Y, w których <warunek> ma wartość True:

SELECT * FROM X LEFT OUTER JOIN Y ON <warunek>

SELECT * FROM Y RIGHT OUTER JOIN X ON <warunek>

Operatory relacyjne – łączenie

ŁĄCZENIE ZEWNĘTRZNE

• Pełne łączenie zewnętrzne zwraca wszystkie rekordy obu zestawów, łącząc ze sobą tylko te rekordy, w których warunek jest spełniony (daje wartość True). SQL Server umożliwia pełne łączenia zewnętrzne za pomocą warunku FULL OUTER JOIN:

SELECT * FROM X FULL OUTER JOIN Y ON <warunek>

• Aparat bazy danych Jet nie umożliwia bezpośrednio pełnego sprzężenia zewnętrznego, ale można je zastąpić przez sumę lewego sprzężenia zewnętrznego i prawego sprzężenia zewnętrznego.

Operatory relacyjne – łączenie

ŁĄCZENIE ZEWNĘTRZNE

• Ostatnią operacją relacyjną jest dzielenie.

• Operator dzielenia relacyjnego (zwanego tak w celu odróżnienia go od dzielenia matematycznego) zwraca rekordy jednego zestawu, które mają wartości pasujące do wszystkich odpowiadających im wartości w drugim zestawie rekordów.

• Na przykład mając zestaw rekordów podający kategorie produktów kupowanych od każdego dostawcy, za pomocą dzielenia relacyjnego można uzyskać listę tych dostawców, którzy oferują produkty we wszystkich kategoriach.

Operatory relacyjnedzielenie

• Nie jest to wcale taka rzadka sytuacja, ale rozwiązanie nie jest proste, gdyż instrukcja SQL-a SELECT, nie pozwala wykonywać bezpośrednio dzielenia relacyjnego.

• Jest jednak kilka innych sposobów uzyskania tego samego rezultatu, jaki daje dzielenie relacyjne.

• Najprostszą metodą jest inne sformułowanie zapytania.

Operatory relacyjnedzielenie

• Zamiast

„listy dostawców, którzy oferują produkty we wszystkich kategoriach",

co jest trudne do przetworzenia, można uzyskać

„listę wszystkich dostawców, dla których liczba kategorii oferowanych produktów jest równa liczbie wszystkich kategorii produktów".

• Jest to przykład operacji rozszerzenia. Nie zawsze jest to możliwe i w takich sytuacjach można implementować dzielenie za pomocą skorelowanych kwerend.

Operatory relacyjnedzielenie

• Ogólnie suma relacyjna jest konkatenacją dwóch zestawów rekordów.

• Jest to mniej więcej relacyjna wersja dodawania. Dodanie zestawu rekordów A do zestawu rekordów B to w praktyce to samo, co dodanie wszystkich rekordów A do B.

• Przykład: potrzebujemy do korespondencji seryjnej listy wszystkich nazwisk i adresów zawartych w bazie danych. Oba zestawy rekordów Klienci i Pracownicy w bazie danych Northwind zawierają adresy i w związku z tym mogą być łatwo łączone za pomocą operacji sumowania.

Operatory mnogościowesuma

W tym przypadku można użyć instrukcji UNION w następującej postaci:

SELECT NazwaFirmy As Nazwa, Adres, Miasto, KodPocztowy

FROM Klienci

UNION SELECT [Imię] & " " & [Nazwisko] As Nazwa,

Adres, Miasto, KodPocztowy FROM Pracownicy ORDER BY Nazwa;

Operatory mnogościowesuma

Operatory mnogościowesuma

• Operator przecięcia zwraca rekordy wspólne dla dwóch zestawów rekordów.

• Jest to zasadniczo operacja „znajdź duplikaty" i w ten właśnie sposób jest najczęściej używana.

• Przecięcie implementuje się za pomocą łączeń zewnętrznych.

Operatory mnogościoweprzecięcie

Operatory mnogościoweprzecięcie

Przypuśćmy na przykład, że otrzymano z kilku różnych systemów listy klientów i trzeba znaleźć powtarzających się:

ZdublowaniKlienci1

ZdublowaniKlienci2

Instrukcja SELECT zwracająca zdublowane rekordy:

SELECT ZdublowaniKliencil.*

FROM ZdublowaniKliencil

LEFT JOIN ZdublowaniKlienci2

ON (ZdublowaniKliencil.IDKlienta = ZdublowaniKlienci2.IDKlienta)

AND (ZdublowaniKliencil.NazwaFirmy = ZdublowaniKlienci2.NazwaFirmy)

WHERE (((ZdublowaniKlienci2.IDKlienta) IS NOT NULL));

Operatory mnogościoweprzecięcie

Operatory mnogościoweprzecięcie

• O ile przecięcie dwóch zestawów rekordów używane jest do „znalezienia duplikatów", różnica dwóch zbiorów pozwala znaleźć „rekordy osierocone".

• Różnica relacyjna dwóch zestawów rekordów to rekordy należące do jednego zestawu, ale nie do drugiego.

Operatory mnogościoweróżnica

• Aby w poprzednim przykładzie znaleźć z pierwszego zestawu tylko te rekordy, które nie powtarzają się w drugim, należy wykonać następującą instrukcję:

SELECT ZdublowaniKliencil.*

FROM ZdublowaniKliencil

LEFT JOIN ZdublowaniKlienci2

ON (ZdublowaniKliencil.IDKlienta = ZdublowaniKlienci2.IDKlienta)

AND ZdublowaniKliencil.NazwaFirmy = ZdublowaniKlienci2.NazwaFirmy)

WHERE (ZdublowaniKlienci2.IDKlienta IS NULL);

Operatory mnogościoweróżnica

• Ostatnim operatorem mnogościowym jest produkt kartezjański.

• Podobnie, jak jego odpowiednik w tradycyjnej teorii mnogościowej, produkt kartezjański dwóch zestawów rekordów łączy każdy rekord jednego zestawu z każdym rekordem drugiego zestawu.

• Produkt kartezjański (lub po prostu „produkt") dwóch zestawów rekordów jest zwracany przez instrukcję SELECT bez klauzuli JOIN.

• Podana niżej instrukcja zwraca każdego klienta z kombinacji z każdym przedstawicielem serwisu klienta (PSK):

SELECT NazwiskoKlienta, NazwiskoPSK FROM Klienci, PSK;

• Produkty kartezjańskie są czasem wygodne do przeprowadzania analizy lub jako wyniki pośrednie do dalszych operacji. Najczęściej jednak powstają przez przypadek.

Operatory mnogościoweprodukt kartezjański

• Operator podsumowania wykonuje dokładnie to, czego można się po nim spodziewać: zwraca rekordy zawierające podsumowanie danych pogrupowanych według wybranych pól.

• Operacja podsumowania jest implementowana za pomocą klauzuli GROUP BY instrukcji SELECT.

• Dla każdej różnej wartości w podanym polu lub w polach jest zwracany dokładnie jeden rekord.

• Jeśli podane jest więcej niż jedno pole, grupy są zagnieżdżane.

Specjalne operatory relacyjnepodsumowanie

• Pola wymienione w pozycji <ListaPól> instrukcji SELECT muszą być również umieszczone w pozycji <ListaPólGrupujących> albo być argumentem funkcji agregującej SQL. Funkcje agregujące SQL podsumowują wartości we wszystkich rekordach.

• Najczęściej używanymi funkcjami agregującymi są AVERAGE, COUNT, SUM, MAXIMUM i MINIMUM.

Specjalne operatory relacyjnepodsumowanie

SELECT Kategorie.NazwaKategorii, Produkty.NazwaProduktu,

SUM([Opisy zamówień].Ilość) AS Sumallości

FROM (Kategorie INNER JOIN Produkty ON Kategorie.IDKategorii=Produkty.IDKategorii)

INNER JOIN [Opisy zamówień]ON Produkty.IDProduktu = [Opisy zamówień].IDProduktu

GROUP BY Kategorie.NazwaKategorii, Produkty.NazwaProduktu;

Specjalne operatory relacyjnepodsumowanie

Specjalne operatory relacyjnepodsumowanie

• Operator rozszerzenia pozwala definiować pola wirtualne, wyliczane na podstawie stałych i wartości znajdujących się w bazie danych, ale nie przechowywane nigdzie fizycznie.

• Aby utworzyć pole wirtualne, wystarczy zdefiniować je w pozycji <ListaPól> instrukcji SELECT.

SELECT [CenaJednostkowa]*[Ilość] AS CenaCałkowita FROM [Opisy zamówień];

Specjalne operatory relacyjnerozszerzenie

• Ostatnim typowym operatorem jest przemianowanie.

• Operacja przemianowania może dotyczyć zarówno zestawu rekordów w pozycji <ListaZestawówRekordów>, jak i indywidualnych pól z pozycji <ListaPól>.

• W aparacie bazy danych Jet przemianowanie ma następującą składnię:

SELECT <NazwaPola> AS <PseudonimPola> FROM <NazwaTabeli> AS <PseudonimTabeli>

• Jak widać poniżej, w SQL Serverze słowo kluczowe „AS" nie jest konieczne:

SELECT <NazwaPola> <PseudonimPola>

FROM <NazwaZestawuRekordów> <PseudonimZestawuRekordów>

Specjalne operatory relacyjneprzemianowanie

• Instrukcja TRANSFORM, częściej nazywana kwerendą krzyżową, pobiera wynik operacji podsumowania (GROUP BY) i obraca go o 90 stopni.

• Ta niezwykle użyteczna operacja jest dostępna tylko w aparacie bazy danych Jet, gdyż nie została (jak dotąd) zaimplementowana w SQL Serverze.

• Instrukcja TRANSFORM ma następującą podstawową składnię:

TRANSFORM <FunkcjaAgregująca>

SELECT <ListaPól>

FROM <ListaZestawówRekordów>

GROUP BY <ListaPólGrupujących>

PIVOT <NagłówekKolumny> [IN (<ListaWartości>)]

Operatory relacyjne – rozszerzenia Microsoft

transform

• Klauzula TRANSFORM <FunkcjaAgregująca> definiuje podsumowane dane, mające wypełnić wynikowy zestaw rekordów.

• Instrukcja SELECT musi zawierać klauzulę GROUP BY i nie może z kolei zawierać klauzuli HAVING.

• Jak w przypadku każdej klauzuli GROUP BY <ListaPólGrupujących> może zawierać wiele pól. (W instrukcji TRANSFORM <ListaPól> i <ListaPólGrupujących> są niemal zawsze identyczne).

• Klauzula PIVOT identyfikuje pole, którego wartości będą używane jako nagłówki kolumn.

• Standardowo aparat bazy danych Jet umieszcza kolumny w wynikowym zestawie rekordów alfabetycznie od lewej do prawej.

• Opcjonalna instrukcja IN pozwala jednak podać w pozycji <ListaWartości> nazwy kolumn w takiej kolejności, w jakiej mają być wyświetlone.

Operatory relacyjne – rozszerzenia Microsoft

transform

TRANSFORM Count(Produkty.IDProduktu) AS LiczbalDProduktów

SELECT Dostawcy.NazwaFirmy

FROM Dostawcy

INNER JOIN (Kategorie INNER JOIN Produkty

ON Kategorie.IDKategorii = Produkty.IDKategorii)

ON Dostawcy.IDDostawcy = Produkty.IDDostawcy

GROUP BY Dostawcy.NazwaFirmy

PIVOT Kategorie.NazwaKategorii;

Operatory relacyjne – rozszerzenia Microsoft

transform

Operatory relacyjne – rozszerzenia Microsoft

transform

• Operator grupowania implementowany przy użyciu klauzuli GROUP BY generuje zestaw rekordów zawierających podsumowane dane. Klauzula ROLLUP umożliwia logiczne rozszerzenie tej operacji poprzez dostarczenie sum całkowitych.

• Klauzula ROLLUP jest dostępna tylko w SQL Serverze.

Operatory relacyjne – rozszerzenia Microsoft

rollup

• Jest ona implementowana jako rozszerzenie klauzuli GROUP BY:

SELECT Kategorie.NazwaKategorii, Produkty.NazwaProduktu,

SUM([Opisy zamówień].Ilość) AS Sumallości FROM (Kategorie INNER JOIN Produkty

ON Kategorie.IDKategorii = Produkty.IDKategorii) INNER JOIN [Opisy zamówień]

ON Produkty.IDProduktu = [Opisy zamówień].IDProduktu

GROUP BY Kategorie.NazwaKategorii, Produkty.NazwaProduktu

WITH ROLLUP;

Operatory relacyjne – rozszerzenia Microsoft

rollup

Operatory relacyjne – rozszerzenia Microsoft

rollup

• Operator CUBE jest dostępny tylko w SQL Serverze i jest implementowany jako rozszerzenie klauzuli GROUP BY.

• Zasadniczo klauzula CUBE podsumowuje każdą kolumnę w pozycji <ListaPólGrupujących> według każdej innej kolumny.

• Operator CUBE jest w zasadzie podobny do operatora ROLLUP, o ile jednak ROLLUP podaje sumy całkowite dla każdej kolumny wymienionej w pozycji <ListaPólGrupujących>, CUBE dokonuje podsumowania danych dla dodatkowych grup.

Operatory relacyjne – rozszerzenia Microsoft

cube

• Na przykład, jeśli <ListaPólGrupujących> zawiera trzy pola - A, B i C - operator CUBE zwróci siedem następujących wartości zagregowanych:

• Sumę wszystkich C,

• Sumy wszystkich C pogrupowanych według A,

• Sumy wszystkich C pogrupowanych według C w ramach A,

• Sumy wszystkich C pogrupowanych według B w ramach A,

• Sumy wszystkich C pogrupowanych według B,

• Sumy wszystkich C pogrupowanych według A w ramach B,

• Sumy wszystkich C pogrupowanych według C w ramach B.

Operatory relacyjne – rozszerzenia Microsoft

cube