Database Design and Development Project for Airline and Car Rental

This assignment details the design and development of relational databases for an airline and car rental system. It begins with an introduction to database management, normalization techniques (specifically 3NF), and the reduction of data redundancy. Part A focuses on designing database tables (Aircraft, Airline, Airport, Booking, Customer, Booking detail, Flight, and Payment), identifying relationships using ER diagrams, generating SQL queries (SELECT statements with JOINs and WHERE clauses), and implementing integrity constraints. Part B extends the design to a car rental activity, creating relationships between customer, booking, and flight entities. The project concludes with a summary of the database design process and references to relevant literature on database design and management.

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")));