Banco de Dados Distribuídos - Facom

GES013 - Sistema de Banco de Dados
SQL DDL e Implementação no PostgreSQL
Ilmério Reis da Silva
ilmerio arroba ufu.br
www.facom.ufu.br/~ilmerio/sbd
UFU/FACOM/BCC
SQL
Def. A SQL(Structured Query Language) é uma Linguagem
de Consulta Estruturada declarativa para acesso a
sistemas de banco de dados relacionais.
OBS: como linguagem declarativa descreve o problema ao
invés da solução, especificando o que deve ser feito e não
como.
UFU/FACOM
Página 2
SQL - Histórico
•
•
•
•
1970s - SEQUEL do SYSTEM R nos laboratórios da IBM
1986 – Padrão ANSI chamado SQL1 ou SQL-86
1992 – SQL2 => introdução de chave estrangeira
1999 – SQL3 => gatilhos; características objetorelacional(tipo LOB-Large Object); consultas recursivas,
etc.
• 2003 - SQL-2003: mais recursos para definição de tipos
(OO); SQL/XML; etc.
• 2008 – SQL-2008: mais recursos para orientação a objetos
UFU/FACOM
Página 3
SQL/ DDLExemplos SGBDs
UFU/FACOM
Página 4
SQL – Principais Classes de Comandos
Duas classes importantes de comandos:
▸
▸
DDL: Data definition language
Comandos para a definição de dados
DML: Data manipulation language
Comandos para a manipulação de dados
DML interativa – interface direta com o SGBD
DML embutida – utilizada em programas de aplicação
UFU/FACOM
Página 5
SQL-DDL
Def SQL/DDL (Data
Definition Language) permite ao usuário
definir tabelas e elementos associados.
OBS: A SQL/DDL se caracteriza por poucos comandos
básicos, embora implementações comerciais tenham
várias extensões.
UFU/FACOM
Página 6
SQL/ DDL Conceitos associados
•
•
•
•
•
•
Banco de dados e Catálogo
Esquema
Tabela
Linha
Coluna
Índice
UFU/FACOM
Página 7
SQL/ DDL Comandos
CREATE – Cria uma definição
CREATE TABLE tab ...
ALTER – Altera uma definição
ALTER TABLE tab ADD ...
DROP – Exclui uma definição
DROP TABLE tab
UFU/FACOM
Página 8
SQL/ DDL Abrangência
•
•
•
•
•
Definição de tabelas
Definição de restrições de integridade
Definição de índices
Definição de privilégios de acesso
Definição de visões
UFU/FACOM
Página 9
O SGBD PostgreSQL
PostgreSql
Um SGBD objeto-relacional.
UFU/FACOM
Página 10
PostgreSQL - Breve Histórico
• 1986-1993: Postgres / University of California at Berkeley
• 1995: Postgres95 (Open source) / PostgreSQL 6.0;
• 2005: PostgreSQL 8.0 com facilidades de instalação em
Windows
• 2010: PostgreSQL 9.0 replicação nativa
UFU/FACOM
Página 11
PostgreSql - Principais Características
•
•
•
•
•
•
•
•
implementa SQL92/SQL1999;
herança
tipos de dados
funções
restrições (constraints)
gatilhos (triggers)
regras(rules)
integridade transacional
UFU/FACOM
Página 12
PostgreSql – Criação de Banco de Dados
CREATE DATABASE nome
[ [ WITH ] [ OWNER [=] dono_bd ]
[ TEMPLATE [=] modelo ]
[ ENCODING [=] codificação ]
[ TABLESPACE [=] tablespace ] ]
[ CONNECTION LIMIT [=] limite_con ] ]
Exemplo: CREATE DATABASE estbX
WITH OWNER estgX;
UFU/FACOM
Página 13
PostgreSql – Convenção de Sintaxe
Convenção
▸
UPPERCASE
(maiúsculo) Palavra-chave SQL.
lowercase (minúsculo) Identificadores ou constantes SQL informadas pelo usuário
itálico Nome de um bloco de sintaxe. Essa convenção é usada para
indicar blocos longos de sintaxe que podem ser usados em mais
de um local.
| (barra vertical) Separa elementos opcionais da sintaxe dentro de colchetes ou
chaves. Somente um dos itens pode ser escolhido.
UFU/FACOM
Página 14
PostgreSql – Convenção de Sintaxe (cont...)
Convenção
[ ] (colchetes)
Item de sintaxe opcional. Os colchetes não fazem parte do
comando.
{ } (chaves)
Item da sintaxe obrigatório. As chaves não fazem parte do
comando.
[,...] O item precedente pode ser repetido N vezes. A separação entre
os itens é feita por uma vírgula
UFU/FACOM
Página 15
PostgreSql - Sintaxe
Sintaxe completa: consultar manual PostgreSQL
www.postgresql.org/docs/8.4/static/sql-createdatabase.html
UFU/FACOM
Página 16
PostgreSql – Criação de Esquemas
CREATE SCHEMA schemaname
[ AUTHORIZATION username ]
Exemplo:
CREATE SCHEMA company
UFU/FACOM
Página 17
PostgreSql – Criação de Tabela
CREATE TABLE tabela (
{coluna tipo [restricoes coluna] | restricoes tabela}
[, ...]
)
[ INHERITS (tabela pai [, ...])]
UFU/FACOM
Página 18
PostgreSql – Exemplo de Tabela
CREATE TABLE tabela ({coluna tipo [restricoes coluna] |
restricoes tabela} [, ...]) [ INHERITS (tabela pai [, ...])]
Exemplos de identificadores de tabela:
 company.employee
 public.employee
 employee
