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),

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 (yeari.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. (10 pts)

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

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". (20 pts)

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

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

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

7.Find cids and names of courses in which every student majoring in “CDS” enrolled in “Fall2015”. (Bonus question: 10 pts)

Answer 1

Relational Algebra is the formal description of how a relational database operates.It is a procedural query language. The fundamental operations of relational algebra is Select(), Project(), Union(), Set different(-), Cartesian product(X), Rename().

Tables are :

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

Question and Answers

1. Temp(students _{dept= major-dept} professors)

_{sid, sname,major-dept} (_{pname = "James"}(Temp))

2.   _pid,pname (_{cid="NULL" AND sem-year="Fall2015"(professors X Teaches)})

3. _{cid,cname(dept"CDS" AND sem-year="Fall2015")(courses X Teaches)}

4. _pid,pname(_dept="CDS"(professors))

5. _pid,pname(_dept="CDS"(professors) AND _{credithours="MAX"}(Courses)

6. _sid (dept="CDS" AND Credithour="3"(Courses _cid=cid Enrollment ) )

7. R(Courses _{cid= cid} Enrollment)

_cid,cname (_{dept= " CDS"ANDsem-year = "Fall2015"}(R))