IBD Aula 6

Prévia do material em texto

Implementação de Banco de Dados - Aula 6
Professor Daniel Silos - 4ª edição - 2015
dsilos@live.estacio.br
Objetivos da aula
• Distinguir os SubConjuntos da Linguagem 
Sql
• Escrever Comandos para criar, alterar e 
eliminar tabelas
Divisão da Linguagem SQL:
• DDL – Data Definition Language
• DRL ou DQL– Data Retrieve Language / 
Data Query Language
• DML - Data Manipulation Language
• DCL - Data Control Language
• DTL – Data Transaction Language
Divisão da Linguagem SQL:
• DDL - Create, Alter, Drop, Rename, Truncate: 
Permitem a criação e definição de objetos 
como tabelas, views (visões) e outros objetos 
no banco de dados.
• DRL ou DQL – SELECT: É o comando mais 
comum do SQL. Utilizado amplamente para 
recuperação (consulta) dos dados de uma 
base.
Divisão da Linguagem SQL:
• DML - Insert, Delete, Update: Comandos de 
manipulação dos dados. Usados nas 
aplicações que mantém a base de informações 
com inserções, atualizações e deleções de 
dados.
• DCL - Grant, Revoke: São utilizados para 
atribuir ou remover direitos de acesso a 
objetos do banco de dados e suas estruturas.
Divisão da Linguagem SQL:
• DTL – Commit, Rollback: Comandos 
relacionados à transações. Uma transação é 
confirmada (Commit) ou o banco permanece 
no estado anterior ao início da transação 
(rollback).
Elementos que Compõem uma Tabela
A tabela é a forma básica de armazenamento de
informações em um sistema gerenciador de banco
de dados relacional e por isso deve conter um
conjunto de elementos, alguns opcionais, na sua
composição e que são:
• Nome
• Colunas
• Tipos de Dados
• Restrições (Constraints)
Nome:
• Em geral é herdado do nome da entidade
que a originou no modelo conceitual, em
maiúsculas, sem espaços ou caracteres
especiais, sendo estes substituídos por
sublinhado (underscore).
Exemplo:
Entidade Tabela
Empregado Empregado
Item Nota Fiscal Item_Nota_Fiscal
Colunas:
• Tal como o nome da tabela, origina-se do
modelo conceitual, sendo mapeados dos
atributos da entidade, sem acentos e
caracteres especiais, em minúsculas e,
preferencialmente, utilizando nomes os
mais curtos possíveis de forma significativa.
Exemplo:
Atributo Coluna
Matrícula_Empregado Mat_Emp
Data_de_Validade Data_valid
Tipos de Dados:
• Cada coluna de uma tabela, tem que ter
um tipo de dado definido e único,
obedecendo a lista de tipos definidos pelo
SGBD. No Oracle existe uma diversidade
grande de tipos e alguns são descritos na
tabela a seguir:
Exemplo:
Tipos de Dados Descrição
VARCHAR2(tam) ou 
VARCHAR(tam)
Caracter de tamanho variável, podendo atingir de 1 até 4000 
bytes, sendo limitado a tam.
CHAR(tam) Caracter de tamanho fixo, podendo tam ser especificado no 
máximo de 2000 caracteres.
NUMBER Número de ponto flutuante podendo atingir a magnitude 
entre 1.0 x 10 -130 e 9.9....9 x 10125 dígitos significativos.
NUMBER(I,d) Numérico com precisão, podendo atingir em i de 1 a 38 
dígitos significativos
inteiros e d representa o valor máximo em decimais, que 
pode variar de –84 a
127.
Exemplo:
Tipos de Dados Descrição
DATE Data e hora, podendo ser representados entre 01 de janeiro 
de 4712 AC até 31 de dezembro de 4712 DC.
LONG Caracter de tamanho variável, podendo atingir até 2 Gb.
ROWID String base 64 representando o endereço único de uma linha 
numa tabela, através da pseudo coluna ROWID
Tipos de Dados de Data e Hora
• DATE
–usado para armazenar valores de data e Hora
em diversos formatos, como os formatos
‘DD/MM/YYYY’, ‘DD-MMM-YYYY’, ou
‘HH24MiSS DD/MM/YYYY’, por exemplo.
Tipos de Dados de Data e Hora
• TIMESTAMP:
– Tem componentes de DATA e HORA
bem complexos, podendo-se incluir
detalhes de fuso horários diferentes
e formatos de horários.
Criando tabelas – comando CREATE TABLE
• Estrutura básica do comando:
Sintaxe:
CREATE TABLE [schema].nome_da_tabela (
nome_col1 tipo_col1 [default vl_default_col1] [restri_col1], 
nome_col2 tipo_col2 [default vl_default_col2] [ restri_col2],
... , 
restri_tab1, [restri_tab2] 
);
Onde
Palavra Chave Descrição
SCHEMA É o schema onde a tabela será criada. Por default é o
próprio schema do usuário que está criando.
NOME_DA_TABELA Nome que será atribuído a tabela
Nome_coln Tipo de dado atribuído à coluna n
default vl_default_coln Valor default a ser atribuído à coluna, podendo ser um
literal, ou uma expressão, função, de mesmo tipo que
o atribuído a coluna. Este valor é automaticamente
atribuído ao campo caso nenhum valor seja
especificado.
Onde
Palavra Chave Descrição
restri_coln Define uma restrição de integridade automática a qual
os campos da coluna devem obedecer.
restri_tabn Define uma restrição de integridade automática a qual
toda a tabela deve obedecer
Restrição Descrição
NOT NULL Coluna com preenchimento obrigatório.
PRIMAY KEY Identifica exclusivamente cada linha da tabela.
FOREIGN KEY Estabelece um relacionamento de chave estrangeira com
um valor de uma coluna (chave primária ou exclusiva)
existente na mesma tabela ou em outra tabela.
CHECK Especifica uma condição que deve ser verdadeira.
UNIQUE
(Chave Alternada)
Requer que a coluna ou de um conjunto de colunas seja
Exclusivos.
Restrições básicas
CREATE TABLE
Exemplo:
CREATE TABLE Departamento (
DNumero NUMBER(3) NOT NULL,
Dnome VARCHAR2(10) NOT NULL,
DNumero NUMBER(3) NOT NULL,
Ramal NUMBER(4),
DataCriacao DATE NOT NULL
);
CREATE TABLE
• A partir do SQL 2 é possível utilizar o comando CREATE TABLE
para especificar os atributos de chave primária , secundária, e
restrições de integridade referencial (chaves estrangeiras).
• Atributos de chaves podem ser especificados por meio das frases
PRIMARY KEY e UNIQUE
CREATE TABLE Empregado (
Mat_Emp INT,
Dnumero INT NOT NULL, 
Emp_Nome VARCHAR2(60) NOT NULL,
EmpTel NUMBER(11) NOT NULL,
PRIMARY KEY (Mat_Emp),
UNIQUE (EmpTel),
FOREIGN KEY (Dnumero) REFERENCES Departamento 
);
Exemplo: 
CREATE TABLE Curso (
idCurso NUMBER(3), 
PRIMARY KEY (idCurso) , 
Descricao VARCHAR2(30),
CHECK (Descricao IS NOT NULL)
);
Exemplo: 
CREATE TABLE Curso (
idCurso NUMBER(3) PRIMARY KEY , 
Descricao VARCHAR2(30) NOT NULL,
);
OU: 
CREATE TABLE Curso (
idCurso NUMBER(3), 
Descricao VARCHAR2(30) NOT NULL,
PRIMARY KEY (idCurso)
);
Exemplo: 
CREATE TABLE Cargo ( 
idCargo NUMBER(3) PRIMARY KEY , 
Descricao VARCHAR2(30) NOT NULL , 
Salario_Min NUMBER(7,2) DEFAULT 900
CHECK (Salario_Min >= 900) , 
Salario_Max NUMBER(18,2) DEFAULT 900
CHECK (Salario_Max >= 900) , 
Escolaridade CHAR
CHECK (Escolaridade IN ('F','M','S') ) 
) ;
Nomeando Restrições: 
CREATE TABLE Cargo ( 
idCargo NUMBER(3) PRIMARY KEY , 
Descricao VARCHAR2(30) NOT NULL , 
Salario_Min NUMBER(7,2) DEFAULT 900, CONSTRAINT
CK_salario_minimo CHECK (Salario_Min >= 900) , 
Salario_Max NUMBER(18,2) DEFAULT 900, CONSTRAINT
CK_salario_maximo CHECK (Salario_Max >= 900 AND
Salario_Max > Salario_Min ) , 
Escolaridade CHAR
CHECK (Escolaridade IN ('F','M','S') ) 
) ;
CREATE TABLE Empregado (
idEmpregado NUMBER(6) ,
Nome VARCHAR2(50) , idCargo NUMBER(3) , 
Endereco VARCHAR2(50), Bairro VARCHAR2(20), 
Cidade VARCHAR2(20), idChefe NUMBER(6), 
UF CHAR(2) DEFAULT 'RJ', DataNasc DATE, 
Sexo CHAR, Salario NUMBER(18,2),
FOREIGN KEY (idChefe) 
REFERENCES Empregado(idEmpregado)
) ;
ALTER TABLE
• O comando ALTER TABLE é utilizado para adicionar atributos
em uma relação existente:
ALTER TABLE r ADD A D
onde A é o nome do atributo a ser adicionado na relação r e D
é o domínio de A.
– Todas as tuplas na relação são designadas com o valor nulo
(null ) para o novo atributo.
• ALTER TABLE podeser também utilizado para remover
atributos de uma relação:
ALTER TABLE r DROP COLUMN A
onde A é o nome de um atributo de relação r
– A remoção de todos os atributos não é suportado por diversos
SGBDs.
Comando ALTER TABLE
• Utilizado para adicionar um atributo a uma
relação e outros detalhes, como definição de
chaves e outras restrições.
• O novo atributo terá valores nulos (NULLs) em
todas as tuplas (registros/linhas ) logo após a
execução do comando; portanto, a restrição NOT
NULL não é permitida para este atributo.
Exemplo:
ALTER TABLE Empregado 
ADD Cargo VARCHAR(12);
• Os usuários de BD precisam ainda entrar com
um valor para o novo atributo Cargo para cada
tupla da tabela Empregado. Isto pode ser
realizado utilizando-se o comando UPDATE
(próxima aula).
No Oracle (PL/SQL):
ALTER TABLE Empregado 
ADD Cargo VARCHAR (12);
ALTER TABLE DEPARTAMENTO ADD (
RAMAL NUMBER (4), 
LOCALIZACAO VARCHAR2 (15), 
DATA_CRIACAO DATE
);
Adicionar múltiplos atributos:
Renomeando uma tabela
• Exemplo: 
–ALTER TABLE funcionario 
RENAME TO colaborador
Renomeando a coluna de uma tabela
• Exemplo: 
–ALTER TABLE colaborador RENAME
COLUMN nomeFuncionario TO
nomeColaborador; 
Eliminando a coluna de uma tabela
• Exemplo: 
–ALTER TABLE colaborador DROP
COLUMN DescCargoFuncionario; 
Restrição de Integridade
•Restrições básicas
– Podem ser definidas por comandos SQL como 
parte da criação de uma tabela. 
– Uso: impor regras no nível da tabela, sempre 
que uma operação for executada.
– Impede que uma tabela seja removida, caso 
exista algum relacionamento com outra tabela. 
ALTER TABLE Empregado ADD CONSTRAINT
PK_Empregado PRIMARY KEY (idEmpregado);
ALTER TABLE Empregado ADD CONSTRAINT
NN_Func_Nome CHECK (nome IS NOT NULL) ;
ALTER TABLE Empregado ADD CONSTRAINT
CK_Func_Sexo CHECK (Sexo IN ('M','F')) ;
Adicionar Restrição:
ALTER TABLE Empregado ADD CONSTRAINT
CK_Func_UF 
CHECK (UF IN ('AM', 'AP', 'PA', 'AC', 'RO', 'RR',
'PI', 'CE', 'MA', 'RN', 'PE', 'PB', 'BA', 'SE', 'AL',
'MT', 'MS', 'GO', 'TO', 'DF', 'RS', 'SC', 'PR', 'RJ',
'SP', 'MG', 'ES' )
);
Adicionar Restrição:
ALTER TABLE Empregado 
ADD CONSTRAINT FK_Func_Cargo 
FOREIGN KEY (idCargo) 
REFERENCES Cargo (idCargo);
Adicionar Restrição:
ALTER TABLE Empregado 
ADD CONSTRAINT FK_Func_Chefe 
FOREIGN KEY (idChefe) 
REFERENCES Empregado (idEmpregado);
Adicionar Restrição:
ALTER TABLE Empregado ADD
CONSTRAINT NN_Func_Nome CHECK
(nome IS NOT NULL) ;
Adicionar Restrição:
ALTER TABLE DEPARTAMENTO 
ADD CONSTRAINT NN_NOMEDEP 
CHECK (NOMEDEP IS NOT NULL);
ALTER TABLE DEPARTAMENTO 
DROP CONSTRAINT NN_NOMEDEP;
Remover Restrição:
ALTER TABLE DEPARTAMENTO 
DISABLE CONSTRAINT NN_NOMEDEP;
Desabilitar Restrição:
ALTER TABLE DEPARTAMENTO 
ENABLE CONSTRAINT NN_NOMEDEP;
Habilitar Restrição:
CREATE TABLE DEPARTAMENTO (
IDDEP INT PRIMARY KEY,
NOMEDEP VARCHAR2(30)
);
Possível modificação:
ALTER TABLE DEPARTAMENTO MODIFY
IDDEP NUMBER(3);
Modificar Tabela:
No Oracle (PL/SQL):
ALTER TABLE DEPARTAMENTO MODIFY ( 
IDDEP NUMBER(3), NOMEDEP
VARCHAR2(40) NOT NULL UNIQUE
);
Modificar múltiplas colunas:
Comando DROP TABLE
• Utilizado para remover uma relação (tabela) e
suas definições
• A relação não poderá mais ser utilizada em
consultas, atualizações ou em qualquer outro
comando pelo fato de sua descrição não existir
mais.
• Exemplo:
DROP TABLE Dependente;
A partir deste momento o aluno está apto 
a resolver os exercícios da Lista de 
exercícios 6.
Bibliografia
– ELMASRI, R.; NAVATHE, S., Sistemas de Banco de 
Dados. Pearson Education do Brasil, 4ª.Ed 2005. 
– http://www.techonthenet.com/oracle/ acessado 
em 09/09/2015.