Fundamentos de Bancos de Dados – Prova 3 Prof. Carlos A. Heuser Julho de 2004 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 referentes a uma base de dados para genealogia. Nesta base, pessoas desejam armazenar informações sobre seus ancestrais. Cada pessoa é identificada por um código. Para cada pessoa, podem ser armazenadas várias informações: sexo, prenome, sobrenome, data e local de nascimento e data e local de falecimento. Todas estas informações são opcionais. Cada local têm um código e um nome. Para cada matrimônio, é necessário armazenar as seguintes informações: pessoa que é marido, pessoa que é esposa, pessoas que são filhos gerados pelo matrimônio e data e local do matrimônio. Novamente, todas estas informações são opcionais. Lembrar que uma pessoa pode ter participado de vários matrimônios. 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. Informar que notação está sendo usada, caso seja usada alguma notação diferente da que consta no livro. 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 e está apresentada com a notação européia do DBMain. 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 informações sobre servidores e alunos em uma Universidade. O modelo está construído com a notação do livro. 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 1

conceitual/1

Pessoa Código da pessoa Prenome Sobrenome Data de nascimento Data de falecimento id: Código da pessoa 0-1

Filho 0-1

0-N

0-N

0-1

Marido

0-1

0-N

Matrimônio Código do matrimônio data do matrimônio id: Código do matrimônio

Esposa 0-1

0-1 Falecimento

Local matrimônio

Nascimento 0-N

0-N

0-N

Cidade Código da cidade Nome da cidade id: Código da cidade

Figura 1: Modelo conceitual para a questão 1

nome data de nascimento número endereço do cartão PESSOA n data início p Lotação escola data fim número n secundária ALUNO CARGO (1,1) cargo n INGRESSO data de ingresso

Figura 2: Modelo conceitual para a questão 2

2

primária e as chaves estrangeiras. Quando houver alternativas de projeto dizer que alternativa foi usada. Pessoa(NoCartao,Nome,DataNasc,Endereco,EscolaSecun) Cargo(CodigoCargo,NomeCargo) Lotacao(NoCartao,CodigoCargo,DataIniLota,DAtaFimLota) (NoCartao) referencia Pessoa (CodigoCargo) referencia Cargo Ingresso(NoCartao,DataIngresso) 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 aeroportos e companhias de aviação. Esta base de dados é a mesma que apareceu na Prova 1 deste semestre. 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, bem como identificadores. Companhia (SiglaCia,NomeCia) Voo(SiglaCia,NoVoo,SiglaAeroSaida,SiglaAeroDestino HoraSaida,HoraChegada) SiglaAeroSaida referencia Aeroporto SiglaAeroDestino referencia Aeroporto SiglaCia referencia Companhia Aeroporto(SiglaAero,NomeAero) VooCompart(SiglaCiaCompart,NoVooCompart,SiglaCia,NoVoo) (SiglaCia,NoVoo) referencia Voo

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: Tab(SiglaCia,NoVoo,NomeCia,SiglaAeroSaida,NomeAeroSaida HoraSaida,HoraChegada) As dependências funcionais (podendo incluir dependências transitivas) que existem nesta tabela são as seguintes: • (SiglaCia,NoVoo)→SiglaAeroSaida 3

conceitual/1

Companhia Sigla da compahia Nome da companhia

0-N

R

Voo Número do voo Hora de saída Hora de chegada id: R.Companhia Número do voo

1-1 0-N

R_1

1-1

1-1

1-1

Voo compartilhado Sigla comanhia voo compart Numero voo compart id: Sigla comanhia voo compart Numero voo compart

Origem 0-N

Destino 0-N

Aeroporto Sigla do aeroporto Nome do aeroporto id: Sigla do aeroporto

Figura 3: Modelo conceitual para a questão 3

4

• (SiglaCia,NoVoo)→NomeAeroSaida • (SiglaCia,NoVoo)→HoraSaida • (SiglaCia,NoVoo)→HoraChegada • SiglaCia→NomeCia • SiglaAeroSaida→NomeAeroSaida 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(SiglaCia,NoVoo,SiglaAeroSaida,NomeAeroSaida HoraSaida,HoraChegada) Tab2(SiglaCia,NomeCia) 3FN: Tab1(SiglaCia,NoVoo,NomeCia,SiglaAeroSaida HoraSaida,HoraChegada) Tab2(SiglaCia,NomeCia) Tab3(SiglaAeroSaida,NomeAeroSaida)

5