Scribd
Upload
31 views3 pages

Database Design for Transportation System

The document outlines a database design for a transportation system, detailing the structure of tables for various departments including Operations, HR, Fleet Maintenance, Customer Service, Sales & Marketing, Finance, and Safety. It includes SQL commands for creating tables such as Station, Route, Stop, Timetable, Driver, and Customer, along with data insertion examples. The design emphasizes relationships between entities through foreign keys and provides a foundation for managing transportation operations and customer interactions.

Uploaded by

JF Rahat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
31 views3 pages

Database Design for Transportation System

The document outlines a database design for a transportation system, detailing the structure of tables for various departments including Operations, HR, Fleet Maintenance, Customer Service, Sales & Marketing, Finance, and Safety. It includes SQL commands for creating tables such as Station, Route, Stop, Timetable, Driver, and Customer, along with data insertion examples. The design emphasizes relationships between entities through foreign keys and provides a foundation for managing transportation operations and customer interactions.

Uploaded by

JF Rahat
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

Database Design

--Operation Department: Tables->Route,Timetable,Stop,sattion


--SQL

CREATE TABLE Station (


StationID NUMBER PRIMARY KEY,
Name VARCHAR2(20) NOT NULL
);

CREATE TABLE Route (


RouteID NUMBER PRIMARY KEY,
Name VARCHAR2(20) NOT NULL,
OriginID NUMBER NOT NULL,
DestinationID NUMBER NOT NULL,
CONSTRAINT FK_OriginID FOREIGN KEY (OriginID) REFERENCES Station(StationID),
CONSTRAINT FK_DestinationID FOREIGN KEY (DestinationID) REFERENCES
Station(StationID)
);

CREATE TABLE Stop (


StationID NUMBER,
RouteID NUMBER,
StopNumber NUMBER,
PRIMARY KEY (StationID, RouteID, StopNumber),
CONSTRAINT FK_StationID FOREIGN KEY (StationID) REFERENCES Station(StationID),
CONSTRAINT FK_RouteID FOREIGN KEY (RouteID) REFERENCES Route(RouteID)
);

CREATE TABLE Timetable (


TimetableID NUMBER PRIMARY KEY,
RouteID NUMBER NOT NULL,
BusID NUMBER NOT NULL,
DriverID NUMBER NOT NULL,
St1_Time TIMESTAMP,
St2_Time TIMESTAMP,
St3_Time TIMESTAMP,
St4_Time TIMESTAMP,
St5_Time TIMESTAMP,
St6_Time TIMESTAMP,
St7_Time TIMESTAMP,
CONSTRAINT FK_RouteID_Timetable FOREIGN KEY (RouteID) REFERENCES
Route(RouteID),
CONSTRAINT FK_BusID FOREIGN KEY (BusID) REFERENCES Bus(BusID),
CONSTRAINT FK_DriverID FOREIGN KEY (DriverID) REFERENCES Driver(DriverID)
);

--2. HR Dept: Tables: Driver,DriverSalary


--SQL

CREATE TABLE Driver (


DriverID NUMBER PRIMARY KEY,
Name VARCHAR2(30) NOT NULL
);

CREATE TABLE DriverSalary (


Year NUMBER NOT NULL,
Month VARCHAR2(3) NOT NULL,
DriverID NUMBER NOT NULL,
Hourly NUMBER(10, 2) NOT NULL,
HoursWorked NUMBER NOT NULL,
Salary NUMBER(10, 2) NOT NULL,
PRIMARY KEY (Year, Month, DriverID),
CONSTRAINT FK_SalaryDriverID FOREIGN KEY (DriverID) REFERENCES Driver(DriverID)
);

--3. Fleet Maintenance and Engineering: Tables: Bus,BusFleetManagement


--SQL
CREATE TABLE Bus (
BusID NUMBER PRIMARY KEY,
Make VARCHAR2(30) NOT NULL,
Model VARCHAR2(30) NOT NULL,
Year NUMBER NOT NULL
);

CREATE TABLE BusFleetManagement (


BusID NUMBER,
MaintenanceDate DATE,
PRIMARY KEY (BusID, MaintenanceDate),
CONSTRAINT FK_FleetBusID FOREIGN KEY (BusID) REFERENCES Bus(BusID)
);

--4. Customer Service Department: Customer, Customer History


--SQL
CREATE TABLE Customer (
CustomerID NUMBER PRIMARY KEY,
Name VARCHAR2(30) NOT NULL
);

CREATE TABLE CustomerHistory (


HistoryID NUMBER PRIMARY KEY,
CustomerID NUMBER NOT NULL,
Datetime TIMESTAMP NOT NULL,
EnterStationID NUMBER NOT NULL,
ExitStationID NUMBER NOT NULL,
Charges NUMBER(10, 2) NOT NULL,
Credits NUMBER(10, 2) NOT NULL,
Balance NUMBER(10, 2) NOT NULL,
CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES
Customer(CustomerID),
CONSTRAINT FK_EnterStation FOREIGN KEY (EnterStationID) REFERENCES
Station(StationID),
CONSTRAINT FK_ExitStation FOREIGN KEY (ExitStationID) REFERENCES
Station(StationID)
);

--[Link] & Marketing: use customer & custoimerHistory table for analysis

--6. Finanace & Accounting Dept: Tables-->DriverSalary,CustomerHistory


