Krzysztof Regulski
AGH, WIMiIP, ZIP

Bazy Danych i SQL

- Projektowanie baz danych (1). E-R Model.

2

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Etapy projektowania baz danych:

1.

Specyfikacja wymaga

ń

u

ż

ytkownika

- okre

ś

lenie zjawisk,

dost

ę

pno

ś

ci i u

ż

yteczno

ś

ci danych, ich formatu i sposobów oblicze

ń

,

cele, zakres i kontekst systemu

2.

Projektowanie konceptualne

- projektowanie schematu E–R bazy.

U

ż

ycie modelu E–R wpływa równie

ż

na realizacj

ę

pozostałych faz.

3.

Specyfikacja wymaga

ń

funkcjonalnych

- dokładny opis wymaga

ń

klienta i wszystkich przyszłych u

ż

ytkowników systemu

4.

Projektowanie logiczne i fizyczne

5.

Implementacja

3

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Projektowanie bazy danych:

Projektowanie logicznej struktury bazy:

•

Etap I:

okre

ś

lenie encji i zdefiniowanie atrybutów opisuj

ą

cych encje

–

przyporz

ą

dkowanie encji do zjawisk

–

standaryzacja nazw i formatów

–

identyfikacja

ź

ródeł danych

•

Etap II:

okre

ś

lenie zwi

ą

zków mi

ę

dzy encjami

–

identyfikacja typu zwi

ą

zków (relacji) (1-1, 1-M, N-M)

•

Etap III:

normalizacja relacji

–

obni

ż

enie redundancji i wyeliminowanie anomalii (usuwania, wstawiania i

aktualizacji)

Projektowanie fizycznej struktury bazy:

•

nało

ż

enie struktury logicznej na fizyczne urz

ą

dzenia

4

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Projektowanie bazy danych:

(1)

(2)

(3)

(4)

(5)

FAKTY W

Ś

WIECIE RZECZYWISTYM

PODZBIÓR J

Ę

ZYKA NATURALNEGO DO

FORMUŁOWANIA WYPOWIEDZI O

FAKTACH W

Ś

WIECIE RZECZYWISTYM

ABSTRAKCYJNY MODEL

Ś

WIATA RZECZYWISTEGO

KONCEPTUALNA BAZA DANYCH

LOGICZNA BAZA DANYCH

PRZEDSTAWIENIE

ABSTRAKCYJNEGO MODELU

ZA POMOC

Ą

DIAGRAMU E-R

5

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Wybór schematu relacji:

Projekt bazy danych polega na znalezieniu wła

ś

ciwych schematów relacji

tworz

ą

cych baz

ę

danych

Niewła

ś

ciwy projekt

(niewła

ś

ciwe schematy) mog

ą

prowadzi

ć

do

•

Redundancji (powtarzania informacji).

•

Niemo

ż

liwo

ś

ci reprezentowania pewnych informacji.

•

Anomalii zwi

ą

zanych z operowaniem danymi (głównie modyfikacj

ą

danych)

Cele

:

•

Unikanie redundancji danych

•

Zapewnienie reprezentowania zwi

ą

zków mi

ę

dzy danymi

•

Zachowanie warunków integralno

ś

ci (umo

ż

liwienie kontroli warunków integralno

ś

ci

podczas modyfikacji danych).

6

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Modelowanie konceptualne:

Model poj

ę

ciowy (konceptualny, podstawowy):

Zapis wymaga

ń

w postaci sformalizowanej, abstrahuj

ą

cy od

problemów implementacyjnych („co” a nie „jak”)

Zawiera

•

modele graficzne

•

tekstowe (ale te

ż

sformalizowane) uzupełnienia modeli graficznych

Jest wa

ż

ny, poniewa

ż

:

•

reprezentuje istot

ę

wymaga

ń

•

nie zale

ż

y od zmiennych mo

ż

liwo

ś

ci implementacji

•

jest dobr

ą

podstaw

ą

do projektowania systemów maj

ą

cych funkcjonowa

ć

przez

wiele lat

7

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Wymagania:

Spełnienie wymaga

ń

u

ż

ytkownika jest naszym celem działania

Wymagania musz

