Banco de dados 2 - A1 - Luiz Antônio Vivacqua

Prévia do material em texto

Banco de Dados II – CIÊNCIA DA COMPUTAÇÂO 
 
Prof. Luiz Antônio Vivacqua C. Meyer 
 
Atividade Individual Avaliativa – Prazo de entrega: 18-04-2020 
 
 
 
1ª Questão (3,0 Pontos) 
 
Considere o modelo conceitual abaixo, que representa um mini-mundo de 
uma empresa, e responda em SQL os itens abaixo: 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1) Forneça os comandos SQL para criar as tabelas que implementam o 
modelo especificando para cada tabela somente as restrições de chave 
primária. (1,0) 
 
• CREATE TABLE PROJETO (CODIGO INTEGER PRIMARY KEY, CIDADE VARCHAR (30), VERBA 
VARCHAR (30)); 
 
• CREATE TABLE FUNCIONARIO (MATRICULA INTEGER PRIMARY KEY, NOME VARCHAR (30), 
CARGO VARCHAR (15), DATA_ADM VARCHAR (10)); 
 
• CREATE TABLE ALOCACAO (ID_ALOC INTEGER PRIMARY KEY, ID_PROJ INTEGER, ID_FUNC 
INTEGER, DATA_INICIO VARCHAR (10), DATA_FIM VARCHAR (10)); 
 
2) Implemente todas as restrições de chave estrangeira do modelo. (0,5) 
 
• ALTER TABLE ALOCACAO ADD CONSTRAINT FK_PROJ FOREIGN KEY (ID_PROJ) REFERENCES 
PROJETO (CODIGO); 
 
• ALTER TABLE ALOCACAO ADD CONSTRAINT FK_FUNC FOREIGN KEY (ID_FUNC) REFERENCES 
FUNCIONARIO (MATRICULA); 
 
 
3) Crie uma restrição para validar o domínio (maior que zero) da coluna verba. 
(0,25) 
 
• ALTER TABLE PROJETO ADD CONSTRAINT CHK_VERBA CHECK (VERBA > 0); 
 
 
 
4) Crie um índice para cada chave estrangeira. (0,50) 
 
• CREATE INDEX IND_PROJ ON ALOCACAO (ID_PROJ); 
 
• CREATE INDEX IND_FUNC ON ALOCACAO (ID_FUNC); 
 
5) Adicione uma nova coluna na tabela Alocação para informar a quantidade 
de horas que o funcionário trabalhou no projeto. (0,25) 
 
• ALTER TABLE ALOCACAO ADD QUANTIDADE_HORAS INTERGER; 
 
6) Elimine os índices criados no item 4. (0,25) 
 
• DROP INDEX IND_PROJETO; 
 
• DROP INDEX IND_FUNCIONARIO; 
 
7) Elimine as tabelas criadas. (0,25) 
 
• DROP TABLE ALOCACAO; 
 
• DROP TABLE PROJETO; 
 
• DROP TABLE FUNCIONARIO; 
 
 
 
2ª Questão (1,5 Pontos) 
 
Considere o banco de dados criado na 1ª questão e responda em 
álgebra relacional as consultas abaixo: 
 
1) Qual é o código e a verba dos projetos em Brasília. 
 
• πCÓDIGO, VERBA (σ CIDADE = “BRASÍLIA” (PROJETO)); 
 
2) Qual é o nome e o cargo dos funcionários alocados em algum projeto. 
 
 
• R1 = σ ID_FUNC = MATRICULA (ALOCACAO X FUNCIONARIO) 
 R2 = σ ID_PROJ = CODIGO (R1 X PROJETO) 
 R3 = π NOME, CARGO (R2) 
 
 
3) Qual é o código, cidade e verba dos projetos sem nenhuma alocação. 
 
• R1 = πCODIGO (PROJETO) 
 R2 = π ID_ALOC (ALOCACAO) 
 R3 = R1 – R2 
 R4 = π CODIGO, CIDADE, VERBA (R3) 
 
 
 
 
 
 
 
4) Qual é a nome dos funcionários que tem o cargo GERENTE e que 
estão alocados em projetos na cidade SÃO PAULO. 
 
• R1= σ CARGO = “GERENTE” (FUNCIONARIO); 
 R2 = σ CIDADE= “SÃO PAULO” (PROJETO) 
 R3 = σ ID_PROJETO = CÓDIGO (R2 X ALOCACAO) 
 R4 = σ ID_FUNCIONARIO = MATRICULA (R3 X R1) 
 R5 = π NOME (R4); 
 
 
 
5) Qual é a matrícula e o nome dos funcionários alocados a todos os projetos. 
 
• R1 = πID_ALOC (ALOCACAO); 
 R2 = πCODIGO (PROJETO) 
 R3 = R1 – R2 
 R4 = ID_FUNCIONARIO = MATRICULA (R3 X FUNCIONARIO) 
 R5 = πMATRICULA, NOME (R4); 
 
 
3ª Questão (Inspirado na questão 21 do ENADE 2008 – 
Bacharelado em Ciência da Computação) (05, Ponto) 
 
Considere a relação ALUNO (Matrícula, RG, nome, sobrenome, nascimento, 
endereço), em que o atributo grifado corresponde à chave primária da relação. 
Suponha que se deseje realizar as seguintes consultas: 
 
1) Listar o nome dos alunos com sobrenome Silva; 
 
2) Listar o nome dos alunos em ordem crescente de seus sobrenomes. 
 
 
Em relação à definição de um índice sobre o atributo sobrenome para 
melhorar o desempenho das consultas acima, que tipo de índice você 
escolheria implementar: índice convencional (árvore B+) ou índice Hash? 
Justifique a sua resposta. 
 
• ÍNDICE CONVENCIONAL ÁRVORE B+. NESSA QUESTÃO ELE É O MAIS ADEQUADO E 
EFETUA O MELHOR DESEMPENHO PARA AS DUAS CONSULTAS ACIMA. POIS ELA OTIMIZA 
CONSULTAS POR IGUALDADE, DESIGUALDADE E COM ORDENAÇÃO. 
 
NA PRIMEIRA CONSULTA ONDE DIZ LISTAR O NOME DOS ALUNO COM O SOBRENOME 
SILVA CORRESPONDE A UMA CONSULTA DE IGUALDADE SOBRE O ATRIBUTO 
SOBRENOME. 
 
NA SEGUNDA CONSULTA ONDE SE PEDE PARA LISTAR O NOME DOS ALUNOS EM ORDEM 
CRESCENTE CORRESPONDE A UMA ORDENAÇÃO SOBRE O ATRIBUTO SOBRENOME.