Bazy danych – Notatki z wykładu dr Adama Domańskiego

1. Pojęcia podstawowe – baza danych, system zarządzania bazą danych , modele

danych, algebra relacji, języki zapytań

2. - SQL

- ochrona integralności, więzy referencyjne
- bezpieczeństwo

3. - zarządzanie transakcjami

- współbieżny dostęp do bazy, mechanizm blokad
- dziennik transakcji

4. Architektura baz danych (klient- serwer)
5. Projektowanie baz danych
-------------------------------------------------------------------------------------------------------------------------

•

BAZA DANYCH – zestaw danych pamiętanych, wzajemnie powiązanych,
wykorzystywanych przez wielu użytkowników.

•

SYSTEM ZARZĄDZANIA BAZĄ DANYCH – DBMS – uniwersalny system programowy
umożliwiający obsługę, ochronę baz danych w różnych zastosowaniach.

Funkcje, struktura i użytkownicy bazy danych

Schemat – model danych
Rekord – zestaw danych, które tworzą całość
Typ danych - w jaki sposób dana jest reprezentowana

Administrator
- Definicja i tworzenie BD
- Strojenie BD
- Czuwanie nad bezpieczeństwem
(Archiwizowanie,
odtwarzanie po awarii,
tworzenie użytkowników)

Programiści Aplikacji
- Wykonuje aplikacje
- Wprowadza dane
- Modyfikacje
- Przetwarzanie danych
- raporty

Użytkownicy okazjonalni
- pytania interaktywne
- wyświetlenia

PROCESOR ZAPYTAŃ

Kontrola dostępu * Optymalizacja (Rozważyć sposób dostępu) * Kontrola integralności

MODUŁ ZARZĄDZANIA PAMIĘCIĄ

Cache / Dyski

MODUŁ ZARZĄDZANIA TRANSAKCJAMI

-Sterowanie współbieżnym dostępem

-Blokady

-Dziennik transakcji

Definicje użytkowników
i praw dostępu

Metadane

Baza Danych

Dziennik transakcji

Model hierarchiczny

Wypłaty

Nazwa

Kierownik

Kierownik

Adres

Nazwisko

Nazwa

Pracownik

Zespół

Tematy Prac

Należ

y

W

yko

nu

je

ZESPÓŁ
Nazwa | Kierownik

PRACOWNIK
Nazwisko | Adres

Analizy | Jaroniewska

Waszek |Katowice

Domanski | Gliwice

Kwota

1 | Temat 1 | 1

2 | Temat 2 | 2

50

200

100

30

TEMAT
Nr_tem | Nazwa | Kierownik

Typy

Wystąpienia

Model sieciowy

Model relacyjny (1979 Codd)

Typy:

ZESPOŁY

Nazwa

Kierownik

Id_zesp

PRACOWNIK ID_prac

Nazwisko

Adres

ID_zesp

TEMAT

ID_temat

Nazwa

Kierownik

DOCHÓD

Kwota

ID_temat

ID_prac

Wystąpienie:

ZESPOŁY

PRACOWNICY

ID_Zesp

Nazwa

Kierownik

Id_Prac

Nazwa

Adres

Nr_zesp

1

Analiz

2

1 Jaworek

Bytom

2

2

Prac

3

2 Waszek

Gliwice

2

...

...

...

...

...

...

...

Z1

10

100

T2

T1

P1

P3

20

P2

Iloczyn Kartezjański
X * Y => (x,y) taki, że x

∈

Y , y

∈

X

Relacja dwuczłonowa
(x,y) własność

σ

(ro)

Algebra relacji
Selekcja

σ

-> wybór elementów spełniających jakiś warunek

A

B

C

A

B

C

a1 b1 c1

a2

b2 c2

a2 b2 c2

σ

Β

=b2

a4

b2 c4

a3 b1 c3
a4 b2 c4

Projekcja

π

Α

Β

Χ

Α

Χ

-

-

-

-

-

-

-

-

π

A,C

-

-

-

-

-

-

-

Złączenie



A

B

C

C

D

A

B

C

D

a1 b1 c1

c1 d1

a1 b1

c1

d1

a2 b2 c2

c2 d2

a2 b2

c2

d2

c1 c2 c3

c4 d4

a2 b2

c2

d5

a4 b4 c1

c2 d5

a4 b4

c1

d1

c6 d6

ZESPOŁY

DOCHODY

ID_zesp

Nazwa

Kierownik

Id_prac

Nr_tematu

Kwota

1

Analiz

2

1

1

100

2

Prac

3

2

1

50

3

Kontroli

1

1

2

30

PRACOWNICY

TEMATY

ID_prac

Nazwa

Adres

NR_zesp

ID_tem

Nazwa

Kier

1

Jaworek

Bytom

2

1

Nazwa1

2

2

Adamski

Gliwice

1

2

Nazwa2

3

3

Grabski

Gliwice

2

3

Nazwa3

1

Nazwiska tych ludzi którzy zarobili
p1 =

π (

pracownicy

dochody)

Pracownicy zespołu pierwszego
p2 =

π

naz

σ

nr

_

zesp=1

(pracownicy)

Nazwy tematów które wykonywał Jaworek
p3 =

π

nazwa_tematu

σ

nazwisko=jaworek

(Pracownicy

DochodyTematy)

SQL

SELECT Nazwisko FROM Pracownicy, Dochody, Tematy WHERE
Pracownicy.Id_pracownika=dochody.nr_pracownika;

SQL

–

Interaktywny

–

wbudowany

Podjęzyki SQL

–

DDL – Data Definition Language – Definicja Bazy

–

DML – Data Manipulation Language – wprowadzanie, usuwanie, poprawa
danych

–

DQL – Data Query Language – wyszukiwanie

–

DCL – Data Control Language – sterowanie danymi, zarządzanie
transakcjami

DDL – definiowanie struktury tabel

CREATE TABLE <nazwa tabeli>

(
<nazwa pola> <typ pola> [<ograniczenia>],
...
);

Typy znakowe:

–

charakter (n)

–

varchar (n)

–

longchar

Postgres

–

Char – jeden znak

–

Char (n) – n znaków (Tyle ile zadeklarowano będzie w bazie nawet
jeśli realnie bedzie wprowadzone mniej znaków)

–

Varchar (n) – na znaków (ale w bazie długość będzie dostosowana
do ilości znaków: np. Ala =3, Tomek=5)

–

Text – długi tekst

Typy Numeryczne

Dokładne

–

NUMERIC (n,p) // xxxxxx.pp n=x+p+'.'

–

DECIMAL (n,p)

–

INTEGER

–

SMALLINT

Niedokładne

–

FLOAT

–

REAL

–

DOUBLE PRECISION

Postgres

–

FLOAT4 – zmiennopozycyjny – 4 bajty

–

FLOAT8 – zmiennopozycyjny – 8 bajtów

–

INT2

–

INT28 – 8 dwubajtowych liczb całkowitych (tylko do wewnętrznych
operacji bazy danych)

–

INT4

–

INT8

Data i Czas

Postgres i SQL

–

DATE – yyyy-mm-dd

–

INTERVAL – odległość czasowa z dokładnością do mikrosekundy

–

TIME – GG:MM:SS - format 24h

–

TIMESTAMP – znaczek czasowy wstawiający aktualny czas

–

ABSTIME – Absolutny czas od włączenia komputera (Linux)

–

DATE TIME – data i czas w jednym

–

RELTIME – odstęp (-68 - +68 lat) krótkie okresy czasu

–

TIMESPAN – odległości czasowe z wielką precyzją

Predefiniowane stałe

–

CURRENT – bieżący czas transakcji

–

NOW – bieżąca chwila

–

TODAY – dziś o pólnocy

–

TOMMOROW – jutro o północy

–

YESTERDAY – wczoraj o północy

Bitowe

-BIT (n)
-VARBIT (n)

Inne

–

BYTE
Postgres:

–

BOOL – true/false, yes/no, 1/0

–

MONEY

Tworzenie własnych typów danych
CREATE TYPE <nazwa> (<nazwa> <typ>, ...);
CREATE TYPE Film (Tytul_filmu VARCHAR (30), Dlugosc INTERVAL);

CREATE TABLE Zespoly
(
Nazwa

VARCHAR (30),

Id_Zesp

INT4,

Kierownik INT4
);

Ograniczenia

–

NOT NULL

–

NOT NULL WITH DEFAULT <...>

–

UNIQUE

CONSTRUAN <nazwa ograniczenia> CHECK <warunek>

Więzy referencyjne

CREATE TABLE Uczniowie
(
Nazwisko CHAR(20),
Wzrost

INT4,

Waga

INT8

);

CREATE TABLE Uczniowie
(
Nazwisko VARCHAR(20) NOT NULL,
Wzrost

INT4 CONSTRUAN

(?)

Wzrost CHECK (Wzrost>0 AND

Wzrost<250),
Waga

INT4 CONSTRUATE

(? ma byc w koncu CONSTRUAN czy

CONSTRUATE?)

Waga CHECK (Waga>10)

);

Modyfikowanie tablic
ALTER TABLE <nazwa tabeli>

–

ADD (<nazwa pola>,....) - dodaje kolumnę

/y

–

ADD (ograniczenia dodatkowe) – dodaje ograniczenia

–

MODIFY (<nazwa pola> <typ> <ograniczenia>)- modyfikacja
istniejących kolumn

–

DROP (<nazwa pola>) - usuwa kolumny

ALTER TABLE Uczniowie ADD (plec VARCHAR(5));

Indeksy
CREATE [UNIQUE] INDEX <nazwa indeksu> ON <nazwa tablicy> (<lista
kolumn indeksowanych>);

Perspektywy / Widoki
CREATE VIEW <nazwa perspektywy> AS SELECT..

Usuwanie obiektów

DROP TABLE

<Nazwa tabeli>

DROP INDEX <nazwa indeksu>
DROP VIEW <nazwa widoku>

DML
INSERT INTO <tabela> [ ( <lista kolumn> ) ] VALUES ( <lista wartości> );

INSERT INTO Uczniowie VALUES ('Domański', 173, 30, 1);
INSERT INTO Uczniowie (Nazwisko, Klasa) VALUES ('Domański', 1);

INSERT INTO Zwolnieni SELECT * FROM Pracownicy WHERE data_zw IS
NOT NULL;

UPDATE <tabela> SET <nazwa pola>=wyrazenie [WHERE <warunek>] ;

UPDATE Uczniowie SET Waga=Waga+1;
UPDATE Uczniowie SET Waga=73, wzrost=175 WHERE
nazwisko='Domański';

DELETE FROM <tabela> [WHERE <warunek>];
DELETE FROM Uczniowie; //usuwa wszystkie wpisy z tabeli uczniowie
DELETE FROM Uczniowie WHERE klasa=4;

DQL
SELECT < > FROM < > [WHERE < > GROUP BY < > HAVING < > ORDER
BY < > UNION ... ]

SELECT [ALL | DISTINCT] {<wyrażenie>,<nazwa pola>, *};

SELECT nazwisko,wzrost-waga-100 FROM Uczniowie;
SELECT nazwisk

o,wzrost-waga-100 AS Nadwaga, klasa FROM Uczniowie;

Funkcje agregujące

–

MAX – wartość maksymalna

–

MIN – wartość minimalna

–

AVG – wartość średnia

–

COUNT – zlicza ilość wystąpienia

–

SUM – suma

//W postgresie funkcje agregujące musz wystapic razem z grupowaniem (GROUP BY) !

SELECT SUM(waga) FROM Uczniowie;
SELECT COUNT(*) FROM Uczniowie;
SELECT 3+7;

ALL | DISTINCT
ALL – wyświetl wszystkie wiersze – domyślnie aktywna
DISTINCT – Wyświetl niepowtarzające się wiersze

SELECT DISTINCT klasa FROM Uczniowie;

FROM

FROM <Element>[<alias>] //element – tabela, widok, select

SELECT Pracownicy.nazwisko FROM Pracownicy
SELECT p.nazwisko FROM Pracownicy p; //alias p=pracownicy

WHERE

WHERE <warunek>

warunek

–

Zagnieżdżony

–

Prosty

–

filtrujący, 1 nazwa tabeli

–

łączący, kilka tabel

Filtrujący zwykły -> <wyrażenie> <operator> <wyrażenie>

operatory: =,<,>,=>,=<,<>,!=

SELECT * FROM Uczniowie WHERE klasa=2;
SELECT * FROM Uczniowie WHERE wzrost-waga-100>0;
SELECT Nazwisko,wzrost-waga-100 AS Nadwaga FROM Uczniowie WHERE
Nadwaga>0;
SELECT * FROM Uczniowie WHERE wzrost=<180 AND wzrost=>170;
SELECT * FROM Uczniowie WHERE BETWEEN(wrost,170,180);

WHERE <wyrażenie> {NOT] LIKE <wzorzec tekstowy>

_ - 1 znak (podkreślenie) %- Dowolny ciąg znaków (również 0 znaków)

SELECT * FROM Uczniowie WHERE nazwisko LIKE 'Kowalsk_';

SELECT * FROM Uczniowie WHERE UPPER (nazwisko) LIKE 'KOWAL%';
SELECT * FROM Uczniowie WHERE nazwa LIKE '12 \%%' ;

Warunki łączące

SELECT p.nazwisko, z.nazwa FROM Pracownicy p, Zespoly z
WHERE z.id_zesp=p.id_zesp;

SELECT p.nazwisko FROM pracownicy p, dochody d
WHERE p.id_prac=d.id_prac AND d.kwota>500;

SELECT p2.nazwisko FROM pracownicy p1, pracownicy p2
WHERE p1.nazwisko='Jaworek' AND p1.id_zesp=p2._idzesp
AND p2.nazwisko='jaworek';

Wyświetl nazwiska pracowników którzy dostali kasę z tematów kierowanych przez
Grabskiego

SELECT p2.nazwisko FROM pracownicy p1, pracownicy p2, dochody d, tematy t
WHERE p1.nazwisko='Grabski' AND p1.id_prac=t.kierownik
AND t.id_tematu=d.id_tematu AND t.id_prac=p2.id_prac;

Wyświetl nazwiska i kwoty wypłat

[*]

SELECT nazwisko, kwota, nr_tematu FROM pracownicy p, dochody d

WHERE d.id_prac=p.id_prac;

Wyświetl nazwiska wszystkich pracowników i kwoty wypłat

//dygresja ;-)
A

B

C

C

D



I



II

II



I

a1

b1

c1

c1

d1

a1 b1 c1 d1

a1 b1 c1 d1

a1 b1 c1 d1

a2

b2

c2

c5

d5

a4 b4 c1 d1

a2 b2 c2 .

a4 b4 c1 d1

a3

b3

c3

a3 b3 c3 .

. . c5 d5

a4

b4

c1

a4 b4 c1 d1

//

[**]

SELECT Nazwisko, kwota FROM Pracownicy p, Dochody d
WHERE p.id_prac=d.id_prac(+);

Inne formy zapisu złączeń

FROM <tabela a> [typ złączenia] JOIN <tabela b>;
FROM <tabela a> [typ złączenia] JOIN <tabela b> USING <lista kolumn>;
FROM <tabela a> [typ złączenia] JOIN <tabela b> ON <warunek>;

typ złączenia: INNER, LEFT, RIGHT, FULL, OUTER

[*]

SELECT nazwisko, kwota, ID_Tematu FROM Pracownicy JOIN Dochody;

SELECT nazwisko, kwota, ID_Tematu FROM Pracownicy
JOIN Dochody USING Id_Prac;

SELECT nazwisko, kwota, ID_Tematu FROM Pracownicy
JOIN Dochody ON Pracwonicy.Id_praac=Dochody.ID_prac;

[**]

SELECT nazwisko, kwota FROM Pracownicy LEFT OUTER JOIN Dochody;

Warunki filtrujące z pytaniami zagnieżdżonymi

SELECT * FROM ... WHERE <początek warunku> (<nowe zapytanie SELECT>);

początek warunku:

a) <wyrażenie> <operator porównania>
b) <wyrażenie> <operator porównania> <ANY | ALL>
c) <wyrażenie> [NOT] IN ...
d) [NOT] EXIST

1) Skorelowane
2) Nieskorelowane

Ad a.

<wyrażenie> < operator porównania>

nr_zesp=4
SELECT nazwisko FROM Pracownicy WHERE nr_zesp=(

SELECT nr_zesp FROM Pracownicy WHERE nazwisko='jaworek);

Ad b.

<wyrażenie> <operator porównania> <ANY|ALL>

//NIESKORELOWANE
Wyszukać nazwy tematów z których były jakieś kwoty

SELECT nazwa FROM Tematy WHERE nr_tem= ANY(

SELECT nr_tem FROM Dochody);

Wyszukać nazwiska uczniów klasy I, którzy są wyżsi od każdego ucznia klasy 3

SELECT nazwisko FROM Uczniowie WHERE klasa=1 AND wzrost> ALL (

SELECT wzrost FROM Uczniowie WHERE klasa=3);

SELECT nazwisko FROM Uczniowie WHERE klasa-1 AND wzrost>(

SELECT MAX(wzrost) FROM Uczniowie WHERE klasa=3);

//SKORELOWANE
Wyszukać nazwiska tych co wszystkie wypłaty były większe niż 2000

SELECT nazwiska FROM pracownicy p WHERE 2000< ALL (

SELECT kwota FROM dochody d WHERE d.id_prac=p.Id_prac);

//Korelacja -> tu: odwołanie w 2 selekcie do tabeli w 1 selekcie

Ad c.

<wyrażenie> [NOT] IN // IN <=> ANY

Znaleźć nazwy zespołów,których pracownicy uczestniczyli w temacie nr 5.

SELECT z.nazwa FROM Dochody d, Zespoly z, Pracownicy p WHERE

d.id_tem=5 AND d.id_prac=p.id_prac AND z.id_zesp=p.id_zesp;

SELECT nazwa FROM Pracownicy WHERE nr_zesp IN (

SELECT nr_zesp FROM Pracownicy WHERE id_prac IN (

SELECT id_prac FROM Dochody WHERE id_tem=5 ));

Ad d.

[NOT] EXIST

Znajdź tematy w których były jakiekolwiek tematy.

SELECT nazwa FROM Tematy t, WHERE EXIST (

SELECT * FROM Dochody WHERE d.id_tem=t.id_tem);

Wyszukać nazwiska pracowników, którzy nie mieli wypłat.

SELECT nazwisko FROM Pracownicy p, WHERE NOT EXIST (

SELECT * FROM Dochody d WHERE p.id_prac=d.id_prac);

Wyszukaj pracowników, którzy wykonywali wszystkie tematy.

SELECT nazwisko FROM Pracownicy p WHERE NOT EXIST (

SELECT * FROM Tematy t WHERE NOT EXIST (

SELECT * FROM Dochody WHERE

d.id_tem=t.id_tem AND d.id_prac=p.id_prac));

FUNKCJE AGREGUJĄCE
SUM(<argument>)

- Suma wybranych pól

COUNT(<argument>)

- Ilość wystąpienia danego pola

MAX(<argument>)

- Wartość maksymalna wśród wybranych pól

MIN(<argument>)

- Wartości minimalna wśród wybranych pól

AVG(<argument>)

- Średnia wartość z wybranych pól

<argument> = nazwa kolumny lub

ALL | DISTINCT <wyrażenie>

Znaleźć liczbę pracowników

SELECT COUNT(*) FROM Pracownicy;

Znaleźć sumę wszystkich wypłat, maksymalną wypłatę, minimalną wypłatę, średnią
wypłatę z tematu nr 1.

SELECT SUM(kwota), MIN(kwota), MAX(kwota), AVG(kwota) FROM Dochody
WHERE id_tem=1;

Ile było wypłat, liczbę pracowników, którzy otrzymali wypłaty, liczbę tematów w ramach
których były te wypłaty

SELECT COUNT(*), COUNT( DISTINCT id_prac ), COUNT( DISTINCT id_tem)
FROM Dochody;

Znaleźć liczbę pracowników praujących w temacie 1.

SELECT COUNT( DISTINCT id_prac ) FROM Dochody WHERE id_tem=1;

SELECT COUNT(*) FROM Dochody WHERE id_tem=1 GROUP BY id_prac;

Znaleźć maksynalną nadwagę.

SELECT MAX( wzrost-waga-100) FROM Uczniowie;

Wyświetlić uczniów z nadwagą sortując po nadwadze.

SELECT nazwisko, wzrost-waga-100 FROM Uczniowie
WHERE wzrost-waga-100>0 ORDER BY wzrost-waga-100;

//Ten select nie zadziała :-) musi być jeszcze AS <Nazwa>//

Znaleźć powiat, dla którego liczba mieszkańców jest większa niż 50000.

SELECT powiat, SUM(liczba_mieszk) FROM Polska
GROUP BY powiat HAVING SUM(liczba_mieszk)>50000;

Widoki / Perspektywy

CREATE VIEW <nazwa widoku> AS <wyrazenie select>;

CREATE VIEW Zespol1 AS SELECT * FROM Pracownicy WHERE id_zesp=1;

//W ten sposób można ograniczyć dostęp użytkownikom – tu będzie dostęp tylko do
zespołu pierwszego i nic poza tym nie można zepsuć//

Więzy REFERENCYJNE (Ochrona integralności danych)

Klucze główne – (Primary Key) – Jednoznacznie identyfikuje wiersz w tabeli. Muszą być
unikalne i niepuste. Niektóre odmiany SQL wymagają indexu na kluczu głównym.

Klucze obce – Foreign Key – Niepuste wartości klucza obcego muszą odpowiadać
istniejącej wartości klucza głownego w tablicy nadrzędnej. Dopuszcza się wartości puste
kluczy obcych.

Ograniczenia na usuwanie danych

–

Restrykcyjne – usunięcie wiersza z tablicy nadrzędnej jest możliwe jeśli nies istnieje
wiersz w tablicy podrzędnej.

–

Z zostawieniem wartości pustych – Zostawi sam kluch ale reszta danych zostanie
usunięta – będzie pusta.

–

Ograniczenie kaskadowe – automatycznie po usunięciu wiersza nadrzędnego usuwa
się wszystkie połączone z nim wiersze podrzędne.

Najpierw definiujemy tablice nadrzędne a potem podrzędne!

CREATE TABLE Zespoly
(
id_zesp

INT NOT NULL,

nazwa

VARCHAR(50),

kierownik

INT,

PRIMARY KEY (id_zesp)
);

CREATE TABLE Pracownicy
(
id_prac

INT NOT NULL,

nazwisko

VARCHAR(50),

adres

VARCHAR(50),

id_zesp

INT,

PRIMARY KEY(id_prac),
CORIN KEY klucz1(id_zesp) REFERENCES Zespoly ON DELETE RESTRICT
//lub SET NULL lub CASCADE//
);

Bezpieczeństwo Baz Danych
1. Fizyczne zabezpieczenie dostępu
2. Personalna odpowiedzialność za dostęp do danych
3. Mechanizmy zabezpieczeń systemu komputerowego
4. Zabezpieczenia baz danych

Zagrożenia BD

–

Nielegalny odczyt

–

Niepoprawna modyfikacja danych

–

działania umyślne

–

przypadkowa pomyłka

–

zdarzeń losowych, współbieżny dostęp do danych

–

błędy programowe, awaria systemu

Przeciwdziałania

–

Kontrola dostępu

–

Monitorowanie baz danych

–

Szyfrowanie baz danych

–

Ochrona integralności (spójności)

–

semantyczna spójność – więzy referencyjne

–

Mechaniczna interalność transakcji

Kontrola dostępu

–

Identyfikacja i autentyfikacja

–

tworzenie użytkowników

–

nadawanie uprawnień

CREATE USER <nazwa uzytk> IDENTIFIED BY <haslo>
GRANT <uprawnienia> TO <nazwa uzytkownika> [WITH ADMIN OPTION]

Uprawnienia

–

CREATE TABLE

–

SELECT TABLE

–

UPDATE TABLE

Odbieranie uprawnień

REVOKE <uprawnienie> FROM <uzytkownik>

Rola (zarządzanie uprawnieniami)

CREATE ROLE <nazwa roli> GRANT <uprawnienia> TO <nazwa roli>
//w niektórych SQLach ROLE=RULE

Nadawanie ról użytkownikom
GRANT <nazwa roli> TO <uzytkownik>

Postgres:
CREATE USER <uzytkownik> [WITH PASSWORD <haslo>]
[CREATEDB | NOCREATEDB]
[CREATEUSER | NOCREATEUSER]
[VALID UNTIL <data>]

CREATE USER Jasio WITH PASSWORD ala_i_as VALID UNTIL 'jan-01-2005';

GRANT <przywilej1,.....,przywilejN> ON Tabela1,...,TabelaM
TO [PUBLIC | GROUP <nazwa grupy> | <uzytkownik>]

Przywileje
ALL, SELECT, INSERT, UPDATE, DELETE, RULE

GRANT INSERT ON Pracownicy TO Jasio

Monitorowanie BD
Audit

–

Monitorowanie operacji

–

Monitorowanie upranień

–

Monitorowanie obiektów (stany aktualne, ujemne)

Szyfrowanie

–

Hasła (szyfrowanie hasel)

–

Szyfrowanie procedur

–

Szyfrowanie danych

Współbieżny dostęp do bazy + zarządzanie transakcjami
Transakcja – operacja na bazie, która stanowi pewną całość (przeprowadza bazę ze
stanu spójnego w stan spójny)

TBegin

Read(x)

//stan konta x

x=x-k

//ściągamy kwotę z konta x

Write(x)

//zapisz zmiany na koncie x

Read(y)

//stan konta y

y=y+k

//dokładamy kwotę do konta y

Write(y)

//zapisz zmiany na koncie y

TEnd

Pożądane własności transakcji

–

Atomowość (transakcja musi zostać wykonana, skutki częściowego wykonania muszą
być usunięte)

–

spójność (przed rozpoczęciem transakcji i po wykonaniu transakcji baza musi być
spójna

–

Izolacja (wyłączność)

–

trwałość (zatwierdzona transakcja nie może zostać cofnięta)

Harmonogram – kolejność wykonywania wszystkich elementarnych operacji pewnego
zbioru transakcji realizowanych współbieżnie

T1 – Przelanie z konta A na konto B 100zł
T2 – Przelanie z konta B na konto C 200zł
Wartości początkowe: A=1000zł B=2000zł C=3000zł
A+B+C=1000+2000+3000=6000 stan spójny bazy

Harmonogram 1 - Szeregowy

Przebieg

Stany

T1 Begin

Read(A)

A=1000

A=A-100
Write (A)

A=900

Read(B)

B=2000

B=B+100
WrIte(B)

B=2100

T1 END
T2 Begin

Read(B)

B=2100

B=B-200
Write(B)

B=1900

Read(C)

C=3000

C=C+200
Write(C)

C=3200

T2 END

A+B+C=900+1900+3200=6000

Harmonogram 2 - Szeregowalny

Transakcja 1

Transakcja 2

Stany

Read(A)

A=1000

Read(B)

B=2000

A=A-100

B=B-200

Write(A)

A=900

Write(B)

B=1800

Read(B)

B=1800

Read(C)

C=3000

B=B+100

C=C+200

Write(B)

B=1900

Write(C)

C=3200

A+B+C=900+1900+3200

Harmonogram 3 - Nieszeregowalny

Transakcja 1

Transakcja 2

Stany

Read(A)

A=1000

A=A-100

Read(B)

B=2000

Write(A)

A=900

B=B-200

Read(B)

B=

2000

Write(B)

B=

1800

B=B+100

Read(C)

C=3000

Write(B)

B=2100

C=C+200
Write(C)

C=3200

A+B+C=900+2100+3200=

6200

Blokowanie dostępu do bazy danych
LOCK (Jednostka danych) - zablokowanie
UNLOCK (Jednostka danych) - odblokowanie

EFEKT:

–

Sukces (Udało się zablokować)

–

Porażka (Nie udało się zablokować)

–

Zawieszamy transakcję

–

Kontynuujemy bez praw dostępu (nie uda się zapisać, odczytać)

Harmonogram 3`

Transakcja 1

Transakcja 2

Stany

Lock(A)
Read(A)

A=1000

A=A-100

Lock(B)

Write(A)

A=900

Read(B)

B=2000

Unlock(A)

B=B-200

Lock(B)

T1 porażka-B

Transakcja
zawieszona

Write(B)

B=1800

Transakcja
zawieszona

Unlock(B)

B odblokowane
T1 Sukces

Harmonogram 3`

Read(B)

B=1800

Lock (C)

B=B+100

Read(C)

C=3000

Write(B)

B=1900

C=C+200

Unlock(B)

Write(C)

C=3200

Unlock(C)

Rozpatrzmy teraz takie transakcje gdzie x=20 y=30:

Transakcja 1

Transakcja 2

Lock(Y)

Lock(X)

Read(Y)

Read(X)

Unlock(Y)

Unlock(X)

Lock(X)

Lock(Y)

Read(X)

Read(Y)

X=X+Y

Y=X+Y

Write(X)

Write(Y)

Unlock(X)

Unlock(Y)

Według harmonogramu 1:

H1 T1->T2 X'=50 Y'=80

Według harmonogramu 2:

H2 T2->T1 X'=70 Y'=50

Według harmonogramu 3:

Lock(Y)
Read(Y)
Unlock(Y)

Lock(X)
Read(X)
Unlock(X)
Lock(Y)
Read(Y)
Y=Y+X
Write(Y)

Zapis Y – Transakcja 1 posiada nieaktualne juz dane

Unlock(Y)

Lock(X)
...

...

Ciag dalszy niewazny

Dwufazowy protokół blokowania
Transakcja wypełnia protokół dwufazowy jeśli wszystkie operacje blokowania występują
przed pierwsza operacją odblokowania. Dowolny harmonogram transakcji dwufazowych
jest harmonogramem szeregowym.

Transakcja 1

Transakcja 2

Lock(Y)

Read(Y)
Lock(X)
Unlock(Y)

Lock(X)

T2 Porażka

Trans, zawieszona Pauza
Trans, zawieszona Pauza

...

...

...

Ochrona przed blokadą (Dead Lock)

–

dziennik transakcji

Transakcje w SQL

–

BEGIN [TRANSAKCTION] [WORK] – rozpocznij transakcję

–

COMMIT [TRANSAKCTION] [WORK] – zatwierdź transakcję

–

ROLLBACK [TRANSAKCTION] [WORK] – cofnij transakcję

Begin Work

IF Lock(X)

Read(X)
X=X+100
Write(X)

Else

Rollback Work

EndIF
IF Lock(Y)

Read(Y)
Y=Y+200
Write(Y)

Else

Rollback Work

EndIF

Commit Work

T1

T2

X

Y