1 Język definicji danych (Data Definition Language)SQL – JDD (DDL) Język definicji danych (Data Definition Language)

2 SQL - język definicji danychElementy 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 Obiekty wywodzące się ze standardu: dziedziny, tabele, perspektywy, asercje; Obiekty standardu „wymuszone” przez praktykę: indeksy, użytkownicy, role i uprawnienia, zbiory znaków, zestawienia i translacje; Obiekty wywodzące się z praktyki: typy, baza danych, schemat , katalog, wyzwalacze i procedury; 15 marzec 2005 SQL - język definicji danych

3 Dziedziny standardowaLogiczna (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żytkownikaCREATE DOMAIN AS [DEFAULT ][] ::= | NULL | ::= [CONSTRAINT ] CHECK ( VALUE { IN (v1,v2,...,vk) | IS NOT NULL | | BETWEEN AND }) Problemy: - możliwość zmiany dziedziny w trakcie działania i związana z tym kontrola poprawności dziedzin; - warunek nałożony na wartości z dziedziny nie może powodować problemów z „dynamiczną” kontrolą poprawności, czyli nie powinien zawierać odwołań do funkcji, zawartości tabel (?); - określanie zgodności dziedzin i możliwości porównywania elementów różnych dziedzin (np. porównanie nazwiska i adresu). TYP można uznać za „techniczny” odpowiednik dziedziny, ale nie logiczny. 15 marzec 2005 SQL - język definicji danych

5 SQL - język definicji danychTabela CREATE TABLE ( [], [],... [],... ) 15 marzec 2005 SQL - język definicji danych

6 SQL - język definicji danychWięzy kolumny [CONSTRAINT ] NOT NULL | DEFAULT | PRIMARY KEY | UNIQUE | CHECK | REFERENCES [()] [] [] - stała, funkcja typu: USER, CURRENT_DATE,… - powinien zawierać tylko odwołania do stałych, czyli być obliczalny na podstawie sprawdzanej wartości bez odwoływania się do innych elementów b.d. Jego dopuszczalna postać, to: CHECK data>” ” lub CHECK kierunek IN (‘inf’,’mat’) Problem: - co się dzieje, gdy dla krotek już wstawionych warunek przestaje być prawdziwy (niedobra sytuacja, należy jej unikać lub przynajmniej kontrolować, np. jeżeli w pewnym momencie ograniczymy kierunek studiów do „mat” i „inf”, to powinniśmy być pewni, że w bazie nie ma innych wartości. Ograniczenie: Czasami kluczem obcym może być tylko klucz główny w tabeli „obcej” (to, że musi to być jakiś klucz, to oczywiste); 15 marzec 2005 SQL - język definicji danych

7 SQL - język definicji danychAkcja referencyjna ON { DELETE | UPDATE } { RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT } Przykłady: Zaliczenie.przedmiot REFERENCES Przedmiot(nazwa) ON DELETE CASCADE ON UPDATE CASCADE Prowadzący.dziedzina REFERENCES Przedmiot(nazwa) ON DELETE SET NULL ON UPDATE CASCADE (?) Wypożyczenie.nrC REFERENCES Czytelnik(nrC) ON DELETE RESTRICT ON UPDATE CASCADE 15 marzec 2005 SQL - język definicji danych

8 Integralność referencyjnaDomyś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. Przykłady: Indeks,przedmiot -> ocenaKoncowa MATCH FULL i NOT NULL Miasto, Ulica, nrDomu -> kodPocztowy MATCH PARTIAL i wszystko oprócz miasta – nie NOT NULL 15 marzec 2005 SQL - język definicji danych

9 SQL - język definicji danychWięzy tabeli [CONSTRAINT ] PRIMARY KEY () | UNIQUE () | FOREIGN KEY () REFERENCES () [][]| CHECK [DEFERRED | IMMEDIATE] PRIMARY KEY .. REFERENCES – analogicznie jak dla kolumny, ale gdy dotyczą kilku atrybutów; CHECK – może dotyczyć całek krotki, np. stypSoc 15 marzec 2005 SQL - język definicji danych

10 Więzy – moment kontroliSpeł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 Perspektywa (tabela wirtualna)CREATE VIEW [,...] AS [ WITH [CASCADED|LOCAL] CHECK OPTION] Przykłady: CREATE VIEW tytulyDostepne(tytul, autor, sygnatura, [nkr]) AS SELECT tytul, autor sygn FROM ks NATURAL JOIN egz WHERE sygn NOT IN ( SELECT sygn FROM WYP); CREATE VIEW egzWypozyczone AS SELECT * FROM egz WHERE sygn IN (SELECT sygn FROM WYP); Korzystanie z perspektyw: (prawie) jak z normalnych tabel bazowych SELECT tytul FROM tytulyDostepne WHERE autor=‘Prus’; SELECT tytul, autor FROM ks NATURAL JOIN egz WHERE sygn IN (SELECT sygn FROM egzWypozyczone); Zastosowanie perspektyw: - uproszczenie zapytań; - dostosowanie do potrzeb konkretnego użytkownika; - zabezpieczenie danych przed nieuprawnionym użyciem (potem); 15 marzec 2005 SQL - język definicji danych

12 Perspektywa - implementacjaPerspektywa 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. Problem: Może być trudne do wykrycia, które modyfikacje mają wpływa (i jaki) na zawartość perspektywy. 15 marzec 2005 SQL - język definicji danych

13 Perspektywa – implementacjaRozkł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. Przykład: SELECT tytul FROM tytulyDostepne WHERE autor=‘Prus’; => SELECT tytul FROM ks NATURAL JOIN egz WHERE autor=‘Prus’ AND sygn IN (SELECT sygn FROM wyp); Ogólnie: SELECT A,B1 FROM T,V WHERE Ftv; V: SELECT B1,B2 FROM T1 WHERE Tv; Podstawienie: SELECT A,B1 FROM T,T1 WHERE Fty AND Fv; 15 marzec 2005 SQL - język definicji danych

14 Perspektywa - modyfikacjePerspektywa 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. Uwaga: W perspektywie tytulyDostepne należy dodać klucz tabeli ks i ma szanse być modyfikowalna; Perspektywa egzWypożyczone jest modyfikowalna (?) – teoretycznie; 15 marzec 2005 SQL - język definicji danych

15 Perspektywa - modyfikacjePerspektywa 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 Ad. ograniczenie Perspektywa: osoby, które mieszkają w tym samym mieście, co Abacki; Select nazwisko from osoba where miasto in (select miasto from osoba where nazwisko=‘Abacki’); Zmiana miejsca zamieszkania Abackiego, ma daleko idące skutki; Książki, które są w bibliotece w jednym (więcej niż jednym) egzemplarzu; 15 marzec 2005 SQL - język definicji danych

16 Perspektywa – WITH CHECK OPTIONrekord 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. Osoby mieszkające w tym samym mieście co Abacki (with check option) Książki w jednym egzemplarzu (cascaded) Egzemplarze wypożyczone – logiczne jest, by dopisanie egzemplarza do grona wypożyczonych spowodowało jego pojawienie się w tabeli wyp. 15 marzec 2005 SQL - język definicji danych

17 SQL - język definicji danychIndeks CREATE [UNIQUE] INDEX ON () [USING { HASH | BTREE | RTREE }] 15 marzec 2005 SQL - język definicji danych

18 SQL - język definicji danychSekwencje CREATE SEQUENCE [INCREMENT ] [START ] [MINVALUE ][MAXVALUE ][CYCLE|NOCYCLE] [ORDER|NOORDER] … Zastosowanie: nextval(‘sekw1’); setval(‘sekw1’,wartosc); 15 marzec 2005 SQL - język definicji danych

19 SQL - język definicji danychZmiany w schemacie BD DROP – usunięcie danego obiektu (DOMAIN, TABLE, VIEW, INDEX, SEQUENCE) z opcją RESTRICT 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 SQL - język definicji danychWięzy ogólne – asercje CREATE ASSERTION CHECK 15 marzec 2005 SQL - język definicji danych

21 SQL - język definicji danychPrzykł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 SQL - język definicji danychPrzykład cd. CREATE TABLE Osoba ( nazwisko Tnazwa NOT NULL, imię Tnazwa, KM Tpłeć, PESEL CHAR(11) UNIQUE NOT NULL, PRIMARY KEY (nazwisko, imię)); 15 marzec 2005 SQL - język definicji danych

23 SQL - język definicji danychPrzykł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); 15 marzec 2005 SQL - język definicji danych

24 SQL - język definicji danychPrzykład cd.3 CREATE VIEW WW AS SELECT nazwisko, imię FROM Osoba JOIN Samochód ON pesel=właściciel; CREATE VIEW WX AS 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