Transformacja modelu

EER do postaci

relacyjnego modelu

danych

Zbyszko Królikowski

Repetytorium – pojęcia podstawowe

relacyjnego modelu danych

Schemat implementacyjny

Schemat implementacyjny

(logiczny) bazy danych:

schemat, na którym działają aplikacje.

Relacja (tablica):

Relacja (tablica):

dwuwymiarowa tabela będąca

implementacją encji. Kolumny tabeli są atrybutami.

Wiersze tabeli to krotki - reprezentują wystąpienia

encji.

Klucz podstawowy

Klucz podstawowy

(główny) (identyfikator): atrybut

lub zbiór atrybutów wybrany spośród kluczy

potencjalnych do identyfikacji krotek relacji.

Klucz obcy:

Klucz obcy:

atrybut lub zbiór atrybutów relacji, który

jest kluczem podstawowym w innej relacji. Klucz

obcy służy do logicznego powiązania krotek dwóch

relacji.

Transformacja do schematu relacyjnego

- scenariusz

1.

Transformacja encji

Dla każdej encji, która nie tworzy hierarchii

encji, utwórz relację odwzorowując atrybuty

encji na nazwy kolumn relacji.

Hierarchie encji transformuj zgodnie z

odpowiednimi regułami

Utwórz klucz podstawowy relacji na

podstawie unikalnego identyfikatora encji.

Transformacja do schematu relacyjnego

- scenariusz

2.

Transformacja związków 1:1

Dla związków jednostronnie obligatoryjnych,

dodaj klucz obcy

do relacji powstałej z encji

wiązanej obligatoryjnie, na podstawie
klucza podstawowego drugiej relacji.

Dla związków dwustronnie opcjonalnych,

dodaj klucz obcy

do relacji o przewidywanej

mniejszej liczbie krotek.

Transformacja do schematu relacyjnego

- scenariusz

3.

Transformacja związków 1:N
Dodaj klucz obcy

do relacji po stronie N, na

podstawie klucza podstawowego relacji po
stronie 1.

4.

Transformacja związków M:N

Dla każdego związku

utwórz nową relację

,

zawierającą

klucze obce

zbudowane na

podstawie kluczy podstawowych wiązanych
relacji.

Transformacja encji

Zasady transformacji

:

:

Nazwę encji wyrażoną w

liczbie

mnogiej

przenosimy jako nazwę

relacji.
Atrybuty encji przenosimy jako

nazwy kolumn (atrybuty) relacji.

Unikalny identyfikator

encji

przenosimy jako klucz

podstawowy relacji.
Obligatoryjność atrybutu encji

przenosimy jako ograniczenie

NOT NULL

atrybutu relacji.

Opcjonalność atrybutu encji

przenosimy jako własność

NULL

atrybutu relacji.

PRACOWNIK

# id_pracownika

* imię

* nazwisko

o adres_ulica

o adres_miasto

PRACOWNICY

PRACOWNICY

