Bases de dados relacionais (3ª aula) ○ Modelo Entidade-Associação (revisões)

Bases de dados relacionais (3ª aula) ○ Modelo Entidade-Associação (revisões)

○ Conceitos básicos de bases de dados relacionais (revisões)○ A 3ª forma normal (revisões)○ Structured Query Language – SQL (revisões)

Geodatabases no ArcGIS 9.x○ Criação de uma geodatabase○ Criação de uma feature class armazenada numa geodatabase○ Importação de uma shapefile para uma geodatabase

Graça Abrantes

2

Desenvolvimento de uma base de dados

Realidade

Modelo conceptual(e.g. Modelo Entidade-Associação)

Modelo lógico(e.g. Modelo Relacional)

3

Modelos conceptuais de tipo E-A Os modelos de tipo E-A são compostos por

entidades e associações. As entidades representam objectos reais (concre-

tos ou abstractos) e fenómenos que possuem uma descrição que é determinada pelos problemas que se pretendem resolver por meio da Base de Dados. – exemplos: pessoas, parcelas, fontes de poluição, ...

As associações representam relacionamentos relevantes entre entidades.– exemplos: uma pessoa é proprietária de parcela(s),

parcela é utilizada para cultura(s), ...

4

Tipo de entidades e atributos

As entidades que possuem uma descrição comum são representadas por um tipo de entidade, o qual é caracterizado por um conjunto de atributos.– exemplos: pessoa é descrita por nome, nº de contribuinte,

data de nascimento, ...; parcela é descrita por coordenadas geográficas, tipo de uso, ...

Os valores que tomam cada um dos atributos de um tipo de entidade permitem descrever e distinguir entre si as entidades que pertencem a um mesmo tipo de entidade.– exemplos: o atributo data de nascimento pode tomar o valor

2008/03/03 ou ...; o atributo tipo de uso (de parcelas) pode tomar o valor agrícola ou florestal ou industrial ou urbano ou ...

5

Exemplo

Num sistema de informação geográfica, um tipo de entidade pode ser constituído por parques de campismo. Cada parque de campismo pode ter um conjunto de atributos (e.g. designação, capacidade, proprietário, ...).

Um segundo tipo de entidade poderia ser constituído por unidades territoriais (e.g. concelhos), tendo atributos (e.g. código, designação).

Parques de campismo e concelhos podem estar relacionados.

6

Diagrama do modelo conceptual: exemplo

Concelho Empresa

Situado

Actua

PqCampismo

7

Atributos de um tipo de Associação

Um tipo de associação pode possuir atributos que permitam descrever as características próprias de cada associação.– por exemplo, cada empresa tem um certo número de

trabalhadores em cada concelho.

8

Diagrama do modelo conceptual

Concelho Empresa

Situado

Actua

PqCampismo

designaçãocapacidadeproprietário

códigodesignação

número trab.NIFnome

9

Grau de um tipo de associação

Um tipo de associação caracteriza-se pelo número de tipos de entidade que envolve, podendo ser unária, binária (o caso mais frequente), ternária, ...– no exemplo anterior, as associações Situado e Actua

são binárias

10

Multiplicidade (ou cardinalidade) de um tipo de associação

Um tipo de associação binária pode ser:– de um para um (1:1) quando cada entidade só pode

ocorrer numa única associação desse tipo;– de um para muitos (1:n) quando cada entidade de um

tipo só pode ocorrer numa associação, mas as entidades do outro tipo podem ocorrer em mais do que uma associação desse tipo;

– de muitos para muitos (n:n) quando não existe nenhuma restrição ao número de ocorrências de cada entidade em associações desse tipo.

11

Multiplicidade (continuação)

A multiplicidade de um tipo de associação binária é indicada por 4 números: número mínimo e máximo de entidades que ocorrem numa associação, em cada sentido.

A multiplicidade é frequentemente 0,1 ou 1,1 ou 0,n ou 1,n, mas pode ser diferente (2,2 ou 2,n, por exemplo).

12

Exemplo

Concelho Empresa

Situado

Actua

PqCampismo

designaçãocapacidadeproprietário

códigodesignação

número trab.NIFnome

0:n

1:1

1:n 1:n

1:n

n:n

13

Criação de um modelo conceptual de dados

1. Identificar tipos de entidade e seus atributos2. Identificar tipos de associação e seus

atributos3. Definir multiplicidades dos tipos de

associação

14

Modelo lógico: SGBD relacional

Baseiam-se num conjunto de conceitos teóricos apresentados em 1970 por E. F. Codd.

Vantagens dos SGBD relacionais:– simplicidade dos conceitos que utilizam– existência de definições formais para os conceitos

