BeD - AEISTECP - GRUPO DE ESTUDOS

Transcrição

Capitulo 1
Introdução a Bases de Dados
Transparências
1
Capitulo 1 - Objectivos
 Uso
comum das bases de dados.
 Características dos Sistemas de Ficheiros.
 Problemas com os Sistemas de Ficheiros.
 Significado do termo base de dados.
 Significado do termo Sistema de Gestão de
Bases de Dados (SGBD/DBMS).
2
 Funções
típicas de um SGBD.
 Principais Componentes de um SGBD.
 Actores num SGBD.
 História do desenvolvimento dos SGBDs.
 Vantagens e Desvantagens num SGBD.
3
Exemplos de aplicações de bases de dados
 Compras
num hipermercado
 Compras usando um cartão crédito
 Reservar férias numa agência de viagens
 Usar a biblioteca
 Fazer um seguro
 Usar a Internet
 Estudar uma Universidade
4
Sistema baseado em ficheiros
 Colecção
de aplicações que executam tarefas
para utilizadores finais. (ex: relatórios).
 Cada
aplicação define e gere os seus próprios
dados.
5
Processamento baseado em ficheiros
6
Limitações com os Sistemas de Ficheiros

Separação e Isolamento dos dados
– Cada aplicação mantém o seus próprios dados.
– Utilizador de uma aplicação pode não ter o
conhecimento de informação útil na posse de outra
aplicação.

Duplicação dos dados
– Os mesmos dados estão em diferentes aplicações.
– Espaço desperdiçado e possibilidade de diferentes
valores e/ou diferentes formatos para a mesma
informação.
7
Limitações com os Sistemas de Ficheiros
 Dependência
de dados
– A estrutura dos ficheiros está definida no código da
aplicação.
 Formato
de ficheiros incompatíveis
– As aplicações estão escritas em diferentes
linguagens, o que torna difícil o acesso a outros
ficheiros.
 Queries
fixas, proliferação de aplicações
– As aplicações são feitas para satisfazer
determinados requisitos.
– Qualquer novo requisito necessita de um nova
aplicação.
8
Abordagem das Bases de Dados
 Surgiu
porque:
– Definição dos dados estava embutida nas aplicações,
em vez de estarem armazenadas separadamente.
– Não existe controlo sobre o acesso e manipulação
dos dados para além do imposto pela aplicação.
 Resultado:
– As bases de dados e o Sistema de Gestão de Bases de
Dados (SGBD).
9
Bases de Dados
 Colecção
partilhada de dados logicamente
relacionados (e respectiva descrição),
projectada para responder ás necessidades de
informação organizada.
 Dicionário
de Dados (metadados) disponibiliza
a descrição dos dados para obtermos
aplicações independentes.
 Dados
relacionados são compostos por
entidades, atributos e relacionamentos da
informação de uma organização.
10
Sistema de Gestão de Bases de Dados
(SGBD/DBMS)
 Um
sistema de software que permite que os
utilizadores definam, criem e mantenham a
base de dados e providencia um acesso
controlado a essa base de dados.
11
Sistema de Gestão de Bases de Dados
(SGBD/DBMS)
12
 Linguagem
de Definição de Dados (LDD/DDL).
– Permite a especificação de tipos de dados,
estruturas e restrições de dados.
– Todas as especificações estão armazenadas na base
de dados.
 Linguagem
de Manipulação de Dados
(LMD/DML).
– Facilita o acesso as bases de dados através de uma
linguagem de manipulação (query language).
13
 Acesso
controlado as bases de dados pode
incluir:
–
–
–
–
–
Sistema de Segurança.
Sistema de integridade.
Sistema de controlo de concorrência.
Sistema de recuperação de falhas.
Dicionário de Dados
 Mecanismo
de Vistas.
– Providencia os utilizadores apenas com a
informação que eles precisam ou necessitam.
14
Vistas
 Permite
a cada utilizador ter a sua vista sobre a
base de dados.
 Uma
vista é um filtro sobre a base de dados.
15
Vistas
 Benefícios:
– Complexidade reduzida;
– Providência alguma segurança;
– Providência um mecanismo de costumização da
aparência da base de dados;
– Apresenta uma imagem consistente e persistente da
estrutura da base de dados, mesmo que esta tenha
sido alterada.
16
Componentes de um SGBD
17
 Hardware
– Varia entre um PC e uma rede de
computadores.
 Software
– SGBD, sistema operativo, software de rede (se
necessário) e as aplicações.
 Dados
– Usados pela organização e respectiva
descrição a que chamamos esquema.
18
 Procedimentos
– Instruções e regras que devem ser aplicadas ao
desenho e ao uso da base de dados e do SGBD.
 Pessoas
19
Tarefas num ambiente de Base de Dados
 Administrador
de Dados (DA)
 Administrador da Base de Dados (DBA)
 Desenhador de Bases de Dados (Lógica e
Física)
 Programadores das aplicações
 Utilizadores Finais (ingénuos e sofisticados)
20
Historia dos Sistemas de Bases de Dados
 1ª
Geração
– Hierárquico e de Rede
 2ª
Geração
– Relacional
 3ª
Geração
– Objectos Relacionais
– Orientado a Objectos
21
Vantagens dos SGBDs
 Controlo
sobre a redundância de dados
 Consistência de Dados
 Mais informação tendo os mesmo dados
 Partilha de Dados
 Integridade dos Dados melhorada
 Mais Segurança
 Implica uso de standards
 Economia de Escala
22
Vantagens dos SGBDs
 Requisitos
conflituosos balanceados
 Acessibilidade aos dados e rapidez de resposta
melhorados
 Mais produtividade
 Melhoria na manutenção através da
independência dos dados
 Mais concorrência
 Serviços de cópias de segurança e de
recuperação de falhas melhorados
23
Desvantagens dos SGBDs
 Complexidade
 Tamanho
 Custo
do SGBD
 Custos de hardware acrescidos
 Custo da conversão
 Performance
 Maior impacto em caso de falha
24
Capitulo 2
Ambiente de Base de Dados
Transparências
25
 Objectivo
da arquitectura de três níveis nas bases
de dados.
 Conteúdo
dos níveis externo, conceptual e interno.
 Significado
da independência de dados lógica e
física.
 Distinção
entre LDD e LMD.
 Classificação
dos modelos de dados.
26
Objectivo/Importância da modelação conceptual.
 Funções e Serviços que um SGBD deve fornecer.
 Componentes de Software de um SGBD.
 Significado da arquitectura cliente–servidor e
vantagens deste tipo de arquitectura para o SGBD.
 Função e Importância do dicionário de dados.

27
Objectivos da Arquitectura de três níveis
 Todos
os utilizadores devem poder aceder aos
mesmos dados.
 A vista
de um utilizador é imune a alterações
feitas noutras vistas.
 Os
utilizadores não necessitam de saber
pormenores do armazenamento físico da base
de dados.
28
Objectivos da Arquitectura de três níveis

DBA deve poder alterar a estrutura de armazenamento
da base de dados sem que tal afecte as vistas dos
utilizadores.

Estrutura interna da base de dados não deve ser
afectada pelas alterações efectuadas no armazenamento
físico.

DBA deve poder alterar a estrutura conceptual da base
de dados sem afectar os utilizadores.
29
Arquitectura de três níveis ANSI-SPARC
30

Nível Externo
– Visão de um utilizador sobre a base de dados.
– Descreve parte da base de dados que é relevante a
um determinado utilizador.

Nível Conceptual
– União das Vistas da base de dados.
– Descreve que dados são armazenados na base de
dados e quais as relações entre esses dados.
31
 Nível
Interno
– Representação física da base de dados no
computador.
– Descreve como os dados são armazenados na
base de dados.
32
Diferenças entre os três níveis da
arquitectura ANSI-SPARC
33
Independência de Dados
 Independência
Lógica de Dados
– Refere-se à imunidade dos esquemas
externos a alterações feitas no esquema
conceptual.
– Alterações no esquema conceptual (ex.
adição/remoção de entidades).
– Não deve implicar alterações no esquema
externo ou rescrita de aplicações.
34
Independência de Dados
 Independência
Física de Dados
– Refere-se à imunidade dos esquemas
conceptuais a alterações feitas no esquema
interno.
– Alterações no esquema interno (ex. usar uma
organização de ficheiros diferente,
estruturas/dispositivos de armazenamento).
– Não deve implicar alterações no esquema
conceptual ou nos esquemas externos.
35
Independência de Dados e a Arquitectura
de três níveis ANSI-SPARC
36
Linguagens de Base de Dados
 Linguagem
de Definição de Dados (LDD)
– Permite ao DBA ou ao utilizador descrever
e nomear entidades, atributos e
relacionamentos necessários à aplicação.
– mais as respectivas restrições de integridade
e segurança.
37



Linguagem de Manipulação de Dados (LMD)
– Fornece operações de manipulação de dados sobre a
base de dados.
LMD Procedimental
– permite ao utilizador dizer exactamente como
manipular os dados.
LMD Não-Procedimental
– Permite ao utilizador dizer que dados precisa em
vez de como estes devem ser manipulados.
38
 Linguagens
–
–
–
–
–
de Quarta Geração (4GL)
Query Languages
Forms Generators
Report Generators
Graphics Generators
Application Generators.
39
Modelo de Dados
Colecção integrada de conceitos para descrever
dados, relacionamentos entre dados, e
restrições na informação de uma organização.
 Modelo
de Dados compreende:
– parte estrutural;
– parte de manipulação;
– possivelmente um conjunto de regras de
integridade.
40
Modelo de Dados
 Objectivo
– Representar informação de forma
perceptível.
 Categorias
de Modelos de Dados:
– Baseado em Objectos
– Baseado em Registos
– Físicos.
41
Modelo de Dados
 Modelo
–
–
–
–
de Dados Baseado em Objectos
Entidades-Relacionamentos
Semânticos
Funcionais
Orientado a Objectos.
 Modelo
de Dados Baseado em Registos
– Modelo de Dados Relacional
– Modelo de Dados de Rede
– Modelo de Dados Hierárquico.
 Modelo
de Dados Físico
42
Funções de um SGBD
 Armazenamento,
Dados.
 Dicionário
Pesquisa e Actualização de
de Dados.
 Suporte
a Transações.
 Serviços
de Controlo de Concorrência.
 Serviços
de Recuperação.
43
Funções de um SGBD
 Serviços
de Autenticação.
 Suporte
a Comunicação de Dados.
 Serviços
de Integridade.
 Serviços
Dados.
que promovam a Independência de
 Utilitários.
44
45
Componentes do Database Manager (DM)
46
Arquitecturas
utilizadores
de
SGBD
para
multi-
 Teleprocessamento
 Servidor de
Ficheiros
 Cliente-Servidor
47
Teleprocessamento
 Arquitectura
 Um
Tradicional.
mainframe com vários terminais ligados.
 Tendência
para “downsizing”.
48
Topologia de Teleprocessamento
49
Servidor de Ficheiros
 Servidor de
Ficheiros está interligado por rede
a várias estações de trabalho.
 A base
de dados está no Servidor de Ficheiros.
 SGBD
e as aplicações correm em cada estação
de trabalho.
 Desvantagens:
– Trafego de rede significativo.
– Um SGBD em cada estação de trabalho.
– Controlo de Concorrência, Integridade e
Recuperação de elevada complexidade.
50
Arquitectura Servidor de Ficheiros
51
Cliente-Servidor
 Servidor tem
a Base de Dados e o SGBD.
 Cliente
gere o interface como utilizador e corre
as aplicações.
 Vantagens:
–
–
–
–
–
maior acesso às base de dados existentes;
maior performance;
possibilidade de redução de custos com hardware;
redução de custos com a comunicação;
mais consistência.
52
Arquitectura Cliente-Servidor
53
Topologias Cliente-Servidor Alternativas
54
Dicionário de Dados
 Repositório
de informação (metadados) que
descreve os dados na base de dados.
 Armazena normalmente:
–
–
–
–
nomes de utilizadores autorizados;
nomes de itens de dados na base de dados;
restrições sobre cada item de dados;
itens de dados acessíveis por utilizador e por tipo de acesso.
 Utilizado
por módulos como Controlo de
Autenticação e Verificação de Integridade.
55
Capitulo 3
O Modelo Relacional
Transparências
 Terminologia
do modelo relacional.
 Como são usadas as tabelas para representar
informação.
 Propriedades das relações nas bases de dados.
 Como identificar chaves candidata, primária e
estrangeira.
 Significado de integridade de entidades e de
integridade referencial.
 Objectivo e Vantagens das Vistas.
57
Terminologia do Modelo Relacional

Uma relação é uma tabela com colunas e linhas.
– Apenas se aplica à estrutura lógica da base de
dados, não à física.

Atributo é o nome de uma coluna de uma relação.

Domínio é um conjunto de valores permitidos para um
ou mais atributos.
58
Terminologia do Modelo Relacional

Tuplo é uma linha de uma relação.

Grau é o número de atributos de uma relação.

Cardinalidade é o número de tuplos de uma relação.

Base de Dados Relacional é um conjunto de relações
normalizadas com nomes de relações distintos.
59
Instancias das Relações Branch e Staff (parciais)
60
Exemplos do Domínio de Atributos
61
Terminologia Alternativa para o Modelo
Relacional
62
Relações de Bases de Dados
 Esquema
Relacional
– Relação definida por um conjunto de pares
de atributos e domínios.
 Esquema
de Base de Dados Relacional
– Conjunto de Esquemas Relacionais, cada um
com um nome distinto.
63
Propriedades das Relações
O
nome da relação é distinto do nome de todas
as outras relações do mesmo esquema
relacional.
 Cada
célula de uma relação contem
exactamente um único valor.
 Cada
atributo tem um nome distinto.
 Valores
de um atributo são todos do mesmo
domínio.
64
Propriedades das Relações
 Cada
tuplo é distinto; não existem tuplos
duplicados.
 A ordem
dos atributos não tem qualquer
significado.
 A ordem
dos tuplos não tem qualquer significado,
teoricamente.
65
Chaves Relacionais
 Super
Chave
– Um atributo, ou um conjunto de atributos, que
identifica univocamente um tuplo numa relação.
 Chave
Candidata
– Super Chave (K) tal que nenhum subconjunto de
atributos seja Super Chave na relação.
– Em cada tuplo de R, valores de K identificam
univocamente esse tuplo (unicidade).
– Nenhum subconjunto de atributos de K tem a
propriedade unicidade (irreductibilidade).
66
Chaves Relacionais
 Chave
Primaria
– Chave Candidata seleccionada para identificar
univocamente tuplos na relação.
 Chaves Alternativas
– Chaves Candidatas que não foram escolhidas para
Chave Primária.
 Chave
Estrangeira
– Atributo, ou conjunto de atributos, numa relação
que são chaves candidatas de alguma ( pode ser a
mesma ) relação.
67
Integridade Relacional
 Null
– Representa o valor de um atributo que ainda é
desconhecido ou não aplicável no tuplo.
– Lida com informação incompleta ou
excepcional.
– Representa a ausência de valor e não é o mesmo
que zero ou espaços, que são valores.
68

Integridade de Entidade
– Numa relação, nenhum atributo pertencente à
chave primária pode ser nulo.

Integridade Referencial
– Se uma Chave Estrangeira existe numa relação, ou
o valor da Chave Estrangeira é idêntico a um valor
de uma Chave Candidata de um tuplo da sua
relação ou o valor da Chave Estrangeira é null.
69
 Restrições
Empresariais
– Regras adicionais especificadas por
utilizadores ou pelos administradores da
base de dados.
70
Vistas
 Relação
Base
– Uma relação a que corresponde uma
entidade no esquema conceptual em que os
tuplos estão armazenados na base de dados.
 Vista
– Resultado dinâmico de uma ou mais
operações relacionais sobre relações bases
para produzir outra relação..
71
Vistas
 Uma
relação virtual que pode não existir na base
de dados mas que é produzida na altura do
pedido..
O
conteúdo de uma vista é definido como uma
query sobre uma ou mais relações base.
 As
vistas são dinâmicas, isto é, alterações
efectuadas nas relações base que afectam os
atributos da vista reflectem-se imediatamente na
vista.
72
Objectivo das Vistas
 Providencia
um mecanismo de segurança
poderoso e flexível escondendo parte da base
de dados de certos utilizadores.
 Permite
aos utilizadores acesso a informação
customizada, de forma a que a mesma
informação possa ser vista por diferentes
utilizadores de diferentes formas, ao mesmo
tempo.
 Podem
simplificar operações complexas sobre
relações base.
73
Actualizando Vistas
 Todos
as actualizações efectuadas nas relações
base devem reflectir-se imediatamente em
todas as vistas que usam essa relação base.
 Se
uma vista é alterada, a relação base que a
suporta deve reflectir tal alteração.
74
Actualizando Vistas
 Existem
restrições ao tipo de modificações que
podem ser feitas através das vistas:
- Actualizações são permitidas se a query envolve
apenas uma relação base e contém uma chave
candidata da relação base.
- Actualizações não são permitidas se a vista é
constituída por várias relações base..
- Actualizações não são permitidas se a vista contém
agregação ou operações de agrupamento.
75
Actualizando Vistas
 As
vistas são divididas nas seguintes classes:
– Teoricamente não actualizável;
– Teoricamente actualizável;
– Parcialmente actualizável.
76
Capitulo 4
Álgebra Relacional
Transparências
 Significado
 Como
do termo plenitude relacional.
criar queries em álgebra relacional.
 Categorias
de LMD relacionais.
78
Introdução
 Álgebra
relacional e calculo relacional são
linguagens formais associadas ao modelo
relacional.
 Informalmente, álgebra relacional é uma (altonível) linguagem procedimental e calculo
relacional é uma linguagem nãoprocedimental.
 No entanto, formalmente ambas são
equivalentes.
 Uma linguagem que produz uma relação que
pode derivar do uso de cálculo relacional é
relacionalmente completa.
79
Álgebra Relacional
 Operações
de Álgebra Relacional são sobre
uma ou mais relações e definem uma nova
relação sem alterar as relações originais.
 Os
operandos e o resultado são relações, sendo
assim, o resultado de uma operação pode ser o
operando de outra operação.
 Permite
que as expressões sejam aninhadas,
como na aritmética. Esta propriedade é
chamada encerramento.
80
Álgebra Relacional
 Cinco
operações básicas na Álgebra
Relacional: Selecção, Projecção, Produto
Cartesiano, União, e Diferença.
 Estas
efectuam quase todas as operações de
busca de informação necessárias.
 Também
existem as operações Junção,
Intersecção, e Divisão, que podem ser expressas
usando as 5 operações básicas.
81
Operações de Álgebra Relacional
82
Operações de Álgebra Relacional
83
Selecção (ou Restrição)
 predicado
(R)
– Efectuado sobre uma só relação R e define uma
relação que apenas contém os tuplos (linhas) de
R que satisfazem a condição especificada
(predicado).
84
Exemplo - Selecção (ou Restrição)
 Todo
o staff com salário superior a £10,000.
salary > 10000 (Staff)
85
Projecção
 col1, . . . , coln(R)
– Efectuado sobre uma só relação R e define uma
relação que contém um subconjunto vertical de
R, extraindo os valores dos atributos
especificados e eliminando os duplicados.
86
Exemplo - Projecção
 Produza
uma lista de salários de todo o staff,
mostrando apenas staffNo, fName, lName, e
salary.
staffNo, fName, lName, salary(Staff)
87
União
S
– A União de duas relações R e S define uma
relação que contém todos os tuplos de R, ou S,
ou R e S, sendo os tuplos duplicados eliminados.
– R e S devem ser compatíveis na União.
R
 Se
R e S têm I e J tuplos, respectivamente, a união
obtém-se através da concatenação de ambos numa
só relação com, no máximo, (I + J) tuplos.
88
Exemplo - União
 Mostre
todas as cidades onde existe ou um
escritório ou uma propriedade para arrendar.
city(Branch)  city(PropertyForRent)
89
Diferença
R
–S
– Define uma relação que consiste dos tuplos que
estão na relação R, mas não em S.
– R e S têm de ser compatíveis na União.
90
Exemplo - Diferença
 Mostre
todas as cidades onde existe um escritório
mas não existe nenhuma propriedade para
arrendar.
city(Branch) – city(PropertyForRent)
91
Intersecção
S
– Define uma relação que consiste do conjunto
de tuplos que estão quer em R quer em S.
– R e S têm de ser compatíveis na União.
R
 Pode-se
obter através das seguintes operações
básicas:
R  S = R – (R – S)
92
Exemplo - Intersecção
 Mostre
todas as cidades onde existe um escritório
e pelo menos uma propriedade para arrendar.
city(Branch)  city(PropertyForRent)
93
Produto Cartesiano
R
XS
– Define uma relação que é a concatenação de
cada tuplo da relação R com todos os tuplos da
relação S.
94
Exemplo – Produto Cartesiano

Mostre os nomes e os comentários de todos os clientes que
visitaram uma propriedade para arrendar.
(clientNo, fName, lName(Client)) X (clientNo, propertyNo, comment
(Viewing))
95
Exemplo – Produto Cartesiano e Selecção

Use a operação Selecção para extrair os tuplos onde
Client.clientNo = Viewing.clientNo.
Client.clientNo = Viewing.clientNo((clientNo, fName, lName(Client)) 
(clientNo, propertyNo, comment(Viewing)))
Produto Cartesiano e Selecção podem ser reduzidos a uma só
operação denominada Junção.

96
Operações de Junção

Junção é uma derivação do Produto Cartesiano.

Equivalente a uma Selecção, usando um predicado de
junção como selecção, sobre o Produto Cartesiano de duas
relações.

Uma das operações mais difíceis de implementar
eficientemente num SGBD R e uma das razões porque os
SGBD R têm problemas intrínsecos de performance.
97
Operações de Junção
 Várias
–
–
–
–
–
operações de Junção
Theta join
Equijoin (caso particular de Theta join)
Natural join ( Junção Natural )
Outer join
Semijoin
98
Theta join (-join)
R
FS
– Define uma relação que contém os tuplos
que satisfazem o predicado F do Produto
Cartesiano de R e S.
– O predicado F é da forma R.ai  S.bi onde 
pode ser um dos operadores de comparação
(<, , >, , =, ).
99
Theta join (-join)
 Pode-se
rescrever Theta join usando operações de
Selecção e de Produto Cartesiano.
R
FS
= F(R  S)
O
grau do resultado desta operação é a soma dos
graus das relações R e S. Se o predicado F contém
apenas a igualdade (=), o termo Equijoin é aplicado.
100
Exemplo - Equijoin
 Mostrar os
nomes e comentários de todos os clientes
que visitaram uma propriedade para arrendar.
(clientNo, fName, lName(Client)) Client.clientNo =
Viewing.clientNo (clientNo, propertyNo, comment(Viewing))
101
Natural join ( Junção Natural )
R
S
– Um Equijoin de duas relações R and S sobre
todos os atributos comuns x. Uma ocorrência de
cada atributo comum é eliminado do resultado.
102
Exemplo - Natural join ( Junção Natural )
 Mostrar todos
os nomes e comentários de todos os
clientes que visitaram uma propriedade para arrendar.
(clientNo, fName, lName(Client))
(clientNo, propertyNo, comment(Viewing))
103
Outer join
 Para
mostrar linhas que não têm valores iguais
na coluna de junção usa-se o Outer join.
R
S
– (Left) outer join é uma junção em que os
tuplos de R que não têm valores em colunas
comuns de S são também incluídos na
relação final.
104
Exemplo - Left Outer join
 Produza
um relatório de estado sobre as visitas
a propriedades.
propertyNo, street, city(PropertyForRent)
Viewing
105
Semijoin
R
FS
– Define uma relação que contém os tuplos de R que
participam na junção de R com S.
 Pode-se
rescrever Semijoin usando Projecção e
Junção:
R
FS
= A(R
F
S)
106
Example - Semijoin
 Mostrar o
detalhe completo de todo o staff que
trabalha no escritório de Glasgow.
Staff Staff.branchNo = Branch.branchNo and Branch.city = ‘Glasgow’
Branch
107
Divisão

RS
– Define uma relação sobre os atributos C que consistem
num conjunto de tuplos de R que combinam com todos
os tuplos de S.

Usando operações básicas:
T1  C(R)
T2  C((S X T1) – R)
T  T1 – T2
108
Exemplo - Divisão
 Identifique
todos os clientes que visitaram todas as
propriedades com três quartos.
(clientNo, propertyNo(Viewing))  (propertyNo(rooms = 3
(PropertyForRent)))
109
Capitulo 5
SQL: Manipulação de Dados
Transparências
 Objectivo
e importância de SQL.
 Como obter dados de uma base de dados usando
o comando SELECT e:
– Usando condições WHERE .
– Ordenar os resultados das queries usando
ORDER BY.
– Usar funções de agregação.
– Agrupar dados usando GROUP BY e HAVING.
– Usar subqueries.
111
– Juntar tabelas.
– Efectuar operações sobre conjuntos (UNION,
INTERSECT, EXCEPT).
 Como
actualizar a base de dados usando INSERT,
UPDATE, e DELETE.
112
Objectivos de SQL



Uma linguagem de base de dados deve permitir ao
utilizador :
– criar a base de dados e a estrutura das relações;
– efectuar inserções, modificações, remoções de dados
nas relações;
– efectuar queries simples e complexas.
Deve efectuar estas tarefas com o mínimo de esforço para
o utilizador e a sintaxe/estrutura do comando deve ser de
fácil aprendizagem.
Deve ser portável.
113
Objectivos de SQL

SQL é uma linguagem orientada á transformação com 2
componentes principais:
– A LDD para definir a estrutura da base de dados.
– A LMD para obter e actualizar os dados.

Até ao SQL3, SQL não continha controlo de fluxo de
comandos. Estes tinham de ser implementados usando
uma linguagem de programação, ou interactivamente
através de decisões do utilizador.
114
Objectivos de SQL
 SQL é
relativamente simples de aprender:
– é não procedimental – apenas se especifica o
quê que queremos ao invés de como queremos;
– essencialmente de formato livre.
115
Objectivos de SQL
 Consiste
de palavras standard Inglesas:
1) CREATE TABLE Staff(staffNo VARCHAR(5),
lName VARCHAR(15),
salary DECIMAL(7,2));
2) INSERT INTO Staff VALUES (‘SG16’, ‘Brown’,
8300);
3) SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;
116
Objectivos de SQL
 Pode
ser usado por um variado conjunto de
utilizadores
incluindo
DBAs,
gestores,
programadores, e outros tipos de utilizadores
finais.
 Existe
agora um standard ISO para SQL,
tornando assim quer formalmente e de facto
linguagem standard para bases de dados
relacionais.
117
Comandos SQL
 Um
comando SQL consiste de palavras reservadas
e palavras definidas pelo utilizador.
–
–
As palavras reservadas são a parte fixa do SQL e
têm de ser escritas exactamente como exigido.
As palavras definidas pelo utilizador representam
nomes de vários objectos da base de dados como,
relações, atributos, vistas.
118
Comandos SQL


Grande parte dos componentes do comando SQL são case
insensitive, exceptuando os valores dos dados armazenados.
Mais perceptíveis com indentação e separação de linhas:
– Cada clausula deve começar numa nova linha.
– O inicio de uma clausula deve estar alinhada com
outras .
– Se uma clausula tem varias partes, cada uma deve
aparecer em linhas separadas e indentadas da clausula
inicial.
119
Comandos SQL
 Usar
a forma estendida da notação BNF:
-Letras maiúsculas representam palavras reservadas.
- Letras minúsculas representam palavras definidas
pelo utilizador.
- | indica uma escolha entre alternativas.
- Chavetas indicam um elemento obrigatório.
- Parêntesis Rectos indicam um elemento opcional.
- … indica repetição opcional (0 ou mais).
120
Comando SELECT
SELECT [DISTINCT | ALL]
{* | [columnExpression [AS newName]] [,...] }
FROM
TableName [alias] [, ...]
[WHERE
condition]
[GROUP BY columnList] [HAVING condition]
[ORDER BY columnList]
121
Comando SELECT
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Especifica a/as tabela(s) a ser
utilizadas.
Filtra linhas.
Forma grupos de linhas com o
mesmo valor na coluna.
Filtra grupos sujeitos a alguma
condição.
Especifica quais as colunas que
devem aparecer no resultado.
Especifica a ordem do resultado.
122
Comando SELECT
 Ordem
das componentes do comando não pode
ser alterado.
 Só
o SELECT e FROM é que são obrigatórios.
123
Exemplo 5.1 Todas as colunas, todas as linhas
Listar toda a informação de todo o staff.
SELECT staffNo, fName, lName, address,
position, sex, DOB, salary, branchNo
FROM Staff;

Pode-se usar * como abreviatura de ‘todas as colunas’:
SELECT *
FROM Staff;
124
Exemplo 5.1 Todas as colunas, todas as linhas
125
Exemplo 5.2 Especifica colunas, todas as linhas
Produzir uma lista de salários de todo o staff,
mostrando apenas o seu código, o primeiro e
ultimo nome, e o salário.
SELECT staffNo, fName, lName, salary
FROM Staff;
126
Exemplo 5.2 Especifica colunas, todas as linhas
127
Exemplo 5.3 Uso do DISTINCT
Listar os códigos das propriedades que já foram
visitadas.
SELECT propertyNo
FROM Viewing;
128
Exemplo 5.3 Uso do DISTINCT
 Usar
DISTINCT para eliminar duplicados:
SELECT DISTINCT propertyNo
FROM Viewing;
129
Exemplo 5.4 Campos Calculados
Produzir uma lista dos salários mensais de todo o
staff, mostrando o código do trabalhador, o seu
primeiro e último nome, e o valor do salário.
SELECT staffNo, fName, lName, salary/12
FROM Staff;
130
Exemplo 5.4 Campos Calculados
 Para
dar um nome a uma coluna usar AS :
SELECT staffNo, fName, lName, salary/12
AS monthlySalary
FROM Staff;
131
Exemplo 5.5
Comparação
Condição de procura por
Listar todo o staff com o salário superior a 10,000.
SELECT staffNo, fName, lName, position, salary
FROM Staff
132
Exemplo 5.6 Condição de procura por
Comparação Composta
Listar as moradas de todos os escritórios em
London e Glasgow.
SELECT *
FROM Branch
WHERE city = ‘London’ OR city = ‘Glasgow’;
133
Exemplo 5.7
intervalo
Condição de procura num
Listar todo o staff com o salário entre 20,000 e
30,000.
FROM Staff
WHERE salary BETWEEN 20000 AND 30000;
 BETWEEN
inclui os valores limites.
134
Exemplo 5.7
intervalo
135
Exemplo 5.7
intervalo


Existe também a condição negada NOT BETWEEN.
BETWEEN não adiciona muito ao poder expressivo do
SQL. Pode-se também escrever:
FROM Staff
WHERE salary>=20000 AND salary <= 30000;

No entanto, é útil para intervalos de valores.
136
Exemplo 5.8 Pertença de um conjunto
Listar todos os managers e supervisors.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE position IN (‘Manager’, ‘Supervisor’);
137
Exemplo 5.8 Pertença de um conjunto
Existe
a versão negada (NOT IN).
 IN não adiciona muito ao poder expressivo do SQL.
 Podia-se ter escrito assim:
FROM Staff
WHERE position=‘Manager’ OR
position=‘Supervisor’;
IN é mais eficiente quando o conjunto contém muitos
valores.

138
Exemplo 5.9 Procura de Padrões
Procure todos os proprietários com ‘Glasgow’ na
sua morada.
SELECT clientNo, fName, lName, address, telNo
FROM PrivateOwner
WHERE address LIKE ‘%Glasgow%’;
139
Exemplo 5.9 Procura de Padrões
 SQL
tem dois símbolos especiais para se procurar
padrões:
– %: sequência de zero ou mais caracteres;
– _ (underscore): um qualquer caracter.
 LIKE
‘%Glasgow%’ significa uma sequência de
qualquer tamanho de caracteres contendo
‘Glasgow’.
140
Exemplo 5.10 Condição de Procura de Nulos
Listar o detalhe de todas as visitas à propriedade
PG4 onde não existam comentários.
 Existem
2 visitas à propriedade PG4, uma com e
outra sem qualquer comentário.
 É necessário testar nulos usando a palavra especial
IS NULL:
SELECT clientNo, viewDate
FROM Viewing
WHERE propertyNo = ‘PG4’ AND
comment IS NULL;
141
Exemplo 5.10 Condição de Procura de Nulos
 Versão
Negada (IS NOT NULL) testa valores não
nulos.
142
Exemplo 5.11 Ordenação por uma coluna
Listar os salários de todo o staff, ordenando os
salários por ordem decrescente.
SELECT staffNo, fName, lName, salary
FROM Staff
ORDER BY salary DESC;
143
Exemplo 5.11 Ordenação por uma coluna
144
Exemplo 5.12 Ordenação por várias colunas
Produza uma lista abreviada das propriedades por
ordem do tipo da propriedade.
SELECT propertyNo, type, rooms, rent
FROM PropertyForRent
ORDER BY type;
145
146


Quatro flats nesta lista – como não existe mais nenhum
requisito de como ordenar a listagem, o sistema ordena as
linhas na ordem que bem entende.
Para ordenar por rent, especificar o segundo campo:
SELECT propertyNo, type, rooms, rent
ORDER BY type, rent DESC;
147
148
Expressão SELECT - Agregações
 ISO
standard define cinco funções agregadas:
COUNT retorna número de ocorrências numa
determinada coluna.
SUM retorna a soma dos valores de uma coluna.
AVG retorna a média dos valores de uma coluna.
MIN retorna o valor mais pequeno de uma coluna.
MAX retorna o maior valor de uma coluna.
149
 Cada
uma opera sobre uma só coluna e retorna um
só valor.
 COUNT,
MIN, e MAX aplica-se a campos
numéricos e não-numéricos, mas SUM e AVG só se
aplicam a campos numéricos.
À
parte de COUNT(*), cada função elimina
primeiro os nulos e opera apenas nos valores
restantes.
150
 COUNT(*)
conta todas as linhas da tabela,
independentemente de haver nulos ou valores
duplicados.
 Pode-se usar DISTINCT antes do nome da coluna
para eliminar duplicados.
 DISTINCT não afecta as funções MIN/MAX, mas
pode afectar as funções SUM/AVG.
151

Funções de Agregação podem apenas ser usadas na lista de
colunas da expressão SELECT e na clausula HAVING.

Se a lista de colunas da expressão SELECT inclui uma
função de agregação e não existe a clausula GROUP BY, a
lista de colunas da expressão SELECT não pode conter
uma referência a uma coluna fora da função de agregação.
Por exemplo, o seguinte é ilegal:
SELECT staffNo, COUNT(salary)
FROM Staff;
152
Exemplo 5.13 Uso de COUNT(*)
Quantas propriedades têm o aluguer superior a
£350 por mês?
SELECT COUNT(*) AS count
WHERE rent > 350;
153
Exemplo 5.14 Uso de COUNT(DISTINCT)
Quantas propriedades diferentes foram visitadas
no mês de Maio de 2001?
SELECT COUNT(DISTINCT propertyNo) AS count
FROM Viewing
WHERE viewDate BETWEEN ‘1-May-01’
AND ‘31-May-01’;
154
Exemplo 5.15 Uso de COUNT e SUM
Procure quantos Managers existem e qual a soma dos
seus salários.
SELECT COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
WHERE position = ‘Manager’;
155
Exemplo 5.16 Uso de MIN, MAX, AVG
Procure o mínimo, máximo e média dos salários.
SELECT MIN(salary) AS min,
MAX(salary) AS max,
AVG(salary) AS avg
FROM Staff;
156
Expressão SELECT - Agrupar
 Use
a clausula GROUP BY para obter sub-totais.
 SELECT e GROUP BY em conjunto: cada coluna
na lista da expressão SELECT tem que ter um só
valor por grupo, e a clausula SELECT apenas pode
conter:
- nomes de colunas
- funções de agregação
- constantes
- expressões contendo combinações das anteriores.
157
Expressão SELECT - Agrupar

Todos nomes das colunas existentes na expressão SELECT
tem que estar também na clausula GROUP BY com
excepção dos nomes que são apenas usados nas funções de
agregação.

Se WHERE é usado em conjunção com GROUP BY,
WHERE é aplicado primeiro, depois os grupos são
formados das linhas resultantes que satisfaçam o
predicado.

ISO considera que dois nulos são iguais para a clausula
GROUP BY.
158
Exemplo 5.17 Uso do GROUP BY
Quantos trabalhadores existem por escritório e o
total dos seus salários.
SELECT
branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
ORDER BY branchNo;
159
Exemplo 5.17 Uso do GROUP BY
160
Agrupamentos Restrictos – clausula HAVING

A clausula HAVING usa-se em conjunto com a clausula
GROUP BY para restringir os grupos que aparecem na
tabela final.

Parecido com o WHERE, mas o WHERE filtra linhas
individuais enquanto que HAVING filtra grupos.

Os nomes das colunas que aparecem na clausula HAVING
têm de aparecer também na clausula GROUP BY ou
estarem contidos numa função de agregação.
161
Exemplo 5.18 Uso do HAVING
Para cada escritório com mais que 1 funcionário,
quantos funcionários existem em cada escritório e
qual a soma dos seus salários.
SELECT branchNo,
COUNT(staffNo) AS count,
SUM(salary) AS sum
FROM Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1
ORDER BY branchNo;
162
Exemplo 5.18 Uso do HAVING
163
Subqueries
 Algumas
expressões SQL podem conter uma
expressão SELECT embebida.
 Uma
subselecção pode ser usada nas clausulas
WHERE e HAVING de um SELECT, e é
denominada de subquery ou query aninhada.
 Subselecções
podem também aparecer
expressões INSERT, UPDATE, e DELETE.
nas
164
Exemplo 5.19 Subquery com Igualdade
Listar o staff que trabalha no escritório de ‘163
Main St’.
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
165
 SELECT
interno determina o número do
escritório de ‘163 Main St’ (‘B003’).
 SELECT externo retorna o detalhe de todo o staff
que trabalha nesse escritório.
 SELECT externo transforma-se em:
FROM Staff
WHERE branchNo = ‘B003’;
166
167
Exemplo 5.20 Subquery com Agregação
Listar todos os funcionários cujo salário seja superior á
média dos salários e qual a diferença.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) As SalDiff
FROM Staff
WHERE salary >
(SELECT AVG(salary)
FROM Staff);
168


Não podemos escrever ‘WHERE salary > AVG(salary)’
Usamos uma subquery para determinar a média dos
salários (17000), e depois usamos o SELECT externo para
sabermos quais os funcionários que ganham mais que a
média:
SELECT staffNo, fName, lName, position,
salary – 17000 As salDiff
FROM Staff
169
170
Regras das Subqueries
 A clausula
ORDER BY não pode ser usada dentro
de uma subquery (no entanto pode ser usada no
SELECT externo).
A
lista da subquery SELECT consiste de apenas
uma coluna ou expressão, exceptuando as
subqueries que usem a clausula EXISTS.
 Por
defeito, os nomes das colunas referem-se à
tabela da clausula FROM da subquery. Pode-se
referir à tabela do FROM através de um alias.
171
Regras das Subqueries
 Quando
a subquery é um operando numa
comparação, a subquery tem de aparecer no lado
direito.
 Uma
subquery não pode ser utilizada como um
operando numa expressão.
172
Exemplo 5.21 Subquery aninhada: uso do IN
Listar as propriedades geridas pelo staff de ‘163 Main St’.
SELECT propertyNo, street, city, postcode, type, rooms, rent
WHERE staffNo IN
(SELECT staffNo
FROM Staff
WHERE branchNo =
(SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
173
Exemplo 5.21 Subquery aninhada: uso do IN
174
ANY e ALL
 ANY e
ALL podem ser usados em subqueries que
produzam uma só coluna de números.
 Com
ALL, a condição apenas será verdade se
esta for satisfeita por todos os valores produzidos
pela subquery.
 Com
ANY, a condição é verdadeira se esta for
satisfeita por algum dos valores produzidos pela
subquery.
 Se
a subquery for vazia,
verdadeiro, ANY retorna falso.
 SOME
ALL
pode ser usado em vez de ANY.
retorna
175
Exemplo 5.22 Uso de ANY/SOME
Listar os funcionários cujo salário seja superior ao
salário de pelo menos um funcionário do escritório
B003.
FROM Staff
WHERE salary > SOME
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
176
Exemplo 5.22 Uso de ANY/SOME
A
query interna produz o conjunto {12000, 18000,
24000} e a query externa selecciona os
funcionários cujo salário é superior a qualquer um
dos valores do conjunto.
177
Exemplo 5.23 Uso do ALL
Liste os funcionários cujo salário seja superior ao
salário de todos os funcionários do escritório B003.
FROM Staff
WHERE salary > ALL
(SELECT salary
FROM Staff
WHERE branchNo = ‘B003’);
178
Exemplo 5.23 Uso do ALL
179
Queries Multi-Tabela
 Podemos
usar subquerys desde que as colunas
resultado sejam da mesma tabela.
 Se
as colunas resultado pertencem a mais que uma
tabela temos de usar uma junção.
 Para
fazermos uma junção temos que mencionar o
nome das tabelas na clausula FROM .
 Usar
a virgula como separador e usar a clausula
WHERE para especificar as colunas da operação
de junção.
180
Queries Multi-Tabela
È
possível usar um alias em vez do nome da tabela
na clausula FROM.
O
alias está separado do nome da tabela por um
espaço.
O
alias pode ser usado para melhor identificar a
proveniência de uma coluna quando existe
ambiguidade.
181
Exemplo 5.24 Junção Simples
Listar os nomes de todos os clientes que visitaram
uma propriedade assim como o respectivo
comentário.
SELECT c.clientNo, fName, lName,
propertyNo, comment
FROM Client c, Viewing v
WHERE c.clientNo = v.clientNo;
182
Exemplo 5.24 Junção Simples
 Apenas
são incluídas no resultado as linhas de
ambas as tabelas que têm valores idênticos na
coluna clientNo (c.clientNo = v.clientNo).
 Equivalente
a equi-join na algebra relacional.
183
Construções de JOIN Alternativas
 SQL
proporciona
especificar joins:
várias
alternativas
para
FROM Client c JOIN Viewing v ON c.clientNo = v.clientNo
FROM Client JOIN Viewing USING clientNo
FROM Client NATURAL JOIN Viewing
 Em
cada caso, FROM substitui o FROM e
WHERE original. No entanto, a primeira
alternativa produz uma tabela com duas colunas
idênticas chamadas clientNo.
184
Exemplo 5.25 Ordenando uma Junção
Para cada escritório, liste os números e nomes de
todos os funcionários que gerem propriedades, e
que propriedades.
SELECT s.branchNo, s.staffNo, fName, lName,
propertyNo
FROM Staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo
ORDER BY s.branchNo, s.staffNo, propertyNo;
185
Exemplo 5.25 Ordenando uma Junção
186
Exemplo 5.26 Junção de Três Tabelas
Para cada escritório, liste os funcionários que
gerem propriedades, incluindo a cidade em que
se situa o escritório assim como o número da
propriedade.
SELECT b.branchNo, b.city, s.staffNo, fName, lName,
propertyNo
FROM Branch b, Staff s, PropertyForRent p
WHERE b.branchNo = s.branchNo AND
s.staffNo = p.staffNo
ORDER BY b.branchNo, s.staffNo, propertyNo;
187
Exemplo 5.26 Junção de Três Tabelas

Formulação alternativa para o FROM e WHERE:
FROM (Branch b JOIN Staff s USING branchNo) AS
bs JOIN PropertyForRent p USING staffNo
188
Exemplo 5.27 Agrupar Múltiplas Colunas
Quantas propriedades são geridas por cada
funcionário.
SELECT s.branchNo, s.staffNo, COUNT(*) AS count
GROUP BY s.branchNo, s.staffNo
ORDER BY s.branchNo, s.staffNo;
189
Exemplo 5.27 Agrupar Múltiplas Colunas
190
Computação de uma Junção
Procedimentos para geração de resultados de uma
operação de junção:
1. Faz o produto cartesiano das tabelas que estão na
clausula FROM.
2. Se existir a clausula WHERE, aplica a condição de
procura a cada linha da tabela do produto
cartesiano, retendo as linhas que satisfazem a
condição.
3. Para as linhas restantes, determina o valor de
cada item na lista da clausula SELECT para
191
termos uma só linha na tabela resultante.
Computação de uma Junção
4. Se a clausula DISTINCT foi especificada, elimina
as linhas duplicadas da tabela resultante.
5. Se existe uma clasula ORDER BY, ordena a
tabela resultante como especificado.
 SQL
providência um formato especial
SELECT para o produto cartesiano:
do
SELECT [DISTINCT | ALL] {* | columnList}
FROM Table1 CROSS JOIN Table2
192
Outer Joins
 Se
uma linha de uma tabela de junção não tem
correspondência, essa linha é omitida da tabela
resultante.
 As operações de Outer join mantêm as linhas que
não satisfazem a condição de junção.
 Considere as seguintes tabelas:
193
Outer Joins
 A (inner
join) junção destas duas tabelas:
SELECT b.*, p.*
FROM Branch1 b, PropertyForRent1 p
WHERE b.bCity = p.pCity;
194
Outer Joins
A
tabela resultante tem duas linhas onde as
cidades são as mesmas.
 Não existem linhas correspondentes a escritórios
em Bristol e Aberdeen.
 Para
incluir as linhas que não têm
correspondência na tabela resultante, use um
Outer join.
195
Exemplo 5.28 Left Outer Join
Listar os escritórios e propriedades que estão
localizados na mesma cidade assim como
escritórios que não tenham correspondência.
SELECT b.*, p.*
FROM Branch1 b LEFT JOIN
PropertyForRent1 p ON b.bCity = p.pCity;
196
Exemplo 5.28 Left Outer Join
 Inclui
as linhas da primeira (left) tabela que não
têm correspondência com as linhas da segunda
(right) tabela.
 Colunas da segunda tabela são preenchidas com
NULLs.
197
Exemplo 5.29 Right Outer Join
Liste escritórios e propriedades da mesma cidade
assim como propriedades que não tenham
correspondência.
SELECT b.*, p.*
FROM Branch1 b RIGHT JOIN
198
Exemplo 5.29 Right Outer Join
 Right
Outer join inclui as linhas da segunda
(right) tabela que não têm correspondência com as
linhas da primeira (left) tabela.
 Colunas da primeira tabela são preenchidas com
NULLs.
199
Exemplo 5.30 Full Outer Join
Liste escritórios e propriedades que estão na
mesma cidade assim como escritórios e
propriedades que não têm correspondência.
SELECT b.*, p.*
FROM Branch1 b FULL JOIN
200
Exemplo 5.30 Full Outer Join
 Inclui
linhas que não têm correspondência em
ambas as tabelas.
 Colunas sem correspondência são preenchidas
com NULLs.
201
EXISTS e NOT EXISTS

EXISTS e NOT EXISTS são apenas usadas com
subqueries.

Produzem com resultado apenas verdadeiro/falso.

Verdadeiro se e só se existe pelo menos uma linha na tabela
resultante devolvida pela subquery.

Falso se a subquery devolve uma tabela resultante vazia.

NOT EXISTS é o contrário de EXISTS.
202
EXISTS e NOT EXISTS
 Como
(NOT) EXISTS apenas verifica a existência
ou não de linhas na tabela resultante da subquery,
a subquery pode conter qualquer número de
colunas.
É
comum as subquerys a seguir a (NOT) EXISTS
serem da seguinte forma:
(SELECT * ...)
203
Exemplo 5.31 Query usando EXISTS
Todos os funcionários que trabalham no escritório
de Londres.
FROM Staff s
WHERE EXISTS
(SELECT *
FROM Branch b
WHERE s.branchNo = b.branchNo AND
city = ‘London’);
204
205
 Nota,
a condição de procura s.branchNo =
b.branchNo é necessária para considerar o
escritório correcto para cada funcionário.
 Se omitido, teriamos todos os funcionários porque
a subquery:
SELECT * FROM Branch WHERE city=‘London’
 seria
sempre verdadeira e a query ficaria:
SELECT staffNo, fName, lName, position FROM Staff
WHERE true;
206
 Podíamos
também escrever esta query usando
uma junção:
FROM Staff s, Branch b
WHERE s.branchNo = b.branchNo AND
city = ‘London’;
207
União, Intersecção, e Diferença (Excepto)
 Podemos
usar as operações normais sobre
conjuntos como União, Intersecção, e Diferença
para combinar o resultado de duas ou mais querys
numa só tabela resultante.
 União de duas tabelas, A e B, dá uma tabela
contendo todas as linhas de A ou B ou ambas.
 Intersecção dá uma tabela com as linhas comuns
às tabelas A e B.
 Diferença dá uma tabela com todas as linhas de A
que não estaõ em B..
 As duas tabelas têm de ser compatíveis na união.
208

Formato da clausula de operações sobre conjuntos é:
op [ALL] [CORRESPONDING [BY {column1 [, ...]}]]



Se CORRESPONDING BY for especificado, a operação é
efectuada sobre as colunas indicadas.
Se CORRESPONDING for especificado mas a clausula BY
não, a operação é efectuada sobre as colunas comuns.
Se ALL for especificado, o resultado pode incluir as linhas
duplicadas.
209
210
Exemplo 5.32 Uso de UNION
Listar todas as cidades onde existe ou um escritório
ou uma propriedade.
(SELECT city
FROM Branch
WHERE city IS NOT NULL) UNION
(SELECT city
WHERE city IS NOT NULL);
211
– Ou
(SELECT *
FROM Branch
WHERE city IS NOT NULL)
UNION CORRESPONDING BY city
(SELECT *
WHERE city IS NOT NULL);
212
 Produz
tabelas resultantes de ambas as queries e
une ambas numa só tabela.
213
Exemplo 5.33 Uso de INTERSECT
Listar todas as cidades onde existe um escritório e
também uma propriedade.
(SELECT city FROM Branch)
INTERSECT
(SELECT city FROM PropertyForRent);
214
 Ou
(SELECT * FROM Branch)
INTERSECT CORRESPONDING BY city
(SELECT * FROM PropertyForRent);
215
 Podíamos
rescrever esta query sem o operador
INTERSECT :
SELECT b.city
FROM Branch b PropertyForRent p
WHERE b.city = p.city;
 Ou:
SELECT DISTINCT city FROM Branch b
WHERE EXISTS
(SELECT * FROM PropertyForRent p
WHERE p.city = b.city);
216
Exemplo 5.34 Uso de EXCEPT
Listar todas as cidades onde existe um escritório
mas não existe nenhuma propriedade.
(SELECT city FROM Branch)
EXCEPT

Ou
(SELECT * FROM Branch)
EXCEPT CORRESPONDING BY city
(SELECT * FROM PropertyForRent);
217
Exemplo 5.34 Uso de EXCEPT
 Podíamos
rescrever essa query sem o EXCEPT:
SELECT DISTINCT city FROM Branch
WHERE city NOT IN
 Ou
SELECT DISTINCT city FROM Branch b
WHERE NOT EXISTS
(SELECT * FROM PropertyForRent p
WHERE p.city = b.city);
218
INSERT
INSERT INTO TableName [ (columnList) ]
VALUES (dataValueList)
é opcional; se omitida, SQL assume a
lista de todas as colunas que estão originalmente
no CREATE TABLE.
 Qualquer coluna omitida tem de ter sido
declarada como NULL na criação da tabela, a não
ser que tenha sido definido um valor DEFAULT.
 columnList
219
INSERT
 dataValueList
tem em comum com columnList o
seguinte:
– número de items em cada lista é o mesmo;
– tem de haver correspondência directa entre a
posição dos items nas duas listas;
– o tipo de dados de cada item na dataValueList
tem que ser compatível com o tipo de dados
da coluna correspondente.
220
Exemplo 5.35 INSERT … VALUES
Inserir uma nova linha na tabela Staff incluindo
valores para todas as colunas.
INSERT INTO Staff
VALUES (‘SG16’, ‘Alan’, ‘Brown’, ‘Assistant’,
‘M’, Date‘1957-05-25’, 8300, ‘B003’);
221
Exemplo 5.36 INSERT usando Defaults
Inserir uma nova linha na tabela
preenchendo todas as colunas obrigatórias.
Staff
INSERT INTO Staff (staffNo, fName, lName,
position, salary, branchNo)
VALUES (‘SG44’, ‘Anne’, ‘Jones’,
‘Assistant’, 8100, ‘B003’);
 Ou
INSERT INTO Staff
VALUES (‘SG44’, ‘Anne’, ‘Jones’, ‘Assistant’, NULL,
NULL, 8100, ‘B003’);
222
INSERT … SELECT
 Uma
outra forma de utilizar o INSERT permite
que várias linhas sejam copiadas de uma ou mais
tabelas para outra:
INSERT INTO TableName [ (columnList) ]
SELECT ...
223
Exemplo 5.37 INSERT … SELECT
Assuma que existe a tabela StaffPropCount que
contém nomes de staff e as propriedades que estes
gerem:
StaffPropCount(staffNo, fName, lName, propCnt)
Preencha StaffPropCount usando a tabela Staff e
PropertyForRent.
224
INSERT INTO StaffPropCount
(SELECT s.staffNo, fName, lName, COUNT(*)
GROUP BY s.staffNo, fName, lName)
UNION
(SELECT staffNo, fName, lName, 0
FROM Staff
WHERE staffNo NOT IN
(SELECT DISTINCT staffNo
FROM PropertyForRent));
225
 Se
a segunda parte do UNION for omitida, exclui
os funcionários que neste momento não gerem
nenhuma propriedade.
226
UPDATE
UPDATE TableName
SET columnName1 = dataValue1
[, columnName2 = dataValue2...]
[WHERE searchCondition]
pode ser o nome de uma tabela base ou
de uma vista actualizável.
 a clausula SET especifica os nomes de uma ou
mais colunas que vão ser actualizadas.
 TableName
227
UPDATE
 clausula
WHERE é opcional:
– se omitida, as colunas identificadas vão ser
actualizadas em todos os registos;
– se especificada, só as linhas que satisfazem a
condição searchCondition é que vão ser
actualizadas.
 Os novos dataValue(s) têm de ser compatíveis com
o tipo de dados da coluna correspondente.
228
Exemplo 5.38/39 UPDATE Todas as linhas
Dê uma aumento de 3% a todo o pessoal.
UPDATE Staff
SET salary = salary*1.03;
Dê a todos os Managers um aumento de 5%.
UPDATE Staff
SET salary = salary*1.05
WHERE position = ‘Manager’;
229
Exemplo 5.40 UPDATE Várias Colunas
Promova David Ford (staffNo=‘SG14’) para
Manager e mude o salário para £18,000.
UPDATE Staff
SET position = ‘Manager’, salary = 18000
WHERE staffNo = ‘SG14’;
230
DELETE
DELETE FROM TableName
[WHERE searchCondition]
pode ser o nome de uma tabela base ou
de uma vista actualizável.
 searchCondition é opcional; se omitida, todas as
linhas são apagadas da tabela. Não apaga a tabela.
Se search_condition é especificada, só as linhas que
satisfazem a condição é que são apagadas.
 TableName
231
Exemplo
5.41/42
Especificadas
DELETE
Linhas
Apague todas as visitas relacionadas com a
propriedade PG4.
DELETE FROM Viewing
WHERE propertyNo = ‘PG4’;
Apague todos os registos da tabela Viewing.
DELETE FROM Viewing;
232
Capitulo 6
SQL: Definição de Dados
Transparências
 Tipos
de Dados suportados pelo standard SQL.
 Objectivo
 Como
das melhorias de integridade no SQL.
definir restrições de integridade usando
SQL.
 Como
usar as melhorias de integridade nas
expressões CREATE e ALTER TABLE.
234
 Objectivo
das Vistas.
 Como criar e apagar Vistas usando SQL.
 Como o SGBD efectua operações sobre vistas.
 Sobre que condições as vistas são actualizáveis.
 Vantagens e Desvantagens das Vistas.
 Como funciona o modelo de transacções ISO.
 Como usar as expressões GRANT e REVOKE
como controlo de segurança.
235
ISO SQL Tipos de Dados
236
Melhorias de Integridade
 Considere
–
–
–
–
–
cinco tipos de restrições de integridade:
Dados Obrigatórios.
Restrições de Domínio.
Integridade de entidades.
Integridade referencial.
Restrições de negócio.
237
Dados Obrigatórios
position
VARCHAR(10)
NOT NULL
Restrições de Domínio
(a) CHECK
sex
CHAR
NOT NULL
CHECK (sex IN (‘M’, ‘F’))
238
(b) CREATE DOMAIN
CREATE DOMAIN DomainName [AS] dataType
[DEFAULT defaultOption]
[CHECK (searchCondition)]
Por exemplo:
CREATE DOMAIN SexType AS CHAR
CHECK (VALUE IN (‘M’, ‘F’));
sex SexType
NOT NULL
239

searchCondition pode conter table lookup:
CREATE DOMAIN BranchNo AS CHAR(4)
CHECK (VALUE IN (SELECT branchNo
FROM Branch));

Domínios podem ser removidos usando DROP DOMAIN:
DROP DOMAIN DomainName
[RESTRICT | CASCADE]
240
IEF – Integridade de Entidades


Chave primária de uma tabela tem que conter um valor
único, não nulo para cada linha.
O standard ISO suporta a clausula FOREIGN KEY nas
expressões CREATE e ALTER TABLE :
PRIMARY KEY(staffNo)
PRIMARY KEY(clientNo, propertyNo)

Só se pode ter uma clausula PRIMARY KEY por tabela.
Pode-se no entanto garantir valores únicos para chaves
alternativas usando UNIQUE:
UNIQUE(telNo)
241
IEF – Integridade Referencial
 FK
é uma coluna ou um conjunto de colunas que
ligam cada linha da tabela filha que contém a FK à
linha da tabela pai que contém uma PK igual.
 Integridade referencial significa que, se FK contém
um valor, esse valor tem que se referir a uma linha
existente na tabela pai.
 O standard ISO suporta a definição de FKs com a
clausula FOREIGN KEY nas expressões CREATE
e ALTER TABLE:
FOREIGN
Branch
KEY(branchNo)
REFERENCES
242


Qualquer INSERT/UPDATE que tente criar um valor na
FK da tabela filha sem que exista um valor idêntico na
chave candidata da tabela pai é rejeitado.
A tentativa de actualizar/apagar o valor de uma chave
candidata na tabela pai com linhas idênticas nas tabelas
filhas, está dependente da acção referencial especificada
nas subclausulas de ON UPDATE e ON DELETE:
– CASCADE
– SET DEFAULT
- SET NULL
- NO ACTION
243
CASCADE: Apaga a linha da tabela pai e linhas
correspondentes das tabelas filhas, e assim sucessivamente em
cascata.
SET NULL: Apaga a linha da tabela pai e muda todas as
colunas FK na tabela filha para NULL. Só é valido se as
colunas FK não estiverem a NOT NULL.
SET DEFAULT: Apaga a linha da tabela pai e muda cada
componente da FK da tabela filha para o valor default
especificado. Só é válido se houver um valor DEFAULT
especificado para as colunas FK.
NO ACTION: Rejeita a operação na tabela pai. Default.
244
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL
FOREIGN KEY (ownerNo) REFERENCES Owner
ON UPDATE CASCADE
245
IEF – Restrições de Negócio
 Podemos
usar CHECK/UNIQUE no CREATE e
ALTER TABLE.
 Também existe:
CREATE ASSERTION AssertionName
CHECK (searchCondition)
 que
é muito semelhante à clausula CHECK.
246
IEF – Restrições de Negócio
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
GROUP BY staffNo
HAVING COUNT(*) > 100))
247
Definição de Dados
 SQL
