Projeto De Banco De Dados

Universidade Federal de São Carlos Departamento de Computação

Projeto de Banco de Dados Profa Marina Teresa Pires Vieira

Março 2000 Última revisão: Abril 2002

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

PROJETO DE BANCO DE DADOS 1. Conceitos de Modelagem de Dados............................................................................. 3 1.1. Processo de Projeto de Banco de Dados .........................................................................3 1.2. Modelos de Dados Semânticos.........................................................................................5 1.2.1. Abstrações no Projeto Conceitual de Banco de Dados.........................................................6

1.3. Modelo EER (Extended Entity-Relationship)................................................................7 1.3.1. Modelo ER ...............................................................................................................................7 1.3.2. Extensão do Modelo ER..........................................................................................................9

1.4. Exercícios ........................................................................................................................12

2. Questões no Projeto Conceitual de um Banco de Dados ......................................... 15 2.1.Introdução........................................................................................................................15 2.2. Projeto de Visão..............................................................................................................15 2.2.1. Projeto de Visão com base em Linguagem Natural ...........................................................15 2.2.2. Projeto de Visão com base em Formulários........................................................................18 2.2.4. Exercícios - Projeto de Visão................................................................................................23

2.3. Integração de Visões.......................................................................................................26 2.4. Estudo de Caso: Gerenciamento de uma Biblioteca...................................................28 2.5. Melhorando a Qualidade de um Esquema de Banco de Dados.................................32 2.5.1. Qualidades de um esquema de BD.......................................................................................32 2.5.2. Transformações de Esquema ...............................................................................................35 2.5.2.1. Transformações para conseguir minimalidade ...............................................................35 2.5.2.2. Transformações para conseguir Expressividade .............................................................36 2.5.2.3. Transformações para obter Normalização ......................................................................38 2.5.3. Reestruturação do esquema de Gerenciamento de uma Biblioteca..................................41

2.6. Exercício..........................................................................................................................42

3. Projeto Lógico de Banco de Dados ........................................................................... 45 3.1. Refinamento do Esquema Conceitual...........................................................................45 3.1.1. Particionamento de Entidades .............................................................................................46 3.1.2. Fusão de entidades ................................................................................................................48 3.1.3. Reprodução de atributos ......................................................................................................48

3.2. Mapeamento para o Modelo Relacional.......................................................................48 3.2.1. Exercícios – Mapeamento.....................................................................................................53

3.3. Tópicos Adicionais sobre Modelagem de Dados e Mapeamento Análise de diferentes situações..................................................................................................................................54 3.3.1.Categorização - mapeamento ................................................................................................54 3.3.2. Atividades de extensão - fazer o esquema conceitual .........................................................55 3.3.3. Docentes e Docentes Bolsistas - fazer o esquema conceitual..............................................56 3.3.4. A modelagem satisfaz aos requisitos dos usuários?...........................................................57 3.3.5. Elaboração de Esquemas Conceituais .................................................................................58

3.4. Esquema de Navegação para Operações de Banco de Dados.....................................60 3.4.1. Exercícios - Esquema de Navegação....................................................................................63

3.5. Modelagem da Carga do Banco de Dados....................................................................64

4.

Projeto Físico de Banco de Dados ........................................................................ 67

Estruturas de Armazenamento - OpenIngres............................................................... 67 Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

1

4.1. HEAP...............................................................................................................................67 4.2. HASH...............................................................................................................................68 4.3. ISAM ...............................................................................................................................70 4.4. Btree.................................................................................................................................71 4.5. Índices Secundários........................................................................................................73 5. Bibliografia.........................................................................................................................74

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

2

1. Conceitos de Modelagem de Dados 1.1. Processo de Projeto de Banco de Dados Bancos de dados são componentes importantes dos sistemas de informação (SIs) e, consequentemente, o projeto do banco de dados apresenta-se como uma atividade essencial na fase de desenvolvimento dos SIs. Projetar bancos de dados tem se tornado uma atividade popular, as vezes realizada não somente por profissionais da área de banco de dados, mas também por não especialistas. Freqüentemente, a falta de abordagens adequadas para o projeto de um banco de dados pode incorrer em resultados indesejáveis, como ineficiência em atender a demanda de aplicações e problemas com a manutenção do banco de dados. Geralmente a causa disso é a falta de clareza em entender a natureza exata dos dados em um nível conceitual (abstrato). O projeto de um banco de dados é decomposto em Projeto Conceitual, Projeto Lógico e Projeto Físico, conforme mostrado na figura 1.1. Requisitos de dados

Projeto Conceitual Esquema conceitual Projeto Lógico Esquema Lógico Projeto Físico Esquema Físico

Fig. 1.1

O Projeto Conceitual usa como base a especificação dos requisitos produzindo como resultado o esquema conceitual do banco de dados. Um esquema conceitual é Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

3

uma descrição em alto nível da estrutura do banco de dados, independente do Sistema de Gerenciamento de Banco de Dados (SGBD) adotado para implementá-lo. Um modelo conceitual (por exemplo, o modelo Entidade-Relacionamento) é usado para descrever os esquemas conceituais. O propósito do projeto conceitual é descrever o conteúdo de informação do banco de dados ao invés das estruturas de armazenamento que serão necessárias para gerenciar essa informação. O Projeto Lógico tem por objetivo avaliar o esquema conceitual frente às necessidades de uso do banco de dados pelos usuários/aplicações, realizando, no mesmo, possíveis refinamentos para alcançar maior desempenho das operações sobre o banco de dados. A tarefa final do projeto lógico é a geração do esquema lógico correspondente ao esquema conceitual resultante do refinamento . Um esquema lógico é uma descrição da estrutura do banco de dados que pode ser processada por um Sistema Gerenciador de Banco de Dados (SGBD). Um modelo lógico é usado para especificar esquemas lógicos. Os modelos lógicos mais conhecidos para bancos de dados convencionais, pertencem a três classes: relacional, em redes e hierárquico, sendo o modelo relacional o mais amplamente usado atualmente. O projeto lógico depende da classe do modelo de dados usado pelo SGBD, mas não do SGBD específico usado. O Projeto Físico toma por base o esquema lógico para construir o esquema físico. Um esquema físico é uma descrição da implementação do banco de dados em memória secundária; ele descreve as estruturas de armazenamento e métodos de acesso usados para efetivamente realizar o acesso aos dados. O projeto físico é direcionado para um SGBD específico (por exemplo: Oracle, Sybase, OpenIngres, Access). Decisões tomadas durante o projeto físico, para melhorar o desempenho, podem afetar a estrutura do esquema lógico. Uma vez que o projeto físico do banco de dados é completado, os esquemas lógico e físico são expressos usando a linguagem de definição de dados do SGBD adotado. O banco de dados é criado e populado e pode ser testado para se tornar operacional. O esquema físico do banco de dados é influenciado pelas fases por que passou a construção do banco de dados. A fase de projeto conceitual é tida como uma das mais (senão a mais) delicadas em todo esse processo, pois depende muito da habilidade do projetista do banco de dados e das qualidades do modelo de dados adotado para a Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

4

elaboração do esquema conceitual. A meta nessa fase é obter um esquema conceitual do banco de dados que seja tão completo e expressivo quanto possível. Esse esquema deve procurar expressar o máximo da semântica envolvida na informação. Mecanismos de representação de alto nível são empregados, tais como representação de hierarquias de subconjunto e de generalização, representação de restrições de cardinalidade e de atributos compostos e multivalorados. Para a representação do esquema conceitual geralmente utiliza-se uma extensão do modelo Entidade-Relacionamento. O projeto conceitual de um banco de dados não pode ser totalmente auxiliado por ferramentas automáticas; cabe ao projetista entender e transformar os requisitos dos usuários em esquemas conceituais. O projeto conceitual é, assim, a fase mais crítica do projeto do banco de dados. Ele não depende somente da habilidade e experiência do projetista, mas também da cooperação dos usuários que são responsáveis por descrever suas necessidades e o significado dos dados. O esquema conceitual deve fazer parte da documentação do processo de projeto, sendo utilizado durante a operação e manutenção do banco de dados, pois facilita o entendimento dos esquemas de dados e das aplicações que os utilizam. Para auxiliar o projetista a elaborar o projeto conceitual de um banco de dados existem as abstrações de dados, que apresentam as vantagens: • ajudam o projetista a entender, classificar e modelar a realidade, • melhoram a eficiência de implementações subsequentes, • permitem melhor representar a semântica das novas aplicações de banco de dados, provenientes de áreas não tradicionais.

1.2. Modelos de Dados Semânticos Modelos de dados são veículos para descrever a realidade. Um modelo de dados é uma coleção de conceitos que podem ser usados para descrever um conjunto de dados e operações para manipular os dados. Os modelos de dados servem de base para o desenvolvimento de Sistemas de Gerenciamento de Banco de Dados (SGBDs). Distingue-se dois tipos de modelos de dados: • Modelos conceituais, que são ferramentas para representar a realidade em alto nível de abstração;

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

5

• Modelos lógicos, que suportam descrições de dados que podem ser processados por um computador (ex: modelos relacional, hierárquico, em redes). Esses modelos são facilmente mapeados para a estrutura física do banco de dados .

Modelos de dados semânticos são modelos de dados que procuram capturar tanto quanto possível, os relacionamentos semânticos existentes entre as entidades do mundo real. São exemplos de modelos de dados semânticos o modelo EntidadeRelacionamento e o modelo funcional DAPLEX.

1.2.1. Abstrações no Projeto Conceitual de Banco de Dados Para auxiliar o projetista na tarefa de modelar os dados, existem os mecanismos de abstração de dados que permitem melhor representar a semântica da informação envolvida na aplicação. As abstrações comumente usadas no projeto conceitual são: classificação, agregação e generalização. ♦ Abstração de Classificação: é usada para agrupar objetos similares, caracterizados por propriedades comuns, em classes de objetos. Ex: classe EMPREGADO - instancias : (João, Pedro, ..., Maria). A classificação estabelece um relacionamento É-INSTANCIA-DE entre cada elemento da classe e a classe. ♦ Abstração de Agregação: é um conceito de abstração para construir objetos compostos a partir de seus objetos componentes. Ex: -

Uma entidade é uma agregação de atributos: PESSOA, composta por Nome, Sexo, Profissão;

-

Um relacionamento é uma agregação de entidades e atributos;

-

Um atributo composto é uma agregação de atributos;

-

Pode-se agregar entidades relacionadas entre si, compondo uma entidade de nível mais alto.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

6

Essa abstração estabelece um relacionamento É-PARTE-DE entre os componentes e a classe. ♦ Abstração de Generalização: define um relacionamento de subconjunto entre os elementos de duas ou mais classes. Ex: classes CARRO e BICICLETA são subconjuntos da classe VEÍCULO. Essa abstração estabelece um relacionamento É-UM entre a classe pai (chamada superclasse) e cada classe filha (subclasse). As subclasses são definidas com base em alguma característica da superclasse. No exemplo dado, essa característica é tipo de veículo (Carro, Bicicleta).

