5. J

zyk SQL

•

Przegl

•

Instrukcja wyboru (SELECT)

•

Instrukcje modyfikacji danych

•

Inne instrukcje

for each
{x=1;
SQL

select

for each
{x=1;
SQL

update

for each
{x=1;
SQL

update

SQL (

Structured

Query

Language

)

SEQUEL (1974) - opracowany w IBM Research Laboratory w San Jose. Następnie

jego nazwę zmieniono na SQL (często wymawiane jako „sequel”).

Pierwsze zastosowanie - SYSTEM R, następnie w systemach DB2, SQL/DS, QMF na

komputerach IBM/370 (mainframe).

Standardy:

SQL-86: ANSI X3.135-1986 „Database Language SQL”
SQL-89: ANSI X3.168-1989 „Database Language - Embedded SQL”

ANSI X3.135-1989 „Database Language - SQL with Integrity Enhancement”

SQL-92: ISO/IEC 9075:1992 „Database Language SQL”
SQL-99: ISO/IEC 9075:1999 „Information Technology - Database Languages - SQL”

interakcyjny - instrukcje wprowadzane interakcyjnie, wyniki natychmiast

prezentowane użytkownikowi w sposób ustalony przez DBMS
(SQL nie zawiera żadnych instrukcji we/wy ani formatujących).

zanurzony - instrukcje są zamieszczone w programie napisanym w innym

języku (3GL lub 4GL); wyniki mogą być przekazane do zmiennych
programu.

SQL

adniki SQL

SQL = Data Definition Language (DDL)

+ Data Manipulation Language (DML)

+ Data Control Language (DCL)

Podstawowe instrukcje ("core SQL"`):

DDL

DML

DCL

CREATE TABLE
CREATE VIEW
CREATE INDEX
ALTER TABLE
DROP TABLE
DROP VIEW
DROP INDEX

SELECT
INSERT
UPDATE
DELETE

GRANT
REVOKE

Standardy SQL-92 i SQL-99 obejmują ponadto szereg innych instrukcji, które rozszerzają
funkcje DBMS np. o definiowanie ograniczeń, reguł, funkcji, wyzwalaczy itd.
Szczegóły na temat konkretnej implementacji SQL - w dokumentacji DBMS.

Tworzenie tablic

CREATE TABLE <nazwa tablicy>

(<nazwa kolumny> <typ danych> [opcje]
[, <nazwa kolumny > <typ danych> [opcje]. . . ]
[, UNIQUE (<nazwa kolumny>, [<nazwa kolumny>])]...);

Cel: Utworzenie w bazie danych nowej tablicy. Trzeba określić:

- nazwę tablicy (niepowtarzalna w bazie danych),
- nazwy kolumn (niepowtarzalne w tablicy),
- typ danych każdej kolumny.

Typy danych: (SQL-89)

INTEGER, SMALLINT - liczba całkowita (zakres zależy od DBMS).
DECIMAL[(m [ , n ])], NUMERIC[(m [ , n ])] - stałoprzecinkowa liczba rzeczywista

o m cyfrach znaczących i n cyfrach po przecinku.

FLOAT[(m)] - zmiennoprzecinkowa liczba rzeczywista o m cyfrach.
REAL – to samo co DECIMAL.
DOUBLE PRECISION – to samo co FLOAT.
CHARACTER(n) - łańcuch znaków o długości do n znaków (separator: “ lub ‘).
DATE - data (zwykle w formacie: mm/dd/yyyy).
LOGICAL - wartości logiczne: yes/no, true/false.

Tworzenie tablic (cd.)

Inne typy danych: (SQL-92)

VARCHAR, DATETIME, MONEY, TEXT, IMAGE, BLOB, CLOB, ...

(szczegóły - w dokumentacji DBMS)

Opcje (przykładowe):

NOT NULL [ UNIQUE ] - w kolumnie musi być wartość, jeśli UNIQUE - to unikatowa.
UNIQUE (<kolumna> [,<kolumna>]...) – wartość w kolumnie (kolumnach) musi być unikatowa.

Każda kolumna musi być zdefiniowana z opcją NOT NULL.

[NOT] CASE-SENSITIVE – określa, czy przy porównywaniu wartości w kolumnie znakowej

ma być brana pod uwagę wielkość liter.

COLUMN-LABEL łańcuch – etykieta (nagłówek) kolumny przy wyświetlaniu tabelarycznym
LABEL łańcuch – etykieta kolumny przy wyświetlaniu swobodnym
FORMAT łańcuch – format wyświetlania wartości kolumny.
DEFAULT wartość-początkowa – domyślna wartość kolumny.
AUTOINCREMENT - powoduje automatyczne nadawanie wartości kolumny przez system

podczas wstawiania nowego wiersza.

PRIMARY KEY - określa kolumnę jako klucz główny.
REFERENCES [nazwa tablicy] - określa kolumnę jako klucz obcy.

Szczegółowy wykaz opcji - w opisie instrukcji CREATE TABLE konkretnego DBMS.

Tworzenie tablic

przyk

Instrukcje tworzące tablice bazy danych School1:

CREATE TABLE Teachers

(Tno

CHAR(3) NOT NULL UNIQUE,

Tname CHAR(12),
Title

CHAR(6),

City CHAR(8),
SupNo CHAR(3));

CREATE TABLE Students

(Sno

CHAR(3) NOT NULL UNIQUE,

Sname CHAR(12),
Sdate DATE,
City CHAR(8));

CREATE TABLE TS

(Tno

CHAR(3) NOT NULL,

Sno

CHAR(3) NOT NULL,

hours INT,
UNIQUE (Tno, Sno));

Tworzenie tablic

przyk

(cd.)

Z zastosowaniem opcji definiujących klucze główne i klucze obce:

CREATE TABLE Teachers

(Tno

CHAR(3) PRIMARY KEY,

Tname CHAR(12),
Title

CHAR(6),

City CHAR(8),
SupNo CHAR(3) REFERENCES Teachers);

CREATE TABLE Students

(Sno

CHAR(3) PRIMARY KEY,

Sname CHAR(12),
Sdate DATE,
City CHAR(8));

CREATE TABLE TS

(Tno

CHAR(3) REFERENCES Teachers,

Sno

CHAR(3) REFERENCES Students,

Hours INT,
PRIMARY KEY (Tno, Sno));

Zmiana schematu tablicy

ALTER TABLE <nazwa tablicy>

{ ADD COLUMN <kolumna> <typ danych> [opcje] |

DROP COLUMN <kolumna> |
ALTER COLUMN <kolumna> [opcje] }

Cel: Dodanie nowej kolumny do istniejącej tablicy. Nowa kolumna przyjmuje wartości puste (NULL).

Usunięcie kolumny z istniejącej tablicy.
Zmiana opcji istniejącej kolumny (ale nie typu danych!).

Przykłady:

ALTER TABLE Students

ADD COLUMN PhoneNo Char(13);

ALTER TABLE Teachers

DROP COLUMN City;

ALTER TABLE Teachers

ALTER COLUMN Tname LABEL "Name of Teacher”;

Uwaga: Nie można zmienić niektórych opcji, np. NOT NULL, UNIQUE, PRIMARY KEY, ...

Wstawianie danych do tablicy

INSERT INTO <nazwa tablicy>

[( <lista kolumn> )]
VALUES (<lista wartości>)

Cel: Wstawienie do tablicy nowego wiersza na podstawie listy podanych wartości.

Jeśli nie ma <listy kolumn>, wówczas należy podać wszystkie wartości,

w kolejności kolumn tablicy wynikającej z jej schematu.

Przykłady:

INSERT INTO Teachers

VALUES (“T6”, “Taylor”, “Prof.”, “New York”, “T1”);

INSERT INTO Teachers

( TNo, TName, City )
VALUES (“T7”, “James”, “Paris”);

#10

SELECT

przegl

SELECT [DISTINCT] <lista kolumn/wyrażeń>

FROM <lista tablic>
[ WHERE <warunek> ]
[ GROUP BY <lista kolumn> ]
[ HAVING <warunek> ]
[ UNION <instrukcja SELECT> ]
[ ORDER BY <lista kolumn/numerów> ]

• SELECT - określa, które kolumny z tablic podanych we frazie FROM mają zostać włączone

do wyniku (projekcja). Dodatkowo, mogą zostać włączone wyrażenia.

• DISTINCT - powoduje wyeliminowanie duplikatów (wierszy powtarzających się).
• FROM - określa tablice, na jakich działa instrukcja SELECT. Jeśli jest kilka tablic, dokonywany

jest produkt kartezjański tych tablic.

• WHERE - określa warunki wyboru wierszy z tablic wymienionych we frazie FROM (selekcja).
• GROUP BY - powoduje wiązanie wierszy wynikowych w grupy o jednakowych wartościach

we wskazanych kolumnach, a następnie redukowanie tych grup do pojedynczych wierszy.

• HAVING - określa warunki wyboru dla wierszy powstałych w wyniku działania frazy GROUP BY.
• UNION - oblicza sumę wyników dwóch instrukcji SELECT, eliminując duplikaty (unia).
• ORDER BY - porządkuje wiersze wynikowe rosnąco lub malejąco według wartości wskazanych

kolumn/wyrażeń.

#11

SELECT

zapytania proste

SELECT [DISTINCT] <lista kolumn/wyrażeń>

FROM <nazwa tablicy>
[ WHERE <warunek> ]

Zapytanie proste przeprowadza kombinację projekcji i selekcji (jeśli występuje fraza WHERE)
w stosunku do jednej tablicy.

Uwaga: <lista kolumn> może przybrać formę * (co oznacza wybór wszystkich kolumn).

Przykłady:

1. Pokaż identyfikatory i nazwiska wszystkich nauczycieli.

SELECT TNo, TName

FROM Teachers;

2. Pokaż nazwy wszystkich miast, skąd pochodzą studenci.

SELECT DISTINCT City

FROM Students;

Uwaga: Bez słowa DISTINCT nazwy miast mogłyby się powtarzać.

#12

SELECT

zapytania proste

(cd.)

3. Pokaż informacje o zajęciach, które trwały ponad 95 godzin.

SELECT *

FROM TS
WHERE Hours > 95;

4. Pokaż identyfikatory i nazwiska nauczycieli, którzy pochodzą z Londynu.

SELECT TNo, TName

FROM Teachers
WHERE City = “London”;

5. Pokaż identyfikatory, tytuły i nazwiska nauczycieli, którzy mają szefa.

SELECT TNo, Title, TName

FROM Teachers
WHERE SupNo IS NOT NULL;

#13

SELECT

zapytania proste

(cd.)

6. Dla każdego studenta nauczyciela T3 pokaż średnią liczbę godzin zajęć na tydzień.

SELECT Sno, Hours/15, ”godz. na tydzień”

FROM TS
WHERE Tno = ”T3”;

Wynik:

SNO

EXP1

EXP2

2.13

godz. na tydzień

2.40

godz. na tydzień

4.00

godz. na tydzień

4.80

godz. na tydzień

#14

Wyra

enia

Operatory języka SQL występujące w wyrażeniach we frazie SELECT, WHERE i in.:

Operatory arytmetyczne:

** ^

- potęgowanie

* /

- mnożenie, dzielenie

+ -

- dodawanie, odejmowanie

Operatory logiczne:

NOT AND OR

Operatory porównania:

= > < >= <= <>

Operatory specjalne:

IN BETWEEN LIKE MATCHES

Operatory porównania z wartością pustą:

IS NULL IS NOT NULL

#15

Wyra

enia

(cd.)

Funkcje agregujące:

COUNT() - zwraca liczbę wierszy wybranych w zapytaniu
AVG()

- oblicza średnią arytmetyczną w kolumnie numerycznej

SUM()

- sumuje wartości kolumn numerycznych

MIN()

- znajduje wartość minimalną w kolumnie

znakowej, numerycznej lub daty

MAX()

- znajduje wartość maksymalną w kolumnie

znakowej, numerycznej lub daty

Funkcje zależne od DBMS

operujące na datach/godzinach, łańcuchach znakowych, polach tekstowych,
wielkich obiektach binarnych,...

#16

Operatory specjalne

Sprawdza, czy wartość w kolumnie jest równa jednej z wartości określonej na liście wartości
lub będących wynikiem instrukcji SELECT.
Przykłady:

WHERE City = “London” OR City = “Davos” OR City = “Bristol”

jest równoważne:
WHERE City IN (“London”, “Davos”, “Bristol”)

2. SELECT *

FROM Teachers
WHERE City IN

(SELECT City

FROM Students);

Wynik:

Ci nauczyciele z relacji Teachers, którzy pochodzą z miasta występującego
w relacji Students.

#17

Operatory specjalne

(cd.)

BETWEEN

Sprawdza, czy wartość w kolumnie jest zawarta w przedziale wartości.
Przykłady.
1.

WHERE Hours >= 96 AND Hours <= 126
jest równoważne:
WHERE Hours BETWEEN 96 AND 126

2. SELECT

FROM Teachers
WHERE TName BETWEEN “A” AND “C”;

Wynik:

TNO

TNAME TITLE

CITY

SUPNO

Blake

Prof

London

NULL

Adams

MSc

Bristol

gdyż “Clark” > “C”

#18

Operatory specjalne

(cd.)

LIKE

Porównuje zawartość kolumny znakowej z łańcuchem znaków, który może zawierać
symbole wieloznaczne (wildcards). Łańcuch ze znakami wieloznacznymi to wzorzec.
Symbole wieloznaczne:

_ (podkreślenie) - pasuje do dowolnego pojedynczego znaku,

% (procent)

- pasuje do dowolnej liczby dowolnych znaków.

Przykład:

1. Pokaż wszystkich studentów o nazwiskach rozpoczynających się na “J”:

SELECT SName

FROM Students
WHERE SName LIKE “J%”;

Wynik:

SNAME
Jones
Johnson

Uwaga: Podobne działanie ma operator MATCHES, jednak MATCHES pozwala stosować

znacznie bardziej złożone wzorce niż LIKE. Szczegóły - w dokumentacji konkretnego DBMS.

#19

Operatory specjalne

(cd.)

CONTAINS

Sprawdza, czy wartość w kolumnie znakowej zawiera wskazany łańcuch.

Przykład:

Pokaż wszystkich studentów, których nazwisko zawiera łańcuch "Mary":

SELECT SName

FROM Students
WHERE SName CONTAINS “Mary”;

Uwaga: Łańcuch użyty w operatorze CONTAINS może zawierać znaki specjalne

oznaczające OR i AND. Szczegóły - w dokumentacji konkretnego DBMS.

Przykład (Progress):

SELECT SName

FROM Students
WHERE SName CONTAINS “(Mary) | (Maria)”;

#20

Funkcje agreguj

COUNT()

COUNT(*)

- zwraca liczbę wierszy w tablicy wynikowej

COUNT(DISTINCT <nazwa kolumny>)

- zwraca liczbę różnych wartości we wskazanej kolumnie.

Przykłady:

1. Z ilu różnych miast pochodzą studenci?

SELECT COUNT(DISTINCT City)

FROM Students;

Wynik:

COUNT1

2. Ilu nauczycieli pochodzi z Londynu?

SELECT COUNT(*)

FROM Teachers
WHERE City = “London”;

Wynik:

COUNT1

#21

Funkcje agreguj

(cd.)

SUM([DISTINCT] <nazwa kolumny>)
MIN([DISTINCT] <nazwa kolumny>)
MAX([DISTINCT] <nazwa kolumny>)
AVG([DISTINCT] <nazwa kolumny>)

Zwracają odpowiednio: sumę, minimum, maksimum i średnią z wartości podanej kolumny.
Dla SUM() i AVG(), kolumna musi być numeryczna, dla MIN() i MAX() kolumna może być
też kolumną znakową lub daty.
Przykłady:

1. Podaj sumę godzin zajęć studenta S5.

SELECT SUM(Hours)

Wynik:

FROM TS

SUM1

WHERE SNo = “S5”;

168.00

2. Podaj minimum, maksimum i średnią godzin zajęć nauczyciela T3.

SELECT MIN(Hours), MAX(Hours), AVG(Hours)

FROM TS
WHERE TNo = “T3”;

Wynik:

MIN1

MAX2

AVG3

32.00

72.00

50.00

#22

SELECT

porz

dkowanie wynik

Bez frazy ORDER BY wyniki zapytania ukazują się w porządku nieokreślonym
(zwykle w porządku wstawienia ich do tablicy)

ORDER BY <nazwa kolumny/numer> [ASC/DESC]

[, <nazwa kolumny/numer> [ASC/DESC]... ]

Uwagi:

1.Numer kolumny we frazie ORDER BY oznacza pozycję kolumny na liście wyboru

instrukcji SELECT. Trzeba go użyć, jeśli porządkujemy wg wartości wyrażenia.

2. Domyślnie przyjmowany jest porządek rosnący (ASC).

Przykład 1.

Pokaż alfabetycznie dane o nauczycielach (nazwisko, identyfikator i tytuł.

SELECT TName, TNo,Title

FROM Teachers
ORDER BY Tname;

Wynik:

TNAME

TNO

TITLE

Adams

MSc

Blake

Prof.

Clark

PhD

Jones

Prof.

Smith

PhD

#23

SELECT

porz

dkowanie wynik

(cd.)

Przykład 2.

Pokaż dane (ident. studenta, liczba godzin, ident. nauczyciela) o zajęciach studentów
S1, S2 i S3, porządkując je rosnąco wg ident. studentów i malejąco wg liczby godzin.

SELECT SNo, Hours, TNo

FROM TS
WHERE SNo IN (“S1”, “S2”, “S3”)
ORDER BY SNo, Hours DESC;

Wynik:

SNO

HOURS

TNO

100

120

#24

SELECT

grupowanie

GROUP BY <nazwa kolumny>

[, <nazwa kolumny> ...]

[HAVING <warunek>]

Fraza ta powoduje powiązanie ze sobą wierszy wynikowych z instrukcji SELECT w grupy wierszy,
w których wskazane we frazie GROUP BY kolumny mają tę samą wartość. Następnie każda
grupa jest redukowana do pojedynczego wiersza. W tym wierszu występują kolumny z frazy
GROUP BY oraz ew. kolumny będące wynikiem funkcji agregujących. Funkcje te wówczas działają
na każdej grupie osobno. Następnie eliminowane są wiersze nie spełniające warunku HAVING.
Uwaga 1. Każda kolumna występująca we frazie GROUP BY musi wystąpić na liście wyboru

instrukcji SELECT, i na odwrót.

Uwaga 2. W warunku HAVING może wystąpić funkcja agregująca (ale nie w warunku WHERE!).

Przykład 1.

Dla każdego nauczyciela pokaż,
z iloma studentami miał zajęcia.

SELECT Tno, COUNT(*)

FROM TS
GROUP BY Tno;

Wynik

TNO

COUNT1

#25

SELECT

grupowanie

(cd.)

Przykład 2.

Dla każdego studenta oblicz sumę godzin jego zajęć. Pokaż tylko identyfikatory
tych studentów, dla których ta suma przekracza 160.
Wynik uporządkuj malejąco wg liczby godzin.

SELECT SNo, SUM(Hours)

FROM TS
GROUP BY SNo
HAVING SUM(Hours) > 160
ORDER BY 2 DESC;

Wynik:

SNO

SUM1

276

196

192

#26

SELECT

łą

czenia

Jeśli fraza FROM zawiera więcej niż jedną tablicę, instrukcja SELECT wykonuje produkt
kartezjański (operator TIMES) wskazanych tablic. Jeśli dodatkowo występuje fraza WHERE,
wykonywana jest operacja relacyjnego złączenia.

Przykład 1.

Pokaż informacje o wszystkich zajęciach nauczyciela T4, podając identyfikatory i nazwiska
studentów oraz liczby godzin. Wynik uporządkuj alfabetycznie wg nazwisk studentów.

SELECT SName, TS.SNo, Hours

FROM TS, Students
WHERE TS.SNo = Students.SNo AND TNo = “T4”
ORDER BY SName;

Wynik:

STUDENTS.SNAME TS.SNO

TS.HOURS

Ford

Henry

Johnson

#27

SELECT

łą

czenia

(cd.)

Przykład 2.

Pokaż wszystkie pary (nauczyciel, student) takie, że nauczyciel i student pochodzą z tego
samego miasta. Podaj identyfikatory i nazwiska studentów i nauczycieli. Wynik uporządkuj
alfabetycznie wg nazw miast, a potem wg nazwisk nauczycieli i studentów.

SELECT T.City, TName, TNo, SName, SNo

FROM Teachers T, Students S
WHERE T.City = S.City
ORDER BY T.City, TName, SName;

Wynik:

T.CITY

T.TNAME

T.TNO

S.SNAME

S.SNO

Bristol

Adams

Ford

London

Blake

Henry

London

Blake

Higgins

London

Jones

Henry

London

Jones

Higgins

Uwaga: S i T są aliasami tablic. W tym przykładzie służą tylko do skrócenia zapisu.

#28

SELECT

łą

czenia

(cd.)

Przykład 3.

Dla każdego nauczyciela pokaż jego nazwisko, identyfikator i sumę godzin zajęć.
Wynik uporządkuj alfabetycznie wg nazwisk.

SELECT TName, TS.TNo, SUM(Hours)

FROM Teachers T, TS
WHERE T.TNo = TS.TNo
GROUP BY TName, TS.TNo
ORDER BY TName;

Wynik:

T.TNAME

TS.TNO

SUM1

Adams

32.00

Blake

128.00

Clark

288.00

Jones

200.00

Smith

340.00

#29

SELECT

łą

czenia

(cd.)

Złączenie tablicy z tą samą tablicą nazywamy samozłączeniem.

Samozłączenie umożliwia wiązanie ze sobą informacji zawartych w różnych wierszach
jednej tablicy.

Do dokonania samozłączenia konieczne jest zastosowanie aliasów (nazw korelacji).

Przykład 4.

Dla każdego nauczyciela pokaż listę nauczycieli, którzy są jego podwładnymi.
Wynik uporządkuj alfabetycznie wg nazwisk szefów, a potem wg nazwisk podwładnych.

SELECT Over.TName, Over.TNo, “jest szefem dla”, Under.TName, Under.TNo

FROM Teachers Under, Teachers Over
WHERE Under.SupNo = Over.TNo
ORDER BY Over.TName, Under.TName;

Wynik:

OVER.TNAME

OVER.TNO

EXP1

UNDER.TNAME

UNDER.TNO

Blake

jest szefem dla

Jones

Blake

jest szefem dla

Smith

Clark

jest szefem dla

Adams

Jones

jest szefem dla

Clark

#30

SELECT

podzapytania

W podzapytaniu instrukcja SELECT jest zagnieżdżona we frazie WHERE lub HAVING.
Zagnieżdżona instrukcja SELECT, zwana zapytaniem wewnętrznym, dostarcza wartości
dla warunku WHERE lub HAVING. Instrukcja SELECT zawierająca podzapytanie to
zapytanie zewnętrzne.

Przykłady:
1. Pokaż dane o zajęciach nauczycieli pochodzących z Londynu.

SELECT *

FROM TS
WHERE TNo IN

(SELECT TNo

FROM Teachers

WHERE City = “London”) ;

2. Pokaż identyfikatory i nazwiska nauczycieli, którzy prowadzili chociaż jedne zajęcia,

które trwały ponad 64 godziny.

SELECT TNo, TName

FROM Teachers
WHERE TNo IN

(SELECT TNo

FROM TS

WHERE Hours > 64) ;

#31

SELECT

podzapytania

(cd.)

3. Pokaż identyfikatory i nazwiska nauczycieli, którzy prowadzili chociaż jedne zajęcia,

które trwały dłużej niż wynosi średnia liczba godzin na jedno zajęcie.

SELECT TNo, TName

FROM Teachers
WHERE TNo IN

(SELECT TNo

FROM TS

WHERE Hours >

(SELECT AVG(Hours)

FROM TS));

4. Pokaż nazwiska, identyfikatory i tytuły nauczycieli, dla których łączna liczba godzin zajęć

przekracza 200. Uporządkuj wynik alfabetycznie wg nazwisk.

SELECT TName, TNo, Title

FROM Teachers
WHERE TNo IN

(SELECT TNo

FROM TS
GROUP BY TNo
HAVING SUM(Hours) > 200)

ORDER BY TName;

#32

SELECT

podzapytania

(cd.)

5. Pokaż dane o zajęciach, w których nauczyciel lub student pochodzą z Londynu.

Uporządkuj wyniki wg identyfikatorów nauczycieli i studentów.

SELECT *

FROM TS
WHERE TNo IN

(SELECT TNo

FROM Teachers

WHERE City = “London”)

OR SNo IN

(SELECT SNo

FROM Students

WHERE City = “London”)

ORDER BY TNo, SNo;

#33

SELECT

podzapytania

(cd.)

W warunku WHERE może wystąpić operator EXISTS (predykat). EXISTS zwraca True,
jeśli podzapytanie zwraca choć jeden wiersz, a False w przeciwnym wypadku.

6. Pokaż dane o nauczycielach, którzy uczyli studenta S1.

SELECT *

FROM Teachers
WHERE EXISTS

(SELECT *

FROM TS

WHERE Teachers.TNo = TS.TNo AND

TS.SNo = “S1”);

7. Pokaż dane (ident. i nazwisko) studentów, których nie uczył nauczyciel T3.

SELECT SNo, SName

FROM Students
WHERE NOT EXISTS

(SELECT *

FROM TS
WHERE Students.SNo = TS.SNo AND

TS.TNo = “T3”);

#34

SELECT

podzapytania

(cd.)

8. Pokaż dane (ident., nazwisko, tytuł) o nauczycielach, którzy uczyli wszystkich studentów.

SELECT TNo, TName, Title

FROM Teachers
WHERE NOT EXISTS

(SELECT *

FROM Students
WHERE NOT EXISTS

(SELECT *

FROM TS
WHERE Teachers.TNo = TS.Tno AND

Students.SNo = TS.SNo));

#35

SELECT

podzapytania

(cd.)

9. Pokaż identyfikatory nauczycieli, którzy uczyli więcej niż jednego studenta.

SELECT DISTINCT TS1.TNo

FROM TS TS1
WHERE EXISTS

(SELECT *

FROM TS TS2

WHERE S1.TNo = TS2.TNo AND TS1.SNo <> TS2.SNo);

10. Pokaż nazwiska i identyfikatory tych nauczycieli, którzy są szefami.

SELECT Over.TName, Over.TNo

FROM Teachers Over
WHERE EXISTS

(SELECT *

FROM Teachers Under

WHERE Over.TNo = Under.SupNo);

Uwaga: Ten sam wynik można uzyskać za pomocą złączenia podobnego do tego

z przykładu 4 sekcji "SELECT - złączenia".

#36

Wstawianie danych z innych tablic

INSERT INTO <nazwa tablicy>

[( <lista kolumn> )]

< instrukcja SELECT >

Cel: Wstawienie do jednej tablicy wierszy wybranych z innej tablicy lub innych tablic.

Przykład:

Załóżmy, że tablica Students1 ma tę samą strukturę co tablica Students. Instrukcja:

INSERT INTO Students

SELECT *
FROM Students1
WHERE SYear > 1990;

wstawia do tablicy Students wszystkie wiersze z tablicy Students1 dotyczące studentów,
którzy rozpoczęli naukę po roku 1990.

#37

Zachowywanie wynik

w zapytania

Niektóre DBMS pozwalają na zachowywanie wyników instrukcji SELECT
w tablicy tymczasowej, przez zastosowanie frazy podobnej do poniższej:

INTO TEMP <nazwa tablicy> (<lista kolumn>)

Tablica tymczasowa jest tworzona przez instrukcję SELECT zgodnie z typami danych
umieszczonych na liście wyboru instrukcji SELECT. Tablica taka jest automatycznie
kasowana podczas zamykania bazy danych przez bieżącego użytkownika.

Tablica tymczasowa może być używana we frazach FROM następnych instrukcji SELECT.
W ten sposób można rozbijać skomplikowane zapytania na kilka etapów.

#38

Zachowywanie wynik

w zapytania

(cd.)

Przykład.

Pokaż nazwiska, identyfikatory i tytuły tych nauczycieli, którzy mieli zajęcia o łącznej długości
przekraczającej średnią łączną długość zajęć na jednego nauczyciela. Wynik uporządkuj

alfabetycznie wg nazwisk.

SELECT TNo, SUM(Hours)

FROM TS
GROUP BY TNo
INTO TEMP Tsum(TNo, HSum);

SELECT TName, TNo, Title

FROM Teachers
WHERE TNo IN

(SELECT TNo

FROM Tsum
WHERE Hsum >

(SELECT AVG(Hsum)

FROM Tsum));

ORDER BY TName;

Tablica TSUM zawiera sumy godzin zajęć dla każdego nauczyciela. W drugim zapytaniu
z tablicy TEACHERS wybieramy tylko tych nauczycieli, dla których suma godzin z tablicy
TSUM przekracza średnią z tych sum.

#39

Zachowywanie wynik

w zapytania

(cd.)

Niektóre systemy DBMS nie dają możliwości bezpośredniego zachowywania wyników
instrukcji SELECT w tablicy tymczasowej (tzn. składnia instrukcji SELECT nie zawiera
opcjonalnej frazy INTO TEMP lub podobnej). W takich wypadkach należy jawnie utworzyć
nową tablicę i zastosować “wsadową” wersję instrukcji INSERT:

SELECT ......

FROM ...
WHERE ...

....

INTO TEMP Tab1(kol1, kol2, ...);

CREATE TABLE Tab1

(kol1

typ1,

kol2

typ2, ......);

INSERT INTO Tab1

SELECT ......

FROM ...
WHERE ...;

Przykład:

CREATE TABLE Profes

(PNo

Char(4),

Pname

Char(12));

INSERT INTO Profes

SELECT TNo, Tname

FROM Teachers
WHERE Title = “Prof.”;

SELECT TNo, Tname

FROM Teachers
WHERE Title = “Prof.”
INTO TEMP Profes (PNo, PName);

#40

Aktualizowanie danych

UPDATE <nazwa tablicy>

SET <nazwa kolumny> = <wyrażenie>

[, <nazwa kolumny> = <wyrażenie> ...]

[ WHERE <warunek> ]

Cel: Zmiana wartości w kolumnach jednego lub wielu wierszy tablicy.

Przykłady:

1. Prof. Blake (ident. T1) zmienił przeniósł się z Londynu do Birmingham.

UPDATE Teachers

SET City = “Birmingham”
WHERE TNo = “T1”;

2. Okazało się, że pan Jones (ident. T3) miał ze wszystkimi swoimi studentami o 8 godzin więcej

niż to zostało poprzednio zapisane. Trzeba dokonać następującej aktualizacji:

UPDATE TS

SET Hours = Hours+8
WHERE Tno = “T3”;

3. Okazało się, że niektóre dane o studencie S3 zostały błędnie zapisane w bazie danych.

Trzeba zmienić wartość kilku pól.

UPDATE Students

SET Sname = “Johnsone”, Syear = 1984, City = “Diblan”
WHERE Sno = “S3”;

#41

Usuwanie danych

DELETE FROM <nazwa tablicy>

[ WHERE <warunek> ]

Cel: Usunięcie z tablicy jednego lub wielu wierszy.

Przykłady:

1. Prof. Blake (ident. T1) zmienił miejsce pracy. Należy usunąć zapis o nim z tablicy Teachers

(gdzie przechowujemy dane tylko o aktualnie zatrudnionych nauczycielach) i przenieść je
do tablicy Teachers1 (o strukturze takiej samej jak Teachers), pełniącej rolę archiwum.

INSERT INTO Teachers1

SELECT *
FROM Teachers
WHERE TNo = “T1”;

DELETE FROM Teachers

WHERE TNo = “T1”;

2. Przy końcu roku akademickiego przenosimy wszystkie rekordy z tablicy TS do TS1,

w której przechowujemy dane archiwalne z tablicy TS. Następnie czyścimy tablicę TS.

INSERT INTO TS1

SELECT *
FROM TS;

DELETE FROM TS;

#42

Sumowanie tablic

<instrukcja SELECT>
UNION [ALL]
<instrukcja SELECT>

Cel: Realizacja operacji algebry relacji UNION w stosunku do tablic będących wynikiem

dwóch lub więcej instrukcji SELECT. Tablice muszą być zgodne.

Uwagi:

1. Z tablicy wynikowej usuwane są wiersze powtarzające się (duplikaty), chyba że występuje ALL.
2. Jeśli nazwy kolumn sumowanych tablic są różne, tablica wynikowa przyjmuje nazwy

kolumn z pierwszej tablicy.

3. Jeśli po UNION występuje fraza ORDER BY, muszą w niej występować numery,

a nie nazwy kolumn.

Przykład:

Pokaż alfabetycznie nazwy miast,
skąd pochodzą studenci lub nauczyciele.

SELECT City

FROM Teachers

UNION
SELECT City

FROM Students

ORDER BY 1;

CITY
Adelaide
Bristol
Davos
Dublin
Glasgow
Liverpool
London

#43

Odejmowanie tablic

(SQL

92)

<instrukcja SELECT>
MINUS [ALL]
<instrukcja SELECT>

Cel: Realizacja operacji algebry relacji MINUS w stosunku do tablic będących wynikiem

dwóch lub więcej instrukcji SELECT. Tablice muszą być zgodne.

Uwagi:

1. Z tablicy wynikowej usuwane są wiersze powtarzające się (duplikaty), chyba że występuje ALL.
2. Jeśli nazwy kolumn sumowanych tablic są różne, tablica wynikowa przyjmuje nazwy

kolumn z pierwszej tablicy.

3. Jeśli po MINUS występuje fraza ORDER BY, muszą w niej występować numery,

a nie nazwy kolumn.

Przykład:

Pokaż alfabetycznie nazwy miast,
skąd pochodzą studenci, ale nie nauczyciele.

SELECT City

FROM Students

MINUS
SELECT City

FROM Teachers

ORDER BY 1;

CITY
Adelaide
Davos
Dublin

#44

Przecinanie tablic

(SQL

92)

<instrukcja SELECT>
INTERSECT [ALL]
<instrukcja SELECT>

Cel: Realizacja operacji algebry relacji INTERSECT w stosunku do tablic będących wynikiem

dwóch lub więcej instrukcji SELECT. Tablice muszą być zgodne.

Uwagi:

1. Z tablicy wynikowej usuwane są wiersze powtarzające się (duplikaty), chyba że występuje ALL.
2. Jeśli nazwy kolumn sumowanych tablic są różne, tablica wynikowa przyjmuje nazwy

kolumn z pierwszej tablicy.

3. Jeśli po INTERSECT występuje fraza ORDER BY, muszą w niej występować numery,

a nie nazwy kolumn.

Przykład:

Pokaż alfabetycznie nazwy miast, skąd
pochodzą zarówno studenci, jak i nauczyciele.

SELECT City

FROM Students

INTERSECT
SELECT City

FROM Teachers

ORDER BY 1;

CITY
Bristol
London

#45

Tworzenie indeks

CREATE [UNIQUE] INDEX <nazwa indeksu>

ON <nazwa tablicy> (<nazwa kolumny> [ASC/DESC]

[, <nazwa kolumny> [ASC/DESC]... ]);

Cel:

Tworzy indeks na określonych kolumnach, zwanych kluczami indeksu.
Domyślnie, indeks jest rosnący (ASC) .
Fraza UNIQUE oznacza, że kolumny indeksu zawierają wartości niepowtarzalne.

Przykłady:

CREATE UNIQUE INDEX TeachInd

ON Teachers (Tno );

CREATE UNIQUE INDEX TSInd

ON TS (Tno, SNo );

CREATE INDEX HrsInd

ON TS (Sno, Hours DESC);

#46

Tworzenie indeks

(cd.)

Co to jest indeks?

Jest to tablica zawierająca w jednej kolumnie wartości klucza, a w drugiej adresy
wierszy w indeksowanej tablicy, które zawierają te wartości.

Do czego służy?

1. Pozwala zwiększyć efektywność przetwarzania żądań dostępu do danych

z tablicy (instrukcji SELECT).

2. Porządkuje wiersze tablicy w określonym porządku.
3. Zapobiega powtarzaniu się wartości w wierszach tablicy.

Kiedy tworzyć indeks?

1. Gdy tablica jest często przetwarzana sekwencyjnie zgodnie z wartościami

pewnej kolumny (rosnąco lub malejąco).

2. Gdy konieczne jest zachowanie unikatowości wartości kolumn(y).
3. Gdy dostęp do tablicy jest często realizowany zgodnie z wartościami

danej kolumny lub kolumn (dostęp asocjacyjny).

Jaki jest koszt?

1. Koszt pamięciowy (utrzymywanie dodatkowych danych).
2. Koszt czasowy (konieczność aktualizacji indeksu podczas aktualizowania

tablicy (instrukcje INSERT, UPDATE, DELETE).

#47

Tworzenie indeks

przyk

ady

Przykład 1:

CREATE INDEX TSHours

ON TS (Hours DESC);

TSHours

Hours

RecNo

TNO

SNO

HOURS

RecNo

120

100

120

#48

Tworzenie indeks

przyk

ady

(cd.)

Przykład 2:

CREATE INDEX StudHrs

ON TS (Sno, Hours);

StudHrs

SNo+Hours RecNo

TNo

SNo

Hours

RecNo

S1 + 32

S1 + 64

S1 + 96

100

S2 + 36

120

S2 + 64

120

S2 + 100

S3 + 60

S3 + 96

S3 + 120

S4 + 120

S5 + 72

S5 + 96

S6 + 32

#49

Indeksy

bitmapowe

(Oracle)

Przykład:

CREATE BITMAP INDEX Titles

ON Teachers (Title);

Titles

Teachers

TNO

TNAME

TITLE

CITY

SUPNO

Rowid

Blake

Prof.

London

NULL

Smith

PhD

Glasgow

Jones

Prof.

London

Clark

PhD

Liverpool

Adams

MSc

Bristol

Rowid

1
2
3
4
5

0 0 1
0 1 0
0 0 1
0 1 0
1 0 0

MSc

PhD

Prof

Indeks bitmapowy warto stosować wtedy, gdy liczba różnych wartości indeksowanej kolumny
jest b. mała w stosunku do liczby wierszy tablicy i jest z góry znana (nie rośnie).
Typowym zastosowaniem są systemy przetwarzania danych w hurtowniach danych
(bardzo obszerne dane, ale niezmienne w czasie).

#50

Widoki

Widok (wirtualna tablica)

Kombinacja wierszy i kolumn wybranych z jednej lub kilku tablic. Po utworzeniu widoku można
na nim wykonywać instrukcje SELECT jak na zwykłej tablicy. W bazie danych (w słowniku danych)
pamiętana jest jedynie definicja widoku. Widok jest materializowany w razie potrzeby.

Korzy ści ze stosowania widoków:

• Te same dane mogą być widziane w różny sposób przez różnych użytkowników.
• Pozwalają koncentrować się ma danych istotnych, a ignorować inne.
• Wprowadzają dodatkowy poziom niezależności od danych: Jeśli zmieni się coś w schemacie

bazy danych, często wystarczy zmienić definicję widoku, nie zmieniając programu.

• Wprowadzają ochronę danych przez ich ukrycie: Użytkownicy "widzą" tylko widoki,

czyli tylko te dane, do których mają prawo dostępu.

Różnice między widokami a tablicami:

• Widoki nie zawierają danych; zawierają tylko informacje, jak zebrać te dane w razie potrzeby

(tzn. z jakich tablic, z jakich kolumn i w jaki sposób).

• Widoków nie można indeksować (ale można indeksować tablice wchodzące w skład widoku).
• W ogólności, nie można aktualizować danych poprzez widok.

#51

Tworzenie widok

CREATE VIEW <nazwa widoku>

[ <lista kolumn> ]

AS <instrukcja SELECT >
[WITH CHECK OPTION]

Cel: Utworzenie widoku (wirtualnej tablicy). Opcjonalna <lista kolumn> określa nazwy kolumn

widoku. Jeśli jej nie ma, nazwy kolumn pochodzą z instrukcji SELECT widoku.

WITH CHECK OPTION – Gwarantuje, że wszystkie aktualizacje widoku (jeśli widok jest
aktualizowalny) spełniają warunki występujące we frazie WHERE instrukcji SELECT.

Przykład 1. (widok aktualizowalny)

CREATE VIEW TopTeachers

AS SELECT TNo, TName, Title

FROM Teachers
WHERE Title <> 'MSc'

WITH CHECK OPTION;

SELECT *

FROM TopTeachers;

Wynik zapytania:

TNO

TNAME

TITLE

Blake

Prof.

Smith

PhD

Jones

Prof.

Clark

PhD

#52

Tworzenie widok

(cd.)

Przykład 2.

CREATE VIEW TSJoined

(TeachNo, TeachName, StudNo, StudName, TeachHours)
AS SELECT Teachers.TNo, Teachers.TName,

Students.SNo, Students.SName, TS.Hours

FROM TS, Teachers, Students
WHERE TS.TNo = Teachers.TNo AND TS.SNo = Students.Sno ;

SELECT *

FROM TSJoined
WHERE TeachHours >= 90 ;

Wynik zapytania:

TEACHNO

TEACHNAME

STUDNO

STUDNAME

TEACHHOURS

Smith

Jones

100

Smith

Johnson

120

Smith

Higgins

120

Clark

Henry

Clark

Johnson

Clark

Ford

Czy widok TSJoined jest aktualizowalny?

#53

Aktualizowanie widok

Widok TSJoined (przykład 2.) może nie być aktualizowalny. Zależy to od ograniczeń
na aktualizowanie widoków nałożonych przez konkretny DBMS

Przykładowe ograniczenia:

• Instrukcja SELECT nie zawiera fraz DISTINCT, GROUP BY, HAVING.
• Instrukcja SELECT nie zawiera na liście wyboru wyrażeń ani funkcji agregujących.
• We frazie FROM występuje tylko jedna tablica.
• We frazie WHERE nie występuje podzapytanie.
• Na liście kolumn widoku występuje klucz główny tablicy występującej we frazie FROM.

Szczegóły - w dokumentacji DBMS.

#54

Usuwanie tablic, indeks

w i widok

DROP TABLE <nazwa tablicy>

Cel: Skasowanie istniejącej tablicy. Kasowane są wszystkie dane z tablicy. Kasowane są także

wszystkie indeksy i widoki zdefiniowane na tej tablicy.

Przykład:

DROP TABLE Books;

DROP INDEX <nazwa indeksu>

Cel: Skasowanie istniejącego indeksu. Nie wpływa na indeksowaną tablicę.
Przykład:

DROP INDEX Hours;

DROP VIEW <nazwa widoku>

Cel: Skasowanie istniejącego widoku. Nie wpływa na tablice występujące w definicji widoku.
Przykład:

DROP VIEW MyView;

#55

łą

czenia zewn

trzne

W złączeniu wewnętrznym w wyniku zwracane są zawsze wiersze z obu tablic (lewej i prawej).
Wiersze w jednej tablicy, które nie mają swoich odpowiedników w drugiej tablicy, są odrzucane.
W dowolnym złączeniu zewnętrznym zwracane są wszystkie wiersze zwracane przez
odpowiednie złączenie wewnętrzne. Ponadto:

• lewe złączenie zewnętrzne – Dla każdego wiersza wybranego z lewej tablicy,

dla którego nie istnieje odpowiedni wiersz w prawej tablicy, zamiast wiersza
z prawej tablicy zwracany jest wiersz złożony z wartości pustych .
Innymi słowy: Zwracane są wszystkie wiersze z lewej tablicy, nawet te, do których nie
pasują żadne wiersze z prawej tablicy.

• prawe złączenie zewnętrzne – Dla każdego wiersza wybranego z prawej tablicy,

dla którego nie istnieje odpowiedni wiersz w lewej tablicy, zamiast wiersza
z lewej tablicy zwracany jest wiersz złożony z wartości pustych .
Innymi słowy: Zwracane są wszystkie wiersze z prawej tablicy, nawet te, do których nie
pasują żadne wiersze z lewej tablicy.

• pełne złączenie zewnętrzne – Zwraca wyniki lewego i prawego złączenia zewnętrznego

(czyli wynik operacji UNION dla wyników lewego i prawego złączenia zewnętrznego).

Uwaga 1: DBMS może wprowadzać różne ograniczenia dotyczące złączeń zewnętrznych.

Szczegóły - w dokumentacji konkretnego DBMS.

Uwaga 2: Złączenia zewnętrzne nie są przemienne!

#56

łą

czenia zewn

trzne

przyk

ady

Przykład lewego złączenia zewnętrznego:

Tablica1

Tablica2

C11

C12

Left Outer

C21

C22

Join

on C11=C21

Wynik:

C11

C12

C21

C22

SQL:

SELECT *
FROM Tablica1 LEFT JOIN Tablica2

ON C11 = C21

SELECT *
FROM Table1 LEFT JOIN Table2
WHERE C11 = C21

#57

łą

czenia zewn

trzne

przyk

ady

(cd.)

Przykład prawego złączenia zewnętrznego:

Tablica1

Tablica2

C11

C12

Right Outer

C21

C22

Join

on C11=C21

Wynik:

C11

C12

C21

C22

SQL:

SELECT *
FROM Tablica1 RIGHT JOIN Tablica2

ON C11 = C21

SELECT *
FROM Table1 RIGHT JOIN Table2
WHERE C11 = C21

#58

łą

czenia zewn

trzne

przyk

ady

(cd.)

Inny przykład:

SELECT T.City, TName, TNo, SName, SNo

FROM Teachers T LEFT JOIN Students S

ON T.City = S.City

WHERE Title <> 'MSc';

Wynik:

T.CITY

TNAME TNO

SNAME

SNO

London

Blake

Henry

London

Blake

Higgins

London

Jones

Henry

London

Jones

Higgins

Glasgow

Smith

Liverpool

Clark

#59

NOT IN i NOT EXISTS a z

łą

czenia

zewn

trzne

Załóżmy, że tablice table1 i table2 mają kolumny id.

SELECT *

FROM table1
WHERE id NOT IN

(SELECT id

FROM table2);

SELECT *

FROM table1
WHERE NOT EXISTS

(SELECT id

FROM table2
WHERE table1.id=table2.id);

SELECT table1.*

FROM table1 LEFT JOIN table2

ON table1.id=table2.id

WHERE table2.id IS NULL

#60

NOT IN i NOT EXISTS a z

łą

czenia

zewn

trzne

przyk

Przykład:

Pokaż dane o nauczycielach, którzy pochodzą z takich miast, z których nie
pochodzą studenci.

SELECT T.*

FROM Teachers T LEFT JOIN Students S

ON T.City = S.City

WHERE S.Sno IS NULL;

Wynik:

TNO TNAME TITLE CITY SUPNO

T2 Smith

PhD Glasgow T1

T4 Clark

PhD Liverpool T3

#61

Kursory

Kursor– wskaźnik służący do sekwencyjnego poruszania się po zbiorze wynikowym zapytania.

W danym momencie wskazuje na jeden wiersz ze zbioru wynikowego.

Kursory są elementem zanurzonego języka SQL. Umożliwiają:

• sekwencyjne przetwarzanie przez program wyników zapytania;
• pobieranie wartości kolumn z wierszy pobranych przy użyciu instrukcji SELECT;

• przypisywanie zmiennym programowym wartości pobranych z kolumn.

Po ustalenia pozycji kursora na wierszu można:

• uaktualnić wiersz
• usunąć wiersz

• przetworzyć wiersz (np. wyświetlić go w sposób niestandardowy)

Fazy użycia kursora:

1. Deklaracja kursora:

DECLARE CURSOR

2. Otwarcie kursora:

OPEN

3. Pobranie danych:

FETCH

i po zakończeniu operowania kursorem:

4. Zamknięcie kursora:

Raz zadeklarowany kursor można otwierać i zamykać wiele razy.

#62

Kursory

(cd.)

Instrukcja

Opis

DECLARE CURSOR

Kojarzy nazwę kursora z instrukcją SELECT

OPEN

Wybiera wszystkie wiersze, które spełniają
warunki instrukcji DECLARE CURSOR, po
czym ustawia kursor na pozycję przed
pierwszym wierszem.

FETCH

Pobiera kolejny wiersz i przypisuje wartości z
kolumn do zmiennych programowych.
Ustawia kursor na pozycji przed następnym
wierszem lub, jeśli nie ma następnego
wiersza, po ostatnim wierszu.

UPDATE (pozycyjna)

Modyfikuje wiersz, na który wskazuje kursor.

DELETE (pozycyjna)

Usuwa wiersz, na który wskazuje kursor.

Zamyka kursor.

#63

Kursory

(cd.)

DECLARE <nazwa kursora> CURSOR FOR <instrukcja SELECT>

[FOR {READ [ONLY]|UPDATE}]

nazwa kursora - określa nazwę nowego kursora.
instrukcja SELECT– określa zbiór wierszy, który będzie dostępny po otwarciu kursora

FOR READ [ONLY] – określa, że wiersze pobierane przez kursor, nie mogą być modyfikowane.

FOR UPDATE – określa, że wiersze pobierane przez kursor mogą być modyfikowane

(o ile instrukcja-SELECT nie zawiera UNION).

Przykład:

DECLARE c01 CURSOR FOR

SELECT *
FROM Teachers
WHERE City = "London”;

#64

Kursory

(cd.)

OPEN <nazwa kursora>

nazwa kursora – nazwa kursora określona w instrukcji DECLARE CURSOR.

Przykład:

OPEN c01;

CLOSE <nazwa kursora>

nazwa kursora – nazwa otwartego kursora.

Przykład:

CLOSE c01;

Uwaga: Po ponownym otwarciu kursora system pobiera od nowa wynikowy zbiór wierszy,

który może różnić się od zbioru poprzedniego.

#65

Kursory

(cd.)

FETCH <nazwa kursora> INTO <lista zmiennych>

nazwa kursora – nazwa kursora zadeklarowanego instrukcją DECLARE CURSOR

i otwartego instrukcją OPEN.

lista zmiennych – lista zmiennych programowych, do których są przekazywane wartości kolumn.

Przykład:

FETCH c01

INTO numberv, namev, titlev, cityv, supnov;

#66

Kursory

(cd.)

DELETE FROM <nazwa tablicy>

WHERE CURRENT OF <nazwa kursora>

UPDATE <nazwa tablicy>

SET <nazwa kolumny> = { NULL | <wyrażenie> }
[, <nazwa kolumny> = { NULL | <wyrażenie> }]...
WHERE CURRENT OF <nazwa kursora>

Fraza WHERE CURRENT OF nazwa-kursora identyfikuje kursor wskazujący na wiersz,
który ma zostać usunięty lub zaktualizowany.

Przykład 1: (Progress SQL/4GL)

DEFINE VARIABLE tnovar LIKE TS.Tno.
DEFINE VARIABLE hoursvar LIKE TS.Hours.
DECLARE c1 CURSOR FOR

SELECT Tno, Hours
FROM TS WHERE Tno = T1.

OPEN c1.
REPEAT:

FETCH c1 INTO tnovar, hoursvar.
UPDATE TS

SET Hours = hoursvar + 12 WHERE CURRENT OF c1.

END.
CLOSE c1.

#67

Kursory

(cd.)

Przykład 2: (Progress SQL/4GL)

/* zdefiniuj potrzebne zmienne */

DEF VAR name LIKE Teachers.Tname.
DEF VAR hour LIKE TS.Hours.
DEF VAR name1 AS CHARACTER.

/* zadeklaruj kursor */

DECLARE c1 CURSOR FOR

SELECT Tname, Hours

FROM Teachers, TS
WHERE Teachers.Tno = TS.Tno.

/* otwórz kursor*/

OPEN c1.

/* przeczytaj daną od użytkownika*/

SET name1.

/* przetwarzaj wiersze wynikowe */

REPEAT:

FETCH c1 INTO name, hour.

/* wyświetl wiersze o wartości kolumny Tname podanej przez użytkownika */

IF name = name1 THEN

DISPLAY name hour.

END.

/* zamknij kursor */

CLOSE c1.

#68

Autoinkrementacja

(

MySQL

)

1. Utworzenie dwóch tablic z kolumnami AUTO_INCREMENT typu

SMALLINT UNSIGNED.

CREATE TABLE Persons

( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

name CHAR(60) NOT NULL,
PRIMARY KEY (id) );

CREATE TABLE Shirts

( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id) );

#69

Autoinkrementacja

(

2. Wstawienie danych do tablic Persons i Shirts.

INSERT INTO Persons

VALUES (NULL, 'Antonio Paz');

INSERT INTO Shirts

VALUES (NULL, 'polo', 'blue', LAST_INSERT_ID()),

(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO Persons

VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO Shirts

VALUES (NULL, 'dress', 'orange', LAST_INSERT_ID()),

(NULL, 'polo', 'red', LAST_INSERT_ID());

#70

Autoinkrementacja

(

3. Odczytanie zawartości tablic Persons i Shirts.

SELECT * FROM Persons;

+----+---------------------+
| id | name

+----+---------------------+
| 1 | Antonio Paz

| 2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM Shirts;

+----+---------+--------+-------+
| id | style | color

| owner |

+----+---------+--------+-------+
| 1 | polo | blue

| 1 |

| 2 | dress

| white

| 1 |

| 3 | t-shirt | blue

| 1 |

| 4 | dress

| orange | 2 |

| 5 | polo | red | 2 |
+----+---------+--------+-------+

#71

Pseudokolumny

(Oracle)

Pseudokolumna może wystąpić na liście wyboru SELECT, choć nie jest przechowywana
w tablicy.

ROWNUM - numer kolejny wiersza zwróconego przez SELECT; 1, 2, ...
ROWID - fizyczny adres wiersza w tablicy
LEVEL

- numer poziomu wiersza w wyniku zapytania hierarchicznego; 1, 2, ...

CURRVAL, NEXTVAL - wartości zwracane przez sekwencje

Przykłady:

1. SELECT *

FROM Teachers
WHERE Rownum > 1;

2. SELECT *

FROM Teachers
WHERE Rownum < 3;

To zapytanie nie zwróci żadnego wiersza!

A to zwróci maks. 2 wiersze

#72

Rownum

a porz

dkowanie wynik

Przykład:

Pokaż te wiersze z tablicy TS, które zawierają 8 największych wartości kolumny Hours.

Rozwiązanie niepoprawne (wynik nieokreślony):

SELECT *

FROM TS
WHERE Rownum <= 8
ORDER BY Hours DESC;

Rozwiązanie poprawne:

SELECT *

FROM (SELECT *

FROM TS
ORDER BY Hours DESC)

WHERE Rownum <= 8

Przykład wyrażenia tablicowego

#73

Zapytania hierarchiczne

(Oracle)

[START WITH warunek]

CONNECT BY warunek

Pozwala wydobywać wiersze z relacji w porządku hierarchicznym.
Stosuje się do relacji, w których wiersze powiązane są ze sobą rekursywnym związkiem

typu „jeden do wiele”. Taką relację można reprezentować jako las (w szczególnym
przypadku - drzewo).

Kolejność wydobywania wierszy z relacji przez zapytanie hierarchiczne („depth first”):

#74

Zapytania hierarchiczne

(cd.)

START WITH określa warunek, przy którym dany wiersz traktowany jest jako korzeń drzewa.
CONNECT BY określa warunek określający związek rodzic-potomek w drzewie.

W tym warunku słowo PRIOR odnosi się do wiersza-poprzednika (rodzica).

Przykład 1:

Pokaż wszystkich nauczycieli w porządku hierarchicznym.

SELECT LPAD(‘ ‘, 2*(LEVEL-1)) || Tname AS “Schemat”, Tno, Title, City

FROM Teachers
START WITH SupNo IS NULL
CONNECT BY PRIOR Tno = SupNo;

Schemat

Tno

Title

City

Blake

Prof.

London

Smith

PhD

Glasgow

Jones

Prof.

London

Clark

PhD

Liverpool

Adams

MSc

Bristol

#75

Zapytania hierarchiczne

(cd.)

Przykład 2:

Pokaż wszystkich nauczycieli, którzy - pośrednio lub bezpośrednio - są podwładnymi
nauczyciela T3.

SELECT Tname, Tno, Title, City

FROM Teachers
WHERE Tno <> “T3”
START WITH Tno = “T3”
CONNECT BY PRIOR Tno = SupNo;

Tname

Tno

Title

City

Clark

PhD

Liverpool

Adams

MSc

Bristol

Uwaga:

Jednoczesne użycie frazy ORDER BY zakłóci hierarchiczny porządek wierszy w wyniku zapytania.

#76

Wstawianie danych z innych tablic

(Oracle)

Operację przepisania danych z jednej tablicy do drugiej można połączyć z utworzeniem tej
drugiej tablicy. Schemat tej tworzonej tablicy wynika z listy wyboru instrukcji SELECT.

Przykład:

CREATE TABLE Students1 AS

SELECT *
FROM Students
WHERE SYear > 1990;

Ograniczenia (np.PRIMARY KEY, UNIQUE, NOT ULL lub inne) można
do tak utworzonej tablicy dodać później, instrukcją

ALTER TABLE tablica ADD CONSTRAINT ograniczenie

Np:

ALTER TABLE Students1

ADD CONSTRAINT kl_gl PRIMARY KEY (Sno);

#77

Wstawianie danych z innych tablic

(

Przykład 1.

alfabetycznie wg nazwisk nauczycieli.

CREATE TABLE Tsum AS

SELECT Tno, SUM(Hours) Hsum

FROM TS
GROUP BY Tno;

SELECT Tname, Tno, Title

FROM Teachers
WHERE Tno IN

(SELECT Tno

FROM Tsum
WHERE Hsum >

(SELECT AVG(Hsum)

FROM Tsum))

ORDER BY Tname;

DROP TABLE Tsum;

#78

Wyra

enia tablicowe

(Oracle)

Rozbijania skomplikowanych zapytań na kilka prostszych można uniknąć, stosując
wyrażenia tablicowe we frazie FROM:

Przykład 1a:

CREATE TABLE Tsum AS

SELECT Tno, SUM(Hours) Hsum

FROM TS

GROUP BY Tno;

SELECT Tname, Tno, Title

FROM Teachers

WHERE Tno IN

(SELECT Tno

FROM Tsum
WHERE Hsum >

(SELECT AVG(Hsum)

FROM Tsum))

ORDER BY Tname;

DROP TABLE Tsum;

SELECT Tname, Tno, Title

FROM Teachers

WHERE Tno IN

(SELECT Tsum.Tno

FROM

(SELECT Tno, SUM(Hours) Hsum

FROM TS

GROUP BY Tno) Tsum

WHERE Hsum >

(SELECT AVG(Hsum)

FROM Tsum))

ORDER BY Tname;

#79

Wyra

enia tablicowe

(cd.)

Przykład 2

Podaj procentowy udział każdego nauczyciela w łącznej liczbie zajęć i w łącznej sumie
godzin zajęć. Uporządkuj alfabetycznie wg identyfikatorów nauczycieli.

CREATE TABLE Total AS

SELECT COUNT(*) Ile, SUM(Hours) Suma

FROM TS;

SELECT Tno, COUNT(*) / Ile*100 “% zajęć”, SUM(Hours) / Suma*100 “% godzin”

FROM TS, Total
GROUP BY Tno
ORDER BY Tno;

DROP TABLE Total;

SELECT a.Tno, a.Liczba / b.Ile*100 “% zajęć”, a.Godz / b.Suma “% godzin”

FROM

(SELECT Tno, COUNT(*) Liczba, SUM(Hours) Godz

FROM TS
GROUP BY Tno) a,

(SELECT COUNT(*) Ile, SUM(Hours) Suma

FROM TS) b

ORDER BY a.Tno;

#80

Sekwencje

(Oracle)

CREATE SEQUENCE <nazwa sekwencji>

[START WITH wartość]
[INCREMENT BY wartość]
[MAXVALUE wartość]
[MINVALUE wartość]
[CYCLE | NOCYCLE]

Cel: Utworzenie obiektu typu sekwencja, służącego do automatycznego generowania

unikatowych wartości całkowitych (np. dla kluczy głównych) relacji.

Można określić:

• wartość początkową (START WITH); domyślnie: 1
• przyrost (INCREMENT BY); domyślnie: 1
• wartość maksymalną (MAXVALUE, jeśli przyrost jest dodatni); domyślnie: brak
• wartość minimalną (MINVALUE, jeśli przyrost jest ujemny); domyślnie: brak
• to, czy sekwencja ma mieć charakter cykliczny (CYCLE, jeśli podano

MAXVALUE lub MINVALUE); domyślnie: nie (NOCYCLE)

Sekwencja ma 2 pseudokolumny:

NEXTVAL - wartość następna (pobranie powoduje inkrementację)
CURRVAL - wartość bieżąca

#81

Sekwencje

(

Przykład:

CREATE SEQUENCE numer_zam

START WITH 2 INCREMENT BY 2 MAXVALUE 100000;

...

INSERT INTO Zamowienia (numer, klient, data)

VALUES (numer_zam.NEXTVAL, ‘Jan Nowak’, SYSDATE);

INSERT INTO PozZam (numer_zam, lp, towar, ilosc)

VALUES (numer_zam.CURRVAL, 1, ‘dysk’, 20);

INSERT INTO PozZam (numer_zam, lp, towar, ilosc)

VALUES (numer_zam.CURRVAL, 2, ‘cd-rom’, 120);

INSERT INTO PozZam (numer_zam, lp, towar, ilosc)

VALUES (numer_zam.CURRVAL, 3, ‘toner’, 2);

Uwaga:

Na bieżącą wartość sekwencji (CURRVAL) jednego użytkownika
nie wpływa wywołanie NEXTVAL na tej samej sekwencji przez innego użytkownika.

#82

Relacyjna zupe

ść

SQL

Definicja

Język zapytań jest relacyjnie zupełny, jeśli dla dowolnego wyrażenia algebry relacji
istnieje równoważne mu wyrażenie tego języka.

SQL jest relacyjnie zupełny

Niech R i S są relacjami o atrybutach odpowiednio: A

..., A

i B

, B

, ..., B

Załóżmy dodatkowo, że dla operatorów UNION i MINUS relacje R i S są zgodne.

Algebra relacji

SQL

R UNION S

SELECT *

FROM R

UNION
SELECT *

FROM S

#83

Relacyjna zupe

ść

SQL

(cd.)

Algebra relacji

SQL

R MINUS S

SELECT *

FROM R
WHERE NOT EXISTS

(SELECT *

FROM S
WHERE R.A

= S.B

AND

R.A

= S.B

AND ...

R.A

= S.B

)

R TIMES S

SELECT *

FROM R, S

SELECT R WHERE warunek

SELECT *

FROM R
WHERE warunek

PROJECT R OVER A

...

SELECT A

, A

, ...

FROM R

#84

5. Normalizacja relacyjnych

baz danych

•

Redundancja danych

•

Druga posta

normalna (2NF)

•

Trzecia posta

normalna (3NF)

•

Czwarta posta

normalna (4NF)

#85

Normalizacja

przegl

PARTICIPANTS (IDENT, NAME, CITY, INHAB, COURSE, GRADE)

Znaczenie: Student identyfikowany przez IDENT, o nazwisku NAME, pochodzący z miasta CITY
o liczbie mieszkańców INHAB ukończył kurs COURSE z oceną GRADE.

PARTICIPANTS

IDENT

NAME

CITY

INHAB

COURSE

GRADE

Collins

London

8000000

English

Collins

London

8000000

Geography C

Collins

London

8000000

Logics

Jones

Glasgow

400000

Geography B

Jones

Glasgow

400000

Databases

Rodin

Aberdeen

400000

Physics

Thatcher

London

8000000

Logics

Thatcher

London

8000000

Chemistry

Biggs

Bristol

800000

Databases

Biggs

Bristol

800000

English

Biggs

Bristol

800000

Biology

#86

Normalizacja

przegl

(cd.)

Tablica PARTICIPANTS wykazuje wiele niepożądanych cech wynikających z
redundancji (wiele danych jest powtórzonych). Na przykład:

• Jeśli chcemy zmienić liczbę mieszkańców miasta, musimy to uczynić w wielu
wierszach (inaczej baza danych utraci integralność)
• Jeśli usuwamy rekord (np. dane o kursancie P3 Rodin), możemy jako efekt uboczny

utracić pewne inne informacje (w tym wypadku informację o liczbie
mieszkańców miasta Aberdeen).

• Aby dodać informacje o nowym kursie, który ukończył dany kursant, musimy też

dodać informacje, które już istnieją w tablicy PARTICIPANTS:
nazwisko kursanta oraz nazwę i liczbę mieszkańców miasta, skąd pochodzi.

Są to anomalie aktualizacji, powodowane przez redundancję danych.

Celem normalizacji jest usunięcie redundancji tak, by jedna informacja była
przechowywana w bazie danych tylko jeden raz.

#87

Zale

ci funkcjonalne

R - relacja, X ,Y - różne atrybuty relacji R (być może złożone).

Definicja

Atrybut Y zależy funkcjonalnie od atrybutu X (oznaczenie: X -> Y)

⇔

w każdym momencie dla każdej wartości X istnieje dokładnie jedna wartość Y.

Innymi słowy, jeśli istnieją w R dwie krotki z tą samą wartością X, to mają też tę samą wartość Y.

Diagramy F-D ilustrują zależności funkcjonalne. Diagram F-D dla relacji PARTICIPANTS:

IDENT

COURSE

CITY

NAME

GRADE

INHAB

Zależności funkcjonalne:

IDENT -> CITY
IDENT -> NAME
IDENT -> INHAB
CITY -> INHAB
(IDENT, COURSE) -> GRADE

#88

Druga posta

normalna

Relacja PARTICIPANTS zawiera także inne zależności funkcjonalne, np.:

(IDENT, COURSE) -> NAME
(IDENT, CITY) -> INHAB

zależności niepełne

Definicja

Atrybut Y zależy w pełni funkcjonalnie od atrybutu X (oznaczenie: X -> Y)

⇔

Y zależy funkcjonalnie od X i nie zależy funkcjonalnie od żadnego podzbioru właściwego X.

Definicja

Relacja jest w drugiej postaci normalnej (2NF)

⇔

• jest w 1NF,
• każdy atrybut niekluczowy zależy w pełni funkcjonalnie od klucza głównego.

Relacja PARTICIPANTS zawiera następujące niepełne zależności funkcjonalne:

IDENT -> NAME
IDENT -> CITY
IDENT -> INHAB

zatem nie jest w 2NF.

#89

Druga posta

normalna

przyk

Relację PARTICIPANTS możemy sprowadzić do 2NF droga dekompozycji (projekcji) na dwie relacje:

PART_COURSE (IDENT REF PART_DATA, COURSE, GRADE)
PART_DATA (IDENT, NAME, CITY, INHAB)

Diagramy F-D:

PART_DATA

PART_COURSE

IDENT

COURSE

GRADE

IDENT

CITY

NAME

INHAB

#90

Druga posta

normalna

przyk

(cd.)

PART_DATA

PART_COURSE

IDENT

NAME

CITY

INHAB

Collins

London

8000000

Jones

Glasgow

400000

Rodin

Aberdeen

400000

Thatcher

London

8000000

Biggs

Bristol

800000

IDENT

COURSE

GRADE

English

Geography C

Logics

Geography B

Databases

Physics

Logics

Chemistry

Databases

English

Biology

Relacja PART_DATA nadal wykazuje pewną redundancję: Jeśli kilku uczestników pochodzi z tego
samego miasta, liczba mieszkańców będzie się powtarzać.

Przyczyną tego jest fakt, że atrybut INHAB zależy funkcjonalnie od atrybutu niekluczowego CITY.

#91

Trzecia posta

normalna

Definicja

Zależność funkcjonalna X -> Y jest przechodnia

⇔

istnieje atrybut Z (Z

≠ X, Z ≠ Y) taki, że X -> Z i Z -> Y.

W relacji PART_DATA: X to IDENT, Y to INHAB, Z to CITY. W tej relacji istnieje zatem
przechodnia zależność funkcjonalna atrybutu INHAB od IDENT. Narusza to definicję 3NF:

Definicja

Relacja jest w trzeciej postaci normalnej (3NF)

⇔

• jest w 2NF,
• żaden atrybut niekluczowy nie zależy przechodnio od od klucza głównego.

IDENT

CITY

NAME

INHAB

ta część diagramu F-D
narusza 3NF

#92

Trzecia posta

normalna

przyk

Relację PART_DATA możemy sprowadzić do 3NF drogą dekompozycji (projekcji) na dwie relacje:

PART_ID (IDENT, NAME, CITY REF CITIES)
CITIES (CITY, INHAB)

Końcowe diagramy F-D:

PART_ID

IDENT

CITY

NAME

PART_COURSE

IDENT

COURSE

GRADE

CITIES

CITY

INHAB

#93

Trzecia posta

normalna

przyk

(cd.)

PART_ID

PART_COURSE

IDENT

COURSE

GRADE

English

Geography C

Logics

Geography B

Databases

Physics

Logics

Chemistry

Databases

English

Biology

IDENT

NAME

CITY

Collins

London

Jones

Glasgow

Rodin

Aberdeen

Thatcher

London

Biggs

Bristol

CITIES

CITY

INHAB

London

8000000

Glasgow

400000

Aberdeen

400000

Bristol

800000

#94

Posta

normalna

Boyce'a

Codda

Przykład:

OSOBY(Pesel, Paszport, Nazwisko)

Załóżmy, że jedna osoba może mieć tylko jeden paszport.
Zatem w realcji OSOBY istnieją 2 klucze kandydujące: Pesel i Paszport.

Diagram zależności funkcjonalnych:

Pesel

Paszport

Nazwisko

W relacji OSOBY Istnieją zależności przechodnie, niezależnie od wyboru klucza głównego:

Paszport -> Pesel -> Nazwisko
Pesel -> Paszport -> Nazwisko

Ale czy jest tu jakaś redundancja?

#95

Posta

normalna

Boyce'a

Codda

(cd.)

Wyznacznik relacji

Atrybut relacji (być może – złożony), od którego w pełni funkcjonalnie zależy inny
atrybut tej relacji.

Wyznacznik

Atrybut

Definicja postaci normalnej Boyce'a-Codda:

Relacja jest w postaci normalnej Boyce'a-Codda (BCNF) wtedy i tylko wtedy, gdy
każdy wyznacznik jest kluczem kandydującym.

W naszym przykładzie postać BCNF jest spełniona:

Pesel

Paszport

Nazwisko

#96

Posta

normalna

Boyce'a

Codda

(

Postać BCNF jest silniejsza niż 2NF i 3NF:

Jeśli relacja spełnia BCNF, to spełnia 2NFi 3NF, ale nie na odwrót.

Przykład relacji relacji w 3NF, która nie jest w BCNF:

KODY_POCZTOWE (MIASTO, ULICA, KOD_POCZTOWY)

MIASTO

ULICA

KOD

POCZTOWY

KODY_POCZTOWE

#97

Zale

ci wielowarto

ciowe

Relacja:

PERSONS (SSN, LANGUAGE, SPORT)

Znaczenie: Osoba identyfikowana przez SSN zna język LANGUAGE i uprawia sport SPORT.

Jedna osoba może znać wiele języków i uprawiać wiele sportów.

Klucz główny: (SSN, LANGUAGE, SPORT)

Niech osoba SSN = P1 zna angielski, fiński i francuski oraz uprawia piłkę nożną i narciarstwo.
Oto niektóre możliwe postaci relacji PERSONS:

PERSONS (1)

PERSONS (2)

SSN

LANGUAGE

SPORT

English

football

Finnish

football

French

football

English

skiing

Finnish

skiing

French

skiing

SSN

LANGUAGE

SPORT

English

football

Finnish

football

French

skiing

Obie tablice są w 3NF (bo klucz główny składa się z wszystkich atrybutów), ale:
• W pierwszej tablicy występuje spora redundancja danych. Powoduje to problemy przy dodawaniu

i usuwaniu informacji o językach i sportach.

• W drugiej tablicy jest mniej redundancji, ale jeśli np. osoba P1 przestanie uprawiać narciarstwo,

nie możemy zwyczajnie usunąć odpowiedniego wiersza.

Powód anomalii: relacja PERSONS zawiera dwie zależności wielowartościowe.

#98

Zale

ci wielowarto

ciowe

(cd.)

R - relacja, X ,Y, Z - różne atrybuty relacji R (być może złożone).

Definicja

Pomiędzy atrybutami X i Y zachodzi zależność wielowartościowa (oznaczenie: X ->-> Y)
⇔ dla każdej wartości X istnieje zbiór możliwych wartości Y i ten zbiór nie zależy od Z.

W relacji PERSONS:

SSN ->-> LANGUAGE (bo znajomość języków nie zależy od uprawianych sportów)
SSN ->-> SPORT

(bo uprawiane sporty nie zależą od znajomości języków)

Diagram F-D:

SSN

Language

Sport

#99

Zale

ci wielowarto

ciowe

(cd.)

Zastrzeżenie o niezależności od innego atrybutu jest bardzo ważne. Rozważmy przykład:

PERS_LAN (SSN, LANGUAGE, HOURS)

o znaczeniu: Osoba identyfikowana przez SSN zna język LANGUAGE, którego się uczyła przez

HOURS godzin. Jedna osoba może znać wiele języków.

Występuje tu tylko jedna zależność wielowartościowa:

SSN ->-> LANGUAGE

Nie ma takiej zależności pomiędzy SSN i HOURS, gdyż HOURS zależy od (SSN, LANGUAGE).

Diagram F-D:

Language

Hours

SSN

#100

Czwarta posta

normalna

Definicja

Relacja jest w czwartej postaci normalnej (4NF)

⇔

•

jest w trzeciej postaci normalnej (3NF),

•

nie zawiera dwóch lub więcej zależności wielowartościowych.

Relacja PERSONS może być sprowadzona do 4NF drogą dekompozycji (projekcji) na 2 relacje:

PER_LAN (SSN, LANGUAGE)
PER_SPORT (SSN, SPORT).

Powstałe tablice wyglądają następująco:

PER_LAN

PER_SPORT

SSN

LANGUAGE

English

Finnish

French

SSN

SPORT

football

skiing

#101

Normalizacja

podsumowanie

Dobrze zaprojektowana relacja składa się z klucza głównego (prostego lub złożonego) i
z pewnej liczby niezależnych od siebie atrybutów. Każdy atrybut zależy tylko od całego klucza głównego.

2NF dotyczy relacji ze złożonym kluczem głównym. Wymaga, by żaden atrybut niekluczowy nie zależał

od części klucza głównego.

3NF wymaga, by każdy atrybut niekluczowy zależał tylko od klucza głównego.

4NF dotyczy relacji ze złożonym kluczem głównym. Wymaga, by relacja zawierała co najwyżej

jedną zależność wielowartościową.

BCNF odpowiada 2NF i 3NF dla relacji o kilku kluczach kandydujących.

W każdym wypadku, sprowadzenie do danej postaci normalnej wymaga dekompozycji relacji na
kilka innych relacji drogą projekcji.

Uwaga:

Dla celów efektywnościowych czasami celowo pozostawia się relację w postaci nie w pełni
znormalizowanej. Rozważmy naszą przykładową relację PARTICIPANTS. Jeśli przy
podawaniu informacji o uczestnikach kursów zawsze obok nazwy miasta podajemy jego
liczbę mieszkańców, może być celowe pozostawienie relacji PART_DATA bez sprowadzania
jej do 3NF, tym bardziej że dane o liczbie mieszkańców zmieniają się wolno.
Należy jednak w takich wypadkach zawsze brać pod uwagę konsekwencje:

redundancję danych i anomalie aktualizacji

i starannie kontrolować takie sytuacje.

#102

Przetwarzanie transakcyjne

stan logicznie spójny (integralny)

stan logicznie niespójny

Transakcja przeprowadza bazę danych

z jednego stanu spójnego do innego.

Po drodze mogą wystąpić stany niespójne.

Przetwarzanie transakcyjne gwarantuje, że
baza danych nie pozostanie w stanie niespójnym,
mimo przerwania transakcji (np. wskutek awarii
systemu lub błędu programu).

start transaction

abort

(rollback)

end transaction

(commit)

#103

Transakcje

cechy

tomicity (atomowość): Transakcja jest atomowa:
wykonuje się cała albo wcale.

onsistency (spójność): Transakcja przeprowadza bazę
danych z jednego stanu spójnego do innego stanu spójnego.

solation (izolacja): Transakcja wykonuje się w izolacji od innych
transakcji; czyli tak, jakby była wykonywana tylko ona.

urability (trwałość): Efekty wykonania transakcji są trwałe:
po zakończeniu wszystkie zmiany wykonane w trakcie
transakcji stają się permanentne.

#104

Transakcje

rozpoczynanie i zatwierdzanie

Początek transakcji:

•

dowolna instrukcja DML: INSERT, UPDATE, DELETE, SELECT

•

dowolna instrukcja DDL: CREATE, DROP, ALTER, GRANT, REVOKE

Uwaga:

Niektóre DBMS (np. DB2, Informix, Progress) udostępniają specjalne instrukcje
rozpoczynające transakcje, takie jak
DO TRANSACTION, BEGIN WORK, START TRANSACTION itp.

Zatwierdzanie transakcji:

COMMIT

Cel: Zatwierdzenie transakcji: zakończenie jej i uczynienie wszystkich zmian trwałymi.

Uwaga:

Niektóre DBMS (np.Oracle) w sposób niejawny wykonują COMMIT
przed i po każdej instrukcji DDL.

#105

Transakcje

wycofywanie

Wycofywanie transakcji:

ROLLBACK [TO SAVEPOINT nazwa]

Cel: Wycofanie i zakończenie całej transakcji lub - opcjonalnie - wskazanie punktu

w transakcji, do którego należy się wycofać.

Uwagi:

1. Wycofanie transakcji nie oznacza cofnięcia się do poprzednio wykonywanych instrukcji!

Oznacza wycofanie skutków wykonania instrukcji wchodzących w skład
wycofywanej transakcji.

2. W razie wystąpienia błędu podczas transakcji (nieprzechwycony przez aplikację błąd

instrukcji SQL, błąd aplikacji, błąd systemu) DBMS niejawnie wykonuje ROLLBACK,
wycofując całą transakcję, w której wstąpił błąd.

#106

Transakcje

wycofywanie

(cd.)

Definiowanie punktów wycofania:

rollback to A commit

savepoint A

start

SAVEPOINT nazwa

Cel: Określenie miejsca w transakcji, do którego można się potem wycofać

instrukcją ROLLBACK TO SAVEPOINT.

Przykład:

UPDATE Prac

SET płaca = ...

WHERE ident = ‘Id01’;

SAVEPOINT S1;
UPDATE Prac

SET płaca = ...

WHERE ident = ‘Id02’;

SAVEPOINT S2;

...

SELECT SUM(płaca) FROM Prac

WHERE ...;

...
ROLLBACK TO SAVEPOINT S1;
UPDATE Prac

SET płaca = ...

WHERE ident = ‘Id02’;

COMMIT;

#107

Obs

uga wsp

bie

Transakcja A

Transakcja B

READ Konto

----------

Konto = Konto + X

----------

WRITE Konto

----------

---------

READ Konto

---------

Konto = Konto + Y

---------

WRITE Konto

Gdzie są

moje

pieniądze?

#108

Isolation

Phenomena

(1)

LOST UPDATE

Trans_A

Trans_B

...

Read

...

Change

...

Change

Commit

...

Commit

The change made by Trans_A has been lost.

#109

Isolation

Phenomena

(2)

DIRTY READ

Trans_A

Trans_B

Read

...

Change

...

Read

Rollback

...

Commit

Trans_B sees data changed by Trans_A and bases its work on the
value of these data. But Trans_A rolls back its work, so the change
really never happened.

#110

Isolation

Phenomena

(3)

NON-REPEATABLE READ

Trans_A

Trans_B

...

Read

...

Change

Read

...

Commit

...

Trans_A may suppose that it reads the same row twice. The second time, though,
the values in the row will be different.

Note: This problems is not so serious as Lost Update or Dirty Read,

but breaks the requirement of ACID transactions.

#111

Isolation

Phenomena

(4)

PHANTOM ROWS

Trans_A

SELECT * FROM t

WHERE col=5;

SELECT * FROM t

WHERE col=5;

At the beginning (t = t1) , Trans_A does not see some rows that it sees later (t = t4),
because Trans_B has added or updated them.

Note: This problems is not so serious as Lost Update or Dirty Read,

but breaks the requirement of ACID transactions.

Trans_B

INSERT INTO t (col)

VALUES (5);

UPDATE t SET col = 5

WHERE col = 6;

#112

Isolation

Levels

(SQL

99)

Isolation levels of transactions are controlled by SET TRANSACTION statement:

SET TRANSACTION <access mode> ISOLATION LEVEL <isolation level>

This statement tells DBMS what sort of isolation phenomena is intolerable for the next transaction.

Access modes

•

READ ONLY

•

READ WRITE (default)

Isolation levels

•

READ UNCOMMITTED

•

READ COMMITTED

•

REPEATABLE READ

•

SERIALIZABLE

#113

Access

Modes

READ ONLY

Tells DBMS that all statements in the upcoming transaction will be read statements
(they do not make any changes).

READ WRITE

(default)

Tells DBMS that the statements in the upcoming transaction will be either reads or writes.

Specifying READ ONLY may result in performance gain:

• If all current transactions are READ ONLY, no locks have to be used,

• For such transactions DBMS can make a temporary copy of the rows they fetch

and all SELECTs are performed on this copy, with no conflicts with other transactions.

READ ONLY is especially good for report writers, screen displayers, file dumps, …

Note:

For READ UNCOMMITTED isolation level READ ONLY is the only legal option.

#114

Isolation

Levels

READ UNCOMMITTED

Lowest level of isolation. It implies READ ONLY access mode.

Meaning:

“Allow reading of rows which have been written by other transactions,
but not yet committed”.

Possible:

Dirty Reads, Non-Repeatable Reads, Phantoms

Not possible:

Lost Updates (as changes are not allowed at all).

The highest level of concurrency (no locks are issued); concurrency checking is turned off.

Good choice for slow transactions and transactions which can tolerate small errors,
e.g. SELECT COUNT (*); from large tables. Also reports in which we don’t care about details.

concurrency

isolation

#115

Isolation

Levels

(

ctd

READ COMMITTED

Meaning:

“Allow reading of rows written by other transactions

only after they have been committed”.

Possible:

Non-Repeatable Reads, Phantoms

Not possible:

Lost Updates, Dirty Reads.

Ensures reasonably high level of concurrency: shared locks must be made,
but they can be released again before the transaction ends.

Good compromise between the level of isolation among concurrent transactions
and performance.

Default isolation level in some DBMSs.

isolation

concurrency

#116

Isolation

Levels

(

ctd

REPEATABLE READ

Meaning:

“Do not tolerate non-repeatable reads”.

Possible:

Phantoms

Not possible:

Lost Updates, Dirty Reads, Non-Repeatable Reads.

The concurrency drops sharply, as shared locks obtained during the transaction are kept
until the transaction terminates.

This is a preferable level in multi-statements transactions that involve changes.

concurrency

isolation

#117

Isolation

Levels

(

ctd

SERIALIZABLE

(default in SQL standard)

Meaning:

“Make the transactions serializable”.

Possible:

None

Not possible:

Lost Updates, Dirty Reads, Non-Repeatable Reads, Phantoms

The highest level of isolation, and the lowest level of concurrency.
Often performed by DBMSs as locking whole tables. The only level that guarantees safe,
error-free transactions every time.

If the transactions in an application are all short (fast-executing, affecting only a few records),
they all may be executed as SERIALIZABLE.

concurrency

isolation

#118

Zasady budowy transakcji w aplikacjach

Transakcje w RDBMS:

• pochłaniają zasoby systemowe (dodatkowa pamięć i czas DBMS)
• ograniczają współbieżność w dostępie do danych

przez wielu użytkowników

Transakcje powinny trwać możliwie krótko

Powinny być wykonywane na możliwie najniższym poziomie izolacji

(ale na wystarczającym dla zachowania poprawności transakcji!).
Np. nie należy niepotrzebnie stosować poziomu SERIALIZABLE tam,
gdzie wystarczy READ COMMITTED.

Interakcje z użytkownikiem powinny przebiegać poza transakcjami.

#119

Zasady budowy transakcji w aplikacjach (

Załóżmy, że w ramach aplikacji bazodanowej użytkownik aktualizuje dane w bazie
danych w zależności od aktualnych wartości innych lub tych samych danych.

Typowy schemat postępowania:

Odczytanie wartości danych z bazy danych.

Wyświetlenie ich użytkownikowi.

Użytkownik podaje wartości danych dla transakcji.

Przeprowadzenie transakcji (aktualizacja danych w bazie danych).

Problemy:

Krok nr 3 może trwać bardzo długo, dlatego nie należy kroków 1.-4. ujmować
w jedną transakcję.

Jeśli transakcję rozpoczniemy między krokiem 3. a 4., to nie mamy
gwarancji, że dane, na podstawie których użytkownik ustalił dane dla
transakcji, nie zostały w międzyczasie zmienione przez innego użytkownika.

#120

Zasady budowy transakcji w aplikacjach (

Najprostsza sytuacja:

Użytkownik zmienia te same dane, na podstawie których podejmuje decyzję
o zmianie. Jeśli te dane są zawarte tylko w jednej tablicy, wówczas można
w ogóle obyć się bez trybu transakcyjnego (pojedyncza instrukcja SQL jest
zawsze atomowa – tzw. tryb autocommit):

1. SELECT id, pole1, pole2, ...

FROM Tablica
WHERE id = klucz ;

Wyświetlenie wartości pole1, pole2, ....

Ustalenie przez użytkownika nowych wartości pól pole1, pole2, ...

4. UPDATE Tablica

SET pole1 = <nowa wartość1>,

pole2 = <nowa wartość2>, ...

WHERE id = klucz AND pole1 = <stara wartość1>

AND pole2 = <stara wartość2> AND ... ;

#121

Zasady budowy transakcji w aplikacjach (

W ogólnym przypadku:

Po ustaleniu przez użytkownika wartości danych dla transakcji należy rozpocząć
transakcję, w ramach której trzeba sprawdzić, czy dane uprzednio pobrane
z bazy danych i wyświetlone użytkownikowi nie uległy w międzyczasie zmianie.

1. SELECT id, pole1, pole2, ...

FROM Tablica
WHERE id = klucz ;

Wyświetlenie wartości pole1, pole2, .... i zapamiętanie ich.

Ustalenie przez użytkownika wartości danych dla transakcji.

4. START TRANSACTION
5. SELECT id, pole1, pole2, ...

FROM Tablica
WHERE id = klucz ;

Porównanie wartości pole1, pole2, .... z wartościami zapamiętanymi

Jeśli są takie same, to wykonanie aktualizacji i COMMIT.
Jeśli są inne, to COMMIT (albo ROLLBACK) i stosowny komunikat do
użytkownika.

#122

Zasady budowy transakcji w aplikacjach (

Uwaga:

Aby ten schemat zadziałał na pewno poprawnie, poziom izolacji transakcji
musi być ustawiony (instrukcją SET TRANSACTION) przynajmniej na
REPEATABLE READ, a jeśli DBMS na to nie pozwala, to na SERIALIZABLE.
W przeciwnym razie nie mamy gwarancji, że między krokami 5 i 7 dane
nie ulegną zmianie.
Inną możliwością jest skorzystanie z opcji FOR UPDATE instrukcji SELECT,
dostępnej w niektórych DBMS. Opcja ta powoduje zablokowanie danych
wybranych instrukcją SELECT przed jakimikolwiek zmianami.

W językach typu 4GL opcja FOR UPDATE odpowiada opcji SHARED LOCK
w instrukcji READ lub podobnej.

...

4. START TRANSACTION
5. SELECT id, pole1, pole2, ...

FROM Tablica
WHERE id = klucz
FOR UPDATE ;

...

#123

Ochrona danych

Pełny dostęp

Tylko odczyt

Brak dostępu

#124

Security

SQL

Views

Two ways of security control supported directly in SQL:

• views mechanism

• GRANT and REVOKE statements

Views can be used to hide sensitive data from unauthorised users.

Example 1:

Assume that information where students live (see table STUDENTS)
should be hidden from some users. We can create a view:

CREATE VIEW Stud

AS SELECT Sno, Sname, Syear

FROM Students;

and then we can GRANT specific rights on Stud to those users,
REVOKEing from them all rights on Students.

#125

Security

SQL

Views

(

ctd

Example 2:

We do not want the detailed information about teachings (table TS) to be
commonly available. Instead we offer an ordered summary for each teacher.
We can create a view:

CREATE VIEW TeachingSummary

(Tno, SumHours)
AS SELECT Tno, SUM(Hours)

FROM TS
GROUP BY Tno
ORDER BY Tno;

and establish access rights as desired.

#126

Security

SQL

GRANT, REVOKE

♦ Granting rights:

GRANT { ALL | <right list> }

ON <table or view name>
TO { PUBLIC | <user list> }
[ WITH GRANT OPTION ]

This statements grants either all (if ALL) or only specific access rights on the specified table
or view for all users (if PUBLIC) or only for those that are listed on the user list.

WITH GRANT OPTION

- allows a grantee to grant the rights to other users

The <right list> may contain:

• ALTER

- to execute ALTER TABLE statement

• INDEX

- to execute CREATE INDEX statement

• DELETE

- to execute DELETE FROM statement

• SELECT [<column list>]

- to execute SELECT statement (on specific columns)

• INSERT [<column list>]

- to execute INSERT INTO statement (on specific columns)

• UPDATE [<column list>]

- to execute UPDATE statement (on specific columns)

• REFERENCES [<column list>] - to define CONSTRAINTs (on specific columns)

Note: In some DBMSs (compatible with SQL 92) <column list> may be valid only for UPDATE right.

#127

Security

SQL

GRANT,

REVOKE

♦

Revoking rights:

REVOKE { ALL | <right list> }

ON <table or view name>
FROM { PUBLIC | <user list> }

This statement is symmetric to GRANT.

Example:

We want to revoke from all users all rights given before on Teachers, then grant rights to
SELECT and INDEX for each user and to UPDATE specific columns only for some users
(with the right to grant them to other users).

REVOKE ALL

ON Teachers
FROM PUBLIC;

GRANT SELECT, INDEX

ON Teachers
TO PUBLIC;

GRANT UPDATE (Title, City, SupNo)

ON Teachers
TO John, Helen
WITH GRANT OPTION;

#128

Constraints

(SQL

99)

Constraints enable DBMS automatically enforce logical integrity of a database.

Constraints may be:

• table level constraints
• column level constraints
• domain constraints

Defining a constraint:

[ CONSTRAINT <constraint name> ]

constraint_type

[ constraint attributes ]

Each constraint has a name. If omitted, DBMS will automatically assign a name to a constraint.

#129

Constraint definition

constraint_type ::=

<table constraint> ::=

UNIQUE constraint |
PRIMARY KEY constraint
FOREIGN KEY constraint
CHECK constraint

<column constraint> ::=

UNIQUE constraint |
PRIMARY KEY constraint
FOREIGN KEY constraint
NOT NULL constraint
CHECK constraint

<domain constraint> ::=

CHECK constraint

<constraint attributes> ::= { INITIALLY DEFERRED | INITIALLY IMMEDIATE }

{ [NOT] DEFERRABLE }

default:

INITIALLY IMMEDIATE NOT DEFERRABLE

#130

Constraint attributes

· Constraint check time specifies, when within a transaction the constraint is checked:

IMMEDIATEly (just after instruction execution) or
DEFERRED (at COMMIT time).

· Deferral mode specifies whether the constraint check time:

may be changed (DEFERRABLE) or

not may be changed (NOT DEFERRABLE) after it is initially set.

The change of the constraint check time is performed via SET CONSTRAINTS statement:

SET CONSTRAINTS { <constraint name list> | ALL }

{ DEFERRED | IMMEDIATE }

Note:

If deferred constraints are violated at commit time, the whole transaction will be rolled back
(COMMIT fails). If an immediate constraint is violated, only one SQL statement fails and
an SQLSTATE error is issued. The error may be captured by the program that issued
the statement (otherwise the transaction will be rolled back).

#131

Constraint attributes

Examples

1. A constraint NOT NULL for one column, initially immediate, deferrable,

CREATE TABLE Tab1 (

col1 SMALLINT

CONSTRAINT constr1 NOT NULL

DEFERRABLE INITIALLY IMMEDIATE,

col2 CHAR(16));

CREATE TABLE Tab1 (

col1 SMALLINT,
col2 CHAR(16)),
CONSTRAINT constr1

UNIQUE (col1, col2) DEFERRABLE INITIALLY DEFERRED);

2. A constraint UNIQUE for two columns, initially deferred, deferrable,

#132

PRIMARY KEY, FOREIGN KEY Constraints

Examples of typical usage:

CREATE TABLE Tab1 (

id INTEGER,
name

VARCHAR(64),

CONSTRAINT pr_key1 PRIMARY KEY (id));

CREATE TABLE Tab2 (

id INTEGER,
int_address

VARCHAR(256),

CONSTRAINT fr_key2 FOREIGN KEY (id) REFERENCES Tab1);

In this case, the REFERENCES clause in the second statement references the column
defined in Tab1 as the PRIMARY KEY column.

#133

PRIMARY KEY, FOREIGN KEY Constraints

A FOREIGN KEY clause may reference any unique column(s) of a table.

Example:

CREATE TABLE Tab1 (

col1

SMALLINT,

col2

VARCHAR(4),

col3

DATE,

CONSTRAINT pr_key1 PRIMARY KEY (col1, col2),
CONSTRAINT fr_key1 UNIQUE (col3) NOT DEFERRABLE);

CREATE TABLE Tab2 (

col1

SMALLINT,

col2

VARCHAR(4),

col3

DATE,

CONSTRAINT fr_key2 FOREIGN KEY (col3) REFERENCES Tab1(col3));

In this case, the REFERENCES clause in the second statement references a column
defined in Tab1 as a UNIQUE column.

#134

FOREIGN KEY Constraints

Referential actions

In a FOREIGN KEY constraint may be specified an action to be taken
ON DELETE and/or ON UPDATE, which specifies what to do with referenced table
when the primary key in the referencing table changes.

Possible actions:

• NO ACTION

- no action is taken (default)

• CASCADE

- the update or delete operation is cascaded from

the referenced table to the referencing table.

• RESTRICT

- the update or delete operation causes an SQLSTATE error

to be returned, if any matching row exists in the referencing table
(acts essentially the same as NO ACTION).

• SET NULL

- the update or delete operation causes the foreign key value

to be set to NULL.

• SET DEFAULT - the update or delete operation causes the foreign key value

to be set to its default value (if specified).

#135

Referential actions

Examples

Example 1:

CREATE TABLE Tab1 (

col1

SMALLINT DEFAULT 12,

CONSTRAINT ogr1 PRIMARY KEY (col1));

CREATE TABLE Tab2 (

col1

SMALLINT DEFAULT 15,

CONSTRAINT ogr2 FOREIGN KEY (col1) REFERENCES Tab1

ON UPDATE RESTRICT ON DELETE RESTRICT);

INSERT INTO Tab1 VALUES (10);
INSERT INTO Tab1 VALUES (15);
INSERT INTO Tab2 VALUES (10);

Each of the following statements will return an error:

UPDATE Tab1 SET col1 = 11 WHERE col1 = 10;
UPDATE Tab2 SET col1 = 11 WHERE col1 = 10;
INSERT INTO Tab2 VALUES(11);
DELETE FROM Tab1 WHERE col1 = 10;

#136

Referential actions

Examples

Example 2:

CREATE TABLE Tab1 (

col1

SMALLINT DEFAULT 12,

CONSTRAINT ogr1 PRIMARY KEY (col1));

CREATE TABLE Tab2 (

col1

SMALLINT DEFAULT 15,
CONSTRAINT ogr2 FOREIGN KEY (col1) REFERENCES Tab1

ON UPDATE CASCADE ON DELETE CASCADE);

INSERT INTO Tab1 VALUES (10);
INSERT INTO Tab1 VALUES (15);
INSERT INTO Tab2 VALUES (10);

Effect of the following statements will cascade down from Tab1 to Tab2:

UPDATE Tab1 SET col1 = 11 WHERE col1 = 10;

DELETE FROM Tab1 WHERE col1 = 10;

#137

CHECK Constraints

CHECK constraint ::=

[ CONSTRAINT <constraint name> ]

CHECK (search condition)

[ <constraint attributes> ]

search condition

- an expression which may normally appear in a WHERE clause

of a SELECT statement (with some limitations and extensions)

Example 1:

A CHECK constraint on a table in CREATE TABLE

CREATE TABLE Tab1 (

col1 SMALLINT,
CONSTRAINT ogr1 CHECK (col1 < 400) NOT DEFERRABLE);

#138

CHECK Constraints

Example 2:

A CHECK constraint on a column in CREATE TABLE

CREATE TABLE Tab1 (

col1 SMALLINT

CONSTRAINT ogr1 CHECK (col1 < 400)

NOT DEFERRABLE,

col2 CHAR(15)));

Example 3:

A CHECK constraint on a table in ALTER TABLE

ALTER TABLE Tab2

ADD CONSTRAINT ogr2 CHECK (col1 > 100 OR col2 = “hello”)

NOT DEFERRABLE INITIALLY IMMEDIATE;

#139

CHECK Constraints

Example 4:

A CHECK constraint on a domain in CREATE DOMAIN

CREATE DOMAIN dom1 AS SMALLINT

CONSTRAINT ogr1 CHECK (VALUE IN (50, 100, 150));

Example 5:

A CHECK constraint on a domain in ALTER DOMAIN

ALTER DOMAIN dom1

ADD CONSTRAINT ogr2 CHECK (VALUE IS NOT NULL);

Example 6:

An inter-table CHECK constraint (may be not supported in many DBMSs):

ALTER TABLE tab1

ADD CONSTRAINT ogr3

CHECK (col1 > (SELECT MAX (col2) FROM Tab2)) NOT DEFERRABLE;

#140

Triggers

Trigger (triggered procedure):

A piece of code that is implicitly invoked by DBMS when a specified event
(a triggering event) occurs in the database.

A typical specification of a trigger contains 3 elements:

• the name of the trigger
• a triggering events list
• an executable unit

Possible applications of triggered procedures:

• security (enforcing authorisation constraints or performing

data encryption and decryption);

• performance measurement (monitoring or tracing various database-related events);

• program debugging (monitoring changes to database fields and state variables);

• controlling stored record formats (e.g. compressing and decompressing);

• exception reporting

(e.g. "Warn me if the quantity in stock of any item goes below the danger level").

#141

Triggers

SQL

Syntax

CREATE TRIGGER <trigger name>

{ BEFORE | AFTER } <trigger event> ON <table name>
[ REFERENCING <old or new values alias list> ]
<triggered action>

• <trigger event> ::= INSERT |

DELETE |
UPDATE [ OF <trigger column list> ]

• <old or new values alias> ::=

OLD [ ROW ] [ AS ] <old values correlation name> |
NEW [ ROW ] [ AS ] <new values correlation name> |
OLD TABLE [ AS ] <old values table alias> |
NEW TABLE [ AS ] <new values table alias> |

• <triggered action> ::=

[ FOR EACH { ROW | STATEMENT} ] [ WHEN (<search condition>)]

• <triggered SQL statement> ::=

<SQL statement> |
BEGIN ATOMIC {<SQL statement>;}… END

#142

Triggers

SQL

Examples

1. We want to keep a log file (Books_deleted_log) containing data from rows

that have been deleted from the BOOKS table.

CREATE TRIGGER Books_delete

AFTER DELETE ON Books

REFERENCING OLD ROW AS Old

FOR EACH ROW

INSERT INTO Books_deleted_log

VALUES (Old.ISBN, Old.Title, Old.Author, Old.Editor);

2. When we add a new customer, we want the value of Home_telephone

to be the same as Work_telephone.

CREATE TRIGGER Customer_insert

BEFORE INSERT ON Customers

REFERENCING NEW ROW AS New

FOR EACH ROW

SET New.Home_telephone = New.Work_telephone;

#143

Triggers in SQL

Examples

3. When we elect Bob as a president, we all get a 1% tax cut. On the other hand,

every change in taxes will affect the national debt and cause Bob’s popularity to drop.

CREATE TRIGGER President_update
AFTER UPDATE ON Presidents

REFERENCING OLD ROW AS Old, NEW ROW AS New

FOR EACH ROW

WHEN (New.Name = “Bob” AND New.Name <> Old.Name)

UPDATE Taxpayers

SET tax_payable = tax_payable * 0.99;

CREATE TRIGGER Taxpayer_update
AFTER UPDATE ON Taxpayers OF tax_payable

REFERENCING OLD ROW AS Old, NEW ROW AS New

FOR EACH STATEMENT

BEGIN ATOMIC

UPDATE National_debt

SET amount = amount * (Old.tax_payable / New.tax_payable)
WHERE Year = CURRENT_YEAR;

UPDATE Presidents

SET popularity = popularity – 0.01
WHERE Name = “Bob”;

END;

#144

Rozproszenie danych

Sieć komunikacyjna

1. Każdy węzeł może pracować autonomicznie
2. Węzły ustaliły zasady współpracy

#145

Rozproszone systemy baz danych

Główny problem:

Zachowanie zasady przezroczystości w warunkach

• rozproszenia danych
• fragmentacji danych
• replikacji danych

• Przezroczystość rozproszenia (lokalizacji)

Działania użytkownika (aplikacji) nie powinny być uzależnione
od faktycznej lokalizacji danych.

• Przezroczystość fragmentacji

Działania użytkownika (aplikacji) nie powinny być uzależnione
od sposobu rozdzielenia danych pomiędzy węzły systemu.

• Przezroczystość replikacji

Działania użytkownika (aplikacji) nie powinny być uzależnione
od sposobu replikowania danych.

#146

Fragmentacja danych

Rozdzielenie jednej relacji pomiędzy różne węzły systemu
rozproszonego w celu polepszenia efektywności

Fragmentacja pozioma

Rozdzielenie relacji wierszami

Fragmentacja pionowa

Rozdzielenie relacji kolumnami, z zachowaniem klucza głównego
na wszystkich węzłach

#147

Replikacja danych

Utrzymywanie kopii danych na różnych węzłach systemu
rozproszonego w celu polepszenia efektywności i dostępności.

Podstawowy problem:

Zachowanie integralności replikowanych danych (integralności
rozproszonego systemu baz danych) w warunkach aktualizacji
dokonywanych w wielu węzłach.

#148

Replikacja typu

read

only

Serwer główny

Serwery

docelowe

Serwery

docelowe

Jeden z serwerów systemu rozproszonego jest serwerem głównym, odpowiedzialnym
za dokonywanie aktualizacji. Repliki danych są przesyłane przez serwer główny
na serwery docelowe, na których są one widoczne dla użytkowników w postaci
tzw. migawek (ang. snapshot), aktualizowanych okresowo lub w sposób ciągły.

Jest to najprostszy i najłatwiejszy w zarządzaniu schemat replikacji.

#149

Replikacja typu

update

anywhere

Sieć

Definicja

grupy

Wszystkie serwery mają prawo do aktualizacji replikowanych danych,
a także są odpowiedzialne za propagację aktualizacji do wszystkich replik,
rozmieszczonych na innych serwerach. Jeden z serwerów w grupie obsługującej
replikację przechowuje definicję grupy, zawierającą informacje o uczestniczących
w replikacji serwerach i o replikowanych danych.

Schemat ten jest znacznie bardziej skomplikowany w zarządzaniu niż schemat read-only.

#150

wnoleglenie przetwarzania

Medium komunikacyjne

. . .

Procesory

Pamięci RAM

Dyski

Architektura typu shared-nothing

#151

wnoleg

e przetwarzanie zapyta

SQL

MERGE

SORT

DANE A

DANE

SELECT

SORT

DANE B

DANE D

DANE C

SELECT

SPLIT

#152

Ewolucja technologii baz danych

Systemy

plików

Hierarchiczne

bazy danych

Sieciowe

bazy danych

Relacyjne

bazy danych

1980

1960

1970

1990

1. Systemy plików:

realizacja operacji plikowych; stanowią poziom fizyczny współczesnych baz danych
ISAM, CSAM, VSAM, WiSS, inne specjalizowane

2. Hierarchiczne bazy danych

dane organizowane są w struktury drzewiaste
IMS, System 2000, ...

3. Sieciowe bazy danych

dane organizowane są w dowolne struktury sieciowe (Codasyl)
IDS, Total, Adabas, IDMS, ...

4. Relacyjne bazy danych

dane organizowane są w relacje (zbiory krotek o ustalonej, prostej strukturze)
Oracle, Informix, DB2, Ingres, Progress, MSQL, ...

#153

Ewolucja technologii baz danych

Relacyjne

bazy danych

Bazy danych

nowej generacji

2000

1980

1990

2010

Bazy danych nowej generacji - motywacje:

nowe aplikacje baz danych:

CAD, CAM, CAP, CASE, DSS,
automatyzacja prac biurowych, kartografia,
multimedia, sztuczna inteligencja

niedostatki systemów tradycyjnych:

zbyt prosty model danych, nieodpowiedni do modelowania złożonych struktur
ograniczony, nierozszerzalny zestaw typów danych
ograniczone możliwości obliczeniowe
niedopasowanie pomiędzy językiem operowania danymi a językiem programowania
zbyt prosty model transakcji
brak pojęcia wersji
mała elastyczność w operowaniu schematem