I am working the SQL query code now. I made query code with the below information. However,

Fantastic news! We've Found the answer you've been seeking!

Question:

I am working the SQL query code now. I made query code with the below information.

However, I have some difficulty to make query code when one to many relationship are needed.

How could I do the query code?

1. Routes:
• Attributes: Route ID (unique identifier), Name, Start Point, End Point.
• Description: Represents the various transit routes in the system. Each route has a unique identifier and defined start and end points.

Routes (
RouteID INT PRIMARY KEY,
Name VARCHAR(255),
StartPoint VARCHAR(255),
EndPoint VARCHAR(255)
);

2. Vehicles:
• Attributes: Vehicle ID (unique identifier), (e.g., bus, train), Capacity.
• Description: The physical means of transportation. Each vehicle is associated with a type and has a certain passenger capacity.

CREATE TABLE Vehicles (
VehicleID INT PRIMARY KEY,
Type VARCHAR(255),
Capacity INT
);

3. Stations/Stops:
• Attributes: Station ID (unique identifier), Location, Name.
• Description: The locations where passengers can board or alight from vehicles. Each station or stop serves one or more routes.

CREATE TABLE Stations (
StationID INT PRIMARY KEY,
Location VARCHAR(255),
Name VARCHAR(255)
);

4. Trips:
• Attributes: Trip ID (unique identifier), Route ID, Vehicle ID, Departure Time, Arrival Time.
• Description: A single journey on a particular route, using a specific vehicle. Each trip follows a schedule, with set departure and arrival times.

CREATE TABLE Trips (
TripID INT PRIMARY KEY,
RouteID INT,
VehicleID INT,
DepartureTime TIMESTEMP,
ArrivalTime TIMESTEMP,
FOREIGN KEY (RouteID) REFERENCES Routes(RouteID),
FOREIGN KEY (VehicleID) REFERENCES Vehicles(VehicleID)
);

5. Passengers:
• Attributes: Passenger ID (unique identifier), Name, Frequent Routes.
• Description: Individuals who utilize the transportation system. Passengers might have preferences or frequent routes they use.

CREATE TABLE Passengers (
PassengerID INT PRIMARY KEY,
Name VARCHAR(255),
FrequentRoutes VARCHAR(255) -- Assuming FrequentRoutes can be a comma-separated list of route IDs
);

6. Ticket Sales:
• Attributes: Ticket ID (unique identifier), Passenger ID, Trip ID, Price, Purchase Time.
• Description: Records of tickets sold for trips. Each ticket is linked to a passenger and a specific trip.

CREATE TABLE TicketSales (
TicketID INT PRIMARY KEY,
PassengerID INT,
TripID INT,
Price DECIMAL(10, 2),
PurchaseTime DATETIME,
FOREIGN KEY (PassengerID) REFERENCES Passengers(PassengerID),
FOREIGN KEY (TripID) REFERENCES Trips(TripID)
);

Conceptual Diagram Overview: The conceptual ERD for a Public Transportation System

Analysis would illustrate the following relationships:
• Routes to Stations/Stops: A one-to-many relationship, as each route will have multiple stations/stops.
• Vehicles to Trips: A one-to-many relationship, as each vehicle can be used for multiple trips.
• Routes to Trips: A one-to-many relationship, as each route can have multiple trips scheduled.
• Trips to Ticket Sales: A one-to-many relationship, since each trip can have multiple tickets sold.
• Passengers to Ticket Sales: A one-to-many relationship, as passengers can purchase multiple tickets for different trips.