I am working the SQL query code now. I made query code with the below information. However,
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.
International Marketing And Export Management
ISBN: 9781292016924
8th Edition
Authors: Gerald Albaum , Alexander Josiassen , Edwin Duerr