Bancos de Dados Relacionais
Uma introdução prática
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
1
PROGRAMA
– 1.Introdução
• 1.1 Evolução do armazenamento de dados
• 1.2 Arquitetura de um DBMS
– 2.Modelo Relacional
• 2.1 Modelo E-R
• 2.2 Modelo Relacional
• 2.3 Operações
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
2
PROGRAMA
– 3.SQL Básico
•
•
•
•
EPUSP-PMR
3.1 Criação de tabelas
3.2 Queries simples
3.3 Subqueries
3.4 Agregação
Prof. Dr. Marcos Tsuzuki
3
PROGRAMA
– 4.SQL Avançado
•
•
•
•
•
EPUSP-PMR
4.1 Indexação
4.2 Joins
4.3 Views
4.4 Triggers
4.5 Constraints
Prof. Dr. Marcos Tsuzuki
4
PROGRAMA
– 5, 6, 7, 8. Apresentação do Projeto
• Cada aluno fará um projeto de banco de dados
especificando o caso de estudo, a aplicação, a
modelagem do banco de dados, o fluxo de
informação e como utilizá-lo como Data Ware
House.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
5
1.Introdução
1.1 Evolução do armazenamento de dados
1.2 Arquitetura de um DBMS
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
6
1.1 Introdução: Evolução do armazenamento de dados
Evolução dos sistemas de armazenamento de massa
•Memória
•Sistema de arquivos
•Acesso seqüencial
•Acesso direto
•Acesso indexado
•Bancos de dados
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
7
1.1 Introdução: Evolução do armazenamento de dados
Características de um DBMS
• Usuários devem poder definir seu schema (estrutura lógica
dos dados), usando uma linguagem apropriada (DDL, data
definition language)
• Usuários devem poder realizar perguntas e acessos (“query”)
sem conhecimento da estrutura de armazenamento dos dados,
usando uma linguagem apropriada (DML, data manipulation
language ou query-language)
• Usuários devem poder acessar grandes volumes de dados de
forma eficiente
• Usuários devem poder acessar dados de forma independente
uns dos outros, sob controle de acesso
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
8
1.1 Introdução: Evolução do armazenamento de dados
Evolução histórica dos DBMS
•Hierárquico (modelo em árvore, ‘60)
Stone
Schwarzenegger
Basic Instinct
Total Recall
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
9
1.1 Introdução: Evolução do armazenamento de dados
Evolução histórica dos DBMS
•Rede (CODASYL, fim ‘60)
Stone
Schwarzenegger
Basic Instinct
Total Recall
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
10
1.1 Introdução: Evolução do armazenamento de dados
Evolução histórica dos DBMS
•Relacional (Codd, ‘70)
Stone
Basic Instinct
Schwarzenegger
Total Recall
Stone
Total Recall
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
11
1.2 Introdução: Arquitetura de um DBMS
Arquitetura de um DBMS
Schema
Queries
Query Processor
Transaction Mng
Storage Manager
Data
Metadata
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
12
1.2 Introdução: Arquitetura de um DBMS
Storage Manager
Composto por
• File Manager, responsável pela manipulação do disco
• Buffer Manager, responsável pelos dados em memória
(cache, paginação)
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
13
1.2 Introdução: Arquitetura de um DBMS
Query Manager
Composto por
• Query translator (de SQL para acessos a dados armazenados)
• Query optimizer
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
14
1.2 Introdução: Arquitetura de um DBMS
Transaction Manager
• Garante as propriedades ACID
• Atomicidade (Atomicity): uma transação é indivisível; ou
ela acontece ou não
• Consistência (Consistency):Antes e após uma transação, o
estado do banco de dados é consistente
• Isolação (Isolation):Se duas ou mais transações
acontecem simultaneamente, seus efeitos devem ser
isolados
• Durabilidade (Durability): Se uma transação se completa,
seus efeitos não devem cessar mesmo que o sistema falhe
imediatamente após
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
15
1.2 Introdução: Arquitetura de um DBMS
Transaction Manager
• Lock
• Define o nível de isolação
• Página
• Linha
• Logging
• Armazenamento não volátil
• Commit
• Durabilidade da transação
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
16
1.2 Introdução: Arquitetura de um DBMS
Transaction Manager
• Índice
• Hash
• Balanced tree
• Binary tree
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
17
2.Modelo Relacional
2.1 Modelo E-R
2.2 Modelo Relacional
2.3 Operações
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
18
2.1 Modelo E-R
Ciclo de Projeto
Requisitos
Projeto Conceitual
Schema conceitual
Projeto Lógico
E-R
Schema lógico
Projeto Físico
Schema físico
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
SQL
19
2.1 Modelo E-R
Conceitos básicos
• Entidade
– Representam classes do mundo real
• Relacionamento
– Representam agregações entre duas ou mais
entidades
– Os relacionamentos podem ser
• Binários: entre duas entidades
• N-ários: entre N entidades
• Recursivos: de uma entidade para ela própria
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
20
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• 1 para 1
–
–
–
–
min_card(Ordem, remessa) = 0
max_card(Ordem, remessa) = 1
min_card(Fatura, remessa) = 1
max_card(Fatura, remessa) = 1
Ordem
EPUSP-PMR
(0,1)
remessa
(1,1)
Prof. Dr. Marcos Tsuzuki
Fatura
21
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• 1 para n
–
–
–
–
min_card(Pessoa, mora_em) = 0
max_card(Pessoa, mora_em) = 1
min_card(Cidade, mora_em) = 0
max_card(Cidade, mora_em) = n
Pessoa
EPUSP-PMR
(1,1)
mora_em
(0,n)
Prof. Dr. Marcos Tsuzuki
Cidade
22
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• n para n
–
–
–
–
min_card(Produto, usa) = 1
max_card(Produto, usa) = n
min_card(Materiais, usa) = 0
max_card(Materiais, usa) = n
Produto
EPUSP-PMR
(1,n)
usa
(0,n)
Prof. Dr. Marcos Tsuzuki
Materiais
23
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• Recursivo (1-n)
–
–
–
–
min_card(Empregado, chefia) = 0
max_card(Empregado, chefia) = n
min_card(Empregado, responde_a) = 0
max_card(Empregado, responde_a) = 1
Empregado
(0,n)
gerencia
chefia
(0,1)
responde_a
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
24
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• N-ário
Matéria
(1,3)
aula
(0,40)
Sala
(0,n)
Dia
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
25
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• N-ário
Aluno
(1,n)
aula
(0,n)
Disciplina
(0,1)
Professor
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
26
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• N-ário
– Separar a entidade Aluno e analisar o par Professor,
Disciplina. Para cada par Professor/Disciplina podemos ter
de 1 até N Alunos relacionados;
– Separar a entidade Professor e analisar o par Aluno,
Disciplina. Para cada par Aluno/Disciplina podemos ter 1 e
somente 1 Professor relacionado;
– Separar a entidade Disciplina e analisar Professor, Aluno.
Para cada par Professor/Aluno podemos ter de 1 até N
Disciplinas relacionadas.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
27
2.1 Modelo E-R
Cardinalidade dos Relacionamentos
• N-ário
– Quando um aluno está matriculado em uma disciplina, este
tem sempre um professor;
– Um aluno pode estar matriculado em várias disciplinas;
– Uma disciplina tem vários alunos, e somente um professor;
– Um professor leciona uma disciplina para vários alunos.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
28
2.1 Modelo E-R
Decidir a Cardinalidade
• Modelagem de Dados é uma Arte
– Nota Fiscal, Item de Nota Fiscal e Produto
Nota Fiscal
(1,n)
possui
(1,1)
Item de NF
(1,1)
associa
(1,n)
Produto
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
29
2.1 Modelo E-R
Decidir a Cardinalidade
• Modelagem de Dados é uma Arte
– Todas as notas fiscais têm, no mínimo, um item de nota fiscal
relacionado;
– Todo item de nota fiscal está relacionado a uma nota fiscal;
– Todo item de nota fiscal está relacionado a um produto.
• A entidade item de NF está relacionando NF e produto.
Ela também é conhecida como Entidade Associativa.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
30
2.1 Modelo E-R
Atributos
• Atributos são propriedades elementares de
entidades e relacionamentos
Nome
Pessoa
RG
Profissão
mora_em
Data_da_mudança
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
31
2.1 Modelo E-R
Identificadores (chaves)
• Identificadores (chaves) são conjuntos de
atributos que podem determinar unicamente
uma entidade
Nome
Pessoa
EPUSP-PMR
RG
Profissão
Prof. Dr. Marcos Tsuzuki
32
2.1 Modelo E-R
Identificadores (chaves)
• Simples ou compostos
• Internos ou externos
Empregado (1,1)
trabalha_no
(1,n) Departamento
ID
Nota: Empregado é uma entidade fraca
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
33
2.1 Modelo E-R
Agregação
• Um assassino faz vítimas. Quando ele assassina,
utiliza armas.
Assassino
Assassina
Vítima
Usa
Arma
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
34
2.1 Modelo E-R
Agregação
• Existem na realidade dois relacionamentos para retratar um fato
por completo.
• O que desejamos é relacionar uma ocorrência de Arma, com
uma ocorrência do fato, do relacionamento Assassina.
Assassino
Faz
Crime
Tem
Vítima
Usa
Arma
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
35
2.1 Modelo E-R
Agregação
•
Um médico atende a muitos pacientes, que o consultam, e um paciente pode
realizar consultas com muitos médicos. Sempre que um paciente consulta um
médico, este fornece uma receita, que pode ter um, ou vários remédios.
Consulta
Médico
Atende
Consulta
Paciente
Receita
Remédio
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
36
2.1 Modelo E-R
Problemas 2.1
a.Prepare um diagrama E-R para um sistema de
controle de pedidos
b.Mostre dois identificadores para a entidade
DETALHE
PRODUTO
CABEÇALHO
nro_linha
EPUSP-PMR
DETALHE
Prof. Dr. Marcos Tsuzuki
37
2.2.Modelo Relacional
Conceitos básicos
• Relação: tabela bi-dimensional
• Atributos: nomes das colunas da tabela
• Schema: nome da relação e atributos
– Filme(Título, Ano, Duração, Classificação)
• Tupla: linhas de uma relação
– (“Star Wars”, 1977, 124, Ficção)
• Domínio: valores que um atributo pode assumir
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
38
2.2.Modelo Relacional
Formas normais
• O conceito de normalização foi introduzido por E.F.Codd em
1970 (primeira forma normal). Esta técnica é um processo
matemático que tem seus fundamentos na teoria dos conjuntos.
• Através deste processo pode-se, gradativamente, substituir um
conjunto de entidades e relacionamentos por um outro, o qual se
apresenta purificado em relação às anomalias de atualização
(inclusão, alteração e exclusão) as quais podem causar certos
problemas, tais como: grupos repetitivos (atributos
multivalorados) de dados, dependências parciais em relação a
uma chave concatenada, redundância desnecessária de dados,
dificuldade na representação de fatos da realidade observada e
dependências transitivas entre atributos.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
39
2.2.Modelo Relacional
Fomulário de Pedido
Num. Ped. Cliente
3445
TCA
3445
TCA
3445
TCA
3445
TCA
3445
TCA
3445
TCA
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
2610
Lopes
EPUSP-PMR
Endereço
R. Meira
R. Meira
R. Meira
R. Meira
R. Meira
R. Meira
R. 127
R. 127
R. 127
R. 127
R. 127
R. 127
R. 127
R. 127
R. 127
R. 127
CGC
1111111
1111111
1111111
1111111
1111111
1111111
23232323
23232323
23232323
23232323
23232323
23232323
23232323
23232323
23232323
23232323
IE
111111
111111
111111
111111
111111
111111
34343434
34343434
34343434
34343434
34343434
34343434
34343434
34343434
34343434
34343434
Cod. Prod. Unid.
45
L
130
M
335
Kg
78
Kg
90
L
39
Kg
45
L
78
Kg
21
Kg
98
L
90
L
43
M
25
F
65
L
51
M
74
M
Quant.
Prof. Dr. Marcos Tsuzuki
20
2
30
50
40
3
50
47
20
15
15
10
10
5
20
30
Descrição Val.
álcool
tecido
farinha
cimento
cola
chumbo
álcool
cimento
pregos
tinta azul
cola
arame
algodão
querosene
fio elétrico
linha 10
Unit. Tot. Prod. Tot. do Ped.
5,00
100,00
1799,00
20,00
40,00
1799,00
1,00
30,00
1799,00
30,00 1500,00
1799,00
3,00
120,00
1799,00
3,00
9,00
1799,00
5,00
250,00
2650,00
30,00 1410,00
2650,00
5,00
100,00
2650,00
25,00
375,00
2650,00
3,00
45,00
2650,00
3,00
30,00
2650,00
2,00
20,00
2650,00
8,00
40,00
2650,00
13,00
260,00
2650,00
4,00
120,00
2650,00
40
2.2.Modelo Relacional
Fomulário de Pedido (Atributos)
•
•
•
•
•
•
•
•
•
•
Número do pedido
Prazo de entrega
Cliente
Endereço
Cidade
UF
CGC
Inscrição Estadual
Código do produto
Unidade do produto
EPUSP-PMR
•
•
•
•
•
•
•
Quantidade do produto
Descrição do produto
Valor unitário do produto
Valor total do produto
Valor total do pedido
Código do vendedor
Nome do vendedor
Prof. Dr. Marcos Tsuzuki
41
2.2.Modelo Relacional
Formas normais
• Caso esta entidade fosse implementada como uma
tabela em um banco de dados, as seguintes anomalias
iriam aparecer:
– Anomalia de inclusão: ao ser incluído um novo cliente, o
mesmo tem que estar relacionado a uma venda;
– Anomalia de exclusão: ao ser excluído um cliente, os dados
referentes as suas compras serão perdidos;
– Anomalia de alteração: caso algum fabricante de produto
altere a faixa de preço de uma determinada classe de
produtos, será preciso percorrer toda a entidade para se
realizar múltiplas alterações.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
42
2.2.Modelo Relacional
Primeira Forma Normal (1FN)
•
•
•
•
Em uma determinada realidade, às vezes encontramos algumas informações
que se repetem (atributos multivalorados), retratando ocorrências de um
mesmo fato dentro de uma única linha e vinculadas a sua chave primária;
Ao observarmos a entidade PEDIDO, apresentada anteriormente,
visualizamos que um certo grupo de atributos (produtos solicitados) se repete
(número de ocorrências não definidas) ao longo do processo de entrada de
dados na entidade;
A 1FN diz que: cada ocorrência da chave primária deve corresponder a uma e
somente uma informação de cada atributo, ou seja, a entidade não deve conter
grupos repetitivos (multivalorados);
Para se obter entidades na 1FN, é necessário decompor cada entidade não
normalizada em tantas entidades quanto for o número de conjuntos de
atributos repetitivos. Nas novas entidades criadas, a chave primária é a
concatenação da chave primária original mais o(s) atributo(s) do grupo
repetitivo visualizado(s) como chave primária deste grupo.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
43
2.2.Modelo Relacional
Primeira Forma Normal (1FN)
•
•
Ao aplicarmos a 1FN sobre a entidade PEDIDO, obtemos mais uma entidade
chamada ITEM-DE-PEDIDO, que herdará os atributos repetitivos e
destacados da entidade PEDIDO;
Um PEDIDO possui no mínimo 1 e no máximo N elementos em ITEM-DEPEDIDO e um ITEM-DE-PEDIDO pertence a 1 e somente 1 PEDIDO.
Pedido
EPUSP-PMR
(1,n)
Possui
(1,1)
Prof. Dr. Marcos Tsuzuki
Item de Pedido
44
2.2.Modelo Relacional
Primeira Forma Normal (1FN)
•
PEDIDO
•
•
•
•
•
•
•
•
•
•
•
•
Número do pedido
Prazo de entrega
Cliente
Endereço
Cidade
UF
CGC
Inscrição Estadual
Valor total do pedido
Valor total do pedido
Código do vendedor
Nome do vendedor
EPUSP-PMR
•
ITEM DE PEDIDO
•
•
•
•
•
•
•
Prof. Dr. Marcos Tsuzuki
Número do pedido
Código do produto
Quantidade do produto
Descrição do produto
Valor unitário do produto
Valor total do produto
Unidade do produto
45
2.2.Modelo Relacional
Dependência Funcional
•
•
•
•
Para descrevermos as próximas formas normais, se faz necessária a
introdução do conceito de dependência funcional, sobre o qual a maior parte
da teoria de normalização foi baseada;
Dada uma entidade qualquer, dizemos que um atributo ou conjunto de
atributos A é dependente funcional de um outro atributo B contido na mesma
entidade, se a cada valor B existir nas linhas da entidade em que aparece, um
único valor de A. Em outras palavras, A depende funcionalmente de B;
Na entidade PEDIDO, o atributo PRAZO-DE-ENTREGA depende
funcionalmente de NUMERO-DO-PEDIDO;
O exame das relações existentes entre os atributos de uma entidade deve ser
feito a partir do conhecimento (conceitual) que tem sobre a realidade a ser
modelada.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
46
2.2.Modelo Relacional
Dependência Funcional Total e Parcial
•
•
•
Na ocorrência de uma chave primária concatenada, dizemos que um atributo
ou conjunto de atributos depende de forma completa ou total desta chave
primária concatenada, se e somente se, a cada valor da chave (e não parte
dela), está associado um valor para cada atributo, ou seja, um atributo não se
apresenta com dependência completa ou total quando só depende de parte da
chave primária concatenada e não dela como um todo;
Dependência total – na entidade ITEM-DO-PEDIDO, o atributo
QUANTIDADE-DO-PRODUTO depende de forma total ou completa da
chave primária concatenada (NÚMERO-DO-PEDIDO + CÓDIGO-DOPRODUTO);
A dependência total ou completa só ocorre quando a chave primária for
composta por vários atributos, ou seja, em uma entidade de chave primária
composta de um único atributo não ocorre este tipo de dependência.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
47
2.2.Modelo Relacional
Dependência Funcional Transitiva
•
•
•
Quando um atributo ou conjunto de atributos A depende de outro atributo B
que não pertence à chave primária, mas é dependente funcional desta,
dizemos que A é dependente transitivo de B.
Dependência transitiva – na entidade PEDIDO, os atributos ENDEREÇO,
CIDADE, UF, CGC e INSCRIÇÃO ESTADUAL são dependentes transitivos
do atributo CLIENTE. Nesta mesma entidade, o atributo NOME-DOVENDEDOR é dependente transitivo do atributo CÓDIGO-DOVENDEDOR;
Com base na teoria sobre as dependências funcionais entre atributos de uma
entidade, podemos continuar com a apresentação das outras formas normais.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
48
2.2.Modelo Relacional
Segunda Forma Normal (2FN)
• Devemos observar se alguma entidade possui chave primária
concatenada, e para aquelas que satisfizerem esta condição,
analisar se existe algum atributo ou conjunto de atributos com
dependência parcial em relação a algum elemento da chave
primária concatenada;
• Com a finalidade de tornar ainda mais estável o modelo de
dados, a aplicação da 2FN sobre as entidades em observação
geram novas entidades, que herdarão a chave parcial e todos os
atributos que dependem desta chave parcial, ou seja, uma
entidade para estar na 2FN não pode ter atributos com
dependência parcial em relação à chave primária.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
49
2.2.Modelo Relacional
Segunda Forma Normal (2FN)
• A entidade ITEM-DO-PEDIDO apresenta uma chave primária
concatenada e por observação, notamos que os atributos
UNIDADE-DO-PRODUTO, DESCRIÇÃO-DO-PRODUTO e
VALOR-UNITÁRIO dependem de forma parcial do atributo
CÓDIGO-DO-PRODUTO, que faz parte da chave primária.
Logo devemos aplicar a 2FN sobre esta entidade. Quando
aplicarmos a 2FN sobre ITEM-DO-PRODUTO, será criada a
entidade PRODUTO que herdará os atributos UNIDADE-DOPRODUTO, DESCRIÇÃO-DO-PRODUTO e VALORUNITÁRIO e terá como chave primária o CÓDIGO-DOPRODUTO.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
50
2.2.Modelo Relacional
Segunda Forma Normal (2FN)
Pedido
(1,n)
Possui
(1,1)
Item de Pedido
(1,1)
Participa
(0,n)
•
Um PRODUTO participa de no mínimo 0
e no máximo N elementos de ITEM-DEPEDIDO e um ITEM-DE-PEDIDO só
pode conter 1 somente 1 PRODUTO.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
Produto
51
2.2.Modelo Relacional
Terceira Forma Normal (3FN)
•
•
•
Uma entidade está na 3FN se nenhum de seus atributos possui dependência
transitiva em relação a outro atributo da entidade que não participe da chave
primária, ou seja, não exista nenhum atributo intermediário entre a chave
primária e o próprio atributo observado;
Ao retirarmos a dependência transitiva, devemos criar uma nova entidade que
contenha os atributos que dependem transitivamente de outro e a sua chave
primária é o atributo que causou esta dependência;
Além de não conter atributos com dependência transitiva, entidades na 3FN
não devem conter atributos que sejam o resultado de algum cálculo sobre
outro atributo, que de certa forma pode ser encarada como uma dependência
funcional.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
52
2.2.Modelo Relacional
Terceira Forma Normal (3FN)
•
•
Na entidade PEDIDO, podemos observar que o atributo NOME-DOVENDEDOR depende transitivamente do atributo CÓDIGO-DOVENDEDOR que não pertence à chave primária. Para eliminarmos esta
anomalia devemos criar a entidade VENDEDOR, com o atributo NOMEDO-VENDEDOR e tendo como chave primária o atributo CÓDIGO-DOVENDEDOR;
Encontramos ainda o conjunto de atributos formados por ENDEREÇO,
CIDADE, UF, CGC e INSCRIÇÃO ESTADUAL que dependem
transitivamente do atributo CLIENTE. Neste caso, devemos criar a entidade
CLIENTE que conterá os atributos ENDEREÇO, CIDADE, UF, CGC e
INSCRIÇÃO ESTADUAL. Para chave primária desta entidade vamos criar
um atributo chamado CÓDIGO-DO-CLIENTE que funcionará melhor como
chave primária do que NOME-DO-CLIENTE, deixando este último como
simples atributo da entidade CLIENTE.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
53
2.2.Modelo Relacional
Terceira Forma Normal (3FN)
(1,n)
Pedido
(1,1)
Faz
(0,n)
Cliente
EPUSP-PMR
Possui
(1,1)
(1,1)
Item de Pedido
(1,1)
Tira
(0,n)
Vendedor
Prof. Dr. Marcos Tsuzuki
Participa
(0,n)
Produto
54
2.2.Modelo Relacional
Terceira Forma Normal (3FN)
• Um PEDIDO só é feito por um e somente um CLIENTE e um
CLIENTE pode fazer de 0 até N elementos de PEDIDO. Um
PEDIDO só é tirado por um e somente um VENDEDOR e
VENDEDOR pode tirar de 0 a N elementos de PEDIDO.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
55
2.2.Modelo Relacional
Forma Normal de BOYCE/CODD (FNBC)
•
•
As definições da 2FN e 3FN, desenvolvidas por Codd, não cobriam certos
casos. Esta inadequação foi apontada por Boyce em 1974. Os casos não
cobertos pelas definições de Codd somente ocorrem quando três condições
aparecem juntas:
– A entidade tenha várias chaves candidatas;
– Estas chaves candidatas sejam concatenadas (mais de um atributo);
– As chaves concatenadas compartilham pelo menos um atributo comum;
Na verdade, a FNBC é uma extensão da 3FN, que não resolvia certas
anomalias presentes na informação contida em uma entidade. O problema foi
observado porque a 2FN e a 3FN só tratavam dos casos de dependência
parcial e transitiva de atributos fora de qualquer chave, porém quando o
atributo observado estiver contido em uma chave, ele não é captado pela
verificação da 2FN e 3FN.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
56
2.2.Modelo Relacional
Forma Normal de BOYCE/CODD (FNBC)
•
Considere a seguinte entidade FILHO:
Nome do Filho
Endereço do Filho
Data Nascimento
Nome da Escola
Número da Sala
Nome do Professor
•
Por hipótese, vamos assumir que um professor possa estar associado a mais
de uma escola e uma sala. Sob esta suposição, tanto a chave (candidata)
concatenada NOME-DA-ESCOLA + SALA-DA-ESCOLA bem como
NOME-DA-ESCOLA + NOME-DO-PROFESSOR podem ser determinantes.
Logo esta entidade atende às três condições relacionadas anteriormente.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
57
2.2.Modelo Relacional
Forma Normal de BOYCE/CODD (FNBC)
– As chaves candidatas para a entidade FILHO são: NOME-DO-FILHO +
ENDEREÇO-DO-FILHO, NOME-DO-FILHO + NÚMERO-DA-SALA e
NOME-DO-FILHO + NOME-DO-PROFESSOR;
– Todas as três chaves apresentam mais de um atributo (concatenados);
– Todas as três chaves compartilham um mesmo atributo: NOME-DO-FILHO.
• A definição da FNBC é a seguinte: uma entidade está na FNBC
se e somente se todos os determinantes forem chaves candidatas.
Notem que esta definição é em termos de chaves candidatas e
não sobre chaves primárias.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
58
2.2.Modelo Relacional
Forma Normal de BOYCE/CODD (FNBC)
•
•
Neste exemplo, NOME-DO-PROFESSOR não é completamente dependente
funcional do NÚMERO-DA-SALA, nem NÚMERO-DA-SALA é
completamente dependente funcional do NOME-DO-PROFESSOR. Neste
caso, NOME-DO-PROFESSOR é realmente completamente dependente
funcional da chave candidata concatenada NOME-FILHO + NÚMERO-DASALA ou NÚMERO-DA-SALA é completamente dependente funcional da
chave candidata concatenada NOME-DO-FILHO + NOME-DOPROFESSOR;
Ao se aplicar a FNBC, a entidade FILHO deve ser dividida em duas
entidades, uma que contém os atributos que designam um professor em uma
particular escola e número de sala.
Nome da Escola
Número da Sala
Nome do Professor
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
Nome do Filho
Endereço do Filho
Data Nascimento
Nome da Escola
Número da Sala
59
2.2.Modelo Relacional
Quarta Forma Normal (4FN)
•
•
•
Na maioria dos casos, as entidades normalizadas até a 3FN são fáceis de
entender, atualizar e de se se recuperar dados. Mas às vezes podem surgir
problemas com relação a algum atributo não chave, que recebe valores
múltiplos para um mesmo valor de chave. Esta nova dependência recebe o
nome de dependência multivalorada que existe somente se a entidade contiver
no mínimo três atributos;
Uma entidade que esteja na 3FN também está na 4FN, se ela não contiver
mais do que um fato multivalorado a respeito da entidade descrita. Esta
dependência não é o mesmo que uma associação M:N entre atributos,
geralmente descrita desta forma em algumas literaturas.
Dada a entidade hipotética a seguir:
Código do Fornecedor
1111
1111
1111
1111
EPUSP-PMR
Código da Peça
BA3
CJ10
88A
BA3
Prof. Dr. Marcos Tsuzuki
Código do Comprador
113
113
435
537
60
2.2.Modelo Relacional
Quarta Forma Normal (4FN)
•
•
Como podemos observar, esta entidade tenta conter dois fatos multivalorados:
as diversas peças compradas e os diversos compradores. Com isto apresenta
uma dependência multivalorada entre CÓDIGO-FORNECEDOR e o
CÓDIGO-PEÇA e entre CÓDIGO-FORNECEDOR e o CÓDIGOCOMPRADOR. Embora esteja na 3FN, ao conter mais de um fato
multivalor, torna a sua atualização muito difícil, bem como a possibilidade de
ocorrer problemas relativos ao espaço físico de armazenamento, causados
pela ocupação desnecessária de área de memória;
Para passarmos a entidade acima para a 4FN, é necessária a realização de
uma divisão da entidade original, em duas outras, ambas herdando a chave
CÓDIGO-FORNECEDOR e concatenado, em cada nova entidade, com os
atributos CÓDIGO-PEÇA e CÓDIGO-COMPRADOR.
Código do Fornecedor
Código da Peça
EPUSP-PMR
Código do Fornecedor
Código do Comprador
Prof. Dr. Marcos Tsuzuki
61
2.3.Operações no Modelo Relacional
Seleção
Produz uma nova relação R’ com um subconjunto
das tuplas originais
σtítulo,ano=“Star Wars” (Filmes)
selecionaria os atributos Título e Ano da relação
Filmes cujos títulos são “Star Wars”.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
62
2.3.Operações no Modelo Relacional
Operações de conjunto
R, S sendo relações, tem-se
• R ∪ S : união
• R ∩ S : intersecção
• R - S : diferença
aplicam-se sobre os elementos de R e S
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
63
2.3.Operações no Modelo Relacional
Projeção
Seleção de um ou mais atributos de uma relação
∏título, ano (Filmes)
selecionaria os atributos Título e Ano da relação
Filmes.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
64
2.3.Operações no Modelo Relacional
Produto Cartesiano
O produto R x S é o conjunto de pares formados
tomando-se o primeiro elemento do par como
sendo um elemento qualquer de R e o segundo,
um elemento de S
EPUSP-PMR
A
1
3
B
2
4
A
1
1
1
3
3
3
R.B
2
2
2
4
4
4
S.B
2
4
9
2
4
9
B
2
4
9
C
5
7
10
C
5
7
10
5
7
10
D
6
8
11
6
8
11
Prof. Dr. Marcos Tsuzuki
D
6
8
11
65
2.3.Operações no Modelo Relacional
Join
O join natural R ◊ S é o produto das duas relações,
relacionando somente as tuplas que
correspondem em um dado sentido
EPUSP-PMR
A
1
6
9
B
2
7
7
C
3
8
8
A
1
1
6
9
B
2
2
7
7
C
3
3
8
8
B
2
2
7
C
3
3
8
D
4
5
10
D
4
5
10
10
Prof. Dr. Marcos Tsuzuki
66
2.3.Operações no Modelo Relacional
Problemas 2.3
a. Para o modelo relacional do sistema de pedidos,
calcule as operações para alguma(s) das
relações.
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
67
2.3.Operações no Modelo Relacional
Produto Cartesiano
O produto R x S é o conjunto de pares formados
tomando-se o primeiro elemento do par como
sendo um elemento qualquer de R e o segundo,
um elemento de S
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
68
2.2.Modelo Relacional
Problemas 2.2
a.Converta o modelo E-R para o relacional
b.Analise o modelo relacional obtido em a. em
relação às formas normais
EPUSP-PMR
Prof. Dr. Marcos Tsuzuki
69