UFSM > Ciência da Computação > ELC119 - Fundamentos de Bancos de Dados

Objetivo

O objetivo deste exercício é praticar o uso dos seguintes recursos oferecidos pela linguagem SQL: restrições de integridade, visões e gatilhos (triggers).

Preparação

• Conecte-se ao servidor linux03, que já possui o HSQLDB instalado.
• Baixe o código SQL para criação do banco de dados: empresa2.sql
• Baixe o programa exemplo: TestTrigger.java

Exercícios

Restrições

1. Execute a interface gráfica de gerenciamento do HSQLDB:

   java -cp /opt/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing \
   -url jdbc:hsqldb:mem -driver org.hsqldb.jdbcDriver
   

   A opção -url jdbc:hsqldb:mem significa que o banco de dados só existirá em memória.

2. Execute o código empresa2.sql no HSQLDB para criação do banco de dados.

3. Liste os nomes de todos os dependentes cadastrados, mostrando a relação de parentesco e o nome do empregado relacionado:

   SELECT dependente.nome AS nome_dependente, 
          dependente.relacao, 
          empregado.nome AS empregado 
   FROM empregado,dependente 
   WHERE dependente.codEmp = empregado.codEmp
   

4. Tente eliminar do banco de dados o empregado de código 3. Por que essa operação provoca um erro?

5. Altere a regra de integridade na tabela "dependente" (remova a restrição anterior e adicione a nova restrição):

   ALTER TABLE dependente 
   DROP CONSTRAINT dependente_empregado_fk;
   

   ALTER TABLE dependente 
   ADD CONSTRAINT dependente_empregado_fk FOREIGN KEY (codEmp) 
   REFERENCES empregado(codEmp) 
   ON DELETE CASCADE ON UPDATE CASCADE;
   

6. Execute novamente o comando SQL para eliminar o empregado de código 3.

7. Execute novamente a consulta abaixo e veja o que aconteceu devido à alteração na regra de integridade:

   SELECT dependente.nome AS nome_dependente, 
          dependente.relacao, 
          empregado.nome AS empregado 
   FROM empregado,dependente 
   WHERE dependente.codEmp = empregado.codEmp
   

8. Acrescente a seguinte restrição de integridade:

   ALTER TABLE empregado 
   ADD CONSTRAINT testchk CHECK(salario > 0);
   

9. Teste a restrição inserindo o seguinte registro:

   INSERT INTO empregado VALUES (10,'Fulano','1998-01-01','Endereco','M',0,null,1)
   

10. Insira uma restrição na tabela "trabalhaEm", para que horas trabalhadas em projetos não excedam 20h. Por que esse comando provoca um erro?

Visões

1. Crie uma visão que junte as tabelas "empregado", "projeto" e "trabalhaEm":

   CREATE VIEW trabalhadores AS
   SELECT empregado.nome, projeto.titulo 
   FROM empregado, projeto, trabalhaEm
   WHERE empregado.codEmp = trabalhaEm.codEmp 
   AND   trabalhaEm.codProj = projeto.codProj;
   

2. Use esta visão para obter os nomes dos empregados que trabalham no projeto denominado "Business Intelligence".

3. Crie uma visão sobre a tabela "empregado" que permita esconder o campo salário.

Gatilhos

1. Compile e execute o programa exemplo:

   javac -cp /opt/hsqldb/lib/hsqldb.jar TestTrigger.java
   java -cp /opt/hsqldb/lib/hsqldb.jar:. TestTrigger
   

2. Analise o código do programa exemplo (TestTrigger.java) e descubra qual é o gatilho usado.

3. Crie um novo gatilho no programa para atualizar o total de salários do departamento quando um empregado é removido do banco de dados. Para isso, você terá que alterar os métodos "main" e "fire".

4. Teste o novo gatilho, removendo um empregado do banco de dados.

Material de apoio

• SQL Tutorial
  Tutorial SQL, com demonstração on-line e guia de referência.

• HSQLDB User Guide
  Guia do usuário do HSQLDB.

• Interface Trigger
  Documentação da interface Trigger do HSQLDB.