Algebra relacyjna

1. Algebra relacyjna Przykłady: Projektowanie systemów relacyjnych baz danych – R. M. Riordan Northwind.mdb

2. Instrukcja SELECT SELECT <ListaPól> FROM <ListaZestawówRekordów> <RodzajŁączenia> JOIN <Warunek> WHERE <KryteriaWyboru> GROUPBY <ListaPólGrupujących> HAVING <KryteriaWyboru> ORDERBY <ListaPólPorządkujących>

3. Logika trójwartościowa

4. Logika trójwartościowa

5. 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.

6. Operatory relacyjnewybó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.

7. Operatory relacyjnerzut 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ę.

8. Operatory relacyjnełączenie • 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.

9. Operatory relacyjne– łączenieRÓWNOŁĄ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 Opisyzamówień. • Aby ponownie połączyć te tabele (i w konsekwencji doprowadzić do denormalizacji), należy użyć następującej instrukcji:

10. Operatory relacyjne– łączenieRÓWNOŁĄCZENIE SELECT Zamówienia.IDZamówienia, Zamówienia.IDKlienta,[Opisy Zamówień].IDProduktu FROM Zamówienia INNERJOIN [Opisy zamówień] ON Zamówienia.IDZamówienia = [Opisy zamówień].IDZamówienia WHERE (((Zamówienia.IDZamówienia)=10248));

11. Operatory relacyjne– łączenieŁĄCZENIE NATURALNE • 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ę.

12. Operatory relacyjne– łączenieŁĄCZENIE THETA • 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.

13. Operatory relacyjne– łączenieŁĄCZENIE THETA ŚrednieZProduktówKategorii 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. ProduktyOgółem

14. 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: SELECTDISTINCTROW ŚrednieZProduktówKategorii.NazwaKategorii, ProduktyOgółem.NazwaProduktu FROM ŚrednieZProduktówKategorii INNERJOIN ProduktyOgółem ON ŚrednieZProduktówKategorii.IDKategorii = ProduktyOgółem.IDKategorii AND ProduktyOgółem.SprzedażCałkowita > [ŚrednieZProduktówKategorii].[SprzedażŚrednia];

15. Operatory relacyjne– łączenieŁĄCZENIE THETA

16. Operatory relacyjne– łączenieŁĄCZENIE THETA • Lub za pomocą klauzuli WHERE: SELECTDISTINCTROW ŚrednieZProduktówKategorii.NazwaKategorii, ProduktyOgółem.NazwaProduktu FROM ŚrednieZProduktówKategorii INNERJOIN ProduktyOgółem ON ŚrednieZProduktówKategorii.IDKategorii = ProduktyOgółem.IDKategorii WHERE (((ProduktyOgółem.SprzedażCałkowita)>[ŚrednieZProduktówKategorii].[SprzedażŚrednia])) ;

17. 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.

18. Operatory relacyjne– łączenieŁĄCZENIE ZEWNĘTRZNE • 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.

19. Operatory relacyjne– łączenieŁĄCZENIE ZEWNĘTRZNE • Algebra relacyjna udostępnia jeszcze jeden rodzaj łączenia - łączeniezewnę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.

20. 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 * FROMX LEFTOUTERJOIN Y ON <warunek> SELECT * FROM Y RIGHTOUTERJOINX ON <warunek>

21. Operatory relacyjne– łączenieŁĄCZENIE ZEWNĘTRZNE • Pełne łączenie zewnętrznezwraca 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 * FROMX FULLOUTERJOIN 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.

22. Operatory relacyjnedzielenie • 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.

23. 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.

24. 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.

25. Operatory mnogościowesuma • Ogólnie suma relacyjnajest 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.

26. 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 UNIONSELECT [Imię] & " " & [Nazwisko] As Nazwa, Adres, Miasto, KodPocztowy FROM Pracownicy ORDERBY Nazwa;

27. Operatory mnogościowesuma

28. Operatory mnogościoweprzecięcie • Operator przecięciazwraca 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.

