plik

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA-101 Bazy danych

Strona i-3

Wprowadzenie

Informacje o kursie

Opis kursu
We współczesnej informatyce coraz większą rolę odgrywa przepływ
informacji. Dane są gromadzone i przekazywane w ilościach dotąd
niespotykanych. Od umiejętnego sterowania przepływem danych zależy los
wielu wielkich firm. Odpowiednia automatyzacja procesu przepływu
informacji daje ogromne wymierne korzyści. Bazy danych i systemy
zarządzania bazami danych służą właśnie temu, by móc przechowywać
nawet ogromne ilości danych bez narażenia na ich utratę oraz by móc
odpowiednio szybko i wygodnie sterować ich przepływem.

Bazy danych wdarły się zarówno do sieci lokalnych w firmach, gdzie
gromadzone są dane na potrzeby pracowników, jak również do Internetu,
gdzie dostęp do nich mają miliardy użytkowników na całym świecie.
Dynamiczny rozwój baz danych implikował powstanie wielu nowych
technologii programowania ukierunkowanych na jeszcze wydajniejsze
wykorzystanie baz danych w aplikacjach.

Z kolei administracja systemami zarządzania bazami danych stała się osobną
gałęzią informatyki, tak jak administracja systemami operacyjnymi
komputerów lub administracja sieciami komputerowymi. Wielu
pracodawców poszukuje wykwalifikowanych specjalistów z zakresu
określonych systemów zarządzania bazami danych (jak Oracle czy MS SQL
Server). Znajomość zarówno teorii baz danych, jak i konkretnego
środowiska pracy z nimi, jest więc okazją podniesienia swoich kwalifikacji.

Wykorzystując możliwości systemu zarządzania bazami danych Microsoft
SQL Server 2008 postaramy się w niniejszym podręczniku zilustrować
podstawowe własności baz danych (w szczególności relacyjnych baz
danych) oraz systemów zarządzania tymi bazami.

Mamy nadzieję, że podręcznik pozwoli Państwu na bliższe zapoznanie się z
tematyką baz danych oraz systemem Microsoft SQL Server 2008. Życzymy
owocnej pracy z naszym podręcznikiem.

Uzyskane kompetencje
Po zrealizowaniu kursu będziesz:

•

Zrozumieć schemat, zaprojektować i zoptymalizować prostą bazę
danych,

•

Administrować serwerem bazodanowym MS SQL Sever 2008 na
poziomie podstawowym,

•

Zaimplementować prostą bazę danych w systemie SZBD opartym o
MS SQL Sever 2008,

•

Tworzyć skrypty w języku T-SQL,

•

Monitorować i dokonywać tuningu baz danych,

•

Dbać o bezpieczeństwo systemów SZBD w podstawowym zakresie,

•

Używać języka XML w procesie komunikacji z SZBD,

•

Tworzyć raporty przy użyciu MS SQL Reporting Services

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA-101 Bazy danych

Strona i-4

Wymagania wstępne
Aby przystąpić do pracy z podręcznikiem musisz:

•

umieć obsługiwać komputer z zainstalowanym systemem
operacyjnym Microsoft Windows 9X/NT/2000/ME/XP/2003,

•

znać podstawowe zagadnienia dotyczące programowania (m.in.
wiedzieć, co to jest zmienna, procedura, pętla),

•

nie musisz znać teorii baz danych - poznasz ją czytając wykłady
zawarte w niniejszym podręczniku.

Zakres tematyczny kursu

Opis modułów
W Tab. 1 przedstawiony został opis modułów, zawierający podział na
zajęcia. Każde zajęcie jest zaplanowane na 90 minut. Wykładowca może
dostosować harmonogram do swoich potrzeb.

Tab. 1 Zakres tematyczny modułów

Numer moduł
Tytuł

Opis

Moduł 1

Budowa diagramów
ERD

W tym module zajmiemy się pierwszym krokiem, jaki
należy wykonać projektując bazę danych. Będzie nim
identyfikacja encji i narysowanie na diagramie, zwanym
diagramem ERD, zależności między nimi. Prawidłowy i
przejrzysty diagram ERD jest kluczowym czynnikiem
sukcesu dla zaprojektowania, a później eksploatacji bazy
danych.

Moduł 2

Instalacja i
konfiguracja MS SQL
Server 2008

W tym module znajdziesz informację o podstawowych
zadaniach administratora systemu bazodanowego. Do
zadań tych należy instalacja serwera baz danych,
konserwacja oraz aktualizacji serwisów serwera.
Prawidłowe przygotowanie środowiska pracy zapewni
stabilność oraz pozwoli na poznanie systemu
bazodanowego od podstaw.

Moduł 3

Definiowanie i
zarządzanie bazą
danych

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

Moduł 4

Wewnętrzna
struktura bazy
danych

W tym module znajdziesz informacje w jaki sposób w SQL
Server 2008 przechowywane są dane oraz w jaki sposób
przechowywane są podstawowe obiekty w bazie danych.

Moduł 5

Język SQL - DCL, DDL

Język SQL został opracowany w 1987 roku z myślą o
relacyjnych bazach danych. Składa się on z trzech
składowych: języka definiowania danych (DDL), języka
sterowania danymi (DCL) oraz języka operowania na
danych (DML). W module tym zostaną wprowadzone, a
następnie przedstawione na przykładach podstawowe
instrukcje języka definiowania danych – języka SQL DDL
oraz języka sterowania danymi – języka SQL DCL.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA-101 Bazy danych

Strona i-5

Moduł 6

Język SQL - DML

Język SQL składa się z trzech składowych: języka
definiowania danych (DDL), języka sterowania danymi
(DCL) oraz języka operowania na danych (DML). W
module tym zostaną wprowadzone, a następnie
przedstawione na przykładach podstawowe instrukcje
języka sterowania na danych – języka SQL DML

Moduł 7

Indeksy i transakcje

W module tym znajdziesz informacje na temat dostępu
fizycznego do danych oraz optymalizacji dostępu.
Poznasz indeksy i ich rodzaje a następnie dowiesz się
jakie operacje wykonywane są na indeksach. Dowiesz się,
że jest to parametr niezbędny do zapewnienia
rozsądnych czasów wyszukiwania informacji. W drugiej
części poznasz transakcje, które służą do zapewnienia
spójności bazy danych i mają wpływ na wydajność bazy
danych. Dowiesz się, że obsługa transakcji nie jest rzeczą
łatwą i wymaga rozwiązywania wielu trudnych
problemów.

Moduł 8

Programowanie
zaawansowane
w T-SQL

Programowanie w języku zapytań to ważna umiejętność.
Powinni ją opanować zarówno programiści, jak i
administratorzy. Różne dialekty języka SQL oferują różne
składnie, jednak reguły, jakimi powinien kierować się
tworzący kod, są te same niezależnie od SZBD. Bardzo
często opanowanie w zaawansowanym stopniu składni
jednego języka pozwala w przyszłości na łatwe
opanowanie innego. W module tym znajdziesz
informację na temat zaawansowanego programowania w
T-SQL.

Moduł 9

Procedury
składowane i
wyzwalacze

W module zostanie zaprezentowany sposób działania
oraz podstawy tworzenia procedur składowanych.
Dowiesz na czym polega różnica pomiędzy zwykłym
zapytaniem T-SQL a procedurą składowaną oraz co to jest
kompilacja i rekompilacja procedury. Zostanie
wprowadzony również specjalny rodzaj procedury
składowanej – wyzwalacz.

Moduł 10

Bezpieczeństwo w
bazach danych

W tym module dowiesz się, jak należy rozumieć
bezpieczeństwo baz danych oraz jakie są poziomy
bezpieczeństwa. Ponadto dowiesz się, jakim zagrożeniom
należy przeciwdziałać, a jakich nie da się uniknąć oraz jak
należy planować implementację poszczególnych
poziomów bezpieczeństwa w aplikacji bazodanowej.

Moduł 11

Praca z XML

Wymiana danych z relacyjnymi bazami danych może być
utrudniona ze względu na różnice programowo –
sprzętowe itp. Rozwiązaniem jest język XML, który jest
niezależny od standardów sprzętowych / programowych.

Moduł 12

Praca z Reporting
Services

Aby osiągnąć sukces na dzisiejszym, konkurencyjnym
rynku, przedsiębiorstwa gromadzące duże ilości danych
powinny wprowadzić rozwiązania biznesowe działające w
czasie rzeczywistym zapewniające bezproblemową,
skuteczną wymianę informacji pomiędzy własnymi
oddziałami, swoimi partnerami, a także klientami.
Microsoft SQL Server Reporting Services jest
rozwiązaniem, które pozwala szybko i komfortowo dzielić
i udostępniać dane biznesowe, przy niższych nakładach
rozmaitych zasobów.

ITA-101 Bazy danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 1

Wersja 1.0

Budowa diagramów ERD

Spis treści

Budowa diagramów ERD ...................................................................................................................... 1

Informacje o module ............................................................................................................................ 2

Przygotowanie teoretyczne ................................................................................................................. 3

Przykładowy problem .................................................................................................................. 3

Podstawy teoretyczne.................................................................................................................. 3

Przykładowe rozwiązanie ............................................................................................................. 7

Porady praktyczne ..................................................................................................................... 12

Uwagi dla studenta .................................................................................................................... 13

Dodatkowe źródła informacji..................................................................................................... 13

Laboratorium podstawowe ................................................................................................................ 14

Problem (czas realizacji 40 min)................................................................................................. 14

Laboratorium rozszerzone ................................................................................................................. 16

Zadanie 1 (czas realizacji 45 min) ............................................................................................... 16

Zadanie 2 (czas realizacji 45 min) ............................................................................................... 16

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 2/18

Informacje o module

Opis modułu

W  tym  module  zajmiemy  się  pierwszym  krokiem,  jaki  należy  wykonać
projektując  bazę  danych.  Będzie  nim  identyfikacja  encji  i narysowanie  na
diagramie,  zwanym  diagramem  ERD,  zależności  między  nimi.  Prawidłowy
i przejrzysty  diagram  ERD  jest  kluczowym  czynnikiem  sukcesu  dla
zaprojektowania, a później eksploatacji bazy danych.

Cel modułu

Celem modułu jest wykształcenie umiejętności budowania poprawnych,
przejrzystych

i dobrze

udokumentowanych

diagramów

ERD

z wykorzystaniem narzędzia MS VISIO.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:

•

rozumiał, czym jest diagram ERD,

•

rozumiał, w jaki sposób buduje się diagramy związków encji na
różnych poziomach abstrakcji,

•

umiał zbudować poprawny diagram ERD,

•

umiał dokonać przekształcenia diagramu ERD tak, aby był on
implementowany w relacyjnej bazie danych.

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:

•

rozumieć, czym jest baza danych i jakie powinna mieć cechy,

•

znać założenia modelu relacyjnego baz danych.

Mapa zależności modułu

Przed przystąpieniem do realizacji tego modułu nie są wymagane inne
moduły.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 3/18

Przygotowanie teoretyczne

Przykładowy problem

Wyobraź  sobie,  że  zostałeś  poproszony  o przygotowanie  bazy  danych  ułatwiającej  zarządzanie
przydziałem sal i zajęć na swoim wydziale na uczelni. Pani Jola zajmująca się przydzielaniem sal na
zajęcia chciałaby uzyskać narzędzie do kontroli i monitorowania obciążenia sal przez różne zajęcia
dydaktyczne  oraz  chciałaby  przy  tej  okazji  zminimalizować  liczbę  popełnianych  błędów.  Błędy
polegają najczęściej na tym, że w jednej sali umieszczane są w tym samym czasie różne zajęcia lub
na  tym,  że  ta  sama  grupa  studencka  ma  zajęcia  w różnych  salach  w jednym  czasie.  Pani  Jola
chciałaby  też  mieć  możliwość  szybkiego  generowania  raportów  o przydziale  sal  i zajęć.  Dla
uniknięcia  nieporozumień  przy  pracach  nad  narzędziem  wspomagającym  pracę  pani  Joli  zostałeś
poproszony o przygotowanie prostego i krótkiego dokumentu przedstawiającego, jakie dane będą
gromadzone  w bazie  danych  i jakie  będą  między  nimi  zależności.  Dokument  ten  powinien  zostać
zweryfikowany i zaakceptowany przez panią Jolę przed przystąpieniem do dalszych prac.

Podstawy teoretyczne

Przy modelowaniu baz danych możemy posłużyć się notacją graficzną modelowania danych –
diagramem związków encji (ERD, ang. Entity-Relationship Diagram). Jest to model sieciowy
opisujący na wysokim poziomie abstrakcji dane, które są przechowywane w systemie.

Model ERD budowany jest przez analityka. Służy on do zobrazowania w sposób zrozumiały zarówno
dla projektanta, jak i osoby niemającej wykształcenia informatycznego (np. klienta) obiektów
i związków zachodzących w projektowanej dziedzinie problemowej.

Model ERD nie jest związany z konkretną implementacją systemu (np. na serwerze MS SQL czy
Oracle), choć jego odmiany mogą zawierać informacje specyficzne dla danego języka lub
środowiska implementacyjnego. Staje się on wówczas modelem projektowym

Encja

Encja (ang. entity) jest to coś, co istnieje, co odróżnia się od innych, o czym trzeba mieć informacje.
Zbiory encji reprezentują zbiór elementów występujących w rzeczywistym świecie i każdy element
tego zbioru musi posiadać następujące cechy:

•

Każdy element musi być unikalny, jednoznacznie określony, w celu odróżnienia go od
pozostałych.

•

Każdy element musi odgrywać jakąś rolę w projektowanym systemie, nie może zdarzyć się
sytuacji, w której system może działać bez dostępu do danego elementu.

•

Każdy element powinien być opisany przez odpowiednią liczbę atrybutów.

W diagramach ERD encja jest reprezentowana przez prostokąt, a jej nazwa powinna być
rzeczownikiem.

Atrybut

Atrybut (ang. attribute) jest pewną własnością encji, o której chcemy przechowywać informacje.
Atrybut jest reprezentowany przez pewną wartość. Na przykład encja Student może mieć atrybut
Nazwisko reprezentowany przez wartość Kowalski.

Wśród atrybutów encji wyróżniamy jeden atrybut lub zbiór atrybutów, którego wartość w sposób
jednoznaczny identyfikuje instancję (egzemplarz) encji. Taki atrybut lub zbiór atrybutów nazywamy
kluczem głównym encji. Klucz główny oznacza się często na wykresach symbolem PK (ang. Primary
Key) umieszczanym obok nazwy atrybutu.

W.Dąbrowski, P.Kowalczuk, K.Markowski

ITA-101 bazy danych

Drugim rodzajem klucza
nazywamy atrybut encji, który wskazuje na klucz główny innej encji. Klucz obcy oznacza się często
na wykresach symbolem FK (ang.

Związek

Bardzo  ważnym  elementem
i warunki  określające  te  związk
związków to powiązania stopnia drugiego
bierze  udział  dwóch  uczestników  (dwie  encje).  Mogą  występować  także
powiązana z samą sobą), jak również

