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
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
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 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 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 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 rozwiązać dzięki znajomości budowy wewnętrznej struktury w SQL Server 2008.
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 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ść. 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 sysobjects i columns podczas tworzenia tabeli Ksiazka.
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.
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.
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ęść 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.
4. 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.
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.
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:
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ść średnią.
2. 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.
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ść 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.
Rys. 7 Wstawiony wiersz do tabeli sysindexes
• syscolumns 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ć na rys. 8.
Rys. 8 Wstawione wiersze do tabeli syscolumns
W celu poprawnego uruchomienia polecenia DBCC PAGE należy pobrać odpowiednią wartość z widoku sysindexes (0x9D0000000100) i przekształcić ją na adres pliku strony (kolumna first na rys. 7). W pierwszym kroku należy zamienić bajty, aby uzyskać 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ść wierszy tabeli Ksiazki z bazy danych Biblioteka.
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.
1. 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
2. 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
3. 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 4. Sumujemy liczbę wszystkich stron
162 strony + 23 strony + 22 strony = 207 stron 5. 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 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
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. 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.
2. 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.
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 poszerzyć swoją wiedzę z tej tematyki.
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. Utwórz testową bazę i tabelę
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Microsoft Virtual PC, dodaj nową maszynę używając wirtualnego dysku twardego z pliku D:\VirtualPC\Dydaktyka\BD2008.vhd.
• Kliknij Start. Z grupy programów Microsoft SQL Server 2008 uruchom SQL Server Management Studio.
• W oknie logowania kliknij Connect.
2. Utwórz bazę • 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
• 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,
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. Sprawdź zawartość 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 SELECT 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 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]
where object_id='85575343' OR object_id='117575457' GO
• W wyniku powinieneś otrzymać 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ś otrzymać informację na temat wpisu w widoku systemowym sysindexes.
Rys. 12 Wpis w widoku systemowym sysindexes
Informacja z kolumny first w widoku systemowym zawiera informacje na temat numeru pliku oraz numeru strony, na której przechowywane są dane z tabeli.
4. Zbadaj strony danych
• Dla tabeli Osoba odczytaj wartość kolumny first z widoku sysindexes (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 rys. 13.
Rys. 13 Wyświetlona strona danych
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.
Zadanie Tok postępowania
1. Utwórz testową bazę
• Uruchom maszynę wirtualną BD2008.
— Jako nazwę użytkownika podaj Administrator.
— Jako hasło podaj P@ssw0rd.
Jeśli nie masz zdefiniowanej maszyny wirtualnej w Mirosoft 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.
• Rozwiń węzeł Database i wybierz PraceDyplomowe
• W obrębie bazy danych PraceDyplomowe rozwiń węzeł Database Diagram.
• Powinieneś zobaczyć diagram taki jak na Rys. 14.
Rys. 14 Diagram bazy danych PraceDyplomowe
W celu wykonania tego zadania należy wykonać moduł 2 i moduł 3 związany z projektowaniem i implementacją bazy danych. Dla zaprojektowanej bazy danych będziemy szacowali wielkość pliku danych.
2. Oszacuj wielkość pliku danych
• Na 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) +
Nr_Indeksu(int)(4B) + ID_Stopien(2B) = 78B
StopienNaukowy = ID_Stopien(2B) + Stopien(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
• Wyznacz 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
• Zsumuj liczbę stron:
Liczba stron = 405 stron
• Otrzymaną liczbę stron pomnóż przez 8196B (rozmiar pojedynczej strony), co powinno dać w wyniku wielkość pliku danych:
Wielkość pliku danych = Liczba stron * 8196B = 405 * 8196B =
= 3 319 380 B = 3,32 MB
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:
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 być wielkość pliku danych dla przeprojektowanej bazy danych.