29. 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

30. Operatory mnogościoweprzecięcie Instrukcja SELECT zwracająca zdublowane rekordy: SELECT ZdublowaniKliencil.* FROM ZdublowaniKliencil LEFTJOIN ZdublowaniKlienci2 ON (ZdublowaniKliencil.IDKlienta = ZdublowaniKlienci2.IDKlienta) AND (ZdublowaniKliencil.NazwaFirmy = ZdublowaniKlienci2.NazwaFirmy) WHERE (((ZdublowaniKlienci2.IDKlienta) ISNOTNULL));

31. Operatory mnogościoweprzecięcie

32. Operatory mnogościoweróżnica • 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.

33. 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 LEFTJOIN ZdublowaniKlienci2 ON (ZdublowaniKliencil.IDKlienta = ZdublowaniKlienci2.IDKlienta) AND ZdublowaniKliencil.NazwaFirmy = ZdublowaniKlienci2.NazwaFirmy) WHERE (ZdublowaniKlienci2.IDKlienta ISNULL);

34. Operatory mnogościoweprodukt kartezjański • Ostatnim operatorem mnogościowym jest produkt kartezjański. • Podobnie, jak jego odpowiednik w tradycyjnej teorii mnogościowej, produkt kartezjańskidwó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.

35. Specjalne operatory relacyjnepodsumowanie • 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.

36. 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.

37. Specjalne operatory relacyjnepodsumowanie SELECT Kategorie.NazwaKategorii, Produkty.NazwaProduktu, SUM([Opisy zamówień].Ilość) AS Sumallości FROM (Kategorie INNERJOIN Produkty ON Kategorie.IDKategorii=Produkty.IDKategorii) INNERJOIN [Opisy zamówień]ON Produkty.IDProduktu = [Opisy zamówień].IDProduktu GROUPBY Kategorie.NazwaKategorii, Produkty.NazwaProduktu;

38. Specjalne operatory relacyjnepodsumowanie

39. Specjalne operatory relacyjnerozszerzenie • 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ń];

40. Specjalne operatory relacyjneprzemianowanie • 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>

41. Operatory relacyjne – rozszerzenia Microsofttransform • 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> GROUPBY <ListaPólGrupujących> PIVOT <NagłówekKolumny> [IN (<ListaWartości>)]

42. Operatory relacyjne – rozszerzenia Microsofttransform • 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.

43. Operatory relacyjne – rozszerzenia Microsofttransform TRANSFORM Count(Produkty.IDProduktu) AS LiczbalDProduktów SELECT Dostawcy.NazwaFirmy FROM Dostawcy INNERJOIN (Kategorie INNERJOIN Produkty ON Kategorie.IDKategorii = Produkty.IDKategorii) ON Dostawcy.IDDostawcy = Produkty.IDDostawcy GROUPBY Dostawcy.NazwaFirmy PIVOT Kategorie.NazwaKategorii;

44. Operatory relacyjne – rozszerzenia Microsofttransform

45. Operatory relacyjne – rozszerzenia Microsoftrollup • 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.

46. Operatory relacyjne – rozszerzenia Microsoftrollup • Jest ona implementowana jako rozszerzenie klauzuli GROUP BY: SELECT Kategorie.NazwaKategorii, Produkty.NazwaProduktu, SUM([Opisy zamówień].Ilość) AS Sumallości FROM (Kategorie INNERJOIN Produkty ON Kategorie.IDKategorii = Produkty.IDKategorii) INNERJOIN [Opisy zamówień] ON Produkty.IDProduktu = [Opisy zamówień].IDProduktu GROUPBY Kategorie.NazwaKategorii, Produkty.NazwaProduktu WITHROLLUP;

47. Operatory relacyjne – rozszerzenia Microsoftrollup

48. Operatory relacyjne – rozszerzenia Microsoftcube • 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.

49. Operatory relacyjne – rozszerzenia Microsoftcube • 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.