Banco de Dados by Lucas Amiune

Universidade Veiga de Almeida Instituto de Ciência e Tecnologia Modelagem e Projeto de Banco de Dados e Sistemas de Gerenciamento de Banco de Dados

Carlos Augusto B. Ribeiro Marcos Vinícius Ramos Eduardo Corrêa Lima

Considerações Este conteúdo trabalho foi inicialmente elaborado pelo Coordenador e Professor Carlos Augusto B. Ribeiro. O professor Marcos Vinícius Ramos, DBA e especialista em ORACLE e SQL SERVER enriqueceu-a ainda mais e eu, inseri exercícios de modelagem conceitual, formas normais, engenharia reversa e diversos exemplos da linguagem SQL bem como modifiquei a forma de apresentação. Queremos aproveitar a oportunidade e dividir com vocês duas frases, que possivelmente permearão vários momentos das nossas vidas. "Nada mais difícil de manejar, mais perigoso de conduzir ou de mais incerto sucesso, do que liderar a introdução de uma nova ordem de coisas. Pois, o inovador tem contra si todos os que se beneficiarão com a nova ordem". NICOLAU MAQUIAVEL 1459 – 1527 “É muito melhor arriscar coisas grandiosas, alcançar triunfos e glórias, mesmo expondo-se à derrota, do que formar fila com os pobres de espírito que nem gozam muito nem sofrem muito, porque vivem nesta penumbra cinzenta que não conhece nem vitória nem derrota”. THEODORE ROOSEVELT

Esperamos que o material didático, que atualizado, seja útil para o seu aprendizado.

constantemente

mantemos

Muito obrigado,

Eduardo Corrêa Lima eduardocorrealima@hotmail.com

Carlos Augusto B. Ribeiro carlos@uerj.br

Apresentação Este curso tem como objetivo, oferecer uma noção geral sobre a construção de sistemas de banco de dados. Para isto, é necessário estudar modelos para a construção de projetos lógicos de bancos de dados, modelos para a construção de projetos físicos de banco de dados, técnicas de controle de dependência de dados e métodos de consultas. Para construção dos modelos lógicos, será estudado o modelo Entidade Relacionamento, utilizando a abordagem proposta em ELMAS89 que oferece uma notação rica em recursos, permitindo a modelagem de entidades normais, fracas, atributos simples, compostos, multivalorados, derivados e a modelagens de objetos mais complexos como classes e subclasses (modelo Entidade Relacionamento Estendido). Para construção dos modelos físicos, será estudado o modelo Relacional como originalmente proposto por CODD. Para eliminar dependência de dados, utilizaremos a normalização, abordando a Primeira, Segunda e Terceira Formas Normais, propostas originalmente por COOD bem como a BCNF (Boyce-Cood Normal Form). Para a elaboração de consultas, será estudado a Álgebra Relacional, que nada mais é do que uma forma canônica para as linguagens de consulta e a linguagem de consultas SQL, padrão mundial nos gerenciadores de Bancos de Dados mais utilizados no mercado.

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

Capítulo 1 Introdução e Conceitos Gerais

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Introdução e Conceitos Gerais A tecnologia aplicada aos métodos de armazenamento de informações vem crescendo e gerando um impacto cada vez maior no uso de computadores, em qualquer área em que os mesmos podem ser aplicados. Um banco de dados pode ser definido como um conjunto de dados devidamente relacionados. Por dados podemos compreender como fatos conhecidos que podem ser armazenados e que possuem um significado implícito. Porém, o significado do termo “banco de dados” é mais restrito que simplesmente a definição dada acima. Um banco de dados possui as seguintes propriedades: -

um banco de dados é uma coleção lógica coerente de dados com um significado inerente; uma disposição desordenada dos dados não pode ser referenciada como um banco de dados;

um banco de dados é projetado, construído e povoado com dados para um propósito específico; um banco de dados possui um conjunto pré definido de usuários e aplicações;

um banco de dados representa algum aspecto do mundo real, o qual é chamado de mini mundo; qualquer alteração efetuada no mini mundo é automaticamente refletida no banco de dados.

Um banco de dados pode ser criado e mantido por um conjunto de aplicações desenvolvidas especialmente para esta tarefa ou por um Sistema Gerenciador de Banco de Dados (SGBD). Um SGBD permite aos usuários criarem e manipularem bancos de dados de propósito geral. O conjunto formado por um banco de dados mais as aplicações que manipulam o mesmo é chamado de Sistema de Banco de Dados. Banco de Dados X Processamento Tradicional de Arquivos Auto Informação Uma característica importante da abordagem Banco de Dados é que o SGBD mantém não somente os dados, mas também a forma como os mesmos são armazenados, contendo uma descrição completa do banco de dados. Estas informações são armazenadas no catálogo do SGBD, o qual contém informações, como a estrutura de cada arquivo, o tipo e o formato de armazenamento de cada tipo de dado, restrições, etc. A informação armazenada no catálogo é chamada de Meta Dados. No processamento tradicional de arquivos, o programa que irá manipular os dados deve conter este tipo de informação, ficando limitado a manipular as informações que o mesmo conhece. Utilizando a abordagem banco de dados, a aplicação pode manipular diversas bases de dados diferentes.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Separação entre Programas e Dados No processamento tradicional de arquivos, a estrutura dos dados está incorporada ao programa de acesso. Desta forma, qualquer alteração na estrutura de arquivos implica na alteração no código fonte de todos os programas. Já na abordagem banco de dados, a estrutura é alterada apenas no catálogo, não alterando os programas.

Programas de Aplicação/Consulta

Software para processar a manipulação

Software para acesso aos dados

Dados

Meta-Dados Sistema de Bancos de Dados

Figura 1 Abstração de Dados O SGBD deve fornecer ao usuário uma representação conceitual dos dados, sem fornecer muitos detalhes de como as informações são armazenadas. Um modelo de dados é uma abstração de dados que é utilizada para fornecer esta representação conceitual utilizando conceitos lógicos como objetos, suas propriedades e seus relacionamentos. A estrutura detalhada e a organização de cada arquivo são descritas no catálogo. Múltiplas Visões de Dados Como um conjunto de informações pode ser utilizado por um conjunto diferenciado de usuários, é importante que estes usuários possam ter visões diferentes da base de dados. Uma visão é definida como um subconjunto de uma base de dados, formando deste modo, um conjunto virtual de informações. Usuários Para um grande banco de dados, existe um grande número de pessoas envolvidas: os encarregados com o projeto, quem o utiliza e os responsáveis pela manutenção.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Administrador de Banco de Dados (DBA) Em um ambiente de banco de dados, o SGBD é o recurso primário e os SOFTWARES de apoio são os recursos secundários. A administração destes recursos cabe ao Administrador de Banco de Dados, o qual é responsável pela autorização de acesso ao banco de dados e pela coordenação e monitoração de seu uso. Administrador de Dados (DA) O Administrador de Dados (DA ou AD) é responsável pela identificação dos dados que devem ser armazenados no banco de dados, escolhendo a estrutura correta para representar e armazenar dados. Muitas vezes, os administradores de dados atuam como STAFF do DBA, assumindo outras responsabilidades após a construção do banco de dados. É função do administrador de dados também avaliar as necessidades de cada grupo de usuários para definir as visões que serão necessárias, integrando-as, fazendo com que o banco de dados seja capaz de atender a todas as necessidades dos usuários. Usuários Finais Existem basicamente três categorias de usuários finais que são os usuários finais do banco de dados, fazendo consultas, atualizações e gerando documentos: -

Usuários casuais: acessam o banco de dados casualmente, mas que podem necessitar de diferentes informações a cada acesso; utilizam sofisticadas linguagens de consulta para especificar suas necessidades;

Usuários novatos ou paramétricos: utilizam porções pré definidas do banco de dados, utilizando consultas preestabelecidas que já foram exaustivamente testadas;

Usuários sofisticados: são usuários que estão familiarizados com o SGBD e realizam consultas complexas.

Analistas de Sistemas e Programadores de Aplicações Os analistas determinam os requisitos dos usuários finais e desenvolvem especificações para transações que atendam estes requisitos, e os programadores implementam estas especificações como programas, testando, depurando, documentando e dando manutenção no mesmo. É importante que, tanto analistas quanto programadores, estejam a par dos recursos oferecidos pelo SGBD. Vantagens e desvantagens do uso de um SGBD

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Controle de Redundância No processamento tradicional de arquivos, cada grupo de usuários deve manter seu próprio conjunto de arquivos e dados. Desta forma, acabam ocorrendo redundâncias que prejudicam o sistema com problemas como: Toda vez que for necessário atualizar um arquivo de um grupo, então todos os grupos devem ser atualizados para manter a integridade dos dados no ambiente como um todo; A redundância desnecessária de dados leva ao armazenamento excessivo de informações, ocupando espaço que poderia estar sendo utilizado com outras informações. Compartilhamento de Dados Um SGBD multiusuário deve permitir que múltiplos usuários acessem o banco de dados ao mesmo tempo. Este fator é essencial para que múltiplas aplicações integradas possam acessar o banco. O SGBD multiusuário deve manter o controle de concorrência para assegurar que os resultados de atualizações sejam corretos. Um banco de dados multiusuário deve fornecer recursos para a construção de múltiplas visões. Restrição a Acesso não Autorizado Um SGBD deve fornecer um subsistema de autorização e segurança, o qual é utilizado pelo DBA para criar contas de usuários e especificar as restrições destas contas; o controle de restrições se aplica tanto ao acesso aos dados (segurança lógica) quanto ao uso de software inerentes ao SGBD e acesso físico à instalação do servidor e/ou servidores de bancos de dados (segurança física). Representação de relacionamentos complexos entre dados Um banco de dados pode incluir uma variedade de dados que estão interrelacionados de várias formas. Um SGBD deve fornecer recursos para se representar uma grande variedade de relacionamentos entre os dados, bem como, recuperar e atualizar os dados de maneira prática e eficiente. Tolerância a falhas Um SGBD deve fornecer recursos para recuperação de falhas tanto de software quanto de hardware. Quando não utilizar um SGBD Em algumas situações, o uso de um SGBD pode representar uma carga desnecessária aos custos quando comparado à abordagem processamento tradicional de arquivos, por exemplo:

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Alto investimento inicial na compra de software e hardware adicionais;

Generalidade que um SGBD fornece na definição e processamento de dados;

Sobrecarga na provisão de controle de segurança, controle de concorrência, recuperação e integração de funções.

Problemas adicionais podem surgir caso os administradores de dados ou os administradores de banco de dados não elaborem os projetos corretamente ou se as aplicações não são implementadas de forma apropriada. Se o DBA não administrar o banco de dados de forma correta, tanto a segurança quanto a integridade dos sistemas podem ser comprometidas. A sobrecarga causada pelo uso de um SGBD e a má administração justificam a utilização da abordagem processamento tradicional de arquivos em casos como: -

O banco de dados e as aplicações são simples, bem definidas e não se esperam mudanças no projeto;

A necessidade de processamento em tempo real de certas aplicações, que são terrivelmente prejudicadas pela sobrecarga causada pelo uso de um SGBD;

Não haverá múltiplo acesso ao banco de dados.

Conceitos e Arquiteturas de um SGBD Modelos de Dados Definição: conjunto de conceitos que descrevem a estrutura lógica e física de um banco de dados. Uma das principais características da abordagem banco de dados, é que a mesma fornece alguns níveis de abstração de dados omitindo ao usuário final, detalhes de como estes dados são armazenados. Um modelo de dados é um conjunto de conceitos que podem ser utilizados para descrever a estrutura lógica e física de um banco de dados. Por estrutura podemos compreender o tipo dos dados, os relacionamentos e as restrições que podem recair sobre os dados. Os modelos são classificados basicamente em três tipos: -

Modelo de dados conceitual (alto nível), que fornece uma visão mais próxima do modo como os usuários visualizam os dados realmente (Entidades e relacionamentos);

Modelo de dados lógico (nível intermediário), que fornece uma visão lógica da derivação das estruturas conceituais (Tabelas e chaves);

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Modelo de dados físico (baixo nível), que fornece uma visão mais detalhada do modo como os dados estão realmente armazenados no computador (Tabelas, índices, chaves, arquivos físicos, memória, etc.).

Requisitos do modelo conceitual (ERA) - Entrevistas, questionários, observações diretas - Regras de Negócio - Requisitos de Informação

Modelo Descritivo

Expressividade: grau de detalhamento Legibilidade: todos devem entender Flexibilidade: permitir mudanças Minimalidade: evitar redundâncias Correção: utilizar a simbologia correta Completeza: atender às Regras de Negócio e aos Requisitos de Informação

Modelo Conceitual

Transformação do ERA em tabelas

Criação das tabelas

Modelo Lógico Modelo Físico

Esquemas e Instâncias Em qualquer modelo de dados utilizado, é importante distinguir a descrição do banco de dados do próprio SGBD. A descrição de um banco de dados é chamada de esquema de um banco de dados e é especificada durante o projeto do banco de dados. Geralmente, poucas mudanças ocorrem no esquema do banco de dados. Os dados armazenados em um banco de dados em um determinado instante do tempo formam um conjunto chamado de instância do banco de dados. A instância altera toda vez que uma alteração no banco de dados é feita. O SGBD é responsável por garantir que toda instância do banco de dados satisfaça ao esquema do banco de dados, respeitando sua estrutura e suas restrições. O esquema de um banco de dados também pode ser chamado de intenção de um banco de dados e a instância de extensão de um banco de dados. A Arquitetura Três Esquemas A principal meta da arquitetura três esquemas é separar as aplicações do usuário do banco de dados físico. Os esquemas podem ser definidos como: -

Nível interno: ou esquema interno, o qual descreve a estrutura de armazenamento físico do banco de dados; utiliza um modelo de dados e

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

descreve detalhadamente os dados armazenados e os caminhos de acesso ao banco de dados; Nível conceitual: ou esquema conceitual, o qual descreve a estrutura do banco de dados como um todo; é uma descrição global do banco de dados, que não fornece detalhes do modo como os dados estão fisicamente armazenados; Nível externo: ou esquema de visão, o qual descreve as visões do banco de dados para um grupo de usuários; cada visão descreve quais porções do banco de dados um grupo de usuários terá acesso.

Usuários Finais

Visão externa

Nível conceitual

Nível interno

Visão externa

Esquema Conceitual

Esquema Interno

Mapeamento conceitual externo

Mapeamento conceitual interno

Banco de dados armazenado

Figura 3 Independência de Dados A independência de dados pode ser definida como a capacidade de se alterar um esquema em um nível em um banco de dados sem ter que alterar um nível superior. Existem dois tipos de independência de dados: -

Independência de dados lógica: é a capacidade de alterar o esquema conceitual sem ter que alterar o esquema externo ou as aplicações do usuário.

Independência de dados física: é a capacidade de alterar o esquema interno sem ter que alterar o esquema conceitual, o esquema externo ou as aplicações do usuário.

As Linguagens para uso em bancos de Dados Para a definição dos esquemas conceitual e interno utiliza-se uma linguagem chamada DDL - DATA DEFINITION LANGUAGE - Linguagem

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

de Definição de Dados. O SGBD possui um compilador DDL que permite a execução das declarações para identificar as descrições dos esquemas e para armazená-las no catálogo do SGBD. A DDL é utilizada nos SGBD, onde a separação entre os níveis interno e conceitual não é muito clara. Em um SGBD em que a separação entre os níveis conceitual e interno é bem clara, é utilizada uma outra linguagem, a SDL (STORAGE DEFINITION LANGUAGE Linguagem de Definição de Armazenamento) para a especificação do esquema interno. A especificação do esquema conceitual fica por conta da DDL. Em um SGBD que utiliza a arquitetura três esquemas, é necessária a utilização de mais uma linguagem para a definição de visões, a VDL (VISION DEFINITION LANGUAGE - Linguagem de Definição de Visões). Uma vez que o esquema esteja compilado e o banco de dados esteja povoado, utiliza-se uma linguagem para fazer a manipulação dos dados, a DML (DATA MANIPULATION LANGUAGE - Linguagem de Manipulação de Dados). Os Módulos Componentes de um SGBD Um SGBD é um sistema complexo, formado por um conjunto muito grande de módulos. A figura 4 mostra um esquema da estrutura de funcionamento de um SGBD. Usuários Simples

Programadores de Aplicação

Usuários ocasionais

DBA

Programas de Aplicação

Chamadas de Rotina

Consultas

Esquemas de bancos de dados

Pré-compilador da linguagem de manipulação de dados

Processador da Consultas

Compilador da linguagem de Definição de Dados

Código objeto dos programas de aplicação

Gerenciador de Bancos de Dados SGBD

Gerenciador de Arquivos

Arquivos de dados

Dicionário de dados

Memória de disco

Figura 4

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Classificação dos SGBD O principal critério para se classificar um SGBD é o modelo de dados no qual é baseado. Existe o modelo hierárquico, e de rede e a grande maioria dos SGBD contemporâneos que são baseados no modelo relacional, alguns em modelos conceituais e alguns em modelos orientados a objetos. Modelo Hierárquico O modelo de dados Hierárquico está baseado na estrutura de dados: REGISTROS e relacionamentos PAI-FILHO, que consiste em uma coleção de registros conectados a outro através de ligações que representam o relacionamento PAI-FILHO. Cardiologia

Divisão do Hospital

Pacientes

Médicos

10 José

11 Maria

3 Carlos

1 Fred

12 João

1 Fred

2 Júlio

Figura 5 Cada registro é composto por uma coleção de atributos mono valorados. O relacionamento PAI-FILHO é do tipo 1:N entre dois tipos de registro: o tipo do registro do lado 1 é chamado de PAI enquanto que o registro do lado N é chamado de FILHO. Este modelo apresenta problemas para representar relacionamentos M:N. No exemplo de banco de dados da figura 5, seria mais fácil obter as informações das visitas dos médicos efetuadas ao paciente 10 – José e mais complicado saber quais pacientes foram visitados pelo médico 1 – Fred. Modelo de Rede É baseado na estrutura de dados de REGISTROS e CONJUNTOS DE DADOS. Diferentemente do modelo Hierárquico, suporta tipos de dados complexos: vetores e grupos (atributos compostos). Veja o atributo ENDEREÇO no exemplo a seguir.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Também é capaz de representar relacionamentos M:N. Estes relacionamentos podem ser simulados no modelo hierárquico pela duplicação de registros. Suporta o tipo de manipulação (DML) que trata um registro por vez, com linguagens de propósito geral (COBOL) chamada de linguagem HOST. No exemplo da figura 6, estão representados, com o diagrama de BACHMAN, o registro do tipo PACIENTE (proprietário) e o registro do tipo MÉDICO (membro). Paciente CPF

Endereço

Nome

Rua

Cidade

CEP

Medicado por Médico Nome

Divisão

Figura 6 Outras classificações -

quanto aos Usuários: um SGBD pode ser mono-usuário, normalmente utilizado em computadores pessoais (também chamados DESKTOP) ou multiusuário, utilizado em estações de trabalho, minicomputadores e máquinas de grande porte;

quanto à Localização: um SGBD pode ser localizado (também chamado centralizado) ou distribuído; se ele for localizado, então todos os dados estarão em uma máquina (ou em um único disco) ou distribuído, onde os dados estarão distribuídos por diversas máquinas (ou diversos discos);

quanto ao Ambiente: homogêneo é o ambiente composto por um único SGBD e um ambiente heterogêneo é composto por diferentes SGBD.

Modelagem de Dados Conceitual de Alto Nível O Modelo Entidade e Relacionamento (MER) O modelo Entidade Relacionamento é um modelo de dados conceitual de alto nível, cujos conceitos foram projetados para estar o mais próximo possível da visão que o usuário tem dos dados, não se preocupando em representar como estes dados estarão realmente armazenados. O modelo ER é utilizado principalmente durante o processo de projeto de banco de dados. 15

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Entidades e Atributos O objeto básico tratado pelo modelo ER é a entidade, que pode ser definida como um objeto do mundo real, concreto ou abstrato e que possui existência independente. Cada entidade possui um conjunto particular de propriedades que a descreve chamado de atributos. Um atributo pode ser dividido em diversas partes com significado independente entre si, recebendo o nome de atributo composto. Um atributo que não pode ser subdividido é chamado de atributo simples ou atômico. O atributo que pode assumir apenas um determinado valor em uma determinada instância é denominado atributo simplesmente valorado, enquanto que um atributo que pode assumir diversos valores em uma mesma instância é denominado atributo multivalorado. Um atributo que é gerado a partir de outro atributo é chamado de atributo derivado. TIPOS ENTIDADE, Conjunto de Valores, Atributo Chave Um banco de dados costuma conter grupos de entidades que são similares, possuindo os mesmos atributos, porém, cada entidade com seus próprios valores para cada atributo. Este conjunto de entidades similar define um TIPO ENTIDADE. Cada TIPO ENTIDADE é identificado por seu nome e pelo conjunto de atributos que definem suas propriedades. A descrição do TIPO ENTIDADE é chamada de esquema do TIPO ENTIDADE, especificando o nome do TIPO ENTIDADE, o nome de cada um de seus atributos e qualquer restrição que incida sobre as entidades. Uma restrição muito importante em uma entidade de um determinado TIPO ENTIDADE é a chave. Um TIPO ENTIDADE possui um atributo cujos valores são distintos para cada entidade individual. Este atributo é chamado atributo chave ou atributo identificador e seus valores podem ser utilizados para identificar cada entidade de forma única. Muitas vezes, uma chave pode ser formada pela composição de dois ou mais atributos. Uma entidade pode também ter mais de um atributo chave. Cada atributo simples de um TIPO ENTIDADE está associado com um conjunto de valores denominado domínio, o qual especifica o conjunto de valores que podem ser designados para este determinado atributo para cada entidade. Chave Primária (matrícula) Atributo ou propriedade (nome)

Entidade

(0,n)

Atributo multi-valorado (telefone) Atributo composto (endereço)

Atributo derivado (dígito)

Figura 7 16

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Tipos e Instâncias de Relacionamento Além de conhecer detalhadamente os TIPOS ENTIDADE, é muito importante conhecer também os relacionamentos entre estes TIPOS ENTIDADE. Um TIPO RELACIONAMENTO entre entidades é um conjunto de associações entre TIPOS ENTIDADE. Informalmente falando, cada instância de relacionamento r1 em R é uma associação de entidades, onde a associação inclui exatamente uma entidade de cada TIPO ENTIDADE participante no TIPO RELACIONAMENTO. Isto significa que estes TIPOS ENTIDADE estão relacionadas de alguma forma no mini mundo. A figura 8 mostra um exemplo entre dois TIPOS ENTIDADE (Produto e Cliente) e o relacionamento entre eles (Venda). Repare que para cada relacionamento, participam apenas uma entidade de cada TIPO ENTIDADE, porém, uma entidade pode participar de mais do que um relacionamento. código

Produto

CPF (1,n)

Venda

(1,n)

Cliente

nome

descrição data da venda

Figura 8 Grau de um Relacionamento O grau de um TIPO RELACIONAMENTO significa o número de TIPOS ENTIDADE que participam do TIPO RELACIONAMENTO. No exemplo da figura 8, temos um relacionamento binário. O grau de um relacionamento é ilimitado, porém, a partir do grau 3 (ternário), a compreensão e a dificuldade de se desenvolver a relação corretamente se tornam extremamente complexas, conforme demonstrado nas figuras 9 e 10. Sempre que possível, modelar os relacionamentos ternários (ou superiores) em uma AGREGAÇÃO e relacioná-la com a terceira TIPO ENTIDADE, conforme demonstrado nas figuras 9 e 10.

Senha CPF

CRM (0,1)

Médico

nome

(1,n)

Consulta

data

(1,n)

Paciente nome

Figura 9

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Senha (1,1) Liberação (0,1) CPF

CRM

Médico

(1,n)

Consulta

(1,n)

data

nome

Paciente nome

Figura 10 Outras Características de um Relacionamento Relacionamentos como Atributos Algumas vezes é conveniente pensar em um relacionamento como um atributo. Considere o exemplo da figura anterior. Podemos pensar departamento como sendo um atributo da entidade empregado, ou empregado, como um atributo multivalorado da entidade departamento. Se uma entidade não possuir existência muito bem definida, talvez seja mais interessante que ela seja representada como um atributo, para a manter a coesão do modelo lógico. código

Depto

CPF (1,n)

(1,1)

Empregado

nome

descrição

Figura 11 Nomes de Papéis e Relacionamentos Recursivos Cada TIPO ENTIDADE que participa de um TIPO RELACIONAMENTO desempenha um papel particular no relacionamento. O nome do papel representa a atuação desempenhada pela TIPO ENTIDADE no relacionamento. No exemplo da figura abaixo, nós temos o papel supervisor ou supervisionado para o TIPO ENTIDADE FUNCIONÁRIO. Nomes de papéis não são necessariamente importantes quando todas as entidades participantes desempenham papéis diferentes. Algumas vezes, o papel torna-se essencial para distinguir o significado de cada participação. Isto é muito comum em relacionamentos recursivos.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Um relacionamento recursivo é um relacionamento entre entidades do mesmo TIPO ENTIDADE. Veja o exemplo das figuras 12, 13, 14 e 15 a seguir. O relacionamento recursivo também é conhecido como auto relacionamento. supervisor (1,n)

Funcionário

Chefia (1,1)

supervisionado

Figura 12

(0,1)

Pessoa

Casada (0,1)

Figura 13 É necessária (1,n)

Disciplina

Pré-Req (1,n) Necessita

Figura 14

É componente (0,1)

Peça

Compõe (0,n) É composta

Figura 15 Cobertura Resumidamente, cobertura é o escopo da generalização, ou seja, quais entidades especializadas, estão em um determinado modelo conceitual, definidas (cobertas) pela generalização. As especializações podem ser: -

Totais (T) ou Parciais (P): define se o modelo contempla totalmente ou parcialmente, os tipos de entidades envolvidas na generalização; 19

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Exclusivas (E) ou Sobrepostas (S): determina se os elementos participam exclusivamente de uma especialização ou se sobrepõem à outra.

Observe as figuras 16, 17, 18 e 19 a seguir, alguns exemplos de cobertura. Pessoa (T,E)

Física

Jurídica

Figura 16

Funcionários (P,E)

INFO

Figura 17 Profissional de Cinema (P,S)

Diretor

Ator

Figura 18

Cargo (T,S)

Empregado

Chefe

Figura 19 Restrições em Tipos Relacionamentos Geralmente, os tipos relacionamentos sofrem certas restrições que limitam as possíveis combinações das entidades participantes. Estas restrições são derivadas de restrições impostas pelo estado destas entidades no mini mundo.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

A este tipo de restrição, nós chamamos cardinalidade. A cardinalidade indica o número de relacionamentos dos quais uma entidade pode participar. A cardinalidade pode ser: 0:1, 1:1, 1:N, M:N. Matrícula

código

Disciplina

(1,n)

Cursa

(1,n)

Aluno nome

descrição

Figura 20 Outra restrição muito importante é a participação. A participação define a existência de uma entidade através do relacionamento, podendo ser parcial ou total. Veja o a figura 21 abaixo, onde a participação do empregado é parcial, pois nem todo empregado gerencia um departamento, porém a participação do departamento neste relacionamento é total, pois todo departamento precisa ser gerenciado por um empregado. Desta forma, todas as entidades do TIPO ENTIDADE DEPTO precisam participar do relacionamento, mas nem todas as entidade do TIPO ENTIDADE EMPREGADO precisam participar do relacionamento. Já no exemplo da figura 22 ambas as participações são totais pois todo Empregado precisa trabalhar em um Projeto e todo Projeto tem que ter Empregado trabalhando nele. Estas restrições são chamadas de restrições estruturais.

Empregado

(0,1)

Gerencia

(1,1)

Depto

Figura 21

Empregado

(1,n)

Trabalha

(1,n)

Projeto

Figura 22 Algumas vezes, torna-se necessário armazenar um atributo no TIPO RELACIONAMENTO. Veja o exemplo, da figura 21. Poderíamos desejar saber em que dia o Empregado passou a gerenciar o Departamento. Pode se tornar difícil estabelecer a qual TIPO ENTIDADE pertence atributo, pois o mesmo é definido apenas pela existência do relacionamento. Quando temos relacionamentos com cardinalidade 1:1, podemos colocar o atributo em uma das entidades, de preferência, na entidade com participação total. 21

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

No caso, o atributo poderia ir para o TIPO ENTIDADE departamento. Isto porque nem todo empregado participará do relacionamento. Caso as cardinalidades máximas sejam 1:N, então podemos migrar o atributo no TIPO ENTIDADE com cardinalidade N para o de cardinalidade 1. Porém, se a cardinalidade for N:M, então o atributo deverá mesmo ficar no TIPO RELACIONAMENTO. Veja o exemplo da figura 22. Caso queiramos armazenar quantas horas cada empregado trabalhou em cada projeto, então este deverá ser um atributo do relacionamento. Tipos Entidade Fraca Alguns TIPOS ENTIDADE podem não ter um atributo chave por si só. Isto implica que não poderemos distinguir algumas entidades por que as combinações dos valores de seus atributos podem ser idênticas. Estes TIPOS ENTIDADE são chamados entidades fracas. As entidades deste tipo precisam estar relacionadas com uma entidade pertencente ao TIPO ENTIDADE proprietária. Este relacionamento é chamado de relacionamento identificador. O TIPO ENTIDADE DEPENDENTE é uma entidade fraca pois não possui um método de identificar uma entidade única. O EMPREGADO não é uma entidade fraca, pois possui um atributo para identificação (atributo chave). O número do RG de um empregado identifica um único empregado. Porém, um dependente de 5 anos de idade não possui necessariamente um documento. Desta forma, esta entidade é um TIPO ENTIDADE fraca. Um TIPO ENTIDADE fraca possui uma chave parcial, que juntamente com a chave primária da entidade proprietária, forma uma chave primária composta. Neste exemplo: a chave primária do EMPREGADO é o RG. A chave parcial do DEPENDENTE é o seu nome, pois dois irmãos não podem ter o mesmo nome. Desta forma, a chave primária desta entidade fica sendo o RG do pai ou mãe mais o nome do dependente. Matrícula

Nome (1,1)

(0,1)

Titular

Dependente

Venda

Nascimento

Nome

Figura 23 Código

Banco

Descrição

Código (1,n)

Possui

(1,1)

Agência

Descrição

Código (1,n)

Possui

(1,1)

Conta Nome

Figura 24

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Todos os exemplos vistos anteriormente foram para relacionamentos binários, ou seja, entre dois TIPOS ENTIDADE diferentes ou recursivos. Porém, o modelo entidade relacionamento não se restringe apenas à relacionamentos binários. O número de entidades que participam de um TIPO RELACIONAMENTO é irrestrito e armazenam muito mais informações do que diversos relacionamentos binários. Diagrama Entidade Relacionamento O diagrama Entidade Relacionamento é composto por um conjunto de elementos gráficos que visa representar todos os objetos do modelo Entidade Relacionamento tais como entidades, atributos, atributos chaves, relacionamentos, restrições estruturais, etc. O diagrama ER fornece uma visão lógica do banco de dados, fornecendo um conceito mais generalizado de como estão estruturados os dados de um sistema. Observe abaixo, os elementos gráficos que compõem o diagrama ER.

Atributo chave (RG) Tipo Entidade Tipo Entidade Fraca

Tipo Relacionamento

Atributo simples (nome)

(0,1)

Atributo multivalorado (telefone)

Atributo composto (endereço)

Tipo Relacionamento Identificador

Atributo derivado (dígito verificador)

Figura 25

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

O Modelo Relacional O modelo relacional foi criado por CODD em 1970 (Edgar Frank Cood, recentemente falecido – 18 de abril de 2003, mais informações podem ser obtidas no site IBM) com a finalidade de representar os dados como uma coleção de relações, onde cada relação é representada por uma tabela, ou falando de uma forma mais direta, um arquivo. Porém, um arquivo é mais restrito que uma tabela. Toda tabela pode ser considerada um arquivo, porém, nem todo arquivo pode ser considerado uma tabela. Quando uma relação é pensada como uma tabela de valores, cada linha nesta tabela representa uma coleção de dados relacionados. Estes valores podem ser interpretados como fatos descrevendo uma instância de uma entidade ou de um relacionamento. O nome da tabela e das colunas desta tabela são utilizados para facilitar a interpretação dos valores armazenados em cada linha da tabela. Todos os valores em uma coluna são necessariamente do mesmo tipo. Na terminologia do modelo relacional, cada tabela é chamada de relação; uma linha de uma tabela é chamada de TUPLA; o nome de cada coluna é chamado de ATRIBUTO; o tipo de dado que descreve cada coluna é chamado de DOMÍNIO. Verifique estes conceitos na figura 26.

SGBD DATABASE 1

Linha ou TUPLA

Tabela 1

DATABASE 2

Tabela 2

CATÁLOGO

Coluna, Atributo ou Propriedade

Figura 26 Domínios, Tuplas, Atributos e Relações Um domínio “D” é um conjunto de valores atômicos, sendo que por atômico, podemos compreender que cada valor do domínio é indivisível. Durante a especificação do domínio é importante destacar o tipo, o tamanho e a faixa do atributo que está sendo especificado.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Por exemplo: COLUNA RG Nome Salário

TIPO Numérico Caracter Numérico

TAMANHO 10,0 30 5,2

FAIXA 03000000-25999999 a-z, A-Z 00100,00-12000,99

Um esquema de relação R, denotado por R(A1, A2,..., An), onde cada atributo Ai é o nome do papel desempenhado por um domínio D no esquema relação R, onde D é chamado domínio de Ai e é denotado por dom(Ai). O grau de uma relação R é o número de atributos presentes em seu esquema de relação. A instância r de um esquema relação denotado por r(R) é um conjunto de n-tuplas r = intt1, t2,..., tn onde os valores de intt1, t2,..., tn devem estar contidos no domínio D. O valor nulo também pode fazer parte do domínio de um atributo e representa um valor não conhecido para uma determinada tupla. Atributo Chave de uma Relação Uma relação pode ser definida como um conjunto de tuplas distintas. Isto implica que a combinação dos valores dos atributos em uma tupla não pode se repetir na mesma tabela. Existirá sempre um subconjunto de atributos em uma tabela que garantem que não haverá valores repetidos para as diversas tuplas da mesma, garantindo que t1intSC ≠ t2intSC. SC é chamada de super chave de um esquema de relação. Toda relação possui ao menos uma super chave - o conjunto de todos os seus atributos que identificam uma tupla única. Uma chave C de um esquema de relação R é uma super chave de R com a propriedade adicional que removendo qualquer atributo A de K, resta ainda um conjunto de atributos K’ que não é uma super chave de R. Uma chave é uma super chave da qual não se pode extrair atributos. Por exemplo, o conjunto: (RA, Nome, Endereço) é uma super chave para estudante, porém, não é uma chave, pois se tirarmos o campo Endereço continuaremos a ter uma super chave. Já o conjunto (Nome da Revista, Volume, No da Revista) é uma super chave e uma chave, pois qualquer um dos atributos que retirarmos, deixaremos de ter uma super chave, ou seja, (Nome da Revista, Volume) não identifica uma única tupla. Em outras palavras, uma super chave é uma chave composta formada por mais de um atributo. Quando uma relação possui mais que uma chave (não confundir com chave composta) - como, por exemplo, RG e CIC para empregados - cada uma destas chaves são chamadas de chaves candidatas. Uma destas chaves candidatas deve ser escolhida como chave primária.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Uma chave estrangeira de uma tabela R1 em R2 ou vice-versa, especifica um relacionamento entre as tabelas R1 e R2. Atributo Chave de uma Relação (resumo) CHAVE PRIMÁRIA CANDIDATAS

NOME Primary Key Candidate Key

SUPERCHAVE

Super Key

ESTRANGEIRA

Foreign Key

DEFINIÇÂO Atributo que identifica uma única tupla Entidade que possui mais de um atributo para identificá-la unicamente, porém apenas um deles deve ser definido como chave primária Conjunto de atributos que identificam uma única tupla; chaves primárias das entidades envolvidas no relacionamento.

Derivação do modelo conceitual para o lógico O mapeamento do Modelo Entidade Relacionamento (conceitual) para o Modelo Relacional (lógico) segue sete passos básicos: a) Para cada entidade no modelo ER, é criada uma tabela no modelo relacional que inclua todos os atributos simples desta entidade. Para cada atributo composto são inseridos apenas os componentes simples de cada um. Um dos atributos chaves da entidade deve ser escolhida como chave primária da tabela; Matrícula Nome

Empregados

Telefone CPF

Figura 27 TB=EMPREGADOS (Matrícula, Nome, Telefone, CPF) b) Para cada entidade fraca com entidade proprietária é criada uma tabela no modelo Relacional, incluindo todos os atributos desta entidade fraca. Para cada atributo composto são inseridos apenas os componentes simples de cada um. A chave primária desta relação será composta pela chave parcial da entidade fraca mais a chave da entidade proprietária; Matrícula Nome Telefone

Empregados

Número (0,n)

(1,1)

Dependentes

CPF Nome

Figura 28

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

TB=EMPREGADOS (Matrícula, Nome, Telefone, CPF) TB=DEPENDENTES(Matrícula, Número, Nome) FK=EMPREGADOS(Matrícula) Æ DEPENDENTES (Matrícula) c) Para cada relacionamento regular com cardinalidades máximas 1:1 entre entidades E1 e E2 que geraram as tabelas T1 e T2 respectivamente, devemos escolher a chave primária de uma das relações (T1, T2)e inseri-la como chave estrangeira na outra relação; se um dos lados do relacionamento tiver participação total e outro parcial, então é interessante que a chave do lado com participação parcial seja inserido como chave estrangeira no lado que tem participação total; Apólice

Seguro de Saúde

Matrícula (1,1)

(0,1)

Nome

Empregados

Telefone CPF

Data opção

Figura 29 TB=EMPREGADOS(Matrícula, Nome, Telefone, CPF) TB=SEGUROS-SAUDE(Apólice, Matrícula, Data opção) FK=EMPREGADOS(Matrícula) Æ SEGUROS-SAUDE (Matrícula) d) Para cada relacionamento regular com cardinalidades máximas 1:N entre entidades E1 e E2 respectivamente e que geraram as tabelas T1 e T2 respectivamente, deve-se inserir a chave primária de T1 como chave estrangeira em T2; Número

Nívei Salariai

Matrícula (1,n)

(1,1)

Nome

Empregad

Telefone CPF

Valor

Figura 30 TB=EMPREGADOS(Matrícula, Nome, Telefone, CPF, Número) TB=NIVEIS-SALARIAIS(Número, Valor) FK=NIVEIS-SALARIAIS(Número) Æ EMPREGADOS(Número) e) Para cada relacionamento regular com cardinalidades máximas N:N entre entidades E1 e E2, cria-se uma nova tabela T1, contendo todos os atributos do relacionamento mais o atributo chave de E1 e o atributo chave de E2; a chave primária de T1 será composta pelos atributos chave de E1 e E2;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

número

Matrícula (0,n)

Projetos

Alocação

Nome

(0,n)

Empregados CPF

descrição

Figura 31 TB=EMPREGADOS(Matrícula, Nome, Telefone, CPF) TB=PROJETOS(Número, Nome) TB=ALOCACAO(Número, Matrícula) FK=EMPREGADOS(Matrícula) Æ ALOCACAO(Matrícula) FK=PROJETOS(Número) Æ ALOCACAO(Número) f) Para cada atributo multivalorado A1, cria-se uma tabela T1, contendo o atributo multivalorado A1, mais o atributo chave C da tabela que representa a entidade ou relacionamento que contém A1; a chave primária de T1 será composta por A1 mais C; se A1 for composto, então a tabela T1 deverá conter todos os atributos de A1; Matrícula Nome

Empregados

telefone (0,n) CPF

Figura 32 TB=EMPREGADOS(Matrícula, Nome, CPF) TB=TELEFONES-EMPREGADO(Matrícula, Telefone) FK=TELEFONES-EMPREGADO(Matrícula) Æ EMPREGADOS (Matrícula) g) Para cada relacionamento n-ário, n > 2, cria-se uma tabela T1, contendo todos os atributos do relacionamento; a chave primária de T1 será composta pelos atributos chaves das entidades participantes do relacionamento; Placa

Caminhão

CPF (0,n)

Direções

(0,n)

Motorista

(0,n) Nome

Numero

Trajetos

Nome

Rodovia Sentido

Figura 33 TB=MOTORISTA(CPF, Nome) TB=CAMINHÃO(Placa, Nome) 28

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

TB=TRAJETOS(Numero, Rodovia, Sentido) TB=DIRECOES(CPF, Placa, Número) FK=DIRECOES(CPF) Æ MOTORISTAS(CPF) FK=DIRECOES(Placa) Æ CAMINHÕES(Placa) FK=DIRECOES(Número) Æ TRAJETOS(Número) Derivação do modelo conceitual para o modelo lógico (RESUMO) -

para cada entidade é criada uma tabela; para cada atributo composto será inserido cada componente individualmente; para cada entidade fraca é criada uma tabela cuja chave primária é composta do seu atributo identificador concatenado com o atributo identificador da entidade proprietária; para relacionamentos com participação parcial, a chave da entidade com participação parcial será chave estrangeira da participação total; para relacionamentos com cardinalidade (1,1 : 1,N) a chave primária de (1,N) será chave estrangeira de (1,1) para relacionamentos com cardinalidade (1,N : 1,N) será criada uma nova tabela, cuja chave primária será a composição dos atributos identificadores das entidades envolvidas e seus próprios atributos, caso existam; para cada atributo multivalorado, cria-se uma nova tabela cuja chave primária será composta pela chave primária da entidade mais o atributo; para cada relacionamento de grau superior a 2, cria-se uma nova tabela contendo todos os atributos deste relacionamento mais as chaves primárias das entidades participantes deste relacionamento; 1o Passo - Entidades Normais

2o Passo - Entidades Fracas

3o Passo - Relacionamentos 1:1

1 E

5o Passo - Relacionamentos N:N

4o Passo - Relacionamentos 1:N N

N E

ou 6o Passo - Atrbs. Multi Valorados E

E E

c E 7o Passo - Relacionamento n-ário E

Figura 34

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

ExercĂcios Aula inicial

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Aula Inicial Objetos da vida real e seus relacionamentos I)

Imagine quatro “planilhas” que correspondam a figura abaixo e defina algumas de suas informações (características, propriedades ou atributos). Departamento DRH

Funcionário

Material Projeto

Dependente

Figura 35 II)

Abaixo, insira informações hipotéticas nestas “planilhas”, baseandose nas características citadas acima.

Por exemplo: se você disse que Funcionário possui as características: CPF, Nome e Sexo, terá que preencher estes dados para CADA Funcionário e da mesma forma para as outras “planilhas”.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

III)

Problemas:

1) Como associar o Dependente ao Funcionário ? 2) Como e onde armazenar a informação “Horas Trabalhadas” de cada Funcionário ao(s) Projeto(s) que porventura estejam alocados ? 3) Como e onde armazenar a informação “Data do Pedido” e “Quantidade” para cada Material solicitado pelo Funcionário ? 4) Como determinar a qual Departamento o funcionário está alocado? Obs.: você pode criar novas “planilhas” se achar necessário.

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

ExercĂcios Mini Mundos

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 1 A administradora de imóveis "DoLar" é uma empresa que cuida principalmente, da compra e venda de imóveis residenciais e comerciais no Grande Rio, dentre outras atividades. O atendimento atualmente é demorado e muitas vezes incompleto devido a demora no manuseio de muitas fichas acarretando a perda de muitas oportunidades de negócio. Todos os imóveis são comprados pela imobiliária para então, serem colocados a venda. A direção da empresa definiu como prioridade automatizar o processo de comercialização (compra e venda) dos imóveis, envolvendo seus proprietários (novos e antigos). A imobiliária considera "proprietário" toda pessoa que participou de um processo de comercialização (compra ou venda) no papel de dono (antigo ou novo). Entre outras informações, o sistema deverá ser capaz de controlar os imóveis comprados, vendidos e os de se "interesse" (não foram comercializados) e emitir: -

relação de todos os imóveis disponíveis para venda, contendo para cada um: Endereço, Bairro, Área (m2), descrição, proprietário antigo (o atual é a administradora) e o preço mínimo para a venda; relação de todos os imóveis vendidos, por bairro, contendo para cada um: Bairro, proprietário antigo, proprietário novo, preço de venda (ao proprietário novo) e preço de compra (peal imobiliária); relação dos proprietários que compraram mais de um imóvel na imobiliária (nome , CPF, endereço e telefone);relação dos proprietários que venderam mais de um imóvel para a imobiliária (nome e telefone).

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 2 Uma loja de venda de Eletrodomésticos quer automatizar o seu controle de compra e troca de aparelhos por parte de seus clientes. Todo aparelho vendido possui garantia de 1 ano, a partir da data de venda. Isto significa que qualquer troca só poderá ser realizada dentro deste período, mesmo que já tenha havido trocas em função desta compra. No termo de garantia é anotado a data da compra, marca modelo e número de série do aparelho vendido, juntamente com o nome e endereço do cliente que o comprou. A cada troca de aparelho, relativo a primeira compra, é verificado se ainda está no prazo de garantia e é registrado o cliente que realizou a troca. Qualquer cliente pode realizar uma troca, mesmo que não tenha sido o comprador. Os aparelhos defeituosos são devolvidos para a fábrica e não mais retornam para a loja. A loja quer saber: a) relação de aparelhos disponíveis na loja; b) relação de aparelhos que apresentaram defeitos contendo, data e o defeito apresentado; c) relação de clientes cujas compras nunca apresentaram defeito.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 3 Um dos restaurantes mais tradicionais do Rio de Janeiro, resolveu informatizar o seu controle de atendimento. O restaurante possui atualmente 30 garçons que servem diariamente mais de 500 refeições e o cardápio oferece mais de 40 pratos diferentes. O restaurante dispõe de mesas de 2, 4 e 6 lugares num total geral de 80 mesas. Cada garçom é responsável por atender no mínimo à 4 mesas e no máximo 10 e não podem atender mesas fora de sua responsabilidade. A remuneração dos mesmos é um percentual fixo sobre o consumo das mesas que cada um atendeu (TRIGGER). Ao encerrar a conta o cliente preenche uma avaliação sobre o atendimento prestado pelo garçom. Ao final do mês os garçons que obtiveram as 3 melhores avaliações recebem uma gratificação extra (STORED PROCEDURE). Não é permitido que pessoas ocupem mesas sem consumir. Requisitos de informação: -

relação de mesas que o garçom tem sob sua responsabilidade; número de assentos que o garçom tem sob sua responsabilidade; salário a pagar ao garçom no fim do mês; lista de pratos mais consumidos por dia da semana; mesas que estiveram mais tempo ocupadas; garçons que devem receber bonificação extra no fim do mês;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 4 O Instituto “KPenga” de Seguridade Social, IKSS, está interessado em controlar os pacientes internados, e seus atendimentos, nos seus hospitais. Quando uma pessoa credenciado junto ao IKSS passa mal, ela se dirige a um dos hospitais e se consulta com algum médico. Dependendo da gravidade o(s) médico(s) pode(m) decidir pela internação. Os pacientes, pessoas credenciadas que foram internadas, podem receber atendimento de vários profissionais de saúde, particularmente de médicos e enfermeiras. Não há interesse em controlar as pessoas que não foram internadas, nem as consultas antes da internação. Cada médico ou enfermeira pode estar vinculado a, no máximo 3 hospitais. Não se admite um empregado com mais de um vínculo no mesmo hospital. Não há interesse em controlar as datas em que ocorreram os atendimentos. O Modelo deve prever as seguintes informações: -

relação dos pacientes (nome, código do seguro social, idade) internado em um hospital, juntamente com os nomes e números dos médicos responsáveis por cada internação e o período de internação; relação dos médicos (nome, matrícula, especialidade) e enfermeiras (nome, matrícula e cargo) que deram atendimento a um paciente durante a internação; relação dos hospitais (nome, código e endereço, que um médico ou enfermeira mantém vínculo

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 5 (1 ponto) A corretora “Boa Ação”, deseja um sistema automatizado que a auxilie na venda de ações dos seus clientes durante o pregão. O sistema será reinicializado diariamente para que os clientes sem saldo e as ações que nenhum cliente possui saldo não apareçam nunca. Saldo é a quantidade de ação. Somente a tabela corretoras será preservada. O cliente expressa a intenção de vender uma ação pela ordem de venda. Nela o cliente informa a ação e a quantidade que ele quer vender e, a partir dela, a corretora tenta fechar negócios pelo preço mais alto que o mercado pagar. Para avaliar um ordem de venda o sistema deverá conferir se o cliente tem disponível a quantidade que colocou à venda. Como as compras feitas em um dia só estarão disponíveis para a venda no dia seguinte, o saldo das ações de cada cliente é calculado de madrugada e permanece válido por todo o pregão, e a quantidade disponível para venda, a qualquer momento do pregão, é sempre o saldo menos as quantidades das ordens já emitidas daquela ação por aquele cliente, naquele dia. Uma mesma ação pode ser posta a venda por um mesmo cliente em diversas ordens durante o pregão, desde que o saldo permita. Da mesma forma que uma mesma ordem pode ser negociada em vários lotes que somados não ultrapassem a quantidade da ordem. Com isso a mesma ordem pode acabar sendo negociada aos poucos por várias corretoras e ou pela mesma várias vezes. O sistema deve ser capaz de, ao final do pregão: -

Listar matrícula cliente, código da ação, quantidade e momento (h/m) das ordens cadastradas que não foram negociadas, nem parcialmente; Listar matricula cliente, código ação, quantidade e momento (h/m) das ordens cadastradas que foram parcialmente negociadas, juntamente com o CGC e nome da corretora, quantidade, momento e preço de cada lote negociado dentro desta ordem. Ao final de cada ordem, informar a quantidade que sobrou sem ser negociada.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 6 O Hospital “AkiCFicaBom” esta informatizando o controle de consultas médicas e o receituário que, eventualmente, o médico determina para o cliente. Cada médico cadastrado, possui mais de uma especialidade que segue o padrão da AMB: código com 8 dígitos e descrição com 100 caracteres e só podem estar vinculados a, no máximo, 4 Hospitais. Quando um médico começa ou deixa de clinicar uma determinada especialidade, é gravada a informação: especialidade ativa (A) ou inativa (I), para o médico. São gravadas também, informações para que o médico seja encontrado a qualquer momento: três telefones, um celular, um BIP e um eMail. Quando um médico perde o vínculo com um Hospital, a informação é eliminada do Banco de Dados. -

As especialidades são cadastradas com as seguintes informações: código da especialidade e sua descrição. Quando o cliente procura o Hospital pela primeira vez, é mantido o seu cadastro com as seguintes informações: código do cliente, nome do cliente, CPF e seu endereço. Ao final da consulta, que ocorreu em uma determinada data, o médico poderá emitir um receituário para o cliente, com um ou mais remédios. Os remédios são cadastrados com os seguintes dados: código do remédio, descrição do remédio, princípio ativo. Os hospitais cadastrados possuem as seguintes características: CGC, nome, endereço, 3 telefones, eMail.

São requisitos de informação: -

Médicos que mais consultaram em um determinado período de tempo Tempo de duração para cada consulta; Remédios que foram mais receitados; Clientes que mais se consultaram; Hospitais e seu corpo clínico Médicos que possuem a especialidade: CARDIOLOGIA.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 7 A loja de revenda de peças automotivas, está informatizando a sua revenda. Quer identificar também, caso uma peça esteja danificada, quantas outras, no caso peças componentes, poderão também estar afetadas. Esta loja efetua a venda, cadastrando apenas a data da venda e quantidade desejada da peça solicitada. O faturamento da venda ocorre apenas no final do mês, quando é gerada toda a documentação para que o comprador possa efetuar o pagamento na rede bancária. Infelizmente, estamos em um país onde a inflação é alta e o preços aumentam constantemente, porém, é necessário respeitar o preço de quando ocorreu a venda. O dono da loja pode não aumentar a peça, mesmo com a inflação alta. São requisitos de informação: -

Relação de peças e seus componentes; Peças que estão com o saldo abaixo da quantidade mínima; Faturamento que deve ser emitido, baseado nas compras de um período; Relação de compradores contendo: CPF ou CGC, nome e eMail; Evolução dos valores de cada peça;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 8 Uma empresa de contabilidade “KontaOK” tem a seguinte definição para parte do seu sistema contábil: -

Uma conta contábil, pode agregar diversas outras contas; Uma conta contábil, pode possuir movimentação; Cada movimentação está associada à um único Centro de Custo;

São requisitos de informação: -

Relação dos centros de custo contendo: código do centro, descrição e valor consumido em um período; Relação das contas contábeis (código e descrição) bem como das suas agregadas (código e descrição); Relação da movimentação contábil de um período de uma ou todas as contas; Centro de Custo que não movimentou em um determinado período;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 9 Uma empresa que fabrica pesticidas, material altamente sistematizar três casos do seu sistema de pagamento.

tóxico,

resolve

A cada departamento da empresa estão alocados diversos funcionários. Cada departamento possui um funcionário no cargo de chefia. O departamento possui de 1 a 5 ramais para comunicação interna. A empresa possui diversos setores mas a ADMINISTRAÇÃO, FÁBRICA e o setor de INVESTIMENTOS possuem algumas características específicas. -

apenas os funcionários da ADMINISTRAÇÃO podem fazer horas extras que, ao final do mês, caso ocorram, devem ser calculadas para que o pagamento possa ser efetuado. Sabemos que as horas extras dependem do dia da semana, e da quantidade de horas trabalhadas; os trabalhadores da FÁBRICA, podem manipular diversos produtos tóxicos que possuem um índice de toxicidade, utilizado tanto para o cálculo da quantidade máxima de horas para a exposição diária quanto para o cálculo de adicional de insalubridade do funcionário daquele setor. O número de horas de trabalho destes funcionários depende da demanda de produção, podendo trabalhar o máximo de horas em um dia ou não trabalhar no outro. os analistas financeiros da área de INVESTIMENTOS, acumulam bônus a cada negociação bem sucedida no mercado de ações. A cada venda diária, imediatamente o sistema de investimentos calcula e acumula o bônus do funcionário. Ao final do mês a quantidade ali acumulada é imediatamente creditada em conta corrente, fora da folha de pagamento. A informação do bônus, volta a zero para o próximo período.

São requisitos de informação: -

Relação de ramais do departamento, ordenado por departamento, contendo também o nome do chefe; Relação de funcionários dos departamentos e seus telefones (3 no máximo) de contato; Relação de horas extras do período, de cada funcionário da administração; Relação de materiais tóxicos contendo seu código, descrição, índice de toxicidade (IT) e tempo máximo de exposição (TME); Relatório com a quantidade de horas que cada funcionário ficou exposto ao material tóxico e quanto isto custará, no período; Relatório com matrícula, nome e bonificação em ordem decrescente de bonificação dos funcionários da área de investimentos;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 10 Uma empresa deseja fazer um “bolão de apostas“ centre seus funcionários, com base na Copa do Mundo de Futebol. As apostas serão feitas pela Internet e o valor depositado em uma conta corrente definida pelo funcionário que gerenciará o “bolão”. Para registrar corretamente as informações, se faz necessário observar as seguintes questões: -

Os apostadores podem fazer quantas cartelas de apostas desejar. Eles serão identificados pelo CPF, NOME e ainda é necessário o registro do EMAIL para futuras comunicações; Cada cartela de apostas é identificada por um NÚMERO e, a medida em que os jogos forem se realizando, a PONTUAÇÃO TOTAL será registrada para facilitar o processo de obtenção do RELATÓRIO DOS APOSTADORES COM MAIS PONTOS (RANKING) e a informação se é uma cartela válida (ou não) dependendo se o valor da aposta foi depositado ou não; Para cada jogo, que possui um NÚMERO, é registrado o placar. Em cada jogo, obviamente participam dois times; Como a Copa do Mundo está definida com 64 jogos, foi definido que cada cartela, conterá os palpites dos 64 jogos, onde o apostador registrará seus placares e a cada jogo realizado, um processamento registrará a pontuação obtida naquele jogo, que também refletirá na pontuação total da cartela, mencionada acima; Cada time é registrado no banco de dados com um NÚMERO, NOME e a IMAGEM da bandeira do país; A regras que regulamentam a pontuação sobre os placares, não são relevantes neste momento. O importante é registar os palpites dos apostadores e o placar oficial para que possam ser comparados e a pontuação atribuída.

Podemos dizer a seguinte frase para resumir o problema: “O apostador joga várias cartelas que contém palpites individuais sobre o placar dos jogos que se realizam entre dois times” Requisitos de informação: -

Relatório com o RANKING, (NÚMERO DA CARTELA, NOME DO APOSTADOR e TOTAL DE PONTOS OBTIDOS) ordenado de forma descendente pela pontuação; Time que fez mais gols Time que fez menos gols Times que mais participaram da COPA O nome do apostador com mais pontos O nome do apostador “lanterninha”

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Mini mundo 11 A Rádio “FM Istério”, deseja guardar as informações de todas as músicas transmitidas por ela. Para isso é necessário montar um banco de dados de acordo como seguinte: As gravadoras são identificadas pelo CGC, razão social, endereço, uma pessoa de contato e um web site e são responsáveis pela produção de vários CDs. As músicas, podem estar em CDs diferentes e em faixas diferentes. Cada música possui uma codificação própria, um nome e o tempo de duração e podem ter mais de um autor. Os CDs recebem uma classificação que determina sua categoria (ROCK, POP, MPB, CLÁSSICA, SERTANEJO, PAGODE etc....). Esta categoria define inclusive, o maior e o menor preço para comercialização. São registradas também, as seguintes informações para cada CD: nome, preço, data de lançamento e a quantidade de indicações. Os ouvintes ao solicitarem as músicas acabam fazendo referência a um determinado CD, que recebe uma indicação a mais (um contador de indicações). Dos autores, são registradas as seguintes informações: CPF e nome São requisitos de informações: -

Os CDs mais indicados As músicas que compõem mais de 3 CDs Os autores que possuem mais de 30 músicas As gravadoras e os CDs produzidos por elas A quantidade de CDs, por categoria

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Capítulo 2 Dependência Funcional e Normalização

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Dependência Funcional e Normalização Dependência Funcional Dados dois conjuntos de atributos (ou dois atributos) A e B de uma entidade, diz-se que B é funcionalmente dependente de A, ou que A determina B, ou que B depende de A, se a cada valor de A estiver associado um, e só um, valor de B. Se A determina B então B não é funcionalmente dependente de nenhum subconjunto de A. Uma dependência funcional é representada por : A Æ B (A determina B) Exemplo de identificação de dependências funcionais: #Funcionário 4022 4023 4024

Nome_próprio Renata Ari Maria

Apelido Reis Reis Cardoso

Departamento 501 203 203

Departamento Æ #Funcionário ? Não, pois o Departamento 203 Æ {4023, 4024} #Funcionário Æ Departamento ? Sim, pois conhecendo-se o #Funcionário (atributo unívoco) é possível determinar o Departamento (um funcionário só pode pertencer a um departamento). Nome_próprio Æ #Funcionário ? Não, pois podem existir funcionários com o mesmo nome. Podem haver múltiplos valores de #Funcionário para o mesmo Nome_próprio. #Funcionário Æ Apelido ? Apesar de dois funcionários terem o mesmo apelido, conhecendo-se o #Funcionário determina-se um só Apelido. #Funcionário Æ todos os outros atributos Observação Importante: A identificação de dependências funcionais não pode ser obtida apenas a partir da inspeção de algumas instâncias, mas sim através das próprias propriedades dos atributos.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Outro exemplo de identificação de dependências funcionais: Papelaria Colmeia Central Aguarela Silva

Artigo Caneta BIC fina Fita cola Borracha Caneta BIC fina

Preço 150,00 300,00 215,00 175,00

O Preço é funcionalmente dependente de artigo (Artigo Æ Preço) ? Não, pois o mesmo artigo pode ter preços diferentes em diferentes papelarias. O preço é funcionalmente dependente de papelaria (Papelaria Æ Preço) ? Não, porque para cada papelaria há tantos valores para Preço quantos os artigos vendidos nessa papelaria. Preço depende funcionalmente de ambos {Papelaria, Artigo} Æ Preço

Normalização Os dados, no mundo real, não aparecem da forma que Codd preconizou. Assim, devem ser tratados, der forma a corrigir as anomalias resultantes do processo de derivação lógica das tabelas e atingir os objetivos propostos pelo modelo relacional. A este processo, chamamos normalização. Depois dos dados serem sujeitos a este tratamento, dizem-se normalizados. Para normalizar podemos seguir os 5 mandamentos de Codd: 1 2 3 4 5

No repeating (Sem repetições) The fields depend upon the key (Os atributos dependem da CHAVE) The whole key (Da chave inteira) And nothing but the key (De mais nada a não ser da chave) So help me Cood (Que Cood me ajude!)

A normalização converte cada entidade gradualmente para “Formas Normais”, através da aplicação sucessiva de regras que alteram o formato dos dados da 1ªForma Normal até à 5ª Forma normal.

Formas Normais 1a. Forma Normal Uma relação está na 1a. forma normal (1FN) quando os domínios de todos os atributos consistem apenas em valores atômicos e não existem subgrupos de atributos repetidos. 47

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Passagem de uma entidade à 1FN Eliminar subgrupos repetidos, decompondo a relação em duas (ou mais) relações.

Exemplo: Consideremos assim a seguinte relação, representada sob a forma de uma tabela: #Funcionário F23 F12 F43 F56 F23 F55 F54 F95 F54 F56 F36 F67 F95 F55

Nome Fernanda Amanda Ari Cassia Fernanda Paulo Lucio Angelo Lucio Cassia Cassia Domingos Angelo Paulo

Sexo F F M F F M M M M F F M M M

Salário 33000 33000 46000 33000 33000 56000 56000 46000 56000 33000 44000 44000 46000 56000

Ano 70 57 70 65 70 70 65 57 65 65 65 65 57 70

Mês 7 5 9 8 7 7 6 5 6 8 8 6 5 7

Dia 9 6 8 10 9 9 12 6 12 10 3 6 6 9

#Função FUN01 FUN02 FUN03 FUN01 FUN02 FUN04 FUN05 FUN01 FUN02 FUN02 FUN01 FUN06 FUN06 FUN01

Anos 3 3 1 1 1 1 5 3 5 2 1 2 3 1

Esta relação pode ser considerada como estando na 1a. Forma Normal ? De fato, podemos encontrar grupos repetidos, especialmente, se olharmos para os dados, segundo uma organização diferente: #Funcionário F23 F23 F12 F36 F43 F54 F54 F55 F55 F56 F56 F67 F95 F95

Nome Fernanda Fernanda Amanda Cassia Ari Lucio Lucio Paulo Paulo Cassia Cassia Domingos Angelo Angelo

Sexo F F F F M M M M M F F M M M

Salário 33000 33000 33000 44000 46000 56000 56000 56000 56000 33000 33000 44000 46000 46000

Ano 70 70 57 65 70 65 65 70 70 65 65 65 57 57

Mês 7 7 5 8 9 6 6 7 7 8 8 6 5 5

Dia 9 9 6 3 8 12 12 9 9 10 10 6 6 6

#Função FUN01 FUN02 FUN02 FUN01 FUN03 FUN05 FUN02 FUN04 FUN01 FUN01 FUN02 FUN06 FUN01 FUN06

Anos 3 1 3 1 1 5 5 1 1 1 2 2 3 3

Podemos olhar para o esquema de relação da seguinte forma: #Funcionário

Nome

Sexo

Salário

Ano

Mês

Dia

#Função #Função #Função

Anos Anos Anos

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Para passar para a 1FN, temos que dividir a tabela em duas, eliminando os grupos repetidos:

#Funcionário

Nome

Sexo

#Funcionário

#Função

Salário

Ano

Mês

Dia

Anos

A chave para o primeiro registro é facilmente identificável: #Funcionário

Nome

Sexo

Salário

Ano

Mês

Dia

O segundo registro tem a chave concatenada #Funcionário, #Função. Não se pode saber o número de anos de experiência sem se saber qual o código desse funcionário e qual a função desempenhada:

#Funcionário

#Função

Anos

A informação ficaria da seguinte forma, na 1ª Forma Normal: Funcionário #Funcionário F23 F12 F36 F43 F54 F55 F56 F67 F95

Nome Fernanda Amanda Cassia Ari Lucio Paulo Cassia Domingos Angelo

Sexo F F F M M M F M M

Salário 33000 33000 44000 46000 56000 56000 33000 44000 46000

Ano 70 57 65 70 65 70 65 65 57

Mês 7 5 8 9 6 7 8 6 5

Dia 9 6 3 8 12 9 10 6 6

Funcionário X Função #Funcionário F23 F12 F43 F56 F23 F55 F54 F95 F54 F56 F36 F67 F95 F55

#Função FUN01 FUN02 FUN03 FUN01 FUN02 FUN04 FUN05 FUN01 FUN02 FUN02 FUN01 FUN06 FUN06 FUN01

Anos 3 3 1 1 1 1 5 3 5 2 1 2 3 1

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

2a. Forma Normal Uma relação está na 2a. forma normal (2FN) quando estiver na 1FN e, todos os atributos que não pertencem à chave dependem de toda a chave (e não de um subconjunto da chave). Assim, uma tabela estará na 2a. FN se cada atributo da relação depende totalmente da chave. Quando a chave é constituída por mais do que um atributo, a relação pode não estar na 2a. forma Normal. Daqui resulta que se a chave de uma tabela é constituída apenas por um atributo, está automaticamente na 2a. forma normal.

Passagem de uma relação à 2FN Separar os atributos que dependem de um subconjunto da chave, decompondo a relação em duas (ou mais) relações. Exemplo: Consideremos a seguinte relação: #Peça 1 1 1 1 2 2 2 3 4 4 4 5 5

#Fornecedor FOR10 FOR11 FOR13 FOR19 FOR31 FOR10 FOR13 FOR13 FOR10 FOR31 FOR19 FOR11 FOR10

Nome Mauro Basílio Padreira Favos BBC Mauro Padreira Padreira Mauro BBC Favos Basílio Mauro

Local PAlegre Minas Ceará VRedonda Natal PAlegre Ceará Ceará PAlegre Natal VRedonda Minas PAlegre

Preço 20,00 20,00 20,00 22,00 825,00 723,00 589,00 213,00 852,00 752,00 952,00 1.236,00 1.258,00

Em que forma normal se encontra a relação? Encontra-se na 1FN, pois não contém grupos repetidos e a chave é composta pelos atributos #Peça + #Fornecedor. No entanto, não temos qualquer garantia de que se encontra na 2FN, pois a chave é composta por mais do que um atributo. Repare-se nos problemas que se podem levantar, pelo fato de não se encontrar na 2FN: Não se pode inserir a ficha de um fornecedor sem que tenha fornecido pelo menos uma peça. Este campo nunca poderia ficar em branco, uma vez que faz parte da chave.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Existem problemas quando se pretende fazer a atualização dos dados de um fornecedor. Temos que procurar todas as ocorrências desse fornecedor e mudar tantas vezes quantas as necessárias. Estes tipos de irregularidades podem ser removidos se dividirmos o registro em dois, sendo necessário o conceito de dependências funcionais: Dependência Funcional: Um atributo ou um conjunto de atributos B, de um registro R, é funcionalmente dependente de um atributo, ou conjunto de atributos, A, do registro R se para cada instância de A em R é encontrada uma única instância de B. Consideremos ainda outro tipo de dependências Dependência Funcional Total Um atributo ou um conjunto de atributos, B, de uma relação, R, é totalmente funcionalmente dependente de um atributo, ou conjunto de atributos, A, da relação R se B é funcionalmente dependente de todo A e não de um subconjunto de A. Podemos, agora, identificar todas as dependências do exemplo dado anteriormente: #Peça

#Fornecedor

Nome

Local

Preço

Para o registro estar na 2FN, todas as dependências da chave têm que ser totais. Reparemos que apenas o atributo Preço é totalmente dependente de toda a chave. Sendo assim, há necessidade de dividir o registro em dois: #Fornecedor

#Peça

Nome

#Fornecedor

Local

Preço

A relação na 2FN tem a seguinte forma:

Fornecedor #Fornecedor FOR10 FOR11 FOR19 FOR13 FOR31

Nome Mauro Basílio Favos Padreira BBC

Local PAlegre Minas VRedonda Ceará Natal

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Peça/Fornecedor #Peça 1 1 1 1 2 2 2 3 4 4 4 5 5

#Fornecedor FOR10 FOR11 FOR13 FOR19 FOR31 FOR10 FOR13 FOR13 FOR10 FOR31 FOR19 FOR11 FOR10

Preço 20,00 20,00 20,00 22,00 825,00 723,00 589,00 213,00 852,00 752,00 952,00 1.236,00 1.258,00

3ª Forma Normal Uma relação está na 3a. forma normal (3FN) se estiver na 2FN e, os atributos que não pertencem à chave não dependem de nenhum atributo que também não pertence à chave. Assim, uma tabela que esteja na 2FN pode apresentar ainda um certo tipo de anomalia, de tal forma que possa existir um conjunto de atributos não chave, mas que são chave para um outro conjunto de atributos. Esta situação ocorre quando existem dependências transitivas. Uma tabela está na 3FN se não há dependências entre atributos não chave. Vamos supor que A, B e C são conjuntos de atributos de uma relação R. Se C é funcionalmente dependente de B e B é funcionalmente dependente de A, então C é transitivamente dependente de A: A

Para ser feita a conversão para a 3FN, o registro é dividido da seguinte forma: A

Passagem de uma relação à 3FN Separar os atributos que dependem de outro atributo não pertencente à chave, decompondo a relação em duas (ou mais) relações.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Exemplo: Consideremos a seguinte relação: #Empregado EMP01 EMP02 EMP03 EMP04 EMP05 EMP06 EMP07 EMP08 EMP09 EMP10 EMP11 EMP12 EMP13 EMP14

Nome João Maria Josefina Maria Luís Jorge José Reinaldo Nuno Fernando Mariano Jeremias Esaú Pedro

Salário 120 452 23 614 123 842 120 452 65 52 52 52 52 52

#Projeto PROJ01 PROJ01 PROJ02 PROJ01 PROJ03 PROJ02 PROJ03 PROJ01 PROJ03 PROJ01 PROJ02 PROJ02 PROJ01 PROJ01

Data do fim 17.07.97 17.07.97 31.08.97 17.07.97 31.12.97 31.08.97 31.12.97 17.07.97 31.12.97 17.07.97 31.08.97 31.08.97 17.07.97 17.07.97

Identifiquemos as dependências funcionais que existem: #Empregado

Nome

Salário

#Projeto

Data do fim

Verifica-se que existem dependências transitivas. A conversão para a 3ªFN é feita através da remoção das dependências transitivas, dividindo as tabelas. A relação fica com a seguinte forma : Empregado #Empregado EMP01 EMP02 EMP03 EMP04 EMP05 EMP06 EMP07 EMP08 EMP09 EMP10 EMP11 EMP12 EMP13 EMP14

Nome João Maria Josefina Maria Luís Jorge José Reinaldo Nuno Fernando Mariano Jeremias Esaú Pedro

Salário #Projeto 120,00 PROJ01 452,00 PROJ01 23,00 PROJ02 614,00 PROJ01 123,00 PROJ03 842,00 PROJ02 120,00 PROJ03 452,00 PROJ01 65,00 PROJ03 52,00 PROJ01 52,00 PROJ02 52,00 PROJ02 52,00 PROJ01 52,00 PROJ01

Projeto #Projeto PROJ01 PROJ02 PROJ03

Data do Fim 17.07.97 31.08.97 31.12.97

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

As anomalias que podem ocorrer quando uma relação não está na 3FN são: • •

•

Não podem ser inseridos dados relativos ao projeto antes de se ter selecionado os empregados para o executarem. Se todos os empregados abandonarem um determinado projeto, antes de serem selecionados outros, pode ocorrer uma situação de inconsistência que pode ocorrer em remoção de informação importante. Se a data de fim de projeto for alterada, tem que se fazer alteração tantas vezes quantas as necessárias.

Vantagens/Desvantagens da Normalização -

Vantagens - Estruturas de dados mais estáveis - Elimina a redundância - Obtém-se um modelo de dados mais natural e mais simples - Evitam-se os efeitos laterais da alteração - Evitam-se os efeitos laterais da inserção - Evitam-se os efeitos laterais da remoção Conclusão: Facilita a exploração e manutenção de tabelas

Desvantagens - Favorece a proliferação do no. de tabelas - Favorece a fragmentação exagerada - Perigoso de seguir cegamente Conclusão: Normalizar? Sim, mas com bom senso ...

Forma Normal Boyce Codd Uma relação está na forma normal de Boyce Codd (FNBC) quando todo o determinante da relação for uma chave candidata. A FNBC corresponde a m grau de normalização mais elevado do que a 3FN e é necessária quando: -

uma entidade tem várias chaves candidatas; as chaves candidatas são compostas; as chaves candidatas sobrepõem-se porque possuem pelo menos um atributo em comum.

Exemplo de entidade que necessita da FNBC: Seminário S1 S1 S2 S2

Estudante 1022 3088 1022 4325

Instrutor Reis Couto Pires Guedes

Participações 12 12 14 14

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Cada seminário é dirigido por dois instrutores, mas um instrutor só pode dirigir um seminário; Um estudante pode participar em mais do que um seminário mas é orientado somente por um dos instrutores.

Chaves candidatas: Seminário, Estudante Estudante, Instrutor

Dependências funcionais Seminário, Estudante Participações Estudante, Instrutor Participações Instrutor → Seminário

Determinantes Instrutor, São chaves candidatas

Seminário, São chaves candidatas Não é chave candidata

Passagem de uma relação à FNBC Separar o(s) atributo(s) que depende(m) de atributo que não é chave candidata, decompondo a relação em duas (ou mais) relações. No exemplo ficaríamos com as seguintes entidades: Participante (Estudante, Instrutor, Participações) Orientador (Instrutor, Seminário) Considerações relativamente a normalização A essência do processo de normalização consiste na decomposição sucessiva de uma coleção de relações, sem perda de informação, com base num conjunto de regras (formas normais). Benefícios do processo de normalização: -

Estruturação da informação e melhoria da qualidade da representação relacional; Eliminação das possibilidades de ocorrência de anomalias na manipulação dos dados (que comprometem a sua integridade); Economia de espaço de armazenamento e de custos de manipulação. Exemplos de custos evitados: manipulação de um volume de dados maior do que o efetivamente, atualização de dados redundantes, etc.).

Potência a estabilidade do modelo lógico relacional, ao aumentar a capacidade de um modelo se manter inalterado face a mudanças que

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

venham a ser percebidas ou introduzidas no ambiente que tenha sido modelado; Observações importante Normalização NÃO é um processo com finalidade restritiva, mas sim de caráter organizacional. Fragmentação da informação e suas conseqüências é a principal limitação do processo de normalização

Estratégias de Normalização Alguns aspectos a serem considerados: -

O processo de normalização raramente percorre todas as formas normais (da 1FN à 5FN);

Freqüentemente, o analista reconhece, por experiência própria, que uma dada entidade não está normalizada e coloca-a diretamente na 3FN ou na FNBC;

Uma estratégia muito usada consiste em normalizar para a FNBC em iterações sucessivas, utilizando a análise de dependências funcionais.

Estratégia de decomposição usando a análise de dependências funcionais

Desenvolver Relação Universal

Determinar todas as Dependências Funcionais

A relação está na FNBC

SIM

Modelo Concluído

NÃO

Decompor a relação em duas

Figura 36

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Decomposição de uma relação com vista à obtenção de relações na FNBC -

Consideramos a relação R (A, B, C, D, E, ...), que não está na FNBC;

Procura-se uma DF C Æ D que seja responsável por a relação não estar na FNBC;

Criam-se duas relações: R1(A, B, C, E, ...) e R2(C, D);

Verifica-se se R1 está na FNBC;

O processo continua até todas as relações obtidas por decomposição estarem na FNBC.

Diagrama de dependências funcionais #Produto Preço #Fornecedor

Figura 37 #Produto, #Fornecedor Æ Preço Exemplo de normalização usando a análise de dependências funcionais Existências Q_Alerta Tipo #Produto Preço #Fornecedor Local

Telefone

Figura 38 Não está na FNBC porque existem determinantes que não são chave (Tipo) Chave candidata Determinantes

#Produto, #Fornecedor #Produto, #Fornecedor #Produto #Fornecedor

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Tipo Decompondo as relações para a FNBC

Relação_01 Q_Alerta Tipo

Figura 39 Chave candidata e determinante: TIPO Relação_02 Existências #Produto Tipo

Figura 40 Chave candidata e determinante: #PRODUTO

Relação_03 Local #Fornecedor Telefone

Figura 41 Chave candidata e determinante: #FORNECEDOR

Relação_04

#Produto Preço #Fornecedor

Figura 42 Chave candidata e determinante: #PRODUTO, #FORNECEDOR Modelo de dados final R2 R3 R4 R5

(Tipo, Q_Alerta) (#Produto, Existências, Tipo) (#Fornecedor, Morada, Telefone) (#Produto, #Fornecedor, Preço)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Exercícios Normalização

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 1 Suponha que temos os seguintes requisitos para um banco de dados de Universidade, que é usado para manter o histórico escolar dos alunos: a) A Universidade mantém de cada aluno, o nome (ALUNOME), número do aluno (ALUNUM), número da identidade (ALURG), endereço atual (ALUENDER) e telefone (ALUFONE), data de nascimento (ALUDTNASC), sexo (SEXO), classe (CLASSE: calouro, veterano, ..., formando), centro universitário (CODCENTRO), departamento (CODDEPTO) e o tipo de graduação (TIPOGRAD: licenciatura, bacharelado, ..., doutorado). ALUNUM e ALURG são únicos para cada aluno. b) Cada departamento é descrito pelo nome (NOMEDEPTO), código do departamento (CODDEPTO), código da unidade (CODUNIDEPTO), telefone do departamento (FONEDEPTO) e centro universitário (CODCENTRO). Os nomes e códigos têm valores únicos para cada departamento. c) Cada curso tem um nome de curso (CURNOME), descrição (CURDESC), número de código (CURNUM), número de horas semestrais (CREDITOS), nível (NIVEL) e departamento que oferece (CODDEPTO). O número de código é único para cada curso. d) Cada turma de oferecimento tem um instrutor (NOMEINSTRUTOR), semestre (SEMESTRE), ano (ANO), curso (CURNUM) e número da turma (NUMTURMA). O número de turma distingue diferentes turmas de oferecimento de um mesmo curso que é oferecido durante o mesmo semestre / ano; seus valores são 1, 2, 3, ...., até o total de números de turmas oferecidas durante cada semestre. e) Uma transcrição no histórico corresponde a um aluno (ALURG), os dados do curso efetuado (NUMTURMA e CURNUM) e a média obtida no curso (NOTA). Desenvolva um esquema de banco de dados relacional para essa aplicação de banco de dados. Primeiro mostre todas as dependências funcionais que devem existir entre os atributos. Então, desenvolva esquemas relacionais para o banco de dados que estão em 3NF ou FNBC. Especifique os atributos chaves de cada relação. Note qualquer requisito não especificado e faça suposições apropriadas para tornar a especificação completa.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 2 Normalize a relação até a 3NF: InfoIPTU = ( codinscIMV, nomepropIMV, ruapropIMV, bairropropIMV, cidpropIMV, estpropIMV, fonepropIMV, benfsIMV, ruaIMV, bairroIMV, vlrterreno, areaterreno, areaconstruida, padraoconstrucao, vlrIMV, codloteamento, quadraterr, loteterr, nomesmoradores, dtanivmoradores ) Legenda: - prop = proprietário - IMV = imóvel - cid = cidade - est = estado - benfs = conjunto de benefícios - vlr = valor -

Bairro determina os vários loteamentos que o compõem. As quadras são numeradas em ordem no loteamento e os lotes são numerados em ordem de quadra. CODLOTEAMENTO + QUADRA + LOTE são chave Os terrenos que têm construção, têm alíquota de imposto diferenciada dos que não tem construção. O número de benfeitorias é importante para determinar a alíquota dos terrenos em construção.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 3 Considere o esquema de relação EmpDepto = ( numat, nomemp, datanasc, endereço, ndepto, nomedep, nchefe) e o seguinte conjunto G de dependências funcionais em EmpDepto: G = {numat Æ {nomemp, datanasc, endereço, ndepto}, ndepto Æ {nomedep, nchefe}}. Calcule {numat}+ e {ndepto}+ em respeito a G. Normalização 4 Por que as dependências transitivas e consideradas ruins no esquema relacional?

dependências

parciais

são

Normalização 5 Considere a relação universal R = { A, B, C, D, E, F, G, H, I, J } e o conjunto de dependências funcionais F={ {A, B} Æ C A Æ {D, E} BÆF F Æ {G, H} D Æ {I, J} } Qual é a chave para R? Decomponha R em relação a 2NF e, então, em relação a 3NF. Normalização 6 Repita o exercício Normalização dependências funcionais:

para

seguinte

conjunto

G={ {A, B} Æ C {B, D} Æ {E, F} {A, D} Æ {G, H} AÆI HÆJ }.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 7 Seja F o conjunto de dependências funcionais abaixo: codend, prefixo, numfone Æ tipofone, numfone codend, numrua, cep Æ tipofone nomerua, numrua, cep Æ nomerua, cep codend, complemento Æ complemento cidade, estado Æ codcid codcid Æ ddd codcid Æ cepmin, cidade codcid Æ cepmax, estado codcid, estado Æ cepmin, cepmax, cidade cidade, codcid Æ cepmin, cepmax, ddd ddd Æ estado Aplique o algoritmo visto em sala de aula para produzir os fechos de cada dependência funcional acima. Então, analise os conjuntos resultantes e normalize o esquema até a 3NF.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 8 Normalize a relação PEDIDO até a 3NF: PEDIDO = (NÚMERO_PED, PRAZO_PED, COD_CLI, END_CLI, CGC_CLI, IE_CLI, {COD_PROD}, {DESC_PROD}, {UNID_PROD}, {QTD_PROD}, {VU_PROD}, COD_VEND, NOME_VEND) 1a. Forma Normal: ausência de atributos multivalorados PEDIDO (NÚMERO_PED, PRAZO_PED, COD_CLI, END_CLI, CGC_CLI, IE_CLI, COD_VEND, NOME_VEND) ITEM PEDIDO (NÚMERO_PED, COD_PROD, DESC_PROD, UNID_PROD, QTD_PROD, VU_PROD) 2a. Forma Normal: ausência de dependências funcionais parciais PEDIDO (NÚMERO_PED, PRAZO_PED, COD_CLI, END_CLI, CGC_CLI, IE_CLI, COD_VEND, NOME_VEND) ITEM PEDIDO (NÚMERO_PED, COD_PROD, QTD_PROD) PRODUTO (COD_PROD, DESC_PROD, UNID_PROD, VU_PROD) 3a. Forma Normal: ausência de dependências funcionais transitivas PEDIDO (NÚMERO_PED, PRAZO_PED, COD_CLI, COD_VEND) ITEM PEDIDO (NÚMERO_PED, COD_PROD, QTD_PROD) PRODUTO (COD_PROD, DESC_PROD, UNID_PROD, VU_PROD) CLIENTE (COD_CLI, NOME_CLI, END_CLI, CGC_CLI, IE_CLI) VENDEDOR (COD_VEND, NOME_VEND)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 9 Normalize a relação TURMA até a 3NF: TURMA = (COD_TURMA,DESC_TURMA,{COD_ALUNO}, NOME_ALUNO}, {NASC_ALUNO}, {TEL_ALUNO}, {COD_DISC}, {DESC_DISC}) 1a. Forma Normal: ausência de atributos multivalorados TURMA (COD_TURMA, DESC_TURMA) ALUNO (COD_ALUNO, NOME_ALUNO, NASC_ALUNO, COD_TURMA, COD_DISC, DESC_DISC) ALUNO_TELEFONE (COD_ALUNO, TEL_ALUNO) 2a. Forma Normal: ausência de dependências funcionais parciais Não existem dependências funcionais parciais 3a. Forma Normal: ausência de dependências funcionais transitivas TURMA (COD_TURMA, DESC_TURMA) ALUNO (COD_ALUNO, NOME_ALUNO, NASC_ALUNO, COD_TURMA) ALUNO_TELEFONE (COD_ALUNO, TEL_ALUNO) DISCIPLINA(COD_DISC, DESC_DISC) ALUNO_DISCIPLINA(COD_ALUNO, COD_DISC)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 10 Normalizar a relação ASSOCIADO até a 3FN Associados - Os TITULARES são vinculados aos PLANOS de saúde. - Os DEPENDENTES são ligados aos TITULARES e possuem um GRAU de parentesco ASSOCIADOS = (MAT_TIT, NOM_TIT, {TEL_TIT}, {SEQ_DEP}, {NOM_DEP}, {COD_GRA}, {DES_GRA}, COD_PLA, DES_PLA) 1a. Forma Normal: ausência de atributos multivalorados TITULAR (MAT_TIT, NOM_TIT, COD_PLA, DES_PLA) TEL_TIT (MAT_TIT, TEL_TIT) DEPENDENTE (MAT_TIT, SEQ_DEP, NOM_DEP, COD_GRA, DES_GRA) 2a. Forma Normal: ausência de dependências funcionais parciais TITULAR (MAT_TIT, NOM_TIT, COD_PLA, DES_PLA) TEL_TIT (MAT_TIT, TEL_TIT) DEPENDENTE (MAT_TIT, SEQ_DEP, NOM_DEP, COD_GRA) GRAU (COD_GRA, DES_GRA) 3a. Forma Normal: ausência de dependências funcionais transitivas TITULAR (MAT_TIT, NOM_TIT, COD_PLA) TEL_TIT (MAT_TIT, TEL_TIT) DEPENDENTE (MAT_TIT, SEQ_DEP, NOM_DEP, COD_GRA) GRAU (COD_GRA, DES_GRA) PLANO (COD_PLA, DES_PLA)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 11 Normalizar a relação CLIENTES até a 3FN Clientes - Os CLIENTES são associados a determinadas CLASSES. - As CONTAS correntes pertencem aos CLIENTES e existem em um determinado BANCO. CLIENTES = (COD_CLI, NOM_CLI, {TEL_CLI}, COD_CLA, DES_CLA, {NUM_CC}, {SALDO_CC}, {COD_BCO}, {DES_BCO}) 1a. Forma Normal: ausência de atributos multivalorados CLIENTE (COD_CLI, NOM_CLI, COD_CLA, DES_CLA) TEL_TIT (COD_CLI, TEL_CLI) CONTAS (COD_CLI, NUM_CC, SALDO_CC, COD_BCO, DES_BCO) 2a. Forma Normal: ausência de dependências funcionais parciais CLIENTE (COD_CLI, NOM_CLI, COD_CLA, DES_CLA) TEL_TIT (COD_CLI, TEL_CLI) CONTAS (COD_CLI, NUM_CC, SALDO_CC, COD_BCO) BANCO (COD_BCO, DES_BCO) 3a. Forma Normal : ausência de dependências funcionais transitivas CLIENTE (COD_CLI, NOM_CLI, COD_CLA) TEL_CLI (COD_CLI, TEL_CLI) CONTAS (COD_CLI, NUM_CC, SALDO_CC, COD_BCO) BANCO (COD_BCO, DES_BCO) CLASSE (COD_CLA, DES_CLA)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 12 Normalizar a relação FOLHA até a 3FN Folha de Pagamento (FOLHA) - Nos DEPARTAMENTOS trabalham vários FUNCIONÁRIOS - Na folha de pagamento dos FUNCIONÁRIOS constam várias rubricas de PROVENTOS e DESCONTOS (PD). FOLHA = (MAT_FUN, NOM_FUN, {COD_PD}, {DES_PD}, {COMP_FOL}, {VALOR_FOL}, SIG_DEP, DES_DEP) 1a. Forma Normal: ausência de atributos multivalorados FUNCIONARIO (MAT_FUN, NOM_FUN, SIG_DEP, DES_DEP) FOLHA (MAT_FUN, COD_PD, DES_PD, COMP_FOL, VALOR_FOL) 2a. Forma Normal: ausência de dependências funcionais parciais FUNCIONARIO (MAT_FUN, NOM_FUN, SIG_DEP, DES_DEP) PD (COD_PD, DES_PD) FOLHA (MAT_FUN, COD_PD, COMP_FOL, VALOR_FOL) 3a. Forma Normal: ausência de dependências funcionais transitivas DEPARTAMENTO(SIG_DEP, DES_DEP) FUNCIONARIO (MAT_FUN, NOM_FUN, SIG_DEP) PD (COD_PD, DES_PD) FOLHA (MAT_FUN, COD_PD, COMP_FOL, VALOR_FOL)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Exercício de Engenharia Reversa Nos exercícios anteriores, obtivemos o Modelo Lógico normalizado. Para cada um deles, elabore o Modelo Conceitual.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Capítulo 3 Álgebra Relacional

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

A Álgebra Relacional A álgebra relacional é uma coleção de operações canônicas (do grego KÁNON - regra, regra geral onde se inferem regras especiais) utilizadas para manipular as relações. Estas operações são utilizadas para selecionar tuplas de relações individuais e para combinar tuplas relacionadas de relações diferentes para especificar uma consulta em um determinado banco de dados. O resultado de cada operação também pode ser manipulado pela álgebra relacional. É utilizada a letra grega σ (sigma) para demonstrar a condição de seleção e π (pi) para definir a lista de atributos. Portanto a “sintaxe” seria algo assim: CONSULTA = π atributos σ condição (relação ) Adotaremos o seguinte Banco de Dados para nossos primeiros exemplos: cod_d

desc_d

DEPTO (1,n)

POSSUI supervisionado

EMPREGADO

POSSUI (1,n) supervisor

(0,n)

cod_p des_p local

(1,1)

(0,n)

Trabalha

(0,n)

Projeto

nome salario

POSSUI nome

(1,1)

nasc Dependente

relacao sexo

Figura 43 MODELO LÓGICO DEPTO( cod_d,

desc_d)

PROJETO( cod_p, EMPREGADO( rg,

desc_p, nome,

DEPENDENTES ( rg, TRABALHA ( rg,

local)

salário,

nome,

nasc,

rg_sup,

cod_d)

relacao, sexo)

cod_p)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Conteúdo das bases de dados

RG 1 2 3 4 5 6 7 8 9 10 11

RG 1 1 3 4 5

NOME Rodrigo Renato Fernanda Roberta Flávia Vera Leonardo Carla Camilo Lúcio Cleôncio

NOME Bruna Júlia José João Verônica

COD_P 1 2 3 4

EMPREGADO SALARIO 1.000,00 1.500,00 2.500,00 1.000,00 500,00 3.000,00 1.000,00 500,00 1.200,00 1.200,00 5.000,00

COD_D 1 3 2 1 3 1 1 3 2 2 -

DEPENDENTES NASC SEXO 01/01/1987 F 04/10/1960 F 03/05/1991 M 10/10/1990 M 15/08/1980 F PROJETO DESC_P FINANC CONTAB SAUDE ESTOQUE

COD_D 1 2 3

RG 1 2 3 4 5 6 7 8 9 10 1 6 5 7

RG_SUP 6 2 3 6 2 6 6 2 3 3 11

RELAÇÃO Filho(a) Esposa(o) Filho(a) Filho(a) Filho(a)

LOCAL SP RJ MG ES

DEPTO DESC_D Análise Programação Operação TRABALHA COD_P 1 2 3 3 2 1 3 2 1 1 2 3 1 2

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

A Operação Select A operação select é utilizada para selecionar um subconjunto de tuplas de uma relação, sendo que estas tuplas devem satisfazer uma condição de seleção. A forma geral de uma operação select é: σ condição_de_seleção (nome_da_relação) condição_de_seleção é uma expressão booleana aplicada sobre os atributos da relação nome_da_relação é o nome da relação sobre a qual será aplicada a operação select. As operações relacionais que podem ser aplicadas na operação select são: < > <= >= = <> ou !=

Menor que Maior que Menor ou igual Maior ou igual Igual Diferente

além dos operadores booleanos: And Or Not

E Ou Negação

A operação select é unária, ou seja, só pode ser aplicada a uma única relação. Não é possível aplicar a operação sobre tuplas de relações distintas. Exemplos: CONSULTA01 = σ salário < 1.500,00 (EMPREGADO) RG 1 4 5 7 8 9 10

NOME Rodrigo Roberta Flávia Leonardo Carla Camilo Lúcio

EMPREGADO SALARIO 1.000,00 1.000,00 500,00 1.000,00 500,00 1.200,00 1.200,00

COD_D 1 1 3 1 3 2 2

RG_SUP 6 6 2 6 2 3 3

CONSULTA02 = σ relacao = “Filho(a)” e sexo = “F” (DEPENDENTES) RG 1 5

NOME Bruna Verônica

DEPENDENTES NASC SEXO 01/01/1987 F 15/08/1980 F

RELAÇÃO Filho(a) Filho(a)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

A Operação Project A operação project seleciona um conjunto determinado de colunas de uma relação. A forma geral de uma operação project é: π lista_de_atributos (nome_da_relação) A letra grega π (pi) representa a operação project, lista_de_atributos representa a lista de atributos que o usuário deseja selecionar; nome_da_relação representa a relação sobre a qual a operação project será aplicada. CONSULTA03 = π nome, nasc

(DEPENDENTES)

DEPENDENTES NOME NASC Bruna 01/01/1987 Júlia 04/10/1960 José 03/05/1991 João 10/10/1990 Verônica 15/08/1980

Seqüencialidade de Operações As operações project e select podem ser utilizadas de forma combinada, permitindo que apenas determinadas colunas de determinadas tuplas possam ser selecionadas. A forma geral de uma operação seqüencializada é: π lista_de_atributos σ condição_de_seleção (nome_da_relação) CONSULTA04= (EMPREGADO) NOME Renato Fernanda Vera Cleôncio

nome,

EMPREGADO SALARIO 1.500,00 2.500,00 3.000,00 5.000,00

salário,

cod_d

salário

1.500,00

COD_D 3 2 1 -

Apesar de mais elegante, a consulta4 também pode ser escrita da seguinte forma: CONSULTA05 = σ salário >= 1.500,00 (EMPREGADO)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

CONSULTA06 = π nome, salário, cod_d

(CONSULTA05)

Operações Matemáticas Levando em consideração que as relações podem ser tratadas como conjuntos, podemos então aplicar um conjunto de operações matemáticas sobre as mesmas. Estas operações são: união (∪) , interseção (∩) e diferença (−). Este conjunto de operações não é unário, ou seja, podem ser aplicadas sobre mais de uma tabela, porém, existe a necessidade das tabelas possuírem tuplas exatamente do mesmo tipo. Estas operações podem ser definidas da seguinte forma: união - o resultado desta operação representada por R ∪ S é uma relação T que inclui todas as tuplas que se encontram em R e todas as tuplas que se encontram em S; interseção - o resultado desta operação representada por R ∩ S é uma relação T que inclui as tuplas que se encontram em R e em S ao mesmo tempo; diferença - o resultado desta operação representada por R − S é uma relação T que inclui todas as tuplas que estão em R mas não estão em S. Selecione todos os empregados que trabalham no departamento número 2 ou que supervisionam empregados que trabalham no departamento número 2. Vamos primeiro selecionar todos os funcionários que trabalham no departamento número 2. CONSULTA07 = σ cod_d = 2 (EMPREGADOS)

RG 3 9 10

NOME Fernanda Camilo Lúcio

EMPREGADO SALARIO 2.500,00 1.200,00 1.200,00

COD_D 2 2 2

RG_SUP 3 3 3

Vamos agora selecionar o supervisor dos empregados que trabalham no departamento número 2. CONSULTA08 = π rg_sup

(CONSULTA07)

EMPREGADO RG_SUP 3

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Vamos projetar apenas o RG dos empregados selecionados: CONSULTA09 = π rg

(CONSULTA07)

EMPREGADO RG 3 9 10

E por fim vamos unir as duas tabelas, obtendo o resultado final CONSULTA10 = CONSULTA08 U CONSULTA09

EMPREGADO RG 3 3 9 10

Vamos selecionar todos os empregados que desenvolvem algum projeto e que trabalham no departamento número 2. Para isso, primeiro devemos selecionar todos os empregados que trabalham em algum projeto: CONSULTA11 = π rg

(TRABALHA)

TRABALHA RG 1 2 3 4 5 6 7 8 9 10

Agora, vamos selecionar empregados que trabalham no departamento 2: CONSULTA12 = π rg σ (depto=2) (EMPREGADO) EMPREGADO RG 3 9 10

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Com a consulta a seguir, obteremos todos os empregados que trabalham no departamento 2 e que desenvolvem algum projeto CONSULTA13 = CONSULTA11 ∩ CONSULTA12 EMPREGADO RG 3 9 10

Para selecionar todos os empregados que não desenvolvem projetos, devemos inicialmente selecionar os que trabalham em algum projeto CONSULTA14 = π rg

(TRABALHA)

TRABALHA RG 1 2 3 4 5 6 7 8 9 10

...e a seguir, quem é empregado CONSULTA15 = π rg (EMPREGADO) EMPREGADO RG 1 2 3 4 5 6 7 8 9 10 11

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

...finalmente, quem é empregado e não está trabalhando em nenhum projeto. CONSULTA16 = CONSULTA15 - COLSULTA14 CONSULTA16 RG 11

Produto Cartesiano O produto cartesiano é uma operação binária que combina todas as tuplas de duas tabelas. Diferentemente da operação união, o produto cartesiano não exige que as tuplas das tabelas possuam exatamente o mesmo tipo. O produto cartesiano permite então a consulta entre tabelas relacionadas utilizando uma condição de seleção apropriada. O resultado de um produto cartesiano é uma nova tabela formada pela combinação das tuplas das tabelas sobre as quais aplicou-se a operação. O formato geral do produto cartesiano entre duas tabelas R e S é:

R x S. Exemplo: CONSULTA17 =(PROJETO) x (DEPTO)

