2005 1

MS Access 2000

Paweł Górczyński

Normalizacja

2005 2

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 3

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

Proces normalizacji przeprowadzimy dla przykładu na tabeli Faktura, która jest modelem rzeczywistej faktury.

2005 4

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 5

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 6

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 7

Pierwsza postać normalna tabeli Faktura

2005 8

Pierwsza postać normalna tabeli Faktura

NumerFaktury NazwaOdbiorcy NIPOdbiorcy MiejscowoscOdbiorcyFK/00001/2001 Henio Sp. z o.o. 526-21-78-150 WarszawaFK/00001/2001 Henio Sp. z o.o. 526-21-78-150 WarszawaFK/00002/2001 GAZBUD S.A. 526-21-78-541 ŁódźFK/00003/2001 Henio Sp. z o.o. 526-21-78-150 Warszawa

UlicaOdbiorcy KodOdbiorcy DataSprzedaży SposobPlatnosciul. Modelowa 10 02-589 2001-11-10 Gotówkaul. Modelowa 10 02-589 2001-11-10 GotówkaAl. Testowa 2 05-202 2001-11-12 Przelewul. Modelowa 10 02-589 2001-11-13 Gotówka

NazwaTowaru Ilosc CenaNetto StawkaVatPapier toal. Różowy op. 10 6,50 7%Mydło szt. 20 1,30 7%Mydło szt. 1000 1,21 7%Papier toal. Różowy op. 10 6,50 7%

2005 9

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 10

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 11

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 12

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 Bultek Sp. Z o. O. 2000-11-09 gotówka kapusta 10 2 22%1/2000 Bultek Sp. Z o. O. 2000-11-09 gotówka ogórki 10 2 22%1/2002 Bultek Sp. Z o. O. 2000-11-09 gotówka ziemniaki 10 2 22%1/2002 Bultek Sp. Z o. O. 2000-11-09 gotówka kapusta 10 2 22%

2005 13

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 14

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.

A

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

2005 15

Zależność funkcjonalna pól tabeli Faktura

2005 16

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 17

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 18

Druga postać normalna tabeli Faktura

2005 19

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

A

B

B

C

2005 20

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 21

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 22

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 23

Diagram relacji

2005 24

Dane w tabeli Faktura

2005 25

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 26

Przykład wielowartościowej zależności funkcjonalnej

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.

Nazwisko Telefon MailKowalski 5462289 kowalski@firma.comKowalski 5462290 kowalski@firma.comKowalski 5462291 kowalski@firma.comMiauczyński 3568750 miau@onet.plMiauczyński 3568751 kotek@poczta.wp.plMiauczyński 3568750 kotek@poczta.wp.plMiauczyński 3568751 miau@onet.pl

Nazwisko Telefon MailKowalski 5462289 kowalski@firma.comKowalski 5462290 kowalski@firma.comKowalski 5462291 kowalski@firma.comMiauczyński 3568750 miau@onet.plMiauczyński 3568751 kotek@poczta.wp.plMiauczyński 3568750 kotek@poczta.wp.plMiauczyński 3568751 miau@onet.pl

2005 27

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 28

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 29

Przykład normalizacji tabeli do czwartej postaci

Nazwisko Telefon MailKowalski 5462289 kowalski@firma.comKowalski 5462290 kowalski@firma.comKowalski 5462291 kowalski@firma.comMiauczyński 3568750 miau@onet.plMiauczyński 3568751 kotek@poczta.wp.plMiauczyński 3568750 kotek@poczta.wp.plMiauczyński 3568751 miau@onet.pl

Nazwisko TelefonKowalski 5462289Kowalski 5462290Kowalski 5462291Miauczyński 3568750Miauczyński 3568751

Nazwisko MailKowalski kowalski@firma.comMiauczyński miau@onet.plMiauczyński kotek@poczta.wp.pl