Projeto de Banco de Dados - files.vivacquabd.webnode.com.brfiles.vivacquabd.webnode.com.br/200000156-91b9893acc/Projeto... · Regras de Derivação do Modelo Conceitual Associações

Prof. Luiz A. Vivacqua C. Meyer31/08/2016 1

Projeto de Banco de Dados

Projeto de Banco de DadosProf. Luiz Antônio Vivacqua C. Meyer

([email protected])

Prof. Luiz A. Vivacqua C. Meyer2


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, ...)



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



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



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


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 estruturas como colunas.


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

Integridade Referencial


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 é referenciada tem seus valores alterados para NULL.


Projeto Lógico

Regras de Derivação do Modelo Conceitual1) Toda classe deriva uma tabela. Os atributos são mapeados

para colunas

Exemplo:

Funcionario (matricula, nome,sexo, nascimento)


Projeto Lógico

Regras de Derivação do Modelo Conceitual2) 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 Conceitual3) 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)

funcionário referencia funcionario(id)


Projeto Lógico

Regras de Derivação do Modelo Conceitual4) 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)


funcionário referencia funcionario(id)


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

Funcionario(matricula, nome, sexo, nascimento)

Departamento(codigo, nome, funcionario)

funcionario referencia Funcionario(matricula)


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 )


Projeto Lógico


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


Projeto Lógico


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)


fornecedor referencia Fornecedor(id)

peca referencia Peca(id)


Projeto Lógico

Regras de Derivação do Modelo Conceitual7) 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


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


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)


Projeto Lógico


Contribuinte

endereco

telefone

PessoaFisica

CPF

nome

nascimento

Contribuinte

CNPJ

razaoSocial

2ª alternativa: Uma tabela para cada classe concreta da hierarquiaPessoaFisica (CPF, endereco, telefone, nome, nascimento)

PessoaJuridica (CNPJ, endereco, telefone, razaoSocial


Projeto Lógico


Contribuinte

endereco

telefone

PessoaFisica

CPF

nome

nascimento

Contribuinte

CNPJ

razaoSocial

3a alternativa: Uma única tabelaContribuinte ( id, endereco, telefone, CPF, nome,nascimento, CNPJ,

razaoSocial, tipo_contribuinte )

Prof. Luiz A. Vivacqua C. Meyer

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

Í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

220 NEVES BH 1245

RID NOME

4568 CASTRO

3870 MENDES

1245 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 buscafor igual ao campo indexado.

Busca no arquivo índice é mais rápida porque o arquivode índice é menor e é ordenado. Logo é possível fazer umacesso 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. MeyerProf. 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 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

– CREATE VIEW


Projeto Físico

Criando tabelaCREATE 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

ExemplosCREATE 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) REFERENCESDEPARTAMENTO (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;

Documents

Projeto de Banco de Dados - files.vivacquabd.webnode.com.brfiles.vivacquabd.webnode.com.br/200000156-91b9893acc/Projeto... · Regras de Derivação do Modelo Conceitual Associações