WEwLPG4P

1
2CREATE TABLE IF NOT EXISTS BANDS (
3  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
4  BAND_NAME           VARCHAR(255)  NOT NULL,
5  WEBSITE             VARCHAR(255),
6
7  PRIMARY KEY (ID)
8);
9
10CREATE TABLE IF NOT EXISTS RELEASE_TYPES (
11  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
12  RELEASE_TYPE        VARCHAR(255)  NOT NULL,
13
14  PRIMARY KEY (ID)
15);
16
17INSERT INTO RELEASE_TYPES (ID, RELEASE_TYPE) VALUES
18  (1, 'album'),
19  (2, 'live'),
20  (3, 'single');
21
22CREATE TABLE IF NOT EXISTS RELEASES (
23  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
24  BAND_ID             BIGINT(20)    NOT NULL,
25  TYPE_ID             BIGINT(20)    NOT NULL,
26  TITLE               VARCHAR(255)  NOT NULL,
27  RATING              TINYINT       CHECK (RATING>=1 AND RATING<=10),
28  RELEASE_YEAR        INT           NOT NULL,
29
30  PRIMARY KEY (ID),
31  CONSTRAINT RELEASES_BANDS_FK FOREIGN KEY (BAND_ID) REFERENCES BANDS (ID),
32  CONSTRAINT RELEASES_RELEASE_TYPES_FK FOREIGN KEY (TYPE_ID) REFERENCES RELEASE_TYPES (ID)
33);
34
35CREATE TABLE IF NOT EXISTS SONGS (
36  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
37  RELEASE_ID          BIGINT(20)    NOT NULL,
38  TITLE               VARCHAR(255)  NOT NULL,
39  RECEIVED_AN_AWARD   TINYINT(1)    NOT NULL DEFAULT FALSE,
40
41  PRIMARY KEY (ID),
42  CONSTRAINT SONGS_RELEASES_FK FOREIGN KEY (RELEASE_ID) REFERENCES RELEASES (ID)
43);
44
45CREATE TABLE IF NOT EXISTS MUSICIANS (
46  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
47  MUSCIAN_NAME        VARCHAR(255)  NOT NULL,
48  WEBSITE             VARCHAR(255),
49
50  PRIMARY KEY (ID)
51);
52
53CREATE TABLE IF NOT EXISTS BAND_ROLES (
54  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
55  ROLE_NAME           VARCHAR(255)  NOT NULL,
56
57  PRIMARY KEY (ID)
58);
59
60INSERT INTO BAND_ROLES (ID, ROLE_NAME) VALUES
61  (1, 'vocals'),
62  (2, 'guitar'),
63  (3, 'bass'),
64  (4, 'keyboards'),
65  (5, 'drums');
66
67CREATE TABLE IF NOT EXISTS MUSICIAN_MEMBERSHIPS (
68  ID                  BIGINT(20)    NOT NULL AUTO_INCREMENT,
69  MUSICIAN_ID         BIGINT(20)    NOT NULL AUTO_INCREMENT,
70  BAND_ID             BIGINT(20)    NOT NULL AUTO_INCREMENT,
71  ROLE_ID             BIGINT(20)    NOT NULL AUTO_INCREMENT,
72  START_YEAR          INT           DEFAULT NULL,
73  END_YEAR            INT           DEFAULT NULL,
74
75  PRIMARY KEY (ID),
76
77  CONSTRAINT MUSICIAN_MEMBERSHIPS_MUSICIANS_FK FOREIGN KEY (MUSICIAN_ID) REFERENCES MUSICIANS (ID),
78  CONSTRAINT MUSICIAN_MEMBERSHIPS_BANDS_FK FOREIGN KEY (BAND_ID) REFERENCES BANDS (ID),
79  CONSTRAINT MUSICIAN_MEMBERSHIPS_BAND_ROLES_FK FOREIGN KEY (ROLE_ID) REFERENCES BAND_ROLES (ID)
80);