5. DB SQL DDL v1

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

Gestão de Banco de Dados
Roberto Harkovsky, MSc
1
SQL (Structured Query Language)
Os 4 tipos de SQL e seus comandos 
Roberto Harkovsky
2
Vamos abordar DDL!!!
Linguagens de definição SQL como DdL
Professor: Roberto Harkovsky
3
SQL como DDL
A Data Definition Language (DDL) é a parte do SQL que permite que tabelas, índices, relações entre tabelas sejam criadas, modificadas e/ou apagadas
Constituída de 3 tipos principais de instruções:
Instruções de CREATE
Instruções de DROP
Instruções de ALTER
Comandos mais comuns
CREATE TABLE – Cria uma nova tabela no DB
ALTER TABLE – Altera uma nova tabela no DB
DROP TABLE – Apaga uma tabela do DB
CREATE INDEX – Cria um índice
DROP INDEX  – Apaga um Índice 
Roberto Harkovsky
4
4
Criação de Tabelas
<nome-tabela> - Nome da tabela a ser criada
<Descrição das colunas> - Lista de colunas da Tabela - campos e seus respectivos tipos de dados (smallint, varchar2, varchar, integer, money, decimal, float, real, date, time, timestamp, logical)
<restrições de integridade> - É a lista de colunas que são tratadas como chave primária, chaves candidatas,chaves estrangeiras, checks etc
Exemplo
Roberto Harkovsky
5
CREATE TABLE tabela (<Descrição das colunas>) , [NOT NULL], (<restrições de integridade>)
Pessoa(nome, sobrenome, endereco, idade)
CREATE TABLE pessoa  ( 
nome varchar(20), 
sobrenome varchar(20), 
endereco varchar(40), 
idade int NOT NULL) 
5
SQL como DDL
Tipos de variáveis (SQL Server)
Cada coluna da tabela tem um nome e um tipo de dados;
Alguns tipos de variáveis mais usados são:
CHAR(n) – texto tamanho fixo
Varchar(n) – texto tamanho variável (max 8000 caracteres)
Text – texto tamanho variável (max 2M caracteres)
TINYINT – inteiro variando de 0 até 256
SMALLINT– inteiro variando de -32.768 até 32.768 
INT– inteiro variando de -2.147.483.648 até 2.147.483.647
BIGINT - inteiro variando de –92.23.372.036.854.775.808 até 9.223.372.036.854.775.807
BOOLEAN - Somente pode assumir os valores 0 ou 1. Utilizado para armazenar
valores lógicos.
Numeric – (-1038 +1) a (1038 -1)
decimal – (-1038 +1) a (1038 -1)
Datetime – dia e hora
Float
Roberto Harkovsky
6
6
Alterando uma Tabela
Ex: Incluir o nome fantasia da editora na tabela Editora
	ALTER TABLE Editora ADD NFantasia varchar(100)
Ex: Alterar o tamanho da coluna Nome fantasia para 50
	ALTER TABLE Editora ALTER COLUMN NFantasia varchar(50)
Ex: Apagar a coluna Nome fantasia para 50
	 ALTER TABLE Editora DROP COLUMN NFantasia 
Roberto Harkovsky
7
ALTER TABLE tabela ADD nome_coluna tipodado,... 
ALTER TABLE tabela ALTER COLUMN nome_coluna tipodado,... 
ALTER TABLE tabela DROP COLUMN nome_coluna 
Editora(código, nome)
7
Exemplos
As tabelas da nossa base de médicos
Roberto Harkovsky
8
CREATE TABLE Funcionarios
(
 codf int PRIMARY KEY, 
 CPF char(11) UNIQUE, 
 nome char(40) Not Null, 
 idade char(2) Not Null, 
 cidade char(20) NOT NULL, 
 salario int
) 
CREATE TABLE Medicos
(
codm int PRIMARY KEY,
CPF char(11) UNIQUE, 
nome Varchar(40) Not null, 
idade int Not null, 
cidade char(15) Not null, 
especialidade char(30) Not null, 
nroa int
)
CREATE TABLE Ambulatorio
(
nroa int PRIMARY KEY, 
andar char(2) not null, 
capacidade int not null 
)
8
Apagando uma Tabela
Ex: Apagar a tabela Editora 
	DROP TABLE Editora
 Muito cuidado com este comando!!!
Roberto Harkovsky
9
DROP TABLE tabela 
9
Restrições de integridade
Roberto Harkovsky
10
Checando Integridade
Roberto Harkovsky
11
Uma Restrição é uma propriedade associada a uma coluna, ou conjunto de colunas numa tabela, que previne certos tipos de inconsistências de valores de dados serem colocados nestas coluna(s). 
Restrições são utilizados para reforçar integridade dos dados, garantindo uma exatidão e confiabilidade dos dados no BD.
11
Checando Integridade
Roberto Harkovsky
12
As categorias de integridade de dados são:
Integridade de entidade
Garante que não existe 2 linhas (tuplas) duplicadas
Integridade de Domínio
Reforça a validação dos dados, em termos de tipo, formato ou intervalo de valores.
Integridade Referencial
Garante que as linhas(tuplas) não podem ser apagadas se estão sendo referenciadas por outra tabela
Integridade definida pelo usuário 
Garante algumas regras de negócio, não cobertas por outras restrições.
 Existem 2 formas de declarar restrições no SQL
Na declaração dos campos
Após a declaração dos campos
12
Restrições: chave primária
PRIMARY KEY 
Cria a restrição de chave primária
Exemplo:
CREATE TABLE Empregado ( 
Codm INT PRIMARY KEY,
.....
)
CREATE TABLE Empregado ( 
Codm INT,
.....
CONSTRAINT PK_medico PRIMARY KEY (Codm)
)
Roberto Harkovsky
13
[coluna] [tipo] [ outras restriçoes] PRIMARY KEY 
CONSTRAINT <nome_restrição> PRIMARY KEY ([coluna])
13
Restrições: chave estrangeira
FOREIGN KEY 
Cria a restrição de chave estrangeira
Onde condições podem ser:
SET NULL – coloca NULL na tabela referenciada
CASCADE – Apaga o(s) registro(s) na tabela referenciada
RESTRICT – Impede a exclusão se existirem linhas em outras tabelas que referenciem esta linha
Exemplo:
Depto int FOREIGN KEY (Depto) REFERENCES Departamento(depto) ON DELETE CASCADE
CONSTRAINT Depto_FK FOREIGN KEY (Depto) REFERENCES Departamento(depto) ON DELETE SET NULL
Roberto Harkovsky
14
[coluna] [tipo] FOREIGN KEY ([coluna]) REFERENCES <tabela>(<coluna>) ON DELETE <condições>
CONSTRAINT <nome_restrição> FOREIGN KEY ([coluna]) REFERENCES <tabela>(<coluna>) ON DELETE <condições>
14
E se inserirmos uma tupla em Matricula, mas nenhum aluno correspondente? 
INSERT é rejeitado (chaves estrangeiras são restrições)!
E se apagarmos um estudante?
Desativar a exclusão
Remover todos os cursos para esse estudante
Colocar NULL
chaves estrangeiras e 
operações de atualização
3 opções possíveis- Escolha do DBA
Estudante(sid: string, nome: string, CR: float)
Matricula(student_id: string, cid: string, grau: string)
Classe(cid: string , nome: string, horario: string)
Roberto Harkovsky
15
15
Checando Integridade
Restrição de Domínio
Limita valores de uma coluna
Salary int CHECK (salary > 0 and Depto=“TI”) 
CONSTRAINT check_sale CHECK (salary > 0 and Depto=“TI”) 
UNIQUE 
Garante que o valor é único na tabela (para aquela coluna)
[coluna] [tipo] UNIQUE 
CONSTRAINT <nome_restrição> UNIQUE([coluna]) 
Idade number(3) UNIQUE
CONSTRAINT UK_Idade UNIQUE (idade)
Roberto Harkovsky
16
[coluna] [tipo] CHECK ([condição]) 
CONSTRAINT <nome_restrição> CHECK ([condição]) 
16
Checando Integridade
NOT NULL 
Não aceita valores nulos na coluna
[coluna] [tipo] OT NULL
Salary int NOT NULL 
Roberto Harkovsky
17
Exemplos
As tabelas da nossa base de médicos
Roberto Harkovsky
18
CREATE TABLE Funcionarios
(
 codf int PRIMARY KEY, 
 CPF char(11) UNIQUE, 
 nome char(40) Not Null, 
 idade char(2) Not Null, 
 cidade char(20) NOT NULL, 
 salario int CHECK (salario>0)
) 
CREATE TABLE Medicos
(
codm int PRIMARY KEY,
CPF char(11) UNIQUE, 
nome Varchar(40) Not null, 
idade int Not null, 
cidade char(15) Not null, 
especialidade char(30) Not null, 
nroa int 
CONSTRAINT FK_nroa REFERENCES AMBULATORIO(nroa)
)
CREATE TABLE Ambulatorio
(
nroa int PRIMARY KEY, 
andar char(2) NOT NULL, 
capacidade int NOT NULL
)
18
Restrições Criando Tabelas 
Exemplo1 – restrições após declarações
Roberto Harkovsky
19
CREATE TABLE Empregado ( 
EmpId INT NOT NULL, 
CPF char(11) UNIQUE,
Nome VARCHAR(30) NOT NULL, 
Sobrenome VARCHAR(30) NOT NULL, 
Endereco VARCHAR(100) NOT NULL, 
Depto int default 10 NOT NULL, 
Salario int NOT NULL, 
	CONSTRAINT PK_Emp PRIMARY KEY (EmpId),
	CONSTRAINT FK_Depto FOREIGN KEY (Depto) 
	 REFERENCES Departamento(depto) ON DELETE cascade,
	CONSTRAINT check_sale CHECK (salario > 0)
) 
Declaração
Restrição
19
Restrições Criando Tabelas 
Exemplo2 – restrições na declaração da coluna
Roberto Harkovsky
20
CREATE TABLE Empregado ( 
EmpId INT NOT NULL PRIMARY
KEY, 
CPF char(11) UNIQUE,
Nome VARCHAR(30) NOT NULL, 
Sobrenome VARCHAR(30) NOT NULL, 
Endereco VARCHAR(100) NOT NULL, 
Depto int default 10 NOT NULL FOREIGN KEY (Depto) 
	 REFERENCES Departamento(depto) ON DELETE cascade, 
Salario int NOT NULL CHECK (salary > 0)
) 
Declaração
Restrição
20
Agora é com vocês...
Dado o esquema acima, no seu ambiente SQL crie as tabelas Departamento e empregado considerando:
EmpId, DepId e salario são inteiros; o resto são strings variaveis;
EmpId é Chave primaria de “Empregado”, e DepId é a chave primária de “Departamento”
CPF é String de 11 posições e não pode ter duplicidade
Salario tem que ser um numero positivo
Depto é chave estrangeira, associado a chave primaria da tabela “Departamento”
Roberto Harkovsky
21
Empregado(EmpId, nome, sobrenome, cpf, endereço, salario, depto)
Departamento(DepId, nome)
Restrições Alterando Tabelas 
exemplos de alteração de tabelas
Roberto Harkovsky
22
ALTER TABLE employee 
	ADD CONSTRAINT pk_employee PRIMARY KEY (EmployeeId) 
ALTER TABLE employee 
	DROP CONSTRAINT pk_employee 
ALTER TABLE employee 
	NOCHECK CONSTRAINT check_sale 
ALTER TABLE Books 
	ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID) 
	ON DELETE CASCADE 
22
Atividade 5
Criação de um Schema 
Livraria
Roberto Harkovsky
23
Visões
Roberto Harkovsky
24
Visões
Tabelas virtuais cujos conteúdos são alimentados por tabelas reais;
Definidos através de comandos SELECT
Utilizado para disponibilizar parte dos dados de uma tabela real
Tabela original permanece preservada
As consultas às visões são iguais àquelas realizadas em tabelas
Não são atualizáveis!!!
Roberto Harkovsky
25
25
Visões
Roberto Harkovsky
26
Ex1: Aniversariantes de Setembro:
Create View aniversariantes_setembro
As
Select nome, nascimento
From emp
Where month(nascimento) = 09
Obs: [WITH CHECK OPTIONS]
Força todas declarações de alteração de dados a seguir os critérios estabelecidos no comando_de_seleção
CREATE VIEW nome [col1, col2,...]
AS
SELECT ...
[WITH CHECK OPTIONS]
DROP VIEW nome
26
Visões
Exemplos
Roberto Harkovsky
27
Ex2: Criar uma visão com todos os atributos dos projetos do departamento 2030:
CREATE VIEW Projetos2030 AS
SELECT *
FROM Projeto
WHERE NumDpto = 2030
27
Visões
Exemplos
Roberto Harkovsky
28
Ex3: Criar uma visão com todos os funcionários e suas datas de admissão
CREATE VIEW DataContratacao_view AS 
SELECT c.Nome, c.sobrenome, e.Matricula, e.DataContratacao 
FROM Empregados e JOIN Contatos.Pessoais c on e.ContactID = c.ContactID ; 
Visões
Exemplos
Roberto Harkovsky
29
Ex4: Listar todos os produtos ativos (produtos que não estão descontinuados).
CREATE VIEW Produtos_Ativos AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
Atividade 6
VISÕES
Roberto Harkovsky
30