Relational Database Design and Implementation Project

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

Contents
Introduction......................................................................................................................................2
Task 1...............................................................................................................................................3
RDBMS V/S FILE-BASED SYSTEM.......................................................................................3
Comparison of database models..................................................................................................4
Comparison of Approaches.........................................................................................................6
Task 2...............................................................................................................................................7
Entities.........................................................................................................................................7
Crow’s Foot Notation (Conceptual Diagram)...........................................................................10
Task 3.............................................................................................................................................10
Data Normalization....................................................................................................................10
ERD diagram.............................................................................................................................14
Logical diagram.........................................................................................................................16
Task 4.............................................................................................................................................17
Queries Creation........................................................................................................................17
Data Population:........................................................................................................................19
Conclusion.....................................................................................................................................21
References......................................................................................................................................22
List of Figures
Figure 1: Example of the Hierarchical Model.................................................................................7
Figure 2: Example of a Network Model..........................................................................................7
Figure 3: Example of a Relational Model........................................................................................8
Figure 4: Conceptual diagram.......................................................................................................12
Figure 5: Entity Relationship Diagram..........................................................................................16
Figure 6: PhpMyAdmin Diagram..................................................................................................17
Figure 7: Logical Diagram.............................................................................................................18

Figure 8: WorkingStaff..................................................................................................................19
Figure 9: JetAirlines......................................................................................................................19
Figure 10: TravellersInfo...............................................................................................................19
Figure 11: FlightDetails.................................................................................................................19
Figure 12: PaymentsInfo................................................................................................................20
Figure 13: AirWorkingStaff..........................................................................................................20
Figure 14: GroundWorkingStaff....................................................................................................20
Figure 15: Foreign Key Tables......................................................................................................21
Figure 16: WorkingStaff Data.......................................................................................................21
Figure 17: JetAirlines Data............................................................................................................21
Figure 18: TravellersInfo Data......................................................................................................22
Figure 19: FlightDetails Data........................................................................................................22
Figure 20: PaymentsInfo Data.......................................................................................................22
Figure 21: AirWorkingStaff Data..................................................................................................22
Figure 22: GroundWorkingStaff Data...........................................................................................22

Introduction
The given assessment is based on database management systems, different tasks are given in the
assignment that can be seen in the coming report. Various discussion on different topics are
shown in the first task, in the second task, for the provided scenario that is on the Jet Airlines,
entities are shown and the diagram is also created for the entities, in the third task the tables that
were created are normalized and at last, the implementation of the tables as well as the data
population of the tables are shown by the help of screenshots and phpMyAdmin is used to
perform the implementation.

Task 1.
RDBMS V/S FILE-BASED SYSTEM
RDBMS is way far better than the traditional File system as this is more secure, easy to use and
really very effective. Earlier, the data was stored in the forms of large files which was considered
to be safe and effective but nowadays as the technology has developed, Relational Database
Management System has taken place and this helps in keeping the data safe and secure. This is
most effective as well as the efficient way of storing or keeping the data.
Dissimilarities
 Talking about the consistency, the first and the most important drawback in the File-
based system is the lack of consistency and this is quite nicely available in the Relational
Database Management System. Consistency is one of the most important features of
RDBMS.
 The next and the most important feature of the Relational Database Management System
is the security of the database. Due to this reason of security, RDBMS is considered a
way far better than File based System.
 Centralization is the other dissimilarity present in the File-based System. The saddest part
here is that it is unachievable in File-based system and RDBMS could easily achieve
Centralisation (Ranganath et al., 2016).
 Redundancy is considered as one of the most important drawbacks present in the File-
based System, as due to the redundancy it is difficult to access the exact data that is being
needed. RDBMS contains various methods to remove the redundancy as well as the
inconsistency from the database.
Relational Database
Management System.
File-Based System
Advantages 1. RDBMS is the most
secure and reliable
1. The backing of the
data could easily be

system.
2. It is very easy to
maintain the RDBMS.
3. Retrieving of the data
could be easily done in
RDBMS (Freire et al.,
2016).
done here.
2. Sharing of the data
could be easily done.
3. Retrieving of the data
could be easily done.
Dis-Advantages 1. The cost of
maintenance is quite
high here.
2. Proper protocols are
defined for setting up
the limitations.
3. Complexity is quite
high.
1. Data redundancy is a
major drawback.
2. Data is highly
inconsistent here.
3. Security of the data is
compromised.
Limitations 1. Sizes of the fields are
limited here.
2. Complexity is really
the major factor.
3. This is quite expensive.
1. Security of the
data is
compromised.
2. Data consistency
is very low.

Comparison of database models
Here the discussion on the three types of model is mentioned that are Hierarchical database
model, Network Database model and Relational Database model (Ranganath et al., 2016)
.
Hierarchical Model Network Model Relational Model
Definition This model is relatable to a
tree which is inverted in
which the node that exists on
the topmost part is the root
and the rest of the structure
emerges from the root
(Mereu et al., 2017).
This is created in
order to eliminate
the problems that
occur in the
hierarchical model.
This represents
relationships
between an object
inflexible way.
For the problems related
to the complex structure
and inflexible structure
this model is used and
the other two models are
incapable of resolving
these problems so this
model handles such type
of problem.
Characteris
tics
1. Performing operations like
insert and delete are easy
here.
2. Data retrieval is also easy.
1. No high skill
requires
understanding this
model.
2. Easy to use.
3. Make the process
of accessing data
lighter and faster
(Losch et al., 2017).
4. Integration of the
data can be done
here.
1. Least complex.
2. Provide help in
increasing flexibility and
reducing complexity.
3. Highly Scalable.
4. Data retrieval is easy.
5. Costly than other
models (Hazber et al.,
2016).
Example Given below Given below Given below

Examples of the models:
Hierarchical Model
Figure 1: Example of the Hierarchical Model
(Source: Dbmsinternals.com, 2019)
Network Model
Figure 2: Example of a Network Model
(Source: D3n817fwly711g.cloudfront.net, 2019)
Relational Model

Figure 3: Example of a Relational Model
(Source: Etutorials.org, 2019)
Comparison of Approaches
Top-Down Approach
The first approach to be discussed is the Top-down Approach. This is also known as Design by
analysis. The main work done here is the breaking up of the entities in order to accomplish the
various requirements.
Strengths
 The first strength of the Top-down approach is the saving of the time and along with the
time-saving ability, it doesn’t affect the quality at all.
 Reliability is the next strength here, as this approach is quite highly reliable and the
decision-making feature also develops it in a great way.
Weaknesses
 Good knowledge along with the great skill knowledge is required in this approach.
 Participation is quite low.
Bottom-Up Approach

The very next approach to be discussed here is the Bottom-up approach and the other name given
to this approach is Design by Synthesis. The thing that takes place here is the data collection in
the first phase and followed by the entire implementation ((Hazber et al., 2016).
Strengths
 The biggest strength here is that the solutions are easily provided.
 Implementation is quite easy.
Weaknesses
 Cost of implementation could be increased as there are high chances of increment.
 Effectiveness along with the efficiency is absent here (Armbrust et al., 2015).
A top-down approach is followed in this assignment as this is considered as better in many ways.

Task 2
Entities
AirWorkingStaff
Attributes Data type
Emp_Number integer
FlightType varchar
FlightsDetails
Attributes Data type
F_Number integer
Flight_Manufacture varchar
Flight_ModelName varchar
Flight_ArrivalTime integer
Flight_DepartureTime integer
Flight_Destination varchar
GroundWorkingStaff
Attributes Data type
Emp_Number integer
RoleOfEmp varchar

JetAirlines
Attributes Data type
Branch_Number integer
FlightOrigin varchar
TotalFlights integer
TotalBookings integer
TotalCancellations integer
PaymentsInfo
Attributes Data type
Paymnt_ID integer
PaymentMode varchar
PaymentStatus varchar
TravellersInfo
Attributes Data type
T_ID integer
Paymnt_ID integer
Branch_Number integer
T_Name varchar
T_Contact integer
T_Email varchar

WorkingStaff
Attributes Data type
Emp_Number integer
F_Number integer
Branch_Number integer
Emp_Name varchar
Emp_Address varchar
Emp_Salary integer
Crow’s Foot Notation (Conceptual Diagram)
Figure 4: Conceptual diagram