Pracownicy

id_pracownika
imię
nazwisko
id_stanowiska
id_przełożonego

Stanowiska

id_stanowiska
nazwa
wynagrodzenie

∞

1

Punkt

pracownik

id_punktu
id_pracownika

1

Punkty poboru

przesyłek

id_punktu

adres_punktu
id_centrum_powiązanego

∞ 1

Centrum

id_centrum

adres_centrum

Centrum

pracownik

id_centrum
id_pracownika

1

∞

Transport P → C

id_transportu
id_punktu
dzień_tyg_kursu
godzina_odjazdu
czas_przejazdu
masa_dopuszczalna
masa_załadowana

1

∞

Przesyłki

id_przesyłki
data_nadania
godz_nadania
adres_nadawcy
adres_odbiorcy
typ_przesyłki
waga

Przesyłki – informacje

przewozowe

id_przesyłki
id_aktualnego_punktu

id_aktualnego_centrum

id_aktualnego_transportu

id_rejonu_odbiorcy
rodzaj_aktualnego_transportu
status

Transport C → C

id_transportu
id_centrum_źródłowego
id_centrum_docelowego
dzień_tyg_kursu
godzina_odjazdu
czas_przejazdu
masa_dopuszczalna
masa_załadowana

Transport C → R

id_transportu
id_centrum
dzień_tyg_kursu
godzina_odjazdu
czas_przejazdu
masa_dopuszczalna
masa_załadowana

Transport rejon

id_transportu
id_rejonu

∞

1

∞

1

∞

1

∞

∞

1

1

1

1

∞

1

1

1

1

1

1

1

1

∞

∞
∞

B a z a d a n y c h f i r m y k u r i e r s k i e j

B a z a d a n y c h f i r m y k u r i e r s k i e j

∞

Autor: Dariusz Mikołajczuk

Rejon

id_rejonu
nazwa_rejonu
id_centrum_powiązanego

1

∞

1

1

1

1

Pracownicy

Informacje o wszystkich pracownikach firmy.

id_pracownika NUMBER(8)
imię VARCHAR2(15)

NOT NULL

nazwisko VARCHAR2(20)

NOT NULL

id_stanowiska NUMBER(8)
id_przełożonego NUMBER(8)

Stanowiska

Informacje o istniejących stanowiskach pracy.

id_stanowiska NUMBER(8)
nazwa VARCHAR2(20)

NOT NULL

wynagrodzenie NUMBER(7,2)

Opisy poszczególnych tabel (1)

Użyte oznaczenia:

id_pracownika

– oznacza klucz główny

id_stanowiska

– oznacza klucz obcy

ADRES

– typ rekordowy przechowujący dane:
nr_lokalu

VARCHAR2(8)

ulica

VARCHAR2(25)

miejscowość

VARCHAR2(25)

kod_pocztowy

VARCHAR2(8)

Punkt pracownik

Identyfikatory pracowników
danego punktu.

id_punktu NUMBER(8)
id_pracownika NUMBER(8)

Centrum pracownik

Identyfikatory pracowników

danego centrum.

id_centrum NUMBER(8)
id_pracownika NUMBER(8)

Punkty poboru przesyłek

Punkty, do których klienci przynoszą przesyłki do wysłania.

id_punktu NUMBER(8)

adres_punktu ADRES
id_centrum_powiązanego NUMBER(8)

Centrum

Siedziby firmy zajmujące się tylko dystrybucją przesyłek.

id_centrum NUMBER(8)

adres_centrum ADRES

Rejon miejscowość

Miejscowości przypisane odpowiednim

rejonom odbiorczym.

id_rejonu NUMBER(8)
id_miejscowości NUMBER(8)

Transport rejon

Przypisuje rejony odpowiednim

transportom C → R

id_transportu NUMBER(8)
id_rejonu NUMBER(8)

Rejon

Tabela rejonów odbiorczych

id_rejonu NUMBER(8)
nazwa_rejonu VARCHAR2(30)

id_centrum_powiązanego

NUMBER(8)

NOT NULL

Transport P → C

