Banco de Dados Distribuídos - facom.ufu.brilmerio/sbd20132/sbd5sqlDDLparte1.pdf · Página 3 Prgrama Teórico/Prático - SQL • Linguagem SQL – Comandos de criação e eliminação

GBC043 - Sistemas de Banco de DadosSQL DDL e Implementação no PostgreSQL

Ilmério Reis da [email protected]/~ilmerio/sbdUFU/FACOM/BCC

mailto:[email protected]

Página 2

Programa Teórico – Visão Panorâmica

• Sistemas de Banco de Dados e Modelos de Dados

• Manipulação de Dados e Ferramentas de Projeto de BD

• Controle de concorrência e tópicos em Banco de Dados

Página 3

Prgrama Teórico/Prático - SQL

• Linguagem SQL– Comandos de criação e eliminação de tabelas– Especificação de restrições em bancos de dados– Evolução de esquemas de banco de dados– Comandos de inserção de tuplas em tabelas– Comandos de alteração e supressão de tuplas– Comandos de consulta (simples e complexos)– Definição de visões

Álgebra Relacional Cálculo Relacional

UFU/FACOM Página 4

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 5

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 objeto-relacional(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 6

SQL/ DDLExemplos SGBDs

UFU/FACOM Página 7

SQL 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 8

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 9

SQL/ DDL Conceitos associados

• Banco de dados e Catálogo

• Esquema

• Tabela

• Linha

• Coluna

• Índice

UFU/FACOM Página 10

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


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


O SGBD PostgreSQL

PostgreSql

Um SGBD objeto-relacional.


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


PostgreSql - Principais Características

• implementa SQL92/SQL1999;

• herança

• tipos de dados

• funções

• restrições (constraints)

• gatilhos (triggers)

• regras(rules)

• integridade transacional


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 sbdX

WITH OWNER bccX;


PostgreSql - Sintaxe do manual/slides

▸ Sintaxe completa: consultar manual PostgreSQL

http://www.postgresql.org/docs/8.4/static/sql-createtable.html

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.

[ ] (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

[ ...] O item precedente pode ser repetido N vezes. A separação entre os itens é feita por um espaço em branco.







PostgreSql – Criação de Esquemas

CREATE SCHEMA schemaname

[ AUTHORIZATION username ]

Exemplo:

CREATE SCHEMA company


PostgreSql – Criação de Tabela

CREATE TABLE tabela (

{coluna tipo [restricoes coluna] | restricoes tabela}

[, ...]

)

[ INHERITS (tabela pai [, ...])]


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


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 case-sensite

▸ UPDATE MY_TABLE SET A = 5; ▸ uPDaTE my_TabLE SeT a = 5;

▸ Convenção adotada▸ Palavras-chave em maiúscula▸ Identificadores em minúsculo

▸ UPDATE my_table SET a = 5;

▸ Identificadores com aspas▸ Aceitam quaisquer caracteres

▸ UPDATE "my_table" SET "a" = 5;


PostgreSql – regras para identificadores cont.▸ Ao colocar aspas em um identificador ele torna-se case-sensitive

▸ 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

http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html






PostgreSql – Exemplo de Tipos


Exemplos de coluna tipo:

nome VARCHAR(40)

sexo CHAR

salario DECIMAL(10, 2)


PostgreSql – Exemplos de outros tipos


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


PostgreSql – Tipo Lógico


PostgreSql – Tipos para números exatos


PostgreSql – Tipos p/ números aproximados


PostgreSql – Tipos p/ dados temporais


PostgreSql – Tipos p/ cadeias de caracteres


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)

http://www.postgresql.org/docs/8.4/static/datatype.html




PostgreSql– Exemplo de restrições de coluna


[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’))


PostgreSql – DOMÍNIOS


PostgreSql – CREATE DOMAIN

CREATE DOMAIN name [ AS ] data_type[ DEFAULT expression ][ constraint [ ... ] ]

onde constraint é:

[ CONSTRAINT constraint_name ]{ NOT NULL | NULL | CHECK (expression)


PostgreSql –CREATE DOMAIN- Exemplos

CREATE DOMAIN sexo AS char(1)DEFAULT 'M'NOT NULLCHECK ( VALUE IN ('M', 'F'));

CREATE DOMAIN data_evento AS dateCONSTRAINT 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


PostgreSql – Exemplo de restrição de tabela


[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)


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));


PostgreSql – CREATE TABLE sintaxe completa

▸ Sintaxe completa: consultar manual PostgreSQL








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) );


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));


PostgreSql – DROP TABLEDROP 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;


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 ] NULLDROP <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 ]


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);


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)


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 ]


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)


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


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


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>


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 sbdgX

template1 => PASSWORD ’pw.X.sbd’;


PostgreSql – Criando um banco de dados

Exemplo de um banco de dados criado para um usuário:

template1 => CREATE DATABASE sbdX

template1 => OWNER bccX;


PostgreSql – Controle de acesso - Grant

GRANT lista_privilegios

ON tipo_objeto lista_objetos

TO lista_usuarios

Exemplo:

GRANT SELECT, DELETE

ON employee

TO joao


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] }


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/mo410-071/Ch05-PadraoSQL-art.pdf, acesso em setembro/2008

Sugestão de consulta: Manuais do PostgreSQL

http://www.ic.unicamp.br/~geovane/mo410-071/Ch05-PadraoSQL-art.pdf


FIM – SQL DDL e PostgreSQL

FIM – SQL DDL e PostgreSQL

Documents

Banco de Dados Distribuídos - facom.ufu.brilmerio/sbd20132/sbd5sqlDDLparte1.pdf · Página 3 Prgrama Teórico/Prático - SQL • Linguagem SQL – Comandos de criação e eliminação