Transcript 1_Normalizacja

MS Access 2000
Normalizacja
Paweł Górczyński
2005
1
Wstęp
Podstawowym celem procesu normalizacji jest
usunięcie redundancji (powtarzania się) danych.
Dzięki temu operacje związane z dodawaniem,
modyfikacją lub usuwaniem danych będą
ograniczone do minimum.
2005
2
Faktura











Numer faktury
Nazwa odbiorcy
NIP odbiorcy
Adres odbiorcy
Data sprzedaży
Sposób płatności
Nazwa towaru
Ilość
Cena netto
Wartość netto
Stawka VAT
2005
Proces normalizacji
przeprowadzimy dla
przykładu na tabeli
Faktura, która jest
modelem rzeczywistej
faktury.
3
Definicja: Pierwsza postać normalna
Tabela jest w pierwszej postaci normalnej, jeśli
wartości pól są elementarne.
O wartości elementarnej możemy myśleć jak o
najmniejszej informacji, którą możemy uzyskać z bazy
danych. Nie można uzyskać z bazy danych części
wartości elementarnej. Na przykład jeśli w tabeli
chcemy przechowywać imiona i nazwiska osób, i
będziemy chcieli wybierać z niej osoby tylko po
nazwisku, to znaczy, że musimy mieć w tabeli pole
‘Nazwisko’ i pole ‘Imię’, a nie jedno pole ‘Nazwisko i
Imię’.
2005
4
Pola elementarne 1











Numer faktury
Nazwa odbiorcy
NIP odbiorcy
Adres odbiorcy
Data sprzedaży
Sposób płatności
Nazwa towaru
Ilość
Cena netto
Wartość netto
Stawka VAT
•
•
•
Miejscowość odbiorcy
Ulica odbiorcy
Kod odbiorcy

Nie można uzyskać z bazy danych części wartości
pola. W tym przykładzie nie będzie można uzyskać
numerów ulic odbiorców.
2005
5
Pola elementarne 2
Pole Wartość netto zostanie usunięte z tabeli
ponieważ zawsze można je będzie obliczyć z
pozostałych pól (Ilość i Cena netto)
2005
6
Pierwsza postać normalna tabeli Faktura
2005
7
Pierwsza postać normalna tabeli Faktura
NumerFaktury
FK/00001/2001
FK/00001/2001
FK/00002/2001
FK/00003/2001
NazwaOdbiorcy
Henio Sp. z o.o.
Henio Sp. z o.o.
GAZBUD S.A.
Henio Sp. z o.o.
UlicaOdbiorcy
ul. Modelowa 10
ul. Modelowa 10
Al. Testowa 2
ul. Modelowa 10
NIPOdbiorcy
526-21-78-150
526-21-78-150
526-21-78-541
526-21-78-150
KodOdbiorcy
02-589
02-589
05-202
02-589
MiejscowoscOdbiorcy
Warszawa
Warszawa
Łódź
Warszawa
DataSprzedaży
2001-11-10
2001-11-10
2001-11-12
2001-11-13
NazwaTowaru
Papier toal. Różowy op.
Mydło szt.
Mydło szt.
Papier toal. Różowy op.
2005
8
SposobPlatnosci
Gotówka
Gotówka
Przelew
Gotówka
Ilosc CenaNetto StawkaVat
10
6,50
7%
20
1,30
7%
1000
1,21
7%
10
6,50
7%
Klucze
 Kluczem nazywamy minimalny zbiór pól, które
jednoznacznie identyfikują wszystkie rekordy w tabeli.
 Kluczem prostym nazywamy klucz składający się z
jednego pola.
 Kluczem złożonym nazywamy klucz składający się z
więcej niż jednego pola.
2005
9
Przykłady kluczy
 Imię, nazwisko, imię ojca, imię matki, nazwisko rodowe matki, data
urodzenia, miejsce urodzenia – klucz złożony identyfikujący osobę.
 NIP – klucz prosty identyfikujący podatnika.
 Numer albumu – klucz prosty identyfikujący studenta.
