plik

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 2/18

Informacje o module

Opis modułu

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.

Cel modułu

Celem modułu jest wprowadzenie słuchacza w 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ęd wierszy)



potrafił badad zawartośd stron danych używamy instrukcji DBCC
PAGE, która umożliwia oglądanie nagłówka strony, wierszy danych i
tablicy przesunięd wierszy



wiedział jak wygląda struktura wierszy danych



potrafił wyświetlid i przeanalizowad wiersze danych zapisane w
tabelach

Wymagania wstępne

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



potrafid budowad diagram ERD (patrz moduł „Budowa diagramu
ERD”)



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



wiedzied, 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 zapoznad się z materiałem zawartym
w module 1 i module 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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 3/18

Przygotowanie teoretyczne

Przykładowy problem

Jak  zapewne  wiesz,  z  SQL  Server  2008  można  korzystad  w  bardzo  efektywny  sposób  nie  mając
wiedzy na temat sposobu wewnętrznej organizacji danych, jednak w wielu przypadkach dobrze jest
wiedzied, jak takie przechowywane dane są zorganizowane dane są. Ułatwia to zrozumienie wielu
mechanizmów  funkcjonujących  w  SQL  Server  2008.  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.  Znając  w  jaki  sposób  zorganizowane  są
dane możemy lepiej dobierad typy danych na atrybutach mając świadomośd tego, gdzie fizycznie są
one  umieszczone.  Możemy  uniknąd  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 byd
początkowa wielkośd pliku danych i dziennika transakcji w momencie, kiedy wiemy, ile danych ma
zostad wpisanych do naszej bazy. Na podstawie tej informacji w kolejności możemy ustawid, o ile
pliki danych i dziennika transakcji mają wzrosnąd.

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

Podstawy teoretyczne

Chociaż z SQL Server można korzystad 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 sysobjects,
sysindexes i 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

otrzymuje dla każdej

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

Na Rys. 2 pokazano wiersze, które dodawane są do widoku

sysobjects

columns

podczas

tworzenia tabeli

Ksiazka

Rys. 2. Informacje katalogowe zapisane po utworzeniu tabeli

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 4/18

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

text

image

), których

stały rozmiar wynosi

8 KB

(

8192

bajtów). Składają się one z trzech elementów (Rysunek 3):

nagłówka strony, wierszy danych, tablicy przesunięd 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 Tabeli 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 łaocucha stron.

m_prev_Page

Numer pliku i nazwa poprzedniej strony. Parametr wyświetlany jeżeli
bieżąca strona należy do łaocucha 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śd zawsze równa 1.

IndexId

Identyfikator indeksu strony. Dla stron danych zawsze wartośd 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/18

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 obejmowad kilku stron.
Wyjątek stanowią kolumny, dla których przypisano typ

image

lub

text

. Należy zauważyd, iż liczba

wierszy może się różnid 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ęd 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ętad 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ęd wierszy dla każdej strony bazy danych.
Polecenia tego może używad tylko administrator systemu. Składnię polecenia

DBCC PAGE

pokazano

poniżej:

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

W Tab. 1 zostały opisane podstawowe parametry instrukcji

DBCC PAGE

Tab. 1 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ęd
wierszy.



2 : Zwraca informacje: o nagłówku bufora, nagłówku
strony, zawartośd całej strony oraz tablicę przesunięd
wierszy.



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

Przykładowy wynik działania instrukcji

DBCC PAGE

pokazano na Rys. 44.

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 6/18

Rys. 4 Przykładowy wydruk instrukcji DBCC PAGE

Wydruk instrukcji DBCC PAGE składa się z czterech sekcji:

1.  BUFFER – przedstawia informacje o buforze danej strony.
2.  PAGE HEADER – pokazuje dane dla pól nagłówka strony.
3.  DATA – zawiera informacje o każdym wierszu tabeli. Dane strony dzielone są na trzy części.

Częśd pierwsza (lewa kolumna) określa położenie bajtowe w obrębie wiersza, częśd druga
(środkowe cztery kolumny) zawiera właściwe dane zapisane na stronie, częśd trzecia (prawa
kolumna) zawiera znakową reprezentacje danych.

4. OFFSET TABLE – pokazuje zawartośd tablicy przesunięd wierszy, która znajduje się na koocu

strony. Pierwszy wiersz fizycznie znajdujący się na stronie jest wierszem numer 6, z
przesunięciem w tablicy przesunięd równym 96.

Struktura wiersza danych
Na Rysunku 4 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/18

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

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ęd kolumn. Wiersz danych, w którym znajdują się kolumny o zmiennej długości,
zawiera tablicę przesunięd 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 uzyskad 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/18

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 oszacowad rozmiar pliku
danych.

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

1. 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śd średnią.

2. Następnie maksymalną liczbę bajtów na pojedynczej stronie danych (8060) dzielimy przez

szacunkową wielkośd wiersza. W wyniku otrzymujemy liczbę wierszy mieszczących się na
stronie.

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

