Transcript SQL – JOD (DDL) - Warszawska Wyższa Szkoła Informatyki

SQL – JDD (DDL)
Język definicji danych
(Data Definition/Description
Language)
Elementy bazy danych
Tabele, perspektywy
 Dziedziny, typy
 Indeksy, sekwencje
 Schematy, użytkownicy, prawa
 Wyzwalacze i asercje
 Procedury użytkownika
 Katalog danych (data dictionary)

26 marzec 2007
SQL - język definicji danych
2
Tabela

CREATE {TEMPORARY|TEMP} TABLE
nazwa tabeli
(kolumna1 dziedzina1 [DEFAULT wartosc_domyslna1]
[wiezy_kolumny_1],
kolumna2 dziedzina2 [DEFAULT wartosc_domyslna2]
[więzy_kolumny_2],...
[więzy_tabeli],... )
26 marzec 2007
SQL - język definicji danych
3
Dziedziny standardowe

Logiczna: bool, boolean (TRUE, FALSE, UNKNOWN);

Znakowe: char, 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],
interval p to k

Duze obiekty: text lub clob (varchar), blob.

Liczniki: SERIAL (AUTO_INCREMENT), CREATE SEQUENCE

Przekształcanie typu: CAST(wartosc AS typ), wartosc::typ,
typ wartosc,
26 marzec 2007
SQL - język definicji danych
4
Więzy kolumny

[CONSTRAINT nazwa więzu]
NOT NULL | NULL |
DEFAULT wartość domyślna |
PRIMARY KEY |
UNIQUE |
CHECK (warunek) |
REFERENCES tabela [(kolumna)]
[akcja referencyjna]
[integralność referencyjna]
[DEFERRABLE | IMMEDIATE]
[INITIALLY IMMEDIATE | INITIALLY DEFERRED]
26 marzec 2007
SQL - język definicji danych
5
Akcja referencyjna

ON { DELETE | UPDATE }
{ RESTRICT |
NO ACTION |
CASCADE |
SET NULL |
SET DEFAULT }
26 marzec 2007
SQL - język definicji danych
6
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.
26 marzec 2007
SQL - język definicji danych
7
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]
26 marzec 2007
SQL - język definicji danych
8
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).
SET CONSTRAINTS {ALL|nazwa,…}
{DEFERRED | IMMEDIATE}
26 marzec 2007
SQL - język definicji danych
9
Dziedzina użytkownika

CREATE DOMAIN <nazwa> AS <dziedzina_zdefiniowana>
[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>|
LIKE <wzorzec>|
IN (SELECT…)
})
26 marzec 2007
SQL - język definicji danych
10
26 marzec 2007
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]
26 marzec 2007
SQL - język definicji danych
12
Perspektywa - implementacja

Obliczanie wartości na żądanie – znaczenie

Perspektywa materializowana – zapytanie
analogiczne jak wyrażenie tabelowe w klauzuli FROM
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
26 marzec 2007
SQL - język definicji danych
13
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.
26 marzec 2007
SQL - język definicji danych
14
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
26 marzec 2007
SQL - język definicji danych
15
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.
26 marzec 2007
SQL - język definicji danych
16
Indeksy

CREATE [UNIQUE] INDEX
<nazwa indeksu> ON
<nazwa tabeli> (<lista atrybutów>)
[USING {
HASH |
BTREE |
RTREE }]
26 marzec 2007
SQL - język definicji danych
17
Sekwencje

CREATE SEQUENCE <nazwa>
[INCREMENT <przyrost>]
[START <wartoscPocz>]
[MINVALUE <wartoscMin>]
[MAXVALUE <wartoscMaks>]
[CYCLE|NOCYCLE] [ORDER|NOORDER] …

Zastosowanie:
• nextval(‘sekw1’);
• setval(‘sekw1’,wartosc);
26 marzec 2007
SQL - język definicji danych
18
Zmiany w schemacie BD

DROP –

ALTER –
usunięcie danego obiektu (DOMAIN, TABLE,
VIEW, INDEX, SEQUENCE) z opcją RESTRICT (domyślnie)
lub CASCADE;
usunięcie z opcją RESTRICT nie powiedzie się, jeśli istnieją
obiekty zależne od danego; z opcją CASCADE usuwamy
wszystkie obiekty zależne (kasujemy perspektywy,
likwidujemy więzy klucza obcego odwołujące się do
likwidowanej tabeli,…)
modyfikacja definicji; możliwe operacje, to:
• [DROP | ADD ] COLUMN]
• [DROP | ADD ] CONSTRAINT
• [DROP | SET ] [DEFAULT...][CHECK...]
26 marzec 2007
SQL - język definicji danych
19
Schematy i użytkownicy





