Projeto de Banco de Dados

Projeto de Banco de Dados

Projeto de Banco de Dados

Prof. Luiz Antônio Vivacqua C. Meyer

Projeto de Banco de Dados

Projeto Lógico

 Conversão do esquema conceitual para o esquema

de representação de um SGBD (esquema lógico)

 Forma de realização

• aplicação de regras de conversão (derivação)

 Resultado

• esquema lógico (tabelas,RIs, transações, consultas relevantes e visões, autorizações de acesso, ...)

Projeto de Banco de Dados

Exemplo de Projeto Lógico

Funcionário (Matricula, nome, sexo, nascimento, depto) •Chave primária: Matrícula

•Chave estrangeira: depto

Departamento (Código, nome, localização) •Chave primária: Código

Projeto de Banco de Dados

Projeto Físico

 Definição do esquema lógico em um SGBD adequado

ao modelo;

 Dependente do SGBD onde será implementado o BD

 Forma de realização: SQL

Projeto de Banco de Dados

Exemplo de Projeto Físico

 CREATE TABLESPACE "ST_TABELA" OWNER Admbd LOCATION 'C:/tablespace/tabela';

CREATE TABLESPACE "ST_INDICE" OWNER admbd LOCATION 'c:/tablespace/indice';

CREATE TABLE departamento(

codigo smallint primary key, nome varchar(30) not null,

localizacao varchar(30) not null) tablespace ST_TABELA;

CREATE INDEX ind_nome on departamento(nome) Tablespace ST_INDICE;

Projeto de Banco de Dados

Projeto Top-Down – Objetivos

 Projeto Conceitual

• preocupação: correta abstração do mundo real

(captura correta da semântica da aplicação)

 Projeto Lógico + Físico

• preocupação: escolhas corretas na conversão para o esquema do SGBD (relacional) para maximizar o

desempenho

Projeto Lógico

Principais Características do Modelo Relacional

 Conceito de Chaves:

• Chave candidata => Um ou mais atributos que permitem identificar unicamente cada linha da tabela. Uma tabela pode ter muitas chaves candidatas.

• Chave primária => identificador único da tabela, ou seja, é a chave candidata escolhida. Uma tabela pode uma única chave primária.

• Chave estrangeira => coluna(s) associada a uma chave primária de outra tabela.

Projeto Lógico

 Propriedades Das Relações No Modelo

Relacional:

• Não há tuplas duplicadas => uma relação é um conjunto e conjuntos não incluem elementos duplicados.

• As tuplas não seguem um ordenamento. • Os atributos não seguem um ordenamento.

• Todos os valores dos atributos são atômicos (simples) => uma relação não contém grupos repetidores nem

Projeto Lógico

 Regras De Integridade Do Modelo Relacional:

 Integridade da Entidade => Nenhum atributo que

participa da chave primária da relação pode ter

valor nulo.

 Integridade Referencial => Se uma relação R2

possui uma chave estrangeira FK equivalente a

chave primária PK de uma relação R1, então todo

valor de FK em R2 deve:

• Ser igual ao valor de PK em alguma tupla de R1, ou • Ser totalmente nulo.

Projeto Lógico

Projeto Lógico

 Restrições (Constraints)

 NOT NULL – Definida sobre uma coluna para não permitir

inclusão ou alteração que contenha nulo para a coluna.  UNIQUE – Definida sobre uma ou mais colunas. Não

permite que duas linhas contenham o mesmo valor para a(s) coluna(s).

 PRIMARY KEY – Definida sobre uma ou mais colunas. Possui

as mesmas propriedades de unicidade, implicitamente implementa a regra NOT NULL e só pode ser definida uma única vez para cada tabela.

 FOREIGN KEY – Implementa a integridade referencial e os

relacionamentos.

 CHECK – Regra definida pelo usuário para garantir que a

inclusão e a alteração na tabela obedeça a um determinado domínio de valores para uma coluna.

Projeto Lógico

 Ações Definidas Pela Restrição De Integridade

Referencial

• UPDATE/DELETE CASCADE – Estabelece que quando

uma chave primária (PK) é atualizada ou excluída, todas as linhas onde esta PK é referenciada através de uma chave estrangeira (FK), também são atualizadas ou excluídas.

• DELETE SET NULL – Estabelece que quando uma chave

primária é excluída, todas as linhas onde a PK é

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

1) Toda classe deriva uma tabela. Os atributos são mapeados para colunas

 Exemplo:

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

2) Associações Um para Muitos são implementadas utilizando

o conceito de chave estrangeira. Um novo atributo é adicionado na relação “filha” para referenciar a chave primária da outra relação “pai”.

 Exemplo:

Departamento (codigo, nome)

Funcionario (matricula, nome, sexo, nascimento, depto) depto REFERENCIA Departamento (codigo)

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

3) Associações Muitos para Muitos são implementadas

através da criação de uma nova tabela.

 Esta nova tabela terá como atributos as chaves primárias das relações envolvidas.

 As chaves estrangeiras em geral quando concatenadas definem a chave primária da relação.

Projeto Lógico

Regras de Derivação do Modelo Conceitual

 Associações Muitos para Muitos  Exemplo:

Projeto(codigo, nome, verba)

Funcionario(matricula, nome, profissão, nascimento) Alocação(projeto, funcionario)

projeto referencia Projeto(id)

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

4) Associações Muitos para Muitos com classe associativa são

implementadas da mesma forma que o item anterior sendo que os atributos da classe associativa ficam na nova tabela.

Projeto(codigo, nome, verba)

Funcionario(matricula, nome, profissão, nascimento) Alocação(projeto, funcionario, horas, função)

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

5) Associações Um para Um são um caso particular de

associações UM para MUITOS. A sua derivação

obedece às mesmas regras.

 Desta forma duas possibilidades de implementação

da chave estrangeira estão disponíveis. A escolha

deve ser definida tendo-se por base a cardinalidade

das relações e a participação das classes no

relacionamento.

 Uma terceira alternativa é agregar todos atributos

das duas classes em uma única tabela.

Projeto Lógico

Regras de Derivação do Modelo Conceitual

 Associações Um para Um

Departamento(codigo, nome)

Funcionario(matricula, nome, sexo, nascimento, departamento) departamento referencia Departamento(codigo)

Ou

Projeto Lógico

Regras de Derivação do Modelo Conceitual

 Associações Um para Um

 Em algumas situações, como nos relacionamentos de

composição 1:1, a derivação poderá ser feita gerando-se uma única tabela

.

FUNCIONARIO ( matricula, nome, sexo, nascimento, rua, numero, complemento, bairro, cidade, estado )

Projeto Lógico

Regras de Derivação do Modelo Conceitual

6) Associações ternárias são implementadas através da

criação de uma nova tabela.

A nova tabela terá como colunas as chaves primárias das três relações envolvidas no relacionamento e mais os

Projeto Lógico

Regras de Derivação do Modelo Conceitual

Fornecedor codigo nome Projeto codigo nome verba * * quantidade Fornecimento Peca codigo nome peso cor *

Projeto (codigo, nome, verba) Fornecedor (codigo, nome) Peca (codigo, nome, peso, cor)

Fornecimento (projeto, fornecedor, peca, qtde) projeto referencia Projeto(id)

fornecedor referencia Fornecedor(id) peca referencia Peca(id)

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

7) Associações Reflexivas são mapeadas de acordo com as

mesmas regras das associações binárias (1..1, 1..*, *..*).

Funcionario matricula nome cargo telefone 0..1 0..* Chefia

Funcionario(matricula, nome, cargo, telefone, chefe) chefe referencia funcionario(id)

Projeto Lógico

Regras de Derivação do Modelo Conceitual

Peca codigo nome peso cor 0..* 0..* Composicao

Peca (codigo, nome, peso, cor)

Composição (idcomposicao, idpeca)

idcomposicao referencia peca(codigo) idpeca referencia peca(codigo)

Projeto Lógico

 Regras de Derivação do Modelo Conceitual

8) Estruturas de generalização/Especialização podem ser

mapeadas de três maneiras:

• Uma tabela para cada classe da hierarquia,

• uma tabela para cada classe concreta da hierarquia ou • uma única tabela.

Projeto Lógico

Regras de Derivação do Modelo Conceitual

1ª alternativa: Uma tabela para cada classe da hierarquia

Contribuinte (id, endereco, telefone)

PessoaFisica (CPF, nome, nascimento, idcontribuinte) idcontribuinte referencia Contribuinte(id) PessoaJuridica (CNPJ, razaoSocial, idcontribuinte)

Projeto Lógico

Regras de Derivação do Modelo Conceitual

Contribuinte endereco telefone PessoaFisica CPF nome nascimento Contribuinte CNPJ razaoSocial

2ª alternativa: Uma tabela para cada classe concreta da hierarquia

