Database Fundamentals Report: ERD, Normalization, MS Access

This report provides a comprehensive overview of database fundamentals, focusing on the design and implementation of an airline database. It begins with an introduction to databases and the importance of Entity Relationship Diagrams (ERD) in visualizing relationships between entities. The report then details the creation of an ERD with entities such as airplanes, flights, airports, and customers, defining their attributes. It proceeds to explain the conversion of the ERD into relational tables, demonstrating the process of normalization up to the Third Normal Form (3NF) to minimize data redundancy. Furthermore, the report describes the creation of the database using Microsoft Access, including the implementation of distinct constraints. The report concludes with the creation of forms, queries, and reports within MS Access, showcasing practical application of database concepts. The report also includes references to relevant academic resources.

Database Fundamentals

Table of Contents
Introduction.................................................................................................................................................3
Overview.................................................................................................................................................3
Creation of ERD with entities as well as relationship..............................................................................3
Conversion of ERD diagram within set of relational tables till 3NF as well as dependency diagram......5
Create of database by making use of MS-Access through usage of distinct constraints..........................8
Creation of form, queries and report......................................................................................................11
Conclusion.................................................................................................................................................13
REFERENCES..........................................................................................................................................14

Introduction
Database refers to a data structure that is liable for storing organized information.
Basically, it is a systematic gathering of data which stores as well as manipulates data. Entity
relationship diagram illustrates relationship among entities which are stored within the database.
This provides a visualized view of entities or objects through which it becomes easy to
understand relationship among them. Different languages or methodologies are being utilized for
manipulating data so that it can be understood in a better way. This report is based on airline firm
which is headquartered in Sydney NSW and they operate their flights in Australia only. This
report comprises of ERD diagrams, relational database and database will be created through the
usage of Microsoft Access. In addition to this, form will be created, entries will be made within
database and distinct queries will be utilized for making alterations within table.
Overview
Airline firm furnish their services in Australia and have 15 airplanes which varies from
10 to 90 seats. Each flight possesses unique airplane number, type, capacity along with their
manufacturing company. They are being scheduled on daily basis and day for which flights are
being scheduled. Furthermore, the time is also different. Database comprises of all the details of
airport and customers who are opting for services that are being offered by them. Each ticket
which is being offered comprises details of flights and their customers.
Creation of ERD with entities as well as relationship
There will be four objects or entities within this scenario and each have different attributes
which differentiates them as well as formulate relationship among them. They are airplanes,
flights, airport and customers. The tables are illustrated beneath with respect to this.
Airplanes
Attributes Types
Airplane_Number Integer
Airplane_Type Integer
Manufacturing_Firm Varchar

Flights
Attributes Types
Airplane_Number Integer
Dept_Airport Char
Dept_Time Integer
Arrival_Airport Char
Arrival_Time Integer
Airport
Attributes Types
Airport_Code Varchar
Airport_Name Varchar
Airport_City Char
State Char
Customers
Attributes Types
Airplane_Number Integer
Cust_Id Varchar
Cust_Name Char
Cust_Title Char
Cust_DOB Varchar
Cust_Address Varchar
The different entities are illustrated above which possess by different attributes and on
that basis there exist relationship among them. It is being represented through usage of entity
relationship diagram (ERD). The structural diagram which is being used for database design,

comprises of symbols along with connectors that are utilized for visualization of major objects or
entities and inter-relationship between them is referred to as ERD. For the given scenario and
tables illustrated above ERD diagram is illustrated below:
Figure 1: ERD Data Representation
Conversion of ERD diagram within set of relational tables till 3NF as well as dependency
diagram.
While developing the schema of RDBMS (relational database), one of the important
aspects that need to be taken under consideration is to ensure that duplication must be minimized
as much as possible. This would help in reducing storage amount for storing data along with,
avoiding unnecessary conflicts of information which may arise due to multiple copies of same
constraints. Therefore, in designing such database schema, Normalization technique can be used,
which would help in ensuring that minimum data is duplicated. The core main concept of this
technique is to segment the table into sub-tables to minimize chance of duplication. In this
regard, normalization in database can be done in three ways – 1NF, 2NF and 3 NF where all

processes used to optimize the database more efficiently, for saving storage and reducing
redundancies.
1NF (First Normal Form)
Under this technique, each cell of a table contains only one value and each row stores
only unique information that helps in avoiding repetition. From the above mention data,
normalization of database can be done in following way –
Table 1:
Airplane Number Airplane Type Manufacturing Address
1 22 Dubbo Airport
2 31 Darwin International Airport
3 11 Brisbane Airport
4 10 Dubbo Airport
Table 2:
Airplane
Number
Departure
Airport
Departure time Arrival Airport Arrival time
1 Darwin
International
Airport
1:06:24 AM Brisbane Airport 2:02:34 AM
2 Adelaide Airport 4:05:22 AM Newcastle
Airport
5:05:02 AM
3 Brisbane Airport 2:04:32 AM London Airport 6:05:22 PM

Table 3:
Airplane
Number
Airport Code Airport name Airport
City
State
1 ANO Darwin International
Airport
Darwin Queensland
2 UTY Brisbane Airport Brisbane New South
Wales
3 JTE Adelaide Airport Adelaide South
Australia
Table 4:
Airplane
Number
Customer ID Customer
name
Customer
Type
Customer
DOB
Customer
Address
1 11220 Jhon Smith First class 6/8/1998 14/16, Main
street road,
Brisbane
2 11221 Jessica Clarie Business
Class
12/8/1992 52,
Morningside,
Adelaide
2 11222 Alice Cooper Economy
Class
6/21/2001 21,Main
Street,
London
Thus, tables 1 and 2 can be normalized in first form in following way –
1NF Table

Airplane
Number
Departure Airport
1 Darwin International
Airport
2 Adelaide Airport
3 Brisbane Airport
While all tables can be summarized in one form as –
1NF Table
Airplane
Number
Customer Id
1 11220
2 11221
2 11222
2NF (Second Normal Form)
To normalize the database in second normal form, two conditions are required to be met
– table must be constructed in first normal form and primary key of table also need to be
composed in one form only. Along with this, first column represents the primary key, which
includes a set of columns that identifies a row uniquely. As per above discussion, many
customers have same name and onboard in same flight with same destinations. Therefore, to
distinguish customers, every customer given a unique id, as per above examples 1NF can be
changed into 2NF in following way –
Airplane
Number
Customer ID Airport code
1 11220 ANO
2 11221 UTY
2 11222 UTY

Customer
name
Airplane
number
John Smith 1
Brizell 2
John Smith 2
Gilchrist 1
Since, this table doesn’t represent the uniqueness, therefore, broken down the above table into
two tables achieve second normal form, in following way –
Customer name Customer id
John Smith 1131
Brizell 1123
Gilchrist 1132
Airplane Number Customer id
1 1131
2 1123
2 1132
3NF (Third Normal Form)
A table can be designed in 3NF if data represent in 2NF and transitive functional
dependency of attribute (non-prime) on super key should be removed. To create 3NF, let take an
example –
Cus. ID Cus_name Cus_ zip Cus_state Cus_city
111 John 23224 Qeensland Brisbane
222 Gilchrist 24425 New South
Wales
Sydney
333 John 25234 South Adelaide

Australia
444 Norji 34425 Tasmania Hobart
In 3NF form, the above data can be represented in following manner –
Cus. ID Cus. Name Cus. Zip
111 John 23224
222 Gilchrist 24425
333 John 25234
444 Norji 34425
Customer Zip table –
Cus. Zip Cus_state Cus_city
23224 Qeensland Brisbane
24425 New South Wales Sydney
25234 South Australia Adelaide
34425 Tasmania Hobart

Create of database by making use of MS-Access through usage of distinct constraints
Table 1: Airplanes

Table 2: Flights
Table 3: Airport