Modelo Relacional + SQL
Prof. Daniel Callegari
Material elaborado pela Prof. Karin Becker
Modelo Relacional

Codd, E.F


“A relational model of data for large shared data banks”.
Communications of the ACM, 13(6):377-87, June 1970.
Objetivos

Independência de dados




ordem
indexação
caminhos de acesso
Reduzir insconsistências

regras de projeto (normalização)
Modelo Relacional

Informal

Uma base de dados vista pelos usuários como um
conjunto de tabelas (e nada além de tabelas)
COD
C1
C2
C3
C4

NOME CIDADE
João
Pedro
Paulo
Maria
POA
SP
SP
RJ
Formal



Aplicação de princípios matemáticos
Relação (subconjunto do produto cartesiano de
conjuntos)
Álgebra Relacional
Modelo Relacional : Formal
DOMÍNIO
NOME
COD
X
C4
C1
C3
C2
C5
RELAÇÃO
...
CIDADES
João Pedro
José
Maria
...
COD
NOME
CIDADE
C1
João
POA
C2
Pedro
SP
C3
Paulo
SP
C4
Maria
RJ
X
RJ
SP
POA
NY
Londres
...
TUPLA
Definições

Domínio


conjunto de valores atômicos
Relação


Dados os conjuntos S1, S2, .., Sn (não
necessariamente distintos), R é uma relação nestes
n conjuntos se ele é um conjunto de tuplas <v1, v2,
..., vn> onde v1  S1, v2  S2, ... e vn  Sn.
A relação R é um subconjunto do produto cartesiano
S1 X S2 X ... X Sn.
Banco de Dados Relacional

Esquema


Instâncias (extensão)


Definição das relações
Conjunto de tuplas que compõem as relacões
Para cada relação define-se, usando a DDL disponível:



nome
atributos
restrições de integridade




chave primária, chave alternativa
chave estrangeira
valores nulos
dependentes do UdD
DDL e DML

Data Definition Language (DDL) – Comandos definem estrutura ou
esquema do banco de dados. Exemplos:







CREATE
ALTER
DROP
TRUNCATE
COMMENT
RENAME
Data Manipulation Language (DML) – Comandos gerenciam dados
dentro dos objetos do esquema. Exemplos:







SELECT
INSERT
UPDATE
DELETE
CALL
EXPLAIN PLAN
LOCK TABLE
DCL e TCL

Data Control Language (DCL) – Exemplos:



GRANT
REVOKE
Transaction Control (TCL) – Comandos usados para gerenciar as
mudanças feitas por comandos DML. Permitem que comandos sejam
agrupados em transações lógicas.
 COMMIT



SAVEPOINT
ROLLBACK
SET TRANSACTION (isolation level, rollback segment)
Restrições de Integridade do Modelo Relacional

Restrições de Integridade (RI) que se aplicam a
todo modelo que se diz conforme ao modelo
relacional

Integridade de Identidade





Chave primária
Chave alternativa
Chave estrangeira
Valor nulo
Integridade Referencial
Integridade de Identidade : Conceitos

Integridade de Identidade


Valor nulo (NOT NULL)


Grupo mínimo de atributos tal que a combinação de valores
assumida por este grupo corresponde a no máximo uma tupla
Chave Primária (PRIMARY KEY)




Tuplas incompletas
Chave Candidata


Nenhum atributo que participe de uma chave candidata de uma
relação pode assumir valor nulo
Uma entre as chaves candidatas
Selecionada pelo projetista por conveniencia de desempenho
Uma relação possui uma e somente uma chave primária
Chave Alternativa (UNIQUE)

Toda chave candidata que não for chave primária
SQL92
CREATE TABLE <table-name>
(<atr-name> <domain> [NOT NULL]
[,<atr-name> <domain> [NOT NULL]]*
[, PRIMARY KEY (atr-name(s))]
[, UNIQUE (atr-name(s))]*);
CREATE [UNIQUE] INDEX <index-name>
ON <table-name>(atr-name(s));
Exemplos
CREATE TABLE DEPARTAMENTO
(cod NUMBER(2) NOT NULL,
nome VARCHAR(15) NOT NULL,
ramal CHAR(4),
PRIMARY KEY (cod));
CREATE TABLE EMPREGADO
(cpf CHAR(8) NOT NULL,
rg CHAR(10) NOT NULL,
nome VARCHAR(60) NOT NULL,
nascimento DATE NOT NULL,
salario MONEY,
dep NUMBER(2),
gerente CHAR(8),
PRIMARY KEY (cpf),
UNIQUE(rg),
UNIQUE (nome, nascimento));
Exemplos
CREATE TABLE EMPREGADO
(cpf CHAR(8) NOT NULL,
rg CHAR(10) NOT NULL,
nome VARCHAR(60) NOT NULL,
nascimento DATE NOT NULL,
PRIMARY KEY (cpf),
UNIQUE(rg),
UNIQUE (nome, nascimento));
CREATE TABLE EMPREGADO
(cpf CHAR(8) NOT NULL PRIMARY KEY,
rg CHAR(10) NOT NULL UNIQUE,
nome VARCHAR(60) NOT NULL,
nascimento DATE NOT NULL,
UNIQUE (nome, nascimento));
Integridade Referencial