CREATE USER nazwa [CREATEDB | CREATEUSER
| IN GROUP nazwaGrupy ]
CREATE GROUP nazwaGrupy [opcje-jak-przyuzytkowniku]
CREATE ROLE nazwaRoli [opcje-jak-przyuzytkowniku]
CREATE SCHEMA [nazwa] [AUTHORIZATION
uzytkownik]
Ogólna nazwa obiektu b.d.:schemat.obiekt
26 marzec 2007
SQL - język definicji danych
20
Prawa
Dowolny SZBD powinien zapewniać również
poufność naszych danych oraz możliwość
regulowania oraz limitowania dostępu do danych.
Dlatego w SQL-u zdefiniowano tzw. prawa, które
właściciel obiektu może dać (GRANT) lub odebrać
(REVOKE) innym użytkownikom. Istnieje też
możliwość przekazania otrzymanych wcześniej
praw (GRANT OPTION).
UWAGA: użytkownik ma pełne prawa do obiektów,
które utworzył; tych praw nie można mu odebrać
(sam też nie może się pozbyć prawa usuwania i
modyfikowania swoich obiektów).
26 marzec 2007
SQL - język definicji danych
21
Składnia do nadawania praw
GRANT prawa ON element-bazy-danych TO listaużytkowników [WITH GRANT OPTION]
Prawa, które można nadać to:




dla tabel, perspektyw: SELECT, INSERT, DELETE, UPDATE,
REFERENCES;
dla indeksów, dziedzin, sekwencji, funkcji: USAGE, UPDATE,
EXECUTE;
REFERENCES dotyczy możliwości odwołania się do danej struktury
w więzach integralności (np. asercje,więzy integralności
referencyjnej); USAGE odnosi się do dziedzin i elementów
schematu innych niż relacje; użytkownik definiujący perspektywę
musi mieć prawo SELECT do tabel, do których odwołuje się w
perspektywie;
można także nadać wszystkie prawa (ALL [PRIVILEGES]) lub
prawa wszystkim użytkownikom (PUBLIC).
26 marzec 2007
SQL - język definicji danych
22
Przykłady nadawania praw
root: GRANT REFERENCES(ido), SELECT ON osoba TO
mariusz; - potrzebne by mariusz mógł stworzyć poniższe:
perspektywę (SELECT) i tabelę (REFERENCES)
mariusz: CREATE VIEW absolwenci AS
SELECT * FROM osoba WHERE kato=‘abs’;
mariusz: CREATE TABLE moi_koledzy
(ident INT REFERENCES osoba,
adres VARCHAR, tel VARCHAR(20));
root: GRANT SELECT, INSERT ON osoba TO mariusz
WITH GRANT OPTION;
mariusz: GRANT SELECT ON absolwenci TO PUBLIC;
root: GRANT USAGE ON DOMAIN imiona TO jacek, placek
WITH GRANT OPTION;
26 marzec 2007
SQL - język definicji danych
23
Składnia REVOKE


Odbieranie nadanych wcześniej praw
(innych niż GRANT)
REVOKE prawa ON element-bazy-danych
FROM lista-użytkowników
[CASCADE|RESTRICT];
Odbieranie prawa GRANT
REVOKE GRANT OPTION FOR prawa ON
element-bazy-danych FROM listaużytkowników
[CASCADE|RESTRICT]
26 marzec 2007
SQL - język definicji danych
24
REVOKE




