Can someone convert these sql queries into relational algebra format? 1. SELECT no_of_copies FROM ((BOOK NATURAL JOIN BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH) WHERE title='The Lost Tribe' AND BranchName='Sharpstown' ; 2. SELECT Branch_name, no_of_copies FROM ((BOOK NATURAL JOIN BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH) WHERE title='The Lost Tribe'; 3. SELECT B.Name FROM BORROWER B WHERE NOT EXIST (SELECT * FROM BOOK_LOANS L WHERE B.Card_no = L.Card_no);
Can someone convert these sql queries into relational algebra format?
1. SELECT no_of_copies
FROM ((BOOK NATURAL JOIN BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH)
WHERE title='The Lost Tribe' AND BranchName='Sharpstown' ;
2. SELECT Branch_name, no_of_copies
FROM ((BOOK NATURAL JOIN BOOK_COPIES) NATURAL JOIN LIBRARY_BRANCH)
WHERE title='The Lost Tribe';
3. SELECT B.Name
FROM BORROWER B
WHERE NOT EXIST (SELECT * FROM BOOK_LOANS L WHERE B.Card_no = L.Card_no);
4. SELECT BOOK.Title, BORROWER.Name, BORROWER.Address
FROM BOOK, BORROWER, BOOK_LOANS, LIBRARY_BRANCH
WHERE LIBRARY_BRANCH.Branch_name = 'Sharpstown' AND LIBRARY_BRANCH.Branch_id = BOOK_LOANS.Branch_id AND BOOK_LOANS.Due_date='today' AND BOOK_LOANS.Card_no = BORROWER.Card_no AND BOOK_LOANS.Book_id = BOOK.Book_id;
5. SELECT LIBRARY_BRANCH.Branch_name, COUNT(*) AS 'total number of books loaned out'
FROM BOOK_LOANS, LIBRARY_BRANCH
WHERE BOOK_LOANS.Branch_id=LIBRARY_BRANCH.Branch_id
GROUP BY LIBRARY_BRANCH.Branch_name;
6. SELECT Name, Address, count(Book_Id)
FROM BORROWER b join BOOK_LOANS l
ON b.card_no = l.card_no
JOIN BOOK_COPIES c
ON c.book_id = l.book_id
GROUP BY Name, Address
HAVING count(Book_Id) > 5;
7.SELECT Title, count(Book_Id)
FROM BOOK b join book_copies c on b.book_id = c.book_id
join book_authors a on a.book_id = b.book_id join book_loans l on l.book_id=a.book_id
join library_branch br on br.branch_id = l.branch_id
where author_name = 'Stephen King'
and branch_name = 'Central';
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
