Podstawy projektowania i implementacji baz danych

(1)

(2)

Podstawy projektowania i

implementacji baz danych

(3)

Bazy danych wprowadzenie

(4)

Dane

informatyka +

4 • 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

(5)

Informacja

informatyka +

5

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 .

(6)

Wiedza

informatyka +

6

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 .

(7)

Społeczeństwo informacyjne 1

informatyka +

7

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

(8)

Społeczeństwo informacyjne 2

informatyka +

8

Umiejętność korzystania ze zgromadzonych danych jest jedną z podstawowych cech społeczeństwa

(9)

Podsumowanie części 1

informatyka +

9

D a n e I n f o r m a c j a Wiedza

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 - często powstaje w wyniku przetwarzania i interpretacji danych

Wiedza - to miedzy innymi umiejętność wykorzystania informacji

Społeczeństwo informacyjne - to także my

(10)

Porozmawiajmy o danych 1

informatyka + 10 Tom ek K owal Zosia Nowak Mon ika Łago dna Fizy ka Matem atyka Infor_maty ka Zenon P oważny Maria C iekawa Rome k Atom ek 12-0 3-20 09 15-03-2009 18-03 -2009 5 3 6

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 powinno być dobrze

Uczeń Przedmiot Nauczyciel Data Ocena

(11)

Porozmawiajmy o danych 2

informatyka +

11

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 ?

(12)

Bazy danych 1

informatyka +

12

Baza danych Nazwisko : Kotek Imię : Jasio Data ur. : 07-11-1991 Pesel : 07111134498 Kod poczt: 26-987 Miasto : Sopot Ulica : Klonowa 12/8 Nazwisko : Kotek Imię : Jasio Data ur. : 07-11-1991 Pesel : 07111134498 Kod poczt: 26-987 Miasto : Sopot Ulica : Klonowa 12/8 Nazwisko : Sarenka Imię : Zosia Data ur. : 11-11-1991 Pesel : 91111134498 Kod poczt: 26-987 Miasto : Poznań Ulica : Osinowa 22/8 Nazwisko : Sarenka Imię : Zosia Data ur. : 11-11-1991 Pesel : 91111134498 Kod poczt: 26-987 Miasto : Poznań Ulica : Osinowa 22/8 Nazwisko : Lisek Imię : Piotr Data ur. : 11-02-1991 Pesel : 91021134498 Kod poczt: 96-987 Miasto : Opole Ulica : Długa 62/8 Nazwisko : Lisek Imię : Piotr Data ur. : 11-02-1991 Pesel : 91021134498 Kod poczt: 96-987 Miasto : Opole Ulica : Długa 62/8 Nauczyciel : Józef Ostry

Przedmiot :Informatyka Rodzaj : Sprawdzian Data wyst. : 17-05-2009 Ocena : 2

Nauczyciel : Józef Ostry Przedmiot :Informatyka Rodzaj : Sprawdzian Data wyst. : 17-05-2009 Ocena : 2

Nauczyciel : Jan Powaga Przedmiot :Matematyka Rodzaj : Sprawdzian Data wyst. : 17-05-2009 Ocena : 5

Nauczyciel : Maria Bryła Przedmiot :Fizyka Rodzaj : Sprawdzian Data wyst. : 17-05-2009 Ocena : 3

(13)

Bazy danych 2

informatyka +

13

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

(14)

Podsumowanie

informatyka +

14

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

(15)

Relacyjny model danych 1

informatyka +

15

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.

(16)

Wybrane

_{cechy modelu relacyjnego 1}

informatyka +

16

Nazwisko Imię Adres Języki obce Rodzeństwo Kod Ulica Miasto

Kot Jasio 12-098 Nowa 33/21

Opole Angielski, francuski, hiszpański

brat Staś, siostra Mariola

Lis Hania 65-987 Cicha 17/2 Sopot Angielski, niemiecki

brak Żuk Piotrek 33-093 Miła 4/3 Gdynia Nie zna brat Jaś,

brat Staś, siostra Hania

Wszystkie wartości zapisywane w tabelach oparte są na prostych typach danych(brak struktur złożonych )

Powyższa tabela nie spełnia tej cechy - w dalszej części pokażemy jak można ten problem rozwiązać

(17)

Wybrane

_{cechy modelu relacyjnego 2}

informatyka +

17

Wszystkie dane w bazie relacyjnej przedstawione są w formie dwuwymiarowych tabel zwanych relacjami

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

(18)

Wybrane cechy modelu relacyjnego 3

informatyka +

18

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

Wszystkie operacje wykonywane są w oparciu o logikę bez względu na położenie wiersza w tabeli

(19)

Wybrane cechy modelu relacyjnego 4

informatyka +

19

W tabeli musi istnieć kolumna lub zbiór kolumn o

wartościach niepowtarzalnych, pozwalający odnaleźć konkretny wiersz.

Taką kolumnę (lub zbiór) nazywamy kluczem podstawowym (ang. primary key)

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 Zaznaczone wiersze są nierozróżnialne,

Tak naprawdę nie wiadomo czy jest to pomyłka czy też opis

dwóch różnych osób

Dodatkowa kolumna „Pesel” – umożliwiła rozróżnienie dwóch osób. Pesel może być uznany za klucz

(20)

Relacyjny model danych 2

informatyka +

20

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

(21)

informatyka +

21 Tabela relacyjna

Cechy tabeli relacyjnej

1.Przeznaczenie

Uczniowie

2.Kolumny – określają cechy opisywanego obiektu

Nazwisko Imie Data_ur Pesel

3.Klucz podstawowy Pesel Iducznia Sztuczny klucz podstawowy 4.Wiersze – suma cech danego obiektu 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

(22)

informatyka +

22 Normalizacja – podstawa

projektowania

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

Faktury

I co tutaj nie gra 

(23)

informatyka +

23 Normalizacja – podstawa projektowania

Idfaktury Numer Data_w Netto Vat

1 234/08 11.08.08 345.67 71.22 2 43/08 12.08.08 763.00 167.00 3 01/2008 15.08.08 322.00 68.65 4 11.08/1 22.09.08 100.00 22.00 5 34w/08 28.09.08 882.00 187.00 6 987/08 02.10.08 250.55 58.12 7 002.08 11.10.08 891.00 201.15

Faktury

Firma Nip Ulica Miasto

Wedel 1234652789 Nowa 3 Warszawa Złotex 6573298722 Miła 7 Sopot Koral 5582998721 Dobra 1 Opole

Firmy

IdFirmy 1 2 3 Idfirmy 1 1 2 3 1 2 2

1 Wedel 1234652789 Nowa 3 Warszawa 1 Wedel 1234652789 Nowa 3 Warszawa

1 Wedel 1234652789 Nowa 3 Warszawa 2 Złotex 6573298722 Miła 7 Sopot 3 Koral 5582998721 Dobra Warszawa

2 Złotex 6573298722 Miła 7 Sopot 2 Złotex 6573298722 Miła 7 Sopot

(24)

Podsumowanie

informatyka +

24

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.

(25)

Plan prezentacji

informatyka +

25

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.

(26)

Przykładowy projekt bazy danych

informatyka +

26

(27)

Przykładowe fragmenty baz danych

informatyka +

27

Rejestr wypożyczeń książek

Tabela słownikowa Tabela opisująca osoby Powiązanie pomiędzy tabelami Tabela opisująca książki Tabela opisująca wypożyczenia książek Powiązanie pomiędzy tabelami Powiązanie pomiędzy tabelami

(28)

Analiza pewnego problemu 1

informatyka +

28

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

(29)

Analiza pewnego problemu 2

informatyka +

29

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

(30)

Analiza pewnego problemu 3

informatyka +

30

… 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

(31)

Analiza pewnego problemu 4

informatyka +

31

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”

(32)

Analiza pewnego problemu 5

informatyka +

32

(33)

Analiza pewnego problemu 6

informatyka +

33

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

(34)

Analiza pewnego problemu 7

informatyka +

34

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.

(35)

Analiza pewnego problemu 8

informatyka +

35

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

(36)

Analiza pewnego problemu 9

informatyka +

36

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

(37)

informatyka +

37

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

(38)

Problemy gromadzenia danych w tabelach 1

informatyka +

38

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

Problem 1 : Gromadzenie danych w tabeli nie może się odbywać bez reguł i ograniczeń – nie wystarczy samo nazwanie kolumn

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

(39)

informatyka +

39

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

Kilka słów o przedstawionych problemach

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

(40)

informatyka +

40

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

(41)

informatyka +

41

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 ?????

(42)

System Zarządzania Bazą Danych 1

informatyka +

42

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ń

(43)

System Zarządzania Bazą Danych 2

informatyka +

43

MS SQL Server 2008 Przykłady SZBD : Oracle MySQL Access DB2

(44)

System Zarządzania Bazą Danych 2

informatyka +

44

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

(45)

System Zarządzania Bazą Danych 2

informatyka +

45

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

(46)

Spójność i integralność danych

informatyka +

46

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

(47)

Deklaracja typu 1

informatyka +

47

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

(48)

Deklaracja typu 2

informatyka +

48

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)

(49)

Deklaracja typu 3

informatyka +

49

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

(50)

Deklaracja typu 4

informatyka +

50

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

(51)

Deklaracja typu 5

informatyka +

51

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

(52)

Deklaracja typu 5

informatyka +

52

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

(53)

Deklaracja typu 6

informatyka +

53

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

(54)

Deklaracja typu 7

informatyka +

54

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

(55)

Deklaracja typu 8

informatyka +

55

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

(56)

Deklaracja kluczy

informatyka +

56

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

(57)

Reguły poprawności dla kolumny

informatyka +

57

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ń)

(58)

Reguły poprawności dla wiersza

informatyka +

58

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

(59)

Reguły integralności referencyjnej

informatyka +

59

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

(60)

Podsumowanie

informatyka +

60

•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

(61)

Podstawy języka SQL

(62)

informatyka +

62 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.

(63)

informatyka +

63 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

(64)

informatyka +

64 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 – rózne implementacje różnia się od siebie (nieznacznie)

(65)

informatyka +

65 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ć

(66)

informatyka +

66 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

(67)

informatyka +

67 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

(68)

informatyka +

68 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)

(69)

informatyka +

69 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

(70)

informatyka +

70 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

(71)

informatyka +

71 Cechy języka SQL

Język Kontroli Danych – DCL (ang. Data Control Language)

Polecenia :

•GRANT– przydzielenie prawa do danych

•REVOKE – pozbawienie prawa do danych

(72)

informatyka +

72 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),

(73)

Przykładowa baza danych

(74)

Przykładowa baza danych

Pisanie zapytań w języku SQL wymaga

dobrej znajomości bazy danych do

której te zapytania się odnoszą

(75)

Podstawowe operacje realizowane

na modelu relacyjnym

informatyka + 75

Realizacja zapytań opiera się na trzech podstawowych operacjach wykonywanych na modelu relacyjnym:

Operacja projekcji (zwana także rzutowaniem) Operacja selekcji

(76)

Podstawowe operacje realizowane na modelu relacyjnym informatyka + 76 Operacja projekcji Tabela wyjściowa

Wykonanie operacji projekcji Tabela wynikowa po operacji projekcji

(77)

Podstawowe operacje realizowane na modelu relacyjnym informatyka + 77 Operacja selekcji Tabela wyjściowa Wykonanie operacji selekcji

Tabela wynikowa

Warunek selekcji Idklasy=2

(78)

Podstawowe operacje realizowane na modelu relacyjnym

informatyka + 78

Operacja łączenia

Wykonanie operacji łączenia _Tabela wyjściowa Tabela dołączania Tabela

(79)

Podstawowe operacje realizowane

na modelu relacyjnym

informatyka + 79

Przedstawione operacje wykonywane

na modelu relacyjnym są podstawa

(80)

Polecenie SELECT- zapytania

proste

informatyka + 80

SELECT *

FROM Uczniowie

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

Przykładowy wynik zapytania

(81)

Realizacja operacji projekcji

informatyka + 81

SELECT Nazwisko, Imie, Pesel, CzyChlopak FROM Uczniowie

Określona została , w opcji FROM, tabela z której

pobieramy dane i wymieniono liste kolumn, które maja się pojawić w wyniku

(82)

Realizacja operacji projekcji i

selekcji

informatyka + 82

SELECT Nazwisko, Imie, Pesel, CzyChlopak FROM Uczniowie

WHERE CzyChlopak=1

W klauzuli WHERE dodano warunek selekcji

(83)

Porządkowanie wyniku zapytania

informatyka + 83

SELECT Nazwisko, Imie, Pesel, Idklasy FROM Uczniowie

WHERE Idklasy=1 OR Idklasy=2

ORDER BY Idklasy ASC, Nazwisko DESC

Dodano klauzule ORDER BY – realizującą porządkowanie wyniku zapytania (opcja ASC – rosnąco, DESC – malejąco)

(84)

