Baixe o app para aproveitar ainda mais
Prévia do material em texto
Sistemas de Banco de Dados Fundamentos em Bancos de Dados Relacionais Wladmir Cardoso Brandão www.wladmirbrandao.com Material distribuído sob licença CC BY-NC-ND 4.0 Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International http://www.wladmirbrandao.com https://creativecommons.org/licenses/by-nc-nd/4.0/ introdução Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Sistemas de Banco de Dados (SBD) "Sistemas de banco de dados referem-se ao conjunto de dados relacionados e sua respectiva forma de acesso e organização... São compostos por uma coleção de dados organizados, uma estrutura lógica determinando a forma como os dados são armazenados, organizados e manipulados, e um software que provê acesso aos dados a usuários e aplicações." Elmasri & Navathe, 2016 I Coleção de Dados→ banco de dados I Estrutura Lógica→ modelo de dados I Software→ sistema gerenciador de banco de dados www.wladmirbrandao.com 3 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Sistemas de Banco de Dados (SBD) Usuários e aplicações intera- gem com o sistema subme- tendo consultas consultas são interpretadas pelo sistema, que realiza otimizações necessárias para sua correta execução O próprio sistema decide quais dados são necessários para responder uma consulta e se encarrega de recuperá- los a partir dos repositórios sob seu controle www.wladmirbrandao.com 4 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Banco de Dados (BD) Coleção de dados organizados I Dados→ símbolos, sinais, códigos I Atende necessidades específicas de usuários I Presente em diferentes ambientes de negócio I Reserva de hotel I Reserva de livros em biblioteca I Visualização de catálogos de filmes I Compra de produtos em supermercado I Saque e depósito de dinheiro em caixa bancário www.wladmirbrandao.com 5 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Bilhões de produtos em catálogo Dezenas de milhões de transações diárias Atualização frequente de estoque e pedidos www.wladmirbrandao.com 6 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Propriedades BDs possuem características que os diferenciam de outros tipos de coleções I Finalidade→ construídos com um propósito específico I Realidade→ representam o "mundo real" I Mundo Real→ minimundo, universo de discurso I Coerência→mantêm a coerência lógica da coleção I Compartilhamento→ provêm compartilhamento de dados www.wladmirbrandao.com 7 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Taxonomia BDs podem ser categorizados quanto à forma de utilização I Manual→ criado e mantido sem o uso de computadores I Exemplo→ lista telefônica (páginas amarelas) I Computadorizado→ criado e mantido com o uso de computadores I Exemplo→ The Human Genome Database (GDB) www.wladmirbrandao.com 8 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Taxonomia BDs também podem ser categorizados quanto à sua aplicação I Tradicional→ texto, incluindo números e registros temporais I Multimídia→ imagens, áudios e vídeos I Geográfico→mapas, imagens de satélite e registros climáticos I Data Warehouse→ armazém de dados utilizado no processamento analítico online (OLAP) para auxílio à tomada de decisão I Ativo (Tempo Real)→ utilizado em aplicações com rigorosos requisito de desempenho, como em processos industriais de manufatura www.wladmirbrandao.com 9 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Abordagens Diferentes abordagens de implementação I Processamento em Arquivo I Usuário define arquivos necessários para uma aplicação específica como parte da programação da aplicação I Sistema Gerenciador de Banco de Dados (SGBD) I Repositório único I Abstração de dados I Natureza autodescritiva I Compartilhamento de dados I Isolamento entre programas e dados I Suporte a múltiplas visões sobre dados I Processamento de transação multiusuário www.wladmirbrandao.com 10 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Projeto Construção de modelos para implementação I Modelo→ representação de entes e eventos reais I Etapas de implementação 1. Especificação→ descrição do minimundo 2. Análise de Requisitos→ restrições de operação 3. Projeto Conceitual→ estruturas e restrições conceituais 4. Projeto Lógico→ estruturas e restrições lógicas 5. Projeto Físico→ estruturas e restrições físicas I Revisado continuamente para que o BD reflita o estado do minimundo www.wladmirbrandao.com 11 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 BD: Atores Ator→ papel desempenhado pelos que interagem com o BD I Administrador (DBA)→ responsável pela operação e pelo cumprimento dos requisitos, atuando em todas as etapas da implementação I Projetista→ responsável pelo projeto, atuando em todas as etapas da implementação I Analista→mais presente nas etapas de projeto conceitual e lógico I Programador→ atua preponderantemente no projeto lógico I Usuário→ demandante, conhecedor do minimundo e mais presente na especificação e análise de requisitos www.wladmirbrandao.com 12 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados Estrutura lógica que determina a forma como os dados são armazenados, orga- nizados e manipulados I Coleção de conceitos que descrevem a estrutura do BD I Incorpora operações para especificar atualização e recuperação de dados I Exemplo→ inserir, remover, modificar ou recuperar I Define o comportamento de uma determinada aplicação www.wladmirbrandao.com 13 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Sistema Gerenciador de Banco de Dados (SGBD) Coleção de programas (software) que permitem aos usuários criar e manter BDs I Definir→ especificar tipos, estruturas e restrições armazenadas sob forma de metadados no catálogo (dicionário) do sistema I Construir→ armazenar dados em meio controlado pelo SGBD I Manipular→ inserir, remover, modificar e recuperar dados I Compartilhar→ prover acesso simultâneo a múltiplos usuários www.wladmirbrandao.com 14 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SGBD: Propriedades Controle de Redundância I Flexibilidade I Múltiplas interfaces I Economia de escala I Garantia de padrões I Restrições de acesso I Backup e recuperação I Disponibilidade elevada I Restrições de integridade I Tempo de desenvolvimento I Relacionamentos complexos www.wladmirbrandao.com 15 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SGBD: Limitações Uso de SGBDs pode ser inadequado em algumas situações I Monousuário→ acesso por múltiplos usuários não requerido I Baixa complexidade→ aplicações muito simples e bem definidas I Requisitos rigorosos→ aplicações de tempo real, de alta escalabilidade e sistemas embarcados com capacidade de armazenamento limitada I Alta especialização→ aplicações que demandam recursos que a generalidade oferecida pelo SGBD para definição e processamento de dados não suporta I Exemplo→ funções de segurança sofisticadas I Custo proibitivo→ impossibilidade de investimento inicial significativo em hardware, software e treinamento www.wladmirbrandao.com16 / 373 http://www.wladmirbrandao.com modelo de dados Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Estrutura de BD Composta por tipos, relacionamentos e restrições aplicados aos dados I Abstração de Dados I Estrutura do BD percebida de maneira diferente por usuários de acordo com diferentes níveis de detalhamento I Abordagem de BD deve ocultar detalhes de organização e armazenamento dos dados I Fornece recursos essenciais para a compreensão dos dados e de seus relacionamentos I Modelo de Dados→ oferece meios necessários para se alcançar a abstração www.wladmirbrandao.com 18 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados Estrutura lógica que determina a forma como os dados são armazenados, orga- nizados e manipulados I Coleção de conceitos que descrevem a estrutura do BD I Incorpora operações para especificar atualização e recuperação de dados I Exemplo→ inserir, remover, modificar ou recuperar I Define o comportamento de uma determinada aplicação www.wladmirbrandao.com 19 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados Oferece diferentes níveis de abstração: I Conceitual→ alto nível de abstração I Representa a estrutura como os usuários a percebem I Conceitos→ entidade, atributo e relacionamento I Representativo→ nível intermediário de abstração I Também conhecido como modelo de implementação I Representa a estrutura detalhando aspectos de implementação I Oculta detalhes de armazenamento físico I Conceitos→ objeto, relação, tupla e coluna I Físico→ baixo nível de abstração I Representa a estrutura detalhando aspectos de armazenamento físico I Conceitos→ arquivo, registro, campo, índice www.wladmirbrandao.com 20 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados: Conceitual Entidade→ ente (objeto) do universo de discurso ProfessorCPF Nome Sexo Salário possui Departamento Número Nome Atributo→ propriedade que caracteriza uma entidade ProfessorCPF Nome Sexo Salário possui Departamento Número Nome www.wladmirbrandao.com 21 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados: Conceitual Relacionamento→ associação entre duas ou mais entidades ProfessorCPF Nome Sexo Salário possui Departamento Número Nome ProfessorCPF Nome Sexo Salário possui Departamento Número Nome CampusNome Cidade www.wladmirbrandao.com 22 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados: Representativo Existem diferentes modelos representativos I Hierárquico I BD→ coleção de árvores formando uma floresta I Registro→ nó da árvore I Associação entre registros→ aresta da árvore I Um nó filho só pode ter um pai (1:N ) I Rede I Extensão do modelo hierárquico I Permite associações N:N I Objeto I BD→ coleção de objetos I Registro→ objeto I Associação entre registros→ ligação I Próximo ao modelo de dados conceitual www.wladmirbrandao.com 23 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados: Representativo Existem diferentes modelos representativos I Relacional I BD→ coleção de relações (elementos tabulares) I Registro→ tupla (linha) I Associação entre registros→ relacionamentos I Embasamento em lógica de predicados e na teoria dos conjuntos I Amplamente adotado em SGBDs comerciais baseados em transações I Consolidado, com alto desempenho na execução de operações básicas PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome www.wladmirbrandao.com 24 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo de Dados: Físico Descreve detalhes de armazenamento de dados em memória I Formatos e ordenação de registros em arquivos I Organização dos dados em arquivos em memória secundária I Caminhos de acesso alternativos para recuperação rápida de registros PROFESSOR CPF Nome Sexo Salario Departamento # ÍNDICE Ponteiro Departamento 12345678900 Roberto Machado M 1200.00 1 00 #00 1 21345678900 Carlos A. Martins M 3200.00 1 10 #20 2 32145678900 Ana Maria Freitas F 7500.00 2 20 #30 3 12345678901 Manuela Costa F 2700.00 3 30 · · · · · · 52345678902 Luiz A. Barbosa M 5300.00 3 40 #FF N 97345671200 Rebeca Lins Rêgo F 6800.00 3 50 · · · · · · · · · · · · · · · · · · 68345618900 Amanda Ramirez F 1700.00 N FF www.wladmirbrandao.com 25 / 373 http://www.wladmirbrandao.com esquema e linguagem Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Esquema de BD Descrição do banco de dados (metadados) I Especificado no projeto e não muda com frequência I Existem convenções para se representar esquemas usando diagramas I Diagrama de Esquema→ representação de um esquema I Capta aspectos como restrições, tipos de registros e de itens de dados PROFESSOR CPF Nome Sexo Salario Departamento I Construtor de Esquema→ elemento que compõe o esquema, como por exemplo professor www.wladmirbrandao.com 27 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Esquema de BD O diagrama de esquema apresenta a estrutura de cada tipo de elemento, mas NÃO apresenta as instâncias dos elementos PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 www.wladmirbrandao.com 28 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Instância de BD Conjunto de dados armazenados em determinado momento I Estado Vazio→ esquema especificado, mas nenhum dado armazenado I Estado Inicial→ BD carregado (populado) com dados inicias I Estado se altera ao se inserir, remover ou modificar o valor de um item www.wladmirbrandao.com 29 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Arquitetura de Três Esquemas Abordagem que permite visualização do esquema em diferentes níveis I Autodescrição→metadados descritivos em diferentes níveis de abstração, de acordo com características estruturais I Suporte a Múltiplas Visões→ usuários e aplicações têm acesso restrito a porções do BD suficientes para atender suas necessidades I Independência de Aplicação→ estrutura do BD armazenada separadamente de aplicações, garantindo que alterações na estrutura não necessariamente levem a mudanças em aplicações www.wladmirbrandao.com 30 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Arquitetura de Três Esquemas www.wladmirbrandao.com 31 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Arquitetura de Três Esquemas I Nível Externo I Esquema Externo→ visões de usuário I Cada visão descreve a parte do BD em que um grupo de usuários está interessado, ocultando o restante I Implementado com modelo de dados representativo I Nível Conceitual I Esquema Conceitual→ estrutura do BD I Descrição de tipos de dados, entidades, relacionamentos, restrições e operações do usuário I Oculta detalhes de armazenamento físico I Nível Interno I Esquema Físico→ estrutura do armazenamento físico do BD I Descrição de detalhes de armazenamento de dados e de caminhos de acesso www.wladmirbrandao.com 32 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mirbra nda o.c om CC BY- NC -ND 4.0 Arquitetura de Três Esquemas Níveis apresentam descritores para dados que estão efetivamente armazenados em meio físico I Mapeamento→ transformação de requisições e resultados entre níveis I SGBD transforma uma solicitação especificada por usuários em uma solicitação no esquema conceitual e, em seguida, em uma solicitação no esquema interno para que o processamento de dados possa ser realizado www.wladmirbrandao.com 33 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Independência de Dados Capacidade de se alterar o esquema em um nível sem precisar alterar o esquema no nível adjacente superior I Lógica→ capacidade de alterar o esquema conceitual sem precisar alterar o esquema externo I Exemplo→ ao acrescentar ou remover um tipo de dado somente o mapeamento entre os níveis e a definição da visão são alterados I Física→ capacidade de alterar o esquema interno sem precisar alterar o esquema conceitual I Exemplo→ ao organizar arquivos físicos criando estruturas de acesso adicionais somente o mapeamento entre os níveis é alterado www.wladmirbrandao.com 34 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Independência de Dados A arquitetura de três esquemas facilita a independência de dados I Independência lógica é mais difícil de ser alcançada, uma vez que é mais difícil realizar alterações estruturais e de restrição sem afetar as aplicações I Poucos SGBDs comerciais implementam a arquitetura de três esquemas completamente por haver uma sobrecarga com os mapeamentos, levando a ineficiência www.wladmirbrandao.com 35 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Linguagens Abordagem de BD precisa oferecer linguagens e interfaces apropriadas para cada tipo de usuário I VDL→ linguagem de definição de visão que especifica o esquema externo, as visões de usuário e seus mapeamentos ao esquema conceitual I DDL→ linguagem de definição de dados que especifica o esquema conceitual I SDL→ linguagem de definição de armazenamento que especifica o esquema interno I DML→ linguagem de manipulação de dados utilizada para especificação de operações de inserção, exclusão, modificação e recuperação de dados www.wladmirbrandao.com 36 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Linguagens Se diferenciam quanto à forma como as operações são especificadas I Alto Nível→ não procedural I Especifica operações complexas de forma concisa I Pode recuperar muitos registros em uma única instrução I Declarativa→ especifica quais dados recuperar e não como I Denominada linguagem de consulta por ser usada de maneira interativa I Baixo Nível→ procedural I Embutida em linguagem de programação de uso geral (linguagem hospedeira), sendo assim denominada sublinguagem de dados I Recupera objetos ou registros individuais e os processa separadamente www.wladmirbrandao.com 37 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Linguagens SGBDs tipicamente não consideram as diferentes linguagens como distintas I SQL→ linguagem de consulta estruturada que combina VDL, DDL, SDL e DML, bem como instruções para especificação de restrição, evolução de esquema e outros recursos www.wladmirbrandao.com 38 / 373 http://www.wladmirbrandao.com modelo entidade-relacionamento Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo Entidade-Relacionamento (ER) Modelo conceitual elaborado a partir da especificação do minimundo I Minimundo→ tipicamente especificado de forma textual, estabelecendo os requisitos de dados I Diagrama Entidade-Relacionamento→ representação gráfica de entidades, atributos, relacionamentos e restrições do modelo ER Cliente Nome Rua Cidade Possui Conta Número Saldo 1 N www.wladmirbrandao.com 40 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Entidade Ente com existência real no minimundo especificado I Seja E = {e1,e2, · · · ,ek } um conjunto de k entidades de mesmo tipo I Tipo de Entidade (E)→ conjunto de instâncias de entidades do mesmo tipo I Instância de Entidade (ei )→ ente específico de um tipo de entidade E , tal que ei ∈ E e1 e2 ek E www.wladmirbrandao.com 41 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Entidade No diagrama ER representa-se um tipo de entidade, ou simplesmente entidade, como um retângulo rotulado Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 42 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Entidade Entidade Fraca→ entidade que existência depende da existência de outra FuncionárioCPF Nome Sexo Salário possui Dependente Nome Parentesco 1 N www.wladmirbrandao.com 43 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Propriedade que descreve uma característica específica de uma entidade Representa-se como uma elipse rotulada e ligada à entidade que ele caracteriza Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 44 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Simples→ indivisível, representado por uma elipse simples rotulada Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 45 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Composto→ desmembra-se em outros atributos, representado por uma elipse simples rotulada com outros atributos ligados a ele Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 46 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Multivalorado→ conteúdo formado por mais de um valor, representado por uma elipse rotulada com borda dupla Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 47 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Derivado→ valor obtido a partir de valores de outros atributos ou relacionamen- tos, representado por uma elipse rotulada com borda tracejada Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 48 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Chave→ atributo ou conjunto de atributos que juntos identificam cada instância de entidade de maneira exclusiva, representado por uma elipse simples rotulada com rótulo sublinhado Funcionário CPF Nome Primeiro Sobrenome Sexo SalárioData Nascimento Dependentes Idade www.wladmirbrandao.com 49 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Atributo Chave Parcial→ ou discriminador, atributo ou conjunto de atributos que juntos potencialmente identificam cada instância de entidade fraca de maneira exclu- siva, representado por uma elipse simples rotulada com rótulo sublinhado de ma- neira tracejada DependenteNome Parentesco www.wladmirbrandao.com 50 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento Associação entre entidades I Seja R = {r1,r2, · · · ,rk } um conjunto de k associações entre entidades I Tipo de Relacionamento (R)→ conjunto de instâncias de associações do mesmo tipo I Instância de Relacionamento (ri )→ associação específica entre instâncias de entidades, tal que ri ∈ R e1 e2 e3 e4 E r1 r2 r3 r4 R e1 e2 e3 e4 E www.wladmirbrandao.com 51 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento No diagrama ER representa-se um tipo de relacionamento, ou simplesmente rela- cionamento, como um losango rotulado Funcionário CPF Nome Primeiro Sobrenome Sexo Salário Data Nascimento Dependentes Idade possui Departamento Nome Locais Qtd. Func. www.wladmirbrandao.com 52 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento Relacionamento Fraco→ ou de dependência, associação envolvendo ao menos uma entidade fraca, representado por um losango rotulado com borda dupla FuncionárioCPF Nome Sexo Salário possui Dependente Nome Parentesco www.wladmirbrandao.com 53 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento Cada relacionamento ri ∈ R é uma associação entre entidades que inclui exata- mente uma única instância de cada entidade participante d1 d2 d3 Departamento r1 r2 r3 possui f1 f2 f3 Funcionário Grau do Relacionamento→ # entidades participantes no relacionamento I Binário→ grau 2 I Ternário→ grau 3 www.wladmirbrandao.com 54 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento Nome de Função→ rotula o relacionamento e representa a função que uma enti- dade desempenha em cada relacionamento I Enriquece a semântica do relacionamento Funcionário CPF Nome Sexo Salário Data Nascimento Idade possui Departamento Nome Locais No relacionamento possui, Funcionário desempenha a função possuído, en- quanto Departamento desempenha a função possuidor www.wladmirbrandao.com 55 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Relacionamento Relacionamento Recursivo→ a mesma entidade participa mais de uma vez, com funções diferentes, em um relacionamento Funcionário CPF Nome Sexo supervisiona No exemplo, Funcionário participa com as funções de supervisor e supervisio- nado no relacionamento supervisiona www.wladmirbrandao.com 56 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Característica limitadora da possibilidade de associação entre entidades nos relacionamentos I Razão de Participação→ especifica se a participação de uma entidade no relacionamento é parcial ou total I Razão de Cardinalidade→ especifica o número máximo de relacionamentos em que uma entidade pode participar, opcionalmente indica limites mínimos Funcionário CPF Nome Sexo possui Departamento Nome Locais N 1(0, N) (1, 1) www.wladmirbrandao.com 57 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Participação Total→ todas as instâncias da entidade devem obrigatoriamente participar de relacionamentos d1 d2 d3 Departamento r1 r2 r3 possui f1 f2 f3 Funcionário Representa-se por uma linha dupla entre a entidade e o relacionamento Funcionário possui Departamento www.wladmirbrandao.com 58 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Cardinalidade 1:1 → uma instância de cada entidade só pode participar de um único relacionamento d1 d2 d3 Departamento r1 r2 r3 possui f1 f2 f3 Funcionário Representa-se por rótulos 1 nas duas extremidades do relacionamento Funcionário possui Departamento 1 1 www.wladmirbrandao.com 59 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Cardinalidade 1:N → uma instância de uma entidade só pode participar de um relacionamento, enquanto uma instância da outra pode participar de múltiplos d1 d2 d3 Departamento r1 r2 r3 possui f1 f2 f3 Funcionário Representa-se por rótulos 1 em uma extremidade e N na outra Funcionário possui Departamento N 1 www.wladmirbrandao.com 60 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Cardinalidade N:N → uma instância de qualquer entidade pode participar de múltiplos relacionamentos d1 d2 d3 Departamento r1 r2 r3 possui f1 f2 f3 Funcionário Representa-se por rótulos N nas duas extremidades do relacionamento Funcionário possui Departamento N N www.wladmirbrandao.com 61 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Restrição Mínimos e Máximos→ opcionalmente pode-se definir limites mínimos e máximos de cardinalidade para os relacionamentos Representa-se por rótulos (min, max) nas duas extremidades do relacionamento Funcionário possui Departamento (0, 20) (0, 1) www.wladmirbrandao.com 62 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Convenção de Nomes Elementos do modelo ER referenciam elementos textuais em uma especificação textual de minimundo I Substantivo→ pode indicar entidade ou atributo I Verbo→ pode indicar relacionamento Para a construção do diagrama ER deve-se adotar uma convenção, por exemplo: I Entidade→ nome no singular com letra inicial em maiúscula I Relacionamento→ nome no singular com todas as letras minúsculas I Atributo→ nome com as letras iniciais em maiúscula e atributo multivalorado com nome no plural www.wladmirbrandao.com 63 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Resumo de Elementos Rótulo ENTIDADE (FORTE) Rótulo ENTIDADE FRACA rótulo RELACIONAMENTO rótulo RELACIONAMENTO FRACO Rótulo ATRIBUTO Rótulo ATRIBUTO CHAVE Rótulo CHAVE PARCIAL Rótulo ATRIBUTO MULTIVALORADO Rótulo ATRIBUTO DERIVADO Rótulo ATRIBUTO COMPOSTO Rótulo Rótulo www.wladmirbrandao.com 64 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 ER: Resumo de Elementos E1 R PARTICIPAÇÃO PARCIAL DE E1 EM R E1 R PARTICIPAÇÃO TOTAL DE E1 EM R E1 R E2 1 N CARDINALIDADE 1:N DE E1 PARA E2 E1 R (0, 5) LIMITES DE CARDINALIDADE DE E1 EM R www.wladmirbrandao.com 65 / 373 http://www.wladmirbrandao.com modelo er estendido Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo ER Estendido (EER) Modelo entidade-relacionamento (ER) aprimorado, incorporando conceitos adici- onais de modelagem semântica de dados I Acrônimo do inglês para Enhanced Entity-Relationship I Apresenta requisitos mais complexos e precisos I Herança I Supertipo e subtipo I Restrições complexas I Generalização e especialização www.wladmirbrandao.com 67 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Supertipos e Subtipos Entidades podem possuir subtipos que precisam de representação explícita Subtipo ou subclasse são subagrupamentos de uma entidade denominada su- pertipo ou superclasse I O relacionamento entre essas entidadesé denominado relacionamento de supertipo/subtipo, superclasse/subclasse ou classe/subclasse Professor I Doutor I Mestre I Bacharel I Mensalista I Horista Professor (supertipo) I Doutor (subtipo) I Mestre (subtipo) I Bacharel (subtipo) I Mensalista (subtipo) I Horista (subtipo) www.wladmirbrandao.com 68 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Supertipos e Subtipos Professor CPF Nome Primeiro Último Meio Endereço Data NascimentodMestre Dissertação DoutorTese Bacharel Monografia Coordenador gerencia Departamento www.wladmirbrandao.com 69 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Herança de Tipo Ocorre em situações em que a entidade de uma subclasse herda todos os atribu- tos e relacionamentos da classe I Uma entidade na subclasse possui atributos específicos, assim como valores de atributos da classe I Exemplo→ subclasse Doutor possui atributo próprio Tese e vários outros atributos herdados da superclasse Professor, como CPF www.wladmirbrandao.com 70 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Especialização Definição de um conjunto de subclasses de uma entidade com base em alguma característica específica Caso 1→ Titulação Professor I Doutor I Mestre I Bacharel Caso 2→ Forma de remuneração Professor I Mensalista I Horista www.wladmirbrandao.com 71 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Especialização Professor CPF Nome Primeiro Último Meio Endereço Data NascimentodMestre Dissertação DoutorTese Bacharel Monografia Coordenador gerencia Departamento www.wladmirbrandao.com 72 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Especialização Definição por Condição→ entidades que serão subclasse são definidas a partir de uma condição aplicada a um atributo Professor CPF Nome Primeiro Último Meio Endereço Data Nascimento Titulação d Titulação Mestre Dissertação DoutorTese Bacharel Monografia Coordenador gerencia Departamento www.wladmirbrandao.com 73 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Generalização Definição de um tipo de entidade geral com base em entidades específicas I Identifica-se características comuns e generaliza-se em uma classe Carro Placa Velocidade Chassi Preço Passageiros Caminhão Placa Eixos Capacidade Preço Chassi Carro e Caminhão possuem vários atributos comuns www.wladmirbrandao.com 74 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Generalização Carro e Caminhão podem ser generalizadas, passando a ser subclasses da classe generalizada Veículo CarroPassageiros Velocidade d Caminhão Eixos Capacidade VeículoChassi Preço Placa www.wladmirbrandao.com 75 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Restrição Característica limitadora da participação de entidades em subclasses Disjunção→ uma entidade pode ser membro de no máximo uma das subclasses I Uma especialização definida por um atributo de valor único implica em uma restrição de disjunção MensalistaSalário Regime d Horista Valor Hora ProfessorCPF Nome www.wladmirbrandao.com 76 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Restrição Sobreposição→ uma entidade pode ser membro de mais de uma subclasse Doutor Tese o Mestre Dissertação ProfessorCPF Nome www.wladmirbrandao.com 77 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Restrição Participação→ determina a participação de uma entidade em subclasses I Especialização Parcial→ entidade não precisa ser membro de subclasses I Especialização Total→ toda entidade precisa ser membro de pelo menos uma subclasse na especialização MensalistaSalário Regime d Horista Valor Hora ProfessorCPF Nome www.wladmirbrandao.com 78 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Reticulado e Herança Uma subclasse pode ser superclasse de outras subclasses, formando um reticu- lado de especializações I Hierarquia Estrita→ cada subclasse tem apenas uma superclasse, resultando em uma estrutura de árvore I Reticulado→ cada subclasse pode pertencer a diferentes superclasses, resultando em uma estrutura emaranhada complexa Subclasses podem herdar atributos e relacionamentos de múltiplas classes I Herança Simples→ herança de uma única classe I Herança Múltipla→ herança de múltiplas classes www.wladmirbrandao.com 79 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Especialização x Generalização Especialização→ processo de refinamento conceitual de cima para baixo (top- down) em que se inicia com uma entidade e depois são definidas subclasses pela especialização sucessiva Generalização→ processo de refinamento conceitual de baixo para cima (bottom- up) em que pela síntese conceitual é possível se chegar a mesma hierarquia ou reticulado da alcançada pela outra direção Estruturalmente o resultado de ambos os processos são idênticos www.wladmirbrandao.com 80 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: União Subclasse representa uma coleção de entidades, um subconjunto da união de entidades distintas Exemplo→ Proprietário pode ser um Banco, uma Empresa ou uma Pessoa Proprietário (união) I Banco (entidade) I Pessoa (entidade) I Empresa (entidade) Proprietário herda atributos e relacionamentos de Banco, Empresa e Pessoa www.wladmirbrandao.com 81 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: União BancoNome Endereço u Empresa Nome Endereço CNPJ Pessoa CNH Nome CPF Endereço Proprietário www.wladmirbrandao.com 82 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Projeto Conceitual Em um projeto conceitual há um processo de refinamento iterativo até que o pro- jeto mais adequado seja alcançado Existem diretrizes para direcionar escolhas em projetos: 1. Representar apenas subclasses necessárias para evitar aglomeração do modelo conceitual 2. Subclasse com poucos atributos são candidatas à mesclagem com a superclasse I Atributos específicos da subclasse teriam valores NULL para entidades não membros da subclasse I Atributo de tipo pode especificar se uma entidade é um membro da subclasse www.wladmirbrandao.com 83 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 EER: Projeto Conceitual 3 União deve ser evitada, a menos que a situação definitivamente justifique esse tipo de construção 4 A escolha de restrições de disjunção, sobreposição e totalidade sobre a especialização deve ser regida pelas regras do minimundo I Se na especificação do minimundo não é explicitamente citada nenhuma restrição, o padrão menos restritivo de sobreposição parcial deve ser adotado www.wladmirbrandao.com 84 / 373 http://www.wladmirbrandao.com modelo relacional Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo Relacional Modelo de implementação (representativo) baseado no paradigma relacional I Dadossão organizados de maneira tabular: I Entidade→ Relação I Relacionamento→ Relacionamento I Atributo→ Atributo I Registro→ Tupla I Pode ser criado a partir do EER utilizando um procedimento em 7 etapas www.wladmirbrandao.com 86 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Diagrama EER: Sistema de Matrícula Área Sigla Nome integrapossui Curso Sigla Nome Horas Custo Professores CPF Nome possuimatricula Data Pago Aluno CPF Nome Data Nascimento Sexo Primeiro Nome Sobrenome Módulo Sigla Nome possui TópicoSigla Nome Horas 1 N 1 N 1 NN N 1 N www.wladmirbrandao.com 87 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 1→ Entidades Fortes I Crie uma relação para cada entidade forte e inclua todos os atributos simples I Inclua apenas atributos simples de um atributo composto I Escolha um dos atributos chave da entidade forte como chave primária da nova relação I Se a chave escolhida for composta, o conjunto de atributos simples que a compõe formarão a chave primária www.wladmirbrandao.com 88 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 1→ Entidades Fortes CURSO Sigla Nome Horas Custo AREA Sigla Nome MODULO Sigla Nome ALUNO CPF Nome Sobrenome Sexo DataNasc www.wladmirbrandao.com 89 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 2→ Entidades Fracas I Crie uma relação para cada entidade fraca e inclua todos os atributos simples I Inclua como atributos de chave estrangeira da relação, os atributos de chave primária da relação que corresponde à entidade proprietária I Escolha a chave estrangeira e um atributo chave parcial como chave primária da nova relação www.wladmirbrandao.com 90 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 2→ Entidades Fracas CURSO Sigla Nome Horas Custo AREA Sigla Nome MODULO Sigla Nome TOPICO Modulo Sigla Nome Horas ALUNO CPF Nome Sobrenome Sexo DataNasc www.wladmirbrandao.com 91 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 3→ Relacionamentos Binários 1:N I Identifique a relação R1 que representa a entidade participante no lado N do relacionamento I Inclua como chave estrangeira em R1 a chave primária de R2, que representa a outra entidade participante do relacionamento www.wladmirbrandao.com 92 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 3→ Relacionamentos Binários 1:N CURSO Sigla Nome Horas Custo Area AREA Sigla Nome SuperArea MODULO Sigla Nome Curso TOPICO Modulo Sigla Nome Horas ALUNO CPF Nome Sobrenome Sexo DataNasc www.wladmirbrandao.com 93 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 4→ Relacionamentos Binários N:N I Crie uma nova relação R3 para cada relacionamento N:N I Inclua como chave estrangeira em R3 as chaves primárias das relações R1 e R2, que representam as entidades participantes no relacionamento I A chave primária de R3 será formada pela combinação das chaves estrangeiras em R3 www.wladmirbrandao.com 94 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 4→ Relacionamentos Binários N:N CURSO Sigla Nome Horas Custo Area AREA Sigla Nome SuperArea MODULO Sigla Nome Curso TOPICO Modulo Sigla Nome Horas MATRICULA Curso Aluno Data Pago ALUNO CPF Nome Sobrenome Sexo DataNasc www.wladmirbrandao.com 95 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 5→ Relacionamentos Binários 1:1 I Identifique as relações que correspondem às entidades participantes I Existem três estratégias: 1. Mesclagem→ consiste em mesclar as entidades e o relacionamento em uma única relação 2. Chave Estrangeira→ consiste em mapear o relacionamento 1:1 como um relacionamento 1:N 3. Referência Cruzada→ consiste em mapear o relacionamento 1:1 como um relacionamento N:N www.wladmirbrandao.com 96 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 6→ Atributos Multivalorados I Crie uma nova relação para cada atributo multivalorado A I A nova relação incluirá um atributo de A, mais o atributo da chave primária da relação que representa a entidade ou relacionamento que tenha A como atributo multivalorado www.wladmirbrandao.com 97 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 6→ Atributos Multivalorados CURSO Sigla Nome Horas Custo Area AREA Sigla Nome SuperArea MODULO Sigla Nome Curso TOPICO Modulo Sigla Nome Horas MATRICULA Curso Aluno Data Pago ALUNO CPF Nome Sobrenome Sexo DataNasc PROFESSOR Curso CPF Nome www.wladmirbrandao.com 98 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Mapeamento EER Etapa 7→ Relacionamentos de Alto Grau I Crie uma relação R3 para cada relacionamento n-ário, em que n > 2 I Inclua como chave estrangeira em R3 as chaves primárias das relações que representam as entidades participantes I A chave primária de R3 é a combinação de todas as chaves estrangeiras que referenciam as relações das entidades participantes www.wladmirbrandao.com 99 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Modelo Relacional: Sistema de Matrícula CURSO Sigla Nome Horas Custo Area AREA Sigla Nome SuperArea MODULO Sigla Nome Curso TOPICO Modulo Sigla Nome Horas MATRICULA Curso Aluno Data Pago ALUNO CPF Nome Sobrenome Sexo DataNasc PROFESSOR Curso CPF Nome www.wladmirbrandao.com 100 / 373 http://www.wladmirbrandao.com álgebra relacional i Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Álgebra Relacional Conjunto de operações para manipulação de BD relacionais I Linguagem formal para o modelo relacional I Consulta→ solicitação de recuperação de dados especificada por uma sequência de operações algébricas I Resultado de uma consulta é uma nova relação I SQL incorpora seus principais conceitos I SQL→ linguagem de consulta prática para BD relacionais I Multiplicidade de operações: I Unárias→ aplicadas sobre uma relação I Binárias→ aplicadas sobre duas relações I Agregação→ resumem dados de relações www.wladmirbrandao.com 102 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação SELEÇÃO ãcondicional(R) I Operador→ selecionar (ã sigma) I Função→ filtrar tuplas de uma relação R que satisfaçam a uma condição I Tuplas que não satisfazem a condição são descartadas do resultado I Relação resultante tem os mesmos atributos de R I Número de tuplas na relação resultante é menor ou igual ao número de tuplas em R www.wladmirbrandao.com 103 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação SELEÇÃO Condição de seleção é uma expressão booleana <atributo> <operador> <valor> | <atributo> <operador> <atributo> I <atributo> → nome de um atributo de R I <operador> → operador de comparação < ≤ = ≥ > , I <valor> → constante dodomínio do atributo Conectada por operadores booleanos (∧ ∨ ¬) formam um único bloco condicio- nal www.wladmirbrandao.com 104 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação SELEÇÃO Exemplo→ selecionar tuplas de professores do sexo feminino ã Sexo = ′F ′ (PROFESSOR) PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 www.wladmirbrandao.com 105 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação SELEÇÃO Exemplo→ selecionar tuplas de professores do sexo masculino que recebem sa- lário maior que 3000,00 ã (Sexo = ′M ′ ∧ Salario > 3000.00) (PROFESSOR) PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 www.wladmirbrandao.com 106 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação SELEÇÃO I Condições de seleção são aplicadas a cada tupla individualmente e não se aplicam a mais de uma tupla I Operação unária e comutativa ãcond1(ãcond2(R)) = ãcond2(ãcond1(R)) I Pode-se combinar uma sequência de operações em uma única operação com operadores conjuntivos ãcond1(ãcond2(... (ãcondn(R))...)) = ãcond1 ∧ cond2 ∧ ... ∧ condn(R) www.wladmirbrandao.com 107 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PROJEÇÃO áatributos(R) I Operador→ projetar (á pi ) I Função→ filtrar atributos de uma relação R I Atributos não especificados são descartados do resultado I Relação resultante possui um subconjunto de atributos de R explicitamente especificados e na mesma ordem I Número de tuplas na relação resultante é menor ou igual ao número de tuplas em R I Menor se houverem tuplas duplicadas, pois duplicatas são eliminadas www.wladmirbrandao.com 108 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PROJEÇÃO Exemplo→ projetar nome e salário de professores á Nome , Salario (PROFESSOR) PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 www.wladmirbrandao.com 109 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PROJEÇÃO Exemplo→ projetar número do departamento e sexo de professores á Departamento , Sexo (PROFESSOR) PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 Duas tuplas repetidas (< 1, M >) no resultado, uma delas será eliminada www.wladmirbrandao.com 110 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PROJEÇÃO I Se a lista de atributos para projeção inclui a chave da relação R , a relação resultante terá o mesmo número de tuplas de R I Operação unária, mas não comutativa áatr1(áatr2(R)) , áatr2(áatr1(R)) I Aninhamento de sequência de operações válidas equivale à operação externa do aninhamento áatr1(áatr2(R)) = áatr1(R) www.wladmirbrandao.com 111 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Sequência de Operações Consultas combinam sequências de operações algébricas I Expressão em Linha→ aninha-se múltiplas operações, gerando uma única expressão algébrica á CPF , Nome , Salario ( ãSexo = ′F ′ (PROFESSOR) ) I Relacões Intermediárias→ aplica-se uma operação de cada vez, criando relações com resultados intermediários reutilizáveis A ←− ãSexo = ′F ′ (PROFESSOR) B ←− á CPF , Nome , Salario (A) www.wladmirbrandao.com 112 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação RENOMEAR âS(atributos)(R) I Operador→ renomear (â rho) I Função→ renomear relações e atributos I S → nome da relação resultante I atributos → lista dos novos nomes dos atributos de R na relação resultante, ordem na lista deve ser compatível com ordem dos atributos de R I Variações: I âatributos(R)→ renomeia apenas atributos I âS (R)→ renomeia apenas relação www.wladmirbrandao.com 113 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação RENOMEAR Exemplo→ renomear a relação professor e seus respectivos atributos âTEACHER(CPF ,Name ,Gender ,Salary ,DNum)(PROFESSOR) TEACHER CPF Name Gender Salary DNum 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 www.wladmirbrandao.com 114 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação RENOMEAR Alternativamente, pode-se renomear relações e atributos utilizando relações intermediárias TEACHER(CPF , Name , Gender , Salary , DNum)←− PROFESSOR TEACHER(Name , Salary)←− á Nome , Salario (PROFESSOR) MAN(CPF , Nom , Sex , Sal , DNum)←− ã Sexo = ′M ′ (PROFESSOR) Pode-se renomear qualquer subconjunto de atributos de R www.wladmirbrandao.com 115 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PRODUTO R1 ×R2 I Operador→ multiplicar (×) I Função→ combinar tuplas de duas relações I Relação resultante possui os atributos de R1 e R2, incluindo como tuplas todas as combinações possíveis entre as tuplas de R1 e R2 I Número de tuplas da relação resultante é o produto cartesiano entre o número de tuplas de R1 e R2 www.wladmirbrandao.com 116 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PRODUTO Exemplo→ PROFESSOR ×DEPARTAMENTO PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia Resultado: CPF Nome Sexo Salario Departamento Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678900 Roberto M 1200.00 1 2 Comercial 12345678900 Roberto M 1200.00 1 3 Tecnologia 12345678901 Manuela F 2700.00 3 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 12345678901 Manuela F 2700.00 3 3 Tecnologia www.wladmirbrandao.com 117 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PRODUTO Associada à SELEÇÃO opera como uma JUNÇÃO ã Departamento = Numero (PROFESSOR ×DEPARTAMENTO) PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia Resultado: CPF Nome Sexo Salario Departamento Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 3 Tecnologia www.wladmirbrandao.com 118 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO R1 Zcondicional R2 I Operador→ juntar (Z) I Função→ combinar tuplas de duas relações a partir de uma condição I Relação resultante possui atributos de R1 e R2, incluindo como tuplas todas as combinações entre as tuplas deR1 e R2 que respeitam condição I Tuplas que não respeitam condição de junção ou que valores dos atributos usados na condição sejam NULL são descartadas do resultado www.wladmirbrandao.com 119 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Exemplo→ PROFESSOR Z Departamento = Numero DEPARTAMENTO PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia Resultado: CPF Nome Sexo Salario Departamento Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 3 Tecnologia www.wladmirbrandao.com 120 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Equijunção→ condicionais com operadores de igualdade PROFESSOR Z Departamento = Numero DEPARTAMENTO Junção Natural→ equijunção automática (natural) com atributos que possuem o mesmo nome nas duas relações removendo-se duplicatas PROFESSOR ∗DEPARTAMENTO PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia www.wladmirbrandao.com 121 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Frequentemente relações diferentes possuem atributos com mesmo nome I Operação RENOMEAR deve ser utilizada antes da junção para evitar problemas de ambiguidade Exemplo: A(Numero , DNome)←− á Numero , Nome (DEPARTAMENTO) RESULTADO ←− PROFESSOR Z Departamento = Numero A RESULTADO CPF Nome Sexo Salario Departamento Numero DNome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 3 Tecnologia www.wladmirbrandao.com 122 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação AGREGAÇÃO atributos Õfuncoes(R) I Operador→ agregar (Õ gamma) I Função→ agregar tuplas de uma relação a partir de uma lista de atributos (atributos de agregação), aplicando funções de agregação em atributos remanescentes I Múltiplas notações→ F , G I Relação resultante possui atributos de agregação e um atributo para cada função de agregação I Tuplas com valores NULL nos atributos usados na função de agregação são descartadas da agregação www.wladmirbrandao.com 123 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação AGREGAÇÃO Função de Agregação→ função matemática aplicada em tuplas agrupadas I COUNT→ conta o número de tuplas agrupadas I SUM→ soma valores do atributo utilizado na função I AVG→ calcula média dos valores do atributo utilizado na função I MIN→ captura valor mínimo dentre valores do atributo utilizado na função I MAX→ captura valor máximo dentre valores do atributo utilizado na função Se função de agregação não for renomeada, nome do atributo resultante será concatenação do nome da função e do nome do atributo usado por ela www.wladmirbrandao.com 124 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação AGREGAÇÃO Exemplo→ apresentar o número de professores e o total em salários PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 Õ COUNT(CPF), SUM(Salario) (PROFESSOR) Resultado: COUNT_CPF SUM_SALARIO 4 14600.00 www.wladmirbrandao.com 125 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação AGREGAÇÃO Exemplo→ apresentar o total e a média salarial por sexo PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 Sexo Õ SUM(Salario), AVG(Salario) (PROFESSOR) Resultado: Sexo SUM_SALARIO AVG_SALARIO F 10200.00 5100.00 M 4400.00 2200.00 www.wladmirbrandao.com 126 / 373 http://www.wladmirbrandao.com álgebra relacional ii Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação PROJEÇÃO Projeção Generalizada→ estende operação de projeção permitindo que funções sejam incluídas na lista de atributos para projeção áfuncoes(R) Funções envolvem operações aritméticas e valores constantes Exemplo: A ← áCPF , Nome + ′ ′ + Sobrenome , Salario ∗ 1.1(PROFESSOR) B ← âCPF , NomeCompleto , Bonus(A) www.wladmirbrandao.com 128 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Junção Interna (INNER JOIN)→ operação de junção convencional (JOIN) Exemplo→ PROFESSOR Z Departamento = Numero DEPARTAMENTO PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia Resultado: CPF Nome Sexo Salario Departamento Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 3 Tecnologia www.wladmirbrandao.com 129 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Junção Externa (OUTER JOIN)→ a "relação externa" participa com tuplas não correspondentes da junção interna Exemplo→ PROFESSOR n Departamento = Numero DEPARTAMENTO PROFESSOR CPF Nome Sexo Salario Departamento DEPARTAMENTO Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 2 Comercial 3 Tecnologia Resultado: CPF Nome Sexo Salario Departamento Numero Nome 12345678900 Roberto M 1200.00 1 1 Administrativo 12345678901 Manuela F 2700.00 3 3 Tecnologia 2 Comercial www.wladmirbrandao.com 130 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação JUNÇÃO Junção Externa à Esquerda (LEFT OUTER JOIN)→ junção externa em que a "relação externa" é a da esquerda PROFESSOR o Departamento = Numero DEPARTAMENTO Junção Externa à Direita (RIGHT OUTER JOIN)→ junção externa em que a "re- lação externa" é a da direita PROFESSOR n Departamento = Numero DEPARTAMENTO Junção Externa Completa (FULL OUTER JOIN)→ junção externa em que ambas as relações são "externas" PROFESSOR [ Departamento = Numero DEPARTAMENTO www.wladmirbrandao.com 131 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operações de Conjunto Operações da teoria dos conjuntos usadas para mesclar elementos de dois conjuntos, através de operações binárias I União→ adiciona todas as tuplas de ambas as relações I Interseção→ adiciona as tuplas comuns entre as relações I Diferença→ adiciona as tuplas da primeira relação que não pertencem à segunda relação Relações devem ser compatíveis, possuindo o mesmo número de atributos ali- nhados de acordo com o domínio de dados Tuplas duplicadas são eliminadas da relação resultante Nomes dos atributos da primeira relação são mantidos na relação resultante www.wladmirbrandao.com 132 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operações UNIÃO R1∪R2 I Operador→ unir (∪) I Função→ unir tuplas de duas relações I Operação comutativa R1∪R2 = R2∪R1 Exemplo→ PROFESSOR ∪ALUNO PROFESSOR Nome Depto ALUNO Nome Departamento Roberto Machado 1 Roberto Machado 2 Manuela Costa 3 Manuela Costa 3 UNIÃO Nome Depto Roberto Machado 1 Manuela Costa3 Roberto Machado 2 www.wladmirbrandao.com 133 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operações INTERSEÇÃO R1∩R2 I Operador→ intersecionar (∩) I Função→ selecionar tuplas comuns nas duas relações I Operação comutativa R1∩R2 = R2∩R1 Exemplo→ PROFESSOR ∩ALUNO PROFESSOR Nome Depto ALUNO Nome Departamento Roberto Machado 1 Roberto Machado 2 Manuela Costa 3 Manuela Costa 3 INTERSEÇÃO Nome Depto Manuela Costa 3 www.wladmirbrandao.com 134 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operações DIFERENÇA R1 −R2 I Operador→ menos (−) I Função→ selecionar tuplas da primeira relação não contidas na segunda I Operação não comutativa R1 −R2 , R2 −R1 Exemplo→ PROFESSOR −ALUNO PROFESSOR Nome Depto ALUNO Nome Departamento Roberto Machado 1 Roberto Machado 2 Manuela Costa 3 Manuela Costa 3 DIFERENÇA Nome Depto Roberto Machado 1 www.wladmirbrandao.com 135 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operações DIVISÃO R1 ÷R2 I Operador→ dividir (÷) I Função→ extrair subconjunto de tuplas de R1 presente em R2 I Relação resultante possui os atributos de R1 não presentes em R2 I Relação resultante possui as tuplas de R1 que contêm as tuplas de R2 I Operação não comutativa R1 ÷R2 , R2 ÷R1 www.wladmirbrandao.com 136 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Operação DIVISÃO Exemplo→ CARGA ÷ EXATAS Encontrar os professores que possuem cargas horárias em todos os cursos da área de ciências exatas CARGA Professor Curso EXATAS Curso Felipe Administração Computação Max Administração Engenharia Felipe Computação Max Computação Teldo Computação Luiz Engenharia Max Engenharia DIVISÃO Professor Max www.wladmirbrandao.com 137 / 373 http://www.wladmirbrandao.com sql: definição de dados Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL StructuredQuery Language I Linguagem de consulta estruturada I Linguagem declarativa de alto nível I Usuário especifica o que deseja, deixando decisões sobre como executar a consulta para o SGBD I Contém instruções para definição e manipulação de dados I Padrão em SGBDs relacionais comerciais I Mantém equivalência com o modelo relacional I Relação→ Tabela I Tupla→ Linha I Atributo→ Coluna www.wladmirbrandao.com 139 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Recursos SQL oferece múltiplos recursos I Definição de visões sobre dados I Definição de restrições sobre os dados I Especificação de controles de transações I Especificação de autorizações e segurança www.wladmirbrandao.com 140 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Definição de Dados Existem diferentes instruções (comandos) para definição de dados I CREATE→ cria elementos no catálogo, como esquemas, tabelas e domínios I ALTER→modifica elementos no catálogo I DROP→ remove elementos do catálogo www.wladmirbrandao.com 141 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Definição de Dados Esquema→ elemento que agrupa outros elementos que pertencem à mesma aplicação de BD I Nome→ identificador do esquema I Proprietário→ usuário com autoridade sobre o esquema Exemplo→ criar esquema UNIVERSIDADE pertencente ao usuário ’Pedro’ CREATE SCHEMA UNIVERSIDADE AUTHORIZATION ’Pedro’; www.wladmirbrandao.com 142 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Definição de Dados Catálogo→ coleção nomeada de esquemas I Contém o esquema padrão que oferece informação sobre todos os esquemas no catálogo, bem como sobre os descritores dos elementos I Esquema padrão→ INFORMATION_SCHEMA Tabela Base→ declarada por meio da instrução CREATE TABLE I Tabela realmente criada e armazenada como um arquivo pelo SGBD Tabela Virtual→ declarada por meio da instrução CREATE VIEW I Tabela pode ser criada e armazenada como um arquivo pelo SGBD I Geralmente não são realmente armazenados em arquivo www.wladmirbrandao.com 143 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Definição de Dados CREATE TABLE I Cria uma nova tabela, dando-lhe um nome e especificando suas colunas e restrições iniciais I Restrições de tipo são geralmente especificadas I Uma vez que colunas e suas respectivas restrições de tipo são criadas, podem ser redefinidas a partir da instrução ALTER TABLE Exemplo: CREATE TABLE PROFESSOR; Esquema em que as tabelas são criadas é especificado implicitamente no ambi- ente em que as instruções CREATE TABLE são executadas www.wladmirbrandao.com 144 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrições Restrições podem ser especificadas em SQL como parte da criação de tabela I Tipo→ domínio de valores válidos para a coluna I Nulidade→ possibilidade de valor NULL em coluna I Valor→ faixa de valores válidas para uma coluna I Valor Padrão→ valor atribuído a uma coluna caso nenhum valor seja especificado I Chave→ coluna(s) identificadora(s) de uma instância I Unicidade→ coluna(s) candidata(s) a identificadora(s) de uma instância I Integridade Referencial→ regras para atualização de linhas correlacionadas em diferentes tabelas www.wladmirbrandao.com 145 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Tipo Numérico→ incluem números inteiros de vários tamanhos (INTEGER e SMAL- LINT) e números de ponto flutuante (reais) de várias posições (FLOAT, REAL e DOUBLE PRECISION) Cadeias de Caracteres→ incluem cadeias de caracteres de diferentes tipos I CHAR(n)→ cadeias de tamanho fixo, onde n é a quantidade exata de caracteres a ser armazenada I VARCHAR(n)→ cadeias de tamanho variável, onde n é a quantidade máxima de caracteres armazenados I Valor literal da cadeia de caracteres deve ser especificado entre aspas simples, com maiúsculas diferenciadas de minúsculas (case sensitive) www.wladmirbrandao.com 146 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Tipo Cadeias de Bits→ incluem cadeias binárias de diferentes tipos I BIT(n)→ cadeias de tamanho fixo, onde n é a quantidade exata de bits a ser armazenada I BIT VARYING(n)→ cadeias de tamanho variável, onde n é a quantidade máxima de bits armazenados I O valor literal da cadeia de bits deve ser especificado entre apóstrofos, precedidos por um B para distingui-los das cadeias de caracteres I Exemplo→ B’10101’ Booleano→ valores binários verdadeiro (1) e falso (0) www.wladmirbrandao.com 147 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Tipo Date & Time→ valores de data e hora I Date→ dez posições compostas de dia, mês e ano na forma DD-MM-YYYY I Time→ oito posições compostas de hora, minuto e segundo na forma HH:MM:SS Timestamp→ valores temporais de alta precisão I Inclui os campos DATE e TIME, mais um mínimo de seis posições para frações decimais de segundos e um qualificador opcional WITH TIME ZONE I Valores literais representados por cadeias entre apóstrofos precedidos pela palavra-chave TIMESTAMP na forma TIMESTAMP ’27-09-2008 09:12:47.648302’ www.wladmirbrandao.com 148 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.wlad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Nulidade Pode ser especificada se valor NULL não for permitido para determinada coluna I Implícito para colunas que fazem parte da chave primária Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT ); www.wladmirbrandao.com 149 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Valor Padrão Define valor padrão para uma coluna I Valor padrão será incluído em qualquer nova linha se um valor explícito não for fornecido para essa coluna I Se essa restrição não for especificada valor padrão será NULL para colunas que não possuem a restrição NOT NUL. Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT DEFAULT 1 ); www.wladmirbrandao.com 150 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Valor Limita valores possíveis para coluna Exemplo→ supondo que números de departamento sejam restritos a inteiros entre 1 e 20, podemos modificar a tabela professor, adicionando uma restrição para a coluna Departamento : ALTER TABLE PROFESSOR ADD CHECK (Departamento > 0 AND Departamento < 21); www.wladmirbrandao.com 151 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Chave Especifica uma ou mais colunas que compõem a chave primária de uma tabela I Se a chave primária for composta por apenas uma coluna, a cláusula PRIMARY KEY pode acompanhar a coluna diretamente Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT, PRIMARY KEY (CPF) ); www.wladmirbrandao.com 152 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Unicidade Especifica chaves secundárias alternativas I Pode ser especificada diretamente para chave secundária em coluna única Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT, PRIMARY KEY (CPF), UNIQUE (Nome) ); www.wladmirbrandao.com 153 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Integridade Referencial Estabelece regras para restrição de atualização de linhas correlacionadas em diferentes tabelas através de referência à chave primária por chave estrangeira Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT, PRIMARY KEY (CPF), FOREIGN KEY (Departamento) REFERENCES DEPARTAMENTO(Numero) ); www.wladmirbrandao.com 154 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Integridade Referencial I A integridade referencial entre tabelas pode ser violada quando linhas são manipuladas ou o valor de uma chave primária é modificado I Ação de disparo referencial especifica uma ação alternativa para os casos de violação de integridade: I RESTRICT→ a linha da chave primária não pode ser modificada se houver linhas contendo chaves estrangeiras associadas a ela I CASCADE→ a linha da chave primária, bem como as linhas contendo chaves estrangeiras são modificadas I SET NULL→ a linha da chave primária é modificada, desde que se consiga atualizar para NULL as chaves estrangeiras associadas a ela I SET DEFAULT→ a linha da chave primária é modificada, desde que as chaves estrangeiras associadas a ela possuam valor padrão que possa ser usado www.wladmirbrandao.com 155 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrição de Integridade Referencial Ações de disparo devem ser escolhidas em caso de remoção (ON DELETE) ou atualização (ON UPDATE) Exemplo: CREATE TABLE PROFESSOR ( CPF CHAR(11) NOT NULL, Nome VARCHAR(80) NOT NULL, Departamento INT DEFAULT 1, PRIMARY KEY (CPF), FOREIGN KEY (Departamento) REFERENCES DEPARTAMENTO(Numero) ON DELETE SET DEFAULT ON UPDATE CASCADE ); www.wladmirbrandao.com 156 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Restrições Nomeadas Restrição pode ser rotulada utilizando o descritor CONSTRAINT I Nomes de todas as restrições de um esquema precisam ser exclusivos Exemplos: CONSTRAINT PK_PROFESSOR PRIMARY KEY (CPF); CONSTRAINT FK_DEPARTAMENTO_PROFESSOR FOREIGN KEY (Departamento) REFERENCES DEPARTAMENTO(Numero) ON DELETE SET DEFAULT ON UPDATE CASCADE; www.wladmirbrandao.com 157 / 373 http://www.wladmirbrandao.com sql: manipulação de dados Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL StructuredQuery Language I Linguagem de consulta estruturada I Linguagem declarativa de alto nível I Usuário especifica o que deseja, deixando decisões sobre como executar a consulta para o SGBD I Contém instruções para definição e manipulação de dados I Padrão em SGBDs relacionais comerciais I Mantém equivalência com o modelo relacional I Relação→ Tabela I Tupla→ Linha I Atributo→ Coluna www.wladmirbrandao.com 159 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 SQL: Manipulação de Dados Existem diferentes instruções (comandos) para manipulação de dados I INSERT→ inserir linhas em tabelas I DELETE→ remover linhas de tabelas I UPDATE→ atualizar valores de colunas em linhas de tabelas I SELECT→ recuperar dados em tabelas www.wladmirbrandao.com 160 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Instrução INSERT Acrescenta uma linha em uma tabela I Necessário especificar o nome da tabela e uma lista de valores para a linha I Valores devem ser listados na mesma ordem em que as colunas correspondentes foram definidas na tabela PROFESSOR CPF Nome Sexo Salario Departamento INSERT INTO PROFESSOR VALUES (’12345678900’, ’Ricardo Marini’, ’M’, 3000.00, 1); www.wladmirbrandao.com 161 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Instrução INSERT É possível especificar nomes de colunas correspondentes a valores fornecidos PROFESSOR CPF Nome Sexo Salario Departamento INSERT INTO PROFESSOR (CPF, Sexo, Nome, Departamento) VALUES (’12345678900’, ’M’, ’Ricardo Marini’, 1); Coluna não especificada tem seu valor definido como DEFAULT ou NULL, sendo que valores e colunas devem ser listadas na mesma ordem www.wladmirbrandao.com 162 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Instrução INSERT Se alguma restrição for violada a operação é rejeitada PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins M 3200.00 1 32145678900 Ana Maria Freitas F 7500.00 2 INSERT INTO PROFESSOR (CPF, Nome, Sexo, Departamento) VALUES (’68345618900’, ’Amanda Ramirez’, ’F’, 4); Caso não exista tupla na tabela departamento com chave primária Numero = 4 para manter integridade referencial com a coluna Departamento da tabela professor a operação será rejeitada www.wladmirbrandao.com 163 / 373 http://www.wladmirbrandao.com Wla dm ir C ard oso Bra ndã o ww w.w lad mir bra nda o.c om CC BY- NC -ND 4.0 Instrução INSERT Se alguma restrição for violada a operação é rejeitada PROFESSOR CPF Nome Sexo Salario Departamento 12345678900 Roberto Machado M 1200.00 1 12345678901 Manuela Costa F 2700.00 3 21345678900 Carlos A. Martins
Compartilhar