Bases de dados relacionais e SQL

Desenvolvimento de uma base de dados
Realidade
Bases de dados relacionais e SQL
Modelo conceptual
(e.g. Modelo Entidade-Associação)
○ Conceitos básicos de bases de dados relacionais
○ A 3ª forma normal
○ Structured Query Language (SQL)
Modelo lógico
(e.g. Modelo Relacional)
2
Graça Abrantes
Modelo lógico: SGBD relacional
Relação
Baseiam-se num conjunto de conceitos teóricos
Nas bases de dados relacionais a estrutura
fundamental é a relação.
ç
apresentados
p
em 1970 p
por E. F. Codd.
Uma relação é definida por
Vantagens dos SGBD relacionais:
– um esquema e
– uma tabela.
– simplicidade dos conceitos que utilizam
– existência de definições formais para os conceitos
Um esquema é composto:
• permitiram uma rápida divulgação
• permitiram a adesão de diversos fabricantes de software;
– pelo nome da relação
– pelos nomes dos atributos
– adequação à representação de muitos dos aspectos que
constituem a realidade
3
4
1
Tabelas e esquemas - exemplos
Atributos
Tabela – exemplo:
Um atributo Ai toma valores num conjunto Di
chamado domínio do atributo.
– O domínio determina o tipo de valores que o atributo pode
tomar.
Dado U={A1, A2,...,An}, uma relação R sobre U é
Esquema desta tabela:
um subconjunto de D1 x D2 x ... x Dn.
Conc_1998 (FID, Shape, AREA, PERIMETER, COD_NUTS, DTCC,
DT, DISTRITO, AGRUPC, CONCELHO)
A cada tuplo deste produto cartesiano dá-se
dá se o nome
Outros exemplos de esquema de uma tabela:
de instância da relação R.
PqCampismo(código, designação, capacidade, prop)
Rios (FID, Shape, LENGTH, CODRIOS, DESIGNAÇÃO, TIPO)
5
2. Tabela
6
Observações
os valores de cada atributo pertencem a um mesmo
domínio;
o valor de um atributo é sempre atómico;
– isto é, numa tabela, no cruzamento de uma linha com uma coluna
só pode existir um valor de atributo;
O conjunto das instâncias da relação R constituí uma
numa relação não podem existir instâncias iguais;
a ordem porque se encontram as instâncias de uma relação
e os seus atributos é irrelevante;
tabela em que
p
podem existir instâncias sem valores em alguns
g
dos seus
- as linhas são as instâncias (ou registos)
atributos; neste caso o atributo diz-se opcional e o seu
valor é null;
os nomes (ou identificadores) dos atributos que
constituem o esquema de uma relação são únicos nessa
relação.
- as colunas são os atributos (ou campos)
7
8
2
Chave(s) de uma relação
Chave primária e chave estrangeira
Um conjunto de atributos que tomam valores diferentes
Nos SGBD relacionais, para representar as associações
para cada instância da relação é uma chave da relação.
existentes entre as várias entidades utilizam-se esquemas
Chave
h
primária
i
i da
d
de relações em que figuram atributos comuns.
tabela Distrito
– cada instância pode ser identificada pelo valor da chave: o valor da
chave nunca se repete.
– Exemplo:
Uma chave pode ser composta por um ou mais atributos.
Um atributo que pertence à chave, designa-se por Atributo
primário; um atributo que não pertence à chave, designa-se
por Atributo não primário.
Chave primária de uma relação é um subconjunto
mínimo de atributos cujos valores permitem identificar de
modo único cada uma das instâncias dessa relação.
Uma chave estrangeira de uma relação
é um conjunto de atributos que é chave
primária de outra relação.
9
10
Modelo relacional - Dependências
funcionais
Modelo relacional: 1ª forma normal
Todos os atributos tomam valor único.
Exemplo: Em vez de
•
nome
tipo
•
fronteira
Tejo
principal
marítima, terrestre
Mondego
principal
marítima
tipo
fronteira
marítima
fazer:
nome
fronteira
terrestre
Tejo
principal
sim
sim
Mondego
principal
sim
não
Dada um relação
ç R definida sobre um conjunto
j
de atributos U={A1, A2, …, An}, diz-se que o
atributo Ak depende funcionalmente do
atributo Ai (Ai→Ak) se e só se sempre que duas
instâncias tiverem o mesmo valor em Ai tiverem
também o mesmo valor em Ak.
D
Dependência
dê i funcional
f i l
11
12
3
Modelo relacional: 2ª forma normal
•
•
Modelo relacional: 3ª forma normal
•
A relação tem que estar na 1ª forma normal
Não existem dependências funcionais entre
atributos que não pertencem à chave primária e
partes da chave primária
•
A tabela tem que estar na 2ª forma normal
Não existem dependências
p
funcionais entre
atributos que não pertencem à chave primária
•
• a 2ª forma normal só é relevante quando a chave
primária de uma tabela é composta por mais do que
um atributo
Por exemplo, a tabela conc_1998 não está na 3ª forma
normal
•
13
Normalização
•
Nota: aqui só está uma parte da tabela conc_1998; mesmo
retirando o atributo DISTRITO, esta tabela ainda não
verificaria a 3ª forma normal porque contém mais
dependências funcionais ... (ver pág. 18)
Exemplo: 2ª FN
A passagem de uma forma normal para outra
pode implicar
p
p
a decomposição
p ç de uma tabela
num conjunto de tabelas.
14
3ª FN
Exemplo: dada a tabela na 2ª forma normal
COD NUTS DTCC
COD_NUTS
CONCELHO DT DISTRITO
RC13206
1111
SINTRA
11 Lisboa
RC13205
1110
OEIRAS
11 Lisboa
substituir por 2 tabelas:
COD_NUTS
15
DTCC CONCELHO
DT
RC13206
1111 SINTRA
11
RC13205
1110 OEIRAS
11
CODDT
DISTRITO
11
Lisboa
16
4
Por que é que a tabela abaixo não está na 3ª
forma normal?
Tabela normalizada (3ª forma normal)
•
Numa tabela que verifica as primeiras três formas
normais,, qualquer
q q
atributo que
q não pertence
p
à
chave primária depende completamente e
exclusivamente da totalidade dessa chave.
17
Exemplo:
18
Consistência, redundância e formas normais
Suponha que tem que estruturar um tema relativo
a árvores notáveis numa estrutura de dados
vectoriais de um sistema de informação geográfica
(SIG). Para cada árvore pretende registar o nome
comum, o nome científico, a família, a altura da
árvore e o diâmetro do tronco (DAP). Existe um
milhar de árvores notáveis na região de interesse,
havendo várias espécies representadas por
diversos indivíduos. Como estruturaria essa
informação no SIG?
19
Para evitar redundâncias e para facilitar a manutenção da
consistência dos dados na base de dados, as tabelas
devem verificar as formas normais.
normais
A primeira forma normal é obrigatoriamente respeitada por
uma relação.
A segunda forma normal é obrigatoriamente respeitada por
uma relação cuja chave primária seja simples.
A segunda forma normal nas relações que possuem chave
primária composta e a terceira forma normal asseguram a
não existência de um certo tipo de redundâncias na base
de dados.
20
5
Structured Query Language (SQL)
A instrução SELECT
As consultas a uma base de dados relacional fazem-se em
SQL recorrendo à instrução SELECT. Esta instrução
permite criar conjuntos de registos de uma ou mais tabelas
da base de dados seleccionados segundo diversos critérios.
SQL é uma linguagem
li
normalizada
li d (ANSI) para
consultas e actualizações de bases de dados
relacionais.
21
A cláusula WHERE na instrução SELECT
22
A cláusula WHERE (exemplos)
SELECT atributo1, atributo2, .... (ou *)
FROM tabela1, tabela 2, ...
WHERE condição;
esta instrução selecciona os
registos que verificam a
condição; na condição podem
ser utilizados operadores
relacionais (<, <=, >, >=, =,
<>) , operadores lógicos (NOT,
AND OR) e os operadores IN
AND,
e LIKE.
select * from solo where “CODSOLO” >= 800
select * from solo
where “CODSOLO” > 400 and “CODSOLO” <= 700
select * from solo
where not (“CODSOLO” > 400 and “CODSOLO” <= 700)
select * from solo
where “CODSOLO” <= 400 or “CODSOLO” > 700) (1)
(1)
23
Nota: esta instrução selecciona exactamente as mesmas features que a
instrução
ç anterior
select * from conc_1998 where “CONCELHO” like ‘A%’
select * from rios
where "TIPO" = 'Principal' or
"DESIGNACAO" in ( 'Fronteira terrestre' , 'Fronteira marítima' )
select * from conc_1998 where “CONCELHO” = ‘’
24
6
A cláusula WHERE nos SIG
Cruzamento de tabelas (“join”)
Nos SIG a condição da instrução SELECT também pode
A cláusula FROM especifica o(s) nome(s) da(s)
utilizar operadores relacionais espaciais: intersect, are
within a distance of, contain, are within, touch the
boundary of, ...
tabela(s) em que se encontram os registos a
seleccionar.
A instrução SELECT produz o produto cartesiano das
tabelas especificadas, isto é, cada registo do resultado
é composto por um registo de cada uma dessas
tabelas.
Num
N
cruzamento
t (join)
(j i ) de
d tabelas
t b l a cláusula
lá l WHERE
é utilizada para seleccionar no resultado do produto
cartesiano os tuplos que correspondem a registos em
que o valor de uma chave estrangeira é igual ao valor
de uma chave primária.
25
26
Outro exemplo:
Exemplo (cruzamento ou join):
SELECT *
FROM conc,ValorAcrescentado
WHERE DTCC=codigo;
onde codigo é a chave
primária da tabela
ValorAcrescentado e DTCC
é a correspondente chave
estrangeira na tabela conc.
1.
2.
3.
27
Suponha que pretende estruturar para uma determinada região um
tema relativo a culturas agrícolas numa estrutura de dados vectoriais
ç geográfica
g g
(SIG).
(
) Para cada parcela
p
de
de um sistema de informação
terreno ocupada por uma determinada cultura pretende-se registar (i)
o nome comum da espécie, (ii) o nome científico, (iii) o rendimento
médio da cultura na região, (iv) a data da sementeira ou plantação e
(v) a área da parcela. A região de interesse é um concelho rural do
norte de Portugal, com um elevado índice de desagregação das
parcelas.
Que estrutura de dados deve usar?
Explique porque é vantajoso usar duas tabelas na base de dados do
sistema de informação geográfica, uma referente a objectos
espaciais, outra com informação não espacial.
Como deve proceder para – através de pesquisas à base de dados que
estruturou – obter informação, para cada tipo de cultura, sobre a
primeira data de sementeira/plantação na região?
28
7