Late Travel has inaugurated a new travelling business between the airport and the lost town. The travelling options they offer can either their own chauffeured car or a coach service. A database is designed to assist the administration of this new service. The database contains the information pertaining to staff information, customer information, car or couch availability, customer journey and payment details etc. An E-R model is developed based on the above case study and it is normalised into 3NF. A relational database based on the normalised E-R model is produced which demonstrate that your database stores the required data and relationship between different entities.
Table of Contents
Table of Figures
ER Model: conceptual model and logical models
Entity relationship model is a database modelling method used to represent the conceptual data. Conceptual data model is prepared when an information system is based on a database, which at the later stage is mapped to logical data model such as relational model which is in turn mapped to the physical model.
Identified Entities and their Attributes
An entity has a distinct existence which is uniquely identified. An entity can be a house, employee or a car. Entities are described by a set of properties called attributes. Each entity must have the values for its attributes which is uniquely identifiable from other entities in a particular entity set. Following are the entities and its attributes for the Late Travel (LT). Below are the Entities (customer, employee and booking) and there attributes.
Attributes of an employee are shown in the figure below. Here Employee_id is a primary key which is used to uniquely identify each employee.
Employee
Employee_id
Employee_address_id
Employee_name
Employee_phone
Other_employee_details
Figure : Entity (Employee) and its attributes
Attributes of the customers are shown in the figure below. Here Customer_id is a primary key which is used to uniquely identify each customer.
Customer
customer_id
payment_id
customer_name
Customer_address
Sex
customer_phone
Date_of_birth
Date_became_customer
Figure : Entity (Customer) and its attributes
Attributes of a booking is shown in the figure. Here booking_id is a primary key which is used to uniquely identify each booking by the customer. Customer_id is a foreign key which is mapped to Entity (customer) and outcome_code will provide information whether the booking is cancelled or not. Status_code field will give information whether status is confirmed or waiting, for the customer for particular booking.
Booking
booking_id
customer_id
Outcome_code
Status_code
date_of_booking
Car_id
Journey_code
Booking_details
Figure : Entity (booking) and its attributes
Identified Relationships between Entities
A relationship is an association among the entities. Relational database model represents the set of relationship of same type. Figure 4 shows the order relationship between a customer and booking.
Customer
customer_id
payment_id
customer_name
Customer_address
Sex
customer_phone
Date_of_birth
Date_became_customer
Booking
booking_id
customer_id
Outcome_code
Status_code
date_of_booking
Car_id
Journey_code
Booking_details
order
Figure : Relationship set of customer and booking
Figure 4 represents the idea that a customer orders a booking for hiring a car or equivalently, a booking is ordered by the customer. Here the another question comes which is whether a customer can order more than one booking or whether booking can be ordered by more than one customer. The question raised is that of cardinality. In fact the example above depicts that customer can order zero or more booking and likewise, a booking may be ordered by zero or more customer. An arrow is used to connect the entities when one-to-one or one-to-many relationship is modelled. Below figure depicts how arrows are used to represent cardinality:
One
Many
One
One
Many
Many
Figure : Cardinality
In the previous example it is reasonable to expect that a customer can exist without having order a single booking, and conversely, that booking exist even though none have been ordered yet. Therefore none of the entities in an entity set may participate in a particular relationship is partial participation. In contrast to this, an entity set may participation in a relationship. Diagrammatically, this is represented by connecting the entity set to the relationship set with a double line. Figure 6 shows the relationship of an agent to a Late Travel agency which combines both cardinality and participation. It is clearly stated that Late Travel agency can employs zero or more agents, and that an agent must be employed by only Late Travel agency.
Late Travel agency
Employs
Agent
Figure : Cardinality and participation constraint
Conceptual model
A conceptual model is a modelling of concepts and relationship. This term is used from a long time in a database design. It is independent of DBMS and allows easy communication between end users and the developers. Below is the conceptual model of Late Travel showing the entities, attributes and there relationship.
Customer
customer_id
payment_id
Customer_name
Customer_phone
Customer_address
Date_of_birth
Sex
Date_bacame_customer
Booking
booking_id
customer_id
car_id
outcome_code
status_code
journey_code
date_of_booking
booking_detail
Payment
Payment_id
booking_id
payment_amount
payment_date
other_details
Ref_booking_status
Status_code
Status_description (eg: confirmed/waiting)
Ref_booking_outcome
outcome_code
outcome_description (eg: cancelled/ok)
Car_details
Car_id
Driver_id
Seats_avail
Car_no
Driver_details
Driver_id
Driver_name
Driver_address
Driver_phone
Journey_details
Journey_code
Customer_id
Place_name
Renting_rate
Other_details
Order
Goes for
Makes
has
consist of
has
Consist of
Figure : Conceptual model
Above figure shows the conceptual model of Late Travel. The primary objective of the conceptual model is to establish a base understanding of the objects in the system. However it is necessary to create an association among different entities and select their multiplicity.
Logical model
Logical model represents the abstract structure of information. It is used to display the business process that seeks to capture the importance things usually in the form of a diagram. Some of the benefit of using the logical model is to improve the business process, facilitates reuse of data, gather functional information and decrease the system design cost and time. Figure 7 is already logical as it shows the attributes and relationship among the different entities. All attributes are appearing in a data store. Above diagram is showing the business entities, their inter-relationships, and the cardinality i.e. each customer can order one or more booking of a car for their journey and same way a booking can be ordered by one or more customers-these relationships can be viewed from both ends.
Is database is storing the required data?
In order to identify whether the database is storing the necessary data, following are the tasks that are needed to be performed in an iterative manner.
Identify the entity type.
Identify the attributes of the entity.
Application of naming convention.
Identify relationship among entities.
Data model patterns need to be applied.
Assign keys such as primary, unique and foreign key.
To reduce data redundancy it should be normalized.
All these tasks are being performed on this case study Late Travel (LT). Therefore required data is being stored in the database. And as it is already stated that above tasks are needed to be performed in an iterative manner, therefore it is a continuous process.
Normalized E-R Model to 3NF
Normalization is the way of ensuring that a relational database structure is free from insertion, update and deletion anomalies that might lead to loss of integrity of data. In 1970s E.F. Codd and his team at IBM introduced the concept of relational databases, proposed normalization through several normal forms.
Below tables are assigned with the keys.
Customer
customer_id (PK)
payment_id (FK)
Customer_name
Customer_phone
Customer_address
Date_of_birth
Sex
Date_bacame_customer
Booking
booking_id (PK)
customer_id (FK)
car_id (FK)
outcome_code (FK)
status_code (FK)
journey_code (FK)
date_of_booking
booking_detail
Payment
Payment_id (PK)
booking_id (FK)
payment_amount
payment_date
other_details
Ref_booking_status
Status_code (PK)
Status_description (eg: confirmed/waiting)
Ref_booking_outcome
outcome_code (PK)
outcome_description (eg: cancelled/ok)
Car_details
Car_id (PK)
Driver_id (FK)
Seats_avail
Car_no
Driver_details
Driver_id (PK)
Driver_name
Driver_address
Driver_phone
Journey_details
Journey_code (PK)
Customer_id (FK)
Place_name
Renting_rate
Other_details
Order
Goes for
Makes
has
consist of
has
Consist of
Figure : Tables of Late Travel
First Normal Form (1NF)
A relational table X is in 1NF (First Normal form), if it meets certain set of criteria. This criterion ensures that each attribute is single-valued with atomic values. In other words table is free from repeating groups. Now if we check whether the logical model discussed above is in 1NF or not. Let’s discuss the employee and customer table.
Employee
employee_id (PK)
employee_address_id (FK)
employee_name
employee_phone
other_employee_details
Customer
customer_id (PK)
payment_id (FK)
Customer_name
Customer_phone
Customer_address
Date_of_birth
Sex
Date_became_customer
Driver_details
Driver_id (PK)
Driver_name
Driver_address
Driver_phone
Figure : Employee and customer table
As 1NF says that there should be no repeating group but in this case phone number of employee or customer can be more than one. Therefore this relational table is not in first normal form e.g.
Employee_id
Employee_add_id
Employee_name
Employee_phone
Other_emp_details
1
100
John
98787787, 8898987988
none
2
101
Steve
89898987,98989898
–
3
102
Mark
98989898898
–
Here, column employee_phone doesn’t contains the atomic value therefore in order to make it 1NF separate entity such as customer_phone_num and employee_phone_num is needed to be defined.
Employee
employee_id (PK)
employee_address_id (FK)
employee_name
other_employee_details
Customer
customer_id (PK)
payment_id (FK)
Customer_name
Customer_address
Date_of_birth
Sex
Date_became_customer
Employee_phone
employee_id (PK)
phone_num
Customer_phone
customer_id (PK)
phone_num
Driver_details
Driver_id (PK)
Driver_name
Driver_address
Driver_phone
Driver_phone
driver_id (PK)
phone_num
Figure : Employee, customer and driver table
Now all the tables are in First Normal Form as all the columns are having the atomic values.
Second Normal Form (2NF)
A relational table X is in Second Normal Form when table is in 1NF. A table that is in 1NF must meet the criteria to qualify for the second normal form. This criterion ensures that set of attributes X is functionally dependent on set of attributes Y.
For instance, let’s look at the booking table:
Employee_id
Employee_add_id
Employee_name
Employee_phone
Other_emp_details
1
100
John
98787787, 8898987988
none
2
101
Steve
89898987,98989898
–
3
102
Mark
98989898898
–
Booking
booking_id (PK)
customer_id (FK)
car_id (FK)
outcome_code (FK)
status_code (FK)
journey_code (FK)
date_of_booking
booking_detail
Figure : Booking table
Booking_id
Customer_id
Car_id
Outcome_code
Status_code
Journey_code
Date_of_booking
Booking_details
1
1001
100
1
3
2
24-Mar-10
–
2
1002
101
2
4
3
25-Mar-10
–
Here as we can see that, none of its non prime attributes are functionally dependent on a subset of a candidate key. Therefore the tables are already in second normal form. Following are the tables that are designed to store the information regarding customer, employees, car, journey and booking. These tables meet the criteria to be second normal form. Hence there is no need to alter the tables.
Third Normal Form (3NF)
A relation table X is in second normal form when every non prime attribute R is non-transitively dependent on every key of R. In other words a relationship database is in 3NF if it is in 2NF and no non-primary attribute is functionally dependent on another non primary key.
Let’s look at one of the table journey_details of Late Travel.
Journey_details
Journey_code (PK)
Customer_id (FK)
Place_name
Renting_rate
Other_details
Figure : Journey table
Journey_code
Customer_id
Place_name
Renting_rate
Other_details
001
331
Airport
500
–
002
332
Airport
500
–
Here as we can see that renting rate depends upon the place, therefore this table is not in third normal form. In order to make it in third normal form two separate tables or entities would be created- One containing the journey code, customer_id, place_id and other_details and the other containing the place_id, place_name and renting_rate. Below figure is showing the two tables:
Journey_details
Journey_code (PK)
Customer_id (FK)
Place_id (FK)
Other_details
Place_details
Place_id (PK)
Place_name
Renting_rate
Figure : Journey and place table
Now the E-R model is normalized into third normal form (3NF). Now all the tables contain the atomic values and non primary attributes are functionally dependent on primary key. And lastly no non-primary key attribute is functionally dependent on another non-primary key.
SQL Queries
Below are the different SQL queries which will be mostly used during the management of Late Travel database. Query result is displayed and explained below:
Select customer_name, customer_address, date_of_birth, sex, date_became_active from Customer
WHERE Date_became_customer > ‘2010-01-01’
Description: This query will display all the active customers since 1st Jan 2010.
Why Needed: This query is important because it displays the list of new customers added from a particular date.
Output: Expected Output is:
customer_name
customer_address
date_of_birth
sex
date_became_active
John
35, paris road
1988-09-06
male
2010-02-02
Smith
78, dahiya bhavan
1978-07-08
male
2010-03-04
David
109, green park
1980-08-08
male
2010-01-09
Select emp.employee_id ID, emp.employee_name Name, addr.address Address from Employee emp, emp_Address addr
WHERE emp.employee_id = addr.employee_id
Description: This query will display all the employees with their details.
Why Needed: This query is important because it displays the list of all employees. It might possible that at any instance of time it is required to get the information about the employees working in Late Travel.
Output: Expected Output is:
ID
Name
Address
1
John
32 James park
2
Mark
45 green avenue
3
Steve
14 Vikas puri
Select car.car_id ID, car.car_no Car No., driver.driver_name Driver Name, driver.driver_address Driver Address,
FROM Car_details car, Driver_details driver
WHERE car.driver_id = driver.driver_id
Description: This query will display the information about the particular car like who is the driver, car number etc.
Why Needed: This query is important because it is required to get the information about the particular car and the driver who is responsible of handling it.
Output: Expected Output is:
ID
Car No
Driver Name
Driver Address
1
HGH 7678
Austin
23, Qutub Enclave
2
HKK 101
Preety
44, Hilton
3
NMN 420
Lisa
88, oberios
Select * FROM Booking
RIGHT JOIN Journey
On Booking.customer_id =Journey.customer_id
WHERE date_of_booking = ‘2010-03-24’
Description: This query will display all the booking for 24 Mar 2010 with details of journey.
Why Needed: This query is the basic one as it shows the booking for a particular date. It will display all the information related to booking for whether car is available or not or booking is confirmed or pending.
Output: Expected Output is:
Booking_id
Customer_id
Car_id
Outcome_code
Status_code
Journey_code
Date_of_booking
Booking_details
Journey_code
Place_name
Other_details
1
1001
100
1
3
2
24-Mar-10
–
001
Airport
–
2
1002
101
2
4
3
24-Mar-10
–
002
Airport
–
Critical Reflection
Whether we use a file or relational database, depends on the nature of the data and how we conceptualize it. There are some major problems and issues arise during the development of the database majorly of data integrity, performance and flexibility. Normally many of the people result into development of database which contains redundant data or insertion, deletion and modification anomalies.
In order to understand the normal form, four terms that must be understood are:
Dependency
Key
Domain and
Restriction
Designing of database plays an important role in integrity and reliability of the data, therefore it is necessary to carefully inspect the database structure, entities and their relationship.
Conclusion
A database is designed for Late Travel (LT) which is used to store the information related to employee, customer, journey, payment, car and driver details. A conceptual and E-R diagram is developed based on the above case study, showing the relationship among different entities. All the tables are normalized into the third normal form and then the set of queries are fired to show the retrieval of information from database.
Cite This Work
To export a reference to this article please select a referencing style below:
