O banco de dados empresa

Nas últimas aulas discutimos a respeito de modelagem e projetos de banco de dados, além de fazer apresentação da parte da SQL responsável pela definição de banco de dados e tabelas. Usamos como base a proposta de banco de dados para uma empresa encontrada no livro Sistema de Banco de Dados de Elmasri/Navathe 4ª edição.

A proposta desta postagem é mostrar o banco de dados que pode ser gerado a partir do Diagrama Entidade-Relacionamento abaixo. Foi utilizado o sistema de gerenciamento de Banco de dados MySQL na versão 5.023 para a definição das tabelas. A sintaxe de alguns comandos pode variar um pouco dependendo da versão do banco que for utilizado para gerar o mesmo banco

set foreign_key_checks = 0;

Comentário: A variável foreign_key_checks determina se as restrições de chave estrangeira devem ou não ser aplicadas. Este é um dos recursos que pode-se aplicar quando se quer criar um banco de dados baseado na exportação de uma estrutura já pronta. Veja que na linha 8 da definição da tabela departamento criamos uma chave estrangeira para a tabela de empregado, entretanto, no momento de definição desta tabela, a tabela empregado ainda nao foi criada e se foreign_key_checks não tiver sido alterado para 0, o comando retornaria um erro. As outras formas formas seriam: 1. Ordenar a criação das tabelas de maneira que uma tabela que seja origem de uma chave estrangeira seja criada antes da tabela que seja destino desta chave estrangeira. Seria um trabalho monstruoso dependendo do tamanho do seu banco de dados. 2. Criar as tabelas inicialmente sem as restrições e, depois de tudo pronto, alterar as mesmas, inserindo as restrições necessárias.

--

-- Estrutura da tabela `departamento`

--

