Baixe o app para aproveitar ainda mais
Prévia do material em texto
Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 1 – Parte IV (Projeto Lógico de BD Relacional) Projeto Lógico de BD Relacional 31 páginas Colaborador: Prof. Osvaldo K .Takai ♦♦ IINNTTRROODDUUÇÇÃÃOO ♦♦ NNOOTTAAÇÇÃÃOO MMAATTEEMMÁÁTTIICCAA ♦♦ CCHHAAVVEESS ♦♦ RREESSTTRRIIÇÇÕÕEESS DDEE IINNTTEEGGRRIIDDAADDEE ♦♦ OOPPEERRAAÇÇÕÕEESS EE ÁÁLLGGEEBBRRAA RREELLAACCIIOONNAAIISS ♦♦ MMAAPPEEAAMMEENNTTOO ((MM--EERR PPAARRAA MM--RREELLAACCIIOONNAALL)) ♦♦ NNOORRMMAALLIIZZAAÇÇÃÃOO ♦♦ SSGGBBDDRR ((CCAARRAACCTTEERRÍÍSSTTIICCAASS)) ♦♦ EEXXEEMMPPLLOOSS DDEE ÁÁLLGGEEBBRRAA RREELLAACCIIOONNAALL ♦♦ EEXXEERRCCÍÍCCIIOOSS IV Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 2 – Parte IV (Projeto Lógico de BD Relacional) 1 - INTRODUÇÃO Proposto originalmente por E. F. Codd durante seu trabalho no Laboratório de Pesquisas da IBM em San Jose na segunda metade da década de 60, o Modelo Relacional se baseia em conceitos matemáticos para proporcionar uma representação confiável para Bancos de Dados. Desde sua implantação no primeiro SGBDR (Sistema de Gerenciamento de Bancos de Dados Relacionais) da IBM denominado System R, várias melhorias têm sido incluídas ao modelo, o que o torna ainda atual e o mais utilizado no mercado. O Modelo de Dados Relacional representa os dados em um BD através de um conjunto de Relações (tabelas). Estas relações contêm informações sobre entidades ou relacionamentos existentes no domínio da aplicação utilizada como alvo para a modelagem. Informalmente uma relação pode ser considerada como uma tabela de valores, onde cada linha desta tabela representa uma coleção de valores de dados interrelacionados. Estes conjuntos de valores podem estar representando uma instância de uma entidade ou relacionamento da aplicação. Os nomes fornecidos às tabelas e às suas colunas podem auxiliar na compreensão do significado dos valores armazenados em cada uma das suas linhas. Em terminologia do Modelo Relacional, cada linha da relação é denominada de Tupla, o nome da coluna é denominado Atributo da relação. FCODIGO FNOME FCATEGORIA FCIDADE F1 Santos 20 Piracicaba F2 Machado 10 São Paulo F3 Almeida 30 São Paulo F4 Ferreira 20 Campinas F5 Rodrigues 30 São Carlos O Domínio consiste de um grupo de valores atômicos a partir dos quais um ou mais atributos (ou colunas) retiram seus valores reais. Como por exemplo o domínio do atributo FCIDADE consiste no conjunto de todos os nomes legais de cidades. O Esquema de uma Relação consiste de um conjunto de atributos que descrevem as características dos elementos a ser modelados. É denotado por R(A1, A2, ...., An), onde cada atributo Ai toma seus valores a partir de um domínio Di; e R é o nome da relação. O número de atributos na relação n, consiste no grau da relação. Os domínios a partir dos quais os atributos da relação retiram seus valores não precisam ser necessariamente distintos. Como exemplo o esquema da relação apresentada na figura anterior é dado por: Fornecedor (FCodigo, FNome, FCategoria, FCidade) Como Esquema de um BD Relacional entende-se o conjunto de intenções (Esquemas das Relações) definidas para todas as relações da Base, e um conjunto de restrições de integridade. Sobre os nomes fornecidos aos atributos, é permitido àqueles que representem conceitos semelhantes, possuir ou não o mesmo nome em diferentes relações. Da mesma forma, atributos representando conceitos diferentes podem possuir o mesmo nome. O conjunto de restrições de integridade define regras básicas que os valores dos atributos devem obedecer quando aparecerem em uma relação. A Instância de uma Relação consiste no conjunto de valores que cada atributo, definido no esquema, assume em um determinado instante, formando o conjunto de tuplas. Ou seja, as Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 3 – Parte IV (Projeto Lógico de BD Relacional) instâncias das relações formam os dados que são armazenados no BD. As relações apresentam as seguintes características: a). Não há tuplas duplicadas em uma relação; b). Ordem das tuplas na relação não é relevante para diferenciar as relações; c). Os valores dos atributos devem ser atômicos, não sendo divisíveis em componentes. 2 – NOTAÇÃO MATEMÁTICA As seguintes notações serão utilizadas para apresentar alguns conceitos do modelo relacional: • Uma relação esquema R de grau n é representada como R(A1, A2, ..., An). • Uma tupla t em uma relação r(R) é representada como t=<v1, v2, ..., vn>, onde vi é o valor correspondente para atributos Ai. Será utilizado as seguintes notações para se referir aos valores dos componentes de tuplas: • t[Ai] indica o valor de vi em t para o atributo Ai. • t[Au, Aw, ..., Az] onde Au, Aw, ..., Az é uma lista de atributos de R, indica o conjunto de valores <vu, vw, ..., vz> de t correspondentes aos atributos especificados na lista. • As letras maiúsculas como Q, R e S denotam nomes de relação. • As letras minúsculas como q, r e s denotam instâncias de relação. • As letras minúsculas como t, u e v denotam tuplas. Uma relação r (ou instância de relação) da relação esquema R(A1, A2, ..., An), também denotado por r(R), é um conjunto de tuplas r={t1,t2,...,tm}. Cada tupla t é uma lista ordenada de n valores t=<v1,v2,...,vn>, onde cada valor vi, 1 <= i <= n, é um elemento do dom(Ai) ou um valor especial null. São utilizadas, com freqüência, o termo intenção da relação para o esquema R e extensão da relação para a instância r(R). Um esquema da base de dados relacional S é um conjunto de relações esquemas S={R1, R2, ..., Rm} e um conjunto de restrições de integridade RI. Uma instância da base de dados relacional DB de S é um conjunto de instâncias de relações DB={r1, r2, ..., rm} tal que ri é uma instância de Ri e que satisfaz as restrições de integridade especificadas em RI. Uma relação esquema R, denotada por R(A1, A2, ..., An), é um conjunto de atributos R={A1,A2,..., An}. Cada atributo Ai indica o nome do papel de algum domínio D na relação esquema R. D é chamado domínio de Ai e denotado por dom(Ai). Uma relação esquema é utilizada para descrever uma relação e R é o nome dessa relação. O grau de uma relação é o número de atributos da relação. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 4 – Parte IV (Projeto Lógico de BD Relacional) 3 - CHAVES Chave é um conjunto de atributos de uma relação e que pode ser utilizado para a realização de qualquer operação que envolva atributos e valores de atributos. Super Chave é o maior conjunto de atributos para manipulação e/ou identificação univocamente de uma tupla em uma relação. Uma relação pode ter várias chaves para identificação unívoca de suas tuplas, onde cada uma é denominada de Chave Candidata. Entre as chaves candidatas é escolhida uma pelo DBA (durante a fase de projeto lógico) para ser suportada pelo SGBD e assim, é mantido automaticamente a restrição de unicidade. Esta chave escolhida é denominada de Chave Primária. Desta forma, uma relação nunca apresentará tuplas repetidas em sua instância, o que significa a possibilidade de identificação de cada tupla separadamente uma da outra. Da relação apresentada para fornecedores, o conjunto {FCODIGO} é a chave primária da relação, uma vez que dois fornecedores não apresentarão o mesmo código. Qualquer outro conjunto de atributos da relação FORNECEDOR que contenha FCODIGO, como por exemplo {FCODIGO, FNOME, FCIDADE} é uma chave candidata, no entanto a escolha da chave primária é sempre objetivando minimizar sua quantidade de atributos. Quando uma chave primária for constituída por mais de um atributo da relação esta é denominada de Chave Primária Composta, caso contrário e denominada Chave Primária Simples. Entre as Chaves Candidatas, aquelas não escolhidas para ser a Chave Primária são denominadas de Chaves Alternativas e podem ser utilizadas como chaves de consultas, chaves de ordenação lógica (em consultas por formuláriose/ou relatórios) ou chaves de ordenação física das relações em termos de arquivos de dados. As Chaves que não pertencem aos conjunto de Chaves Candidatas, ou seja, são chaves que não permitem a identificação individual das tuplas de relação, são denominadas de Chaves Secundárias. Um conjunto de atributos Fk em um esquema da relação R1 é uma Chave Estrangeira de R1 se os atributos em Fk possuem o mesmo domínio que os atributos da chave primária de uma relação R2 e os valores de Fk em uma tupla de R1 devem ser os mesmos que ocorrem em tuplas de R2 ou serem nulos, e neste caso é dito que os atributos da Fk se referenciam à relação R2. Importante observar que uma chave estrangeira pode referenciar à sua própria relação, e que também não é necessário aparecer também como chave na relação R1. Para exemplificar o conceito de chave estrangeira, considere as relações abaixo sobre Peças e Fornecedores, e das peças fornecidas por cada fornecedor (as chaves de cada relação se encontram grifadas): FORNECEDOR(FCODIGO, FNOME, FCATEGORIA, FCIDADE); PEÇA (PCODIGO, PNOME, PCOR, PESO, PCIDADE); FP (FPCODIGO, FCODIGO, PCODIGO, QTDE); Entre as relações anteriores, pode-se observar que FCODIGO e PCODIGO quando aparecem na relação FP são chaves estrangeiras desta relação pois são chaves primárias das relações FORNECEDOR e PEÇA, respectivamente. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 5 – Parte IV (Projeto Lógico de BD Relacional) 4 - RESTRIÇÕES DE INTEGRIDADE São normas estabelecidas para a manter a integridade dos dados armazenados no Banco de Dados. As principais restrições de integridade são: Restrição de Domínio: os valores de atributos devem ser coerentes com os domínios correspondentes. Cada Atributos de uma relação é definido com base em um domínio de valores (exemplos: Idade, seu domínio são os números inteiros positivos; Salários, seu domínio são os números reais positivos). Restrição de Chave Primária (unicidade): cada valor de chave primária deve ser único dentro de seu escopo (a relação a que pertence). Restrição de Entidade: o valor de uma chave primária nunca deve ser nulo. O valor nulo não permite a identificação de uma tupla. Restrição de Referência: toda referência a uma tupla através de chave estrangeira deve ser verificada, ou seja, toda tupla referenciada deve previamente existir no Banco de Dados a menos que esta restrição seja explicitamente desprezada pelo usuário do Banco de Dados. Outras restrições são denominadas de Restrições Semântica, que podem ou não serem especificadas ou garantidas por um BD Relacional. Estas restrições ditas semânticas se referem mais especificamente sobre valores ou características que determinados atributos podem assumir no contexto de uma determinada aplicação. As Restrições Semânticas consistem em definir intervalos de valores para os atributos, limites, condições de existência, e outras. Exemplos:- Salário: valores no intervalo de 150,00 a 2400,00 reais; Categoria: Senior se tempo de trabalho >20, Pleno se tempo de trabalho é >10, Júnior caso contrário. 5 – OPERAÇÕES E ÁLGEBRA RELACIONAIS As operações no Modelo Relacional abordam 4 categorias. Todas estas operações devem obedecer as restrições de integridade, no entanto quando estas restrições forem violadas em algumas das operações, o SGBD pode tomar uma ação adaptativa (que permitirá a execução da operação mais com algumas restrições) ou uma ação de esclarecimento da impossibilidade de execução. Inserção Remoção Atualização Inserção Remoção Atualização União Interseção Diferença Produto Cart. Estrutura Dados Conjunto Tabela Seleção Projeção Junção Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 6 – Parte IV (Projeto Lógico de BD Relacional) ESTRUTURA: as operações sobre a estrutura apoiam os DBAs na tarefa de definição e manutenção do Esquema de Dados Relacional. INSERÇÃO : adição de novas tabelas ao Esquema de Dados. Exemplo: adição de uma relação de funcionários e seus correspondentes atributos (nome, endereço, idade, salário, cargo). REMOÇÃO : retirada de tabelas e atributos. Exemplo: a retirada de uma relação empresas_estrangeiras que não tem mais utilizada no Banco de Dados (neste caso todas as suas tuplas são retiradas desde que não violem as restrições de referências) ou mesmo de certos atributos de uma relação. ATUALIZAÇÃO: adição de atributos às tabelas existentes no Esquema de Dados. Exemplo: colocação de novos atributos (tempo de serviço e estado civil) na relação funcionários. É importante salientar que as operações sobre estrutura (atingindo o Esquema de Dados) permitem ao DBA adaptar o Banco de Dados às novas necessidades de informação de uma empresa. Apesar de oferecerem esta facilidade, estas operações não devem ser utilizadas para corrigir erros decorrentes das fases de projeto, pois sua utilização trás prejuízos (altos custos de manutenção corretiva) à empresa e desvaloriza os profissionais envolvidos. DADOS: operações sobre as tuplas de uma relação já existente no Banco de Dados INSERÇÃO: adição de uma ou mais tuplas em uma relação. A ação do SGBD diante de um erro da operação pode ser: rejeitar a operação, ou pedir para corrigir o campo que provocou a violação. Exemplo: Dada uma relação funcionários definida no Esquema de Dados, pode-se inserir os dados de uma pessoa recém contratada (Nome: João da Silva, Idade: 34, Endereço: Rua 4 Nro. 8 Cidade Rio Claro, Salário: , Cargo: Operador de torno CNC). REMOÇÃO: retirada de tuplas de uma relação. A ação do SGBD diante de um erro da operação pode ser: rejeitar a operação; realizar uma série de remoções em cascata; modificar os valores dos atributos referenciados que provocaram a violação (trocando por nulo, ou para um valor que faz referência a outra tupla válida de uma relação). Exemplo: Dada uma relação funcionários definida no Esquema de Dados, pode-se retirar os dados de uma pessoa dispensada (Nome: Maria da Silva, Idade: 26, Endereço: Rua 4 Nro. 8 Cidade Rio Claro, Salário: 1000, Cargo: Manobrista de D8). Relação Banco de Dados Relação tupla Relação tupla Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 7 – Parte IV (Projeto Lógico de BD Relacional) ATUALIZAÇÃO: alteração de algum valor de atributo de uma tupla. A alteração do valor de uma chave primária segue como se fosse uma remoção seguida de uma posterior inserção. Exemplo: Dada uma relação funcionários definida no Esquema de Dados, pode-se corrigir, atualizar ou mesmo estabelecer alguma informação antes desconhecida, como o salário de João da Silva (Salário: 1200,00). A Álgebra Relacional apresenta uma série de operadores que aplicados a uma determinada relação, ou seja, sobre o seu conjunto de tuplas, resultam em uma outra relação. Os operadores da álgebra relacional podem ser divididos em dois grupos: i). Operadores de Conjuntos: são operadores típicos definidos pela álgebra para conjunto, tais como união, interseção, diferença e produto cartesiano. ii). Operadores de Tabelas: são operadores especiais definidos especialmente para a manipulação de tuplas, tais como, Select, Project e Join. CONJUNTO: operações que se aplicam a duas relações que obedecem à "compatibilidade de união", ou seja, ambas as relações devem apresentar atributos que pertençam respectivamente aos mesmos domínios. Em termos das relações (A e B) usadas nas operações: - ambas podem estar fisicamente armazenadas no mesmo Banco de Dados; ou - estarem fisicamente armazenadas em diferentes Bancos de Dados; ou - uma estar armazenada e a outra ser originária (relação resultante) de uma operação de conjunto anteriormente executada; ou - ambas serem resultantes de operações de conjunto realizadas anteriormente. UNIÃO (∪): o resultado da união de duas relações consiste no conjunto de todas as tuplas das duas relações, porémsem redundância de tuplas. Importante salientar que duas tuplas são semelhantes quantos todos os valores de atributos forem iguais em seus respectivos atributos. Exemplo: União de uma relação dos funcionários da matriz de São Paulo com a relação dos funcionários da filial de Campinas. Existe a possibilidade de execução da União Exclusiva (∪|) na qual as tuplas repetidas não aparecem na resultante. Relação valor Relação A Relação B Relação Resultante tupla x tupla x tupla x Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 8 – Parte IV (Projeto Lógico de BD Relacional) INTERSEÇÃO (∩): o resultado da interseção de duas relações consiste no conjunto de todas as tuplas que pertençam às duas relações. Exemplo: Interseção da relação dos funcionários com idade maior que 35 anos com a relação dos funcionários com salário acima de 1500,00 reais. O resultado será uma relação com os funcionários com idade maior que 35 anos e que recebem acima de 1500,00 reais. DIFERENÇA (-): a diferença entre duas relações é relação resultante formada pelas tuplas que pertencem a uma relação e que não pertencem a outra. Diferença entre as relações A e B (A - B) é o conjunto de tuplas que aparecem na relação A e não aparece na relação B. Diferença entre as relações B e A (B - A) é o conjunto de tuplas que aparecem na relação B e não aparecem na relação A. Exemplo: Diferença da relação dos funcionários com idade maior que 35 anos com a relação dos funcionários com salário acima de 1500,00 reais. O resultado será uma relação com os funcionários com idade maior que 35 anos e que não recebem acima de 1500,00 reais. PRODUTO CARTESIANO (X): aplica-se a duas relações que não necessitam ser "compatíveis para união", resultando em uma relação que apresenta tuplas formadas pela combinação de todas as tuplas de uma relação com todas as tuplas da outra relação (R(A1, A2, ..., An) X S(B1, B2, ..., Bm) = Relação Produto(A1, A2, ..., An, B1, B2, ...,Bm). Exemplo: Produto Cartesiano de uma relação de todos os alunos do terceiro ano de seu curso com uma relação com todas as disciplinas do terceiro ano de seu curso. O resultado será uma relação de todos os alunos do terceiro ano e suas disciplinas. Relação A Relação B Relação Resultantetupla xtupla y tupla x tupla y tupla x tupla y Relação A Relação B Relação B - A Relação A - B Relação B Relação A Relação Resultante Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 9 – Parte IV (Projeto Lógico de BD Relacional) TABELA: operações que se aplicam a quaisquer relações. SELEÇÃO (σ): operação aplicada sobre uma relação de modo a selecionar um sub-conjunto de tuplas (com todos os seus atributos) que satisfaçam a um determinada condição (simples ou composta). O sub-conjunto selecionado forma uma relação resultante temporária. Esta condição aplica-se apenas em uma única relação sendo verificada individualmente para cada tupla da relação. Exemplo: Dado uma relação dos funcionários da empresa, selecionar aqueles que recebem salário > 1000,00 reais. A operação SELECT é denotada por: σ<condição de seleção> (<nome da relação>) onde o símbolo σ é usado para denotar o operador SELECT, e a condição de seleção é uma expressão Booleana especificada sobre atributos da relação especificada. A relação resultante da operação SELECT tem os mesmos atributos da relação especificada em <nome da relação>. A expressão Booleana especificada em <condição de seleção> é construída a partir de cláusulas da forma: <nome de atributo> <operador de comparação> <valor constante>, ou <nome de atributo> <operador de comparação> <nome de atributo> Onde <nome de atributo> é o nome de um atributo da <nome da relação>, <operador de comparação> é normalmente um dos operadores relacionais {=, <, ≤, ≥, ≠} e <valor constante> é um valor constante. As cláusulas podem ser utilizadas em conjunto com os operadores lógicos {AND, OR NOT}, seguindo a Lógica Booleana, para formar uma condição de seleção composta. Exemplo, suponha que se deseja selecionar as tuplas de todos os empregados que ou trabalham no departamento 4 e faz mais de 2500 ou trabalha no departamento 5 e faz mais que 3000. Neste caso, pode-se especificar a consulta da seguinte forma: σ(NDEP = 4 AND SALÁRIO > 2500) OR (NDEP = 5 AND SALÁRIO > 3000) (EMPREGADO) O operador SELECT é comutativo; isto é, σ<cond1> (σ<cond2> (R))= σ<cond2> (σ<cond1> (R)) Assim, uma seqüência de SELECTs pode ser aplicado em qualquer ordem. Além disso, pode-se sempre trocar operadores SELECT em cascata com a conjuntiva AND; isto é: σ<cond1> (σ<cond2> (...σ<condn> (R) ...))=σ<cond1> AND <cond2> AND ... AND <condn>(R) Relação Origem Relação Resultante Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 10 – Parte IV (Projeto Lógico de BD Relacional) PROJEÇÃO (π atributos): operação aplicada sobre uma relação de modo a selecionar os atributos de uma relação de acordo com uma lista de atributos oferecida. Os atributos são exibidos na mesma ordem que aparecem na lista. Como resultado tem-se uma relação onde não existem repetições de tuplas. Exemplo: Projetar apenas o nome, idade e salário de uma relação de funcionários. A relação resultante é uma tabela sem repetições. Caso na lista de atributos estiver incluída a chave primária da relação então tem-se certeza que não há repetições. A forma geral do operador PROJECT é: π <lista de atributos> (<nome da relação>) onde π é o símbolo usado para representar o operador PROJECT e <lista de atributos> é uma lista de atributos da relação especificada por <nome da relação>. A relação resultante tem apenas os atributos especificados em <lista de atributos> e aparecem na mesma ordem que aparecem na lista. Assim, o grau é igual ao número de atributos em <lista de atributos>. Convém salientar que, caso a lista de atributos não contenha atributos chaves, então é provável que tuplas duplicadas apareçam no resultado. A operação PROJECT remove implicitamente quaisquer tuplas duplicadas, tal que o resultado da operação PROJECT seja um conjunto de tuplas e assim, uma relação válida. O número de tuplas na relação resultante sempre será igual ou menor que a quantidade de tuplas na relação original. Note-se que: π <lista1> (π <lista2> (R)) = π <lista1> (R) caso <lista2> contém os atributos de <lista1>; caso contrário, o lado esquerdo da igualdade acima estará incorreta. A comutatividade não é válida para PROJECT. JUNÇÃO (⌧ condição): operação utilizada para combinar tuplas relacionadas (via chave primária/chave estrangeira) de duas ou mais relações de modo a estabelecer virtualmente uma única tupla. Esta combinação é realizada de acordo com uma condição indicada. Exemplo: Junção da relação de peças fornecidas com a relação de fornecedores (em comum as duas relações possuem o código do fornecedor). A relação resultante terá tuplas contendo o código do fornecedor, demais atributos do fornecedor e os atributos de peça, estando juntas apenas as peças e seus respectivos fornecedores. Tuplas cujos valores dos atributos join são null não aparecem no resultado. A operação Junção (“join”) onde a condição for uma expressão explícita de comparação qualquer (=, <, <=, >, >=, <>) é denominada de Theta Join (θθθθ⌧ condição), exemplo: fornecedor.código <> peça.código. Quando o operador de comparação for o de igualdade, teremos o Relação Origem A tr ib ut o a A tr ib ut o b A tr ib ut o c A tr ib ut o a A tr ib ut o b A tr ib ut o c Relação Resultante a, b, c Relação A Relação B Relação Resultante Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 11 – Parte IV (Projeto Lógico de BD Relacional) Equi Join (=⌧ condição) (exemplo: fornecedor.código = peça.código) Como resultado de um Equi Join teremostuplas apresentando pares de atributos com valores idênticos. O Natural Join (*⌧ condição) consiste em uma operação na qual do cruzamento de uma chave primaria e uma chave estrangeira, apenas a chave primária aparece na relação resultante. Quando os atributos sobre os quais se aplicam o Natural Join apresentarem o mesmo nome em ambas as relações, a condição do Join pode ser totalmente omitida. A forma geral da operação JOIN sobre duas relações R(A1, A2, ..., An) e S(B1, B2, ..., Bm) é: R ⌧ S <condição join> O resultado de JOIN é uma relação Q com n+m atributos Q(A1, A2, ..., An, B1, B2, ..., Bm) nesta ordem; Q tem um tupla para cada combinação de tuplas uma de R e uma de S onde quer que a combinação satisfaça a condição join. Esta é a principal diferença entre CARTESIAN PRODUCT e JOIN; em JOIN, apenas combinações de tuplas que satisfazem a condição join é que aparecerá no resultado, já no CARTESIAN PRODUCT, todas as combinações de tuplas são incluídas no resultado. A condição join é especificada sobre atributos de R e de S, e é avaliada para cada combinação de tuplas. Uma condição join tem a forma: <condição> AND <condição> AND ... AND <condição>, onde cada condição é da forma Ai θ Bj, Ai é um atributo de R, Bj é um atributo de S, Ai e Bj têm o mesmo domínio e θ é um dos operadores de comparação {=, <, ≤, >, ≥, ≠}. Outras formas explícitas de variações da operação de “join” são: • LEFT OUTER JOIN : Exemplo - A LEFT OUTER JOIN B permite a junção da relação A com a relação B, sendo colocadas nas relação resultante todas as tuplas da relação A mesmo que não tenham correspondentes na relação B. • RIGHT OUTER JOIN : Exemplo - A RIGHT OUTER JOIN B permite a junção da relação A com a relação B, sendo colocadas nas relação resultante todas as tuplas da relação B mesmo que não tenham correspondentes na relação A. • FULL OUTER JOIN : Exemplo - A FULL OUTER JOIN B permite a junção da relação A com a relação B, sendo colocadas nas relação resultante todas as tuplas da relação A e da relação B mesmo que não tenham correspondentes. Na relação resultante de um Outer Join, os atributos de tuplas que não possuem correspondente são apresentados com o valor nulo (NULL). Exemplo: Fcodigo Fnome Fcategoria Fcidade Cod_Peça Preço Qtde. F1 Santos 20 Piracicaba 12 1,40 230 F2 Machado 10 São Paulo 45 3,20 450 F3 Almeida 30 São Paulo 71 5,00 78 F4 Ferreira 20 Campinas NULL NULL NULL F5 Rodrigues 30 São Carlos NULL NULL NULL Tabela Resultante da Operação:- Fornecedor LEFT OUTER JOIN Peça Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 12 – Parte IV (Projeto Lógico de BD Relacional) 6 - MAPEAMENTO (M-ER P/ M-RELACIONAL) O Modelo Entidade Relacionamento é responsável por realizar uma representação dos dados de uma determinada aplicação a um nível mais conceitual, um pouco distante da forma como os seus elementos serão efetivamente implementados. Os modelos baseados em registros, entre eles o relacional, fornece uma representação dos dados de forma mais próxima como estes se encontrarão quando forem definidos os arquivos para o BD. Os passos necessários para se realizar o mapeamento da modelagem conceitual (representado pelo DER) para a modelagem lógica são: • Regra 1: Cada Tipo de Entidade é mapeado em uma Relação. • Regra 2: Os Tipos de Atributos (mono-valorados e não compostos) de Tipos de Entidades presentes no DER são mapeados em Atributos de Relação. Os Identificadores são mapeados em Chaves Primárias. • Regra 3: Para cada Tipo de Entidade Fraca é mapeado uma Relação contendo todos os Atributos referentes aos Tipos de Atributos da Entidade (inclusive identificador) e acrescido de uma chave estrangeira referente à chave primária do Tipo de Entidade Forte. Os Tipos de Entidades Fracas somente poderão ser mapeados após os Tipos de Entidades Fortes. MER M-REL a b c a b c E E a b c a f d E1 d E E1 f Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 13 – Parte IV (Projeto Lógico de BD Relacional) • Regra 4: Para Tipos de Relacionamentos Unários e Binários (cardinalidade máxima 1:1 e cardinalidade mínima 1:1) existe a possibilidade de mapeamento dos Tipos de Entidades envolvidos em uma única Relação (contrariando a regra 1). • Regra 5: Tipos de Relacionamentos Unários e Binários (cardinalidade máxima 1:1 e cardinalidade mínima 0:0, 0:1 ou 1:0) são mapeados através da colocação da chave primária (pertencente a uma das relações envolvidas) na forma de chave estrangeira na outra relação. Preferivelmente a relação do lado 0 (ou seja, aquela que mapeia o Tipo de Entidade com cardinalidade mínima 1) deve receber a chave primária da outra relação. • Regra 6: Tipos de Relacionamentos Unários e Binários (cardinalidade máxima 1:N ou N:1 e cardinalidade mínima 1:1, M:1 ou 1:M) são mapeados através da colocação da chave primária de uma das relações envolvidas como chave estrangeira na outra relação. Obrigatoriamente a relação do lado N (ou seja, aquela que mapeia o Tipo de Entidade com cardinalidade máxima 1) deve receber a chave primária da outra relação. a b c a b c d f E1E2 d E2 E1 f 1:1 1:1 R a b c E1_R d E2 E1 f 0:1 1:1 R f d a a b c E1_R d E2 E1 f 1:N 1:1 R f d a Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 14 – Parte IV (Projeto Lógico de BD Relacional) • Regra 7: Tipos de Relacionamentos Unários e Binários (cardinalidade máxima 1:N ou N:1 e cardinalidade mínima 0:0, 1:0, 0:1, 0:M ou M:0) são mapeados em relações “especiais” contendo uma chave primária criada artificialmente e as chaves primárias das duas relações envolvidas. Opcionalmente a chave primária pode ser formada pela composição das duas chaves estrangeiras. Outra forma de mapeamento é utilizar a regra 6 tendo como diferença um dos lados do Tipo de Relacionamento que terá a cardinalidade máxima 0 e não 1. • Regra 8: Tipos de Relacionamentos Unários e Binários (cardinalidade máxima M:N e independente da cardinalidade mínima) são mapeados em relações “especiais” contendo uma chave primária criada artificialmente e as chaves primárias das relações envolvidas. Opcionalmente a chave primária pode ser formada pela composição das chaves estrangeiras. • Regra 9: Tipos de Relacionamentos Ternários (cardinalidade máxima M:N:P e independente da cardinalidade mínima) e Tipos de Relacionamentos Quaternários (cardinalidade máxima M:N:P:Q e independente da cardinalidade mínima) são mapeados em relações “especiais” contendo uma chave primária criada artificialmente e as chaves primárias das relações envolvidas. Opcionalmente a chave primária pode ser formada pela composição das chaves estrangeiras. • Regra 10: Os Tipos de Atributos (mono-valorados e não compostos) de Tipos de Relacionamentos (Unários, Binários, Ternários ou Quaternários) presentes no DER são mapeados em Atributos das Relações que respectivamente mapeiam os Tipos de Relacionamentos. a b c R d E2 E1 f 0:N 1:1 R r f a a b c R d E2 E1 f 0:N 1:M R r f a a b c R d E2 E1 f R a b f h E3 h Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 15 – Parte IV (Projeto Lógico de BD Relacional) • Regra 11: Os Tipos de Atributos (multi-valorados) de Tipos de Entidades e Tipos de Relacionamentos (Unários, Binários, Ternários ou Quaternários) presentes no DER são mapeados de duas formas: • Regra 11.1: Não tendo conhecimento da quantidade de ocorrências envolvidas então o mapeamento estabelece uma Relação “especial” contendo a chave primária da relaçãoenvolvida (isto é, a relação mapeada do Tipo de Entidade a que pertence o Tipo de Atributo Multi- valorado) e o próprio Atributo que representa o Tipo de Atributo Multi- valorado. Ambos os atributos podem formar a chave primária composta da relação. Caso o Tipo de Atributo Multi- valorado seja composto então seu componentes são incluídos na relação estabelecida. • Regra 11.2: Tendo conhecimento da quantidade de ocorrências envolvidas então estabelece-se esta quantidade de atributos na relação que representa o Tipo de Entidade do respectivo Tipo de Atributo. (opção menos aconselhável) • Regra 12: As Abstrações de Generalização/Especialização são mapeadas de seis formas: • Regra 12.1: Os Tipos de Entidades genéricos são mapeados com seus Tipos de Atributos, Identificadores e os predicados das especializações (que são também atributos de relação). Os Tipos de Entidades específicos são mapeados com seus Tipos de Atributos e o identificador do Tipo de Entidade genérico. Bom p/ especialização exclusiva. a b c a b E E E_c a c a b c E a b c1 c2 c3 E a b EG a b p EG E1 E2 fd p a d E1 a f E2 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 16 – Parte IV (Projeto Lógico de BD Relacional) • Regra 12.2: Os Tipos de Entidades genéricos são mapeados com seus Tipos de Atributos, Identificadores mais sem os predicados das especializações. Os Tipos de Entidades específicos são mapeados com seus Tipos de Atributos e o identificador do Tipo de Entidade genérico. Bom p/ especialização com sobreposição. Opcionalmente, o predicado pode ser mapeado como se fosse um Tipo de Atributo Multi-Valorado (regra 11). • Regra 12.3: Os Tipos de Entidades genéricos e os Tipos de Entidades específicos são mapeados em uma mesma Relação (mapeamento de Tipos de Atributos, Identificadores e o predicado da especialização). Bom quando os Tipos de Entidades específicos não possuírem muitos Tipos de Atributos ou quando alguns Tipos de Entidades específicos não possuírem Tipos de Atributos. • Regra 12.4: Os Tipos de Entidades genéricos e os Tipos de Entidades específicos são mapeados em uma mesma Relação (mapeamento de Tipos de Atributos, Identificadores mas sem o predicado da especialização). Bom quando os Tipos de Entidades específicos não possuírem muitos Tipos de Atributos ou quando alguns Tipos de Entidades específicos não possuírem Tipos de Atributos e p/ especialização com sobreposição. a b EG a b EG E1 E2 fd p a d E1 a f E2 a b EG E1 E2 fd p a b p d f EG_E1_E2 a b EG E1 E2 fd p a b d f EG_E1_E2 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 17 – Parte IV (Projeto Lógico de BD Relacional) • Regra 12.5: Os Tipos de Entidades genéricos e os Tipos de Entidades específicos são mapeados em uma mesma Relação (mapeamento de Tipos de Atributos, Identificadores e de cada situação do predicado da especialização que terão valores sim/não). • Regra 12.6: Os Tipos de Entidades específicos são mapeados em Relações (mapeamento dos Tipos de Atributos próprios, Tipos de Atributos do Tipo de Entidade genérico e o Identificador). Bom quando é freqüente o interesse do usuário pela totalidade da informações de uma Entidade. Não permite conhecer a especialidade de uma Entidade. Total Parcial Exclusiva Sobreposição Regra 12.1 Bom Ideal Bom Nunca Regra 12.2 Bom Ideal Nunca Bom Regra 12.3 Ideal Bom Bom Nunca Regra 12.4 Ideal Bom Ideal Bom Regra 12.5 Bom Bom Nunca Bom Regra 12.6 Bom Nunca Bom Nunca • Observações: Para critérios estabelecidos pelo usuários (isto é, não existem predicados) as regras 12.2 e 12.6 são aconselhadas. Identificadores de Tipos de Entidades específicos são mapeados como chaves candidatas. a b EG E1 E2 fd p a b p1 p2 p3 d f EG_E1_E2 a b EG a b d E1 E1 E2 fd p a b f E2 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 18 – Parte IV (Projeto Lógico de BD Relacional) • Regra 13: Na Abstração de Composição/Decomposição, o mapeamento dos Tipos de Entidades compostos e os Tipos de Entidades componentes segue as seguintes opções: • Regra 13.1: Caso a Abstração seja de composição física então o mapeamento da Abstração é o mapeamento dos Tipos de Relacionamentos de composição que estão implicitamente representados pela Abstração. Dependendo da cardinalidade mínima destes Tipos de Relacionamentos utiliza-se a regra 6 ou a regra 7. Outra opção é mapear a Abstração em um Relação contendo as chaves primárias das Relações envolvidas (isto é, relações mapeadas dos Tipos de Entidade compostos e componentes) e opcionalmente atributos de quantidade para cada chave primária. • Regra 13.2: Caso a Abstração seja de composição lógica então o mapeamento da Abstração é o mapeamento dos Tipos de Relacionamentos de composição com cardinalidades máximas N:M, indenpendente da cardinalidade mínima destes Tipos de Relacionamentos, ou seja utiliza-se a regra 8. • Regra 13.3: Caso a Abstração seja de composição com dependência existencial então o mapeamento da Abstração é o mapeamento dos Tipos de Relacionamentos de composição ligados a Tipos de Entidades Fracas, ou seja utiliza-se a regra 3. a b EG E1 E2 fd h m (f) física a b EG E1 E2 fd h m r1 r2 1:1 1:1 ?:N ?:N a b EG E1 E2 fd h m (l) lógica a b EG E1 E2 fd h m r1 r2 ?:M ?:M ?:N ?:N a b EG E1 E2 fd h m (cd) com dependência a b EG E1 E2 fd h m r1 r2 Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 19 – Parte IV (Projeto Lógico de BD Relacional) • Regra 13.4: Caso a Abstração seja de composição sem dependência existencial então o mapeamento da Abstração seque a regra 13.1 ou 13.2, dependendo da Abstração ser física ou lógica. • Observação: A Abstração de Composição ser total ou parcial não muda a estrutura do mapeamento mas sua interpretação. • Regra 14: As Abstrações de Agregação são mapeadas em relações “especiais” contendo uma chave primária criada artificialmente e as chaves primárias das duas relações envolvidas. Esta regra tem prioridade sobre as regras 4, 5, 6 e 7. A Abstração poderá ter seus próprios Tipos de Atributos que devem ser mapeados para a Relação que representa a Abstração. O Tipo de Relacionamento Unário ou Binário englobado pela Agregação poderá ser mapeado pela Relação da Agregação caso seus Tipos de Atributos possam ser repassados para a Agregação. Exemplo: Agregação Aula entre T.E. Professor (Nome), T.R. Ministra (Livro Texto) e T.E. Disciplina (Sigla). A Agregação Aula possui T.A . Data, Horário e Sala. • Regra 15: As Abstrações de Derivação são mapeadas em Atributos que terão seus valores estabelecidos por cálculos executados por procedimentos pré-programados. • Regra 16: As Abstrações de Divisão (Agregação de Atributos) são mapeadas em Atributos que correspondem aos Tipos de Atributos componentes. • Regra 17: As Abstrações de Tempo são mapeadas em relações “especiais” contendo a chave primária da relação envolvida, o próprio atributo temporal e dois atributos que determinam o intervalo de tempo válido para cada valor do atributo temporal. a c X d E2 E1 f R x a f h m h X m a b c a b E E E_c a c Tempo Tempo Início Fim Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 20 – Parte IV (Projeto Lógico de BD Relacional) 7 - NORMALIZAÇÃO É de fundamental importância definir esquemas relacionais de tal forma que a consistência nos dados seja mantida em todo o BD.O controle da consistência dos dados pode ser exercido pelo Gerenciador, pelo aplicativo, ou pela própria estrutura do BD. De uma forma geral o controle de consistência obtido pela estrutura do BD é mais eficiente pois normalmente não implica em perda de desempenho por parte de Gerenciador durante a sua execução. O Modelo Relacional, devido à sua natureza inerentemente formal, dispõe de um ferramental conceitual que permite modelar diversas formas de controle de consistência. O controle através da própria estrutura do BD é obtido no Modelo Relacional construindo-se as relações segundo regras que garantam a manutenção de certas propriedades. As relações que atendem a um determinado conjunto de regras diz-se estarem em uma determinada Forma Normal. O processo de Normalização pode ser considerado como a aplicação de uma série de regras, que constituem as Formas Normais que irão provocar a decomposição de esquemas de dados insatisfatório de algumas relações, em novas relações. A Normalização também permite ao programador controlar o quanto da consistência é garantida pela maneira de estrutura do BD, e quanto deve ser responsabilidade dos aplicativos e/ou do SGBD. No entanto deve ser realizada alguma ponderação: normalizar demais diminui a eficiência dos aplicativos e de menos abre flancos para inconsistências. Uma das maneiras de controlar a consistência é através de Dependências Funcionais existentes entre os atributos armazenados. A Dependência Funcional baseia-se no reconhecimento que os valores de alguns atributos podem ser determinados a partir de outros pelo SGBD, e portanto deve ser avaliado e informado pelo programador ao BD. Se o valor de um conjunto de atributos A permite descobrir o valor de um outro conjunto de atributos B, diz-se que A determina funcionalmente B, ou que B depende de A. A representação gráfica para esta dependência é a seguinte : A → B. As dependências funcionais são informações semânticas fornecidas pelo projetista, portanto fazem parte do Esquema de Dados, ou seja, refletem a intenção do projetista no instante em que realizou a definição do BD. Para um controle da consistência é necessário conhecerem-se todas as dependências funcionais. Sendo uma informação semântica, todas deveriam ser fornecidas pelo projetista. No entanto, é possível inferir dependências funcionais a partir de outras já conhecidas. Desta forma, partindo-se de um conjunto básico, é possível ampliá-lo através de um conjunto de 6 regras de inferência. É importante observar que não se pode inferir todas, apenas aquelas que podem ser obtidas através desse conjunto de regras. Assim, elaborar um conjunto inicial o mais completo possível é de importância fundamental para se controlar consistência adequadamente. As 6 regras de inferência para as Dependências Funcionais são as seguintes: a). Reflexiva : Se A ⊃ B ==> B → A b). Aumentativa: Se A → B ==> AC → B c). Separação: Se A → BC ==> A → B, A → C d). Aditiva: Se A → B, A → C ==> A → BC e). Transitiva: Se A → B, B → C ==> A → C f). Pseudo-Transitiva: Se A → B, BC → D ==> AC → D Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 21 – Parte IV (Projeto Lógico de BD Relacional) As Formas Normais apresentam-se identificadas através de números e devem ser seguidas segundo a seqüência abaixo: Importante: Seguindo-se as regras de mapeamento estabelecidas anteriormente pode-se levar as relações de um Banco de Dados até a 3FN ou FNBC (Forma Normal de Boyce-Codd). Primeira Forma Normal Para o Modelo Relacional a Forma Normal mais importante consiste da Primeira Forma Normal, pois é considerada parte da própria definição de uma relação. Uma relação se encontra na Primeira Forma Normal (1FN) se todos os domínios de atributos possuem apenas valores atômicos (indivisível), e que os valores de cada atributo na tupla seja um valor simples. A 1FN não permite a construção de relações que apresentem atributos compostos e nem possibilita a existência de atributos multivalorados em suas tuplas. Os únicos valores de atributos permitidos devem ser simples e atômicos. Para Normalizar para a Primeira Forma Normal deve-se: A). Atributos Compostos - cada um dos atributos compostos (ou não atômicos) devem ser "divididos" em seus atributos componentes. B). Atributos Multi-valorados - b.1) Quando a quantidade de valores for pequena e conhecida a priori, substitui-se o atributo multivalorado por um conjunto de atributos de mesmo domínio, cada um monovalorado representando uma ocorrência do valor. b.2) Quando a quantidade de valores for muito variável, desconhecida ou grande, retira-se da relação o atributo multivalorado, e cria-se uma nova relação que tem o mesmo conjunto de atributos chave, mais o atributo multivalorado também como chave, mas tomado como monovalorado. A primeira Forma Normal é uma das maneiras de controlar a consistência através da própria estrutura do sistema, sendo também fundamental para a conceituação do sistema, e é exigida por todos os SGBD’s Relacionais comuns. O SGBD Relacional e a teoria correspondente que não 1 F N F N B C 2 F N 3 F N M a i o r I m p a c t o M e n o r I m p a c t o 4 F N 5 F N Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 22 – Parte IV (Projeto Lógico de BD Relacional) exigem a 1FN são designados como NF2 ("Non First Non Form"). Relações que não estão na 1FN são chamadas de "Nested Relations" ( relações dentro de relações). Exemplo 1: Aluno (Não 1FN) Nome Idade Data Nasc. Disciplinas Nro. Matr. Após transformação temos: Aluno (1FN) Nome Idade Dia Nasc. Mês Nasc. Ano Nasc. Nro. Matr. Aluno_Disciplina (1FN) Nome Disciplina Exemplo 2: Paciente (Não 1FN) Nome Graus das Lentes Tipo Sangüíneo Após transformação temos: Paciente (1FN) Nome GrauEsq GrauDir Tipo Sangüíneo Segunda Forma Normal Uma relação está na Segunda Forma Normal (2FN) quando: - estiver na Primeira Forma Normal e; - todos os atributos que não participam da chave primária são dependentes funcionalmente 1 (diretos ou transitivos) de toda a chave primária. 1 Dependência Funcional: Se o valor de uma Atributo A permite descobrir o valor de um outro Atributo B, dizemos que A determina funcionalmente B (A → B). Exemplo: Nro_Matrc → Nome_aluno, idade, curso, e assim sempre que o Nro_Matr se repetir tem-se a repetição de Nome_aluno, idade e curso. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 23 – Parte IV (Projeto Lógico de BD Relacional) Para Normalizar para a Segunda Forma Normal deve-se : a). Verificar os grupos de atributos que dependem da mesma parte da chave; b). Retirar da relação todos os atributos de um desses grupos; c). Criar uma nova relação, que contem esse grupo como atributo não chave, e os atributos que determinam esse grupo como chave; d). Repetir estes procedimentos para cada grupo, até que todas as relações somente contenham atributos que dependam de toda a chave primária. Exemplo: Ministra (2FN) Professor Sigla da Disciplina Livro Texto Turma (Não 2FN) Número da Turma Sigla da Disciplina Sala de Aula Nro. Horas Com a seguinte lista de Dependências Funcionais: Professor → Sigla da Disciplina ( Cada professor pode lecionar apenas uma disciplina) Sigla da Disciplina → Livro Texto, Nro. Horas Número da Turma, Sigla da Disciplina → Sala de Aula Relações que não estão na 2FN podem apresentar problemas de inconsistência devido a duplicidade dos dados e a perda de dados em operações de remoção/alteração (Exemplo: A disciplina TGS não sendo oferecida para nenhuma turma este ano/semestre leva a remoção da quinta tupla desta relação, o que ocasionará a perda da quantidade de horas da disciplina TGS pois esta informação não se encontra em outra relação ou tupla) Número da Turma Sigla da Disciplina Sala de Aula Nro. Horas 1 TLP II 21 4 2 TLPII 23 4 1 SBD 12 3 2 SBD22 3 1 TGS 15 4 Após a transformação temos: Ministra (2FN) Professor Sigla da Disciplina Livro Texto Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 24 – Parte IV (Projeto Lógico de BD Relacional) Turma (2FN) Número da Turma Sigla da Disciplina Sala de Aula Disciplina (2FN) Sigla da Disciplina Nro. Horas Terceira Forma Normal Um relação é considerada estar na Terceira Forma Normal (3FN), quando: - estiver na Segunda Forma Normal e; - todos os atributos que não participam da chave primária são dependentes não transitivos de toda a chave primária. Para Normalizar para a Terceira Forma Normal deve-se: a). Verificar um grupo de atributo que depende não diretamente da chave; b). Retirar da relação esse grupo de atributos; c). Criar uma nova relação que contem esse grupo de atributos, e defina com chave, os atributos dos quais esse grupo depende diretamente; d). Repetir os passos anteriores até que todos os atributos restantes na relação original dependam diretamente de toda a chave primária. Exemplo: Ministra (Não 3FN) Professor Sigla da Disciplina Livro Texto Turma (3FN) Número da Turma Sigla da Disciplina Sala de Aula Disciplina (3FN) Sigla da Disciplina Nro. Horas Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 25 – Parte IV (Projeto Lógico de BD Relacional) Com a seguinte lista de Dependências Funcionais: Professor → Sigla da Disciplina ( Cada professor pode lecionar apenas uma disciplina) Sigla da Disciplina → Livro Texto, Nro. Horas Número da Turma, Sigla da Disciplina → Sala de Aula Relações que não estão na 3FN podem apresentar problemas de inconsistência devido a duplicidade dos dados e a perda de dados em operações de remoção/alteração (Exemplo: A licença da Professora Sandra pode levar a remoção da quinta tupla desta relação o que ocasionará a perda do livro texto da disciplina TEL pois esta informação não se encontra em outra relação ou tupla) Professor Sigla da Disciplina Livro Texto André TLP II A João SBD D Maria ADB C Mário SBD D Sandra TEL E Após a transformação temos: Ministra (3FN) Professor Sigla da Disciplina Turma (3FN) Número da Turma Sigla da Disciplina Sala de Aula Disciplina (3FN) Sigla da Disciplina Livro Texto Nro. Horas Forma Normal de Boyce-Codd Um relação é considerada estar na Forma Normal de Boyce-Codd (FNBC), quando: - estiver na Primeira Forma Normal ; e - Para cada chave candidata, todos os atributos que não participam da chave candidata são dependentes não transitivos de toda a chave candidata. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 26 – Parte IV (Projeto Lógico de BD Relacional) Para Normalizar para a Forma Normal de Boyce-Codd segue-se os mesmos passos da Terceira Forma Normal. Para Relações que tenham apenas uma chave candidata que corresponde a própria chave primária, a FNBC reduz-se à 3FN. Exemplo: Aloca (Não FNBC) Sala Professor Sigla Disc. Dia_Hora Nro. Alunos Chave Primária: Sala , Professor, Sigla Disc. Chave Candidata: Sala, Sigla Disc. Com a seguinte lista de Dependências Funcionais: Sala, Professor, Sigla Disc. → Dia_Hora Sala, Sigla Disc. → Nro. Alunos Após a transformação temos: Aloca1 (FNBC) Sala Professor Sigla Disc. Dia_Hora Aloca2 (FNBC) Sigla_Disc. Sala Nro. Alunos Quarta Forma Normal Uma relação está na Quarta Forma Normal (4FN) quando: - se não possuir dois ou mais Atributos multi-valorados independentes.2 Para Normalizar para a Quarta Forma Normal deve-se : a). Colocar cada atributo multi-valorado em uma relação, como na 1FN - b.2 2 Multi-Dependência Funcional: Se cada valor de uma Atributo A permite descobrir um conjunto de possíveis valores de um outro Atributo B, dizemos que A determina multi-funcionalmente B (A "" B). Exemplo: Nome_Prof "" Disciplina, e assim sempre que o Nome_Prof for pesquisado poderá ser obtido os nomes de suas disciplinas. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 27 – Parte IV (Projeto Lógico de BD Relacional) Observação: Para se verificar a 4FN a relação deve ter no mínimo três atributos. Exemplo: Professor (Não 4FN) Nome Disciplina Orientado Com a seguinte lista de Multi-Dependências Funcionais: Nome "" Disciplina (Cada professor pode ministrar várias disciplinas) Nome "" Orientado (Cada professor pode orientar vários alunos) Relações que não estão na 4FN podem apresentar problemas de inconsistência devido a duplicidade dos dados. Exemplo: Nome Disciplina Orientado Luiz SBD Paulo Luiz SBD José João TLP I Antônio João TLP II Antônio João ADM Antônio Ana TEL Silvio Após a transformação temos: Professor_disciplina (4FN) Nome Disciplina Professor_orientado (4FN) Nome Orientado Quinta Forma Normal O conjunto de relações do BD está na Quinta Forma Normal (5FN) quando: - Dado o conjunto de multi-dependências funcionais do BD, não se estabelece um ciclo de multi-dependência entre pelo menos 3 atributos, ou os Atributos de um ciclo estão na mesma relação. Para Normalizar para a Quinta Forma Normal deve-se : Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 28 – Parte IV (Projeto Lógico de BD Relacional) a). Colocar todos os atributo multi-valorado que pertencem à um mesmo ciclo em uma relação. Exemplo: Professor_apostila (Não 5FN) Nome Apostila Professor_disciplina (Não 5FN) Nome Disciplina Disciplina_apostila (Não 5FN) Apostila Disciplina Com a seguinte lista de Multi-Dependências Funcionais : Nome "" Disciplina (Cada professor pode ministrar várias disciplinas) Nome "" Apostila (Um grupo de professor pode elaborar uma apostila) Disciplina "" Apostila (Cada disciplina pode ter um certo número de apostilas) Apostila "" Nome (Cada apostila pode ser elaborada por vários professores) Após a transformação temos: Atividade (5FN) Nome Disciplina Apostila O processo de normalização é aplicado em uma relação por vez. Durante o processo a relação vai sendo "quebrada", criando-se outras relações. Geralmente o processo de normalização é realizado em etapas começando através das formas normais menos rígidas, e depois através de refinamentos sucessivos, chegar até uma normalização considerada satisfatória para a aplicação. A decisão de normalizar ou não uma relação é um compromisso entre garantir a eliminação de inconsistências no BD e alcançar a eficiência de acesso. A normalização para formas apoiadas em dependências funcionais evita inconsistências, usando para isso a própria construção do BD. Se a mesma consistência puder ser garantida pelos aplicativos, a normalização correspondente não precisa ser realizada. Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 29 – Parte IV (Projeto Lógico de BD Relacional) 8 - SGBDR (CARACTERÍSTICAS) Característica 1 (Estrutura da Informação): Toda informação é apresentada explicitamente em nível lógico e exatamente de uma maneira - valores em tabelas. Característica 2 (Acesso Garantido): Toda informação tem a garantia de ser logicamente acessível recorrendo-se a operações e uma combinação de nomes de tabelas, valores de atributos e nomes de colunas. Característica 3 (Tratamento Sistemático dos Valores Nulos): Deve-se permitir a representação de valores nulos de um modo homogêneo para qualquer domínio. Zero não é valor nulo. Característica 4 (Meta-Dados Representado por tabelas): Todas as informações geradas pela execução dos comandos da DDL formam o Esquema de Dados, que é internamente mantido pelo SGBDR na forma de Tabelas. Característica 5 (Sublinguagem Detalhada): Deve haver uma linguagem expressa por uma sintaxe bem definida e que seja detalhada para suportar: - Definição de Dados - Definição de Visão - Manipulação de Dados (interativo ou programado) - Restrições de Integridade - Autorização - Limites de Transações. Característica 6 (Atualização de Visão): Todasas Visões são passíveis de atualizações, inclusões e remoções com o efeito de melhoria dos serviços do BD. Característica 7 (Operações de Alto Nível): Inserções, Atualizações e eliminações tratam relações básicas, relações derivadas e relações do Esquema de forma homogênea possibilitando um aprendizado mais rápido e um tratamento uniforme das otimizações internas. Característica 8 (Independência de Dados Físicos): Deve existir um limite claro e preciso entre os aspectos lógicos e os aspectos físicos e de desempenho, de modo que, os programas aplicativos permaneçam inalterados sempre que forem realizadas mudanças na representação em memória ou nos métodos de acesso. Nome Data Criador Comentário Tabela Nome Nome Tipo de Coluna Tabela Dados Nome Nome Unívoco Coluna Tabela Nome Nome Data Criador Tabela Programa Tabelas Colunas Índices Aplicativos Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 30 – Parte IV (Projeto Lógico de BD Relacional) Característica 9 (Restrições Semânticas Embutidas): As Restrições de Integridade Semântica devem ser definidas pela Sub-linguagem de Banco de Dados e armazenada no dicionário de dados, não nos programas aplicativos. Característica 10 (Distribuição Transparente): Aplicativos permanecem inalterados quando a distribuição de dados é introduzida ou os dados são redistribuídos, devido a flexibilidade e poder dos operadores relacionais. Característica 11 (Não Subversão): Não pode existir nenhuma interface, linguagem ou outra forma de interação que permita a qualquer usuário subverter a integridade e segurança do BDR. 9 – EXEMPLO DE ÁLGEBRA RELACIONAL EMPREGADO PNOME MNOME SNOME NSS DATANASC ENDEREÇO SEXO SALARIO NSSSUPER NDEP John B Smith 123456789 09-JAN-55 R. A, 1 M 3000 333445555 5 Franklin T Wong 333445555 08-DEZ-45 R. B, 2 M 4000 888665555 5 Alícia J Zelaya 999887777 19-JUL-58 Av. C, 3 F 2500 987654321 4 Jennifer S Wallace 987654321 20-JUN-31 Trav. D, 4 F 4300 888665555 4 Ramesh K Narayan 666884444 15-SET-52 R. E, 5 M 3800 333445555 5 Joyce A English 453453453 31-JUL-62 R. F, 6 F 2500 333445555 5 Ahmad V Jabbar 987987987 29-MAR-59 Av G, 7 M 2500 987654321 4 James E Borg 888665555 10-NOV-27 Av H, 8 M 5500 null 1 DEPARTAMENTO DNOME DNÚMERO SNNGER DATINICGER Pesquisa 5 333445555 22-MAI-78 Administrativo 4 987654321 01-JAN-85 Gerencial 1 888665555 19-JUN-71 PROJETO PNOME PNÚMERO PLOCALIZAÇÃO DNUM ProdutoX 1 Bellaire 5 ProdutoY 2 Sugarland 5 ProdutoZ 3 Houston 5 Automação 10 Stafford 4 Reorganização 20 Houston 1 Beneficiamento 30 Stafford 4 DEPENDENTE NSSEMP NOMEDEPENDENTE SEXO DATANIV RELAÇÃO 333445555 Alice F 05-ABR-76 FILHA 333445555 Theodore M 25-OUT-73 FILHO 333445555 Joy F 03-MAI-48 ESPOSA 987654321 Abner M 29-FEV-78 MARIDO 123456789 Michael M 01-JAN-78 FILHO 123456789 Alice F 31-DEZ-78 FILHA 123456789 Elizabeth F 05-MAI-57 ESPOSA Consulta 1: Encontrar o nome e o endereço de todos os empregados que trabalham para o departamento 'Pesquisa'. • PESQUISA_DEPTO←σ←σ←σ←σ DNOME = 'Pesquisa' (DEPARTAMENTO) • PESQUISA_DEPTO_EMPS←←←←(PESQUISA_DEPTO ⌧⌧⌧⌧ DNÚMERO = NDEP EMPREGADO) • RESULT←π←π←π←π PNOME, SNOME, ENDEREÇO (PESQUISA_DEPTO_EMPS) Consulta 2: Para todo projeto localizado em 'Stafford', listar o número do projeto, o número do departamento responsável, e o sobrenome, endereço e data de nascimento do gerente responsável pelo departamento. LOCAIS_DEPTO DNÚMERO DLOCALIZAÇÃO 1 Houston 4 Stafford 5 Bellaire 5 Sugariand 5 Houston TRABALHA_EM NSSEMP PNRO HORAS 123456789 1 32.5 123456789 2 7.5 666884444 3 40.0 453453453 1 20.0 453453453 2 20.0 333445555 2 10.0 333445555 3 10.0 333445555 10 10.0 333445555 20 10.0 999887777 30 30.0 999887777 10 10.0 987987987 10 35.0 987987987 30 5.0 987654321 30 20.0 987654321 20 15.0 888775555 20 null Notas Didáticas Prof. Luiz Camolesi Jr.Pg. 31 – Parte IV (Projeto Lógico de BD Relacional) • STAFFORD_PROJS←σ ←σ ←σ ←σ PLOCALIZAÇÃO = 'Stafford' (PROJETO) • CONTR_DEPT←←←←(STAFFORD_PROJS ⌧⌧⌧⌧ DNUM = DNÚMERO DEPARTAMENTO) • PROJ_DEPT_MGR←←←←(CONTR_DEPT ⌧⌧⌧⌧ SSNGER = NSS EMPREGADO) • RESULT←π←π←π←π PNÚMERO, DNUM, SNOME, ENDEREÇO, DATANASC (PROJ_DEPT_MGR) Consulta 3: Encontrar os nomes de empregados que trabalham em todos os projetos controlados pelo departamento 5. • DEPT5_PROJS(PNO)←π←π←π←π PNÚMERO (σσσσ DNUM=5 (PROJETO))) • EMP_PROJ(NSS, PNO)←π←π←π←π NSSEMP, PNRO (TRABALHA_EM) • RESULT_EMP_SSNS←←←←EMP_PROJ ÷÷÷÷ DEPT5_PROJS • RESULT←π←π←π←π SNOME, PNOME (RESULT_EMP_SSNS * EMPREGADO) Consulta 4: Fazer uma lista de números de projetos no qual um empregado, cujo sobrenome é 'Smith', trabalha no projeto ou é gerente do departamento que controla o projeto. • SMITH(NSSEMP)← π← π← π← π NSS (σσσσ SNOME='Smith' (EMPREGADO)) • SMITH_WORKER_PROJS← π← π← π← π PNRO (TRABALHA_EM * SMITH) • MGRS←π←π←π←π SNOME, DNÚMERO (EMPREGADO ⌧⌧⌧⌧ NSS = SNNGER DEPARTAMENTO) • SMITH_MGS←σ←σ←σ←σ SNOME = 'Smith' (MGRS) • SMITH_MANAGED_DEPTS(DNUM)←π←π←π←π DNÚMERO (SMITH_MGRS) • SMITH_MGR_PROJS(PNRO)←π←π←π←π PNÚMERO (SMITH_MANAGED_DEPTS * PROJETO) • RESULT←←←←(SMITH_WORKER_PROJS ∪∪∪∪ SMITH_MGR_PROJS) Consulta 5: Listar os nomes de todos os empregados com dois ou mais dependentes. Esta consulta não pode ser realizada usando apenas a álgebra relacional. Deve-se utilizar a operação FUNCTION com a função de agregação COUNT, que não é da álgebra relacional. Nas formulações que se seguirão, assume-se que dependentes de um mesmo empregado possuem nomes distintos. • T1(NSS, NO_DE_DEPS)←←←←NSSEMP ℑℑℑℑ COUNT NOMEDEPENDENTE (DEPENDENTE) • T2←σ←σ←σ←σ NO_DE_DEPS > 2 (T1) • RESULT←π←π←π←π SNOME, PNOME (T2 * EMPREGADO) Consulta 6: Listar os nomes dos empregados que não possuem dependentes. • TODOS_EMPS←π←π←π←π NSS (EMPREGADO) • EMPS_COM_DEPS(NSS)←π←π←π←π NSSEMP (DEPENDENTE) • EMPS_SEM_DEPS←←←←(TODOS_EMPS - EMPS_COM_DEPS) • RESULT←π←π←π←π SNOME, PNOME (EMPS_SEM_DEPS * EMPREGADO) Consulta 7: Listar os nomes dos gerentes que têm ao menos um dependente. • MGRS(NSS)←π←π←π←π SNNGER (DEPARTAMENTO) • EMPS_COM_DEPS(NSS)←π←π←π←π NSSEMP (DEPENDENTE) • MGRS_COM_DEPS←←←←(MGRS ∩∩∩∩ EMPS_COM_DEPS) • RESULT←π←π←π←π SNOME, PNOME (MGRS_COM_DEPS * EMPREGADO) ❋
Compartilhar