1 sem. IIS PWSZ Elbląg

mgr inż. Marzanna Skowrońska

[e-mail]

IIS p.317

Systemy Baz Danych

Systemy Baz Danych

2

1.

C. J. Date. „

Wprowadzenie do systemów baz

danych

”.

WNT Warszawa, 2000.

2.

J. D. Ulman. „Systemy baz danych”. WNT, Warszawa,

1988.

każda książka, publikacja strona na temat

skrót slajdów z wykładu – na koniec semestru

Literatura

3

Codd Edgar ‘Ted’

Wielka Brytania,

matematyka na Oxfordzie; wojna - lotnik

USA – IBM

niech

ęć

do obsesyjnego ‘polowania na czarownice’

prowadzonego przez senatora McCarthy’ego, wyjazd

Kanada

Powrót do USA i IBM

19.08.

1969

r. publikacja

ż

yciowego dzieła

A Relational Model of Data for Large Shared Data Banks

Zmarł 23.04.2003 w wieku 79 lat

4

Baza danych - zbiór

zbiór

powiązanych ze sobą informacji, opisujących

fakty i zdarzenia, zachodzące w pewnym wycinku
rzeczywistości, zorganizowanych w strukturę pozwalającą na
ich szybkie wyszukiwanie i analizę

zbiór

danych trwałych wykorzystywanych przez system

aplikacji

zbiór

wzajemnie powiązanych danych, mający pewną

strukturę, pamiętanych bez zbędnej nadmiarowości
(redundancji), niezależny od oprogramowania, które nim
zarządza

Baza danych <> Program/Aplikacja/ System

5

dane
sprzęt
programy
użytkownicy

System baz danych – DataBase System

tworzą

data
hardware
software
users

(skomputeryzowany system przechowywania

danych/informacji/rekordów)

6

Stan a schemat bazy danych

Dane w bazie danych <> Opis bazy danych

Stan bazy danych - zbiór wartości
przechowywanych w bazie danych w danym
momencie czasu

Schemat bazy danych (model danych) -
zbiór pojęć wykorzystywanych do opisu bazy
danych, czyli opis struktury bazy danych jak:
-typy danych,
-związki pomiędzy danymi,
-ograniczenia, które dane musza spełnić

7

DBMS - DataBase Management System

SZBD - System zarządzania bazą danych

oprogramowanie, które realizuje wszelki dostęp do

bazy tzw. zarządca bazy danych, czyli

oprogramowanie działające

między fizyczną bazą

danych a użytkownikiem

- uwalniające użytkownika

od znajomości szczegółów technicznych np.:
zależnych od sprzętu; często udostępnia
użytkownikowi język komunikacji z bazą np.: SQL -
tzw. języki wyższego poziomu lub 4GL

8

Funkcje DBMS

Definiowanie danych

wprowadzanie i zmiana schematów

->Słownik danych

miejsce definicji bazy, metadane, dane o danych, sam
słownik też jest bazą

Manipulowanie danymi

wyszukiwanie, aktualizacja, usuwanie danych

Ochrona i konserwacja
udostępnianie,zabezpieczanie przed nieuprawnionym
dostępem, eliminacja prób naruszenia bezpieczeństwa,
narzucanie protokołów kontroli odzyskiwania danych
i współbieżności

9

Serwer = DBMS

Można przyjąć architekturę systemów baz danych za system
o 2 częściowej architekturze klient - server

Architektura systemów baz danych

Klient = Aplikacja

Użytkownik

Baza danych

Frontend

Backend

serwer pełni role
DBMS, klientami
mogą być aplikacje
napisane przez
użytkowników jak i
wbudowane, czyli
dostarczone razem z
DBMS

10

Sie

ć

komunikacyjna

Serwer = DBMS

Klient 1

Klient 2

Klient 3

Klient 4

Gdy klient i server na różnych komputerach połączonych w sieć, to
jest to przetwarzanie rozproszone, czyli pojedyncze zadanie
przetwarzania danych rozłożone na kilka maszyn.

Architektura CS Client- Server

I ten wariant
zwykło się
nazywać
klient-server

11

Sie

ć

komunikacyjna

Serwer A

Klienci A

Serwer B

Klienci B

Serwer C

Klienci C

Serwer D

Klienci D

W rozproszonej architekturze każda maszyna może przechowywać
dane i być serwerem dla jednych klientów, natomiast klientem dla
innych. Każda maszyna wspiera cały DBMS.

Architektura obiektów rozproszonych

12

byt, jednostka, rozróżnialny obiekt, element w zbiorze,

coś, co istnieje i ma być reprezentowane w bazie danych

-- np. Pracownik, Utwór, Samochód

Encja = entity

Krotka

– instancja obiektu, konstruktor typu encji

np.{‘Jan’,’Kowalski’,’kierowca’,1234}, {‘Bolero’,’Ravel’},
{‘czarny’,’lexus’}

Są różne rodzaje np..:
• obiekty materialne - osoba, samochód, klasa, grupa studentów
• obiekty koncepcyjne – rachunek bankowy, stanowisko,
• zdarzenia - wysłanie towaru (data wysłania, nazwa towaru,
symbol, nazwa i adres odbiorcy),
• fakty - znajomość języka (nazwa języka, czas nauki, stopień
znajomości).

13

-grupa wszystkich podobnych encji, podobnego typu
=>

typ encji

Każdemu takiemu zbiorowi przyporządkowujemy
jednoznaczną nazwę.

Często entity set = entity

np. Pracownicy, Utwory, Samochody

Zbiór encji = entity set

14

Encje silne i słabe

Encje dzielimy na

•silne/ regularne/ właciwe – nie zależy od innych encji

•słabe – istnienie encji zależy od innej i z chwilą
‘śmierci’ tej drugiej sama przestaje istnieć

15

- zależność między podstawowymi zbiorami encji
(encjami).

Związki są też specyficznymi encjami.

np. Zatrudnienie (pracownik-pracodawca),

Nagranie (zespół – utwór)

Związek = relationship

16

-cecha, własność encji istotna z punktu widzenia
projektu bazy przypisujące każdej encji wartość z
dziedziny atrybutu.

Każdemu takiemu atrybutowi
przyporządkowujemy jednoznaczną nazwę.

np. Pracownik(imię, nazwisko, stanowisko,nr_prac)

Atrybut = attribute

17

atrybut kluczowy lub zestaw atrybutów jednoznacznie
identyfikujący każdą encję w zbiorze;
wartości tego atrybutu w zbiorze encji są unikalne = nie
powtarzają się.

Klucz = key

np. dla

Pracownik(imię, nazwisko, stanowisko,nr_prac, data_ur)

kluczem jest
1) nr_prac,
2) imię, nazwisko, stanowisko,data_ur

ale ...

18

jednojednoznaczny

jednoznaczny

wieloznaczny

Klasyfikacja związków encji

19

każda encja z dowolnego zbioru encji
może być skojarzona z co najwyżej
jednym elementem z drugiego zbioru

•

•

•

•

A

•

•

•

•

B

Związek jednojednoznaczny 1:1

np. tożsamość osoby i pesel;

małżeństwo, ale...

20

•

•

•

•

A

każda encja ze zbioru A może być
skojarzona z pewną ich liczbą ze
zbioru B, natomiast ze zbioru B co
najwyżej z jedną ze zbioru A

•

B

•

•

•

•

Związek jednoznaczny 1:n

np.ojcowie-dzieci; małżeństwo, ale...

21

dowolna liczba encji ze zbioru A
może być skojarzona z dowolną
liczbą encji ze zbioru B

•

•

•

•

A

•

B

•

•

•

•

Związek wieloznaczny m:n

np. komputery-studenci; małżeństwo ? partnerstwo

22

Przedmiot

Nauczyciel

Termin

Student

jest

prow.

odbywa si

ę

jest słuchaczem

nr

rodzaj

nazwa

godziny

nazwisko

imi

ę

nr leg.

dzie

ń

tyg.

godzina

sala

nazwisko

imi

ę

nr ind.

Encje,atrybuty,związki-diagram przyk.1

i to juz jest logiczny Entity-Relationship Diagram =ERD

23

Pewna firma logistyczna realizuje dostawy

produktów

różnych

kategorii

. Współpracuje ona z wieloma

dostawcami

. Każdy

dostawca może

dostarczać

wiele produktów, ale jeden produkt

nie może być zamawiany u więcej niż jednego dostawcy.
Odbiorcami produktów są

