Modelo de Dados Professores Edson E. Scalabrin Marcos A. H. Shmeil telefone: 0xx41-330-1786 e-mail: {scalabrin, shm}@ppgia.pucpr.br download: http://www.ppgia.pucpr.br/~{scalabrin, shm} 1 Modelo de Dados Objetivo • introduzir os principais conceitos do modelo de dados relacional e dimensional 2 Modelo de Dados Sistemas Operacionais: Geralmente implementados através de banco de dados relacionais. Sistemas Analíticos: Geralmente implementados através de um banco de dados dimensional. SISTEMA OPERACIONAL PROJETO BOTTOM-UP SISTEMA ANALÍTICO PROJETO TOP-DOWN 3 Banco de Dados Modelo Relacional 4 Modelo Relacional TABELA DE PRODUTO Chave Produto Descrição Marca Categoria Preço Compra Preço Venda P00001 PALIO FIAT PASSEIO 12.000,00 18.000,00 P00002 K FORD PASSEIO 7.000,00 14.000,00 P00003 C10 FORD CARGA 18.000,00 27.000,00 TABELA DE LOJA Chave Loja Endereço Nome L001 Av. Sete de Setembro, 2032 Só Passeio Ltda. L002 Av. Visconde de Guarapuava, 4 Bom Negócio Ltda. TABELA DE VENDA Chave Venda Chave Produto Chave Loja Quantidade Nota Fiscal V00001 P00003 V00002 V00003 Data L001 1 0001 01/06/01 P00001 L002 1 0002 03/06/01 P00001 L002 1 0003 10/06/01 5 Relacionamento No modelo relacional, as tabelas mantém um relacionamento entre si. No exemplo abaixo, os registros da tabela venda se relacionam com os registros das tabelas loja e produto. venda No modelo relacional os dados do produto não precisam ser duplicados para cada registro de venda. 1 2 3 produto 4 loja X a b Y 6 Implementação Física do Relacionamento Os relacionamentos são implementados fisicamente através do relacionamento das chaves primárias de cada tabela que compõe o relacionamento. TABELA DE PRODUTO Chave Produto Descrição Marca Categoria Preço Compra Preço Venda P00001 PALIO FIAT PASSEIO 12.000,00 18.000,00 P00002 K FORD PASSEIO 7.000,00 14.000,00 P00003 C10 FORD CARGA 18.000,00 27.000,00 Chave Loja Chave TABELA DE LOJA Estrangeira Endereço Nome L001 Av. Sete de Setembro, 2032 Só Passeio Ltda. L002 Av. Visconde de Guarapuava, 4 Bom Negócio Ltda. TABELA DE VENDA Chave Venda Chave Produto Chave Loja Quantidade Nota Fiscal Data V00001 P00003 L001 1 0001 01/06/01 V00002 P00001 L002 1 0002 03/06/01 V00003 P00001 L002 1 0003 10/06/017 Formas Normais Regras desenvolvidas para: • Evitar inconsistências lógicas nas operações de atualização das tabelas. • Evitar redundância na organização das tabelas. Primeira Forma Normal Segunda Forma Normal Terceira Forma Normal Aumenta as restrições Diminui o desempenho 8 Primeira Forma Normal – 1FN Definição: o domínio de todos os atributos das tabelas deve ser atômico (indivisível) • Cada coluna da tabela deve conter só um tipo de atributos Tabela Pessoa Id-pessoa nome contato Brigadeiro Franco 233-3932 [email protected] Não Satisfaz 1FN Satisfaz Tabela Pessoa Id-pessoa nome endereço telefone email 1FN 9 Segunda Forma Normal – 2FN Definição: cada tabela deve satisfazer a 1FN, cada registro deve ter uma chave primária e cada campo não chave deve depender totalmente da chave primária. chave primária: id_pessoa, conta Não Satisfaz 2FN Id-pessoa nome endereço conta saldo agência endereço-agência Satisfaz 2FN chave primária: id_pessoa Id-pessoa nome endereço conta os campos dependem apenas de parte da chave primária, alguns apenas de conta outros apenas de id_pessoa chave primária: conta conta saldo agência endereço-agência 10 Terceira Forma Normal – 3FN Definição: cada tabela deve satisfazer a 2FN e cada atributo não chave primária depende diretamente da chave primária. Id-pessoa nome endereço conta conta saldo agência endereço-agência conta saldo agência o endereço da agência não depende da conta, mas da agência. Não Satisfaz 3FN Satisfaz 3FN agência endereço-agência 11 Integridade Devem ser observados dois tipos de integridade: • a) Integridade de Entidades (cada tabela deve ter exatamente uma chave primária) • b) Integridade Referencial (cada chave estrangeira deve ser consistente com sua chave primária correspondente) Chave primária Chave estrangeira Tabela Pessoa Id-pessoa nome endereço Id-empresa Tabela Empresa Id-empresa nome-da-empresa endereço 12 Integridade Referencial . O valor da chave estrangeira deve existir na tabela empresa ou ser NULO. Quando um registro da tabela empresa for excluído, todas os registros da tabela pessoa que façam referência a esse registro devem ter o valor da sua chave estrangeira alterado para NULO. TABELA PESSOA id-pessoa nome endereço id-empresa 1 Adão Paraíso 2 2 Eva Paraíso 2 TABELA EMPRESA id-empresa nome-da-empresa 2 Só Maçã S/A. endereço Botas Perdidas 13 Características do Modelo Relacional Reduz a redundância das informações armazenadas, diminuindo o espaço total gasto para armazenar-las. Simplifica significativamente as operações de escrita, tanto na inserção de novas informações quanto a alteração de informações existentes. Complica as operações de leitura. Quanto mais normalizado for o modelo do banco de dados operacional, mais lenta e trabalhosa será a operação de leitura. 1a FORMA NORMAL Redução no volume de dados e aumento da consistência 2a FORMA NORMAL 3a FORMA NORMAL Desempenho na leitura 14 Modelo Relacional: Conclusões Operação: ESCRITA: • Apenas um pequeno número de registros precisa ser alterado. • Por exemplo, para associar uma nova conta ao usuário os dados do usuário não precisam ser recadastrados. Operação: LEITURA: • Várias tabelas precisam ser associadas para obter a resposta. • Por exemplo, para obter o faturamento total que uma loja obteve com um dado produto, num dado período. ESCRITA LEITURA 15 Banco de Dados Modelo Dimensional 16 Modelo Dimensional Considere a seguinte afirmativa. • “Nós vendemos produtos em vários mercados, e nós medimos nosso desempenho ao longo do tempo”. O modelo de dados mais adequado para representar diversas relações entre grandezas é o modelo dimensional. TEMPO Cada ponto do cubo representa uma combinação de Produto, Mercado e Tempo armazenado. MERCADO PRODUTO 17 Modelo Dimensional: Esquema em Estrela O projeto de um banco de dados dimensional é do tipo top-down, isto é, ele é projetado a partir do tipo de análise que se quer efetuar. DIMENSÃO PRODUTO ANÁLISE DE VENDAS (TABELA DE FATOS) DIMENSÃO TEMPO Chave-tempo dia-da-semana mês quadrimestre ano flag-feriado Chave-tempo Chave-produto Chave-loja reais-faturados unidades-vendidas reais-gastos Chave-produto descrição marca categoria DIMENSÃO LOJA Chave-loja nome-da-loja endereço tipo-de-planta-da-loja 18 Modelo Dimensional: Esquema em Floco de Neve DIMENSÃO PRODUTO DIMENSÃO TEMPO Chave-tempo dia-da-semana mês quadrimestre ano flag-feriado ANÁLISE DE VENDAS (TABELA DE FATOS) Chave-tempo Chave-produto Chave-loja reais-faturados unidades-vendidas reais-gastos Chave-produto descrição marca Chave-categoria DIMENSÃO LOJA Chave-loja nome-da-loja endereço tipo_de_planta_da_loja Chave-categoria categoria 19 Modelo Dimensional: Conclusões Operações: • ESCRITA: Não pode ser utilizado, pois não guarda os registros na forma de unidades. • LEITURA: Rápida, pois a consulta é feita basicamente em uma única tabela. Características dos Bancos Analíticos: • A dimensão de tempo é definida de acordo com uma granularidade prédefinida: dia, semana, mês. Ela não reflete o instante em que as operações individuais foram efetuadas. • O projeto é top-down, isto é, a tabela central parte do objetivo final da análise. • Não contém necessariamente todos os atributos relativos aos dados, apenas os que interessam para análise. 20 • Não é adequado para efetuar transações operacionais.