Chave Estrangeira:



Forma de implementar relacionamentos/associações no
modelo relacional
Não necessariamente a chave estrangeira participa da
chave primária da relação que a contém (S)
Integridade Referencial:

Se uma relação S inclui uma chave estrangeira S-FK
equivalendo à chave primária R-PK de uma relação R,
então todo valor S-FK em S deve ser :


Igual ao valor de R-PK em alguma tupla de R
Nulo
Integridade Referencial: Trocando em miúdos
Departamento
Código
Primary Key = Código
Nome
4620
Fundamentos da Computação
4622
Computação Aplicada
R-PK = Código
S-FK = Depto
Foreign Keys:
Depto  Departamento.Código
Superior  Empregado.Código
Empregado
Código
R-PK = Código
Nome
Depto Superior
1
Ir. Clotet
2
Avelino
4620
1
3
Rodrigo
4622
2
S-FK = Superior
Integridade Referencial: Anomalias de
Atualização

As atualizações na base de dados podem
causar violações da integridade referencial
Integridade Referencial: Anomalias de
Atualização

Enunciado :

Sejam R e S duas relações.




R-PK a chave primária de R
S-FK uma chave estrangeira em S que corresponde à R-PK de
R.
Seja v(t, k) uma função, onde t é uma tupla, e k um
conjunto de um ou mais atributos, e que designe o valor
do(s) atributo(s) k de uma tupla t.
Inclusão:

se uma tupla ts é inserida em S, o sistema precisa assegurar
uma das opções abaixo:


existe uma tupla tr em R tal que v(tr, R-PK) = v(ts, S-FK)
v(ts, S-FK)= NULL

possível somente se S-FK admitir valores nulos
Integridade Referencial: Trocando em miúdos
Departamento
Código
Primary Key = Código
Nome
4620
Fundamentos da Computação
4622
Computação Aplicada
R-PK = Código
S-FK = Depto
Foreign Keys:
Depto  Departamento.Código
Superior  Empregado.Código
Empregado
Código
R-PK = Código
Nome
Depto Superior
1
Ir. Clotet
2
Avelino
4620
1
3
Rodrigo
4622
2
S-FK = Superior
Integridade Referencial: Anomalias de
Atualização

Remoção:


se uma tupla tr é removida de R, o sistema precisa
computar o conjunto de tuplas tsi de S (i >= 0), onde
é verificada a propriedade v(tr, R-PK) = v(tsi , S-FK).
Se o conjunto não for vazio, as alternativas são:



a supressão de tr é proibida
suprime-se TAMBÉM todas as tuplas tsi do conjunto
(EFEITO CASCATA !!)
deve-se substituir TAMBÉM o valor de S-FK em cada tupla
tsi por NULL (DEPENDE DA DEFINIÇÃO DE S-FK)
Integridade Referencial: Trocando em miúdos
Departamento
Código
Primary Key = Código
Nome
4620
Fundamentos da Computação
4622
Computação Aplicada
R-PK = Código
S-FK = Depto
Foreign Keys:
Depto  Departamento.Código
Superior  Empregado.Código
Empregado
Código
R-PK = Código
Nome
Depto Superior
1
Ir. Clotet
2
Avelino
4620
1
3
Rodrigo
4622
2
S-FK = Superior
Integridade Referencial: Anomalias de
Atualização

Alteração da S-FK (relação referenciadora S):


Semelhante à inserção de tuplas em S
Alteração da R-PK (relação referenciada R):

Deve-se computar o conjunto de tuplas tsi de S,
onde a propriedade v(tr, R-PK) = v(tsi , S-FK) é
verificada. Se o conjunto não for vazio, as
alternativas são:



deve-se proibir a modificação
altera-se TAMBÉM o valor de S-FK em todas as tuplas tsi
do conjunto;
deve-se TAMBÉM substituir o valor de S-FK por NULL em
todas tuplas tsi

