Bancos de Dados –
SQL – parte 1
Ceça
Conteúdo
•
•
•
•
Introdução
Histórico
Componentes
DDL
– Criando tabelas
Ceça Moraes
2
Introdução
• Linguagem padrão completa de
consulta e manipulação de dados em
BDs relacionais
• Largamente implementada no mercado
• Desenvolvida pela IBM
– Início da década de 70
– Implementada pela primeira vez em um
protótipo (Sistema R)
– Reimplementada depois em muitos
produtos da IBM e outros fornecedores
Ceça Moraes
3
Componentes
• Uma DDL para definição do esquema da base
de dados
• Uma DML para programação de consultas e
transações que inserem,removem e alteram
linhas de tabelas
• SQL pode ser embutida em linguagens de
programação de 3ª geração (COBOL, C,
Java…) estendendo-as para a manipulação
de banco de dados
• Um padrão para comunicação
cliente/servidor (ODBC - open database
connectivity)
Ceça Moraes
4
DDL – Definição de Estrutura
• SQL oferece três instruções para definição
do esquema do banco de dados:
• Create Table
– Define a estrutura de uma tabela, suas restrições
de integridade e cria a tabela vazia (sem linhas)
• Drop Table
– elimina a tabela da base de dados
• Alter Table
– permite modificar a definição de uma tabela
Ceça Moraes
5
Criando Tabelas
Domínios
• Um domínio é uma coleção de valores
válidos;
• O padrão-SQL2 aceita diversos tipos de
restrições de domínio:
–
–
–
–
–
–
–
–
–
–
Ceça Moraes
Char/Varchar
Integer
Smallint
Numeric
Real, double precision
Float
Date
Time
Datetime
Null/Not null
6
Criando Tabelas
CREATE TABLE
• O comando CREATE TABLE cria tabelas
e obedece a seguinte forma:
CREATE TABLE <tabela>
(<descrição das colunas>),
(<descrição das chaves>);
Ceça Moraes
7
Exemplo
nome
cod
Fornecedor
cidade
Ceça Moraes
cgc
cod
nome
status
N
Embarque
qtd
data
N
cor
Peca
cidade
peso
8
Criando Tabelas
CREATE TABLE Peca
CREATE TABLE Peca
(CodPeca CHAR(4) NOT NULL,
NomePeca CHAR(50) NOT NULL,
CorPeca CHAR(10) NULL,
PesoPeca INTEGER NULL,
CidadePeca CHAR(40) NULL,
PRIMARY KEY (CodPeca))
ENGINE = INNODB
MYSQL
Ceça Moraes
9
Criando Tabelas
CREATE TABLE Fornecedor
CREATE TABLE Fornecedor
(CodFornec
CHAR(4) NOT NULL,
NomeFornec
CHAR(50) NOT NULL,
StatusFornec INTEGER ,
CidadeFornec CHAR(40),
CGC
CHAR(14) NULL Unique,
PRIMARY KEY (CodFornec))
ENGINE = INNODB
MYSQL
Ceça Moraes
10
Criando Tabelas
CREATE TABLE Embarque
CREATE TABLE Embarque
(CodPeca CHAR(4) NOT NULL,
CodFornec CHAR(4) NOT NULL,
QtdeEmbarc INTEGER NOT NULL,
PRIMARY KEY (CodPeca,CodFornec),
FOREIGN KEY (CodPeca) REFERENCES
Peca (CodPeca),
FOREIGN KEY (CodFornec) REFERENCES
Fornecedor (CodFornec))
ENGINE = INNODB
MYSQL
11
Observações
• A cláusula REFERENCES estabelece a
restrição de integridade referencial
entre as tabelas
• Só podemos incluir essas restrições se
as tabelas referenciadas já existirem
• Podemos criar tabelas sem a cláusula
REFERENCES e depois utilizar o
comando ALTER TABLE para realizar a
restrição
Ceça Moraes
12
Chaves
• Especificação de chaves:
– primária (PRIMARY KEY)
– estrangeira (FOREIGN KEY)
– alternativa (Unique)
• Praticamente todos SGBDS comerciais
incluem a definição de chaves
Ceça Moraes
13
Integridade Referencial
Deleção
• Caso nada seja especificado na
definição da chave estrangeira...
– Uma linha que contenha uma chave
primária referenciada como chave
estrangeira não pode ser excluída (regra
RESTRICT)
• Pode ser definida a propagação da
exclusão da linha para as linhas que a
referenciam
Ceça Moraes
14
Integridade Referencial
Cláusula ON DELETE
CREATE TABLE Embarque
(CodPeca CHAR(4) NOT NULL,
CodFornec CHAR(4) NOT NULL,
QtdeEmbarc INTEGER NOT NULL,
PRIMARY KEY (CodPeca,CodFornec),
FOREIGN KEY (CodPeca) REFERENCES Peca
(CodPeca)
ON DELETE CASCADE,
FOREIGN KEY (CodFornec) REFERENCES
Fornecedor (CodFornec)
ON DELETE CASCADE)
15
Integridade Referencial
Cláusula ON DELETE
• Sintaxe completa
FOREIGN KEY ... REFERENCES ...
ON DELETE {CASCADE|SET NULL|RESTRICT}
• SET NULL
– Especifica que as chaves estrangeiras que
referenciam a linha excluída devem ser tornadas
vazias
Ceça Moraes
16
Integridade Referencial
Cláusula ON DELETE
• RESTRICT (default)
– Especifica que uma linha não pode ser
excluída caso existam chaves estrangeiras
que a referenciem
• CASCADE
– Propaga a exclusão da chave primária para
as linhas que a referenciam como
estrangeira
Ceça Moraes
17
Integridade Referencial
Cláusula ON UPDATE
• Sintaxe completa
FOREIGN KEY ... REFERENCES ...
ON UPDATE {CASCADE|SET NULL|RESTRICT}
• CASCADE
– Alteração da chave primária é propagada para as
chaves estrangeiras que a referenciam
• SET NULL
– Chaves estrangeiras que referenciam a linha
alterada devem ser tornadas vazias
• RESTRICT
– uma chave primária não pode ser alterada, caso
existam chaves estrangeiras que a referenciem
Ceça Moraes
18
Removendo Tabelas
DROP TABLE
• Para eliminar completamente uma tabela
(vazia ou não) da base de dados, usa-se a
instrução:
– DROP TABLE Embarque
• o SQL/2 inclui cláusulas RESTRICT e CASCADE
(obrigatória) que informam se a exclusão
deve ser propagada ou não para objetos
definidos com base na tabela (visões)
• Exemplo
– DROP TABLE Embarque RESTRICT
– Exclui a tabela somente se não existirem visões
definidas com base na tabela
Ceça Moraes
19
Modificando Tabelas
ALTER TABLE
• A instrução ALTER TABLE serve para
modificar a estrutura original da tabela.
• Nem todas modificações são permitidas
• Primeiros SGBDs e SQL/1 somente
permitiam adicionar colunas
• Exemplo: adicionar colunas a tabelas
– ALTER TABLE Embarque ADD DataEmbarq
DATE
– Para cada linha existente será criada uma
instância de DataEmbarq vazia
Ceça Moraes
20
Observações
ALTER TABLE
• A instrução adiciona uma nova coluna
com o valor vazio para todas linhas
• Os valores para as diversas linhas
devem ser adicionadas através de
instruções da DML (UPDATE)
• Não pode ser especificada a cláusula
NOT NULL já que a coluna é criada com
o valor vazio
Ceça Moraes
21
Modificações de Chaves
ALTER TABLE
• ALTER TABLE permite incluir ou excluir
restrições de chave
– ALTER TABLE Representante DROP
PRIMARY KEY
• Para excluir uma chave estrangeira é
necessário que ela tenha recebido um nome
quando de sua definição
– ALTER TABLE Representante ADD FOREIGN
KEY (CodFilial) REFERENCES Filial
(CodFilial)
– ALTER TABLE Representantes DROP
FOREIGN KEY (CodFilial) REFERENCES
Filial (CodFilial)
Ceça Moraes
22
Observações
• ALTER TABLE com definição de chaves permite
separar a DDL
• em duas seções
– Primeira seção contém apenas os CREATE TABLE
com a estrutura da base de dados
– Segunda seção contém as restrições de chave
através de instruções ALTER TABLE
• Sintaxe para chaves vista até aqui está saindo
de uso
• A tendência em SQL/2 é tratar de forma
uniforme todas restrições de integridade (chave
primária, chave única, chave estrangeira, …)
– São chamados de CONSTRAINT
Ceça Moraes
23
Restrições de Integridade
CONSTRAINT
CREATE TABLE Embarque
(CodFornec CHAR(4) not null,
CodPeca
CHAR(4) not null,
Quant
INTEGER not null,
DataEmbarq DATE,
CONSTRAINT PK_EMBARQ PRIMARY KEY
(CodFornec,CodPeca))
Ceça Moraes
24
Restrições de Integridade
CONSTRAINT
ALTER TABLE Embarque
ADD CONSTRAINT
FK_EMBARQ_RELATION_PECA
FOREIGN KEY (CodPeca)
REFERENCES Peca (CodPeca)
Ceça Moraes
25
Restrições de Integridade
CONSTRAINT
ALTER TABLE Embarque
ADD CONSTRAINT
FK_EMBARQ_RELATION_FORNECEDOR
FOREIGN KEY (CodFornec)
REFERENCES Fornecedor (CodFornec)
Ceça Moraes
26
Referências
• Date
– Capítulo 4
• Rob & Coronel
– Capítulos 7 e 8
Ceça Moraes
27
Exercício
• Instalar o BR Office
• Executar os comandos SQL dos
slides a seguir em um banco de
dados MS Access ou BR Office
Ceça Moraes
28
Exercício
1. Criação da tabela Peca:
CREATE TABLE Peca
(CodPeca CHAR(4) NOT NULL,
NomePeca CHAR(50) NOT NULL,
CorPeca CHAR(10) NULL,
PesoPeca INTEGER NULL,
CidadePeca CHAR(40) NULL,
PRIMARY KEY (CodPeca))
Ceça Moraes
29
Exercício
2. Criação da tabela Fornecedor:
CREATE TABLE Fornecedor
(CodFornec
CHAR(4) NOT NULL,
NomeFornec
CHAR(50) NOT NULL,
StatusFornec INTEGER ,
CidadeFornec CHAR(40),
CGC
CHAR(14) NULL
Unique,
PRIMARY KEY (CodFornec))
Ceça Moraes
30
Exercício
3. Criação da tabela Embarque:
CREATE TABLE Embarque
(CodPeca CHAR(4) NOT NULL,
CodFornec CHAR(4) NOT NULL,
QtdeEmbarc INTEGER NOT NULL,
PRIMARY KEY (CodPeca,CodFornec),
FOREIGN KEY (CodPeca) REFERENCES
Peca (CodPeca),
FOREIGN KEY (CodFornec) REFERENCES
Fornecedor (CodFornec))
Ceça Moraes
31
Exercício
4. Alteração da tabela Embarque:
–
ALTER TABLE Embarque ADD
DataEmbarq DATE
5. Remoção da tabela Embarque:
–
Ceça Moraes
DROP TABLE Embarque
32
Exercício
6. Criação da tabela Embarque
(CONSTRAINT):
CREATE TABLE Embarque
(CodFornec CHAR(4) not null,
CodPeca
CHAR(4) not null,
Quant
INTEGER not null,
DataEmbarq DATE,
CONSTRAINT PK_EMBARQ PRIMARY KEY
(CodFornec,CodPeca))
Ceça Moraes
33
Exercício
7. Criação do relacionamento
Embarque-Peca (CONSTRAINT):
ALTER TABLE Embarque
ADD CONSTRAINT
FK_EMBARQ_RELATION_PECA
FOREIGN KEY (CodPeca)
REFERENCES Peca (CodPeca)
Ceça Moraes
34
Exercício
8. Criação do relacionamento EmbarqueFornecedor (CONSTRAINT):
ALTER TABLE Embarque
ADD CONSTRAINT
FK_EMBARQ_RELATION_FORNECEDOR
FOREIGN KEY (CodFornec)
REFERENCES Fornecedor
(CodFornec)
Ceça Moraes
35