Tabela transportów z punktu o danym id do
powiązanego z nim centrum.
id_transportu NUMBER(8)
id_punktu NUMBER(8)
dzień_tyg_kursu NUMBER(1) [1-pn.]
godzina_odjazdu VARCHAR2(5) [HH24:MM]
czas_przejazdu NUMBER(3) [min]

masa_dopuszczalna NUMBER(5) [kg]

NOT NULL

masa_załadowana NUMBER(6,2) [kg]

Opisy poszczególnych tabel (2)

Użyte oznaczenia:

id_pracownika

– oznacza klucz główny

id_stanowiska

– oznacza klucz obcy

ADRES

– typ rekordowy przechowujący dane:
nr_lokalu

VARCHAR2(8)

ulica

VARCHAR2(25)

miejscowość

VARCHAR2(25)

kod_pocztowy

VARCHAR2(8)

Transport C → C

Tabela transportów między centrami.
id_transportu NUMBER(8)
id_centrum_źródłowego NUMBER(8)
id_centrum_docelowego NUMBER(8)
dzień_tyg_kursu NUMBER(1) [1-pn.]
godzina_odjazdu VARCHAR2(5) [HH24:MM]
czas_przejazdu NUMBER(3) [min]

masa_dopuszczalna NUMBER(5) [kg]

NOT NULL

masa_załadowana NUMBER(6,2) [kg]

Transport C → R

Tabela transportów między centrami, a podległymi
im rejonami.
id_transportu NUMBER(8)
id_centrum NUMBER(8)
dzień_tyg_kursu NUMBER(1) [1-pn.]
godzina_odjazdu VARCHAR2(5) [HH24:MM]
czas_przejazdu NUMBER(3) [min]
masa_dopuszczalna NUMBER(5) [kg]
masa_załadowana NUMBER(6,2) [kg]

Przesyłki

Ogólne informacje o każdej przesyłce będącej w obiegu.
id_przesyłki

NUMBER(10)

data_nadania VARCHAR2(10) [DD/MM/YYYY]
godz_nadania

VARCHAR2(5) [HH24:MM]

adres_nadawcy ADRES
adres_odbiorcy ADRES
typ_przesyłki VARCHAR2(20) [kilka predefiniowanych typów]
waga NUMBER(10,3) [kg]

Przesyłki – informacje przewozowe

Informacje dotyczące przesyłek użyteczne podczas transportu.

id_przesyłki

NUMBER(10)

id_aktualnego_punktu

NUMBER(8) [id lub NULL]

id_aktualnego_centrum

NUMBER(8) [id lub NULL]

id_aktualnego_transportu

NUMBER(8) [id lub NULL]

rodzaj_aktualnego_transportu NUMBER(1) [0, 1, 2, lub NULL]
id_rejonu_odbiorcy

NUMBER(8)

status NUMBER(1) [-2 … 4 różne od NULL]

Opis bazy danych

Zaprojektowana przeze mnie baza danych ma służyć obsłudze

przesyłek oraz zatrudnionych pracowników w firmie kurierskiej. Baza
powinna umożliwiać zarządzanie przesyłkami w firmie o zasięgu
krajowym lub międzynarodowym. Przyjęto jednak założenie, że transport
odbywa się tylko drogą lądową (transport kołowy i/lub kolejowy).

Działanie systemu najlepiej opisze następujący

„cykl życia”

przesyłek:

1. Po odebraniu w punkcie poboru przesyłki od klienta, najpierw

tworzony jest nowy rekord w tabeli Przesyłki. Ustawiane są w nim
wszystkie atrybuty, żaden nie może być pominięty.
Istotne jest aby pracownik punktu sprawdził dodatkowo, czy
miejscowość odbiorcy podana przez nadawcę znajduje się w tabeli
Rejon miejscowość.
Jeśli taka nie istnieje, wówczas są dwie opcje: albo klient się
pomylił i musi poprawić dane, albo taka miejscowość rzeczywiście
istnieje – należy ją wówczas dodać do tabeli Rejon miejscowość,
przypisać do istniejącego Rejonu lub utworzyć nowy rejon oraz
dodać nowy rejon do istniejącego transportu C → R lub utworzyć
nowy taki transport.
Operacja dodawania nowej miejscowość może być dość
czasochłonna. Aby klient nie czekał, status przesyłki ustawiany jest
na -1. Oznacza to, że przesyłka powinna czekać w punkcie do
czasu rozwiązania problemu. Wówczas status przestawiany jest na
0. Przypadki takie rozpatrywane są indywidualnie, lecz są to
rzadkie przypadki.