4. W kolejnym kroku otrzymaną liczbę stron mnożymy przez 8196 B (rozmiar pojedynczej

strony), co daje nam w wyniku wielkośd pliku danych.

Przykładowe rozwiązanie

Wyświetlanie wierszy danych
Zanim będziesz mógł wyświetlid wiersze danych w twojej tabeli w bazie danych będziesz musiał
odpowiednio zgromadzid 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 co możemy zaobserwowad na Rys. 7.

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 9/18

Rys. 7 Wstawiony wiersz do tabeli sysindexes



syscolumns

wstawiane są wiersze, co możemy zaobserwowad na Rys. .

Rys. 8 Wstawione wiersze do tabeli syscolumns

W celu poprawnego uruchomienia polecenia

DBCC PAGE

należy pobrad odpowiednią wartośd z

widoku

sysindexes

(

0x9D0000000100

) i przekształcid ją na adres pliku strony (Rys. 7 kolumna

first

). W pierwszym kroku należy zamienid bajty, aby uzyskad ciąg

00 01 00 00 00 9D

. Pierwsze

dwie grupy (

00 01

) reprezentują 2-bajtowy numer pliku, a ostatnie cztery grupy (

00 00 00 9D

) –

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

Na Rys. 9 pokazano faktyczną zawartośd wierszy tabeli

Ksiazki

z bazy danych

Biblioteka

Rys. 9 Wiersze danych tabeli Ksiazki

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 10/18

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

. Dla tej tabeli określimy

wielkośd pliku danych.

1. Określamy rozmiar pojedynczego wiersza w tabeli

Ksiazki

ID_Ksiazka

(int)

4 bajty

Tytul

(varchar(50))

50 bajtów

Autor

(varchar(30))

30 bajtów

Rok

(char(4))

4 bajty

Suma

88 bajty

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

Ksiazka = 8060 / 88 B = 91 wierszy na stronie

3. Przy założeniu że w tabeli

Ksiazki

będzie znajdowało się 5000 rekordów wyznaczamy

liczbę stron niezbędnych do zapisania danych znajdujących się w tabeli.

Ksiazka = 5000 rekordów / 91 wiersze = 55 stron

4. Obliczamy wielkośd pliku danych.

8196 B * 55 stron = 450780 B = 0,45 MB

Porady praktyczne



Widok

sysindex

zawiera dwie ważne kolumny, które reprezentują numery stron w obrębie

bazy danych:

first

. Kolumna

przechowuje numer identyfikacyjny bazy danych.

Kolumna

first

przechowuje wartośd, powyżej której znajdują się fizycznie wpisane rekordy

(numer strony) oraz numer pliku.



W sytuacji, kiedy tabela przechowuje dane LOB (text, ntext lub image) rzeczywiste dane
mogą nie byd przechowywane na stronach razem z resztą danych wiersza.



W celu poprawnego uruchomienia polecenia

DBCC PAGE

należy prawidłowo pobrad

odpowiednia wartośd z widoku

sysindexes

i przekształcid ją na adres pliku i strony.



Pamiętaj, że z widoku

sysindexes

pobieramy zawartośd kolumny

first

w postaci

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



Widoki systemowe

sysindexes

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łodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 11/18



W  przypadku,  obliczenia  wielkości  pliku  danych  wychodzi  wartośd  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  ustawid  wielkośd  początkową  pliku
danych  na  6.0MB.  SQL  Server  2008  nie  daje  możliwości  stworzenia  bazy  danych  z  plikiem
danych wielkości niecałkowitej.



Pamiętaj w opcji SQL Server 2008 przy tworzeniu bazy danych mamy możliwośd 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 ustawid wielkośd 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ęd
wierszy



rozumiesz składnię instrukcji

DBCC



umiesz przeliczad system heksadecymalny na system dziesiętny



znasz podstawowe typy danych w SQL Server 2008



umiesz obliczad wielkośd pliku danych

Pamiętaj o zapoznaniu się z uwagami i poradami zawartymi w tym module. Upewnij się, że
rozumiesz omawiane w nich zagadnienia. Jeśli masz trudności ze zrozumieniem tematu zawartego
w uwagach, przeczytaj ponownie informacje z tego rozdziału i zajrzyj do notatek z wykładów.

Dodatkowe źródła informacji

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

W książce autor w prystepny i zrozumiały sposób przedstawia między innymi w jaki
sposób zorganizowane jest przechowywanie danych oraz pokazuje w jaki sposób
można odczytad strony danych.

2. Date Christopher.J., 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. Pozycja szczególnie polecana dla osób
pragnących poszerzenie swojej wiedzy z tej tematyki.

3. 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 poszerzenie swojej wiedzy z tej tematyki.

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 12/18

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.

Zadanie

Tok postępowania

1. Tworzenie
testowej bazy i
tabeli



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.



Z menu głównego wybierz File -> Open -> File.



Odszukaj plik C:\baza.sql i kliknij Open.



Wciśnij F5, aby uruchomid kod. Kod ten tworzy przykładową bazę
danych wraz z dwoma tabelami, Osoba i Praca, uzupełnionymi
przykładowymi danymi.

2. Tworzenie
bazy



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\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Dyplomowe.mdf' , SIZE =
3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

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

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 13/18



Stwórz dwie tabele Osoba i Praca. 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

  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.

3. Sprawdzanie
zawartości
widoków
systemowych



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ś otrzymad informację na temat wpisu w widoku
systemowym

sysobjects

Rys. 10. Wpis w widoku systemowym sysobjects

W wyniku wykonania zapytania na tabeli systemowej

sysobject

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 syscolumns

SELECT object_id, name, column_id,
system_type_id, max_length, collation_name
FROM [Dyplomowe].[sys].[columns]

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 14/18

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



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

syscolumns

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ś otrzymad informację na temat wpisu w widoku
systemowym

sysindexes

Rys.

12 Wpis w widoku systemowym sysindexes

Informacja z kolumny w tabeli systemowej zawiera informacje na
temat numeru pliku oraz numeru strony, na której przechowywane są
dane z tabeli

4. Badanie
strony danych



Dla tabeli Osoba odczytujemy wartośd kolumny first z tabeli

sysindexes

(

0x9B0000000100

) i przekształcamy ją na adres pliku strony



Zamieo bajty, aby uzyskad 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łodzimierz Dąbrowski, Przemysław Kowalczuk, Konrad Markowski

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 16/18

Problem 2 (czas realizacji 20 min)

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

Zadanie

Tok postępowania

1. Tworzenie
testowej bazy



Uruchom maszynę wirtualną BD2008.

— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.

Jeśli nie masz zdefiniowanej maszyny wirtualnej w MS Virtual PC,
dodaj nową maszynę używając wirtualnego dysku twardego z pliku
D:\VirtualPC\Dydaktyka\BD2008.vhd.



Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom
SQL Server Management Studio.



W oknie logowania kliknij Connect.



Rozwio węzeł Database i wybierz PraceDyplomowe



W obrębie bazy danych PraceDyplomowe rozwio węzeł Database
Diagram



Powinieneś zobaczyd diagram taki jak na Rys. 14.

Rys. 14. Diagram bazy danych PraceDyplomowe

W  celu  wykonania  tego  zadania  należy  wykonad  moduł  3  i  moduł  4
związany  z  projektowaniem  i  implementacją  bazy  danych.  Dla
zaprojektowanej  Bazy  Danych  będziemy  szacowali  wielkośd  pliku
danych.

2. Szacowanie
wielkości pliku
danych



Na podstawie tabeli określamy rozmiar pojedynczego wiersza.

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) = 27B
Autorzy = ID_Osoby(2B) + ID_Pracy(2B) = 4B
Slownik = ID_SlowoKlucz(2B) + ID_Pracy(2B) = 4B

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 17/18

SlowoKlucz = ID_SlowoKlucz(2B) + SlowoKlucz(25B) = 27B
TypStudiow = ID_TypStudiow(2B) + NazwaTypu(15B) = 17B
Ocena = ID_Ocena(2B) + Ocena(4B) = 6B
PracaDyplomowa = ID_Pracy(2B) + Temat (char500)(500B) + Data(8B) +
ID_Ocena(2B) + ID_TypStudiow(2B) + ID_Promotor(2B) = 516B



Wyznaczamy liczbę stron niezbędnych do zapisania danych znajdujących
się w tabelach.

Recenzja = 8540 rekordów / 1343 wierszy = 7 stron
Osoba = 5000 rekordów / 103 wierszy = 49 stron
StopienNaukowy = 4 rekordy / 298 wierszy = 1 strona
Autorzy = 4270 rekordów / 2015 wierszy = 3 strony
Slownik = 12810 rekordów / 2015 wierszy = 7 stron
SlowoKlucz = 500 rekordów / 298 wierszy = 2 strony
TypStudiow = 3 rekordy / 474 wierszy = 1 strona
Ocena = 4 rekordy / 1343 wierszy = 1 strona
PracaDyplomowa = 5000 rekordów / 15 wierszy = 334



Sumujemy liczbę stron

Liczba stron = 405 stron



Otrzymaną liczbę stron mnożymy przez 8196B (rozmiar pojedynczej
strony), co daje nam w wyniku wielkośd pliku danych.

Wielkość pliku danych = Liczba stron * 8196B = 405 * 8196B =
= 3 319 380 B = 3,32 MB

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

Moduł 4

ITA-101 Bazy danych

Wewnętrzna struktura

Strona 18/18

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  istnied  ś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 zostad zaimportowane  nowe  dane  za  okres  ostatnich 5  lat.  W  tym  celu  musisz
przeprojektowad swoja bazę danych i obliczyd wielkośd początkową pliku danych i pliku dziennika
transakcji.

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

1. Pokaż programistom w jaki sposób zorganizowane jest przechowywanie danych w

SQL Server 2008

2. Przeprojektuj istniejącą bazę danych.
3. Oblicz jaka powinna byd wielkośd pliku danych dla przeprojektowanej bazy danych