--7. Safety and Complaince Department: Tables-->Driver & BusfleetManagement

--Data Insertion:
-- Insert Stations
INSERT INTO Station VALUES
(1, 'Est'), (2, 'Meadow Park'), (3, 'Central Terminal'), (4, 'Downtown Square'),
(5, 'Lake View'), (6, 'River Reach'), (7, 'West Bridge');

-- Insert Routes
INSERT INTO Route VALUES
(1, 'Main', 1, 5), (2, 'Main', 5, 1), (3, 'River', 1, 7), (4, 'River', 7, 1);

-- Insert Stops
INSERT INTO Stop VALUES
(1, 1, 1), (2, 1, 2), (3, 1, 3);

-- Insert Buses
INSERT INTO Bus VALUES
(350001, 'Mitsubishi', 'Aero Ace', 2019),
(350002, 'Mitsubishi', 'Aero Star', 2021);

-- Insert Drivers
INSERT INTO Driver VALUES
(350001, 'Teddy Park'), (350002, 'Sophia Nguyen');

-- Insert Timetable
INSERT INTO Timetable VALUES
(1, 1, 350001, 350001, TO_TIMESTAMP('2024-01-01 08:00:00', 'YYYY-MM-DD
HH24:MI:SS'), NULL, NULL, NULL, NULL, NULL, NULL);

-- Insert Customers
INSERT INTO Customer VALUES
(1, 'Yuna Ukawa'), (2, 'Liam Rodriguez');

-- Insert Customer History


INSERT INTO CustomerHistory VALUES
(1, 1, TO_TIMESTAMP('2024-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 1, 2, 10.00,
0.00, 90.00);

-- Insert Salaries
INSERT INTO DriverSalary VALUES
(2024, 'Jan', 350001, 25.00, 160, 4000.00);

-- Insert Fleet Maintenance


INSERT INTO BusFleetManagement VALUES
(350001, TO_DATE('2024-01-01', 'YYYY-MM-DD'));

Common questions

Powered by AI

In the Route table, integrity constraints include the primary key constraint on RouteID and two foreign key constraints—FK_OriginID and FK_DestinationID—which point to Station(StationID). These constraints enforce unique identification of routes and ensure that origin and destination references are valid, existing stations. By doing so, they guarantee meaningful relationships between routes and stations, preventing erroneous or orphaned entries and preserving the logic of route planning .

The BusFleetManagement table lists BusID with MaintenanceDate as a composite primary key. This structure effectively tracks maintenance history for each bus by associating specific maintenance records with individual buses. It ensures precise logging of maintenance activities, allowing fleet managers to optimize service schedules and manage maintenance requirements to enhance fleet reliability and performance .

The Timetable table utilizes foreign key constraints such as FK_RouteID_Timetable, FK_BusID, and FK_DriverID, which reference the Route, Bus, and Driver tables, respectively. These constraints ensure that every record within the Timetable corresponds to valid entries in these tables, maintaining data integrity. This linkage across departments ensures that schedules are accurately tied to its associated routes, buses, and drivers, preventing potential data inconsistencies .

DriverSalary table enforces referential integrity through the FK_SalaryDriverID foreign key constraint, which references DriverID from the Driver table. This ensures that any driver listed in the DriverSalary table corresponds to a valid and active driver recorded in the Driver table. This prevents discrepancies in salary data entries, as invalid staff cannot have salary records without first being a part of the Driver table .

The CustomerHistory table records transactions with fields such as EnterStationID, ExitStationID, Charges, Credits, and Balance. This provides detailed insights into individual customer journeys and financial activities. The Sales & Marketing Department can utilize this data to analyze travel patterns, calculate average charges, and adjust marketing strategies. Understanding high-frequency travel patterns can support targeted promotions and improve customer engagement strategies .

The Customer table provides basic customer information while the CustomerHistory table records detailed transaction data. Together, they facilitate comprehensive account management, allowing customer service departments to access both static details and dynamic account activity. This integration enables monitoring of individual customer balances, transaction histories, and helps manage credit and charge adjustments effectively .

In BusFleetManagement, a composite primary key consisting of BusID and MaintenanceDate ensures that each maintenance record is uniquely identifiable by associating each maintenance entry with both the bus and the specific date of maintenance. This structure prevents duplicate records and supports comprehensive maintenance history tracking, critical for monitoring fleet maintenance effectively and ensuring buses are serviced timely and as required .

The presence of NULL values in timestamp fields such as St2_Time through St7_Time in the Timetable table implies incomplete scheduling for stops on a route. This can lead to inefficiencies, such as inaccurate route length estimation or misallocation of resources. Incomplete data can affect the planning and optimization of bus schedules, potentially leading to suboptimal service delivery and impacting operational efficiency .

The DriverSalary table includes a foreign key constraint (FK_SalaryDriverID) that references the Driver table's DriverID. This ensures that each salary record is associated with an existing driver, maintaining referential integrity. The structure allows for detailed tracking of each driver's compensation by storing year, month, hourly rate, hours worked, and total salary. This relationship supports effective compensation management by linking salary details directly with driver identities .

Join operations between the Stop and Station tables are essential for retrieving detailed stop descriptions along a route. By matching Stop records using StationID foreign key, one can pair stop sequence numbers with station names. This facilitates the generation of human-readable route itineraries and ensures accurate mapping of routes with their respective station stops, which is critical for operational planning and providing customer information .

You might also like