PRACA Z DANYMI

Z WYKORZYSTANIEM NARZĘDZIA

MS SQL SERVER

Warszawa 2007

Spis treści

1. Praca z danymi - Transakcje

1. Praca z danymi - Transakcje

1. Wprowadzenie

Transakcja jest sekwencją operacji wykonywanych jako jedna logiczna jednostka pracy. Cechą charakterystyczną transakcji jest, że wszystkie polecenia, wchodzące w skład transakcji, muszą być wykonane poprawnie lub w przeciwnym przypadku transakcja nie jest realizowana. Transakcje są jednym z kluczowych elementów zapewniających spójność i integralność systemów bazodanowych.

Każda z transakcji tworzona jest z zachowaniem pewnych standardowych własności:

• Atomic - transakcja jest najmniejszą jednostką logiczną. Oznacza to, że jest wykonywana w całości albo w całości jest odwoływana;

• Consistent - transakcja nie zmienia spójności bazy. To oznacza, że jeśli baza była spójna przed wykonaniem transakcji, będzie także spójna po jej ukończeniu;

• Isolated - transakcja musi być izolowana, czyli nie może wchodzić w konflikty z innymi transakcjami wykonywanymi na tym samym zbiorze danych;

• Durable - transakcja jest trwała, jeżeli gwarantowane jest, że wykonane działania pozostaną kompletne bez względu na to, co się stanie z bazą po poprawnym zakończeniu transakcji. Jeżeli wystąpi awaria zasilania i serwer bazy danych ulegnie awarii, istnieje gwarancja, że transakcja będzie kompletna po ponownym uruchomieniu serwera.

Aby zagwarantować spójność bazy i mieć pewność, że wykonywane operacje zakończą się sukcesem lub porażką, stosowany jest mechanizm blokowania. Blokady są gwarancją tego, że podczas operowania na danych przez jedną transakcję dane nie zostaną zmienione bądź usunięte.

Rozpoczęcie transakcji na SQL Server 2005 można dokonać na trzy sposoby:

• Explicite - jawne rozpoczęcie transakcji za pomocą polecenia BEGIN TRANSACTION;

• Autocommit - automatyczne, operacje na serwerze standardowo podlegają transakcjom. Każde z poleceń jest automatycznie zatwierdzane po poprawnym wykonaniu. Nie ma wtedy potrzeby zatwierdzenia transakcji;

• Implicit - niejawne, wywoływane przez programy użytkowe działające na bazie danych.

Zakończenie transakcji odbywa się za pomocą poleceń COMMIT lub ROLLBACK. Polecenia COMMIT używamy, gdy wszystkie operacje od rozpoczęcia transakcji powiodły się. W takiej sytuacji możemy zatwierdzić wszystkie zmiany, jakie zostały wprowadzone. Polecenie to dodatkowo zdejmuje wszystkie blokady z tabel, które zostały zablokowane na czas trwania transakcji.

Komenda ROLLBACK odwołuje transakcję. Polecenie to odwołuje wszystkie modyfikacje jakie zostały dokonane podczas trwania transakcji poprzez przywrócenie stanu danych sprzed transakcji. Polecenie to, podobnie jak COMMIT, również zdejmuje wszystkie blokady z danych, jakie zostały założone na czas trwania transakcji.

1. Zadanie 1

Zadanie prezentujące realizację transakcji. Transakcja będzie obejmowała wprowadzanie nowych rekordów do tabeli DZIECI.

2. Wykonanie zadania 1

• 
  Po zalogowaniu się do MS SQL Server Management Studio należy utworzyć nowy skrypt SQL'owy (Rys. 1):

Rys. 1 Tworzenie nowego skryptu SQL'owego

• Realizację zadania zaczynamy od sprawdzenia liczby rekordów w tabeli DZIECI (Rys. 2)

Rys. 2 Sprawdzenie liczby rekordów w tabeli DZIECI

• Teraz wprowadzimy dwa nowe rekordy do tabeli DZIECI i ponownie sprawdzamy liczbę rekordów w tabeli DZIECI (Rys. 3).

Rys. 3 Wprowadzanie nowych rekordów do tabeli DZIECI

