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. (Please ask for clarifications if you have questions about the relations and/or the semantics of the following queries!).

Express the queries below using Relational Algebra.

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

2. Find sids, names and major-dept of students who enrolled in a course that is taught by professor James. (10 pts)

3. Find pid and names of professors who teach no courses in “Fall2015”. (10 pts)

4. Find cid and cname of courses that are offered by “CDS” department that are taught by professors who are from another department in “Fall2015". (20 pts)

5. Find pid and names of professors who teach only courses offered by “CDS” department. (20 pts)

6. Find pnames and pids of professors who teach every course offered by “CDS” dept. (20 pts)

7. Find sids of students who enroll in “Fall2015” every 3 credit hour course offered by “CDS” department. (20 pts)

Answer 1

RELATIONAL ALGEBRA:

1. select c.cid, c.cname from Courses c join Students s on c.dept = s.major_dept join Enrollment e on s.sid = e.sid where s.major_dept = 'CDS' and e.sem_year = 'Fall2015';
2. select s.sid, s.sname from Students s join Courses c on s.major_dept = c.dept join Professors p on c.dept = p.dept where p.pname = 'James';
3. select p.pid, p.pname from Professors p join Courses c on p.dept = c.dept join Enrollment e on c.cid = e.cid where p.pid NOT IN (select * from Enrollment where sem_year = 'Fall2015');
4. select c.cid, c.cname from Courses c join Professors p on c.dept = p.dept join Enrollment e on c.cid = e.cid where c.dept = 'CDS' AND p.pid IN (select * from Enrollment where sem_year = 'Fall2015');
5. select p.pid, p.pname from Professors p join Courses c on p.dept = c.dept where c.dept = 'CDS';
6. select p.pname, p.pid from Professors p join Courses c on p.dept = c.dept group by p.pname, p.pid having count(*) (select count(*) from Courses c where c.dept = 'CDS'); (or) select p.pname, p.pid from Professors p join Courses c on p.dept = c.dept where c.dept = 'CDS';
7. select s.sid from Students s join Courses c on s.major_dept = c.dept join Enrollment e on c.cid = e.cid where c.credithours = '3' AND c.dept = 'CDS';

NOTE: column names shouldnot contain '-' so I replaced them with '_'(underscore) like sem-year to sem_year.

I have written relational algebra and also sql statements

The above queries will excute perfectly.

Please give an upvote.

For any queries please do comment I'll update