Reputation: 113
This is problem about answer to "Database System Concepts(6th Edition)" Practice Exercise 3.2-a.
This error is not included in the errata. I think this should be updated to the errata of this book!
Give four relations:
grade_points
grade_points(grade, points)
If grade = 'A+', points = 4,
if grade = 'A-', points = 3.7
just like this ...
student
student(ID, name, dept_name, tot_cred)
ID is the id of the student, name is his name, dept_name is his department name, tot_cred is his the total credits he got.
course
course(course_id, title, dept_name, credits)
takes
takes(ID, course_id, sec_id, semester, year, grade)
The ID is the student ID who takes the course of course_id, the grade is the level he got in this course.
The problem is:
Find the total grade-points earned by the student with ID 12345, across all courses taken by the student.
The solution given by Answers of textbook is this:
(select sum(credits * points)
from (takes natural join course) natural join grade_points
where ID = '12345')
union
(select 0
from student -- This is relation "student"
where takes.ID = '12345' and -- takes.ID should be student.ID ???
not exists (select * from takes where takes.ID = '12345'))
If a student has not taken any course, we would expect to give 0 as the answer.
I think the first "takes.ID" in last where clause should be "student.ID" to indicate that he is a student and then check whether he takes courses. Is the using of "takes.ID" legal and correct? If it is correct, why?
Upvotes: 2
Views: 468
Reputation: 113
This is just and error in the book's practice answers. I will email the authors to update the errata.
Upvotes: 1