(

(

id_pracownika primary key,
imię

NOT NULL

,

nazwisko

NOT NULL

,

adres_ulica

NULL

,

adres_miast

NULL

)

Komentarz:

Transformacja związków 1:1 –

związek jednostronie obligatoryjny

Zasady transformacji:

Tworzymy

klucz obcy

w

relacji wiązanej
obligatoryjnie na podstawie
klucza podstawowego
drugiej relacji.
Na atrybuty klucza obcego
nakładamy

ograniczenie

referencyjne

.

SPRZEDAŻ TOWARU

# numer_sprzedaży

* data_sprzedaży

* wartość

ZWROT TOWARU

# id_zwrotu

* data

o przyczyna

wycofana

dotyczy

ZWROTY

(

Id_zwrotu

PRIMARY KEY

Data

NOT NULL

Przyczyna

NULL

SPRZEDAŻE

(

Numer_sprzedaży PRIMARY KEY
Data_sprzedaży NOT NULL
Wartość

NOT NULL

FOREIGN KEY

(

Id_sprzedaży) NOT NULL

REFERENCE

Sprzedaże

(Numer_sprzedaży))

Komentarz:

Transformacja związków 1:1 –

związek dwustronnie opcjonalny

Zasady transformacji:

Tworzymy klucz obcy w relacji

o mniejszej

liczbie krotek

, na podstawie klucza

podstawowego drugiej relacji.
Na atrybuty klucza obcego nakładamy
ograniczenie referencyjne.

A
# id_A

...

B
# id_B

...

Transformacja związków 1:1 –

związek dwustronnie opcjonalny

Zasady transformacji:

Tworzymy klucz obcy w
relacji

o mniejszej

liczbie krotek

, na

podstawie klucza
podstawowego drugiej
relacji.

Pracownik
# id_P

...

Dział

# id_D

...

Która tabela

będzie miała

mniejszą liczbę

krotek ?

zarządza

jest kierowany przez

Komentarz:

Transformacja związków 1:1 –

związek dwustronnie opcjonalny

PRACOWNICY

(

Id_P PRIMARY KEY

................

Pracownik
# id_P

...

Dział

# id_D

...

zarządza

jest kierowany przez

FOREIGN KEY

(

Id_Kierownika NULL

REFERENCE

Pracownicy (Id_P))

DZIAŁY

(

Id_D PRIMARY KEY

................

Uzasadnienie:

Tylko np. 10%

pracowników

pełni funkcje

kierownicze

Transformacja związków 1:N

Zasady transformacji:

Tworzymy

klucz obcy w relacji po

stronie "wiele"

na podstawie klucza

podstawowego relacji po stronie

"jeden".
Na atrybuty klucza obcego

nakładamy

ograniczenie

referencyjne

.

Obligatoryjność związku po stronie

"wiele" przenosimy jako

ograniczenie

NOT NULL

atrybutów

klucza obcego.
Opcjonalność związku po stronie

"wiele" przenosimy jako własność

NULL

atrybutów klucza obcego

STANOWISKA

(

Nazwa

PRIMARY KEY

Płaca_min

NOT NULL

Płaca_max

NULL

PRACOWNICY

(

Id_pracownika PRIMARY KEY
Imię

NOT NULL

Nazwisko

NOT NULL

PRACOWNIK
# id_pracownika

* imię

* nazwisko

STANOWISKO
# nazwa

* płaca_min

o płaca_max

zatrudniony na

zajmowane

przez

FOREIGN KEY

Stanowisko

NOT

NULL

REFERENCE

Stanowiska(Nazwa) )

Komentarz:

Transformacja związków M:N

Zasady transformacji:

Związek M:N przenosimy jako nową relację
Tworzymy

klucze obce

na podstawie kluczy

podstawowych dwóch pozostałych relacji.
Na atrybuty jednego i drugiego klucza obcego
nakładamy dwa ograniczenia referencyjne.
Wszystkie atrybuty relacji tworzą klucz podstawowy.

Transformacja związków M:N

PROJEKTY

(

Nazwa_projektu

PRIMARY KEY

Data_rozpocz NOT NULL
Data_zakoncz NULL )

PRACOWNICY

(

Id_pracownika

PRIMARY KEY

Imię

NOT NULL

Nazwisko

NOT NULL )

PRACOWNIK

# id_pracownika

* imię

* nazwisko

PROJEKT
# nazwa_proj

* data_rozpocz

o data_zakoncz

bierze

realizowany

przez

udział w

UDZIAŁY_W_PROJEKTACH

(

Id_pracownika REFERENCE
pracownicy(id_pracownika),

Nazwa_projektu

REFERENCE

Projekty(Nazwa_projektu),

PRIMARY KEY

(Id_pracownika,

Nazwa_projektu) )

Relacja systemowa (związku) stanowiąca implementację związku M:N

Komentarz:

Transformacja związków M:N

PRACOWNIK

# id_pracownika

* imię

* nazwisko

PROJEKT
# nazwa_proj

* data_rozpocz

o data_zakoncz

jest realizowany

przez

pracuje nad

A co w przypadku gdybyśmy

chcieli przechowywać

informację o liczbie godzin

tygodniowo przepracowanych

przez pracownika nad

określonym projektem ?

Transformacja związków M:N

PRACOWNIK

# id_pracownika

* imię

* nazwisko

PROJEKT
# nazwa_proj

* data_rozpocz

o data_zakoncz

jest realizowany

przez

pracuje nad

Jest to przypadek atrybutu

charakteryzującego

związek !

Transformacja związków M:N

PRACOWNIK

# id_pracownika

* imię

* nazwisko

PROJEKT
# nazwa_proj

* data_rozpocz

o data_zakoncz

jest realizowany

przez

pracuje nad

PROJEKTY

(

Nazwa_projektu

PRIMARY KEY

Data_rozpocz NOT NULL
Data_zakoncz NULL )

PRACOWNICY

(

Id_pracownika

PRIMARY KEY

Imię

NOT NULL

Nazwisko

NOT NULL )

UDZIAŁY_W_PROJEKTACH

(

Id_pracownika REFERENCE

Pracownicy(id_pracownika),

Nazwa_projektu

REFERENCE

Projekty(Nazwa_projektu),

Godziny

NULL

PRIMARY KEY

(Id_pracownika,

Nazwa_projektu) )

Atrybut związku

Ćwiczenie 1

Wytwórnie filmów

Chcemy pamiętać w systemie jakie

filmy

(tytuł, data produkcji,

budżet) zostały wyprodukowane przez różne

wytwórnie.

Każdy

film został wyreżyserowany przez jednego

reżysera

(nazwisko,

imię, data urodzenia, telefon). W filmie zazwyczaj występuje
wielu

aktorów

(nazwisko, imię, adres, telefon). Chcemy

wiedzieć w jakiej

roli

dana osoba występowała w filmie oraz

jaka była gaża jaką za swoją grę ona dostała. Reżyserzy
bardzo często są aktorami w filmach (w tym w swoich filmach).
Zdarza się również tak, że jedna osoba gra w wielu rolach w
jednym filmie.

Zadanie 4.

Dokonaj transformacji opracowanego wcześniej

modelu ER do postaci schematu relacyjnej bazy danych.

Ćwiczenie 2

Lecznica zwierząt

Chcemy pamiętać w systemie podstawowe informacje o pacjentach lecznicy
zwierząt, przy czym dla danego gatunku (np. pies) trzeba pamiętać rasę
pacjenta (np. minster lander). Lecznica zatrudnia lekarzy weterynarii.
Lekarze w ramach swoich obowiązków przyjmują określonych pacjentów
(wizyty). Wizyty mogą mieć różny charakter, tj. w ich trakcie mogą być
wykonywane badania, zabiegi i operacje. Oczywiście za wizyty lecznica
pobiera określone płatności. Chcemy wiedzieć jacy lekarze jakich pacjentów
przyjmowali w ramach wizyt oraz jakie badania, zabiegi lub operacje tym
pacjentom były wykonywane i przez kogo. O wszystkich wymienionych tutaj
elementach tej dziedziny rzeczywistości, będziemy chcieli przechowywać
odpowiednie informacje w projektowanej bazie danych.

Zadanie 4.

Dokonaj transformacji opracowanego wcześniej

modelu ER do postaci schematu relacyjnej bazy danych.

Komentarz:

Komentarz:

Rekurencyjny związek 1:N

Zasady transformacji:

Zasady transformacji
związków rekurencyjnych –
analogicznie jak w przypadku
związków 1:1, 1:N oraz M:N.

PRACOWNICY

(

Id_pracownika PRIMARY KEY
... ,

id_szefa

REFERENCE

Pracownicy(id_pracownika) )

P R A C O W N I K

# i d _ p r a c

* i m i ę

* n a z w i s k o

. . .

k i e r u j e

p o d l e g a

Transformacja hierarchii encji – do

trzech relacji

Zasady transformacji:

Tworzymy jedną relację

zawierająca atrybuty wspólne

i atrybut określający

typ

specjalizacji

Dla każdej specjalizacji

tworzymy relację zawierającą

jej atrybuty specyficzne i

klucz podstawowy

dziedziczony z generalizacji.

PRACOWNICY

(

Id_pracownika PRIMARY KEY
atrybuty_wspólne,

typ_prac NOT NULL

)

PRACOWNIK

atrybuty_wspólne

PRACOWNIK

atr_specyf_kraj

PRACOWNIK

atr_specyf_zagr

# id_prac

KRAJOWY

ZAGRANICZNY

PRACOWNICY_KRAJ

(

Id_pracownika PRIMARY KEY
atr_specyf_kraj )

PRACOWNICY_ZAGR

(

Id_pracownika PRIMARY KEY
atr_specyf_zagr )

Komentarz:

Transformacja hierarchii encji – do

dwóch relacji

Zasady transformacji:

Dla każdej specjalizacji
tworzymy relację zawierającą
atrybuty wspólne, atrybuty
specyficzne danej
specjalizacji i klucz
podstawowy dziedziczony z
generalizacji.

PRACOWNIK

atrybuty_wspólne

PRACOWNIK

atr_specyf_kraj

PRACOWNIK

atr_specyf_zagr

# id_prac

KRAJOWY

ZAGRANICZNY

PRACOWNICY_KRAJ

(

Id_pracownika PRIMARY KEY
atrybuty wspólne
atr_specyf_kraj )

PRACOWNICY_ZAGR

(

Id_pracownika PRIMARY KEY
atrybuty wspólne
atr_specyf_zagr )

Komentarz:

Transformacja hierarchii encji – do

jednej relacji

Zasady transformacji:

Tworzymy relację zawierającą

atrybuty wspólne, atrybuty

specyficzne wszystkich

specjalizacji i

atrybut określający

typ specjalizacji

.

Wszystkim atrybutom

specyficznym poszczególnych

specjalizacji nadajemy własność
NULL

.

PRACOWNIK

atrybuty_wspólne

PRACOWNIK

atr_specyf_kraj

PRACOWNIK

atr_specyf_zagr

# id_prac

KRAJOWY

ZAGRANICZNY

PRACOWNICY

(

Id_pracownika PRIMARY KEY
atrybuty wspólne
atr_specyf_kraj NULL
atr_specyf_zagr NULL

typ_prac

NOT NULL)

Przetransformuj poniższy diagram związków encji (EER) do schematu

relacyjnego. Zaznacz klucze główne (podkreśleniem ciągłym) oraz klucze

obce (podkreśleniem przerywanym) w schematach relacji. Zaznacz

dodatkowo obowiązkowość/opcjonalność kolumn relacji.

Ćwiczenie 3

Komentarz:

Komentarz: