Projeto e Organização de BD
Linguagem SQL
Data Definition Language
Linguagem SQL

O Modelo Relacional prevê, desde sua
concepção, a existência de uma
linguagem baseada em caracteres que
suporte a definição do esquema físico
(tabelas, restrições, etc.), e sua
manipulação (inserção, consulta,
atualização e remoção)
Linguagem SQL

A Linguagem SQL (Structured Query Language) é
padrão para SGBDs Relacionais
– padrão ANSI (American National Standards Institute)
•
•
•
•
•

ANSI X3.135-1986 = ISO/IEC 9075:1987
ANSI X3.135-1989 = ISO/IEC 9075:1989
ANSI X3.135-1992 = ISO/IEC 9075:1992 (SQL2)
ANSI X3.135.10-1998 substituído pelo SQL1999
ANSI X3.135-1999 = ISSO/IEC 9075:1999
Embora seja capaz de prover acesso facilitado aos
dados, a linguagem SQL possui certas limitações,
como a impossibilidade de manipular uma tabela
linha-a-linha, exigindo sua extensão, neste caso,
através da definição de cursores
Conjuntos de Comandos da
Linguagem SQL

A Linguagem SQL pode ser dividida em
5 conjuntos de comandos:
– Recuperação de dados: comando SELECT
– Linguagem de manipulação de dados
(DML - Data Manipulation Language):
comandos para inserções (INSERT),
atualizações (UPDATE) e exclusões
(DELETE)
Conjuntos de Comandos da
Linguagem SQL
– Linguagem de definição de dados (DDL Data Definition Language): comandos para
criação e manutenção de objetos do banco
de dados: CREATE, ALTER, DROP,
RENAME e TRUNCATE
– Linguagem para controle de transações:
COMMIT, ROLLBACK e SAVEPOINT
– Linguagem para controle de acesso a
dados: GRANT e REVOKE
Código Armazenado no Banco de
Dados

O Modelo Relacional não previa,
originalmente, a possibilidade de armazenar
trechos de código no banco de dados. No
entanto, foi adaptado para permitir a
definição de
– Stored Procedures: trechos de código escritos em
linguagem SQL, armazenados no BD, e que
podem ser ativados a partir de aplicações-cliente,
comandos SQL, outras stored procedures, etc.
– Triggers: trechos de código armazenados no BD
ativados automaticamente após determinados
eventos
Código Armazenado no Banco de
Dados


No Oracle, os trechos de código
armazenado (triggers, stored
procedures e stored functions) são
criados utilizando-se a linguagem PLSQL
PL-SQL é uma linguagem de
programação de código procedural
Exemplo de Trigger
create trigger t_itens_pedidos after insert or update or
delete on pedidos_produtos for each row
begin
if inserting or updating then
update pedidos
set valor_total = valor_total + :new.valor *
:new.quantidade
where num_pedido = :new.num_pedido;
endif;
if deleting or updating then
update pedidos
set valor_total = valor_total - :old.valor *
:old.quantidade
where num_pedido = :old.num_pedido;
endif;
end;
/
Exemplo de Stored Procedure
create procedure reajusta_precos (percentual in
number) as
begin
update produtos
set preco = preco * (1 + percentual/100);
end
/
Apresentação do Estudo de Caso



