e - Student
advertisement
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. Operatory relacyjne rzut 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 łą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. Operatory relacyjne – łączenie RÓ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 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 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)); 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ę. 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. 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 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)>[Średnie ZProduktó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 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. 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 dzielenie • 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 relacyjne dzielenie • 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 relacyjne dzielenie • 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 mnogościowe suma • 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ściowe suma 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ściowe suma Operatory mnogościowe przecięcie • 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ściowe przecięcie Przypuśćmy na przykład, że otrzymano z kilku różnych systemów listy klientów i trzeba znaleźć powtarzających się: ZdublowaniKlienci2 ZdublowaniKlienci1 Operatory mnogościowe przecięcie 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ściowe przecięcie Operatory mnogościowe róż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. Operatory mnogościowe róż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ściowe produkt kartezjański • 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. Specjalne operatory relacyjne podsumowanie • 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 relacyjne podsumowanie • 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 relacyjne podsumowanie 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 relacyjne podsumowanie Specjalne operatory relacyjne rozszerzenie • 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 relacyjne przemianowanie • 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> Operatory relacyjne – rozszerzenia Microsoft transform • 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 rollup • 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 cube • 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.
