-3

5.Consider the following library schema

Books (book_no,title,authors,publisher)
Borrower(borrower_id, name, DOB, job)
Borrowed(borrower_id,book_no,date)

Write the following queries in relational algebra

  • a. Find the name and the borrower_id of borrowers who have borrowed more than 3 books published by “Wiley”
  • b. Find the names of borrowers who have borrowed any book published by “wiley”
  • c. Find the average number of books borrowed by each borrowers,.
raghad
  • 21
  • 1
  • 5
  • 3
    what you have tried so far?? – Rams Apr 14 '17 at 10:11
  • 3
    What's your problem? What did you try so far? – Robert Kock Apr 14 '17 at 10:11
  • a. you need to join the tables and do group by operation and give condition Wiley, b. simply give condition by joining required tables, c. same as 'a' but no need of condition and just take count – Rams Apr 14 '17 at 10:17
  • 2
    And all these need to be done in **relational algebra**, not **SQL**. I think you will benefit the most if you answer these questions yourself. Happy learning! – Giorgos Altanis Apr 14 '17 at 11:25
  • Please give a reference to the "relational algebra" you are supposed to use. There are many. The accepted answer is (still) SQL not any common relational algebra variant. – philipxy Apr 14 '17 at 18:00

1 Answers1

0

as per your question, sql query is like this

For question 1

select B.borrower_id, B.name 
  from Borrower as B, Borrowed as Bw, Books as Bk
 where B.borrower_id = Bw.borrower_id
   and Bw.book_no = Bk.book_no
   and Bk.publisher = 'Wiley'
group by B.borrower_id, B.name 
having count(Bw.borrower_id) > 3

For question 2

select B.name 
  from Borrower as B, Borrowed as Bw, Books as Bk
 where B.borrower_id = Bw.borrower_id
   and Bw.book_no = Bk.book_no
   and Bk.publisher = 'Wiley'
  group by B.name

For question 3

select B.borrower_id, B.name, count(Bw.borrower_id) as cnt 
  from Borrower as B, Borrowed as Bw, Books as Bk
 where B.borrower_id = Bw.borrower_id
   and Bw.book_no = Bk.book_no
group by B.borrower_id, B.name
ITSGuru
  • 194
  • 8