Slides 3 e 4 - Fernando Zaidan

02/04/2009
Núcleo de Pós
Graduação
Pitágoras
Professor: Fernando Zaidan
Disciplina: Modelagem e
Projeto de Banco de Dados
Especialização em Tecnologia da
Informação - Ênfases
Março- 2009
1
Modelo Físico
Introdução a SQL
Histórico da linguagem SQL
• A ANSI e ISO publicou a padronização SQL-86, ANSI-89 e
ANSI-92; Novamente houveram revisões em 1999 e 2003 para
se tornar SQL-1999 e SQL-2003;
• Produtos adotam o SQL ANSI mas adicionam extensões
proprietárias;
• Ao usar um produto SQL, procure trabalhar voltado para o
padrão ANSI;
• SQL tornou-se o padrão para client/server:
Client --> entrada de dados com alguma consistência,
interface amigável, dependência lógica do servidor;
Server --> armazenamento, consulta, trabalho pesado
de I/O e integridade da informação;
1
02/04/2009
Cliente / Servidor
Regras
de
Negócio A li
Aplicação
ã
Regras de Negócio
Figura: Arquitetura Cliente / Servidor – SGDB
Fonte: http://www.sirmacstronger.eti.br/introdbd.php
Linha do tempo SQL
Histórico da linguagem SQL
• É uma linguagem não-procedural: ela diz o que fazer e
não como fazer;
• SQL é que torna possível o funcionamento de um SGBD sistema gerenciador de banco de dados;
• Poupa tempo de programação com entrada/saída de arquivos;
• SQL usa uma combinação de construtores em álgebra e cálculo
relacional;
2
02/04/2009
Padronização da SQL
• A implementação regras de negócio, caso o BD for proprietário
(comandos específicos da linguagem SQL), e não padronizado,
em uma migração do BD, fatalmente muitos comandos SQL
deverão ser reescritos para o novo BD.
• Padronização - é imprescindível para que se alcance um alto
grau de qualidade na engenharia de sistemas;
• Garantir a uniformidade e manutenibilidade dos objetos
construídos e disponibilizados;
Debate – padronizar SQL???
Objetivos da padronização
O estabelecimento de padrões implica em:
• Diminuição de custo na manutenção dos sistemas;
• Coesão dos dados e consistência nas informações fornecidas;
• Independência do fabricante;
• Portabilidade entre computadores;
• Estímulo à reutilização dos objetos.
Subdivisões da linguagem SQL
Um esquema de banco de dados precisa de uma linguagem:
DDL (Data Definition Language): linguagem de definição de
dados.
Permite a especificação da base de dados;
Definir as tabelas;
Comandos para esquemas de relação;
Criação de índices;
Ex.: create table, alter, drop, …
3
02/04/2009
Subdivisões da linguagem SQL
DML (Data Manipulation Language): linguagem de manipulação
de dados.
Permite a consulta e atualização de informações;
Abrange a álgebra e o cálculo relacional de tuplas;
Comandos para inserção
inserção, exclusão e modificação;
Ex.:
- select, insert, delete, …
Subdivisões da linguagem SQL
DCL (Data Control Language - Linguagem de Controle de
Dados).
Controla os aspectos de autorização de dados;
Também as licenças concedidas aos usuários;
Controle de quem tem acesso e quem pode manipular
dados dentro do banco de dados.
Ex.:
- grant, revoke, alter password, …
Subdivisões da linguagem SQL
DTL (Data Transaction Language - Linguagem de Transação de
Dados).
Inclui comandos para a especificação de iniciação e
finalização de transações;
Algumas implementações permitem o bloqueio de dados
para controle de concorrência;
Ex.: start transaction, commit, rollback, …
4
02/04/2009
Limitações da linguagem SQL
• SQL padrão oferece recursos limitados para o tratamento de
campos longos;
• SQL é uma linguagem mais voltada para descrever conjuntos e
suas relações;
ç
;
• Não-procedural, ou seja, não permite agrupar as palavras
chaves sob a forma de programas executáveis;
• Linguagens de consulta são diferentes das linguagens de
programação;
Limitações da linguagem SQL
• Não se espera que as linguagens de consulta sejam Turing
completas
• Turing completas - se puder ser demonstrado que a linguagem é
computacionalmente equivalente à máquina de Turing;
• As linguagens de consulta não foram pensadas para uso em
cálculos complexos;
• Linguagem de consulta – de mais alto nível que as de
linguagens de programação tradicionais.
DML
SELECT
• Sintaxe:
SELECT <lista de atributos>
FROM <nome das tabelas>
WHERE <condição de pesquisa / filtro>
Peca
Cod_Peca
Nome_Peca
Preco
Qte
56
Peca X
23,90
10
99
Peca Y
56,99
5
200
Peca Z
80,00
0
5
02/04/2009
• Selecionar o código e o nome das peças com código
menor do que 100:
SELECT Cod_Peca, Nome_Peca
FROM Peca
WHERE Cod_Peca < 100
• Selecionar todas as informações de todas as peças:
SELECT *
FROM Peca
Cod_Peca
Nome_Peca
Preco
Qte
56
Peca X
23,90
10
99
Peca Y
56,99
5
200
Peca Z
80,00
0
INSERT - Inserção
Sintaxe - Inserção Unitária:
INSERT INTO <tabela>
( <lista-de-colunas>)
VALUES ( <li
<lista-de-valores>)
t d
l
>)
– Inserir Cod_Peca, Nome_Peca, Preco
INSERT INTO Peca
(Cod_Peca, Nome_Peca,Preco)
VALUES (380,’Peca W’,77.00)
– Inserir uma Peca com todos os atributos
INSERT INTO Peca
VALUES (423,’Peca K’,100.00,15)
Sintaxe - Inserção
ç em Massa:
INSERT INTO <tabela1>
(<lista-de-colunas>)
SELECT ...
6
02/04/2009
UPDATE - Alteração
UPDATE <tabela>
SET <coluna1> = <expressão1>,
<expressão1>
<coluna2> =<expressão2>, ...
WHERE <condição-de-alteração>
Exemplos:
Alterar o Preco da peça 200 de 80,00 para 90,00
UPDATE Peca
SET Peso = 90.00
WHERE Cod_Peca = 200
Alterar o preco das peças cuja qte for menor que 10, para 50,00
UPDATE Peca
SET Preco = 50.00
WHERE Qte < 10 or Qte is null
DELETE (Exclusão)
DELETE FROM <tabela>
WHERE <condição-de-exclusão>
Excluir a p
peça
ç 200:
DELETE FROM Peca
WHERE Cod_Peca = 200
Excluir as peças que tem mais de 1000 na Qte:
DELETE FROM Peca
WHERE Qte > 1000
7
02/04/2009
ORDER BY - Ordenação
– Exibir os registros em uma determinada ordem.
Crescente (ASC – default) ou decrescente (Desc).
Caso a expressão não esteja presente, os registros serão
exibidos na ordem em que foram inseridos na tabela.
– Os campos que constam da expressão ORDER BY devem
obrigatoriamente estar presentes na expressão SELECT.
SELECT
Não é necessário possuir um índice fisicamente criado e
composto pelos campos da ordenação para usar o
ORDER BY. No entanto, caso o índice exista o comando
será executado mais rápido.
rápido Pode-se utilizar números
indicando que a ordenação será feita por determinado
campo de acordo com a ordem do SELECT.
Exemplos – Order by
SELECT Cod_Func, Nome
FROM Funcionario
ORDER BY Nome
SELECT Salario,
Salario Nome
FROM Funcionario
ORDER BY 1 DESC
 Nomes dos funcionários em ordem decrescente de
