Assignment 6 Major stores

Assignment 6

Consider the following relation schemas about students and books.
Student(Sid, Sname)
Major(Sid, M ajor)
Book(BookNo, T itle, P rice)
Cites(BookNo, CitedBookNo)
Buys(Sid, BookNo)
The relation Major stores students and their majors. A student can have
multiple majors, but we also allow that a student can have no major. A tuple
(b, c) in the relation Cites indicates that the book with book number b cites the
book with book number c. Note that a book may cite multiple other books.
Also, a book does not have to cited.
Translate the following SQL queries into equivalent RA expressions. Subsequently, optimize these RA expressions as much as possible. Show your work.
1. SELECT b.bookno, c1.bookno
FROM book b, cites c1, cites c2
WHERE b.bookno = c1.citedbookno AND
c1.bookno = c2.citedbookno
b.bookno = 10 AND c2.citedbookno < 20;
2. SELECT b.bookno FROM book b
WHERE b.Title = ’AI’ AND EXISTS(SELECT b1.bookno
FROM book b1
WHERE b1.Price < b.Price);
3. SELECT b.bookno
FROM book b
WHERE b.bookno NOT IN (SELECT T.bookno
FROM buys T);
4. SELECT b.bookno FROM book b
WHERE NOT EXISTS(SELECT b1.bookno
FROM book b1
WHERE b1.Price < b.Price);
5. SELECT b.bookno FROM book b
WHERE b.Title = ’AI’ AND b.bookno NOT IN (SELECT c.citedbookno
FROM cites c, book b1
WHERE c.bookno = b1.bookno AND
b1.Title = ’OS’);
1
6. SELECT s.sid
FROM student s, major m
WHERE s.sid = m.sid AND m.major = ’CS’
NOT EXISTS( (SELECT T.bookno
FROM buys T
WHERE s.sid = T.sid AND s.Sname < ’John’)
INTERSECT
(SELECT b.bookno
FROM book b
WHERE b.Price < 30));
7. SELECT s1.sid
FROM student s1, student s2
WHERE s1.sid < s2.sid AND
EXISTS(SELECT T1.bookno
FROM buys T1
WHERE s1.Sname = ’John’ AND s1.sid = T1.sid AND
T1.bookno IN (SELECT T2.bookno
FROM buys T2
WHERE s2.sid = T2.sid));
2