W  zależności  od  tego,  jakiego  typu  jest  uczestnic
encje  na  słabe  lub  regularne.
w powiązaniu, to oznacza, że encja nie może istnieć bez tego powiązania (np. encja
nie  może  istnieć  bez  powiązania
w związku  jest  tylko  częściowe,  czyli  encja  może  istnieć  samodzielnie  bez  powiązania  (np.  encja
Klienci może istnieć bez powiązania

Bardzo istotnym czynnikiem określanym przy z
jako maksymalną liczbę instancji jednej encji (wystąpień
z instancją innej encji. Ze względu na wartość mocy możemy wyróżnić trzy typy powiązań:

•

jeden-do-jeden,

•

jeden-do-wiele,

•

wiele-do-wiele.

Związki binarne

Związek jeden-do-jeden (jedno

Jest to najprostszy typ powiązania, występuje wtedy, gdy tylko jedna instancja pierwszej encji jest
powiązana z tylko jedną instancją drugiej encji. Jest to powiązanie
ograniczenia, gdyż warunek jeden do jednego musi być zawsze spełniony. Opcjonalnie przy
powiązaniu jeden może występować również opcja żadne, oznaczana graficznie

Związek jeden-do-wiele (jedno

Najbardziej  typowym  rodzajem  powiązania  jest  powiązanie  jeden
instancja jednej encji może być połączona
na  swoją  uniwersalność  i
Opcjonalnie  przy  powiązaniu  jeden  lub  wiele  może  występować  również  opcja  żadne,  oznaczana
graficznie w postaci okręgu.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Strona 4/18

stosowanym w bazach relacyjnych jest klucz obcy.

nazywamy atrybut encji, który wskazuje na klucz główny innej encji. Klucz obcy oznacza się często
na wykresach symbolem FK (ang. Foreign Key) umieszczanym obok nazwy atrybutu.

Rys. 1. Przykład klucza obcego (FK1)

Bardzo ważnym elementem w modelu danych są związki (ang. relationship

związki – elementy łączące encje między sobą. Zdecydowana większość

związków to powiązania stopnia drugiego – związki binarne, charakteryzujące się tym, że
bierze udział dwóch uczestników (dwie encje). Mogą występować także

samą sobą), jak również związki ternarne (z trzema uczestnikami).

W zależności od tego, jakiego typu jest uczestnictwo encji w danym związku, możemy podzielić
encje na słabe lub regularne. Encje słabe charakteryzują się całkowitym uczestnictwem

powiązaniu, to oznacza, że encja nie może istnieć bez tego powiązania (np. encja

nie może istnieć bez powiązania z encją Klienci), natomiast uczestnictwo

związku jest tylko częściowe, czyli encja może istnieć samodzielnie bez powiązania (np. encja

może istnieć bez powiązania z encją Zamówienia).

Bardzo istotnym czynnikiem określanym przy związkach jest moc powiązania
jako maksymalną liczbę instancji jednej encji (wystąpień w danej encji), które mogą być powiązane

instancją innej encji. Ze względu na wartość mocy możemy wyróżnić trzy typy powiązań:

jeden (jedno-jednoznaczny)

Jest to najprostszy typ powiązania, występuje wtedy, gdy tylko jedna instancja pierwszej encji jest

tylko jedną instancją drugiej encji. Jest to powiązanie wprowadzające dosyć znaczne

ograniczenia, gdyż warunek jeden do jednego musi być zawsze spełniony. Opcjonalnie przy
powiązaniu jeden może występować również opcja żadne, oznaczana graficznie

wiele (jedno-wieloznaczny)

jbardziej typowym rodzajem powiązania jest powiązanie jeden-do-wiele,

instancja jednej encji może być połączona z jedną lub wieloma instancjami drugiej encji. Ze względu

i małą kłopotliwość, ten typ powiązania jest najczęściej stosowany.

Opcjonalnie przy powiązaniu jeden lub wiele może występować również opcja żadne, oznaczana

postaci okręgu.

Rys. 2. Związek jeden-do-wielu

Moduł 1

Budowa diagramów ERD

bazach relacyjnych jest klucz obcy. Kluczem obcym

nazywamy atrybut encji, który wskazuje na klucz główny innej encji. Klucz obcy oznacza się często

) umieszczanym obok nazwy atrybutu.

relationship) pomiędzy encjami

elementy łączące encje między sobą. Zdecydowana większość

charakteryzujące się tym, że w związku

bierze udział dwóch uczestników (dwie encje). Mogą występować także związki unarne (encja

(z trzema uczestnikami).

danym związku, możemy podzielić

charakteryzują się całkowitym uczestnictwem

powiązaniu, to oznacza, że encja nie może istnieć bez tego powiązania (np. encja Zamówienia

atomiast uczestnictwo encji regularnych

związku jest tylko częściowe, czyli encja może istnieć samodzielnie bez powiązania (np. encja

moc powiązania, która definiuje się

danej encji), które mogą być powiązane

instancją innej encji. Ze względu na wartość mocy możemy wyróżnić trzy typy powiązań:

Jest to najprostszy typ powiązania, występuje wtedy, gdy tylko jedna instancja pierwszej encji jest

wprowadzające dosyć znaczne

ograniczenia, gdyż warunek jeden do jednego musi być zawsze spełniony. Opcjonalnie przy
powiązaniu jeden może występować również opcja żadne, oznaczana graficznie w postaci okręgu.

wiele, w którym pojedyncza

jedną lub wieloma instancjami drugiej encji. Ze względu

jest najczęściej stosowany.

Opcjonalnie przy powiązaniu jeden lub wiele może występować również opcja żadne, oznaczana

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 5/18

Związek wiele-do-wiele (wielo-wieloznaczny)

Powiązania tego typu występują równie często jak powiązania jeden do wielu, jednak nie dają się
bezpośrednio implementować w relacyjnych bazach danych. Są one realizowane przy pomocy encji
pośrednich (w modelu relacyjnym są to tabele sprzęgające) powiązanych z encjami pierwotnymi
przy pomocy powiązań jeden do wielu.

W  powiązaniu  wiele-do-wiele  encjami  głównymi  są  encje  pierwotne,  natomiast  encją  obcą  jest
relacja  sprzęgająca,  która  zwiera  klucze  główne  relacji  oryginalnej.  Dlatego  w powiązaniu
jeden-do-wiele  pomiędzy  relacjami  pierwotnymi  a relacją  obcą,  po  stronie  relacji  oryginalnej
znajduje się strona „jeden” powiązania jeden-do-wiele, a po stronie relacji obcej znajduje się strona
„wiele”  z tego  powiązania.  Związki  wiele-do-wiele  nie  są  bezpośrednio  implementowane
w relacyjnych bazach danych i wymagają dodatkowych przekształceń.

Rys. 3. Związek wiele-do-wielu

Związki unarne

Powiązania tego typu mają tylko jednego uczestnika, czyli relację, która jest powiązana sama ze
sobą. Powiązanie realizowane jest w podobny sposób jak w przypadku powiązań binarnych, ale
odnosi się do jednej encji. Klucz główny tej encji jest dodawany do tej encji.

Rys. 4. Związek unarny

Powiązania unarne tak jak powiązania binarne mogą być różnej mocy. To znaczy mogą występować
powiązania jeden do wielu, które mogą być opcjonalne po stronie „jeden”. Ten typ powiązania jest
stosowany przy odwzorowywaniu struktur hierarchicznych.

Powiązania unarne mogą być również realizowane jako powiązania wiele do wielu. Wtedy,
podobnie jak przy powiązaniach binarnych, muszą być modelowane przy użyciu tabeli sprzęgającej.

Związki ternarne

Są to powiązania, w skład których wchodzą trzy związane ze sobą encje. Powiązania te, podobnie
jak powiązania wiele-do-wiele, nie mogą być realizowane bezpośrednio w relacyjnych bazach
danych.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 6/18

Rys. 5. Związek ternarny

Związki ternarne nie są bezpośrednio implementowane w relacyjnych bazach danych i wymagają
dodatkowych przekształceń.

Notacje związków

W  praktyce  spotkasz  się  z różnymi  sposobami  reprezentacji  graficznej  związków  (dla  przykładu:
w programach służących m.in. do projektowania diagramów ERD takich jak Visio lub IBM Rational
Rose  możliwe  jest  użycie  kilku  różnych  notacji).  Bodaj  najpopularniejsza  jest  notacja  czysto
graficzna.

Metody przekształcania związków

Związki binarne wiele-do-wiele oraz związki ternarne nie są implementowane w relacyjnych bazach
danych. Przed zamodelowaniem ich w bazie relacyjnej wymagają one pewnych przekształceń.
Przykłady takich przekształceń zaprezentowane są poniżej

Przekształcanie związków wielo-wieloznacznych

Jeśli mamy związek binarny wielo-wieloznaczny, to należy wprowadzić dodatkową encję oraz dwa
nowe związki jednoznaczne. Nowa encja powinna wśród atrybutów zawierać klucze obce
odnoszące się do kluczy głównych dwóch pozostałych encji.

Rys. 6. Przekształcanie związków binarnych wielo-wieloznacznych

Przekształcanie związków ternarnych

Przy przekształcaniu związków ternarnych postępujemy podobnie jak w wypadku związków wielo-
wieloznacznych binarnych. Wprowadzamy wówczas dodatkową encję oraz 3 nowe związki
jednoznaczne.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 7/18

Rys. 7. Przekształcanie związków ternarnych

Podobnie postępujemy, jeśli mamy do czynienia ze związkami o większej liczbie argumentów.

Podsumowanie

W tym rozdziale przedstawione zostało podejście do modelowania konceptualnego bazy danych
z wykorzystaniem techniki zwanej diagramami związków encji.

Dowiedziałeś się, czym jest encja, jakie posiada cechy oraz czym jest związek encji. Pamiętaj, że nie
wszystkie typy związków encji są bezpośrednio implementowane w relacyjnej bazie danych. Związki
typu  wiele  do  wielu  oraz  związki  więcej  niż  dwu  encji  wymagają  przekształcenia  modelu
konceptualnego  do  postaci  dającej  się  implementować  w modelu  relacyjnym.  Przekształcenie  to
polega zazwyczaj na wprowadzeniu dodatkowej encji i dodaniu nowych związków.

Projektując  bazę  danych  warto  zawsze  rozpocząć  modelowanie  danych  od  diagramów  ERD.
Diagramy  takie  powinny  przede  wszystkim,  w pierwszym  etapie  projektowania,  odzwierciedlać
w możliwie przejrzysty sposób dane i zależności występujące w świecie rzeczywistym – na przykład
obiekty biznesowe i zależności między nimi. W pierwszym etapie diagram ERD pokazuje więc często
związki  wiele  do  wielu  oraz  związki  wieloencyjne  (rzadziej).  Kolejne  kroki  prowadzą  do
przekształcania takiego diagramu aż do postaci modelu zgodnego z modelem relacyjnym.

Przykładowe rozwiązanie

Przypomnijmy problem z początku tego rozdziału dotyczący przygotowania bazy danych
ułatwiającej zarządzanie przydziałem sal i zajęć na wydziale uczelni.

Naszym celem jest przygotowanie modelu danych, który będzie spełniał dwa podstawowe cele:

•

pozwalał zweryfikować wymagania stawiane przez panią Jolę oraz

•

stanowił podstawę do zbudowania relacyjnej bazy danych.

Jak  widać  musimy  posłużyć  się  językiem  wyrazu  zrozumiałym  zarówno  dla  osoby  niemającej
wykształcenia czy tez doświadczenia informatycznego jak i przydatnym dla informatyka budującego
bazę  danych.  Jaki  środek  wyrazu,  język  wybrać?  Dosyć  powszechny  jest  tutaj  pogląd,  że  takim
uniwersalnym  środkiem    wyrazu  spełniającym  stawiane  przed  nami  wymagania  jest  język
obrazkowy – diagramy związków encji.

Sformułujmy więc teraz cel naszych działań w następujący sposób:

Naszym zadaniem jest opracowanie diagramu związków encji, który będzie jednoznacznie
i przejrzyście przedstawiał wymagania pani Joli w zakresie przetwarzanych przez nią danych oraz
umożliwiał zbudowanie na jego podstawie relacyjnej bazy danych.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 8/18

Przypominamy, że diagram związków encji powstaje w sposób iteracyjny. Wynikiem naszych prac
powinien być nie jeden diagram, ale zestaw diagramów przedstawiający nasz problem na różnych
poziomach abstrakcji (np. z różną liczbą szczegółów).

Spróbujemy teraz przedstawić w punktach nasze działania. Co więc i w jakiej kolejności powinniśmy
wykonać?

Krok 1

Powinniśmy uważnie wysłuchać, co ma do powiedzenia ekspert dziedzinowy, czyli pani Jola. Na
podstawie zebranych informacji możemy zidentyfikować i wypisać encje występujące w naszym
problemie. Dobrym zwyczajem jest też wypisanie kilku przykładowych instancji encji dla każdej ze
zidentyfikowanych encji.

Krok 2

Powinniśmy zidentyfikować związki występujące między encjami. Dobrze jest nazwać te związki
i określić role, jakie w nich odgrywają poszczególne encje. Koniecznie powinniśmy też
zidentyfikować liczności związków.

Krok 3

Powinniśmy wykonać pierwszy rysunek diagramu związków encji, na którym zamieszczamy:

•

nazwa encji,

•

związki między encjami,

•

liczności związków.

Warto też umieścić na nim nazwy związków i nazwy ról. Często jednak dla zachowania
przejrzystości rysunku rezygnujemy z umieszczania na diagramie ERD tych informacji.

UWAGA: Diagram związków encji będący wynikiem kroku 3 jest często w postaci
nieznormalizowanej i nierealizowalnej w bazie relacyjnej (np. przedstawia związki wiele do wielu).
Na tym etapie najczęściej nie należy dokonywać przekształceń tego diagramu.

Krok 4

Diagram  z kroku  3  powinniśmy  skonsultować  z ekspertem  dziedzinowym.  Na  tym  etapie  diagram
ERD  nie  zawiera  zbyt  wiele  szczegółów,  jest  więc  prosty  i przejrzysty.  Pozwoli  nam  to  na
upewnienie  się,  że  dobrze  zrozumieliśmy  stawiane  przez  eksperta  wymagania  dotyczące
przetwarzanych  danych  oraz  umożliwi  dokonanie  niezbędnych  poprawek  i uzupełnień  już  na  tym
wstępnym etapie.

Krok 5

Rozpoczynamy  identyfikowanie  atrybutów  dla  każdej  z przedstawionych  na  diagramie  encji.
Powinniśmy  zidentyfikować  wszystkie  atrybuty,  które  są  wykorzystywane  w procesach
opisywanych przez eksperta dziedzinowego – czyli tak zwane atrybuty biznesowe. Nie wszystkie ze
zidentyfikowanych  na  tym  etapie  atrybutów  muszą  znaleźć  swoje  odzwierciedlenie  w końcowym
projekcie bazy danych.

Na przykład: na pewnym wydziale po drugim roku studiów dokonywany jest przez studenta wybór
specjalizacji  dalszych  studiów.  O klasyfikacji  na  specjalizację  decyduje,  w wypadku  braku  miejsc,
średnia  ocen  uzyskanych  przez  studenta  z pierwszych  czterech  semestrów  studiów.  Dla  osoby
opisującej proces klasyfikacji studentów na specjalizację istotnym atrybutem każdego studenta jest
jego  średnia  z pierwszych  czterech  semestrów  nauki.  Powinniśmy  dla  encji  Student
zidentyfikować  atrybut  biznesowy  srednia_z_czterech_semestrow.  W trakcie  kolejnych
iteracji  budowy  diagramu  ERD  możemy  zdecydować,  że  nie  będziemy  przechowywać  w bazie  tej
średniej, ale wyliczać ją, gdy będzie potrzebna, na podstawie ocen cząstkowych.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 9/18

Krok 6

Diagram z kroku 5 powinniśmy skonsultować z ekspertem dziedzinowym.

Krok 7

Dla  każdego  atrybutu  powinniśmy  zidentyfikować  i zapisać  jego  dziedzinę.  Pamiętaj,  że  dziedzina
atrybutu  to  nie  to  samo,  co  jego  typ.  Dziedzina  związana  jest  z wyższym  poziomem  abstrakcji
modelu  i dotyczy  wartości,  które  może  przyjmować  atrybut  wynikających  z modelu  biznesowego
procesu.  Typ  natomiast  związany  jest  z niższym  poziomem  abstrakcji  modelu  i dotyczy
reprezentacji  danych  w silniku  bazy  danych.  Na  przykład  dziedziną  dla  atrybutu  Ocena  może  być
zbiór { 2; 3; 3,5; 4; 4,5; 5 }, a typem tego atrybutu Integer.

Krok 8

Diagram lub tabelę z kroku 7 powinniśmy skonsultować z ekspertem dziedzinowym.

Krok 9

Po zaakceptowaniu diagramu związków encji przez eksperta dziedzinowego możemy przystąpić do
normalizacji,  określenia  kluczy  głównych  i kluczy  obcych,  dokonać  zmian  atrybutów  (na  przykład
dodać  atrybuty  sztuczne)  oraz  przekształcenia  związków  nierealizowalnych  w modelu  relacyjnym
(np. zamiana związków wiele do wielu na związki jedne do wielu).

Krok 10

Proponujemy aby w tym kroku określić typy wszystkich atrybutów uwzględniając typy silnika bazy
danych, na której będzie realizowana baza danych, zdefiniować niezdefiniowane jeszcze klucze
główne i klucze obce oraz wskazać pola indeksowane.

Na zakończenie powinniśmy dokonać przeglądu diagramu ERD pod kątem jego spójności
i kompletności. W naszym wypadku zadanie jest dosyć proste, gdyż problem, z którym mamy do
czynienia nie jest skomplikowany. Przystępujemy więc do kolejnych kroków budowy diagramu ERD.

Krok 1

Po spotkaniu z panią Jolą identyfikujemy trzy encje: Sala, Zajecia i Grupa.

Przygotowujemy też zestawienie przykładowych instancji encji.

Tabela 1. Zestawienie instancji encji
Encja

Sala

Zajęcia

Grupa

Przykład

instancji

encji

110

C155

A001

Bazy danych – wykład

Bazy

danych

–

laboratorium

Podstawy informatyki

Programowanie
obiektowe

101

112

203

315c

Krok 2

Identyfikujemy związki:

Tabela 2. Liczności związków
Nazwa związku

Encje

Liczności

Zajecia_w_Sali

Sala, Zajęcia

Wiele do jeden (*..1)

Grupa_na_zajeciach

Grupa, Zajęcia

Wiele do wiele (*..*)

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 10/18

Krok 3

Przedstawiamy diagram ERD z uwzględnieniem związków i ich liczności.

Rys. 8. Diagram ERD z uwzględnieniem związków i ich liczności

Krok 4

Pani Jola po obejrzeniu naszego diagramu zauważa, że mogą być zajęcia, które w danym semestrze
nie odbywają się, ale znajdują się w katalogu zajęć (np. przedmiot obieralny, który nie został
w danym semestrze wybrany przez wystarczającą liczbę chętnych). Nie są one przypisane do żadnej
sali ani do grupy studentów.

Dostrzegamy też błąd polegający na początkowym przypisaniu do konkretnej sali tylko jednych
zajęć. Oczywiście na taki luksus żaden wydział nie może sobie pozwolić. Zamieniamy liczność
związku Zajęcia_w_Sali na wiele do wielu.

Uwagę tę powinniśmy uwzględnić na naszym diagramie ERD. Wprowadzamy stosowną poprawkę
na diagramie.

Rys. 9. Diagram ERD po uwzględnień poprawy liczności związku

Krok 5

Przystępujemy do identyfikacji atrybutów. Wygodnie jest informacje o atrybutach zebrać w tabeli
podając jednocześnie przykład wartości atrybutu.

Tabela 3. Przykładowe wartości atrybutów
Encja

Atrybut

Przykład

Sala

Numer Sali

C101

Liczba miejsc

120

Zajecia

Nazwa zajęć

Bazy danych – wykład

Grupa

Nazwa grupy

112

Liczność

Na diagramie ERD:

Rys. 10. Diagram ERD z zaznaczonymi atrybutami

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 11/18

Krok 6

Pokazujemy nasz diagram ERD pani Joli. Jeśli zostanie on zaakceptowany, przechodzimy do kroku
siódmego.

Krok 7

Powinniśmy teraz określić dla każdego atrybutu jego dziedzinę. Najwygodniej będzie nam to
wykonać znowu w postaci tabelki takiej jak tabela xxx uzupełnionej o kolumnę Dziedzina atrybutu.

Tabela 4. Dziedziny atrybutów

Encja

Atrybut

Przykład

Dziedzina atrybutu

Sala

Numer
sali

C101

Ciąg

składający

się

z litery

reprezentującej budynek oraz co
najwyżej czterech cyfr

Liczba
miejsc

120

Przedział od 15 do 250

Zajecia Nazwa

zajęć

Bazy danych – wykład

Lista zajęć

Grupa

Nazwa
grupy

112

Ciąg składający się z 3 lub 4 cyfr
i/lub litery

Liczność 35

Przedział od 12 do 40

Krok 8

Powinniśmy znowu skonsultować wyniki naszej pracy z panią Jolą. Jeśli uzyskamy akceptację,
możemy przejść do kroku dziewiątego. W przeciwnym razie nanosimy poprawki i ponownie
prosimy o akceptację.

Krok 9

Jeśli  dobrnęliśmy  aż  tutaj,  to  oznacza,  że  zakończyliśmy  konsultację  z panią  Jolą  i możemy
przystąpić  do  prac  zmierzających  do  nadania  naszemu  modelowi  postaci  dającej  się
zaimplementować w relacyjnej bazie danych.

W naszym diagramie ERD występują związki wiele do wiele. Są to związki nieimplementowane
bezpośrednio w modelu relacyjnym, dlatego musimy dokonać ich przekształcenia. Wprowadzamy
nowe encje ObciazenieSali i ZajeciaGrupy tak jak na rysunku.

Rys. 11. Diagram ERD

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 12/18

Informację na diagramie możemy uzupełnić o typy danych, tak jak przedstawiamy to na Rys. 12.
Diagram ERD z typami danych

Sala

ID_Sala

uniqueidentifier

Numer sali

varchar(6)

Liczba miejsc

uniqueidentifier

Zajecia

ID_Zajecia

uniqueidentifier

Nazwa zajęć

varchar(255)

Grupa

ID_Grupa

uniqueidentifier

Nazwa grupy

char(10)

Liczność

smallint

ZajeciaGrupy

PK,FK1

ID_Zajecia

int

PK,FK2

ID_Grupa

int

ObciazenieSali

PK,FK1

ID_Sala

int

PK,FK2

ID_Zajecia

int

Dzien

char(10)

GodzinaOd

char(10)

GodzianDo

char(10)

Rys. 12. Diagram ERD z typami danych

Krok 10

W ostatnim kroku dokonujemy przeglądu naszego modelu ERD. Rzadko kiedy pierwsze podejście
będzie całkowicie wolne od błędów, pomyłek czy niedopatrzeń, dlatego zawsze należy
przeprowadzić weryfikację poprawności diagramu.

Porady praktyczne

•

Pamiętaj, że diagram związków encji ma być zrozumiały nie tylko dla informatyka. Ma on
służyć dialogowi między projektantem a użytkownikiem, który formułuje wymagania dla
przyszłej bazy danych. Modelując dane należy posługiwać się jasnym, prostym i przejrzystym
językiem i formami wyrazu.

•

Budując  diagram  związków  encji  nie  spiesz  się.  Nie  dokonuj  zbyt  pochopnie  przekształceń
i nie  wprowadzaj  od  razu  zbyt  wielu  szczegółów,  nawet  jeśli  przekształcenia  wydają  Ci  się
oczywiste, a definiowanie typów danych czy określanie kluczy natychmiastowe. Pamiętaj, że
kluczowym elementem budowanych diagramów jest ich czytelność i zrozumiałość dla osoby
definiującej  wymagania,  czyli  tak  zwanego  eksperta  dziedzinowego  (w  każdym  razie
w początkowych etapach tworzenia diagramów ERD).

•

Przy identyfikowaniu encji bardzo zachęcamy do tego, aby zawsze wypisać kilka przykładów
instancji encji. Podejście to pozwala na lepsze zrozumienie świata rzeczywistego i weryfikację
poprawności identyfikacji encji. Nazwa encji często nie oddaje jej istoty i może być różnie
rozumiana przez różne osoby biorące udział w budowaniu modelu danych. Szybko docenisz
tę technikę podczas dialogu z przyszłym użytkownikiem bazy, który z pewnością będzie lepiej
rozumiał prezentowane przez Ciebie modele.

•

Rysowanie diagramów związków encji najlepiej zacząć od rysowania na dużej kartce papieru
lub  tablicy.  Dopiero  pod  koniec  (krok  9)  warto  jest  przenieść  diagramy  związków  encji  do
narzędzia  wspomagającego  pracę  z diagramami  ERD.  Narzędzi  takich  jest  wiele.  My
proponujemy  wykorzystać  do  tego  celu  program  MS  Visio  –  znany  i wygodny  program  do
rysowania wyposażony w specjalny moduł wspomagający projektowanie baz danych.

•

Zwracamy  uwagę,  że  w teorii  relacyjnych  baz  danych  pod  pojęciem  relacji  rozumie  się
dwuwymiarową tabelę danych. Tabele te odpowiadają na etapie projektowym pojęciu encji,
natomiast  powiązania  między  tabelami  (encjami)  noszą  nazwę  związków.  W niektórych
aplikacjach  i  w żargonie  informatycznym  słowo  relacja  ma  jednak  czasem  inne  znaczenie

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 13/18

i oznacza powiązanie między tabelami (encjami), czyli związek. Takie nazewnictwo
stosowane jest na przykład w polskich wersjach aplikacji firmy Microsoft.

•

Ostateczny  projekt  bazy  danych  zależy  w istotnym  stopniu  od  zwyczajów  i upodobań
projektanta.  Modele  ERD  bazy  danych  zbudowane  dla  tego  samego  problemu  mogą  się
różnić.  Nie  zawsze  potrafimy  jednoznacznie  wskazać,  który  z modeli  jest  lepszy.  Często  są
one po prostu jednakowo dobre.

•

Zwróć  uwagę,  że  notacja  proponowana  przez  nas  w tym  module  nie  jest  jedyną  notacją
stosowaną  przy  modelowaniu  danych.  Popularność  zyskuje  modelowanie  baz  danych
z wykorzystaniem języka UML. Modelowanie w języku UML bazuje na podejściu obiektowym
do  analizy  i projektowania  systemów.  Choć  założenia,  na  których  opiera  się  modelowania
diagramami ERD i językiem UML są inne, to jednak ogólna droga postępowania jest bardzo
podobna. Jeśli znasz język UML i zasady modelowania obiektowego, to do projektowania baz
danych możesz zamiast diagramów ERD wykorzystać diagramy klas języka UML.

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium, jeśli:

•

rozumiesz, czym jest encja i związek między encjami,

•

rozumiesz, na czym polega proces dochodzenia do końcowego diagramu związków encji,

•

umiesz dokonać przekształcenia związków nieimplementowanych w relacyjnych bazach
danych do związków binarnych jednoznacznych,

•

potrafisz przedstawić diagram ERD na różnym poziomie abstrakcji,

•

wiesz, jakie jest znaczenie słowa relacja w teorii relacyjnych baz danych i w żargonie
informatycznym.

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

Rebeca R. Riordan, Projektowanie relacyjnych baz danych, Microsoft Press, 2000

Książka  poświęcona  jest  praktycznym  aspektom  projektowania  relacyjnych  baz
danych  w środowisku  aplikacji  firmy  Microsoft.  Znajdziesz  w niej  między  innymi
przegląd  modeli  normalizacyjnych,  których  nie  omawialiśmy  w tym  module
bezpośrednio. Rebeca Riordan znana jest z łatwego i zrozumiałego języka i łatwości
tłumaczenia  zagadnień  trudnych.  Ten  swój  talent  wykorzystuje  również  w tej
pozycji.  Jeśli  nie  interesuje  Cię  zgłębianie  teoretycznych  podstaw  działania  baz
danych, a bardziej nastawiony jesteś na praktyczne wykorzystanie wiedzy, to jest to
książka dla Ciebie.

C.J.Date, Wprowadzenie do systemów baz danych, WNT, 2000

Jest  to  pełny  podręcznik  do  wykładu  z baz  danych  znanego  i cenionego  na  całym
świecie  autora.  Znajdziesz  w nim  szersze  spojrzenie  na  problematykę  budowy
i modelowania  baz  danych.  Polecamy  ją  wszystkim,  którzy  chcieliby  poszerzyć
swoje wiadomości z tego zakresu.

System pomocy programu Visio

Jeśli po raz pierwszy spotykasz się z programem Visio, to zajrzyj koniecznie do jego
systemu pomocy. Znajdziesz tam wszystkie niezbędne informacje, aby efektywnie
korzystać z tego oprogramowania.

W.Dąbrowski, P.Kowalczuk, K.Markowski

ITA-101 bazy danych

Laboratorium podstawowe

Problem (czas realizacji

Jesteś  projektantem  bazy  danych.
użytkownikiem  bazy)  opracowałeś  model  diagramu  związków  encji  opisany
Przykładowe rozwiązanie. Model
na papierze. Teraz warto jest przenieść tę dokumentację „do
archiwizację  modelu,  wprowadzanie  zmian
Dodatkowo  może  też  skrócić  czas  projektowania  bazy  danych  dzięki  wykorzystaniu  narzędzi  RAD
(ang. Rapid Application Design
bazy  danych  wybrana  została  aplikacji  MS  Viso  2007.  Twoim  zadaniem  będzie  utworzenie  przy
pomocy tego programu modelu danych

Zadanie

Tok postępowania

Uruchom

projekt bazy
danych
w programie MS
Visio 2007

•

Uruchom aplikację MS Visio 2007

•

Z panelu
grupę

Wprowadź

tabele

•

W obszarze roboczym wyłącz linie siatki wybierając
Widok

•

Z zasobnika
roboczy (kartka). Na obszarze roboczym zostanie utworzona encja
o

•

Zaznacz encję
element
tekst

•

Powtórz powyższe czynności dla pozostałych encji

W.Dąbrowski, P.Kowalczuk, K.Markowski

Strona 14/18

Laboratorium podstawowe

Problem (czas realizacji 40 min)

Jesteś projektantem bazy danych. W wyniku spotkań z ekspertem dzied
użytkownikiem bazy) opracowałeś model diagramu związków encji opisany

. Model i wszystkie dodatkowe dane (np. tabele) zostały zapisane jedynie

na papierze. Teraz warto jest przenieść tę dokumentację „do komputera”. Umożliwi to łatwiejszą
archiwizację modelu, wprowadzanie zmian i wymianę informacji między członkami zespołu.
Dodatkowo może też skrócić czas projektowania bazy danych dzięki wykorzystaniu narzędzi RAD

Rapid Application Design). Jako aplikację wspomagającą prace na tym etapie budowy modelu

bazy danych wybrana została aplikacji MS Viso 2007. Twoim zadaniem będzie utworzenie przy
pomocy tego programu modelu danych i diagramu ERD zgodne z wymaganiami „papierowymi”.

Tok postępowania

Uruchom aplikację MS Visio 2007.
Z panelu Wprowadzenie do programu Microsoft Office Visio
grupę Diagram modelu bazy danych.

W  obszarze  roboczym  wyłącz  linie  siatki  wybierając
Widok -> Siatka.
Z  zasobnika  Model  encja-relacja  przeciągnij  element
roboczy  (kartka).  Na  obszarze  roboczym  zostanie  utworzona  encja
o nazwie Tabela1.
Zaznacz  encję  Tabela1.  W oknie  Właściwości  bazy  danych
element  Definicja,  a następnie  w polu  Nazwa  koncepcyjna
tekst Sala.

Nazwa encji na obszarze roboczym powinna zmienić się na

Powtórz powyższe czynności dla pozostałych encji

Rys. 13. Fragment okna programu Visio

Moduł 1

Budowa diagramów ERD

ekspertem dziedzinowym (przyszłym

użytkownikiem bazy) opracowałeś model diagramu związków encji opisany w rozdziale

wszystkie dodatkowe dane (np. tabele) zostały zapisane jedynie

komputera”. Umożliwi to łatwiejszą

wymianę informacji między członkami zespołu.

Dodatkowo może też skrócić czas projektowania bazy danych dzięki wykorzystaniu narzędzi RAD

ikację wspomagającą prace na tym etapie budowy modelu

bazy danych wybrana została aplikacji MS Viso 2007. Twoim zadaniem będzie utworzenie przy

wymaganiami „papierowymi”.

Wprowadzenie do programu Microsoft Office Visio wybierz

W obszarze roboczym wyłącz linie siatki wybierając polecenie

przeciągnij element Encja na obszar

roboczy (kartka). Na obszarze roboczym zostanie utworzona encja

Właściwości bazy danych wskaż

Nazwa koncepcyjna wprowadź

Nazwa encji na obszarze roboczym powinna zmienić się na Sala.

Powtórz powyższe czynności dla pozostałych encji w modelu.

. Fragment okna programu Visio

W.Dąbrowski, P.Kowalczuk, K.Markowski

ITA-101 bazy danych

Wprowadź

atrybuty

•

Zaznacz encję

•

W oknie

•

W kolumnie

•

Na dole okna
wybór
jest inny sterownik wybierz
a w
Microsoft SQL Server

•

jako typ danych
pole jako
symbol budynku

•

Wprowadź pozostałe kolumny tabeli
jest kluczem głównym
Wprowadź atrybuty pozostałych tabel. Pamiętaj
kolumny stanowią kluc

Zmień widok

dokumentu

•

Przejdź do menu
dokumentu bazy danych
modelu ERD.

Dodaj związki

między encjami

•

Z  zasobnika
roboczy  (kartka).  Na  obszarze  roboczym  zostanie  utworzona  relacja.
Przeciągnij końce relacji odpowiednio na encje
aby  uzyskać  zakotwiczenie  relacji
program wyróżnia encję czerwonym obramowaniem)

•

Wskaż utworzoną relację.
kategorię
fizyczna

•

Przejdź do kate
różnych opcji kardynalności (liczności).
zdaniem kardynalności dla tej relacji.

•

Zdefiniuj pozostałe związki między encjami.

Zmiana notacji

diagramu

•

Wybierz
zmień zaznaczenie opcji
diagramie ERD.

Zapisz model

•

Zapisz model

W.Dąbrowski, P.Kowalczuk, K.Markowski

Strona 15/18

Zaznacz encję Sala.
W oknie Właściwości bazy danych zaznacz element
W kolumnie Nazwa fizyczna wprowadź Numer_Sali
Na dole okna Właściwości bazy danych upewnij się, że zaznaczony jest
wybór  Fizyczny  typ  danych  (Microsoft  SQL  Server)
jest inny sterownik wybierz z menu Baza danych -
a  w oknie  Sterowniki  bazy  danych  z karty
Microsoft SQL Server.
jako  typ  danych  dla  kolumny  Numer_Sali wybierz
pole  jako  wymagane,  a  w uwagach  wpisz:  Numer  sali;  litera  oznacza
symbol budynku.

Sprawdź w systemie pomocy co oznacza typ danych
zgadzasz się z takim wyborem typu dla pola Nazwa_Sali
Sprawdź, co stanie się po zmianie wyboru na

danych?

Wprowadź pozostałe kolumny tabeli Sala. Wskaż, że kolumna
jest kluczem głównym w tej tabeli.
Wprowadź atrybuty pozostałych tabel. Pamiętaj
kolumny stanowią klucz główny oraz które wartości są wymagane.

Program MS Visio pozwala na przestawienie modelu ERD
zestawem informacji (np. można ukryć lub pokazać typy danych,
oznaczenia kluczy głównych itd.).

Przejdź do menu Baza danych -> Opcje -> Dokument
dokumentu bazy danych wypróbuj różne ustawienia wyświetlania
modelu ERD.

Jakie opcje należy wybrać, aby na diagramie były widoczne typy
danych zdefiniowane w oknie Właściwości bazy danych
poszczególnych atrybutów?

Z  zasobnika  Model  encja-relacja  przeciągnij  element
roboczy  (kartka).  Na  obszarze  roboczym  zostanie  utworzona  relacja.
Przeciągnij końce relacji odpowiednio na encje Sala
aby  uzyskać  zakotwiczenie  relacji  (jeśli  następuje  zakotwiczeni
program wyróżnia encję czerwonym obramowaniem)
Wskaż  utworzoną  relację.  W oknie  Właściwości  bazy  danych
kategorię  Nazwa.  W pola  Fraza  orzeczenia,  Fraza  odwrotna
fizyczna i Uwagi wpisz odpowiednie Twoim zdaniem wartości.
Przejdź do kategorii  Różne. Sprawdź, jaki wpływ na diagram ma wybór
różnych  opcji  kardynalności  (liczności).  Ustaw  prawidłowe  Twoim
zdaniem kardynalności dla tej relacji.
Zdefiniuj pozostałe związki między encjami.

Co należy zrobić, aby w widoku roboczym nie były wyświe
związki?

Wybierz z menu Baza danych -> Opcje -> Dokument
zmień zaznaczenie opcji Kurze łapki. Zaobserwuj zmiany notacji na
diagramie ERD.

Zapisz model w odpowiednim pliku na dysku.

Moduł 1

Budowa diagramów ERD

zaznacz element Kolumny.

Numer_Sali.

upewnij się, że zaznaczony jest

Fizyczny typ danych (Microsoft SQL Server). Jeśli wyświetlany

-> Opcje -> Sterowniki,

karty Sterowniki sterownik

wybierz varchar(6), wskaż to

Numer sali; litera oznacza

systemie pomocy co oznacza typ danych varchar(6). Czy

Nazwa_Sali?

się po zmianie wyboru na Pokaż: Przenośny typ

. Wskaż, że kolumna ID_Sala

Wprowadź atrybuty pozostałych tabel. Pamiętaj o wskazaniu, które

główny oraz które wartości są wymagane.

Program MS Visio pozwala na przestawienie modelu ERD z różnym
zestawem informacji (np. można ukryć lub pokazać typy danych,

Dokument i w karcie Opcje

wypróbuj różne ustawienia wyświetlania

Jakie opcje należy wybrać, aby na diagramie były widoczne typy

Właściwości bazy danych dla

przeciągnij element Relacja na obszar

roboczy (kartka). Na obszarze roboczym zostanie utworzona relacja.

Sala i ObciazenieSali, tak

(jeśli następuje zakotwiczenie, to

program wyróżnia encję czerwonym obramowaniem).

Właściwości bazy danych wskaż

Fraza odwrotna, Nazwa

wpisz odpowiednie Twoim zdaniem wartości.

. Sprawdź, jaki wpływ na diagram ma wybór

Ustaw prawidłowe Twoim

widoku roboczym nie były wyświetlane

Dokument i na karcie Relacja

. Zaobserwuj zmiany notacji na

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 16/18

Laboratorium rozszerzone

Zadanie 1 (czas realizacji 45 min)

W  pewnej  uczelni  (jeśli  nie  w każdej)  student  kończący  pewien  etap  edukacji  musi  wykonać
i obronić  pracę  końcową  zwaną  pracą  dyplomową.  Praca  dyplomowa  realizowana  jest  na  koniec
każdego  etapu  studiów.  Tak  więc  prace  dyplomową  piszą  studenci  studiów  licencjackich,
inżynierskich i magisterskich. W przyszłości uczelnia planuje poszerzyć swoją ofertę studiów o nowe
rodzaje  studiów,  które  też  będą  kończyć  się  pracą  dyplomową.  Każdy  student  oprócz  imienia
i nazwiska ma przypisany na uczelni jednoznaczny identyfikator w postaci numeru indeksu. Numer
indeksu  jest  ciągiem  złożonym  z 10  znaków  i  w sposób  jednoznaczny  identyfikuje  studenta
studiującego na uczelni.

Student  chcąc  ukończyć  dany  rodzaj  studiów  musi  wybrać  temat  pracy  dyplomowej.  Z tematem
pracy  związany  jest  oczywiście  jej  opiekun  zwany  zazwyczaj  promotorem  pracy.  Zasadą  jest,  że
jeden  temat  kierowany  jest  przez  jednego  promotora  –  pracownika  uczelni  ze  stopniem  doktora,
doktora habilitowanego lub profesora.

Władze uczelni zachęcają swoich studentów do pisania prac indywidualnych. Ale dopuszczają
również możliwość realizacji prac przez dwóch lub trzech studentów. Więcej osób nie może pisać
jednej pracy dyplomowej.

Do pracy przyporządkowywane są pewne słowa ze z góry zdefiniowanego zbioru. Są to tak zwane
słowa kluczowe, które pozwalają przypisać tematykę pracy do określonego obszaru, a następnie
odnajdywać prace związane z podobną tematyką. Słowami kluczowymi mogą być: informatyka,
systemy operacyjne, konstrukcje żelbetowe itp. Każda praca powinna mieć przypisane co najmniej
jedno słowo kluczowe.

Po napisaniu pracy student przystępuje do obrony pracy. Obrona ta odbywa się w wyznaczonym
dniu i kończy się wystawieniem pracy dyplomowej końcowej, ostatecznej oceny.

Uczelnia chciałaby usprawnić obsługę prac dyplomowych i związanych z tym procesów. Dlatego
planuje opracować system informatyczny wspierający obsługę tych procesów. Pierwszym etapem
prac ma być zbudowanie bazy danych, która będzie spełniać następujące wymagania:

Umożliwi przechowywanie informacji o obronionych pracach dyplomowych wszystkich
studentów uczelni.

Umożliwi szybkie i łatwe wyszukiwanie prac związanych z daną tematyką lub prowadzonych
przez określonego promotora.

Umożliwi raportowanie o pracach dyplomowych:

•

recenzowanych przez pracowników uczelni,

•

obronionych w danym dniu, miesiącu, roku,

•

obronionych na danym rodzaju studiów.

Zaproponuj diagram ERD dla projektowanej bazy danych. Pamiętaj również o udokumentowaniu
przykładowych instancji encji oraz wartości atrybutów, tak jak robiliśmy to w przykładowym
rozwiązaniu w krokach piątym i siódmym. Model zapisz w pliku programu MS Visio.

Zadanie 2 (czas realizacji 45 min)

Wymagania z zadania 1 okazały się niewystarczające. Nasz ekspert dokonał przeglądu wymagań
i dodał uzupełnienia. W poniższym tekście zostały one uwydatnione.

W.Dąbrowski, P.Kowalczuk, K.Markowski

Moduł 1

ITA-101 bazy danych

Budowa diagramów ERD

Strona 17/18

Zaproponuj  diagram  ERD  dla  projektowanej  bazy  danych  po  uwzględnieniu  rozszerzeń.  Pamiętaj
również  o udokumentowaniu  przykładowych  instancji  encji  oraz  wartości  atrybutów,  tak  jak
robiliśmy  to  w przykładowym  rozwiązaniu  w krokach  piątym  i siódmym.  Model  zapisz  w pliku
programu MS Visio.

W  pewnej  uczelni  (jeśli  nie  w każdej)  student  kończący  pewien  etap  edukacji  musi  wykonać
i obronić pracę końcową zwaną pracą dyplomową. Uczelnia składa się kilku wydziałów. Na każdym
wydziale  studenci  mogą  być  kształceni  na  różnych  specjalizacjach,  a informacja  o wydziale
i specjalizacji jest istotna przy wykonywaniu sprawozdań uczelni z obronionych prac dyplomowych.
Praca dyplomowa realizowana jest na koniec każdego  etapu studiów. Tak więc prace dyplomową
piszą  studenci  studiów  licencjackich,  inżynierskich  i magisterskich.  W przyszłości  uczelnia  planuje
poszerzyć  swoją  ofertę  studiów  o nowe  rodzaje  studiów,  które  też  będą  kończyć  się  pracą
dyplomową.  Każdy  student  oprócz  imienia  i nazwiska  ma  przypisany  na  uczelni  jednoznaczny
identyfikator  w postaci  numeru  indeksu.  Numer  indeksu  jest  ciągiem  złożonym  z 10  znaków  i
w sposób jednoznaczny identyfikuje studenta studiującego na uczelni.

Po napisaniu praca podlega recenzji. Recenzja wykonywana jest przez jednego lub kilku
pracowników uczelni i oceniana. Każdy recenzent może ocenić pracę w skali od 2 do 5. Praca
oceniana jest również przez promotora. Ocena recenzenta i promotora nie może być zbiorczą oceną
pracy, ale musi osobno dotyczyć każdego z autorów pracy.

Do pracy przyporządkowywane są pewne słowa ze z góry zdefiniowanego zbioru. Są to tak zwane
słowa kluczowe, które pozwalają przypisać tematykę pracy do określonego obszaru, a następnie
odnajdywać prace związane z podobną tematyką. Słowami kluczowymi mogą być na przykład:
informatyka, systemy operacyjne, konstrukcje żelbetowe. Każda praca powinna mieć przypisane co
najmniej jedno słowo kluczowe.

Po  napisaniu  pracy  student  przystępuje  do  obrony  pracy.  Obrona  ta  odbywa  się  w wyznaczonym
dniu  przed  komisją  składającą  się  z 3  członków  oraz przewodniczącego  i kończy  się  wystawieniem
ostatecznej  oceny  każdemu  studentowi  osobno.  W czasie  egzaminu  każdemu  studentowi  są
zadawane i protokołowane trzy pytania. Każde z pytań podlega osobnej ocenie.

1. Umożliwi przechowywanie informacji o obronionych pracach dyplomowych wszystkich

studentów uczelni.

2. Umożliwi szybkie i łatwe wyszukiwanie prac związanych z daną tematyką, wydziałem,

specjalizacją lub prowadzonych przez określonego promotora.

3. Umożliwi raportowanie o pracach dyplomowych:

•

recenzowanych przez pracowników uczelni

•

obronionych w danym dniu, miesiącu, roku

•

obronionych na danym rodzaju studiów

ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

Wersja 2.0

Instalacja i konfiguracja Microsoft SQL

Server 2008

Spis treści

Instalacja i konfiguracja MS SQL Server 2008 ...................................................................................... 1

Informacje o module ............................................................................................................................ 2

Przygotowanie teoretyczne ................................................................................................................. 3

Przykładowy problem .................................................................................................................. 3

Podstawy teoretyczne.................................................................................................................. 3

Przykładowe rozwiązanie ............................................................................................................. 6

Porady praktyczne ..................................................................................................................... 10

Uwagi dla studenta .................................................................................................................... 11

Dodatkowe źródła informacji..................................................................................................... 11

Laboratorium podstawowe ................................................................................................................ 12

Laboratorium rozszerzone ................................................................................................................. 16

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 2/16

Informacje o module

Opis modułu

W  tym  module  znajdziesz  informację  o  podstawowych    zadaniach
administratora  systemu  bazodanowego.  Do  zadań  tych  należy  instalacja
serwera  baz  danych,  konserwacja  oraz  aktualizacji  serwisów  serwera.
Prawidłowe  przygotowanie  środowiska  pracy  zapewni  stabilność  oraz
pozwoli na poznanie systemu bazodanowego od podstaw.

Cel modułu

Celem modułu jest przedstawienie czytelnikowi typowych zagadnień
związanych z instalacją i konfiguracją serwera bazodanowego.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:

•

potrafił zaplanować instalację systemu bazodanowego

•

potrafił przeprowadzić instalację SQL Server 2008 za pomocą
centrum instalacji

•

wstępne opcje dotyczące bezpieczeństwa serwera

•

rozumiał czym są serwisy serwera SQL i jakie mają funkcje

•

wiedział, jak skonfigurować poszczególne serwisy serwera SQL

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ć, jak przebiega instalacja oprogramowania w systemie
Windows

Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na rys. 1, nie istnieje konieczność
wykonania wcześniej żadnego innego modułu.

Rys. 1 Mapa zależności modułu

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 3/16

Przygotowanie teoretyczne

Przykładowy problem

Wdrożenie nowego systemu bazodanowego nie jest procesem trywialnym. W pierwszej kolejności
wykonywana jest analiza potrzeb organizacji w zakresie gromadzenia i przetwarzania informacji.
Następnie można wybrać konkretny system z użytecznymi komponentami. Instalacja systemu, testy
wydajnościowe i integracja z istniejącymi składnikami IT organizacji to niezbędne kroki wdrożenia,
przeważnie oddelegowane do doświadczonego informatyka.

Typowym  scenariuszem  jest  pojawienie  się  potrzeby  przetwarzania  coraz  większych  ilości
informacji w małej firmie, która w związku z dogodnymi warunkami na rynku przeżywa dynamiczny
rozwój. Obsługa rosnącej liczby klientów przysparza coraz więcej kłopotów, głównie działowi analiz.
Na  kolejnym  zebraniu  pada  propozycja  wdrożenia  nowego  systemu  bazodanowego  opartego  na
technologii Microsoft SQL Server 2008, który pomoże usprawnić procesy związane z obróbką coraz
większej ilości danych o klientach, co potencjalnie przełoży się na jakość obsługi  i przyszłe zyski.

Sygnał i wsparcie finansowe od strony biznesowej jest motorem procesu wdrożenia systemu
bazodanowego przedstawionego w tym module.

Podstawy teoretyczne

Właściwie każde oprogramowanie określane mianem serwera (serwer WWW, serwer baz danych)
działa na podobnej zasadzie – opiera się na uruchomionych w systemie operacyjnym usługach. Od
konfiguracji tych usług zależy, czy i jak będzie funkcjonował serwer.

Instalacja

Aby móc cokolwiek konfigurować, w pierwszej kolejności należy dokonać instalacji tego składnika.
Podstawowym składnikiem, czyli usługą systemu bazodanowego, jest silnik (ang. Database Engine),
odpowiadający za niskopoziomowe przewarzanie danych. Element ten znajdziemy w każdym
systemie bazodanowym, choć różnie może się nazywać. Wszystkie inne elementy bezpośrednio lub
pośrednio korzystają z silnika i w przypadku jego braku lub uszkodzenia po prostu nie działają.

Sam  silnik  jest  jednak  bardzo  niewygodny  w  obsłudze  z  punktu  widzenia  użytkownika  serwera
bazodanowego.  W  tej  sytuacji  powstało  wiele  innych  komponentów,  które  uzupełniają  i
usprawniają pracę z takim systemem. Narzędzia graficzne do obsługi i administracji serwera (ang.
Client  Tools)  są  szczególnie  pomocne  dla  administratorów.  Serwis  analityczny  (ang.  Analysis
Services)  jest  nieoceniony  przy  przetwarzaniu  dużej  ilości  danych.  Serwis  raportujący  (ang.
Reporting Services) usprawnia i uatrakcyjnia prezentację analizowanych danych.

W celu integracji czynności instalacyjnych tych i innych komponentów serwera SQL administrator
do dyspozycji ma narzędzie o nazwie Centrum Instalacji przedstawione na rys. 2.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 4/16

Rys. 2 Nowe Centrum Instalacji

Z tego miejsca można dokonać instalacji nowej instancji serwera SQL (w jednym systemie może być
wiele wystąpień serwera bazodanowego), zmodyfikować komponenty serwera, zainstalować
przykładowe bazy danych lub poczytać dokumentację.

Instalacja nowej instancji serwera SQL wiąże się zawsze z kilkoma etapami:

Określenie komponentów serwera, które będziemy instalować.

Określenie miejsca w systemie operacyjnym, gdzie komponenty będą składowane.

Podanie kont systemowych potrzebnych do uruchomienia poszczególnych usług serwera.

Po zakończeniu procesu instalacji powinniśmy przejrzeć i skonfigurować nowe usługi bazodanowe,
które pojawiły się w naszym systemie operacyjnym.

Usługa serwera

Silnik

Silnik  serwera  bazodanowego,  odpowiedzialny  za  przechowywanie  danych,  utrzymywanie
spójności  danych  i  obsługę  poleceń  języka  zapytań  najczęściej  jest  tożsamy  z  główną  usługą
serwera.  Aby  użytkownicy  mogli  stale  korzystać  z  tej  usługi,  powinna  być  ona  uruchomiona
praktycznie  zawsze  (nie  licząc  czasu  na  ewentualne  prace  administracyjne,  wymagające  przejścia
serwera w tryb offline).

Dodatkowo,  ponieważ  niektóre  czynności  wymagają  restartu  samej  maszyny,  usługa  ta  powinna
być  skonfigurowana  jako  uruchamiana  wraz  ze  startem  systemu  operacyjnego.  Niektóre  systemy
operacyjne  pozwalają  również  na  określenie  zachowań  usługi  w  momencie,  gdy  zostanie  ona
nieoczekiwanie zatrzymana (nie przez użytkownika, a na przykład na skutek awarii).

Dla przykładu, w systemie Microsoft Windows Server można skonfigurować każdą usługę tak, by po
nieoczekiwanym zatrzymaniu była podejmowana próba automatycznego restartu usługi (czyli
ponownego jej uruchomienia).

Usługa automatyzacji zadań

Dobry administrator potrafi wykorzystać zdobycze techniki i zautomatyzować pracę systemu
bazodanowego tak, by mieć przy nim jak najmniej pracy. Do automatyzacji najczęściej służy usługa,
która albo jest oferowana przez system operacyjny, albo jest dostarczana wraz z systemem
bazodanowym. Pozwala ona między innymi na przygotowywanie harmonogramów tworzenia kopii
zapasowych baz danych. Taką usługą w systemie Microsoft SQL Server 2008 jest SQL Server Agent.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 5/16

Należy zadbać o to, by była ona zawsze uruchomiona, a często domyślnie usługi te są wyłączone lub
zatrzymane.

Bezpieczeństwo usług

W trakcie konfiguracji usług należy kierować się pewnymi zasadami, by uniknąć nieprawidłowości w
ustawieniach i w działaniu serwera. Zasady te dotyczą głównie kwestii związanych z
bezpieczeństwem środowiska.

Pierwszą  ważną  zasadą  jest  zasada  minimalizacji  ilości  usług.  Mówi  ona,  że  należy  zawsze
instalować  i  uruchamiać  tylko  te  usługi,  z  których  w  danym  momencie  korzysta  nasz  system
bazodanowy.  Pozostałe  usługi,  o  ile  mogą  być  zainstalowane  (w  przyszłości  może  się  okazać,  że
jakaś dodatkowa usługa będzie potrzebna), o tyle ich uruchamianie powoduje, że administrator ma
więcej pracy, zaś w systemie znajduje się więcej potencjalnych słabych punktów.

Zasada ta obowiązuje głównie osoby administrujące systemami i sieciami komputerowymi.
Stosowanie się do niej może znacznie zwiększyć bezpieczeństwo systemu.

Druga  zasada  dotyczy  konfiguracji  kontekstu  usług.  Usługi  w  systemie  operacyjnym  działają  jak
użytkownicy  –  wykonują  operacje  i  mają  określone  uprawnienia.  W  niektórych  systemach
(np. Windows)  każdej  usłudze  przyporządkowane  jest  konto  użytkownika.  Mówimy,  że  usługa
pracuje w kontekście użytkownika. Oznacza to, że usługa działa w systemie operacyjnym i w sieci
na takich samych uprawnieniach, jakie zostały określone dla użytkownika.

W związku z tym pojawia się problem wyboru właściwego konta dla usług. Problem ten dotyczy
głównej usługi serwera SQL – silnika oraz usługi odpowiedzialnej za automatyzację zadań w tym
systemie.

Większość systemów operacyjnych oferuje wbudowane konta o kreślonych uprawnieniach.
Niestety, uprawnienia te na ogół są zbyt duże, by konta systemowe można było wykorzystać do
pracy z usługami systemu bazodanowego. Przykładem takiego konta jest konto Local System w
systemach Microsoft Windows.

Konto to działa jako element systemu operacyjnego, co w praktyce oznacza, że ma uprawnienia
nawet większe od tych, jakie posiada administrator systemu. Stąd pierwszy wniosek – na ogół nie
wybieramy wbudowanych kont systemowych dla usług serwera SQL. Powstaje pytanie – jakich w
takim razie kont używać?

Jeśli serwer ma pracować w środowisku rozproszonym, ma mieć możliwość dostępu do innych
serwerów i dodatkowo maszyna znajduje się w domenie, to wybieramy dla usług konto domenowe,
specjalnie utworzone i skonfigurowane. Konto to powinno mieć ustawienia, które pozwolą w
sposób nieprzerwany pracować usłudze.

Zatem – należy ustawić dla tego konta silne hasło, które nie powinno mieć daty wygasania (hasło
nigdy nie wygasa) i nie powinno być zmieniane przez użytkowników (użytkownik nie może zmieniać
hasła, bez opcji użytkownik musi zmienić hasło przy pierwszym logowaniu). Co do uprawnień konta,
należy rozważyć przydzielenie konta do roli lokalnego administratora (dla potrzeb na przykład
automatycznego restartu usługi w przypadku nieoczekiwanego jej zatrzymania).

Czasami taka konfiguracja jest wymagana (na przykład przy instalacji systemu Microsoft SQL Server
w klastrze Microsoft Windows). Dla głównych usług możesz skonfigurować jedno konto (ułatwia to
zdecydowanie administrację kontami). Nazwa konta dla zwiększenia bezpieczeństwa nie powinna
kojarzyć się z systemem bazodanowym.

Narzędzia konfiguracji usług

Większość  systemów  operacyjnych  oferuje  narzędzie  do  zcentralizowanego  zarządzania  usługami
zainstalowanymi  w  systemie.  Jednak  ponieważ  ogólna  ilość  usług  w  systemach  operacyjnych  jest
ogromna,  a  dodatkowo  bardzo  często  usługi  systemu  bazodanowego  wymagają  dodatkowych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 6/16

ustawień, producenci zazwyczaj dodają narzędzie (lub zestaw narzędzi) do zarządzania usługami
tylko samego serwera SQL.

Dla przykładu, W systemie Microsoft SQL Server 2008 są dwa narzędzia do zarządzania usługami:
Configuration Manager oraz Reporting Services Configuration. Pierwszy program umożliwia
zarządzanie usługami i protokołami sieciowymi, zaś drugi pozwala dostosować usługę raportowania
serwera SQL. Narzędzie Configuration Manager ilustruje rys. 3.

Rys. 3 Konfiguracja serwisów

Przykładowe rozwiązanie

Wdrożenie systemu bazodanowego opartego na Microsoft SQL Server 2008 przebiega w kilku
etapach.

Pierwszym z nich jest wybór maszyny i systemu operacyjnego, który będzie podstawą dla serwera
SQL. Serwery takie jak serwer baz danych, poczty czy stron internetowych zawsze instalowane są
na gotowym środowisku operacyjnym. My mamy do dyspozycji maszynę z systemem Microsoft
Windows Server 2003, która w pełni odpowiada wymaganiom serwera SQL.

Następnym  etapem  jest  uzupełnienie  środowiska  o  wymagane  i  opcjonalne  komponenty.
Komponenty  wymagane,  takie  jak  .NET  Framework  2.0  czy  MS  XML  6,  znajduję  się  na  płycie  z
plikami  instalacyjnymi  Microsoft  SQL  Server  2008.  Elementy  użyteczne,  lecz  opcjonalne,  takie  jak
Microsoft

PowerShell,

można

doinstalować

własnego

źródła,

np.

witryny

www.microsoft.com/poland

Właściwa instalacja systemu bazodanowego zaczyna się po uruchomieni instalatora Microsoft SQL
Server 2008.

Środkowa  grupa  opcji  (Install)  pozwala  na  uruchomienie  właściwego  komponentu  programu
instalacyjnego  (rys.  ).  W  tym  kroku  instalator  jest  gotowy  do  organizacji  swojego  środowiska  w
naszym  systemie.  Dopiero  teraz  widać  główne  okno  centrum  instalacji  środowiska  serwera  SQL
Server 2008. Można tu dokonać aktualizacji serwera z wersji SQL Server 2000 lub SQL Server 2005,
przeczytać dokumentację, zainstalować komponenty dodatkowe lub przykładowe bazy.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 9/16

Rys. 7 Konfiguracja typu autoryzacji

W oknie dotyczącym powiadamiania firmy Microsoft o występujących błędach i użyciu zasobów
serwera SQL możemy zgodzić się lub nie na wysyłanie tych informacji. Przed zaznaczeniem
czegokolwiek warto poczytać dokładnie, jakie informacje będą wysyłane.

Pora na podsumowanie opcji instalacji. W oknie Ready to Install możesz przejrzeć wszystkie
zaznaczone uprzednio opcje, jeżeli coś się nie zgadza, zawsze możesz cofnąć się i wprowadzić
zmianę.

Zatwierdzając wybrane opcje rozpoczynamy kopiowanie plików i wykonywanie skryptów
instalacyjnych zgodnie z założeniami ustalonymi przez nas wcześniej. Możemy podejrzeć postęp
instalacji poszczególnych składników serwera SQL.

Po zakończeniu procesu instalacji komponentów serwera SQL możemy przejść do konfiguracji
poszczególnych usług. W tym celu należy posłużyć się programem SQL Server Configuration
Manager dostepnym w grupie aplikacji SQL Server 2008.

W głównym oknie narzędzia konfiguracyjnego widzimy usługi związane z serwerem SQL. Usługa
automatyzacji zadań (SQL Server Agent) jest wyłączona, co nie jest dla nas stanem zadowalającym,
zatem należy ją aktywować.

Aby usługa uruchamiała się wraz ze startem systemu operacyjnego należy zaznaczyć odpowiednią
opcję w zakładce Service, co ilustruje rys. Błąd! Nie można odnaleźć źródła odwołania..

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 10/16

Rys. 8 Konfiguracja serwisów

Serwer SQL przeważnie działa w środowisku sieciowym opartym o protokoły TCP/IP. Ze względów
bezpieczeństwa obsługa tych protokołów domyślnie jest wyłączona. Jeżeli jesteśmy pewni
zabezpieczeń swojej sieci informatycznej, możemy włączyć obsługę TCP/IP dla serwera SQL.

Ostatnim etapem instalacji serwera SQL jest uruchomienie aplikacji zarządzającej SQL Management
Studio i próba połączenia się z istniejącą bazą danych AdventureWorks. Jeżeli to zadanie się
powiedzie, możemy uznać, że posiadamy gotowy do pracy system bazodanowy.

Porady praktyczne

W miarę możliwości dokonuj instalacji serwera SQL na systemie nieobciążonym przez inne serwery.
W  praktycznej  działalności  serwery  bazodanowe  mocno  wykorzystują  zasoby  sprzętowe  takie  jak
czas procesora, pamięć operacyjna czy dostęp do dysku twardego. Współdzielenie tych zasobów z
innymi usługami zdecydowanie obniża wydajność obsługi baz danych.
Pamiętaj  o  możliwości  instalacji  wielu  instancji  serwera  SQL  na  jednej  maszynie  fizycznej.
Umożliwia  to  logiczne  i  organizacyjne  rozdzielenie  poszczególnych  grup  baz  danych  a  co  za  tym
idzie lepsza kontrolę nad nimi.
Dodatkowe serwisy możesz doinstalować w miarę potrzeb. Pamiętaj, że każdy z nich to potencjalne
obciążenie  dla  systemu  i  luka  w  zabezpieczeniach.  Planując  rozszerzanie  funkcjonalności  serwera
bazodanowego  o  dodatkowe  serwisy  warto  zastanowić  się  nad  jego  rozbudową  od  strony
sprzętowej.
Już  na  etapie  instalacji  serwera  bazodanowego  warto  przemyśleć  sprawę  bezpieczeństwa
poszczególnych usług. Usługi serwera powinny posiadać dedykowane konta, za pomocą których są
uruchamiane.  Wydzielenie  kont  i  przyznanie  im  wąskiego  zakresu  praw  minimalizuje  możliwość
użycia  ich  przez  nieautoryzowane  osoby.  Więcej  o  zabezpieczeniach  serwera  bazodanowego
znajdziesz w module 10.
Dobrą praktyką jest wydzielenie zasobu sieciowego z plikami instalacyjnymi serwera SQL. Pozwoli
to  na  szybką  re-instalację  lub  dodanie  nowych  komponentów  do  systemu.  Niezależnie  od  tego
warto posiadać kopię plików instalacyjnych na nośniku przenośnym (płyta DVD, pendrive, mobilny
dysk  twardy  itp.),  jeżeli  zaistniałaby  potrzeba  instalacji  środowiska  na  maszynie  odciętej  od  sieci
informatycznej.  Dotyczy  to  także  systemów  towarzyszących  serwerowi  baz  danych  takich  jak  np.:
serwis publikacji stron WWW.
W przypadku stwierdzenia braku dostępu do instancji serwera SQL, w pierwszej kolejności sprawdź,
czy  odpowiednie  serwisy  systemowe  są  uruchomione.  Jeżeli  w  wyniku  jakiegoś  konfliktu  usługi
serwera  SQL  zostały  wyłączone  lub  zatrzymane,  dokonaj  ich  ponownej  aktywacji.  W  skrajnym

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 11/16

przypadku  należy  zrestartować  system  operacyjny,  który  może  blokować  usługi  systemu
bazodanowego przez własne usługi zależne.
Nigdy nie zaniedbuj regularnego skanowania systemu operacyjnego, na którym znajduje się system
bazodanowy pod względem obecności wirusów i złośliwego oprogramowania. System bazodanowy
jest centralną składnicą danych organizacji, a ich utrata zawsze jest bardzo kosztowna.
Zawsze sprawdzaj dostępność najnowszych aktualizacji dla systemu bazodanowego, jak i systemu
operacyjnego,  na  którym  serwer  SQL  jest  zainstalowany.  Pamiętaj,  że  nie  ma  oprogramowania
doskonałego  a  producenci  starają  się  ulepszać  swoje  produkty  m.in.  poprzez  udostępnianie
aktualizacji.  Widać  to  wyraźnie  w  systemach  operacyjnych  gdzie  np.:  bez  nowych  aktualizacji  w
ogóle nie można zainstalować niektórych aplikacji.

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:

•

rozumiesz, co oznacza serwis systemowy, serwis bazy danych, instalacja serwisu

•

rozumiesz zasadę działania uruchomienia serwisów w kontekście użytkownika

•

umiesz wymienić i opisać podstawowe komponenty systemu bazodanowego

•

umiesz podać przykład zastosowania systemu bazodanowego w praktyce

Dodatkowe źródła informacji

Kalen Delaney, Microsoft SQL Server 2005: Rozwiązania praktyczne krok po kroku, Microsoft
Press, 2006

Podręcznika adresowany do projektantów systemów bazodanowych, którzy
opanowali już podstawy idei relacyjnych baz danych. Przedstawia praktyczne
problemy i ich szczegółowe rozwiązania.

Edward Whalen, Microsoft SQL Server 2005 Administrator’s Companion, Microsoft Press, 2006

Jest  to  przejrzysty  przewodnik,  zawierający  wszystkie  informacje  potrzebne  do
wdrożenia,  administrowania  i  obsługi  SQL  Server  2005.  Znajdziesz  w  nim  wiele
procedur i wskazówek opartych na doświadczeniach profesjonalistów pracujących z
Microsoft SQL Server 2005.

Dusan Petkovic, Microsoft SQL Server 2008: A Beginner's Guide, McGraw-Hill, 2008

Pozycja  adresowana  do  osób  zaczynających  przygodę  z  bazami  danych.  Znajdziemy  tu
wprowadzenie  do  relacyjnych  baz  danych,  sposoby  ich  projektowania,  optymalizacji  i  w końcu
wdrożenia  w  najnowszej  odsłonie  serwera  SQL  w  wersji  2008.  Omówienie  języka  T-SQL  w
osobnej, dużej części książki jest kolejną mocną tej pozycji.

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

Laboratorium podstawowe

Problem 1 (czas realizacji 45 min)

Firma  National  Insurance  w  związku  z  dogodnymi  warunkami  na  rynku
rozwój. Obsługa rosnącej liczby klientów przysparza coraz więcej kłopotów, głównie działowi analiz.
Na  ostatnim  zebraniu  padła  propozycja  wdrożenia  nowego  systemu  bazodanowego  opartego  na
technologii Microsoft SQL Server 2008, który pom
większej  ilości  danych  o  klientach  firmy  co  potencjalnie  przełoży  się  na  jakość  obsługi    i  przyszłe
zyski.

Wdrożenie  nowego  systemu  złożono  na  barki  głównego  informatyka  firmy,  który  do  tej  pory
zajmował  się  głownie  zagadnieniami  związanymi  z  administracją  systemami  operacyjnymi
i bezpieczeństwem  sieci.  Jako  praktykant  dostałeś  za  zadanie  wdrożyć  serwera  SQL  2008  na
testowej  platformie.  Twoje  doświadczenia  z  procesu  instalacji  i  konfiguracji  serwera  będą
wykorzystane przy uruchamianiu systemu roboczego.

Zadanie

Tok postępowania

Uruchom

instalację serwera
SQL Server 2008

•

Uruchom maszynę wirtualną

—
—

•

Klik

•

Na ekranie powinno pokazać się okno instalatora Mi
2008

Dokonaj

konfiguracji
przedinstalacyjnej

•

Z grupy
and Samples

•

Przeczytaj i zaakceptuj postanowienia umowy licencyjnej poprzez
zaznaczenie pola

•

Kliknij przycisk

•

Kliknij przycisk

Dokonaj

•

Kliknij odnośnik

Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 12/16

Laboratorium podstawowe

Problem 1 (czas realizacji 45 min)

Firma  National  Insurance  w  związku  z  dogodnymi  warunkami  na  rynku
rozwój. Obsługa rosnącej liczby klientów przysparza coraz więcej kłopotów, głównie działowi analiz.
Na  ostatnim  zebraniu  padła  propozycja  wdrożenia  nowego  systemu  bazodanowego  opartego  na
technologii Microsoft SQL Server 2008, który pomoże usprawnić procesy związane z obróbką coraz
większej  ilości  danych  o  klientach  firmy  co  potencjalnie  przełoży  się  na  jakość  obsługi    i  przyszłe

Wdrożenie nowego systemu złożono na barki głównego informatyka firmy, który do tej pory

łownie zagadnieniami związanymi z administracją systemami operacyjnymi

Jako praktykant dostałeś za zadanie wdrożyć serwera SQL 2008 na

testowej platformie. Twoje doświadczenia z procesu instalacji i konfiguracji serwera będą

ystane przy uruchamianiu systemu roboczego.

Tok postępowania

Uruchom maszynę wirtualną BD2008.

—

Jako nazwę użytkownika podaj Administrator.

—

Jako hasło podaj P@ssw0rd.

Maszyna wirtualna używa obrazów ISO płyt CD/DVD na równi z
płytami fizycznymi. Ważne jest, aby w wirtualnym napędzie DVD
umieszczony był obraz płyty o nazwie SQLDEV_ENU.ISO

Kliknij dwukrotnie ikonę Mój komputer, a później
Na ekranie powinno pokazać się okno instalatora Mi
2008, tak jak na rys. :

Rys. 9 Strona główna programu instalacyjnego

Z grupy Install kliknij odnośnik Server components, tools,
and Samples.
Przeczytaj i zaakceptuj postanowienia umowy licencyjnej poprzez
zaznaczenie pola I accept the license terms.
Kliknij przycisk Next.
Kliknij przycisk Install.

Kliknij odnośnik New Installation.

Moduł 2

Instalacja i konfiguracja MS SQL Server 2008

Firma National Insurance w związku z dogodnymi warunkami na rynku przeżywa dynamiczny
rozwój. Obsługa rosnącej liczby klientów przysparza coraz więcej kłopotów, głównie działowi analiz.
Na ostatnim zebraniu padła propozycja wdrożenia nowego systemu bazodanowego opartego na

oże usprawnić procesy związane z obróbką coraz

większej ilości danych o klientach firmy co potencjalnie przełoży się na jakość obsługi i przyszłe

Wdrożenie nowego systemu złożono na barki głównego informatyka firmy, który do tej pory

łownie zagadnieniami związanymi z administracją systemami operacyjnymi

Jako praktykant dostałeś za zadanie wdrożyć serwera SQL 2008 na

testowej platformie. Twoje doświadczenia z procesu instalacji i konfiguracji serwera będą

ISO płyt CD/DVD na równi z

aby w wirtualnym napędzie DVD

SQLDEV_ENU.ISO.

a później CD ROM (D:).

Na ekranie powinno pokazać się okno instalatora Microsoft SQL Server

Strona główna programu instalacyjnego

Server components, tools, Books Online,

Przeczytaj i zaakceptuj postanowienia umowy licencyjnej poprzez

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 13/16

instalacji
właściwej

•

W oknie sprawdzania konfiguracji kliknij przycisk Details, a potem Next.

•

Zgodnie z rys. 1 zaznacz kolejno Database Engine Services, Full text
search, Analysis Services, Client Tools, SQL Server books online,
Business Intelligence Development Studio.

•

Kliknij przycisk Next.

Rys. 10 Wybór składników instalacji

•

Zostaw ustawienia standardowe i kliknij przycisk Next.

•

W polu Account wpisz nazwę Administrator.

•

W polu Password wpisz P@ssw0rd.

•

Kliknij przycisk Apply to All.

Rys. 11 Określenie praw dostępu do składników

•

Kliknij zakładkę Collation.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

•

Dla pola

•

Kliknij pole

•

Z listy

•

Kliknij przycisk

•

Dla pola

•

Z listy

•

Kliknij przycisk

•

Przejdź do następnego okna klikając przycisk

•

Aby dodać konto bieżącego użytkownika (
Add Current User

•

Aby dodać konto bieżącego użytkownika (
Add Current User

•

Kliknij

•

Przeczytaj informacje w otwartej stronie WWW

•

Zamknij okno przeglądarki

•

Kliknij

•

Aby zakończyć ten etap i przejść do podsumowania instalacji
przycisk

•

Wyświetl

dziennik

instalacji

klikając

odnośnik

files

•

Przeczytaj informacje zawarte w dzienniku i zamknij okno notatnika

•

Kliknij przycisk

Konfiguracja

•

Kliknij przycisk
Configuration Tools

Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 14/16

Dla pola Database Engine kliknij przycisk Customize
Kliknij pole Windows collation designator.
Z listy Collation designator wybierz Polish.
Kliknij przycisk OK.
Dla pola Analysis Services kliknij przycisk Customize
Z listy Collation designator wybierz Polish.
Kliknij przycisk OK.
Przejdź do następnego okna klikając przycisk Next
Aby dodać konto bieżącego użytkownika (Administrator
Add Current User.
Kliknij przycisk Next.

Rys. 12 Konfiguracja typu autoryzacji

Dla serwisu analitycznego również należy określić konto z
uprawnieniami administracyjnymi.

Aby dodać konto bieżącego użytkownika (Administrator
Add Current User.
Kliknij przycisk Next.
Kliknij odnośnik View the Microsoft SQL Server.
Przeczytaj informacje w otwartej stronie WWW.
Zamknij okno przeglądarki.
Kliknij przycisk Next.
Kliknij przycisk Install.
Aby zakończyć ten etap i przejść do podsumowania instalacji
przycisk Next.
Wyświetl

dziennik

instalacji

klikając

odnośnik

files\Microsoft SQL Server\100\....
Przeczytaj informacje zawarte w dzienniku i zamknij okno notatnika
Kliknij przycisk Close.

Kliknij przycisk START -> Programy -> Microsoft SQL Server 2008

Configuration Tools -> SQL Server Configuration Manager

Moduł 2

Instalacja i konfiguracja MS SQL Server 2008

Customize.

Next.

Administrator), kliknij przycisk

Konfiguracja typu autoryzacji

Dla serwisu analitycznego również należy określić konto z

Administrator), kliknij przycisk

Aby zakończyć ten etap i przejść do podsumowania instalacji, kliknij

Wyświetl

dziennik

instalacji

klikając

odnośnik

C:\Program

Przeczytaj informacje zawarte w dzienniku i zamknij okno notatnika.

Microsoft SQL Server 2008 ->

SQL Server Configuration Manager.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 15/16

usług serwera

•

W nowym oknie rozwiń drzewo SQL Server Network Configuration
klikając przycisk +.

•

Zaznacz pole SQL Server Services.

•

Kliknij dwukrotnie pole SQL Server Agent.

•

Kliknij przycisk Start.

•

Kliknij na zakładce Service.

•

W polu Start Mode wybierz z listy rozwijanej opcję Automatic.

•

Kliknij przycisk OK.

•

Kliknij pole Protocols for MSSQLSERVER.

•

Kliknij dwukrotnie pole TCP/IP.

•

Dla pola Enabled z listy rozwijanej wybierz Yes.

•

Kliknij przycisk OK.

•

Zamknij okno SQL Server Configuration Manager.

Sprawdzenie

poprawności
instalacji

•

Kliknij przycisk Start -> Programy -> Microsoft SQL Server 2008 ->
Management Studio.

•

W nowym oknie kliknij przycisk Connect.

•

Jeżeli instalacja system jest poprawna, będziesz mógł w polu Object
Explorer rozwinąć drzewo Databases -> AdventureWorks.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 2

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 16/16

Laboratorium rozszerzone

Zadanie 1 (czas realizacji 90 min)

Jesteś  głównym  informatykiem  firmy  National  Insurance.  Niedawne  wdrożenie  nowego  serwera
bazodanowego  opartego  na  Microsoft  SQL  Server  2008  zakończyło  się  sukcesem.  Firma  planuje
rozbudowę  infrastruktury  informatycznej  o  farmę  serwerów  Microsoft  SQL  Server  2008  i  ich
integrację z istniejącymi bazami danych.

W dotychczasowej działalności od strony baz danych wykorzystywane były serwery Microsoft SQL
Server  2000  i  Microsoft  SQL  Server  2005,  na  których  hostowane  bazy  zawierały  dane  w  języku
polskim  i  angielskim.  Niezbyt  dobrze  wyglądała  sprawa  prezentacji  danych,  każdy  z  menedżerów
korzystał  ze  swoich  narzędzi,  niekompatybilnych  ze  sobą.  Jako  dobry  administrator  poleciłeś
przetestować  nowy  serwer  pod  względem  kompatybilności  z  istniejącymi  bazami oraz  możliwości
nowego serwisu raportującego.

Zadanie polega na instalacji nowej instancji serwera SQL Server 2008 (na nowej, testowej maszynie)
spełniającej kryteria:

•

Pliki bazy danych i pliki systemu operacyjnego są rozdzielone.

•

Zachowana jest kompatybilność wstecz względem używanych wcześniej baz.

•

Instalacja zawiera serwis silnika, serwis analityczny i serwis raportujący.

•

Dostarczone są narzędzia ułatwiające administrację.

•

Dostarczone są przykładowe bazy.

Zespół testowy przygotuje również dokumentację w formie raportu z instalacji.

ITA-101 Bazy Danych

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 2/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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 4/26

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 5/26

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)

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 6/26

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 7/26

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 8/26

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 9/26

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 10/26

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 11/26

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

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 14/26

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 15/26

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

Dodatkowe źródła informacji

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.

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.

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

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
częścią  twojego  semestralnego  projektu  zaliczeniowego.  W
zaprojektowany przez kolegę z zespołu
SQL Server 2008 wraz z graficznym interfejsem
zadania.

Zadanie

Tok postępowania

Utwórz bazę

danych

•

Uruchom maszynę wirtualną

—
—

•

Kliknij
SQL Server Management

•

W oknie logowania kliknij

•

W oknie
Databases

•

W pole

•

Ustaw początkowe wielkości plików na
pliku dziennika transakcji

•

Kliknij przycisk

Ustaw opcje

•

Kliknij prawym przyciskiem myszy

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 16/26

Laboratorium podstawowe

ealizacji 45 min)

W firmie National Insurance trwa właśnie sezon urlopowy. Jako jedyny obecny administrator baz

postanowiłeś przetestować implementacje bazy danych

Prace Dyplomowe

częścią twojego semestralnego projektu zaliczeniowego. Wytyczne do bazy
zaprojektowany przez kolegę z zespołu diagram. Postanowiłeś użyć dostępnego systemu
SQL Server 2008 wraz z graficznym interfejsem SQL Server Management Studio do wykonania tego

Tok postępowania

Uruchom maszynę wirtualną BD2008.

—

Jako nazwę użytkownika podaj Administrator.

—

Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w
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
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
Ustaw początkowe wielkości plików na 4 MB dla pliku danych i
pliku dziennika transakcji.

Rys. 10 Tworzenie nowej bazy danych

Kliknij przycisk OK.

Kliknij prawym przyciskiem myszy bazę danych

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

W firmie National Insurance trwa właśnie sezon urlopowy. Jako jedyny obecny administrator baz

Prace Dyplomowe, która jest

do bazy jasno określa

diagram. Postanowiłeś użyć dostępnego systemu Microsoft

Management Studio do wykonania tego

li nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual

dodaj nową maszynę używając wirtualnego dysku twardego z pliku

Microsoft SQL Server 2008 uruchom

kliknij prawym przyciskiem myszy folder

New Database.

wpisz nazwę bazy danych PraceDyplomowe.

dla pliku danych i 1 MB dla

Tworzenie nowej bazy danych

danych PraceDyplomowe i z

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

bazy danych

menu kontekstowego wybierz

•

W oknie
stronie opcj

•

Zmień ustawienie opcji

•

Kliknij przycisk

Dodaj tabele

do bazy danych

•

Kliknij prawym przyciskiem myszy pol
bazy

•

Kliknij opcję

•

Kliknij pol

•

Kliknij pol

•

Odznacz pole
kolumny wartości

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 17/26

menu kontekstowego wybierz Properties.
W oknie Database Properties - PraceDyplomowe
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
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. 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.

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

PraceDyplomowe kliknij po lewej

Simple.

tawianie właściwości bazy danych

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

w rozwiniętym drzewie

ID_TypStudiow.

smallint.

w celu zapobiegania wpisywania do tej

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

Uwórz

pozostałe proste
tabele

•

Bazując na poprzednim schemacie postępowania utwórz tabele
i
ID_SlowoKlucz

•

Jako rezultat powinieneś zobaczyć listę trzech tabel w polu
PraceDyplomowe

Utwórz

główne tabele
bazy

•

Kliknij prawym przyciskiem myszy pol
NewTable

—
—
—
—
—
—

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 19/26

Rys. 17 Tworzenie klucza głównego tabeli

Bazując na poprzednim schemacie postępowania utwórz tabele
i SlowoKlucz zawierające odpowiednio kolumny
ID_SlowoKlucz, obydwie z typem danych varchar(25)
Jako rezultat powinieneś zobaczyć listę trzech tabel w polu
PraceDyplomowe.

Rys. 18 Pozostałe proste tabele bazy PraceDyplomowe

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

—

Wprowadź nazwę dla nowej kolumny ID_Pracy

—

Ustal typ danych dla tej kolumny jako smallint.

—

Wprowadź nazwę dla następnej kolumny jako

—

Ustal typ danych jako varchar(100).

—

Wprowadź nazwę dla trzeciej kolumny jako Data

—

Jako typ danych ustal datetime.

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

Tworzenie klucza głównego tabeli

Bazując na poprzednim schemacie postępowania utwórz tabele Stopien

zawierające odpowiednio kolumny ID_Stopien i

archar(25).

Jako rezultat powinieneś zobaczyć listę trzech tabel w polu Tables bazy

Pozostałe proste tabele bazy PraceDyplomowe

Pora na utworzenie głównej tabeli naszej bazy danych. Tabela

jest punktem centralnym bazy i zawiera kolumny

Tables i wybierz opcję

ID_Pracy.

Wprowadź nazwę dla następnej kolumny jako Temat.

Data.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

—
—

•

Dla każdej kolumny odznacz opcję

•

Ustal klucz główny klikając prawym przyciski
ID_Pracy

•

Zapisz tabelę pod nazwą

•

Kliknij prawym przyciskiem myszy pol
Table

—
—
—
—
—
—
—
—

•

Dla każdej kolumny odznacz opcję

•

Ustal klucz główny klikając prawym przyciskiem myszy na kolumnie
ID_Osoby

•

Zapisz tabelę pod nazwą

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 20/26

—

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

—

Wprowadź nazwę dla nowej kolumny ID_Osoby

—

Ustal typ danych dla tej kolumny jako smallint.

—

Wprowadź nazwę dla następnej kolumny jako

—

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

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

Ocena.

Ustal klucz główny klikając prawym przyciskiem myszy kolumnę

klikając ikonę dyskietki.

Główna tabela bazy PraceDyplomowe

przechowująca informacje o

osobach wykonujących prace dyplomowe oraz wykładowcach i

i wybierz opcję New

ID_Osoby.

Wprowadź nazwę dla następnej kolumny jako Imie.

Nazwisko.

Nr_Indeksu.

Ustal klucz główny klikając prawym przyciskiem myszy na kolumnie

klikając ikonę dyskietki.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

Utwórz tabele

pomocnicze

•

Kliknij prawym przyciskiem myszy pol
Table

—
—
—
—

•

Dla każdej kolumny odznacz opcję

•

Zaznacz z wciśniętym klawiszem

•

Ustal klucz główny klikając prawym przyciskiem myszy na zaznaczeniu i
wybierz opcję

•

Zapisz tabelę pod nazwą

Utwórz

pozostałe tabele
pomocnicze

•

Bazując na poprzednim schemacie postępowania utwórz tabele
i
ID_Pracy

•

Jako rezultat powinieneś zobaczyć listę wszystkich tabel w polu
bazy

Utwórz

związki pomiędzy

•

Kliknij prawym klawiszem myszy pol
PraceDyplomowe

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 21/26

Kliknij prawym przyciskiem myszy pole Tables
Table.

—

Wprowadź nazwę dla nowej kolumny ID_Osoby

—

Ustal typ danych dla tej kolumny jako smallint.

—

Wprowadź nazwę dla następnej kolumny jako

—

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

Bazując na poprzednim schemacie postępowania utwórz tabele
i Recenzent, zawierające odpowiednio kolumny
ID_Pracy oraz ID_Osoby, wszystkie z typem danych
Jako rezultat powinieneś zobaczyć listę wszystkich tabel w polu
bazy PraceDyplomowe.

Rys. 15 Wszystkie tabele bazy PraceDyplomowe

Kliknij prawym klawiszem myszy pole Database Diagrams
PraceDyplomowe.

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

i wybierz opcję New

ID_Osoby.

Wprowadź nazwę dla następnej kolumny jako ID_Pracy.

Ponieważ dla tej tabeli unikalna będzie zarówno jedna jak i druga

klucz główny możemy założyć na obydwu jednocześnie.

obydwie kolumny.

Ustal klucz główny klikając prawym przyciskiem myszy na zaznaczeniu i

klikając ikonę dyskietki.

Tabela Autorzy z kluczem głównym na dwóch kolumnach

Bazując na poprzednim schemacie postępowania utwórz tabele Slownik

zawierające odpowiednio kolumny ID_SlowoKlucz,

, wszystkie z typem danych smallint.

Jako rezultat powinieneś zobaczyć listę wszystkich tabel w polu Tables

Wszystkie tabele bazy PraceDyplomowe

Database Diagrams bazy

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 22/26

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 24/26

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.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

ITA

101 Bazy Danych

•

Kliknij przycisk

•

Kliknij pole

•

Zaznacz pole

•

Kliknij przycisk

ski, Przemysław Kowalczuk, Konrad Markowski

Instalacja i konfiguracja MS SQL Server 2008

Strona 25/26

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
strukturę bazy w obszarze Object Explorer.

Moduł 3

Instalacja i konfiguracja MS SQL Server 2008

Wskazywanie pliku z kopią zapasową

zakończyło się sukcesem.

czy nic po drodze nie zginęło, przeglądając

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 3

ITA

101 Bazy Danych

Instalacja i konfiguracja MS SQL Server 2008

Strona 26/26

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.

ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

Wersja 1.0

Wewnętrzna struktura bazy danych

Spis treści

Wewnętrzna struktura ......................................................................................................................... 1

Informacje o module ............................................................................................................................ 2

Przygotowanie teoretyczne ................................................................................................................. 3

Przykładowy problem .................................................................................................................. 3

Podstawy teoretyczne.................................................................................................................. 3

Przykładowe rozwiązanie ............................................................................................................. 8

Porady praktyczne ..................................................................................................................... 11

Uwagi dla studenta .................................................................................................................... 12

Dodatkowe źródła informacji..................................................................................................... 12

Laboratorium podstawowe ................................................................................................................ 13

Problem 1 (czas realizacji 25 min) .............................................................................................. 13

Problem 2 (czas realizacji 20 min) .............................................................................................. 17

Laboratorium rozszerzone ................................................................................................................. 19

Zadanie 1 (czas realizacji 90 min) ............................................................................................... 19

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 2/19

Informacje o module

Opis modułu

W  tym  module  znajdziesz  informacje,  w  jaki  sposób  w  SQL  Server  2008
przechowywane  są  dane,  jak  zbudowana  jest  strona  danych,  oraz  w  jaki
sposób  przechowywane  są  podstawowe  obiekty  w  bazie  danych.  Dowiesz
się również w jaki sposób należy wyznaczać wielkość pliku danych.

Cel modułu

Celem modułu jest zapoznanie czytelnika z wewnętrzną strukturą
przechowywania danych w SQL Server 2008.

Uzyskane kompetencje

Po zrealizowaniu modułu będziesz:

•

Wiedział, w jaki sposób zbudowane są strony danych (nagłówek
strony, wiersze danych, tablica przesunięć wierszy)

•

potrafił badać zawartość stron danych używamy instrukcji

DBCC

PAGE, która umożliwia oglądanie nagłówka strony, wierszy danych i
tablicy przesunięć wierszy

•

wiedział jak wygląda struktura wierszy danych

•

potrafił wyświetlić i przeanalizować wiersze danych zapisane w
tabelach

Wymagania wstępne

Przed przystąpieniem do pracy z tym modułem powinieneś:

•

potrafić budować diagram ERD (patrz: moduł 1)

•

wiedzieć, w jaki sposób tworzy się nową bazę danych

•

wiedzieć, w jaki sposób tworzy się podstawowe obiekty bazy danych

Mapa zależności modułu

Zgodnie z mapą zależności przedstawioną na rys. 1, przed przystąpieniem
do realizacji tego modułu należy zapoznać się z materiałem zawartym
w modułach 1 i 3.

Moduł 4

Dodatek

Moduł 1

Moduł 2

Moduł 3

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
ITA-101 Bazy danych

Przygotowanie teoretyczne

Przykładowy problem

Jak zapewne wiesz, z SQL Server 2008 można korzystać w bardzo efektywny sposób nie mając
wiedzy na temat sposobu wewnętrznej organizacji danych, jednak w wielu przypadkach dobrze jest
wiedzieć, jak takie przechowywane dane są zorganizowane. Ułatwia to zrozumienie wielu
mechanizmów funkcjonujących w SQL Server 2008

Typowy problem pojawiający
danych,  zbudowany  diagram  ERD,  zdefiniowane  relacje  oraz  przypisane  atrybutom  typy  danych.
Nie  zawsze  jednak  etap  projektowania  jest  przeprowadzony  we właściwy  sposób.
sposób  zorganizowane  są  dane  możemy  lepiej  dobierać  typy  danych  na  atrybutach  mając
świadomość  tego,  gdzie  fizycznie  są  one  umieszczone.  Możemy  uniknąć  pewnych  błędów,  na
przykład  co  do  doboru  wielkości  typu  danych.  Szczególnie  duże  znaczenie  ma  to  w  przypadku
obiektów typu OLE.

Kolejnym  problemem  pojawiającym  się  w  tym  zagadnieniu  jest  próba  odpowiedzenia  sobie  na
pytanie,  jaka  powinna  być  początkowa  wielkość  pliku  danych  i  dziennika  transakcji  w  momencie,
kiedy  wiemy,  ile  danych  ma  zostać  wpisanych  do  naszej  bazy.  Na  podstawie  t
kolejności możemy ustawić, o ile pliki danych i dziennika transakcji mają wzrosnąć.

Z punktu widzenia administracji bazą danych są to bardzo ważne problemy, które możemy
rozwiązać dzięki znajomości budowy wewnętrznej struktury w SQL Server

Podstawy teoretyczne

Chociaż z SQL Server można korzystać efektownie bez znajomości wewnętrznej budowy,
zrozumienie szczegółów przechowywania danych pomaga w pisaniu wydajnych aplikacji.

Gdy tworzy się tabelkę, do wielu katalogów systemowych wstawia
niezbędnymi do jej obsługi. Wiersze są dodawane do katalogów systemowych
sysindexes i syscolumns

Dla  każdej  nowo  utworzonej  tabeli  do  widoku
nazwę,  identyfikator  obiektu
każdej kolumny z nowej tabeli informacje zawierające nazwę kolumny, typ danych i długość. Każda
z  kolumn  otrzymuje  unikatowy  identyfikator,  który  wyznacza  kolejność,  w  jakiej  zostały  kolumny
zdefiniowane w tabeli.

Na rys. 2 pokazano wiersze, które dodawane są do widoku
tworzenia tabeli

Ksiazka.

Rys.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 3/19

Przygotowanie teoretyczne

Typowy problem pojawiający się w tym zagadnieniu jest następujący. Mamy zaprojektowaną bazę
danych, zbudowany diagram ERD, zdefiniowane relacje oraz przypisane atrybutom typy danych.
Nie zawsze jednak etap projektowania jest przeprowadzony we właściwy sposób.

organizowane są dane możemy lepiej dobierać typy danych na atrybutach mając

świadomość tego, gdzie fizycznie są one umieszczone. Możemy uniknąć pewnych błędów, na
przykład co do doboru wielkości typu danych. Szczególnie duże znaczenie ma to w przypadku

Z punktu widzenia administracji bazą danych są to bardzo ważne problemy, które możemy
rozwiązać dzięki znajomości budowy wewnętrznej struktury w SQL Server 2008.

QL Server można korzystać efektownie bez znajomości wewnętrznej budowy,

zrozumienie szczegółów przechowywania danych pomaga w pisaniu wydajnych aplikacji.

Gdy tworzy się tabelkę, do wielu katalogów systemowych wstawiane są rekordy z informacjami
niezbędnymi do jej obsługi. Wiersze są dodawane do katalogów systemowych

syscolumns.

Dla każdej nowo utworzonej tabeli do widoku

sysobjects dodawany jest wiersz zawierający

nazwę, identyfikator obiektu oraz właściciela nowej tabeli. Widok

syscolumns

każdej kolumny z nowej tabeli informacje zawierające nazwę kolumny, typ danych i długość. Każda
z kolumn otrzymuje unikatowy identyfikator, który wyznacza kolejność, w jakiej zostały kolumny

pokazano wiersze, które dodawane są do widoku

sysobjects

Rys. 2 Informacje katalogowe zapisane po utworzeniu tabeli

Moduł 4

Wewnętrzna struktura

się w tym zagadnieniu jest następujący. Mamy zaprojektowaną bazę

danych, zbudowany diagram ERD, zdefiniowane relacje oraz przypisane atrybutom typy danych.
Nie zawsze jednak etap projektowania jest przeprowadzony we właściwy sposób. Wiedząc w jaki

organizowane są dane możemy lepiej dobierać typy danych na atrybutach mając

świadomość tego, gdzie fizycznie są one umieszczone. Możemy uniknąć pewnych błędów, na
przykład co do doboru wielkości typu danych. Szczególnie duże znaczenie ma to w przypadku

Kolejnym  problemem  pojawiającym  się  w  tym  zagadnieniu  jest  próba  odpowiedzenia  sobie  na
pytanie,  jaka  powinna  być  początkowa  wielkość  pliku  danych  i  dziennika  transakcji  w  momencie,
kiedy  wiemy,  ile  danych  ma  zostać  wpisanych  do  naszej  bazy.  Na  podstawie  tej  informacji  w
kolejności możemy ustawić, o ile pliki danych i dziennika transakcji mają wzrosnąć.

Z punktu widzenia administracji bazą danych są to bardzo ważne problemy, które możemy

2008.

QL Server można korzystać efektownie bez znajomości wewnętrznej budowy,

zrozumienie szczegółów przechowywania danych pomaga w pisaniu wydajnych aplikacji.

ne są rekordy z informacjami

niezbędnymi do jej obsługi. Wiersze są dodawane do katalogów systemowych

sysobjects,

dodawany jest wiersz zawierający

syscolumns otrzymuje dla

sysobjects i columns podczas

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 4/19

Strony danych

Strony danych są strukturami zawierającymi wszystkie dane (z wyjątkiem

text i image), których

stały rozmiar wynosi 8 KB (8192 bajtów). Składają się one z trzech elementów (rys. 3): nagłówka
strony, wierszy danych i tablicy przesunięć wierszy.

Rys. 3 Struktura strony danych

Nagłówek strony

Nagłówek strony danych zawiera pierwszych 96 bajtów strony danych. Informacje zawarte w
nagłówku pokazano w tab. 1.

Tab. 1 Informacje zawarte w nagłówku strony

Pole

Zawartość

m_poleID

Numer pliku i numer strony bazy danych.

m_nextPage

Numer pliku i nazwa następnej strony. Parametr wyświetlany jeżeli
bieżąca strona należy do łańcucha stron.

m_prev_Page

Numer pliku i nazwa poprzedniej strony. Parametr wyświetlany jeżeli
bieżąca strona należy do łańcucha stron.

m_objID

Identyfikator obiektu, do którego należy bieżąca strona.

m_lsn

Numer LSN używany do modyfikowania tej strony.

m_slotCnt

Łączna liczba wierszy tej strony.

m_level

Poziom strony w indeksie. Dla stron liści wartość zawsze równa 1.

IndexId

Identyfikator indeksu strony. Dla stron danych zawsze wartość równa 1.

m_freeData

Przesunięcie bajtowe pierwszego wolnego obszaru na tej stronie.

pminlen

Liczba bajtów części wiersza zawierającej pola o stałej długości.

m_freeCnt

Liczba niewykorzystanych bajtów na stronie.

m_reservedCnt

Liczba bajtów zarezerwowanych przez wszystkie transakcje.

m_xactReserved

Liczba bajtów zarezerwowanych przez ostatnio uruchomiona transakcje.

m_flagBits

2-bajtowy ciąg bitów zawierający dodatkowe informacje o stronie.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 5/19

Wiersze danych

Za nagłówkiem znajduje się obszar, w którym przechowywane są dane tabeli. Maksymalny rozmiar
pojedynczego wiersza danych wynosi 8060 bajtów. Wiersz danych nie może obejmować kilku stron.
Wyjątek stanowią kolumny, którym przypisano typ

image lub text. Należy zauważyć, iż liczba

wierszy może się różnić w zależności od struktury tabeli oraz przechowywanych danych. Tabele,
które posiadają kolumny o stałej długości będą zawierały na każdej stronie taką samą liczbę wierszy
danych, natomiast tabele zawierające kolumny o różnych długościach będą zawierały na każdej
stronie różną liczbę wierszy danych.

Tablica przesunięć wierszy

Tablica przesunięć wierszy jest blokiem 2-bajtowych wpisów, z których każdy stanowi przesunięcie
na stronie, od którego zaczynają się właściwe dane wiersza. Należy pamiętać o tym, że bajty te nie
są zapisywane w wierszu razem z danymi. Wpływają one jednak na liczbę wierszy mieszczących się
na stronie.

Badanie stron danych

Do badania zawartości stron danych używamy instrukcji

DBCC PAGE, która umożliwia oglądanie

nagłówka strony, wierszy danych i tablicy przesunięć wierszy dla każdej strony bazy danych.
Polecenia tego może używać tylko administrator systemu. Składnię polecenia

DBCC PAGE

pokazano poniżej:

DBCC PAGE ({id_bazy | nazwa_bazy}, numer_pliku, numer_strony[, opcje])

W tab. 2 zostały opisane podstawowe parametry instrukcji

DBCC PAGE

Tab. 2 Parametry polecenia DBCC PAGE
Parametr

Opis

id_bazy

Identyfikator bazy danych.

nazwa_bazy

Nazwa bazy danych.

numer_pliku

Numer pliku zawierającego stronę

numer_strony

Numer strony w obrębie pliku.

opcje

Parametr ustawiany opcjonalnie przyjmuje następujące
wartości:

•

0 – domyślnie. Zwraca informacje o nagłówku bufora i
nagłówku strony.

•

1 – zwraca informacje o nagłówku bufora, nagłówku
strony, każdym wierszu (osobno) oraz tablicę przesunięć
wierszy.

•

2 – zwraca informacje o nagłówku bufora, nagłówku
strony, zawartość całej strony oraz tablicę przesunięć
wierszy.

•

3  –  zwraca  informacje  o  nagłówku  bufora,  nagłówku
strony,  każdym  wierszu  (osobno)  oraz  tablicę  przesunięć
wierszy,  a  po  każdym  wierszu  listę  poszczególnych
wartości kolumn.

Przykładowy wynik działania instrukcji

DBCC PAGE

pokazano na rys. 4.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 6/19

Rys. 4 Przykładowy wydruk instrukcji DBCC PAGE

Wydruk instrukcji

DBCC PAGE składa się z czterech sekcji:

BUFFER – przedstawia informacje o buforze danej strony.

PAGE HEADER – pokazuje dane dla pól nagłówka strony.

DATA – zawiera informacje o każdym wierszu tabeli. Dane strony dzielone są na trzy części.
Część pierwsza (lewa kolumna) określa położenie bajtowe w obrębie wiersza, część druga
(środkowe cztery kolumny) zawiera właściwe dane zapisane na stronie, zaś część trzecia
(prawa kolumna) zawiera znakową reprezentacje danych.

OFFSET TABLE – pokazuje zawartość tablicy przesunięć wierszy, która znajduje się na końcu
strony. Pierwszy wiersz fizycznie znajdujący się na stronie jest wierszem numer 6, z
przesunięciem w tablicy przesunięć równym 96.

Struktura wiersza danych

Na rys. 5 pokazano ogólną strukturę wierszy danych. Na pierwszym miejscu znajdują się dane
kolumn o stałej długości. Za nimi znajdują się dane wszystkich kolumn o zmiennej długości.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 7/19

Rys. 5 Struktura wierszy danych

W każdym bloku o stałej długości lub danych o zmiennej długości dane są przechowywane w
kolejności w jakiej kolumny zostały zdefiniowane przy tworzeniu tabeli. Przyjrzyjmy się poniższemu
przykładowi:

CREATE TABLE Proba
{

Kolumna_1 int NOT NULL,

Kolumna_2 char(25) NOT NULL,

Kolumna_3 varchar(60) NULL,

Kolumna_4 int NOT NULL,

Kolumna_5 varchar(20) NOT NULL
}

W powyższym przykładzie w części związanej z danymi o stałej długości wiersze będą zawierały
najpierw dane kolumny

Kolumna_1 następnie Kolumna_2 i wreszcie Kolumna_4. W następnej

kolejności będą występowały dane o zmiennej długości związane odpowiednio z

Kolumną_3 i

Kolumna_5.

Tablice przesunięć kolumn

Wiersz danych, który ma kolumny o stałej długości, nie zawiera licznika zmiennych kolumn ani
tablicy przesunięć kolumn. Wiersz danych, w którym znajdują się kolumny o zmiennej długości,
zawiera tablicę przesunięć kolumn.

Połączenia stron

W SQL Server 2008 strony łączone są ze sobą na każdym poziomie indeksu. Jedynym sposobem, w
jaki SQL Server określa, które strony należą so tabeli, jest zaglądanie do stron IAM tabeli.
Poleceniem

DBCC EXTENTINFO można uzyskać listę wszystkich obszarów należących do obiektu.

W poniższym przykładzie pokazano listę wszystkich obszarów należących do obiektu

Orders w

bazie danych

Northwind.

Osiem pierwszych wierszy wskazuje rozmiar obszaru (

ext_size) równy 1. Wynika to z tego, że

pierwszych osiem stron w tabeli alokowanych jest z obszarów mieszanych. Gdy tabela osiągnie
osiem stron, wówczas SQL Server przydziela jej obszary jednolite po osiem stron każdy.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 8/19

Rys. 6 Wydruk wszystkich obszarów należących do obiektu

Rozmiar danych

Znając strukturę tabel oraz typy danych kolumn w bazie danych, możemy oszacować rozmiar pliku
danych.

Procedura szacowania rozmiaru pliku danych przebiega według następującego schematu:

Na podstawie tabeli określamy rozmiar pojedynczego wiersza. Dla typów danych o stałym
rozmiarze dodajemy liczbę bajtów poszczególnych kolumn, zaś dla typów o zmiennym
rozmiarze przyjmujemy wartość średnią.

Następnie maksymalną liczbę bajtów na pojedynczej stronie danych (8060) dzielimy przez
szacunkową wielkość wiersza. W wyniku otrzymujemy liczbę wierszy mieszczących się na
stronie.

Następnie szacunkową liczbę wierszy dzielimy przez otrzymaną liczbę wierszy na stronie. W
ten sposób otrzymujemy liczbę stron niezbędnych do zapisania danych znajdujących się w
tabeli.

W kolejnym kroku otrzymaną liczbę stron mnożymy przez 8196 B (rozmiar pojedynczej
strony), co daje nam w wyniku wielkość pliku danych.

Przykładowe rozwiązanie

Wyświetlanie wierszy danych

Zanim będziesz mógł wyświetlić wiersze danych w twojej tabeli w bazie danych będziesz musiał
odpowiednio zgromadzić informacje na temat numeru pliku i numeru strony na której interesujące
Cię dane są zawarte. Wyobraźmy sobie, że stworzyliśmy tabelę

Ksiazka o postaci:.

CREATE TABLE [dbo].[Ksiazka]
(

[ID_Ksiazka] [int] IDENTITY(1,1) NOT NULL,

[Tytul] [varchar](50) NULL,

[Autor] [varchar](30) NULL,

[Rok] [char](4) NULL

)

Do nowo powstałej tabeli

Ksiazka wpisujemy trzy dowolne rekordy.

Podczas tworzenia tabeli

Ksiazka do widoku systemowego:

•

sysindexes wstawiany jest nowy wiersz, przechowujący informację o nowym obiekcie

(tabela

Ksiazka), który przed chwilą został stworzony, co możemy zaobserwować na rys. 7.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
ITA-101 Bazy danych

•

syscolumns

wstawiane są

atrybutów, z których złożona jest tabela

W celu poprawnego uruchomienia
widoku

sysindexes (0x9D0000000100

na rys. 7). W pierwszym kroku należy zamienić bajty, aby uzyskać ciąg
Pierwsze dwie grupy (

00 01

00 9D) – numer strony. Zatem plik ma numer 1, a strona ma numer 157.

Na rys. 9 pokazano faktyczną zawartość wierszy

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 9/19

Rys. 7 Wstawiony wiersz do tabeli sysindexes

wstawiane są wiersze, przechowujące informację na temat poszczególnych

atrybutów, z których złożona jest tabela

Ksiazka, co możemy zaobserwować

Rys. 8 Wstawione wiersze do tabeli syscolumns

W celu poprawnego uruchomienia polecenia

DBCC PAGE należy pobrać odpowiednią wartość z

0x9D0000000100) i przekształcić ją na adres pliku

). W pierwszym kroku należy zamienić bajty, aby uzyskać ciąg

00 01 00 00 00 9D

00 01) reprezentują 2-bajtowy numer pliku, a ostatn

numer strony. Zatem plik ma numer 1, a strona ma numer 157.

pokazano faktyczną zawartość wierszy tabeli

Ksiazki z bazy danych

Rys. 9 Wiersze danych tabeli Ksiazki

Moduł 4

Wewnętrzna struktura

przechowujące informację na temat poszczególnych

co możemy zaobserwować na rys. 8.

należy pobrać odpowiednią wartość z

strony (kolumna

first

00 01 00 00 00 9D.

bajtowy numer pliku, a ostatnie cztery grupy (

00 00

azy danych

Biblioteka.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 10/19

Polecenie

DBCC PAGE wyświetla wiersze danych w czterech grupach po 4 bajty naraz. W obrębie

każdej z grup bajty wyświetlane są w odwrotnej kolejności.

Szacowanie wielkości pliku danych

W przykładzie tym posłużymy się wcześniej stworzona tabelą

Ksiazki oraz stworzymy nową

tabelę

Czytelnicy oraz tabelę techniczna Ksiazki_Czytelnicy. Dla tej tabeli określimy

wielkość pliku danych.

Określamy rozmiar pojedynczego wiersza w tabeli

Ksiazki, Czytelnicy oraz

Ksiazki_Czytelnicy.

Tabela

Ksiazki

Kolumna

Typ

Rozmiar

ID_Ksiazka

int

4 bajty

Tytul

varchar(200)

200 bajtów

Autor

varchar(50)

50 bajtów

Rok

char(4)

4 bajty

ID_Czytelnik

int

4 bajty

Suma

262 bajtów

Tabela

Czytelnicy

Kolumna

Typ

Rozmiar

ID_Czytelnik

int

4 bajty

Nazwisko

varchar(50)

50 bajtów

Imie

varchar(30)

30 bajtów

Data_urodzenia

smalldatatime

4 bajty

ID_Ksiazka

int

4 bajty

Suma

92 bajtów

Tabela

Ksiazki_Czytelnicy

Kolumna

Typ

Rozmiar

ID_Ksiazka

int

4 bajty

ID_Czytelnik

int

4 bajty

Suma

8 bajtów

Wyznaczamy liczbę wierszy mieszczących się na stronie. W naszym przypadku mamy:

Ksiazki = 8060 / 262 B = 31 wierszy na stronie

Czytelnicy = 8060 / 92 B = 88 wierszy na stronie

Ksiazki_Czytelnicy = 8060 / 8 B = 1008 wierszy na stronie

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 11/19

Przy założeniu, że w tabeli

Ksiazki będzie znajdowało się 5000 rekordów, w tabeli

Czytelnicy 2000 rekordów a w tabeli Ksiazki_Czytelnicy 22000 rekordów,
wyznaczamy liczbę stron niezbędnych do zapisania danych znajdujących się w tabeli.

Ksiazka = 5000 rekordów / 31 wiersze = 162 strony

Czytelnicy = 2000 rekordów / 88 wierszy = 23 strony

Ksiazki_Czytelnicy = 22000 rekordów / 1008 wierszy = 22 strony

Sumujemy liczbę wszystkich stron

162 strony + 23 strony + 22 strony = 207 stron

Obliczamy wielkość pliku danych.

8196 B * 207 stron = 1696572 B= 1,7 MB

Porady praktyczne

•

Widok systemowy

sysindex zawiera dwie ważne kolumny, które reprezentują numery

stron w obrębie bazy danych:

id i first. Kolumna id przechowuje numer identyfikacyjny

bazy danych. Kolumna

first przechowuje wartość, powyżej której znajdują się fizycznie

wpisane rekordy (numer strony danych) oraz numer pliku danych.

•

W sytuacji, kiedy tabela przechowuje dane typu OLE (

text, ntext lub image), rzeczywiste

dane mogą nie być przechowywane na stronach razem z resztą danych wiersza. Wynika to z
faktu, iż dane tego typu zajmują dużo przestrzeni dyskowej a zatem ich fragmenty mogą być
przechowywane w różnych plikach oraz na różnych stronach danych.

•

Z racji tego, iż obiekty typu OLE zajmują dużo przestrzeni dyskowej pamiętaj, że należy
podczas wyboru tego typu danych kilkakrotnie zastanowić się czy jest to niezbędny typ
danych i czy nie można zastąpić go innym lżejszym typem.

•

W celu poprawnego uruchomienia polecenia

DBCC PAGE, należy prawidłowo pobrać

odpowiednia wartość z widoku systemowego

sysindexes i przekształcić ją na adres pliku i

strony.

•

Pamiętaj, że z widoku

sysindexes pobieramy zawartość kolumny first w postaci

heksadecymalnej. W pierwszym kroku należy zawsze zamienić bajty. Uzyskana wartość
składa się z dwóch części: numeru pliku (pierwsze dwie grupy) oraz numeru strony (ostatnie
cztery grupy).

•

Widoki systemowe

sysindexes i syscolumns przechowują wiele różnorodnych informacji

na temat obiektów w ramach bazy danych (data utworzenia, data modyfikacji, itp.)
przydatnych w skutecznych i sprawnych pracach administracyjnych.

•

Pamiętaj,  w  wielu  sytuacjach  szacowanie  wielkości  pliku  danych  i  pliku  dziennika  transakcji
jest  rzeczą  bardzo  ważną  z  punktu  widzenia  administracji  serwerem  bazodanowym.  Ma  to
szczególne znaczenie w przypadku przenoszenia bazy  danych z innego systemu zarządzania
bazą danych, gdzie znana jest liczba rekordów w poszczególnych obiektach bazy danych.

•

W  przypadku,  obliczenia  wielkości  pliku  danych  wychodzi  wartość  niecałkowita  (patrz
przykładowe  obliczenie  wielkości  pliku  danych),  np.  5.48MB,  wówczas  w  celu  zapewnienia
odpowiedniej  przestrzeni  na  importowane  dane  należy  ustawić  wielkość  początkową  pliku
danych na 6.0MB. Microsoft SQL Server 2008 nie daje możliwości stworzenia bazy danych z
plikiem danych wielkości niecałkowitej.

•

Pamiętaj, w systemowej bazie danych

tempdb dostarczonej z serwerem Microsoft SQL

Server 2008, ustawione są parametry wyjściowe Twojej bazy danych. Jeżeli zmienisz
cokolwiek w tej bazie danych wszystkie bazy tworzone w ramach serwera będą pobierały jej
wartości początkowe.

•

Pamiętaj nigdy nie kasuj baz danych dostarczonych wraz z Microsoft SQL Server 2008.
Operacja ta może doprowadzić do niestabilnej pracy serwera a w niektórych sytuacjach
wręcz do jego poważnego uszkodzenia.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 12/19

•

Pamiętaj w opcji SQL Server 2008 przy tworzeniu bazy danych mamy możliwość ustawienia
wielkości  pliku  danych  oraz  wielkości  wzrostu  pliku  danych.  Wielkości  te  są  liczbami
całkowitymi  z  dokładnością  do  1  MB.  Dobrą  praktyką  jest  ustawienie  parametru  wzrostu
pliku  danych  jako  30%  wartości  pliku  danych  (jednak  nie  mniej  niż  1MB).  Przykładowo  dla
pliku danych wielkości 10MB powinniśmy ustawić wielkość wzrostu pliku danych na poziomie
3MB.

Uwagi dla studenta

Jesteś przygotowany do realizacji laboratorium jeśli:

•

rozumiesz, co oznacza strona danych, nagłówek strony, wiersze danych, tablica przesunięć
wierszy

•

rozumiesz składnię instrukcji

DBCC

•

umiesz przeliczać system heksadecymalny na system dziesiętny

•

znasz podstawowe typy danych w SQL Server 2008

•

umiesz obliczać wielkość pliku danych

Dodatkowe źródła informacji

Jeffrey D. Ullman, Jennifer Widom, Podstawowy wykład z systemów baz danych, WNT, 2000

W książce autor w przystępny i zrozumiały sposób przedstawia między innymi jak
zorganizowane jest przechowywanie danych oraz pokazuje, w jaki sposób można
odczytać strony danych.

Christopher J. Date, Wprowadzenie do systemów baz danych, WNT, 2000

W książce tej znajdziemy dużo szczegółowych informacji na temat teorii
przechowywania danych w bazach danych. Znajdzie cie w niej również szczegółową
informację na temat polecenia DBCC, wraz z wieloma wariantami jego użycia w celu
przeglądania danych na bardzo niskim poziomie. Pozycja szczególnie polecana dla
osób pragnących poszerzenie swojej wiedzy z tej tematyki.

Ramez Elmasri, Shamkant B. Navathe, Wprowadzenie do systemów baz danych, Wydawnictwo
Helion, 2005

Podobnie jak w poprzedniej pozycji, w książce tej znajdziemy bardzo szczegółowe
informacje na temat teorii przechowywania danych w bazach danych. Pozycja
szczególnie polecana dla osób pragnących poszerzyć swoją wiedzę z tej tematyki.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
ITA-101 Bazy danych

Laboratorium podstawowe

Problem 1 (czas realizacji 25 min)

Jesteś  administratorem  w  firmie  National
dostępową  do  twojej  bazy  danych  poprosili  Cię  jako  eksperta  o  zaprezentowanie  sposobu
przechowywania  danych  w  SQL  Server  2008.  Ma  to  im  pomóc  w  zrozumienia  sposobu
przechowywania danych i ulepszeniu niskopozi

Zadanie

Tok postępowania

Utwórz

testową bazę i
tabelę

•

Uruchom maszynę wirtualną

—
—

•

Kliknij
SQL Server Management Studio

•

W oknie logowania kliknij

Utwórz bazę

•

Wykonaj poniższy kod tworzący bazę danych

USE master
GO

--
--

IF EXISTS (SELECT *

GO

CREATE DATABASE Dyplomowe
ON
( NAME = 'Dyplomowe', FILENAME = N'C:
Server
3072KB , MAXSIZE =

LOG ON
( NAME = 'Dyplomowe_log', FILENAME = N'C:
SQL Server
1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

•

Stwórz dwie tabele

CREATE TABLE Osoba(

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 13/19

Laboratorium podstawowe

Problem 1 (czas realizacji 25 min)

Jesteś  administratorem  w  firmie  National  Insurance.  Programiści  modernizujący  aplikacje
dostępową  do  twojej  bazy  danych  poprosili  Cię  jako  eksperta  o  zaprezentowanie  sposobu
przechowywania  danych  w  SQL  Server  2008.  Ma  to  im  pomóc  w  zrozumienia  sposobu
przechowywania danych i ulepszeniu niskopoziomowego działania aplikacji.

Tok postępowania

Uruchom maszynę wirtualną BD2008.

—

Jako nazwę użytkownika podaj Administrator.

—

Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w
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
SQL Server Management Studio.
W oknie logowania kliknij Connect.

Wykonaj poniższy kod tworzący bazę danych Dyplomowe

-- (1) Ustawiamy sie na baze danych master

USE master
GO

-- (2) sprawdzmy, czy taka baza juz istnieje;
-- jesli tak, to usuwamy ja

IF EXISTS (SELECT *
     FROM   master..sysdatabases
     WHERE  name = 'Dyplomowe')
  DROP DATABASE Dyplomowe
GO

-- (3) Tworzymy baze danych Dyplomowe

CREATE DATABASE Dyplomowe
ON
( NAME = 'Dyplomowe', FILENAME = N'C:\Program Files
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Dyplomowe.mdf' , SIZE =
3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = 'Dyplomowe_log', FILENAME = N'C:\Program Files
SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Dyplomowe.ldf' , SIZE =
1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

Stwórz dwie tabele: Osoba i Praca. W tym celu wykonaj poniższy kod

-- (3) Tworzenie przykladowych dwoch tabel: Osoba i Praca

CREATE TABLE Osoba(
ID_Osoby smallint

NOT NULL,

Imie varchar(20)

NOT NULL,

Nazwisko varchar(50)

NOT NULL,

Nr_Indeksu

varchar(6)

NOT NULL

Moduł 4

Wewnętrzna struktura

Insurance. Programiści modernizujący aplikacje

dostępową do twojej bazy danych poprosili Cię jako eksperta o zaprezentowanie sposobu
przechowywania danych w SQL Server 2008. Ma to im pomóc w zrozumienia sposobu

omowego działania aplikacji.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual
PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku

Microsoft SQL Server 2008 uruchom

Dyplomowe:

(2) sprawdzmy, czy taka baza juz istnieje;

Program Files\Microsoft SQL
Dyplomowe.mdf' , SIZE =

UNLIMITED, FILEGROWTH = 1024KB )

Program Files\Microsoft
Dyplomowe.ldf' , SIZE =

1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

ykonaj poniższy kod:

(3) Tworzenie przykladowych dwoch tabel: Osoba i Praca

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
ITA-101 Bazy danych

)

CREATE TABLE Praca(

)

•

Uzupełnij tabele

Sprawdź

zawartość
widoków
systemowych

•

Z menu głównego wybierz

•

Odszukaj plik

•

Na utworzonej bazie danych

USE Dyplomowe
GO

SELECT name, object_id, type,

GO

•

W wyniku powinieneś otrzymać informację na temat wpisu w widoku
systemowym

•

Wykonaj zapytanie:

SELECT object_id, name, column_id,

GO

•

W wyniku
systemowym

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 14/19

  CONSTRAINT [PK_Osoba] PRIMARY KEY CLUSTERED
  (
    [ID_Osoby] ASC
  )

CREATE TABLE Praca(
ID_Pracy smallint

NOT NULL,

  Temay   varchar(200)  NOT NULL,
  Data   smalldatetime  NOT NULL,
  Ocena   smallint

NOT NULL

  CONSTRAINT [PK_Praca] PRIMARY KEY CLUSTERED
  (
    [ID_Pracy] ASC
  )

Uzupełnij tabele Osoba i Praca przykładowymi danymi.

Z menu głównego wybierz File -> Open -> File.
Odszukaj plik C:\widoki_systemowe.sql i kliknij Open
Na utworzonej bazie danych Dyplomowe wykonaj poniższe zapytanie

-- (1) Ustaw się n abaze danych Dyplomowe

USE Dyplomowe
GO

-- (2) Informacja o wpisie w widoku sysobjects

ELECT name, object_id, type,

    type_desc, create_date, modify_date
  FROM [Dyplomowe].[sys].[objects]
  WHERE name='Osoba' OR name='Praca'
GO

W wyniku powinieneś otrzymać informację na temat wpisu w widoku
systemowym sysobjects.

Rys. 10 Wpis w widoku systemowym sysobjects

W wyniku wykonania zapytania na tabeli
otrzymasz między innymi informację o numerze identyfikacyjnym
obiektu tabeli object_id. Numer ten będzie Ci pomocny w wyszukaniu
odpowiedniego wpisu w widoku systemowym syscolumns

Wykonaj zapytanie:

-- (3) informacja na temat wpisu w widoku systemowym sy

SELECT object_id, name, column_id,
    system_type_id, max_length, collation_name
  FROM [Dyplomowe].[sys].[columns]
  where object_id='85575343' OR object_id='117575457'
GO

W wyniku powinieneś otrzymać informację na temat wpisu w widoku
systemowym syscolumns.

Moduł 4

Wewnętrzna struktura

CONSTRAINT [PK_Osoba] PRIMARY KEY CLUSTERED

CONSTRAINT [PK_Praca] PRIMARY KEY CLUSTERED

przykładowymi danymi.

Open.

wykonaj poniższe zapytanie:

Informacja o wpisie w widoku sysobjects

W wyniku powinieneś otrzymać informację na temat wpisu w widoku

Wpis w widoku systemowym sysobjects

W wyniku wykonania zapytania na tabeli systemowej sysobject
otrzymasz między innymi informację o numerze identyfikacyjnym

. Numer ten będzie Ci pomocny w wyszukaniu

syscolumns.

(3) informacja na temat wpisu w widoku systemowym syscolumns

system_type_id, max_length, collation_name

where object_id='85575343' OR object_id='117575457'

powinieneś otrzymać informację na temat wpisu w widoku

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
ITA-101 Bazy danych

•

Wykonaj zapytanie:

SELECT id, first, root, minlen, name, rows
FROM [Dyplomowe].[sys].[sysindexes]
WHERE
GO

•

W wyniku powinieneś otrzymać informację na temat wpisu w widoku
systemowym

Zbadaj strony

danych

•

Dla tabeli
(0x9B0000000100

•

Zamień bajty, aby uzyskać ciąg

•

Pierwsze dwie grupy (
ostatnie cztery grupy (
1

•

Z menu głównego wybierz

•

Odszukaj plik

•

Wywołaj instrukcję

DBCC TRACEON(3604)
GO
DBCC PAGE (Dyplomowe,1,155,1)

•

W wyniku powinieneś otrzymać

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 15/19

Rys. 11 Wpis w widoku systemowym syscolumns

Wykonaj zapytanie:

-- (4) Informacja na temat wpisu w widoku systemowym sysindexes

SELECT id, first, root, minlen, name, rows
FROM [Dyplomowe].[sys].[sysindexes]
WHERE name='PK_Osoba' OR name='PK_Praca'
GO

W wyniku powinieneś otrzymać informację na temat wpisu w widoku
systemowym sysindexes.

Rys. 12 Wpis w widoku systemowym sysindexes

Informacja z kolumny first w widoku systemowym
na temat numeru pliku oraz numeru strony, na której przechowywane
są dane z tabeli.

Dla tabeli Osoba odczytaj wartość kolumny first

0x9B0000000100) i przekształć ją na adres pliku strony

Zamień bajty, aby uzyskać ciąg 00 01 00 00 00 9B.
Pierwsze dwie grupy (00 01) reprezentują 2-bajtowy numer pliku, a
ostatnie cztery grupy (00 00 00 9B) numer strony. Zatem plik ma numer
1, a strona ma numer 155.
Z menu głównego wybierz File -> Open -> File.
Odszukaj plik C:\strona_danych.sql i kliknij Open.
Wywołaj instrukcję BDCC PAGE z ustalonymi wcześniej parametrami

DBCC TRACEON(3604)
GO
DBCC PAGE (Dyplomowe,1,155,1)

Instrukcja DBCC TRACEON(3604) nakazuje żeby SQL Server wyświetlił
strony danych na ekranie monitora w sposób przyjazny dla
użytkownika.

W wyniku powinieneś otrzymać informacje jak na

Moduł 4

Wewnętrzna struktura

Wpis w widoku systemowym syscolumns

(4) Informacja na temat wpisu w widoku systemowym sysindexes

W wyniku powinieneś otrzymać informację na temat wpisu w widoku

Wpis w widoku systemowym sysindexes

widoku systemowym zawiera informacje

na temat numeru pliku oraz numeru strony, na której przechowywane

first z widoku sysindexes

ją na adres pliku strony.

bajtowy numer pliku, a

) numer strony. Zatem plik ma numer

z ustalonymi wcześniej parametrami:

nakazuje żeby SQL Server wyświetlił

danych na ekranie monitora w sposób przyjazny dla

informacje jak na rys. 13.

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski
ITA-101 Bazy danych

Problem 2 (czas realizacji 20 min)

Jesteś administratorem w firmie National Insurance.
zaimportowane nowe dane. W tym celu musisz przeprojektować swoja bazę danych i obliczyć
wielkość początkową pliku danych i pliku dziennika transakcji.

Zadanie

Tok postępowania

Utwórz

testową bazę

•

Uruchom maszynę wirtualną

—
—

•

Kliknij
SQL Server Managemen

•

W oknie logowania kliknij

•

Rozwiń węzeł

•

W obrębie bazy danych
Diagram

•

Powinieneś zobaczyć diagram taki jak na Rys. 14.

Oszacuj

wielkość pliku
danych

•

Recenzja = ID_Osoby(2B) + ID_Pracy(2B) + ID_Ocena(2B) = 6B
Osoba = ID_Osoby(2B) + Imie(20B) + Nazwisko(50B) +
Nr_Indeksu(int)(4B) + ID_Stopien(2B) = 78B
StopienNaukowy = ID_Stopien(2B) + Stopien(25B) =
Autorzy = ID_Osoby(2B) + ID_Pracy(2B) = 4B
Slownik = ID_SlowoKlucz(2B) + ID_Pracy(2B) = 4B
SlowoKlucz = ID_SlowoKlucz(2B) + SlowoKlucz(25B) = 27B

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Strona 17/19

Problem 2 (czas realizacji 20 min)

Jesteś administratorem w firmie National Insurance. Wiesz, że do twojej bazy danych maja zostać
zaimportowane nowe dane. W tym celu musisz przeprojektować swoja bazę danych i obliczyć
wielkość początkową pliku danych i pliku dziennika transakcji.

Tok postępowania

Uruchom maszynę wirtualną BD2008.

—

Jako nazwę użytkownika podaj Administrator.

—

Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w
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
SQL Server Management Studio.
W oknie logowania kliknij Connect.
Rozwiń węzeł Database i wybierz PraceDyplomowe
W obrębie bazy danych PraceDyplomowe rozwiń węzeł
Diagram.
Powinieneś zobaczyć diagram taki jak na Rys. 14.

Rys. 14 Diagram bazy danych PraceDyplomowe

W  celu  wykonania  tego  zadania  należy  wykonać  moduł
związany  z  projektowaniem  i  implementacją  bazy  danych.  Dla
zaprojektowanej  bazy  danych  będziemy  szacowali  wielkość  pliku
danych.

Na podstawie tabeli określ rozmiar pojedynczego wiersza

Moduł 4

Wewnętrzna struktura

iesz, że do twojej bazy danych maja zostać

zaimportowane nowe dane. W tym celu musisz przeprojektować swoja bazę danych i obliczyć

Jeśli nie masz zdefiniowanej maszyny wirtualnej w Mirosoft Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku

Microsoft SQL Server 2008 uruchom

PraceDyplomowe

rozwiń węzeł Database

anych PraceDyplomowe

W celu wykonania tego zadania należy wykonać moduł 2 i moduł 3
związany z projektowaniem i implementacją bazy danych. Dla

anych będziemy szacowali wielkość pliku

podstawie tabeli określ rozmiar pojedynczego wiersza:

Recenzja = ID_Osoby(2B) + ID_Pracy(2B) + ID_Ocena(2B) = 6B
Osoba = ID_Osoby(2B) + Imie(20B) + Nazwisko(50B) +

StopienNaukowy = ID_Stopien(2B) + Stopien(25B) = 27B

Slownik = ID_SlowoKlucz(2B) + ID_Pracy(2B) = 4B
SlowoKlucz = ID_SlowoKlucz(2B) + SlowoKlucz(25B) = 27B

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 19/19

Laboratorium rozszerzone

Zadanie 1 (czas realizacji 90 min)

Jesteś  głównym  administratorem  sytemu  bazodanowego  w  firmie  BARKA,  która  zajmuje  się
inwestycjami  na  giełdzie  papierów  wartościowych.  Kierownictwo  firmy  w  wyniku  przejęcia
mniejszej firmy TFI START zarządzającej funduszami inwestycyjnymi podjęło decyzje o modernizacji
aplikacji  dostępowej  do  bazy  danych  oraz  rozbudowie  istniejącej  bazy  danych.  W  związku  z  tym
pomiędzy  Tobą  a  programistami  aplikacji  dostępowej  powinna  istnieć  ścisła  współpraca.  W  celu
ulepszeniu niskopoziomowego działania aplikacji poproszono Cię, jako eksperta, o zaprezentowanie
sposobu przechowywania danych w SQL Server 2008. Z drugiej strony wiesz, że po rozbudowie do
twojej  bazy  danych  maja  zostać  zaimportowane  nowe  dane  za  okres  ostatnich  5  lat.  W  tym  celu
musisz  przeprojektować  swoja  bazę  danych  i  obliczyć  wielkość  początkową  pliku  danych  i  pliku
dziennika transakcji.

Zadania, jakie przed tobą zostały postawione, są następujące:

Pokaż programistom, w jaki sposób zorganizowane jest przechowywanie danych w
SQL Server 2008.

Przeprojektuj istniejącą bazę danych.

Oblicz, jaka powinna być wielkość pliku danych dla przeprojektowanej bazy danych.

ITA-101 Bazy Danych

Włodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 5

Wersja 1.0

Język SQL – DCL, DDL

Spis treści

Język SQL – DCL, DDL ............................................................................................................................ 1