3. Co jakiś czas, wyznaczony pracownik punktu wyszukuje przesyłki
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 powinna umożliwiać zarządzanie przesyłkami w firmie o zasięgu
● id_aktualnego_transportu – może tu być automatycznie krajowym lub międzynarodowym. Przyjęto jednak założenie, że transport wpisany najbliższy transport do związanego z punktem
odbywa się tylko drogą lądową (transport kołowy i/lub kolejowy).
centrum. Przed powiązaniem przesyłki z transportem,
sprawdzane jest dodatkowo czy nie przekroczono
Działanie systemu najlepiej opisze następujący „cykl życia”
masy_dopuszczalnej danego transportu. Można to obliczyć na przesyłek:
podstawie pól:
waga,
masa_załadowana
i
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 wszystkie atrybuty, żaden nie może być pominięty.
● rodzaj_aktualnego_transportu – tu wpisywany jest numer 0,
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 rzadkie przypadki.
a) masa_załadowana w powiązanym transporcie zmniejszana jest
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, powiązanym z dopiero co powstałą przesyłką, uzupełniane jest c) id_aktualnego_punktu ustawiane na NULL.
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.
a) Jeśli tak jest, to status przesyłki ustawiany jest na 3. Wymusza Kierowca transportu ma obowiązek rozwozić wszystkie przesyłki
to uzupełnienie id_aktualnego_transportu. Jest to operacja do czasu kiedy masa_załadowana osiągnie wartość zero. Jeśli nie analogiczna jak w Punkcie poboru przesyłek, z tym że przy ma już żadnych paczek, a masa załadowana nie jest równa zero,
wyborze transportu oprócz masy_załadowanej i czasu odjazdu łatwo można znaleźć przesyłki, które zostały zgubione.
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 b) Jeśli miejscowość odbiorcy nie jest bezpośrednio obsługiwana
Przesyłki – informacje przewozowe.
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
Po pomyślnym znalezieniu identyfikatora kolejnego centrum
operacji powtarza się od punktu piątego.
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).
CREATE TABLE Punkt_pracownik (
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 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),
czas_przejazdu
NUMBER(3),
ALTER TABLE Rejon_miejscowosc
ADD CONSTRAINT kg3 PRIMARY KEY
masa_dopuszczalna
NUMBER(5) NOT NULL,
(id_rejonu, id_miejscowosci);
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),
id_centrum
NUMBER(8),
ALTER TABLE Transport_C_C
ADD CONSTRAINT kg6 PRIMARY KEY
dzien_tyg_kursu
NUMBER(1),
(id_transportu, id_centrum_zrodlowego, id_centrum_docelowego);
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,
data_nadania
VARCHAR2(10),
ALTER TABLE Punkt_pracownik ADD CONSTRAINT ko3 FOREIGN KEY (id_pracownika) godz_nadania
VARCHAR2(5),
REFERENCES Pracownicy(id_pracownika);
adres_nadawcy
ADRES,
ALTER TABLE Punkt_poboru_przesylek ADD CONSTRAINT ko4 FOREIGN KEY
adres_odbiorcy
ADRES,
(id_centrum_powiazanego) REFERENCES Centrum(id_centrum);
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 ALTER TABLE Transport_P_C ADD CONSTRAINT ko7 FOREIGN KEY (id_punktu) ((status=2) and (id_aktualnego_punktu is NULL) and (id_aktualnego_centrum REFERENCES Punkt_poboru_przesylek(id_punktu); is NOT NULL) and (id_aktualnego_transportu is NULL) and (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 ALTER TABLE Przesylki_inf_przewozowe ADD CONSTRAINT ko10 FOREIGN KEY
is NOT NULL) and (id_aktualnego_transportu is NOT NULL) and
(id_przesylki) REFERENCES Przesylki(id_przesylki) ON DELETE CASCADE; (rodzaj_aktualnego_transportu=1))
);
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