DEPENDE DA DEFINIÇÃO DE S-FK
Integridade Referencial: Trocando em miúdos
Departamento
Código
Primary Key = Código
Nome
4620
Fundamentos da Computação
4622
Computação Aplicada
R-PK = Código
S-FK = Depto
Foreign Keys:
Depto  Departamento.Código
Superior  Empregado.Código
Empregado
Código
R-PK = Código
Nome
Depto Superior
1
Ir. Clotet
2
Avelino
4620
1
3
Rodrigo
4622
2
S-FK = Superior
Manutenção de Restrições de Integridade

Restrições de Integridade do Modelo Relacional
estruturam-se em termos de estados consistentes da
base de dados
E1
(consistente)
rejeitar !

operação
E2
(consistente)
compensar !
Os modelos mais expressivos são aqueles que deixam
expressar de forma declarativa, junto ao esquema, as
políticas de manutenção de R.I. adotadas pela
corporação
SQL92

Definição de Chave estrangeira

Política de Rejeição (default)
 a operação só não é rejeitada se não houver tuplas (chave
estrangeira) fazendo referência a uma dada chave primária

Políticas Compensatórias (EXPLICITAMENTE DECLARADAS)
 CASCADES : propaga a alteração/remoção de tuplas
 SET NULL : o valor da chave estrangeira é ajustado para valor
nulo
 SET DEFAULT: o valor da chave estrangeira é ajustado para o
valor default (inicial)
SQL92

Cláusula Foreign-Key
FOREIGN KEY <fkey-atrs>
REFERENCES <target-relation>[(<pkey-atrs>)]
[ON DELETE <policy>]
[ON UPDATE <policy>]
<policy>:= CASCADE | SET NULL | SET DEFAULT
ESPECIFICADA NA TABELA QUE CONTÉM A CHAVE ESTRANGEIRA !!!
POLÍTICAS COMPESATÓRIAS ESPECIFICAM “CORREÇÕES” NA
TABELA QUE CONTÉM A CHAVE ESTRANGEIRA !!!!!
Exemplo
CREATE TABLE DEPARTAMENTO
(cod NUMBER(2) NOT NULL,
nome VARCHAR(15) NOT NULL,
ramal CHAR(4),
PRIMARY KEY (cod));
CREATE TABLE EMPREGADO
(cpf CHAR(8) NOT NULL,
nome VARCHAR(60) NOT NULL,
salario MONEY,
dep NUMBER(2),
gerente CHAR(8),
PRIMARY KEY (cpf),
FOREIGN KEY dep REFERENCES DEPARTAMENTO,
FOREIGN KEY gerente REFERENCES EMPREGADO);

Qual a política?
Exemplo

CREATE TABLE EMPREGADO
(cpf CHAR(8) NOT NULL,
nome VARCHAR(60) NOT NULL,
salario MONEY,
dep NUMBER(2),
gerente CHAR(8),
PRIMARY KEY (cpf),
FOREIGN KEY dep REFERENCES DEPARTAMENTO
ON DELETE SET NULL
ON UPDATE CASCADE
FOREIGN KEY gerente REFERENCES EMPREGADO
ON DELETE SET NULL;

Qual a política?
DDL : Remoção de Objetos do
Esquema




drop table <table name>
drop index <index name>
drop view <view name>
drop constraint <const name> (dentro de
comando alter table!)
DDL : Restrições


Definidas no comando create table ou alter table
Tipos:





unique
primary key
not null
foreign key
check(expressão)





check(sexo in (‘f’, ‘m’))
check(idade > 0 and idade <70)
check(salario <> 0)
check(cod between 1 and 10)
check(<nome de procedimento>)
DDL : Restrições
create table conta
(
cod number(5) not null,
saldo number(7,2) not null check(saldo >=0),
primary key (cod)
);
create table cliente
(
codc number(2) primary key,
nomec varchar(60) not null,
rg char(10) not null unique,
cpf char(10) not null,
idade number(2) check (idade between 16 and 90),
mae varchar(60) not null,
unique (cpf),
unique(nomec, mae)
);
DDL : Restrições
create table conta(
cod number(5) not null,
saldo number(7,2) not null check(saldo >=0),
primary key (cod)
);
ou
create table conta
(cod number(5),
saldo number(7,2));
alter table conta
add check(cod is not null);
alter table conta
add check(saldo is not null and saldo>=0);
alter table conta
add constraint conta_pk primary key(cod);
DDL : Restrições
ALTER TABLE <nome tabela>
ADD <definição restrição>
<definição restrição>::=
<padrão>| <nomeada>
<nomeada>::=
CONSTRAINT <nome><padrão>
<padrão>::= unique | check | primary key | foreign key
DDL : Restrições
alter table conta
add constraint emp_pk
primary key(cod);
ou
alter table conta
add primary key(cod);
---------------------------------------------------------alter table conta
add constraint saldo_verif
check(saldo is not null and saldo>0);
ou
alter table conta
add check(saldo is not null and saldo>0);