Projektowanie bazy danych - przykład
Projektowanie bazy danych – przykład
Pierwszą fazą tworzenia projektu bazy danych jest postawienie definicji celu, założeń wstępnych i
określenie podstawowych funkcji aplikacji. Każda baza danych jest projektowana w jakimś celu w
oparciu o pewne założenia. Na początku projektu bardzo ważne również jest określenie
podstawowych funkcji systemu bazy danych. Oprócz definicji celu należy również sformułować
założenia wstępne. Są to wymagania jakie powinny spełniać dane przechowywane w bazie danych.
Definicja celu
Celem jakiemu ma służyć projektowana baza danych może być rejestracja zamówień klientów,
wstępne planowanie produkcji, bilansowanie mocy produkcyjnych i obliczanie zapotrzebowania na
materiały do produkcji wyrobów.
Założenia wstępne
Poniżej podano następujące założenia wstępne do systemu planowania produkcji seryjnej wyrobów
prostych:
• producent ma wielu klientów stałych i okazyjnych,
• klienci zamawiając wyroby składają zamówienia,
• pojedyncze zamówienie może dotyczyć wielu wyrobów,
• wyroby wykonuje się w centrach roboczych (np. wtryskarka do tworzyw, maszyna do szycia,
mieszalnik do artykułów spożywczych itp.),
• dla każdego wyrobu jest określony czas przygotowawczo-zakończeniowy, czas jednostkowy
wykonania i centrum robocze w jakim należy wykonać daną operację technologiczną,
• gotowe wyroby przekazuje się do magazynu wyrobów,
• na podstawie zamówień z uwzględnieniem stanu magazynu wyrobów tworzy się wstępny plan
okresowy (np. miesięczny),
• następnie bilansuje się obciążenie centrów (stanowisk) roboczych,
• w przypadku wolnych mocy produkcyjnych uzupełnia się plan własnymi zamówieniami,
• po zbilansowaniu planu produkcji określa się terminy dostaw poszczególnych materiałów i
surowców potrzebnych do realizacji planu, czyli wytworzenia zaplanowanych ilości
określonych wyrobów,
• następnie po zbilansowaniu stanów magazynowych materiałów wysyła się zapotrzebowanie na
materiały do dostawców,
• każdy dostawca dostarcza określoną grupę materiałów,
• materiały przechowuje się w magazynie materiałów.
Definiowanie funkcji systemu baz danych
Już na etapie projektowania bazy danych należy określić podstawowe funkcje systemu baz danych.
W przykładowym systemie planowania produkcji mogą to być następujące funkcje:
• wprowadzanie danych ogólnych (o wyrobach i materiałach z jakich są wytworzone, czasach
technologicznych wykonania wyrobów itp.),
• wprowadzanie danych o klientach,
• wprowadzanie danych o zamówieniach,
• wspomaganie tworzenia wstępnego planu,
• wyszukiwanie wszystkich zamówień klienta,
• wyszukanie wszystkich klientów, którzy zamówili dany towar,
• bilansowanie mocy produkcyjnych,
• obliczanie dziennego zapotrzebowania na materiały i surowce do produkcji,
• generowanie zapotrzebowania na materiały z podziałem na dostawców.
1
Projektowanie bazy danych - przykład
Budowanie diagramu związków między relacjami
Metoda przedstawiania związków między relacjami (tabelami) za pomocą diagramu jest bardzo
wygodna i znacznie usprawnia proces tworzenia bazy danych. Na diagramie (schemacie) widać od
razu wszystkie tabele i powiązania między nimi. Zadanie to najlepiej wykonać w kilku
następujących krokach:
• Identyfikacja zbioru obiektów występujących w danym problemie.
• Identyfikacja powiązań bezpośrednich między obiektami i przekształcenie w pojęciowy model
danych (ustalenie typu relacji).
• Określenie atrybutów kluczowych i pozostałych atrybutów dla wszystkich obiektów.
Normalizacja do pierwszej (rozbicie atrybutów wielowartościowych na jednowartościowe) i
drugiej postaci normalnej (rozbicie tabel na dwie lub więcej w celu uniknięcia redundancji
danych w tabeli).
• Normalizacja do trzeciej postaci normalnej, przekształcenie relacji typu m : n na powiązania
typu 1 : n.
• Sprawdzenie poprawności struktury bazy danych poprzez porównanie jej struktury z
wymaganiami względem systemu bazy danych.
Identyfikacja bezpośrednich zależności między obiektami
Po wyróżnieniu obiektów w systemie należy zidentyfikować wszystkie powiązania występujące
między nimi.
Pojęciowy model danych
Na podstawie identyfikacji bezpośrednich zależności między obiektami możemy utworzyć diagram
zależności między obiektami przyszłej bazy danych. Rodzaj relacji należy ustalić na podstawie
założeń wstępnych i funkcji aplikacji. Rozważmy kolejno przykłady relacji:
• relacja między klientami, a złożonymi zamówieniami jest typu jeden do wielu (1 : n), ponieważ
każdy klient może złożyć wiele zamówień, natomiast każde zamówienie należy tylko do
jednego klienta,
• relacja między wyrobami, a zamówieniami jest typu wiele do wielu (m : n), ponieważ na
zamówieniu może być wiele wyrobów i na każdy wyrób może być wiele zamówień,
• relacja między wyrobami, a planami jest typu m : n, ponieważ do każdego planu należy wiele
wyrobów i każdy wyrób może wystąpić w wielu planach,
• relacja między centrami, a wyrobami jest typu m : n, ponieważ każdy wyrób może być
wykonywany na kilku centrach (maszynach) i na każdej maszynie można wykonać wiele
różnych wyrobów,
• relacja między wyrobami, a materiałami jest typu m : n, ponieważ na każdy wyrób może się
składać kilka materiałów oraz ten sam materiał może wchodzić w skład kilku wyrobów,
• relacja między dostawcami, a materiałami jest typu 1 : n, ponieważ każdy dostawca dostarcza
określone materiały, a każdy materiał jest dostarczany tylko przez jednego dostawcę.
Rysunek 1. Początkowy diagram zależności między obiektami
2
Projektowanie bazy danych - przykład
Przekształcenie powiązań typu wiele do wiele.
Każde z powiązań typu m : n, ze względu na późniejszą implementację, powinno zostać rozdzielone
na dwa powiązania typu jeden do wielu 1 : n. Operację tę przeprowadza się wg schematu
zilustrowanego na diagramie poniżej.
Rysunek 2. Schemat zastępowania relacji m : n przez dwa powiązania typu 1: n
Po zastąpieniu wszystkich powiązań typu m : n z diagramu na rys.1 otrzymujemy diagram
docelowy (rys.2), z dodatkowymi obiektami. Jak można zauważyć, na diagramie tym występują
tylko relacje 1: n. Taka struktura zależności w bazie danych jest prawidłowa i nadaje się do dalszej
analizy tj. ustalenia atrybutów i kluczy co będzie wykonane w następnym punkcie.
Rysunek 3.Docelowy diagram zależności między obiektami
Określenie atrybutów
Biorąc pod uwagę założenia wstępne i funkcje jakie ma pełnić przyszły system baz danych można
określić atrybuty dla wszystkich relacji (obiektów) z diagramu docelowego ( poniższe tabele).
Oprócz nazw atrybutów zostaną określone ich domeny, czyli praktycznie cała struktura tabel bazy
danych.
Tabela Materiały
Nazwa atrybutu
Rodzaj atrybutu
Znaczenie
Domena
Id_Materialu Klucz
Identyfikator
materiału Integer
Nazwa_Materialu
Nazwa
materiału Char(32)
Jednostka
Jednostka miary materiału Char(5)
Ilosc_Min
Min
ilość dostawy
Integer
Ilosc_Min_Mag
Min
ilość w magazynie
Integer
3
Projektowanie bazy danych - przykład
Tabela Dostawcy
Nazwa atrybutu
Rodzaj atrybutu
Znaczenie
Domena
Id_Dostawcy Klucz
Identyfikator
dostawcy
Integer
Firma
Nazwa skrócona klienta
Char(20)
NIP
Nr identyfikacji podatkowej
Char(13)
Adres Atrybut
wielowartościowy Adres klienta
Char( 100)
Telefon
Telefon do klienta
Char(15)
Bank
Nazwa
banku
Char(40)
Konto
Konto
bankowe
Char(40)
Nazwa_Dostawcy
Nazwa
dostawcy
Char(80)
Tabela Specyfikacja
Nazwa atrybutu
Rodzaj atrybutu
Znaczenie
Domena
Id_Wyrobu Klucz
Identyfikator
towaru
Integer
Id_Materialu Klucz
Identyfikator
materiału Integer
Ilosc
Ilość materiału na wyrób
Decimal(9,3)
Tabela Wyroby
Nazwa atrybutu
Rodzaj atrybutu
Znaczenie
Domena
Id_Wyrobu Klucz
Identyfikator
towaru
Integer
Nazwa_Wyrobu
Nazwa
wyrobu
Char(32)
Jednostka
Jednostka miary wyrobu
Char(5)
Ilość_Min
Min
ilość wyrobów w produkcji Integer
Ilość_ Min_Mag
Min ilość w magazynie
Integer
Tabela Zamówienia
Nazwa atrybutu
Rodzaj atrybutu
Znaczenie
Domena
Id_Zamowienia Klucz
Nr
zamówienia
Integer
Id_Klienta
Klucz obcy
Identyfikator klienta
Integer
Data_Zamowienia
Data
zamówienia
Data
Data_Dostawy
Data dostawy
Data
Transport
Rodzaj
transportu
Char(20)
Tabela Zamowienia_Pozycje
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Zamowienia Składnik klucza, klucz obcy Nr zamówienia
Integer
Pozycja Składnik klucza
Nr pozycji na zamówieniu
Integer
Id_Wyrobu
Klucz obcy
Identyfikator wyrobu
Integer
Ilosc
Ilość zamówiona towaru
Decimal(12,3)
Cena
Cena netto towaru
Decimal(12,2)
VAT
Podatek
VAT
Decimal(12,2)
Tabela Klienci
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Klienta Klucz
Identyfikator
klienta
Integer
Firma
Nazwa skrócona klienta
Char(20)
NIP
Nr identyfikacji podatkowej
Char(13)
Adres Atrybut
wielowartościowy Adres
klienta
Char(100)
Telefon
Telefon do klienta
Char(15)
4
Projektowanie bazy danych - przykład
Bank
Nazwa
banku
Char(40)
Konto
Konto
bankowe
Char(40)
Nazwa_Klienta
Nazwa
klienta
Char(80)
Tabela Centra
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Centrum
Klucz
Identyfikator centrum wykonawczego Integer
Nazwa_Centrum
Nazwa centrum wykonawczego
Char(32)
Tabela Plany
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Planu Klucz
Identyfikator
planu
Integer
Nazwa_Planu
Nazwa
planu
Char(32)
Data_Od
Data
początku planu
Date
Data_Do
Data
końca planu
Date
Tabela Specyfikacja_Planu
Nazwa atrybutu Rodzaj atrybutu Znaczenie
Domena
Id_Planu Klucz
Identyfikator
planu
Integer
Id_Partii
Klucz
Identyfikator partii produkcyjnej wyrobów Integer
Id_Wyrobu Klucz
Identyfikator
wyrobu
Integer
Ilosc_Partii
Ilość wyrobów w partii
Integer
Termin
Termin wykonania partii
Date
Tabela Technologia
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Wyrobu
Klucz, Klucz obcy
Identyfikator wyrobu
Integer
Id_Operacji
Klucz
Identyfikator operacji technologicznej Integer
Nazwa_Operacji
Nazwa operacji technologicznej
Char(32)
Id_Centrum
Klucz obcy
Identyfikator centrum wykonawczego Integer
Nazwa_Techn
Nazwa
technologii
Char(32)
Czas_Techn
Czas techniczny [min]
Integer
Czas Jedn
Czas jednostkowy [min]
Integer
Sprawdzenie kryteriów normalności tabel
Na zakończenie procesu projektowania należy jeszcze sprawdzić, czy tabele, których strukturę
podano w powyższych tabelach są co najmniej w trzeciej postaci normalnej. Przeglądając tabele
można zauważyć, że warunku tego nie spełniają tabele Klienci i Dostawcy, ponieważ zawierają
atrybut wielowartościowy adres. Wobec tego należy przeprowadzić normalizację tych tabel. Po
normalizacji, czyli zastąpieniu atrybutu adres przez trzy atrybuty elementarne tj. kod, miasto i ulicę
tabele te otrzymują postać:
Tabela Klienci
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Klient Klucz
Identyfikator
klienta
Integer
Id_Rejonu
Klucz obcy
Identyfikator rejonu
Integer
Firma
Nazwa skrócona klienta
Char(20)
NIP
Nr identyfikacji podatkowej
Char(13)
Kod
Kod
pocztowy
Char(6)
Miasto
Miejscowość Char(40)
5
Projektowanie bazy danych - przykład
Ulica
Ulica
Char(30)
Telefon
Telefon do klienta
Char(15)
Bank
Nazwa
banku
Char(40)
Konto
Konto
bankowe
Char(40)
Nazwa_Klienta
Nazwa
klienta
Char(80)
Tabela Dostawcy
Nazwa atrybutu Rodzaj atrybutu
Znaczenie
Domena
Id_Dostawcy Klucz
Identyfikator
klienta
Integer
Firma
Nazwa skrócona klienta
Char(20)
NIP
Nr identyfikacji podatkowej
Char(13)
Kod
Kod
pocztowy
Char(6)
Miasto
Miejscowość Char(40)
Ulica
Ulica
Char(30)
Telefon
Telefon do klienta
Char(15)
Bank
Nazwa
banku
Char(40)
Konto
Konto
bankowe
Char(40)
Nazwa_Klienta
Nazwa
klienta
Char(80)
6