1. CREATE TABLE `departamento` (

2. `IDdepartamento` int(5) unsigned zerofill NOT NULL auto_increment,

3. `nomedep` varchar(60) NOT NULL,

4. `IDgerente` int(5) unsigned zerofill NOT NULL,

5. `datainicio` date NOT NULL,

6. PRIMARY KEY (`IDdepartamento`),

7. UNIQUE KEY `IDgerente` (`IDgerente`),

8. CONSTRAINT `departamento_ibfk_1` FOREIGN KEY (`IDgerente`) REFERENCES `empregado` (`IDempregado`) ON UPDATE CASCADE

9. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COMMENT='Armazena dados sobre o departamento'

Comentarios:

A tabela departamento possui uma chave estrangeira com origem na tabela empregado, com cardinalidade 1:1. Por isso a criação do índice do tipo UNIQUE, na linha 7, que garante que esta restrição seja obedecida.

Cabe chamar atenção aqui para a diretiva CONSTRAINT na linha 8, que permite criar um nome para as restrições. Uma aplicação prática para a criação de um nome para a restrição seria a utilização deste identificador para tratamentos de erros. Note ainda que a opção ON DELETE RESTRICT pode ser omitida pois, a exclusão de uma linha que possua uma chave estrangeira é impedida por padrão.

Na linha 9 a expressão DEFAULT CHARSET=latin1 determina como serão feitos os tratamentos de caracteres nesta tabela.

-- --------------------------------------------------------

--

-- Estrutura da tabela `dependente`

--

1. CREATE TABLE IF NOT EXISTS `dependente` (

2. `IDdependente` int(5) unsigned zerofill NOT NULL auto_increment,

3. `IDempregado` int(5) unsigned zerofill NOT NULL,

4. `nomedepende` varchar(60) NOT NULL,

5. `sexo` enum('M','F') NOT NULL,

6. `nascimento` date NOT NULL,

7. `parentesco` varchar(60) NOT NULL,

8. PRIMARY KEY (`IDdependente`),

9. UNIQUE KEY `IDempregado` (`IDempregado`,`nomedepende`),

10. CONSTRAINT `dependente_ibfk_1` FOREIGN KEY (`IDempregado`) REFERENCES `empregado` (`IDempregado`) ON DELETE CASCADE ON UPDATE CASCADE

11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Comentários:

No diagrama ER a entidade dependente é indicada como sendo fraca e, assim sendo, deveria-se ter usado como chaves da tabela a chave estrangeira de empregado (IDempregado) e a chave fraca de dependente. Mas, para fins mais práticos, esta abordagem costuma ser evitada, pois se tivermos que relacionar esta tabela com alguma outra, teríamos então que migrar as duas chaves. Novamente pode-se lançar mão do recurso de criar uma chave UNIQUE para manter as restrições indicadas no diagrama ER. No caso da linha 9 foi possível fazer com que os nomes dos dependentes não se repetissem para cada funcionário. (Detalhe: se o empregado tivesse um cônjuge e um filho(a) com o mesmo nome, ele não poderia ser cadastrado)

-- --------------------------------------------------------

--

-- Estrutura da tabela `empregado`

--

1. CREATE TABLE `empregado` (

2. `IDempregado` int(5) unsigned zerofill NOT NULL auto_increment,

3. `pnome` varchar(20) NOT NULL,

4. `meio` varchar(20) NOT NULL,

5. `sobrenome` varchar(20) NOT NULL,

6. `nascimento` date NOT NULL,

7. `sexo` enum('M','F') NOT NULL,

8. `endereco` varchar(100) NOT NULL,

9. `salario` float(10,2) NOT NULL,

10. `IDdepartamento` int(5) unsigned zerofill default NULL,

11. `IDsupervisor` int(5) unsigned zerofill default NULL,

12. PRIMARY KEY (`IDempregado`),

13. KEY `IDdepartamento` (`IDdepartamento`),

14. KEY `IDsupervisor` (`IDsupervisor`),

15. CONSTRAINT `empregado_ibfk_2` FOREIGN KEY (`IDsupervisor`) REFERENCES `empregado` (`IDempregado`) ON UPDATE CASCADE,

16. CONSTRAINT `empregado_ibfk_1` FOREIGN KEY (`IDdepartamento`) REFERENCES `departamento` (`IDdepartamento`) ON UPDATE CASCADE

17. ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT='armazena dados sobre os funcionarios'

Comentários:

A novidade desta tabela é a utilização do campo do tipo ENUM, na linha 7. Este tipo permite controlar os valores a serem recebidos pelo campo. Neste caso, o campo sexo poderá receber apenas os valores M ou F. É uma sugestão interessante para o campo parentesco da tabela dependente tendo em vista que possui um número bem limitado de valores, ajudando a manter controle sobre as informações lançadas, possibilitando maior acuracidade as consultas.

Na linha 10 foi criado o campo que servirá de chave estrangeira para a tabela Departamento. Note que o valor padrão do campo é NULL. Isto foi feito para que não caíssemos em um deadlock: a tabela Departamento possui uma chave estrangeira para Empregado (IDgerente) que não pode ser nula. Neste caso, se ambas as chaves estrangeiras fossem NOT NULL teríamos que cadastrar um empregado antes de cadastrar um departamento e vice versa.

O mesmo ocorre para a linha, onde temos um auto-relacionamento de empregado com empregado.

-- --------------------------------------------------------

--

-- Estrutura da tabela `localiza_dep`

--

1. CREATE TABLE `localiza_dep` (

2. `IDlocaliza` int(5) unsigned zerofill NOT NULL auto_increment,

3. `IDdepartamento` int(5) unsigned zerofill NOT NULL,

4. `localizacao` varchar(60) NOT NULL,

5. PRIMARY KEY (`IDlocaliza`),

6. UNIQUE KEY `IDdepartamento` (`IDdepartamento`,`localizacao`),

7. CONSTRAINT `localiza_dep_ibfk_1` FOREIGN KEY (`IDdepartamento`) REFERENCES `departamento` (`IDdepartamento`) ON UPDATE CASCADE

8. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COMMENT='Armazena dados sobre a localizacao do apartamento'

Comentários:

Esta tabela precisou ser criada para indicar as diversas localizações de um departamento. No DER esta tabela era representada por um atributo multivalorado. Usamos aqui a mesma estratégia da tabela dependente de criarmos uma chave primária com numeração seqüencial automática (auto_increment) e garantir com o índice UNIQUE a restrição de unicidade entre departamento e localização.

-- --------------------------------------------------------

--

-- Estrutura da tabela `projeto`

--

CREATE TABLE IF NOT EXISTS `projeto` (

`IDprojeto` int(5) unsigned zerofill NOT NULL auto_increment,

`IDdepartamento` int(5) unsigned zerofill NOT NULL,

`nomeproj` varchar(60) NOT NULL,

`localizacao` varchar(60) NOT NULL,

PRIMARY KEY (`IDprojeto`),

KEY `IDdepartamento` (`IDdepartamento`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Sem comentários relevantes

-- --------------------------------------------------------

--

-- Estrutura da tabela `proj_emp`

--

1. CREATE TABLE `proj_emp` (

2. `IDproj_emp` int(5) unsigned zerofill NOT NULL auto_increment,

3. `IDempregado` int(5) unsigned zerofill NOT NULL,

4. `IDprojeto` int(5) unsigned zerofill NOT NULL,

5. `horas` time NOT NULL,

6. PRIMARY KEY (`IDproj_emp`),

7. UNIQUE KEY `IDempregado_2` (`IDempregado`,`IDprojeto`),

8. CONSTRAINT `proj_emp_ibfk_1` FOREIGN KEY (`IDempregado`) REFERENCES `empregado` (`IDempregado`) ON UPDATE CASCADE,

9. CONSTRAINT `proj_emp_ibfk_2` FOREIGN KEY (`IDprojeto`) REFERENCES `projeto` (`IDprojeto`) ON UPDATE CASCADE

10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comentários:

Esta tabela é o resultado do relacionamento M:N entre projeto e empregado. A estratégia das tabelas dependente e localizacao se repete e o índice UNIQUE nos ajuda a manter a restrição de integridade.