salário.
Funcionalidades do SQL
Operadores:
–
–
–
–
–
–
Comparação: =, <>, >, <, >=, <=
Lógicos: AND, OR, NOT
BETWEEN <expressão1> AND <expressão2>: testa intervalo
IN ( <lista de valores>): testa presença na lista
IS NULL: testa nulo
LIKE: testa conteúdo de string de caracteres
8
02/04/2009
Objetos básicos do SQL
• A linguagem do SQL Server tem os mesmos recursos
básicos das outras linguagens de programação comuns;
• Comentários  /* */ ou -- ;
• Identificadores:
– Constantes, variáveis e nome de programas;
• Palavras-chave reservadas;
Tipos de dados
• Tipos de dados numéricos;
• Tipos de dados de string;
• Tipos de dados para data e/ou hora;
• Tipos de dados derivados.
Tipos de dados Numéricos
INT
Representa valores inteiros
4 bytes
SMALLINT
Valores inteiros entre –32768 e 32767
2 bytes
DECIMAL(p,[s])
2 a 17 bytes
Descreve valores em ponto fixo. O argumento p
(precisão) especifica o número total de
algarismos com os dígitos s (escala) de ponto
decimal pressuposto à partir da direita
NUMERIC(p,[s])
Sinônimo de DECIMAL
9
02/04/2009
Tipos de dados Numéricos
FLOAT([p])
Representa valores em ponto flutuante, como
real. P define a precisão, com p < 25 como
precisão simples (4 bytes) e p >= 25 como
precisão dupla (8bytes) - 4 e 8 bytes
REAL
e DOUBLE
PRECISION
Usado para valores de ponto flutuante. A faixa
da valores positivos é de 2,23E-308 a 1,79E+308.
Valores negativos é de -2,23E-308 a -1,79E+308
Tipos de dados String
CHAR[(n)]
1 a 8000
bytes
Representa uma string, onde n é o número de
caracteres fixo dentro da string. O valor máximo
de n é 8000. Se n for omitido, o comprimento
suposto será 1.
VARCHAR[(n)] Descreve uma string de tamanho variável 1 a 8000
bytes.
BLOB (alguns SGBDs
SGBDs))
• Tipo que possui tamanho variável;
• Não é conhecido o tamanho correto momento de criação;
• Poderá ser usado para armazenar qualquer dado em que não se
sabe seu tamanho exato, como fotos, textos (memos), gráficos,
etc.
• Não pode ser indexado.
• No momento de criação de um campo BLOB, deve se observar seu
sub-tipo:
- usado para armazenar dados binários - fotos;
- usado para armazenar textos - memos;
10
02/04/2009
Date / Time
• DATE
dd/mm/aaaa ou dd.mm.aaaa
• TIME
hh:mm:ss
• TIMESTAMP
dd/mm/aaaa hh:mm:ss
• Modelo Físico de Dados
– Alguns modelos ignoraram os problemas relativos ao acesso
aos dados (performance, volume e custo) para se concentrar
exclusivamente na lógica da organização dos dados.
– O Modelo
M d l Fí
Físico
i d
dos D
Dados
d (MFD) ttem como objetivo
bj ti agir
i sobre
b
o Modelo Lógico dos Dados (MLD) assim como ter em conta as
especificidades informáticas na implementação.
• Modelo Físico de Dados
– A otimização do MLD e a análise do MFD devem ter em conta
os tratamentos realizados sobre os dados. Assim, otimizações
diferentes poderão ser propostas segundo o tipo de tratamento
efetuado sobre os dados.
– A organização física dos dados deverá ter em conta as
seguintes restrições:
• Volume dos dados na Base de Dados;
• Tempo necessário para acessar aos dados e
• Transferência dos dados entre a Base de Dados e a unidade
de Processamento.
11
02/04/2009
• Modelo Físico de Dados
Otimização:
– As performances das bases de dados relacionais são cada vez
mais elevadas devido a dois fatos principais:
• Módulo integrado de otimização de consulta.
• Paralelismo a nível do tratamento das consultas.
– No entanto, a otimização do MLD é uma etapa importante para a
implementação com sucesso de um sistema de informação.
• Modelo Físico de Dados
Otimização
– Devem-se tomar em conta dois tipos de otimização:
• A Otimização Física que consiste em tirar partido das
potencialidades do SGBD para uma implementação mais
eficiente;
• A Otimização Lógica que consiste em adaptar o MLD à
implementação. Deverá ser realizada com muito cuidado
devido à possível introdução de futuras incoerências quando
da evolução da Base de Dados.
Tunning
– A tradução literal de ‘tuning’ seria sintonia ou ajuste
de alguma coisa para que funcione melhor.
– Um SGBD é um produto de software sofisticado
permitindo vários ajustes.
– Por ser flexível, é possível fazer pequenos ajustes
que afetam a performance do banco de dados.
12
02/04/2009
O que é performance de banco de dados?
– Fazendo uma analogia em termos de oferta e
demanda. Os usuários demandam informações do
banco de dados. O SGBD fornece informação para
aqueles que o pedem.
pedem
– A taxa entre os pedidos que o SGBD atende e a
demanda para informação pode ser denominado
performance de banco de dados.
DDL - CREATE TABLE
Peca
Cod_Peca
Nome_Peca
Preco
Qte
56
Peca X
23,90
10
99
Peca Y
56,99
5
200
Peca Z
80,00
0
create table Peca (
Cod_Peca smallint not null,
Nome Peca varchar(30),
Nome_Peca
varchar(30)
Preco decimal(12,2),
Qte int,
CONSTRAINT chavepeca PRIMARY KEY
(Cod_Peca));
ou
alter table peca add constraint pkcodpec primary key
(cod_peca);
Modo Gráfico
13
02/04/2009
• Constraint  restrições que a tabela possui, incluindo
chave primária, unicidade de campos, default de
campos e verificação de consistências.
• Toda restrição constraint deve possuir um nome para
controle interno.
PRIMARY KEY constraint: garante a integridade de
entidade.
• Todas as colunas participantes de uma chave primária
devem ser NOT NULL;
• Apenas uma restrição PRIMARY KEY por tabela.
• Cria um índice exclusivo nas colunas especificadas.
UNIQUE constraint: como uma tabela possui somente
uma chave primária, as chaves alternativas ou
candidatas que sejam únicas são implementadas
através desta restrição.
• Permitem valores nulos;
• Permitem várias restrições UNIQUE em uma tabela.
FOREIGN KEY constraint: a tabela referenciada deve
possuir uma restrição de PRIMARY KEY ou UNIQUE.
• Esta restrição de chave estrangeira não cria índices
automaticamente;
• F
Fornecem uma integridade
i
id d referencial
f
i ld
de uma ou várias
ái
colunas;
14
02/04/2009
DEFAULT constraint: especifica o valor default que será
gravado em uma coluna quando o valor do campo não for
informado no momento do INSERT.
• Aplicam-se apenas a instruções INSERT;
• Apenas uma restrição DEFAULT por coluna;
CHECK constraint: especifica a validação do domínio do
campo.
• São usadas com as instruções INSERT e UPDATE;
• Podem fazer referência a outras colunas na mesma tabela;
• Não podem conter subconsultas.
Definição da Base de Dados em SQL
CREATE TABLE
CREATE TABLE table_name (
Atributo1Dominio1,
…,
AtributonDominion
<regras de integridade1>
…,
<regras de integridaden> )
Funcionario (Matric, Nome, Salario, Cargo, Estado,
Idade,Cod_Depto)
CREATE TABLE Funcionario (
Matric INT NOT NULL,
Nome CHAR(30) NOT NULL,
Salario DECIMAL NOT NULL
NULL,
Cargo CHAR(15) DEFAULT 'Analista',
Estado CHAR(2) NOT NULL,
Idade SMALLINT NOT NULL,
Cod_Depto SMALLINT NOT NULL,
CONSTRAINT chavefunc PRIMARY KEY (Matric),
CONSTRAINT uniconome UNIQUE (Nome),
CONSTRAINT checkestado CHECK (Estado IN ('MG', 'RJ', 'SP')))
15
02/04/2009
Departamento (Cod_Depto, Desc_Depto)
CREATE TABLE Departamento (
Cod_Depto SMALLINT NOT NULL,
Desc_Depto VARCHAR(30));
ALTER TABLE departamento ADD CONSTRAINT CHAVECOD
PRIMARY KEY (COD_DEPTO);
Departamento
Funcionario
Funcionario
----------------- ADD CONSTRAINT Depto_Func
ALTER TABLE Departamento
---------------FOREIGN KEY (Cod_Depto) REFERENCES Funcionario
Departamento
(Cod_Depto);
ÍNDICES
CREATE INDEX
• Os índices são estruturas físicas de banco de dados
criadas para otimizar a performance no acesso.
• Os comandos SELECT que envolvem ORDER BY ficam
mais rápidos após a criação de índices pelos campos de
ordenação.
• Para escolher bem os índices, analisa-se quais campos
da tabela participam das expressões WHERE de
comandos de SELECT, UPDATE e DELETE.
 Cuidado ao usar índices.
