Normalizacja bazy danych.
Powiązania. Integralność baz
danych

Piotr Piskozub

WYKŁAD

Informatyka– Semestr I

Model relacyjny

Systemy relacyjnych baz danych charakteryzują się następującymi właściwościami:
• Wszystkie dane są reprezentowane koncepcyjnie jako zbiór wartości
uporządkowanych w formie wierszy i kolumn zwanych

relacją

.

• Wszystkie

wartości

są

skalarne

. Oznacza to, że dowolna pozycja relacji na

przecięciu kolumny i wiersza zawiera zawsze jedną i tylko jedną wartość.
• Wszystkie operacje są wykonywane na relacji, a ich wynikiem jest również relacja.
Taka właściwość operacji nazywana jest

domknięciem

.

Liczba krotek w relacji określa jej

moc

, a liczba atrybutów relacji określa jej

stopień

.

CZYTELNICY

NrKart

y

Nazwisko

Imie

Ucze

n

NrMiejscowos

ci

RokUrodzen

ia

DataZapis

u

1

Kowalski

Jan

Nie

2

1967

2000-11-24

2

Nowakows

ka

Anna

Tak

1

1975

1999-04-17

3

Kotecki

Michał

Tak

2

1977

2000-11-24

Model danych – Podstawowe
elementy

Encja

– coś, o czym system musi przechowywać informację.

CZYTELNICY

wypożyczają

KSIĄŻKI

.

WYDAWNICTWA

wydają

KSIĄŻKI

.

Atrybut

– fakt dotyczący encji.

CZYTELNICY

– Nazwisko, Imię, Adres zamieszkania, Data urodzin

KSIĄŻKI

– Tytuł, Liczba stron, Cena, Rok wydania

Domena

– Zbiór wszystkich możliwych wartości jakie może przyjmować atrybut.

KSIĄŻKI.Cena

– Liczba rzeczywista większa od zera lub równa zero

CZYTELNICY.MiesiącUr

– Trzy literowe ciągi znaków (STY, LUT, MAR itd..)

Powiązania

– Związki łączące encje ze sobą.

CZYTELNICY

wypożyczają

KSIĄŻKI.

– Związek między encjami

CZYTELNICY

i

KSIĄŻKI

Model danych – Diagram E/R

Model powiązań między encjami

(ang. Entity Relationship – E/R) opisuje dane

w terminach encji, atrybutów i powiązań za pomocą diagramów E/R.

W diagramach E/R na oznaczenie encji stosuje się prostokąty, atrybutów – elipsy,

a powiązań – romby.

CZYTELNICY

NrKarty

Nazwisko

Telefon

Czytelnicy

dokonują

wypożyczń

WYPOŻYCZENIA

DataZwrotu

NrKarty

jeden

wiele

zero lub jeden

Normalizacja

Normalizacja

ma na celu uporządkowanie struktury danych, tak aby mogła ona

w sposób efektywnych udzielić odpowiedzi na poprawnie sformułowane zapytania.

Postacie normalne

nie są przepisami na tworzenie „poprawnych” modeli danych.

Jeżeli jednak model danych jest znormalizowany – to znaczy zgodny z regułami
struktury relacyjnej – istnieje duża szansa, że będzie to efektywny i wydajny
model danych.

NrWy

p

Nazwisko

Data

Zapisu

Tytuł Książki

Nazwa

Wydawnict

wa

Data

Wypożyczen

ia

Telefon

Wydawnictw

a

1

Kowalski

1999-12-

02

Access 2000

Helion

2000-11-24

45093992

2

Nowakows

ka

1998-04-

12

Bazy danych

RM

1999-04-17

48598499

3

Majewski

1998-01-

20

C++ Builder

3.0

WNT

2001-02-02

29048933

4

Majewski

1998-01-

20

Access 2000

RM

2001-07-20

48598499

5

Nowakows

ka

1997-04-

12

Podstawy

Delphi

Helion

2000-11-24

45093992

Bezstratna dekompozycja

Bezstratna dekompozycja

polega na podziale relacji w taki sposób, aby uzyskane

w efekcie relacje mogły być znowu połączone bez straty żadnej informacji.

NrAutora

Nazwisko

Imie

NazwaKraju

SymbolKraju

1

Liberty

Jesse

Stany Zjednoczone

USA

2

Mickiewicz

Adam

Polska

PL

3

Reisdorph

Kent

Stany Zjednoczone

USA

4

Greene

Joe

Stany Zjednoczone

USA

5

Sienkiewicz

Henryk

Polska

PL

AUTORZY

NrAutora

Nazwisk

o

Imie

NrKraj

u

1

Liberty

Jesse

1

2

Liberty

Adam

2

3

Reisdorph

Kent

1

4

Greene

Joe

1

5

Sienkiewi

cz

Henry

k

2

KRAJE

NrKraj

u

NazwaKraju

SymbolKraj

u

1

Stany

Zjednoczone

USA

2

Polska

PL

Klucze kandydujące i klucze
główne

Kluczem kandydującym

nazywamy kombinację atrybutów relacji jednoznacznie

identyfikujących każdą jej krotkę.
Klucz składający się z pojedynczego atrybutu nazywamy

kluczem prostym

, a klucz

składający się z wielu atrybutów nazywamy

kluczem złożonym

.

Jeżeli w relacji występuje wiele kluczy kandydujących, to jeden z nich określa się
mianem

klucza głównego

, a pozostałe jako

klucze alternatywne

.

CZYTELNICY

NrKart

y

Nazwisko

Imie

DataZapis

u

Telefon

NrMiejscowos

ci

PESEL

1

Kowalski

Jan

2000-11-24

328929

1

197401139

34

2

Nowakows

ka

Anna

1999-04-17

328981

2

196901203

90

3

Majewski

Jan

2001-01-20

289893

1

197802029

32

4

Kotecki

Michał

2000-11-24

289822

1

197411240

29

Klucze kandydujące

Zależność funkcjonalna

Niech dana będzie relacja R i dwa zbiory jej atrybutów X = {X1, ..., Xn} i
Y = {Y1, ..., Ym} (nie muszą być to zbiory wzajemnie rozłączne}. Mówimy, że zbiór
Y jest

funkcjonalnie zależny

od zbioru X, jeśli dla każdego poprawnego elementu

zbioru X istnieje tylko jeden poprawny element zbioru Y.
Co oznacza, że istnieje pewien zbiór atrybutów unikatowy dla każdej krotki
i znajomość tych atrybutów pozwala ustalić pozostałe, nie unikatowe atrybuty.

CZYTELNICY

NrKart

y

Nazwisko

Imie

DataZapis

u

Telefon

NrMiejscowos

ci

PESEL

1

Kowalski

Jan

2000-11-24

328929

1

197401139

34

2

Nowakows

ka

Anna

1999-04-17

328981

2

196901203

90

3

Majewski

Jan

2001-01-20

289893

1

197802029

32

4

Kotecki

Michał

2000-11-24

289822

1

197411240

29

X = {PESEL}, Y = {NrKarty, Nazwisko, Imie, DataZapisu,
Telefon, NrMiejscowosci}
Mówimy, że PESEL określa funkcjonalnie atrybuty
NrKarty, Nazwisko, Imie, DataZapisu, Telefon oraz NrMiejscowosci.

Pierwsza postać normalna

Relacja jest w pierwszej postaci normalnej

, jeśli domeny zdefiniowane dla jej

atrybutów są skalarne. Oznacza to, że każdy atrybut krotki musi zawierać
pojedynczą wartość.

POWIATY

NrPowiat

u

NazwaPowia

tu

Miejscowości

1

Nowosolski

67-100 Nowa Sól, 67-120 Kożuchów, 67-160 Otyń

2

Zielonogórski

65-560 Zielona Góra, 65-250 Ochla

Przykład 1:

Przykład 2:

POWIATY

NrPowiat

u

NazwaPowia

tu

Kod1

M1

Kod2

M2

Kod3

M3

1

Nowosolski

67-
100

Nowa Sól

67-

120

Kożuchów

67-
160

Otyń

2

Zielonogórski

65-

560

Zielona Góra

65-
250

Ochla

Jak reprezentować daty?
Czy tworzyć atrybuty bitowe?

Druga postać normalna

Relacja jest w drugiej postaci normalnej

, jeśli jest w pierwszej postaci normalnej,

a ponadto wszystkie jej atrybuty zależą od całego klucza kandydującego.
Zatem warunek ten oznacza, aby nie reprezentować dwóch różnych encji (Książki
i Wydawnictwa) w ramach jednej relacji.

KSIAZKI

NrKatego

rii

Tytul

NazwaWydawnict

wa

IloscStro

n

TelefonWydawnictw

a

3

SQL – Księga eksperta

HELION

879

2312219

7

Gramatyka angielska

PWN

354

2389839