Podobne jak w przypadku GRANT w zdaniu REVOKE mogą
wystąpić ALL PRIVILEGES oraz PUBLIC.
Prawa są „autoryzowane” - jeśli ktoś otrzymał takie samo prawo
od wielu użytkowników to odebranie praw przez jednego
użytkownika nie powoduje odebrania tego prawa przyznanego
przez innych użytkowników (czyli dopiero, gdy wszystkie osoby
odbiorą to prawo zostanie ono utracone)
Jeśli użytkownik przekazał prawo, które uzyskał z klauzulą GRANT
OPTION, to odebranie mu tej opcji powoduje, że prawo, które
przekazał innym, jest im odbierane.
Prawo trzeba „odbierać” tą samą drogą, jaką było nadane – jeśli A
nadał prawo X użytkownikowi B WITH GRANT OPTION, a B nadał
to prawo C, to A nie może odebrać prawa bezpośrednio C
(REVOKE X FROM C) – musi odebrać „GRANT OPTION”
użytkownikowi B (z klauzulą CASCADE), a to spowoduje odebranie
prawa X użytkownikowi C.
26 marzec 2007
SQL - język definicji danych
25
REVOKE
root:GRANT INSERT ON osoba TO mariusz WITH GRANT OPTION;
mariusz: GRANT INSERT ON osoba TO janusz;
root: REVOKE INSERT ON osoba FROM janusz; -> janusz ma nadal prawo, bo
nadał je mariusz
root: GRANT SELECT ON przedmiot TO mariusz, janusz WITH GRANT
OPTION;
mariusz: GRANT SELECT ON przedmiot TO janusz;
root: REVOKE SELECT ON przedmiot FROM janusz; -> janusz ma nadal prawo
SELECT – to od mariusza
root: REVOKE GRANT OPTION FOR SELECT ON przedmiot FROM mariusz
[CASCADE]; -> janusz traci prawo przekazania prawa SELECT – próba
odebrania tego prawa bez CASCADE spowoduje ostrzeżenie (ale nie
powiedzie się), z opcją CASCADE prawo zostanie odebrane;
mariusz: REVOKE SELECT ON przedmiot FROM janusz; -> janusz nie ma już
prawa SELECT;
26 marzec 2007
SQL - język definicji danych
26
Więzy ogólne, czyli asercje
Dotychczas poznane więzy dotyczyły pojedynczej
krotki (CHECK dla kolumny i/lub tabeli), klucza
głównego (PRIMARY KEY) i alternatywnego
(UNIQUE, NOT NULL) oraz ewentualnie prostych
związków między krotkami z różnych relacji
(FOREIGN KEY…REFERENCES). Czasami
chcielibyśmy zapewnić przestrzeganie warunku,
który dotyczy całej relacji lub paru relacji.
Próba zapisania takiego warunku w definicji jednej
z relacji może nie dać zamierzonego efektu –
warunek taki jest sprawdzany przy okazji operacji
na tej relacji, a nie na innych.
26 marzec 2007
SQL - język definicji danych
27
Składnia asercji
CREATE ASSERTION nazwa_asercji CHECK (warunek)
gdzie warunek musi być typu logicznego, np.