UFU/FACOM
Página 19
PostgreSql – regras para identificadores
▸
Iniciam com letras (a-z) ou underscore (_)
▸
▸
Caracteres subsequentes: letras, dígitos (0-9), _
Identificadores e palavras-chave não são casesensite
▸
▸
▸
Convenção adotada
▸
▸
Palavras-chave em maiúscula
Identificadores em minúsculo
▸
▸
UPDATE MY_TABLE SET A = 5;
uPDaTE my_TabLE SeT a = 5;
UPDATE my_table SET a = 5;
Identificadores com aspas
▸
Aceitam quaisquer caracteres
▸
UPDATE "my_table" SET "a" = 5;
UFU/FACOM
Página 20
PostgreSql – regras para identificadores cont.
▸
▸
▸
▸
Ao colocar aspas em um identificador ele torna-se casesensitive
Identificadores sem aspas são sempre transformados em
minúsculo (embora o padrão SQL defina que se
transforme em maiúscula)
Se você criar um esquema ou tabela usando a interface
gráfica do pgAdmin e, caso o identificador deste objeto
não seja composto por letras minúsculas, o objeto será
identificado somente por meio de aspas.
▸ Faça o teste, criando esquemas e tabelas por meio da
interface gráfica e utilizando letras maiúsculas.
Mais informações e referência:
▸ http://www.postgresql.org/docs/8.4/static/sql-syntax
-lexical.html
UFU/FACOM
Página 21
PostgreSql – Exemplo de Tipos
CREATE TABLE tabela ({coluna tipo [restricoes coluna] |
restricoes tabela} [, ...]) [ INHERITS (tabela pai [, ...])]
Exemplos de coluna tipo:
 nome VARCHAR(40)
 sexo CHAR
 salario DECIMAL(10, 2)
