D
8 9
1. (a)
(b)
(c)
(d)
(e) 2. (a)
COMS W4111.001–Introduction to Databases Fall 2020
Homework Assignment 2
Solutions
ΠD (T2 )=
T1 IT1.C=T2.A T2 =
T1 I T2 =
T1 − T2 =
(T1 IT2)I(T2 IT2)=
ρ(T1(1 → starName1,2 → title1,3 → year1),Stars)
ρ(T2(1 → starName2,2 → title2,3 → year2),Stars) Πtitle1,year1,title2,year2(T1 I(title1̸=title2∨year1̸=year2)∧starName1=starName2 T2)
A
(B)
C
(B)
D
1 1 2 2
a a b b
2 2 2 2
b b b b
8 9 8 9
A
B
C
D
2 2
b b
2 2
8 9
A
B
C
1 2 3
a b c
2 2 3
A
B
C
D
2 2
b b
2 2
8 9
(b)
ρ(T1(2 → title1,3 → year1,4 → title2,5 → year2),Stars IstarName Stars)
ΠstarName(Stars)−ΠstarName(σyear1=year2∧title1̸=title2(T1))
(c)
Stars/Πtitle,year(σyear≥2000∧year≤2019∧studioName=′Paramount′(Movies)) 3. SQL queries:
1
a. Print the name and department of each professor who has taught a course not from their department at least once.
SELECT DISTINCT P.pname, P.dname
FROM section S, prof P
WHERE S.pname=P.pname AND S.dname!=P.dname
pname
dname
Smith, S.
Industrial Engineering
Clark, E.
Civil Engineering
b. Print the sid and name of each student who has earned a 3.5 or higher grade in at least two different courses.
SELECT DISTINCT E1.sid, S.sname
FROM enroll E1, enroll E2, student S
WHERE E1.sid=E2.sid AND S.sid=E1.sid AND
E1.grade>=3.5 AND E2.grade>=3.5 AND
(E1.dname!=E2.dname OR E1.cno!=E2.cno OR E1.sectno!=E2.sectno)
c. Print the name and age of the student(s) with the highest GPA in their exact age group (i.e., the name and age of the student(s) with the highest GPA among all 15 year olds, the name and age of the student(s) with the highest GPA among all 16 year olds, and so on), for ages less than or equal to 18.
SELECT S1.sname, S1.age
FROM student S1
WHERE S1.age<=18 AND S1.gpa=(SELECT MAX(S2.gpa)
FROM student S2
WHERE S2.age=S1.age)
sid
sname
49
Villa-lobos, M.
90
Zappa, F.
67
Altenhaus, Gloria
64
Fred, Edwin B.
sname
age
Baker, C.
18
News, Nightly
15
Zappa, F.
16
Morgan, D.
18
d. For each department that both (a) has the substring "Engineering" in its name (e.g., "Electrical Engineering") and (b) has at least 2 students majoring in the department, print the name of the department and the average GPA of the students who major in the department.
SELECT M.dname, AVG(S.gpa)
FROM major M, student S
WHERE M.dname LIKE '%Engineering%' AND M.sid=S.sid
GROUP BY M.dname
HAVING COUNT(*)>=2
e. Some courses are popular among students just because students enrolled in those courses usually get good grades. Print the department name, course number, and course enrollment of each course C such that the following two conditions hold: (1) course C’s enrollment is at least 3% larger than the average enrollment of the courses offered by C’s department, and (2) course C’s average grade is at least 3% larger than the average grade obtained by students in the courses offered by C’s department. Assume that the enrollment of a course is the sum of the enrollment of all its sections. You can completely ignore any course that has no students enrolled in it.
SELECT E.dname, E.cno, COUNT(*) as enrollment
FROM enroll E
GROUP BY E.dname, E.cno
HAVING COUNT(*)>=1.03*(SELECT COUNT(*)/COUNT(DISTINCT E1.cno)
FROM enroll E1
WHERE E1.dname = E.dname) AND
AVG(E.grade)>=1.03*(SELECT AVG(E2.grade)
FROM enroll E2
WHERE E2.dname = E.dname)
dname
avg
Civil Engineering
2.91428572365216
Industrial Engineering
2.76999999061227
Chemical Engineering
3.29999999566512
dname
cno
enrollment
Computer Sciences
726
17