Banco de Dados Flashcards
Banco de Dados
Representa uma coleção de dados com significado implícito.
É um conjunto de dados estruturados que são confiáveis , coerentes e compartilhados por usuários que tem necessidades de informações diferentes.
Sistema de Gerência de Banco de Dados (SGBD)
Software que incorpora as funções de definição, recuperação e alteração de dados em um banco de dados.
Catálogo (Dicionário de Dados) do SGBD
*Meta Dados é a informação armazenada no catálogo do SGBD
Independência de dados
Independência Lógica
Independência Física
Características da abordagem de SGBD
Independência de dados *Independência Lógica Independência *Física Abstração de dados Suporte a Múltiplas Visões dos Dados Controle de acesso concorrente
Arquitetura ANSI/SPARC Arquitetura de três esquemas
Nível Interno (físico, de armazenamento):
–O nível mais baixo de abstração.
–Descreve como os dados estão realmente armazenados -
**NÃO LIDA com registros físicos RELACIONADO A MELHORAR DESEMPENHO»_space; Age é o DBA
Nível Conceitual (lógico de comunidade):
–Nível intermediário de abstração
–Descreve a estrutura do BD completo sob o ponto de vista do usuário. Descrições de entidades, tipos de dados, restrições atributos, relacionamentos, operações e restrições; Age é o AD
Nível Externo (de visões, lógico do usuário): DATE : também é conhecido com o nível lógico do usuário.
–O mais alto nível de abstração
–Descreve a visão da base de dados de um grupo de usuários. Cada visão descreve cada parte da base de dados que um grupo está interessado e esconde o resto da base. Age é o USUÁRIO FINAL
Mapeamento Externo/Conceitual
Mapeamento Conceitual/Interno
•Mapeamento Externo/Conceitual
–Define a correspondência entre uma visão externa específica e a visão conceitual.
–Exemplo: os campos podem ter tipos de dados diferentes, as denominações de campo e registro podem ser modificadas
—-> 1 mapeamento conceitual/interno 1 OU MAIS mapeamentos externos/conceituais.
•Mapeamento Conceitual/Interno
–Define a correspondência entre a visão conceitual e o banco de dados armazenado;
–Especifica o modo como os registros e campos conceituais são representados no nível interno.
Independência de dados
–Habilidade de modificar a definição de um esquema em um nível sem
Independência física de dados
–Modificar o esquema físico sem a necessidade de reescrever os programas aplicativos
–Mudanças de esquema interno não afetam o esquema conceitual ou a esquemas externos.
–Modificações no nível físico são ocasionalmente necessárias para melhorar o desempenho;
Independência lógica de dados
–Modificar o esquema conceitual sem a necessidade de reescrever os programas aplicativos.
MAS SE PRECISAR PODE ALTERAR
–Modificações no nível conceitual são necessárias quando a estrutura lógica do banco de dados é alterada
–Exemplo: adição de um novo tipo de conta „contas de bolsas de mercado‟ num sistema bancário.
Os mapeamentos são os grandes responsáveis por proporcionar a Independência de Dados
Independência lógica dos dados é MAIS difícil de ser alcançada do que a independência física
Modelo de Dados
–Coleção de conceitos que são usados para descrever a estrutura de um banco de dados;
–Descrição formal da estrutura de um banco de dados.
Esquema
–Descrição de um BD, segundo um modelo de dados.
As descrições das construções e restrições do esquema ficam armazenadas no catálogo do banco de dados.
NAVATHE
Geralmente, o esquema do banco de dados não é alterado com frequência.
Instância
É o conjunto de dados armazenados no BD em um determinado instante
Modelo Conceitual ou Semântico (Alto Nível)
–Descrevem a realidade observada
–Preocupação com a semântica da aplicação
–Independente de SGBD e de modelo de dados!!
NÃO TRATA DE TECNOLOGIA
–exemplos:
•modelo entidade-relacionamento (MER)
•Diagrama de classes UML
Modelo Lógico ou de Implementação (Nível Intermediário)
–Representam os dados em alguma estrutura (lógica) de armazenamento de dados
–Dependente do tipo/modelo de dados particular do SGBD que será usado
–Independente do SGBD
–Exemplos:
•modelo relacional (tabelas)
•modelos hierárquico e XML(árvore)
•modelo em redes (grafo)
•modelo orientado a objetos (classes–objetos complexos)
DESCREVE TIPOS DE DADOS E COMO SE RELACIONAM
TRATA DA TECNOLOGIA
** REPRESENTA ESTRUTURA DE DADOS VISTA PELO USUÁRIO
Modelo Físico (Baixo Nível)
MAIOR DETALHES
DESCRIÇÃO DE ARMAZENAMENTO
FORMATOS DE REGISTRO
CAMINHO DE ACESSO
–Representação gráfica do detalhamento físico das estruturas dos dados que o banco dedados irá armazenar.
–Este modelo é dependente do SGBD em que será implementado.
Usuários de Banco de Dados - Desenvolvedor de Aplicações
–Responsáveis pela concepção e construção de sistemas de informações que utilizam bases de dados implementadas em SGBDs.
–Responsáveis pela elaboração do Projeto Conceitual e Lógico da base de dados utilizada pela Aplicação.
FAZ REQUISIÇÃO AO BANCO
CRUD
Usuários de Banco de Dados - Projetista de BD ou Administrador de Dados (AD)
–Responsáveis por auxiliar os Analistas na criação do projeto lógico das bases de dados.
–Mantenedor do Projeto de Banco Corporativo
–Responsável pelos Dados da Organização
CRIA MODELO CONCEITUAL
Usuários de Banco de Dados - Administrador de Banco de Dados (DBA)
–Responsáveis por Administrar o SGBD Principais atividades: –Instalação e Configuração do SGBD –Monitoração do SGBD –Ajuste do SGBD (Tunning) PERFROMANCE –Controle de Segurança –Parte do Projeto Físico (organização interna dos dados, estruturas de acesso –índices) –Backup e Recuperação
Usuários Finais
–Utilizam o SGBD de diferentes formas a depender do conhecimento adquirido sobre SGBDs.
–Indiretamente através de aplicações
–Diretamente através de comandos ou operações válidas para os SGBDs..
Usuários Casuais
Usuários Iniciantes ou Paramétricos
Usuários Sofisticados
Usuários Isolados
Sistema gerenciador de banco de dados (SGBD)
É uma coleção de programas que permite aos usuários criar e manter um banco dedados.
O SGBD é considerado um software de uso geral que facilita o processo de definição, construção, manipulação e compartilhamento do banco dedados entre diversos usuários e aplicações.
Sistema de BD = BD + SGBD
Catálogo ou Dicionário de dados
Repositório com a definição completa da estrutura do banco deda dos e suas restrições.
O catálogo pode ser considerado um banco de dados isolado (banco de dados do sistema).
Nem sempre o uso do SGBD é recomendado.
Aplicações com requisitos rigorosos de tempo real.
Sistemas embarcados com capacidade de armazenamento limitado.
Nenhum acesso de múltiplos usuários aos dados
Algumas informações mantidas no Catálogo
Estrutura detalhada de cada arquivo Definição dos esquema(externos, conceitual e interno) Informações dos mapeamentos Restrições de segurança e integridade Autorização de acesso Visões Estimativas
Catálogo
Armazena esquemas internos, conceituais e externos, mapeamentos e metadados.
DICIONÁRIO DE DADOS
DBA» Instruções DDL > Comandos privilegiados
Viabilizam a definição e os ajustes no banco de dados por parte do DBA.
COMPILADOR DDL
Armazena as definições dos esquemas no Catálogo do Sistema.
Módulos Componentes de um SGBD
USUÁRIOS CASUAIS
CONSULTA INTERATIVA
Formulação das consultas.
COMPILADOR DE CONSULTA
Analisa e validação da sintaxe da consulta, compilando essas consultas para um formato interno.
OTIMIZADOR DE CONSULTA
Entre outras coisas,o Otimizador de Consultas preocupa-se como rearranjo e a possível reordenação de operações, com a eliminação de redundâncias e o uso dos algoritmos e índices corretos durante a execução.
PROGRAMADORES DE APLICAÇÃO
Entre outras coisas, o Otimizador d Consultas preocupa-se com o rearranjo e a possível reordenação de operações, com a eliminação de redundâncias e o uso dos algoritmos e índices corretos durante a execução.
A saída do Compilador DML e do Compilador de Linguagem Hospedeira são linkados e formam as Transações Compiladas
COMPILADOR DML
Compila esses comandos em código objeto para o acesso ao banco de dados.
PROJETO DE BD
LEVANTAMENTO E ANÁLISE DE REQUISITO
Etapa onde os Administradores de Dados entrevistam os usuários de forma a identificar os requisitos de dados.
PROJETO CONCEITUAL
Captura as necessidades da organização
PROJETO LÓGICO
Banco de dados é enriquecido com detalhes referentes às estruturas de armazenamento, organização de arquivos, índices…
ENTIDADE
É o objeto básico que o modelo ER representa.
É algo do mundo real que pode ser identificada de forma unívoca
ATRIBUTO
propriedadesespecíficasquedescrevemasentidades
Atributo Simples X Atributo Composto
Os atributos simples não são divisíveis. Eles também podem ser denominados atributos atômicos
atributo composto pode ser subdividido em partes menores
Atributo Multivalorado
São atributos que admitem um conjunto de valores para uma mesma entidade
Atributo Armazenado X Atributo Derivado
O atributo que pode ser derivado é denominado atributo derivado. O outro atributo é determinado atributo armazenado.
Modelo Entidade-Relacionamento (ER)
inicialmentepropostoporPeterCheneseuobjetivoérepresentaromundorealatravésdasentidades,suascaracterísticaseorelacionamentoentreessasentidades.
Diagrama de entidade-relacionamento (DER)
notação diagramática associada ao modelo entidade-relacionamento
Atributos chave / Atributo identificador
Atributo chave é o conjunto de atributos cujos valores podem ser usados para identificar cada entidade de maneira exclusiva
Tipo de relacionamentos e Conjunto de relacionamentos
Um tipo de relacionamento define um conjunto de relacionamentos entre entidades
Grau de um tipo relacionamento
O grau de um tipo de relacionamento é indicado pelo número dos tipos de entidades participantes
Papéis no relacionamento
O papel especifica a função que uma entidade desempenha em cada instância de relacionamento.
é essencial quando um mesmo tipo de entidade aparece mais de uma vez num mesmo relacionamento
Relacionamento Recursivo
umaentidadeparticipamaisdeumavezemumtipoderelacionamentocomfunçõesdiferentes.
Razão de Cardinalidade
A razão de cardinalidade especifica o número máximo de instâncias de relacionamento Razões de cardinalidade possíveis: 1X1 1XN NXM
Restrição de Participação
existência de uma entidade depende dela estar relacionada a outra entidade por meio do tipo de relacionamento > CARDINALIDADE MÍNIMA
Atributos de tipos de relacionamentos
Assim como as entidades, os relacionamentos também podem ter atributos
Atributos de tipos de relacionamentos
No caso dos relacionamentos 1:1, como ambas as entidades envolvidas participam de apenas uma instância de relacionamento
No caso dos relacionamentos N:1, o atributo do relacionamento pode ser representado no relacionamento ou na entidade do lado N
Entidade Fraca
Tipos de entidade que não possuem atributos chave próprios
Esse relacionamento entre entidades fortes e entidades fracas é chamado de relacionamento de identificação da entidade fraca.
**empretem uma restrição de participação total
Subclasses, Superclasses e Herança
Herança é um mecanismo que consiste na herança por parte de uma subclasse de todos os atributos e relacionamentos da sua superclasse correspondente.
Especialização e Generalização
Especializaçãoéoprocessodedefinirumconjuntodesubclassesdeumtipodeentidade
Generalizaçãoéoprocessoquesuprimiasdiferençasentreváriostiposdeentidades,identificandoassuascaracterísticascomuns.
Agregação / Entidade Associativa
Agregação é a abstração por meio da qual os relacionamentos são tratados como entidades de nível superior
Modelo Relacional
Primeiro modelo de banco de dados formal. Ele foi introduzido por Ted Codd, da IBM.
Baseado na teoria de conjunto e lógica de predicados de primeira ordem.
Coleção de relações.
Atributo
Todososnomesdosatributosdeumarelaçãoprecisamserdistintos
Otipodedadoquedescreveostiposdevaloresquepodemapareceremcadacolunaérepresentadoporumdomíniodevalorespossíveis
Tupla
Uma tupla representa um fato que normalmente corresponde a uma entidade ou relacionamento do mundo real.
Cada n tuplas t é uma lista ordenada de n valores, em que cada valor é um elemento de dom (Ai), ou é um valore special NULL
Esquema de Relação
Um esquema de relação é usado para descrever uma relação
O grau (ou aridade) de uma relação é definido pela quantidade de atributos desse esquema de relação
As tuplas em uma relação não possuem nenhuma ordem em particular.
Cada valor em uma tupla é um valor atômico ou um valor NULL.
estado de uma relação é definido pelas suas tuplas
Restrições de integridade (constraints)
fornecem a garantia de que mudanças feitas no banco de dados por usuários autorizados não resultem em perda de consistência de dados.
Superchave
subconjunto de atributos cujos valores não se repetem em nenhuma tupla da relação
CHAVE
A chave é uma super chave com a propriedade adicional de que a remoção de qualquer atributo faz com que ela deixe de ser uma superchave (superchave mínima).
Unicidade + Irredutibilidade
Chave Candidata
Quando um esquema tem mais de uma chave, cada uma dessas chaves é denominada chave candidata
Chave Primária (PrimaryKey)
Chave primária é a chave candidata selecionada para identificar tuplas na relação.
As demais chaves candidatas que não foram selecionadas são denominadas chaves secundárias
Chave Estrangeira (ForeignKey)
Mecanismo que possibilita a implementação de relacionamentos em um banco de dados relacional.
Conjunto de atributos usado para manter a consistência entre tuplas de duas relações.
Restrição de Integridade de Domínio
A restrição de domínio delimita um conjunto com os valores que podem ser atribuídos a um atributo.
Restrição de Integridade de Chave
A chave tem como característica identificar uma tupla dentro de uma relação.
A restrição d integridade de chave indica que o valor das chaves candidatas devem ser únicos.
Restrição de Integridade de Entidade
A restrição de integridade de entidade indica que nenhum valor de chave primária pode ser NULL
Restrição de Integridade de Unicidade
Especifica que o valor de um determinado atributo deve ser único.
Restrição de Integridade de Vazio
Especifica se os campos de uma coluna podem ou não ser vazios.
Restrição de Integridade Referencial
especificada entre duas relações e usada para manter a consistência entre tuplas nas duas relações.
O domínio da chave estrangeira deve ser o mesmo da chave referenciada na outra tabela.
O valor da chave estrangeira deve ter uma correspondência como atributo referenciado na outra relação ou ter valor NULL.
Primeira Forma Normal (1FN)
Uma relação está na Primeira Forma Normal (1FN) se contém apenas atributos atômicos
- É parte da definição formal de uma relação.
- Foi definida para não permitir atributos multivalorados, atributos compostos, tabelas aninhadas e suas combinações.
2FN
Uma relação encontra-se na 2FN se e somente se estiver em 1FN e não contém dependências parciais.
*Dependência Parcial
◦quando uma coluna depende apenas de uma parte de uma chave primária composta.
3FN
Uma relação está em 3FN se e somente se estiver em 2FN e nenhum atributo não chave for dependente transitivo da chave primária.
*Dependência Transitiva:
◦Uma coluna, além de depender da chave primária, depende de outra coluna ou conjunto de colunas.
◦X → Y e Y → Z
*A relação não deve ter um atributo não-chave funcionalmente determinado por um outro atributo não-chave (ou por um conjunto de atributos não-chave).
FNBC
Uma relação está em FNBC somente se estiver em 3FN e para toda dependência funcional X → A, X é uma superchave.”
- É uma forma mais restritiva de 3FN
- Toda relação em FNBC está também em 3FN; entretanto, uma relação em 3FN não está necessariamente em FNBC.
4FN
Uma relação está em 4ª Forma Normal (4FN) se, e somente se, estiver na 3FN e não contiver dependências multivaloradas
5FN
Existem relações que não podem ser decompostas em duas projeções sem perda, mas podem ser decompostas em três ou mais. Estas relações podem ser descritas como “decomponível n” (n>2) (Date), significando que a relação em questão pode ser decomposta sem perda em n projeções, mas não em m projeções, m < n.
*Esta limitação é denominada dependência de junção (DJ).
Big Data (megadados ou grandes dados)
área do conhecimento que estuda como tratar, analisar e obter informações a partir de conjuntos de dados grandes demais para serem analisados por sistemas tradicionais.
*São dados multivariados e de elevada dimensão, geralmente criados em tempo real e apresentam um crescimento exponencial (na escala temporal), nomeados de megadados
5 V
Volume: relacionado à grande quantidade de dados gerados;
Variedade: as fontes de dados são muito variadas, o que aumenta a complexidade das análises;
Velocidade: Devido ao grande volume e variedade de dados, todo o processamento deve ser ágil para gerar as informações necessárias;
Veracidade: A veracidade está ligada diretamente ao quanto uma informação é verdadeira;
Valor: Este conceito está relacionado com o valor obtido desses dados, ou seja, com a “informação útil”.
Big Data Analytics ESTUDADOS PELO BIG DATA
Social Data: Dados coletados de redes sociais ou ambientes de interação entre usuários, geralmente demográficos e comportamentais, ou seja, ditam um padrão de um determinado grupo com as mesmas característica
Enterprise Data: Na tradução literal Dados Empresariais, coletados pelo RH de empresas, setores de vendas, finanças, logística e produção, esses dados são atributos sobre funcionários e setores diferentes dentro de um ambiente empresarial
Personal Data: Dados pessoais, facilmente relacionados ao conceito da Internet das coisas, são dados obtidos através de aparelhos de uso pessoal ou coletivo, tais como smartphones, geladeiras, televisões, carros, etc.
Hadoop E Hadoop Distributed File System (HDFS)
plataforma de software em Java de computação distribuída voltada para clusters e processamento de grandes volumes de dados, com atenção a tolerância a falhas.
Hadoop Distributed File System (HDFS) - Sistema de arquivos distribuído que armazena dados em máquinas dentro do cluster, sob demanda, permitindo uma largura de banda muito grande em todo o cluster.
Apache hadoop é composto de 3 módulos principais:
1. Hadoop Distributed File System (HDFS).
2. Hadoop Yarn
3. Hadoop MapReduce
MapReduce
Modelo de programação criado pelo Google para trabalhar com grande quantidade de dados.
Consiste de duas fases: Mapeamento e Redução
MapReduce é um modelo de programação, e framework introduzido pelo Google para suportar computações paralelas em grandes coleções de dados em clusters de computadores.
Machine
Learning
ANÁLISE: DESCRITIVA : O QUE ACONTECEU? DIAGNÓSTICO : PORQUE ISSO ACONTECEU? PREDITIVA : O QUE ACONTECERÁ? PRESCRITIVA : O QUE DEVE SER FEITO?
DATA LAKE
O data lake é um tipo de repositório que armazena conjuntos grandes e variados de dados brutos em formato nativo. Com os data lakes, você tem uma visão não refinada dos dados
os dados são transformados apenas quando são necessários para análises, por meio da aplicação de esquemas
BI
“Um conjunto de conceitos, métodos e recursos
tecnológicos que habilitam a obtenção e distribuição de
informações geradas a partir de dados operacionais,
históricos e externos, visando proporcionar subsídios para a tomada de decisões gerenciais e estratégicas.”
• Gartner Group, 1989
Objetivos fundamentais de BI/DW (Kimball)
– Fazer a informação acessível mais facilmente
– Apresentar a informação consistente (credibilidade)
– Adaptado a mudanças
– Apresentar a informação de forma temporal
– Um bastião de segurança que protege os ativos de
informação
– Servir como base de autoridade e de confiança para uma
melhor tomada de decisão
– Ser aceito pela comunidade organizacional para considerar
bem-sucedido
Business Intelligence (BI) - Efrain Turban
é um termo abrangente que combina arquiteturas, ferramentas, bancos de dados, ferramentas de análise, aplicações e metodologias
Objetivos fundamentais de BI
• Acesso a dados confiáveis
• Aumento da transparência e compreensão do
negócio
• Suporte para a tomada de decisão
BI - Carlos Barbieri
BI representa a habilidade de se estruturar, acessar e explorar informações, normalmente guardadas em um DW/DM (Data Warehouse/Data Mart),
BUSINESS INTELLIGENCE
Conjunto de tecnologias que dão suporte as decisões gerenciais por meio de informações internas e externas às organizações.
Essas tecnologias tem um profundo impacto na
estratégia corporativa, na performance e na
competitividade.
Data Warehouse
Inmon: É uma coleção de dados orientados por
assunto, integrados, variáveis com o tempo e não
voláteis, para dar suporte ao processo de tomada de
decisão
• Kimball: É um conjunto de ferramentas e técnicas de
projeto, que quando aplicadas às necessidades
específicas dos usuários e aos bancos de dados
específicos permitirá que planejem e construam um
Data Warehouse
Hadoop
implementação de código aberto do paradigma de programação Map-Reduce
Map-Reduce
paradigma de programação introduzido pelo Google para processar e analisar grandes conjuntos de dados
Componentes do Apache Hadoop
- *HDFS (Hadoop Distributed File System), que manipula o armazenamento de dados entre todas as máquinas na qual o cluster do Hadoop está sendo executado
- *Map-Reduce, manipula a parte do processamento do framework
HDFS (Hadoop Distributed File System)
HDFS é um sistema de arquivos escalonável e distribuído, cujo desenho é baseado fortemente no GFS (Google File System), que também é um sistema de arquivo distribuído
HDFS
HDFS possuem dois tipos de nós
1- namenode, que é um master,
2- múltiplos datanodes, que são nós slave
Namenode
administra o namespace do sistema de arquivos. Ele gerencia todos os arquivos e diretórios. Namenodes possuem o mapeamento entre arquivos e os blocos nos quais estes estão armazenados. Todos os arquivos são acessados usando esses namenodes e datanodes
Datanode
armazena os dados em forma de blocos. Datanodes se reportam a namenodes sobre os arquivos que possuem armazenados para que o namenode esteja ciente e os dados possam ser processados
** crucial de falha do sistema, sem o qual os dados não podem ser acessado
Namenodes secundários
esse node é responsável por checar a informação do namenode. No caso de falha, podemos usar esse nó para reiniciar o sistema.
Map-Reduce
paradigma de programação em que cada tarefa é especificada em termos de funções de mapeamento e redução. Ambas as tarefas rodam paralelamente no cluster.
Job Tracker
tarefas de Map-Reduce são submetidas ao Job Tracker. Ele precisa falar com o Namenode para conseguir os dados. O Job Tracker submete a tarefa para os nós task trackers > que reportam e intervalos pra dizerem que estão vivos
Task Tracker
ask Tracker aceita as tarefas to Job Tracker. Essas tarefas são tanto de map, reduce ou ambas (shuffle)
BI x BIG DATA ANALYTICS
O Big Data não necessariamente entregará dados concretos com análises claras, por isso podemos fazer o uso do BI para que ele nos proporcione o entendimento, com saídas lógicas e estratégicas. Deste modo, como ambas possuem grande importância, elas devem ser bem entendidas para que as empresas possam aproveitá-las da melhor forma.
Data Warehouse
Não-volátil, integrado, orientado por assuntos e variante no tempo
DW é orientado por assuntos, armazenando dados de forma histórica de assuntos específicos, conforme interesses de nível estratégico da empresa
TIPICAMENTE MULTIDIMENSIONAIS
Data Marts
são como pequenas fatias que armazenam subconjuntos de dados, normalmente organizados para um departamento ou um processo de negócio. Normalmente o Data Mart é direcionado para uma linha de negócios ou equipe, sendo que a sua informação costuma pertencer a um único departamento
OLAP
software cuja tecnologia de construção permite aos analistas de negócios, gerentes e executivos analisar e visualizar dados corporativos de forma rápida, consistente e principalmente interativa. ESTRATÉGICO
- Tipo ou variação de um datawarehouse
- organizar os dados em uma estrutura comparada a um cubo
- possibilitar o suporte a decisões gerenciais em Business Intelligence (BI).
- permitir a navegação entre as dimensões do OLAP em uma hierarquia de níveis.
- suportar implementação em uma estrutura do tipo cliente-servidor.
- *muitos registros, por arrays e por assunto
Business Intelligence (BI)
combina análise empresarial, mineração de dados, visualização de dados, ferramentas/infraestrutura de dados e práticas recomendadas para ajudar as organizações a tomar decisões impulsionadas por dados.
Metadados DW
Camada de diretório de dados da arquitetura do datawarehouse
ETL
filtragem, integração, conversão, condensação e derivação dos dados de entrada, que podem ser
originários de diversas fontes, inclusive externas aos sistemas OLTP da organização.
Componentes do ETL
– Extração
Subsistema de extração, de perfil dos dados, e captura das alterações
– Limpeza e conformidade
limpeza de dados, eventos de erro, auditoria, deduplicação e conformidade
– Entrega
alteração de dimensão, chave substituta, hierarquia, Date/Time Dimensions, Junk(lixo), tabela fato, construção OLAP
– Gestão
Ambiente ETL
Confiabilidade - fornecer dados em tempo hábil, confiáveis e em qualquer nível de detalhe Disponibilidade - acordos de nível de serviço (SLAs) Gerenciamento - ele cresce e muda
Modelo multidimensional
Modelagem conceitual de negócios, que facilita a investigação, o resumo e a organização de dados para a análise de negócios
**modelo multidimensional relaciona tabelas de fatos com tabelas de dimensões em um banco de dados do tipo OLAP
OLAP (Online Analytical Processing)
capacidade para manipular e analisar um grande volume de dados sob múltiplas perspectivas
Drill-down (desmembramento)
PARA BAIXO
níveis crescentes de detalhes são revelados (ano, mês, dia), Um maior nível de detalhamento.
(-) Granularidade
(+) aumenta o nível de detalhe da informação
Roll-up (agregação) ou Drill-up
os dados são resumidos com generalização crescente (dia, mês, ano)
(+) Granularidade
(-) detalhamento da informação
OLTP
TRANSACIONAL
RELATÓRIO ROTINEIRO + FOCADO
+ RÁPIDO PRA PROCESSAR - CONSULTA PRÉ DEFINIDA
OLAP
ANALÍTICO
INFORMAÇÃO PRO GESTOR
DW, DATA MART
RELATÓRIO AD HOC»_space;> consulta é criada apenas para satisfazer aquela necessidade específica, aquele propósito, em um momento específico
CONSULTA ABRANGENTE
PROCESSA MAIS LENTO PORÉM MAIOR EFICIENTE PRA APRESENTAR DADOS
Surrogate Key
**chave artificial e auto incremental
gerada automaticamente na hora da carga, quando você carrega a dimensão no ETL – não é reutilizável
**Na fato, essa Surrogate Key vai ser uma Foreign Key, a chave que serve para relacionar os dados entre duas tabelas, sempre apontando para uma Primary Key em outra tabela, que no caso da dimensão, vai ser a Surrogate Key.
*tem as características de uma Primary Key.
*é utilizada para referenciar a dimensão na fato
*é auto incremental
*é uma chave artificial
*é criada no Data Warehouse
*não pode se repetir
Modelagem dimensional
técnica de projeto lógico normalmente usada para data warehouses que contrasta com a modelagem entidade-relacionamento
Tipos de modelagem
Modelo Estrela: Mais simples de entender, nesse modelo todas as dimensões relacionam-se diretamente com a fato.
Modelo Floco de Neve: Visa normalizar o banco, esse modelo fica mais complicado do analista entender, nele temos dimensões auxiliares.
Tabela Fatos
Uma tabela Fato armazena as métricas relacionadas a determinado evento, por exemplo, uma fato de Vendas pode armazenar quantidade de itens vendidos, valor dos itens vendidos, entre outras métricas
Granularidade
granularidade diz respeito ao nível de detalhamento
***maior a granularidade, menor o nível de detalhamento e quanto menor a granularidade, maior o nível de detalhamento.
Business Intelligence (BI)
é baseado na transformação dos dados em informação, em seguida, informações em decisões e, finalmente, em ações
OLTP – Online Transactional Processing
baseiam-se em transações
- Sistemas Contábeis;
- Aplicações de Cadastro;
- Sistemas de Compra, Estoque, Inventário;
- ERPs, CRMs.
Atualizações - Mais freqüentes
Precisão Dados atuais
Linhas e Colunas
OLAP – Online Analytical Processing
subsídio para tomadas de decisão, a partir de análises realizadas sobre bases de dados históricas, por vezes com milhões de registros a serem totalizados
Atualizações - Menos freqüentes
Precisão - Dados históricos
Dimensões, Medidas e Fatos
Decision Support Systems (DSS), ou Sistemas de Apoio a Decisão
são baseados em relatórios analíticos, normalmente utilizados por usuários de nível operacional;
Management Information Systems (MIS), ou Sistemas de Informações Gerenciais
permitem análises mais profundas, com a realização de simulações de cenários - negócio no nível tático
Executive Information Systems (EIS), ou Sistemas de Informações Executivas
voltados para profissionais que atuam no nível estratégico das empresas, como diretores e presidência
KMS
sistema de gerenciamento de informações. Seu propósito é permitir que os usuários colaborem em criar, capturar, guardar e compartilhar informações.
BSC
Balanced Scorecard ou método de avaliação de desempenho empresarial foi criado por Robert Kaplan e David Norton nos anos 90. Uma vez definida a estratégia corporativa, o BSC a traduz em objetivos, indicadores, metas e planos de ação, constituindo a base de um processo de monitoramento e gerenciamento.
CI
Inteligência Computacional - A Inteligência Computacional compreende a teoria e a aplicação de técnicas computacionais inspiradas em fenômenos naturais que incluem: Redes Neurais, Lógica Fuzzy e Computação Evolucionária.
Surrogate Key
A Surrogate Key nada mais é que o campo de Primary Key da dimensão.
Foreign Key na FATO
Surrogate Key na dimensão
ou seja –> chaves primárias das tabelas-dimensão compõem chaves estrangeiras na tabela-fato
Conformidade de dimensões
Coerência das definições entre dimensões, estabelecidas em momentos diferentes do projeto de data warehouse
Drill Through
Ocorre quando o usuário passa de uma informação contida em uma dimensão para uma outra.
Slice
Corta o cubo (extrai uma fatia), mas mantém a mesma perspectiva de visualização dos dados. Funciona como um filtro que restringe uma dimensão à apenas um ou alguns de seus valores.
Dice
extrai um subcubo do cubo original executando uma operação de seleção em duas ou mais dimensões. Mudança de perspectiva da visão multidimensional, como se o cubo fosse girado. Permite descobrir comportamentos e tendências entre os valores das medidas analisadas em diversas perspectivas.
Drill Across
O nível de análise dentro de uma mesma dimensão é alterado, ou seja, o usuário avança um nível intermediário dentro de uma mesma dimensão.
Pivot
Adicionar ou rearranjar as dimensões das tabelas.
Drill Down
apresenta os dados cada vez mais agrupados ou sumarizados, subindo na hierarquia de uma dimensão.
Rotation
permite visualizar dados de uma nova perspectiva.
Roll Up
apresenta os dados cada vez mais agrupados ou sumarizados, subindo na hierarquia de uma dimensão.
Apache Cassandra
projeto de sistema de banco de dados distribuído altamente escalável de segunda geração, que reúne a arquitetura do DynamoDB, da Amazon Web Services e modelo de dados baseado no BigTable, do Google.
O Cassandra inicialmente foi criado pelo Facebook
Avaliação de produtos OLAP – > 12 regras de avaliação (Ted Codd)
- Visão conceitual multidimensional
- Transparência
- Acessibilidade
- Desempenho consistente na geração de relatórios
- Arquitetura cliente-servidor
- Dimensionalidade genérica
- Manuseio dinâmico de matriz esparsa
- Suporte a multiusuários
- Operações irrestritas de cruzamento de dimensões
- Manipulação de dados intuitiva
- Relatório flexível
- Dimensões e agregação de níveis ilimitados
Arquiteturas OLAP
MOLAP (Multidimensional On Line Analytical
processing);
– ROLAP (Relational On Line Processing);
– HOLAP (Hybrid On Line Analytical Processing);
– DOLAP (Desktop On Line Analytical Processing);
– WOLAP (Web On Line Analytical Processing
ROLAP – Relacional OLAP Server
Esse é um servidor intermediário que fica
entra a base de dados relacional de back-end
e as ferramentas de front-end
**usam SGBDs relacionais ou relacionais
estendidos para gravar e gerencias os dados
do DW
**esquema estrela - STAR SCHEMA
**esquema floco de neve - SNOW FLAKE
-OUTRIGGER
-MULTI STAR
ROLAP é mais indicado para DATA WAREHOUSE pelo grande volume de dados
*RDBMS (Relational DataBase Management System)
LINHA E COLUNA
MOLAP (OLAP Multidimensional)
Os dados são armazenados de forma multidimensional.
MOLAP é mais indidado para DATA MARTS
**MOLAP necessita de um longo período para execução da carga de dados
MOLAP usa a tecnologia MDDB (MultiDimensional Database) – ARRAYS
DOLAP (OLAP Desktop)
O conjunto de dados multidimensionais deve ser criado no servidor e transferido para o desktop. Permite portabilidade aos usuários OLAP que não possuem acesso direto ao servidor
HOLAP (OLAP Híbrido)
Uma combinação dos métodos ROLAP e MOLAP.
Mineração de dados - DATA MINING
processo de análise de conjuntos de dados
que tem por objetivo a descoberta de padrões
interessantes e que possam representar
informações útIL
Classificação das tarefas
Descritivas – caracterizam as propriedades
gerais dos dados em um banco de dados
– Achar padrões reconhecidos por seres humanos
para descrever os dados
Preditivas – essas tarefa realiza uma
inferências sobre os dados atuais para fazer
previsões sobre os mesmos
– Usa variáveis para prever valores futuros ou
desconhecidos de outras variáveis
Três características aplicadas a muitos Conjuntos
de dados
A dimensão refere-se à quantidade de atributos de um conjunto de dados
A resolução está elacionada à Granularidade dos
dados
Um conjunto de dados é muito disperso usando para um atributo relevante, a maioria dos valores é NULL ou um valor padrão, e esse conceito está relacionado à dispersão
PADRÕES EM DADOS
DATA MINING
Modelos simples – Consultas baseadas em SQL, OLAP • Modelos intermediários – Regressão, árvore de decisão, agrupamento • Modelos complexos – Redes neurais, outra indução de regras
Knowledge Discovery in Databases - KDD
Processo geral de descoberta de
conhecimentos úteis previamente
desconhecidos a partir de grandes bancos de
dados
Data Mining ETAPA KDD
Parte do processo de descoberta
de conhecimentos em bancos de dados (KDD)
Prof. Thiago Cavalcanti
Fases da Mineração de Dados (CRISP-DM)
visão geral do ciclo de vida de um projeto de mineração de dados -Entendimento do Negócio (Business Understanding) -Seleção dos Dados (Data Understanding) -Limpeza dos Dados (Data Preparation) Modelagem dos Dados (Modeling) -Avaliação do processo (Evaluation) - Execução (Deployment)
TIPOS DE ATRIBUTOS
Compostos. Os atributos compostos podem ser divididos em partes menores
Simples. São chamados também por atributos atômicos.
Monovalorados. São atributos que possuem apenas um valor para uma entidade em particular.
Multivalorado. São atributos que possuem um ou mais valores para o mesmo
Armazenado. Em geral todos os atributos são armazenados.
Derivado. Alguns atributos podem ter uma relação entre si. Por exemplo, idade e data-nascimento de uma pessoa
Nulo. Em alguns casos, uma entidade pode não necessitar de um valor aplicável a um de seus atributos
SQL
A linguagem de consulta estruturada, ou Structured Query Language (SQL), que serve
para descrever estruturas de dados e esquemas, é uma linguagem de pesquisa declarativa padrão para banco de dados relacionais
DECLARATIVA
Linguagem de definição de dados (DDL)
criam, modificam ou destroem
objetos de banco de dados, incluindo CREATE, ALTER,
DROP …
NÍVEL CONCEITUAL
Linguagem de manipulação de dados (DML)
Fornece comandos de manipulação de dados específicos
como SELECT, INSERT, UPDATE e DELETE
Linguagem de Consulta de dados (DQL)
um subconjunto da DML, portanto também serve para
manipular dados. Possui apenas o comando SELECT
Linguagem de transação de dados (DTL)
Inclui comandos de COMMIT, ROLLBACK, or
SAVEPOINT
Linguagem de Controle de dados (DCL)
comandos relacionados com
permissões ou privilégios. Os mais conhecidos são
GRANT e REVOKE
Linguagem de Definição de
Armazenamento - SDL
NÍVEL INTERNO
VDL - Linguagem de Definição de Visões
NÍVEL EXTERNO
DEFINIÇÃO DE VISÕES
Partes da linguagem SQL (Silberchatz)
• Linguagem de definição de dados (DDL) • Linguagem interativa de manipulação de dados (DML) • Incorporação DML (Embedded SQL) • Definição de Visões(DDL) • Autorização (DDL) • Integridade (DDL) • Controle de Transações (DTL)
Controle de Acesso
É todo controle feito quanto ao acesso ao BD, impondo regras de restrição, através das contas dos usuários. O Administrador do BD (DBA) é o responsável superior por declarar as regras dentro do SGBD.
Controle de Inferência
É um mecanismo de segurança para banco de dados estatísticos que atua protegendo informações estatísticas de um individuo ou de um grupo.
Controle de Fluxo
É um mecanismo que previne que as informações fluam por canais secretos e violem a política de segurança ao alcançarem usuários não autorizados. Ele regula a distribuição ou fluxo de informação entre objetos acessíveis.
Domínio de Segurança
Onde cada usuário tem um domínio de segurança, um conjunto de propriedades que determinam coisas como ações (privilégios e papeis) disponíveis para o usuário; cota de tablespaces (espaço disponível em disco) do usuário; limites de recursos de sistema do usuário.
SQL
usada para definição e manutenção de bancos de dados relacionais
• Não existe fora do universo relacional
PL/SQL
Encontrado no Oracle. PL/SQL significa Procedural Language/SQL e contém muitas similaridades com a linguagem Ada
ALTER TABLE
usado para adicionar
atributos a uma relação existente.
• Todas as tuplas da relação recebem valores nulo para seu novo atributo
CHECK
CREATE ASSERTION CHECK (condição de busca)
SQL contem 6 tipos de restrições
PRIMARY KEY FOREING KEY NOT NULL UNIQUE CHECK DEFAULT podem ser criadas tanto no “crate table” ou no “alter table”
UNIQUE
testa se a sub-consulta tem
alguma tupla repetida no seu resultado
where UNIQUE
where NOT UNIQUE
HAVING
predicados na cláusula having são aplicados após
a formação dos grupos
GROUP BY
atributos na cláusula select fora das funções
agregadas devem aparecer na lista group by.
Resumo das consultas SQL
SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ];
COMANDO INSERT
Adicionar uma nova tupla em conta
insert into conta
values ('Perryridge', A-9732, 1200);
• ou de forma equivalente
insert into conta (nome_agencia, saldo, numero_conta)
values ('Perryridge', 1200, A-9732);
• Adicionar uma nova tupla à conta com saldo
igual a nulo
insert into account
values ('Perryridge', A-777, null)COMANDO DELETE e TRUNCATE
DELETE – Deleta linha(s) de uma tabela – DELETE FROM [ [ AS ] ] [ WHERE ] – Exclua todos os registros de contas da agência Ceilandia delete from conta where nome_agencia = 'Ceilandia‘; • TRUNCATE – Deleta todas as linhas de uma tabela sem gerar nenhuma ação de gatilho (triggered action) • TRUNCATE TABLE [CONTINUE IDENTITY | RESTART IDENTITY ]
COMANDO UPDATE
update conta set saldo = saldo * 1.06 where saldo > 10000 update conta set saldo = saldo * 1.05 where saldo =< 10000
Visões
Fornecem um mecanismo para:
– Esconder certos dados do alcance de certos
usuários.
– Especificar consultas complexas apenas uma vez
– Conhecidas como tabelas virtuais cuja definição
existe como um objeto do schema
CREATE VIEW [ ( ) ]
AS
[ WITH CHECK OPTION ]
Schema objects
- Base tables
- Views
- Columns
- Domains
- Character sets
- Collations
- Translations
- User-defined types
- Sequences
- Triggers
- SQL-invoked routines
Grant e Revoke
O comando SQL GRANT é usado para prover acesso ou privilégios para os usuários no banco de dados. O comando REVOKE serve para remover permições e privilegios de acesso a base de dados dos usuários. ***UPDATE ou INSERT pode especificar atributos ****(SELECT, DELETE) não especificam atributos
Triggers
Ações que são automaticamente disparadas
quando um evento acontece
• INSERT, DELETE e UPDATE
PL/SQL
• Linguagem procedural
• Blocos de instrução com condicionais e loops
• Extensão da linguagem SQL para o banco de
dados Oracle
• Combina o poder e a flexibilidade de SQL com as
estruturas de código de procedimentos
Elementos básicos do PL/SQL
Operadores – Atribuição := – Diferente de < > ou ~= – Referência à base de dados @ • Comentários • Data do sistema – data_provaTI := SYSDATE Declaração de constante – desconto_padrao CONSTANT NUMBER(3,2) := 8.25; • Declaração de valor default – participante BOOLEAN DEFAULT TRUE; • Declaração de variável com tipo de um atributo de tabela – carro.modelo%TYPE
Recuperação de Dados para Variável
Uvlizar comando → SELECT … INTO
– SELECT INTO FROM
WHERE… ;
Blocos PL/SQL
Várias instruções de SQL podem estar contidas
em um único bloco de PL/SQL e enviadas
como uma só unidade para o servidor
DECLARE
/* Seção para declarar variáveis, tipos, cursores e subprogramas locais */
BEGIN
/Seção executável -
comandos procedurais e
SQL./
É a única obrigatória
EXCEPTION
/*Comandos Manipulação de erros*/ END; Tratamento de Exceções – Para cada tipo de erro pode-se colocar um WHEN na seção EXCEPTION – A opção WHEN OTHERS pode ser usada para tratar qualquer erro diferente dos listados
Blocos anônimos
Construídos de forma dinâmica e executados só uma
vez
Gatilho (Database Triggers
Um gatilho de banco de dados é um subprograma armazenado associado a uma tabela de banco de dados, visão ou evento. O gatilho pode ser chamado uma vez, quando
algum evento ocorre, ou muitas vezes, uma para cada linha afetada por uma instrução
INSERT, UPDATE ou DELETE.
Blocos anônimos (Anonymous Blocks)
Blocos anônimos de PL / SQL podem ser
submetidos a ferramentas interativas como o
SQL * Plus e Enterprise Manager, ou
incorporado em um pré-compilador Oracle ou
programa OCI. Em tempo de execução, o
programa envia estes blocos para o banco de
dados Oracle, onde são compilados e
executados.
Subprogramas Armazenados (Stored Subprograms)
Subprogramas podem ser compilados e armazenados numa base de dados Oracle, pronto para ser executado. Uma vez compilados, é um objecto de esquema conhecido como uma função armazenada(stored function) ou procedimento armazenado (stored procedure), que podem ser referenciadas por diversas aplicações ligadas ao banco de dados.
Estrutura Básica do PL/SQL
unidade básica em PL/SQL é um bloco. Todos os programas em PL/SQL são compostos por blocos, que podem estar localizados uns dentro dos outros. Geralmente, cada bloco efetua uma ação lógica no programa. Um bloco tem basicamente a seguinte estrutura:
DECLARE
Seção para declaração de variáveis,tipos e subprogramas locais.
BEGIN (ÚNICA OBRIGATÓRIA)
Seção Executável, nesta seção ficam as instruções procedurais e SQL. Esta é a única seção do bloco que é indispensável e obrigatória.
EXCEPTION
Seção/Setor onde ficam as instruções de tratamento de erro.
END (ÚNICA OBRIGATÓRIA)
JOIN
Na linguagem SQL, executamos diversas consultas para geralmente criar relatórios que serão posteriormente utilizados para análise e a tomada de alguma decisão, e para criarmos consultas cada vez mais completas precisamos utilizar várias tabelas em conjunto e para isso usamos os JOINs.
JOIN em SQL
correspondente a uma operação de junção em álgebra relacional, combina colunas de uma ou mais tabelas em um banco de dados relacional. Ela cria um conjunto que pode ser salvo como uma tabela ou usado da forma como está.
INNER JOIN
compara cada linha da tabela A com as linhas da tabela B para encontrar todos os pares de linhas que satisfazem a condição de junção. Se a condição de junção for avaliado como TRUE, os valores da coluna das linhas correspondentes das tabelas A e B serão combinados em uma nova linha e incluídos no conjunto de resultados
LEFT JOIN
LEFT JOIN retorna todas as linhas da tabela “esquerda” A e as linhas correspondentes ou valores NULL da tabela “esquerda” A.
RIGHT JOIN
A RIGHT JOIN combina dados de duas ou mais tabelas. A RIGHT JOIN começa a selecionar dados da tabela “direita” B e a corresponder às linhas da tabela “esquerda” A.
FULL JOIN
A cláusula FULL JOIN retorna todas as linhas das tabelas unidas, correspondidas ou não, ou seja, você pode dizer que a FULL JOIN combina as funções da LEFT JOIN e da RIGHT JOIN. FULL JOIN é um tipo de junção externa, por isso também é chamada junção externa completa.
CROSS JOIN
retorna todas as linhas das tabelas por cruzamento, ou seja, para cada linha da tabela esquerda queremos todos os linhas da tabelas direita ou vice-versa. Ele também é chamado de produto cartesiano entre duas tabelas. Porém, para isso é preciso que ambas tenham o campo em comum, para que a ligação exista entre as duas tabelas.
PARAMETERS
Para consultas executadas regularmente
BEGIN TRANSACTION
As transações não são iniciadas automaticamente. Para iniciar uma transação, faça isso explicitamente usando BEGIN TRANSACTION.
É possível aninhar transações até cinco níveis de profundidade. Para iniciar uma transação aninhada, use BEGIN TRANSACTION no contexto de uma transação existente
PROCEDURE
uma lista opcional das definições de parâmetro e uma única instrução SQL
ECA - TRIGGERS -> GATILHO
ECA -> EVENTO- CONDIÇÃO - AÇÃO **EVENTO OBRIGATÓRIO **AÇÃO OBRIGATÓRIO Procedimentos armazenados especializados > DISPARAM UM EVENTO > VERIFICA A CONDIÇÃO > EXECUTA
PODE CAUSAR ANOMALIA
UPDATE, INSERT E DELETE
pode atualizar e se o banco estiver errado pode causar anomalia
Comandos básicos em SQL - INSERT, UPDATE, DELETE e SELECT
INSERT SQL - O comando para inclusão no banco de dados é o INSERT
UPDATE SQL -O comando para atualizar registros é UPDATE
DELETE SQL - O comando utilizado para apagar dados é o DELETE
SELECT SQL - Uma sub-consulta é uma instrução SELECT aninhada dentro de outra instrução SELECT, INSERT, DELETE ou UPDATE
INSERT SQL
INSERT INTO nome_tabela (lista-de-campos)
VALUES (lista_dados)
–OU
INSERT INTO nome_tabela VALUES (lista_dados)
Onde:
Nome_tabela: nome da tabela no qual será inserido os dados. Lista-de-campos: nome das colunas que receberão os valores. Lista-dados: valores que serão inseridos na tabela. Estes campos devem estar na mesma ordem descrita em lista-de-campos, todos separados por vírgula. Se for utilizado um comando SELECT o mesmo deve retornar a mesma quantidade de colunas com os mesmos tipos de dados especificados em lista-de-campos.
UPDATE SQL
UPDATE nome_tabela
SET CAMPO = “novo_valor”
WHERE CONDIÇÃO
WHERE FILTRA NO UPDATE
Onde:
Nome_tabela: nome da tabela que será modificada Campo: campo que terá seu valor alterado Novo_valor: valor que substituirá o antigo dado cadastrado em campo Where: Se não for informado, a tabela inteira será atualizada Condição: regra que impõe condição para execução do comando
DELETE SQL
DELETE FROM nome_tabela
WHERE condição
Onde:
Nome_tabela: nome da tabela que será modificada Where: cláusula que impõe uma condição sobre a execução do comando
SELECT SQL
SELECT * FROM A
WHERE Y > ANY (SELECT Y FROM B WHERE X>20)
Comparação [ANY|SOME|ALL] (instrução sql) Expressão [NOT] IN (instrução sql) Expressão [NOT] EXISTS (instrução sql) O uso de != ALL equivale a NOT IN
LIKE
caracteres porcentagem (%) e sublinhado () podem ser combinados para realizar uma busca por substrings. Assim, por exemplo, a expressão “%” permitirá a busca por qualquer string com, pelo menos, um caractere.
DDL
Data Definition Language (Linguagem de Definição de Dados) são usadas para definir a estrutura de banco de dados ou esquema.
◦CREATE-para criar objetos no banco de dados
◦ALTER –altera a estrutura da base de dados
◦DROP –apagaobjetosno banco de dados
◦TRUNCATE –remover todos os registros de uma tabela, incluindo todos os espaços alocados para os registros são removidos. Não tem rollback.
◦COMMENT –adicionar comentários ao dicionário de dados
◦RENAME –para renomear um objeto
DML
Data Manipulation Language(Linguagem de Manipulação de Dados) são utilizados para o gerenciamento de dados dentro de objetos do banco.
◦SELECT-recuperar dados do banco de dados
◦INSERT –inserir dados em uma tabela
◦UPDATE –atualiza os dados existentes em uma tabela
◦DELETE –exclui registros de uma tabela,
◦CALL –chamar um subprograma PL / SQL
◦EXPLAIN PLAN –explicar o caminho de acesso aos dados
◦LOCK TABLE –controle de concorrência
TCL
Transaction Control Language–(Controle de Transações) são usados para gerenciar as mudanças feitas por instruções DML . Ele permite que as declarações a serem agrupadas em transações lógicas .
◦COMMIT –salvar o trabalho feito
◦SAVEPOINT –identificar um ponto em uma transação para que mais tarde você pode efetuar um ROLLBACK
◦ROLLBACK –restaurar banco de dados ao original desde o último COMMIT
DCL
Data Control Language(Linguagem de Controle de dados) declarações.
◦GRANT –atribui privilégios de acesso do usuário a objetos do banco de dados
◦REVOKE –remove os privilégios de acesso aos objetos obtidos com o comando GRAN
DQL
Data Query Language (Linguagem de Consulta de Dados)
◦SELECT-recuperar dados do banco de dados
Obs: A maioria das literaturas considera o SELECT como DML, mas existem algumas que o consideram DQL
DDL
Data Definition Language:
◦utilizada para definir os schemas conceitual e interno (níveis lógico e físico), em SGBD sem que não há uma separação estrita entre estes dois níveis.
SDL
Storage Definition Language:
◦usada para especificar o esquema interno.
VDL
View Definition Language:
◦usada para especificar as visões dos usuários e o seu mapeamento no esquema conceitual.
DDL
Na criação de tabelas, é possível especificar vários tipos de restrições:
◦Chave Primária: PRIMARY KEY ;
◦Chave Estrangeira: FOREIGN KEY;
◦Chave Alternativa (ou alternada):
**UNIQUE NOT NULL;
◦Única sem repetição: UNIQUE;
◦Restrição de Domínio: CHECK.
**Pode-se atribuir nomes às restrições de integridade:
◦CONSTRAINT NOME_RESTRIÇÃO TIPO RESTRIÇÃO
Otimizar as consultas
Atentar para normlização
USE ÍNDICES
Índices são identificações que ajudam a tornar a consulta a um dado do banco muito mais rápida.
COMANDO OPTIMIZE
O comando Optimize table deve ser utilizado em tabelas que são atualizadas frequentemente e que tem manipulações de registros (alteração e exclusão).
SIMPLIFIQUE AS CONSULTAS
Remova parênteses ( ) desnecessários em cláusulas WHERE;
Utilize múltiplas linhas com uma única instrução SQL;
Utilize SELECT COUNT(1) e não SELECT COUNT(*) para contar valores especificados e não generalizar a consulta.
Melhorar desempenho da query:
Remoção de ORDER BY desnecessários; Uso de campo indexados nos filtros do WHERE; Não utilizar funções no WHERE (year(), month()) para comparações de campos.
Processamento SQL
- Análise do código fonte para localizar erros de sintaxe;
- Uso do otimizador SQL para obter um plano de execução;
- O script é executado com base no plano elaborado;
- Busca do conjunto de resultados do banco de dados e resposta à consulta chamada.
Índices
Um índice é uma estrutura em disco associada a uma tabela ou exibição que agiliza a recuperação de linhas.
Índices são normalmente criados em colunas que são acessadas com maior frequência de modo que a informação possa ser recuperada mais rapidamente.
Uso de Union
Um comando union equivale a fazermos a junção de dois conjuntos eliminando, em seguida, os elementos duplicados (o que poderia ser feitos através de um comando distinct). Se sabemos que existem registros duplicados e isso representa um problema para a aplicação, então devemos utilizar o union para eliminá-los. Por outro lado, se não haverá linhas duplicadas ou se não é um problema tê-las, utiliza-se o union all em vez de union. A vantagem do union all é que ele não realiza o distinct, evitando o desperdício de recursos do servidor SQL.
Uso do comando Group By
A cláusula group by pode ser usada com ou sem uma função agregada (max, sum, count, avg, …). Para obtermos um melhor desempenho, não devemos utilizá-la sem uma função agregada.
Relação entre tabelas
É bastante comum realizar uma comparação e relação entre tabelas.
· O número de linhas de retorno a partir da consulta deve ser o menor possível;
· Manter o número de agrupamentos o mais limitado possível;
· Não agrupar colunas redundantes;
· Se existe um join na mesma instrução select que tem um group by, tente reescrever uma consulta utilizando uma subconsulta em vez de usar o join. Se for possível fazer isso, o desempenho será melhor. Se for necessário usar um join, utilize as colunas do group by com a mesma coluna da tabela em que a função está sendo usada;
· Considere adicionar um order by para a(s) mesma(s) coluna(s) existente(s) no group by. Isso pode fazer com que ele tenha um melhor desempenho
Otimização de consultas
um trabalho extremamente importante para a gestão e manutenção de uma base de dados
Otimização de Consultas SQL
Passos principais
Tradução da consulta SQL para a álgebra relacional
Otimização do resultado
Estratégias de otimização
Otimização baseada em heurísticas
Otimização baseada na estimativa de custo da consulta
Otimização semântica
RESTRIÇÕES DE INTEGRIDADE ADIÁVEIS
NOT DEFERRABLE
◦Padrão.
◦Restrições são aplicadas imediatamente.
DEFERRABLE
◦Inicialmente setadas para DEFERRED ou IMMEDIATE
INITIALLY DEFERRED ouINITIALLY IMMEDIATE
◦DEFERRED
Validações são feitas no momento do commit (final da transação)
◦IMMEDIATE
Validações são feitas a cada commando (igual ao NOT DEFERRABLE)
Criação de tabela a partir de outra tabela
CREATE TABLE Cliente_Simples
AS SELECT cliid, nome, cpf, sexo
FROM Cliente;
Já cria a tabela e adiciona as linhas do select
ADICIONAR COLUNA
SQL ANSI:
ALTER TABLE ADD [COLUMN] ;
SQL Server, Oracle, MySQL:
ALTER TABLE Cliente ADD Tipo_Cli VARCHAR(2) NOT NULL DEFAULT ‘PF’;
ALTER TABLE Cliente ADD (DataNascimento DATETIME, Idade INTEGER);
Visão
Meio de prover ao usuário um “modelo personalizado” do banco de dados
Objetivos:
◦Simplificar consultas
◦Autorização de acesso (segurança)
O SGBD armazena a definição da visão, mas ela é instanciada quando uma consulta sobre ela for executada.
Nem toda visão pode ser atualizada.
***NO ORACLE VISÃO MATERIALIZADA > A VISÃO ESPECIFICA FICA SEMPRE CRIADA
PSEUDO COLUNAS - PL/SQL - SELECT
CURRVAL : Valor corrente de uma seqüência armazenada no banco de dados.
NEXTVAL : Valor do próximo valor da seqüência armazenada no banco de dados.
LEVEL : Nível na estrutura da árvore em bancos de dados.
ROWID : Coluna que especifica a localização da linha (definido pelo Oracle).
ROWNUM : Número da linha selecionada em uma tabela. Não é afetada pela cláusula ORDER BY.
SELECT INTO
usada para recuperar uma linha ou conjunto de colunas do banco de dados Oracle. O SELECT INTO é na verdade um consulta SQL padrão onde a cláusula SELECT INTO é usada para colocar o dados retornados em variáveis predefinidas.
INSERT INTO
Em PL / SQL, podemos inserir os dados em qualquer tabela
Sobre OLAP
MOLAP
Alto desempenho (alta velocidade) e baixa escalabilidade;
ROLAP
Baixo desempenho; Alta escalabilidade, flexibilidade e padronização. Pode ocasionar problemas na rede.
HOLAP
Hibrido (ROLAP e MOLAP). Mais caros dos tipos de OLAP
Controle de Transação - ACID
ATOMICIDADE»_space; Subsistema de recuperação
ISOLAMENTO»_space; Subsistema de controle de concorrência.
DURABILIDADE»_space; Subsistema de recuperação.
CONSISTÊNCIA»_space; Programador ou módulo de restrições de integridade.
TRANSAÇÕES
Atualização Temporária: Ocorre quando uma transação atualiza um item do BD e depois a transação falha por algum motivo. Nesse meio tempo o item atualizado é acessado (lido) por outra transação antes de ser alterado ou voltar para seu valor original
b) Leitura Suja: Leitura de dados não confirmados de uma linha existente podendo ocasionar a leitura de uma informação nunca confirmada.
c) Resumo Incorreto: Uma transação está calculando uma função de resumo agregado em uma série de itens de um BD, enquanto outras transações estão atualizando alguns desses itens.
d) Leitura não repetitiva: Uma transação A lê o mesmo item duas vezes e entre essas duas leituras uma transação B altera o valor do item. Transação A percebe valores diferentes para as suas duas leituras.
e) Atualização perdida:quando duas (ou mais) transações leem o valor antigo de uma variável e depois a utilizam para calcular um novo valor.
Duas escritas sobre o mesmo valor inicial, o valor de uma das alterações é perdido.
TRANSAÇÃO - Operações Adicionais
Begin-transaction: Denota o início da execução da transação;
End-transaction: Especifica que as operações da transação terminaram e marca o limite final da execução da transação. Neste ponto é necessário verificar se o COMMIT ou o ABORT são necessários, caso e marca o limite final da execução da transação. Neste ponto é necessário verificar se o COMMIT ou o ABORT são necessários, caso já não tenham sido explicitados;
Commit-transaction: Sinal de término com sucesso e que as alterações podem ser “permanentemente” gravadas no BD;
Rollback(abort-transaction): Assinala que a transação não terminou com sucesso e que seus efeitos devem ser desfeitos;
Undo: Desfaz uma operação
Redo: Refaz uma operação.
Execução Concorrente
Permitir que múltiplas transações concorram na atualização de dados traz diversas complicações em relação à consistência desses dados. Problemas na Concorrência Problema de alterações perdidas; Problema de alteração temporária Problema do resumo incorreto.
VIEW PL/SQL
CREATE VIEW valores (nome, minsal, maxsal, medsal)
AS SELECT d.depnome, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM empregado e, departamento d
WHERE e.depnro=d.depnro
GROUP BY d.depnome;
Cursores
Um cursor é um identificador ou nome para uma área SQL privada - uma área na memória na qual uma instrução analisada e outras informações para processamento da instrução são mantidas.
RESTRIÇÕES DE INTEGRIDADE ADIÁVEIS
NOT DEFERRABLE
◦Padrão.
◦Restrições são aplicadas imediatamente.
DEFERRABLE
◦Inicialmente setadas para DEFERRED ou IMMEDIATE
INITIALLY DEFERRED ou INITIALLY IMMEDIATE
◦DEFERRED
Validações são feitas no momento do commit (final da transação)
◦IMMEDIATE
Validações são feitas a cada commando (igual ao NOT DEFERRABLE)
DDL DML DCL DTL
DDL (Data Definition Language): Create, Drop, Alter, Rename, Truncate
DML (Data Manipulation Language): Delete, Insert, Update, Select
DCL (Data Control Language): Grant, Revoke
DTL (Data Transaction Language): Rollback, Commit, Begin Transation, End Transaction
DML Alto nível ou não-procedural
DML de alto nível pode ser usada sozinha para especificar operações complexas de BD concisamente. Vários SGBDs permitem declarações DML de alto nível de duas formas:
Inseridas de forma interativa a partir de um monitor ou terminal – neste caso, declarações DML devem ser identificadas dentro do programa de tal forma que possam ser extraídas pelo pré-compilador e processadas pelo SGBD; Embutidas em uma linguagem de programação de propósito geral.DMLs de alto nível, como SQL, são também chamadas de DMLs set-at-a-time ou DMLs set-oriented – podem especificar e recuperar vários registros em uma única sentença DML. Uma sentença DML de alto nível, usada sozinha de forma interativa, é chamada de linguagem de consulta.
DML Baixo nível ou procedural
DMLs de baixo nível devem ser embutidas em uma linguagem de programação de propósito geral. Este tipo de DML tipicamente recupera registros individuais ou objetos do BD e os processa de forma separada. Assim, é necessário usar construções de linguagem de programação, tal como looping, para recuperar e processar cada registro de um conjunto de registros.
Sempre que os comandos DML, tanto de alto quanto de baixo nível, forem embutidos em uma linguagem de programação de uso geral, esta será chamada de linguagem hospedeira e o comando DML de sub-linguagem de dados.
DML BAIXO NÍVEL
somente as DMLs de baixo nível ou procedurais devem estar embutidas em uma linguagem de programação de propósito geral.
Linguagem hospedeira
Sempre que os comandos DML, tanto de alto quanto de baixo nível, forem embutidos em uma linguagem de programação de uso geral, esta será chamada de linguagem hospedeira e o comando DML de sub-linguagem de dados.
MERGE JOIN
é um algoritmo de junção (por exemplo, HASH JOIN ou NESTED LOOPS).
Baseia-se primeiro na classificação de ambos os conjuntos de dados de acordo com as condições de junção (talvez já classificados devido ao índice existente) e, em seguida, percorrer os conjuntos de dados classificados e encontrar correspondências.
Custo de juntar as duas relações = M + N
PL/SQL possui 2 elementos básicos: PL/SQL Engine e Oracle Server
O primeiro a receber o bloco PL/SQL é o Engine. Ele executa a parte procedural e manda a parte SQL para o Oracle Server (servidor), pois é necessário acessar fisicamente o banco de dados para obter as informações requeridas na consulta.
CREATE PROCEDURE
instrução permite criar procedimentos autônomos que são armazenados no banco de dados.
Tuning em Banco de Dados
ação que visa a otimização do desempenho da sua infraestrutura de dados. Ou seja, potencializar o trabalho de seus próprios recursos em busca de máxima performance, seja na camada operacional ou mesmo no código-fonte
1- Planejamento de performance:
Definição e configuração do ambiente em que o BD será instalado, considerando-se os seguintes itens: Hardware, Software, Sistema Operacional e Infraestrutura de rede.
2- Tuning de instância e BD:
Ajuste de parâmetros e configurações do BD (atividades que fazem parte do trabalho de um DBA).
3- SQL Tuning:
Otimização de instruções SQL.
EXPLAIN PLAN FOR ;
Iteração PL/SQL
IF e CASE - são estruturas condicionais.
GOTO - é estrutura de salto.
NULL - não realiza nenhuma operação.
LOOP e EXIT que são estruturas de controle de um bloco de comandos.
Bloco Anônimo PL/SQL
É um bloco PL/SQL que não será armazenado definitivamente no banco. O bloco será interpretado, executado e depois será descartado
