Consider the following database schema for a university library. BOOK = {BookID,
ID: 3588467 • Letter: C
Question
Consider the following database schema for a university library.
BOOK = {BookID, Title} PK: {BookID}
AUTHOR = {BookID, AuthorID, Name} PK: {BookID, AuthorID} FK: [BookID] BOOK[BookID]
STUDENT = {StudentID, Name, Address, Phone} PK: {StudentID}
LOAN = {BookID, StudentID, StartDate, DueDate} PK: {BookID, StudentID} FK: [BookID] BOOK[BookID], [StudentID] STUDENT[StudentID]
1.1 Find all students who have borrowed at least one book. List their StudentIDs and names.
1.2 Find all the students who have borrowed any book(s) written by George Martin. List their StudentIDs and phone numbers.
1.3 Find all the authors whose book(s) has/have never been borrowed by any student. List their AuthorIDs and names.
1.4 Find all the students who have only borrowed the book “the Fellowship of the Ring”. List their StudentIDs.
1.5 Find all pairs of authors who have co-authored at least one book (i.e., both of them are authors of the same book). List all pairs of their AuthorIDs and the title of the book that they co-authored.
1.6 Find all students who have borrowed at most 1 book. List their StudentIDs and names.
Explanation / Answer
1) select s.StudentID,s.Name
from STUDENT s, LOAN l
where s.StudentID=l.StudentID
slection of student id's and names from student table where studentid in student and loan table are equal, gives us student taking atleast 1 book.
2) select s.StudentID,s.Phone
from STUDENT s, LOAN l, BOOK b, AUTHOR a
where s.StudentID=l.StudentID and l.BookID=b.BookID and b.BookID=a.BookID and a.Name="George Martin"
select studentid and phone from student tanle where studentid in student table and loan table , also bookid equals in loan table and book table, also same bookid is equal in book table and author table with author name George Martin. gives student details who borrowed books of GeorgeMartin as author
3) select a.AuthorID,a.Name
from AUTHOR a
where a.BookID NOT IN (select l.BookID
from loan)
selecting authgor id and name from author table where the bookid should not be in the table of loan table, i.e author id and name whoose books are not borrowed
4) select s.StudentID
from STUDENT s, LOAN l
where s.StudentID=l.StudentID
UNION
select l.StudentID
from LOAN l, BOOK b
where l.BookID=b.BookID and b.Title="the Fellowship of the Ring"
select studentid from student table where studentid equals from student table and loan table.
select studentid from loan table where bookid is equal in book and loan table with book title the Fellowship of the Ring.
Union of above 2 tables gives us studentid's borrowing only books with the aboive title name
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.