UFU/FACOM
Página 22
PostgreSql – Outros exemplos de tipos
CREATE TABLE tabela ({coluna tipo [restricoes coluna] |
restricoes tabela} [, ...]) [ INHERITS (tabela pai [, ...])]
Outros tipos:
INT; SMALLINT; REAL; DATE; TIME; TIMESTAMP;
BOOLEAN, GEOMETRIC(POINT, LINE, etc), NETWORK
ADDRES, BIT, XML, ARRAYS, COMPOSITE, OID, etc.
VER DOCUMENTAÇÃO DO POSTGRES:
www.postgres.org
UFU/FACOM
Página 23
PostgreSql – Tipo Lógico
UFU/FACOM
Página 24
PostgreSql – Tipos para números exatos
UFU/FACOM
Página 25
PostgreSql – Tipos p/ números aproximados
UFU/FACOM
Página 26
PostgreSql – Tipos p/ dados temporais
UFU/FACOM
Página 27
PostgreSql – Tipos p/ cadeias de caracteres
UFU/FACOM
Página 28
PostgreSql – Outros Tipos
Existem outros tipos de dados além dos
apresentados anteriormente. Consulte o manual do
PostgreSQL:
▸ http://www.postgresql.org/docs/8.4/static
/datatype.html
▸ Livro: Beginning databases with PostgreSQL:
▸
Matthew and Stones, 2nd ed. Apress(citado pelo
Prof. Bruno)
UFU/FACOM
Página 29
PostgreSql– Exemplo de restrições de coluna
CREATE TABLE tabela ({coluna tipo [restricoes coluna] |
restricoes tabela} [, ...]) [ INHERITS (tabela pai [, ...])]
[CONSTRAINT restrição]
{NOT NULL | NULL | UNIQUE | PRIMARY KEY
| CHECK (expressão) | REFERENCES tabela [ ( coluna ) ]
[ON DELETE ação ] [ ON UPDATE ação ]}
[DEFERRABLE | NOT DEFERRABLE ]
[INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Exemplos de restricoes coluna:
sexo CHAR CHECK (sexo IN (’M’, ’F’))
UFU/FACOM
Página 30
PostgreSql – DOMÍNIOS
UFU/FACOM
Página 31
PostgreSql – CREATE DOMAIN
CREATE DOMAIN name [ AS ] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]
onde constraint é:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression)
UFU/FACOM
Página 32
PostgreSql –CREATE DOMAIN- Exemplos
CREATE DOMAIN sexo AS char(1)
DEFAULT 'M'
NOT NULL
CHECK ( VALUE IN ('M', 'F'));
CREATE DOMAIN data_evento AS date
CONSTRAINT valida_data
CHECK ( VALUE > '01/01/1900' AND VALUE < '01/01/2099');
Obs.: podemos então definir os tipos sexo ou data_evento no
CREATE TABLE
UFU/FACOM
Página 33
PostgreSql – Exemplo de restrição de tabela
CREATE TABLE tabela ({coluna tipo [restricoes coluna] |
restricoes tabela} [, ...]) [ INHERITS (tabela pai [, ...])]
[CONSTRAINT restrição]
{UNIQUE(coluna [,...]) | PRIMARY KEY(coluna [,...])
| CHECK (expressão)
| FOREIGN KEY REFERENCES tabela [ ( coluna, [,...] ) ]
[ON DELETE ação ] [ ON UPDATE ação ]}
[DEFERRABLE | NOT DEFERRABLE ]
[INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
Exemplo restricoes tabela: PRIMARY KEY(ssn)
UFU/FACOM
Página 34
PostgreSql – Exemplo de criação de tabela
CREATE TABLE emp (
ssn CHAR(9) NOT NULL,
name VARCHAR(40),
sex CHAR CHECK (sex IN (’M’, ’F’)),
salary DECIMAL(10,2),
mngrsalary DECIMAL(10,2),
CHECK (mngrsalary > salary),
PRIMARY KEY (ssn));
UFU/FACOM
Página 35
PostgreSql – CREATE TABLE sintaxe
completa
▸
Sintaxe completa: consultar manual PostgreSQL
http://www.postgresql.org/docs/8.4/static/sql-createtable.html
UFU/FACOM
Página 36
PostgreSql – Company Database - employee
CREATE TABLE employee (fname VARCHAR (15) NOT NULL,
minit CHAR, lname VARCHAR (15) NOT NULL,
ssn CHAR(9) NOT NULL, bdate DATE,
address VARCHAR(30), sex CHAR CHECK (sex IN (’M’, ’F’)),
salary DECIMAL(10,2), superssn CHAR(9),
dno INT NOT NULL,
PRIMARY KEY (ssn));
CREATE TABLE department (dname varchar(15) not null,
dnumber int not null, mgrssn char(9),
mgrstartdate date,
PRIMARY KEY (dnumber) );
UFU/FACOM
Página 37
PostgreSql – Company Database - outras
CREATE TABLE dependent (essn char (9) not null,
dependent_name varchar (30) not null, sex char, bdate date,
relationship varchar(15), PRIMARY KEY (essn, dependent_name) );
CREATE TABLE dept_locations (dnumber int not null, dlocation varchar(15));
CREATE TABLE project (pname varchar(20),
pnumber int not null,
plocation varchar(15),
dnum int,
PRIMARY KEY (pnumber))
CREATE TABLE works_on (essn char(9) not null, pno int not null,
hours decimal(4,2));
UFU/FACOM
Página 38
PostgreSql – DROP TABLE
DROP TABLE – Exclui uma tabela existente de um
banco de dados. Não pode ser excluída a tabela que
possui alguma referência. Neste caso, deve-se primeiro
excluir a tabela que possui algum campo que a está
referenciando e depois excluir a tabela inicial.
DROP TABLE <nome da tabela>
Exemplo:
/* Apaga tabela Departamento */
DROP TABLE Departamento;
UFU/FACOM
Página 39
PostgreSql – ALTER TABLE
▸
ALTER TABLE – Altera as definições de campos e de
restrições.
ALTER TABLE <nome da tabela>
ADD <definição de Coluna>
ADD <Restrição de integridade>
ALTER <definição de Coluna>
ALTER <definição de Coluna> DEFAULT <default-value>
ALTER <definição de Coluna> [ NOT ] NULL
DROP <definição de Coluna>
DROP CONSTRAINT <nome da restrição>
RENAME <novo nome>
RENAME <Atributo> TO <novo atributo>
Onde <definição de coluna> pode ser:
<Nome Atributo> <Tipo de Dado> [NULL ] |
[ DEFAULT default-value ]
UFU/FACOM
Página 40
PostgreSql – Company Database – Alter table
ALTER TABLE employee
ADD CONSTRAINT emp_superssn FOREIGN KEY (superssn)
REFERENCES employee(ssn) DEFERRABLE
ALTER TABLE employee
ADD CONSTRAINT emp_dno FOREIGN KEY (dno)
REFERENCES department(dnumber) DEFERRABLE;
ALTER TABLE department
ADD CONSTRAINT dept_mgrssn FOREIGN KEY (mgrssn)
REFERENCES employee(ssn) DEFERRABLE;
ALTER TABLE dependent
ADD CONSTRAINT depe_essn FOREIGN KEY(essn)
REFERENCES employee(ssn);
UFU/FACOM
Página 41
PostgreSql–Company Database–Alter table 2
ALTER TABLE dept_locations
ADD CONSTRAINT loc_dnumber
FOREIGN KEY (dnumber) REFERENCES department(dnumber)
ALTER TABLE project
ADD CONSTRAINT proj_dnum
FOREIGN KEY (dnum) REFERENCES department(dnumber)
ALTER TABLE works_on
ADD CONSTRAINT w_essn
FOREIGN KEY (essn) REFERENCES employee(ssn),
ALTER TABLE works_on
ADD CONSTRAINT w_pno
FOREIGN KEY (pno) REFERENCES project(pnumber)
UFU/FACOM
Página 42
PostgreSql – Objetivos da Criação de Índices
OBJETIVOS:
 Restrições de integridade: chaves
 Desempenho: atributos frequentemente usados em
comparações da cláusula WHERE)
CREATE [UNIQUE] INDEX nome_do_indice
ON tabela
[ USING metodo_de_acesso ]
( coluna [ nome_operador ] [, ...] )
[ WHERE predicado ]
UFU/FACOM
Página 43
PostgreSql – sintaxe de criação de índices
CREATE [UNIQUE] INDEX nome_do_indice ON tabela
[USING metodo_de_acesso ] (coluna [nome_operador] [, ...] )
[WHERE predicado ]
• metodo_de_acesso: BTREE; RTREE; HASH; GIST;
 BTREE: para operadores <,<=,=,>=,>
 RTREE: para operadores espaciais, por exemplo, left of
 HASH: para operador de igualdade (=)
 GIST: operadores genéricos entre classes
