Prévia do material em texto
Fundamentos em Engenharia de Dados Capítulo 3. Modelagem e manipulação de dados em ambientes relacionais Profª. Fernanda Farinelli Aula 3.1. Sistemas gerenciadores de bancos de dados relacionais Entender o que são sistemas gerenciadores de bancos de dados. Entender o que são sistemas gerenciadores de bancos de dados relacionais. Nesta aula Banco de dados Conjunto de dados armazenados (persistidos). Coleção de dados inter-relacionados. Sistemas de banco de dados Usuários Software e hardware SGBD Banco de dados Sistema Gerenciadores de Banco de Dados Criar, modificar e eliminar BD CRUD em dados Segurança de acesso Integridade de dados Backup e recover Controle de concorrência Tipos de sistemas gerenciadores de banco de dados Bancos de dados relacionais Bancos de dados NoSQL Bancos de dados NewSQL Sistemas Gerenciadores de Banco de Dados Relacionais Implementa o modelo relacional de dados. Fundamentado na teoria de conjuntos e nas relações entre os conjuntos. • Operações de junção, união e seleção. Persistência de dados mais adotados. Persistência de dados estruturados. Propriedades de transação ACID Atomicidade Transação é uma unidade atómica de processamento. Ou se faz tudo, ou nada. Consistência Os dados estarão sempre consistentes e completos. Uma transação leva de um estado consistente a outro estado consistente. Isolamento Cada transação é realizada de forma isolada. Uma transação começa após o término da outra. Durabilidade As alterações causadas por uma transação devem ser persistidas. O dado deve durar, estar salvo, ele não deve ser perdido. SGBD relacionais Entendemos o que são bancos de dados. Visão geral dos bancos de dados relacionais. Conhecemos as propriedades ACID que regem as transações em um banco de dados relacional. Conhecemos algumas ferramentas disponíveis. Conclusão Entender o que é modelagem de dados. Próxima aula Aula 3.2. Modelagem de dados Conhecer os níveis de abstração de modelos de dados. Entender o que é modelagem de dados. Nesta aula Modelo e modelagem de dados Modelo Abstração de um objeto ou evento da realidade. Modelo de dados Representação, normalmente gráfica, de estrutura de dados de um sistema de informação. Um diagrama é uma representação gráfica do modelo. Modelagem de dados Atividade realizada para a criação de um banco de dados. Ato Baixo Abstração Nível conceitual Nível lógico Nível físico A b st ra ç ã o Abstração é o mecanismo através do qual o ser humano se concentra em determinadas características de objetos ou conceitos, e “abandona” outras características “indesejáveis”. Tipos de modelos de dados • Visão de negócio, independente de tecnologia ou paradigma de desenvolvimento. Modelo Conceitual • Modelo de dados que leva em consideração algum paradigma tecnológico, mas não uma ferramenta em si. Modelo Lógico • Modelo que considera a tecnologia ou sistema que será usado para implementar o modelo. Modelo Físico Projeto de banco de dados Levantamento e análise de requisitos Definir o modelo conceitual Definir o modelo lógico Definir o modelo físico 1 2 3 4 Conhecer os fundamentos sobre modelos e modelagem de dados. Conhecemos os passos para um projeto de banco de dados. Conclusão Entender o que é o modelo conceitual. Conhecer o modelo entidade-relacionamento e seus principais elementos. Aprender a construir um modelo entidade-relacionamento. Próxima aula Aula 3.3. Modelagem entidade-relacionamento Entender o que é o modelo conceitual. Conhecer o modelo entidade-relacionamento e seus principais elementos. Aprender a construir um modelo entidade-relacionamento. Nesta aula Modelo conceitual Representa e/ou descreve a realidade do ambiente, constituindo uma visão global dos principais dados e relacionamentos que compõe o projeto. Não registra como estes dados estão armazenados no SGBD. Existem várias técnicas de modelagem conceitual: – Abordagem entidade-relacionamento. – Abordagem orientada a objetos. Modelo entidade-relacionamento (MER) Definido por Peter Chen em 1976, teve como base a teoria relacional criada por E.F.Cood (1970). Fundamentado na ideia de que a visão de uma dada realidade baseia-se no relacionamento entre conceitos desta, os quais retratam os fatos que governam esta mesma realidade, e que cada conceito (entidade ou relacionamento) pode possuir atributos (qualificadores desta realidade). Sua representação gráfica é facilitada através do diagrama entidade- relacionamento (DER). Modelo entidade-relacionamento (MER) Inspirado na teoria dos conjuntos. Elementos do MER Entidades Entes ou “coisas” do mundo real Concreto ou abstrato 27 Atributos Características que descrevem as entidades Simples ou compostos. Mono ou multivalorados Relacionamento Envolvimento entre entidades Expressos por verbos Entidade É uma representação concreta ou abstrata de um objeto, com características semelhantes, do mundo real. – Ex.: Fornecedor, Pessoa, Imóvel, Curso e Venda. Entidade fraca: não existe se não estiver relacionada a outra, isto é, ela é logicamente dependente da outra. Alguns conjuntos entidade não possuem um conjunto de atributos capaz de identificar univocamente uma determinada entidade. Neste caso, sua existência depende da existência de outra entidade. EmpregadoDepartamento Dependente Atributo Elemento de dado que contém o valor de uma propriedade de uma entidade. Classificação de atributos: Atributo simples Atributo único Atributo opcional Atributo monovalorado Atributo composto Atributo não único Atributo obrigatório Atributo multivalorado Atributo Atributo chave (identificador): identifica unicamente cada entidade de um conjunto-entidade, devem ser obrigatórios e únicos. Atributo derivado: o seu valor pode ser calculado a partir do valor de outro(s) atributo(s). – Ex.: idade (pode ser calculada a partir da data de nascimento). Domínio: descrição de possíveis valores permitidos para um atributo. – Ex.: Sexo {M, F}. Cada atributo possui um NOME, um TIPO e um TAMANHO (número de caracteres). Tipo: determina a natureza dos valores permitidos para um atributo. – Ex.: inteiro, real, string, etc. Relacionamentos As entidades são conectadas/relacionadas umas às outras através de relacionamentos. Responsável por definir as características das ligações entre as entidades. O nome do relacionamento é expresso por um verbo ou por uma locução verbal. Cardinalidade: é o número máximo e mínimo de ocorrências de uma entidade, que estão associadas às ocorrências de outra entidade que participa do relacionamento. É lotadoEmpregado Departamento 1..* 1 Grau do relacionamento Relacionamento binário é aquele envolve duas ocorrências de entidade. Relacionamento ternário é aquele envolve três conjuntos de entidades. Autorrelacionamento Relacionamento entre ocorrências de uma mesma entidade (relacionamento unário). Material 0..N 1..N É composto compõe Pessoa 0..1 0..1 É cônjuge É cônjuge Empregado 0..N 0..1 É gerenciado Gerencia Generalização/Especialização Onde uma ou mais entidades possuem parte das características de uma entidade mais genérica. – Total: para cada ocorrência da entidade genérica, existe sempre uma ocorrência em uma das entidades especializadas. – Parcial: nem toda ocorrência da entidade genérica possui uma ocorrência correspondente em uma entidade especializada. Cliente Pessoa jurídicaPessoa física ISA Código nome cpf rg cnpj t (total) Funcionário FisioterapeutaMedico ISAMatrícula nome CRM CREFITO p (parcial) Generalização/Especialização – Múltipla: nem toda ocorrência da entidade genérica possui uma ocorrência correspondente em uma entidade especializada. Entidade associativa As entidades associativas são o resultados de relacionamentos N:N. Podem possuir atributos próprios. ConsultaMédico 0..* 0..* Paciente nome nomeCRM cpf data Medicamento prescreve Notação do diagrama entidade- relacionamento (DER) ATRIBUTO DERIVADO Cardinalidade: 1..1, 1..N ou 1..*; N..N ou *..* AUTORRELACIONAMENTO Exemplo de DER Conhecer os fundamentos sobre modelos e modelagem de dados. Conhecemos o modelo entidade-relacionamento (MER), que é um modelo conceitual, independente de paradigma tecnológico. Conclusão Aprender a modelar dados conforme o paradigma tecnológico de bancos de dados relacional. Próxima aula Aula 3.4.1. Modelagem relacional de dados (Parte 1) Conhecer o modelo lógico do paradigma tecnológico de banco de dados relacional. Conhecer os principais elementos de um modelo relacional de dados. Nesta aula Modelo lógico Definido por E. F. Codd em 1970, teve sua grande aceitação comercial a partir de meados da década de 1980. Utilizados para se descrever a estrutura de um banco de dados da forma como será manipulado, através de um paradigma tecnológico de banco de dados. Os modelos lógicos mais conhecidos, são: – Modelo de rede. – Modelo hierárquico. – Modelo relacional. – Modelo orientado a objetos. – Modelo objeto – Relacional. Modelo relacional Representa um banco de dados como um conjunto de relações (ou tabelas). Cada linha de uma tabela representa um “fato” que tipicamente corresponde a uma entidade ou relacionamento do mundo real. Modelo relacional Características básicas: – Organização dos dados: • Relação, atributo, relacionamento, chaves, domínio e tuplas. – Restrição de integridade: restrições básicas para dados e relacionamentos. • Domínio, chaves primária e única, integridade referencial e valores opcionais. Manipulação de dados: – Linguagens formais (álgebra e cálculo relacional). – Linguagem SQL (comercial). Modelo relacional Relação: – Principal construção para representação dos dados no modelo relacional, análogo à entidade do MER. – É uma tabela de valores, onde cada linha representa uma coleção de dados relacionados. – Composto por um cabeçalho e um corpo. – Cabeçalho: • Número fixo de atributos (grau da relação). • Atributos não-ambíguos. – Corpo: • Número variável de tuplas (cardinalidade da relação). • Ordem não é relevante. Modelo relacional Relação Modelo relacional Relação Cabeçalho Corpo Modelo relacional Atributo: – Um item de dado do Banco de Dados (BD). – Possui um nome e um domínio. – Exemplos: Nome do atributo Nome dataNascimento salario Domínio do atributo Char (caractere) Date decimal Modelo relacional Atributo Modelo relacional Domínio: – Representa o conjunto de valores atômicos admissíveis de um componente de uma relação. – Possui uma descrição física e outra semântica. • A descrição física identifica o tipo e o formato dos valores que compõem o domínio. • A descrição semântica ajuda na interpretação de seus valores. – Manipulação: dois valores só podem ser comparados se definidos sobre o mesmo domínio. •Exemplos: Idade: inteiro / Nome: alfanumérico (string). Modelo relacional Tupla: – Um conjunto de pares (atributo, valor). – Valor de um atributo, definido no momento da criação de uma tupla, que deve ser: • Compatível com o domínio ou NULL. • Atômico (indivisível). Modelo relacional Tupla Domínio numérico alfanumérico Cada linha é uma tupla 6 tuplas Modelo relacional Chaves: – Conjunto de um os mais atributos de uma relação. • Chave candidata. • Chave primária (primary key) – PK. • Chave alternative. • Chave estrangeira (foreign key) – FK. Modelo relacional Chave Primária - Primary key (PK): – Atributo(s) cujo (conjunto de) valor(es) identifica(m) unicamente uma tupla em uma relação. – Recupera uma única ocorrência do conjunto de dados. – Unicidade de valores na coluna que compõe a chave. – Exemplo: • Mátricula do aluno. Modelo relacional Chave candidata: – Possui as mesmas propriedades que a chave primária e são candidatos a serem PK. • Qual escolher para chave primária? • Uma estratégia é escolher para chave primária a chave candidata com o menor número de caracteres. 347685784432 693529876987 701034263890 CPF 20/02/198501427Maria 12/11/198002467Renato 12/11/198001035Maria DataNascMatrículaNome Chaves candidatas Modelo relacional Chave alternativa: chave candidata que não foi definida como primária. Chave primária Chave alternativa 347685784432 693529876987 701034263890 CPF 20/02/198501427Maria 12/11/198002467Renato 12/11/198001035Maria DataNascMatrículaNome Modelo relacional Chave primária composta Chave primária Chave estrangeira (Foreign Key - FK): – Atributo(s) de uma relação, cujos valores devem obrigatoriamente aparecer na chave primária de uma outra relação (da mesma ou de outra), que esteja relacionada com ela. – Implementa o relacionamento em um modelo relacional. Modelo relacional Departamento Empregado Matrícula CPF Nome dataNasc codDepto 01035 701034263890 Maria 12/11/1980 101 Código Nome 101 Compras PK FK Modelo relacional Chave estrangeira Chave primária Conhecemos o modelo relacional de dados e alguns de seus elementos: – Relação é uma tabela. – Atributo é um campo (coluna da tabela). – Tupla é uma linha da tabela. – Domínio: tipo de dado, formato de um atributo. – Chave primária (PK). – Chave candidata e chave alternativa. – Chave estrangeira (FK). Conclusão Conhecer os tipos de restrições de integridade existentes no modelo relacional. Próxima aula Aula 3.4.2. Modelagem relacional de dados (Parte 2) Conhecer os tipos de restrições de integridade existentes no modelo relacional. Aprender os passos para criar um modelo relacional de dados. Nesta aula Modelo relacional Restrições de Integridade: – Regras que impõe os estados válidos da base de dados. – Garantem que os dados refletem corretamente a realidade modelada. – Podem ser: • Integridade de domínio. • Integridade de valores padrão. • Integridade de valores vazios. • Integridade de unicidade. • Integridade de chave primária/alternativa. • Integridade semântica. • Integridade referencial. Modelo relacional Restrição de integridade: – Integridade de domínio: • Define os valores que podem ser assumidos pelos campos de uma coluna. • Exemplo: – Idade: Inteiro (não aceita valores alfanuméricos ou decimais). – Estado civil só pode ser um dos seguintes valores: solteiro, casado, viúvo, separado judicialmente, união estável ou divorciado. Modelo relacional Restrição de integridade: – Integrigade de valor padrão (default): • Quando o valor do campo não for informado, este campo assume um valor padrão predeterminado. • Exemplo: – A data padrão da abertura do chamado é igual a data atual. – Se não informada a nacionalidade do cliente, assume-se que é brasileiro. Modelo relacional Restrição de integridade: – Integridade de vazio: • Especifica se os campos de uma coluna podem ou não serem vazios (nulos). • Exemplo: – É obrigatório informar o CPF do funcionário (Não nulo – NOT NULL). – É opcional informar o número de dependents (Nulo – NULL). Modelo relacional Restrição de integridade: – Integridade de unicidade: • Define que o valor do campo ou campos, são únicos, ou seja, não se repetem. • Exemplo: – O CPF do funcionário deve ser único, ou seja, dois funcionários distintos não podem ter o mesmo CPF. Modelo relacional Restrição de integridade: – Integridade semântica: – Especificada através de regras sobre o esquema do banco dedados. – Exemplos: • O salário de um empregado deve ser menor ou igual ao do seu supervisor. • A data de nascimento não pode ser superior à data atual. • Não é permitido abaixar o salário de um empregado. • O número de horas semanais de um empregado em um projeto não pode ser maior do que 50. Modelo relacional Restrição de integridade: – Integridade de chave primária/alternativa: • Define que os valores da chave primária e alternativa devem ser únicos e não nulos. • Exemplos: –Na relação/tabela de funcionário, a matrícula e o CPF são únicos e obrigatórios. Observação: um campo que foi definido com a restrição apenas de unicidade pode ser opcional, ou seja, aceitar valores nulos. Modelo relacional Restrição de integridade: – Integridade referencial: • Refere-se à integridade definida pelo relacionamento PK-FK. • Define que os valores dos campos que aparecem numa chave estrangeira, devem aparecer na chave primária (candidata) da tabela referenciada. • Usada para manter a consistência entre as tuplas de duas relações (tabelas). Modelo relacional Restrição de integridade: integridade referencial. • Garante a consistência entre as duas relações (tabelas). DepartamentoEmpregado Código Nome 101 Compras 102 Vendas 103 Financeiro PKMatrícula CPF Nome codDepto 01035 70103426389 Maria 101 01036 12345678952 José 102 01037 14253687545 Maria 102 01038 78932145682 Ana 101 01039 963852074119 Pedro 103 FK 010140 46791382461 João 100 Não existe o departamento 100 Modelo relacional Restrição de integridade: – Restrições de atualização: • Associada à uma restrição de integridade referencial. • Como proceder quando uma operação de atualização ou exclusão de dados acontecer na PK. • Pode ser definido como: – CASCATA (CASCADE): se alterar a o valor da PK, altera o valor da FK também. – RESTRITO (RESTRICT): não pode alterar a PK enquanto tiver FK associada a ela. – DEFINE COMO NULO (SET NULL): se alterar o valor da PK, altera o valor da FK para NULO. Processo de modelagem Identifique as relações. Identifique as características que descreve as relações (atributos). Determine os domínio de valores dos atributos (numérico, alfanumérico, booleano etc.). Identifique os atributos-chave (candidatas e chave primária). Determine como as relações se relacionam entre elas, incluindo a cardinalidade dos relacionamento e chave estrangeira. Identifique as restrições de integridade existentes. Processo de modelagem Exemplo de requisito: – Um funcionário pode ser designado para trabalhar em apenas um departamento, durante a vigência de seu contrato. – Um departamento pode designar muitos funcionários. – Um departamento é supervisionado por apenas um funcionário. – Cada projeto é de responsabilidade de um único departamento, entretanto, ele pode ter funcionários de diversos departamentos trabalhando. – Um funcionário pode ser o gerente de um único projeto. – Os funcionários podem ser alocados em múltiplos projetos, com definição de número de horas de dedicação. Processo de modelagem Exemplo de requisito: – Um funcionário pode ser designado para trabalhar em apenas um departamento durante a vigência de seu contrato. – Um departamento pode designar muitos funcionários. – Um departamento é supervisionado por apenas um funcionário. – Cada projeto é de responsabilidade de um único departamento, entretanto, ele pode ter funcionários de diversos departamentos trabalhando. – Um funcionário pode ser o gerente de um único projeto. – Os funcinários podem ser alocados em múltiplos projetos, com definição de número de horas de dedicação. Identifique as relações. Processo de modelagem – Funcionário: • Nome (alfanumérico), matrícula (alfanumérico), salário (numérico decimal), data nascimento (data), departamento etc. – Departamentos: • Nome (alfanumérico), código (alfanumérico), gerente (alfanumérico) etc. – Projeto: • Nome (alfanumérico), código (alfanumérico), gerente (alfanumérico), gerente etc. – Alocação: • Funcionários alocados, horas de alocação, projeto etc. Identifique as características que descreve as relações (atributos). Determine os domínio de valores dos atributos (numérico, alfanumérico, booleano etc.). Processo de modelagem – Candidatas: Primária: – Funcionário: • Nome (alfanumérico), matrícula (alfanumérico), cpf (alfanumérico), salário (numérico decimal), data nascimento (data), departamento etc. – Departamentos: • Nome (alfanumérico), código (alfanumérico), gerente (alfanumérico) etc. – Projeto: • Nome (alfanumérico), código (alfanumérico), gerente (alfanumérico) etc. – Alocação: • Funcionários alocados, horas de alocação, projeto etc. Identifique os atributos-chave (candidatas e chave primária). Processo de modelagem Determine como as relações se relacionam entre elas, incluindo a cardinalidade dos relacionamento e chave estrangeira. designado Departamento AlocaçãoFuncionário 0..* 0..* Projeto 0..* 1 0..1 1 gerencia É responsável 0..1 1 Processo de modelagem Unicidade: matrícula e CPF de funcionário, código do departamento e do projeto. Vazio: nome de funcionário e departamento são obrigatórios. Semântica: a data de nascimento não pode ser superior à data atual. Identifique as restrições de integridade existentes. Conhecemos os diversos tipos de restrições de integridade. – Integridade de domínio. – Integridade de valores padrão. – Integridade de valores vazios. – Integridade de unicidade. – Integridade de chave primária/alternativa. – Integridade semântica. – Integridade referencial. Aprendemos os passos para criar um modelo relacional de dados. Conclusão Entender o que é um modelo físico. Conhecer detalhes do modelo de dados relacional físico. Próxima aula Aula 3.5. Modelo de dados físico Entender o que é um modelo físico. Conhecer detalhes do modelo de dados relacional físico, considerando o SGBD MySQL. Nesta aula Modelo físico Descreve, por meio de alguma linguagem, como será feita a armazenagem no banco. Nesse nível se escolhe qual Sistema Gerenciador de Banco de Dados (SGBD) será usado, levando em consideração o modelo lógico adotado (paradigma tecnológico). Aqui são detalhados os componentes da estrutura física do banco, como tabelas, campos, tipos de valores, índices etc. Modelo físico Exemplos de SGBDs conforme modelo lógico: – Modelo hierárquico IMS e Adabas (IBM), DMSII (Unisys). – Modelo relacional Oracle, SQL Server, DB2, MySQL etc. – Modelo orientado a objetos Caché e Jasmine. Projeto físico de banco de dados Adiciona ao modelo questões relativas a: – Mapa de Acesso Lógico (MAL): especifica como os dados serão acessados, usuários (aplicação), a periodicidade, tipo de processamento (batch ou on- line), entre outros. – Dimensionamento de espaço e processamento. – Criação de índices para acesso aos dados (performance). – Definir views para simplificar acessos. – Definir tipos de colunas adequados (alfanumérico VARCHAR). – Identificar tabelas com grandes volumes de dados para eventual particionamento. – Definir parâmetros para garantir a disponibilidade dos dados conforme requisitos. Modelo relacional físico Exemplos de diferenças entre SGBDs relacionais: – Tipos de dados: • VARCHAR2 Existe no Oracle mas não existe no MySQL e SQL Server • MONEY Existe no PostGreSQL e não existe no MySQl e Oracle. – Objetos de dados: • Materialized view Existe apenas no Oracle, PostgreSQL e SQL Server. • Auto-incremento Não existe no Oracle, é feito via SEQUENCE.. Tipos de dados - MySQL vs SQL Server M y S Q L S Q L S e rv e r Modelo relacional físico Em geral, é descrito por um script escrito utilizando a linguagem SQL (Structured Query Language). Entendemos o que é um modelo físico. Conhecemos detalhes que são parte do projeto de banco dedados relacional físico. Conclusão Aprender a realizar a modelagem de dados utilizando a ferramenta case MySQL Workbench. Próxima aula Aula 3.6. Modelagem de dados utilizando o MySQL Workbench Conhecer o MySQL Workbench e sua funcionalidade para modelagem de dados. Aprender a realizar a modelagem de dados utilizando a ferramenta case MySQL Workbench. Nesta aula Ferramentas CASE CASE (do inglês Computer-Aided Software Engineering). Ferramentas que auxiliam atividades de engenharia de software. – DBDesigner (Download: http://fabforce.eu/dbdesigner4/). – MySQL Workbench: • (Download: https://dev.mysql.com/downloads/workbench/). – ER/Studio (Idera Software). – ERwin Data Modeler (CA Technologies). – Ferramentas online: • www.lucidchart.com. • https://creately.com/. http://fabforce.eu/dbdesigner4/ https://dev.mysql.com/downloads/workbench/ http://www.lucidchart.com/ https://creately.com/ Instalação MySQL Workbench Para realizer o download do instalador, acesse: – https://dev.mysql.com/downloads/workbench/ Siga o passo a passo disponível. https://dev.mysql.com/downloads/workbench/ Diagrama do modelo relacional Nesta aula, começamos a criar o modelo abaixo no MySQL Workbench. Finalize o diagrama abaixo e poste suas dúvidas no forum de discussão. Conhecemos o MySQL Workbench e sua funcionalidade para modelagem de dados. Aprendemos a realizar a modelagem de dados utilizando a ferramenta case MySQL Workbench. Conclusão Conhecer a linguagem de consulta estruturada usada na manipulação de dados de bases de dados relacionais. Próxima aula Aula 3.7. Introdução à linguagem SQL Apresentar a linguagem de consulta estruturada usada na manipulação de dados. Nesta aula Linguagem de consulta estruturada Structured Query Language (SQL). Linguagem comercial usada fundamentalmente em SGBDs relacionais. – Padrão ISO desde a década de 80. A razão do sucesso dos bancos de dados relacionais e da linguagem SQL se deve ao fato de existir um modelo matemático formal, que serviu de base para seu desenvolvimento. – Sua base formal é a álgebra relacional e a teoria dos conjuntos. Não é apenas uma linguagem de consulta. Linguagem SQL SQL DDL CREATE ALTER DROP DQL SELECT DML INSERT UPDATE DELETE DCL GRANT REVOQUE Data Definition Language Data Manipulation Language Data Control Language Data Query Language Linguagem SQL • Linguagem de definição de dados: • É um conjunto de instruções usado para criar e modificar as estruturas dos objetos armazenados no banco de dados. DDL • Linguagem de consulta de dados: • É um conjunto de instruções usado para consultar dados nas estruturas dos objetos armazenados no banco de dados. DQL • Linguagem de Manipulação de Dados: • É um conjunto de instruções usada nas consultas e modificações dos dados armazenados nas tabelas do banco de dados. DML • Linguagem de Controle de Dados: • São usados para controle de acesso e gerenciamento de permissões para usuários em no banco de dados. DCL • Linguagem de Controle de Transações: • São usados para gerenciar as mudanças feitas por instruções DML. • Permite que as declarações a serem agrupadas em transações lógicas. DTL Visão geral da linguagem SQL. Conclusão Conhecer a linguagem de definição de dados. Próxima aula Aula 3.8. Linguagem de definição de dados Conhecer a linguagem de definição de dados do SQL. Nesta aula Linguagem de definição de dados Data Definition Language (DDL) Subconjunto de comandos para criação e manutenção de objetos de banco de dados. • Esquemas, tabelas, índices, chaves, colunas, visões, restrições de integridade etc. Linguagem de definição de dados CREATE objeto: – Cria um objeto (uma tabela, por exemplo) no banco de dados. ALTER objeto: – Altera a estrutura ou a configuração de um objeto no banco de dados. DROP objeto: – Exclui um objeto do banco de dados. Objetos podem ser: – DATABASE, TABLE, INDEX, CONSTRAINT (PRIMARY KEY, FOREING KEY, UNIQUE KEY), ROLE, USER, PROCEDURE, FUNCTION, TRIGGER e VIEW. – Existem variações entre fabricantes de SGBDs. DDL - Criando uma base de dados Criação de um BD: – SQL padrão não oferece tal comando. •CREATE DATABASE <nome_BD>; Exclusão de um BD: – SQL padrão não oferece tal comando. – Quando executado, exclui tudo, BD e todos os objetos de dados abaixo dele. •DROP DATABASE <nome_BD>; DDL - Trabalhando com tabelas SQL oferece quatro instruções para definição de tabelas: – Create Table: • Define a estrutura da tabela. • Cria a tabela vazia. – Drop Table: • Elimina a tabela da base de dados. • Se a tabela já estiver populada, os dados também serão excluídos. – Alter Table: • Permite modificar a definição da tabela. – Truncate Table: • Elimina apenas os dados da tabela. DDL - Criando uma tabela CREATE TABLE - cria a tabela, define colunas e restrições. CREATE TABLE tabela ( coluna 1 tipo [<restrições da coluna 1>], coluna 2 tipo [<restrições da coluna 2>], .... coluna n tipo [<restrições da coluna n>], <restrições da tabela> ); Restrições de colunas: – NOT NULL. – DEFAULT valor. – CHECK(condição). CREATE TABLE Restrições de tabela: – PRIMARY KEY ( <coluna(s) da chave primária> ) – UNIQUE ( <coluna(s) da chave candidata> ) – FOREIGN KEY ( < coluna(s) da chave estrangeira> REFERENCES tabelaRef [(<chave primária>)] [<ações>] •<ações> –ON DELETE | ON UPDATE »CASCADE | SET NULL | SET DEFAULT – CHECK(condição) CREATE TABLE Exemplo no MySQL: DDL - Alterando uma tabela ALTER TABLE – incluir/alterar/remover definições de colunas e restrições. ALTER TABLE tabela <ação>; <ação>: - ADD novaColuna tipo [<restrições de coluna>] - ADD [CONSTRAINT nome] <restrição de tabela> - DROP coluna [CASCADE | RESTRICT] - DROP CONSTRAINT nome - ALTER coluna DROP DEFAULT; - ALTER coluna SET DEFAULT <valor>; DDL – Alterando uma tabela ALTER TABLE ADD novoAtrib tipo [<restrições de coluna>] DROP atributo [CASCADE | RESTRICT] CASCADE: todas as visões e restrições (constraints) que referenciam o atributo, são removidas automaticamente. RESTRICT: atributo só é removido se não houver nenhuma visão ou restrição que o referencie. ALTER TABLE Exemplo no MySQL: DDL - Esvaziando uma tabela TRUNCATE TABLE: exclui todas as linhas de uma tabela da base de dados, mantendo a tabela vazia. TRUNCATE TABLE tabela; – CASCADE: todas as visões e restrições que referenciam a tabela são removidas automaticamente – RESTRICT: a tabela é removida somente se não for referenciada em nenhuma restrição ou visão. TRUNCATE TABLE Considere a tabela cliente e seus dados, conforme imagem abaixo: Tabela Cliente TRUNCATE TABLE Para limpar os dados da tabela, execute o comando abaixo: TRUNCATE TABLE cliente; O resultado será a tabela vazia: Tabela Cliente DDL – Excluindo uma tabela DROP TABLE: exclui uma tabela da base de dados. DROP TABLE: tabela [CASCADE | RESTRICT]; – CASCADE: todas as visões e restrições que referenciam a tabela são removidas automaticamente. – RESTRICT: a tabela é removida somente se não for referenciada em nenhuma restrição ou visão. DROP TABLE Exemplo no MySQL: DDL - Trabalhando com índices Criar um índice: CREATE INDEX <nome do índice> ON tabela <coluna(s)>; Exemplo no MySQL: – Considere a tabela Cliente conforme script abaixo à esquerda. DDL - Trabalhando com índices Excluir um índice (varia conforme o SGBD): – MYSQL: ALTER TABLE table_name DROP INDEX index_name; – SQLServer: DROP INDEX table_name.index_name; – Oracle e DB2: DROP INDEX index_name; Exemplo no MySQL: DDL - Trabalhando com visões Criar uma visão (view): CREATE VIEW <nome da view> AS <commando DQL para criação da visão>; Excluir uma visão (view): DROP VIEW <nome da view>; Exemplo no MySQL - Considere a tabela cliente: Conhecemosa linguagem de definição de dados do SQL e seus principais comandos. Aprendemos a criar, alterar e excluir objetos do banco de dados. Conclusão Aprender como incluir, excluir e alterar dados nas tabelas do banco de dados. Próxima aula Aula 3.9. Linguagem de manipulação de dados Conhecer a linguagem de manipulação de dados do SQL. Aprender como incluir, excluir e alterar dados nas tabelas do banco de dados. Nesta aula Linguagem de manipulação de dados Data manipulation language (DML). Parte da linguagem SQL que define operações de manipulação de dados: – Inclusão / inserção (INSERT). – Alteração (UPDATE). – Exclusão (DELETE). Instruções declarativas: – Manipulação de conjuntos. – Especifica-se o que fazer e não como fazer. Linguagem de manipulação de dados Inclusão ou inserção de dados: INSERT INTO nome_tabela [(lista_atributos)] VALUES (lista_valores_atributos) [, (lista_valores_atributos)] Linguagem de manipulação de dados Exemplos usando o MySQL: Linguagem de manipulação de dados Alteração de dados: UPDATE nome_tabela SET nome_atributo_1 = Valor [{, nome_atributo_n = Valor}] [WHERE condição] Exemplo no MySQL: Linguagem de manipulação de dados Exclusão de dados: DELETE FROM nome_tabela [WHERE condição] Exemplo no MySQL: Conhecemos os comando da linguagem SQL para incluir, excluir e alterar dados nas tabelas do banco de dados. Conclusão Realizar os comandos de DDL e DML no MySQL, utilizando a ferramenta MySQL Workbench. Usar o MySQL Workbench para realizar alguns comandos de criação de objetos e de manipulação de dados. Próxima aula Aula 3.10. MySQL Workbench: DDL e DML Usar o MySQL Workbench para realizar alguns comandos de criação de objetos e de manipulação de dados. Nesta aula Conhecemos as funcionalidades do MySQL Workbench para criar objetos de banco de dados utilizando a linguagem DDL do SQL. Conhecemos as funcionalidades do MySQL Workbench para manipular (incluir, alterar e excluir) dados do banco de dados utilizando a linguagem DML do SQL. Conclusão Conhecer a linguagem de consulta de dados. Próxima aula Aula 3.11.1. Linguagem de consulta de dados (Parte 1) Conhecer a linguagem de consulta de dados. Nesta aula Linguagem de consulta de dados Data Query Language (DQL). SELECT: comando de consulta. – Retorno Tabela resultado (multiconjunto – potencialmente um conjunto com repetições). Sintaxe básica (consulta em uma única tabela): SELECT <lista de atributos> ou * FROM <tabela> Significa que são todas as colunas Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados da tabela genero. Linguagem de consulta de dados Exemplo no MySQL: – Listar a descrição da tabela genero. Linguagem de consulta de dados Valores não duplicados (distintos): Valores ordenados: Linguagem de consulta de dados Exemplo no MySQL: – Listar as diferentes descrições existentes na tabela genero. Linguagem de consulta de dados Exemplo no MySQL: listar ordenado. Linguagem de consulta de dados Filtros de seleção (condição): Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados apenas dos clientes que moram no estado do Ceará. Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados apenas dos clientes que NÃO moram no estado do Ceará. Linguagem de consulta de dados Filtros de valor nulo: Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados apenas dos clientes cujo débito é NULO ou ausente. Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados apenas dos clientes cujo débito NÃO é NULO. Linguagem de consulta de dados Combinação de múltiplos filtros (condições): – Utiliza combinações dos operadores AND e OR. Linguagem de consulta de dados Exemplo no MySQL: – Listar apenas os clientes cujo debito não é nulo E moram no estado do Ceará. Listar apenas os clientes cujo débito não é nulo OU moram no estado do Ceará. Linguagem de consulta de dados Funções de agregação: Mínimo Máximo Contagem Média Somatório Linguagem de consulta de dados Exemplo no MySQL: 1. Existem quantos clientes cadastrados? 2. Existem quantos clientes cadastrados onde o valor do débito foi informado? 3. Listar o valor do menor e maior débito, da média do débito e o somatório do débito. 1 2 3 Linguagem de consulta de dados Filtro de funções de agrupamento: Linguagem de consulta de dados Exemplo no MySQL: – Listar o estado, o valor máximo e o valor mínimo do débito para cada estado. Linguagem de consulta de dados Exemplo no MySQL: 1. Listar o estado, média do valor do débito e o somatório do débito para cada estado. 2. Em cada estado existem quantos clientes cadastrados? Para quantos clientes o valor do débito foi informado? 1 2 Linguagem de consulta de dados Exemplo no MySQL: 1. Listar o estado e a média do valor do débito para cada estado. 2. Listar o estado e a média do valor do débito para cada estado, quando esta média for superior a 100. 1 2 Linguagem de consulta de dados Exemplo no MySQL: 1. Listar o estado e o valor máximo do débito para cada estado onde este valor máximo for inferior a 700. 2. Listar o estado e o valor mínimo do débito para cada estado, onde este valor mínimo for superior a 0. 1 2 Linguagem de consulta de dados Operador BETWEEN: – Seleciona valores dentro de um intervalo. Exemplo no MySQL: – Listar os clientes cujo débito está entre 500 e 1000. Linguagem de consulta de dados Operador LIKE: – Compara partes de uma sequência de caracteres. – Atributo LIKE ‘%string%’: • % compara qualquer substring (número arbitrário de caracteres). – Atributo LIKE ‘_string_ _’ • _ compara qualquer caractere. – Comparação case-sensitive. Observação: alguns SGBDs usam * ao invés de %. Linguagem de consulta de dados Exemplo no MySQL: – Listar os dados onde o nome da cidade inicia com a letra ‘A’. – Listar os dados onde o nome do cliente termina com ‘Silva’. Linguagem de controle de dados Exemplo no MySQL: – Listar os dados onde, no endereço, exista a preposição ‘de’. Linguagem de consulta de dados Operadores aritméticos: são responsáveis pela execução de operações matemáticas simples. + Adição - Subtração * Multiplicação / Divisão Linguagem de controle de dados Exemplo no MySQL: Linguagem de consulta de dados Operadores relacionais: são utilizados quando precisamos fazer comparações entre dois valores. > Maior que < Menor que = Igual a <> Diferente de >= Maior ou igual a <= Menor ou igual a Conhecemos a linguagem de consulta de dados. Aprendemos alguns dos principais parâmetros e funções que podem ser usados para realizer a consulta de dados nas tabelas do banco de dados. Conclusão Aprender a realizar consultas em múltiplas tabelas. Próxima aula Aula 3.11.2. Linguagem de consulta de dados (Parte 2) Aprender a realizar consultas em múltiplas tabelas. Nesta aula Consultas Aninhadas (Nested Queries) Não correlacionadas – independentes: – Ex.: selecionar nome e nusp dos alunos com a idade mais alta. select nome, nusp from aluno where idade IN (select max(idade) from aluno) Consultas Aninhadas Nested Queries. Pode-se desenvolver comandos sofisticados a partir de comandos simples, utilizando-se subconsulta (subqueries). Sintaxe: SELECT <lista de colunas> FROM <tabela ou lista de tabelas> WHERE <coluna> operador subquery; Operadores: IN, NOT IN, EXISTs, NOT EXIST, ALL, SOME, ANY e os operadores aritimético (>, <, =, etc.). Consultas Aninhadas Considere as tabelas a seguir: DEPARTAMENTO FUNCIONARIO Consultas Aninhadas Exemplo no MySQL: 1. Quais os departamentos que não possuem funcionários cadastrados? 2. Quais os departamentos que possuem funcionários cadastrados? Consultas Aninhadas Exemplo no MySQL: 1. Quaisos funcionário que possuem salário acima da media do salário de todos os funcionários? Junções Junções (joins, em inglês) são consultas SQL usadas para recuperar dados de várias tabelas. As operações de junção tomam duas relações e retornam como resultado outra relação. Está relacionado ao conceito de FK, pois em geral, a junção se dá por meio dela. Junções Existem diferentes tipos de JOINs no SQL: – (INNER) JOIN: retorna registros que possuem valores correspondentes nas duas tabelas. – LEFT (OUTER) JOIN: retorna todos os registros da tabela a esquerda e registros correspondentes da tabela a direita. – RIGHT (OUTER) JOIN: retorna todos os registros da tabela da direita e os registros correspondentes da tabela da esquerda – FULL (OUTER) JOIN: retorna todos os registros quando houver uma correspondência na tabela esquerda ou direita. Junções Considere as tabelas a seguir: DEPARTAMENTO FUNCIONARIO (INNER) JOIN Retorna apenas as linhas em ambas as tabelas que possuam o campo de relacionamento com o mesmo valor. SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name LEFT (OUTER) JOIN Retorna todas as linhas da primeira tabela relacionada no join (esquerda). Quando a linha listada não possuir equivalência na tabela a direita, tais colunas aparecerão com valores nulos. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name RIGHT (OUTER) JOIN Retorna todas as linhas da tabela relacionada no join (direita). Quando a linha listada não possuir equivalência na tabela a esquerda, tais colunas aparecerão com valores nulos. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name FULL (OUTER) JOIN Retorna todas as linhas quando há uma correspondência nos registros da tabela esquerda ou direita. SELECT column_name(s) FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name Aprendemos a realizar consultas em múltiplas tabelas. – Conhecemos os diferentes tipos de junções (JOINS). – Entendemos como podemos usar subconsultas ou consultas aninhadas. Conclusão Usar o MySQL Workbench para realizar algumas consultas a dados, utilizando a linguagem DQQL da SQL. Próxima aula Aula 3.12. MySQL Workbench: DQL Usar o MySQL Workbench para realizar alguns comandos de consulta de dados. Nesta aula Conhecemos as funcionalidades do MySQL Workbench para consultar dados do banco de dados utilizando a linguagem DQL do SQL. Conclusão Conhecer a linguagem de controle de acesso a dados. Próxima aula Aula 3.13. Linguagem de controle de acesso a dados Conhecer a linguagem de controle de acesso a dados. Nesta aula Linguagem de controle de acesso a dados Data Control Language (DCL). Manter a segurança interna do banco de dados e seus objetos de dados, protegendo contra acessos não autorizados. Permite conceder e revogar permissões de acesso a objetos do banco de dados para usuários. – GRANT – Conceder. – REVOKE - Revogar. Criar usuário e papéis (roles): – CREATE USER/ROLE. – ALTER USER/ROLE. DCL – Definindo roles CREATE ROLE: cria uma nova role (papel). CREATE ROLE <role>[,<lista de roles>]; DROP ROLE: exclui uma role. DROP ROLE <role>[,<lista de roles>]; DCL – Definindo usuários CREATE USER: cria um novo usuário. CREATE USER <usuario>[@<hostname>] IDENTIFIED BY PASSWORD(<senha>)]; DROP USER: exclui um usuário. DROP USER <usuario>[@<hostname>]; DCL – Permissões GRANT: concede privilégios para usuário/role. GRANT <lista de privilegios> ON <objeto> TO <lista de usuários/roles> [WITH GRANT OPTION] [GRANTED BY grantor]; Lista de usuários e roles: pode ser um conjunto separado por vírgulas ou PUBLIC. O garantidor (grantor) e o usuário corrente (CURRENT_USER) ou o papel (CURRENT_ROLE), é opcional no comando. WITH GRANT OPTION Pode propagar a permissão. DCL – Permissões REVOKE: revoga privilégios para usuário/role. REVOKE [GRANT OPTION FOR] <lista de privilegios> ON objeto FROM <usuários/roles> [RESTRICT|CASCADE] ; DCL – Permissões Exemplo no MySQL: – Concedendo permissões para roles e usuários: DCL – Permissões Exemplo no MySQL: – Revogando permissões de roles e usuários: Conhecemos os principais comandos da linguagem de controle de acesso a dados. Conclusão Conhecer os conceitos fundamentais de data warehouse. Próxima aula