Aula 04 Modelo Fisico

Prévia do material em texto

Modelagem de Dados
MODELAGEM DE DADOS
MODELO FÍSICO
Profa. Rosemary Melo
Modelagem de Dados
2
Objetivo
 Elaborar Modelos Físicos de Dados
 Migrando do Modelo Lógico
ou
 Criando as tabelas diretamente no SGBD
2
MODELO FÍSICO
Modelagem de Dados
3
Na elaboração do Modelo Físico precisamos
Definir as tabelas no SGBD escolhido
Criar as Chaves
Definir restrições
Definir índices
...
3
MODELO FÍSICO
Modelagem de Dados
MODELO FÍSICO
DEFINIÇÃO DE RESTRIÇÕES DO MODELO RELACIONAL
Restrições podem ser definidas:
 No nível de coluna
 No nível de tabela (exceto NOT NULL)
Restrições podem ser criadas:
 No momento em que a tabela for criada (CREATE TABLE)
 Depois que a tabela tiver sido criada (ALTER TABLE)
Tipos de restrição:
 NOT NULL
 UNIQUE
 PRIMARY KEY
 FOREIGN KEY
 CHECK
Modelagem de Dados
MODELO FÍSICO
DEFINIÇÃO DE ÍNDICES
O que é um Índice?
Mecanismos utilizados para acelerar o acesso aos dados.
Um arquivo de índice consiste de registros (chamados de entradas de
índice) na forma:
Search Key (Chave de Busca) – Atributo ou conjunto de atributos usados
para procurar registros em um arquivo.
Ponteiro – Localização do registro no arquivo de dados
Três tipos básicos de índices:
•Índices Ordenados: as chaves de busca são armazenadas de forma
ordenada.
•Índices Hash: as chaves de busca são distribuídas uniformemente em
“buckets” usando uma “função hash”.
•Índices “Mapeados a Bit”: utiliza um único bit para representar a
ocorrência de determinado valor
Modelagem de Dados
MODELO FÍSICO
DEFINIÇÃO DE ÍNDICES
Três tipos básicos de índices:
 Índices Ordenados: as chaves de busca são armazenadas de forma
ordenada.
 Índices Hash: as chaves de busca são distribuídas uniformemente em
“buckets” usando uma “função hash”.
 Índices “Mapeados a Bit”: utiliza um único bit para representar a
ocorrência de determinado valor.
Modelagem de Dados
MODELO FÍSICO
DEFINIÇÃO DE ÍNDICES
Quando criar um Índice?
 São criados automaticamente quando definimos PRIMARY KEY ou
UNIQUE KEY.
 Sempre criar para colunas Foreign Key.
 Coluna contiver um grande número de valores nulos.
 Colunas forem usadas com freqüência em uma cláusula WHERE .
 A tabela for grande e a expectativa for de que a maioria das consultas
recuperará poucas linhas.
Observação:
Devem ser criados com moderação pois podem afetar o desempenho
de comandos DML (Insert, Update e Delete).
Modelagem de Dados
INTRODUÇÃO À SQL
LINGUAGENS DE DEFINIÇÃO E MANIPULAÇÃO DE DADOS
 Os SGBDs possuem duas linguagens:
 DDL (Linguagem de Definição de Dados): usada para
definir os esquemas, atributos, regras de integridade,
índices, etc.
DML (Linguagem de Manipulação de Dados): usada para
ter acesso aos dados armazenados no BD.
Exemplo de linguagem de comercial que implementam DDL e
DML no Modelo Relacional:
QUEL
QBE
SQL
...
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – STRUCTURED QUERY LANGUAGE
 Considerada linguagem de consulta padrão para SGBDR’S.
 Inicialmente chamada de SEQUEL.
 Desenvolvida pela IBM na década de 1970.
 Aceita por quase todos os produtos (SQL server, Oracle,
Interbase, etc)
 Utiliza os termos tabela, linha e coluna no lugar de relação,
