-
Notifications
You must be signed in to change notification settings - Fork 0
/
SqlScript
96 lines (86 loc) · 1.93 KB
/
SqlScript
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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
drop table pedido;
drop table usuario_plano;
drop table video;
drop table plano;
drop table status_pedido;
drop table usuario;
drop table status_video;
CREATE TABLE status_video (
id_status INT AUTO_INCREMENT PRIMARY KEY,
descricao VARCHAR(500)
);
CREATE TABLE usuario (
id_user INT auto_increment primary key,
nome VARCHAR(100),
sobrenome VARCHAR(100),
email VARCHAR(100),
senha VARCHAR (500),
tipo VARCHAR(50),
endereco VARCHAR(200),
numero VARCHAR(10),
complemento VARCHAR(50),
bairro VARCHAR(100),
cidade VARCHAR(100),
uf VARCHAR(2),
cep VARCHAR(10),
pais VARCHAR(60),
apelido VARCHAR(30),
dat_cadastro date,
flag_ativo INT
);
CREATE TABLE status_pedido (
id_status INT PRIMARY KEY,
descricao VARCHAR(500)
);
CREATE TABLE plano (
id_plano INT PRIMARY KEY,
valor NUMERIC(8,2),
descricao VARCHAR(100),
duracao_dias INT
);
CREATE TABLE video (
id_video INT auto_increment PRIMARY KEY,
titulo VARCHAR (100),
descricao VARCHAR (500),
img_miniatura VARCHAR (500),
id_status INT,
local_video VARCHAR (500),
FOREIGN KEY(id_status)
REFERENCES status_video(id_status)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
CREATE TABLE usuario_plano (
id_user INT,
id_plano INT,
dat_inicio DATE,
dat_fim DATE,
PRIMARY KEY(id_user, id_plano),
FOREIGN KEY(id_user)
REFERENCES usuario(id_user)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FOREIGN KEY(id_plano)
REFERENCES plano(id_plano)
ON DELETE CASCADE
ON UPDATE NO ACTION
);
CREATE TABLE pedido (
id_pedido double auto_increment,
id_user INT,
id_usuario INT,
id_plano INT,
dat_solicitacao DATE,
dat_atualizacao DATE,
id_status INT,
PRIMARY KEY(id_pedido),
FOREIGN KEY(id_status)
REFERENCES status_pedido(id_status)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FOREIGN KEY(id_user)
REFERENCES usuario(id_user)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
select "Sucesso!";