Projeto_lógico_físico2

Prévia do material em texto

Projeto de Banco de Dados
Projeto de Banco de Dados
Prof. Luiz Antônio Vivacqua C. Meyer
(luiz.vcm@gmail.com)
2
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, ...)
3
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
•Restrição de domínio: Código > 100
4
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
▪ Resultado: esquema físico
5
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;
6
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
(distribuição adequada dos dados em tabelas)
7
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.
8
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 
estruturas como colunas.
9
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.
10
Projeto Lógico
Integridade Referencial
Código Nome
10 Informática
20 Pessoal
30 Manutenção
Matrícula Nome Cargo Depto
100 José Analista 10
200 João Advogado 20
300 Maria Analista 10
400 Carlos Pintor 30
500 Ana Médica
Departamento Empregado
Chave Primária
Chave Estrangeira
11
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. 
12
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 é 
referenciada tem seus valores alterados para NULL.
13
Projeto Lógico
❑ Regras de Derivação do Modelo Conceitual
1) Toda classe deriva uma tabela. Os atributos são mapeados 
para colunas
▪ Exemplo:
Funcionario (matricula, nome,sexo, nascimento)
14
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)
15
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.
16
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(codigo)
funcionário referencia funcionario(matricula)
17
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 referencia Projeto(codigo)
funcionário referencia funcionario(matricula)
18
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.
19
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
Funcionario(matricula, nome, sexo, nascimento)
Departamento(codigo, nome, funcionario)
funcionario referencia Funcionario(matricula)
20
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.
1 1Possui
Funcionario
matricula
nome
sexo
nascimento
Endereço
rua
numero
complemento
bairro
cidade
estado
FUNCIONARIO ( matricula, nome, sexo, nascimento, rua, numero, 
complemento, bairro, cidade, estado )
21
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 
atributos da associação, caso existam
22
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(codigo)
fornecedor referencia Fornecedor(codigo)
peca referencia Peca(codigo)
23
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(matricula)
24
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)
25
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. 
26
Projeto Lógico
❑Regras de Derivação do Modelo Conceitual
Contribuinte
endereco
telefone
PessoaFisica
CPF
nome
nascimento
Contribuinte
CNPJ
razaoSocial
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)
idcontribuinte referencia Contribuinte(id)
27
❑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
28
Projeto Lógico
❑Regras de Derivação do Modelo Conceitual
Contribuinte
endereco
telefone
PessoaFisica
CPF
nome
nascimento
Contribuinte
CNPJ
razaoSocial
3a alternativa: Uma única tabela
Contribuinte ( id, endereco, telefone, CPF, nome,nascimento, CNPJ, 
razaoSocial, tipo_contribuinte )
Projeto Físico
❑ESTRUTURAS DE ARMAZENAMENTO
▪ Tablespaces
▪ Índices
▪ Tabelas
29
Projeto Físico
❑Estruturas de Armazenamento
Lógica Física
Tablespace
Tabela
Índice
1
*
1
*
Arquivo1
*
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
❑Exemplos:
1. Create table DEP (codigo char(5), nome varchar(20)) tablespace dbspace;
2. Create index ind_nome on DEP(nome) tablespace idspace;
06/03/2020 31
Create tablespace dbspace location ‘/data/dbs’;
Create tablespace idspace location ‘/data/ids’;
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 0
250 CASTRO RIO 1
221 NEVES BH 2
RID NOME
1 CASTRO
0 MENDES
2 NEVES
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 é logn .
• Acesso seqüencial (ordenado) pelo campo indexado.
❑ Desvantagens
☹Maior espaço de armazenamento.
☹Inclusão, exclusão, alteração mais lentos.
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.
➢ Em chaves estrangeiras (Facilitam a junção)
Prof. Luiz A. Vivacqua 
C. Meyer
❑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. 
Projeto Físico
❑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.
Projeto Físico
❑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.
Projeto Físico
❑ 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
Projeto Físico
❑ 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';
Projeto Físico
• 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'
Projeto Físico
❑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
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
Projeto Físico
A Linguagem de Definição de Dados:
• Subcategoria deSQL 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
– CREATE VIEW
Projeto Físico
❑Criando tabela
CREATE TABLE nome-da-tabela
( definição-da-coluna1 , definição-da-coluna2, ..., definição-da-
colunaN )
ONDE
definição-da-coluna é definida como:
nome-da-coluna tipo-de-dado [CONSTRAINT]
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)
▪ character varying(n), varchar(n) – limite 1GB
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
▪ através do comando ALTER TABLE
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
RENAME nome_da_coluna TO novo_nome_coluna
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
CHECK (SEXO IN (‘M’, ‘F’));
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;
/* Remove do banco de dados a tabela Funcionario */
Projeto Físico
❑Criando Índice
▪ Sintaxe: CREATE INDEX 
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:
• DROP INDEX IND_NOME;