# SESSION THREE — BANCO DE DADOS
Author: Igor Lima Charles
N°: 18
Grade: INFO A

# MODELAGEM LOJA

## CREATE TABLE

 create table tb_categoria (
               
    id_categoria  int primary key auto_increment,
    nm_categoria varchar(255)
  );
  
  
create table tb_produto ( 
    
    id_produto   int primary key auto_increment,
    id_categoria int,
    nm_produto   varchar (255),
    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(255),
    ds_endereco varchar(255),
    nr_endereco varchar(255),
    ds_cidade varchar(255)
    );
    
    
create table tb_cliente (
      
      id_cliente int primary key auto_increment,
      nm_cliente varchar(255),
      ds_cpf     varchar(255),
      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(255),
       tp_forma_pagamento varchar(255),
       qtd_parcelas int,
       dt_venda datetime,
       id_endereco_entrega int,
       foreign key (id_cliente) references tb_cliente(id_cliente)
       );
       
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)
         );

## INSERTS

INSERT into tb_categoria (nm_categoria) VALUES
("Hardware de Entrada"),
("Hardware de saída"),
("Hardware de processamento"),
("Hardware de armazenamento"),
("Software"),
("Doces"),
("Chaveiros");

INSERT INTO tb_produto (id_categoria, nm_produto, vl_preco) VALUES
(1, "Teclado Mecânico Gamer", 299.99),
(1, "Teclado Gamer HyperX ", 294.00),
(1, "Mouse Gamer Sem Fio Logitech", 500.00),
(1, "Mouse Gamer Com Fio Redragon 1", 114.90),
(2, "Monitor LG LED 23.8´ Widescreen, Full HD, IPS, HDMI - 24MK430H", 1334.99),
(2, "Monitor Acer LED 21.5´ Widescreen, Full HD, HDMI/VGA/DVI - V226HQL", 700.00),
(2, "Monitor LG LED 29´ Ultrawide, IPS, HDMI, FreeSync - 29WK500", 1499.99),
(2, "Monitor Samsung LED 27´ Widescreen Curvo", 1200.00),
(3, "Processador AMD Ryzen 5 3600 Cache 32MB 3.6GHz(4.2GHz Max Turbo) AM4, Sem Vídeo", 1529.99),
(3, "Processador Intel Core i5-10400", 1700),
(3, "Processador Intel Core i5-11400F 11ª Geração", 1300),
(3, "Processador Intel Core i9-10940X", 6000.00),
(4, "HD Seagate Externo Portátil Expansion USB 3.0 2TB Preto - STEA2000400", 400.00),
(4, "HD WD Blue, 1TB, 3.5´, SATA - WD10EZEX", 435.00),
(4, "SSD Kingston A400, 240GB, SATA", 299.00),
(4, "SSD Crucial BX500, 480GB, SATA", 410.00),
(5, "Microsoft Windows 10 Pro 64 Bits Português FQC-08932 COEM", 1100.00),
(5, "Microsoft 365 Family", 226.00),
(5, "Kaspersky Antivírus Internet Security para 1 dispositivo 18 meses PT 1 UN", 69.90),
(5, "Pacote Adobe Creative Cloud — 1 ano", 2500),
(6, "Chaveiro do Cruzeiro", 2.00),
(6, "Chaveiro do Barcelona", 2.00),
(6, "Chaveiro do São Paulo", 2.00),
(6, "Chaveiro do Palmeiras", 2.00),
(6, "Chaveiro do Corinthians", 2.00);

INSERT INTO tb_estoque (id_produto, qtd_minima, qtd_disponivel) VALUES
(1, 2, 20),
(2, 2, 19),
(3, 5, 10),
(4, 5, 10),
(5, 2, 7),
(6, 2, 3),
(7, 2, 1),
(8, 2, 5),
(9, 1, 1),
(10, 1, 1),
(11, 1, 1),
(12, 1, 1),
(13, 5, 10),
(14, 5, 10),
(15, 5, 20),
(16, 5, 15),
(17, 2, 6),
(18, 2, 10),
(19, 1, 0),
(20, 1, 0),
(21, 1, 10),
(22, 1, 10),
(23, 1, 10),
(24, 1, 10),
(25, 1, 10);