NOT EXISTS (SELECT * FROM zapis WHERE ido IN (SELECT ido FROM
osoba WHERE kato=‘abs’)
3< ALL(SELECT COUNT(*) FROM (((przedmiot NATURAL JOIN edycja)
JOIN sem ON ids=semestr) JOIN kategoria ON (idk=typ))
WHERE sem.nazwa LIKE ‘%letni 2006%’ AND kategoria.nazwa=‘kurs’)
Inne przykłady warunków:

do żadnej grupy nie może być zapisanych więcej studentów, niż liczba
podana w polu limit;

wykład obowiązkowy musi prowadzić profesor (moglibyśmy go
weryfikować, gdybyśmy mieli w bazie tytuły);

aby zapisać się na przedmiot X, trzeba mieć wcześniejszy wpis na
przedmiot Y;
26 marzec 2007
SQL - język definicji danych
28
Wyzwalacze (TRIGGERS)
Czasami chcielibyśmy reagować w bardziej aktywny sposób na
sytuacje, gdy dochodzi do naruszenia jakiegoś warunku lub
niezmiennika niż tylko nie dopuszczając do tych modyfikacji
bazy danych, która ten warunek narusza (być może dla
pewnych sytuacji umiemy temu zaradzić). Poza tym
czasami to my chcielibyśmy decydować, kiedy warunek ma
być sprawdzany (choćby ze względu na wydajność), a w
przypadku np. asercji to system o tym decyduje.
Poza tym asercje są „drogie”, więc w praktyce nie istnieją.
Dlatego firmy zaoferowały coś „tańszego” od asercji, co
pozwala nam na kontrolowanie poprawności danych w bazie
w pewnych sytuacjach.
26 marzec 2007
SQL - język definicji danych
29
Cechy wyzwalaczy





Wyzwalacze (potrzeba ich użycia) są testowane tylko przy zajściu
zdarzenia (wstawienie, usunięcie, modyfikacja krotki) określonego przez
programistę (projektanta bazy) (w przypadku asercji i więzów krotkowych
decyduje o tym SZBD).
Warunek jest testowany w chwili zajścia zdarzenia.
Jeśli zachodzi warunek opisany w wyzwalaczu, to wykonywana jest akcja
związania z wyzwalaczem, której zadaniem jest przygotowanie bazy do
zmian wywołanych zdarzeniem lub uzupełnienie tych zmian tak, by baza
po nich pozostawała w poprawnym stanie.
W akcji wyzwalacza mogą być zawarte zdarzenia, z którymi są związane
ich własne wyzwalacze. Są one uruchamiane zarówno w trakcie
bezpośredniego wywołania zdarzenia w SQL, jak i wówczas, gdy
wydarzenie to zostało wywołane np. przez inny wyzwalacz.
Zdarzenie i akcja wyzwalacza stanowią jedną transakcję – gdy coś się nie
powiedzie w trakcie akcji wyzwalacza, to wycofywana jest cała operacja
(aż do początku całej transakcji, czyli zdarzenia, które jako pierwsze
uruchomiło wyzwalacz).
26 marzec 2007
SQL - język definicji danych
30
Składnia wyzwalaczy
CREATE TRIGGER nazwa_wyzwalacza
{BEFORE|INSTEAD OF |AFTER}
{UPDATE [OF nazwa_kolumny] | INSERT | DELETE} ON
nazwa_tabeli
REFERENCING
{OLD|OLD TABLE} AS nazwa_zmiennej1
{NEW|NEW TABLE} AS nazwa_zmiennej2
WHEN (warunek)
lista_poleceń_SQL_do_wykonania //akcja
[FOR [EACH] {ROW|STATEMENT}]
UWAGA: W miejscu klauzul „REFERENCING… WHEN... lista_poleceń”
może wystąpić wywołanie procedury „EXECUTE PROCEDURE”, w
której wykonywane są analogiczne operacje.
26 marzec 2007
SQL - język definicji danych
31
Cechy wyzwalaczy



Akcja może być wykonana przed (BEFORE), po (AFTER) lub
zamiast (INSTEAD OF) zdarzenia
Można określać czy akcja ma być wykonywana dla każdej
modyfikowanej krotki (FOR EACH ROW) czy tylko raz dla
wszystkich krotek zmodyfikowanych w pojedynczej operacji
(FOR EACH SENTENCE, czyli domyślnie);
W akcji dostępne są wartości sprzed (OLD, OLD TABLE)
zajścia zdarzenia jak i nowe (NEW,NEW TABLE) wartości;
• OLD i OLD TABLE mają sens dla DELETE i UPDATE, a NEW i
NEW TABLE dla UPDATE i INSERT;
• OLD TABLE i NEW TABLE mają sens dla FOR EACH SENTENCE,
a OLD i NEW dla FOR EACH ROW.

Akcja jest wykonywana tylko wtedy, gdy spełniony jest
warunek z klauzuli WHEN.
26 marzec 2007
SQL - język definicji danych
32
Przykład wyzwalacza
Uniemożliwienie zapisu ponad limit do
grupy:
CREATE TRIGGER PilnujLimituPrzyZapisie
AFTER INSERT ON zapis
REFERENCING NEW AS nowa
WHEN ((SELECT limit FROM grupa WHERE
idg=nowa.idg)<(SELECT COUNT(*) FROM zapis
WHERE idg=nowa.idg))
DELETE FROM zapis WHERE idg=nowa.idg AND
ido=nowa.ido
FOR EACH ROW
26 marzec 2007
SQL - język definicji danych
33
Inny przykład wyzwalacza
Uniemożliwienie zapisu ponad limit do grupy (cd.) – konieczne
jest także zareagowanie, gdy ktoś zechce obniżyć limit
poniżej liczby aktualnie zapisanych w grupie osób:
CREATE TRIGGER PilnujLimituPrzyGrupie
AFTER UPDATE OF limit ON grupa
REFERENCING NEW AS nowa
REFERENCING OLD AS stara
WHEN (stara.limit>nowa.limit AND EXISTS
(SELECT grupa.idg FROM zapis NATURAL JOIN grupa
GROUP BY grupa.idg HAVING COUNT(*)>limit)
UPDATE grupa SET limit=stara.limit WHERE idg=stara.idg
FOR EACH ROW
26 marzec 2007
SQL - język definicji danych
34
26 marzec 2007
SQL - język definicji danych
35