CEA440-07-SQL-2

Prévia do material em texto

CEA 440– Banco de Dados I 
SQL 
 
 
Eduardo da Silva Ribeiro 
Universidade Federal de Ouro Preto 
Instituto de Ciências e Exatas e Aplicadas 
2 
SQL - Exemplo 
Banco de Dados I 
3 
SQL – Tabela DEPARTAMENTO 
Banco de Dados I 
1. set foreign_key_checks = 0; 
2. CREATE TABLE `departamento` 
3. ( 
4. `IDdepartamento` int(5) unsigned zerofill NOT NULL 
 auto_increment, 
5. `nomedep` varchar(60) NOT NULL, 
6. `IDgerente` int(5) unsigned zerofill NOT NULL, 
7. `datainicio` date NOT NULL, 
8. PRIMARY KEY (`IDdepartamento`), 
9. UNIQUE KEY `IDgerente` (`IDgerente`), 
10. CONSTRAINT `departamento_ibfk_1` FOREIGN KEY 
 (`IDgerente`) REFERENCES `empregado` (`IDempregado`) ON 
 UPDATE CASCADE 
11.) 
12.ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 
COMMENT = ’Armazena dados sobre o departamento’ ; 
4 
SQL – Tabela DEPARTAMENTO 
Banco de Dados I 
• 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 10 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 não foi criada e se foreign_key_checks não 
tiver sido alterado para 0, o comando retornaria um erro. 
•As outras 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. 
5 
SQL – Tabela DEPARTAMENTO 
Banco de Dados I 
• 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 9, que 
garante que esta restrição seja obedecida. 
•Cabe chamar atenção aqui para a diretiva CONSTRAINT na 
linha 10, 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 12 a expressão DEFAULT CHARSET=latin1 determina 
como serão feitos os tratamentos de caracteres nesta tabela. 
6 
SQL – Tabela DEPENDENTE 
Banco de Dados I 
1. CREATE TABLE IF NOT EXISTS `dependente` 
2. ( 
3. `IDdependente` int(5) unsigned zerofill NOT NULL 
 auto_increment, 
4. `IDempregado` int(5) unsigned zerofill NOT NULL, 
5. `nomedepende` varchar(60) NOT NULL, 
6. `sexo` enum(‘M’,'F’) NOT NULL, 
7. `nascimento` date NOT NULL, 
8. `parentesco` varchar(60) NOT NULL, 
9. PRIMARY KEY (`IDdependente`), 
10. UNIQUE KEY `IDempregado` (`IDempregado`,`nomedepende`), 
11. CONSTRAINT `dependente_ibfk_1` FOREIGN KEY 
 (`IDempregado`) REFERENCES `empregado` (`IDempregado`) 
 ON DELETE CASCADE ON UPDATE CASCADE 
12.) 
13.ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3; 
7 
SQL – Tabela DEPENDENTE 
Banco de Dados I 
• 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 10 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). 
8 
SQL – Tabela EMPREGADO 
Banco de Dados I 
1. CREATE TABLE `empregado` 
2. ( 
3. `IDempregado` int(5) unsigned zerofill NOT NULL 
 auto_increment, 
4. `pnome` varchar(20) NOT NULL, 
5. `meio` varchar(20) NOT NULL, 
6. `sobrenome` varchar(20) NOT NULL, 
7. `nascimento` date NOT NULL, 
8. `sexo` enum(‘M’,'F’) NOT NULL, 
9. `endereco` varchar(100) NOT NULL, 
10. `salario` float(10,2) NOT NULL, 
11. `IDdepartamento` int(5) unsigned zerofill default NULL, 
12. `IDsupervisor` int(5) unsigned zerofill default NULL, 
13. PRIMARY KEY (`IDempregado`), 
14. KEY `IDdepartamento` (`IDdepartamento`), 
15. KEY `IDsupervisor` (`IDsupervisor`), 
16. CONSTRAINT `empregado_ibfk_2` FOREIGN KEY (`IDsupervisor`) 
 REFERENCES `empregado` (`IDempregado`) ON UPDATE CASCADE, 
17. CONSTRAINT `empregado_ibfk_1` FOREIGN KEY 
 (`IDdepartamento`) REFERENCES `departamento` 
 (`IDdepartamento`) ON UPDATE CASCADE 
18.) 
19. ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COMMENT=’armazena 
dados sobre os funcionarios’ 
9 
SQL – Tabela EMPREGADO 
Banco de Dados I 
• A novidade desta tabela é a utilização do campo do tipo ENUM, na 
linha 8. 
• 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 11 foi criado o campo (IDepartamento) 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. 
10 
SQL – Tabela LOCALIZA_DEP 
Banco de Dados I 
1. CREATE TABLE `localiza_dep` 
2. ( 
3. `IDlocaliza` int(5) unsigned zerofill NOT NULL 
 auto_increment, 
4. `IDdepartamento` int(5) unsigned zerofill NOT NULL, 
5. `localizacao` varchar(60) NOT NULL, 
6. PRIMARY KEY (`IDlocaliza`), 
7. UNIQUE KEY `Iddepartamento` 
 (`IDdepartamento`,`localizacao`), 
8. CONSTRAINT `localiza_dep_ibfk_1` FOREIGN KEY 
 (`IDdepartamento`) REFERENCES `departamento` 
 (`IDdepartamento`) ON UPDATE CASCADE 
9. ) 
10.ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 
COMMENT=’Armazena dados sobre a localizacao do 
departamento’; 
11 Banco de Dados I 
• 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 sequencial 
automática (auto_increment) e garantir com o índice UNIQUE a 
restrição de unicidade entre departamento e localização. 
SQL – Tabela LOCALIZA_DEP 
12 
SQL – Tabela PROJETO 
Banco de Dados I 
1.CREATE TABLE IF NOT EXISTS `projeto` 
2.( 
3. `IDprojeto` int(5) unsigned zerofill NOT 
 NULL auto_increment,4. `IDdepartamento` int(5) unsigned zerofill 
 NOT NULL, 
5. `nomeproj` varchar(60) NOT NULL, 
6. `localizacao` varchar(60) NOT NULL, 
7. PRIMARY KEY (`IDprojeto`), 
8. KEY `IDdepartamento` (`IDdepartamento`) 
9.) ENGINE=InnoDB DEFAULT CHARSET=latin1 
AUTO_INCREMENT=5; 
13 
SQL – Tabela PROJ_EMP 
Banco de Dados I 
1. a CREATE TABLE `proj_emp` 
2. ( 
3. `IDproj_emp` int(5) unsigned zerofill NOT NULL 
 auto_increment, 
4. `IDempregado` int(5) unsigned zerofill NOT NULL, 
5. `IDprojeto` int(5) unsigned zerofill NOT NULL, 
6. `horas` time NOT NULL, 
7. PRIMARY KEY (`IDproj_emp`), 
8. UNIQUE KEY `IDempregado_2` (`IDempregado`,`IDprojeto`), 
9. CONSTRAINT `proj_emp_ibfk_1` FOREIGN KEY 
 (`IDempregado`) REFERENCES `empregado` (`IDempregado`) 
 ON UPDATE CASCADE, 
10. CONSTRAINT `proj_emp_ibfk_2` FOREIGN KEY (`IDprojeto`) 
 REFERENCES `projeto` (`IDprojeto`) ON UPDATE CASCADE 
11. ) 
12. ENGINE=InnoDB DEFAULT CHARSET=latin1; 
14 Banco de Dados I 
• 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. 
SQL – Tabela Localiza_DEP