Język SQL - Wkuwanko.pl

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={tKROTKA(X): ( rR) 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={tR: 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 / CD(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 XY nazywamy złączeniem tych relacji
T=R
S
wtw gdy
T={tKROTKA(XY): 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={tKROTKA(U-X): dla każdego sKROTKA(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ć:
IN
Inna zależność: ocena zależy od przedmiotu i od studenta:
IPO
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: XY gdzie X,YU. 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 XY jeżeli dla dwóch krotek r1,r2R :
(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,XU.
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: YX  XY F+
(zwrotność)
F2: XY F+  XZYZ F+
(poszerzalność)
F3: XY F+  YZ F+  XZ 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: XY F+  YWZ F+  XWZ  F+
F5: XY F+  XZ F+  XYZ F+
F6: XYZ F+  XY F+  XZ 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={ PGS, GSP, PIO, GIPS, PGSE }
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 ={ PG, PS, GSP, PIO, GIP, PE }
bo
PGS  PG i PS
GIPS  GIP i GIS
w zależnościach PGS i PGSE powtarza się GS zatem
PGS  PGSE  PE
F02 ={ PG, PS, GSP, PIO, GIS, PE }
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 KU nazywamy kluczem (key) schematu R wtw gdy zbiór ten spełnia następujące
warunki:
a) KU  F+
(jednoznaczna identyfikowalność)
Od klucza muszą być uzależnione funkcyjnie wszystkie atrybuty należące do zbioru U.
b) XU  F+  (XK)
(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}, {INAK, IPO} )
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}, {INAK, IPO} )
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 AU 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}, {WAPD, PD}
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) WAPD – 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) PD – 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 YU rozłączne z X iż oraz takie, że:
XYF+
YXF+
YZF+
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 ZU 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