klienci

.

Klienci

składają

zamówienia

na produkty. Można zamawiać

wiele produktów na jednym zamówieniu. Każde zamówienie

jest

obsługiwane

przez konkretną

firmę przewozową

i

pracownika

firmy. Celem łatwiejszej obsługi, pracowników

przydziela się

do

terytoriów

obsługi dostaw, a terytoria łączy w większe jednostki

–

regiony

, zaś klientów

grupuje się

w

jednostki demograficzne

.

Jeden pracownik może należeć do jednego terytorium, tak jak
jeden klient może należeć tylko do jednej jednostki
demograficznej.

Encje,związki -scenariusz

24

Diagram ERD

/z aplikacji wspomagającej projektowanie graficzne/

Region

PK

RegionID

RegionDescription

Shippers

PK

ShipperID

Com panyNam e
Phone

Orders

PK

OrderID

FK1

CustomerID

FK2

EmployeeID
OrderDate
RequiredDate
ShippedDate

FK3

ShipVia
Freight
ShipName
ShipAddress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry

Employees

PK

Em ployeeID

LastNam e
FirstNam e
Title
TitleOfCourtesy
BirthDate
HireDate
Address
City
Region
PostalCode
Country
HomePhone
Extension
Photo
Notes
ReportsTo
PhotoPath

Suppliers

PK

SupplierID

Com panyNam e
ContactName
ContactTitle
Address
City
Region
PostalCode
Country
Phone
Fax
HomePage

Categories

PK

CategoryID

CategoryNam e
Description
Picture

CustomerCustomerDemo

PK,FK2

Custom erID

PK,FK1

Custom erTypeID

Territories

PK

TerritoryID

TerritoryDescription

FK1

RegionID

Products

PK

ProductID

ProductNam e

FK2

SupplierID

FK1

CategoryID
QuantityPerUnit
UnitPrice
UnitsInStock
UnitsOnOrder
ReorderLevel
Discontinued

CustomerDemographics

PK

Custom erTypeID

CustomerDesc

EmployeeTerritories

PK,FK1

Em ployeeID

PK,FK2

TerritoryID

Customers

PK

Custom erID

Com panyNam e
ContactName
ContactTitle
Address
City
Region
PostalCode
Country
Phone
Fax

Order Details

PK,FK1

OrderID

PK,FK2

ProductID

UnitPrice
Quantity
Discount

Northwind

Database

Diagram

25

*** Model danych ***

konceptualne postrzeganie danych, ich właściwości oraz

związków między danymi

zbiór notacji oraz narzędzi konceptualnych służących do

opisu



struktur danych,



związków między danymi,



związków semantycznych (znaczeniowych),



związków integralnościowych (nienaruszalności

poprawności),



sposobów manipulowania danymi (operatorów)

26

Rodzaje modeli danych

Model sieciowy

Model hierarhiczny

Model relacyjny

Model obiektowy

przeszłość

zdecydowana
teraźniejszość

słaba teraźniejszość/
zdecydowana przyszłość

Modle sieciowy i hierarchiczny zostały zdefiniowane „post factum”, przez
uogólnienie z istniejących już implementacji.
Model relacyjny był pierwszym abstrakcyjnym modelem danych

27

Model relacyjny - charakterystyka

Każda relacja w bazie danych jest jednoznacznie określona przez swoją

nazwę.

Każda kolumna w relacji ma jednoznaczną nazwę
Kolumny relacji (nazywane też atrybutami) tworzą zbiór

nieuporządkowany.

Wszystkie wartości w danej kolumnie muszą być tego samego typu.

Zbiór możliwych wartości elementów danej kolumny nazywany bywa też jej
dziedziną.

Wiersze relacji (nazywane też encjami) tworzą nieuporządkowany

zbiór; w szczególności, nie ma powtarzających się wierszy.

Każde pole (przecięcie wiersza z kolumną) zawiera wartość atomową z

dziedziny określonej przez kolumnę. Brakowi wartosci odpowiada wartość
specjalna NULL.

Każda relacja zawiera klucz główny, której wartości jednoznacznie

identyfikują wiersz (a więc nie powtarzają się i nie są NULL).

Do wiązania ze sobą danych przechowywanych w różnych tabelach

używa się kluczy obcych.

28

Model relacyjny - operacje

operacje na danych definiuje się w terminach tzw.
algebry relacyjnej
operatory algebry relacyjnej mają za argumenty jedną
lub więcej relacji,
wynikiem działania jest zawsze relacja. - tj. instrukcje
do przetwarzania danych generują z istniejących tabel
nowe tabele
obejmują co najmniej operatory:

wyboru

SELECT

- wybiera określone wiersze tabeli

rzutu

PROJECT

- wybiera określone kolumny z tabeli

złączenia

JOIN

- łączy dwie tabele na podstawie tych samych

wartości we wspólnej kolumnie

dalej:

suma,
różnica,
przecięcie,
iloczyn kartezjański

29

Model relacyjny – cechy operacji

wynik operacji na tabelach jest tabelą, która może stanowić
dane wejściowe dla kolejnego polecenia - tabele będące
wynikami operacji nie muszą być tabelami fizycznie
istniejącymi
prócz tabel bazowych (podstawowych), fizycznie
istniejących w bazie, istnieją tabele pochodne (nazwane i
przechowywane to perspektywy) będące wynikiem
wykonania operacji na pozostałych tabelach - nie muszą być
przechowywane

poszczególne operacje przetwarzają całe zbiory rekordów,

nie pojedyncze rekordy - zarówno dane wejściowe jak i
wyjściowe są całymi tabelami - główny wyróżnik systemów
relacyjnych

Zob.

TOPEMPS

30

Model obiektowy – paradygmat obiektowości

