Wyk

ład

5

SQL – język relacyjnych

i obiektowo-relacyjnych

baz danych

Zaawansowane cechy

Perspektywy

• Perspektywa jest zapamiętaną w bazie

danych definicja zapytania do
późniejszego użycia:

• Perspektywa jest “wirtualną” tabelą,

• można ją używać tak jak tabelę,
• jej wiersze nie są przechowywane w

bazie danych,

• mogą być wyliczone na żądanie.

CREATE VIEW Urzędnicy (Empno,
Ename, Sal)
AS SELECT Empno, Ename, Sal
FROM Emp
WHERE Job = 'CLERK';

Perspektywy

• Perspektywy służą do dostosowania bazy

danych do potrzeb różnych grup
użytkowników:

• stanowią “perspektywę” z jakiej dana

klasa użytkowników “widzi” bazę
danych;

• różne grupy użytkowników mogą

mieć odmienne perspektywy na te
same dane
w bazie danych.

• Na perspektywach możliwe jest

wykonywanie instrukcji SELECT,
INSERT, DELETE i UPDATE podobnie
jak na tabelach.

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%:

Przykład

CREATE VIEW Prac_sprzedaży
AS SELECT * FROM Emp
WHERE Deptno= 20;

UPDATE
Prac_sprzedaży
SET Sal = Sal * 1.1;

Ograniczenia w Standardzie

SQL dla

modyfikowalnych perspektyw

•w klauzuli SELECT nie ma DISTINCT,
•w klauzuli FROM jest tylko jedna nazwa tabeli

lub jedna
nazwa perspektywy - spełniająca definiowane
kryteria,

•na liście SELECT są tylko nazwy kolumn,
•w klauzuli WHERE nie ma podzapytania,
•nie ma klauzul GROUP BY i HAVING.

Powyższe punkty stanowią warunek
dostateczny na to,
aby jednoznacznie był określony wiersz w
tabeli,
której dotyczy zmiana.

Modyfikowalne perspektywy

zawierające złączenia (Oracle)

W Oracle można dokonywać zmian tylko po
stronie klucza obcego, np. w tabeli Emp ale nie
Dept:  

CREATE VIEW PracD AS
SELECT Empno, Ename, p.Deptno, Dname,
Loc
FROM Emp p, Dept d
WHERE p.Deptno= d.Deptno;

 
INSERT INTO PracD(Empno, Ename,
Deptno)
VALUES (5600, 'Nowiński',20);

wstawia pracownika “Nowiński” do tabeli Emp,
ustalając
jego numer działu na 20 (Dname i Loc są
nieistotne
przy wstawianiu).

Tworzenie perspektywy z opcją

sprawdzania

Opcja

WITH CHECK OPTION

powoduje, że przy

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

.

CREATE VIEW
nazwa_perspektywy[(nazwa_kolumny,...)]
AS podzapytanie
WITH CHECK OPTION;

Poprzez tę perspektywę nie uda się zmiana
wysokości zarobków pracownika o nazwisku
‘Kowalski’, jeśli jego Zarobki są równe

0

lub

Null

.

Przykład

CREATE VIEW
Emp_na_urlopie_bezpłatnym AS
SELECT *
FROM Emp
WHERE Sal = 0 OR Sal IS NULL
WITH CHECK OPTION;

UPDATE
Emp_na_urlopie_bezpłatnym
SET Sal = 10000
WHERE Ename = 'KOWALSKI';

Perspektywa pracowników będących na urlopie
bezpłatnym

Perspektywy tylko do odczytu

Zabezpieczenie przed dokonywaniem zmian przez

perspektywę.

 

CREATE VIEW Pracownicy AS
SELECT * FROM Emp
WITH READ ONLY;

 

Każdy uprawniony użytkownik:

• może oglądać dane o pracownikach,
• nie może wykonywać wstawiania, modyfikowania

ani usuwania wierszy.

Podsumowanie perspektyw

• Perspektywy udostępnia się konkretnym grupom

użytkowników.

• Określają widok na bazę danych zaprojektowany

dla tej grupy użytkowników.

