A library uses a relational database to store data about library books, their members and lending (books, which are currently borrowed). The database relations of this system are given below. Lengths of attributes are given within brackets.
Book(bookno(13), title(20), author(30), edition(7)) Member(memno(4), name(26), category(5), address(40), phone(15)) Lending(memno(4), bookno(13), duedate(8))
The library database currently has 10,000 book tuples and 1,000 member tuples. Assume that 400 book borrowings are on record and 10 copies of the bookno "81-203-1257-0" is on lend.
The following SQL query has been written to retrieve borrower's name with due date given the book no "81-203-1257-0".
SELECT m.*, l.* FROM Member m, Lending l WHERE m.memno = l.memno and bookno = "81-203-1257-0";
The following are relational algebra expressions to retrieve the information for the above query.
JOIN(Member and Lending Over memno) where bookno="81-203-1257-0")
JOIN(Member and (RESTRICT Lending where bookno="81-203-1257-0") over memno
CARTESIAN(Member and Lending) where bookno="81-203-1257-0")
RESTRICT(CARTESIAN (Member and (RESTRICT Lending where bookno="81-203-1257-0")) where Member.memno=Lending.memno
PROJECT m.*, l.* (RESTRICT Member m and Lending l where m.memno = l.memno and bookno = "81-203-1257-0")
which one is the least optimised and how to compare with it others?
I am newly comer to db and query optimization setup , I hope some expert, explanation answer with query trees?