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:

  1. The attribute to find: title of the book from "Book" schema.
  2. Given Condition: Publisher's name = Addison_wisely. From "Publisher" schema.
  3. 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:

  1. 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.
  2. Get all the tuples where the book's title is NOT "Introduction to Programming". Store it in another temporary relation Temp2.
  3. 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:

  1. The attribute to find: publish_date is in Publish.
  2. Given Condition: title of the book is "Software Engineering".
  3. 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:

  1. 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.
  2. From Temp1, list all the attributes related to the Book schema. Store them in Temp2.
  3. 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.