LDD permite a criação e remoção de objectos
de base de dados como esquemas, domínios,
tabelas, vistas e índices.
 Principais expressões de SQL LDD são:
CREATE SCHEMA
CREATE/ALTER DOMAIN
CREATE/ALTER TABLE
CREATE VIEW
 Muitos
DROP SCHEMA
DROP DOMAIN
DROP TABLE
DROP VIEW
SGBDs também permitem:
CREATE INDEX DROP INDEX
248
Definição de Dados




Relações e outros objectos de bases de dados existem dentro
de um ambiente.
Cada ambiente contém um ou mais catálogos, e cada
catálogo consiste de um conjunto de esquemas.
Um esquema é uma colecção de objectos de bases de dados
relacionados.
Os objectos de um esquema podem ser tabelas, vistas,
domínios, assertions, collations, translations, e conjuntos de
caracteres. Todos pertencem ao mesmo owner.
249
CREATE SCHEMA
CREATE SCHEMA [Name |
AUTHORIZATION CreatorId ]
DROP SCHEMA Name [RESTRICT | CASCADE ]
 Com
a opção RESTRICT (default), o esquema
tem de estar vazio ou a operação falha.
 Com a opção CASCADE, a operação remove
todos os objectos associados ao esquema em
cascata. Se alguma destas operações falhar, o
DROP SCHEMA falha.
250
CREATE TABLE
CREATE TABLE TableName
{(colName dataType [NOT NULL] [UNIQUE]
[DEFAULT defaultOption]
[CHECK searchCondition] [,...]}
[PRIMARY KEY (listOfColumns),]
{[UNIQUE (listOfColumns),] […,]}
{[FOREIGN KEY (listOfFKColumns)
REFERENCES ParentTableName [(listOfCKColumns)],
[ON UPDATE referentialAction]
[ON DELETE referentialAction ]] [,…]}
{[CHECK (searchCondition)] [,…] })
251
CREATE TABLE





Cria uma tabela com uma ou mais colunas do tipo
dataType.
Com NOT NULL, o sistema rejeita qualquer tentativa de
inserção de um valor nulo nesta coluna.
Podemos especificar um valor DEFAULT para a coluna.
Chaves primárias devem ser sempres especificadas como
NOT NULL.
A clausula FOREIGN KEY especifica FK em conjunto
com a acção referencial
252
Exemplo 6.1 - CREATE TABLE
CREATE DOMAIN OwnerNumber AS VARCHAR(5)
CHECK (VALUE IN (SELECT ownerNo FROM
PrivateOwner));
CREATE DOMAIN StaffNumber AS VARCHAR(5)
CHECK (VALUE IN (SELECT staffNo FROM Staff));
CREATE DOMAIN PNumber AS VARCHAR(5);
CREATE DOMAIN PRooms AS SMALLINT;
CHECK(VALUE BETWEEN 1 AND 15);
CREATE DOMAIN PRent AS DECIMAL(6,2)
CHECK(VALUE BETWEEN 0 AND 9999.99);
253
Exemplo 6.1 - CREATE TABLE
CREATE TABLE PropertyForRent (
propertyNo PNumber NOT NULL, ….
rooms PRooms
NOT NULL DEFAULT 4,
rent
PRent
NOT NULL, DEFAULT 600,
ownerNo
OwnerNumber
NOT NULL,
staffNo
StaffNumber
Constraint StaffNotHandlingTooMuch ….
branchNo BranchNumber
NOT NULL,
PRIMARY KEY (propertyNo),
FOREIGN KEY (staffNo) REFERENCES Staff
ON DELETE SET NULL ON UPDATE CASCADE ….);
254
ALTER TABLE
 Adiciona
uma nova coluna à tabela.
 Remove uma coluna da tabela.
 Adiciona uma nova restrição à tabela.
 Remove uma restrição da tabela.
 Especifica um valor por defeito para uma coluna.
 Remove o valor por defeito de uma coluna.
255
Exemplo 6.2(a) - ALTER TABLE
Altere a tabela Staff e remova o valor por defeito
‘Assistant’ da coluna position e determine o para
valor por defeito da coluna sex o valor (‘F’).
ALTER TABLE Staff
ALTER position DROP DEFAULT;
ALTER TABLE Staff
ALTER sex SET DEFAULT ‘F’;
256
Exemplo 6.2(b) - ALTER TABLE
Remova a restrição de que um funcionário não
pode gerir mais de 100 propriedades ao mesmo
tempo da tabela PropertyForRent. Adicione uma
nova coluna (prefNoRooms) na tabela Client.
ALTER TABLE PropertyForRent
DROP CONSTRAINT StaffNotHandlingTooMuch;
ALTER TABLE Client
ADD prefNoRooms PRooms;
257
DROP TABLE
DROP TABLE TableName [RESTRICT | CASCADE]
ex.



DROP TABLE PropertyForRent;
Remove uma determinada tabela e todas as suas linhas.
Com a opção RESTRICT, se a existência de algum objecto
depender da existência desta tabela,, SQL não permite a
execução deste pedido.
Com a opção CASCADE, SQL remove todos os objectos
dependentes (e objectos dependentes destes).
258
Vistas
Vista
Resultado dinâmico de uma ou mais operações
relacionais sobre tabelas bases para produzir
uma nova relação.
 Relação
Virtual que não necessita de existir na
base de dados mas é produzida quando necessária
na altura do pedido.
259
Vistas
 Conteúdo
de uma vista é definido como uma
query sobre uma ou mais tabelas base.
 Com view resolution, qualquer operação efectuada
sobre uma vista é automaticamente traduzida em
operações sobre as relações da qual esta deriva.
 Com view materialization, a vista é armazenada
como uma tabela temporária, que é mantida à
medida que as tabelas base são actualizadas.
260
SQL - CREATE VIEW
CREATE VIEW ViewName [ (newColumnName [,...]) ]
AS subselect
[WITH [CASCADED | LOCAL] CHECK OPTION]
 Podemos
determinar um nome para cada coluna da vista.
 Se for especificado uma lista de nomes de colunas, tem de
ter o mesmo número de items que o número de colunas
produzidas pelo subselect.
 Se omitida, cada coluna toma o nome da coluna
correspondente do subselect.
261
SQL - CREATE VIEW




A lista de nomes tem de ser especificada se houver alguma
ambiguidade com os nomes das colunas.
O subselect é conhecido como defining query.
WITH CHECK OPTION assegura que se uma linha não
satisfizer a clausula WHERE da defining query, não é
adicionada a tabela base correspondente.
É necessário privilégio SELECT em todas as tabelas
referenciadas no subselect e privilégio USAGE em
qualquer domínio usado nas colunas referenciadas.
262
Exemplo 6.3 – Crie uma Vista Horizontal
Crie uma vista tal que o gerente (manager) do
escritório B003 só possa ver os detalhes dos
funcionários que trabalham no seu escritório.
CREATE VIEW Manager3Staff
AS
SELECT *
FROM Staff
263
Exemplo 6.4 - Crie uma Vista Vertical
Crie uma vista do detalhe do staff do escritório
B003 excluindo os salários.
CREATE VIEW Staff3
AS SELECT staffNo, fName, lName, position, sex
FROM Staff
264
Exemplo 6.5-Vistas com Junção e Agrupamento
Crie uma vista do staff que administre
propriedades para arrendar, incluindo o número
do escritório em que trabalham, código do
trabalhador e a quantidade de propriedades que
administram.
CREATE VIEW StaffPropCnt (branchNo, staffNo, cnt)
AS SELECT s.branchNo, s.staffNo, COUNT(*)
GROUP BY s.branchNo, s.staffNo;
265
Exemplo 6.5-Vistas com Junção e Agrupamento
266
SQL - DROP VIEW
DROP VIEW ViewName [RESTRICT | CASCADE]
 Remove
a definição da vista da base de dados.
 Por exemplo:
DROP VIEW Manager3Staff;
267
SQL - DROP VIEW
 Com
a opção CASCADE, todos os objectos
relacionados com a vista são removidos; i.e.
quaisquer vistas definidas sobre esta vista são
removidas.
 Com a opção RESTRICT (default), se a existência
de algum outro objecto depender da existência da
vista a ser removida, o comando é rejeitado.
268
Resolução de Vistas
Contar o número de propriedades geridas por cada
membro do escritório B003.
SELECT staffNo, cnt
FROM StaffPropCnt
WHERE branchNo = ‘B003’
ORDER BY staffNo;
269
(a) Os nomes das colunas da Vista na lista SELECT
são traduzidos para os nomes das colunas
correspondentes na definição da Vista:
SELECT s.staffNo As staffNo, COUNT(*) As cnt
(b) Os nomes das Vistas no FROM são substituídos
pelos correspondentes da lista do FROM da
definição da Vista:
270
(c) o WHERE da query do utilizador é combinado
com o WHERE da definição da Vista usando o
AND:
WHERE s.staffNo = p.staffNo AND branchNo = ‘B003’
(d) as clausulas GROUP BY e HAVING são copiadas
da definição da Vista:
(e) o ORDER BY é copiado da query e o nome da
coluna traduzido para o nome da coluna da
definição da Vista
ORDER BY s.staffNo
271
(f) A query final é então executada para devolver o
resultado:
SELECT s.staffNo, COUNT(*)
FROM staff s, PropertyForRent p
WHERE s.staffNo = p.staffNo AND
branchNo = ‘B003’
ORDER BY s.staffNo;
272
Restricções sobre Vistas
SQL impõe algumas restrições na criação e
utilização das Vistas.
(a) Se uma coluna da Vista for baseada numa função
de agregação:
– A coluna só pode aparecer na clausula SELECT e
na ORDER BY de queries que acedam a esta
Vista.
– A coluna não pode ser utilizada no WHERE nem
ser argumento numa função de agregação de
qualquer query que se baseie nesta Vista.
273
 Por
exemplo, a seguinte query não funciona:
SELECT COUNT(cnt)
FROM StaffPropCnt;
 Igualmente,
a seguinte query também falha:
SELECT *
FROM StaffPropCnt
WHERE cnt > 2;
274
(b) Uma Vista agrupada não pode ser joined com
uma tabela base ou vista.

Por exemplo, a Vista StaffPropCnt é uma Vista
agrupada, por isso qualquer tentativa de junção
desta Vista com outra tabela ou vista falha.
275
Actualização de Vistas
 Todas
as actualizações feitas sobre a tabela base
reflectem-se sobre todas as vistas que utilizem essa
tabela.
 À partida poderíamos esperar que se uma vista
fosse actualizada, as tabelas bases reflectiriam
essas actualizações.
276
 No
entanto, considerando a Vista StaffPropCnt.
 Se tentarmos inserir o registo em que no escritório
B003, o SG5 gere 2 propriedades:
INSERT INTO StaffPropCnt
VALUES (‘B003’, ‘SG5’, 2);
 Seria
necessário inserir 2 registos na tabela
PropertyForRent mostrando que propriedades é
que SG5 geria. No entanto, não sabemos que
propriedades são; i.e. não sabemos as chaves
primárias!
277
 Se
mudarmos a definição da vista e substituirmos
a contagem pelos códigos das propriedades:
CREATE VIEW StaffPropList (branchNo,
staffNo, propertyNo)
AS SELECT s.branchNo, s.staffNo, p.propertyNo
WHERE s.staffNo = p.staffNo;
278
 Tentando
inserir o registo:
INSERT INTO StaffPropList
VALUES (‘B003’, ‘SG5’, ‘PG19’);
 Ainda
existem problemas, porque na tabela
PropertyForRent todas as colunas à excepção de
postcode/staffNo não permitem nulos.
 No entanto, não existe forma de atribuir valores
às colunas que não admitem valores nulos.
279


ISO especifica quais as vistas que devem ser actualizáveis
no sistema conforme um standard.
Uma vista é actualizável se e só se :
- DISTINCT não for especificado.
- Cada elemento da lista SELECT da definição da vista é um nome de
uma coluna e nenhuma coluna aparece mais que uma vez.
- a clausula FROM especifica apenas uma tabela, excluindo qualquer
vista baseada numa junção, união, intersecção ou diferença.
- Não pode ter SELECT aninhados com referências à tabela externa.
- Não pode ter as clausulas GROUP BY ou HAVING.
- e também, cada linha adicionada via vista não pode violar as regras
de integridade da tabela base.
280
Vista actualizavel
Para uma vista ser actualizável, o SGBD deve
conseguir rastrear qualquer linha ou coluna até à
linha ou coluna da tabela base.
281
WITH CHECK OPTION
 As
linhas existentes numa vista satisfazem a
condição WHERE da query que a define.
 Se uma linha é modificada e deixa de satisfazer a
condição, desaparece da vista.
 Aparecem novas linhas na vista quando existem
insert/update na vista que satisfazem a condição
WHERE.
 Linhas que entram e saiem da vista são
denominadas migrating rows.
 WITH CHECK OPTION proíbe que uma linha
282
saia da vista.
WITH CHECK OPTION



LOCAL/CASCADED aplica-se nas hierarquias de vistas.
Com LOCAL, qualquer insert/update na vista e sobre
qualquer vista definida directa ou indirectamente sobre
esta vista não deve deixar a linha desaparecer da vista a
não ser que esta também desapareça da tabela ou vista de
que deriva.
Com CASCADED (default), qualquer insert/ update nesta
vista e sobre qualquer vista definida directa ou
indirectamente sobre esta vista não deve deixar que a linha
desapareça da vista.
283
Exemplo 6.6 - WITH CHECK OPTION
AS
SELECT *
FROM Staff
WHERE branchNo = ‘B003’
WITH CHECK OPTION;
 Não
podemos actualizar o número do escritório
B003 para B002 porque iria fazer com que a linha
desaparecesse da vista.
 Também não é possível inserir linhas na vista que
tenham um número de escritório diferente de
B003.
284
 Se
Manager3Staffnão for definida directamente
sobre Staff mas sobre outra vista de Staff:
CREATE VIEW LowSalary
AS SELECT * FROM Staff WHERE salary > 9000;
CREATE VIEW HighSalary
AS SELECT * FROM LowSalary
WHERE salary > 10000
WITH LOCAL CHECK OPTION;
AS SELECT * FROM HighSalary
285
UPDATE Manager3Staff
SET salary = 9500
WHERE staffNo = ‘SG37’;
O
Update falhava: embora o update causasse o
desaparecimento da linha de HighSalary, a linha
não desapareceria de LowSalary.
 No entanto, se o update fosse salary = 8000, este
seria executado uma vez que a linha também
desapareceria de LowSalary.
286
 Se
HighSalary fosse especificada WITH
CASCADED CHECK OPTION, determinar
salary = 9500 ou 8000 seria rejeitado porque a
linha iria desaparecer de HighSalary.
 Para prevenir este tipo de anomalias, cad vista
deveria ser criada usando WITH CASCADED
CHECK OPTION.
287
Vantagens das Vistas
 Independência
de Dados
 Actualidade
 Segurança
melhorada
 Complexidade reduzida
 Conveniência
 Customização
 Integridade de Dados
288
Desvantagens das Vistas
 Restrições
nas actualizações
 Restrição da Estrutura
 Performance
289
Materialização das Vistas




Mecanismo de resolução de vistas pode ser lento,
particularmente quando a vista é acedida frequentemente.
Materialização de vistas armazena a vista numa tabela
temporária no primeiro acesso à vista.
Daí em diante, queries baseadas na vista materializada
podem ser mais rápidas que a recomputação da vista em
cada acesso.
Dificuldade é manter a actualidade da vista à medida que
as tabelas base são actualizadas.
290
Manutenção das Vistas
 Manutenção
das Vistas visa aplicar apenas as
alterações necessárias para manter a vista
actualizada.
 Considere a seguinte vista:
CREATE VIEW StaffPropRent(staffNo)
AS SELECT DISTINCT staffNo
WHERE branchNo = ‘B003’ AND
rent > 400;
291
Materialização de Vistas





Se inserirmos uma linha em PropertyForRent com rent
400 então a vista não seria actualizada.
Se inserirmos uma linha com propriedade PG24 no
escritório B003 com staffNo = SG19 e rent = 550, então a
linha apareceria na vista materializada.
Se inserirmos uma linha com propriedade PG54 no
escritório B003 com staffNo = SG37 e rent = 450, então
nenhuma nova linha seria adicionada na vista
materializada.
Se removermos a propriedade PG24, a linha deveria ser
removida da vista materializada.
Se removermos a propriedade PG54, então a linha de
SG37 não deveria ser removida (devido a existência da
292
propriedade PG21).
Transacções




SQL define um modelo de transacções baseado em no
COMMIT e ROLLBACK.
Uma transacção é uma unidade lógica de trabalho, com
uma ou mais expressões SQL, que é executada
atomicamente no que diz respeito à recuperação.
Uma transacção SQL começa automaticamente com uma
das seguintes expressões SQL (e.g., SELECT, INSERT,
UPDATE).
Alterações provocadas por uma transacção não são visíveis
a transacções concorrentes até essa transacção finalizar.
293
Transacções

Uma transacção pode acabar de quatro formas diferentes:
- COMMIT acaba a transacção com sucesso, tornando as
alterações permanentes.
- ROLLBACK aborta a transacção, repondo os valores
alterados pela transacção.
- Para programmatic SQL, se o programa termina com
sucesso então a transacção termina com sucesso, mesmo
que o COMMIT nao tenha sido executado.
- Para programmatic SQL, se o programa terminal
anormalmente, a transacção é abortada.
294
Transacções
 Novas
transacções começam com a proxima
expressão transaction-initiating.
 Transacções SQL não podem ser aninhadas.
 SET TRANSACTION determina a transacção:
SET TRANSACTION
[READ ONLY | READ WRITE] |
[ISOLATION LEVEL READ UNCOMMITTED |
READ
COMMITTED|REPEATABLE
READ
|SERIALIZABLE ]
295
Restrição de Integridade Imediata ou Deferida
 Nem
sempre se quer que as restrições sejam
verificadas imediatamente, mas sim no final da
transacção.
 Restrição pode ser definida como INITIALLY
IMMEDIATE ou INITIALLY DEFERRED,
indicando o modo como cada restrição se
comporta no inicio de cada transacção.
 No ultimo caso, também é possível especificar se o
modo pode ser alterado usando a opção [NOT]
DEFERRABLE.
 Por defeito o modo é INITIALLY IMMEDIATE.
296
Restrição de Integridade Imediata ou Deferida
A
expressão SET CONSTRAINTS é usada da
seguinte forma:
SET CONSTRAINTS
{ALL | constraintName [, . . . ]}
{DEFERRED ¦ IMMEDIATE}
297
Controlo de Acessos – Identificadores de
Autorização e de Propriedade
O
Identificador de autorização é um identificador
normal SQL utilizado para estabelecer a
identidade de um utilizador. Tem normalmente
uma palavra passe a si associado.
 Usado para definir quais os objectos o utilizador
pode utilizar e quais as operações que pode
efectuar sobre esses objectos.
 Cada objecto criado em SQL tem um proprietário,
definido na clausula AUTHORIZATION do
esquema a que esse objecto pertence.
298
Privilégios
 Acções
permitidas ao utilizador numa determinada
tabela ou vista:
SELECT Consultar dados de uma tabela.
INSERT Inserir novas linhas numa tabela.
UPDATE Modificar linhas de dados numa tabela.
DELETE Remover linhas de dados de uma tabela.
REFERENCES Referencia colunas de tabelas nas
restrições de integridade.
USAGE
Usa dominios, collations, character sets, e
translations.
299
Privilégios
 Pode-se
restringir
INSERT/UPDATE/
REFERENCES a determinadas colunas.
 O proprietário da tabela tem de permitir aos
outros utilizadores os privilégios necessários
usando a expressão GRANT.
 Para criar uma vista, o utilizador tem de ter
privilégio de SELECT em todas as tabelas
necessárias à vista e privilégio REFERENCES nas
colunas a utilizar.
300
GRANT
GRANT {PrivilegeList | ALL PRIVILEGES}
ON
ObjectName
TO
{AuthorizationIdList | PUBLIC}
[WITH GRANT OPTION]
consiste em um ou mais dos
privilégios já enunciados separados por virgulas.
 ALL PRIVILEGES atribui todos os privilégios a
um utilizador.
 PrivilegeList
301
GRANT
 PUBLIC
permite que todos os utilizadores (
actuais e futuros ) tenham acesso.
 ObjectName pode ser uma tabela, vista, domínio,
character set, collation ou translation.
 WITH GRANT OPTION permite que os
privilégios sejam “passados” a outros.
302
Exemplo 6.7/8 - GRANT
Atribuir a Manager privilégios totais sobre a
tabela Staff.
GRANT ALL PRIVILEGES
ON Staff
TO Manager WITH GRANT OPTION;
Atribuir aos utilizadores Personnel e Director
privilégios SELECT e UPDATE na coluna salary
da tabela Staff.
GRANT SELECT, UPDATE (salary)
ON Staff
TO Personnel, Director;
303
Exemplo 6.9 – GRANT - Atribuir privilégios
específicos a PUBLIC
Atribuir a todos os utilizadores
SELECT na tabela Branch.
privilégio
GRANT SELECT
ON Branch
TO PUBLIC;
304
REVOKE

REVOKE retira privilégios atribuídos por GRANT.
REVOKE [GRANT OPTION FOR]
{PrivilegeList | ALL PRIVILEGES}
ON ObjectName
FROM {AuthorizationIdList | PUBLIC}
[RESTRICT | CASCADE]

ALL PRIVILEGES refere-se a todos os privilégios
atribuídos a esse utilizador pelo utilizador que esta a usar o
REVOKE.
305
REVOKE
 GRANT
OPTION FOR permite que os privilégios
“passados” via WITH GRANT OPTION do
GRANT possam ser retirados separadamente
destes privilégios.
 REVOKE falha se o seu resultado for um objecto
abandonado, p.ex. uma vista, a não ser que a
palavra chave CASCADE tenha sido especificada.
 Privilégios atribuídos a este utilizador por outros
utilizadores não são afectados.
306
REVOKE
307
Example 6.10/11 - REVOKE Specific Privileges
Retirar o privilégio SELECT sobre a tabela
Branch a todos os utilizadores.
REVOKE SELECT
ON Branch
FROM PUBLIC;
Retirar todos os privilégios atribuídos a Director
sobre a tabela Staff.
REVOKE ALL PRIVILEGES
ON Staff
FROM Director;
308
Capitulo 9
Planeamento, Desenho e Administração
de Bases de Dados
Transparências







Principais componentes de um sistema de informação.
Principais estágios do ciclo de vida de uma aplicação de
base de dados.
Principais fases do desenho de base de dados: desenho
conceptual, lógico, e físico.
Benefícios das ferramentas CASE.
Como avaliar e seleccionar um SGBD.
Distinção entre administração de daos e administração
de base de dados.
Propósito e tarefas associadas à administração de
dados e administração de base de dados.
3102
Software Depression
 Nas
ultimas décadas tem-se observado a
proliferação de aplicações de software, muitas
das quais requerem constante manutenção:
– Corrigir falhas,
– Implementar novos requisitos para os utilizadores,
– Modificar software para funcionar em novas/melhoradas
plataformas.

Esforço despendido na manutenção começou a
absorver recursos a uma taxa alarmante.
311
Software Depression

Como resultado, muitos projectos de software ficavam
– atrasados,
– fora do orçamento,
– pouco fiáveis,
– manutenção difícil,
– fraca performance.

No fim dos anos 60s, levou a ‘software crisis’, referido
agora como ‘software depression’.
312
Software Depression

As principais razões para a falha dos projectos de
software eram:
- falta da especificação completa dos requisitos;
- falta de métodos apropriados de desenvolvimento;
- fraca decomposição do design em componentes
específicos.

Uma aproximação estruturada para o desenvolvimento foi
proposta e chamava-se ciclo de vida dos sistemas de
informação ( information systems lifecycle ).
313
Sistema de Informação
Recursos que permitem a recolha,
manuseamento, controlo e dessiminação de
informação na organização.
 A Base
de Dados é um componente
fundamental do S.I., e o seu desenvolvimento/
uso deve ser visto segundo a perspectiva geral
dos requisitos da organização.
314
Ciclo de vida de uma aplicação de Base de
Dados
 Planeamento
 Definição
da base de dados
do Sistema
 Requisitos
de recolha e analise
 Desenho
da base de dados
 Selecção
do SGBD (opcional)
315
Ciclo de vida de uma aplicação de Base de
Dados
 Desenho
da Aplicação
 Prototipagem
(opcional)
 Implementação
 Conversão
e Alimentação de Dados
 Testes
 Manutenção
operacional.
316
Estágios do ciclo de vida de uma aplicação de
base de dados
317
Planeamento da Base de Dados
Actividades de Gestão que permitem que os
estágios do ciclo de vida de um aplicação de
base de dados sejam realizados tão eficiente e
objectivamente quanto possível.
 Devem
ser integradas com a estratégia global
do S.I. da organização.
318
Planeamento da Base de Dados –
Mission Statement

Mission statement para o projecto da base de dados
define os principais objectivos da aplicação de base de
dados.

Os orientadores do projecto de base de dados
normalmente definem o mission statement.

Mission statement ajuda a clarificar o propósito do
projecto de base de dados e providencia um caminho
mais claro para a criação de uma aplicação de base de
dados mais eficiente e objectiva.
319
Planeamento da Base de Dados –
Objectivos
 Uma
vez definido o mission statement, os
objectivos são também definidos.
 Cada
objectivo deve identificar uma tarefa
particular que a base de dados deve suportar.
 Pode
também incluir alguma informação
adicional que especifique o trabalho a relaizar,
os recursos a utilizar, e o dinheiro necessário.
320
Planeamento da Base de Dados
O
planeamento da base de dados deve também
incluir o desenvolvimento de standards que
determinem:
–
–
–
–
como vai ser feita a recolha da informação,
em que formato ela deve ser especificada,
qual a documentação necessária,
como deve ser feito o desenho e a
implementação.
321
Definição do Sistema
Descreve o escopo e os limites da aplicação de
base de dados e as vistas globais dos
utilizadores.
 Vista
do utilizador define o que é requerido da
aplicação de base de dados da perspectiva de:
– determinada função (como Manager ou
Supervisor) ou
– determinada secção (como marketing,
seccção pessoal, ou controlo de stock).
322
Definição do Sistema

Aplicação de Base de dados pode ter uma ou mais
vistas de utilizadores.

A identificação das vistas dos utilizadores garantem
que não sejam esquecidos potenciais utilizadores da
base de dados no desenvolvimento dos requisitos para
a nova aplicação.

As vistas dos utilizadores também ajudam no
desenvolvimento de uma aplicação de base de dados
complexa, permitindo que os requisitos sejam divididos
em peças mais simples.
323
Representação de uma Aplicação de Base de
Dados com Múltiplas Vistas de Utilizadores
324
Recolha e Analise de Requisitos
É um processo de recolha e analise de
informação sobre parte da organização a ser
suportada pela aplicação de base de dados, e
utilizar esta informação para identificar os
requisitos dos utilizadores no novo sistema.
325

A informação é recolhida para cada vista global
incluindo:
– Descrição dos dados utilizados ou produzidos;
– Detalhes sobre como esses dados serão
usados/produzidos;
– Quaisquer novos requisitos para a nova aplicação de
base de dados.

A informação é analisada para identificar requisitos a
serem incluídos na nova aplicação de base de dados.
326
 Outra
actividade importante é decidir como
gerir a aplicação de base de dados tendo em
conta várias vistas de utilizadores.
 Três principais aproximações:
– centralizada;
– integração de vistas;
– combinação das duas anteriores.
327
 Centralizada
– Requisitos de cada vista de utilizador é
agregada numa só colecção de requisitos.
– É criado um modelo global de dados
baseado neste conjunto de requisitos (que
representam todas as vistas dos
utilizadores).
328
Versão Centralizada para a Gestão de
várias Vistas de Utilizadores
329
 Integração
de Vistas
– São utilizados os requisitos de cada
vista de utilizador para criar um
modelo de dados separado.
330

Ao modelo de dados que representa uma vista de um
utilizador chama-se modelo de dados local, e é composto
por diagramas e documentação que descreve os
requisitos de uma vista particular de um utilizador sobre
a base de dados.

Os modelos de dados locais são depois fundidos de forma
a produzir o modelo de dados global, que representa a
vista global de todos os utilizadores sobre a base de
dados.
331
Integração de Vistas para a Gestão de
várias Vistas de Utilizadores
332
Desenho da Base de Dados
É o processo de criação do desenho da base de
dados que irá suportar as operações e
objectivos de uma empresa.
333
 Principais
Objectivos:
– Representar os dados e relacionamentos entre os
dados requeridos pelas principais áreas de aplicação
e grupos de utilizadores.
– Providenciar um modelo de dados que suporte
qualquer transacção sobre determinados dados.
– Especificar um desenho que seja estruturalmente
apropriado para responder aos requisitos minimos
de performance. (ex: tempos de resposta).
334

Aproximações são:
– Top-down (entidades  atributos )
» Bases de Dados complexas
– Bottom-up ( atributos  entidades )
» Bases de Dados simples
– Inside-out
» Semelhante a Bottom-up, mas identificando um conjunto
principal de entidades e pormenorizando daí em diante.
– Mixed
» Usa Bottom-up e Top-down, convergindo no final para um só
modelo.
335
 Os
principais objectivos da modelação de
dados são:
– ajudar a perceber o significado (semântica)
dos dados;
– facilitar a divulgação sobre os requisitos de
informação.
 Para
a construção do modelo de base de dados
é necessário responder a questões sobre as
entidades, relacionamentos, e atributos.
336
 Um
modelo de dados garante que se percebeu:
- a perspectiva de cada utilizador sobre os
dados;
- natureza dos dados, independentemente da
sua representação fisica;
- uso dos dados em todas as vistas dos
utilizadores.
337
Critérios para a Elaboração de um Modelo
de Dados Óptimo
338
 Três
fases no desenho da base de dados:
– Desenho Conceptual
– Desenho Lógico
– Desenho Físico.
339
Desenho Conceptual da Base de Dados
É o processo de construção de um modelo da
informação utilizada pela empresa,
independentemente de todas as considerações
físicas.
O
modelo de dados é construído utilizando a
informação recolhida na especificação de
requisitos dos utilizadores.
 Fonte
de informação para o desenho lógico.
340
Desenho Lógico da Base de Dados
É o processo de construção de um modelo de
informação utilizada numa empresa baseado
num modelo de dados especifico (e.g.
relacional), mas independente do SGBD e de
outras considerações físicas.
O
modelo conceptual é refinado e convertido
num modelo lógico de dados.
341
Desenho Físico de Base de Dados
É o processo de elaboração da descrição da
implementação da base de dados em
armazenamento secundário.
 Descreve
as estruturas de armazenamento e
métodos de acesso utilizados para alcançar um
acesso eficiente aos dados.
 Criado
para um determinado SGBD.
342
Arquitectura ANSI-SPARC de três níveis e
as Fases do Desenho da Base de Dados
343
Escolha do SGBD
Escolha de um SBGD apropriado para
suportar uma aplicação de base de dados.
 Executado assim que houver informação
suficiente sobre os requisitos do sistema.
 Principais passos para a escolha de um SGBD:
–
–
–
–
definir os pontos de referência no estudo;
lista de dois ou três produtos;
avaliar os produtos;
aconselhar a escolha e produzir um relatório.
344
Características da Avaliação de um SGBD
345
Características da Avaliação de um SGBD
346
Exemplo - Características da Avaliação de
um SGBD
347
Desenho da Aplicação
Desenho do interface com o utilizador e das
aplicações que usam e processam a base de
dados.
O
Desenho da Base de Dados e das Aplicações
são actividades paralelas.
 Inclui
duas actividades importantes:
– desenho das transacções;
– desenho do interface com o utilizador.
348
Desenho das Aplicações - Transacções
Uma tarefa, ou serie de tarefas, executadas por
um só utilizador ou aplicação, que acede ou
modifica o conteúdo da base de dados.
 Deve
especificar e documentar as
características de alto-nível das transacções
requeridas.
349
Desenho das Aplicações - Transacções
 Características
–
–
–
–
–
importantes das transacções:
dados a ser utilizados pela transacção;
características funcionais da transacção;
resultado da transacção;
importância para os utilizadores;
taxa de utilização.
 Três
tipos principais de transacções: recolha,
actualização, e composto.
350
Prototipagem
Construção de um modelo de trabalho da aplicação da base
de dados.

Objectivos
– identificar características do sistema que funcionam
adequadamente, ou inadequadamente;
– sugerir melhorias ou novas características;
– clarificar os requisitos dos utilizadores;
– avaliar fiabilidade do desenho do sistema.
351
Implementação
 Realização
aplicações.
física da base de dados e do desenho das
– Usar LDD para criar os esquemas das bases de
dados.
– Usar LDD para criar vistas especificas por
utilizadores.
– Usar 3GL ou 4GL para criar as aplicações. Inclui as
transacções das bases de dados usando a LMD,
possivelmente embebidas na linguagem de
programação.
352
Conversão e Alimentação de Dados
Transferência de quaisquer dados já existentes para a nova
base de dados e converter quaisquer aplicações para
trabalharem com a nova base de dados.


Só é necessário quando a nova base de dados vem substituir
um sistema antigo.
– O SGBD normalmente tem funções que importam
ficheiros para a nova base de dados.
Pode ser possível converter e utilizar aplicações do sistema
antigo para uso com o novo sistema.
353
Testes
Processo de execução da aplicação com o objectivo
de detecção de erros.
 Utilizar estratégias
planeadas de teste e dados reais.
 Os testes não mostram a falta de erros; apenas
mostram que existem erros.
 Demonstra que a base de dados e as aplicações
funcionam ( em principio ) segundo os requisitos.
354
Manutenção Operacional
Processo de monitorização e manutenção do sistema
após a instalação.



Monitorização da performance do sistema.
– Se houver falha de performance, pode ser
necessário a reorganização ou melhoramentos na
base de dados.
Manutenção e actualização da aplicação da base de
dados (quando necessário).
Implementação de novos requisitos na aplicação de
base de dados.
355
Ferramentas CASE

As ferramentas CASE providência alguns destes tipo
de suporte:
- dicionário de dados que armazena informação sobre
os dados da aplicação de B.D.;
- ferramentas de desenho para suporte à analise de
dados;
- ferramentas que permitem o desenvolvimento do
modelo de dados organizacional, e o modelo de
dados conceptual e lógico;
- ferramentas que permitem a prototipagem de
aplicações.
356
Ferramentas CASE
 Tem
–
–
–
–
–
os seguintes benefícios:
standards;
integração;
suporte a métodos standard;
consistência;
automatização .
357
Ferramentas CASE e Ciclo de Vida de uma
Aplicação de Base de Dados
358
Administração de Dados e Administração
de Bases de Dados
 O Administrador
de Dados (DA) e o
Administrador de Bases de Dados (DBA) são
responsáveis, respectivamente, pela gestão e
controlo das actividades associadas aos dados
da empresa e à base de dados da empresa.
O
DA preocupa-se mais com os estágios iniciais
do ciclo de vida e o DBA preocupa-se mais com
os últimos estágios.
359
Administração de Dados
 A gestão
dos recursos de dados inclui:
– planeamento das bases de dados,
– desenvolvimento e manutenção de
standards, políticas e procedimentos, e o
desenho conceptual e lógico das bases de
dados.
360
Administração de Bases de Dados
 Gestão
da implementação física da aplicação
de base de dados incluindo:
– desenho físico e implementação da base de
dados,
– determinar controlos de segurança e de
integridade,
– monitorização da performance do sistema, e
reorganização da base de dados.
361
Capitulo 10
Técnicas de Descoberta de Factos
Transparências
362
 Quando
é que as técnicas de descobertas de
factos são utilizadas no ciclo de vida de uma
aplicação de base de dados.
 Tipo
