Transformacja do schematu relacyjnego - scenariusz

(1)

1

Transformacja modelu EER do postaci relacyjnego modelu

danych

Zbyszko Królikowski

(2)

2

Repetytorium – pojęcia podstawowe relacyjnego modelu danych

Schemat implementacyjny (logiczny) bazy danych:

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

Relacja (tablica): dwuwymiarowa tablica będąca

implementacją encji. Kolumny tablicy są atrybutami.

Wiersze tablicy to krotki - reprezentują wystąpienia encji.

Klucz podstawowy (główny) (identyfikator): atrybut lub zbiór atrybutów wybrany spośród kluczy

potencjalnych do identyfikacji krotek relacji.

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.

(3)

3 1. Transformacja encji

Dla każdej encji, która nie tworzy hierarchii encji, utwórz relację odwzorowując atrybuty encji na nazwy kolumn relacji.

Utwórz klucz podstawowy relacji na podstawie unikalnego identyfikatora encji.

Transformacja do schematu relacyjnego - scenariusz

Hierarchie encji transformuj zgodnie z odpowiednimi regułami

(4)

4

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.

(5)

5

Transformacja do schematu relacyjnego - scenariusz

3. Transformacja zwiazkó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.

(6)

6

Komentarz:

(7)

7

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 ((

primary key (id_pracownika),

imię NOT NULL,

nazwisko NOT NULL, adres_ulica NULL,

adres_miast NULL )

(8)

8

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 (

PRIMARY KEY Id_zwrotu

Data NOT NULL

Przyczyna NULL SPRZEDAŻE (

PRIMARY KEY Numer_sprzedaży Data_sprzedaży NOT NULL

Wartość NOT NULL

FOREIGN KEY (Id_sprzedaży) NOT NULL

REFERENCE Sprzedaże (Numer_sprzedaży))

(9)

9

Komentarz:

(10)

10

Transformacja związków 1:1 – Związek dwustronnie opcjonalny

A

# id_A ...

B

# id_B ...

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.

(11)

11

Transformacja związków 1:N

PRACOWNIK

# id_pracownika

* imię

* nazwisko

zatrudniony na za

STANOWISKO

# nazwa

* płaca_min o płaca_max jmowane

przez

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

FOREIGN KEY Stanowisko NOT NULL REFERENCE

Stanowiska(Nazwa) )

(12)

12

Komentarz:

(13)

13

Transformacja związków M:N

PRACOWNIK

# id_pracownika

* imię

* nazwisko

PROJEKT

# nazwa_proj

* data_rozpocz o data_zakoncz bierze

realizowany przez udział w

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

PRIMARY KEY (Id_pracownika, Nazwa_projektu) )

PROJEKTY (

Nazwa_projektu PRIMARY KEY Data_rozpocz NOT NULL

Data_zakoncz NULL )

(14)

14

Transformacja związków M:N

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.

(15)

15

Komentarz:

(16)

16

Ć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.

(17)

17

Rozwiązanie:

(18)

18

Rozwiązanie:

(19)

19

Ć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.

(20)

20

Rekurencyjny związek 1:N

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

(21)

21

Transformacja hierarchii encji – do trzech relacji

PRACOWNIK

atrybuty_wspólne PRACOWNIK atr_specyf_kraj

PRACOWNIK atr_specyf_zagr

# id_prac

KRAJOWY ZAGRANICZNY

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 ) PRACOWNICY_KRAJ (

Id_pracownika PRIMARY KEY atr_specyf_kraj )

PRACOWNICY_ZAGR (

Id_pracownika PRIMARY KEY atr_specyf_zagr )

(22)

22

Komentarz:

(23)

23

Transformacja hierarchii encji – do dwóch relacji

PRACOWNIK

# id_prac

Dla każdej specjalizacji

tworzymy relację zawierającą atrybuty wspólne, atrybuty specyficzne danej

specjalizacji i klucz

podstawowy dziedziczony z generalizacji.

PRACOWNICY_KRAJ (

Id_pracownika PRIMARY KEY atrybuty wspólne

atr_specyf_kraj )

PRACOWNICY_ZAGR (

atr_specyf_zagr )

(24)

24

Komentarz:

(25)

25

Transformacja hierarchii encji – do jednej relacji

PRACOWNIK

# id_prac

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.

PRACOWNICY (

atr_specyf_kraj NULL atr_specyf_zagr NULL

typ_prac NOT NULL)

(26)

Zadanie

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.