zpDJW4e2

1-- -----------------------------------------------------
2-- Table `artist`
3-- -----------------------------------------------------
4CREATE TABLE IF NOT EXISTS `artist` (
5  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
6  `name` VARCHAR(16) CHARACTER SET 'utf8mb4' NOT NULL,
7  `description` VARCHAR(128) CHARACTER SET 'utf8mb4' NULL,
8  PRIMARY KEY (`id`))
9ENGINE = InnoDB;
10
11
12-- -----------------------------------------------------
13-- Table `album`
14-- -----------------------------------------------------
15CREATE TABLE IF NOT EXISTS `album` (
16  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
17  `artist_id` INT UNSIGNED NOT NULL,
18  `title` VARCHAR(16) CHARACTER SET 'utf8mb4' NOT NULL,
19  `released_at` DATE NOT NULL,
20  PRIMARY KEY (`id`),
21  INDEX `album_artist_idx` (`artist_id` ASC),
22  CONSTRAINT `fk_album_artist`
23    FOREIGN KEY (`artist_id`)
24    REFERENCES `artist` (`id`)
25    ON DELETE CASCADE
26    ON UPDATE CASCADE)
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `song`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `song` (
34  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
35  `album_id` INT UNSIGNED NOT NULL,
36  `name` VARCHAR(16) NOT NULL,
37  `duration` INT UNSIGNED NOT NULL,
38  PRIMARY KEY (`id`),
39  INDEX `song_album_idx` (`album_id` ASC),
40  CONSTRAINT `fk_song_album`
41    FOREIGN KEY (`album_id`)
42    REFERENCES `album` (`id`)
43    ON DELETE CASCADE
44    ON UPDATE CASCADE)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `genre`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `genre` (
52  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
53  `name` VARCHAR(32) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' NOT NULL,
54  PRIMARY KEY (`id`))
55ENGINE = InnoDB;
56
57
58-- -----------------------------------------------------
59-- Table `record_label`
60-- -----------------------------------------------------
61CREATE TABLE IF NOT EXISTS `record_label` (
62  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
63  `name` VARCHAR(32) CHARACTER SET 'utf8mb4' NOT NULL,
64  PRIMARY KEY (`id`))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `artist_x_record_label`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `artist_x_record_label` (
72  `artist_id` INT UNSIGNED NOT NULL,
73  `record_label_id` INT UNSIGNED NOT NULL,
74  PRIMARY KEY(`artist_id`),
75  INDEX `record_label_x_artist_artist_idx` (`artist_id` ASC),
76  INDEX `record_label_x_artist_record_label_idx` (`record_label_id` ASC),
77  CONSTRAINT `fk_record_label_x_artist_artist`
78    FOREIGN KEY (`artist_id`)
79    REFERENCES `artist` (`id`)
80    ON DELETE CASCADE
81    ON UPDATE CASCADE,
82  CONSTRAINT `fk_record_label_x_artist_record_label`
83    FOREIGN KEY (`record_label_id`)
84    REFERENCES `record_label` (`id`)
85    ON DELETE CASCADE
86    ON UPDATE CASCADE)
87ENGINE = InnoDB;
88
89
90-- -----------------------------------------------------
91-- Table `featuring`
92-- -----------------------------------------------------
93CREATE TABLE IF NOT EXISTS `featuring` (
94  `song_id` INT UNSIGNED NOT NULL,
95  `artist_id` INT UNSIGNED NOT NULL,
96  PRIMARY KEY (`song_id`, `artist_id`),
97  INDEX `featuring_artist_idx` (`artist_id` ASC),
98  CONSTRAINT `fk_featuring_song`
99    FOREIGN KEY (`song_id`)
100    REFERENCES `song` (`id`)
101    ON DELETE CASCADE
102    ON UPDATE CASCADE,
103  CONSTRAINT `fk_featuring_artist`
104    FOREIGN KEY (`artist_id`)
105    REFERENCES `artist` (`id`)
106    ON DELETE CASCADE
107    ON UPDATE CASCADE)
108ENGINE = InnoDB;
109
110
111-- -----------------------------------------------------
112-- Table `song_x_genre`
113-- -----------------------------------------------------
114CREATE TABLE IF NOT EXISTS `song_x_genre` (
115  `song_id` INT UNSIGNED NOT NULL,
116  `genre_id` INT UNSIGNED NOT NULL,
117  PRIMARY KEY (`song_id`, `genre_id`),
118  INDEX `song_x_genre_genre_idx` (`genre_id` ASC),
119  CONSTRAINT `fk_song_x_genre_song`
120    FOREIGN KEY (`song_id`)
121    REFERENCES `song` (`id`)
122    ON DELETE CASCADE
123    ON UPDATE CASCADE,
124  CONSTRAINT `fk_song_x_genre_genre`
125    FOREIGN KEY (`genre_id`)
126    REFERENCES `genre` (`id`)
127    ON DELETE CASCADE
128    ON UPDATE CASCADE)
129ENGINE = InnoDB;
130
131