Bazy danych
Zasady konstrukcji baz danych
Diagram związków encji
Cel: Opracowanie modelu logicznego danych
Diagram związków encji [ang. Entity-Relationship diagram]:
" zapewnia efektywne operacje na danych
" chroni przed anomaliami (wstawiania, usuwania, istnienia)
" pozwala zbudować systematyczny model rzeczywistości opisywanej
przez bazę danych
kod
PESEL
nazwa
Instytut
pracuje
nazwisko
profil
Pracownik
imię
skrót
prowadzi Przedmiot
stanowisk
semestr
o
rodzaj nazwa
2
Diagram związków encji
Encja Encja  obiekt występujący w rzeczywistości
Atrybut  cecha, właściwość obiektu lub związku
atrybut
atrybut kluczowy (klucz)
klucz
klucz
klucz złożony z kilku atrybutów
klucz
Związek  związek zachodzący pomiędzy obiektami
związek
3
Diagram E-R
Uproszczenia
Pracownik
" włączenie atrybutów do symbolu encji
PESEL
Imię
" pominięcie atrybutów na diagramie
Nazwisko
(umieszczenie atrybutów w osobnym dokumencie)
stanowisko
" opuszczenie symbolu graficznego związku
(pozostaje tylko linia)
pracuje
Instytut
Pracownik
4
Rodzaje związków
1  1 (jedno-jednoznaczny)
prawdopodobnie można połączyć
encje ze sobą
1  wielu (jednoznaczny)
Odpowiada funkcji
Instytut Pracownik
każdy pracownik jest związany
tylko z jednym instytutem
wiele  wielu (wieloznaczny)
Przedmiot
Pracownik
każdy pracownik prowadzi grupę
(1 lub więcej) przedmiotów
5
Związki opcjonalne i rekurencyjne
Związek opcjonalny
Instytut Pracownik
istnieją pracownicy nie przypisani
do żadnego instytutu (np. stróż)
Związek rekurencyjny
Pracownik
pracownik1 jest kierownikiem
pracowników
6
Podtypy i nadtypy
Podtypy - odmiany tej samej encji - nadtypu
Towar
" podobny zbiór atrybutów w encjach
AGD
" można określić wspólnym pojęciem
RTV
Konstrukcja nadtyp (typu uogólnionego)
" zbiór wszystkich pól podtypów
" pola niewykorzystane = NULL
Narzędzia
7
Modelowanie zbieżne
Poszukiwanie nadtypów
Próba łączenia podobnych encji
Zmniejszenie liczby encji w bazie danych
Większa elastyczność i abstrakcyjność modelu
Uproszczenie dostępu do danych
Zmniejszenie liczby złączeń (kosztem selekcji)
8
Abstrakcyjny model świata
Wartość atrybutu
dla dla z
Kwalifikowana dziedzina dla klasyfikacja
Zbudowany
klasyfikacja
z jako
Struktura
Obiekt Atrybut Typ atrybutu
z
z
część
z
z
z
klasyfikacja klasyfikacja
klasyfikacja
Typ struktury Typ obiektu
rysunek z książki: Ulka Rodgers  ORACLE, przewodnik projektanta baz danych
9
Diagram hierarchii funkcji
Cel zastosowania: Zapanowanie nad dużym projektem
Sprzedaż wysyłkowa
towarów
Zarządzanie finansami firmy
Zarządzanie pracownikami
Sprzedaż klientom
Zaopatrzenie
10
Na podstawie: Ulka Rodgers Oracle, przewodnik projektanta baz danych
Diagram hierarchii funkcji
Sprzedaż wysyłkowa
towarów
Zarządzanie finansami firmy
Płać dostawcom
Zarządzanie pracownikami Obciążaj klientów
Sprzedaż klientom Kontroluj stan finansów
Płać pracownikom
Zaopatrzenie
Na podstawie: Ulka Rodgers Oracle, przewodnik projektanta baz danych
11
Budowa modelu relacyjnego
na podstawie diagramu związków encji
Konwersja
Encja Tabela
Atrybuty encji Pola w tabeli
Związki encji Relacje
Rozwinięcie związku wiele-wielu
Przedmiot
Pracownik
Każdy pracownik prowadzi grupę
(1 lub więcej) przedmiotów.
12
Budowa modelu relacyjnego
na podstawie diagramu związków encji
Konwersja
Encja Tabela
Atrybuty encji Pola w tabeli
Związki encji Relacje
Rozwinięcie związku wiele-wielu
zastąpienie dwoma związkami 1-wielu
Lista
Przedmiot
Pracownik
przedm.
Każdy pracownik ma tylko jedną Pozycja listy zawiera tylko jeden
listę prowadzonych przedmiotów. przedmiot.
13
Realizacja związku 1-wielu
Instytut Pracownik
Tabela Pracownicy
Tabela Instytuty
Instytut IDA Nazwisko Tytuł
Nazwa IDI(klucz)
I-16 1 Kowalski mgr
Instytut Metrologii I-14
I-16 2 Nowak dr
Instytut I-15
I-15 3 Dębski mgr
Elektrotechniki
I-14 4 Wrocławski mgr
Instytut Elektroniki I-16
I-14 5 Kamiński dr
Tabela nadrzędna
Tabela podrzędna
14
Integralność związku
Wartość klucza do której się odwołuje encja podrzędna
musi istnieć w encji nadrzędnej.
Tabela Pracownicy
Tabela Instytuty
Instytut IDA Nazwisko Tytuł
Nazwa IDI(klucz)
I-16 1 Kowalski mgr
Instytut Metrologii I-14
I-16 2 Nowak dr
?
Instytut I-15
I-20 3 Dębski mgr
Elektrotechniki
I-14 4 Wrocławski mgr
Instytut Elektroniki I-16
I-14 5 Kamiński dr
15
Integralność związku
Utrzymywanie integralności przez system (opcjonalne)
- system nie pozwala dodać danych naruszających integralność
- postępowanie przy usuwaniu rekordów encji nadrzędnej
" usuwanie kaskadowe (rekordów powiązanych)
" blokada usuwania jeśli istnieją powiązane rekordy
- postępowanie przy modyfikacji wartości klucza
" blokada pola klucza
" kaskadowe uaktualnianie w tabelach podrzędnych
16
Klucze, zależność funkcyjna
Klucz główny
minimalny zbiór pól (pole) na podstawie których można
jednoznacznie zidentyfikować każdy rekord
Klucz obcy
odwołanie do klucza głównego innej tabeli
Zależność funkcyjna ( istnienie funkcji )
Pomiędzy zbiorem pól A oraz polem b zachodzi zależność
funkcyjna jeśli wartość pola b można jednoznacznie określić na
podstawie wartości pól zbioru A.
A b
17
Klucz główny
Tabela: lista przedmiotów prowadzonych przez nauczycieli
IDnauczyciela Skrót_przedmiotu LiczbaGodzin
10 ALG 5
10 ANAL 30
11 ALG 10
12 TPROB 15
12 ANAL 30
13 TMNOG 45
Klucz główny tabeli: IDnauczyciela i Skrót_przedmiotu
18
Klucz obcy
Tabela: lista przedmiotów prowadzonych przez nauczycieli
IDnauczyciela Skrót_przedmiotu LiczbaGodzin
10 ALG 5
10 ANAL 30
11 ALG 10
12 TPROB 15
12 ANAL 30
13 TMNOG 45
Dwa klucze obce tabeli:
1. IDnauczyciela
2. Skrót_przedmiotu
19
Zależność funkcyjna
Tabela: lista przedmiotów prowadzonych przez nauczycieli
IDn SkrPrzedm LiczbaGodzin
10 ALG 5
Zależność funkcyjna:
10 ANAL 30
(IDn, SkrPrzedm) LiczbaGodzin
11 ALG 10
12 TPROB 15
Brak zależności funkcyjnej:
12 ANAL 30
(IDn) SkrPrzedm
13 TMNOG 45
(IDn) LiczbaGodzin
...
20
Normalizacja tabel
Normalizacja:
" doprowadzanie tabel do postaci umożliwiającej efektywne
wykonywanie operacji na danych
" uniknięcie anomalii przy zmianach danych
" podział tabeli (pola) na kilka tabel (pól)
Tabele utworzone na podstawie diagramu E-R są znormalizowane.
Formy normalne [ang. Normal Form = NF]:
" numeracja form 1NF, 2NF, ...
" zawieranie się form normalnych w sobie (tabela w 3NF jest w 2NF)
21
Pierwsza i druga postać normalna
1NF  każde pole zawiera niepodzielną część informacji
2NF  każde pole niekluczowe zależy jedynie od całego klucza
głównego (nie można znalez
ć zależności funkcyjnej
od części klucza)
Jak wykryć postać  nie 2NF ?
Należy sprawdzić czy istnieją pola, które zależą tylko od
podzbióru (części) klucza głównego
Jak doprowadzić do 2NF?
Podział na dwie związane tabele.
Anomalie w  nie 2NF
Anomalia usuwania, aktualizacji, istnienia
22
Tabele nie spełniające 2NF
Tabela: lista rachunków za telefony (tylko jeden w miesiącu)
NRklienta Nazwisko Imię Telefon Data Kwota
102034 Nowak Jan 656-23-00 10-01 34,56
102034 Nowak Jan 656-23-00 11-02 40,50
103000 Kowalska Anna 724-00-01 12-04 120,00
103000 Kowalska Anna 724-00-01 10-01 30,45
103000 Kowalska Anna 724-00-01 9-02 100,32
301988 Woz
niak Stefan 546-00-23 5-01 80,87
301988 Woz
niak Stefan 546-00-23 6-02 70,65
Klucz główny: (NRklienta i Data)
(NRklienta) Nazwisko, Imię, Telefon
Zależności funkcyjne:
(Data, NRklienta) Kwota
23
Anomalie gdy nie jest spełniona 2NF
Tabela: lista rachunków za telefony (tylko jeden w miesiącu)
NRklienta Nazwisko Imię Telefon Data Kwota
102034 Nowak Jan 656-23-00 10-01 34,56
102034 Nowak Jan 656-23-00 11-02 40,50
103000 Kowalska Anna 724-00-01 12-04 120,00
103000 Kowalska Anna 724-00-01 10-01 30,45
103000 Kowalska Anna 724-00-01 9-02 100,32
301988 Woz
niak Stefan 546-00-23 5-01 80,87
301988 Woz
niak Stefan 546-00-23 6-02 70,65
Anomalie:
Nie można dodać klienta, który jeszcze nie zapłacił rachunku
Zmiana telefonu dla jednego klienta musi być wykonywana w wielu miejscach
24
Normalizacja do 2NF
Podział na dwie tabele: Klienci i Rachunki
NRklienta Nazwisko Imię Telefon Data Kwota
102034 Nowak Jan 656-23-00 10-01 34,56
102034 Nowak Jan 656-23-00 11-02 40,50
103000 Kowalska Anna 724-00-01 12-04 120,00
103000 Kowalska Anna 724-00-01 10-01 30,45
103000 Kowalska Anna 724-00-01 9-02 100,32
301988 Woz
niak Stefan 546-00-23 5-01 80,87
301988 Woz
niak Stefan 546-00-23 6-02 70,65
Klucz główny: (NRklienta i Data)
(NRklienta) Nazwisko, Imię, Telefon
Zależności funkcyjne:
(Data, NRklienta) Kwota
25
Normalizacja do 2NF
Tabela Rachunki
NRk Data Kwota
Tabela Klienci
102034 10-01 34,56
NRklienta Nazwisko Imię Telefon
102034 11-02 40,50
102034 Nowak Jan 656-23-00
103000 12-04 120,00
103000 Kowalska Anna 724-00-01
103000 10-01 30,45
301988 Woz
niak Stefan 546-00-23
103000 9-02 100,32
301988 5-01 80,87
301988 6-02 70,65
Klucz główny: (NRk i Data)
Klucz główny: (NRklienta)
Klucz obcy: (NRk)
26
Trzecia postać normalna
3NF  nie istnieją zależności funkcyjne pól od innych pól
niekluczowych
Jak wykryć nie 3NF?
Należy sprawdzić czy istnieją pola, które zależą
tylko od innych pól
Jak doprowadzić do 3NF?
Podział na dwie związane tabele.
Anomalie w  nie 3NF
istnienia i modyfikacji
27
Anomalie gdy nie jest spełniona 3NF
Tabela Pracownicy
NRpracownika Nazwisko Imię Stanowisko Średnie zarobki
102034 Nowak Jan profesor 3000
102035 Nowak Janina adiunkt 2500
103000 Kowalska Anna adiunkt 2500
103103 Kowalczyk Tomasz asystent 2000
103000 Kowalski Dariusz asystent 2000
301988 Woz
niak Stefan asystent 2000
Klucz: (NRpracownika)
Anomalie aktualizacji i istnienia:
Zmiana wynagrodzenia osób na określonym stanowisku musi być
wykonywana w wielu miejscach.
Nie można wprowadzić nowego stanowiska bez konkretnej osoby
28
Normalizacja do 3NF
Podział na dwie tabele: Pracownicy,  wynagrodzenie na stanowisku
NRpracownika Nazwisko Imię Stanowisko Średnie zarobki
102034 Nowak Jan profesor 3000
102035 Nowak Janina adiunkt 2500
103000 Kowalska Anna adiunkt 2500
103103 Kowalczyk Tomasz asystent 2000
103000 Kowalski Dariusz asystent 2000
301988 Woz
niak Stefan asystent 2000
Klucz: (NRpracownika)
Zależność funkcyjna: Stanowisko Średnie zarobki
29
Normalizacja do 3NF
Tabela  wynagrodzenie
Tabela Pracownicy
na stanowisku
NRpracownik Nazwisko Imię Stanowisko
Stanow. Średnie zarobki
a
profesor 3000
102034 Nowak Jan profesor
adiunkt 2500
102035 Nowak Janina adiunkt
asystent 2000
103000 Kowalska Anna adiunkt
Klucz: (Stanow.)
103103 Kowalczyk Tomasz asystent
103000 Kowalski Dariusz asystent
301988 Woz
niak Stefan asystent
Klucz główny: (NRpracownika)
Klucz obcy: Stanowisko
30
Aktualność a rejestracja historii
Normalizacja
 pozwala przechowywać aktualne dane w jednym miejscu
 upraszcza modyfikowanie danych
