SQL - 3.pdf

(379 KB) Pobierz
7.04.2015
SQL ­ 3
Wykład 3
SQL ­ zaawansowane konstrukcje
 
Streszczenie
Na tym wykładzie kontynuujemy omawianie kolejnych obiektów i związanych z
nimi instrukcji języka SQL. Przedstawiamy więc takie obiekty jak: perspektywy,
synonimy, schematy, dziedziny atrybutów, asercje, tabele tymczasowe,
sekwencje i klastry. Dodatkowo omawiamy problem reprezentowania w bazie
danych metadanych (danych o danych) za pomocą słownika danych (inaczej
nazywanego katalogiem systemowym) oraz podstawowy dla środowiska
współbieżnego wykonywania operacji na bazie danych przez wielu użytkowników
temat transakcji, blokad i poziomów izolacji.
 
3.1 Perspektywy
W sposób oczywisty dobrze byłoby zapamiętywać zapytania w bazie danych, aby
je móc później użyć. Do tego celu służy pojęcie perspektywy. Perspektywa jest
to wirtualna tabela. Jest określona przez zapytanie czyli instrukcję SELECT.
 
CREATE VIEW nazwa_perspektywy
AS zapytanie;
 
Można jej używać tak jakby była tabelą np. w instrukcji SELECT. Wiersze
perspektywy nie są jednak przechowywane w bazie danych ­ mogą być tylko
wyliczone na żądanie.
 
CREATE VIEW Urzędnicy (Empno, Ename, Sal)
AS SELECT Emp.Empno, Emp.Ename, Emp.Sal
   FROM Emp
   WHERE Emp.Job = 'CLERK';
W Standardzie podzapytanie jest ujmowane w nawiasy.
http://edu.pjwstk.edu.pl/wyklady/sbd/scb/w3.htm
1/24
7.04.2015
SQL ­ 3
 
Perspektywy służą do dostosowania bazy danych do potrzeb różnych grup
użytkowników. Stanowią widok, perspektywę, z jakiej dana klasa użytkowników
widzi bazę danych. Różne grupy użytkowników mogą mieć odmienne
"perspektywy" na dane w bazie danych.
 
Skoro perspektywy mają zastępować użytkownikom tabele, muszą także
umożliwiać wykonywanie podobnych operacji, jakie są wykonywane na tabelach,
a więc takich jak przeglądanie i wyszukiwanie danych, wstawianie,
modyfikowanie i usuwanie danych z bazy danych.
Postulat dotyczący przeglądania i wyszukiwania danych jest w pełni spełniony. W
instrukcjach SELECT perspektyw można używać na takich samych zasadach jak
tabel, bez żadnych ograniczeń np.
 
SELECT u.Ename, u.Sal
FROM Urzędnicy u
WHERE u.Sal>5000
ORDER BY u.Ename;
Przedyskutujemy teraz możliwość wykonywania operacji INSERT, DELETE i
UPDATE.
Przykład
Rozpatrzmy perspektywę:
CREATE VIEW Prac_sprzedaży
AS SELECT * FROM Emp
   WHERE Emp.Deptno= 20;
Używając tej perspektywy możemy przeprowadzać modyfikację danych w
odniesieniu do pracowników z działu numer 20, np. możemy podnieść ich zarobki
Sal o 10%:
UPDATE Prac_sprzedaży
SET Sal = Sal * 1.1;
Każdy system baz danych ogranicza zakres perspektyw, przez które można
dokonywać zmian w bazie danych. W odniesieniu do Standardu i systemu Oracle
mamy do czynienia z następującymi ograniczeniami:
http://edu.pjwstk.edu.pl/wyklady/sbd/scb/w3.htm
2/24
7.04.2015
SQL ­ 3
1. w klauzuli SELECT nie może być słowa DISTINCT,
2. w klauzuli FROM może być tylko jedna nazwa tabeli lub jedna nazwa
perspektywy ­ spełniająca definiowane kryteria ­ dotyczy to Standardu, gdyż
Oracle umożliwia użycie więcej niż jednej tabeli,
3. na liście SELECT mogą znajdować się tylko nazwy kolumn,
4. w klauzuli WHERE nie może być podzapytania,
5. w zapytaniu nie mogą występować klauzule GROUP BY i HAVING.
Powyższe warunki zapewniają to, aby jednoznacznie był określony wiersz w
tabeli, którego dotyczy zmiana. W Oracle możliwe jest dokonywanie
ograniczonych zmian w bazie danych poprzez perspektywy zawierające
złączenia. Można mianowicie w Oracle dokonywać zmian po stronie klucza
obcego, ale nie głównego. Przy użyciu perspektywy:
CREATE VIEW Ed
AS SELECT e.Empno, e.Ename, d.Deptno, d.Loc
   FROM Emp e, Dept d
   WHERE e.Deptno = d.Deptno;
