Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Informática II – Cap. 5-2
Bases de Dados - MsAccess
•Filipe Caldeira - 2001
Informática II
1
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Introdução
Porquê a utilização de Sistemas de
Bases de Dados (SBD)?
– Armazenamento dos dados de uma forma
consistente ( a informação não deve conter
informação divergente sobre o mesmo facto), de
fácil acesso usando linguagens de consultas e
com mecanismos de segurança para o acesso à
informação
Informática II
2
1
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Dados vs Informação
• Dados só se transformam em informação
relacionados ou interpretados de alguma forma.
quando
• As pessoas, ao tomarem decisões, utilizam a informação e
não os dados.
• A informação é uma necessidade diária em qualquer acto de
gestão e tem um papel fundamental na previsão de
actividades e resultados futuros de uma empresa.
Informática II
3
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Sistemas de Bases de Dados
Funções de um SGBD
Aplicação
A
Aplicação
B
Aplicação
C
• Descrição
• Manipulação
⇒Consulta
⇒Inserção
⇒Actualização
• Controlo
⇒Confidencialidade
⇒Segurança
Informática II
SGDB
Base de
Dados
SGBD
Sistema de Gestão de Bases de Dados
4
2
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Funcionalidades de um SGBD
• Conjunto de programas para aceder aos dados
• Evitar redundâncias e inconsistências
• Facilitar o acesso aos dados
• Permitir acesso simultaneo à informação
• Garantir a segurança dos dados
• Garantir integridade da
restrições pré-definidas
informação, de acordo com
Informática II
5
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Exemplo
Um banco quer guardar a informação dos seus
clientes e das contas.
Para além de guardar a informação, o banco
precisa de programas para
• Fazer débitos e créditos numa conta
• Abrir e fechar contas
• Ver o saldo de uma conta
• Fazer os estratos das contas
Informática II
6
3
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Vantagens
• Facilidade de partilha dos dados
• Diminuição dos custos de utilização e de pesquisa
• Diminuição dos custos de manutenção e de exploração
• Prevenção da redundância e da ambiguidade dos dados
• Consistência na formatação dos dados
• Suporte à criação de múltiplas formas de visualização da
informação
Informática II
7
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Tabelas
Os dados estão armazenados em forma de
tabelas nas diferentes bases de dados, como
o ORACLE e o ACCESS.
Para o desenho e a construcção de SBDs são
utilizados modelos conceptuais, como o
modelo Entidade-Relações
O objectivo principal é o de providenciar um
modelo independente do método de
armazenamento e acesso de dados.
Informática II
8
4
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Modelo Entidade-Relação (ER)
Identificação das “coisas” de maior importância
num dado sistema do mundo real: as
entidades, as propriedades das entidades (os
atributos), e a forma como se relacionam
entre si (as relações)
São fácilmente compreendidos pelos nãoespecialistas
O diagrama ER é o ponto de partida para o
desenho da base de dados.
Informática II
9
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Conceitos do modelo ER (1)
Entidades
– Um conjunto de
(identificar)
objectos
que
se podem
distinguir
– Exemplo:
⇒O conjunto de todos os clientes do banco.
⇒O conjunto de todas as contas do banco.
Atributos
– Propriedades de um entidade
– Exemplos: Nome, telefone e morada dos cliente
Informática II
10
5
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Conceitos do modelo ER (2)
Relações
• Conjunto de associações entre várias entidades
Conta
Cliente
33
22
Maria
11
Manuel
193710
11111
100.000$00
Lisboa
3847101
11112
50.000$00
Beja
168389
11113
60.000$00
relação
entidade
conjunto entidade
Informática II
11
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Tipos de relações
Relação “um para um” (1:1)
– Uma pessoa só tem um nº de bilhete de identidade.
Relação “um para muitos” (1:N)
– Uma turma tem vários alunos, mas um aluno só
pertence a uma turma.
Relação “muitos para muitos” (N:M)
– Uma conta bancária pode pertencer a vários clientes
e vice-versa.
Informática II
12
6
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Como distinguir entidades?
Chaves
– Um ou mais atributos que permitem distinguir entre entidades, i.e
permitem identificar (inequivocamente) cada uma das entidades
de um conjunto de entidades.
– Exemplo:
• O atributo nº de cliente da entidade clientes.
Nas relações a chave corresponde à união das
chaves das entidades associadas à relação
Informática II
13
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Exemplo de um modelo ER
d a ta
nº
M :N
c lie n te s
nº
s a ld o
c o n ta s
c lien te s /c o n ta s
nom e
m o ra d a
Informática II
te lef o n e
14
7
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Modelo Relacional /Tabelas
Como implementar o modelo ER?
– No modelo relacional em forma de tabelas
• Dado um diagrama ER, que tabelas se devem
criar?
⇒A cada conjunto entidade, e a cada conjunto
relação, vai corresponder um tabela.
• Com que atributos?
Informática II
15
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Elaborar um modelo de dados
Determinar o alcance (suas fronteiras) da análise
Identificar as entidades
Identificar as relações entre as entidades
Identificar os atributos das entidades e das
relações
Identificar a chave de cada entidade
Informática II
16
8
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Entidades
Conjunto entidade E com n
atributos a1,…,an
nome
– Deve criar-se uma tabela
com n colunas, uma para
cada atributo.
clientes
morada
telefone
clientes
...
nome morada telefone
Informática II
17
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Relações
Conjunto relação R
entre entidades
E1,…,Em com k
atributos
adicionais
a1,…,ak.
Então os atributos
da relação
correspondem às
chaves das
entidades mais
os k atributos
adicionais
a1,…,ak.
data
clientes
contas
M:N
clientes/contas
nome
morada
telefone
atributo(
R)
clientes/conta
chave(E1)
Informática II
saldo
nº cliente data
chave(E2)
18
9
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Tabelas
Clientes
Conta
Conta/
C lientes
núm ero
141
142
nom e
João
M aria
morada
Évora
Lisboa
telefone
12635
61623918
núm ero
saldo
100 200.000$00
200 50.000$00
300 100.000$00
Núm ero
cliente
141
142
142
Núm ero
Data
C onta
100
21.10.1998
100
22.10.1998
200
27.11.1998
Informática II
19
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Criação de Tabelas em Access
•Criar uma Base de Dados nova, em que se irá inserir as tabelas.
–Ficheiro > Nova > Base de Dados -> Dar nome ao ficheiro e especificar a
pasta onde ficará
Criar tabela
•Criar tabela
Informática II
20
10
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Criação de Tabelas em Access
•Cria-se uma nova tabela em Vista de Estrutura
•Zonas no ecrã para a construcção de tabelas
– Nome do Campo
•Poderão ser 64 caracteres incluindo espaços
– Tipo de Dados
•Texto (alfanumérico), Número (dados numéricos que podem ser utilizados
para cálculos), Data/Hora, Moeda, Númeração Automática (para as chaves,
autoincrementável), Sim/Não (booleana)
–Descrição (Descrição do campo)
–Propriedades do Campo (Geral ou Pesquisa)
•Para permitir alterar tamanho dos campos, controlos de verificação, buscas
em outras tabelas, etc.
Informática II
21
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Tabelas
Nome da tabela
gravar com
Ficheiro > Guardar
como
Chave
Se não fôr
indicada
nenhuma chave
primária, então o
Access propõe a
criação de uma
chave.
Informática II
22
11
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Lookup Lists (Listas de Pesquisa) - 1
•Automatização a nível da tabela para a selecção de
informação de um atributo (coluna)
–Criar tabela auxiliar para armazenar as actividades económicas
Informática II
23
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Lookup Lists (Listas de Pesquisa) - 2
–Regressar à tabela
“Clientes” e aceder às
propriedades Pesquisa
do campo “Actvidade
Económica” e alterálas como se mostra ao
lado
–Agora não é mais
preciso inserir os
conteudos deste
campo, mas sim
seleccioná-los.
Informática II
24
12
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Inserção de mais duas tabelas
–Tabela auxiliar “Tipos de Cursos”
–Tabela “Cursos
Facturados”
Informática II
25
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Como é feita a inserção com
Pesquisa na Tabela
Informática II
26
13
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Definir Relações entre as Tabelas
–Aceder ao menu
“Ferramentas” e
seleccionar “Relações”
ou botão
–Em mostrar tabela, adicioná-las
todas
Criar ligação
–Arrastar e largar
o rato do campo
na tabela “um”
para o resp. campo na tabela
“muitos”
Informática II
27
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Criar Relações entre Tabelas
Informática II
28
14
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Tabelas Criadas
Informática II
29
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Estruturação de bases de dados Access
•Determinar as tabelas necessárias à base de dados
•Determinar os atributos de cada tabela
•Determinar os atributos com valores exclusivos, i.e.,
os atributos chaves
•Determinar as relações entre as tabelas
•Aperfeiçoar a estrutura
•Adicionar os dados
Informática II
30
15
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas
•Não basta armazenar os dados.
−Há que, pelo menos, poder consultar os dados
armazenados.
•O que são consultas?
−Analisar a informação nas tabelas para obter um
conjunto filtrado de informação
·Que tipo de consultas existem?
−Selecção, Projecção, Junção, ...
Informática II
31
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas assistidas em Access
•Consultas mais usadas
–seleccionar um conjunto de campos de uma
ou várias tabelas (projecção)
–criar condições que permitam filtrar um
conjunto de registos (selecção)
–operações estatísticas sobre os registos
Informática II
32
16
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consulta de Selecção
Seleccionar Consultas > Criar consulta na vista de estrutura
Seleccionar quais as tabelas
necessárias à consulta
pretendida
Informática II
33
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Selecção de Campos
Arrastar os campos
pretendidos para as colunas da
consulta p/ a zona (QBE)
Informática II
Zona Query-byExample (QBE) onde
são especificadas
características
específicas da consulta
34
17
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Executar Consulta
–Activar a consulta seleccionando no menu Consulta >
Executar ou através do botão
Informática II
35
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Assistente de Consulta
Informática II
36
18
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas com Condição Simples
•Exemplo:
–Seleccionar as empresas com actividades em Informática
Indicar
critério
Resultado
Informática II
37
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas com Condição Múltipla
•Exemplo:
–Seleccionar as empresas com actividades em Indústria ou estejam em
Cascais
–Utilizar o LIKE para a selecção de substrings
Informática II
38
19
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas entre Tabelas
–Obter um conjunto de registos constituídos
por dados provenientes de várias tabelas,
havendo a necessidade de relacioná-las.
–A relação tem por base um campo comum
(p.e. o Nº de Cliente)
Informática II
39
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Exemplo: Junção das Tabelas Clientes e
Cursos Facturados
Informática II
40
20
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas com Campos Calculados
–Calcular “Total Curso”, ou seja multiplicar o “Nº de Participantes” e o “Custo
por Participante”
•Ao criar uma Query seleccionar ambas as tabelas “Clientes” e Cursos Facturados”
•Seleccionar os registos (veja em baixo)
•Inserir no campo
Total Curso: [Nº de participantes] * [Custo por Participante]
Informática II
41
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Operações Estatísticas
– Como, por exemplo, o
somatório ou a média
– Exemplo:
Seleccionar Σ para agrupar
• Total gasto por cada
cliente em cursos
Total Curso: Soma([Nº
de
participantes]*[Custo
por Participante])
• Agrupar pela empresa
(Group By)
Informática II
42
21
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Consultas em SQL
•Linguagem comercial padrão para consultas no
modelo relacional
•As consultas em SQL são feitas sempre segundo um
esquema
Uma pergunta típica em SQL tem a forma
select A1, A2, ... , Am
from E1, E2, ... , En
where condition
atributos
Entidades (tabelas)
Informática II
43
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Selecção
•selecção de tuplos, ou linhas, com as seguintes
condições {=
=, ≠ , <, ≤, >, ≥}
•Exemplo:
select *
from Clientes
where Nº de Clientes > 2
Informática II
44
22
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Projecção
•selecção de colunas
•Exemplo:
select Clientes.Empresa
from Clientes
Informática II
45
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Junção
•De mais de uma tabela, por exemplo as
tabelas “Clientes” e “Cursos Facturados”
select Clientes.Nº de Cliente,
Clientes.Empresa, Cursos Facturados.Designação
from Clientes, Cursos Facturados
where Designação = “Access 2000”
Informática II
46
23
Instituto Superior Politécnico de VISEU
Escola Superior de Tecnologia
Mais Exemplos
Nº de Cliente, Empresa em Lisboa
select Nº de Cliente, Empresa
from Clientes
where Morada like “Access 2000”
Total do Curso agrupado pelas empresas
select c.Empresa, cf.”Nº de participantes”,
“Nº de participantes” * “Custo por Participante”
from Clientes c, Cursos Facturados cf
group by c.Empresa
Informática II
47
24