1

Bazy danych I

Bartosz Trybus

PWSZ Krosno

[e-mail]

Przykład bazy danych I

2

Przykład bazy danych II

Tabela pracownicy

NUMER NAZWISKO

ETAT

SZEF

PRACUJE_OD PŁACA_POD PŁACA_DOD ID_ZESP

1000 Lech

Dyrektor

01-JAN-71

3160

570

10

1080 Koliberek

sekretarka

1000

20-FEB-83

1150

10

1010 Podgajny

profesor

1000

01-MAY-75

2180

420

20

1040 Rus

adiunkt

1010

15-SEP-79

1750

20

1070 Muszy

ń

ski

adiunkt

1010

01-MAY-85

1600

20

1060 Misiecki

asystent

1010

01-MAR-85

1400

20

1090 Palusz

asystent

1040

15-SEP-89

1200

20

1020 Delcki

profesor

1000

01-SEP-77

2050

270

30

1030 Maleja

adiunkt

1020

01-JUL-68

1750

30

1100 Warski

asystent

1030

15-JUL-87

1350

30

1110 Rajski

sta

ż

ysta

1030

01-JUL-90

900

30

1050 Lubicz

adiunkt

1000

01-SEP-83

1780

40

1120 Orka

asystent

1050

01-APR-88

1350

40

1130 Kolski

sta

ż

ysta

1050

01-SEP-91

900

40

Tabela zespoły Tabela etaty

ID_ZESP

NAZWA

ADRES

NAZWA

PŁACA_MIN

PŁACA_MAX

10

Administracja

Piotrowo 3a

Sta

ż

ysta

800

1000

20

Bazy danych

Wie

ż

owa 75

Sekretarka

900

1200

30

Sieci komputerowe

Garbary 3

Asystent

1000

1600

40

Systemy operacyjne Piotrowo 3a

Adiunkt

1600

2000

50

Translatory

Mansfelda 4

Profesor

2000

2500

Dyrektor

2500

3200

Kontekst bazy danych

Z w i ą z k i z r z e c z y w i s t o ś c i ą

U ż y t k o w n i c y

B A Z A D A N Y C H

Ź

r ó d ł o d a n y c h

3

Struktura tabeli w modelu

relacyjnym

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

relacja

krotka

nazwa pola

warto

ść

U

U

pojedyncza

kolumna (pole)

Perspektywa

relacja

perspektywa

4

Operacje na relacjach

Selekcja

OPERATORY ALGEBRY RELACJI

Projekcja

Poł

ą

czenie

Operatory

teoriomnogo-

ś

ciowe

Selekcja

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

U

U

Jan

Nowak

Stanisław

Kowlaski

wyra

ż

enia, funkcje

U

U

200403

200405

5

Projekcja

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

U

U

wyra

ż

enia, funkcje

200403

200405

Kowalski

Nowak

Połączenie

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

U

U

Jan

Kowalski

Uniwersytet

Stanisław

Nowak

nazwa

typ_uczelni

Akademia

Uniwersytet

U

P

P

A

P

Politechnika

P

A

Uniwersytet

6

Operatory teoriomnogościowe

• unia - sumowanie krotek dwóch lub więcej relacji;

warunkiem jest zgodność liczby i typów
atrybutów relacji źródłowych;

• przekrój - iloczyn zbiorów krotek dwóch lub

więcej relacji tzn. zbiór tych krotek, które
występują jednocześnie w tych relacjach;
warunkiem jest zgodność liczby i typów
atrybutów;

• różnica - wyodrębnia krotki występujące

wyłącznie w pierwszej spośród relacji
wyjściowych.

Własności relacyjnej bazy

danych

• Relacyjna baza danych jest widziana przez użytkownika

jako zbiór relacji

• Dostępny jest zbiór operatorów umożliwiających łączenie

lub wydzielanie części relacji

• Występuje całkowita niezależność danych
• Nie istnieją jawne wskaźniki; powiązania danych są

