Campos, Ricardo. (2008). Apresentação de Bases de Dados. Access slides. Bases de Dados

(1)

Bases de Dados

© Ricardo Campos [ h t t p : / / w w w . c c c . i p t . p t / ~ r i c a r d o ]

Bases de Dados

Ricardo Campos (ricardo.campos@ipt.pt)

Esta apresentação foi desenvolvida por Ricardo Campos, docente do Instituto Politécnico de Tomar. Encontra-se disponível na página web do autor no link Publications ao abrigo da seguinte licença:

Mais detalhes em: http://creativecommons.org/licenses/by-nc/3.0/deed.pt

O seu uso, de parte ou da totalidade, pressupõe a utilização da seguinte referência:

Campos, Ricardo. (2008). Apresentação de Bases de Dados. Access 2007. 260 slides.

A sua disponibilização em formato PPT pode ser feita mediante solicitação (email: ricardo.campos@ipt.pt)

(2)

Bases de Dados

Bibliografia

Recursos:

J.L.Pereira

“Tecnologia de Bases de Dados” FCA – Editora de Informática D, Luis

“SQL – Structured Query Language” FCA – Editora de Informática

Bibliografia

Recursos on-line (Bases de Dados e SQL):

Nota: Não é possível garantir a existência futura dos endereços (URL’s), citados nos recursos on-line. Como se sabe, os endereços são virtuais e a sua vida útil depende única e exclusivamente dos seus autores e proprietários.

Tutoriais, http://apostilando.com/sessao.php?cod=18

Linha de Código SQL, http://www.linhadecodigo.com.br/desenvolvimento/sql.asp Noções da Linguagem SQL,

(3)

Bases de Dados © Ricardo Campos [ h t t p : / / w w w . c c c . i p t . p t / ~ r i c a r d o ]

Sumário

Objectivos

Parte Teórica Parte Prática

Bibliografia

Programa

Objectivos

Diagrama de entidade-relação (modelo conceptual) para a representação do modelo de dados;

Introdução às bases de dados relacionais (modelo lógico) e à normalização;

Discussão dos conceitos relacionados com bases de dados; Objectivos

Programa Parte Teórica Parte Prática

Aprendizagem das cláusulas mais importantes da linguagem SQL, quer para a criação da estrutura da base de dados (DDL) quer para a exploração dos dados (DML);

Capacidade de espelhar os conhecimentos teóricos num projecto de desenho e construção de uma base de dados utilizando um SGBD relacional.

(4)

Bases de Dados

Objectivos

Levantamento das necessidades de informação;

Modelação de dados (diagramas E-R e normalização de dados); Implementação de uma base de dados em Microsoft Access 2007;

Parte Teórica Parte Prática Objectivos Programa

Programa

Objectivos Programa

Conceitos de Bases de Dados

Ficheiros vs SGBDs;

Níveis de Análise do Sistema de Informação; Bases de Dados;

Tipos de Bases de Dados.

Modelo Relacional

Normalização de uma Base de Dados; Modelo Relacional (nível lógico); Modelo E-R (nível conceptual);

(5)

Bases de Dados

Programa

Desenho de um Esquema de Bases de Dados

Desenho de uma base de dados para uma biblioteca;

Desenvolvimento de um esquema de BD em Access 2007

Criação de programas de aplicação com interfaces gráficas recorrendo ao Access 2007; Objectivos Programa

Programa

Álgebra Relacional

Operações Unárias. Operações Binárias;

Linguagem SQL

Definição de SQL; Comandos DDL e Comandos DML; Objectivos Programa

(6)

Programa

Transacções e Recuperação

Definição de Transacção;

Definição de Recuperação e Tolerância a Falhas; Mecanismos de Recuperação; Tipos de Falhas; Objectivos Programa

Conceitos de Bases de

Dados

(7)

Bases de Dados

Sumário

Sumário

Conceitos de Bases de Dados

Ficheiros vs SGBDs;

Níveis de Análise do Sistema de Informação; Bases de Dados;

Tipos de Bases de Dados.

Motivação

Porque é que alguém que não quer ser profissional de sistemas de informação, deve aprender bases de dados?

A criação de sistemas de informação é um processo que envolve pessoas de várias áreas. Ter conhecimentos em bases de dados, facilitará a comunicação com os profissionais de SI.

Níveis de Análise do Sistema de Informação Ficheiros vs SGBDs Tipos de Bases de Dados

Bases de Dados Motivação

Definição Aplicações

Pelo mesmo motivo pelo qual se devia aprender a usar um computador há 10 anos atrás;

(8)

Bases de Dados

O que é uma Base de Dados?

A World Wide Web.

De uma forma genérica uma base de dados é uma estrutura que guarda dados:

O objectivo de uma base de dados é possibilitar de uma forma rápida e automática o acesso aos seus dados:

Procurar a página WWW do CET.

Níveis de Análise do Sistema de Informação Ficheiros vs SGBDs Tipos de Bases de Dados

Bases de Dados Motivação

Definição

Aplicações

Uma agenda com moradas e contactos telefónicos; Uma lista de CDs;

Procurar a morada de uma determinada pessoa;

Procurar o nome da pessoa que canta determinada música;

Bibliotecas: registo de livros, revistas.

Aplicações de Bases de Dados

Níveis de Análise do Sistema de Informação

Ficheiros vs SGBDs Tipos de Bases de Dados

Bases de Dados Motivação Definição

Aplicações

Banca: todas as transacções e movimentos; Companhias áreas: reservas, horários; Universidades: inscrições, notas; Vendas: clientes, produtos, compras; Indústria: inventário, pedidos;

(9)

Bases de Dados

Nível Conceptual (Modelo E-R)

Nível Lógico (Modelo Relacional)

00100110001100010001000

Nível Físico

Nível Conceptual, Lógico e Físico

Ficheiros vs SGBDs Tipos de Bases de Dados Bases de Dados

Gestão de Ficheiros vs SGBDs

Para a organização dos dados existem 2 sistemas: Sistema de Gestão de Ficheiros

Sistemas de Gestão de Bases de Dados Criados e mantidos pelas aplicações.

No qual se verifica uma certa independência entre as aplicações e os dados, sob a mediação do SGBD.

Bases de Dados S G B D

Sistema de Gestão de Ficheiros Sistema de Gestão de Bases de Dados Níveis de Análise do Sistema de Informação

Ficheiros vs SGBDs

Tipos de Bases de Dados Bases de Dados

(10)

Bases de Dados

Definição de Sistema de Gestão de Ficheiros

Uma das características principais dos Sistemas de Gestão de Ficheiros é que cada aplicação é responsável pela criação e gestão da estrutura interna da informação dos seus próprios ficheiros. Desta forma, qualquer alteração na estrutura de ficheiros implica uma alteração no código de cada uma das aplicações;

Cada tipo de ficheiro possui a sua própria estrutura (docs como o Word e Excel têm estruturas diferentes) e é a aplicação que os cria que é responsável pela sua definição da estrutura interna desses ficheiros.

Sistema de Gestão de Ficheiros

Sistema de Gestão de Bases de Dados Níveis de Análise do Sistema de Informação

Desvantagens do Sistema de Gestão de Ficheiros

Sistema de Gestão de Bases de Dados

Redundância e Inconsistência dos dados

Considere um sistema em que a informação relativa à morada dos clientes é registada em dois ficheiros diferentes. Se o cliente muda de morada e a mesma é apenas actualizada num dos ficheiros, o sistema passa a conter informação inconsistente;

Múltiplos formatos, duplicação da informação em ficheiros diferentes; No processamento tradicional de ficheiros (Sistema de Gestão de Ficheiros), cada aplicação mantém o seu próprio conjunto de ficheiros e dados, provocando a ocorrência de redundâncias;

A redundância leva ao armazenamento excessivo de informações, ocupando espaço que poderia estar a ser utilizado com outras informações e dificulta a integridade da informação;

(11)

Bases de Dados

Atomicidade das alterações

Falhas podem colocar a base de dados num estado inconsistente, com alterações parciais já efectuadas.

Explo: A operação de levantamento de dinheiro de uma conta deve ser totalmente realizada ou caso contrário nenhuma alteração deve ser efectuada.

Acessos concorrentes por diversos utilizadores

Acessos concorrente não controlados podem originar inconsistências. Explo: duas pessoas a consultarem um saldo e a alterarem-no ao mesmo tempo.

Desvantagens do Sistema de Gestão de Ficheiros

Dificuldades no acesso aos dados

Necessidade de escrever um novo programa para efectuar uma nova tarefa; Sistema de Gestão de Ficheiros

Definição de Sistemas de Gestão de Bases de Dados (SGBDs)

A abordagem pelos SGBDs tem uma característica fundamental: os dados são organizados num único conjunto (uma única unidade de armazenamento em vez de várias unidades independentes);

Adicionalmente todos os acessos aos dados passam por entidade designada por SGBD que centraliza em si o acesso à base de dados;

Os SGBDs ocultam os detalhes físicos de armazenamento e organização dos dados (a base de dados encontra-se evidentemente num conjunto de ficheiros); O SGBD é uma única entidade que manipula

a base de dados, atendendo a solicitações do nível aplicacional. Bases de Dados S G B D Sistema de Gestão de Ficheiros

(12)

Bases de Dados

Características dos SGBDs

Diferentes utilizadores poderão ter o seu acesso limitado a partes da base de dados. Por exemplo, embora todos os dados de uma organização estejam na mesma base de dados, aqueles que são importantes para a definição da estratégia só podem ser consultados pela administração.

Os SGBDs mantém não somente os dados, mas também a forma como os mesmos são armazenados, contendo uma descrição completa da base de dados;

Essas informações são armazenadas no catálogo do SGBD (o dicionário de dados), o qual contém informações sobre a estrutura de cada ficheiro, o tipo, o formato, restrições, etc…

A informação armazenada no catálogo (no dicionário) é chamada de metadados; Assim, uma qualquer alteração na estrutura dos ficheiros, apenas implica uma alteração no catálogo, evitando a alteração no código fonte de cada uma das aplicações;

Vantagens dos SGBDs

Acesso Simultâneo

Vários utilizadores podem aceder e alterar ao mesmo tempo a mesma base de dados sem criar inconsistências.

Explo: 2 utilizadores diferentes podem consultar simultaneamente os dados do mesmo cliente. No entanto o SGBD não permite que ambos os utilizadores alterem esses dados ao mesmo tempo.

Controle de Redundância

Restrição a acessos não autorizados

Um SGBD deve fornecer um sistema de autorização e segurança, o qual é utilizado pelo administrador da base de dados, para criar contas e especificar as restrições dessas contas.

Ao contrário do tradicional sistema de gestão de ficheiros, onde cada aplicação deve manter o seu próprio conjunto de ficheiros e dados, os SGBDs não, situação que facilita o controle de redundância.

(13)

Bases de Dados

Vantagens dos SGBDs

Assim, uma qualquer alteração na estrutura dos ficheiros, apenas implica uma alteração no catálogo, evitando a alteração no código fonte de cada uma das aplicações.

Tolerância a Falhas

Um SGBD deve fornecer recursos para recuperação de falhas, tanto de software como de hardware.

Independência dos dados relativamente às aplicações

Exemplos de SGBDs

MySql, Access,

Mais adequados para uso doméstico, pequenas empresas, etc…

Explo: 2 utilizadores diferentes podem consultar simultaneamente os dados do mesmo cliente. No entanto o SGBD não permite que ambos os

utilizadores alterem esses dados ao mesmo tempo.

Oracle, Informix, SQL Server, etc…

(14)

Bases de Dados © Ricardo Campos [ h t t p : / / w w w . c c c . i p t . p t / ~ r i c a r d o ] Utilizadores Nível Básico Programadores de Aplicações Utilizadores Nível Avançado Administradores da Base de Dados Programas de Interface Programas Ferramentas de Análise Ferramentas de Administração

usam escrevem usam usam

Utilizadores de um SGBD

Programadores de Aplicações

Interagem com o sistema através de chamadas DML; Utilizadores Nível Avançado

Constroem pedidos numa linguagem de consulta de bases de dados ou através de ferramentas de OLAP ou Data Mining;

Utilizadores Nível Básico

Chamam uma das aplicações que foi construída previamente; Explo: pessoas a aceder a uma base de dados através da web.

Administrador da Base de Dados

Coordena todas as actividades do sistema de bases de dados.

(15)

Bases de Dados

Funções do Administrador de Bases de Dados

Manutenção de rotina (backups, gestão de espaço em disco).

Concede aos utilizadores autorizações de acesso à base de dados; Define os métodos de acesso;

Define restrições de integridade; Monitoriza o desempenho;

Arquitectura de um SGBD

utilizadores

nível básico programadores de aplicações DBA Utilizadores

programas

de interface chamadas de rotina

consultas esquema de bases de dados Utilizadores nível avançado SGBD pré-compilador da linguagem de manipulação de dados processador de consultas compilador da linguagem de definição de dados código objecto dos programas de interface gestor da base de dados gestor de ficheiros ficheiros de dados dicionário de dados

(16)

Bases de Dados

Arquitectura de duas camadas Arquitectura de três camadas

Aplicações web recorrendo a software intermediário.

Programas clientes que comunicam com a base de

dados.

Arquitectura Cliente/Servidor

Servidor Servidor Aplicação Cliente BD Aplicação Cliente Rede Servidor Aplicação BD Aplicação Rede

Vantagens Arquitectura Cliente/Servidor

Independência das aplicações de cliente relativamente à localização física dos dados;

Todo o trabalho de interface com o utilizador e tratamento da informação (gráfico, formatação) é feito no cliente;

Todas as tarefas de armazenamento da informação, consulta, segurança, partilha de dados, são concentradas no servidor;

Só os pedidos de informação e os resultados é que são enviados pela rede, donde resulta um tráfego geralmente baixo;

Adapta-se facilmente a problemas de diferentes dimensões: mais ou menos clientes, servidores com maior ou menor capacidade, mais ou menos servidores.

(17)

Bases de Dados

Bases de Dados Distribuídas

Cliente Servidor Servidor 1 BD Cliente Servidor Servidor 2 BD Rede

Uma base de dados distribuída surge ao utilizador como se fosse uma única base de dados, mas na realidade é constituída por diversas bases de dados distribuídas por diversos computadores.

Este sucesso pode ser explicado pela simplicidade do modelo em que assentam, o modelo relacional, que é constituído somente por relações, e pelo surgimento de uma linguagem de manipulação simples e eficiente, o SQL (Structured Query Language).

Contextualização Histórica

Tipos de Bases de Dados

Bases de Dados Hierárquico Em Rede Relacional

A utilização comercial de BDs começou nos anos 60. Inicialmente a informação era guardada em ficheiros e a sua consulta e manipulação era pouco prática;

Para definição de dados eram usados os modelos hierárquico e de rede; No início dos anos 70 surgiram os SGBD relacionais cuja popularidade não tem parado de crescer até hoje;

(18)

Bases de Dados

Hierárquicos; Em Rede; Relacional:

Relações entre tabelas de dados controladas por álgebra relacional; Proposto por Codd em 1969;

Actualmente existem vários fornecedores de tecnologia relacional no mercado: Oracle; MySql; SqlServer, Access, etc…;

Níveis de Análise do Sistema de Informação Ficheiros vs SGBDs

Tipos: Hierárquicos, em Rede, Relacionais

Exemplo de Informações numa Base de Dados

Nome Rua Cidade Conta Saldo

Nuno Terlamonte Aljezur 1 55€ Filipa Sítio Fechado Aljezur 2 1000€ Filipa Sítio Fechado Aljezur 3 5366€ António Glorioso Lisboa 3 5366€ António Glorioso Lisboa 4 10553€

Consideremos a seguinte tabela como base para a definição dos vários tipos de bases de dados:

(19)

Bases de Dados

Modelo Hierárquico

Os dados são representados por registos e os relacionamentos por ligações; Os registos são organizados como colecções arbitrárias de árvores.

Nuno Terlamonte Aljezur

1 55€

António Glorioso Lisboa

3 5366€ 4 10533€ Filipa Sítio Fechado Aljezur

2 1000€ 3 5366€

Bases de Dados Hierárquico

Em Rede Relacional

Modelo Hierárquico

Cliente Encomenda Linha_Encomenda Produto

Em Rede Relacional

Modelo Hierárquico

Como se pode concluir, esta é uma organização que privilegia o acesso aos registos do Cliente, mas que dificulta o acesso aos registos do Produto.

(20)

Bases de Dados

Modelo em Rede

Os dados são representados por colecções de registos e os relacionamentos por ligações:

Nuno Terlamonte Aljezur

Filipa Sítio Fechado Aljezur

António Glorioso Lisboa

1 55€

4 10533€ 3 5366€ 2 1000€

Em Rede

Relacional

Modelo Relacional

Tanto os dados, como os relacionamentos são representados por tabelas; Possui fundamento matemático sólido;

Suponha-se a representação da realidade recorrendo ao modelo E-R (nível conceptual): Cliente rua cidade nome _Conta número saldo possui M N numConta Saldo 1 55€ 2 1000€ 3 5366€ 4 10533€

codCliente Nome Rua Cidade

100 Nuno Terlamonte Aljezur 101 Filipa Sítio Fechado Aljezur 102 António Glorioso Lisboa

codCliente numConta

100 1 101 2 101 3 102 3

Bases de Dados Hierárquico Em Rede

Relacional

(21)

Bases de Dados

Grau e Cardinalidade de uma Relação

Grau de uma Relação

Número de atributos de uma relação. Cardinalidade de uma Relação Número de tuplos de uma relação

codCliente Nome Rua Cidade

100 Nuno Terlamonte Aljezur 101 Filipa Sítio Fechado Aljezur 102 António Glorioso Lisboa

Cliente

4 atributos Grau = 4

3 tuplos Cardinalidade = 3

Bases de Dados Hierárquico Em Rede

Relacional

Modelo Relacional

(22)

Sumário

Sumário

Modelo Relacional

Normalização de uma Base de Dados; Modelo Relacional (nível lógico); Modelo E-R (nível conceptual);

Estratégias para a Concepção de uma Base de Dados;

Autor M obra N Publicação

Modelo Relacional (Nível Lógico)

Modelo E-R (Nível Conceptual)

Estratégias para a concepção de uma Base de Dados

Representação Gráfica

Conceito de Chaves Tipos de Associações Tipos de Relações Existentes

Tipos de Participação

O desenvolvimento de uma base de dados passa por representar graficamente a realidade que se quer moldar:

O Diagrama de Entidade – Relação é uma técnica de representação gráfica que ilustra conceptualmente as relações entre entidades de bases de dados;

Definição do Modelo E-R (Entidade – Relação)

Normalização de uma Base de Dados

(23)

Bases de Dados

Definição do Modelo E-R (Entidade – Relação)

No Access o modelo conceptual não existe. Assim, sempre que se pretende construir uma base de dados, o desenvolvimento da parte conceptual realiza-se numa folha de papel ☺;

Uma vez que o Access apenas conhece o modelo lógico, é necessário aprender os fundamentos teóricos da passagem do modelo conceptual para o modelo lógico, por forma que seja possível construir as tabelas no Access;

Entidade

Objecto que é distinguível de outros objectos, mas que pode ser agrupado na mesma entidade, pois possuem várias características comuns. Pretende representar a realidade que queremos modelar (exemplo: empresa, empregado, aluno, professor, carro).

Entidade, Atributos, Associações e Chaves

Tipos de Participação Normalização de uma Base de Dados

Cor

Cor kms

(24)

Bases de Dados

Dê um exemplo de entidades:

Aluno Disciplina

Entidade, Atributos, Associações e Chaves

Atributos _{Propriedades ou características que permitem} descrever as entidades. Os livros por exemplo possuem vários atributos comuns: o título, o número de páginas, o ano de publicação, etc…

Titulo _{de Páginas}Número

Ano de Publicação

(25)

Bases de Dados

Entidade, Atributos, Associações e Chaves

Aluno n.º aluno nome morada _Disciplina código Designação Dê um exemplo de atributos:

Associação / Relação

Estabelecimento de uma relação entre várias entidades;

Chave

Um atributo (chave simples) ou grupo de atributos (chave combinada) que permite identificar univocamente cada entidade;

Entidade, Atributos, Associações e Chaves

sublinhado

A presença de um substantivo usualmente indica uma entidade; A presença de um verbo é uma forte indicação de um relacionamento; Um adjectivo que é uma qualidade é uma forte indicação de um atributo; Resumo:

(26)

Bases de Dados © Ricardo Campos [ h t t p : / / w w w . c c c . i p t . p t / ~ r i c a r d o ] Dê um exemplo de associação: Aluno n.º aluno nome morada _Disciplina código Designação inscrito

Entidade, Atributos, Associações e Chaves

Chaves Primárias

Todas as tabelas têm de possuir uma chave primária; Permitem identificar univocamente um registo;

Número Nome Morada

001 João Rua a 013 João Rua b Número é chave porque não

podem existir dois clientes com o mesmo número;

Nome não é chave porque podem existir dois clientes com o mesmo nome;

Conceito de Chaves

Tipos de Associações Tipos de Relações Existentes

(27)

Bases de Dados

Chaves Primárias

Fila Lugar Ocupado?

A 1 sim

A 2 não

B 1 não

Apenas os atributos {Fila,Lugar} garante que identificamos um registo;

Número Nome Morada BI

001 João NULL 1234567 013 Ana NULL 7654321

Número e BI são chaves candidatas. É obrigatório optar por uma única chave;

Aluno (nAluno, nome, morada)

Disciplina (codDisciplina, desDisciplina) (8699, Ricardo, Rua A)

(8700, Nuno, Rua José) (8701, Ana, Travessa do Viriato)

(1, TIC) (2, Informática)

registo / linha / ocorrência / instância

Dê exemplos de registos para as seguintes entidades, não se esquecendo de indicar a chave primária:

Chaves Primárias

(28)

Bases de Dados

Chaves Estrangeiras

As chaves estrangeiras são conjuntos de uma ou mais colunas que, numa determinada tabela, referenciam a chave primária de outra tabela.

As chaves estrangeiras constituem um mecanismo essencial de funcionamento dos sistemas de bases de dados relacionais;

Aluno (numAluno, nome, morada,……) Disciplina (codDisciplina, designação,………) Exame (numAluno, codDisciplina, data, nota, ………)

Verifica-se que a tabela Exame possui duas chaves estrangeiras;

Chaves Estrangeiras

Importa chamar a atenção para o facto de não ser obrigatório que uma chave externa faça parte da chave primária da tabela em que está inserida.

Empregado (codEmpregado, nome, codDepartamento,……) Departamento (codDepartamento, designação,………)

(29)

Bases de Dados

Nenhum componente de chave primária de uma tabela pode conter nulos. Integridade de Entidade

O valor da chave

estrangeira tem de existir na tabela referenciada. Por outras palavras, não pode ser usada como chave estrangeira um valor que não exista como chave na sua tabela de origem

Integridade Referencial

A inscrição do valor J152 na tabela Participações, constituiria uma violação do princípio de integridade referencial, uma vez que este valor não existe na tabela jogadores.

Integridade de Entidade e Integridade Referencial

IdJogador

IdJogador NomeNome DataNascDataNasc J1 Vaz 08/10/1970 J2 Inácio 20/08/1972 J3 Paulo 20/01/1977 J4 Pinho 15/02/1978 ….. ….. ….. IdJogador

IdJogador IdJogoIdJogo GolosGolos J1 1 1 J2 2 0 J3 3 2 J152 1 0 ….. ….. ….. Tabela Participações Tabela Participações Tabela Jogadores Tabela Jogadores

Do seguinte texto determine o que é Entidade, Associação, Atributos e Chave: Numa universidade existem alunos e professores. Os alunos são identificados por um número, têm um nome e morada. Os professores são identificados por um código, têm um nome e habilitações. Os professores leccionam aula aos alunos.

Aluno

n.º aluno nome

morada _leccionar _Professor

código

nome

habilitações

Defina o modelo E-R (nível conceptual):

Representação Gráfica Conceito de Chaves Tipos de Associações Tipos de Relações Existentes

Exercício

(30)

Bases de Dados

Tipos de relações existentes

Com base nestes três tipos de relações é possível decompor qualquer relação do mundo real:

Relação Binária (as mais comuns)

Possui duas entidades que são relacionadas entre si; Relação Ternária (complexas)

Possui três entidades que se relacionam entre si; Relação Unária (reflexiva)

Possui apenas uma entidade que se relaciona com ela própria;

Representação Gráfica Conceito de Chaves Tipos de Associações

Tipos de Relações Existentes

Relação Unária (reflexiva)

Possui apenas uma entidade que se relaciona com ela própria;

Selecção

jogos

Esquema de uma relação unária, entre elementos da entidade selecção;

Tipos de relações existentes

Selecção

Portugal x Espanha Portugal x França Alemanha x França jogos

(31)

Bases de Dados

Relação Binária (as mais comuns)

Possui duas entidades que são relacionadas entre si;

Tipos de relações existentes

Deputados pertencem _{Parlamentares}Grupos

Tipos de relações existentes

Relação Ternária (complexas)

Possui três entidades que se relacionam entre si;

Actores _Filmes

Director

(32)

Bases de Dados

3 Tipos de Associações

Numa associação binária podemos encontrar 3 tipos de associações:

Um para Um (1:1)

Caso em que a um elemento corresponde um outro elemento;

Um para Muitos (1:N)

Caso em que a um elemento podem corresponder um ou mais elementos;

Muitos para Muitos (M:N)

Caso em que vários elementos podem ser origem de vários outros elementos;

Representação Gráfica Conceito de Chaves

Tipos de Associações

2 Tipos de Participação

Uma entidade pode participar numa relação de duas formas: Participação Obrigatória

Não pode existir nenhuma ocorrência dessa entidade que não esteja associada a alguma ocorrência da outra entidade que participa na relação;

Participação Não Obrigatória

Podem existir ocorrências dessa entidade que não estejam relacionadas a alguma ocorrência da outra entidade que participa na relação;

Representação Gráfica Conceito de Chaves Tipos de Associações Tipos de Relações Existentes

A B

Entidade Obrigatória Entidade Obrigatória

(33)

Bases de Dados

Associação 1:1 – nenhuma Entidade Obrigatória

Dê um exemplo para cada um dos seguintes elementos: Um para Um (1:1)

Homem 1 Casamento 1 Mulher

H1 H2 H3 H M1 M2 M3 M

Um Homem está casado com uma Mulher Uma Mulher está casada com um Homem

Outro Exemplo de Um para Um (1:1)

Um Carro pertence obrigatoriamente a um Empregado Mas nem todo o Empregado tem um Carro

Carro Empregado 1 Tem 1 E C E1 E2 E4 C1 C2 C3 E3

Associação 1:1 – uma Entidade Obrigatória

(34)

Bases de Dados

Empregado 1 Tem 1 Carro

Um Empregado tem obrigatoriamente um Carro Um Carro pertence obrigatoriamente a um Empregado

E1 E2 E3 E C1 C2 C3 C

Associação 1:1 – as duas Entidades Obrigatórias

Enfermaria 1 Tem N Paciente Uma Enfermaria têm vários Pacientes Um Paciente pertence a uma só Enfermaria

E1 E2 E P1 P2 P3 P

Associação 1:N – nenhuma Entidade Obrigatória

(35)

Bases de Dados

Empresa 1 Trabalhar N Empregado Uma Empresa têm vários Empregados a trabalhar Um Empregado trabalha obrigatoriamente numa Empresa

A1 A2 A B1 B2 B3 B

Associação 1:N – Entidade do lado N Obrigatória

Autor M Escreveu_Pertence N Livro Um Autor escreveu vários Livros Um Livro pertence a vários Autores

A1 A3 A L1 L2 L3 L A2

Associação M:N – nenhuma Entidade Obrigatória

(36)

Bases de Dados

É no entanto necessário aprender os fundamentos teóricos da passagem do modelo E-R (nível conceptual) para o modelo relacional (nível lógico), por forma que seja possível definir as tabelas:

Autor M obra N Publicação

De acordo com os tipos de associações e os tipos de participação: Modelo Relacional (Nível Lógico)

é possível definir o modelo relacional (nível lógico);

Transformação do modelo E-R (ass. 1:1) para modelo Relacional Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional

Contextualização

Dê um exemplo para cada um dos seguintes elementos: Um para Um (1:1)

Homem 1 Casamento 1 Mulher

H1 H2 H3 H M1 M2 M3 M

Um Homem está casado com uma Mulher Uma Mulher está casada com um Homem

Relação 1:1 – nenhuma Entidade Obrigatória

Transformação do modelo E-R (ass. 1:1) para modelo Relacional

Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional Normalização de uma Base de Dados

(37)

Bases de Dados

Relação 1:1 – nenhuma Entidade Obrigatória

Homem (BIHomem, nome, morada,……) Mulher (BIMulher, nome, morada,………)

Se no relacionamento 1:1 nenhuma entidade é obrigatória são necessárias 3 tabelas (2 para as entidades + 1 para a associação):

Casamento (BIHomem, BIMulher, ……) Homem (BIHomem, nome, morada,……) Mulher (BIMulher, nome, morada,………)

Transf. Modelo E-R para modelo relacional Quando se transforma um modelo conceptual (E-R) para um modelo lógico (relacional), é necessário realizar um conjunto de alterações.

A transformação passa por criar uma nova tabela, onde a chave, é a chave primária de uma das entidades (por exemplo Homem). Deve-se também adicionar como atributo a chave da outra entidade (Mulher):

Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional Normalização de uma Base de Dados

Um Carro pertence obrigatoriamente a um Empregado Mas nem todo o Empregado tem um Carro

Carro Empregado 1 Tem 1 E C E1 E2 E4 C1 C2 C3 E3

Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional

Relação 1:1 – uma Entidade Obrigatória

(38)

Bases de Dados

Se o relacionamento de 1:1 tem participação obrigatória de apenas uma entidade são necessárias 2 tabelas:

A transformação passa por colocar o atributo que é chave primária da relação Empregado (entidade não obrigatória) na relação Carro (entidade obrigatória) :

Empregado (codEmpregado, nome, morada,……) Carro (Matricula, marca, modelo, codEmpregado,……) Empregado (codEmpregado, nome, morada,……)

Carro (Matricula, marca, modelo,………) Modelo Relacional (Nível Lógico)

Relação 1:1 – uma Entidade Obrigatória

Empregado 1 Tem 1 Carro

Um Empregado tem obrigatoriamente um Carro Um Carro pertence obrigatoriamente a um Empregado

E1 E2 E3 E C1 C2 C3 C

Relação 1:1 – as duas Entidades Obrigatórias

(39)

Bases de Dados

