=========================preview======================
(COMP231)midterm01S_sol.pdf
Back to COMP231 Login to download
======================================================
Comp 231 Midterm C March 13, 2001
Problem 1a (32%)
Given a database with the four relation schemas below, express the following queries using SQL, relational algebra, TRC (calculus) and QBE.
Student (sid, sname) Attend(sid, cid, grade)
Class (cid, cname, pid) Professor (pid, pname)

Q1: Find the names of students who took a class, taught by prof. Dimitris and their grade (in this class) was A. Q2: Find the names of students who got an A grade in every class that they attended.
Assumptions: primary keys are underlined and foreign keys in italics. Each student has taken at least one class.

Query SQL (4% each)
algebra (4% each)
(tuple) calculus (4% each)
QBE (4% each)
Student
sid
sname
Q1
_S
P.
Select S.sname
{X | $ S .Student $ A .Attend
psname(sgrade=A and pname=Dimitris
From Student S, Attend A, Class C, (Student JOINsidAttends $ C . Class $ P . Professor
Professor P JOINcid (X.name = S.name . S.sid=A.sid
Where S.sid=A.sid and A.cid=C.cid ClassJOINpidProfessor)) . A.cid=C.cid . C.pid = P.pid
and C.pid=P.pid and P.pname=Dimitris . P.name='Dimitris' .
and A.grade='A' A.grade='A')}

Attend sid cid grade
_S _C A

Class cid cname pid
_C _P


Q2
Select S.sname
psname(Student
{X | $ S .Student .$ A
Student
sid
sname
_S
P.
From Student S
JOINsid(psidAttend-
.Attend (X.name = S.name .
Where not exists psid(sgrade<>'A'Attend))) S.sid=A.sid . A.grade <>'A'}
(Select *
From Attend A Notice the assumption that each
Where A.grade <> 'A' and student has taken at least one
A.sid=S.sid) class.

Attend sid cid grade
. _S <>A

Problem 1b (12%)
Express in words the result of the following SQL, algebra and TRC statements on the schema of problem 1a ("p" and "/" are the algebra operators rename and division respectively, "." is the negation symbol in calculus) . For the SQL query assume that the grades are ordered, i.e., A>B>C>D.
Select A.cid, S.sname From Student S, Attend A Where S.sid=A.sid and not exists (Select * From Attend A2 Where A2.cid=A.cid and A2.grade > A.grade) p(Temp1, pcid(ClassJOINpid(ssname='Dimitris'Professor))) p(Temp2, (pcid,sidAttend) / Temp1) p(Temp3, psname (Student JOINsidTemp2) {S | S .Student $ A1 .Attend .$ A2 .Attend (S.sid=A1.sid . S.sid=A2.sid . A1.cid <>A2.cid)}
Answer (4% ): For each class id display the names of students that got the maximum grade in this class Answer (4% ): Display the names of students who attended all classes taught by Dimitris Answer (4% ): Display the tuples of students who attended exactly one class

Problem 1c (15%)
Write the SQL statements for the following queries (on the schema of problem 1a):
1] Find the names of students who attended at least two classes taught by professor with pid=100 (5%).
2] For each student that has at least 3 'A's, display the student id and the number of A's (5%).
3] Find the class id of the class with the maximum a