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.

Ex. Range of t is Depósito

             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)

 

 

 

1 SQL – DDL

 

·         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 em colocar NULL em todos os atributos da chave estrangeira de cada linha de T2 que combina.

 

-          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 “333444555”

            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

 

Visões

 

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!!!!