Po utworzeniu projektu bazy danych kolej na jego realizację, czyli na utworzenie tabel w relacyjnej bazie danych. Jeśli chodzi o tworzenie tabel i implementację różnorodnych reguł związanych z tabelami większość baz danych udostępnia wiele opcji. Najprostsza i najszybsza metoda polega na założeniu tabel i rozpoczęciu wprowadzania danych. W wielu wypadkach warto jednak architekturę bazy danych znacznie rozbudować: zdefiniować klucze główne, reguły zapewniające integralność danych, określić wartości domyślne dla kolumn i relacje między tabelami. Wbudowanie tych reguł do samej bazy danych już przy jej utworzeniu pozwoli zaoszczędzić pisania wielu linii kodu w aplikacji obsługującej bazę, mających na celu walidację danych i zapewnienie ich integralności. Poza tym pozwala to zachować pewność, że dane w bazie spełniają ustalone założenia, bez względu na to w jaki sposób (np. za pomocąjakiej aplikacji) zostały tam wprowadzone. Tabele definiujemy używając instrukcji CREATE TABLE. W języku SQL instrukcja CREATE służy do definicji wielu obiektów w bazie danych: tabel, indeksów czy użytkowników. Do określenia jaki obiekt jest definiowany służy słowo kluczowe następujące po instrukcji CREATE. W poprzednim rozdziale omówiliśmy projektowanie relacyjnej bazy danych. Pokazałem, jak dane powinny być podzielone między tabelami oraz jakie własności przypisujemy poszczególnym tabelom. Poza wskazaniem, które kolumny spełniają rolę kluczy nie wnikaliśmy w inne elementy struktury tabeli. W tym rozdziale pokażę zastosowanie instrukcji CREATE TABLE do tworzenia tabel oraz instrukcji CREATE INDEX do tworzenia indeksów na kolumnach i grupach kolumn. Zostanie to zilustrowane utworzeniem naszej przykładowej bazy danych o filmach. #68 Tworzenie bazy danych
W niektórych bazach danych przed rozpoczęciem tworzenia tabel, należy zdefiniować samą bazę danych oraz przydzielić dla niej miejsce w pamięci masowej komputera. Tej operacji można dokonać poprzez graficzny interfejs użytkownika lub używając instrukcji CREATE DATABASE. W najprostszej formie instrukcja ta wygląda następująco:
CREATE DATABASE nazwa_bazy
System Oracle wymaga, aby nazwa bazy danych była nie dłuższa niż 8 znaków. Utworzenie w tym systemie bazy danych o nazwie FI wykona następująca instrukcja :
CREATE DATABASE FI
Do instrukcji CREATE DATABASE można dodać wiele dodatkowych argumentów. Pozwala to na przykład określić fizyczną lokalizację bazy danych na dysku i wiele innych opcji. Opcje te różnią się znacznie dla poszczególnych implementacji baz danych, dlatego zostaną opisane później, przy okazji omawiania konkretnych produktów.
Istnieje wiele instrukcji CREATE, które pozwalają określić różne wewnętrzne parametry bazy danych. Na przykład w systemie ORACLE za pomocą instrukcji CREATE TABLESPACE można powiększyć przydzielone dla tabel miejsce na dysku. Z kolei instrukcja CREATE SCHEMA umożliwia zdefiniowanie więcej niż jednej tabeli w jednej instrukcji. Wiele z instrukcji CREATE pojawi się jeszcze w różnych rozdziałach tej książki.
Dostęp do wybranej bazy danych
W przypadku, gdy pracujemy z więcej niż jedną bazą danych musimy dokładnie określić, w której bazie znajduje się wybrana tabela. Zwykle poprzedzamy nazwę tabeli nazwą bazy danych, żeby wskazać, że odwołujemy się do bazy innej niż aktualnie aktywna. ================ Rada Użycie dwóch kropek pomiędzy nazwą bazy danych, a nazwą tabeli jest skrótem, który można stosować, gdy jest znany właściciel bazy danych. Pełen zapis wymaga podania ID użytkownika jak poniżej:
DB_DWA.KTO.JakaśTabela
Dostęp do innej bazy danych uzyskujemy też wykorzystując komendę USE lub DATABASE. Przejście do drugiej bazy danych następuje przez wydanie komendy:
USE DB_DWA #69 Na przykład w Microsoft SQL Server, jeżeli pracujemy w z bazą DB_JEDEN, a chcemy mieć dostęp do tabeli z DB_DWA używamy następującej instrukcji:
SELECT * FROM DB DWA.. JAKAŚ TABLICA
Tworzenie tabeli
Do zdefiniowania nowej tabeli używamy instrukcji CREATE TABLE. Najprostsza instrukcja wygląda następująco:
Powstanie tabela o nazwie Nazwa_Tabeli, z kolumnami zgodnie z podaną listą. Każda kolumna musi mieć określony typ danych. Dla większości typów danych wymagane jest także określenie rozmiaru. Szczegółowo zapoznamy się z typami danych jeszcze w tym rozdziale. Listing 3.1 przedstawia instrukcję definiującą tabelę w systemie Oracle. Inna wersja tej instrukcji, stosowana w języku Transact SQL (używanym w bazach danych Sybase i Microsoft SQL Server), znajduje się na listingu 3.2. ----------------------- Listing 3.1. Instrukcja tworząca tabelę Studios
CREATE TABLE Studios (name CHAR(20), city VARCHAR2(50), state CHAR(2), revenue FLOAT) --------------------- --------------------- Listing 3.2. Instrukcja tworząca tabelę Studios w Transact SQL
CREATE TABLE Studios (name CHAR(20), city VARCHAR(50), state CHAR(2), revenue FLOAT)
W tej chwili zwróćmy uwagę na strukturę instrukcji, nie przejmując się zdefiniowanymi typami danych. Została zdefiniowana tabela studios (studia) o czterech kolumnach: name, city, state, revenue (nazwa, miasto, stan, dochody). W instrukcji CREATE zostało pominięte wiele opcji, jak na przykład możliwość zdefiniowania klucza głównego, określenie relacji z innymi tabelami, wprowadzenie ograniczeń na wartości dla kolumn itp. Zostaną one wszystkie omówione w dalszej części tego rozdziału. #70 Typy danych
Definiując tabelę możemy określić dla każdej kolumny odpowiedni typ danych. Typ determinuje nie tylko sposób przechowywania danych na dysku, ale, co ważniejsze, sposób interpretacji tych danych. Na przykład porównując dwie wartości liczbowe lub dwie daty na pewno nie chcesz posługiwać się kryterium porządku alfabetycznego. Wymagania dotyczące zajmowania pamięci są równie istotne. Marnotrawstwem byłoby rezerwowanie 255 bajtów dla pola, które wykorzystuje tylko 2 bajty. Analogicznie, rezerwowanie 5 bajtów dla numeru telefonu, który może mieć 10 cyfr to zdecydowanie za mało. Na szczęście relacyjne bazy danych dostarczają bardzo bogaty zestaw typów danych. Istnieją typy danych tekstowych, liczby, typy określające czas oraz obiekty, takie jak dane binarne czy duże teksty. Każda baza danych posiada swoje własne zestawy typów danych, mogące się różnić pomiędzy sobą np. nazwami. Niektóre systemy baz danych udostępniają również podtypy, jak na przykład dla typu liczbowego może to być liczba całkowita, zmiennoprzecinkowa czy waluta. ================== Rada Pomimo, że wiele komercyjnych baz danych jest zgodna ze standardem SQL-89, a nawet częściowo z SQL-92, to nie ma jednoznaczności w sprawie typów danych. Większość baz danych obsługuje podstawowe typy, choć pomiędzy różnymi produktami nie ma pełnej zgodności. Wiele produktów uzupełnia typy standardowe własnymi rozszerzeniami. W tej książce omówię typy standardowe, choć po szczegóły trzeba będzie sięgnąć do dokumentacji technicznej konkretnego produktu. W części VI "Przegląd baz danych" opisałem kilka typów niestandardowych. ===================
Typy danych możemy podzielić na cztery kategorie: dane łańcuchowe, numeryczne, określające czas i duże obiekty. Dane łańcuchowe mogą przechowywać właściwie każdy typ danych z zastrzeżeniem, że dane te są traktowane tylko jak łańcuch znaków. Dane numeryczne i określenia czasu umożliwiają wykonywanie działań matematycznych oraz innych funkcji do przetwarzania danych. Ostatnia kategoria, czyli duże obiekty, służy do gromadzenia dużych ilości informacji. Są one traktowane odmiennie od poprzednio omówionych typów danych, np. nie można porównywać takich obiektów. Ważna różnica między typami danych polega na sposobie traktowania ich przez język SQL. Dane łańcuchowe, określenia czasu i duże obiekty muszą być w instrukcjach SQL zawarte w pojedyncze cudzysłowy. Dane numeryczne nie są zapisywane w cudzysłowach. #71 Dane łańcuchowe
W większości baz danych mamy do dyspozycji dwa rodzaje typów łańcuchowych. Pierwsze, to łańcuchy o ustalonej długości, drugie - o zmiennej długości. Różnica polega na tym, że ustalona długość powoduje zawsze rezerwację takiej samej ilości pamięci, bez względu na wymagania danych, natomiast zmienna długość zużywa tylko tyle pamięci, ile jest potrzebne dla konkretnej wartości. CHAR jest typem dla danych o ustalonej długości. W listingu 3.1 kolumna state ma określony typ CHAR (2). Kolumna ta zużywa zawsze 2 bajty pamięci, nawet gdy jest pusta lub wpisana wartość zajmuje l bajt. Podobnie ustaloną na 20 znaków długość ma pole name. W polu typu CHAR miejsce nie zużyte przez dane jest automatycznie uzupełniane spacjami. Możemy również przechowywać łańcuchy wewnątrz typu o zmiennej długości. Ten typ danych to VARCHAR lub w bazie Oracle VARCHAR2. Przy deklaracji tego typu danych określamy maksymalną długość. Pole city z listingu 3.1 może mieć maksymalną długość 50 znaków. Różnica między VARCHAR (50) a CHAR (50) polega na tym, że pole o zmiennej długości dostosowuje potrzebną pamięć do danych. Jeżeli pole city potrzebuje tylko 10 znaków, to tylko tyle pamięci zostanie zużytej. Maksymalny rozmiar pola znakowego różni się znacząco pomiędzy systemami. W systemie Oracle wynosi 2000 znaków, a pole YARCHAR2 do 4000 znaków. Inne bazy danych dopuszczają tylko 255 znaków dla obydwu typów. W przypadku, gdy chcemy zapamiętać większą ilość danych znakowych, do dyspozycji mamy specjalny typ dla dużych obiektów tekstowych. Oracle oferuje CLOB (Character Large OBject) umożliwiający zapamiętanie do 2GB. W Microsoft SQL Server do tego celu mamy typ danych TEXT.
Dane numeryczne
Dane numeryczne nie muszą być pisane wewnątrz cudzysłowów, mogą występować w wyrażeniach matematycznych. Czasami opłaca się przechowywanie danych numerycznych w polu znakowym. Takie dane, jak kod pocztowy, czy numer telefonu lepiej zapamiętać w polu tekstowym, mimo że składają się z cyfr. Często dane numeryczne pomijają początkowe zero. Jeśli dane nie będą używane w wyrażeniach matematycznych, równie dobrze można je przechowywać w polu znakowym. Większość baz danych dostarcza dwóch typów numerycznych jeden dla liczb całkowitych, drugi dla zmiennoprzecinkowych. Czasem mamy do dyspozycji jeszcze bardzie szczegółowe, jak MONEY (ang. waluta), który automatycznie przydziela dwa miejsca po przecinku. Tabela 3.1 przedstawia listę najpowszechniejszych typów numerycznych. Niektóre z nich mogą nie być obsługiwane przez twoją bazę danych, może też się zda- rzyć, że masz dostęp do typu danych nie zawartego w tej tabeli. #72 Liczba cyfr obsługiwana prze/ pole numeryczne może się różnić w zależności od bazy danych. W wielu możesz sam o tym zdecydować, podobnie jak w typie CHAR.
Tabela 3.1. Typy danych numerycznych w SQL
Typ danych Definicja DECIMAL Liczba zmiennoprzecinkowa FLOAT Liczba zmiennoprzecinkowa INTEGER(rozmiar) Liczba całkowita o określonej długości MONEY Liczba posiadająca dwie pozycje dziesiętne NUMBER Standardowa liczba zmiennoprzecinkowa
Określenia czasu
Kolejnym ważnym typem danych są dane określające czas. Bazy danych różnią się sposobem obsługi tego typu, sposobem przechowywania w pamięci, wyświetlania na ekranie i różnymi podtypami. Występują trzy podstawowe typy określające czas: data, czas oraz data i czas. ================= Rada Chciałbym przedstawić problem związany z rokiem 2000. Zarezerwowanie tylko dwóch pozycji do reprezentacji roku w dacie prowadzi do problemu znanego jak Y2K. Opiera się on na przyjętym uproszczeniu, że dwie pierwsze cyfry roku to 19. W momencie zmiany daty z 1999 na 2000 programy bazujące na tym założeniu przyjmą, że jest rok 1900. =================
Inne typy danych, nie opisane tutaj, zostaną omówione w rozdziale 12. Na razie zajmiemy się zastosowaniem przedstawionych wiadomości w przykładowej bazie danych.
Określanie kluczy
W większości baz danych nie ma konieczności wskazywania, które kolumny należą do klucza. Jednak dobrym zwyczajem jest używanie kluczy, aby zapobiec duplikacji wierszy. Kiedy tworzysz tabelę, możesz zdefiniować zarówno klucz główny jak i klucze kandydujące. Słowo kluczowe UNIQUE służy do określenia, która kolumna (lub grupa kolumn) musi być unikalna. Użycie ograniczenia UNIQUE powoduje, że próba powtórzenia danych w tych kolumnach będzie przez bazę danych powstrzymana. Listing 3.3 przedstawia instrukcję wykorzystującą słowo kluczowe UNIQUE do definicji kluczy kandydujących. W listingu 3.3 klauzula UNIQUE powoduje, że wartości w kolumnie name muszą być unikalne. Zdefiniowanie grupy kolumn jako wartości unikalnych wymaga wypisania w nawiasach nazw tych kolumn. #73 ------------------ Listing 3.3. Definiowanie kluczy kandydujących w tabeli Studios
CREATE TABLE Studios (studio_id NUMBER, name CHARI20), city VARCHAR2(50 , state CHAR(2) UNIQUE (name)) ------------------
Aby określi6 kilka kluczy kandydujących używamy kilka razy klauzuli UNIQUE, jak w listingu 3.4. --------------------- Listing 3.4. Wiele kluczy kandydujących w jednej tabeli
CREATE TABLE Studios (studio_id NUMBER, name CHAR120), city YARCHAR2(50), state CHAR(2) UNIQUE (name)) UNIQUE(city, state) --------------------
W tym wypadku zdefiniowano dwa klucze kandydujące. Taka definicja mogłaby być potrzebna, gdyby istniał przepis, że w jednym mieście może działać tylko jedno studio. Zdefiniowanie klucza głównego w miejsce klucza kandydującego wymaga użycia klauzuli PRIMARY KEY. W tabeli może być zdefiniowany tylko jeden klucz główny. Listing 3.5 przedstawia taki przykład. --------------------- Listing 3.5. Tabela z kluczem głównym i kluczem kandydującym CREATE TABLE Studios (studio_id NUMBER, name CHAR(20), City VARCHAR2(50), state CHAR(2) PRIMARY KEY (studio_id) UNIQUE (name)) --------------------
Można również określić klucz główny i kandydujący w obrębie kolumny zamiast umieszczania dodatkowych klauzuli na końcu instrukcji. Jedynie klucze wielokolumnowe mogą być definiowane tylko na końcu instrukcji. Listing 3.6 zawiera klucz zdefiniowany w obrębie kolumny. ------------------------ Listing 3.6. Tablica z kluczami zdefiniowanymi w obrębie kolumny
CREATE TABLE Studios (studio_id NUMBER PRIMARY KEY, name CHARI20) UNIQUE, city VARCHAR2 (50), state CHAR(2) --------------------------
Niestety, nie wszystkie bazy danych umożliwiają definiowanie kluczy w ten sposób. Zdarzają się przypadki, że w celu zapewnienia integralności danych musimy kontrolować wprowadzane dane z poziomu programu. #74 Klucze obce
W systemie Oracle i w wielu innych bazach danych możemy wskazać, że kolumna jest kluczem obcym, który odwołuje się do kolumny w innej tabeli. Klauzula REFERENCES służy do ustalenia relacji pomiędzy kolumnami z jednej tabeli i kluczem kandydującym drugiej. Pokazuje to listing 3.7. ------------------------ Listing 3.7. Ustalanie relacji
CREATE TABLE Movies (movie_title VARCHAR2(40), studio id NUMBER REFERENCES Studios(studio id) -----------------------
Można również użyć klauzuli REFERENCES w obrębie definicji tabeli w przypadku,gdy klucz składa się z kilku kolumn. Na przykład gdy movie_title oraz studio_id stanowią klucz główny tabeli Movies, z której chcemy utworzyć relację do tablicy zawierającej aktualnie wyświetlane filmy, możemy użyć instrukcji jak w listingu 3.8. -------------------------- Listing 3.8. Tabela Showtimes
Kolumny movie_title oraz studio_id w tabeli Showtimes tworzą odnośnik do kolumn o takiej samej nazwie w tabeli Movies. Użycie klauzuli REFERENCES w listingu 3.7 tworzy relację pomiędzy kolumnami studio_id w tabelach Movies i Studios. W systemie Oracle można używać wyrażenia ON CASCADE DELETE łącznie z klauzulą REFERENCES, aby automatycznie usuwać powiązane rekordy w tabeli podrzędnej, zawsze gdy usuwany jest rekord z tabeli nadrzędnej. W wielu przypadkach nie wykorzystuje się tej możliwości, ponieważ dane w podrzędnej tabeli mogą być przydatne. Ograniczenia dla kolumn Poza określeniem, które kolumny stanowią klucze, możemy również wprowadzić dodatkowe ograniczenia na dane wprowadzane do poszczególnych kolumn.. Można zdefiniować ograniczenie, które sprawdza, czy wysokość wynagrodzenia pracownika nie jest wyższa od wynagrodzenia jego szefa, albo uniemożliwia wprowadzanie dat późniejszych niż bieżąca. #75 Odrzucanie wartości null Najczęściej stosujemy ograniczenia dla kolumn, żeby zapobiec wprowadzaniu wartości null. Pole, do którego nie wpisano żadnej wartości zawiera właśnie null, co wskazuje, że wartość w tym polu jest nieznana. Często nie chcemy, żeby wartości null zostały wprowadzone do pól, które są częścią klucza głównego lub kandydującego. W związku z problemami, jakie mogą wyniknąć z dopuszczenia wartości null, zabezpieczamy tabelę poprzez zdefiniowanie klucza głównego dla wybranych kolumn. Użycie klauzuli NOT NULL w definicji kolumny wymusza podanie wartości dla takiej kolumny przy każdym wprowadzeniu nowego wiersza. Zapobiega to również zmianie wartości na null przy aktualizacji danych w tabeli. Listing 3.9 przedstawia przykład wykorzystania klauzuli not null. ------------------------- Listing 3.9. Definicja tabeli Studios z ograniczeniem not null
CREATE TABLE Studios studio_id NUMBER PRIMARY KEY, name CHAR(20) NOT NULL, City VARCHAR2(50) NOT NULL, State CHAR(2) NOT NULL) --------------------------
W listingu 3.9 żadna z kolumn nie może zawierać wartości null. W przypadku kolumn name, city, state decyduje o tym wyrażenie NOT NULL, w przypadku kolumny studio_id wynika to z użycia PRIMARY KEY. Można również pozwolić na wprowadzanie wartości null poprzez dopisanie słowa NULL na końcu definicji kolumny. Dla wielu baz danych jest to ustawienie domyślne i nie wymaga jawnej definicji.
Inne ograniczenia
Wiele baz danych umożliwia stosowanie innych ograniczeń na dane wprowadzane do poszczególnych kolumn. Te ograniczenia korzystają z operatorów relacyjnych omówionych w rozdziale 6. Działanie polega na tym, że jeżeli dane wprowadzane poprzez instrukcję INSERT lub UPDATE nie spełniają warunków ograniczenia, to nie są dopisywane. Słowo kluczowe CHECK jest używane do tworzenia takich ograniczeń. Na przykład jeżeli nie wprowadzamy do bazy filmów, których budżet był mniejszy od 500 000, możemy użyć wyrażenia jak w listingu 3.10. --------------------------- Listing 3.10. Definicja tabeli Movies z ograniczeniem check
CREATE TABLE Movies (movie_title VARCHAR2(40) PRIMARY KEY, studio_id NUMBER, budget NUMBER CHECK (budget > 500000)) --------------------- #76 Wyrażenie budget > 500000 testuje każdą wartość wprowadzaną do kolumny budget i odrzuca jeśli jest mniejsza lub równa 500000. Można również zdefiniować ograniczenia na poziomie tabeli używając słowa kluczowego CONSTRAINT. Zaletą ograniczeń na poziomie tabeli jest możliwość nazywania ich oraz tworzenia ograniczeń wielokolumnowych. Omówione wcześniej ograniczenia UNIQUE oraz PRIMARY KEY również mogą być stosowane na poziomie tabeli. Listing 3.11 przedstawia przykład utworzenia takiego ograniczenia, które posiada nazwę. ------------------------- Listing 3.11. Definicja z ograniczeniem na poziomie tabeli:
CREATE TABLE Movies (movie_title VARCHAR2(40) studio_id NUMBER, release_date DATĘ, CONSTRAINT release_date_constraint CHECK (release_date BETWEEN '01-STY-1980' AND '31-GRU-1989')) ------------------------ ================== Rada Ograniczenia nakładane na kolumny przy pomocy klauzuli CHECK nie dają możliwości definiowania złożonych warunków testowania danych. Do tego celu służą specjalne procedury składowania zwane triggerami. Przy ich pomocy można tworzyć na wprowadzanych i modyfikowanych danych bardzo złożone testy. =================
Przedstawiony przykład z ograniczeniem nazwanym release_date_constraint umożliwia wprowadzanie do bazy tylko filmów wypuszczonych od początku roku 1980 do końca 1989 roku. Nazwanie ograniczenia umożliwia odwoływanie się do niego później, na przykład w celu aktywacji lub deaktywacji.
Wartości domyślne
Kolejnym udogodnieniem dostępnym podczas projektowania tabel jest używanie wartości domyślnych. Chcąc zidentyfikować każdy rekord przez datę i czas wprowadzenia można zastosować słowo kluczowe DEFAULT. Pokazuje to listing 3.12. --------------------------------- Listing 3.12. Wykorzystanie słowa kluczowego DEFAULT
CREATE TABLE Movies (movie_title VARCHAR2(40) NOT NULL release_date DATE DEFAULT SYSDATE NULL, genre varchar2(20) DEFAULT 'Comedy' CHECK genre IN('Horror1, 'Komedia', 'Dramat')) ------------------------
Kolumny release_date i genre (gatunek) mają zadeklarowane wartości domyślne. Dla kolumny release_date wartością domyślną jest bieżąca data i czas, poza tym jest dozwolone używanie wartości null. Wartością domyślną dla kolumny genre jest komedia, a dodatkowo zdefiniowano ograniczenie, które sprawdza czy wprowadzane dane należą do gatunku komedii, horroru czy dramatu. #77 ================ Rada Można używać dowolnych wyrażeń do określania wartości domyślnych dla kolumn. Na przykład jeżeli chcemy, żeby wprowadzana data była o tydzień późniejsza od obecnej, możemy w klauzuli DEFAULT użyć następującego wyrażenia: SYSDATE + 7. ===============
Projekt bazy danych o filmach
W poprzednim rozdziale opisałem proces projektowania bazy danych w oparciu o przykładową bazę danych o filmach. Została tam omówiona struktura poszczególnych tabel, które teraz zostaną po kolei zdefiniowane.
Tabela Movies
Wszystkie dane w naszej bazie są zorganizowane wokół tabeli Movies. Inne tabele dostarczają informacji uzupełniających i chociażby z tej perspektywy są drugorzędne. Tabela Movies będzie się składała z następujących kolumn: movie_title - tytuł filmu, studio_id - identyfikator studia, w którym wyprodukowano film, director_id - identyfikator reżysera filmu, release_date - data wypuszczenia filmu, gross - dochody z filmu, budget - budżet filmu. Dodatkowo wprowadzimy kolumnę movie_id, aby ułatwić tworzenie powiązań z innymi tabelami. Wprowadzenie w każdej tabeli specjalnego pola numerycznego jako klucza głównego znacznie ułatwia definiowanie kluczy obcych i połączeń między tabelami. Kolumna movie_id służy jako klucz główny tabeli. Kolumny movie_title, studio_id stanowią klucz kandydujący. Kolumna studio_id jest również kluczem obcym związanym z tabelą studios, a kolumna director_id kluczem obcym z tabeli People. W przypadku kolumn budget i gross wartości będą wprowadzane w milionach. Definicję tabeli przedstawia listing 3.13. ----------------------- Listing 3.13. Definicja tabeli Movies
CREATE TABLE Movies (movie_id NUMBER, movie_title VARCHAR2(20), studio_id NUMBER REFERENCES Studios (studio_id), director_id NUMBER REFERNCES People(człowiek_id), gross NUMBER, budget NUMBER, release_date DATĘ, PRIMARY KEY (movie_id), UNIOUE (movie_title, studio_id)) --------------------- #78 Listing 3.13 przedstawia definicję tabeli Movies dla bazy danych Oracle. W przykładzie zdefiniowano kolumny, klucze i relacje. W systemie Oracle wystarcza zdefiniować tylko nazwy kolumn. Najprostszą wersję definicji tabeli Movies przedstawia listing 3.14. W przypadku innych systemów instrukcje mogą się nieznacznie różnić. ----------------------- Listing 3.14. Najprostsza wersja definicji tabeli Movies
Teraz zajmijmy się typami danych. Użyłem standardowego typu numerycznego zarówno dla pól ID oraz pola budget i gross. Ten typ danych jest prawidłowy tak dla liczb całkowitych jak i liczb zmiennoprzecinkowych. Dla tytułu filmu zastosowałem VARCHAR2 (20), który ogranicza tytuł filmu do 20 znaków. W innych okolicznościach możnaby zastosować więcej znaków dla tego pola. W książce, aby mieć pewność że wszystkie kolumny tabeli zmieszczą się na ekranie o szerokości 80 znaków, zdecydowałem się użyć krótszych pól. W listingu 3.14 instrukcja CREATE nie wprowadza do definicji żadnych ograniczeń związanych z kontrolą integralności danych. Nie ma zabezpieczeń przed wprowadzaniem duplikujących się danych lub pojawieniem się niespójnych danych. W celu zapewnienia integralności danych należy posłużyć się definicją z listingu 3.13. Jak wiadomo PRIMARY KEY i UNIQUE zapobiega wprowadzaniu wartości null do kolumn movie_id, studio_id, movie_title. Jednak, jeśli chcielibyśmy mieć możliwość wprowadzenia filmu do bazy, zanim wejdzie na ekrany, sensowne jest dopuszczenie wartości null dla pól gross i release_date. Ponadto, jeśli chcemy mieć film, który jeszcze nie wszedł do produkcji, dodatkowo musimy dopuścić wartości null dla pól director_id oraz budget. Po naszej analizie pojawia się pytanie nad sensownością używania wartości domyślnych. Z drugiej strony, niektóre pola jak budget czy gross, do których wprowadzamy wartości w milionach przy zastosowaniu ograniczeń zapewniają, że wprowadzane są liczby z odpowiedniego przedziału. Dla pola budget wydaje się sensowna wartość maksymalna 200 (chodzi o 200 min), a dla pola gross 1000 (chodzi o l mld). Wartości te były bezpieczne zanim nie pojawił się "Titanic". Prowadzi to do kolejnej ważnej wskazówki, aby przy projektowaniu myśleć o przyszłych danych, które mogą się w bazie pojawić, a nie tylko o tych, którymi dysponujemy w momencie projektowania bazy. Kolejną definicję tabeli Movies zawiera listing 3.15. ---------------------------- Listing 3.15. Definicja tabeli Movies z ograniczeniem na pola budget i gross
Użycie definicji tabeli Movies z instrukcją REFERENCES wymaga najpierw utworzenia tabeli Studios. Wynika to stąd, że musi istnieć kolumna, do której następuje odwołanie w instrukcji REFERNCES. Tablica studios jest prosta. Składa się z kolumn studio_id, studio_name, city i stan, w którym studio się znajduje. Podobnie jak przy definicji poprzedniej tabeli rozmiar pól jest celowo mniejszy, aby umożliwić prezentację danych na ekranie monitora. Listing 3.16 przedstawia taką definicję. -------------------------- Listing 3.16. Definicja tabeli Studios
CREATE TABLE Studios (Studio_id NUMBER PRIMARY KEY, studio_name VARCHAR2(20) UNIQUE, studio_city VARCHAR2(20) NOT NULL, studio_state CHAR(2) NOT NULL Table created --------------------------
Jak widać na listingu 3.16 ograniczenia zostały zawarte na poziomie definicji kolumny studio_id jest kluczem głównym, a studio_name jest kluczem kandydującym. Nie dopuszczam do wprowadzania wartości null do tabeli. Kolumna studio_id z tabeli Movies jest powiązana relacją z kolumną o tej samej nazwie w tabeli studios. Obie kolumny majątaki sam typ danych.
Tabela People
Tabela People posiada najwięcej kolumn spośród tabel w naszej bazie danych. W kilku kolumnach, choć przechowywane są dane numeryczne, zadeklarowałem typ pola jako łańcuchowy. Dane w tych polach będą używane w porównaniach, jako łańcuchy znakowe i nie będą na nich wykonywane tradycyjne działania matematyczne. Podobnie jak w innych tablicach, wprowadziłem dodatkową kolumnę ID, aby mogła być używana w kluczach obcych. Dla nazwy aktora, reżysera zdefiniowałem osobne pola na imię i nazwisko oraz poszczególne części adresu. Definicję pokazuje listing 3.17. #80 ----------------------- Listing 3.17. Definicja tabeli People
Przyjrzyjmy się dokładniej zdefiniowanej tabeli. Zauważmy, że tabela jest dostosowana tylko do realiów Stanów Zjednoczonych. W tym miejscu chciałbym usprawiedliwić się przed wszystkimi międzynarodowymi czytelnikami, że wynika to jedynie z chęci uproszczenia naszej przykładowej bazy danych. Gdyby tabela miała gromadzić informacje o ludziach z całego świata, należałoby dodać pole określające państwo, z którego pochodzi osoba oraz umożliwić wartości null dla pola person_ss n (numer ubezpieczenia). Dla większości pól tej tabeli dozwolone są wartości null. Jednak musi być wpisane nazwisko (person_fname, imię (person_lname, identyfikator (person_id) i numer ubezpieczenia (person_snn). Może to powodować problemy przy próbie wprowadzenia do tabeli Cher czy Prince'a. Pola person_zip, person_phone i per-son_snn mają ustaloną długość, ponieważ każde z tych danych podlega standardowej strukturze. Numer telefonu to 3 cyfry numeru kierunkowego, 3 cyfry zmiennych i 4 cyfry rozszerzenia. Dopuszczenie tylko 10 cyfr zapobiega dodatkowemu formatowaniu numeru, co zwykle prowadzi tylko do zamieszania. W przypadku numeru ubezpieczenia, który składa się z 9 cyfr wprowadzono dodatkowe ograniczenie klauzulą UNIQUE. Ostatnie pole określa, czy wybrana osoba jest członkiem związku zawodowego aktorów. W tym wypadku przyjęto domyślną wartość 'y' zakładając, że większość aktorów należy do tego związku. W bazie SQL Server można dla tego pola przyjąć typ danych bit, który określa czy wartość jest aktywna czy nie.
Tabela Cast_Movies
Cast_Movies (obsada filmów) jest tabelą łączącą tabele Movies i People. Klucz główny tej tabeli składa się z kluczy obcych tabel Movies i People. Gromadzone są w niej między innymi informacje na temat ról granych przez aktorów w filmach oraz wynagrodzenia za poszczególne role. W kolumnie role wpisywana jest nazwa roli odgrywanej przez aktora w filmie, a w kolumnie payment wpisywana jest gaża aktora za rolę. Listing 3.18 zawiera definicję tabeli Cast_Movies.\ #81 ----------------------- Listing 3.18. Definicja tabeli Cast_Movies
CREATE TABLE Cast_Movies (movie_id NUMBER REFERENCES Movies (raovie_id), person_id NUMBER REFERENCES People (person_id), role VARCHAR2(20), payment NUMBER, PRIMARY KEY (movie_id, person_id), CONSTRAINT rainimum_pay CHECK (payment>500))
Table created -----------------------
Relacje są określone na poziomie definicji kolumny dla pól movie_id oraz person_id. Klucz główny dwukolumnowy zdefiniowany jest na poziomie definicji tabeli. Pojawia się jedno ograniczenie, zapobiegające wprowadzeniu wynagrodzenia poniżej minimum określonego przez związek zawodowy aktorów.
Tabela Locations
Ta tabela zawiera spis plenerów, wykorzystanych podczas kręcenia filmów. Składa się z trzech kolumn, które wszystkie stanowią klucz główny. Podobnie jak poprzednia tabela, również ta jest zaprojektowana z myślą o gromadzeniu danych tylko z obszaru Stanów Zjednoczonych. ----------------------- Listing 3.19. Definicja tabeli Locations
CREATE TABLE Locations (movie_id NUMBER REFERENCES Movies (movie_id), city VARCHAR2 (20), state CHAR12), PRIMARY KEY (movie_id, city, state))
Table created -----------------------
Indeksy
Wydajność jest jednym z podstawowych parametrów branych pod uwagę podczas projektowania bazy danych. Jak pokazałem w poprzednim rozdziale dotyczącym normalizacji, problem wydajności stoi w sprzeczności z innymi ważnymi kryteriami projektowania bazy danych. Indeksy umożliwiają uzyskanie większej wydajności podczas przeszukiwania bazy danych za cenę zmniejszonej prędkości podczas wprowadzania i aktualizacji danych. Idea indeksu w bazie danych może być porównana do roli indeksów w książce. Jest to posortowana lista danych z tabeli ze wskaźnikami do miejsc, gdzie te dane się znajdują. #82 Podczas wykonywania zapytania, w którym w klauzuli WHERE znajduje się odwołanie do pola indeksowanego, następuje przeszukiwanie listy indeksów i na podstawie znalezionej tam informacji skok do odpowiedniego miejsca w tabeli. Indeksy są przechowywane w strukturach nazywanych B-drzewo (ang. B-tree), które umożliwiają szybki dostęp do poszukiwanej informacji. Stworzenie na kolumnie indeksu powoduje, że dane z niej są przechowywane w strukturze B-drzewa. Podczas operacji dopisania danych lub aktualizacji względnie usuwania równocześnie następuje aktualizacja indeksu. W związku z tym wolniej odbywają się te operacje na polach indeksowanych. Instrukcja tworzenia indeksu wygląda następująco :
CREATE [UNIQUE] INDEX nazwa_indeksu ON tablica (kolumna)
Na przykład :
CREATE INDEX movle_title_index ON Movles (movle_title)
Po zdefiniowaniu indeksu, za każdym odwołaniem się do kolumny movie_title w klauzuli WHERE tytuł filmu będzie wyszukiwany na liście indeksów i następnie odnajdywany w tabeli. Można również definiować indeksy wielokolumnowe. Takie indeksy, jeśli się pojawiają w klauzuli WHERE, znacząco przyspieszają wykonywanie zapytań. Poniżej zaprezentowano indeks wielokolumnowy :
CREATE INDEK imię_nazwisko ON People (person_fname, person_lname)
Indeksy unikalne
Możemy zdefiniować dla kolumny ograniczenia zapewniające wprowadzanie tylko unikalnych wartości. Można również utworzyć unikalne indeksy zapewniające, że wprowadzane wartości będą niepowtarzalne. Utworzenie indeksu unikalnego dla kolumn movie_title i studio_id z tabeli Movies wymaga użycia następującej instrukcji SQL:
CREATE UNIQUE INDEX film_index ON Movies (movie_tltle, studio_id)
Podczas definicji indeksu na polu, w którym są już zgromadzone dane następuje sprawdzenie danych przed utworzeniem indeksu. Każda zmiana lub dopisanie nowych danych uruchamia procedurę sprawdzania zgodności z istniejącymi w bazie wartościami i w przypadku próby duplikacji przerwanie takiej operacji.
Indeks klastrowy
Indeks klastrowy powoduje zmianę uporządkowania tabeli w taki sposób, że tabela i indeks są uporządkowane w taki sam sposób. Za każdym razem, gdy do tabeli jest dopisywany nowy rekord, zmieniany jest porządek wierszy w tabeli. #83 Indeks klastrowy może znacznie spowolnić operacje modyfikacji tablicy właśnie z powodu, że dodatkowo ustawiany jest nowy porządek wierszy w tabeli. Z drugiej strony indeks klastrowy zapewnia znacznie szybsze wykonywanie zapytań. Dla tabeli może być utworzony tylko jeden indeks klastrowy, ponieważ wiersze w tabeli są porządkowane według tego indeksu.
Powody do indeksowania kolumn
Jest wiele kryteriów decydujących o wyborze kolumny na indeks. Poniższe zestawienie zawiera listę cech, które powinny spełniać dane, aby warto je było poindeksować: * W kolumnie znajduje się wiele różnych wartości. Jeśli w kolumnie tabeli znajduje się, np. 300 pozycji, które przyjmują tylko 3 różne wartości, to użycie indeksu jest mało przydatne. Idealnymi kandydatami na indeks jest pole movie_title z tabeli Movies oraz ostatnie dwa pola z tabeli Peo-ple, ponieważ zawieraj ą różne wartości dla każdego rekordu. * Pole jest często używane w zapytaniach. Im więcej kolumn indeksowanych pojawia się w klauzuli WHERE zapytania, tym lepsza jest jego wydajność. W przypadku pól, które nigdy nie występują w kryteriach zapytań, nie ma sensu tworzyć dla nich indeksu. * Często wykorzystuje się pole w operacjach złączenia. Łączenie tabel poprzez pola indeksowane znacznie przyspiesza tę operację. Wartości w polach indeksowanych są uporządkowane, więc można znacznie szybciej dopasować poszczególne wartości pochodzące z kolumn poindeksowanych.
Kiedy nie używać indeksów
Wcale nie jest prawdziwy pogląd, że skoro indeksy przyspieszają wykonywanie zapytań, to warto indeksować wszystkie pola w tabeli. Rozważając powody skłaniające do indeksowania, można znaleźć też powody przeciwko tworzeniu indeksów. Nie indeksujemy kolumn, które przyjmują tylko kilka różnych wartości lub nie będą używane podczas tworzenia zapytań. Również nie zaleca się zakładania indeksów dla tabel, które często są modyfikowane, a rzadko biorą udział w zapytaniach. Korzyści nie przynosi także zakładanie indeksów dla małych tabel. Ponadto nie zakłada się wielu indeksów w tabeli ze względu na zajęcie dodatkowej pamięci. Trudno podać ogólne reguły dotyczące stosowania indeksów, ponieważ ich wykorzystanie zależy od wbudowanego w każdej bazie danych optymalizatora zapytań, a te różnią się znacznie między sobą. Najlepszym rozwiązaniem jest stosowanie indeksów, gdy zapytania są powolne. Jeśli założenie indeksu wpłynęło na zwiększenie prędkości, indeks należy zachować, w przeciwnym wypadku usunąć. #84 W praktyce
Jeśli stworzyłeś tabele naszej przykładowej bazy danych w kolejności opisanej w książce, prawdopodobnie nie wszystko się udało. Spowodowane to jest tym, że niektóre kolumny tabeli Movies odnosiły się do tabel, które w momencie tworzenia odwołania jeszcze nie istniały. W przypadku użycia klauzuli REFERENCES tabele People i studios muszą być zdefiniowane najpierw. Najlepszym rozwiązaniem w praktyce jest definiowanie ograniczeń i relacji po wstępnym zdefiniowaniu wszystkich tabel. Podobnie w przypadku wprowadzenia ograniczeń do tabeli, może się okazać kłopotliwa zmiana struktury tabel, ponieważ mechanizmy zachowania integralności zapobiegaj ą takim operacjom. Można te ograniczenia obejść dwoma drogami. Pierwsza polega na wyłączeniu tymczasowo ograniczeń, druga na użyciu instrukcji ALTER TABLE w celu usunięcia ograniczeń w definicji. Innym powodem usuwania ograniczeń jest ich wpływ na wydajność bazy danych. Łączenie tabel przy pomocy klauzuli REFERENCES powoduje, że każda modyfikacja danych jest sprawdzana pod kątem zgodności z wprowadzonymi ograniczeniami. W przypadku dużych tabel, w których przeprowadzana jest duża liczba transakcji, spowolnienie spowodowane klauzulą REFERENCES może mieć bardzo znaczący wpływ na wydajność. W systemie Oracle można te ograniczenia wyłączyć przy pomocy klauzuli DISABLE w instrukcji ALTER TABLE. Jak wiadomo ograniczenia można nazywać wewnątrz instrukcji CREATE. Znając nazwę ograniczenia można je wyłączyć w następujący sposób:
ALTER TABLE nazwa_tabeli DISABLE_CONSTRAINT nazwa_ograniczenia
W systemie Oracle w przypadku, gdy nie pamiętamy nazwy ograniczenia, można ją znaleźć w tabeli systemowej User_Constraints. Dodanie ograniczeń po zdefiniowaniu tabeli jest możliwe przy pomocy instrukcji ALTER TABLE z klauzulą ADD. Na przykład, aby dodać ograniczenie zapobiegające wprowadzeniu do kolumny budget z tabeli Movies wartości poniżej 5 użyjemy następującej instrukcji:
ALTER TABLE Movies ADD CONSTRAINT kwota CHECK (budget >= 5)
Aby dodanie ograniczenia do istniejącej tabeli powiodło się, wszystkie dane przechowywane w tej tabeli muszą spełniać kryteria narzucane przez dodawane ograniczenie. Większość baz danych dostarcza graficznych narzędzi do tworzenia tabel i powiązań między nimi, co znacznie upraszcza tworzenie i zarządzanie strukturą bazy danych.