Share
Explore BrainMass

Relational Algebra and Tuple Relational Calculus

Consider the following relation schemas (in attached image). Primary keys are underlined.

Use (1) Relational Algebra and (2) Tuple Relational Calculus to express the following queries:
1. Find the instruments (InstID) played by musician named 'John'.
2. Find the titles of the albums produced by musicians who play guitar or piano (iname='guitar' or 'piano').
3. Find the musicians who played both song1 (songID='song1') and song2 (songID='song2').
4. Find the musician(s) with the highest annual income.
5. Find all the pairs of musicians (give names) who share the same addresses.
6. Find the songs that are not performed by any musician.
7. Find the musicians (names) who played all the songs written by 'John' (musician author's name = 'John').

Attachments

Solution Preview

1. Find the instruments (InstID) played by musician named 'John'.

Since join attribute 'ssn' is named same in both the relations - Musicians and Plays, we can apply natural join here.

Result <= MinstrID (?name='John' (Musicians * Plays))

Another way to do this would be as follows:

Result ? MinstrID ((?name='John' Musicians) * Plays)

Second query will significantly reduce the number of Musicians tuples that participate in natural join.

2. Find the titles of the albums produced by musicians who play guitar or piano (iname='guitar' or 'piano').

Result <= Ltitle (((omega*iname='guitar' OR iname='piano' Instruments) * Plays) * AlbumProducer)

Here, innermost natural join "... Instruments) * Plays" on join attribute 'instrID' results in tuples that give the 'ssn' of Musicians that play either guitar or piano. This result is further 'natural join'-ed with AlbumProducer on join attribute 'ssn'.

Another way to achieve same result would be as follows:

Result <= Mtitle ((Mssn ((omega*iname='guitar' OR iname='piano' Instruments) * Plays)) * AlbumProducer)

3. Find the musicians who played both song1 (songID='song1') and song2
(songID='song2').

Result <= ((Mssn (omega*songID='song1' Perform)) and (Mssn (omega*songID='song2' ...

Solution Summary

Kindly refer to the solution attachment 503152.doc for answer with proper symbols/notations. Little bit of supportive explanation is also provided with many queries.

$2.19