zf6krThS

1/*
2Creación de esquema*/
3drop schema if exists superstarbts;
4create database if not exists superstarbts;
5use superstarbts;
6
7/*creación de tablas*/
8drop table if exists discos;
9create table if not exists discos (
10id_disco int not null auto_increment,
11nombre_disco varchar (100),
12primary key (id_disco)
13);
14
15drop table if exists canciones;
16create table if not exists canciones (
17id_cancion int not null auto_increment,
18nombre_cancion varchar (100),
19id_cancion_disco int,
20primary key (id_cancion)
21);
22
23drop table if exists cantantes;
24create table if not exists cantantes (
25
26id_cantante int not null auto_increment,
27nombre_cantante varchar (100),
28primary key (id_cantante)
29);
30
31drop table if exists dificultad;
32create table if not exists dificultad (
33id_dificultad int not null auto_increment,
34dificultad_tipo varchar (6),
35primary key (id_dificultad)
36);
37
38drop table if exists puntuaciones;
39create table if not exists puntuaciones (
40id_puntuacion int not null auto_increment,
41id_theme_disco int,
42id_dificultad_cancion int,
43puntuacion int,
44Sperfect int,
45perfect int,
46good int,
47miss int,
48primary key (id_puntuacion)
49);
50
51drop table if exists cantantes_disco;
52create table if not exists cantantes_disco (
53id_cantante int,
54id_disco int,
55primary key (id_cantante,id_disco)
56);
57
58drop table if exists cartas;
59create table if not exists cartas (
60id_disco int,
61id_cantante int,
62id_theme int,
63rango_carta varchar (1),
64firmado boolean,
65prism boolean,
66primary key (id_disco,id_cantante,id_theme)
67);
68
69drop table if exists themes;
70create table if not exists themes (
71id_theme int not null auto_increment,
72nombre_theme varchar (10),
73primary key (id_theme)
74);
75
76drop table if exists dificultad_cancion;
77create table if not exists dificultad_cancion (
78id_dificultad_cancion int not null auto_increment,
79id_cancion int,
80id_dificultad int,
81primary key (id_dificultad_cancion)
82);
83
84drop table if exists theme_disco;
85create table if not exists theme_disco (
86id_theme_disco int not null auto_increment,
87id_disco int,
88id_theme int,
89primary key (id_theme_disco)
90);
91
92/* relaciones entre tablas*/
93alter table canciones add foreign key (id_cancion_disco) references discos(id_disco);
94
95alter table puntuaciones
96add foreign key (id_theme_disco) references theme_disco(id_theme_disco),
97add foreign key (id_dificultad_cancion) references dificultad_cancion(id_dificultad_cancion);
98
99alter table cantantes_disco
100add foreign key (id_cantante) references cantantes(id_cantante),
101add foreign key (id_disco) references discos(id_disco);
102
103alter table theme_disco
104add foreign key (id_disco) references discos(id_disco);
105
106alter table dificultad_cancion
107add foreign key (id_cancion) references canciones(id_cancion),
108add foreign key (id_dificultad) references dificultad(id_dificultad);
109
110alter table theme_disco
111add foreign key (id_disco) references discos(id_disco),
112add foreign key (id_theme) references themes(id_theme);
113
114alter table cartas
115add foreign key (id_disco) references discos(id_disco),
116add foreign key (id_cantante) references cantantes(id_cantante),
117add foreign key (id_theme) references themes(id_theme);
118/* datos de discos*/
119
120insert into discos (nombre_disco) values
121("Love Yourself: 承 'HER'"),
122("WINGS : You Never Walk Alone"),
123("WINGS"),
124("화양연화 : Young Forever / The Most Beautiful Moment In Life: Young Forever"),
125("화양연화 Pt. 2 / The Most Beautiful Moment In Life Pt. 2"),
126("화양연화 Pt. 1 / The Most Beautiful Moment In Life Pt. 1"),
127("DARK&WILD"),
128("SKOOL LUV AFFAIR"),
129("O!RUL8,2?"),
130("2 COOL 4 SKOOL");
131
132/* datos de cantantes*/
133 insert into cantantes (nombre_cantante) values
134 ("RM"),
135 ("SUGA"),
136 ("JIN"),
137 ("J-HOPE"),
138 ("JIMIN"),
139 ("V"),
140 ("JUNGKOOK");
141
142/*datos de canciones*/
143insert into canciones (nombre_cancion,id_cancion_disco) values 
144("21세기소녀 (21ST CENTURY GIRLS)",3),
145("2학년 (SECOND GRADE)",7),
146("A SUPPLEMENTARY STORY : YOU NEVER WALK ALONE", 2),
147("AM I WRONG",3),
148("BEGIN",3),
149("BTS CYPHER PT.3: KILLER (FEAT. SUPREME BOI)",7),
150("BUTTERFLY",5),
151("CONVERSE HIGH",6),
152("DANGER",7),
153("DNA",1),
154("EPILOGUE: YOUNG FOREVER",4),
155("I NEED U",6),
156("LIE",3),
157("MIC DROP",1),
158("N.O",9),
159("NO MORE DREAM	",10),
160("NOT TODAY",2),
161("PIED PIPER",1),
162("RAIN",7),
163("RUN",5),
164("SAVE ME",4),
165("SPRING DAY",2),
166("TOMORROW",8),
167("WE ARE BULLETPROOF PT.2",10),
168("WE ON",9),
169("WHAILEN 52",5),
170("ê³ ì—½ (AUTUMN LEAVES)",5),
171("등골브레이커 (SPINE BREAKER)",8),
172("보조개 (ILLEGAL)",1),
173("불타오르네 (FIRE)",4),
174("상남자 (BOY IN LUV)",8),
175("이블킥 (BLANKET KICK)",7),
176("이사 (MOVING ON)",6),
177("좋아요 (I LIKE IT)",10),
178("진격의 방탄 (THE RISE OF BANGTAN)",9),
179("쩔어 (DOPE)",6),
180("팔도강산 (SATOORI RAP)",9),
181("피땀눈물 (BLOOD, SWEAT & TEARS)",3),
182("하루만 (JUST ONE DAY)",8),
183("핸드폰 좀 꺼줄래 (CAN YOU TURN OFF YOUR PHONE)",7),
184("호르몬 전쟁 (WAR OF HORMONE)",7),
185("흥탄소년단 (BOYZ WITH FUN)",6),
186("COFFEE",9);
187
188/*datos de dificultad*/
189insert into dificultad (dificultad_tipo) values ("EASY"), ("MEDIUM"), ("HARD");
190
191/*datos de theme*/
192insert into themes (nombre_theme) values ("Theme Pt.1"), ("Theme Pt.2"), ("Theme Pt.3");
193/* datos combinados para tablas dinámicas */
194insert into cantantes_disco select c.id_cantante, d.id_disco from cantantes c left join discos d on (c.id_cantante);
195insert into dificultad_cancion (id_cancion,id_dificultad) select c.id_cancion, d.id_dificultad from dificultad d left join canciones c on (c.id_cancion) order by 1,2;
196insert into theme_disco (id_disco,id_theme) select d.id_disco, t.id_theme from themes t left join discos d on (d.id_disco);
197insert into puntuaciones (id_theme_disco,id_dificultad_cancion) select d.id_theme_disco, t.id_dificultad_cancion from theme_disco d left join dificultad_cancion t on (d.id_disco);