We finished triggers (Chapter 7), and moved on to database programming (Chapter 9). Next week, we will talk about database connectivity (another form of programming with databases), views and indices (Chapter 8).
1. (Reading Assignment) Read Sections 9.1-9.5 of the textbook. If you want to read ahead, you can start on Sections 8.1 and 8.2.
2. (Functions and Procedures, 15pt) Implement functions for the following tasks. Include testruns by embedding the functions in SELECT queries.
Write a function member_ct(gname) which takes as an argument the name of a studentgroup, and returns the number of members the group currently has.
Write a fucntion standing(st_id) which takes as an argument the ID st_id of a student and returns the standing of a student, where a frehsman is a student who started <= 2 years ago, a sophomore <= 4, a junior is a student <= 6. Everybody else is a senior. For testing list all students (SID, Lastname, Firstname) with their standing. Hint: you can use extract(year from sysdate) to get the current year.
Write a function member_yrs(gname, st_id) which take as an argument the name of a group gname, and the ID st_id of a student, and returns for how many years the student has been a member of the group. If the student is not a member of the group, return -1. Hint: you can use extract(year from sysdate) to get the current year. Hint 2: in a first step solve the problem assuming that the student is a member of the group. Deal with the other case separately.
3. (Functions and Procedures, 15pt). Implement functions and procedures for the following tasks. Include testruns (you can use call from SQL or run them inside a PL/SQL block, as we did in class).
[7pt] Write a procedure last_offered(dept, coursenr) that takes as an argument the Department and CourseNr of a class, and then prints a message (using dbms_output.put_line) in which year the class was last offered, or the information that it has never been offered (according to the enrolled table). E.g. a call to last_offered('CSC', 389) should print something like 'CSC 389 has never been offered', while last_offered('CSC', 440) would result in 'CSC 440 was last offered in 2018'.
[8pt] Write a procedure make_pres(st_id, gname) that takes a student ID st_ID and the name of a student group and makes that student the new president of that group. If the student is not a member of the group at the time, we first make the student a member of the group (and set the value of joined to the current year).
4. (Cursor, 10pt) Write a PL/SQL procedure that enforces that every president of a studentgroup is a member of that studentgroup. To do so, use a cursor to process all studentgroups and forcibly make the president a member of the studentgroup if they aren't already. (Do not in any way hardcode the current database instance in which we have four studentrgoups; your code needs to keep working for an arbitrary number of studentgroups). If the president has to be made a member of the studentgroup, set the value of joined to the current year. If the president is a member of the studentgroup, or the studentgroup doesn't have a president, nothing has to be done, of course.