Rejestracja historii
 zachowanie wartości z określonej chwili czasowej
 niezmienność wartości w istniejących dokumentach
 aktualna wartość dla nowo tworzonych dokumentów
 realizacja przez kopiowanie do jednej tabeli wartości z
innych związanych tabel
 problem wprowadzania poprawek do wystawionych dokumentów
31
Wielostanowiskowe
bazy danych
Zagadnienia wykładu
" Architektura klient-serwer
" Problemy równoczesnego dostępu do bazy danych
" Blokowanie dostępu do przetwarzanych danych
" Transakcje
33
Zapotrzebowanie
" Wspólna baza danych
" Jednoczesny dostęp wielu użytkowników
" Współbieżne wykonywanie operacji
Użytkownik 3
Użytkownik 1
Baza
danych
Użytkownik 4
Użytkownik 2
Użytkownik 5
34
Architektura klient-serwer
" Podział na dwa procesy
Serwer
" Klient  wydaje polecenia
" Serwer  realizuje polecenia i zwraca wynik
" Możliwość konfiguracji sieciowej
Klient
35
Architektura klient-serwer
" Podział na dwa procesy
Serwer
" Klient  wydaje polecenia
" Serwer  realizuje polecenia i zwraca wynik
" Możliwość konfiguracji sieciowej
Klient 3
Klient 1 Klient 2
36
Struktura aplikacji bazy danych
System Bazy
" Różne możliwości podziału na części
Danych
 klient i  serwer :