• Można również sprawdzić w widoku tabeli DZIECI pojawienie się dwóch nowych rekordów o numerach 419 i 420 (Rys. 4).

Rys. 4 Nowe rekordy w tabeli DZIECI

• Wykonajmy teraz tę samą operację, ale z wykorzystaniem mechanizmu transakcji (Rys. 5), który na końcu odwoła całą transakcję (ROLLBACK).

Rys. 5 Wycofywanie transakcji

• Transakcja została odwołana, nowe rekordy nie zostały dodane - liczba rekordów w tabeli DZIECI ponownie wynosi 420 rekordów pomimo, że w zakładce Messages jest informacja o pozytywnym wykonaniu całego skryptu (Rys. 6).

Rys. 6 Komunikat zakładki Messages

• Sprawdźmy teraz realizację tej transakcji (Rys. 7) przy potwierdzeniu jej wykonania (COMMIT).

Rys. 7 Zatwierdzenie transakcji

• Po zatwierdzeniu transakcji operacja zostanie wykonana i będą widoczne dwa kolejne rekordy (Rys. 8).

Rys. 8 Widok tabeli DZIECI po zatwierdzeniu transakcji

Brak kolejności w numeracji rekordów (425 i 426 zamiast 421 i 422) potwierdza wcześniejszą operację wycofania transakcji.

1. Zadanie 2

Zadanie zaprezentuje funkcję kontrolną transakcji. Transakcja będzie realizowała kilka operacji aktualizacji rekordów w tabeli DZIECI, ale zatwierdzenie transakcji będzie wykonywane tylko wtedy, kiedy wszystkie aktualizacje składowe będą poprawne.

2. Wykonanie zadania 2

• Należy utworzyć nowy skrypt SQL'owy wpisać do niego polecenia aktualizacji idPrac (numerów rodziców) dla dzieci (idDziecka) o numerach 1 i 2 (Rys. 9).

Rys. 9 Aktualizacja rekordów w tabeli DZIECI

Dziecku o idDziecka = 1 przypisano numer idPrac = 1, natomiast dziecku o idDziecka = 2 próbowano przypisać idPrac=-5. Komunikat w zakładce Messages informuje, że pierwsza aktualizacja powiodła się (została wykonana), natomiast druga nie została zrealizowana z powodu konfliktu numeru ujemnego z definicją pola idPrac (tylko liczby całkowite dodatnie).

• Przekształćmy teraz te dwie instrukcje w transakcję, która będzie w całości wycofywana, jeżeli chociaż jedna z operacji aktualizacji nie powiedzie się (Rys. 10). Żeby sprawdzić efekt działania przypiszmy teraz idDziecka = 1 numer rodzica idPrac = 10 i idDziecka = 2 ponownie idPrac = -5.

Rys. 10 Blokowanie błędnej transakcji

Blok TRY-CATCH działa w ten sposób, że instrukcje bloku CATCH są wykonywane tylko wtedy, kiedy w bloku TRY wystąpi błąd. Proszę sprawdzić w danych tabeli DZIECI, czy zostało zmienione idPrac przy idDziecka = 1 lub idDziecka = 2. Zmiana taka nie nastąpiła!

Na tym właśnie polega blokowanie błędnych transakcji, które składają się z szeregu różnych elementów, a każdy element ma znaczenie krytyczne - nie może zostać wykonany niepoprawnie. Klasycznym przykładem takiej transakcji jest wypłata pieniędzy z bankomatu, na którą składają się takie operacje jak: identyfikacja klienta, sprawdzenie stanu konta, wypłata pieniędzy, aktualizacja stanu konta. Jeżeli którakolwiek z tych operacji nie powiedzie się to cała transakcja musi zostać wycofana!

• Sprawdźmy jeszcze jak powyższy skrypt zachowa się, kiedy obydwie aktualizacje będą możliwe do przeprowadzenia (Rys. 11).

Rys. 11 Realizacja poprawnej transakcji

• Transakcja została wykonana pomyślnie - dane zostały zaktualizowane zgodnie z instrukcją (Rys. 12).