Propriedade Fundamental da Generalização: Todas as abstrações definidas para a classe genérica são herdadas por todas as classes que são subconjunto.

1.3. Modelo EER (Extended Entity-Relationship) 1.3.1. Modelo ER Devido à popularidade e ampla utilização do modelo Entidade-Relacionamento (ER) para o projeto conceitual de bancos de dados, várias extensões desse modelo foram propostas, visando sua utilização para a modelagem de informações mais complexas. O modelo ER foi proposto por Peter Chen em 1976, sendo que originalmente o modelo incluia somente os conceitos de entidade, relacionamento e atributos; posteriormente outros conceitos foram introduzidos no modelo, tais como atributos compostos e hierarquias de generalização. As entidades representam classes de objetos do mundo real. Na figura 1.2, Aluno e Professor são exemplos de entidades. São representadas graficamente através de um retângulo. Relacionamentos representam associações entre duas ou mais entidades. Na figura 1.2, orienta representa um relacionamento binário entre as entidades Professor e Aluno, representando que um aluno tem 1 (um) professor como orientador e um professor orienta n (vários) alunos. Os relacionamentos são representados graficamente através de losangos.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

7

Os atributos representam propriedades das entidades ou dos relacionamentos. Na figura 1.2 tem-se para a entidade Aluno os atributos nro_aluno e nome; Professor possui os atributos nome e sexo. nro_aluno nome

Aluno

n

1 orienta

Professor

nome sexo

Figura 1.2 - Diagrama Entidade-Relacionamento Cardinalidade mínima e máxima de uma entidade em um relacionamento: Para indicar, de forma mais precisa, a cardinalidade de um relacionamento, pode-se usar uma notação que indique a ocorrência mínima e máxima de cada entidade no relacionamento. Por exemplo, a cardinalidade do relacionamento da figura 1.2 pode ser representada como na figura 1.3, indicando que um aluno pode ou não ter um orientador e pode ter no máximo 1 orientador; um professor orienta vários alunos, podendo haver professor que não orienta nenhum aluno.

nro_aluno nome

Aluno

(0,1)

(0,n) orienta

Professor

nome sexo

Figura 1.3 - Uso de cardinalidade mínima e máxima no relacionamento

Cardinalidade mínima e máxima de um atributo

:

Os atributos também são caracterizados por cardinalidade mínima e máxima. Por exemplo, na entidade Professor, da figura 1.4, o atributo títulos possui cardinalidade mínima 1 e máxima n, indicando que cada professor deve ter no mínimo um título e pode ter vários. Quando não especificado no atributo, o valor da cardinalidade é (1,1).

Professor

nome sexo títulos (1,n)

Fig. 1.4 - cardinalidade mínima e máxima de atributo

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

8

Cardinalidade mínima de atributo igual a 0 (zero) indica atributo opcional; cardinalidade máxima de atributo maior que 1 (um) indica que o atributo é multivalorado.

Atributos compostos: Um atributo composto representa um grupo de atributos que possuem uma afinidade em significado ou uso. Como exemplo, considere o atributo endereço na figura 1.5, que é composto por Rua, Cidade, Estado, País e CEP. Rua Cidade Professor Estado País CEP Fig. 1.5 - atributo composto endereço

Um identificador interno é um atributo ou grupo de atributos que determina uma entidade. Será adotada a representação da figura 1.6 para identificador interno. identificador

ident1 ident2

Fig. 1.6 - identificador interno Quando uma entidade é identificada através de outra entidade associada a ela, temse um identificador externo, cuja representação pode ser a da figura 1.7(a) ou usando a notação de entidade fraca (figura 1.7 (b)). Essa figura indica que o identificador de A faz parte do identificador de B 1.3.2. Extensão do Modelo ER

A

B

Fig. 1.7 (a) - identificador externo

A

B

Fig. 1.7 (b) - identificador externo

Para permitir melhor expressar as informações a serem modeladas, foram introduzidos no modelo ER as hierarquias de generalização e de subconjunto.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

9

♦ Hierarquia de Generalização: Uma classe E é uma generalização de um grupo de classes E1, E2, ..., En se cada objeto das classes E1, E2, ..., En é também um objeto da classe E. Uma forma de representar uma hierarquia de generalização é dada na figura 1.8. E

E2

E1

...

En

Fig. 1.8 - Hierarquia de generalização ♦ Propriedades de Cobertura da generalização ú

Cobertura TOTAL ou PARCIAL:

A cobertura de uma generalização é total (t) se cada elemento da classe genérica é mapeada para pelo menos um elemento das classes especializadas. Ex: A generalização formada pela classe PESSOA e as subclasses HOMEM

e

MULHER (figura 1.9) possui cobertura total. A cobertura é parcial (p) se existe algum elemento da classe genérica que não é mapeado para nenhum elemento das subclasses. Exemplo: Suponha que VEÍCULO é uma classe que contém outros tipos de veículos além de carros e bicicletas. A generalização da figura 1.10 é parcial.

Pessoa Veículo parcial

total Homem

Mulher

Fig.1.9 - cobertura total

Projeto de Banco de Dado

Carro

Bicicleta

Fig.1.10 - cobertura parcial

Profa. Marina Teresa Pires Vieira

10

ú Cobertura EXCLUSIVA (DISJUNÇÃO) ou de SOBREPOSIÇÃO (OVERLAPPING): A cobertura de uma generalização é exclusiva (e) se cada elemento da classe genérica é mapeado para no máximo um elemento das subclasses. Ex: A figura 1.10 representa uma cobertura exclusiva.

A cobertura é de sobreposição (s) se existe algum elemento da classe genérica que é mapeado para elementos de duas ou mais subclasses diferentes. Ex: Na figura 1.11, supondo que pode existir aluno que cursa a graduação e a pósgraduação ao mesmo tempo, tem-se uma cobertura de sobreposição.

Aluno sobreposição AlunoGraduaçao

Aluno-PósGraduação

Fig. 1.11 - cobertura de sobreposição

Notação: Para denotar o tipo de cobertura de uma hierarquia de generalização será usada a seguinte notação: (t,e), (t,s), (p,e), (p,s) e será indicado como na figura 1.12. Quando numa generalização a cobertura não está especificada, admite-se que é (t,e).

Veículo (p,e) Carro

Bicicleta

Fig. 1.12 - cobertura parcial e exclusiva ♦ Hierarquia de Subconjunto: uma entidade E1 é um subconjunto de uma entidade E se toda ocorrência de E1 for também uma ocorrência de E (figura 1.13). É um caso particular da hierarquia de generalização. Numa hierarquia de subconjunto o tipo de cobertura é (p,e) sempre.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

11

A representação de hierarquia de subconjunto é dada na figura 1.13.

E

e1 e2 e3

E1

e4 e5

Fig.1.13 - hierarquia de subconjunto

Cliente

nro-cli nome-cli endereço

Cliente Especial

vantagens

Fig.1.14 - Exemplo

Observações: 1. O identificador da entidade genérica é também um identificador para as entidades da especialização. 2. Entidades da especialização podem ter outros identificadores, como mostrado na figura 1.15.

nome

Pessoa

situaçãoserv-mil

Homem

Mulher

Empr

RG título

Chefe

Gerente

nome_ nro-emp nome_divisão categoria solteira Fig.1.15

Militar

divisão ident-nadivisão

posição

1.4. Exercícios 1. Indique na figura 1.9 se a cobertura é exclusiva ou sobreposição. 2. Indique na figura 1.11 se a cobertura é total ou parcial. 3. Indique as propriedades de cobertura da generalização na figura 1.16. Suposições: - só existem jogadores de futebol e de tênis; - pode ter jogadores que jogam os dois esportes.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

12

Jogador

Tênis

Futebol Fig.1.16

4. Verifique como as propriedades de cobertura se relacionam com as restrições de cardinalidade. (Sugestão: interprete hierarquias de generalização como tipos especiais de relacionamento entre classes).

5. Transforme as hierarquias do exerc.4 em relacionamentos entre a classe genérica e as subclasses.

6. Considerando a propriedade fundamental de hierarquias de generalização, simplifique o esquema da figura 1.17. mora (1,n) (1,n)

nome nascida

Cidade

Pessoa (1,1)

(1,n) reside

Masculino

(1,1) Feminino

(0,n) idade trabalha

Soldado (0,1)

Empregado

idade

Fig.1.17

7. Considere o esquema da figura 1.17. Como você pode mudá-lo para representar no esquema todos os empregados, homens e mulheres?

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

13

8. Faça o esquema conceitual do seguinte problema: Uma companhia mantém informações sobre todas as pessoas que, de alguma forma, possuem com ela algum vínculo, dentre essas seus funcionários. Os seguintes requisitos foram levantados junto aos usuários:

a. De cada pessoa mantém-se um código, o nome, endereço.

b. De cada funcionário guarda-se também seu salário e o departamento a que ele pertence. Desses funcionários, alguns são gerentes e para cada um destes guarda-se os nomes dos projetos que eles gerenciam.

c. Dos demais funcionários que são operários, guarda-se suas habilidades (um operário pode ter várias habilidades).

d. Mantém-se também os tipos de trabalho executados na Companhia (código e característica) e os operários que executaram cada trabalho, juntamente com o período que isto se deu. Sabe-se também que pode haver operários que não exercem nenhum tipo de trabalho dentre os cadastrados. e. Deve-se também manter os dependentes de cada funcionário (nome, sexo e data de nascimento).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

14

2. Questões no Projeto Conceitual de um Banco de Dados 2.1.Introdução A meta na fase de projeto conceitual é obter um esquema conceitual do banco de dados que seja tão completo e expressivo quanto possível. Esse esquema deve procurar expressar o máximo da semântica envolvida na informação. Mecanismos de representação de alto nível são empregados, tais como representação de hierarquias de subconjunto e de generalização, representação de restrições de cardinalidade e de atributos compostos e multivalorados. Para a representação do esquema conceitual geralmente utiliza-se uma extensão do modelo Entidade-Relacionamento.

Visões: Quando as aplicações são complexas e usualmente diferentes analistas estão envolvidos no processo de projeto do banco de dados, a aplicação pode ser particionada em atividades menores, representando visões de grupos de usuários. Visão, na fase de projeto conceitual, é a parte de um banco de dados ou dos requisitos de dados de uma aplicação que é vista por um usuário ou grupo de usuários. 2.2. Projeto de Visão Refere-se à modelagem da parte do banco de dados de interesse de um usuário ou grupo de usuários, com base nos requisitos de dados, usando os conceitos de um modelo de dados. Os requisitos podem estar expressos através de: •

Descrições em linguagem natural, através da qual os usuários expõem suas necessidades;

•

Formulários impressos em papel, que são utilizados para coletar dados. Se já houver um sistema preexistente, esses formulários podem ser telas formatadas para entrada de dados;

•

Declarações de registros pertencentes a aplicações preexistentes.

2.2.1. Projeto de Visão com base em Linguagem Natural Considere a seguinte descrição de um sistema envolvendo dados de uma universidade: Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

15

1 [Em um bd de universidade representamos dados sobre 2 alunos e professores]. [ Para alunos, 3 representamos o último nome, idade, sexo, cidade e estado de 4 origem, cidade e estado de residência de suas 5 famílias, locais e estados onde viveram antes 6 (com o período em que viveram), disciplinas que 7 tenham cursado, com nome, código, professor, 8 nota e data.] [Representamos também disciplinas que 9 estão freqüentando presentemente e para cada dia, locais 10 e horários que as aulas são oferecidas (cada disciplina 11 é oferecida no máximo uma vez em um dia)].[ Para estudantes 12 graduados, representamos o nome do orientador 13 e o número total de créditos do último ano. 14 Para alunos de doutorado, representamos o título e área de pesquisa 15 de suas teses].[ Para professores, representamos o último 16 nome, idade, local e estado de origem, nome do 17 departamento a que pertencem, número do telefone, 18 título, estado civil e tópicos de sua pesquisa.] Requisitos do banco de dados da universidade.

Nessa descrição pode-se identificar algumas ambigüidades, como mostrado abaixo.

LINHA 5 6 9 9 9 10 11 16 17

TERMO locais período Presentemente dia locais aulas estudantes local telefone

NOVO TERMO RAZÃO PARA CORREÇÃO Cidades Local é uma palavra genérica número de anos Período é uma palavra genérica no ano corrente Presentemente é ambíguo dia da semana mais específico Salas Homônimo de locais na linha 5 Disciplinas Sinônimo de disciplinas na linha 8 Alunos Sinônimo de alunos na linha 2 Cidade igual à linha 5 telefone do mais específico departamento tópico área de pesquisa linha 14 18 Termos ambíguos nos requisitos, com possíveis correções.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

16

Descrição com os termos ambíguos substituídos por novos termos:

[Em um bd de universidade representamos dados sobre alunos e professores]. [Para alunos, representamos o último nome, idade, sexo, cidade e estado de origem, cidade e estado de residência de suas famílias, cidades e estados onde viveram antes (com o número de anos em que viveram), disciplinas que tenham cursado, com nome, código, professor, nota e data]. [Representamos também disciplinas que estão freqüentando no ano corrente e para cada dia da semana, salas e horários que as disciplinas são oferecidas (cada disciplina é oferecida no máximo uma vez em um dia)]. [Para alunos graduados, representamos o nome do orientador e o número total de créditos do último ano. Para alunos de doutorado, representamos o título e área de pesquisa de suas teses]. [Para professores, representamos o último nome, idade, cidade e estado de origem, nome do departamento a que pertencem, número do telefone do departamento, título, estado civil e áreas de pesquisa] . Requisitos após a filtragem das ambigüidades.

Exercício: Faça o esquema conceitual desse banco de dados.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

17

2.2.2. Projeto de Visão com base em Formulários Distinguem-se: -

Partes do formulário que não apresentam informações relevantes a serem armazenadas (assinaturas, data de emissão, cabeçalho, etc.).

-

Partes que são pré-impressas no formulário e que referem-se a nomes de campos;

-

Partes que devem ser preenchidas pelos usuários e que serão armazenadas no sistema;

-

Partes descritivas referentes às instruções que devem ser seguidas para preencher os campos do formulário.

A seguir são dados vários exemplos de formulários. Para cada caso, faça o esquema conceitual correspondente. a) Formulário para registrar informações de docentes que atuam em programa de pósgraduação. Formulário 1 INFORMAÇÃO DE DOCENTE ATUANTE NA PÓS-GRADUAÇÃO Nome do docente: __________________________________________ Departamento:_____________________________________________ Mês/Ano término doutorado:__________________________________ Instituição do doutorado: _____________________________________ Áreas de atuação (no máximo 4 áreas): __________________________ __________________________________________________________ __________________________________________________________ Orientações de Mestrado/Doutorado no programa: Nome do aluno

Data início

Data término

Categoria (mestr/dout)

.. .

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

18

b) Considere no formulário 1 as seguintes alterações: b1) para informações sobre alunos, adicionar colunas para indicar a situação do aluno:

Nome do aluno

...

Categoria (mestr/dout)

Situação cursando trancado Abandonou

b2) Para docente incluir campos para assinalar opções, onde várias opções podem ser selecionadas para um mesmo docente: Assinale: Professor contratado Ministra aula na graduação Possui vínculo com outras Instituições. Se sim, indique os nomes das Instituições: ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________ ____________________________________________________________

c) Considere a tabela tridimensional que representa os gastos de várias companhias para um período de 3 anos. Sobre cada companhia deseja-se guardar esses gastos,bem como o nome da companhia, CGC e endereço. Gastos nos últimos 3 anos Ano Jan Fev 1996 01-15 quinzena 16-31 Jan Fev 1997 01-15 quinzena 16-31 Jan Fev 1998 01-15 quinzena 16-31

Projeto de Banco de Dado

Mar

Mês Abr ...

Dez

Mar

Abr

...

Dez

Mar

Abr

...

Dez

Profa. Marina Teresa Pires Vieira

19

2.2.3. Projeto de Visão com base em Declarações de Registro A seguir são tratados alguns aspectos relativos à estrutura de declaração de arquivos como guia para a modelagem de esquemas E-R (Entidade-Relacionamento). Não serão tratadas declarações de registros de um SGBD (Oracle, Acess, Dbase, Paradox, etc...), e sim, declarações de registros de linguagens de mais baixo nível (C, C++, Pascal, Cobol, etc...). As aplicações comerciais geralmente utilizam arquivos compostos de registros que são armazenados na memória secundária. Esses arquivos são acessados, consultados e/ou alterados de acordo com as funções executadas pelo sistema. Os registros são compostos de campos. Os campos por sua vez podem ser compostos de sub-campos. Como conseqüência, geralmente têm uma estrutura hierárquica, e cada campo se coloca numa posição desta hierarquia. Em C pode-se utilizar estruturas ou classes definidas pelas cláusulas struct/class, para armazenar informações nos arquivos segundo as estruturas declaradas. O mesmo se aplica aos tipos definidos em Pascal. Em Cobol, a declaração do(s) arquivo(s) que irá(ão) fazer parte do sistema é realizada em uma estrutura chamada DATA DIVISION. Cobol é formado por quatro partes principais ( as DIVISIONS). São elas: • IDENTIFICATION (Identificação do programa - nome, autor, data compilação...); • ENVIRONMENT (Informações a respeito do ambiente físico de compilação e de execução); • DATA (Descrição resumida de cada arquivo e a organização dos registros no arquivo); • PROCEDURE (Informações a respeito do processamento em si). A parte que nos interessará é a DATA DIVISION, que é subdividida da seguinte forma: DATA DIVISION. FILE SECTION. [descrição do arquivo descrição do registro]... WORKING STORAGE SECTION [descrição de item ou de registro]...

A seguir apresenta-se um exemplo de declaração de um arquivo e da estrutura dos registros em Cobol, e as correspondentes declarações de registros em C e em Pascal .

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

20

COBOL: DATA DIVISION FILE SECTION FD ARQUIVO-EXEMPLO BLOCK CONTAINS O RECORDS RECORD CONTAINS 80 CHARACTERES 01 EXEMPLO 05 DADO-NUMERICO 05 LETRA

PIC 9(8). PIC X(1).

C:

Pascal:

struct exemplo { int dado_numerico; char letra; };

Type Exemplo = record dado_numerico: integer; letra: char; end;

Na declaração de um registro podem estar expressos vários tipos de campos, como por exemplo, campos simples, campos multivalorados (em COBOL os campos multivalorados são definidos na cláusula OCCURS), redefinição de campos (através da cláusula REDEFINES), etc. Esses campos devem ser mapeados para uma modelagem adequada no esquema, que expresse toda a semântica da informação envolvida. A seguir são dados vários exemplos de declarações de registros em COBOL. Para cada caso, faça o esquema conceitual correspondente. a) Considere a declaração em COBOL dos registros para um arquivo de pedidos: 01 PEDIDO. 02 NRO-PEDIDO

PIC X(10).

02 DATA-DE-EMISSÃO. 03 DIA PIC 9(2). 03 MÊS PIC 9(2). 03 ANO PIC 9(2). 02 DATA-ENTREGA. 03 DIA PIC 9(2). 03 MÊS PIC 9(2). 03 ANO PIC 9(2).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

21

02 VALOR. 03 PREÇO 03 TAXA-MUDANÇA

PIC 9(6)V99. PIC 9(6)V99.

02 PEÇAS-PEDIDO OCCURS 10 TIMES. 03 CODIGO-PEÇA PIC 9(6). 03 QTIDADE PIC 9(4).

b) Uso de cláusulas OCCURS subordinadas: 01 PRODUTO. 02 NOME PIC X(20). 02 CÓDIGO PIC X(4). 02 PREÇO PIC 9(5). 02 QTDE-EM-ESTOQUE. 03 QTDE-EM-ESTOQUE-POR-ANO OCCURS 4 TIMES. 04 QTDE-EM-ESTOQUE-POR-MÊS PIC 99 OCCURS 12 TIMES. c) Redefinição de campo Permite ao programador definir a mesma parte de um registro usando declarações de campos diferentes. Com isso, otimiza o espaço de armazenamento físico. 01 PESQUISADOR. 02 NOME-PESQUISADOR PIC X(30). 02 ENDEREÇO PIC X(25). 02 DOCENTE-INTERNO. 03 NOME-PROGRAMA-POS-GRAD PIC X(27). 03 PROJETOS-PESQUISA-DO-DOCENTE PIC X(15) OCCURS 5 TIMES. 02 PESQUISADOR-VISITANTE REDEFINES DOCENTE-INTERNO. 03 INSTITUIÇÃO-ORIGEM PIC X(50). 03 DATA-INÍCIO. 04 DIA PIC 9(2). 04 MÊS PIC 9(2). 04 ANO PIC 9(2). 03 DATA-TÉRMINO. 04 DIA PIC 9(2). 04 MÊS PIC 9(2). 04 ANO PIC 9(2). 03 AREA-ATUAÇÃO PIC X(10) OCCURS 4 TIMES. d) Ponteiros Simbólicos 02 EMPREGADO. 03 CÓDIGO PIC X(10). 03 COD-DEPTO PIC X(5). Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

22

03 COD-PROJETO PIC X(7) OCCURS 10 TIMES. 02 DEPTO. 03 CÓDIGO PIC X(5). 03 NOME PIC X(20). 02 PROJETO. 03 CÓDIGO PIC X(7). 03 COD-DEPTO PIC X(5). 03 DESCRIÇÃO PIC X(30). 03 EMPREGADO-RESPONSÁVEL PIC X(10). e) Ponteiro que se refere ao campo identificador do registro em que ele é definido. 01 PROJETO. 02 CÓDIGO PIC X(7). 02 DESCRIÇÃO PIC X(30). 02 CÓDIGO-SUPERPROJETO PIC X(7). 02 ORÇAMENTO PIC 9(6)V99. 2.2.4. Exercícios - Projeto de Visão 1. Construa um esquema conceitual para a seguinte descrição em linguagem natural: Projete o banco de dados para um ambiente de suporte de programação. Nesse ambiente os programadores produzem programas, que são escritos em determinadas linguagens de programação. Cada programa é escrito por um certo programador, pode chamar outros programas e pode ser usado por determinados usuários. Os usuários são reconhecidos por seu nome de log-in e por seu código; os programas têm nomes compostos que incluem o nome do programa, a extensão e o código do programador. Os programas têm um número de versão, uma data e uma breve descrição; alguns programas interagem com SGBDs. Cada SGBD mantém dados armazenados na forma de relações, com vários atributos e uma chave primária. Cada banco de dados é definido por um administrador de banco de dados, que é um programador especializado em gerenciamento de dados. 2. Transcreva as definições de arquivo em COBOL a seguir para um esquema ER. A aplicação lida com o controle distribuído de um processo industrial. O arquivo DADOSEMPREGADO indica que cada empregado pode controlar comandos e alarmes. O arquivo SISTEMA-COMANDOS lida com comandos disponíveis para controle de processos e os locais onde cada comando pode ser executado. O arquivo SISTEMA-

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

23

ALARMES lida com os mesmos dados para alarmes. Finalmente, o arquivo COMUNICAÇÕES descreve dados sobre subsistemas de comunicação, sua tecnologia, os dispositivos conectados, locais dos dispositivos, etc. A transmissão de dados pode ser através de microondas ou cabos. 01 DADOS-EMPREGADO. 02 NRO-ID 02 NOME 02 SUPERVISOR-EMPREGADO 02 SISTEMA-COMANDO-CONTROLADO 02 SISTEMA-ALARME-CONTROLADO 01 SISTEMA-COMANDOS. 02 TIPO 02 DATA 02 HORARIO. 03 HORA 03 MINUTO 03 SEGUNDO 02 POSIÇÃO. 03 NOME 03 LOCAL 03 TIPO 01 SISTEMA.ALARMES. 02 TIPO 02 DATA 02 VALOR 02 HORARIO. 03 HORA 03 MINUTO 03 SEGUNDO 02 POSIÇÃO. 03 NOME 03 LOCAL 03 TIPO

01 COMUNICAÇÕES. 02 TECNOLOGIA 02 VELOCIDADE

PIC 9(6). PIC X(20). PIC 9(6). PIC X(5) OCCURS 10 TIMES. PIC X(5) OCCURS 10 TIMES.

PIC X(5). PIC 9(6). PIC 99. PIC 99. PIC 99. PIC X(20). PIC X(20). PIC X(5).

PIC X(5) PIC 9(6). PIC 9(8). PIC 99. PIC 99. PIC 99. PIC X(20). PIC X(20). PIC X(5).

PIC X(8). PIC 9(6).

02 DISPOSITIVO-REMOTO OCCURS 10 TIMES. 03 NOME PIC X(10). 03 TIPO PIC X(5). 03 LOCAL PIC X(20).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

24

02 DADOS-CABO. O3 MODEM. 04 TIPO 04 TAXA-TRANSMISSÃO

PIC X(10). PIC 9(6).

02 DADOS-MICROONDAS REDEFINES DADOS-CABO. 03 RADIO. 04 RADIO PIC X(5). 04 MODO-TRANSMISSÃO PIC X(5). 04 FREQUENCIA PIC X(10). 04 ANTENA PIC X(5).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

25

2.3. Integração de Visões Integração de visões: Processo de fusão de vários esquemas conceituais em um esquema conceitual global que representa todos os requisitos da aplicação. Aconselha-se o uso do processo de integração principalmente para aplicações complexas, aonde o volume de informação é grande, podendo haver vários analistas envolvidos no processo de projeto. O projetista estabelece uma prioridade entre os esquemas com base em sua importância, confiabilidade e completitude. A integração dos esquemas pode ser realizada por etapas conforme ilustrado abaixo: Esquema 1

Esquema 2

Esquema parcial integrado

...

correção dos conflitos

Esquema 3

Esquema n

Esquema global

Análise de Conflito: realiza-se uma análise nos esquemas a serem integrados para verificar se há algum conflito entre informações de diferentes esquemas. Os tipos de conflitos que podem ocorrer são: a. Conflito de nome: Sinônimos : quando há nomes diferentes para uma mesma informação em diferentes esquemas. Nesse caso deve-se escolher um dos nomes e renomear os demais com esse nome. Homônimos: quando há informações diferentes com o mesmo nome. Nesse caso devese renomea-las para diferenciar.

b. Conflito estrutural: Após realizada a eliminação dos conflitos de nomes, tem-se que dois conceitos de diferentes esquemas com mesmo nome representam o mesmo conceito. Esses conceitos com mesmo nome são agora comparados para verificar se podem ser fundidos num único. Usa-se o seguinte procedimento:

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

26

b1. Conceitos idênticos Se os conceitos são idênticos (mesma representação e mesmos relacionamentos com outras entidades), eles se resumem num único. b2. Representações diferentes da mesma realidade: Se os conceitos são compatíveis, isto é, possuem diferentes representações não contraditórias, muda-se uma das representações.A seguir tem-se exemplos de esquemas nessa situação. esquema 1

esquema 2 título

Livro

Editora

nome

esquema 1 Pessoa

Homem

título nome_editora

Livro

esquema 2 nome Pessoa

nome sexo

Mulher

b3. Especificações de projeto incompatíveis: Se os conceitos forem incompatíveis, como por exemplo, diferentes cardinalidades para o mesmo atributo ou relacionamento, diferentes identificadores, etc., as possíveis soluções são: selecionar uma das representações ou construir uma representação comum satisfazendo todas as restrições dos dois esquemas. Exemplo: esquema 1 esquema 2 Empregado

Empregado

(1,1)

(1,n)

(1,n)

(1,n)

Projeto Projeto de Banco de Dado

Projeto Profa. Marina Teresa Pires Vieira

27

2.4. Estudo de Caso: Gerenciamento de uma Biblioteca

Projeto de Visões – Biblioteca particular de pesquisadores Considere os esquemas conceituais a seguir que

têm por finalidade armazenar

informações sobre publicações (livros, revistas, etc) de uma biblioteca.

O esquema 1 representa informações de interesse de pesquisadores em suas bibliotecas particulares. Nesse esquema tem-se:

Publicação: publicações (artigos) mantidas pelos pesquisadores em seus gabinetes particulares. Elas são geralmente obtidas pelos pesquisadores através de contatos com os autores.

Tópico: refere-se às áreas de pesquisa de interesse dos autores.

Solicitado-por: relaciona artigos que foram enviados por autores, aos pesquisadores que os solicitaram.

O esquema 2 representa as informações a serem mantidas na biblioteca do Departamento desses pesquisadores:

Publicação: publicações presentemente mantidas na biblioteca.

Artigo: artigos publicados em revistas ou anais mantidos na biblioteca.

Comprado-pelo: indica que o pesquisador é responsável pelo recurso usado para comprar a publicação.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

28

nome

Tópico

de_interesse (1,n)

Pertence (1,n)

(0,n)

(1,n)

(0,n)

Grupo_ Pesquisa

Último-nome endereço

Autor (0,n) (1,n)

nome endereço

Nome endereço

Editora

(1,n)

Escrito_por

tem (1,1)

(1,n) Enviadopor

(0,n)

título palavra-chave (1,n) ano

Publicação

(0,n)

nome editor

Séries (1,n)

(1,n) Pertence_a

Solicitado -por

Publ-deinteresse

(1,1)

(0,n)

(1,n)

(1,n) ultimo-nome idade estado-nasceu

Pesquisador

(0,n)

número título ano

Livro

de-interesse (1,n)

Fig. 2.1 - (a) Esquema : Biblioteca Particular (esquema 1) (1,n)

cód-tópico nome

Tópico (1,n)

trata

Referente-a (1,1)

(1,n) (0,1)

Comprada-pelo

(1,n)

Artigo

Título autor (1,n)

Publicação

(0,1)

Pesquisador

Título estante

(0,1)

(0,n) Empresta

pertence -rev

(0,n)

(0,n) Último-nome posição grau cidade-nasceu

dia-emprestimo

Revista

Anais (0,n)

pertence -anais

Livro (1,1) Autor (1,n)

Publicado -por

(0,n)

Editora

nome endereço

Fig. 2.1 - (b) Esquema :Biblioteca do Depto (esquema 2)

Etapa1: Analisar os esquemas da Fig. 2.1 a e b, quanto a : a) conflito de nomes b) conflitos estruturais

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

29

Exemplo: Gerenciamento de uma Biblioteca - Esquemas modificados nome

Área_ pesquisa

de_interesse (1,n)

Pertence (1,n) (1,n)

(0,n)

(1,n)

(0,n) Último-nome endereço

Autor (0,n)

(1,n)

tem

(1,n) A-T

(1,1)

nome

(1,n)

título

Artigo

nome editor

Séries

Tópico

ano

(0,n)

Nome endereço

Editora

(1,n) (0,n)

nome endereço

(1,n)

Escrito_por

Enviadopor

Grupo_ Pesquisa

(1,n)

(1,n) Pertence_a

Solicitado -por

Artigo-deinteresse

(1,1)

(1,n)

número título ano

Livro

de-interesse (0,n)

(1,n)

(1,n) ultimo-nome idade estado-nasceu

Pesquisador (0,n)

Fig. 2.2 - (a) Esquema : Biblioteca Particular (esquema 1)

(1,n)

cód-tópico nome

Tópico (1,n)

trata

Referente-a (1,n)

(1,1)

(1,n)

Comprada-pelo

(1,n)

Artigo

Título

(0,1)

(0,1)

(0,n) Empresta

pertence -rev

(0,n)

Último-nome posição grau cidade-nasceu

dia-emprestimo

Revista

Anais

Livro

(0,n) pertence -anais

escrito_ por

Pesquisador

Título estante

Publicação

(0,1)

(0,n)

(1,1)

Publicado -por

(0,n)

Editora

nome endereço

(1,n) nome (0,n)

(0,n)

Autor

escrevelivro

Fig. 2.2 - (b) Esquema :Biblioteca do Depto (esquema 2) Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

30

Etapa 2: Fazer a integração dos esquemas. Esquema global após fusão dos esquemas: Biblioteca Particular e Biblioteca do Depto

título

Comprada-pelo (1,n)

Pesquisador

Publicação

(1,n)

(1,n) (1,1) Pertence_a número (1,n) ano

Série

(1,n)

Empresta (0,n)

dia-emprestimo

cód-tópico nome

Tópico

(0,n)

(0,1)

(1,n)

(1,1)

(0,n)

(1,1)

Referente-a

Livro (1,1)

último-nome posição grau cidade-nasceu estado-nasceu idade

estante

deinteresse

Anais (0,n)

(1,n)

Publicado -por

Revista (0,n)

pertence -anais

nome editor

pertence -rev

(0,n) de (0,1)

Editora (0,n)

título ano

(0,1)

Artigo

nome endereço

(0,n)

(1,n) trata

(1,n)

artigo-deinteresse

(0,n)

(0,n)

Solicitad o-por

(1,n) Escrito_por

Enviadopor

Escreve -livro

nome-area

(1,n)

(0,n)

ÁreaPesquisa

(0,n)

(1,n)

de_interesse

(1,n)

Autor (0,n) Pertence

(0,n)

(0,n)

Último-nome endereço

(1,n)

Grupo_ Pesquisa

nome endereço

Fig. 2.3 – Esquema global (fusão de Biblioteca Particular e Biblioteca do Depto

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

31

2.5. Melhorando a Qualidade de um Esquema de Banco de Dados Meta: Aplicar transformações para reestruturar o esquema para produzir uma versão melhor em termos das seguintes qualidades: a. b. c. d. e. f.

Ser completo Ser correto Ser mínimo Ser expressivo e auto explicativo Ser Legível Estar normalizado

2.5.1. Qualidades de um esquema de BD

a. Ser completo

•

esquema completo com relação aos requisitos: se todos os requisitos do domínio da aplicação estiverem representados no esquema.

•

requisitos completos com relação ao esquema: se cada conceito no esquema é mencionado nos requisitos.

b. Ser correto Um esquema é correto quando usa adequadamente os conceitos do modelo ER. Erros semânticos mais freqüentes: a. Usar um atributo no lugar de uma entidade. b. Esquecer de representar uma generalização. c. Esquecer a propriedade de herança das generalizações. d. Usar um relacionamento com um número errado de entidades (por ex: usar um relacionamento binário no lugar de um relacionamento ternário). e. Usar uma entidade ao invés de um relacionamento. f. Esquecer algum identificador de entidade, especialmente identificadores compostos externos. g. Omitir alguma especificação de cardinalidade mínima ou máxima.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

c. Ser mínimo nome data-nasc

Empregado (1,n) trabalha (1,n)

código gerente nro_empregados

Projeto

Um esquema é mínimo se não for possível eliminar qualquer conceito do esquema sem perder informação. Exemplo: •

nro_empregados pode ser eliminado sem afetar o conteúdo de informação do esquema, pois pode ser derivado a partir do esquema, logo o esquema não é mínimo.

•

Pode-se permitir alguma redundância no esquema, que deve ser documentada.

d. Ser expressivo e auto-explicativo Um esquema é expressivo quando pode ser entendido através dos construtores do esquema E-R, sem a necessidade de explicação adicional. Exemplo: Considere o esquema: Aluno (0,2)

tipo

tem orientador

Suponha que cada aluno tem, no máximo, um orientador de mestrado e um orientador de doutorado e um mesmo aluno pode ser um aluno de mestrado e de doutorado(em momentos diferentes). A informação acima está totalmente representada no esquema?

(0,n)

Professor

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

33

Solução:

e. Ser legível Respeitar critérios de estética que tornam o diagrama agradável. -

evitar cruzamento de ligações dos relacionamentos; evitar curvas hierarquias: pai deve ser colocado acima dos filhos.

A

mudar para

C

A

D

D C

B

B

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

34

2.5.2. Transformações de Esquema Esquema E1

transformações

Esquema E2

2.5.2.1. Transformações para conseguir minimalidade Situações que apresentam redundância na modelagem:

a) Ciclos de Relacionamentos

Exemplo: (1,1) Empregado (1,1) - Trab-no é redundante?

trab-com Trab-no

(1,n)

- Trab-com é redundante? - Dirige é redundante?

Diretor (1,n) Dirige (1,1) Departamento (1,n)

b) Atributos Derivados Atributos derivados podem ser omitidos de um esquema ER, porém podem ser úteis para melhorar a eficiência do banco de dados.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

35

c) Subconjuntos Implícitos Solução:

Empregado Empregado integração? Especialista em Computação

Analista

Analista

2.5.2.2. Transformações para conseguir Expressividade Expressividade: melhorada quando se simplifica o esquema. Transformações típicas para melhorar a expressividade: a) Eliminar Subentidades "Inexpressivas" em Hierarquias de Generalização: Inexpressivas: sem atributos ou com poucos Membro Ensino

Professor

Instrutor

Membro Ensino

categoria

Estudante Graduado

b) Eliminar Entidades "Inexpressivas"

(1,n)

(1,1) Pessoa

nascida

nome RG idade

Cidade

transformar para:

Pessoa

nome nome RG idade cidade_nasc

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

36

c) Criar uma Generalização Quando se tem entidades com propriedades semelhantes. No exemplo a seguir, Professor e Instrutor têm propriedades semelhantes. Professor

Seminário

dá

ensina

Assistente Ensino

dá

Curso

ensina

Instrutor

auxilia

Melhorando a expressividade: Membro Docente

Seminário

Professor

Assistente Ensino

Atividade

ensina

Curso

auxilia

Instrutor

d) Criar um Subconjunto nome nro (0,1) Empregado

nome nro

(1,1) Carro_da_ Compainha

dirige

tipo nro_licença ano

Transformar para:

Empregado

(1,1) Motorista

Projeto de Banco de Dado

(1,1) dirige

Carro_da_ Compainha

tipo nro_licença ano

Profa. Marina Teresa Pires Vieira

37

Obs: Quando for significativo para o projeto, pode-se usar a criação de um novo subconjunto quando a entidade possui cardinalidade mínima = 0 em um relacionamento. Isso enfatiza o papel da entidade. 2.5.2.3. Transformações para obter Normalização

2FN:

Pedido

nro-ped nro-peça custo qtde data

nro-ped data

Pedido (1,n)

qtde

P-P dependências funcionais: nro-ped, nro-peça à qtde nro-peça à custo nro-ped à data

(1,n)

Peça

nro-peça custo

Exemplo da 2FN para relacionamento: nro-aluno Aluno

nro-aluno Aluno

prof cursou

cursou média

média

cod-disc Discip

Discip

cod-disc prof

dependências funcionais: nro-al, cod-disc à prof, média cod-disc à prof

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

38

3FN: nro-e nome nro-depto nome-depto nro-divisão gerente

Empregado

Suposição: Cada departamento pertence a uma só divisão e cada divisão tem um gerente.

dependências funcionais: nro-e à nome, nro-depto, nome-depto, nro-divisão, gerente nro-depto à nome-depto, nro-divisão nro-divisão à gerente

Empregado

nro-e nome

Obs: 2 transitividades:

Pertence

nro-e à nro-depto à nro-divisão Departamento

nro-depto nome-depto

nro-depto à nro-divisão à gerente

Da

Divisão

Projeto de Banco de Dado

nro-divisão gerente

Profa. Marina Teresa Pires Vieira

39

Exemplo de relacionamento que viola a 3FN: verba

nome nro-e

nro-proj

Empregado

horas-trab

Trabalha

Depto (1,n)

(1,n)

nro-depto nome

nro-e, nro-depto : identificador de trabalha Para os atributos do relacionamento tem-se: nro-e, nro-depto à nro-proj, verba, horas-trab nro-proj à verba

=> não está na 3FN

Passando para a 3FN:

nome nro-e

horas-trab

Empregado

Trabalha

Depto (1,n)

(1,n)

nro_depto nome

(1,n)

Projeto

Projeto de Banco de Dado

nro_proj verba

Profa. Marina Teresa Pires Vieira

40

2.5.3. Reestruturação do esquema de Gerenciamento de uma Biblioteca Reestruturação do esquema da Fig. 2.3 (Etapa 3).

título

Comprada-pelo

(0,n)

(1,1)

(1,n) Referente-a

Pesquisador

Publicação Empresta

nasceuem

dia-emprestimo

cód-tópico nome

(1,n)

(1,n)

Trabalho_de_autoria

trata

(1,1)

(0,n)

(0,1)

(1,n)

Tópico

último-nome posição grau idade

estante

de-interesse

código ano

(1,n)

(1,n)

(1,n)

Cidade-nasc Publicaçãocoletiva

Escrito_por

Tipo-publ código nome estado

(1,n) contém (1,1)

número ano Pertence_a

Livro

(1,1)

(1,n)

Série (1,1)

título ano

Artigo

(0,n) (0,n)

(1,1)

(1,n)

(0,n)

Solicitado -por

(0,n)

Publicado -por

Enviadopor

nome editor (0,n) de (0,n)

Editora nome endereço (0,n)

(0,n)

(1,n)

Autor ÁreaPesquisa

(1,n)

de_interesse

(1,n)

Pertence

(0,n)

(1,n)

nome endereço

Grupo_ Pesquisa

Endereço Último_nome

nome-area

Fig. 2.4 - Esquema global após reestruturação

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

41

2.6. Exercício Deseja-se desenvolver o esquema conceitual do banco de dados de informações sobre os empregados de uma empresa. As informações sobre o sistema está organizado em 4 visões, conforme mostrado a seguir. a) Faça o esquema conceitual de cada uma dessas visões. Faça a integração das visões por etapas, conforme dado em aula. Para os esquemas intermediários, caso queira, indique somente as partes que mudam na nova etapa de integração, porém o esquema final deve ser completo. Siga as seguintes diretrizes: 1. Os esquemas devem satisfazer aos critérios de qualidade dados em aula. 2. FAÇA AS SUPOSIÇÕES QUE ACHAR NECESSÁRIAS E INDIQUE-AS (faça isso, por exemplo, para as cardinalidades de relacionamentos não definidas nas visões).

3. Use a notação dada em aula para desenhar os esquemas. Visão 1 - Formulário para cadastramento de empregado. Nome do empregado:____________________________________ Nro empr:________ Endereço:______________________________________________________________ RG:_________________________________ CIC: _____________________________ Data nascimento:_________________ nro carteira reservista: _____________________ Sigla Depto: ______ Nome Depto:___________________Data admissão:___________ Dependentes (no máximo 10): Nome dependente Data nascimento ________________________________________________ ________________________________________________ ________________________________________________ ... Se vendedor, indicar: região em que atua: ___________________ Porcentagem comissão:__________ Viajante regional

Projeto de Banco de Dado

Viajante nacional

Não é viajante

Profa. Marina Teresa Pires Vieira

42