Uwagi:

- Atrybut TelefonWydawnictwa zależy tylko od atrybutu
NazwaWydawnictwa, a nie
od całego klucza złożonego. Podobnie atrybut IloscStron zależy tylko od
atrybutu
Tytuł.

- Niemożliwe jest zapisanie jakichkolwiek informacji o wydawcach, bez
podania
informacji o książce, gdyż żaden ze składników klucza głównego nie
może być
pusty.

Access 2000 - Typy danych

Typ danych

Opis

Rozmiar

Tekst

Typ domyślny. Ciąg znaków i cyfr lub tylko cyfr, które nie

są wykorzystywane w obliczeniach.

Do 255 znaków

Nota

Łańcuch znaków o dużej liczbie znaków.

Do 65 535

znaków.

Liczba

Dane numeryczne poddawane obliczeniom

matematycznym

1, 2, 4, 8, 16

bajtów

Data/Godzina

Data i czas

8 bajtów

Walutowy

Typ walutowy. Dokładność obliczeń do czterech miejsc po

przecinku.

8 bajtów

Autonumerowanie

Unikalna liczba całkowita generowana sekwencyjnie lub

losowo przez Microsoft Access wówczas, gdy do tabeli

dodawany jest nowy rekord. Nie podlega aktualizacji.

4 lub 16 bajtów

Tak/Nie

Typ danych do przechowywania jednej z dwóch logicznych

wartości

1 bit

Obiekt OLE

Wskazanie na obiekt lub obiekt osadzony (arkusz

kalkulacyjny Microsoft Excel, dokument programu

Microsoft Word, dźwięk, dane binarne)

Do 1GB

Hiperłącze

Adres hiperłącza

do 3 razy 2048

Access 2000 - Właściwości
atrybutów

Właściwość pola

Zastosowanie

Rozmiar pola

Ustawienie rozmiaru pola typu Tekst lub ograniczenie zakresu

dopuszczalnych wartości w polu typu liczba

Format

Wyświetlanie dat i liczb w pewnym formacie, takim jak format daty „Data

krótka”

Miejsca dziesiętne

Wyświetlanie określonej liczby miejsc po przecinku w przypadku

zastosowania formatu dla pola typu Liczba lub Waluta

Maska

wprowadzenia

Wyświetlanie znaków formatujących w polu, aby było zbędne ich

wprowadzanie

i zapewnienie, że wprowadzane dane pasują do maski

Tytuł

Określenie etykiet innych niż domyślne (nazwa pola) dla nowych formularzy

i raportów

Wartość domyślna

Automatyczne wpisywanie pewnej wartości do pola w nowych rekordach

dodawanych do tabeli

Reguła

poprawności
Komunikat o

błędzie

Ograniczenie danych wprowadzanych do pola do wartości spełniających

pewne wymagania

Wymagane

Wymaganie wprowadzenia danych do pola

Zerowa długość?

Dopuszczenie przechowywania ciągów znaków o zerowej długości w polach

typu Tekst i Nota

Indeksowanie

Przyspieszenie wyszukiwania w często poszukiwanych polach oraz

przyspieszenie sortowania. Może jednak spowodować spowolnienie

aktualizacji danych

Access 2000 – Maski
wprowadzania (1)

Przeznaczenie masek wprowadzania danych

:

• ułatwienie i ujednolicenie wprowadzania danych;

• zapewnienie zgodności wprowadzanych danych ze
zdefiniowanym formatem oraz
typem danych,

• ochrona poufnych informacji.

Uwaga

:

• Maska wprowadzenie utworzona dla pola w tabeli jest także
stosowana dla tego
pola w zapytaniach, formularzach i raportach, w których
wykorzystano tą tabelę.

• Jeśli zostanie ustawiona właściwość „Format” dla pola w tabeli,
w zapytaniu lub
elementu sterującego w formularzu lub raporcie, do
wyświetlania danych w polu
zostanie wykorzystany określony format, a nie maska
wprowadzania. Jeśli jednak
pole jest edytowane w formularzu, wyświetlana jest maska
wprowadzania, a nie
określony format.

Sekcje maski wprowadzania

: [phone];0;” ”

Access 2000 – Maski
wprowadzania (2)

Znak maski

Interpretacja

0

Tylko cyfra (wprowadzanie wymagane)

9

Tylko cyfra (wprowadzanie nie wymagane)

#

Cyfra, znak + lub – albo spacja (wprowadzanie nie wymagane:

puste miejsca są przekształcane na spacje)

L

Litera (wprowadzanie wymagane)

?

Litera (wprowadzanie nie wymagane)

A

Litera lub cyfra (wprowadzanie wymagane)

a

Litera lub cyfra (wprowadzanie nie wymagane)

&

Dowolny znak lub spacja (wprowadzanie wymagane)

C

Dowolny znak lub spacja (wprowadzanie nie wymagane)

.,:;-/

Przecinek dziesiętny, separator tysięcy, daty lub godziny

(zależny od lokalnych ustawień systemowych)

<

Powoduje przekształcenie znaków po prawej stronie na małe

litery

>

Powoduje przekształcenie znaków po prawej stronie na wielkie

litery

Access 2000 – Maski
wprowadzania (3)

Definicja maski

wprowadzania

Przykład maski wprowadzania

[phone]

[phone]

(000) AAA-AAAA

(206) 555-TELE

00000-9999

98115-

>L<??????????????

Tomasz

ISBN 0-&&&&&&&&&-0

ISBN 1-55615-507-7

Definiowanie powiązań między
relacjami

Definiowanie relacji między tabelami

Uwagi ogólne:

• Przed rozpoczęciem modyfikowania relacji należy zamknąć
wszystkie otwarte
tabele;

• Relacje mogą być tworzone dla tabel i kwerend, ale w
przypadku kwerend nie są
wymuszane więzy integralności;

Aby zdefiniować relację
należy:

• określić tabele i pola
pomiędzy
którymi definiowana jest
relacja,

• określić typ relacji,

• określić typ sprzężenia,

• zdefiniować, czy dla
tworzonej
relacji mają być
wymuszane
więzy integralności.

Właściwości relacji – typ relacji

Typ relacji ustalany jest automatycznie na podstawie właściwości
pól, które są łączone relacją na etapie jej tworzenia. Typ relacji
wyświetlany jest w oknie edycji relacji.

Możliwe są do zdefiniowania następujące typy relacji:

• relacja

Jeden–do-wielu

oznacza, że jedno z powiązanych pól

jest kluczem
podstawowym lub ma unikatowy indeks,

• relacja

Jeden-do-jednego

oznacza, że oba pola powiązane

relacją są kluczem
podstawowym lub mają unikatowy indeks,

• relacja

Nieokreślona

oznacza, że żadne z pól nie jest kluczem

podstawowym
ani nie ma unikatowego indeksu. Aby zapobiec relacjom
nieokreślonym, należy
zdefiniować przynajmniej jeden klucz podstawowy lub indeks
unikatowy dla
powiązanych pól.

Uwaga:

Symbole określające typ relacji wyświetlane są dla

danej relacji tylko
wtedy, gdy więzy integralności są wymuszane.

Właściwości relacji – typ
sprzężenia

Sprzężenie jest to wiązanie między polem w jednej tabeli lub
kwerendzie a polem o tym samym typie danych w innej tabeli lub
kwerendzie. Sprzężenie stanowi dla programu Microsoft Access
informację o sposobie powiązania danych.
Typ sprzężenia wskazuje, które rekordy wybiera kwerenda lub
jaką akcję na nich przeprowadza.

Kiedy do kwerendy dodawane są
tabele, które mają już zdefiniowane
relacje, program Access
automatycznie tworzy sprzężenie
wewnętrzne. Jeżeli relacje nie były
uprzednio zdefiniowane, program
automatycznie tworzy sprzężenie
wewnętrzne między polami, które
mają takie same nazwy i typy
danych, pod warunkiem, że jedno z
pól jest kluczem głównym.
Utworzenie sprzężenia
w widoku projektu kwerendy nie
jest równoważne z utworzeniem
trwałej relacji..

Właściwości relacji – typ
sprzężenia

Sprzężenie wewnętrzne – domyślny typ sprzężenia

KLIENCI

PESEL

Nazwisko

00000000001

Nowak

00000000002 Kowalski

00000000003 Marciniszyn

ZAMÓWIENIA

PESEL

Ilość IDTowaru

00000000001

5

5

00000000003

2

2

00000000001

10

5

00000000001

2

1

ZAMÓWIENIA KLIENTÓW: Kwerenda

wybierająca

Nazwisko

PESEL

Ilość IDTowaru

Nowak

00000000001

5

5

Marciniszyn 00000000003

2

2

Nowak

00000000001 10

5

Nowak

00000000001

2

1

