bdUrbanFix, MySQL - rextester

bdUrbanFix

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

CREATE TABLE IF NOT EXISTS `_UB_tbPessoas` (
    `cpf` varchar(14) NOT NULL,
    `nome` varchar(30) NOT NULL,
    `sobrenome` varchar(50) NOT NULL,
    `telefone` varchar(15) NOT NULL,
    `email` varchar(50) NOT NULL,
    `numeroRelatos` int(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`cpf`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbBeneficio` (
    `cpfDoador` varchar(14) NOT NULL, 
    `cpfReceptor` varchar(14) NOT NULL,
    PRIMARY KEY (`cpfDoador`, `cpfReceptor`),
    FOREIGN KEY (`cpfDoador`) REFERENCES `_UB_tbPessoas` (`cpf`),
    FOREIGN KEY (`cpfReceptor`) REFERENCES `_UB_tbPessoas` (`cpf`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbEnderecos` (
    `cep` varchar(9) NOT NULL,
    `endereco` varchar(50) NOT NULL COMMENT 'rua, avenida, estrada, etc.',
    `bairro` varchar(50) NOT NULL,
    `cidade` varchar(30) NOT NULL,
    `uf` char(2) NOT NULL,
    PRIMARY KEY (`cep`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbPessoaEndereco`(
    `cpf` varchar(14) NOT NULL,
    `cep` varchar(9) NOT NULL,
    `numero` int(11),
    `complemento` varchar(50),
    PRIMARY KEY (`cpf`, `cep`),
    FOREIGN KEY (`cpf`) REFERENCES `_UB_tbPessoas` (`cpf`),
    FOREIGN KEY (`cep`) REFERENCES `_UB_tbEnderecos` (`cep`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbTipoProblema` (
    `codTipo` int NOT NULL AUTO_INCREMENT,
    `descricao` varchar(100) NOT NULL,
    PRIMARY KEY (`codTipo`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbNotificacoes` (
    `codNotificacao` int NOT NULL AUTO_INCREMENT,
    `situacao` varchar(30),
    `previsaoSolucao` datetime,
    `codTipoProblema` int NOT NULL,
    PRIMARY KEY (`codNotificacao`),
    FOREIGN KEY (`codTipoProblema`) REFERENCES `_UB_tbTipoProblema` (`codTipo`)
);
 
CREATE TABLE IF NOT EXISTS `_UB_tbChamado` (
    `codChamado` int(11) NOT NULL AUTO_INCREMENT,
    `dataAbertura` datetime NOT NULL,
    `visualizacoes` int(11) NOT NULL,
    `nomeFoto` varchar(100),
    `codTipoProblema` int(11) NOT NULL,
    `cpf` varchar(14) NOT NULL,
    `codNotificacao` int(11) NOT NULL,
    `cep` varchar(9) NOT NULL,
    # NOVOS CAMPOS
    `visualicacao` int NOT NULL DEFAULT 0 COMMENT 'Flag de visualização: 0 = viu, 1 = nao viu', 
    `dataHoraVizualizacao` DATETIME NOT NULL DEFAULT '0000-00-00',
    #############
    PRIMARY KEY (`codChamado`),
    FOREIGN KEY (`codTipoProblema`) REFERENCES `_UB_tbTipoProblema` (`codTipo`),
    FOREIGN KEY (`cpf`) REFERENCES `_UB_tbPessoas` (`cpf`),
    FOREIGN KEY (`codNotificacao`) REFERENCES `_UB_tbNotificacoes` (`codNotificacao`),
    FOREIGN KEY (`cep`) REFERENCES `_UB_tbEnderecos` (`cep`)
);

# NOVAS TABELAS
CREATE TABLE IF NOT EXISTS `_UB_tbSugestao` (
    `codSugestao` int NOT NULL AUTO_INCREMENT,
    `dataSugerida` datetime NOT NULL,
    `codTipoProblema` int(11) NOT NULL,
    `descSugestao` varchar(255) NOT NULL,
    `nomeFoto` varchar(100) NOT NULL,
    PRIMARY KEY (`codSugestao`),
    FOREIGN KEY (`codTipoProblema`) REFERENCES `_UB_tbTipoProblema` (`codTipo`)
);
###############

SHOW TABLES;


run \| edit \| history \| help	0