#11 Conteúdo SQL Comandos DDL (CREATE, ALTER e DROP TABLE)

Prévia do material em texto

Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 1 de 13 
Conteúdo: 
1. Comandos DDL: 
1.1. CREATE TABLE; 
1.2. ALTER TABLE; 
1.3. DROP TABLE; 
2. Exercício. 
3. Referências; 
 
Atividade a ser desenvolvida: 
 Implementação de um modelo E-R proposto. 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 2 de 13 
1. Comandos DDL: 
1.1. CREATE TABLE 
Utiliza-se o comando CREATE TABLE para a criação de uma nova tabela no banco de dados. Essa tabela não 
possui registros quando é criada. A inserção de registros dar-se-á através do comando INSERT. 
 
Pode-se definir restrições aos dados a serem inseridos na tabela. Essas restrições podem ser de 2 tipos: 
o Restrição de coluna: Utilizada quando a restrição afeta apenas aquela coluna a qual está vinculada; 
o Restrição de tabela: Utilizada quando a restrição não afeta apenas a uma coluna. Podemos utilizar a 
restrição de tabela mesmo quando ela afeta uma coluna apenas. 
 
1.1.1. SINTAXE 
CREATE TABLE nome_da_tabela ( 
 nome_campo1 <tipo_dado> [DEFAULT expressão_padrão] 
 [NULL | NOT NULL] restrição_campo, 
 ..., 
 nome_campon <tipo_dado> [DEFAULT expressão_padrão] 
 [NULL | NOT NULL] restrição_campo, 
 CONSTRAINT [nome_da_restrição1] definições_restrição1, 
 ..., 
 CONSTRAINT [nome_da_restriçãon] definições_restriçãon 
); 
 
O parâmetro nome_da_tabela especifica o nome da tabela a ser criada. 
 
O parâmetro nome_campo especifica o nome do campo a ser criado na tabela. O tipo de dado que o 
campo irá armazenar é especificado na cláusula tipo_dado. 
 
Normalmente ao se inserir (comando INSERT) um novo registro na tabela, informa-se os valores a 
serem armazenados no novo registro mas, caso o valor de um dos campos não seja informado no 
momento da inserção esse campo receberá o valor NULL (nulo). Há a alternativa de se especificar o 
valor padrão para um campo quando o valor desse não é informado no momento da inserção utilizando 
a cláusula DEFAULT, onde o parâmetro expressão_padrão especifica esse valor. 
 
A cláusula NULL | NOT NULL especifica se um campo permite (NULL) ou não (NOT NULL) valores 
nulos do campo 
 
A cláusula CONSTRAINT define a restrição a ser aplicada na tabela. O parâmetro nome_da_restrição 
é utilizado para se definir um nome para a restrição. Esse parâmetro pode ser omitido. 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 3 de 13 
O parâmetro definições_restrição é utilizado para se definir o tipo de restrição a ser aplicado. Ele 
pode ser: 
 PRIMARY KEY ( nome_da_coluna [, ... ] ) 
Atribui uma restrição de chave primária a tabela. Cada tabela pode conter apenas uma restrição de 
chave primária, porém a restrição de chave primária pode conter um ou mais campos da tabela. 
 
A restrição de chave primária impõe que o campo, ou conjunto de campos, só poderão conter 
valores únicos (valores que não podem ter duplicação) e não nulos. 
 
 UNIQUE ( nome_do_campo [, ... ] ) 
Define o caráter de unicidade (não aceitar valores iguais) em um ou mais campos da tabela. Para 
efeitos de restrição, os valores null não são levados em consideração por essa restrição. 
 
Nativamente os campos de chave primária (PRIMARY KEY) já possuem essa característica, não 
sendo necessária a configuração deles com essa cláusula. 
 
 CHECK ( expressão ) 
Esta cláusula define uma expressão que limita os valores a serem aceitos pelo campo. 
 
Essa cláusula retorna valores booleanos. As expressões avaliadas como true ou unknown são bem-
sucedidas. Se alguma linha de uma operação de inserção ou de atualização produzir um resultado 
false será lançada uma exceção de erro, e a inserção ou atualização não irá alterar o banco de 
dados. 
 
 FOREIGN KEY ( nome_da_coluna [, ... ] ) 
 REFERENCES tabela_referenciada ( coluna_referenciada [, ... ] ) 
 [ ON DELETE ação ] [ ON UPDATE ação ] 
Esta cláusula especifica uma chave estrangeira na tabela. A chave estrangeira pode requerer um 
ou mais campos como colunas referenciadas. Caso a coluna referenciada seja omitida, FOREIGN 
KEY irá se referenciar a chave primária da tabela_referenciada. 
 
O parâmetro ação pode receber um dos seguintes valores: 
NO ACTION 
Gera um erro de violação de integridade referencial provocado por uma alteração ou 
exclusão de dados. Podendo ser postergável (1). Esse é o valor padrão. 
RESTRICT Idem a NO ACTION, exceto que não pode ser postergável. 
CASCADE 
Exclui os registros de outras tabelas dependentes do registro que está sendo excluído 
ou altera os valores dos campos associadas ao campo que está sendo alterado. 
SET NULL Atribui o valor NULL aos campos que são associados. 
SET DEFAULT Atribui o valor default aos campos que são associados. 
(1) Uma ação postergável significa que a ação só será verificada ao término da execução de todas 
as instruções de alteração ou exclusão de dados dentro de uma transação. 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 4 de 13 
1.1.2. TIPOS DE CAMPOS 
1.1.2.1. NUMÉRICOS 
Tipo Tamanho Faixa de Valores 
smallint 2 bytes -32768 a +32767 
integer 4 bytes -2147483648 a +2147483647 
bigint 8 bytes -9223372036854775808 a 9223372036854775807 
decimal variable Sem limite 
Numeric Variable Sem limite 
real 4 bytes 6 decimais com precisão 
double precision 8 bytes 6 decimais com precisão 
serial 4 bytes 1 a 2147483647 
bigserial 8 bytes 1 a 9223372036854775807 
 
1.1.2.2. MONETÁRIOS 
Tipo Tamanho Faixa de Valores 
Money 4 bytes -21474836.48 a +21474836.47 
 
1.1.2.3. CARACTERES (ALFANUMÉRICOS) 
Tipo Faixa de Valores 
character varying(n), varchar(n) Comprimento variável, limitado a n caracteres 
character(n), char(n) 
Tamanho fixo em n caracteres, preenchido com espaços em 
branco 
text Comprimento ilimitado 
 
1.1.2.4. BOOLEANOS 
Tipo Faixa de Valores 
boolean 
Armazena valores booleanos (TRUE ou FALSE). NULL 
também é aceito por esse tipo. 
 
1.1.2.5. DATA / HORA 
Tipo Tamanho Faixa de Valores 
timestamp [ (p) ] [ without time zone ] 8 bytes 4713 BC a 5874897 AD 
timestamp [ (p) ] with time zone 8 bytes 4713 BC a 5874897 AD 
interval [ (p) ] 12 bytes -178000000 anos a 178000000 anos 
date 4 bytes 4713 BC a 5874897 AD 
time [ (p) ] [ without time zone ] 8 bytes 00:00:00 a 24:00:00 
time [ (p) ] with time zone 12 bytes 00:00:00+1459 a 24:00:00-1459 
 
 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 5 de 13 
1.1.3. EXEMPLOS 
Tomando por base o cenário abaixo, analisemos o modelo E-R e em seguida vejamos a sua 
implementação: 
Gostaria de manter os dados pessoais dos alunos, entre eles a matrícula, o nome e o telefone de contato. 
As turmas possuem aulas diárias em apenas um turno (matutino, vespertino ou noturno), iniciando em uma 
determinada data e possuindo uma data prevista de término. 
Quero também guardar os dados dos professores (CPF e nome). Estes podem ministrar aulas em várias 
turmas ao mesmo tempo, mas em turnos distintos. 
As turmas são compostas por diversos alunos, e nada impede que esse aluno assista aula em diversas 
turmas em horários distintos. 
 
Após a análise, chegamos ao seguinte modelo E-R:Analisando os valores que serão armazenados nas tabelas, montamos o seguinte dicionário de dados: 
 
 Entidade: docentes (armazenar os dados dos docentes) 
 
 
 Entidade: disciplinas (armazenar os dados das disciplinas) 
 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 6 de 13 
 Entidade: cursos (armazenar os dados dos cursos) 
 
 
 Entidade: docentes_disciplinas (Armazenar as associações entre docentes, disciplinas, cursos e 
períodos letivos) 
 
 
Para iniciarmos a criação das tabelas devemos observar que tempos tabelas que possuem campos que 
são dependentes de outros campos em outras tabelas (chaves estrangeiras ou foreign keys). Portanto 
essas tabelas deverão ser criadas primeiro. 
CREATE TABLE docentes ( 
 matricula INTEGER NOT NULL, 
 nome VARCHAR(60) NOT NULL, 
 CONSTRAINT pk_docentes PRIMARY KEY(matricula) 
); 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 7 de 13 
CREATE TABLE disciplinas ( 
 codigo SERIAL, 
 nome VARCHAR(100) NOT NULL, 
 cargahoraria INTEGER NOT NULL, 
 CONSTRAINT pk_disciplinas PRIMARY KEY(codigo), 
 CONSTRAINT ck_cargahoraria CHECK(cargahoraria IN (30, 45, 60, 90, 120)) 
); 
 
 
CREATE TABLE cursos ( 
 codigo INTEGER, 
 nome VARCHAR(200) NOT NULL, 
 CONSTRAINT pk_cursos PRIMARY KEY(codigo), 
 CONSTRAINT ck_cursos_codigo CHECK(codigo <= 99999) 
); 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 8 de 13 
Uma vez criadas as tabelas que não dependem de outras, criamos a tabela que possui as chaves 
estrangeiras. 
CREATE TABLE docentes_disciplinas ( 
 iddocentes_disciplinas SERIAL, 
 periodo VARCHAR(6) NOT NULL, 
 matricula INTEGER NOT NULL, 
 codigo integer NOT NULL, 
 codcurso INTEGER NOT NULL, 
 CONSTRAINT pk_docentes_disciplinas PRIMARY KEY(iddocentes_disciplinas), 
 CONSTRAINT ck_codcurso CHECK(codcurso <= 99999), 
 CONSTRAINT fk_matricula FOREIGN KEY (matricula) 
 REFERENCES docentes (matricula), 
 CONSTRAINT fk_codigo FOREIGN KEY (codigo) 
 REFERENCES disciplinas (codigo), 
 CONSTRAINT fk_codcurso FOREIGN KEY (codcurso) 
 REFERENCES cursos (codigo) 
); 
 
 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 9 de 13 
Uma vez criadas as tabelas, podemos observar no Object Browser como ficou a estrutura do banco: 
 
 
 
1.1. ALTER TABLE 
Comando utilizado para alterar a estrutura de uma tabela. 
 
1.1.1. SINTAXE 
ALTER TABLE <nome_tabela> RENAME TO <novo_nome_tabela>; 
Esta sintaxe permite alterar o nome da tabela. 
 
ALTER TABLE <nome_tabela> RENAME COLUMN <nome_campo> TO <novo_nome_campo>; 
Altera o nome de um campo em uma tabela.. 
 
ALTER TABLE <nome_tabela> ADD COLUMN <especicacoes_novo_campo>; 
Adiciona um novo campo na tabela. Para maiores detalhes sobre especicacoes_novo_campo veja o 
tópico CREATE TABLE. 
 
ALTER TABLE <nome_tabela> ALTER COLUMN <nome_campo> <especicacoes_campo>; 
Permite alterar as especificações de um campo da tabela. Para maiores detalhes sobre 
especicacoes_campo veja o tópico CREATE TABLE. 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 10 de 13 
ALTER TABLE <nome_tabela> DROP COLUMN <nome_campo>; 
Exclui um campo da tabela. 
 
ALTER TABLE <nome_tabela> ADD CONSTRAINT <especicacoes_restrição>; 
Adiciona uma nova restrição na tabela. Para maiores detalhes sobre especicacoes_restrição veja 
o tópico CREATE TABLE. 
 
ALTER TABLE <nome_tabela> DROP CONSTRAINT <nome_restrição>; 
Exclui uma restrição da tabela. 
 
1.1.2. EXEMPLOS 
Vejamos no Object Browser a estrutura da tabela disciplinas. 
 
 
Vemos que temos um campo chamado nome. Se desejarmos alterar o nome desse campo para 
nome_discplina devemos executar o comando abaixo: 
ALTER TABLE disciplinas RENAME COLUMN nome TO nome_disciplina; 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 11 de 13 
O resultado será 
 
 
Vamos agora adicionar o o campo email na tabela docentes. Lembre-se de visualizar a estrutura 
antes e depois do comando. 
ALTER TABLE docentes ADD COLUMN email VARCHAR(100) NOT NULL; 
 
 
1.2. DROP TABLE 
Esse comando é utilizado para excluir a tabela do banco de dados. 
 
Quando se executa esse comando, deve-se observar os seguintes pontos: 
o Ao se excluir uma tabela, todo o seu conteúdo será excluído; 
o Ao se excluir uma tabela que possui um relacionamento, o comando irá dar um alerta. 
 
Cabe ressaltar que o comando DROP TABLE não pode ser desfeito, portanto deve-se tomar muito cuidado na 
sua execução. 
 
1.2.1. SINTAXE 
DROP TABLE [ IF EXISTS ] <nome_tabela>; 
Esta sintaxe permite remover o banco de dados. 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 12 de 13 
A cláusula IF EXISTS verifica a existência do banco de dados antes da execução do comando. 
 
1.2.2. EXEMPLOS 
Para remover a tabela docentes devemos executar a seguinte instrução: 
DROP TABLE docentes; 
Caso a tabela docentes não exista, o SGBD irá exibir uma mensagem de erro. 
 
Para evitar essa mensagem, utiliza-se a cláusula IF EXISTS. 
DROP TABLE IF EXISTS docentes; 
 
Nesse exemplo podemos ver que irá ser exibido uma mensagem de erro, pois há uma tabela que possui 
uma chave estrangeira dependente da tabela docentes. Para evitar essa mensagem podemos adotar 
uma das alternativas a seguir: 
 Excluir o relacionamento entre as tabelas; 
 Excluir apenas o campo chave estrangeira na tabela docentes_disciplinas ; 
 Excluir a tabela que possui a chave estrangeira (docentes_disciplinas). 
 
2. Exercício: 
Dado o modelo descritivo, diagrama E-R e dicionário de dados a seguir, monte a estrutura do banco de dados no servidor 
PostgreSQL. 
 
Uma escola de nível médio solicitou a criação de um banco de dados para armazenar os boletins dos alunos. 
A pessoa ao se cadastrar, informa o CPF, que servirá de identificação, e o nome. Quando essa pessoa for se matricular em 
uma determinada série, passando a ser considerado como aluno, o sistema deverá armazenar a matrícula, o CPF da pessoa, 
o ano letivo e a série que irá cursar. 
Esse aluno cursará em cada série um grupo de disciplinas que serão ministradas por algum professor. A disciplina só poderá 
ser lecionada por um professor em uma determinada série, mas esse professor poderá ministrar diversas disciplinas em 
séries distintas. 
 
 
 
Curso Técnico Subsequente de Informática para Internet 
 
Disciplina: Banco de Dados 
Professor: Freitas, Charles Cesar Magno de 
 
 
Página: 13 de 13 
 
 
 
 
 
 
2. Referências 
 ROB, Peter; CORONEL, Carlos. SISTEMAS DE BANCO DE DADOS: PROJETO, IMPLEMENTAÇÃO E 
GERENCIAMENTO – Tradução da 8ª edição norte-americana. 8ª. ed. [S.l.]: Cengage, 2010. 744 p. 
 http://pgdocptbr.sourceforge.net/pg82/reference.html 
 http://pgdocptbr.sourceforge.net/pg82/datatype.html 
 Entidade: pessoas 
AtributoDomínio Tamanho Restrição 
cpf Texto 11 PK 
nome Texto 50 
 
 Entidade: disciplinas 
Atributo Domínio Tamanho Restrição 
id_disciplina Inteiro Autoincremento, PK 
nome Texto 40 
 
 Entidade: professores 
Atributo Domínio Tamanho Restrição 
id_professor Inteiro Autoincremento, PK 
nome Texto 50 
 
 Entidade: alunos 
Atributo Domínio Tamanho Restrição 
matricula Inteiro Autoincremento, PK 
ano Inteiro Só aceitar valores iguais ou superiores a 
2016 
serie Inteiro Só aceitar valores entre 1 e 12 
cpf Texto 11 FK (pessoas → cpf) 
 
 Entidade: disciplinas_alunos 
Atributo Domínio Tamanho Restrição 
matricula Inteiro PK, 
FK (alunos → matricula) 
id_professor Inteiro PK, 
FK (professores → id_professor) 
id_disciplina Inteiro PK, 
FK (discplinas → id_disciplina) 
nota_bimestre_1 Número (1 decimal) Só aceitar valores entre 0 e 10 
nota_bimestre_2 Número (1 decimal) Só aceitar valores entre 0 e 10 
nota_bimestre_3 Número (1 decimal) Só aceitar valores entre 0 e 10 
nota_bimestre_4 Número (1 decimal) Só aceitar valores entre 0 e 10 
freq_bimestre_1 Número (1 decimal) Só aceitar valores entre 0 e 100 
freq_bimestre_2 Número (1 decimal) Só aceitar valores entre 0 e 100 
freq_bimestre_3 Número (1 decimal) Só aceitar valores entre 0 e 100 
freq_bimestre_4 Número (1 decimal) Só aceitar valores entre 0 e 100