• nome_operador: operador usado na comparação, por exemplo, valor
absoluto em BTREE
• predicado: usado para índices parciais (seleção da tabela)
UFU/FACOM
Página 44
PostgreSql – exemplos de criação de índices
EXEMPLOS:
CREATE INDEX ind_ename
ON employee (fname, minit, lname)
CREATE UNIQUE INDEX ind_pname
ON project (pname)
OBS: antes de implementar um BD em nosso SGBD,
vamos discutir alguns detalhes do servidor
PostgreSQL
UFU/FACOM
Página 45
PostgreSql - Arquitetura Cliente/Servidor
• Servidor - processo postmaster
 acessa arquivos
 aceita conexões
 cria canal direto cliente/servidor
• Cliente
 ferramente textual, por exemplo, psql
 aplicação gráfica, por exemplo, pgadmin
 servidor web, por exemplo, apache rodando phpadmin
UFU/FACOM
Página 46
PostgreSql – Criando o Ambiente no Servidor
• Superusuário, geralmente o postgres
• Criando um Cluster de BD:
[postgres]$ initdb -D <diretório>
• Configurando:
 postgresql.conf : geral, como número de conexões
 pg_hba.conf : métodos de autenticação de conexões
 pg_ident.conf : mapeamento de ids de usuários do SO
• Colocando o SGBD, que gerencia um Cluster de BD, no ar
[postgres]$ pg_ctl start -l <arquivo_log> -D
<diretório>
UFU/FACOM
Página 47
PostgreSql – Criando um usuário
Criando um usuário
CREATE USER nome [ [ WITH ] opções [ ... ] ]
Conexão via psql:
[postgres]$ psql template1
Exemplo:
template1 => CREATE USER estgX
template1 =>
PASSWORD ’*******’;
UFU/FACOM
Página 48
PostgreSql – Criando um banco de dados
Exemplo de um banco de dados criado para um usuário:
template1 => CREATE DATABASE estbX
template1 => OWNER estgX;
UFU/FACOM
Página 49
PostgreSql – Controle de acesso - Grant
GRANT lista_privilegios
ON tipo_objeto lista_objetos
TO lista_usuarios
Exemplo:
GRANT SELECT, DELETE
ON employee
TO joao
UFU/FACOM
Página 50
PostgreSql – Concedendo Privilégios
Privilégios de acordo com o objeto:
• TABLE: { { SELECT | INSERT | UPDATE | DELETE |
RULE | REFERENCES | TRIGGER } [,...] | ALL
[PRIVILEGES] }
• DATABASE: { { CREATE | TEMPORARY | TEMP } [,...]
| ALL [ PRIVILEGES] }
• FUNCTION: { EXECUTE | ALL [PRIVILEGES] }
• LANGUAGE: { USAGE | ALL [ PRIVILEGES ] }
• SCHEMA: {{CREATE | USAGE} [,...] | ALL
[PRIVILEGES] }
UFU/FACOM
Página 51
Bibliografia/Exercícios
•
Ver roteiros de aulas de laboratório
•
•
•
[EN] Capítulo 4, 5
[RG] Capítulo 5
[SK] Capítulo 4, 5 e 6
Sugestão de leitura:
Lima, A L G, Padrão SQL e sua Evolução, http://www.ic.unicamp.br/~geovane/mo410091/Ch05-PadraoSQL-art.pdf, acesso em setembro/2016
Sugestão de consulta: Manuais do PostgreSQL
UFU/FACOM
Página 52
FIM – SQL DDL e PostgreSQL
FIM – SQL DDL e PostgreSQL
UFU/FACOM
Página 53