Podstawy projektowania i implementacji baz danych

Podstawy projektowania i
implementacji baz danych
informatyka +
2
Bazy danych wprowadzenie
informatyka +
3
Dane
• Liczby, znaki, symbole (i cokolwiek
innego) zapisane w celu ich
przetwarzania
• 15,’ Ala’,’12-09-1987’, /…/---/…/,
‘Warszawa’,
To są jakieś dane
Tylko do końca nie wiemy
co one znaczą
Wniosek :
Dane bez uporządkowania i
bez umiejętności ich
interpretacji to najczęściej
„ŚMIETNIK”
informatyka +
4
Informacja
Trudno przytoczyć jedną definicję pojęcia informacja
Informacja to taki czynnik, któremu
człowiek może przypisać określony
sens (znaczenie), aby móc ją
wykorzystywać do różnych celów
Informacje możemy „zdobywać” dzięki
przetwarzaniu i interpretacji danych .
informatyka +
5
Wiedza
Podobnie jak w przypadku informacji – trudno jest
jednoznacznie zdefiniować pojęcie wiedza
Tak definiował to pojecie Platon :
„ogół wiarygodnych informacji o
rzeczywistości wraz z umiejętnością
ich wykorzystywania”
Proszę zwrócić uwagę na fakt, że wiedza to,
miedzy innymi, umiejętność wykorzystania
informacji .
informatyka +
6
Społeczeństwo informacyjne 1
I znów będziemy mieli problem z jednoznacznym
zdefiniowaniem pojęcia społeczeństwo informacyjne
Społeczeństwo charakteryzujące się
przygotowaniem i zdolnością do
użytkowania systemów
informatycznych, skomputeryzowane i
wykorzystujące usługi telekomunikacji
do przesyłania i zdalnego przetwarzania
informacji”
(I Kongres Informatyki Polskiej, 1994)
Wszystko wskazuje na to, że przyszłość należeć będzie
do społeczeństw informacyjnych
informatyka +
7
Społeczeństwo informacyjne 2
Umiejętność korzystania ze zgromadzonych danych
jest jedną z podstawowych cech społeczeństwa
informacyjnego.
informatyka +
8
Podsumowanie części 1
Dane -
powstają na różnych etapach działalności
gromadzimy dane w celu ich późniejszego wykorzystania
przechowując dane należy zapewnić określony porządek
Informacje -
Wiedza
-
często powstaje w wyniku przetwarzania
i interpretacji danych
to miedzy innymi umiejętność
wykorzystania informacji
Społeczeństwo informacyjne
- to także my
informatyka +
Wiedza
9
Porozmawiajmy o danych 1
Uczeń
Przedmiot
Nauczyciel
Data
Ocena
Zosia Nowak
3
15-03-2009
Jak widać - dane bez określonego porządku to chaos … i nie ma z
takich danych korzyści
Powoli zbiór danych staje się bardziej przejrzysty
+
Jeszcze trochę pracy i powinnoinformatyka
być dobrze
informatyka +
10
10
Porozmawiajmy o danych 2
Gromadzenie danych musi
zapewnić porządek bo w
przeciwnym razie nie damy
sobie rady w sytuacji gdy
danych będzie bardzo dużo.
Jak sprawnie gromadzić dane zachowując możliwość ich
wykorzystania ?
informatyka +
11
Bazy danych 1
Nauczyciel : Maria Bryła
Nauczyciel: : Józef
Jan Powaga
Nauczyciel
Ostry
Przedmiot :Fizyka
Przedmiot
:Matematyka
Przedmiot :Informatyka
Rodzaj
: Sprawdzian
Rodzaj
Sprawdzian
Rodzaj
: :Sprawdzian
Data wyst. : 17-05-2009
Data
wyst.
:
17-05-2009
Data wyst. : 17-05-2009
Ocena
:3
Ocena
:
5
Ocena
:2
Nazwisko
Lisek
Nazwisko :: Kotek
Sarenka
Imię
:
Jasio
Piotr
Imię
: Zosia
Data
11-02-1991
Data ur.
ur. :: 07-11-1991
11-11-1991
Pesel
:: 07111134498
91021134498
Pesel
91111134498
Kod
96-987
Kod poczt:
poczt: 26-987
26-987
Miasto
:
Sopot
Miasto : Opole
Poznań
Ulica
:: Klonowa
12/8
Długa
62/8
Ulica
Osinowa
22/8
Dane gromadzimy w bazach danych
Baza danych
informatyka +
12
Bazy danych 2
Baza danych to zbiór danych
zapisanych w ściśle określony sposób w
strukturach odpowiadających
założonemu modelowi danych.
Aktualnie najczęściej wykorzystuje się bazy danych
oparte na relacyjnym modelu danych
informatyka +
13
Podsumowanie
Dane opisują pewne fakty i zdarzenia
Gromadzimy dane w celu ich późniejszego wykorzystania
Gromadzenie danych bez określonego porządku jest bezsensowne
Dane gromadzimy w bazach danych
Bardzo wiele codziennych czynności związanych jest z korzystaniem
z baz danych
informatyka +
14
Relacyjny model danych 1
Sposób modelowania danych w którym
podstawowym pojęciem jest tabela.
Relacja jest pojęciem matematycznym (z
dziedziny teorii zbiorów) i cały model
relacyjny jest doskonale opisany przez
matematyków.
Dobra interpretacja matematyczna
pozwoliła zrealizować dobre
oprogramowanie obsługujące
relacyjne bazy danych.
informatyka +
15
Wybrane cechy modelu relacyjnego 1
Wszystkie wartości zapisywane w tabelach
oparte są na prostych typach danych(brak
struktur złożonych )
Nazwisko
Imię
Adres
Ulica
Nowa
33/21
Kot
Jasio
Kod
12-098
Miasto
Opole
Lis
Hania
65-987
Cicha 17/2 Sopot
Żuk
Piotrek
33-093
Miła 4/3
Gdynia
Języki obce
Rodzeństwo
Angielski,
francuski,
hiszpański
Angielski,
niemiecki
Nie zna
brat Staś, siostra
Mariola
brak
brat Jaś,
brat Staś, siostra
Hania
Powyższa tabela nie spełnia tej cechy - w dalszej części
pokażemy jak można ten problem rozwiązać
informatyka +
16
Wybrane cechy modelu relacyjnego 2
Wszystkie dane w bazie relacyjnej przedstawione są
w formie dwuwymiarowych tabel zwanych relacjami
Płyty
Numer
Nazwa albumu
Rok
wydania
Nazwa
zespołu
1
Kwiaty polskie
1969
Akwarele
2
Help
1967
The Beatles
3
Mrowisko
1971
Klan
4
Rubikone
2009
Piotr Rubik
5
Hellwood
2009
Hunter
informatyka +
17
Wybrane cechy modelu relacyjnego 3
Wszystkie operacje wykonywane są w oparciu o logikę
bez względu na położenie wiersza w tabeli
Ponieważ
w
modelu
relacyjnym kolejność kolumn
i wierszy nie ma żadnego
znaczenia - to widoczne
trzy
postaci
tabel
są
identyczne i można z nich
pobrać dokładnie te same
informacje
informatyka +
18
Wybrane cechy modelu relacyjnego 4
W tabeli musi istnieć kolumna lub zbiór kolumn o
wartościach niepowtarzalnych, pozwalający odnaleźć
konkretny wiersz.
Zaznaczone wiersze są
nierozróżnialne,
Tak naprawdę nie wiadomo czy
jest to pomyłka czy też opis
dwóch różnych osób
Nazwisko
Imię
DataUr.
Nowak
Jan
15-07-1992
Nowak
Piotr
16-10-1992
Kowalski
Jan
22-11-1992
Kowalski
Jan
22-11-1992
Piskorska
Beata
03-05-1992
Pesel
Nazwisko
Imię
DataUr.
92071598712
Nowak
Jan
15-07-1992
92101675643
Nowak
Piotr
16-10-1992
92112287965
Kowalski
Jan
22-11-1992
92112233562
Kowalski
Jan
22-11-1992
92050322411
Piskorska
Beata
03-05-1992
Dodatkowa kolumna „Pesel”
– umożliwiła rozróżnienie
dwóch osób. Pesel może
być uznany za klucz
podstawowy.
Taką kolumnę (lub zbiór) nazywamy kluczem podstawowym
(ang. primary key)
informatyka +
19
Relacyjny model danych 2
Projekt bazy danych, opartej na
modelu relacyjnym, polega na
opisaniu pewnej dziedziny życia za
pomocą wielu tabel
Każda tabela opisuje jeden rodzaj
obiektów (np. uczeń, klient, książka)
lub zdarzeń (np. wystawiona ocena,
wykonany przelew, wizyta lekarska)
Projektując bazę danych zapewnia się możliwość łączenia ze
sobą danych zawartych w różnych tabelach.
Więcej o projektowaniu relacyjnej bazy danych w dalszej części
wykładu
informatyka +
20
Tabela relacyjna
Sztuczny klucz
podstawowy
Iducznia
Cechy tabeli relacyjnej
Uczniowie
Nazwisko
Imie
Data_ur
Pesel
1
Nowak
Jan
11.09.1991
91091145654
2
Rybak
Zofia
12.11.1991
91111256744
3
Kowal
Stefan
21.02.1992
92022172138
4
Kozak
Jan
17.08.1992
92081711737
5
Pływak
Anna
04.02.1993
93020495571
I już mamy tabelę wraz z zawartością 
informatyka +
1.Przeznaczenie
2.Kolumny –
określają cechy
opisywanego
obiektu
3.Klucz
podstawowy
4.Wiersze –
suma cech
danego obiektu
21
Normalizacja – podstawa projektowania
Faktury
Idfaktury
Numer
Data_w
Netto
Vat
Firma
Nip
Ulica
Miasto
1
234/08
11.08.08
345.67
71.22
Wedel
1234652789
Nowa 3
Warszawa
2
43/08
12.08.08
763.00
167.00
Wedel
1234652789
Nowa 3
Warszawa
3
01/2008
15.08.08
322.00
68.65
Złotex
6573298722
Miła 7
Sopot
4
11.08/1
22.09.08
100.00
22.00
Koral
5582998721
Dobra 1
Opole
5
34w/08
28.09.08
882.00
187.00
Wedel
1234652789
Nowa 3
Warszawa
6
987/08
02.10.08
250.55
58.12
Złotex
6573298722
Miła 7
Sopot
7
002.08
11.10.08
891.00
201.15
Złotex
6573298722
Miła 7
Sopot
Redundancja!!!!!!!!!!!!!!
I co tutaj nie gra 
informatyka +
22
Normalizacja – podstawa projektowania
Faktury
Idfaktury
Numer
Data_w
Netto
Vat
1
234/08
11.08.08
345.67
71.22
2
43/08
12.08.08
763.00
3
01/2008
15.08.08
4
11.08/1
5
Klucz obcy
Idfirmy
1
1
Wedel
1234652789
Nowa 3
Warszawa
167.00
1
1
Wedel
1234652789
Nowa 3
Warszawa
322.00
68.65
2
Złotex
6573298722
Miła 7
Sopot
22.09.08
100.00
22.00
2
3
3
Koral
5582998721
Dobra
Warszawa
34w/08
28.09.08
882.00
187.00
1
1
Wedel
1234652789
Nowa 3
Warszawa
6
987/08
02.10.08
250.55
58.12
2
2
Złotex
6573298722
Miła 7
Sopot
7
002.08
11.10.08
891.00
201.15
2
2
Złotex
6573298722
Miła 7
Sopot
Firmy
IdFirmy
Firma
Nip
Ulica
Miasto
1
Wedel
1234652789
Nowa 3
Warszawa
2
Złotex
6573298722
Miła 7
Sopot
3
Koral
5582998721
Dobra 1
Opole
informatyka +
23
Podsumowanie
Model relacyjny opiera się na pojęciu tabeli
Każda tabela musi posiadać klucz podstawowy
W modelu relacyjnym nieistotna jest kolejność
kolumn i wierszy
Baza danych oparta na modelu relacyjnym
składa się z wielu tabel opisujących pewną
dziedzinę życia.
Bazy relacyjne są aktualnie najbardziej
rozpowszechnione.
informatyka +
24
Plan prezentacji
1. Kilka definicji na dobry początek.
2. Dane i bazy danych.
3. Podstawy relacyjnego modelu danych.
4. Rozważania o tabeli.
5. Modelowanie z wykorzystaniem tabel relacyjnych.
6. Problemy i anomalie związane z gromadzeniem danych w
tabelach.
7. Systemy Zarządzania Bazami Danych.
8. Spójność i integralność danych.
9. Od rozkładu jazdy do bankowości internetowej.
10.Podsumowanie wykładu – pytania.
informatyka +
25
Przykładowy projekt bazy danych
Schemat bazy danych do rejestrowania ocen uczniów
informatyka +
26
Przykładowe fragmenty baz danych
Rejestr wypożyczeń książek
Tabela opisująca
wypożyczenia
książek
Powiązanie
pomiędzy
tabelami
Tabela
słownikowa
Powiązanie
pomiędzy
tabelami
Powiązanie
pomiędzy
tabelami
Tabela opisująca
książki
Tabela opisująca
osoby
informatyka +
27
Analiza pewnego problemu 1
Wyobraźmy sobie, że w pewnej bazie
danych istnieje tabela o nazwie „Klienci” o
strukturze pokazanej na rysunku obok
Przykładowa zawartość takiej tabeli mogłaby wyglądać tak
jak na rysunku poniżej
Proszę zwrócić uwagę na fakt, że jak brak pewnych danych to
w tabeli przechowywana jest specyficzna wartość null
informatyka +
28
Analiza pewnego problemu 2
W trakcie eksploatacji naszej przykładowej bazy danych wyniknął problem,
ponieważ użytkownicy bazy danych chcieliby dodatkowo przechowywać
dane o numerze telefonu komórkowego.
Rozwiązaniem problemu mogłoby być dodanie do tabeli Klienci
dodatkowej kolumny TelefonKomorkowy – tak jak poniżej
Tak mogłaby wyglądać zmodyfikowana
tabela
… a tak zawartość tej tabeli
informatyka +
29
Analiza pewnego problemu 3
… ale czy mamy pewność, że w trakcie dalszej eksploatacji
tej bazy danych nie będzie potrzeby dodawania kolejnych
kolumn np. żeby zapisać więcej niż jeden numer telefonu
albo adres strony www, numer faksu (… a może dwa
numery), numer GG … itd.
… a może chwila zastanowienia i
rozwiązać ten problem raz a dobrze
???
informatyka +
30
Analiza pewnego problemu 4
Problem rozwiążemy w trzech krokach :
1.Utworzymy tabelę słownikową o
nazwie „RodzajeKontaktow”
2. Z tabeli „Klienci”
usuniemy kolumny
opisujące numery telefonów
itp.
3.Utworzymy nową tabelę (tzw.
tabelę asocjacyjną) o nazwie
„KontaktyKlienta”
informatyka +
31
Analiza pewnego problemu 5
Model bazy danych
informatyka +
32
Analiza pewnego problemu 6
Przykładowa zawartość tabel (RodzajeKontaktow)
Tabele podobnego typu nazywamy
tabelami słownikowymi.
Jeżeli będziemy dodatkowo
potrzebowali przechowywać w
bazie danych informacje o
numerach Gadu Gadu i adresy
stron WWW – to wystarczy
dopisać kolejne wiersze do tabeli
informatyka +
33
Analiza pewnego problemu 7
Przykładowa zawartość tabel (Klienci)
W tabeli „Klienci” nie zapisujemy teraz danych o numerach
telefonów, adresach e-mail itp.
Dodatkowo uzyskujemy jeszcze jedna korzyść – w sytuacji
gdy dany klient nie ma telefonu lub innego środka łączności,
nie musimy przechowywać w tabeli wartości null.
informatyka +
34
Analiza pewnego problemu 8
Przykładowa zawartość tabel (KontaktyKlienta)
Tabele tego typu nazywamy tabelą powiązań (asocjacyjną)
Dane zawarte w tabeli KontaktyKlienta wymagają
interpretacji, żeby stały się czytelne
informatyka +
35
Analiza pewnego problemu 9
Poniżej postać danych z poprzedniego slajdu, przekształcona
do bardziej czytelnej postaci
Dzięki kluczom obcym w tabeli KontaktyKlienta mogliśmy
powiązać dane zapisane w różnych tabelach
informatyka +
36
Podsumowanie
Za pomocą dwuwymiarowych tabel opisujemy wybrany
fragment rzeczywistości (bank, szkoła , kolekcja płyt)
Tabele relacyjne mogą opisywać :
•Obiekty rzeczywiste (uczniowie, nauczyciele, klasy)
•Słowniki pojęć (przedmioty, rodzaje ocen)
•Zdarzenia i powiązania (wystawione oceny)
Powiązanie danych zapisanych w
różnych tabelach osiągamy dzięki
parze kluczy :
klucz obcy --- klucz podstawowy
informatyka +
37
Problemy gromadzenia danych w tabelach 1
Problem 1 : Gromadzenie danych w tabeli nie może się
odbywać bez reguł i ograniczeń – nie wystarczy samo
nazwanie kolumn
Pesel
Nazwisko
Imię
DataUrodzenia
Płeć
Wiek
92092256787 Kotek
Janina 1992-09-22
Kobieta
17
921105au34
Lisek
Piotr
1992-07-21
Kotek
33
Wiktor
23
Lis
8 maj 91
Chłopak
OK
Jak widać na powyższym przykładzie – w tabeli bardzo łatwo
może zapanować totalny bałagan, choćby z tego powodu, że
to ludzie wprowadzają dane, a człowiek jest omylny.
Bazy danych powinny posiadać mechanizmy ułatwiające
wymuszanie poprawności zapisywanych danych
informatyka +
38
Problemy gromadzenia danych w tabelach 3
Kilka słów o przedstawionych problemach
Pesel
Nazwisko
Imię
DataUrodzenia
Płeć
Wiek
92092256787 Kotek
Janina 1992-09-22
Kobieta
17
921105au34
Lisek
Piotr
1992-39-42
Kotek
33
Wiktor
23
Lis
8 maj 91
Chłopak
OK
1. Nazwa kolumny nie gwarantuje zapisywania w niej
właściwych danych
2. Gdy mamy zapisane błędne dane – baza danych traci
sens.
3. W powyższym przykładzie – numer Pesel powinien być
zależny od daty urodzenia
informatyka +
39
Problemy gromadzenia danych w tabelach 4
Problem 2: Problemy i anomalie związane z zapisywaniem
danych
W powyższej, przykładowej, tabeli mamy cały szereg
problemów, które mogą wyniknąć w nieprawidłowo
zabezpieczonej bazie danych
informatyka +
40
Problemy gromadzenia danych w tabelach 5
Problem 2: Problemy i anomalie związane z zapisywaniem
danych
1. Czy Jan Kotek i Kotek Jan – to ta sama osoba????
2. Czy Daria Miła mieszka na ulicy Naftowej czy Benzynowej ???
3. Czy Sprawdzian i Sprawdz. to ten sam rodzaj oceny????
4. Czy Historia i Chistoria (ale wtyd – ale zdarzyć się może) to ten
sam przedmiot ?????
informatyka +
41
System Zarządzania Bazą Danych 1
Systemem Zarządzania Bazami Danych nazywamy
specjalistyczne oprogramowanie umożliwiające tworzenie baz
danych oraz ich eksploatację
SZBD powinien(miedzy innymi) umożliwiać :
•Definiowanie obiektów bazy danych
•Manipulowanie danymi
•Generowanie zapytań
•Zapewnienie spójności i integralności danych
informatyka +
42
System Zarządzania Bazą Danych 2
Przykłady SZBD :
MS SQL Server 2008
Oracle
MySQL
Access
DB2
… i wiele, wiele innych
informatyka +
43
System Zarządzania Bazą Danych 2
Jednym z najważniejszych zadań stojących przed SZBD jest
zapewnienie spójności i integralności danych
SZBD dostarczają szereg mechanizmów służących
zapewnieniu poprawności przechowywanych danych
informatyka +
44
System Zarządzania Bazą Danych 2
Rodzaje reguł i ograniczeń
•Deklaracja typu
•Definicje kluczy
•Reguły poprawności dla kolumny
•Reguły poprawności dla wiersza
•Reguły integralności referencyjnej
Poszczególne typy reguł zostaną omówione w dalszej
części wykładu
informatyka +
45
Spójność i integralność danych
Jednym z najistotniejszych elementów Systemów Zarządzania
Bazami Danych są mechanizmy zapewnienia spójności i
integralności danych przechowywanych w bazie
Podstawowe sposoby zapewnienia integralności danych
•Deklaracja typu
•Deklaracje kluczy
•Reguły poprawności dla kolumny
•Reguły poprawności dla wiersza
•Reguły integralności referencyjnej
informatyka +
46
Deklaracja typu 1
W tabelach relacyjnych przechowujemy dane różnego typu
(liczby, teksty, znaki, daty …)
Każda kolumna w tabeli musi mieć określony typ
przechowywanych danych
Deklaracja typu jest pierwszym
sposobem zapewnienia
poprawności danych – w ujęciu
matematycznym jest to określenie
dziedziny wartości dla kolumny
informatyka +
47
Deklaracja typu 2
Przykładowe typy danych w SQL Server 2008
Dla danych znakowych
•char(n) - ciąg n znaków o stałej długości (np. jeżeli
kolumna ma określony typ char(25) a wpiszemy
słowo „kot” – to i tak zostanie ono zapisane za
pomocą 25 znaków – uzupełnione spacjami)
•varchar(n) – ciąg n znaków o zmiennej długości (np. jeżeli
kolumna ma określony typ varchar(25) a
wpiszemy słowo „kot” –zostanie ono zapisane
za pomocą 3 znaków)
•varchar(max) – ciąg znaków o zmiennej długości do 2 GB
informatyka +
48
Deklaracja typu 3
Przykładowe typy danych w SQL Server 2008
Pytanie :
Skoro typ char w porównaniu z varchar
wykorzystuje więcej pamięci do
zapisywania danych (uzupełnianie spacjami) –
to jakie korzyści możemy osiągnąć w
przypadku wykorzystania typu char
informatyka +
49
Deklaracja typu 4
Przykładowe typy danych w SQL Server 2008
Istnieją także odmiany podanych wcześniej
znakowych typów danych poprzedzone literka
„n”
• nchar (n)
• nvarchar(n)
• nvarchar(max)
Są to typy danych znakowych (zapisywanych
łącznie z informacją o stronie kodowej)
umożliwiające przechowywanie tekstów
używających znaków specyficznych dla
różnych języków
informatyka +
50
Deklaracja typu 5
Przykładowe typy danych w SQL Server 2008
Dla danych liczbowych – liczby całkowite
•tinyint- liczba całkowita z zakresu 0 ÷ 255 - przechowywana
za pomocą 1 bajtu
•smallint- liczba całkowita z zakresu -32768 ÷ 32767
przechowywana za pomocą 2 bajtów
•int- liczba całkowita z zakresu -2147483648 ÷ 2147483647
przechowywana za pomocą 4 bajtów
•bigint- liczba całkowita z zakresu
-9223372036854775808 ÷ 9223372036854775807
przechowywana za pomocą 8 bajtów
informatyka +
51
Deklaracja typu 5
Przykładowe typy danych w SQL Server 2008
Dla danych liczbowych – liczby z ułamkiem
•real , float - do zapisywania liczb
zmiennoprzecinkowych
•decimal, numeric - do zapisywania liczb
zmiennoprzecinkowych
o określonej precyzji
•money - do zapisywania liczb wyrażających
kwoty pieniężne
informatyka +
52
Deklaracja typu 6
Przykładowe typy danych w SQL Server 2008
Dla danych liczbowych – data i czas
•date- do zapisywania dat np. 2009-08-22
•time- do zapisywania czasu np. 19:22:07.2345644
•datetime - do zapisywania łącznie daty i czasu
np. 2009-08-22 19:22:07.2345644
informatyka +
53
Deklaracja typu 7
Przykładowe typy danych w SQL Server 2008
Dla danych liczbowych – typy różne
•bit- do zapisywania wartości logicznych (true, false lub 0,1)
•varbinary(n)- do zapisywania danych binarnych o długości
n bajtów
•varbinary(max) - do zapisywania danych binarnych o
długości do 2 GB (np. obrazy, dźwięki itp. )
•xml- do zapisywania dokumentów XML o długości do 2 GB
•Timestamp - specjalny znacznik który automatycznie
zmienia swoja wartość przy modyfikacji wiersza
informatyka +
54
Deklaracja typu 8
Krótkie podsumowanie
•Każda kolumna w tabeli musi mieć określony typ danych jaki
będzie w tej kolumnie zapisywany
•Decyzja o wyborze odpowiedniego typu danych jest
pierwszym etapem zapewnienia spójności danych
•Wybór typu jest równoznaczny z określeniem dziedziny
wartości dla danych zapisywanych w danej kolumnie
informatyka +
55
Deklaracja kluczy
W każdej tabeli relacyjnej powinien być zdefiniowany klucz
podstawowy – taka definicja zapewnia, ze każda wartość w
kolumnie klucza podstawowego musi przyjąć inną wartość
W SZBD istnieją mechanizmy nadające kolumnom klucza
podstawowego automatycznie unikalne wartości
(autonumeracja)
Można także wymusić unikalność kolumn, które nie są
kluczem podstawowym – klucze potencjalne
informatyka +
56
Reguły poprawności dla kolumny
Deklaracja typu określa dziedzinę wartości dla kolumny ale
często jest to dziedzina zbyt szeroka
Reguła poprawności dla kolumny jest wyrażeniem
logicznym ograniczającym dziedzinę do tych wartości, które
spełniają ten warunek
Przykład :
Numer Pesel (w tabeli Uczniowie) ma zadeklarowany typ danych
char(11) - czyli ciąg znaków o maksymalnej długości 11.
Powinniśmy wymusić, żeby to było dokładnie 11 znaków i mogą to
być tylko cyfry.
Taka definicja zapewni, że zapisywane w tabeli numery Pesel będą
poprawne (w tej części wymagań)
informatyka +
57
Reguły poprawności dla wiersza
Czasami występują logiczne zależności pomiędzy danymi
zapisanymi w różnych kolumnach (dla jednego wiersza)
Reguła poprawności dla wiersza jest wyrażeniem logicznym
ograniczającym dziedzinę do tych wartości, które spełniają
ten warunek .
Przykład :
Numer Pesel (w tabeli Uczniowie) jest logicznie powiązany z datą
urodzenia ucznia zapisaną w tej samej kolumnie.
Powinniśmy wymusić, żeby pierwsze sześć cyfr numeru Pesel
odpowiadało dacie urodzenia zapisanej w innej kolumnie
Taka definicja zapewni, że zapisywane w tabeli numery Pesel i daty
urodzenia będą logicznie poprawne
informatyka +
58
Reguły integralności referencyjnej
Integralność referencyjna określa poprawność logiczna
danych zapisanych w różnych tabelach
Klasycznym przykładem takich zależności jest para klucz
obcy – klucz podstawowy.
Przykład :
Kolumna iducznia w tabeli Uczniowie (jako klucz podstawowy) i kolumna
iducznia w tabeli Oceny (jako klucz obcy)
Powinniśmy wymusić, żeby wartości klucza obcego przyjmowały
tylko takie wartości, które występują w tabeli gdzie ta kolumna jest
kluczem podstawowym – zapewnia to odpowiednie powiązanie
danych zapisanych w różnych tabelach.
informatyka +
59
Podsumowanie
•Zapewnienie spójności i integralności danych jest
jednym z najważniejszych wyzwań stojących przed
twórcami baz danych
•Systemy Zarządzania Bazami Danych dostarczają
mechanizmy ułatwiajace realizacje tych zadań
•W trakcie zajęć warsztatowych przyjrzymy się jak te
mechanizmy działają w praktyce
informatyka +
60
Podstawy języka SQL
informatyka +
61
Krótka historia języka SQL – kroki milowe
1970 - publikacją E.F.Codda pt. A Relational Model of Data for
Large Shared Data Banks. ( pol. Relacyjny model danych dla
dużych współdzielonych banków danych).
1974- w IBM powstał język SEQUEL (ang. Structured English
Query Language – Stukturalny Angielski Język Zapytań)
1979 - firma ORACLE wypuściła na rynek pierwszy
komercyjny system zarządzania bazami danych oparty
o SQL.
… do dnia dzisiejszego trwa burzliwy rozwój tego języka
informatyka +
62
Standardy języka SQL
Krótka historia standardów języka SQL :
•1986: pierwszy standard SQL (SQL-86),
•1989: następny standard SQL (SQL-89),
•1992: wzbogacona wersja standardu (SQL-92 lub SQL 2),
•1999: standardu rozszerzonego o pewne cechy obiektowości
(SQL 3)
•2003: Kolejne rozszerzenie standardu (m.in. włączenie do
standardu języka XML) - SQL 4
•2006 : Niewielkie rozszerzenie standardu
•2008 : Kolejne niewielkie rozszerzenie standardu
informatyka +
63
Standardy języka SQL
Opracowywaniem i publikowanie standardów SQL zajmują się
organizacje :
ISO (ang. International Organization for Standarization)
ANSI (ang. American National Standards Institute).
Standard języka to wytyczne dla producentów Systemów
Zarządzania Bazami Danych
Pomimo istnienia standardów jezyka SQL
implementacje różnia się od siebie (nieznacznie)
informatyka +
–
rózne
64
Cechy języka SQL
SQL jest językiem IV generacji
…i
co z tego że IV generacji
SQL jest językiem deklaratywnym
… ?????????
W języku SQL deklarujemy co chcemy osiągnąć – bez
określania jak to należy wykonać
informatyka +
65
Cechy języka SQL
Język SQL dzielimy na trzy podstawowe części:
Język Definiowania Danych – DDL (ang. Data Definition
Language
Język Manipulacji Danymi – DML (ang. Data Manipulation
Language
Język Kontroli Danych – DCL (ang. Data Control Language)
informatyka +
66
Cechy języka SQL
Język Definiowania Danych – DDL (ang. Data Definition
Language
Polecenia :
•CREATE – definiowanie obiektów w bazie danych
•ALTER - modyfikowanie obiektów w bazie danych
•DROP - usuwanie obiektów z bazy danych
informatyka +
67
Cechy języka SQL
Przykład polecenia DDL :
CREATE TABLE Uczniowie
(
IdUcznia
int IDENTITY(1,1) NOT NULL,
Nazwisko
varchar(50) NOT NULL,
Imie
varchar(50) NOT NULL,
DataUrodzenia date NOT NULL,
CzyChlopak
bit NOT NULL,
Pesel
varchar(11) NULL,
CONSTRAINT PK_uczniowie PRIMARY KEY CLUSTERED
(IdUcznia ASC)
)
informatyka +
68
Cechy języka SQL
Język Manipulacji Danymi – DML (ang. Data Manipulation
Language
Polecenia :
•INSERT– wstawianie do tabeli nowych wierszy
•UPDATE - modyfikowanie wierszy w tabeli
•DELETE - usuwanie wierszy z tabeli
•MERGE - warunkowa modyfikacja danych w tabeli
•SELECT – pobieranie danych z tabel (zapytania)
informatyka +
69
Cechy języka SQL
Przykład polecenia DML :
INSERT INTO Uczniowie (Nazwisko, Imie, DataUrodzenia,
CzyChlopak, Pesel)
VALUES(‘Kot’, ‘Jan’, ‘1991-07-12’,’true’, ‘91071276538’)
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE CzyChlopak=true
ORDER BY nazwisko
informatyka +
70
Cechy języka SQL
Język Kontroli Danych – DCL (ang. Data Control Language)
Polecenia :
•GRANT– przydzielenie prawa do danych
•REVOKE – pozbawienie prawa do danych
•DENY - bezwarunkowe pozbawienie prawa do danych
informatyka +
71
Cechy języka SQL
Praca z wykorzystaniem SQL może być realizowana na
kilka sposobów :
•poprzez interaktywne zadawanie pytań do bazy (monitor),
•budowanie skryptów (zbioru wsadowo wykonywanych
zapytań w SQL),
•osadzanie kodu (pojedynczych zapytań i całych procedur)
SQL w innych językach programowania (na poziomie
aplikacji),
•procedur składowanych (na poziome bazy danych).
informatyka +
72
Przykładowa baza danych
informatyka +
73
Przykładowa baza danych
Pisanie zapytań w języku SQL wymaga
dobrej znajomości bazy danych do
której te zapytania się odnoszą
informatyka +
74
Podstawowe operacje realizowane
na modelu relacyjnym
Realizacja zapytań opiera się na trzech podstawowych
operacjach wykonywanych na modelu relacyjnym:
Operacja projekcji (zwana także rzutowaniem)
Operacja selekcji
Operacja łączenia
informatyka +
75
Podstawowe operacje realizowane na modelu
relacyjnym
Operacja projekcji
Tabela wynikowa po operacji
projekcji
Tabela
wyjściowa
Wykonanie operacji projekcji
informatyka +
76
Podstawowe operacje realizowane na modelu
relacyjnym
Operacja selekcji
Warunek selekcji
Idklasy=2
Tabela wynikowa
Tabela
wyjściowa
Wykonanie operacji selekcji
informatyka +
77
Podstawowe operacje realizowane na modelu
relacyjnym
Operacja łączenia
Wykonanie operacji łączenia
Tabela
wynikowa
informatyka +
Tabela
wyjściowa
Tabela
dołączania
78
Podstawowe operacje realizowane
na modelu relacyjnym
Przedstawione operacje wykonywane
na modelu relacyjnym są podstawa
realizacji zapytań
informatyka +
79
Polecenie SELECT- zapytania
proste
SELECT *
FROM Uczniowie
Przykładowy wynik
zapytania
Określona została , w opcji FROM, tabela z której
pobieramy dane a znaczek * powoduje dostarczenie do
wyniku zapytania wszystkich dostępnych w tabeli kolumn
informatyka +
80
Realizacja operacji projekcji
SELECT Nazwisko, Imie, Pesel, CzyChlopak
FROM Uczniowie
Przykładowy wynik
zapytania
Określona została , w opcji FROM, tabela z której
pobieramy dane i wymieniono liste kolumn, które maja się
pojawić w wyniku
informatyka +
81
Realizacja operacji projekcji i
selekcji
SELECT Nazwisko, Imie, Pesel, CzyChlopak
FROM Uczniowie
Przykładowy wynik
WHERE CzyChlopak=1
zapytania
W klauzuli WHERE dodano warunek selekcji
informatyka +
82
Porządkowanie wyniku zapytania
SELECT Nazwisko, Imie, Pesel, Idklasy
FROM Uczniowie
Przykładowy wynik
WHERE Idklasy=1 OR Idklasy=2
zapytania
ORDER BY Idklasy ASC, Nazwisko DESC
Dodano klauzule ORDER BY – realizującą porządkowanie
wyniku zapytania (opcja ASC – rosnąco, DESC – malejąco)
informatyka +
83
Przekształcanie danych
SELECT Nazwisko, Imie, Pesel,
CASE CzyChlopak
WHEN 1 THEN ‘Mężczyzna’
ELSE ‘Kobieta’
END as Płeć
FROM Uczniowie
WHERE Idklasy=2
Przykładowy wynik
zapytania
Kolumna o nazwie Płeć powstała w wyniku przekształcenia
wartości zapisanych w tabeli
informatyka +
84
Operacja łączenia
SELECT Uczniowie.* , Klasy.*
FROM Uczniowie JOIN Klasy
ON Uczniowie.Idklasy=Klasy.Idklasy
Przykładowy wynik
zapytania
Do wiersza opisującego ucznia został dołaczony
odpowiedni wiersz z tabeli klasy
informatyka +
85
Operacja łączenia
SELECT Uczniowie.Nazwisko, Uczniowie.Imie,
CASE CzyChlopak
WHEN 1 THEN ‘Mężczyzna’
ELSE ‘Kobieta’
Przykładowy wynik
END as Płeć,
zapytania
Klasy.Nazwa, Klasy.RokSzkolny
FROM Uczniowie JOIN Klasy ON Uczniowie.Idklasy=Klasy.Idklasy
WHERE YEAR(Uczniowie.DataUrodzenia)=1992
ORDER BY Płeć, Nazwisko DESC
Zapytanie wykonujące operacje projekcji, selekcji, łączenia,
przekształca dane i porządkuje wynik zapytania
informatyka +
86
Rozwiązanie problemu
Chcemy napisać zapytanie, które przygotuje
wykaz uczniów (nazwisko i imię) oraz dane
nauczyciela (nazwisko i imię oraz stopień
zawodowy), który wystawił ocenę i datę
wystawienia oceny tym uczniom, którzy w
roku 2009 otrzymali z fizyki ocenę 5, wynik
uporządkować malejąco według daty
wystawienia oceny.
informatyka +
87
Rozwiązanie problemu
SELECT Uczniowie.Nazwisko+’ ‘+Uczniowie.Imie AS Uczen,
Nauczyciele.Nazwisko+’ ‘ Nauczyciele.Imie AS Nauczyciel,
Oceny.DataWystawienia, Oceny.Ocena
FROM Uczniowie JOIN Oceny ON
Uczniowie.Iducznia=Oceny.IdUcznia
JOIN Nauczyciele ON
Nauczyciele.IdNauczyciela=Oceny.IdNauczyciela
JOIN Przedmioty ON
Oceny.Idprzedmiotu=Przedmioty.Idprzedmiotu
WHERE YEAR(DataWystawienia) =2009 AND Ocena=5 AND
Przedmioty.Nazwa=’Fizyka’
ORDER BY DataWystawienia DESC
informatyka +
88
Rozwiązanie problemu
Przykładowy wynik
zapytania
informatyka +
89
Złączenie zewnętrzne
Do tej pory, domyślnie, realizowaliśmy tzw. złączenie wewnętrzne
– czyli w wyniku zapytania pojawiały się tylko te wiersze dla
których spełniony był warunek złączenia
SQL umożliwia wykonanie złączenia zewnętrznego – czyli
umożliwia dołączenie do wyniku zapytania także te wiersze dla
których warunek złączenia nie jest spełniony
Złączenie zewnętrzne omówimy na przykładzie
informatyka +
90
Złączenie zewnętrzne
Przygotujemy zapytanie w którym będą wszyscy
uczniowie wraz z informacja kiedy otrzymali w lutym
roku 2009 ocenę mierną
SELECT Uczniowie.Nazwisko, Uczniowie.Imie,
Oceny.DataWystawienia, Ocena
FROM Uczniowie LEFT OUTER JOIN Oceny
ON Uczniowie.iducznia=Oceny.Iducznia
AND Oceny.Ocena=2
AND YEAR(DataWystawienia)=2009
AND MONTH(DataWystawienia)=2
informatyka +
91
Złączenie zewnętrzne
Przykładowy wynik
zapytania
informatyka +
92
Funkcje agregujące
Zapytania SQL mogą być także wykorzystane do wykonywania obliczeń
na podstawie danych zawartych w tabelach. Do tego celu służą funkcje
agregujące.
Język SQL udostępnia pięć podstawowych funkcji agregujących;
COUNT – oblicza ilość wierszy otrzymanych w wyniku zapytania,
SUM – sumuje zawartość kolumny (lub wyrażenia obliczonego na
podstawie danych) dla wszystkich wierszy w wyniku zapytania,
AVG – oblicza średnią arytmetyczną zawartości kolumny (lub wyrażenia
obliczonego na podstawie danych) dla wszystkich wierszy w wyniku
zapytania,
MIN – określa wartość minimalną dla kolumny w wyniku zapytania,
MAX – określa wartość maksymalną dla kolumny w wyniku zapytania.
informatyka +
93
Funkcje agregujące
SELECT COUNT(*) AS IluUczniow
FROM Uczniowie
SELECT COUNT(*) AS IluUczniow
FROM Uczniowie JOIN Klasy
ON Uczniowie.idklasy=Klasy.idklasy
WHERE Klasy.Nazwa='IIa'
Funkcja agregująca użyta w zapytaniu powoduje, ze w
wyniku otrzymujemy jeden wiersz z wynikiem działania
funkcji agregującej
informatyka +
94
Funkcje agregujące i grupowanie danych
•SELECT Klasy.Nazwa,
•
COUNT(*) AS IluUczniow
•FROM Uczniowie JOIN Klasy ON Uczniowie.idklasy=Klasy.idklasy
•GROUP BY Klasy.Nazwa
Wykorzystaliśmy klauzulę GROUP BY w celu „rozbicia”
działania funkcji agregującej dla wierszy zawierających tę
sama wartość dla kolumny Klasy.Nazwa
informatyka +
95
Funkcje agregujące i grupowanie danych
Lista uczniów z klasy IIa oraz ich średnią ocen otrzymanych w roku 2009
•SELECT Uczniowie.Nazwisko, Uczniowie.Imie, AVG(Oceny.Ocen) as Średnia
•FROM Uczniowie JOIN Oceny ON Uczniowie.Iducznia=Oceny.IdUcznia
•
JOIN Uczniowie.Idklasy=Klasy.Idklasy
•WHERE YEAR(Oceny.DataWystawienia)=2009 AND Klasy.Nazwa=’IIa’
•GROUP BY Uczniowie.Nazwisko, Uczniowie.Imie
•ORDER BY Średnia DESC
informatyka +
96
Funkcje agregujące i grupowanie danych
Lista uczniów z klasy IIa oraz ich średnią ocen otrzymanych w roku 2009
- tylko ci uczniowie których srednia przekracza 3.00
•SELECT Uczniowie.Nazwisko, Uczniowie.Imie, AVG(Oceny.Ocen) as Średnia
•FROM Uczniowie JOIN Oceny ON Uczniowie.Iducznia=Oceny.IdUcznia
•
JOIN Uczniowie.Idklasy=Klasy.Idklasy
•WHERE YEAR(Oceny.DataWystawienia)=2009 AND Klasy.Nazwa=’IIa’
•GROUP BY Uczniowie.Nazwisko, Uczniowie.Imie
•HAVING AVG(Oceny.Ocena) > 3.00
•ORDER BY Średnia
Klauzulę HAVING nazywamy opóźnionym warunkiem selekcji
informatyka +
97
Zapytania złożone
Polecenie SELECT języka SQL umożliwia zagnieżdżanie zapytań, czyli
wykorzystanie zapytania w wewnątrz innego zapytania.
Dzięki tej właściwości można za pomocą jednego polecenia wykonywać
bardzo złożone operacje na danych.
Omówimy to, chcąc przygotować listę uczniów (zawierającą nazwisko i
imię ucznia oraz nazwę klasy), którzy w roku 2009 nie otrzymali oceny
niedostatecznej z fizyki.
Należy zwrócić uwagę na fakt, że chcemy pobrać z bazy dane, które nie są
bezpośrednio w niej zapisane, bo jeżeli uczeń nie otrzymał oceny to w
bazie danych nie ma żadnego zapisu tego faktu.
Rozwiązując ten problem korzystamy z pewnych zależności logicznych.
informatyka +
98
Zapytania złożone
Pomyślmy o tym problemie jako o działaniu na następujących
zbiorach:
A – zbiór wszystkich uczniów,
B – zbiór uczniów, którzy otrzymali w roku 2009 ocenę
niedostateczną z fizyki,
C – poszukiwany zbiór uczniów, którzy w roku 2009 nie
otrzymali oceny niedostatecznej z fizyki.
Wyrażenie: C = A – B opisuje rozwiązanie naszego problemu,
czyli poszukiwany zbiór możemy otrzymać jako różnicę dwóch
innych zbiorów.
informatyka +
99
Zapytania złożone
Zapytanie tworzące
zbiór A
SELECT Uczniowie.Nazwisko, Uczniowie.Imie, Klasy.Nazwa,
FROM Uczniowie JOIN Klasy
ON Uczniowie.idklasy=Klasy.idklasy
Zapytanie tworzące
zbiór B
WHERE Iducznia NOT IN
(SELECT DISTINCT Iducznia
FROM Oceny JOIN Przedmioty
ON Oceny.Idprzedmiotu=Przedmioty.Idprzedmiotu
WHERE Przedmioty.Nazwa=’Fizyka’ AND
YEAR(Oceny.DataWystawienia)=2009 AND
Oceny.Ocena=2)
Warunek różnicy
zbiorów
informatyka +
100
Zapytania złożone
Pokazaliśmy jeden przykład zapytania złożonego,
pokazujący
dodatkowe
możliwości,
jakimi
dysponujemy przy pisaniu zapytań do baz danych z
wykorzystaniem języka SQL.
Trudno wymienić wszystkie sytuacje, w których
można wykorzystywać podzapytania ale jest jedna
zasada ogólna:
Podzapytanie może być wykorzystane wszędzie
tam, gdzie ma sens wynik tego podzapytania
informatyka +
101
Co jeszcze potrafię??
Tworzenie wyniku zapytania w języku XML
SELECT Klasy.Nazwa, Klasy.RokSzkolny
<ListaKlas>
FROM Klasy
<Klasy>
FOR XML AUTO,ROOT('ListaKlas'),ELEMENTS
<Nazwa>Ia</Nazwa>
<RokSzkolny>2008/2009</RokSzkolny>
</Klasy>
<Klasy>
<Nazwa>IIa</Nazwa>
<RokSzkolny>2008/2009</RokSzkolny>
</Klasy>
<Klasy>
<Nazwa>Ib</Nazwa>
<RokSzkolny>2008/2009</RokSzkolny>
</Klasy>
<Klasy>
<Nazwa>IIb</Nazwa>
<RokSzkolny>2008/2009</RokSzkolny>
</Klasy>
</ListaKlas>
informatyka +
102
Co jeszcze potrafię??
Operacje na zbiorach danych z wykorzystaniem operatorów
UNION, EXCEPT i INTERSECT
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE idklasy=1
UNION
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE idklasy=2
informatyka +
103
Co jeszcze potrafię??
Zapytanie, które przygotuje listę uczniów z klasy o id klasy=1 za
wyjątkiem tych, którzy urodzili się w marcu
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE idklasy=1
EXCEPT
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE MONTH(DataUrodzenia)=3
informatyka +
104
Co jeszcze potrafię??
Zapytanie, które przygotuje listę uczniów urodzonych w marcu,
których nazwisko zaczyna się na literę K
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE MONTH(DataUrodzenia)=3
INTERSECT
SELECT Nazwisko, Imie, Pesel
FROM Uczniowie
WHERE nazwisko LIKE ‘K%’
informatyka +
105
Co jeszcze potrafię??
Tabele przestawne
SELECT *
FROM
(
SELECT Przedmioty.Nazwa as Przedmiot,
Klasy.Nazwa as Klasa,
Oceny.Ocena
FROM Klasy Join Uczniowie ON Klasy.idklasy=Uczniowie.idklasy
Join Oceny ON Oceny.iducznia=Uczniowie.iducznia
Join Przedmioty ON Przedmioty.idprzedmiotu=Oceny.idprzedmiotu
) as A
PIVOT
(AVG(Ocena) FOR Klasa in ([Ia],[IIa],[IIc])) as B
informatyka +
106
Co jeszcze potrafię??
Tabele przestawne
informatyka +
107
Podsumowanie
• Przedstawiliśmy podstawowe możliwości
języka SQL a w szczególności polecenia SELECT
• Standard języka SQL ciągle jest rozwijany i
wzbogacany o nowe możliwości
informatyka +
108
Tabele tymczasowe i
widoki
informatyka +
109
Tabele „inline”
Zapytanie SELECT tworzy tabelę wynikową, która może być
wykorzystana w innym zapytaniu.
select lp,numer,rok
from
(
select row_number() over (order by year(data_wyst) desc ) as lp,
numer,
year(data_wyst) as rok
from faktury
) tmp
where lp between 3 and 7
mgr inż. Andrzej Ptasznik
Wyrażenia tabelaryczne (CTE)
(widoki tymczasowe)
with wystawili as
(
select idwystawcy
from faktury
where year(data_wyst)=2006
)
select nazwa,nip
from klienci
where idklienta not in (select idwystawcy from wystawili)
mgr inż. Andrzej Ptasznik
Widoki.
Zapytanie SELECT zapisane w bazie danych
tworzy tabelę wirtualną, której zawartość jest
wynikiem zapisanego polecenia.
mgr inż. Andrzej Ptasznik
Widoki … kilka przykładów
create view v_klienci
as
select idklienta,nazwa,nip,idmiasta,kod,ulica,test
from dbo.klienci
create view v_klienci
with schemabinding, encryption
as
select idklienta,nazwa,nip,idmiasta,kod,ulica
from dbo.klienci
create view v_klienci
as
select idklienta,nazwa,nip,idmiasta,kod,ulica
from klienci
where idmiasta=1
with check option
mgr inż. Andrzej Ptasznik
Definicja bez opcji
Opcje powiązania ze
schematem bazy danych i
opcja szyfrująca
Opcja powiązania operacji
wstawiania danych z
warunkiem WHERE
Widoki – zastosowanie.
1. Uproszczenie schematu bazy danych.
2. Sterowanie uprawnieniami do danych.
3. Rozwiązywanie problemów
wydajnościowych
mgr inż. Andrzej Ptasznik
Funkcje tabelaryczne.
„Widoki sparametryzowane” – definiowane w
bazie danych funkcje zwracające tabelę.
Funkcja może posiadać parametry by wynik
zapytania uzależnić od wartości parametrów
wejściowych.
mgr inż. Andrzej Ptasznik
Funkcje tabelaryczne -przykład
create function f_klienci(@idmiasta int)
returns table
with schemabinding, ENCRYPTION
as
return
(select
idklienta,nazwa,nip,idmiasta,kod,ulica
from dbo.klienci
where idmiasta=@idmiasta)
select nazwa,nip,idmiasta
from dbo.f_klienci(1)
order by nazwa
Wywołanie
funkcji
mgr inż. Andrzej Ptasznik
Definicja funkcji
Wynik funkcji
Transact - SQL
informatyka +
117
• Nie jest to „pełny” język programowania - nie można
w nim napisać od podstaw poważnej aplikacji.
• Można przy pomocy T-SQL zaprogramować realizację
zadań po stronie serwera.
• W T-SQL osadzony jest standardowy język SQL tzn.
wszystkie polecenia języka SQL są integralną częścią
języka T-SQL
• Język T-SQL jest wykorzystywany do
programowania:
•
•
•
•
Procedur składowanych
Funkcji składowanych
Wyzwalaczy
Skryptów
Zmienne
•Wymaga jawnej deklaracji
•Nazwa zmiennej lokalnej musi rozpoczynać się
od znaku „@”
•Typy zmiennych mogą być takie – jak
wbudowane typy SQL Servera
Typy zmiennych
Typami zmiennych w języku T-SQL mogą być wszystkie
typy udostępniane przez SQL Server do deklaracji typu
kolumn.Np.
int,
Char
Varchar
Money
Datetime
Smallint
Itd..
Konwersje typów
Polecenie CAST:
CAST ( wyrażenie AS typ_danych )
Polecenie CONVERT:
CONVERT ( typ_danych [ ( rozmiar ) ] ,
wyrażenie [ , styl ] )
Konwersje typów-przykłady
PRINT CAST(12.45 as int)
PRINT CONVERT(int,12.45)
print CONVERT(char(24), GETDATE(), 9)
Zmienne - przykłady
• Declare @licznik int
• Declare @dane xml,
@nazwa varchar(100)
• Declare @@flaga1 bit,
@@flaga2 bit
Uwaga : SQL Server 2005 nie umożliwia inicjacji wartości
zmiennej w trakcie deklaracji
(jest to być możliwe w SQL Server 2008 :
np. Declare @ile int=8)
Tabele tymczasowe
•Nazwa lokalnej tabeli tymczasowej musi
rozpoczynać się od znaku „#”
•Nazwa globalnej tabeli tymczasowej musi
rozpoczynać się od znaków „##”
•Tabele tymczasowe tworzone są w bazie
danych „tempdb”
Tabele tymczasowe-przykłady
Select nazwa, symbol into #listaslownika
From kategorie
Create table ##tmp (idklienta int not null,
nazwa varchar(200))
Instrukcje sterujące – instrukcja warunkowa
•Język T-SQL udostępnia „klasyczną” istrukcję
warunkową:
IF Wyrażenie _logiczne
{ polecenie_SQL | blok_poleceń}
[ ELSE
{ polecenie_SQL | blok_poleceń} ]
Instrukcje sterujące – iteracje
•Język T-SQL udostępnia „klasyczną”
instrukcję iteracyjną:
WHILE Wyrażenie_logiczne
{ polecenie_SQL | Blok_poleceń }
[ BREAK ]
{ polecenie_SQL | Blok_poleceń }
[ CONTINUE ]
BREAK – BEZWARUNKOWO PRZERYWA DZIAŁANIE PĘTLI
CONTINUE- PRZERYWA PĘTLĘ I PRZECHODZI DO
SPRAWDZENIA WARUNKU KONTYNUACJI
Instrukcja podstawiania
Dwa rodzaje instrukcji podstawiania :
SET
np.( Set @i=@i+1)
np.(set @nip= (Select nip=nip
from klienci
where idklienta=321)
SELECT
np.( Select @i=@i+1)
np.(Select @nip=nip
from klienci
where idklienta=321)
Funkcja CASE
SELECT nazwisko,imie,
CASE plec
WHEN 0 THEN ‘KOBIETA'
WHEN ‘1' THEN ‘Mężczyzna'
ELSE Płeć nieokreślona‘
END
FROM Osoby
Obsługa błędów
SQL Server 2005 wprowadził do języka T-SQL
elementy strukturalnej obsługi wyjątków – zapewnia to
konstrukcja :
BEGIN TRY
Instrukcje
END TRY
BEGIN CATCH
Instrukcje obsługi błędów
END CATCH
Funkcje
•Funkcje konfiguracyjne
•Funkcje kursora
•Funkcje daty i czasu
•Funkcje matematyczne
•Funkcje metadanych
•Funkcje bezpieczeństwa
•Funkcje operujące na łańcuchach znaków
•Funkcje systemowe
•Funkcje statystyki systemowej
•Funkcje tekstu i obrazu
I to prawie wszystko o języku T-SQL
… reszta wynika z :
•Problemu
•Kontekstu
•Fantazji programisty 
Procedury, funkcje, wyzwalacze
informatyka +
134
Procedury składowane – to kod w języku
T-SQL (lub CLR)
wykonywany po stronie serwera.
Procedura składowana jest obiektem bazy
danych !!!
Interfejs „Form”
SQLServer
Środowisko
śieciowe
Wykonane
Wykonaj przelew z konta A na konto B
1.Zapytanie o stan konta A
2.Odpowiedź – stan Konta A
3.Rozpoczęcie transakcji
4.Odpowiedź na polecenie
5.Zmiejszenie stanu konta A
6.Odpowiedź na polecenie
7.Zwiekszenie stanu konta B
8.Odpowiedź na polecenie
9. Zakończenie transakcji
10.Odpowiedź na polecenie
A teraz inne podejście
????????????
Metoda
„WykonajPrzelew”
Iterfejs „Form”
Wykonane
Wykonaj przelew z konta A na konto B
SQL Server
Środowisko
śieciowe
1.Wykonaj metodę „…Przelew”
2.Odpowiedź z metody
Zastosowanie procedur składowanych :
1. Hermetyzacja „logiki bazy danych”
2. Realizacja zadań administracyjnych
3. Warstwa zabezpieczeń.
Definiowanie procedury
CREATE PROCEDURE ListaDostaw
;1
@data datetime,
@komunikat varchar(500) output
WITH ENCRYPTION,RECOMPILE
Nagłówek procedury
Lista parametrów
Opcje
AS
BEGIN TRY
SELECT Tytuly.nazwa,
ilosc,
cena
FROM Tytuly join DostawyPrasy
on Tytuly.idtytulu=DostawyPrasy.idtytulu
WHERE DataDostawy=@data
SET @komunikat=‘ Procedura wykonana poprawnie’
RETURN 0
END TRY
BEGIN CATCH
SET @komunikat=‘ Błąd zapytania’
RETURN 1
END CATCH
Blok procedury
Blok obsługi
wyjątków
Rodzaje procedur składowanych :
1. Procedury składowane użytkownika – definiowane przez
użytkowników bazy danych.
2. Procedury specjalne – procedury oznaczone prefixem
sp_ i utworzone w bazie systemowej master. (można ją
uruchamiać w kontekście dowolnej bazy danych)
3. Procedury systemowe – procedury oznaczone prefixem
sp_ dostarczone przez producenta systemu (na serwerze
są przechowywane w ukrytej systemowej bazie danych
Resource)
4. Tymczasowe procedury składowane (nazwa poprzedzona
symbolem # lub ##).
5. Rozszerzone procedury składowane – (kod w innych
językach – prefix xp_) – uznane za przestarzałe. Zostały
zastąpione możliwością pisania procedur w językach .Net
Rodzaje funkcji składowanych ;
1. Funkcje systemowe
2. Funkcje tabelaryczne
a)Funkcje typu „inline”
b)Złożone funkcje tabelaryczne
3. Funkcje skalarne
4. Agregujące funkcje użytkownika
Przykład funkcji tabelaryczne typu „inline”
CREATE FUNCTION ListaDostaw(@data datetime)
RETURNS Table
AS
return ( SELECT iddostawy,
ilosc,
cena,
idtytulu
FROM DostawyPrasy
WHERE DataDostawy=@data )
Wykorzystanie
SELECT Tytuly.nazwa,
ilosc,
cena
FROM Tytuly join dbo.ListaDostaw(‘20060912’) as ld
on ld.idtytulu=Tytuly.idtytulu
ORDER BY nazwa
Akademia Baz Danych
Przykład złożonej funkcji tabelarycznej
CREATE FUNCTION ListaDostaw(@data datetime)
RETURNS @wynik Table(iddostawy int,
ilosc int,
cena money,
idtytulu int)
AS
BEGIN
INSERT INTO @wynik(iddostawy,ilosc,cena,idtytulu)
SELECT iddostawy,
ilosc,
cena,
idtytulu
FROM DostawyPrasy
WHERE DataDostawy=@data
RETURN
END
Akademia Baz Danych
Przykład funkcji skalarnej
CREATE FUNCTION DataOstatniejDostawy(@idtytulu int)
RETURNS datetime
AS
BEGIN
Declare @wynik datetime
SELECT @wynik=MAX(DataDostawy)
FROM DostawyPrasy
WHERE idtytulu=@idtytulu
Return @wynik
END
Wykorzystanie:
SELECT Tytuly.nazwa,
dbo.DataOstatniejDostawy(idtytulu)
FROM tytuly
WHERE idcyklu=2
Wyzwalacze (triggers)
informatyka +
146
Wyzwalacze
Wyzwalacz, to specjalny typ procedury
składowanej, która jest wykonywana
automatycznie, jako reakcja na zdarzenie.
Wyzwalacze
Wyzwalacze można wykorzystać między innymi
do :
• złożonych warunków poprawności dla
kolumny lub wiersza
• implementacji złożonych zachowań dla
integralności referencyjnej
• Obliczania wartości kolumn
• Inspekcji działania użytkowników
Wyzwalacze
Wyzwalacze mogą wykonywać następujące
czynności:
• porównywać wersje danych z „przed” i „po ”
modyfikacji
• anulować nieprawidłowe modyfikacje
• odczytywać dane z innych tabel
• modyfikować inne tabele
• wykonywać procedury składowane
Rodzaje wyzwalaczy
Wyzwalacze DML (Data Manipulation Language)
Reagują na „zdarzenia” Insert, Update, Delete
Wyzwalacze DDL (Data Definition Language )
Reagują na „zdarzenia” CREATE,ALTER,DROP
Wyzwalacze DML
• Rodzaje wyzwalaczy DML
– Typ AFTER (po operacji DML)
– Typ INSTEAD OF (zamiast operacji DML)
• Wyzwalacz jest tworzony dla tabeli i jest związany z
jedną (bądź więcej) instrukcji modyfikujących
dane(INSERT, UPDATE lub DELETE)
Cechy wyzwalaczy
• Wyzwalacz jest wykonywany tylko raz dla danego
polecenia niezależnie od tego ile wierszy modyfikuje
te polecenie.
• Wyzwalacz jest integralna częścią transakcji związanej
z realizacją polecenia
• Jawne polecenie ROLLBACK usuwa skutki działania
wyzwalacza oraz polecenia które go wywołało.
Schemat działania wyzwalaczy
„AFTER”
Rozpoczęcie transakcji niejawnej (BEGIN TRANSACTION)
Polecenia DML (Insert |Update | Delete)
Realizacja polecenia DML (sprawdzanie reguł integralności)
Kod wyzwalacza
Zatwierdzenie transakcji niejawnej (COMMIT TRANSACTION)
Schemat działania wyzwalaczy „INSTEAD OF”
Rozpoczęcie transakcji niejawnej (BEGIN TRANSACTION)
Polecenia DML (Insert |Update | Delete)
Kod wyzwalacza
Zatwierdzenie transakcji niejawnej (COMMIT TRANSACTION)
Wyzwalacze DML
W trakcie działania wyzwalacza jest dostęp do
dwóch pseudotabel o nazwach Inserted i
Deleted, które udostępniają te wiersze które
zostały zmodyfikowane przez operacje, która
jest związana z uruchomionym wyzwalaczem.
Pseudo tabele Inserted i Deleted
Wyrażenie
Zawartość
tabeli Inserted
Zawartość
tabeli Deleted
INSERTS
ERT
UPDATE
Dodane
wierszesze
Pusta
Wiersze po
modyfikacji
wiersze
Pusta
Wiersze przed
modyfikacjąwi
ersze
Usunięte
wiersze
DELETE
Sekwencja działania wyzwalacza DML
Osoby
1. Dana jest tabela Osoby
2. Do tabeli zdefiniowano wyzwalacz, który
sprawdza czy po zmianie numeru Pesel jego
nowa wartość nie istnieje już w bazie danych
IdOsoby
Nazwisko
Imie
Pesel
1
Kot
Jan
23454567876
2
Lis
Piotr
3
Rybka
Anna
87565434322
2345456787
6
78121287565
3. Wykonujemy polecenie
UPDATE Osoby SET
Pesel=23454567876
WHERE idosoby=2
Deleted
IdOsoby
Nazwisko
Imie
Pesel
2
Lis
Piotr
87565434322
Inserted
4.Zostaje uruchomiony kod wyzwalacza
5.Wyzwalacz , wobec stwierdzenia powielenia
Numeru pesel odwołuje transakcję
IdOsoby
Nazwisko
Imie
Pesel
2
Lis
Piotr
23454567876
Kod wyzwalacza
CREATE TRIGGER TR_sprawdzanie_pesel
ON Osoby
AFTER Update
AS
If Update(pesel)
Begin
If EXISTS (SELECT *
FROM Inserted
WHERE pesel IN (SELECT pesel
FROM Osoby
WHERE
idosoby<>Inserted.Idosoby)
ROLLBACK
End
mgr inż.Andrzej Ptasznik
Wyzwalacze DDL
• Wyzwalacze działające w kontekście bazy
danych
• Wyzwalacze działające w kontekście serwera
Wyzwalacze typu DDL
Nowy typ wyzwalaczy dla języka definiowania danych
• Duże możliwości przy zabezpieczaniu bazy danych
przed przypadkowymi lub niedopuszczalnymi
modyfikacjami
• Możliwość ingerowania w proces logowania