nxwLzirP

1-- -----------------------------------------------------
2-- Table `artist`
3-- -----------------------------------------------------
4CREATE TABLE IF NOT EXISTS `artist` (
5  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
6  `name` VARCHAR(16) NOT NULL,
7  `description` VARCHAR(128) 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) NOT NULL,
19  `released_at` DATE NOT NULL,
20  PRIMARY KEY (`id`, `artist_id`),
21  INDEX `fk_album_artist1_idx` (`artist_id` ASC),
22  CONSTRAINT `fk_album_artist1`
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`, `album_id`),
39  INDEX `fk_song_album1_idx` (`album_id` ASC),
40  CONSTRAINT `fk_song_album1`
41    FOREIGN KEY (`album_id`)
42    REFERENCES `album` (`id`)
43    ON DELETE NO ACTION
44    ON UPDATE NO ACTION)
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) NOT NULL,
64  PRIMARY KEY (`id`))
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `record_label_x_artist`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `record_label_x_artist` (
72  `artist_id` INT UNSIGNED NOT NULL,
73  `record_label_id` INT UNSIGNED NOT NULL,
74  INDEX `fk_record_label_x_artist_artist_idx` (`artist_id` ASC) VISIBLE,
75  PRIMARY KEY (`artist_id`, `record_label_id`),
76  INDEX `fk_record_label_x_artist_record_label1_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 NO ACTION
81    ON UPDATE NO ACTION,
82  CONSTRAINT `fk_record_label_x_artist_record_label1`
83    FOREIGN KEY (`record_label_id`)
84    REFERENCES `record_label` (`id`)
85    ON DELETE NO ACTION
86    ON UPDATE NO ACTION)
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  INDEX `fk_featuring_song1_idx` (`song_id` ASC) VISIBLE,
97  INDEX `fk_featuring_artist1_idx` (`artist_id` ASC) VISIBLE,
98  PRIMARY KEY (`song_id`, `artist_id`),
99  CONSTRAINT `fk_featuring_song1`
100    FOREIGN KEY (`song_id`)
101    REFERENCES `song` (`id`)
102    ON DELETE NO ACTION
103    ON UPDATE NO ACTION,
104  CONSTRAINT `fk_featuring_artist1`
105    FOREIGN KEY (`artist_id`)
106    REFERENCES `artist` (`id`)
107    ON DELETE NO ACTION
108    ON UPDATE NO ACTION)
109ENGINE = InnoDB;
110
111
112-- -----------------------------------------------------
113-- Table `song_x_genre`
114-- -----------------------------------------------------
115CREATE TABLE IF NOT EXISTS `song_x_genre` (
116  `song_id` INT UNSIGNED NOT NULL,
117  `genre_id` INT UNSIGNED NOT NULL,
118  PRIMARY KEY (`song_id`, `genre_id`),
119  INDEX `fk_song_x_genre_genre1_idx` (`genre_id` ASC),
120  CONSTRAINT `fk_song_x_genre_song1`
121    FOREIGN KEY (`song_id`)
122    REFERENCES `song` (`id`)
123    ON DELETE NO ACTION
124    ON UPDATE NO ACTION,
125  CONSTRAINT `fk_song_x_genre_genre1`
126    FOREIGN KEY (`genre_id`)
127    REFERENCES `genre` (`id`)
128    ON DELETE NO ACTION
129    ON UPDATE NO ACTION)
130ENGINE = InnoDB;
131