using oracle database
Part I. Description of the model A car rental company has several outlets (i.e., locations) with cars/vehicles that are rented for various periods of time. For each vehicle, we record its license plate number, make, model, year, last inspection date, the daily rate, etc. For the clients who rent vehicles/cars only basic information is recorded. Each outlet has several employees working at the outlet (e.g., sales reps, mechanics, and administrative assistants). The information stored about the employees includes name, gender, date of birth, contact information, hire date, etc. Each employee is working at only one outlet. A few of the employees may supervise other employees (e.g., one sales rep would supervise other sales reps). For each supervised employee, we need to identify which other employee supervises them. Some employees may manage up to 3 outlets. Each outlet has only one manager responsible for the operation of the outlet. For each rental agreement! (agreement with a client who rents a vehicle) we need to record the rental date, rental time, and the number of miles driven before a rental agreement is signed. When the rented vehicle is returned the date and time as well as the mileage (i.e., odometer reading after the vehicle is returned) is recorded. The rental agreement must also include the insurance type used. When a rented vehicle is returned, it is checked by one of the employees (typically a mechanic on duty at the time) for any problems. When it is needed, the employee prepares a report describing the problems found with the vehicle. The fault report is created only when there are issues/problems with the returned vehicle. The fault report is very simple and includes only date, time, and comments (describing and evaluating the issues/problems). Once a vehicle has accumulated 5 fault reports, that vehicle is put out of service and is no longer available for rentals until a complete check is done (and all issues are resolved). We also need to link each fault report to individual rental agreement, in case a follow up is needed. The ERD and schema for the model are shown below. ! The entity name is shortened to “RAGREEMENT? in the ERD and schema. / ER Diagram: 1S associated with CLIENT RAGREEMENT (1,1) (1,N) (1,1) Schema: EMPLOYEE (EmpNo, FName, LName, Position, Phone, Email, DOB, Gender, Salary, HireDate, OutNo@, Super No®) FAULTREPORT (ReportNum, DateChecked, Comments, EmpNo@, LicenseNo@, RentalNo@) 2 OUTLET (outNo, Street, City, State, ZipCode, Phone, ManagerNo®) VEHICLE (LicenseNo, Make, Model, Color, Year, NoDoors, Capacity, DailyRate, InspectionDate, outNo@) CLIENT (clientNo, FName, LName, Phone, Email, Street, City, State, ZipCode) RAGREEMENT (RentalNo, StartDate, ReturnDate, MileageBefore, MileageAfter, InsuranceType, ClientNo@, LicenseNo@) ? 2 DateChecked attribute is of “Date” datatype and includes both the date and the time 3 Both StartDate & ReturnDate attribute are of “Date” datatype and include both the date and the time. 2 Part II. tables, data integrity constraints, for creating all necessary abase as defined above. bles to implement the dat Lave the necessary integrity constraints including he script with a set of “Drop Table” statements, that will allow ting it (very useful when you recreate the database using the ou to clean up the database before cred tel : : as the names of attributes exactly as provided in the conceptual forced within the “Create Table” script). Use the names of tables as well 2 ns model above. All constraints, except ONE, must be created ana en : vention discussed in class (lecture 1 2). Run the script to t). Your statements and must be named using the con i : i create the DB tables and to create the missing constraint (as the last statement in your SCrip CREATE TABLE statements should also include the following: a. All date attributes must have a default value that is the current date. b. All email addresses must conform to a validation rule ) i. For employees of the rental company, ensure that the domain name is used properly ii. For client, email addresses conform to a simpler rule, and J c. Three more appropriate and useful check constraints of your choice (but not the “Not Null or “Unique” constraints). Make sure that you clearly identify each of the three constraints. A. Create two separate SQL scripts and for inserting data into the ta 1. Script #1: Write all CREATE TABLE statements that | primary keys, foreign keys, etc. Start t raints are created, insert about 10-15 rows of de data that would allow you to test all queries as defined in section B below (each query must give you results). It may be required to add more than 15 rows in some tables in order to show that your queries work as intended. Outlet table can have just a few rows. Run the script #2 to perform the task of populating the database tables. (Note: All constraints must be enabled and enforced — not deferred — before you populate your tables.) 2. Script #2: After the DB tables and all data integrity const data into each table. Provi 3. After all tables are populated, list the complete content of each table. Format each table’s contents to make sure it is readable. B. Create SQL queries to answer the following questions 1. For each rental agreement started in previous month, the company needs a detailed report that includes start date and time, return date and time, vehicle information the following information: rental number, (license number, outlet number, make, model, and year). If the rental required a fault report, also include the date when the vehicle was checked. the third quarter of current year? Create a “pivot query” that lists number of rentals started in each outlet and each month of the third quarter. The last column should provide the total number of rentals started in the outlet. As the last row, provide total number of rentals started in each month of the 3rd quarter. Include zero in each cell where we do not have any value. 2. How well did each outlet do each month of List details of vehicles that are at least 5 years old. Include the vehicle license number, vehicle make, model, age, miles driven, current reading of the odometer, and number of fault reports generated. How do we assess the likelihood of getting a fault report for each of the vehicle’s “make” we have? Calculate the likelihood for each quarter of this year and for each “make” combination. Rank each make within each quarter when the vehicles were rented. The higher the chance of getting a fault report, the higher the rank. Also, include the number of rentals of each make within each quarter of this year. How much each vehicle model contributes (in percent points) to the total revenue for the 3" quarter of this year? Rank vehicles from the highest revenue earning percentage to the least. With each model, list the number of vehicles we have of that model, and the revenue earning percentage. 3 6. List employee ID and name, his/her title, the street address of the outlet where he or she works, and the number of fault reports prepared by the employee in the past 90 days. For each employee show the “level” in the “supervising hierarchy” and indent the name accordingly to the level so that the name of a person who is lower in the hierarchy is indented more than their supervisor. 7. For each outlet, provide number of vehicles at the outlet, number of rentals from the outlet started within the past 60 days, average distance driven per rental from the outlet started within the past 60 days, number of employees working at the outlet, and the number of rentals in the past 60 days per employee. 8. Identify customers that produced the greatest revenue this year (provide names of the customers and the revenue). 9. We want to evaluate vehicle rentals in the context of locations. For each outlet, list the number of customers (who rented cars from the outlet) who live in the same state as the location of the outlet. Also, include the outlet number and state where the outlet is located, and the number of rentals by those customers. In addition, include for each outlet, the proportion of the customers from within the state compared with all customers in the outlet and the proportion of rentals from those customers compared with all rentals in the outlet. 10. What types of clients do we have? Based on the “web address” information, group the client by the type of client (“.edu” indicates an educational institution, “.gov” a government agency, “.org” a non-for- profit organization, and “.com” a for-profit company) and provide how many clients we have of each type/category and number of rental we had in the past quarter from each type of client. If the web address does not match any of the four major types, then use “Other” for the client type. If we do not have a web address for a client, then the “client type” should be “Not Available”.