PessoaFisica (CPF, endereco, telefone, nome, nascimento) PessoaJuridica (CNPJ, endereco, telefone, razaoSocial

Projeto Lógico

Regras de Derivação do Modelo Conceitual

3a alternativa: Uma única tabela

Contribuinte ( id, endereco, telefone, CPF, nome,nascimento, CNPJ, razaoSocial, tipo_contribuinte )

Projeto Físico

ESTRUTURAS DE ARMAZENAMENTO

 Tablespaces

 Índices

Projeto Físico

Estruturas de Armazenamento

Tablespace define a localização de armazenamento para tabelas e índices

Nível Lógico

Usar tablespaces diferentes para cada projeto. Usar tablespaces diferentes para índice e tabela Nível Físico

Usar unidades de disco diferentes para índices e tabelas, melhorando o “througput” de E/S

Projeto Físico

Índice

O que é ?

 Estrutura de dados mantida internamente pelo SGBD  2 campos:

• Valores dos atributos indexados (ordenados) • Endereço da linha na tabela

Exemplo

TABELA FUNCIONARIO INDICE NA COLUNA NOME

CODIGO NOME CIDADE RID

300 MENDES RIO 3870

250 CASTRO RIO 4568

RID NOME

4568 CASTRO

Projeto Físico

Índice

 Finalidade

 Otimizar o acesso aos dados nas relações  Analogia a um índice de um livro

 Acesso mais rápido ao registro no caso da chave de busca for igual ao campo indexado.

 Busca no arquivo índice é mais rápida porque o arquivo de índice é menor e é ordenado. Logo é possível fazer um acesso mais rápido via busca binária.

• Número de acessos (médio) em busca binária é log_n .

• Acesso seqüencial (ordenado) pelo campo indexado.

 Desvantagens

☹ Maior espaço de armazenamento.

Projeto Físico

Tipos

 Estrutura de dados

• Árvores B ou B+ ( comuns em chaves primárias) – Alta seletividade

• Hash

 Forma

• Únicos (implementam a restrição “unique”)

• Compostos (definidos sobre múltiplos atributos)

Quando usar?

 Em atributos usados como filtros (cláusula “where”) caso a freqüência de execução seja alta ou tempo de resposta

insatisfatório.

PostgreSql

Arquitetura

Armazenamento

Usuário

Utilitários

SQL

Projeto Físico

 O PostgreSQL é um sistema de gerenciamento de banco de

dados objeto-relacional, derivado do pacote POSTGRES escrito na Universidade da Califórnia em Berkeley.

 Qualquer plataforma Unix-compatível pode rodar PostgreSQL.  A compatibilidade nativa para Windows está disponível desde a

versão 8.0.

Fundamentos da Arquitetura

 O PostgreSQL utiliza o modelo cliente-servidor.

 O processo servidor, que gerencia os arquivos de banco de dados, aceita conexões dos aplicativos-cliente e executa ações no banco de dados em nome dos clientes. O

programa servidor de banco de dados se chama postgres.  Alguns aplicativos-cliente são fornecidos na distribuição do

PostgreSQL, sendo a maioria desenvolvido pelos usuários.

Armazenamento

 Esquema

• é uma coleção de tabelas, visões, índices, tipos de dados e funções. • podem existir tabelas com o mesmo nome, mas em esquemas

diferentes

• possibilita vários usuários utilizarem o mesmo banco sem interferências

• facilita a organização lógica dos objetos do banco

• Por padrão, as tabelas (e outros objetos) são colocadas

automaticamente no esquema chamado "public", presente em todos os banco de dados.

 Banco de Dados

• É uma coleção de esquemas. Quando um cliente se conecta a um servidor, ele especifica o banco de dados que ele quer acessar.

 Tablespace

 São locais no filesystem selecionados para o armazenamento de bancos de dados.

 Um banco de dados pode estar fisicamente armazenado em mais de um tablespace e um tablespace pode conter informações de mais de um banco de dados.

 Os tablespaces permitem que o administrador utilize seu

conhecimento do padrão de utilização dos objetos de banco de dados para otimizar o desempenho.

• Por exemplo, um índice muito utilizado pode ser colocado em um disco muito rápido com alta disponibilidade.

• Ao mesmo tempo, uma tabela armazenando dados históricos raramente utilizados, ou que seu desempenho não seja crítico, pode ser armazenada em um sistema de disco mais barato e mais lento

 Tablespace

 Por padrão são criados dois tablespaces no PostgreSQL:

• pg_default – repositório padrão dos objetos e dados fornecidos pelos usuários

• pg_global – armazena o catálogo do servidor e templates (protótipos) de banco de dados

 Para definir um tablespace é utilizado o comando CREATE TABLESPACE.

• CREATE TABLESPACE ´ST_TABELA´ OWNER vivacqua LOCATION 'c:/tablespace/tabelas';

• Usuário

– Usuário é equivalente a Role com a exceção de

que quando um usuário é criado é assumido que

ele pode dar login enquanto que na Role isto não

acontece.

– Ex: create user vivacqua with createdb password

'vivacqua'

 Utilitários  Backup • Pgdump • Pgrestore  Carga / Extração • Copy

 Recuperar o espaço em disco ocupado por registros deletados.

• Vacuum

 Coleta de estatísticas

• Analyse

Projeto Físico

• PGADMIN

– Ferramenta cliente para projeto e administração distribuída gratuitamente – Permite executar comandos SQL (DDL e DML)

SQL - (Structured Query Language)

• Surgiu no início na década de 70 pela IBM.

• Em1980 o produto mudou seu nome para SQL.

• “American National Standards Institute” é o mantenedor e responsável pelo padrão.

• A linguagem é enorme ( especificação contém + 2000 páginas).

Os comandos de SQL podem ser usados interativamente como

uma linguagem de consulta ou podem ser incorporados a programas de aplicações.

Projeto Físico

A Linguagem de Definição de Dados:

• Subcategoria de SQL que permite a criação e a alteração dos objetos do banco de dados.

• Normalmente, é de uso exclusivo do Administrador de Banco de Dados; contudo, seu uso fica sujeito à política da empresa.

• PRINCIPAIS COMANDOS DDL: – CREATE, ALTER, DROP TABLE – CREATE, DROP INDEX

Projeto Físico

Criando tabela

CREATE TABLE nome-da-tabela

( coluna1 , coluna2, ...,

definição-da-colunaN )

ONDE

definição-da-coluna é definida como:

Projeto Físico

Principais tipos de dados numéricos

 smallint 2 bytes inteiro com faixa pequena -32768 a +32767  integer 4 bytes escolha usual para inteiro -2147483648 a

+2147483647

 bigint 8 bytes inteiro com faixa larga -9223372036854775808 a  9223372036854775807

 decimal variável precisão especificada pelo usuário sem limite  numeric variável precisão especificada pelo usuário sem limite  serial 4 bytes inteiro com auto-incremento 1 a 2147483647

Principais tipos de dados de caracteres

 character(n), char(n)

Projeto Físico

PRINCIPAIS CONSTRAINTS:

 Primary key – define a chave primária da tabela  Foreign key – define uma chave estrangeira

 Unique – define que o valor da coluna é único

 Not Null – especifica que a coluna não pode ter valores nulos  Check – estabelece um domínio para os valores da coluna

Podem ser especificadas:  na definição da coluna

 ao final da definição das colunas

Projeto Físico

 Exemplos

CREATE TABLE DEPARTAMENTO

( CODIGO INTEGER PRIMARY KEY, NOME CHAR(20) NOT NULL, STATUS INTEGER,

CIDADE CHAR(20) )

CREATE TABLE FUNCIONÁRIO

( MATRICULA INTEGER PRIMARY KEY, NOME VARCHAR (20) NOT NULL,

SEXO CHAR (1),

DEPTO INTEGER,

CONSTRAINT FK_DEP FOREIGN KEY(DEPTO) REFERENCES

DEPARTAMENTO (CODIGO)

Projeto Físico

Alterando a estrutura da tabela

 Adicionando coluna

 Removendo coluna

 Renomeando coluna

 Adicionando constraint

 Removendo constraint

Projeto Físico

SINTAXE:

ALTER TABLE nome-da-tabela

ADD CONSTRAINT ou DROP CONSTRAINT ou ADD COLUMN ou

DROP COLUMN ou

RENAME TO novo_nome_tabela ou

Projeto Físico

Exemplos:

ALTER TABLE DEPARTAMENTO ADD TELEFONE CHAR(11); TRANSFORMA A TABELA DE DEPARTAMENTO EM:

DEPARTAMENTO (CÓDIGO, NOME, STATUS, CIDADE, TELEFONE);

ALTER TABLE DEPARTAMENTO ADD CONSTRAINT FORNECEDOR_PK PRIMARY KEY (CODIGO);

CRIA A CHAVE PRIMÁRIA NA TABELA FORNECEDOR. ALTER TABLE FUNCIONARIO

ADD CONSTRAINT FK_DEPARTAMENTO FOREIGN KEY(DEPTO) REFERENCES DEPARTAMENTO( CODIGO);

CRIA O RELACIONAMENTO ENTRE UMA TABELA DEPARTAMENTO E A TABELA FUNCIONARIO. ALTER TABLE FUNCIONÁRIO ADD CONSTRAINT CHK_SEXO

Projeto Físico

Removendo uma tabela

SINTAXE:

DROP TABLE nome-da-tabela

Esta sentença não só remove a descrição da tabela do catálogo do banco, como todas suas instâncias, visões e índices

definidos sobre ela.

EXEMPLO:

DROP TABLE Funcionario;

Projeto Físico

Criando Índice

 Sintaxe:

nome-indice ON nome-da-tabela(nome_coluna) [Tablespace nome-da-tablespace]

 Exemplo

• Create Index ind_nome on Funcionario (NOME) tablespace ST_INDICE ;

Projeto Físico

Removendo índice

 Sintaxe: Drop index nome-do-índice

 Exemplo: