Data Model of (Spatial) Database

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
Image 1: Conceptual Database 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
Image 2: Logical DB Model created in StarUML.

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.

Sub-tasks
1. Universe of discourse
From the statement provided in the assignment document, we need to mentally visualize the entities within the universe of discourse. Starting from the actual real-world entities such as busses or employees, we need to imagine all other important entities relevant for modeling this scenario. This includes more abstract concepts such as the bus stops or the connection of these bus stops, moving on to the concept of a schedule where a certain location needs to be serviced at a certain time. Imagining these entities, writing them down and creating relationships between those in a conceptual model can give a good first insight into the universe of discourse, without having to worry too much about data types and the actual information we want to store in these tables.
 
2. Additional Questions
Based on the statements provided by the client, the models provided cover more or less the minimum entities within this universe of discourse. The only additional entity for now is the inspector who controls the tickets of passengers at certain stops, but it would be interesting to know which other entities might be modeled at the same time. Is it interesting to monitor the timeliness per bus driver for example? Or checking the timeliness for each line, figuring out which stops introduce the most delays into the system? Do we maybe also monitor the odometer of busses or the cumulative cost of repairs per bus. Additionally, do we want that information in this database or maybe in a separate database? Is it maybe also worth introducting time into the DB, meaning that we monitor the real-time position of busses and the scheduled stops in order to provide screens a the stations, listing the arrival times of the next connections.

Discussion

Leave a Reply

Your email address will not be published. Required fields are marked *

Further Reading
Recent Updates