realizowane za pomocą samych danych (wspólnych
wartości atrybutów)

• Do przetwarzania struktur relacyjnych jest stosowany

język nieproceduralny

• Użytkownik nie specyfikuje ścieżek dostępu do danych i

nie musi znać fizycznej reprezentacji danych.

7

Klucz relacji

• Kluczem relacji nazywamy taki zbiór atrybutów relacji, dla

których

kombinacje

ich

wartości

jednoznacznie

identyfikują każdą krotkę tej relacji. Wymaga się, aby
ż

aden podzbiór klucza nie był kluczem.

• W przypadku ogólnym w relacji można wyodrębnić wiele

kluczy, które nazywamy kluczami potencjalnymi
(kandydującymi). Klucz wybrany z potencjalnych
określony jest jako klucz główny. Klucz prosty jest
jednoelementowy, a złożony składa się z więcej niż
jednego atrybutu.

• Klucz obcy odnosi się do takiego atrybutu relacji, który

występuje jako klucz główny w innej relacji.

Języki baz danych

• język definiowania danych DDL (Data

Definition Language)

• język manipulowania danymi DML (Data

Manipulation Language)

• język sterowania danymi DCL (Data

Control Language)

• język zapytań QL (Query Language)

8

Użytkownicy języka SQL

Składnia poleceń SQL

9

Proste polecenia SELECT

select * from pracownik;

select nazwisko, etat from pracownik;

Proste polecenia SELECT c.d.

• Przypuśćmy, że adiunktom zabiera się 20%

pensji na podatek dochodowy.
Zaproponować obliczenie takiego podatku
w oparciu o tabelę pracownik.

select nazwisko, placa_pod,

placa_pod*0.20 podatek

from pracownik

where etat = 'ADIUNKT';

10

Proste polecenia SELECT c.d.

• Obliczyć całkowite dochody roczne

wszystkich profesorów

select nazwisko, placa_pod*12+placa_dod

as dochód

from pracownik

where etat = 'PROFESOR';

Proste polecenia SELECT c.d.

• Wybrać wszystkich pracowników, których

pensja podstawowa jest mniejsza od
dwukrotnej pensji dodatkowej.

select nazwisko, placa_pod, placa_dod

from pracownik

where placa_pod<2*placa_dod;

11

Wyszukiwanie danych - where

select atrybuty_projekcji

from relacje

where warunki_do_spełnienia;

Możliwe operatory:

=, !=, >, <, >=, <=,

is [not] null,

between... and...,

in(zbiór),

like wzorzec

Znaki specjalne we wzorcu: %, _

Niektóre operatory mogą być poprzedzone słowem „not”

select nazwisko

from pracownik

where nazwisko like ’L%’;

Wyszukiwanie danych – where

Przykłady

Operatory logiczne:

and, or, not

• Wyznaczyć wszystkich adiunktów i profesorów, których
płaca podstawowa jest wyższa od 1750.

select nazwisko, etat, placa_pod

from pracownik

where placa_pod>1750

and (etat=’adiunkt’ or etat=’profesor’);

12

Porządkowanie – order by

select nazwisko, pracuje_od

from pracownik

where etat=’ASYSTENT’

order by pracuje_od;

• Sporządzić spis wszystkich asystentów w kolejności ustalonej
datą ich zatrudnienia.

Modyfikowanie zawartości

relacji

• Wstawianie krotek

insert into nazwa_relacji [(atrybut1,

atrybut2,...)]

values (warto

ść

1, warto

ść

2,...);

• Dodać do relacji pracownik nową osobę opisaną

następującymi atrybutami:

NUMER

NAZWISKO

ETAT

SZEF

PRACUJE_OD

PŁACA_POD

PŁACA_DOD

ID_ZESP

1140 Morawski

sta

ż

ysta

1010

Warto

ść

domy

ś

lna

950

Warto

ść

domy

ś

lna

20

insert into pracownik (NUMER, NAZWISKO, ETAT, SZEF,

PLACA_POD, ID_ZESP)

