Bases de Dados
(usando Microsoft Access)
Alberto Manuel Simões
([email protected])
1
Alberto Simões - Dept Informática - Univ. Minho
Motivação




2
Qualquer colecção deve estar catalogada;
Qualquer catálogo deve permitir pesquisas;
Um catálogo não é mais que uma colecção
de registos;
Podemos ver uma base de dados como
sendo um catálogo informatizado e com
métodos optimizados de pesquisa e
armazenamento.
Alberto Simões - Dept Informática - Univ. Minho
Noção de Base de Dados


Qualquer conjunto de registos pode ser visto
como uma base de dados;
Um conjunto de registos com a mesma
estrutura é denominado por tabela:
Turma Número
3
Nome
Rua
Localidade
A
1
António Martins Sousa Pires
Barcelos
A
2
Maria Irene
Joana d'Arc
Barcelos
B
1
João Francisco
Pires Viegas
Pousa
C
2
Manuel Saraiva Almirante Coutinho
Barcelos
Alberto Simões - Dept Informática - Univ. Minho
Noção de Base de Dados (2)
4

A uma linha da tabela chama-se registo;

A cada elemento do registo chama-se campo
ou atributo;

Chama-se chave a um campo (ou conjunto
de campos) que nunca se repete em toda a
tabela;
Da tabela anterior, que chave poderíamos escolher?
Alberto Simões - Dept Informática - Univ. Minho
Tipos de dados

Cada campo de uma tabela tem um tipo de
dados definido:
–
–
–
–
–
Número;
Texto;
Booleano;
Data;
Moeda
Que tipos de dados associar a cada um do campos da tabela anterior?
5
Alberto Simões - Dept Informática - Univ. Minho
Divisão de Tabelas

Vamos construir uma base de dados para
livros:
–
–
–
–
–
–
6
Título;
ISBN;
Ano da edição;
Editora;
Colecção;
Autores
Quantos autores é que um livro tem?
Teoricamente, não existe limite para o
número de autores.
Alberto Simões - Dept Informática - Univ. Minho
Divisão de Tabelas (2)

Soluções:
–
–
criar um campo texto, onde se introduziria todos
os nomes dos autores;
criar um número de campos elevado para
armazenar os autores:


7
Ao definir um número de autores elevado para cada livro,
a tabela iria ficar com muitos campos em branco;
Qual o número de campos a definir? Ou se define um
número excessivamente exagerado, ou pode sempre
ocorrer que venha a aparecer um livro com mais autores
que campos disponíveis.
Alberto Simões - Dept Informática - Univ. Minho
Divisão de Tabelas (3)

A solução para este tipo de casos consiste
em dividir a informação em duas tabelas:
–
–
8
informação de cada livro;
autor por livro;
ISBN
Nome Autor
01-234567
John Gardner
03-123456
Ana Sofia
03-123456
Pedro Raposo
Titulo
ISBN
Ano Edição
Editora
Colecção
Crimes Perfeitos
01-234567
1973
Suspense
ABC do Crime
Manual do Access
03-123456
2001
Porto Editora
Informática
Alberto Simões - Dept Informática - Univ. Minho
Chaves Estrangeiras


O campo ISBN estabelece a ligação entre as
tabelas Livros e Autores. Este campo, na
tabela Autores, diz-se que é uma chave
estrangeira.
Note-se que não existe obrigatoriedade de
utilizar o mesmo nome para ambos os
campos ao definir uma ligação entre tabelas.
Na tabela de autores, que chave poderíamos escolher?
9
Alberto Simões - Dept Informática - Univ. Minho
Divisão de Tabelas (4)

Considerando que se pretende armazenar
informação sobre os autores:
código Livro
Nome Autor
Ano Nascimento
Nacionalidade
01-234567
John Gardner
1944
EUA
03-123456
Ana Sofia
1970
Portugal
03-123456
Pedro Raposo
1971
Portugal
Mas, John Gardner escreveu uma vasta lista de obras.
Vamos repetir toda esta informação para cada um dos livros?
10
Alberto Simões - Dept Informática - Univ. Minho
Dependências Funcionais

A solução convencional para este problema
consiste em identificar as dependências
entre campos:
–
–

11
o campo Nacionalidade depende do campo
Nome Autor
o campo Ano Nascimento depende do campo
Nome Autor
Ou seja, se soubermos o valor do campo
Nome Autor podemos saber o valor dos
campos Ano Nascimento e Nacionalidade.
Alberto Simões - Dept Informática - Univ. Minho
Divisão de Tabelas (5)

12
Sempre que se verificar a existência de
campos numa tabela que não dependam da
chave primária, a tabela deve ser dividida;
ISBN
Nome Autor
01-234567
John Gardner
03-123456
Ana Sofia
03-123456
Pedro Raposo
Nome Autor
Ano Nascimento
Nacionalidade
John Gardner
1944
EUA
Ana Sofia
1970
Portugal
Pedro Raposo
1971
Portugal
Alberto Simões - Dept Informática - Univ. Minho
Resultado Normalizado
13
Titulo
ISBN
Ano Edição
Editora
Colecção
Crimes Perfeitos
01-234567
1973
Suspense
ABC do Crime
Manual do Access
03-123456
2001
Porto Editora
Informática
ISBN
Nome Autor
01-234567
John Gardner
03-123456
Ana Sofia
03-123456
Pedro Raposo
Em que ano nasceram os
autores do Manuel de Access?
Nome Autor
Ano Nascimento
Nacionalidade
John Gardner
1944
EUA
Ana Sofia
1970
Portugal
Pedro Raposo
1971
Portugal
Alberto Simões - Dept Informática - Univ. Minho
Exercício 1
Criar as tabelas
necessárias para
acomodar os
dados de uma
factura;
E todos os dados
da empresa?
14
Factura N.:
1425433
Data:
Cliente:
Produto
04-05-2002
António Carlos Silva e Sá
Descrição
Qt.
Preço Unit.
Total
XX743K
Pregos
100
€
1,00
€ 100,00
YZ3MN
Porcas
150
€
1,00
€ 150,00
XP992O
Parafusos
170
€
2,00
€ 340,00
YZ3MN
Porcas
140
€
1,00
€ 140,00
Total
Alberto Simões - Dept Informática - Univ. Minho
€ 730,00
Resolução 1.1

Cada factura contém:
–
–
–
–
Um número da factura;
Uma data em que foi emitida;
Um cliente que efectuou a compra;
Várias linhas de produtos

–
15
Código do produto, descrição, quantidade, preço
unitário e valor total;
Um valor total das compras
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.2

Uma vez que não podemos saber quantos
produtos vão ser comprados, consideremos
apenas os dados que aparecem uma só vez.
Nº Factura
Data
Cliente
Total
1425433 04-05-2002 António Carlos Silva e Sá € 730.00
1425434 04-05-2002 Ana Maria Teixeira Cunha € 900.00
1425435
16
04-05-2002 José Miguel Lopes Araújo € 400.00
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.3

Produto Designação
XX743K
Pregos
YZ3MN
Porcas
XP992O Parafusos
Qt
100
150
170
Preço Unitário
€ 1.00
€ 1.00
€ 2.00
Total
€ 100.00
€ 150.00
€ 340.00
YZ3MN
140
€ 1.00
€ 140.00

17
Criemos uma nova tabela para os produtos:
Porcas
Mas, quem é que efectuou estas compras?
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.4

Para relacionar as compras com o cliente,
temos de adicionar uma chave estrangeira a
esta tabela:
Factura
1425433
1425433
1425433
1425433
18
Produto Designação
XX743K
Pregos
YZ3MN
Porcas
XP992O Parafusos
YZ3MN
Porcas
Qt
100
150
170
140
Preço Un.
€ 1.00
€ 1.00
€ 2.00
€ 1.00
Total
€ 100.00
€ 150.00
€ 340.00
€ 140.00
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.5


O preço de cada produto, e respectiva
descrição são dependências funcionais do
código do produto;
Devemos dividir a tabela em:
–
–
19
Detalhe de cada linha da factura;
Informação sobre cada produto;
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.6
Produto Designação
XX743K
Pregos
YZ3MN
XP992O
20
Porcas
Parafusos
Factura Produto
1425433 XX743K
Qt
100
Total
€ 100.00
1425433 YZ3MN
1425433 XP992O
1425433 YZ3MN
150
170
140
€ 150.00
€ 340.00
€ 140.00
Preço Un.
€ 1.00
€ 1.00
€ 2.00
Alberto Simões - Dept Informática - Univ. Minho
Resolução 1.7

Para melhorar o sistema de facturação
poderíamos ainda:
–
–
–
21
Atribuir um código a cada cliente;
Criar uma tabela de clientes, com a sua
informação, como a morada, telefone, número de
contribuinte;
Cada factura relacionava-se com o cliente
apenas pelo seu código
Alberto Simões - Dept Informática - Univ. Minho
Exercício 2

Usando o exemplo
da factura, vamos
criar as seguintes
tabelas em Access:
Facturas
Número number
22
Data date/time
Cliente text (200)
Total currency
Produtos
Código text (10)
Designação text (200)
Preço Unitário currency
Factura/Produto
Factura number
Produto text (10)
Quantidade number
Total currency
Alberto Simões - Dept Informática - Univ. Minho
Relacionamentos

Os relacionamentos entre tabelas podem ser
de tipos diferentes:
–
–
–

23
1–n
1–1
m–n
Estes valores correspondem ao número de
elementos de uma tabela a que podem
corresponder a elementos de outra tabela;
Alberto Simões - Dept Informática - Univ. Minho
Relacionamentos (1-n)

Uma turma tem n alunos
–
–
–
24
Neste caso, a tabela de relacionamento associa a
cada aluno apenas uma turma;
Ou seja, um aluno não pode pertencer a duas
turmas;
Neste caso, o relacionamento é feito na tabela
alunos, em que se adiciona a chave estrangeira
relativa à turma (chave da tabela de turmas).
Alberto Simões - Dept Informática - Univ. Minho
Relacionamentos (1-1)