Rys. 12 Stan tabeli DZIECI po wykonaniu transakcji

2. Praca z danymi - Widoki i skrypty

1. Wprowadzenie

Po utworzeniu i zapełnieniu bazy danymi bardzo ważnym procesem, obok bieżącego uaktualniania zasobów danych, jest możliwość ich efektywnego wykorzystywania w celu pozyskania niezbędnych informacji. To jest główny cel gromadzenia i utrzymywania zasobów danych w organizacji. Realizację tego celu zapewniają dwa narzędzia: widoki (Views) oraz zapytania skryptowe (Queries). Widoki są zapisywane w strukturze bazy danych i mogą być wykorzystywane jako źródła innych widoków. Skrypty to programy tworzone w języku sql, które wykonują określone operacje na bazie danych (tabelach i widokach).

2. Zadanie 1

Należy przygotować widok przechowujący listę zatrudnionych w firmie pracowników. Lista powinna zawierać następujące elementy:

• idPrac,

• Imię i Nazwisko pracownika,

• Stanowisko,

• Miejsce zatrudnienia - numer jednostki organizacyjnej, w której pracownik jest zatrudniony,

• Wiek pracownika - liczony jako różnica roku bieżącego i roku urodzenia pracownika,

• Staż pracy - liczony jako różnica roku bieżącego i roku zatrudnienia pracownika,

• Płaca pracownika.

Wyniki powinny być posortowane według Miejsca zatrudnienia, Stanowiska (malejąco) oraz Nazwiska pracownika.

1. Wykonanie zadania 1

• Po zalogowaniu się do MS SQL Server Management Studio należy utworzyć nowy widok (Rys. 13).

Rys. 13 Tworzenie nowego widoku

• Pierwszym krokiem definiowania nowego widoku jest okno dodawania tabel (Rys. 14), w którym należy wskazać tabele potrzebne do jego realizacji. Do wykonania bieżącego zadania potrzebny jest dostęp do tabeli Pracownicy.

Rys. 14 Dodawanie tabel do definicji widoku

• 
  Edytor projektu kwerendy składa się z czterech elementów (Rys. 16):

• Diagramu, gdzie widoczne są tabele, inne widoki i relacje, które zastały włączone do projektu,

• Definicji, gdzie specyfikuje się pola widoku oraz wszystkie kryteria ograniczające,

• Kodu, gdzie zawartość Diagramu i Definicji jest zamieniana na kod sql'owy,

• Wyników, gdzie prezentowane są wyniki działania widoku na bieżąco.

• 
  
  
  
  
  Poszczególne elementy panelu można włączać lub wyłączać z widoku wykorzystując przyciski na pasku narzędziowym (Rys. 15).

Rys. 15 Pasek narzędziowy View Designer

Rys. 16 Struktura widoku

• Pierwsze pole, które należy wprowadzić do widoku to idPrac (Rys. 17). Wystarczy zaznaczyć je w oknie Diagram na liście pól tabeli Pracownicy lub wybrać w oknie Definicja w kolumnie o nazwie Column z dostępnej listy.

Rys. 17 Wprowadzanie pola z tabeli

• Pole Imię i Nazwisko pracownika jest polem obliczanym, ponieważ w tabeli Pracownicy występuje nie jedno, ale dwa oddzielne pola: ImięP i NazwiskoP. Należy stworzyć nowe pole obliczane o nazwie Pracownik, które połączy te dwa elementy. Definicję tego pola prezentuje Rys. 18. Do łączenia pól tekstowych wykorzystuje się znak dodawania. Spacja w środku wyrażenia zapewni odstęp pomiędzy imieniem i nazwiskiem pracownika. Nazwę nowego pola wprowadzamy do kolumny Alias.

Rys. 18 Łączenie pól tekstowych

• Po uruchomieniu działania kodu (przycisk Execute SQL na pasku narzędziowym) można obejrzeć listę pracowników w oknie wyników (Rys. 19). Aby ujednolicić format wyświetlanych wyników można wykorzystać funkcję Upper (zamiana wszystkich liter na kapitaliki).

Rys. 19 Wyświetlanie wyniku działania widoku

