Bazy danych I

Bartosz Trybus

PWSZ Krosno

Bartosz.Trybus@pwsz.krosno.pl

Przykład bazy danych I

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

1080 Koliberek

sekretarka

1000

20-FEB-83

1150

1010 Podgajny

profesor

1000

01-MAY-75

2180

420

1040 Rus

adiunkt

1010

15-SEP-79

1750

1070 Muszy

ski

adiunkt

1010

01-MAY-85

1600

1060 Misiecki

asystent

1010

01-MAR-85

1400

1090 Palusz

asystent

1040

15-SEP-89

1200

1020 Delcki

profesor

1000

01-SEP-77

2050

270

1030 Maleja

adiunkt

1020

01-JUL-68

1750

1100 Warski

asystent

1030

15-JUL-87

1350

1110 Rajski

sta

ysta

1030

01-JUL-90

900

1050 Lubicz

adiunkt

1000

01-SEP-83

1780

1120 Orka

asystent

1050

01-APR-88

1350

1130 Kolski

sta

ysta

1050

01-SEP-91

900

Tabela zespoły Tabela etaty

ID_ZESP

NAZWA

ADRES

NAZWA

PŁACA_MIN

PŁACA_MAX

Administracja

Piotrowo 3a

Sta

ysta

800

1000

Bazy danych

Wie

owa 75

Sekretarka

900

1200

Sieci komputerowe

Garbary 3

Asystent

1000

1600

Systemy operacyjne Piotrowo 3a

Adiunkt

1600

2000

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

Struktura tabeli w modelu

relacyjnym

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

relacja

krotka

nazwa pola

warto

ść

pojedyncza

kolumna (pole)

Perspektywa

relacja

perspektywa

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

Jan

Nowak

Stanisław

Kowlaski

wyra

enia, funkcje

200403

200405

Projekcja

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

wyra

enia, funkcje

200403

200405

Kowalski

Nowak

Połączenie

numer

imię

typ_uczelni

nazwisko

200403

200405

Jan

Stanisław

Kowalski

Nowak

Jan

Kowalski

Uniwersytet

Stanisław

Nowak

nazwa

typ_uczelni

Akademia

Uniwersytet

Politechnika

Uniwersytet

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.

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)

UŜytkownicy języka SQL

Składnia poleceń SQL

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';

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;

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’);

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

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

PLACA_POD, ID_ZESP)

values (1140, 'Morawski', 'STA

YSTA', 1010, 950, 20);

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);

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, …]

);

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)

);

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));

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];

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.

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;

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

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

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);

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;

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;

Pionowe łączenie relacji – przykład

• 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;

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%’;

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

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';

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;

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

Architektura wielowarstwowa

Ochrona danych

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

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