-- Song---- o The same song can be interpreted by different bands/interprets-- (see table "compilation")-- o The same song can appear on different albums (releases),-- (see table "compilation)CREATE TABLE song ( sid INT NOT NULL PRIMARY KEY, -- song id title VARCHAR(100) -- UNIQUE(sid));-- Release (of an album). Note that the songs are assigned-- using the table "compilation".CREATE TABLE album ( aid INT NOT NULL PRIMARY KEY, -- album id title VARCHAR(100), published DATE -- date this album was published -- UNIQUE(aid));-- A band, a group of people releasing albums-- Membership is establised using "bandrelation"---- o A band can be created or dissolved-- o Several people can be part of a band (see "bandrelation")-- o People can join or leave the band (see "bandrelation")CREATE TABLE band ( bid INT NOT NULL PRIMARY KEY, -- band id name VARCHAR(100), created DATE, -- when was this band created? dissolved DATE -- when did the members break up? -- UNIQUE(bid));-- Compilation, relation between an album and a song-- A song can appear on multiple albums, and a song can also-- be sung by different bands (e.g. a cover version)CREATE TABLE compilation ( aid INT NOT NULL, -- release id sid INT NOT NULL, -- song id bid INT NOT NULL, -- band id UNIQUE(aid, sid, bid));-- A single person-- o At this time a person can only be a member of a band-- o Using optional additional tables a person could also-- o produce an album-- o compose or produce a songCREATE TABLE person ( pid INT NOT NULL PRIMARY KEY, -- person id givenname VARCHAR(100), surname VARCHAR(100) -- UNIQUE(pid));-- band - how are people related to a band?---- This table keeps a history of people joining and leaving the bandCREATE TABLE bandrelation ( bid INT NOT NULL, -- band pid INT NOT NULL, -- person -- reltype ENUM('MEMBER', 'VOCAL', 'GUITAR', 'BASS', 'DRUMS', -- 'TRUMPET', 'MEMBER') DEFAULT 'MEMBER' memberfrom DATE, memberuntil DATE);---- CREATE TABLE songrelation (-- sid INT NOT NULL, -- song-- pid INT NOT NULL, -- person-- reltype ENUM('COMPOSER')-- );-- Usage examples:-- List all albums (releases) of a band:---- SELECT distinct a.title, b.name, a.published-- FROM album a, band b, compilation c-- WHERE b.name = 'Beatles'-- AND c.bid = b.bid -- album by this band-- AND a.aid = c.aid -- right album-- List all songs with the albums they appear on from a band:-- SELECT distinct s.title, a.title-- FROM song s, album a, compilation c-- WHERE b.name = 'Beatles'-- AND b.bid = c.bid -- right band-- AND c.aid = a.aid -- right album-- AND c.sid = s.sid -- right song-- List all cover versions of a song-- SELECT s.title, a.title, b.name-- FROM song s, album a, band b, compilation c-- WHERE s.title = 'Yesterday'-- AND b.name <> 'Beatles'-- AND s.sid = c.sid -- which compilation-- AND c.aid = a.aid -- album name