Consider the following schema of the relational database.
- Publisher(pid, name, location)
- Book(bid, title, author, page, price)
- Publish(bid, pid, publish_date)
Write Relational Algebra expressions for the following:
- Find title of book published by Addison_wisely.
- Update the author name to "MNP" of book "Introduction to Programming".
- Find the publish date of book "Software Engineering".
- Delete all books published by "Mc_graw_hill".
- Insert new record of book named "Database" published by "Pearson" in "2010-12-11" and written by "Patterson".
A: Relational Algebra for Relations Publisher, Book and Publish
i. Find title of book published by Addison_wisely.
π Book.title ( σ Publisher.name = "Addison_wisely" ( Book ⨝ Publish ⨝ Publisher))
Explanation:
- The attribute to find: title of the book from "Book" schema.
- Given Condition: Publisher's name = Addison_wisely. From "Publisher" schema.
- Thus, ⨝ is needed. But Book and Publisher do not have a common attribute to perform Natural Join(⨝). Hence, "Publish" is used in the join to connect Book and Publisher through attributes "bid" and "pid".
ii. Update the author name to "MNP" of book "Introduction to Programming".
Temp1 ← π bid, title, author = "MNP", page, price ( σ title = "Introduction to Programming" ( Book ) )
Temp2 ← σ title ≠ "Introduction to Programming" ( Book )
Book ← Temp1 ∪ Temp2
Explanation:
- Get the book whose title is "Introduction to Programming" and change its author name to "MNP" using Generalized Projection. Store the tuple in a temporary relation Temp1.
- Get all the tuples where the book's title is NOT "Introduction to Programming". Store it in another temporary relation Temp2.
- Assign to Book the union of Temp1 and Temp2.
OR
Book ← π bid, title, author = "MNP", page, price ( σ title = "Introduction to Programming" ( Book ) ) ∪ σ title ≠ "Introduction to Programming" ( Book )
Explanation:
Just like the previous one. All three steps are combined into a single expression.
iii. Find the publish date of book "Software Engineering".
π Publish.publish_date ( σ Book.title = "Software Engineering" ( Book ⨝ Publish ) )
Explanation:
- The attribute to find: publish_date is in Publish.
- Given Condition: title of the book is "Software Engineering".
- Hence ⨝ is needed.
iv. Delete all books published by "Mc_graw_hill".
Temp1 ← σ Publisher.name = "Mc_graw_hill" ( Book ⨝ Publish ⨝ Publisher )
Temp2 ← π Book.bid, Book.title, Book.author, Book.page, Book.price ( Temp1 )
Book ← Book - Temp2
Explanation:
- To find all the books published by "Mc_graw_hill", we need to perform ⨝ of Book, Publish and Publisher. Just like the first question. Store the tuples in Temp1.
- From Temp1, list all the attributes related to the Book schema. Store them in Temp2.
- Delete the tuples from Temp2 using the Difference operation.
OR
Book ← Book - π Book.bid, Book.title, Book.author, Book.page, Book.price ( σ Publisher.name = "Mc_graw_hill" ( Book ⨝ Publish ⨝ Publisher ) )
Explanation:
Just combined those steps into a single expression.
v. Insert new record of book named "Database" published by "Pearson" in "2010-12-11" and written by "Patterson".
Book ← Book ∪ { 'B104', 'Database', 'Patterson', 400, 500 }
Publisher ← Publisher ∪ { 'P10', 'Pearson', 'California' }
Publish ← Publish ∪ { 'B104', 'P10', '2010-12-11' }
Explanation:
Here, we are assuming that the records of publisher 'Pearson' and the author 'Patterson' does not already exist. And thus we are inserting them too into their respective relations.