0

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?

mukund
  • 317
  • 5
  • 10
  • 22
  • You are giving your answers (in natural language but you could have just given it in relational algebra), but you are not really explaining "how you have approached the problem" or "your procedure". (Maybe by those terms you really just mean "your expressions of nested algebra operators". All we can tell you is (guessing at the meaning of your tables) whether the answers are correct. *Why* do you think these are the answers? If you search for and give your justification then we can help you "understand" and "solve". – philipxy Sep 28 '15 at 06:53
  • You might find [this answer](http://stackoverflow.com/a/24425914/3404097) (for a different database) helpful. – philipxy Sep 28 '15 at 06:57

1 Answers1

1

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?

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Thanks. So In (a), in step 4, instead of projecting student_id and course_id, I need to project student_name and course_id. right? And in (b), in step 1, I need to do natural join on only two tables, that is, Course and Student_course. – mukund Sep 27 '15 at 23:09
  • In (d), I am taking the difference to get student_id of those who have taken courses that are not taught by 'jacob'. so what is wrong in it? – mukund Sep 27 '15 at 23:18
  • In (e), I have come up with this solution. Step 1 - Natural join of Course and Student_Course and select tuples where instructor='jacob' and project student_id. Step 2 - Project student_id from course table and find the difference between this and the one in step 1. Now we have got students who are not in professor class. Step 3 - natural join of the one from step 2 and course table and project course_id. I guess this would retrieve the required course_ids. Please correct me if I am wrong. – mukund Sep 28 '15 at 00:20
  • In (a) in step 3, remember to join all 3 tables. – reaanb Sep 28 '15 at 02:59
  • In (d), the results of step 1 and 2 aren't union-compatible, they don't have the same attributes. – reaanb Sep 28 '15 at 03:08
  • Your new solution for (e) looks like a solution for (c) instead. However, in step 2 and 3, there is no student_id in the Course relation so I assume you meant Student_Course. With that you're good. – reaanb Sep 28 '15 at 03:25
  • Yes. it is (c) and it is the student_course table only. And yes I am still working on (e) – mukund Sep 28 '15 at 05:23
  • Can you please give me an hint for (e)? – mukund Sep 30 '15 at 02:55
  • Start with all of Jacob's courses and subtract the courses you don't want. Hope that helps. – reaanb Sep 30 '15 at 09:43
  • Yeah. I tried that. What I am finding it difficult is how to find if all the students who have taken the course have scored C or above? – mukund Sep 30 '15 at 21:07
  • If you subtract the courses of any learners who scored worse, the remainder is what we want. – reaanb Sep 30 '15 at 23:05
  • Thanks. And i have a doubt. Why In (a), in step 3, I need to join 3 tables. Will it not be enough if I join Student and Student_Course ? Because in step 2 I have got courseId of courses offered by jacob. – mukund Oct 02 '15 at 01:03
  • I think you're right, it doesn't make sense to me either now. – reaanb Oct 02 '15 at 05:30
  • @mukund & reaanb There are many relational divisions. Codd's original gives students who took all courses J taught *and took at least one course*. So if J taught no courses then *all* students *including* students who took no courses took all courses J taught. But the latter students are not in the result. You need (names of) students where for all courses if J taught it then they took it, ie where not for some course J taught it and they didn't take it. For Student_Ids: S MINUS (and not) of PROJECT (for some) of [RESTRICT C (J's courses) MINUS (and not) S_C]. (Plus appropriate PROJECTs.) – philipxy Oct 02 '15 at 07:07
  • @philipxy I agree with your comment but don't follow your algebra. My own answer used antijoins to achieve the same idea: PROJECT Sname (Student ANTIJOIN ((Student ✕ RESTRICT Instructor=Jacob (Course)) ANTIJOIN (RESTRICT Instructor=Jacob (Student_Course NATURAL JOIN Course)))). – reaanb Oct 02 '15 at 12:43
  • @reaanb R ANTIJOIN S is R MINUS [R JOIN S followed by PROJECT on R's attributes]. Hence my use of MINUS, JOIN and "appropriate PROJECTs". Note how my natural language expression corresponds to my relation expression via correspondence of base relation predicates to base relation names and logic operators to relation operators. (R & predicate of R; MINUS & and not; JOIN/✕ & and; r PROJECT out A & for some A [predicate of r].) PS You only need to join in student names once at the end – philipxy Oct 02 '15 at 13:07
  • If I want to write SQL command for 1st question, how to do that ? because I am struck with implementing division in sql. How to find students taking "EVERY" course by jacob? – mukund Oct 08 '15 at 02:53
  • I found the following article helpful: https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ – reaanb Oct 08 '15 at 05:18
  • @mukund *Division is not appropriate here.* See my comments! – philipxy Nov 13 '16 at 00:57
  • Except for 'Now we have got student_id of those who have also taken courses that are not taught by "jacob"' (which as you point out is wrong) @mukund *didn't* show their work. They described their answer in natural language. See my comment on the question. – philipxy Nov 13 '16 at 01:06
  • @mukund Division is not appropriate here. See my comments! When we want tuples where FOR SOME *c,...* [*statement template in terms of c,... and other columns*] we calculate PROJECT *all columns but c,...* [*relation that is the tuples that make the statement template into a true statement*]. Also, FOR ALL ... [...] means NOT FOR SOME ... [NOT ...]. Also *statement template 1* AND NOT *statement template 1* gets calculated by *tuples satisfying statement template 1* MINUS *tuples satisfying statement template 1*. Find a statement template for the given and, via FOR SOME, the answer relations, – philipxy Nov 13 '16 at 01:20