Description: The car rental company allows customers to reserve rental cars over
ID: 3865062 • Letter: D
Question
Description:
The car rental company allows customers to reserve rental cars over the internet, the company also allows customers to rent cars by calling up car rental personnel or by walking up to the rental counter
The rental car company has a number of branches and each car is assigned to a home branch. Cars are always rented from the home branch but may be returned to a different branch. Cars are sometimes shifted from location to location – i.e. the home branch location of a given car can be changed.
The company offers various types of cars and various price plans. Cars are grouped into the following classes: Subcompact, Compact, Midsized, Full sized, Luxury. Pricing varies by class of car, duration of rental. The company periodically offers special deals, e.g. one week rental of midsized car for $150. The company also offers discounts for organizational memberships, e.g. AAA, AARP. There is a separate set of fees when a car is not returned to the car’s home branch – this fee will depend on the identity of the home branch and the branch to which the car is returned, e.g. the fee might depend on the distance between the home branch and the return location.
We assume that a customer must register before he or she rents a car. Once the customer is registered, the registration data stays in the system.
Provide:
2. Provide DDL including: (50 points) • CREATE tables. (25 points
) • Populate (INSERT Statements) tables with some data. (10 points)
• Construct the following queries: (15 points)
A) What compact cars are available at a particular (pick any) branch?
B) Show all cars available, sizes, location to pick. Group by sizes, and location.
Explanation / Answer
DDL: CRATE TABLES:
Customer table:gives customer details
SQL> create table Customer(Customer_no int primary key,name varchar2(15),street varchar2(15),
town varchar2(10),county varchar2(10),post_code int);
Table created.
Car table: gives car details
TABLE:CARTYPE:
SQL> Create table Type_of_car (model varchar(10), make varchar(10), constraint cartype_key primary key (model));
Table created.
TABLE:CAR_RETES
SQL> Create table Hire_rate (engine_size INT, daily_hire_rate INT, constraint rate_key primary key (engine_size));
Table created.
TABLE:CAR DETAILS
SQL> Create table Car (reg_no char(20), model varchar(10), engine_size integer,
constraint car_key primary key (reg_no), constraint is_costed_at foreign key (engine_size) references hire_rate,
constraint is_of_type foreign key (model) references type_of_car, constraint registration_numbers check (reg_no in ('rfg 465w', 'yaw 567v','tfn 607v')), constraint models check (model in ('Mondeo', '707','Locus')),
constraint engine_sizes check (engine_size in (1000, 1200, 1400, 1600, 2000, 2500)));
Table created.
TABLE: CAR CONTRACT TYPE
SQL> Create table Contract
(reg_no char(15), Customer_no int, contract_date date, date_out date, date_in date,
constraint contract_key primary key (reg_no, Customer_no, contract_date), constraint for_a foreign key (reg_no) references car, constraint takes_out_a foreign key (Customer_no) references Customer);
Table created.