School Project: Database Design

This post is on the database design for a school project I am currently working on. We are going to look at the design process for an airline database so lets get started.

Every good application needs a user story for the front end, backend and even for the design of the database so as to enable those working on the app to come up with a product that users will find appealing to use.

What is a user story?

It is basically an informal, general explanation of a software feature written from the perspective of the end user. What this mean is looking at the every feature or part of a software from a user's point of view. In this regard, I am going to begin my database design with a user story. The end users in this case is the airline and its passengers. The user story is in two parts that is from the airline's point of view and from the passenger's point of view. let's begin with the airline's view point.

For the Airline

1. An airline wants to keep track of all of their airplanes so that they know how to allocate them for flights. We also want to have a record of the capacity of each airplane so that we will know the booking status (that is if a flight is completely booked or not) of each flight on any of our airplanes.
2. An airline will want to keep information about their passengers so that they will be able track each passenger's luggage and send them information on things happening at the airport that might be of importance to them.
3. An airline will want to keep information about all their workers(pilots, cock-pit engineers, air hostesses, security personnel and other auxiliary staff) so that we will be able to identify absences and other issues easily.
4. An airline might want to have information on all the planes that are either landing at or leaving their airport, when and where they will be coming from for planes coming into the airport.
5. An airline may want to keep track of all their airplane model and their manufacturers so that they can know where to get help if a plane has a malfunction.

For the Passenger

1. A passenger wants to have information on all flights that are available and whether they are economic or business class so that they will choose flights within their budget plan.
2. A passenger also wants to have a secure, fast and easy way to buy flight tickets so as to enable them fly in comfort.

After developing that elaborate user story for the database design we will go on to produce data models for the easy and on point implementation of the database.

What is a data model?

It is way of defining how the logical structure of a database is presented. We are going to use two different kinds of data models for the database. The models we are going to use are; the ER(Entity-Relationship) model and the Relational data model. Let's begin with ER model.

ER Model

Here, data is modeled in a way that is similar to how humans perceive data. Below is an the ER diagram for the airline database that I am working on.

Let me breakdown this ER diagram for you.

1. An airplane is uniquely identified by its Registration Number(RegNo), so we are going to use it to as our primary Key (PK).
2. Since no two passengers can have the same email address, we can use the email-address attribute of the passenger entity as the PK for this entity.
3. A Flight is uniquely identified by a flight number (fNo) so we can use this as a primary key in the flight table. The departure and destination airports are captured by the From and To attributes.
4. An airplane can be involved in any number of flights while each flight uses exactly one airplane, so the Flies relationship between Airplane and Flight is a many-to-one relationship and because a flight cannot occur if there is no airplane, the Flight entity participates fully in the Flies relationship.
5. A passenger is allowed to book more than one flight while a flight can be booked by many passengers reason why the Booking entity has a one-to-many relationship with the Passenger and Flight entities.

Relational Model

The relational model represents data as relations or tables. We easily transition from an ER to a Relational data model. Below is an image of the Relational model of our database.

Again let me debunk the relational model. In our relational data model there are four tables or relations, one for each entity from the Entity-Relationship Diagram we saw above. This model depicts the manner in which data is stored in relational or SQL databases. In the image above each table has one or more attributes and the datatypes and data constraints are also specified on our tables to ensure consistency. We have also specified the columns we want to use as our primary key.

And that's that about the database design. From here, it is much easier to implement the database. Thank you for checking out this post. If you have any question I'll be happy to help just drop your question in the comment section.