Answered step by step
Verified Expert Solution
Link Copied!

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(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,
PRIMARY KEY (RouteID, BusID, DepartureTime),
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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Big Data Fundamentals Concepts, Drivers & Techniques

Authors: Thomas Erl, Wajid Khattak, Paul Buhler

1st Edition

0134291204, 9780134291208

More Books

Students also viewed these Databases questions