de factos recolhidos em cada estágio do
ciclo de vida da aplicação de base de dados.
 Tipo
de documentação elaborada em cada
estágio do ciclo de vida de uma aplicação de
base de dados.
 Técnicas
mais comuns na descoberta de factos.
363
 Como
usar cada técnica de descoberta de
factos e quais as suas vantagens e
desvantagens.
 Como
aplicar técnicas de descoberta de factos
nos estágios iniciais do ciclo de vida de uma
aplicação de base de dados.
364
 Necessárias
à captura de factos importantes à
construção da aplicação de base de dados.
 Estes
factos são recolhidos usando técnicas de
descoberta de factos.
 Processo
formal em que se usam técnicas como
entrevistas e questionários para a recolha de
factos sobre o sistema, requisitos e
preferências.
365
Quando são utilizadas as técnicas de
descoberta de factos?
 A descoberta
de factos é utilizada durante todo o
ciclo de vida de uma aplicação de base de dados.
No entanto, é crucial nos estágios iniciais como no
planeamento da base de dados, na definição do
sistema, e na recolha e analise de requisitos.
 Permite
que se aprenda a terminologia, os
problemas, oportunidades, restrições, requisitos e
prioridades da organização e dos utilizadores do
sistema.
366
Exemplos de Dados Recolhidos e da Documentação
Elaborada Durante o Ciclo de Vida de uma
367
Exemplos de Dados Recolhidos e da Documentação
Elaborada Durante o Ciclo de Vida de uma
368
O
responsável pelo desenvolvimento da base de
Dados usa normalmente várias técnicas de
descoberta de factos durante o projecto da base
de dados:
– análise da documentação
– entrevistas
– observando o funcionamento da organização
– pesquisa
– questionários
369
Análise da Documentação
 Pode
ser útil:
– para perceber o porquê da necessidade da
existência de uma base de dados;
– identificar parte da organização associada
ao problema;
– entender o sistema actual.
370
Exemplos do tipo de documentação que
deve ser analisada
371
Entrevistas
 Técnica
de Descoberta de Factos mais utilizada
e normalmente a mais útil.
 Permite
a recolha de informação de indivíduos
cara-a-cara.
 Os
objectivos são descobrir factos, verificar
factos, esclarecer factos, gerar entusiasmo,
envolver o utilizador final, identificar
requisitos, e recolher ideias e opiniões.
372
Vantagens e Desvantagens das Entrevistas
373
Entrevistas
 Dois
tipo de entrevistas: livres e estruturadas.
 Questões
abertas permitem ao entrevistado
responder da forma que acha mais adequada.
 Questões
fechadas restringem a resposta para
um conjunto de opções ou para respostas curtas
e directas.
374
Observar o Funcionamento da organização
 Técnica
eficiente para perceber o sistema.
 Possibilidade
de participar, ou observar, uma
pessoa a fazer determinada actividade para
aprender sobre o sistema.
 Útil
quando a validade de determinados dados
são questionados, ou quando a complexidade
de certos aspectos do sistema não permitem
uma explicação clara pelos utilizadores finais.
375
Vantagens e Desvantagens do uso da
Observação
376
Pesquisa
É
Útil pesquisar o problema e a aplicação.
 Usar
jornais electronicos, livros de referência, e
a Internet (incluindo grupos de discussão e
foruns).
 Providencia
informação sobre como outros
resolveram problemas similares, se existem ou
não pacotes de software para resolver parcial
ou totalmente o problema.
377
Vantagens e Desvantagens do uso da
Pesquisa
378
Questionarios
 Coordenar a
consulta através de questionários
– documentos específicos que permitem a
recolha de factos de um grande número de
pessoas mantendo algum controle sobre as suas
respostas.
 Dois
tipo de questões, formato livre ou fixo.
379
Vantagens e Desvantagens do uso de
Questionários
380
Capitulo 13
Normalização
Transparências
 Propósito
da Normalização.
 Problemas
associados aos dados redundantes.
 Identificação
de vários tipos de anomalias de
actualização como p. ex. inserções, remoções e
modificações.
382
 Como
usar as dependências funcionais para
agrupar atributos em relações que estão numa
determinada forma normal.
 Como
usar o processo de normalização.
 Como
identificar as formas normais mais
comuns, nomeadamente 1FN, 2FN, 3FN, e
Boyce–Codd (BCNF).
 Como
identificar a quarta (4FN) e quinta
(5FN) forma normal.
383
Normalização
O
principal objectivo no desenvolvimento do
modelo lógico para sistemas de bases de dados
relacionais é a representação correcta dos
dados, dos suas relações e restrições.
 Para
atingir este objectivo, devemos identificar
um conjunto acertado de relações.
384
Normalização

As quatro formas normais mais utilizadas são a primeira
(1FN), segunda (2FN) e terceira (3FN) forma normal, e a
forma normal Boyce–Codd (BCNF).

Baseadas nas dependências funcionais entre atributos de
uma relação.

Uma relação pode ser normalizada até uma forma
especifica para evitar a ocorrência de anomalias
provenientes de actualizações.
385
Redundância de Dados
O
principal objectivo do desenho da base de
dados relacional é agrupar atributos em
relações de forma a minimizar a redundância
de dados e reduzir o espaço de armazenamento
necessário pelas relações.
 Problemas
associados à redundância de dados
são ilustrados através da comparação das
relações Staff e Branch com a relação
StaffBranch.
386
387
 A relação
StaffBranch tem dados redundantes: a
informação de cada escritório é repetida para
cada membro do staff.
 Em
contraste, a informação de cada escritório só
aparece uma vez por cada escritório na relação
Branch e apenas o branchNo é repetido na
relação Staff, para representar onde cada
membro do staff trabalha.
388
Anomalias nas Actualizações
 Relações
que contêm informação redundante
podem sofrer de anomalias nas actualizações.
 Tipo
de anomalias nas actualizações incluem:
– Inserções,
– Remoções,
– Modificações.
389
Propriedade Lossless-join e Preservação de
Dependências
 Duas
propriedades importantes da
decomposição:
- propriedade Lossless-join permite-nos
encontrar qualquer instância da relação
original através das instâncias correspondentes
das relações mais simples.
- propriedade de preservação de dependências
permite-nos manter as restrições da relação
original aplicando restrições às tabelas mais
simples.
390
Dependência Funcional

Principal Conceito associado à normalização.

– Descreve o relacionamento entre atributos de uma
relação.
– Se A e B são atributos da relação R, B é
funcionalmente dependente de A ( notação A  B ),
se cada valor de A em R está associado a
exactamente um valor de B em R.
391
 Propriedade
do significado (ou
semântica) dos atributos numa relação.
 Representação
Gráfica:
de uma dependência funcional
refere-se ao atributo ou grupo de atributos na
parte esquerda da seta.
 Determinante
392
Exemplo - Dependência Funcional
393
 Principais
características das dependências
funcionais usadas na normalização:
– ter uma relação 1:1 entre o(s) atributo(s) do
lado esquerdo e os do lado direito da
dependência;
– sempre válidas;
– não triviais.
394
O
conjunto de todas as dependências
funcionais para uma dada relação pode ser
muito grande.
É
importante encontrar um método que reduza
este conjunto para um tamanho razoável.
É
necessário identificar um conjunto de
dependências funcionais (X) para uma relação
que seja inferior ao conjunto completo de
dependência funcionais (Y) para essa relação e
que esse conjunto tenha a propriedade de
incluir todas as dependências funcionais de Y.
395
 Seja
A, B, e C subconjuntos de atributos da
relação R. Os axiomas de Armstrong são:
1. Reflexividade
Se B é um subconjunto de A, então A  B
2. Augmentation
Se A  B, então A,C  C
3. Transitividade
Se A  B e B  C, então A  C
396
O Processo de Normalização
 Técnica
formal para analisar uma relação com
base na sua chave primária e nas dependências
funcionais entre atributos.
 Executada
normalmente num conjunto de
passos. A cada passo corresponde um forma
normal específica, que tem propriedades
conhecidas.
À
medida que a normalização progride, as
relações tornam-se mais restritivas (fortes) no
seu formato e também menos vulneráveis a
anomalias de actualização.
397
Relação entre Formas Normais
398
Forma não normalizada (UNF)
 Uma
tabela que contém um ou mais grupos
repetidos.
 Para
se criar uma tabela desnormalizada:
– Transformar os dados da fonte de
informação (ex. form) para uma tabela com
colunas e linhas.
399
Primeira Forma Normal (1FN)
 Uma
relação em que a intersecção entre uma
linha e uma coluna contenha um e um só valor.
400
UNF para 1FN
 Nomear
um atributo ou grupo de atributos
para chave da tabela não normalizada.
 Identificar grupos
repetidos na tabela
desnormalizada para os atributos chave.
401
UNF para 1FN
 Remover
grupos repetidos:
– Introduzindo dados apropriados nas colunas
vazias das linhas que contêm grupos
repetidos (‘flattening’ the table).
ou
– Colocar os dados repetidos assim como uma
cópia dos valores dos atributos chave numa
nova relação.
402
Segunda Forma Normal (2FN)
 Baseada
no conceito de dependência funcional
total:
– A e B são atributos de uma relação,
– B é totalmente dependente de A se B é
funcionalmente dependente de A mas não o é
de qualquer subconjunto de A.
 2FN
– Uma relação que está na 1FN e todo o
atributo não pertencente à chave primária é
totalmente dependente da chave primária.
403
1FN para 2FN
 Identifique
a chave primária da relação que
está na 1FN.
 Identifique
as dependências funcionais da
relação.
 Se
existem dependências parciais sobre a chave
primária, remova-as e coloque-as numa nova
relação em conjunto com uma cópia do seu
determinante.
404
Terceira Forma Normal (3FN)

Baseada no conceito de dependência transitiva:
– A, B e C são atributos de uma relação tal que se A  B
e B  C,
– então C é transitivamente dependente de A através de
B. (Desde que A não seja funcionalmente dependente de
B ou C).

3FN - Uma relação que está na 1FN e na 2FN em que
nenhum atributo não pertencente à chave primária é
transitivamente dependente da chave primária.
405
2FN para 3FN
 Identifique
a chave primária da relação que
está na 2FN.
 Identifique
as dependências funcionais da
relação.
 Se
existem dependências transitivas sobre a
chave primária, remova-as e coloque-as numa
nova relação em conjunto com uma cópia do
seu determinante.
406
Definições Gerais da 2FN e 3FN

Segunda Forma Normal (2FN)
– Uma relação que está na 1FN e todos os atributos
não pertencentes à chave primária são totalmente
dependentes de qualquer chave candidata.

Terceira Forma Normal (3FN)
– Uma relação que está na 1FN e na 2FN e na qual
nenhum atributo não pertencente à chave primária
depende de qualquer chave candidata.
407
Forma Normal Boyce–Codd (FNBC)
 Baseada
nas dependências funcionais,
incluindo sobre as chaves candidatas de uma
relação, no entanto a FNBC tem restrições
adicionais quando comparada com a definição
geral da 3FN.
 FNBC
– Uma relação está na FNBC se e só se
todo o determinante é chave candidata.
408
 A diferença
entre a 3FN e a FNBC é que para
uma dependência funcional A  B, a 3FN
permite este tipo de dependência numa relação
se B for um atributo da chave primária e A não
é uma chave candidata.
 Enquanto
que , FNBC insiste que para este
tipo de dependência permanecer na relação, A
tem de ser chave candidata.
 Todas
as relações na FNBC estão também na
3FN. No entanto, uma relação na 3FN pode
não estar na FNBC.
409
 Violação
da FNBC é bastante rara.
O
potencial para uma violação da FNBC pode
acontecer numa relação que:
– contém duas (ou mais) chaves candidatas
compostas;
– as chaves candidatas sobrepõem-se (i.e. têm
pelo menos um atributo em comum).
410
Revisão da Normalização (UNF até FNBC)
411
412
413
414
Quarta Forma Normal (4FN)
 Embora
a FNBC remova as anomalias
resultantes das dependências funcionais, outro
tipo de dependências chamada dependência
multi-valor (MVD) pode causar redundância
de dados.
 A existência
de MVDs numa relação é
resultante da 1FN e pode resultar em
redundância de dados.
415
Quarta Forma Normal (4FN) - MVD
 Dependência
entre atributos (por exemplo, A,
B, e C) numa relação, tal que para cada valor
de A existe um conjunto de valores de B e um
conjunto de valores de C. No entanto, o
conjunto de valores de B e de C são
independentes um do outro.
416
 MVD
entre atributos A, B, e C numa relação
representa-se com a seguinte notação:
A  B
A  C
417
 MVD
–
–
–
pode-se definir com sendo trivial ou nãotrivial.
MVD A  B numa relação R é
definida com sendo trivial se (a) B é um
subconjunto de A ou (b) A  B = R.
MVD é definida como sendo não-trivial se nem
(a) nem (b) são satisfeitas.
MVD trivial não especifica uma restrição sobre
a relação, enquanto que uma MVD não-trivial
especifica uma restrição.
418
 Definida
com uma relação que está na FNBC e
não contém MVDs não-triviais.
419
4FN - Example
420
Quinta Forma Normal (5FN)

Uma relação decomposta em duas relações tem de ter a
propriedade lossless-join, que garante que não serão
criados tuplos adicionais aquando da junção das
tabelas através de uma junção natural (natural join).

No entanto, existem requisitos para a decomposição de
uma relação em mais que duas relações.

Embora raros, estes casos são geridos pela dependência
de junção e pela Quinta Forma Normal (5FN).
421
Quinta Forma Normal (5FN)
 Uma
relação sem dependência de junção.
422
5FN - Exemplo
423
Capitulo 14
Metodologia Desenho Conceptual de Bases de Dados
Transparências

Propósito da metodologia de desenho.

Desenho da Base de Dados tem 3 fases principais:
Desenho Conceptual, Lógico, e Físico.

Como decompor o âmbito do desenho nas vistas
especificas de cada utilizador de uma empresa.

Como usar a modelação ER para construção de um
modelo de dados conceptual baseado em informação
adquirida sobre uma vista da empresa.
425
 Como
validar um modelo conceptual de forma
a garantir uma representação verdadeira e
precisa de uma vista da empresa.
 Como
documentar o processo do desenho
conceptual da Base de Dados.
 Os
utilizadores finais são parte integrante
durante o processo do desenho conceptual da
Base de Dados.
426
Metodologia de Desenho
 Abordagem
estruturada que usa
procedimentos, técnicas, ferramentas e
documentação para o suporte e ajuda do
processo de desenho.
 Metodologia
de Desenho de Base de Dados tem
3 fases principais:
– Desenho Conceptual da Base de Dados;
– Desenho Lógico da Base de Dados;
– Desenho Físico da Base de Dados.
427
Desenho Conceptual e Lógico da Base de
Dados
 Desenho
Conceptual da Base de Dados
– Processo de construção de um modelo de
informação usado numa empresa, independente de
todas as considerações físicas.
 Desenho
Lógico da Base de Dados
– Processo de construção de um modelo de
informação usado numa empresa baseado num
modelo de dados especifico (ex. relacional), mas
independente de um determinado SGBD e de outras
considerações físicas.
428
Desenho Físico da Base de Dados
 Processo
de produção de uma descrição da
armazenamento secundário; descreve as
relações base, organização de ficheiros, o
desenho dos índices utilizados para alcançar
um acesso eficiente aos dados, as restrições de
integridade e as medidas de segurança.
429
Factores críticos de sucesso no desenho da
Base de Dados
Trabalhar interactivamente o mais possível com os
utilizadores.
 Seguir uma metodologia estruturada durante o
processo de modelação de dados.
 Incorporar as considerações estruturais e de
integridade nos modelos de dados.
 Reunir a conceptualização, normalização e a
validação de transacções na metodologia de
modelação de dados.

430
Factores críticos de sucesso no desenho da
Base de Dados

Usar diagramas para representar o mais possível dos
modelos de dados.

Usar a Linguagem de Desenho de Bases de Dados
(LDBD) para representar semântica de dados
adicionais.

Construir um dicionário de dados para acrescentar aos
diagramas de modelos de dados.

Estar preparado para repetir passos.
431
Resumo da Metodologia – Desenho
Conceptual da Base de Dados
 Passo
1 Construir um modelo conceptual de dados
para cada vista de utilizador
– Passo 1.1 Identificar tipos de entidades
– Passo 1.2 Identificar tipos de relacionamentos
– Passo 1.3 Identificar e associar atributos aos tipos de entidades ou
relacionamentos
– Passo 1.4 Determinar os domínios dos atributos
– Passo 1.5 Determinar os atributos das chaves primárias e candidatas
– Passo 1.6 Considere o uso de conceitos de modelação avançada
(Passo opcional)
– Passo 1.7 Verificar a redundância do modelo
– Passo 1.8 Validar o modelo conceptual local com as transacções dos
utilizadores
– Passo 1.9 Rever o modelo conceptual de dados com o utilizador
432
Resumo da Metodologia – Desenho Lógico da
Base de Dados para o Modelo Relacional
 Passo
2 Construir e validar o modelo lógico de
dados de cada vista
– Passo 2.1 Remover componentes não compatíveis com o
modelo relacional (Passo opcional)
– Passo 2.2 Obter as relações para o modelo lógico de dados
– Passo 2.3 Validar as relações usando a normalização
– Passo 2.4 Validar as relações com as transacções de
utilizadores
– Passo 2.5 Definir restrições de integridade
– Passo 2.6 Rever o modelo lógico de dados com o utilizador
433
Resumo da Metodologia - Desenho Lógico da
Base de Dados para o Modelo Relacional
 Passo
3 Construir e Validar o modelo lógico global
de dados
– Passo 3.1 Fundir os modelos lógicos locais de dados no
modelo global
– Passo 3.2 Validar o modelo lógico global de dados
– Passo 3.3 Verificar crescimento futuro
– Passo 3.4 Rever o modelo lógico global de dados com os
utilizadores
434
Resumo da Metodologia - Desenho Físico da Base
de Dados para o Modelo Relacional
 Passo
4 Traduzir o modelo lógico global de dados
para o SGBD escolhido
– Passo 4.1 Desenhar as relações base
– Passo 4.2 Desenhar a representação dos dados derivados
– Passo 4.3 Desenhar as restrições empresariais
 Passo
–
–
–
–
5 Desenhar a representação física
Passo 5.1
Passo 5.2
Passo 5.3
Passo 5.4
Analisar as transacções
Escolher a organização dos ficheiros
Escolher Índices
Calcular a necessidade de espaço em disco
435
Resumo da Metodologia - Desenho Físico da Base
de Dados para o Modelo Relacional
 Passo
6 Desenhar as vistas dos utilizadores
 Passo 7 Desenhar os mecanismos de segurança
 Passo 8 Avaliar a introdução controlada de
redundância
 Passo 9 Monitorizar e Optimizar o Sistema
Operacional
436
Extracto do Dicionário de Dados da Vista Staff da
DreamHome com a descrição das Entidades
437
Primeiro diagrama ER para a Vista Staff
da DreamHome
438
DreamHome com a descrição dos relacionamentos
439
DreamHome com a descrição dos Atributos
440
Diagrama ER da Vista Staff da DreamHome com
as Chaves Primárias
441
Diagrama ER Revisto para a Vista Staff da
DreamHome com Specialization / Generalization
442
Usar Caminhos para verificar que o Modelo
Conceptual Suporta as Transacções dos Utilizadores
443
Modelo Conceptual Local de Dados da
Vista Staff com todos os Atributos
444
Passo 2 Build and Validate Local Logical
Data Model
Passo 2.1 Remove features not compatible with the
relational model (optional Passo)

To refine the local conceptual data model to remove
features that are not compatible with the relational
model. This involves:
–
–
–
–
remove *:* binary relationship types;
remove *:* recursive relationship types;
remove complex relationship types;
remove multi-valued attributes.
445
Remover Relacionamentos do Tipo *:*
446
Remover Relacionamentos Recursivos do
Tipo *:*
447
Remover
Complexo
Relacionamentos
do
Tipo
448
Remover Atributos Multivalor
449
Data Model
 Passo
2.2 Derive relations for local logical data
model
– To create relations for the local logical data model to
represent the entities, relationships, and attributes
that have been identified.
450
Passo 2.2 Derive Relations for Local
Logical Data Model
(1) Strong entity types
–
Create a relation that includes all simple attributes
of that entity. For composite attributes, include
only constituent simple attributes.
Staff (staffNo, fName, lName, position, sex, DOB)
Primary Key staffNo
451
Logical Data Model
(2) Weak entity types
–
–
Create a relation that includes all simple attributes
of that entity.
Primary key is partially or fully derived from each
owner entity.
Preference (prefType, maxRent)
Primary Key None (at present)
452
Logical Data Model
(3) 1:* binary relationship types
– Entity on ‘one side’ is designated the parent entity
and entity on ‘many side’ is the child entity.
– Post copy of the primary key attribute(s) of parent
entity into relation representing child entity, to act
as a foreign key.
453
Logical Data Model
(4) 1:1 binary relationship types
– More complex as cardinality cannot be used to
identify parent and child entities in a relationship.
– Instead, participation used to decide whether to
combine entities into one relation or to create two
relations and post copy of primary key from one
relation to the other. Consider following:
– (a) mandatory participation on both sides of 1:1 relationship;
– (b) mandatory participation on one side of 1:1 relationship;
– (c) optional participation on both sides of 1:1 relationship.
454
Logical Data Model
(a) Mandatory participation on both sides of 1:1
relationship
– Combine entities involved into one relation and
choose one of the primary keys of original entities to
be primary key of new relation, while other (if one
exists) is used as an alternate key.
Client (clientNo, fName, lName, telNo, prefType,
maxRent, staffNo)
Primary Key clientNo
Foreign Key staffNo references Staff(staffNo)
455
Logical Data Model
(b) Mandatory participation on one side of a 1:1
relationship
– Identify parent and child entities using participation
constraints.
– Entity with optional participation is designated parent
entity, and other entity designated child entity.
– Copy of primary key of parent placed in relation
representing child entity.
– If relationship has one or more attributes, these
attributes should follow the posting of the primary
key to the child relation.
456
Logical Data Model
(b) Mandatory participation on one side of a 1:1
relationship - Example
457
Logical Data Model
(c) Optional participation on both sides of a 1:1
relationship
– Designation of the parent and child entities is
arbitrary unless can find out more about the
relationship.


Consider 1:1 Staff Uses Car relationship with optional
participation on both sides. Assume majority of cars,
but not all, are used by staff and only minority of staff
use cars.
Car entity, although optional, is closer to being
mandatory than Staff entity. Therefore designate Staff
as parent entity and Car as child entity.
458
Logical Data Model
(5) 1:1 recursive relationships - follow rules for
participation for a 1:1 relationship.
– mandatory participation on both sides: single
relation with two copies of the primary key.
– mandatory participation on only one side: option to
create a single relation with two copies of the
primary key, or create a new relation to represent
the relationship. The new relation would only have
two attributes, both copies of the primary key.
– optional participation on both sides, again create a
new relation as described above.
459
Logical Data Model
(6) Superclass/subclass relationship types
– Identify superclass as parent entity and subclass entity
as child entity.
– There are various options on how to represent such a
relationship as one or more relations.
– Most appropriate option dependent on number of
factors such as:
» disjointness and participation constraints on the superclass/subclass
relationship,
» whether subclasses are involved in distinct relationships,
» number of participants in superclass/subclass relationship.
460
Guidelines for Representation of
Superclass / Subclass Relationship
461
Logical Data Model
(7) *:* binary relationship types
– Create relation to represent relationship and
include any attributes that are part of relationship.
– Post a copy of the primary key attribute(s) of the
entities that participate in relationship into new
relation, to act as foreign keys.
– These foreign keys will also form primary key of
new relation, possibly in combination with some of
the attributes of the relationship.
462
Logical Data Model
(7) *:* binary relationship types - Example
463
Logical Data Model
(8) Complex relationship types
– Create relation to represent relationship and include
any attributes that are part of the relationship.
– Post copy of primary key attribute(s) of entities that
participate in the complex relationship into new
relation, to act as foreign keys.
– Any foreign keys that represent a ‘many’
relationship (for example, 1..*, 0..*) generally will
also form the primary key of new relation, possibly
in combination with some of the attributes of the
relationship.
464
Logical Data Model
(8) Complex relationship types - Example
465
Logical Data Model
(9) Multi-valued attributes
– Create new relation to represent multi-valued
attribute and include primary key of entity in new
relation, to act as a foreign key.
– Unless the multi-valued attribute is itself an
alternate key of the entity, primary key of new
relation is combination of the multi-valued attribute
and the primary key of the entity.
466
Logical Data Model
(9) Multi-valued attributes - Example
467
Summary of How to Map Entities and
Relationships to Relations
468
Relations for the Staff View of DreamHome
469
Data Model
 Passo
2.3 Validate relations using normalization
– To validate the relations in the local logical data model
using the technique of normalization.
 Passo
2.4 Validate relations against user
transactions
– To ensure that the relations in the local logical data
model support the transactions required by the view.
 Passo
2.5 Define integrity constraints
– To define the integrity constraints given in the view
(i.e. required data, entity and referential integrity,
domains, and enterprise constraints).
470
Referential Integrity Constraints for
Relations in Staff View of DreamHome
471
Data Model
 Passo
2.6 Review local logical data model with
user
– To ensure that the local logical data model and
supporting documentation that describes the model
is a true representation of the view.
472
Passo 3 Build and Validate Global Logical
Data Model
To combine the individual local logical data
models into a single global logical data model
that represents the enterprise.
 Passo
3.1 Merge local logical data models into
global model
– To merge the individual local logical data models
into a single global logical data model of the
enterprise.
473
Data Model
 Typically
includes:
– (1) Review the names and contents of
–
–
–
–
entities/relations and their candidate keys.
(2) Review the names and contents of
relationships/foreign keys.
(3) Merge entities/relations from the local data
models.
(4) Include (without merging) entities/relations
unique to each local data model.
(5) Merge relationships/foreign keys from the local
data models.
474
Data Model
– (6) Include (without merging) relationships/foreign
keys unique to each local data model.
– (7) Check for missing entities/relations and
relationships/foreign keys.
– (8) Check foreign keys.
– (9) Check Integrity Constraints.
– (10) Draw the global ER/relation diagram.
– (11) Update the documentation.
475
Data Model
476
Data Model
477
Data Model
 Passo
3.2 Validate global logical data model
– To validate the relations created from the global
logical data model using the technique of
normalization and to ensure they support the
required transactions, if necessary.
 Passo
3.3 Check for future growth
– To determine whether there are any significant
changes likely in the foreseeable future and to assess
whether the global logical data model can
accommodate these changes.
478
Data Model
 Passo
3.4 Review global logical data model
with users
– To ensure that the global logical data model is a true
representation of the enterprise.
479
Relations for the Branch View of DreamHome
480
Relations that Represent the Global
Logical Data Model for DreamHome
481
Diagrama Global das Relações da
DreamHome
482
Capitulo 16
Metodologia – Desenho Físico de Bases
de Dados Relacionais
Transparências
 Propósito
do desenho físico da base de dados.
 Como
mapear o desenho lógico da base de
dados para o desenho físico da base de dados.
 Como
desenhar as relações base para um
determinado SGBD.
 Como
desenhar as restrições de negócio para
um determinado SGBD.
484
 Como
seleccionar correctamente a organização
de ficheiros baseada na análise das transacções.
 Quando
usar índices secundários para
melhorar a performance.
 Como
calcular o tamanho da base de dados.
 Como
desenhar as vistas dos utilizadores.
 Como
desenhar mecanismos de segurança para
satisfazer os requisitos dos utilizadores.
485
Comparação entre o Desenho Lógico e
Físico de uma Base de Dados
 A fonte
de informação para o processo do
desenho físico inclui o modelo lógico global e a
documentação que descreve esse modelo.
O
desenho lógico da base de dados está
preocupado com o Quê, enquanto que o
desenho físico da base de dados está
preocupado com o Como.
486
Desenho Físico da Base de Dados
Processo que produz a descrição da
armazenamento secundário; descreve as
relações base, organização de ficheiros, os
índices necessários ao acesso eficiente dos
dados, assim como quaisquer restrições de
integridade e mecanismos de segurança.
487
Resumo da Metodologia do Desenho Físico
da Base de Dados
 Passo
4 Tradução do modelo lógico global para o
SGBD escolhido
– Passo 4.1 Desenho das relações base
– Passo 4.2 Desenho da representação dos dados
derivados
– Passo 4.3 Desenho das restrições de negócio
488
da Base de Dados
 Passo
–
–
–
–
5 Desenho da representação física
Passo 5.1 Analise das transacções
Passo 5.2 Escolha da organização de
ficheiros
Passo 5.3 Escolha dos índices
Passo 5.4 Cálculo das necessidades de
espaço em disco
489
da Base de Dados
 Passo
6 Desenho das vistas dos utilizadores
 Passo 7 Desenho dos mecanismos de segurança
 Passo 8 Avaliar a introdução controlada de
redundância
 Passo 9 Monitorizar e optimizar o sistema
operacional
490
Passo 4 - Tradução do modelo lógico global
para o SGBD escolhido

É necessário conhecer o funcionamento do SGBD escolhido,
saber como criar as relações base e saber se o sistema
suporta a definição de:
– PKs, FKs, e AKs;
– Dados necessários– ex. Saber se o sistema suporta NOT
NULL;
– Domínios;
– Restrições de integridade relacional;
– Restrições de negócio.
491
LDBD para a relação PropertyForRent
492
Relação PropertyforRent e Staff com o atributo
derivado noOfProperties
493
Passo 4.3 Desenho de Restrições de
Negócio

Alguns SGBD permitem mais facilmente que outros a
definião de restrições de negócio. Exemplo:
CONSTRAINT StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
GROUP BY staffNo
HAVING COUNT(*) > 100))
494
Passo 5 Desenho da Representação Física
Determinar a organização de ficheiros ideal
para o armazenamento das relações base e
índices necessários à obtenção de uma
performance aceitável; isto é, qual a forma de
armazenamento das relações e tuplos em disco.
495
Passo 5 Desenho da Representação Física
 Existem
um número de factores que podem ser
usados para a medir a eficiência do sistema:
- Transaction throughput: número de transações
processadas num determinado intervalo.
- Response time: tempo decorrido para a conclusão de uma
transacção.
- Disk storage: quantidade de disco necessário para
armazenamento dos ficheiros da base de dados.
 No
entanto, não só um factor está correcto.
Normalmente, é necessário haver um
compromisso entre factores para se alcançar um
balanço razoável.
496
Exemplo de um Formulário de Análise de
Transacções
497
Passo 5.2 Escolher a Organização de
Ficheiros
 Organização
de Ficheiros inclui Pilha, Hash,
Indexed Sequential Access Method (ISAM),
Árvores B+ e Clusters.
498
Capitulo 17
Metodologia – Monitorização e
Optimização do Sistema Operacional
Transparências
 Significado
da Desnormalização.
 Quando
desnormalizar para obter melhorias
de performance.
 A importância
da monitorização e da
optimização do sistema operacional.
500
Passo 8 - Avaliar a introdução controlada
de redundância
Determinar se a introdução de redundância de
uma forma controlada através da flexibilização
das regras da normalização produz uma
melhoria na performance do sistema.
501
Passo 8 - Avaliar a introdução controlada
de redundância
 De
notar que a desnormalização :
– Torna a implementação mais complexa;
– Pode sacrificar a flexibilização;
– Pode acelerar as consultas mas atrasa as
actualizações.
502
Diagrama Exemplo dos Relacionamentos
503
Exemplos das Relações
504
Passo 8.1 Combinar Relacionamentos 1:1
505
Passo 8.2 Duplicação de atributos não-chave nos
relacionamentos 1:* para reduzir junções
506
Passo 8.3 Duplicação das chaves estrangeiras nos
relacionamentos 1:* para reduzir junções
507
Passo 8.4 Duplicação de Atributos nos
relacionamentos *:* para reduzir junções
508
Passo 8.5 Introdução de Grupos Repetidos
509
Passo 8.6 Juntar as relações de lookup com
as relações base
510
Passo 9 - Monitorizar e optimizar o sistema
operacional
A monitorização e optimização do sistema
operacional serve para corrigir decisões inadequadas
ou implementar mudanças nos requisitos.
A DreamHome pretende armazenar fotografias das
propriedades, e comentários sobre a propriedades.
511
Capitulo 22
SGBDs Distribuídos – Conceitos e
Desenho
Transparências
512
 Conceitos.
 Vantagens
e Desvantagens das bases de dados
distribuídas.
 Funções e arquitectura para um SGBDD.
 Desenho de bases de dados distribuídas.
 Critérios de comparação para SGBDDs.
513
Conceitos
Base de Dados Distribuída
Uma colecção de dados partilhados interrelacionados
logicamente (e a descrição destes dados), distribuídos
fisicamente sobre uma rede de computadores.
SGBD Distribuído
Sistema de Software que permite a gestão das bases de
dados distribuídas e torna a distribuição transparente
para o utilizador.
514
Conceitos








Colecção de dados partilhados logicamente relacionados.
Dados divididos em fragmentos.
Fragmentos podem ser replicados.
Fragmentos/replicas atribuídos a sites.
Sites interligados por uma rede de comunicações.
Os dados em cada site estão sobre controlo de um SGBD.
SGBDs gerem as aplicações locais autonomamente.
Cada SGBD participa em pelo menos uma aplicação
global.
515
SGBD Distribuído
516
Processamento Distribuído
Uma base de dados centralizada que pode ser
acedida através de uma rede de computadores.
517
SGBD Paralelos
Um SGBD que funciona sobre vários processadores e
discos com o objectivo, sempre que possível, de executar
operações em paralelo para melhorar a performance.


Baseado na premissa que um sistema com um só
processador não consegue responder a requisitos de
escalabilidade, segurança e performance.
SGBDs Paralelos interligam várias, pequenas máquinas
para atingir o mesmo rendimento que uma única grande
máquina, mas com mais escalabilidade e maior segurança.
518
SGBDs Paralelos
 As
principais arquitecturas para SGBDs Paralelos
são:
– Memória partilhada,
– Disco partilhado,
– Nada partilhado.
519
SGBDs Paralelos
(a) Memória
partilhada
(b) Disco
partilhado
(c) Nada
partilhado
520
Vantagens dos SGBDDs
 Reflecte
a estrutura organizacional
 Melhor partilha e maior autonomia local
 Maior disponibilidade
 Maior confiança
 Performance melhorada
 Economia
 Crescimento Modular
521
Desvantagens dos SGBDDs
 Complexidade
 Custo
Manutenção
 Segurança
 Controlo de Integridade mais difícil
 Falta de standards
 Falta de experiência
 Desenho da Base de dados mais complexo
522
Tipos de SGBDDs
 SGBDDs
Homogéneos
 SGBDDs Heterogéneos
523
SGBDDs Homogéneos
 Todos
os sites usam o mesmo SGBD.
 Muito mais simples de “desenhar” e gerir.
 Abordagem
proporciona um crescimento
incremental e permite uma maior performance.
524
SGBDDs Heterogéneos



Sites podem ter SGBDs diferentes, possivelmente com
diferentes modelos de dados.
Ocorre quando os sites implementaram as suas bases de
dados e a integração só foi considerada mais tarde.
Traduções necessárias para permitir:
– Hardware diferente.
– SGBDs diferentes.
– Hardware e SGBDs diferentes.

Solução típica é o uso de gateways.
525
Funções de um SGBDD
É
de esperar que um SGBDD tenha pelo menos a
funcionalidade de um SGBD.
E
que
tenha
também
as
seguintes
funcionalidades:
– Serviços de comunicação estendidos.
– Dicionário de Dados Estendido.
– Processamento de Querys distribuído.
– Controlo de Concorrência Estendido.
– Serviços de recuperação estendidos.
526
Arquitectura de Referência para SGBDD


Dada a diversidade, não existe uma arquitectura
equivalente à arquitectura de 3 níveis ANSI/SPARC.
A arquitectura de referência consiste em:
–
–
–
–

Conjunto de esquemas globais externos.
Esquema global conceptual (GCS).
Esquema de Fragmentação e de Alocação.
Conjunto de Esquemas para cada SGBD local em conformidade
com os 3-níveis ANSI/SPARC.
Alguns níveis podem não aparecer, dependendo dos níveis
de transparência suportados.
527
Arquitectura de Referência para SGBDD
528
Componentes de um SGBDD
529
Desenho Distribuído de Bases de Dados
 Três
tópicos essenciais:
– Fragmentação,
– Alocação,
– Replicação.
530
Desenho Distribuido de Bases de Dados
Fragmentação
Relação pode ser dividida num conjunto de subrelações, que depois são distribuídas.
Alocação
Cada fragmento é armazenado no site com
distribuição “óptima”.
Replicação
Uma cópia do fragmento pode ser mantida em
vários sites.
531
Fragmentação


Definição e a alocação dos fragmentos de forma a obter:
– Proximidade do local de uso.
– Maior Segurança e Disponibilidade.
– Performance melhorada.
– Balanceamento da capacidade e custo de
armazenamento.
– Custo mínimo com comunicações.
Envolve a análise das aplicações mais importantes,
baseada na informação quantitativa/qualitativa.
532
Fragmentação
 Informação
quantitativa pode incluir:
– frequência em que a aplicação é executada;
– site de onde a aplicação é executada;
– critérios de performance para as transacções
e aplicações.
 Informação
qualitativa pode incluir transacções
que são executadas por aplicação, tipo de acesso
(leitura ou escrita), e predicados das operações
de leitura.
533
Alocação de Dados
 Quatro
estratégias alternativas para a alocação
dos dados:
– Centralizada,
– Particionada (ou Fragmentada),
– Replicação Completa,
– Replicação Selectiva.
534
Alocação de Dados
Centralizada
Consiste numa só base de dados e um SGBD
armazenados num site com utilizadores
distribuídos por uma rede.
Particionada
Base de Dados particionada em fragmentos
independentes, cada um atribuído a um site.
535
Alocação de Dados
Replicação Completa
Consiste em manter uma cópia completa da
base de dados em cada site.
Replicação Selectiva
Combinação de particionamento, replicação e
centralização.
536
Comparação
das
Estratégias
Distribuição de Dados
para
a
537
Porquê Fragmentar?
 Uso