INDICES - Exemplos
• O índice deve ser o mais seletivo possível. Ex.: não se deve
criar um índice para o campo Sexo  só 2 valores.
CREATE [UNIQUE] INDEX Nome-do-índice
ON tabela (coluna ou lista-de-colunas)
• Exemplos:
CREATE INDEX IX_Cod_Depto ON Funcionario (Cod_Depto);
CREATE UNIQUE INDEX xfuncionarios ON Funcionario
(Matricula);
16
02/04/2009
• Os índices podem ou não ser únicos.
• O índice é único quando não se permitem repetições.
Índice pela chave primária é sempre único.
• Em alguns SGBDs este índice pela chave primária já é
gerado
d automaticamente.
t
ti
t
• Portanto, os índices são criados usualmente para as
chaves primárias e para as chaves estrangeiras,
visando agilizar os comandos que envolvem junção de
tabelas.
ALTER TABLE
• O comando ALTER TABLE pode ser usado para, por
exemplo, acrescentar colunas numa tabela. A coluna é
adicionada no final da tabela.
ALTER TABLE Funcionario ADD Aniversario DATE
ALTER TABLE Funcionario ADD CONSTRAINT
CHAVEFUNC PRIMARY KEY (MATRIC);
DROP TABLE
• O comando DROP TABLE elimina a estrutura da tabela e os
registros da mesma.
DROP TABLE Funcionario;
Em alguns SGBDs, ao se eliminar uma tabela, todas as estruturas
relacionadas a mesma (visões, índices) são também excluídos
automaticamente.
 Para apagar uma constraint:
ALTER TABLE tabela DROP CONSTRAINT nomeconstraint
 Se tentar apagar uma PK de um relacionamento:
Cannot delete PRIMARY KEY being used in FOREIGN KEY definition.
Se a PK não tiver relacionamentos = OK.
17
02/04/2009
Normalização
18
02/04/2009
19
02/04/2009
Exercícios
MER  Mdelo Lógico  Modelo Físico
Fonte: HEUSER, C. A.
MER  Passagem para o Modelo Lógico  Modelo
Físico
Modelo Lógico:
vendedor( cpf, telefone, nome, dt_nasc, sexo)
cidade( cep, nome)
cidade_vendas( cep (fk), cpf (fk), valor_da_venda)
Fonte: ROCHA, Rogério Morais.
Bons Estudos,
Prof. Zaidan
“As pessoas podem alterar suas vidas alterando suas atitudes.”
Willian James
20