Specify the following queries in relational algebra on the database schema given

Question

Specify the following queries in relational algebra on the database schema given below:

STUDENT(Ssn, Name, Major, Bdate)

COURSE(Course#, Cname, Dept)

ENROLL(Ssn, Course#, Quarter, Grade)

BOOK_ADOPTION(Course#, Quarter, Book_isbn)

TEXT(Book_isbn, Book_title, Publisher, Author)

1. List the number of courses taken by all students named John Smith in Winter 2009 (i.e., Quarter=W09).

2. Produce a list of textbooks (include Course#, Book_isbn, Book_title) for courses offered by the ‘CS’ department that have used more than two books.

3. List any department that has all its adopted books published by ‘Pearson Publishing’.

Explanation / Answer

If you have any doubts, please give me comment...

-- 1. List the number of courses taken by all students named John Smith in Winter 2009 (i.e., Quarter=W09).

SELECT COUNT(*)

FROM STUDENT S, ENROLL E

WHERE S.Ssn = E.Ssn AND S.Name = 'John Smith' AND Quarter = 'W09';

-- 2. Produce a list of textbooks (include Course#, Book_isbn, Book_title) for courses offered by the ‘CS’ department that have used more than two books.

SELECT Course#, Book_isbn, Book_title

FROM TEXT T, BOOK_ADOPTION B, COURSE C

WHERE T.Book_isbn = B.Book_isbn AND B.Course# = C.Course# AND Dept = 'CS'

GROUP BY C.Course#, B.Book_isbn, T.Book_title

HAVING COUNT(*)>2;

-- 3. List any department that has all its adopted books published by ‘Pearson Publishing’.

SELECT DISTINCT Dept

FROM COURSE C, BOOK_ADOPTION B, TEXT T

WHERE T.Book_isbn = B.Book_isbn AND B.Course# = C.Course# AND Publisher = 'Pearson Publishing'

GROUP BY Dept

HAVING COUNT(*) = (

SELECT COUNT(*)

FROM COURSE C1, BOOK_ADOPTION B1

WHERE C1.Course# = B1.Course# AND C.Dept = C1.Dept

);

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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