Ćwiczenia > Budowa bazy danych w MS SQL Server 2000 > Tworzenie bazy danych

3.3 Tworzenie bazy danych



Zaprojektowana przez nas w poprzedniej lekcji baza danych składa się z następujących relacji:

Ksiazki(ID_ksiazki, tytul, rok_wydania, ID_wydawnictwa)
Autorzy(ID_autora, imie, nazwisko)
Autorstwo(ID_ksiazki, ID_autora)
Wydawnictwa(ID_wydawnictwa, wydawnictwo)


Jeśli nie ustaliłeś typów danych dla kolumn w tabelach, wróć do lekcji poprzedniej.

Krok 1 - Oszacuj rozmiar pliku danych

Znając strukturę tabel oraz typy danych kolumn, możesz oszacować rozmiar pliku (lub plików) danych. Zacznimy od tabeli Ksiazki.

1. Na podstawie projektu tabeli spróbuj określić rozmiar pojedynczego wiersza. Dla typów o stałym rozmiarze dodajesz liczbę bajtów poszczególnych kolumn, zaś dla typów o zmiennym rozmiarze przyjmujesz wielkość średnią.

Dla tabeli Ksiazki wygląda to następująco:

4B (ID_ksiazki) + 50B (tytul) + 4B (rok_wydania) + 4B (ID_wydawnictwa) = 62B

Dla pozostałych tabel rachunek daje wyniki: tabela Autorzy - 39B, tabela Autorstwo - 8B, tabela Wydawnictwa - 54B.

2. Teraz liczbę 8060 (maksymalna ilość bajtów na pojedynczej stronie danych) dzielimy przez szacunkową wielkość pojedynczego wiersza w każdej tabeli (zaokrąglając wynik w dół). Oto wyniki takiego działania:

- tabela Ksiazki - 8060 / 62 = 130 (wierszy na stronie)
- tabela Autorzy - 8060 / 39 = 206 (wierszy na stronie)
- tabela Autorstwo - 8060 / 8 = 1007 (wierszy na stronie)
- tabela Wydawnictwa - 8060 / 54 = 149 (wierszy na stronie)

3. Dla każdej tabeli należy teraz szacunkową liczbę wierszy dzielimy przez otrzymaną ilość wierszy na stronie (zaokrąglając w górę). W ten sposób otrzymamy liczbę stron niezbędnych do zapisania danych znajdujących się w tabelach. Np. zakładając, że tabela Ksiazki będzie zawierać 1000 rekordów obliczymy, że dane z tej tabeli zostaną zapisane na 8 stronach (bo 1000 / 130 = 8).

Załóżmy dla uproszczenia, że tabele Autorzy, Autorstwo oraz Wydawnictwa będą zawierały odpowiednio 700, 1500 oraz 600 wierszy. Działając analogicznie jak dla tabeli Ksiazki otrzymamy wyniki: dla tabeli Autorzy - 4 strony, dla tabeli Autorstwo - 2 strony, dla tabeli Wydawnictwa - 5 stron.

Podsumowując - dane z tabel zajmą 8 + 4 + 2 + 5 = 19 stron.

4. Otrzymaną w poprzednim punkcie ilość stron mnożymy teraz przez 8196B (rozmiar pojedynczej strony), co daje wynik 19 * 8196 = 155724 bajtów (czyli 155,7 kilobajtów).

5. Całą procedurę należy jeszcze powtórzyć dla indeksów niegrupowanych (one także zajmują obszar na dysku). Na razie jednak nie będziemy tego robić.

Jak widzisz, można w miarę dokładnie określić, ile przestrzeni dyskowej będziesz potrzebował do przechowania bazy danych. Domyślny rozmiar pliku danych w MS SQL Server 2000 to 1MB, zatem przestrzeń wystarczająca do zapisania zaprojektowanej przez Ciebie bazy danych Biblioteka.

O ile w przypadku małej i nieskomplikowanej bazy danych oszacowanie rozmiaru plików danych nie jest istotne, o tyle w przypadku bardzo dużych baz przechowujących miliony wierszy danych jest to sprawa kluczowa. Oczywiście zawsze lepiej jest przyjąć rozmiar za duży niż za mały (pamiętaj, że SQL Server co prawda może automatycznie powiększać pliki danych, ale jest to operacja obciążająca serwer).

Krok 2 - Oszacuj rozmiar pliku dziennika transakcji

O ile rozmiar plików danych można oszacować dość dokładnie, o tyle z plikami dziennika transakcji sprawa nie jest tak prosta. Rozmiar tych plików zależy od: wybranej opcji odtwarzania danych, liczby przeprowadzonych w bazie danych transakcji, rodzaju przeprowadzanych transakcji (jeśli więcej będzie operacji odczytu, to rozmiar dziennika danych będzie mniejszy).

Początkową wielkość dziennika transakcji należy przyjąć jako 25-35% wielkości plików danych. Następnie na podstawie obserwacji działania bazy danych należy dostosować rozmiar dziennika do konkretnej bazy.

W przypadku bazy danych Biblioteka domyślny rozmiar 1MB jest zupełnie wystarczający (a nawet za duży).

Krok 3 - Utwórz bazę danych przy użyciu języka T-SQL

1. Uruchom program Query Analyzer.
2. Zaloguj się używając uwierzytelnienia systemu Windows.
3. W menu głównym programu wybierz File - Open.
4. W oknie Open Query File wybierz plik demo_3_3_1.sql.
5. Prześledź kod skryptu i spróbuj odpowiedzieć, co wykonują poszczególne bloki kodu.

