Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Im not sure about my tables they are true or not but... CREATE TABLE Routes ( RouteID INT PRIMARY KEY, RouteName VARCHAR ( 2 5
Im not sure about my tables they are true or not but...
CREATE TABLE Routes
RouteID INT PRIMARY KEY,
RouteName VARCHAR NOT NULL,
RouteDescription TEXT
;
CREATE TABLE Stops
StopID INT PRIMARY KEY,
StopName VARCHAR NOT NULL,
Address VARCHAR
Latitude DECIMAL
Longitude DECIMAL
;
CREATE TABLE RouteStops
RouteID INT,
StopID INT,
StopSequence INT,
PRIMARY KEY RouteID StopID
FOREIGN KEY RouteID REFERENCES RoutesRouteID
FOREIGN KEY StopID REFERENCES StopsStopID
;
CREATE TABLE Buses
BusID INT PRIMARY KEY,
BusNumber VARCHAR NOT NULL,
Capacity INT,
DriverID INT
;
CREATE TABLE Drivers
DriverID INT PRIMARY KEY,
FirstName VARCHAR NOT NULL,
LastName VARCHAR NOT NULL,
LicenseNumber VARCHAR
;
CREATE TABLE Schedules
ScheduleID INT PRIMARY KEY,
RouteID INT,
BusID INT,
DriverID INT,
DepartureTime TIME,
ArrivalTime TIME,
PRIMARY KEY RouteID BusID, DepartureTime
FOREIGN KEY RouteID REFERENCES RoutesRouteID
FOREIGN KEY BusID REFERENCES BusesBusID
FOREIGN KEY DriverID REFERENCES DriversDriverID
;
CREATE TABLE Passengers
PassengerID INT PRIMARY KEY,
FirstName VARCHAR NOT NULL,
LastName VARCHAR NOT NULL,
Email VARCHAR
PhoneNumber VARCHAR
;
CREATE TABLE Payments
PaymentID INT PRIMARY KEY,
PassengerID INT,
RouteID INT,
BusID INT,
ScheduleID INT,
PaymentAmount DECIMAL NOT NULL,
PaymentDate DATE,
CardNumber VARCHAR NOT NULL,
CardHolderName VARCHAR NOT NULL,
ExpiryDate DATE,
PaymentMethod VARCHAR DEFAULT 'Card',
FOREIGN KEY PassengerID REFERENCES PassengersPassengerID
FOREIGN KEY RouteID REFERENCES RoutesRouteID
FOREIGN KEY BusID REFERENCES BusesBusID
FOREIGN KEY ScheduleID REFERENCES SchedulesScheduleID
;
CREATE TABLE BusLocations
BusID INT,
Latitude DECIMAL
Longitude DECIMAL
Timestamp TIMESTAMP,
PRIMARY KEY BusID Timestamp
FOREIGN KEY BusID REFERENCES BusesBusID
;
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 PassengersPassengerID
FOREIGN KEY RouteID REFERENCES RoutesRouteID
FOREIGN KEY BusID REFERENCES BusesBusID
FOREIGN KEY ScheduleID REFERENCES SchedulesScheduleID
;
CREATE TABLE TripStatus
TripStatusID INT PRIMARY KEY,
ScheduleID INT,
BusID INT,
CurrentStopID INT,
ArrivalTimeEstimate TIME,
Status VARCHAR
FOREIGN KEY ScheduleID REFERENCES SchedulesScheduleID
FOREIGN KEY BusID REFERENCES BusesBusID
FOREIGN KEY CurrentStopID REFERENCES StopsStopID
;
CREATE TABLE PaymentHistory
paymentid INT PRIMARY KEY,
passengerid INT,
scheduleid INT,
paymentdate DATETIME,
amountpaid DECIMAL
paymentmethod VARCHAR
fromstopid INT,
tostopid INT,
FOREIGN KEY passengerid REFERENCES Passengerspassengerid
FOREIGN KEY scheduleid REFERENCES Schedulesscheduleid
FOREIGN KEY fromstopid REFERENCES Stopsstopid
FOREIGN KEY tostopid REFERENCES Stopsstopid
;
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
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started