Database design and development - Assignment

Database design and development

Table of Contents
INTRODUCTION...........................................................................................................................3
PART A...........................................................................................................................................3
Database tables- .........................................................................................................................5
Airline table-...............................................................................................................................5
Entity Relationships-...................................................................................................................8
Queries-.....................................................................................................................................10
Integrity constraints- ................................................................................................................13
PART B..........................................................................................................................................14
CONCLUSION .............................................................................................................................18
REFERENCES..............................................................................................................................19

INTRODUCTION
Database management is a process for creating and managing the database effectively
and efficiently. It provides the programmer and user to create, retrieve, update, delete and
manage the data in effective manner. In part A, it will discuss about the normalization technique
to create 3NF relation. It will design the relational database system to identify the relationship
between the database tables and also generate the query by using Ms Access. In part B, it will
design ER model of car rental activity and also create relationship between the three different
entities.
PART A
Normalization- It is a database design technique that organize the tables in appropriate manner. It
also reduces the dependency and redundancy of data (Wynn, 2018).
a)
1 NF-
3 NF -

Third normal form is basically used in the normalization of database that design to reduce the
duplication in the data(Djenouhat, Belala and Barkaoui, 2018). 3NF design is improved the
processing of database that minimize the storage of cost. Customer table contain primary key
that shows as foreign key into the booking detail table. On the other hand, it will create
relationship between the two tables. Afterwards, it is reduced to minimize into the single table
like booking table.
Customer table
primary key – customer id
booking detail
foreign key- customer id
primary key- reservation id
In this way, it can be designed the one to many relationships whereas foreign key is
deepened on the primary key. All the other non key attributes are depended on the primary key.
b) List Functional dependencies-
 booking date

 total price
These are functional dependencies that exists in the both customer table and booking table.
Database tables-
Aircraft table-
Airline table-

Airport table-
Booking table
customer table-

Booking detail table-
Flight table-
Payment table-

Entity Relationships-
Entity relationship diagram shows the relation between the different entity that store in the form
of set in the database (Trisnanto and et.al., 2019). An entity is based on the collection of similar
entities that define the properties. For designing of database, ER model plays important role to
easily identify their relation to create table.
Table Entity attributes
Booking table Booking  Reservation id(PK)
 booking date
 total price
 customer id (FK)
Airport table airport  Airport code (PK)
 name
 city
 location
 flight no (FK)
Customer table Customer  customer_id(PK)
 customer_firstname

 customer_lastname
 DOB
 type
 reservation id(FK)
 address
Booking detail table Booking detail  reservation_id (PK)
 flight no (FK)
 travel date
 seat no
 class
Payment table Payment  Payment id(PK)
 method
 date amount
 invoice
 customer_id (FK)
Flight table flight  Flight no (PK)
 dept time
 arrival time
 dept airport code
 arrival airport code
 price
 airline code
Aircraft table aircraft  Aircraft type id (PK)
 type name
 seat capacity
 manufacture
 flight no (FK)
Airline table Airline  Airline code(PK)
 airline name

 customer service
 headquarters address
Queries-
Query – 1
SELECT customer.customer_id, customer.customer_firstname, customer.customer_lastname,
customer.customer_DOB, customer.customer_type, customer.customer_phone
FROM customer;

Query – 2
SELECT airline.[airline code], [booking detail].[reservation id], flight.[flight no], flight.[dept
airport code], flight.[arrival airport code], [booking detail_1].[travel date]
FROM airline, [booking detail] AS [booking detail_1], [booking detail] INNER JOIN flight ON
[booking detail].[flight no] = flight.[flight no];
Query – 3
SELECT customer.customer_firstname, customer.customer_lastname, [booking detail].
[reservation id], flight.[flight no], [booking detail].[travel date], booking.[total price]
FROM ([booking detail] INNER JOIN flight ON [booking detail].[flight no] = flight.[flight no])
INNER JOIN (customer INNER JOIN booking ON customer.customer_id =
booking.customer_id) ON [booking detail].[reservation id] = customer.reservation_id
WHERE ((([booking detail].[travel date])>=#12/1/2018# And ([booking detail].[travel
date])<=#12/5/2018#));

Query- 4
Select airline.[airline code], airline.numofflight
from airline
where ((airline.[airline code])=88));
Query- 5
SELECT flight.[flight no], flight.[dept time], flight.[arrival time],
flight.[dept airport code], flight.[arrival airport code], flight.price
FROM flight
WHERE (((flight.price)=Min("price")));

Integrity constraints-
Integrity constraints is provided the best way to ensure that change made to the database
by the authorized users. It cannot be loss of data consistency. Sometimes, it may costly for the
used of testing. It is useful for test the data integrity with the minimum over head (Halpin,
2019).
In ER model
It is created a form of relationship that certain attributes from the entity set and candidate key.
 It is created many to many relationships between customer and booking.
 Customer and payment has one to many relationships because one customer has been
used the many methods to pay.
Implementation-
Database implementation is described the own experience for used different query
functions to solve the problem. It is requirement of different functions and formulas to determine
the accurate solution in appropriate ways (Schwichtenberg, 2018). It is responsible for selecting
the Ms Access database that help for creating dynamic tables for the process of flight booking
system. There are some issues arises at the time of implementation of relational database.
 Increasing the data size and volume
 scalability limit
 Decentralized the data management
I can choose the MS access and My SQL server that help for increase the database tables and
their relationship by using primary key. It is important for identifying the correct pk that create
relation with another table. The primary objective of database to mange and control all the
information in proper manner. It is easily accessed the data at anywhere and any place (Tavakoli
and Wijesinghe, 2019).

PART B
It is designed the car rental activity and create relationship between booking, customer and flight.
Database tables-

Entity Relationship between Customer, booking and flight-
Entity relationship diagram is basically the graphical representation between the different
entities. Each entity has own attributes in the database table.
Table Entity attributes
customer_table customer  customer_id (PK)
 customer_name
 customer_lastname
 customer_no
Booking_table booking  booking_id(PK)
 booking time
 booking date
 customer_id (FK)
flight_table flight  flight_no(PK)

 flight dept_time
 customer_id (FK)
 flight arrival_time
CONCLUSION
As per discussion, it is concluded that database management system is important part for
storing all the relevant information. It is also designed the relational database system to identify
the relationship between the database tables and also generate the query by using Ms Access.
ER model of car rental activity and also create relationship between the three different entities. In
this way, it is successfully designed the relational database system.

REFERENCES
Books and journals
Djenouhat, M.A., Belala, F. and Barkaoui, K., 2018. Architectural method to design and control
dynamic composite web services. International Journal of Computer Applications in
Technology. 57(1). pp.59-71
Halpin, T., 2019. Reference Scheme Modeling. In New Perspectives on Information Systems
Modeling and Design(pp. 227-254). IGI Global.
Schwichtenberg, H., 2018. Forward Engineering for New Databases. In Modern Data Access
with Entity Framework Core (pp. 61-86). Apress, Berkeley, CA.
Tavakoli, R. and Wijesinghe, S.N., 2019. The evolution of the web and netnography in tourism:
A systematic review. Tourism Management Perspectives.29. pp.48-55.
Trisnanto, P.Y and et.al., 2019. Testing Design Value of CC with Reflective SEM Line
Application Data Resistance Design Medical Safety Reserve in Airport Abdul Rachman
Saleh Malang, Indonesia.
Wynn, D.E., 2018. Dragon Air: A Database Design Case. Information Systems Education
Journal. 16(2). p.18.