Tuple Relational Calculus I got the first few questions i th

Tuple Relational Calculus

I got the first few questions i think but i need a some help with the last questions plz any help and explanation would be lovely.

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

4.Find the btag, chirp number, chirp date, and text of all Chirps uttered by a Bird whose email address is \'realDonaldTrump@gibson.info\'.

5. Find the tag, email address, gender, and varieties of all of the Peacocks.

6. Find the bird tag, last name, first name, and birth date of any birds who are currently listening to none of the birds.

7. [5 pts] Find the bird tag, last name, first name, and birth date of any birds who are currently listening to all of the birds. (Hint: Use University Quantification!)

variety income Peacock bdate Owns first name Bird bname latitude longitude ast locati Cno senti ment Utter Chirp 1 (Original M (Chirper) N (Copy) Parrots About Bird level Listen name N (Listener M interests Topics bsecto date User Topics Listen Watcher Signup date password emai add State country For Bird, Watcher ISA User: picture street Bird AND Watcher COVER User number mailcode

Solution

4. SELECT BTAG, CNO, DATE, TEXT
FROM CHIRP
WHERE BTAG IS IN (SELECT TAG
       FROM USER
       WHERE EMAIL=\"realDonaldTrump@gibson.info\");

We have to find data from three table: User, Bird and Chirp. Bird has different ralationsip with User and Chirp, both Bird and Chirp contains btag(string) field and hence we can filter our data on btag of Chirp

5. SELECT USER.TAG, USER.EMAIL, BIRD.GENDER, PEACOCK.VARIETY
FROM PEACOCK
INNER JOIN USER ON USER.TAG = PEACOCK.PTAG
INNER JOIN BIRD ON BIRD.BTAG = PEACOCK.PTAG

We have to select data from three different tables, hence we have used inner join to be applied on three tables. Since variety of peacock table is a multi-valued attribute, and we need to include all varities of peacock thats why we have applied first inner join on peacock and user, and second inner join on peacock and bird.

6. SELECT BTAG, LAST_NAME, FIRST_NAME, BIRTHDATE
FROM BIRD
INNER JOIN User ON BIRD.BTAG <> USER.TAG

Inner join is applied on the third table which reduces the querry runtime. If we would have used where btag not in (select tag from user) then the time of execution of query would been much higher. Hence inner joins are more effecient.

7. SELECT BTAG, LAST_NAME, FIRST_NAME, BIRTHDATE
FROM BIRD
INNER JOIN User ON BIRD.BTAG = USER.TAG