playlistgen.github.io/schema at master · sherryson/playlistgen.github.io · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
USE music_project;

CREATE TABLE artistsAlbum (
  artistsname VARCHAR(100) NOT NULL,
  albumname VARCHAR(100) NOT NULL,
  year INT NOT NULL
) engine=InnoDB;

CREATE TABLE pop (
  artistsname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(artistsname),
  albumname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(albumname),
  PRIMARY KEY(artistsname, albumname)
) engine=InnoDB;

CREATE TABLE electronic (
  artistsname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(artistsname),
  albumname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(albumname),
  PRIMARY KEY(artistsname, albumname)
) engine=InnoDB;

CREATE TABLE country (
  artistsname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(artistsname),
  albumname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(albumname),
  PRIMARY KEY(artistsname, albumname)
) engine=InnoDB;

CREATE TABLE hipHopRap (
  artistsname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(artistsname),
  albumname VARCHAR(100) NOT NULL REFERENCES artistsAlbum(albumname),
  PRIMARY KEY(artistsname, albumname)
) engine=InnoDB;

CREATE TABLE songs (
  sname VARCHAR(100),
  artistsname VARCHAR(100) NOT NULL,
  runtime time NOT NULL,
  PRIMARY KEY (sname)
) engine=InnoDB;

ALTER TABLE songs
	ADD FOREIGN KEY (artistsname) REFERENCES artistsAlbum(artistsname) ON UPDATE CASCADE;

CREATE TABLE playlists (
  pname VARCHAR(20) NOT NULL,
  sname VARCHAR(100),
  PRIMARY KEY (pname)
) engine=InnoDB;

ALTER TABLE playlists
	ADD FOREIGN KEY (sname) REFERENCES songs(sname);