Consider the following database schema for a university library. BOOK = {BookID,

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]

Answer the following question using relational algebra expressions. You are encouraged to use relational algebra expressions to represent intermediate results if needed.

1. 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.

2. Find all students who have borrowed at most 1 book. List their StudentIDs and names

Explanation / Answer

1) AUTHOR1 <- SELECT(AUTHOR)

AUTHOR2 <- SELECT(AUTHOR)

TempResult <- SELECTAUTHOR1.AuthorID <> AUTHOR2.AuthorID and AUTHOR1.BookID = AUTHOR2.BookID (AUTHOR1 X AUTHOR2)

ANSWER <- PROJECTAUTHOR1.AuthorID, AUTHOR2.AuthorID, AUTHOR1.BookID(TempResult)

2) TempResult <- StudentIDFcount(StudentID)<=1(LOAN)

Answer <- PROJECTStudentID, NAME(TempResult JOINTempResult.StudentID = STUDENT.StudentID STUDENT)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.