2nd phase of my project

[Total Mark for this Case Study is 5]

College of Computing and Informatics

Special Instructions

Please follow the below instructions:

· You should answer the questions in this case study as a group.

· The case study consists of two phases. This is Phase II, which consists of Questions Three and Four. Due date for this Phase is 15-April-2021, End of the Day.

· Students can use any relational DBMS (MS SQL Server, MySQL, Oracle, DB2 etc.) for database creation and manipulation.

· Students are required to answer all the questions based on the below scenario.

______________________________________________________________________

The Scenario

Rental Cars Reservation System

Year 2022, you are hired as a software engineer in a car rental company. Your company is planning for nationwide expansion of its business. You are required to develop a database to support storage and manipulation of information about the cars, customers, and reservations.

Each car is uniquely identified with its license plate. It also has manufacturer (Toyota, Jeep, etc), model, year of made and color information. Cars are rented on hourly, daily, and monthly rates. To ensure the availability of the car, its status is updated from available to rented (if rented), or under maintenance (if under maintenance).

The company wants to identify each customer with a unique customer id., name, address, contact number, driving license number, and payment information (credit, cash) for each customer is also stored.

Record of each car reservation is maintained with unique identification number and identification of the car and customer. Date and time of the reservation request is also stored along with the date and time of pick up and return of the car. Status of each reservation is also updated form confirmed (if confirmed), to picked up (when the car is picked up), and to returned (when the car is returned). The total amount for the reservation is also stored for future reference.

You can specify the assumptions (if any) for designing the required database.

Question Three

2.5 Marks

Learning Outcome(s):

LO 4

3.1 Write a query involving any two relations and translate the query to a relational algebra expression.

3.2 Draw the Query tree for the above relational algebra expression.

Question Four

2.5 Marks

Learning Outcome(s):

LO 3

4.1 Create a simple SQL trigger that must check a specific condition before the INSERT in a particular relation.

4.2 Apply horizontal fragmentation of a particular relation based on a specific condition. Show the relation before fragmentation and the relations after fragmentation.

4.3 Briefly discuss the security features of the relational DBMS you have used for this case study. Compare these features with any other relational DBMS.