Prévia do material em texto
MCG241 – Sistemas de Informação Profa. Janaína Gomide 1 Janaína Gomide Banco de Dados 2 Janaína Gomide Definição ? 3 Janaína Gomide 4 Janaína Gomide 1990 Imagine... • Uma pessoa por vez acessando • Busca sequencial • Quem pode atualizar? • Pode tirar 2 fichas ao mesmo tempo? • E se uma pessoa está com a ficha e uma outra não encontra a ficha, então cria uma nova; 2 fichas para mesmo cliente? • E se precisar de fichários diferentes para dados diferentes? 5 Janaína Gomide Conceitos básicos • Quais dados armazenar? • Como modelar os dados? • Como consultar? • Atualizar? • Quem pode acessar? 6 Janaína Gomide Conceitos básicos • Banco de dados fornece acesso seguro e eficiente a grandes quantidade de dados • Resolve problemas: – armazenamento – consulta eficiente – acesso e atualização com segurança por múltiplos usuários 7 Janaína Gomide Definições • [Chu, 1985] Um banco de dados é um conjunto de arquivos relacionados entre si. • [Date,1975] Um banco de dados é uma coleção de dados operacionais usados pelas aplicações de uma determinada organização. 8 Janaína Gomide Definições • [Elmasri & Navathe, 2000] Um banco de dados é uma coleção de dados relacionados. Entretanto, um BD possui as seguintes propriedades implícitas: 1. Representa aspectos do mundo real 2. É uma coleção de dados logicamente coerentes com algum significado inerente 3. É projetado, construído e instanciado (“povoado”) para uma aplicação específica. 9 Janaína Gomide Banco de Dados em diversas áreas Exemplos: • Aplicações bancárias: contas bancárias, agências • Reservas de passagens: passagem, passageiros, pilotos • Aplicações hospitalares: pacientes, médicos, remédios • Universidades: estudantes, turmas, professores 10 Janaína Gomide Vantagens de usar um Banco de Dados • Controle de redundância dos dados • Controle de acesso (segurança) • Armazenamento persistente dos dados • Existência de múltiplas interfaces para os usuários • Representação de restrições de integridade • Acesso multi-usuário (controle de concorrência e recuperação de falhas) 11 Janaína Gomide Exemplo de Banco de Dados • Mini-mundo: parte de uma universidade • Alguns integrantes – Alunos – Disciplinas – Departamentos • Alguns relacionamentos – Disciplinas são oferecidas por Departamentos – Alunos estão matriculados em Disciplinas 12 Janaína Gomide Exemplo de Banco de Dados 13 Janaína Gomide Exemplo de Banco de Dados 14 Janaína Gomide Características da Abordagem de BD 15 Janaína Gomide • Auto-descrição dos dados • Isolamento entre programas e dados: abstração de dados • Suporte a múltiplas visões dos dados • Compartilhamento de dados e processamento de transações concorrentes Mais alguns exemplos... 16 Janaína Gomide Mais alguns exemplos... 17 Janaína Gomide Mais alguns exemplos... 18 Janaína Gomide Mais alguns exemplos... 19 Janaína Gomide Banco de Dados em diversas áreas • Economia • Engenharia • Medicina • Educação Qualquer aplicação precisa de dados! E os dados precisam ser armazenados, gerenciados, consultados, recuperados! 20 Janaína Gomide Sistema de Gerência de Banco de Dados • Um Sistema de Gerência de Banco de Dados (SGBD) é um conjunto de programas que permite criar e manter (manipular) um banco de dados • BD + SGBD = sistema de banco de dados 21 Janaína Gomide Sistema de Banco de Dados 22 Janaína Gomide Sistema de Banco de Dados 23 Janaína Gomide Vantagens da Utilização de um SGBD • Controle de redundância dos dados – Compartilhamento informações replicadas • Manutenção de restrições de integridade • Controle de acesso (segurança) • Armazenamento persistente dos dados • Existência de múltiplas interfaces para os usuários • Representação de relacionamentos complexos entre os dados • Recuperação de falhas 24 Janaína Gomide Implicação da Abordagem de BD • Adoção/imposição de padrões • Redução do tempo de desenvolvimento das aplicações • Flexibilidade • Atualidade da informação disponível • Economia de escala 25 Janaína Gomide Quando NÃO utilizar um SGBD • Aplicações simples e bem definidas onde não se espera mudanças • Aplicações onde não é necessário acesso multi- usuário • Motivos: – Investimento inicial alto – Generalidade na definição e manipulação dos dados – Custo adicional para prover outras facilidade funcionais 26 Janaína Gomide Para lembrar... • [Elmasri & Navathe, 2000] Um banco de dados é uma coleção de dados relacionados. Entretanto, um BD possui as seguintes propriedades implícitas: 1. Representa aspectos do mundo real 2. É uma coleção de dados logicamente coerentes com algum significado inerente 3. É projetado, construído e instanciado (“povoado”) para uma aplicação específica. 27 Janaína Gomide • E ai? • Por onde começar? 28 Janaína Gomide Modelos de Dados, Esquema e Instância 29 Janaína Gomide • Modelo de dados: conjunto de conceitos Descrevem a estrutura de um BD Abstração de dados Estrutura = tipos de dados + relacionamentos + restrições • Esquema: Definição (textual ou gráfica) da estrutura de um BD de acordo com um determinado modelo de dados • Instância: Conjunto de dados armazenados em um BD em um determinado instante de tempo Esquema 30 Janaína Gomide Instância 31 Janaína Gomide Modelos de Dados, Esquema e Instância • Esquema do BD – Armazenamento no catálogo – Mudanças muito menos frequentes • Estado do BD – Dados do banco em qualquer ponto do tempo – Inicialmente vazio – Muda frequentemente – Validade parcialmente garantida pelo SGBD 32 Janaína Gomide Arquitetura de um Sistema de BD • Características do enfoque de BD – Isolamento de programas e dados – Suporte de visões múltiplas de usuários – Catálogo para armazenar a descrição (esquema) do BD • Arquitetura de três níveis – Mantém independência de dados e programas – Suporta múltiplas visões 33 Janaína Gomide Arquitetura de um Sistema de BD 1. Esquema interno – Descreve armazenamento físico 2. Esquema conceitual – D e s c r e v e e n t i d a d e s , t i p o s d e d a d o s , relacionamentos, operações e restrições – Esconde detalhes de armazenamento 3. Esquemas externos (visões) – Descreve porções do banco para diferentes comunidades 34 Janaína Gomide Arquitetura de um Sistema de BD 35 Janaína Gomide Exemplo 1. Esquema interno – Relações guardadas como arquivos desordenados – Índices na primeira coluna de estudantes 2. Esquema conceitual – Estudantes (eid: string, nome: string, login: string, idade: integer, gpa: real) – Cursos (cid: string, cnome: string, creditos: integer) – Matricula (eid: string, cid: string, nota: string) 3. Esquemas externos (visões) – Info_Curso (cid: string, horário: string) 36 Janaína Gomide Arquitetura de um Sistema de BD 37 Janaína Gomide Altera um nível, outro também altera? Independência de dados! 38 Janaína Gomide Independência de Dados Capacidade de se alterar o esquema em um determinado nível SEM alterar o esquema (ou esquemas) do nível imediatamente mais alto 39 Janaína Gomide Independência de Dados VANTAGEM: • Imunidade dos programas em relação a mudanças na estrutura do banco de dados • Um dos maiores benefícios de usar SGBD! 40 Janaína Gomide Independência de Dados • Independência de dados lógica: – Capacidade de alterar o esquema conceitual sem alterar oesquema externo ou aplicações • Independência de dados física: – Capacidade de mudar o esquema interno sem mudar o esquema conceitual ( ou externo) • Ex. Reorganização de arquivos para melhorar desempenho 41 Janaína Gomide Arquitetura de um Sistema de BD 42 Janaína Gomide Exercício 43 Janaína Gomide • Como seria o esquema de um Banco de Dados para da americanas.com? – Quais atributos para os clientes? – Quais atributos para as produtos? – Quais atributos para as categorias? – Quais relações entre essas entidades? – Como clientes e produtos se relacionam? – Como seriam as tabelas? Faça um exemplo... Revisão... 44 Janaína Gomide • [Elmasri & Navathe, 2000] Um banco de dados é uma coleção de dados relacionados. Entretanto, um BD possui as seguintes propriedades implícitas: 1. Representa aspectos do mundo real 2. É uma coleção de dados logicamente coerentes com algum significado inerente 3. É projetado, construído e instanciado (“povoado”) para uma aplicação específica. Revisão... 45 Janaína Gomide • Um Sistema de Gerência de Banco de Dados (SGBD) é um conjunto de programas que permite criar e manter (manipular) um banco de dados • Esquema do BD – Armazenamento no catálogo – Mudanças muito menos frequentes • Estado do BD – Dados do banco em qualquer ponto do tempo – Inicialmente vazio – Muda frequentemente – Validade parcialmente garantida pelo SGBD Revisão... 46 Janaína Gomide • Arquitetura de Banco de Dados: • Características do enfoque de BD – Isolamento de programas e dados – Suporte de visões múltiplas de usuários – Catálogo para armazenar a descrição (esquema) do BD • Arquitetura de três níveis – Mantém independência de dados e programas – Suporta múltiplas visões Revisão... Independência de Dados • Capacidade de se alterar o esquema em um determinado nível SEM alterar o esquema (ou esquemas) do nível imediatamente mais alto VANTAGEM: • Imunidade dos programas em relação a mudanças na estrutura do banco de dados • Um dos maiores benefícios de usar SGBD! 47 Janaína Gomide Modelo Entidade Relacionamento 48 Janaína Gomide Conceitos do modelo relacional O modelo relacional representa o banco de dados como uma coleção de relações. 49 Janaína Gomide Conceitos do modelo relacional • Cada relação é semelhante a uma tabela de valores. • Cada linha na tabela representa uma coleção de valores de dados relacionados. 50 Janaína Gomide Conceitos do modelo relacional • Uma linha na tabela é chamada de tupla. • Um cabeçalho da coluna é chamado de atributo. • A tabela é chamada de relação. 51 Janaína Gomide Conceitos do modelo relacional 52 Janaína Gomide Restrições 53 Janaína Gomide • No banco de dados relacional haverá muitas relações, e as tuplas nessas relações são relacionadas de várias maneiras. • Existem muitas restrições sobre os valores reais em um estado do banco de dados. • Essas restrições são derivadas das regras do contexto que o banco de dados representa. Restrições 54 Janaína Gomide • Restrições de domínio • Restrições de chave • Restrições de identidade Restrições de domínio 55 Janaína Gomide • As restrições de domínio especificam que valor cada atributo pode assumir. • Os tipos de dados associados aos domínios incluem: – Tipos de dados numéricos para inteiros e números reais – Caracteres, booleanos, cadeia de caracteres de tamanho fixo ou variável – Data, hora, moeda e outros tipos especiais • Outros domínios podem ser descritos por um subintervalo dos valores possíveis. 56 Janaína Gomide • O tipo de dado que descreve os tipos de valores que podem aparecer em cada coluna é representado por um domínio de valores possíveis. Restrições de domínio Restrições de chave 57 Janaína Gomide • No modelo relacional, uma relação é definida por um conjunto de tuplas. • Todas as tuplas em uma relação precisam ser distintas, ou seja, duas tuplas não podem ter a mesma combinação de valores para todos os seus atributos. • Existem subconjuntos de atributos de um esquema para os quais duas tuplas não deverão ter a mesma combinação de valores. Restrições de chave 58 Janaína Gomide • Uma chave satisfaz duas propriedades: 1. Duas tuplas distintas em qualquer estado da relação não podem ter valores idênticos para os atributos na chave. 2. Ela é uma superchave mínima – ou seja, uma superchave da qual não podemos remover nenhum atributo e ainda mantemos uma restrição de exclusividade • O valor de um atributo de chave pode ser usado para identificar exclusivamente cada tupla na relação Restrições de chave 59 Janaína Gomide • No exemplo abaixo, qual seria a chave da relação aluno? Restrições de chave 60 Janaína Gomide • Um esquema de uma relação pode ter mais de uma chave. • Cada chave é chamada de chave candidata. • Uma das chaves candidatas é a chave primária da relação. • A relação CARRO, com duas chaves candidatas: Placa e Numero_chassi • Apenas a chave primária é sublinhada. Entidades • Entidades – Objetos do mundo real que são de interesse para alguma aplicação Empregado Departamento 61 Janaína Gomide Atributos • Atributos – Propriedades para descrever uma entidade 62 Janaína Gomide Tipos de atributos • Simples ou compostos – Ex. Endereço • Monovalorados ou multivalorados – Ex. Profissão • Armazenados ou derivados – Data de nascimento -> Idade – Empregados trabalhando no departamento -> Número de empregados • Valores Null – Não aplicável: Número do apartamento – Desconhecido: Telefone de casa 63 Janaína Gomide Tipos de atributos 64 Janaína Gomide Exercícios 65 Janaína Gomide Analise e desenhe um modelo ER para cada um dos casos abaixo: 1. Um empregado tem CPF e Nome. 2. Um departamento tem um Código e um Nome 3. Um curso tem um código, um nome e uma duração 4. Um professor tem um código e um nome Exercícios 66 Janaína Gomide Analise e desenhe um modelo ER para cada um dos casos abaixo: 1. Um empregado tem CPF e Nome. 2. Um departamento tem um Código e um Nome Exercícios 67 Janaína Gomide Analise e desenhe um modelo ER para cada um dos casos abaixo: 3. Um curso tem um código, um nome e uma duração 4. Um professor tem um código e um nome Relacionamentos • Associações entre duas ou mais entidades distintas com um significado • Exemplo: – Empregado John Smi th t raba lha para Departamento pesquisa 68 Janaína Gomide Relacionamento 1:1 69 Janaína Gomide Relacionamento 1:N Relacionamento um-para-muitos 70 Janaína Gomide Relacionamento N:M Relacionamento muitos-para-muitos 71 Janaína Gomide Relacionamentos N:M 72 Janaína Gomide Exercícios Analise e desenhe um modelo ER para cada um dos casos abaixo: 1. Um membro da equipe gerencia vários clientes. Um cliente nunca será contactado por mais de um membro da equipe 2. Uma pessoa pode consultar diversos livros. Estes podem ser lidos por vários alunos em momentos diferentes 3. Cada aluno participa de diversas palestras. Cada palestra é assistida por diversos alunos. Um professor dá diversas palestras. Cada palestra é dada por um professor 4. Cada cliente pode abrir várias contas correntes. Cada conta é aberta por um cliente. Cada banco gerencia diversas contas. Cada conta é gerenciada por um banco. 73 Janaína Gomide Exercícios 1. Um membro da equipe gerencia vários clientes. Um cliente nunca será contactado por mais de um membro da equipe 74 Janaína Gomide Exercícios 2. Uma pessoa pode consultar diversos livros. Estes podem serlidos por vários alunos em momentos diferentes 75 Janaína Gomide Exercícios 3. Cada aluno participa de diversas palestras. Cada palestra é assistida por diversos alunos. Um professor dá diversas palestras. Cada palestra é dada por um professor 76 Janaína Gomide Exercícios 4. Cada cliente pode abrir várias contas correntes. Cada conta é aberta por um cliente. Cada banco gerencia diversas contas. Cada conta é gerenciada por um banco. 77 Janaína Gomide • Além das restrições de domínio e chave, dois outros tipos de restrições são considerados parte do modelo relacional: – integridade de entidade – integridade relacional 78 Janaína Gomide Restrições • A restrição de integridade de entidade afirma que nenhum valor de chave primária pode ser NULL. • Isso porque o valor de chave primária é usado para identificar tuplas individuais em uma relação. • As restrições de chave e de integridade são especificadas sobre relações individuais. 79 Janaína Gomide Restrições de integridade de entidade • A restrição de integridade referencial é especificada entre duas relações e usada para manter a consistência entre tuplas nas duas relações. • A restrição de integridade referencial afirma que uma tupla em uma relação que referencia outra relação precisa se referir a uma tupla existente nessa relação. 80 Janaína Gomide Restrições de integridade referencial Por exemplo, um funcionário trabalha em um departamento. Na relação Funcionário há o número de departamento para o qual trabalha. Logo, o valor para departamento na tupla de Funcionário precisa combinar com o valor de departamento em alguma tupla na relação Departamento. 81 Janaína Gomide Restrições de integridade referencial restrição de integridade referencial é definida por meio da chave estrangeira (foreign key) 82 Janaína Gomide Restrições de integridade referencial 83 Janaína Gomide Restrições de integridade referencial • Tipos de entidade que não têm chave própria • As instâncias são identificadas através do relacionamento com entidades de outro tipo, chamado dono ou identificador, juntamente com os valores de alguns atributos (chave parcial) 84 Janaína Gomide Entidade Fraca 85 Janaína Gomide Entidade Fraca Exemplo • Banco de dados de uma empresa • Essa empresa tem departamentos, projetos e empregados • Tem algumas restrições e relações que devem ser consideradas... 86 Janaína Gomide Exemplo • Os departamentos: – Tem um nome único – Tem um número único – Um empregado gerencia o departamento – Deve-se registrar a data de quando o empregado começou a gerenciar o departamento – Um departamento pode ter várias localizações 87 Janaína Gomide Exemplo • Os projetos: – Tem um nome e número únicos – Uma única localização • Um departamento controla um número de projetos 88 Janaína Gomide Exemplo • Os empregados: – Nome, identidade, endereço, salário, sexo, data de nascimento • Um empregado é assinalado a um departamento • Pode trabalhar em diversos projetos • O número de horas por semana que o empregado trabalha em cada projeto e o supervisor direto de cada empregado devem ser armazenados 89 Janaína Gomide Exemplo • Registro para cada empregado – Número de dependentes – Para cada dependente: • Primeiro nome, sexo, data de nascimento e relacionamento com o empregado. 90 Janaína Gomide 91 Janaína Gomide 92 Janaína Gomide 93 Janaína Gomide 94 Janaína Gomide 95 Janaína Gomide 96 Janaína Gomide 97 Janaína Gomide Revisando... • O modelo relacional representa um banco de dados como um conjunto de relações • Informalmente: Uma relação = uma tabela de valores – Cada linha representa uma coleção de dados relacionados – Cada linha de uma tabela representa um “fato” que tipicamente corresponde a uma entidade ou relacionamento do mundo real 98 Janaína Gomide Revisando... • Uma linha na tabela é chamada de tupla. • Um cabeçalho da coluna é chamado de atributo. • A tabela é chamada de relação. 99 Janaína Gomide Revisando... ü Entidade ü Atributo – Tipos: Simples ou composto; monovalorados ou multivalorados; armazenados ou derivados; valores NULL ü Relacionamento – 1:1 ou 1:N ou N:M ü Entidade fraca ü Restrições: – Domínio – Chave – Integridade de entidade – Integridade relacional ü Modelo ER 100 Janaína Gomide Exercícios – Identificar: • As entidades • Os atributos de cada entidade • Os identificadores de cada entidade • Os principais relacionamentos • As cardinalidades • Desenhar o diagrama entidade-relacionamento – Temas: ... 101 Janaína Gomide Para desenhar... https://www.draw.io 102 Janaína Gomide Mapeamento ER -> Relacional 103 Janaína Gomide 104 Janaína Gomide ? Mapeamento ER Relacional 105 Janaína Gomide Algoritmo de mapeamento • O algoritmo de mapeamento faz a conversão dos construtores básicos (entidades, atributos e relacionamentos) em relações. 1. Entidade regulares 2. Atributos multivalorados 3. Entidades fracas 4. Relacionamentos a) Relacionamento binários 1:1 b) Relacionamento binários 1:N c) Relacionamento binários N:M 106 Janaína Gomide Mapeamento ER Relacional Entidades Regulares • Entidade regular E Relação R • Atributo em E Coluna em R • Atributo identificador em E Chave primária em R 107 Janaína Gomide Mapeamento ER Relacional Atributos Multivalorados • Não existem no modelo relacional: viram tabelas. 108 Janaína Gomide Mapeamento ER Relacional Entidades Fracas • Não existem no modelo relacional: viram tabelas. • As instâncias são identificadas através do relacionamento com entidades de outro tipo (dono ou identificador) juntamente com os valores de alguns atributos (chave parcial) 109 Janaína Gomide Mapeamento ER Relacional Relacionamentos • Relacionamentos binários 1:1 – Uma relação tem chave estrangeira para outra Empregado (CPF, Nome) Departamento (CodDept, NomeDept, CPF_gerente) 110 Janaína Gomide Mapeamento ER Relacional Relacionamentos • Relacionamentos binários 1:N – Um relação tem chave estrangeira OU – Cria tabela para relacionamento Curso (CodCurso, Nome, Duração, Id) Professor (Id, Nome) OU Curso (CodCurso, Nome, Duração) Professor (Id, Nome) Leciona(IdProfessor, CodCurso) 111 Janaína Gomide Mapeamento ER Relacional Relacionamentos • Relacionamentos binários N:M – Criar uma tabela para o relacionamento. – A chave primária da nova tabela é composta pela chave primária da primeira tabela mais a chave da segunda tabela Curso (CodCurso, Nome) Aluno (RA, Nome) Exame(CodCurso, RA, Nota) 112 Janaína Gomide Quais são as relações? 113 Janaína Gomide 114 Janaína Gomide 115 Janaína Gomide Exemplo: Universidade • Aluno está inscrito em curso • Um curso tem várias disciplinas • Uma disciplina pode ter vários requisitos • Um departamento é responsável por várias disciplinas 116 Janaína Gomide Exemplo: Universidade Identificação de entidades • Aluno está inscrito em curso • Um curso tem várias disciplinas • Uma disciplina pode ter vários requisitos • Um departamento é responsável por várias disciplinas 117 Janaína Gomide Exemplo: Universidade Identificação de relacionamentos • Aluno está inscrito em curso • Um curso tem várias disciplinas • Uma disciplina pode ter vários requisitos • Um departamento é responsável por várias disciplinas118 Janaína Gomide Exemplo: Universidade 1 N N M N M 1 N 119 Janaína Gomide Exemplo: Universidade 120 Janaína Gomide Exemplo: Universidade 121 Janaína Gomide Exemplo: Universidade 122 Janaína Gomide Exercício: Locadora 123 Janaína Gomide • Uma locadora de vídeos possui cerca de 2.000 DVDs, cujo empréstimo deve ser controlado. Cada DVD possui um número. Para cada filme, é necessário saber seu título e sua categoria (comédia, drama, aventura, ...). • Cada filme recebe um identificador próprio. Para cada DVD é controlado que filme ele contém. Para cada filme há pelo menos um DVD. Alguns poucos filmes necessitam de mais de um DVD. • Os clientes podem desejar encontrar os filmes estrelados pelo seu ator predileto. Por isso, é necessário manter a informação dos atores que atuam em cada filme. Os clientes, às vezes, desejam receber referências de determinado ator, tais como o nome real, a data de nascimento, etc. • A locadora possui clientes cadastrados. Só clientes cadastrados podem alugar DVDs. Para cada cliente é necessário saber seu pré- nome e seu sobrenome, seu telefone e seu endereço. Cada cliente recebe um número • Finalmente, desejamos saber quais DVDs estão locados por um dado cliente. Um cliente pode locar vários DVDs ao mesmo tempo. Não são mantidos registros históricos de aluguéis. 124 Janaína Gomide • Uma locadora de vídeos possui cerca de 2.000 DVDs, cujo empréstimo deve ser controlado. Cada DVD possui um número. Para cada filme, é necessário saber seu título e sua categoria (comédia, drama, aventura, ...). • Cada filme recebe um identificador próprio. Para cada DVD é controlado que filme ele contém. Para cada filme há pelo menos um DVD. Alguns poucos filmes necessitam de mais de um DVD. • Os clientes podem desejar encontrar os filmes estrelados pelo seu ator predileto. Por isso, é necessário manter a informação dos atores que atuam em cada filme. Os clientes, às vezes, desejam receber referências de determinado ator, tais como o nome real, a data de nascimento, etc. • A locadora possui clientes cadastrados. Só clientes cadastrados podem alugar DVDs. Para cada cliente é necessário saber seu pré- nome e seu sobrenome, seu telefone e seu endereço. Cada cliente recebe um número • Finalmente, desejamos saber quais DVDs estão locados por um dado cliente. Um cliente pode locar vários DVDs ao mesmo tempo. Não são mantidos registros históricos de aluguéis. 125 Janaína Gomide • Uma locadora de vídeos possui cerca de 2.000 DVDs, cujo empréstimo deve ser controlado. Cada DVD possui um número. Para cada filme, é necessário saber seu título e sua categoria (comédia, drama, aventura, ...). • Cada filme recebe um identificador próprio. Para cada DVD é controlado que filme ele contém. Para cada filme há pelo menos um DVD. Alguns poucos filmes necessitam de mais de um DVD. • Os clientes podem desejar encontrar os filmes estrelados pelo seu ator predileto. Por isso, é necessário manter a informação dos atores que atuam em cada filme. Os clientes, às vezes, desejam receber referências de determinado ator, tais como o nome real, a data de nascimento, etc. • A locadora possui clientes cadastrados. Só clientes cadastrados podem alugar DVDs. Para cada cliente é necessário saber seu pré- nome e seu sobrenome, seu telefone e seu endereço. Cada cliente recebe um número • Finalmente, desejamos saber quais DVDs estão locados por um dado cliente. Um cliente pode locar vários DVDs ao mesmo tempo. Não são mantidos registros históricos de aluguéis. 126 Janaína Gomide Exercício: Locadora 127 Janaína Gomide Exercício: Locadora Cliente (NúmeroC, Pré-nome, Sobrenome, Telefone, Endereço) Ator (CodA, Nome popular, Nome artístico, Data de nascimento) Filme (Id, Título, CodC) Categoria (CodC, Nome) DVD (NúmeroD, Id) Estrela (Id, CodA) Empréstimo (NúmeroC, NúmeroD) 128 Janaína Gomide ? Como são realizadas as operações? Transações 129 Janaína Gomide 130 Janaína Gomide Transações • Usuários de um banco de dados podem: – pesquisar, – inserir, – atualizar e – excluir dados. • Um conjunto de operações bem-sucedidas efetuadas por um único usuário é chamado de transação 131 Janaína Gomide Transações • Quando se compartilha um banco de dados, é importante: – garantir que múltiplas transações possam ser processadas sem causar conflitos nos dados – proteger os dados para evitar inconsistência na eventualidade de falhas durante o processamento de uma transação • Nesse sentido, as transações precisam apresentar algumas propriedades: ACID 132 Janaína Gomide Transações • Propriedades obrigatórias para uma transação: Atomicidade – uma transação precisa sempre terminar com uma operação de efetivação ou de reversão Consistência – processar uma transação nunca pode resultar em perda de consistência no banco de dados Isolamento – mesmo quando transações são processadas simultaneamente, os resultados precisam ser os mesmos de um processamento sequencial Durabilidade – o conteúdo de uma transação completa não pode ser afetado por falhas 133 Janaína Gomide Transações Atomicidade • Uma transação precisa terminar com uma efetivação ou com uma reversão para manter o banco de dados livre de inconsistências • Em resumo, ou todas as ações da transação são concluídas, ou todas são canceladas • Uma efetivação finaliza as operações de uma transação • Uma reversão as cancela. 134 Janaína Gomide Transações Consistência • Uma transação não deve gerar erros • Se o banco de dados estava consistente antes do processamento de uma transação, ele precisa continuar consistente depois que a transação se encerra • Garante que o processo da transação respeite todas as normas pré estabelecidas na integridade dos dados, como o relacionamento entre chaves para que não ocorra nenhuma divergência no momento da transação. 135 Janaína Gomide Transações Isolamento • Quando duas ou mais transações simultâneas produzem os mesmos resultados que seriam obtidos se elas tivessem sido processadas em momentos diferentes, diz-se que elas são serializáveis. • A propriedade de isolamento exige que as transações sejam serializáveis, o que serve como proteção contra erros • Para conseguir que as transações sejam serializáveis, é preciso ter controle sobre aquelas que ocorrem simultaneamente. • O método mais comum utilizado é o controle baseado em travas (bloqueios). 136 Janaína Gomide Transações Durabilidade • Um banco de dados gerencia dados importantes, de modo que garantir a segurança e durabilidade no caso de falhas é essencial. • Uma vez que a transação foi confirmada, vai continuar assim, mesmo que haja perda de energia ou erros. 137 Janaína Gomide ? Como criar tabelas, pesquisar, inserir, atualizar e excluir alguma informação? Linguagem SQL 138 Janaína Gomide 139 Janaína Gomide Linguagem SQL • Structured Query Language • SQL é a linguagem padrão para SGBDs relacionais • A linguagem SQL pode ser considerada um dos principais motivos para o sucesso dos banco de dados relacionais • Como ela se tornou um padrão para esse tipo de banco de dados, facilita a migração de aplicações para outros sistemas de banco de dados • É abrangente: tem instruções para definição de dados, consultas e atualizações 140 Janaína Gomide Linguagem SQL • Conceitos: – Tabela/Table = Relação – Linha/Row = Tupla – Coluna/Column = Atributo 141 Janaína Gomide Linguagem SQL SCHEMA • Um esquema SQL é identificado por um nome de esquema e inclui umidentificador de autorização para identificar o usuário ou conta do proprietário do esquema, bem como descritores para cada elemento. • Esses elementos incluem tabelas, restrições, domínios e outras construções CREATE SCHEMA Empresa AUTHORIZATION ‘Jsilva’ 142 Janaína Gomide Linguagem SQL CREATE TABLE • O comando CREATE TABLE é usado para especificar uma nova relação. • Deve informar o nome, seus atributos e restrições iniciais • Os atributos são especificados primeiro, e cada um deles recebe um nome, um tipo de dado para especificar seu domínio de valores e quaisquer restrições de atributo como NOT NULL 143 Janaína Gomide Linguagem SQL CREATE TABLE 144 Janaína Gomide Linguagem SQL CREATE TABLE • Tipos de dados básicos disponíveis para atributos: – Numérico: INTEGER, REAL, DECIMAL(i,j) – Cadeia de caracteres de tamanho fixo – CHAR(n) ou de tamanho variável VARCHAR(n) – Booleano: TRUE ou FALSE – DATE que possui 10 posições e seus componentes são DAY, MONTH e YEAR na forma DD-MM-YYYY. O tipo TIME com HOUR, MINUTE e SECOND • É possível especificar o tipo de dado de cada atributo diretamente. CREATE DOMAIN tipo_CPF AS CHAR(11) CREATE DOMAIN telefone AS CHAR (8 9) 145 Janaína Gomide Linguagem SQL CREATE TABLE Especificando restrições de chave e integridade referencial • Como chaves e restrições de integridade referencial são muito importantes, existem cláusulas especiais dentro da instrução CREATE TABLE para especificá-las. • A cláusula PRIMARY KEY especifica um ou mais atributos que compõem a chave primária de uma relação • Se a chave tiver um único atributo, a cláusula pode acompanhar o atributo diretamente. 146 Janaína Gomide Linguagem SQL CREATE TABLE Especificando restrições de chave e integridade referencial 147 Janaína Gomide Linguagem SQL CREATE TABLE Especificando restrições de chave e integridade referencial • A cláusula UNIQUE especifica chaves alternativas (secundárias) • Também pode ser especificada diretamente para uma chave secundária se esta for um único atributo. Dnome VARCHAR(15) UNIQUE; 148 Janaína Gomide Linguagem SQL CREATE TABLE Especificando restrições de chave e integridade referencial • A integridade referencial é especificada por meio da cláusula FOREIGN KEY (chave estrangeira) • Uma restrição de integridade referencial pode ser violada quando tuplas são inseridas ou excluídas, ou quando um valor de atributo de chave estrangeira ou chave primária é modificado. • A ação default que a SQL toma para essa violação é rejeitar a operação, o que é conhecido como opção RESTRICT. • Porém, o projetista pode especificar uma ação alternativa. 149 Janaína Gomide Linguagem SQL ON DELETE, ON UPDATE Especificando restrições de chave e integridade referencial • As opções incluem: – SET NULL – CASCADE – SET DEFAULT • Deve especificar ON DELETE ou ON UPDATE 150 Janaína Gomide Linguagem SQL ALTER TABLE • As restrições de chave, integridade de entidade e integridade referencial podem ser especificadas: – junto com a criação da tabela, – depois que os atributos forem declarados, ou, – acrescentadas depois, usando o comando ALTER TABLE ALTER TABLE tabelaA ADD FOREIGN KEY (chave) REFERENCES tabelaB (atributo) • PRIMARY KEY (atributo) • UNIQUE (atributo) • atributo NOT NULL • FOREIGN KEY (atributo) REFERENCES (tabela(atributo)) 151 Janaína Gomide Linguagem SQL CREATE TABLE 152 Janaína Gomide Linguagem SQL CREATE TABLE 153 Janaína Gomide Linguagem SQL CREATE TABLE 154 Janaína Gomide Linguagem SQL CREATE TABLE 155 Janaína Gomide Linguagem SQL CREATE TABLE 156 Janaína Gomide Linguagem SQL CREATE TABLE 157 Janaína Gomide Linguagem SQL Operações sobre Relações • As operações sobre um banco de dados relacional podem ser classificadas em: – Operações de recuperação (consulta) – Operações de atualização • Operações de atualização (sobre tuplas): – Inserção (insert) – Remoção (delete) – Modificação (update) 158 Janaína Gomide Linguagem SQL Operações sobre Relações • Inserção (insert) – Adiciona uma nova tupla (ou várias) em uma mesma relação • Remoção (delete) – Remove uma tupla da relação • Modificação (update) – Altera os dados de uma tupla Em qualquer operação, a integridade da base de dados relacional deve ser mantida! 159 Janaína Gomide Linguagem SQL Restrições Restrições de integridade não podem ser violadas! • Restrição de domínio: valor fora do domínio • Restrição de chave: valor já existe • Restrição de integridade de entidade: se a chave for NULL • Restrição de integridade referencial: se a chave estrangeira referencia tupla inexisteste 160 Janaína Gomide Linguagem SQL INSERT • Em sua forma mais simples, INSERT é usado para acrescentar uma única tupla a uma relação • Temos de especificar o nome da relação e uma lista de valores para a tupla Na mesma ordem em que foram declarados no comando CREATE TABLE Especifica o nome do atributo explicitamente. Precisa incluir todos os atributos com especificação NOT NULL e nenhum valor padrão. 161 Janaína Gomide Linguagem SQL DELETE • O comando DELETE remove as tuplas de uma relação. Ele inclui uma cláusula WHERE para selecionar as tuplas a serem excluídas • No entanto, a exclusão pode se propagar para as tuplas em outras relações. 162 Janaína Gomide Linguagem SQL UPDATE • O comando UPDATE é usado para modificar valores de atributo de uma ou mais tuplas selecionadas. • Tem a cláusula WHERE para selecionar as tuplas a serem modificadas em uma única relação • A cláusula SET especifica os atributos a serem modificados e seus novos valores. 163 Janaína Gomide Linguagem SQL UPDATE • Várias tuplas podem ser modificadas com um único comando UPDATE. • Ex : dar a todos os func ionár ios no departamento ‘Pesquisa’ um aumento de 10% no salário. 164 Janaína Gomide Linguagem SQL Exercícios Escreva instruções de criação das tabelas com as restrições necessárias: CREATE TABLE EDITORA (Nome VARCHAR (50) NOT NULL, Endereço VARCHAR(50), Telefone CHAR(9), PRIMARY KEY (Nome)); E a tabela Livro? 165 Janaína Gomide Linguagem SQL Exercícios Escreva instruções de criação das tabelas com as restrições necessárias: CREATE TABLE EDITORA (Nome VARCHAR (50) NOT NULL, Endereço VARCHAR(50), Telefone CHAR(9), PRIMARY KEY (Nome)); CREATE TABLE LIVRO (Cod_livro INT NOT NULL, Título VARCHAR(50), Nome_editora VARCHAR(50), PRIMARY KEY (Cod_livro), F O R E I G N K E Y ( N o m e _ e d i t o r a ) REFERENCES EDITORA (Nome)); 166 Janaína Gomide Linguagem SQL Exercícios • Escreva instruções de atualização SQL para realizar ações sobre o esquema de banco de dados: 1. Inserir um novo aluno <‘Alves’, 25, 1, ‘MAT’>, no banco de dados 2. Alterar o nome da disciplina ‘Banco de Dados’ para ‘Banco de Dados I’ 3. Excluir o registro para o aluno cujo nome é ‘Silva’ e cujo número de aluno é 17. 167 Janaína Gomide Linguagem SQL Exercícios • Escreva instruções de atualização SQL para realizar ações sobre o esquema de banco de dados: 1. Inserir um novo aluno <‘Alves’, 25, 1, ‘MAT’>, no banco de dados INSERT INTO Aluno VALUES (‘Alves’, 25, 1, ‘MAT’) ou INSERT INTO Aluno (Nome, Numero_aluno, Tipo_aluno, Curso) VALUES (‘Alves’, 25, 1, ‘MAT’) 168 Janaína Gomide Linguagem SQL Exercícios • Escreva instruções de atualização SQL para realizar ações sobre o esquema de banco de dados: 2. Alterar o nome da disciplina ‘Bancode Dados’ para ‘Banco de Dados I’ UPDATE Disciplina SET Nome_disciplina = ‘Banco de Dados I’ WHERE Nome_disciplina = ‘Banco de dados’ 169 Janaína Gomide Linguagem SQL Exercícios • Escreva instruções de atualização SQL para realizar ações sobre o esquema de banco de dados: 3. Excluir o registro para o aluno cujo nome é ‘Silva’ e cujo número de aluno é 17. DELETE FROM Aluno WHERE Nome = ‘Silva’ and Numero_aluno = 17 170 Janaína Gomide Linguagem SQL Operações sobre Relações • As operações sobre um banco de dados relacional podem ser classificadas em: – Operações de recuperação (consulta) ü Operações de atualização ü Operações de atualização (sobre tuplas): ü Inserção (insert) ü Remoção (delete) ü Modificação (update) 171 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • A SQL tem uma instrução básica para recuperar informações de um banco de dados: a instrução SELECT • A forma básica do comando SELECT é composta pelas três cláusulas SELECT, FROM e WHERE, e tem a seguinte forma: SELECT <lista de atributos> FROM <lista de tabelas> WHERE <condição>; Os operadores básicos de comparação lógicos para comparar valores de atributos entre si e com constantes são =, <, <=, >, >= e <> 172 Janaína Gomide Linguagem SQL Consultas Básicas em SQL 173 Janaína Gomide Linguagem SQL Consultas Básicas em SQL 174 Janaína Gomide Linguagem SQL Consultas Básicas em SQL 175 Janaína Gomide Linguagem SQL Consultas Básicas em SQL 176 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • Em SQL, o mesmo nome pode ser usado para dois (ou mais) atributos, desde que estes estejam em relações diferentes. • Se isso acontecer, e uma consulta em múltiplas tabelas se referir a dois ou mais atributos com mesmo nome, é preciso qualificar o nome do atributo com o nome da relação. 177 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • Também podemos criar um apelido para cada nome de tabela, para evitar a digitação repetida de nomes de tabela longos 178 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • A ambiguidade dos nomes de atributo também surge no caso de consultas que se referem à mesma relação duas vezes 179 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • A falta de uma cláusula WHERE indica que não há condição sobre a seleção de tuplas. • Dessa forma, todas as tuplas da relação especificada na cláusula FROM são selecionadas para o resultado da consulta 180 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • Para recuperar todos os atributos das tuplas selecionadas, não precisamos listar os nomes explicitamente. • Basta especificar um asterisco (*) 181 Janaína Gomide Linguagem SQL Consultas Básicas em SQL • Quando queremos eliminar tuplas duplicadas do resultado de uma consulta SQL, usamos a palavra DISTINCT na cláusula SELECT • Significa que apenas as tuplas distintas deverão permanecer no resultado 182 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Combinação de padrão de subcadeias e operadores aritméticos • LIKE – permite condições de comparação apenas sobre partes de uma cadeia de caracteres – % substitui um número qualquer de zero ou mais caracteres – _ substitui um único caractere 183 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Combinação de padrão de subcadeias e operadores aritméticos • LIKE – permite condições de comparação apenas sobre partes de uma cadeia de caracteres – % substitui um número qualquer de zero ou mais caracteres – _ substitui um único caractere 184 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Combinação de padrão de subcadeias e operadores aritméticos • BETWEEN 185 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Ordem dos resultados da consulta • SQL permite que o usuário ordene as tuplas no resultado de uma consulta pelos valores de um ou mais dos atributos • Cláusula ORDER BY 186 Janaína Gomide Linguagem SQL Consultas Básicas em SQL 187 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Ordem dos resultados da consulta • Padrão é ascendente, mas pode alterar para DESC 188 Janaína Gomide Linguagem SQL Consultas Básicas em SQL Resumo 189 Janaína Gomide Linguagem SQL Exercícios Para o banco relacional EMPRESA: 1. Recupere os nomes de todos os funcionários no departamento 5 que trabalham mais de 10 horas por semana no projeto ProdutoX 2. Liste os nomes de todos os funcionários que possuem um dependente com mesmo primeiro nome que seu próprio 3. Ache os nomes de todos os funcionários que são supervisionados diretamente por ‘Fernando Wong’ 190 Janaína Gomide Linguagem SQL Exercícios Para o banco relacional EMPRESA: 1. Recupere os nomes de todos os funcionários no departamento 5 que trabalham mais de 10 horas por semana no projeto ProdutoX 191 Janaína Gomide Linguagem SQL Exercícios Para o banco relacional EMPRESA: 1. Recupere os nomes de todos os funcionários no departamento 5 que trabalham mais de 10 horas por semana no projeto ProdutoX SELECT Pnome, Unome FROM FUNCIONARIO, PROJETO, TRABALHA_EM WHERE Dnr = Dnum and Fcpf = Cpf and Pnr = Projnumero and Dnr = 5 and Horas > 10 and Projnome = ‘ProdutoX' 192 Janaína Gomide Linguagem SQL Exercícios Para o banco relacional EMPRESA: 2. Liste os nomes de todos os funcionários que possuem um dependente com mesmo primeiro nome que seu próprio SELECT Pnome, Unome FROM FUNCIONARIO, DEPENDENTE WHERE Fcpf = Cpf and Nome_dependente = Pnome 193 Janaína Gomide Linguagem SQL Exercícios Para o banco relacional EMPRESA: 3. Ache os nomes de todos os funcionários que são supervisionados diretamente por ‘Fernando Wong’ SELECT Pnome, Unome FROM FUNCIONARIO as F1, FUNCIONARIO as F2 WHERE F2.Pnome = ‘Fernando’ and F2.Unome = ‘Wong’ and F2.Cpf = F1.Cpf_supervisor MCG241 – Sistemas de Informação Profa. Janaína Gomide 194 Janaína Gomide