Przeanalizujmy kod naszego skryptu:

- w pierwszych wierszach kodu (oczywiście nie licząc nagłówka - komentarza) następuje sprawdzenie, czy na serwerze istnieje baza danych Biblioteka; jeśli istnieje, to jest usuwana (DROP DATABASE),


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


Od razu zapamiętaj, jak w T-SQL wstawić komentarz.

Jeśli chcesz wykomentować jedną linijkę kodu używasz dwóch myślników, np.:

-- to jest wykomentowana linia kodu T-SQL

Jeśli chcesz wykomentować więcej linijek kodu, używasz oznaczeń /* komentarz */, np.:

/*
to jest komentarz
na dwie linijki
*/


Pamiętaj o komentowaniu swojego kodu T-SQL. Lepiej komentować wszystko niż w ogóle nie używać komentarzy.


- następnie zmieniamy roboczą bazę danych na master (pamiętaj, że w tej bazie danych zapisywane są informacje o innych bazach),


USE master
GO


- w dalszej części tworzymy bazę danych Biblioteka; baza ta składa się z jednego pliku danych o nazwie logicznej biblioteka_data oraz jednego pliku dziennika transakcji o nazwie biblioteka_log; poszczególne właściwości plików: NAME - logiczna nazwa pliku, FILENAME - ścieżka do pliku (nazwa fizyczna pliku może różnić się od nazwy logicznej), SIZE - rozmiar początkowy pliku (domyślnie w megabajtach, chyba że podano inaczej), MAXSIZE - maksymalny dopuszczalny rozmiar pliku (domyślnie w megabajtach), FILEGROWTH - wartość o jaką serwer powiększy plik w przypadku zapełnienia (w jednostkach pojemności dyskowej - domyślnie w megabajtach - lub w procentach wartości aktualnej).

Utworzyliśmy więc plik danych o rozmiarze początkowym 10MB, rozmiarze dopuszczalnym 50MB, który rozrasta się o 5MB oraz plik dziennika transakcji o rozmiarze początkowym 3MB, rozmiarze dopuszczalnym 15MB, który rozrazsta się o 1MB.


CREATE DATABASE Biblioteka
ON 
	( NAME = biblioteka_data,
	  FILENAME = 'c:\program files\microsoft sql server\mssql\data\biblioteka_data.mdf',
          SIZE = 10,
          MAXSIZE = 50,
          FILEGROWTH = 5)
LOG ON
	( NAME = biblioteka_log,
	  FILENAME = 'c:\program files\microsoft sql server\mssql\data\biblioteka_log.ldf',
          SIZE = 3,
          MAXSIZE = 15,
          FILEGROWTH = 1)
GO


Bazę danych możesz też utworzyć przy użyciu kreatora dostępnego w programie Enterprise Manager. Kreator ten znajduje się na liście kreatorów w kategorii Databases pod pozycją Create Database. Można do niego uzyskać także dostęp klikając prawym przyciskiem myszy w lewej części okna Enterprise Manager'a na folderze Database (opcja New Database...).


Jeśli chcesz wybrać inną lokalizację plików (bo na przykład zainstalowałeś serwer MS SQL na innym dysku), możesz zmienić właściwość FILENAME plików, podając właściwą ścieżkę.

Krok 4 - Zobacz właściwości bazy danych

1. Mając otwarty program Query Analyzer napisz w oknie edytora:

sp_helpdb Biblioteka

2. Uruchom tylko tę linijkę kodu (zaznacz ją i wciśnij klawisz F5).
3. Sprawdź, czy właściwości plików zgadzają się ze zdefiniowanymi w kroku 3 tego ćwiczenia.

Sp_helpdb jest systemową procedurą składowaną, która pozwala oglądać podstawowe właściwości bazy danych i jej plików. Więcej o procedurach składowanych dowiesz się w dalszej części podręcznika.


4. Uruchom program Enterprise Manager.
5. Rozwijaj drzewo po lewej stronie okna programu i wejdź do folderu Databases.
6. Kliknij prawym przyciskiem myszy na bazie danych Biblioteka i z menu kontekstowego wybierz Właściwości.
7. Przejrzyj zakładki okna Biblioteka Properties ze szczególnym uwzględnieniem zakładek General, Data Files oraz Transaction Log. Czy już zauważyłeś, że dane, jakie znajdują się w tych zakładkach odpowiadają danym, które podałeś przy tworzeniu bazy danych Biblioteka?

Właściwości bazy danych można oczywiście modyfikować po jej utworzeniu. Do tego celu służy polecenie języka T-SQL ALTER DATABASE. Drugą możliwość daje okno właściwości bazy danych w programie Enterprise Manager.


Zaplanuj i utwórz pliki dla bazy danych Prace dyplomowe. Użyj programu Query Analyzer. Zapisz skrypt tworzący bazę (z menu głównego Query Analyzer'a wybierz File - Save) pod nazwą baza.sql, by dołączyć go później do projektu. W dokumentacji zamieść też wstępne oszacowanie wielkości pliku danych i dziennika transakcji (wzorując się na przykładzie podanym w tej lekcji).


Przejdź dalej



Ćwiczenia > Budowa bazy danych w MS SQL Server 2000 > Tworzenie bazy danych