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)