ą

wi

ę

c by

ć

dokładnie znane

Poprawne i kompletne sformułowanie wymaga

ń

na ogół nie jest proste

Nale

ż

y odró

ż

nia

ć

funkcje

systemu od

mechanizmów

, czyli sposobów

implementacji funkcji

Modelowa

ć

dane niezale

ż

nie od przyszłej implementacji -

modelowanie E/R

Tworz

ą

c model poj

ę

ciowy nie mo

ż

na formułowa

ć

zastrze

ż

e

ń

co do

realizowalno

ś

ci wymaga

ń

Zaprojektowa

ć

dobry schemat -

normalizacja

8

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Cel modelowania E-R:

Model

E-R

słu

ż

y do nieformalnego przedstawienia projektu bazy

danych.

Projekt ma posta

ć

graficzn

ą

zwan

ą

diagramem

E-R

(entity-relationship

diagram), diagramem

jednostka-zwi

ą

zek

lub diagramem

zwi

ą

zków

encji

Istnieje procedura (pół)automatycznej transformacji diagramu

E-R

do

konkretnej implementacji, na przykład do relacyjnej bazy danych.

9

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Encja (zbiory encji):

Encja (entity)

- model rzeczy, osób, zjawisk, poj

ęć

itp., o których chcemy przechowywa

ć

informacje,

które maj

ą

to

ż

samo

ść

(s

ą

rozró

ż

nialne)

Np. konkretna osoba, firma, zdarzenie

Zbiór encji (entity set)

– zbiór (klasa) obiektów,

które s

ą

tego samego typu (maj

ą

wspólne

własno

ś

ci), inaczej: zbiór wyst

ą

pie

ń

(instancji) tej

klasy

Np. zbiór osób, zbiór firm, zbiór okre

ś

lonych zdarze

ń

Sło

ń

Adam

12347

Lis

Ewa

12346

Pies

Jan

12345

student

encja

zbiór encji

W diagramach E-R zbiory encji oznaczane s

ą

prostok

ą

tami

:

student

10

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Atrybuty:

Zbiór encji (i ka

ż

da z nich) jest opisywany przez

zestaw atrybutów

,

które odpowiadaj

ą

własno

ś

ciom posiadanym przez wszystkie obiekty

reprezentowane przez ten zbiór. Np.:

student = (nr_albumu, imie, nazwisko)

Dziedzina atrybutu

– zbór dopuszczalnych warto

ś

ci, jakie mo

ż

e

przyjmowa

ć

dany atrybut na danym zbiorze encji. Dziedzina

uzupełniona jest o warto

ść

NULL

oznaczaj

ą

c

ą

,

ż

e encja nie posiada

warto

ś

ci atrybutu lub jest on nieznany.

Atrybuty

:

•

proste vs. pochodne (mog

ą

by

ć

obliczone na podstawie innych informacji w bazie),

•

proste vs. zło

ż

one.

11

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Atrybuty proste vs. zło

ż

one (przykład):

W diagramach atrybuty oznaczane s

ą

elipsami

:

Atrybuty zło

ż

one

elipsami z podwójn

ą

lini

ą

:

Atrybuty pochodne

elipsami z przerywanej linii

:

nazwa

nazwa

nazwa

12

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Zbiory zwi

ą

zków:

Zwi

ą

zek

(relationship) reprezentuje relacj

ę

pomi

ę

dzy pewnymi encjami

(obiektami). Np.:

Jan Kowalski

encja:

pracownik

B3 Betatrex

encja:

oddzial

pracuje w

W diagramach E-R zbiory

zwi

ą

zków oznaczane s

ą

rombami

13

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Zbiór zwi

ą

zków (przykład):

student

oceny

ma

14

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Diagramy E-R – symbole podstawowe:

Prostok

ą

ty

– zbiory encji

Elipsy

– atrybuty

Elipsy

–

linia podwójna

– atrybuty zło

ż

one

Elipsy

–

linia przerywana

– atrybuty pochodne

Romby

– zbiory zwi

ą

zków

Linie

– ł

ą

cz

ą

zbiory encji ze zbiorami zwi

ą

zków oraz atrybuty ze

zbiorami encji i zwi

ą

zków

15

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Stopie

ń

zbioru zwi

ą

zków:

Stopie

ń

zbioru zwi

ą

zków

(degree of a relationship set) okre

ś

lony jest

przez liczb

ę

zbiorów encji, które pozostaj

ą

w danym zwi

ą

zku.

Zwi

ą

zek dwóch zbiorów encji nazywany jest

binarnym

(stopie

ń

= 2).

Wi

ę

kszo

ść

zwi

ą

zków obserwowanych w

ś

wiecie rzeczywistym to

zwi

ą

zki binarne.

W zwi

ą

zku mo

ż

e uczestniczy

ć

pewna, wi

ę

ksza od 2 ilo

ść

zbiorów encji

–

zwi

ą

zek

n-arny

(stopie

ń

= n) – mówimy wtedy o zwi

ą

zku

wieloargumentowym lub wieloczłonowym.

•

Np. zbiory encji:

przedmiot, prowadzacy

oraz

student

mog

ą

by

ć

poł

ą

czone

zwi

ą

zkiem o stopniu = 3.

prowadzacy

ma

zaj

ę

cia

przedmiot

student

16

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Stopie

ń

przyporz

ą

dkowania (krotno

ść

zwi

ą

zków):

Stopie

ń

przyporz

ą

dkowania

zbiorów encji do zwi

ą

zku (relacji)

okre

ś

lony jest przez ilo

ść

encji z ka

ż

dego zbioru, które s

ą

ze sob

ą

w

tym zwi

ą

zku powi

ą

zane.

Rodzaje zwi

ą

zków binarnych

ze wzgl

ę

du na stopie

ń

przyporz

ą

dkowania:

•

jeden do jeden

•

jeden do wielu

•

wiele do wielu

W diagramie E-R

rodzaje zwi

ą

zków s

ą

oznaczane w nast

ę

puj

ą

cy

sposób:

→

(grot strzałki) oznacza stopie

ń

przyporz

ą

dkowania jeden

— (brak grotu) – wielu

lub:

17

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Stopie

ń

uczestnictwa:

Je

ż

eli ka

ż

da encja zbioru

musi

bra

ć

udział w zwi

ą

zku, wówczas jest

on całkowity (uczestnictwo obowi

ą

zkowe); w przeciwnym razie –

cz

ęś

ciowy (uczestnictwo opcjonalne).

W diagramie E-R linie podwójne

ł

ą

cz

ą

zbiory encji ze zbiorami

zwi

ą

zków w przypadku całkowito

ś

ci.

Wyst

ę

powanie encji pewnego zbioru (istnienie obiektu w

rzeczywisto

ś

ci) mo

ż

e zale

ż

e

ć

od wyst

ę

powania encji (obiektu) innego

zbioru.

Je

ż

eli wyst

ę

powanie encji x zale

ż

y od y (is existence dependent on),

to:

•

y jest encj

ą

nadrz

ę

dn

ą

(dominuj

ą

c

ą

) (dominant entity)

•

x jest encj

ą

podrz

ę

dn

ą

(subordinate entity)

Np. je

ś

li encja dominuj

ą

ca

student

zostanie usuni

ę

ta z bazy, to

wszystkie wyst

ą

pienia zwi

ą

zanych z ni

ą

encji

oceny

tak

ż

e musz

ą

zosta

ć

usuni

ę

te.

18

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Role:

Zbiory encji pozostaj

ą

ce w zwi

ą

zku niekoniecznie musz

ą

by

ć

ró

ż

ne,

wówczas encje takie wyst

ę

puj

ą

w ró

ż

nych

rolach

, a zwi

ą

zek taki

nazywamy

zwi

ą

zkiem rekurencyjnym

.

W diagramie E-R

role oznaczane s

ą

przez etykiety przy liniach

ł

ą

cz

ą

cych symbol encji (prostok

ą

t) i zwi

ą

zku (romb). Oznaczanie ról

nie jest obowi

ą

zkowe, stosuje si

ę

je dla podkre

ś

lenia semantyki

zwi

ą

zku.

prowadzacy

hierarchia

profesor

asystenci