Przekształcanie danych

informatyka + 84

SELECT Nazwisko, Imie, Pesel, CASE CzyChlopak

WHEN 1 THEN ‘Mężczyzna’ ELSE ‘Kobieta’

END as Płeć FROM Uczniowie

WHERE Idklasy=2

Kolumna o nazwie Płeć powstała w wyniku przekształcenia wartości zapisanych w tabeli

(85)

Operacja łączenia

informatyka + 85

SELECT Uczniowie.* , Klasy.* FROM Uczniowie JOIN Klasy

ON Uczniowie.Idklasy=Klasy.Idklasy

Do wiersza opisującego ucznia został dołaczony odpowiedni wiersz z tabeli klasy

(86)

Operacja łączenia

informatyka + 86

SELECT Uczniowie.Nazwisko, Uczniowie.Imie, CASE CzyChlopak

WHEN 1 THEN ‘Mężczyzna’ ELSE ‘Kobieta’

END as Płeć,

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

(87)

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.

(88)

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

(89)

Rozwiązanie problemu

informatyka + 89

(90)

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

(91)

Złączenie zewnętrzne

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

Przygotujemy zapytanie w którym będą wszyscy

uczniowie wraz z informacja kiedy otrzymali w lutym roku 2009 ocenę mierną

(92)

Złączenie zewnętrzne

(93)

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.

(94)

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

(95)

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

(96)

Funkcje agregujące i grupowanie danych

•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

(97)

Funkcje agregujące i grupowanie danych

•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

Lista uczniów z klasy IIa oraz ich średnią ocen otrzymanych w roku 2009 - tylko ci uczniowie których srednia przekracza 3.00

(98)

Zapytania złożone

informatyka + 98

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.

(99)

Zapytania złożone

informatyka + 99

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.

(100)

Zapytania złożone

informatyka + 100

SELECT Uczniowie.Nazwisko, Uczniowie.Imie, Klasy.Nazwa, FROM Uczniowie JOIN Klasy

ON Uczniowie.idklasy=Klasy.idklasy

Zapytanie tworzące zbiór A

(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)

Zapytanie tworzące zbiór B

WHERE Iducznia NOT IN

Warunek różnicy zbiorów

(101)

informatyka + 101

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

(102)

Co jeszcze potrafię??

SELECT Klasy.Nazwa, Klasy.RokSzkolny

FROM Klasy

FOR XML AUTO,ROOT('ListaKlas'),ELEMENTS

Tworzenie wyniku zapytania w języku XML

(103)

Co jeszcze potrafię??

Operacje na zbiorach danych z wykorzystaniem operatorów UNION, EXCEPT i INTERSECT

WHERE idklasy=1

UNION

(104)

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

WHERE idklasy=1 EXCEPT

(105)

Co jeszcze potrafię??

Zapytanie, które przygotuje listę uczniów urodzonych w marcu, których nazwisko zaczyna się na literę K

WHERE MONTH(DataUrodzenia)=3 INTERSECT

(106)

Co jeszcze potrafię??

informatyka + 106 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

(107)

Co jeszcze potrafię??

(108)

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

(109)

Tabele tymczasowe i

widoki

(110)

mgr inż. Andrzej Ptasznik

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

(111)

Wyrażenia tabelaryczne (CTE)

(widoki tymczasowe)

with wystawili as ( select idwystawcy from faktury where year(data_wyst)=2006 ) select nazwa,nip from klienci

(112)

Widoki.

Zapytanie

SELECT

zapisane w bazie danych

tworzy tabelę wirtualną, której zawartość jest

wynikiem zapisanego polecenia.

(113)

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

Definicja bez opcji

Opcje powiązania ze schematem bazy danych i

opcja szyfrująca

Opcja powiązania operacji wstawiania danych z

(114)

Widoki – zastosowanie.

1. Uproszczenie schematu bazy danych.

2. Sterowanie uprawnieniami do danych.

3. Rozwiązywanie problemów

(115)

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.

(116)

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 Definicja funkcji Wywołanie funkcji Wynik funkcji

(117)

Transact - SQL

(118)

• 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

(119)

• Język T-SQL jest wykorzystywany do

programowania:

• Procedur składowanych

• Funkcji składowanych

• Wyzwalaczy

• Skryptów

(120)

Zmienne

• _{Wymaga jawnej deklaracji}

_{Wymaga jawnej deklaracji}

