09. Z teorii do praktyki czyli "Data Definition Language"

 „Z teorii do praktyki” to motto przewodnie poniższego artykułu. Kolejnych kilka stron będzie poświęconych poleceniom Data Definition Language (DDL). Za pomocą których możemy przeprowadzać operacje edycji i usuwania elementów w MS SQL Server 2005.

Teraz, kiedy już mamy głowę nabitą teorią, możemy przejść do praktycznego aspektu tworzenia baz danych. Stworzymy własny system bazodanowy zgodnie z projektem jaki został zrealizowany i znormalizowany na wcześniejszym kursie. Zaczynamy więc od wymienienia elementów języka Data Definition Language i wyjaśnieniu, czym jest sam język DDL. Używamy go zawsze wtedy, gdy tworzymy, usuwamy lub wstawiamy nowy obiekt do już istniejącego systemu. Inaczej mówiąc służy do zarządzania obiektami bazodanowymi. Podstawowe jego elementy to:

Jednak praktycznie wszystkie powyższe operacje można wykonać bez konieczności pisania kodu, a jedynie za pomocą aplikacji dostępowej Microsoft SQL Server Management Studio. Teraz już kończę wywody i przechodzimy do tworzenia bazy.

Najpierw utworzymy bazę danych za pomocą skryptu SQL. W tym celu naciskamy na przycisk New Query, spowoduje to otwarcie nowego okna gdzie wprowadzimy nasz skrypt. W najprostszej postaci wymaga to jedynie wpisania jednej linii kodu.

CREATE DATABASE Deanery
GO

Teraz wystarczy tylko wcisnąć przycisk Execute (lub F5) i można już sprawdzić czy nasza baza danych została dodana. Niestety standardowo w okienku Object Explorer lista baz danych nie odświeża się automatycznie(dotyczy to wszystkich elementów i operacji na bazie). Aby zobaczyć wyniki konieczne jest odświeżenie korzenia Database w wyżej wymienionym oknie. Jest to jest pokazane na poniższym zrzucie ekranowym.

Jeśli umiemy już tworzyć bazę danych, chciałbym pokazać, jak można ją usunąć, a następnie ponownie utworzyć za pomocą GUI aplikacji. Aby usunąć całą bazę danych wystarczy tylko prawym guzikiem myszy kliknąć na obiekt, który chcemy usunąć. Następnie wybrać z menu podręcznego opcje Delete i potwierdzić wybór. Alternatywnie skrypt wyglądałby również dość nieskomplikowanie. Rożni się jedynie tym iż zamiast polecenia CREATE wstawiamy DROP:

DROP DATABASE Deanery
GO

Czym jest jednak pusta baza danych? Przejdźmy do tworzenia poszczególnych tabel i relacji pomiędzy nimi w taki sposób, aby odwzorować strukturę bazy którą zaprojektowaliśmy wspólnie we wcześniejszych rozdziałach.

Rozpoczynamy od utworzenia tabeli słownikowej zawierającej kraje z jakich pochodzą studenci studiujący na naszej uczelni.

