sistema banco dados 2

Prévia do material em texto

<p>GES013 – Sistema de Banco de Dados</p><p>Prof. Renato Pimentel</p><p>2022/1</p><p>Sumário</p><p>1 Introdução aos Sistemas Gerenciadores de Banco de Dados 7</p><p>1.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7</p><p>1.2 Sistema gerenciador de banco de dados . . . . . . . . . . . . . . . 7</p><p>1.3 Sistema de banco de dados (SBD) . . . . . . . . . . . . . . . . . 12</p><p>1.4 Modelos conceituais e físicos . . . . . . . . . . . . . . . . . . . . . 15</p><p>1.5 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17</p><p>2 Modelo entidade-relacionamento (MER) 18</p><p>2.1 Conceitos básicos . . . . . . . . . . . . . . . . . . . . . . . . . . . 18</p><p>2.2 Entidades, atributos e diagrama ER . . . . . . . . . . . . . . . . 18</p><p>2.3 Chaves . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20</p><p>2.4 Mais conceitos sobre atributos . . . . . . . . . . . . . . . . . . . . 20</p><p>2.5 Relacionamentos . . . . . . . . . . . . . . . . . . . . . . . . . . . 24</p><p>2.6 Entidades fracas . . . . . . . . . . . . . . . . . . . . . . . . . . . 33</p><p>2.7 Agregação . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35</p><p>2.8 Passos para elaboração do projeto lógico . . . . . . . . . . . . . . 36</p><p>2.9 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37</p><p>3 Modelo relacional (MR) 41</p><p>3.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41</p><p>3.2 Definições formais . . . . . . . . . . . . . . . . . . . . . . . . . . 42</p><p>3.3 Restrições . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45</p><p>3.4 Operações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49</p><p>3.5 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51</p><p>4 Mapeamento do MER para relacional 53</p><p>4.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53</p><p>4.2 Passo 1: mapeamento das entidades regulares . . . . . . . . . . . 54</p><p>4.3 Passo 2: mapeamento das entidades fracas . . . . . . . . . . . . . 55</p><p>4.4 Passo 3: mapeamento dos relacionamentos binários 1 : 1 . . . . . 56</p><p>4.5 Passo 4: mapeamento dos relacionamentos binários 1 : N . . . . 58</p><p>4.6 Passo 5: mapeamento dos relacionamentos binários M : N . . . . 60</p><p>4.7 Passo 6: mapeamento dos atributos multivalorados . . . . . . . . 61</p><p>1</p><p>4.8 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62</p><p>5 Introdução e acesso ao PostgreSQL 66</p><p>5.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66</p><p>5.2 Arquitetura do PostgreSQL . . . . . . . . . . . . . . . . . . . . . 67</p><p>5.3 Acesso aos dados e conexão com o servidor . . . . . . . . . . . . 68</p><p>6 SQL DDL – Comandos para definição de dados 75</p><p>6.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75</p><p>6.2 Composição do SQL . . . . . . . . . . . . . . . . . . . . . . . . . 76</p><p>6.3 DDL – comandos para definição de dados . . . . . . . . . . . . . 77</p><p>6.4 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88</p><p>7 SQL DML – Comandos para manipulação de dados 91</p><p>7.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91</p><p>7.2 Comando INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . 91</p><p>7.3 Comando UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . 92</p><p>7.4 Comando DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . 94</p><p>7.5 Observações . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94</p><p>7.6 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97</p><p>8 Álgebra relacional 99</p><p>8.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99</p><p>8.2 Relações unárias . . . . . . . . . . . . . . . . . . . . . . . . . . . 101</p><p>8.3 Relações binárias . . . . . . . . . . . . . . . . . . . . . . . . . . . 108</p><p>9 Comando SELECT – consultas básicas e consultas aninhadas 118</p><p>9.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118</p><p>9.2 Consultas básicas . . . . . . . . . . . . . . . . . . . . . . . . . . . 119</p><p>9.3 Consultas aninhadas . . . . . . . . . . . . . . . . . . . . . . . . . 123</p><p>10 Comando SELECT – consultas com tabelas de junção e tipos de</p><p>junção 131</p><p>10.1 Introdução . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131</p><p>10.2 Uso das cláusulas JOIN . . . . . . . . . . . . . . . . . . . . . . . 132</p><p>10.3 Exercícios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138</p><p>11 SQL DML – SELECT – agregação e agrupamentos 139</p><p>11.1 Funções de agregação . . . . . . . . . . . . . . . . . . . . . . . . 139</p><p>11.2 Agrupamentos . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142</p><p>11.3 Agrupamentos com funções de agregação . . . . . . . . . . . . . . 144</p><p>2</p><p>Apresentação</p><p>Objetivos e Ementa</p><p>Objetivos</p><p>• Objetivo geral: Ensinar o aluno a modelar e implementar um sistema</p><p>banco de dados por meio de um sistema de gerência de banco de dados</p><p>relacional.</p><p>• Objetivos específicos:</p><p>– capacitar o aluno a modelar bancos de dados por meio do Diagrama</p><p>Entidade-Relacionamento (DER);</p><p>– capacitar o aluno a modelar o esquema lógico do Banco de Dados por</p><p>meio do mapeamento do DER para o Modelo Relacional e da teoria</p><p>de dependências funcionais (formas normais);</p><p>– capacitar o aluno a implementar o banco de dados (BD) do Modelo</p><p>Relacional correspondente a um DER por meio da linguagem de</p><p>descrição de dados SQL/DDL, incluindo as restrições de integridade</p><p>de dados modeladas;</p><p>– capacitar o aluno a inserir, remover e atualizar dados de um BD por</p><p>meio de instruções da linguagem de manipulação de dados SQL/DML;</p><p>– capacitar o aluno recuperar dados de um BD por meio de consultas</p><p>da linguagem de manipulação de dados SQL/DML;</p><p>– capacitar o aluno a gerar relatórios referentes aos dados contidos em</p><p>um BD relacional.</p><p>Ementa do curso</p><p>• Introdução: conceitos e termos relacionados a bancos de dados (BDs).</p><p>• Modelagem: Diagrama Entidade-relacionamento (DER); como modelar</p><p>um BD usando o DER.</p><p>• SQL: criação e manipulação de BDs.</p><p>• Geração de Relatório: ferramentas; cabeçalho; quebra de página; totalizadores.</p><p>Bibliografia</p><p>Bibliografia básica</p><p>• DATE, C. J. Introdução a sistemas de bancos de dados. 8. ed. Rio de</p><p>Janeiro: Elsevier / Campus, 2004.</p><p>• ELMASRI R.; NAVATHE, S. B. Sistemas de banco de dados. 6. ed. São</p><p>Paulo: Addison-Wesley, 2011.</p><p>3</p><p>• RAMAKRISHNAN, R.; GEHRKE, J. Sistemas de gerenciamento de banco</p><p>de dados. 3. ed. São Paulo: McGraw-Hill, 2008.</p><p>Bibliografia complementar</p><p>• ELMASRI R.; NAVATHE, S. B. Fundamentals of database systems. 5.</p><p>ed. New York: Addison-Wesley, 2006.</p><p>• GUIMARÃES, C. C. Fundamentos de banco de dados: modelagem, projeto</p><p>e linguagem SQL. Campinas: Editora Unicamp, 2003.</p><p>• HEUSER, C. A. Projeto de banco de dados. 5. ed. Porto Alegre: Sagra,</p><p>2004. Obs.: 6. ed. (2011): Portal Minha Biblioteca</p><p>• KROENKE, D. M. Banco de dados: fundamentos, projeto e implementação.</p><p>6. ed. Rio de Janeiro: LTC, 1999.</p><p>• SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistema de banco</p><p>de dados. 5. ed. Rio de Janeiro: Elsevier / Campus, 2006.</p><p>Conteúdo previsto</p><p>1. Introdução a BD:</p><p>• O que é um banco de dados (BD)</p><p>• O que é um sistema gerenciador de banco de dados (SGBD)</p><p>• Paradigmas de SGBD: relacional, objeto-relacional, objeto</p><p>• Aplicações</p><p>2. Modelagem de dados:</p><p>• Modelo entidade-relacionamento (ER)</p><p>• Modelo relacional (MR)</p><p>• Mapeamentos e formas normais</p><p>3. Linguagem SQL</p><p>Avaliação: aproveitamento e frequência</p><p>• 3 provas teóricas:</p><p>– 27/10/2022 (P1) – 25 pontos</p><p>– 02/12/2022 (P2) – 25 pontos</p><p>– 20/01/2023 (P3) – 25 pontos</p><p>Nota de prova:</p><p>NP = P1 + P2 + P3</p><p>4</p><p>• Atividades práticas (NS = 10 pontos): na forma de exercícios de modelagem</p><p>ou SQL, a serem feitas ao longo do curso, com prazo estipulado para</p><p>entrega.</p><p>• Trabalho de implementação semestral (NI = 15 pontos): a ser apresentado</p><p>ao final do curso, com arguição.</p><p>• Nota final (aproveitamento):</p><p>NF = NP + NS + NI</p><p>Avaliação substitutiva</p><p>• Alunos que não atingiram a nota 60 (somente) terão direito a uma prova</p><p>substitutiva (SUB)</p><p>• Data: 02/02/2023</p><p>• O conteúdo da prova será o visto ao longo de todo o semestre até a data</p><p>da SUB.</p><p>• A prova substitutiva vale 25 pontos,</p><p>• Com relação ao autorrelacionamento SUPERVISIONA, a relação FUNCIONARIO</p><p>foi atualizada, recebendo o atributo CPF\_supervisor – chave estrangeira</p><p>para sua própria chave primária CPF;</p><p>• No caso de TRABALHA_PARA, a relação FUNCIONARIO novamente é modificada,</p><p>pelo acréscimo da chave estrangeira DNumero, que se refere à chave primária</p><p>DNumero de departamento.</p><p>59</p><p>• Finalmente, o relacionamento CONTROLA deu origem ao atributo Depto</p><p>na relação PROJETO – chave estrangeira que também se refere à chave</p><p>primária DNumero de departamento.</p><p>4.6 Passo 5: mapeamento dos relacionamentos binários</p><p>M : N</p><p>Considere um relacionamento M : N do qual fazem parte duas relações.</p><p>• Deve ser criada uma nova relação (uma nova tabela), S, para representar</p><p>tal relacionamento (relação de relacionamento ou referência cruzada);</p><p>• Inserir, como chaves estrangeiras, as chaves primárias das entidades envolvidas</p><p>no relacionamento;</p><p>• A chave primária da nova relação é composta pela concatenação dessas</p><p>chaves estrangeiras;</p><p>• Mapear os atributos simples do relacionamento nesta nova relação (e</p><p>atributos simples pertencentes aos atributos compostos).</p><p>60</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Cria-se uma nova relação, a partir de FUNCIONARIO e PROJETO:</p><p>FUNCIONARIO(Pnome, NomeM, Unome, CPF, Data_nasc, Endere-</p><p>co, Salario, Sexo, CPF_supervisor, DNumero)</p><p>PROJETO(PjNome, PNumero, PLocal, Depto)</p><p>TRABALHA_EM(CPF, PNumero, Horas)</p><p>• Ou seja, o relacionamento TRABALHA_EM foi mapeado numa nova relação,</p><p>de mesmo nome, contendo como sua chave primária a concatenação das</p><p>duas chaves estrangeiras, além do atributo do próprio relacionamento,</p><p>Horas.</p><p>4.7 Passo 6: mapeamento dos atributos multivalorados</p><p>• Criar uma nova relação R para cada atributo multivalorado A.</p><p>• Incluir em R, além de um atributo que represente A, uma chave estrangeira</p><p>que faça referência à chave primária ChP da relação que representa a</p><p>entidade que contém o atributo multivalorado A.</p><p>• Se o atributo multivalorado for composto, mapear seus atributos simples.</p><p>61</p><p>• A chave primária de R é a combinação da chave primária ChP com o</p><p>atributo multivalorado A.</p><p>• Se o atributo for composto, deve-se escolher qual fará parte da chave</p><p>primária.</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Cria-se uma nova relação, a partir de DEPARTAMENTO:</p><p>DEPARTAMENTO(DNome, DNumero, CPF_gerente, Data_inicio_ge-</p><p>rente)</p><p>DEPTO_LOCALIZACOES(DNumero, Dlocalizacao)</p><p>• O atributo multivalorado Localizacoes foi mapeado numa nova relação,</p><p>DEPTO_LOCALIZACOES, contendo a chave estrangeira DNumero que se</p><p>refere a chave primária DNome de DEPARTAMENTO.</p><p>4.8 Exercícios</p><p>1. Usando o Diagrams.net (com os elementos Table da lista “Relação de</p><p>entidade”), mapeie para o modelo relacional o diagrama ER do BD</p><p>COMPANHIA:</p><p>62</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Adaptado de (ELMASRI; NAVATHE, 2018).</p><p>63</p><p>2. Faça o mesmo para o diagrama que segue:</p><p>horariodisciplina</p><p>estudante</p><p>Id</p><p>nome</p><p>salário</p><p>Id</p><p>nome</p><p>tot_créditos</p><p>id_curso</p><p>nome</p><p>créditos</p><p>id_horario</p><p>{ dia</p><p>hora_inicio</p><p>hora_fim</p><p>}</p><p>course_id prereq_id</p><p>orienta</p><p>ensina matricula</p><p>turma_disciplina turma_horario</p><p>nota</p><p>pré-requisito</p><p>prof_depto estud_depto</p><p>professor</p><p>departamento</p><p>depto_nome</p><p>bloco</p><p>orçamento</p><p>turma</p><p>id_turma</p><p>semestre</p><p>ano</p><p>turma_depto</p><p>turma_sala</p><p>sala</p><p>bloco</p><p>numero_sala</p><p>capacidade</p><p>Adaptado de (SILBERSCHATZ, 2020)</p><p>A notação usada no diagrama anterior é a que segue:</p><p>As restrições de cardinalidade são ilustradas por uma linha direcionada</p><p>(→), significando “um”; ou por uma linha não-direcionada (−), significando</p><p>“muitos”, entre o CE e o CR.</p><p>Exemplos:</p><p>• Um estudante está associado com no máximo um instrutor através</p><p>do relacionamento orienta;</p><p>• Um estudante está associado a no máximo um departamento através</p><p>do relacionamento estud_depto.</p><p>64</p><p>3. (ELMASRI; NAVATHE, 2018) Num processo de engenharia reversa, mapeie</p><p>o esquema relacional que segue num diagrama ER.</p><p>LIVRO</p><p>Cod_livro Titulo Nome_editora</p><p>AUTORES_LIVRO</p><p>Nome_autor</p><p>EDITORA</p><p>Nome Endereco</p><p>COPIAS_LIVRO</p><p>Total_copia</p><p>EMPRESTIMOS_LIVRO</p><p>UNIDADE_BIBLIOTECA</p><p>Nome_unidade</p><p>Cod_livro</p><p>Telefone</p><p>Cod_livro Cod_unidade</p><p>Cod_livro Cod_unidade Numero_cartao Data_emprestimo Data_devolucao</p><p>Cod_unidade Endereco</p><p>Numero_cartao</p><p>USUARIO</p><p>Endereco TelefoneNome</p><p>Referências</p><p>1. ELMASRI R.; NAVATHE, S. Sistemas de banco de dados, Tradução da</p><p>7a. edição, Addison- Wesley, São Paulo, 2018.</p><p>2. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Database system</p><p>concepts. 7. ed. New York: McGraw Hill, 2020.</p><p>3. SILVA, I. R. (FACOM/UFU). Notas de aula.</p><p>Os materiais de parte desta seção foram gentilmente cedidos por Bruno A.</p><p>N. Travençolo (FACOM/UFU)</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>65</p><p>5 Introdução e acesso ao PostgreSQL</p><p>5.1 Introdução</p><p>PostgreSQL</p><p>• PostgreSQL6 é um SGBD que incorpora o modelo relacional para o banco</p><p>de dados e suporta a linguagem SQL como linguagem de consulta padrão.</p><p>– Em sua documentação7, descrito como SGBDOR (SGBD Objeto-</p><p>relacional).</p><p>• Ferramenta multi-plataforma: possui pacotes de instalação para o Windows</p><p>(64 bits somente a partir da versão 11); MacOS (amd64, arm64); e UNIX</p><p>(BSD, Linux e Solaris).</p><p>• É um software livre com código-fonte aberto (open-source).</p><p>Histórico</p><p>• Um banco de dados relacional chamado Ingres foi desenvolvido entre 1977</p><p>e 1985 na Universidade da Califórnia em Berkeley.</p><p>• Seu código foi usado para a construção do primeiro SGBD comercial.</p><p>6https://www.postgresql.org</p><p>7https://www.postgresql.org/docs/</p><p>66</p><p>– Atualmente, o Ingres é um software livre.</p><p>• Em 1986, um novo projeto na UCB foi iniciado: o POSTGRES, utilizando</p><p>várias ideias do Ingres.</p><p>• Em 1994, o POSTGRES passou receber suporte à SQL, mudando assim</p><p>sua denominação para Postgres95.</p><p>• Em 1996, passou a se chamar PostgreSQL e foi aberto a desenvolvedores</p><p>na rede.</p><p>• Em 2005: PostgreSQL 8.0 com facilidades de instalação (servidor nativo)</p><p>em Windows.</p><p>5.2 Arquitetura do PostgreSQL</p><p>• Assim como ocorre com vários SGBDs comerciais, o PostgreSQL pode ser</p><p>usado em um ambiente Cliente/Servidor:</p><p>• Existe um processo servidor que é executado numa única máquina;</p><p>• Os aplicativos que necessitam acessar os dados devem fazer via o processo</p><p>servidor;</p><p>• Os clientes não conseguem acessar o dado diretamente, mesmo quando</p><p>estão rodando no mesmo computador que o servidor.</p><p>• É possível que o cliente funcione numa arquitetura e o servidor noutra</p><p>(ex.: cliente Windows e o servidor Unix);</p><p>67</p><p>• Facilidades para acesso simultâneo.</p><p>• ODBC – Open Database Connectivity: API (interface de programação de</p><p>aplicações) que possibilita acesso de aplicativos ao BD, como por exemplo</p><p>o Excel/Access, aplicativos móveis, etc.</p><p>Arquitetura cliente/servidor</p><p>Banco de</p><p>dados</p><p>postgres</p><p>Múltiplos clientes</p><p>Cliente</p><p>O</p><p>D</p><p>BCWindows</p><p>Linux</p><p>UNIX Cliente</p><p>Servidor</p><p>Conecta</p><p>Acessa</p><p>Múltiplos acessos simultâneos</p><p>Clientes conectam-se ao</p><p>postgres, principal</p><p>processo do servidor. Um</p><p>processo específico é</p><p>criado para servir um</p><p>cliente</p><p>ODBC é uma</p><p>interface padrão</p><p>para</p><p>comunicação</p><p>com SGBDs</p><p>5.3 Acesso aos dados e conexão com o servidor</p><p>Acessando os dados em PostgreSQL</p><p>• Aplicativo em linha de comando (terminal): psql.</p><p>– É possível executar consultas SQL interativamente.</p><p>• Aplicação gráfica: pgAdmin (versão atual: 4).</p><p>– Utilizaremos o pgAdmin neste curso</p><p>• Servidor web: Apache rodando phpPgAdmin</p><p>• Criação de aplicativos clientes utilizando bibliotecas de acesso ao BD.</p><p>• Acesso indireto via ODBC ou JDBC (Java Database Connectivity).</p><p>68</p><p>Mais informações</p><p>https://www.postgresql.org/docs/current/app-postgres.</p><p>html</p><p>postgres</p><p>postgres – PostgreSQL database server</p><p>Synopsis</p><p>postgres [option...]</p><p>Description</p><p>postgres is the PostgreSQL database server. In order for a client application</p><p>to access a database it connects (over a network or locally) to a running postgres</p><p>instance. The postgres instance then starts a separate server process to handle the</p><p>connection.</p><p>One postgres instance always manages the data of exactly one database cluster.</p><p>A database cluster is a collection of databases that is stored at a common file system</p><p>location (the “data area”). More than one postgres instance can run on a system at</p><p>one time, so long as they use different data areas and different communication ports.</p><p>(...)</p><p>Conectando-se ao servidor</p><p>• No laboratório:</p><p>Host/Maquina: localhost</p><p>Username/Nome do usuário:</p><p>postgres</p><p>Password/Senha: root</p><p>Port/Porta: 5432 ou 5433</p><p>DB/BD: postgres</p><p>• Em sua máquina:</p><p>Host/Maquina: localhost</p><p>Username/Nome do usuário:</p><p>postgres</p><p>Password/Senha: a definida</p><p>na instalação</p><p>Port/Porta: 5432</p><p>DB/BD: postgres</p><p>69</p><p>No pgAdmin III (até 2016): • No laboratório:</p><p>Host/Maquina: localhost</p><p>Username/Nome do usuário:</p><p>postgres</p><p>Password/Senha: root</p><p>Port/Porta: 5432 ou 5433</p><p>DB/BD: postgres</p><p>• Em sua máquina:</p><p>Host/Maquina: localhost</p><p>Username/Nome do usuário:</p><p>postgres</p><p>Password/Senha: a definida</p><p>na instalação</p><p>Port/Porta: 5432</p><p>DB/BD: postgres</p><p>Selecionando o banco de dados</p><p>postgres é o BD</p><p>padrão,</p><p>mas é possível criar /</p><p>trabalhar com outros</p><p>70</p><p>Utilizando o pgAdmin III</p><p>Selecione um</p><p>esquema</p><p>(public ou outro)</p><p>Abra a interface</p><p>SQL</p><p>(Ctrl+E)</p><p>71</p><p>Digite seus</p><p>comandos SQL</p><p>Utilizando o pgAdmin 4</p><p>Abra a interface — ou Ferramentas /</p><p>Ferramenta de Consulta (em inglês:</p><p>Tools / Query Tool)</p><p>72</p><p>Digite seus comandos SQL</p><p>(F5) Executa todos os</p><p>comandos SQL que estão no</p><p>editor, ou executa os</p><p>comandos selecionados</p><p>Esquemas</p><p>• A organização do banco de dados em esquemas é útil pois:</p><p>– Permite o acesso do usuário aos objetos de qualquer esquema do</p><p>banco de dados que ele está conectado (diferente do caso de diferentes</p><p>bancos de dados);</p><p>– Permite organizar os objetos do banco de dados em grupos lógicos,</p><p>facilitando o gerenciamento;</p><p>– Permite vários usuários utilizarem um mesmo banco de dados sem</p><p>que um interfira no outro.</p><p>• “(...) There is nothing special about the public schema except that it</p><p>exists by default. It can be dropped, too.”</p><p>73</p><p>Manipulando esquemas</p><p>-- criando esquemas</p><p>create schema universidade;</p><p>create schema farmacia;</p><p>create schema oficina;</p><p>-- usando os esquemas</p><p>set search_path to universidade;</p><p>set search_path to farmacia;</p><p>set search_path to oficina;</p><p>-- apagando *todas* as tabelas e o esquema</p><p>drop schema cinema cascade;</p><p>drop schema grupo01 cascade;</p><p>drop schema public cascade;</p><p>Referências</p><p>1. FREECODECAMP.ORG. Learn PostgreSQL Tutorial – Full Course for</p><p>Beginners. Youtube, 04 abr. 2019. Disponível em: <https://www.yo</p><p>utube.com/watch?v=qw--VYLpxG4>. Acesso em: 27 out. 2022.</p><p>2. PostgreSQL: The world’s most advanced open source database. Disponível</p><p>em: <https://www.postgresql.org/>. Acesso em: 27 out. 2022.</p><p>3. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Os materiais de parte desta seção foram gentilmente cedidos por Bruno A.</p><p>N. Travençolo (FACOM/UFU)</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>74</p><p>6 SQL DDL – Comandos para definição de dados</p><p>6.1 Introdução</p><p>SQL – Structured Query Language</p><p>• Desenvolvida e implementada pelo laboratório de pesquisa da IBM em</p><p>San Jose – 1974.</p><p>• Inicialmente chamada de SEQUEL (Structured English QUEry Language).</p><p>• Criada como interface entre usuários e o primeiro SGBDR – SYSTEM R.</p><p>• Uma das mais importantes linguagens relacionais.</p><p>• Exemplos de SGBD que utilizam SQL:</p><p>– Oracle</p><p>– Informix</p><p>– Ingress</p><p>– MS SQL Server</p><p>– Interbase/Firebird</p><p>– Sybase</p><p>– DB2</p><p>– MySQL</p><p>– PostgreSQL</p><p>• Atrativo: pequena quantidade de comandos para realizar todas as operações</p><p>necessárias para definição e manipulação de relações:</p><p>– Simplicidade;</p><p>– Grande poder de consulta.</p><p>• Padrão facilita migração entre SGBDRs.</p><p>O padrão SQL</p><p>• Padronização: esforço conjunto de American National Standard Institute</p><p>(ANSI) e International Organization for Standardization (ISO).</p><p>• Versão mais recente: SQL 2016.8</p><p>• Versões anteriores:</p><p>– SQL:2011</p><p>8Padrão ISO/IEC 9075-1:2016 (https://www.iso.org/standard/63555.html)</p><p>75</p><p>– SQL:2008</p><p>– SQL:2006</p><p>– SQL:2003</p><p>– SQL:1999 (SQL3)</p><p>– SQL-92 (SQL2)</p><p>– SQL-86 (primeira padronização ANSI)</p><p>6.2 Composição do SQL</p><p>Linguagem de Definição dos Dados (DDL – Data definition language)</p><p>Comandos para a definição, a modificação e a remoção de relações, além da</p><p>criação e da remoção de índices.</p><p>Linguagem Interativa de Manipulação dos Dados (DML – Data manipulation</p><p>language)</p><p>Comandos para a consulta, a inserção, a remoção e a modificação de tuplas no</p><p>banco de dados.</p><p>• Linguagem de Manipulação dos Dados Embutida</p><p>– Pode ser utilizada a partir de linguagens de programação de propósito</p><p>geral</p><p>• Definição de visões:</p><p>– SQL DDL inclui comandos para a criação e a remoção de visões.</p><p>• Restrições de integridade:</p><p>– SQL DDL possui comandos para a especificação de restrições de</p><p>integridade.</p><p>• Autorização:</p><p>– SQL DDL inclui comandos para a especificação de direitos de acesso</p><p>a relações e visões.</p><p>• Gerenciamento de transações:</p><p>– Introduz comandos para a especificação do início e do fim das transações.</p><p>• Recuperação de falhas:</p><p>– introduz comandos para utilização do arquivo de log (registro de</p><p>erros).</p><p>76</p><p>6.3 DDL – comandos para definição de dados</p><p>• Em SQL, usam-se os termos tabela, linha e coluna para os termos do</p><p>modelo relacional relação, tupla e atributo:</p><p>No MR Em SQL</p><p>relação tabela</p><p>tupla linha</p><p>atributo coluna</p><p>• DDL trabalha com três comandos básicos para definição de dados:</p><p>– CREATE – Cria uma definição de dados.</p><p>Ex.: CREATE TABLE tab ...</p><p>– ALTER – Altera uma definição.</p><p>Ex.: ALTER TABLE tab ADD ...</p><p>– DROP – Exclui uma definição.</p><p>Ex.: DROP TABLE tab.</p><p>Exemplo</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL,</p><p>4 DNumero INT NOT NULL,</p><p>5 CPF_gerente CHAR(11) NOT NULL,</p><p>6 Data_inicio_gerente DATE,</p><p>7 CONSTRAINT dptopk PRIMARY KEY (DNumero),</p><p>8 UNIQUE(DNome),</p><p>9 CONSTRAINT dptogerfk FOREIGN KEY (CPF_gerente)</p><p>REFERENCES FUNCIONARIO(CPF)</p><p>10 );</p><p>Identificadores</p><p>• Iniciam com letras (a–z) ou underscore/traço baixo (_).</p><p>– Caracteres subsequentes: letras, dígitos (0–9), _</p><p>77</p><p>• Identificadores e palavras-chave não são case-sensitive – ao contrário, por</p><p>exemplo, da linguagem C. Os comandos a seguir são equivalentes:</p><p>UPDATE MY_TABLE SET A = 5;</p><p>uPDaTE my_TabLE SeT a = 5;</p><p>• Convenção adotada:</p><p>UPDATE my_table SET a = 5;</p><p>– Palavras-chave em maiúsculas;</p><p>– Identificadores em minúsculas.</p><p>• Identificadores com aspas duplas: aceitam quaisquer caracteres (inclusive</p><p>palavras-chave).</p><p>UPDATE "my table" SET a = 5;</p><p>SELECT * FROM "INDEX";</p><p>• Ao colocar aspas num identificador ele torna-se case-sensitive;</p><p>• Identificadores sem aspas são sempre transformados em minúsculo (embora</p><p>o padrão SQL defina que se transforme em maiúscula);</p><p>• Se você criar um esquema ou tabela usando a interface gráfica do pgAdmin</p><p>e, caso o identificador deste objeto não seja composto por letras minúsculas,</p><p>o objeto será identificado somente por meio de aspas.</p><p>– Faça o teste, criando esquemas e tabelas por meio da interface gráfica</p><p>e utilizando letras maiúsculas.</p><p>• Mais informações e referência: https://www.postgresql.org/doc</p><p>s/current/sql-syntax-lexical.html</p><p>CREATE TABLE</p><p>• Cria uma tabela, seus atributos, e as respectivas restrições.</p><p>• Sintaxe:</p><p>1 CREATE TABLE <nome da tabela> (</p><p>2 <definição de coluna 1></p><p>3 ....</p><p>4 ....</p><p>5 <definição de coluna N></p><p>6 <restrições de integridade></p><p>7 );</p><p>• Onde <definição de coluna> pode ser: <nome atributo> <tipo de dado></p><p><restrições de integridade>.</p><p>78</p><p>Tipos de dados</p><p>• Lógico ou booleano (mais em https://www.postgresql.org/doc</p><p>s/15/datatype-boolean.html)</p><p>Nome</p><p>em SQL</p><p>Nome alternativo</p><p>no PostgreSQL Notas</p><p>boolean bool Armazena valor lógico. Aceita</p><p>valores como TRUE, 't','</p><p>true', 'y', 'yes', e '1'</p><p>para verdadeiro, e equivalentes</p><p>como FALSE, etc., para falso.</p><p>Consome 1 byte, e pode</p><p>armazenar NULL.</p><p>• Valores inteiros (mais em https://www.postgresql.org/docs/cu</p><p>rrent/datatype-numeric.html)</p><p>Nome</p><p>em SQL</p><p>Nome alternativo</p><p>no PostgreSQL Notas</p><p>smallint int2 Inteiro com sinal de 2 bytes. Pode</p><p>representar valores entre −32768 e</p><p>32767.</p><p>integer,int int4 Inteiro com sinal de 4 bytes.</p><p>Pode representar valores entre</p><p>−2147483648 e 2147483647.</p><p>bigint int8 Inteiro com sinal de 8 bytes. Pode</p><p>representar valores entre −263 e 263 −</p><p>1.</p><p>bit Armazena único bit, 0 ou 1. Exemplo:</p><p>INSERT INTO ... VALUES(B'1')</p><p>;</p><p>bit varying(n) varbit(n) Armazena uma string de n bits.</p><p>Exemplo: INSERT INTO ...</p><p>VALUES(B'011101');</p><p>• Valores reais (mais em https://www.postgresql.org/docs/curr</p><p>ent/datatype-numeric.html)</p><p>79</p><p>Nome</p><p>em SQL</p><p>Nome alternativo</p><p>no PostgreSQL Notas</p><p>numeric(P,E) decimal(P,E) Ponto fixo com P – de precisão</p><p>– dígitos no total, sendo E (de</p><p>escala) após o ponto. Ex.:</p><p>numeric(3,1): valores de</p><p>−99.9 a 99.9.</p><p>real float4 Ponto flutuante de 4 bytes</p><p>(precisão simples), como na</p><p>linguagem C.</p><p>double</p><p>precision</p><p>float8 Ponto flutuante de 8 bytes</p><p>(precisão dupla), como na</p><p>linguagem C.</p><p>money Equivalente a numeric(9,2),</p><p>usando 4 bytes.</p><p>• Dados temporais (mais em https://www.postgresql.org/docs/</p><p>current/datatype-datetime.html).</p><p>Nome</p><p>em SQL</p><p>Nome alternativo</p><p>no PostgreSQL Notas</p><p>timestamp Armazena datas e horas de</p><p>4713 a.C. até 294276 d.C., com</p><p>resolução de 1 microssegundo.</p><p>interval Intervalo de tempo, entre</p><p>±178000000 anos, com reso-</p><p>lução de 1 microssegundo.</p><p>date Datas, de 4713 a.C. até 5874897</p><p>d.C., com resolução de 1 dia</p><p>(sem horário).</p><p>time Hora do dia, de 00:00:00 até</p><p>23:59:59, com resolução de 1</p><p>microssegundo (sem data).</p><p>• Caracteres (mais em https://www.postgresql.org/docs/15/dat</p><p>atype-character.html)</p><p>80</p><p>Nome</p><p>em SQL</p><p>Nome alternativo</p><p>no PostgreSQL Notas</p><p>character char Armazena apenas um único</p><p>caractere.</p><p>character(n) char(n) Armazena exatamente n</p><p>caracteres. Caracteres em</p><p>branco são preenchidos nas</p><p>posições restantes, quando não</p><p>utilizadas.</p><p>character</p><p>varying(n)</p><p>varchar(n) Armazena uma quantidade va-</p><p>riável de até n caracteres.</p><p>text Exclusivo do PostgreSQL;</p><p>corresponde a um varchar</p><p>ilimitado.</p><p>• Existem outros tipos de dados além dos apresentados anteriormente. Consulte</p><p>o manual do PostgreSQL: https://www.postgresql.org/docs/cu</p><p>rrent/datatype.html</p><p>• Adaptado de: MATTHEW, N.; STONES, R.: Beginning databases with</p><p>PostgreSQL. 2. ed. Berkeley: Apress, 2005.</p><p>Exemplo</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL,</p><p>4 DNumero INT NOT NULL,</p><p>5 CPF_gerente CHAR(11) NOT NULL,</p><p>6 Data_inicio_gerente DATE,</p><p>7 CONSTRAINT dptopk PRIMARY KEY (DNumero),</p><p>8 UNIQUE(DNome),</p><p>9 CONSTRAINT dptogerfk FOREIGN KEY (CPF_gerente)</p><p>REFERENCES FUNCIONARIO(CPF)</p><p>10 );</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-createtable.html</p><p>CREATE [[GLOBAL|LOCAL]{TEMPORARY|TEMP}|UNLOGGED] TABLE [IF NOT EXISTS]</p><p>table_name ( [</p><p>{ column_name</p><p>data_type [COLLATE collation] [column_constraint [ ... ]]</p><p>| table_constraint</p><p>| LIKE source_table [ like_option ... ] }</p><p>[, ... ]</p><p>81</p><p>] )</p><p>[ INHERITS ( parent_table [, ... ] ) ]</p><p>[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS|WITHOUT</p><p>OIDS]</p><p>[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]</p><p>[ TABLESPACE tablespace_name ]</p><p>Continua ...</p><p>Como ler a sintaxe da documentação</p><p>Convenção</p><p>UPPERCASE</p><p>(maiúsculas)</p><p>Palavra-chave SQL.</p><p>lowercase</p><p>(minúsculas)</p><p>Identificadores ou constantes SQL informadas pelo usuário</p><p>itálico Nome de um bloco de sintaxe. Essa convenção é usada para</p><p>indicar blocos longos de sintaxe que podem ser usados em mais</p><p>de um local.</p><p>| (barra vertical) Separa elementos opcionais da sintaxe dentro de colchetes ou</p><p>chaves. Somente um dos itens pode ser escolhido.</p><p>[ ] (colchetes) Item de sintaxe opcional. Os colchetes não fazem parte do</p><p>comando.</p><p>{ } (chaves) Item da sintaxe obrigatório. As chaves não fazem parte do</p><p>comando.</p><p>[,...] O item precedente pode ser repetido N vezes. A separação entre</p><p>os itens é feita por uma vírgula.</p><p>[ ...] O item precedente pode ser repetido N vezes. A separação entre</p><p>os itens é feita por um espaço em branco.</p><p>CREATE TABLE – column_constraint</p><p>Especificando restrição em frente à coluna:</p><p>(continuação)</p><p>where column_constraint is:</p><p>[ CONSTRAINT constraint_name ]</p><p>{ NOT NULL |</p><p>NULL |</p><p>CHECK ( expression ) [ NO INHERIT ] |</p><p>DEFAULT default_expr |</p><p>UNIQUE index_parameters |</p><p>PRIMARY KEY index_parameters |</p><p>REFERENCES reftable [ ( refcolumn ) ]</p><p>[MATCH FULL|MATCH PARTIAL|MATCH SIMPLE ]</p><p>[ ON DELETE action ] [ ON UPDATE action ]</p><p>}</p><p>[DEFERRABLE | NOT DEFERRABLE][INITIALLY DEFERRED | INITIALLY IMMEDIATE]</p><p>82</p><p>• Restrição de chave primária (PRIMARY KEY) na coluna;</p><p>• Restrição de chave estrangeira (FOREIGN KEY) na coluna;</p><p>– Observe que a palavra chave REFERENCES é usada.</p><p>• Restrição de unicidade (UNIQUE) na coluna.</p><p>Exemplo:</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL UNIQUE,</p><p>4 DNumero INT NOT NULL PRIMARY KEY,</p><p>5 CPF_gerente CHAR(11) NOT NULL REFERENCES FUNCIONARIO(</p><p>CPF),</p><p>6 Data_inicio_gerente DATE</p><p>7 );</p><p>Adicionando nomes às restrições:</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL CONSTRAINT uqdnome UNIQUE,</p><p>4 DNumero INT NOT NULL CONSTRAINT dptopk PRIMARY KEY,</p><p>5 CPF_gerente CHAR(11) NOT NULL CONSTRAINT dptogerfk</p><p>REFERENCES FUNCIONARIO(CPF),</p><p>6 Data_inicio_gerente DATE</p><p>7 );</p><p>CREATE TABLE – table_constraint</p><p>Especificando restrição na tabela:</p><p>Observe a mudança na sintaxe de algumas restrições (de chave primária e</p><p>estrangeira):</p><p>and table_constraint is:</p><p>[ CONSTRAINT constraint_name ]</p><p>{ CHECK ( expression ) [ NO INHERIT ] |</p><p>UNIQUE ( column_name [, ... ] ) index_parameters |</p><p>PRIMARY KEY ( column_name [, ... ] ) index_parameters |</p><p>FOREIGN KEY ( column_name [, ... ] )</p><p>REFERENCES reftable [ ( refcolumn [, ... ] ) ]</p><p>[MATCH FULL| MATCH PARTIAL | MATCH SIMPLE ]</p><p>[ ON DELETE action ] [ ON UPDATE action ]</p><p>}</p><p>[DEFERRABLE|NOT DEFERRABLE][INITIALLY DEFERRED|INITIALLY IMMEDIATE]</p><p>83</p><p>• Restrição de chave primária (PRIMARY KEY) na tabela;</p><p>• Restrição de chave estrangeira (FOREIGN KEY) na tabela;</p><p>• Restrição de unicidade (UNIQUE) na tabela</p><p>Exemplo:</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL,</p><p>4 DNumero INT NOT NULL,</p><p>5 CPF_gerente CHAR(11) NOT NULL,</p><p>6 Data_inicio_gerente DATE,</p><p>7 PRIMARY KEY (DNumero),</p><p>8 UNIQUE(DNome),</p><p>9 FOREIGN KEY (CPF_gerente) REFERENCES FUNCIONARIO(CPF)</p><p>10 );</p><p>Adicionando nomes às restrições:</p><p>1 /* Cria tabela departamento */</p><p>2 CREATE TABLE DEPARTAMENTO (</p><p>3 DNome VARCHAR(15) NOT NULL,</p><p>4 DNumero INT NOT NULL,</p><p>5 CPF_gerente CHAR(11) NOT NULL,</p><p>6 Data_inicio_gerente DATE,</p><p>7 CONSTRAINT dptopk PRIMARY KEY (DNumero),</p><p>8 CONSTRAINT uqdnome UNIQUE(DNome),</p><p>9 CONSTRAINT dptogerfk FOREIGN KEY (CPF_gerente)</p><p>REFERENCES FUNCIONARIO(CPF)</p><p>10 );</p><p>ALTER TABLE</p><p>• Altera as definições de campos e de restrições.</p><p>• Sintaxe:</p><p>1 ALTER TABLE <nome da tabela></p><p>2 ADD <definição de Coluna></p><p>3 ADD <Restrição de integridade> -- Chaves primárias,</p><p>estrangeiras</p><p>4 ALTER <definição de Coluna></p><p>5 ALTER <definição de Coluna> DEFAULT <default-value></p><p>6 ALTER <definição de Coluna> [ NOT ] NULL</p><p>84</p><p>7 DROP <definição de Coluna></p><p>8 DROP CONSTRAINT <nome da restrição> -- Remove uma restrição</p><p>9 RENAME TO <novo nome> -- Renomeia a tabela</p><p>10 RENAME <Atributo> TO <novo atributo></p><p>11</p><p>12 Onde <definição de coluna> pode ser:</p><p>13 <Nome Atributo> <Tipo de Dado> [NULL ] |</p><p>14 [ DEFAULT default-value</p><p>] -- não vale [NOT NULL]</p><p>Exemplos</p><p>• ALTER TABLE FUNCIONARIO ADD COLUMN CorCabelos CHAR(25)</p><p>DEFAULT 'Branco';</p><p>• ALTER TABLE FUNCIONARIO ADD Altura INT DEFAULT NULL;</p><p>• ALTER TABLE FUNCIONARIO DROP Altura;</p><p>• ALTER TABLE FUNCIONARIO ALTER TYPE CorCabelos CHAR(30)</p><p>;</p><p>• ALTER TABLE DEPARTAMENTO ADD VICECPF CHAR(11)REFERENCES</p><p>FUNCIONARIO (CPF)ON UPDATE CASCADE ON DELETE SET NULL</p><p>;</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-altertable.html</p><p>ALTER TABLE [ ONLY ] name [ * ]</p><p>action [, ... ]</p><p>ALTER TABLE [ ONLY ] name [ * ]</p><p>RENAME [ COLUMN ] column TO new_column</p><p>ALTER TABLE name</p><p>RENAME TO new_name</p><p>ALTER TABLE name</p><p>SET SCHEMA new_schema</p><p>where action is one of:</p><p>ADD [ COLUMN ] column type [ column_constraint [ ... ] ]</p><p>DROP [ COLUMN ] column [ RESTRICT | CASCADE ]</p><p>ALTER [ COLUMN ] column [ SET DATA ] TYPE type [ USING expression ]</p><p>ALTER [ COLUMN ] column SET DEFAULT expression</p><p>ALTER [ COLUMN ] column DROP DEFAULT</p><p>ALTER [ COLUMN ] column { SET | DROP } NOT NULL</p><p>ALTER [ COLUMN ] column SET STATISTICS integer</p><p>ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED |</p><p>MAIN }</p><p>ADD table_constraint</p><p>DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]</p><p>85</p><p>DISABLE TRIGGER [ trigger_name | ALL | USER ]</p><p>ENABLE TRIGGER [ trigger_name | ALL | USER ]</p><p>ENABLE REPLICA TRIGGER trigger_name</p><p>ENABLE ALWAYS TRIGGER trigger_name</p><p>(continuação)</p><p>DISABLE RULE rewrite_rule_name</p><p>ENABLE RULE rewrite_rule_name</p><p>ENABLE REPLICA RULE rewrite_rule_name</p><p>ENABLE ALWAYS RULE rewrite_rule_name</p><p>CLUSTER ON index_name</p><p>SET WITHOUT CLUSTER</p><p>SET WITH OIDS</p><p>SET WITHOUT OIDS</p><p>SET ( storage_parameter = value [, ... ] )</p><p>RESET ( storage_parameter [, ... ] )</p><p>INHERIT parent_table</p><p>NO INHERIT parent_table</p><p>OWNER TO new_owner</p><p>SET TABLESPACE new_tablespace</p><p>DROP TABLE</p><p>• Exclui uma tabela existente de um banco de dados. Não pode ser excluída</p><p>a tabela que possui alguma referência. Neste caso, deve-se primeiro excluir</p><p>a tabela que possui algum campo que a está referenciando – ou eliminar</p><p>a(s) referências – e depois excluir a tabela inicial.</p><p>• Sintaxe:</p><p>DROP TABLE <nome da tabela></p><p>• Exemplo:</p><p>1 /* Apaga tabela Departamento */</p><p>2 DROP TABLE DEPARTAMENTO;</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-droptable.html</p><p>DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]</p><p>86</p><p>• CASCADE: Se a tabela sendo excluída é referenciada por uma chave estrangeira</p><p>de alguma outra tabela, este parâmetro elimina a referência (exclui a</p><p>restrição de chave estrangeira dessa outra tabela).</p><p>• RESTRICT: recusa a exclusão da tabela se existem dependências ou referências</p><p>(caso padrão).</p><p>Adicionando/removendouma restrição (exemplos)</p><p>• Adição de restrição a uma tabela pré-existente:</p><p>ALTER TABLE FUNCIONARIO ADD CONSTRAINT funcpk PRIMARY</p><p>KEY (CPF);</p><p>• Remoção de restrição a uma tabela pré-existente:</p><p>ALTER TABLE FUNCIONARIO DROP CONSTRAINT funcpk;</p><p>Mais exemplos</p><p>1 CREATE TABLE tabela ( valor FLOAT);</p><p>2 INSERT INTO tabela VALUES (1.23),(1.50),(1.49),(12.74);</p><p>3 SELECT * FROM tabela;</p><p>4 ALTER TABLE tabela ALTER COLUMN valor TYPE INT;</p><p>5 SELECT * FROM tabela;</p><p>87</p><p>Se a conversão for bem sucedida, ao alterar o domínio, os valores que estão</p><p>na tabela são atualizados.</p><p>Retornando à tabela inicial (dados do tipo FLOAT):</p><p>4 ALTER TABLE tabela ALTER COLUMN valor TYPE VARCHAR;</p><p>5 SELECT * FROM tabela;</p><p>Se a conversão for bem sucedida, ao alterar o domínio, os valores que estão</p><p>na tabela são atualizados.</p><p>Novamente retornando à tabela inicial (dados do tipo FLOAT):</p><p>4 ALTER TABLE tabela ALTER COLUMN valor TYPE DATE;</p><p>5 SELECT * FROM tabela;</p><p>Se a conversão for mal sucedida, é gerada uma mensagem de erro.</p><p>6.4 Exercícios</p><p>1. (ELMASRI; NAVATHE, 2018) Considere o banco dado pelas tabelas que</p><p>seguem. Quais são as restrições de integridade referencial a serem mantidas</p><p>no esquema? Escreva as instruções DDL da SQL apropriadas para definir</p><p>o banco de dados.</p><p>ALUNO</p><p>Nome Numero_aluno Tipo_aluno Curso</p><p>Silva 17 1 CC</p><p>Braga 8 2 CC</p><p>88</p><p>DISCIPLINA</p><p>Nome_disciplina Numero_disciplina Creditos Departamento</p><p>Introdução à ciência</p><p>da computação CC1310 4 CC</p><p>Estruturas de dados CC3320 4 CC</p><p>Matemática discreta MAT2410 3 MAT</p><p>Banco de dados CC3380 3 CC</p><p>TURMA</p><p>Identificador_turma Numero_disciplina Semestre Ano Professor</p><p>85 MAT2410 Segundo 07 Kleber</p><p>92 CC1310 Segundo 07 Anderson</p><p>102 CC3320 Primeiro 08 Carlos</p><p>112 MAT2410 Segundo 08 Chang</p><p>119 CC1310 Segundo 08 Anderson</p><p>135 CC3380 Segundo 08 Santos</p><p>REGISTRO_NOTA</p><p>Numero_aluno Identificador_turma Nota</p><p>17 112 B</p><p>17 119 C</p><p>8 85 A</p><p>8 92 A</p><p>8 102 B</p><p>8 135 A</p><p>PRE_REQUISITO</p><p>Numero_disciplina Numero_pre_requisito</p><p>CC3380 CC3320</p><p>CC3380 MAT2410</p><p>CC3320 CC1310</p><p>89</p><p>2. Adaptado de (ELMASRI; NAVATHE, 2018). Faça o mesmo a partir do</p><p>esquema que segue:</p><p>LIVRO</p><p>Cod_livro Titulo Nome_editora</p><p>AUTORES_LIVRO</p><p>Nome_autor</p><p>EDITORA</p><p>Nome Endereco</p><p>COPIAS_LIVRO</p><p>Total_copia</p><p>EMPRESTIMOS_LIVRO</p><p>UNIDADE_BIBLIOTECA</p><p>Nome_unidade</p><p>Cod_livro</p><p>Telefone</p><p>Cod_livro Cod_unidade</p><p>Cod_livro Cod_unidade Numero_cartao Data_emprestimo Data_devolucao</p><p>Cod_unidade Endereco</p><p>Numero_cartao</p><p>USUARIO</p><p>Endereco TelefoneNome</p><p>Referências</p><p>1. ELMASRI R.; NAVATHE, S. Sistemas de banco de dados, Tradução da</p><p>7a. edição, Addison-Wesley, São Paulo, 2018.</p><p>2. MATTHEW, N.; STONES, R.: Beginning databases with PostgreSQL. 2.</p><p>ed. Berkeley: Apress, 2005.</p><p>3. PostgreSQL: The world’s most advanced open source database. Disponível</p><p>em: <https://www.postgresql.org/>. Acesso em: 27 out. 2022.</p><p>4. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Slides adaptados da aula da Profa. Josiane M. Bueno (in memoriam)</p><p>e Prof. Humberto L. Razente (FACOM/UFU). Material gentilmente cedido</p><p>por Bruno A. N. Travençolo (FACOM/UFU). Adaptações: Renato Pimentel,</p><p>FACOM/UFU</p><p>90</p><p>7 SQL DML – Comandos para manipulação de</p><p>dados</p><p>7.1 Introdução</p><p>Linguagem de Definição dos Dados (DDL – Data definition language)</p><p>Comandos para a definição, a modificação e a remoção de relações, além da</p><p>criação e da remoção de índices.</p><p>Linguagem Interativa de Manipulação dos Dados (DML – Data manipulation</p><p>language)</p><p>Comandos para a consulta, a inserção, a remoção e a modificação de tuplas no</p><p>banco de dados.</p><p>7.2 Comando INSERT</p><p>Consideremos a tabela a seguir como referência9:</p><p>1 CREATE TABLE produtos (</p><p>2 cod_prod INTEGER,</p><p>3 nome TEXT,</p><p>4 preco NUMERIC</p><p>5 );</p><p>• Para inserir uma tupla (linha):</p><p>INSERT INTO produtos VALUES (1, 'Queijo', 9.99);</p><p>– Dados colocados na ordem de criação das colunas da tabela.</p><p>– É necessário, neste caso, conhecer a ordem das colunas.</p><p>∗ atenção, pois esta ordem pode ser alterada.</p><p>• Outra forma:</p><p>INSERT INTO produtos (cod_prod, nome, preco) VALUES</p><p>(1, 'Queijo', 9.99);</p><p>– Uma opção possível, com a mesma sintaxe:</p><p>INSERT INTO produtos (nome, preco, cod_prod)</p><p>VALUES ('Queijo', 9.99, 1);</p><p>• Essa sintaxe é fortemente recomendada.</p><p>9https://pgdocptbr.sourceforge.io/pg80/dml.html</p><p>91</p><p>– A ordem das colunas de uma tabela pode ser alterada em uma eventual</p><p>manutenção no BD.</p><p>– É possível inclusive omitir uma coluna:</p><p>INSERT INTO produtos (cod_prod, nome) VALUES (1,</p><p>'Queijo');</p><p>∗ As colunas não indicadas são preenchidas com seus respectivos</p><p>valores DEFAULT.</p><p>• Outra forma de especificar valores padrão é o uso da cláusula DEFAULT</p><p>de forma explícita:</p><p>INSERT INTO produtos (cod_prod, nome, preco) VALUES</p><p>(1, 'Queijo', DEFAULT);</p><p>• Desta forma, é possível incluir uma tupla com todos os valores DEFAULT:</p><p>INSERT INTO produtos DEFAULT VALUES;</p><p>• Inserindo múltiplas linhas na tabela:</p><p>1 INSERT INTO produtos (cod_prod, nome, preco) VALUES</p><p>2 (1, 'Queijo', DEFAULT),</p><p>3 (2, 'Goiabada', 10.00)</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-insert.html</p><p>INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]</p><p>[ OVERRIDING { SYSTEM | USER } VALUE ]</p><p>{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [,</p><p>...] | query }</p><p>[ ON CONFLICT [ conflict_target ] conflict_action ]</p><p>[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]</p><p>7.3 Comando UPDATE</p><p>• A operação de modificação/atualização de</p><p>dados que já se encontram no</p><p>banco de dados é realizada pelo comando UPDATE.10</p><p>• Para atualizar alguma tupla é necessário informar:</p><p>10https://pgdocptbr.sourceforge.io/pg80/dml-update.html</p><p>92</p><p>– O nome da tabela e da coluna a ser atualizada;</p><p>– O novo valor para a coluna;</p><p>– Quais tuplas (linhas) serão atualizadas.</p><p>• Exemplo: o comando</p><p>UPDATE produtos SET preco = 10 WHERE preco = 5;</p><p>atualiza a coluna preco da tabela produtos, modificando o preço para</p><p>10 das linhas que possuem preço igual a 5.</p><p>• Outro exemplo: o comando</p><p>UPDATE produtos SET preco = preco*1.10;</p><p>atualiza a coluna preco da tabela produtos, aumentando o preço em</p><p>10% para todos os itens.</p><p>• É possível atualizar mais de uma coluna:</p><p>UPDATE minha_tabela SET a = 5, b = 3, c = 1 WHERE a ></p><p>0;</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-update.html</p><p>UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]</p><p>SET { column_name = { expression | DEFAULT } |</p><p>( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT }</p><p>[, ...] ) |</p><p>( column_name [, ...] ) = ( sub-SELECT )</p><p>} [, ...]</p><p>[ FROM from_item [, ...] ]</p><p>[ WHERE condition | WHERE CURRENT OF cursor_name ]</p><p>[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]</p><p>Cuidado</p><p>Sempre teste a cláusula WHERE de um comando UPDATE antes de executá-lo.</p><p>Um simples erro no WHERE pode resultar na alteração de muitas, senão todas,</p><p>linhas da tabela, atribuindo a elas o mesmo valor.</p><p>93</p><p>7.4 Comando DELETE</p><p>• Para deletar dados do banco, usa-se o comando DELETE.</p><p>• Por exemplo11, o comando</p><p>DELETE FROM produtos WHERE preco = 10;</p><p>remove todas as tuplas cujos produtos tem custo igual a 10.</p><p>• Para remover todas as tuplas da tabela produtos, basta usar o comando</p><p>DELETE FROM produtos;</p><p>Sintaxe completa: consultar o manual do PostgreSQL</p><p>https://www.postgresql.org/docs/current/sql-delete.html</p><p>DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]</p><p>[ USING from_item [, ...] ]</p><p>[ WHERE condition | WHERE CURRENT OF cursor_name ]</p><p>[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]</p><p>Cuidado</p><p>É muito fácil apagar os dados das tabelas.</p><p>7.5 Observações</p><p>Inserindo campos de data</p><p>Consideremos a tabela a seguir como referência:</p><p>1 DROP TABLE IF EXISTS tab;</p><p>2 CREATE TABLE tab (d DATE);</p><p>• Exemplos:</p><p>1 INSERT INTO tab(d) VALUES ('11/12/2022'); -- ok,</p><p>funciona, mas qual o mês? 11 ou 12?</p><p>2</p><p>3 INSERT INTO tab(d) VALUES ('30/12/2022'); -- pode</p><p>funcionar ou não</p><p>4</p><p>5 -- tentativa de inserir a mesma data, só que em outro</p><p>formato (mês, dia, ano)</p><p>11https://pgdocptbr.sourceforge.io/pg80/dml-delete.html</p><p>94</p><p>6 INSERT INTO tab(d) VALUES ('12/30/2022'); -- ERRO:</p><p>valor do campo date/time está fora do intervalo:</p><p>"12/30/2022"</p><p>• É possível verificar/configurar o formato usado pelo PostgreSQL.</p><p>• O formato atual pode ser encontrado no arquivo de configurações do</p><p>SGBD, o postgresql.conf (dentro da pasta data da instalação).</p><p>– Exemplo de caminho para o arquivo: C:\Program Files\</p><p>PostgreSQL\15\data\postgresql.conf</p><p>• Dentro do arquivo, haverá uma linha possivelmente mostrando o valor</p><p>padrão, i.e.</p><p>datestyle = ’iso, dmy’</p><p>• Também é possível verificar o formato usando o comando</p><p>SHOW datestyle;</p><p>• Para mudar a configuração, por exemplo, para o padrão mdy (mês-dia-</p><p>ano):</p><p>SET datestyle TO 'mdy'; --para conferir a mudança, basta</p><p>executar SHOW datestyle;</p><p>A alteração para o padrão mdy permitirá a execução do último comando de</p><p>inserção normalmente:</p><p>1 INSERT INTO tab(d) VALUES ('12/30/2022'); -- agora está</p><p>ok!</p><p>• É possível inserir dados independentemente do tipo configurado no PostgreSQL.</p><p>Para isso, use o comando to_date:</p><p>1 -- inserindo o dia 30 de dezembro em dois formatos</p><p>diferentes</p><p>2 INSERT INTO tab(d) VALUES (to_date('12/30/2022','MM/</p><p>DD/YYYY'));</p><p>3 INSERT INTO tab(d) VALUES (to_date('30/12/2022','DD/</p><p>MM/YYYY'));</p><p>4 -- outra opção (a melhor, pois não é ambígua): usar o</p><p>timestamp</p><p>5 INSERT INTO tab(d) VALUES ('2022-12-30');</p><p>95</p><p>• Comando EXTRACT: para extrair subcampos da data (ex.: ano):</p><p>SELECT EXTRACT(YEAR FROM datanasc) FROM funcionario;</p><p>• Outras opções:</p><p>• CENTURY, DAY, DOW – dia da semana, de 0 (domingo) a 6 (sábado) –,</p><p>MONTH, etc.</p><p>– Mais detalhes: https://www.postgresql.org/docs/15/fun</p><p>ctions-datetime.html#FUNCTIONS-DATETIME-EXTRACT</p><p>Lidando com o desconhecido: NULL</p><p>• Quando não sabemos qual o valor preencher em um determinado atributo,</p><p>podemos utilizar um valor especial chamado NULL.</p><p>– NULL significa que o valor é desconhecido no momento.</p><p>∗ Ex.: hora da chegada de um voo que ainda está no ar.</p><p>– Ou pode significar algo não relevante para uma determinada tupla.</p><p>∗ Ex.: Número do apartamento no cadastro do endereço. Muitas</p><p>pessoas não moram em prédios.</p><p>• NULL não quer dizer 0 (zero);</p><p>• NULL não quer dizer '' (string vazia).</p><p>• Qual o resultado da comparação de dois valores NULL?</p><p>É indeterminado, ou seja NULL.</p><p>SELECT NULL = NULL</p><p>• Algumas operações com NULL também são indefinidas:</p><p>SELECT 2 + NULL</p><p>• SQL provê uma maneira para fazer testes com NULL, por meio das expressões</p><p>IS NULL e IS NOT NULL.</p><p>– Ex.: buscar no banco quais as pessoas que não possuem nome do</p><p>meio.</p><p>– Forma errada (consulta retorna vazio):</p><p>SELECT * FROM funcionario WHERE minicial = NULL;</p><p>– Forma correta:</p><p>SELECT * FROM funcionario WHERE minicial IS NULL;</p><p>– Exercício: como seria a busca de pessoas que possuem o nome do</p><p>meio?</p><p>96</p><p>7.6 Exercícios</p><p>1. Adaptado de (ELMASRI; NAVATHE, 2018): Considere o banco dado</p><p>pelas tabelas que seguem. A partir do banco criado na seção anterior</p><p>(DDL), popule o banco com as linhas dadas abaixo.</p><p>Obs.: Todas as tabelas precisam ter sua chave primária definida, incluindo</p><p>as duas últimas – que não correspondem a entidades, mas surgiram de</p><p>relacionamentos do tipo M : N (muitos-para-muitos). A seção de mapea-</p><p>mento vista esclarece o que é considerado chave primária para tais tabelas.</p><p>Caso necessário, recorra ao auxílio da documentação dos comandos INSERT</p><p>, UPDATE e DELETE na página do PostgreSQL – https://www.post</p><p>gresql.org/docs/current/index.html.</p><p>ALUNO</p><p>Nome Numero_aluno Tipo_aluno Curso</p><p>Silva 17 1 CC</p><p>Braga 8 2 CC</p><p>DISCIPLINA</p><p>Nome_disciplina Numero_disciplina Creditos Departamento</p><p>Introdução à ciência</p><p>da computação CC1310 4 CC</p><p>Estruturas de dados CC3320 4 CC</p><p>Matemática discreta MAT2410 3 MAT</p><p>Banco de dados CC3380 3 CC</p><p>TURMA</p><p>Identificador_turma Numero_disciplina Semestre Ano Professor</p><p>85 MAT2410 Segundo 07 Kleber</p><p>92 CC1310 Segundo 07 Anderson</p><p>102 CC3320 Primeiro 08 Carlos</p><p>112 MAT2410 Segundo 08 Chang</p><p>119 CC1310 Segundo 08 Anderson</p><p>135 CC3380 Segundo 08 Santos</p><p>REGISTRO_NOTA</p><p>Numero_aluno Identificador_turma Nota</p><p>17 112 B</p><p>17 119 C</p><p>8 85 A</p><p>8 92 A</p><p>8 102 B</p><p>8 135 A</p><p>97</p><p>PRE_REQUISITO</p><p>Numero_disciplina Numero_pre_requisito</p><p>CC3380 CC3320</p><p>CC3380 MAT2410</p><p>CC3320 CC1310</p><p>2. Altere o nome da disciplina Estruturas de dados para Estrutura</p><p>de dados.</p><p>3. Tente remover a disciplina Estrutura de dados da tabela correspondente.</p><p>O que acontece?</p><p>4. Remova todos os pré-requisitos da disciplina Banco de Dados.</p><p>5. Mantenha nos registros de nota apenas aqueles com aproveitamento A,</p><p>eliminando os demais. Dica: Basta um único comando; use o operador <></p><p>(diferente).</p><p>Referências</p><p>1. Documentação do PostgreSQL 8.0.0: Manipulação de dados. Disponível</p><p>em: <https://pgdocptbr.sourceforge.io/pg80/dml.html>.</p><p>Acesso em: 07 nov. 2022.</p><p>2. PostgreSQL: Documentation: 15: PostgreSQL 15.0. Disponível em: <ht</p><p>tps://www.postgresql.org/docs/current/index.html>.</p><p>Acesso em: 07 nov. 2022.</p><p>3. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>98</p><p>8 Álgebra relacional</p><p>8.1 Introdução</p><p>• Relembrando: Um modelo de dados inclui um conjunto de operações para</p><p>manipular um banco de dados, além dos conceitos de modelagem necessários</p><p>para a estruturação do mesmo.</p><p>• Operações: Linguagem de consulta.</p><p>Linguagem de consulta</p><p>Linguagem por meio da qual os usuários obtém informações do banco de</p><p>dados.</p><p>• Linguagens de consulta são de mais alto nível que as linguagens de programação</p><p>tradicionais (apenas se implementa o que fazer, e não também como fazer).</p><p>– Exemplo: SQL –</p><p>Structured Query Language.</p><p>Álgebra relacional</p><p>• Modelo de dados inclui:</p><p>– Conceitos para a definição das restrições e estrutura do BD;</p><p>– Conjunto de operações para manipular o BD.</p><p>Álgebra relacional</p><p>Maneira teórica de se manipular o BD relacional.</p><p>• Importância:</p><p>– Fundamento formal para as operações no modelo relacional.</p><p>– Base para implementar e otimizar consultas em SGBDR.</p><p>– Introduz conceitos incorporados na SQL.</p><p>• Linguagem de consulta procedural.</p><p>– Usuários especificam os dados necessários e como obtê-los.</p><p>• Consiste de um conjunto de operações:</p><p>– entrada: uma ou mais relações e restrições;</p><p>– saída: uma nova relação resultado.</p><p>99</p><p>Classificação das operações</p><p>Unárias – operam sobre uma única relação</p><p>• Seleção;</p><p>• Projeção;</p><p>• Renomear.</p><p>Binárias – operam sobre duas relações</p><p>• Produto cartesiano;</p><p>• União;</p><p>• Diferença de conjuntos;</p><p>• Intersecção de conjuntos;</p><p>• Junção natural;</p><p>• Divisão.</p><p>Estudo de caso: esquema relacional</p><p>cliente</p><p>PK nro_cli</p><p>nome_cli</p><p>end_cli</p><p>saldo</p><p>FK vendedor</p><p>vendedor</p><p>PK cod_vend</p><p>nome_vend</p><p>pedido</p><p>PK nro_ped</p><p>data</p><p>FK nro_cliente</p><p>pedido_peça</p><p>PK,FK1 nro_ped</p><p>PK,FK2 nro_peça</p><p>peça</p><p>PK nro_peça</p><p>descrição_peça</p><p>100</p><p>8.2 Relações unárias</p><p>Seleção (σ)</p><p>• Seleciona tuplas da relação argumento que satisfaçam à condição de seleção.</p><p>Sintaxe</p><p>σcondição_seleção(relação argumento)</p><p>– Condição pode envolver operadores de comparação (=, <, ≤, >, ≥,</p><p≯=).</p><p>– Pode-se combinar condições usando-se ∧ (e), ∨ (ou), ¬ (negação).</p><p>– Relação argumento: relação ou resultado de alguma operação da</p><p>álgebra relacional.</p><p>• Produz um subconjunto horizontal de uma relação.</p><p>Seleção</p><p>Exemplo: relação cliente</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>101</p><p>Consulta 1</p><p>• Liste toda a informação da relação cliente referente ao cliente de número</p><p>4.</p><p>σnro_cli=4(cliente)</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>Relação resultado</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>4 Rodrigo Rua X 137 2</p><p>• Grau: mesmo grau da relação argumento;</p><p>• Número de tuplas: menor ou igual ao número de tuplas da relação argumento.</p><p>Consulta 2</p><p>• Liste toda a informação da relação cliente para clientes que possuam</p><p>saldo inferior a R$ 200,00 e que morem na Rua X.</p><p>σsaldo<200,00∧end_cli=’Rua X’(cliente)</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>Relação resultado</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>4 Rodrigo Rua X 137 2</p><p>102</p><p>Condições lógicas</p><p>As condições booleanas ou lógicas ∧ (and), ∨ (or) e ¬ (not) têm sua interpretação</p><p>conforme segue:</p><p>• (cond1 ∧ cond2)</p><p>– É verdadeira quando ambas cond1 e cond2 são verdadeiras;</p><p>– caso contrário, é falsa.</p><p>• (cond1 ∨ cond2)</p><p>– É verdadeira se cond1 ou cond2 – ou ambas – forem verdadeiras;</p><p>– caso contrário, é falsa.</p><p>• (¬cond)</p><p>– verdadeira se cond for falsa.</p><p>– caso contrário, é falsa.</p><p>Projeção (π)</p><p>• Extrai atributos (colunas) específicos de uma relação, sem repetições.</p><p>Sintaxe</p><p>πlista_atributos(relação argumento)</p><p>– Os atributos são separados por vírgulas.</p><p>– Relação argumento: relação ou resultado de alguma operação da</p><p>álgebra relacional.</p><p>• Produz um subconjunto vertical de uma relação.</p><p>Projeção</p><p>103</p><p>Consulta 3</p><p>• Liste o número e o nome de todos os clientes.</p><p>πnro_cli,nome_cli(cliente)</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>Relação resultado</p><p>nro_cli nome_cli</p><p>1 Márcia</p><p>2 Cristina</p><p>3 Manoel</p><p>4 Rodrigo</p><p>• Grau: número de atributos a serem listados em lista_atributos;</p><p>• Número de tuplas: menor ou igual ao número de tuplas da relação argumento.</p><p>Consulta 4</p><p>• Liste o endereço de todos os clientes.</p><p>πend_cli(cliente)</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>Relação resultado</p><p>end_cli</p><p>Rua X</p><p>Avenida 1</p><p>Avenida 3</p><p>• Relação resultante sem tuplas repetidas.</p><p>104</p><p>Propriedades das relações unárias</p><p>• A operação de seleção é comutativa:</p><p>σ<cond1> (σ<cond2>(R)) = σ<cond2> (σ<cond1>(R))</p><p>• Logo, uma sequência de seleções pode ser executada em qualquer ordem,</p><p>ou pode ser transformada numa única seleção com uma condição conjuntiva</p><p>– termos cujo valor é verdadeiro ou falso, ligados pelo operador ∧ (and):</p><p>σ<cond1> (σ<cond2> (. . . (σ<condn>(R)))) ;</p><p>σ<cond1>∧<cond2>∧···∧<condn>(R).</p><p>• A operação de projeção não é comutativa.</p><p>• Entretanto, se lista_atribs_B contém lista_atribs_A, então ambas as</p><p>expressões seguintes são corretas, e vale a igualdade:</p><p>π<lista_atribs_A> (π<lista_atribs_B>) = π<lista_atribs_A>(R).</p><p>Consulta 5</p><p>• Liste o número e o nome de todos os clientes que possuam saldo inferior</p><p>a R$ 200,00 e que morem na Rua X.</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>• Passos:</p><p>– realizar uma operação de seleção para criar uma nova relação que</p><p>contém somente aqueles clientes com o saldo e o endereço apropria-</p><p>dos;</p><p>– realizar uma projeção sobre a relação resultante do passo anterior,</p><p>restringindo o resultado desejado às colunas indicadas.</p><p>105</p><p>• Primeiro passo</p><p>σsaldo<200,00∧end_cli=’Rua X’(cliente)</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>• Segundo passo.</p><p>πnro_cli,nome_cli(primeiro passo)</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>4 Rodrigo Rua X 137 2</p><p>• Liste o número e o nome de todos os clientes que possuam saldo inferior</p><p>a R$ 200,00 e que morem na Rua X.</p><p>πnro_cli,nome_cli (σsaldo<200,00∧end_cli=’Rua X’(cliente))</p><p>• Relação resultado:</p><p>nro_cli nome_cli</p><p>1 Márcia</p><p>4 Rodrigo</p><p>Atribuição (←)</p><p>• Associa uma relação argumento a uma relação temporária.</p><p>• Permite o uso da relação temporária em expressões subsequentes.</p><p>Sintaxe</p><p>relação temporária← relação argumento</p><p>106</p><p>Consulta 5</p><p>• Liste o número e o nome de todos os clientes que possuam saldo inferior</p><p>a R$ 200,00 e que morem na Rua X.</p><p>πnro_cli,nome_cli (σsaldo<200,00∧end_cli=’Rua X’(cliente))</p><p>• Usando atribuição:</p><p>temp← σsaldo<200,00∧end_cli=’Rua X’(cliente);</p><p>πnro_cli,nome_cli (temp) .</p><p>• Atribuição permite renomear os atributos de relações intermediárias e</p><p>final:</p><p>R(código,nome)← πnro_cli,nome_cli (temp) .</p><p>• Observações:</p><p>– Não adiciona semântica adicional à álgebra relacional;</p><p>– Geralmente utilizada para expressar consultas complexas.</p><p>Renomear (ρ)</p><p>• Renomeia:</p><p>– nome da relação;</p><p>– nomes dos atributos da relação;</p><p>– nome da relação e nomes dos atributos.</p><p>Sintaxe</p><p>ρS(B1,B2,...,Bn)(R)</p><p>– S: Nome novo da relação R;</p><p>– B1, B2, . . . , Bn: nomes novos dos atributos.</p><p>• Exemplos</p><p>– ρcomprador(cliente)</p><p>– ρ(código,nome,rua,saldo,vendedor)(cliente)</p><p>– ρcomprador(código,nome,rua,saldo,vendedor)(cliente)</p><p>Observação</p><p>Indicada para ser utilizada quando uma relação é usada mais do que uma vez</p><p>para responder à consulta.</p><p>107</p><p>8.3 Relações binárias</p><p>Produto cartesiano (×)</p><p>• Combina tuplas de duas relações (quaisquer)</p><p>• Tuplas da relação resultante: todas as combinações de tuplas possíveis</p><p>entre as relações participantes.</p><p>Sintaxe</p><p>relação argumento 1× relação argumento 2</p><p>– Relações argumento: relação ou resultado de alguma operação da</p><p>álgebra relacional.</p><p>• Utilizado quando se necessita obter dados presentes em duas ou mais</p><p>relações.</p><p>Produto</p><p>a</p><p>b</p><p>c</p><p>x</p><p>y</p><p>a</p><p>a</p><p>b</p><p>b</p><p>c</p><p>c</p><p>x</p><p>y</p><p>x</p><p>y</p><p>x</p><p>y</p><p>Exemplo: relações cliente e vendedor</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>vendedor</p><p>cod_vend nome_vend</p><p>1 Adriana</p><p>2 Roberto</p><p>nro_cli nome_cli end_cli saldo cliente.cod_vend vendedor.cod_vend nome_vend</p><p>1 Márcia Rua X 100 1 1 Adriana</p><p>1 Márcia Rua X 100 1 2 Roberto</p><p>2 Cristina</p><p>Avenida 1 10 1 1 Adriana</p><p>2 Cristina Avenida 1 10 1 2 Roberto</p><p>3 Manoel Avenida 3 234 1 1 Adriana</p><p>3 Manoel Avenida 3 234 1 2 Roberto</p><p>4 Rodrigo Rua X 137 2 1 Adriana</p><p>4 Rodrigo Rua X 137 2 2 Roberto</p><p>108</p><p>• Grau: número de atributos de cliente + número de atributos de vendedor;</p><p>• Número de tuplas: número de tuplas de cliente × número de tuplas de</p><p>vendedor.</p><p>Exemplo 2</p><p>• Considere as seguintes relações:</p><p>– usuário ( cliente_nome, gerente_nome )</p><p>– cliente ( cliente_nome, rua, cidade )</p><p>usuário</p><p>cliente_nome gerente_nome</p><p>Márcia Manoel</p><p>Rodrigo Maria</p><p>cliente</p><p>cliente_nome rua cidade</p><p>Márcia Rua X Itambé</p><p>Rodrigo Rua X Maringá</p><p>• Liste o nome de todos os usuários atendidos pelo gerente Manoel, assim</p><p>como as cidades nas quais eles vivem.</p><p>Solução</p><p>• Primeiro passo: determinar quem são os usuários atendidos pelo gerente</p><p>Manoel.</p><p>temp1 ← πcliente_nome</p><p>(</p><p>σgerente_nome=’Manoel’(usuário)</p><p>)</p><p>– Relação resultado temp1:</p><p>cliente_nome</p><p>Márcia</p><p>• Segundo passo: realizar o produto cartesiano das relações.</p><p>temp2 ← temp1 × cliente</p><p>– Relação resultado temp2:</p><p>temp1.cliente_nome cliente.cliente_nome rua cidade</p><p>Márcia Márcia Rua X Itambé</p><p>Márcia Rodrigo Rua X Maringá</p><p>109</p><p>• Terceiro passo: eliminar as informações inconsistentes.</p><p>temp3 ← σtemp1.cliente_nome=cliente.cliente_nome (temp2)</p><p>– Relação resultado temp3:</p><p>temp1.cliente_nome cliente.cliente_nome rua cidade</p><p>Márcia Márcia Rua X Itambé</p><p>• Quarto passo: exibir as informações solicitadas.</p><p>πtemp1.cliente_nome,cidade (temp3)</p><p>– Relação resultado:</p><p>temp1.cliente_nome cidade</p><p>Márcia Itambé</p><p>Exemplo 3</p><p>• Considere a seguinte relação:</p><p>– cliente ( cliente_nome, rua, cidade )</p><p>cliente</p><p>cliente_nome rua cidade</p><p>Márcia Rua X Itambé</p><p>Rodrigo Rua X Maringá</p><p>Cristina Rua XTZ Maringá</p><p>Sofia Rua X Maringá</p><p>Ricardo Rua AAA Itambé</p><p>• Liste o nome dos clientes que moram na mesma rua e na mesma cidade</p><p>que Rodrigo (exceto o próprio Rodrigo).</p><p>Solução</p><p>• Primeiro passo: determinar o nome da rua e o nome da cidade na qual</p><p>Rodrigo mora.</p><p>temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>– Relação resultado temp1:</p><p>rua cidade</p><p>Rua X Maringá</p><p>110</p><p>• Segundo passo: realizar o produto cartesiano das relações.</p><p>temp2 ← temp1 × cliente</p><p>– Relação resultado temp2:</p><p>temp1.rua temp1.cidade cliente_nome cliente.rua cliente.cidade</p><p>Rua X Maringá Márcia Rua X Itambé</p><p>Rua X Maringá Rodrigo Rua X Maringá</p><p>Rua X Maringá Cristina Rua XTZ Maringá</p><p>Rua X Maringá Sofia Rua X Maringá</p><p>Rua X Maringá Ricardo Rua AAA Itambé</p><p>• Terceiro passo: eliminar as informações indesejadas.</p><p>temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>– Relação resultado temp3:</p><p>temp1.rua temp1.cidade cliente_nome cliente.rua cliente.cidade</p><p>Rua X Maringá Márcia Rua X Itambé</p><p>Rua X Maringá Cristina Rua XTZ Maringá</p><p>Rua X Maringá Sofia Rua X Maringá</p><p>Rua X Maringá Ricardo Rua AAA Itambé</p><p>• Quarto passo: exibir as informações solicitadas.</p><p>πcliente_nome</p><p>(</p><p>σ(temp1.rua=cliente.rua)∧( temp1.cidade=cliente.cidade) (temp3)</p><p>)</p><p>– Relação resultado:</p><p>cliente_nome</p><p>Sofia</p><p>Discussão</p><p>• Solução proposta:</p><p>1. temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← temp1 × cliente</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome</p><p>(</p><p>σ(temp1.rua=cliente.rua)∧( temp1.cidade=cliente.cidade) (temp3)</p><p>)</p><p>• Operação de atribuição</p><p>1. temp1(rua_rodrigo,cidade_rodrigo)</p><p>← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← temp1 × cliente</p><p>111</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome</p><p>(</p><p>σ(rua_rodrigo=rua)∧(cidade_rodrigo=cidade) (temp3)</p><p>)</p><p>• Operação renomear (1):</p><p>1. temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← ρ(rua_rodrigo,cidade_rodrigo) (temp1)× cliente</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome</p><p>(</p><p>σ(rua_rodrigo=rua)∧(cidade_rodrigo=cidade) (temp3)</p><p>)</p><p>• Operação renomear (2):</p><p>1. temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← ρdados_rodrigo(rua_rodrigo,cidade_rodrigo) (temp1)×cliente</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome</p><p>(</p><p>σ(rua_rodrigo=rua)∧(cidade_rodrigo=cidade) (temp3)</p><p>)</p><p>Junção (⋊⋉)</p><p>• Concatena tuplas relacionadas de duas relações em tuplas únicas.</p><p>• Simplifica consultas que requerem produto cartesiano.</p><p>– forma um produto cartesiano dos argumentos.</p><p>Sintaxe</p><p>relação argumento 1 ⋊⋉condição_junção relação argumento 2</p><p>Exemplo: relações cliente e vendedor</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>vendedor</p><p>cod_vend nome_vend</p><p>1 Adriana</p><p>2 Roberto</p><p>cliente ⋊⋉cliente.cod_vend=vendedor.cod_vend vendedor</p><p>nro_cli nome_cli end_cli saldo cliente.cod_vend vendedor.cod_vend nome_vend</p><p>1 Márcia Rua X 100 1 1 Adriana</p><p>2 Cristina Avenida 1 10 1 1 Adriana</p><p>3 Manoel Avenida 3 234 1 1 Adriana</p><p>4 Rodrigo Rua X 137 2 2 Roberto</p><p>• Grau: número de atributos de cliente + número de atributos de vendedor;</p><p>• Número de tuplas: entre 0 e (número de tuplas de cliente × número de</p><p>tuplas de vendedor).</p><p>112</p><p>Mais detalhes</p><p>• Condição de junção: pode ser simples ou composta – envolver várias</p><p>condições numa somente:</p><p><cond1> ∧<cond2> ∧ · · · ∧<condn>.</p><p>• Cada condição é do tipo</p><p>Ai θ Bj ,</p><p>onde:</p><p>– Ai: atributo da relação argumento 1;</p><p>– Bj : atributo da relação argumento 2;</p><p>– Ai e Bj têm o mesmo domínio;</p><p>– θ (theta) é um operador de comparação (=,<,≤,>,≥ ou ̸=);</p><p>– Existem diversas variações sobre a operação de junção.</p><p>θ-junção (⋊⋉θ)</p><p>• A qual pode ser usada qualquer operador θ válido no domínio dos atributos</p><p>comparados (=,<,≤,>,≥ ou ̸=);</p><p>• Os atributos envolvidos na comparação aparecem ambos na relação resultado</p><p>(gerando pares de atributos). Porém, tuplas com valores nulos nos atributos</p><p>envolvidos na junção não aparecem no resultado;</p><p>• variação mais genérica.</p><p>Exemplos</p><p>• Equijunção (exemplo visto anteriormente):</p><p>cliente ⋊⋉cliente.cod_vend=vendedor.cod_vend vendedor</p><p>– uso mais comum de junção;</p><p>– o operador θ é a igualdade (=).</p><p>– No exemplo visto, o grau da relação resultante é 7 (número de atributos</p><p>de cliente + número de atributos de vendedor).</p><p>• Quando o operador θ não é de igualdade, a θ-junção é uma não-equijunção:</p><p>cliente ⋊⋉cliente.cod_vend ̸=vendedor.cod_vend vendedor.</p><p>113</p><p>Junção natural (∗)</p><p>1. Semelhante à operação de equijunção.</p><p>2. Porém, dos atributos da junção, apenas os originários de uma das relações</p><p>operadas aparecem na relação resultado;</p><p>• Requer que os atributos comparados tenham nomes iguais nas duas</p><p>relações;</p><p>3. Assim como na equijunção, tuplas com valores nulos nos atributos envolvidos</p><p>na comparação também não aparecem no resultado.</p><p>Exemplo: relações cliente e vendedor</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>vendedor</p><p>cod_vend nome_vend</p><p>1 Adriana</p><p>2 Roberto</p><p>cliente ∗ vendedor</p><p>nro_cli nome_cli end_cli saldo cod_vend nome_vend</p><p>1 Márcia Rua X 100 1 Adriana</p><p>2 Cristina Avenida 1 10 1 Adriana</p><p>3 Manoel Avenida 3 234 1 Adriana</p><p>4 Rodrigo Rua X 137 2 Roberto</p><p>• Grau: número de atributos de cliente + número de atributos de vendedor</p><p>−1;</p><p>• Número de tuplas: entre 0 e (número de tuplas de cliente × número de</p><p>tuplas de vendedor).</p><p>Quando os nomes dos atributos a serem comparados diferem entre si para</p><p>ambas as relações, basta renomear um deles antes da operação de junção natural:</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo vendedor</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>vendedor</p><p>cod_vend nome_vend</p><p>1 Adriana</p><p>2 Roberto</p><p>ρ(nro_cli,nome_cli,end_cli,saldo,cod_vend)cliente ∗ vendedor</p><p>nro_cli nome_cli end_cli saldo cod_vend nome_vend</p><p>1 Márcia Rua X 100 1 Adriana</p><p>2 Cristina Avenida 1 10 1 Adriana</p><p>3 Manoel Avenida 3 234 1 Adriana</p><p>4 Rodrigo Rua X 137 2 Roberto</p><p>114</p><p>Junção interna – inner join</p><p>Dadas duas relações R e S, somente as tuplas de R que possuem tuplas</p><p>correspondentes em S – e vice-versa – aparecem no resultado.</p><p>R S R ⋊⋉ S</p><p>A B C A D A B C A D</p><p>1 a x 1 d 1 a x 1 d</p><p>2 b y 2 d 2 b y 2 d</p><p>3 a y 5 e</p><p>4 c y</p><p>Junção externa esquerda – left outer join (⋊⋉)</p><p>• Dadas duas relações R e S,</p><p>mantém todas as tuplas de R em R ⋊⋉ S.</p><p>• Preenche com valores nulos os atributos de S que não correspondem às</p><p>tuplas em R.</p><p>R S R ⋊⋉ S</p><p>A B C A D A B C A D</p><p>1 a x 1 d 1 a x 1 d</p><p>2 b y 2 d 2 b y 2 d</p><p>3 a y 5 e 3 a y NULL NULL</p><p>4 c y 4 c y NULL NULL</p><p>Junção externa direita – right outer join (⋊⋉)</p><p>• Dadas duas relações R e S, mantém todas as tuplas de S em R ⋊⋉ S.</p><p>• Preenche com valores nulos os atributos de R que não correspondem às</p><p>tuplas em S.</p><p>R S R ⋊⋉ S</p><p>A B C A D A B C A D</p><p>1 a x 1 d 1 a x 1 d</p><p>2 b y 2 d 2 b y 2 d</p><p>3 a y 5 e NULL NULL NULL 5 e</p><p>4 c y</p><p>Junção externa completa – full outer join (⋊⋉)</p><p>• Dadas duas relações R e S, mantém todas as tuplas sem correspondência,</p><p>tanto de R quanto de S, em R ⋊⋉ S.</p><p>• Preenche com valores nulos os atributos de R e S que não têm correspondência.</p><p>115</p><p>R S R ⋊⋉ S</p><p>A B C A D A B C A D</p><p>1 a x 1 d 1 a x 1 d</p><p>2 b y 2 d 2 b y 2 d</p><p>3 a y 5 e 3 a y NULL NULL</p><p>4 c y 4 c y NULL NULL</p><p>NULL NULL NULL 5 e</p><p>Exemplo 1</p><p>• Considere as seguintes relações:</p><p>– usuário ( cliente_nome, gerente_nome )</p><p>– cliente ( cliente_nome, rua, cidade )</p><p>usuário</p><p>cliente_nome gerente_nome</p><p>Márcia Manoel</p><p>Rodrigo Maria</p><p>cliente</p><p>cliente_nome rua cidade</p><p>Márcia Rua X Itambé</p><p>Rodrigo Rua X Maringá</p><p>• Liste o nome de todos os usuários atendidos pelo gerente Manoel, assim</p><p>como as cidades nas quais eles vivem.</p><p>• Solução vista anteriormente:</p><p>1. temp1 ← πcliente_nome</p><p>(</p><p>σgerente_nome=’Manoel’(usuário)</p><p>)</p><p>2. temp2 ← temp1 × cliente</p><p>3. temp3 ← σtemp1.cliente_nome=cliente.cliente_nome (temp2)</p><p>4. πtemp1.cliente_nome,cidade (temp3)</p><p>• Solução usando junção – no caso, a natural:</p><p>1. temp1 ← πcliente_nome</p><p>(</p><p>σgerente_nome=’Manoel’(usuário)</p><p>)</p><p>2. temp2 ← temp1 ∗ cliente</p><p>3. πcliente_nome,cidade (temp2)</p><p>116</p><p>Exemplo 2</p><p>• Considere a seguinte relação:</p><p>– cliente ( cliente_nome, rua, cidade )</p><p>cliente</p><p>cliente_nome rua cidade</p><p>Márcia Rua X Itambé</p><p>Rodrigo Rua X Maringá</p><p>Cristina Rua XTZ Maringá</p><p>Sofia Rua X Maringá</p><p>Ricardo Rua AAA Itambé</p><p>• Liste o nome dos clientes que moram na mesma rua e na mesma cidade</p><p>que Rodrigo (exceto o próprio Rodrigo).</p><p>• Solução vista anteriormente:</p><p>1. temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← temp1 × cliente</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome</p><p>(</p><p>σ(temp1.rua=cliente.rua)∧( temp1.cidade=cliente.cidade) (temp3)</p><p>)</p><p>• Solução usando junção natural:</p><p>1. temp1 ← πrua,cidade (σcliente_nome=’Rodrigo’(cliente))</p><p>2. temp2 ← temp1 ∗ cliente</p><p>3. temp3 ← σcliente_nome ̸=’Rodrigo’ (temp2)</p><p>4. πcliente_nome (temp3)</p><p>Referências</p><p>1. Sugestão de leitura: ELMASRI R.; NAVATHE, S. Sistemas de banco de</p><p>dados, Tradução da 7a. edição, Addison- Wesley, São Paulo, 2018. Cap.</p><p>8.</p><p>2. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Slides adaptados do Prof. Humberto L. Razente (FACOM/UFU). Material</p><p>gentilmente cedido por Bruno A. N. Travençolo (FACOM/UFU). Adaptações:</p><p>Renato Pimentel, FACOM/UFU</p><p>117</p><p>9 Comando SELECT – consultas básicas e consultas</p><p>aninhadas</p><p>9.1 Introdução</p><p>Consultas em SQL</p><p>Consulta</p><p>Processo de obtenção ou o comando para a obtenção de dados de um banco de</p><p>dados.</p><p>• Em SQL, o comando SELECT é usado para especificar consultas.</p><p>• As consultas simples em SQL correspondem às operações de seleção, projeção</p><p>e junção da álgebra relacional.</p><p>Comando SELECT</p><p>Sintaxe (adaptada de https://www.postgresql.org/docs/current/sq</p><p>l-select.html)</p><p>SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]</p><p>* | expression [ AS output_name ] [, ...]</p><p>[ FROM from_item [, ...] ]</p><p>[ WHERE condition ]</p><p>[ GROUP BY expression [, ...] ]</p><p>[ HAVING condition [, ...] ]</p><p>[ \{ UNION | INTERSECT | EXCEPT \} [ ALL ] selection ]</p><p>[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]</p><p>[ LIMIT \{ count | ALL \} ]</p><p>[ OFFSET start ]</p><p>[ FOR UPDATE [ OF table_name [, ...] ] ]</p><p>(continuação)</p><p>where from_item can be one of:</p><p>[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] )</p><p>] ]</p><p>(...)</p><p>[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]</p><p>(...)</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] )</p><p>[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [,</p><p>...] ) ] ]</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias (</p><p>column_definition [, ...] )</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] ) AS (</p><p>column_definition [, ...] )</p><p>(...)</p><p>from_item join_type from_item { ON join_condition | USING (</p><p>join_column [, ...] ) [ AS join_using_alias ] }</p><p>from_item NATURAL join_type from_item</p><p>118</p><p>from_item CROSS JOIN from_item</p><p>Organização</p><p>Considerando os diversos parâmetros do comando SELECT, para efeito didático,</p><p>vamos dividir nosso estudo em:</p><p>• Consultas básicas: no WHERE não existe outro SELECT;</p><p>• Consultas aninhadas: no WHERE existe outro SELECT;</p><p>• Consultas com tabelas de junção e tipos de junção;</p><p>• Consultas envolvendo funções de agregação: como máximo, soma, média,</p><p>etc; e agrupamentos.</p><p>9.2 Consultas básicas</p><p>Formato de comando SELECT para consultas básicas:</p><p>1 SELECT <lista-de-atributos></p><p>2 FROM <lista-de-tabelas></p><p>3 [WHERE <condição>]</p><p>Observação</p><p>Condição sem cláusula SELECT (aninhamento):</p><p>Este caso será visto em seguida.</p><p>Exemplos</p><p>1 /* Seleciona todas as colunas da tabela ALUNO */</p><p>2 SELECT *</p><p>3 FROM ALUNO;</p><p>4</p><p>5 /* Seleciona todos os nomes e números de matrícula da</p><p>tabela ALUNO (projeção) */</p><p>6 SELECT NMat, Nome</p><p>7 FROM ALUNO;</p><p>8</p><p>9 /* Seleciona todos os nomes e números de matrícula da</p><p>tabela ALUNO, renomeando a coluna Nome para "aluno" */</p><p>10 SELECT NMat, Nome AS Aluno</p><p>11 FROM ALUNO; -- tabulação, mudança de linha: opcionais</p><p>119</p><p>DISTINCT</p><p>Remove as tuplas duplicadas da resposta.</p><p>Exemplo:</p><p>1 /* Seleciona todas as</p><p>cidades */</p><p>2 SELECT cidade</p><p>3 FROM filiais;</p><p>Resultado correto, mas com muitas</p><p>repetições:</p><p>Cidade</p><p>São Paulo</p><p>São Paulo</p><p>São Paulo</p><p>Rio de Janeiro</p><p>Rio de Janeiro</p><p>Angra dos Reis</p><p>Belo Horizonte</p><p>Belo Horizonte</p><p>Uberlândia</p><p>Uberlândia</p><p>1 /* Seleciona todas as</p><p>cidades */</p><p>2 SELECT DISTINCT cidade</p><p>3 FROM filiais;</p><p>Cidade</p><p>São Paulo</p><p>Rio de Janeiro</p><p>Angra dos Reis</p><p>Belo Horizonte</p><p>Uberlândia</p><p>WHERE</p><p>Especifica quais registros das tabelas listadas na cláusula FROM são afetados</p><p>por uma instrução SELECT, UPDATE ou DELETE. Se não for especificada uma</p><p>cláusula WHERE, a consulta retornará todas as linhas da tabela.</p><p>Sintaxe:</p><p>WHERE condition;</p><p>onde condition é qualquer expressão que retorne um tipo booleano. Qualquer</p><p>tupla que não satisfaça tal expressão será eliminada do resultado.</p><p>Exemplo:</p><p>1 /* Seleciona nome/idade dos alunos cuja idade seja maior</p><p>que 22 */</p><p>2 SELECT Nome, Idade</p><p>3 FROM ALUNO</p><p>4 WHERE Idade > 22;</p><p>120</p><p>1 /* Seleciona os números de matrícula dos alunos</p><p>matriculados a partir de 30/07/2019 */</p><p>2 SELECT NMat</p><p>3 FROM MATRICULA</p><p>4 WHERE Data > '30/07/2019';</p><p>5</p><p>6 /* Seleciona os números de matrícula e nomes dos alunos</p><p>matriculados a partir de 30/07/2019 (nomes numa tabela</p><p>Aluno separada) */</p><p>7 SELECT M.NMat, A.Nome</p><p>8 FROM MATRICULA M, ALUNO A</p><p>9 WHERE M.NMat = A.NMat AND M.Data > '30/07/2019';</p><p>Operador BETWEEN...AND na cláusula WHERE</p><p>Determina se o valor de uma expressão se situa dentro de um intervalo especificado</p><p>de valores (inclusive). Em caso positivo, o operador BETWEEN...AND retornará</p><p>TRUE; caso contrário, retornará FALSE.</p><p>Exemplo:</p><p>1 /* Listar todos alunos com idade entre 20 e 22 anos*/</p><p>2 SELECT Nome, Idade</p><p>3 FROM Aluno</p><p>4 WHERE Idade BETWEEN 20 AND 22;</p><p>Operador IN na cláusula WHERE</p><p>Determina se o valor de uma expressão é igual a algum dos vários valores em</p><p>uma lista especificada. Em caso positivo, o operador IN retornará TRUE; caso</p><p>contrário, retornará FALSE.</p><p>Exemplo:</p><p>1 /* Listar todos os alunos provenientes de São Paulo,</p><p>Uberlândia ou Rio de Janeiro */</p><p>2 SELECT *</p><p>3 FROM ALUNO</p><p>4 WHERE UPPER(Cidade) IN('SÃO PAULO', 'UBERLÂNDIA', 'RIO DE</p><p>JANEIRO'); -- UPPER transforma os caracteres do</p><p>atributo Cidade em maiúsculas</p><p>121</p><p>Operadores booleanos (lógicos)</p><p>a b a AND b a OR b</p><p>TRUE TRUE TRUE TRUE</p><p>TRUE FALSE FALSE TRUE</p><p>TRUE NULL NULL TRUE</p><p>FALSE FALSE FALSE FALSE</p><p>FALSE NULL FALSE NULL</p><p>NULL NULL NULL NULL</p><p>a NOT a</p><p>TRUE FALSE</p><p>FALSE TRUE</p><p>NULL NULL</p><p>FALSE OR NULL → NULL</p><p>Lembrando: NULL é desconhecido. I.e. não se conhece o resultado:</p><p>• Se o desconhecido fosse</p><p>TRUE, então</p><p>FALSE OR TRUE → TRUE;</p><p>• Se o desconhecido fosse FALSE, então</p><p>FALSE OR FALSE → FALSE.</p><p>Logo, desconhece-se o resultado da operação, i.e. o mesmo é NULL.</p><p>Operador IS NULL na cláusula WHERE</p><p>Determina se o valor de uma expressão é nulo.</p><p>Exemplo:</p><p>1 /* Listar todas as disciplinas que NÃO possuem pré-</p><p>requisito */</p><p>2 SELECT Nome</p><p>3 FROM Discip</p><p>4 WHERE SiglaPreReq IS NULL; -- forma ERRADA: WHERE</p><p>SiglaPreReq = NULL</p><p>Operador LIKE na cláusula WHERE</p><p>Compara uma expressão de sequência com um padrão numa expressão SQL.</p><p>Para o padrão, é possível utilizar caracteres curinga (por exemplo, LIKE '</p><p>MAK%', para 'MAKROMBOOKS'), ou utilizar caracteres isolados (por exemplo,</p><p>LIKE '_OSE', para 'JOSE' e 'ROSE').</p><p>Exemplo:</p><p>1 /* Listar todos os alunos cujo nome termina em 'ina',</p><p>ignorando as 3 primeiras letras */</p><p>2 SELECT Nome</p><p>3 FROM Aluno</p><p>4 WHERE UPPER(Nome) LIKE '___INA'; -- São 3 '_' .</p><p>Procure também substituir os 3 '_' por um '%'</p><p>122</p><p>ORDER BY</p><p>Classifica os registros resultantes de uma consulta em um campo ou campos</p><p>especificados, em ordem crescente ou decrescente. Os registros são classificados</p><p>pelo primeiro campo listado após ORDER BY. Os registros que têm valores</p><p>iguais naquele campo serão então classificados pelo valor no segundo campo</p><p>listado, e assim por diante.</p><p>Exemplo:</p><p>1 /* Listar todos os alunos, ordenando-os de forma</p><p>decrescente por idade e depois por nome */</p><p>2 SELECT Nome, Idade</p><p>3 FROM Aluno</p><p>4 ORDER BY Idade DESC, Nome DESC; -- Tente também trocar</p><p>DESC por ASC</p><p>9.3 Consultas aninhadas</p><p>• Muitas vezes, é preciso buscar uma informação no banco de dados para,</p><p>então, usá-la na condição de comparação de uma consulta.</p><p>• Isso pode ser feito por meio de consultas aninhadas:</p><p>– Forma-se um bloco SELECT-FROM-WHERE dentro da cláusula WHERE</p><p>de outra consulta.</p><p>– Essa consulta é chamada de consulta interna.</p><p>O operador IN, já visto, é utilizado.</p><p>Exemplo (ELMASRI; NAVATHE, 2018):</p><p>1 /* Listar os nomes funcionários que trabalham em projetos</p><p>*/</p><p>2 SELECT nome</p><p>3 FROM funcionario</p><p>4 WHERE cpf IN (</p><p>5 SELECT cpf</p><p>6 FROM trabalha_em )</p><p>123</p><p>Outro exemplo: operador NOT IN</p><p>1 /* Listar os nomes funcionários que não trabalham em</p><p>nenhum projeto */</p><p>2 SELECT nome</p><p>3 FROM funcionario</p><p>4 WHERE cpf NOT IN (</p><p>5 SELECT cpf</p><p>6 FROM trabalha_em )</p><p>Outro exemplo:</p><p>1 /* Listar os projetos dos funcionários de nome 'José' */</p><p>2 SELECT pnumero</p><p>3 FROM trabalha_em</p><p>4 WHERE cpf IN ( -- consulta interna</p><p>5 SELECT cpf</p><p>6 FROM funcionario</p><p>7 WHERE pnome = 'José' );</p><p>Outro exemplo:</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Internet</p><p>1 CREATE TABLE ProximasCidades (</p><p>2 cidade VARCHAR(50));</p><p>3 DELETE FROM ProximasCidades;</p><p>4 INSERT INTO ProximasCidades</p><p>5 SELECT DISTINCT cidade</p><p>6 FROM filiais</p><p>7 WHERE cidade IS NOT NULL;</p><p>8 INSERT INTO ProximasCidades VALUES</p><p>('Araguari'), ('Ituiutaba');</p><p>9 SELECT * FROM ProximasCidades;</p><p>A sequência de comandos anterior produz o resultado:</p><p>cidade</p><p>São Paulo</p><p>Rio de Janeiro</p><p>Angra dos Reis</p><p>Belo Horizonte</p><p>Uberlândia</p><p>Araguari</p><p>Ituiutaba</p><p>124</p><p>1 /* Qual o resultado? */</p><p>2 SELECT *</p><p>3 FROM filiais</p><p>4 WHERE cidade IN (</p><p>5 SELECT cidade</p><p>6 FROM ProximasCidades );</p><p>São todas as filiais presentes em cidades que constam na tabela ProximasCidades</p><p>:</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>E a filial da Internet?</p><p>1 /* Qual o resultado? */</p><p>2 SELECT *</p><p>3 FROM ProximasCidades</p><p>4 WHERE cidade IN (</p><p>5 SELECT cidade</p><p>6 FROM filiais );</p><p>São as cidades onde já existem filiais:</p><p>cidade</p><p>São Paulo</p><p>Rio de Janeiro</p><p>Angra dos Reis</p><p>Belo Horizonte</p><p>Uberlândia</p><p>1 /* Qual o resultado? */</p><p>2 SELECT *</p><p>3 FROM ProximasCidades</p><p>4 WHERE cidade NOT IN (</p><p>5 SELECT cidade</p><p>125</p><p>6 FROM filiais );</p><p>Neste caso, o resultado é:</p><p>cidade</p><p>[null]</p><p>(pois a filial Internet não está localizada numa cidade).</p><p>1 /* Qual o resultado? */</p><p>2 SELECT *</p><p>3 FROM ProximasCidades</p><p>4 WHERE cidade NOT IN (</p><p>5 SELECT cidade</p><p>6 FROM filiais</p><p>7 WHERE cidade IS NOT NULL );</p><p>São as cidades sem filiais:</p><p>cidade</p><p>Araguari</p><p>Ituiutaba</p><p>Se um único atributo com uma única tupla é retornado da consulta externa,</p><p>então podemos usar o operador =.</p><p>1 /* Listar o(s) funcionário(s) com o maior salário */</p><p>2 SELECT pnome, unome, salario</p><p>3 FROM funcionario</p><p>4 WHERE salario = (</p><p>5 SELECT MAX(salario)</p><p>6 FROM funcionario );</p><p>Consultas aninhadas correlacionadas</p><p>• Consultas aninhadas correlacionadas ocorrem quando o resultado da subconsulta</p><p>(consulta interna) varia de acordo com a tupla que está sendo avaliada na</p><p>consulta externa.</p><p>• Cuidado: isso pode ser muito lento, pois a subconsulta é reavaliada para</p><p>cada linha da consulta externa. Se houver uma forma de evitar a correlação,</p><p>a consulta pode ser mais rápida.</p><p>Exemplo (ELMASRI; NAVATHE, 2018):</p><p>126</p><p>1 /* Listar o(s) funcionário(s) com dependentes do mesmo</p><p>sexo e que possuam o mesmo primeiro nome */</p><p>2 SELECT f.pnome, f.unome</p><p>3 FROM funcionario AS f</p><p>4 WHERE f.cpf IN (</p><p>5 SELECT d.cpf_funcionario</p><p>6 FROM dependente AS d</p><p>7 WHERE f.pnome = d.nome AND f.sexo = d.sexo );</p><p>• cpf_funcionario é chave estrangeira de dependente que faz referência</p><p>à chave primária cpf de funcionario.</p><p>• A condição sendo testada na subconsulta (linha 7) depende de qual tupla</p><p>(funcionário) está sendo considerado na consulta externa.</p><p>• Em geral, uma consulta aninhada em que a subconsulta usa os operadores</p><p>= ou IN pode ser expressa como uma consulta simples.</p><p>• Logo, a consulta aninhada anterior pode ser reescrita (eliminando-se o</p><p>custo computacional da correlação) como:</p><p>1 /* Listar o(s) funcionário(s) com dependentes do</p><p>mesmo sexo e que possuam o mesmo primeiro nome */</p><p>2 SELECT f.pnome, f.unome</p><p>3 FROM funcionario AS f, dependente AS d</p><p>4 WHERE f.cpf = d.cpf_funcionario</p><p>5 AND f.pnome = d.nome AND f.sexo = d.sexo;</p><p>• Outra possibilidade (porém mantendo o aninhamento): usar EXISTS.</p><p>EXISTS (subconsulta)</p><p>retorna TRUE se existir ao menos uma tupla no resultado da subconsulta.</p><p>NOT EXISTS (subconsulta)</p><p>retorna TRUE se a subconsulta retornar um conjunto vazio (sem tuplas).</p><p>Exemplo (Produz no máximo uma tupla para cada tupla de tab1, mesmo</p><p>se há várias tuplas em tab2 que satisfaçam a condição):</p><p>1 SELECT col1</p><p>2 FROM tab1</p><p>3 WHERE EXISTS (SELECT 1</p><p>4 FROM tab2</p><p>5 WHERE col2 = tab1.col2);</p><p>127</p><p>• O otimizador do SGBD pode executar a consulta apenas até determinar</p><p>que tem ao menos uma tupla como resultado.</p><p>• Como o resultado depende apenas se há tuplas no mesmo, uma convenção</p><p>comum é escrever as cláusulas EXISTS na forma:</p><p>... EXISTS (SELECT 1 FROM ... WHERE ...)</p><p>(como no exemplo anterior).</p><p>No caso do esquema da empresa (ELMASRI; NAVATHE, 2018) visto, a</p><p>consulta correlacionada dos dependentes funcionários seria reescrita como:</p><p>1 /* Listar o(s) funcionário(s) com dependentes do mesmo</p><p>sexo e que possuam o mesmo primeiro nome */</p><p>2 SELECT f.pnome, f.unome</p><p>3 FROM funcionario AS f</p><p>4 WHERE EXISTS ( SELECT *</p><p>5 FROM dependente AS d</p><p>6 WHERE f.cpd = d.cpf_funcionario</p><p>7 AND f.pnome = d.nome AND f.sexo = d.sexo );</p><p>Exemplo: considere as tabelas</p><p>produto</p><p>cod_produto nome valor</p><p>1 Queijo 15.00</p><p>2 Goiabada 8.00</p><p>3 Doce de leite 7.00</p><p>cliente</p><p>cod_cliente nome data_nasc</p><p>1 Maria 2000-01-05</p><p>2 Ana 1998-05-03</p><p>3 Carlos 1990-02-02</p><p>compra</p><p>cod_cliente cod_produto datahora</p><p>1 1 2016-10-20 00:00:00</p><p>1 2 2016-10-20 00:00:00</p><p>1 3 2016-10-20 00:00:00</p><p>2 1 2016-10-20 00:00:00</p><p>2 2 2016-10-21 00:00:00</p><p>2 2 2016-10-22 00:00:00</p><p>1 -- Mostrar os clientes que fizeram compras</p><p>2 SELECT *</p><p>3 FROM cliente</p><p>4 WHERE EXISTS</p><p>5 ( SELECT 1</p><p>6 FROM compra</p><p>7 WHERE compra.cod_cliente = cliente.cod_cliente );</p><p>128</p><p>• Subconsulta (linhas 5–7) com uma tabela no FROM (tabela compra) mas</p><p>com comando no WHERE que usa uma tabela da consulta externa.</p><p>• Para cada tupla da consulta externa, a consulta interna (subconsulta) é</p><p>feita;</p><p>– Como temos 3 clientes,</p><p>a subconsulta é executada 3 vezes.</p><p>• Para cada execução da subconsulta o EXISTS verifica se a subconsulta</p><p>retornou algum resultado.</p><p>E a situação oposta?</p><p>1 -- Mostrar os clientes que NÃO fizeram compras</p><p>2 SELECT *</p><p>3 FROM cliente</p><p>4 WHERE NOT EXISTS</p><p>5 ( SELECT 1</p><p>6 FROM compra</p><p>7 WHERE compra.cod_cliente = cliente.cod_cliente );</p><p>Outra possibilidade de pesquisa:</p><p>1 -- Mostrar os clientes que compraram TODOS os produtos</p><p>2 SELECT *</p><p>3 FROM cliente</p><p>4 WHERE NOT EXISTS</p><p>5 ( SELECT cod_produto</p><p>6 FROM produto</p><p>7 EXCEPT</p><p>8 SELECT cod_produto</p><p>9 FROM compra</p><p>10 WHERE compra.cod_cliente = cliente.cod_cliente );</p><p>• Subconsulta executada para cada cliente existente na base:</p><p>– Todos os produtos (linhas 5–6);</p><p>– Produtos para o cliente “atual” (linhas 11–12);</p><p>– EXCEPT: ao subtrair o conjunto de todos os produtos dos produtos</p><p>que o cliente comprou, teremos uma resposta vazia caso ele tenha</p><p>comprado todos.</p><p>∗ NOT EXISTS retornará TRUE caso a subconsulta seja vazia, ou</p><p>seja, caso o cliente tenha comprado todos os produtos.</p><p>Referências</p><p>1. PostgreSQL: Documentation: 15: PostgreSQL 15.0. Disponível em: <ht</p><p>tps://www.postgresql.org/docs/current/index.html>.</p><p>Acesso em: 07 nov. 2022.</p><p>129</p><p>2. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>3. SILVA, I. R. (FACOM/UFU). Notas de aula.</p><p>Slides adaptados da aula da Profa. Josiane M. Bueno (in memoriam)</p><p>e Prof. Humberto L. Razente (FACOM/UFU). Material gentilmente cedido</p><p>por Bruno A. N. Travençolo (FACOM/UFU). Adaptações: Renato Pimentel,</p><p>FACOM/UFU</p><p>130</p><p>10 Comando SELECT – consultas com tabelas de</p><p>junção e tipos de junção</p><p>10.1 Introdução</p><p>Relembrando – Consultas em SQL</p><p>Consulta</p><p>Processo de obtenção ou o comando para a obtenção de dados de um banco de</p><p>dados.</p><p>• Em SQL, o comando SELECT é usado para especificar consultas.</p><p>• As consultas simples em SQL correspondem às operações de seleção, projeção</p><p>e junção da álgebra relacional.</p><p>Relembrando – Comando SELECT</p><p>Sintaxe (adaptada de https://www.postgresql.org/docs/current/sq</p><p>l-select.html)</p><p>SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]</p><p>* | expression [ AS output_name ] [, ...]</p><p>[ FROM from_item [, ...] ]</p><p>[ WHERE condition ]</p><p>[ GROUP BY expression [, ...] ]</p><p>[ HAVING condition [, ...] ]</p><p>[ { UNION | INTERSECT | EXCEPT } [ ALL ] selection ]</p><p>[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]</p><p>[ LIMIT { count | ALL } ]</p><p>[ OFFSET start ]</p><p>[ FOR UPDATE [ OF table_name [, ...] ] ]</p><p>(continuação)</p><p>where from_item can be one of:</p><p>[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] )</p><p>] ]</p><p>(...)</p><p>[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]</p><p>(...)</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] )</p><p>[ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [,</p><p>...] ) ] ]</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias (</p><p>column_definition [, ...] )</p><p>[ LATERAL ] function_name ( [ argument [, ...] ] ) AS (</p><p>column_definition [, ...] )</p><p>(...)</p><p>from_item join_type from_item { ON join_condition | USING (</p><p>join_column [, ...] ) [ AS join_using_alias ] }</p><p>from_item NATURAL join_type from_item</p><p>131</p><p>from_item CROSS JOIN from_item</p><p>O foco deste conteúdo será nas seguintes partes:</p><p>SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]</p><p>* | expression [ AS output_name ] [, ...]</p><p>[ FROM from_item [, ...] ]</p><p>(...)</p><p>where from_item can be one of:</p><p>(...)</p><p>from_item join_type from_item { ON join_condition | USING (</p><p>join_column [, ...] ) [ AS join_using_alias ] }</p><p>from_item NATURAL join_type from_item</p><p>from_item CROSS JOIN from_item</p><p>(...)</p><p>where join_type can be one of:</p><p>[ INNER ] JOIN</p><p>LEFT [ OUTER ] JOIN</p><p>RIGHT [ OUTER ] JOIN</p><p>FULL [ OUTER ] JOIN</p><p>10.2 Uso das cláusulas JOIN</p><p>Orientador supervisiona Aluno</p><p>1 N</p><p>1 /* Criando as tabelas */</p><p>2 CREATE TABLE orientador (</p><p>3 id INT PRIMARY KEY,</p><p>4 nome VARCHAR(255)</p><p>5 );</p><p>6 CREATE TABLE aluno (</p><p>7 matricula INT PRIMARY KEY,</p><p>8 nome VARCHAR(255),</p><p>9 orientador_id INT REFERENCES orientador(id)</p><p>10 );</p><p>11</p><p>12 /* Povoando as tabelas*/</p><p>13 INSERT INTO orientador VALUES (1,'Prof. José'), (2,'Profa. Maria');</p><p>14 INSERT INTO aluno VALUES (1,'Carlos',NULL), (2,'Roberto',2), (3,'Ailton</p><p>',NULL)</p><p>132</p><p>orientador</p><p>id nome</p><p>1 Prof. José</p><p>2 Profa. Maria</p><p>aluno</p><p>matricula nome orientador_id</p><p>1 Carlos</p><p>2 Roberto 2</p><p>3 Ailton</p><p>1 SELECT *</p><p>2 FROM orientador, aluno</p><p>3 WHERE aluno.orientador_id = orientador.id;</p><p>id nome matricula nome orientador_id</p><p>2 Profa. Maria 2 Roberto 2</p><p>INNER JOIN – junção interna</p><p>Ao invés de simplesmente usar a cláusula WHERE, o comando anterior pode</p><p>ser reescrito:</p><p>1 SELECT *</p><p>2 FROM orientador INNER JOIN aluno</p><p>3 ON aluno.orientador_id = orientador.id;</p><p>id nome matricula nome orientador_id</p><p>2 Profa. Maria 2 Roberto 2</p><p>Outer joins – junções externas</p><p>• Variantes da operação de junção que baseiam-se em valores NULL. O</p><p>resultado de uma junção externa é similar ao de uma consulta interna</p><p>(inner join), mas com a inclusão das tuplas que não satisfaçam a condição</p><p>da junção.</p><p>• Três variantes:</p><p>– LEFT OUTER JOIN: as tuplas da tabela à esquerda que não obedecem</p><p>à condição da junção aparecem na resposta;</p><p>– RIGHT OUTER JOIN: as tuplas da tabela à direita que não obedecem</p><p>à condição da junção aparecem na resposta;</p><p>– FULL OUTER JOIN: as tuplas de ambas as tabelas que não obedecem</p><p>à condição da junção aparecem na resposta.</p><p>133</p><p>LEFT OUTER JOIN – junção externa esquerda</p><p>1 SELECT *</p><p>2 FROM orientador LEFT OUTER JOIN aluno</p><p>3 ON aluno.orientador_id = orientador.id;</p><p>id nome matricula nome orientador_id</p><p>1 Prof. José</p><p>2 Profa. Maria 2 Roberto 2</p><p>RIGHT OUTER JOIN – junção externa direita</p><p>1 SELECT *</p><p>2 FROM orientador RIGHT OUTER JOIN aluno</p><p>3 ON aluno.orientador_id = orientador.id;</p><p>id nome matricula nome orientador_id</p><p>1 Carlos</p><p>2 Profa. Maria 2 Roberto 2</p><p>3 Ailton</p><p>FULL OUTER JOIN – junção externa completa</p><p>1 SELECT *</p><p>2 FROM orientador FULL OUTER JOIN aluno</p><p>3 ON aluno.orientador_id = orientador.id;</p><p>id nome matricula nome orientador_id</p><p>1 Carlos</p><p>2 Profa. Maria 2 Roberto 2</p><p>3 Ailton</p><p>1 Prof. José</p><p>NATURAL JOIN – Junção natural</p><p>cliente</p><p>nro_cli nome_cli end_cli saldo cod_vend</p><p>1 Márcia Rua X 100 1</p><p>2 Cristina Avenida 1 10 1</p><p>3 Manoel Avenida 3 234 1</p><p>4 Rodrigo Rua X 137 2</p><p>vendedor</p><p>cod_vend nome_vend</p><p>1 Adriana</p><p>2 Roberto</p><p>134</p><p>1 SELECT * FROM cliente NATURAL JOIN vendedor;</p><p>nro_cli nome_cli end_cli saldo cod_vend nome_vend</p><p>1 Márcia Rua X 100 1 Adriana</p><p>2 Cristina Avenida 1 10 1 Adriana</p><p>3 Manoel Avenida 3 234 1 Adriana</p><p>4 Rodrigo Rua X 137 2 Roberto</p><p>• Junção natural: Equijunção que testa a(s) condição(ões) tab1.atr =</p><p>tab2.atr, onde atr é todo atributo que aparece – com mesmo nome –</p><p>tanto na tabela tab1 quanto na tab2.</p><p>– Essa(s) condição(ões) é(são) definida(s) de forma implícita: não se</p><p>fornece uma condição para o comando.</p><p>• Diferenças para a junção interna: além de na junção interna a(s) condição(ões)</p><p>precisar(em) ser definida(s), as colunas sendo comparadas aparecem ambas</p><p>na tabela resultado.</p><p>– Uma projeção é necessária para se eliminar uma das mesmas.</p><p>No caso do exemplo visto, a junção interna correspondente seria:</p><p>1 SELECT nro_cli, nome_cli, end_cli, saldo, cliente.</p><p>cod_vend, nome_vend</p><p>2 FROM cliente INNER JOIN vendedor</p><p>3 ON cliente.cod_vend = vendedor.cod_vend;</p><p>No caso de estudo sendo visto:</p><p>orientador</p><p>id nome</p><p>1 Prof. José</p><p>2 Profa. Maria</p><p>aluno</p><p>matricula nome orientador_id</p><p>1 Carlos</p><p>2 Roberto 2</p><p>3 Ailton</p><p>1 SELECT *</p><p>2 FROM orientador NATURAL JOIN aluno;</p><p>nome id matricula orientador_id</p><p>(tabela vazia): por quê?</p><p>• Para listar os orientadores que possuem alunos sendo orientados, bem</p><p>como seus alunos, tal como a junção interna (INNER JOIN) vista:</p><p>135</p><p>– É necessário renomear os atributos de uma das tabelas os quais se</p><p>deseja comparar com os correspondentes da outra – bem como aqueles</p><p>que não devem ser comparados entre si).</p><p>1 SELECT *</p><p>2 FROM orientador NATURAL JOIN aluno AS aluno(matricula</p><p>,nome_aluno,id);</p><p>id nome matricula nome_aluno</p><p>2 Profa. Maria 2 Roberto</p><p>A junção interna vista anteriormente tem grau 5, porque as colunas</p><p>orientador_id (de aluno) e id (de orientador) são ambas mostradas.</p><p>Para que o resultado seja similar à junção natural, basta fazer a projeção:</p><p>1 SELECT id, orientador.nome,</p><p>eliminando a menor das 3 anteriores,</p><p>caso maior que a mesma.</p><p>• A NP neste caso será dada por:</p><p>– Se SUB > min (P1, P2, P3):</p><p>NP = P1 + P2 + P3 + SUB −min (P1, P2, P3) .</p><p>– Caso contrário, a nota permanece inalterada:</p><p>NP = P1 + P2 + P3 .</p><p>Frequência</p><p>• O aluno que tiver frequência inferior a 75% é reprovado por faltas.</p><p>• A assiduidade será computada através da chamada em sala durante as</p><p>aulas, em um horário aleatório após 10 minutos do início de cada encontro</p><p>diário. O professor poderá adotar, a seu critério, caso haja demasiada</p><p>desistência de continuidade em sala, uma segunda chamada ao final do</p><p>segundo horário de aula.</p><p>• Falta em dia de prova: o aluno somente terá direito a fazer prova em nova</p><p>data caso apresente justificativa prevista pelas Normas de Graduação.</p><p>• É responsabilidade do aluno controlar sua frequência, de modo a evitar</p><p>reprovação por falta.</p><p>• O professor não corrigirá atividades de alunos que já tenham sido reprovados</p><p>por falta. Inclusive, os mesmos não terão direito à SUB (Normas da</p><p>Graduação).</p><p>5</p><p>Logística</p><p>Aulas</p><p>• Quintas-feiras: 20:50 até 22:30 – Sala 3Q205</p><p>• Sextas-feiras: 19:00 até 20:40 – Sala 3Q205 – ou Lab. 3Q106 (14 máquinas</p><p>+ mesas para laptop) para práticas.</p><p>Atividades extraclasse</p><p>• Listas de exercícios (fixação) – complementação de carga horária.</p><p>• Teams: Equipe GES013 – 2022/1, código de inscrição 5s4yv2b</p><p>Atendimento e outras informações</p><p>• Professor: Renato Pimentel</p><p>– Página: http://www.facom.ufu.br/∼rpimentel</p><p>– E-mail: rpimentel @ ufu . br</p><p>– Sala 1B139</p><p>• Atendimento (agendar previamente através de e-mail):</p><p>– Terças-feiras, 19:00 até 20:40, sala 1B139</p><p>– Quartas-feiras, 14:00 até 15:40, sala 1B139</p><p>• Material da disciplina:</p><p>– http://www.facom.ufu.br/∼rpimentel > Ensino > 2022/1 > GES013</p><p>– Sistema de Banco de Dados</p><p>6</p><p>1 Introdução aos Sistemas Gerenciadores de Ban-</p><p>co de Dados</p><p>1.1 Introdução</p><p>• Armazenamento e manipulação de grandes quantidades de dados.</p><p>• Exemplos:</p><p>– Tradicionais (textuais e/ou numéricos):</p><p>∗ Dados de uma universidade: cadastro de alunos, notas, frequências,</p><p>servidores.</p><p>∗ Dados de uma empresa: dados dos clientes, funcionários, produtos.</p><p>– Big data / NOSQL1 – postagens e imagens, vídeos etc.</p><p>∗ Aplicações de mídia social (Facebook, Twitter, Gmail etc.)</p><p>Banco de dados</p><p>“Coleção” de itens de dados relacionados.</p><p>Propriedades do banco de dados</p><p>• Representa um aspecto (abstração) do mundo real, de forma que alterações</p><p>em tal aspecto são refletidas no banco de dados.</p><p>• Significado inerente: coleção logicamente coerente de dados (não pode ser</p><p>uma coleção aleatória de dados ou informações).</p><p>• Finalidade específica: um BD é projetado, montado e preenchido desta</p><p>maneira, com um público alvo específico e com aplicações previamente</p><p>planejadas às quais tal público esteja interessado.</p><p>1.2 Sistema gerenciador de banco de dados</p><p>• Um sistema gerenciador de banco de dados, ou SGBD, é um software</p><p>criado para auxiliar na manutenção e utilização de grandes coleções de</p><p>dados.</p><p>• A alternativa ao uso de um SGBD é armazenar os dados em arquivos e</p><p>escrever códigos específicos para gerenciá-los.</p><p>• Diversos problemas estão associados ao uso de arquivos para armazenamento</p><p>e manipulação de dados:</p><p>1Not only SQL (Structured Query Language)</p><p>7</p><p>– Inconsistência e redundância de dados;</p><p>– Dificuldade de acesso aos dados;</p><p>– Isolamento de dados;</p><p>– Problemas de integridade;</p><p>– Problemas de atomicidade;</p><p>– Anomalias no acesso concorrente;</p><p>– Escalabilidade;</p><p>– Problemas de segurança.</p><p>Inconsistência e redundância de dados</p><p>• Arquivos e aplicações criadas por diferentes programadores.</p><p>– Arquivos com formatos diferentes;</p><p>– Programas escritos em diversas linguagens de programação.</p><p>• Inconsistências de dados</p><p>– As cópias podem divergir ao longo do tempo.</p><p>– Ex.: a mudança do telefone de um cliente pode ocorrer somente em</p><p>um dos arquivos.</p><p>• Redundância de dados</p><p>– Ex.: o telefone de um cliente pode aparecer em mais de um arquivo;</p><p>– Aumento no custo de armazenamento e acesso.</p><p>Dificuldade de acesso aos dados</p><p>• Cada nova consulta diferente das previstas inicialmente envolve a implementação</p><p>de novos programas para realizá-las.</p><p>– Ex.: encontre os clientes que morem numa localidade cujo CEP é</p><p>12345-234.</p><p>– Ex.: encontre os clientes que possuem saldo maior que $ 10.000,00.</p><p>– ...</p><p>• Ou seja, cada nova requisição exige uma nova implementação, um novo</p><p>programa.</p><p>Isolamento de dados</p><p>• Dados dispersos em vários arquivos com diferentes formatos aumenta a</p><p>dificuldade de escrever novas aplicações para recuperação apropriada dos</p><p>dados (acesso).</p><p>• Não há isolamento de dados e programas (código).</p><p>8</p><p>Problemas de integridade</p><p>• Os valores dos dados devem satisfazer certas restrições de integridade para</p><p>manutenção da consistência – o dado armazenado deve representar um</p><p>fato ou atributo da realidade modelada pelo sistema.</p><p>• Restrições feitas adicionando-se códigos aos vários programas de aplicações.</p><p>• Difícil a implementação de novas restrições — podem envolver vários</p><p>programas ou diversos itens de dados em diferentes arquivos.</p><p>Problemas de atomicidade</p><p>• Os sistemas estão sujeitos a falhas.</p><p>• As aplicações devem assegurar que, após a detecção de uma falha, os dados</p><p>sejam salvos em seu último estado consistente, anterior a ela.</p><p>• Ex.: Transferir um valor X da conta A para a conta B. Se SA e SB são</p><p>os saldos respectivos de tais contas:</p><p>1. SA = SA−X</p><p>2. SB = SB + X</p><p>• É possível que seja feito o débito em A e que o crédito em B não se realize</p><p>por causa de uma falha, criando assim um estado inconsistente.</p><p>• As operações devem ser atômicas — devem ocorrer por completo ou não</p><p>ocorrer.</p><p>• Difícil garantir a atomicidade em caso de falha entre operações 1 e 2 num</p><p>sistema convencional de processamento de arquivos, sem um SGBD.</p><p>Anomalias no acesso concorrente</p><p>• Vários sistemas exigem o acesso simultâneo, ou seja, concorrente, aos</p><p>dados.</p><p>• Exemplo: Seja uma conta corrente A com Saldo SA = 500 e duas retiradas</p><p>simultâneas em tarefas T1 e T2, cada uma de 100, cuja execução ocorre</p><p>na seguinte ordem temporal:</p><p>– T1: Obtém SA;</p><p>– T2: Obtém SA;</p><p>– T1: SA = SA− 100;</p><p>– T2: SA = SA− 100;</p><p>– T1: grava SA;</p><p>– T2: grava SA;</p><p>• É difícil garantir a consistência dos dados em caso de acesso concorrente a</p><p>arquivos convencionais. No caso acima SA será 400, mas deveria ser 300.</p><p>9</p><p>Escalabilidade</p><p>• Crescimento do volume de dados pode tornar o sistema lento ou ineficaz:</p><p>• Dificuldade de implementação de novas consultas e restrições.</p><p>Problemas de segurança</p><p>• Nem todos os usuários de um banco de dados estão autorizados a acessar</p><p>todos os dados nele contidos.</p><p>• Ex.: O setor de RH de uma instituição bancária deve ter acesso somente</p><p>aos dados relativos aos funcionários, não aos de clientes.</p><p>• Difícil garantir a efetividade das regras de segurança, usando-se arquivos</p><p>convencionais.</p><p>Como o SGBD lida com tais problemas</p><p>Utilizar um SGBD oferece diversas vantagens:</p><p>Independência dos dados</p><p>O SGBD provê uma visão abstrata dos dados, de forma que um programa</p><p>aplicativo não é exposto a detalhes de representação e armazenamento de dados.</p><p>Acesso eficiente aos dados</p><p>O SGBD dispõe de uma variedade de técnicas sofisticadas para armazenamento</p><p>e recuperação eficiente de dados, incluindo dados armazenados em dispositivos</p><p>externos.</p><p>Integridade dos dados e segurança</p><p>Se os dados forem acessados pelo SGBD é possível garantir restrições de integridade</p><p>e também o controle de acesso aos dados para diferentes classes de usuários.</p><p>10</p><p>Acesso concorrente e recuperação de falhas</p><p>Um SGBD garante a consistência no acesso concorrente e recuperação de falhas</p><p>por meio da atomicidade das operações.</p><p>Administração dos dados</p><p>Centralização da administração do banco de dados – profissional responsável</p><p>para organização da representação dos dados para minimizar a redundância e</p><p>fazer ajustes finos para melhorar o desempenho.</p><p>Redução do tempo de desenvolvimento de aplicativos</p><p>Disponibilidade de diversas funções para acesso aos dados, bem como uma</p><p>interface de alto nível.</p><p>Exemplos de SGBD</p><p>• MS Access: Primeiro SGBD voltado para</p><p>matricula, aluno.nome</p><p>2 FROM orientador JOIN aluno</p><p>3 ON aluno.orientador_id = orientador.id;</p><p>id nome matricula nome</p><p>2 Profa. Maria 2 Roberto</p><p>(a cláusula INNER JOIN pode ser sempre abreviada para JOIN).</p><p>A junção natural vista está relacionada à junção interna. Entretanto, as</p><p>junções externas também podem ser “naturais”:</p><p>1 SELECT *</p><p>2 FROM orientador NATURAL LEFT OUTER JOIN aluno AS aluno(</p><p>matricula,nome_aluno,id);</p><p>id nome matricula nome_aluno</p><p>1 Prof. José</p><p>2 Profa. Maria 2 Roberto</p><p>CROSS JOIN: produto cartesiano</p><p>1 SELECT *</p><p>2 FROM orientador CROSS JOIN aluno;</p><p>id nome matricula nome orientador_id</p><p>1 Prof. José 1 Carlos</p><p>1 Prof. José 2 Roberto 2</p><p>1 Prof. José 3 Ailton</p><p>2 Profa. Maria 1 Carlos</p><p>2 Profa. Maria 2 Roberto 2</p><p>2 Profa. Maria 3 Ailton</p><p>136</p><p>Mais exemplos</p><p>1 SELECT *</p><p>2 FROM professor p</p><p>3 LEFT JOIN</p><p>4 (estudante e</p><p>5 INNER JOIN</p><p>6 faculdade f</p><p>7 ON f.sigla = e.fac_est) ON p.id = e.tutor;</p><p>(as cláusulas LEFT OUTER JOIN e RIGHT OUTER JOIN podem ser encurtadas</p><p>para LEFT JOIN e RIGHT JOIN, respectivamente).</p><p>estudante frequenta turma</p><p>N M</p><p>compostapor disciplina</p><p>N 1</p><p>1 /* Liste as matrículas efetuadas em cada turma de cada</p><p>disciplina */</p><p>2 SELECT estudante.id, estudante.nome, frequenta.id_turma,</p><p>3 turma.cod_disc, turma.turma, disciplina.nome</p><p>4 FROM disciplina</p><p>5 INNER JOIN turma ON disciplina.codigo = turma.cod_disc</p><p>6 INNER JOIN frequenta ON turma.id = frequenta.id_turma</p><p>7 INNER JOIN estudante ON estudante.id = frequenta.id_est;</p><p>1 /* Liste as matrículas efetuadas em cada turma de cada</p><p>disciplina */</p><p>2 SELECT estudante.id, estudante.nome, frequenta.id_turma,</p><p>3 turma.cod_disc, turma.turma, disciplina.nome</p><p>4 FROM disciplina INNER JOIN</p><p>5 (turma INNER JOIN</p><p>6 (frequenta INNER JOIN estudante</p><p>7 ON frequenta.id_est = estudante.id)</p><p>8 ON turma.id = frequenta.id_turma)</p><p>9 ON disciplina.codigo = turma.cod_disc;</p><p>1 /* Liste as matrículas efetuadas em cada turma de cada</p><p>disciplina - alterando-se a ordem das junções */</p><p>2 SELECT estudante.id, estudante.nome, frequenta.id_turma,</p><p>3 turma.cod_disc, turma.turma, disciplina.nome</p><p>4 FROM estudante INNER JOIN</p><p>5 (frequenta INNER JOIN</p><p>6 (turma INNER JOIN disciplina</p><p>7 ON turma.cod_disc = disciplina.codigo)</p><p>137</p><p>8 ON frequenta.id_turma = turma.id)</p><p>9 ON estudante.id = frequenta.id_est;</p><p>1 /* Liste as matrículas efetuadas em cada turma de cada</p><p>disciplina - comando equivalente usando WHERE */</p><p>2 SELECT e.id, e.nome, f.id_turma,</p><p>3 t.cod_disc, t.turma, d.nome</p><p>4 FROM estudante e, frequenta f, turma t, disciplina d</p><p>5 WHERE t.cod_disc = d.codigo AND</p><p>6 f.id_turma = t.id AND</p><p>7 e.id = f.id_est;</p><p>10.3 Exercícios</p><p>1. Reproduza os exemplos vistos nesta seção no PostgreSQL.</p><p>2. Implemente os exemplos de junções discutidos na seção de álgebra relacional,</p><p>criando em linguagem SQL as tabelas exemplificadas, e posteriormente</p><p>definindo os comandos SELECT correspondentes às junções vistas.</p><p>Referências</p><p>1. PostgreSQL: Documentation: 15: PostgreSQL 15.0. Disponível em: <ht</p><p>tps://www.postgresql.org/docs/current/index.html>.</p><p>Acesso em: 07 nov. 2022.</p><p>2. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Material gentilmente cedido por Bruno A. N. Travençolo (FACOM/UFU).</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>138</p><p>11 SQL DML – SELECT – agregação e agrupa-</p><p>mentos</p><p>11.1 Funções de agregação</p><p>Funções de agregação calculam e retornam um único valor a partir de um</p><p>conjunto de valores de entrada.</p><p>Function Description</p><p>array_agg(expression) concatenação dos valores num array (vetor)</p><p>avg(expression) média aritmética dos valores de entrada</p><p>bit_and(expression) AND bit-a-bit de todos os valores não-nulos, ou null se</p><p>nenhum não-nulo</p><p>bit_or(expression) OR bit-a-bit de todos os valores não-nulos, ou null se</p><p>nenhum não-nulo</p><p>bool_and(expression) verdadeiro se todos os valores são verdadeiros, senão falso</p><p>bool_or(expression) verdadeiro se ao menos um valor o é, senão falso</p><p>count(*) número de linhas (tuplas) não-nulas</p><p>count(expression) número de linhas</p><p>every(expression) equivalente a bool_and no padrão SQL</p><p>max(expression) valor máximo dentre todos os valores de entrada</p><p>min(expression) valor mínimo dentre todos os valores de entrada</p><p>sum(expression) soma de todos os valores de entrada</p><p>xmlagg(expression) concatenação de valores</p><p>(adaptado de https://www.postgresql.org/docs/8.4/functions-a</p><p>ggregate.html).</p><p>Função MIN()/MAX()</p><p>• MIN() : Retorna o mínimo de um conjunto de valores contido num</p><p>atributo especificado em uma consulta.</p><p>• MAX(): Retorna o máximo de um conjunto de valores contido num campo</p><p>especificado em uma consulta.</p><p>Exemplo:</p><p>1 /* Verificar qual é a idade do aluno mais velho */</p><p>2 SELECT MAX(Idade)</p><p>3 FROM ALUNO;</p><p>4</p><p>5 /* Verificar o(s) nome(s) e a idade do(s) aluno(s) mais</p><p>novo(s) */</p><p>6 SELECT Nome, Idade</p><p>7 FROM ALUNO</p><p>8 WHERE Idade IN ( SELECT MIN(Idade) FROM ALUNO );</p><p>139</p><p>Função SUM()</p><p>SUM() : Retorna a soma de um conjunto de valores contido num atributo</p><p>especificado em uma consulta. A função SUM ignora os registros que contenham</p><p>campos NULL.</p><p>Exemplo:</p><p>1 /* Calcular a quantidade de créditos oferecidos */</p><p>2 SELECT SUM(NNCred) AS TotalCreditos</p><p>3 FROM DISCIP;</p><p>4</p><p>5 /* Calcular o gasto da companhia com os salários */</p><p>6 SELECT SUM(salario) AS TotalSalario</p><p>7 FROM funcionario;</p><p>Função AVG()</p><p>AVG() : Calcula a média aritmética de um conjunto de valores contido num</p><p>atributo especificado em uma consulta.</p><p>Exemplo:</p><p>1 /* Calcular a idade média de alunos cadastrados */</p><p>2 SELECT AVG(Idade) AS Media</p><p>3 FROM ALUNO;</p><p>4</p><p>5 /* Calcular a média dos salários dos func. do</p><p>departamento 2 */</p><p>6 SELECT AVG(salario) AS Media</p><p>7 FROM funcionario</p><p>8 WHERE dnumero = 2;</p><p>Função COUNT()</p><p>COUNT() : Calcula o número de registros retornado por uma consulta. A</p><p>função COUNT() não conta registros que tenham campos NULL, exceto quando</p><p>for usado o caractere curinga asterisco (*).</p><p>Exemplo:</p><p>1 /* Contar quantos alunos estão</p><p>cadastrados */</p><p>2 SELECT COUNT(*)</p><p>3 FROM aluno;</p><p>4</p><p>5 /* Contar quantos alunos fazem iniciaçã</p><p>o científica (IC) - Observe que</p><p>registros onde cpf_orientador é</p><p>NULL não são considerados */</p><p>6 SELECT COUNT(cpf_orientador)</p><p>7 FROM aluno;</p><p>8</p><p>9 /* Contar quantos orientadores de IC</p><p>existem. Note que um orientador</p><p>pode orientar mais de um aluno */</p><p>10 SELECT COUNT( DISTINCT cpf_orientador)</p><p>11 FROM aluno;</p><p>140</p><p>NMAT Nome cpf_orientador</p><p>1 Carlos 222.222.222-22</p><p>2 José</p><p>3 Maria 555.555.222-12</p><p>4 André 222.222.222-22</p><p>É possível usar a palavra chave DISTINCT com o COUNT(). Desta forma,</p><p>os valores duplicados nas colunas não são contados.</p><p>Exemplo:</p><p>1 /* Qual o número de estados</p><p>atendidos pela empresa</p><p>*/</p><p>2 SELECT COUNT ( DISTINCT uf</p><p>)</p><p>3 FROM filiais;</p><p>Resposta: 3</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Observações</p><p>• Uma função agregada não pode aparecer com um atributo da tabela:</p><p>1 /* Verificar qual é a idade e o nome do aluno mais</p><p>velho */</p><p>2 SELECT nome, MAX(Idade) -- incorreto</p><p>3 FROM ALUNO;</p><p>• Funções de agregação podem aparecer com atributos quando é utilizada</p><p>a cláusula GROUP BY, explicada a seguir.</p><p>141</p><p>Funções de agregação para análise estatística</p><p>Function Description</p><p>corr(Y, X) correlation coefficient</p><p>covar_pop(Y, X) population covariance</p><p>covar_samp(Y, X) sample covariance</p><p>regr_avgx(Y, X) average of the independent variable (sum(X)/N)</p><p>regr_avgy(Y, X) average of the dependent variable (sum(Y)/N)</p><p>regr_count(Y, X) number of input rows in which both expressions are nonnull</p><p>regr_intercept(Y, X) y-intercept of the least-squares-fit linear equation</p><p>determined by the (X, Y) pairs</p><p>regr_r2(Y, X) square of the correlation coefficient</p><p>regr_slope(Y, X) slope of the least-squares-fit linear equation determined by</p><p>the (X, Y) pairs</p><p>regr_sxx(Y, X) sum(X^2) - sum(X)^2/N (“sum of squares” of the</p><p>independent variable)</p><p>regr_sxy(Y, X) sum(X*Y) - sum(X) * sum(Y)/N (“sum of products” of</p><p>independent times dependent variable)</p><p>regr_syy(Y, X) sum(Y^2) - sum(Y)^2/N (“sum of squares” of the dependent</p><p>variable)</p><p>stddev(expression) historical alias for</p><p>stddev_samp</p><p>stddev_pop(expression) population standard deviation of the input values</p><p>stddev_samp(expression) sample standard deviation of the input values</p><p>variance(expression) historical alias for var_samp</p><p>var_pop(expression) population variance of the input values (square of the</p><p>population standard deviation)</p><p>var_samp(expression) sample variance of the input values (square of the sample</p><p>standard deviation)</p><p>(adaptado de https://www.postgresql.org/docs/8.4/functions-a</p><p>ggregate.html).</p><p>11.2 Agrupamentos</p><p>Relembrando: sintaxe do SELECT (adaptada de https://www.postgresql</p><p>.org/docs/current/sql-select.html)</p><p>SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]</p><p>* | expression [ AS output_name ] [, ...]</p><p>[ FROM from_item [, ...] ]</p><p>[ WHERE condition ]</p><p>[ GROUP BY expression [, ...] ]</p><p>[ HAVING condition [, ...] ]</p><p>[ { UNION | INTERSECT | EXCEPT } [ ALL ] selection ]</p><p>[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]</p><p>[ LIMIT { count | ALL } ]</p><p>[ OFFSET start ]</p><p>[ FOR UPDATE [ OF table_name [, ...] ] ]</p><p>142</p><p>• Podemos dividir o conjunto</p><p>de tuplas de uma relação em</p><p>grupos, de acordo com algum</p><p>critério baseado nos valores dos</p><p>atributos.</p><p>– Por exemplo, na tabela</p><p>dependente ao lado, as</p><p>tuplas pode ser agrupadas</p><p>de acordo com o parentesco</p><p>do dependente (pai, filho,</p><p>irmã, irmão, tio, avó, etc.)</p><p>dependente</p><p>nome_dependente cod_func sexo data_nasc parentesco</p><p>Felipe 108 M 25/02/97 Filho</p><p>Felipe 115 M 15/12/04 Filho</p><p>Felipe 116 M 29/03/01 Filho</p><p>Felipe 117 M 30/04/00 Filho</p><p>Renato Jr. 1000 M 24/05/68 Filho</p><p>Joaquim 1006 M 30/06/05 Filho</p><p>João 101 M 11/10/92 Filho</p><p>Paula 200 F 25/06/75 Irmã</p><p>Maria 1201 F 20/06/88 Irmã</p><p>Alberto 1202 M 15/02/58 Irmão</p><p>João 1201 M 28/09/92 Irmão</p><p>Tania 208 F 10/06/50 Mãe</p><p>Maria Inês 1203 F 15/11/40 Mãe</p><p>Dolores Martins 704 F 20/05/56 Mãe</p><p>Maria Junger 403 F 05/10/54 Mãe</p><p>José Moreno 204 M 15/08/70 Marido</p><p>João 207 M 05/05/50 Pai</p><p>1 /* parentesco de todos os dependentes */</p><p>2 SELECT parentesco</p><p>3 FROM dependente</p><p>4 GROUP BY parentesco;</p><p>parentesco</p><p>Filho</p><p>Pai</p><p>Mãe</p><p>Irmão</p><p>Marido</p><p>Irmã</p><p>• Observe que na cláusula SELECT somente podem constar os atributos</p><p>presentes no GROUP BY.</p><p>• Isso faz sentido pois, por exemplo, existem duas irmãs cadastradas: qual</p><p>delas apareceria no resultado?</p><p>1 /* parentesco de todos os dependentes */</p><p>2 SELECT nome_dependente, parentesco</p><p>3 FROM dependente</p><p>4 GROUP BY parentesco;</p><p>ERRO: coluna “dependente.nome_dependente” deve aparecer na cláusula</p><p>GROUP BY ou ser usada numa função de agregação.</p><p>143</p><p>Mais de um atributo pode ser usado</p><p>no agrupamento:</p><p>1 /* agrupando as filiais por</p><p>cidade/estado */</p><p>2 SELECT cidade, uf</p><p>3 FROM filiais</p><p>4 GROUP BY cidade, uf;</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Resultado:</p><p>cidade uf</p><p>Angra dos Reis RJ</p><p>Uberlândia MG</p><p>São Paulo SP</p><p>Belo Horizonte MG</p><p>Rio de Janeiro RJ</p><p>11.3 Agrupamentos com funções de agregação</p><p>As funções de agregação (ex.:</p><p>COUNT, MIN, MAX e AVG) podem ser</p><p>usadas para cálculos com subgrupos de</p><p>tuplas definidos pela cláusula GROUP</p><p>BY.</p><p>1 /* Qual o número de filiais</p><p>por estado? */</p><p>2 SELECT uf, COUNT(uf) --</p><p>poderia ser COUNT(*)</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Resultado:</p><p>uf count</p><p>MG 4</p><p>SP 3</p><p>RJ 3</p><p>144</p><p>Valores nulos também são</p><p>agrupados:</p><p>1 /* agrupando as filiais por</p><p>estado */</p><p>2 SELECT uf</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Internet</p><p>Resultado:</p><p>uf</p><p>[null]</p><p>MG</p><p>SP</p><p>RJ</p><p>Valores nulos também são</p><p>agrupados e contados:</p><p>1 /* agrupando as filiais por</p><p>estado e contando o nú</p><p>mero por estado */</p><p>2 SELECT uf, COUNT(*)</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Resultado:</p><p>uf count</p><p>[null] 1</p><p>MG 4</p><p>SP 3</p><p>RJ 3</p><p>Valores nulos também são</p><p>agrupados e contados:</p><p>1 /* agrupando as filiais por</p><p>estado e contando o nú</p><p>mero por estado */</p><p>2 SELECT uf, COUNT(uf)</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Resultado:</p><p>uf count</p><p>[null] 0</p><p>MG 4</p><p>SP 3</p><p>RJ 3</p><p>145</p><p>Valores nulos também são</p><p>agrupados e contados:</p><p>1 /* agrupando as filiais por</p><p>estado e contando o nú</p><p>mero por estado */</p><p>2 SELECT uf, COUNT(nome)</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Resultado:</p><p>uf count</p><p>[null] 1</p><p>MG 4</p><p>SP 3</p><p>RJ 3</p><p>Valores nulos também são</p><p>agrupados e contados:</p><p>1 /* agrupando as filiais por</p><p>estado e contando o nú</p><p>mero por estado */</p><p>2 SELECT uf, COUNT(cidade)</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Resultado:</p><p>uf count</p><p>[null] 0</p><p>MG 4</p><p>SP 3</p><p>RJ 3</p><p>Valores nulos também são</p><p>agrupados e contados:</p><p>1 /* agrupando as filiais por</p><p>estado e contando o nú</p><p>mero por estado */</p><p>2 SELECT uf, COUNT(DISTINCT(</p><p>cidade))</p><p>3 FROM filiais</p><p>4 GROUP BY uf;</p><p>Cada cidade é contada uma vez:</p><p>uf count</p><p>[null] 0</p><p>MG 2</p><p>SP 1</p><p>RJ 2</p><p>Cláusula HAVING</p><p>• HAVING: é semelhante à cláusula WHERE. HAVING elimina tuplas agrupadas</p><p>que não satisfazem uma determinada condição.</p><p>• Diferença com relação a WHERE: WHERE filtra tuplas individuais antes</p><p>146</p><p>da aplicação do GROUP BY, enquanto HAVING filtra o grupo de tuplas</p><p>criadas por GROUP BY.</p><p>• As condições de filtragem do HAVING devem ser feitas baseando-se nos</p><p>atributos agrupados por GROUP BY.</p><p>Exemplo:</p><p>1 /* Listar os estados com</p><p>mais de 3 filiais */</p><p>2 SELECT uf</p><p>3 FROM filiais</p><p>4 GROUP BY uf</p><p>5 HAVING COUNT(*) > 3;</p><p>Filiais</p><p>Nome Cidade UF</p><p>Brás São Paulo SP</p><p>Limão São Paulo SP</p><p>Penha São Paulo SP</p><p>Tijuca Rio de Janeiro RJ</p><p>Barra Rio de Janeiro RJ</p><p>Angra Angra dos Reis RJ</p><p>Pampulha Belo Horizonte MG</p><p>Glória Belo Horizonte MG</p><p>Tibery Uberlândia MG</p><p>Centro Uberlândia MG</p><p>Resultado:</p><p>uf</p><p>MG</p><p>Exemplo:</p><p>1 /* Listar os estados com</p><p>mais de 3 filiais,</p><p>renomeando o atributo de</p><p>saída como nfiliais */</p><p>2 SELECT uf, Count(*) AS</p><p>nfiliais</p><p>3 FROM filiais</p><p>4 GROUP BY uf</p><p>5 HAVING COUNT(*) > 3;</p><p>Resultado:</p><p>uf nfiliais</p><p>MG 4</p><p>Exemplo:</p><p>1 /* Listar os estados,</p><p>exceto SP, com</p><p>2 mais de 2 filiais, e</p><p>renomeando o atributo</p><p>de saída como nfiliais</p><p>*/</p><p>3 SELECT uf, COUNT(*) AS</p><p>nfiliais</p><p>4 FROM filiais</p><p>5 WHERE uf <> 'SP'</p><p>6 GROUP BY uf</p><p>7 HAVING COUNT(*) > 2;</p><p>Resultado:</p><p>uf nfiliais</p><p>MG 4</p><p>RJ 3</p><p>147</p><p>Erro comum:</p><p>1 /* Listar os estados com</p><p>mais de 3 filiais,</p><p>renomeando o atributo de</p><p>saída como nfiliais */</p><p>2 SELECT uf, COUNT(*) AS</p><p>nfiliais</p><p>3 FROM filiais</p><p>4 GROUP BY uf</p><p>5 HAVING nfiliais > 3;</p><p>ERRO: coluna “nfiliais” não existe</p><p>Mais exemplos</p><p>Considere agora as tabelas:</p><p>cliente</p><p>cod_cliente nome data_nasc</p><p>1 Maria 2000-01-05</p><p>2 Ana 1998-03-05</p><p>3 Carlos 1990-02-02</p><p>cupom_desconto</p><p>num_cupom valordesconto validade cod_cliente</p><p>1 R$ 0,30 2020-10-20 1</p><p>2 R$ 0,40 2020-10-20 1</p><p>3 R$ 0,15 2020-10-20 1</p><p>4 R$ 0,15 2020-10-20 2</p><p>5 R$ 0,15 2020-10-20 2</p><p>Juntando as tabelas:</p><p>SELECT * FROM cliente NATURAL JOIN cupom_desconto;</p><p>cod_cliente nome data_nasc num_cupom valordesconto validade</p><p>1 Maria 2000-01-05 1 R$ 0,30 2020-10-20</p><p>1 Maria 2000-01-05 2 R$ 0,40 2020-10-20</p><p>1 Maria 2000-01-05 3 R$ 0,15 2020-10-20</p><p>2 Ana 1998-03-05 4 R$ 0,15 2020-10-20</p><p>2 Ana 1998-03-05 5 R$ 0,15 2020-10-20</p><p>148</p><p>Exemplo:</p><p>1 /* Listar a quantidade de cupons por cliente */</p><p>2 SELECT cliente.cod_cliente, COUNT(num_cupom) AS qte_cupom</p><p>3 FROM cliente NATURAL JOIN cupom_desconto</p><p>4 GROUP BY cliente.cod_cliente;</p><p>• Note que somente atributos que estão no GROUP BY podem aparecer na</p><p>projeção do SELECT.</p><p>• Outros atributos podem constar dentro de funções de agregação.</p><p>Resultado:</p><p>cod_cliente qte_cupom</p><p>2 2</p><p>1 3</p><p>Exemplo:</p><p>1 /* Listar a quantidade de cupons por cliente */</p><p>2 SELECT cupom_desconto.cod_cliente, COUNT(num_cupom) AS</p><p>qte_cupom</p><p>3 FROM cliente NATURAL JOIN cupom_desconto</p><p>4 GROUP BY cliente.cod_cliente;</p><p>ERRO: coluna “cupom_desconto.cod_cliente” deve aparecer na cláusula GROUP</p><p>BY ou ser usada numa função de agregação.</p><p>A partir do SQL 1999, é possível colocar outros atributos que não estejam</p><p>agrupados no SELECT, desde que o atributo usado no agrupamento seja uma</p><p>chave primária (como é o caso de cod_cliente).</p><p>1 /* Listar a quantidade de cupons por cliente */</p><p>2 SELECT cliente.cod_cliente, cliente.nome, COUNT(num_cupom</p><p>) AS qte_cupom</p><p>3 FROM cliente INNER JOIN cupom_desconto</p><p>4 ON cliente.cod_cliente = cupom_desconto.</p><p>cod_cliente</p><p>5 GROUP BY cliente.cod_cliente;</p><p>Resultado:</p><p>cod_cliente nome qte_cupom</p><p>2 Ana 2</p><p>1 Maria 3</p><p>149</p><p>Referências</p><p>1. PostgreSQL: Documentation: 15: PostgreSQL 15.0. Disponível em: <ht</p><p>tps://www.postgresql.org/docs/current/index.html>.</p><p>Acesso em: 07 nov. 2022.</p><p>2. TRAVENÇOLO, B. A. N. (FACOM/UFU). Notas de aula.</p><p>Slides adaptados da aula da Profa. Josiane M. Bueno (in memoriam)</p><p>e Prof. Humberto L. Razente (FACOM/UFU). Material gentilmente cedido</p><p>por Bruno A. N. Travençolo (FACOM/UFU). Adaptações: Renato Pimentel,</p><p>FACOM/UFU</p><p>150</p><p>uso em massa no ambiente</p><p>gráfico do Windows (1992), com origens na aquisição pela Microsoft da</p><p>ferramenta FoxPro, da Borland. Atualmente parte do MS Office. Versão</p><p>estável (Office 365): 2107 (jul/2021).</p><p>• MS SQL Server: Antecessor ao MS Access, lançado pela MS em 1989.</p><p>Versão estável atual: 2019.</p><p>• MySQL: SGBD relacional open-source surgido em 1995, multi-plataforma,</p><p>de origem sueca. O “My” é uma referência direta à filha do cofundador</p><p>Michael “Monty” Widenius. Posteriormente vendido à Sun Microsystems</p><p>em 2008 – a Sun por sua vez foi adquirida pela Oracle. Versão estável:</p><p>8.0.30 (jul/2022).</p><p>• Oracle Database: Também conhecido por Oracle DBMS2. Ferramenta</p><p>comercial da Oracle, multi-plataforma, desenvolvida em C e Assembly,</p><p>lançada em 1979. Versão estável: 19c (fev/2019).</p><p>• PostgreSQL: SGBD relacional open-source gratuito, lançado em 1996.</p><p>Originalmente chamado POSTGRES, referência à sua origem no Ingres</p><p>(SGDB comercial desenvolvido na UC Berkeley). Forte ênfase na aderência</p><p>às normas SQL. Versão estável: 14.5 (ago/2022).</p><p>2database management system, SGBD</p><p>11</p><p>Popularidade</p><p>Fonte: https://db-engines.com/en/ranking</p><p>1.3 Sistema de banco de dados (SBD)</p><p>Usuários/Programadores</p><p>Programas de aplicação/consultas</p><p>Software</p><p>SGBD Software para processar</p><p>as consultas / programas</p><p>Software para acessar</p><p>os dados armazenados</p><p>Definição do banco</p><p>de dados armazenado</p><p>(metadados)</p><p>Banco</p><p>de dados</p><p>armazenado</p><p>Sistema de banco</p><p>de dados (SBD)</p><p>Detalhes da arquitetura do SBD</p><p>• Programas Aplicativos ou de Aplicação: softwares desenvolvidos em diferentes</p><p>linguagens.</p><p>• Consultas – em inglês, queries – obtenção/modificação de dados do banco</p><p>ou da organização do mesmo.</p><p>• SGBD: Software que gerenciará os dados do SBD:</p><p>12</p><p>– Uma parte do SGBD é feita para processar as consultas, ou seja,</p><p>interpretar o que o software aplicativo está solicitando.</p><p>– Outra parte: operações de entrada e saída em arquivos armazenados</p><p>em disco, utilizando para isso diferentes estruturas de dados.</p><p>Em suma:</p><p>SBD = BD + SGBD + Aplicação/ões</p><p>• Banco de dados armazenado (stored database) – são os dados que estão</p><p>guardados no banco (ex.: “José da Silva”,222.653.365-95)</p><p>Observação</p><p>É comum usar um desenho de um cilindro para indicar armazenamento</p><p>de dados em diagramas.</p><p>• Definição do banco de dados armazenado (metadados): são as informações</p><p>que indicam quais tipos, estruturas e restrições armazenados no banco (ex.:</p><p>campos nome com 100 caracteres; CPF possui 11 dígitos).</p><p>Níveis de abstração</p><p>Usuários Finais</p><p>Visão 1</p><p>Nível físico</p><p>Visão 2 Visão n</p><p>. . .</p><p>Nível de visão</p><p>Nível lógico</p><p>• Visões descrevem como os</p><p>usuários veem os dados.</p><p>• O nível lógico ou conceitual</p><p>descreve quais dados estão</p><p>descritos no BD, e suas relações.</p><p>• O nível físico ou interno</p><p>descreve como os dados estão</p><p>armazenados, e suas estruturas</p><p>de dados.</p><p>Independência de dados</p><p>Modificações da definição de um esquema em um nível sem afetar o nível</p><p>mais alto. Dois tipos:</p><p>• Independência lógica de dados: alterações no nível lógico não afetam os</p><p>programas aplicativos.</p><p>Essas alterações podem ter o objetivo de alterar a estrutura do BD.</p><p>Ex.: inserção de uma tabela.</p><p>13</p><p>• Independência física de dados: alterações nas estruturas dos arquivos e</p><p>índices não modificam o nível lógico.</p><p>Ex.: criação de um índice para melhoria de desempenho.</p><p>Arquitetura de um SBD</p><p>• A arquitetura é fortemente influenciada pelo sistema básico computacional</p><p>sobre o qual o sistema de banco de dados é executado;</p><p>• Em outras palavras: aspectos da arquitetura do computador – como rede,</p><p>paralelismo e distribuição – têm influência na arquitetura do banco de</p><p>dados.</p><p>Sistemas centralizados</p><p>• São executados sobre um único sistema computacional, que não interage</p><p>com outros sistemas.</p><p>• Sistema monousuário: uma unidade de trabalho de um única pessoa; única</p><p>CPU; poucos discos; SO monousuário (ex.: PC). Sistema multiusuário:</p><p>mais discos, mais memória, múltiplas CPUs; SO multiusuário. Serve um</p><p>alto número de usuários que se conectam via terminais. Normalmente</p><p>chamados de sistemas servidor.</p><p>CPU Controlador</p><p>de disco</p><p>Controlador</p><p>USB</p><p>Adaptador</p><p>gráfico</p><p>Memória</p><p>Sistema cliente-servidor</p><p>• Sistemas servidores atendem aos requisitos de vários sistemas clientes.</p><p>• Estrutura geral de um sistema cliente-servidor:</p><p>Servidor</p><p>Rede</p><p>Cliente Cliente Cliente Cliente</p><p>• Funcionalidades de um banco de dados podem ser divididas em:</p><p>14</p><p>– Back-end – gerencia estruturas de acesso, desenvolvimento e otimização</p><p>de consultas, controle de concorrência e recuperação;</p><p>– Front-end – consiste em ferramentas como gerador de relatórios, formulários</p><p>e recursos de interface gráfica.</p><p>• A interface entre esses funcionalidades é feita pela SQL ou por um programa</p><p>de aplicação.</p><p>SQL Engine</p><p>Interface</p><p>(SQL + API)</p><p>Interface SQL</p><p>com o usuário</p><p>Interface para</p><p>formulários</p><p>Gerador de</p><p>relatórios</p><p>Interface</p><p>gráfica Front-end</p><p>Back-end</p><p>1.4 Modelos conceituais e físicos</p><p>• O maior benefício de um banco de dados é proporcionar ao usuário uma</p><p>visão abstrata dos dados.</p><p>– O sistema oculta os detalhes de armazenamento e manutenção dos</p><p>dados.</p><p>• Relembrando: Níveis de Abstração:</p><p>1. Nível de visão;</p><p>2. Nível lógico ou conceitual;</p><p>3. Nível físico.</p><p>Modelo de dados</p><p>• Conjunto de ferramentas conceituais usadas para a descrição dos dados,</p><p>relacionamento entre os dados, semântica de dados e regras de consistência.</p><p>• Modelos são classificados em três grupos:</p><p>– Modelos lógicos com base em objetos;</p><p>– Modelos lógicos com base em registros;</p><p>– Modelos físicos.</p><p>15</p><p>Modelos lógicos com base em objetos</p><p>• Descrevem os dados no nível lógico e de visão.</p><p>• Modelos mais conhecidos:</p><p>1. Modelo entidade-relacionamento (MER);</p><p>2. Modelo orientado a objetos;</p><p>3. Modelo semântico de dados;</p><p>4. Modelo funcional de dados.</p><p>• Modelo entidade-relacionamento (MER ou ER)</p><p>– Descreve entidades, relacionamentos, utiliza-se um diagrama ER</p><p>• Modelo orientado a objetos:</p><p>– Objetos; Métodos; Classes.</p><p>Modelos lógicos com base em registros</p><p>• Banco de dados é estruturado por meio de registros com um número fixo</p><p>de campos (atributos).</p><p>• Três modelos são (ou eram) comumente usados:</p><p>– Modelo relacional;</p><p>– Modelo de rede;</p><p>– Modelo hierárquico.</p><p>Modelo relacional</p><p>Conjunto de tabelas é usado para representar tanto os dados quanto a relação</p><p>entre os mesmos.</p><p>Sigla Nome Créditos</p><p>SMA0304 Álgebra Linear 4</p><p>SCC0503 Algoritmos e Estruturas de Dados II 6</p><p>SME0101 Cálculo Numérico II 4</p><p>SSC0110 Elementos de Lógica Digital I 4</p><p>SCC0240 Banco de Dados 4</p><p>Modelo de rede</p><p>• Dados são representados como um conjunto de registros (como em Pascal)</p><p>e as relações entre esses registros são representadas por links (ponteiros);</p><p>• Os registros são organizados no banco de dados por um conjunto arbitrário</p><p>de grafos.</p><p>16</p><p>Modelo hierárquico</p><p>• Os dados e suas relações são representados por registros e links, respectivamente,</p><p>como no modelo de rede.</p><p>• A diferença é que os registros estão organizados em árvores, ao invés de</p><p>grafos arbitrários.</p><p>1.5 Exercícios</p><p>1. Defina SGBD e dê um exemplo.</p><p>2. Qual o problema da redundância de dados, e como os SGBD lidam com</p><p>esse problema?</p><p>3. Defina atomicidade de transações em BD, qual problema se não for observada</p><p>e como os SGBD lidam com o problema.</p><p>4. Defina os três níveis de abstração dos dados armazenados por meio de um</p><p>SGBD.</p><p>5. Em relação à independência de dados: O que é independência lógica? O</p><p>que é independência física? Cite um exemplo de cada.</p><p>Referências</p><p>1. ELMASRI R.; NAVATHE, S. Sistemas de banco de dados, Tradução da</p><p>7a. edição, Addison- Wesley, São Paulo, 2018.</p><p>2. SILVA, I. R. (FACOM/UFU). Notas de aula.</p><p>Os materiais de parte desta seção foram gentilmente cedidos por Bruno A.</p><p>N. Travençolo (FACOM/UFU)</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>17</p><p>2 Modelo entidade-relacionamento (MER)</p><p>2.1 Conceitos básicos</p><p>Relembrando:</p><p>• Modelo entidade-relacionamento (MER ou simplesmente ER)</p><p>– Descreve entidades, e relacionamentos, utiliza-se um diagrama ER.</p><p>2.2 Entidades, atributos e diagrama ER</p><p>Entidade</p><p>“Coisa” ou “Objeto” do mundo real, que pode ser identificada</p><p>de forma unívoca3</p><p>em relação a todos os outros objetos.</p><p>• Pode existir fisicamente.</p><p>Ex.: uma pessoa, casa, carro, funcionário.</p><p>• Pode ser um objeto de existência conceitual.</p><p>Ex.: uma companhia, um trabalho, um curso universitário, um empréstimo.</p><p>• As entidades possuem atributos, que são propriedades que a descrevem.</p><p>Ex.: uma entidade Funcionario pode ser descrita por atributos como</p><p>nome, data de nascimento, endereço, salário e função.</p><p>Conjunto de entidades (CE)</p><p>Conjunto que abrange entidades do mesmo tipo e que compartilham os mesmos</p><p>atributos.</p><p>Ex.: conjunto de entidades Funcionario:</p><p>• E1: Carlos; 14/12/1984; Rua A; ajudante; R$ 550,00</p><p>• E2: Carol; 02/04/1987; Rua B, supervisor; R$ 3.000,00</p><p>• . . .</p><p>3inequívoca, não ambígua</p><p>18</p><p>Diagrama entidade-relacionamento</p><p>• Notação diagramática associada ao MER.</p><p>• Similar a outras notações bastante utilizadas de projeto de software e</p><p>banco de dados, como os diagramas de classe e a notação UML (linguagem</p><p>de modelagem unificada).</p><p>• Representa visualmente o projeto lógico (conceitual) do banco de dados.</p><p>• Não existe um padrão para a notação, ao contrário do que ocorre ex. com</p><p>UML.</p><p>– Diferentes livros e diferentes softwares usam notações diversas.</p><p>– Será mostrada aqui a notação usada em (ELMASRI; NAVATHE,</p><p>2018), bastante similar a encontrada em (RAMAKRISHNAN; GEHR-</p><p>KE, 2008).</p><p>Representação de entidades</p><p>Aluno Disciplina</p><p>• Utilizam-se retângulos para representação das entidades no diagrama ER.</p><p>• Observe que são os conjuntos de entidades que são considerados pelo MER,</p><p>e não as entidades individuais.</p><p>Representação de atributos</p><p>Aluno Disciplina</p><p>Nome N. aluno Código Nome</p><p>Créditos</p><p>• Os atributos nos diagramas ER são representados por elipses ligadas aos</p><p>conjuntos de entidades.</p><p>• Um conjunto de entidades sem atributos não tem sentido em uma modelagem.</p><p>19</p><p>2.3 Chaves</p><p>• Cada entidade pode ser identificada univocamente dentro do conjunto de</p><p>entidades, por meio de um ou mais atributos (chave).</p><p>• A chave é representada por um sublinhado no atributo correspondente.</p><p>• Mais de um atributo pode compor a chave.</p><p>Aluno</p><p>Nome</p><p>N. aluno</p><p>Disciplina</p><p>Créditos</p><p>Nome</p><p>Código</p><p>Turma</p><p>Sigla</p><p>Sala</p><p>Código</p><p>Observações (ELMASRI; NAVATHE, 2018)</p><p>• Chave: atributo cujo valor é distinto para cada uma das entidades do</p><p>conjunto.</p><p>• Diversos atributos em conjunto podem formar uma chave, significando</p><p>que a combinação dos valores dos atributos deve ser distinta para cada</p><p>entidade.</p><p>– Caso um conjunto de atributos seja chave, a forma adequada de</p><p>representação no MER é a definição de um atributo composto.</p><p>Outra definição:</p><p>Chave (RAMAKRISHNAN; GEHRKE, 2008)</p><p>Uma chave é um conjunto mínimo de atributos cujos valores identificam univocamente</p><p>uma entidade no conjunto de entidades.</p><p>Chaves candidatas e chave primária</p><p>• Pode existir mais de uma chave ⇒ chave candidata.</p><p>• Caso ocorra, uma delas é escolhida como chave primária.</p><p>2.4 Mais conceitos sobre atributos</p><p>Diversos tipos de atributos ocorrem no MER:</p><p>• Simples versus compostos;</p><p>• Monovalorados versus multivalorados;</p><p>• Armazenados versus derivados.</p><p>20</p><p>Atributos simples versus compostos</p><p>• Atributo simples ou atômico:</p><p>– Não pode ser dividido em partes menores.</p><p>– Exemplos:</p><p>∗ CPF</p><p>∗ Sexo ∈ {M, F}</p><p>• Atributo composto:</p><p>– Podem ser decomposto (dividido) em partes menores -– atributos</p><p>com significados diferentes;</p><p>– Seu valor é a concatenação de seus atributos constituintes.</p><p>Representação de um atributo composto (ex.: endereço):</p><p>Pessoa</p><p>CPF</p><p>Endereço</p><p>Nome</p><p>Rua</p><p>Estado</p><p>Cidade</p><p>Sexo</p><p>CEP</p><p>• Exemplos de atributos compostos:</p><p>– Endereço pode ser dividido em: Rua; Cidade; Estado; CEP</p><p>– Nome pode ser dividido em: Primeiro, Meio, Ultimo.</p><p>• Os atributos compostos são úteis em situações na qual o usuário se refere</p><p>ao atributo ora por completo, ora por suas unidades (subdivisões)</p><p>• Se o atributo é sempre referenciado como um todo, não é necessário dividi-</p><p>lo no BD.</p><p>Hierarquia de atributos compostos</p><p>Atributos compostos podem formar uma hierarquia.</p><p>21</p><p>• Ex.: No caso do diagrama anterior, Rua pode ser dividida em: Nome,</p><p>Número, Complemento</p><p>Pessoa</p><p>CPF</p><p>Endereço</p><p>Nome</p><p>Rua</p><p>Estado</p><p>Cidade</p><p>Sexo</p><p>CEP</p><p>NomeRua</p><p>Compl.</p><p>Número</p><p>Atributos monovalorados versus multivalorados</p><p>• Atributo monovalorado:</p><p>– Possui um único valor para uma entidade em particular.</p><p>– Exemplos:</p><p>∗ Idade</p><p>∗ CPF</p><p>• Atributo multivalorado:</p><p>– Possui um conjunto de valores para cada entidade.</p><p>– Exemplos:</p><p>∗ Graduação (entidade Pessoa): uma pessoa pode ser formada</p><p>em mais de um curso de graduação;</p><p>∗ Telefone (entidade Empresa): a empresa pode ter mais de</p><p>um telefone fixo.</p><p>Representação do atributo multivalorado: elipse dupla.</p><p>Empresa</p><p>Telefone</p><p>Nome</p><p>CNPJ</p><p>Endereço</p><p>22</p><p>Atributos armazenados versus derivados</p><p>• Atributo armazenado:</p><p>– Atributo da entidade realmente armazenado no BD.</p><p>• Atributo derivado:</p><p>– Refere-se ao atributo cujo valor pode ser obtido a partir dos valores</p><p>de outros.</p><p>∗ Exemplo: a idade de uma pessoa pode ser calculada subtraindo-</p><p>se a data de nascimento da data atual.</p><p>– Um atributo também pode ser derivado de uma entidade relacionada.</p><p>∗ Exemplo: determinar o número de empregados de um departamento</p><p>– contagem do número de funcionários que trabalham no departamento.</p><p>Representação do atributo derivado: elipse com borda tracejada.</p><p>Pessoa</p><p>Data Nasc.</p><p>Nome</p><p>Idade</p><p>Departamento</p><p>Núm.</p><p>funcionários</p><p>Valores nulos</p><p>• Em alguns casos, uma entidade em particular pode não ter um valor</p><p>aplicável para um atributo.</p><p>– O complemento de um endereço (por exemplo, apartamento, loja,</p><p>etc.) não é um atributo que se aplica a todas as entidades.</p><p>– Uma pessoa pode não ter feito graduação, porém na entidade em</p><p>questão existe o atributo Graduação.</p><p>• Para esses casos, é criado um valor chamado NULL (nulo).</p><p>• Também pode ser usado quando um determinado atributo existe (ex.:</p><p>altura de uma pessoa), porém o valor é desconhecido no momento da</p><p>inclusão da entidade no banco.</p><p>23</p><p>2.5 Relacionamentos</p><p>Aluno Disciplina</p><p>• Um relacionamento é uma associação entre duas ou mais entidades.</p><p>• Vários relacionamentos semelhantes podem ser agrupados num conjunto</p><p>de relacionamentos (CR).</p><p>• Um relacionamento – ou melhor, um cojunto de relacionamentos – é</p><p>representado por um losango.</p><p>– Ex.: Um aluno se matricula em uma disciplina.</p><p>– Ex.: Um empregado trabalha em um departamento</p><p>Aluno matricula Disciplina</p><p>Empregado trabalha Departamento</p><p>Um CE pode participar de mais de um CR.</p><p>Aluno</p><p>matricula</p><p>Disciplina</p><p>trabalha</p><p>Escola</p><p>24</p><p>Da mesma forma, mais de um CR pode envolver os mesmos CE.</p><p>Aluno</p><p>matricula</p><p>Disciplina</p><p>monitora</p><p>Atributos de relacionamentos</p><p>• Atributos de relacionamentos são atributos descritivos que armazenam</p><p>informações sobre o relacionamento.</p><p>• Tais atributos não fariam sentido se estivessem nos conjuntos de entidades.</p><p>Aluno</p><p>Nome</p><p>N. aluno</p><p>Matricula</p><p>Nota</p><p>Disciplina</p><p>Código</p><p>Créditos</p><p>Nome</p><p>Errado: neste caso, os alunos somente poderiam ter uma nota, independente</p><p>da quantidade de disciplinas que cursou.</p><p>Aluno</p><p>Nome</p><p>Matricula Disciplina</p><p>Código</p><p>Créditos</p><p>NomeN. aluno</p><p>Nota</p><p>25</p><p>Errado: neste caso, todos os alunos matriculados numa determinada disciplina</p><p>teriam necessariamente a mesma nota.</p><p>Aluno</p><p>Nome</p><p>Matricula Disciplina</p><p>Código</p><p>Créditos</p><p>NomeN. aluno</p><p>Nota</p><p>Outro exemplo.</p><p>Funcionário</p><p>CPF</p><p>Desenvolve Projeto</p><p>Código</p><p>NomeNome</p><p>Horas</p><p>trabalhadas</p><p>Funcionário</p><p>CPF</p><p>Desenvolve Projeto</p><p>Código</p><p>NomeNome</p><p>• Um CR tem representatividade mesmo sem atributos, pois ele relaciona</p><p>as entidades.</p><p>– Ex.: No relacionamento acima as horas trabalhadas em cada projeto</p><p>podem não ser de interesse da empresa.</p><p>Papéis de relacionamento</p><p>• Cada conjunto de entidades que participa de um conjunto de relacionamentos</p><p>tem um papel nesse relacionamento.</p><p>26</p><p>• A indicação dos papéis no diagrama é opcional, embora eles sempre existam.</p><p>Aluno matricula Disciplina</p><p>Matricula em Recebe matrícula</p><p>• Como ler:</p><p>– Da esquerda para a direita: “Aluno matricula em disciplina”;</p><p>– Da direita para a esquerda: “Disciplina recebe matricula de alunos”.</p><p>• A indicação de papéis</p><p>deve ser feita sempre que houver ambiguidade na</p><p>interpretação do CR.</p><p>• Exemplo:</p><p>Empresa contrata Curso</p><p>– Quem contrata quem?</p><p>• Neste caso, o curso contrata a empresa (i.e. a empresa é contratada pelo</p><p>curso);</p><p>Empresa contrata Curso</p><p>contratada contrata</p><p>• Neste outro caso,a situação inversa: a empresa contrata o curso (i.e. o</p><p>curso é contratado pela empresa).</p><p>Empresa contrata Cursocontrata contratado</p><p>27</p><p>Autorrelacionamento</p><p>• Ocorre quando um mesmo conjunto de entidades desempenha mais de um</p><p>papel num mesmo conjunto de relacionamentos.</p><p>Funcionário</p><p>supervisiona</p><p>supervisor supervisionado</p><p>Disciplina</p><p>pré-</p><p>requisito</p><p>tem pré-</p><p>requisito</p><p>é pré-</p><p>requisito</p><p>• Um funcionário é supervisor de outros funcionários / funcionários são</p><p>supervisionados por um funcionário.</p><p>• Uma disciplina tem como pré-requisito outra disciplina. Ou vice-versa:</p><p>uma disciplina é pré-requisito de outra disciplina.</p><p>Restrições nos tipos de relacionamento</p><p>• As relações normalmente possuem algumas restrições que limitam a possibilidade</p><p>de combinação de entidades que possam participar do respectivo CR.</p><p>• Essas restrições têm origem na análise de requisitos do banco de dados.</p><p>• Exemplos: cada funcionário deve trabalhar exatamente em um departamento;</p><p>um empregado não precisa ser, necessariamente, um gerente; etc.</p><p>• Restrições Estruturais:</p><p>– Cardinalidade;</p><p>– Participação.</p><p>Restrições de cardinalidade</p><p>• Indica o número máximo de instâncias de relação que uma entidade pode</p><p>participar.</p><p>• Exemplo:</p><p>Funcionário trabalha Departamento</p><p>N 1</p><p>28</p><p>– Cardinalidade N : 1 indica que cada departamento pode estar relacionado</p><p>com um número qualquer de funcionários, mas cada funcionário só</p><p>pode estar relacionado a um departamento.</p><p>– i.e.: um departamento emprega qualquer número de funcionários,</p><p>porém um funcionário somente pode trabalhar num departamento.</p><p>– Interpretação: da esquerda para a direita: “funcionário trabalha para</p><p>um departamento”;</p><p>da direita para a esquerda: “No departamento trabalham N funcionários”.</p><p>• Cardinalidades possíveis em um relacionamento binário: 1 : 1, 1 : N ,</p><p>N : 1, e M : N .</p><p>• Um-para-um (1 : 1):</p><p>– 1 empregado gerencia no máximo 1 departamento;</p><p>– 1 departamento é gerenciado por no máximo 1 empregado.</p><p>• Um-para-muitos (1 : N ou N : 1):</p><p>– 1 departamento emprega N funcionários (N ∈ {0, 1, . . . });</p><p>– 1 funcionário trabalha para (no máximo) 1 departamento.</p><p>• Muitos-para-muitos (M : N):</p><p>– 1 projeto é desenvolvido por M funcionários (M ∈ {0, 1, . . . });</p><p>– 1 funcionário desenvolve N projetos (N ∈ {0, 1, . . . }).</p><p>Exemplos:</p><p>Funcionário gerencia Departamento</p><p>1 1</p><p>Funcionário trabalha Departamento</p><p>N 1</p><p>Funcionário desenvolve Projeto</p><p>M N</p><p>29</p><p>Mais exemplos:</p><p>Ementa descreve Disciplina</p><p>1 1</p><p>Professor leciona Turma</p><p>1 N</p><p>Aluno matricula Disciplina</p><p>M N</p><p>Autorrelacionamento e cardinalidade</p><p>Funcionário</p><p>supervisiona</p><p>supervisor</p><p>1</p><p>supervisionado</p><p>N</p><p>Disciplina</p><p>pré-</p><p>requisito</p><p>tem pré-</p><p>requisito</p><p>M</p><p>é pré-</p><p>requisito</p><p>N</p><p>• Um funcionário é supervisor de N funcionários; Um funcionário é supervisionado</p><p>por 1 funcionário.</p><p>• Uma disciplina tem como pré-requisitos N disciplinas. Ou vice-versa:</p><p>Uma disciplina é pré-requisito para M disciplinas.</p><p>30</p><p>Outra notação (RAMAKRISHNAN; GEHRKE, 2008)</p><p>Funcionário gerencia Departamento</p><p>1 1</p><p>Funcionário trabalha Departamento</p><p>N 1</p><p>Funcionário desenvolve Projeto</p><p>M N</p><p>(As cardinalidades 1, N , não devem aparecer nesta representação. Elas são</p><p>mostradas aqui somente para referência.)</p><p>Grau do conjunto de relacionamentos</p><p>• Um conjunto de relacionamentos (CR) pode envolver dois ou mais conjuntos</p><p>de entidades (CE).</p><p>Grau do CR</p><p>Número de CEs envolvidos.</p><p>• Ex.: Dois CEs ⇒ CR binário;</p><p>• Ex.: Três CEs ⇒ CR ternário.</p><p>• CR binário:</p><p>Aluno matricula Disciplina</p><p>M N</p><p>• CR ternário:</p><p>Aluno monitora Disciplina</p><p>Professor</p><p>31</p><p>Dado um professor e uma disciplina, pode existir mais de um aluno monitor</p><p>que a monitora.</p><p>Aluno monitora Disciplina</p><p>Professor</p><p>N</p><p>Dado um professor e um aluno monitor, existe no máximo uma disciplina</p><p>que esse aluno monitora.</p><p>Aluno monitora Disciplina</p><p>Professor</p><p>N 1</p><p>Dados uma disciplina e um aluno monitor, mais de um professor pode ser</p><p>responsável.</p><p>Aluno monitora Disciplina</p><p>Professor</p><p>N 1</p><p>M</p><p>Restrição de participação</p><p>• Especifica se a existência de uma entidade depende ou não do fato de ela</p><p>estar relacionada com outra.</p><p>• Tal restrição indica o número mínimo de instâncias de relacionamento que</p><p>cada entidade pode participar.</p><p>• Dois tipos:</p><p>– Total;</p><p>32</p><p>– Parcial.</p><p>Total.</p><p>• Ex.: Todo empregado deve trabalhar num departamento, então uma entidade</p><p>Empregado deve participar do relacionamento trabalha.</p><p>• Representação: linha dupla conectando o CE ao CR.</p><p>Empregado trabalha Departamento</p><p>N 1</p><p>Departamento controla Projeto</p><p>1 N</p><p>• Não é esperado que todos os empregados gerenciem um departamento</p><p>(participação parcial);</p><p>• Mas todos os departamentos devem ter um gerente (participação total).</p><p>Empregado gerencia Departamento</p><p>1 1</p><p>2.6 Entidades fracas</p><p>• Vimos previamente o conceito de chave: atributos que permitem distinguir</p><p>as entidades de forma unívoca, quando repetições não são permitidas.</p><p>• Porém nem todas as entidades precisam ser necessariamente distintas entre</p><p>si:</p><p>Entidade fraca</p><p>Todo CE que não possui atributo(s) chave, i.e. não pode ser distinguível</p><p>pois a combinação dos valores de seus atributos pode ser idêntica para</p><p>duas ou mais entidades.</p><p>• Uma entidade que não é fraca (i.e. possui chave) é simplesmente uma</p><p>entidade regular ou ainda, forte.</p><p>33</p><p>• As entidades fracas são identificadas por meio da relação que possuem</p><p>com alguma entidade forte, em combinação com algum de seus atributos.</p><p>• Exemplo: entidade Funcionario do BD de alguma empresa se relaciona</p><p>com entidade Dependente, que é fraca, pois possui como atributos NomeD</p><p>e Sexo apenas.</p><p>Funcionário possui Dependente</p><p>1 N</p><p>CPF Nome SexoNomeD</p><p>• Representação por linha dupla.</p><p>– Tanto na entidade fraca quanto no relacionamento com a entidade</p><p>forte que a identifica.</p><p>• Chave parcial: permite identificar as entidades fracas que estão relacionadas</p><p>com a mesma entidade forte. Ex.: pode existir mais de um João como</p><p>dependente no cadastro de empresa, mas não dois dependentes com nome</p><p>João do(a) mesmo(a) funcionário(a), permitindo diferenciá-los.</p><p>– Representação por um sublinhado tracejado.</p><p>• As entidades fracas sempre possuem restrição de participação total em</p><p>relação a seu relacionamento identificador.</p><p>– Ex.: Não faz sentido cadastrar um dependente sem ele esteja associado</p><p>a um funcionário.</p><p>No exemplo, a participação total está indicada pela linha dupla conectando</p><p>a entidade fraca ao relacionamento.</p><p>• Note que nem toda participação total é entidade fraca. Se a entidade tem</p><p>chave, ela não é fraca.</p><p>– Ex.: A entidade Carteira de motorista não pode existir se não</p><p>existir a entidade Pessoa com quem ela está relacionada. Porém, a</p><p>mesma não é entidade fraca pois possui chave (o número da carteira).</p><p>• Chave primária de Funcionário: CPF.</p><p>• Chave primária de Dependente: CPF + NomeD.</p><p>Chave primária da entidade forte + chave parcial da fraca.</p><p>• Entidade fraca (subordinada): Dependente;</p><p>34</p><p>• Entidade forte (dominante): Funcionário.</p><p>• O CE fraca às vezes pode ser representado como atributos complexos</p><p>(compostos, multivalorados).</p><p>– Ex.: entidade que possui mais de um endereço.</p><p>• Isso não pode ocorrer caso a entidade fraca participe de mais de um</p><p>relacionamento.</p><p>Outro exemplo</p><p>Ementa descreve Disciplina</p><p>1 1</p><p>Não há motivo para armazenar as ementas de disciplinas que não existam</p><p>mais, ou ementas que não pertençam a nenhuma disciplina.</p><p>2.7 Agregação</p><p>• Como visto, um CR é uma associação entre dois ou mais CEs.</p><p>• Em algumas situações, é necessário entretanto modelar um relacionamento</p><p>que envolva uma coleção de entidades e relacionamentos.</p><p>– Ex.: A agregação Aluno é composta pelos CEs Pessoa e Universidade</p><p>. Neste caso, o CR orienta é estabelecido entre a agregação Aluno</p><p>e a CE Professor.</p><p>Pessoa ingressa Universidade</p><p>N M</p><p>CPF Nome NomeCódigoData_ingresso</p><p>Aluno</p><p>orienta Professor</p><p>1</p><p>M</p><p>35</p><p>Outro exemplo:</p><p>Médico consulta Paciente</p><p>N M</p><p>CPF Nome NomeCPF</p><p>Consulta</p><p>prescreve Medicamento</p><p>N</p><p>M</p><p>Alternativa sem agregação:</p><p>Médico Paciente</p><p>CPF Nome NomeCPF</p><p>prescreve Medicamento</p><p>N</p><p>N</p><p>Consulta</p><p>NN</p><p>1 1</p><p>2.8 Passos para elaboração do projeto lógico</p><p>1. Identificar as entidades e os atributos.</p><p>• As entidades possuem informações descritivas.</p><p>• Atributos devem associados às entidades que descrevem.</p><p>2. Identificar chaves primárias.</p><p>3. Identificar os relacionamentos e seus atributos.</p><p>• Determinar o grau dos relacionamentos (ex.: binário, ternário, etc.).</p><p>• Identificar as restrições que se aplicam em cada relacionamento.</p><p>– Cardinalidade.</p><p>– Participação.</p><p>4. Identificar as entidades fortes e fracas.</p><p>36</p><p>2.9 Exercícios</p><p>1. Com base no Diagrama ER a seguir, descreva os principais requisitos do</p><p>banco de dados COMPANHIA.</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Adaptado de (ELMASRI; NAVATHE, 2018).</p><p>Reproduza o diagrama em um software de modelagem de sua escolha.</p><p>Exemplos (fonte: https://www.holistics.io/blog/top-5-fre</p><p>e-database-diagram-design-tools):</p><p>• dbdiagram.io</p><p>• Diagrams.net</p><p>• Lucidchart</p><p>• QuickDBD</p><p>• ERD Plus</p><p>2. Faça um diagrama entidade-relacionamento para os seguintes requisitos:</p><p>(a) Os professores têm um CPF, um nome, uma idade, uma posição e</p><p>uma especialidade de pesquisa.</p><p>37</p><p>(b) Os projetos têm um número de projeto, um nome de financiador (por</p><p>exemplo, CNPq), uma data inicial, uma data final e um orçamento.</p><p>(c) Os estudantes de pós-graduação têm um CPF, um nome, uma idade e</p><p>um programa de pós-graduação (por exemplo, mestrado ou doutorado).</p><p>(d) Cada projeto é gerenciado por um professor (conhecido como pesquisador</p><p>principal do projeto).</p><p>(e) Cada projeto é conduzido por um ou mais professores (conhecidos</p><p>como co-pesquisadores).</p><p>(f) Os professores podem gerenciar e/ou trabalhar em múltiplos projetos.</p><p>(g) Cada projeto é conduzido por um ou mais estudantes de pós-graduação</p><p>(conhecidos como os assistentes de pesquisa do projeto).</p><p>(h) Quando os alunos de pós-graduação conduzem um projeto, um professor</p><p>deve supervisionar seu trabalho no projeto. Os alunos de pós-graduação</p><p>podem trabalhar em múltiplos projetos e neste caso eles terão um</p><p>supervisor (potencialmente diferente) para cada projeto.</p><p>(i) Os departamentos têm um número de departamento, um nome e um</p><p>escritório principal.</p><p>(j) Os departamentos têm um professor (conhecido como chefe do departamento)</p><p>que o administra.</p><p>(k) Os professores trabalham em um ou mais departamentos e, para cada</p><p>departamento em que trabalham, uma porcentagem de tempo está</p><p>associada a seu trabalho.</p><p>(l) Os alunos de pós-graduação têm um departamento principal no qual</p><p>estão conduzindo seu programa de pós-graduação.</p><p>(m) Cada aluno tem um outro aluno mais experiente (conhecido como</p><p>conselheiro do aluno) que o aconselha nos cursos a que deve assistir.</p><p>3. A Notown Records decidiu armazenar informações sobre músicos que tocam</p><p>em seus álbuns(assim como outros dados da empresa) em um banco de</p><p>dados. A empresa escolheu sabiamente contratar você como projetista de</p><p>banco de dados.</p><p>(a) Cada músico que grava na Notown tem um CPF, um nome, um</p><p>endereço, e um número de telefone. Músicos (que não ganham muito</p><p>normalmente) compartilham o mesmo endereço, e nenhum endereço</p><p>tem mais do que um telefone.</p><p>(b) Cada instrumento utilizado nas músicas gravadas na Notown tem um</p><p>nome (por exemplo, guitarra, sintetizador, flauta) e um tom musical</p><p>(por exemplo C, B#, E#).</p><p>(c) Cada álbum gravado com o selo Notown tem um título, uma data</p><p>de direitos autorais, um formato (por exemplo, CD ou MC) e um</p><p>identificador de álbum.</p><p>38</p><p>(d) Cada música gravada na Notown tem um título e um autor.</p><p>(e) Cada músico toca diversos instrumentos e um determinado instrumento</p><p>pode ser tocado por vários músicos.</p><p>(f) Cada música é interpretada por um ou mais músicos, e um músico</p><p>pode interpretar mais de uma música.</p><p>(g) Cada álbum tem exatamente um músico que trabalha como seu produtor.</p><p>Naturalmente, um músico pode produzir diversos álbuns.</p><p>4. (HEUSER, 2004) Locadora de Vídeo (adaptado do material de um curso</p><p>de modelagem de dados Oracle).</p><p>Uma pequena locadora de vídeos possui ao redor de 2.000 fitas de vídeo,</p><p>cujo empréstimo deve ser controlado.</p><p>Cada fita possui um número. Para cada filme, é necessário saber seu</p><p>título e sua categoria (comédia, drama, aventura). Cada filme recebe um</p><p>identificador próprio. Para cada fita é controlado que filme ela contém.</p><p>Para cada filme há pelo menos uma fita, e cada fita contém somente um</p><p>filme. Alguns poucos filmes necessitam duas fitas.</p><p>Os clientes podem desejar encontrar os filmes estrelados pelo seu ator</p><p>predileto. Por isso, é necessário manter a informação dos atores que</p><p>estrelam em cada filme. Nem todo filme possui estrelas. De cada ator,</p><p>os clientes às vezes desejam saber o nome real, bem como a data de</p><p>nascimento.</p><p>A locadora possui muitos clientes cadastrados. Somente clientes cadastrados</p><p>podem alugar fitas. Para cada cliente, é necessário saber seu prenome e seu</p><p>sobrenome, seu telefone e seu endereço. Além disso, cada cliente recebe</p><p>um número de associado.</p><p>Finalmente, desejamos saber que fitas cada cliente tem emprestada. Um</p><p>cliente pode ter várias fitas em um instante no tempo. Não são mantidos</p><p>registros históricos de aluguéis.</p><p>5. (HEUSER, 2004) Reservas de passagens aéreas:</p><p>O objetivo é projetar um sistema de reservas para uma companhia de</p><p>aviação. O sistema contará com um banco de dados central, que será</p><p>acessado por aplicações clientes, rodando tanto dentro da própria companhia,</p><p>quanto fora dela.</p><p>A transação central do sistema é a reserva. Uma reserva é identificada por</p><p>um código gerado pelo sistema em computador. A reserva é feita para um</p><p>único passageiro, do qual se conhece apenas o nome. A reserva compreende</p><p>um conjunto de trechos de voos, que acontecerão em determinada data</p><p>e hora. Para cada trecho, a reserva é feita em uma classe (econômica,</p><p>executiva, etc.).</p><p>Um voo é identificado por um código e possui uma origem e um destino.</p><p>Por exemplo, o voo 595 sai de Porto Alegre, com destino a São Paulo. Um</p><p>39</p><p>voo é composto de vários trechos, correspondendo às escalas intermediárias</p><p>do voo. Por exemplo, o voo 595 é composto de dois trechos, um de Porto</p><p>Alegre a Londrina, o outro de Londrina a São Paulo. Cabe salientar que</p><p>há cidades que são servidas por vários aeroportos. Por isso, é importante</p><p>informar ao passageiro que faz a reserva, qual é o aeroporto no qual o voo</p><p>passa.</p><p>Às vezes os clientes, ao fazer a reserva, desejam saber qual é o tipo</p><p>de aeronave que será utilizada em determinado trecho do voo. Alguns</p><p>poucos vôos, principalmente internacionais, têm troca de aeronave em</p><p>determinadas escalas.</p><p>Nem todos os voos operam em todos os dias da semana. Inclusive, certos</p><p>voos têm pequenas mudanças de horário em certos dias da semana.</p><p>Cada reserva possui um prazo de validade. Caso os bilhetes não tenham</p><p>sido emitidos, até esgotar-se o prazo da reserva, a mesma é cancelada.</p><p>Reservas podem ser prorrogadas.</p><p>Como o “check-in” de todos os voos está informatizado, a companhia</p><p>possibilita a reserva de assento para o passageiro. Reservas de assento</p><p>podem ser feitas com até 6 meses de antecedência.</p><p>Além de efetivar reservas, o sistema deve servir para vários tipos de</p><p>consultas que os clientes podem querer fazer:</p><p>(a) possibilidades de viagem de uma cidade ou de um aeroporto para o</p><p>outro;</p><p>(b) o mesmo, mas restrito a determinados dias da semana;</p><p>(c) horários de chegada ou de saída em determinados voos;</p><p>(d) disponibilidade de vagas em um trecho de voo;</p><p>(e) disponibilidade de determinados assentos em um trecho de voo.</p><p>Referências</p><p>1. ELMASRI R.; NAVATHE, S. Sistemas de banco de dados, Tradução da</p><p>7a. edição, Addison- Wesley, São Paulo, 2018.</p><p>2. HEUSER, C. A. Projeto de Banco de Dados, 5a. Edição, Porto Alegre:</p><p>Sagra, 2004.</p><p>3. RAMAKRISHNAN, R.; GEHRKE, J. Sistemas de Gerenciamento de Banco</p><p>de Dados, Tradução da 3a. Edição Americana, McGraw-Hill Interamericana,</p><p>2008.</p><p>Os materiais de parte desta seção foram gentilmente cedidos por Bruno A.</p><p>N. Travençolo (FACOM/UFU)</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>40</p><p>3 Modelo relacional (MR)</p><p>3.1 Introdução</p><p>Minimundo</p><p>Análise de</p><p>requisitos</p><p>Projeto</p><p>conceitual</p><p>Modelo</p><p>relacional Implementação</p><p>Modelo entidade-</p><p>relacionamento (MER) SQL</p><p>Mapeamento</p><p>MER p/ o</p><p>modelo relacional</p><p>Modelo relacional (MR)</p><p>• Introduzido por Ted Codd (IBM) em 19704.</p><p>– Simplicidade e base matemática;</p><p>– Base teórica na teoria de conjuntos e na lógica de predicados de</p><p>primeira ordem;</p><p>– é um método declarativo de especificar dados e consultas.</p><p>– Primeiras implementações: início da década de 1980 (SQL/DS; ORACLE).</p><p>• Banco de dados:</p><p>– representado como uma coleção de relações.</p><p>• Relação:</p><p>– Possui um nome que é único em um BD;</p><p>– Representado por uma tabela bidimensional de valores.</p><p>Tabela bidimensional</p><p>• Cada coluna tem um nome distinto e representa um atributo.</p><p>– Todos os valores de uma coluna são valores do mesmo atributo.</p><p>• Cada atributo possui um domínio de valores.</p><p>4https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf</p><p>41</p><p>• Cada domínio é formado por um conjunto de valores atômicos.</p><p>– Por atômico entendemos que o valor é indivisível no domínio.</p><p>• Cada linha da tabela representa o relacionamento entre um conjunto de</p><p>valores.</p><p>• Cada linha é distinta e representa uma tupla.</p><p>• Uma n-tupla representa uma tupla que possui n valores.</p><p>– grau da relação: número n de atributos de seu esquema de relação.</p><p>Exemplo: uma tabela Aluno</p><p>Esquema de relação: Aluno (nmat, nome, endereço, idade)</p><p>nmat Nome Endereço Idade</p><p>935639 Adriana Zagalo Rua Floriano Peixoto, 1234 18</p><p>935632 Beatriz da Silva Rua Itambé, 124 apto. 62 bloco B 22</p><p>933219 Carlos Alberto Bozato Rua Sucupira, 3452 apto 125 19</p><p>938904 Antônio Nascimento Av. Castro Alves, 57 18</p><p>934789 Roberto Antonione Av. Sunab Jatab, 3467 apto 32 32</p><p>Aluno</p><p>Nome da relação Atributos</p><p>Nome do atributo</p><p>Valor tupla ou linha</p><p>3.2 Definições formais</p><p>Passaremos agora a definir formalmente os elementos do modelo relacional.</p><p>Domínio</p><p>Domínio</p><p>Um domínio D qualquer é um conjunto de valores atômicos.</p><p>Possui uma cardinalidade, denotada por |D|, que indica a quantidade de</p><p>valores de D.</p><p>• Exemplos:</p><p>– D1 = dom(sexo) = {F, M}; |D1| = 2.</p><p>– D2 = dom(funcionario.idade) = [15, 80]; |D2| = 66.</p><p>• Também possui um tipo de dado ou formato. Exemplos:</p><p>– funcionario.idade: inteiro;</p><p>42</p><p>– fone: (dd)ddddd.dddd, onde cada d representa um dígito numérico</p><p>decimal.</p><p>– int, date, etc.</p><p>Esquema de relação e atributo</p><p>Esquema de relação</p><p>Um esquema de relação corresponde à descrição da relação representada na</p><p>tabela de valores.</p><p>Notação: R(A1, A2, . . . , An), onde R é o nome da relação – ex.: Aluno visto</p><p>anteriormente – e cada Ai, 1 ≤ i ≤ n, é o nome de um atributo.</p><p>Atributo</p><p>Nome do papel desempenhado por algum domínio na relação.</p><p>• Exemplo: FUNCIONARIO(cpf, nome, endereco, sexo)</p><p>– dom(cpf) = ddd.ddd.ddd− dd;</p><p>– dom(nome) = cadeia de caracteres que representam nomes de pessoas;</p><p>– dom(endereco) = cadeia de caracteres que representam endereços;</p><p>– dom(sexo) = {F, M}.</p><p>Obs.: Um método comum para especificar o domínio compreende:</p><p>• Definição lógica;</p><p>• Definição do tipo de dado ou formato.</p><p>Ex.: No caso de um esquema de relação Aluno ( nmat, nome, telefone</p><p>, celular, idade ):</p><p>• Definição lógica:</p><p>– Números de matrícula: conjunto de dígitos válidos para matrícula de</p><p>alunos;</p><p>– Nomes de aluno: conjunto de todos os nomes possíveis para pessoas;</p><p>– Números de telefone: conjunto de números de telefone válidos no</p><p>Brasil;</p><p>– Idade: conjunto de idades possíveis para alunos.</p><p>• Definição do tipo de dado ou formato:</p><p>– Números de matrícula: inteiro com oito dígitos;</p><p>– Nomes de aluno: string de 60 caracteres;</p><p>– Números de telefone: inteiro com dez dígitos;</p><p>– Idade: inteiro entre zero e 127.</p><p>43</p><p>Tuplas e valores de atributos</p><p>Tupla</p><p>Uma n-tupla, ou simplesmente tupla t, é um conjunto ordenado de valores,</p><p>denotado por t =< v1, v2, . . . , vn >, onde cada vi, 1 ≤ i ≤ n é derivado do</p><p>domínio apropriado de Ai ou é um valor nulo.</p><p>Observação: Se t =< v1, v2, . . . , vn >, então t[Ai] = t.Ai = vi (valor).</p><p>Valor nulo</p><p>O valor nulo, denotado por NULL, é um valor especial para representar valores</p><p>desconhecidos ou que não se aplicam.</p><p>• Exemplo: FUNCIONARIO(cpf, nome, endereco, sexo)</p><p>– funcionario = <12363289544, 'John Smith', '101 Main</p><p>St. Atlanta, GA 30332', 'M'></p><p>– funcionario.sexo = 'M'.</p><p>Relação</p><p>Relação</p><p>Uma relação ou estado r de um esquema R(A1, A2, . . . , An), denotada por r(R),</p><p>é um conjunto de tuplas r = {t1, t2, . . . , tm}.</p><p>Observações:</p><p>• O uso do termo estado para r reflete as tuplas válidas que representam</p><p>um estado particular do mundo real.</p><p>• O esquema R é relativamente estático, mas em geral, o estado da relação</p><p>r é dinâmico;</p><p>• n é o grau do esquema da relação (número de atributos);</p><p>• m é a cardinalidade da relação (número de tuplas).</p><p>Exemplo de possível relação do esquema Aluno (nmat, nome, telefone</p><p>, celular, idade):</p><p>r(Aluno) = {<22222222>, Júlia, 1134343434, 1126262626,</p><p>21>,</p><p><11111111>, Pedro, 1965656565, 1977777777,</p><p>18>,</p><p><99999999>, Cecília, 1144443333, 1165658888,</p><p>23>}</p><p>44</p><p>Características das relações</p><p>• Ordenação de tuplas numa relação (nível abstrato):</p><p>– Matematicamente, não há ordem entre os elementos de um conjunto.</p><p>– Na implementação de um SGBD existe uma ordem física de armaze-</p><p>namento das tuplas na memória externa</p><p>∗ Determina uma ordem na recuperação das informações.</p><p>• Ordenação de tuplas em uma relação (nível lógico):</p><p>– muitas ordens lógicas podem ser especificadas para uma relação.</p><p>Ex.: relação ALUNO pode ser ordenada pelos atributos NOME,</p><p>DATANASCIMENTO, CPF, etc.</p><p>• Ordenação de valores dentro de uma tupla:</p><p>– uma tupla é uma lista de n valores dispostos numa ordem determinada</p><p>de acordo com a disposição dos atributos no esquema da relação.</p><p>• Valores nas tuplas:</p><p>– são atômicos e monovalorados (relações não permitem atributos mul-</p><p>tivalorados).</p><p>3.3 Restrições</p><p>Restrições são condições que devem ser mantidas por todos os estados válidos</p><p>das relações.</p><p>Categorias:</p><p>• Inerentes: são características do modelo de dados. Ex.: uma relação não</p><p>pode ter tuplas duplicadas;</p><p>• Baseadas em esquema: podem ser expressas no modelo de dados. Exs.:</p><p>restrições de domínio e restrições de chave (serão vistas em seguida);</p><p>• Baseadas na aplicação: não podem ser expressas no modelo de dados,</p><p>portanto devem ser implementadas por meio de programas de aplicação.</p><p>Restrições sobre uma relação</p><p>• Domínio:</p><p>– Dentro de cada tupla, o valor de cada atributo Ai deve ser um valor</p><p>atômico de dom(Ai).</p><p>45</p><p>• Unicidade de chave:</p><p>– Chave primária ⇒ identifica de forma única cada tupla da relação.</p><p>• Valor nulo:</p><p>– Permitido: NULL (padrão);</p><p>– Se não permitido: atributo NOT NULL.</p><p>• Integridade de entidade:</p><p>– Nenhum valor de chave primária pode ser nulo.</p><p>∗ Permitir valor NULL para a chave primária implica que não podemos</p><p>identificar algumas tuplas.</p><p>∗ I.e. não seria possível diferenciá-las ao tentar referenciá-las por</p><p>outras relações.</p><p>Chaves e Superchaves</p><p>Superchave</p><p>Numa relação r, é um conjunto de atributos SCh contido em R no qual não</p><p>haverá duas tuplas ti e tj , com i ̸= j, tais que ti[SCh] = tj [SCh].</p><p>• Como r é um conjunto de tuplas, e, por definição, todos os elementos de um</p><p>conjunto são distintos entre si, é sempre possível definir uma superchave</p><p>Sch – sempre existirá um conjunto de atributos que, juntos, permita</p><p>diferenciar todas as tuplas entre si.</p><p>• Obs.: O próprio esquema R como um todo pode ser uma superchave.</p><p>Chave</p><p>Superchave Ch com a propriedade adicional de que a remoção de qualquer</p><p>atributo da chave fará com que Ch não identifique mais unicamente cada tupla</p><p>da relação.</p><p>• A diferença é que uma chave tem que ser mínima – a superchave não.</p><p>• I.e. toda chave é superchave, mas não vice-versa:</p><p>a superchave pode ser</p><p>chave – se o número de atributos é mínimo – ou não.</p><p>Exemplo: Esquema de relação Aluno (nmat, nome, telefone, celular,</p><p>idade):</p><p>• {nmat} é chave – dois alunos distintos não podem ter o mesmo número</p><p>de matrícula.</p><p>• Superchaves:</p><p>– {nmat, nome};</p><p>– {nmat, nome, telefone};</p><p>– {nmat, nome, telefone, celular};</p><p>– {nmat, nome, telefone, celular, idade}.</p><p>46</p><p>Chave candidata e chave primária</p><p>• Se um esquema de relação tiver mais de uma chave, então cada uma será</p><p>chave candidata;</p><p>• Uma é arbitrariamente designada para ser chave primária.</p><p>– Boa prática: que tenha atributo único ou ao menos poucos atributos.</p><p>• Notação: atributos que formam a chave primária são sublinhados. As</p><p>demais candidatas (designadas agora como chaves únicas) não são destacadas.</p><p>Exemplo: Esquema de relação Aluno (nmat, nome, telefone, ce-</p><p>lular, idade):</p><p>• {nmat} é a única chave candidata de Aluno, portanto é também a chave</p><p>primária.</p><p>Outro exemplo (ELMASRI; NAVATHE, 2018): a relação CARRO que segue</p><p>tem duas chaves candidatas: Placa e Numero_chassi. A primeira foi designada</p><p>como chave primária.</p><p>CARRO</p><p>Placa Numero_chassi Marca Modelo Ano</p><p>Itatiaia ABC-7039 A6935207586 Volkswagen Gol 02</p><p>Itu TVP-3470 B4369668697 Chevrolet Corsa 05</p><p>Santos MPO-2902 X8355447376 Fiat Uno 01</p><p>Itanhaem TFY-6858 C4374268458 Chevrolet Celta 99</p><p>Itatiba RSK-6279 Y8293586758 Renault Clio 04</p><p>Atibaia RSK-6298 U0283657858 Volkswagen Parati 04</p><p>• Chave primária para um esquema de relação R satisfaz duas restrições:</p><p>– Duas tuplas distintas não podem ter valores idênticos para os atributos</p><p>da chave.</p><p>– Ela é uma superchave mínima.</p><p>Esquema de um banco de dados</p><p>• Os conceitos vistos até então referem-se a relações isoladas e seus atributos.</p><p>• No entanto, um banco de dados relacional em geral é formado de várias</p><p>relações, com tuplas que podem se relacionar de várias formas.</p><p>Esquema de um banco de dados relacional</p><p>É o conjunto dos esquemas de relação de um banco de dados, denotado por</p><p>S = {R1, R2, . . . , Rm} .</p><p>Em geral, a definição também engloba, além do conjunto S, o conjunto de</p><p>restrições de integridade IC, que será visto a seguir.</p><p>47</p><p>A partir da definição anterior, também é possível definir o estado do banco</p><p>de dados DB – o conjunto de estados da relação – DB = {r1, r2, . . . , rm} onde</p><p>cada ri é um estado ou relação do esquema Ri.</p><p>• Os estados ri devem satisfazer às restrições de integridade especificadas</p><p>em IC.</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>FUNCIONARIO</p><p>Nome CPF Data_nasc Endereco Salario Sexo CPF_Supervisor Dnumero</p><p>DEPARTAMENTO</p><p>DNome DNumero CPF_gerente Data_inicio_gerente</p><p>DEPTO_LOCALIZACOES</p><p>DNumero Dlocalizacao</p><p>PROJETO</p><p>PjNome PNumero PLocal Depto</p><p>TRABALHA_EM</p><p>PNumeroCPF Horas</p><p>DEPENDENTE</p><p>CPF_funcionario Nome Sexo Data_nasc Parentesco</p><p>Restrição de integridade referencial</p><p>• A restrição de integridade referencial mantém a consistência entre as</p><p>tuplas de duas relações:</p><p>– Declara que uma tupla numa relação, a qual faz referência a outra</p><p>relação, deve necessariamente se referir a uma tupla existente nessa</p><p>segunda.</p><p>• Definida entre a chave estrangeira ChE (em inglês: foreign key) de uma</p><p>relação esquema R1 e a chave primária ChP de uma relação esquema R2.</p><p>Chave estrangeira</p><p>Chave estrangeira</p><p>Dados dois esquemas de relação R1 e R2, um conjunto de atributos5 ChE de</p><p>R1 é uma chave estrangeira de R1 que referencia R2 se:</p><p>• Os atributos de ChE têm os mesmos domínios que os atributos que foram</p><p>ChP , a chave primária de R2;</p><p>5Pode ser um atributo único</p><p>48</p><p>• Um valor de ChE numa tupla t1 do estado atual r1(R1):</p><p>– ocorre como um valor de ChP para alguma tupla t2 no estado atual</p><p>r2(R2) (i.e. t1[ChE] = t2[ChP ]); ou é NULL.</p><p>• No caso em que t1[ChE] = t2[ChP ], dizemos que t1 referencia ou refere-se</p><p>a t2.</p><p>• R1 é então a relação que faz referência e R2, a relação referenciada.</p><p>• Se as condições de mantiverem, então é mantida uma restrição de integridade</p><p>referencial de R1 para R2.</p><p>Observação: não confundir restrição de integridade referencial com a restrição</p><p>de integridade de identidade vista anteriormente (de que nenhuma chave primária</p><p>pode ser NULL).</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>FUNCIONARIO</p><p>Nome CPF Data_nasc Endereco Salario Sexo CPF_Supervisor Dnumero</p><p>DEPARTAMENTO</p><p>DNome DNumero CPF_gerente Data_inicio_gerente</p><p>DEPTO_LOCALIZACOES</p><p>DNumero Dlocalizacao</p><p>PROJETO</p><p>PjNome PNumero PLocal Depto</p><p>TRABALHA_EM</p><p>PNumeroCPF Horas</p><p>DEPENDENTE</p><p>CPF_funcionario Nome Sexo Data_nasc Parentesco</p><p>3.4 Operações</p><p>Restrições e operações</p><p>• Operações de modificação (típicas de BD):</p><p>– Inserção (insert);</p><p>– Remoção (delete);</p><p>– Alterar ou modificar (update).</p><p>• Quando tais operações ocorrem, as restrições de integridade especificadas</p><p>no esquema do banco de dados relacional não devem ser violadas.</p><p>49</p><p>Operação insert</p><p>• Característica:</p><p>– fornece uma lista de valores de atributos para uma nova tupla t, que</p><p>é inserida em uma relação R.</p><p>• Pode violar as seguintes restrições:</p><p>– domínio (valores dos atributos de t são todos atômicos?);</p><p>– unicidade de chave;</p><p>– integridade de entidade (chave primária de t é diferente de NULL?);</p><p>– integridade referencial.</p><p>• Soluções:</p><p>– rejeitar a inserção;</p><p>– enviar mensagem de erro ao usuário.</p><p>Operação delete</p><p>• Característica:</p><p>– remove uma ou mais tuplas.</p><p>• Pode violar a integridade referencial:</p><p>– quando as tuplas removidas forem referidas por chaves estrangeiras</p><p>de outras tuplas.</p><p>• Soluções:</p><p>– rejeitar a remoção;</p><p>– remover em cascata;</p><p>– modificar valores dos atributos de referência.</p><p>Operação update</p><p>• Característica:</p><p>– altera valores de alguns atributos em tuplas.</p><p>• Pode violar as seguintes restrições:</p><p>– domínio;</p><p>– unicidade de chave (se algum atributo sendo alterado é ou faz parte</p><p>da chave primária);</p><p>– integridade de entidade (idem ao caso anterior);</p><p>50</p><p>– integridade referencial (se algum atributo sendo alterado é ou faz</p><p>parte de uma chave estrangeira).</p><p>• Soluções:</p><p>– semelhantes à vistas nas operações anteriores (a atualização pode ser</p><p>interpretada como uma operação delete seguida de insert).</p><p>3.5 Exercícios</p><p>1. Uma administradora trabalha com administração de condomínios e de</p><p>aluguéis.</p><p>Uma entrevista com o gerente resultou nas seguintes informações:</p><p>• São administrados condomínios formados por unidades condominiais.</p><p>Para cada condomínio a administradora precisa saber seu nome (que</p><p>é único) e endereço (nome da rua, número, bairro e CEP). Para cada</p><p>unidade condominial seu número único é o andar;</p><p>• Cada unidade condominial é de propriedade de uma ou mais pessoas.</p><p>Uma pessoa pode possuir diversas unidades;</p><p>• Cada unidade pode estar alugada para no máximo uma pessoa. Uma</p><p>pessoa pode alugar diversas unidades. Uma data determina quando</p><p>a pessoa alugou uma unidade condominial;</p><p>• Para cada pessoa são armazenadas informações como CPF, nome e</p><p>telefones (residencial, comercial e celular).</p><p>Pede-se:</p><p>(a) Definir os domínios (definição lógica, tipo de dado e formato) necessários</p><p>para a criação dos esquemas de relação para essa especificação</p><p>• ex: nomes de pessoas: conjunto de todos os nomes possíveis para</p><p>pessoas – strings de 60 caracteres.</p><p>(b) Definir os esquemas de relação para a especificação.</p><p>(c) Instancie relações a partir dos esquemas criados exemplificando situações</p><p>em que não são satisfeitas as seguintes restrições de integridade:</p><p>• unicidade de chave, integridade de entidade e integridade referencial.</p><p>2. Compare os conceitos de Entidade, Relação e Tabela.</p><p>3. Compare os conceitos de instância de uma entidade, tupla de uma relação</p><p>e linha de uma tabela.</p><p>4. Compare os conceitos de atributo no Modelo ER, atributo no Modelo R</p><p>e coluna de uma Tabela.</p><p>5. O que é Chave Estrangeira? Dê um exemplo.</p><p>51</p><p>Referências</p><p>1. ELMASRI R.; NAVATHE, S. Sistemas de banco de dados, Tradução da</p><p>7a. edição, Addison- Wesley, São Paulo, 2018.</p><p>2. SILVA, I. R. (FACOM/UFU). Notas de aula.</p><p>Os materiais de parte desta seção foram gentilmente cedidos por Bruno A.</p><p>N. Travençolo (FACOM/UFU)</p><p>Adaptações: Renato Pimentel, FACOM/UFU</p><p>52</p><p>4 Mapeamento do MER para relacional</p><p>4.1 Introdução</p><p>Minimundo</p><p>Análise de</p><p>requisitos</p><p>Projeto</p><p>conceitual</p><p>Modelo</p><p>relacional Implementação</p><p>Modelo entidade-</p><p>relacionamento (MER) SQL</p><p>Mapeamento</p><p>MER p/ o</p><p>modelo relacional</p><p>Projeto lógico</p><p>Projeto lógico</p><p>É a especificação detalhada da estrutura do banco de dados num modelo que</p><p>possa ser implementado por um SGBD.</p><p>• Uma estratégia de projeto lógico é o mapeamento de um modelo conceitual</p><p>(como o MER) para o modelo relacional.</p><p>• Neste curso, os SGBD considerados são do tipo SGBDR – sistema gerenciador</p><p>de bancos de dados relacional.</p><p>– Modelo relacional ⇒ modelo implementável.</p><p>Etapas do mapeamento do MER para relacional</p><p>1. Mapeamento das entidades regulares (fortes);</p><p>2. Mapeamento das entidades fracas;</p><p>3. Mapeamento dos relacionamentos binários 1 : 1;</p><p>4. Mapeamento dos relacionamentos binários 1 : N ;</p><p>5. Mapeamento dos relacionamentos binários M : N ;</p><p>6. Mapeamento dos atributos multivalorados e compostos;</p><p>53</p><p>4.2 Passo 1: mapeamento das entidades regulares</p><p>• Para cada entidade regular (forte), criar uma relação que inclua todos os</p><p>atributos simples da entidade;</p><p>• Incluir somente os atributos simples dos atributos compostos;</p><p>– Poderia incluir somente o atributo composto;</p><p>• Escolher um dos atributos-chave da entidade para ser a chave primária da</p><p>relação;</p><p>• O atributo derivado pode ou não ser mapeado. Tomar a decisão com base</p><p>na complexidade de seu cálculo e no número de possíveis consultas que</p><p>serão feitas para este atributo.</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>O mapeamento produz os seguintes esquemas de relação:</p><p>FUNCIONARIO(Pnome, NomeM, Unome, CPF, Data_nasc, Endereco,</p><p>Salario, Sexo)</p><p>DEPARTAMENTO(DNome, DNumero)</p><p>PROJETO(PjNome, PNumero, PLocal)</p><p>54</p><p>• A relação FUNCIONARIO poderia ter um único atributo Nome (atributo</p><p>composto no diagrama ER), ao invés de 3 atributos separados;</p><p>• No caso de DEPARTAMENTO e PROJETO, outras chaves primárias poderiam</p><p>ter sido escolhidas.</p><p>• O atributo Localizacoes de DEPARTAMENTO é multivalorado e será</p><p>tratado numa etapa posterior do mapeamento.</p><p>• O atributo Numero_de_funcionarios de DEPARTAMENTO é derivado.</p><p>4.3 Passo 2: mapeamento das entidades fracas</p><p>• Criar uma relação com todos os atributos simples (e os componentes</p><p>simples dos atributos compostos);</p><p>• Inserir como chave estrangeira os atributos que são chave primária das</p><p>entidades fortes identificadoras com quem a entidade fraca se relaciona;</p><p>• A chave primária é composta pelas chaves primárias das entidades fortes</p><p>identificadoras relacionadas em conjunto com a chave parcial da entidade</p><p>fraca.</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>O mapeamento produz a relação:</p><p>DEPENDENTE(CPF_funcionario, Nome, Sexo, Data_nasc, Parentesco)</p><p>55</p><p>• CPF_funcionario é a chave</p><p>estrangeira para o atributo CPF</p><p>da relação FUNCIONARIO.</p><p>• O relacionamento já fica</p><p>mapeado:</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>4.4 Passo 3: mapeamento dos relacionamentos binários</p><p>1 : 1</p><p>• Identificar as entidades que participam do relacionamento.</p><p>• Três opções são possíveis:</p><p>1. Escolha da chave estrangeira – incluir como chave estrangeira numa</p><p>das relações a chave primária da outra.</p><p>2. Relação unificada – junta as duas tabelas numa única relação (possível</p><p>somente quando ambas as participações são totais);</p><p>3. Referência cruzada ou relação de relacionamento – nova tabela com</p><p>um dos atributos sendo chave primária e o outro chave única. Será</p><p>visto para o caso M : N .</p><p>Escolha da chave estrangeira</p><p>• Sendo as duas relações que participam do relacionamento S e T :</p><p>– Escolher uma das relações (por exemplo, S) e inserir nela, como chave</p><p>estrangeira, a chave primária da outra relação (no caso, T );</p><p>∗ A melhor escolha para o papel de S num relacionamento é a</p><p>entidade com participação total no relacionamento.</p><p>– Incluir os atributos simples do relacionamento como atributos de S</p><p>(e também os componentes simples dos atributos compostos).</p><p>S</p><p>ChaveS</p><p>T</p><p>ChaveTAtributo_rel</p><p>1 1</p><p>S(ChaveS, ChaveT, ..., Atributo_rel)</p><p>56</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Continuando o exemplo, atualizamos uma das relações:</p><p>FUNCIONARIO(Pnome, NomeM, Unome, CPF, Data_nasc, Endereco,</p><p>Salario, Sexo)</p><p>DEPARTAMENTO(DNome, DNumero, CPF_gerente, Data_inicio_gerente)</p><p>• A relação DEPARTAMENTO foi atualizada e ganhou os atributos CPF_gerente</p><p>(chave estrangeira para FUNCIONARIO) e Data_inicio_gerente (o</p><p>atributo do relacionamento).</p><p>• Neste caso é melhor colocar o atributo chave de FUNCIONARIO em DEPARTAMENTO</p><p>, e não o contrário, devido à participação total deste último no relacionamento</p><p>GERENCIA.</p><p>Relação unificada</p><p>• Sendo as duas relações que participam do relacionamento S e T :</p><p>– Se o relacionamento considera participação total de ambas, é perfeitamente</p><p>possível agrupar todos os atributos numa única relação – sendo as</p><p>chaves primárias de S e T iguais ou não;</p><p>– Isto é possível pois as respectivas tabelas teriam o mesmo número de</p><p>tuplas todo o tempo.</p><p>57</p><p>Cliente Carregamento</p><p>Data</p><p>1 1</p><p>CodigoClienteCodigoCliente</p><p>Nome Endereco</p><p>ClienteCarregamento(CodigoCliente, Nome, Endereco, Data)</p><p>Referência cruzada</p><p>• Quando ambas as relações que participam do relacionamento possuem</p><p>restrição de participação parcial, agrupam-se as chaves e os atributos de</p><p>tal relacionamento numa terceira entidade.</p><p>• O mesmo ocorre em relacionamentos binários M : N , e será visto em</p><p>detalhes adiante. Abaixo, um exemplo para o caso 1 : 1.</p><p>Pessoa QuartoHotel</p><p>Check-in</p><p>1 1</p><p>NumeroCPF</p><p>Nome Tipo</p><p>Hospeda</p><p>Diarias</p><p>Hospedagem(CPFHospede, NumQuarto, DataChekin, Diarias)</p><p>4.5 Passo 4: mapeamento dos relacionamentos binários</p><p>1 : N</p><p>Considere um relacionamento 1 : N do qual fazem parte duas relações</p><p>(entidades) S – com cardinalidade N e T .</p><p>• Inserir em S (como chave estrangeira) a chave primária da relação T ;</p><p>– Isso é feito pois cada entidade de S (lado N) está relacionado a, no</p><p>máximo, uma entidade de T (lado 1).</p><p>• Os atributos do relacionamento são idem mapeados para a relação S.</p><p>58</p><p>S</p><p>ChaveS</p><p>T</p><p>ChaveTAtributo_rel</p><p>N 1</p><p>S(ChaveS, ChaveT, ..., Atributo_rel)</p><p>Exemplo (ELMASRI; NAVATHE, 2018)</p><p>Nomem Unome</p><p>Nome Endereco</p><p>Sexo</p><p>Salario</p><p>CPF</p><p>CONTROLA</p><p>PROJETO</p><p>DEPARTAMENTO</p><p>M N</p><p>1 1</p><p>1</p><p>N</p><p>N 1</p><p>DEPENDEM_DE</p><p>Nome</p><p>Localizacoes</p><p>Localizacao</p><p>Horas</p><p>Nome Numero</p><p>Numero</p><p>Numero_de_funcionarios</p><p>GERENCIA</p><p>Data_inicio</p><p>N</p><p>1</p><p>N1</p><p>DEPENDENTE</p><p>Sexo Data_nasc ParentescoNome</p><p>Pnome</p><p>Data_nasc</p><p>SUPERVISIONA</p><p>SupervisionadoSupervisor</p><p>TRABALHA_PARA</p><p>TRABALHA_EM</p><p>FUNCIONARIO</p><p>Atualizam-se as relações FUNCIONARIO e PROJETO:</p><p>FUNCIONARIO(Pnome, NomeM, Unome, CPF, Data_nasc, Endereco,</p><p>Salario, Sexo, CPF_supervisor, DNumero)</p><p>PROJETO(PjNome, PNumero, PLocal, Depto)</p><p>DEPARTAMENTO(DNome, DNumero, CPF_gerente, Data_inicio_gerente)</p>