Due: Monday, October 29

Please put your code in two SQL script files hw3-create.sql and hw3-drop.sql. Running hw3-create.sql should create all the stored functions and triggers, and running hw3-drop.sql should drop them from the database. Please upload the script files to CSNS. Note that file uploading will be disabled automatically after noon of the due date, so please turn in your work on time.

1. (40pt) Here at Cal State LA an academic year is divided into four quarters as follows (strictly speaking there is one or two weeks between quarters, but for simplicity we will include those weeks into the quarters):

• Winter Quarter is from Week 1 to Week 12.
• Spring Quarter is from Week 13 to Week 24.
• Summer Quarter is from Week 25 to Week 37.
• Fall Quarter is from Week 38 to the end of the year.

Furthermore, each quarter is represented by a four-digit code. The first 3 digits of the code is the result of (year_of_the_quarter - 1900), and the last digit is either 1 (for Winter), 3 (for Spring), 6 (for Summer), or 9 (for Fall). For example, the code for the Fall 2012 quarter is 1129, and the code 1011 represents the Winter 2001 quarter.

For this exercise, please write two stored functions in PL/pgSQL.

The first function quarter( date ) takes a date argument and returns the code of the quarter that the date belongs to. For example, quarter(current_date) should return 1129.

The second function quarter( integer ) takes a quarter code as the argument and returns the name of the quarter as a string. For example, quarter(1129) should return 'Fall 2012'.

2. (30pt) Exercise 4 in the Sample Midterm.