CODEWITHSUNDEEP
database-designrelational-databasedatabase-schemarelational-algebra
mukund
mukund

Reputation: 327

How to write the relational algebra from the schema?

Could you please help me understand and solve the following question step by step?

Schemas: STUDENT(Student_Id, SNAME, DEPARTMENT)

COURSE(Course_Id, CNAME, INSTRUCTOR)

Student_Course(Student_Id, Course_Id, GRADE)

(a) Find the names of all students who have taken every course taught by Professor Jacob ?

Answer : 1. Selecting the tuples where instructor="jacob" from table "Course" 2. Projecting the Course_Id 3. Natural Join of Student and Student_Course table. 4. projecting Studen_Id and Course_Id and dividing this by the result
got from step 2

(b) Find the IDs of all students who have never taken any course taught by Professor Jacob ?

Answer : 1. find students who have taken some course taught by jacob by doing natural join of all three tables and project Student_Id. 2. Project Student_Id from Student table. 3. Find the difference between the one in step 2 and the one in step 1

(c) Find the IDs of all courses which have never been taken by any student who has taken at least one course taught by Professor Jacob ?

Answer : 1. Natural Join of all three tables and select tuples where
Instructor="jacob" 2. Project Course_Id from the result. 3. Project Course_Id from "Course" table and find the difference between this and the one from step 2.

(d) Find the IDs of all students who have only taken courses taught by Professor Jacob ?

Answer : 1. select Course_Id from Course table where instructor="jacob" 2. Project Student_id, course_id from student_course table and find the difference from this and the one from step 1. Now we have got student_id of those who have also taken courses that are not taught by "jacob" 3. Project student_id from Student table and find difference between this and the one in step 2.

(e) Find the IDs of all courses satisfying following conditions: (i)they are taught by Professor Jacob and (ii) all students who have taken the course get a grade C or above

Answer : 1. Fetch course_id of the course taught by jacob from "Course" table. 2. Fetch course_id of the course where grade is greater than "C" from
Student_Course table. 3. Intersection of the results from step 1 and step 2.

This is how I have approached the problem. Is there any other efficient way of doing this ? or Is there anything wrong in my procedure?

Upvotes: 0

Views: 647

Answers (1)

reaanb
reaanb

Reputation: 10065

Thanks for showing your work.

(a) You're finding the right students but getting their IDs instead of names.

(b) Correct, but you don't need to include the Student relation in step 1.

(c) No. You're getting all courses except Professor Jacob's active courses. You need to get all courses that don't share students with Professor Jacob.

(d) Correct if you mean antijoin instead of set difference in step 2.

(e) No. You're getting all Professor Jacob's courses where any student has a grade C or above. You need to get those where all students have a grade C or above.

Your approach in general is good, you're not overcomplicating it but watch out for joining redundant relations.

Shouldn't your answers be written in relational algebra notation?

Upvotes: 1

Related Questions