Banco de Dados - Conceitos Basicos e Arquitetura - Aula 1

CONCEITOS BÁSICOS E ARQUITETURA DE UM BANCO DE DADOS Você já deve ter observado que os Bancos de Dados estão em nosso dia a dia e nós estamos sempre interagindo com um mesmo que indiretamente como quan do você faz a transferência de um valor da sua conta para a conta de um amigo por meio de um aplicativo de um banco financeiro Nesse caso está acontecendo a atualização em um banco de dados do seu saldo e do saldo do seu amigo que recebeu um valor como transferência Importante Nesta disciplina você trabalhará com bancos de dados tradicionais relacionais em que a maior parte da informação armazenada é textual e numérica Novos tipos de banco de dados como o NoSQL serão traba lhados em uma disciplina futura O que é um banco de dados então É uma coleção de dados organizada relacionada e armazenada de forma a possibilitar uma fácil manipulação É importante você saber que um banco de da dos é projetado construído e preenchido com dados para um propósito específico Ou seja uma universidade pode ter um banco de dados com o propósito de arma zenar os dados para controle acadêmico ou seja dados dos alunos dos cursos a que eles pertencem das disciplinas que eles cursaram dentre outras informações que tem o propósito de controle acadêmico Imagine uma tabela de um banco de dados como mostrada a seguir Matrícula Nome DataNasc Celular 1 Ana 12jan1995 11 988881111 2 Bruno 11dez1998 11 977774444 3 Camila 17out2000 11 966662222 4 AULA 1 Banco de Dados As operações que você pode realizar em um banco de dados são inserir novos dados buscar dados atualizar dados e eliminar dados Considerando a tabela Aluno acima você pode inserir um novo aluno ou seja inserir uma nova linha nesta tabela atualizar o celular de um determinado aluno buscar todos os alunos que nasceram a partir do ano de 1998 Atenção A implementação destas operações será vista na Aula 6 com a Linguagem SQL como mostrado na Figura 1 Figura 1 Comandos da Linguagem SQL Fonte Elaborada pela autora COMPONENTES DE UM BANCO DE DADOS Na Figura 2 você pode ver uma imagem simplificada de um sistema de banco de dados sendo possível identificar quatro componentes dados hardware sof tware e usuários 5 AULA 1 Banco de Dados Figura 2 Representação simplificada de um banco de dados Fonte DATE 2004 Os dados são os valores fisicamente registrados no banco de dados ou seja todos os seus dados como aluno estão armazenados em um banco de dados da universidade Os dados em um banco de dados conforme pode ser observado na Figura 2 são integrados em arquivos que formam o banco de dados o que diminui a re dundância entre os arquivos e compartilhados ou seja o banco de dados pode ser compartilhado entre di ferentes usuários os quais podem ter acesso de leitura aos mesmos dados possivelmente ao mesmo tempo acesso concorrente O SGBD Sistema de Gerenciamento ou gerenciador de Banco de Dados é a camada de software que fica entre os dados do banco de dados e os usuários do sistema conforme pode ser observado na Figura 2 Todas as solicitações de acesso ao banco de dados são tratadas pelo SGBD por exemplo inserir ou atualizar dados 6 AULA 1 Banco de Dados É importante você saber que a função geral do SGBD é isolar os usuários do banco de dados dos detalhes do nível de hardware da mesma forma como os sis temas de linguagem de programação protegem os programadores dos detalhes no nível de hardware Os componentes de hardware consistem em volumes de armazenamento secundário que são usados para manter os dados armazenados e processador de hardware e memória principal associada que são usados para dar suporte à execução do software do sistema de banco de dados SGBD Importante Não deixe de assistir à videoaula Introdução a Banco de Dados com a professora Elisângela Botelho Gracias Com relação aos usuários de um banco de dados podemos classificálos em Programadores de aplicações Usuários finais Administrador de dados DA Administrador de banco de dados DBA Os Programadores de aplicações ou programas de aplicação conforme você pode observar na Figura 2 são responsáveis pela implementação de programas aplicativos de banco de dados ou seja aplicativos desenvolvidos em alguma linguagem Python Java etc que acessam dados de um banco de dados Esses programas obtêm acesso ao banco de dados emitindo a solicitação apropriada ao SGBD Os Usuários Finais conforme Figura 3 podem acessar o banco de dados por meio das aplicações desenvolvidas pelos programadores de aplicações da interface de linguagem de consulta SQL e da interface acionada por menus ou formulários 7 AULA 1 Banco de Dados Figura 3 Usuários finais de um banco de dados O Administrador de Dados DA tem a responsabilidade central pelos dados ou seja é um gerente dos dados Ele deve entender os dados e as necessidades da empresa com relação a esses dados Algumas das funções do DA são decidir que dados devem ser armazenados no banco de dados escolher as estruturas apropriadas para representar e armazenar tais dados estabelecer as normas para manter e tratar esses dados definir as permissões aos usuários O Administrador de Banco de Dados DBA ilustrado na Figura 4 é responsável pela implementação do banco de dados de acordo com as decisões tomadas pelo DA Algumas das funções do DBA são criar o banco de dados autorizar o acesso ao banco de dados coordenar e monitorar o uso do banco de dados implementar os controles técnicos referentes às decisões sobre normas to madas pelo DA 8 AULA 1 Banco de Dados responsável por problemas tais como quebra de segurança assegurar que o sistema opere com desempenho adequado Figura 4 DBA Importante Não deixe de assistir à videoaula Usuários de um Banco de Dados com a professora Elisângela Botelho Gracias VANTAGENS DE UM BANCO DE DADOS Importante A melhor maneira para você entender a natureza e as características dos bancos de dados é comparar as características do processamento de arquivos tradicionais que antecederam a tecnologia de banco de dados com os sistemas de banco de dados relacionais Considerando o processamento de arquivos tradicionais os dados estão isolados e separados ou seja os dados estão espalhados em diversos arquivos Em um sistema de banco de dados porém os dados estão armazenados em um único local que é o banco de dados 9 AULA 1 Banco de Dados No processamento de arquivos tradicionais ocorre facilmente a duplicação de dados e ainda um mesmo dado pode ter valores diferentes nos arquivos Ou seja um arquivo pode ter atualizado o valor deste dado e os outros não No sistema de banco de dados ocorre uma redução da duplicação de dados pois como os dados estão armazenados em um único local banco de dados existem menos chances para os dados terem múltiplas cópias No processamento de arquivos tradicionais existe uma dependência dos programas aplicativos com o arquivo de dados pois os programas dependem dos formatos dos arquivos Se for feita qualquer alteração no formato dos arquivos o programa precisa ser alterado também No sistema de banco de dados ocorre uma independência de dadosprogramas pois os programas que acessam dados de um banco de dados não necessitam incluir o formato dos arquivos mas devem conter somente uma definição de cada dado Considerando o que foi dito até agora você já deve ter percebido que um banco de dados possui muitas vantagens Destacarei algumas compartilhamento de dados as aplicações existentes podem compartilhar os dados do banco de dados controle de redundância como os dados se encontram em um único local banco de dados essa redundância pode ser controlada integridade dos dados assegurar que os dados do banco de dados estejam corretos Um exemplo de falta de integridade armazenar que um emprega do trabalhou 400 horas na semana em vez de 40 horas segurança dos dados o DBA pode implementar restrições de segurança ou seja regras que devem ser verificadas sempre que houver uma tentativa de acesso a dados confidenciais ARQUITETURA DE UM BANCO DE DADOS A arquitetura de um banco de dados Figura 5 pode ser dividida em três níveis nível externo nível conceitual e nível interno 10 AULA 1 Banco de Dados Figura 5 Os três níveis de arquitetura Nível externo visões de usuários individuais Nível conceitual visão da comunidade de usuários Nível interno visão do meio de armazenamento Fonte DATE 2004 Qual nível está mais próximo do usuário O nível mais próximo do usuário é o nível externo É o nível do usuário individual o qual pode ser um programador de aplicações ou um usuário final com qualquer grau de especialidade Uma visão externa é o conteúdo do banco de dados visto por algum usuário determinado ou seja para esse usuário a visão externa é o ban co de dados No nível conceitual temos a representação de todo o conteúdo de informações do banco de dados de uma forma um tanto abstrata quando comparada com o modo como os dados são armazenados fisicamente no nível interno Uma visão conceitual é a visão do conteúdo total do banco de dados ou seja todas as infor mações do banco de dados E o nível interno ou físico é o mais próximo do meio de armazenamento físico Esse nível trata como os dados são fisicamente armazenados por exemplo como os campos armazenados estão representados em que sequência física estão os registros armazenados etc Atenção É importante você ter entendido que fazendo uma comparação entre os níveis temos que o nível externo se preocupa com as percepções dos usuários individuais enquanto o nível conceitual está preocupado com uma percepção da comunidade dos usuários ou seja todos os tipos de usuários 11 AULA 1 Banco de Dados Na Figura 5 você pode observar que sempre teremos visões externas distintas representando partes do banco de dados de interesse de cada usuário individual e somente uma visão conceitual de todo o banco de dados Isso porque a maioria dos usuários não está interessada ou não tem o privilégio em todo o banco de dados mas somente em alguma porção restrita do banco de dados Importante Não deixe de assistir à videoaula Arquitetura de um Banco de Dados com a professora Elisângela Botelho Gracias 12 AULA 1 Banco de Dados REFERÊNCIAS DATE C J Introdução a sistemas de banco de dados Rio de Janeiro Campus 2004 ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson MODELO ENTIDADERELACIONAMENTO INTRODUÇÃO A primeira etapa e muito importante do projeto de um banco de dados é a construção de um modelo conceitual Você viu na disciplina de Introdução à Engenharia de Software que na fase de Projeto existia a modelagem do banco de dados Agora você aprenderá a modelar um banco de dados O Modelo EntidadeRelacionamento MER é um modelo de dados concei tual de alto nível cujos conceitos foram projetados para estarem o mais próximo possível da visão que o usuário tem dos dados não se preocupando em representar como esses dados estarão armazenados Ou seja no MER você desenhará as informações que serão armazena das em seu banco de dados O MER oferece três construtores básicos para sua representação entidade atributo e relacionamento que você aprenderá a seguir ENTIDADE Uma entidade é uma representação abstrata de um objeto do mundo real assim uma entidade pode ser a representação de um ser de um fato de uma coisa de um organismo social etc Se você tiver um conjunto de objetos do mundo real que tem caracte rísticas semelhantes e deseja armazenar essas informações no banco de dados então você terá uma entidade Exemplo prático suponha que você modelará um banco de dados para uma universidade e deseja armazenar as seguintes informações para todos os alunos matrícula nome email data de nascimento e sexo 4 AULA 2 Banco de Dados Então você deverá criar a entidade Aluno que será representada por um retân gulo conforme mostra a Figura 1 Figura 1 Entidade Aluno Fonte Elaborada pela autora Caso você queira referirse a um possível objeto particular da entidade Aluno por exemplo o aluno Joao de Souza falase em instância da entidade ou ocor rência da entidade ATRIBUTO Os atributos são as características da entidade ou seja as informações que você deseja armazenar de uma entidade no banco de dados Graficamente um atributo é representado por uma elipse ligada à entidade Exemplo prático na Figura 2 você pode observar os atributos exemplificados anteriormente ligados à entidade Aluno Figura 2 Entidade Aluno com seus atributos Fonte Elaborada pela autora Um exemplo de uma possível instância da entidade Aluno ou seja um aluno que poderá estar armazenado no banco de dados após ele estar implementado o aluno de matrícula 1 com nome Joao de Souza cujo email é joaomackenziebr nasceu no dia 23021999 e é do sexo masculino 5 AULA 2 Banco de Dados CHAVE PRIMÁRIA Toda entidade pode ter vários atributos para caracterizála mas dentre esses atributos devese sempre definir um atributo ou conjunto de atributos que com seus valores consiga identificar um único objeto dentro da entidade Esse atri buto ou conjunto de atributos é denominado chave primária Atenção A chave primária deve ser única dentro daquela entidade e nunca pode ser nula Exemplo prático voltando ao exemplo da Figura 2 você pode definir que o atri buto Matricula seja a chave primária da entidade Aluno ou seja cada aluno terá um valor único para o atributo Matricula e nunca poderá ser nulo A notação adotada para chave primária é sublinhar o atributo ou conjunto de atributos que formam a única chave primária de uma entidade conforme você pode observar na Figura 3 Figura 3 Entidade Aluno com seus atributos e a identificação da chave primária Fonte Elaborada pela autora Atenção De acordo com a Figura 3 é muito importante você entender que todo aluno deverá ter um valor único para o atributo Matricula e se um aluno tem o valor do Matricula igual a 1 então nenhum outro aluno poderá ter esse valor para Matricula Importante Não deixe de assistir à videoaula Introdução ao Modelo Entidade Relacionamento com explicações de entidade atributo e chave primá ria com a professora Elisângela Botelho Gracias 6 AULA 2 Banco de Dados RELACIONAMENTO Você aprendeu na Aula 1 que um banco de dados é uma coleção de dados organizada e relacionada Então as entidades devem estar relacionadas ou seja o relacionamento é a associação das entidades no mundo real O relacionamento é representado no MER por um losango Exemplo prático na Figura 4 temos o relacionamento cursa que mostra a associação entre as entidades Aluno e Disciplina Ou seja um aluno cursa discipli na Se você analisar esse relacionamento realmente existe entre aluno e disciplina pensando um cenário de um banco de dados de uma universidade Figura 4 Entidades Aluno e Disciplina se relacionando Fonte Elaborada pela autora Pode existir mais de um relacionamento envolvendo as mesmas entidades Você pode observar na Figura 5 que um aluno pode cursar disciplina e que tam bém pode monitorar disciplina Esses dois relacionamentos são distintos pois se um aluno cursar a disciplina Banco de Dados não quer dizer que ele deverá monitorar esta disciplina Figura 5 Entidades Aluno e Disciplina com dois relacionamentos distintos cursa e monitora Fonte Elaborada pela autora Uma entidade pode se relacionar com ela mesma Nesse caso então temos um autorrelacionamento ou seja uma mesma entidade desempenha mais de um papel em um mesmo relacionamento 7 AULA 2 Banco de Dados Exemplo prático na Figura 6 um empregado supervisiona outro empregado ou seja um empregado é o supervisor e o outro empregado é o supervisionado Figura 6 Exemplo de autorrelacionamento Fonte Elaborada pela autora CARDINALIDADE MÁXIMA DOS RELACIONAMENTOS Todo relacionamento associa uma ou mais instâncias de uma entidade com uma ou mais instâncias da outra entidade envolvida no relacionamento Na cardinalidade máxima será utilizado 1 no máximo uma instância ou N uma ou mais instâncias Se o relacionamento envolver até duas entidades podemos ter as seguintes cardinalidades NN muitos para muitos 1N um para muitos 11 um para um No relacionamento de cardinalidade NN muitos para muitos entre duas en tidades A e B ocorre que uma instância da entidade A pode estar associada a qualquer número de instâncias da entidade B e viceversa Exemplo prático no exemplo da Figura 7 temos o relacionamento cursa cuja cardinalidade é NN ou seja um Aluno pode cursar várias N disciplinas e uma Disciplina pode ser cursada por vários N alunos 8 AULA 2 Banco de Dados Figura 7 Exemplo de relacionamento NN Fonte Elaborada pela autora Na Figura 8 você pode compreender melhor a cardinalidade NN do relaciona mento cursa entre Aluno e Disciplina Ou seja a aluna Ana cursa três disciplinas mais de 1 e a disciplina de BDII tem dois alunos cursando mais de 1 Figura 8 Exemplo de relacionamento NN com dados Fonte Elaborada pela autora No relacionamento de cardinalidade 1N um para muitos entre duas entidades A e B ocorre que uma instância da entidade A pode estar associada a qualquer número de instâncias de B uma instância da entidade B entretanto deve estar associada no máximo a uma única instância da entidade A Exemplo prático na Figura 9 temos o relacionamento pertence cuja cardinali dade é N1 entre Aluno e Curso nesta ordem ou seja um Aluno pode pertencer a um curso somente 1 e um Curso pode ter pertencendo vários N alunos 9 AULA 2 Banco de Dados Figura 9 Exemplo de relacionamento N1 entre Aluno e Curso Fonte Elaborada pela autora Na Figura 10 você pode entender melhor a cardinalidade N1 do relacionamen to pertence entre Aluno e Curso nesta ordem Ou seja a aluna Ana pertence somente a um único curso SI e o curso de SI tem dois alunos Figura 10 Exemplo de relacionamento N1 com dados Fonte Elaborada pela autora Atenção Se você inverter as cardinalidades do exemplo da Figura 9 onde for 1 colocar N e onde for N colocar 1 muda totalmente o modelo do banco de dados ou seja um aluno pode pertencer a vários cursos mas um curso poderá ter somente um aluno Imagine em uma universidade cada curso ter no máximo um aluno é uma regra de negócio totalmente incorreta No relacionamento de cardinalidade 11 um para um entre duas entidades A e B ocorre que uma instância da entidade A está associada no máximo a uma instância da entidade B uma instância da entidade B também está associada no máximo a uma instância da entidade A 10 AULA 2 Banco de Dados No exemplo da Figura 11 temos o relacionamento possui cuja cardinalidade é 11 ou seja uma Disciplina possui uma Ementa somente 1 e uma Ementa é de uma Disciplina somente 1 Figura 11 Exemplo de relacionamento 11 entre Disciplina e Ementa Fonte Elaborada pela autora ATRIBUTOS DE RELACIONAMENTO Você deve ter observado que uma entidade deve ter pelo menos um atributo para caracterizála ou seja uma entidade sem atributos não tem sentido existir Os relacionamentos por sua vez podem existir mesmo que não tenham atri butos próprios Exemplo prático se você analisar a Figura 12 verá que um aluno pode cursar várias disciplinas e uma disciplina pode ser cursada por vários alunos Figura 12 Exemplo de relacionamento cursa sem atributos Fonte Elaborada pela autora E se for necessário saber a nota final de um aluno em uma disciplina que ele cursou Onde este atributo nota deverá estar Analisemos as situações se nota for um atributo somente da entidade Aluno cada aluno teria uma única nota para qualquer disciplina que ele cursar se nota for um atributo somente da entidade Disciplina todos os alunos ma triculados em uma disciplina teriam a mesma nota 11 AULA 2 Banco de Dados Nesse exemplo nota seria um atributo do relacionamento cursa ou seja quando o aluno Joao de Souza cursa a disciplina Banco de Dados ele terá uma nota 98 por exemplo Se este mesmo aluno cursa Estrutura de Dados ele terá uma outra nota 95 por exemplo Observe que a Figura 13 mostra o atributo nota como atributo do relaciona mento cursa Figura 13 Exemplo de relacionamento cursa com o atributo nota Fonte Elaborada pela autora Importante Não deixe de assistir à videoaula Modelo EntidadeRelacionamento com a professora Elisângela Botelho Gracias Nessa aula você terá explica ções sobre relacionamentos entre as entidades cardinalidade e atributo de relacionamento além de um exemplo prático CARDINALIDADE MÍNIMA OU RESTRIÇÃO DE PARTICIPAÇÃO DOS RELACIONAMENTOS Além da cardinalidade máxima que você viu nesta aula temos também a cardinalidade mínima que é o número mínimo de instâncias de uma entidade que pode estar envolvida no relacionamento Na cardinalidade mínima será utilizado 0 participação opcional ou 1 participa ção obrigatória Exemplo prático na Figura 14 temos as cardinalidades mínimas e máximas respectivamente entre parênteses representando que 12 AULA 2 Banco de Dados um Empregado deve trabalhar no mínimo em uma 1 empresa e no máxi mo em uma 1 Empresa somente Ou seja para que exista uma instância de Empregado é necessário que o empregado trabalhe em pelo menos uma Empresa uma Empresa pode ter no mínimo zero 0 empregados e no máximo vários N Empregados Ou seja uma instância de Empresa pode existir mesmo que ela não tenha nenhum Empregado Figura 14 Exemplo de cardinalidade mínima e máxima Fonte Elaborada pela autora Atenção Na Figura 14 podemos ler o relacionamento com suas cardinalidades da seguinte forma um Empregado deve trabalhar em uma e somente uma Empresa uma Empresa pode não ter empregados ou pode ter vários Empregados Símbolos que você aprendeu nesta aula com relação ao MER Símbolo Significado Entidade Relacionamento Atributo Chave primária 13 AULA 2 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S B Sistemas de banco de dados 7 ed São Paulo Pearson 2018 MODELO RELACIONAL INTRODUÇÃO O Modelo EntidadeRelacionamento MER que você aprendeu na Aula 2 é um modelo de dados conceitual de alto nível Nesse modelo você desenha as infor mações que serão armazenadas no banco de dados Nesta Aula 3 você aprenderá a fazer o mapeamento do MER para o Modelo Relacional ou seja a partir do MER você definirá as relações ou tabelas do seu banco de dados O Modelo Relacional representa o banco de dados como uma coleção de relações ou tabelas Uma das principais vantagens do Modelo Relacional foi ele ter se baseado em um ramo da matemática que é simultaneamente simples e poderoso a Teoria dos Conjuntos Dessa forma o Modelo Relacional baseiase em um modelo matemático rigoroso A estrutura de dados utilizada no Modelo Relacional é a relação que pode ser definida como uma tabela constituída por linhas e colunas em que as linhas representam os registros ou instâncias ou tuplas da relação as colunas ou campos representam os atributos da relação O esquema de uma relação é constituído por um ou mais atributos que tra duzem o tipo de dados a ser armazenado Dessa forma cada atributo está asso ciado a um tipo de dados de acordo com o tipo de informação que armazenará O domínio de um atributo corresponde então aos valores admissíveis para esse atributo como valores inteiros 0 e Sexo M F 4 AULA 3 Banco de Dados RESTRIÇÕES DE INTEGRIDADE As restrições de integridade são regras de consistência de dados que devem ser garantidas pelo próprio SGBD sem auxílio de validações externas Existem três tipos de integridade Integridade de entidade cada linha da tabela deve ser identificada unica mente ou seja cada tabela deve ter uma chave primária Integridade de domínio o valor de um atributo deve obedecer ao tipo de dados e às restrições de valores admitidos por este atributo Integridade referencial o valor do atributo que constitui a chave estrangei ra de uma tabela deve estar também presente na chave primária da tabela que referencia ou então com valor igual a NULL A integridade referencial tem por objetivo manter os dados sincronizados entre tabelas que estejam relacionadas MAPEAMENTO DO MER PARA O MODELO RELACIONAL Agora você aprenderá as regras mais utilizadas para fazer o mapeamento do MER para o Modelo Relacional ou seja como transformar cada item do seu MER em relações tabelas Você aprenderá os seguintes mapeamentos sendo que para cada mapea mento será comentada a técnica mais utilizada ou seja a mais comum 1 Mapeamento das entidades regulares 2 Mapeamento de relacionamentos com cardinalidade 1N 3 Mapeamento de relacionamentos com cardinalidade NN 4 Mapeamento de relacionamentos com cardinalidade 11 5 AULA 3 Banco de Dados 1 Mapeamento das entidades regulares Cada entidade regular é mapeada como uma relação que envolve todos os seus atributos simples e monovalorados mais sua chave primária Exemplo prático a Figura 1 a seguir mostra que a entidade Funcionario ge rará uma nova relação tabela juntamente com seus atributos CodFunc que é chave primária NomeFunc e Salario Uma forma de representar essa relação é colocando Funcionario como nome da relação e entre parênteses todos os atributos da relação Funcionario A chave primária sempre deve estar sublinhada Como o Modelo Relacional tem sua fundamentação baseada na teoria de con junto a relação Funcionario também pode ser representada entre chaves como um conjunto mesmo Funcionario CodFunc NomeFunc Salario Figura 1 Mapeamento da entidade Funcionario Fonte Elaborada pela autora Se o MER tivesse somente a entidade Funcionario o banco de dados teria uma única tabela Funcionario A Figura 2 ajuda você a ter uma visão melhor desse mapeamento onde a tabela Funcionario está exemplificada com dados 6 AULA 3 Banco de Dados Figura 2 Tabela Funcionario com dados Fonte Elaborada pela autora Atenção O que é importante você observar na Figura 2 Os valores da chave primária de Funcionario CodFunc não foram dupli cados e não são nulos Lembrando que o valor de um atributo que é chave primária não pode ser nulo nem se repetir dentro daquela tabela já que é o identificador de cada instância linha da tabela Importante Não deixe de assistir à videoaula Introdução ao mapeamento do MER para o Modelo Relacional com explicações sobre o mapeamento de entidades e atributos com a professora Elisângela Botelho Gracias 2 Mapeamento de relacionamentos com cardinalidade 1N Cada relacionamento com cardinalidade 1N não é mapeado como uma nova relação A relação que está do lado N do relacionamento recebe a chave estrangeira que é a chave primária da relação que está do lado 1 do relacionamento Caso o relacionamento tenha atributos eles são acrescentados à relação de cardinalidade N envolvida nesse relacionamento Exemplo prático a Figura 3 a seguir mostra uma parte de um MER de uma em presa no qual a entidade Funcionario se relaciona com a entidade Departamento no relacionamento pertence com cardinalidade N1 no sentido de Funcionario para Departamento 7 AULA 3 Banco de Dados O mapeamento do MER para o Modelo Relacional gerou duas relações como você pode observar na Figura 3 Cada entidade gerou uma nova relação com seus atributos Mas a relação Funcionario recebeu mais um atributo CodDepto que é a chave estrangeira Importante A chave estrangeira é que faz o relacionamento entre as relações Funcionario e Departamento Logo o relacionamento pertence está mapeado na relação Funcionario Figura 3 Mapeamento das entidades Funcionario e Departamento e do relacionamento pertence Fonte Elaborada pela autora Se o MER de uma empresa fosse somente o relacionamento entre as entida des Funcionario e Departamento Figura 3 o banco de dados teria duas relações tabelas Departamento e Funcionario A Figura 4 ajuda você a ter uma visão melhor desse mapeamento no qual as tabelas Departamento e Funcionario estão exemplificadas com dados Figura 4 Tabelas Departamento e Funcionario com dados Fonte Elaborada pela autora 8 AULA 3 Banco de Dados Atenção O que é importante você observar na Figura 4 Os valores da chave primária de cada uma das tabelas Departamento e Funcionario foram respeitados Todo funcionário pertence a um departamento que existe na tabela Departamento ou seja nesse exemplo eu não poderia ter um funcionário em um departamento cujo CodDepto fosse igual a 8 já que o departamento 8 não existe na tabela Departamento É essa integridade que a chave estran geira mantém Se você quiser saber o nome do departamento do funcionário de nome Mario Souza por exemplo é só fazer o relacionamento conforme está ilustrado na Figura 4 pela seta vermelha que mostra o relacionamento entre as duas tabelas 3 Mapeamento de relacionamentos com cardinalidade NN Cada relacionamento com cardinalidade NN é mapeado como uma nova relação que envolve todos os atributos do relacionamento caso tenha A chave primária é a concatenação das chaves primárias das entidades envolvidas nesse relacionamento NN E cada integrante da chave primária também é chave es trangeira Exemplo prático a Figura 5 a seguir mostra o MER completo de uma empresa Observe que o relacionamento participa com cardinalidade NN entre Funcionario e Projeto gerou uma nova relação denominada FuncProj que tem como chave primária a concatenação das chaves primárias das entida des envolvidas no relacionamento CodFunc e CodProj E cada atributo integrante da chave primária também é chave estrangeira tem o atributo do relacionamento participa HorasTrab que é um atributo desta relação 9 AULA 3 Banco de Dados Figura 5 Mapeamento somente do relacionamento participa com cardinalidade NN Fonte Elaborada pela autora Importante com o MER completo de uma empresa conforme mostra a Figura 5 quantas relações tabelas seriam geradas no total De acordo com o que você viu até agora temos que cada entidade gera uma nova relação com seus atributos o relacionamento pertence 1N não gera uma nova relação mas a relação Funcionario recebe a chave estrangeira para representar esse relaciona mento o relacionamento participa NN gera uma nova relação conforme você acabou de ver Então o Modelo Relacional referente ao MER da Figura 5 terá quatro re lações chave primária está sublinhada e chave estrangeira está descrita Modelo Relacional Departamento CodDepto NomeDepto Funcionario CodFunc NomeFunc Salario CodDepto CodDepto é chave estrangeira que referencia CodDepto da tabela Departamento 10 AULA 3 Banco de Dados Projeto CodProj NomeProj Duracao FuncProj CodFunc CodProj HorasTrab CodFunc é chave estrangeira que referencia CodFunc da tabela Funcionario CodProj é chave estrangeira que referencia CodProj da tabela Projeto Esse Modelo Relacional que você acabou de ver é uma representação mais for mal do banco de dados mas você pode ter uma visão mais amigável utilizando a ferramenta DBDesigner por exemplo conforme mostra a Figura 6 Atenção Não deixe de assistir à videoaula Como utilizar a ferramenta DBDesigner com a professora Elisângela Botelho Gracias com uma explicação simples e breve de como utilizar a ferramenta DBDesigner Figura 6 Representação do Modelo Relacional utilizando a ferramenta DBDesigner Fonte Elaborada pela autora A Figura 7 mostra as tabelas desse banco de dados exemplificadas com dados A análise dos dados dessas tabelas ajudará você a ter uma visão melhor desse ban co de dados ou seja as tabelas e seus relacionamentos 11 AULA 3 Banco de Dados Figura 7 Tabelas Departamento Funcionario Projeto e FuncProj exemplificadas com dados Fonte Elaborada pela autora Atenção O que é importante você analisar na Figura 7 Os valores da chave primária de cada uma das tabelas Departamento Funcionario Projeto e FuncProj foram respeitados Na tabela FuncProj o funcionário de código igual a 101 apareceu em duas linhas pois ele participou de dois projetos diferentes Lembrando que a chave primária dessa tabela é formada por dois atributos CodFunc e CodProj Na tabela FuncProj um mesmo código de projeto por exemplo 1001 pode aparecer várias vezes já que vários funcionários podem participar de um mesmo projeto Na tabela FuncProj se você quiser saber o nome do funcionário de código igual a 101 por exemplo é só fazer o relacionamento com a tabela Funcionario Isso porque o CodFunc da tabela FuncProj é chave estrangeira que referen cia está ligado à chave primária da tabela Funcionario Na tabela FuncProj se você quiser saber o nome do projeto de código igual a 1002 por exemplo é só fazer o relacionamento com a tabela Projeto Isto porque o CodProj da tabela FuncProj é chave estrangeira que referencia está ligado à chave primária da tabela Projeto De acordo com o conceito de chave estrangeira você não pode inserir na tabe la FuncProj um código de funcionário que não exista na tabela Funcionário O mesmo raciocínio vale para código de projeto 12 AULA 3 Banco de Dados Importante Não deixe de assistir à videoaula Mapeamento do MER para o Modelo Relacional com um exemplo prático de mapeamento do MER para o Modelo Relacional com a professora Elisângela Botelho Gracias 4 Mapeamento de relacionamento com cardinalidade 11 Cada relacionamento com cardinalidade 11 não é mapeado como uma nova relação Caso o relacionamento tenha atributos eles são acrescentados a uma das relações que mapeia a entidade envolvida nesse relacionamento Essa mesma relação recebe a chave primária da outra relação que mapeia a outra entidade envolvida no relacionamento ou seja ela recebe a chave estrangeira Exemplo prático a Figura 8 a seguir mostra o exemplo de um MER em que as entidades Empregado e Departamento possuem dois relacionamentos distintos pertence e gerencia Figura 8 Dois relacionamentos distintos entre as entidades Empregado e Departamento Fonte Elaborada pela autora De acordo com as regras de mapeamento que você viu até agora teremos as seguintes relações de acordo com o MER da Figura 8 Modelo Relacional Departamento coddepto nomedepto codempgerencia codempgerencia é chave estrangeira que referencia codemp da tabela Departamento 13 AULA 3 Banco de Dados Empregado codemp nomeemp telefone dtnasc dtdecisao coddepto coddepto é chave estrangeira que referencia coddepto da tabela Departamento Importante O relacionamento pertence foi mapeado pela chave estrangeira coddepto na tabela Funcionario juntamente com o atributo desse relacionamento dtdecisao O relacionamento gerencia por sua vez foi mapeado pela chave estrangeira codempgerencia na tabela Departamento Observe que o nome de uma chave estrangeira não pre cisa obrigatoriamente ter o mesmo nome da chave primária a que ela está relacionada MAPEAMENTO DE UM AUTORRELACIONAMENTO Para mapear um autorrelacionamento é só verificar sua cardinalidade e seguir a regra correspondente aos relacionamentos 11 1N ou NN No exemplo da Figura 9 você pode ver que a entidade Empregado se relacio na com ela mesma supervisiona com cardinalidade 1N Nesse caso a própria relação Empregado recebe a chave estrangeira que referencia a chave primária de Empregado Figura 9 Mapeamento da entidade Empregado se relacionando com ela mesma autorelacionamento Fonte Elaborada pela autora 14 AULA 3 Banco de Dados Para você ter uma visão melhor da relação Empregado que se relaciona com ela mesma conforme mostra a Figura 9 analise os dados da Figura 10 que exemplifica essa tabela com dados Figura 10 Tabela Empregado exemplificada com dados Fonte Elaborada pela autora Importante Na Figura 10 você pode observar que o empregado Joao tem como empregado supervisor a empregada Ana 15 AULA 3 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018 ORMALIZAÇÃO INTRODUÇÃO Você sabe o que é normalização Para que o modelo de seu banco de dados esteja bem modelado ou seja não tenha redundância existem algumas diretrizes que você deve utilizar A normaliza ção possui regras para eliminar redundâncias O objetivo da normalização é reagrupar informações para eliminar re dundâncias A normalização é utilizada tanto no processo de modelagem de um banco de dados quanto no processo de engenharia reversa O termo engenharia reversa vem do fato de usarse como ponto de partida do processo um produto implementado para obter sua especificação modelo con ceitual Nos exemplos a seguir será utilizada a engenharia reversa para explicar e exemplificar as seguintes formas normais 1FN 1ª forma normal 2FN 2ª forma normal e 3FN 3ª forma normal Mas o que é uma forma normal Uma forma normal é uma regra que deve ser obedecida por uma tabela para que esta seja bem projetada Existem diversas formas normais e cada uma delas elimina um tipo de redun dância Um banco de dados normalizado até a 3FN é o mais utilizado na prática Exemplo considere o conjunto de dados da Figura 1 com muita redundância no qual temos as seguintes informações para cada projeto são informados o código a descrição e o tipo do projeto bem como os empregados que atuam no projeto 4 AULA 4 Banco de Dados para cada empregado são informados seu código nome categoria funcional salário de acordo com sua categoria funcional data em que o empregado foi alocado ao projeto e o tempo em meses pelo qual o empregado foi alocado ao projeto Atenção Observe na Figura 1 que temos dados duplicados referentes a um mes mo projeto uma vez que vários empregados podem trabalhar no mesmo projeto e também a um mesmo empregador pois um empregado pode trabalhar em mais de um projeto Figura 1 Conjunto de dados representado na forma de uma tabela não normalizada Adaptada de Heuser 2009 Uma representação da extensão do modelo relacional um pouco modificada para representar tabelas aninhadas seria Com o modelo relacional correspondente ao conjunto de dados passase para o processo de normalização 5 AULA 4 Banco de Dados PRIMEIRA FORMA NORMAL 1FN Uma tabela encontrase na 1FN quando NÃO contém tabelas aninhadas Ou seja o domínio de um atributo deve incluir apenas valores atômicos simples e indivisíveis e o valor de qualquer atributo em uma tupla linha deve ser um único valor Se você observar temos uma tabela aninhada ou seja uma tabela dentro de uma outra como mostrado a seguir Portanto a passagem para a 1FN consta da eliminação das tabelas aninhadas E para transformar uma tabela não normalizada em um esquema que obedeça a regra da 1FN criase uma tabela referente à própria tabela e uma tabela para cada tabela aninhada Exemplo da 1FN no caso da Figura 1 cujo conjunto de dados contém muita redundância as relações resultantes da 1FN seriam Proj Codproj Tipo Descrição ProjEmp Codproj Codemp Nome Categoria Salário Dataini Tempo A decomposição das tabelas para a 1FN é feita nos seguintes passos é criada uma tabela na 1FN referente à tabela não normalizada e que contém apenas os atributos com valores atômicos isto é sem as tabelas aninhadas A chave primária da tabela na 1FN é idêntica à chave da tabela não normalizada para cada tabela aninhada é criada uma tabela na 1FN composta pelos se guintes atributos a chave primária de cada uma das tabelas na qual a tabela em questão está aninhada os atributos da própria tabela aninhada 6 AULA 4 Banco de Dados a chave primária da tabela aninhada será a chave primária dela mesma caso seja suficiente e caso contrário devese determinar quais os demais atributos necessários para identificar as linhas da tabela na 1FN compon do assim a chave primária na 1FN O conteúdo das tabelas na 1FN considerando os dados que tínhamos inicial mente na Figura 1 ficaria da seguinte forma Figura 2 Figura 2 Tabelas com dados referentes à 1FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 2 a tabela Proj na 1FN eliminou um tipo de redundância ou seja temos uma linha para cada projeto diferente a tabela ProjEmp foi criada e sua chave primária é formada pelos atributos Codproj e Codemp pois um empregado pode trabalhar em mais de um projeto se um empregado pudesse trabalhar em um único projeto a chave primária seria composta apenas pelo Codemp na tabela ProjEmp ainda temos redundância pois os dados dos emprega dos estão duplicados no caso deles participaram de mais de um projeto 7 AULA 4 Banco de Dados DEPENDÊNCIA FUNCIONAL Para você compreender a 2FN e a 3FN é necessário conhecer o conceito de dependência funcional ou seja em uma tabela relacional dizse que um atributo C2 depende funcional mente de um atributo C1 ou viceversa quando em todas as linhas da tabela para cada valor de C1 que aparece na tabela aparece o mesmo valor de C2 O conceito fica mais fácil com o exemplo destes dados a seguir Podemos dizer que o atributo Salário depende funcionalmente de um atri buto Código pelo fato de cada valor de Código estar associado sempre ao mesmo valor de Salário ou seja sempre que aparece o valor E1 do atributo Código o valor do atributo Salário é 10 sempre que aparece o valor E2 do atributo Código o valor do atributo Salário é 5 sempre que aparece o valor E3 do atributo Código o valor do atributo Salário é 9 De forma geral o determinante de uma dependência funcional pode ser um conjunto de atributos e não somente um atributo 8 AULA 4 Banco de Dados SEGUNDA FORMA NORMAL 2FN Uma tabela encontrase na 2FN quando além de encontrarse na 1FN cada atributo que não faz parte da chave pri mária depende da chave primária completa Uma tabela que não se encontra na 2FN contém dependências funcionais par ciais ou seja contém atributos não chave que dependem apenas de uma parte da chave primária Obviamente uma tabela que está na 1FN e cuja chave primária é formada por um único atributo não contém dependências parciais Isso porque nesta tabela é impossível um atributo depender de uma parte da chave primária visto que a chave primária é composta por um único atributo atômico e indivisível Importante Toda tabela que está na 1FN e que possui apenas um atributo como chave primária já está na 2FN O mesmo conceito aplicase para uma tabela que contenha apenas atributos que fazem parte da chave primária Para transformarmos um banco de dados para a 2FN pegamos as relações na 1FN e aplicamos a regra da 2FN Tabelas na 1FN que já vimos Proj Codproj Tipo Descrição ProjEmp Codproj Codemp Nome Categoria Salário Dataini Tempo O processo de passagem da 1FN para a 2FN é o seguinte copiar para a 2FN cada tabela que tenha chave primária simples ou que não tenha atributos além da chave primária No caso do nosso exemplo é o que acontece com a tabela Proj 9 AULA 4 Banco de Dados para cada tabela com chave primária composta e com pelo menos uma coluna não chave no nosso exemplo a tabela ProjEmp fazer a seguinte per gunta para cada atributo não chave o atributo depende de toda a chave ou de apenas parte dela 1 Caso o atributo dependa de toda a chave criar o atributo correspon dente na tabela com a chave completa na 2FN os atributos Dataini e Tempo da tabela ProjEmp na 2FN Ou seja os atributos Dataini e Tempo dependem da chave primária completa pois para determinar a data em que um empregado começou a trabalhar em um projeto bem como para determinar o tempo pelo qual ele foi alocado ao projeto é necessário conhecer tanto o código do projeto quanto o código do empregado 2 caso o atributo dependa apenas de parte da chave deve ser criada caso ainda não exista uma tabela na 2FN que tenha como chave pri mária a parte da chave que é determinante do atributo em questão a tabela Emp na 2FN Ou seja os atributos Nome Categoria e Salário de pendem cada um apenas do atributo Codemp já que esses atributos são determinados tão somente pelo código do empregado os atributos Nome Salário e Categoria da tabela Emp na 2FN 10 AULA 4 Banco de Dados Assim o modelo relacional correspondente à 2FN é o seguinte Proj Codproj Tipo Descrição Emp Codemp Nome Categoria Salário ProjEmp Codproj Codemp Dataini Tempo O conteúdo das tabelas na 2FN considerando os dados que tínhamos na 1FN Figura 2 ficaria da seguinte forma Figura 3 Tabelas com dados referentes à 2FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 3 a tabela Proj não foi alterada a tabela ProjEmp agora não tem mais redundância a tabela Emp tem ainda um outro tipo de redundância já que o atributo Salario depende da Categoria do empregado ou seja todos da categoria A1 tem o salário 4 B1 é 9 e A2 é 4 11 AULA 4 Banco de Dados TERCEIRA FORMA NORMAL 3FN Uma tabela encontrase na 3FN quando além de estar na 2FN todo atributo não chave depende diretamente da chave primária isto é quando não há dependências funcionais transitivas ou indiretas Uma dependência funcional transitiva ou indireta ocorre quando um atribu to além de depender da chave primária completa depende de outro atributo ou combinação de atributos não chaves Para transformarmos um banco de dados para a 3FN pegamos as relações na 2FN e aplicamos a regra da 3FN Tabelas na 2FN que já vimos Proj Codproj Tipo Descrição Emp Codemp Nome Categoria Salário ProjEmp Codproj Codemp Dataini Tempo O processo de passagem da 2FN para a 3FN é o seguinte copiar para a 3FN cada tabela que tenha menos que dois atributos não chave pois nesse caso não há como ter dependências transitivas para tabelas com dois ou mais atributos não chave criar uma tabela na 3FN com a chave primária da tabela em questão e para cada atributo não chave fazer a seguinte pergunta o atributo depende de algum outro atributo não chave 12 AULA 4 Banco de Dados 1 Caso o atributo dependa apenas da chave copie o atributo para a tabela na 3FN 2 caso o atributo dependa de outro atributo criar caso ainda não exis ta uma tabela na 3FN que tenha como chave primária o atributo do qual há a dependência transitiva copiar o atributo dependente para a tabela criada e o atributo determinante deve permanecer também na tabela original Assim o modelo relacional correspondente à 3FN é o seguinte Proj Codproj Tipo Descrição Categoria Categoria Salário Emp Codemp Nome Categoria ProjEmp Codproj Codemp Dataini Tempo O conteúdo das tabelas na 3FN considerando os dados que tínhamos na 2FN Figura 3 ficaria da seguinte forma Figura 4 Tabelas com dados referentes à 3FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 4 as tabelas Proj e ProjEmp não foram alteradas 13 AULA 4 Banco de Dados a tabela Emp não tem redundância pois sabendose a categoria que o em pregado pertence é possível obter seu salário por meio da tabela Categoria que foi criada na 3FN Atenção Não deixe de assistir à videoaula Normalização 1FN 2FN e 3FN com a professora Elisângela Botelho Gracias com uma explicação sobre a 1FN 2FN e 3FN Na Figura 5 temos um resumo da 1FN 2FN e 3FN Figura 5 Resumo das formas normais 1FN 2FN e 3FN Fonte Elmasri Navathe 2018 14 AULA 4 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018 LINGUAGEM SQL COMANDOS INSERT UPDATE DELETE E SELECT BÁSICO INTRODUÇÃO A linguagem SQL Structured Query Language é a linguagem padrão de aces so a bancos de dados relacionais Alguns exemplos de bancos de dados relacionais são Oracle Microsoft SQL Server MySQL dentre outros Os comandos da linguagem SQL podem ser divididos em três classes Linguagem de Definição de Dados DDL inclui comandos para definir alte rar e remover tabelas e índices Linguagem de Manipulação de Dados DML inclui comandos para inserir remover atualizar e consultar os dados armazenados nas tabelas Linguagem de Controle de Dados DCL inclui comandos para trabalhar em ambiente multiusuário permitindo estabelecer níveis de segurança e mani pular transações A seguir você aprenderá os seguintes comandos da linguagem SQL CREATE TABLE e DROP TABLE que fazem parte da DDL INSERT UPDATE DELETE e SELECT envolvendo uma única tabela Todos esses comandos fazem parte da DML Importante Você deve utilizar algum banco de dados relacional para aprender e pra ticar a linguagem SQL Uma sugestão é utilizar o Oracle na nuvem sendo necessário apenas acessar o link livesqloraclecom criar um usuário e começar a praticar Assista à videoaula Como utilizar o Oracle Live SQL da professora Elisângela Botelho Gracias com um exemplo de utilização deste serviço da Oracle 4 AULA 5 Banco de Dados Alguns dos operadores que você pode utilizar na linguagem SQL são Lógicos AND OR e NOT Aritméticos adição subtração multiplicação e divisão Relacionais e menor e menor ou igual respectivamente e maior e maior ou igual respectivamente e diferente e igual respectivamente LIKE especifica um padrão de comparação e BETWEEN especifica um intervalo de valores Conjunturais IN NOT IN dentre outros CREATE TABLE Este comando cria uma tabela dandolhe um nome e especificando seus atri butos chave primária chave estrangeira se for o caso e outras restrições Para cada atributo é definido um nome um domínio e se necessário uma restrição Sintaxe do comando CREATE TABLE Em que nometabela indica o nome da tabela a ser criada nomeatributo indica o nome do campo a ser criado na tabela tipo indica a definição do tipo de atributo INTEGER VARCHARN CHARN etc sendo N o número de caracteres 5 AULA 5 Banco de Dados tabelaorigem indica a tabela em que a chave estrangeira foi originalmente criada atributoorigem indica o nome da chave primária na tabela em que foi cria da NOT NULL não permite a inserção de valores nulos UNIQUE não permite que os valores de um atributo se repitam É possível ter vários atributos UNIQUE CHECK condição permite validar os valores inseridos neste atributo por meio de uma condição PRIMARY KEY é o atributo ou atributos que identifica unicamente cada linha da tabela ou seja não pode se repetir nem ser nulo FOREIGN KEY é o atributo que faz o relacionamento entre as tabelas e ele está sempre ligado à chave primária de uma outra tabela Atenção Tudo que está entre é opcional mas se for utilizar tireo E o ponto e vírgula indica a finalização de um comando da linguagem SQL Exemplo que será utilizado para explicar os comandos CREATE TABLE INSERT UPDATE e DELETE Modelo EntidadeRelacionamento MER 6 AULA 5 Banco de Dados Modelo Relacional gerado a partir do MER Exemplo 1 criação das três tabelas desse banco de dados O que você deve analisar cuidadosamente no script de criação das tabelas Aluno Disciplina e AlunoDisciplina O tipo de dado e restrições se houver de cada um dos atributos das tabelas Chave primária de cada uma das tabelas Chaves estrangeiras da tabela AlunoDisciplina 7 AULA 5 Banco de Dados DROP TABLE Este comando remove uma tabela existente no banco de dados Sintaxe do comando DROP TABLE Atenção Nas tabelas que criamos anteriormente não seria possível eliminar a tabe la Aluno ou a tabela Disciplina antes de eliminar a tabela AlunoDisciplina pois esta tem duas chaves estrangeiras que referenciam as tabelas Aluno e Disciplina INSERT O comando INSERT possibilita a inclusão de dados em uma tabela Sintaxe do comando INSERT Para inserir dados em uma tabela é necessário conhecer o script de criação da tabela para ver as restrições existentes como PRIMARY KEY FOREIGN KEY NOT NULL UNIQUE CHECK e não as violar Exemplo 2 inserção de um novo aluno com matrícula 1 e nome Joao 8 AULA 5 Banco de Dados Resultado da inserção Exemplo 2 e alguns comentários valores numéricos são inseridos como se escrevem enquanto as cadeias de caracteres têm sempre que ser delimitadas por aspas simples os valores serão inseridos nos respectivos atributos obedecendo a ordem em que foram escritos ou seja o primeiro valor corresponde ao primeiro atributo o segundo valor corresponde ao segundo atributo e assim sucessivamente Exemplo 3 inserção de mais dois alunos Resultado da inserção Exemplo 3 e alguns comentários observe que a tabela Aluno agora tem três linhas e cada aluno possui um valor para a Matrícula que é diferente dos demais alunos Exemplo 4 inserção de duas disciplinas e depois inserção de um mesmo aluno em duas disciplinas tabela AlunoDisciplina 9 AULA 5 Banco de Dados O que é importante você analisar nessas inserções O atributo NomeDisc foi definido como UNIQUE então não é possível ter duas disciplinas com mesmo nome A tabela AlunoDisciplina tem duas chaves estrangeiras portanto só é pos sível inserir alunos que já existam na tabela Aluno e disciplinas que já existam na tabela Disciplina No atributo Nota só poderá ser inserido um valor entre 0 e 10 Agora após as inserções as tabelas Aluno Disciplina e AlunoDisciplina têm os seguintes dados O INSERT poderá falhar em inúmeras situações dentre as quais se destacam ao tentar inserir mais de uma vez a mesma chave primária ao tentar inserir mais de uma vez o mesmo valor em um atributo UNIQUE ao tentar inserir o valor NULL em um atributo NOT NULL se o tipo do dado enviado na cláusula VALUES não estiver de acordo com o tipo de dado definido para aquele atributo 10 AULA 5 Banco de Dados se algum dos atributos obrigatórios for ignorado se o número de atributos for diferente do número de valores se existir algum tipo de restrição no atributo a que os dados não obedeçam UPDATE Este comando possibilita a atualização de dados em uma tabela Sintaxe do comando UPDATE Exemplo 5 aumente em um ponto a nota dos alunos que tiraram uma nota maior ou igual a 7 e menor ou igual a 9 Resultado da inserção Exemplo 5 e alguns comentários observe que na cláusula WHERE foram utilizados vários operadores AND e para criar as condições Importante Se você não colocar nenhuma condição para a atualização dos dados em uma tabela todas as linhas serão atualizadas desde que não viole as restrições definidas 11 AULA 5 Banco de Dados DELETE Este comando possibilita a remoção de dados em uma tabela Sintaxe do comando DELETE Exemplo 6 elimine todas as linhas da tabela AlunoDisciplina que tenham o Coddisc igual a 101 Resultado da inserção Exemplo 6 Importante se você não colocar nenhuma condição para a remoção dos dados em uma tabela todos os dados desta tabela serão removidos des de que não viole as restrições definidas A tabela ainda existe mas agora sem nenhum dado Atenção Não deixe de assistir à videoaula Linguagem SQL comandos CREATE e DROP TABLE INSERT UPDATE e DELETE com a professora Elisângela Botelho Gracias a qual contém uma explicação simples e breve sobre os comandos CREATE e DROP TABLE INSERT UPDATE e DELETE 12 AULA 5 Banco de Dados SELECT Este comando possibilita a consulta de uma ou mais tabelas de acordo com os critérios estabelecidos e com as necessidades Nesta aula você aprenderá a respei to de consultas envolvendo apenas uma tabela Sintaxe do comando SELECT Na sintaxe acima tudo que está entre é opcional e SELECT é o que se deseja no resultado da consulta DISTINCT não permite repetição de valores no resultado FROM é o local de onde buscar os dados necessários WHERE são condições para busca dos resultados GROUP BY formam agrupamento de dados HAVING são as condições para o agrupamento ORDER BY estabelece a ordenação desejada do resultado Os exemplos utilizados para explicar o comando SELECT utilizarão o seguinte banco de dados apresentado a seguir a Figura 1 apresenta o MER foi utilizada a ferramenta brModelo na Figura 2 temos o Modelo Relacional foi utilizada a ferramenta DBDesigner a Figura 3 tem as tabelas populadas deste banco de dados 13 AULA 5 Banco de Dados Figura 1 Modelo EntidadeRelacionamento MER Figura 2 Modelo Relacional gerado a partir do MER Figura 3 Tabelas populadas com dados 14 AULA 5 Banco de Dados Atenção O script de criação das tabelas e inserção de dados encontrase no final desta aula Exemplo 7 obtenha sem repetição o código de todas as peças que já foram utilizadas em quaisquer projetos em ordem crescente do código da peça Resultado da consulta Exemplo 7 e alguns comentários DISTINCT retorna o resultado da consulta eliminando as linhas duplicadas ou seja se mais de uma linha do resultado da consulta contém valores iguais ele só trará uma linha com estes valores ORDER BY traz o resultado da consulta ordenado e como foi utilizado ASC a ordem é crescente Exemplo 8 obtenha o nome e a duração em DIAS de cada projeto Resultado da consulta Exemplo 8 e alguns comentários 15 AULA 5 Banco de Dados observe que é possível utilizar os operadores aritméticos em uma consulta formatando o resultado da consulta da maneira que desejar o AS permite alterar o nome de um atributo expressão no resultado da con sulta Exemplo 9 obtenha em ordem crescente de preço o nome das peças de cor vermelha OU amarela E com preço de 9 18 22 40 ou 90 Resultado da consulta Exemplo 9 e alguns comentários observe que foram utilizados os operadores lógicos OR e AND foi utilizado também o operador IN que é igualdade para um conjunto de valores O NOT IN é a negação do IN Exemplo 10 obtenha o nome dos fornecedores residentes em cidades iniciadas com a letra S Resultado da consulta Exemplo 10 e alguns comentários LIKE permite a comparação de partes de uma cadeia de caracteres representa nenhum ou vários caracteres 16 AULA 5 Banco de Dados outros exemplos com o LIKE a retornaria todas as cidades que têm como último caractere a letra a os retornaria todas as cidades que têm as letras os não importan do o que vem antes nem depois Exemplo 11 obtenha o nome dos projetos com custo entre 20000 e 30000 inclusive estes valores Resultado da consulta Exemplo 11 Exemplo 12 obtenha o nome dos projetos que estão sem valor para duração ou seja a duração está nula Comentários sobre a consulta Exemplo 12 ela não retornou nenhum dado pois todos os projetos têm valor para duração se fosse para obter os projetos que têm algum valor para o atributo duração deveria utilizar IS NOT NULL Atenção Não deixe de assistir à videoaula SELECT Básico da professora Elisângela Botelho Gracias com uma explicação simples e breve sobre como elabo rar consultas simples 17 AULA 5 Banco de Dados SELECT COM FUNÇÕES AGREGADAS Podemos utilizar as seguintes funções agregadas em um SELECT AVG obtém o valor médio de um atributo COUNT obtém o número de linhas analisadas MAX obtém o maior valor de um atributo MIN obtém o menor valor de um atributo SUM obtém a soma dos valores de um atributo O COUNT pode ser utilizado de três formas COUNT devolve o número de linhas que resulta de um SELECT COUNTAtributo devolve o número de linhas em que esse atributo não é NULL COUNTDISTINCT Atributo devolve o número de linhas sem repetição desse atributo Atenção Observe nos exemplos a seguir que serão utilizadas as funções agrega das e tudo que vimos anteriormente Exemplo 13 obtenha a média dos custos dos projetos que têm duração maior ou igual a três meses 18 AULA 5 Banco de Dados Exemplo 14 obtenha a quantidade de fornecedores que pertencem a cidades iniciadas com a letra S Exemplo 15 obtenha o valor mínimo e máximo de custo de um projeto SELECT COM GROUP BY Quando se deseja aplicar as funções agregadas a vários grupos de uma tabela ou várias devese utilizar o agrupamento GROUP BY Neste caso é necessário particionar a tabela em grupos que possuem o mesmo valor de atributo A cláusula GROUP BY especifica os atributos de agrupamento Para cada grupo normalmente especificase a função agregada ou as funções agregadas desejada Exemplo prático se você precisa fazer uma consulta para obter a mé dia de idade dos alunos de cada curso da universidade deve utilizar o GROUP BY sendo que o grupo é o atributo curso e a informação de cada curso é a média de idade dos alunos Neste caso para cada valor do atributo curso é criado um grupo e sobre cada grupo é calculada a média de idade Exemplo 16 obtenha o número de cada peça e a quantidade total de cada peça utilizada em todos os projetos em ordem crescente do número da peça Antes de mostrar a consulta em SQL você deverá entender os grupos que serão criados conforme exemplificado a seguir 19 AULA 5 Banco de Dados Resultado da consulta Exemplo 16 e alguns comentários observe que o atributo utilizado para agrupar foi PeNro Logo para cada valor diferente de PeNro foi criado um grupo para cada grupo foi calculada a quantidade total SUM utilizada de cada peça Exemplo 17 obtenha o número de cada peça e a quantidade total de cada peça utilizada em todos os projetos desde que esse total seja menor que três Retorne primeiro em ordem decrescente este total e depois em ordem crescente do número da peça Resultado da consulta Exemplo 17 e alguns comentários 20 AULA 5 Banco de Dados observe que esta consulta é muito semelhante à do exemplo 16 tendo so mente uma condição do agrupamento SUMQuant 3 a cláusula HAVING é a condição de um agrupamento portanto ela só existe se a consulta tiver a cláusula GROUP BY lembrese de que a cláusula WHERE de uma consulta é condição de cada linha da tabela e não do agrupamento portanto em uma consulta com agrupamento é possível ter condições de cada linha da tabela WHERE e condições de um agrupamento HAVING Exemplo 18 obtenha somente o nome das cidades que têm apenas um único fornecedor em ordem crescente do nome da cidade Resultado da consulta Exemplo 18 e alguns comentários observe que foi feito o agrupamento utilizando o atributo FCidade logo temos quatro grupos conforme ilustrado a seguir como foi solicitado as cidades com apenas um único fornecedor utilizouse a cláusula HAVING a quantidade de fornecedores por cidade não aparece no resultado da con sulta pois foi solicitado somente o nome das cidades 21 AULA 5 Banco de Dados Exemplo 19 obtenha a quantidade de fornecedores de cada peça em cada projeto em ordem decrescente desta quantidade e depois em ordem crescente do projeto Resultado da consulta Exemplo 19 e um comentário observe que foi feito o agrupamento utilizando dois atributos PeNro e PNro Exemplo prático podemos agrupar os alunos de uma universidade por curso e turma para saber a média de idade dos alunos de cada turma de cada curso da universidade Neste caso serão utilizados os atributos curso e turma no GROUP BY Atenção Não deixe de assistir à videoaula SELECT com agrupamento de dados da professora Elisângela Botelho Gracias com uma explicação simples e breve sobre como criar consultas utilizando o GROUP BY Importante o script de criação do banco de dados utilizado para o comando SELECT está disponível a seguir 22 AULA 5 Banco de Dados Script de criacao do banco de dados utilizado no SELECT CREATE TABLE Peca PeNro CHAR4 PeNome VARCHAR30 NOT NULL PePreco INTEGER NOT NULL PeCor VARCHAR20 NOT NULL PRIMARY KEYPeNro CREATE TABLE Fornecedor FNro CHAR4 FNome VARCHAR30 NOT NULL FCidade VARCHAR30 NOT NULL FCategoria CHAR1 NOT NULL PRIMARY KEYFNro CREATE TABLE Projeto PNro CHAR4 PNome VARCHAR30 NOT NULL PDuracao INTEGER NOT NULL PCusto INTEGER NOT NULL 23 AULA 5 Banco de Dados PRIMARY KEYPNro CREATE TABLE Fornecepara PeNro CHAR4 FNro CHAR4 PNro CHAR4 Quant INTEGER PRIMARY KEYPeNroFNroPNro FOREIGN KEYPeNro REFERENCES PecaPeNro FOREIGN KEYFNro REFERENCES FornecedorFNro FOREIGN KEYPNro REFERENCES ProjetoPNro INSERT INTO Peca VALUES PE1 Cinto 22 Azul INSERT INTO Peca VALUES PE2 Volante 18 Vermelho INSERT INTO Peca VALUES PE3 Lanterna 14 Preto INSERT INTO Peca VALUES PE4 Limpador 9 Amarelo INSERT INTO Peca VALUES PE5 Painel 43 Vermelho INSERT INTO Fornecedor VALUES F1 Plastec Campinas B INSERT INTO Fornecedor VALUES F2 CM Sao Paulo D 24 AULA 5 Banco de Dados INSERT INTO Fornecedor VALUES F3 Kirurgic Campinas A INSERT INTO Fornecedor VALUES F4 Piloto Piracicaba A INSERT INTO Fornecedor VALUES F5 Equipament Sao Carlos C INSERT INTO Projeto VALUES P1 Detroit 5 43000 INSERT INTO Projeto VALUES P2 Pegasus 3 37000 INSERT INTO Projeto VALUES P3 Alfa 2 26700 INSERT INTO Projeto VALUES P4 Sea 3 21200 INSERT INTO Projeto VALUES P5 Paraiso 1 17000 INSERT INTO Fornecepara VALUES PE1 F5 P4 5 INSERT INTO Fornecepara VALUES PE2 F2 P2 1 INSERT INTO Fornecepara VALUES PE3 F3 P4 2 INSERT INTO Fornecepara VALUES PE4 F4 P5 3 INSERT INTO Fornecepara VALUES PE5 F1 P1 1 INSERT INTO Fornecepara VALUES PE2 F2 P3 1 INSERT INTO Fornecepara VALUES PE4 F3 P5 2 25 AULA 5 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018 RAMAKRISHNAN R GEHRKE J Sistemas de gerenciamento de banco de dados SELECT CONSULTAS COMPLEXAS INTRODUÇÃO Na Aula 5 você aprendeu o comando SELECT com uma única tabela logo após a cláusula FROM Mas o conceito de banco de dados reúne várias tabelas relaciona das Então em vários momentos você precisará acessar dados de diferentes tabelas simultaneamente Para isso podese utilizar o conceito de join ou seja junção de tabelas que se relacionam Uma das formas de trabalhar com junção de duas ou mais tabelas é colocando na cláusula WHERE a condição de junção entre as tabelas que estão logo após o FROM Uma outra forma é utilizando o INNER JOIN Você aprenderá a seguir estas duas formas e também LEFT JOIN RIGHT JOIN FULL JOIN e consultas aninhadas Importante Você observará que tudo que aprendeu na Aula 5 será utilizado nesta aula também Lembrando novamente que você deve usar algum banco de dados relacional para aprender e praticar o comando SELECT a ser estudado nesta aula Uma sugestão é utilizar o Oracle na nuvem assista à videoaula Como utilizar o Oracle Live SQL da Aula 5 Só para você relembrar a sintaxe do comando SELECT Os exemplos utilizados para explicar o comando SELECT desta aula utilizarão o seguinte banco de dados é o mesmo banco de dados do Texto de Apoio sobre Álgebra Relacional Aula 4 4 AULA 6 Banco de Dados na Figura 1 temos o Modelo Relacional foi utilizada a ferramenta DBDesigner a Figura 2 tem as tabelas populadas deste banco de dados Atenção O script de criação das tabelas e inserção de dados encontrase no final desta aula Figura 1 Representação do Modelo Relacional utilizando a ferramenta DBDesigner Figura 2 Tabelas Departamento Funcionario Projeto e FuncProj exemplificadas com dados 5 AULA 6 Banco de Dados JUNÇÃO NA CLÁUSULA WHERE Para você entender a junção será exemplificado no Exemplo 1 o comando SELECT e os dados retornados com a junção completa das tabelas Funcionario e Departamento ou seja todos os atributos e todas as linhas combinadas Exemplo 1 junção completa das tabelas Funcionario e Departamento Resultado da consulta Exemplo 1 Considerações importantes sobre a junção de Funcionario com Departamento a junção respeita o relacionamento entre Funcionario e Departamento pois combina cada linha de Funcionario com a única linha do Departamento a que ela realmente pertence observe que o departamento de código 3 cujo nome é Dados não apareceu neste resultado pois não teve nenhuma linha combinada com Funcionario foi inserida na cláusula WHERE a condição de junção entre elas F CodDepto D CodDepto que combina duas linhas uma de Funcionario e outra de Departamento sempre que o valor do atributo CodDepto da tabela Funcionario for igual ao valor do atributo CodDepto da tabela Departamento um mesmo nome de atributo pode ser utilizado em tabelas diferentes Portanto quando uma consulta envolver duas ou mais tabelas e fizer refe rência a atributos com o mesmo nome de tabelas diferentes é preciso dizer de qual tabela ele pertence Isso é feito prefixando o nome da tabela ao nome 6 AULA 6 Banco de Dados do atributo e separando os dois por um ponto nometabelanomeatributo Para não ter que ficar escrevendo o nome da tabela completa podemos dar um apelido à ela como foi feito no Exemplo 1 é só colocar o apelido logo após o nome da tabela Funcionario F Importante A condição de junção entre tabelas combina chave primária de uma tabela com chave estrangeira da outra tabela A junção de n tabelas em um único SELECT obriga a colocação de pelo menos n1 condições de junção Se mais de uma tabela for especificada na cláusula FROM e não for inserida a condição de junção entre elas gerase o produto cartesiano que você já viu na Aula 4 sobre Álgebra Relacional O produto cartesiano gera todas as combinações possíveis entre as linhas das tabelas que estão na cláusula FROM e neste caso não serão respeitados os relacionamentos existentes entre as tabelas O comando para gerar o produto cartesiano entre as tabelas Funcionario e Departamento é O Exemplo 1 mostrou a junção completa entre as tabelas Funcionario e Departamento mas você pode inserir restrições de linhas e colunas aplicar funções agregadas fazer agrupamentos etc Você verá isso nos próximos exemplos Exemplo 2 obtenha o nome de cada funcionário e o nome do departamento de cada um mas somente para os funcionários que ganham mais de 1600 Observe que para fazer esta consulta precisamos utilizar as tabelas Funcionario e Departamento então fazemos a junção entre elas e você já sabe que elas se relacionam para obter o resultado 7 AULA 6 Banco de Dados Resultado da consulta Exemplo 2 e alguns comentários a condição da cláusula WHERE F Salario 1600 é uma condição de se leção que busca na tabela Funcionario somente as linhas em que os valores para o atributo Salario sejam maior que 1600 quando temos condições de seleção a condição de junção deve obrigato riamente ser precedida pelo operador AND uma vez que essa condição de junção sempre deve ser verdadeira Exemplo 3 junção com função agregada obtenha a quantidade de funcio nários do departamento de Vendas Resultado da consulta Exemplo 3 e um comentário observe que foi feita a junção das tabelas Funcionario e Departamento e também foi utilizada a função agregada COUNT Exemplo 4 junção com agrupamento obtenha o nome de cada departa mento que tenha funcionário e a quantidade de funcionários em cada um deles em ordem crescente do nome do departamento 8 AULA 6 Banco de Dados Resultado da consulta Exemplo 4 e alguns comentários observe que foi feita a junção das tabelas Funcionario e Departamento e também o agrupamento dos departamentos por nome o departamento de Dados não apareceu no resultado pois ele não tem ne nhum funcionário relacionado a ele Exemplo 5 junção de três tabelas obtenha o nome de cada funcionário que já tenha participado de algum projeto e o nome dos projetos de que cada um participou Observe que para fazer esta consulta precisamos utilizar as tabelas Funcionario FuncProj e Projeto então fazemos a junção dessas três tabelas para obter o resul tado Resultado da consulta Exemplo 5 e alguns comentários observe que foi necessária a junção de três tabelas portanto temos duas con dições de junção F CodFunc FP CodFunc e FP CodProj P CodProj 9 AULA 6 Banco de Dados se pelo menos uma das condições de junção acima não aparecer na cláusula WHERE o produto cartesiano ocorrerá observe também que o funcionário de nome Marcio Silva Santana não apareceu no resultado desta consulta pois ele não participou de nenhum projeto ainda Atenção Não deixe de assistir à videoaula SELECT com junção de tabelas na cláu sula WHERE com a professora Elisângela Botelho Gracias JUNÇÃO COM INNER JOIN Você aprendeu que a junção de várias tabelas pode ser feita colocandose os nomes de todas as tabelas envolvidas na cláusula FROM e as condiçãoões de junção na cláusula WHERE Agora você verá que também podemos utilizar o INNER JOIN para fazer a junção de tabelas Para isso utilize as palavraschave INNER JOIN e as condiçãoões de junção ésão indicadas pela palavrachave ON dentro da cláusula FROM observe que a condição de junção não aparece mais na cláusula WHERE Exemplo 6 junção completa das tabelas Funcionario e Departamento utilizan do o INNER JOIN Resultado da consulta Exemplo 6 e alguns comentários 10 AULA 6 Banco de Dados observe que o resultado desta consulta é o mesmo do Exemplo 1 comparando esta consulta com a do Exemplo 1 no lugar da vírgula entre as tabelas colocase o INNER JOIN e a condição de junção agora fica na cláusula ON o INNER JOIN traz somente as linhas combinadas de acordo com a condição de junção FCodDepto DCodDepto ou seja ele respeita o relacionamento entre Funcionario e Departamento pois combina cada linha de Funcionario com a única linha do Departamento a que ele realmente pertence observe novamente que o departamento de código 3 cujo nome é Dados não apareceu neste resultado pois não teve nenhuma linha combinada com Funcionario Atenção A seguir você verá como ficam os Exemplos 2 3 4 e 5 com a utilização do INNER JOIN Lembrando que o resultado é o mesmo da junção feita na cláusula WHERE Exemplo 7 mesmo do Exemplo 2 obtenha o nome de cada funcionário e o nome do departamento de cada um mas somente para os funcionários que ganham mais de 1600 Resultado da consulta Exemplo 7 e um comentário na cláusula WHERE temos apenas a condição F Salario 1600 uma vez que a condição de junção FCodDepto DCodDepto fica na cláusula ON 11 AULA 6 Banco de Dados Exemplo 8 mesmo do Exemplo 3 obtenha a quantidade de funcionários do departamento de Vendas Resultado da consulta Exemplo 8 Exemplo 9 mesmo do Exemplo 4 obtenha o nome de cada departamento que tenha funcionário e a quantidade de funcionários em cada um deles em ordem crescente do nome do departamento Resultado da consulta Exemplo 9 Exemplo 10 mesmo do Exemplo 5 obtenha o nome de cada funcionário que já tenha participado de algum projeto e o nome dos projetos de que cada um participou 12 AULA 6 Banco de Dados Resultado da consulta Exemplo 10 e alguns comentários observe que primeiro foi feita a junção INNER JOIN das tabelas Funcionario e FuncProj cuja condição de junção é F CodFunc FP CodFunc Depois com o resultado da junção anterior foi feito o INNER JOIN com Projeto cuja condição de junção é FP CodProj P CodProj observe novamente que o funcionário de nome Marcio Silva Santana não apareceu no resultado dessa consulta pois ele não participou de nenhum projeto ainda JUNÇÃO COM LEFT JOIN Você já aprendeu que a junção entre duas tabelas gera um resultado no qual temos linhas que se combinam de acordo com a condição de junção Mas existem também as junções externas que geram o resultado da junção as linhas combinadas mais as linhas não combinadas A palavrachave LEFT JOIN gera um resultado contendo as linhas combinadas e as linhas não combinadas da tabela que estiver do lado esquerdo Exemplo 11 junção externa completa das tabelas Funcionario e Departamento utilizando o LEFT JOIN 13 AULA 6 Banco de Dados Resultado da consulta Exemplo 11 e alguns comentários o LEFT JOIN retorna as linhas combinadas entre as tabelas Departamento e Funcionario e também as linhas de Departamento do lado esquerdo que não estão ligadas a nenhum funcionário observe na primeira linha do resultado que o departamento de Dados apa receu no resultado mas não está ligado a nenhum funcionário por isso os dados referentes a um funcionário aparecem como nulos analise com atenção que no LEFT JOIN se trocarmos a posição das tabelas Departamento e Funcionario o resultado será alterado Exemplo 12 obtenha os nomes de todos os departamentos da empresa em ordem crescente com os nomes dos funcionários que trabalham em cada um deles Resultado da consulta Exemplo 12 e um comentário 14 AULA 6 Banco de Dados observe que o departamento de Dados não tem funcionário mas apareceu no resultado Exemplo 13 obtenha os nomes de todos os departamentos da empresa e a quantidade de funcionários pertencentes a cada um deles retorne mesmo aqueles departamentos em que não tem funcionários Este exemplo é parecido com o Exemplo 9 sendo que a única diferença é que este trará um resultado com todos os departamentos e não somente com os que têm funcionários Resultado da consulta Exemplo 13 e alguns comentários observe que o departamento de Dados apareceu no resultado mas sem funcionários ou seja zero atentese ao atributo que você colocará no COUNT pois se ele for nulo não contará com uma linha que é o que aconteceu com o departamento de Dados que tem zero funcionários JUNÇÃO COM RIGHT JOIN Você aprendeu a junção externa LEFT JOIN anteriormente Temos também a junção externa RIGHT JOIN A palavrachave RIGHT JOIN gera um resultado contendo as linhas combinadas e as linhas não combinadas da tabela que estiver do lado direito 15 AULA 6 Banco de Dados Assim o resultado de uma junção externa de um lado depende da direção DIREITA ou ESQUERDA e da posição dos nomes das tabelas Exemplo 14 obtenha os nomes de todos os funcionários da empresa e o nome dos projetos de que cada um já participou retorne mesmo aqueles funcionários que ainda não participaram de nenhum projeto Este exemplo é parecido com o exemplo 10 com INNER JOIN sendo que a única diferença é que este trará um resultado com todos os funcionários e não somente os funcionários que já participaram de algum projeto Analise cuidadosamente as três consultas a seguir que trazem o mesmo resul tado Utilizando somente LEFT JOIN Utilizando RIGHT JOIN e LEFT JOIN Utilizando INNER JOIN e RIGHT JOIN 16 AULA 6 Banco de Dados Resultado da consulta Exemplo 14 e um comentário observe que o funcionário Marcio Silva Santana apareceu no resultado mas não tem nenhum projeto vinculado a ele Atenção Não deixe de assistir à videoaula SELECT com INNER LEFT e RIGHT JOIN com a professora Elisângela Botelho Gracias FULL JOIN A palavrachave FULL JOIN gera um resultado contendo as linhas combinadas e também todas as linhas não combinadas das tabelas envolvidas neste FULL JOIN Exemplo 15 obtenha todas as informações de funcionários com as respecti vas informações do departamento a que cada um pertence e também as infor mações daqueles departamentos que não têm funcionários e dos funcionários que não estão ligados a nenhum departamento 17 AULA 6 Banco de Dados Um comentário Exemplo 15 esta consulta retorna todas as linhas combinadas de Departamento e Funcionario todos os departamentos que não têm funcionários e todos os funcionários que não estão ligadas a um departamento SELECT COM CONSULTAS ANINHADAS Uma consulta aninhada é aquela que tem outra consulta embutida dentro dela sendo que a consulta embutida é chamada de subconsulta Uma subconsulta aparece tipicamente dentro da cláusula WHERE de uma con sulta mas pode aparecer também na cláusula SELECT FROM ou HAVING E uma subconsulta pode conter outras subconsultas aninhadas Importante Uma subconsulta sempre retornará um valor ou um conjunto de valo res para uma consulta aninhada Então o que está sendo retornado por uma subconsulta tem de ser compatível com o que se está comparando Observe atentamente os exemplos que veremos a seguir Exemplo 16 obtenha os nome e salário dos funcionários que ganham mais do que a média salarial paga na empresa Resultado da consulta Exemplo 16 e alguns comentários 18 AULA 6 Banco de Dados observe que a subconsulta retorna a média salarial dos funcionários da empresa esta média salarial que está sendo retornada pela subconsulta será utilizada para obter quais funcionários têm o salário maior que a média salarial Exemplo 17 obtenha sem repetição e em ordem crescente o nome dos depar tamentos que têm algum funcionário Observe que nesta consulta você poderá utilizar o INNER JOIN que é a melhor opção e também é uma opção a consulta aninhada Resolução com INNER JOIN Resolução com consulta aninhada Resultado da consulta Exemplo 17 e alguns comentários observe na segunda resolução que a subconsulta retornará todos os va lores de código de departamento da tabela Funcionario ou seja o código dos departamentos que têm funcionários os valores dos códigos de departamentos que estão sendo retornados pela subconsulta serão utilizados para obter o nome dos departamentos em que temos algum funcionário 19 AULA 6 Banco de Dados você deve se lembrar do operador IN que é igualdade para um conjunto de valores Utilizamos esse operador IN pois uma subconsulta pode retornar vários valores se você desejasse saber o nome dos departamentos que não têm funcioná rios bastava trocar o IN por NOT IN na segunda resolução dessa consulta Exemplo 18 obtenha o nome de cada departamento que tenha funcionário e a quantidade de funcionários em cada um deles mas somente para os departa mentos que têm mais funcionários do que o departamento de Pesquisa Resultado da consulta Exemplo 18 e alguns comentários observe que a subconsulta retornará somente a quantidade de funcio nários do departamento de Pesquisa e para obter este valor foi feito um INNER JOIN entre as tabelas Funcionario e Departamento esta quantidade de funcionários de Pesquisa a ser retornada pela subcon sulta será utilizada na cláusula HAVING Atenção Não deixe de assistir à videoaula SELECT com subconsulta com a pro fessora Elisângela Botelho Gracias 20 AULA 6 Banco de Dados Uma subconsulta pode ser utilizada nos comandos UPDATE e DELETE e isso será necessário quando a atualização dos dados for feita em uma determinada tabela mas os critérios para essa atualização envolvem dados de outras tabelas a eliminação de dados for feita em uma determinada tabela mas os critérios para essa eliminação envolvem dados de outras tabelas Exemplo 19 aumente em 10 o salário dos funcionários do departamento de Marketing Alguns comentários Exemplo 19 observe que a atualização está sendo feita na tabela Funcionario mas o cri tério para atualização envolve o nome do departamento que se encontra na tabela Departamento logo a subconsulta retorna o código do departamento cujo nome seja Marketing este código de departamento que será retornado pela subconsulta será utilizado no UPDATE 21 AULA 6 Banco de Dados Script do Banco de Dados utilizado nesta Aula 6 22 AULA 6 Banco de Dados 23 AULA 6 Banco de Dados REFERÊNCIAS DAMAS L SQL Structured Query Language 6 ed Rio de Janeiro LTC 2007 ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018 RAMAKRISHNAN R GEHRKE J Sistemas de gerenciamento de banco de dados LINGUAGEM PLSQL PROCEDURE FUNCTION E TRIGGER INTRODUÇÃO Nas aulas 5 e 6 você aprendeu a linguagem SQL que é a linguagem padrão para o acesso a bancos de dados relacionais Nesta aula você conhecerá a linguagem PLSQL que é uma linguagem proprietária do SGBD Oracle de programação de banco de dados Utilizando esta linguagem você poderá criar os seguintes objetos de um banco de dados procedure function e trigger Importante Observe que você utilizará nesta aula quase tudo que aprendeu nas aulas 5 e 6 A única diferença é que nesta aula você deverá utilizar o banco de dados Oracle para aprender e praticar a linguagem PLSQL procedure function e trigger A sugestão novamente é utilizar o Oracle na nuvem assista à videoaula Como utilizar o Oracle Live SQL da Aula 5 LINGUAGEM PLSQL PLSQL como já dito é uma linguagem proprietária de programação de ban co de dados do SGBD Oracle A linguagem PLSQL utiliza o conceito de bloco estruturado e sua estrutura é composta por três seções Seção de declaração DECLARE todos os objetos são declarados é opcional Seção de execução os comandos PLSQL são colocados Seção de exceção EXCEPTION os erros são tratados é opcional 4 AULA 7 Banco de Dados Estrutura de um bloco PLSQL Seção de Declaração A seção de declaração é a primeira do bloco PLSQL Esta seção é opcional pois ela só será utilizada se for preciso criar variáveis ou constantes as quais devem ser previamente declaradas antes de serem utilizadas em comandos Esta seção é iniciada pela palavrachave DECLARE Algumas observações importantes sobre variáveis e constantes devem começar com uma letra e ter no máximo 30 caracteres não podem ser idênticas aos nomes de tabelas ou aos nomes de atributos não podem ser idênticas a nenhuma palavra ou símbolo reservado As variáveis podem ter qualquer tipo de dado válido pela linguagem SQL e Oracle como Alfanuméricos CHARN VARCHARN nos quais N é o comprimento máximo Numéricos INTEGER FLOAT REAL NUMBERWD DECIMALWD nos quais W é a largura e D é o número de dígitos à direita da vírgula decimal Data DATE que armazena informações de data e hora 5 AULA 7 Banco de Dados Exemplo 1 declaração e inicialização de uma variável qtdadeitens declaração de uma constante valorfixo e declaração de outra variável nome Comentários sobre o Exemplo 1 observe que o comando é o comando de atribuição o deve ser colocado ao final de cada declaração de variável ou constante outra forma de atribuir o tipo de dado a uma variável é herdando o tipo de dados de um atributo de uma tabela da seguinte maneira nomevariável nometabela nomeatributotype Seção de Execução A seção de execução do bloco PLSQL é obrigatória iniciada com a declaração BEGIN finalizada com END e pode conter comandos SQL comandos de controles lógicos comandos de atribuição dentre outros Você verá diversos exemplos nesta aula Seção de Exceção A seção de exceção do bloco PLSQL também é opcional e pode ser utilizada para tratar um erro que eventualmente ocorra durante a execução de um progra ma PLSQL FUNCIONAMENTO DO PLSQL Um bloco PLSQL executa os comandos procedurais e repassa os comandos SQL para o servidor Oracle processar Como a maioria das linguagens procedurais a linguagem PLSQL possui co mandos para controlar o fluxo de execução do programa tais como estruturas de controles condicionais estruturas de controles sequenciais e estruturas de controles de repetição Você aprenderá a sintaxe de IF WHILE e FOR a seguir 6 AULA 7 Banco de Dados Sintaxe do comando IF tudo que estiver entre colchetes é opcional Exemplo 2 utilização do IF ELSIF e ELSE Comentários sobre o Exemplo 2 mediasal é uma variável se mediasal 2000 então todos os funcionários receberão 40 de aumento se mediasal 3000 então todos os funcionários receberão 30 de aumento ou seja se nenhuma das condições anteriores forem verdadeiras todos os funcionários receberão 20 de aumento observe que foi utilizado o comando UPDATE com IF ELSE ELSIF não se esqueça de finalizar com o comando UPDATE sempre que tiver um IF não se esqueça de finalizálo com END IF Sintaxe do comando FOR Este comando executa um trecho de instruções de forma iterativa por meio de um contador com valor inicial e final Essa contagem pode ser crescente IN ou decrescente REVERSE 7 AULA 7 Banco de Dados Sintaxe do comando WHILE Este comando analisa uma condição e somente se ela for verdadeira executa os comandos contidos dentro dessa estrutura Exemplos completos de um bloco PLSQL Nesta seção você aprenderá vários exemplos de um bloco PLSQL e poderá testar utilizando o Oracle httpslivesqloraclecom Atenção para os exemplos 3 4 e 5 a seguir considere a criação da seguinte tabela Temp1 Exemplo 3 comandos WHILE e INSERT o bloco PLSQL a seguir insere dez linhas na tabela Temp1 enquanto a variável i 10 Para o valor do atributo código é inserido o valor de i e para o atributo data a data atual sysdate Execute este bloco e após sua execução verifique os dados da tabela Temp1 Comentários sobre o Exemplo 3 as linhas 1 e 2 fazem parte da seção de declaração sendo que na linha 2 a variável i está sendo declarada e inicializada 8 AULA 7 Banco de Dados da linha 3 até a linha 11 temos a seção de execução que está entre o BEGIN e END na linha 4 temos o comando WHILE e dentro deste comando o comando INSERT na linha 9 temos o incremento da variável i sempre que tiver um WHILE não se esqueça de finalizálo com END LOOP não se esqueça de finalizar com o comando INSERT observe que é fundamental a utilização do ao final de cada comando Exemplo 4 comandos FOR IF ELSE e INSERT o bloco PLSQL a seguir é semelhante ao Exemplo 3 pois também insere linhas na tabela Temp1 Aqui utili zouse o comando FOR e dentro desse comando temos o IF e o ELSE Ou seja se o valor de i for múltiplo de três será inserida uma nova linha em Temp1 com o valor de i e a data do sistema nos atributos código e data respectivamente caso contrário é feito um cálculo para que seja inserido o valor de i5 no atributo código e a data do sistema somada ao valor de i no atributo data ou seja a data atual somada com i dias Execute este bloco e após sua execução verifique os dados da tabela Temp1 Comentários sobre o Exemplo 4 observe que neste exemplo não temos a seção de declaração pois a variável i foi inicializada no comando FOR 9 AULA 7 Banco de Dados na linha 2 temos o comando FOR que se inicia em 20 e vai até 30 com incre mento de 1 e dentro deste comando temos os comandos IF ELSE e INSERT sempre que tiver um IF não se esqueça de finalizálo com END IF sempre que tiver um FOR não se esqueça de finalizálo com END LOOP lembrese sempre do ao final de cada comando Exemplo 5 SELECT INTO o bloco PLSQL a seguir insere uma única linha na tabela Temp1 mas esse bloco nunca violará a chave primária pois será buscado o maior valor que tem na tabela Temp1 do atributo código Somando 1 a este valor ele será inserido no atributo código e a data será o sysdate Execute este bloco e após sua execução verifique os dados da tabela Temp1 Comentários sobre o Exemplo 05 nas linhas 4 e 5 foi feito um SELECT para buscar o maior valor de código e o INTO armazena esse resultado na variável codmax o que está sendo retornado tem de ser compatível com o tipo de dado da variável na linha 7 foi utilizado o pacote DbmsOutputPutLine que é empregado para apresentação de mensagens Observe que após a execução deste bloco apareceu uma mensagem Maior valor 145 Atenção Não deixe de assistir à videoaula Linguagem PLSQL com a professora Elisângela Botelho Gracias 10 AULA 7 Banco de Dados PROCEDURE Uma procedure é um grupo de comandos SQL e PLSQL que executam uma determinada tarefa A definição e o funcionamento de uma procedure são simila res à programação em outras linguagens Uma procedure precisa ser chamada a partir de um programa ou ser executada manualmente pelo usuário Sintaxe da procedure tudo que estiver entre colchetes é opcional OR REPLACE recria uma procedure já existente sem ter de eliminála Argumento é o nome da variável que será enviada ou retornada do ambiente chamador para a procedure e pode ser passada em um dos três modos IN OUT IN OUT IN especifica que se deve determinar um valor para o argumento quando o procedimento for chamado Se não for especificado nenhum modo o IN é o padrão OUT especifica que o procedimento devolve um valor para esse argu mento quando o procedimento for chamado IN OUT especifica que se deve determinar um valor para o argumento quando o procedimento for chamado e que o procedimento devolve um valor ao seu ambiente de chamada após sua execução Tipodedados é o tipo de dado do argumento que deve ser especificado sem comprimento precisão ou escala por exemplo VARCHAR 11 AULA 7 Banco de Dados BLOCO PLSQL é o bloco PLSQL que o Oracle executa Atenção os exemplos 6 até 11 a partir de agora utilizarão o seguinte banco de dados é o mesmo banco de dados utilizando no Texto de Apoio da Aula 6 SELECT Consultas Complexas na Figura 1 temos o Modelo Relacional foi utilizada a ferramenta DBDesigner a Figura 2 tem as tabelas populadas deste banco de dados Atenção O script de criação das tabelas e a inserção de dados encontrase no final desta aula Figura 1 Representação do Modelo Relacional utilizando a ferramenta DBDesigner 12 AULA 7 Banco de Dados Figura 2 Tabelas Departamento Funcionario Projeto e FuncProj exemplificadas com dados Exemplo 6 procedure desejase criar uma procedure que aumente o salário dos funcionários de um determinado departamento de acordo com um percen tual Ou seja serão passados dois argumentos nome do departamento e valor do percentual valor inteiro Comentários sobre o Exemplo 6 nas linhas 2 e 3 foram definidos os dois argumentos da procedure nome Depto e percentual os dois argumentos possuem o modo IN ou seja quando essa procedure for chamada deverá ser passado um valor para cada um dos argumentos da linha 6 até a 11 foi feito um UPDATE com subselect e os argumentos percentual e nomeDepto foram utilizados 13 AULA 7 Banco de Dados Executando a procedure do Exemplo 6 A execução de uma procedure é feita por meio de uma chamada ao seu nome Uma maneira é pelo comando EXECUTE Observe que deve ser obedecida a ordem em que os argumentos foram criados na procedure na hora de passar os valores A execução desta procedure com os valores de argumentos acima fará o seguinte aumentará em 50 o salário dos funcionários que são do departa mento de Vendas Para você verificar se a procedure funcionou realmente execute um SELECT a seguir e observe que os funcionários do departamento de Vendas recebe ram o aumento de 50 Exemplo 7 procedure outra forma de fazer o Exemplo 6 14 AULA 7 Banco de Dados Comentários sobre o Exemplo 7 na linha 5 a variável local vdepto foi declarada da linha 7 até 9 foi feito um SELECT para descobrir o código do departamento cujo nome será passado como argumento este código será armazenado na variável local vdepto da linha 11 até 13 foi feito o UPDATE utilizando a variável local vdepto e o argumento percentual da linha 15 até 17 está sendo tratado um erro quando a procedure for chama da e for passado um valor para o nome de departamento que não existe Exemplo 8 procedure desejase criar uma procedure que insira um funcioná rio em um projeto já existente considerando que serão passados três argumentos nome do funcionário e do projeto já existentes no banco de dados e também o número de horas que ele trabalhou neste projeto Comentários sobre o Exemplo 8 a linha 2 foram definidos os três argumentos da procedure nfunc nproj e horas nas linhas 4 e 5 foram definidas duas variáveis locais vcodf e vcodp 15 AULA 7 Banco de Dados da linha 7 até 9 foi feito um SELECT para descobrir o código do funcionário cujo nome será passado como argumento este código será armazenado na variável local vcodf da linha 11 até 13 foi feito um SELECT para descobrir o código do projeto cujo nome será passado como argumento este código será armazenado na vari ável local vcodp da linha 15 até 17 foi feito o INSERT utilizando as variáveis locais vcodf e vcodp da linha 19 até 21 está sendo tratado um erro quando a procedure for chama da e for passado um valor para o nome de funcionário e ou projeto que não existem Executando a procedure do Exemplo 8 A execução desta procedure com os valores de argumentos acima inserirá uma linha na FuncProj Observe que deve ser obedecida a ordem em que os argumentos foram criados na procedure na hora de passar os valores Para você verificar se a procedure funcionou realmente faça um SELECT na tabela FuncProj e observe se uma nova linha foi inserida A exclusão de uma procedure é feita com o seguinte comando Atenção Não deixe de assistir à videoaula Procedure e Function com a professora Elisângela Botelho Gracias 16 AULA 7 Banco de Dados FUNCTION Function é um subprograma que tem por objetivo retornar algum resultado ou valor Uma function é muito semelhante a uma procedure do ponto de vista estrutu ral sendo que a diferença é que uma function tem a inclusão da cláusula RETURN A função pode ser invocada por meio de um comando SELECT e também usada em cálculos como outra função do Oracle já que ela sempre retorna um valor Sintaxe da function tudo que estiver entre colchetes é opcional RETURN TipodeDado especifica o tipo de dado do valor de retorno da função Exemplo 9 function desejase criar uma função que retorne o total gasto em salário pago aos funcionários de um determinado departamento O nome do de partamento será o argumento a ser passado para esta function 17 AULA 7 Banco de Dados Comentários sobre o Exemplo 9 na linha 2 foi definido o argumento desta function nomeDepto na linha 3 é especificado que a função retorna um dado do tipo INTEGER na linha 5 é definida a variável local total que receberá a soma dos salários pagos em um determinado departamento da linha 7 até 10 é feito o SELECT para armazenar na variável local total o valor total pago aos funcionários do departamento cujo nome será passado como argumento na linha 12 é retornado o valor da function Verificação da function do Exemplo 9 Para exibir o resultado de uma function podese utilizar o comando SELECT especificando o nome da function que está sendo pesquisada na tabela DUAL que é uma tabela do sistema juntamente com o valor do argumento A exclusão de uma function é feita com o seguinte comando Atenção Não deixe de assistir à videoaula Procedure e Function com a professora Elisângela Botelho Gracias 18 AULA 7 Banco de Dados TRIGGER Triggers são blocos PLSQL disparados automática e implicitamente sempre que ocorre um evento associado a uma tabela INSERT UPDATE ou DELETE Ele é disparado quando os comandos INSERT UPDATE ou DELETE são executados em uma tabela Os triggers podem ser utilizados para implementação de regras de negócios geração de valores calculados replicação de dados manutenção de registro histórico de alterações ocorridas no banco de dados atualização de outras tabelas em função de inclusão ou alteração da tabela atual etc Importante Um trigger está associado a uma tabela e oferece um mecanismo pode roso para tratar as alterações em um banco de dados mas eles devem ser usados com muito cuidado pois o efeito de vários triggers pode ser muito complexo e a execução de um trigger pode ativar outro trigger e este ativa o trigger anterior novamente gerando uma recursão Um trigger é disparado BEFORE antes ou AFTER depois de um evento E os eventos podem ser INSERT UPDATE ou DELETE 19 AULA 7 Banco de Dados Sintaxe do trigger tudo que estiver entre colchetes é opcional OR REPLACE recria um trigger já existente sem ter de eliminálo BEFORE faz o Oracle disparar o trigger antes de executar o comando de disparo AFTER faz o Oracle disparar o trigger após executar o comando de disparo DELETE faz o Oracle disparar o trigger sempre que um comando DELETE eliminar uma linha da tabela INSERT faz o Oracle disparar o trigger sempre que um comando INSERT adicionar uma linha à tabela UPDATE faz o Oracle disparar o trigger sempre que um comando UPDATE alterar um valor em uma das colunas especificadas na cláusula OF Se não for especificado nenhum atributo sempre que ocorrer qualquer atualização o trigger será disparado ON especifica o nome da tabela REFERENCING especifica nomes de correlação FOR EACH ROW designa um trigger como um trigger de linha O Oracle dispara um trigger de linha para cada linha que for afetada por um comando de disparo WHEN utilizada para restringir as linhas que dispararão o trigger ou seja uma condição que precisa ser atendida para o Oracle disparar o trigger BLOCO PLSQL é o bloco PLSQL que o Oracle executa 20 AULA 7 Banco de Dados Quando um trigger é disparado é possível acessar os valores dos atributos da tabela que disparou o trigger dentro do trigger ou seja é possível acessar o valor de um atributo de uma linha podendose obter tanto o valor antigo old quanto o novo valor new para o comando INSERT os valores dos atributos podem ser acessados da seguinte maneira newnomedoatributo ou seja se o dado está sendo inserido ele é novo para o comando DELETE os valores dos atributos podem ser acessados da seguinte maneira old nomedoatributo ou seja se o dado está sendo eli minado ele é velho para o comando UPDATE poderão ser acessados os valores antes da atuali zação e após a atualização Os valores antigos antes da atualização podem ser acessados da seguinte maneira old nomedoatributo Os valores novos após a atualização por sua vez podem ser acessados da seguinte maneira newnomedoatributo Dentro dos triggers são disponibilizados predicados para testar o evento que dispara o trigger e realizar uma ação de acordo com cada um deles Esses predi cados são inserting retorna true se o trigger foi disparado por um comando INSERT updating retorna true se o trigger foi disparado por um comando UPDATE deleting retorna true se o trigger foi disparado por um comando DELETE Exemplo 10 trigger desejase criar um trigger que seja disparado quando um novo funcionário for inserido ou quando o salário for atualizado na tabela Funcionario ou seja se um novo funcionário for inserido esse trigger deverá inserir uma nova linha na tabela Admissao com o código do funcionário nome do funcionário e o dia em que ele foi inserido no banco de dados se o salário do funcionário for atualizado esse trigger deverá inserir uma nova linha na tabela HistoricoSalario com o código do funcionário o valor do salário antes da atualização e o novo salário após a atualização 21 AULA 7 Banco de Dados Considere a criação destas duas tabelas para a implementação deste trigger Implementação do trigger Comentários sobre o Exemplo 10 da linha 2 até 4 você pode observar que o trigger será disparado antes de ocorrer um INSERT ou UPDATE do atributo Salario na tabela Funcionario na linha 5 temse o FOR EACH ROW pois o trigger é de linha ou seja ele será disparado para cada linha inserida ou atualizada na tabela Funcionario na linha 7 é utilizado o predicado inserting para saber se o trigger foi dispara do pelo INSERT Se esta condição for verdadeira da linha 8 até 10 será inserida uma nova linha na tabela Admissao observe que os valores são novos uma vez que o evento que disparou o trigger é INSERT 22 AULA 7 Banco de Dados na linha 11 é utilizado o predicado updating para saber se o trigger foi dis parado pelo UPDATE Se esta condição for verdadeira da linha 12 até 14 será inserida uma nova linha na tabela HistoricoSalario Observe que os valores de salário podem ser velhos antes da atualização ou novos após a atualização o comando IF deve ser finalizado por END IF linha 15 Verificação do trigger do Exemplo 10 para verificar se o trigger está funcionando basta executar um comando que dispare o trigger se você inserir um novo funcionário na tabela Funcionario como mostra do a seguir o trigger será disparado e ele inserirá uma nova linha na tabela Admissao conforme foi implementado Observe que a Amanda foi inserida pelo trigger na tabela Admissao se você atualizar o salário dos funcionários do departamento de código 2 como mostrado a seguir o trigger será disparado e ele inserirá uma nova linha na tabela HistoricoSalario conforme foi implementado Observe que a Amanda código 106 adicionada anteriormente na tabela Funcionario já recebeu aumento pois é do departamento 2 23 AULA 7 Banco de Dados Exemplo 11 trigger desejase criar um trigger que seja disparado quando uma linha for eliminada da tabela FuncProj Quando isso ocorrer o trigger deverá inse rir uma nova linha na tabela HistoricoProj com o código do funcionário nome do funcionário código do projeto e o dia em que esta linha foi eliminada Considere a criação desta tabela para a implementação deste trigger Implementação do trigger Comentários sobre o Exemplo 11 da linha 2 até 4 você pode observar que o trigger será disparado antes de ocorrer um DELETE na tabela FuncProj na linha 5 temse o FOR EACH ROW pois o trigger é de linha ou seja ele será disparado para cada linha eliminada da tabela FuncProj nas linhas 6 e 7 é utilizado o DECLARE para declarar a variável local nomeF da linha 9 até 11 é feito um SELECT na tabela Funcionario para descobrir o nome do funcionário cujo valor do código conseguimos com oldCodFunc da tabela FuncProj 24 AULA 7 Banco de Dados da linha 13 até 15 é inserida uma nova linha na tabela HistoricoProj Observe que os valores do código do funcionário e do projeto são velhos pois estão sendo eliminados e que o nome do funcionário é a variável nomeF Verificação do trigger do Exemplo 11 para verificar se o trigger está funcionando basta executar o comando que dispare o trigger se você eliminar uma linha da tabela FuncProj como mostrado a seguir o trigger será disparado e ele inserirá uma nova linha na tabela HistoricoProj conforme foi implementado Observe que o projeto 1001 do qual a Maria Castro participou foi inserido na tabela HistoricoProj pelo trigger Quando um trigger é criado ele fica automaticamente ativo Caso você precise desativar ou ativar a execução de um trigger devese utilizar o seguinte comando A exclusão de um trigger é feita com o comando Atenção Não deixe de assistir à videoaula Trigger com a professora Elisângela Botelho Gracias 25 AULA 7 Banco de Dados Script do Banco de Dados utilizado nesta Aula 7 CREATE TABLE Departamento CodDepto INTEGER NomeDepto VARCHAR20 NOT NULL PRIMARY KEYCodDepto CREATE TABLE Funcionario CodFunc INTEGER NomeFunc VARCHAR20 NOT NULL Salario INTEGER NOT NULL CodDepto INTEGER NOT NULL PRIMARY KEYCodFunc FOREIGN KEY CodDepto REFERENCES Departamento CodDepto CREATE TABLE Projeto CodProj INTEGER NomeProj VARCHAR20 NOT NULL Duracao INTEGER NOT NULL 26 AULA 7 Banco de Dados PRIMARY KEYCodProj CREATE TABLE FuncProj CodFunc INTEGER CodProj INTEGER HorasTrab INTEGER PRIMARY KEYCodFunc CodProj FOREIGN KEY CodFunc REFERENCES FuncionarioCodFunc FOREIGN KEY CodProj REFERENCES ProjetoCodProj INSERT INTO Departamento CodDepto NomeDepto VALUES 1 Marketing INSERT INTO Departamento CodDepto NomeDepto VALUES 2 Vendas INSERT INTO Departamento CodDepto NomeDepto VALUES 3 Dados INSERT INTO Departamento CodDepto NomeDepto VALUES 4 Pesquisa INSERT INTO Funcionario CodFunc NomeFunc Salario CodDepto VALUES 101 Joao da Silva Santos 2000 2 INSERT INTO Funcionario CodFunc NomeFunc Salario CodDepto VALUES 102 Mario Souza 1500 1 INSERT INTO Funcionario CodFunc NomeFunc Salario CodDepto VALUES 103 Sergio Silva Santos 2400 2 27 AULA 7 Banco de Dados INSERT INTO Funcionario CodFunc NomeFunc Salario CodDepto VALUES 104 Maria Castro 1200 1 INSERT INTO Funcionario CodFunc NomeFunc Salario CodDepto VALUES 105 Marcio Silva Santana 1400 4 INSERT INTO Projeto CodProj NomeProj Duracao VALUES 1001 SistemaA 2 INSERT INTO Projeto CodProj NomeProj Duracao VALUES 1002 SistemaB 6 INSERT INTO Projeto CodProj NomeProj Duracao VALUES 1003 SistemaX 4 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 101 1001 24 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 101 1002 160 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 102 1001 56 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 102 1003 45 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 103 1001 86 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 103 1003 64 INSERT INTO FuncProj CodFunc CodProj HorasTrab VALUES 104 1001 46 28 AULA 7 Banco de Dados REFERÊNCIAS FANDERUFF D Dominando o Oracle 9i modelagem e desenvolvimento São Paulo Pearson Education do Brasil 2003 PUGA S FRANÇA E GOYA M Banco de dados implementação em SQL PLSQL e Oracle 11g São Paulo Pearson 2013 RECUPERAÇÃO E CONCORRÊNCIA EM BANCO DE DADOS INTRODUÇÃO Recuperação e Concorrência em banco de dados estão relacionadas com a questão geral da proteção dos dados ou seja a proteção dos dados contra perdas ou danos Alguns problemas tratados pela recuperação e concorrência em banco de da dos o sistema pode cair sofrer uma pane em meio à execução de algum progra ma deixando assim o banco de dados em um estado incorreto dois programas que estão sendo executados ao mesmo tempo modo concorrente podem interferir um com o outro e assim produzir resultados incorretos no banco de dados Nesta aula você aprenderá sobre estes dois tópicos Recuperação de Banco de Dados Concorrência em Banco de Dados RECUPERAÇÃO DE BANCO DE DADOS Você sabe o que significa recuperar um banco de dados Significa restaurar o banco de dados a um estado que se sabe ser correto consistente depois que alguma falha o leva a um estado inconsistente Alguns exemplos de falhas são falha do sistema erros de hardware ou software erro de programa overflow divisão por zero problemas físicos incêndio 4 AULA 8 Banco de Dados Transação Para você entender recuperação e concorrência em um banco de dados primei ro deve saber o que é uma transação Transação ou unidade lógica de trabalho é uma sequência de várias operações sobre um banco de dados cujo objetivo é transformar o banco de dados de um estado consistente para outro estado consistente mesmo que nos passos inter mediários o sistema permaneça temporariamente inconsistente A Figura 1 ilustra esse conceito Figura 1 Conceito de transação Operações sobre o Banco de Dados consistente inserção atualização etc Passos intermediários sobre o Banco de Dados banco pode estar inconsistente Banco de Dados consistente após todas as atualizações realizadas Fonte Elaborada pela autora Exemplo 1 transação considere a tabela a seguir que contém o código a quantidade de cada pedido e a QtdadeTotal soma de todos os valores do atributo Qtdade 5 AULA 8 Banco de Dados foi realizada a operação de inserção de um novo pedido como você pode observar a seguir Mas a coluna QtdadeTotal não foi atualizada então neste momento o banco de dados está em um estado inconsistente após a inserção foi realizada a atualização da coluna QtdadeTotal como você pode observar a seguir Agora o banco de dados voltou ao seu estado consistente esta transação envolveu duas operações inserção e atualização Em uma transação pode ocorrer que uma das operações da transação não seja executada Se isso acontecer o banco de dados fica em estado inconsistente Considerando o Exemplo 1 poderia ocorrer uma queda do sistema entre as opera ções de inserção e atualização Mas o banco de dados tem um gerenciador de transações que garante que se a transação executar algumas atualizações e ocorrer uma falha por qualquer motivo antes da transação atingir seu término planejado então essas atualizações serão desfeitas perdidas Dessa forma com o gerenciamento de transações uma transação ou será exe cutada integralmente ou será totalmente cancelada 6 AULA 8 Banco de Dados O gerenciador de transações utiliza as operações COMMIT e ROLLBACK para manter um banco de dados consistente COMMIT indica o término de uma transação bemsucedida ou seja informa ao gerenciador de transações que uma transação foi concluída com sucesso que o banco de dados está novamente em um estado consistente e que todas as atualizações feitas pela transação podem agora ser validadas ou tornadas permanentes no banco de dados ROLLBACK indica o término de uma transação malsucedida ou seja infor ma ao gerenciador de transações que algo saiu errado durante a execução da transação que o banco de dados pode estar em estado inconsistente e que todas as atualizações feitas pela transação até agora devem ser desfeitas No Exemplo 1 será emitida uma instrução COMMIT se as duas operações in serção e atualização forem realizadas com sucesso Se algo sair errado ou seja se uma das operações da transação resultar em uma condição de erro será emitida uma instrução ROLLBACK Outro ponto que você deve entender sobre transação são as suas propriedades ACID A tomicidade C onsistência I solamento D urabilidade Atomicidade as transações são atômicas tudo ou nada ou seja uma transação ou será executada integralmente ou será totalmente cancelada Consistência as transações preservam a consistência do banco de dados ou seja uma transação transforma um estado consistente do banco de dados em outro estado consistente sem necessariamente preservar a consistência em todos os pontos intermediários da execução da transação Isolamento as transações são isoladas umas das outras ou seja embora haja muitas transações sendo executadas de modo concorrente as atualizações de qualquer transação são ocultas de todas as outras até o commit dessa transação Exemplo considere duas transações distintas T1 e T2 T1 pode ver as atualizações 7 AULA 8 Banco de Dados de T2 após T2 fazer o commit ou T2 pode ver as atualizações de T1 após T1 fazer o commit mas certamente não ambas Durabilidade se a transação for concluída com sucesso suas atualizações não são perdidas mesmo que haja uma falha no sistema Atenção Não deixe de assistir à videoaula O que é uma transação da professora Elisângela Botelho Gracias com uma explicação simples e breve sobre transação e suas propriedades ACID RECUPERAÇÃO DE TRANSAÇÕES Você pode estar pensando em como é possível desfazer uma operação de uma transação Isso é feito por meio de um log de sistema presente em disco no qual são registrados detalhes de todas as operações de atualização Sendo assim para desfazer alguma atualização o sistema usará a entrada de log correspondente para restaurar o objeto atualizado a seu valor anterior Você já sabe que um ponto de COMMIT é estabelecido quando todas as atu alizações feitas pela transação em execução desde o ponto de COMMIT anterior são validadas Então antes do ponto de COMMIT todas as atualizações devem ser consideradas apenas como tentativas no sentido de que podem ser desfeitas sub sequentemente Pela propriedade de durabilidade se uma transação for concluída com sucesso então o sistema garantirá que suas atualizações serão instaladas permanentemen te no banco de dados mesmo que o sistema caia no momento seguinte O sistema no entanto pode cair depois da instrução COMMIT ser aceita mas an tes das atualizações terem sido gravadas fisicamente no banco de dados pois elas ainda podem estar esperando em um buffer de memória principal e dessa forma serem perdidas no instante da queda Mesmo que isso aconteça o procedimento de reinicialização do sistema ainda instalará essas atualizações no banco de dados pois ele é capaz de descobrir os valores que devem ser gravados por meio do log 8 AULA 8 Banco de Dados Então como o sistema pode consultar o log em caso de queda o log deve ser fisicamente gravado antes de se completar o processamento de COMMIT Assim o procedimento de reinicialização recuperará qualquer transação concluída com su cesso que não tenha conseguido fazer com que suas atualizações fossem gravadas fisicamente antes de uma queda RECUPERAÇÃO DO SISTEMA O ponto crítico no que se refere a falhas do sistema é o fato de que o conteúdo da memória principal é perdido em particular os buffers do banco de dados se perdem Então o estado exato de qualquer transação em andamento no momen to da falha deixa de ser conhecido e desse modo tal transação não poderá nunca mais ser concluída com sucesso e deverá ser desfeita Na falha de sistema talvez seja necessário também refazer no momento de reinicialização certas transações concluídas com êxito antes da queda do sistema mas que não conseguiram ter suas atualizações transferidas dos buffers do banco de dados para o banco de dados físico Como porém o sistema saberá quais transações devem ser desfeitas e quais devem ser refeitas Por meio de checkpoints ou seja em certos intervalos pre determinados o sistema automaticamente marca um checkpoint Marcar um checkpoint envolve gravar fisicamente gravação forçada o conteúdo dos buffers do banco de dados no banco de dados físico gravar fisicamente um registro de checkpoint especial no log físico O registro de checkpoint fornece uma lista de todas as transações que estavam em andamento quando o checkpoint foi marcado A Figura 2 mostra o cenário de um banco de dados com cinco transações T1 T2 T3 T4 e T5 sendo que ocorreu uma falha do sistema no instante tf foi marcado um checkpoint tc antes do instante tf 9 AULA 8 Banco de Dados a transação T1 foi concluída com sucesso antes do tempo tc a transação T2 foi iniciada antes do instante tc e concluída com sucesso após o instante tc e antes de tf a transação T3 foi iniciada antes do instante tc mas não foi concluída até o instante tf a transação T4 começou após o instante tc e foi concluída com sucesso antes do instante tf finalmente a transação T5 também foi iniciada após o instante tc mas não foi concluída até o instante tf Figura 2 Cenário de um banco de dados com cinco transações Fonte DATE 2004 p 387 Na Figura 2 após a falha tf o sistema será reinicializado Você sabe o que acon tecerá com cada uma das cinco transações As transações T3 e T5 devem ser desfeitas pois elas estavam em andamento no instante que ocorreu a falha As transações T2 e T4 devem ser refeitas pois elas foram finalizadas antes da falha do sistema mas como não teve outro checkpoint elas não foram gravadas fisicamente no banco de dados 10 AULA 8 Banco de Dados A transação T1 não entra no processo de reinicialização porque suas atuali zações foram gravadas fisicamente no banco de dados no instante tc como parte do processo do checkpoint Atenção Não deixe de assistir à videoaula Recuperação de um banco de dados com a professora Elisângela Botelho Gracias CONCORRÊNCIA EM BANCO DE DADOS A concorrência se refere ao fato de que o banco de dados permite que várias transações tenham acesso a um mesmo banco de dados e ao mesmo tempo Dessa forma é necessário ter algum tipo de mecanismo de controle de concorrência para assegurar que transações concorrentes não interfiram umas com as outras Três problemas podem resultar do acesso simultâneo a um banco de dados Atualização perdida Dependência sem COMMIT Análise inconsistente Esses três problemas de concorrência serão explicados a seguir Atualização Perdida Neste problema de concorrência a atualização de um usuário sobrescreve a atualização de outro usuário como está ilustrado na Figura 3 11 AULA 8 Banco de Dados Figura 3 Problema da Atualização Perdida Fonte DATE 2004 p 398 Entendendo o problema da Figura 3 a primeira coluna é a transação A a segunda é o tempo e a terceira é a transação B instante t1 transação A lê o valor de t considere t 200 instante t2 transação B também lê o valor de t t200 instante t3 transação A acrescenta 50 ao valor de t t 20050 250 instante t4 transação B também acrescenta 100 ao valor de t mas acrescenta de acordo com o que leu no instante t2 t 200100 300 Conclusão sobre a atualização perdida se você observar a Figura 3 a atualização da transação A é perdida no instante t4 porque a transação B a sobrescreve sem sequer examinála Dependência sem COMMIT Este problema de concorrência ocorre quando uma transação tiver permissão para ler ou atualizar um valor que foi atualizado por outra transação mas que ainda não foi validada por essa outra transação 12 AULA 8 Banco de Dados Portanto se o COMMIT ainda não ocorreu existe a possibilidade de que não somente o COMMIT não se realize como também um ROLLBACK ocorra em seu lugar e então a primeira transação terá visto alguns dados que não existirão mais Esse problema está ilustrado na Figura 4 Figura 4 Problema de Dependência sem COMMIT Fonte DATE 2004 p 399 Entendendo o problema da Figura 4 a primeira coluna é a transação A a segunda é o tempo e a terceira é a transação B instante t1 transação B acrescenta 100 ao valor de t Considere t100 então t100100200 instante t2 transação A lê o valor de t200 com a atualização sem COMMIT no instante t1 Ou seja lê um dado que não foi validado ainda instante t3 transação B dá um ROLLBACK ou seja desfaz a atualização do instante t1 Conclusões sobre a dependência sem COMMIT você pode observar na Figura 4 que a transação A leu o valor de t200 mas essa atualização foi desfeita pela transação B ou seja a transação A está operando sobre uma suposição falsa a suposição de que o valor de t é o valor visto no instante t2 enquanto de fato o valor era o do instante t1 como resultado a transação A pode produzir uma saída incorreta 13 AULA 8 Banco de Dados Análise Inconsistente Este problema de concorrência acontece quando uma transação que está cal culando uma função de sumarização lê alguns valores antes de outra transação alterálos mas também lê alguns valores depois que outra transação já os alterou A Figura 5 ilustra este problema Figura 5 Problema da Análise Inconsistente Fonte DATE 2004 p 400 Entendendo o problema da Figura 5 as transações A e B operam sobre três contas bancárias a transação A está totalizando o saldo das três contas conta1 conta2 e conta3 a transação B está transferindo uma quantia10 da conta3 para a conta1 o resultado produzido pela transação A 110 está incorreto pois a transação A viu um estado inconsistente do banco de dados e portanto efetuou uma análise inconsistente 14 AULA 8 Banco de Dados Conclusão sobre a análise inconsistente você pode observar na Figura 5 que a transação A leu o valor da conta1 antes da atualização feita pela transação B O valor total seria igual a 120 505020 Atenção Não deixe de assistir à videoaula Problemas de Concorrência em Banco de Dados da professora Elisângela Botelho Gracias com uma explicação breve sobre os três problemas de concorrência Bloqueio Os três problemas de concorrência discutidos podem ser resolvidos por meio de uma técnica de controle de concorrência chamada bloqueio A ideia do bloqueio é simples quando uma transação necessita de uma garantia de que um objeto no qual está interessada não mudará de modo algum enquanto ela estiver ativa a transação adquire um bloqueio sobre esse objeto o efeito do bloqueio é impedir que outras transações atuem sobre o objeto em questão ou seja não permitir às outras transações alterarem este objeto Explicação detalhada de como funciona o bloqueio 1 Suponha que o sistema admita dois tipos de bloqueio bloqueios exclusivos bloqueiosX e bloqueios compartilhados bloqueiosC BloqueiosX são blo queios de gravação e bloqueiosC são bloqueios de leitura 2 Se a transação A mantiver um bloqueio exclusivo X sobre um objeto t então uma solicitação feita por uma transação distinta B de um bloqueio de qual quer tipo sobre t será negada 3 Se a transação A mantiver um bloqueio compartilhado C sobre o um objeto t então 15 AULA 8 Banco de Dados uma solicitação de uma transação distinta B de um bloqueioX sobre t será negada uma solicitação de alguma transação distinta B de um bloqueioC sobre t será concedida ou seja agora B também manterá um bloqueio C sobre t O banco de dados faz uso dos bloqueios X e C a fim de garantir que problemas de concorrência atualização perdida dependência sem COMMIT e análise incon sistente não ocorram O protocolo de bloqueio funciona da seguinte maneira 1 Uma transação que deseja ler o valor de um objeto deve adquirir um blo queioC leitura sobre esse objeto 2 Uma transação que deseja atualizar o valor de um objeto deve adquirir um bloqueioX gravação sobre esse objeto 3 Se uma solicitação de bloqueio da transação B for negada devido a um con flito com um bloqueio já mantido pela transação A então B entrará em um estado de espera B esperará até que o bloqueio de A seja liberado 4 Os bloqueios X e C são mantidos até o fim da transação COMMIT ou ROLLBACK A seguir você verá como o bloqueio resolve os três problemas de concorrência estudados Problema da atualização perdida Problema da dependência sem COMMIT Problema da análise inconsistente 16 AULA 8 Banco de Dados Problema da Atualização Perdida com bloqueio Figura 6 Problema da Atualização Perdida com bloqueio Fonte DATE 2004 p 403 Explicação da Figura 6 que mostra a execução intercalada sob o protocolo de bloqueio já descrito no instante t1 a transação A quer ler o valor de t então ela solicita e adquire o bloqueioC leitura sobre t no instante t2 a transação B também quer ler o valor de t então ela solicita e adquire o bloqueioC leitura sobre t isso porque o bloqueioC pode ser com partilhado por mais de uma transação no instante t3 a transação A quer acrescentar 100 ao valor de t Como é gra vação ela solicita o bloqueioX mas é negado pois é uma solicitação de um bloqueioX sobre t e essa solicitação entra em conflito com o bloqueioC já mantido pela transação B Assim a transação A entra em estado de espera pelas mesmas razões a transação B entra em estado de espera no instante t4 agora as duas transações são incapazes de prosseguir e portanto não há nenhuma dúvida de que alguma atualização possa ser perdida neste caso o bloqueio resolve o problema da atualização perdida reduzin doo a outro problema deadlock será visto mais adiante nesta aula 17 AULA 8 Banco de Dados Problema de Dependência sem COMMIT com bloqueio Figura 7 Problema de Dependência sem COMMIT com bloqueio Fonte DATE 2004 p 404 Explicação da Figura 7 que mostra a execução intercalada sob o protocolo de bloqueio já descrito no instante t1 a transação B quer acrescentar 100 ao valor de t Como é uma gravação ela solicita e adquire o bloqueioX pois t não está com nenhum bloqueio no instante t2 a transação A quer ler o valor de t então ela solicita o bloqueioC leitura mas não é aceito pois é uma solicitação de um bloqueio sobre t e tal pedido entra em conflito com o bloqueioX já mantido por B Assim a transação A entra em estado de espera a transação A permanece esperando até que B atinja seu término com COMMIT ou ROLLBACK quando então o bloqueio da transação B é liberado e a transação A pode continuar no instante t3 a transação B é finalizada com um COMMIT ou ROLLBACK E quando uma transação é finalizada todos os bloqueios que ela adquiriu são liberados após a finalização da transação B a transação A adquire o bloqueioC sobre t e lê o valor correto de t 18 AULA 8 Banco de Dados Problema da Análise Inconsistente com bloqueio Figura 8 Problema da Análise Inconsistente com bloqueio Fonte DATE 2004 p 404 Explicação da Figura 8 que mostra a execução intercalada sob o protocolo de bloqueio já descrito a atualização da conta1 solicitada pela transação B no instante t6 não é aceita pois é uma solicitação de um bloqueioX sobre a conta1 e tal solicitação entra em conflito com o bloqueioC já mantido pela transação A sobre a conta1 no instante t1 Assim a transação B entra em estado de espera a leitura da conta3 solicitada pela transação A no instante t7 também não é aceita pois é uma solicitação de um bloqueioC sobre conta3 e essa solicita ção entra em conflito com o bloqueioX já mantido pela transação B sobre a conta3 no instante t4 Assim a transação A também entra em estado de espera o bloqueio resolve o problema da análise inconsistente forçando um dea dlock será explicado a seguir 19 AULA 8 Banco de Dados Atenção Não deixe de assistir à videoaula Funcionamento do Bloqueio da pro fessora Elisângela Botelho Gracias com uma explicação sobre o protocolo de bloqueio aplicado aos três problemas de concorrência Deadlock Bloqueios podem ser usados para resolver os problemas de concorrência mas ele pode introduzir seus próprios problemas principalmente o deadlock O que é então deadlock É uma situação na qual duas ou mais transações estão em estado de espera simultânea cada uma esperando que uma das outras libere um bloqueio antes de poder prosseguir Se ocorrer um deadlock é desejável que o sistema o detecteo e interrompao Detectar um deadlock envolve detectar um ciclo no Grafo de Espera grafo de quem está esperando por quem E para interromper um deadlock será necessário esco lher uma das transações participantes como vítima e fazer o ROLLBACK liberando assim seus bloqueios e permitindo o prosseguimento de alguma outra transação Na prática nem todos os sistemas detectam de fato os deadlocks pois isso pode ser custoso computacionalmente A maioria utiliza um mecanismo de tem po de espera e assume simplesmente que uma transação que não tenha realizado qualquer trabalho durante um período de tempo prescrito está em deadlock 20 AULA 8 Banco de Dados REFERÊNCIAS DATE C J Introdução a sistemas de banco de dados Rio de Janeiro 8 ed Rio de aula 4 NORMALIZAÇÃO INTRODUÇÃO Você sabe o que é normalização Para que o modelo de seu banco de dados esteja bem modelado ou seja não tenha redundância existem algumas diretrizes que você deve utilizar A normaliza ção possui regras para eliminar redundâncias O objetivo da normalização é reagrupar informações para eliminar re dundâncias A normalização é utilizada tanto no processo de modelagem de um banco de dados quanto no processo de engenharia reversa O termo engenharia reversa vem do fato de usarse como ponto de partida do processo um produto implementado para obter sua especificação modelo con ceitual Nos exemplos a seguir será utilizada a engenharia reversa para explicar e exemplificar as seguintes formas normais 1FN 1ª forma normal 2FN 2ª forma normal e 3FN 3ª forma normal Mas o que é uma forma normal Uma forma normal é uma regra que deve ser obedecida por uma tabela para que esta seja bem projetada Existem diversas formas normais e cada uma delas elimina um tipo de redun dância Um banco de dados normalizado até a 3FN é o mais utilizado na prática Exemplo considere o conjunto de dados da Figura 1 com muita redundância no qual temos as seguintes informações para cada projeto são informados o código a descrição e o tipo do projeto bem como os empregados que atuam no projeto 4 AULA 4 Banco de Dados para cada empregado são informados seu código nome categoria funcional salário de acordo com sua categoria funcional data em que o empregado foi alocado ao projeto e o tempo em meses pelo qual o empregado foi alocado ao projeto Atenção Observe na Figura 1 que temos dados duplicados referentes a um mes mo projeto uma vez que vários empregados podem trabalhar no mesmo projeto e também a um mesmo empregador pois um empregado pode trabalhar em mais de um projeto Figura 1 Conjunto de dados representado na forma de uma tabela não normalizada Adaptada de Heuser 2009 Uma representação da extensão do modelo relacional um pouco modificada para representar tabelas aninhadas seria Com o modelo relacional correspondente ao conjunto de dados passase para o processo de normalização 5 AULA 4 Banco de Dados PRIMEIRA FORMA NORMAL 1FN Uma tabela encontrase na 1FN quando NÃO contém tabelas aninhadas Ou seja o domínio de um atributo deve incluir apenas valores atômicos simples e indivisíveis e o valor de qualquer atributo em uma tupla linha deve ser um único valor Se você observar temos uma tabela aninhada ou seja uma tabela dentro de uma outra como mostrado a seguir Portanto a passagem para a 1FN consta da eliminação das tabelas aninhadas E para transformar uma tabela não normalizada em um esquema que obedeça a regra da 1FN criase uma tabela referente à própria tabela e uma tabela para cada tabela aninhada Exemplo da 1FN no caso da Figura 1 cujo conjunto de dados contém muita redundância as relações resultantes da 1FN seriam Proj Codproj Tipo Descrição ProjEmp Codproj Codemp Nome Categoria Salário Dataini Tempo A decomposição das tabelas para a 1FN é feita nos seguintes passos é criada uma tabela na 1FN referente à tabela não normalizada e que contém apenas os atributos com valores atômicos isto é sem as tabelas aninhadas A chave primária da tabela na 1FN é idêntica à chave da tabela não normalizada para cada tabela aninhada é criada uma tabela na 1FN composta pelos se guintes atributos a chave primária de cada uma das tabelas na qual a tabela em questão está aninhada os atributos da própria tabela aninhada 6 AULA 4 Banco de Dados a chave primária da tabela aninhada será a chave primária dela mesma caso seja suficiente e caso contrário devese determinar quais os demais atributos necessários para identificar as linhas da tabela na 1FN compon do assim a chave primária na 1FN O conteúdo das tabelas na 1FN considerando os dados que tínhamos inicial mente na Figura 1 ficaria da seguinte forma Figura 2 Figura 2 Tabelas com dados referentes à 1FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 2 a tabela Proj na 1FN eliminou um tipo de redundância ou seja temos uma linha para cada projeto diferente a tabela ProjEmp foi criada e sua chave primária é formada pelos atributos Codproj e Codemp pois um empregado pode trabalhar em mais de um projeto se um empregado pudesse trabalhar em um único projeto a chave primária seria composta apenas pelo Codemp na tabela ProjEmp ainda temos redundância pois os dados dos emprega dos estão duplicados no caso deles participaram de mais de um projeto 7 AULA 4 Banco de Dados DEPENDÊNCIA FUNCIONAL Para você compreender a 2FN e a 3FN é necessário conhecer o conceito de dependência funcional ou seja em uma tabela relacional dizse que um atributo C2 depende funcional mente de um atributo C1 ou viceversa quando em todas as linhas da tabela para cada valor de C1 que aparece na tabela aparece o mesmo valor de C2 O conceito fica mais fácil com o exemplo destes dados a seguir Podemos dizer que o atributo Salário depende funcionalmente de um atri buto Código pelo fato de cada valor de Código estar associado sempre ao mesmo valor de Salário ou seja sempre que aparece o valor E1 do atributo Código o valor do atributo Salário é 10 sempre que aparece o valor E2 do atributo Código o valor do atributo Salário é 5 sempre que aparece o valor E3 do atributo Código o valor do atributo Salário é 9 De forma geral o determinante de uma dependência funcional pode ser um conjunto de atributos e não somente um atributo 8 AULA 4 Banco de Dados SEGUNDA FORMA NORMAL 2FN Uma tabela encontrase na 2FN quando além de encontrarse na 1FN cada atributo que não faz parte da chave pri mária depende da chave primária completa Uma tabela que não se encontra na 2FN contém dependências funcionais par ciais ou seja contém atributos não chave que dependem apenas de uma parte da chave primária Obviamente uma tabela que está na 1FN e cuja chave primária é formada por um único atributo não contém dependências parciais Isso porque nesta tabela é impossível um atributo depender de uma parte da chave primária visto que a chave primária é composta por um único atributo atômico e indivisível Importante Toda tabela que está na 1FN e que possui apenas um atributo como chave primária já está na 2FN O mesmo conceito aplicase para uma tabela que contenha apenas atributos que fazem parte da chave primária Para transformarmos um banco de dados para a 2FN pegamos as relações na 1FN e aplicamos a regra da 2FN Tabelas na 1FN que já vimos Proj Codproj Tipo Descrição ProjEmp Codproj Codemp Nome Categoria Salário Dataini Tempo O processo de passagem da 1FN para a 2FN é o seguinte copiar para a 2FN cada tabela que tenha chave primária simples ou que não tenha atributos além da chave primária No caso do nosso exemplo é o que acontece com a tabela Proj 9 AULA 4 Banco de Dados para cada tabela com chave primária composta e com pelo menos uma coluna não chave no nosso exemplo a tabela ProjEmp fazer a seguinte per gunta para cada atributo não chave o atributo depende de toda a chave ou de apenas parte dela 1 Caso o atributo dependa de toda a chave criar o atributo correspon dente na tabela com a chave completa na 2FN os atributos Dataini e Tempo da tabela ProjEmp na 2FN Ou seja os atributos Dataini e Tempo dependem da chave primária completa pois para determinar a data em que um empregado começou a trabalhar em um projeto bem como para determinar o tempo pelo qual ele foi alocado ao projeto é necessário conhecer tanto o código do projeto quanto o código do empregado 2 caso o atributo dependa apenas de parte da chave deve ser criada caso ainda não exista uma tabela na 2FN que tenha como chave pri mária a parte da chave que é determinante do atributo em questão a tabela Emp na 2FN Ou seja os atributos Nome Categoria e Salário de pendem cada um apenas do atributo Codemp já que esses atributos são determinados tão somente pelo código do empregado os atributos Nome Salário e Categoria da tabela Emp na 2FN 10 AULA 4 Banco de Dados Assim o modelo relacional correspondente à 2FN é o seguinte Proj Codproj Tipo Descrição Emp Codemp Nome Categoria Salário ProjEmp Codproj Codemp Dataini Tempo O conteúdo das tabelas na 2FN considerando os dados que tínhamos na 1FN Figura 2 ficaria da seguinte forma Figura 3 Tabelas com dados referentes à 2FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 3 a tabela Proj não foi alterada a tabela ProjEmp agora não tem mais redundância a tabela Emp tem ainda um outro tipo de redundância já que o atributo Salario depende da Categoria do empregado ou seja todos da categoria A1 tem o salário 4 B1 é 9 e A2 é 4 11 AULA 4 Banco de Dados TERCEIRA FORMA NORMAL 3FN Uma tabela encontrase na 3FN quando além de estar na 2FN todo atributo não chave depende diretamente da chave primária isto é quando não há dependências funcionais transitivas ou indiretas Uma dependência funcional transitiva ou indireta ocorre quando um atribu to além de depender da chave primária completa depende de outro atributo ou combinação de atributos não chaves Para transformarmos um banco de dados para a 3FN pegamos as relações na 2FN e aplicamos a regra da 3FN Tabelas na 2FN que já vimos Proj Codproj Tipo Descrição Emp Codemp Nome Categoria Salário ProjEmp Codproj Codemp Dataini Tempo O processo de passagem da 2FN para a 3FN é o seguinte copiar para a 3FN cada tabela que tenha menos que dois atributos não chave pois nesse caso não há como ter dependências transitivas para tabelas com dois ou mais atributos não chave criar uma tabela na 3FN com a chave primária da tabela em questão e para cada atributo não chave fazer a seguinte pergunta o atributo depende de algum outro atributo não chave 12 AULA 4 Banco de Dados 1 Caso o atributo dependa apenas da chave copie o atributo para a tabela na 3FN 2 caso o atributo dependa de outro atributo criar caso ainda não exis ta uma tabela na 3FN que tenha como chave primária o atributo do qual há a dependência transitiva copiar o atributo dependente para a tabela criada e o atributo determinante deve permanecer também na tabela original Assim o modelo relacional correspondente à 3FN é o seguinte Proj Codproj Tipo Descrição Categoria Categoria Salário Emp Codemp Nome Categoria ProjEmp Codproj Codemp Dataini Tempo O conteúdo das tabelas na 3FN considerando os dados que tínhamos na 2FN Figura 3 ficaria da seguinte forma Figura 4 Tabelas com dados referentes à 3FN Adaptada de Heuser 2009 O que é importante você observar nos dados da Figura 4 as tabelas Proj e ProjEmp não foram alteradas 13 AULA 4 Banco de Dados a tabela Emp não tem redundância pois sabendose a categoria que o em pregado pertence é possível obter seu salário por meio da tabela Categoria que foi criada na 3FN Atenção Não deixe de assistir à videoaula Normalização 1FN 2FN e 3FN com a professora Elisângela Botelho Gracias com uma explicação sobre a 1FN 2FN e 3FN Na Figura 5 temos um resumo da 1FN 2FN e 3FN Figura 5 Resumo das formas normais 1FN 2FN e 3FN Fonte Elmasri Navathe 2018 14 AULA 4 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018 ÁLGEBRA RELACIONAL A álgebra relacional é um conjunto básico de operações para o modelo rela cional Essas operações permitem que um usuário especifique as solicitações de recuperação básicas como expressões da álgebra relacional Ela é muito impor tante pois oferece um alicerce formal para as operações do modelo relacional e também é usada como base para a implementação e otimização de consultas A álgebra relacional é fechada ou seja o resultado de uma ou mais operações relacionais é uma nova relação O resultado de uma operação pode ser utilizado como dado de entrada de outra Você entenderá isso melhor nos exemplos que utilizaremos a seguir As operações da álgebra relacional podem ser divididas em dois grupos um grupo que consiste em operações desenvolvidas especificamente para banco de dados relacionais as quais incluem Seleção Projeção e Junção dentre outras outro grupo são as operações da teoria de conjuntos da matemática União Interseção Diferença e Produto Cartesiano Exemplo utilizaremos o seguinte banco de dados já visto na Aula 3 para expli car e exemplificar algumas operações da álgebra relacional Modelo Relacional Departamento CodDepto NomeDepto Funcionario CodFunc NomeFunc Salario CodDepto Coddepto é chave estrangeira que referencia o atributo Coddepto da tabela Departamento 4 AULA 4 Banco de Dados Projeto CodProj NomeProj Duracao FuncProj CodFunc CodProj HorasTrab CodFunc é chave estrangeira que referencia o atributo CodFunc da tabela Funcionario CodProj é chave estrangeira que referencia o atributo CodProj da tabela Projeto A Figura 1 mostra as tabelas Departamento Funcionario Projeto e FuncProj exemplificadas com dados Atenção Utilizaremos essas tabelas com dados para mostrar o resultado gerado pelas operações da Álgebra Relacional Figura 1 Tabelas Departamento Funcionario Projeto e FuncProj exemplificadas com dados Fonte Elaborada pela autora OPERAÇÃO SELEÇÃO A operação Seleção é utilizada para selecionar um subconjunto de tuplas linhas em uma relação que satisfaça uma condição de seleção Essa operação também pode ser considerada como uma partição horizontal da relação 5 AULA 4 Banco de Dados A operação Seleção é denotada por σ condição da seleção Nome da Relação Exemplo 1 selecione todos os dados dos funcionários que ganham mais que 1600 σ Salario 1600 Funcionario Nesse exemplo 1 serão retornados todos os atributos da relação Funcionario mas somente as linhas que obedecem à condição do salário ser maior que 1600 como você pode observar a seguir A relação resultante da operação Seleção tem os mesmos atributos da rela ção especificada A operação Seleção é comutativa ou seja σ cond1 σ cond2 R σ cond2 σ cond1 R OPERAÇÃO PROJEÇÃO A operação Projeção seleciona certas colunas da tabela e descarta as ou tras Essa operação também pode ser considerada como uma partição vertical da relação A forma geral da operação Projeção é π lista de atributos Nome da Relação 6 AULA 4 Banco de Dados Exemplo 2 selecione somente o código e o nome de todos os funcionários π CodFunc NomeFunc Funcionario Nesse exemplo 2 serão retornados somente os atributos CodFunc e Nome Func de todos os funcionários da relação Funcionario como você pode observar no resultado a seguir A relação resultante da operação Projeção tem somente os atributos especi ficados em lista de atributos na mesma ordem que aparecem na lista A operação Projeção remove implicitamente qualquer tupla duplicada então o número de tuplas em uma relação resultante da operação Projeção é sempre menor ou igual ao número de tuplas da relação original Se a lista dos atributos inclui uma chave da relação a relação resultante tem o mesmo número de tuplas que o original A comutatividade não é mantida na operação Projeção SEQUÊNCIA DE OPERAÇÕES As operações podem ser aplicadas de forma aninhada usando uma única expressão da álgebra relacional Exemplificaremos a seguir as operações de proje ção e seleção em uma única expressão da álgebra relacional Exemplo 3 selecione o código e o nome dos funcionários mas somente para aqueles funcionários que ganham mais de 1600 π CodFunc NomeFunc σ Salario1600 Funcionario Observe que nesse exemplo 3 as duas operações Seleção e Projeção foram utilizadas ou seja primeiro selecionase as linhas de Funcionario de acordo com a condição e depois fazse a projeção das colunas CodFunc e NomeFunc 7 AULA 4 Banco de Dados O mesmo exemplo 3 pode ser feito por meio da criação de relações resultantes intermediárias dando um nome para cada relação intermediária ou seja Temp1 σ Salario1600 Funcionario Result π CodFunc NomeFunc Temp1 O resultado da relação gerada Temp1 você já viu no exemplo 1 e o da relação Result você pode observar a seguir OPERAÇÃO PRODUTO CARTESIANO O produto cartesiano denotado por X combina tuplas de duas relações R e S resultando em uma relação que tem uma tupla para cada combinação de tuplas uma de R e outra de S QA1 A2 AnB1 B2 Bm RA1 A2 An X SB1 B2 Bm Exemplo 4 considere as relações Funcionario e Departamento da Figura 1 para fazermos o produto cartesiano e observe o resultado a seguir 8 AULA 4 Banco de Dados Considerações sobre o resultado do produto cartesiano de Funcionario com Departamento ele combina cada linha de funcionário com cada linha de departamento ele não respeita o relacionamento entre Funcionario e Departamento pois sua operação combina cada linha tupla de Funcionario com cada linha de Departamento gerando um total de 20 linhas 5 funcionários X 4 departa mentos como não foi realizada outra operação seleção ou projeção além do produto cartesiano o resultado trouxe todas as colunas e todas as linhas OPERAÇÃO JUNÇÃO A operação Junção denotada por X é utilizada para combinar tuplas de duas relações por meio de um ou mais atributos comuns que se relacionam às duas relações Atenção Esta operação é muito importante para qualquer banco de dados relacio nal pois permite processar relacionamentos entre relações A forma geral da operação Junção com duas relações RA 1 A 2 A n e SB 1 B 2 B m é R X condição de junção S Exemplo 5 considere as relações Funcionario e Departamento Figura 1 para fazermos a junção entre elas que é escrita da seguinte maneira Funcionario X CodDepto CodDepto Departamento O resultado da Junção desse exemplo 5 está ilustrado a seguir 9 AULA 4 Banco de Dados Considerações sobre o resultado da junção de Funcionario com Departamento a junção respeita o relacionamento entre Funcionario e Departamento pois sua operação combina cada linha tupla de Funcionario com a única linha do Departamento a que ele realmente pertence não foram utilizadas as operações Seleção e Projeção então o resultado ante rior retornou todos os atributos das duas tabelas e todas as linhas combinadas pela junção Exemplo 6 selecione o nome de cada funcionário e o nome do departamento a que cada um pertence Temp1 Funcionario X CodDepto CodDepto Departamento Result π NomeFunc NomeDepto Temp1 Nesse exemplo 6 foram utilizadas as operações Junção e Projeção O resultado da relação gerada Temp1 você já viu no exemplo 5 e o da relação Result você pode observar a seguir Exemplo 7 selecione o nome de cada funcionário e o nome do departamento de cada um mas somente para os funcionários que ganham mais de 1600 Temp1 Funcionario X CodDepto CodDepto Departamento Temp2 σ Salario1600 Temp1 Result π NomeFunc NomeDepto Temp2 Nesse exemplo 7 foram utilizadas as operações Junção Seleção e Projeção Observe o resultado Result a seguir 10 AULA 4 Banco de Dados Outra forma de fazer o exemplo 7 é realizar primeiro a Seleção Observe que não perdemos nenhuma informação retornando o mesmo resultado anterior Temp1 σ Salario1600 Funcionario Temp2 Temp1 X CodDepto CodDepto Departamento Result π NomeFunc NomeDepto Temp2 Exemplo 8 selecione o nome dos projetos em que a funcionária Maria Castro trabalhou Quais tabelas você acha que devemos utilizar nesse exemplo 8 considerando o modelo relacional do banco de dados da Figura 1 Temp1 FuncProj X CodProj CodProj Projeto Temp2 Funcionario X CodFunc CodFunc Temp1 Temp3 σ NomeFunc Maria Castro Temp2 Result π NomeProj Temp3 Outra forma de fazer o exemplo 8 é realizar primeiro a Seleção Observe que não perdemos nenhuma informação retornando o mesmo resultado anterior Temp1 σ NomeFunc Maria Castro Funcionario Temp2 FuncProj X CodFunc CodFunc Temp1 Temp3 Projeto X CodProj CodProj Temp2 Result π NomeProj Temp3 OPERAÇÕES DA TEORIA DE CONJUNTOS Para realizarmos as operações União Interseção e Diferença na álgebra rela cional as duas relações envolvidas devem possuir o mesmo tipo de tuplas ou seja elas devem possuir uma condição chamada compatibilidade de união Consideremos as relações R e S da Figura 2 para explicar e exemplificar as ope rações União Interseção e Diferença 11 AULA 4 Banco de Dados Figura 2 Relações R e S que são compatíveis para união A operação União denotada por R S tem como resultado uma relação que inclui todas as tuplas que estão em R ou em S ou em ambas as relações Tuplas duplicadas são eliminadas Observe o resultado da união de R S a seguir A operação Interseção denotada por R S tem como resultado uma relação que inclui todas as tuplas que estão nas duas relações R e S conforme você pode observar no resultado a seguir A operação Diferença denotada por R S tem como resultado uma relação que inclui todas as tuplas que estão em R mas não estão em S conforme você pode observar no resultado a seguir 12 AULA 4 Banco de Dados As operações União e Interseção são comutativas enquanto a Diferença não é comutativa Atenção Não deixe de assistir à videoaula Operações da Álgebra Relacional Seleção Projeção e Junção com a professora Elisângela Botelho Gracias na qual há uma explicação sobre algumas operações da Álgebra Relacional 13 AULA 4 Banco de Dados REFERÊNCIAS ELMASRI R NAVATHE S Sistemas de banco de dados 7 ed São Paulo Pearson 2018