Banco de Dados I
SQL – DDL
Frederico D. Bortoloti
[email protected]
Introdução
• Modelo relacional encontra-se
padronizado p
p
pela indústria de informática.
Ele é chamado de padrão SQL (Structured
Query Language)
• SQL usa uma combinação de construtores
em álgebra
á
relacional e cálculo
á
relacional
• Embora o próprio nome se refira a SQL
como uma linguagem de consulta, ela
possui outros recursos
Introdução
• O padrão SQL define precisamente uma
interface SQL para a definição de tabelas,
para as operações
õ sobre
b as mesmas
(seleção, projeção, junção e outras) e para a
d fi i ã d
definição
de regras d
de iintegridade
t id d d
de b
bancos
de dados
• A interface SQL é implementada em todos os
sistemas de bancos de dados relacionais
existentes
• A existência de padrões facilita a
interoperabilidade
Introdução
• Um SGBD necessita possuir duas
linguagens
– DDL (Data Definition Language) - Usada para
definir os esquemas, atributos, visões, regras
de integridade, índices, etc.
– DML (Data Manipulation Language) - Usada
para se ter acesso aos dados armazenados
no BD
• Exemplos de linguagens de consulta
– QUEL
– QBE
– SQL
Histórico
• A versão original de SQL foi desenvolvida pela
IBM
– Originalmente chamada de SEQUEL
– Parte do projeto System R
• Tornou-se a linguagem padrão para SGBD’s
relacionais
• Existem diversos padrões para SQL
–
–
–
–
ANSI SQL (SQL
ANSI-SQL
(SQL-86)
86)
IBM SQL (SQL-89)
ANSI/ISO SQL (SQL 92)
SQL-3 (SQL 99)
Partes do SQL
• SQL é dividido em diversas partes
– Linguagem
g g
de definição
ç de dados ((DDL)) –
proporciona comandos para a definição,
exclusão e modificação
ç de esquemas
q
de
relações e criação de índices
– Linguagem interativa de manipulação de
dados (DML) – linguagem de consulta
baseada na álgebra e no cálculo relacional de
tuplas, incluindo inserção, exclusão e
modificação de tuplas
Partes do SQL
– Incorporação DML (Embedded DML) – projetada
para aplicação em linguagens de programação
d uso geral,l como C
de
Cobol,
b l P
Pascal,
l F
Fortran
t
eC
– Definição de visões
– Autorização – definição de direitos de acesso a
relações e visões
– Integridade – especificação de regras de
integridade que os dados que serão
armazenados
d d
deverão
ã satisfazer
i f
– Controle de transações – comandos para
especificação de iniciação e finalização de
transações
Domínios
Tipos de Domínios em SQL-92
Tipo
p
Descrição
ç
Char(n) ou character String de tamanho fixo n
Varchar(n) ou
String de tamanho variável com
Character varying
tamanho máximo n
int ou integer
Número inteiro
Smallint
Inteiro pequeno
Numeric(p,d)
Número ponto fixo com 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
di i do
d ponto decimal
d i l
real,
Ponto flutuante e ponto flutuante
double precision
de precisão dupla
Float(n)
Ponto flutuante com precisão
definida pelo usuário
Date
Data: aaaa/mm/dd
Time
Hora: hh:mm:ss
Comandos SQL – DDL
• Os comandos SQL para definição de
dados são:
– CREATE
– DROP
– ALTER
• SQL permite a criação de domínios
– CREATE DOMAIN nome_pessoa
nome pessoa char(20)
• depois nome_pessoa pode ser usado
como domínio
d í i d
de um atributo
t ib t qualquer
l
Comandos SQL – DDL
• CREATE TABLE:
TABLE especifica
ifi uma nova tabela
t b l
(relação), dando o seu nome e especificando as
colunas (atributos)
(atributos), cada uma com seu nome
nome,
tipo e restrições
• Sintaxe:
CREATE TABLE r (A1D1, A2D2, ..., ANDN,
<regra de
d iintegridade
t id d 1>,
...,
<regra de integridadeK> )
• Onde r é o nome da relação, Ai é um nome de
atributo e Di é o domínio do atributo Ai
Comandos SQL – DDL
• As definições dos atributos têm o seguinte
formato:
– atributo tipo [NOT NULL [UNIQUE]] [DEFAULT valor]
• Onde:
– atributo: nome do atributo que está sendo definido
– tipo_dado:
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.
– DEFAULT valor: indica um valor a ser atribuído ao
atributo caso não seja determinado um valor na
inserção
Comandos SQL – DDL
• Regras de integridade (constraints)
– Integridade
g
de chave
• PRIMARY KEY (atributos chave)
– Integridade referencial
• FOREIGN KEY (atributos) REFERENCES
tabela base(atributos)
tabela_base(atributos)
– Integridade de domínio
• CHECK (condição)
Comandos SQL – DDL
CREATE TABLE
empregado
( nome VARCHAR(15)
NOT NULL,
matricula char(9) NOT
NULL,
dataNasc DATE
DATE,
endereco VARCHAR(30),
sexo CHAR,
CHAR
salario NUMERIC(10,2),
supervisor CHAR(9),
CHAR(9)
depto INT NOT NULL,
PRIMARY KEY (matricula),
CHECK (salario >= 0),
FOREIGN KEY (supervisor)
REFERENCES
empregado(matricula),
FOREIGN KEY (depto)
REFERENCES
departamento(codDep)
);
Comandos SQL – DDL
CREATE TABLE departamento
d
t
t
( nomeDep
VARCHAR(15) NOT NULL,
codDep
INT
NOT NULL,
gerente
g
CHAR(9)
( ) NOT NULL,
dataInicioGer DATE,
PRIMARY KEY(codDep),
UNIQUE (nomeDep),
FOREIGN KEY (gerente) REFERENCES
empregado(matricula)
);
Comandos SQL – DDL
CREATE TABLE estudante (
nome
char(15) NOT NULL
NULL,
id-estudante char(10) NOT NULL,
nivel
char(15) NOT NULL,
PRIMARY KEY (id-estudante)
(id-estudante),
CHECK (nivel IN (“Bacharelado”,
”Mestrado”, ”Doutorado”)))
Comandos SQL – DDL
• O valor NULL é um valor válido para todos
p em SQL,, salvo se o tipo
p for
os tipos
definido como NOT NULL
• Sabemos que uma chave primária não
pode conter valores nulos e repetições
– O SQL-89 obrigava os atributos da chave
primária a serem declarados como NOT
NULL e UNIQUE
– SQL-92 jjá assume essas condições,
ç
, sua
declaração é redundante
Comandos SQL – DDL
• Exercício: Defina as tabelas abaixo
usando SQL
– Fornecedor (codigo, nome, cidade)
– Venda (codForn
(codForn, codPeca
codPeca, quantidade,
quantidade data)
– Peca (codPeca, nome, descricao)
• É obrigatório que:
– quantidade seja maior que zero
– nome da peça não seja nulo
Comandos SQL – DDL
CREATE TABLE F
Fornecedor
d (
codigo CHAR(10) NOT NULL,
nome VARCHAR(50) NOT NULL,
cidade VARCHAR(80),
PRIMARY KEY(codigo));
Comandos SQL – DDL
CREATE TABLE V
Venda
d (
codForn CHAR(10) NOT NULL,
codPeca CHAR(10) NOT NULL,
quantidade INT NOT NULL,
data DATE NOT NULL,
PRIMARY KEY (codForn
(codForn,codPeca),
codPeca)
FOREIGN KEY (codForn) REFERENCES
Fornecedor(codigo),
Fornecedor(codigo)
CHECK (quantidade > 0));
Comandos SQL – DDL
CREATE TABLE P
Peca (
codPeca CHAR(10) NOT NULL,
nome VARCHAR(50) NOT NULL,
descricao VARCHAR(100),
PRIMARY KEY (codPeca))
Comandos SQL – DDL
• Chave estrangeira
g
– É definida com a cláusula FOREIGN KEY
– Alguns SGBD’s permitem que se use uma
notação
t ã abreviada
b i d para chave
h
estrangeira
t
i
quando esta é formada por um único atributo
CREATE TABLE Empregado (
matricula CHAR(9) NOT NULL,
nome VARCHAR(15) NOT NULL,
…
supervisor
p
CHAR(9)
( ) REFERENCES
Empregado(matricula),
codDep INT NOT NULL REFERENCES
Departamento(codigo),
Departamento(codigo)
…);
Comandos SQL – DDL
• U
Uma cláusula
lá
l FOREIGN KEY iinclui
l i regras d
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
Comandos SQL – DDL
• 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
p
em
remoção de todas as linhas de T2 que combina com
a chave de T1
– SET NULL
NULL: remoção
ã d
de T1 iimplica
li em colocar
l
NULL
em todos os atributos da chave estrangeira de cada
linha
a de T2 que co
combina
b a
– SET DEFAULT: remoção de linha em T1 implica em
colocar valores DEFAULT nos atributos da chave
estrangeira
t
i d
de cada
d lilinha
h d
de T2 que combina
bi
Comandos SQL – DDL
• 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
li h que combinam
linhas
bi
em T2 ttambém
bé serão
ã
atualizadas
– SET NULL: update de T1 implica que valores da
chave estrangeira em T2 nas linhas que combinam
são postos para NULL
– SET DEFAULT: update de T1 implica que valores da
g
de T2 nas linhas q
que combinam
chave estrangeira
terão valores default aplicados
Comandos SQL – DDL
• As restrições de integridade podem ter um
p
com a
nome e serem especificadas
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
Comandos SQL – DDL
CREATE TABLE empregado (
…
depto
INT NOT NULL DEFAULT 1,
CONSTRAINT empPK
PRIMARY KEY(matricula),
CONSTRAINT empSuperFK
FOREIGN KEY(supervisor) REFERENCES
empregado(matricula)
ON DELETE SET NULL ON UPDATE
CASCADE,
CONSTRAINT deptoFK
FOREIGN KEY (depto) REFERENCES
departamento(codigo)
ON DELETE SET DEFAULT ON UPDATE
CASCADE
)
Comandos SQL – DDL
• ALTER TABLE
– Permite que se altere os atributos de uma
determinada tabela ou que se adicione
novos atributos (evolução de esquemas)
– Os novos atributos terão valores nulos em
t d as linhas
todas
li h
– Ao incluirmos uma coluna devemos
especificar o seu tipo de dado, não
podendo esta coluna ser NOT NULL
p
Comandos SQL – DDL
• Sintaxe:
ALTER TABLE tabela_base
ADD atributo tipo_dado
• Exemplo:
ALTER TABLE Peca
ADD espessura INT
Comandos SQL – DDL
• 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 permite a remoção do
atributo se este é usado numa visão ou como
chave estrangeira numa outra tabela
Comandos SQL – DDL
• Exemplos:
ALTER TABLE empregado DROP endereco
CASCADE;
ALTER TABLE departamento ALTER gerente DROP
DEFAULT
ALTER TABLE departamento ALTER gerente
SET DEFAULT “333444555”
ALTER TABLE empregado
DROP CONSTRAINT empsuperFK;
ALTER TABLE empregado
ADD CONSTRAINT empsuperFK FOREIGN KEY
(supervisor) REFERENCES empregado (matricula)
Comandos SQL – DDL
• DROP TABLE
– Remove uma tabela-base do BD. Remove
tanto os dados quanto a definição da tabela
• Sintaxe
– DROP TABLE <nomeTabela>
• Exemplo
– DROP TABLE Peca
Comandos SQL – DDL
• Especificando
E
ifi
d ííndices
di
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
d um arquivo,
de
i
permitindo
iti d um acesso mais
i
eficiente aos dados
– Se
S os atributos
t ib t usados
d nas condições
di õ
d
de
seleção e junção de uma query são
indexados o tempo de execução da query é
indexados,
melhorado
Comandos SQL – DDL
• Ex.: Criar um índice no atributo nome da
ç Empregado
p g
relação
CREATE INDEX nomeEmpIndex
ON Empregado(nome)
• O default é ordem ascendente, se
quisermos uma ordem descendente
palavra chave DESC
adicionamos a p
depois do nome do atributo
Comandos SQL – DDL
• Para especificar a restrição de chave
palavra UNIQUE
usamos a p
CREATE UNIQUE INDEX matrEmpIndex
ON Empregado(matricula)
• Para eliminarmos um índice usamos o
comando DROP
– Ex. DROP INDEX nome
nome-indice
indice
Comandos SQL – DDL
• Restrições
R ti õ d
de d
domínios
í i
– Na criação de domínios, é possível aplicar constraints
para realizar algumas restrições
– Exemplos
CREATE DOMAIN turno
turno_trabalho
trabalho NUMERIC(5
NUMERIC(5,2)
2)
CONSTRAINT valor_teste_turno CHECK (VALUE >=
4,00))
CREATE DOMAIN numero_conta CHAR(10)
CONSTRAINT teste_nulo_numero_conta CHECK
(
(VALUE
NOT
O NULL))
CREATE DOMAIN tipo_conta CHAR(10)
CONSTRAINT teste_tipo_conta
t t ti
t CHECK (VALUE IN
(“Corrente”, “Poupança”))