COD_D 1 1 1 1 2 2 2 2 3 3 3 3

CONSULTA17 DESC_D COD_P Análise 1 Análise 2 Análise 3 Análise 4 Programação 1 Programação 2 Programação 3 Programação 4 Operação 1 Operação 2 Operação 3 Operação 4

DESC_P Financ Contab Saude Estoque Financ Contab Saude Estoque Financ Contab Saude Estoque

Vamos agora selecionar as tuplas resultantes que estão devidamente relacionadas que são as que possuem o mesmo valor em número do projeto e número e cuja localização seja ‘SP’. CONSULTA18 = (TRABALHA x PROJETO)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

CONSULTA19 = π t.rg, p.cod_p σ t.cod_p = p.cod_p e p.local = “SP” (CONSULTA18) A operação produto cartesiano não é muito utilizada por não oferecer um resultado otimizado. Veja o item seguinte. Operação Junção A operação junção atua de forma similar à operação produto cartesiano, porém, a tabela resultante conterá apenas as combinações das tuplas que se relacionam de acordo com uma determinada condição de junção. A forma geral da operação junção entre duas tabelas R e S é a seguinte:

R x [condição_de_ junção] S Vamos refazer as consultas 18 e 19. CONSULTA20 = π t.rg, p.cod_p σ t.cod_p = p.cod_p e p.local=“SP” (TRABALHA x PROJETO) CONSULTA19 T.RG P.COD_P 1 1 6 1 9 1 10 1 5 1

O resultado será o mesmo, porém com mais eficiência.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Capítulo 4 Structured Query Language – SQL Data Definition Language - DDL Data Manipulation Language- DML

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL - Structured Query Language SQL é um conjunto de declarações que é utilizado para acessar os dados utilizando gerenciadores de banco de dados. Nem todos os gerenciadores utilizam SQL. SQL não é uma linguagem baseada em procedimentos, pois processa conjuntos de registros, ao invés de um por vez, provendo navegação automática através dos dados, permitindo ao usuário manipular tipos complexos de dados. SQL pode ser utilizada para todas as atividades relativas a um banco de dados podendo ser utilizada pelo administrador de sistemas, pelo DBA, por programadores, sistemas de suporte à tomada de decisões e outros usuários finais. Definição de Dados Utilizando SQL Comando CREATE TABLE O comando CREATE TABLE permite ao usuário criar uma nova tabela (ou relação). Para cada atributo da relação é definido um nome, um tipo, máscara e algumas restrições. Os tipos mais comuns de uma coluna são: char(n): caracteres e strings onde n é o número de caracteres; integer: inteiros float: ponto flutuante; decimal(m,n): onde m é o no. de casas inteiras e n o no. de casas decimais. A nomenclatura destes tipos varia de acordo com o banco de dados adotado. A restrição not null indica que o atributo deve ser obrigatoriamente preenchido; se não for especificado, então o default é que o atributo possa assumir o valor nulo. A forma geral do comando CREATE TABLE então é: CREATE TABLE <nome_tabela> ( <nome_coluna1><tipo_coluna1><NOT NULL>, <nome_colunan><tipo_colunan><NOT NULL>);

Para criar a tabela EMPREGADOS teríamos o seguinte comando: CREATE TABLE EMPREGADO ( rg integer NOT NULL, nome nvarchar(50) NOT NULL, salario money NOT NULL cod_d integer NOT NULL, rg_sup integer NOT NULL, );

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Comando DROP TABLE O comando DROP TABLE permite a exclusão de uma tabela (relação) em um banco de dados. A forma geral para o comando DROP TABLE é: DROP TABLE <nome_tabela>;

Por exemplo, para eliminar a tabela EMPREGADOS faríamos: DROP TABLE EMPREGADO;

Observe que neste caso, a chave da tabela EMPREGADO, (rg) é utilizada como chave estrangeira ou como chave primária composta em diversos tabelas que devem ser devidamente corrigidas. Este processo não é assim tão simples pois, como vemos neste caso, a exclusão da tabela EMPREGADO implica na alteração do projeto físico de diversas tabelas. Isto acaba implicando na construção de uma nova base de dados. Comando ALTER TABLE O comando ALTER TABLE permite que o usuário faça a inclusão de novos atributos em uma tabela. A forma geral para o comando ALTER TABLE é a seguinte: ALTER TABLE <nome_tabela> ADD <nome_coluna> <tipo_coluna>;

No caso do comando ALTER TABLE, a restrição NOT NULL não é permitida pois assim que se insere um novo atributo na tabela, o valor para o mesmo em todas as tuplas da tabela receberão o valor NULL. Manipulando dados utilizando a SQL Selecionando dados - SELECT O comando SELECT permite a seleção de tuplas e atributos em uma ou mais tabelas. A forma básica para o uso do comando SELECT é: SELECT <lista de atributos> FROM <lista de tabelas> [WHERE <condições>];

Por exemplo, para selecionar o nome e o rg dos funcionários que trabalham no departamento número 2 na tabela EMPREGADOS utilizamos o seguinte comando:

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados SELECT FROM WHERE

nome, rg EMPREGADO cod_d = 2;

CONSULTA21= π nome, rg σ intdepto=2 (EMPREGADO) EMPREGADO NOME RG Fernanda 3 Camilo 9 Lucio 10

Também é permitido o uso de condições múltiplas. Veja o exemplo a seguir: SELECT FROM WHERE

nome, rg, salario EMPREGADO depto = 2 AND salario >= 2.500.00;

CONSULTA22 = π nome, rg, salario σ depto=2 e salario >= 2.500,00 (EMPREGADO) EMPREGADO NOME RG Fernanda 3

A operação select-from-where em SQL pode envolver quantas tabelas forem necessárias. Exemplo: selecione o código e o nome do departamento que controla projetos localizados em “MG”; SELECT FROM WHERE AND AND AND

COD_D 1 2

DISTINCT d.cod_d, d.desc_d depto d, projeto p, trabalha t, empregado e p.local = “MG” p.cod_p = t.cod_p t.rg = e.rg e.cod_d = d.cod_d; DEPTO DESC_D Análise Programação

Na expressão SQL acima, d, p, t e e são chamados alias (apelidos) e representam a mesma tabela a qual estão referenciando. Um alias é muito importante quando há redundância nos nomes das colunas de duas ou mais tabelas que estão envolvidas em uma expressão. Ao invés de utilizar o alias, é possível utilizar o nome da tabela, mas isto pode ficar cansativo em consultas muito complexas além do que, impossibilitaria a utilização da mesma tabela mais que uma vez em uma expressão SQL. Considere a seguinte consulta: selecione o rg, nome e nome do supervisor.

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SELECT FROM WHERE

e1.rg, e1.nome, e2.nome empregado e1, empregado e2 e1.rg_sup = e2.rg;

em álgebra relacional: CONSULTA23 =π nome, rg σ e1.rg = e2.rg_sup(EMPREGADO e1 x EMPREGADO e2) RG 1 2 3 4 5 6 7 8 9 10 11

EMPREGADO NOME NOME_SUP Rodrigo Vera Renato Renato Fernanda Fernanda Roberta Vera Flávia Renato Vera Vera Leonardo Vera Carla Renato Camilo Fernanda Lúcio Fernanda Cleôncio Cleôncio

O operador (*) especificado na cláusula select seleciona todos os atributos de uma tabela, enquanto que a exclusão da cláusula where faz com que todas as tuplas de uma tabela sejam selecionadas. SELECT FROM

* empregado;

Diferente de álgebra relacional, a operação SELECT em SQL permite a geração de tuplas duplicadas como resultado de uma expressão. Para evitar isto, devemos utilizar o especificador DISTINCT. Veja a seguir os exemplos. Sem DISTINCT SELECT FROM

cod_d empregado;

EMPREGADO COD_D 1 3 2 1 3 1 1 3 2 2 -

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Com DISTINCT SELECT FROM

distinct cod_d empregado;

EMPREGADO COD_D 1 2 2

Podemos gerar consultas aninhadas em SQL utilizando o especificador IN, que faz uma comparação do especificador WHERE da consulta mais externa com o resultado da consulta mais interna. Considere a consulta a seguir: selecione o nome de todos os funcionários que trabalham em projetos localizados em Rio Claro; SELECT FROM WHERE AND

e.nome, e.rg, e.cod_d empregado e, trabalha t e.rg = t.rg t.cod_p in (SELECT cod_p FROM projeto WHERE local = “SP”);

EMPREGADO NOME RG COD_D Rodrigo 1 1 Flávia 5 3 Vera 6 1 Camilo 9 2 Lúcio 10 2

Para selecionar um conjunto de tuplas de forma ordenada devemos utilizar o comando ORDER BY. Leve em consideração a seguinte consulta: selecione todos os empregados por ordem alfabética: SELECT FROM ORDER BY

nome, rg, cod_d empregado nome;

EMPREGADO Nome RG COD_D Camilo 9 2 Carla 8 3 Cleôncio 11 Fernanda 3 2 Flávia 5 3 Leonardo 7 1 Lúcio 10 2 Renato 2 3 Roberta 4 1 Rodrigo 1 1 Vera 6 1

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Inserções, Atualizações e Exclusões Para elaborar inserções em SQL, utiliza-se o comando INSERT INTO. A forma geral para o comando INSERT INTO é: INSERT INTO <nome da tabela> <(lista de colunas)> VALUES <(lista de valores)>;

Insira na tabela empregados, os seguintes dados: RG NOME 21 Jorge G. 22 João da C.

SALARIO 150,00 200,00

COD_D 3 3

RG_SUP 2 -

INSERT INTO empregado VALUES (21,”Jorge G.”, 150,00, 3, 2); INSERT INTO empregado (rg, nome, cod_d, salario) VALUES (22, ”Joao de C.”, 3, 200,00);

Como no primeiro exemplo, todos os campos foram inseridos e não foi necessário especificar o nome das colunas. Na segunda inserção, o campo rg_sup não foi inserido, então especificou-se as colunas. Outra forma de se elaborar esta inserção seria: INSERT INTO empregado VALUES (22, “João de C.”, 200.00, 3, “”);

Neste caso, utilizou-se os caracteres “” (aspas) para informar que um valor nulo seria inserido nesta coluna. Para se efetuar uma alteração em uma tabela, é utilizado o comando UPDATE. A forma geral do comando UPDATE é: UPDATE <tabela> SET <coluna> = <expressão> WHERE <condição>

Considere a seguinte declaração: atualize o salário de todos os empregados que trabalham no departamento 2 para R$ 3.000,00; UPDATE empregado SET salario = 3.000,00 WHERE cod_d = 2;

Para se eliminar uma tupla de uma tabela, utiliza-se o comando DELETE. A forma geral do comando DELETE é: DELETE FROM <tabela> WHERE <condição>;

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Elimine os registros nos quais o empregado trabalhe no departamento 3 e possua salário <= R$ 200,00; DELETE FROM empregado WHERE salario <= 200,00 AND cod_d = 3;

(estas inserções foram feitas anteriormente) Nos casos de atualização que foram vistos, todas as <condições> podem ser uma consulta utilizando o comando SELECT, onde o comando será aplicado sobre todos os registros que satisfizerem as condições determinadas pelo comando de seleção.

Banco de Dados VENDAS

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL – Passo a passo ERA do Banco de dados Vendas

cod_d desc_d CGC

cod_v

salário VENDEDOR

CLIENTE

end_c

cidade

UF CEP num_p

comissão

PE (0,n)

(0,n)

FAZ

nome_v

(1,1)

PEDIDO

(1,1)

PREENCHE

(0,n)

POSSUI cod_p

qtd

(1,1)

ITENS PEDIDO

(0,n)

REQUISITA

desc_p

unidade

PRODUTO

Figura 44

CLIENTE COD_C

NOME_C

END_C

CIDADE

CEP

720

Ana

Rua Dez 19

Niteroi

25352-131 RJ

111111111111-11 54654

870

Flávio

São Paulo

15546-464 SP

222222222222-22 32132

Curitiba

30000-311 SP

333333333333-33 8979

Belo Horizonte São Paulo

54654-812 MG

444444444444-44

98610-132 SP

555555555555-55 5646

Salvador

12144-568 BA

666666666666-66 8987

Rio de Janeiro São Paulo

23546-876 RJ

777777777777-77 218

45665-455 SP

888888888888-88 21325

Londrina

12345-874 PR

999999999999-99 23121

110 222 830 130 410 20 157 180 260 290 390 234

Av P Vargas 10 Jorge Rua Caiapo 13 Lucia Rua Itabira 123 Pedro Av Paulista 1235 Edmar Rua da Prai s/n Rodolfo Largo da Lapa 27 Elisabeth Av Climério 45 Livio Tv Moraes c/3 Susana Av Baira Mar 200 Renato Rua Mendes 56 Sebastião Rua Meireles 98 Jose Rua da Igreja 14 Paulo Av Rio Branco 1

CPF_CGC

Florianópoli 98511-321 SC s Niteroi 12131-545 RJ

010101010101-01 020202020202-02 21544

São Paulo

32012-112 SP

030303030303-03 184879

Uberaba

54645-477 MG

040404040404-04 189410

Brasília

26448-253 DF

050505050505-05 21213

VENDEDOR COD_V NOME_V

SALARIO

COMISSAO

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados 209

Jose

1.800,00 C

111

Carlos

2.490,00 A

123

João Sinval 2.780,00 C

240

Antonio

900,00

720

Felipe

4.600,00 A

213

Jonas

2.300,00 A

101

João Silva

2.300,00 C

310

Josias

870,00

250

Bonifácio

2.930,00 B

PRODUTO COD_P DESC_P

UNIDADE

25 Queijo

VU 0,97

31 Chocolate Bar

0,87

78 Vinho

2,00

22 Linho

0,11

30 Açúcar

Sac

0,30

53 Linha

1,80

13 Ouro

0,18

45 Madeira

0,25

87 Cano

1,98

77 Papel

Res

1,05

PEDIDO NUM_P

PE COD_C COD_C

121

410

720

209 101

101

720

101

137

720

148

720

101

189

870

213

104

110

101

203

830

250

410

209

143

111

105

180

240

111

260

240

103

260

138

260

108

290

310

119

390

250

127

410

ITENS PEDIDO NUM_P

COD_P

QTD

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados 121

121

101

148

104

203

189

143

105

111

103

138

108

119

137

189

Selecionando informações de uma tabela - SELECT...FROM SELECT desc_p, unidade, VU FROM Produto SELECT nome_c, CGC FROM Cliente SELECT * FROM Vendedor Obs.: o caracter ‘*’ seleciona todas as colunas da tabela.

Impondo condições à seleção das informações - WHERE SELECT

* 91

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

FROM Produto WHERE desc_p = ‘PREGO’ Obs.: quando o tipo de <nome da coluna> for caracter, devemos colocar <valor> entre ‘ ‘ (aspas simples). Devemos lembrar também que, para o <valor>, letras MAIÚSCULAS diferem das minúsculas. Operadores Operadores Relacionais = <> ou != < > <= >=

Igual Diferente Menor que Maior que Menor ou igual Maior ou igual

SELECT FROM WHERE

num_p, cod_p, qtd item_pedido qtd >= 35

SELECT FROM WHERE

nome_c cliente cidade = ‘RIO DE JANEIRO’

Operadores Lógicos AND OR NOT

E Ou Negação SELECT FROM WHERE

desc_p produto unidade = ‘M’ and VU = 1.05

SELECT FROM WHERE AND OR

nome_c, endereco cliente CEP >= ‘30077000’ CEP <= ‘30079000’ cidade = ‘SÃO PAULO’

SELECT FROM WHERE

num_p pedido NOT pe = 15

Operadores EXISTS e NOT EXISTS

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Selecionar pedidos de vendedores que não mais trabalham na empresa e já foram eliminados da tabela vendedores. SELECT FROM WHERE

* pedido p NOT EXISTS (

SELECT FROM WHERE

v.cod_v vendedor v v.cod_v = p.cod_v

)

Operadores BETWEEN e NOT BETWEEN SELECT FROM WHERE

cod_p, desc_p produto VU BETWEEN 0.32 AND 2.00

Operadores IN e NOT IN SELECT FROM WHERE

nome_v vendedor comissao IN (‘A’, ‘B’)

Operadores LIKE e NOT LIKE Obs.: atuam apenas sobre os campos do tipo caracter e pode ser utilizado com os caracteres “%”, na substituição de palavras ou o “_”, substituindo um caracter. SELECT FROM WHERE

cod_p, desc_p produto desc_p LIKE ‘LAPIS%’

SELECT FROM WHERE

cod_p, desc_p produto desc_p LIKE ‘BROCA N_’

SELECT FROM WHERE

cod_p, desc_p produto unidade LIKE ‘K_’

SELECT FROM WHERE

cod_v, nome_v vendedor nome_v NOT LIKE ‘Jo%’

Operadores IS NULL e IS NOT NULL SELECT FROM WHERE

* cliente IE IS NULL

Ordenando as informações selecionados - ORDER BY

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SELECT FROM ORDER BY

nome_v, salario vendedor nome_v

SELECT FROM ORDER BY

nome_c, cidade, UF cliente UF DESC, cidade DESC

SELECT FROM WHERE ORDER BY

desc_p, VU produto unidade = ‘M’ 2 ASC

Obs.: o número 2 refere-se ao segundo atributo da cláusula SELECT Efetuando cálculos com a informação selecionada SELECT FROM WHERE ORDER BY

nome_v, salario * 1.75 + 120 vendedor comissao = ‘C’ nome_v

AS aumento

Utilizando funções sobre conjuntos de informações -

MAX e MIN (Máximo e mínimo) SELECT MIN(salario), MAX(salario) FROM vendedor

SUM (Somatório) SELECT SUM(qtd) FROM item_pedido WHERE cod_p = 78

AVG (Média) SELECT FROM

AVG(salario) vendedor

COUNT SELECT FROM WHERE

COUNT(*) vendedor salario > 2500.00

Eliminando as informações repetidas – DISTINCT SELECT FROM

DISTINCT unidade produto

Agrupando as informações selecionadas – GROUP BY SELECT num_p, COUNT(*) AS Total_Produtos FROM item_pedido GROUP BY num_p Agrupando de forma condicional – HAVING SELECT num_p, COUNT(*) AS Total_Produtos FROM item_pedido 94

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

GROUP BY HAVING

num_p COUNT(*) >= 3

Recuperando informações de várias tabelas – JOIN SELECT c.nome_c, p.cod_c, p.num_p FROM cliente c, pedido p WHERE c.cod_c = p.cod_c Clientes de SP ou RJ, com prazo de entrega superior a 15 dias. SELECT c.nom_c, c.UF, p.PE FROM cliente c, pedido p WHERE UF IN (‘SP’, ‘RJ’) AND p.PE > 15 AND c.cod_c = p.cod_c Utilizando apelidos – ALIAS Substitui o uso dos qualificadores, normalmente o nome da própria tabela, por apelidos. SELECT v.nome_v, p.PE FROM vendedor v, pedido p WHERE v.salario >= 1000 AND p.PE > 15 AND v.cod_v = p.cod_v Juntando várias SELECT FROM WHERE AND AND AND AND AND ORDER BY

tabelas c.nome_c cliente c, pedido p, item_pedido ip, produto pr c.cod_c – p.cod_c p.num_p = ip.num_p ip.cod_p = pr.cod_p p.PE > 15 p.desc_p = ‘QUEIJO’ c.UF = ‘RJ’ c.nome_c

Vendedores que venderam ‘CHOCOLATE’ com quantidade > 10 Kg. SELECT DISTINCT v.nome_v FROM vendedor v, pedido p, item_pedido ip, produto pr WHERE v.cod_v = p.cod_v AND p.num_p = ip.num_p AND ip.cod_p = pr.cod_p AND ip.qtd > 10 AND pr.desc_p = ‘CHOCOLATE’ Quantos clientes fizeram pedidos com o vendedor ‘JOAO’ SELECT COUNT(p.cod_c)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

FROM WHERE AND AND

cliente c, pedido p, vendedor v c.cod_c = p.cod_c p.cod_v = v.cod_v v.nome_v = ‘JOAO’

Quantos clientes do RJ e ES tem pedidos com a vendedora ‘MARIA’ SELECT UF, COUNT(c.nome_c) FROM cliente c, pedido p, vendedor v WHERE v.nome_v = ‘MARIA’ AND UF IN (‘RJ’, ‘ES’) AND v.cod_v = p.cod_v AND p.cod_c = c.cod_c GROUP BY UF Utilizando consultas encadeadas (SubQueries) Produtos que foram pedidos com quantidade superior a 10 SELECT FROM WHERE

desc_p produto produto IN

(SELECT FROM WHERE

cod_p item_pedido qtd > 10)

Vendedores com salário abaixo da média SELECT v.nome_v FROM vendedor v WHERE v.salario < (SELECT FROM Produtos que NÃO SELECT FROM WHERE

AVG(v1.salario) vendedor v1 )

estão presentes em nenhum pedido p.cod_p, p.desc_p produto p NOT EXISTS (SELECT * FROM item_pedido ip WHERE p.cod_p = ip.cod_p

)

Vendedores que só venderam produtos cuja unidade = ‘Gr’ (grama) SELECT DISTINCT v.cod_v, v.nome_v FROM vendedor v WHERE ‘G’ = ALL (SELECT p.unidade FROM produto p, item_pedido ip produto pr WHERE p.num_p = ip.num_p AND ip.cod_p = pr.cod_p AND p.cod_v =v.cod_v )

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Produtos que não SELECT FROM WHERE

estão presentes em nenhum pedido p.cod_p, p.desc_p produto p NOT EXISTS (SELECT * FROM item_pedido ip WHERE ip.cod_p = p.cod_p

Clientes que estão SELECT FROM WHERE

presentes em mais de 3 c.nome_c cliente c EXISTS (SELECT FROM WHERE GROUP BY HAVING

)

pedidos COUNT(*) pedido p p.cod_c = c.cod_c p.cod_c COUNT(*) > 3 )

Adicionando registros INSERT INTO produto VALUES (108,’Kg’,’PARAFUSO’,1.25) Adicionando registros de outra tabela INSERT INTO (cod_v, nome_v, salario, comissao) SELECT * FROM vendedor_old Atualizando um UPDATE SET WHERE

registro produto VU = VU * 1.25 unidade = ‘Kg’

Excluindo um registro DELETE FROM vendedor WHERE comissao IS NULL DELETE FROM WHERE

pedido p NOT EXISTS (SELECT FROM WHERE

cod_v vendedor cod_v = p.cod_v

)

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Como funciona o Produto Cartesiano Tabelas envolvidas

COD_F F1 F2 F3

Funcionário F NOM_F NASC_F Jose 01/12/1980 Maria 15/06/1975 João 26/11/1972

Trabalha T COD_F COD_P F1 P1 F1 P2 F2 P1 F3 P3

Projeto P COD_P DESC_P P1 Projeto 1 P2 Projeto 2 P3 Projeto 3

Produto Cartesiano Produto Cartesiano entre F x T x P) COD_F NOM_F NASC_F

COD_F COD_P COD_P DESC_P

Jose

01/12/1980 F1

Projeto 1

Jose

01/12/1980 F1

Projeto 2

Jose

01/12/1980 F1

Projeto 3

Jose

01/12/1980 F1

Projeto 1

Jose

01/12/1980 F1

Projeto 2

Jose

01/12/1980 F1

Projeto 3

Jose

01/12/1980 F2

Projeto 1

Jose

01/12/1980 F2

Projeto 2

Jose

01/12/1980 F2

Projeto 3

Jose

01/12/1980 F3

Projeto 1

Jose

01/12/1980 F3

Projeto 2

Jose

01/12/1980 F3

Projeto 3

Maria 15/06/1975 F1

Projeto 1

Maria 15/06/1975 F1

Projeto 2

Maria 15/06/1975 F1

Projeto 3

Maria 15/06/1975 F1

Projeto 1

Maria 15/06/1975 F1

Projeto 2

Maria 15/06/1975 F1

Projeto 3

Maria 15/06/1975 F2

Projeto 1

Maria 15/06/1975 F2

Projeto 2

Maria 15/06/1975 F2

Projeto 3

Maria 15/06/1975 F3

Projeto 1

Maria 15/06/1975 F3

Projeto 2

Maria 15/06/1975 F3

Projeto 3

João

26/11/1973 F1

Projeto 1

João

26/11/1973 F1

Projeto 2

João

26/11/1973 F1

Projeto 3

João

26/11/1973 F1

Projeto 1

João

26/11/1973 F1

Projeto 2

João

26/11/1973 F1

Projeto 3

João

26/11/1973 F2

Projeto 1

João

26/11/1973 F2

Projeto 2

João

26/11/1973 F2

Projeto 3

João

26/11/1973 F3

Projeto 1

João

26/11/1973 F3

Projeto 2

João

26/11/1973 F3

Projeto 3

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Problema proposto Desejo saber o nome dos funcionários e a descrição dos projetos nos quais trabalham SELECT FROM WHERE AND NOM_F Jose Jose Maria João

f.nom_f, des_p funcionario f, trabalha t, projeto p f.cod_f = t.cod_f t.cod_p = p.cod_p DESC_P Projeto Projeto Projeto Projeto

1 2 1 2

Como funcionam as funções de agregação (agrupamento) Problema proposto Desejo saber a quantidade de Funcionários e o somatório dos seus salários, por Departamento, exibindo também seu código e descrição. COD_F F1 F2 F3 F4 F5 F6 F7 F9 F10

FUNCIONÁRIO NOM_F SAL_F Jose 5.000,00 Maria 2.500,00 João 1.500,00 Carla 2.700,00 Sergio 6.000,00 Antônio 1.500,00 Vera 2.500,00 Leo 600,00 Edu 1.200,00

COD_D D1 D3 D3 D3 D2 D1 D3 D1 D1

DEPARTAMENTO COD_D DES_D D1 DRH D2 ENG D3 INFO

Um código fonte orientado a procedimentos, geraria um relatório assim... (talvez sem as repetições de códigos e descrições) D1

DRH

1.500,00

DRH

600,00

DRH

1.200,00 3

ENG

3.300,00 5.000,00 6.000,00

11.000,00

INFO

2.500,00

INFO

1.500,00

INFO

2.700,00

INFO

2.500,00 4

9.200,00

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Observamos abaixo, a construção do comando SQL, com funções de agregação e o resultado obtido. SELECT

FROM WHERE GROUP BY ORDER BY COD_D D1 D2 D3

d.cod_d, d.des_d, count(d.doc_d) qtd, sum(f.sal_f) soma Funcionario F, Departamento D d.cod_d = f.cod_d d.cod_d, d.des_d d.cod_d

DES_D DRH ENG INFO

QTD 3 2 4

SOMA 3.300,00 11.000,00 9.200,00

100

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL 1 Com base no Diagrama de Entidade, Relacionamento e Atributos abaixo, responda às questões a seguir. COD_D

DES_D

DEPARTAMENTO

(1,n)

POSSUI (1,1)

COD_F

FUNCIONÁRIO

NOM_F

(1,1)

COD_P DES_P

TRABALHA

(0,n)

PROJETO

SAL_F

Figura 45 1) Derive o modelo conceitual para o modelo lógico

2) Com base no ERA, resolva os SQL propostos abaixo. a) Funcionário (NOM_F) e os projetos nos quais Trabalha (COD_P), ordenado por NOM_F b) Departamento (COD_D) e seus Funcionários (NOM_F), ordenado por Código do Departamento (COD_D) c) Quantidade de projetos nos quais o Funcionário (NOM_F) Trabalha (QTD) d) Funcionário (COD_F, NOM_F) com somatório de QH >= a 50 HORAS e) Departamento (COD_D, DES_D) com custo salarial >= R$ 5.000 f) Funcionário (COD_F, NOM_F) com SAL_F entre R$200 e R$1.000 g) Departamento (COD_D, DES_D) com menos de 3 Funcionários h) Funcionário (COD_F, NOM_F) com o nome iniciado por JOAO i)

Funcionário (NOM_F, SAL_F) e o salário aumentado em 23%

j) Departamento (COD_D, DES_D) que não possui Funcionários

101

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL 1 – Área para respostas

102

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL 2 Com base no Diagrama de Entidade, Relacionamento e Atributos abaixo, responda às questões a seguir. CGC

ESCOLA

RAZÃO

(1,n)

POSSUI (1,1)

MAT NOME SAL

PROFESSOR (1,n)

Requer

LECIONA COD (1,n)

CURSA

É requerida

DISCIPLINAS

(1,n)

CURSA

(1,n)

ALUNO

MAT NOME EMAIL

DES

Figura 46 1) Derive o modelo conceitual para o modelo lógico

2) Com base no ERA, resolva os SQL propostos abaixo. a) ESCOLA (CGC, RAZÃO) e os PROFESSORES (NOME) que nela trabalham b) ALUNO (NOME) e as DISCIPLINAS (DES) que cursa c) ALUNO (NOME) que ainda não cadastrou seu email d) PROFESSOR (NOME) e a exibição de seu salário aumentado em 25% e) DISCIPLINAS (DES) e seus PRE-REQUISITOS (DES) f) Quantidade (QTD) de PROFESSORES na ESCOLA (RAZÃO) g) PROFESSOR (NOME, SAL) que ganham mais de R$ 1.000,00, ordenado, de forma descendente, pelo atributo SAL h) PROFESSOR (NOME) e as DISCIPLINAS (DES) que leciona i)

ALUNOS (NOME) que NÃO estão cursando nenhuma disciplina

j) PROFESSORES (NOME) que lecionam DISCIPLINAS (DES) que contenham na sua descrição, a palavra “DADOS” k) ALUNOS (NOME, NASC) nascidos entre “01/01/75” e “31/12/80”

103

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL - Exercícios de SQL 2 – Área para respostas

104

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

CapĂtulo 5 Stored Procedures Triggers e Views

105

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Views - Visões Uma visão (VIEW) é uma forma alternativa de acessar os dados contidos em uma ou mais tabelas. Para definir uma visão, usa-se o comando SELECT que faz uma consulta sobre as tabelas. A visão aparece depois como se fosse uma tabela. Visões têm as seguintes vantagens: -

Pode restringir quais as colunas da tabela que podem ser acessadas (para leitura ou para modificação), o que é útil no caso de controle de acesso, como veremos mais tarde. Uma consulta SELECT que é usada muito freqüentemente pode ser criada como visão. Com isso, a cada vez que ela é necessária, basta selecionar dados da visão. Podem conter valores calculados ou valores de resumo, o que simplifica a operação. Pode ser usada para exportar dados para outras aplicações.

Criando uma visão com o Enterprise Manager Para criar uma visão com o Enterprise Manager, expanda um grupo de servidores, então o servidor em que está o banco de dados onde será criada a visão. Clique com o botão direito em Views. Aparece uma tela quase idêntica a do Query Designer.

Figura ?? Na janela superior (logo abaixo dos ícones, chamada de seção do diagrama, clique com o botão direito, e selecione Add Table. Na guia Tables (ou Views, caso você já tenha criado alguma visão e queira que ela faça parte desta que está sendo criada), selecione a tabela (ou visão) a ser adicionada, e então clique Add. Caso você queira remover

106

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

alguma tabela adicionada ao diagrama, clique na mesma com o botão direito e selecione Remove. Repita tantas vezes quantas forem as tabelas (ou visões) a serem adicionadas à nova visão. Clique em Close quando tiver escolhido todas as tabelas (ou visões) desejadas. Na caixa Column da seção da grade (parte da janela logo abaixo de onde estão as tabelas adicionadas), selecione as colunas a serem referenciadas na visão. Note que caso haja mais de uma tabela na seção do diagrama, quando você for selecionar a coluna na seção da grade, aparecerá o nome completo da coluna (tabela. coluna). Marque a caixa Output se a coluna deve ser mostrada no resultado da visão. Note que você também pode escolher as colunas que farão parte da visão, selecionando-as na representação gráfica da tabela, mas as colunas selecionadas dessa maneira farão parte da saída por padrão. Para que não apareçam na saída, desmarque a caixa Output. Para agrupar por alguma coluna, clique com o botão direito na coluna (na seção da grade) e selecione Group By. Na coluna Criteria, digite o critério especificando quais linhas retornar; isso determina a cláusula WHERE. Se GROUP BY for especificado, isso determina a cláusula HAVING. Na coluna Or... entre com qualquer critério adicional para especificar quais linhas a serem retornadas. Clique com o botão direito em qualquer lugar da seção da grade, e então selecione Properties. -

"Output all columns" mostrará todas as linhas da visão no resultado;

"DISTINCT values" filtra os valores duplicados no resultado;

"Encrypt view" criptografa a definição da visão;

Opcionalmente, em "Top", entre com o número de linhas a serem retornadas no resultado. Digite a palavra PERCENT depois do número para mostrar uma porcentagem das linhas, no resultado.

Clique com o botão direito em qualquer lugar da seção do diagrama; clique então em Run (para ver o resultado) ou Save (para salvar a visão). Note que na seção SQL, aparece o código SQL do SELECT envolvido na criação da visão. Criando uma visão com comandos SQL Para criar uma visão através de SQL, use o comando CREATE VIEW. Esse comando tem a seguinte sintaxe:

107

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

CREATE VIEW nome_visão [(coluna [,...n])] [WITH ENCRYPTION] AS declaração_SELECT [WITH CHECK OPTION]

nome_visão é o nome a ser dados à visão coluna é o nome a ser usado para uma coluna em uma visão. Nomear uma coluna em CREATE VIEW só é necessário quando uma coluna é obtida por uma expressão aritmética, uma função, ou uma constante, ou quando duas ou mais colunas poderiam ter o mesmo nome (freqüentemente por causa de uma junção), ou quando a coluna em uma visão recebe um nome diferente do nome da coluna da qual se originou. Os nomes de colunas também podem ser atribuídos no comando SELECT. Caso você queira nomear mais de uma coluna, entre com o nome de cada uma separado por vírgulas. WITH ENCRYPTION: criptografa as entradas na tabela syscomments que contém o texto do comando CREATE VIEW. WITH CHECK OPTION: força todas as modificações de dados executadas na visão a aderirem aos critérios definidos na declaração_SELECT. Quando uma coluna é modificada através de uma visão, WITH CHECK OPTION garante que os dados permaneçam visíveis através da visão depois que as modificações forem efetivadas. Vamos criar uma visão no banco de dados Exemplo, usando as tabelas 'Produto', 'Fornecedor' e 'ProdutoFornecedor'. Essa visão vai mostrar o nome do fornecedor e o nome do produto. Crie-a digitando o texto abaixo no Query Analyzer: create view VisaoFornecProduto as select f.Nome NomeFornecedor, p.Nome NomeProduto from Fornecedor f inner join ProdutoFornecedor pf on f.CodFornecedor = pf.CodFornecedor inner join Produto p on pf.CodProduto = p.CodProduto

Para criar uma visão você deve estar posicionado no banco de dados onde a visão será criada ou então especificá-lo através da cláusula USES. Ao criar uma visão, o texto do comando acima é armazenado na tabela syscomments. Agora, para testar, digite: select * from VisaoFornecProduto

O resultado terá as colunas 'NomeFornecedor' e 'NomeProduto', mostrando os dados relacionados entre elas. Você pode também criar uma visão que calcula valores usando colunas das tabelas, ou usando GROUP BY e funções agregadas, na declaração SELECT.

108

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Alterando ou excluindo uma visão Para alterar uma visão, você pode usar tanto o Enterprise Manager quanto o comando SQL, ALTER VIEW. Para alterá-la com o Enterprise Manager, selecione a visão que se quer alterar, clique na mesma com o botão direito e selecione Design View. Aparecerá a mesma janela vista na criação da visão com o Enterprise Manager, e aí você pode fazer as alterações que julgar necessárias à visão, salvar as alterações, executar a visão, etc.. Tudo da mesma forma que se você estivesse criando uma nova visão. O comando SQL ALTER VIEW tem a seguinte sintaxe: ALTER VIEW nome_visão [(coluna [,...n])] [WITH ENCRYPTION] AS declaração_select [WITH CHECK OPTION]

Todas as considerações feitas a respeito do comando CREATE VIEW se aplicam aqui. Caso você não se lembre do comando usado na criação da visão (o comando CREATE VIEW) , você pode obtê-lo usando o procedimento sp_helptext, da forma: sp_helptext VisaoFornecProduto

Este texto é consultado na tabela syscomments. Algumas linhas podem aparecer quebradas no resultado. É importante considerar que a alteração de uma visão não afeta os procedimentos armazenados ou gatilhos dependentes da mesma e não altera as permissões atribuídas à mesma Modificando dados através de uma visão Você pode executar um comando UPDATE em uma visão. Se ela foi baseada em uma única tabela, isso não provoca grandes problemas. Se a opção WITH CHECK OPTION acima for usada, as atualizações devem satisfazer as condições da cláusula WHERE usada na criação da visão. Inserções com INSERT também podem ser feitas. Se a visão é baseada em duas ou mais tabelas, a atualização só é possível se o comando altera dados de apenas uma tabela. Colunas calculadas não podem ser alteradas. Se foram usadas funções de agregação, também não é possível modificar os dados através da visão. Na inserção, se uma coluna de uma tabela subjacente não permite nulos (NOT NULL), não é possível inserir linhas na visão, pois isso deixaria a coluna sem valor.

109

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Stored Procedure - Procedimentos Armazenados Um procedimento armazenado (STORED PROCEDURE) é um conjunto de comandos SQL que são compilados e armazenados no servidor que podem ser chamados a partir de um comando SQL qualquer. Em versões anteriores do SQL Server, os procedimentos armazenados eram uma maneira de pré-compilar parcialmente um plano de execução. Quando da criação do procedimento armazenado, um plano de execução parcialmente compilado era armazenado em uma tabela de sistema. A execução de um procedimento armazenado era mais eficiente do que a execução de um comando SQL, porque o SQL Server não precisava compilar um plano de execução completamente, apenas tinha que terminar a otimização do plano armazenado para o procedimento. Além disso, o plano de execução completamente compilado para o procedimento armazenado era mantido na cache de procedimentos do SQL Server, significando que execuções posteriores do procedimento armazenado poderiam usar o plano de execução pré compilado. A versão 7.0 do SQL Server apresenta várias mudanças no processamento de comandos que estendem muitos dos benefícios de desempenho dos procedimentos armazenados para todos os comandos SQL. O SQL Server 7.0 não salva um plano parcialmente compilado para os procedimentos quando os mesmos são criados. Um procedimento armazenado é compilado em tempo de execução como qualquer outro comando Transact-SQL. O SQL Server 7.0 mantém planos de execução para todos os comandos SQL na cache de procedimentos, não apenas planos de execução de procedimentos armazenados. Ele então usa um algoritmo eficiente para comparação de novos comandos Transact-SQL com os comandos TransactSQL de planos de execução existentes. Se o SQL Server 7.0 determinar que um novo comando Transact-SQL é o mesmo que um comando Transact-SQL de um plano de execução existente, ele reutiliza o plano. Isso reduz o ganho relativo de desempenho, na pré compilação de procedimentos armazenados, já que estende a reutilização de planos de execução para todos os comandos SQL. A vantagem de usar procedimentos armazenados encapsular rotinas de uso freqüente no próprio disponíveis para todas as aplicações. Parte da lógica armazenada no próprio banco de dados, em vez de vezes em cada aplicação.

é que eles podem servidor, e estarão do sistema pode ser ser codificada várias

Criando procedimentos armazenados Para criar um procedimento, use o comando CREATE PROCEDURE. Por exemplo, o procedimento abaixo recebe um parâmetro (@nome) e mostra todos os clientes cujo nome contenha o nome informado:

110

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

create procedure BuscaCliente @nomeBusca varchar(50) as select CodCliente, Nome from Cliente where Nome like '%' + @nomeBusca + '%'

Note que os parâmetros são sempre declarados com @, logo após o nome do procedimento. Um procedimento pode ter zero ou mais parâmetros. Declara-se o nome do procedimento, e a seguir o tipo de dados do parâmetro. Nota: ao invés de CREATE PROCEDURE, pode-se utilizar CREATE PROC, com o mesmo efeito. Dentro do procedimento pode haver vários comandos SELECT e o resultado desses comandos será o resultado do procedimento. O corpo do procedimento começa com a palavra AS e vai até o final do procedimento. Não se pode usar os comandos SET SHOWPLAN_TEXT, e SET SHOWPLAN_ALL dentro de um procedimento armazenado, pois os mesmos devem ser os únicos comandos de um lote (BATCH). Dentro de um procedimento, nomes de objetos usados em alguns comandos devem ser qualificados com o nome do proprietário do objeto, se outros usuários utilizarão o procedimento armazenado. Os comandos são: ALTER TABLE DROP TABLE TRUNCATE TABLE Todos os comandos

CREATE INDEX DROP INDEX UPDATE STATISTICS DBCC

Executando procedimentos armazenados Para executar um procedimento, utiliza-se o comando EXEC (ou EXECUTE). A palavra "EXEC" pode ser omitida se a chamada de procedimento for o primeiro comando em um script ou vier logo após um marcador de fim de lote (a palavra "GO"). Por exemplo, execute o procedimento anterior da seguinte forma: BuscaCliente 'an'

O resultado será as linhas da tabela Cliente onde o valor de Nome contém 'an' (se existirem tais linhas). Ao executar um procedimento, você pode informar explicitamente o nome de cada parâmetro, por exemplo: BuscaCliente @nomeBusca = 'an'

Isso permite passar os parâmetros (se mais de um) fora da ordem em que eles foram definidos no procedimento.

111

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

EXEC também pode executar um procedimento em outro servidor. Para isso, a sintaxe básica é: EXEC nome_servidor.nome_banco_de_dados..nome_procedimento

Comandos para uso em procedimentos armazenados Você pode declarar uma variável em um procedimento e usá-la para guardar valores. Por exemplo, exclua o procedimento anterior e crie-o novamente como abaixo: drop procedure BuscaCliente go create procedure BuscaCliente @nomeBusca varchar(50) as declare @contagem int, @mensagem char(100) select CodCliente, Nome from Cliente where Nome like '%' + @nomeBusca + '%'

conta quantas linhas foram encontradas select @contagem = count(*) from Cliente where Nome like '%' + @nomeBusca + '%' if @contagem = 0 begin select @mensagem = 'Nenhum cliente contém "'+@nomeBusca+'"' print @mensagem print "" end O comando DECLARE declara variáveis, que são sempre introduzidas pelo caractere @. No caso, @contagem é uma variável do tipo int e @mensagem do tipo char(100). Note que quando você usa um comando SELECT, o resultado pode ser colocado numa variável, como @contagem acima. Esse resultado não aparece no resultado do SELECT. Essa é também a única forma de alterar uma variável (você não pode escrever '@variável = valor' diretamente). O comando IF verifica uma condição e executa um comando caso a condição seja verdadeira. Se acompanhado da cláusula ELSE, executa um outro comando caso a condição seja falsa. O comando PRINT usado acima é geralmente usado para mostrar mensagens, que aparecem quando você chama o procedimento interativamente. Os comandos BEGIN e END são usados para delimitar uma lista de comandos, que passa a ser tratada como um comando único. No caso acima, eles são necessários para poder executar três comandos dentro do IF (o SELECT e os dois PRINT).

112

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Criando procedimentos armazenados com o Enterprise Manager

Também é possível a criação de procedimentos armazenados através do Enterprise Manger. Para isso, deve-se expandir um grupo de servidores, um servidor, e o banco de dados onde o procedimento armazenado será criado. Clique então com o botão direito em Stored Procedures, e selecione New Stored Procedure. Aparece uma tela como abaixo Nessa tela você deve dar o nome que desejar ao procedimento, substituindo o texto em preto [PROCEDURE NAME] pelo nome que você quer dar ao procedimento armazenado sendo criado. Logo depois do AS, você deve entrar com o código do procedimento armazenado, conforme descrito acima. Você pode após entrar com o código desejado, clicar no botão Check Syntax, que verificará se há erros de sintaxe nas declarações SQL. Quando tiver terminado de entrar com o código do procedimento, basta clicar em OK que o mesmo será criado. Triggers - Gatilhos Um gatilho (TRIGGER) é um tipo de procedimento armazenado, que é executado automaticamente quando ocorre algum tipo de alteração em uma tabela. Gatilhos "disparam" quando ocorre uma operação INSERT, UPDATE ou DELETE sobre um determinada tabela.

113

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Geralmente gatilhos são usados para reforçar restrições de integridade que não podem ser tratadas pelos recursos mais simples, como regras, DEFAULTS, restrições, a opção NOT NULL etc. Deve-se usar DEFAULTS e restrições quando eles fornecem toda a funcionalidade necessária. Um gatilho também pode ser usado para calcular e armazenar valores automaticamente em outra tabela, como veremos a seguir. Exemplo de gatilhos Para utilizar gatilhos, vamos criar antes algumas tabelas que serão usadas como exemplo. A tabela "NotaFiscal" conterá os cabeçalhos de notas fiscais. A tabela "ItemNotaFiscal" irá conter itens de nota fiscal relacionados com as notas fiscais. Execute o script abaixo para criar as tabelas: create table NotaFiscal (NumeroNota numeric(10) primary key, ValorTotal numeric(10,2) default (0) ) GO create table ItemNotaFiscal (NumeroNota numeric(10) foreign key references NotaFiscal, CodProduto int foreign key references Produto, Quantidade int not null check (Quantidade > 0), primary key (NumeroNota,CodProduto) )

Vamos usar gatilhos para duas finalidades: primeiro, quando for excluída uma nota fiscal, todos os seus itens serão excluídos automaticamente. Depois, quando for incluído um item, a coluna 'ValorTotal' será atualizada, na tabela 'NotaFiscal'. Criando gatilhos Gatilhos são sempre criados vinculados a uma determinada tabela. Se a tabela for excluída, todos os gatilhos dela são excluídos como conseqüência. Ao criar um gatilho, você pode especificar qual(is) a(s) operação(ões) em que ele será acionado: INSERT, UPDATE ou DELETE. Gatilhos para inserção Quando é feita a inclusão de uma ou mais linhas na tabela, o SQL Server cria uma tabela virtual chamada inserted, que contém as linhas que serão incluídas (mas ainda não foram). Essa tabela tem a mesma estrutura da tabela principal e pode-se consultar dados nessa tabela com o comando SELECT, da mesma forma que uma tabela normal.

114

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Vamos criar um gatilho, chamado InclusaoItemNota, que será ativado por uma operação INSERT na tabela ItemNotaFiscal. Primeiro ele vai verificar se os valores sendo inseridos possuem uma NotaFiscal relacionada ou não. Para isso, digite o seguinte comando: create trigger InclusaoItemNota on ItemNotaFiscal for insert as if not exists ( select * from inserted, NotaFiscal where inserted.NumeroNota = NotaFiscal.NumeroNota) raiserror('Esse item não contém um número de nota válido') update NotaFiscal set ValorTotal = ValorTotal + (select i.Quantidade * p.Preço from Produto p, inserted i where p.CodProduto = i.CodProduto) where NumeroNota = (select NumeroNota from inserted)

Primeiro o gatilho usa as tabelas inserted e NotaFiscal para consultar se existe o valor de NumeroNota na tabela. Caso não exista, o comando RAISERROR gera um erro de execução, com uma mensagem que será retornada para a aplicação. Esse comando efetivamente cancela o comando INSERT que estiver sendo executado. Depois verifica a quantidade que está sendo inserida (inserted.Quantidade) e multiplica pelo preço do produto (Produto.Preço). Esse preço é obtido na tabela de produtos, usando como valor de pesquisa o código do produto inserido (inserted.CodProduto). Ele atualiza a nota fiscal relacionada com o item que está sendo inserido (para isso verifica where NumeroNota=(select NumeroNota from inserted)). Gatilhos para exclusão Na exclusão, as linhas da tabela são removidas e colocadas na tabela virtual deleted, que tem a mesma estrutura da tabela principal. Um gatilho para exclusão pode consultar deleted para saber quais as linhas excluídas. Vamos criar um gatilho, na tabela NotaFiscal para, quando a nota fiscal for excluída, todos os seus itens de nota relacionados, na tabela ItemNotaFiscal, sejam excluídos em cascata. Execute o seguinte: create trigger ExclusaoNota on NotaFiscal for delete as -- excluir todos os itens relacionados -- (mesmo NumeroNota que deleted) delete from ItemNotaFiscal where NumeroNota in (select NumeroNota from deleted)

115

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Gatilhos para atualização As tabelas inserted e deleted, como já vimos, são tabelas virtuais que podem ser usadas dentro de um gatilho. A primeira contém os dados que estão sendo inseridos na tabela real e a segunda contém os dados antigos, que estão sendo incluídos. Num gatilho de atualização (FOR UPDATE), essas duas tabelas também estão disponíveis. No caso, deleted permite acessar os dados como eram antes da modificação e inserted permite acessar os dados depois da atualização. Podemos então considerar uma atualização como uma exclusão seguida de uma inserção (excluem-se valores antigos e inserem-se valores novos). Por exemplo, ao mudar a Quantidade em um ItemNotaFiscal, o total da nota deve ser recalculado. Para isso, é preciso levar em conta a diferença entre a quantidade antiga (deleted.Quantidade) e a nova (inserted.Quantidade). Vamos criar um gatilho em ItemNotaFiscal que faz isso: create trigger AlteracaoItemNota on ItemNotaFiscal for update as if update(Quantidade) or update(CodProduto) begin update NotaFiscal set ValorTotal = ValorTotal + (select p.Preço * (i.Quantidade - d.Quantidade) from Produto p inner join inserted i on p.CodProduto = i.CodProduto inner join deleted d on i.CodProduto = d.CodProduto and i.NumeroNota = d.NumeroNota) end

Note acima o uso de 'if update(nome_da_coluna)'. Dentro de um gatilho de atualização, isso permite descobrir se a coluna está sendo alterada ou não. Isso para evitar trabalho desnecessário se não estiver sendo modificada uma dessas colunas. Criando gatilhos para múltiplas ações Um gatilho pode ser criado para uma tabela para múltiplas operações nessa tabela. Por exemplo, para criar um gatilho usado em INSERT, UPDATE e DELETE, usa-se uma sintaxe, como: create trigger nome_do_gatilho on nome_da_tabela for INSERT, UPDATE, DELETE as texto_do_gatilho

Outros comandos

116

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Em gatilhos, assim como em procedimentos armazenados, é possível declarar variáveis e usar comandos como IF, BEGIN..END etc. Alguns comandos não são permitidos dentro de um gatilho. Estes são: ALTER DATABASE ALTER TRIGGER CREATE DEFAULT CREATE RULE CREATE TRIGGER DISK INIT DROP DEFAULT DROP RULE DROP VIEW LOAD LOG REVOKE UPDATE STATISTICS

ALTER PROCEDURE ALTER TABLE ALTER VIEW CREATE DATABASE CREATE INDEX CREATE PROCEDURE CREATE SCHEMA CREATE TABLE CREATE VIEW DENY DISK RESIZE DROP DATABASE DROP INDEX DROP PROCEDURE DROP TABLE DROP TRIGGER GRANT LOAD DATABASE RESTORE DATABASE RESTORE LOG RECONFIGURE TRUNCATE TABLE

117

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Capítulo 6 Criação de um Banco de Dados ORACLE SQL SERVER ACCESS

118

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

Oracle SQL SERVER Access

119

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Trabalhos

Trabalhos - Acompanhamento de Processos - Solicitação de Serviços - Sistema de Estoque

120

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados Mini-mundo do Sistema de Acompanhamento de Processos Elabore o ERA e a Derivação das tabelas O departamento jurídico da empresa DataVenia está sendo informatizado. Verificou-se então a necessidade de controlar e acompanhar o andamento dos processos sob responsabilidade deste departamento. As informações básicas de um PROCESSO, extraídas da ficha ACOMPANHAMENTO DE PROCESSOS, são as seguintes: número do processo, se a DataVenia é Ré ou Autora (R ou A), nome da comarca, nome da vara de execução, código do tipo da ação, uf da filial (onde o processo ocorre), número do processo de origem, status do andamento, valor estimado, data início e data fim. A empresa DataVenia pode ser acionada ou estar acionando uma pessoa física/jurídica, que são chamadas de PARTE. As PARTES possuem as seguintes informações: código, nome, endereço, telefone, física/jurídica (F ou J), email. Na parte de trás da ficha, reservada para anotações de acompanhamento do processo, constam as seguintes informações: data do acompanhamento, que é uma anotação do fato ocorrido ou compromisso futuro, uma breve observação e a data de alerta, quando for o caso, para que toda a documentação possa ser preparada para o dia agendado (compromisso futuro). Em cada filial da empresa, são registradas as seguintes informações: sigla da uf, nome do gerente, telefone e email. Cada Tipo de Ação possui as seguintes informações: código do tipo da ação e descrição. Observações: a) somente são cadastradas informações necessárias; b) um PROCESSO pode ter várias PARTES envolvidas e a PARTE pode se envolver em vários processos; c) os status possíveis de um PROCESSO são fixos e nunca mudarão cujos códigos previstos são os seguinte códigos: AP-Arquivado e Procedente, AI-Arquivado e Improcedente, AA-Arquivado com Acordo, EAEm andamento, AC-Ação cancelada d) os TIPOS DE AÇÃO serão determinados e cadastrados pelos usuários; São requisitos de informação: a) b) c) d) e)

