ITA 101 Modul 04

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 15/18



W wyniku powinieneś otrzymad

Rys. 13 Wyświetlona strona danych

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

Wyszukiwarka

Podobne podstrony:
ITA 101 Modul 03
ITA 101 Modul 02
ITA 101 Modul 06
ITA 101 Modul 13
ITA 101 Modul 11
ITA 101 Modul 09
ITA 101 Modul Dodatek A
ITA 101 Modul 08
ITA 101 Modul 07
ITA 101 Modul 10
ITA 101 Modul 12
ITA 101 Modul 01
ITA 101 Modul 05
ITA 101 Modul 02 v2

więcej podobnych podstron