Baixe o app para aproveitar ainda mais
Prévia do material em texto
Banco de Dados Diego Silveira Costa Nascimento Instituto Federal do Rio Grande do Norte diego.nascimento@ifrn.edu.br 20 de março de 2015 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 2 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 3 / 104 Objetivos da Disciplina Capacitar para desenvolvimento de sistemas que utilizam a tecnologia de Banco de Dados (BD); Apresentar os conceitos fundamentais da área; Ensinar as técnicas de modelagem de dados, bem como as técnicas de normalização; Implementar projetos com BD; Utilizar uma linguagem relacional; e Elaborar consultas de complexidade variada. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 4 / 104 Motivações em Estudar Banco de Dados Sistemas de Bancos de Dados (BD) têm sido amplamente utilizados no desenvolvimento de sistemas computacionais corporativos; A disseminação de BDs requer a capacitação de profissionais da área na utilização e no gerenciamento de tais sistemas, diante de um nível de complexidade crescente; e Geralmente, profissionais de BD são muito bem remunerados. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 5 / 104 Banco de Dados Definição São conjuntos de registros dispostos em estrutura regular que possibilita a organização dos dados e produção de informação. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 6 / 104 Características Natureza auto-descritiva de um sistema de BD Um catálogo do SGBD armazena a descrição do banco de dados (meta-dados), permite trabalhar com diferentes BDs. Independência programa-dados Permite mudanças na estrutura de armazenamento e operações dos dados sem ter que mudar os programas que acessam os dados. Abstração de dados Um modelo de dados é usado para esconder detalhes de armazenamento, apresentando ao usuário uma visão conceitual dos dados; Suporta múltiplas visões dos dados Cada usuário pode ter diferentes visões do banco de dados, a qual descreve somente os dados de interesse dos usuário. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 7 / 104 Propriedades Implícitas Um banco de dados representa algum aspecto do mundo real; Um banco de dados é uma coleção lógica e coerente de dados com algum significado inerente; Uma organização de dados ao acaso não pode ser corretamente interpretado como um banco de dados; Um banco de dados é projetado, construído e povoado por dados, atendendo a uma proposta; e Possui um grupo de usuários definido em algumas aplicações preconcebidas de acordo com o interesse desse grupo de usuário. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 8 / 104 Benefícios Controle de Redundância; Compartilhamento dos dados com vários usuários; Restringe o acesso não autorizado dos dados; Provê múltiplas interfaces para diferentes tipos de usuários; Representa relacionamentos complexos dos dados; Manutenção de restrições de integridade; Provê Backup e Recuperação; e Potencial para ditar padrões. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 9 / 104 Evolução Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 10 / 104 Aplicações Tradicionais Sistemas bancários; Controle em hotelaria; Prontuário de pacientes em hospitais; Sites de relacionamento; Controle de estoque de supermercados; Cadastramento de funcionários por uma empresa; e Sistemas acadêmico. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 11 / 104 Avanços Tecnológicos de Armazenamento Figuras; Vídeos; Mensagens sonoras; Mapas; e Dados temporais. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 12 / 104 Sistema Gerenciador de Banco de Dados Definição É uma coleção de programas de propósito geral que facilita os processos de definição, construção, manipulação e compartilhamento de bancos entre vários usuários e aplicações. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 13 / 104 Objetivo de um SGBD Gerenciamento eficiente (mais rápidos que arquivos); com segurança (crashes); de grandes quantidades (gigabyte); de informações persistentes; e compartilhadas (múltipos usuários). Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 14 / 104 Configuração de um Sistema de Bando de Dados Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 15 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 16 / 104 Modelos de Banco de Dados Definição É uma descrição dos tipos de informações que estão armazenadas em um banco de dados. Para construir um modelo de dados usa-se uma linguagem de modelagem de dados; A linguagem de modelagem pode ser textual ou gráfica; Existem linguagens de modelagem para descrever modelos de dados em diferentes níveis de abstração e objetivos; Cada representação de um modelo de dados recebe a denominação de esquema de banco de dados; Para um projeto de banco de dados, normalmente são considerados dois níveis de abstração de modelos de dados: Modelo conceitual; e Modelo lógico. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 17 / 104 Modelo Conceitual Definição É uma descrição do banco de dados de forma independente de implementação em um SGBD. O modelo conceitual registra que dados podem aparecer no banco de dados, mas não registra como estes dados estão armazenados a nível de SGBD. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 18 / 104 Modelo Lógico Definição É uma descrição de um banco de dados no nível de abstração visto pelo usuário do SGBD. Desta forma, o modelo lógico é dependente do tipo particular de SGBD que está sendo usado. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 19 / 104 Entidade Definição Conjunto de objetos da realidade modelada sobre os quais deseja-se manter informações o banco de dados. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 20 / 104 Relacionamento Definição Conjunto de associações entre ocorrência de entidades. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 21 / 104 Cardinalidade de Relacionamento Definição É o número (mínimo, máximo) de ocorrências de entidade associadas a uma ocorrência da entidade em questão através do relacionamento. Classificação de relacionamentos binários: 1:1 (um-para-um); 1:N (um-para-muitos); N:N (muitos-para-muitos); Relacionamento Ternário; e Cardinalidade Mínima. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 22 / 104 Relacionamento Binário 1:1 Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 23 / 104 Relacionamento Binário 1:N Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 24 / 104 Relacionamento Binário N:N Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 25 / 104 Relacionamento Ternário Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 26 / 104 Cardinalidade Mínima Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 27 / 104 Atributo Definição Dado que é associado a cada ocorrêcia de uma entidade ou de um relacionamento. Ilustração Cardinalidade de atributo: Obrigatório; Mono-valorado; ou Multi-valorados.Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 28 / 104 Identificação de Entidade Definição Um identificador é um conjunto de um ou mais atributos (e possivelmente relacionamentos, como visto abaixo) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade. Tipo de identificação: Simples; ou Composto. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 29 / 104 Relacionamento Identificado Definição Há casos em que o identificador de uma entidade é composto não somente por atributos da própria entidade mas também por relacionamentos dos quais a entidade participa. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 30 / 104 Generalização Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 31 / 104 Entidade Associativa Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 32 / 104 Símbolos do Diagrama Entidade Relacionamento Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 33 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 34 / 104 Tabela Definição É um conjunto não ordenado de linhas (tuplas); Cada linha é composta por uma série de campos (valor do atributo); Cada campo é identificado por nome de campo (nome de atributo); e O conjunto de campos das linhas de uma tabela que possuem o mesmo nome formam uma coluna. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 35 / 104 Chaves Definição E a forma na qual se estabelece relações entre linhas de tabelas de um banco de dados relacional. Tipos de chaves: Primária; Estrangeira; e Alternativa; Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 36 / 104 Chave Primária Definição Uma chave primária é uma coluna ou uma combinação de colunas cujos valores distinguem uma linha das demais dentro de uma tabela. Ilustração Tab: Departamento COD DESC D1 Compras D2 Engenharia D3 Vendas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 37 / 104 Chave Estrangeira Definição Uma chave estrangeira é uma coluna ou uma combinação de colunas, cujos valores aparecem necessariamente na chave primária de uma tabela. Ilustração Tab: Empregado COD NOME COD_DEP CAT_FUNC E1 Souza D1 - E2 Santos D2 C5 E3 Silva D2 C5 E5 Soares D1 C2 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 38 / 104 Chave Alternativa Definição Em alguns casos, mais de uma coluna ou combinações de colunas podem servir para distinguir uma linha das demais. Ilustração Tab: Empregado COD NOME COD_DEP CAT_FUNC CPF E1 Souza D1 - 132.121.331-20 E2 Santos D2 C5 891.221.111-11 E3 Silva D2 C5 341.511.773-45 E5 Soares D1 C2 631.692.754-88 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 39 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 40 / 104 Normalização Definição É uma regra que deve ser obedecida por uma tabela para que esta seja considerada “bem projetada”. Tipos de Normalização: Primeira forma normal (1FN); Segunda forma normal (2FN); Terceira forma normal (3FN); e Quarta forma normal (4FN). Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 41 / 104 Tabela Não Normalizada Exemplo Tab: Projeto_Empregado COD_PROJ TIPO DESC COD_EMP NOME CAT SAL INI DURAC LSC001 Desenvolvimento Sistema de Estoque 2146 João A1 4 01/11/91 24 LSC001 Desenvolvimento Sistema de Estoque 3145 Sílvio A2 4 02/10/91 24 LSC001 Desenvolvimento Sistema de Estoque 6126 José B1 9 03/10/92 18 LSC001 Desenvolvimento Sistema de Estoque 1214 Carlos A2 4 04/10/92 18 LSC001 Desenvolvimento Sistema de Estoque 8191 Mário A1 4 01/11/92 12 PAG02 Manutenção Sistema de RH 8191 Mário A1 4 01/05/93 12 PAG02 Manutenção Sistema de RH 4112 João A2 4 04/01/91 24 PAG02 Manutenção Sistema de RH 6126 José B1 9 01/11/92 12 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 42 / 104 Primeira Forma Normal (1FN) Definição Diz-se que uma tabela está na primeira forma normal, quando ela não contém tabelas aninhadas. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 43 / 104 Tabela na 1FN Exemplo Tab: Projeto COD TIPO DESC LSC001 Desenvolvimento Sistema de Estoque PAG02 Manutenção Sistema de RH Tab: Empregado COD_PROJ COD_EMP NOME CAT SAL INI DURAC LSC001 2146 João A1 4 01/11/91 24 LSC001 3145 Sílvio A2 4 02/10/91 24 LSC001 6126 José B1 9 03/10/92 18 LSC001 1214 Carlos A2 4 04/10/92 18 LSC001 8191 Mário A1 4 01/11/92 12 PAG02 8191 Mário A1 4 01/05/93 12 PAG02 4112 João A2 4 04/01/91 24 PAG02 6126 José B1 9 01/11/92 12 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 44 / 104 Dependência Funcional Definição Diz-se que uma coluna C2 depende funcionalmente de uma coluna C1 (ou que a coluna C1 determina a coluna C2) quando, em todas linhas da tabela, para cada valor de C1 que aparece na tabela, aparece o mesmo valor de C2. Ilustração Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 45 / 104 Segunda Forma Normal (2FN) Definição Uma tabela encontra-se na segunda forma normal, quando, além de estar na 1FN, não contém dependências parciais. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 46 / 104 Tabela na 2FN Exemplo Tab: Projeto_Empregado COD_PROJ COD_EMP INI DURAC LSC001 2146 01/11/91 24 LSC001 3145 02/10/91 24 LSC001 6126 03/10/92 18 LSC001 1214 04/10/92 18 LSC001 8191 01/11/92 12 PAG02 8191 01/05/93 12 PAG02 4112 04/01/91 24 PAG02 6126 01/11/92 12 Tab: Empregado COD_EMP NOME CAT SAL 2146 João A1 4 3145 Sílvio A2 4 6126 José B1 9 1214 Carlos A2 4 8191 Mário A1 4 4112 João A2 4 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 47 / 104 Terceira Forma Normal (3FN) Definição Uma tabela encontra-se na terceira forma normal, quando, além de estar na 2FN, não contém dependências transitivas. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 48 / 104 Tabela na 3FN Exemplo Tab: Empregado COD_EMP NOME CAT 2146 João A1 3145 Sílvio A2 6126 José B1 1214 Carlos A2 8191 Mário A1 4112 João A2 Tab: Categoria CAT SAL A1 4 A2 4 B1 9 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 49 / 104 Quarta Forma Normal (4FN) Definição Uma tabela encontra-se na quarta forma normal, quando, além de estar na 3FN, não contém dependências multi-valoradas. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 50 / 104 Incluindo uma Nova Entidade Exemplo Tab: Equipamento COD DESC EQ1 Projetor EQ2 Notebook EQ3 Roteador Tab: Projeto_Empregado_Equipamento COD_PROJ COD_EMP COD_EQUI PAG02 8191 EQ1 PAG02 4112 EQ1 PAG02 6126 EQ1 PAG02 8191 EQ2 PAG02 4112 EQ2 PAG02 6126 EQ2 PAG02 8191 EQ3 PAG02 4112 EQ3 PAG02 6126 EQ3 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 51 / 104 Tabela na 4FN Exemplo Tab: Projeto_Equipamento COD_PROJ COD_EQUI PAG02 E1 PAG02 E2 PAG02 E3 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 52 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 53 / 104 Linguagem SQL Derivada de Structured Query Language; Adotada como padrão para BDs relacionais; Implementada no projeto de pesquisa do System R da IBM em meados dos anos 70;Padronizada em 1986 e melhorada em 1989; SQL-2 ou SQL-92: Padrão hoje em vigor; e SQL-3: Em fase de desenvolvimento. Vai estender o padrão atual com conceitos de orientação a objeto e outros novos conceitos de BDs. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 54 / 104 Comandos SQL Comandos de definição de dados; e Comandos de manipulação dos dados. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 55 / 104 Comandos de Definição de Dados Criação e exclusão de banco de dados; Criação, alteração e exclusão de tabelas; Especificação de restrições; Criação de visão; Criação de procedimentos armazenados; e Criação de gatilhos. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 56 / 104 Criando Banco de Dados Estrutura create database <nome do database > Exemplo create database sistema_academico Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 57 / 104 Excluindo Banco de Dados Estrutura drop database <nome do banco > Exemplo drop database sistema_academico Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 58 / 104 Tabelas Exemplo Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 59 / 104 Criando Tabela Estrutura create table <nome da tabela >( <nome do atributo 1> <tipo >, <nome do atributo 2> <tipo >) Exemplo create table tb_aluno( matricula int , nome varchar (50), sexo char) create table tb_disciplina( identificador int , descricao varchar (50), creditos int) create table tb_disciplina_cursada( matricula_aluno int , identificador_disciplina int , semestre int , ano int , nota float) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 60 / 104 Alterando Tabela Estrutura alter table <nome da tabela > add <nome do atributo > <tipo > Exemplo alter table tb_aluno add cpf varchar (11) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 61 / 104 Excluindo Tabela Estrutura drop table <nome da tabela > Exemplo drop table tb_aluno Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 62 / 104 Comandos de Manipulação de Dados Inserir registros em tabelas; Selecionando registros; Atualizar registros em tabelas; e Excluir registros em tabelas. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 63 / 104 Inserindo Valores Estrutura insert into <nome da tabela > (nome do atributo 1, nome do atributo 2) values (’valor 1’,’valor 2’) Exemplo insert into tb_aluno (matricula , nome , sexo , cpf) values (’1’,’João da Silva ’,’M’,’12345678 ’) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 64 / 104 Selecionando Registros Estrutura select <nome do atributo 1>, <nome do atributo 2> from <nome da tabela > Exemplo select matricula , nome from tb_aluno Caso necessite selecionar todos os atributos de uma única vez, deve-se apenas utilizar o síbolo de asterisco (∗) em substituição do(s) nome(s) da(s) coluna(s). Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 65 / 104 Cláusula WHERE Estrutura select <nome do atributo 1>, <nome do atributo 2> from <nome da tabela > where <condição> Exemplo select matricula , nome from tb_aluno where matricula = ’1’ Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 66 / 104 Conectores Relacionais igual: =; maior: >; menor: <; maior igual: >=; menor igual: <=; diferente: <>; entre: between; e parte: like (Permitido o uso de máscara com % entre áspas simples). Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 67 / 104 Conectores Lógicos e: and; ou: or; é nulo: is null; e não é nulo: is not null. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 68 / 104 Atualizando Valores Estrutura update <nome da tabela > set <nome do atributo > = <valor > where <condições > Exemplo update tb_aluno set nome = ’João da Silva Filho’ where matricula = ’1’ Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 69 / 104 Excluindo Valores Estrutura delete from <nome da tabela > where <condições > Exemplo delete from tb_aluno where matricula = ’1’ Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 70 / 104 Especificando Restrições de Integridade Declarativas: Domínio ou tipo de dados; Vazio; Padrão; Checagem; Unicidade; Chave primária; e Referencial. Procedimental: Procedimentos armazenados ou funções; e Gatilhos (Banco de dados ativos). Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 71 / 104 Restrição de Vazio Estrutura alter table <nome da tabela > alter <nome do atributo > set not null Exemplo alter table tb_aluno alter nome set not null Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 72 / 104 Restrição de Padrão Estrutura alter table <nome da tabela > alter <nome do atributo > set default <valor > Exemplo alter table tb_aluno alter sexo set default ’M’ Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 73 / 104 Restrição de Checagem Estrutura alter table <nome da tabela > add constraint <nome da restrição> check (<restrição>) Exemplo alter table tb_disciplina_cursada add constraint ck_tb_disciplina_cursada_nota check (nota >= 0 and nota <= 10) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 74 / 104 Restrição de Unicidade Estrutura alter table <nome da tabela > add constraint <nome da restrição> unique (<nome do atributo >) Exemplo alter table tb_aluno add constraint u_tb_aluno_cpf unique (cpf) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 75 / 104 Restrição de Chave Estrutura alter table <nome da tabela > add constraint <nome da restrição> primary key (nome do atributo) Exemplos alter table tb_aluno add constraint pk_tb_aluno_matricula primary key (matricula) alter table tb_disciplina add constraint pk_tb_disciplina_identificador primary key (identificador) alter table tb_disciplina_cursada add constraint pk_tb_disciplina_cursada_matricula_disciplina primary key (matricula_aluno , identificador_disciplina , semestre , ano) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 76 / 104 Restrição de Referencial Estrutura alter table <nome da tabela > add constraint <nome da restrição> foreign key (<nome do atributo >) references <nome da tabela -referência >(<nome do atributo -referência >) Exemplos alter table tb_disciplina_cursada add constraint fk_tb_disciplina_cursada_matricula_aluno foreign key (matricula_aluno) references tb_aluno (matricula) alter table tb_disciplina_cursada add constraint fk_tb_disciplina_cursada_idenfificador_disciplina foreign key (identificador_disciplina) references tb_disciplina (identificador) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 77 / 104 Criando Visão Estrutura create view <nome da visão> as <consulta sql > Exemplo create view vw_alunos as select matricula , nome from secretaria.tb_aluno Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 78 / 104 Criando Função Estrutura create function <nome da função>(<parâmetros >) returns <tipo de retorno > as $$ <definição do comando >; $$ language <nome da linguagem > Exemplo create function fn_inserir_aluno ( matricula INT , nome VARCHAR (50), sexo CHAR , cpf VARCHAR (11) ) returns void as $$ insert into tb_aluno (matricula , nome , sexo , cpf) values ($1 ,$2,$3,$4); $$ language SQL Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 79 / 104 Criando Função Exemplo create function fn_descricao_sexo(sexo CHAR) returns varchar (10) as $$ declare pSexo alias for $1; begin if (pSexo = ’M’) then return ’MASCULINO ’; else return ’FEMININO ’; end if; end; $$ language PLPGSQL Diego S.C. Nascimento (IFRN) Banco de Dados Apresentação 80 / 104 Criando Gatilhos Estrutura create trigger <nome do gatilho > [BEFORE ,AFTER] [INSERT ,UPDATE ,DELETE] on <nome da tabela > for each row execute procedure <nome da função>() Exemplos create table tb_log_alteracao_nota( codigo serial , data date , matricula_aluno int , identificador_disciplina int , nota_anterior float , nota_atual float , usuario varchar (20) ) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 81 / 104 Criando Gatilhos Exemplos create function fn_log_alterar_nota () returns trigger as $$ begin insert into tb_log_alteracao_nota (data , matricula_aluno , identificador_disciplina , nota_anterior , nota_atual ,usuario) values (now(),OLD.matricula_aluno , OLD.identificador_disciplina , OLD.nota , NEW.nota ,user); return null; end; $$ language PLPGSQL create trigger tg_alterar_nota after update on tb_disciplina_cursada for each row execute procedure fn_log_alterar_nota () Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 82 / 104 Ementa do Curso 1 Introdução 2 Abordagem Entidade-relacionamento 3 Abordagem Relacional 4 Normalização 5 Liguagem Estruturada de Consulta 6 Consultas Avançadas Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 83 / 104 Exemplo de um Banco de Dados Exemplos Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 84 / 104 Operação de Renomeação AS Definição A linguagem SQL permite a renomeação de relações e atributos recorrendo à cláusula “as”. Exemplo select e.pnome as nome from empregado as e Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 85 / 104 Cláusula GROUP BY Estrutura select <nome do atributo 1>, <nome do atributo 2> from <nome da tabela > where <condições > group by <nome do atributo > Funções de grupo: count(); min(); max(); sum(); ou avg(). Exemplo select e.sexo , count (*) from empregado as e group by e.sexo Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 86 / 104 Cláusula HAVING Estrutura select <nome do atributo 1>, <nome do atributo 2> from <nome da tabela > where <condições > group by <nome do atributo > having <condições> Exemplo select e.dno , avg(e.salario) from empregado as e group by e.dno having avg(e.salario) < 55000 Importante Predicados da cláusula WHERE são aplicados depois da formação dos grupos. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 87 / 104 Cláusula ORDER BY Estrutura select <nome do atributo 1>, <nome do atributo 2> from <nome da tabela > where <condições > order by <nome do atributo > <tipo de ordenação> Tipo de ordenação: Ascendente: asc; ou Descendente: desc. Exemplo select * from empregado as e order by e.pnome desc Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 88 / 104 Operações de Conjunto União dos resultados de uma ou mais queries; Interseção entre os resultados de duas queries; e Subtração entre os resultados de duas queries. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 89 / 104 Operações de Conjunto para União Estrutura <Primeira consulta > union <Segunda consulta > Exemplo select e.pnome , e.sexo from empregado as e union select d.nome_dependente , d.sexo from dependente as d Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 90 / 104 Operações de Conjunto para Interseção Estrutura <Primeira consulta > intersect <Segunda consulta > Exemplo select e.pnome , e.sexo from empregado as e intersect select e.pnome , e.sexo from empregado as e where e.superssn is not null Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 91 / 104 Operações de Conjunto para Subtração Estrutura <Primeira consulta > except <Segunda consulta > Exemplo select e.pnome , e.sexo from empregado as e except select e.pnome , e.sexo from empregado as e where e.superssn is not null Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 92 / 104 Junções entre Tabelas Junção cruzada; Junção interna; e Junção externa: Esquerda; Direita; ou Completa. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 93 / 104 Junção Cruzada Estrutura select <atributos > from <primeira tabela > cross join <segunda tabela > Exemplo select e1.pnome , e2.pnome from empregado as e1 cross join empregado as e2 Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 94 / 104 Junção Interna Estrutura select <atributos > from <primeira tabela > inner join <segunda tabela > on (<condição>) Exemplo select e1.pnome , e2.pnome from empregado as e1 inner join empregado as e2 on (e1.ssn = e2.superssn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 95 / 104 Junção Externa a Esquerda Estrutura select <atributos > from <primeira tabela > left outer join <segunda tabela > on (<condição>) Exemplo select e1.pnome , e2.pnome from empregado as e1 left outer join empregado as e2 on (e1.ssn = e2.superssn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 96 / 104 Junção Externa a Direita Estrutura select <atributos > from <primeira tabela > right outer join <segunda tabela > on (<condição>) Exemplo select e1.pnome , e2.pnome from empregado as e1 right outer join empregado as e2 on (e1.ssn = e2.superssn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 97 / 104 Junção Externa Completa Estrutura select <atributos > from <primeira tabela > inner join <segunda tabela > on (<condição>) Exemplo select e1.pnome , e2.pnome from empregado as e1 full outer join empregado as e2 on (e1.ssn = e2.superssn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 98 / 104 Subconsultas Na cláusula SELECT; Na cláusula FROM; ou Na cláusula WHERE. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 99 / 104 Subconsulta na Cláusula Select Estrutura select <atributos >, (<subconsulta >) from <primeira tabela > Exemplo select e.pnome , (select count (*) from dependente as d where e.ssn = d.essn) as qt_dependente from empregado as e Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 100 / 104 Subconsulta na Cláusula From Estrutura select <atributos > from (<subconsulta >) Exemplo select e.pnome , d.qt_dependentes from empregado as e inner join (select d.essn , count (*) as qt_dependentes from dependente as d group by d.essn ) as d on (e.ssn = d.essn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 101 / 104 Expressões em Subconsulta in; not in; exists; e not exists. Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 102 / 104 Subconsulta na Cláusula Where Estrutura select <atributos > from <tabela > where (subconsulta) Exemplo select e.ssn , e.pnome from empregado as e where e.ssn in (select d.essn from dependente as d group by d.essn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 103 / 104 Subconsulta na Cláusula Where Exemplo select e.ssn , e.pnome from empregado as e where exists (select d.essn from dependente as d where d.essn = e.ssn group by d.essn) Diego S. C. Nascimento (IFRN) Banco de Dados Apresentação 104 / 104 Introdução Abordagem Entidade-relacionamento Abordagem Relacional Normalização Liguagem Estruturada de Consulta Consultas Avançadas
Compartilhar