Question

Consider the relational schema below: Students(sid: integer, sname: string, major: string) Courses(cid: integer, cname: string, hours: integer) Enrollment(sid: integer, cid: integer, grade: real) Write a relational algebra expression that satisfies this query? Find the distinct names of all students that take at least three courses and major in "Philosophy".

Answer 1

The relational algebra expression that satisfies the given query is a combination of selection, join, and projection operators to find the distinct names of students majoring in 'Philosophy' and taking at least three courses.

Step-by-step explanation:

The relational algebra expression that satisfies the given query is:

Πsname(σmajor='Philosophy'(Students) ⨝ Enrollment.sid = Students.sid ⨝ Πsid, COUNT(cid)(σhours >= 3(Courses) ⨝ Enrollment.cid = Courses.cid )))

This expression performs the following steps:

Selects students majoring in 'Philosophy' from the Students table using the σ (selection) operator.

Joins the Enrollment table with the result of step 1 using the ⨝ (join) operator on the sid attribute.

Selects courses with at least three hours from the Courses table using the σ (selection) operator.

Joins the result of step 3 with the result of step 2 using the ⨝ (join) operator on the cid attribute.

Projects only the sname attribute from the result of step 4 using the Π (projection) operator.

Finds the distinct names of students using the Π (projection) operator and counts the number of courses each student is enrolled in using the COUNT aggregation function.

The final result is the distinct names of students who major in 'Philosophy' and take at least three courses.

Answer 2

Here, we need to perform JOIN operation of the students and registration.

Use NATURAL JOIN operator
`\Join` and so no need to specify Join condition.

Also we need to selection operator tp select only those course whose major in "philosophy".

Finally we projection operator π to project only distinct students name who took at least three courses.

`$\pi [\sigma_{\text{student's nam}e} [ \sigma(\text{three courses}) \Join \text{`