-- 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 song

CREATE 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 band

CREATE 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