Nauka SQL – aplikacja do nauki SQL

Mam przyjemność zaprezentować wersję BETA skryptu/aplikacji do nauki SQL.

aplikacja dostępna tutaj

Aplikacja pozwala na pisanie zapytań SQL i ich testowanie. W chwili obecnej obsłużony został tylko dialekt MySQL.
Trwają prace nad obsługą innych baz (Oracle, SQL Server).

Równolegle szykowane są kolejne rozdziały z zadaniami.

Zapraszam do komentowania aplikacji, feedback mile widziany.

Baza danych – definicja, informacje teoretyczne

Baza danych jest pewnym modelem wycinka rzeczywistości w danej organizacji
(np. firmie, uczelni). Tę rzeczywistość nazywamy obszarem analizy (ang. Uniwerse Of Discourse, UOD) [Beynon-Davies 2003].
Bazę danych możemy uważać za zbiór danych (faktów), których zadaniem jest reprezentowanie określonego UOD. Jednostka danych, jest symbolem, którego używamy, aby reprezentować jakąś rzecz. Chcąc by fakty były użyteczne, muszą być one zinterpretowane. Dane zinterpretowane to informacje. Natomiast informacje to dane w otoczeniu (kontekście) nadającym im znaczenie. Zbiór faktów lub predykatów na temat obszaru analizy tworzą bazę danych. Zazwyczaj fakty negatywne, np. które przedmioty nie są zaliczane przez studenta, nie są przechowywane [Beynon-Davies 2003].
W ściśle określonej chwili baza danych znajduje się w pewnym stanie. Stan oznacza zbiór faktów, które są prawdziwe w danej chwili. Dlatego też bazę danych uważamy za bazę faktów, która zmienia się w czasie [Beynon-Davies 2003].
Baza danych składa się z części intensjonalnej oraz ekstensjonalnej. Część intensjonalna bazy danych jest zbiorem definicji, które opisują strukturę danych. Z kolei część ekstensjonalna jest łącznym zbiorem danych w bazie danych. Intensjonalną stronę bazy danych nazywamy schematem bazy danych. Tworzenie schematu systemu bazy danych jest
w istocie procesem projektowania bazy danych. Baza danych musi być zaprojektowana. Natomiast proces projektowania bazy danych jest czynnością polegającą na reprezentowaniu klas, atrybutów i związków w bazie danych [Beynon-Davies 2003].

Charakterystyka baz danych

Dane w bazie danych traktowane są, jako trwałe. Przez trwałość rozumiemy, że dane są przechowywane przez pewien przedział czasowy. Ten przedział nie musi być bardzo duży. Termin „trwałość” jest używany do rozróżnienia bardziej trwałych danych od informacji, które są tymczasowe. Dlatego dane dotyczące pacjentów, pracowników czy studentów są zwykle uważane za przykłady danych trwałych. Dane wprowadzone przy terminalu, przeznaczone do przetwarzania w programie lub drukowane w postaci raportu, nie są uznane za trwałe, ponieważ po jednym użyciu nie są już dłużej potrzebne [Beynon-Davies 2003].
Chcąc uznać, iż baza danych ma właściwość integralności, musi zawierać dokładne odbicie obszaru analizy. Proces zapewnienia integralności jest jednym z podstawowych zadań systemu zarządzania danymi. Integralność oznacza zapewnienie, że baza danych daje poprawne odpowiedzi na zadane pytania. Jest ważną sprawą, ponieważ większość baz danych jest projektowana z myślą o zmianach zachodzących w trakcie ich używania. Innymi słowy, dane w bazie danych ulegają zmianie w czasie [Beynon-Davies 2003].
W zbiorze możliwych, przyszłych stanów bazy danych niektóre są poprawne,
a niektóre nie. Każdy poprawny stan stanowi zawartość bazy danych w konkretnym momencie. Integralność jest procesem zapewniającym, że baza danych zmienia się
w przestrzeni, określonej przez stany poprawne. Integralność jest związana z określeniem, czy przejście do kolejnego stanu jest poprawne [Beynon-Davies 2003].
Integralność bazy danych jest zapewniana przez więzy integralności, czyli reguły, które określają, w jaki sposób baza danych ma pozostać dokładnym odbiciem swojego obszaru analizy. Więzy (integralności) dzielimy na dwa główne typy: więzy statyczne i więzy przejść.
Więzy statyczne, czyli „niezmiennik stanu”, używamy do sprawdzania, czy wykonywana transakcja nie zmienia stanu bazy danych w stan niepoprawny. Więzy statyczne to ograniczenie określone na stanie bazy danych. Zapewniają np. unikalność wartości bądź zawieranie wartości w określonym zbiorze [Beynon-Davies 2003].
Więzy przejść są to reguły, które wiążą ze sobą stany bazy danych. Przejście jest zmianą stanu i dlatego może być reprezentowane przez parę stanów. Także są ograniczeniem nałożonym na przejście poprzez zablokowanie możliwości zakończenia transakcji przy próbie wprowadzenia faktów niezgodnych z modelem [Beynon-Davies 2003].
Bazy danych są projektowane tak, aby zminimalizować powtarzanie się danych.
W bazie danych chcemy przechowywać tylko jeden element danych na temat obiektów lub związków między obiektami z naszego obszaru analizy. Idealna baza danych powinna być zbiorem niepowtarzających się faktów. Niestety ze względu na zmiany zachodzące w czasie, w celu zachowania integralności danych (zgodności z obszarem analizy) niektóre informacje muszą zostać zapisane więcej niż jeden raz. Przykładem takiego powielania informacji
w bazie jest nagłówek dokumentu. Należy na nim zapisać identyfikator kontrahenta oraz wszelkie wymagane dane dla danego typu dokumentu. Mimo iż logika tworzenia baz danych sugerowałaby zapisanie samego identyfikatora, to w sytuacji, gdy kontrahent zmieni jakąś składową nagłówka (np. zmieni adres) baza może przejść w stan niespójny. W takiej sytuacji dopuszczamy sytuacje, w której dokument zapisany w bazie danych może być różny od jego wydrukowanej wersji, co jest z kolei niedozwolone. Tak, więc idealna baza danych to nie tylko zbiór niepowtarzających się faktów, ale również zbiór faktów nastawiony na minimalizację powtórzeń pozostający w spójności z obszarem analizy w okresie czasu.
Zdarzenia, które powodują zmianę stanu, są w terminologii baz danych nazywane transakcjami. Transakcja zmienia bazę danych z jednego stanu w kolejny (nowy). Nowy stan jest wprowadzany przez stwierdzenie faktów, które stają się prawdziwe oraz przez zaprzeczenie faktów, które przestają być prawdziwe [Beynon-Davies 2003].
Większość danych jest przechowywana w bazie danych po to, aby spełnić pewne potrzeby związane z organizacją jakiejś struktury. Do wykonywania takiej operacji na bazie danych są potrzebne dwa rodzaje funkcji: aktualizujące i zapytań. Funkcje aktualizujące dokonują zmian danych (przeprowadzają transakcje). Funkcje zapytań wydobywają dane
z bazy danych niedokonując żadnych zmian w strukturze danych. Akcja danej funkcji aktualizującej może spowodować, że warunki innej funkcji aktualizującej staną się prawdziwe. W tym przypadku mówimy, że pierwsza funkcja aktualizująca „wyzwoliła” zmianę stanu. W ten sposób z transakcji „zewnętrznej” możemy uruchomić kaskadowo transakcje „wewnętrzne”.
Drugim podstawowym typem funkcji bazy danych jest funkcja zapytania. Nie modyfikuje ona w żaden sposób bazy danych, ale jest używana głównie do sprawdzania, czy pewien fakt lub grupa faktów jest spełniona w danym stanie bazy danych
[Beynon-Davies 2003].
Każdy system bazy danych musi używać jakiegoś formalizmu reprezentacji. Patrick Henry Winston zdefiniował formalizm reprezentacji, jako zbiór składniowych
i semantycznych konwencji, które umożliwiają opisywanie rzeczy. Składnia reprezentacji określa reguły łączenia symboli w celu kreowania wyrażeń w formalizmie reprezentacji. Semantyka reprezentacji określa, w jaki sposób takie wyrażenia mają być rozumiane, innymi słowy, w jaki sposób wprowadza się ich znaczenie. W terminologii baz danych idea formalizmu reprezentacji odpowiada pojęciu modelu danych. Model danych dostarcza twórcom baz danych zbioru reguł, za pomocą, których mogą skonstruować system bazy danych. Istnieje wiele różnych modeli danych. Najbardziej powszechny jest relacyjny model danych, który oferuje bardzo elastyczny sposób reprezentowania faktów, ale nie dostarcza łatwego sposobu reprezentowania zawiłości funkcji aktualizujących w systemach baz danych. Obiektowe (post-relacyjne) bazy danych oferują dobrą notację reprezentowania funkcji aktualizujących w systemach baz danych, ale ich dużą wadą jest brak jednolitości dostępnych implementacji [Beynon-Davies 2003]. Należy także zauważyć, iż najpopularniejszy (relacyjny) model danych ze względu na drobne różnice w implementacji u konkretnych dostawców oprogramowania bazodanowego napotyka podobne trudności, co modele post-relacyjne. Skutkuje to dużymi trudnościami przy przenoszeniu logiki bazodanowej pomiędzy dostawcami, a czasem nawet pomiędzy różnymi wersjami oprogramowania tego samego dostawcy.
Baza danych może być używana tylko przez jedną osobę lub jeden system użytkowy. Jednakże w większości przedsiębiorstw wiele baz danych jest używanych przez wielu użytkowników. Korzystanie z wspólnych danych stanowi główną cechę większości systemów baz danych.
Z definicji w bazie danych z wieloma użytkownikami musi istnieć metoda obsługi przypadku, gdy kilka osób lub systemów użytkowych chce uzyskać dostęp do tych samych danych w tym samym czasie. Problem ten nosi nazwę współbieżności
[Beynon-Davies 2003].
Weźmy pod uwagę sytuację, gdy jeden użytkownik rejestruje np. studenta na określonym kierunku studiów. W tej samej chwili inny użytkownik usuwa ze zbioru aktualnie oferowanych kierunków, kierunek na który właśnie jest rejestrowany student. Jest oczywiste, że w tej chwili, gdy pierwszy użytkownik wprowadza fakt rejestracji, drugi użytkownik mógłby zanegować fakt istnienia tego faktu. Baza danych zostałaby w stanie niespójnym.
W takim systemie bazy danych muszą istnieć sposoby radzenia sobie z tego typu sytuacjami
[Beynon-Davies 2003]. Moim zdaniem należy też zwrócić uwagę, iż mechanizmy panowania nad problemem współbieżności implementowane są przez każdego dostawcę inaczej. Dlatego też już na etapie projektowania bazy należy wybrać dostawcę systemu bazodanowego
i uwzględnić dany mechanizm. Nawet subtelne różnice mogą okazać się przeszkodą sprawiającą, że dany system stanie się bezużyteczny dla końcowego użytkownika.

