Database Management System Project

This assignment focuses on database management systems (DBMS) through a practical project involving a car rental system. Students are tasked with creating an E-R model depicting the relationships between customers, rentals, and cars. They then design and implement a MySQL database based on this model, including SQL queries for data retrieval and analysis. The project explores concepts like table structures, foreign keys, and relational algebra to demonstrate effective database management practices.

DATABASE
MANAGEMENT SYSTEM

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................3
MAIN BODY...................................................................................................................................3
Part 1................................................................................................................................................3
A set of relations mapped from E-R diagram.............................................................................3
Design database management using MS Access-.......................................................................4
Database table-............................................................................................................................6
Queries Design..........................................................................................................................10
Part 2..............................................................................................................................................13
E-R model on the car rental activity, with three relations Customer, Rental and Car..............13
Database design and implementation using MYSQL (structural query Language).................13
CONCLUSION..............................................................................................................................25

INTRODUCTION
Database is a collection of data and information which helps for update, delete, inserts
and manage data in the form tables. Data base manage system is important for organization to
manage the entire business process effectively and efficiently. Database manager provide the
information to their user and they have ability to manage read, write operations in business
operations and functions. This assignment will discuss about database that is implementations
occurred with the help of MS Access. This report will describe 3rd normal form to create
relationship between tables. Further, it also describes to database design with the help of
MYSQL server.
MAIN BODY
PART 1
A set of relations mapped from E-R diagram
1

Design database management using MS Access-
Normalization- Normalization is a database design technique used for creating database tables in
proper manner and it will reduce the dependency and redundancy of data and information in
database management system. It shows the relationship between the tables and data.
There are various types of Normalization techniques useful in database management system
which are discussed below:
1NF (Normal Form)
 Each and every table should contain single value.
 Each record needed to be unique.
2NF (Normal Form)
2nd Normal form is following satisfy the conditions such as:
 It will create table with the help of 1NF.
 All non-key attributes are fully functioned dependent on primary key.
3NF (Normal Form)
3rd Normal form has no transitive functional dependency.
2

Consider a Car Rental database, which contain many tables. To show functional
dependency in database we take 3 tables: CUSTOMER, INSURANCE, CAR where in
CUSTOMER table contains customer details, in CAR table contains vehicle detail and in
INSURANCE table contains insurance details of vehicles and the person who owns the vehicle.
In this case, there need to link these tables in order to get insurance of car. The way to do
this is via foreign key. There is a column in INSURANCE table that points to or references a
corresponding column (called primary key) is CUSTOMER and CAR parent table i.e.
customer_id and car_reg_no. Suppose in CUSTOMER table Clay Jennson with customer_id 151
wants an insurance of car where reg_no 93484OPY whose detail stored in INSURANCE table
for create an insurance for particular customer.
3

Now 3NF dictates that in INSURANCE table, the information hold about customer
should Customer_id and reg_no is that foreign key and references to identifies the customer who
owns the car with Customer_id (151) and reg_no (93484OPY) i.e. Clay Jennson. No other data
of CUSTOMER ansand CAR table stored in INSURANCE table. This ensuring that there is no
duplication of information, which turns queries run much more efficiently and shows functional
dependency 3NF.
Database table-
4

5

6

Queries Design
Q1. Which cars have never been rented out? List the details that includes the car’s
registration number, make&modelmake model, number of seats, manufactured year,
category.
SQL Code:-Code: - SELECT Car.Vehicle_ID, Car.Location_ID, Car.PolicyNo,
Car.Manufacture_year, Car.Num_of_Seat, Car.[Make&model], Car.Reg_no
FROM Car LEFT JOIN Booking ON Car.[Car. [Reg_no] = Booking.[Booking. [Reg_no]
WHERE (((Booking.Reg_no) Is Null));
Output:-Output: -
Q2. How many bookings have been processed by each employee? Show the employee’s
name, working location and the number of bookings processed. Order the list so that
the employee who processed the most bookings appear first.
SQL Code:-Code: - TRANSFORM Count(Booking.Reservation_id) AS
CountOfReservation_id
SELECT Booking.Employee_id, Employee.Full_name, Count(Booking.Reservation_id) AS
[Total Ofof Reservation_id], First(Location.Location_name) AS FirstOfLocation_name
FROM (Booking INNER JOIN Employee ON Booking.Employee_id = Employee.Employee_id)
INNER JOIN Location ON Employee.Employee_id = Location.Employee_ID
GROUP BY Booking.Employee_id, Employee.Full_name
PIVOT Booking.Rental_ID;
Output:-Output: -
7

Q3. Find out the customers who made bookings for a minivan. This includes customer’s
name, phone and the booking date.
SQL Code:-Code: - SELECT Customer.full_name, Customer.phone, Booking.Booking_Date
FROM Vehicle INNER JOIN (Customer INNER JOIN Booking ON Customer.customer_id =
Booking.Customer_id) ON Vehicle.Category_id = Booking.Category_id
WHERE (((Vehicle.Category_id)=id) =605));
Output:-Output: -
Q4. Find out the rental activities that have the most expensive cost. This includes the
customer names, car registration numbers, make and models, start hire dates, end hire
dates and the costs.
SQL Code:-Code: - SELECT Customer.full_name, Rental.Startdate, Rental.enddate,
Rental.Cost
FROM Rental INNER JOIN (Customer INNER JOIN Booking ON Customer.customer_id =
Booking.Customer_id) ON Rental.RentalID = Booking.Rental_ID
ORDER BY Rental.Cost DESC;
Output:-Output: -
8

Q5. Show details of all rental activities. These details include customer name, pick up
location, drop off location, start hire date, end hire date and the cost.
SQL Code:-Code: - SELECT Customer.full_name, Rental.Pickup, Rental.Drop,
Customer.phone, Booking.Vehicle_id, Rental.Startdate, Rental.enddate, Rental.Cost
FROM Rental INNER JOIN (Customer INNER JOIN Booking ON Customer.customer_id =
Booking.Customer_id) ON Rental.RentalID = Booking.Rental_ID;
Output:-Output: -
9

PART 2
E-R model on the car rental activity, with three relations Customer, Rental and Car
Database design and implementation using MYSQL (Structured query Language)
Structured query language is useful for creating data base table and storing data in proper
manner. This language is helpful for accessing multiple database by using SQL. It is required
for creating an effective database and using scripting language such as php, Asp.net etc. There
are variety of syntax written in query language and implemented to design database system.
10

Design Car rental activity database:
Booking table-
11

Output: -
Car database table-
12

Output-
13

Customer table-
14

15

Output-
Employee table-
16

Output-
17

Insurance table-
18

Output-
19

Location table-
20

Output-
21

Rental table-
22