Songs

Task

Suppose you want to design a database which stores information about songs and you want to allow your customers to click like if they like a song.

We want to store the title of each song, its singer, and the total number of likes it got. In addition to this, we want also to know whether the singer is also the author of the song. As for the singers, our database will store their stage-name (if there is one) or the real name and surname (or both stage-name and real name and surname).

It is necessary to be able to manage songs such as "Sally" which was sung both by Vasco Rossi (who is also the author of the song) and by Fiorella Mannoia.

Design an entity-relationship diagram, a relational schema and an instance of the database.

Create also the queries which will return:

1. an alphabetical list of all the songs sung by Vasco Rossi.
2. an alphabetical list of all the songs sung and written by Vasco Rossi.
3. a list of the songs which got more than 50 "likes".
4. a list of all the songs sung by artists who use a stage-name. For each song display the title, the total number of "like" it got, and the stage-name(s) of the singer(s) who sing(s) it.
5. a list of the singers who are not songwriters.