Task 1 (7.0 marks)

Stored PL/pgSQL Procedure

Task Specification:

Objective:

You are required to implement  a stored PL/pgSQL procedure that  inserts  full information about an employee into the database. The employee's details will include the following attributes:

• E# : Employee number

• NAME : Employee name

• DOB: Date of birth

• ADDRESS: Residential address

• HIREDATE : Date of hire

• L# : License number (if applicable)

• STATUS: Employment status

• EXPERIENCE : Experience (only for mechanics)

Consistency Constraint:

Your procedure must enforce the following logical consistency constraint:

•     The sets of drivers and mechanics must be disjoint.

o  This means that an employee cannot be both a driver and a mechanic.

o  It is not allowed for the same employee (with the same employee number E# and/or license number L#) to exist in both the MECHANIC and DRIVER tables.

Stored Procedure Requirements:

•     The procedure should:

o Insert full information about the employee into the relevant table(s).

o Check  the disjoint constraint:   Before  inserting,  ensure that the employee number E# and license number L# do not already exist in the other table (i.e., an employee cannot be both a driver and a mechanic).

o  If the consistency constraint is violated, the procedure should fail with an appropriate error message.

Execution Instructions:

1. First Execution :

o  Execute  the  procedure to insert full information for a new employee (either a driver or a mechanic) who does not violate the consistency constraint.

2. Second Execution :

o  Execute the  procedure again, attempting to insert an employee whose details (either E# or L#) will violate the consistency constraint (i.e., trying to insert an employee who is already in the other table).

o  This second execution should fail due to the constraint, demonstrating the procedure’s ability to enforce logical consistency.

3. PL/pgSQL Script:

o  Write and save the stored procedure implementation in a PL/pgSQL script named solution3.sql.

4. Execution Output:

o  Execute  the  script  twice  as  described  above  and  capture  the  output (including success and error messages) in a file named solution3.lst.

Submission Instructions:

You are required to submit two files:

1. solution1.sql – This file should contain all the SQL statements.

2. solution1.pdf – A report containing screen captures of your SQL output.

Report Guidelines:

•     Your report must include:

o Screenshots of the SQL output: Copy and paste the screen output from your terminal into a document, then save it as a PDF file.

o SQL statements processed : The report must list all SQL statements that were executed.

o  Ensure there are no errors in the output shown in your report.

SQL Script Requirements:

•     Include the following settings at the beginning of your script:

o  \set ECHO all – This will ensure that all SQL commands are echoed to the terminal.

o  \set ECHO none – Use this to stop echoing the commands when needed.

Important Notes:

•     Your printouts must satisfy all the above requirements. Any report that fails to meet these requirements will receive no marks.

Task 2 (5.0 marks)

Stored PL/pgSQL Trigger

Task Specification:

Objective:

You are required to implement  a row-level trigger that  ensures  the  following consistency constraint:

•     The column totalTripMade in the DRIVER table is currently empty (i.e., does not contain any values).

•     You need to create  a row trigger that automatically updates the totalTripMade column when a new trip made by a driver is inserted into the TRIP table.

Trigger Details:

•     The trigger will activate after each new trip is inserted into the TRIP table.

•     Once triggered, it will calculate the total number of trips made by the driver and update the totalTripMade column in the DRIVER table accordingly.

Important Notes:

• PL/pgSQL Script:

•     Implement the trigger logic in a PL/pgSQL script and save it as solution2.sql.

• No  need  to  handle  DELETE or  UPDATE cases : You  are only required to consider the scenario where new trips are inserted into the TRIP table.

•     Your trigger should only  update the totalTripMade column  based on  newly inserted trips. Other operations (such as trip deletions or updates) do not need to be considered.

• Recording Results:

•     Process the script (solution2.sql) in PostgreSQL and record the output (results of processing) in a file named solution2 in pdf format.

• Output File:

•     Ensure that your solution2.pdf file captures any messages or results from running the  script, such as  successful  trigger  creation  or  output  generated  during insertions into the TRIP table.

Submission Instructions:

You are required to submit two files:

1. Solution2.sql – This file should contain all the SQL statements.

2. Solution2.pdf – A report containing screen captures of your SQL output.

Report Guidelines:

•     Your report must include:

o Screenshots of the SQL output: Copy and paste the screen output from your terminal into a document, then save it as a PDF file.

o SQL statements processed : The report must list all SQL statements that were executed.

o  Ensure there are no errors in the output shown in your report.

SQL Script Requirements:

•     Include the following settings at the beginning of your script:

o  \set ECHO all – This will ensure that all SQL commands are echoed to the terminal.

o  \set ECHO none – Use this to stop echoing the commands when needed.

Important Notes:

•     Your printouts must satisfy all the above requirements. Any report that fails to meet these requirements will receive no marks.

Task 3 (3.0 marks) Task Specification:

Objective:

You are required to implement a stored PL/pgSQL function called LONGTRIP(DLNUM), which calculates the length (i.e., the total number of legs) of the longest trip performed by a driver. The driver is identified by their driving license number (L#), which corresponds to the DLNUM parameter passed to the function.

Function Details:

1. Parameters:

o DLNUM :  The  driving  license number of the driver (L# in the DRIVER table).

2. Function Logic:

o  The function should find the length of the longest trip (i.e., the trip with the most legs) performed by the driver with the given driving license number (DLNUM).

o  The function should consider drivers who have not performed any trips. For such drivers, the function should return a length of 0.

o  The function should correctly handle cases where a driver exists in the DRIVER table but has no associated trips in the TRIP table.

Expected Output:

•     For a driver who has performed trips, the function will return the total number of legs in their longest trip.

•     For a driver who has not performed any trips, the function should return 0.

Usage:

After implementing the LONGTRIP(DLNUM) function, use it in a SELECT statement to generate a report listing the names of all drivers, along with the length of their longest trip (calculated using the LONGTRIP function).

Example Query:

SELECT NAME, LONGTRIP(L#) AS LongestTrip FROM DRIVER;

This query should return the names of all drivers along with the length of their longest trip.

Execution Instructions:

1. PL/pgSQL Function:

o  Write  the  function  definition  and  save  it  in  a  PL/pgSQL  script  named solution3.sql.

2. Execution Output:

o  Use the function in a SELECT statement (as demonstrated above) to list the names of all drivers and their longest trips.

o  Save the output of this query in a file in pdf format,  and name it solution3.pdf.

Submission Instructions:

You are required to submit two files:

3. Solution3.sql – This file should contain all the SQL statements.

4. Solution3.pdf – A report containing screen captures of your SQL output.

Report Guidelines:

•     Your report must include:

o Screenshots of the SQL output: Copy and paste the screen output from your terminal into a document, then save it as a PDF file.

o SQL statements processed : The report must list all SQL statements that were executed.

o  Ensure there are no errors in the output shown in your report.

SQL Script Requirements:

•     Include the following settings at the beginning of your script:

o  \set ECHO all – This will ensure that all SQL commands are echoed to the terminal.

o  \set ECHO none – Use this to stop echoing the commands when needed.

Important Notes:

•     Your printouts must satisfy all the above requirements. Any report that fails to meet these requirements will receive no marks.