zagadnienie związane z obiektowymi językami programowania (Java, Smalltalk,C#, C++)

Obiekt - ,,pakiet danych i procedur'', wyposażony w jednoznaczną

tożsamość; obiektowe języki programowania nie ograniczają się do
prostych typów danych (liczba całkowita, znak, ...) umożliwiają
tworzenie abstrakcyjnych typów danych, składających się z wartości
(zwanych atrybutami) oraz procedur lub funkcji (zwanych metodami)

Klasa to typ (wzorzec obiektów), może być więc uważana za definicję

pewnego typu danych; dostępne są klasy pochodzące z zewnętrznych
bibliotek.

Dziedziczenie jest sposobem tworzenia nowych klas, poprzez

modyfikację i/lub wzbogacenie już istniejącej definicji klasy

Abstrakcja czy enkapsulacja danych polega na tym, że szczególy

implementacji danego obiektu nie są bezpośrednio dostępne z zewnątrz --
oddziaływanie z innymi obiektami zachodzi jedynie za pośrednictwem
jasno określonego zestawu metod (tzw. publicznych) , tworzących
interfejs obiektu.

31

Model obiektowy

trwały zapis obiektowych struktur danych

definiowanie danych to określenie klas obiektów

(zarówno ich atrybutów jak i metod);

związki między obiektami wyrażają się w

dziedziczeniu i agregacji (związek całość-część)

operacje na danych realizuje się przez odwołania do

metod.

32

Model obiektowy - uwagi

Jak dotąd nie ma standardów ani szeroko stosowanych

systemów zarządzania bazami danych opartych na
czystym modelu obiektowym

Obecne na rynku ObjectDBMS:
Jasmine [Computer Associates], Gemstone, O2, Object Store [Object Design],
Objectivity/DB [Objectivity], Versant ODMBS [Versant]

• Coraz częściej spotyka się wprowadzanie rozszerzeń
obiektowych do relacyjnych DBMS.

• Metoda obiektowa, jako tzw. modelowanie obiektowe,
znajduje za to zastosowanie w projektowaniu złożonych
systemów oprogramowania, których obiektowy model bazy
danych może być implementowany takżew modelu
relacyjnym.

33

*** Model relacyjny ***

model relacyjny oparty jest na tylko jednej podstawowej
strukturze danych -- relacji.

baza danych jest zbiorem relacji

pojęcie relacji można uważać za pewną abstrakcję
intuicyjnego pojęcia tabeli, zbudowanej z wierszy
i kolumn, w której na przecięciu każdej kolumny z
każdym wierzem występuje określona wartość.

zbiory danych – tabele, gdzie
atrybury są kolumnami tabeli, a encje wierszami

34

S#
S1
S2
S3
S4
S5

SNAME

Smith

Jones

Blake

Clark

Adams

STATUS

20
10
30
20
30

CITY

Londyn

Paryż
Paryż

Londyn

Ateny

Snr

Sname

Status

City

Dziedziny

klucz

główny

Relacja

/tabela

Krotki

/wiersze

li

cz

eb

n

o

ść

Atrybuty

/kolumny

stopień tabeli

Terminologia - nieformalnie

35

typ danych (w rozumieniu języków programowania),

zbiór dopuszczalnych wartości skalarnych (atomowych,

nierozkładalnych, nie mających struktury wewnętrznej)
będących tego samego typu co dany atrybut oraz
posiadających odpowiedni dla niego zakres wartości

np. skalar to nazwa miasta, dziedzina – zbiór wszystkich nazw
miast,
dziedzinę dla atrybutu

QTY

(wielkości dostaw) tabeli

Dostawy

możemy zdefiniować jako liczby całkowite >0 i <10 000.

Dziedzina danego atrybutu

domain

36

Krotka

konstruktor typu, instancja obiektu, wystąpienie encji

zbiór uporządkowanych par

{ <A

1

:v

1

>, <A

2

:v

2

>, ... , <A

n

: v

n

> }

gdzie A

i

-nazwa atrybutu,v

i

-wartość z dziedziny, czyli typ

<

nazwa-atrybutu

:

warto

ść

-z-dziedziny

>

37

Relacja

Relacja R

⊆

⊆

⊆

⊆

D

1

××××

...

××××

D

n

na zbiorze dziedzin

D

1

,..,

D

n

Relacja jest podzbiorem iloczynu kartezjańskiego swoich dziedzin

1.

nagłówka

(tytułów kolumn)

- ustalony zbiór par

{ <A

1

:D

1

>, <A

2

:D

2

>, ... , <A

n

: D

n

> } , czyli typ

〈〈〈〈

nazwa-atrybutu

:

nazwa-dziedziny

〉〉〉〉

Schemat
relacji

2.

treści

- zbiór krotek, z których każda jest zbiorem par

{ <A

1

:v

1

>, <A

2

:v

2

>, ... , <A

n

: v

n

> } , czyli typ

〈〈〈〈

nazwa-atrybutu

:

warto

ść

〉〉〉〉

składa się z:

38

{

〈〈〈〈

S# : Snr

〉〉〉〉

,

〈〈〈〈

SNAME : Sname

〉〉〉〉

,

〈〈〈〈

STATUS : Status

〉〉〉〉

,

〈〈〈〈

CITY : City

〉〉〉〉

}

Tabele nie są relacjami w sensie poprzedniej definicji.
Można je jednak traktować jako obraz relacji - o ile ustalimy jak
czytać taki obraz. Przeczytajmy tabelę dostawców:

1.Nagłówek

atrybut :dziedzina

2.Treś: tabela to zbiór wierszy,

np. wiersz

( ‘S1’, ‘Smith’, 20, ‘Londyn’)

każdy wiersz możemy interpretować jako zbiór par:

{

〈〈〈〈

S# : ‘S1’

〉〉〉〉

,

〈〈〈〈

SNAME : ‘Smith’

〉〉〉〉

,

〈〈〈〈

STATUS : 20

〉〉〉〉

,

〈〈〈〈

CITY : ‘Londyn’

〉〉〉〉

}

Przykład relacji

wartości_atrybutu

39

1. krotki nie są uporządkowane: relacje są zbiorami; tabele są

uporządkowane

2. atrybuty nie są uporządkowane: krotki relacji są zbiorami par;

tabele posiadają uporządkowanie kolumn

3. nie ma podwójnych krotek: relacja jest zbiorem, więc nie może

zawierać powtórzeń - tabela może - krotki są rozróżnialne i
można wyznaczyć klucz główny

4. wszystkie wartości atrybutów są atomowe: jest to

konsekwencją tego, że dziedziny posiadają tylko wartości
atomowe - o relacji, która nie zawiera grup wielokrotnych
(wartości nieatomowych) mówimy, że jest w I postaci
normalnej

Własności relacji

40

danej relacji R - każdy podzbiór SK atrybutów
relacji R, taki że
dla każdych 2 krotek relacji prawdą jest, że
k

1

[ SK ]

≠≠≠≠

k

2

[ SK ]

tj.taki komplet atrybutów, który implikuje
unikalny komplet wartości

Każda relacja ma co najmniej jeden superklucz –
schemat relacji.

Nadklucz/ Superklucz

superkey

41

K schematu relacji R - “minimalny” nadklucz,
tzn. taki, że nie istnieje K’

⊂

K będący nadkluczem

schematu relacji R (nie ma nadklucza w nadkluczu)

Jeżeli atrybut nie należy on do żadnego z kluczy schematu
relacji R to jest to atrybut wtórny

Jeżeli relacja zawiera więcej niż jeden klucz, to są one

nazywane kluczami potencjalnymi (kandydującymi)

Klucz

Key

42

relacji R jest podzbiorem K zbioru atrybutów relacji R,
posiadającym własności:

jednoznaczności: żadne dwie różne krotki relacji R nie mają tej
samej wartości dla K, są unikalne
nieredukowalności: żaden właściwy podzbiór K nie posiada
własności jednoznaczności, nie ma ‘klucza w kluczu’

Każde pole jednoznacznie identyfikuje pierwiastek, więc każde pole jest kluczem
kandydującym.

jakie klucze kandydujące posiada tabela przechowująca układ okresowy pierwiastków

Pierwiastki

(nazwa, symbol, liczba atomowa)

Klucz kandydujący

Candidate Key (CK)

43

Klucz główny

danej relacji - wybrany jej klucz kandydujący,

pozostałe klucze kandydujące to klucze alternatywne.

W tabeli

Pierwiastki

układ okresowy pierwiastków (pola: nazwa, symbol,

liczba atomowa) jako klucz główny możemy wybrać dowolne pole tabeli np.
liczbę atomową, pozostałe pola będą wówczas kluczami alternatywnymi.

Klucz główny

Primary Key (PK) i klucze alternatywne

Jeżeli dana tabela posiada wiele kluczy kandydujących, to wybór
klucza głównego jest dowolny. Wybór klucza głównego powinien
mieć na względzie prostotę rozwiązania, jednak kwestia ta leży
poza zakresem modelu relacyjnego.

44

relacji podstawowej R

2

jest to podzbiór FK zbioru atrybutów relacji

R

2

, taki że:

istnieje relacja podstawowa R

1

(R

1

i R

2

nie muszą być różne) z

kluczem kandydującym CK oraz
w każdej chwili każda wartość FK w aktualnej wartości relacji R

2

jest taka sama, jak wartość CK w pewnej krotce aktualnej
wartości relacji R

1

.

Klucz obcy

Foreign Key (FK)

45

integralność

referencyjna

– ad.

FK

-dotyczy kluczy obcych

integralność

encji

– ad.

PK

-dotyczy kluczy głównych

Integralność

46

Integralność encji

zagwarantowanie, by w relacji podstawowej
ż

aden składnik

klucza głównego

nie miał

wartości NULL

Gwarantujemy na poziomie na poziomie definicji
modelu fizycznego.

Ale co z założeniami modelu relacyjnego i definicją klucza
głównego ?

47

Integralność referencyjna

zagwarantowanie, by w bazie danych
nie występowały żadne
niedopasowane (unmached)
wartości

klucza obcego

48

Reguły integralności referencyjnej

CO się dzieje z kluczem obcym relacji R2 podczas

Usuwania Aktualizacji

klucza kandydującego relacji R1, adresata referencji ?

RESTRICTED

CASCADES

Operacja ograniczona (restricted) do przypadku,
w którym nie ma takich powiązań,

w przeciwnym razie operacja

odrzucana

Operacja „kaskadowo”

usuwa

aktualizuje klucz obcy

powiązane krotki w powiązanych krotkach

relacji R2

Możliwości:

49

*** SQL/92 ***

Międzynarodowy Standardowy Język

Baz Danych SQL (1992)

Spotykane w praktyce oprogramowanie nie implementuje

w pełni tego standardu; jest podzbiorem
(podstawowa dokumentacja ma ponad 600 stron)

Ale istniejące implementacje rozszerzają niektóre aspekty

standardu; jest nadzbiorem

Taką sytuację określa się jako nadzbiór podzbioru

50

Własności języka SQL

Język do formułowania operacji, które definiują dane i
operują na nich w sposób relacyjny.
Oparty na wyrażeniach z j.angielskiego
Język poleceń
Nieproceduralny
Przetwarzający cały zbiór danych, a nie pojedyncze krotki
Stosuje się terminy: tabela, wiersz, kolumna/ pole w
miejsce relacyjnych, odpowiednio: relacja, krotka, atrybut
Jest rendundacyjnym językiem tzn. umożliwia
formułowanie tego samego zapytania na wiele sposobów

51

SQL – podział języka, warstwy

j

ę

zyk definiowania struktur danych

- D

D

L (Data

Definition

Language) - jest wykorzystywany do

operacji na tabelach, takich jak: tworzenie, modyfikacja oraz
usuwanie,

j

ę

zyk do wybierania i manipulowania danymi

- D

M

L (Data

Manipulation

Language) - słu

ż

y do manipulowania

danymi umieszczonymi w tabelach, pozwala na prezentacj

ę

danych,

ich wstawienie danych, modyfikowanie oraz usuwanie,

j

ę

zyk do zapewnienia bezpiecze

ń

stwa dost

ę

pu do danych

- D

C

L (Data

Control

Language) - jest u

ż

ywany głównie przez

administratorów systemu baz danych do nadawania odpowiednich
uprawnie

ń

do korzystania z bazy danych.

52

SQL – podział języka na grupy poleceń

DDL

- Data Definition Language

Create
Alter
Drop

DML

- Data Manipulation Language

SELECT
Insert
Update
Delete

DCL

- Data Control Language

Grant Deny

Revoke

Commit Rollback

dane

struktury

ochrona danych

algebra
relacji

53

Select - instrukcja wyboru –

semantyka

SELECT

lista elementów do wyświetlenia, każdy element

w postaci osobnej kolumny

FROM

związki między źródłem danych, najprostrza postać

– jedna tabela

[

Where

warunek logiczny, restrykcja na wiersze zwracane

]

[

Group by

atrybuty grupowane wg. tych samych wartości

]

[

Having

warunek logiczny na Group by tj. na grupę

]

[

Order by

lista atrybutów wymuszających uporządkowanie

] ;

1

2

3
4
5
6

54

Subquery - podzapytania

1) SELECT (

Subquery zwraca

warto

ść

)