Klucz Obcy -teoria

Klucze obce łączą dane przechowywane w różnych tabelach. Klucz obcy jest kolumną lub grupą kolumn z jednej tabeli, która czerpie swoje wartości z tej samej dziedziny co klucz główny tabeli powiązanej z nią w bazie danych. W systemach relacyjnych wprowadzamy specjalną wartość, aby wskazać niepełną lub nieznaną informację – wartość „null”.
Ta wartość, różna od zera i spacji, jest szczególnie użyteczna przy powiązaniu kluczy głównego i obcego.

Z pojęciem klucza głównego i obcego wiąże się pojęcie integralności:

Integralność referencyjna dotyczy kluczy obcych. Reguła integralności referencyjnej mówi, że każda wartość klucza obcego może się znajdować tylko w jednym z dwóch stanów.
Normalnie wartość klucza obcego odwołuje się do wartości klucza głównego w tabeli w bazie danych. Czasami wartość klucza obcego może być null i fakt ten zależy od zasad związanych z przedsięwzięciem. W tym wypadku jawnie stwierdzamy, że nie ma związku z reprezentowanymi obiektami w bazie danych albo że ten związek jest nieznany [Beynon-Davies 2003].

Oczywiście oprócz integralności referencyjnej istnieje integralność encji ( która w sumie powinna być pierwsza opisana):

Integralność encji dotyczy kluczy głównych. Jest ona również regułą integralności, która mówi, że każda tabela musi mieć klucz główny oraz że kolumna lub kolumny wybrane jako klucz główny powinny być jednoznaczne i nie zawierać wartości null.

Dla każdego związku między tabelami w naszej bazie danych powinniśmy określić, jak mamy postępować z usuwaniem docelowych i powiązanych kartotek. Na przykład, jeżeli usuniemy wiersz z tabeli w bazie danych, to musimy zdecydować, co powinno się stać z powiązanymi wierszami w tabeli korzystającej z klucza obcego wskazującego na tabele, w której usuwamy wiersz.

Są trzy (a właściwie cztery) możliwości:

graniczone usuwanie (ang. restricted delete). Jest to ostrożne podejście. Oznacza to, że zabraniamy usunąć wiersz np. z danymi wykładowcy dopóty, dopóki nie będą usunięte wszystkie wiersze informacji o zajęciach prowadzonych przez tego wykładowcę.

Kaskadowe usuwanie (ang. cascades delete). Jest to ufne podejście. Jeżeli usuwamy wiersz np. nagłówka dokumentu, to są jednocześnie usuwane wszystkie powiązane z nim pozycje tego dokumentu.

Wstaw null przy usuwaniu (ang. nullifies delete). Jest to wyważone podejście. Jeżeli usuwamy wiersz wykładowcy, to numery pracowników w powiązanych wierszach przedmiotów ustawiamy na null.

Istnieje także możliwość wymuszenia braku akcji
(ang. no action). Jest to podejście wysoce nie rozważne, co uargumentowałem we wstępie do integralności referencyjnej. Jeżeli usuniemy wiersz klucza głównego (wykładowcy) to wartości klucza obcego (np. prowadzonych przez niego przedmiotów nie ulegną zmianie).

wstęp do DML – czyli trwała zmiana danych w bazie.

DML czyli data modyfication language, jest kolejnym tematem rzeką związanym z SQL. Większość osób do DML zalicza także zapytania typu SELECT, ale osobsićie uważam że trwałe modyfikacje danych należy omówić jako osobny rozdział. Umiejętność modyfikowania danych jest potrzebna zdecydowanie mniejszej grupie osób niż konieczność ich pobierania. Nie mniej wiele osób stanie przed koniecznością zmian na bazie z pominięciem interfejsu użytkownika. Są to najbardziej niebezpieczne operacje wymagające największej dozy wyobraźni i wiedzy o bazie. W wielu systemach zmiany mogą okazać się nie odwracalne i tylko kopia zapasowa może nas uratować.

Pracując przy bazach danych powinno się należeć do grupy osób, które robią kopie zapasowe. Jeśli tak nie jest prędzej czy później się to zemści.

Ze względu na krzyżowanie się tego tematu z językiem tworzenia struktury baz danych kilka rzeczy zostanie w tej części kursu omówionych na zasadach komentarza a ich głębsze wyjaśnienie znajdzie się w części do tego przeznaczonej.

Tak naprawdę baza rozróżnia dwie operacje modyfikacji danych wstawianie i usuwanie. Przy czym aktualizacja danych jest zoptymalizowanym procesem usuwania i wstawiania. W takiej właśnie kolejności zostaną omówione te zagadnienia w poniższym kursie.