Consider the following schema:
Professor(Id: INTEGER,Name: STRING,DeptId:DEPTS)
Teaching(ProfId:INTEGER,CrsCode:COURSES,Semester:SEMESTERS)
Transcript(StudId:INTEGER,CrsCode:COURSES,Semester:SEMESTERS,Grade:GRADES)
(a) Write a SELECT statement that returns the course code of every course that has ever
been taught by a professor whose DeptId is 'CS'.
(b) Translate the above statement into an equivalent relational algebra expression. Briey
describe the evaluation procedure that your expression specifies.
(c) Translate the following relational algebra expression into an SQL statement.
(a) Write a SELECT statement that returns the course code of every course that has ever
been taught by a professor whose DeptId is 'CS'.
Solution:
SELECT DISTINCT T.CrsCode
FROM Transcript T, Professor P
WHERE P.DeptId = 'CS' AND P.Id = T.ProfId
(b) Translate the above statement into an equivalent relational algebra expression. Briey
describe the evaluation procedure that your expression species.
Solution:
or
others are possible.
(c) Translate the following relational algebra expression into an SQL statement.
Solution:
SELECT R.StudId
FROM Transcript R, Teaching E
WHERE E.ProfId = 9999 AND R.CrsCode = E.CrsCode
AND R.Semester = E.Semester
You might also like to view...
MC Which of the following results in a fatal logic error?
a) attempting to add numbers that have not been converted from string format. b) dividing by zero. c) forgetting a colon in an if structure. d) All of the above.
What operator is assumed when an operator is omitted in a search expression used with the CentOS 7 find command?
A. ! B. -and C. -or D. -not
Stream method ________ eliminates duplicate objects in a stream.
a. distinct b. discrete c. unique d. different
Types define a set of values and a set of operations that can be applied on the values.
Answer the following statement true (T) or false (F)