DBMS Case Study: QRentals database system QRentals (QR) is a private-owned busin
ID: 3919144 • Letter: D
Question
DBMS Case Study: QRentals database system
QRentals (QR) is a private-owned business located in a large city that specializes in car hire service.
The management of QR has approached you to build a relational database that will be used as the
most critical backend software component in their enterprise management system so that the
relevant business data will be recorded appropriately and further information query will be processed
efficiently. The proposed database system requires efficiently to track all QR’s vehicles, customers,
employees and relevant rental activities. Consider the following user requirements carefully and
design a database conceptual schema (i.e. E-R model) to support such an application.
The company owns a large number of cars for hire. These cars are usually parked in a number of
locations (belonging to QR’s property) in the city-wide, each location at a distinct address. Each car
has a unique registration number which can used as an identifier. The car’s make and model,
manufactured year, as well as the number of seats must be recorded. All cars in this case study
hypothetically fall into a number of categories such as minivan, SUV, sedan, ute, luxury limos, etc. The
daily hire rate of a vehicle is dependent on its category; for example the daily price of an ordinary
sedan is approximately $35 while hiring a minivan may need paying up to $70 per day. In addition,
each car has been commercially insured. The related insurance details are required to store in the
database, which include the policy number, amount of premium, insurer name, policy start date and
expiry date.
The system also needs to handle two different kinds of people: QR’s employees and customers. An
employee, whose main duty is to process booking request from customers, is identified by a unique
identification number, and has a name consisting of first name and last name. Other data on
employees need to record are their birthdate, skill level, annual salary and the working location which
normally is located at where the QR cars have been parked. For each customer, in addition to an
identification number assigned, it is necessary to record his/her full name, license number, address,
and phone number. In addition, the customers are generally categorized into two distinct types:
individual and corporate. For an individual customer, whether the customer has a personal life
insurance or not is recorded. For a corporate customer, the organization name, Australian Business
Number (ABN) and whether the business travel special deal is applicable or not are recorded.
To rent a car from QR, the customer must contact the company by a phone call. An employee of QR
will make a booking through the phone. QR often also stores details of some customers who have
made enquiries through phone calls but have not made actual booking due to availability issue or a
factor of price. The database keeps their details on record so that QR may follow up with them at a
later stage particularly in promotional activities. For each booking, a reservation number will be
created automatically. A brief description of reservation, the required category of car and the booking
date will be recorded. Then the customer will go to a particular location to pick up the car at the
scheduled start date (there is no concern what time to pick up it). After the rental activity ends, the
customer returns the car to a location (the so called drop off location) which may be different from
the pickup location. The database must keep track of relevant rental details such as pick up/drop off
location, start date, end date as well as the cost in the system. Finally, the database also stores an
image for each vehicle so that the prospective customers can conveniently view them online before
their hiring.
Question - Assumptions and Business rules
Provide assumptions and business rules relevant to your ERD.
Please write in more detail rather than bullet points
Explanation / Answer
Assumptions:
The company allows the intake of customers where their contact lists are linked in the form of cust_ID, f_name, l_name, email, adress, city and state and ph_No. company is an organization with the several Employee, who are listed in the form of Name, DOB,Emp_Id, Salary, worklocation and skills. employee handles the customers. Customer should be an individual with the documentation of life_Insuarance. Customer can be a corporate that could be an organization and could have a special deal with the company. Customer gives the details and views the car listed with image_type, image_size, image_Id and car_no. Images of the car should have the car_model, car_no, car_type and Noof_seats. Car should have the insurance with the policy_no, Premium, Insurance_name and policy_exdate. Cars should be chosen for booking that include cartype, res_no, pick_up, start_date and end_date.Customers makes their payment either by cash or cheque or card.
Business rules:
According to the business rules the employees working in the company is responsible for receiving the customer. Employees are organized well with the collection of essential details with sufficient salary. Employees should get all the essential details of the customers then analyzed as an individual or corporate. The relevant details are collected from the customers and then they are furthur proceeded. The model of the cars are shown to these customers and according to their necessity they pick a car. Then the essential payment should be done and the insurance of the car is also offered.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.