Banco de Dados I
SQL – DDL
Ricardo Oliveira
ba.ricardo@uol.com.br
Introdução
Modelo relacional encontra-se padronizado
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 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 é implementada em todos os
sistemas de bancos de dados relacionais
existentes
A existência de padrões facilita a
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-86) IBM SQL (SQL-89)
ANSI/ISO SQL (SQL 92) SQL-3 (SQL 99)
Partes do SQL
SQL é dividido em diversas partes
Linguagem de definição de dados (DDL) –
proporciona comandos para a definição, exclusão
e modificação de esquemas 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 de uso
geral, como Cobol, Pascal, Fortran e C
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 deverão
satisfazer
Controle de transações – comandos para
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 char(20)
depois nome_pessoa pode ser usado como
Comandos SQL – DDL
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 r (A
1D
1, A
2D
2, ..., A
ND
N,
<regra de integridade
1>,
...,
<regra de integridade
K> )
Onde r é o nome da relação, A
ié um nome de atributo
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: 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
Comandos SQL – DDL
Regras de integridade (constraints)
Integridade de chave
PRIMARY KEY (atributos chave)
Integridade referencial
FOREIGN KEY (atributos) REFERENCES
tabela_base(atributos)
Integridade de domínio
Comandos SQL – DDL
CREATE TABLE empregado ( nome VARCHAR(15) NOT
NULL,
matricula char(9) NOT NULL, dataNasc DATE, endereco VARCHAR(30), sexo CHAR, salario NUMERIC(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) );
Comandos SQL – DDL
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)
Comandos SQL – DDL
CREATE TABLE estudante (
nome
char(15) NOT NULL,
id-estudante char(10) NOT NULL,
nivel
char(15) NOT NULL,
PRIMARY KEY (id-estudante),
CHECK (nivel IN (“Bacharelado”, ”Mestrado”,
”Doutorado”)))
Comandos SQL – DDL
O valor NULL é um valor válido para todos os
tipos em SQL, salvo se o tipo for 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 já assume essas condições, sua
Comandos SQL – DDL
Exercício: Defina as tabelas abaixo usando
SQL
Fornecedor (codigo, nome, cidade)
Venda (codForn, codPeca, 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
Chave estrangeira
É definida com a cláusula FOREIGN KEY
Alguns SGBD’s permitem que se use uma notação
abreviada para chave estrangeira quando esta é formada por um único atributo
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),
Comandos SQL – DDL
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
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 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
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 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 para NULL
SET DEFAULT: update de T1 implica que valores da chave
estrangeira de T2 nas linhas que combinam terão valores default aplicados
Comandos SQL – DDL
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
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
todas as linhas
Ao incluirmos uma coluna devemos
especificar o seu tipo de dado, não podendo
esta coluna ser NOT NULL
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
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
Comandos SQL – DDL
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
Comandos SQL – DDL
Ex.: Criar um índice no atributo nome da
relação Empregado
CREATE INDEX nomeEmpIndex
ON Empregado(nome)
O default é ordem ascendente, se quisermos
uma ordem descendente adicionamos a
palavra chave DESC depois do nome do
atributo
Comandos SQL – DDL
Para especificar a restrição de chave usamos
a palavra UNIQUE
CREATE UNIQUE INDEX matrEmpIndex
ON Empregado(matricula)
Para eliminarmos um índice usamos o
comando DROP
Comandos SQL – DDL
Restrições de domínios
Na criação de domínios, é possível aplicar constraints para
realizar algumas restrições
Exemplos
CREATE DOMAIN turno_trabalho NUMERIC(5,2)
CONSTRAINT valor_teste_turno CHECK (VALUE >= 4,00) CREATE DOMAIN numero_conta CHAR(10)
CONSTRAINT teste_nulo_numero_conta CHECK (VALUE NOT NULL)
CREATE DOMAIN tipo_conta CHAR(10)
CONSTRAINT teste_tipo_conta CHECK (VALUE IN (“Corrente”, “Poupança”))