Identified Entities And Their Attributes Information Technology Essay

✅ Paper Type: Free Essay	✅ Subject: Information Technology
✅ Wordcount: 3425 words	✅ Published: 1st Jan 2015

Reference this

Share this: Facebook Twitter Reddit LinkedIn WhatsApp

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.

Get Help With Your Essay

If you need assistance with writing your essay, our professional essay writing service is here to help!
Find out more about our Essay Writing Service

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

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

100

John

98787787, 8898987988

none

101

Steve

89898987,98989898

–

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_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

100

John

98787787, 8898987988

none

101

Steve

89898987,98989898

–

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

1001

100

24-Mar-10

–

1002

101

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:

Name

Address

John

32 James park

Mark

45 green avenue

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:

Car No

Driver Name

Driver Address

HGH 7678

Austin

23, Qutub Enclave

HKK 101

Preety

44, Hilton

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

1001

100

24-Mar-10

–

001

Airport

–

1002

101

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.