Project 3

• modify the Students table to add one more field login. The values stored in this field should be unique.
• the login name for each student should be the first character of the student's first name followed by the 6 characters of the student's last name and a number. For example, Bob Smith can have login name bsmith3, William Tester can have a login name wtester1, etc.
• write an anonymous PL/SQL block to create login names for all students already entered in the database and create user account for each student.
• modify the AddStudent procedure to create user account at the same time you are adding a new student record to the table.

Design a role Student to let every student ability

• to register for a class
• to check her/his own grades (make sure a student cannot see other students' grades)
• to change her/his major
• to find out some information about professors (like phone and office number)
• to look through the courses offered next semester
• and all other privileges a student needs to have to work with the database, but make sure you are not giving any extra privilege.

• Since each student has an insert privilege on the Registration table, he/she can insert any new record in this table. We need to provide some tool to control the following things:
  • student can register only him/her-self; that is, student is allowed to insert values with his/her SID in the table Registration
  • student is allowed to register only for the offered courses
  To implement these, write a trigger RegisterTrig that checks corresponding values and allows or disallows the inserting operation. Please notice, that a student doesn't have to use procedure Register you've created before. This trigger is needed if the student is using insert statement.
• Allow the student to change only her/his major. Any student has select privilege on the Student table, but each student can see only hers/his own data (we implemented this through a view). To change a major, the student needs to have an update access on that view. This means, that this feature may be implemented via usual Oracle restrictions, but I'd like you to do it in both ways:
  1. by restrictions on update of the view;
  2. by the trigger ChangeMajorTrig - this trigger should be an instead of trigger and it should take care of that student may change only her/his major and student.
• Every student is allowed to drop a class. In other words, student is allowed to update his/her grade in the table Transcript from NULL to "W". However, a class can be dropped only if this student is currently taking it; that is, the field Grade in the Transcript table contains NULL, and the last date of dropping the classes has not passed. To implement this,
  1. modify table Semester to add one more field LastDropDay
  2. add some dates in the new field
  3. create table Footprints that contains the following fields:
     • who
     • when
     • which student
     • from what class
     • did what
  4. write trigger DropClassTrig that checks all the conditions and allows or disallows do the update. No matter if the update is allowed or not your trigger should insert a new record in the table Footprints. This record must contain the information: what Oracle user was trying to perform the operation, when the user tried it, for which student (SID), for which class (CID, section), what happened (class was dropped, class wasn't dropped, etc). Please notice that this trigger is for the view the students has select privilege on, not on the Transcript table itself.
• We would like to monitor who sets/changes students grades and when those changees are made. To do that please write trigger ChangeGradeTrig that inserts in the Footprints tables information about who is updating and when updates were made in the Transcript table by changing grades. This trigger also should store the old and the new grades in the last field of the Footprints table.

This project is due by 11:59pm on Monday, March 22, 2004.