• permitiram uma rápida divulgação • permitiram a adesão de diversos fabricantes de software;

– adequação à representação de muitos dos aspectos que constituem a realidade

15

Relação

Nas bases de dados relacionais a estrutura fundamental é a relação.

Uma relação é definida por um esquema e por uma tabela.

Um esquema é composto:– pelo nome da relação– pelos nomes dos atributos

16

1. Esquema

Exemplos:

PqCampismo(designação, capacidade, prop)

Concelho(código,designação)

17

Atributos

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 é um subconjunto de D1 x D2 x ... x Dn.

A cada tuplo deste produto cartesiano dá-se o nome de instância da relação R.

18

2. Tabela

O conjunto das instâncias da relação R constituí uma tabela em que - as linhas são as instâncias (ou registos)- as colunas são os atributos (ou campos)

19

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;

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; podem existir instâncias sem valores em alguns dos seus

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.

20

Chave(s) de uma relação

Um conjunto de atributos que tomam valores diferentes para cada instância da relação é uma chave da relação. – cada instância pode ser identificada pelo valor da

chave: o valor da chave nunca se repete. Uma chave pode ser composta por um ou mais

atributos. Atributo primário: pertence à chave Atributo não primário: não pertence à chave

21

Chave primária e chave estrangeira

Nos SGBD relacionais, para representar as associações existentes entre as várias entidades utilizam-se esquemas de relações em que figuram atributos comuns.

Chave primária de uma relação é um subcon-junto 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.

22

Passagem do modelo E-A para relacional

Um modelo de tipo E-A descreve de uma forma natural e simples a realidade.

Os modelos deste tipo são suficientemente flexíveis para poderem ser utilizados eficazmente numa fase em que a estruturação dos dados é ainda confusa.

A estas vantagens pode ainda adicionar-se a facilidade com que é possível efectuar a sua passagem para um SGBD relacional, bem formalizado e comercializado por diversos fornecedores de software.

23

Regras para transformação de um modelo E-A num esquema relacional (1)

Cada tipo de entidades do modelo E-A traduz-se por uma relação em que a chave primária e os atributos provêm do tipo de entidade.

24

Regras para transformação de um modelo E-A num esquema relacional (2)

Um tipo de associação de 1:n (um para muitos) entre dois tipos de entidades Ei e Ej que tenha uma multiplicidade igual a 0,1 ou 1,1 para um tipo de entidade Ei traduz-se por uma chave estrangeira na relação R que é tradução de Ej.

Ej Ei1,n 1,1

Rj Ri

25

Regras para transformação de um modelo E-A num esquema relacional (2')

Um tipo de associação de 1:1 (um para um) entre dois tipos de entidades Ei e Ej é tratado como um caso especial do tipo de associação 1:n– traduz-se por uma chave estrangeira na relação que é

tradução de Ej ou de Ei; – se apenas para um destes tipos entidades a

multiplicidade for 1,1 (sendo para a outra 0,1) dá-se preferência à relação que traduz este tipo de entidade,

– no caso contrário é indiferente qual a relação que é escolhida.

26

Regras para transformação de um modelo E-A num esquema relacional (3)

Um tipo de associação n:n (muitos para muitos) traduz-se por uma relação R em que a chave primária inclui as chaves estrangeiras que são chave primária dos tipos de entidade que a constituem; os outros atributos são a tradução dos tipos de atributos da associação (se esta possuir algum).

Ej Ei1,n 1,n

RjRiR

27

Nota: se nas associações n:n as chaves estrangeiras não são suficientes para formar a chave primária da relação R, na constituição desta devem também ser utilizados outros atributos de forma a ser obtida uma chave primária.

28

Modelo relacional: 1ª forma normal

• Todos os atributos tomam valor único.

Exemplo: Em vez de

Fazer:

Tejo principal marítima, terrestre

Mondego principal marítima

Tejo principal sim sim

Mondego principal sim não

29

Modelo relacional - Dependências funcionais

• Dada um relação R definida sobre um conjunto de atributos U={A1, A2, …, An}, diz-se que o atributo Ak depende funcionalmente do atributo Ai (AiAk) se e só se sempre que duas instâncias tiverem o mesmo valor em Ai tiverem também o mesmo valor em Ak.

30

Modelo relacional: 2ª 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 2ª forma normal só é relevante quando a chave primária de uma tabela é composta por mais do que um atributo

31

Modelo relacional: 3ª forma normal

• A tabela tem que estar na 2ª forma normal• Não existem dependências funcionais entre

atributos que não pertencem à chave primária

32

Normalização

• A passagem de uma forma normal para outra pode implicar a decomposição de uma tabela num conjunto de tabelas.

33

Exemplo: 2ª FN 3ª FN

Exemplo: dada a tabela na 2ª forma normal

Fazer:

e

RC13206 1111 SINTRA 11 Lisboa

RC13205 1110 OEIRAS 11 Lisboa

RC13206 1111 SINTRA 11

RC13205 1110 OEIRAS 11

11 Lisboa

34

Tabela normalizada (3ª forma normal)

• Numa tabela que verifica as primeiras três formas normais, qualquer atributo que não pertence à chave primária depende completamente e exclusivamente da totalidade dessa chave.

35

Por que é que a tabela abaixo não está na 3ª forma normal?

36

Exemplo:

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?

37

Consistência, redundância e formas normais

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.

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.

38

Structured Query Language (SQL)

SQL é uma linguagem normalizada (ANSI) para consultas e actualizações de bases de dados relacionais.

39

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.

40

A cláusula WHERE: selecção de linhas

SELECT atributo1, atributo2, .... (ou *)FROM tabela1, tabela 2, ...WHERE condição;

onde o argumento de WHERE é uma condição que os registos seleccionados verificam; podem ser utilizados operadores relacionais (<, <=, >, >=, =, <>) , operadores lógicos (NOT, AND, OR) e os operadores IN, IS, BETWEEN e LIKE.

41

A cláusula WHERE (exemplos) select * from conc where area > 500000000 select * from conc where concelho like ‘A%’ select * from rios where "TIPO" = 'Principal' or

"DESIGNACAO" in ( 'Fronteira terrestre' , 'Fronteira marítima' )

42

Cruzamento de tabelas (“join”)

A cláusula FROM especifica o(s) nome(s) da(s) 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 cruzamento (join) de tabelas a cláusula 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.

43

SELECT *FROM conc,ValorAcrescentadoWHERE DTCC=codigo;

Exemplo (cruzamento ou join):

onde codigo é a chave primária da tabela ValorAcrescentado e DTCC é a correspondente chave estrangeira na tabela conc.

44

A cláusula GROUP BY e as funções de agregação

As funções de agregação aplicam-se a selecções de registos. Podem ser utilizadas as funções SUM, AVG, MAX, MIN e COUNT,...

A utilização de funções de agregação é feita frequentemente em conjunto com a cláusula GROUP BY. Esta especifica os conjuntos de registos seleccionados que são objecto da(s) função(ões).

Quando é utilizada a cláusula GROUP BY, só podem ser indicados na cláusula SELECT os atributos incluídos na cláusula GROUP BY (para além daqueles que são argumento de uma função).

45

GROUP BY

Group by indica o atributo que define o agrupamento Select atrib1From tabela_entradaGroup by atrib1

Se se quiser também incluir no resultado da operação uma função f dos valores do atributo atrib2, aplicada a cada grupo definido da forma acima:

Select atrib1, f(atrib2)From tabela_entradaGroup by atrib1

46

Dissolve vs GROUP BY

Num SIG, a operação de “dissolução” de objectos espaciais (dissolve) corresponde a uma pesquisa com cláusula GROUP BY na tabela do tema que se quer “dissolver”.

47

SELECT DT as conc.DT, area AS SUM(conc.area) FROM conc GROUP BY conc.DT;

48

Resultado da operação de dissolução

49

Outro exemplo:Suponha que pretende estruturar para uma determinada região um tema relativo a culturas agrícolas numa estrutura de dados vectoriais de um sistema de informação geográfica (SIG). Para cada parcela de 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.

1. Que estrutura de dados deve usar? 2. 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.

3. 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?

Geodatabases no ArcGIS 9.x

51

O que é uma Geodatabase Uma geodatabase é uma base de dados (por ex. Access) onde

é possível guardar conjuntos de dados geográficos organizados em feature classes.

Estes dados são armazenados, analisados e consultados como layers semelhantes a ficheiros shape.

Um geodatabase pode armazenar simultaneamente dados vectoriais e raster.

Vantagens:– facilita a gestão dos dados,– actualiza automaticamente comprimentos, áreas e perímetros,– suporta redes complexas, topologias, relações entre classes, regras

para manutenção da integridade referencial e topológica entre objectos espaciais, ...

52

Criação de uma Geodatabase

Renomear!

53

Criação de um Feature Class (FC)

Nomear

54

Indicar o tipo de geometria da FC

Nomear

Clicar

ClicareseleccionarPonto, Linhaou Polígono

Clicar

55

Indicar o tipo de geometria da FC

Nomear

Clicar

ClicareseleccionarPonto, Linhaou Polígono

Clicar

56

Indicar a referência espacial da FC

Preencher

A menor unidade mensurávelque pode ser guardada nesta FC,por ex. 10m

Seleccionar o sistemade georreferenciação

57

Importar uma shapefile