1 / 10

Tutorial 6 : Relational Def / Updates / Triggers

Tutorial 6 : Relational Def / Updates / Triggers. Question: Create three tables using SQL: Students, Courses, and Enrolled. In table definitions, you must enforce the following constraints: Students: sid, sname, major, year_of_study, gpa Courses: cid, cname, enroll_count, lecturer

warner
Download Presentation

Tutorial 6 : Relational Def / Updates / Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tutorial 6: Relational Def / Updates / Triggers

  2. Question: • Create three tables using SQL: Students, Courses, and Enrolled. In table definitions, you must enforce the following constraints: • Students: sid, sname, major, year_of_study, gpa • Courses: cid, cname, enroll_count, lecturer • Enrolled: sid, cid, score • A student can be enrolled into a course only if he/she exists in Students table. • If a student is removed from Students table, his/her enrollment record(s) should also be deleted from Enrolled table. • Only the courses available in Courses table can be enrolled. • gpa must be between 0 and 4. • score must be between 0 and 100 if not NULL.

  3. Answer: • CREATE TABLE Students(sid CHAR(8), sname CHAR(20), major CHAR(10), year_of_study INTEGER, gpa REAL, PRIMARY KEY (sid), CHECK (gpa >= 0 AND gpa <= 4)); • CREATE TABLE Courses(cid CHAR(8), cname CHAR(20), enroll_count INTEGER, lecturer CHAR(20), PRIMARY KEY (cid)); • CREATE TABLE Enrolled(sid CHAR(8), cid CHAR(8), score REAL, PRIMARY KEY (sid, cid), FOREIGN KEY (sid) REFERENCES Students(sid) ON DELETE CASCADE, FOREIGN KEY (cid) REFERENCES Courses(cid), CHECK (score IS NULL OR score BETWEEN 0 AND 100));

  4. Question: • Consider the following tables: • Students(sid, sname, major, year_of_study, gpa) • Courses(cid, cname, enroll_count, lecturer) • Enrolled(sid, cid, score) • Perform the following actions using SQL. • Enrolled all CS/IS majored year 1 students into COMP1160, with initial scores set to NULL • Delete all courses whose enrollments are less than 5. • Update the gpa of all students enrolled in 1160 by 10%.

  5. Answer: • Students(sid, sname, major, year_of_study, gpa) • Courses(cid, cname, enroll_count, lecturer) • Enrolled(sid, cid, score) • Enrolled all CS/IS majored year 1 students into COMP1160, with initial scores set to NULL • INSERT INTO Enrolled SELECT sid, ‘COMP1160’, NULL FROM Students WHERE (major = ‘CS’ OR major = ‘IS’) AND year_of_study = 1;

  6. Answer: • Students(sid, sname, major, year_of_study, gpa) • Courses(cid, cname, enroll_count, lecturer) • Enrolled(sid, cid, score) • Delete all courses whose enrollments are less than 5. • DELETE FROM CoursesWHERE (cid IN ( SELECT cid FROM Enrolled GROUP BY cid HAVING count(*) < 5));

  7. Answer: • Students(sid, sname, major, year_of_study, gpa) • Courses(cid, cname, enroll_count, lecturer) • Enrolled(sid, cid, score) • Update the gpa of all students enrolled in COMP1160 by 10%. • UPDATE StudentsSET gpa = gpa * 1.1WHERE sid IN ( SELECT sid FROM Enrolled WHERE cid = ‘COMP1160’);

  8. Question: • Again consider the following tables: • Students(sid, sname, major, year_of_study, gpa) • Courses(cid, cname, enroll_count, lecturer) • Enrolled(sid, cid, score) • Automate the following procedures by using trigger. • Whenever a record in Enrolled is inserted/deleted, update enroll_count in Courses.

  9. Answer: • Whenever Enrolled is inserted, update enroll_count in Courses. • CREATE OR REPLACE TRIGGER Increase_Enrollment_Count AFTERINSERT ON Enrolled FOR EACH ROW BEGINUPDATE Courses SET enroll_count = enroll_count + 1 WHERE cid = :new.cid; END;.

  10. Answer: • Whenever Enrolled is deleted, update enroll_count in Courses. • CREATE OR REPLACE TRIGGER Decrease_Enrollment_Count AFTERDELETE ON Enrolled FOR EACH ROW BEGINUPDATE Courses SET enroll_count = enroll_count - 1 WHERE cid = :old.cid; END;.

More Related