Question

Introduction to Database Systems

The following is the ỤML class diagram which was discussed in class Students Departments DName 1SName majorsAbbr GradYear enrolls offers Enrollments Sections Courses Grade Title Professor OfYearOffered offeredIn A UML diagram for a university Here we assume courses are year long courses The artificial primary keys and foreign keys are added to the UML diagram to get the schemas. The schemas Departments (DId DName, Abbr) Courses (CId, Title, Deptld) Sections (SectId, Professor, YearOffered, Courseld) Students (StudentId, SName, GradYear, Majorld) Enrollments (EId, Grade, Studentld, Sectionld) Primary keys are underlined; foreign keys are in italics. 3. Suppose we want to ensure that the foreign key Courseld values of the SECTION table records are never modified, either accidentally or intentionally. That is, no section should possibly be assigned to a different course other than the course it was originally intended for. Design a trigger that intercepts a modification to this field and sets the value back to what it was

Answer 1

Answer.)

Trigger to update CourseId to its old value, after it is being modified:

create or replace trigger update_actual_course
after update on Sections
FOR EACH ROW
begin
update Sections set CourseId= :old.CourseId
where SecId = :old.SecId;
end;