# Session three: It's always time to restart | Banco de dados
**Autor**: Vitório Trindade Santana
**Turma**: Informática C
**Número**: 50
[toc]
## Modelagem 1: Catálogo de Filmes
### Create Script
```sql=
CREATE TABLE TB_Categoria (
ID_Categoria int PRIMARY KEY AUTO_INCREMENT,
NM_Categoria varchar(100)
);
CREAte TABLE TB_Produto (
ID_Produto int PRIMARY KEY AUTO_INCREMENT,
ID_Categoria int,
NM_Produto varchar (200),
VL_Preco decimal (15,2),
FOREIGN KEY (ID_Categoria) REFERENCES TB_Categoria (ID_Categoria)
);
CREATE TABLE TB_Estoque (
ID_Estoque int PRIMARY KEY AUTO_INCREMENT,
ID_Produto int,
QTD_Minima int,
QTD_Disponivel int,
FOREIGN KEY (ID_Produto) REFERENCES TB_Produto (ID_Produto)
);
CREATE TABLE TB_Endereco (
ID_Endereco int PRIMARY KEY AUTO_INCREMENT,
DS_Cep varchar (100),
DS_Endereco varchar (100),
NR_Endereco varchar (100),
DS_Cidade varchar (100)
);
CREATE TABLE TB_Cliente (
ID_Cliente int PRIMARY KEY AUTO_INCREMENT,
NM_Cliente Varchar (150),
DS_Cpf varchar (100),
ID_Endereco int,
FOREIGN KEY (ID_Endereco) REFERENCES TB_Endereco (ID_Endereco)
);
CREATE TABLE TB_Venda (
ID_Venda int PRIMARY KEY AUTO_INCREMENT,
ID_Cliente int,
DS_Nota_Fiscal varchar (70),
TP_Forma_Pagamento varchar (100),
QTD_Parcelas int,
DT_Venda datetime,
ID_Endereco int,
FOREIGN KEY (ID_Cliente) REFERENCES TB_Cliente (ID_Cliente),
FOREIGN KEY (ID_Endereco) REFERENCES TB_Endereco (ID_Endereco)
);
CREATE TABLE TB_Venda_Item (
ID_Venda_Item int PRIMARY KEY AUTO_INCREMENT,
ID_Venda Int,
ID_Produto int,
FOREIGN KEY (ID_Venda) REFERENCES TB_Venda (ID_Venda),
FOREIGN KEY (ID_Produto) REFERENCES TB_Produto (ID_Produto)
);
```
### Insert Script
```sql=
INSERT into TB_Categoria (NM_Categoria)
VALUES ('Eletrônicos'),
('Livraria');
INSERT INTO TB_Produto (ID_Categoria, NM_Produto, VL_Preco)
VALUES (1, 'Kindle 8', 269.99),
(1, 'Kindle 9', 305),
(1, 'Kindle 10', 420),
(1, 'Kindle Oasis', 260),
(1, 'Kindle Oasis Lite', 220),
(1, 'Kindle Paperwhite', 320),
(1, 'Kindle Paperwhite S', 400),
(2, 'O Homem de Giz', 41.99),
(2, 'Star Wars - Marcas da Guerra', 60),
(2, 'A cinco passos de você', 45),
(2, 'O Hobbit', 32),
(2, 'O duque e eu', 27),
(2, 'Harry Potter e a criança amaldiçoada', 68),
(2, 'Animais fantásticos e onde habitam', 78),
(2, 'Drácula', 47),
(1, 'Celular Poco X3', 1200),
(1, 'Kit upgrade Ryzen 7 2700', 1800),
(1, 'Smart Tv Samsung U7000', 2400),
(1, 'Redmi Airdots S', 120),
(1, 'Smartphone Galaxy S9', 1300);
INSERT INTO TB_Estoque (ID_Produto, QTD_Minima, QTD_Disponivel)
VALUES (1, 10, 12),
(2, 7, 8),
(3, 20, 24),
(4, 10, 17),
(5, 30, 34),
(6, 25, 32),
(7, 20, 42),
(8, 30, 47),
(9, 20, 52),
(10, 10, 27),
(11, 5, 14),
(12, 10, 17),
(13, 34, 50),
(14, 25, 28),
(15, 5, 10),
(16, 8, 15),
(17, 20, 27),
(18, 18, 22),
(19, 45, 56),
(20, 20, 22);
INSERT into TB_Endereco (DS_Cep, DS_Endereco, NR_Endereco, DS_Cidade)
VALUES ('13087-534', 'Rua Alfredo da Costa Figo', '117', 'Campinas'),
('13422-210', 'Avenida Comendador Leopoldo Dedini', '89', 'Piracicaba'),
('04890-187', 'Viela Vinte e Um', '100', 'São Paulo'),
('18608-004', 'Rua Alfredo Corêa', '50', 'Botucatu'),
('12302-319', 'Alameda Antares', '23', 'Jacareí'),
('05209-040', 'Rua Nandiras', '12', 'São Paulo'),
('13234-620', 'Rua Onofre Machado', '37', 'Campo Limpo Paulista'),
('13456-343', 'Estrada de Servidão', '56', 'Santa Bárbara D Oeste'),
('07749-160', 'Rua Maria Bernarda Butler', '23', 'Caieiras'),
('06712-180', 'Rua Saxão', '54', 'Cotia'),
('08371-240', 'Rua Simão Cerqueira', '244', 'São Paulo'),
('03133-000', 'Rua Ibitirama', '241', 'São Paulo'),
('08660-050', 'Rua Miguel do Nascimento', '734', 'Suzano'),
('13178-550', 'Avenida José Hoffmann', '123', 'Sumaré'),
('13425-135', 'Rua Oito', '153', 'Piracicaba'),
('13181-726', 'Rua A', '157', 'Sumaré'),
('12234-004', 'Rua Álvaro Lordello', '274', 'São José dos Campos'),
('08582-645', 'Rua Itápolis', '97', 'Itaquaquecetuba'),
('06326-070', 'Viela Rio Grande do Norte', '54', 'Carapicuíba'),
('14165-440', 'Avenida Antônio Vanzella', '84', 'Sertãozinho'),
('12913-008', 'Rua Ilha Bela', '64', 'Bragança Paulista'),
('12603-000', 'Avenida Targino Vilella Nunes', '21', 'Lorena'),
('13178-380', 'Rua Regina Consulin Esclhão', '342', 'Sumaré'),
('12509-860', 'Rua René Pinheiro Chagas', '234', 'Guaratinguetá'),
('18273-100', 'Rua Santo Amaro', '234', 'Tatuí');
INSERT INTO TB_Cliente (NM_Cliente, DS_Cpf, ID_Endereco)
VALUES ('Marcela Carolina Baptista', '401.635.240-17', 1),
('Bruno Manoel Porto', '186.894.800-56', 2),
('Heloisa Isabela Barbosa', '278.362.330-73', 3),
('Carlos Marcos Costa', '364.354.370-09', 4),
('Juan Danilo da Paz', '049.474.460-05', 5),
('Manuel Cláudio Nathan da Rocha', '587.146.400-90', 6),
('Laura Marina Dias', '412.481.930-75', 7),
('Joana Bruna da Mata', '217.338.740-54', 8),
('Leonardo Danilo da Mata', '407.903.050-97', 9),
('Miguel Heitor Bernardo Silveira', '683.105.310-60', 10),
('Emanuel Bryan Gomes', '031.721.020-33', 11),
('Enrico Edson Aragão', '456.597.470-35', 12),
('Ana Isabelle Laís Barros', '222.393.840-00', 13),
('Sophia Lívia Dias', '499.021.230-40', 14),
('Agatha Gabriela Fátima Galvão', '953.954.340-11', 15),
('Bianca Giovana Beatriz Mendes', '253.482.480-53', 16),
('Luan Pietro da Rocha', '589.291.420-87', 17),
('Cecília Jaqueline Peixoto', '558.091.920-41', 18),
('Manuela Regina Sophia Rocha', '451.834.100-70', 19),
('Mariah Lara Tatiane da Cruz', '609.892.730-83', 20),
('Guilherme Miguel Luan Silveira', '966.477.500-29', 21),
('Luzia Manuela Andrea Castro', '660.750.350-01', 22),
('Sebastião Iago Diego Bernardes', '984.553.760-07', 23),
('Ester Fabiana Elisa Rezende', '658.336.990-70', 24),
('Lavínia Marli Figueiredo', '869.031.530-63', 25);
INSERT INTO TB_Venda (ID_Cliente, DS_Nota_Fiscal, TP_Forma_Pagamento, QTD_Parcelas, DT_Venda, ID_Endereco)
VALUES (1, 17263128, 'Boleto', 1, '2021-11-7', 1),
(2, 12873628, 'Carnê', 4, '2021-03-11', 2),
(3, 12323313, 'Cartão de crédito', 2, '2021-06-26', 3),
(4, 38723816, 'Boleto', 1, '2021-09-3', 4),
(5, 87163289, 'Carnê', 2, '2021-04-23', 5),
(6, 26381773, 'Cartão de crédito', 3, '2021-05-12', 6),
(7, 12736712, 'Cartão de crédito', 6, '2021-09-14', 7),
(8, 18237217, 'Cartão de crédito', 2, '2021-02-19', 8),
(9, 71236784, 'Boleto', 1, '2021-09-30', 9),
(10, 11723671, 'Boleto', 1, '2021-07-28', 10),
(11, 91238129, 'Boleto', 1, '2021-05-26', 11),
(12, 72637177, 'Cartão de crédito', 3, '2021-03-29', 12),
(13, 12335463, 'Boleto', 2, '2021-11-7', 13),
(14, 28179928, 'Cartão de crédito', 5, '2021-12-27', 14),
(15, 43672834, 'Cartão de crédito', 2, '2021-12-2', 15),
(16, 38472834, 'Cartão de crédito', 3, '2021-08-18', 16),
(17, 23478872, 'Boleto', 1, '2021-07-4', 17),
(18, 28748277, 'Boleto', 1, '2021-05-9', 18),
(19, 18277378, 'Boleto', 1, '2021-09-11', 19),
(20, 81278123, 'Cartão de crédito', 3, '2021-02-2', 20),
(21, 14578362, 'Cartão de crédito', 2, '2021-01-23', 21),
(22, 18237388, 'Boleto', 1, '2021-11-7', 22),
(23, 18273192, 'Boleto', 1, '2021-05-27', 23),
(24, 21381723, 'Carnê', 2, '2021-04-10', 24),
(25, 45282187, 'Boleto', 1, '2021-09-3', 25);
INSERT INTO TB_Venda_Item (ID_Venda, ID_Produto)
VALUES ( 1, 2),
( 1, 16),
( 1, 2),
( 2, 6),
( 2, 10),
( 2, 11),
( 3, 14),
( 3, 16),
( 3, 4),
( 4, 7),
( 4, 17),
( 4, 19),
( 5, 5),
( 5, 6),
( 5, 9),
( 6, 2),
( 6, 9),
( 6, 18),
( 7, 2),
( 7, 3),
( 7, 9),
( 7, 1),
( 7, 2),
( 8, 3),
( 8, 5),
( 9, 6),
( 9, 8),
( 9, 12),
( 10, 16),
( 10, 2),
( 10, 3),
( 11, 5),
( 11, 15),
( 11, 18),
( 12, 17),
( 12, 16),
( 12, 12),
( 13, 2),
( 13, 3),
( 13, 6),
( 14, 7),
( 14, 20),
( 14, 16),
( 15, 13),
( 15, 1),
( 15, 3),
( 15, 8),
( 16, 3),
( 16, 7),
( 17, 15),
( 17, 17),
( 17, 20),
( 18, 2),
( 18, 9),
( 18, 14),
( 19, 2),
( 19, 19),
( 19, 20),
( 20, 11),
( 20, 20),
( 20, 11),
( 21, 12),
( 21, 18),
( 21, 15),
( 22, 1),
( 22, 6),
( 22, 7),
( 23, 15),
( 23, 2),
( 24, 12),
( 24, 20),
( 25, 1),
( 25, 13),
( 25, 19),
( 25, 12),
( 25, 4);
```
{"metaMigratedAt":"2023-06-16T00:33:38.221Z","metaMigratedFrom":"Content","title":"Session three: It's always time to restart | Banco de dados","breaks":true,"contributors":"[{\"id\":\"052c8e10-b233-429a-a1e0-0d8df7864830\",\"add\":10796,\"del\":0}]"}