19

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Decyzje projektowe:

Zbiory encji czy atrybuty

Wybór jest zale

ż

ny od rodzaju modelowanego przedsi

ę

wzi

ę

cia (fragmentu

rzeczywisto

ś

ci) oraz od znaczenia poszczególnych atrybutów.

Zbiory encji czy zbiory zwi

ą

zków

Mo

ż

liwa wskazówka: zwi

ą

zek wyznacza pewn

ą

akcj

ę

(zdarzenie), która

zachodzi (zaszła) pomi

ę

dzy encjami (obiektami).

Zwi

ą

zki binarne czy zwi

ą

zki o wy

ż

szym stopniu

Zwi

ą

zek o stopniu wi

ę

kszym ni

ż

2 zawsze mo

ż

e by

ć

zast

ą

piony przez pewn

ą

liczb

ę

zwi

ą

zków binarnych. Jednak zwi

ą

zki n-arne mog

ą

lepiej oddawa

ć

semantyk

ę

relacji (jawne okre

ś

lenie,

ż

e chodzi o zwi

ą

zek wi

ę

cej ni

ż

2

obiektów).

Przykład:

1. Adres jako atrybut
Adres mo

ż

e by

ć

traktowany jako atrybut (na przykład encji

student

), wówczas jego warto

ś

ci

ą

jest tekst, np: "Pozna

ń

, ul. Kwiatowa 5 m. 12".

2. Adres jako jednostka
Adres mo

ż

e by

ć

traktowany jako niezale

ż

na jednostka o atrybutach:

Miejscowo

ść

, Ulica,

Nr Domu, Nr Lokalu.

20

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Ograniczenia - warunki integralno

ś

ci

W ramach projektowania baz danych specyfikuje si

ę

pewne

ograniczenia ilo

ś

ciowe i zale

ż

no

ś

ci pomi

ę

dzy encjami lub zwi

ą

zkami,

które maj

ą

zachodzi

ć

zawsze.

Ograniczenia te i zale

ż

no

ś

ci okre

ś

laj

ą

warunki integralno

ś

ci

rozwa

ż

nej bazy i s

ą

nast

ę

pnie sprawdzane i dotrzymywane.

Do najistotniejszych warunków ogólnych, specyfikowalnych w modelu
E-R nale

żą

:

•

stopie

ń

przyporz

ą

dkowania zbiorów encji do zwi

ą

zku,

•

zale

ż

no

ść

wyst

ę

powania encji z ró

ż

nych zbiorów,

•

całkowito

ść

vs. cz

ęś

ciowo

ść

udziału zbioru encji w zbiorze zwi

ą

zków.

21

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Klucze w zbiorze encji:

Nadkluczem

(super key) jest ka

ż

dy zbiór atrybutów, którego warto

ś

ci

jednoznacznie identyfikuj

ą

encj

ę

w zbiorze encji.

Kluczem kandyduj

ą

cym

(candidate key) nazywany jest nadklucz o

minimalnej liczbie atrybutów w zbiorze encji.

W danym zbiorze mo

ż

e wyst

ę

powa

ć

kilka kluczy kandyduj

ą

cych, jeden

z nich (dowolnie wybrany) nazywany jest

kluczem głównym

(primary

key).

W diagramie E-R

klucze główne s

ą

oznaczane przez podkre

ś

lenie

etykiety atrybutu.

22

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Klucze w zbiorze zwi

ą

zków:

Je

ż

eli zbiór zwi

ą

zków posiada atrybuty o odpowiednich własno

ś

ciach,

na podstawie tych atrybutów mo

ż

na zdefiniowa

ć

dla niego

(jednoznacznie identyfikuj

ą

ce)

klucze główne

tak, jak dla zbiorów

encji.

Zło

ż

enie

kluczy głównych zbiorów encji bior

ą

cych udział w zwi

ą

zku

tworzy

nadklucz

dla zbioru zwi

ą

zków.

Sposób zdefiniowania

klucza głównego

zale

ż

y od stopnia zwi

ą

zku

oraz stopnia przyporz

ą

dkowania poszczególnych zbiorów encji.

•

Dla binarnego zwi

ą

zku

jeden do jeden

