Congresso Nacional - Projeto de BD (11 questões) - Gabarito

Prévia do material em texto

Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 1 de 9 
Curso: Bacharelado em Sistemas de Informação 
Disciplina: 
Banco de Dados II 
Semestre: 
V 
Professor: 
Pablo Freire Matos 
Avaliação: 
Projeto de Banco de Dados 
Assunto: 
Modelo Lógico (modelo relacional) e Modelo Físico (SQL) 
Data: 
28/08/2019 
Aluno (a): 
Material do Professor 
Turma: 
Única 
Turno: 
Noturno 
 
Observação: As questões a seguir são relacionadas ao minimundo Congresso Nacional respondido em sala 
de aula. 
 
Minimundo: Congresso Nacional 
 
O congresso nacional necessita de um sistema de informação para armazenar suas informações. Atualmente, 
o controle é feito em papel, através das atas das sessões. As atas em papel não serão dispensadas, mas o 
sistema deverá auxiliar as tarefas do congresso. O sistema deve guardar o nome de cada congressista 
(prenome e sobrenome) e o sua matrícula (única). Um congressista pode ser eleito por estados diferentes a 
cada mandato, por isso armazena-se o estado que ele representa no momento e a data em que ele foi eleito. 
Guarda-se, além da representação atual, o histórico de eleições de cada candidato, indicando qual o estado 
que o elegeu e quantas vezes já foi eleito como representante daquele estado, para cada estado diferente. Dos 
estados, guarda-se o nome, a sigla e a região em que o estado se encontra (sul, sudeste, centro-oeste, norte e 
nordeste). 
 
Cada congressista é filiado a pelo menos um partido (mais de um quando se trata de uma coligação). 
Mantém-se o nome (único) e sigla (única) dos partidos e quando cada congressista filiou-se ao partido. Cada 
emenda constitucional ou projeto de lei proposto possui um nome, a data de votação, o resultado da votação 
(aprovado ou rejeitado). O sistema também armazena qual a opção de cada congressista na votação de uma 
emenda ou projeto de lei (a favor, contra, abstenção ou ausência). O sistema deverá facilitar a busca de 
emendas e projetos de lei através de busca por nome, palavras-chave e resumo. Além disso, deve apresentar 
estatísticas de acesso e de votações. 
 
1. Faça o mapeamento do Diagrama de Entidade-Relacionamento (DER) referente ao minimundo 
Congresso Nacional a seguir para o Modelo Relacional (MR) de acordo com o exemplo mostrado 
abaixo. 
 
Exemplo: 
 
 
 
 
Mapeamento 
Empregado (CPF_empregado, nome_empregado) 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 2 de 9 
DER no brModelo: Congresso Nacional 
 
Resposta: 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Congressista (codCongressista, prenome, sobrenome) 
Estado (sigla, nome, regiao) 
Eleito _por (codCongressista, sigla, dataEleito, periodoMandato) 
codCongressista referencia Congressista 
sigla referencia Estado 
Partido (sigla, nome) 
Filiado (codigo, sigla, data) 
codigo referencia Congressista 
sigla referencia Partido 
EmendaProjeto (codEmendaProjeto, nome, tipo, resultado, dataVotacao, codCongressista) 
codCongressista referencia Congressista 
PalChaveEmenda (codEmendaProjeto, palavraChave) 
codEmendaProjeto sigla referencia EmendaProjeto 
Vota (codEmendaProjeto, codCongressista) 
codEmendaProjeto sigla referencia EmendaProjeto 
codCongressista referencia Congressista 
 
 
 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 3 de 9 
2. Responda as questões a seguir de acordo com o resultado do mapeamento da Questão 1: 
 
a) Quantas tabelas são geradas? Quais são? 
8 tabelas = EmendaProjeto, PalavraChave, Congressita, Partido, Estado, Vota, Filiado, Eleito_Por 
b) Qual o total de chaves primárias? Informe quais são. 
13 chaves-primárias: 
 matricula em Congressista 
 codEmendaProjeto em EmendaProjeto 
 palavra_chave e codEmendaProjeto em Palavra_Chave_Emenda 
 sigla_partido em Partido 
 sigla_estado em Estado 
 codEmendaProjeto e matricula em Vota 
 matricula e sigla_partido em Filiado 
 data_eleito, matricula e sigla_estado em Eleito_Por 
 
c) Qual o total de chaves estrangeiras? Informe quais são. 
8 chaves-estrangeiras: 
 matricula em EmendaProjeto 
 codEmendaProjeto em Palavra_Chave_Emenda 
 codEmendaProjeto e matricula em Vota 
 matricula e sigla_partido em Filiado 
 matricula e sigla_estado em Eleito_Por 
 
d) Qual o total de chaves que são, ao mesmo tempo, primárias e estrangeiras? Informe quais são. 
7 chaves-primárias e chaves-estrangeiras ao mesmo tempo: 
 codEmendaProjeto em Palavra_Chave_Emenda 
 codEmendaProjeto e matricula em Vota 
 matricula e sigla_partido em Filiado 
 matricula e sigla_estado em Eleito_Por 
 