Partes envolvidas em quais processos; Acompanhamentos de um processo; Valor total estimado por UF; processos por tipo de ação; filiais com mais processos AP onde somos autores (A/R igual a A);

121

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados Mini-mundo do Sistema de Solicitações de Serviços Elabore o ERA e a Derivação das tabelas A empresa de consultoria CPEDE EUFAÇO, é responsável pelo desenvolvimento e manutenção de sistemas e o controle de todo o hardware de vários clientes. Os clientes são vistos como “usuários emissor”. Com o crescimento da quantidade de solicitações para estes serviços, estava sendo necessária a criação de um sistema que controlasse tais pedidos. As informações básicas de uma SOLICITAÇÃO, extraídas do formulário que atualmente é utilizado, são as seguintes: número da solicitação, data e hora de emissão, código do serviço solicitado, especificação do serviço, sistema ao qual a solicitação está associada, data e hora da recepção da solicitação, usuário emissor, usuário receptor, usuário que executará o serviço, data e hora do inicio do serviço, data e hora do final do serviço, solução dada à solicitação, algum tipo de observação, tempo utilizado para a conclusão do serviço e os STATUS, situações nas quais a solicitação pode ser encontrada. Os possíveis STATUS (situações) que as solicitações podem se encontar são os seguintes: N – Não foi recepcionada pela empresa (como um eMail não lido); R – Recepcionada pela empresa (usuário receptor) ; A – Alocada a um profissional (usuário executor); E – Encerrada pelo profissional, o trabalho foi totalmente executado; P - Encerrada pelo profissional, o trabalho foi parcialmente executado; C – O usuário solicitante cancelou a solicitação; F – O usuário solicitante concordou que tudo o que foi solicitado foi executado. Periodicamente, são gerados relatórios ou consultas exibidas no terminal. Observações: a) somente são cadastradas informações necessárias; b) uma SOLICITAÇÃO está alocada a somente um SISTEMA; c) uma SOLICITAÇÃO normalmente é emitida, recebida e executada por usuários distintos e eventualmente recebida e executada pelos mesmos usuários ; d) os USUÁRIOS, os SISTEMAS, os TIPOS DE SERVIÇO e TIPOS DE STATUS, são criados e mantidos por funcionários da empresa CPEDE EUFAÇO e) Solicitações anteriores a três anos e com a situação F, podem ser copiadas para uma tabela de expurgo e devem ser eliminadas. São requisitos de informação: a) Nome do Executor e as solicitações em aberto, por executor; b) Nome do Sistema e a quantidade de solicitações em aberto, por Sistema; c) Nome do Executor e quantidade de solicitações em aberto, por Executor; d) Nome do Executor, a quantidade de solicitações encerradas e o tempo total consumido, por Executor; e) Expurgar solicitações Canceladas, Fechadas ou Pendentes com três anos, ou mais, de conclusão;

122

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados Mini-mundo do Sistema de Estoque Elabore o ERA e a Derivação das tabelas A empresa de CPRICIZA EUTIDÔ, deseja controlar as requisições de materiais feitas pelos seus diversos departamentos, por intermédio de um sistema informatizado permitindo aos usuários fazerem suas solicitação diretamente nas estações de trabalho. Foi elaborado um estudo, baseado em reuniões com o Departamento de Informática e deste trabalho, resultou a seguinte lista de premissas: -

Deverão ser criadas CATEGORIAS e SUBCATEGORIAS, ambas contendo código e descrição. É nas SUBCATEGORIAS que serão enquadrados os MATERIAIS; Foi criada uma padronização para os tipos de UNIDADE DE ARMAZENAGEM com código, descrição e sigla. Por exemplo: 001 - RESMA DE PAPEL XEROX – RES; Cada MATERIAL possui as seguintes características: código, descrição, estoque mínimo, ponto de compra, saldo atual e valor unitário; As REQUISIÇÕES serão vinculadas aos DEPARTAMENTOS, código e descrição; Uma REQUISIÇÃO, pertence a um DEPARTAMENTO, possui um número, data de emissão e data de atendimento; Cada REQUISIÇÃO pede no mínimo um ITEM, com a quantidade solicitada; Os item solicitados estão associados a MATERIAIS preexistentes no sistema, ou seja, o usuário não solicita materiais que NÃO estejam cadastrados; Para efetivar a baixa do estoque, cada item solicitado e atendido, gera uma MOVIMENTAÇÃO com a quantidade atendida, que pode ser diferente da quantidade solicitada e a data na qual foi efetivado o atendimento. Existem ITENS que podem não ser atendidos; Cada MOVIMENTO possui um TIPO DE MOVIMENTO, padronizado e cadastrado previamente com código e descrição. Por exemplo: 01-Entrada, 02-Saída, 03-Acerto a mais, 04-Acerto a menos etc... Cada MOVIMENTO, pode ter uma JUSTIFICATIVA, pois a quantidade atendida, pode estar completamente fora dos padrões e isto deve ser descrito e armazenado, para futuras auditorias.

Serão necessários, inicialmente, os seguintes relatórios: a) Lista de REQUISIÇÕES não atendidas e seus ITENS, para que o funcionário do setor possa efetuar o atendimento; b) Relatório contendo a quantidade de MATERIAIS movimentados, por DEPARTAMENTO; c) Relatório com os MATERIAS que precisam ser repostos. Este materiais são identificados quando o saldo atual fica menor ou igual ao ponto de compra; d) MATERIAIS que estão abaixo do estoque mínimo; e) DEPARTAMENTOS, a quantidade de itens consumidos e a soma de suas quantidades em um determinado período de atendimento

123

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Bibliografia recomendada

DATE, C. J., Introdução a Sistema de Bancos de Dados, Tradução da 7ª

edição americana, Editora Campus, 2000 ELMASRI, R. e NEVATHE S.B., Fundamentals of Database System, LTC 2000

SILBERSCHATZ, A., KORTH, H. F. e SUDARSHAN, S., Sistemas de

Bancos de Dados, Editora Makron Books, 1999 HEUSER, C.A, Projeto de Banco de Dados, Sagra Luzatto, 1998 MELO, Rubens N., Bancos de Dados em Aplicações Cliente/Servidor, IBPI, Infobook, 1997 KROENKE, D.M, Bancos de Dados: Fundamentos, Projetos e Implementação, 6ª edição, Editora LTC – 1999 DATE, C. J., Guia para o padrão SQL, Editora Campus, 1999 RAMALHO, J.A., SQL: A Linguagem dos Bancos de Dados, Editora Berkeley, 1999 ERWin – Logic Works – Platinum Technology Microsoft SQL Server – Books On Line Bibliografia complementar

BARBIERI, Carlos – Modelagem e Administração de Dados – Ed. Infobook, 1994 COOD, E.F.; The Relational Model for Databases Systems,. AddisonWesley FURTADO, Antonio Luiz; Organização de Bases de Dados, Campus, 1993 HACKATHORN, Richard D.; Conectividade de Banco de Dados, Infobook, 1993 MACHADO, Felipe Nery Rodrigues, Projeto de Banco de Dados, Érica, 1998 SETZEK, Valdemar Waingort; Banco de Dados, E. Blücher, 1989 ORFALI, HARKEY, D. & Edwards J.;The Essential Client/Server Survival Guide, Wiley Computer Publishing, 1996 RAMAKRISHNAN, R.;Database Managemente Systems, McGraw-Hill, 1998 MELO, Rubens N.;Bancos de Dados em Aplicações Cliente/Servidor O’NEIL, P. e O’NEIL, E., Database: Principles, Programming and Performance, Editora Morgen Kaufmann Publishers, 2ª Edição, 2000 INMON, W.H.;Como Construir o Data Warehouse, Campus., 1997 System Architect, Data Architetch – Popkin Software & Systems Inc.

124

Universidade Veiga de Almeida Instituto Superior PolitĂŠcnico Sistemas de Gerenciamento de Banco de Dados

Respostas -

Mini mundos Engenharia Reversa ExercĂcios de SQL Trabalhos

125

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 1 preço

data COMPRA

(0,n)

nome

endereço bairro

CPF IMÓVEL

(1,1)

PROPRIETÁRIO

endereço

descrição Preço mínimo (0,1)

(0,n)

VENDA

Telefone (0,1)

Data

Preço

Resposta do Mini mundo 2 (0,n)

DEFEITUOSO

série

(0,1)

data

nome endereço

modelo

TROCA

APARELHO

CLIENTE

marca (0,1) SUBSTITUTO

(0,1)

defeito

(0,n)

COMPRA

Data

126

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 3

número

nome

assentos (4,10)

GRAÇOM

RESPONSABILIDADE

avaliação (1,n)

(1,1)

MESA

data (1,n)

ATENDIMENTO (1,n)

hora_ini

hora_fim número nome preço

PRATO

Resposta do Mini mundo 4 matricula EMPREGADO (T,S)

ENFERMEIRA

MÉDICO

(0,n)

ATENDIMENTO

especialidade

cargo

(1,3)

RESPONSABILIDADE VÍNCULO (1,n)

(0,n)

seguro social

nome PESSOA

nascimento

(1,n)

inicio INTERNAÇÃO

fim (1,1)

endereço

código

(1,n)

HOSPITAL

nome

127

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 5 (vale um ponto)

código CLIENTE

nome (1,n)

dt/h saldo DETEM

código (1,n)

desc

AÇÃO

(0,n) EMITE (1,1)

ORDEM DE VENDA

preço

qtd

(1,n)

NEGOCIA

qtd

seq

lote

(1,n)

CORRETORA

CGC nome

128

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 6 CGC nome endereço

codigo descrição

REMÉDIOS

HOSPITAL

(1,n)

(0,n)

telefone (1,3)

ATENDIMENTO

(0,n)

nome

codigo ESPECIALISTA

(1,n)

h_inicio

(1,n)

MÉDICO

CONSULTA

data

email telefone celular bip (1,3)

ESPECIALIDADE

codigo

RECEITUÁRIO

(1,4)

status

inicio

h_fim (1,n)

endereço

codigo CLIENTE

CPF nome

descricao

Resposta do Mini mundo 7

cpf/cgc nome email

COMPRADOR

qtd

(1,n)

data

COMPRA É composta (1,n)

(0,n) COMPOSIÇÃO

codigo

PEÇA

saldo (0,n) É componente

(1,n)

status

minimo

POSSUI (1,1)

VALORES

data

preço

129

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 8

agrupa

numero

(0,n)

CONTA CONTÁBIL

AGRUPAMENTO

descrição

(1,1) (1,n)

agrupada

POSUI

seq

(1,1)

codigo

data (1,1)

MOVIMENTO

(1,n)

VINCULADO

CENTRO DE CUSTO

valor

descrição

Resposta do Mini mundo 9 codigo descricao ramal (1,4)

DEPARTAMENTO (1,1)

(1,n)

matricula

CHEFIA

ALOCA

nome

(1,1)

(0,1) FUNCIONÁRIO

telefone

(P,E)

(1,3)

ADMINISTRAÇÃO

INVESTIMENTOS

(1,n)

bonus

FAZ

FÁBRICA (1,n)

data fim

MANIPULA

(1,1)

(1,n)

data HORA EXTRA

inicio

(0,n)

fim

ini

MATERIAL TÓXICO

codigo descrição

TME

130

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Resposta do Mini mundo 10

codigo nome email

APOSTADOR

(1,n)

(1,1)

Aposta

numero pontos valida

CARTELA (1,64)

palpite2 palpite2 pontos

PALPITE mandante (1,n)

(3,7)

numero nome imagem

TIME

(2,2)

Participa

numero gols_1 gols_2

JOGO

(3,7)

mandatário

conferido

Resposta do Mini mundo 11 COD_GRA

URL_GRA

NOM_GRA

GRAVADORA

(1,n)

COD_CD NOM_CD

PRODUZ

(1,1)

PRE_CD END_GRA

CON_GRA

IND_CD

COD_CAT

(1,1)

CATEGORIZA

LAN_CD

(1,n)

FAIXA

(0,n)

CATEGORIA

MAP_CAT

MEP_CAT

NUM_FAX

(1,n)

MUSICA

COD_MUS NOM_MUS DUR_MUS

(1,n)

CRIA (1,n)

AUTOR

COD_AUT NOM_AUT

131

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Respostas do exercício de Engenharia Reversa Normalização 8 cod_vend nome_vend

cod_cli nome_cli

(1,n)

Cliente cgc_cli

Pedido

(1,n)

Vendedor

ie_cli (1,n)

Possui

cod_prod

qtd_prod

desc_prod

(1,1) (1,1)

Item

(1,n)

Possui

Produto

unid prod

vu prod

Normalização 9 cod

nome

cod

desc

tel (1,n) (1,1)

Aluno

Compõe

(1,n)

Turma

(1,n)

nasc Aluno Disciplina (1,n)

Disciplina cod

desc

Normalização 10 mat

nom

Titular

cod

des

tel (1,n) (1,1)

Tem

(1,n)

Plano

(1,n)

cod

des

(1,1)

Dependente

seq

(1,1)

define

(1,n)

Grau

nom

132

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Normalização 11 mat nom

cod des

tel (1,n) (1,1)

Cliente

Pertence

(1,n)

Classe

(1,n)

cod des

(1,1) (1,1)

Contas num

(1,n)

Possui

Banco

saldo

Normalização 12 SIG_DEP

DES_DEP

DEPARTAMENTO (1,n)

(1,1)

FUNCIONÁRIO

MAT_FUN

COMP_FOL

(1,n)

VALOR_FOL

FOLHA

(1,n)

COD_PD

DES_PD

NOM_FUN

133

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL - Respostas do Exercício 1 1)

DEPARTAMENTO ( COD_D, DES_D ) FUNCIONARIO ( COD_F, NOM_F, SAL_F, COD_D ) PROJETO ( COD_P, DES_P ) TRABALHA ( COD_F, COD_P, CH )

2) a) SELECT F.NOM_F, T.COD_P FROM Funcionario F, Trabalha T WHERE F.COD_F = P.COD_F ORDER BY F.COD_F b) SELECT D.COD_D, F.NOM_F FROM Departamento D, Funcionario F WHERE D.COD_D = F.COD_D ORDER BY D.COD_D c) SELECT F.NOM_F, COUNT(T.COD_P) AS QTD FROM Funcionario F, Trabalha T WHERE F.COD_F = T.COD_F GROUP BY F.NOM_F d) SELECT F.COD_F, F.NOM_F, SUM(T.CH) AS HORAS FROM Funcionario F, Trabalha T WHERE F.COD_F = T.COD_F GROUP BY F.COD_F, F.NOM_F HAVING SUM(T.CH) >= 50 e) SELECT D.COD_D, D.DES_D, SUM(F.SAL_F) AS CUSTO FROM Departamento D, Funcionario F WHERE D.COD_D = F.COD_D GROUP BY D.COD_D, D.DES_D HAVING SUM(F.SAL_F) >= 5000 f) SELECT F.COD_F, F.NOM_F, F.SAL_F FROM Funcionario F WHERE F.SAL_F BETWEEN 200 AND 1000 g) SELECT D.COD_D, D.DES_D FROM Departamento D WHERE EXISTS (SELECT COUNT(F.COD_F) FROM Funcionario F WHERE F.COD_D = D.COD_D GROUP BY F.COD_D HAVING COUNT(F.COD_F) >= 3) h) SELECT F.COD_F, F.NOM_F FROM Funcionario F WHERE F.NOM_F LIKE 'JOAO%' i) SELECT F.NOM_F, F.SAL_F, F.SAL_F * 1.23 AS AUMENTO FROM Funcionario F j) SELECT D.COD_D, D.DES_D FROM Departamento AS D WHERE NOT EXISTS (SELECT * FROM Funcionario F WHERE F.COD_D = D.COD_D) OU SELECT D.COD_D, D.DES_D FROM Departamento D WHERE D.COD_D NOT IN (SELECT DISTINCT F.COD_D FROM Funcionario F)

134

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

SQL - Respostas do Exercício 2 1)

ESCOLA ( CGC, RAZÃO ) PROFESSOR ( MAT, NOM, SAL, CGC ) DISCIPLINAS ( COD, DES ) ALUNO ( MAT, NOME, EMAIL, NASC ) CURSA ( MAT, COD ) LECIONA ( MAT, COD ) PRE-REQ ( COD, COD_R )

2) a) SELECT FROM WHERE b) SELECT FROM WHERE AND c) SELECT FROM WHERE d) SELECT FROM e) SELECT FROM WHERE AND f) SELECT FROM WHERE GROUP g) SELECT FROM WHERE ORDER h) SELECT FROM WHERE AND i) SELECT FROM WHERE j) SELECT FROM WHERE AND AND k) SELECT FROM WHERE

E.CGC, E.RAZAO, P.NOM Escola E, Professor P E.CGC = P.CGC A.NOME, D.DES Aluno A, Disciplinas D , Cursa C A.MAT = C.MAT C.COD = D.COD A.NOM Aluno A A.EMAIL IS NULL P.NOME, P.SAL * 1.25 Professor P D1.DES, D2.DES Disciplinas D1, Pre-Req P, Disciplinas D2 D1.COD = P.COD P.COD_R = D2.COD E.RAZÃO, COUNT(P.MAT) AS QTD_PROF Professor P, Escola E E.CGC = P.CGC BY E.RAZAO P.NOME Professor P SAL > 1000 BY SAL DESCENDING P.NOME, D.DES Professor P, Leciona L, Disciplina D P.MAT = L.MAT L.COD = D.COD A.NOME Aluno A, Cursa C NOT EXISTS (SELECT * FROM Cursa C WHERE A.MAT = C.MAT ) P.NOME, D.DES Professor P, Leciona L, Disciplina D P.MAT = L.MAT L.COD = D.COD D.DES LIKE “%DADOS%” A.NOME, A.NASC Aluno A A.NASC BETWEEN “01/01/75” AND “31/12/80”

135

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Trabalho – Acompanhamento de Processos Modelo Conceitual

codigo nome endereço telefone F/J eMail

PARTE

codigo

(1,n)

descricao

ACIONA originado

(1,1)

(1,1) MOTIVA

POSSUI

número comarca vara

PROCESSO

(1,n)

TIPO ACAO

(1,1)

A/R status

sigla (1,1)

(1,n) origina

POSSUI

POSSUI (1,1)

telefone gerente

(0,n) (1,n)

FILIAL

data_acomp data_alerta

ACOMPANHAMENTO

observação

Modelo Lógico PROCESSO ( número, comarca, vara, ar, status, tipoacao, siglauf, motivador ) FILIAL ( sigla, descricao, gerente, email ) TIPOACAO ( codigo, descricao ) PARTE ( codigo, nome, endereco, telefone, fj, email ) ACOMPANHAMENTO ( processo.numero, data_acomp, data_alerta, observação ) PARTE_PROCESSO ( processo.numero, parte.codigo )

