Criacao de Tabelas BD FINAL

CREATE SCHEMA IchibaSuperMarket;
USE IchibaSuperMarket;


-- TABELA DE CURSOS

CREATE TABLE curso (

    id char(4),
    cargahoraria integer(4) not null CHECK ( cargahoraria > 0 ),
    descricao varchar(40),
    primary key(id)
);

-- TABELA DE JORNADA DE TRABALHO

CREATE TABLE jornadatrabalho (

    id char(4) ,
    descricao varchar(60),
    trabalha_sabado boolean not null default true,
    primary key(id)

);


-- TABELA DE TURNO

CREATE TABLE turno (

    cod char(4),
    descricao varchar(60),
    hora_inicio time not null,
    hora_fim time not null,
    primary key (cod)

);

-- TABELA DE DIA

CREATE TABLE dia (

    sequencial char(4),
    descricao varchar(60),
    primary key (sequencial)

);

-- TABELA DA MATRIZ

CREATE TABLE matriz (

    CNPJ char(14),
    nomefantasia varchar(10) not null,
    primary key (CNPJ)

);

-- TABELA DE TELEFONE DA MATRIZ


CREATE TABLE telefone_matriz (

    CNPJ char(14),
    telefone char(10),
    CONSTRAINT telefone_matriz_pk  primary key (CNPJ, telefone),
    CONSTRAINT fk_cnpjmatriz foreign key (CNPJ) references matriz (CNPJ) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE FILIAL

CREATE TABLE filial (

    seq char(4),
    CNPJ_Matriz char(14) not null,
    CPF_gerente char(11) ,
    endereco varchar(50),
    qtd_func int(4) CHECK (qtd_func >= 0),
    CONSTRAINT filial_pk primary key ( seq , CNPJ_Matriz ),
    CONSTRAINT fk_cnpjmatrizfilial foreign key ( CNPJ_MATRIZ ) references matriz (CNPJ) ON DELETE CASCADE ON UPDATE CASCADE

);

-- TABELA DE TELEFONE DA FILIAL

CREATE TABLE telefone_filial (

    seq_filial char(4),
    CNPJ_Matriz char(14),
    telefone char(10),
    CONSTRAINT telefone_filial_pk primary key (seq_filial , CNPJ_Matriz , telefone ),
    CONSTRAINT fk_cnpj foreign key (seq_filial, CNPJ_Matriz) references filial (seq , CNPJ_Matriz) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE FUNCIONARIOS


CREATE TABLE funcionario (

    CPF char(11) ,
    id_jornada char(4) ,
    seq_filial char(4),
    cnpj_matriz char(14),
    data_admissao date not null,
    sex enum ('M', 'F'),
    estado_civil varchar(10),
    login varchar(60) default 'func' ,
    senha varchar(15) default 'func',
    RG char(7) not null UNIQUE,
    nome varchar(45),
    situacao varchar(10),
    endereco varchar(45),
    primary key(CPF),
    foreign key (id_jornada) references jornadatrabalho (id) ,
    foreign key ( seq_filial ) references filial (seq),
    foreign key (cnpj_matriz) references matriz (CNPJ)

);

-- TABELA DE ESTOQUISTA HERDA FUNCIONARIO


CREATE TABLE estoquista (

    CPF char(11),
    primary key (CPF),
    CONSTRAINT fk_estoq foreign key (CPF) references funcionario (CPF) ON DELETE CASCADE ON UPDATE CASCADE

);

-- TABELA DE DBA HERDA FUNCIONARIO

CREATE TABLE DBA (

    CPF char(11),
    primary key (CPF),
    CONSTRAINT fk_dba foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);

-- TABELA DE GERENTE HERDA FUNCIONARIO

CREATE TABLE gerente(

    CPF char(11),
    primary key (CPF),
    CONSTRAINT fk_gerente foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE ENTREGADOR HERDA FUNCIONARIO

CREATE TABLE entregador(

    CPF char(11),
    primary key (CPF),
    CONSTRAINT fk_entregador foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE SUPERVISOR ESTOQUE HERDA FUNCIONARIO

CREATE TABLE supervisorestoque (

    CPF char(11),
    primary key(CPF),
    CONSTRAINT fk_super foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);



-- TABELA MULTVALORADA DE FUNCIONARIO


CREATE TABLE telefone_funcionario(

    CPF char(11),
    telefone char(11),
    primary key (CPF, telefone),
    CONSTRAINT fk_telefonefuncinario foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);



-- alterando a tabela filial

ALTER TABLE filial add constraint foreign key ( CPF_gerente ) references gerente (CPF);


-- TABELA DE NOTIFICAÇÃO DADA A FUNCIONARIO

CREATE TABLE notificacao (

    id char(4),
    cpf_fun char(11),
    dia date not null,
    descricao varchar(80),
    primary key (id),
    foreign key (Cpf_fun) references funcionario (CPF)

);


-- TABELA DE NOTIFICAÇÃO DE MULTA DADA A FUNCIONARIO HERDA DE NOTIFICAÇÃO

CREATE TABLE notif_multa (

    id char(4),
    pontos_cnh int,
    valor float not null,
    cep char(8),
    complemento varchar(30),
    primary key(id),
    CONSTRAINT fk_notmulta foreign key (id) references notificacao(id) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE NOTIFICAÇÃO ADVERTENCIA DADA A FUNCIONARIO HERDA DE NOTIFICAÇÃO

CREATE TABLE notif_advertencia (

    id char(4),
    descricao varchar(20),
    primary key(id),
    CONSTRAINT fk_notadvert foreign key (id) references notificacao (id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- TABELA DE NOTIFICAÇÃO DE SUSPENSAO DADA A FUNCIONARIO HERDA DE NOTIFICAÇÃO

CREATE TABLE notif_suspensao (

    id char(4),
    data_inicio date not null,
    data_termino date not null,
    primary key(id),
    CONSTRAINT not_suspen foreign key (id) references notificacao(id) ON DELETE CASCADE ON UPDATE CASCADE
);



-- TABELA DE NOTIFICAÇÃO DE FALTA DADA A FUNCIONARIO HERDA DE NOTIFICAÇÃO


CREATE TABLE notif_falta (

    id char(4),
    dia date,
    duracao int CHECK (duracao > 0 ),
    primary key (id),
    CONSTRAINT fk_notfalta foreign key (id) references notificacao (id) ON DELETE CASCADE ON UPDATE CASCADE
);


-- TABELA DE FERIAS DE FUNCIONARIO

CREATE TABLE ferias (

    id char(4),
    cpf_fun char(11),
    numero_dias int CHECK ( numero_dias > 0 ),
    seq char(4) not null,
    data_inicio date not null,
    data_fim date not null,
    primary key (id),
    foreign key (cpf_fun) references funcionario (CPF)

);



-- TABELA DE CONTRA CHEQUE

CREATE TABLE contracheque (

    mes_referencia char(2) CHECK( mes_referencia > 0 AND mes_referencia < 13 ),
    CPF char(11),
    data_pagamento date,
    valor_bruto float(5) not null CHECK( valor_bruto >= 0 ),
    valor_liqd float(5) not null CHECK( valor_liqd >= 0),
    valor_desconto float(5) CHECK ( valor_desconto >= 0 ),
    primary key (mes_referencia , CPF),
    foreign key(CPF) references funcionario(CPF) ON DELETE CASCADE ON UPDATE CASCADE

);


-- TABELA DE ENTREGA

CREATE TABLE entrega (

    seq char(4) ,
    CPF_entregador char(11),
    data_entrega date ,
    hora_estimada time,
    primary key (seq),
    foreign key(CPF_entregador) references entregador(CPF)

);



-- TABELA DE COMPRA

CREATE TABLE compra (

    cod char(4),
    seq_entrega char(4),
    valor_total float not null CHECK( valor_total > 0 ),
    dia date not null,
    valor_total_desconto float CHECK (valor_total_desconto >= 0 ),
    statos boolean default true,
    primary key (cod),
    foreign key (seq_entrega) references entrega ( seq )
);

-- TABELA DE COMPRA COMUM

CREATE TABLE compra_comum (

    cod char(4),
    primary key(cod),
    CONSTRAINT fk_compracomum foreign key (cod) references compra (cod) ON DELETE CASCADE ON UPDATE CASCADE
);


-- TABELA DE COMPRA PROGRAMADA

CREATE TABLE compra_programada (

    cod char(4),
    data_1 date not null,
    data2 date,
    esta_ativa boolean default true,
     CONSTRAINT primary key (cod),
    CONSTRAINT fk_compraprogramada foreign key (cod) references compra (cod) ON DELETE CASCADE ON UPDATE CASCADE
);



-- TABELA DE GARAGEM

CREATE TABLE garagem (

    cod char(4),
    seq_filial char(4),
    CNPJ_matriz char(14),
    descricao varchar(15),
    capacidade int(3) not null,
    num_veiculos_atual int(3),
    primary key(cod),
    foreign key(seq_filial , CNPJ_matriz ) references filial (seq , CNPJ_Matriz)


);


-- TABELA DE VEICULO

CREATE TABLE veiculo (

    placa char(7),
    seq_filial char(4),
    CNPJ_matriz char(14),
    cod_garagem char(4) ,
    modelo varchar(15),
    descricao varchar(15),
    cor varchar(10),
    ano year,
    statuss boolean default true,
    primary key (placa),
    foreign key(seq_filial , CNPJ_matriz ) references filial(seq , CNPJ_Matriz),
    foreign key(cod_garagem) references garagem (cod)
);

-- TABELA DE ESTOQUE

CREATE TABLE estoque (

    id char(4),
    seq_filial char(7),
    cnpj_matriz char(14),
    descricao varchar(80),
    dt_ultima_entrada date,
    primary key (id),
    foreign key (seq_filial , cnpj_matriz) references filial (seq , CNPJ_Matriz)

);


-- TABELA MAQUINA

CREATE TABLE maquina (

    id char(4),
    id_estoque char(4),
    id_operador char(11),
    ano year ,
    combustivel varchar(30),
    modelo varchar(30),
    capacidade float CHECK( capacidade > 0 ) ,
    elevavao_max float  CHECK ( elevavao_max > 0 ),
    garantia date,
    tipo varchar(30),
    comprimento float ,
    primary key (id),
    foreign key (id_estoque) references estoque (id),
    foreign key (id_operador) references estoquista (cpf)
);

-- TABELA DE AVARIA

CREATE TABLE avaria (

    id char(4),
    causa varchar(20) not null,
    preco float(3) CHECK ( preco > 0 ),
    obs varchar(40),
    primary key(id)

);

-- TABELA DE PRATELEIRA

CREATE TABLE prateleira (

    codigo char(4),
    altura float(2) not null,
    comprimento float(2) not null,
    posicao_nivel char(2),
    primary key(codigo)

);

-- TABELA DE NCM DE PRODUTO

CREATE TABLE NCM (

    id char(4),
    descricao varchar(40),
    cod_mercosul char(9) not null,
    primary key(id)

);


-- TABELA DE UNIDADE DE PRODUTO

CREATE TABLE unidade (

    cod char(4),
    descricao varchar(30),
    sigla char(2) not null,
    primary key(cod)

);


-- TABELA DE CATEGORIA DE PRODUTO

CREATE TABLE categoria (

    cod char(4),
    descricao varchar(30) not null,
    primary key (cod)

);


-- TABELA DE SUBCATEGORIA DE PRODUTO

CREATE TABLE subcategoria (

    cod char(4),
    cod_categoria char(4),
    descricao varchar(30) not null,
    primary key(cod),
    foreign key (cod_categoria) references categoria(cod)

);

 -- TABELA DE MARCA DE PRODUTO

CREATE TABLE marca (

    cod char(4),
    descricao varchar(40),
    primary key(cod)

);



-- TABELA DE FORNECEDOR

CREATE TABLE fornecedor (

    cod char(4),
    nome varchar(20) not null,
    CNPJ char(14) not null,
    rua varchar(20),
    bairro varchar(15),
    CEP char(8),
    estado varchar(15) not null,
    ativo boolean default true,
    primary key(cod )
);

 -- TABELA DE TELEFONE DE FORNECEDOR

CREATE TABLE telefone_fornecedor(

    cod_fornecedor char(4),
    telefone char(11),
    primary key( cod_fornecedor, telefone),
    CONSTRAINT fk_forn foreign key(cod_fornecedor) references fornecedor (cod) ON DELETE CASCADE ON UPDATE CASCADE

);



-- TABELA  DE PRODUTO REF

CREATE TABLE produto_ref (

    cod char(4),
    id_unidade char(4),
    id_marca char(4),
    id_ncm char(4),
    id_categoria char(4),
    id_subcategoria char(4),
    id_fornecedor char(4),
    qtd_estoque int ,
    ICMS float,
    CST varchar(3),
    preco_por_tabela float,
    cod_barra char(13),
    freq_pedido float NULL,
    descricao varchar(30),
    qtd_min int,
    qtd_total_estoque int,
    preco_ult_compra float,
    primary key (cod),
    foreign key (id_unidade) references unidade (cod),
    foreign key (id_marca) references marca (cod),
    foreign key (id_ncm) references ncm (id),
    foreign key (id_categoria) references categoria (cod),
    foreign key (id_subcategoria) references subcategoria (cod),
    foreign key (id_fornecedor) references fornecedor (cod)

);

-- TABELA ITEM DE COMPRA

CREATE TABLE item_compra (

    cod_compra char(4),
    cod_produto char(4),
    quantidade int CHECK (quantidade > 0) ,
    valor_desconto float default 0 CHECK (valor_desconto >= 0),
    valor_unitario float CHECK (valor_unitario > 0),
    primary key (cod_compra , cod_produto),
    foreign key (cod_compra) references compra (cod),
    foreign key (cod_produto) references produto_ref (cod)
);



-- TABELA DE INCIDENTE

CREATE TABLE incidente (

    cod char(4),
    seq_entrega char(4),
    dataa date not null,
    relatorio varchar(200),
    hora time , # nao precisa ser not null
    primary key(cod),
    foreign key (seq_entrega) references entrega (seq)

);

-- TABELA DOCS

CREATE TABLE docs (

    cod_incidente char(4),
    docs varchar(20),
    primary key (cod_incidente , docs),
    CONSTRAINT fk_incidente foreign key (cod_incidente) references incidente (cod ) ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABELA DE TIPO DE PAGAMENTO

CREATE TABLE tipo_pagamento (

    cod char(4),
    descricao varchar(30),
    primary key(cod)

 );


-- TABELA DE PEDIDO FORNECEDOR

CREATE TABLE pedido_fornecedor (

    cod char(4),
    CPF_gerente char(11),
    total_desconto float CHECK ( total_desconto >= 0 ),
    valor_total_IPI float CHECK ( valor_total_IPI >= 0 ),
    CFOP char(4) not null,
    valor_total float CHECK ( valor_total >= 0),
    dia date not null,
    statos boolean default true,
    valor_frete float CHECK (valor_frete >= 0),
    primary key (cod),
    foreign key (CPF_gerente) references gerente (CPF)
);

-- TABELA DE FATURA

CREATE TABLE fatura (

    id char(4),
    cod_pedido_fornecedor char(4),
    data_vencimento date not null,
    valor_pago_atual float CHECK (valor_pago_atual > 0 ),
    valor_total_final float CHECK (valor_total_final > 0),
    data_emissao date not null,
    statos boolean default true,
    data_paga date,
    multa float,
    primary key (id),
    foreign key (cod_pedido_fornecedor) references pedido_fornecedor (cod)

);

-- TABELA DE NOTA FISCAL

CREATE TABLE nota_fiscal (

    NFE char(9),
    ICMS float not null,
    valor_total float not null CHECK ( valor_total > 0 ),
    valor_total_desconto float CHECK ( valor_total_desconto >= 0 ),
    dia date not null,
    valor_frete float,
    primary key (NFE)
);


-- TABELA DE PAGAMENTO

CREATE TABLE pagamento (

    cod char(4),
    id_fatura char(4),
    cod_compra char(4),
    cod_tipo_pagamento char(4),
    valor_pago float CHECK ( valor_pago > 0 ),
    dia date not null,
    statos boolean default true ,
    tipo_pagamento enum ('Cartao credito', 'Cartao Debito', 'Boleto'),
    primary key (cod),
    foreign key (id_fatura) references fatura (id),
    foreign key (cod_compra) references compra (cod),
    foreign key (cod_tipo_pagamento) references tipo_pagamento ( cod)

);


-- TABELA DA NOTA FISCAL FORNECEDOR

CREATE TABLE nota_fiscal_fornecedor (

    NFE char(9),
    cod_pagamento char(4),
    CFOP char(4) not null,
    IPI float,
    primary key(NFE),
    CONSTRAINT fk_ntfiscalfornecedor foreign key (NFE) references nota_fiscal (NFE) ON DELETE CASCADE ON UPDATE CASCADE,
    foreign key (cod_pagamento) references pagamento (cod)
 );

-- TABELA NOTA FISCAL COMPRA

CREATE TABLE nota_fiscal_compra (

    NFE char(9),
    cod_pagamento char(4),
    primary key (NFE),
    CONSTRAINT fk_ntfiscalcompra foreign key (NFE) references nota_fiscal (NFE) ON DELETE CASCADE ON UPDATE CASCADE,
    foreign key (cod_pagamento) references pagamento (cod)

);


-- TABELA DE CLIENTE

CREATE TABLE cliente (

    CPF char(11),
    seq_filial char(4),
    cnpj_matriz char(14),
    cep char(8) not null,
    cidade varchar(30),
    numero char(3),
    descricao varchar(30),
    valor_credito float,
    p_nome varchar(20) not null,
    m_nome varchar(20) ,
    u_nome varchar(30) not null,
    rg char(7) not null UNIQUE,
    senha varchar(12) not null default '123456',
    tem_clube_desconto boolean default false,
    data_cadastro date,
    email varchar(40) not null,
    data_nascimento date not null,
    primary key (CPF),
    foreign key (seq_filial , cnpj_matriz) references filial (seq , CNPJ_Matriz)

);


-- TABELA DE TELEFONE DE CLIENTE

CREATE TABLE telefone_cliente (

    CPF char(11) not null,
    telefone char(10),
    CONSTRAINT telefone_cliente_pk primary key (CPF, telefone),
    CONSTRAINT fk_telefonecliente foreign key (CPF) references cliente (CPF) ON DELETE CASCADE ON UPDATE CASCADE
);


-- TABELA DE SUGESTAO

CREATE TABLE sugestao (

    CPF_cliente char(11),
    id char(4),
    dia date,
    descricao varchar(30) not null,
    primary key (id , CPF_cliente),
    CONSTRAINT fk_sugestao foreign key (CPF_cliente) references cliente (CPF) ON DELETE CASCADE ON UPDATE CASCADE
);



-- TABELA DE RECLAMACAO

CREATE TABLE reclamacao (

    CPF_cliente char(11),
    id char(4),
    descricao varchar(30),
    motivo varchar(70),
    data_ocorrido date,
    data_reclamacao date,
    primary key (id, CPF_cliente),
    CONSTRAINT fk_reclamacao foreign key (CPF_cliente) references cliente (CPF) ON DELETE CASCADE ON UPDATE CASCADE
);

-- TABELA DE PROMOCAO

CREATE TABLE promocao (

    cod char(4),
    seq_filial char(4),
    cnpj_matriz char(14),
    nome varchar(30),
    data_inicio date not null,
    percentual_reducao float not null CHECK ( percentual_reducao > 0 ),
    data_fim date,
    obs varchar(30),
    descricao varchar(50),
    primary key (cod),
    foreign key (seq_filial, cnpj_matriz ) references filial (seq , CNPJ_Matriz)

);


-- TABELA DE ITEM PEDIDO

CREATE TABLE item_pedido (

    seq char(4),
    cod_produto_ref char(4),
    cod_pedido_fornecedor char(4)   ,
    quantidade int not null CHECK (quantidade > 0) ,
    preco_unitario float  not null CHECK (preco_unitario  > 0),
    primary key (cod_produto_ref , cod_pedido_fornecedor),
    key(seq),
    foreign key (cod_produto_ref) references produto_ref (cod),
    foreign key (cod_pedido_fornecedor) references pedido_fornecedor(cod)
);


-- TABELA DE LOTE

CREATE TABLE lote (

    cod char(4),
    cod_pedido_fornecedor char(4),
    descricao varchar(30),
    data_chegada date not null,
    primary key( cod) ,
    foreign key (cod_pedido_fornecedor) references pedido_fornecedor (cod)

);



-- TABELA DE ITEM DE ESTOQUE

CREATE TABLE item_estoque (

    cod_lote char(4),
    id_estoque char(4),
    cod_produto char(4),
    id_avaria char(4),
    id_prateleira char(4),
    data_validade date ,
    data_fabricacao date,
    data_entrada date not null,
    valor_compra float CHECK (valor_compra > 0),
    quantidade int CHECK (quantidade > 0),
    primary key (cod_lote, id_estoque , cod_produto),
    foreign key (cod_lote) references lote (cod),
    foreign key (id_estoque) references estoque (id),
    CONSTRAINT fk_produtoestoque foreign key ( cod_produto ) references produto_ref( cod) ON DELETE CASCADE ON UPDATE CASCADE,
    foreign key (id_avaria ) references avaria ( id),
    foreign key (id_prateleira ) references prateleira ( codigo)

);

-- TABELA DE PERDA DE PRODUTO

CREATE TABLE perda (

    seq char(4),
    cod_lote char(4),
    cod_produto char(4),
    id_estoque char(4),
    cpf_gerente char(11),
    dia date not null ,
    quantidade_perdida int CHECK (quantidade_perdida > 0) ,
    motivo varchar(80),
    primary key (seq , cod_lote , cod_produto , id_estoque),
    foreign key (cpf_gerente) references gerente (cpf),
    CONSTRAINT fk_perdaitestoq foreign key ( id_estoque ) references item_estoque(id_estoque ),
    CONSTRAINT fk_perdalote foreign key (cod_lote) references item_estoque (cod_lote),
    CONSTRAINT fk_perdaprod foreign key (cod_produto) references item_estoque (cod_produto)  ON DELETE CASCADE ON UPDATE CASCADE
);



-- TABELA DE RELACIONAMENTO TEM DE FUNCIONARIO

CREATE TABLE tem (

    id_jornada char(4),
    id_turno char(4),
    id_dia char(4),
    primary key (id_jornada , id_turno , id_dia ),
    foreign key (id_jornada) references jornadatrabalho(id),
    foreign key (id_turno) references turno(cod),
    foreign key (id_dia) references dia (sequencial)

);


-- TABELA RELACIONAMENTO REALIZA CURSO

CREATE TABLE realizacurso (

    cpf_fun char(11),
    id_curso char(4),
    dt_inicio date,
    dt_fim date,
    primary key (cpf_fun , id_curso),
    foreign key (cpf_fun) references funcionario(CPF),
    foreign key (id_curso) references curso (id)
);

-- TABELA DE GERENCIA DE ESTOQUE

CREATE TABLE gerencia_estoque (

    cpf_super_estoque char(11),
    id_estoque char(4),
    dt_fim date,
    dt_inicio date,
    primary key (cpf_super_estoque , id_estoque),
    foreign key (cpf_super_estoque) references SupervisorEstoque (CPF),
    foreign key (id_estoque) references estoque(id)

);