SQL – JOD (DDL)

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