3. Implemente o MR no brModelo como mostrado a seguir, incluindo o tipo de dados de cada campo. 
Modelo Relacional no brModelo: Congresso Nacional 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 4 de 9 
 
a) Informar qual a quantidade de cada um dos tipos de dados existentes no modelo relacional. Não 
considerar as chaves estrangeiras na contagem. 
varchar: 11 date: 3 char: 2 mediumint: 1 tinyint: 1 
 
b) Informar quais os campos que têm valores preestabelecidos? Quais são estes valores preestabelecidos. 
opcao em vota: a favor – contra – abstencao – ausencia 
tipo em EmendaProjeto: emenda – projeto 
resultado em EmendaProjeto: aprovado – rejeitado 
regiao em Estado: centro-oeste – nordeste – norte – sudeste – sul 
 
4. Implemente o modelo físico (o banco de dados propriamente dito) como mostrado na figura a seguir. 
Atenção: Antes de criar o banco de dados, incluir as restrições de integridade no script de criação do banco 
de dados. As alterações necessárias estão listadas a seguir. 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 5 de 9 
 
Alterações necessárias no script de criação do banco de dados: 
a) Definir todos os campos com restrição NOT NULL, exceto as chaves primárias e estrangeiras, e o campo 
“resultado” da tabela “EmendaProjeto”. Escreve aqui um exemplo da restrição NOT NULL. 
prenome varchar(20) NOT NULL 
 
b) O campo “periodo_mandato” da tabela “Eleito_Por” tem como valor padrão o valor 4. Isto significa que 
se nenhum valor for inserido para o campo supracitado, este campo receberá por padrão o valor 4. Define 
o valor DEFAULT como 4 neste campo. 
periodo_mandato tinyint NOT NULL DEFAULT 4 
 
c) Para todos os campos que têm valores preestabelecidos (por exemplo, “tipo” da tabela 
“EmendaProjeto”), criar as regras de integridade para validar a inserção de somente estes valores no 
banco de dados. 
CHECK FUNCIONA NA VERSÃO ANTIGA DO MySQL 
CONSTRAINT ck_regiao CHECK (regiao IN (‘centro-oeste’, ‘nordeste’, ‘norte’, ‘sudeste’, ‘sul’)) 
 
ENHUM SUBSTITUIU O CHECK NA VERSÃO MAIS ATUAL DO MySQL 
regiao ENUM (‘centro-oeste’, ‘nordeste’, ‘norte’, ‘sudeste’, ‘sul’); 
d) Definir o campo “nome” da tabela “EmendaProjeto” com restrição UNIQUE. Se tentar inserir uma 
emenda/projeto com o mesmo nome. O que acontece? 
CONSTRAINT un_nome UNIQUE (nome) 
 
e) Definir o campo “codEmendaProjeto” da tabela “EmendaProjeto” como do tipo AUTO_INCREMENT. 
CREATE TABLE EmendaProjeto ( 
codEmendaProjeto mediumint AUTO_INCREMENT, 
CONSTRAINT pk_EmendaProjeto PRIMARY KEY (codEmendaProjeto) 
); 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 6 de 9 
5. Povoar (inserir dados) o banco de dados com o comando da Linguagem de Manipulação de Dados 
INSERT. Colocar os dados de acordo com os valores das tabelas a seguir. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 7 de 9 
6. Quais foram os comandos SQL utilizados para criar e usar o banco de dados Congresso Nacional? 
Para criar: CREATE DATABASE CongressoNacional; 
Para usar: USE CongressoNacional; 
7. Após o povoamento do banco de dados, faça o backup do banco de dados. Qual é o comando para fazer o 
backup? 
mysqldump–u root –p CongressoNacional > c:/congresso-backup.sql 
8. Em seguida, vamos realizar as seguintes consultas simples. A seguir, um exemplo de uma consulta para 
recuperar do banco de dados todos os empregados: Select * from Empregado. 
Agora é sua vez. 
a) Quais os congressistas cadastrados no banco de dados? 
SELECT * 
FROM congressista; 
b) Quais os congressistas cadastrados no banco de dados listados em ordem alfabética? 
SELECT * 
FROM congressista 
ORDER BY prenome, sobrenome; 
c) Quais os estados (sigla e nome) da região nordeste? 
SELECT sigla_estado, nome_estado 
FROM estado 
WHERE regiao=‘Nordeste’; 
d) Quais os partidos existentes? 
SELECT * 
FROM partido; 
e) Quantos os projetos existentes? 
SELECT COUNT(*) 
FROM EmendaProjeto 
WHERE tipo=”projeto”; 
f) Quais as Emendas/Projetos que foram votadas entre os anos 2010 e 2011? 
SELECT nome 
FROM EmendaProjeto 
WHERE YEAR(data_votacao) >=2010 AND YEAR(data_votacao) <= 2011; 
 
SELECT nome 
FROM EmendaProjeto 
WHERE YEAR(data_votacao) IN (2010, 2011); 
 
