Bazy danych 2

Wykład 5

Structured Query Language (SQL) c.d.

DDL

Dziedzina (DOMAIN)

Dziedzina to zdefiniowany przez u

ż

ytkownika zbiór dopuszczalnych warto

ś

ci

definiowany niezale

ż

nie od definicji tabel

Sk

ł

adnia

CREATE DOMAIN NazwaDziedziny [AS] TypDanych

[[NOT] NULL]

[DEFAULT warto

ść

_domy

ś

lna]

[CHECK (warunek_selekcji)]

Warunek selekcji mo

ż

e odnosi

ć

si

ę

do innej tabeli

Do zmiany nazwy dziedziny u

ż

ywamy polecenia ALTER DOMAIN o sk

ł

adni

ALTER DOMAIN NazwaDziedziny RENAME NowaNazwa

Dziedzin

ę

usuwamy poleceniem

DROP DOMAIN NazwaDziedziny [RESTRICT|CASCADE]

Dziedzina (DOMAIN)

Przyk

łłłł

ad1

CREATE DOMAIN P

łłłł

e

ć

ć

ć

ć

AS CHAR

NOT NULL

DEFAULT 'M'

CHECK (VALUE IN ('M', 'K'));

Przyk

łłłł

ad 2

CREATE DOMAIN NumerBiura AS CHAR(4)

NULL

CHECK (VALUE IN (SELECT biuroNr FROM Biuro));

Tworzenie tabeli (CREATE TABLE)

CREATE TABLE [ w

ł

a

ś

ciciel.]NazwaTabeli