CREATE TABLE [dbo].[Country]
(
        [CountryID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
        [CountryName] [nvarchar] (64) NOT NULL
)
GO

Ponieważ w naszej bazie klucze główne są wartościami całkowitymi musimy mieć pewność, iż każde następne wstawione państwo będzie miało unikalny w skali tabeli klucz główny. Aby sprostać temu zadaniu, dokładamy do klucza głównego klauzule IDENTITY (1, 1). Mówi ona tyle, że wartość będzie nadawana od wartości 1 i z każdym kolejnym nadanym ID będzie zwiększana. Jeśli byśmy użyli wyrażenia w następujący sposób IDENTITY (15, 3) to pierwszy wstawiony do bazy danych kraj miał by ID 15, a następny 18. Kolejnym interesującym nas polem jest wartość NOT NULL. Wspomnieliśmy o niej już wcześniej w rozdziale dotyczącym typów danych. Tu chciałbym tylko przypomnieć, że jeśli wartość kolumny w bazie danych jest ustawiona na NOT NULL oznacza, iż nie ma możliwości wstawienia do bazy danych rekordu, który będzie miał wartość nieokreśloną dla danej kolumny. W naszym konkretnym przypadku dotyczącym państw niemożliwe byłoby wstawienie wiersza, który nie ma podanej nazwy kraju. Dzięki temu, iż CountryID ma ustawioną IDENTITY, nie musimy dbać o wstawienie tam wartości. Serwer bazy danych sam zaopiekuje się uzupełnieniem pola oraz nadaniem unikalnego w ramach tabeli klucza. Dla krajów założyliśmy, że liczba liter nie będzie większa niż 64 znaki czyli „Zjednoczone Emiraty Arabskie” oraz „Stany Zjednoczone Ameryki Północnej” bez problemu zmieszczą się w przeznaczonym dla nich rekordzie. Dodatkowo umożliwiliśmy wpisywanie wszystkich znaków międzynarodowych (parz „Co to jest typ danych? Jakie typy danych dostarcza nam SQL Server 2005”).
Skrypty tworzące kolejne tabele będę mniej dokładnie opisywał. Skupię się na tych opcjach, które nie były przedstawione dotychczas.

CREATE TABLE [dbo].[Student]
(
            [StudentID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [Firstname] [nvarchar] (64) NOT NULL ,
            [Lastname] [nvarchar] (64) NOT NULL ,
            [Birth] [datetime] NULL ,
            [EmailAdress] [nvarchar] (64) NULL ,
            [Mobile] [nvarchar] (16) NULL ,
            [CountryID] [int] NULL REFERENCES [dbo].[Country] ([CountryID])
)
GO

Jak łatwo zauważyć, kod tworzący tabelę jest dość intuicyjny. Jedyną obco wyglądającą linią może być ta ostatnia:

[CountryID] [int] NULL REFERENCES [dbo].[Country] ([CountryID])

Informuje ona serwer o tym, iż wartość CountryID w tabeli Student jest wartością, która musi się znajdować w tabeli Country w kolumnie CountryID. Podczas wstawiania nowego studenta system sprawdza czy identyfikator kraju znajduje się w tabeli Country. Gdybyśmy mieli w tabeli z krajami następujące wpisy:

To próba dodania następującego rekordu:

zakończyłaby się niepowodzeniem. Przyczyną tego jest fakt, iż w tabeli Country nie mamy kraju z CountryID = 7. Aby operacja zakończyła się powodzeniem, musielibyśmy najpierw wstawić do zbioru krajów rekord:

Teraz bez problemu możemy umieścić w naszym repozytorium wpis dotyczący pana nazywającego się Grzegorz Chuchra.

Po utworzeniu struktury danych opisujących studentów czas przejść do tabeli charakteryzującej przedmioty.

CREATE TABLE [dbo].[Subject]
(
            [SubjectID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [SubjectName] [nvarchar] (64) NOT NULL ,
            [Type] [int] NULL
) GO

Skrypt tworzący jest analogiczny do tego, który posłużył nam do utworzenia tabeli zawierające dane o krajach. Dodaliśmy tylko pole z możliwością wpisania rekordu bez uzupełnienia tej wartości. Pole Type, bo o nim tu mowa, charakteryzuje rodzaj kursu - czy jest to zaliczenie, wykład, laboratoria itd.

Przechodzimy teraz do encji związanej z danymi o lektorach:

CREATE TABLE [dbo].[Lector]
(
            [LectorID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
            [Firstname] [nvarchar] (64) NOT NULL ,
            [Lastname] [nvarchar] (64) NOT NULL ,
            [EmailAdress] [nvarchar] (64) NULL ,
            [Mobile] [nvarchar] (16) NULL ,
            [Phone] [nvarchar] (16) NULL ,
            [NIP] [nvarchar] (16) NULL ,
            [Salary] [float] NULL
)
GO

Po dokładnym przeanalizowaniu wcześniejszych skryptów wydaje mi się, że nie ma potrzeby rozwodzenia się nad tabelą Lector. Jest ona tożsama do Student z dokładnością do danych dotyczących wynagrodzenia i związanego z nim NIP-u.

Jeśli już opanowaliśmy tworzenie prostych relacji między obiektami bazodanowymi czas przejść do najbardziej skomplikowanej struktury w naszej bazie. Sam opis tabeli i charakter jej funkcjonowania został zawarty w rozdziale „Klucze główne, obce. Asocjacji.” Tu tylko ograniczę się do napisania skryptu, co jest znacznie prostszą operacją, aniżeli zaprojektowanie takiej struktury.

CREATE TABLE [dbo].[StudentLectorSubject]
(
            [StudentID] [int] NOT NULL REFERENCES [dbo].[Lector] ([LectorID]),
            [LectorID] [int] NOT NULL REFERENCES [dbo].[Student] ([StudentID]),
            [SubjectID] [int] NOT NULL REFERENCES [dbo].[Subject] ([SubjectID]),
            [Mark] [float] NULL,
            PRIMARY KEY ([StudentID], [LectorID], [SubjectID])
)

Jeśli już wiemy jak napisać skrypt tworzący bazę, chciałbym abyśmy przeszli do utworzenie tego samego za pomocą Management Studio. Jak zaraz zobaczymy jest to znacznie przyjemniejsze, a dodatkowo w każdym momencie możemy automatycznie wygenerować kod tworzący poszczególne obiekty, jak i całą bazę danych. Przedstawię, jak utworzyć strukturę dla tabelki Country, ponieważ utworzenie pozostałych tabel odbywa się na identycznej zasadzie, więc wydaje mi się, że nie ma potrzeby rozwodzenia się nad tym zagadnieniem. Ale koniec gadania - przechodzimy do pracy.

Standardowo zaczynamy od rozwinięcia katalogu z bazami danych istniejącymi na naszym serwerze. Odszukujemy bazę Deanery, naciskamy prawym guzikiem myszy na katalogu Tables. Z menu podręcznego wybieramy New Table… i przepisujemy kolejno dane ze skryptów. Jako pierwszą dodaliśmy encję Country, więc edycję rozpoczniemy właśnie od tej tabeli. Wpisujemy do pierwszej komórki zatytułowanej Column Name wartość CountryID, do Column Type int, a ostatnie pole checkBox pozostawiamy odznaczone. Tym sposobem wprowadziliśmy już większą część informacji dotyczącej kolumny. Teraz musimy przejść do zakładki Column Properties gdzie włączamy opcję autoinkrementacji (kolejnego nadawania ID dla klucza głównego). U mnie wygląda to następująco:

Podsumowanie

W rozdziale tym zapoznaliśmy się z zagadnieniem implementacji bazy danych. Wydaje mi się, że tworzenie bazy danych jest operacją mało skomplikowaną. Skrypty SQL ograniczają się do zapoznania z niewielką liczbą komend, a dodatkowym atutem jest możliwość skorzystania z narzędzi takich jak Microsoft SQL Server Management Studio wspomagających implementację zarówno z poziomu skryptów, jak i za pomocą interfejsu graficznego.