Numer PESEL niestety nie jest unikalny,
co okazało się podczas wprowadzania
reformy emerytalnej.
2005
10
Klucz główny
 Zdarza się, że w tabeli istnieje wiele kluczy.
Nazywamy je kluczami potencjalnymi.
 Kluczem głównym (primary key) nazywamy wybrany
klucz potencjalny, którym będziemy posługiwać się do
identyfikacji rekordów.
 Kluczami drugorzędnymi (secondary key)
nazywamy pozostałe klucze potencjalne.
2005
11
Które pole(-a) są kluczem?
Numer faktury Nazwa odbiorcy Data sprzedaży Sposób płatności Nazwa towaru Ilość Cena netto Stawka VAT
1/2000
Bultek Sp. Z o. O.
2000-11-09 gotówka
ziemniaki
10
2
22%
1/2000
1/2000
1/2002
1/2002
Bultek
Bultek
Bultek
Bultek
Sp.
Sp.
Sp.
Sp.
2005
Z o.
Z o.
Z o.
Z o.
O.
O.
O.
O.
2000-11-09
2000-11-09
2000-11-09
2000-11-09
gotówka
gotówka
gotówka
gotówka
12
kapusta
ogórki
ziemniaki
kapusta
10
10
10
10
2
2
2
2
22%
22%
22%
22%
Klucz główny w tabeli Faktura
Pole ‘Numer faktury’ i ‘Nazwa towaru’
jednoznacznie identyfikują każdy rekord w
tabeli faktura. Zakładamy przy tym, że na
jednej fakturze nie można umieścić dwóch
pozycji z tym samym towarem.
2005
13
Zależność funkcjonalna
Pole B tabeli jest funkcjonalnie zależne od pola A,
jeśli dowolnej wartości pola A odpowiada nie więcej
niż jedna wartość pola B. Mówimy, że A identyfikuje
B.
Wyobraźmy sobie tabele z polami
‘Numer albumu’, ‘Nazwisko’ i ‘Ocena’.
Pole ‘Nazwisko’ jest funkcjonalnie
zależne od pola ‘Numer albumu’ – wielu
studentów nie może posiadać albumu o
tym samym numerze. Ale pole ‘Ocena’
nie jest już funkcjonalnie zależne od
pola ‘Numer albumu’ – jeden student ma
zazwyczaj więcej niż jedną ocenę.
A
B
2005
14
Zależność funkcjonalna pól tabeli Faktura
2005
15
Zależność funkcjonalna pól tabeli Faktura
 NumerFaktury identyfikuje:
 DataSprzedazy
 SposobPlatnosci
 NIPOdbiorcy
 NIPOdbiorcy identyfikuje:
 NazwaOdbiorcy
 MiejscowoscOdbiorcy
 UlicaOdbiorcy
 KodOdbiorcy
 NazwaTowaru identyfikuje:
 StawkaVAT
 NumerFaktury i NazwaTowaru identyfikują:
 Ilosc
 CenaNetto
2005
16
Definicja: Druga postać normalna
Tabela jest w drugiej postaci normalnej, jeżeli każde
pole tabeli nie wchodzące w skład klucza jest
funkcjonalnie zależne od wszystkich pól klucza, a nie
ich części.
Z definicji wynika, że jeśli tabela w
pierwszej postaci normalnej ma klucz prosty,
to tabela jest od razu w drugiej postaci
normalnej.
Jeśli pole tabeli jest zależne od części pól
klucza tabeli, to pole to wraz z częścią
klucza staje się odrębna tabelą.
2005
17
Druga postać normalna tabeli Faktura
2005
18
Definicja: Trzecia postać normalna
Tabela jest w trzeciej postaci normalnej jeśli każde
pole nie wchodzące w skład klucza nie jest
funkcjonalnie zależne od innego pola nie
wchodzącego w skład klucza.
A
B
C
2005
19
A
B
B
C
Normalizacja tabeli Faktura do trzeciej
postaci
Pola ‘NazwaOdbiorcy’,’MiejscowoscOdbiorcy’, ‘UlicaOdbiorcy’ i ‘KodOdbiorcy’ są
funkcjonalnie zależne od pola ‘NIPOdbiorcy’, które nie wchodzi w skład klucza.
2005
20
Diagram relacji
 Relacja łączy pola Faktura.NIPOdbiorcy z Odbiorcy.NIPOdbiorcy
 Jest to relacja typu 1 do  (nieskończoności) oznaczająca, że dla 1 rekordu w
tabeli Odbiorcy o określonej wartości w polu Odbiorcy.NIPObiorcy, może
wystąpić nieskończenie wiele rekordów w tabeli Faktura o takiej samej wartości
w polu Faktura.NIPObiorcy
 Tabelę Odbiorcy nazywamy tabelą główną (master, parent), a tabelę Faktura
tabelą szczegółową (detail, child)
2005
21
Trzecia postać normalna tabeli Faktura
Tabela Faktura w wyniku
normalizacji została podzielona na
cztery tabele, które spełniają
definicję pierwszej, drugiej i
trzeciej postaci normalnej.
2005
22
Diagram relacji
2005
23
Dane w tabeli Faktura
2005
24
Wielowartościowa zależność funkcjonalna
Pole B jest wielowartościowo funkcjonalnie zależne
od pola A w tej samej tabeli, jeśli dowolne dwa
rekordy, w których wartości w polu A są równe, po
zamianie wartości w polu B będą również należeć do
tabeli.
Wielowartościowa zależność funkcjonalna występuje w
tabeli z co najmniej trzema polami A, B, C, gdzie dla każdej
wartości A istnieje dobrze zdefiniowany zestaw wartości B i
oddzielnie istnieje dobrze zdefiniowany zestaw wartości C,
jednak zestaw wartości B jest niezależny od zestawu
wartości C.
2005
25
Przykład wielowartościowej zależności
funkcjonalnej
Nazwisko
Kowalski
Kowalski
Kowalski
Miauczyński
Miauczyński
Miauczyński
Miauczyński
Telefon
5462289
5462290
5462291
3568750
3568751
3568750
3568751
Mail
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Nazwisko
Kowalski
Kowalski
Kowalski
Miauczyński
Miauczyński
Miauczyński
Miauczyński
Telefon
5462289
5462290
5462291
3568750
3568751
3568750
3568751
Mail
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
W tabeli zapisane są osoby w polu ‘Nazwisko’,
każda osoba może mieć kilka telefonów w polu
‘Telefon’ i adresów poczty w polu ‘Mail’. Widać, że
dwa wybrane rekordy pana Miauczyńskiego po
zamianie numeru telefonu także znajdują się w tabeli.
Zestawy wartości w polach ‘Telefon’ i ‘Mail’ są
dobrze określone dla wartości w polu ‘Nazwisko’,
ale są niezależne od siebie.
2005
26
Trywialna wielowartościowa zależność
funkcjonalna
Dla tabel składających się z dwóch pól A i B, jeśli
pole A jest wielowartościowo funkcjonalnie zależne
od pola B, to pole B jest wielowartościowo
funkcjonalnie zależne od pola A. Zależność taką
nazywamy trywialną wielowartościową zależnością
funkcjonalną.
2005
27
Definicja: Czwarta postać normalna
Tabela jest w czwartej postaci normalnej jeśli zawiera
co najwyżej jedną trywialną wielowartościową
zależność funkcjonalną.
Z definicji wynika, że tabele, które
mają więcej niż jedną
wielowartościową zależność
funkcjonalną, muszą ulec podzieleniu
w ten sposób, by zawierały tylko
zależności trywialne.
2005
28
Przykład normalizacji tabeli do czwartej
postaci
Nazwisko
Kowalski
Kowalski
Kowalski
Miauczyński
Miauczyński
Miauczyński
Miauczyński
Nazwisko
Kowalski
Kowalski
Kowalski
Miauczyński
Miauczyński
Telefon
5462289
5462290
5462291
3568750
3568751
2005
Telefon
5462289
5462290
5462291
3568750
3568751
3568750
3568751
Mail
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Nazwisko
Kowalski
Miauczyński
Miauczyński
29
Mail
[email protected]
[email protected]
[email protected]