Banco de Dados - SQL

Banco de Dados - SQL
Profa. Flávia Cristina Bernardini
O que é?


SQL = DDL + DML
Structured Query Language = Data
Definition Language + Data Manipulation
Language
◦ comandos para definir, modificar e remover
relações (tabelas), além de criar e remover
índices.

Implementa operadores de AR + as
seguintes operações:
◦ Inserção, Atualização e Remoção
◦ CRUD (CReate, Update, Delete)
Composição da SQL

DML embutida
◦ pode ser utilizada a partir de linguagens
de programação de propósito geral

Definição de visões
◦ SQL DDL inclui comandos para a criação
e a remoção de visões

Restrições de integridade
◦ SQL DDL possui comandos para a
especificação de restrições de integridade
Composição da SQL

Autorização
◦ SQL DDL inclui comandos para a
especificação de direitos de acesso a
relações e visões

Gerenciamento de transações
◦ introduz comandos para a especificação
do início e do fim das transações

Recuperação de falhas
◦ introduz comandos para utilização do
arquivo de log
Versões
Ansi SQL
 SQL 92 (SQL 2)
 SQL 99 (SQL 3)

◦ Características Objeto-Relacionais e
outras capacidades
◦ Será o alvo do nosso estudo
SQL DDL

CREATE DATABASE | SCHEMA
◦ cria um esquema de BD relacional

DROP DATABASE | SCHEMA
◦ remove um esquema de BD relacional
CREATE DATABASE

Cria um esquema de BD relacional
◦ agrupa tabelas/comandos que pertencem
à aplicação
◦ identifica o proprietário do esquema
◦ esquema inicial não possui tabelas/dados
CREATE {DATABASE | SCHEMA} nome
[USER `username` [PASSWORD `password`] ]
... ;
DROP DATABASE

Remove um esquema de BD
relacional
◦ tabelas/dados
◦ índices
◦ arquivos de log

Usuários autorizados
◦ proprietário do banco de dados
◦ DBA
DROP DATABASE

CASCADE
◦ remove um esquema de BD, incluindo
todas as suas tabelas e os seus outros
elementos

RESTRICT
◦ remove um esquema de BD somente se
não existirem elementos definidos para
esse esquema
SQL DDL

CREATE TABLE
◦ cria uma nova tabela (relação) no BD
nova tabela não possui dados

DROP TABLE
◦ remove uma tabela e sua instância do BD

ALTER TABLE
◦ altera a estrutura de uma tabela já
existente no BD
CREATE TABLE
Cria uma nova tabela (relação)
 Cria os atributos da nova tabela, com

◦ nome do atributo: Ai (1 <= i <= n)
◦ tipo de dado (domínio do atributo): Di
◦ restrições que atuam no atributo: Ri
CREATE TABLE nome_tabela (
A1 D1 R1,
A2 D2 R2,
...
An Dn Rn ) ;
Exemplos de Tipos de Dados

Numéricos
◦ Integer, float, ...

Hora/Data
◦ Date (YYYY-MM-DD), time (HH-MM-SS),
...
Strings
 Etc.

Restrições de Integridade

Valor nulo
◦ representado por NULL
◦ membro de todos os domínios

Restrição NOT NULL
◦ especificada quando NULL não é
permitido
◦ proíbe que o atributo receba valor nulo
Restrições de Integridade

Cláusula PRIMARY KEY
◦ identifica os atributos que formam a
chave primária
 NOT NULL
◦ sintaxe
PRIMARY KEY (atributo1, atributo2, ...,
atributoX)

Cláusula UNIQUE
◦ não permite valores duplicados para um
atributo
Restrições de Integridade

Cláusula DEFAULT
◦ associa um valor default para um atributo,
caso nenhum outro valor seja
especificado

Cláusula CHECK
◦ especifica um predicado que precisa ser
satisfeito por todas as tuplas de uma
relação
◦ exemplos
 saldo int CHECK (saldo >= 0)
 nível char(15) CHECK (nível IN
Restrições de Integridade

Integridade referencial
◦ dependência existente entre a chave
estrangeira de uma relação R1 (referência) e
a chave primária da relação referenciada R2
(relação referida).
◦ problemas
 atualização ou exclusão de elementos da chave
primária sem fazer um ajuste coordenado nas
chaves estrangeiras:
 Excluir tupla de Empregado que é gerente de
Departamento.
 inclusão ou alteração de valores não nulos na
chave estrangeira de R1 que não existam na chave
primária de R2
Restrições de Integridade

Cláusula FOREIGN KEY
◦ características
 elimina a possibilidade de violação da
integridade referencial
 reflete nas chaves estrangeiras todas as
alterações na chave primária
◦ sintaxe
FOREIGN KEY (atributos)
REFERENCES nome_relação (atributos)
[ON UPDATE [NO ACTION | CASCADE | SET NULL | SET
DEFAULT]]
[ON DELETE [NO ACTION | CASCADE | SET NULL | SET
DEFAULT]]
DROP TABLE

Remove uma tabela existente do BD
◦ dados
◦ índices, etc.

Usuários autorizados
◦ proprietário do banco de dados
◦ DBA
DROP TABLE nome_tabela ;
ALTER TABLE

Altera o esquema de uma tabela do
BD
colunas ou
◦ adiciona
◦ remove
◦ altera
restrições de
integridade
ALTER TABLE nome_tabela;
Exemplos: ALTER TABLE

inclui novas colunas na tabela
ALTER TABLE nome_tabela
ADD (A1 D1 R1),
...
ADD (An Dn Rn)

elimina uma coluna já existente da
tabela
ALTER TABLE nome_tabela DROP A1
Exemplos: ALTER TABLE

modifica o nome de uma coluna
existente de A1 para A2
ALTER TABLE nome_tabela ALTER [COLUMN]
A1
TO A2

modifica o tipo de dado de uma
coluna
ALTER TABLE nome_tabela
ALTER [COLUMN] A1 TYPE INT
SQL DDL

CREATE DOMAIN
◦ cria um domínio para um tipo de dados

DROP DOMAIN
◦ remove um domínio existente do BD

ALTER DOMAIN
◦ altera a definição de domínio
CREATE DOMAIN

Cria um domínio para um tipo de
dados
◦ restrições de integridade

Característica
◦ a definição do domínio é global ao BD
CREATE DOMAIN nome_domínio [AS] tipo_dado
[DEFAULT ... ]
[NOT NULL]
[CHECK ...]
... ;
DROP DOMAIN

Remove um domínio existente do BD
◦ falha caso o domínio esteja definindo o
tipo de dado de alguma coluna

Usuários autorizados
◦ proprietário do banco de dados
◦ DBA
DROP DOMAIN nome_domínio ;
ALTER DOMAIN

Altera um domínio existente do BD
◦ remove ou define restrições de
integridade
ALTER DOMAIN nome_domínio... ;
Exemplo
Exemplo ...
CREATE DATABASE loja_vinhos;
CREATE TABLE região
(
região_id int NOT NULL,
nome_região varchar(100) NOT NULL,
mapa_região blob,
descrição_região blob,
PRIMARY KEY (região_id),
);
* BLOB: Binary Long Objects (para armazenar áudio e
vídeo)
Exemplo ...
CREATE TABLE vinícola
(
vinícola_id int NOT NULL,
nome_vinícola varchar(100) NOT NULL,
descrição_vinícola blob,
fone_vinícola varchar(15),
fax_vinícola varchar(15),
região_id int DEFAULT ‘0’ NOT NULL,
PRIMARY KEY (vinícola_id),
FOREIGN KEY (região_id)
REFERENCES região (região_id)
ON UPDATE SET DEFAULT,
ON DELETE SET DEFAULT,
);
Exemplo
CREATE TABLE vinho
(
vinho_id int NOT NULL,
nome_vinho varchar(50) DEFAULT ‘ ’ NOT NULL,
tipo_vinho varchar(10) DEFAULT ‘ ’ NOT NULL,
ano_vinho int DEFAULT ‘0’ NOT NULL,
descrição_vinho blob,
vinícola_id int DEFAULT ‘0’ NOT NULL,
PRIMARY KEY (vinho_id),
FOREIGN KEY (vinícola_id)
REFERENCES vinícola (vinícola_id),
ON UPDATE CASCADE,
ON DELETE CASCADE,
);
SQL DML

SELECT ... FROM ... WHERE ...
◦ lista atributos de uma ou mais tabelas de
acordo com alguma condição

INSERT INTO ...
◦ insere dados em uma tabela

DELETE FROM ... WHERE ...
◦ remove dados de tabelas já existentes

UPDATE ... SET ... WHERE ...
◦ altera dados específicos de uma tabela
SELECT
SELECT <lista de atributos>
FROM <lista de tabelas>
[ WHERE predicado/condição ]
[ GROUP BY <atributos de agrupamento> ]
[ HAVING <condição para agrupamento> ]
[ ORDER BY <lista de atributos> ] ;
SELECT

Cláusula SELECT (lista de atributos)
◦ lista os atributos cujos valores serão
recuperados.

Cláusula FROM (lista de tabelas)
◦ especifica as relações necessárias para o
processamento da consulta.

Cláusula WHERE (condição)
◦ especifica as condições para a seleção
das tuplas a serem recuperadas.
◦ pode ser omitida.
Exemplo 1:

SELECT datanasc, endereco
FROM Empregado
WHERE Pnome=Flavia AND
Unome=Bernardini
SELECT

Resultado de uma consulta
◦ ordem de apresentação dos atributos
 ordem dos atributos na cláusula SELECT
◦ ordem de apresentação dos dados (parte
das tuplas)
 ordem ascendente ou descendente de acordo com
a cláusula ORDER BY
 sem ordenação
◦ duas ou mais tuplas podem possuir valores
idênticos de atributos
 para eliminação de tuplas duplicadas: SELECT
DISTINCT
Exemplo 2:
Select model
From PC
Where speed > 1000 AND rd = ’16xDVD’;
Select model: atributos selecionados (π)
From PC: tabelas afetadas
Where ...: condição de filtro (σ), que é
opcional
Expressão em AR:
πmodel(σspeed>1000 AND RD=’16xDVD’(PC))
Projeção





Lista de atributos do select
Podem ser renomeados (ρ em AR)
Podem conter expressões e constantes
Curinga ‘*’ lista todos os atributos
Pode conter o modificador DISTINCT
Select DISTINCT ram AS memória, price*1.2,
‘reais’ AS moeda
From ...
Where ...;
Seleção
Conteúdo do where
 Pode conter qualquer expressão
condicional usual de Ling’s de
Programação

Select *
From PC
Where speed > 100 AND hd <> 40 AND
NOT (ram < 512);
Cláusula WHERE

Operadores de comparação
igual a
=
diferente de
<>
maior que
>
maior ou igual
a
>=
menor que
<
menor ou igual
a
<=
entre dois
valores
BETWEEN ...
AND ...
de cadeias de
caracteres
LIKE ou
NOT LIKE
Strings
Representadas entre ‘ ’
Podem ter tamanho fixo ou variável (CHAR
e VARCHAR)
 Podem ser comparadas lexicograficamente
(=, <, >, ...)
 Operador LIKE: compara strings baseadas
num padrão:


Select model
From Printer
Where type LIKE ‘las__’ OR type LIKE
‘%jet%’;
Strings

Operadores de comparação de
cadeias de caracteres
◦ % (porcentagem): substitui qualquer
string
◦ _ (underscore): substitui qualquer
caractere

Característica
◦ operadores sensíveis ao caso
 letras maiúsculas são consideradas diferentes
de letras minúsculas
Exemplo
Cláusula WHERE

Exemplos
◦ WHERE nome_região LIKE ‘Mar%’
 qualquer string que se inicie com ‘Mar’
◦ WHERE nome_região LIKE ‘Mar_’
 qualquer string de 4 caracteres que se inicie
com ‘Mar’
Exemplos

SELECT *
FROM região;

SELECT região_id, nome_região
FROM região
WHERE nome_região LIKE ‘M%’ AND
região_id >= 3 AND
mapa_região IS NOT NULL;
Data e Hora

Implementações de SQL usualmente
suportam tipos específicos para data
(DATE) e hora (TIME)
◦ Exemplo: ‘1975-07-07’ e ’20:05:00.5’

Timestamp é um tipo comum que
combina data e hora
◦ Exemplo: ‘1975-07-07 20:05:00’
Null
Valor especial em SQL
 Entretanto, não há como utilizá-los
explicitamente em expressões SQL (um
teste, por exemplo)
 Em clásulas Where, precisamos nos
preparar para a ocorrência de valores
nulos:

◦ Operações aritméticas envolvendo NULL
retornam NULL
◦ Operações de comparação envolvendo NULL
retornam UNKNOWN (terceiro valor-verdade)
Unknown
Terceiro valor-verdade
 Pode ser interpretado como ½, com
TRUE=1 e FALSE=0
 Fórmulas

◦ A AND B = MIN (A, B)
◦ A OR B = MAX (A, B)
◦ NOT A = |1 – A|

Caso atípico:
Select * From Laptop
Where screen > 14.0 OR screen <= 14.0;
Operações sobre conjuntos

SQL
Álgebra Relacional
UNION
União
INTERSECT
Intersecção
MINUS
Diferença
Observações
◦ as relações participantes das operações
precisam ser compatíveis.
Exemplo

Liste os anos de fabricação dos vinhos
tintos e brancos:
SELECT ano_vinho
FROM vinho
WHERE tipo_vinho = ‘tinto’
UNION ALL
SELECT ano_vinho
FROM vinho
WHERE tipo_vinho = ‘branco’;

Modificador ALL considera duplicatas
Exemplo
(Select model
From Product
Where maker = ‘A’) UNION
(Select model
From PC
Where price < 1000)
Junção

Usar SELECT e WHERE
◦ especificam atributos com mesmo nome
usando nomes de tabelas e atributos
(nome_tabela.nome_atributo)

Cláusula FROM
◦ possui mais do que uma tabela

Cláusula WHERE
◦ inclui as condições de junção
Exemplo
Select maker
From Product, Printer
Where Product.model = Printer.model AND
Printer.type = ‘laser’;

From Product, Printer realiza o produto
cartesiano entre as 2 tabelas
Exemplo
SELECT nome_vinícola, nome_região
FROM vinícola, região
WHERE vinícola.região_id = região.região_id;
SELECT nome_vinícola, nome_região,
nome_vinho
FROM vinícola, região, vinho
WHERE vinícola.região_id = região.região_id
AND vinho.vinícola_id = vinícola.vinícola_id;
Variáveis de Tupla

Imagine uma consulta que retorne os
modelos de PC’s com a mesma
quantidade de hd
Select p1.model, p2.model
From PC p1, PC p2
Where p1.hd = p2.hd AND p1.model <
p2.model;
Sub-consultas Aninhadas

Produzindo um valor atômico:
Select maker
From Product
Where type = ‘printer’ AND
Product.model =
(Select model
From Printer
Where price < 500)
Condições envolvendo Relações
(1/2)

Operadores existentes: EXISTS, IN, ALL,
ANY e combinações com NOT
Select maker
From Product
Where type = ‘printer’ AND Product.model IN
(Select model
From Printer
Where price < 500)
Condições envolvendo Relações
(2/2)

Significado dos operadores:
◦
◦
◦
◦
EXISTS R: TRUE se R <> {}
s IN R: TRUE se s  R
s > ALL R: TRUE se s > t, t  R
s > ANY R: TRUE se s > t, t  R
O que esta consulta retorna?
Select p1.maker
From Product p1
Where p1.type = ‘printer’ AND EXISTS (
Select *
From Product p2
Where p2.model = p1.model AND
p2.maker <> p1.maker );
E esta?
Select l.model
From Laptop l
Where l.price < ANY (
Select p.price
From PC p);
Sub-consultas Relacionadas

Consultas que obrigam a avaliação de
consultas aninhadas diversas vezes
Select model
From Laptop l
Where price <= ANY
(Select price
From PC
Where l.hd >= hd);
Sub-consultas em Cláusulas
From
Select distinct p1.maker
From Product p1,
( Select *
From Printer
Where color = ‘true’ ) p2
Where p1.model = p2.model;
Expressões de Junção SQL
(Joins)

Sejam R e S relações (tabelas)
◦
◦
◦
◦
R cross join S; (produto cartesiano)
R join S on CONDIÇÃO; (junção theta)
R natural join S; (junção natural)
R natural {full | left | right} outer join (outer
join)
Cláusula ORDER BY

Ordena as tuplas resultantes de uma
consulta
◦ asc: ordem ascendente (padrão)
◦ desc: ordem descendente

Ordenação pode ser especificada em
vários atributos
◦ Ordenação referente ao primeiro atributo é
prioritária.
◦ Se houver valores repetidos, então é
utilizada a ordenação referente ao segundo
atributo, e assim por diante
Exemplo


Liste os dados das vinícolas e suas
regiões.
Ordene o resultado pela região da
vinícola em ordem ascendente.
SELECT *
FROM vinícola, região
WHERE vinícola.região_id =
região.região_id
ORDER BY nome_região asc
Agregação – Operadores


SUM, AVG, MIN, MAX e COUNT
Tipicamente utilizados com expressões
escalares com uma coluna numérica
Select AVG (price)
From Laptop;

Observações:
◦ Exceção é o COUNT (*), que conta o número de
tuplas de uma relação
◦ DISTINCT: não considera valores duplicados
◦ ALL: inclui valores duplicados

Características
◦ recebem uma coleção de valores como entrada;
◦ retornam um único valor.
Funções de Agregação
vinho (vinho_id, nome_vinho, tipo_vinho, preço,
vinícola_id)
vinho_id
nome_vinho tipo_vinho
preço
vinícola_id
10
Sta Carolina
Merlot
35,00
1
09
Sta Carolina
Carménere
36,00
1
05
Sta Helena
Cabernet
Sauvignon
50,00
2
15
Sta Helena
Cabernet
Blanc
53,00
2
27
Casillero del
Diablo
Cabernet
Ssuvignon
90,00
3
48
Casillero del
Diablo
Carménere
98,00
3
13
Reservado
Carménere
60,00
3
12
Reservado
Cabernet
Sauvignon
62,00
3
Exemplos
Quantos vinhos existem na relação
vinho?
 Quantos tipos de vinho diferentes
existem na relação vinho?
 Qual a média dos preços?
 Qual a soma dos preços?
 Qual o preço mais baixo?
 Qual o preço mais alto?

Agrupamento - Cláusula GROUP
BY

Funcionalidade:
◦ permite aplicar uma função de agregação
não somente a um conjunto de tuplas,
mas também a um grupo de um conjunto
de tuplas;

Grupo de um conjunto de tuplas:
◦ conjunto de tuplas que possuem o
mesmo valor para os atributos de
agrupamento;
Exemplo

Qual o preço mais alto e a média dos
preços por tipo de vinho?
SELECT tipo_vinho, MAX (preço), AVG
(preço)
FROM vinho
GROUP BY tipo_vinho
Exemplo
Select type, COUNT(DISTINCT maker)
From Product
Group By type;
Agrupamento
Para a consulta anterior:
Select type, COUNT(DISTINCT maker)
From Product
Group By type;


Como reformulá-la para retornar
produtos diferentes de PC?
Agrupamento
Select type, COUNT(DISTINCT maker)
From Product
Where type <> ‘PC’
Group By type;
Agrupamento
E para retornar apenas produtos com
número >= 3 fabricantes?
Cláusula HAVING

Funcionalidade:
◦ especificar uma condição de seleção para
grupos;

Resposta:
◦ recupera os valores para as funções
somente para aqueles grupos que
satisfazem à condição imposta na
cláusula HAVING;
Exemplo

Qual o preço mais alto e a média dos
preços por tipo de vinho, para médias
de preços superiores a R$200,00
SELECT tipo_vinho, MAX (preço), AVG
(preço)
FROM vinho
GROUP BY tipo_vinho
HAVING AVG (preço) > 200
Exemplo
Select type, COUNT(DISTINCT maker)
From Product
Group By type
HAVING COUNT(DISTINCT maker) >=
3;
Atualização em
Bancos de Dados

Inserção, Remoção e Alteração
Inserção:
INSERT INTO R (A1,...,An) VALUES (v1,...,vn);

Onde:
R: Relação
Ak: Atributos
vk: valores
INSERT INTO R VALUES (v1,...,vn);
 Usado quando há valores vk para todos os
atributos de R
◦ Ordem dos atributos precisa ser mantida
INSERT
INSERT INTO nome_tabela
SELECT ...
FROM ...
WHERE ... ;

Tuplas resultantes da cláusula
SELECT serão inseridas na tabela
nome_tabela
Atualização em
Bancos de Dados
Remoção
DELETE FROM R WHERE <condição>;
 Atualização
UPDATE R SET <atribuições> WHERE
<condição>;
 Exemplo:
UPDATE Laptop
SET price = price * 1.1
WHERE speed >= 600;

DELETE



Remove tuplas inteiras
Opera apenas em uma relação
Tuplas de mais de uma relação a serem
removidas:
◦ um comando DELETE para cada relação
* A remoção de uma tupla de uma relação
pode ser propagada para tuplas em
outras relações devido às restrições de
integridade referencial.
Exemplo
DELETE FROM vinícola
WHERE vinícola_id = 10;
◦ remove a tupla referente a vinícola_id =
10;
◦ tabela vinho (i.e., se CASCADE foi
especificada na cláusula ON DELETE p/
vinícola_id desta tabela)
DELETE FROM região
◦ remove todos os dados da tabela região
UPDATE

Opera apenas em uma relação
* A atualização da chave primária pode
ser propagada para tuplas em outras
relações devido às restrições de
integridade referencial
Exemplos

Alterar os anos de produção de vinhos
de 2005 para 2003
UPDATE vinho
SET ano_vinho = 2003
WHERE ano_vinho = 2005;

Suponha o atributo adicional preço na
tabela vinho. Aumentar os preços dos
vinhos em 10%.
UPDATE vinho SET preço = preço * 1.10;
Exemplo
UPDATE vinícola
SET vinícola_id = 10
WHERE vinícola_id = 2;

altera o valor de vinícola_id = 10 para
vinícola_id = 2
◦ tabela vinícola
◦ tabela vinho (i.e., se a opção CASCADE
foi especificada na cláusula ON UPDATE
do campo vinícola_id desta tabela)
Índices
São criados baseados em 1 ou + campos
 São estruturas de dados que tornam mais
eficientes as consultas sobre esse atributos
CREATE INDEX velocIndex ON PC(speed);
DROP INDEX velocIndex;
 2 fatores a se considerar:

◦ Tornam eficientes as consultas que utilizam este
atributos
◦ Tornam mais custosas as operações de
inserção, remoção e atualização
Visões
São relações abstratas (virtuais)
 CREATE TABLE cria relações que existem
fisicamente, enquanto que CREATE VIEW
cria visões, as quais não existem
fisicamente e tipicamente expressam o
resultado de um consulta
 Diferentemente do resultado das consultas,
visões podem ser consultadas como
tabelas (como se existissem fisicamente)

Exemplo de Definição e Uso de
Visão
CREATE VIEW Barganha(modelo, ram,
hd, preço) AS
SELECT model, ram, hd, price
FROM Laptop
WHERE price < 1300;
SELECT Min(preco)
FROM Barganha;
Modificação de Visões (Visões
Atualizáveis)
Nem sempre são possíveis
 A operação que mais restringe é a inclusão:

◦ Esta não pode infringir a integridade do banco
(os atributos não citados na inclusão precisam
poder admitir NULL ou valores padrão)
 Por exemplo, se o campo speed da relação Laptop não
admitisse NULL
◦ Uma tupla inserida numa visão precisa ser
inserida na tabela base e constar na visão
resultante
 No exemplo citado, não podemos inserir um laptop com
preço >= 1300
Modificação de Visões (Visões
Atualizáveis)
Remoções e atualizações numa visão
são traduzidas para operações
equivalentes na tabela base:
DELETE FROM Barganha
WHERE ram < 512;

DELETE FROM Laptop
WHERE ram < 512 AND price < 1300;
Modificação de Visões (Visões
Atualizáveis)
O comando DROP TABLE remove a
tabela base e inutiliza (e
eventualmente também remove) as
visões baseadas nesta tabela
 DROP VIEW remove apenas a visão,
ou seja, não remove as tuplas da
tabela base

Restrições (Constraints)
Uma forma de definição de elementos
ativos em Bancos de Dados
 Utilizados para garantir integridade do
sistema (tanto na visão do modelo
relacional quanto na de regras de
negócio)
 Um exemplos de restrições são as
chaves primárias e estrangeiras

Chave Primária
Exemplo:
CREATE TABLE PC (
model INTEGER PRIMARY KEY,
speed INTEGER,
... ); ou

CREATE TABLE PC (
model INTEGER,
speed INTEGER,
PRIMARY KEY (model), ...);
Campo UNIQUE
Além da chave primária, com o modificador
UNIQUE podemos garantir também que
outros campos tenham valores distintos
 Diferentemente de chave primária, um
campo UNIQUE permite valores NULL
CREATE TABLE Printer (
model INTEGER PRIMARY KEY,
color BOOLEAN,
type VARCHAR(50) UNIQUE, ...);

Chaves Estrangeiras

Possibilitam a associação entre tabelas
CREATE TABLE Aluno (
matr INTEGER,
nome VARCHAR (100) NOT NULL,
cr FLOAT,
codCurso INTEGER,
PRIMARY KEY (matr),
FOREIGN KEY (codCurso) REFERENCES
Curso(cod);
Manutenção da Integridade
Referencial

Na realização de modificações sobre
tuplas que são referenciadas em
outras tabelas (chaves estrangeiras),
as seguintes ações podem ser
definidas:
◦ Rejeição das modificações (operação
padrão em BDs)
◦ Modificações em cascata (altera também
as referências)
◦ Modificações gerando NULL (no caso de
remoções, é atribuído NULL à referência)
Sintaxe de Ações sobre Chaves
Estrangeiras
CREATE TABLE Aluno (
matr INTEGER,
nome VARCHAR (100) NOT NULL,
cr FLOAT,
codCurso INTEGER,
PRIMARY KEY (matr),
FOREIGN KEY (codCurso) REFERENCES
Curso(cod) ON (DELETE | UPDATE) (CASCADE |
SET NULL | REJECT) *;
* Sintaxe de expressões regulares: () encapsula
termos e | é um OU de termos
Asserções (Assertions)
Uma asserção é uma expressão em
SQL de valor booleano que precisa
ser verdadeira sempre
 Formato:
CREATE ASSERTION <nome> CHECK
<condição>;
DROP ASSERTION <nome>;
 Qualquer modificação que invalide
uma asserção será rejeitada

Asserções
Exemplo
CREATE ASSERTION crIncorreto
CHECK (
NOT EXISTS (
SELECT *
FROM Aluno a
WHERE a.cr > 0 AND NOT EXISTS (
SELECT *
FROM Historico h
Where a.mat = h.mat );
Gatilhos (Triggers)


Um gatilho é uma série de ações que são
associadas a eventos num BD, como inserções,
atualizações e remoções
Regras ECA (Evento – Condição – Ação)
CREATE TRIGGER Atualizar
AFTER INSERT ON Historico
REFERENCING NEW ROW AS NovaTupla
FOR EACH ROW BEGIN
UPDATE Aluno a
SET cr = (SELECT AVG(nota) FROM Historico
WHERE a.mat = NovaTupla.mat)
WHERE a.mat = NovaTupla.mat;
...
END;
Gatilhos

Opções:
◦
◦
◦
◦
◦
◦
◦
◦
◦
◦
AFTER / BEFORE / INSTEAD OF
INSERT / DELETE / UPDATE
WHEN (condição para disparo do gatilho)
BEGIN .. END; (+ de 1 expressão SQL)
OLD/NEW ROW AS (Atualização)
NEW ROW AS (Inserção)
OLD ROW AS (Remoção)
FOR EACH ROW / STATEMENT
OLD TABLE AS
NEW TABLE AS
Programação
com Banco de Dados

2 alternativas:
◦ Codificação utilizando uma biblioteca de
acesso ao banco de dados
 Exemplo: C, C++, ...
◦ Utilizar os recursos existentes na
linguagem (se for o caso) para acesso ao
banco de dados
 Java, PHP, ...
Procedimentos e
Funções Armazenados (Stored
Procedures)





São módulos (procedimentos ou funções) précompilados e armazenados no SGBD
O termo padrão em SQL é PSM (Persistent Stored
Modules)
Permitem o encapsulamento de definições do
esquema de um BD e melhoram o desempenho
Dispondo de recursos de linguagem de
programação (variáveis, condicionais, repetições,
etc), estes módulos podem realizar diversas
operações para uma única chamada
Estes procedimentos podem conter parâmetros
com tipos de SQL e modos de entrada (IN), saída
(OUT) e entrada juntamente com saída (INOUT)
Stored Procedures
Exemplo
CREATE PROCEDURE Bonifica (
IN n FLOAT,
IN m VARCHAR (20)
)
IF n <= 1 (
UPDATE Aluno
SET nota = nota + n
WHERE mat = m; );
Stored Procedures
Situações Úteis
Se um BD precisa ser chamado em vários
aplicativos, estes módulos podem ser
armazenados no servidor
 Do contrário, teríamos instruções SQL repetidas
entre estes aplicativos
 Observe os diagramas abaixo sem e com stored
procedures e imagine a modificação do esquema
da tabela Aluno

Transações


São uma unidade lógica de trabalho
Necessárias quando se deseja que
operações sejam executadas como uma
única, de forma indivisível
◦ Por exemplo, uma operação de transferência
bancária pode ser traduzida como uma operação
de débito e outra de crédito
Transações se justificam pelo fato de um
SGBD ser multiusuário e multitarefa
 Ou seja, vários processos pode estar
executando sobre a mesma base de dados
e inconsistências podem ocorrer

Transações
Outra situação que justifica o uso de
transações é na ocorrência de falhas
 O SGBD garante que, dada uma transação
com n operações, ou as n operações são
executadas ou nenhuma delas
 O estado consistente existente antes da
execução de uma transação deve existir
após a execução desta
 Com isso, numa falha (disco, quebra no
sistema, exceção detectada pela
transação, etc), o SGBD deve ser capaz de
se recuperar da situação errônea

Transações –
Pseudo-código
BEGIN TRANSACTION;
UPDATE ACC 123 { Saldo := Saldo - $100 };
IF ocorrer_algum_erro GO TO UNDO; END
IF;
UPDATE ACC 456 { Saldo := Saldo + $100 };
IF ocorrer_algum_erro GO TO UNDO; END
IF;
COMMIT; GO TO FINISH;
UNDO: ROLLBACK;
Transações – Pontos
Importantes








COMMIT/ROLLBACK implícito
Tratamento de mensagens
Log de recuperação
Atomicidade de instruções
A execução de um programa é uma
seqüência de transações
Transações aninhadas
Corretude
Múltiplas associações
Transações
Transações

Tipos de transações
◦ Read-only
◦ Writable

Propriedades de transações (ACID)
◦ Atomicidade: todas operações ou nenhuma
◦ Correção: somente geram estados válidos
◦ Isolamento: atualizações percebidas entre
diferentes transações somente após o commit
◦ Durabilidade: Atualizações depois do commit
persistem após possível queda do sistema
Transações

Recuperação do sistema
◦ Buffers de BDs
◦ BDs físicos
◦ Checkpoints
Consultas vistas
anteriormente
select th.nome, tm.nome
from TabHomem as th, TabMulher as tm
where th.mulher = tm.id;
Retornar os nomes dos casais
Consultas vistas
anteriormente
select distinct tm.cidade
from TabHomem as th, TabMulher as tm
where th.mulher = tm.id AND
tm.nome like ‘%Brunet%’
order by tm.cidade desc;
Retornar os nomes das cidades em ordem
decrescente que possuem casais cuja mulher
tem Brunet no nome
Consultas Complexas
Teste de Campos Nulos
select nome
from TabHomem as t
where t.mulher is NULL;
Retornar os nomes dos homens que
não são casados
Consultas Complexas
Teste de Pertinência
select tm.nome, tm.tel
from TabMulher as tm
where tm.cidade in (‘Petropolis’,
‘Teresopolis’, ‘Friburgo’);
Retornar os nomes e telefones das
mulheres que moram na região serrrana
Consultas Complexas
Consultas Aninhadas
select nome
from TabHomem
where nome in (
select th.nome
from TabHomem as th, TabMulher as tm
where th.mulher = tm.id AND
th.cidade = tm.cidade );
Retornar os nomes dos homens que são
casados e moram na mesma cidade que a
sua mulher
Consultas Complexas
Consultas Aninhadas
select t.nome, t.tel
from TabHomem as t
where (t.nome, t.tel) in (
select th.nome, th.tel
from TabHomem as th, TabMulher as tm
where th.mulher = tm.id AND
th.cidade = tm.cidade );
Retornar os nomes e telefones dos homens
que são casados e moram na mesma cidade
que a sua mulher
Consultas Complexas
Teste de Existência
select th.nome, th.tel
from TabHomem as th
where exists (
select *
from TabMulher as tm
where th.cidade = tm.cidade );
Retornar os nomes e telefones dos homens
moram na mesma cidade que alguma mulher
Consultas Complexas
Teste de Existência
select th.nome, th.tel
from TabHomem as th
where th.mulher is NULL AND
not exists (
select *
from TabMulher as tm
where th.cidade = tm.cidade );
Retornar os nomes e telefones dos homens
que são solteiros e moram numa cidade que
não tem mulheres
Consultas Complexas
Comparações Avançadas
select tm.nome, tm.tel
from TabMulher as tm
where tm.salario > ALL (
select th.salario
from TabHomem as th );
Retornar os nomes e telefones das
mulheres que possuem salários maiores
que todos os homens (mulheres
independentes)
Consultas Complexas
Variações de Pertinência
select tm.nome, tm.tel
from TabMulher as tm
where not exists (
( select th.id
from TabHomem as th
where th.cidade = tm.cidade )
except
( select th.id
from TabHomem as th
where th.mulher is not Null ) );
Retornar os nomes e telefones das mulheres que
moram numa cidade que não possua homens solteiros
Consultas Complexas
Variações de Consultas Aninhadas
select count(*)
from TabMulher as tm
where tm.salario >
( select avg(th.salario)
from TabHomem as th );
Retornar a quantidade mulheres que
possuem salário maior que a média
masculina
Consultas Complexas
Agrupamento
select tm.cidade, count(*)
from TabMulher as tm
group by tm.cidade;
Retornar a cidade e a respectiva
quantidade de mulheres
Consultas Complexas
Variações com Agrupamento
select tm.cidade, count(*)
from TabMulher as tm
where tm.salario >
( select avg(th.salario)
from TabHomem as th )
group by tm.cidade;
Retornar a cidade e a quantidade das
mulheres que possuem salário maior que a
média masculina
Junção de Tabelas
select tm.nome, tm.tel
from (TabMulher tm join TabHomem th on
id=mulher)
where tm.cidade=‘Petrópolis’;
Retornar nome e telefone das mulheres
casadas que moram em Petrópolis
Junção de Tabelas
Natural Join
select tm.nome, tm.tel
from TabMulher tm natural join
(TabHomem as th (idh, nome, tel, end,
id))
where tm.cidade=‘Petrópolis’;
Retornar nome e telefone das mulheres
casadas que moram em Petrópolis
(renomeia a tabela TabHomem para que
a junção natural possa ser executada)
Junção de Tabelas
Inner Join
select tm.nome, tm.tel
from TabMulher as tm, TabHomem as th
where tm.id=th.mulher;
Retornar nome e telefone das mulheres
casadas (Somente retorna a lista das
casadas)
Junção de Tabelas
Outer Join
select tm.nome, tm.tel
from TabMulher as tm left outer join
TabHomem as th on tm.id=th.mulher;
Retornar nome e telefone das mulheres
casadas (Forçar retorno da lista de todas
as mulheres)
Exercícios
Retornar uma tabela que contenha o
nome do professor, ano e a quantidade
de disciplinas ministradas neste ano.
 Retornar uma tabela que contenha o
nome do professor, ano, quantidade de
alunos que cursaram as disciplinas e a
quantidade de disciplinas ministradas
neste ano.
