QuestionQuestion

Consider the following relation schemas. Primary keys are underlined.

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 PreviewSolution 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.

$98.00
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats