Modelo Relacional - Conceitos, Álgebra, Cálculo e Normalização

Departamento de Engenharia de Produção Gestão da Informação II 2014 Diego Carvalho Agenda Modelo relacional Normalização Modelo relacional Modelo relacional Modelo Relacional Conceitos Atributos Tuplas Relações Domínios Esquemas Chaves Restrições Integridade da entidade Integridade referencial Restrição semântica Álgebra Relacional Cálculo Relacional Modelo relacional Relação VEÍCULO Tipo Fabricante Modelo Ano de Fabricação Cor Combustível Limousine BMW 740 2008 preto gasolina Van VW Transporter 2009 vermelho diesel Compacto Kia Picanto 2010 preto gasolina Limousine Audi Allroad 2009 azul diesel Relação ou cabeçalho Corpo Tupla Atributo Valor Modelo relacional conceitos Atributo características dos dados por exemplo os possíveis atributos de uma pessoa são nome sexo data do aniversário Domínios conjunto de valores atômicos de um mesmo tipo valores atômicos são valores que não podem ser decompostos no modelo exemplo BMW Mercedes Audi VW GM Kia etc são valores possíveis para ao atributo Fabricante de Carros Um domínio indica quais são os valores possíveis para um determinado atributo Um domínio apresenta nome e dimensão Modelo relacional conceitos Tuplas um conjunto ordenado de valores que representa uma instância na tabela outros nomes ntupla linha registro Relações uma relação nos domínios D1 D2 Dn não necessariamente diferentes consiste em um cabeçalho e um corpo Cabeçalho conjunto fixo de atributos A1 A2 An de maneira que corresponda aos domínios Di i1 2 n Corpo conjunto de tuplas que variam no tempo Modelo relacional conceitos Grau da Relação número de atributos de uma relação Cardinalidade número de tuplas de um corpo em um determinado momento Propriedades de uma relação i não existem tuplas duplicadas numa relação ii as tuplas não são ordenadas top to bottom iii os atributos não são ordenados left to right iv todos os atributos são atômicos Modelo relacional conceitos Esquema descrição formal de todas as relações e de todos os relacionamentos entre relações de uma base de dados Chaves o modelo relacional usa chaves para identificar as tuplas de uma relação São fundamentais para a garantia das regras de integridade Chave candidata identificador único de uma tupla em uma relação Modelo relacional conceitos Definição formal de Chave Candidata Seja R uma relação de atributos A1 A2 An O conjunto K Ai Aj Ak de R é dito chave candidata se e somente se ele satisfaz as duas propriedades independentes do tempo Único em qualquer momento nenhum par de tuplas apresenta o mesmo valor para todo Aq qij k Minimalidade nenhum Aq pode ser descartado de K sem invalidar a propriedade anterior Modelo relacional Tipo Fabricante Modelo Ano de Fabricação Cor Combustível Limousine BMW 740 2008 preto gasolina Van VW Transporter 2009 vermelho diesel Compacto Kia Picanto 2010 preto gasolina Limousine Audi Allroad 2009 azul diesel Chave Candidata Modelo relacional conceitos Tipo Fabricante Modelo Ano de Fabricação Cor Combustível Número de série Número de identificação Limousine BMW 740 2008 preto gasolina WBAKD23 100020 Van VW Transporter 2009 vermelho diesel KJSDS293 120010 Compacto Kia Picanto 2010 preto gasolina ERO348K 220010 Limousine Audi Allroad 2009 azul diesel KDJA3942 302020 Chave Candidata Modelo relacional conceitos Chave primária identificador único das tuplas de uma relação Dentre as Chaves Candidatas uma Chave Primária é escolhida para garantir a unicidade das tuplas normalmente a de menor grau Chave substituta ou artificial existem chaves primárias que não são práticas nesse caso criamos um ID que será usado como chave primária Chave estrangeira atributo ou combinação de atributos em uma relação R2 em que os valores são os de uma chave primária em uma relação R1 R1 e R2 não precisam ser relações separadas O domínio da chave estrangeira é o mesmo da chave primária Modelo relacional restrições Integridade de entidade nenhum atributo que compõe a chave primária pode ter valores nulos Nulo representa propriedade de não aplicabilidade ou informação desconhecida É simplesmente um marcador para indicar a falta de informação o nulo não é valor válido a um domínio Contradição uma chave primária nula indicaria que a entidade não existe não está disponível ou é desconhecida Se esse é o caso então não deve figurar na base de dados Modelo relacional restrições Integridade referencial Se uma relação R2 tem uma chave estrangeira FK de uma chave primária PK de uma relação R1 então para todo o valor de FK em R2 existe um valor igual ao de PK em alguma tupla de R1 ou FK é integralmente nula R1 e R2 não são necessariamente distintas Modelo relacional restrições Restrição semântica garante a corretude do significado do dado exemplo não existem número nos endereços que são negativos Restrição de domínio implica a cada atributo de cada relação onde o domínio do atributo tem que ser garantido Restrição de nulidade é utilizada quando um determinado atributo não pode ser nulo NOT NULL WITH DEFAULT Restrição de unicidade é utilizada quando um determinado atributo não pode ter valores repetidos ex números de série Modelo relacional álgebra União de duas relações compatíveis R1 UNION R2 é o conjunto de todas as tuplas que pertencem à R1 e R2 Modelo relacional álgebra Interseção de duas relações compatíveis R1 INTERSECT R2 é o conjunto de todas as tuplas que pertencem somente às duas relações Modelo relacional álgebra Diferença de duas relações compatíveis R1 MINUS R2 é o conjunto de todas as tuplas que pertencem somente à R1 e não à R2 Modelo relacional álgebra Produto cartesiano de duas relações R1 TIMES R2 é o conjunto de todas as tuplas que são a concatenação da tupla r R1 e a tupla s R2 Ou seja r r1 r2 rm e s sm1 sm2 smn então t r1 r2 rm sm1 sm2 smn Modelo relacional álgebra R1 TIMES R2 Modelo relacional álgebra Seleção seleciona um subconjunto das tuplas de uma relação É um operador unário e é aplicado a uma única relação 𝛔 condição relação Modelo relacional álgebra Projeção cria uma nova relação selecionando um subconjunto de atributos de uma relação existente É um operador unário 𝛑 lst de atributos relação Modelo relacional álgebra Join concatena duas relações usando uma condição de join ou predicado As relações tem que ter um atributo em comum no mesmo domínio onde a condição pode ser especificada R condição S Modelo relacional álgebra Divisão divide R1 de grau mn pela relação R2 de grau m produzindo uma relação de grau n o n1 atributo da relação R1 e o iésimo da R2 tem que ser definidos no mesmo domínio Exercícios Considere o BD de uma Companhia EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Superssn Dno DEPARTMENT Dname Dnumber Mgrssn Mgrstartdate DEPTLOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKSON Essn Pno Hours DEPENDENT Essn Dependentname Sex Bdate Relationship Figure 35 Schema diagram for the COMPANY relational database schema Considere o BD de uma Companhia Figure 36 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Superssn Dno John B Smith 123456789 19650109 731 Fondren Houston TX M 30000 333445555 5 Franklin T Wong 333445555 19551208 638 Voss Houston TX M 40000 888665555 5 Alicia J Zelaya 999887777 19680119 3321 Castle Spring TX F 25000 987654321 4 Jennifer S Wallace 987654321 19410620 291 Berry Bellaire TX F 43000 888665555 4 Ramesh K Narayan 666884444 19620915 975 Fire Oak Humble TX M 38000 333445555 5 Joyce A English 453453453 19720731 5631 Rice Houston TX F 25000 333445555 5 Ahmad V Jabbar 987987987 19690329 980 Dallas Houston TX M 25000 987654321 4 James E Borg 888665555 19371110 450 Stone Houston TX M 55000 NULL 1 DEPARTMENT Dname Dnumber Mgrssn Mgrstartdate Research 5 333445555 19880522 Administration 4 987654321 19950101 Headquarters 1 888665555 19810619 DEPTLOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland Considere o BD de uma Companhia WORKSON Essn Pno Hours 123456789 1 325 123456789 2 75 666884444 3 400 453453453 1 200 453453453 2 200 333445555 2 100 333445555 3 100 333445555 10 100 333445555 20 100 999887777 30 300 999887777 10 100 987987987 10 350 987987987 30 50 987654321 30 200 987654321 20 150 888665555 20 NULL PROJECT Pname Pnumber Plocation Dnum ProductX 1 Bellaire 5 ProductY 2 Sugarland 5 ProductZ 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford 4 DEPENDENT Essn Dependentname Sex Bdate Relationship 333445555 Alice F 19860405 Daughter 333445555 Theodore M 19831025 Son 333445555 Joy F 19580503 Spouse 987654321 Abner M 19420228 Spouse 123456789 Michael M 19880104 Son 123456789 Alice F 19881230 Daughter 123456789 Elizabeth F 19670505 Spouse Figure 36 One possible database state for the COMPANY relational database schema Q1 Dê o nome e o endereço de todos os empregados que trabalham para o departamento Research Q2 Para todos os projetos localizados em Stafford liste seus números o número do departamento controlador e o último nome do gerente do departamento bem como seu endereço e data de nascimento Q3 Ache os nomes dos empregados que trabalham em todos os projetos controlados pelo departamento de número 5 Q4 Faça uma lista dos números de projetos que contenham empregados cujo último nome seja Smith tanto como trabalhador quanto como gerente do departamento que controla o projeto Q5 Liste os nomes de todos os empregados com dois ou mais dependentes Q6 Liste os nomes dos empregados que não possuem dependentes Q7 Liste os nomes de todos os gerentes com pelo menos um dependente Q1Q7 Repita todas as consultas utilizando a notação de Cálculo Relacional Normalização Normalização O problema da redundância encontrar o mesmo dado em mais de um lugar na base de dados A redundância em um esquema relacional é não ótimo e cria os seguntes problemas Anomalias de inserção Anomalias de deleção Anomalias de atualização Normalização ID Student Rank College CollegeLevel A00001 Ria Sinha 6 Fergusson 4 A00002 Vivek Kaul 15 PICT 5 A00003 George Smith 9 IIT 1 A00004 Will Brown 1 IIT 1 Normalização Anomalias ID Student Rank College CollegeLevel A00001 Ria Sinha 6 Fergusson 4 A00002 Vivek Kaul 15 PICT 5 A00003 George Smith 9 IIT 1 A00004 Will Brown 1 IIT 1 A00005 Susan Fuller 10 Fergusson Anomalia de Inserção Normalização Anomalias A00003 George Smith 9 IIT 1 A00004 Will Brown 1 IIT 1 ID Student Rank College CollegeLevel A00001 Ria Sinha 6 Fergusson 4 A00002 Vivek Kaul 15 PICT 5 A00003 George Smith 9 IIT 1 A00004 Will Brown 1 IIT 1 Anomalia de Deleção Normalização Anomalias A00001 Ria Sinha 6 Fergusson 4 A00003 George Smith 9 Fergusson 1 ID Student Rank College CollegeLevel A00001 Ria Sinha 6 Fergusson 4 A00002 Vivek Kaul 15 PICT 5 A00003 George Smith 9 IIT 1 A00004 Will Brown 1 IIT 1 Anomalia de Atualização Normalização Dependência Funcional FD é um tipo de integridade que define a dependência de subconjuntos de atributos em uma relação Definição Dado um esquema relacional R com um subconjunto de atributos A e B dizemos que existe a dependência funcional A B se os valores do atributo A implicam literalmente nos valores do atributo B vale para toda a instância r da relação R Podemos ler A B das seguintes maneiras A implica em B A determina B B é dependente de A Normalização Dependência Funcional FD Exemplos A D verificar que não existe o inverso nas tuplas a1 b1 c1 d1 e a2 b2 c2 d1 Normalização Dependência Funcional FD Exemplos STUDENTID COLLEGE outras STUDENTID STUDENT STUDENTID STUDENT COLLEGE STUDENTID STUDENT RANK Normalização Dependências Funcionais Triviais Nome Nome de Família Nome Ocorre quando B é subconjunto de A Normalização Formas normais Normalização é um procedimento no desenho bases relacionais que tem por objetivo converter os esquemas relacionais em uma forma conveniente O objetivo principal é remover a redundância das relações e os problemas e anomalias relacionados com ela Normalização Primeira forma normal 1NF Uma relação está na primeira forma normal quando todos os seus atributos tem domínios que são indivisíveis e atômicos A idéia de atomicidade garante a não existência de grupos repetitivos É necessário pois os sistemas de gerenciamento de base de dados só armazenam um único valor para a interseção de linha e coluna Normalização Primeira forma normal 1NF A definição extendida de C J Date é Uma tabela está na 1NF se e somente se satisfaz as seguintes condições Não existe ordenamento toptobottom nas linhas Não existe ordenamento lefttoright nas colunas Não existem linhas repetidas Cada interseção de linhacoluna só apresenta um único valor existente no domínio Todas as colunas são regulares ie não existe componentes escondidos como ID de linha ID de objetos e timestamps Normalização Primeira forma normal 1NF exemplos 1NF Não 1NF Normalização Primeira forma normal 1NF exemplos Nome NomeFilho1 NomeFilho2 NomeFilho3 Jorge Diego Bianca Pedro Sílvia Felipe Ana Clara Antônio Carlos Joaquim Rafael Eduardo Não 1F Normalização Segunda forma normal 2NF Uma relação está na segunda forma normal quando já se encontra na 1NF e nenhum atributo nãochave depende de uma parte da chave candidata Se existe uma chave candidata que é composta por um único atributo esta relação já se encontra em 2NF Normalização Segunda forma normal 2NF Yeas yrreleasescnt contudo a chave candidata é MovieTitle Year 1NF Normalização Segunda forma normal 2NF Yeas yrreleasescnt Levar a dependência para uma outra tabela 2NF Normalização Terceira forma normal 3NF Uma relação está na terceira forma normal quando já se encontra na 2NF e nenhum atributo nãochave depende da chave primária de forma transitiva Cabe lembrar que uma dependência funcional transitiva é do tipo X Y e Y Z então X Z Normalização Terceira forma normal 3NF Director DirectorDOB Chave candidata MovieTitle Year Só que MovieTitle Director 2NF Normalização Terceira forma normal 3NF Solução decompor a primeira relação criando uma relação para diretor 3NF Normalização Terceira forma normal 3NF exemplos