SQL Serwer SQL
- uniwersalny serwer bazy danych (SQL)
- serwer dostosowany do określonego zadania
Implementacja
modelowanych
" Bezpieczeństwo bazy danych
pojęć i reguł
" Ilość przesyłanych danych
poprawności
Interfejs
użytkownika
Klient
37
Struktura aplikacji bazy danych
System Bazy
" Różne możliwości podziału na części
Danych
 klient i  serwer :
SQL
- uniwersalny serwer bazy danych (SQL)
- serwer dostosowany do określonego zadania
Implementacja
modelowanych
" Bezpieczeństwo bazy danych
pojęć i reguł
" Ilość przesyłanych danych
poprawności
Serwer
Interfejs
użytkownika
Klient
38
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
102 112400 112088 1000,00
Pobranie treści tego samego rekordu
Użytkownik 1 Użytkownik 2
NRop Od Do Kwota NRop Od Do Kwota
102 112400 112088 1000,00 102 112400 112088 1000,00
39
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
102 112400 112088 1000,00
Pobranie treści tego samego rekordu
Użytkownik 1 Użytkownik 2
NRop Od Do Kwota NRop Od Do Kwota
102 112400 112088 1200,00 102 112400 112088 1000,00
Modyfikacja pola Kwota
40
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
102 112400 112088 1000,00
Użytkownik 1 Użytkownik 2
NRop Od Do Kwota NRop Od Do Kwota
102 112400 112088 1200,00 102 112400 143000 1000,00
Modyfikacja pola  Do
41
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
102 112400 112088 1200,00
Zwrócenie rekordu do bazy
Użytkownik 1 Użytkownik 2
NRop Od Do Kwota NRop Od Do Kwota
102 112400 112088 1200,00 102 112400 143000 1000,00
Modyfikacja pola  Do
42
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
102 112400 143000 1000,00
Zwrócenie rekordu do bazy
Użytkownik 1 Użytkownik 2
NRop Od Do Kwota
102 112400 143000 1000,00
43
Problemy równoczesnego dostępu
Stracona modyfikacja
NR operacji Od Do Kwota
Jest:
102 112400 143000 1000,00
Powinno być:
NR operacji Od Do Kwota
102 112400 143000 1200,00
44
Problemy równoczesnego dostępu
Odczytanie nieaktualnych danych
Niedopuszczalny debet na koncie.
NRrachunku Saldo
10203 1000,00
Opłata 1. Opłata 2.
Kwota 800zł Kwota 400zł
45
Problemy równoczesnego dostępu
Odczytanie nieaktualnych danych
Niedopuszczalny debet na koncie.
NRrachunku Saldo
10203 1000,00
Sprawdzenie stanu konta
Opłata 1. Opłata 2.
Kwota 800zł Kwota 400zł
Stan konta 1000zł
46
Problemy równoczesnego dostępu
Odczytanie nieaktualnych danych
Niedopuszczalny debet na koncie.
NRrachunku Saldo
10203 1000,00
Sprawdzenie stanu konta
Opłata 1. Opłata 2.
Kwota 800zł Kwota 400zł
Stan konta 1000zł
47
Problemy równoczesnego dostępu
Odczytanie nieaktualnych danych
Niedopuszczalny debet na koncie.
NRrachunku Saldo
10203 200,00
Realizacja opłaty 1.
-800zł
Opłata 1. Opłata 2.
Kwota 800zł Kwota 400zł
48
Problemy równoczesnego dostępu
Odczytanie nieaktualnych danych
Niedopuszczalny debet na koncie.
Debet
NRrachunku Saldo
na koncie
10203
 200,00
200zł
Realizacja opłaty 2.
-400zł
Opłata 1. Opłata 2.
Kwota 800zł Kwota 400zł
49
Blokowanie dostępu do danych
Rodzaj blokady Uprawnienie Uprawnienia
innych
zakładającego
tylko do odczytu odczyt odczyt
do zapisu odczyt i zapis odczyt
wyłączność odczyt i zapis brak dostępu
Granulacja blokady:
" baza danych,
" tabela,
" zestaw rekordów,
" rekord,
" pole
50
Zakleszczenie
Wzajemne zablokowanie się dwóch procesów, spowodowane
zakładaniem blokad na wspólne dane.
Przykład
Założenie
blokady
Tabela A
Założenie
Proces 1 Proces 2
blokady
Tabela B
51
Zakleszczenie
Wzajemne zablokowanie się dwóch procesów, spowodowane
zakładaniem blokad na wspólne dane.
Próba założenia
Przykład
blokady,
oczekiwanie na
zwolnienie blokady
Tabela A
Proces 1 Proces 2
Tabela B
Próba założenia
blokady,
oczekiwanie na
zwolnienie blokady
52
Rozwiązanie problemu zakleszczenia
" Blokowanie wszystkich obiektów na początku, usuwanie
blokad na zakończenie ciągu operacji
" Systemowe wykrywanie zakleszczenia (rozwiązanie blokad,
anulowanie procesów)
" Przeterminowanie blokady (nie wolno zablokować obiektu
na zbyt długo)
53
Transakcja
Niepodzielny ciąg operacji na danych, który może być
wykonany w całości albo wcale.
Przykład
Rezerwacja biletów lotniczych przy podróży z przesiadką:
Warszawa -> Paryż -> Nowy Jork
Transakcja
SET TRANSACTION READ WRITE
rezerwacja biletu Warszawa-Paryż
ciąg operacji
rezerwacja biletu Paryż-Nowy Jork
COMMIT albo ROLLBACK
Zatwierdzenie
Anulowanie
54
Cechy transakcji
" Niepodzielność  transakcja może być wykonana
w całości albo wcale
" Spójność  po wykonaniu transakcji, dane w bazie danych muszą
być spójne; transakcja nie może naruszyć reguł poprawności
(np. nie można przydzielić jednego miejsca w samolocie
dwóm osobom)
" Izolacja  transakcje powinny być tak wykonywane jak gdyby
były wykonywane po kolei, oddzielnie
" Trwałość  zakończenie transakcji oznacza, że wprowadzono
wynik do bazy danych (nawet gdyby wystąpiła awaria)
55
Przykład operacji bez użycia transakcji
Realizacja przelewu bankowego:
z konta 102030 na konto 400023 należy przelać kwotę 1000zł
krok 1: zmniejszyć stan konta 102030 o kwotę 1000zł
Awaria
krok 2: zwiększyć stan konta 400023 o kwotę 1000zł
Wynik: Konto 102030, zmniejszono saldo o kwotę 1000zł
Konta 400023 bez zmian (nie wpłynęły pieniądze)
Jak gdyby po drodze zgubiono pieniądze
56