3. Co jakiś czas, wyznaczony pracownik punktu wyszukuje przesyłki
Opis bazy danych
mające status 0. Dla każdej z nich zmienia status na 1. Wymusza
Zaprojektowana przeze mnie baza danych ma służyć obsłudze
to z kolei uzupełnienie dwóch pól:
przesyłek oraz zatrudnionych pracowników w firmie kurierskiej. Baza
id_aktualnego_transportu  może tu być automatycznie
powinna umożliwiać zarządzanie przesyłkami w firmie o zasięgu
wpisany najbliższy transport do związanego z punktem
krajowym lub międzynarodowym. Przyjęto jednak założenie, że transport
centrum. Przed powiązaniem przesyłki z transportem,
odbywa się tylko drogą lądową (transport kołowy i/lub kolejowy).
sprawdzane jest dodatkowo czy nie przekroczono
masy_dopuszczalnej danego transportu. Można to obliczyć na
Działanie systemu najlepiej opisze następujący  cykl życia
podstawie pól: waga, masa_załadowana i
przesyłek:
masa_dopuszczalna. Masa_załadowana jest automatycznie
1. Po odebraniu w punkcie poboru przesyłki od klienta, najpierw
zwiększana po przypisaniu paczki do transportu.
tworzony jest nowy rekord w tabeli Przesyłki. Ustawiane są w nim
rodzaj_aktualnego_transportu  tu wpisywany jest numer 0,
wszystkie atrybuty, żaden nie może być pominięty.
oznaczający że korzystamy Transportu P C. Rodzaju
Istotne jest aby pracownik punktu sprawdził dodatkowo, czy
transportu nie można zidentyfikować po samym id_transportu
miejscowość odbiorcy podana przez nadawcę znajduje się w tabeli
ze względu na użycie osobnych tabel do różnych rodzajów
Rejon miejscowość.
transportu (identyfikatory te mogą się powtarzać).
Jeśli taka nie istnieje, wówczas są dwie opcje: albo klient się
4. Gdy nadchodzi czas załadowania pewnego transportu, inny
pomylił i musi poprawić dane, albo taka miejscowość rzeczywiście
pracownik punktu wyszukuje w bazie wszystkie identyfikatory
istnieje  należy ją wówczas dodać do tabeli Rejon miejscowość,
przesyłek, które mają status 1 i przypisane są do danego
przypisać do istniejącego Rejonu lub utworzyć nowy rejon oraz
id_transportu oraz danego rodzaju_transportu. Przesyłki te są
dodać nowy rejon do istniejącego transportu C R lub utworzyć
ładowane na dany transport i odjeżdżają do powiązanego z
nowy taki transport.
punktem centrum.
Operacja dodawania nowej miejscowość może być dość
5. Bezpośrednio po odebraniu transportu przesyłek w centrum,
czasochłonna. Aby klient nie czekał, status przesyłki ustawiany jest
pracownik centrum ustawia dla każdej przesyłki status 2. Dla
na -1. Oznacza to, że przesyłka powinna czekać w punkcie do
każdej automatycznie wykonywane są następujące czynności
czasu rozwiązania problemu. Wówczas status przestawiany jest na
(kolejność istotna):
0. Przypadki takie rozpatrywane są indywidualnie, lecz są to
a) masa_załadowana w powiązanym transporcie zmniejszana jest
rzadkie przypadki.
o wagę danej przesyłki.
2. Potem, automatycznie tworzony jest nowy rekord w tabeli
b) id_aktualnego_transportu ustawiane jest na wartość NULL.
Przesyłki  informacje przewozowe. Poza kluczem głównym,
c) id_aktualnego_punktu ustawiane na NULL.
powiązanym z dopiero co powstałą przesyłką, uzupełniane jest
tylko pole id_aktualnego_punktu, a status ustawiany jest na 0.
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 8. Podobnie sprawa wygląda z załadunkiem przesyłek między 
nie jest równa zero oznacza to, że jakaś przesyłka została centrowych. Wyszukiwane są tylko przesyłki o statusie 4 z
pominięta. Można wówczas łatwo sprawdzić jakie paczki zaginęły określonym identyfikatorem i rodzajem transportu.
(paczki o statusie 1, powiązane z danym transportem).
9. Kiedy pojedyncza przesyłka rejonowa, zostanie dostarczona
6. Co jakiś czas inny pracownik centrum wyszukuje przesyłki odbiorcy wówczas jej status ustawiany jest na -2. Powoduje to
oznaczone statusem 2. Sprawdza następnie dla każdej przesyłki pomniejszenie masy_załadowanej transportu o wagę przesyłki.
czy miejscowość odbiorcy znajduje się w rejonie obsługiwanym Wszystkie informacje przewozowe o przesyłce (poza
przez Transporty C R z danego centrum. identyfikatorem i statusem) zostają ustawione na wartość NULL.
Kierowca transportu ma obowiązek rozwozić wszystkie przesyłki
a) Jeśli tak jest, to status przesyłki ustawiany jest na 3. Wymusza
do czasu kiedy masa_załadowana osiągnie wartość zero. Jeśli nie
to uzupełnienie id_aktualnego_transportu. Jest to operacja
ma już żadnych paczek, a masa załadowana nie jest równa zero,
analogiczna jak w Punkcie poboru przesyłek, z tym że przy
łatwo można znalez
ć przesyłki, które zostały zgubione.
wyborze transportu oprócz masy_załadowanej i czasu odjazdu
brany jest też pod uwagę rejon obsługiwany przez dany Przesyłki po doręczeniu mają ustawiany status na -2. Oznacza to,
transport. Rodzaj_transportu ustawiany jest na 2. Operacje te że mają być przechowane informację o nich w tabeli Przesyłki
wykonywane są z reguły automatycznie lub pod kontrolą przez określony czas (w razie reklamacji). Po tym czasie są
pracownika. automatycznie usuwane ich rekordy w tabelach Przesyłki i
Przesyłki  informacje przewozowe.
b) Jeśli miejscowość odbiorcy nie jest bezpośrednio obsługiwana
przez dane centrum wówczas należy wyznaczyć dla przesyłki 10. Kiedy transport przesyłek między  centrowych dotrze do centrum
id_centrum do którego zostanie ona przekazana. Jest to docelowego wówczas następuje operacja analogiczna do odbioru
wyznaczane przy pomocy mapy sieci centrów oraz transportów. przesyłek pochodzących z punktu. Znów ustawiany jest status 2 co
Z reguły może to być wykonane automatycznie. powoduje automatyczne wykonanie określonych czynności. Cykl
operacji powtarza się od punktu piątego.
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. A
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).
CREATE TABLE Punkt_pracownik (
Implementacja bazy w Sqlplus
id_punktu NUMBER(8),
Plik z definicjami tabel i relacji: Struktura.sql
id_pracownika NUMBER(8)
Plik z przykładowymi danymi: Dane.sql
);
DROP TABLE Przesylki_inf_przewozowe CASCADE CONSTRAINTS;
CREATE TABLE Centrum_pracownik (
DROP TABLE Przesylki CASCADE CONSTRAINTS;
id_centrum NUMBER(8),
DROP TABLE Transport_C_R CASCADE CONSTRAINTS;
id_pracownika NUMBER(8)
DROP TABLE Transport_C_C CASCADE CONSTRAINTS;
);
DROP TABLE Transport_P_C CASCADE CONSTRAINTS;
DROP TABLE Transport_rejon CASCADE CONSTRAINTS;
CREATE TABLE Punkt_poboru_przesylek (
DROP TABLE Rejon CASCADE CONSTRAINTS;
id_punktu NUMBER(8) PRIMARY KEY,
DROP TABLE Centrum CASCADE CONSTRAINTS;
adres_punktu ADRES,
DROP TABLE Punkt_poboru_przesylek CASCADE CONSTRAINTS;
id_centrum_powiazanego NUMBER(8)
DROP TABLE Centrum_pracownik CASCADE CONSTRAINTS;
);
DROP TABLE Punkt_pracownik CASCADE CONSTRAINTS;
DROP TABLE Pracownicy CASCADE CONSTRAINTS;
CREATE TABLE Centrum (
DROP TABLE Stanowiska CASCADE CONSTRAINTS;
id_centrum NUMBER(8) PRIMARY KEY,
DROP TYPE ADRES;
adres_centrum ADRES
);
CREATE TYPE ADRES is Object (
nr_lokalu VARCHAR2(8),
CREATE TABLE Rejon_miejscowosc (
ulica VARCHAR2(25),
id_rejonu NUMBER(8),
miejscowosc VARCHAR2(25),
id_miejscowosci NUMBER(8)
kod_pocztowy VARCHAR2(8)
);
);
/
CREATE TABLE Rejon (
id_rejonu NUMBER(8) PRIMARY KEY,
CREATE TABLE Stanowiska (
nazwa_rejonu VARCHAR2(30),
id_stanowiska NUMBER(8) PRIMARY KEY,
id_centrum_powiazanego NUMBER(8) NOT NULL
nazwa VARCHAR2(20) NOT NULL,
);
wynagrodzenie NUMBER(7,2)
);
CREATE TABLE Transport_rejon (
id_transportu NUMBER(8),
CREATE TABLE Pracownicy (
id_rejonu NUMBER(8)
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 Transport_P_C ( CREATE TABLE Przesylki_inf_przewozowe (
id_transportu NUMBER(8), id_przesylki NUMBER(10) PRIMARY KEY,
id_punktu NUMBER(8), id_aktualnego_punktu NUMBER(8),
dzien_tyg_kursu NUMBER(1), id_aktualnego_centrum NUMBER(8),
godzina_odjazdu VARCHAR2(5), id_aktualnego_transportu NUMBER(8),
czas_przejazdu NUMBER(3), rodzaj_aktualnego_transportu NUMBER(1),
masa_dopuszczalna NUMBER(5) NOT NULL, id_rejonu_odbiorcy NUMBER(8),
masa_zaladowana NUMBER(6,2) status NUMBER(2) NOT NULL
); );
CREATE TABLE Transport_C_C (
id_transportu NUMBER(8), ALTER TABLE Punkt_pracownik ADD CONSTRAINT kg1 PRIMARY KEY
id_centrum_zrodlowego NUMBER(8), (id_punktu, id_pracownika);
id_centrum_docelowego NUMBER(8),
ALTER TABLE Centrum_pracownik ADD CONSTRAINT kg2 PRIMARY KEY
dzien_tyg_kursu NUMBER(1),
(id_centrum, id_pracownika);
godzina_odjazdu VARCHAR2(5),
ALTER TABLE Rejon_miejscowosc ADD CONSTRAINT kg3 PRIMARY KEY
czas_przejazdu NUMBER(3),
(id_rejonu, id_miejscowosci);
masa_dopuszczalna NUMBER(5) NOT NULL,
masa_zaladowana NUMBER(6,2) ALTER TABLE Transport_rejon ADD CONSTRAINT kg4 PRIMARY KEY
); (id_transportu, id_rejonu);
ALTER TABLE Transport_P_C ADD CONSTRAINT kg5 PRIMARY KEY
CREATE TABLE Transport_C_R (
(id_transportu, id_punktu);
id_transportu NUMBER(8),
ALTER TABLE Transport_C_C ADD CONSTRAINT kg6 PRIMARY KEY
id_centrum NUMBER(8),
(id_transportu, id_centrum_zrodlowego, id_centrum_docelowego);
dzien_tyg_kursu NUMBER(1),
godzina_odjazdu VARCHAR2(5), ALTER TABLE Transport_C_R ADD CONSTRAINT kg7 PRIMARY KEY
czas_przejazdu NUMBER(3), (id_transportu, id_centrum);
masa_dopuszczalna NUMBER(5) NOT NULL,
masa_zaladowana NUMBER(6,2) 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)
CREATE TABLE Przesylki (
REFERENCES Punkt_poboru_przesylek(id_punktu);
id_przesylki NUMBER(10) PRIMARY KEY,
ALTER TABLE Punkt_pracownik ADD CONSTRAINT ko3 FOREIGN KEY (id_pracownika)
data_nadania VARCHAR2(10),
REFERENCES Pracownicy(id_pracownika);
godz_nadania VARCHAR2(5),
ALTER TABLE Punkt_poboru_przesylek ADD CONSTRAINT ko4 FOREIGN KEY
adres_nadawcy ADRES,
(id_centrum_powiazanego) REFERENCES Centrum(id_centrum);
adres_odbiorcy ADRES,
typ_przesylki VARCHAR2(20),
ALTER TABLE Centrum_pracownik ADD CONSTRAINT ko5 FOREIGN KEY (id_centrum)
waga NUMBER(10,3)
REFERENCES Centrum(id_centrum);
);
ALTER TABLE Centrum_pracownik ADD CONSTRAINT ko6 FOREIGN KEY (id_pracownika) (id_aktualnego_centrum is NULL) and (id_aktualnego_transportu is NOT NULL)
REFERENCES Pracownicy(id_pracownika); and (rodzaj_aktualnego_transportu=0)) or
((status=2) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum
ALTER TABLE Transport_P_C ADD CONSTRAINT ko7 FOREIGN KEY (id_punktu)
is NOT NULL) and (id_aktualnego_transportu is NULL) and
REFERENCES Punkt_poboru_przesylek(id_punktu);
(rodzaj_aktualnego_transportu is NULL)) or
ALTER TABLE Transport_C_C ADD CONSTRAINT ko8 FOREIGN KEY
((status=3) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum
(id_centrum_zrodlowego) REFERENCES Centrum(id_centrum);
is NOT NULL) and (id_aktualnego_transportu is NOT NULL) and
ALTER TABLE Transport_C_C ADD CONSTRAINT ko9 FOREIGN KEY (rodzaj_aktualnego_transportu=2)) or
(id_centrum_docelowego) REFERENCES Centrum(id_centrum); ((status=4) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum
is NOT NULL) and (id_aktualnego_transportu is NOT NULL) and
ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko10 FOREIGN KEY
(rodzaj_aktualnego_transportu=1))
(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 Stanowiska ADD CONSTRAINT c3 CHECK
ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko12 FOREIGN KEY ((wynagrodzenie is NULL) or wynagrodzenie>0);
(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