• _{Nazwa zmiennej lokalnej musi rozpoczynać się}

_{Nazwa zmiennej lokalnej musi rozpoczynać się}

od znaku „@”

• Typy zmiennych mogą być takie – jak

_{Typy zmiennych mogą być takie – jak}

wbudowane typy SQL Servera

(121)

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..

(122)

Konwersje typów

Polecenie

CAST

:

CAST

( wyrażenie AS typ_danych )

Polecenie

CONVERT

:

CONVERT

( typ_danych [ ( rozmiar ) ] ,

(123)

Konwersje typów-przykłady

PRINT

CAST

(12.45 as int)

PRINT

CONVERT

(int,12.45)

(124)

• 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)

Zmienne - przykłady

(125)

Tabele tymczasowe

• Nazwa lokalnej tabeli tymczasowej musi

_{Nazwa lokalnej tabeli tymczasowej musi}

rozpoczynać się od znaku „#”

• Nazwa globalnej tabeli tymczasowej musi

_{Nazwa globalnej tabeli tymczasowej musi}

rozpoczynać się od znaków „##”

• _{Tabele tymczasowe tworzone są w bazie}

_{Tabele tymczasowe tworzone są w bazie}

danych „tempdb”

(126)

Tabele tymczasowe-przykłady

Select nazwa, symbol

Select nazwa, symbol into #listaslownika

into #listaslownika

From kategorie

Create table

Create table ##tmp

##tmp

(idklienta int not null,

(127)

Instrukcje sterujące – instrukcja warunkowa

• Język T-SQL udostępnia „klasyczną” istrukcję

_{Język T-SQL udostępnia „klasyczną” istrukcję}

warunkową:

IF Wyrażenie _logiczne

{ polecenie_SQL | blok_poleceń}

[ ELSE

{ polecenie_SQL | blok_poleceń} ]

(128)

Instrukcje sterujące – iteracje

• _{Język T-SQL udostępnia „klasyczną”}

_{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

(129)

Instrukcja podstawiania

Dwa rodzaje instrukcji podstawiania :

SET

np.( Set @i=@i+1)np.( Set @i=@i+1)

np.(set @nip= (Select nip=nip np.(set @nip= (Select nip=nip

from klienci from klienci

where idklienta=321)where idklienta=321)

SELECT

np.( Select @i=@i+1)np.( Select @i=@i+1)

np.(Select @nip=nip np.(Select @nip=nip

from klienci from klienci

(130)

Funkcja CASE

SELECT nazwisko,imie,

CASE plec

WHEN 0 THEN ‘KOBIETA'

WHEN ‘1' THEN ‘Mężczyzna'

ELSE Płeć nieokreślona‘

END

(131)

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

(132)

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

(133)

I to prawie wszystko o języku T-SQL

…

reszta wynika z :

• _Problemu

_Problemu

• _Kontekstu

_Kontekstu

(134)

Procedury, funkcje, wyzwalacze

(135)

Procedury składowane – to kod w języku

T-SQL (lub CLR)

wykonywany po stronie serwera.

Procedura składowana jest obiektem bazy

danych !!!

(136)

SQLServer

Interfejs „Form”

Środowisko śieciowe

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 Wykonane

(137)

A teraz inne podejście

????????????

(138)

SQL Server

Iterfejs „Form”

Środowisko śieciowe

Wykonaj przelew z konta A na konto B

Metoda

„WykonajPrzelew”

1.Wykonaj metodę „…Przelew” 2.Odpowiedź z metody

(139)

Zastosowanie procedur składowanych :

1. Hermetyzacja „logiki bazy danych” 2. Realizacja zadań administracyjnych 3. Warstwa zabezpieczeń.

(140)

Definiowanie procedury

CREATE PROCEDURE ListaDostaw ; 1 Nagłówek procedury

@data datetime,

@komunikat varchar(500) output Lista parametrów

WITH ENCRYPTION,RECOMPILE _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

Blok procedury

BEGIN CATCH

SET @komunikat=‘ Błąd zapytania’ RETURN 1

END CATCH

Blok obsługi wyjątków

(141)

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

(142)

Rodzaje funkcji składowanych ;

1. Funkcje systemowe

2. Funkcje tabelaryczne

a)Funkcje typu „inline”

b)Złożone funkcje tabelaryczne

3. Funkcje skalarne

(143)

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

(144)

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

(145)

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

(146)

informatyka + 146

(147)