O Estudo de caso aqui utilizado visa
modelar a realidade de uma livraria
São apresentados os esquemas
conceitual, lógico e físico
Todas as tabelas estão “populadas” e
sobre estas devem ser executados os
exercícios
CREATE TABLE tipos_telefones (
cod_tipo_telefone NUMBER ( 2 ) NOT NULL,
descricao VARCHAR2 ( 20 ) NOT NULL,
CONSTRAINT PK_TIPOS_TELEFONES PRIMARY KEY (cod_tipo_telefone)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE administradores (
cod_administrador NUMBER ( 6 ) NOT NULL,
nivel_privilegio NUMBER ( 1 ) NOT NULL,
CONSTRAINT PK_ADMINISTRADORES PRIMARY KEY (cod_administrador)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE clientes_enderecos (
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_endereco NUMBER ( 2 ) NOT NULL,
data_cadastro DATE NOT NULL,
CONSTRAINT PK_CLIENTES_ENDERECOS PRIMARY KEY (cod_cliente, cod_endereco)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE produtos (
cod_produto NUMBER ( 5 ) NOT NULL,
titulo VARCHAR2 ( 200 ) NOT NULL,
ano_lancamento DATE NOT NULL,
importado CHAR ( 1 ) NOT NULL,
preco NUMBER ( 10, 2 ) NOT NULL,
prazo_entrega NUMBER ( 3 ) NOT NULL,
CONSTRAINT PK_PRODUTOS PRIMARY KEY (cod_produto),
CONSTRAINT CHK_PROD_IMPORTADO CHECK (importado in ('S','N'))
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE enderecos (
cod_endereco NUMBER ( 2 ) NOT NULL,
rua VARCHAR2 ( 30 ) NOT NULL,
numero NUMBER ( 5 ) NOT NULL,
complemento VARCHAR2 ( 20 ),
cod_cidade NUMBER ( 4 ) NOT NULL,
cep CHAR ( 8 ) NOT NULL,
CONSTRAINT PK_ENDERECOS PRIMARY KEY (cod_endereco)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE telefones (
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_telefone NUMBER ( 2 ) NOT NULL,
cod_tipo_telefone NUMBER ( 2 ) NOT NULL,
ddd NUMBER ( 3 ),
numero VARCHAR2 ( 10 ) NOT NULL,
CONSTRAINT PK_TELEFONES PRIMARY KEY (cod_cliente, cod_telefone)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE clientes (
cod_cliente NUMBER ( 6 ) NOT NULL,
data_nascimento DATE,
data_cadastro DATE NOT NULL,
CONSTRAINT PK_CLIENTES PRIMARY KEY (cod_cliente)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE estados (
uf CHAR ( 2 ) NOT NULL,
nome VARCHAR2 ( 20 ) NOT NULL,
regiao CHAR ( 2 ) NOT NULL,
CONSTRAINT PK_ESTADOS PRIMARY KEY (uf)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE usuarios (
cod_usuario NUMBER ( 6 ) NOT NULL,
nome VARCHAR2 ( 100 ) NOT NULL,
cpf CHAR ( 11 ) NOT NULL,
email VARCHAR2 ( 40 ) NOT NULL,
username VARCHAR2 ( 20 ) NOT NULL,
password VARCHAR2 ( 20 ) NOT NULL,
CONSTRAINT PK_USUARIOS PRIMARY KEY (cod_usuario),
CONSTRAINT AK_USU_CPF UNIQUE (cpf),
CONSTRAINT AK_USU_USERNAME UNIQUE (username)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE cidades (
cod_cidade NUMBER ( 4 ) NOT NULL,
nome VARCHAR2 ( 40 ) NOT NULL,
uf CHAR ( 2 ) NOT NULL,
CONSTRAINT PK_CIDADES PRIMARY KEY (cod_cidade)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE pedidos_produtos (
num_pedido NUMBER ( 7 ) NOT NULL,
cod_produto NUMBER ( 5 ) NOT NULL,
quantidade NUMBER ( 3 ) NOT NULL,
valor_unitario NUMBER ( 10, 2 ) NOT NULL,
CONSTRAINT PK_PEDIDOS_PRODUTOS PRIMARY KEY (num_pedido, cod_produto)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE autores (
cod_autor NUMBER ( 4 ) NOT NULL,
nome VARCHAR2 ( 100 ) NOT NULL,
descricao VARCHAR2 ( 1024 ),
CONSTRAINT PK_AUTORES PRIMARY KEY (cod_autor)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE pedidos (
num_pedido NUMBER ( 7 ) NOT NULL,
cod_cliente NUMBER ( 6 ) NOT NULL,
cod_endereco NUMBER ( 2 ) NOT NULL,
data_emissao DATE NOT NULL,
CONSTRAINT PK_PEDIDOS PRIMARY KEY (num_pedido)
) TABLESPACE TSP_CURSOSQL
/
CREATE TABLE autores_produtos (
cod_autor NUMBER ( 4 ) NOT NULL,
cod_produto NUMBER ( 5 ) NOT NULL,
CONSTRAINT PK_AUTORES_PRODUTOS PRIMARY KEY (cod_autor, cod_produto)
) TABLESPACE TSP_CURSOSQL
/
ALTER TABLE administradores ADD ( CONSTRAINT FK_USU_ADM FOREIGN KEY (cod_administra
REFERENCES usuarios (cod_usuario))
/
ALTER TABLE clientes_enderecos ADD ( CONSTRAINT FK_CLI_CLIEND FOREIGN KEY (cod_clien
REFERENCES clientes (cod_cliente))
/
ALTER TABLE clientes_enderecos ADD ( CONSTRAINT FK_END_CLIEND FOREIGN KEY (cod_endereco)
REFERENCES enderecos (cod_endereco))
/
ALTER TABLE enderecos ADD ( CONSTRAINT FK_CID_END FOREIGN KEY (cod_cidade)
REFERENCES cidades (cod_cidade))
/
ALTER TABLE telefones ADD ( CONSTRAINT FK_CLI_TEL FOREIGN KEY (cod_cliente)
REFERENCES clientes (cod_cliente))
/
ALTER TABLE telefones ADD ( CONSTRAINT FK_TIPTEL_TEL FOREIGN KEY (cod_tipo_telefone)
REFERENCES tipos_telefones (cod_tipo_telefone))
/
ALTER TABLE clientes ADD ( CONSTRAINT FK_USU_CLI FOREIGN KEY (cod_cliente)
REFERENCES usuarios (cod_usuario))
/
ALTER TABLE cidades ADD ( CONSTRAINT FK_EST_CID FOREIGN KEY (uf)
REFERENCES estados (uf))
/
ALTER TABLE pedidos_produtos ADD ( CONSTRAINT FK_PED_PEDPROD FOREIGN KEY (num_pedido)
REFERENCES pedidos (num_pedido))
/
ALTER TABLE pedidos_produtos ADD ( CONSTRAINT FK_PROD_PEDPROD FOREIGN KEY (cod_produto)
REFERENCES produtos (cod_produto))
/
ALTER TABLE pedidos ADD ( CONSTRAINT FK_CLIEND_PED FOREIGN KEY (cod_cliente, cod_endereco)
REFERENCES clientes_enderecos (cod_cliente, cod_endereco))
/
ALTER TABLE autores_produtos ADD ( CONSTRAINT FK_AUT_AUTPROD FOREIGN KEY (cod_autor)
REFERENCES autores (cod_autor))
/
ALTER TABLE autores_produtos ADD ( CONSTRAINT FK_PRD_AUTPROD FOREIGN KEY (cod_produto)
REFERENCES produtos (cod_produto))
Linguagem de definição de dados
(DDL)

A linguagem de definição de dados
permite a criação, manutenção e
eliminação de objetos do banco de
dados:
– tabelas
– visões
– índices
– seqüências
– sinônimos
Convenções de Nomes





Devem começar com uma letra
Pode ter de 1 a 30 caracteres
Pode conter somente A-Z, a-z, 0-9, _, $
e#
Os nomes devem ser únicos por
usuário
Não podem ser utilizadas palavras
reservadas (salvo se entre aspas)
Tipos de Dados Básicos




CHAR(tamanho): seqüência de
caracteres de tamanho fixo
VARCHAR(tamanho): seqüência de
caracteres de tamanho variável
NUMBER(total, decimais): valores
numéricos
DATE: data e hora
Tipos de Dados para Caracteres

CHAR(tamanho [BYTE | CHAR]): até 2000 bytes
– Alocação de bytes é o padrão, mas podem ser alocados
CHARs, em formato Unicode






NCHAR(tamanho): até 2000 bytes em alocação
Unicode (2 ou 3 bytes por caracter)
VARCHAR2 ou VARCHAR(tamanho [BYTE |
CHAR]): até 4000 bytes
NVARCHAR2: até 4000 bytes
CLOB: até 232 – 1 bytes (4 GB)
NCLOB: até 232 – 1 bytes (4 GB) em alocação
Unicode
LONG: até 231 – 1 bytes (2 GB), mantido para
compatibilidade
Tipos de Dados para Data e Tempo






DATE: ano, mês, dia, hora, minuto, segundo,
armazenados em 7 bytes
INTERVAL DAY (precisão) TO SECOND (precisão):
intervalos entre duas datas em dias, horas, minutos e
segundos
INTERVAL (precisão) YEAR TO MONTH: intervalo
entre duas datas em anos e meses
TIMESTAMP (precisão): valores de instantes de
tempo com precisão de até 9 casas decimais nos
segundos
TIMESTAMP (precisão) WITH TIME ZONE: inclui a
informação do fuso horário
TIMESTAMP (precisão) WITH LOCAL TIME ZONE:
recupera a informação ajustada ao fuso horário local
Tipos de Dados Binários




BLOB: até 232 – 1 bytes (4 GB) de
dados binários armazenados no banco
de dados
BFILE: até 232 – 1 bytes (4 GB) de
dados binários armazenados em
arquivos externos
RAW (tamanho): armazena até 2000
bytes, mantido para compatibilidade
LONG RAW: até 231 – 1 bytes (2 GB) ,
mantido para compatibilidade
Comando CREATE TABLE

Permite a criação de uma tabela:
– CREATE TABLE nome_da_tabela (
nome_da_coluna tipo_de_dado [NULL|NOT
NULL] restrições_de_coluna, ...
restrições_de_tabela);
– CREATE TABLE autores (
cod_autor NUMBER (4) NOT NULL,
nome VARCHAR2 (100) NOT NULL,
descricao VARCHAR2 (1024)
);
Restrições de Integridade
Integridade de Entidade

Toda a tabela deve possuir,
obrigatoriamente, uma coluna (ou uma
composição de colunas) cujo valor é único
para todas as instâncias  CHAVE
PRIMÁRIA (PK)
Eventualmente, outras colunas além da
chave primária podem ser únicas  CHAVE
ALTERNATIVA (AK)
Restrições de Integridade
Integridade de Domínio

Cada coluna da tabela tem seus valores
restringidos por
– tipos de dados: definem o domínio de uma coluna
– cod_cliente NUMBER ( 6 )
– username
VARCHAR2 ( 20 )

Restrições a valores nulos: definem se as
colunas serão obrigatórias ou opcionais
–
–
email VARCHAR2 ( 40 ) NOT NULL
ddd NUMBER ( 3 )
Restrições de Integridade
Integridade de Domínio
– valores padrão: define um valor padrão a
ser associado a uma coluna se não for
fornecido valor (ou for explicitamente
fornecido um valor NULL) para esta
durante a inserção
importado CHAR ( 1 ) DEFAULT 'N' NOT NULL
– A definição de valores padrões é particularmente
importante para colunas NOT NULL
Restrições de Integridade
Integridade de Domínio
– restrições de validação: restringem os
valores que podem ser atribuídos a uma
coluna
CONSTRAINT CHK_PROD_IMPORTADO
CHECK (importado in ('S','N'))
– tipos definidos pelo usuário: tipo de dado
construído a partir de tipos pré-definidos,
contendo restrição a valores nulos, regras
de validação e um valor padrão
• Não disponível em todos os SGBDs
Restrições de Integridade
Integridade Referencial

As restrições de integridade referencial
garantem a consistência dos relacionamentos
entre as tabelas
Restrições de Integridade
Integridade Referencial

No exemplo, a definição da restrição de
integridade referencial seria criada como
segue
CONSTRAINT FK_EST_CID FOREIGN KEY (uf)
REFERENCES estados (uf)

Esta restrição estabelece uma relação entre
o atributo uf da tabela CIDADES e a chave
primária da tabela ESTADOS
Restrições de Integridade
Integridade Referencial


O SGBD deve prover mecanismos para
assegurar que a restrição de integridade seja
respeitada, ou seja, uma linha violando a
restrição de integridade referencial não
poderá existir;
Estes mecanismos prevêem a execução de
uma ação em resposta a uma atualização, a
fim de manter a integridade referencial.
Restrições de Integridade
Integridade Referencial

Existem diferentes ações possíveis para
evitar que uma operação de atualização viole
as restrições de integridade referencial
– RESTRICT: impede a execução da operação
Inserir uma Sala situada em um Prédio não
existente
– CASCADE: propaga a operação para respeitar
as restrições de integridade referencial
Remover uma Disciplina do Currículo,
removendo todos os Pré-requisitos associados a
esta
Restrições de Integridade
Integridade Referencial
– SET NULL: assegura as restrições de
integridade referencial associando um valor
NULL à chave estrangeira que poderia violá-las
Remover um Professor, fazendo com que a
coluna matricula_professor em
DISCIPLINAS_TURMAS fosse atualizada para
NULL em todas as disciplinas que atua
 NULL não é considerado um valor, portanto, não
fere as restrições de integridade referencial
– SET DEFAULT: semelhante à ação de SET
NULL, mas associa um valor padrão à chave
estrangeira
Restrições de Integridade
Integridade Definida pelo Usuário


O Projetista do Banco de Dados pode definir
restrições de integridade complexas,
utilizando, por exemplo, o conceito de
triggers
Triggers são porções de código ativadas
após certos eventos (inserção, atualização
ou exclusão de linhas da tabela)
Restrições de Integridade de Tabelas
– CONSTRAINT nome_da_restrição
PRIMARY KEY (colunas)
– CONSTRAINT nome_da_restrição
FOREIGN KEY (colunas) REFERENCES
nome_da_tabela_pai [ON DELETE
CASCADE]
– CONSTRAINT nome_da_restrição
UNIQUE (colunas)
– CONSTRAINT nome_da_restrição
CHECK (expressao)
Restrições de Integridade de Colunas
– CONSTRAINT nome_da_restrição NOT NULL
– CONSTRAINT nome_da_restrição PRIMARY
KEY
– CONSTRAINT nome_da_restrição
REFERENCES nome_da_tabela_pai [ON
DELETE CASCADE]
– CONSTRAINT nome_da_restrição UNIQUE
– CONSTRAINT nome_da_restrição CHECK
(expressao)
Exemplo
create table usuarios (
cod_usuario number ( 6 ),
nome varchar2 ( 100 )
constraint nn_usu_nome not null,
cpf char ( 11 )
constraint nn_usu_cpf not null,
email varchar2 ( 40 )
constraint nn_usu_email not null,
username varchar2 ( 20 )
constraint nn_usu_username not null,
password varchar2 ( 20 )
constraint nn_usu_password_not null,
constraint pk_usuarios primary key
(cod_usuario),
constraint ak_usu_cpf unique (cpf),
constraint ak_usu_username unique (username));
Exemplo
create table produtos (
cod_produto number ( 5 ) not null,
titulo varchar2 ( 200 ) not null,
ano_lancamento date not null,
importado char ( 1 ) not null,
preco number ( 10, 2 ) not null,
prazo_entrega number ( 3 ) not null,
constraint pk_produtos
primary key (cod_produto),
constraint chk_prod_importado
check (importado in (‘S',‘N'))
);
Exemplo
create table administradores (
cod_administrador number ( 6 ),
nivel_privilegio number ( 1 ) not null,
constraint pk_administradores primary
key (cod_administrador),
constraint fk_usu_adm foreign key
(cod_administrador) references usuarios
(cod_usuario)
);
Definindo Valores Padrão


Pode ser definido um valor padrão para uma
coluna (literais, expressões ou funções)
Se não for definido um valor, será utilizado o
padrão
– create table pedidos (
num_pedido number ( 7 ),
cod_cliente number ( 6 ) not null,
cod_endereco number ( 2 ) not null,
data_emissao date not null default
sysdate,
constraint pk_pedidos primary key
(num_pedido));
Utilizando Valores Padrão
– insert into pedidos (num_pedido,
cod_cliente, cod_endereco)
values (17645, 540, 290);

Foi omitida a coluna data_emissão,
logo, será utilizado o valor padrão
– insert into pedidos (num_pedido,
cod_cliente, cod_endereco,
data_emissao)
values (17645, 540, 290, NULL);

Foi explicitamente definido NULL
Consultando o Dicionário de Dados




USER_TABLES: informações sobre as
tabelas do usuário
USER_OBJECTS: informações completas
sobre todos os diferentes tipos de objetos
(object_type) do usuário
USER_CATALOG ou CAT: lista de todos os
objetos e seus respectivos tipos
Consultas sobre estas tabelas (visões)
fornecem informações úteis
Verificando as Constraints

A tabela (visão) user_constraints inclui
informações sobre todas as constraints do
usuário
– select constraint_name,
constraint_type, search_condition
from user_constraints
where ...;

A tabela (visão) user_cons_columns mostra
as colunas envolvidas em cada constraint
Criando uma Tabela a Partir de uma
Consulta

SQL permite a criação de uma tabela a partir
do resultado de uma consulta:
– CREATE TABLE nome_da_tabela (colunas)
AS
select ...
– CREATE TABLE teste (cod_cliente,
nome_cliente) AS
select u.cod_usuario, u.nome
from usuarios u
where u.cod_cliente in (
select c.cod_cliente
from clientes c);
Comando ALTER TABLE

Permite a alteração de uma tabela
ALTER TABLE nome_da_tabela
[ADD definição de coluna,]
[MODIFY definição de coluna,]
[DROP COLUMN nome,]
[RENAME COLUMN antigo TO novo,]
[ADD definição de constraint,]
[MODIFY CONSTRAINT definição de
constraint,]
[DROP CONSTRAINT nome,]
[RENAME CONSTRAINT antigo TO novo,]
[ENABLE | DISABLE constraint,]
[RENAME TO novo_nome];
Exemplo





alter table pedidos
add valor_total number(10,2);
alter table enderecos
modify cod_endereco number(3);
alter table autores
drop column descricao;
alter table clientes
rename column cod_usuario to
cod_cliente;
alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and 30);
Exemplo


alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and
30);
alter table produtos
drop constraint
chk_prod_importado;
Exemplo





alter table enderecos
modify cod_endereco number(3);
alter table produtos
add constraint chk_prod_prazo
check(prazo between 3 and 30);
alter table usuarios
disable constraint pk_usuarios;
alter table usuarios
enable constraint pk_usuarios;
alter table pedidos_produtos
rename to itens_pedidos;
Comando DROP TABLE

Permite a eliminação de uma tabela:
– DROP TABLE nome_da_tabela
[CASCADE CONSTRAINTS];
– drop table tipos_telefones
cascade constraints;
Comando TRUNCATE TABLE

Permite a eliminação de todas as linhas
de uma tabela, de forma rápida e
liberando o espaço alocado, sem
possibilitar a execução de rollback:
– TRUNCATE TABLE nome_da_tabela;
Comando CREATE INDEX

Para criar índices deve ser utilizado o
comando CREATE INDEX
– CREATE [UNIQUE] INDEX nome ON
tabela(colunas);
– create index idx_usu_nome on
usuarios (nome);

O comando DROP INDEX pode ser
utilizado para eliminar um índice
Quando Criar Índices?




A coluna é usada freqüentemente nas
cláusulas WHERE ou em joins
A coluna contém inúmeros valores
distintos
A coluna contém muitos NULL
A tabela tem muitas linhas e as
consultas normalmente retornam
poucas linhas
Quando não Criar Índices?




A tabela é pequena
As colunas não aparecem em
condições ou joins
As consultas retornam muitas linhas
A tabela é atualizada muito
freqüentemente
Utilizando Sinônimos


Sinônimos são nomes alternativos pelos
quais podem ser conhecidos os objetos do
banco de dados (tabelas, visões, sequences,
stored procedures, etc.)
Sinônimos podem ser públicos, acessíveis
por todos os usuários, ou privados
– CREATE [PUBLIC] SYNONYM nome FOR
objeto;
– create public synonym usuarios for
cursosql.usuarios;

O comando DROP SYNONYM pode ser
utilizado para eliminar um sinônimo