dq4SXXUc

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