Question

For all problems, use the following schema:

Musician(id, first_name, last_name, instrument, band_id)
Band(id, name, years_together)
Show(id, venue_id, date)
Played_in(band_id, show_id)
Venue(id, name, address)
Album(id, name, year, band_id, genre_id)
Genre(id, name, description)
Song(id, name, album_id)

Primary keys are in bold, foreign keys are in italics.

For each problem, write a query once using relational algebra, and again using SQL.

12. Find the names of all bands that have a member who plays "Guitar", OR have a member that plays "Keyboard".

13. Find the id of all shows that includes a band who has been together more than 5 years.

14. Find the instruments of all members of bands who have played at "The Bluebird".

15. Find the names of all genres for albums put out by bands who played at "The Gothic".

Answer 1

Following data is inserted into the schema :

insert into Genre values(1, ‘Jazz’, ‘Jazz music’);

insert into Genre values(2, 'Hip hop', 'Hip hop music');

insert into Genre values(3, 'Clasical', 'Clasical music');

insert into Band values(10, 'The Beatles', 10);

insert into Band values(20, 'The Rolling Stones', 7);

insert into Band values(30, 'The Beach Boys', 2);

insert into Venue values(100, 'The BlueBird', 'Taxes');

insert into Venue values(200, 'The Gothic', 'New York');

insert into Venue values(300, 'London', 'London');

insert into Show values(123, 100, '31-DEC-2018');

insert into Show values(124, 200, '01-JAN-2019');

insert into Show values(125, 100, '01-JAN-2019');

insert into Album values(1000, 'Beach','2010', 10,1);

insert into Album values(1001, 'Beatles','2012', 20,2);

insert into Album values(1002, 'Hop songs','2010', 20,2);

insert into Song values(2000, 'Gully boy', 1000);

insert into Song values(2002, 'Sunflower', 1001);

insert into Song values(2003, 'In Da club', 1001);

insert into Played_in values(10,123);

insert into Played_in values(10,124);

insert into Played_in values(20,124);

insert into musician values(1010, 'Elvis', 'Presley', 'Guitar',10);

insert into musician values(1011, 'Bob', 'Dylan', 'Guitar',20);

insert into musician values(1012, 'David', 'Bowie', 'Keyboard',10);

insert into musician values(1013, 'Miley Cyrus', 'Presley', 'KeyBoard',20);

insert into musician values(1014, 'John', 'Waters', 'Drums',10);

insert into musician values(1015, 'Mick', 'Jagger', 'Drums',20);

-----------------------------------

Solution 12:

SQL Query : SELECT NAME AS BAND_NAME FROM BAND INNER JOIN MUSICIAN ON MUSICIAN.band_id = Band.id WHERE MUSICIAN.INSTRUMENT = 'Guitar' OR MUSICIAN.INSTRUMENT = 'KEYBOARD';

Relational Algebra:

ρ BAND_NAME←name π name σ MUSICIAN.INSTRUMENT = 'Guitar' or MUSICIAN.INSTRUMENT = 'KEYBOARD' BAND ⨝ MUSICIAN.band_id = Band.id MUSICIAN

OUTPUT : ( Based on above data )

BAND_NAME

The Beatles

The Rolling Stones

SOLUTION 13:

SQL Query : SELECT DISTINCT SHOW_ID FROM PLAYED_IN JOIN BAND ON PLAYED_IN.BAND_ID = BAND.ID WHERE BAND.YEARS_TOGETHER > 5;

Relational Algebra:

π SHOW_ID σ BAND.YEARS_TOGETHER > 5 PLAYED_IN ⨝ PLAYED_IN.BAND_ID = BAND.ID BAND

OUTPUT : ( Based on above data )

show_id

123

124

Solution 14:

SQL Query : SELECT DISTINCT INSTRUMENT AS INTRUMENTS_OF_MEMBBERS_PLAYED_AT_BLUEBIRD FROM MUSICIAN WHERE BAND_ID IN (

SELECT BAND_ID FROM PLAYED_IN WHERE SHOW_ID = (

SELECT SHOW.ID FROM SHOW INNER JOIN VENUE ON SHOW.VENUE_ID = VENUE.ID WHERE VENUE.NAME = 'The BlueBird'));

Relational Algebra:

ρ INTRUMENTS_OF_MEMBBERS_PLAYED_AT_BLUEBIRD← INSTRUMENT π INSTRUMENT σ BAND_ID = (π BAND_ID σ PLAYED_IN. SHOW_ID = ( π SHOW.ID σ ENUE.NAME = 'The BlueBird' SHOW ⨝ SHOW.VENUE_ID = VENUE.ID VENUE) PLAYED_IN) MUSICIAN

OUTPUT : ( Based on above data )

INTRUMENTS_OF_MEMBBERS_PLAYED_AT_BLUEBIRD

Guitar

Keyboard

Drums

Solution 15:

SQL Query : SELECT DISTINCT GENRE.NAME AS GENRE_NAME FROM GENRE INNER JOIN ALBUM ON ALBUM.GENRE_ID = GENRE.ID WHERE ALBUM.BAND_ID IN (

SELECT BAND_ID FROM PLAYED_IN WHERE SHOW_ID = (

SELECT SHOW.ID FROM SHOW INNER JOIN VENUE ON SHOW.VENUE_ID = VENUE.ID WHERE VENUE.NAME = 'The Gothic'));

OUTPUT : ( Based on above data )

GENRE_NAME

Jazz

Hip hop