Domyślny typ sprzężenia powoduje wybranie ze sprzężonych tabel lub kwerend tylko tych rekordów, które
zawierają te same wartości w sprzężonych polach

Właściwości relacji – typ
sprzężenia

Lewo i prawostronne sprzężenie zewnętrzne

KLIENCI

PESEL

Nazwisko

00000000001

Nowak

00000000002 Kowalski

00000000003 Marciniszyn

ZAMÓWIENIA

PESEL

Ilość IDTowaru

00000000001

5

5

00000000003

2

2

00000000001

10

5

00000000001

2

1

ZAMÓWIENIA KLIENTÓW: Kwerenda

wybierająca

Nazwisko

PESEL

Ilość IDTowaru

Nowak

00000000001

5

5

Kowalski 00000000002

Marciniszyn 00000000003

2

2

Nowak

00000000001 10

5

Nowak

00000000001

2

1

Sprzężenia zewnętrzne powodują wybór
przez kwerendę wszystkich rekordów z
danej tabeli bez względu na to czy mają
one odpowiadające rekordy w innej
tabeli. Kiedy dla rekordów z tabeli, nie
można znaleźć odpowiadającego rekordu
w innej tabeli kwerenda wyświetla puste
komórki w tych miejscach.

Właściwości relacji – więzy
integralności

Więzy integralności jest to system zasad, które w programie
Microsoft Access są stosowane aby zapewnić, że relacje pomiędzy
powiązanymi tabelami są prawidłowe
i że powiązane rekordy nie zostaną przypadkowo usunięte lub
zmienione.Wymuszanie więzów integralności możliwe jest tylko,

wtedy, gdy spełnione są wszystkie następujące
warunki:

• obie tabele są przechowywane w tej samej bazie
programu Access,

• powiązane pola są tego samego typu danych,

• wiązane pole z tabeli podstawowej jest kluczem
podstawowym
(kluczem głównym) lub ma unikatowy indeks.

Uwaga:

Więzów integralności nie można wymusić dla tabel

przyłączonych z baz danych innego formatu.

Właściwości relacji – więzy
integralności

Więzy integralności narzucają następujące reguły
postępowania:

• w polu

klucza obcego

w tabeli powiązanej nie można

wprowadzić wartości nie
istniejących w polu klucza podstawowego tabeli podstawowej.
Można jednak
w polu klucza obcego wprowadzić wartość

Null

oznaczającą,

że rekord nie jest
powiązany;

• w tabeli podstawowej nie można usunąć rekordu jeżeli
istnieją powiązane
z nim rekordy w tabeli sprzężonej,

• w tabeli podstawowej nie można zmienić wartości klucza
podstawowego, jeżeli
istnieją powiązane z nim rekordy.

Zapamiętaj:

Aby dla relacji obowiązywały powyższe reguły

należy podczas jej tworzenia zaznaczyć pole wyboru „Wymuszaj
więzy integralności”.

Właściwości relacji – więzy
integralności

Kaskadowe aktualizowanie pól pokrewnych

 Zaznaczenie pola wyboru powoduje automatyczną aktualizację

odpowiadających
sobie wartości w powiązanej tabeli po każdej zmianie wartości
klucza
podstawowego w tabeli podstawowej

 Odznaczenie pola wyboru blokuje możliwość zmian wartości

klucza
podstawowego w tabeli podstawowej jeżeli występują w tabeli
powiązanej
powiązane rekordy

KLIENCI

PESEL

Nazwisko

00000000001

Nowak

00000000002

Potocka

00000000003 Kowalski

00000000004 Marciniszyn
00000000005 Majewska
00000000006 Kotewicz

ZAMÓWIENIA

PESEL

Ilość IDTowaru

00000000002

30

2

00000000003

5

5

00000000003

2

2

00000000001

10

4

00000000005

2

1

00000000003

7

9

Właściwości relacji – więzy
integralności

Kaskadowe usuwanie powiązanych rekordów

 Zaznaczenie pola wyboru powoduje automatyczne usuwanie

powiązanych
rekordów w tabeli powiązanej po każdym usunięciu rekordu z
tabeli podstawowej

 Odznaczenie pola wyboru blokuje możliwość usunięcia

rekordów z tabeli
podstawowej, jeżeli w tabeli powiązanej występują rekordy
powiązane

ZAMÓWIENIA

PESEL

Ilość IDTowaru

00000000002

30

2

00000000003

5

5

00000000003

2

2

00000000001

10

4

00000000005

2

1

00000000003

7

9