Introdução à Linguagem Padrão Relacional SQL
Introdução
·
Modelo
relacional encontra-se padronizado pela indústria de informática. Ele é chamado
de padrão SQL (Structured
Query Language).
·
O padrão SQL
define precisamente uma interface SQL
para a definição de tabelas, para as operações sobre as mesmas (seleção,
projeção, junção, e outras) e para a definição de regras de integridade de
bancos de dados.
·
A interface
SQL é portanto implementada em todos os sistemas de bancos de dados relacionais existentes.
·
Por
quê a indústria tem
interesse em padronizar os sistemas de bancos de dados?
A razão é muito simples: a existência de padrões facilita a
interoperabilidade (comunicação entre máquinas, entre programas). Vivemos a era
das redes de computadores, dos bancos de dados distribuídos, etc.
·
Como vimos, um SGBD possui duas linguagens:
·
DDL: Linguagem
de Definição de Dados. Usada para definir os esquemas, atributos, visões,
regras de integridade, índices, etc.
·
DML: Linguagem
de Manipulação de Dados. Usada para se ter acesso aos dados armazenados no BD.
·
No passado,
havia três principais linguagens comerciais que implementavam
DDL e DML no Modelo Relacional:
1-
QUEL:
linguagem de consulta desenvolvida para o SGBD INGRES baseada no cálculo de tupla.
Retrieve
(t.nomeCliente)
Where t.nomeAgência = ‘Borborema’
2-
QBE (Query By Example):
desenvolvida pela IBM no início dos anos 60, realiza consultas através de exemplos:
o usuário dá um exemplo do que é desejado, o sistema generaliza este exemplo
para computar a resposta da consulta.
Ex.:
"Encontre todos os clientes que têm conta na agência Borborema"
Depósito
Agência |
Conta |
Cliente |
Saldo |
Borborema |
|
P.x |
|
|
|
|
|
|
|
|
|
3-
SQL (Structured Query Language): desenvolvida pela IBM (70) como parte do sistema
System R. A SQL foi
inicialmente chamada de SEQUEL.
É a linguagem de consulta padrão
para os SGBDR's.
Já existem padrões propostos:
ANSI-SQL, SQL-92 e novos padrões estão sendo desenvolvidos: SQL-3 (95).
A
linguagem SQL tem diversas partes:
- Linguagem de Definição de Dados
(DDL): fornece comandos para definições de esquemas de relação, criação/remoção
de tabelas, criação de índices e modificação de esquemas.
- Linguagem de Manipulação de Dados
(DML): inclui uma linguagem de consulta baseada na álgebra relacional e cálculo
relacional de tupla. Compreende comandos para
inserir, consultar, remover e modificar tuplas num
BD.
- Linguagem de Manipulação de Dados Embutida: designada para
acessar o BD dentro de linguagens de programação de uso geral como Cobol, C,
Pascal, CSP, 4GL, PL-I, Fortran, SQL-J entre outros.
- Definição de Visões: a SQL DDL
inclui comandos para definição de visões.
- Autorização: a SQL DDL inlcui comandos para especificação de direitos de acesso às
relações/visões.
- Integridade: a linguagem Sequel o System R inclui comandos
para verificação de restrições de integridade complexas. O padrão ANSI(86) limitou estas restrições. Porém, novos padrões tipo
SQL-3 incorporam várias formas de expressar restrições de integridade
(Assertivas e Triggers).
-
Controle de Transação: algumas implementações de SQL
permitem fazer tratamento de controle de concorrência e tolerância à falhas (locks explícitos, commit, rollback).
Tipos
de Domínios em SQL-92
Tipo
|
Descrição |
Char(n) ou character |
String
de tamanho fixo n |
Varchar(n) |
String
de tamanho variável com tamanho máximo n |
int ou integer |
Número
inteiro |
Smallint |
Inteiro
pequeno |
Number(p,d) |
Número
ponto fixo xom precisão definida pelo usuário. Com p
indicando o número de dígitos e d indicando dos p dígitos os que estão à
direita do ponto decimal |
Date |
Data: mm/dd/aaaa |
Obs.:
O padrão SQL-89 não contém varchar, date e time.
O
padrão SQL-92 permite a criação de domínios:
create domain nomePessoa char(20)
·
Os comandos
SQL para definição de dados são:
-
CREATE
-
DROP
-
ALTER
·
SQL-2 contém o
conceito de esquema (permite agrupar tabelas, triggers,
visões, autorização, etc, que pertencem a uma mesma
aplicação de BD)
·
Um esquema é identificado
pelo nome e
possui uma identificação que indica quem é o dono do esquema.
CREATE SCHEMA CONTABILIDADE
AUTHORIZATION csbaptista;
·
CREATE TABLE:
especifica uma nova tabela (relação), dando o seu nome e especificando as colunas(atributos) (cada uma com seu nome, tipo e
restrições)
Sintaxe:
CREATE TABLE tabela_base
(colunas tabela_base + constraints)
As definições das colunas têm o
seguinte formato:
coluna tipo_dado [NOT NULL [UNIQUE]]
Onde:
coluna:
nome do atributo que está sendo definido
tipo_dado: domínio do atributo
NOT NULL: expressa que o atributo
não pode receber valores nulos
UNIQUE: indica que o atributo tem
valor único na tabela. Qualquer tentativa de se introduzir uma linha na tabela
contendo um valor igual ao do atributo será rejeitada. Serve para indicar
chaves secundárias
·
Constraints (Restrições de Integridade e de domínio)
-
Integridade de Chave:
PRIMARY KEY(atributos_chave)
-
Integridade Referencial:
FOREIGN KEY (atributos) REFERENCE
tabela_base(atributos)
-
Integridade de Domínio:
CHECK(condição)
Ex.:
CREATE TABLE
empregado
( nome VARCHAR(15) NOT NULL,
matricula varchar(9) NOT NULL,
dataNasc DATE,
endereco VARCHAR(30),
sexo CHAR,
salario NUMBER(10,2),
supervisor
CHAR(9),
depto INT NOT
NULL,
PRIMARY KEY (matricula),
CHECK (salario >= 0),
FOREIGN KEY
(supervisor) REFERENCES
empregado(matricula),
FOREIGN KEY (depto) REFERENCES
departamento(codDep)
);
CREATE TABLE departamento
( nomeDep VARCHAR(15) NOT NULL,
codDep INT NOT
NULL,
gerente CHAR(9) NOT NULL,
dataInicioGer DATE,
PRIMARY KEY(codDep),
UNIQUE (nomeDep),
FOREIGN KEY (gerente) REFERENCES
empregado(matricula)
);
Exercício: Defina as
tabelas abaixo usando SQL
Fornecedor
(codigo, nome, cidade),
Venda(codForn, codPeca, quantidade, data) e
Peca(codPeca, Nome, descricao)
·
Chave
estrangeira: como vimos é definida com a cláusula FOREIGN KEY. Alguns SGBDs permitem que se use uma
notação abreviada para chave estrangeira quando esta é formada por um único
atributo
Ex.:
CREATE TABLE Empregado
(
matricula
CHAR(9) NOT NULL,
nome VARCHAR(15) NOT NULL,
…
supervisor
CHAR(9) REFERENCES
Empregado(matricula),
codDep
INT NOT NULL REFERENCES
Departamento(codigo),
…
);
-
Uma cláusula FOREIGN
KEY inclui regras de remoção/atualização:
FOREIGN KEY (coluna)
REFERENCES tabela
[ON DELETE {RESTRICT|CASCADE|SET
NULL| SET DEFAULT}]
[ON UPDATE {RESTRICT|CASCADE|SET
NULL| SET DEFAULT}]
Supondo
que T2 tem uma chave estrangeira para T1, vejamos as cláusulas ON DELETE e ON UPDATE
ON
DELETE:
-
RESTRICT:
(default) significa que uma tentativa de se remover uma linha de T1 falhará se
alguma linha em T2 combina com a chave
-
CASCADE:
remoção de uma linha de T1 implica em remoção de todas as linhas de T2 que
combina com a chave de T1
-
SET NULL:
remoção de T1 implica
-
SET DEFAULT:
remoção de linha em T1 implica em colocar valores DEFAULT nos atributos da
chave estrangeira de cada linha de T2 que combina.
ON UPDATE:
-
RESTRICT:
(default) update de um atributo de T1 falha se
existem linhas em T2 combinando
-
CASCADE: update de atributo em T1 implica que linhas que combinam em
T2 também serão atualizadas
-
SET NULL: update de T1 implica que valores da chave estrangeira em T2
nas linhas que combinam são postos par NULL.
-
SET CASCADE: update de T1 implica que valores da chave estrangeira de T2
nas linhas que combinam terão valores default aplicados.
·
As restrições
de integridade podem ter um nome e serem especificadas com a cláusula CONSTRAINT.Isto permite que possamos no futuro eliminar
(DROP) ou alterar (ALTER) o constraint.
O exemplo a seguir
mostra o uso de CONSTRAINT, DEFAULT, ON DELETE e ON UPDATE
Ex.:
CREATE TABLE empregado
(
…
depto INT NOT NULL DEFAULT 1,
CONSTRAINT empCP
PRIMARY
KEY(matricula),
CONSTRAINT empSuperCE
FOREIGN
KEY(supervisor) REFERENCES
empregado(matricula) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT deptoCE
FOREIGN KEY (depto)
REFERENCES
departamento(codigo) ONDELETE SET DEFAULT
ON UPDATE
CASCADE
);
·
ALTER TABLE:
permite que se altere os atributos de uma determinada
tabela ou que se adicione novos atributs (evolução de
esquemas). Os novos atributos terão valores nulos em todas as linhas.
Ao incluirmos uma
coluna devemos especificar o seu tipo de dado, não podendo esta coluna ser NOT
NULL.
Sintaxe: ALTER TABLE tabela_base
ADD atributo tipo_dado
ALTER TABLE tabela_base
MODIFY atributo tipo_dado;
Ex.: ALTER TABLE Peca
ADD espessura INT
Ex. ALTER
TABLE supplier
MODIFY supplier_name varchar2(100)
not null;
Podemos remover um
atributo usando a
sintaxe
ALTER TABLE tabela_base
DROP atributo [CASCADE|RESTRICT]
CASCADE: remove todas
as restrições relativas ao atributo e visões que contêm o atributo
RESTRICT: não premite a remoção do atributo se este é usado numa visão ou
como chave estrangeira numa outra tabela
Ex,:
ALTER TABLE empregado DROP endereco
CASCADE;
ALTER TABLE departmento
ALTER gerente DROP
DEFAULT
ALTER TABLE departamento ALTER gerente SET
DEFAULT “
ALTER TABLE empregado
DROP CONSTRAINT empsuperCE CASCADE;
ALTER TABLE empregado
ADD CONSTRAINT empsuperCE FOREIGN KEY
(supervisor)
REFERENCES empregado(matricula)
DROP TABLE: remove
uma tabela-base do BD. Remove tanto os dados quanto a definição da tabela
Sintaxe: DROP TABLE Peca
2. SQL – DML
·
Esquemas do BD
Empresa:
Empregado(matricula,
nome, endereco, salario,
supervisor, depto)
Departamento(coddep, nome, gerente, dataini)
Projeto(codproj, nome, local, depart)
Alocacao(matric,codigop, horas)
·
SQL interativo
As operações de
manipulação sem cursor são:
SELECT, INSERT, UPDATE, DELETE
O comando Select:
A forma básica do comando Select é:
SELECT <lista atributos>
FROM <lista
tabelas>
WHERE <condição>
Exemplos:
Q1. Obtenha o salário
de José
Select salario
From Empregado
Where nome = ‘José’
Q2. Selecione o nome e
o endereço de todos os empregados que trabalham no departamento de produção
Select e.nome, e.endereco
From empregado e, departamento d
Where d.nome =
‘Produção’ and d.coddep =
e.depto
Q.3 Para cada projeto
em ‘Fortaleza’, liste o código do projeto, o departamento que controla o
projeto e o nome do gerente com endereço e salário
Select p.codproj, d.nome, e.nome, e.endereco,
e.salario
From Projeto p, Departamento d, Empregado e
Where p.depart = d.coddep
and d.gerente = e.matricula
and
p.local = ‘Fortaleza’
Q4. Para cada
empregado, recupere seu nome e o nome do seu supervisor
Select e.nome, s.nome
From Empregado e s
Where e.matricula = s.supervisor
Q5 Selecione a
matrícula de todos os empregados
Select matricula
From Empregados
Q6 Faça o produto cartesiano,
seguido de projeção de Empregados X Departamento retornando a matrícula do
empregado e o nome do departamento
SELECT matricula, d.nome
FROM Empregado,
Departamento d
Q7 Selecione todos os
atributos de todos os empregados do departamento d5
Select *
From Empregado
Where depto = ‘d5’
Q8. Selecione todos
os atributos de todos os empregados do departamento pessoal
Select *
From Empregado e, Departamento d
Where d.nome =
‘Pessoal’ and d.coddep = e.depto
Q9. Recupere os
salários de cada empregado
Select salario
From empregado
Algumas vezes surgem
duplicatas como resposta a uma query. Podemos
eliminá-las usando o comando DISTINCT na cláusula SELECT
Q10. Selecione os
diferentes salários pagos pela empresa aos empregados
Select distinct salario
From empregado
SQL implementa a operação UNIÃO da álgebra relacional. É
requerido que as relações sejam compatíveis de união
Q11. Liste todos os
nomes de projetos que envolvem o empregado ‘Silva’como
trabalhador ou como gerente do departamento que controla o projeto
(SELECT p.nome
FROM Projeto P,
Departamento d, Empregado e
WHERE d.coddep = p.depart
and
d.gerente = e.matricula and
e,nome
= ‘Silva’)
UNION
(SELECT p.nome
FROM Projeto p,
Alocação a, Empregado e
WHERE p.codproj = a.codproj and e.matricula = a.matricula and
e.nome = ‘Silva’)
Consultas Aninhadas:
consultas que possuem consultas completas dentro de sua cláusula where.
Motivação: Algumas queries requerem que valores do BD sejam buscados e então
usados numa condição.
Q12: A consulta Q11
poderia ser reescrita da seguinte forma:
SELECT DISTINCT nome
FROM Projeto
WHERE codigop in (SELECT codigop
FROM
Projeto p, Departamento d,
Empregado e
WHERE p.depart = d.coddep and
d.gerente = e.matricula and
e.nome = ‘Silva’)
or
codigop in (SELECT codigop
FROM Alocação a, Empregado e,
WHERE a.matric =
e.matricula
And e.nome = ‘Silva’)
Q13. Recupere o nome
de cada empregado que tem um dependente com o mesmo nome e mesmo sexo
SELECT e.nome
FROM empregado e
WHERE e.matricula in
(SELECT matricula
FROM
dependente
WHERE matricula = e.matricula
And e.nome = nome
And e.sexo = sexo)
Obs.: Veja que e.matricula, e.nome e e.sexo são atributos de empregado da
consulta externa.
Q14. Re-escrevendo a Q13 sem usar aninhamento
SELECT e.nome
FROM empregado e,
dependente d
WHERE e.matricula = d.matricula and
e.nome
= d.nome and e.sexo = d.sexo
·
A função EXISTS
É usada para
verificar se o resultado de uma consulta aninhada é vazia
ou não. É sempre usado em conjunto com um query
aninhada.
A conulsta
Q13 poderia ser:
SELECT e.nome
FROM empregado e
WHERE EXISTS (SELECT *
FROM
dependente
Where
e.matricula = matricula
e.nome = nome and e.sexo = sexo)
Podemos usar o NOT
EXISTS(Q)
Q.15 Recupere os
nomes dos empregados que não têm dependentes
SELECT e.nome
FROM empregado e
WHERE NOT EXISTS (SELECT *
FROM
dependente
WHERE e.matricula = matricula)
Podemos usar um
conjunto de valores explícitos:
Q16. Selecione a
matricula de todos os empregados que trabalham nos projetos 10, 20 ou 30
SELECT DISTINCT matric
FROM alocacao
WHERE codigop in (10,20,30)
Podemos verificar
valores nulos através de IS NULL e IS NOT NULL:
Q17. Selecione os
nomes de todos os empregados que não têm supervisores
SELECT nome
FROM empregado
WHERE supervisor IS NULL
·
Funções
SQL fornece 5 funções embutidas:
COUNT: retorna o
número de tuplas ou valores especificados numa query
SUM: retorna a soma os valores de uma coluna
AVG: retorna a média
dos valores de uma coluna
MAX: retorna o
maior valor de uma coluna
MIN: identifica o
menor valor de uma coluna
Estas funções só
podem ser usadas numa cláusula select ou numa
cláusula HAVING (a ser vista depois)
Q18. Encontre o total
de salários, o maior saláriom o menor salário e a mádia salarial da relação empregados
SELECT SUM(salario), MAX(salario), MIN(salario), AVG(salario)
FROM Empregado
Q19. Encontre o maior
e menor salário do departamento de Produção
SELECT MAX(salario), MIN(salario)
FROM Empregado e,
Departamento d
WHERE e.depto = d.coddep
and d.nome = ‘Produção’
Q.20 Obtenha o número
de empregados da empresa
SELECT COUNT(*)
FROM empregado
Q.21 Obter o número
de salários distintos do departamento de Contabilidade
SELECT COUNT(DISTINCT
salario)
FROM empregado e, departamento d
WHERE (e.depto
= d.coddep and d.nome = ‘Contabilidade’)
O que aconteceria se
escrevêssemos COUNT(salario)
ao invés de COUNT(DISTINCT salario))?
Q.22 Obter o nome dos
empregados que tenham 2 ou mais dependentes
SELECT e.nome
FROM empregado e
WHERE (SELECT COUNT(*)
FROM Dependente d
WHERE e.matricula = d.matricula) >=
2
·
Cláusula GROUP BY, HAVING
Usadas para lidar com
grupos.
Q23. Para cada departamento,
obter o código do departamento, o número de empregados e a média salarial
SELECT depto, COUNT(*),
AVG(salario)
FROM Empregado
GROUP BY depto
ð
as tuplas de empregados são
separadas em grupos (departamento) e as funções COUNT e AVG são aplicadas a
cada grupo separadamente.
Q24. Para cada
projeto, obter o código do projeto, seu nome e o número de empregados que
trabalham naquele projeto
SELECT codigop, nome,
COUNT(*)
FROM Projeto p, Alocacao
a
WHERE p.codproj = a.codigop
GROUP BY p.codproj, p.nome
ð
o agrupamento e as funções são aplicadas após a junção.
·
HAVING: usada em conjunto com GROUP BY para permitir a inclusão de
condições nos grupos.
Q.25. Para cada
projeto que possui mais de 2 empregados trabalhando,
obter o código do projeto, nome do projeto e número de empregados que trabalha
neste projeto
SELECT p.codproj, p.nome,
COUNT(*)
FROM Projeto p, Alocacao
a
WHERE p.codproj = a.codigop
GROUP BY codigop, nome
HAVING COUNT(*) > 2
Uma query é avaliada primeiro aplicando a cláusula
WHERE e depois GROUP BY HAVING
·
Operadores de
Comparação e Aritméticos
BETWEEN:
Sintaxe:
expressão [NOT] BETWEEN expressão
AND expressão
Ex.: y BETWEEN x AND
Z equivale a x <= y <= z
Q.26 Selecione os
nomes dos empregados que ganham mais de 1000 e menos de 2000 reais
SELECT nome
FROM Empregado
WHERE salario BETWEEN 1000 AND 2000
LIKE:
Permite comparações de substrings. Usa dois caracteres reservados ‘%’ (substitui
um número arbitrário de caracteres) e ‘-‘ (substitui um único caracter).
Q.27 Obter os nomes
de empregados cujos endereços estão em Natal, RN
SELECT nome
FROM empregado
WHERE endereco LIKE ‘%Natal,RN%’
Q28 Obter todos os
empregados que nasceram durante a década de 50
SELECT nome
FROM empregado
WHERE aniversário LIKE
‘_ _ 5 _’
Oeradores aritméticos: +, -, *, / podem ser aplicados a valores numéricos
·
Ordenação
O operador ORDER BY permite ordenar o resultado de
uma query por um ou mais atributos.
Q.29 Obter uma lista
de empregados e seus respectivos departamentos e projetos, listando ordenado
pelo nome do departamento
SELECT d.nome, e.nome, p.nome
FROM departamento d,
empregado e, projeto p
WHERE d.coddep = e.depto
and e.matricula = a.matricula
And a.codigop = p.codproj
ORDER BY d.nome, e.nome
ð
A ordem default
é ascendente (ASC) caso queiramos ordem decrescente usamos DESC
Ex. ORDER BY d.nome DESC, e.nome
ASC
·
O comando Insert
Usado para adicionar uma tupla a uma relação
Sintaxe:
INSERT INTO tabela [ (lista colunas)] fonte
Onde fonte pode ser uma especificação
de pesquisa (Select) ou uma cláusula VALUES da forma:
VALUES (lista de valores atômicos)
OBS.: Se o comando INSERT incluir
a cláusula VALUES então uma única tupla é inserida na
relação.
Ex. INSERT INTO Empregado(matricula, nome)
VALUES(9491,’Ana’)
Obs.: A inserção será rejeitada se
tentarmos omitir um atributo que não permite valores nulos (NOT NULL)
Ex.: INSERT INTO Empregado(nome, salario)
VALUES(‘Flávia’, 960)
Podemos inserir
várias tuplas numa relação através de uma query.
Ex. CREATE TABLE DEPTO_INFO
(nome
character(15),
numemp
integer,
totsal
real)
INSERT
INTODEPTO_INFO(nome, numemp,
totsal)
SELECT D.nome, COUNT(*), SUM(salario)
FROM
Departamento d, Empregado e
WHERE
d.coddep = e.depto
GROUP BY d.b=nome
·
O comando DELETE
Remove tuplas de uma relação
Sintaxe:
DELETE
FROM tabela
[WHERE condição]
Obs.: Se
omitirmos a cláusula WHERE, então o DELETE deve ser aplicado a todas as tuplas da relação. Porém, a relação permanece no BD como
uma relação vazia.
Ex.:
DELETE FROM Empregado
WHERE nome = ‘Silva’
DELETE FROM Empregado
WHERE matricula = 9401
DELETE
FROM Empregado
WHERE depto in (SELECT coddep
FROM
Departamento
WHERE
nome = ‘Pesquisa’)
DELETE FROM EMPREGADO
·
O comando UPDATE
Modifica o
valor de atributos de uma ou mais tuplas.
Sintaxe:
UPDATE tabela
SET lista_atributos
com atribuições de valores
[WHERE condição]
Obs.:
omitir a cláusula WHERE implica que o UPDATE deve ser aplicado a todas as tuplas da relação
Ex.
Modifique o nome do Departamento de Computação para Departamento de Informática
UPDATE
Departamento
SET nome =
‘Informatica’
WHERE nome
= ‘Computação’
Ex. Dê um
aumento de 10% a todos os empregados do departamento de Pesquisa
UPDATE Empregado
SET salario = salario * 1.1
WHERE depto in ( SELECT coddep
FROM
Departamento
WHERE
nome = ‘Pequisa’)
·
Especificando
índices em SQL
-
SQL possui comandos para criar e remover índices em
atributos de relações base (faz parte da SQL DDL)
-
Um índice é uma estrutura de acesso físico que é
especificado em um ou mais atributos de um arquivo, permitindo um acesso mais
eficiente aos dados.
-
Se os atributos usados nas condições de seleção e junção
de uma query são indexados, o tempo de execução da query é melhorado.
Ex.: Criar um índice no atributo
nome da relação Empregado.
CREATE INDEX nome-índice
ON Empregado(nome)
-
O default é ordem ascendente, se quisermos uma ordem
descendente adicionamos a palavra chave DESC depois do nome do atributo
-
Para especificar a restrição de chave usamos a
palavra UNIQUE
CREATE UNIQUE INDEX matrIndex
ON
Empregado(matricula)
Para elimiarmos
um índice usamos o comando DROP
Ex. DROP INDEX nome-indice
Não é desejável que todos os
usuários tenham acesso ao esquema conceitual/ => visões precisam ser
definidas.
Visão: é uma relação virtual que
não faz parte do esquema conceitual mas que é visível
a um grupo de usuários.
A visão é definida por uma DDL e é
computada cada vez que são realizadas consultas aos dados daquela visão.
O catálogo do SGBD é o repositório
que armazena as definições das visões.
Uma visão possui nome, uma lista
de atributos e uma query que computa a visão.
Uma visão é uma tabela virtual que
é definida a partir de outras tabelas, contendo sempre os dados atualizados.
4.3. Especificação de
Visão em SQL
Sintaxe:
CREATE VIEW nomeVisão
AS expressão_De_consulta
Ex.: CREATE VIEW Alocaçao1(nomeE, nomeP,
Horas)
AS
SELECT E.nome, P.nome, horas
FROM Empregado E, Projeto P, Alocação A
WHERE
E.matricula = A.matricula
and
P.codproj=A.codigop
Cria uma relação virtual Alocacao1( nomeE, nomeP,
horas)
Ex.2: CREATE VIEW InfoDepto (nomeD, numEmp, totSal)
AS SELECT d.nome, COUNT(*), SUM(salario)
FROM
Departamento d, Empregado e
WHERE
d.coddep = e.depto
GROUP BY d.nome
InfoDepto( nomeD, numEmp, totSal)
OBS.: Se não especificarmos os
nomes dos atributos de visão, estes terão o mesmo nome das tabelas baase
Podemos escrever consultas na
visão definida.
Ex.: Obter o nome dos empregados
que trabalham no projeto ‘Informatização’
SELECT nomeE
FROM Alocacao1
WHERE nomeP = ‘Informatizacao’
·
Eliminando uma visão
Usamos o comando DROP VIEW
Sintaxe: DROP VIEW nomeVisão
Ex.: DROP VIEW Alocacao1;
DROP VIEW InfoDepto
·
Atualização de Visões
Visões são úteis em consultas, mas
existem restrições em relação a atualizações (é ainda pesquisa corrente).
Para ilustrarmos alguns problemas,
considere a visão Alocacao1 e suponha que queiramos atualizar o atributo nomeP da tupla
que contém ‘João’ de ‘ProdutoX’ para ‘Produto Y’.
Esta atualizaÇão de visão e’expressa da seguinte forma:
UPDATE Alocacao1
SET pNome = ‘ProdutoY’
WHERE nomeE = ‘João’ and nomeP = ‘ProdutoX’
Este update
pode ser mapeado em vários updates nas relações base.
Dois possíveis updates, com resultados diferentes
são:
a)
UPDATE Alocacao
SET codigop = ( SELECT codproj FROM Projeto
WHERE nome
= ‘ProdutoY’)
WHERE
matricula = (SELECT matricula FROM
Empregado WHERE nome = ‘João’)
And
codigop = (SELECT codproj FROM Projeto
WHERE nome = ‘ProdutoX’)
b)
UPDATE Projeto
SET nome =
‘ProdutoX’
WHERE nome
= ‘ProdutoY’
ð
como o SGBD
vai escolher qual update computar?
Alguns updates
de visões não fazem sentido para relação base.
Ex.: UPDATE InfoDepto
SET totsal
= 10.000
WHERE nomed = ‘Pesquisa’
Obs.:
1 –Uma
visão definida numa única tabela é atualizável se os atributos da visão contêm
a chave primária.
2- Visões definidas sobre
múltiplas tabelas usando junção geralmente não são atualizáveis
3- Visões usando funções de
agrupamento e agregados não são atualizáveis.
3.4
Valores Nulos
Interpretação de um valor nulo:
-
o atributo
não se aplica a tupla
-
o valor do
atributo para esta tupla é desconhecido
-
o valor é
conhecido, mas está ausente (não foi posto ainda)
Problemas com valores nulos:
-
problemas com
junções (informações são perdidas)
-
problemas com
funções tipo SUM, AVG, etc
Ex.: Sejam as
tabelas Empregado e Departamento
Empregado
Matricula |
Nome |
Salário |
Depto |
100 |
José |
1000 |
D1 |
200 |
Maria |
860 |
D2 |
300 |
Ana |
3020 |
D1 |
400 |
Breno |
2000 |
Null |
500 |
Márcia |
1500 |
null |
Departamento
Coddep |
Nomed |
Matrger |
D1 |
Pesquisa |
300 |
D2 |
Pessoal |
200 |
Se fizermos a consulta: obter uma
lista (nomee, nomed) de
todos os empregados, então os empregados Breno e Márcia seriam omitidos =>
Perda de Informação!!!!