2. Potem, automatycznie tworzony jest nowy rekord w tabeli

Przesyłki – informacje przewozowe. Poza kluczem głównym,
powiązanym z dopiero co powstałą przesyłką, uzupełniane jest
tylko pole id_aktualnego_punktu, a status ustawiany jest na 0.

3. Co jakiś czas, wyznaczony pracownik punktu wyszukuje przesyłki

mające status 0. Dla każdej z nich zmienia status na 1. Wymusza
to z kolei uzupełnienie dwóch pól:

●

id_aktualnego_transportu – może tu być automatycznie
wpisany najbliższy transport do związanego z punktem
centrum. Przed powiązaniem przesyłki z transportem,
sprawdzane jest dodatkowo czy nie przekroczono
masy_dopuszczalnej danego transportu. Można to obliczyć na
podstawie pól:

waga,

masa_załadowana

i

masa_dopuszczalna. Masa_załadowana jest automatycznie
zwiększana po przypisaniu paczki do transportu.

●

rodzaj_aktualnego_transportu – tu wpisywany jest numer 0,
oznaczający że korzystamy Transportu P → C. Rodzaju
transportu nie można zidentyfikować po samym id_transportu
ze względu na użycie osobnych tabel do różnych rodzajów
transportu (identyfikatory te mogą się powtarzać).

4. Gdy nadchodzi czas załadowania pewnego transportu, inny

pracownik punktu wyszukuje w bazie wszystkie identyfikatory
przesyłek, które mają status 1 i przypisane są do danego
id_transportu oraz danego rodzaju_transportu. Przesyłki te są
ładowane na dany transport i odjeżdżają do powiązanego z
punktem centrum.

5. Bezpośrednio po odebraniu transportu przesyłek w centrum,

pracownik centrum ustawia dla każdej przesyłki status 2. Dla
każdej automatycznie wykonywane są następujące czynności
(kolejność istotna):
a) masa_załadowana w powiązanym transporcie zmniejszana jest

o wagę danej przesyłki.

b) id_aktualnego_transportu ustawiane jest na wartość NULL.
c) id_aktualnego_punktu ustawiane na NULL.
d) id_aktualnego_centrum ustawiane na id_centrum, które

odebrało przesyłkę.

Jeśli na koniec rozładunku danego transportu masa_załadowana
nie jest równa zero oznacza to, że jakaś przesyłka została
pominięta. Można wówczas łatwo sprawdzić jakie paczki zaginęły
(paczki o statusie 1, powiązane z danym transportem).

6. Co jakiś czas inny pracownik centrum wyszukuje przesyłki

oznaczone statusem 2. Sprawdza następnie dla każdej przesyłki
czy miejscowość odbiorcy znajduje się w rejonie obsługiwanym
przez Transporty C → R z danego centrum.
a) Jeśli tak jest, to status przesyłki ustawiany jest na 3. Wymusza

to uzupełnienie id_aktualnego_transportu. Jest to operacja
analogiczna jak w Punkcie poboru przesyłek, z tym że przy
wyborze transportu oprócz masy_załadowanej i czasu odjazdu
brany jest też pod uwagę rejon obsługiwany przez dany
transport. Rodzaj_transportu ustawiany jest na 2. Operacje te
wykonywane są z reguły automatycznie lub pod kontrolą
pracownika.

b) Jeśli miejscowość odbiorcy nie jest bezpośrednio obsługiwana

przez dane centrum wówczas należy wyznaczyć dla przesyłki
id_centrum do którego zostanie ona przekazana. Jest to
wyznaczane przy pomocy mapy sieci centrów oraz transportów.
Z reguły może to być wykonane automatycznie.
Po pomyślnym znalezieniu identyfikatora kolejnego centrum
pośredniego, status przesyłki ustawiany jest na 4. Wymusza to
wpisanie id_aktualnego_transportu na podstawie tabeli
Transport C → C. Przy wyborze brany jest pod uwagę czas
odjazdu, masa_załadowana i id_centrum_docelowego.
Rodzaj_transportu ustawiany jest na 1. Ta operacja jest znów
automatyczna lub półautomatyczna.

7. Kiedy z centrum ma odjechać transport obsługujący pewien rejon

zwyczajnie wyszukiwane są przesyłki o statusie 3, z przypisanym
określonym identyfikatorem i rodzajem transportu. Ładowane one
są do samochodu i transport odjeżdża (w tym czasie w atrybutach
przesyłki cały czas przechowywane jest id_aktualnego_centrum
na wszelki wypadek).

8. Podobnie sprawa wygląda z załadunkiem przesyłek między –

centrowych. Wyszukiwane są tylko przesyłki o statusie 4 z
określonym identyfikatorem i rodzajem transportu.

9. Kiedy pojedyncza przesyłka rejonowa, zostanie dostarczona

odbiorcy wówczas jej status ustawiany jest na -2. Powoduje to
pomniejszenie masy_załadowanej transportu o wagę przesyłki.
Wszystkie informacje przewozowe o przesyłce (poza
identyfikatorem i statusem) zostają ustawione na wartość NULL.
Kierowca transportu ma obowiązek rozwozić wszystkie przesyłki
do czasu kiedy masa_załadowana osiągnie wartość zero. Jeśli nie
ma już żadnych paczek, a masa załadowana nie jest równa zero,
łatwo można znaleźć przesyłki, które zostały zgubione.
Przesyłki po doręczeniu mają ustawiany status na -2. Oznacza to,
że mają być przechowane informację o nich w tabeli Przesyłki
przez określony czas (w razie reklamacji). Po tym czasie są
automatycznie usuwane ich rekordy w tabelach Przesyłki i
Przesyłki – informacje przewozowe.

10. Kiedy transport przesyłek między – centrowych dotrze do centrum

docelowego wówczas następuje operacja analogiczna do odbioru
przesyłek pochodzących z punktu. Znów ustawiany jest status 2 co
powoduje automatyczne wykonanie określonych czynności. Cykl
operacji powtarza się od punktu piątego.

Implementacja bazy w Sqlplus

Plik z definicjami tabel i relacji: Struktura.sql
Plik z przykładowymi danymi:

Dane.sql

DROP TABLE Przesylki_inf_przewozowe

CASCADE CONSTRAINTS;

DROP TABLE Przesylki

CASCADE CONSTRAINTS;

DROP TABLE Transport_C_R

CASCADE CONSTRAINTS;

DROP TABLE Transport_C_C

CASCADE CONSTRAINTS;

DROP TABLE Transport_P_C

CASCADE CONSTRAINTS;

DROP TABLE Transport_rejon

CASCADE CONSTRAINTS;

DROP TABLE Rejon

CASCADE CONSTRAINTS;

DROP TABLE Centrum

CASCADE CONSTRAINTS;

DROP TABLE Punkt_poboru_przesylek

CASCADE CONSTRAINTS;

DROP TABLE Centrum_pracownik

CASCADE CONSTRAINTS;

DROP TABLE Punkt_pracownik

CASCADE CONSTRAINTS;

DROP TABLE Pracownicy

CASCADE CONSTRAINTS;

DROP TABLE Stanowiska

CASCADE CONSTRAINTS;

DROP TYPE ADRES;

CREATE TYPE ADRES is Object (

nr_lokalu

VARCHAR2(8),

ulica

VARCHAR2(25),

miejscowosc

VARCHAR2(25),

kod_pocztowy

VARCHAR2(8)

);
/

CREATE TABLE Stanowiska (

id_stanowiska NUMBER(8)

PRIMARY KEY,

nazwa

VARCHAR2(20)

NOT NULL,

wynagrodzenie NUMBER(7,2)

);

CREATE TABLE Pracownicy (

id_pracownika

NUMBER(8)

PRIMARY KEY,

imie

VARCHAR2(15)

NOT NULL,

nazwisko

VARCHAR2(20)

NOT NULL,

id_stanowiska

NUMBER(8),

id_przelozonego NUMBER(8)

);

CREATE TABLE Punkt_pracownik (

id_punktu

NUMBER(8),

id_pracownika NUMBER(8)

);