25
Supondo que temos uma tabela de
professores e uma outra de departamentos;
Um professor director de um departamento,
não o pode ser de um outro;
Cada departamento tem apenas um director.
Nestes casos, pouco frequentes, o
relacionamento pode ser feito em qualquer
uma das tabelas;
Neste caso em particular, seria mais sensato
colocar na tabela de departamentos.
Alberto Simões - Dept Informática - Univ. Minho
Relacionamentos (m-n)




26
São os mais frequentes;
Um produto pode aparecer em mais do que
uma factura;
Uma factura pode conter mais do que um
produto;
Neste caso, constrói-se uma tabela auxiliar
que efectua o relacionamento.
Alberto Simões - Dept Informática - Univ. Minho
Integridade referencial



27
Chama-se integridade referencial ao processo
de garantir que todos os dados de campos
relacionados existem em ambas as tabelas;
Por exemplo, existir uma factura com um
código de produto que não existe na tabela de
produtos;
Uma factura passada a um cliente que não
existe na tabela de clientes;
Alberto Simões - Dept Informática - Univ. Minho
Exercício 3
28

Utilizando as tabelas construídas no
exercício 2, efectuar os devidos
relacionamentos no Microsoft Access.

Verificar o funcionamento do Microsoft
Access em relação à integridade referencial;
Alberto Simões - Dept Informática - Univ. Minho
SQL
Query Language
Alberto Manuel Simões
([email protected])
29
Alberto Simões - Dept Informática - Univ. Minho
Motivação



30
As bases de dados servem para se poder
aceder de forma eficiente aos seus dados;
O uso de uma interface gráfica para a
pesquisa nem sempre é eficiente;
Então, desenvolveu-se uma linguagem
específica para este tipo de consultas;
Alberto Simões - Dept Informática - Univ. Minho
Noções




31
Chamamos querie a uma pergunta à base
de dados;
Todas as respostas a queries são tabelas;
A estas tabelas também chamamos de
views, porque consistem em vistas sobre
porções da informação da base de dados;
As views são tabelas virtuais, ou seja, não
são guardadas na base de dados.
Alberto Simões - Dept Informática - Univ. Minho
Nº Factura
Data
Cliente
Total
1425433 04-05-2002 António Carlos Silva e Sá € 730.00
1425434 04-05-2002 Ana Maria Teixeira Cunha € 900.00
1425435
04-05-2002 José Miguel Lopes Araújo € 400.00
Produto Designação
XX743K
Pregos
YZ3MN
Porcas
XP992O Parafusos
Preço Un.
€ 1.00
€ 1.00
€ 2.00
Factura
Produto
Qt
1425433
1425433
1425433
1425433
32
XX743K
YZ3MN
XP992O
YZ3MN
100
150
170
140
Total
Consideremos as
seguintes tabelas:
• Facturas
• Produtos
• Facturas / Produtos
Os resultados dos
próximos exemplos são
retirados desta base de
dados.
€ 100.00
€ 150.00
€ 340.00
€ 140.00
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(apenas com uma tabela)

O comando SELECT permite seleccionar
determinadas colunas de uma tabela:
SELECT <campos> FROM <tabela> ;

Por exemplo, podemos seleccionar os
nomes dos produtos com:
Designação
SELECT Designação
FROM Produtos;
33
Pregos
Porcas
Parafusos
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(apenas com uma tabela - 2)


Podemos pedir mais do que um campo;
Por exemplo, o código e a designação do
produto:
SELECT Produto, Designação
FROM Produtos;
Produto Designação
XX743K
YZ3MN
XP992O
34
Pregos
Porcas
Parafusos
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(apenas com uma tabela - 3)

Para seleccionar todos os campo, podemos
usar um atalho (wildcard):
SELECT * FROM Produtos;
Produto Designação
XX743K
Pregos
YZ3MN
XP992O
35
Porcas
Parafusos
Preço Un.
€ 1.00
€ 1.00
€ 2.00
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(explicitando condições na selecção)



36
É possível especificar condições para
refinamento da selecção;
Uma linha só será incluída no resultado se a
condição for verificada para essa linha;
As condições são testadas nas linhas da
tabela original, i.e. podemos definir
condições sobre colunas que fazem parte da
tabela original mesmo que essas colunas
não apareçam no resultado.
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(explicitando condições na selecção 2)

Neste caso, usa-se a sintaxe:
SELECT <campos> FROM <tabela>
WHERE <condição>;

Seleccionar produtos que custem menos de
€ 2.00:
SELECT Descrição
FROM Produtos
WHERE PreçoUn<2;
37
Designação
Pregos
Porcas
Alberto Simões - Dept Informática - Univ. Minho
Operações de Selecção
(explicitando condições na selecção 3)

Podemos escrever condições mais
complicadas…
SELECT Disciplinas.nome
FROM Disciplinas
WHERE ano = 1 AND semestre = 0;
38
Alberto Simões - Dept Informática - Univ. Minho