• Ułatwia to użycie danych.
• 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.

Tworzenie synonimów nazw

tabel i perspektyw

W przypadku długich identyfikatorów obiektów (np.
specyfikacje sieciowe) – własne synonimy.

 

CREATE SYNONYM

nazwa_synonimu

FOR

nazwa_tabeli_lub_perspektywy

;

 

CREATE SYNONYM Dept
FOR [e-mail];
 

DROP SYNONYM

nazwa_synonimu

;

Transakcje

• Często elementarną operacją na bazie danych nie

jest wcale pojedyncza instrukcja SQL, ale ciąg
takich instrukcji, nazywany transakcją.

• Np. przelanie pieniędzy z jednego konta na drugie

jest elementarną operacją z punktu widzenia
aplikacji bankowej.

• W SQL używamy w tym celu co najmniej dwóch

instrukcji UPDATE:

UPDATE Konta SET Saldo = Saldo – 1000 WHERE Id_klienta

= 1001;

UPDATE Konta SET Saldo = Saldo +1000 WHERE Id_klienta

= 9999;

Transakcje

UPDATE Konta SET Saldo = Saldo – 1000 WHERE Id_klienta

= 1001;

UPDATE Konta SET Saldo = Saldo +1000 WHERE Id_klienta

= 9999;

• Załóżmy, że pierwsza instrukcja wykonała się, a

druga
nie może zostać wykonana na przykład z powodu
tego,
że 9999 jest błędnym identyfikatorem klienta albo
z powodu awarii komputera. Z punktu widzenia
aplikacji dane znalazły się w stanie niespójnym i
pozostaje tylko jedna możliwość -
wycofać wynik pierwszej instrukcji UPDATE.
Do tego celu służy instrukcja

ROLLBACK

.

•

COMMIT

- zatwierdza zmiany w bazie danych

bez możliwości późniejszego ich wycofania.

Blokowanie dostępu do tabel

(Oracle)

• Oracle zakłada blokady na wierszach, na których są

przeprowadzane operacje.

• Programista może sam założyć blokadę na tabelę,

ograniczając możliwości jej zmian przez innych
użytkowników.  

LOCK TABLE nazwa_tabeli
IN [SHARE|EXCLUSIVE] MODE
[NOWAIT];

•

SHARE

– tryb współdzielony - przeciwko zmianom

dokonywanym przez kogokolwiek (wielu użytkowników
może założyć taką blokadę jednocześnie),

•

EXCLUSIVE

- tryb wyłączny - przeciwko zmianom

dokonywanym przez innych użytkowników, samemu
można zmieniać (tylko jeden użytkownik może mieć
założoną taką blokadę),

•

NOWAIT

- nie czekać na założenie blokady.

Blokady

• W Oracle: każdy użytkownik - mający

odpowiednie uprawnienia - może odczytywać
zatwierdzone dane w tabeli, niezależnie od
założonych blokad.

• W innych systemach tryb

EXCLUSIVE

może

uniemożliwiać jednoczesne odczytywanie danych.

• Zdjęcie blokady następuje przez wykonanie

COMMIT

lub

ROLLBACK

.

Klauzula FOR UPDATE

• W systemie Oracle jest możliwe założenie blokady

na wiersze będące wynikiem zapytania:

 

SELECT nazwa_kolumny
. . .
FOR UPDATE [ NOWAIT];

 

• Wykonanie COMMIT lub ROLLBACK kończy

blokadę wskazanych wierszy.

Transakcje “tylko-odczyt”

• “Zamrożenie” widoku bazy danych do jej aktualnego

stanu dla następnie wykonywanych instrukcji SELECT.
Instrukcje SELECT korzystają ze “zdjęcia
migawkowego” bazy danych wykonanego w tym
momencie.  

• Instrukcja

SET TRANSACTION READ ONLY;

musi być pierwszą instrukcją w transakcji – złożonej z

ciągu instrukcji

SELECT

i

LOCK TABLE

.

• ''Odmrożenie'' widoku bazy danych następuje przez

wykonanie polecenia COMMIT.

• Rozpoczęcie transakcji typu

nie-

READ ONLY

(opcjonalne).

SET TRANSACTION READ WRITE;

Poziomy izolacji transakcji

SET TRANSACTION ISOLATION LEVEL
[READ COMMITED|SERIALIZABLE];

• SERIALIZABLE

(izolowana transakcja) ::

gwarancja,
że transakcja działa na spójnych, nie zmienianych
przez inne transakcje danych (domyślny poziom
w Standardzie).

• READ COMMITED

(transakcja z odczytem

zatwierdzonych danych) :: transakcja działa na
zmienianych jednocześnie przez innych
użytkowników danych (ale dopiero po
zatwierdzeniu przez nich zmian). Zapewnia
większą współbieżność działania
systemu baz danych (domyślny poziom w
Oracle).

W standardzie jest więcej

opcji!

Słownik danych - informacja o

obiektach bazy danych (Oracle)

• Ma postać zbioru tabel i perspektyw.
• Z przedrostkiem

User

- informacja o wszystkich obiektach,

których dany użytkownik jest właścicielem –

• tabele: User_Tables (Tabs),

• tabele i kolumny: User_Tab_Columns (Cols),

• więzy spójności: User_Constraints,
User_Cons_Columns,

• indeksy: User_Indexes (Ind),
User_Ind_Columns,

• synonimy: User_Synonyms (Syn),

• perspektywy: User_Views.

• z przedrostkiem

All

- dotyczy wszystkich obiektów do

których użytkownik ma uprawnienia (np. All_Tables),

• z przedrostkiem

Dba

- informacja o obiektach dostępnych

dla administratorów systemu (np. Dba_Users).

SELECT Table_Name
FROM User_Tables;

- Podobnie

All_Tables

wypisuje nazwy tabel, których właścicielem jest
dany użytkownik.

Przykład

SELECT Column_Name,
Data_Type
FROM User_Tab_Columns
WHERE Table_Name =
'EMP';

COLUMN_NAME DATA_TYPE
----------- ---------

EMPNO NUMBER
ENAME VARCHAR2
JOB VARCHAR2
MGR NUMBER
HIREDATE DATE
SAL NUMBER
COMM NUMBER
DEPTNO NUMBER

Przyznawanie i odbieranie

uprawnień w bazie danych

GRANT uprawnienie, ...
ON obiekt
TO użytkownik, ...|PUBLIC
[WITH GRANT OPTION]

• uprawnienie - rodzaj operacji, do jakiej

użytkownik uzyskuje uprawnienie, np.

SELECT

,

DELETE

,

INSERT

,

UPDATE

,

ALTER

i

EXECUTE

;

• obiekt - tabela, perspektywa, …
•

WITH GRANT OPTION

- obdarowany

użytkownik może uzyskane uprawnienia
przekazywać innym użytkownikom;

•

PUBLIC

- przyznanie uprawnienia wszystkich

użytkownikom

.

Nadać księgowej uprawnienia dostępu do tabeli
Emp w zakresie wykonywania instrukcji

SELECT

oraz również instrukcji

UPDATE

, ale tylko w

odniesieniu do kolumny Sal.

Przykład

GRANT SELECT,
UPDATE(Sal)
ON Emp
TO Księgowa;

Uprawnienia systemowe

• Np.

CREATE TABLE

,

CREATE VIEW

,

CREATE

SESSION

(uprawnienie do

logowania się do bazy danych).

• Administrator bazy danych Oracle tworzy

konto użytkownika za pomocą instrukcji:

 

CREATE USER użytkownik IDENTIFIED

BY hasło;

  GRANT CREATE SESSION, CREATE

TABLE TO użytkownik; 

DROP USER użytkownik [CASCADE];

• CASCADE

- system usuwa użytkownika razem

ze wszystkimi jego obiektami.

REVOKE

• Uprawnienia odwołuje się za pomocą instrukcji

odwrotnej do instrukcji GRANT.

 

REVOKE uprawnienie, ...
ON nazwa_obiektu
FROM użytkownik, ... | PUBLIC;