( { definicja kolumny[,....]} [ograniczenie dla tabeli[, ...])

definicja kolumny :
nazwa_kolumny typ_danych [ [ NOT ] NULL ] [UNIQUE]
[ DEFAULT warto

ść

_domy

ś

lna ] [ ograniczenie dla kolumny [... ]]

ograniczenie dla kolumny :
[ CONSTRAINT nazwa_ograniczenia ] { PRIMARY KEY
| REFERENCES NazwaTabeli [( nazwa_kolumny ) ] [ akcja ] }
| CHECK ( warunek )

ograniczenie dla tabeli:
[ CONSTRAINT nazwa_ograniczenia ] {UNIQUE ( nazwa_kolumny[, ...] )
| PRIMARY KEY ( nazwa_kolumny1[, ... ])
| CHECK ( warunek )
| ograniczenie klucza obcego}

ograniczenie klucza obcego
FOREIGN KEY [(nazwa_kolumny[, ...] ) ]
REFERENCES NazwaTabeli [(nazwa_kolumny[, ... ]) ]
[akcja] [ CHECK ON COMMIT]

akcja : ON { UPDATE | DELETE }{ CASCADE | SET NULL | SET DEFAULT | RESTRICT }

Więzy integralności

NO ACTION lub RESTRICT - uniemożliwia usunięcie (modyfikację) krotki
z relacji nadrzędnej jeśli występują jakiekolwiek krotki od niej zależne.

CASCADE – usunięcie (modyfikacja) krotki nadrzędnej pociąga za sobą
usunięcie (modyfikację) wszystkich związanych z nią krotek
podrzędnych; jeśli krotka podrzędna pełni rolę nadrzędną w innym
związku, analogiczna operacja usuwania (modyfikacji) powinna być
wykonana dla krotek podrzędnych tego związku.

SET NULL – usunięcie krotki nadrzędnej pociąga za soną automatyczne
nadanie wartości pustych odpowiednim kluczom obcym wszystkich
krotek

SET DEFAULT – usunięcie krotki nadrzędnej pociąga za soną
automatyczne nadanie wartości domyślnych odpowiednim kluczom
obcym wszystkich krotek podrzędnych.

Tworzenie tabeli (CREATE TABLE)

Przyk

łłłł

ad 1. Równowa

ż

ż

ż

ż

ne sformu

łłłł

owania

CREATE TABLE Product (

product_num INTEGER PRIMARY KEY);

CREATE TABLE Product (

product_num INTEGER,

PRIMARY KEY ( product_num )

Przyk

łłłł

ad 2 Nieokre

ś

ś

ś

ś

lona kolumna w klauzuli FOREIGN KEY

CREATE TABLE library_books (

isbn

CHAR(20) PRIMARY KEY,

copyright_date DATE,

title

CHAR(100),

author

CHAR(50),

FOREIGN KEY location REFERENCES room)

Tworzenie tabeli (CREATE TABLE)

Przyk

łłłł

ad 3.

CREATE TABLE Orders (

order_num

INTEGER NOT NULL PRIMARY KEY,

date_ordered DATE,

name

CHAR(80));

CREATE TABLE Order_item (

order_num

INTEGER NOT NULL,

item_num SMALLINT NOT NULL CHECK(item_num<100),

PRIMARY KEY (order_num, item_num),

FOREIGN KEY (order_num) REFERENCES Orders (order_num)

ON DELETE CASCADE)

Tworzenie tabeli (CREATE TABLE)

Przyk

łłłł

ad 4.

CREATE TABLE borrowed_book (

date_borrowed

DATE NOT NULL DEFAULT CURRENT DATE,

date_returned

DATE,

book

CHAR(20)

REFERENCES library_books (isbn),

PRIMARY KEY (book),

CONSTRAINT GoodDate CHECK( date_returned >= date_borrowed ));

Modyfikacja tabeli (ALTER TABLE)

Polecenie ALTER TABLE pozwala zmieni

ć

strukture tabeli, która zosta

ł

a

utworzona

Sk

ł

adnia ISO zawiera sze

ść

opcji

dodanie nowej koumny do tabeli

usuni

ę

cie kolumny z tabeli

dodanie nowych wi

ę

zów tabeli

usuni

ę

cie wi

ę

zów tabeli

ustalenie warto

ś

ci domy

ś

lnej dla kolumny

usuni

ę

cie warto

ś

ci domy

ś

lnej kolumny

zmiana nazwy tabeli, kolumny i regu

ł

y

Modyfikacja tabeli (ALTER TABLE)

Sk

ł

adnia:

ALTER TABLE NazwaTabeli

[ADD [COLUMN] {definicja kolumny}]

[DROP [COUMN] NazwaKolumny [RESTRICT|CASCADE]

[ADD [CONSTRAINT [nazwa_ograniczenia]] ograniczenie dla tabeli]

[DROP CONSTRAINT nazwa_ograniczenia [RESTRICT|CASCADE]

[ALTER NazwaKolumny modyfikacja_kolumny]

[ALTER nazwa_ograniczenia CHECK ( nowy_warunek )

modyfikacja kolumny :

SET DEFAULT warto

ść

ść

ść

ść

domy

ś

śś

ś

lna

| DROP DEFAULT
| ADD [ CONSTRAINT ] nazwa_ograniczenia CHECK ( warunek )
| DROP CONSTRAINT nazwa_ograniczenia

Modyfikacja tabeli (ALTER TABLE)

Przyk

łłłł

ad 1.

ALTER TABLE employee

ADD office CHAR(20) DEFAULT 'Boston'

Przyk

łłłł

ad 2.

ALTER TABLE employee

DELETE office

Przyk

łłłł

ad 3.

ALTER TABLE customer

ADD sales_contact INTEGER

REFERENCES employee (emp_id)

ON UPDATE CASCADE

ON DELETE SET NULL

Modyfikacja tabeli (ALTER TABLE)

Przyk

łłłł

ad 4.

ALTER TABLE Personel

ALTER stanowisko DROP DEFAULT

Przyk

łłłł

ad 5.

ALTER TABLE Personel

ALTER p

łłłł

e

ć

ćć

ć

SET DEFAULT K'

Przyk

łłłł

ad 6.

ALTER TABLE Nieruchomo

ść

ść

ść

ść

DROP CONSTRAINT PracownikNadzoruj

ą

ą

ą

ą

cyNieZaDu

ż

ż

ż

ż

o

Usuwanie tabeli (DROP TABLE)

Usuwamy tabel

ę

pleceniem DROP TABLE

Sk

ł

adnia

DROP TABLE NazwaTabeli [RESTRICT|CASCADE]

Polecenie DROP TABLE usuwa tabel

ę

wraz z wszystkimi wierszami, je

ż

eli chemy

tylko usun

ąć

wiersze stosujemy polecenie DELETE

Tworzenie indeksów (CREATE INDEX)

Indeks jest struktura przyspieszaj

ą

c

ą

wyszukiwanie wierszy w tabeli

Sk

ł

adnia

CREATE [ UNIQUE ] INDEX nazwa_indeksu

ON NazwaTabeli
( NazwaKolumny [ ASC | DESC ] [, ...])

Je

ś

li w poleceniu wyst

ę

puje klauzula UNIQUE system b

ę

dzie kontrolowa

ł

unikalno

ść

warto

ś

ci kolumny czy kolumn, wed

ł

ug których jest tworzony indeks

Najlepiej jest tworzy

ć

unikalne indeksy w momencie tworzenia tabeli

Przyk

łłłł

ad 1.

CREATE INDEX employee_name_index

ON employee ( emp_lname ASC, emp_fname DESC )

Usuwamy indeks poleceniem

DROP INDEKS nazwa_indeksu

Wybór indeksów

Wskazówki dotyczące tworzenia „listy pożądanych indeksów”

Należy utworzyć indeks dla klucza głównego (jeśli nie robi tego
BDMS)

Jeśli często występują odwołania do klucza obcego, warto

utworzyć dla niego indeks (jeśli nie robi tego BDMS).

Należy utworzyć indeks pomocniczy dla kolumn, które nie są

kluczami głównymi ani kluczami obcymi, ale mogą być używane

w złożonych powiązaniach

Należy utworzyć indeksy pomocnicze dla atrybutów intensywnie
wykorzystywanych w:

Kryteriach selekcji

ORDER BY

GROUP BY

Innych operacjach wymagających sortowania

Wybór indeksów

Wskazówki dotyczące tworzenia „listy pożądanych indeksów”

Nie należy indeksować małych relacji

Należy unikać indeksowania często modyfikowanego atrybutu
bądź relacji

Nie jest wskazane indeksowanie atrybutu służącego realizacji
zapytań, których wynikiem jest istotna frakcja krotek w relacji
(>25%)

Należy unikać indeksowania atrybutów składających się z długich
łańcuchów

Wybór indeksów

Z utrzymywaniem i używaniem indeksów wiąże się dodatkowy
koszt, na który składa się m.in.:

Koszt dodania nowego rekordu indeksowego do każdego
indeksu pomocniczego przy każdym dodaniu krotki do relacji;

Koszt modyfikacji indeksów pomocniczych wynikający z
odpowiednich modyfikacji krotek w relacji;

Wzrost rozmiaru pamięci dyskowej;

Możliwość obniżenia wydajności na etapie optymalizacji
zapytań, będąca wynikiem rozważania przez optymalizator
użycia każdego z indeksów

Przy dodawaniu indeksów pomocniczych należy rozważyć, czy ten
dodatkowy koszt zostanie zrekompensowany poprzez uzyskaną
dzięki indeksowi poprawę wydajności.

Perspektywy (VIEW)

Perspektywy tworzymy poleceniem CREATE VIEW

Sk

ł

adnia

CREATE VIEW nazwa_perspektywy [ ( column-name [, ...] ) ]

AS zapytanie_Select
[ WITH CHECK OPTION ]

Przyk

łłłł

ad 1. Perspektywa pozioma

CREATE VIEW PersonelBiura3

AS SELECT *

FROM Personel

WHERE biuroNr='003';

Przyk

łłłł

ad 2. Perspektywa pionowa

CREATE VIEW Pesonel3

AS SELECT prcownikNr, imi

ę

ę

ę

ę

, nazwisko, stanowisko, p

łłłł

e

ć

ć

ć

ć

FROM PersonelBiura3

Perspektywy (VIEW)

Przyk

łłłł

ad 3. Perspektywa grupuj

ą

ą

ą

ą

ca i perspektywa z

łą

łą

łą

łą

czenia

CREATE VIEW LiczbaNieruchomo

ś

ś

ś

ś

ciPracownika (biuroNr, pracownikNr, liczba)

AS SELECT p.biuroNr, p.pracownikNr, COUNT(*)

FROM Personel p, Nieruchomo

ść

ść

ść

ść

d

WHERE p.pracownikNr=d.pracownikNr

GROUP BY p.biuroNr, p.pracownikNr;

Usuwanie perspektywy wykonujemy za pomoc

ą

polecenia DROP VIEW

Sk

ł

adnia

DROP VIEW nazwa_perspektywy [RESTRICT|CASCADE]

Modyfikowanie danych poprzez perspektywy (VIEW)

Standard ISO nak

ł

ada kilka ogranicza

ń

na mo

ż

liwo

ść

definiowania i wykorzystywania

perspektyw.

Nie wolno wykonywa

ć

z

łą

czenia perspektywy grupuj

ą

cej z tabel

ą

bazow

ą

lub inna

perspektyw

ą

Je

ż

eli kolumna perspektywy jest wyliczana jako funkcja agreguj

ą

ca, to nazwa takiej

kolumny mo

ż

e pojawi

ć

sie tylko w klauzulach SELECT lub ORDER BY zapyta

ń

wykorzystuj

ą

cych t

ę

perspektyw

ę

.

Przyk

łłłł

ad 4. b

łę

dna formu

ł

a

SELECT COUNT(liczba)

FROM LiczbaNieruchomo

ś

ś

ś

ś

ciPracownika

Przyk

łłłł

ad 5. b

łę

łę

łę

łę

dna formu

łłłł

a

SELECT *

FROM LiczbaNIeruchomo

ś

ś

ś

ś

ciPracownika

WHERE liczba>2

Modyfikowanie danych poprzez perspektywy (VIEW)

Istnieje mo

ż

liwo

ść

dokonywania modyfikacji danych poprzez perspektywy

Standard ISO stwierdza,

ż

e perspektywa jest modyfikowalna wtedy i tylko wtedy gdy:

Wiersz dodany poprzez perspektyw

ę

musi spe

ł

nia

ć

wi

ę

zy integralno

ś

ci tabeli bazowej

Nie wyst

ę

puje w niej opcja DISTINCT, czyli w wyniku zapytania nie s

ą

usuwane

powtórzenia wierszy

Ka

ż

dy element na li

ś

cie SELECT zapytania definiuj

ą

cego perspektyw

ę

jest nazwa

kolumny (a nie stal

ą

, wyra

ż

eniem, czy funkcj

ą

agreguj

ą

c

ą

) i

ż

adna kolumna nie

wyst

ę

puje wi

ę

cej ni

ż

jeden raz.

W klauzuli FROM jest okre

ś

lona tylko jedna tabela, czyli istnieje tylko jedna tabela

ź

ród

ł

owa dla perspektywy - w ten sposób wykluczone s

ą

perspektywy oparte na

z

łą

czeniach, czy sumie UNION

W zapytaniu definiuj

ą

cym perspektywy nie wyst

ę

puje klauzula GROUP BY

Klauzula WHERE nie zawiera

ż

adnego zagnie

ż

d

ż

onego podzapytania SELECT, w

którym wyst

ę

puje odwo

ł

anie do tabeli znajduj

ą

cej si

ę

na li

ś

cie FROM

Klauzula WITH CHECK OPTION gwarantuje,

ż

e wiersze, które nie spe

ł

niaj

ą

warunku WHERE zapytania definiuj

ą

cego, nie mog

ą

by

ć

dodane poprzez

perspektyw

ę

do tabeli (tzw. wiersze migruj

ą

ce)

Przyak

łłłł

ad 6:

CREATE VIEW PersonelBiura3

UPDATE PersonelBiura3

AS SELECT *

SET biuroNr=‘B005’

FROM Personel

WHERE pracownikNr=‘SG37’

WHERE biuroNr='003‘

WITH CHECK OPTION;

Klauzula WITH CHECK OPTION

Wady i zalety perspektyw

Zalety perspektyw

Niezale

ż

no

ść

danych – perspektywa mo

ż

e reprezentowa

ć

spójny i

niezmienny obraz struktury bazy danych pomimo zmian dokonywanych w
tabelach bazowych (pomimo dodawania i usuwania kolumn, zmiany
zwi

ą

zków, dzielenia tablic, zmiany ich struktury i nazwy)

Poprawa bezpiecze

ń

stwa – ka

ż

dy u

ż

ytkownik mo

ż

e otrzyma

ć

uprawnienia do bazy danych poprzez niewielki zbiór perspektyw, które
zawieraj

ą

potrzebne mu dane

Uproszczenie zapyta

ń

Dostosowanie do u

ż

ytkownika

Integralno

ść

danych – klauzula WITH CHECK OPTION

Wady i zalety perspektyw

Wady perspektyw

Ograniczona mo

ż

liwo

ść

modyfikacji – pewnych danych nie mo

ż

na

modyfikowa

ć

poprzez perspektywy

Ograniczenia struktury – je

ś

li tworzona by

ł

a perspektywa ze wszystkich

kolumn tabeli i dodali

ś

my nowe kolumny, to nie b

ę

d

ą

one wyst

ę

powa

ł

y w

perspektywie

Wydajno

ść

– korzystanie z perspektyw mo

ż

e spowodowac spadek

wydajno

ś

ci

Mechanizmy bezpieczeństwa (GRANT)

Mechanizm bezpiecze

ń

stwa jest oparty na takich elementach jak:

Identyfikatory u

ż

ytkowników

Prawa w

ł

asno

ś

ci

Prawa dost

ę

pu

Administrator bazy danych (DBA) – wyró

ż

niony u

ż

ytkownik

DBA ma mo

ż

liwo

ść

tworzenia nowych u

ż

ytkowników

Ka

ż

de polecnie SQL wykonywane jest w imieniu pewnego u

ż

ykownika

K

ąż

dy obiekt stworzony w DBMS ma swojego w

łłłł

a

ś

ś

ś

ś

ciciela – jest on

jedyn

ą

osoba, która i ma do niego pe

ł

ny dost

ę

p

Identyfikator u

ż

ytkownika jest wykorzystywany do okre

ś

lenia, do jakich

obiektów bazy danych u

ż

ytkownik ma dost

ę

p i jakie operacje mo

ż

e na

nich wykonywa

ć

Mechanizmy bezpieczeństwa (GRANT)

Osoba z uprawnieniami administratora bazy danych mo

ż

e tworzy

ć

u

ż

ytkowników

oraz grupy u

ż

ytkowników

Tworzenie u

ż

ytkowników i grup odbywa si

ę

w nast

ę

puj

ą

cy sposób

Sk

łłłł

adnia:

GRANT CONNECT TO nazwa_u

ż

ykownika [,…]

IDENTIFIED BY password [,…]

gdzie

nazwa_u

ż

ytkownika

jest te

ż

nazw

ą

grupy, której b

ę

dzie mo

ż

na nada

ć

uprawnienia podobnie jak u

ż

ytkownikowi

Administrator mo

ż

e nast

ę

pnie nada

ć

utworzonemu u

ż

ytkownikowi uprawnienia

DBA, RESOURCES, GROUP

Sk

łłłł

adnia

GRANT {DBA | RESOURCE | GROUP}
TO nazwa_u

ż

ytkownika [,…]

Mechanizmy bezpieczeństwa (GRANT)

Aby utworzy

ć

grup

ę

musimy utworzonemu u

ż

ytkownikowi, który ma by

ć

grup

ą

,

nada

ć

uprawnienia grupy

Sk

łłłł

adnia:

GRANT GROUP,

TO nazwa_u

ż

ytkownika [,…]

Nast

ę

pnie mo

ż

emy przypisa

ć

u

ż

ytkowników do grupy

Sk

łłłł

adnia:

GRANT

MEMBERSHIP IN GROUP nazwa_grupy [, …]

TO nazwa_u

ż

ytkownika [,…]

Prawa dostępu (GRANT)

Prawa dost

ę

pu do tabel opisane w standardzie ISO to:

SELECT - umo

ż

liwia odczyt danych

Mo

ż

e by

ć

limitowany do kolumn

INSERT - umo

ż

liwia dodawanie wierszy do tabeli

UPDATE - umo

ż

liwia modyfikacj

ę

danych

Mo

ż

e by

ć

limitowany do kolumn

DELETE - umo

ż

liwia usuwanie wierszy z tabeli

ALTER - umo

ż

liwia wydanie polecenia ALTER TABLE

Polecenia zmiany struktury tabeli!

REFERENCES - umo

ż

liwia dodawanie kluczy obcych i indeksów

Mo

ż

e by

ć

limitowany do kolumn

ALL PRIVILLEGES - daje uprawnienia do wszystkich powy

ż

szych operacji

Prawa dostępu (GRANT)

U

ż

ytkownik, który tworzy tabel

ę

staje sie jej w

ł

a

ś

cicielem i uzyskuje do niej pe

ł

ny

dost

ę

p

U

ż

ytkownik, który tworzy pespektyw

ę

staje sie jej w

ł

a

ś

cicielem, ale niekoniecznie

posiada do niej pe

ł

ne prawa - prawa INSERT, UPDATE i DELETE do

perspektywy jej w

ł

a

ś

ciciel uzyskuje tylko wtedy gdy posiada odpowiednie prawa

do tabel wyst

ę

puj

ą

cych w perspektywie

Sk

ł

adnia

GRANT {ALL [ PRIVILEGES ], ALTER, DELETE, INSERT,
REFERENCES [ ( nazwa_kolumny [, ... ]) ],
SELECT [ ( nazwa_kolumny [, ...] ) ],
UPDATE [ ( nazwa_kolumny [, ...] ) ]}
ON NazwaTabeli
TO {nazwa_u

ż

ytkownika [, ...]|PUBLIC}

[ WITH GRANT OPTION ]

Klauzula WITH GRANT OPTION pozwala zbywa

ć

uzyskane prawo innym

u

ż

ytkownikom

Prawa dostępu (GRANT)

Prawa dost

ę

pu moga byc przekazywane przez:

DBA

W

ł

a

ś

ciciela tabeli

U

ż

ytkowników z opcj

ą

WITH GRANT OPTION

U

ż

ytkownik b

ę

d

ą

cy cz

ł

onkami grupy dziedzicz

ą

tylko i wy

łą

cznie prawa dost

ę

pu

do tabel, ale nie dziedzicz

ą

praw DBA, RESOURCE, GROUP

UWAGA: Grupa mo

ż

e tworzy

ć

tabel

ę

je

ś

li ma uprawnienia co najmniej

RESOURCE – loguj

ą

c si

ę

nazw

ą

_u

ż

ytkownika b

ę

d

ą

cego nazw

ą

grupy

Prawa dostępu (GRANT)

Prawa dost

ę

pu do tabel odbierane s

ą

poleceniem REVOKE

Sk

łłłł

adnia

REVOKE [GRANT OPTION FOR]{lista_praw|ALL[PRIVILEGES]}
ON NazwaTabeli
FROM {lista_identyfikatorów_u

ż

ytkowników|PUBLIC} [RESTRICT] [CASCADE]

Inne uprawnienia mog

ą

by

ć

usuwane przez administratora za pomoc

ą

polecenia

Sk

łłłł

adnia

REVOKE specialne_uprawnienia [,…] FROM userid [,…]

Gdzie specialne_uprawnienia

CONNECT

| DBA

| GROUP

| MEMBERSHIP IN GROUP userid , . . .

| RESOURCE

Prawa dostępu (GRANT)

U

ż

ytkownicy mog

ą

tworzy

ć

procedury sk

ł

adowane (o tym pó

ź

niej). U

ż

ytkownik

mo

ż

e wykonywa

ć

tak

ą

procedur

ę

o ile ma do niej uprawnienia.

Prawo wykonywania procedury nadawane jest poleceniem GRANT

Sk

łłłł

adnia

GRANT EXECUTE ON procedure_name TO nazwa_u

ż

ytkownika [,…]

Prawo wykonywania procedury odbierane jest poleceniem REVOKE

Sk

łłłł

adnia

REVOKE

EXECUTEON

procedure-name

FROM

userid [

,…

]

Dziękuję za uwagę