INSERT INTO tb_endereco (ds_cep, ds_endereco, nr_endereco, ds_cidade) VALUES
("04863-480", "Rua Pokan Natal — Vila Natal", "01", "São Paulo"),
("01003-905", "Rua José Bonifácio — Sé", "367", "São Paulo"),
("03173-010", "Rua Siqueira Bueno — Belenzinho", "10", "São Paulo"),
("02418-172", "Rua Orlando Alfieri", "300", "São Paulo"),
("01246-080", "Rua Professor Ernest Marcus — Pacaembu", "111", "São Paulo"),
("03436-100", "Rua Ana Carbone — Vila Carrão", "20", "São Paulo"),
("04082-040", "Rua Guaraciara — Indianopólis", "31", "São Paulo"),
("05692-090", "Rua Deputado Euvaldo Lodi — Retiro Morumbi", "54", "São Paulo"),
("05869-200", "Rua Antônio de Barros da Silva — Jardim São José", "66", "São Paulo"),
("03080-050", "Praça Louveira — Tatuapé", "49", "São Paulo"),
("08431-512", "Rua Lisboa — Parque Guaianazes", "15", "São Paulo"),
("04960-110", "Rua Barão de Paiva Manso — Jardim Capela", "03", "São Paulo"),
("04010-100", "Rua Domingos de Morais — Vila Mariana", "35", "São Paulo"),
("05412-040", "Rua Penalva — Pinheiros", "02", "São Paulo"),
("04824-060", "Rua Agar — Parque das Árvores", "16", "São Paulo"),
("02071-013", "Rua Maria Cândida — Vila Guilherme", "21", "São Paulo"),
("04840-640", "Praça Júlio de Matos — Conjunto Habitacional Brigadeiro Faria Lima", "22", "São Paulo"),
("04783-020", "Rua Doutor Oscar Fernandes Martins — Interlagos", "25", "São Paulo"),
("04823-135", "Travessa Rúbens Tavares da Costa — Jardim Reimberg", "46", "São Paulo"),
("04141-000", "Rua Guiratinga — Chácara Inglesa", "55", "São Paulo"),
("01243-909", "Rua Sergipe — Consolação", "605", "São Paulo"),
("01130-970", "Rua Anhaia — Bom Retiro", "686", "São Paulo"),
("04009-999", "Rua Domingos de Morais — Vila Mariana", "193", "São Paulo"),
("04303-100", "Rua Maria Farah Issa — São Judas", "444", "São Paulo"),
("04565-000", "Rua Flórida — Cidade Monções", "357", "São Paulo"),
("05346-030", "Praça Herman Cappelen — Jaguaré", "698", "São Paulo");

INSERT INTO tb_cliente (nm_cliente, id_endereco) VALUES
("Sabrina Lima Paixão", 1),
("Sabrina Lima Paixão", 2 ),
("Fabio Luz Pdilha", 3),
("Micaely Roberto Nogueira", 4),
("Caique Resende", 5),
("Wesley Silva", 6),
("Sofia Reinaldo Brasil" ,7),
("Julia de Lima Alvarenga", 8),
("Isabela Calvacante Luz", 9),
("Carla Isadora", NULL),
("Stéphanie Dixon Linhares", 11),
("Ana Isabel", 12),
("Fernanda Cardoso", 13),
("Dayane Silva", 14),
("William Da Silva", 15),
("Alice Santana", 16),
("Amanda Antonela", NULL),
("Camila Cristo", 18),
("Clarice Aurora", 19),
("Gustavo Naves", 20),
("Otavio Nunes", 21),
("Alexandra Torres", 22),
("Demetrius Franco", 23),
("Maria Aparecida", 24),
("Brunna Diniz", 25);

