Introduction
Imagining a relational database for a public bus operator, conceptual and logical databases model are created. The goal is to visualize the entities and their relationships within this scenario.
Conceptual Model
The conceptual model gives a more broad and informal overview, highlighting mainly the real world objects of the universe of discourse and their general relations. In this example, it gives a broad overview over the entities that will be more deeply analysed in the logical database model.
Logical Database Model
The Logical DB model gives a more in-depth overview of the universe of discourse, defining columns within each entity/table including the expected data types.
Let’s start with the employee table, where each employee has a unique ID (which also functions as the primary key), as well as the name, entry data and the salary. Most importantly, the “Job” column can be filled with one of the following entries: Admin (Administrative Worker), Driver, Inspector (Controlling Tickets), and Maintenance Worker. A certain type of employee, the inspector, can also be assigned to a certain bus stop where ticket controls are executed.
For each of the busses, wich are stored in the next table, one employee is always defined as the responsible, wether that be the driver, the mechanic maintaining it, or a responsible worker while the bus is in storage. The bus additionally has a field for the age as the bus as well as the Line that the bus is currently assigned to, which is nullable since the bus can also be in storage or in service. Also, the status is defined with the three possible keywords.
Moving forward to the Bus Lines, which contain the line number and a geometry of the route, is connected to the Busses via a one to many relationship. One bus can only be assigned to a single line, while multiple busses can service a single line. The lines are also connected to the Schedule table, which holds tables for each lines and stops, containing the arrival times as well as the Line numbers and Bus stop IDs. Since they are all marked as Primary Keys, the Schedule has to be imagined as a multitude of tables for each bus stop and line combination.
The Stops Table contains IDs as well as the names of the bus stops and additionally a point geometry and a foreign key that saves which line the stop is a part of.
Moving on to the Customer-side of the model, the Customer table holds information on each customer, such as ID, name, DoB as well as if they are a loyalty card holder and the ticket they purchased, including the point in time when that ticket loses validity. The ticket is marked as a foreign ID, connecting each ticket a customer holds with a certain ticket type from the Tickets table. the ticket, as well as the price paid and the validity of said ticket can be null, since a customer might neglect to buy a ticket and therefore be caught by the inspector without a valid ticket.
The ticket table itself stores information on the types of tickets that are sold, including it’s price. The final ticket price is defined at the customer level since 20% might be discounted if the boolean value for the loyalty card might be “true”.
Database requests
Answering questions such as the revenue per line as well as the timeliness are now answered by database requests.
Since the lines are connected to the customers, we can return how many customers, holding which tickets including possible loyalty card benefits, take each line. Additionally, querying for timeliness can be done by querying each stop’s schedule time and comparing those to the actual time a bus arrived at said stop.
