Database CS411 Systems
Spring 2007 HW #2 - Part 2 Due: 1:55pm CST, 03/06/07 Problem 5 SQL
(30 points, 6 points each)
Consider the following relations: Students(sid, sname, department) Courses(cid, cname, department) Register(sid, cid, semester, year, grade) Express the following queries in SQL. (5.1) Find the names of all CS students; Answer: SELECT sname FROM Students WHERE department = CS; (5.2) For each course, show its total number of registrations in Spring semester of 2006; Answer: SELECT cid, count(*) FROM Register WHERE semester=Spring AND year=2006 GROUP BY cid; or SELECT cid, cname, count(*) FROM Course, Register WHERE Courses.cid=Register.cid AND semester=Spring AND year=2006 GROUP BY Courses.cid, cname; or SELECT cid, count(*) FROM Course, Register WHERE Courses.cid=Register.cid AND semester=Spring AND year=2006 GROUP BY Courses.cid;
(5.3) Find the courses with the highest total number of students from departments dierent from where the course is oered; Answer:
1
SELECT Courses.cid, Courses.cname, COUNT(DISTINCT Students.sid) AS count1 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid, Courses.cname HAVING count1 = SELECT MAX(count2) FROM (SELECT COUNT(DISTINCT Students.sid) AS count2 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid ); or SELECT Courses.cid, Courses.cname, COUNT(*) AS count1 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid, Courses.cname HAVING count1 = SELECT MAX(count2) FROM (SELECT COUNT(*) AS count2 FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department <> Students.department GROUP BY Courses.cid ); Grading Notes: It is ok not to return Courses.cname. (5.4) Find the names of students who have taken courses in both CS and ECE; Answer: ( SELECT Students.sname FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department = CS ) INTERSECT ( SELECT Students.sname FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department = ECE ); or SELECT Students.sname FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department=CS 2
AND sname IN
( SELECT Students.sname FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department=ECE );
or SELECT sname FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND Courses.department IN (CS,ECE) GROUP BY Students.sid, sname HAVING COUNT(DISTINCT Courses.department)=2
(5.5) Find the records of A+ in CS courses. For each such record, return the student name, course name, semester, and year.; Answer: SELECT Students.sname, Courses.cname, semester, year FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid AND grade = A+ AND Courses.department=CS;
Problem 6 View
(20 points, 10 points each)
Continue Problem 5, (6.1) Create a view LoyalStudent that records every pair of student and department if that student has taken more than 10 courses from that department. You can include as many columns (attributes) in the view as necessary, as long as the view can be used in answering the following question. Answer: CREATE VIEW LoyalStudent AS SELECT Courses.department, Students.sid FROM Courses, Students, Register WHERE Courses.cid=Register.cid AND Students.sid=Register.sid GROUP BY Courses.department, Students.sid HAVING COUNT(DISTINCT Courses.cid) > 10; Grading Notes: It is ok to return more attributes in the view. Grading Notes: DISTINCT is optional. Grading Notes: Check the grouping attributes. For example, Courses.cid should NOT be grouped if COUNT() is to work correctly. (6.2) Using the view LoyalStudent, nd the CS courses that have never been taken by any loyal student of CS courses. Return the course names. Answer: 3
SELECT FROM * Courses WHERE department=CS AND cid NOT IN ( SELECT Register.cid FROM LoyalStudent, Register WHERE LoyalStudent.sid = Register.sid AND LoyalStudent.department=CS );
Problem 7
Denition and Modication
(20 points: (7.1) 10 points; (7.2) 5
points; (7.3) 5 points) With the following relations, write the following SQL declarations and modications. Students(sid, sname, department) Courses(cid, cname, department) Register(sid, cid, semester, year, grade) (7.1) Dene a suitable schema for relation Students, Courses, and Register, respectively; Declare the keys on Students, Courses, and Register; Answer: CREATE TABLE Courses ( cid INT PRIMARY KEY, cname CHAR(30), department CHAR(30) ); CREATE TABLE Students ( sid INT PRIMARY KEY, sname CHAR(30), department CHAR(30) ); CREATE TABLE Register ( sid INT REFERENCES Students(sid), cid INT REFERENCES Courses(cid), semester CHAR(30), year INT, grade CHAR(30), PRIMARY KEY (sid, cid, semester, year) ); Grading Notes: It is ok without declaring the foreign keys (REFERENCES). (7.2) Insert into the database the fact that ECE student John Smith, with id 1001, got A in CS 101 (i.e., course id cid = 101) in Spring 2006; Answer: INSERT INTO Students VALUES(1001, John Smith, ECE); INSERT INTO Register VALUES(1001, 101, Spring, 2006, A); (7.3) Delete all courses in General Engineering that do not have any students registered. Answer: DELETE FROM Courses WHERE department=General Engineering AND NOT EXISTS ( SELECT * FROM Register WHERE Courses.cid=Register.cid ); 4
or DELETE FROM Courses WHERE department=General Engineering and cid NOT IN (SELECT cid FROM Register)
Problem 8 Constraints and Triggers
Consider the following relational schema. Students(sid, sname, department) Courses(cid, cname, department) Register(sid, cid, semester, year, grade)
(30 points, 5 points each)
(8.1) Suppose you want to enforce the constraint that no registration year is later than 2007. (a) Can you use CHECK to implement this constraint? If Yes, how (provide SQL)? If No, why? Answer: CREATE TABLE Register ( sid INT REFERENCES Students(sid), cid INT REFERENCES Courses(cid), semester CHAR(30), year INT CHECK (year <=2007), grade CHAR(30), PRIMARY KEY (sid, cid, semester, year) ); (b) Answer the same questions for assertion instead of CHECK. Answer: CREATE ASSERTION registeryear CHECK ( NOT EXISTS (SELECT * FROM Register WHERE year > 2007)); (c) Answer the same questions for trigger instead of CHECK. Answer: (1) trigger for UPDATE: CREATE TRIGGER updateyear AFTER UPDATE OF year ON Register REFERENCING OLD ROW AS OldTuple, NEW ROW AS NewTuple FOR EACH ROW WHEN (NewTuple.year > 2007) UPDATE Register SET year = OldTuple.year WHERE year = NewTuple.year; (2) trigger for INSERT 5
CREATE TRIGGER insertyear AFTER INSERT ON Register REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN (NewTuple.year > 2007) DELETE FROM Register WHERE year = NewTuple.year; or CREATE TRIGGER insertyear AFTER INSERT ON Register REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN (NewTuple.year > 2007) DELETE FROM Register WHERE cid = NewTuple.cid AND sid = NewTuple.sid AND semester = NewTuple.semester AND year = NewTuple.year; or CREATE TRIGGER insertyear BEFORE INSERT ON REGISTER REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN NewTuple.year > 2007 BEGIN Set NewTuple.year=2007 END Grading Notes: A trigger for either INSERT or UPDATE is ne, i.e., just giving one of the above two triggers is ne. (8.2) Suppose you want to enforce the following constraint. When inserting a tuple into the Register table, sid of the inserted tuple must exist in the sid column of table Students. If not exist, then it must be automatically inserted into the table Students. (a) Can you use CHECK to implement this constraint? If Yes, how (provide SQL)?If No, why? Answer: No. Because CHECK cannot enforce action. (b) Answer the same questions for assertion instead of CHECK. Answer: No. Because assertion cannot enforce action. (c) Answer the same questions for trigger instead of CHECK. Answer:
6
CREATE TRIGGER refintegrity AFTER INSERT ON Register REFERENCING NEW ROW AS NewTuple FOR EACH ROW WHEN (NOT EXISTS (SELECT * FROM Students WHERE sid=NewTuple.sid)) INSERT INTO Students VALUES(NewTuple.sid, NULL, NULL);
7