tupla e atributo, respectivamente.
.
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – STRUTURED QUERY LANGUAGE
Possui diversas partes:
 Linguagem de Definição de Dados (DDL): fornece comandos
para definições de esquemas de relação, criação/remoção de
tabelas, criação de índices e modificação de esquemas.
 Linguagem de Manipulação de Dados (DML): inclui uma
linguagem de consulta baseada na álgebra relacional e cálculo
relacional de tupla. Compreende comandos para inserir,
consultar, remover e modificar tuplas num BD.
 Linguagem de Manipulação de Dados Embutido: designada
para acessar o BD dentro de linguagem de programação de uso
geral como Cobol, C, Pascal, PL-1, entre outros.
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DDL
 Inclui operações de definição de dados:
 criação de tabelas
Alteração de tabelas
Eliminação de tabelas
 Os comandos para definições de dados são:
 CREATE TABLE: criar uma tabela.
 ALTER TABLE: altera a definição (esquema) de uma
tabela.
 DROP TABLE: elimina uma tabela.
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – CRIAÇÃO DE TABELA
 CREATE TABLE: especifica uma nova relação, dando o seu
nome e especificando os seus atributos (cada um com seu
nome, tipo de dado e algumas restrições).
Sintaxe:
CREATE TABLE tabela_base (colunas tabela-base)
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – CRIAÇÃO DE TABELA
 As definições das colunas têm o seguinte formato:
coluna tipo_de_dados [NOT NULL [UNIQUE]]
Onde:
coluna: nome do atributo que está sendo definido
tipo_de_dado: domínio do atributo
NOT NULL: expressa que o atributo não pode receber
valores nulos.
UNIQUE: indica que o atributo tem valor único na
tabela. Qualquer tentativa de se introduzir uma linha na
tabela contendo um valor igual ao do atributo será
respeitado.
 Usamos o qualificador UNIQUE para indicar os atributos que
fazem parte de chave primária. Uma outra forma de fazê-lo
seria usar o qualificador primary key.
Modelagem de Dados
INTRODUÇÃO À SQL
EXEMPLO DE CRIAÇÃO DE TABELA: Seja o seguinte BD de
Peças e Fornecedores
CREATE TABLE Fornecedor (CodForm char(5) NOT NULL,
Nome char(20), Situação decimal(3), Cidade char(15),
PRIMARY KEY (CodForn))
CREATE TABLE Peça (CodPeca char(6) NOT NULL, Nome
char(10), Cor char(10), Peso decimal(3), Cidade char(15),
UNIQUE(CodPeca))
CREATE TABLE Malote (CodigoF char(5) NOT NULL, CodigoP
char(6) NOT NULL, Qtd decimal(5), PRIMARY KEY (CodigoF,
CodigoP))
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – DEFINIÇÃO DE TIPO DE DADOS
Tipos de Dados
A SQL ANSI suporta os seguintes tipos de dados:
 character
 numeric
 decimal
 integer
 smallint
 float
 real
 double precision
Não ANSI
 varchar
date
time
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – CRIAÇÃO DE CHAVE ESTRANGEIRA
 Se quisermos criar chaves estrangeiras para que a
integridade referencial seja validada, usa-se a cláusula
FOREIGN KEY.
Exemplo:
CREATE TABLE Malote
(codigof char(5) NOT NULL, codigo p char(6) NOT NULL,
quantidade decimal(5), PRIMARY KEY (codigof, codigop))
FOREIGN KEY (codigof) REFEENCES Fornecedor
FOREIGN KEY (codigop) REFEENCES Peca
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – CRIAÇÃO DE CHAVE ESTRANGEIRA
Exemplo:
CREATE TABLE Malote
(codigof char(5) NOT NULL REFERENCES Fornecedor,
codigop char(6) NOT NULL REFERENCES Peca,
quantidade decimal(5), PRIMARY KEY (codigof, codigop))
Pode-se abreviar desta maneira se a chave estrangeira for
composta de um único atributo.
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – ALTERAÇÃO DE TABELA
 ALTER TABLE: permite que se adicione novos atributos a uma
