x0S22He8

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