136

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Criação do DATABASE CREATE DATABASE Processo ON PRIMARY (NAME=Processo_data, FILENAME='c:\mssql7\data\processo.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROUTH=25%) LOG ON (NAME=Teste_log, FILENAME='c:\mssql7\data\processo.ldf', SIZE=4MB, MAXSIZE=6MB, FILEGROUTH=2MB)

- Criação das TABELAS (juntamente com os CONSTRAINTS) CREATE TABLE Gerencia ( Uf nvarchar (2) not null CONSTRAINT PK_Gerencia PRIMARY KEY NONCLUSTERED, Gerente nvarchar (40) null, Telefone nvarchar (20) null, Email nvarchar (50) null) ON PRIMARY CREATE TABLE Parte ( Parte int not null CONSTRAINT PK_Parte PRIMARY KEY NONCLUSTERED, Nome nvarchar (60) null ) ON PRIMARY CREATE TABLE TipoAcao ( Tipo int not null CONSTRAINT PK_TipoAcao PRIMARY KEY NONCLUSTERED, Descricao nvarchar (80) null ) ON PRIMARY CREATE TABLE Processo ( Processo int not null CONSTRAINT PK_Processo PRIMARY KEY NONCLUSTERED, Comarca nvarchar (30) null, Telefone nvarchar (20) null, TipoAcao int not null CONSTRAINT FK_Processo_TipoAcao FOREIGN KEY REFERENCES TipoAcao (intTipo), Valor money null, Objeto nvarchar (100) null, Uf nvarchar (2) not null CONSTRAINT FK_Processo_Uf FOREIGN KEY REFERENCES Gerencia (intUf), Status nvarchar (2) null, ProcessOrigem int null, DataInicio smalldatetime null, DataFim smalldatetime null, Observacao nvarchar (100) null ) ON PRIMARY CREATE TABLE Acompanhamento ( Processo int not null CONSTRAINT FK_Acompanhamento_Processo FOREIGN KEY REFERENCES Processo (Processo), DataAgenda smalldatetime not null CONSTRAINT PK_Acompanhamento PRIMARY KEY NONCLUSTERED (Processo, DataAgenda), AlertarEm smalldatetime null, Observacao nvarchar (100) null ) ON PRIMARY CREATE TABLE ProcessoParte ( Processo int not null CONSTRAINT FK_ProcessoParte_Processo FOREIGN KEY REFERENCES Processo (Processo), Parte int not null CONSTRAINT FK_ProcessoParte_Parte FOREIGN KEY REFERENCES Parte (Parte) CONSTRAINT PK_ProcessoParte PRIMARY KEY NONCLUSTERED (Processo,Parte) ) ON PRIMARY

137

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Criação das TABELAS (separadas dos CONSTRAINTS) CREATE TABLE Gerencia ( Uf Gerente Telefone Email ON PRIMARY

nvarchar nvarchar nvarchar nvarchar

CREATE TABLE Parte ( Parte Nome ON PRIMARY

int not null, nvarchar (60) null )

CREATE TABLE TipoAcao ( Tipo Descricao ) ON PRIMARY CREATE TABLE Processo ( Processo comarca Telefone TipoAcao Valor Objeto Uf Status ProcessOrigem DataInicio DataFim Observacao ) ON PRIMARY

(2) not null, (40) null, (20) null, (50) null)

int not null, nvarchar (80) null

int not null, nvarchar (30) null, nvarchar (20) null, int not null, money null, nvarchar (100) null, nvarchar (2) not null, nvarchar (2) null, int null, smalldatetime null, smalldatetime null, nvarchar (100) null

CREATE TABLE Acompanhamento ( Processo int not null, DataAgenda smalldatetime not null, AlertarEm smalldatetime null, Observacao nvarchar (100) null ) ON PRIMARY CREATE TABLE ProcessoParte ( Processo int not null, Parte int not null ) ON PRIMARY

138

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Criação dos CONSTRAINTS ALTER TABLE Gerencia WITH NOCHECK ADD CONSTRAINT PK_Gerencia PRIMARY KEY (UF) ON PRIMARY ALTER TABLE Parte WITH NOCHECK ADD CONSTRAINT PK_Parte PRIMARY KEY (Parte) ON PRIMARY

NONCLUSTERED

ALTER TABLE TipoAcao WITH NOCHECK ADD CONSTRAINT PK_TipoAcao PRIMARY KEY (Tipo) ON PRIMARY

NONCLUSTERED

ALTER TABLE Processo WITH NOCHECK ADD CONSTRAINT PK_Processo PRIMARY KEY (Processo) ON PRIMARY

NONCLUSTERED

ALTER TABLE Acompanhamento WITH NOCHECK ADD CONSTRAINT PK_Acompanhamento PRIMARY KEY (Processo,DataAgenda) ON PRIMARY ALTER TABLE ProcessoParte WITH NOCHECK ADD CONSTRAINT PK_ProcessoParte PRIMARY KEY (Processo,Parte) ON PRIMARY

NONCLUSTERED

ALTER TABLE Processo ADD CONSTRAINT FK_Processo_Gerencia FOREIGN KEY (UF) REFERENCES Gerencia (UF), CONSTRAINT FK_Processo_TipoAcao FOREIGN KEY (TipoAcao) REFERENCES TipoAcao (Tipo) ALTER TABLE Acompanhamento ADD CONSTRAINT FK_Acompanhamento_Processo FOREIGN KEY (Processo) REFERENCES Processo (Processo) ALTER TABLE ProcessoParte ADD CONSTRAINT FK_ProcessoParte_Parte FOREIGN KEY (Parte) REFERENCES Parte (Parte), CONSTRAINT FK_ProcessoParte_Processo FOREIGN KEY (Processo) REFERENCES Processo (Processo)

- Eliminando as TABELAS (Primeiro elimina-se os CONSTRAINTS) ALTER ALTER ALTER ALTER ALTER

TABLE TABLE TABLE TABLE TABLE

Processo DROP CONSTRAINT FK_Processo_TipoAcao Processo DROP CONSTRAINT FK_Processo_Uf ProcessoParte DROP CONSTRAINT FK_ProcessoParte_Parte Acompanhamento DROP CONSTRAINT FK_Acompanhamento_Processo ProcessoParte DROP CONSTRAINT FK_ProcessoParte_Processo

if exists (select * from sysobjects where id = object_id(N'Processo') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Processo GO if exists (select * from sysobjects where id = object_id(N'Acompanhamento') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Acompanhamento GO if exists (select * from sysobjects where id = object_id(N'ProcessoParte') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table ProcessoParte GO if exists (select * from sysobjects where id = object_id(N'TipoAcao') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table TipoAcao GO if exists (select * from sysobjects where id = object_id(N'Gerencia') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Gerencia GO if exists (select * from sysobjects where id = object_id(N'Parte') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Parte GO

139

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Inserindo informações - Tabela : TIPOACAO INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO

TipoAcao TipoAcao TipoAcao TipoAcao TipoAcao TipoAcao TipoAcao

VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'Acoes (2,'Acoes (3,'Acoes (4,'Acoes (5,'Acoes (6,'Acoes (7,'Acoes

Trabalhistas') da Vara de Familia') contra o INSS') Criminais') contra a Tele X') contra o Ponto Quente') contra o Bando do Breziu')

- Inserindo informações - Tabela : GERENCIA INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia Gerencia

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

('AC','Almir','33333333','ac@site.com.br') ('AL','Alfredo','33333333','al@site.com.br') ('AM','Jair','33333333','am@site.com.br') ('AP','Raul','33333333','am@site.com.br') ('BA','Wander','44444444','ba@site.com.br') ('CE','Jose Carlos','55555555','ce@site.com.br') ('DF','Jaqueline','66666666','df@site.com.br') ('ES','Leia','77777777','es@site.com.br') ('GO','Maria da Graça','88888888','go@site.com.br') ('MA','Eva','999999999','ma@site.com.br') ('MT','Mauro','10101010','mt@site.com.br') ('MS','Tania Silene','12121212','ms@site.com.br') ('MG','Reginaldo','13131313','mg@site.com.br') ('PA','Roosevelt','14141414','pa@site.com.br') ('PB','Maria Ana','15151515','pb@site.com.br') ('PR','Mauro Pereira','16161616','pr@site.com.br') ('PE','Sergio','17171717','pe@site.com.br') ('PI','Raimundo','18181818','pi@site.com.br') ('RJ','Carlos H','19191919','rj@site.com.br') ('RN','Pedro Paulo','20202020','rn@site.com.br') ('RS','Roberto','21212121','rs@site.com.br') ('RO','Aluizio','23232323','ro@site.com.br') ('RR','Rosinaldo','24242424','rr@site.com.br') ('SC','Julio Cesar','25252525','sc@site.com.br') ('SE','Gildelia','26262626','se@site.com.br') ('TO','Ivan','27272727','to@site.com.br')

- Inserindo informações - Tabela : PARTE INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte Parte

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'Santos Rosendo') (2,'Jose Geraldo Junior') (3,'Jose Jorge da Silva') (4,'Alan Camargo Silva') (5,'Tatiana Mendonça Meneses') (6,'Sidnei Barbieri Math Jr') (7,'Misael Augusto Araujo') (8,'Leonardo Lima Oliveira') (9,'Carlos Roberto M Soares') (10,'Claudio Silva Soares') (11,'Katiucia Helena Rosa') (12,'Wallace da Silva') (13,'Roberto de Deus Leal') (14,'Milton Souza Santos') (15,'Michel Firmino Souza')

- Inserindo informações - Tabela : PROCESSO INSERT INTO Processo VALUES (1,'Comarca 1','11111111',1,10000.00,'Objeto do processo','RJ','EA','','09/26/2001','','Texto da observacao') INSERT INTO Processo VALUES (2,'Comarca 1','11111111',2,10200.88,'Objeto do processo','MA','EA','','09/25/2001','','Texto da observacao') INSERT INTO Processo VALUES (3,'Comarca 2','22222222',3,20000.00,'Objeto do processo','PI','EA','','09/25/2001','','Texto da observacao') INSERT INTO Processo VALUES (4,'Comarca 2','22222222',4,33000.00,'Objeto do processo','RJ','EA','','08/26/2001','','Texto da observacao') INSERT INTO Processo VALUES (5,'Comarca 3','33333333',5,15000.00,'Objeto do processo','ES','EA','','07/26/2001','','Texto da observacao') INSERT INTO Processo VALUES (6,'Comarca 4','44444444',6,17000.00,'Objeto do processo','MG','EA','','06/26/2001','','Texto da observacao') INSERT INTO Processo VALUES (7,'Comarca 5','55555555',7,23000.00,'Objeto do processo','PA','EA','','09/27/2001','','Texto da observacao')

140

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Inserindo informações - Tabela : PROCESSOPARTE INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte ProcessoParte

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,1) (2,2) (3,4) (3,5) (3,6) (4,6) (5,6) (5,9) (6,10) (7,10) (7,11) (7,11)

- Inserindo informações - Tabela : ACOMPANHAMENTO INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento Acompanhamento

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'09/26/2001','10/10/2001','Entrada no FORUM') (2,'09/26/2001','10/10/2001','Entrada no FORUM') (3,'09/26/2001','10/10/2001','Entrada no FORUM') (4,'09/26/2001','10/10/2001','Entrada no FORUM') (5,'09/26/2001','10/10/2001','Entrada no FORUM') (6,'09/26/2001','10/10/2001','Entrada no FORUM') (7,'09/26/2001','10/10/2001','Entrada no FORUM') (1,'10/10/2001','11/10/2001',’1o acompanhamento') (2,'10/10/2001','11/09/2001',’1o Acompanhamento') (3,'10/10/2001','10/20/2001',’1o Acompanhamento') (3,'10/20/2001','11/10/2001',’2o Acompanhamento') (3,'11/10/2001','12/10/2001',’3o Acompanhamento') (4,'10/10/2001','10/15/2001',’1o Acompanhamento') (4,'10/15/2001','',’2o Acompanhamento') (4,'10/20/2001','10/30/2001',’3o Acompanhamento')

141

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

- Extraindo Informações PARTES COM MAIS DE UM PROCESSO select pp.parte, pa.nome, count(pp.processo) #processos from processoparte pp, parte pa where pp.parte = pa.parte group by pp.parte, pa.nome having count(pp.processo) > 1

PARTES SEM PROCESSOS select pa.parte, pa.nome from parte pa where not exists ( select * from processoparte pp where pp.parte = pa.parte )

PROCESSOS E SUAS PARTES - Usando o INNER JOIN select pp.processo, pp.parte, pa.nome, pr.comarca from processoparte pp inner join processo pr on pp.processo = pr.processo inner join parte pa on pp.parte = pa.parte -- Usando a cláusula WHERE select pp.processo, pp.parte, pa.nome, pr.comarca from processoparte pp, parte pa, processo pr where pr.processo = pp.processo and pp.parte = pa.parte

PROCESSO COM MAIOR VALOR select TOP 1 uf, valor from processo order by valor desc

QUANTIDADE DE PROCESSOS POR UF (ORDENADO PELA QUANTIDADE) select uf, count(*) qtd from processo group by uf order by count(*) desc

ELIMINADO UMA INFORMAÇÃO delete from parte where parte = 1 (deverá ocorrer um erro. A parte tem processo) delete from parte where parte = 3 (não ocorrerá erro. Não existem processos para a parte)

142

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Trabalho – Solicitações de Serviços Modelo Conceitual usu_mat

Usuários

usu_nom

(T,S)

Receptor

Solicitante (1,n)

Emite (1,1)

(1,1)

(1,n)

Recebe

Executa

sol_dtemi sol_dtrec (1,1) (1,1) sol_dtini Solicitação sol_dtfim sol_hrfim sol_hrini sol_hrrec sol_tempo sol_hremi

sol_num

Possui sis_cod

Executor

Possui

Possui (1,n)

(1,n)

Tipos de Serviço

Sistema sis_des ts_cod

(1,1)

sta_cod

Status sta_des ts_des

Modelo Lógico -

SISTEMA (sis_cod, sis_des)

STATUS (sta_cod, sta_des)

TIPOS DE SERVICO (ts_cod, ts_des)

USUARIO (usu_mat, usu_nom)

SOLICITACAO (sol_num, sol_dtemi, sol_dtrec, sol_dtini, sol_dtfim, sol_hremi, sol_hrrec, sol_hrini, sol_hrfim, sol_tempo, sis_cod, sta_cod, sol_mat, rec_mat, exe_mat, ts_cod)

143

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Criação do DATABASE

CREATE DATABASE Solicitacao ON PRIMARY (NAME=Solicitacao_data, FILENAME='c:\mssql7\data\solicitacao.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROUTH=25%) LOG ON (NAME=Teste_log, FILENAME='c:\mssql7\data\solicitacao.ldf', SIZE=4MB, MAXSIZE=6MB, FILEGROUTH=2MB)

Criação das tabelas (juntamente com os CONSTRAINTS)

CREATE TABLE Sistema ( sis_cod int NOT NULL CONSTRAINT PK_Sistema PRIMARY KEY NONCLUSTERED, sis_des nvarchar (40) NULL ) ON PRIMARY CREATE TABLE Status ( sta_cod int NOT NULL CONSTRAINT PK_Status PRIMARY KEY NONCLUSTERED, sta_des nvarchar (40) NULL ) ON PRIMARY CREATE TABLE TipoServico ( ts_cod int NOT NULL CONSTRAINT PK_TipoServico PRIMARY KEY ts_des nvarchar (40) NULL ) ON PRIMARY

NONCLUSTERED,

CREATE TABLE Usuario ( usu_mat int NOT NULL CONSTRAINT PK_Usuario PRIMARY KEY NONCLUSTERED, usu_nom nvarchar (40) NULL ) ON PRIMARY CREATE TABLE Solicitacao ( sol_num int NOT NULL CONSTRAINT PK_Solicitacao PRIMARY KEY NONCLUSTERED, sol_dtemi smalldatetime NULL , sol_tempo int NULL , sis_cod int NOT NULL CONSTRAINT FK_Solicitacao_Sistema FOREIGN KEY REFERENCES Sistema (sis_cod), sta_cod int NOT NULL CONSTRAINT FK_Solicitacao_Status FOREIGN KEY REFERENCES Status (sta_cod), sol_mat int NOT NULL CONSTRAINT FK_Solicitacao_UsuarioS FOREIGN KEY REFERENCES Usuario (usu_mat) rec_mat int NOT NULL CONSTRAINT FK_Solicitacao_UsuarioR FOREIGN KEY REFERENCES Usuario (usu_mat), exe_mat int NOT NULL CONSTRAINT FK_Solicitacao_UsuarioE FOREIGN KEY REFERENCES Usuario (usu_mat), ts_cod int NOT NULL CONSTRAINT FK_Solicitacao_TipoServico FOREIGN KEY (ts_cod) REFERENCES TipoServico (ts_cod), ) ON PRIMARY

Criação das tabelas (separadas dos CONSTRAINTS)

CREATE TABLE Sistema ( sis_cod sis_des ) ON PRIMARY

int NOT NULL , nvarchar (40) NULL

CREATE TABLE Solicitacao sol_num sol_dtemi sol_tempo sis_cod sta_cod sol_mat rec_mat exe_mat ts_cod

( int NOT NULL , smalldatetime NULL , int NULL , int NOT NULL , int NOT NULL , int NOT NULL , int NOT NULL , int NOT NULL , int NOT NULL ) ON PRIMARY

144

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados CREATE TABLE Status ( sta_cod sta_des ) ON PRIMARY

int NOT NULL , nvarchar (40) NULL

CREATE TABLE TipoServico ( ts_cod int NOT NULL , ts_des nvarchar (40) NULL ) ON PRIMARY CREATE TABLE Usuario ( usu_mat usu_nom ) ON PRIMARY

int NOT NULL , nvarchar (40) NULL

Criação dos CONSTRAINTS

ALTER TABLE Sistema WITH NOCHECK ADD CONSTRAINT PK_Sistema PRIMARY KEY (sis_cod) ON PRIMARY

NONCLUSTERED

ALTER TABLE Solicitacao WITH NOCHECK ADD CONSTRAINT PK_Solicitacao PRIMARY KEY (sol_num) ON PRIMARY ALTER TABLE Status WITH NOCHECK ADD CONSTRAINT PK_Status PRIMARY KEY (sta_cod) ON PRIMARY

NONCLUSTERED

ALTER TABLE TipoServico WITH NOCHECK ADD CONSTRAINT PK_TipoServico PRIMARY KEY (ts_cod) ON PRIMARY ALTER TABLE Usuario WITH NOCHECK ADD CONSTRAINT PK_Usuario PRIMARY KEY (usu_mat) ON PRIMARY

NONCLUSTERED

ALTER TABLE Solicitacao ADD CONSTRAINT FK_Solicitacao_Sistema FOREIGN KEY (sis_cod) REFERENCES Sistema (sis_cod ), CONSTRAINT FK_Solicitacao_Status FOREIGN KEY (sta_cod) REFERENCES Status (sta_cod), CONSTRAINT FK_Solicitacao_TipoServico FOREIGN KEY (ts_cod) REFERENCES TipoServico (ts_cod), CONSTRAINT FK_Solicitacao_UsuarioE FOREIGN KEY (exe_mat) REFERENCES Usuario (usu_mat), CONSTRAINT FK_Solicitacao_UsuarioR FOREIGN KEY (rec_mat) REFERENCES Usuario ( usu_mat), CONSTRAINT FK_Solicitacao_UsuarioS FOREIGN KEY (sol_mat) REFERENCES Usuario (usu_mat)

Eliminando as TABELAS (Primeiro elimina-se os CONSTRAINTS)

ALTER ALTER ALTER ALTER ALTER ALTER

TABLE TABLE TABLE TABLE TABLE TABLE

Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao

DROP DROP DROP DROP DROP DROP

CONSTRAINT CONSTRAINT CONSTRAINT CONSTRAINT CONSTRAINT CONSTRAINT

FK_Solicitacao_Sistema FK_Solicitacao_Status FK_Solicitacao_TipoServico FK_Solicitacao_UsuarioE FK_Solicitacao_UsuarioR FK_Solicitacao_UsuarioS

if exists (select * from sysobjects where id = object_id(N'Sistema') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Sistema if exists (select * from sysobjects where id = object_id(N'Solicitacao') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Solicitacao if exists (select * from sysobjects where id = object_id(N'Status') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Status if exists (select * from sysobjects where id = object_id(N'TipoServico') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table TipoServico if exists (select * from sysobjects where id = object_id(N'Usuario') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Usuario

145

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Inserindo informações - Tabela : SISTEMA

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

(1,'Faturamento') (2,'Estoque') (3,'Contabilidade') (4,'Folha de Pag') (5,'Lib. Senha') (6,'Tb Financeiras') (7,'Ativo') (8,'Cobranca') (9,'Emprestimo') (10,'Protocolo') (11,'Renda Fixa') (12,'Compas')

INTO INTO INTO INTO INTO INTO INTO

Status Status Status Status Status Status Status

VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'Nao recebida') (2,'Recebida') (3,'Alocada') (4,'Encerrada') (5,'Pendente') (6,'Cancelada') (7,'Fechada')

INTO INTO INTO INTO INTO INTO INTO INTO

TipoServico TipoServico TipoServico TipoServico TipoServico TipoServico TipoServico TipoServico

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'Desenv. Sistema') (2,'Alteracao Sistema') (3,'Desenv. Prog.') (4,'Alteracao Prog.') (5,'Manutencao Dados') (6,'Chamado Tecnico') (7,'Install') (8,'Executar Prog.')

Inserindo informações - Tabela : USUARIO

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

Inserindo informações - Tabela : TIPOS DE SERVICO

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

Sistema Sistema Sistema Sistema Sistema Sistema Sistema Sistema Sistema Sistema Sistema Sistema

Inserindo informações - Tabela : STATUS

INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario Usuario

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'Santos') (2,'Jose') (3,'Jose') (4,'Alan') (5,'Tatiana') (6,'Sidnei') (7,'Misael') (8,'Leonardo') (9,'Carlos') (10,'Claudio') (11,'Katiucia') (12,'Wallace') (13,'Roberto') (14,'Milton') (15,'Michel')

Inserindo informações - Tabela : SOLICITACAO

INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT INSERT

INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO INTO

Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao Solicitacao

VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES VALUES

(1,'09/26/2001',10,1,1,1,2,3,1) (2,'09/25/2001',11,1,2,2,2,4,2) (3,'09/24/2001',15,2,3,3,2,5,3) (4,'09/23/2001',16,3,1,4,2,6,4) (5,'09/22/2001',17,4,2,1,2,7,1) (6,'09/21/2001',12,5,3,2,2,8,2) (7,'09/20/2001',13,7,1,3,2,9,2) (8,'09/19/2001',11,1,2,4,2,10,3) (9,'09/18/2001',19,1,3,1,2,11,3) (10,'09/17/2001',20,3,1,2,2,12,3) (11,'09/16/2001',12,1,2,3,2,1,4) (12,'09/15/2001',13,2,3,4,2,5,2) (13,'09/14/2001',15,4,4,1,2,4,1) (14,'09/13/2001',10,5,1,2,2,6,1) (15,'09/12/2001',11,4,2,3,2,7,1) (16,'09/11/2001',13,1,3,4,2,4,1) (17,'09/10/2001',14,1,4,1,2,7,2) (18,'09/09/2001',22,1,5,3,2,4,2)

146

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Extraindo informações

NOME DO SOLICITANTE E AS SOLICITAÇÕES EM ABERTO, POR SOLICITANTE select usu_nom, sol_num from usuario u, solicitacao s where u.usu_mat = s.sol_mat and sta_cod = 3 order by usu_nom

NOME DO SISTEMA E A QUANTIDADE DE SOLICITAÇÕES EM ABERTO, POR SISTEMA select si.sis_des, count(so.sol_num) qtd from sistema si, solicitacao so where si.sis_cod = so.sis_cod and so.sta_cod = 3 group by si.sis_des

NOME DO EXECUTOR E QUANTIDADE DE SOLICITAÇÕES EM ABERTO, POR EXECUTOR select u.usu_nom, count(s.sol_num) qtd from usuario u, solicitacao s where u.usu_mat = s.exe_mat and sta_cod = 3 group by u.usu_nom

NOME DO EXECUTOR, A QUANTIDADE DE SOLICITAÇÕES ENCERRADAS E O TEMPO TOTAL CONSUMIDO, POR EXECUTOR select u.usu_nom, count(*), sum(s.sol_tempo) tempo from usuario u, solicitacao s where u.usu_mat = s.exe_mat and s.sta_cod = 5 group by u.usu_nom

EXPURGAR SOLICITAÇÕES CANCELADAS, FECHADAS OU PENDENTES DO ANO DE 2001 delete from solicitacao where year(sol_dtemi) = 2001 and sta_cod in (5,6,7)

147

Universidade Veiga de Almeida Instituto Superior Politécnico Sistemas de Gerenciamento de Banco de Dados

Trabalho – Sistema de Estoque Modelo Conceitual CATEGORIA

cod_c des_c

(1,n)

Contém (1,1)

SUB CATEGORIAS cod_u

des_u

DEPTO

des_s

(1,n)

Faz

cod_m des_m

(1,1)

qtd_sol (1,1)

cod_d des_d

(1,n)

Contém

UNIDADE DE ARMAZENAMENTO

Possui

cod_s

MATERIAL

(1,n)

ITENS

num_rm

(1,n)

emi_rm atend_rm

(0,1)

min pc atu vu

Movimenta data_mov Requer

(0,1)

(1,1)

qtd_mov

MOVIMENTO

(1,1)

Possui

(1,1)

JUSTIFICATIVA

(1,n)

des

TIPO DE MOVIMENTO

cod_t des_t

Modelo Lógico -

CATEGORIA (cod_c, des_c)

SUBCATEGORIA ( cod_c, des_s )

UNIDADE DE ARMAZENAMENTO ( cod_u, des_u )

MATERIAL ( cod_c, cod_s, cod_m, des_m, min, pc, atu, vu, cod_u )

DEPTO ( cod_d, des_d )

RM ( cod_d, num_rm, emi_rm, atend_rm )

ITENS ( cod_d, num_rm, cod_m, qtd_sol )

TIPO DE MOVIMENTO ( cod_t, des_t )

MOVIMENTO ( cod_d, num_rm, cod_m, qtd_ate, data_ate, cod_t )

JUSTIFICATIVA ( cod_d, num_rm, cod_m, des )

148