determinada tabela. Os novos atributos terão valores nulos em
todas as linhas.
 Ao incluir uma coluna deve-se especificar o tipo de
dado.
Sintaxe:
ALTER TABLE tabela_base
ADD atributo domínio
ou
ALTER TABLE tabela_base
DROP atributo domínio
:
:
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – ALTERAÇÃO DE TABELA
Exemplo:
ALTER TABLE Peca
ADD Espessura int
ALTER TABLE Peca
DROP Cidade
ALTER TABLE Peca
MODIFY Espessura float
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – ALTERAÇÃO DE TABELA
 Pode-se usar ALTER TABLE para definir chaves primárias e
estrangeiras.
Exemplo:
ALTER TABLE Malote
ADD PRIMARY Key (CodigoF)
ALTER TABLE Malote
ADD FOREIGN KEY (CodigoF) REFERENCES Fornecedor
ALTER TABLE Malote
DROP PRIMARY KEY
ALTER TABLE Malote
DROP FOREIGN KEY (CodigoF)
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – ALTERAÇÃODE TABELA
 Quando uma chave primária ou estrangeira é eliminada com
ALTER TABLE, as colunas da chave e seus valores não são
eliminados, mas sim a restrição.
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – REMOÇÃO DE TABELA
 DROP TABLE: exclui uma tabela-base do BD. Remove tanto os
dados quanto a definição da tabela.
 Não permite apagar a tabela se esta estiver sendo
referenciada por outra tabela (foreing key)
Sintaxe:
DROP TABLE tabela_base
Modelagem de Dados
INTRODUÇÃO À SQL
DDL – REMOÇÃO DE TABELA
Exemplo:
DROP TABLE Malote
DROP TABLE Peca
DROP TABLE Fornecedor
Modelagem de Dados
Exercicios
24
• Considere uma loja virtual. A loja trabalha com diversos produtos.
Cada produto contem um codigo, nome, descricao, preco,
fornecedor e quantidade disponivel em estoque. Os produtos
tambem são categorizado com codigo e categoria. Ex. produtos de
informática, eletrodomesticos, livros, etc. Muitos fornecedores
podem fornecer o mesmo produto. Assim, a especificação de um
produto depende do fornecedor que o produz.
• Os clientes compram via internet. Por isso precisam se cadastrar
(cpf, nome, email, telefone e endereco). Eles colocam produtos em
um carrinho e depois concluem ou desistem da compra. Quando
uma compra é efetivada é gerada uma nota fiscal, calculado o valor
total da compra e guardada a data da compra. A compra é paga
sempre em cartao de credito. Deve ser guardado o número do
cartão e o código da autorização obtida da operadora de cartão.
• Para o cenário acima, construa: Modelo conceitual, Modelo lógico
e Modelo Físico.
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DML
 Após a definição do banco de dados inicia-se a manipulação
dos dados através das seguintes operações:
 Consulta dados de tabelas
 Insere dados em tabela
 Altera dados em tabela
 Exclui dados em tabela
 As operações de manipulação são:
 SELECT: consulta dados em tabela.
 INSERT: insere dados em tabela.
 UPDATE: altera dados em tabela.
 DELETE: exclui dados em tabela.
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DML
 Permite a realização de operações de restrição, projeção,
