Aula 4: Mais Microsoft Access® e Bancos de Dados Relacionais

Página 1 de 14
Aula 4: Mais Microsoft Access® e Bancos de Dados Relacionais
Todas as telas são do MS Access®
23 de Janeiro de 2002
Meta:
Introdução ao modelo relacional de banco de dados e consultas intermediárias (funções de
agregaçã/agrupamento e consultas multi-tabelas).
Descrição:
Discussão de bancos de dados, introdução ao modelo relacional e exemplos dos tipos de consultas que
podem ser solicitadas nos exercícios de laboratório.
Pontos Principais:
Discussão de formato de dados e estrutura de dados
Característica Básica do modelo relacional de banco de dados
Consultas intermediárias
- função de agragação
- construtor de expressões
- atualização de banco de dados
- conexão de diversas tabelas.
Revisão de Banco de Dados:
l
Formato de dados e padrõoes de dados:
- Texto puro: ASCII
- Texto formatado: MSWord, WordPerfect
- Planinha: *.xls, *wk3
- Banco de Dados: dBase *.dbf
l
Diferença entre a escolha do modelo de dados (categoria da classe de dados) e formas possíveis
de padroniza-lo.
l
Uma forma de ver o que é um banco de dados é ve-lo na sua forma de texto ASCII puro, meio
comum de trocar dados entre programas de software. Este formato também é importante porque
muitas informações na web estão disponíveis neste padrão mais básico.
l
Estruturas de dados e modelos de dados
- Estrutura lógica para representar situações do mundo real
- Como coisas de interesse devem ser representadas
- Como coisas podem ser relacionadas umas-às-outras
l
A forma mais simples de banco de dados ´ um arquivo plano (para dados simples e em pequenas
quantidades)
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 2 de 14
Consultas com arquivo plano: Banco de dados de resumo dos empregados do Departamento de
Transportes.
Importando um arquivo tipo *.dbf. Para usar um arquivo *.dbf no MS Access, vá para File depois
para New Database... e com Blank Database marcado, clique OK. Na janela File New Database,
digite um nome de de arquivo como my_dot.mdb e certifique-se que o local onde o arquivo será salvo
está na sua pasta pessoal, de forma que a janela pareça algo como isto:
l
Criando my_dot.mdb na Janela File New Database
A seguir clique no botão Create button. Você deve ver uma janela chamada my_dot: Database:
A nova janela de banco de dados para my_dot
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 3 de 14
Agora vá para File... Get External Data... e depois Import. Na janela de importação, você deve
especificar onde procurar pelo arquivo *.dbf que você quer importar.
Neste caso, vá para
K:\11.208\data\ e certifique-se de especificar o tipo de arquivo como dBase 4 (*.dbf). Agora
selecione employee.dbf e clique no botão Import. O Access agora mostra uma janela
informando "Successfully imported 'employee.'
Feche a janela de importação. Na janela my_dot: Database, você verá employee
listado nas tabelas.
l
l
l
l
l
Revsão da documentação de banco de dados: definição de dados and forumulário de resumo.
Revisão de especificaç*atilde; de consultas básicas
Uso de AND versus OR:
¡ Quando você digita expressõos em mais de uma célula de crit´rio, o Microsoft Access
combina elas usando ou o operador And ou o operador Or. Se as expressõoes estão em
células na mesma linha, o Microsoft Access usa o operador And, o que significa que
apenas os registros que satisfazem o critério em todas as células serão retornados. Se as
experssõoes estão em linhas diferentes da grade de especificação, o Microsoft Access usa
o operador Or, o que significa que os registros que satisfazem o critério em qualquer das
células serão retornados.
Funções de agragaç*atilde;o (count, distinct); grupo por cláusulas
Expressões e expressõoes calculadas (criar um novo campo primeiro!)
¡ Inicialmente uma consulta simples: quantos empregados do DOT dirigem sozinhos como o
principal modo de transporte. Primeiro vamos olhar o dicionário de dados e pesquisar.
Constas de empregados que dirigem sozinhos como mod principal
Resultado da consulta acima
¡
Note que estamos olhando para o registro. Portanto, contando o identificador único da
pesquisa dá o mesmo resultado.
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 4 de 14
¡
Note, no entanto, que as funções de totalização podem fornecer cálculos em registros
selecionados. Por exemplo, pode-se calcular o tempo m´dia de troca apra aqueles que
dirigem sozinhos.
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 5 de 14
¡
As funções de gupo também permitem níveis progressivos de resolução da seleção. Podese examiar o tempo médio de treco por filhal do DOT..
¡
E pode-se olhar mais a fundo o tempo médio de troca por condado de residência dos
empregados de uma filhal do DOT em particular...
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 6 de 14
¡
E, claro, os códigos de CEP dentro dos condados....
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 7 de 14
¡
Também pode ser útil calcular expressões usando dois ou mais ampos. Pode-se contar as
pessoas em uma resdência (HH_SIZE) e o número de pessoas empregadas na residência
(HH_EMPLOYM), mas qual o percentual de pessoas empregadas?
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 8 de 14
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 9 de 14
n
¡
Claro, você pode necessitar apenas da expressão:
No entanto, isto não nos possibilita usar as funções de Agrupar Por. Para isto, adicionamos
Sum:
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 10 de 14
l
ADICIONANDO UM CAMPO E E A EXPRESSÃO CALCULADA E SAVANDO EM
UMA NOVA TABELA:
¡ Também pode ser útil criar um novo campo e então adiconar dados a ele com base em
uma expressão calculada. Na janela principal do banco de dados vá para a aba Tables e
então clique em Design. Vá para o final da tabela de empregados e clique na última linha
de entrada. Insira um nome de campo (sem espaços ou caracteres estranhos). Deposi,
clique dentro da célula Data Type, e um menu aparece. Selecione "Number." Também, na
área cinze abaixo da aba "General", selecione "Double." A sua janela deve parecer algo
como isto:
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 11 de 14
¡
¡
A seguir, feche a tabela e salve-a. Neste ponto sua tabela deve ter uma nova coluna sem
dados.
Vamos criar uma nova consulta com uma expressão calculada e um campo para ser
incluido na nova tabela:
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 12 de 14
¡
Na barra de ferramentas, clique no botão Query select
e no menu Query options
selecione Make-Table Query. Na janela Make Table, insira um novo nome de tabela e
escolha deixar ela no banco de dados atual. Após cliccar no botão Query now
,o
Microsoft Access avisa para com uma janela para confirmar que vamos criar uma nova
tabela. Clique Yes. Tem-se agora uma nova tabela sobre a qual pode-se fazer consultas.
Bancos de Dados Relacionais e Conexões
Um modelo mais complexo é o banco de dados relacional onde existem diversas tabelas que podem
ser conctadas por um identificador comum (assim como o número do seguro social ou o CPF podem
ser utilizados).
l Conexõoes relacionais ligam diferentes tabelas com diferentes tipos de dados. Isto gera informações
que não sã possíveis de obter de outra forma. Por exemplo, pode-se conectar tabelas de proprietários
de prédios, imóveis s e incêndios e determinar qual proprietá teve o maior prejuízo devido ao fogo.
l Examplo de conexõoes relacionais usando tabelas: imóveis, incêndios, taxas de seguro, proprietários
l Crie um banco de dados "Parcels_plus_other"
l Get External Data... e a seguir Import a seguinte tabela: K:\11.208\Data\parcels.dbf e depois
repita para as tabelas FIRES.DBF TAX.DBF e OWNERS.DBF. Dicionário de Dados para
PARCELS, FIRES, TAX, OWNERS
l
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 13 de 14
PARCELS (Informações dos
TAX (Informações de impostos e
FIRES (Incidentes de inc êndio)
Imóveis)
identificador
ownernum do
proprietário
identificador único do
imóvel
wpb
bairro/arredores/quarteirão Wpb
Bairro/arredores/quarteirão wpb
bairro/arredores/quarteirão oname
nome do
proprietário
parcel
número do imóvel no
quarteirão
Parcel
Número do imóvel no
quarteirão
parcel
número do imóvel no
quarteirão
address
Endere ço
residencial
do
proprietário
add1
número na rua
Fdate
Data do incêndio
prptype tipo de propriedade
city
cidade de
residência
do
proprietário
state
estado de
residência
do
proprietário
zip
código de
CEP da
residência
do
proprietário
add2
nome da rua
zip
código de CEP
sqft
área em pés quadrados
onum
identificador do
proprietário
Ignfactor fator de ignição
Estloss
Prejuízo estimado
id
identificador único do
imóvel
do proprietário)
id
Id
Identificador único do
imóvel
OWNERS (Informaçõ
valores
landval valor do terreno
bldval
valor do prédio
tax
imposto territorial
landuse tipo de uso do terreno
l
l
l
l
l
l
Conecte as tabelas com o registro de identificação único: PARCELS.ID à FIRES.ID à
TAX.ID. A seguir, OWNERS.OWNERNUM à PARCELS.ONUM.
Os mesmos tipos de consutas feitos em arquivos planos podem ser feitos em tabelas conectadas.
(Agrupe por, Ordene por)
NOTA: Usp de critérios em tabelas conectadas:
¡ Se a sua consulta inclui tabelas conectadas, os valores especificados nos critérios sobre os
campo de tabelas conctadas são sensíveis ao caso. Eles devem ter o mesmo caso
(maiúsculo ou minúsculo) dos valores na tabela.
Conexões relacionais podem ser úteis de outras formas. Outro exemplo de adicionar
uma tabela e conexão é tornar a análise mais simples fornecendo uma tabela pra traduzir
códigos:
Abra o banco de dadose myemployee.
Use Get External Data... e a seguir Import e em K:\11.208\Data\ seleccione dotmode1.dbf e
abra para ver que é uma tabela simples:
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003
Página 14 de 14
Conteúdo da tabela dotmode1
Tabelas Employee e dotmode1 conectadas entre MODE1_CODE e MODE1_TO
Resultado da consulta acima
file://T:\Conteúdo\MIT_FINAL_TRAD\11\11.208\Traduzidos\lecture-notes\lecture4imp.htm
27/9/2003