Question

Consider the University Database with the following relations:

Professors (pid, pname, dept, ext)

Students (sid, sname, major-dept, year)

Courses (cid, cname, dept, credithours)

Enrollment (sem-year, sid, cid, grade)

Teaches (pid, cid, sem-year, class-size)

where,

Professors: All professors have professor id (pid), name (pname), department that they work (dept), and a phone number extension for their office (ext).

Students: All students have id (sid), name (sname), department for their major (major-dept), and a year (year i.e, freshman, sophomore, junior, etc). Courses: All courses have a course id (cid), course name (cname), department (dept), and total credit hours (credithours).

Enrollment: has a semester year (sem-year), enrolled student id (sid), course id (cid), and grade that student earns (grade).

Teaches: has a professor id (pid), course id (cid), semester year (sem-year), and class size (class-size).

Attributes “dept” in relations Professors and Courses, and attribute “major-dept” in relation Students have the same domain, and have values like “CDS”, “EE”, “CE”, etc. Attribute “sem-year” has values like “Spring2016”, “Fall2015”, etc. Assume that cids are unique, i.e. if there are multiple sections of a course, each section has a unique cid.

Express the queries below using Relational Algebra.

1. Find sids, names and major-dept of students who enrolled in a course that is taught by professor James.

2. Find pid and names of professors who teach no courses in “Fall2015”.

3. Find cid and cname of courses that are offered by “CDS” department that are taught by professors who are from another department in “Fall2015".

4. Find pid and names of professors who teach only courses offered by “CDS” department.

5. Find pnames and pids of professors who teach every course offered by “CDS” dept.

6. Find sids of students who enroll in “Fall2015” every 3 credit hour course offered by “CDS” department.

7. Find cids and names of courses in which every student majoring in “CDS” enrolled in “Fall2015”.

Answer 1

1. QUERY for  finding sids, names and major-dept of students who enrolled in a course that is taught by professor James.

SELECT student.sid,student.sname,student.major-dept FROM student LEFT JOIN enroll ON student.sid=enroll.sid Where cid= (SELCET cid FROM teaches INNER JOIN Professor ON teaches.pid = Professor.pid WHERE Professor.pname="James"  );

2. Query for finding pid and names of professors who teach no courses in “Fall2015”.

SELECT Professor.pid,Professor.pname FROM Professor LEFT JOIN Teaches ON Professor.pid=Teaches.pid WHERE NOT teaches.sem-year ="Fall2015";

3. Query for  finding cid and cname of courses that are offered by “CDS” department that are taught by professors who are from another department in “Fall2015".

SELECT courses.cid,courses.cname FROM courses INNER JOIN teaches ON courses.cid=teaches.cid WHERE Professor.dept="CDS" AND teaches.pid=(SELECT Professor.pid FROM Professor INNER JOIN teaches ON Professor.pid=teaches.pid WHERE teaches.sem-year="Fall2015" AND NOT Professor.dept="CDS" ) ;

4.  Query for finding pid and names of professors who teach only courses offered by “CDS” department.

SELECT pname, pid
FROM Professor LEFT JOIN teaches ON Professor.pid=teaches.pid
WHERE Professor.pid = (SELECT Professor.pid FROM Professor.pid=teaches.pid WHERE Professor.dept="CDS");

5.Query for finding pnames and pids of professors who teach every course offered by “CDS” dept.

SELECT pname, pid
FROM Professor LEFT JOIN teaches ON Professor.pid=teaches.pid
WHERE Professor.pid = ALL (SELECT Professor.pid FROM Professor.pid=teaches.pid WHERE Professor.dept="CDS");

6. Find sids of students who enroll in “Fall2015” every 3 credit hour course offered by “CDS” department.

SELECT student.sid FROM student LEFT JOIN Enroll ON studentsid=Enroll.sid WHERE student.major- Enroll.sem-year=""Fall2015" AND Enroll.cid=(SELECT Enroll.cid FROM Enroll INNER JOIN Courses ON Enroll.cid=courses.cid WHERE courses.credit-our="3" AND courses.dept="CDS" );

7.Find cids and names of courses in which every student majoring in “CDS” enrolled in “Fall2015”.

SELECT cid,cname FROM courses WHERE ALL (SELECT student.cid FROM student INNER JOIN enrolled ON student.sid =Enroll.sid WHERE student.major-dept="CDS" AND enroll.sem-year="Fall2015");