INSERT INTO tb_venda (id_cliente, ds_nota_fiscal, tp_forma_pagamento, qtd_parcelas, dt_venda,
id_endereco_entrega)
VALUES
(10, "NF20190506", "A vista", 1, "2019-05-06", NULL),
(10, "NF20190506", "A vista", 1, "2019-05-06", NULL),
(2, "NF44114", "Parcelado", 10, "2020-10-10", 2),
(2, "NF44114", "Parcelado", 10, "2020-10-10", 2),
(3, "NF43706", "A vista", 1, "2020-12-10", 3),
(3, "NF43707", "Crédito", 1, "2020-12-10", 3),
(14, "NF43781", "A Vista", 1, "2020-04-21", NULL),
(1, "NFKJAJK", "Parcelado", 3, "2019-12-10", 1),
(1, "NF12345", "Parcelado", 3, "2019-12-10", 1),
(4, "NF765513", "A vista", 1, "2020-11-04", 4),
(4, "NF20070715", "A vista", 1, "2017-07-15",4),
(5, "NF20170707", "Parcelado", 3, "2017-07-07",5),
(5, "NF20141212", "A vista", 1, "2014-12-12",5),
(6, "NF00000000", "´Parcelado", 2, "2016-05-08",6),
(6, "NFxyxyxyxy", "A vista", 1, "2021-07-10",6),
(7, "NF12345667", "Parcelado", 7, "2021-06-07",7),
(7, "NFxyxyxyxy", "A vista", 1, "2021-07-10",7),
(8, "NFabcdefgh", "Debitado", 1, "2021-09-11",8),
(8, "NF20291112", "Debitado", 1, "2029-11-12",8),
(9, "NF20201201", "A vista", 1, "2020-11-02",9),
(9, "NF20190902", "A vista", 1, "2019-09-02",9),
(11, "NF20210515", "A vista", 1, "2021-05-15", 11),
(11, "NF20210515", "A vista", 1, "2021-05-15", 11),
(12, "NF20180506", "A vista", 1, "2018-05-06", 12),
(12, "NF20190902", "A vista", 1, "2019-09-02", 12),
(13, "NF15151515", "Parcelado", 15, "2020-12-31", 13),
(13, "NF20190902", "A vista", 1, "2020-11-24", 13),
(15, "NF20140812", "A vista", 1, "2014-08-12", 15),
(15, "NF20170606", "A vista", 1, "2017-06-06", 15),
(16, "NF20190909", "Parcelado", 10, "2019-09-09", 16),
(16, "NFxxxxxxxx", "A vista", 1, "2017-05-20", 16),
(17, "NF201718192", "A vista", 1, "2017-05-21", 17),
(18, "NFzzzzzzzzz", "Debitado", 1, "2019-09-02", 18),
(18, "NFa1b2c3d4", "Parcelado", 10, "2020-01-11", 18),
(19, "NFe5f6g7h8", "Parcelado", 8, "2021-02-27", 19),
(19, "NFi9j10klm", "A vista", 1, "2021-07-25", 19),
(22, "NF4096gnb", "A vista", 1, "2015-01-13", 22),
(20, "NFn1o2p3qr", "A vista", 1, "2020-09-29", 20),
(20, "NFs4t5u6vw", "Parcelado", 6, "2015-09-30", 20),
(20, "NFxyz80105", "A vista", 1, "2020-02-29", 20),
(20, "NFxvxvxvxv", "A vista", 1, "2015-03-01", 20),
(22, "NF2467810", "Parcelado", 3, "2017-10-14", 22),
(22, "NF1214162", "A vista", 1, "2014-01-12", 22),
(23, "NF78513221", "A vista", 1, "2020-06-01", 23),
(23, "NFhaziahz", "A vista", 1, "2018-08-29", 23),
(24, "NFjkhbvhb", "Parcelado", 3, "2016-09-03", 24),
(24, "NF875745", "A vista", 1, "2016-02-29", 24),
(25, "NFb1h1c3a", "A vista", 1, "2020-06-18", 25),
(21, "NFk4c3g1n", "Parcelado", 3, "2019-10-03", 21);

INSERT INTO tb_venda_item (id_venda, id_produto)
VALUES
(1,1),
(1,2),
(2, 15),
(2, 16),
(3,3),
(3,6),
(4,1),
(4,5),
(5,7),
(5,8),
(6,12),
(6,12),
(7,1),
(7,14),
(8,10),
(8,9),
(9,10),
(9,9),
(10, 1),
(10, 2),
(11,11),
(11,12),
(12,1),
(12,2),
(13,18),
(13,3),
(14,14),
(14,15),
(15,15),
(15,1),
(16,16),
(16,16),
(17,17),
(18,18),
(18,19),
(19,19),
(19, 2),
(20, 1),
(20, 2),
(20, 3),
(20, 4),
(21, 1),
(22, 5),
(22, 15),
(23, 3),
(23, 4),
(24, 9),
(24, 7),
(25, 5);