Visão 2 - Formulário para cálculo de salário. Um empregado é horista ou mensalista. As informações de cada mês, constantes do formulário, devem ser mantidas no bd. Nome do empregado:________________________________ Sigla Depto:__________ Mês/Ano: ________________ Horista. Tipo Contrato:______ Horas Trabalhadas no mês:_____ Horas extras no mês:__________ Mensalista. Regime de Trabalho:________________

Total faltas no mês:______

Visão 3 - Informações sobre empregados e departamentos. Declaração dos registros em COBOL. 01 EMPREGADO. 02 NRO-EMP PIC X(5). 02 SALÁRIO PIC 9(4). 02 ENDEREÇO PIC X(30). 02 COD-DEPTO PIC X(4). 02 SECRETÁRIA. 03 FORMAÇÃO PIC X(10). 02 OPERADOR REDEFINES SECRETÁRIA. 03 TURNO PIC X(7). 02 ENGENHEIRO REDEFINES OPERADOR. 03 ESPECIALIDADE PIC X(10) OCCURS 3 TIMES. 01 DEPTO. 02 COD-DEPTO PIC X(4). 02 NOME-DEPTO PIC X(15). 02 LOCAL PIC X(10).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

43

Visão 4 - Histórico de vendas no atacado e varejo, de cada vendedor, nos últimos 5 anos. Nesse formulário são colocados os valores totais de venda em cada mês, no atacado e varejo.

Nome do Vendedor : ________________________________________ Sigla Depto: __________ Ano Tipo 1993 Atacado

Jan

Fev

Mar

Abr

Mai

Jun

Jul

Ago

Set

Out

Nov

Dez

Varejo 1998 Atacado Varejo 1999 Atacado Varejo 2000 Atacado Varejo 2001 Atacado Varejo

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

44

3. Projeto Lógico de Banco de Dados Projeto lógico de banco de dados é a fase do projeto de um banco de dados que visa desenvolver o esquema lógico do banco de dados, voltado para o modelo de dados a ser adotado para sua implementação. Se for adotado o Modelo Relacional (como é o caso aqui abordado), esta etapa irá gerar como resultado um esquema de banco de dados relacional, que contemple, da melhor forma possível, as necessidades dos usuários. Esse esquema lógico é gerado com base no esquema conceitual do banco de dados, que, por sua vez, foi gerado durante a etapa de projeto conceitual. Cabe a esta etapa de projeto lógico realizar transformações no esquema conceitual, visando torná-lo eficiente no atendimento às consultas mais freqüentes e/ou mais críticas que serão realizadas sobre o banco de dados. Assim, esta etapa está preocupada com o desempenho das operações a serem executadas no banco de dados. Pode-se destacar duas tarefas a serem realizadas nesta etapa: Refinamento do Esquema Conceitual e Mapeamento para o modelo de dados adotado. A seguir são apresentadas essas tarefas, aonde o termo entidade é utilizado no lugar de tipo de entidade, por questões de simplicidade de expressão. Pelo mesmo motivo utiliza-se relação no lugar de esquema de relação. 3.1. Refinamento do Esquema Conceitual O objetivo desta tarefa é reestruturar o esquema conceitual para obter um novo esquema no qual as operações mais importantes possam ser executadas mais eficientemente. Realiza-se um refinamento nas entidades do esquema, de modo a reduzir o número de acessos lógicos das operações às entidades e a quantidade de dados transferidos em operações de entrada e saída entre o meio de armazenamento e a memória. Para auxiliar a análise das operaçõesa realizadas sobre o banco de dados pode-se utilizar um esquema de navegação, conforme descrito na seção 3.4. É neceessário realizar uma estimativa do volume de acessos das operações para apoiar a decisão quanto a modificações no esquema do banco de dados, visando obter melhor desempenho. A seção 3 discute uma forma de realizar essa estimativa do volume de acessos.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

45

Deve-se observar, no entanto, que esse número de acessos lógicos e a quantidade de dados transferidos não são estimados precisamente neste ponto, pois dependem da descrição das estruturas físicas que serão projetadas numa fase posterior. Pode-se aplicar as seguintes transformações no esquema, visando otimizar a execução das operações: • particionamento de entidades, • fusão de entidades • reprodução de atributos 3.1.1. Particionamento de Entidades Subdivide-se em grupos os atributos que não fazem parte do identificador da entidade. Os atributos que irão compor cada grupo são escolhidos de acordo com as operações que usam o grupo de atributos. Para o particionamento de uma entidade, segue-se os passos:

Escolhe-se os atributos que são mais usados pelas operações que acessam a entidade através do seu identificador, formando um grupo com esses atributos, juntamente com o identificador. Esse grupo forma a sub-entidade principal.

Para cada grupo restante constrói-se uma sub-entidade, que é conectada à sub-entidade principal através de um relacionamento 1:1. A sub-entidade principal identifica as outras sub-entidades.

Cada relacionamento, do qual a entidade original participa, é transferido para a subentidade que usa o relacionamento mais freqüentemente.

Exemplo: Considere a entidade Vendedor, da figura 3.1 abaixo e suponha que as operações sobre essa entidade, na maioria, usam somente os atributos nome e endereço do vendedor e que apenas as operações que calculam a comissão dos vendedores e o relatório mensal de comissão de vendedor utilizamos outros atributos. Nessas condições, a entidade Vendedor é uma candidata ao particionamento, resultando nas entidades Dados-Comissão-Vendedor e Vendedor, mostradas na figura 3.2.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

46

nro-vendedor nome_vendedor endereço comissão_total taxa_comissão

Vendedor

(o,n) atende (1,1) nro-cli nome_cli ender_cli saldo lim_cred

(o,n)

Cliente

(1,1) faz

Pedido

Fig. 3.1

nro-ped

Dados_comissão_ Vendedor

data

comissão_total taxa_comissão

(1,1) é_do (1,1)

(o,n)

nro-vendedor nome_vendedor endereço

Vendedor

atende

(1,1) nro-cli nome_cli ender_cli saldo lim_cred

(o,n) Cliente

faz

(1,1) Pedido

nro-ped

data

Fig. 3.2

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

47

3.1.2. Fusão de entidades Para reduzir o número de acessos lógicos, pares de entidades conectadas por um único relacionamento 1:1 são examinados para decidir de devem ser fundidos.

As operações que utilizam as entidades são divididas em duas classes: -

operações que usam o relacionamento e ambas as entidades,

-

operações que usam somente uma das entidades.

Se o número de acessos lógicos na primeira classe for maior que na segunda classe, as entidades são fundidas em uma nova entidade que herda todos os atributos das originais. 3.1.3. Reprodução de atributos Os atributos a1, a2, ..., an de uma entidade x são candidatos a uma reprodução em uma entidade y conectada a x, se a presença de a1, a2, ..., an em y elimina a necessidade de travessia de x para y em algumas operações. A reprodução de atributos força a introdução de operações adicionais para manter a consistência mútua dos dados. Deve-se, portanto, pesar as vantagens e desvantagens da sua utilização. Além das transformações discutidas acima pode-se também usar atributos derivados para otimizar a execução de operações. 3.2. Mapeamento para o Modelo Relacional O objetivo dessa tarefa é gerar o esquema relacional com base no esquema refinado (resultado da aplicação das transformações discutidas no item anterior). A atividade básica realizada aqui refere-se à tradução das entidades e dos relacionamentos do esquema refinado, para o esquema relacional. Para tanto, seguem-se as regras:

a. Entidades com identificação externa Para todos os pares de entidades x e y do esquema refinado, tal que x identifica externamente y, constrói-se:

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

48

-

uma relação correspondente à entidade x,

-

uma relação correspondente a y, contendo o identificador da entidade x.

Os identificadores principais das entidades tornam-se as chaves primárias das relações correspondentes e são sublinhados no esquema relacional. O identificador de y será o conjunto de atributos formado pelo identificador de x com o identificador de y.

b. Demais entidades As outras entidades restantes do esquema, após realizado o passo a anterior, são mapeadas para relações correspondentes.

Nos passos a e b, os atributos compostos e os multivalorados são tratados da seguinte forma: -

atributos compostos: incluir na entidade somente seus atributos simples

-

atributos multivalorados: para cada atributo multivalorado m criar uma relação contendo esse atributo e a chave primária ch da relação original. Se o atributo multivalorado m não for composto (atributo a3 do esquema do exemplo a seguir), a chave primária da nova relação será composta pela chave primária ch e o atributo m. Se o atributo multivalorado m for composto (atributo a4 do exemplo), a chave primária será composta pela chave primária ch juntamente com um subconjunto do conjunto de atributos que compõem m. Exemplo: a1 a2 A

a21 a22

a3 (1,n) a4(1,n)

a41 a42 a43

Mapeamento da entidade A:

A(a1, a21, a22) A1(a1, a3)

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

49

A2(a1, a41, a42, a43) à supondo que são necessários os 3 atributos (a1, a41 e a42) para identificar, de forma única, a entidade A2.

c. Relacionamentos •

1:1

Escolher uma das entidades (por ex. E1) que participam do relacionamento para conter como chave estrangeira a chave primária da outra entidade (por ex.E2). Aconselha-se escolher uma entidade com participação total no relacionamento para fazer o papel de E1. Se houver atributos no relacionamento, inclui-los em E1. • 1:N (não fraco) Incluir na relação do lado N a chave primária do lado 1, tornando-se esta uma chave estrangeira da relação. Incluir os atributos do relacionamento na entidade do lado N. Obs: Essa regra refere-se ao uso de cardinalidade 1:N. No caso de uso da restrição (min, max) para a cardinalidade do relacionamento, a chave primária da relação do lado indicado com (min,N) deve ser incluida como chave estrangeira na relação do lado (min,1). • M:N Criar uma nova relação para representar o relacionamento. Os atributos que irão compor esta relação serão as chaves primárias das duas relações participantes do relacionamento (estes irão formar a chave primária desta nova relação), juntamente com os atributos do relacionamento. • Relacionamentos n_ários, n>2 O tratamento é análogo ao de relacionamento N:M. A chave primária geralmente é a combinação das chaves primárias das entidades participantes do relacionamento.

Obs: O tratamento dado para relacionamentos N:M pode ser adotado para relacionamentos 1:1 e 1:N quando existem poucas instâncias envolvidas no relacionamento (isto é, quando se trata de relacionamento parcial).

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

50

d. Tratamento de hierarquias de generalização Considere a hierarquia de generalização da figura a seguir.

C

S1

S2

ch a1 a2 ... an

...

Sm

O mapeamento dessa hierarquia para esquemas de relação pode seguir uma das opções abaixo:

d1. Criar um esquema de relação correspondente a C: C (ch, a1, a2, ..., an) e um esquema de relação para cada Si, i=1,...,m : Si (ch, atributos de Si ) d2. Somente criar um esquema de relação Ri para cada subclasse Si, i=1,...,m: Ri (ch, a1, a2, ..., an, atributos de Si ) Esta opção é adequada para hierarquias de generalização com cobertura total e exclusiva. Um inconveniente dessa opção é que para toda busca interessada em recuperar uma entidade arbitrária em C, deve-se pesquisar todas as m relações Ri. d3. Criar uma só relação R, colocando em R os atributos de todas as entidades envolvidas na generalização. Neste caso pode-se ter as seguintes situações: d3a. As classes são disjuntas: cria-se um atributo t para indicar a qual subclasse uma determinada tupla de R pertence. R(ch, a1, a2, ..., an, atributos de S1, atributos de S2, ..., atributos de Sm, t)

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

51

d3b. As classes se sobrepõem: R(ch, a1, a2, ..., an, atributos de S1, atributos de S2, ..., atributos de Sm, t1, t2,..., tm) onde cada ti pode ser um atributo booleano para indicar se a tupla pertence ou não à subclasse Si. O uso da opção d3 é válido quando o número de atributos das subclasses é pequeno. No geral, essa opção gera um grande número de valores nulos.

e. Mapeamento de Hierarquias de Herança Múltipla Para hierarquias de herança múltipla pode-se usar qualquer opção dada. Usualmente utiliza-se a opção 1, como a seguir. Considere o esquema abaixo: ch1

C1

C2

ch2

S

Utilizando a opção 1, o mapeamento desse esquema gera o seguinte conjunto de relações: C1(ch1, atributos da entidade C1) C2(ch2, atributos da entidade C2) S (ch1, ch2, atributos da entidade S) A chave primária de S pode ser a de C1 ou de C2. Exemplo:

nro-al nome curso

Aluno

Professor

AssistenteEnsino Projeto de Banco de Dado

RG nome cargo

previsão_fim_curso horas_disponíveis

Profa. Marina Teresa Pires Vieira

52

Aluno(nro-al, nome, curso) Professor(RG, nome, cargo) Assistente_Ensino(nro-al, RG, previsão_fim_curso, horas_disponíveis)

3.2.1. Exercícios – Mapeamento 1. Faça o mapeamento do esquema conceitual do exercício 8, do capítulo 1. 2. Faça o mapeamento do esquema conceitual da figura 2.4.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

53

3.3. Tópicos Adicionais sobre Modelagem de Dados e Mapeamento Análise de diferentes situações

3.3.1.Categorização - mapeamento Em algumas situações de modelagem, surge a necessidade de modelar um único relacionamento de superclasse/subclasse, com mais que uma superclasse, onde as superclasses representam diferentes tipos de entidade. Nesse caso chamamos a subclasse uma categoria.

Exemplo: Suponha que temos três tipos de entidades: PESSOA, BANCO E COMPANHIA. Em um banco de dados para registro de veículos, um proprietário de um veículo pode ser uma pessoa, um banco ou uma companhia. Precisamos criar uma classe que inclui entidades de todos os três tipos para exercer o papel de proprietário de veículo. Cria-se uma categoria PROPRIETÁRIO que é uma subclasse da união das três classes PESSOA, BANCO E COMPANHIA. A figura 3.3, a seguir, representa uma categoria. As superclasses PESSOA, BANCO E COMPANHIA são conectadas ao círculo com um símbolo U, indicando uma operação de união de conjunto. Na figura 3.3 tem-se duas categorias: PROPRIETÁRIO, que é uma

subclasse

da

união

de

PESSOA,

BANCO

e

COMPANHIA

e

VEÍCULO_REGISTRADO, que é uma subclasse da união de CARRO e CAMINHÃO. VEÍCULO_REGISTRADO inclui alguns carros e alguns caminhões, mas não necessariamente todos eles (alguns carros ou caminhões podem não ser registrados).

Uma categoria pode ser: a) Total ou parcial; b) As superclasses podem ter diferentes atributos chaves (como na categoria PROPRIETÁRIO) ou o mesmo atributo como em veículo registrado; c) Quando a categoria é total ela pode ser alternativamente representada como uma generalização. Se duas classes representam os mesmos tipos de entidades e compartilham muitos atributos, incluindo o mesmo atributo chave, generalização/ especialização é preferido, caso contrário a categorização é mais apropriada.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

54

nomeC enderC nomeB enderB nome RG ender nro_cart_mot

Pessoa

id_veículo fabricante ano estilo

Banco

Companhia

id_veículo tonelagem

ano

U Carro

Caminhão Proprietário

(1,n) U Possui nro_chassi

data_compra

VeículoRegistrado (1,n)

Fig. 3.3 - Exemplo de Categorização

Fazer o mapeamento das categorias para o modelo relacional.

3.3.2. Atividades de extensão - fazer o esquema conceitual Deseja-se armazenar os projetos de extensão desenvolvidos pelos docentes de uma universidade. Todo Projeto possui: um número, ano (número e ano identificam um projeto), nome do projeto, público alvo e responsável. Projetos nào existem por si, eles são compostos por pelo menos uma atividade. As atividades de extensão que podem compor um projeto são: Cursos: tipo do curso, carga horária, data de início, data de final do curso; Reuniões: número de inscritos, número de participantes, taxa cobrada, período; Eventos: número de participantes, tipo do evento (congresso, simpósio, seminário,etc) Serviços: receita envolvida (cobrada), tipo de serviço.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

55

Um projeto pode consistir de qualquer conjunto de atividades entre as listadas, podendo inclusive conter uma mesma atividade mais de uma vez. 3.3.3. Docentes e Docentes Bolsistas - fazer o esquema conceitual Deseja-se desenvolver um bd para armazenar informações sobre todos os docentes de uma universidade. Se o docente já obteve algum tipo de bolsa (de mestrado, doutorado, pós-doutorado) deseja-se guardar informações complementares a respeito de sua situação como bolsista. De cada docente deseja-se guardar: um código de identificação, nome, CPF, RG e cargo. Para docentes bolsistas deseja-se guardar: tipo da bolsa (mestrado/doutorado/pósdoutorado), instituição de desenvolvimento do trabalho, local, data de início e de fim da bolsa. Um docente pode ter sido bolsista de uma mesma categoria mais de uma vez, em períodos diferentes envolvendo a mesma instituição de desenvolvimento do trabalho ou não.

1) Agregação - Fazer mapeamento Deseja-se desenvolver um banco de dados para armazenar informações sobre entrevistas de candidatos a trabalhos para várias companhias. Para Companhia deseja-se guardar nome e endereço. Para candidatos: nome, RG, telefone e endereço. A cada entrevista realizada com um candidato deve-se guardar a data e nome da pessoa da companhia que realizou a entrevista. Algumas entrevistas geram ofertas de trabalho, envolvendo as informações: tipo de trabalho, descrição. nome endereço (1,n) Companhia

(1,n) Candidato

entrevista

RG Nome Fone ender

data entrevistador

(0,n) resulta-em

(0,n) Trabalho

tipo_trabalho descrição

Fig. 3.4 - Agregação Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

56

3.3.4. A modelagem satisfaz aos requisitos dos usuários? a) Dado o esquema abaixo,indique quais das consultas a seguir retornam informações corretas: •

Dado o número de um aluno, recupere o nome das disciplinas que ele está cursando atualmente e os nomes dos professores que estão dando aula para esses alunos.

•

Dado o nome de um departamento, recupere os nros dos alunos que são orientados por professores, desse departamento, que atuam na área de pesquisa "Banco de Dados".

•

dado o RG de um professor, recupere o nome dos alunos que estão cursando suas disciplinas.

nome idade RG

pertence (1,n)

Pessoa

sexo nro-al

Aluno

(0,n)

Professor

fone nomedepto estado-civil titulação (1,n) área-pesquisa (1,n)

orienta

(1,n) curso

Cursa

Aprovado

Cod-disc nome

(1,n)

Alunograduado

(1,n)

(1,n)

(1,n)

(1,1)

Depto

AlunoDoutorado

titulo-tese ano-ingresso

ministra

Disciplina (1,n) Fig. 3.5

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

57

b) O esquema a seguir atende à consulta: "Selecione os nomes dos clientes que compraram a revista "Veja" na livraria Saraiva1." ?

(1,n)

frequenta

Livraria (1,n) nome RG fone

nome_livr endereço fone

(1,n)

vende

Cliente

(1,n)

(1,n) compra

(0,n) Publicação

cod_publ nome_publ editora

Fig. 3.6 3.3.5. Elaboração de Esquemas Conceituais a) Deseja-se fazer a modelagem de um banco de dados envolvendo peças e projetos, onde para cada peça deseja-se guardar o código da peça, a descrição e o conjunto de cores existentes para a peça; para cada projeto deseja-se guardar o código do projeto, uma descrição e o nome do coordenador do projeto. Um projeto usa várias peças e uma peça pode ser usada por diferentes projetos. Faça o esquema conceitual desse problema, supondo-se que deseja-se guardar também o preço das peças nas seguintes situações: a1) Para diferentes projetos, uma peça pode possuir diferentes preços. a2) Para diferentes projetos, uma peça pode possuir diferentes preços e para um mesmo projeto, uma mesma peça pode ser usada em vários tamanhos distintos , possuindo preços diferentes dependendo do tamanho, porém o mesmo código.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

58

b) Deseja-se manter um banco de dados de empregados, departamentos e projetos. Dos empregados deseja-se manter seu número (único para cada empregado), nome, endereço e informações sobre as funções exercidas nos projetos (código da função, nome, descrição). Dos departamentos mantém-se a sigla (que identifica cada departamento), descrição, localização, nomes de suas repartições e data de início e de término de mandato de cada diretor. Cada departamento possui apenas um diretor em um determinado momento, que é um empregado do departamento. Um mesmo empregado pode ter sido diretor mais de uma vez em épocas diferentes. Todo departamento possui um diretor. Dos projetos guarda-se seu código, descrição e o departamento a que pertence. As seguintes condições devem ser contempladas: a) Um departamento desenvolve vários projetos e todos os empregados do departamento trabalham em todos os projetos desenvolvidos em seu departamento. b) Os empregados só trabalham em projetos desenvolvidos em seu departamento e cada empregado trabalha em um departamento específico. c) Cada empregado exerce certas funções em certos projetos (isto é, em projetos distintos, um mesmo empregado pode exercer funções diferentes) e em cada projeto um empregado pode exercer mais de uma função. d) Não existe departamento sem empregados. e) Pode existir departamento que não desenvolve nenhum projeto. Faça o esquema conceitual desse banco de dados usando o modelo EntidadeRelacionamento.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

59

3.4. Esquema de Navegação para Operações de Banco de Dados Uma operação de banco de dados é uma interação elementar com o banco de dados (através de um comando de recuperação ou atualização), que não inclui comandos de controle (tais como, if-then-else, while-do, repeat-until). Essas operações podem ser realizadas através de uma única chamada ao sistema de banco de dados. Num sistema de banco de dados, as operações são realizadas como parte das atividades que compõem um processo. Para cada operação desenha-se um esquema de operação, que é um subconjunto do esquema do banco de dados contendo todos os elementos que são mencionados na especificação da operação ou que são requeridos para navegação, se eles não são explicitamente mencionados. Um esquema de operação inclui: -

atributos usados para condições que governam a seleção de entidades e relacionamentos,

-

atributos usados por operações de recuperação ou atualização,

-

entidades ou relacionamentos nos quais são inseridas ou eliminadas instâncias ou entidades das quais serão recuperadas instâncias,

-

relacionamentos ou hierarquias de generalização que são usadas para navegação.

Um esquema de navegação é um esquema de operação acrescido de símbolos especiais (exemplificado na figura 3.7): -

setas apontando para os atributos: indicam os atributos que são usados nas condições de seleção;

-

setas ao longo dos relacionamentos: indicam a navegação no banco de dados;

-

Letras R (Retrieval), U(Update), I(Insert) e D(Delete) dentro das entidades ou dos relacionamentos indicam a operação de acesso ao banco de dados.

R

E1

a1

R

E2

R

R

a3 a4

a2 Fig. 3.7. Um esquema de navegação

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

60

O esquema de navegação da figura 3.7 corresponde à consulta em SQL:

SELECT a3, a4 FROM E1, R, E2 WHERE condição-a1 AND condição-a2 AND condições de junção Exemplo. Considere o esquema de banco de dados da figura 3.8 e a seguinte operação a ser realizada nesse banco de dados: "Selecione todos os nomes de alunos que tiveram média inferior a 6.0 em pelo menos uma disciplina de 2 créditos". O esquema de navegação correspondente a essa consulta é mostrado na figura 3.9.

(0,n)

(0,n) cursa

nroal nome

Disciplina

Aluno cursou (0,n)

nome-trab orientador (0,1) data-defesa

(0,n)

cod nome créditos

média

AlunoMestrado

ano-sem

Fig. 3.8 - Banco de dados de alunos e disciplinas

nome

Aluno

(0,n) R

(0,n)

Disciplina R

cursou

R média

créditos

Fig. 3.9 - Esquema de navegação da consulta

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

61

O esquema de navegação é útil para as fases subsequentes do projeto do banco de dados, em particular para projeto lógico. Na construção de esquemas de navegação deve-se considerar as seguintes questões: a) existem operações que podem gerar mais do que um esquema de navegação; b) a mesma entidade ou relacionamento pode ser usada em diferentes maneiras pela mesma operação e isso resulta em uma replicação da entidade ou relacionamento no esquema de navegação.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

62

3.4.1. Exercícios - Esquema de Navegação Para o esquema de bd da figura 3.8, faça o esquema de navegação das consultas a seguir. 1. Selecionar os nomes dos alunos de mestrado que tiveram média inferior a 6.0 em pelo menos uma disciplina de 2 créditos”. 2. Selecionar os nomes das disciplinas que o aluno “João da Silva”cursou ou está cursando.

Para o esquema da figura 3.10 faça os esquemas de navegação para as operações a seguir. nascida

(1,1) RG nome profissão data-nasc sexo (0,1) nome_cônjuge

Pessoa

cart-reservista

Cidade

mora

(0,m) (p,e)

Homem

(0,m)

(0,m) cod

estado habitantes nome

Mulher Casada

nome-solteira

Fig. 3.10 3. Recuperar os nomes de todos os programadores que moram em cidades com menos que 1000 habitantes.

4. Dado o RG e nome de casada de uma pessoa do sexo feminino existente, atualizar seu nome e inserir seu nome de solteira.

5. Selecionar os nomes dos físicos que moram na mesma cidade em que eles nasceram.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

63

3.5. Modelagem da Carga do Banco de Dados O termo "carga do banco de dados" será empregado para significar as atividades ou aplicações que serão realizadas sobre o banco de dados. Para caracterizar a carga serão utilizados: o volume dos dados e a descrição das aplicações. O volume dos dados é medido no modelo ER pela determinação das seguintes informações: -

N(E) - número médio de instâncias da entidade E

-

N(R) - número médio de instâncias do relacionamento R;

-

cardinalidade média card-media(E,R) (média, para simplificar) de cada entidade E em cada relacionamento R que ela participa.

Num esquema, a informação sobre o volume dos dados pode ser representada conforme nro-cli nome fone(1,n) endereço saldo-líquido nro-de-contas limite-crédito

15.000 Cliente

mostrado na Figura 5, aonde são definidos: o número médio estimado de

instâncias

número

das

médio

entidades; estimado

o de

instâncias dos relacionamentos; e as

(1,n) média=2

cardinalidades 30.000 mantém

médias

de

cada

relacionamento, para cada instância de

(1,3) média=1.5

20.000 Conta

entidade

envolvida

no

relacionamento, definidas ao lado nro-conta saldo-conta

das cardinalidades mínima e máxima dos relacionamentos. O esquema da

(1,n) média=40

Figura 5 representa um banco de dados de contas bancárias, com uma

800.000 possui

média de 15.000 clientes, 20.000 contas e 600.000 transações. Para (1,2) média=1.33

600.000 Transação

cada cliente há, em média, 2 contas; nro-trans data tipo valor

Figura 5 -Esquema com informação sobre volume de dados Projeto de Banco de Dado

para cada conta há uma média de 1.5 clientes transação

e

40

transações;

cada

está relacionada, em

Profa. Marina Teresa Pires Vieira

64

média, com 1.33 contas (isso ocorre porque algumas transações envolvem mais do que uma conta: por exemplo, transferência de conta entre conta corrente e poupança, poupança e empréstimo, etc.). A cardinalidade média é calculada por: card-média = N(R)/ N(E)

A carga da aplicação

é estimada em termos das operações importantes

(transações em batch e on-line, bem como consultas ad hoc). Para muitos casos práticos, é impossível ter uma idéia precisa sobre a futura carga sobre o banco de dados. Deve-se, portanto, estimar a carga em termos das operações mais usadas. Cada operação é descrita por: -

seu esquema de navegação;

-

a freqüência média de ativação da operação, medida em uma unidade apropriada (por exemplo,100 vezes ao dia, 5 vezes ao mês);

-

o tipo da operação (se em batch ou on line).

Os exemplos aqui apresentados irão considerar que as operações são on-line. As informações acima são utilizadas para a elaboração de uma tabela de volume de acesso das operações. Essa tabela possui as seguintes informações (ver tabela 1): o nome da operação (um nome abreviado para a transação ou consulta); a freqüência (indica quão freqüentemente a operação ocorre, em média); o nome do conceito (refere-se ao conceito acessado no esquema de navegação); tipo do conceito (E, se entidade ou R, se relacionamento); read/write (indica se o acesso é de leitura ou escrita); e número médio de ocorrências acessadas. Para o exemplo de contas bancárias, suponha que as seguintes operações são realizadas:

O1: Abrir uma conta para um novo cliente (ou clientes, no caso de conta conjunta). Freqüência: 100 vezes ao dia. O2: recuperar o saldo líquido de um cliente. Freqüência: 3000 vezes ao dia. O3: Mostrar as últimas 10 transações de uma conta. Freqüência: 200 vezes ao dia. O4: Retirar dinheiro de uma conta. Freqüência: 2000 vezes ao dia. O5: Depositar dinheiro em uma conta. Freqüência: 1000 vezes ao dia. Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

65

A tabela a seguir apresenta o volume de acessos lógicos da operação O1, avaliado com base no esquema de navegação. Complete a tabela para as outras operações. Legenda: Op: nome da operação

E/R: Tipo do conceito (Entidade ou Relacionamento)

R/W: Read ou Write

Tabela de volume de acessos lógicos das operações Op

Freq

Conceito

O1

100 vezes/ dia Conta

E/R R/W Média de ocorrências acessadas E

W

100

Mantém

R

W

100 x 1,5 = 150

Cliente

E

W

100 x 1,5 = 150

O2

O3

O4

O5

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

66

4. Projeto Físico de Banco de Dados Estruturas de Armazenamento - OpenIngres 4.1. HEAP

• • • • •

Tabela desordenada Inserção: rápida (linha adicionada no fim da tabela) Adequada para carregar inicialmente a base de dados Fator de preenchimento: 100% Não deve ser usada para grandes tabelas, a não ser que se crie índices secundários

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

67

4.2. HASH modify employee to hash on age

• • •

Função hash para calcular o endereço, com base em uma chave. É o método de acesso mais rápido para consultas de match exato Não adequado para os tipos de buscas: - match não exato: Recuperar empregados com idade>50 => toda a tabela precisa ser pesquisada

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

68

-

baseadas em faixas de valores. Ex: encontrar empregados com números entre 50 e 100.

-

que utilizam só parte da chave. Ex: chave = (age,name) à Modify employee to hash on age, name Select * From employee Where employee.age = 28 => busca sequencial!

-

que utilizam pattern matching. Ex: Select * from employee Where name = ‘Mar%’

•

fator de preenchimento = 50%

•

Utilização: quando a recuperação das linhas é feita com base em valores conhecidos da chave.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

69

4.3. ISAM

• • •

•

Índice estático, nro estático de páginas na tabela principal Novas inserções são colocadas : - no final, se valor chave>todos os existentes, ou - em área de overflow Suporta: - pattern matching (‘MA%’) - busca por faixa de valores - busca através de chave parcial (desde que usada a parte mais da esquerda) - busca por match exato Utilização: para tabelas predominantemente estáticas.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

70

4.4. Btree

• • •

O índice da Btree é dinâmico, crescendo com a tabela. Nível de índice = Isam Diferença da Btree para Isam: - índice Isam aponta para página de dados - nível de índice Btree -> aponta para as páginas folhas.

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

71

•

Permite buscas com: - faixa de valores - pattern matching

•

RESUMO:

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

72

4.5. Índices Secundários Create [unique] index [schema.] nome-indice on nome-tabela (nome-coluna {, nome-coluna}) [with opções] •

Custos adicionais nas operações: atualização, eliminação e inserção.

•

Fatores a serem levados em conta: - nro de vezes em que a consulta é executada - tempo de resposta aceitável - importância da consulta

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

73

5. Bibliografia . BATINI, C.; CERI, S. & NAVATHE, S.B. - Conceptual Database Design. The Benjamin/Cummings Publishing Company, Inc., 470pp., 1992. . ELMASRI, R. & NAVATHE, S.B. - Fundamentals of Database Systems. AddisonWesley Publishing Company, 3a. edição, 955 pp., 2000. . KORTH, H.F. & SILBERSCHATZ, A. - Sistema de Bancos de Dados. Makron Books, 2a. edição revisada, 754 pp., 1995. . RAMAKRISHNAN, R. - Database Management Systems. McGraw -Hill Companies, Inc., 741pp., 1998. . OpenIngres - Manual de utilização

Projeto de Banco de Dado

Profa. Marina Teresa Pires Vieira

74