Consider the following relations: Student(snum: integer, sname: string, major: string, level: string, age: integer) Class(cname: string, meets_at: time, room: string, fid: integer) Enrolled(snum: integer, cname: string) Faculty(fid: integer, fname: string, deptid: integer) The meaning of these relations is straightforward: for example, Enrolled has one record per student-class pair to describe the fact that the student is enrolled in the class. Write the following queries in both relational algebra and SQL. Try your best. a). Find the names of all Juniors (level = JR) who are enrolled in a class taught by I Teach.
Expert Answer
SQL Query:
SELECT distinct studentTable.sname FROM student studentTable,class classTable,faculty facultyTable,enrolled enrolledTable
WHERE (studentTable.snum = enrolledTable.snum) AND (enrolledTable.cname = classTable.cname) AND(studentTable.level=’jr’) AND (facultyTable.fname=’I’) AND (facultyTable.fid = classTable.fid);
Explaination:
We first find all the students which are enrolled in a class, And we find students with level ‘jr’, we find the faculty names of all classes and then check the faculty name = “I”.
Relational Algebra Expression: