Use the following schema to write relational algebra statements for the question

Question

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)

ENROLLMENT (Ssn, Cnum, Semester, Grade)

BOOKSTORE (Cnum, Semester, ISBN)

TEXTBOOK (ISBN, Title, Publisher, Author)

Specify the following queries in relational algebra:

List the course number taken by all students named ‘Jennifer Stack’ in Fall 2018 (i.e., Semester = ‘F18).

Produce a list of textbooks (include Course#, ISBN, Title) for courses offered by the Computer Information Systems Department (i.e., Dept = ‘CIS’).

List any department that has its adopted books published by ‘Wiley Publishing’.

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

Find all the textbooks that were authored by Kevin Fonte.

Explanation / Answer

1)Cnum(STUDENT.Name=‘Jennifer Stack’ ( ENROLLMENT.Semester=‘F18’( ENROLLMENT x STUDENT)

Here in the above query computes the cross product of ENROLLMENT and STUDENT and selects the tuples in which Semester is F18 and from those we will select tuples with STUDENT.Name as Jennifer Stack from those tuples we will project the course numbers

2)Cnum,ISBN,Title (COURSE.Dept=‘CIS'(COURSE x BOOKSTORE x TEXTBOOK)

In the above query computes the cross product of COURSE  and BOOKSTORE and TEXTBOOK and selects the tuples in which Department is CIS and from those we will project Cnum,ISBN and title.

3)Dept(TEXTBOOK.Publisher=‘Wiley Publishing ’(COURSE x BOOKSTORE x TEXTBOOK)

In the above query computes the cross product of COURSE  and BOOKSTORE and TEXTBOOK and selects the tuples in which TEXTBOOK,Publisher is Wiley Publishing and from those we will project Dept

4) name( Ssn(STUDENTS)- Ssn(ENROLLMENT))

Here Ssn(STUDENTS) Selects ssn of all students and Ssn(ENROLLMENT) this selects ssn of all the students who are enrolled in courses Ssn(STUDENTS)- Ssn(ENROLLMENT) gives students who have not been enrolled in any course.therefore name( Ssn(STUDENTS)- Ssn(ENROLLMENT)) gives names of students who have not been enrolled in any course.

5) title(Author like ’%Kevin Fonte%’ (TEXTBOOKS)

This query gives the Title of books whose author name is Kevin fonte

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.