Unidade 2
Modelo Relacional
Engenharia de Computação / Engenharia de Produção
Banco de DadosProf. Maria das Graças da Silva Teixeira
Material base: Banco de Dados, 2009.2, prof. Otacílio José Pereira
Contexto na Disciplina
• 1 – Introdução• 2 – Modelo Relacional• 3 – SQL• 4 – Projeto de Banco de Dados• 5 – Armazenamento e Indexação• 6 – Processamento e Otimização de Consultas• 7 – Gerenciamento de Transações• 8 – Controle de Concorrência• 9 – Recuperação de Falhas• 10 – Segurança de Banco de Dados• 11 – Tópicos Avançados
Reflexões Preliminares
• Como organizar e estruturar as informações de um banco de dados?
• Quais técnicas e mecanismos podem ser empregadas nesta estruturação, ou de forma mais contextualizada, nesta modelagem?
• Que tipo de operações e ações devem ser previstas para manipular as informações modeladas?
Roteiro
– Características do Modelo Relacional– Exemplos de modelo relacional
– Álgebra Relacional– SQL
• Linguagem de Manipulação de Dados - DML• Linguagem de Definição de Dados - DDL
Modelo Relacional
• A maioria dos SGBD’s estruturam as informações com base no modelo relacional;
• Nesta unidade partiremos do pressuposto que játemos um banco de dados modelado, projetado e instalado;
• Na unidade 4 – Projeto de Banco de Dados, veremos como projetar um banco de dados.
Contexto
• A ênfase deste tópico está em compreender um modelo de dados, suas características e as operações que podem ser feitas sobre ele.
Acessa
Estruturado
Armazenados
em Disco
• Um Modelo é a representação abstrata e simplificada de um sistema real, com a qual se pode explicar ou testar o seu comportamento, no todo ou em partes;
• Exemplos de Modelos:– Maquete de um empreendimento imobiliário;– Mapa rodoviário;– Planta de uma casa;– Molde de um novo conjunto feminino;
– Todos estes são modelos que permitem compreender o mundo real de uma forma simplificada.
Modelo
• Objetivos de um modelo relacional:– Permitir refinar e representar o entendimento sobre um ambiente
observado;– Servir de instrumento para comunicação;– Favorecer o processo de verificação e validação;– Servir como referencial para a geração de estruturas de dados;– Estabelecer conceitos únicos a partir de visões diversas.
• O Modelo Relacional é o modelo que permitirá atingir estes objetivos sob o aspecto da organização das informações em um banco de dados.
Modelo Relacional
Exemplo
• Em uma determinada cidade está crescendo o número de adeptos de esportes, sobretudo os esportes dito radicais;
• Um dos esportistas mais antigos, ciente deste crescimento resolve montar uma Loja de Materiais Esportivos especializados para esta área, a Radical Sports;
• Com base neste panorama, quais informações são interessantes considerar para a informatização desta loja?
Exemplo
• Um possível conjunto das informações está abaixo identificado:
• Um banco de dados relacional é formado por um conjunto de tabelas ou relações;
• Relação: Tabela de valores bidimensional organizada em linhas e colunas. Representa um conjunto de entidades do Modelo E/R ou uma classe em um Diagrama de Classes;
• Cada tabela é formada por atributos que pode possuir valores dentro de um Domínio estabelecido;
• Cada linha de uma tabela é uma tupla com vários valores onde cada valor pertence a um determinado domínio.
Introdução ao Modelo Relacional
• Exemplo– Considere as informações de um produto:
Produto (codProduto, dcrProduto, vlrUnitario)
– Produto representa a relação ou tabela;– codProduto, dcrProduto, vlrProduto são seus
atributos;– Cada atributo tem seu domínio, por exemplo
codProduto tem como domínio os números inteiros;
• A relação pode ter as seguintes tuplas ou linhas:1 Velame(Parapente) R$ 2.000,002 Selete (Cadeirinha) R$ 560,003 Capacete R$ 120,00
Introdução ao Modelo Relacional
• Esquema de Relação: Estabelece a estrutura da relação, isto é, seus atributos e domínio;
• Relação: É “uma variável” que representa a relação;
• Instância de Relação: São os valores, um instantâneo da relação em determinado momento;
• Fazendo uma analogia com Orientação a Objetos:– Esquema = Definição da Classe– Relação = Declaração da Variável– Instância = Objeto instanciado na variável
Conceitos de um Modelo Relacional
• Superchaves: Conjunto de um ou mais atributos que, permitem identificar de forma única uma entidade em um conjunto de entidades. Ex:(Nome,Numero_Conta) e (Nome,CPF)
• Chaves Candidatas: Ocorrem quando em uma relação existe mais de uma combinação de atributos possuindo a propriedade de identificação única. Ex: Matrícula é uma chave candidata e CPF também é outra chave candidata
• Chave Primária: Atributo ou combinação de atributos que possuem a propriedade de identificar de forma única uma linha da tabela. Ex: Matrícula
• Chave Estrangeira: Ocorre quando um atributo de uma relação for chave primária em outra relação.
• Linha (Tupla): Representa uma entidade do conjunto de entidades, ou um objeto de uma classe.
Alguns conceitos
• Colunas: Representam os vários atributos do conjunto de entidades ou classe.Ex: Matrícula, Nome, CPF, Endereço, Dt-Nasc, Dt-Adm.
• Célula: Item de dado elementar da linha i, coluna j.Ex: Vitória (linha 3, coluna 4)
• Ligações: Representam os relacionamentos do Modelo E/R ou Diagrama de Classes. A ligação entre duas relações é feita, em geral, transportando-se a chave de uma relação para outra (item transposto).
Alguns Cocneitos
• Nenhum campo componente de uma chave primária pode ser nulo;
• Cada célula de uma relação pode ser vazia (exceto uma chave primária), ou ao contrário, conter um único valor;
• A ordem das linhas é irrelevante;• Não há duas linhas totalmente iguais;• Cada coluna tem um nome e colunas distintas
devem ter nomes distintos;• Usando-se os nomes para se fazer referência
às colunas, a ordem destas torna-se irrelevante;
Características do Modelo Relacional
• Cada relação recebe um nome próprio distinto do nome de qualquer outra relação da base de dados;
• Os valores de uma coluna são retirados todos de um mesmo conjunto, denominado domínio da coluna;
• Duas ou mais colunas distintas podem ser definidas sobre um mesmo domínio;
• Um campo que seja uma chave estrangeira ou um item transposto só pode assumir valor nulo ou um valor para o qual exista um registro na tabela onde ela é chave primária.
Características do Modelo Relacional
Álgebra Relacional
• Define como utilizar um sistema de banco de dados;
• Em outras palavras, uma vez que as informações de um sistema estão estruturadas conforme o modelo e os dados estão armazenados, como estes dados são utilizados?
• Ainda de outra forma: quais operações podemos realizar sobre um banco de dados?
• Aquecimento:– Sobre as tabelas a seguir:
Quais consultas e tipos de operações podem ser realizadas sobre elas?Por exemplo: escolher apenas algumas colunas
Álgebra Relacional
• Informações a exercitar:– Funcionários (cod_func, nome, cod_depto, telefone) – Clientes (cod_cliente, nome, telefone)– Departamentos (cod_depto, dcr_depto)
Álgebra Relacional
• Conceitos Básicos:– Relações: Conjunto de tuplas;– Operações: Manipulação sobre as tuplas das
relações;
• Álgebra– Nos lembra algo relacionado com
matemática;– São os fundamentos matemáticos das
operações com relações (tabelas) em um modelo relacional;
Álgebra Relacional
• Que operações básicas empregamos no banco de dados?– Escolher colunas;– Escolher linhas;– Juntar os dados de uma relação com outra
• Por meio das colunas;• Por meio das linhas;
– Outras.
Álgebra Relacional
Visão Geral das Operações• Unárias
(envolve apenas uma relação)– Projeção;– Seleção;– Renomeação;
• Binárias (envolve mais de uma relação)
– Produto Cartesiano;– União;– Diferença;– Interseção;– Junção Natural.
Álgebra Relacional
abc
xy
aabbcc
xyxyxy
a1a2a3
b1b1b2
b1b2b3
c1c2c3
a1a2a3
b1b1b2
c1c1c2
aaabc
xyzxy
xz
a
Ligação (natural)Dividir
União
Diferença
Intersecção
Selecionar ProjetarProduto
• Projeção (π)– Indicada na literatura por π (a letra grega pi); – Retorna os valores de determinadas colunas de
uma tabela;– Exemplos:
• ππππnome(Funcionários)• ππππnome, telefone (Funcionários)
Projetar
Álgebra Relacional
• Operação Seleção (σ)– Seleciona tuplas que satisfazem certa condição;
– Indicada na literatura por σ (a letra grega sigma);– Exemplos:
• σσσσcod_depto=‘RH’(Funcionários)• σσσσcod_cliente=‘0045’(Clientes)
• Observação– As comparações são permitidas usando
=, ≠,<, ≤, > e ≥ e os conectivos e (^) e ou (∨).
Selecionar
Álgebra Relacional
• Operação Produto Cartesiano ( X )– Combina informações a partir de diversas relações;– Exemplo:
• Funcionário X Depto– Produz todas as combinações entre funcionário
e depto
σσσσfunc.cod_depto = depto.cod_depto(Funcionários X Departamentos)
– É comum empregarmos com a seleção para fazermos a “amarração” entre determinados campos que precisamos visualizar.
Álgebra Relacional
• Exercício– Escreva as sentenças das consultas
abaixo e apresente o resultado conforme dados mostrados como exemplo:
• Quais funcionários são analistas?• Quais os funcionários da Produção em Java e da
Produção em .NET?• Quais o nome e telefone do cliente com código
0012?• Outras
Álgebra Relacional
• Operação União– Permite encontrar tuplas que estão em uma das relações
envolvidas;
– Indicada na literatura pelo símbolo da união (∪∪∪∪);– Exemplo:
• Selecionar os nomes e telefones de funcionários juntamente com os nomes e telefones de clientes para fazermos uma mala direta
ππππnome, telefone (Funcionários)∪∪∪∪
ππππnome, telefone (Clientes)
– Caso as linhas existam nas duas relações (duplicadas), apenas uma será considerada. Segue-se o conceito de operações sobre conjuntos.
Álgebra Relacional
• Operação Diferença– Permite encontrar tuplas que estão em uma relação e não estão
em outra;– Indicada na literatura pelo símbolo da união (-);– Exemplo:
• Quais os telefones de Funcionários que não constam na lista de telefones de cliente?
ππππ telefone (Funcionários)-ππππ telefone (Clientes)
– Poderíamos até considerar que as relações Funcionarios e Clientes possuem agora o atributo CPF para utilizarmos em vez de telefone;
– Observação:• As relações precisam ser compatíveis.
– Apresentarem o mesmo número de atributos;– Cada atributo de uma relação ser do mesmo domínio (tipo) da outra
relação.
Álgebra Relacional
• Operação Renomear (ρ)– Permite dar um nome diferente para relações que aparecem mais de
uma vez em uma consulta;– Indicada na literatura por ρ (a letra grega ro);– Exemplos:
• Selecionar os dados do funcionário cujo código é 2008-01t ���� (σσσσcod_func=”2008-01” (Funcionários))
• Selecionar nome e telefone dos funcionários que sejam do mesmo departamento que o funcionário de código 2008-01
πnome, telefone (σt.cod_depto = func2.cod_depto (t X ρfunc2 (Funcionários))
Álgebra Relacional
• Operação Interseção (∩)– Encontra tuplas que estão nas duas relações
envolvidas, ao mesmo tempo;
– Indicada na literatura pelo símbolo da interseção (∩);
– Exemplo:• Quais funcionários também são clientes da loja?
ππππ nome, telefone (Funcionarios)∩
ππππ nome, telefone (Clientes)
• Poderíamos usar o CPF em vez do telefone, conforme observação anterior.
Álgebra Relacional
• Operação Junção/Ligação Natural (|X|)– Permite combinar relações por meio dos
relacionamentos entre elas;– É uma combinação das operações de Seleção e
Produto Cartesiano em uma mesma operação;– Exemplo:
• (funcionario |X| Departamento)
Álgebra Relacional
• ExercícioSeja o modelo de dados abaixo:
– Alunos (cod_aluno, nome, matricula, telefone, cpf, coeficiente)
– Funcionarios (cod_funcionario, nome, matricula, telefone, cpf, cod_cargo)
– Cargo (cod_cargo, dcr_cargo)– Cod_cargo = 1 -> Professor– Cod_cargo = 2 -> Funcionario
– Turmas (cod_turma, dcr_turma, cod_professor, cod_disciplina)
– Alunos_Turmas (cod_turma, cod_aluno, nota, freq)
– Disciplinas (cod_disciplina, dcr_disciplina)
– PreRequisitos (cod_disc_pre, cod_disc_pos)
Álgebra Relacional
• Exercício (continuação)– Recuperar o código, nome, matrícula e cpf de alunos juntamente com
professores (para convocar para evento da faculdade);
– Recuperar o nome e telefone dos funcionários que também são alunos;
– Recuperar o nome e telefone dos funcionários que não são alunos;
– Recuperar o nome, telefone e descrição do cargo dos funcionários;
– Recuperar a descrição da turma, a descrição da disciplina e o nome do professor das disciplinas ministradas por ‘Pedro’;
– Recuperar as disciplinas que são pré-requisitos de “Programação I”.
Álgebra Relacional
• Funções Agregadas– As funções agregadas tomam uma coleção de valores
e retornam um único valor como resultado.– Principal funções agregadas: avg: valor médio
min: valor mínimomax: valor máximosum: soma dos valorescount: número de valores
– Sintaxe das funções agregadas na álgebra relacional:
G1, G2,..., GnGF1(A1), F2(A2),..., Fm(Am)(E)
E é qualquer expressão de álgebra relacional
G1, G2 …, Gn uma lista de atributos em que agrupar (pode ser vazia)Cada Fi é uma função agregadaCada Ai é um nome de atributoG G G G é o símbolo para “G Caligráfico”
Álgebra Relacional
• Funções Agregadas– Exemplo:
• Qual a quantidade de Professores?Gcount(cod_funcionarios)(δ(cod_cargo = 1) (Funcionarios) )
• Qual a média de notas, por disciplina?
cod_disciplinaGavg(nota) (δ(Turmas.cod_turma = Alunos_Turmas.cod_turma)
(Turmas x Alunos_Turmas) )
Álgebra Relacional
• Lista de Exercícios (disponibilizada no site);
• Leitura: SILBERSCHATZ, A. et.al. Sistema de banco de dados. 5.ed. Rio de Janeiro: Elsevier, 2006. Capítulo 2 – Modelo Relacional.
Para estudos