CREATE TABLE Centrum_pracownik (

id_centrum

NUMBER(8),

id_pracownika NUMBER(8)

);

CREATE TABLE Punkt_poboru_przesylek (

id_punktu

NUMBER(8)

PRIMARY KEY,

adres_punktu

ADRES,

id_centrum_powiazanego NUMBER(8)

);

CREATE TABLE Centrum (

id_centrum

NUMBER(8)

PRIMARY KEY,

adres_centrum ADRES

);

CREATE TABLE Rejon_miejscowosc (

id_rejonu

NUMBER(8),

id_miejscowosci

NUMBER(8)

);

CREATE TABLE Rejon (

id_rejonu

NUMBER(8)

PRIMARY KEY,

nazwa_rejonu

VARCHAR2(30),

id_centrum_powiazanego NUMBER(8)

NOT NULL

);

CREATE TABLE Transport_rejon (

id_transportu

NUMBER(8),

id_rejonu

NUMBER(8)

);

CREATE TABLE Transport_P_C (

id_transportu

NUMBER(8),

id_punktu

NUMBER(8),

dzien_tyg_kursu

NUMBER(1),

godzina_odjazdu

VARCHAR2(5),

czas_przejazdu

NUMBER(3),

masa_dopuszczalna

NUMBER(5) NOT NULL,

masa_zaladowana

NUMBER(6,2)

);

CREATE TABLE Transport_C_C (

id_transportu

NUMBER(8),

id_centrum_zrodlowego NUMBER(8),
id_centrum_docelowego NUMBER(8),
dzien_tyg_kursu

NUMBER(1),

godzina_odjazdu

VARCHAR2(5),

czas_przejazdu

NUMBER(3),

masa_dopuszczalna

NUMBER(5) NOT NULL,

masa_zaladowana

NUMBER(6,2)

);

CREATE TABLE Transport_C_R (

id_transportu

NUMBER(8),

id_centrum

NUMBER(8),

dzien_tyg_kursu

NUMBER(1),

godzina_odjazdu

VARCHAR2(5),

czas_przejazdu

NUMBER(3),

masa_dopuszczalna

NUMBER(5) NOT NULL,

masa_zaladowana

NUMBER(6,2)

);

CREATE TABLE Przesylki (

id_przesylki

NUMBER(10)

PRIMARY KEY,

data_nadania

VARCHAR2(10),

godz_nadania

VARCHAR2(5),

adres_nadawcy

ADRES,

adres_odbiorcy

ADRES,

typ_przesylki

VARCHAR2(20),

waga

NUMBER(10,3)

);

CREATE TABLE Przesylki_inf_przewozowe (

id_przesylki

NUMBER(10) PRIMARY KEY,

id_aktualnego_punktu

NUMBER(8),

id_aktualnego_centrum

NUMBER(8),

id_aktualnego_transportu

NUMBER(8),

rodzaj_aktualnego_transportu NUMBER(1),
id_rejonu_odbiorcy

NUMBER(8),

status

NUMBER(2)

NOT NULL

);

ALTER TABLE Punkt_pracownik ADD CONSTRAINT kg1 PRIMARY KEY

(id_punktu, id_pracownika);

ALTER TABLE Centrum_pracownik ADD CONSTRAINT kg2 PRIMARY KEY

(id_centrum, id_pracownika);

ALTER TABLE Rejon_miejscowosc

ADD CONSTRAINT kg3 PRIMARY KEY

(id_rejonu, id_miejscowosci);

ALTER TABLE Transport_rejon

ADD CONSTRAINT kg4 PRIMARY KEY

(id_transportu, id_rejonu);

ALTER TABLE Transport_P_C

ADD CONSTRAINT kg5 PRIMARY KEY

(id_transportu, id_punktu);

ALTER TABLE Transport_C_C

ADD CONSTRAINT kg6 PRIMARY KEY

(id_transportu, id_centrum_zrodlowego, id_centrum_docelowego);

ALTER TABLE Transport_C_R

ADD CONSTRAINT kg7 PRIMARY KEY

(id_transportu, id_centrum);

ALTER TABLE Pracownicy ADD CONSTRAINT ko1 FOREIGN KEY (id_stanowiska)

REFERENCES Stanowiska(id_stanowiska);

ALTER TABLE Punkt_pracownik ADD CONSTRAINT ko2 FOREIGN KEY (id_punktu)

REFERENCES Punkt_poboru_przesylek(id_punktu);

ALTER TABLE Punkt_pracownik ADD CONSTRAINT ko3 FOREIGN KEY (id_pracownika)

REFERENCES Pracownicy(id_pracownika);

ALTER TABLE Punkt_poboru_przesylek ADD CONSTRAINT ko4 FOREIGN KEY

(id_centrum_powiazanego) REFERENCES Centrum(id_centrum);

ALTER TABLE Centrum_pracownik ADD CONSTRAINT ko5 FOREIGN KEY (id_centrum)

REFERENCES Centrum(id_centrum);

ALTER TABLE Centrum_pracownik ADD CONSTRAINT ko6 FOREIGN KEY (id_pracownika)

REFERENCES Pracownicy(id_pracownika);

ALTER TABLE Transport_P_C ADD CONSTRAINT ko7 FOREIGN KEY (id_punktu)

REFERENCES Punkt_poboru_przesylek(id_punktu);

ALTER TABLE Transport_C_C ADD CONSTRAINT ko8 FOREIGN KEY

(id_centrum_zrodlowego) REFERENCES Centrum(id_centrum);

ALTER TABLE Transport_C_C ADD CONSTRAINT ko9 FOREIGN KEY

(id_centrum_docelowego) REFERENCES Centrum(id_centrum);

ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko10 FOREIGN KEY

(id_przesylki) REFERENCES Przesylki(id_przesylki) ON DELETE CASCADE;

ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko11 FOREIGN KEY

(id_aktualnego_punktu) REFERENCES Punkt_poboru_przesylek(id_punktu);

ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko12 FOREIGN KEY

(id_aktualnego_centrum) REFERENCES Centrum(id_centrum);

ALTER TABLE Transport_C_R ADD CONSTRAINT ko13 FOREIGN KEY (id_centrum)

REFERENCES Centrum(id_centrum);

ALTER TABLE Transport_rejon ADD CONSTRAINT ko15 FOREIGN KEY (id_rejonu)

REFERENCES Rejon(id_rejonu);

ALTER TABLE Rejon_miejscowosc ADD CONSTRAINT ko16 FOREIGN KEY (id_rejonu)

REFERENCES Rejon(id_rejonu);

ALTER TABLE Rejon_miejscowosc ADD CONSTRAINT ko17 FOREIGN KEY

(id_miejscowosci) REFERENCES Miejscowosc(id_miejscowosci);

ALTER TABLE Rejon ADD CONSTRAINT ko18 FOREIGN KEY (id_centrum_powiazanego)

REFERENCES Centrum(id_centrum);

ALTER TABLE Przesylki_inf_przewozowe

ADD CONSTRAINT ko19 FOREIGN KEY

(id_rejonu_odbiorcy) REFERENCES Rejon(id_rejonu);

ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT c1 CHECK

((status is NOT NULL) and (status between -2 and 4));

ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT c2 CHECK (

(status < 0) or
((status=0) and (id_aktualnego_punktu is NOT NULL) and

(id_aktualnego_centrum is NULL) and (id_aktualnego_transportu is NULL) and
(rodzaj_aktualnego_transportu is NULL)) or

((status=1) and (id_aktualnego_punktu is NOT NULL) and

(id_aktualnego_centrum is NULL) and (id_aktualnego_transportu is NOT NULL)
and (rodzaj_aktualnego_transportu=0)) or

((status=2) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum

is NOT NULL) and (id_aktualnego_transportu is NULL) and
(rodzaj_aktualnego_transportu is NULL)) or

((status=3) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum

is NOT NULL) and (id_aktualnego_transportu is NOT NULL) and
(rodzaj_aktualnego_transportu=2)) or

((status=4) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum

is NOT NULL) and (id_aktualnego_transportu is NOT NULL) and
(rodzaj_aktualnego_transportu=1))
);

ALTER TABLE Stanowiska ADD CONSTRAINT c3 CHECK

((wynagrodzenie is NULL) or wynagrodzenie>0);