Fundamentos de Bancos de Dados – 3aProva
Prof. Carlos A. Heuser
4 de julho de 2007
Duração: 2 horas – Prova com consulta
Questão 1 (Construção de modelo ER - Peso 3)
Deseja-se construir um sistema gestão de sítios WEB. Este sistema irá armazenar
o conteúdo de um sítio web.
Um sítio tem um nome, uma url e um número de identificação interna. Cada
sítio é composto por muitas páginas.
Cada página pertence a um sítio somente, é identificada por um número identificador e tem um nome.
As páginas podem ser de dois tipos, chamados custom e template.
No caso das páginas custom, o sistema simplesmente armazena o conteúdo da
página, na forma de string longo que contém o código HTML ou PHP da página,
juntamente com um sinalizador que indica a linguagem (HTM ou PHP). Não estão
previstas outras linguagens.
As páginas template são páginas com funcionalidade pré-determinada. Estas
páginas são usadas para acessar uma base de dados. Neste caso, o sistema deve
armazenar os dados necessários ao acesso da base de dados, que são o número
IP do servidor, o nome da base de dados, o nome do usuário do SGBD e a senha
de acesso. Por sua vez, as páginas template podem ser classificadas em dois tipos, as páginas que exibem dados do resultado de uma consulta e as páginas que
disponibilizam um formulário que permite que sejam feitas modificações em uma
tabela.
Para as páginas de consulta, o sistema armazena um comando SQL que monta
a consulta a exibir na página. A partir deste comando a página é montada dinamicamente cada vez que requisitada pelo usuário. Já para páginas de formulário
de modificação, o sistema armazena o nome da tabela que é alterada através do
formulário, bem como três indicadores que informam que tipo de modificações
(inclusão, exclusão e alteração) podem ser realizada através do formulário.
Além de armazenar o conteúdo do sítio, o sistema de gestão deve controlar o
acesso e a modificação de páginas. Para tal, o sistema deve manter um cadastro de
1
usuários, identificados por seu nome, tendo cada um uma senha. Para cada sítio
e para cada usuário, pode ser definido seu direito de acesso. O direito de acesso
pode ser "Acesso", que significa que o usuário somente pode acessar o sítio, ou
"Adm", que significa que ele também pode fazer modificações nas páginas do
sítio. Da mesma forma, para cada página e para cada usuário, também pode ser
definido o direito de acesso à página específica ("Adm"ou "Acesso").
Solução: A solução encontra-se na Figura 1 e está apresentada com a notação do
Power designer.
2
Direito site
direito acesso site I <M>
Sítio WEB
Relationship_2
Numero Sítio <pi> I
<M>
Nome Sítio
VA60 <M>
URL Sítio
VA60 <M>
Identifier_1
Relationship_5
<pi>
Usuario
Relationship_1
Nome usuário <pi> VA30 <M>
senha usuario
VA30 <M>
Identifier_1
<pi>
Página WEB
Nome da página
Número da página
Identifier_1
<pi> VMBT60 <M>
I
Relationship_4
<pi>
Relationship_3
Direito pagina
Inheritance_1
direito acesso pagina
Página custom
Conteúdo da página
linguagem da página
I <M>
Pagina template
LVA32000
A3
Servidor de BD
Nome do BD
usuario
senha
Identifier_1
A9
VA60
VA30
VA30
<M>
<M>
<M>
<M>
<pi>
Inheritance_2
forumario alteracoa
pagina consulta
Consulta SQL LVA1000 <M>
Identifier_1
<pi>
Nome da Tabela
Inclusao
Exclusao
Alteração
Figura 1: Modelo conceitual para a questão 1
3
VA60
BL
BL
BL
<M>
<M>
<M>
<M>
Usuário
Sessão frustrada
número interno usuário
nome do usuário
senha do usuário
Relationship_1
DataHora <pi> DT <M>
Identifier_1
<pi>
Identifier_1
<pi> I
<M>
VA30 <M>
VA30 <M>
<pi>
Relationship_2
Sessão realizada
início <pi> DT <M>
fim
DT
Página visitada
Ordem de visita
inicio da visita
fim da visita
Identifier_1
<pi>
Relationship_3
Identifier_1
<pi>
<pi> I
<M>
DT <M>
DT <M>
Relationship_4
Página
número da página
nome da página
I
<UNDEF>
Figura 2: Modelo conceitual para a questão 2
Questão 2 (Projeto de base de dados relacional - Peso 2,6)
Na figura 2, está apresentado um modelo ER parcial de um sistema de controle
de acesso a páginas WEB. A notação usada é no estilo engenharia de informação,
especificamente a do software Power Designer que vimos em aula. Os atributos estão anotados dentro dos retângulos representativos das entidades. Atributos
identificadores estão sublinhados. Relacionamentos são representados por linhas.
A cardinalidade mínima 0 é representada por um círculo, e a cardinalidade mínima
1 é representada por um pequeno traço. A cardinalidade máxima n é representada
por um pequeno triângulo, e a cardinalidade máxima 1 é representada pelas ausência deste triângulo. Relacionamentos identificadores têm um símbolo especial
(ver o relacionamento entre Usuário e Sessão realizada).
A entidade Usuário corresponde aos usuários que estão sendo controlados.
A entidade Sessão frustrada representa as sessões que não puderam ser
realizadas por senha incorreta. Já a entidade Sessão realizada corresponde
às sessões que efetivamente ocorreram. A entidade Página visitada informa as páginas que foram visitadas em uma sessão, sendo o atributo Ordem
de visita um número inteiro que representa a seqüencia de visita das páginas
do site. A entidade Página representa as páginas propriamente ditas.
Deve ser projetado o esquema (modelo lógico) de uma base de dados relacional para o modelo ER em questão. A base de dados deve refletir exatamente
o especificado no modelo conceitual. O esquema da base de dados relacional
deve conter os nomes das tabelas, os nomes dos atributos, atributos que formam
a chave primária e as chaves estrangeiras. Quando houver alternativas de projeto
4
dizer que alternativa foi usada. Pode ser usada a notação textual vista em aula para
representar esquemas relacionais.
Solução:
USUARIO ( NUMERO_INTERNO_USUARIO, NOME_DO_USUARIO,
SENHA_DO_USUARIO);
SESSAO_FRUSTRADA ( NUMERO_INTERNO_USUARIO,DATAHORA);
(NUMERO_INTERNO_USUARIO)
references USUARIO (NUMERO_INTERNO_USUARIO)
SESSAO_REALIZADA ( NUMERO_INTERNO_USUARIO,INICIO,
FIM);
(NUMERO_INTERNO_USUARIO)
references USUARIO (NUMERO_INTERNO_USUARIO)
PAGINA_VISITADA ( NUMERO_INTERNO_USUARIO,INICIO,ORDEM_DE_VISITA,
NUMERO_DA_PAGINA, INICIO_DA_VISITA, FIM_DA_VISITA);
(NUMERO_INTERNO_USUARIO, INICIO)
references SESSAO_REALIZADA (NUMERO_INTERNO_USUARIO, INICIO)
(NUMERO_DA_PAGINA)
references PAGINA (NUMERO_DA_PAGINA)
PAGINA (NUMERO_DA_PAGINA, NOME_DA_PAGINA);
5
Questão 3 (Engenharia reversa de BD relacional - Peso 2,6)
Abaixo está apresentado o esquema lógico de uma parte de uma base de dados
que mantém dados sobre pessoas relacionadas a uma universidade.
Execute um processo de engenharia reversa, construindo o modelo entidaderelacionamento correspondente a esta base de dados. O modelo ER deve refletir
exatamente esta base de dados, contendo entidades, atributos, relacionamentos
e cardinalidades máximas (quando possível), bem como os identificadores. O
modelo deve ser construído aplicando as regras apresentadas no livro.
Para representar o modelo ER use a notação do livro ou da ferramenta CASE
utilizada no trabalho.
Pessoa (NumeroCartao,NomePess,SexoPess,DataNascPess)
/* tabela com dados das pessoas */
Aluno (NumeroCartao, NoDeAnosNaEscolaPublica,
ResultadoENEM)
(NumeroCartao) referencia Pessoa
/* tabela com os dados dos alunos */
BolsasIC (NumeroCartao,AnoSemestre,CodOrgãoFinanciador)
(NumeroCartao) referencia Aluno
(CodOrgãoFinanciador) referencia OrgãoFin
/* tabela com dados sobre as bolsas de IC
que cada aluno teve*/
IngressoEmCurso (NumeroCartao,CodCr,DataIngresso,
DataFinaliza)
(NumeroCartao) referencia Aluno
(CodCr) referencia Curso
/* tabela com dados referentes aos ingressos dos alunos
nos cursos */
Curso (CodCr,NomeCurso,NivelCurso)
/* tabela com dados dos cursos*/
OrgãoFin (CodOrgãoFinanciador,NomeOrgãoFinanciador)
/* tabela com dados dos orgãos que financiam bolsas
de iniciação científica*/
Solução: O modelo conceitual gerado pela engenharia reversa encontra-se na figura 3. A entidade Ingresso em curso poderia ser um relacionamento n:n entre
6
Pessoa
Número do cartão
Nome da pessoa
Sexo da pessoa
Data de nascimento
Identifier_1
<pi> LI
VA60
A1
D
Orgão Financiador
<M>
<M>
<M>
<M>
Código do orgão
Nome do orgão
Identifier_1
<pi>
<pi> LI
<M>
VA60 <M>
<pi>
Inheritance_1
Relationship_2
Aluno
BolsaIC
Número de anos em esc publ
Resultado no ENEM
I <M>
F <M>
Relationship_1
Ano semestre I
Relationship_3
Curso
Ingresso em curso
Data de ingresso
Data de finalização
Identifier_1
<pi> D <M>
D
Relationship_4
<pi>
Código do curso <pi> LI
<M>
Nome do curso
VA60 <M>
Nível do curso
VA10 <M>
Identifier_1
<pi>
Figura 3: Modelo conceitual para a questão 3
Aluno e Curso.
7
Questão 4 (Normalização - Peso 1,8)
Considere a tabela abaixo, não necessariamente normalizada, referente à base de
dados da questão anterior.
Esta tabela foi obtida a partir de um documento que lista, para cada aluno:
1. seu número de cartão, seu nome, seu sexo e sua data de nascimento;
2. dados das bolsas IC que o aluno obteve, incluindo o ano semestre em que
teve bolsa, e o código e nome do órgão financiador da bolsa;
3. dados dos cursos nos quais ele ingressou, incluindo o código e o nome do
curso e a data de ingresso no mesmo.
Tabela (NumeroCartao,NomePess,SexoPess,DataNascPess,
(AnoSemestre,CodOrgãoFinanciador,
NomeOrgãoFinanciador),
(CodCr,DataIngresso, NomeCurso),
)
As dependências funcionais (podendo incluir dependências transitivas) que existem nesta tabela são as seguintes:
• (NumeroCartao)→NomePess
• (NumeroCartao)→SexoPess
• (NumeroCartao)→NomeDataNascPess
• (NumeroCartao,AnoSemestre)→CodOrgãoFinanciador
• (NumeroCartao,AnoSemestre)→NomeOrgãoFinanciador
• (CodOrgãoFinanciador)→NomeOrgãoFinanciador
• (CodCr)→NomeCurso
1. Diga em que forma normal encontra-se a tabela.
2. Caso a tabela não se encontre na terceira forma normal, mostre a transformação da tabela para a terceira forma normal. Mostre cada forma normal
intermediária, entre aquela em que a tabela se encontra e a terceira forma
normal.
Solução:
8
1. A tabela não se encontra na 1FN pois contém tabelas aninhadas.
2. 1FN: (eliminação das tabelas aninhadas)
Tab1 (NumeroCartao,NomePess,SexoPess,DataNascPess)
Tab2 (NumeroCartao,AnoSemestre,CodOrgãoFinanciador,
NomeOrgãoFinanciador)
Tab3 (NumeroCartao,CodCr,DataIngresso, NomeCurso)
2FN: (eliminação das dependências funcionais parciais)
Tab1 (NumeroCartao,NomePess,SexoPess,DataNascPess)
Tab2 (NumeroCartao,AnoSemestre,CodOrgãoFinanciador,
NomeOrgãoFinanciador)
Tab3 (NumeroCartao,CodCr,DataIngresso)
Tab4 (CodCr, NomeCurso)
3FN: (eliminação das dependências funcionais transitivas)
Tab1
Tab2
Tab3
Tab4
Tab5
(NumeroCartao,NomePess,SexoPess,DataNascPess)
(NumeroCartao,AnoSemestre,CodOrgãoFinanciador)
(NumeroCartao,CodCr,DataIngresso)
(CodCr, NomeCurso)
(CodOrgãoFinanciador, NomeOrgãoFinanciador)
9