relational calculus

Question

BOOK(ACC_NO, TITLE, YR_PUB)

USER(CARD_NO, B_NAME, B_ADD)

B_BY(ACC_NO , CARD_NO , DOI)

SUPPLIER(S_NAME,S_ADD)

S_BY(S_NAME,S_NAME,PRICE,,DOS)

Query :- find the names of those supplier who have supplied titles corresponding to all book issused by 'VIJAY'.

using relational calculus solve the query (if possible give proper explation) 

Comments

I think Something is missing in database Maybe database has to be as BOOK(ACC_NO, TITLE, YR_PUB) USER(CARD_NO, B_NAME, B_ADD) B_BY(ACC_NO , CARD_NO , DOI) SUPPLIER(S_NAME,S_ADD) S_BY(ACC,S_NAME,PRICE,PRICE,DOS)

---

sorry ,i edited the question

---

How do i solve the Query :- find the names of those supplier who have supplied some title issused by 'VIJAY'.(may not be the same book by acc_no)

Answer 1

This query is quite simple in terms that its very logical... let's see this step wise

Step 1: B_name =" Vijay" present in User relation having Card_no. as the key

Step 2: Card_no is an attribute of B_by relation having Acc_no as an attribute which will be referencing to the Book relation

Step 3: Book relation contains Title which will give titles for a particular Acc_no....we have to find Acc_no of such other similar Titles too...

Step 4: after we successfully found out the possible Acc_no of "Titles" issued by Vijay we join S_by and Book relations to find and display the S_name for the projected Acc_no.

hence the query becomes :

z = { t | ∀ a ∈ User ( a [B_name] = "VIJAY" ==>                 /* selecting tuples with B_name = "VIJAY"*/

        ∃ b ∈ B_by( a[Card_no] = b[Card_no] ∧            /* natural join of B_by(b) and User(a) for Card_no of "Vijay"*/

        ∃ c ∈ Book( c[Acc_no] = b[Acc_no])  ∧    /* natural join of Book(c) and B_by(b) for Acc_no of books issued to the Card_no of "VIJAY" */

         ∃ d ∈ Book( c[Title] = d[Title]) ∧          /* cartesian product of Book(c) with Book(d) to find Titles same as Titles issued by "VIJAY"*/

         ∃ e ∈ S_by( d[Acc_no] = e[Acc_no] ∧ t[S_name] = e[S_name] )  /* natural join of S_by(e) and relation d to find S_name for the all Titles issued by Vijay*/

         )

      )

  }

   I hope this is the correct solution. Still, any suggestion is Welcome. I face issues with brackets. Any help regarding that.

Comments

Doubt: What happens if the user's name is not Vijay? Since if LHS of implication is false, the result is always true? Will z then contain user tuples where the name is not Vijay, instead of S_by tuples of the supplier name which it should have?

 

In Korth, there is an example of an implication query which has a clause similar to t[S_name] = e[S_name] at the beginning of the query itself, to prevent issues due to LHS of implication becoming false (they explicitly say this). Do we need something similar here?

I saw this question in Prof PP Chakrabarti's NPTEL lectures, where he leaves it to viewer as an exercise. There he has the S_name clause in the beginning itself (and rest he does not write). So I came to GO looking for an answer...