Relational Algebra statements for schema STUDENT, COURSE, ENROLL, BOOK_ADOPTION, TEXTBOOK

Use the following schema to write relational algebra statements for the questions that follow. The primary keys are underlined in the schema:

STUDENT(Ssn, Name, Major, DOB)
COURSE(Cnum, Cname, Dept)
ENROLL(Ssn, Cnum, Semester, Grade)
BOOK_ADOPTION(Cnum, Semester, ISBN)
TEXTBOOK(ISBN, Title, Publisher, Author)

 

Specify the following queries in relational algebra:

  1. List the course number taken by all students named 'John Smith' in winter 1999 (i.e., Semester = 'W99').
  2. Produce a list of textbooks (include Course#, ISBN, Title) for courses offered by the Computer Science Department (i.e., Dept = 'CS').
  3. List any department that has its adopted books published by 'BC Publishing'.
  4. Find the names of students who have not been enrolled in any course.
  5. Find all the textbooks that were authored by Mike Wilkins.

a. List the course number taken by all students named 'John Smith' in winter 1999 (i.e., Semester = 'W99').

π Cnum ( σ Name = 'John Smith' ^ Semester = 'W99' ( COURSE ⨝ ENROLL ⨝ STUDENT ) )

 

b. Produce a list of textbooks (include Course#, ISBN, Title) for courses offered by the Computer Science Department (i.e., Dept = 'CS').

π Cnum, ISBN, Title ( σ Dept = 'CS' ( TEXTBOOK ⨝ BOOK_ADOPTION ⨝ COURSE ) )

 

c. List any department that has its adopted books published by 'BC Publishing'.

π Dept ( σ Publisher = 'BC Publishing' ( COURSE ⨝ BOOK_ADOPTION ⨝ TEXTBOOK ) )

 

d. Find the names of students who have not been enrolled in any course.

π Name ( σ Ssn = NULL (STUDENT ⟕ ENROLL) )


OR

π Name ( STUDENT ) - π Name ( STUDENT ⨝ ENROLL )

 

e. Find all the textbooks that were authored by Mike Wilkins.

σ Author = 'Mike Wilkins' ( TEXTBOOK )