Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 P ro je to d e B an co d e D ad os Álgebra Relacional 2 P ro je to d e B an co d e D ad os Álgebra relacional Álgebra relacional: Conjunto básico de operações para o modelo relacional. Expressão da álgebra relacional: Sequência de operações da álgebra relacional. O objeBvo da álgebra relacional é permiBr o cálculo de consultas declaradas com linguagens de nível mais alto no modelo relacional. 3 P ro je to d e B an co d e D ad os Álgebra relacional Linguagem procedural Seis operadores básicos: – seleção: σ – projeção: π – união: ⋃ – diferença de conjuntos: − – produto cartesiano: × – renomeação: ρ Os operadores usam uma ou duas relações como entrada e produzem uma nova relação como resultado. 4 P ro je to d e B an co d e D ad os Operação seleção – Exemplo • Relação R • σ A = B^ D > 5 (R) A B C D α α β β α β β β 1 5 12 23 7 7 3 10 A B C D α β α β 1 23 7 10 5 P ro je to d e B an co d e D ad os Operação select • Notação: σ p(R) • p é chamado o predicado de seleção • Definida como: σ p(r) = {t | t ∈ R and p(t)} • Onde p é uma fórmula em cálculo proposicional consisBndo em termos conectados por: ∧ (and), ∨ (or), ¬ (not) Cada termo pode ser: • <atributo> op <atributo> ou <constante> • onde op pode ser: =, ≠, >, ≥, <, ≤ • Exemplo de seleção: σ nome_agência=“Perryridge”(conta) 6 P ro je to d e B an co d e D ad os Operação projeção – Exemplo • Relação R: • π A,C (R) A B C α α β β 10 20 30 40 1 1 1 2 A C α α β β 1 1 1 2 = A C α β β 1 1 2 Eliminação de duplicatas 7 P ro je to d e B an co d e D ad os Operação projeção • Notação: π A1, A2,..., Ak(R) • onde A1, A2 são nomes de atributo e R é um nome de uma relação. • O resultado é definido como a relação de k colunas obBdas excluindo-‐se as colunas que não estão listadas • Linhas duplicadas são removidas do resultado, de modo que as relações sejam conjuntos • Exemplo: Para eliminar o atributo nome_agência de conta π número_conta, saldo (conta) 8 P ro je to d e B an co d e D ad os Operação união – Exemplo • Relações R, S: • R ⋃ S A B α α β 1 2 1 A B α β 2 3 R S A B α α β β 1 2 1 3 9 P ro je to d e B an co d e D ad os Operação união • Notação: R ⋃ S • Definida como: • R ⋃ S = {t | t ∈ R or t ∈ S} • Para que R ⋃ S seja válido: – 1. R e S precisam ser da mesma aridade (o mesmo número de atributos) – 2. Os domínios de atributo precisam ser compajveis (exemplo: 2a coluna de R lida com o mesmo Bpo de valores que a 2a coluna de S) – Exemplo: encontrar todos os clientes com uma conta ou um emprésBmo π nome_cliente (depositante) ⋃ π nome_cliente (tomador) 10 P ro je to d e B an co d e D ad os Operação diferença de conjuntos – Exemplo • Relações R, S: • R − S A B α α β 1 2 1 A B α β 2 3 R S A B α β 1 1 11 P ro je to d e B an co d e D ad os Operação diferença de conjuntos • Notação R – S • Definida como: • R – S = {t | t ∈ R and t ∉ S} • As diferenças de conjunto precisam ser relações compajveis. – R e S precisam ter a mesma aridade – os domínios de atributo de R e S precisam ser compajveis 12 P ro je to d e B an co d e D ad os Operação produto cartesiano – Exemplo • Relações R, S • R × S A B α α α α β β β β 1 1 1 1 2 2 2 2 C D α β β γ α β β γ 10 10 20 10 10 10 20 10 E a a b b a a b b A B α β 1 2 C D α β β γ 10 10 20 10 E a a b bR S 13 P ro je to d e B an co d e D ad os Operação produto cartesiano • Notação R × S • Definida como: – R × S = {t q | t ∈ R and q ∈ S} • Considere que os atributos de R(R) e S(S) sejam disjuntos. (Ou seja, R ∩ S = ∅). • Se os atributos de R(R) e S(S) não forem disjuntos, então, o restante precisa ser usado. 14 P ro je to d e B an co d e D ad os Composição das operações • Pode construir expressões usando várias operações • Exemplo: σA=C(R × S) • R × S • σA=C(r x s) A B α α α α β β β β 1 1 1 1 2 2 2 2 C D α β β γ α β β γ 10 10 20 10 10 10 20 10 E a a b b a a b b A B C D E α β β 1 2 2 α β β 10 10 20 a a b 15 P ro je to d e B an co d e D ad os Operação renomeação • Permite nomear e, portanto, referir-‐se aos resultados das expressões de álgebra relacional. • Permite referir-‐se a uma relação por mais de um nome. • Exemplo: – ρ x (E) retorna a expressão E sob o nome X • Se uma expressão de álgebra relacional E Bver aridade n, então – ρ x(A1, A2,..., An)(E) • retorna o resultado da expressão E com o nome x, e com os atributos renomeadospara A1, A2,..., An. 16 P ro je to d e B an co d e D ad os Exemplo de banking • agência (nome_agência, cidade_agência, aBvo) • cliente (nome_cliente, rua_cliente, cidade_cliente) • conta (número_conta, nome_agência, saldo) • emprésBmo (número_emprésBmo, nome_agência, quanBa) • depositante (nome_cliente, número_conta) • tomador (nome_cliente, número_emprésBmo) 17 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre todos os emprésBmos de mais de US$ 1200 • Encontre o número de emprésBmo para cada emprésBmo de uma quanBa maior que US$ 1200 • Encontre os nomes de todos os clientes que têm um emprésBmo, uma conta, ou ambos, do banco • Encontre os nomes de todos os clientes que têm um emprésBmo e uma conta no banco 18 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre todos os emprésBmos de mais de US$ 1200 – σ quanBa > 1200 (emprésBmo) • Encontre o número de emprésBmo para cada emprésBmo de uma quanBa maior que US$ 1200 – π número_emprésBmo (σ quanBa > 1200 (emprésBmo)) • Encontre os nomes de todos os clientes que têm um emprésBmo, uma conta, ou ambos, do banco – π nome_cliente (tomador) ⋃ π nome_cliente (depositante) • Encontre os nomes de todos os clientes que têm um emprésBmo e uma conta no banco – π nome_cliente (tomador) ⋃ π nome_cliente (depositante) 19 P ro je to d e B an co d e D ad os Definição formal • Uma expressão básica na álgebra relacional consiste em qualquer um dos seguintes: – Uma relação no banco de dados – Uma relação constante • Seja E1 e E2 expressões de álgebra relacional; todas as expressões a seguir são de álgebra relacional: – E1 ⋃ E2 – E1 − E2 – E1 × E2 – σ p(E1), p é um predicado nos atributos em E1 – π s(E1), S é uma lista consisBndo em alguns dos atributos em E1 – ρ x(E1), x é o novo nome para o resultado de E1 20 P ro je to d e B an co d e D ad os Operação atribuição • A operação atribuição (←) fornece uma maneira conveniente de expressar consultas complexas • Escreva consulta como um programa sequencial consisBndo em – uma série de atribuições – seguidas de uma expressão cujo valor é exibido como resultado da consulta – A atribuição precisa sempre ser feita para uma variável de relação temporária. – R ← (tomador × emprésBmo) – S ← σ tomador.número_emprésBmo = emprésBmo.número_emprésBmo(R) 21 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre os nomes de todos os clientes que têm um emprésBmo na agência Perryridge. • Encontre os nomes de todos os clientes que têm um emprésBmo na agência Perryridge mas não têm uma conta em qualquer agência do banco. 22 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre os nomes de todos os clientes que têm um emprésBmo na agência Perryridge. – R ← (tomador × emprésBmo) – S ← σ tomador.número_emprésBmo =emprésBmo.número_emprésBmo(R) – T ← π nome_cliente(σ nome_agência =“Perryridge”(S)) • Encontre os nomes de todos os clientes que têm um emprésBmo na agência Perryridge mas não têm uma conta em qualquer agência do banco. – R ← (tomador × emprésBmo) – S ← σ tomador.número_emprésBmo =emprésBmo.número_emprésBmo(R) – T ← π nome_cliente(σ nome_agência =“Perryridge”(S)) – U ← T – πnome_cliente (depositante) 23 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre o maior saldo de conta – Estratégia: • Encontre os saldos que não são o maior – Renomeie a relação conta como d de modo que possamos comparar cada saldo de conta com todos os outros • Use diferença de conjuntos para encontrar os saldos de conta que não foram encontrados na primeira etapa. 24 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre o maior saldo de conta – π saldo (conta) -‐ π conta.saldo (σconta.saldo < d.saldo (conta × ρ d (conta))) 25 P ro je to d e B an co d e D ad os Outras operações • Definimos operações adicionais que não acrescentam poder à álgebra relacional, mas que simplificam consultas comuns. – Interseção de conjuntos – Junção natural – Divisão – Atribuição 26 P ro je to d e B an co d e D ad os Operação interseção de conjuntos – Notação: R ⋂ S – Definida como: – R ⋂ S = { t | t ∈ R and t ∈ S } – Considere: • R, S possuem a mesma aridade • os atributos de R e S são compajveis – Nota: R ⋂ S = R − (R − S) 27 P ro je to d e B an co d e D ad os Operação interseção de conjuntos – Exemplo – Relação R, S: – R ∩ S A B α α β 1 2 1 A B α β 2 3 R S A B α 2 28 P ro je to d e B an co d e D ad os Operação junção natural • Notação: R ⋈ S • Sejam R e S relações nos esquemas R e S respecBvamente. Então, R ⋈ S é uma relação noesquema R ∪ S obBda desta forma: – Considere cada par de tuplas tr de R e ts de S. – Se tr e ts possuem o mesmo valor em cada um dos atributos em R ∩ S, acrescente uma tupla t ao resultado, onde • t possui o mesmo valor de tr em R • t possui o mesmo valor de ts em S • Exemplo: – R = (A, B, C, D) – S = (E, B, D) – Esquema resultado = (A, B, C, D, E) – R ⋈ S é definido como: ∏r.A, r.B, r.C, r.D, s.E (σr.B = s.B ∧ r.D = s.D (R × S)) 29 P ro je to d e B an co d e D ad os Operação junção natural – Exemplo • Relações R, S: • R ⋈ S A B α β γ α δ 1 2 4 1 2 C D α γ β γ β a a b a b R A B α α α α δ 1 1 1 1 2 C D α α γ γ β a a a a b E α γ α γ δ B 1 3 1 2 3 D a a a b b E α β γ δ ∈ S 30 P ro je to d e B an co d e D ad os Operação divisão • Notação: R ÷ S • Adequado para consultas que incluem a frase “para todo”. • Sejam R e S relações nos esquemas R e S respecBvamente, onde – R = (A1, …, Am , B1, …, Bn ) – S = (B1, …, Bn) – O resultado de R ÷ S é uma relação no esquema – R – S = (A1, …, Am) – R ÷ S = { t | t ∈ π R − S (R) ∧ ∀ u ∈ S ( tu ∈R) } – Onde tu significa a concatenação das tuplas t e u para produzir uma única tupla 31 P ro je to d e B an co d e D ad os Operação divisão – Exemplo • Relações R, S: • R ÷ S: A α β R B 1 2 A B α α α β γ δ δ δ ∈ ∈ β 1 2 3 1 1 1 3 4 6 1 2 S 32 P ro je to d e B an co d e D ad os Outro exemplo de divisão • Relações R, S: • R ÷ S: A B α γ a a C γ γ A B α α α β β γ γ γ a a a a a a a a C D α γ γ γ γ γ γ β a a b a b a b b E 1 1 1 1 3 1 1 1 D a b E 1 1 R S 33 P ro je to d e B an co d e D ad os Operação divisão (cont.) • Propriedade – Seja Q = R ÷ S – Então, Q é a maior relação que saBsfaz Q x S ⊆ R • Definição em termos da operação de álgebra básica Seja R(R) e s(S) relações e seja S ⊆ R • R ÷ S = ∏R–S (R) – ∏R–S ( ( ∏R–S (R) × S ) – ∏ R–S, S(R )) • Para ver por quê – ∏ R–S, S (R) simplesmente reordena atributos de r – ∏ R–S (∏ R–S (R) × S) – ∏ R–S, S(R) ) fornece as tuplas t em ∏ R–S (R) tal que, para alguma tupla u ∈ S, tu ∉ R. 34 P ro je to d e B an co d e D ad os Exemplo de consultas de banco • Encontre o nome de todos os clientes que têm um emprésBmo e uma conta no banco. • Encontre o nome de todos os clientes que têm um emprésBmo no banco e descubra a quanBa do emprésBmo. • Encontre todos os clientes que possuem uma conta pelo menos das agências “Downtown” e Uptown”. 35 P ro je to d e B an co d e D ad os Exemplo de consultas de banco • Encontre o nome de todos os clientes que têm um emprésBmo e uma conta no banco. – π nome_cliente (tomador) ⋈ π nome_cliente (depositante) • Encontre o nome de todos os clientes que têm um emprésBmo no banco e descubra a quanBa do emprésBmo. – π nome_cliente, número_emprésBmo, quanBa (tomador ⋈ emprésBmo) 36 P ro je to d e B an co d e D ad os Exemplo de consultas de banco • Encontre todos os clientes que possuem uma conta pelo menos das agências “Downtown” e Uptown”. – Consulta 1: – π nome_cliente(σnome_agência = “Downtown” (depositante ⋈ conta)) ⋂ π nome_cliente (σnome_agência = “Uptown” (depositante ⋈ conta)) – Consulta 2: – π nome_cliente, nome_agência(depositante ⋈ conta) ÷ ρtemp(nome_agência) ({(“Downtown” ), (“Uptown” )}) • Observe que a Consulta 2 usa uma relação constante. 37 P ro je to d e B an co d e D ad os Consultas de exemplo • Encontre todos os clientes que têm uma conta em todas as agências localizadas na cidade de Brooklyn. – π nome_cliente, nome_agência(depositante ⋈ conta) ÷ π nome_agência(σ cidade_agência = “Brooklyn”(agência)) 38 P ro je to d e B an co d e D ad os Operações de álgebra relacional estendidas – Projeção generalizada – Funções agregadas – Junção externa 39 P ro je to d e B an co d e D ad os Projeção generalizada • Estende a operação projeção permiBndo que funções aritméBcas sejam usadas na lista de projeção. π F1, F2,..., Fn(E) – E é qualquer expressão de álgebra relacional – Cada F1, F2,..., Fn é uma expressão aritméBca envolvendo constantes e atributos no esquema de E. • Dada a relação info_crédito(nome_cliente, limite, saldo_crédito), descubra quanto mais cada pessoa pode gastar: – π nome_cliente, limite -‐ saldo_crédito (info_crédito) 40 P ro je to d e B an co d e D ad os Funções e operações agregadas • As funções agregadas tomam uma coleção de valores e retornam um único valor como resultado. • avg: valor médio min: valor mínimo max: valor máximo sum: soma dos valores count: número de valores• Operação agregada na álgebra relacional • G1, G2,..., Gn Γ F1(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 agregada – Cada Ai é um nome de atributo 41 P ro je to d e B an co d e D ad os Operação agregada – Exemplo • Relação R: • Γsum(c) (R) A B α α β β α β β β C 7 7 3 10 sum(c) 27 42 P ro je to d e B an co d e D ad os Operação agregada – Exemplo – Relação conta agrupada por nome_agência: – nome_agência Γ sum(saldo) (conta) nome_agêncianúmero_conta soma Perryridge Perryridge Brighton Brighton Redwood A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 nome_agênciasum(saldo) Perryridge Brighton Redwood 1300 1500 700 43 P ro je to d e B an co d e D ad os Funções agregadas (cont.) • O resultado da agregação não possui um nome – Pode usar a operação renomeação para fornecer-‐lhe um nome – Para conveniência, permiBmos a renomeação como parte da operação agregada – nome_agência Γ sum(saldo) as saldo_soma (conta) 44 P ro je to d e B an co d e D ad os Junção externa • Uma extensão da operação junção que evita a perda de informações. • Calcula a junção e acrescenta ao resultado as tuplas de uma relação que não correspondem às tuplas na outra relação. • Usa valores nulos: – nulo significa que o valor é desconhecido ou inexistente – Todas as comparações envolvendo valores nulos são (grosseiramente falando) falsas por definição. • Estudaremos o significado exato das comparações com nulos mais adiante 45 P ro je to d e B an co d e D ad os Junção externa – Exemplo – Relação R – Relação S a L-170 L-230 L-160 b Red Green Blue c 3000 4000 1000 d Jones Smith Suzan b Red Green Yellow 46 P ro je to d e B an co d e D ad os Junção externa – Exemplo • Junção interna R ⋈ S • Junção externa esquerda • R ⟖ S a c L-170 L-230 3000 4000 d Jones Smith b Red Green Jones Smith null a c L-170 L-230 L-160 3000 4000 null d b Red Green Blue 47 P ro je to d e B an co d e D ad os Junção externa – Exemplo • Junção externa direita • R ⟕ S • Junção externa integral • R ⟗ S a c L-170 L-230 null 3000 4000 1000 d Jones Smith Suzan b Red Green Yellow a c L-170 L-230 L-260 null 3000 4000 null 1000 d Jones Smith null Suzan b Red Green Blue Yellow 48 P ro je to d e B an co d e D ad os Valores nulos • É possível que as tuplas tenham um valor nulo, indicado por nulo, para alguns dos seus atributos. • nulo significa um valor desconhecido ou que um valor não existe. • O resultado de qualquer expressão aritméBca envolvendo nulo é nulo. • As funções agregadas simplesmente ignoram valores nulos (como na SQL) • Para eliminação e agrupamento de duplicatas, nulo é tratado como qualquer outro valor e dois nulos são considerados iguais (como na SQL) 49 P ro je to d e B an co d e D ad os Modificação do banco de dados • O conteúdo do banco de dados pode ser modificado usando as seguintes operações: – Exclusão – Inserção – Atualização • Todas essas operações são expressas usando o operador de atribuição. 50 P ro je to d e B an co d e D ad os Exclusão • Uma requisição de exclusão é expressa semelhantemente a uma consulta, exceto que, em vez de exibir tuplas ao usuário, as tuplas selecionadas são removidas do banco de dados. • Pode excluir apenas tuplas inteiras; não pode excluir valores em atributos específicos. • Na álgebra relacional, uma exclusão é expressa por: • R ← R – E • onde R é uma relação e E é uma consulta de álgebra relacional. 51 P ro je to d e B an co d e D ad os Exemplos de exclusão • Exclua todos os registros de conta na agência Perryridge. – conta ← conta – σnome_agência = “Perryridge” (conta) • Exclua todos os emprésBmos com quanBa na faixa de 0 a 50 – emprésBmo ← emprésBmo – σquanBa ≥ 0 ∧ quanBa ≤ 50 (emprésBmo) • Exclua todas as contas em agências localizadas no Brooklyn. – R1 ← σ cidade_agência = “Brooklyn” (conta ⋈ agência) R2 ← π nome_agência, número_conta, saldo (R1) R3 ← π nome_cliente, número_conta (R2 ⋈ depositante) depositante ← depositante -‐ R3 conta ← conta -‐ R2 52 P ro je to d e B an co d e D ad os Inserção • Para inserir dados em uma relação: – especificamos uma tupla a ser inserida – escrevemos uma consulta cujo resultado é um conjunto de tuplas a serem inseridas • Na álgebra relacional, uma inserção é expressa por: – R ← R ∪ E – onde R é uma relação e E é uma expressão de álgebra relacional. • A inserção de uma única tupla é expressa fazendo E ser uma relação constante contendo uma tupla. 53 P ro je to d e B an co d e D ad os Exemplos de inserção • Insira informações no banco de dados especificando que Smith possui US$ 1200 na conta A-‐973 na agência Perryridge. – conta ← conta ⋃ {(A-‐973, “Perryridge”,1200)} depositante ← depositante ⋃ {(“Smith”, A-‐973)} • Forneça, como um presente a todos os clientes de emprésBmo da agência Perryridge, uma conta de poupança de US$200. Deixe o número de emprésBmo servir como o número de conta para a conta de poupança. – R1 ← (σ nome_agência = “Perryridge” (tomador ⋈ emprésBmo)) conta ← conta ∪ π nome_agência, número_emprésBmo, 200 (R1) depositante ← depositante ∪ Πnome_cliente, número_emprésBmo (R1) 54 P ro je to d e B an co d e D ad os Exemplos de atualização • Faça pagamentos de juros acrescendo todos os saldos de 5 por cento. – conta ← π número_conta, nome_agência, saldo * 1,05 (conta) • Pague 6 por cento de juros a todas as contas com saldos acima de US$10.000 e pague 5 por cento a todas as outras contas. – conta ← π número_conta, nome_agência, saldo * 1,06 (σ saldo > 10000 (conta)) ⋃ π número_conta, nome_agência, saldo * 1,05 (σ saldo ≤ 10000 (conta)) 55 P ro je to d e B an co d e D ad os Exercícios • Considere o seguinte esquema relacional: – Fornecedores(fid: integer, fnome: string, end: string) – Pecas(pid: integer, pnome: string, cor:string) – Catalogo(fid: integer, pid: integer, preco: real) • No esquema acima, as chaves estão sublinhadas. A tabela Catalogo lista o preço praBcado pelos fornecedores para cada peça fornecida. • Exercício #1. Obtenha o nome dos fornecedores que fornecem alguma peça vermelha. • Exercício #2. Obtenha o nome dos fornecedores que fornecem alguma peça que custe 100 ou 150. • Exercício #3. Obtenha o fid dos fornecedores que fornecem alguma peça vermelha ou que custe 150. 56 P ro je to d e B an co d e D ad os Exercícios • Exercício #4. Obtenha o fid dos fornecedores que fornecem alguma peça vermelha e alguma peça verde. • Exercício #5. Obtenha o nome dos fornecedores que fornecem alguma peça vermelha que tenha preço abaixo de 100. • Exercício #6. Obtenha o nome das peças que possuem algum fornecedor. • Exercício #7. Obtenha o nome dos fornecedores que fornecem todas as peças. • Exercício #8. Obtenha o nome das peças que são fornecidas pelo fornecedor “Sempre Alerta” e não são fornecidas por nenhum outro fornecedor.
Compartilhar