Padrões de SQL

BANCO DE DADOS
SQL
(DDL: CREATE TABLE ,
CONSTRAINTS)
Prof. Edson Thizon
Conceito
• SQL ( STRUCTURED QUERY LANGUAGE)
• Uma linguagem utilizada para consultar , atualizar
e gerenciar Banco de Dados .
• Uma linguagem de aplicação para Banco de Dados
relacionais , e não um sistema ou uma linguagem
de programação .
Padrões de SQL (2)
•
A aderência a padrões de SQL é importante
para aqueles que:
– escrevem comandos de SQL em suas aplicações (SQL não
fica oculto por algum gerador de telas..)
– desejam portar aplicações a vários SGBD
– O padrão é irrelevante para aqueles que usam ferramentas
como geradores de telas, relatórios, etc. proprietários - o
usuário está preso ao fornecedor da ferramenta
– Praticamente todo fornecedor de SGBD afirma que seu
SQL é compatível com o padrão
– Grandes fornecedores (Oracle, Sybase, DB/2, SQL/Server)
normalmente têm pelo menos SQL/2 entry level (entry-level,
conjunto mínimo para considerar produto como SQL/2)
Histórico
Entre 1974 e 1979, o San José Research Laboratory da
IBM desenvolveu um SGDB relacional que ficou
conhecido como Sistema R.
Para a criação e acesso aos dados foi adotada uma
linguagem chamada SEQUEL, mais tarde rebatizada SQL
(Structured Query Language).
Embora a query em sua definição, a SQL foi
projetada de forma a permitir que além de consultas
(queries), inserções, alterações e deleções fossem feitas,
além da própria criação das tabelas e campos.
Padrões de SQL (1)
• SQL (“structured query language”) é uma
linguagem comercial de definição e
manipulação de banco de dados relacional.
• SQL é padrão de fato (mesmo SGBDs como INGRES que
possuíam outras linguagens oferecem uma interface SQL)
• SQL é padrão de direito (ISO):
– SQL1 aprovado em 1986, com modificações em 1989
– SQL2 aprovado em 1992
– SQL3 aprovado em 1999
Componentes do SQL (1)
• Oferece as seguintes funcionalidades:
– 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
• Uma versão de SQL embutida em linguagens
de programação de 3ª geração (COBOL, C,
…) estendendo-as para a manipulação de
banco de dados.
1
DDL
Componentes do SQL (2)
• Um padrão para comunicação
cliente/servidor (ODBC - open database
connectivity) a partir de SQL/3
• Instruções para definição de visões (tabelas
virtuais vistas por um usuário ou uma classe
de usuários)
• Instruções para controle de autorização de
acesso
• Instruções para controle de transações e
concorrência
• Instruções para especificação de restrições
de integridade
DDL – Criação de banco de dados
• SQL/2 não oferece instruções para criação
de BD.
• Alguns produtos (SQL/Server) têm instruções
de DDL:
– Create Database
• cria uma base de dados vazia
– Drop Database
• elimina uma base de dados
• Outros têm abordagens variadas
A DDL, uma parte muito pequena da SQL, permite a criação e
manutenção do dicionário de dados. O dicionário de dados contém a
definição de cada tabela, de cada campo, enfim, contém a definição da
base de dados propriamente dita. Em outras palavras, o dicionário de
dados guarda dados sobre os dados.
Embora existam algumas outras construções, a mais importante
das construções da DDL é a destinada a criação de tabelas. Por
exemplo, a sentença para a criação das tabelas funcionário, cidade, e
setor, poderia ser como segue:
Adotada como padrão mundial pela ISSO em 1987, é uma
linguagem exclusiva de banco de dados Cliente/Servidor;
Não dispõe dos seguinte recursos:
- Repetição e desvio;
- Comandos para manipulação de telas e impressão de relatórios;
Os Fabricantes de SGBD podem expandir a linguagem SQL padrão
ANSI, desde que os comandos básicos sejam aceitos.
Instruções da DDL
• SQL oferece três instruções para definição
do esquema da base de dados:
– Create Table
• define a estrutura de uma tabela, suas restrições de
integridade e cria a tabela vazia
– Drop Table
• elimina a tabela da base de dados
– Alter Table
• permite modificar a definição de uma tabela
– Oracle cria o BD como parte da instalação do
software
– INGRES tem um utilitário
Observações sobre a definição de tabelas
• Em SQL/2 o conjunto de domínios de valores
de atributos é fixo.
• Desejável (SQL/3, poucos produtos
implementam)
• domínio definível pelo usuário (exemplo: dias da
semana, meses do ano, …)
• Nos SGBD comerciais são oferecidos
domínios adicionais aos do padrão (CHAR,
VARCHAR, INTEGER, REAL,…) destinados
a aplicações especiais como DATE,
CURRENCY e domíniospara armazenar
campos longos (BLOBS, até 2 gigabytes)
destinados a conter imagens, sons, vídeos,
etc. (maioria aparece no SQL/2)
Observações sobre a definição de tabelas
• A cláusula NOT NULL especifica que uma
coluna não admite o valor vazio (requerido
para colunas que sejam chave primária)
• Default é NULL permitido (exceto Sybase e
SQL/Server antigos)
• As colunas de uma tabela são classificadas
na ordem de sua definição (linhas não tem
classificação)
2
TIPOS DE DADOS(DATATYPES)
LIMITES DO ORACLE RDBMS
ITEM
Tabela na Base de dados
Linhas por Tabelas
Colunas por tabelas
Indices por tabelas
Tebelas ou views joined em
uma query
Niveis de ninho de subqueries
Caracteres em um nome
Colunas por índices
LIMITE
Não há limites.
Não há limites
254
Não há limites
Não há limites
30
255
16
Comando para criar Tabela
CREATE TABLE DEPT
(DEPTNO NUMBER(2) ,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
Eliminando uma tabela
• Para eliminar completamente uma tabela
(vazia ou não) da base de dados, é usada a
instrução:
– DROP TABLE ALUNO;
• 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)
TIPO
Char(n)
Varchar2(n)
Long
Number(p,s)
Raw
Long Raw
Date
DESCRIÇÃO DO TIPO DE DADO
Tamanho Fixo, pode conter uma seqüência de 1 a 255
bytes alfanuméricos;
Tamanho Variável, pode conter uma seqüência de 1 a
2000 bytes - alfanuméricos.
Tamanho Variável até 2 Gigabytes alfanuméricos
nota : só pode existir uma coluna long em cada tabela
Numérico com sinal e ponto decimal,
sendo precisão de 1 a 38 dígitos
Binário - Variável até 255 bytes
Binário - Variável até 2 gigabytes - imagem
Data c/ hora, minuto e segundo
Outro Exemplo:
Create table ALUNO
(cd_aluno number(5) not null
nm_aluno varchar2(30) not null);
Alterando uma Tabela
• A instrução ALTER TABLE serve para
modificar a definição original da tabela.
• Nem todas modificações são permitidas
• Primeiros SGBDs e SQL/1 somente
permitiam adicionar colunas
– Exemplo
• DROP TABLE aluno RESTRICT;
– Exclui a tabela somente se não existirem visões definidas
com base na tabela
• DROP TABLE ALUNO CASCADE CONSTRAINT;
– Exlui a tabela com todas as constraints relacionadas
3
Alterando a definição de uma Tabela
• Para modificar a estrutura de tabelas já
existentes na base de dados, há uma
instrução que permite adicionar colunas a
tabelas:
– ALTER TABLE Embarq ADD DataEmbarq DATE
• Observe-se que:
– 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
– Não pode ser especificada a cláusula NOT NULL já que a
coluna é criada com o valor vazio
Alterando a definição de uma Tabela
•
•
SGBD não permite a alteração desejada
Para fazer a alteração:
1. Armazenar o conteúdo da tabela em tabela
temporária ou arquivo do sistema operacional
2. Eliminar todas referencias a tabela antiga
3. Eliminar a tabela antiga (DROP TABLE)
4. Definir a nova tabela (CREATE TABLE)
5. Carregar a nova tabela a partir da tabela
intermediária ou arquivo do sistema operacional
criado no passo 1
6. Reincluir as referencias à tabela
Restrições de Chave
Verificando estrutura da tabela
Desc nome_tabela;
• No SQL original (System R) e no SQL padrão
original (86) não havia cláusulas para
especificar chaves.
• A única maneira de definir chave primária era
através da criação de um índice sem
duplicatas sobre a coluna.
• Não havia forma declarativa de definir chaves
estrangeiras.
– SGBD não dava suporte a integridade referencial.
– Usuário é obrigado a programar os testes de
chaves em sua aplicação
Integridade Referencial nos SGBD
• Padrão (86/89) foi estendido para
especificar chaves:
– primária
– estrangeira
– alternativa (unique key)
Praticamente todos produtos comerciais
incluem a definição de
Integridade Referencial
• As integridades dos dados são definidas
através de objetos Constraints, que podem
ser:
–
–
–
–
–
Not null
Unique Key
Primary Key
Check
Foreign Key
4
Not Null
Não permite a entrada de valores nulos em uma determinada coluna.
Constraint Primary Key
• Identifica unicamente uma tabela
• Exemplo:
Exemplo:Create table ALUNO1 (cd_aluno number(5) not null,
nm_aluno varchar2(30) not null);
– Create table ALUNO2 (cd_aluno number(5) not null,
nm_aluno varchar2(30) not null,
constraint aluno_pk primary key (cd_aluno));
Constraint Unique Key
Constraint Check
É definido para garantir que o valor de uma coluna seja único.
Exemplo Create table ALUNO3 (cd_aluno number(5) not null,
nm_aluno varchar2(30) not null,
cd_cpf varchar2(14),
constraint aluno_pk primary key(cd_aluno),
constraint alu_cpf_uk unique(cd_cpf));
Constraint Check
Exemplo 2:
ALTER TABLE Cliente ADD CONSTRAINT
ValidaCampos CHECK
(TipoCli=“Pessoa
Física”
AND CIC IS NOT NUL
AND CGC IS NULL)
OR
(TipoCli=“Pessoa Jurídica”
AND CIC IS NULL
AND CGC IS NOT NULL)
• São validações de colunas.
• Exemplo 1:
– Create table Aluno (cd_aluno number(5) not null,
nm_aluno varchar2(30),
tp_sexo char(1),
constraint aluno_pk primary key (cd_aluno),
constraint sexo_ck check (tp_sexo in (‘M’,’F’));
Constraint Foreign Key
• Coluna que referencia a chave primária de outra
tabela, indicando um relacionamento entre ambas as
tabelas envolvidas.
5
Foreign Key
FOREIGN KEY- Define a coluna na tabela filho que possua a
chave estrangeira
REFERENCES - Identifica a tabela e a coluna da tabela pai
ON DELETE CASCADE - Indica que quando uma linha na tabela
pai é deletada, as linhas dependentes na tabela filho também são
deletadas.
ALTER TABLE CONSTRAINT
• ALTER TABLE permite incluir ou excluir
restrições de chave
– ALTER TABLE Representantes DROP PRIMARY
KEY
• Para excluir uma chave estrangeira é
necessário que ela tenha recebido um nome
quando de sua definição
– ALTER TABLE Representantes ADD FOREIGN
KEY (CodFilial) REFERENCES Filiais
– ALTER TABLE Representantes DROP FOREIGN
KEY (CodFilial) REFERENCES Filiais
Gerenciando Constraints
• Comando ALTER TABLE Aluno
– ADD constraint aluno_pk primary key
(nm_aluno);
– ENABLE constraint mat_alu_fk;
– DISABLE constraint mat_alu_fk;
– DROP constraint mat_alu_fk;
Exemplo de Foreign Key
Create table matricula(cd_curso number(5) not null,
cd_aluno number(5) not null,
dt_matricula date not null,
constraint mat_pk primary key (cd_curso,cd_aluno),
constraint alu_mat_fk foreign key (cd_aluno)
references aluno(cd_aluno));
Visualizando as colunas
associadas as constraints
(Oracle)
Use a visão User_cons_columns
Exemplo:
Select constraint_name, column_name
from User_cons_columns
where table_name = ‘EMP’;
Consultando Constraints
Use a tabela User_constraints para visualisar todas as
definições e nomes das constraint.
Exemplo:
Select constraint_name, constraint_type, search_condition
from user_constraints
where table_name= ‘EMP’;
6
Referências Bibliográficas
• KORTH, Henry F. & SILBERSCHATZ,
Abraham. Sistemas de Bancos de Dados, São
Paulo. Ed. Makron Books, 1999.
• HEUSER, Carlos Alberto. Projeto de Banco de
Dados. 4ª Edição. Ed. Sagra, 2001.
• FERNANDES,
Lúcia.
Oracle
9i
Para
Desenvolvedores Oracle Developer 6i Curso
Completo. Ed. Axcel. 2002.
• ABBEY, Michael. Oracle: guia do usuário. São
Paulo: Markon Books, 1997.
7