CREATE TABLE Routes $($

RouteID INT PRIMARY KEY,

RouteName VARCHAR$(255)$ NOT NULL,

RouteDescription TEXT

$)$;

CREATE TABLE Stops $($

StopID INT PRIMARY KEY,

StopName VARCHAR$(255)$ NOT NULL,

Address VARCHAR$(255),$

Latitude DECIMAL$(9,6),$

Longitude DECIMAL$(9,6)$

$)$;

CREATE TABLE RouteStops $($

RouteID INT,

StopID INT,

StopSequence INT,

PRIMARY KEY $($RouteID$,$ StopID$),$

FOREIGN KEY $($RouteID$)$ REFERENCES Routes$($RouteID$),$

FOREIGN KEY $($StopID$)$ REFERENCES Stops$($StopID$)$

$)$;

CREATE TABLE Buses $($

BusID INT PRIMARY KEY,

BusNumber VARCHAR$(20)$ NOT NULL,

Capacity INT,

DriverID INT

$)$;

CREATE TABLE Drivers $($

DriverID INT PRIMARY KEY,

FirstName VARCHAR$(50)$ NOT NULL,

LastName VARCHAR$(50)$ NOT NULL,

LicenseNumber VARCHAR$(20)$

$)$;

CREATE TABLE Schedules $($

ScheduleID INT PRIMARY KEY,

RouteID INT,

BusID INT,

DriverID INT,

DepartureTime TIME,

ArrivalTime TIME,

FOREIGN KEY $($RouteID$)$ REFERENCES Routes$($RouteID$),$

FOREIGN KEY $($BusID$)$ REFERENCES Buses$($BusID$),$

FOREIGN KEY $($DriverID$)$ REFERENCES Drivers$($DriverID$)$

$)$;

CREATE TABLE Passengers $($

PassengerID INT PRIMARY KEY,

FirstName VARCHAR$(50)$ NOT NULL,

LastName VARCHAR$(50)$ NOT NULL,

Email VARCHAR$(100),$

PhoneNumber VARCHAR$(15)$

$)$;

CREATE TABLE Payments $($

PaymentID INT PRIMARY KEY,

PassengerID INT,

RouteID INT,

BusID INT,

ScheduleID INT,

PaymentAmount DECIMAL$(10,2)$ NOT NULL,

PaymentDate DATE,

CardNumber VARCHAR$(16)$ NOT NULL,

CardHolderName VARCHAR$(100)$ NOT NULL,

ExpiryDate DATE,

PaymentMethod VARCHAR$(50)$ DEFAULT 'Card',

FOREIGN KEY $($PassengerID$)$ REFERENCES Passengers$($PassengerID$),$

FOREIGN KEY $($RouteID$)$ REFERENCES Routes$($RouteID$),$

FOREIGN KEY $($BusID$)$ REFERENCES Buses$($BusID$),$

FOREIGN KEY $($ScheduleID$)$ REFERENCES Schedules$($ScheduleID$)$

$)$;

CREATE TABLE BusLocations $($

BusID INT,

Latitude DECIMAL$(9,6),$

Longitude DECIMAL$(9,6),$

Timestamp TIMESTAMP,

PRIMARY KEY $($BusID$,$ Timestamp$),$

FOREIGN KEY $($BusID$)$ REFERENCES Buses$($BusID$)$

$)$;

CREATE TABLE Feedback $($

FeedbackID INT PRIMARY KEY,

PassengerID INT,

RouteID INT,

BusID INT,

ScheduleID INT,

Comment TEXT,

Rating INT,

SubmissionDate DATE,

FOREIGN KEY $($PassengerID$)$ REFERENCES Passengers$($PassengerID$),$

FOREIGN KEY $($RouteID$)$ REFERENCES Routes$($RouteID$),$

FOREIGN KEY $($BusID$)$ REFERENCES Buses$($BusID$),$

FOREIGN KEY $($ScheduleID$)$ REFERENCES Schedules$($ScheduleID$)$

$)$;

CREATE TABLE TripStatus $($

TripStatusID INT PRIMARY KEY,

ScheduleID INT,

BusID INT,

CurrentStopID INT,

ArrivalTimeEstimate TIME,

Status VARCHAR$(50),$

FOREIGN KEY $($ScheduleID$)$ REFERENCES Schedules$($ScheduleID$),$

FOREIGN KEY $($BusID$)$ REFERENCES Buses$($BusID$),$

FOREIGN KEY $($CurrentStopID$)$ REFERENCES Stops$($StopID$)$

$)$;

CREATE TABLE PaymentHistory $($

payment$\_$id INT PRIMARY KEY,

passenger$\_$id INT,

schedule$\_$id INT,

payment$\_$date DATETIME,

amount$\_$paid DECIMAL$(10,2),$

payment$\_$method VARCHAR$(50),$

from$\_$stop$\_$id INT,

to$\_$stop$\_$id INT,

FOREIGN KEY $($passenger$\_$id$)$ REFERENCES Passengers$($passenger$\_$id$),$

FOREIGN KEY $($schedule$\_$id$)$ REFERENCES Schedules$($schedule$\_$id$),$

FOREIGN KEY $($from$\_$stop$\_$id$)$ REFERENCES Stops$($stop$\_$id$),$

FOREIGN KEY $($to$\_$stop$\_$id$)$ REFERENCES Stops$($stop$\_$id$)$

$)$;

$$

$$RELATIONAL SCHEMA & MAPPING

$($

Below is an example, you should create your own Relational Schema related to your project. Do not forget to show your PKs

$($

underlined

$)$

$$and FKs

$($

dotted

$)$

$.$

$)$

$($

Show the intermediate steps

$$

$$Mapping a regular entity

$$

$$Mapping a composite attribute

$$

$$Mapping a multivalued attribute

$$

$$Mapping Weak Entities

$$

$$Mapping Binary Relationships

$$

$$Mapping Unary Relationships