Język SQL Język SQL Relacyjna baza danych jest zbiorem tabel. Tabele są dwuwymiarowe, zawierają określoną liczbę kolumn i zmienną liczbę nie uporządkowanych wierszy. Każdy wiersz jest określony za pomocą pewnej liczby atrybutów zwanych kolumnami. W przecięciu kolumny i wiersza znajduje się pojedyncza wartość. Same dane, nawet poukładane w tabelach, to jeszcze bardzo niewiele. Są jedynie podstawą do przetwarzania informacji ze świata rzeczywistego, tworem statycznym i nieożywionym. Aby móc z nich korzystać, trzeba zdefiniować przede wszystkim sposób dostępu do nich oraz pewne procedury umożliwiające podstawowe operacje. W systemach relacyjnych baz danych czynności te wykonywane są za pomocą procedur zwanych zapytaniami lub inaczej kwerendami (query). Język zapytań jest niezbędnym elementem w każdym systemie bazodanowym. Przy jego pomocy użytkownik może określić warunki, które mają spełniać poszukiwane dane, system zaś, na tej podstawie, wyszuka potrzebne informacje. W założeniu język taki nie powinien być uzależniony od konkretnych aplikacji, tj. powinien działać dla dowolnego schematu bazy danych i nie powinien być uzależniony od platformy, czyli powinien działać zarówno na PC-tach, minikomputerach jak i dużych stacjach roboczych. Do formułowania zapytań stworzono kilka języków, początkowo bardzo sformalizowanych. Wiązało się to z matematycznymi podstawami teorii relacyjnych baz danych.. Dopiero później opracowano języki wyższego poziomu, bliższe językowi naturalnemu, których współczesnym przedstawicielem jest język SQL. Język SQL (Structured Query Language – strukturalny język zapytań). Powstał pod koniec lat siedemdziesiątych w firmie IBM. Jest obecnie światowym standardem przeznaczonym do operowania i sterowania relacyjnymi bazami danych. Występuje w produktach większości liczących się firm, sprzedających oprogramowanie dla baz danych. Zaliczany jest do języków czwartej generacji (fourthgeneration language) Oznacza to, że umożliwia użytkownikowi określenie tego co ma być wykonane bez podania konkretnych kroków jak to osiągnąć. SQL i relacyjna baza danych są nieproceduralne, dlatego nie ma potrzeby, aby z góry definiować ścieżkę dostępu do rekordu w bazie. System SQL sam znajdzie ścieżkę do rekordów. Tę właściwość określa się mianem automatycznej nawigacji. Dzięki temu zwiększa się wydajność programisty, a system jest łatwy w obsłudze dla przeciętnego użytkownika. Inną korzyścią wynikającą ze stosowania SQL jest możliwość wymiany danych pomiędzy oprogramowaniem różnego typu takim www.wkuwanko.pl 1 Język SQL jak procesory tekstów czy arkusze kalkulacyjne. Pondto bezpośrednia modyfikacja schematu bazy danych nie zaburza istniejących aplikacji. SQL jest językiem strukturalnym, zdefiniowanym za pomocą reguł składniowych. Zawiera trzy rodzaje poleceń: polecenia języka definiowania danych (DDL), które umożliwiają tworzenie obiektów bazy danych, takich jak np. tabele polecenia języka operowania danymi (DML), które są używane do np. modyfikowania, kasowania, wydobywania informacji z bazy danych. Polecenia języka administrowania danymi, które służą np. do przyznawania i odwoływania uprawnienia dostępu do bazy danych Poniżej podane zostaną podstawowe polecenia języka SQL. Klauzula SELECT Jest to podstawowa instrukcja w SQL używana do wyszukiwania danych w tabeli. Składa się z co najmniej dwóch klauzul: SELECT i FROM.Składnia: SELECT nazwa(y)_kolumn(y) / * FROM nazwa_tabeli; Konstrukcja ta mówi systemowi zarządzania relacyjną bazą danych, które kolumny należy wyszukać w tabeli wymienionej w klauzuli FROM. Nazwę lub nazwy kolumn możemy opcjonalnie zastąpić znakiem * który informuje system, że należy wyszukać wszystkie kolumny tabeli. System w odpowiedzi wyświetli tabelkę o żądanej nazwie, która będzie zawierała kolumny wyspecyfikowane po klauzuli SELECT. Jeśli nie znajdzie żadnych rekordów to tabelka będzie pusta. Po słowie kluczowym SELECT (FROM) może wystąpić nazwa jednej jak i wielu kolumn (tabel). W przypadku podania listy nazw tabel nastąpi połączenie danych z różnych tabel i umieszczenie ich w jednej, wspólnej tabeli. Należy zauważyć, iż każda instrukcja SQL kończy się średnikiem. Przykład: SELECT * FROM nazwa_tabeli Stworzenie takiego zapytania wyświetli całą zawartość tabeli o podanej nazwie. www.wkuwanko.pl 2 Język SQL W kolejnych przykładach posługiwać będziemy się tabelką dotyczącą pracowników o atrybutach podanych poniżej: PRAC NUMP NAZWISK STANOWISK O TELEFON ZATRUD ZAROB PROWIZJA NUMDZ O Obliczenia W poleceniach SQL mogą występować wyrażenia arytmetyczne. Wyrażenie takie składa się z nazw kolumn o liczbowych wartościach i liczb połączonych operatorami: + , - , * , / . Aby wyświetlić wynik obliczenia, trzeba zamieścić wyrażenia arytmetyczne w klauzuli SELECT tak jak poprzednio nazwę kolumny. www.wkuwanko.pl 3 Język SQL Zależności funkcyjne. Poniżej podane zostaną definicje operacji relacyjnych: (1) Relację T typu X nazywamy projekcją relacji R na zbiór X T=R[X] gdy T={tKROTKA(X): ( rR) t=r[X]} Przykład: Mamy tabelkę o trzech kolumnach: A B C a X 1 b X 1 a X 2 c Y 2 Wyznaczyć projekcję na zbiory: {A,C} i {B,C}. www.wkuwanko.pl 4 Język SQL Zgodnie z definicją należy wyrzucić kolumny, których nie ma w zbiorze na jaki rzutowana jest tabela. W wyniku otrzymujemy: A C a 1 b 1 a 2 c 2 B C x 1 x 2 y 2 (2) Relację T(U) nazywamy selekcją relacji R(U) względem warunku selekcji E T=R/E/ wtw gdy T={tR: E(t)=true} Selekcja wiąże się z wyborem odpowiednich krotek za pomocą warunków selekcji (oznaczanych przez E) czyli z wykorzystaniem: operacji : =, , <, , >, , spójników logicznych: , , , Przykład: Dana jest relacja: A B C D a X 1 3 a Y 4 2 c X 3 3 b X 2 1 Wyznaczyć selekcję: T=R / CD(A=a A=b) / www.wkuwanko.pl 5 Język SQL Odpowiedź: A B C D a Y 4 2 b X 2 1 (3) Niech będą dane relacje R typu X i S typu Y. Relację typu XY nazywamy złączeniem tych relacji T=R S wtw gdy T={tKROTKA(XY): t[X]R t[Y]S} Przykład: Dane są dwie tabele R i S: R A B C a X 1 a X 2 a Y 2 b Y 3 A B D a X f a Y g b X h S Wyznaczyć złączenie R i S. W obu tabelach powtarzają się wartości ax i ay w kolumnach AB. Łączymy tylko to co jest wspólne, gdybyśmy połączyli wszystkie kolumny w wyniku otrzymalibyśmy iloczyn kartezjański a więc o wiele więcej wierszy. www.wkuwanko.pl 6 Język SQL A B C D a X 1 f a X 2 f a Y 2 g Wyznaczenie złączenia relacji polega na utworzeniu takiej tabeli, której krotki powstają z połączenia tych krotek z odpowiednich relacji, które mają jednakowe wartości na tych samych atrybutach (czyli każdy atrybut może wystąpić tylko raz). (4) Relację T(U-X) nazywamy podzieleniem relacji R przez zbiór X T=R/X wtw gdy T={tKROTKA(U-X): dla każdego sKROTKA(X) t s R} Przykład: Dane są zbiory atrybutów relacji: U={S,P} i X={S} i ich dziedziny: DOM(S)={1,2,3} i DOM(P)={a,b,c} oraz relacja R(U): S P 1 A 2 B 1 B 1 C 3 C Wówczas krotki są wektorami: KROTKA(S) 1 2 3 KROTKA(P) a b www.wkuwanko.pl 7 Język SQL c Zatem T=R/{P} S 1 Zależności funkcyjne Mając relacje będziemy teraz poszukiwać prawidłowości jakie w nich występują czyli interesować nas będą semantyczne właściwości relacji. Przykład: Egzamin I N P O 10 F a 3 10 F b 4 11 G a 3 12 H a 3 Dana jest tabela o nazwie Egzamin, w której poszczególne pola mają następujące znaczenie: I – numer indeksu N – nazwisko P – przedmiot O – ocena z egzaminu W tabeli tej możemy zauważyć pewien związek: numer indeksu i nazwisko wskazują na tę samą osobę. Mamy tu zależność między atrybutami I oraz N co można zapisać: IN Inna zależność: ocena zależy od przedmiotu i od studenta: IPO Należy podkreślić, że nie są to funkcje a jedynie zależności funkcyjne. Funkcja oznacza istnienie stałego przyporządkowania między elementami zbioru, natomiast zależność funkcyjna nie reprezentuje tej stałości. www.wkuwanko.pl 8 Język SQL Zależność funkcyjna między zbiorem atrybutów X i Y istnieje wtedy gdy w każdym stanie istnieje pewna funkcja ze zbioru krotek typu X w zbiór krotek typu Y. W różnych stanach funkcje te mogą być różne. Zależnością funkcyjną nazywamy każdy zapis postaci: XY gdzie X,YU. Mówimy wówczas, że X determinuje funkcyjnie Y lub że Y zależy funkcyjnie od X. Mówimy, że w tabeli R spełniona nest zależność funkcyjna XY jeżeli dla dwóch krotek r1,r2R : (r1[X]=r2[X]) (r1[Y]=r2[Y]) Istnieją pewne zasady pozwalające w sposób formalny manipulować na atrybutach i dzięki temu można wydedukować jedne zależności funkcyjne z innych. Niech będą dane trzy podzbiory: X,Y,XU. Oznaczmy przez F+ najmniejszy zbiór zależności funkcyjnych, który zawiera zbiór F i jest zamknięty ze względy na następujące reguły wyprowadzeń: F1: YX XY F+ (zwrotność) F2: XY F+ XZYZ F+ (poszerzalność) F3: XY F+ YZ F+ XZ F+ (przechodniość) Zbiór F+ nazywamy najmniejszym domknięciem zbioru F. Zależności F1-F3 to tzw aksjomaty Armstronga. Zbiór tych aksjomatów jest niesprzeczny. Korzystając z nich można wyprowadzić kolejne aksjomaty: F4: XY F+ YWZ F+ XWZ F+ F5: XY F+ XZ F+ XYZ F+ F6: XYZ F+ XY F+ XZ F+ (pseudoprzechodniość) (addytywność) (dekompozycyjność) Minimalny zredukowany generator zbioru F + jest to najmniejszy podzbiór F0 zbioru F dla którego F0+ =F+. www.wkuwanko.pl 9 Język SQL Przykład: Dany jest zbiór zależności funkcyjnych: F={ PGS, GSP, PIO, GIPS, PGSE } Zbiór U składa się więc z atrybutów: U={ P, I, O, E, G, S } gdzie P – przedmiot egzaminacyjny I – numer indeksu O – ocena z egzaminu E – numer ewidencyjny egzaminatora G – godzina egzaminu S – sala egzaminacyjna Spróbujmy wyprowadzić minimalny zredukowany generator dla tego zbioru. F01 ={ PG, PS, GSP, PIO, GIP, PE } bo PGS PG i PS GIPS GIP i GIS w zależnościach PGS i PGSE powtarza się GS zatem PGS PGSE PE F02 ={ PG, PS, GSP, PIO, GIS, PE } Prawe strony zależności funkcyjnych są pojedynczymi atrybutami zatem jest to minimalny zredukowany generator rodziny zależności F+. Badanie związków między rozkładalnością relacji na relacje bardziej elementarne będziemy nazywać rozkładalnością schematów relacyjnych. Wyróżniamy trzy rodzaje takiej rozkładalności: rozkładalność bez straty danych – jeśli mamy tabelę o wielu kolumnach i rozłożymy ją na mniejsze tabelki, to po złączeniu danych stracimy zależności funkcyjne rozkładalność bez straty zależności funkcyjnych – po złączeniu nie mamy gwarancji zachowania danych rozkładalność na składowe niezależne – nie tracimy ani danych, ani zależności Teoria ta potrzebna jest do normalizacji a więc projektowania baz danych. www.wkuwanko.pl 10 Język SQL Proces normalizacji schematów relacyjnych Dokonamy teraz formalizacji pojęcia klucza. Niech będzie dany schemat relacyjny: R=(U,F) gdzie U – cały zbiór atrybutów F – zbiów wszystkich zależności funkcyjnych Zbiór atrybutów KU nazywamy kluczem (key) schematu R wtw gdy zbiór ten spełnia następujące warunki: a) KU F+ (jednoznaczna identyfikowalność) Od klucza muszą być uzależnione funkcyjnie wszystkie atrybuty należące do zbioru U. b) XU F+ (XK) (minimalność) Kluczem może być tylko taki zbiór atrybutów, którego żaden podzbiór nie ma cechy jednoznacznej identyfikowalności, czyli klucz musi być najmniejszym zbiorem. Kryteria wyboru klucza: minimalna liczba atrybutów możliwość przewidzenia zakresu wartości klucza (najlepiej typ wyliczeniowy) niewystępowanie wśród wartości klucza wartości nieokreślonych Przykład: Niech będzie dany schemat relacji E (tabela znajduje się niżej): E=( {I, N, A, K, P, O}, {INAK, IPO} ) gdzie: P – przedmiot egzaminacyjny I – numer indeksu N – nazwisko O – ocena z egzaminu A - adres zamieszkania studenta K – kierunek studiów Pytanie: co będzie kluczem relacji? Odpowiedź: kolumny I oraz P. www.wkuwanko.pl 11 Język SQL Klucz relacji podkreślamy zatem należy napisać: E=( {I, N, A, K, P, O}, {INAK, IPO} ) W schematach relacyjnych mogą występować różne anomalie. Anomalie te mogą być usuwane przez rozkładanie schematów relacyjnych na bardziej elementarne. Proces taki (zaproponowany przez Codd’a) nazywamy procesem normalizacji. 1PN (pierwsza postać normalna) Schemat relacyjny jest w 1PN jeżeli wszystkie atrybuty występujące w tym schemacie są o wartościach prostych. Wartości proste to takie, które nie są podzielne np.: liczby: 58, 34 napisy: „Kowalski” W dalszych przykładach posłużymy się poniższą tabelą: I N A K P O 10 F x 100 a 3 10 F x 100 b 4 11 G y 200 a 3 12 H x 200 a 3 10 F x 100 c 5 Wyróżniamy trzy rodzaje anomalii: anomalia dołączania Powyższa tabela dotyczy tylko tych studentów, którzy zdali egzamin, nie obejmuje natomiast tych, którzy do egzaminu nie przystąpili. A zatem wielu studentów nie moglibyśmy w niej umieścić, gdyż wtedy klucz IP nie byłby pełny. Trudność tę można by przezwyciężyć w sposób sztuczny dopuszczając wartości nieokreślone lub zerowe. Ale klucz nie może mieć takiej wartości. anomalia aktualizacji www.wkuwanko.pl 12 Język SQL Przypuśćmy, że student „10” zmienił adres zamieszkania z „x” na „w”. Ponieważ student ten występuje w trzech krotkach musielibyśmy dokonać trzech poprawek. Jednak należy pamiętać, że przeglądanie dużej tablicy jest czasochłonne. Często przy większych bazach zawartość rekordów może się zmieniać w czasie. Może się wówczas zdarzyć, że przed zakończeniem procesu aktualizacji baza danych mogła zostać zmieniona. Wypływa z tego oczywisty wniosek: w aktualizacji powinno uczestniczyć jak najmniej elementów. anomalia usuwania Ten rodzaj anomalii polega na tym, że usuwając jakieś krotki możemy zgubić cenne informacje potrzebne do innych celów. Przypuśćmy, że student o numerze „12” ściągał i jego egzamin zostaje unieważniony. Jeśli skasujemy cały jego rekord, to stracimy informacje o adresie zamieszkania, nazwisku itd. Podsumowując: pod wieloma względami baza danych składająca się z jednej tabeli jest niekorzystna,.poza tym zajmuje dużo pamięci. Lepiej jeśli składa się z kilku mniejszych tabelek. Powinno się dążyć do wyeliminowania anomalii. Robi się to przez normalizację bazy. Pierwszym etapem jest doprowadzenie jej do 2PN. 2PN (druga postać normalna) Schemat relacyjny R=(U,F) jest w 2PN jeśli każdy niekluczowy atrybut AU jest w pełni funkcyjnie zależny od klucza. W pełni tzn. zależy od całego klucza a nie jego części. I N A K P O www.wkuwanko.pl 13 Język SQL Atrybuty IP stanowią klucz. Atrybut O zależy od całego klucza IP. Z kolei trzy atrybuty NAK zależą tylko od I, a nie zależą od P. A więc te trzy atrybuty nie zależą od całego klucza. Wobec tego ten schemat relacyjny nie jest w 2PN. Aby doprowadzić go do 2PN należy dokonać rozbicia na dwa schematy: I N A K I P O Czyli dużą tabelę rozbić na dwie mniejsze: w jednej zawarte będą informacje o studentach, a w drugiej o egzaminach: I N A I P O K Tak wygląda projektowanie relacyjnych baz danych. Tabelki powinny być tworzone z przeznaczeniem tematycznym, żeby nie było mieszania (redundancji) informacji. Cana jaką płacimy to powtarzanie się niektórych kolumn. www.wkuwanko.pl 14 Język SQL Dekompozycja na tabelki mniejsze nie jest w ogólnym przypadku jednoznaczna tzn. można porozbijać ją na wiele sposobów. Jeżeli klucz składa się z jednego atrybutu to dany schemat jest już w 2PN. 3PN (trzecia postać normalna) Posiadanie przez jakiś schemat relacyjny właściwości 2PN nie jest wystarczające do tego aby nie wystąpiły anomalia (choć w wielu przypadkach jest). Wówczas należy dalej normalizować schemat doprowadzając do 3PN. Przykład: Niech będzie dany schemat relacyjny: R=({W,A,P,D}, {WAPD, PD} gdzie: W – wykonawca A – adres wykonawcy P – projekt zlecony wykonawcy do realizacji D – data zakończenia projektu Podany zbiór zależności funkcyjnych ma następującą interpretację semantyczną: 1) WAPD – każdy wykonawca ma jednoznacznie określony adres i może realizować tylko jeden projekt. Natomiast jeden projekt może być wykonywany przez wielu wykonawców. Każdy wykonawca ma określony termin zakończenia projektu. 2) PD – termin ukończenia projektu jest taki sam dla wszystkich wykonawców (bo przecież kiedyś trzeba projekt zakończyć) Kluczem jest atrybut „W”. Ten schemat jest w 2PN ponieważ ma jednoelementowy klucz, ale anomalie mogą jeszcze wystąpić: 1) anomalia dołączenia – nie można zapamiętać informacji o projekcie i dacie jego zakończenia; 2) anomalia aktualizacji – jeżeli będzie wymagana zmiana daty ukończenia któregoś z projektów, to spowoduje to wiele zmian w różnych krotkach; www.wkuwanko.pl 15 Język SQL 3) anomalia usuwania – jeżeli zaniechamy realizacji jakiegoś projektu to usuwając krotkę tracimy informacje o wykonawcy. Albo jeśli jest jeden wykonawca jakiegoś projektu to jeśli się wycofa – stracimy informacje o projekcie. Z 3PN związane jest pojęcie zależności tranzytywnej. Zbiór atrybutów Z jest tranzytywnie zależny od zbioru atrybutów X w schemacie relacyjnym R=(U,F) jeżeli: X i Z są rozłączne Istnieje YU rozłączne z X iż oraz takie, że: XYF+ YXF+ YZF+ Czyli jest to taka zależność pośrednia – mamy tu tranzyt przez Y: X Y Z Schemat relacyjny R=(U,F) jest w 3PN jeśli jest w 2PN i żaden zbiór niekluczowych atrybutów ZU nie jest tranzytywnie zależny od klucza tego schematu. W rozpatrywanym przykładzie mamy: W A P D Musimy wyeliminować taką zależność na dwie mniejsze (z jednej tabeli stworzyć dwie): W A P www.wkuwanko.pl 16 Język SQL P D www.wkuwanko.pl 17