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.

 

Entity Routes Vehicles Stations/Stops Trips Passengers Ticket Sales Attributes Route ID, Name, Start Point, End Point Vehicle ID, Type, Capacity Station ID, Location, Name Trip ID, Route ID, Vehicle ID, Departure Time, Arrival Time Passenger ID, Name, Frequent Routes Ticket ID, Passenger ID, Trip ID, Price, Purchase Time Relationships 1 to N with Stations/Stops 1 to N with Trips 1 to N with Trips N to 1 with Routes N to 1 with Routes N to 1 with Vehicles 1 to N with Ticket Sales 1 to N with Ticket Sales N to 1 with Trips N to 1 with Passengers