SQL – JDD (DDL) Język definicji danych (Data Definition Language) 15 marzec 2005 SQL - język definicji danych 2 Elementy bazy danych Dziedziny, typy Tabele, perspektywy Indeksy, sekwencje Więzy ogólne (asercje) Baza danych, schemat, katalog Wyzwalacze i procedury użytkownika Użytkownicy, role, uprawnienia Zbiory znaków, zestawienia, translacje 15 marzec 2005 SQL - język definicji danych 3 Dziedziny standardowe Logiczna (TRUE, FALSE, UNKNOWN); Znakowe: char(n) lub character(n), varchar(n) lub character varying(n); Bitowe: bit(n), bit varying(n); Liczowe dokładne: int lub integer, shortint, numeric(n,p), decimal(n,p); Liczbowe zmiennopozycyjne: float(p), real, double; Daty i czasu: date, time, timestamp [WITH TIME ZONE] Przedziały czasu: interval p to k; Inne: text lub clob, blob. CAST – przekształcanie typu. 15 marzec 2005 SQL - język definicji danych 4 Dziedzina użytkownika CREATE DOMAIN <NAZWA> AS <dziedzina zdef.> [DEFAULT <wartość domyślna>][<więzy dziedzinowe>] <wartość domyślna>::= <stała> | NULL | <f. bez arg.> <więzy dziedzinowe>::= [CONSTRAINT <nazwa więzu>] CHECK ( VALUE { IN (v1,v2,...,vk) | IS NOT NULL | <operator porównania> <wartość>| BETWEEN <w1> AND <w2> }) 15 marzec 2005 SQL - język definicji danych 5 Tabela CREATE TABLE <nazwa tabeli> ( <kolumna1> <dziedzina1> [<więzy kolumny 1>], <kolumna2> <dziedzina2> [<więzy kolumny 2>],... [<więzy tabeli>],... ) 15 marzec 2005 SQL - język definicji danych 6 Więzy kolumny [CONSTRAINT <nazwa więzu>] NOT NULL | DEFAULT <wartość domyślna> | PRIMARY KEY | UNIQUE | CHECK <warunek> | REFERENCES <tabela> [(<kolumna>)] [<akcja referencyjna>] [<integralność referencyjna>] 15 marzec 2005 SQL - język definicji danych 7 Akcja referencyjna ON { DELETE | UPDATE } { RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT } 15 marzec 2005 SQL - język definicji danych 8 Integralność referencyjna Domyślnie – jeżeli jakaś kolumna klucza obcego jest pusta, to integralność referencyjna nie jest kontrolowana; MATCH FULL – jeśli wszystkie kolumny klucza obcego są puste, to integralność referencyjna nie jest kontrolowana; przeciwnie – musi istnieć odpowiedni rekord w tabeli nadrzędnej; MATCH PARTIAL – rekord podrzędny pasuje do rekordu nadrzędnego, jeśli zgadza się z nim na niepustych polach klucza obcego. 15 marzec 2005 SQL - język definicji danych 9 Więzy tabeli [CONSTRAINT <nazwa więzu>] PRIMARY KEY (<lista kolumn>) | UNIQUE (<lista kolumn>) | FOREIGN KEY (<lista kolumn>) REFERENCES <tabela>(<lista kolumn>) [<akcja referencyjna>][<integralność referencyjna>]| CHECK <warunek> [DEFERRED | IMMEDIATE] 15 marzec 2005 SQL - język definicji danych 10 Więzy – moment kontroli Spełnienie więzu może być kontrolowane natychmiast po wprowadzeniu, aktualizacji rekordu z tabeli (IMMEDIATE); Może być jednak odroczone do momentu wypełnienia transakcji (DEFERRED); Zmiany więzów nie muszą powodować kontroli całej aktualnej zawartości bazy danych pod kątem ich poprawności (naruszenia więzów dla innych rekordów, które zostały wcześniej wstawione do bazy). 15 marzec 2005 SQL - język definicji danych 11 15 marzec 2005 SQL - język definicji danych 12 Perspektywa (tabela wirtualna) CREATE VIEW <nazwa perspektywy> [<nazwa kolumny>,...] AS <zapytanie SELECT definiujące perspektywę> [ WITH [CASCADED|LOCAL] CHECK OPTION] 15 marzec 2005 SQL - język definicji danych 13 Perspektywa - implementacja Obliczanie wartości na żądanie – znaczenie analogiczne jak wyrażenie tabelowe w klauzuli FROM Perspektywa materializowana – zapytanie definiujące perspektywę jest wyliczane i przechowywane jako tabela przez SZBD. Zapytania do perspektywy są realizowane przez wgląd w tę tabelę. Modyfikacje perspektywy mogą być przeprowadzane przez: ponowne obliczenie zapytania; aktualizację tylko tych rekordów, które dotknęła modyfikacja. Rozkład – jest metodą polegającą na przekształceniu zapytania dotyczącego perspektywy tak, by dotyczyło tabel bazowych, a warunki wyboru pochodzące z definicji perspektywy zostały włączone w definicję tabeli 15 marzec 2005 SQL - język definicji danych 14 Perspektywa – modyfikacje danych Perspektywa jest modyfikowalna, gdy dotyczy jednej tabeli i występują w niej wszystkie kolumny niepuste (z nałożonym warunkiem NOT NULL lub PRIMARY KEY) z tej tabeli. Perspektywa może być modyfikowalna, gdy powstała z kilku tabel, ale występują w niej kolumny kluczy i kolumny niepuste z wszystkich tabel, które są złączane w celu utworzenia perspektywy. 15 marzec 2005 SQL - język definicji danych 15 Perspektywa – modyfikacje danych Perspektywa nie może być modyfikowalna, gdy: zawiera operator DISTINCT; zwraca kolumnę wyliczaną; jest definiowana przez zapytanie grupujące; jest definiowana przez zapytanie zawierające UNION, EXCEPT lub INTERSECT; Modyfikacja może być niebezpieczna, gdy w warunku WHERE zapytania definiującego występuje zapytanie zagnieżdżone odwołujące się do tabeli występującej w klauzuli FROM tego zapytania, czyli warunek selekcji w perspektywie jest zależny od aktualnej zawartości tabeli, na bazie której jest zdefiniowana perspektywa 15 marzec 2005 SQL - język definicji danych 16 Perspektywa – WITH CHECK OPTION WITH CHECK OPTION rekord wstawiony poprzez perspektywę musi należeć do perspektywy; rekord zmodyfikowany poprzez perspektywę nadal musi należeć do perspektywy. CASCADED – wstawienie lub modyfikacja rekordu do perspektywy lub perspektywy na jej podstawie zdefiniowanej, nie może spowodować zniknięcia rekordu z danej perspektywy; LOCAL – rekord może zniknąć po modyfikacji z perspektywy, o ile zniknie z tabeli, na podstawie której jest zdefiniowana perspektywa. 15 marzec 2005 SQL - język definicji danych 17 Indeksy CREATE [UNIQUE] INDEX <nazwa indeksu> ON <nazwa tabeli> (<lista atrybutów>) [USING { HASH | BTREE | RTREE }] 15 marzec 2005 SQL - język definicji danych 18 Sekwencje CREATE SEQUENCE <nazwa> [INCREMENT <przyrost>] [START <wartoscPocz>] [MINVALUE <wartoscMin>] [MAXVALUE <wartoscMaks>] [CYCLE|NOCYCLE] [ORDER|NOORDER] … Zastosowanie: nextval(‘sekw1’); setval(‘sekw1’,wartosc); 15 marzec 2005 SQL - język definicji danych 19 Zmiany w schemacie BD DROP – usunięcie danego obiektu (DOMAIN, TABLE, VIEW, INDEX, SEQUENCE) z opcją RESTRICT (domyślnie) lub CASCADE ALTER – modyfikacja definicji; możliwe operacje, to: [DROP | ADD ] COLUMN] [DROP | ADD ] CONSTRAINT [DROP | SET ] [DEFAULT...][CHECK...] 15 marzec 2005 SQL - język definicji danych 20 Więzy ogólne – asercje CREATE ASSERTION <nazwa więzu> CHECK <warunek> 15 marzec 2005 SQL - język definicji danych 21 Przykład CREATE DOMAIN TNazwa AS VARCHAR(20) DEFAULT ‘???’; CREATE DOMAIN TPłeć AS CHAR DEFAULT ‘M’ CHECK VALUE IN (‘K’, ‘M’); CREATE SEQUENCE kolejny; 15 marzec 2005 SQL - język definicji danych 22 Przykład cd. CREATE TABLE Osoba ( nazwisko Tnazwa NOT NULL, imię Tnazwa, KM Tpłeć, PESEL CHAR(11) UNIQUE NOT NULL, PRIMARY KEY (nazwisko, imię)); CREATE TABLE Samochód ( nrRej VARCHAR(10) PRIMARY KEY, marka Tnazwa, właściciel CHAR(11) REFERENCES Osoba(pesel) ON DELETE SET NULL ON UPDATE CASCADE); 15 marzec 2005 SQL - język definicji danych 23 Przykład cd. CREATE VIEW WW AS SELECT nazwisko, imię FROM Osoba JOIN Samochód ON pesel=właściciel; CREATE VIEW WX AS SELECT nazwisko, imię FROM WW WHERE marka IN (‘BMW’,’Opel’); CREATE INDEX samWgWłaściciel ON Samochód(Właściciel) 15 marzec 2005 SQL - język definicji danych 24 15 marzec 2005 SQL - język definicji danych 25