Prova de BD Unisinos

Prévia do material em texto

060423	
  	
  	
  	
  Banco	
  de	
  Dados	
  I	
   	
  1/4	
  
Cód:	
  060423	
   Atividade	
  Acadêmica:	
  Banco	
  de	
  Dados	
  I	
  
Professor(es):	
  DENISE	
  BANDEIRA	
  DA	
  SILVA	
  
Data:	
  27/09/2014	
   Grau:	
  	
  B	
   	
  
	
  
Orientações	
  gerais	
  para	
  realização	
  da	
  avaliação:	
  
1.	
  A	
  avaliação	
  é	
  individual;	
  	
  
2.	
  Deve	
  ser	
  utilizada	
  caneta	
  azul	
  ou	
  preta	
  para	
  responder	
  as	
  questões;	
  	
  
3.	
  O	
  aluno	
  deve	
  preencher	
  o	
  seu	
  nome	
  completo	
  no	
  espaço	
  indicado	
  no	
  cabeçalho	
  das	
  folhas.	
  
4.	
  A	
  avaliação	
  deverá	
  ser	
  realizada:	
  	
  __X__Com	
  consulta	
  	
  	
  	
  	
  ____	
  Sem	
  consulta	
  
Material	
  autorizado:	
  é	
  permitida	
  a	
  utilização	
  de	
  uma	
  folha	
  A4,	
  construída	
  pelo	
  aluno,	
  frente	
  e	
  verso.	
  Não	
  é	
  
permitido	
  o	
  compartilhamento	
  de	
  materiais	
  entre	
  os	
  alunos.	
  
	
  
Questão	
  1	
  (1,5	
  ponto)	
  
Como	
   você	
   identificaria	
   cada	
   um	
   dos	
   seguinte	
   componentes	
   do	
   modelo	
   Entidade	
   Relacionamento	
   em	
   um	
   Diagrama	
   ER.	
  
Relacione	
  os	
  conceitos	
  da	
  coluna	
  da	
  esquerda	
  com	
  os	
  da	
  coluna	
  da	
  direita.	
  Na	
  folha	
  de	
  respostas,	
  preencha	
  a	
  grade	
  de	
  resposta	
  	
  
com	
  as	
  letras	
  de	
  “a”	
  até	
  “g”	
  na	
  mesma	
  ordem	
  em	
  que	
  seriam	
  preenchidas	
  as	
  lacunas	
  da	
  coluna	
  da	
  direita.	
  
(	
  a	
  )	
  Entidade	
   (	
  b	
  )	
  	
  
	
  
(	
  b	
  )	
  Relacionamento	
  Binário	
  1:N	
   (	
  c	
  )	
  
	
  
(	
  c	
  )	
  Atributo	
  Multivalorado	
   (	
  d	
  )	
  
	
   	
  
(	
  d	
  )	
  Relacionamento	
  Binário	
  N:N	
   (	
  g	
  )	
  
	
  
(	
  e	
  )	
  Atributo	
  Composto	
   (	
  a	
  )	
  
	
  
(	
  f	
  )	
  Atributo	
  Identificador	
   (	
  f	
  )	
  
	
  
(	
  g	
  )	
  Relacionamento	
  Binário	
  1:1	
   (	
  e	
  )	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
	
  
060423	
  	
  	
  	
  Banco	
  de	
  Dados	
  I	
   	
  2/4	
  
Questão	
  2	
  (3,0	
  pontos)	
  
	
  Considerando	
  o	
  seguinte	
  projeto	
  físico	
  de	
  banco	
  de	
  dados,	
  descrito	
  em	
  SQL:	
  
Create table PRESIDENTE ( 
id_presidente integer not null, 
sobrenome varchar(50), 
nome varchar(50), 
partido char(5), 
estado char(2), 
constraint pk_PRESIDENTE primary key(id_presidente) 
); 
 
Create table MANDATO ( 
id_mandato integer not null, 
ano_inicio integer, 
ano_fim integer, 
constraint pk_MANDATO primary key(id_mandato) 
); 
 
Create table PRESIDENTE_MANDATO ( 
id_presidente integer not null, 
id_mandato integer not null, 
data_inicio_no_cargo date, 
data_fim_no_cargo date, 
forma_ingresso varchar(10) CHECK (forma_ingresso IN ('VOTO', 'SUCESSAO')), 
constraint pk_PRESIDENTE_MANDATO primary key(id_presidente, id_mandato), 
constraint fk1_PRESIDENTE_MANDATO foreign key(id_presidente) references PRESIDENTE, 
constraint fk2_PRESIDENTE_MANDATO foreign key(id_mandato) references MANDATO 
); 
 
Conjunto	
  de	
  dados	
  exemplo	
  para	
  o	
  banco	
  de	
  dados	
  acima:	
  
 
PRESIDENTE 
 
 
MANDATO 
 
PRESIDENTE_MANDATO 
 
 
	
  
	
  
	
  
	
  
	
  
	
  
	
  
060423	
  	
  	
  	
  Banco	
  de	
  Dados	
  I	
   	
  3/4	
  
Desenvolva	
  as	
  expressões	
  SQL	
  que	
  resolvem	
  os	
  problemas	
  propostos	
  nos	
  itens	
  a	
  seguir.	
  Coloque	
  as	
  respostas,	
  que	
  
são	
  as	
  expressões	
  SQL	
  que	
  resolvem	
  o	
  problema,	
  na	
  folha	
  de	
  respostas	
  da	
  prova.	
  
(a) Liste	
  o	
  nome,	
  sobrenome	
  e	
  o	
  estado	
  de	
  origem	
  dos	
  presidentes	
  que	
  são	
  do	
  'PT'.	
  
SELECT P.nome, P.sobrenome, P.estado 
FROM presidente P 
WHERE P.partido = 'PT'; 
(b) Liste	
  o	
  nome	
  e	
  sobrenome	
  dos	
  presidentes	
  do	
  Brasil	
  entre	
  os	
  anos	
  de	
  1990	
  até	
  2003,	
  em	
  ordem	
  alfabética.	
  
SELECT DISTINCT P.nome, P.sobrenome 
FROM presidente P, mandato M, presidente_mandato PM 
WHERE P.id_presidente = PM.id_presidente AND M.id_mandato = PM.id_mandato AND 
M.ano_inicio >= 1990 AND M.ano_fim <= 2003 
ORDER BY P.nome, P.sobrenome; 
(c) Liste	
  o	
  nome	
  e	
  sobrenome	
  dos	
  presidentes	
  e	
  o	
  número	
  de	
  mandatos	
  que	
  cada	
  um	
  exerceu.	
  
SELECT P.nome, P.sobrenome, COUNT(*) 
FROM presidente P, presidente_mandato PM 
WHERE P.id_presidente = PM.id_presidente 
GROUP BY P.id_presidente, P.nome, P.sobrenome; 
	
  
Questão	
  3	
  (1,5	
  ponto)	
  
Considerando	
  o	
  mesmo	
  projeto	
  físico	
  de	
  banco	
  de	
  dados	
  da	
  questão	
  anterior,	
  desenvolva	
  uma	
  expressão	
  da	
  Álgebra	
  
Relacional	
   que	
   resolva	
   o	
   seguinte	
   problema.	
   Coloque	
   a	
   resposta,	
   que	
   é	
   a	
   expressão	
   da	
   Álgebra	
   Relacional	
   que	
  
resolve	
  o	
  problema,	
  na	
  folha	
  de	
  respostas	
  da	
  prova.	
  
(a) Liste	
  o	
  nome	
  e	
  sobrenome	
  dos	
  presidentes	
  do	
  Brasil	
  entre	
  os	
  anos	
  de	
  1990	
  até	
  2003.	
  
π nome, sobrenome ( σ ano_inicio >= 1990	
  AND ano_fim <= 2003 ( Presidente |X| Presidente_Mandato |X| Mandato ) ) 
Questão	
  4	
  (1,5	
  pontos)	
  
Apresente	
  o	
  DER	
  completo	
  (Entidades,	
  Relacionamentos,	
  Cardinalidades	
  e	
  Atributos)	
  para	
  o	
  projeto	
  do	
  banco	
  de	
  
dados	
  da	
  Segunda	
  Questão.	
  
	
  
	
  
	
  
	
  
060423	
  	
  	
  	
  Banco	
  de	
  Dados	
  I	
   	
  4/4	
  
Questão	
  5	
  (2,5	
  ponto)	
  
Considerando	
  os	
  seguintes	
  dados	
  que	
  permitem	
  a	
  gestão	
  de	
  projetos:	
  
GESTÃO_PROJETOS	
  (EMPREGADO,	
  NOME_EMPREGADO,	
  PROJETO,	
  GERENTE_PROJETO,	
  HORAS_TRABALHADAS,	
  PRECO_HORA,	
  GERENTE_TELF)	
  
A	
  seguir	
  é	
  apresentado	
  um	
  conjunto	
  de	
  dados	
  exemplo:	
  
EMPREGADO	
   NOME_EMPREGADO	
   PROJETO	
   GERENTE_PROJETO	
   HORAS_TRABALHADAS	
   PRECO_HORA	
   GERENTE_TELF	
  
1-­‐100	
   Ana	
  Maria	
   BFD	
   Jorge	
  Oliveira	
   80	
   35.50	
   5087	
  
1-­‐100	
   Ana	
  Maria	
   LOGIS	
   Maria	
  Correia	
   45	
   35.00	
   5098	
  
1-­‐102	
   Pedro	
  Silva	
   CRONUS	
   Marco	
  Alves	
   80	
   40.00	
   4367	
  
1-­‐103	
   João	
  Abreu	
   LOGIS	
   Maria	
  Correia	
   45	
   30.00	
   5098	
  
e	
  mais	
  as	
  seguintes	
  informações:	
  
• o	
  preço	
  hora	
  de	
  um	
  empregado	
  não	
  depende	
  do	
  projeto	
  
• um	
  projeto	
  tem	
  apenas	
  um	
  gerente	
  de	
  projeto	
  
• um	
  empregado	
  pode	
  participar	
  em	
  vários	
  projetos	
  
• para	
  um	
  determinado	
  projeto	
  há	
  um	
  número	
  de	
  telefone,	
  que	
  é	
  o	
  do	
  gerente	
  do	
  projeto	
  
Normalize	
  até	
  a	
  3FN,	
  passando	
  pela	
  1FN	
  e	
  2FN.	
  Apresente,	
  na	
   folha	
  de	
   respostas	
  da	
  prova,	
   as	
   tabelas,	
   com	
  suas	
  
chaves,	
  na	
  1FN,	
  depois	
  na	
  2FN	
  e,	
  por	
  fim,	
  na	
  3FN.	
  Em	
  cada	
  FN,	
  apresentecada	
  tabela	
  da	
  seguinte	
  forma:	
  
1FN	
  
Tabela	
  Empregado	
  
EMPREGADO	
  	
  	
  NOME_EMPREGADO	
  	
  	
  PROJETO	
  	
  	
  GERENTE_PROJETO	
  	
  	
  HORAS_TRABALHADAS	
  	
  	
  PRECO_HORA	
  	
  	
  GERENTE_TELF	
  
Chave	
  primária:	
  EMPREGADO,	
  PROJETO	
  
	
  
2FN	
  
Tabela	
  Empregado	
  
EMPREGADO	
  	
  	
  NOME_EMPREGADO	
  	
  	
  PRECO_HORA	
  
Chave	
  primária:	
  EMPREGADO	
  
	
  
Tabela	
  Projeto	
  
PROJETO	
  	
  	
  GERENTE_PROJETO	
  	
  	
  GERENTE_TELF	
  
Chave	
  primária:	
  PROJETO	
  
	
  
Tabela	
  Empregado_Projeto	
  
EMPREGADO	
  	
  	
  PROJETO	
  	
  	
  HORAS_TRABALHADAS	
  
Chave	
  primária:	
  EMPREGADO,	
  PROJETO	
  
Chave	
  estrangeira:	
  EMPREGADO	
  referencia	
  Empregado,	
  PROJETO	
  referencia	
  Projeto	
  
	
  
3FN	
  
Tabela	
  Empregado	
  
EMPREGADO	
  	
  	
  NOME_EMPREGADO	
  	
  	
  PRECO_HORA	
  
Chave	
  primária:	
  EMPREGADO	
  
	
  
Tabela	
  Projeto	
  
PROJETO	
  	
  	
  	
  GERENTE_PROJETO	
  
Chave	
  primária:	
  PROJETO	
  
Chave	
  estrangeira:	
  GERENTE_PROJETO	
  referencia	
  Gerente	
  
	
  
Tabela	
  Gerente	
  
GERENTE_PROJETO	
  	
  	
  GERENTE_TELF	
  
Chave	
  primária:	
  GERENTE_PROJETO	
  
	
  
Tabela	
  Empregado_Projeto	
  
EMPREGADO	
  	
  	
  PROJETO	
  	
  	
  HORAS_TRABALHADAS	
  
Chave	
  primária:	
  EMPREGADO,	
  PROJETO	
  
Chaves	
  estrangeiras:	
  EMPREGADO	
  referencia	
  Empregado,	
  PROJETO	
  referencia	
  Projeto