Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3 Wersja 2.0
Definiowanie i zarządzanie bazą danych
Spis treści
Definiowanie i zarządzanie bazą danych ... 1
Informacje o module ... 2
Przygotowanie teoretyczne ... 3
Przykładowy problem ... 3
Podstawy teoretyczne... 3
Przykładowe rozwiązanie ... 10
Porady praktyczne ... 14
Uwagi dla studenta ... 15
Dodatkowe źródła informacji... 15
Laboratorium podstawowe ... 16
Problem 1 (czas realizacji 45 min) ... 16
Laboratorium rozszerzone ... 26
Zadanie 1 (czas realizacji 90 min) ... 26
Informacje o module
Opis modułu
Dobry administrator systemu zarządzania bazami danych wie wszystko o bazach danych. W dzisiejszych czasach rola administratora nie ogranicza się do zarządzania istniejącymi bazami danych, ale również wymaga umiejętności zakładania, konserwacji oraz aktualizacji baz danych znajdujących się pod jego opieką. W tym module poznasz sposoby tworzenia, rozbudowy i zarządzania bazą danych.
Cel modułu
Celem modułu jest przedstawienie czytelnikowi typowych zagadnień związanych z zakładaniem i konserwacją bazy danych na serwerze Microsoft SQL Server 2008.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
• potrafił zaplanować instalację bazy danych
• rozumiał czym są pliki bazy, tabele, diagramy, klucze
• wiedział, jak wykonać kopię bezpieczeństwa bazy oraz jak przywrócić bazę w razie awarii
Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
• wiedzieć, jak używać oprogramowania Microsoft Virtual PC
• znać podstawy obsługi systemu Windows 2000 lub nowszego
• rozumieć, jakie są elementy diagramu ERD i powiązania między nimi
• znać podstawy obsługi narzędzia SQL Server Management Studio Mapa zależności modułu
Zgodnie z mapą zależności przedstawioną na rys. 1, istnieje konieczność wykonania wcześniej modułów 1 i 2.
Rys. 1 Mapa zależności modułu
Przygotowanie teoretyczne
Przykładowy problem
Jesteś administratorem systemu bazodanowego w firmie National Insurance. Analitycy w dziale deweloperskim stworzyli diagram bazy danych dla nowego produktu firmy. Diagram zawiera informacje o wszystkich tabelach i relacjach bazy. Wskazuje też na możliwe do użycia typy danych.
Diagram bazy danych przedstawiony jest na rys. 2.
Rys. 2 Diagram przykładowej bazy danych
Twoim zadaniem jest założenie bazy danych wraz z odpowiednimi tabelami, ustanowienie kluczy głównych, powiązań między nimi i określenie dogodnych typów danych. W końcowej fazie powinieneś wypełnić bazę przykładowymi danymi i zapewnić jej kopię bezpieczeństwa.
Podstawy teoretyczne
Dysponując gotowym projektem relacyjnej bazy danych, jesteś na początku długiej drogi, zanim baza powstanie na serwerze i rozpocznie swoje życie. Zanim zaimplementujesz bazę danych, musisz zaplanować wiele ustawień dotyczących różnych aspektów działania bazy.
Rozmieszczenie plików bazy danych
Baza danych składa się z plików. Plik bazy można podzielić na pliki danych i pliki dziennika transakcji. W plikach danych przechowywane są obiekty baz danych, takie jak tabele (wraz z danymi), indeksy czy procedury składowane. W plikach dziennika transakcji system zapisuje strony (w przypadku systemów SQL Server strona to blok 8 KB, ale można go zwiększyć) zawierające modyfikacje danych.
Rozmieszczenie plików odgrywa znaczącą rolę w procesie tworzenia i optymalizacji bazy danych.
Istnieje szereg zasad przydatnych w planowaniu fizycznej implementacji bazy danych. Zasady te to m.in.:
• Pliki dziennika transakcji należy umieszczać na innym dysku fizycznym niż pliki danych – w przypadku awarii jednego z dysków tracimy tylko część bazy.
• Tabele często występujące razem w złączeniach (ang. joins) należy umieszczać w osobnych plikach (grupach plików) na osobnych dyskach fizycznych – dzięki temu możliwe jest równoległe pobieranie danych z tych tabel.
• Indeksy nieklastrowane należy umieszczać na innych dyskach fizycznych niż ich bazowe tabele – zyskujesz większą wydajność zapisu danych.
• Tabele często modyfikowane należy oddzielić fizycznie (umieścić na innym dysku) od tabel rzadko modyfikowanych – w przypadku większych baz danych upraszcza to proces wykonywania kopii zapasowych.
• W przypadku naprawdę dużych instancji bazy stosowane są dedykowane rozwiązania programowo-sprzętowe typu klastry z wydajnymi macierzami dyskowymi.
Jednoczesne spełnienie wszystkich wymienionych punktów jest raczej niemożliwe, ale w idealnym przypadku oddzielenie dziennika transakcji od danych i maksymalne rozczłonkowanie danych pomiędzy tak wiele dysków, jak się da, byłoby rozwiązaniem dającym maksymalne bezpieczeństwo i wydajność bazy.
Bezpieczeństwo fizyczne danych
Dane, jak i dziennik transakcji można dodatkowo zabezpieczyć przed ewentualnymi awariami dysków fizycznych. Do tego celu należy użyć macierzy dyskowych z zaimplementowanymi konfiguracjami RAID (ang. Redundant Array of Independent Disks).
Istnieje wiele konfiguracji RAID, ale do najbardziej znanych i zarazem najczęściej stosowanych należą: RAID-0, RAID-1, RAID-5 oraz RAID-10. Implementacja RAID może być rozwiązana drogą sprzętową lub programową w systemie operacyjnym. Rozwiązania różnią się wydajnością i… ceną.
Więcej o systemach RAID i bezpieczeństwie systemów bazodanowych znajdziesz w module 10.
Przy tworzeniu bazy danych administrator musi zdecydować o jej konfiguracji. Konfiguracja ta dotyczy nie tylko plików bazy danych, ale także pewnych ustawień samej bazy danych. Ustawienia te decydują o tym, jak będzie się zachowywała baza w trakcie swojego cyklu życia.
Ustawienia dotyczące plików
Po wybraniu odpowiedniej struktury bazy i określeniu jej nazwy przychodzi pora na ustawienie pewnych właściwości plików bazy danych. Ustawienia te to:
• ilość i lokalizacja plików
• wielkość początkowa plików
• sposób powiększania plików (powinien zostać wybrany automatyczny przyrost rozmiaru)
• maksymalny rozmiar dla każdego z plików (alternatywnie można maksymalny rozmiar pozostawić nieokreślony, ale wówczas administrator jest zmuszony do monitorowania wolnego miejsca na dysku twardym)
• nazwy logiczne plików
Rys. 3 Właściwości przykładowej bazy danych w SQL Server 2008
W praktyce należy pamiętać o takim ustawieniu rozmiaru początkowego plików, by znalazło się w nich miejsce na nowe dane. Proces powiększania rozmiaru pliku może niepotrzebnie obciążyć bazę danych. Co za tym idzie, należy też zadbać o to, by przyrost rozmiaru pliku nie był zbyt mały.
Najlepszym nawykiem jest manualne powiększanie rozmiarów plików poza godzinami, w których baza jest używana przez największą liczbę użytkowników. Opcja automatycznego powiększania plików może być ustawiona na wszelki wypadek (gdyby administrator zapomniał o swoich obowiązkach lub wystąpiła nieoczekiwana sytuacja wymagająca powiększenia pliku). Niemniej jednak taka automatyzacja jest dyskusyjna ponieważ wpływa na wydajność bazy.
Dziennik transakcji należy ustawić na początek na 20-30% rozmiaru plików danych. W trakcie pracy bazy danych rozmiar ten można zmienić wedle potrzeb.
Ustawienia dotyczące bazy danych
Równie ważne jak ustawienia plików są ustawienia samej bazy danych. Ustawienia te to na przykład:
• ustawienia dotyczące języka, metod sortowania tekstu, znaków dialektycznych
• ustawienia dotyczące zapisu zmian w dzienniku transakcji (tzw. model przywracania bazy, ang. recovery model)
• ustawienia dotyczące statystyk (statystyki to zapis rozkładu danych w kolumnach najczęściej wykorzystywanych w tabelach, mogą być tworzone i utrzymywane automatycznie)
• ustawienia dotyczące dostępu do bazy danych (w niektórych sytuacjach niezbędne jest ograniczenie dostępu do bazy danych dla użytkowników, np. przy przywracaniu bazy danych z kopii zapasowej)
Rys. 4 Opcje przykładowej bazy danych w SQL Server 2008
Ustawienia opcji bazy danych zmieniają się w trakcie działania bazy. Zmiany dokonywane są przy pomocy narzędzi graficznych (np. SQL Server Management Studio) lub poleceń SQL (np. ALTER DATABASE). Do ciekawszych opcji należą polecenia Auto Shrink, która optymalizuje rozmiar plików (z taką samą wadą jak automatyczne zwiększanie rozmiaru) i Auto Create Statistics, która generuje automatycznie statystyki na podstawie dystrybucji wartości w kolumnie danych.
Informacja ta jest używana przez SQL Server Query Optimizer do generowania planu zapytań bazującego na kosztach używania różnych kolumn.
Tabela bazy danych
Tabela jest dwuwymiarowym zbiorem wzajemnie powiązanych danych dotyczących wspólnego obszaru lub encji. Składa się ona z kolumn, w których przechowywane są dane, np. w tabeli Pracownicy przeważnie znajdziemy kolumny takie jak Nazwisko czy PESEL. W relacyjnej bazie danych relację zachodzą właśnie pomiędzy danymi w poszczególnych kolumnach nazywanych także atrybutami tabeli.
Pojedynczy rząd obejmujący kolumny reprezentuje instancję danych, czyli rekord (ang. record).
Większość operacji na bazie danych wykonuje się na rekordach lub, częściej, zbiorach rekordów (ang. recordset).
Do jednoznacznej identyfikacji tabeli służy klucz główny (ang. primary key), który przeważnie jest dodatkową kolumną z unikatowymi wartościami. Dzięki znajomości tych wartości możemy szybko wskazać interesujący nas rekord w danej tabeli, a następnie odczytać go lub zmodyfikować.
Tabele możemy utworzyć za pomocą narzędzi graficznych, np. w Management Studio rozwijając w obszarze Object Explorer odpowiednie menu.
Możemy też posłużyć się odpowiednim poleceniem języka SQL (i tak najczęściej postępuje się w praktyce):
CREATE TABLE nazwa_tabeli (kolumny_tabeli) Więcej na ten temat znajdziesz w module 5.
Podczas tworzenia tabeli należy zdefiniować chociaż jedną kolumnę zawierającą dane.
Przechowywane dane mogą być tylko ściśle określonego typu. Typ ten jest wspólny dla całej kolumny.
Typy danych
Typ danych jest pierwszą wielkością jaką definiuje użytkownik dla kolumny tabeli. Typ danych kolumny kontroluje typ informacji jaka może być przechowywana w kolumnie. Należy zdefiniować typ danych przez wpisanie za nazwą kolumny słowa kluczowego, może ono wymagać parametrów.
Po zdefiniowaniu typ danych kolumny tabeli jest stałą właściwością i nie powinno być zmieniany.
Można również wykorzystać typy danych do definicji innych struktur przechowujących dane, takich jak parametry i lokalne zmienne.
Przegląd wybranych typów danych dostępnych na platformie Microsoft SQL Server prezentuje tab.
1
Tab. 1 Podstawowe typy danych dostępne w Microsoft SQL Server
Kategoria Opis Typ danych Szczegóły
Typy binarne Dane zawierają łańcuchy binariów zapisanych w postaci liczb w systemie szesnastkowym (heksadecymalnym).
binary Dane o stałej przypisanej długości (do 8 KB).
varbinary Dane o różnej długości aż do zdefiniowanej maksymalnej (do 8 KB).
image Dane mogą być różnej długości i przekroczyć rozmiarem 8 KB.
Typy tekstowe
Dane są kombinacją liter, cyfr i symboli.
char Dane o stałej przypisanej długości (do 8 KB).
varchar Dane o różnej długości aż do zdefiniowanej maksymalnej (do 8 KB).
text Dane tekstowe o rozmiarze
przekraczającym 8 znaków Data i czas Dane są kombinacją dobrze
sformatowanej daty i czasu. Nie istnieją typy danych opisujące osobno datę i czas.
datetime Data z zakresu od 1 stycznia 1753 do 31 grudnia 9999 (jedna wartość zajmuje 8 B).
smalldatetime Data z zakresu od 1 stycznia 1900 do 6 lipca 2079 (jedna wartość zajmuje 4 B).
datetimeoffset zapewnia obsługę stref czasowych datetime2 Wspiera szerszy zakres dat oraz większą
precyzję części ułamkowych sekundy i także umożliwia określanie precyzji
Typy liczb dziesiętnych
Dane liczbowe o dokładności do ostatniej znaczącej cyfry.
decimal Maksymalnie 38 cyfr, z czego wszystkie mogą znajdować się po prawej stronie przecinka. Typ przechowuje dane dokładne (nie przybliżone).
numeric W SQL Server jest to odpowiednik typu decimal.
Typy liczb o zmiennej precyzji
Dane są przybliżonymi liczbami zmiennoprzecinkowymi o dokładności takiej, jaka może w danej chwili być obsłużona przez mechanizmy obliczeniowe.
float Liczba zmiennoprzecinkowa z zakresu od -1,79E+308 do 1,79E+308.
real Liczba zmiennoprzecinkowa z zakresu od–
2.40E + 38 do 2.40E + 38.
Typy liczb całkowitych
Dane są liczbami całkowitymi. bigint Liczba całkowita z zakresu od –2^63 (-9223372036854775808) do 2^63-1 (9223372036854775807). Rozmiar jednej liczby 8 B.
int Liczba całkowita z zakresu od
-2 147 483 648 do 2,147,483,647. Rozmiar
Kategoria Opis Typ danych Szczegóły jednej liczby to 4 B.
smallint Liczba całkowita z zakresu od -32,768 do 32,767. Rozmiar jednej liczby to 2 B.
tinyint Liczba całkowita z zakresu od zera do 255.
Rozmiar jednej liczby to 1 B.
Typy monetarne
Dane reprezentują ujemne lub dodatnie sumy pieniędzy.
money Wartość monetarna z zakresu od -922,337,203,685,477.5808 do
922,337,203,685,477.5807. Rozmiar jednej wartości to 8 B.
smallmoney Wartość monetarna z zakresu od -214,748.3648 do 214,748.3647. Rozmiar jednej wartości to 4 B.
Typy specjalne
Dane specjalne to dane, które nie pasują do innych kategorii.
bit Dane zawierają wartość 1 albo 0. Używaj ich, gdy chcesz przedstawić zagadnienia typu PRAWDA lub FAŁSZ albo TAK lub NIE.
cursor Typ danych używany przez zmienne lub parametry wyjściowe procedur
składowanych, które zawierają referencje do kursora.
timestamp Typ danych używany do wskazania ciągu aktywności
uniqueidentifier Dane są 16-bajtowymi szesnastkowymi liczbami wskazującymi na globalnie unikalne identyfikatory (GUID). GUID są użyteczne, gdy wiersz musi być unikalny pośród wielu wierszy.
SQL_variant Ten typ danych przechowuje wartości różnych typów z wyjątkiem typów text, ntext, timestamp, image oraz sql_variant.
table Typ danych przechowujący zestaw wyników do dalszego przetwarzania. Może być używany tylko do definiowania zmiennych lokalnych lub wartości zwracanych przez funkcje użytkownika.
Typy Unicode Dane tekstowe zapisane w postaci Unicode. Zajmują one dwukrotnie więcej miejsca niż zwykłe dane tekstowe.
nchar Dane o stałej przypisanej długości (do 4000 znaków Unicode).
nvarchar Dane o różnej długości aż do zdefiniowanej maksymalnej (do 4000).
ntext Dane mogą długością przekraczać 4000 znaków Unicode.
Przestrzenne typy danych
Dane przestrzenne to dane, które identyfikują geograficzne lokalizacje i kształty, w szczególności na kuli ziemskiej
Point Lokalizacja
MultiPoint Seria punktów
LineString Seria obejmująca zero lub więcej punktów połączonych liniami
MultiLineString Zestaw obiektów LineString Polygon Spójny region opisany przez zbiór
zamkniętych obiektów LineString
Kategoria Opis Typ danych Szczegóły MultiPolygon Zestaw wielokątów
GeometryCollection Kolekcja typów geometrycznych
Oczywiście nie będziesz używać wszystkich typów danych, ale kilku będziesz używać prawie zawsze podczas pracy z serwerem Microsoft SQL Server.
Perspektywy
W najprostszym rozumieniu perspektywa (ang. view) jest wirtualną tabelą. W szerokim znaczeniu perspektywa jest odwzorowaniem globalnego schematu bazy danych na schemat „zewnętrzny”, przystosowany do potrzeb i przyzwyczajeń konkretnego użytkownika.
Perspektywy stosujemy, aby uprościć (sobie lub użytkownikowi) życie. Powodów stosowania perspektyw jest wiele:
• Uproszczenie z punktu widzenia użytkownika modeli pojęciowych – dzięki temu możemy znacznie uprościć schemat bazy, a tym samym ułatwić użytkownikowi dostęp do danych.
• Dostosowanie się do punktu widzenia i terminologii dziedziny zastosowań BD – zmiana schematu wprowadzana przez perspektywę może być wykorzystana na przykład do dostosowania nazw tabel i kolumn do języka, którym posługuje się użytkownik.
• Ograniczenie dostępu do obiektów – perspektywy powodują ukrycie przez użytkownikiem końcowym prawdziwego schematu bazy danych. Może to mieć również pozytywny wpływ na poprawę bezpieczeństwa danych, gdyż użytkownik nie ma bezpośredniego dostępu do schematu rzeczywistego.
• Współdziałanie systemów heterogenicznych (wspólny schemat) – w wypadku systemów rozproszonych, zbudowanych z różnych systemów baz danych, perspektywa może pomóc w ukryciu różnic.
• Przystosowanie starszych systemów do nowszych technologii i wymagań.
Z punktu widzenia użytkownika (i innych procesów) perspektywa powinna być dla niego przezroczysta, to znaczy, że można na niej wykonywać takie same operacje, jak na „prawdziwych”
tabelach. Warunek przezroczystości perspektyw jest bardzo trudny do spełnienia, gdyż dla pewnych odwzorowań danych przyjęte środki definicji perspektyw (np. SQL) mogą okazać się niewystarczające.
Rys. 5 Schemat działania perspektyw
This image cannot currently be displayed.
Rodzaje perspektyw
Perspektywy dzielimy na dwie grupy:
• perspektywy wirtualne
• perspektywy zmaterializowane Perspektywy wirtualne
Perspektywa wirtualna istnieje wyłącznie w postaci definicji. Wyliczenie (czyli wyznaczenie zbioru krotek danej perspektywy, zwane też materializacją perspektywy) następuje w momencie odwołania się do perspektywy. Wynik ten nie jest później nigdzie przechowywany. Oznacza to, że każde wywołanie perspektywy wirtualnej powoduje jej ponowne wyliczenie (materializację), co obciąża zasoby systemu.
Wadą perspektyw wirtualnych jest obciążanie systemu za każdym odwołaniem do procedury wirtualnej, a tym samym dłuższy czas oczekiwania na materializację perspektywy. Perspektywy wirtualne mają też szereg zalet.
W sytuacji stosowania perspektyw wirtualnych nie ma dublowania danych oraz problemów z aktualizacją danych i problemów z przetwarzaniem transakcji.
Perspektywy zmaterializowane
Perspektywa zmaterializowana jest wyliczana (materializowana) w czasie pierwszego użycia.
Następnie wynik tego wyliczenia (dane) są przechowywane, aby móc ich użyć przy ponownym wywołaniu perspektywy. Dzięki temu kolejne wywołanie perspektywy nie obciąża systemu, a czas dostępu do danych znacznie się zmniejsza.
Przykładowe rozwiązanie
Posiadając gotowe, przetestowane środowisko bazodanowe przystępujemy do założenia pierwszej bazy danych. Do zarządzanie bazami danych, w tym ich tworzenia jaki i usuwania służy narzędzie Management Studio dostępne wraz z podstawową instalacją SQL Server.
Po uruchomieniu narzędzia i połączeniu się z danym serwerem widzimy obszar Object Explorer wraz z drzewem obiektów Databases. Wybierając opcję tworzenia nowej bazy musimy sprecyzować jej kilka podstawowych parametrów. Oprócz nazwy dla nowej bazy podajemy lokację i początkową wielkość pliku dla danych i pliku dla dziennika transakcji. Plików tych może być sprecyzowanych więcej, jednakże minimum to określenie jednego pliku każdego typu, co pokazuje rys. 6.
Rys. 6 Tworzenie nowej bazy danych
Zaraz po utworzeniu bazy warto przyjrzeć się dostępnym opcjom i od razu dokonać wstępnej konfiguracji. Przed wypełnieniem bazy danymi należy określić parametry takie jak poziom kompatybilności bazy z poprzednimi wersjami serwera SQL, dostępność i widoczność przez poszczególne grupy użytkowników czy zachowanie plików bazy w przypadku zbliżenia się do poziomu zapełnienia danymi.
Jedną z ważniejszych opcji, która należy zainteresować się od samego początku jest Recovery model. Opcja ta decyduje o tym, jak dużo informacji o modyfikacjach danych będzie zapisywana do dziennika transakcji bazy danych.
Listę opcji bazy danych znajdziesz w systemie pomocy Books Online pod hasłem „database options”
(najszybciej znajdziesz ten fragment pomocy używając indeksu dostępnego w Books Online). W tym kroku dodamy wymagane tabele bazy danych i określimy ich atrybuty.
Po ustaleniu podstawowych parametrów bazy można przejśc do kroku określania jej wyglądu, czyli tabel i związków pomiędzy nimi. Rozwijając w obszarze Object Explorer drzewo naszej bazy danych widzimy wszystkie obiekty wchodzące w jej sklad, w tym tabele. Po wybraniu opcji tworzenia nowej tabeli należy sprecyzować przynajmniej jeden atrybut, czyli kolumnę. Dla danej kolumny wybieramy nazwę i typ danych, które będzie ona przechowywała. Tutaj należy zdecydować, czy kolumna ma zawsze zawierać jakieś dane, czy może przechowywać wartości puste (ang. null). Do kontroli nad warościami pustymi służy pole Allow Nulls, co ilustruje rys. .
Rys. 7 Określanie nazwy i typu danych dla kolumny
Pozostało nam jeszcze ustalić identyfikator naszej tabeli – klucz główny. Kolumna zawierająca unikalne dane jest dobrym kandydatem dla klucza głównego.
Typowa tabela zawiera wiele kolumn o różnych typach danych oraz klucz główny. Dane przechowywane w poszczególnych kolumnach są ze sobą skojarzone np. dla tabeli zawierającej dano o pracy dyplomowej studenta będą tam kolumny reprezentujące temat pracy, datę obrony czy ocenę z obrony.
Rys. 8 Główna tabela bazy PraceDyplomowe
Najczęstrzym typem związku pomiędzy dwiema tabelami jest wiele-do-wielu. Niestety tego typu związku nie można w bezpośredni sposób zamodelować w relacyjnej bazie danych. Aby obejść ten problem, tworzymy niejako sztuczną, pośrednią tablę. Tabela ta posiada tylko dwie kolumny, wskazujące na łączone tabele.
Ponieważ dla tej tabeli unikalna będzie zarówno jedna jak i druga kolumna, klucz główny możemy założyć na obydwu jednocześnie. Ilustruje to rys. .
Rys. 9 Tabela Autorzy z kluczem głównym na dwóch kolumnach
Posiadając zdefiniowane wszystkie tabele w bazie danych, następnym krokiem jest utworzenie związków pomiędzy nimi. Najwygodniej utworzyć je na graficznym diagramie bazodanowym dostępnym z poziomu Management Studio.
Dla przykładowych tabel po stworzeniu związku zostanie utworzony klucz obcy w tabeli Autorzy jako identyfikator powiązanej tabeli Osoba.
Jako rezultat powinniśmy posiadać diagram tabel i związków pomiędzy nimi bardzo zbliżony do początkowego diagramu ERD.
Rys. 7 Tworzenie pozostałych kluczy obcych
Posiadając gotową bazę danych warto zrobić jej kopię zapasową przed dalszymi eksperymentami.
Narzędzie Management Studio umożliwia łatwe wykonywanie kopii zapasowej i przywracanie z niej bazy danych. Rozpoczęcie procedury tworzenia kopii bezpieczeństwa ilustruje rys. 8.
Rys. 8 Uruchamianie procedury kopii bezpieczeństwa
Jeżeli jest to tylko możliwe, należy zmienić nazwę pliku kopii bezpieczeństwa i jego domyślną lokację. Przeważnie w sieci istnieje dedykowany serwer plików, na którym przechowywane są pliki kopii bezpieczeństwa.
W celu weryfikacji pliku kopii wygodnie jest odzyskać tak utworzoną bazę do innej, roboczej bazy danych. Taką roboczą bazę można przeglądać pod względem zgodności i testować nawet na innym serwerze bazodanowym. Procedurę odzyskiwania dazy z kopii bezpieczeństwa ilustruje rys. 9.
Rys. 9 Odzyskiwanie bazy danych z pliku backup
Porady praktyczne
Zanim przystąpisz do implementacji bazy danych na serwerze, uważnie przyjrzyj się diagramowi ERD lub diagramowi bazodanowemu. Zrozumienie relacji zawartych na tych diagramach pozwoli uniknąć błędów w logice bazy. Błędy popełnione na tak wczesnym etapie będą negatywnie wpływać niemal na każde późniejsze działanie systemu bazodanowego. I z drugiej strony, im więcej uwagi poświęcimy prawidłowemu projektowi bazy tym bardziej zaprocentuje to w przyszłości.
Upewnij się, że każda tabela ma założony klucz główny, bez niego nie zdziałasz za wiele. Klucz główny oprócz funkcji identyfikacji danej tabeli pełni również rolę indeksu, który może przyśpieszyć obsługę danych. Jest on elementem niezbędnym w tabelach systemu bazodanowego niezależnie od platformy.
Uważnie przemyśl sprawę doboru typów danych. Nie mogą być one za małe dla przechowywanej wartości, z drugiej strony szastanie zbyt obszernymi typami sumarycznie zmniejsza wydajność bazy.
W szczególności przyjrzyj się nowym możliwościom jakie oferuje w zakresie typów danych MS SQL 2008. Współczesne wymagania co do obsługi danych typu pliki czy hierarchia obiektów znalazły swoje odzwierciedlenie w nowych serwerach baz danych.
Zawsze zapisuj tworzone obiekty typu tabele, diagramy. System nie robi tego automatycznie!
Zapominanie o zapisywaniu swojej pracy może skutkować przykrymi konsekwencji utraty informacji i co za tym idzie czasu użytkownika serwera baz danych. Wiele operacji w ogóle nie zostanie wykonana jeżeli np.: zaktualizowana struktura tabeli nie zostanie zapisana do pliku bazy.
Uwagi dla studenta
Jesteś przygotowany do realizacji laboratorium jeśli:
• rozumiesz, co oznacza relacja i czym różni się od powiązania w relacyjnych bazach danych
• rozumiesz zasady przypisywania odpowiednich typów danych do atrybutów tabeli
• wiesz jak założyć bazę danych i ustalić jej podstawowe parametry
• umiesz podać przykład diagramu ERD dla typowego zbioru danych
Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.
Dodatkowe źródła informacji
1. William R. Stanek, Vademecum Administratora Microsoft SQL Server 2005, Microsoft Press, 2006
Kompleksowe opracowanie na temat zaplanowania i wdrożenia system bazodanowego opartego o MS SQL Server 2005 w małym i średnim przedsiębiorstwie. Autorzy postawili na formułę przedstawiania wielu problemów z praktyki administratora baz danych oraz możliwych dróg do ich rozwiązania. Książka jest adresowana do praktykujących użytkowników.
2. Wiesław Dudek, Bazy danych SQL. Teoria i praktyka, Helion, 2006
Książka przedstawia większość zagadnień związanych z przechowywaniem i przetwarzaniem danych w współczesnych systemach bazodanowych. Znajdują się w niej informacje o relacyjnym i obiektowym modelu danych oraz najczęściej stosowanych systemach zarządzania bazami danych.
3. Strona domowa SQL Server 2008, http://www.microsoft.com/sql/2008/default.mspx
Tutaj znajdziemy wszystkie podstawowe informacje na temat MS SQL Server 2008 oraz nowości z nim związane.
Laboratorium podstawowe
Problem 1 (czas realizacji 45 min)
W firmie National Insurance trwa właśnie sezon urlopowy. Jako jedyny obecny administrator baz danych postanowiłeś przetestować implementacje bazy danych Prace Dyplomowe, która jest częścią twojego semestralnego projektu zaliczeniowego. Wytyczne do bazy jasno określa zaprojektowany przez kolegę z zespołu diagram. Postanowiłeś użyć dostępnego systemu Microsoft SQL Server 2008 wraz z graficznym interfejsem SQL Server Management Studio do wykonania tego zadania.
Zadanie Tok postępowania
1. Utwórz bazę danych
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
• W oknie Object Explorer kliknij prawym przyciskiem myszy folder Databases i z menu kontekstowego wybierz New Database.
• W pole Database name wpisz nazwę bazy danych PraceDyplomowe.
• Ustaw początkowe wielkości plików na 4 MB dla pliku danych i 1 MB dla pliku dziennika transakcji.
Rys. 10 Tworzenie nowej bazy danych
• Kliknij przycisk OK.
2. Ustaw opcje • Kliknij prawym przyciskiem myszy bazę danych PraceDyplomowe i z
bazy danych menu kontekstowego wybierz Properties.
• W oknie Database Properties - PraceDyplomowe kliknij po lewej stronie opcję Options.
• Zmień ustawienie opcji Recovery model na Simple.
Rys. 11 Ustawianie właściwości bazy danych
• Kliknij przycisk OK.
Listę opcji bazy danych znajdziesz w systemie pomocy Books Online pod hasłem database options (najszybciej znajdziesz ten fragment pomocy używając indeksu dostępnego w Books Online).
3. Dodaj tabele do bazy danych
W tym kroku dodamy wymagane tabele bazy danych i określimy ich atrybuty. Zaczniemy od prostych tabel.
• Kliknij prawym przyciskiem myszy pole Tables w rozwiniętym drzewie bazy PraceDyplomowe.
• Kliknij opcję NewTable.
• Kliknij pole Column name i wpisz nazwę ID_TypStudiow.
• Kliknij pole Data Type i z listy rozwijanej wybierz smallint.
• Odznacz pole Allow null w celu zapobiegania wpisywania do tej kolumny wartości null.
Rys. 12 Określanie nazwy i typu danych dla kolumny
• Ustal nazwę dla nowej tabeli klikając ikonę dyskietki z górnego paska zadań.
• Wprowadź nazwę TypStudiow i kliknij przycisk OK.
Rys. 16 Tworzenie nowej tabeli
4. Ustal klucz główny dla tabeli
• Kliknij prawym klawiszem nazwę kolumny.
• Kliknij opcję Set Primary Key.
• Zapisz tabelę klikając ikonę dyskietki.
Rys. 17 Tworzenie klucza głównego tabeli
5. Uwórz pozostałe proste tabele
• Bazując na poprzednim schemacie postępowania utwórz tabele Stopien i SlowoKlucz zawierające odpowiednio kolumny ID_Stopien i ID_SlowoKlucz, obydwie z typem danych varchar(25).
• Jako rezultat powinieneś zobaczyć listę trzech tabel w polu Tables bazy PraceDyplomowe.
Rys. 18 Pozostałe proste tabele bazy PraceDyplomowe
6. Utwórz główne tabele bazy
Pora na utworzenie głównej tabeli naszej bazy danych. Tabela PracaDyplomowa jest punktem centralnym bazy i zawiera kolumny ID_Pracy, Temat, Data, Ocena.
• Kliknij prawym przyciskiem myszy pole Tables i wybierz opcję NewTable.
— Wprowadź nazwę dla nowej kolumny ID_Pracy.
— Ustal typ danych dla tej kolumny jako smallint.
— Wprowadź nazwę dla następnej kolumny jako Temat.
— Ustal typ danych jako varchar(100).
— Wprowadź nazwę dla trzeciej kolumny jako Data.
— Jako typ danych ustal datetime.
— Wprowadź nazwę dla ostatniej kolumny jako Ocena.
— Jako typ danych ustal smallint.
• Dla każdej kolumny odznacz opcję Allow null.
• Ustal klucz główny klikając prawym przyciskiem myszy kolumnę ID_Pracy i wybierając opcję Set Primary Key.
• Zapisz tabelę pod nazwą PracaDyplomowa klikając ikonę dyskietki.
Rys. 19 Główna tabela bazy PraceDyplomowe
Następną dużą tabelą jest tabela Osoba przechowująca informacje o osobach wykonujących prace dyplomowe oraz wykładowcach i recenzentach
• Kliknij prawym przyciskiem myszy pole Tables i wybierz opcję New Table.
— Wprowadź nazwę dla nowej kolumny ID_Osoby.
— Ustal typ danych dla tej kolumny jako smallint.
— Wprowadź nazwę dla następnej kolumny jako Imie.
— Ustal typ danych jako varchar(50).
— Wprowadź nazwę dla trzeciej kolumny jako Nazwisko.
— Jako typ danych ustal varchar(50).
— Wprowadź nazwę dla ostatniej kolumny jako Nr_Indeksu.
— Jako typ danych ustal varchar(10).
• Dla każdej kolumny odznacz opcję Allow null.
• Ustal klucz główny klikając prawym przyciskiem myszy na kolumnie ID_Osoby i wybierając opcję Set Primary Key.
• Zapisz tabelę pod nazwą Osoba klikając ikonę dyskietki.
Rys. 13 Tabela Osoba
7. Utwórz tabele pomocnicze
• Kliknij prawym przyciskiem myszy pole Tables i wybierz opcję New Table.
— Wprowadź nazwę dla nowej kolumny ID_Osoby.
— Ustal typ danych dla tej kolumny jako smallint.
— Wprowadź nazwę dla następnej kolumny jako ID_Pracy.
— Ustal typ danych jako smallint.
• Dla każdej kolumny odznacz opcję Allow null.
Ponieważ dla tej tabeli unikalna będzie zarówno jedna jak i druga kolumna, klucz główny możemy założyć na obydwu jednocześnie.
• Zaznacz z wciśniętym klawiszem Shift obydwie kolumny.
• Ustal klucz główny klikając prawym przyciskiem myszy na zaznaczeniu i wybierz opcję Set Primary Key.
• Zapisz tabelę pod nazwą Autorzy klikając ikonę dyskietki.
Rys. 14 Tabela Autorzy z kluczem głównym na dwóch kolumnach
8. Utwórz pozostałe tabele pomocnicze
• Bazując na poprzednim schemacie postępowania utwórz tabele Slownik i Recenzent, zawierające odpowiednio kolumny ID_SlowoKlucz, ID_Pracy oraz ID_Osoby, wszystkie z typem danych smallint.
• Jako rezultat powinieneś zobaczyć listę wszystkich tabel w polu Tables bazy PraceDyplomowe.
Rys. 15 Wszystkie tabele bazy PraceDyplomowe
9. Utwórz związki pomiędzy
• Kliknij prawym klawiszem myszy pole Database Diagrams bazy PraceDyplomowe.
tabelami • Wybierz opcję New Database Diagram.
• W nowym oknie dodaj wszystkie nazwy tabel klikając przycisk Add.
• Kliknij przycisk Close.
• Kliknij i przeciągnij kursor myszki pomiędzy pole ID_Osoby tabeli Osoba a polem ID_Osoby tabeli Autorzy.
Rys. 16 Tworzenie związku pomiędzy tabelami
• W nowym oknie dotyczącym właściwości związku kliknij przycisk OK.
• Zostanie utworzony klucz obcy w tabeli Autorzy jako identyfikator powiązanej tabeli Osoba. Zatwierdź klikając przycisk OK.
Rys. 17 Tworzenie klucza obcego
• Powtórz tą operację dla każdego związku pomiędzy tabelami zgodnie z diagramem bazodanowym. Jako rezultat powinieneś otrzymać diagram jak na rys. 18.
Rys. 18 Tworzenie pozostałych kluczy obcych
10. Wykonaj kopię zapasową bazy
• Zamknij wszystkie okna z diagramem i definicjami tabel.
• Kliknij prawym klawiszem myszy na pole PraceDyplomowe.
• Z menu wybierz opcję Tasks -> Back Up.
Rys. 26 Uruchamienie procedury kopii bezpieczeństwa
• W nowym oknie możesz zmienić nazwę pliku kopii bezpieczeństwa i jego domyślną lokację. Kliknij przycisk OK.
• Po chwili powinien pojawić się komunikat o prawidłowym zakończeniu tworzenia kopii bezpieczeństwa.
Rys. 27 Tworzenie kopii bezpieczeństwa zakończone sukcesem
• Kliknij przycisk OK.
11. Odzyskaj bazę danych z kopii bezpieczeństwa
• Załóż nową bazę danych o nazwie PraceDyplomowe_TMP.
• Kliknij prawym przyciskiem myszki nazwę nowej bazy danych.
• Z menu wybierz opcję Tasks -> Restore -> Database.
Rys. 28 Odzyskiwanie bazy danych z kopii bezpieczeństwa
• W nowym oknie kliknij pole From device.
• Kliknij ikonę listy wyboru […].
• Kliknij przycisk Add.
• Wskaż lokację pliku z kopią zapasową bazy danych.
Rys. 29 Wskazywanie pliku z kopią zapasową
• Kliknij przycisk OK.
• Kliknij pole Options z listy po lewej stronie.
• Zaznacz pole Overwrite an existing database.
• Kliknij przycisk OK.
Odzyskanie bazy danych PraceDyplomowe zakończyło się sukcesem.
Możesz sprawdzić, czy nic po drodze nie zginęło, przeglądając strukturę bazy w obszarze Object Explorer.
Laboratorium rozszerzone
Zadanie 1 (czas realizacji 90 min)
Zachęcony sukcesem w utworzeniu bazy danych PraceDyplomowe postanowiłeś ją rozbudować.
Przyglądając się uważnie diagramowi dostarczonemu wraz z dokumentacją bazy zauważyłeś, że przy tworzeniu bazy umknęła Ci tabela reprezentująca nośnik. W bazie nie ma też jak dotąd żadnych danych. Postanowiłeś podjąć następujące kroki:
• dodać tabelę z typem nośnika pracy dyplomowej
• rozbudować bazę o możliwość przechowywania informacji o wydziale i kierunku studenta
• dodać możliwość zaznaczenia liczby dostarczonych kopii pracy z podziałem na wersję papierową i elektroniczną
• dodać możliwość umieszczenia przez recenzenta recenzji pracy i własnej oceny
• zastanawiasz się też, czy byłaby możliwość automatycznego obliczania średniej z oceny z recenzji, obrony i promotora pracy
• zasilić bazę w odpowiednią ilość danych
Tak przygotowaną bazę należy skopiować do bezpiecznej lokacji. Bazując na opcjach dostępnych w narzędziu Management Studio zastanów się, jak najprościej możesz zarządzać kopiami bezpieczeństwa.