Role (Oracle, SQL’1999)

• W celu ułatwienia przyznawania uprawnień

dużej liczbie użytkowników zostało
wprowadzone pojęcie roli tzn. powiązania
grupy użytkowników ze zbiorem uprawnień.

• Z jednej strony konkretnym użytkownikom

przyznaje się uprawnienie do danej roli
(przyznając im tę rolę).

• Z drugiej strony roli przyznaje się konkretne

uprawnienia do wykonywania operacji w bazie
danych.

Utwórz role Dyrektora i Urzędnika, przydziel im
uprawnienia
i zalicz do nich konkretnych użytkowników:

Przykład

CREATE ROLE Dyrektor;
CREATE ROLE Urzędnik;

GRANT SELECT ON Emp TO Dyrektor;
GRANT SELECT, UPDATE ON Emp TO
Urzędnik;

GRANT Dyrektor TO King, Leon;
GRANT Urzędnik TO Liza, Ewa, Marta;

Role zdefiniowane przez

System

•

CONNECT

- zawierająca uprawnienie

CREATE

SESSION

,

•

RESOURCE

- uprawnienia do tworzenia

obiektów,

•

DBA

- uprawnienia do wykonywania funkcji

administratora systemu.

• Przy tworzeniu konta nowego użytkownika

przydziela się mu się najczęściej role:

CONNECT

i

RESOURCE

– umożliwiające pełne korzystanie

z instrukcji SQL
na swoim koncie.

Schemat

• Schemat tworzy grupę powiązanych obiektów.

 

CREATE SCHEMA nazwa_schematu
ciąg instrukcji CREATE TABLE, CREATE VIEW

i GRANT (bez rozdzielających średników);

 

• Ciąg składowych instrukcji jest realizowany jako

transakcja: albo są wykonywane wszystkie
instrukcje albo żadna.

• W instrukcjach składowych mogą być odwołania

cykliczne REFERENCES między tabelami.

Schemat, katalog, klaster,

schemat informacyjny

• W Standardzie nie występuje w sposób jawny

pojęcie bazy danych.

• Katalog - zbiór schematów. Schemat ma jednego

właściciela. W skład katalogu mogą wchodzić
schematy mające różnych właścicieli.

• Klaster - zbiór katalogów. Klaster może być

traktowany jako rozproszona baza danych
składająca się ze zbioru katalogów, do których
użytkownik ma dostęp w ramach jednej sesji.

• Dla każdego katalogu powinien być określony

jeden schemat nazywany schematem
informacyjnym pełniący rolę
słownika danych dla całego katalogu.

Sesja i połączenia

• Zarówno w Standardzie jak i w Oracle8 istnieją

możliwości zmiany ustawień dla danej sesji
użytkownika.

• W Standardzie z każdą sesją jest związany zbiór

połączeń z różnymi bazami danych, z których
tylko jedno jest aktywne. Stosowane są
następujące polecenia:

• uzyskiwanie nowego połączenia

CONNECT TO nazwa_serwera;

• zmiana istniejącego połączenia

SET CONNECTION nazwa_serwera;

• rozłączenie połączenia

DISCONNECT nazwa_serwera;

ALTER SESSION

•

Niektóre własności sesji mogą być zmieniane w
trakcie połączenia. Od Oracle8 zmiany
wprowadza się za pomocą instrukcji ALTER
SESSION, na przykład:

 

ALTER SESSION ISOLATION LEVEL

SERIALIZABLE;

 

powoduje ustawienie poziomu izolacji transakcji
w sesji na SERIALIZABLE.

Dziedziny (domeny)

(Standard)

Zdefiniujmy dziedzinę numerów departamentów:

 

CREATE DOMAIN Dept# CHAR(3)
CHECK (VALUE IN

('A00','B01','D01','D11','D21', 'XXX'))

DEFAULT 'XXX';

 

Następnie, możemy jej użyć przy określaniu typu
danych kolumn w tabelach:

 

CREATE TABLE Dept
(DeptNo DOMAIN Dept# PRIMARY KEY,
...);

Asercje

(Standard)

•

Więzy spójności definiowane poza instrukcjami
CREATE TABLE i ALTER TABLE dotyczące całej
tabeli.

 

CREATE ASSERTION maxempl
CHECK (1000 <= SELECT COUNT (*)

FROM Emp);

 

DROP ASSERTION nazwa_asercji;

Tabele tymczasowe

• Są częścią schematu bazy danych - ich zawartość jest

niszczona:

• przy każdym zakończeniu sesji użytkownika

(opcja ON COMMIT PRESERVE ROWS),

• przy każdej operacji COMMIT

(opcja ON COMMIT DELETE ROWS).

 

CREATE GLOBAL TEMPORARY TABLE

Prac_zatrudniani_dziś

(Nr_kolejny INTEGER PRIMARY KEY,
Imię VARCHAR(40) NOT NULL,
Nazwisko VARCHAR(50) NOT NULL,
Informacja VARCHAR(1000))
ON COMMIT PRESERVE ROWS;

Generowanie jednoznacznych

numerów (Oracle)

• Sekwencja - przechowywana w bazie danych - służy do

generowania jednoznacznych numerów dla wartości
kluczy głównych.  

CREATE SEQUENCE nazwa_sekwencji
[INCREMENT BY k]
[START WITH n];

gdzie n jest pierwszą generowaną liczbą naturalną, a k
jest wartością, o jaką wzrasta kolejno generowany
numer.

• nazwa_sekwencji.NextVal - generowanie kolejnej

wartości w sekwencji,

• nazwa_sekwencji.CurrVal - ostatnio wygenerowana

wartość w sekwencji (w ramach aktualnej sesji).

Utwórz dwie sekwencje generujące jednoznaczne
numery dla działów i dla pracowników.

Przykład

CREATE SEQUENCE Dept_seq
INCREMENT BY 10
START WITH 10;

 
CREATE SEQUENCE Emp_seq
INCREMENT BY 1
START WITH 1000;

INSERT INTO Dept(Deptno, Dname, Loc)
VALUES (Dept_seq.NextVal, 'Sprzedaż',
'Warszawa');

 

Oto instrukcja tworząca kolejnego pracownika, z
wykorzystaniem wygenerowanego przed chwilą,
numeru działu:

 

INSERT INTO Emp
VALUES (Emp_seq.NextVal, 'Stec', 'MANAGER',
NULL, '20-JAN-90', 10000, 0,
Dept_seq.CurrVal);

Przykład

Klaster

(Oracle)

• Struktura fizycznego przechowywania danych, w

której kilka tabel jest zebranych razem według
wartości wspólnej kolumny (kolumn) - lub wartości
funkcji haszującej. Umożliwia szybsze
dokonywanie złączeń tabel wchodzących w skład
klastra.

• Najpierw tworzy się klaster, następnie tabele w

klastrze i w końcu indeks klastra. Dopiero w tym
momencie można rozpocząć wstawianie wierszy
do tabel w klastrze.

• Przykład klastra dla tabel Dept i Emp. Indeksem

klastra, względem którego gromadzone są obok
siebie na dysku
wiersze tych tabel, jest numer działu.

• Razem jest trzymany wiersz opisujący dział jak i

wiersze pracowników przypisanych do tego działu.

CREATE CLUSTER Personnel

(Department_number NUMBER(2));

CREATE TABLE Dept

(Deptno NUMBER(2) PRIMARY KEY,
Dname VARCHAR2(9),
Loc VARCHAR2(9))

CLUSTER Personnel (Deptno);
CREATE TABLE Emp

(Empno NUMBER PRIMARY KEY,
Ename VARCHAR2(10) NOT NULL,
Job VARCHAR2(9),
Mgr NUMBER REFERENCES Emp(Empno),
Hiredate DATE,
Sal NUMBER(10,2),
Comm NUMBER(9,0),
Deptno NUMBER(2)NOT NULL REFERENCES Dept(Deptno))

CLUSTER Personnel (Deptno);
CREATE INDEX Idx_personnel ON CLUSTER Personnel;

Przykład