Fundamentos de Bancos de Dados – Prova 3
Prof. Carlos A. Heuser
22 de agosto de 2003
Duração: 2 horas – Prova com consulta
Questão 1 (Construção de modelo ER - Peso 3)
Deseja-se construir uma base de dados para armazenar informações sobre a produção científica de um conjunto de pesquisadores.
Para cada pesquisador é necessário saber seu nome e a instituição em que
trabalha (somente a atual), juntamente com as publicações de sua autoria. A instituição tem como identificador uma sigla. Além disso, a base de dados devem
manter o nome e país da instituição. O pesquisador é identificado pela instituição
e por um número seqüencial que começa em "1"para cada instituição.
Cada publicação é identificada por um número e tem um título. Para cada
publicação é necessário saber, ainda, que outras publicações ela referencia. Cabe
observar que uma publicação pode ser referenciada por várias outras publicações.
Há dois tipos de publicações, os trabalhos em anais de congressos e os artigos em
periódicos.
Para os trabalhos em anais de congressos, a base de dados deve armazenar,
além do congresso em que ocorreu a publicação, os números inicial e final de
página do artigo dentro dos anais. Cada congresso é identificado por uma sigla e
pelo ano em que ocorreu e é necessário conhecer o nome e o local em que ocorreu
o congresso.
Já para os artigos em periódicos, além dos números de página inicial e final,
deseja-se saber o volume e o número da edição do periódico em que o artigo foi
publicado. Cada periódico tem um identificador único, uma editora e um nome.
Deve ser construído um modelo conceitual para esta base de dados. O modelo
conceitual deve ser construído na forma de um modelo entidade relacionamento,
com a notação usada em aula. Este modelo deve conter, ao menos, entidades,
relacionamentos, atributos, identificadores e generalizações/especializações. A
base de dados modelada não deve conter redundâncias de dados.
Solução: A solução encontra-se na Figura 1
Questão 2 (Projeto de base de dados relacional - Peso 2,6)
Na figura 2 está apresentado um modelo ER parcial de uma sistema que armazena
1
referenciado
0-N
Referência
referenciador
0-N
Publicação
Número publicação
título
página inicial
página final
id: Número publicação
1-N
Autoria
0-N
Pesquisador
número sequencial
nome
id: R.Institutição
número sequencial
1-1
conceitual/1
R
0-N
Artigo periódico
volume
número
Artigo anais
1-1
1-1
R_1
R_2
0-N
0-N
Congresso
sigla congresso
ano
nome
local
id: sigla congresso
Periódico
identificador periódico
editora
nome
id: identificador periódico
Figura 1: Modelo conceitual para a questão 1
2
Institutição
Sigla Institutição
Nome
País
id: Sigla Institutição
Aluno
Código aluno
Nome aluno
id: Código aluno
conceitual/1
0-N
Mat_Alu
1-1
Matrícula
id: Mat_Tur.Turma
Mat_Alu.Aluno
0-N
Nota questão
Nota
0-N
Questão
Número da questão
Peso Questão
id: Prova Questão.Prova
Número da questão
1-1
1-1
Mat_Tur
Prova Questão
0-N
0-N
Turma
Sigla da Turma
Código da disciplina
Ano Semestre
id: Sigla da Turma
Código da disciplina
Ano Semestre
0-N
Truma Prova
Prova
Numero da prova
Data da prova
1-1
Peso prova
id: Truma Prova.Turma
Numero da prova
Figura 2: Modelo conceitual para a questão 2
resultados de provas de um curso de nível superior. O modelo está construído com
a notação da ferramenta DBMain, usada para os estudos de caso. Esta notação
diferencia-se daquela usada no livro nos seguintes aspectos:
• As cardinalidades estão na notação européia (o contrário da usada no livro).
• Os atributos estão listados dentro do símbolo que representa a entidade/relacionamento.
• Os identificadores aparecem na cláusula "id", dentro do símbolo representativo da entidade.
Deve ser projetado o esquema (modelo lógico) de uma base de dados relacional
para o modelo ER em questão. 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 dizer que
alternativa foi usada.
Aluno(Codigo_aluno,Nome_aluno)
Turma(Sigla_da_Turma,Codigo_da_disciplina,Ano_Semestre)
Matricula(Sigla_da_Turma, Codigo_da_disciplina,
Ano_Semestre, Codigo_aluno)
3
(Codigo_aluno) referencia Aluno
(Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre)
referencia Turma
Prova (Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre,
Numero_da_prova,Data_da_prova,Peso_prova,)
(Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre)
referencia Turma
Questao (Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre,
Numero_da_prova, Numero_da_questao,Peso_Questao)
(Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre,
Numero_da_prova)
referencia Prova;
Nota_questao (Numero_da_prova, Numero_da_questao,Sigla_da_Turma,
Codigo_da_disciplina, Ano_Semestre,Codigo_aluno
,Nota)
(Sigla_da_Turma, Codigo_da_disciplina,
Ano_Semestre, Codigo_aluno)
referencia Matricula
(Sigla_da_Turma, Codigo_da_disciplina, Ano_Semestre,
Numero_da_prova, Numero_da_questao)
referencia Questao
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
de acadêmica. Execute um processo de engenharia reversa, construíndo o modelo
entidade-relacionamento correspondente a esta base de dados. O modelo ER deve
refletir exatamente esta base de dados, contendo entidades, atributos, relacionamentos e cardinalidades, bem como identificadores.
create table Disciplina (
CodDisc char(6) not null,
NomeDisc varchar(100) not null,
CreditosDisc numeric(2) not null,
primary key (CodDisc));
create table Turma (
CodDisc char(6) not null,
AnoSem numeric(3) not null,
SiglaTur char(1) not null,
primary key (CodDisc, AnoSem, SiglaTur));
create table Horario (
CodDisc char(6) not null,
4
AnoSem numeric(3) not null,
SiglaTur char(1) not null,
DiaSem numeric(1) not null,
HoraInicio numeric(4) not null,
NumHoras numeric(2) not null,
primary key (CodDisc, AnoSem, SiglaTur, DiaSem,
HoraInicio));
create table Curso (
CodCur numeric(3) not null,
NomeCur varchar(100) not null,
primary key (CodCur));
create table CursoGraduacao (
CodCur numeric(3) not null,
EstagCurricSimNao char not null,
primary key (CodCur));
create table CursoPos (
CodCur numeric(3) not null,
Nivel numeric(1) not null,
NumeroCreditos char(1) not null,
primary key (CodCur));
create table DiscCurso (
CodCur numeric(3) not null,
CodDisc char(6) not null,
Carater char(1) not null,
primary key (CodCur, CodDisc));
alter table Turma add constraint FKR
foreign key (CodDisc)
references Disciplina;
alter table Horario add constraint FKR_1
foreign key (CodDisc, AnoSem, SiglaTur)
references Turma;
alter table CursoGraduacao add constraint FKCur_Cur
foreign key (CodCur)
references Curso;
alter table CursoPos add constraint FKR
foreign key (CodCur)
references Curso;
alter table DiscCurso add constraint FKDis_Dis
foreign key (CodDisc)
references Disciplina;
5
Disciplina
CodDisc
NomeDisc
CreditosDisc
id: CodDisc
0-N
R
1-1
Turma
AnoSem
SiglaTur
id: R.Disciplina
AnoSem
SiglaTur
0-N
0-N
conceitual/1
DiscCurso
Caráter
R_1
1-1
0-N
Horario
DiaSem
HoraInicio
NumHoras
id: R_1.Turma
DiaSem
HoraInicio
Curso
CodCur
NomeCur
id: CodCur
CursoGraduacao
EstagCurricSimNao
CursoPos
Nivel
NumeroCreditos
Figura 3: Modelo conceitual para a questão 3
alter table DiscCurso add constraint FKDis_Cur
foreign key (CodCur)
references Curso;
Solução: O modelo conceitual gerado pela engenharia reversa encontra-se, na
notação do DBMain, na figura 3.
Questão 4 (Normalização - Peso 1,8)
Considere a seguinte tabela, não necessariamente normalizada, referente a base
de dados da questão anterior:
Tab3(CodDisc,AnoSem,SiglaTur, HoraInicio,
NumHoras,NomeDisc, CreditosDisc)
As dependências funcionais (podendo incluir dependências transitivas) que existem nesta tabela são as seguintes:
6
• (CodDisc,AnoSem,SiglaTur,HoraInicio)→NumHoras
• CodDisc→NomeDisc
• CodDisc→CreditosDisc
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:
1. A tabela encontra-se na 1FN pois não contém tabelas aninhadas. Ela não
está na 2FN por conter dependências parciais.
2. 2FN:
Tab1(CodDisc,AnoSem,SiglaTur, HoraInicio,
NumHoras)
Tab2(CodDisc,NomeDisc,CreditosDisc)
3FN=2FN
7