HW11: PlPgSQL due Mon 08 Apr 13:00

Allowed and Disallowed Resources

• Examples and sample code found here: https://josephus.hsutx.edu/classes/db/source/
• The SQL handout provided in class and also available here: https://josephus.hsutx.edu/classes/db/source/sql.pdf
• A text editor (or VSCode) that provides syntax highlighting. You MAY NOT USE an extensions to such editors/IDEs that produce code.
• The phpPgAdmin front end to your personal database (login is here: https://csci.hsutx.edu/phpPgAdmin/
• Video instructions provided in Canvas as part of this course. You MAY NOT USE any other video resources.
• Any handouts provided by the instructor as part of this course.
• Your own course notes
• Your instructor
• Discussions about the assignment with other students as long as you never look at the code produced by another student and you never receive instructions about solving the homework. That is, discussions need to be about concepts and understanding the technologies and not about how to solve the particular problem posed in this assignment.

You may NOT use/access:

• Resources not expressly listed above, including, but not limited to, the following ...
• Source code not provided as part of this assignment. (Obviously, this includes, but is not limited to, source code written by other students whether current or in the past).
• Code-generating tools (of which ChatGPT is one example).

Failure to abide by these guidelines will result in a zero for the assignment and the incident will be reported to the university provost as a violation of the university academic integrity policy. A second incident of academic dishonesty (whether from this course or another computer science course) will result in an F in the course.

Description

The hotel has a script that is executed nightly that will send emails to it customers. Suppose that script determines what emails should be sent by looking in the database in a table called pending_emails that has the following fields: customer_id, email_type, other_id. For now let's assume there are only two types of emails to be sent. If the email_type is 1 then the email is for the purpose of notifying the customer that the credit card they have on file has expired. If the email_type is 2 then the email is for the purpose of reminding the customer that they have a reservation the next day.

The other_id field will hold a payment_method_id value if email_type is 1 and will hold a reservation_id value if email_type is 2.

For this assignment you will not write an email-sending script. Instead you will write a PLPGSQL function that (presumably) would also be run nightly for the purpose of populating the pending_emails table as described above. To do so modify your SQL script to complete these steps:

• (2 pts) Add an email_address field to the customer table and add insert statements to populate the field with some bogus email addresses.
• (4 pts) Create a pending_emails table that has the structure described above. Also, create a sent_emails table that has the same structure as the pending_emails table. We want to keep track of emails we have already sent because we don't want to annoy our customers by sending them daily notifications for which they have already been contacted.
• (18 pts) Write PLPGSQL function called populate_pending_emails that accepts a date as a parameter and performs the following actions:
  1. Move all entries from the pending_emails table to the sent_emails table.
  2. For all forms of payment on file find any that have expired (i.e, whose expiration date comes before the date passed to the function as a parameter) create an entry in the pending_emails table (unless, of course, there exists a corresponding entry in the sent_emails table).
  3. For all reservations that are only one day away (in the future) from the date passed as a parameter add an appropriate entry in the pending_emails table. Again, be sure to check the sent_emails table first.
• (4 pts) Write a series of calls to your populate_pending_emails function that you feel demonstrates that it works properly. You code should be written so that I can run the entire script cleanly and see the results of your function calls by inspecting the new tables.

Grading and Submission