Aula 3 – 7º Sem. – 05/04/2018 – (GPS) GPS – BB

#
# Structure for table “orcamento”
#

CREATE TABLE `orcamento` (
`IdOrcamento` int(11) NOT NULL AUTO_INCREMENT,
`IdRecurso` int(11) DEFAULT NULL,
`Data` date DEFAULT NULL,
`Total` float(10,2) DEFAULT NULL,
`Situacao` varchar(10) DEFAULT NULL,
PRIMARY KEY (`IdOrcamento`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Data for table “orcamento”
#

#
# Structure for table “itens_orcamento”
#

CREATE TABLE `itens_orcamento` (
`IdIten` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(50) DEFAULT NULL,
`Qtde` int(11) DEFAULT NULL,
`Valor` float(10,2) DEFAULT NULL,
`Tipo` varchar(30) DEFAULT NULL COMMENT ‘Pessoal / Serv Terceiro / Mat permanente / Mat consumo’,
`Situacao` varchar(20) DEFAULT NULL COMMENT ‘Orcado / Executado’,
`IdProjeto` int(11) DEFAULT NULL,
`Total_unitario` varchar(255) DEFAULT NULL,
`IdOrcamento` int(11) DEFAULT NULL,
PRIMARY KEY (`IdIten`),
KEY `idx_projeto1` (`IdProjeto`),
KEY `idx_idorcamento` (`IdOrcamento`),
CONSTRAINT `idx_idorcamento` FOREIGN KEY (`IdOrcamento`) REFERENCES `orcamento` (`IdOrcamento`),
CONSTRAINT `idx_projeto1` FOREIGN KEY (`IdProjeto`) REFERENCES `messierc_gps`.`projeto` (`IdProjeto`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “itens_orcamento”
#

#
# Structure for table “portfolio”
#

CREATE TABLE `portfolio` (
`IdPortfolio` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(100) DEFAULT NULL,
`Status` varchar(20) DEFAULT NULL COMMENT ‘Ativo/Inativo’,
`IdRecurso_responsavel` int(11) DEFAULT NULL,
`Patrocinador` varchar(100) DEFAULT NULL,
`OrcamentoTotal` float(10,2) DEFAULT NULL,
`Observacao` varchar(255) DEFAULT NULL,
PRIMARY KEY (`IdPortfolio`),
KEY `idx_recurso` (`IdRecurso_responsavel`),
CONSTRAINT `idx_recurso` FOREIGN KEY (`IdRecurso_responsavel`) REFERENCES `messierc_gps`.`recurso` (`IdRecurso`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “portfolio”
#

#
# Structure for table “programa”
#

CREATE TABLE `programa` (
`IdPrograma` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(100) DEFAULT NULL,
`IdPortfolio` int(11) DEFAULT NULL,
`IdRecurso_responsavel` int(11) DEFAULT NULL,
`Situacao` varchar(20) DEFAULT NULL COMMENT ‘Ativo / Inativo’,
`Observacao` varchar(255) DEFAULT NULL,
PRIMARY KEY (`IdPrograma`),
KEY `idx_portfolio` (`IdPortfolio`),
KEY `idx_recurso1` (`IdRecurso_responsavel`),
CONSTRAINT `idx_portfolio` FOREIGN KEY (`IdPortfolio`) REFERENCES `messierc_gps`.`portfolio` (`IdPortfolio`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `idx_recurso1` FOREIGN KEY (`IdRecurso_responsavel`) REFERENCES `messierc_gps`.`recurso` (`IdRecurso`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#
# Data for table “programa”
#

#
# Structure for table “projeto”
#

CREATE TABLE `projeto` (
`IdProjeto` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(100) DEFAULT NULL,
`DataInicio` date DEFAULT NULL,
`DataFim` date DEFAULT NULL,
`AprovadoPor` varchar(100) DEFAULT NULL,
`ValorOrcado` float DEFAULT NULL,
`ValorExecutado` float(10,2) DEFAULT NULL,
`NivelMaturidade` varchar(20) DEFAULT NULL,
`IdPortfolio` int(11) DEFAULT NULL,
`IdPrograma` int(11) DEFAULT NULL,
`IdRecurso_responsavel` int(11) DEFAULT NULL COMMENT ‘Pessoa responsável’,
`IdTAP` int(11) DEFAULT NULL,
`Situacao` varchar(20) DEFAULT NULL COMMENT ‘Ativa/Inativo’,
`Aprovado` varchar(1) DEFAULT NULL COMMENT ‘Sim / Nao’,
`Observacao` varchar(255) DEFAULT NULL,
PRIMARY KEY (`IdProjeto`),
KEY `idx_portfolio1` (`IdPortfolio`),
KEY `idx_programa` (`IdPrograma`),
KEY `idx_recurso2` (`IdRecurso_responsavel`),
KEY `idx_tap` (`IdTAP`),
CONSTRAINT `idx_portfolio1` FOREIGN KEY (`IdPortfolio`) REFERENCES `messierc_gps`.`portfolio` (`IdPortfolio`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `idx_programa` FOREIGN KEY (`IdPrograma`) REFERENCES `messierc_gps`.`programa` (`IdPrograma`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `idx_recurso2` FOREIGN KEY (`IdRecurso_responsavel`) REFERENCES `messierc_gps`.`recurso` (`IdRecurso`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `idx_tap` FOREIGN KEY (`IdTAP`) REFERENCES `messierc_gps`.`tap` (`IdTAP`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “projeto”
#

#
# Structure for table “recurso”
#

CREATE TABLE `recurso` (
`IdRecurso` int(11) NOT NULL AUTO_INCREMENT,
`Nome` varchar(100) DEFAULT NULL,
`Cargo` varchar(50) DEFAULT NULL,
`Celular` varchar(20) DEFAULT NULL,
`Email` varchar(50) DEFAULT NULL,
`Observacao` varchar(255) DEFAULT NULL,
PRIMARY KEY (`IdRecurso`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#
# Data for table “recurso”
#

#
# Structure for table “situacao”
#

CREATE TABLE `situacao` (
`IdSituacao` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(20) DEFAULT NULL,
PRIMARY KEY (`IdSituacao`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “situacao”
#

#
# Structure for table “swot”
#

CREATE TABLE `swot` (
`IdSWOT` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(200) DEFAULT NULL,
`Tipo` varchar(1) DEFAULT NULL COMMENT ‘Força / Oportunidades / Fraquesas / Ameaças’,
`Peso` int(11) DEFAULT NULL,
`IdProjeto` int(11) DEFAULT NULL,
PRIMARY KEY (`IdSWOT`),
KEY `idx_swot` (`IdProjeto`),
CONSTRAINT `idx_swot` FOREIGN KEY (`IdProjeto`) REFERENCES `projeto` (`IdProjeto`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “swot”
#

#
# Structure for table “tap”
#

CREATE TABLE `tap` (
`IdTAP` int(11) NOT NULL AUTO_INCREMENT,
`Descricao` varchar(100) DEFAULT NULL,
`IdRecurso_responsavel` int(11) DEFAULT NULL,
`IdRecurso_patrocinador` int(11) DEFAULT NULL,
`LocalExecucao` varchar(50) DEFAULT NULL,
`DataInicio` date DEFAULT NULL,
`DataFim` date DEFAULT NULL,
`ValorEstimado` float(10,2) DEFAULT NULL,
`Escopo` varchar(200) DEFAULT NULL,
`NaoEscopo` varchar(200) DEFAULT NULL,
`EquipeDoProjeto` varchar(200) DEFAULT NULL,
`Requisitos` varchar(200) DEFAULT NULL,
`Justificativa` varchar(200) DEFAULT NULL,
`Observacao` varchar(200) DEFAULT NULL,
PRIMARY KEY (`IdTAP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#
# Data for table “tap”
#

#
# Structure for table “tarefas”
#

CREATE TABLE `tarefas` (
`IdTarefas` int(11) NOT NULL AUTO_INCREMENT,
`Titulo` varchar(100) DEFAULT NULL,
`Descricao` varchar(100) DEFAULT NULL,
`Data_Inicio` date DEFAULT NULL,
`Hora_Inicio` time DEFAULT NULL,
`Data_Fim` date DEFAULT NULL,
`Hora_Fim` time DEFAULT NULL,
`Recorrencia` varchar(1) DEFAULT NULL,
`Periodo` varchar(1) DEFAULT NULL,
`Completato` int(11) DEFAULT NULL,
`IdRecurso` int(11) DEFAULT NULL,
`IdProjeto` int(11) DEFAULT NULL,
`IdSituacao` int(11) DEFAULT NULL,
`Observacao` text,
PRIMARY KEY (`IdTarefas`),
KEY `TESTE` (`IdProjeto`),
CONSTRAINT `TESTE` FOREIGN KEY (`IdProjeto`) REFERENCES `projeto` (`IdProjeto`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#
# Data for table “tarefas”
#

#
# Structure for table “usuario”
#

CREATE TABLE `usuario` (
`IdUsuario` int(11) NOT NULL AUTO_INCREMENT,
`Login` varchar(20) DEFAULT NULL,
`Password` varchar(10) DEFAULT NULL,
`Ativo` varchar(1) DEFAULT NULL COMMENT ‘Sim / Nao’,
`Administrador` varchar(1) DEFAULT NULL COMMENT ‘Sim / Nao’,
`Email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`IdUsuario`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

#
# Data for table “usuario”
#