Part $2[30$ points$]$

Consider the schema below:

Student$($sID$,$ lastName, firstName, dID, email, address$)$

Department$($dID$,$ Name, campus, chair, budget$)$

Course$($dID$,$ cNumber, Name$)$

Faculty$\_$member$($eID$,$ lastName, firstName, email, address, dID, position, title$)$

Course$\_$Instructor$($eID$,$ lastName, firstName, email, address dID, status$)$

Offering$($oID$,$ dID, cNumber, year, term, eID$)$

Took$($sID$,$ oID, grade$)$

The following inclusion dependencies denote the foreign key constraints on this schema:

Student$[$dID$]\backslash (\backslash$subseteq $\backslash )$ Department$[$dID$]$

Course$\_$Instructor$[$dID$]\backslash (\backslash$subseteq $\backslash )$ Department$[$dID$]$

Faculty$\_$member$[$dID$]\backslash (\backslash$subseteq $\backslash )$ Department$[$dID$]$

Course$[$dID$]\backslash (\backslash$subseteq $\backslash )$ Department$[$dID$]$

Offering$[$eID$]\backslash (\backslash$subseteq $\backslash )$ Course$\_$Instructor$[$eID$])$

Offering$[$dID$,$ cNumber$]\backslash (\backslash$subseteq $\backslash )$ Course$[$dID$,$ cNumber$]$

Took$[$sID$]\backslash (\backslash$subseteq $\backslash )$ Student$[$sID$]$

Took$[$oID$]\backslash (\backslash$subseteq $\backslash )$ Offering$[$oID$]$

dID represents the code of a department and has values such as CSC$,$ MAT, ITEC, etc.

term has values from $\backslash \{$Fall$,$ Winter, Summer$\backslash \}.$

grade has integer values between $0$ and $100.$

status has values from $\backslash (\backslash \{\backslash$mathbf$\{$C I$\},\backslash$mathbf$\{$S L$\},\backslash$mathbf$\{$F M$\}\backslash \}\backslash ),$ corresponding to the following designations: course instructor, sessional lecturer, and faculty member. Some faculty members are course instructors, but not all. Some course instructors are faculty members, but some are not. If a faculty member is a course instructor, they will be listed in the Course$\_$instructor table with the same eID they were assigned in the Faculty$\_$Member table.

Question $5[7$ points$]$

Write a query in Relational Algebra that returns the sID and last name of each student who has taken at least one course with an instructor who is also a faculty member

Question $6[7$ points$]$

Write a query in Relational Algebra that returns the eID and last name of each course instructor who had a heavy teaching load for at least one term. A heavy term teaching load, for an instructor, is defined as teaching at least three different courses in that term.

Question $7[8$ points$]$

Write a query in SQL that returns all the students who were on the Dean's List at least once while they were at taking courses at the University. To be on the Dean's List, a student must have obtained a grade greater or equal to $70$ in all the courses they took that term.

Question $8[8$ points$]$

Write a query in SQL that returns the dID of those departments that never offered a super easy course. A super easy course is a course for which none of the students taking it ever obtained a grade strictly lower than $70.$