2) FROM

(

Subquery

zestaw wierszy

)

3) WHERE (

Subquery zwraca

warto

ść

lub

zestaw wierszy

,

zale

ż

y od u

ż

ytego operatora

)

Zagnieżdżone w innym zewnętrznym (rodzic) polecenie
wewnętrzne (dziecko), które wykonuje się pierwsze i zwraca
wartość lub zestaw wierszy koniecznych do realizacji polecenia
zewnętrznego
Może być różnie osadzone

55

SELECT

[

ALL

| DISTINCT

]

przecinkowa_lista_elementów_do_wyboru

lista elementów do wyboru po przecinku nie może być pusta,
musi być przynajmniej jeden element (nie ma wtedy żadnego
przecinka)
jeżeli nie podano ALL, ani DISTINCT,

domyślnie jest ALL

;

DISTINCT oznacza, że w wyniku

nie znajdą

się powtarzające

się

wiersze

nie mylić * (gwiazdki) z ALL choć * odnosi się do
wszystkich atrybutów (zastępuje listę wszystkich atrybutów),
ALL do wierszy, dyrektywa

wymaga listy atrybutów

SELECT * <> SELECT ALL

1.Select – klauzula Select

- lista elementów do

wyboru

56

element

[ [ AS ]

nowa_nazwa_kolumny

]

elementowi można nadać nową nazwę - alias, słowo

AS

jest

nieobowiązkowe
element może być:

1.1. Select – element do wyboru

1.

Nazwą atrybutu

2.

Symbolem zastępczym
wszystkich kolumn

3.

literałem

4.

Funkcją agregującą

5.

Funkcją wbudowaną

(zależy od

środowiska np. Upper = Ucase)

6.

Wyrażeniem wyliczanym

7.

Wartością (

tylko 1, nie może być

zestaw wierszy)

zwracaną przez

podzapytanie (subqery)

8.

Zmienną, stałą środowiskową

np.

1.

CITY

2.

*

3.

‘ numerem części jest ‘

4.

COUNT

(*)

5. UPPER

(Name)

6.

Name

+

‘ ‘

+

FirstName

7.

(

SELECT MAX

(QTY)

FROM

SP )

8.

@@version

57

nazwa_funkcji_agregującej

(nazwa_ kolumny)

Funkcja działająca na całym zestawie wierszy
Oprócz COUNT(*) wymaga podania jako argumentu nazwy
kolumny:

1.1.4. Select – Funkcje agregujące

1.

Zliczanie

ilości wierszy

2.

Zliczanie

ilości wystąpień

wartości (bez NULL)

3. Sumowanie

wartości

w kolumnie

4.

Zwracanie

max

wartości

w kolumnie

5.

Zwracanie

min

wartości

w kolumnie

6.

Zwracanie

średniej

wartości w kolumnie

1. COUNT(*)
2. COUNT()

3. SUM( )

4. MAX( )

5. MIN( )

6. AVG( )

Kolumny
musz

ą

by

ć

typu
liczbowego

58

FROM

wyrażenie_relacyjne

Wyrażenie_relacyjne

jest

1.

Nazwą tabeli

2.

Przecinkową listą tabel tj.

iloczynem kartezjańskim

3.

Złączeniem tabel

4.

Zestawem wierszy zwracanym
przez podzapytanie (subqery)

Kombinacją powyższych

2. Select – klazula From

np.

1. P
2. P

,

SP

,

S

3. P

JOIN

S

ON

P.CITY = S.CITY

4.

( SELECT P#, QTY

FROM SP )

SP

,

P

JOIN

S ON P.CITY =

S.CITY

nie mo

ż

e by

ć

puste!!!

59

2.3.Iloczyn lub Złączenie -

łączenie kilku tabel

FROM

tabel_1

,

tabela_2

rezultatem jest iloczyn kartezjański
Jeśli pojawi się fraza WHERE, która wyeliminuje niepożądane z punktu
widzenia złączenia wiersze to mamy do czynienia ze złączeniem naturalnym

WHERE

tabel_1. Kolumna_1

=

tabela_2.Kolumna_1

FROM

tabel_1

JOIN

tabela_2

ON

tabel_1. Kolumna_1 = tabela_2.Kolumna_1

(notacja zMSSQL)

Domyślnie (brak słowa) jest to złączenie

INNER

- wewnętrzne

Może być jeszcze zewnętrzne

OUTER

typu

RIGHT

,

LEFT

lub

FULL

tzn.

do wyniku dołączane są krotki, których zabrakło a pochodzą one z tabeli z
prawej, lewej lub obu stron
Może być tzw. Złączenie krzyżowe

CROSS JOIN

(bez frazy ON), a to jest to

samo co

,

(przecinek)

FROM

tabel_1

LEFT OUTER

JOIN

tabela_2

ON

tabel_1. Kolumna_1 = tabela_2.Kolumna_1

Wszystkie wiersze z tabeli lewej wejdą do wyniku , z prawej te co spełniają warunek

60

WHERE

wyrażenie_warunkowe

rezultatem klauzuli

WHERE

jest tabela otrzymana z wyrażenia

relacyjnego po frazie FROM z wszystkimi wierszami, dla
których wyrażenie warunkowe przyjmuje wartości

prawda

do budowania złożonych wyrażeń (wiele wyrażeń pojedynczych
połączonych w jedno) stosujemy te same reguły logiczne co w
programowaniu tj. korzystamy z operatorów logicznych

OR

i

AND

.

do budowy pojedynczych wyrażeń stosujemy operatory
porównań jak =, >, >= , <= oraz inne jak LIKE, IN, BETWEEN

3. Select – klazula Where

61

1.

Logiczne:

AND, OR, NOT

2.

Porównania:

=, >, <, >=, <=

3.

Do ła

ń

cucha znaków:

LIKE

, NOT LIKE

4.

Zakresu warto

ś

ci:

BETWEEN

, NOT BETWEEN

5.

List warto

ś

ci:

IN

, NOT IN

6.

Nieznanych warto

ś

ci:

IS NULL

, IS NOT NULL

7.

Logiczne dla subquery :

EXISTS,

NOT EXISTS

3. Where - Operatory

Nie
zawsze są
zaimplem
entowane

62



% dowolny ciąg znaków lub żaden znak



_ dowolny pojedynczy znak



[ ] dowolny znak z podanego zbioru znaków



[^]

ż

aden znak z podanego zbioru znaków

nazwisko

LIKE

‘ Kowal

%

’

nazwisko

LIKE

‘

%

sk

%

’

nazwisko

LIKE

‘

[ A B ]

% ’

nazwisko

LIKE

‘ [ A

-

D ] % ek ’

nazwisko

LIKE

‘

_

la’

nazwisko

LIKE

‘ K

[ ^ o]

%’

3.3. Operator LIKE i znaki maski

- Wildcard Characters
(w MSSQL)

63

GROUP BY

przecinkowa_lista_kolumn

przecinkowa_lista_kolumn

nie może być pusta

rezultatem klauzuli

GROUP BY

jest tabela otrzymana z wyrażenia

relacyjnego po frazie FROM przez pogrupowanie wszystkich
wierszy względem kolumn wskazanych przez

przecinkową_listę_kolumn

elementy do wyboru po słowie

SELECT

, w której występuje

klauzula

GROUP BY

nie mogą posiadać referencji do kolumn nie

wymienionych w

GROUP BY

, za wyjątkiem argumentów funkcji

agregujących
tj. co w Group by musi być wymienione na liście Select.

4.Select – klazula Group By

64

HAVING

wyrażenie_warunkowe

rezultatem klauzuli

HAVING

jest tabela pogrupowana otrzymana

z po Grupu By przez eliminację wszystkich tych grup, dla
których

wyrażenie_warunkowe

nie przyjmuje wartości

prawda

jeżeli

GROUP BY

nie występuje, to

HAVING

stosuje się do tabeli

wynikowej (

FROM

i/lub

WHERE

) traktowanej jako jedna grupa

(w wielu środowiskach potraktowane zostanie to jako błąd)

wyrażenie_warunkowe

nie może posiadać referencji do kolumn

nie wymienionych w klauzuli

GROUP BY

np.

5.Select – klazula Having

(ma sens z Group By)

SELECT

CITY ,

COUNT

(

P#

)

FROM

P

GROUP BY

CITY

HAVING COUNT

(

P#

) > 1 ;

Lista różnych miast i liczby różnych części znajdująca się w tych miastach o ile
jest ich więcej niż 1 pozycja

65

ORDER BY

przecinkowa_lista_kolumn

przecinkowa_lista_kolumn

nie może być pusta

wymuszenie uporządkowania zwracanych wierszy określonego
przez

przecinkową_listę_kolumn

Kolejność uporządkowania można wymusić frazą

DESC

(malejąco) lub

ASC

(rosnąco) po każdym atrybucie,

domyślnie (nic nie podano) jest ASC (ale..)

6Select – klazula Order By

SELECT

CITY , SNAME ,S#

FROM

S

ORDER BY

CITY, S# DESC

66

Select … From …

UNION

Select … From …

rezultatem

suma wierszy

wyników instrukcji Select

nagłówki łączonych wyników takie same tj.

liczba kolumn

i odpowiadających im dziedzin

s

ą

identyczne

Suma Select - operator UNION

SELECT

CITY, SNAME , ‘Dostawcy’ AS ‘ ‘

FROM

S

UNION

SELECT CITY, PNAME , ‘Cz

ęś

ci’

FROM P
ORDER BY

CITY

67

INSERT – Wstawianie

/1 z 3 postaci/

INSERT [ INTO ]

nazwa_tabeli

[ [ (lista_kolumn) ] VALUES lista_wartości ];

INTO

jest opcjonalne (może, ale nie musi być).

1. lista_wartości

: wartości po przecinku odpowiadające

poszczególnym kolumnom z listy_kolumn lub

DEFAULT

lub

NULL

2. SELECT_instrukcja

- wszystkie wiersze relacji zwracanej

przez to polecenie

(może być bardzo skomplikowane)

są przepisane do

nazwa_tabeli

, ale

typy relacji zwracanej

i

nazwa_tabeli

są te same,czyli nagłówek relacji instrukcji

Select jest identyczny z nagłówkiem tabeli instrukcji Insert.

3.

DEFAULT VALUES

–

1 wiersz z wszystkimi wartościami domyślnymi;

uwaga na unikalność => patrz klucze główne !!

INSERT [ INTO ]

nazwa_tabeli

[ SELECT_instrukcja ];

INSERT [ INTO ]

nazwa_tabeli

DEFAULT VALUES;

68

INSERT - przykład

INSERT INTO

P

VALUES

('P8', 'rura‘ , ,

DEFAULT

, ’Elbl

ą

g‘);

Insert

Schemat P=( [P#], PNAME, COLOR, WEIGHT, CITY)

INSERT INTO

P

( [P#], PNAME, CITY )

SELECT

'P8', 'rura', 'Elbl

ą

g';

‘oszukane’ Value

INSERT INTO

P

( [P#], PNAME, CITY )

SELECT 'P8', 'rura', 'Elbl

ą

g‘

FROM P;

Próba dodania identycznych krotek
w ilości równej liczbie krotek w P

INSERT INTO

P

( [P#], CITY,PNAME )

SELECT [S#], CITY,’dostawca’
FROM

S

WHERE CITY = ‘Warsaw’;

!! Zgodność schematów relacji!!

INSERT INTO

P

( [P#], PNAME, CITY )

VALUES ( 'P8', 'rura', 'Elbl

ą

g‘);

wymuszony, ograniczony schemat

--wstawianie pojedynczego wiersza z zadanymi danymi

69

DELETE FROM

nazwa_tabeli

[WHERE warunek_logiczny ]

[FROM ….];

WHERE warunek_logiczny – jeśli podano warunek logiczny, to wszystkie
krotki spełniające ten warunek są usuwane.
są RDBMS dopuszczające opuszczenie frazy FROM po słowie DELETE

FROM ….-

druga? fraza From służy do budowy warunków złożonych np.

JOIN

DELETE – Usuwanie !!!Be careful!!!

TRUNCATE TABLE

nazwa_tabeli

szybciej; nie zapisuje

w pliku logu transakcji,

zeruje liczniki typów autonumerowanie

DELETE

FROM

SP

FROM SP INNER JOIN S …

70

Usuwanie kilku tych dostaw, które zostały dostarczone przez dostawców z Elbląga

DELETE FROM

SP

WHERE

‘Elbląg’ =

(

SELECT

S. CITY

FROM

S

WHERE

S.S# = SP.S#

) ;

Delete

DELETE – przykład

DELETE FROM

SP

WHERE

SP.S# IN

(

SELECT

S.S#

FROM

S

WHERE

S. CITY =‘Elbląg’

) ;

DELETE FROM

SP

FROM

SP INNER JOIN S

ON

SP.S# S.S#

WHERE

S. CITY =‘Elbląg’

;

Inne rozwiązanie

71

*** Normalizacja ***

Interesuje nas:

logiczny a nie fizyczny projekt bazy

(projekt fizyczny jest krokiem niezależnym od projektu logicznego, nawet gdy
fizyczny DBMS jest nierelacyjny)

logiczny projekt bazy danych, wyrażający model
konceptualny/pojęciowy danych niezależny od DBMS,
sprzętu, systemu operacyjnego, języka, użytkowników, itd. -
patrz pierwsza część wykładu

Jak pewien zestaw danych, przeznaczony do umieszczenia w
bazie, ująć w poprawną strukturę logiczną,
czyli w logiczny projekt bazy danych?

72

Modelowanie semantyczne

Modelowanie z perspektywy

znaczenia

danych

= Modelowanie danych
= Modelowanie encji
=

Modelowanie związków encji

Projektowanie oparte na modelu semantycznym zwane jest
„z góry na dół” (Top-Down), ponieważ zaczyna od
wysokiego poziomu abstrakcji „świata rzeczywistego”
(np.encje), a kończy na niskim poziomie.

73

Konsekwencje błędów

Niewłaściwe zaprojektowanie schematów relacji
może być przyczyną

 redundancji

(zbędna nadmiarowość przechowywanych danych)

 niespójności

(niezgodność z oczekiwaniami, wzajemna, niedopasowanie)

 anomalii

74

•

Anomalia

wstawiania

•

Anomalia

usuwania

•

Anomalia

modyfikacji

Podczas eksploatacji bazy danych mogą wystąpić
anomalie:

Anomalie

75

Normalizacja

NF (

PN

)

- doprowadzanie

(przekształcanie)

relacji do

odpowiedniej

(właściwej, pożądanej)

Postaci Normalnej

(PN,

ang.

Normal Form – NF)

W uproszczeniu

, proces, który schematy relacji posiadające pewne

niepożądane cechy dekomponuje na mniejsze schematy o
pożądanych własnościach.

Dekompozycja sprowadza się do podziału (projekcji,
rzutowaniu) atrybutów relacji R między schematy nowych
relacji,
a także do podziału krotek relacji wejściowej R między nowe
relacje.

Jest odwracalna tj. zawsze możemy przekształcić otrzymany
zestaw relacji w relację wyjściową (rozkład bezstratny).

76

Pierwsza postać normalna relacji – 1NF

Relacja jest w

pierwszej postaci normalnej

, jeśli

każda wartość atrybutu w każdej krotce tej relacji jest
wartością skalarną, czyli nierozkładalną (atomową).

Z definicje pierwszej postaci normalnej relacji wynika, że każdemu
elementowi relacji znajdującemu się na przecięciu dowolnej krotki
i dowolnego atrybutu odpowiada pojedyncza wartość, a nie zbiór
wartości (czy to nie zawiera się w definicji relacji).

Proces normalizacji do 1NF polega na doprowadzeniu zbioru

danych do

postaci relacji

77

Atrybut Y relacji R jest

funkcyjnie zależny

od atrybutu

X, jeśli zawsze każdej wartości x atrybutu X odpowiada
nie więcej niż jedna wartość y atrybutu Y

Stwierdzenie, że Y jest funkcyjnie zależne od X, jest równoważne
stwierdzeniu, że X identyfikuje (wyznacza,determinuje) Y,

X

→

→

→

→

Y

X – lewa strona FD, element determinujący
Y – prawa strona FD, element determinowany

Zależność funkcyjna FD

–Functional Dependence

tzn. ilekroć 2 krotki z R mają taką samą wartość atrybutu X, mają również
taką samą wartość atrybutu Y
tzn. 2 dowolne krotki pojawiające się w danej chwili, z tą samą wartością
atrybutu X, muszą mieć tę samą wartość atrybutu Y

np. nazwa_instytucji -> adres_instytucji, model_samochodu->marka_samochodu,

pesel ->data_ur

78

Jeżeli X jest kluczem głównym (lub kandydującym)

relacji R, to wszystkie atrybuty Y relacji R muszą zależeć
funkcyjnie od X.

Jeżeli w relacji R występuje FD

X

→

→

→

→

Y oraz X nie jest

kluczem kandydującym, to mówimy, że w R występuje
redundancja.

Zależności funkcyjne - wnioski

np. Magazyn

→

Adres_magazynu ,

gdzie kluczem jest {Nr_zamówienia, Id_części}

Zależności funkcyjne reprezentują więzy integralności, a

zatem system zarządzania bazą danych (SZBD) musi je
sprawdzać w chwili przeprowadzania aktualizacji.

79

Zbiór atrybutów Y jest w

pełni funkcyjnie

zależny

od zbioru atrybutów X w schemacie R,
jeżeli X

→

Y i

nie istnieje

podzbiór X’

⊂

X taki,

ż

e X’

→

Y.

Zbiór atrybutów Y jest

częściowo funkcyjnie

zależny od zbioru atrybutów X w schemacie R,
jeżeli X

→

Y i

istnieje

podzbiór X’

⊂

X taki,

ż

e X’

→

Y.

Pełna i częściowa zależność funkcyjna

np. {nr_indeksu,nazwisko,imię} -> adres_studenta, ale

nr_indeksu -> adres_studenta

np. {nazwisko,imię,data_ur,miejsce_ur,im_ojca,im_matki} -> adres_studenta

80

Relacja R o danym schemacie jest w drugiej postaci
normalnej (2NF), jeżeli jest w pierwszej postaci
normalnej i

żaden atrybut wtórny

(ten, co nie należy do żadnego klucza)

tej

relacji

nie jest częściowo funkcyjnie

zależny od

ż

adnego z

kluczy

relacji R.

lub inaczej

każdy

atrybut wtórny

tej relacji

jest w pełni

funkcyjnie zależny

od

klucza podstawowego

relacji

R

.

Druga postać normalna - 2NF

81

Nr_zamówienia

Id_dostawcy

Nazwa_dostawcy

Adres_dostawcy

Id_części

Nazwa_części

Magazyn

Ilośc

Adres_magazynu

Klucz: Nr_zamówienia, Id_części

Relacja ZAMÓWIENIA nie jest w 2NF

82

Nr_zamówienia

Id_dostawcy

Nazwa_dostawcy

Adres_dostawcy

Id_części

Nazwa_części

Magazyn

Ilośc

Adres_magazynu

Nr_zamówienia

Id_części

Relacje w 2NF - wszystkie zależności funkcyjne względem klucza są
pełnymi zależnościami funkcyjnymi

Zależności funkcyjne w relacjach

DOSTAWCA_NA_ ZAMÓWIENIU, CZESCI_W_MAGAZYNIE,
DOSTAWY_ CZĘŚCI

DOSTAWY_ CZĘŚCI

CZESCI_W_MAGAZYNIE

DOSTAWCA_NA_ ZAMÓWIENIU

83

Zbiór atrybutów

Y

jest

przechodnio funkcyjnie zależny

od zbioru atrybutów

X

w schemacie relacji R,

jeżeli X

→

Y i istnieje zbiór atrybutów Z, nie będący

podzbiorem żadnego klucza schematu relacji R taki,
ż

e zachodzi X

→

→

→

→

Z

i

Z

→

→

→

→

Y

czyli Y jest przechodnio zależny od X poprzez Z

Definicja przechodniej zależności
funkcyjnej

84

DOSTAWCA_NA_ ZAMÓWIENIU

CZĘŚCI_ W_MAGAZYNIE

Nr_zamówienia

Id_dostawcy

Nazwa_dostawcy

Adres_dostawcy

Id_części

Nazwa_części

Magazyn

Adres_magazynu

Zależności funkcyjne i Przechodnie zależności funkcyjne w
relacjach

DOSTAWCA_NA_ ZAMÓWIENIU,

CZESCI_W_MAGAZYNIE

85

Relacja R o danym schemacie jest w trzeciej
postaci normalnej (3NF), jeżeli jest w drugiej
postaci normalnej i

żaden atrybut wtórny

tej

relacji

nie jest przechodnio zależny

od

klucza

głównego

tej relacji.

Trzecia postać normalna –3NF

86

Nr_zamówienia

Id_dostawcy

Nazwa_dostawcy

Adres_dostawcy

Id_części

Nazwa_części

Magazyn

Adres_magazynu

DOSTAWCY

CZ

ĘŚĆ

_W_MAGAZYNIE

ZAMÓWIENIE_DO_DOSTAWCY

Id_dostawcy

CZ

ĘŚ

CI

Id_części

Magazyn

MAGAZYNY

Ze względu na fakt występowania FD atrybutu Nazwa_części
od atrybutu Id_części, oprócz przechodniej FD Id_części

→

→

→

→

Adres_magazynu

Dlaczego relację CZĘŚCI_W_MAGAZYNIE należało
podzielić na 3 relacje ?

87

Jednym ze sposobów redukcji wielkości zbioru
zależności funkcyjnych jest eliminacja tak zwanych
zależności trywialnych

.

Zależność jest trywialna, jeżeli

NIE może być NIEspełniona

.

Zależność jest trywialna wtedy i tylko wtedy, gdy
prawa strona zależności jest podzbiorem lewej
(X, Y

→

X)

Zależności trywialne

88

Mocniejsza wersja 3NF.

Relacja znajduje się w postaci BCNF wtedy i tylko
wtedy, gdy

jedynymi elementami

determinującymi są klucze kandydujące

, przy

czym zakładamy, że wszystkie zależności
funkcyjne są nietrywialne.

Postać normalna Boyce’a/Codda BCNF

Każdy atrybut musi reprezentować jakiś fakt o kluczu, całym
kluczu i tylko kluczu.

89

1. Wykonaj takie projekcje wyjściowej relacji w 1NF,

aby wyeliminować wszystkie niepełne zależności
funkcyjne.

Ten krok da zestaw relacji będących w drugiej postaci

normalnej - 2NF

2. Wykonaj takie projekcje tych relacji w 2NF, które

usuną wszelkie przechodnie zależności funkcyjne.

Krok ten da zestaw relacji w 3NF

3. Wykonaj takie projekcje relacji wyjściowej, które

wyeliminują wszystkie zależności funkcyjne, które
nie wychodzą z klucza potencjalnego.

Krok ten da zestaw relacji postaci BCNF.

Podsumowanie - zasady normalizowania

Kolejny krok procesu redukcji (dekompozycji) polega na wykonaniu operacji
na relacji wynikającej z poprzedniego kroku.

90

*** Algebra relacji ***

Zbiór

operacji

na relacjach

Operacje algebry relacyjnej zostały zaimplementowane w RDBMS
(rel.syst.zarz.b.d.), najbardziej znana jest ich postać w języku
SQL.

Algebra jest wzorcem, za pomocą którego możemy mierzyć
możliwości danego języka relacyjnego.

Język jest relacyjnie zupełny, jeżeli wyrażenia języka pozwalają
zdefiniować każdą relację, którą możemy wyrazić wyrażeniami
algebry.

91

Atrybuty w operacjach algebry relacji

Nazwy atrybutów są dziedziczone

tj.znając atrybuty wejścia przewidujemy atrybuty wyjścia operacji
tj. każda operacja relacyjna daje w wyniku relację z odpowiednim

nagłówkiem

Dwie relacje są

tej samej postaci

lub mają

zgodne typy

,

jeśli mają identyczne nagłówki tzn.

1. Każda z nich ma ten sam zbiór nazw atrybutów (= ten

sam stopień);

2. Odpowiadające sobie atrybuty są zdefiniowane na tej

samej dziedzinie

92

Operacje w algebrze relacji

Operacje na

zbiorach

tradycyjne

Operacje

relacyjne

specjalne

1. Suma - UNION
2. Różnica - DIFFERENCE
3. Przecięcie - INTERSECTION
4. Iloczyn -PRODUCT

1. Projekcja -PROJECT
2. Restrykcja - RESTRICT
3. Złączenie - JOIN
4. Iloraz - DIVIDE

8 operatorów zaproponowanych przez Codda.
Nie wszystkie są elementarne, niektóre mogą być zdefiniowanie za
pomocą innych

93

dwóch relacji zgodnych typów A i B,

A

UNION

B

jest relacją

mającą ten sam nagłówek co A (bądź B), treść zaś złożoną ze
zbioru wszystkich krotek należących do A lub B.

Uwaga:

Ponieważ

A

UNION

B

jest relacją, więc

nie zawiera krotek powtarzających się
w A i B.

A

B

S#
S1
S4

SNAME

Smith

Clark

STATUS

20
20

CITY

Londyn
Londyn

A:

S#
S1
S2

SNAME

Smith

Jones

STATUS

20
10

CITY

Londyn

Paryż

B:

S#
S1
S2
S4

SNAME

Smith

Jones
Clark

STATUS

20
10
20

CITY

Londyn

Paryż

Londyn

A

UNION

B

:

UNION - Suma

94

dwóch relacji zgodnych typów A i B,

A

INTERSECT

B

, jest

relacją mającą ten sam nagłówek co A (bądź B), treść zaś
złożoną ze zbioru wszystkich krotek należących zarówno do
relacji A jak i B.

A

B

S#
S1
S4

SNAME

Smith

Clark

STATUS

20
20

CITY

Londyn
Londyn

A:

S#
S1
S2

SNAME

Smith

Jones

STATUS

20
10

CITY

Londyn

Paryż

B:

S#
S1

SNAME

Smith

STATUS

20

CITY

Londyn

A

INTERSECT

B

:

INTERSECTION - Przecięcie

95

dwóch relacji zgodnych typów A i B, w tej kolejności,

A

MINUS

B

, jest relacją mającą ten sam nagłówek co A (bądź B),

treść zaś złożoną ze zbioru wszystkich krotek należących do relacji
A i nie należących do relacji B.

A

B

S#
S1
S4

SNAME

Smith

Clark

STATUS

20
20

CITY

Londyn
Londyn

A:

S#
S1
S2

SNAME

Smith

Jones

STATUS

20
10

CITY

Londyn

Paryż

B:

S#
S4

SNAME

Clark

STATUS

20

CITY

Londyn

A

MINUS

B

:

S#
S2

SNAME

Jones

STATUS

10

CITY

Paryż

B

MINUS

A

:

DIFFERENCE - Różnica

96

dwóch relacji A i B,

A

TIMES

B

, gdzie A i B nie mają wspólnych

nazw atrybutów, jest relacją, której nagłówek jest konkatenacją
(złączeniem) nagłówków relacji A i B, treścią zaś są wszystkie
krotki będące konkatenacją krotek relacji A i B.

Uwaga:

Liczebność relacji

A

TIMES

B

jest równa iloczynowi liczebności

relacji A i B.

S#
S1
S4

SNAME

Smith

Clark

STATUS

20
20

A:

P#

P1
P2

PNAME

nakrętka

ś

ruba

COLOR

czerwony

zielony

B:

P#

P1
P2
P1
P2

PNAME

nakrętka

ś

ruba

nakrętka

ś

ruba

COLOR

czerwony

zielony

czerwony

zielony

A

TIMES

B

:

STATUS

20
20
20
20

SNAME

Smith
Smith

Clark
Clark

S#
S1
S1
S4
S4

PPRODUCT – Iloczyn (kartezjański)

97

relacji A,

A

WHERE

X

θθθθ

Y

, jest relacją z tym samym nagłówkiem

co relacja A i z treścią złożoną ze wszystkich krotek takich że
warunek X

θθθθ

Y jest prawdziwy dla każdej z nich, przy czym

atrybuty X,Y muszą wywodzić się z tej samej dziedziny, a operator
musi być sensownie określony dla tej dziedziny

Uwaga:

zamiast atrybutów można podać literał

A

WHERE

X

θθθθ

literał

S#
S1
S4

SNAME

Smith

Clark

STATUS

20
20

CITY

Londyn
Londyn

S

WHERE

CITY=‘Londyn’

:

P#

P1
P5

PNAME

nakrętka
krzywka

COLOR

czerwony

niebieski

WEIGHT

12
12

P

WHERE

WEIGHT < 14

:

CITY

Londyn

Paryż

S#
S1

P#

P1

QTY

300

SP

WHERE

S#=‘S1’ AND

P#=‘P1’

:

RESTRICT - Restrykcja /selekcja

A

pozioma maszyna do cięcia, wybór wierszy

θθθθ

- theta

98

relacji A na atrybuty X

1

,...,X

n

,

A

[

X

1

,...X

n

]

, jest relacją z

nagłówkiem {X

1

, .., X

n

} i z treścią, będącą zbiorem wszystkich

krotek {X

1

:x

1

, ..., X

n

:x

n

} takich, że w relacji A występuje krotka,

której atrybut X

i

posiada wartość x

j

, dla każdego i

∈

{1, ..., n}.

CITY

Londyn

Paryż

Ateny

S [ CITY ]

:

COLOR

czerwony

zielony

niebieski
niebieski

P [ COLOR, CITY ]

:

CITY

Londyn

Paryż

Rzym

Paryż

S#
S2
S3

(S

WHERE

CITY = ‘Paryż’) [ S# ]

:

PROJECT – Projekcja / rzut

A

pionowa maszyna do cięcia, wybór kolumn

Uwaga:

eliminacja atrybutów nie wymienionych na

liście i usunięcie powtarzających się krotek

99

Niech A i B będą relacjami o nagłówkach
{X

1

, ..., X

m

,

Y

1

, ..., Y

n

} oraz {

Y

1

, ..., Y

n

, Z

1

, ..., Z

p

},

tj. jedynymi atrybutami wspólnymi relacji A i B są Y

1

, ..., Y

n

, oraz

niech X = {X

1

, ..., X

m

}, Y = {Y

1

, ..., Y

n

} oraz Z = {Z

1

, ..., Z

p

}.

Złączenie naturalne relacji A i B,

A

JOIN

B

, jest relacją z

nagłówkiem {X, Y, Z} i z treścią, będącą zbiorem wszystkich
krotek {X:x, Y:y, Z:z} takich, że krotka ta w relacji A posiada
wartości x i y dla atrybutów X i Y oraz
y i z dla atrybutów Y i Z relacji B.

JOIN - Złączenie naturalne

•

Nie 2 atrybutów o powtarzających się wartościach w relacji wynikowej

•

Złączenia naturalne niekoniecznie muszą być między kluczem obcym a
powiązanym z nim kluczem kandydującym, chociaż stanowią najczęściej
spotykany przypadek.

100

relacji A na atrybucie X z relacją B na atrybucie Y jest relacją z
takim samym nagłówkiem jak iloczyn kartezjański A i B i z treścią
składającą się ze zbioru krotek tego iloczynu oraz spełniających
warunek X

θθθθ

Y.

θθθθ

-złączenie nie jest operacją elementarną i wyraża się jako:

( A

TIMES

B )

WHERE

X

θθθθ

Y

θθθθ

-złączenie

θθθθ

- theta

Jeżeli operatorem

θθθθ

jest `=` wówczas

θθθθ

-złączenie nazywamy

równozłączeniem (

equiJoin

):

( A

TIMES

B )

WHERE

X

=

Y

.

Równozłączenia zawierają dwa atrybuty o identycznych wartościach.

Jeżeli usuniemy jeden z tych atrybutów, to w wyniku otrzymamy

złączenie naturalne

, stąd nie ono jest operacją elementarną i może być

przedstawione jako

( A

TIMES

B )

WHERE

X = Y

przy założeniu, że odrzucamy powtarzający się atrybut.

101

Złączenie naturalne można przedstawić jako

rzut

restrykcji

iloczynu

,

wraz z odp.

zmianami nazw



Złączenie naturalne jako iloczyn

S

JOIN

P

( (

S

TIMES

(

P

RENAME

CITY

AS

PCITY

)

)

WHERE

CITY = PCITY

)

[

S#, SNAME, STATUS, CITY,

P#, PNAME, COLOR, WEIGHT

]

102

iloraz

Niech A i B będą relacjami o nagłówkach
{X

1

, ..., X

m

, Y

1

, ..., Y

n

} oraz {Y

1

, ..., Y

n

},

tj. jedynymi atrybutami wspólnymi relacji A i B są Y

1

, ..., Y

n

, oraz

niech X = {X

1

, ..., X

m

} oraz Y = {Y

1

, ..., Y

n

}.

Iloraz relacji A przez relację B,

A

DIVIDEDBY

B

, jest relacją z

nagłówkiem {X} i z treścią, będącą zbiorem wszystkich krotek
{X:x} takich, że krotka {X:x, Y:y} występuje w relacji A dla
wszystkich krotek {Y:y} występujących w relacji B.

tzn.

A

DIVIDEDBY

B

składa się z tych wartości X z A, których

odpowiadające wartości Y z A obejmują wszystkie
wartości Y z B.

DIVIDE - Iloraz

103

S#
S1
S1
S1
S1
S1
S1
S2
S2
S3
S4
S4
S4

D

:

P#

P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5

D1

:

P#

P1

D2

:

P#

P2
P4

D2

:

P#

P1
P2
P3
P4
P5
P6

D

DIVIDEDBY

D1

:

S#
S1
S2

D

DIVIDEDBY

D2

:

S#
S1
S4

D

DIVIDEDBY

D3

:

S#
S1

Iloraz - przykład

Czy w dostawach (dzielna) są zarejestrowani dostawcy (wynik) dostarczający wszystkie
części (dzielnik) ?

dzielna

dzielnik 1

dzielnik 2

dzielnik 3

104

Dodatkowe operacje w algebrze relacji

1. Zmiana nazwy - RENAME
2. Rozszerzenie - EXTENDED
3. Podsumowanie - SUMMARIZE

obliczeniowe:
poziome
pionowe

105

RENAME - Zmiana nazw

zmiana nazw atrybutów danej relacji

<nazwa relacji>

RENAME

<stara nazwa

AS

<nowa nazwa

atrybutu> atrybutu>

S RENAME city AS Miasto, cname AS Nazwa

106

relacji A o atrybut X o wartościach obliczonych na podstawie
wyrażenia E,

EXTEND

A

ADD

Exp

AS

X

, jest relacją o nagłówku i

treści relacji A poszerzonych o atrybut X o wartościach zadanych
wyrażeniem Exp.

Dodanie nowego atrybutu do relacji wynikowej, którego wartości są
wyznaczane on-line na podstawie wartości innych atrybutów z tej samej krotki.

Możemy dodać literał zamiast wyrażenia, wtedy ta sama wartość stała będzie
występować w każdej krotce.

(

EXTEND

P

ADD

( WEIGHT * 454 )

AS

GMTW )

EXTENDED - rozszerzenie

EXTEND

A

ADD

literał

AS

X

np.Niech kolumna WEIGHT tabeli P przechowuje wagę części w funtach, wówczas
następujące wyrażenie doda do tabeli P kolumnę GMTW zawierającą wagę w
gramach:

EXTEND

S

ADD

‘dostawca’

AS

kto

poziome,wierszowe obliczenia

107

relacji A względem atrybutów A

1

, ..., A

n

o wartościach obliczonych

na podstawie wyrażenia Exp,

SUMARIZE

A

BY

(A

1

, ..., A

n

)

ADD

Exp

AS

Z

,

jest relacją o nagłówku {

A

1

, ..., A

n

, Z

} i treści będącej rzutem relacji

A na atrybuty A

1

, ..., A

n

rozszerzoną o wartości nowego atrybutu Z

obliczonego na podstawie wyrażenia agregującego Exp

inaczej

Wartości kolumny Z są wyznaczane przez obliczenie wyrażenia
agregującego Exp na wszystkich krotkach relacji A, mających takie same
wartości atrybutów A

1

, ..., A

n

co krotka zawierająca obliczaną wartość

kolumny Z.

Krotki o tych samych wartościach wskazanych atrybutów łączymy w grupę
i przepisujemy jako 1 krotkę do relacji wynikowej oraz dodajemy nowy
atrybut, którego wartość będzie wyliczona na podstawie atrybutów grupy

SUMARIZE - sumowanie

pionowe,kolumnowe podsumowania grup krotek

108

Operacje relacyjnego przypisania

1. Wstawianie - INSERT
2. Modyfikacja-aktualizacja - UPDATE
3. Usuwanie - DELETE

109

INSERT

ź

ródło

INTO

cel;

gdzie

ź

ródło

i

cel

są wyrażeniami relacyjnymi o zgodnych typach.

Obliczane jest wyrażenie

ź

ródło

i wszystkie krotki wyniku są

włączane do wyrażenia

cel

.

Przykład:

INSERT

( S

WHERE

CITY = ‘Londyn’ )

INTO

TEMP;

Wszystkie krotki relacji S, których pole CITY = ‘Londyn’ zostaną
wstawione do relacji TEMP - zakładamy, że relacje S i TEMP są zgodne
pod względem typu.

INSERT - Wstawianie

110

UPDATE

cel przecinkowa-lista-przypisań;

gdzie każde wyrażenie

przypisanie

ma postać:

atrybut := literał

cel

jest wyrażeniem relacyjnym. Operacja aktualizacji oblicza

cel

a

następnie aktualizuje go wg operacji przypisania.

Przykład:

UPDATE

P

WHERE

COLOR = ‘czerwony’

CITY := ‘Paryż’;

Lokalizacja wszystkich czerwonych części zostaje zmieniona na Paryż w relacji P.

W praktyce relacja docelowa często będzie selekcją nazwanej relacji.

UPDATE - Modyfikacja/ aktualizacja

111

DELETE

cel;

cel

jest wyrażeniem relacyjnym. Wszystkie krotki relacji docelowej

są usuwane.

Przykład:

DELETE

S

WHERE

STATUS < 20;

Z relacji S usuwane są wszystkie krotki dostawców o statusie
mniejszym niż 20.

Podobnie jak w przypadku aktualizacji,
relacja docelowa często będzie

selekcją

pewnej nazwanej relacji.

DELETE - Usuwanie

112

Operacje na zbiorach

Suma
- UNION

A

B

A

B

A

B

Różnica

- DIFFERENCE

Przecięcie
- INTERSECTION

Iloczyn (kartezjański)

–PRODUCT

A

B

AB

113

Operacje relacyjne

Projekcja
-PROJECT

Restrykcja
- RESTRICT

Złączenie
- JOIN

Iloraz - DIVIDE

A

A

A

B

A

B

AdivB

=>