ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
Wersja 1.0
Definiowanie i zarządzanie bazą
danych
Spis treści
ITA-101 Bazy Danych
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
Wersja 1.0
Definiowanie i zarządzanie bazą
danych
Spis treści
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 2/27
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ą. Moduł przybliży wszystkie te zagadnienia.
Cel modułu
Celem tego dwiczenia jest przedstawienie czytelnikowi typowych zagadnieo
związanych z zakładaniem i konserwacją bazy danych na serwerze MS SQL
Server 2008.
Uzyskane kompetencje
Po zrealizowaniu modułu będziesz:
potrafił zaplanowad instalację bazy danych
rozumiał czym są pliki bazy, tabele, diagramy, klucze
wiedział, jak wykonad kopię bezpieczeostwa bazy oraz jak przywrócid
bazę w razie awarii
Wymagania wstępne
Przed przystąpieniem do pracy z tym modułem powinieneś:
wiedzied, jak używad oprogramowania Microsoft Virtual PC
znad podstawy obsługi systemu Windows 2000 lub nowszego
rozumied, jakie są elementy diagramu ERD i powiązania między nimi
znad 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śd
wykonania wcześniej modułu 1 i modułu 2.
Moduł 3
Dodatek
Moduł 1
Moduł 2
Moduł 4
Moduł 5
Moduł 6
Moduł 7
Moduł 8
Moduł 9
Moduł 10
Moduł 11
Moduł 12
Moduł 13
Rys. 1 Mapa zależności modułu
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 3/27
Przygotowanie teoretyczne
Przykładowy problem
Jesteś administratorem systemu bazodanowego w firmie National Insurance. Analitycy w dziale
deweloperskim stworzyli diagramach 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.
PracaDyplomowa
PK
ID_pracy
Data
Ocena
FK1
ID_rodzajup
FK2
ID_nosnika
ID_promotora
Autor
PK,FK1
ID_osoby
PK,FK2
ID_pracy
OSOBA
PK,FK3
ID_osoby
imie
nazwisko
nr_indeksu
FK1
ID_stopnian
ID_pracy
Recenzent
PK,FK1
ID_osoby
PK,FK2
ID_pracy
Nosnik
PK
ID_nosnika
nosnik
RodzajPracy
PK
ID_rodzajup
rodzaj
SlowoKluczowe
PK
ID_sk
slowo
StopienNaukowy
PK
ID_stopnian
stopien
1:n
1
:n
n:1
1: (1...3)
n
:1
SLOWNIK
PK,FK1
ID_sk
PK,FK2
ID_pracy
n
:1
1
:n
n:1
n
:1
1:n
Rys. 2 Schemat działania perspektyw
Twoim zadaniem jest założenie bazy danych wraz z odpowiednimi tabelami. Ustanowienie kluczy
głównych, powiązao między nimi i określenie dogodnych typów danych. W koocowej fazie
powinieneś wypełnid bazę przykładowymi danymi i zapewnid jej kopię bezpieczeostwa.
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
zaplanowad wiele ustawieo dotyczących różnych aspektów działania bazy.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 4/27
Rozmieszczenie plików bazy danych
Baza danych składa się z plików. Plik bazy można podzielid 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ększyd) 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 umieszczad na innym dysku fizycznym niż pliki danych – w
przypadku awarii jednego z dysków tracimy tylko częśd bazy.
Tabele często występujące razem w złączeniach (ang. joins) należy umieszczad 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 umieszczad na innych dyskach fizycznych niż ich bazowe
tabele – zyskujesz większą wydajnośd zapisu danych.
Tabele często modyfikowane należy oddzielid fizycznie (umieścid 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 bezpieczeostwo
i wydajnośd bazy.
Bezpieczeństwo fizyczne danych
Dane, jak i dziennik transakcji można dodatkowo zabezpieczyd przed ewentualnymi awariami
dysków fizycznych. Do tego celu należy użyd 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 byd 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 bezpieczeostwie systemów bazodanowych znajdziesz w module 11.
Przy tworzeniu bazy danych administrator musi zdecydowad o jej konfiguracji. Konfiguracja ta
dotyczy nie tylko plików bazy danych, ale także pewnych ustawieo 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śd i lokalizacja plików
wielkośd początkowa plików
sposób powiększania plików (powinien zostad wybrany automatyczny przyrost rozmiaru)
maksymalny rozmiar dla każdego z plików (alternatywnie można maksymalny rozmiar
pozostawid nieokreślony, ale wówczas administrator jest zmuszony do monitorowania
wolnego miejsca na dysku twardym)
nazwy logiczne plików
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 5/27
Rys. 3 Właściwości przykładowej bazy danych w SQL Server 2008
W praktyce należy pamiętad 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ążyd bazę
danych. Co za tym idzie, należy też zadbad 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 byd 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śd bazy.
Dziennik transakcji należy ustawid na początek na 20-30% rozmiaru plików danych. W trakcie pracy
bazy danych rozmiar ten można zmienid 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ą byd 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)
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 6/27
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 poleceo 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 zapytao 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śd 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
wskazad interesujący nas rekord w danej tabeli, a następnie odczytad go lub zmodyfikowad.
Tabele możemy utworzyd za pomocą narzędzi graficznych, np. w Management Studio rozwijając w
Object Explorer odpowiednie menu.
Możemy też posłużyd 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 6.
Podczas tworzenia tabeli należy zdefiniowad chociaż jedną kolumnę zawierającą dane.
Przechowywane dane mogą byd tylko ściśle określonego typu. Typ ten jest wspólny dla całej
kolumny.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 7/27
Typy danych
Typ danych jest pierwszą wielkością jaką definiuje użytkownik dla kolumny tabeli. Typ danych
kolumny kontroluje typ informacji jaka może byd przechowywana w kolumnie. Należy zdefiniowad
typ danych przez wpisanie za nazwą kolumny słowa kluczowego, może ono wymagad parametrów.
Po zdefiniowaniu typy danych kolumny tabeli , jest on stałą właściwością i nie może byd zmieniany.
Można również wykorzystad typy danych do definicji innych struktur przechowujących dane, takich
jak parametry i lokalne zmienne.
Przegląd typów danych dostępnych na platformie MS SQL Server prezentuje Tab. 1.
Tab. 1 Podstawowe typy danych dostępne w MS SQL Server
Kategoria
Opis
Typ danych
Szczegóły
Typy binarne Dane zawierają łaocuchy 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ą byd różnej długości i przekroczyd
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śd zajmuje 8 B).
smalldatetime
Data z zakresu od 1 stycznia 1900 do 6 lipca
2079 (jedna wartośd zajmuje 4 B).
Typy liczb
dziesiętnych
Dane liczbowe o dokładności do
ostatniej znaczącej cyfry.
decimal
Maksymalnie 38 cyfr, z czego wszystkie
mogą znajdowad 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 byd 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
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
Dane reprezentują ujemne lub
money
Wartośd monetarna z zakresu od –
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 8/27
Kategoria
Opis
Typ danych
Szczegóły
monetarne
dodatnie sumy pieniędzy.
922,337,203,685,477.5808 do
+922,337,203,685,477.5807. Rozmiar jednej
wartości to 8 B.
smallmoney
Wartośd 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śd 1 albo 0. Używaj ich,
gdy chcesz przedstawid 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 byd 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 byd
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ą przekraczad 4000
znaków Unicode.
Oczywiście nie będziesz używad wszystkich typów danych, ale kilku będziesz używad prawie zawsze
podczas pracy z serwerem MS SQL.
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 przyzwyczajeo konkretnego użytkownika.
Perspektywy stosujemy, aby uprościd (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ścid schemat bazy, a tym samym ułatwid użytkownikowi dostęp do danych.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 9/27
Dostosowanie się do punktu widzenia i terminologii dziedziny zastosowao BD – zmiana
schematu wprowadzana przez perspektywę może byd 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
koocowym prawdziwego schematu bazy danych; może to mied również pozytywny wpływ na
poprawę bezpieczeostwa 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 wymagao.
Z punktu widzenia użytkownika (i innych procesów) perspektywa powinna byd dla niego
przezroczysta, to znaczy, że można na niej wykonywad takie same operacje, jak na "prawdziwych"
tabelach. Warunek przezroczystości perspektyw jest bardzo trudny do spełnienia, gdyż dla
pewnych odwzorowao danych przyjęte środki definicji perspektyw (np. SQL) mogą okazad się
niewystarczające.
Rys. 5 Schemat działania perspektyw
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.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 10/27
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żyd 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 okno Object Explorer wraz
z drzewem obiektów Databases. Wybierając opcję tworzenia nowej bazy musimy sprecyzowad jej
kilka podstawowych parametrów. Oprócz nazwy dla nowej bazy podajemy lokację i początkową
wielkośd dla pliku dla danych i pliku dla dziennika transakcji. Plików tych może byd 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 przyjrzed się dostępnym opcjom i od razu dokonad wstępnej
konfiguracji. Przed wypełnieniem bazy danymi należy określid parametry takie jak poziom
kompatybilności bazy z poprzednimi wersjami serwera SQL, dostępnośd i widocznośd przez
poszczególne grupy użytkowników czy zachowanie plików bazy w przypadku zbliżenia się do
poziomu zapełnienia danymi. Rys. 7 przedstawia fragment możliwych parametrów bazy.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 11/27
Rys. 7 Tworzenie nowej bazy danych
Jedną z ważniejszych opcji, która należy zainteresowad 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 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śnia jej wyglądu czyli
tabel i związków pomiędzy nimi. Rozwijając w 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 sprecyzowad przynajmniej jeden atrybut czyli kolumnę. Dla danej kolumny wybieramy
nazwę i typ danych które będzie ona przechowywała. Tutaj należy zdecydowad czy kolumna ma
zawsze zawierad jakieś dane czy może przechowywad wartości puste (ang. null). Do kontroli nad
warościami pustymi służy pole Allow Nulls co ilustruje Rys. 8.
Rys. 8 Określenie nazwy i typu danych dla kolumny
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 12/27
Pozostało nam jeszcze ustalid identyfikator naszej tabeli – klucz główny. Kolumna zawierająca
unikalne dane jest dobrym kandydatem dla klucza głównego.
Rys. 9Tworzenie klucza głównego tabeli
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 takie jak Temat pracy, Data obrony czy
Ocena z obrony.
Rys. 10 Głowna 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ów zamodelowad w relacyjnej bazie danych. Aby obejśd ten
problem tworzymy niejako sztuczną, pośrednią tablę. Tabela ta posiada tylko z 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żyd na obydwu jednocześnie. Ilustruje to Rys. 11
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 13/27
Rys. 11 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 utworzyd je na graficznym diagramie bazodanowym
dostępnym z poziomu Management Studio.
Związki można utworzyd po prstu przeciągając kursor myszki pomiędzy odpowiednimi atrybutami
tabel co ilustruje Rys. 12.
Rys. 52 Tworzenie związku pomiędzy tabelami
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 posiadad diagram tabel i związków pomiędzy nimi bardzo zbliżony do
początkowego diagramu ERD.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 14/27
Rys. 13 Tworzenie pozostałych kluczy obcych
Posiadając gotową bazę danych warto zrobid 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 bezpieczeostwa ilustruje Rys. 14.
Rys. 14 Uruchomienie procedury kopii bezpieczeostwa
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 15/27
Jeżeli jest to tylko możliwe należy zmienid nazwę pliku kopii bezpieczeostwa i jego domyślną
lokację. Przeważnie w sieci istnieje dedykowany serwer plików, na którym przechowywane są pliki
kopii bezpieczeostwa.
Rys. 15 Tworzenie kopii bezpieczeostwa zakooczone sukcesem
W celu weryfikacji pliku kopii wygodnie jest odzyskad tak utworzoną bazę do innej, roboczej bazy
danych. Taką roboczą bazę można przeglądad pod względem zgodności i testowad nawet na innym
serwerze bazodanowym. Procedurę odzyskiwania dazy z kopii bezpieczeostwa ilustruje Rys. 16.
Rys. 16 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 umożliwi uniknięcie wykonania błędów w logice bazy.
Upewnij się, że każda tabela ma założony klucz główny. Bez niego nie zdziałasz za wiele.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 16/27
Należy uważnie przemyśled sprawę doboru typów danych. Nie mogą byd one za małe dla
przechowywanej wartości, z drugiej strony szastanie zbyt obszernymi typami sumarycznie
zmniejsza wydajnośd bazy.
Zawsze zapisuj tworzone obiekty typu tabele, diagramy. System nie robi tego
automatycznie!
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żyd bazę danych i ustalid jej podstawowe parametry
umiesz podad 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
2. Wiesław Dudek, Bazy danych SQL. Teoria i praktyka, Helion, 2006
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 17/27
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ś przetestowad 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żyd dostępnego systemu MS SQL
Server 2008 wraz z graficznym interfejsem Management Studio do wykonania tego zadania.
Zadanie
Tok postępowania
1. Tworzenie
bazy 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 MS 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. 16 Tworzenie nowej bazy danych
Kliknij przycisk OK.
2. Ustawienie
opcji bazy danych
Kliknij prawym przyciskiem myszy na bazie danych PraceDyplomowe i z
menu kontekstowego wybierz Properties.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 18/27
W oknie Database Properties - PraceDyplomowe kliknij po lewej
stronie opcję Options.
Zmieo ustawienie opcji Recovery model na Simple.
Rys. 17 Tworzenie nowej bazy danych
Kliknij przycisk OK.
Listę opcji bazy danych znajdziesz w Books Online pod hasłem
database options (najszybciej znajdziesz ten fragment pomocy
używając indeksu dostępnego w Books Online)
3. Dodanie tabel
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.
Określimy teraz pierwszą kolumnę tej tabeli wraz z typem danych.
Kliknij pole Column name i wpisz nazwę ID_TypStudiow.
Kliknij na polu data type i z listy rozwijanej wybierz smallint.
Odznacz pole Allow null w celu zapobiegania wpisywania do tej
kolumny wartości null.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 19/27
Rys. 19 Określenie nazwy i typu danych dla kolumny
Ustal nazwę dla nowej tabeli klikając ikonę dyskietki z górnego paska
zadao.
Wprowadź nazwę TypStudiow i kliknij przycisk OK.
Rys. 20 Tworzenie nowej tabeli
4. Ustalenie
klucza głównego
dla tabeli
Kliknij prawym klawiszem nazwę kolumny.
Kliknij opcję Set Primary Key.
Zapisz tabelę klikając ikonę dyskietki.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 20/27
Rys. 21 Tworzenie klucza głównego tabeli
5. Tworzenie
pozostałych
prostych table
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ś zobaczyd listę trzech tabel w polu Tables bazy
PraceDyplomowe.
Rys. 22 Pozostałe proste tabele bazy PraceDyplomowe
6. Utworzenie
głównych tabel
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.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 21/27
— 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. 23 Głowna 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ę
NewTable.
— 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. 24 Tabela Osoba
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 22/27
7. Utworzenie
tabel
pomocniczych
Kliknij prawym przyciskiem myszy pole Tables i wybierz opcję
NewTable.
— 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żyd 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. 25 Tabela Autorzy z kluczem głównym na dwóch kolumnach
8. Utworzenie
pozostałych tabel
pomocniczych
Bazując na poprzednim schemacie postępowania utwórz tabele Slownik
i Recenzent zawierające odpowiednio kolumny ID_SlowoKlucz,
ID_Pracy oraz ID_Osoby, ID_Pracy, wszystkie z typem danych smallint.
Jako rezultat powinieneś zobaczyd listę wszystkich tabel w polu Tables
bazy PraceDyplomowe.
Rys. 26 Wszystkie tabela bazy PraceDyplomowe
9. Utworzenie
związków
Kliknij prawym klawiszem myszy pole Database Diagrams bazy
PraceDyplomowe.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 23/27
pomiędzy
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. 27 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. 28 Tworzenie klucza obcego
Powtórz tą operację dla każdego związku pomiędzy tabelami zgodnie z
diagramem bazodanowym. Jako rezultat powinieneś otrzymad
następujący diagram:
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 24/27
Rys. 29 Tworzenie pozostałych kluczy obcych
10. Wykonanie
kopii zapasowej
bazy
Zamknij wszystkie okna z diagramem i definicjami tabel.
Kliknij prawym klawiszem myszy na pole PraceDyplomowe.
Z menu wybierz opcję Tasks -> Back Up.
Rys. 29 Uruchomienie procedury kopii bezpieczeostwa
W nowym oknie możesz zmienid nazwę pliku kopii bezpieczeostwa i
jego domyślną lokację. Kliknij przycisk OK.
Po chwili powinien pojawid się komunikat o prawidłowym zakooczeniu
tworzenia kopii bezpieczeostwa.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 25/27
Rys. 30 Tworzenie kopii bezpieczeostwa zakooczone sukcesem
Kliknij przycisk OK.
11. Odzyskanie
bazy danych z
kopii
bezpieczeostwa
Załóż nową bazę danych o nazwie PraceDyplomowe_TMP
Kliknij prawym przyciskiem myszki na nazwie nowej bazy danych.
Z menu wybierz opcję Tasks -> Restore -> Database.
Rys. 31 Tworzenie kopii bezpieczeostwa zakooczone sukcesem
W nowym oknie kliknij pole From device.
Kliknij ikonę listy wyboru *…+
Kliknij przycisk Add.
Wskaż lokację pliku z kopią zapasową bazy danych.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 26/27
Rys. 32 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 zakooczyło się sukcesem.
Możesz sprawdzid czy nic po drodze nie zginęło przeglądając strukturę
bazy w oknie Object Explorer.
Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
Moduł 3
ITA
-
101 Bazy Danych
Instalacja i konfiguracja MS SQL Server 2008
Strona 27/27
Laboratorium rozszerzone
Zadanie 1 (czas realizacji 90 min)
Zachęcony sukcesem w utworzeniu bazy danych PraceDyplomowe postanowiłeś ją rozbudowad.
Przyglądając się uważnie diagramowi dostarczonemu wraz z dokumentacją bazy zauważyłeś, że
przy tworzeniu bazy umknęła ci tabela nośnik. W bazie nie ma też jak dotąd żadnych danych.
Postanowiłeś podjąd następujące kroki:
dodad tabelę z typem nośnika pracy dyplomowej
rozbudowad bazę o możliwośd przechowywania informacji o wydziale i kierunku studenta
dodad możliwośd zaznaczenia liczby dostarczonych kopii pracy z podziałem na wersję
papierową i elektroniczną
dodad możliwośd umieszczenia przez recenzenta recenzji pracy i własnej oceny
zastanawiasz się też, czy byłaby możliwośd automatycznego obliczania średniej z oceny z
recenzji, obrony i promotora pracy
zasilid bazę w odpowiednią ilośd danych
Tak przygotowaną bazę należy skopiowad do bezpiecznej lokacji. Bazując na opcjach dostępnych w
narzędziu Management Studio zastanów się, jak najprościej możesz zarządzad kopiami
bezpieczeostwa.