Database Management System Project: ER Diagram and SQL Implementation

Desklib provides solved assignments and past papers to help students learn.

Contents
Introduction......................................................................................................................................2
Solution A: ER Modeling................................................................................................................3
Business Rules.............................................................................................................................3
Entities and Attributes.................................................................................................................3
ER Diagram.................................................................................................................................4
Solution B: Data Dictionary............................................................................................................5
Solution C: Dependency Diagram...................................................................................................8
Solution D: SQL Statements..........................................................................................................11
Create Commands......................................................................................................................11
Foreign key Commands.............................................................................................................13
Default Constraint......................................................................................................................13
Insert table Commands..............................................................................................................14
Conclusion.....................................................................................................................................16
References......................................................................................................................................17
Figure 1: ER Diagram......................................................................................................................4
Figure 2: Dependency Diagram.......................................................................................................8
Figure 3: Table 1..............................................................................................................................9
Figure 4: Table 2..............................................................................................................................9

Introduction
The assessment is based on the database management system in which there is a need to create
an ER diagram and the business rules that are used while creating the diagram for the given
scenario. Then the report contains a Data Dictionary that is created and provided here. On
moving further the report consist of a dependency diagram that normalized the complete table
that is drawn using the ER and at last the report has the SQL statements that are included in the
file. The complete assignment provides a clear understanding of the database management
system with the diagrams that are attached in the report.

Solution A: ER Modeling
Business Rules
There are various business rules that are used while the creation of the ER diagram. They are
listed as:
 Entity Pilot and Flight are interconnected by one-to-many relationship as one pilot can fly
one flight at one time.
 Pilot_NO is the primary key here as for every pilot there must be a unique identification.
 In this Pilot table, designation entity is added as the pilot may be a trainee or experienced
so the designation can explain the pilot experience (McHugh et al., 1997).
 One flight can have multiple passengers so the entities Flight and Passengers are
interconnected by a one-to-many relationship with each other.
 The primary key in the table flight is F_NO that will help in unique identification of the
flights that are available.
 One Pilot can fly one flight at a time so the relationship among both entities is one-to-one
relationship (Coronel and Morris, 2016).
 Airplane is interconnected with Maintenance by one-to-many relationship as one airplane
can take multiple maintenance procedure.
 Passenger table has the details of the passenger including the fare and reserve date of
their booked flights (Astrahan et al., 1976).
Entities and Attributes
There are five different entities comprised of various attributes and they are listed as:
1. Entity: Pilot
Attributes: Pilot_NO, FirstName, LastName, DOB, Hiring Date, Designation,
Total_Flight_Flown.
2. Entity: Flight
Attributes: F_NO, Date, A_NO, Arrival_Time, Departure_Time.

3. Entity: Passenger
Attributes: P_NO, F_NO, P_Name, P_Address, P_Contact, Flight_Fare, Flight_ReserveDate
4. Entity: Airplane
Attributes: A_NO, Model, Manufacture_Name, Passenger_Capacity, Built_Year
5. Entity: Maintenance
Attributes: M_ID, A_NO, Procedure_Name, Frequency, Location, Duration, Date
ER Diagram
The ER diagram will display all the entities along with the relationship that exists between them
and the diagram given is:

Figure 1: ER Diagram
Solution B: Data Dictionary
The data dictionary is provided here with all the five entities and their attributes:
Table Name Attribute Attribute
Description
Data
Type
Mandatory PK/FK Foreign
key
Reference
Table
Pilot Pilot_NO Unique
Identifier of
CHAR Y PK

Pilot
FirstName First Name
of Pilot
CHAR Y
LastName Last Name
of Pilot
CHAR Y
DOB Date of Birth
of Pilot
DATE Y
Hiring Date Date of
Hiring of
Pilot
DATE Y
Designation Position of
the Pilot
CHAR Y
Total_Flight_Flown Number of
flights flown
by the pilot
INT Y
Flight F_NO Unique
Identification
of Flight
CHAR Y PK
Date Unique
Identification
of Flight
DATE Y PK
A_NO Airplane
Number
CHAR Y FK Airplane
Arrival_Time Time of
arriving on
the flight
CHAR Y
Departure_Time Time of
departure of
the flight
CHAR Y

Passenger P_NO Unique
Identification
of Passenger
CHAR Y PK
F_NO Flight
Number
identification
CHAR Y FK Flight
P_Name Name of the
Passenger
CHAR Y
P_Address Address of
the
Passenger
CHAR Y
P_Contact Contact of
the
Passenger
INT Y
Flight_Fare The fare paid
for flight by
the passenger
CHAR Y
Flight_ReserveDate Date of
reservation
of flight by
the
Passenger
DATE Y
Airplane A_NO Unique
Identification
of Airplane
CHAR Y PK
Model Model name
of the
Airplane
CHAR Y
Manufacture_Name Name of CHAR Y

Manufacturer
of Airplane
Passenger_Capacity Number of
passengers
airplane can
carry
INT Y
Built_Year Year in
which the
Airplane is
built,
whether it is
new or old.
INT Y
Maintenance M_ID Unique
identification
of
Maintenance
procedure
CHAR Y PK
A_NO Identification
of the
airplane
CHAR Y FK Airplane
Procedure_Name Name of the
procedure
event
CHAR Y
Frequency Frequency of
the
procedure
event
CHAR Y
Location Location of
the
procedure
CHAR Y

event
Duration Duration of
the
procedure
event
CHAR Y
Date Date of the
procedure
event
DATE Y
Solution C: Dependency Diagram
Dependency diagram of the table Flight is shown here as there exist a partial dependency that is
observed and the it is removed by dividing the table into two different tables:
Figure 2: Dependency Diagram
Table 1
Primary Key: 2
Foreign Key: 0
Normal Form: 2NF

Figure 3: Table 1
Table 2
Primary Key: 1
Foreign Key: 0
Normal Form: 3NF
The above table is divided in above table to eliminate partial dependency and the other table is
provided below:
Figure 4: Table 2
Table 3
Primary Key: 1
Foreign Key: 0
Normal Form: 3NF
As the partial dependency is eliminated, the complete table is normalized as the transitivity
dependency does not exist in the table.

Solution D: SQL Statements
Create Commands
Create table Pilot(
Pilot_NO char(40) Not Null Primary Key,
FirstName char(40) Not Null,
LastName char(40) Not Null,
DOB date Not Null,
Hiring_Date date Not Null,
Designation char(40)Not Null,
Total_Flight_Flown int Not Null
);
Create table Flight(
F_NO char(40) Not Null Primary Key,
Date date Not Null,
A_NO char(40) Not Null,
Arrival_Time char(40) Not Null,
Departure_Time char(40)Not Null
);
Create table Passenger(