• Następne pola w widoku to Stanowisko i Miejsce zatrudnienia. Miejsce zatrudnienia jest numerem jednostki organizacyjnej (idJo), w której pracownik jest zatrudniony. Zmieniamy tylko Alias wprowadzonego pola z idJo na MiejsceZat (Rys. 20).

Rys. 20 Zmiana tytułu pola widoku

• Teraz trzeba wprowadzić pola obliczane Wiek pracownika i jego Staz pracy. Obydwa pola są funkcją czasu zależną od numeru roku bieżącego oraz roku urodzenia/zatrudnienia pracownika. Aby utworzyć formułę dla tego pola należy skorzystać z dwóch funkcji sql'owych:

• GetDate() - funkcja odczytująca datę systemową,

• Year(data) - funkcja odczytująca rok z dowolnej daty.

Prawidłową strukturę formuł dla tych pól prezentuje Rys. 21.

• Ostatnim polem w widoku jest pole Płaca, dołączane z tabeli Pracownicy.

Rys. 21 Obliczenie Wieku i Stażu Pracy

• Teraz należy wprowadzić sortowanie względem MiejscaZat (sortowanie rosnące, pierwszy klucz sortujący), Stanowiska (sortowanie malejące, drugi klucz sortujący) oraz Nazwiska pracownika (sortowanie rosnące, trzeci klucz sortujący, kolumna nie pokazywana w wynikach - tylko klucz sortujący). Do ustawienia parametrów sortowania służą kolumny: Sort Type, Sort Order i Output (Rys. 22).

Rys. 22 Sortowanie wyników

• Gotowy widok należy zapamiętać pod nazwą Lista zatrudnionych.

1. Zadanie 2

Należy przygotować nowe widoki, które umożliwią śledzenie struktury płac w firmie:

• rozpiętość płacy: średnią, maksymalną, minimalną wypłatę oraz sumę wszystkich płac,

• pokazać strukturę płacy w zależności od stażu pracy.

Następnie utworzyć kwerendy (nazywane również zapytaniami lub skryptami), które będą filtrowały dane w bazie. Kwerendy powinny realizować następujące zadania:

• wyszukać pracowników, którzy przepracowali w firmie ponad 8 lat, a ich płaca jest poniżej średniej,

• wyszukać tych pracowników, którzy zarabiają najwięcej i najmniej w firmie.

Wyniki skryptów powinny być zapisane w plikach tekstowych.

1. Wykonanie zadania 2

• Utworzyć nowy widok z dostępem do widoku Lista zatrudnionych. Czterokrotnie wprowadzić pole Placa. Dodać kolumnę grupowania (menu podręczne, opcja Add Group By). W pierwszym polu ustawić funkcję agregującą - średnią (avg), w drugim - maksymalną (max), w trzecim - minimalną (min), a w czwartym - sumę (sum). Strukturę tego widoku prezentuje Rys. 23.




Rys. 23 Widok RozpietoscPlacy

• Utworzyć nowy widok z dostępem do widoku Lista zatrudnionych. Wprowadzić pola: Wiek i trzy razy pole Placa. Dodać grupowanie względem pola Staz. W Placa ustawiamy kolejno funkcje agregujące: średnią, max i min. Zmienić Alias dla poszczególnych pól Placa i dodać sortowanie (Rys. 24).




Rys. 24 Widok RozpietoscPlacyStaz

• Teraz zajmijmy się przeszukiwaniem utworzonych widoków przy pomocy zapytań. Aby utworzyć dowolne zapytanie należy na pasku narzędziowym Standard wybrać opcję New Query (Rys. 25).







Rys. 25 Wywoływanie opcji New Query

• Otwiera się okno, do którego można wpisać i uruchomić kod sql'owy. Inną metodą tworzenia zapytania jest wywołanie edytora (z paska narzędziowego SQL Editor) i tworzenie zapytania w oknie podobnym do okna generowania widoku (Rys. 26).




Rys. 26 Okno Query Designer Editor

• Na Rys. 26 prezentowana jest definicja zapytania PlacaPonizejSredniej z uwzględnieniem źródeł danych (widoki Lista zatrudnionych, RozpiętoscPlacy) oraz warunków filtrujących (Staz > 10, Placa < SredniaPlaca). Po zaakceptowaniu definicji i powrocie do okna podstawowego kwerendy otrzymujemy kod sql'owy (Rys. 27).




Rys. 27 Skrypt filtrujący staż i płacę

• W celu zapisania wyników kwerendy należy z menu podręcznego w oknie wyników (po zaznaczeniu wszystkich wyników - należy wcisnąć lewy górny róg tabeli wyników) wybrać opcję Save Results As.




Rys. 28 Zapisywanie wyników do zbioru

• Podczas zapisywania pliku należy zmienić Type z Export na All files. Nie wolno też zapomnieć o dodaniu rozszerzenia txt do nazwy pliku (Rys. 29).




Rys. 29 Zapisywanie wyników w pliku tekstowym

• Analogicznie należy utworzyć dwa zapytania wyszukujące pracowników zarabiających odpowiednio najwięcej (Rys. 30) i najmniej w firmie. Wyniki obydwu zapytań również należy zapisać w pliku tekstowym.




Rys. 30 Pracownicy zarabiający najwięcej w firmie

1. Zadanie 3

Należy sprawdzić ile będzie kosztowało firmę, udzielenie sekretarkom podwyżki zależnej od ich stażu pracy:

• w wysokości 20% płacy przy stażu powyżej 6 lat,

• w wysokości 15% płacy przy stażu powyżej 2 lat,

• w wysokości 10% płacy dla wszystkich pozostałych.

1. Wykonanie zadania 3

• Utworzyć zapytanie obliczające aktualną sumę płacy wszystkich sekretarek.




Rys. 31 Aktualna suma płacy sekretarek

• Aby przeprowadzić symulację kosztów podwyżki utworzymy nową tabelę na podstawie tabeli Pracownicy. W tym celu należy utworzyć nowe zapytanie i w jego edytorze z menu podręcznego wybrać nowy typ: Make Table (Rys. 32).




Rys. 32 Zapytanie tworzące tabelę

• Należy podać nazwę tworzonej tabeli (Rys. 33):




Rys. 33 Tworzenie tabeli Sekretarki

• Po uruchomieniu zapytania zostanie utworzona tabela Sekretarki (Rys. 34).






Rys. 34 Nowa tabela Sekretarki

• Teraz należy utworzyć zapytanie aktualizujące, które podniesie płacę sekretarkom w zależności od ich stażu pracy. W tym celu należy w edytorze zapytania wybrać typ Update (Rys. 35)




Rys. 35 Zapytanie aktualizujące płacę w tabeli Sekretarki

• Pełną definicję tego zapytania prezentuje Rys. 36.




Rys. 36 Definicja zapytania aktualizującego

• Teraz porównując wyniki zapytań prezentujących aktualną sumę płac sekretarek oraz sumę płac po podwyżce możemy zdecydować o tym czy można przyznać sekretarkom planowaną podwyżkę (Rys. 37). Posługując się tabelą Sekretarki można też zbadać inne warianty podwyżki dla sekretarek.







Rys. 37 Suma wypłat sekretarek przed i po podwyżce

• Jeżeli wielkość podwyżki jest akceptowalna przez zarząd firmy i jest decyzja o jej udzieleniu to możemy zaktualizować wartość Płacy tym razem bezpośrednio w tabeli Pracownicy (Rys. 38).





Rys. 38 Udzielenie podwyżki sekretarkom

3. Zadanie do wykonania

Wykonać dla swojej bazy SQL widoki i zapytania (zgodnie z zatwierdzonym przez prowadzącego projektem). Zadanie stanowi element oceniany.

dr inż. T. Ostrowska, dr inż. K. Rostek Zakład Systemów Informatycznych

IOSP PW Bazy Danych - laboratorium

ćw 10-11 Praca z danymi, 2007-06-22

Definicja (ang. Criteria Pane)

Diagram (ang. Diagram Pane)

Kod (ang. SQL Pane)

Wyniki (ang. Results Pane)

Diagram

Definicja

Kod

Wyniki

Uruchamianie kodu

---