values (1140, 'Morawski', 'STA

ś

YSTA', 1010, 950, 20);

13

Modyfikowanie krotek

• Składnia

update relacja [alias]

set atrybut [,atrybut] =

{wyra

ż

enie | podzapytanie}

[where warunki];

Modyfikowanie krotek - przykłady

• Zmienić błędnie zawyżoną płacę podstawową p.

Morawskiego na 900zł.

• Zaktualizować płace na podstawie dodatkowej

tabeli

update pracownik

set placa_pod = (select placa_pod from dodatki d

where d.numer = pracownik.numer)

where numer in

(select numer from dodatki);

14

Usuwanie krotek

• Składnia

delete from relacja

[where warunki];

• Przykład

delete from pracownik

where etat=‘asystent’;

Tworzenie schematu bazy danych

create table relacja

(nazwa_atrybutu typ(rozmiar) [default warto

ść

_domy

ś

lna]

[[constraint nazwa_ogr] ograniczenie_atrybutu],

nazwa_atrybutu typ(rozmiar) [default warto

ść

_domy

ś

lna]

[[constraint nazwa_ogr] ograniczenie_atrybutu],

...

[[constraint nazwa_ogr] ograniczenie_relacji, …]

);

15

Typy atrybutów i ograniczeń

• number(r)

• number(r,n)

• char(r)

• varchar2(r)

• date

• clob

• blob

• not null

• primary key

• unique

• references

• check

Tworzenie tabel zespoły i etaty

CREATE TABLE zespoly

(

id_zesp number(2) constraint pk_id_zesp primary

key,

nazwa varchar2(20) not null,

adres varchar2(20)

);

CREATE TABLE etaty

(

nazwa varchar2(10) constraint pk_nazwa primary

key,

placa_min number(6,2) not null constraint

pl_min check(placa_min>0),

placa_max number(6,2) not null constraint

pl_max check(placa_max<5000)

);

16

Dodawanie atrybutu

alter table relacja

add (nazwa_atrybutu typ(rozmiar)

[default warto

ść

_domy

ś

lna]

[[constraint nazwa_ograniczenia]

ograniczenie_atrybutu]);

Przykład:

alter table pracownik

add (tytul_nauk varchar2(10));

Dodawanie ograniczenia relacji

alter table relacja

add ([[constraint nazwa_ograniczenia]

ograniczenie_relacji]);

Przykład:

alter table pracownik

add (constraint prac_fk foreign key

(id_zesp) references zespol(id_zesp));

17

Modyfikowanie atrybutu

alter table relacja

modify (nazwa_atrybutu typ(rozmiar)

[default warto

ść

_domy

ś

lna]

null | not null);

Przykład:

alter table pracownik

modify (tytul_nauk varchar2(15) default

’mgr in

ż

.’ not null);

Zmiana nazwy i usuwanie relacji

rename stara_nazwa to nowa_nazwa;

drop table relacja [cascade constraints];

18

Grupowanie danych –

group by

select id_zesp, etat, count(*)

from pracownik

where etat != ’dyrektor’

group by id_zesp, etat;

• Wyświetlić informację o liczbie poszczególnych etatów.

• Wyświetlić informację o liczbie poszczególnych etatów w
zespołach.

Grupowanie danych –

having

select id_zesp, avg(placa_pod)

from pracownik

group by id_zesp

having count(*)>3;

• Podać informację o średniej płacy w zespołach
liczących powyżej trzech pracowników.

19

Funkcje operujące na grupach krotek

•

avg([distinct|all] atr)

select etat, avg(placa_pod) srednia_placa

from pracownik

group by etat;

•

count([distinct|all] wyr)

select id_zesp, etat, count(*) liczba_zatr

from pracownik

group by id_zesp, etat;

•

max([distinct|all] wyr)

select id_zesp, max(placa_pod)

from pracownik

group by id_zesp;

•

sum([distinct|all] atr)

select etat, sum(placa_pod)

from pracownik

where etat=‘asystent’ or etat=‘adiunkt’

group by etat;

Poziome łączenie relacji

• Problem: Podać informację o tym, w jakich zespołach

pracują poszczególni pracownicy.

• Iloczyn kartezjański (!)

select *

from pracownik, zespol;

• Połączenie zgodnie z wartościami atrybutów

select atrybuty

from relacje

where warunek_poł

ą

czenia;

20

Poziome łączenie relacji -

przykład

select nazwisko, nazwa

from pracownik, zespol

where pracownik.id_zesp=zespol.id_zesp;

• z użyciem aliasów

select nazwisko, nazwa

from pracownik p, zespol z

where p.id_zesp=z.id_zesp;

Poziome łączenie relacji - zadanie

• Wyświetlić listę zawierającą nazwisko

pracownika, nazwę zespołu do którego
należy, płacę podstawową oraz „widełki
płacowe” dla etatu, na którym jest
zatrudniony

21

Poziome łączenie relacji

Szersze możliwości

• Klauzule:

– JOIN

– LEFT OUTER JOIN (RIGHT OUTER JOIN)

– FULL OUTER JOIN

select nazwa, nazwisko

from zespol z left outer join pracownik p

on p.id_zesp = z.id_zesp;

Zagnieżdżanie zapytań

• Tryb nieskorelowany

select atrybutA1, atrybutA2, ...

from relacjaA

where atrybut

operator

(select atrybutB1, atrybutB2, ...

from relacjaB

where warunek);

• Operator

• in
• any (some)
• all
• exists

22

Zagnieżdżanie zapytań – przykład 1

• Wyświetlić wszystkich pracowników

zatrudnionych na tym samym etacie co pracownik
Orka, wraz z ich płacą podstawową:

select nazwisko, placa_pod

from pracownik

where etat =

(select etat

from pracownik

where nazwisko=‘Orka’);

Zagnieżdżanie zapytań – przykład 2

• Wyznaczyć tych pracowników, którzy zarabiają

mniej niż każdy pracownik z zespołu 20:

select nazwisko, placa_pod

from pracownik

where placa_pod > all

(select placa_pod

from pracownik

where id_zesp=20);

23

Tryb skorelowany 1

• Podać informacje o tych pracownikach, których

płaca podstawowa jest wyższa niż przeciętna dla
etatu, na którym są zatrudnieni:

select nazwisko, placa_pod, etat

from pracownik p

where placa_pod >

(select avg(placa_pod)

from pracownik

where etat = p.etat)

order by etat;

Tryb skorelowany 2

• Wyznaczyć pracowników, którzy są zatrudnieni

na etatach, na których nie jest zatrudniony nikt
inny.

select numer, nazwisko, etat

from pracownik p

where not exists

(select numer

from pracownik

where etat = p.etat

and numer!=p.numer)

order by numer;

24

Pionowe łączenie relacji

select atrybuty_od_1_do_n

from relacja1

where warunki1

operator

select atrybuty_od_1_do_n

from relacja2

where warunki2

order by 1,...,n;

Operatory:

union, intersect, minus

Pionowe łączenie relacji -

przykład

• Określić te etaty w zespołach 30 i 40, dla których

pewnym pracownikom należącym do różnych zespołów
przyznano jednakowe płace podstawowe.

select etat, placa_pod

from pracownik

where id_zesp = 30

intersect

select etat, placa_pod

from pracownik

where id_zesp = 40;

25

Pionowe łączenie relacji – przykład

2

• Wyświetlić listę zespołów wraz z

pracownikami

select nazwa, nazwisko

from zespol z, pracownik p

where p.id_zesp = z.id_zesp

union

select nazwa, null

from zespol z

where z.id_zesp not in (select id_zesp from

pracownik);

• Ten sam efekt można uzyskać za pomocą

poziomego łączenia zewnętrznego

Funkcje operujące na krotkach

pojedynczych

• abs(wyrażenie)
• add_months(data,n)

select nazwisko, pracuje_od, add_months(pracuje_od,-3)

from pracownik

where id_zesp=10;

• length(wyrażenie)

select nazwa, length(nazwa), length(‘Dzie

ń

dobry’)

from zespol;

• nvl(wyrażenie,wartość)

select nazwisko, placa_pod*12+nvl(placa_dod,0)

roczny_dochod

from pracownik;

26

Funkcje operujące na krotkach

pojedynczych c.d.

• sqrt(wyrażenie)
• substr(wyrażenie,poz,n)

select nazwisko, substr(nazwisko,2),

substr(nazwisko,2,4)

from pracownik

where id_zesp=10;

• trunc(wyrażenie,n)
• upper(wyrażenie)
• vsize(wyrażenie)

select nazwisko, placa_pod, vsize(placa_pod),

pracuje_od, vsize(pracuje_od)

from pracownik where id_zesp=10;

Daty w Oracle

• DATE

:

wiek, rok, miesiąc, dzień, godzina,

minuta, sekunda

• Operacje na datach

– dodawanie i odejmowanie liczby

– odejmowanie innej daty

• SYSDATE

select sysdate, pracuje_od, pracuje_od+7

from pracownik

where pracuje_od like ’%05%’;

27

Perspektywy

• Składnia

create [or replace] view nazwa_perspektywy

[(atrybut1, atrybut2, ...)]

as select ciało_polecenia_SELECT

[with check option];

• Przykład

create view asystenci

as select numer, nazwisko

from pracownik

where etat='asystent';

• Usuwanie

drop view nazwa_perspektywy

Perspektywy proste i złożone

28

Perspektywy złożone

• Przykład

create view pods_zesp

(nazwa, placa_min, placa_max, placa_przec)

as select nazwa, min(placa_pod), max(placa_pod),

avg(placa_pod)

from pracownik, zespol

where pracownik.id_zesp=zespol.id_zesp

group by nazwa;

• Dostęp do perspektywy: select, insert*,

update*, delete*

Zasięg perspektyw

• Przykład

create or replace view adiunkci

as select numer, nazwisko, etat

from pracownik

where etat='adiunkt'

with check option;

• Modyfikowanie

update adiunkci

set etat=‘asystent'

where nazwisko='Rus';

29

Liczniki

• Składnia

create sequence [uzytkownik] nazwa_licznika

[increment by liczba]

[start with warto

ść

_pocz

ą

tkowa]

[minvalue][maxvalue]

[cycle|nocycle];

• Odczyt wartości

select nazwa_licznika.nextval from dual;

select nazwa_licznika.currval from dual;

Liczniki - przykład

create sequence moj_licznik;

select moj_licznik.nextval from

dual;

select moj_licznik.nextval from

dual;

select moj_licznik.currval from

dual;

30

Liczniki - zastosowanie

INSERT INTO zamowienie (nr_zam,

id_kl, data_zam)

VALUES (moj_licznik.nextval, ’08’,

SYSDATE);

INSERT INTO pozycje_zamowienie

(nr_zam, kod_w, il_zam)

VALUES (moj_licznik.currval,

’005’, 100);

Architektura klient-serwer

Pojęcia:

• klient

• serwer

• przetwarzanie rozproszone

• rozproszona baza danych

• skalowalność pozioma i
pionowa

31

Architektura wielowarstwowa

Ochrona danych

32

Sterowanie dostępem

• Elementy:

– ewidencja użytkowników

– identyfikatory i hasła

– grupy

– prawa do określonych obiektów bazy danych

– administrator bazy danych

Obiekty bazy danych

33

Prawa dostępu do relacji

Przywileje w Oracle

•

Składnia

grant przywilej

to u

ż

ytkownik

identified by hasło;

[cycle|nocycle];

•

Typowe przywileje:

•

connect

•

resource

•

dba

•

Przykład:

grant connect to janek identified by tajne_haslo;

•

Odwołanie przywilejów: revoke