Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Formulate relational algebra queries to find the following information: ( 1 ) Musician ( s ) ssn ( s ) and names ( s )

Formulate relational algebra queries to find the following information:
(
1
)
Musician
(
s
)
ssn
(
s
)
and names
(
s
)
who play
Saxophone
.
(
2
)
Musician
(
s
)
ssn
(
s
)
and names
(
s
)
who share the same address. Assume each musician has only one address.
(
3
)
Album
(
s
)
identifiers and Album
(
s
)
title
(
s
)
produced by the musician
(
s
)
named
Ben
.
(
4
)
Musician
(
s
)
ssn
(
s
)
and names
(
s
)
who play all instruments that are played by
Ben
.
(
5
)
Titles of albums produced by musicians who play both
guitar
and
piano
.
(
6
)
Musicians
ssn
(
s
)
and name
(
s
)
performed song with songID
5
4
4
or produced an album that contains that song.
(
7
)
Album
(
s
)
identifiers and Album
(
s
)
title
(
s
)
with the highest speed.
(
8
)
Song ID
(
s
)
and title
(
s
)
that are not performed by any musician.
(
9
)
Song ID
(
s
)
and title
(
s
)
performed by musician
(
s
)
who have produced at least two albums.
(
1
0
)
Musician
(
s
)
ssn
(
s
)
and name
(
s
)
with annual income more than $
5
0
,
0
0
0
and have not produced any album.
Note : Can't join more than two tables at once
,
you can rename two joined tables and join it with third table
use the schema below for reference
Relation Schemas
Musicians
(
ssn: string, name: string, annualIncome: real
)
Primary Key: ssn
Candidate Key: None
Foreign Key: None
Instruments
(
instrID: string, iname: string, musickey: string
)
Primary Key: instrID
Candidate Key: iname
Foreign Key: None
Plays
(
ssn: string, instrID: string
)
Primary Key:
(
ssn
,
instrID
)
Candidate Key: None
Foreign Key: ssn
,
instrID
SongsAppears
(
songID: string, authorSSN: string, title: string, albumIdentifier: string
)
Primary Key: songID
Candidate Key:authorSSN
Foreign Key: albumidentifier
Lives
(
ssn: string, aid: string, phone: string
)
Primary Key:
(
ssn
,
aid
)
Candidate Key: None
Foreign Key: ssn
,
aid
Place
(
aid: string, address: string, otherInfo: string
)
Primary Key: aid
Candidate Key: address
Foreign Key:None
Perform
(
songID: string, ssn: string
)
Primary Key:
(
songID
,
ssn
)
Candidate Key: None
Foreign Key: ssn
,
songID
AlbumProducer
(
albumIdentifier: string, ssn: string, copyrightDate: date, speed: real, title: string
)
Primary Key: albumIdentifier
Canidate Key: title
Foreign Key: ssn

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Professional SQL Server 2012 Internals And Troubleshooting

Authors: Christian Bolton, Justin Langford

1st Edition

1118177657, 9781118177655

More Books

Students also viewed these Databases questions

Question

3r 1 = 2r + 24

Answered: 1 week ago

Question

Provide examples of KPIs in Human Capital Management.

Answered: 1 week ago

Question

What are OLAP Cubes?

Answered: 1 week ago