## Question

Relation Schemas

Meaning and additional information

Musicians(ssn, name, annualIncome)

Registers the ssn of musicians and their names and annual incomes.

Instruments(instrID, iname, key)

Registers instruments.

Plays(ssn, instrID)

Registers which musicians play which instruments

SongsAppears(songID, authorSSN, title, albumIdentifier)

Registers songs and which albums they appear. Every song must have exactly one author who is a musician, and appears in exactly one album.

Lives(ssn, address, phone)

Registers where musicians live and which phones they use.

Place(address, otherInfo)

Register all addresses.

Perform(songID, ssn)

Register which songs are performed by which musicians

AlbumProducer(ssn, albumIdentifier, copyrightDate, speed, title)

Registers albums and their producers (who are musicians).

Problem 1: Use (1) relational algebra, (2) calculus and (3) SQL to express the following queries.

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

2. Find the instruments (InstID) played by every musicians whose name is ‘John’

3. Find the instruments (InstID) played only by musicians whose name is ‘John’, i.e., are not played by any musician with a different name.

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

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

6. Find the musician(s) with the highest annual income.

7. Find the musicians who earn more than every other musician.

Problem 2: Use SQL to express the following queries:

8. Find all the pairs of musicians (give names) who share an address.

9. Find the songs that are not performed by any musician.

10. Find the musicians (names) who played all the songs written by 'John' (musician author name = 'John').

11. Find the musicians who perform a song they have authored

12. Find the musicians who perform all songs they have authored

13. Find the musicians who have authored all songs they perform

14. Find the musicians who perform only songs they have authored, i.e., do not perform any songs they have not authored.

## Solution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

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

result = π INSTRID( σ m.NAME = 'John' (MUSICIANS ⋈ p.ssn = m.SSN PLAYS))

b. Calculus

{ p.INSTRID | MUSICIANS( m ) AND PLAYS (P) AND m.NAME = 'John' AND p.ssn = m.SSN }

c. SQL

select p.INSTRID from MUSICIANS m inner join PLAYS p on p.ssn = m.SSN where m.NAME = 'John' ;

2. Find the instruments (InstID) played by every musicians whose name is ‘John’

a. relational algebra

s1 = (π p.INSTRIDF, count( distinct m.SSN) ( σ m.NAME = 'John' (MUSICIANS ⋈ p.ssn = m.SSN PLAYS)))

s2 = π count( distinct m.SSN) ( σ m.NAME = 'John' (MUSICIANS))

result = π s1.INSTRID ( σ s1.total = s2 (s1))

b. Calculus

{ s1.INSTRID |

{ p.INSTRID, count( distinct m.SSN) as total | MUSICIANS (m)

AND PLAYS (p) AND p.ssn = m.SSN AND m.NAME = 'John' } (s1) AND s1.total =

{ count(m.SSN) | MUSICIANS(m) AND m.NAME = 'John'}

}...

By purchasing this solution you'll be able to access the following files:

Solution.docx.