możliwe są zmiany (INSERT, DELETE, UPDATE) w odniesieniu do tabeli Emp, ale
nie w odniesieniu do tabeli Dept. (Istotne przy tym jest aby kolumna Empno była
kluczem głównym w tabeli EMP.)
Przykład
Przy użyciu instrukcji:
INSERT INTO Ed(Empno, Ename, Deptno)
VALUES (5600, 'Nowiński',20);
wstawimy pracownika o nazwisku 'Nowiński' do tabeli Emp ustalając jego numer
działu na 20.
Istnieje też oczywiście możliwość usunięcia perspektywy z bazy danych.
Instrukcja
 
DROP VIEW nazwa_perspektywy;
 
powoduje usunięcie perspektywy.
http://edu.pjwstk.edu.pl/wyklady/sbd/scb/w3.htm
3/24
7.04.2015
SQL ­ 3
Tworzenie perspektyw z opcją sprawdzania
W celu zapewnienia ograniczenia danych operowanych przy użyciu perspektywy
do danych określonych przez jej warunek WHERE, użyteczna jest dodatkowa
opcja definicji perspektywy:
 
CREATE VIEW nazwa_perspektywy[(nazwa_kolumny,...)]
AS zapytanie
WITH CHECK OPTION;
 
Jeżeli została użyta opcja WITH CHECK OPTION, to przy wykonywaniu INSERT i
UPDATE następuje sprawdzenie, czy wstawiany bądź modyfikowany wiersz
spełnia warunek określony w klauzuli WHERE. Jeśli spełnia, operacja jest
wykonywana. Jeśli nie spełnia, operacja nie zostanie wykonana.
Gwarantuje to dodatkową ochronę danych zapewniając, że zmiany dokonywane
przez użytkowników przez perspektywę są wyłącznie ograniczone do zbioru
danych, do których oglądania i modyfikowania jest uprawniony dany użytkownik.
Jest to zgodne z ideą dopasowywania poziomu zewnętrznego do danej grupy
użytkowników.
Przykład
Utwórz perspektywę pracowników będących na urlopie bezpłatnym.
CREATE VIEW Emp_na_urlopie_bezpłatnym
AS SELECT * 
   FROM Emp
   WHERE Emp.Sal = 0 OR Emp.Sal IS NULL
WITH CHECK OPTION;
Poprzez tę perspektywę modyfikacja danych jest możliwa tylko w stosunku do
pracowników, których zarobki są równe 0 albo Null, ponieważ tylko tacy
pracownicy są widoczni w tej perspektywie. Same zarobki mogą być jednak
zmienione tylko na 0 albo Null, ponieważ po operacji warunek w klauzuli WHERE
perspektywy też musi być spełniony. Oznacza to w praktyce zablokowanie
zmiany zarobków poprzez tę perspektywę np. nie uda się zmiana wysokości
zarobków wszystkim pracowników na urlopie bezpłatnym:
UPDATE Emp_na_urlopie_bezpłatnym
SET Sal = 10000;
http://edu.pjwstk.edu.pl/wyklady/sbd/scb/w3.htm
4/24
7.04.2015
SQL ­ 3
Tworzenie perspektyw z opcją tylko do odczytu
Kolejnym elementem ochrony danych dostarczanym przez perspektywy jest
opcja tylko do odczytu. Mianowicie można zabezpieczyć się przed dokonywaniem
zmian poprzez perspektywę przez utworzenie jej z opcją WITH READ ONLY.
 
CREATE VIEW nazwa_perspektywy[(nazwa_kolumny,...)]
AS zapytanie
WITH READ ONLY;
 
Na przykład:
CREATE VIEW Pracownicy
AS SELECT * FROM Emp
   WITH READ ONLY;
Każdy uprawniony użytkownik może tylko oglądać dane o pracownikach bez
możliwości wykonania wstawiania, modyfikowania czy usuwania wierszy.
Reasumując, perspektywy są obiektami, jakie udostępnia się konkretnym
grupom użytkowników. Określają one widok na bazę danych zaprojektowany dla
tej grupy użytkowników. Ułatwia to użycie bazy danych, jak również stanowi
element ochrony przed niepowołanym lub nieprawidłowym dostępem do danych.
Każdy użytkownik bazy danych ma dostęp tylko do danych dotyczących jego
działalności w firmie. Ponadto jeśli w aplikacji korzystamy z perspektyw a nie z
tabel to mamy zagwarantowaną niezależność logiczną danych tzn. po zmianie
schematu tabel trzeba tylko ewentualnie zmienić definicje perspektyw a same
aplikacje nie wymagają już zmiany.
Postulat modyfikowalności bazy danych przez perspektywy nie jest w pełni
spełniony przez Standard SQL z 1992 roku. Do pełnej realizacji tego postulatu
potrzebna jest dodatkowa konstrukcja ­ wyzwalacz INSTEAD OF ­ wprowadzona
do Standardu SQL'1999 i omawiana w wykładzie 6.
Perspektywa zmaterializowana
Ze względu na użycie w rozproszonych bazach danych i w hurtowniach danych,
kopię zawartości perspektywy można materializować tzn. zapisywać jako tabelę.
Tego rodzaju perspektywa nosi nazwę perspektywy zmaterializowanej. Jej użycie
http://edu.pjwstk.edu.pl/wyklady/sbd/scb/w3.htm
5/24

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin