Due: October 27, 1998 (Tuesday)

Consider the following tables for handling simple student enrollments.

H3_Majors (Major, MajorDesc)
H3_Students (StudentID, SLastName, SFirstName, SMajor) with SMajor a foreign key of H3_Majors(Major).
H3_Courses (CourseID, CMajor, CNumber, CTitle, CDesc) with SMajor a foreign key of H3_Majors(Major).
H3_Enrol (EnrolID, StudentID, CourseID, Score, Grade) with EnrolID and StudentID foreign keys of H3_Students(StudentID) and H3_Courses(CourseID) respectively.

Note that Grade is CHAR(2), e.g. 'A', 'B-', etc.  Score is the raw score, e.g. 75.2, 98, etc.

The following script,  h3_createtables.sql, creates the tables and insert some rows into the tables.  You may run and study the script for a better understanding of the tables.

The script also defines a view:

H3_Raw_Enrol(EnrolID, StudentID, CourseID, Score) which allows updating without the grade.  The grade can be calculated from the score by the function ConvertToGrade.

To be able to correctly insert tuples into H3_Raw_Enrol, an instead-of trigger is
defined:  h3_Raw_Enrol_Insert.sql.  This will allow the correct insertion using the view, such as:

insert into H3_Raw_Enrol (EnrolID, StudentID, CourseID, Score)
values (100, 100, 100, 67.5);

(1)  It is decided that the tables should be re-designed.  In the table H3_Students, three attributes are added: SGPA, STotalCredit and STotalGP, which correspond to the total credits and the total grade points of the student.  SGPA, the GPA of the student, is now a derived attribute equals to STotalGP / STotalCredit.

Also, in the table course, it is decided to store the number of credits of the course in the attribute CCredit.

Modify H3_CreateTables.sql to make the changes and stored the file as H3sol_CreateTables, in the hw directory.

(2)  Create the stored function ConvertToGradePoint(grade) to convert grades (A, B-, D+, etc) to grade points (4, 2.6667, 1.3333, etc).

(3)  Rewrite the trigger to handle the new design.  Note that your trigger should now also be able to handle the case when the value of Score is not submitted.  Save the work as H3sol_Raw_Enrol_Insert.sql.

Print out hard copies of all your scripts.  Try to insert three to four enrollments via H3_Raw_Enrol, with or without scores.  List the contents of the relevant tables before or after insertion.