Data Modeling and SQL Language: A Comprehensive Report

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

DATA MODELLING & SQL LANGUAGE

Contents
Introduction......................................................................................................................................3
Task 1: Understanding the database................................................................................................4
Task 2: Conceptual Modeling........................................................................................................10
Task 3: Logical Modeling..............................................................................................................13
Task 4: SQL and Database Implementation..................................................................................15
Conclusion.....................................................................................................................................16
References......................................................................................................................................17
Figure 1: Example of Hierarchical model.......................................................................................6
Figure 2: Example of Network database model..............................................................................6
Figure 3: Example of relational model............................................................................................7
Figure 4: Conceptual diagram.......................................................................................................13
Figure 5: ER diagram.....................................................................................................................16
Figure 6: UML Diagram................................................................................................................17
Figure 7: ERD by phpmyadmin.....................................................................................................18
Figure 8: Employee........................................................................................................................19
Figure 9: Employees data..............................................................................................................19
Figure 10: Pilot..............................................................................................................................20
Figure 11: Pilot data.......................................................................................................................20
Figure 12: GroundStaff..................................................................................................................21
Figure 13: GroundStaff data..........................................................................................................21
Figure 14: Aircrafts........................................................................................................................22
Figure 15: Aircraft data.................................................................................................................22
Figure 16: Payments......................................................................................................................23
Figure 17: Payment data................................................................................................................23
Figure 18: Airline..........................................................................................................................24
Figure 19: Airline data...................................................................................................................24
Figure 20: Passengers....................................................................................................................25
Figure 21: Passengers data.............................................................................................................25
Figure 22: Alter table queries........................................................................................................26

Introduction
The report is related to an in-depth understanding of the database management systems. The
overall assessment is divided into the subparts where the deeper understanding of various sub-
topics that are given in the assessment is given. The topics that are given are: understanding the
database, conceptual modeling, logical modeling, and physical modeling. The practical of the
database is done with the help of phpMyAdmin and screenshots are given in the report.

Task 1: Understanding the database
The database that is created here is based on the case scenario that is provided in the assignment
that is on the Jet Airlines in which performing the role of a junior developer a database is needed
to be created for the airlines that is capable of storing various details regarding the airlines and
aircrafts along with the employees and pilot that works for the company (Vera et al., 2015).
1.
Differences:
 Data security: The File based system is not as much secure than database management
system as in this file based system, reading as well as writing in a file is easier as
compared to the relational database so the high security is not required in this system
while in the relational database systems the data which is store relies on the model of the
relational that requires security (Biloshchytskyi et al., 2017).
 Redundant Data: This refers to the multiple entries of similar data, this occurs in the File-
based system more frequently as compared to the relational database.
 Centralized: The centralization is easy to achieve in the relational database system as
compared to file-based systems.
 Data Consistency: In the relational database systems, the data stored is consistent but in
File-based system, it is not.
File-Based Systems:
Advantages:
 In this system, it is easy to share the data with any other available systems.
 The process of data retrieval is easy here in this system.
 To create the back ups in this system is easier as compared to any other systems.
 Data restoration can be performed easily (Li et al., 2016).
Disadvantages:
 The data that is present in this system is inconsistent.

 Repetition of the data can be seen very often which is also referred to as data redundancy.
 Data security is also not very high.
Limitations:
 Lack of proper security of the stored data.
 Redundant data can be a critical problem.
 Inconsistent data.
Database Management Systems:
Advantages:
 This system has proper and high security of the data that is stored in this system.
 This system is adopted by many companies and is highly reliable.
 The process of retrieving of the data is easy and quicker than any other database.
 Maintaining the database is easier.
Disadvantages:
 Complex data.
 The fields have some limitation of size so the large amount cannot be stored in a single
field.
 Cost of maintaining the database is quite high (Albarak and Bahsoon, 2018).
Limitations:
 The size of every field is limited.
 Not very cost efficient.
 Complexity is high.
1. Hierarchical Database Model is also referred to as an inverted tree where the topmost
node is termed as root and after this root, the overall structure gets down. The real world
application of this model is given in the picture:

Figure 1: Example of a Hierarchical model
(Source: Upload.wikimedia.org, 2019)
Characteristics of the Hierarchical model:
 It is easy to perform the operation of the insertion as well as the deletion of the data in
this system.
 The retrieval of the data is easy in this model.
 There might some problems regarding the communication as there are some differences
in the level for every node (Zhang et al., 2017).
Network Database Model is used to overcome the limitation that comes in the hierarchical
model. This model helps in representing the relationships among the objects in a very flexible
manner. The relationship exists among the items are direct in this particular model.
Figure 2: Example of Network database model

(Source: Image.slidesharecdn.com, 2019)
Characteristics of the Network model:
 This model is user-friendly and is easy to understand as well as to implement.
 The data integration is available in this model.
 Data access is an easy task to perform in this model.
Relational database Model is created so that the problems and issues faced in the network
model can be removed by using this model. The drawbacks that occur in the above two models
like high complexity, as well as the inflexibility, can be overcome by this model (Albarak et al.,
2018).
Figure 3: Example of a relational model
(Source: Upload.wikimedia.org, 2019)
Characteristics of the Relational model:
 This model is highly scalable.
 Less complex and easily understandable structure.
 The data retrieval process is easy.
 A bit expensive than any other database.

2. Top-Down Approach is referred to as the design by the analysis. In this system, the
entities get to break down for the completion of the requirements of the design. For the
accomplishing of the criteria that are needed a proper analysis is required (Ma et al.,
2018).
Strengths:
 In this approach, decision making is easy and fast for any project or task.
 This approach is highly reliable.
 This approach is mostly used as this approach provides the fast access and
implementation of the project in a minimum amount of time and also ensures the
quality of the project.
Weakness:
 This approach needs high knowledge and skills for the implementation in any given
project.
 In this approach, participation is low.
Bottom-Up Approach also referred to as the design by synthesis approach. This approach needs
to implement after the information and data are gathered appropriately and fulfilling all the
requirements of the projects.
Strengths:
 No need to study and get expert knowledge for implementing this approach.
 This approach provides an accurate solution to every problem that is encountered in the
project.
Weakness:
 As anyone can use this approach without any expert skills so the quality of the project
can be compromised while using this approach.
 The cost of using this approach can increase that can affect the project budget.

Now the approach that is used in the task that has been done in this report will be a top-down
approach as it is more efficient and provides more effective and accurate results with high
efficiency.
Task 2: Conceptual Modeling
Aircrafts table
Attributes Data type
Flight_No int
Manufacture varchar
Model varchar
ArrivalTime int
DepartureTime int
Destination varchar
Airline table
Attributes Data type
Branch_Id int
Origin varchar
TotalFlights int
NumOfBookings int
NumOfCancellations int

Employees table
Attributes Data type
Emp_No int
Name varchar
Branch_Id int
Flight_No int
Address varchar
Salary int
Passengers table
Attributes Data type
P_ID int
Pay_Id int
Branch_Id int
Name varchar
Contact int
Email varchar

Payment table
Attributes Data type
Pay_Id int
Pay_Mode varchar
Pay_Status varchar
Pilot table
Attributes Data type
Emp_No int
Type varchar
GroundStaff table
Attributes Data type
Emp_No int
Designation varchar