BANCO DE DADOS
Araújo Lima Jun / 2018
Índice
• Conceitos de Bancos de Dados
• Modelos de dados
• Modelos Lógicos de Dados
• Modelo de Entidades e Relacionamentos
• Mapeamento MER → MR
• Modelo Relacional
• Normalização
• Linguagem SQL
• Consultas mais complexas
• Stored Procedure
• Triggers
CONCEITOS DE BANCO DE DADOS...
• CHU - 1983
Um banco de dados é um conjunto de arquivos relacionados entre si
• DATE - 1985
Um banco de dados é uma coleção de dados operacionais armazenados usados pelos
sistemas de aplicação de uma determinada organização
...CONCEITOS DE BANCO DE DADOS...
• ELMARSI & NAVATHE - 1989
Um banco de dados é uma coleção de dados relacionados
• ENGLES
Um banco de dados é uma coleção de dados operacionais usados pelos sistemas aplicativos de uma empresa
...CONCEITOS DE BANCO DE DADOS...
Dados Operacionais
Projetos Funcionários
Departamentos Produtos Fornecedores
Aloca
Fornece Lota
...CONCEITOS DE BANCO DE DADOS...
disc1 disc2 BD2 BD1 BD2 LP2
PreRequisito
matric turma nota
0001 TN1 8
0002 TM2 10
Historico
matric nome sexo curso
0001 Maria F SI 0002 Pedro M CC 0003 Carlos M SI
turma disc ano semestre prof
TN1 BD1 2013 1 Luis
TM2 LP2 2014 2 Edna
TN2 BD2 2013 2 Luis
Turma
disc nome cred
BD1 Bco Dados 1 6 BD2 Bco Dados 2 6
Disciplina Aluno
Banco de Dados – coleção de Dados Operacionais
...CONCEITOS DE BANCO DE DADOS...
disc1 disc2
PreRequisito
matric turma nota
Historico
matric nome sexo curso turma disc ano semestre prof
Turma
disc nome cred
Disciplina Aluno
Dados Operacionais
Aluno (matric, nome, sexo, curso) Historico (matric, turma, nota)
Turma (turma, disc, ano, semestre, prof) Disciplina (disc, nome, cred)
Esquema do BD
...CONCEITOS DE BANCO DE DADOS...
cpf, nome, ender, dt-nasc nr-conta, sld-atu, sld-med, cpf
SGBD cpf, nr-cotas
CONTA CORRENTE FUNDO DE INVESTIMENTO
SISTEMAS QUE USAM SGBD
Banco de Dados
Aplicativos Oracle
PostgreSql Sql Server MySQL
...CONCEITOS DE BANCO DE DADOS...
• Um BD representa aspectos do mundo real
• Um BD é projetado, construído e os dados são nele armazenados com algum propósito
• Um BD é definido e mantido por uma coleção de programas (SGBD)
• Sistema de Banco de Dados = Dados + Programas (SBD = BD + SGBD )
...CONCEITOS DE BANCO DE DADOS...
Características de um SGBD:
• Controle de redundância
Sem repetição de dados não há inconsistência
• Compartilhamento de dados
Acesso multiusuário com controle de concorrência garantido
...CONCEITOS DE BANCO DE DADOS
Características de um SGBD:
• Dados separados de programas
Definição das estruturas de dados armazenada fora dos programas de aplicação e dentro do SGBD (catálogo): alteração de estruturas de dados evita recompilação de programas
MODELOS DE DADOS ...
Ferramenta usada para descrever a estrutura de um BD
Compõe-se de:
• Estruturas de dados
• Operações
• Restrições de integridade
... MODELOS DE DADOS ...
Conceitual
Lógico Físico
Modelo Conceitual
descreve dados num nível de abstração muito próximo ao mundo real
Exemplos:
• Modelo Semântico
• MER
Mundo real
... MODELOS DE DADOS ...
Conceitual
Lógico
Físico
Modelo Lógico
descreve dados ao nível de registro bem próximo da implementação física
.
Exemplos:
• Relacional
• Rede
• Hierárquico
Mundo real
... MODELOS DE DADOS ...
Conceitual Lógico
Físico
Modelo Físico
descreve as estruturas de dados ao nível de armazenamento
físico dos dados, definindo:
• Tipo e tamanho de dados
• Índices
Mundo real
... MODELOS DE DADOS
Esquema
Descrição gráfica ou textual de um BD conforme um modelo de dados
Exemplo de esquema relacional:
FUNC(mat, nome, sal, dep) DEPA(dep, descr)
DEPE(mat, seq, nomd,sexd)
MODELOS LÓGICOS DE DADOS ...
RELACIONAL
• Entidades como tabelas e relacionamentos como colunas em pares de tabelas
• Restrições de Integridade:
– componente de PK não pode ser null
– para cada valor de FK em uma tabela deve existir um valor de PK em outra tabela
• Operações: seleção, projeção, junção, união ...
... MODELOS LÓGICOS DE DADOS ...
RELACIONAL
• Dados armazenados de forma tabular
• Usuário vê um conjunto de tabelas
• Ligações implícitas compartilhando chaves num nível lógico
• Navegação automática: otimizador de query
... MODELOS LÓGICOS DE DADOS ...
A
B C
D E
A 2 B 1
3 C 1 4 D 2 1
5 E 2 3
MER RELACIONAL
... MODELOS LÓGICOS DE DADOS
• SGBD´S RELACIONAIS
– ORACLE – INGRES – DB2
– SQL SERVER – MYSQL
– POSTGRESQL
MODELO DE ENTIDADE E RELACIONAMENTO (MER) ...
• proposto por Peter S. Chen - 1976
• entidade
-algo sobre o que se armazena dados
-representação abstrata de algo do mundo real ex.: aluno, cliente, nota fiscal, ...
• conjunto de entidades - grupo de entidades com características semelhantes ex.: alunos, clientes, notas fiscais,...
... MER ...
• relacionamento - associação entre elementos de conjuntos de entidades
• relacionamento binário - é um par ordenado (e1,
e2), onde e1 e e2 são respectivamente
elementos dos conjuntos das entidades E1 e E2
ALUNOS CURSA DISCIPLINAS
MAT NOM NOTA PERÍODO COD DES
n m
... MER ...
• conjunto de relacionamentos - grupo de relacionamentos do mesmo tipo ;
no caso binário, conjunto de pares ordenados c (a, d) onde: a e d D
• grau de um relacionamento - número de conjuntos de entidades envolvidos no
relacionamento.
ex.: unário, binário, ternário, ...
... MER ...
• classe ou cardinalidade dos relacionamentos razão entre as quantidades de entidades com que cada
conjunto participa do relacionamento
• exemplos de classes de relacionamentos:
– binários: 1 : 1 1 : n m : n – ternários: 1 : m : n m : n : p
... MER ...
PROJETOS
FUNCIONÁRIOS DEPARTAMENTOS
CHEFIA TRABALHA
ALOCA
GERENCIA
DT-POSSE DT-ALOC
HOR-TRAB
n m
n
1 1
1
1 n
... MER ...
• RELACIONAMENTO UNÁRIO OU RECURSIVO OU AUTORELACIONAMENTO
PESSOAS PRODUTOS
CASA COMPÕE
ESPOSA MARIDO
1 1 M N
É-COMPOSTO É-COMPONENTE PAPEL DA ENTIDADE NO RELACIONAMENTO
... MER ...
• RELACIONAMENTO TERNÁRIO
ITENS P-R-I REQUISIÇÕES
PEDIDOS
M N
P
- A UM ITEM E UM PEDIDO CORREPONDEM N REQUISIÇÕES - A UMA REQUISIÇÃO E UM PEDIDO CORRESPONDEM M ITENS
... MER
• RELACIONAMENTO TERNÁRIO ...
PROFESSORES P-A-D ALUNOS
DISCIPLINAS
1 N
M
- UM ALUNO CURSA UMA DISCIPLINA COM UM PROFESSOR - UM PROFESSOR MINISTRA UMA DISCIPLINA PARA N ALUNOS - UM PROFESSOR MINISTRA PARA UM ALUNO M DISCIPLINAS
CHAVES ...
• chave primária - conjunto de atributos que
identificam uma única entidade ex.: MATF + NRD, MAT, CDD ...
• chave candidata - conjunto de atributos que podem ser chave primária ex.: MAT, RG, CDD, ...
DEPARTS FUNCS DEPENDS
CDD NMD MAT NMF SEX RG
SAL CDDEP
MATF NRD NMD
chave secundária ou alternada - conjunto de atributos que identificam um grupo de
entidades ex.: SAL, SEX, ...
... CHAVES ...
DEPARTS FUNCS DEPENDS
CDD NMD MAT NMF SEX RG SAL CDDEP
MATF NRD NMD
DEPARTS FUNCS DEPENDS CDD NMD MAT NMF SEX RG
SAL
CDDEP
MATF NRD NMD
... CHAVES ...
•chave estrangeira - conjunto de atributos que são chave primária em outro conjunto de entidades ex.:CDDEP, MATF , ...
... CHAVES ...
• chave externa ou surrogate key - número sequencial sem qualquer semântica, introduzido para ser chave primária de um conjunto de entidades
ex.:NRD, MAT, ...
DEPARTS FUNCS DEPENDS
CDD NMD MAT NMF SEX RG
SAL CDDEP
MATF NRD NMD
... CHAVES
• como escolher a chave primária – deve ser a menor possível
– valor não deve mudar ao longo da vida da entidade
– não pode ser null (nem parcial, nem totalmente)
MAPEAMENTO MER --> MR...
FUNCIONÁRIOS PROJETOS
MAT NOM CDP NMP
ALOCA DT-ALOC
FUNCIONÁRIOS PROJETOS
MAT NOM CDP NMP
N 1
CDP
DT-ALOC
chave primária do lado 1 vai para o lado n
atributos do relacionamento vão para o lado n
1 : N
...MAPEAMENTO MER --> MR...
ALUNOS DISCIPLINAS
CURSA
M N
CDA NMA NOTA PERÍODO CDD NMD
ALUNOS AL-DI DISCIPLINAS
NMA CDD NMD
NOTA PERÍODO CDA
M : N
. quebra relacionamento m : n em dois 1 : n
. atributos do relacionamento ficam na entidade intermediária . chaves primárias das entidades fazem parte da chave primária
CDA CDD
...MAPEAMENTO MER --> MR...
MÉDICOS DIRIGE HOSPITAIS
1 1
CDM NMM CDH NMH
DT-POSSE
MÉDICOS HOSPITAIS
CDM NMM CDH NMH CDM
DT-POSSE
1 : 1
. atributos do relacionamento vão para a entidade menos populosa
. chave primária da entidade mais populosa vai ser
chave estrangeira na entidade menos populosa
...MAPEAMENTO MER --> MR...
FUNCIONÁRIOS CHEFIA 1
N CHEFE
SUBORDINADO MAT
NOM
FUNCIONÁRIOS
MAT NOM MATCHEFE
...MAPEAMENTO MER --> MR...
PEÇAS COMPÕE
M
N COMPONENTES
COMPOSTOS COD
DES
PEÇAS COMPÕE
CD-COMPONENTE QTD
QTD
CD-COMPOSTO COD
DES
...MAPEAMENTO MER --> MR...
FUNCIONÁRIOS ALOCA DEPARTAMENTOS
1 N
GERENCIA
1 1
CURSOS CURSA
M CHEFIA N
1 N
MAT NOM CDD DES
NMC CDC
DT-ALOC DT-POSSE
...MAPEAMENTO MER --> MR...
FUNCIONÁRIOS DEPARTAMENTOS
CURSOS CURSA
MAT NOM
CDD NMD
DT-POSSE
CDC NMC MAT CDC DT-CUR
MAT-CH
CHEFE SUBORDINADO
CDD DT-ALOC
ALOCA
MAT-GER
GERENCIA
...MAPEAMENTO MER --> MR...
MODELO CONCEITUAL MODELO
LÓGICO MODELO
FÍSICO
MER ...
MODELO RELACIONAL...
Mundo real
...MAPEAMENTO MER --> MR...
A R B
CA AA AR CB AB
A B
1 1
CA AA CB AB
CB AR
A R B A B
1 N
CA AA AR CB AB CA AA CB AB
CA AR
A R B
CA AA AR CB AB
M N
A A-B B
CA AA AR CB AB
CA CB
...MAPEAMENTO MER --> MR...
Relacionamento M X N se transforma em 2
relacionamentos 1 : N (1 nas pontas, N no meio)
FUNCIONÁRIOS LOTAÇÕES DEPARTAMENTOS
MAT NOM MAT CDD DAT CDD NMD
100 ARI 200 ANA 300 EVA
VEN VENDAS PRO PRODUÇÃO
ADM ADMINISTRAÇÃO 100 ADM JAN / 80
100 VEN MAR / 85 200 VEN OUT / 86 300 PRO MAR / 90
...MAPEAMENTO MER --> MR
A R B
C
M N
P
A R B
C CA AA
CC AC
CB AB AR
CC AC
CB AB CA AA
CA CB CC
AR
Modelo Relacional ...
• O modelo relacional foi proposto em um paper clássico, por Ted Codd em 1970
• O modelo tem como base matemática a teoria dos conjuntos e usa o conceito de relação
matemática que se assemelha a uma tabela bidimensional
• O modelo relacional usa as operações da Álgebra Relacional
... Modelo Relacional ...
• Os elos são implícitos
• O usuário vê o banco de dados como um conjunto de relações (tabelas)
FUNCIONÁRIOS
MAT NOME DEPART 100 ANA VEN
200 EDU PRO 300 ARI ADM 400 EVA O&M
relação (tabela) atributos
tuplas (linhas) valor de atributo domínios
... Modelo Relacional ...
UMA RELAÇÃO PODE SER REPRESENTADA
SOB A FORMA DE TABELA, ONDE:
- Cada coluna representa um atributo
- Cada linha representa uma ocorrência de:
* UMA ENTIDADE
* UM RELACIONAMENTO - Linha (tupla) é um conjunto de atributos
... Modelo Relacional ...
UMA RELAÇÃO PODE SER REPRESENTADA SOB A FORMA DE TABELA, ONDE: ...
- DOMÍNIO - conjunto de valores do atributo
- GRAU DE UMA RELAÇÃO - número de atributos da tupla da relação
- CHAVE PRIMÁRIA - um ou mais atributos que identificam uma única linha (tupla)
- EX.: ALUNO (MAT, NOM, DT-NASC, SEXO)
... Modelo Relacional
Esquema relacional
Func (mat, nom, sal, sex, dtNasc, cdd, match) Depa (cdd, dsd, orcamento)
Depe (mat, seq, nomd, sexd, parentesco) Proj (cdp, dsp)
FunPro (mat, cdp, horasTrab) Curso (cdc, dsc, cargaHoraria) FunCurs (mat, cdc, datc, nota)
NORMALIZAÇÃO ...
• Simplificação de relações a fim de que os
algoritmos de atualização de dados sejam mais simples e mais claros
• Como sub-produto: ELIMINAÇÃO DA REDUNDÂNCIA DE DADOS
• Resultado das pesquisas de Edgar Frank CODD
É a decomposição de um esquema de relação em outros esquemas de relação com a finalidade de minimizar :
- Redundâncias
- Anomalias de inserção, remoção e atualização
Os esquemas resultantes devem manter a semântica original:
- restrições de integridade - dados e relacionamentos
... NORMALIZAÇÃO ...
cdLoja nmLoja cdProd nmProd qtEstoq
L1 Loja Hum P1 Prod Hum 10
L2 Loja Dois P2 Prod Dois 20
L1 Loja Hum P3 Prod Tres 15
L3 Loja Tres P2 Prod Dois 18
cdL nmL cdP nmP cdL cdP qtd
L1 Loja Hum P1 Prod Hum L1 P1 10
L2 Loja Dois P2 Prod Dois L2 P2 20
L3 Loja Tres P3 Prod Tres L1 P3 15
L3 P2 18
... NORMALIZAÇÃO ...
Normalizando
... NORMALIZAÇÃO ...
• a matrícula de um funcionário determina
funcionalmente o nome bem como o salário do funcionário
MATRÍCULA NOME
MATRÍCULA SALÁRIO
Se A determina funcionalmente B, então B depende funcionalmente de A, logo:
... NORMALIZAÇÃO ...
• DEPENDÊNCIA FUNCIONAL
FUNC ( MAT, NOME, SAL, CPF)
MAT
NOME
SAL
CPF
MAT NOME MAT SAL MAT CPF CPF SAL CPF NOME CPF MAT
... NORMALIZAÇÃO ...
• DEPENDÊNCIA FUNCIONAL COMPLETA um conjunto de atributos B de R é completamente
dependente funcionalmente de outro conjunto de atributos A de R, se B depende funcionalmente de A, como um todo
... NORMALIZAÇÃO ...
• TAREFA ( MAT, NOM, NU-PROJ, NM-PROJ, HORAS-TRAB) MAT, NU-PROJ HORAS-TRAB
MAT NOM
MAT NOM NU-PROJ NM-PROJ HORAS-TRAB
... NORMALIZAÇÃO ...
• QUAIS AS CHAVES DAS RELAÇÕES ABAIXO ?
PEDIDO (NR-PED, NR-CLIENTE, DT-PED, VL-PED)
HIST-ALUNO (MAT,NOM,CD-DISCIPLINA,NOTA, PERIODO) VENDA (NR-PED, NR-CLI, NR-VENDEDOR, CD-ITEM, QT- VENDIDA,PREÇO-UNIT, VL-ITEM)
... NORMALIZAÇÃO ...
• 1FN-NÃO HÁ GRUPO DE ATRIBUTOS REPETIDOS
FUNC (MAT, NOM, ENDER, DT-NASC, DEPENDENTES (NM-DEP, DT-NASC-DEP, PARENTESCO))
A relação não está em 1FN (RELAÇÃO NF2) há n dependentes (grupos repetidos de atributos ) por funcionário
Normalizando, teríamos:
FUNC ( MAT, NOM, ENDER, DT-NASC) DEPEN (MAT, SEQ, NM-DEP, DT-NASCD,PARENTESCO)
... NORMALIZAÇÃO ...
• 2FN - A RELAÇÃO ESTÁ EM 1FN - OS ATRIBUTOS NÃO-CHAVE DEPENDEM
FUNCIONALMENTE DA CHAVE TODA
ESTOQ(CD-ITEM, NM-ITEM, ESTAB, FONE-ESTAB, QTD)
CD-ITEM NM-ITEM ESTAB FONE-ESTAB
ESTOQ não está em 2FN: nm-item e fone-estab não dependem da
chave toda. Normalizando:
ITEM (CD-ITEM, NM-ITEM) ESTA (ESTAB, FONE-ESTAB)
... NORMALIZAÇÃO ...
• 3FN - A RELAÇÃO ESTÁ EM 2FN
- OS ATRIBUTOS NÃO-CHAVE SÃO INDEPENDENTES ENTRE SI
ITEM-PED (NR-PED, NR-ITEM, QTD, PR-UNIT, VL-ITEM)
NR-PED NR-ITEM
QTD PR-UNIT
VL-ITEM
ITEM-PED NÃO ESTÁ EM 3FN : VL-ITEM DEPENDE DE PR-UNIT E DE QTD QUE NÃO SÃO CHAVE
Normalizando:
IT-PED(NR-PED,NR-IT,QTD,VL-IT)
... NORMALIZAÇÃO ...
• normalização x performance
- ideal do ponto de vista de performance:
todos os dados em um único registro - ideal do ponto de vista de simplicidade:
várias relações em 5FN
• como melhorar a performance em bd`s relacionais - criar índice (evita varrer tabela) - armazenar resumos
... NORMALIZAÇÃO ...
• NOTA-FISCAL(NR-CLI, NM-CLI, EN-CLI, NR- NF, (CD-IT, DS-IT, QTD, PREÇO,VAL), TOT-NF, CD-VENDEDOR, NM-VENDEDOR)
• FICARIA ARMAZENADA COMO:
NR-CLI NM-CLI EN-CLI NR-NF CD-IT DE-IT 1206 SILVA RU X,20 468 126 PARAF 1206 SILVA RU X,20 468 130 PORCA 1206 SILVA RU X,20 476 140 ARRUEL 1300 MARIA RU Y,30 477 126 PARAF 1300 MARIA RU Y,30 477 130 PORCA
... NORMALIZAÇÃO ...
• ANOMALIAS
- INCLUSÃO – incluir novo item exige nota fiscal - EXCLUSÃO DE UM REGISTRO - exige verificar
existência do item mais de uma vez para não perder os dados desse item com a exclusão
- ALTERAÇÃO - mudar endereço do cliente 1206 implica alterar vários registros
... NORMALIZAÇÃO ...
• As anomalias existem por falta de independência de
dados: cliente misturado com nota fiscal, misturado com produto, com vendedor.
NORMALIZANDO ....
CLIEN (NR-CLI, NM-CLI,EN-CLI)
NFIS (NR-NF, TOT-NF, NR-CLI,CD-VENDEDOR) IT-NF(CD-IT, DS-IT, QTD, PREÇO,VAL,NR-NF)
VENDEDOR (CD-VENDEDOR, NM-VENDEDOR)
... NORMALIZAÇÃO ...
•IT-NF (CD-IT, DS-IT, QTD, PREÇO, VAL, NR-NF)
ANOMALIAS NA RELAÇÃO ACIMA:
INCLUSÃO - só inclui um item se houver venda
ALTERAÇÃO - alterar descrição de um item, altera vários registros
EXCLUSÃO - excluir único registro que contém um item perde dados desse item
ALÉM DISSO, DS-IT DEPENDE FUNCIONALMENTE
... NORMALIZAÇÃO ...
• ITENS (CD-IT, DS-IT, PREÇO)
• IT-NF (NR-NF, CD-IT, QTD, VAL)
• CLIEN(NR-CLI, NM-CLI, EN-CLI)
• VEND (CD-VENDEDOR, NM-VENDEDOR)
• NFIS (NR-NF,NR-CLI,TOT-NF,CD-VENDEDOR)
... NORMALIZAÇÃO
CLIEN NFIS IT-NF
ITENS VEND
NR-NF CD-IT
QTD
VAL NR-NF
CD-VENDEDOR TOT-NF
NR-CLI
NR-CLI NM-CLI EN-CLI
PRECO
DER
EQUIVALENTE
Linguagem SQL ...
• Linguagem de Consulta Estruturada
• Linguagem comercial, declarativa, padrão para acesso a bancos de dados relacionais
• Introduzida como linguagem de consulta para o sistema R (inicialmente, SEQUEL)
• Trata conjuntos de tuplas (linhas)
• Álgebra Relacional é formal e procedural
... Linguagem SQL ...
• Cria e manipula as estruturas de dados:
– TABLE - tabela real formada por linhas e colunas
– INDEX - índice para otimizar o acesso às linhas das tabelas
– VIEW - tabela virtual formada a partir de
outras tabelas (apenas a definição da visão é armazenada em disco)
... Linguagem SQL ...
mat nom sex sal cdd matCh 100 Zé M 1200,00 P 300
200 Sá M 1210,00 P 300 300 Ed M 1300,00 P 500 400 Dé M 1320,00 V 500 500 Di F 1500,00 A
FK FK
PK FUNC
Tabela
... Linguagem SQL ...
Possui comandos DDL, DML, DCL
• DDL - Linguagem de Definição de Dados Create, Drop, Alter
• DML - Linguagem de Manipulação de Dados Select, Insert, Update, Delete
• DCL - Linguagem de Controle de Dados Commit, Rollback, Grant, Lock
... Linguagem SQL ...
DML- Linguagem de Manipulação de Dados Comandos:
• Select – obtém linhas de uma tabela
• Insert – insere linhas numa tabela
• Update – atualiza linhas de uma tabela
• Delete – exclui linhas de uma tabela
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• SELECT [ALL | DISTINCT] {colunas | *}
FROM tabela1 [nome de correlação], ...
[WHERE condição de linha]
[GROUP BY colunas]
[HAVING condição de grupo]
[ORDER BY coluna1 [ASC | DESC], ...]
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• select * from func
exibe todos os atributos de todas as linhas da tabela func
• select mat, nom from func where sal > 900
exibe matrícula e nome dos funcionários com salário superior a 900
projeção – exibe apenas mat e nom seleção – exibe linhas onde sal > 900
... Linguagem SQL ...
DML- Linguagem de Manipulação de Dados
• select * from func order by sal desc
exibe todos os atributos de todos os funcionários em ordem decrescente de salário
• select * from func order by nom
exibe todos os atributos de todos os funcionários em ordem alfabética
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• select count(*), sum(sal) from func
exibe a quantidade de funcionários e a soma dos salários de todos
• select avg(sal) from func
exibe a média salarial dos funcionários
Funções de agregação: count, sum, avg, min, max
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• select cdd, count(*), sum(sal), avg(sal) from func group by cdd
exibe para cada departamento, o código, a
quantidade de funcionários, a soma dos salários e a média salarial do departamento
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• select * from func, depa
exibe todas as linhas resultado do produto cartesiano de func e depa (func X depa)
• select * from func, depa where func.cdd = depa.cdd
exibe o resultado da junção, ou seja as linhas que têm mesmo valor de cdd em func e em depa (func
depa)
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• select * from clien where uf = 'PI' union select
* from forn where uf = 'PI' -- união
Obtém os clientes ou fornecedores do PI
• select * from clien where uf = 'PI' intersect select * from forn where uf = 'PI' -- interseção
Obtém os clientes que são também fornecedores, do PI
• select * from clien where uf = 'PI' minus select * from forn where uf = 'PI' -- diferença
Obtém os que são clientes mas não fornecedores, do PI
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
select nom from forn where uf = 'PI' union select nom from clien where uf = 'CE'
exibe nomes, sem repetir, dos fornecedores do Piauí ou dos clientes do Ceará
select nom from forn where uf = 'PI' union all select nom from clien where uf = 'CE'
idem, com repetição de nomes
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
INSERT INTO tabela [(coluna1,...)] VALUES ({literal / NULL / valor}, ...)
insert into func values (800, ‘Rui’, ‘M’, ‘PRO’)
inclui na tabela func um novo funcionário com valores (na mesma ordem) para todos os atributos
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
INSERT INTO tabela [(coluna1,...)] VALUES ({literal / NULL / valor}, ...)
insert into func (mat, nom) values (900, ‘Ana’)
inclui na tabela func novo funcionário com valores para apenas os atributos mat e nom
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
UPDATE tabela SET coluna = {valor / NULL} WHERE condição
update func set nom = ‘Gal’ where nom = ‘Gil’
altera para Gal o nome do funcionário Gil
... Linguagem SQL ...
DML - Linguagem de Manipulação de Dados
• update func set sal = sal * 2
duplica o salário de todos os funcionários
• update func set sal = sal * 1.3 where cdd = ‘P’
dá um aumento de 30% aos funcionários do departamento de código ‘pro’ (produção)
... Linguagem SQL
DML - Linguagem de Manipulação de Dados DELETE FROM tabela WHERE condição
• delete from func where mat = 200
exclui o funcionário de matrícula = 200
• delete from func
exclui todos os funcionários (cuidado !!!)
Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func where dep is null
exibe os atributos dos funcionários sem valor de código de departamento (NULL)
• select mat, nom, sal * 12 from func order by 3 desc
exibe matrícula, nome e salário anual dos funcionários em ordem decrescente de salário anual (terceiro
atributo)
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func where cdd in ('V', 'P')
exibe atributos dos funcionários cujo código do
departamento pertence ao conjunto que tem os elementos 'V' e 'P'
• select * from func where cdd not in ('V','P')
exibe atributos dos funcionários cujo código do
departamento não pertence ao conjunto que tem os
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func
where mat between 150 and 500
exibe os atributos dos funcionários de valor de matrícula entre 150 e 500, incluindo os extremos
• select * from func
where mat not between 150 and 500
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func where nom like 'A_B%'
exibe os atributos dos funcionários que têm nome iniciado por A e a terceira letra do nome igual a B (ALBER, ALBERTO, ALBA, ALBINA, ALBINO,
AMBLER, ...)
• select * from func where nom not like 'A_B%'
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func where nom like '%a'
exibe os atributos dos funcionários que têm nome
terminado em a (Francisco Lima, Laura, Maria, Diva, Eva, Lia, Sa)
• select * from func where nom not like '%a'
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select * from func where nom like 'A%'
exibe os atributos dos funcionários que têm nome iniciado com A (Ana Maria, Alves, Ari, Araujo)
• select * from func where nom not like 'A%'
... Consultas mais complexas ...
DML- Linguagem de Manipulação de Dados
• select distinct cdd from func
exibe os diferentes códigos de departamento
existentes na tabela func (havendo 20 funcionários em Produção, 5 em Vendas e 3 em Administração, o resultado da consulta acima será):
A P V
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
• select cdd, count(*), sum(sal), avg(sal) from func group by cdd having count(*) > 3
exibe para cada departamento com mais de 3
funcionários, o código, a quantidade de funcionários, a soma dos salários e a média salarial do
departamento
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
• select count(*) as x from func where sal < 150
• select count(*) as y from func where sal >= 150
• select count(*) as z from func Seria possível z > x + y ?
Seria possível se houvesse algum funcionário com salário NULL (mais problema com NULL)
• select * from func where sal is null
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
• select count(*) from func where sex = 'M'
• select cdd, count(*) from func where sex = 'M' group by cdd
• select cdd, count(*) from func where sex = 'M' group by cdd having count(*) > 3
where - testa condição de linha having - testa condição de grupo
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
• select nom, sal, dsd from func, depa where depa.cdd = func.cdd
exibe nome, salário e nome do departamento dos funcionários
junção (join) entre as tabelas func e depa, com base na igualdade entre PK (primary key) de depa e FK (foreign key) em func
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
• select nom, sal from func
where sal > (select avg(sal) from func)
exibe nome, salário dos funcionários com salário superior à média salarial da empresa
subselect - select dentro de select; o que está entre parênteses é executado antes
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados
Exibir o total de dependentes dos funcionários do departamento de produção
select count(*) from depe where mat in (select mat from func where cdd in
(select cdd from depa where dsd = 'Produção'))
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados Exibir dados dos funcionários sem dependentes
• select * from func where mat not in (select distinct mat from depe)
Exibir dados dos funcionários com salário maior que
qualquer salário de funcionário do departamento de código 'P'
• select * from func where sal >
any (select sal from func where cdd = 'P')
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados func (mat, nom, sal, matChefe)
• select su.nom, su.sal, ch.nom from func su, func ch where su.matCh = ch.mat
exibe nome e salário de cada funcionário e, na mesma linha, o nome do seu chefe
implementação de autorelacionamento 1: n su, ch - alias ou nome de correlação
... Consultas mais complexas ...
DML - Linguagem de Manipulação de Dados func (mat, nom, sal, matChefe)
select su.nom, su.sal from func su, func ch where su.matCh = ch.mat and su.sal > ch.sal
exibe nome e salário dos funcionários que
ganham mais que seus respectivos chefes implementação de autorelacionamento 1 : n
su, ch - alias ou nome de correlação
... Consultas mais complexas ...
• INNER JOIN – resultam da operação, apenas as linhas em que haja igualdade entre o par de
atributos (por exemplo, PK = FK)
• OUTER JOIN – resultam da operação, além das linhas em que PK = FK, também as linhas de
uma tabela sem correspondência na outra tabela
... Consultas mais complexas ...
INNER JOIN ou apenas JOIN
func(mat, nom, cdd) depa(cdd, dsd) select * from func join depa
on func.cdd = depa.cdd /* Inner Join */
• Resultado mostra só os funcionários que estão alocados em algum departamento e apenas
departamentos que têm pelo menos um funcionário alocado ( Inner join)
select * from func, depa where func.cdd = depa.cdd /* produz mesmo resultado */
... Consultas mais complexas ...
LEFT / RIGHT OUTER JOIN
• select * from func left outer join depa on func.cdd = depa.cdd
Resultado mostra todos os funcionários, mesmo os que não estão alocados em nenhum departamento
• select * from func right outer join depa on func.cdd = depa.cdd
Resultado mostra todos os departamentos, mesmo os que não têm nenhum funcionário alocado
• A palavra outer pode ser suprimida
... Consultas mais complexas ...
FULL OUTER JOIN
• select * from func full outer join depa on func.cdd = depa.cdd
Resultado mostra todos os funcionários, mesmo os que não estão alocados em algum departamento e todos departamentos, mesmo os que não têm nenhum funcionário alocado ( Full Join)
• A palavra outer pode ser suprimida
... Consultas mais complexas ...
CROSS JOIN
• select * from func cross join depa
Resultado do comando acima é o produto cartesiano das tabelas func X depa (para 8 linhas em func e 4 linhas em depa, o resultado terá 32 linhas, cada uma delas formada pela concatenação de uma linha de func com uma linha de depa)
• select * from func, depa -- mesmo resultado
... Consultas mais complexas
• select 'nome:', nom from func
exibe a palavra nome: antes do nome de cada funcionário:
nome: Ana Maria nome: José Silva
...
• select 5 + 4 * 10 45
... Linguagem SQL ...
mat nom sex cdd 1 100 Zé M P 2 200 Sá M P 3 300 Ed M P 4 400 Dé M V 5 500 Di F A
Adm 5 Pro 1
Pro 2 Pro 3 Ven 4 cdd E
Idep Inom Nom E Dé 4 Di 5 Ed 3 Sá 2 Zé 1 Índices secundários Create index Idep on func (cdd)
Create index Inom on func (nom)
... Linguagem SQL ...
mat nom sex cdd 1 100 Zé M P 2 200 Sá M P 3 300 Ed M P 4 400 Dé M V 5 500 Di F A
100 1 200 2
300 3 400 4 500 5 mat E
Imat Inom Nom E Dé 4 Di 5 Ed 3 Sá 2 Zé 1 E
Imat é um índice primário (mat é PK)
... Linguagem SQL ...
Índices - estruturas de dados que agilizam o acesso às tuplas (linhas); podem ser:
• primário - campo de indexação é a chave primária não permitindo campos repetidos
• secundário ou alternado - campos de indexação são quaisquer atributos que não a chave primária
• clusterizado - a ordem das entradas no índice é a mesma das linhas da tabela
... Linguagem SQL ...
Índice agiliza consulta e prejudica atualização
• Evitar muitos índices em tabela muito volátil (alta taxa de inclusão / exclusão)
• Em tabela pouco volátil, muitos índices agilizam acessos; desvantagem: maior consumo de disco
• Cuidado ao indexar coluna atualizada com muita freqüência
• Grandes tabelas: recomendável criar os índices após a carga dos dados
... Linguagem SQL ...
Índice clusterizado
• Não é obrigatório, mas, apenas um por tabela
• Força os dados da tabela ficarem na mesma ordem das chaves do índice
• Favorece a leitura exaustiva mas prejudica muito a atualização
• O índice clusterizado deve ser o primeiro a ser criado entre os índices de uma tabela
... Linguagem SQL ...
O que indexar ?
• Chave primária (checa unicidade)
• Chave estrangeira (usada em junções e na integridade referencial)
• Chave secundária que seja argumento de pesquisa freqüentemente feita
• Nome usado para consulta alfabética on-line
• Atributo fonetizado usado em pesquisa fonética
... Linguagem SQL ...
VISÃO
• é como se fosse uma janela que nos permitisse ver um subconjunto de uma ou mais tabelas
• é uma tabela virtual derivada de uma ou mais tabelas (não duplica os dados)
• Exemplos:
– Marajás – funcionários com salário muito alto – Idosas – funcionárias com idade avançada
... Linguagem SQL ...
mat nom sex sal cdd matCh 100 Zé M 1200,00 P 300
200 Sá M 1210,00 P 300 300 Ed M 1300,00 P 500 400 Dé M 1320,00 V 500 500 Di F 1500,00 A
FUNC
As linhas em azul correspondem a uma visão:
... Linguagem SQL ...
mat nom sex sal cdd matCh
100 Zé M 1200,00 P 300 200 Sá M 1210,00 P 300 300 Ed M 1300,00 P 500 400 Dé M 1320,00 V 500 500 Di F 1500,00 A
FUNC
Em azul, uma visão que exibe nome e salário
... Linguagem SQL ...
DDL - Linguagem de Definição de Dados
• CREATE – cria objetos (table, index, view, procedure, trigger) no banco de dados
• DROP – remove objetos (table, index, view, procedure, trigger) do banco de dados
• ALTER – altera objetos (table, index, view, procedure, trigger) do banco de dados
... Linguagem SQL ...
Tipos de dados:
• integer - inteiro entre 32767 e -32768
• char- alfanumérico
• date - data
• decimal - numérico com casas decimais
• varchar - alfanumérico com tamanho variável
... Linguagem SQL ...
DDL - Linguagem de Definição de Dados
• CREATE TABLE nome-tabela (coluna1 tipo1 [NOT NULL] [, coluna2 tipo2 [NOT NULL] ...)
• create table aluno
(matric integer not null, -- matric exigida nome varchar (30),
sexo char(1), nasc date,
ender varchar (40))
... Linguagem SQL ...
DDL - Linguagem de Definição de Dados
• create table Func
(mat integer primary key, nom varchar(30) not null,
sex char(1) check (sex = 'M' or sex = 'F'), sal decimal (8,2) check (sal >879.99), cdd char(1) default 'P' references Depa
on delete set null, matCh integer references Func)