Se o relacionamento de 1:1 tem participação obrigatória de ambas as entidades é necessária apenas 1 única tabela:

Empregado (codEmpregado, nome, morada,……) Carro (Matricula, marca, modelo,………)

Empregado (codEmpregado, nome, morada, Matricula, marca, modelo,……) Os atributos do Carro podem ser vistos como atributos adicionais do Empregado A transformação passa por colocar o atributo que é chave primária de uma das entidades (por exemplo de Carro) na outra entidade (por exemplo em Empregado):

Relação 1:1 – as duas Entidades Obrigatórias

Enfermaria 1 Tem N Paciente Uma Enfermaria têm vários Pacientes Um Paciente pertence a uma só Enfermaria

E1 E2 E P1 P2 P3 P

Transformação do modelo E-R (ass. 1:N) para modelo Relacional

Transformação do modelo E-R (ass. M:N) para modelo Relacional

Relação 1:N – nenhuma Entidade Obrigatória

(40)

Bases de Dados

A transformação passa por criar uma nova tabela, onde a chave, é a chave primária da entidade Paciente (lado N da associação). Deve-se também adicionar como atributo a chave da entidade Enfermaria (lado 1 da associação): Se no relacionamento 1:N a entidade do lado N não é obrigatória, são

necessárias 3 tabelas (2 para as entidades + 1 para a associação):

Enfermaria (codEnfermaria, designação,……) Paciente (codPaciente, nome, morada,………)

Enfermaria_Paciente (codPaciente, codEnfermaria, ……) Enfermaria (codEnfermaria, designação,……)

Paciente (codPaciente, nome, morada,………) Modelo Relacional (Nível Lógico)

Relação 1:N – nenhuma Entidade Obrigatória

Empresa 1 Trabalhar N Empregado Uma Empresa têm vários Empregados a trabalhar Um Empregado trabalha obrigatoriamente numa Empresa

A1 A2 A B1 B2 B3 B

Relação 1:N – Entidade do lado N Obrigatória

(41)

Bases de Dados

A transformação passa por colocar o atributo que é chave primária da relação Empresa (lado 1 da associação) na relação Empregado (lado N da associação):

Empresa (codEmpresa, nome, localização,……) Empregado (BI, nome, morada,……)

Empresa (codEmpresa, nome, localização,……) Empregado (BI, nome, morada, codEmpresa,……)

Se no relacionamento 1:N a entidade do lado N é obrigatória, são necessárias duas tabelas:

Relação 1:N – Entidade do lado N Obrigatória

Autor M Escreveu_Pertence N Livro Um Autor escreveu vários Livros Um Livro pertence a vários Autores

A1 A3 A L1 L2 L3 L A2

Transformação do modelo E-R (ass. 1:1) para modelo Relacional Transformação do modelo E-R (ass. 1:N) para modelo Relacional

Relação M:N – Obrigatoriedade Irrelevante

(42)

Bases de Dados

Nas relações M:N são sempre necessárias 3 tabelas, uma para cada entidade e uma terceira para a associação:

Autor (codAutor, nome,……) Livro (ISBN, Título, numPag,……)

Autor_Livro (codAutor, ISBN, dataLançamento,……)

A transformação passa por criar uma nova tabela, onde a chave, é a chave é constituída pela chave de cada um das entidades (chave de Autor + chave de Livro):

Autor (codAutor, nome,……) Livro (ISBN, Título, numPag,……)

Transformação do modelo E-R (ass. 1:1) para modelo Relacional Transformação do modelo E-R (ass. 1:N) para modelo Relacional

Relação M:N – Obrigatoriedade Irrelevante

A existência de uma chave estrangeira (atributo que é chave primária noutra relação) numa relação, prende-se com a necessidade de manter a interligação entre essa relação e a relação onde esse conjunto de atributos é chave primária;

Resumo

Uma associação dá origem a uma tabela quando:

Quando é uma associação 1:N (com entidade do lado N não obrigatória); Quando é uma associação 1:1 (com ambas as entidades não obrigatórias); Quando é uma associação M:N.

Por exemplo dadas duas relações, Departamentos e Funcionários, a única forma de traduzir que um dado funcionário trabalha num dado departamento é incluir na relação Funcionários a identificação do Departamento onde esse Funcionário trabalha;

Transformação do modelo E-R (ass. 1:1) para modelo Relacional Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional

(43)

Bases de Dados

Exercício

Defina com base no seguinte levantamento de requisitos o modelo de E-R: Um Editor pode editar vários Livros;

Um Livro obrigatoriamente só pode ser editado por um Editor; Um Livro pode ser requisitado por vários Utentes;

Um Utente pode requisitar vários Livros;

Livro M Requisição N Utente

Edição

1 N

Editor

Transformação do modelo E-R (ass. 1:1) para modelo Relacional Transformação do modelo E-R (ass. 1:N) para modelo Relacional Transformação do modelo E-R (ass. M:N) para modelo Relacional Normalização de uma Base de Dados

Exercício

Transformar o modelo E-R (nível conceptual) no modelo relacional (nível lógico): 4 tabelas (3 entidades + 1 associação)

Editor (codEditor, nome,……) Livro (ISBN, título, codEditor……)

Utente (codLeitor, nome,……)

Requisição (ISBN, codLeitor, dataRequisição,……)

Editor codEditor nome …….. Livro ISBN título codEditor ………. Requisição ISBN codLeitor dataReq. ………. Utente codLeitor nome …….. 1 N 1 N ₁ N Modelo Relacional (Nível Lógico)

(44)

Bases de Dados

Exercício

Pretende-se desenvolver uma base de dados para um banco:

Cada cliente pode ter várias contas, mas cada conta só tem associado um cliente;

Cada conta está fixada numa agência, a qual pode ter várias contas; Cada cliente pode fazer vários empréstimos, mas cada empréstimo só está associado a um cliente;

Cada empréstimo está fixado numa agência, a qual pode ter vários empréstimos concedidos;

Cliente Agência 1 N 1 N Modelo de E-R:

Modelo de E-R constituído pelo clientes de um banco, assim como as contas e empréstimos desses clientes nas várias agências do banco.

Conta N Empréstimo 1 N 1

(45)

Bases de Dados

Clientes codClient e nome Profissao localidade Contas numConta tipoConta codCliente codAgencia saldo Agencia codAgencia designacao localidade 1 N N 1 1 Emprestimos numEmprestimo codCliente codAgencia valor N N DDL e DML

Modelo Relacional (nível lógico)

Modelo Relacional:

Clientes(codCliente, nome, profissao, localidade); Agencias(codAgencia, designacao, localidade);

Contas(numConta, tipoConta, codCliente, codAgencia, saldo); Emprestimos(numEmprestimo, codCliente, codAgencia, valor);

Após o desenvolvimento do modelo E-R dos dados (nível conceptual) é feito o desenvolvimento do modelo relacional (nível lógico);

Normalizar para quê?

O conjunto de tabelas obtidas representa a estrutura da informação; Assegurar o mínimo de redundância possível;

Objectivos da Normalização

Objectivos

1FN, 2FN, 3FN

Projecto Normalização de Factura Electrónica Projecto Normalização de Escola Modelo Relacional (Nível Lógico)

Normalização de uma Base de Dados Estratégias para a concepção de uma Base de Dados Modelo E-R (Nível Conceptual)

(46)

Bases de Dados

Limitações das monotabelas

Objectivos

1FN, 2FN, 3FN

Projecto Normalização de Factura Electrónica Projecto Normalização de Escola

Para registar numa única tabela as encomendas efectuadas pelos clientes, caímos numa redundância desnecessária de informação, quer em relação aos produtos, quer em relação aos clientes;

Cliente Endereço Telefone Produto Modelo Preço Data Qtd

Silva Lisboa 665544 Alicate A1 400 04/03/06 400 Santos Porto 554433 Martelo M1 250 04/03/06 250 Costa Coimbra 332211 Serra S1 1500 04/03/06 1500 Castro Faro 443322 Tesoura T1 500 04/03/06 500

Silva Lisboa 665544 Martelo M1 250 05/04/06 250 Silva Lisboa 665544 Tesoura T1 500 05/04/06 500 Costa Coimbra 332211 Alicate A1 400 06/04/06 400 Costa Coimbra 332211 Serra S1 1500 06/04/06 1500 Castro Faro 443322 Alicate A1 400 07/04/06 400

Estratégias para a concepção de uma Base de Dados Modelo E-R (Nível Conceptual)

Objectivos

1FN, 2FN, 3FN

Projecto Normalização de Factura Electrónica Projecto Normalização de Escola

Outros problemas das monotabelas:

Se o endereço do cliente for alterado, essa alteração tem de ser feita em várias linhas da tabela;

Problemas de Actualização

Não é possível registar informação relativa a um cliente que não tenha feito qualquer encomenda;

Problemas de Inserção

Imagine-se que o cliente ainda só fez uma encomenda. Se a encomenda for anulada, perde-se toda a informação relativa ao cliente;

Problemas de Eliminação

Limitações das monotabelas

(47)

Bases de Dados

Modificar o conjunto de tabelas obtido do modelo conceptual num outro conjunto de tabelas equivalente, mas menos redundante e mais estável;

A existência de redundâncias está associada a um conjunto de anomalias de inserção de dados, anomalias de eliminação de dados e anomalias de alteração de dados.

Objectivos da Normalização

Objectivos

1FN, 2FN, 3FN

As 3 formas normais

Uma tabela, está na 1FN quando todos os seus atributos são elementares. Não pode existir um conjunto de valores para um determinado atributo; 1.ª Forma Normal

Quando a chave é composta, qualquer atributo que não seja chave deve depender da totalidade da chave, através de uma dependência funcional elementar;

2.ª Forma Normal

Nenhum atributo não chave pode depender de um outro atributo não chave, ou seja, cada atributo deve depender apenas da chave primária da relação; 3.ª Forma Normal

Objectivos

1FN, 2FN, 3FN

(48)

Bases de Dados

Esquema Geral da Normalização

Dados não normalizados. Dispor a informação em tabelas de forma que todas as ocorrências dos atributos sejam elementares:

1.ª Forma Normal

Os dados estão na 1.ª Forma Normal

Objectivos

1FN, 2FN, 3FN

Identificar as dependências funcionais dos atributos em relação às chaves: 2.ª Forma Normal

Os dados estão na 2.ª Forma Normal

Esquema Geral da Normalização

Objectivos

1FN, 2FN, 3FN