Description

The application also stores and manages information about the cars. A specific car is identified by their license plate with the color of the car recorded. The current scheme for license plate starts with letter S followed by two letters, four digits, and the final checksum letter (see: Wikipedia). The brand and the model of each car are also recorded, which we call as the make of the car.
The application also tracks all passenger information of a car that are being rented. If a car is not currently being rented, there should be no passenger. For simplicity, the passenger will ride for the entire duration of the rented car. So if a car is being rented in the interval [s, e], then any passenger of this car will ride in the car for the entire interval [s, e].
References
Page 2
3. (6 points) Stored Procedures and Triggers
The initial schema can be found on Canvas Files > Project. Additionally, the logical diagram is shown below. Note that in our database, a passenger is a customer who is riding a rented car. In other words, it is based on the entry in the ride table.

(a) (4 points) Triggers
For this question, your task is to implement a series of triggers to capture additional application constraints. More specifically, your triggers should capture and preserve the following 4 constraints.
(3) The number of passengers (i.e., customers riding a rented car) is less than or equal to the capacity of the car.
(4) One of the passengers (i.e., a customer riding a rented car) must be a driver but there can be more than one drivers. In other words, one or more of the passengers must have driver’s licenses.
However, your trigger should work for a transaction. In particular, if we have a transaction that performs several insertion on several tables, as long as the final state of the database is consistent, the entire transaction should be committed.

(b) (2 points) Procedures
Now that you have your triggers in place, your last task is to implement common actions as stored procedures. Here, we want to focus on the act of renting a car. The following two procedures should work even in the presence of your triggers.
(1) Implement the stored procedure rent_solo that attempt to rent the car (i.e., license plate) to the given customer (i.e., the NRIC) from the given start_date to the given end_date. The only passenger is the given customer.
1 CREATE OR REPLACE PROCEDURE rent_solo
2 (IN car VARCHAR(8), IN customer CHAR(9),
4 AS
5 $$
6 …
7 $$ LANGUAGE plpgsql;
(2) Implement the stored procedure rent_group that attempt to rent the car (i.e., license plate) to the given customer (i.e., the NRIC) from the given start_date to the given end_date. For simplicity, you are also given exactly 4 passengers namely, passenger1, passenger2, passenger3, and passenger4. All passengers are given as NRIC number.
1 CREATE OR REPLACE PROCEDURE rent_group
2 (IN car VARCHAR(8), IN customer CHAR(9),
4 IN passenger1 CHAR(9), IN passenger2 CHAR(9), 5 IN passenger3 CHAR(9), IN passenger4 CHAR(9))
6 AS
7 $$
8 …
9 $$ LANGUAGE plpgsql;
Submission
• Canvas Submission: “Assignments > Questions 3” (one file per project group)
• Files
– Trigger: triggers.sql and triggers-tests.sql
∗ The definition for your trigger and trigger functions should be in triggers.sql.
∗ triggers-tests.sql should contain only your own test cases without definition for your trigger and trigger functions. This is optional and used to understand the behavior of your triggers.
– Stored Procedures: procedures.sql and procedures-tests.sql
∗ procedures-tests.sql should contain only your own test cases without definition for your stored procedures. This is optional and used to understand the behavior of your stored procedures.
Note: Submit four separate files. Ensure the name is correct before submission.
Testing
1. Start from a fresh database without any tables, procedures, and/or triggers.
2. Create all tables from the given schema.
3. Insert valid rows into the database as an initial data.
4. Create all procedures and triggers from triggers.sql and procedures.sql.
5. Perform tests (e.g., ) triggers-tests.sql and procedures-tests.sql.
• For a transaction, as long as the final state of the database is consistent, the entire transaction should be committed regardless of the intermediate state.
As a good practice and to enhance your understanding of your implementation and/or problem description, you should prepare test cases that will satisfy the constraints as well as test cases that will not satisfy the constraints.