SELECT nome 
FROM EmendaProjeto 
WHERE YEAR(data_votacao) BETWEEN 2010 AND 2011; 
g) Quais os nomes dos partidos que têm sigla de apenas 3 caracteres? 
SELECT * 
FROM Partido 
WHERE sigla_partido LIKE ‘_ _ _ ‘; 
 
 
 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 8 de 9 
9. Mais consultas, porém avançadas. Estas consultas envolvem o uso do produto cartesiano. Utilize alias 
nas junções das tabelas e nos campos quando necessários. 
a) Quais os nomes (prenome e sobrenome) dos congressistas que são filiados ao partido PMDB? 
SELECT a.prenome, a.sobrenome 
FROM congressista a, filiado b 
WHERE b.sigla_partido=‘PMDB’ AND a.matricula=b.matricula; 
 
b) Quais os nomes (prenome e sobrenome) dos congressistas que filiaram este ano (2012)? 
SELECT a.prenome, a.sobrenome 
FROM congressista a, filiado b 
WHERE YEAR(b.data_filiacao)=‘2012’ AND a.matricula=b.matricula; 
 
c) Quais as matrículas dos congressistas que são filiados ao Partido dos Trabalhadores? 
SELECT a.matricula 
FROM congressista a, filiado b, partido c 
WHERE b.sigla_partido = c.sigla_partido AND c.nome_partido = ‘Partido 
dos Trabalhadores’ AND b.matricula = a.matricula; 
 
d) Qual a região, o período do mandato e a data que o congressista Cândido se elegeu? 
select a.regiao, b.periodo_mandato, b.data_eleito 
FROM estado a, eleito_por b, congressista c 
WHERE c.prenome = ‘Cândido’ AND b.matricula = c.mabricula and 
a.sigla_estado = b.sigla_estado; 
 
e) Qual o nome (prenome e sobrenome) do congressista que elaborou o projeto Lei de Informática? 
SELECT a.prenome, a.sobrenome 
FROM congressista a, emendaprojeto b 
WHERE b.nome = ‘Lei de Informática’ AND b.matricula = a.matricula; 
 
f) O projeto Lei de Informática foi aprovado? Dica: consultar a partir da tabela “Vota” e, em seguida, 
atualizar a tabela EmendaProjeto se o resultado for aprovado ou rejeitado. 
 Funciona: Código da Emenda Projeto informado manualmente. 
UPDATE emendaprojeto 
SET resultado = 'aprovado' 
WHERE codEmendaProjeto = 5 AND 
(SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = 5 AND opcao = 'a 
favor') > (SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = 5 AND opcao 
= 'contra'); 
 
UPDATE emendaprojeto 
SET resultado = 'rejeitado' 
WHERE codEmendaProjeto = 5 AND 
(SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = 5 AND opcao = 'a 
favor') < (SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = 5 AND opcao 
= 'contra'); 
 
 Não funciona, pois está selecionando dados da tabela EmendaProjeto ao mesmo tempo que está atualizando. 
UPDATE emendaprojeto 
Instituto Federal da Bahia – IFBA 
Campus Vitória da Conquista 
 
Página 9 de 9 
SET resultado = 'aprovado' 
WHERE codEmendaProjeto = 5 AND 
(SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = (SELECT 
codEmendaProjeto FROM EmendaProjeto WHERE nome = 'Lei de Informática') AND 
opcao = 'a favor') > (SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = 5 
AND opcao = 'contra'); 
 
 Funciona com o resultado da tabela EmendaProjeto atribuído a uma variável global. 
SET @v = 
(SELECT codEmendaProjeto 
 FROM EmendaProjeto 
 WHERE nome = 'Lei de Informática'); 
 
UPDATE emendaprojeto 
SET resultado = 'aprovado' 
WHERE codEmendaProjeto = (SELECT @v) AND 
(SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = (SELECT @v) AND opcao = 
'a favor') > (SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = (SELECT @v) 
AND opcao = 'contra'); 
 
UPDATE emendaprojeto 
SET resultado = 'reprovado' 
WHERE codEmendaProjeto = (SELECT @v) AND 
(SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = (SELECT @v) AND opcao = 
'a favor') < (SELECT COUNT(*) FROM vota WHERE codEmendaProjeto = (SELECT @v) 
AND opcao = 'contra'); 
 
 Realizar a Consulta após a Atualização 
SELECT resultado 
FROM emendaprojeto 
WHERE nome = 'Lei de Informática'; 
10. Quais os três comandos DDL (Linguagem de Definição de Dados)? Dê exemplo de cada um dos 
comandos DDL. 
CREATE TABLE Partido ( 
sigla_partido varchar(6) PRIMARY KEY, 
nome varchar(50) NOT NULL 
); 
ALTER TABLE Partido ADD PRIMARY KEY (sigla_partido); 
DROP TABLE Partido; 
11. Quais os quatros comandos DML (Linguagem de Manipulação de Dados)? Dê exemplo de cada um dos 
comandos DML. 
INSERT – INSERT INTO partido VALUES (“pv”, “partido verde”); 
DELETE – DELETE FROM eleito_por; 
UPDATE – UPDATE partido SET sigla_partido = “pv”; 
SELECT – SELECT * FROM congressistas; 
 
Bons Estudos!!!