DBMS LaB1 [MY SQL]

Exercise: 1

 Notown Records has decided to store information about musicians who perform on its albums. Each musician that records at Notown has anSSN, a name, an address, and a phone number.  No  musician  has  more  than  one  phone.  Each  instrument  used  in  songs  recorded  at Notown  has  a   unique  identification  number,  a  name  (e.g.,  guitar,  synthesizer,  flute) and a musical  key  (e.g.,  C,  B-flat,  E-flat).  Each  album  recorded  on  the  Notown  label  has a unique identification  number,  a  title,  a  copyright  date  and  a  format  (e.g.,  CD  or  MC).  Each  song recorded at Notown has a title and an author. Each  musician may play several instruments,nd a given instrument may be played by several musicians. Each album has a number of songs on it, but no song may appear on more than one album.  Each song is performed by one or more musicians,  and  a  musician  may  perform  a  number  of  songs.  Each  album  has  exactly one musician who acts as its producer. A musician may produce several albums.

ER Diagram:

 

Relational schema:

Tables:

mysql> create table musician(ssn integer primary key, name varchar(30),addr  varchar(30), phone integer);

Query OK, 0 rows affected (0.58 sec)

mysql> insert into musician values(123,"rahaman","bangalore",232456);

Query OK, 1 row affected, 1 warning (0.11 sec)

mysql> insert into musician values(124,"shreya","mumbai",232477);

Query OK, 1 row affected, 1 warning (0.13 sec)

mysql> select * from musician;

5 rows in set (0.00 sec)

mysql> create table instrument(id integer primary key, iname varchar(30),mkey  varchar(10));

Query OK, 0 rows affected (0.45 sec)

mysql> insert into instrument values(1,"guitar","high");

Query OK, 1 row affected (0.09 sec)

mysql> insert into instrument values(2,"flute","low");

Query OK, 1 row affected (0.09 sec)

mysql> select * from instrument;

6 rows in set (0.02 sec)

mysql> create table plays(ssn integer references musician(ssn), id integer references instrument(id), primary key(ssn,id));

Query OK, 0 rows affected (0.56 sec)

mysql> insert into plays values(123,1),(124,2),(125,3),(126,4),(128,5),(123,6);

Query OK, 6 rows affected (0.08 sec)

Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from plays;

6 rows in set (0.00 sec)

mysql> create table songs(title varchar(10) primary key, author varchar(20), aid integer references album(aid));

Query OK, 0 rows affected (0.53 sec)

mysql> insert into songs values("jaadu","kiran","102");

Query OK, 1 row affected (0.11 sec)

mysql> insert into songs values("sri sai","krishna","101");

Query OK, 1 row affected (0.03 sec)

mysql> select * from songs;

5 rows in set (0.00 sec)

mysql> create table performs(title varchar(10) references songs(title), ssn integer references musician(ssn), primary key(title,ssn));

Query OK, 0 rows affected (0.39 sec)

mysql> insert into performs values("jaadu",123);

Query OK, 1 row affected (0.09 sec)

mysql> insert into performs values("jaadu",124);

Query OK, 1 row affected (0.08 sec)

mysql> select * from performs;

5 rows in set (0.00 sec)

mysql> create table album(aid integer primary key, atitle varchar(30), format varchar(5),rdate date,ssn integer references musician(ssn));

Query OK, 0 rows affected (0.59 sec)

mysql> insert into album values(101,"abc","cd",20120525,123);

Query OK, 1 row affected (0.09 sec)

mysql> insert into album values(102,"xyz","dvd",19990612,124);

Query OK, 1 row affected (0.33 sec)

mysql> select * from album;

6 rows in set (0.00 sec)

Queries

a)      List  musician  name,  title  of  the  song  which  he  has  played,  the  album  in  which  song  has occulted.

mysql> select m.name , s.title, a.aid from musician m, performs p, songs s, album a where m.ssn=p.ssn and p.title=s.title and s.aid=a.aid;

b)      List the details of songs which are performed bymore than 3 musicians.

mysql> select title,author from songs where title=(select title from performs p group by title having count(*)>1);

c)      List the different instruments played by the musicians and the average number of musicians who play the instrument.

mysql> select iname, (select count(ssn) from plays where id=i.id group by id) as no_of_musicians from instrument i;

d)     Retrieve  album  title  produced  by  the  producer  who  plays  guitar  as  well  as  flute  and  has produced no of songs greater than the average songsproduced by all producers.

mysql> select a.atitle from album a , musician m, plays p,performs pe where a.ssn=m.ssn and m.ssn=p.ssn and p.id=(select id from instrument where id="guitar") and p.id=(select id from instrument where id="flute") having count(pe.title) > (select avg(title) from performs);

e)      List the details of musicians who can play all the instruments present.

mysql> select m.name from musician m, plays p,instrument i where m.ssn=p.ssnhaving count(p.id)=count(i.id);

Posted in: II_DBMS_lab