Project 2

• Alter the table Atlas to add the MaxStReg field. This field should contain the maximum number of students allowed to register for the class.
• Create procedure AddProfessor. This procedure inserts a new row into the Professor table. This procedure takes 5 arguments: first and last name, phone, office number, and a webpage. It should create a unique PID using the sequence you've created.
• Create procedure AddStudent that does the same thing for the table Student.
• Create procedure AddMajor that does the same thing for the Major table.
• Create procedure AddCourse. This procedure should add a new course to the Course table. It takes only two argumtens - title and description and should create a unique CID.
• Create procedure OfferCourse. This procedure offers a particular course next semester. In other words, it adds a new record into the Atlas table. It should take only two arguments - the course ID and the max number of students. If the second argument is not provided you may assume that it is equal to 25. The next semester should be chosen automatically. That is, your procedure should choose the correct values for the year and term fields.
• Create procedure RegisterStudent. This procedure takes two arguments - the student ID and the course ID. Student is allowed to register if two conditions are sutisfied:
  1. the number of students currently registered for the course is less then the maximum allowed number.
  2. student has taken all the prerequisites for the course. (Note: a class is considered to be taken if in the Transcript table this student has a positive grade for this class [A, B, C, or D]).
  If any of these conditions is violated, your procedure has to raise an application error and print a clear error message. If both of these conditions are true, this procedure has to add a new row into the Registration table.
• Create procedure StartSemester. This procedure takes no arguments and it works with two tables: Registration and Section. Roughly speaking, this procedure should create classes for all registered students. In other words, this procedure needs to create sections for all classes. If there are more than 5 students registered for the class you need to create more than one section. each section should get its individual snum (section number). This number should be individual only for this particular course. That is, you may have IST467 section 101 and IST466 section 101, but you may not have another IST467 section 101. This procedure also should take care about the correct values year and term. You may assume that this procedure is called before the semester starts.

Make the last two procedures autonomous transactions.

To create all these new unique IDs, write a function getID that takes only one argument that specifies what kind of ID it needs to create (CID, SID, PID, etc), uses appropriate sequences and returns the ID. Use this function in all your procedures.

This project is due by 11:59pm on Thursday, February 12, 2004.