junção, através das instruções SQL.
Exemplo:
Restrição
Select Fcod, Pcod, Qtd
From prod_fornec
Where Qtd<150
Projeção
Select Fcod, Cidade
From Fornecedor
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DML
Exemplo:
Junção
Select Fornecedor.Fcod, Fnome, Status, Cidade, Pcod, Qtd
From Fornecedor, Prod_fornec
Where Fornecedor.Fcof = Prod_forncec.Fcof
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DML
Outros exemplos de manipulação de dados
Select * from fornecedor
Insert into fornecedor
(Fcod, Fnome, Status, cidade) 
Values
(1, 'bompreço’,2,’Salvador’)
Insert into temp (Pcod, peso)
Select Pcod, peso
From produto
Where cor=cor(‘vermelho’)
Modelagem de Dados
INTRODUÇÃO À SQL
SQL – DML
Outros exemplos de manipulação de dados
Delete from prod_fornec where Pcod=‘P2’
Update fornecedor
Set status = 2 * status, cidade = ‘Roma’
Where cidade=‘Paris’
Modelagem de Dados
Sintaxe SQL - Insert
• Utilizado para inserir dados em uma tabela
– Não é obrigatório colocar todos os campos
– A ordem dos campos não precisa ser a mesma ordem da tabela
– Quando se deseja incluir valores para todos os campos, pode-se 
emitir a lista de campos. Neste caso é assumido a ordem dos campos 
da tabela
INSERT INTO nome_da_tabela
(campo1, ..., campon)
VALUES
(valor1,...,valorn)
Ex.: 
jinto fornecedor
(Fcod, Fnome, Status, cidade)
Values
(1, ‘bompreço’,2,’Salvador’)
No SQLServer
INSERT into cliente (cliente, nome, telefone)
values (1001,'João','445-0988')
INSERT into cliente (nome, cliente, telefone)
values ('Carlos',1005,null)
INSERT into cliente
values (1006,'Viu só?','999-0000')
Modelagem de Dados
Sintaxe SQL - Update
• Utilizado para alterar os dados já cadastrados nas tabelas
– A condição é opcional, mas quando não especificada indica uma 
alteração em todos os registros da tabela!
UPDATE nome_da_tabela
SET nome_do_campo1 = novo_valor1, ..., 
nome_do_campon=novo_valorn
[WHERE condição]
Ex.: 
update fornecedor
set status = 2 * status, cidade = ‘Roma’
where cidade=‘Paris’
No SQLServer
UPDATE cliente
SET telefone = ‘000-1111’
UPDATE cliente
SET telefone = '111-0000'
WHERE cliente = 1004
Modelagem de Dados
Sintaxe SQL - Delete
• Utilizado para excluir registros já cadastrados nas tabelas. 
– A condição é opcional, mas quando não especificada indica 
uma exclusão de todos os registros da tabela!
DELETE FROM nome_da_tabela
[WHERE condição]
Ex.: 
Delete from prod_fornec
where Pcod=‘P2’
No SQLServer
DELETE cliente
WHERE matr = 1006
DELETE cliente
Modelagem de Dados
Sintaxe SQL - Select
• Utilizado para recuperar dados cadastrados no BD
– Pode utilizar critérios para a recuperação
SELECT lista_de_campos
FROM lista_de _tabelas
WHERE condições
Ex.:
Select * from fornecedor
No SQLServer
SELECT nome, telefone 
FROM cliente
SELECT * FROM cliente
SELECT * FROM cliente 
WHERE matr = 1500
Modelagem de Dados
SQL - Exercícios 
• Criar o seguinte modelo no SGBD adotado
Campo Tipo Chave Req
Matricula Inteiro PK Sim
Nome String(40) Sim
Telefone String(11) Sim
DataEntrada Date Não
Aluno
Campo Tipo Chave Req
Registro Autoincremento PK Sim
Nome String(40) Sim
Telefone String(11) Sim
Salario Valor Sim
Professor
Campo Tipo Chave Req
CodDisc Autoincremento PK Sim
Nome String(40) Sim
CargaHor Int Sim
Disciplina
Turma
Campo Tipo Chave Req
CodTurma Autoincremento PK Sim
Registro Int FK Sim
Semestre Int Sim
Ano int Sim
CodDisc Int FK Sim
Campo Tipo Chave Req
CodTurma Int PK, FK Sim
Matricula Int PK,FK Sim
Media Valor Não
Faltas Int Nao
Aluno_turma
Modelagem de Dados
SQL - Exercícios 
35
• Construir os comandos de inclusão para cada uma das tabelas criadas 
no exercício anterior:
– Tabela Aluno
• Mat:1 Nome: Maria Telefone: 2222-3344
– Tabela Professor
• Registro 1 nome: Ana Telefone: 555-6677
Salario:1000
– Tabela Disciplina
• Codigo 1 Nome:Banco1 CargaHor: 60
– Tabela Turma
• CodTurma 1 Registro 1 Semestre 2 Ano 2012 CodDisc
1 
– Tabela AlunoTurma
• CodTurma 1 Matricula 1 Media 8 Faltas 5
Modelagem de Dados
SQL - Exercícios 
36
• Construir os comandos de alteração para cada uma das tabelas 
criadas no exercício anterior. Considerar alteração de um único 
registro por vez.
– Tabela Aluno
• Alterar o telefone de um aluno
– Tabela Professor
• Alterar o salario de todo os professores para 10000
– Tabela Disciplina
• Altarar o nome e a carga horaria de uma disciplina
– Tabela Turma
• Alterar o ano da turma
– Tabela AlunoTurma
• Alterar a media e as faltas de um aluno
Modelagem de Dados
SQL - Exercício
37
• Construir os comandos de exclusão para cada uma das tabelas criadas 
no exercício anterior. Considerar alteração de um único registro por 
vez.
– Tabela Aluno
• Excluir um aluno
– Tabela Professor
• Excluir um professor
– Tabela Disciplina
• Excluir uma disciplina
– Tabela Turma
• Excluir uma turma
– Tabela AlunoTurma
• Excluir um aluno da turma
– Delete AlunoTurma where codTurma=1 and Mat=1
Modelagem de Dados
Para refletir
• Considere as tabelas abaixo
codTurma Matricula Media Faltas
1 1 10 0
1 2 7 6
1 3 5 2
2 1 9 0
2 3 8 2
2 4 7 0
 O que aconteceria se os seguintes comandos fossem executados:
 Insert into TurmaAluno (1,5,3,8)
 Update turmaAluno set faltas=9
 Delete turmaAluno where codTurma=2 and Matricula=3
 Update turmaAluno set Media=10 where codTurma=2 and
Matricula=5
Modelagem de Dados
SQL – Exercício• Considere o banco Acadêmico do exercício anterior
– Criar as seguintes sentenças:
• Consultar todos os alunos
• Consultar nome e telefone de todos os alunos
• Consultar nome e salário de todos os professores
• Consultar carga horária de uma disciplina qualquer 
(informando o codigo da disciplina)
Modelagem de Dados
SQL – Exercício
Lembrete:
- Se for usar o SGBD MySQL e quiser executar um arquivo com
todos os scripts SQL, você deve primeiro salvar o script no
bloco de notas com extensão SQL, no mesmo diretório do
mysql: \mysql\bin\nomedoarquivo.sql. Em sequida você deve
executar o mysql, depois executar no prompt do mysql os
seguintes comandos: mysql> source nome do arquivo.sql.
- Para executar o mysql, primeiro você deve executar o arquivo
mysqld para em seguida executar o arquivo mysql, ambos
estão dentro do diretório bin do mysql.
Modelagem de Dados
SQL – Exercício
Comandos no MySql:
- use NomedoBanco: seta o banco de dados que irá usar. 
- show Database: lista todos os banco de dados do SGBD.
- show tables: lista as tabelas do banco em uso.
- show columns from nometabela: mostra todas as colunas 
criadas para a tabela. (4.6.8.1. Recureparando Informações 
sobre Banco de Dados, Tabelas, Colunas e Índices – Manual 
MySql).
Modelagem de Dados
SQL – Exercício
Comandos SQL no MySql:
- Caso queira renomear uma coluna de uma tabela para outro 
nome especificando seu tipo. 
Ex.: ALTER TABLE t1 CHANGE a b INTEGER;
- Caso queira mudar um tipo de coluna, mas não o nome. 
Usando o comando ALTER: 
Ex1.:ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
Ex2.: ALTER TABLE t1 MODIFY b BIGINT NOT NULL;