ka

ż

dy z kluczy głównych (encji) mo

ż

e by

ć

kluczem głównym.

•

Dla binarnego zwi

ą

zku

wielu do wielu

para kluczy głównych encji jest kluczem

głównym.

23

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Zbiory słabe encji:

Zbiór encji, który nie posiada klucza głównego (nie ma odpowiednich
atrybutów), jest nazywany

zbiorem słabym encji

(weak entity set) w

przeciwie

ń

stwie do zbioru silnego - (strong entity set).

Informacyjno

ść

zbioru słabych encji jest uwarunkowana istnieniem

innego silnego zbioru encji, który pozostaje ze słabym w zwi

ą

zku

jeden

do wielu

. Encje tego zbioru silnego dominuj

ą

równie

ż

nad encjami

słabego w sensie zale

ż

no

ś

ci wyst

ę

powania.

W zbiorze słabych encji wyst

ę

powa

ć

musi wówczas

klucz cz

ęś

ciowy

(partial key), który pozwala na rozró

ż

nienie (cz

ęś

ciowe) jego encji

wzgl

ę

dem dominuj

ą

cego zbioru silnego.

Kluczem głównym

zbioru słabego encji jest kompozycja klucza

głównego dominuj

ą

cego zbioru silnego encji i klucza cz

ęś

ciowego

zbioru słabego.

24

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Zbiory słabe encji (c.d.):

W diagramie E-R

klucze cz

ęś

ciowe

oznaczane s

ą

przez

podkre

ś

lenie

etykiety atrybutu

lini

ą

przerywan

ą

.

Zwi

ą

zki mi

ę

dzy słab

ą

jednostk

ą

a jednostkami jej wła

ś

cicieli

nazywamy

zwi

ą

zkami identyfikuj

ą

cymi

.

W notacji graficznej zbiory słabe encji oraz zbiory zwi

ą

zków

identyfikuj

ą

cych rysujemy

podwójn

ą

lini

ą

.

student

imie

nazwisko

nr_albumu

ocena

przedmiot

data

oceny

ma

25

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Diagramy E-R

→

tabele

Opis bazy danych za pomoc

ą

diagramu E-R mo

ż

e by

ć

przekształcony

w opis w postaci kolekcji tabel.

Istnienie kluczy głównych pozwala na jednoznaczne wyra

ż

enie

zbiorów encji i zbiorów zwi

ą

zków w formie tabel.

Zasady ogólne

•

Ka

ż

demu zbiorowi encji odpowiada tabela (tabela przyjmuje nazw

ę

zbioru encji).

•

Ka

ż

demu zbiorowi zwi

ą

zków (je

ż

eli ten posiada atrybuty) odpowiada tabela (tabela

przyjmuje nazw

ę

zbioru zwi

ą

zków).

•

Kolumny tabel reprezentuj

ą

atrybuty opisuj

ą

ce odpowiednio encje lub zwi

ą

zki.

•

Wiersze tabel odpowiadaj

ą

encj

ą

lub zwi

ą

zkom

Zbiór silny encji

jest reprezentowany bezpo

ś

rednio

Dla

zbioru słabego encji

tabela musi zawiera

ć

dodatkowo atrybuty

stanowi

ą

ce klucz główny tabeli nadrz

ę

dnej.

26

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Diagramy E-R

→

tabele (przykład)

student

imie

nazwisko

nr_albumu

ocena

przedmiot

data

oceny

ma

27

Kraków, 2006

str.

K. Regulski, ZIP, v.1.0

Diagramy E-R

→

tabele (c.d.)

Je

ż

eli

zbiór zwi

ą

zków

nie ma własnego klucza głównego, wówczas

tabela musi dodatkowo zawiera

ć

odpowiedni

ą

do własno

ś

ci

kombinacj

ę

kluczy głównych swoich zbiorów encji.

•

Np. Zwi

ą

zek

wielu do wielu

jest reprezentowany przy pomocy tabeli o dodatkowych

kolumnach odpowiadaj

ą

cych kluczom głównym zbiorów encji bior

ą

cych udział w

zwi

ą

zku.

ma

zaj

ę

cia

przedmiot

student