SQL – JDD (DDL)
Język definicji danych
(Data Definition Language)
SQL – JDD (DDL)
Język definicji danych
(Data Definition Language)
18 marzec 2004
SQL - język definicji danych
2
Elementy bazy danych
Dziedziny
Tabele, perspektywy
Indeksy
Więzy ogólne (asercje)
Wyzwalacze i procedury użytkownika
Użytkownicy, role, uprawnienia
Zbiory znaków, zestawienia, translacje
18 marzec 2004
SQL - język definicji danych
3
Dziedzina standardowa
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.
18 marzec 2004
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> |
IN (<zapytanie SELECT>) })
18 marzec 2004
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 relacji>],...
)
18 marzec 2004
SQL - język definicji danych
6
Więzy kolumny
[CONSTRAINT <nazwa więzu>]
NOT NULL |
DEFAULT <wartość domyślna> |
PRIMARY KEY |
UNIQUE |
REFERENCES <tabela>(<kolumna>)
[<akcja referencyjna>]
[<integralność referencyjna>] |
CHECK <warunek>
18 marzec 2004
SQL - język definicji danych
7
Akcja referencyjna
ON { DELETE | UPDATE }
{ RESTRICT |
NO ACTION |
CASCADE |
SET NULL |
SET DEFAULT }
18 marzec 2004
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.
18 marzec 2004
SQL - język definicji danych
9
Więzy relacji
[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>
18 marzec 2004
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)
18 marzec 2004
SQL - język definicji danych
11
Perspektywa (tabela
wirtualna)
CREATE VIEW
<nazwa perspektywy> [<nazwa
kolumny>,...]
AS
<zapytanie SELECT definiujące
perspektywę>
[ WITH
[CASCADED|LOCAL]
CHECK OPTION]
18 marzec 2004
SQL - język definicji danych
12
Perspektywa -
implementacja
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.
18 marzec 2004
SQL - język definicji danych
13
Perspektywa –
implementacja
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.
18 marzec 2004
SQL - język definicji danych
14
Perspektywa -
modyfikacje
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.
Własność modyfikowalności można
rozciągnąć na perspektywy powstałe z kilku
tabel, o ile w tabeli wynikowej występują
kolumny kluczy i kolumny niepuste z
wszystkich tabel, które są złączane w celu
utworzenia perspektywy.
18 marzec 2004
SQL - język definicji danych
15
Perspektywa -
modyfikacje
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;
w warunku WHERE zapytania definiującego
występuje odwołanie do tabeli występującej
w klauzuli FROM.
18 marzec 2004
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.
18 marzec 2004
SQL - język definicji danych
17
Indeks
CREATE [UNIQUE] INDEX
<nazwa indeksu> ON
<nazwa tabeli> (<lista atrybutów>)
[USING {
HASH |
BTREE |
RTREE }]
18 marzec 2004
SQL - język definicji danych
18
Zmiany w schemacie BD
DROP – usunięcie danego obiektu
(DOMAIN, TABLE, VIEW, INDEX) z
opcją RESTRICT lub CASCADE
ALTER – modyfikacja definicji;
możliwe operacje, to:
– [DROP | ADD ] COLUMN]
– [DROP | ADD ] CONSTRAINT
– [DROP | SET ] [DEFAULT...]
[CHECK...]
18 marzec 2004
SQL - język definicji danych
19
Więzy ogólne – asercje
CREATE ASSERTION <nazwa
więzu>
CHECK <warunek>
18 marzec 2004
SQL - język definicji danych
20
Przykład
CREATE DOMAIN TNazwa AS
VARCHAR(20) DEFAULT ‘???’;
CREATE DOMAIN TPłeć AS
CHAR
DEFAULT ‘?’
CHECK VALUE IN (‘K’, ‘M’);
18 marzec 2004
SQL - język definicji danych
21
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ę));
18 marzec 2004
SQL - język definicji danych
22
Przykład cd.2
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);
18 marzec 2004
SQL - język definicji danych
23
Przykład cd.3
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)