Upload
others
View
13
Download
0
Embed Size (px)
Citation preview
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 1
Bancos de Dados Relacionais
Uma introdução prática
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 2
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 3
PROGRAMA
– 3.SQL Básico• 3.1 Criação de tabelas• 3.2 Queries simples• 3.3 Subqueries• 3.4 Agregação
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 4
PROGRAMA
– 4.SQL Avançado• 4.1 Indexação• 4.2 Joins• 4.3 Views• 4.4 Triggers• 4.5 Constraints
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 5
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, amodelagem do banco de dados, o fluxo deinformação e como utilizá-lo como Data WareHouse.
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 6
1.Introdução1.1 Evolução do armazenamento de dados1.2 Arquitetura de um DBMS
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 7
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 8
1.1 Introdução: Evolução do armazenamento de dados
Características de um DBMS• Usuários devem poder definir seu schema (estrutura lógicados dados), usando uma linguagem apropriada (DDL, datadefinition language)
• Usuários devem poder realizar perguntas e acessos (“query”)sem conhecimento da estrutura de armazenamento dos dados,usando uma linguagem apropriada (DML, data manipulationlanguage ou query-language)
• Usuários devem poder acessar grandes volumes de dados deforma eficiente
• Usuários devem poder acessar dados de forma independenteuns dos outros, sob controle de acesso
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 9
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 10
Stone Schwarzenegger
Basic Instinct Total Recall
1.1 Introdução: Evolução do armazenamento de dados
Evolução histórica dos DBMS
•Rede (CODASYL, fim ‘60)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 11
1.1 Introdução: Evolução do armazenamento de dados
Evolução histórica dos DBMS
•Relacional (Codd, ‘70)
Stone
Schwarzenegger
Basic Instinct
Total Recall
Total RecallStone
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 12
1.2 Introdução: Arquitetura de um DBMS
Arquitetura de um DBMS
Query Processor
Transaction Mng
Storage Manager
Schema Queries
DataMetadata
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 13
1.2 Introdução: Arquitetura de um DBMS
Storage ManagerComposto 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 14
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 15
1.2 Introdução: Arquitetura de um DBMS
Transaction Manager
• Garante as propriedades ACID• Atomicidade (Atomicity): uma transação é indivisível; ouela acontece ou não• Consistência (Consistency):Antes e após uma transação, oestado do banco de dados é consistente• Isolação (Isolation):Se duas ou mais transaçõesacontecem simultaneamente, seus efeitos devem serisolados• Durabilidade (Durability): Se uma transação se completa,seus efeitos não devem cessar mesmo que o sistema falheimediatamente após
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 16
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 17
1.2 Introdução: Arquitetura de um DBMS
Transaction Manager
• Índice• Hash• Balanced tree• Binary tree
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 18
2.Modelo Relacional2.1 Modelo E-R2.2 Modelo Relacional2.3 Operações
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 19
Ciclo de Projeto
2.1 Modelo E-R
Projeto Conceitual
Projeto Lógico
Projeto Físico
Schema conceitual
Schema lógico
Schema físico
Requisitos
E-R
SQL
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 20
Conceitos básicos• Entidade
– Representam classes do mundo real• Relacionamento
– Representam agregações entre duas ou maisentidades
– Os relacionamentos podem ser• Binários: entre duas entidades• N-ários: entre N entidades• Recursivos: de uma entidade para ela própria
2.1 Modelo E-R
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 21
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
2.1 Modelo E-R
Ordem remessa(0,1)
Fatura(1,1)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 22
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
2.1 Modelo E-R
Pessoa mora_em(1,1)
Cidade(0,n)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 23
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
2.1 Modelo E-R
Produto usa(1,n)
Materiais(0,n)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 24
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
2.1 Modelo E-R
Empregado gerencia(0,n) (0,1)
chefia
responde_a
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 25
Cardinalidade dos Relacionamentos• N-ário
2.1 Modelo E-R
Matéria aula(1,3)
Sala(0,40)
Dia
(0,n)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 26
Cardinalidade dos Relacionamentos• N-ário
2.1 Modelo E-R
Aluno aula(1,n)
Disciplina(0,n)
Professor
(0,1)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 27
Cardinalidade dos Relacionamentos• N-ário
– Separar a entidade Aluno e analisar o par Professor,Disciplina. Para cada par Professor/Disciplina podemos terde 1 até N Alunos relacionados;
– Separar a entidade Professor e analisar o par Aluno,Disciplina. Para cada par Aluno/Disciplina podemos ter 1 esomente 1 Professor relacionado;
– Separar a entidade Disciplina e analisar Professor, Aluno.Para cada par Professor/Aluno podemos ter de 1 até NDisciplinas relacionadas.
2.1 Modelo E-R
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 28
Cardinalidade dos Relacionamentos• N-ário
– Quando um aluno está matriculado em uma disciplina, estetem 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.
2.1 Modelo E-R
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 29
Decidir a Cardinalidade• Modelagem de Dados é uma Arte
– Nota Fiscal, Item de Nota Fiscal e Produto
2.1 Modelo E-R
Nota Fiscal possui(1,n)
Item de NF(1,1)
Produto
associa(1,n)
(1,1)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 30
Decidir a Cardinalidade• Modelagem de Dados é uma Arte
– Todas as notas fiscais têm, no mínimo, um item de nota fiscalrelacionado;
– 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.
2.1 Modelo E-R
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 31
Atributos• Atributos são propriedades elementares de
entidades e relacionamentos
2.1 Modelo E-R
Pessoa
Nome
RGProfissão
mora_emData_da_mudança
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 32
Identificadores (chaves)• Identificadores (chaves) são conjuntos de
atributos que podem determinar unicamenteuma entidade
2.1 Modelo E-R
Pessoa
Nome
RGProfissão
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 33
Identificadores (chaves)• Simples ou compostos• Internos ou externos
2.1 Modelo E-R
Empregado trabalha_no Departamento
ID
(1,1) (1,n)
Nota: Empregado é uma entidade fraca
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 34
Agregação• Um assassino faz vítimas. Quando ele assassina,
utiliza armas.
2.1 Modelo E-R
Assassino Assassina Vítima
Usa
Arma
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 35
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.
2.1 Modelo E-R
Assassino Faz VítimaTemCrime
Usa
Arma
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 36
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 ummédico, este fornece uma receita, que pode ter um, ou vários remédios.
2.1 Modelo E-R
Receita
Remédio
ConsultaMédico Atende Paciente
Consulta
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 37
Problemas 2.1a.Prepare um diagrama E-R para um sistema de
controle de pedidosb.Mostre dois identificadores para a entidade
DETALHE
2.1 Modelo E-R
CABEÇALHO PRODUTO
DETALHEnro_linha
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 38
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
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 39
Formas normais• O conceito de normalização foi introduzido por E.F.Codd em
1970 (primeira forma normal). Esta técnica é um processomatemático que tem seus fundamentos na teoria dos conjuntos.
• Através deste processo pode-se, gradativamente, substituir umconjunto de entidades e relacionamentos por um outro, o qual seapresenta purificado em relação às anomalias de atualização(inclusão, alteração e exclusão) as quais podem causar certosproblemas, tais como: grupos repetitivos (atributosmultivalorados) de dados, dependências parciais em relação auma chave concatenada, redundância desnecessária de dados,dificuldade na representação de fatos da realidade observada edependências transitivas entre atributos.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 40
Num. Ped. Cliente Endereço CGC IE Cod. Prod. Unid. Quant. Descrição Val. Unit. Tot. Prod. Tot. do Ped.3445 TCA R. Meira 1111111 111111 45 L 20 álcool 5,00 100,00 1799,003445 TCA R. Meira 1111111 111111 130 M 2 tecido 20,00 40,00 1799,003445 TCA R. Meira 1111111 111111 335 Kg 30 farinha 1,00 30,00 1799,003445 TCA R. Meira 1111111 111111 78 Kg 50 cimento 30,00 1500,00 1799,003445 TCA R. Meira 1111111 111111 90 L 40 cola 3,00 120,00 1799,003445 TCA R. Meira 1111111 111111 39 Kg 3 chumbo 3,00 9,00 1799,002610 Lopes R. 127 23232323 34343434 45 L 50 álcool 5,00 250,00 2650,002610 Lopes R. 127 23232323 34343434 78 Kg 47 cimento 30,00 1410,00 2650,002610 Lopes R. 127 23232323 34343434 21 Kg 20 pregos 5,00 100,00 2650,002610 Lopes R. 127 23232323 34343434 98 L 15 tinta azul 25,00 375,00 2650,002610 Lopes R. 127 23232323 34343434 90 L 15 cola 3,00 45,00 2650,002610 Lopes R. 127 23232323 34343434 43 M 10 arame 3,00 30,00 2650,002610 Lopes R. 127 23232323 34343434 25 F 10 algodão 2,00 20,00 2650,002610 Lopes R. 127 23232323 34343434 65 L 5 querosene 8,00 40,00 2650,002610 Lopes R. 127 23232323 34343434 51 M 20 fio elétrico 13,00 260,00 2650,002610 Lopes R. 127 23232323 34343434 74 M 30 linha 10 4,00 120,00 2650,00
Fomulário de Pedido
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 41
• Número do pedido• Prazo de entrega• Cliente• Endereço• Cidade• UF• CGC• Inscrição Estadual• Código do produto• Unidade do produto
• 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
Fomulário de Pedido (Atributos)
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 42
Formas normais• Caso esta entidade fosse implementada como uma
tabela em um banco de dados, as seguintes anomaliasiriam 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 deprodutos, será preciso percorrer toda a entidade para serealizar múltiplas alterações.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 43
Primeira Forma Normal (1FN)• Em uma determinada realidade, às vezes encontramos algumas informações
que se repetem (atributos multivalorados), retratando ocorrências de ummesmo 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 dedados na entidade;
• A 1FN diz que: cada ocorrência da chave primária deve corresponder a uma esomente uma informação de cada atributo, ou seja, a entidade não deve contergrupos repetitivos (multivalorados);
• Para se obter entidades na 1FN, é necessário decompor cada entidade nãonormalizada em tantas entidades quanto for o número de conjuntos deatributos repetitivos. Nas novas entidades criadas, a chave primária é aconcatenação da chave primária original mais o(s) atributo(s) do gruporepetitivo visualizado(s) como chave primária deste grupo.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 44
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 edestacados da entidade PEDIDO;
• Um PEDIDO possui no mínimo 1 e no máximo N elementos em ITEM-DE-PEDIDO e um ITEM-DE-PEDIDO pertence a 1 e somente 1 PEDIDO.
2.2.Modelo Relacional
Pedido Possui Item de Pedido(1,n) (1,1)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 45
Primeira Forma Normal (1FN)
2.2.Modelo Relacional
• 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
• ITEM DE PEDIDO• 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
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 46
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 parteda teoria de normalização foi baseada;
• Dada uma entidade qualquer, dizemos que um atributo ou conjunto deatributos A é dependente funcional de um outro atributo B contido na mesmaentidade, 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 dependefuncionalmente de NUMERO-DO-PEDIDO;
• O exame das relações existentes entre os atributos de uma entidade deve serfeito a partir do conhecimento (conceitual) que tem sobre a realidade a sermodelada.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 47
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 chaveprimária concatenada, se e somente se, a cada valor da chave (e não partedela), está associado um valor para cada atributo, ou seja, um atributo não seapresenta com dependência completa ou total quando só depende de parte dachave primária concatenada e não dela como um todo;
• Dependência total – na entidade ITEM-DO-PEDIDO, o atributoQUANTIDADE-DO-PRODUTO depende de forma total ou completa dachave primária concatenada (NÚMERO-DO-PEDIDO + CÓDIGO-DO-PRODUTO);
• A dependência total ou completa só ocorre quando a chave primária forcomposta por vários atributos, ou seja, em uma entidade de chave primáriacomposta de um único atributo não ocorre este tipo de dependência.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 48
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 transitivosdo atributo CLIENTE. Nesta mesma entidade, o atributo NOME-DO-VENDEDOR é dependente transitivo do atributo CÓDIGO-DO-VENDEDOR;
• Com base na teoria sobre as dependências funcionais entre atributos de umaentidade, podemos continuar com a apresentação das outras formas normais.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 49
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 comdependência parcial em relação a algum elemento da chaveprimária concatenada;
• Com a finalidade de tornar ainda mais estável o modelo dedados, a aplicação da 2FN sobre as entidades em observaçãogeram novas entidades, que herdarão a chave parcial e todos osatributos que dependem desta chave parcial, ou seja, umaentidade para estar na 2FN não pode ter atributos comdependência parcial em relação à chave primária.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 50
Segunda Forma Normal (2FN)• A entidade ITEM-DO-PEDIDO apresenta uma chave primária
concatenada e por observação, notamos que os atributosUNIDADE-DO-PRODUTO, DESCRIÇÃO-DO-PRODUTO eVALOR-UNITÁRIO dependem de forma parcial do atributoCÓDIGO-DO-PRODUTO, que faz parte da chave primária.Logo devemos aplicar a 2FN sobre esta entidade. Quandoaplicarmos a 2FN sobre ITEM-DO-PRODUTO, será criada aentidade PRODUTO que herdará os atributos UNIDADE-DO-PRODUTO, DESCRIÇÃO-DO-PRODUTO e VALOR-UNITÁRIO e terá como chave primária o CÓDIGO-DO-PRODUTO.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 51
Pedido Possui Item de Pedido(1,n) (1,1)
Segunda Forma Normal (2FN)
2.2.Modelo Relacional
Participa
Produto
(1,1)
(0,n)
• Um PRODUTO participa de no mínimo 0e no máximo N elementos de ITEM-DE-PEDIDO e um ITEM-DE-PEDIDO sópode conter 1 somente 1 PRODUTO.
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 52
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 chaveprimária, ou seja, não exista nenhum atributo intermediário entre a chaveprimária e o próprio atributo observado;
• Ao retirarmos a dependência transitiva, devemos criar uma nova entidade quecontenha os atributos que dependem transitivamente de outro e a sua chaveprimária é o atributo que causou esta dependência;
• Além de não conter atributos com dependência transitiva, entidades na 3FNnão devem conter atributos que sejam o resultado de algum cálculo sobreoutro atributo, que de certa forma pode ser encarada como uma dependênciafuncional.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 53
Terceira Forma Normal (3FN)• Na entidade PEDIDO, podemos observar que o atributo NOME-DO-
VENDEDOR depende transitivamente do atributo CÓDIGO-DO-VENDEDOR que não pertence à chave primária. Para eliminarmos estaanomalia devemos criar a entidade VENDEDOR, com o atributo NOME-DO-VENDEDOR e tendo como chave primária o atributo CÓDIGO-DO-VENDEDOR;
• Encontramos ainda o conjunto de atributos formados por ENDEREÇO,CIDADE, UF, CGC e INSCRIÇÃO ESTADUAL que dependemtransitivamente do atributo CLIENTE. Neste caso, devemos criar a entidadeCLIENTE que conterá os atributos ENDEREÇO, CIDADE, UF, CGC eINSCRIÇÃO ESTADUAL. Para chave primária desta entidade vamos criarum atributo chamado CÓDIGO-DO-CLIENTE que funcionará melhor comochave primária do que NOME-DO-CLIENTE, deixando este último comosimples atributo da entidade CLIENTE.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 54
Terceira Forma Normal (3FN)
2.2.Modelo Relacional
(1,n) (1,1)
Participa
Produto
(1,1)
(0,n)
TiraFaz
Cliente Vendedor
Pedido Possui Item de Pedido
(0,n) (0,n)
(1,1)(1,1)
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 55
Terceira Forma Normal (3FN)
• Um PEDIDO só é feito por um e somente um CLIENTE e umCLIENTE pode fazer de 0 até N elementos de PEDIDO. UmPEDIDO só é tirado por um e somente um VENDEDOR eVENDEDOR pode tirar de 0 a N elementos de PEDIDO.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 56
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ãocobertos pelas definições de Codd somente ocorrem quando três condiçõesaparecem 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 certasanomalias presentes na informação contida em uma entidade. O problema foiobservado porque a 2FN e a 3FN só tratavam dos casos de dependênciaparcial e transitiva de atributos fora de qualquer chave, porém quando oatributo observado estiver contido em uma chave, ele não é captado pelaverificação da 2FN e 3FN.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 57
Forma Normal de BOYCE/CODD (FNBC)• Considere a seguinte entidade FILHO:
• Por hipótese, vamos assumir que um professor possa estar associado a maisde uma escola e uma sala. Sob esta suposição, tanto a chave (candidata)concatenada NOME-DA-ESCOLA + SALA-DA-ESCOLA bem comoNOME-DA-ESCOLA + NOME-DO-PROFESSOR podem ser determinantes.Logo esta entidade atende às três condições relacionadas anteriormente.
2.2.Modelo Relacional
Nome do FilhoEndereço do FilhoData NascimentoNome da EscolaNúmero da SalaNome do Professor
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 58
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 eNOME-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 FNBCse e somente se todos os determinantes forem chaves candidatas.Notem que esta definição é em termos de chaves candidatas enão sobre chaves primárias.
2.2.Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 59
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. Nestecaso, NOME-DO-PROFESSOR é realmente completamente dependentefuncional da chave candidata concatenada NOME-FILHO + NÚMERO-DA-SALA ou NÚMERO-DA-SALA é completamente dependente funcional dachave candidata concatenada NOME-DO-FILHO + NOME-DO-PROFESSOR;
• Ao se aplicar a FNBC, a entidade FILHO deve ser dividida em duasentidades, uma que contém os atributos que designam um professor em umaparticular escola e número de sala.
2.2.Modelo Relacional
Nome do FilhoEndereço do FilhoData NascimentoNome da EscolaNúmero da Sala
Nome da EscolaNúmero da SalaNome do Professor
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 60
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 surgirproblemas com relação a algum atributo não chave, que recebe valoresmúltiplos para um mesmo valor de chave. Esta nova dependência recebe onome de dependência multivalorada que existe somente se a entidade contiverno mínimo três atributos;
• Uma entidade que esteja na 3FN também está na 4FN, se ela não contivermais do que um fato multivalorado a respeito da entidade descrita. Estadependê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:
2.2.Modelo Relacional
Código do Fornecedor Código da Peça Código do Comprador1111 BA3 1131111 CJ10 1131111 88A 4351111 BA3 537
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 61
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 apresentauma dependência multivalorada entre CÓDIGO-FORNECEDOR e oCÓDIGO-PEÇA e entre CÓDIGO-FORNECEDOR e o CÓDIGO-COMPRADOR. Embora esteja na 3FN, ao conter mais de um fatomultivalor, torna a sua atualização muito difícil, bem como a possibilidade deocorrer problemas relativos ao espaço físico de armazenamento, causadospela ocupação desnecessária de área de memória;
• Para passarmos a entidade acima para a 4FN, é necessária a realização deuma divisão da entidade original, em duas outras, ambas herdando a chaveCÓDIGO-FORNECEDOR e concatenado, em cada nova entidade, com osatributos CÓDIGO-PEÇA e CÓDIGO-COMPRADOR.
2.2.Modelo Relacional
Código do FornecedorCódigo da Peça
Código do FornecedorCódigo do Comprador
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 62
Seleção
Produz uma nova relação R’ com um subconjuntodas tuplas originais
σtítulo,ano=“Star Wars” (Filmes)
selecionaria os atributos Título e Ano da relaçãoFilmes cujos títulos são “Star Wars”.
2.3.Operações no Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 63
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
2.3.Operações no Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 64
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çãoFilmes.
2.3.Operações no Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 65
Produto CartesianoO produto R x S é o conjunto de pares formados
tomando-se o primeiro elemento do par comosendo um elemento qualquer de R e o segundo,um elemento de S
2.3.Operações no Modelo Relacional
A B B C D1 2 2 5 63 4 4 7 8
9 10 11
A R.B S.B C D1 2 2 5 61 2 4 7 81 2 9 10 113 4 2 5 63 4 4 7 83 4 9 10 11
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 66
JoinO join natural R ◊ S é o produto das duas relações,
relacionando somente as tuplas quecorrespondem em um dado sentido
2.3.Operações no Modelo Relacional
A B C B C D1 2 3 2 3 46 7 8 2 3 59 7 8 7 8 10
A B C D1 2 3 41 2 3 56 7 8 109 7 8 10
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 67
Problemas 2.3
a. Para o modelo relacional do sistema de pedidos,calcule as operações para alguma(s) dasrelações.
2.3.Operações no Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 68
Produto CartesianoO produto R x S é o conjunto de pares formados
tomando-se o primeiro elemento do par comosendo um elemento qualquer de R e o segundo,um elemento de S
2.3.Operações no Modelo Relacional
EPUSP-PMR Prof. Dr. Marcos Tsuzuki 69
Problemas 2.2
a.Converta o modelo E-R para o relacionalb.Analise o modelo relacional obtido em a. em
relação às formas normais
2.2.Modelo Relacional