– Aplicações trabalham com vistas e não com
relações completas.
 Eficiência
– Dados são armazenados o mais perto possível
de onde são mais frequentemente utilizados.
– Dados que não são necessários às aplicações
locais não são armazenados.
538
Porquê Fragmentar?
 Paralelismo
– Com fragmentos como unidades de
distribuição, as transacções podem ser
divididas em várias subqueries que trabalham
com fragmentos.
 Segurança
– Dados não necessários às aplicações locais não
são armazenados, como tal também não são
acedidos por utilizadores não autorizados.
539
Porquê Fragmentar?
 Desvantagens
– Performance,
– Integridade.
540
Tipos de Fragmentação

Quatro tipos de fragmentação:
–
–
–
–

Horizontal,
Vertical,
Mista,
Derivada.
Outra possibilidade é não fragmentar:
– Se a relação é pequena e não é actualizada
frequentemente, pode ser melhor não fragmentar a
relação.
541
Fragmentação Horizontal e Vertical
542
Fragmentação Mista
543
Exemplo – Performance da Transparência
Property(propNo, city)
10000 registos em Londres
Client(clientNo,maxPrice) 100000 registos em Glasgow
Viewing(propNo, clientNo) 1000000 registos em Londres
SELECT p.propNo
FROM Property p INNER JOIN
(Client c INNER JOIN Viewing v ON c.clientNo = v.clientNo)
ON p.propNo = v.propNo
WHERE p.city=‘Aberdeen’ AND c.maxPrice > 200000;
544
Assumir:
 Cada tuplo em cada relação tem 100 caracteres.
 10 clientes com preço máximo superior a £200,000.
 100000 visitas para as propriedades em Aberdeen.
 Tempo de computação negligivel comparativamente ao
tempo de comunicação.
 Taxa de transferência 10000 caracteres por segundo
 Atraso no envio de uma mensagem – 1 segundo
545
546
Capitulo 24
Introdução a SGBDs de Objectos
Transparências
547

Aplicações de Bases de Dados Avançadas.

Inadequação dos SGBD Relacionais para as aplicações
avançadas de Bases de Dados.

Conceitos de Orientação aos Objectos.

Problemas com o armazenamento de objectos numa base
de dados relacional.

A próxima geração de sistemas de bases de dados.
548
Aplicações de Bases de Dados Avançadas
Computer-Aided Design (CAD)
 Computer-Aided Manufacturing (CAM)
 Computer-Aided Software Engineering (CASE)
 Network Management Systems
 Office Information Systems (OIS) and Multimedia
Systems
 Digital Publishing
 Geographic Information Systems (GIS)
 Interactive and Dynamic Web sites
 Outras aplicações com objectos complexos e
interrelacionados e dados procedimentais.

549
 Armazena
dados relacionados com desenhos
mecânicos e eléctricos, por exemplo, edifícios,
aviões, e circuitos integrados.
 Desenhos deste tipo têm algumas características
comuns:
– Dados de muitos tipos, cada um com um
pequeno numero de instancias.
– Desenhos podem ser muito grandes.
550
– Desenho não é estático, evolve ao longo do
tempo.
– Updates são de grande envergadura.
– Envolve controlo de versões e gestão de
configurações.
– Engenharia Cooperativa.
551
Aplicações de Bases de Dados Avançadas
 Computer-Aided
Manufacturing (CAM)
– Armazena Dados similares ao de CAD, mais
dados sobre a produção.
 Computer-Aided Software Engineering (CASE)
– Armazena dados sobre os diferentes estágios
do ciclo de vida do desenvolvimento de
software.
552
Network Management Systems




Coordenação da disponibilização de serviços de
comunicação sobre uma rede de computadores.
Tarefas como gestão de caminhos de rede, gestão de
problemas, e planeamento da rede.
Sistemas que lidam com dados complexos e que
requerem
performance
em
tempo
real
e
operacionalidade continua.
Encaminhar conexões, diagnosticar problemas,
balanceamento de carga, sistemas têm de se mover
sobre este complexo grafo em tempo real.
553
Office Information
Multimedia Systems
Systems
(OIS)
and
 Armazena
dados relacionados com o controlo de
informação de uma empresa, incluindo correio
electrónico, documentos, facturas, etc.
 Sistemas modernos lidam com texto e livre
formato, fotografias, diagramas, porções de áudio
e vídeo.
 Os documentos podem ter uma estrutura
especifica, por exemplo usando linguagem de
marcação como SGML, HTML, ou XML.
554
Digital Publishing
 Possibilidade
de armazenar livros, jornais,
artigos electronicamente e disponibilizá-los
através de redes de banda larga aos
consumidores.
 Como com os OIS, digital publishing está a ser
expandido de forma a lidar com documentos
multimedia com texto, áudio, imagem, e vídeo e
animação.
 A quantidade de informação disponível a ser
colocada online é da ordem dos petabytes (1015
bytes), tornando-as nas maiores bases de dados
que os SGBD alguma vez tiveram de gerir.
555
Geographic Information Systems (GIS)
 As
bases de dados GIS armazenam informação
espacial e temporal, como a que é utilizada na
gestão de terras e exploração subaquática.
 Maior parte dos dados é proveniente de
fotografias topográficas e de satélites, e tendem
a ser muito grandes.
 Procuras podem envolver a identificação de
características baseadas em, por exemplo,
forma, cor, ou textura, usando técnicas
avançadas de reconhecimento de padrões.
556
Interactive and Dynamic Web Sites


Considere um site web com catálogo online de venda de
roupa. O site Web mantém um conjunto de preferências
das visitas passadas ao site e permite ao visitante:
– obter imagens 3D de qualquer item baseado em cor,
tamanho, tecido, etc.;
– modificar as imagens 3D para contemplar
movimento, iluminação, etc.;
– seleccionar acessórios apresentados numa barra
lateral para juntar ao conjunto;
Necessidade de manusear conteúdos multimedia e de
modificar interactivamente a visualização baseado nas
preferências e escolhas do utilizador. E tem ainda a
complexidade de fornecer rendering 3D.
557
Pontos Fracos dos SGBD Relacionais
 Pobre
Representação de Entidades do “Mundo-
Real”
– Normalização leva a relações que não
correspondem a entidades no “mundo-real”.
 Semantic
Overloading
– Modelo Relacional apenas tem uma
construção para representar dados e
relacionamentos entre dados: a relação.
– Relational model é semantically overloaded.
558
 Pobre
Suporte a Restrições de Intergridade e de
Negócio
 Estrutura
de Dados Homogénea
– Modelo Relacional assume homogeneidade
vertical e horizontal.
– Muitos SGBDRs permitem Binary Large
Objects (BLOBs).
559

Operações Limitadas
– SGBDRs apenas contém um conjunto fixo de
operações que não pode ser expandido.

Dificuldade no Manuseamento de Queries Recursivas
– Extremamente difícil produzir queries recursivas.
– Extensão proposta para álgebra relacional para
permitir este tipo de queries, unary transitive
(recursive) closure operation.
560
Example - Recursive Query
561

Impedance Mismatch
– Maioria das LMDs não possuem computational
completeness.
– Como solução, SQL pode ser embebido numa
linguagem de alto-nivel 3GL.
– Isto produz impedance mismatch – mistura de
diferentes paradigmas de programação.
– Estima-se que cerca de 30% do trabalho e do código
de programação seja despendido nas conversões
entre linguagens.
562
 Outros
Problemas com SGBDRs
– Transacções são normalmente de curta
duração e os protocolos de controlo de
concorrência não se ajustam a transacções
longas.
– Modificações aos Esquemas são difíceis.
– SGBDRs são pobres no acesso via navegação.
563
Conceitos Orientação Objectos
 Abstracção,
encapsulamento, information hiding.
 Objectos e atributos.
 Identidade do Objecto.
 Métodos e mensagens.
 Classes, subclasses, superclasses, e herança.
 Overloading.
 Polimorfismo e dynamic binding.
564
Identidade do Objecto
O Identificador do Objecto (OID) atribuído ao
objecto aquando da sua criação é:
–
–
–
–
Gerado pelo Sistema.
Único para esse objecto.
Invariante.
Independente dos valores dos seus atributos
(isto é, o seu estado).
– Invisivel ao utilizador (idealmente).
565
Identidade do Objecto - Implementação


Em SGBDR, a identidade do objecto é baseada nos
valores: a chave primaria é usada para a identificação
unívoca.
As chaves primárias não fornecem o tipo de identidade de
objecto requeridas pelos sistemas OO:
– chave única apenas dentro da relação, não no sistema
inteiro;
– chave geralmente escolhida de entre atributos da
relação, fazendo com que esteja dependente do estado
do objecto.
566
Vantagens dos OIDs
 São
eficientes.
 São rápidos.
 Não podem ser modificados pelo utilizador.
 Independentes do contexto.
567
Instancia da Classe partilha Atributos e
Métodos
568
Objectos Complexos
Um objecto que é composto por subobjectos mas
é visto como um único objecto.
 Objectos
participam num relacionamento APART-OF (APO).
 Objectos podem ser encapsulados dentro de
objectos complexos, acedidos pelos métodos do
objecto complexo.
 Ou ter uma existência independente, e apenas o
seu OID é armazenado no objecto complexo.
569
Sistemas de Bases de Dados da Próxima
Geração
Primeira Geração SGBD: Rede e Hierárquica
– Necessidade de programas complexos até para
queries simples.
– Independência de Dados mínima.
– Fundamento teórico não muito aceite.
Segunda Geração SGBD: SGBD Relacional
– Ajudou a resolver estes problemas.
Terceira Geração SGBD: OODBMS e ORDBMS.
570
História dos Modelos de Dados
571
Capitulo 30
Conceitos de Data Warehousing
Transparências
 Evolução
do data warehousing.
 Principais
conceitos e benefícios associados a
data warehousing.
 Diferenças
entre sistemas de processamento de
transacções (OLTP) e data warehousing.
 Problemas
associados a data warehousing.
 Arquitectura
e principais componentes da data
warehouse.
573
 Conceito
de data mart e principais razões para
a implementação de um data mart.
 Vantagens
e Desvantagens de um data mart.
 Principais
pontos sobre o desenvolvimento e
gestão de data marts.
574
A Evolução do Data Warehousing
 Desde
1970s, as organizações obtiveram
vantagem competitiva através do uso de
sistemas que automatizavam processos de
negócio com o objectivo de oferecer serviços
mais eficientes e mais baratos ao consumidor.
 Isto
resultou na acumulação crescente de dados
em bases de dados operacionais.
575

As organizações focam-se agora em como utilizar esses
dados operacionais para suportar decisões, de forma a
ganharem vantagem competitiva.

No entanto, os sistemas operacionais nunca foram
concebidos para suportar tais actividades.

Os negócios normalmente têm vários sistemas
operacionais com definições possivelmente
contraditórias e sobrepostas.
576
 As
Organizações necessitam de transformar os
seus arquivos de dados em conhecimento, de
forma a que uma vista integrada e consolidada
da empresa seja disponibilizada ao utilizador.
 Um
data warehouse foi a solução encontrada
para responder aos requisitos de um sistema
capaz de suportar decisões e receber dados de
vários sistemas operacionais.
577
Conceito de Data Warehousing
 A subject-oriented,
integrated, time-variant,
and non-volatile collection of data in support of
management’s decision-making process
(Inmon, 1993).
578
Dados Orientados ao Tema
 Um
Warehouse está organizado à volta de temas
essenciais à empresa (ex. clientes, produtos,
vendas) em vez de áreas aplicacionais (e.g.
facturação, gestão de stocks, vendas de produtos).
 Isto
reflecte a necessidade de armazenar dados de
suporte à decisão em vez de dados orientados à
aplicação.
579
Dados Integrados
O
data warehouse integra dados orientados às
aplicações da empresa de diferentes sistemas,
que muitas vezes incluem dados que estão
inconsistentes.
 Os
dados integrados têm que estar consistentes
de forma a disponibilizar uma vista unificada
de dados aos utilizadores.
580
Time-Variant Data
 Os
dados num warehouse são precisos e válidos
apenas num determinado espaço temporal ou
ao longo de um intervalo de tempo.
 A dependência
temporal evidencia-se também
na quantidade de tempo que os dados ficam
armazenados, na associação implícita ou
explicita do tempo aos dados, e o facto de os
dados representarem uma serie de snapshots.
581
Dados Não-Voláteis
 Os
Dados da warehouse não são actualizáveis
em tempo real mas são refrescados dos
sistemas operacionais regularmente.
 Os
novos dados são adicionados como
suplemento à base de dados, em vez de
substituídos.
582
Data Webhouse
 Web
é uma fonte enorme de dados
comportamentais à medida que os indivíduos
interagem através dos seus Web browsers com
Web sites remotos. Dados gerados por este
comportamento são chamados de clickstream.
 Um
data webhouse é um data warehouse
distribuído sem repositório central de dados que
é implementado sobre a Web para explorar
dados clickstream.
583
Beneficios de Data Warehousing
 Grande
potencial do retorno sobre o
investimento
 Vantagem
competitiva
 Incremento
de produtividade dos decision-
makers
584
Comparação de Sistemas Transaccionais e
Data Warehousing
585
Queries sobre Data Warehouse
 Os
tipos de queries que se espera que um data
warehouse responda variam entre muito
simples e altamente complexos e dependem do
tipo de ferramenta que o utilizador final está a
usar.
 As
Ferramentas utilizadas incluem:
– Relatórios, Querys e Ferramentas de
desenvolvimento de aplicações
– Sistemas de Informação para Executivos (EIS)
– Ferramentas OLAP
– Ferramentas de Data mining
586
Exemplos de Queries Típicas sobre Data
Warehouse





Qual o total facturado na Escócia no terceiro trimestre
de 2001?
Qual o total facturado de vendas de propriedades por
tipo no Grã-Bretanha em 2000?
Quais são as três áreas de aluguer mais procuradas em
cada cidade no ano de 2001 e como se comparam a
valores dos dois anos anteriores?
Qual o rendimento mensal de vendas obtido em cada
escritório, comparativamente aos 12 meses anteriores?
Qual seria o efeito nas vendas de propriedades nas
diferentes regiões da Grã-Bretanha se os custos legais
subissem 3.5% e as taxas governamentais descessem
1.5% para propriedades acima de £100,000?
587
Problemas com Data Warehousing
 Subestimar
os recursos necessários ao
carregamento dos dados
 Problemas
 Dados
escondidos nos sistemas fonte
necessários não capturados
 Crescimento
dos pedidos dos utilizadores finais
 Homogeneização
dos Dados
588
Problemas com Data Warehousing
 Precisa
 Dados
de grandes recursos
proprietários
 Manutenção
 Projectos
Elevada
de Longa Duração
 Complexidade
da Integração
589
Arquitectura
Warehouse
Típica
de
um
Data
590
Fluxos de Informação no Data Warehouse
591
 Inflow
– Processos associados à extracção,
limpeza e carregamento dos dados dos sistemas
fonte para o data warehouse.
 Upflow
– Processos associados à adição de
valor aos dados do warehouse através da
sumariação, agregação e distribuição dos
dados.
592

Downflow - Processos associados ao arquivamento e
armazenamento/recuperação dos dados do warehouse.

Outflow - Processos associados a disponibilização dos
dados aos utilizadores finais.

Metaflow - Processos associados à gestão dos
metadados.
593
Ferramentas e Tecnologias de Data
Warehousing

A construção de um data warehouse é uma tarefa complexa
uma vez que não existe nenhum fornecedor que comercialize
um conjunto de ferramentas ‘end-to-end’.

É necessário que se use um conjunto de produtos de
diferentes fornecedores para a construção do data
warehouse.

Garantir que estes produtos funcionem em conjunto e
integramente é um desafio enorme.
594
Ferramentas de Extracção, Limpeza e
Transformação
 As
tarefas de capturar os dados dos sistemas
fontes, limpá-los e transformá-los, e carregar
os resultados no sistema destino podem ser
executadas por diferentes produtos, ou por
uma só solução integrada.
 Soluções
Integradas incluem:
– Geradores de Código
– Ferramentas de Replicação de Dados
– Motores de Transformação Dinâmica
595
Requisitos do SGBD do Data Warehouse
Load performance
 Load processing
 Data quality management
 Query performance
 Terabyte scalability
 Mass user scalability
 Networked data warehouse
 Warehouse administration
 Integrated dimensional analysis
 Advanced query functionality

596
Metadados do Data Warehouse
 Os
metadados são utilizados para uma
variedade de propósitos e a sua gestão é um
assunto critico para a obtenção de um data
warehouse completamente integrado.
O
problema é que os metadados têm diversas
funções no data warehouse:
– Transformação e Carregamento de Dados.
– Gestão do Data warehouse.
– Geração de Queries.
597
Metadados do Data Warehouse
 Ferramentas
metadados.
geram e usam os seus próprios
O
desafio é sincronizar os metadados de diferentes
produtos de diferentes fornecedores usando
diferentes formas de armazenamento.
 Duas
organizações industriais: Meta Data
Coalition (MDC) e Object Management Group
(OMG) fundiram-se para propor um só standard
para os metadados e modelação de dados em data
warehousing chamado de Common Warehouse
598
Metamodel (CWM).
Arquitectura Tipical do Data Warehouse e
dos Data Mart
599
Data Mart
 Um
subconjunto de um data warehouse que
suporta os requisitos de um determinado
departamento ou função de negócio.
 Características
incluem:
– Foca-se apenas nos requisitos de um departamento ou
função de negócio.
– Não é normal conter dados operacionais detalhados ao
invés dos data warehouses.
– Mias compreensível e de fácil navegação.
600
Razões para a criação de um Data Mart

Dar aos utilizadores acesso aos dados que precisam de
analisar mais frequentemente.

Providenciar os dados que coincidam com a vista
colectiva de um grupo de utilizadores num
departamento ou área funcional.

Melhorar o tempo de resposta aos utilizadores finais
através da redução do volume de dados que precisam
de aceder.
601

Disponibilizar os dados devidamente estruturados
como foram especificados nos requisitos das
ferramentas dos utilizadores finais.

Construir um data mart é mais simples que construir
um data warehouse empresarial.

O custo de implementação de um data mart é
normalmente inferior ao custo necessário para a
criação de um data warehouse.
602
 Potenciais
utilizadores de um data mart são
mais facilmente definidos e podem ser mais
facilmente descobertos de forma a obter maior
suporte à criação de um projecto de um data
mart ao invés de um projecto de um data
warehouse empresarial.
603
Questões sobre Data Marts
 Data
mart functionality
 Data mart size
 Data mart load performance
 Users access to data in multiple data marts
 Data mart Internet / Intranet access
 Data mart administration
 Data mart installation
604

BeD - AEISTECP - GRUPO DE ESTUDOS

Transcrição

Documentos relacionados

EMPORT Os utilizadores do projeto EMPORT

estrutura organizacional - Keilla Lopes

Bases de dados relacionais e SQL

Mais informações

Visualizar PDF - BCSD Portugal

Aula 09 e 10 – Desenho Organizacional

Hotel Britania distinguido com a Expedia Insider`s Select

Cavitron Select

MySql Operações com SQL básico continuação

Da Prevenção do Erro Médico em Serviços de Saúde