slides - IME-USP
Propaganda
BANCO DE DADOS EM BIOINFORMÁTICA Márcio K. Oikawa UFSCar [email protected] Palestrante • Professor da Universidade Federal de São Carlos – campus Sorocaba; • Formação na área de Ciência da Computação; • Atuação em vários projetos relacionados a Bioinformática e Biologia Molecular Computacional; • Material desta aula é baseado nos slides do prof. Luciano Vieira de Araújo (EACH-USP). Objetivos • Reconhecer as necessidades de um banco de dados; • Aprender a resolver problemas usando bancos de dados; • Explorar os recursos de ferramentas de bancos de dados relacionais; • Reconhecer oportunidades de aplicar isso a projetos de Bioinformática. Roteiro • Introdução • Motivação; • O que é banco de dados; • Quando usar e não usar banco de dados. • Um pouco sobre banco de dados • Modelo relacional; • Consultas SQL; • Data warehouse e Data mining. • Pontos importantes a serem considerados em um projeto de pesquisa • Aplicações em Bioinformática • Desafios e oportunidades de pesquisa em banco de dados aplicado à Bioinformática Dados – Informações – Conhecimento Os dados são elementos brutos, sem significado, desvinculados da realidade. "observações sobre o estado do mundo". Davenport, 1998. “descrição elementar”. Turban, 2007. As informações são dados com significado - resultado do encontro de uma situação de decisão com um conjunto de dados "São dados dotados de relevância e propósito" (Davenport, 1998). Dados organizados, com valor para o receptor (Turban, 2007) O conhecimento pode então ser considerado como a informação processada pelos indivíduos – entendimento, experiência, aprendizagem acumulada e prática. o "conhecimento é a informação mais valiosa (...) é valiosa precisamente porque alguém deu à informação um contexto, um significado, uma interpretação (...)". Davenport (1998) ANGELONI, Maria Terezinha.2003 Dados – Informações – Conhecimento O caminho da sociedade da informação para a sociedade do conhecimento é o caminho da informação para o significado, da percepção para o julgamento." MARKL, Hubert.,1998 Contexto Conhecimen to ANGELONI, Maria Terezinha. Elementos intervenientes na tomada de decisão. Ci. Inf. [online]. 2003, v. 32, n. 1, pp. 17-22. http://www.scielo.br/pdf/ci/v32n1/15969.pdf Vamos começar do começo! • O que é um banco de dados? Um conjunto de dados qualquer. Banco de dados simples • Para que serve um banco de dados? • Guardar; • Lembrar; • Proteger; • Zelar (garantir a qualidade). • Este é um bom banco de dados? História do banco de dados • Evolução do processamento e armazenamento dos dados Gerenciamento de dados - História PROGRAMA 1 Gerenciamento dos dados PROGRAMA 2 Gerenciamento dos dados DATA SET 1 DATA SET 2 • Dados não armazenados nos discos • Programador define tanto a estrutura lógica quanto física dos dados (estrutura de armazenamento, métodos de acesso, modos de I/O, etc) • Um conjunto de dados por programa • Alto nível de redundância Processamento de arquivos - história(recente e atual) PROGRAMA 1 PROGRAMA 2 Gerenciamento dos dados PROGRAMA 3 Serviços de File System Arquivo 1 Arquivo 2 dados redundantes Gerenciamento dos dados Gerenciamento dos dados • Dados armazenados em arquivos com interface entre o programa e o arquivo • Vários métodos de acesso disponíveis: • Seqüencial, indexado, randômico, etc • Um arquivo utilizado por vários programas Funções do Sistema de Arquivos • Mapeamento entre arquivos lógicos e físicos • Arquivos lógicos: arquivo visto pelos usuários e programas • Programa manipula arquivos lógicos • Arquivos físicos: arquivo realmente como armazenado em um dispositivo • Sistema operacional manipulam os arquivos físicos • Um conjunto de serviços e uma interface PROGRAMA 1 PROGRAMA 2 Gerenciamento dos dados PROGRAMA 3 Gerenciamento dos dados Serviços de File System Arquivo 1 Arquivo 2 dados redundantes Gerenciamento dos dados Problemas com Sistemas de Arquivos • Dados altamente redundantes • Compartilhamento limitado e em nível de arquivo • Alto custo de manutenção • Dependência dos dados • Para garantir a consistência e controle de acesso • Granularidade de compartilhamento • Dificuldades no desenvolvimento de novas aplicações A abordagem dos Bancos de Dados PROGRAMA 1 SGBD Base de Dados Integrada PROGRAMA 2 Processador de consultas Gerenciador de transações ... O que é um SGBD? • Uma grande coleção integrada de dados • Modelos do mundo real • Entidades (ex. estudantes, cursos) • Relacionamentos (ex. Sivia está cursando PRBD) Um Sistema de Gerenciamento de Banco de Dados (SGBD) é um pacote de software designado para guardar e gerenciar um banco de dados Estrutura do sistema users Forms PGMs Application Front ends DBA DML/CLI interface DDL SQL commands Transaction & Lock Manager Query Evaluation Engine File & Access Methods Buffer Manager Disk Space Manager indexes catalog data files Recovery Manager SGBD DDL Compiler Um banco de dados oferece • Consistência • Eficiência • Controle de restrições • • • • Chave, Tipo de dados, Chave estrangeira, Unicidade, • Possível qualidade de dados • Como assim ? Quando não usar banco de dados • Existe algum caso ? Porque usar um SGBD? • Os dados constituem um bem para a organização e • • • • • • • requerem controle integrado Acesso independente e eficiente a dados Redução no tempo de desenvolvimento da aplicação Integridade e segurança dos dados Administração uniforme dos dados Acesso concorrente (compartilhamento) Recuperação de “crashes” Produtividade para o programador Independência dos dados Porque estudar Bancos de Dados? • Mudança da computação para a informação • Mundo heterogêneo – Diferentes tipos de aplicações e complexidade • Conjuntos de dados aumentando em diversidade e volume • Bibliotecas digitais, projeto do genoma humano, uso da informação para o processo de tomada de decisões das empresas (competitividade) • Necessidade de bancos de dados cada vez maiores • SGBD permeia a maior parte da computação: • SOs, linguagens, lA, multimidia, etc.... SGBDs • Comerciais • • • • • Oracle Sybase Sql server DB2 Access • Gratuitos • Mysql • Postgresql • Hsqldb Tipos de banco de dados • Quanto ao processamento de transações • • • • Centralizados • Distribuídos Modelo de dados • Relacional • Orientado a objetos Especialização • Geográficos • Temporais Natureza dos dados • Produção • Analítico (Data warehouse) Desafios da Bioinformática • • • • • Conceitos em constante mudança Modelagem e gerenciamento dos dados Integração de banco de dados Rastreabilidade de dados Qualidade de dados Exercício • Desenhe um banco de dados para atender um sistema de controle acadêmico (geração de histórico escolar) que contemple: • • • • Dados sobre os alunos Dados sobre as diciplinas Dados sobre os professores Seus relacionamentos (qual a nota de cada aluno nas disciplinas que cursa/cursou, qual professor ministra cada disciplina etc.) Discussão: quais as dificuldades encontradas? Modelo de dados • Um modelo de dados é uma coleção de conceitos para descrever dados • Um esquema é uma descrição de uma coleção particular de dados, usando algum modelo de dados Modelo de dados • O modelo de dados relacional é o modelo mais usado hoje • Principal conceito: relação, basicamente uma tabela com linhas e colunas • Toda relação tem um esquema, que descreve as colunas, ou campos • É um formalismo que define qual a estrutura dos dados • Dentro de um arquivo • Entre arquivos Níveis de Abstrações • Muitas visões simples, esquema conceitual (lógico) e esquema físico • Visões descrevem como usuários vêem o dado • Esquema conceitual define a estrutura lógica • Esquema físico descreve o arquivo e índices usados Visão 1 ... Esquema conceitual Esquema físico Visão N Características dos SGBDs • Independência dos dados: • Aplicações isoladas da maneira que os dados são estruturados e armazenados • Invisibilidade ou transparência dos detalhes para o usuário (organização, estrutura de armazenamento e estratégia de acesso) • Transparência da estratégia lógica de acesso • Transparência da organização física do armazenamento e acesso Características dos SGBDs • Esquema integrado • Usuários têm uma visão uniforme dos dados • Usuários vêem apenas relações no modelo relacional • Integridade declarativa e garantia de consistência • 1.000 Salário 10.000 • Nenhum empregado pode ter um salário maior que seu/sua gerente • O usuário especifica e o sistema garante • Visões individualizadas • Restrições a certas relações • Reorganização das relações por certas classes de usuários Características dos SGBDs • Acesso declarativo • Linguagem de consulta – SQL • Esquemas são definidos usando uma DDL • Dados são modificados/consultados usando uma DML • O sistema determina a execução • Processador de consultas e otimizador • Controle de concorrência Controle de concorrência • Execução da concorrência de processos é essencial para uma boa performance do SGBD • Acessos a discos são freqüentes e lentos, é importante deixar a CPU alocada a diversos processos concorrentemente • Executar ações de diferentes processos pode levar a contradições • Ex.: o cheque compensado enquanto o saldo é computado • O SGBD evita tais problemas: usuários podem fingir que estão usando um sistema único Transações • Execução das solicitações do usuário como uma seqüência atômica de ações no BD (unidade de leituras/gravações) • Pode conter uma ou múltiplas consultas • Cada transação, executada completamente, deve deixar o BD em um estado consistente (se o BD é consistente quando a transação começa) • Usuários podem especificar alguma restrições • O SGBD não entende a semântica dos dados Transações • Transparência na concorrência: • Múltiplos usuários podem acessar o banco de dados, porém com uma visão pessoal • Controle de concorrência • Transparência nas falhas: • Mesmo que ocorra uma falha, a consistência da base de dados é garantida • Mecanismos de logging e recuperação SGBD – Sistemas gerenciadores de banco de dados Exemplos de dado em um banco de dados O lugar do SGBD no sistema Programas de aplicação Ferramentas de desenvolvimento SGBD Sistema Operacional Hardware Usuários do banco de dados • Usuário final • Usuário “leigo” ou casual • Acessa o BD através de forms ou através de front ends de aplicações • Os mais sofisticados geram consultas adhoc usando DML • Programadores de aplicação/desenvolvedores • Desenham e implementam aplicações que acessam o BD (algumas podem ser utilizadas pelos usuários finais) Usuários do banco de dados • Administradores de bancos de dados (DBA – database administrator) • Define e gerencia o esquema conceitual • Define as visões para as aplicações e usuários • Monitora e ajusta a performance do SGBD (define/modifica esquema interno) • Carrega e controla a base de dados • Responsável pela segurança e confiabilidade O Modelo Entidade-Relacionamento O Modelo de dados EntidadeRelacionamento (ER) • Descreve os dados do mundo real como objetos e seus relacionamentos • Amplamente utilizado para o desenho inicial da base de dados • Descrição informal descrição detalhada e precisa que pode ser implementada em um DBMS O Processo de Desenho do BD 1. Análise de requerimentos ou necessidades 2. Desenho conceitual do BD 3. Desenho lógico do BD 4. Refinamento do esquema 5. Desenho físico do BD 6. Desenho da segurança MER - necessário principalmente nas etapas 2 e 3 O Projeto do BD • O projeto de um novo BD dá-se em duas fases: 1. Modelagem Conceitual: o modelo conceitual é construído na forma de um diagrama entidaderelacionamento 2. Projeto Lógico: define como o banco de dados será implementado em um SGBD específico passando pelo refinamento do esquema (normalização), onde verifica o esquema relacional para redundâncias e anomalias Exemplo prático • Estudo de caso... Entidade • Conjunto de elementos (objetos) do sistema que possui vida própria e sobre os quais se deseja armazenar informações no banco de dados. Relacionamento p1 p7 p3 p8 p4 p2 p6 p5 p4,d2 p1,d1 d1 p5,d3 p2,d1 Entidade EMPREGADO d2 d3 Relacionamento LOTAÇÃO Entidade DEPARTAMENTO Obs: o relacionamento não necessariamente associa entidades diferentes. Pode ocorrer um auto-relacionamento Classificação de relacionamentos binários • Exemplos: relacionamentos 1:1 EMPREGADO PESSOA 1 1 marido esposa CASAMENTO 1 ALOCAÇÃO 1 (Relacionamento binário que envolve apenas uma entidade) MESA Classificação de relacionamentos binários • Exemplos: relacionamentos 1:n n ALUNO 1 INSCRIÇÃO CURSO EMPREGADO 1 EMPREGADO n DEPENDENTE 1 supervisor n supervisionado SUPERVISÃO Classificação de relacionamentos binários • Exemplos: relacionamentos n:n n MÉDICO n PACIENTE CONSULTA n PEÇA n FORNECEDOR CAPACIDADE PRODUTO n n n composto componente COMPOSIÇÃO ENGENHEIRO n ALOCAÇÃO PROJETO Exercício • Explique cada caso de cardinalidade n 1 1 1 n n Cardinalidade de Relacionamentos Cardinalidade (mínima, máxima) de entidade em relacionamento = 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 Cardinalidade de Relacionamentos DEPARTAMENTO LOTAÇÃO 1 Expressa que a uma ocorrência de EMPREGADO (entidade do lado oposto da anotação) pode estar associada ao máximo uma (“1”) ocorrência de DEPARTAMENTO n EMPREGADO Expressa que a uma ocorrência de DEPARTAMENTO (entidade do lado oposto da anotação) podem estar associadas ao máximo muitas (“n”) ocorrências de EMPREGADO Cardinalidade Mínima • Representa o número mínimo de ocorrências de entidade que são associadas a uma ocorrência de uma entidade através de um relacionamento • Para o projeto de BD considera-se apenas as cardinalidades mínimas 0 e 1 Cardinalidade Mínima • A cardinalidade mínima 1 também é chamada de “associação obrigatória”, pois indica que o relacionamento deve obrigatoriamente associar uma ocorrência de entidade a cada ocorrência da entidade em questão • A cardinalidade mínima 0, seguindo o mesmo raciocínio, recebe a denominação de “associação opcional” Cardinalidade Mínima Exemplo: e1 e3 EMPREGADO e4 e2 (0,1) e3,m6 ALOCAÇÃO e1,m1 e4,m4 e2,m2 (1,1) MESA m6 m1 m2 m5 m3 m4 Cada empregado dever ter a ele alocada obrigatoriamente uma mesa (cardinalidade mínima 1) e que uma mesa pode existir sem que a ela esteja alocado um funcionário (cardinalidade mínima 0) Exemplo de uso de entidades e relacionamentos DER para o controle acadêmico de uma universidade: PRÉ-REQUIS DEPARTAMENTO (1,1) RESPONSÁVEL liberada liberadora (0,n) (0,n) (0,n) DISCIPLINA (0,n) DISC-CURSO (0,n) ALUNO (0,n) INSCRIÇÃO (1,1) CURSO Explique o significado das cardinalidades 1) cursos alunos matricula 0,n 0,n 2) cursos alunos matricula 0,n 1,n cursos alunos 3) matricula 0,1 cursos alunos 4) 0,n matricula 0,1 1,1 Atributos atributo = dado que é associado a cada ocorrência de uma entidade ou de um relacionamento • O modelo ER permite a especificação de propriedades de entidades, como: • participar de um relacionamento • possuir atributo(s) Atributos • São representados conforme diagrama abaixo: PROJETO tipo código nome • Na prática, atributos não são representados graficamente, para não sobrecarregar os diagramas. Pode-se utilizar uma representação textual dos atributos Atributos • Exemplo: atributo de relacionamento n:n (0,n) ENGENHEIRO (0,n) ATUAÇÃO PROJETO Função Código Nome Código Título Obs: um engenheiro pode atuar em diversos projetos, exercendo diferentes funções Identificando entidades • Cada entidade deve possuir um identificador • Identificador é um conjunto de um ou mais atributos (e possivelmente relacionamentos) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade Identificando entidades • No exemplo abaixo, o atributo código é identificador • Cada pessoa possui um código diferente Código PESSOA Nome Endereço • O atributo código é um identificador simples Identificando entidades • Exemplo de identificador composto: número do corredor PRATELEIRA número da prateleira capacidade • considera-se o almoxarifado de uma empresa de ferragens • os produtos ficam armazenados em prateleiras • estas prateleiras encontram-se em armários organizados em corredores • para cada prateleira deseja-se saber sua capacidade em metros cúbicos Identificando entidades • O identificador de uma entidade (simples ou composto, ou composto por identificadores externos) deve obedecer duas propriedades: • o identificador deve ser mínimo - retirando um dos atributos ou relacionamentos que o compõe, ele deixa de ser identificador. • Exemplo: Código PESSOA Nome Endereço código+nome poderia ser usado como identificador mas não atende ao requisito descrito acima, pois não formam um identificador mínimo (código é suficiente para distinguir as ocorrências de PESSOA) Esquemas gráficos de modelos ER • Símbolos usados na construção de esquemas ER: Entidade Relacionamento identificador (entidade fraca) (1,1) Relacionamento Generalização/especialização Atributo Entidade associativa (agregação) Atributo identificador SQL – Structured Query Language SQL STRUCTURED QUERY LANGUAGE • Semelhante ao inglês falado • Fácil de escrever, ler e entender Componentes do SQL • DDL - Data Definition Language • • • CREATE • ALTER • DROP DML - Data Manipulation Language • SELECT • UPDATE • INSERT • DELETE DCL – Data Control Language • GRANT • REVOKE Exemplo de um banco de dados para pacientes pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostras Regiao_Genoma Tamanho Fasta • Tabela (conjunto de registros/linhas/tuplas com os mesmos atributos) INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Relacionamento entre tabelas Pacientes Id_Paciente Idade Sexo Cidade Pais I <M> I 1,1 A1 A100 A100 Sequencia Amostras Id_Amostra Possui 0,n Data Pais_Coleta Origem_Amostra pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) I <M> D 0,1 A100 A100 amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) Origina Id_Sequencia Regiao_Genoma 0,n Tamanho Fasta I <M> A100 I LBIN sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Conteúdo das tabelas Id_Paciente Idade Paciente Amostra Id_Amostra Sexo o Cidade Pais 1 15 M São Paulo Brasil 2 28 F São Paulo Brasil 3 19 M Campinas Brasil Id_Paciente Data Pais origem Origem (FK) Sequencia Id_Sequencia 1 2 01/01/99 Brasil Sangue 2 3 05/03/99 Françae Pele 3 2 07/08/99 Brasil Sangue Id_Amostra Tamanho Fasta (FK) Regiao genoma 1 2 Pol 200 ACCTTAT...AACT 2 1 Gag 300 TCATTAA...CACA 3 2 Env 250 CCCTTCA...TACG Criar Tabela - Create Tabela Sintaxe do comando: Create Table Nome da Tabela ( Coluna1 Tipo de dado [ Not Null] [Primay Key], Coluna2 Tipo de dado [ Not Null], ... ColunaN Tipo de dado [Not Null] ); [] – Indica termo opcional Tipos de dados • char[(n)] – Sequência de caracteres com tamanho fixo. n indica a quantidade de caracteres. Tamanho ocupado n bytes. • int – Inteiro – Valores possíveis de -231 (-2,147,483,648) até 231 (2,147,483,647). Tamanho ocupado 4 bytes. • smallint – Inteiro de -215 (-32,768) até 215 - 1 (32,767). Tamanho ocupado 2 bytes. • datetime – Armazena data e horário de Janeiro. Tamanho ocupado 8 bytes • text – Sequência de caracteres de tamanho variado. Criar Tabela - Create Tabela pacientes Id_Paciente Idade Sexo Cidade Pais INT8 <pk> INT2 CHAR(1) VARCHAR(100) varchar(100) Create Table Pacientes ( Id_Paciente int8 not null primary key, Idade int2, Sexo char(1), Cidade varchar(100), Pais varchar(100)) Criar tabelas com relacionamentos pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT O relacionamento entre duas tabelas é indicado pela restrição de integridade chamada Chave Estrangeira (Foreign Key). Constraint Nome_da_restricao ConstraintType (Nome_atributo ) References Nome_tabela (Nome_atributo) Constraint Fk_IdPaciente Foreign Key (Id_Paciente ) References Paciente (Id_Paciente) Conteúdo das tabelas Paciente Amostra Id_Paciente Idade Id_Amostra Sexo o Cidade Pais 1 15 M São Paulo Brasil 2 28 F São Paulo Brasil 3 19 M Campinas Brasil Id_Paciente Data Pais origem Origem (FK) Sequencia Id_Sequencia 1 2 01/01/99 Brasil Sangue 2 3 05/03/99 Françae Pele 3 2 07/08/99 Brasil Sangue Id_Amostra tamanho Fasta (FK) Região do genoma 1 2 Pol 200 ACCTTAT...AACT 2 1 Gag 300 TCATTAA...CACA 3 2 Env 250 CCCTTCA...TACG Criar tabelas com relacionamentos pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Create Table amostras ( Id_Amostra int not null primary key, Id_Paciente int, Data DateTime, Indica que a tabela amostras possui relacionamento com a tabela pacientes Pais_Coleta varchar(100), Origem_Amostra varchar(100), Constraint Fk_IdPaciente Foreign Key (Id_Paciente) References pacientes (Id_Paciente) ) Criar tabelas com relacionamentos pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta Create Table Sequencias ( Id_Sequencia int not null primary key, Id_Amostra int, Regiao_Genoma varchar(100), Tamanho int, Fasta Text, Constraint Fk_IdSequencia Foreign Key (Id_Amostra ) References Amostras (Id_Amostra) ) INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Inserir dados nas tabelas pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta • Comando para inserir dados: INSERT INTO Nome_Tabela (Col 1, ..., Col N) • Example: VALUES (Val 1, ..., Val N) insert into Paciente (Id_Paciente, idade, Sexoo, cidade, pais) values (1, 18,‟M‟, „São Paulo‟,„Brasil‟) INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Inserir dados - tabela Amostras pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Comando para inserir dados: INSERT INTO Nome_Tabela (Col 1, ..., Col N) VALUES (Val 1, ..., Val N) • Exemplo: insert into amostras (Id_Amostra,Id_Paciente, Data,Pais_Coleta, Origem_Amostra) values (1, 1,‟25/03/02‟, „Brasil‟,„Sangue‟) Insert into – Tabela Sequencias pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Example: insert into Sequencia (Id_Sequencia, Id_Amostra,Regiao_Genoma, Tamanho, Fasta) values (1, 1,‟Env‟, 200, „>AA0001 Paciente1 Amostra1 ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA ACTGAATCGAACTGAATCGAACTGAATCGAACTGAATCGA‟) Entrada de de dados • Além do Insert, existem outros comeos para entrada de dados. Ex.: Copy, Select into, etc • Uma interface para entrada de dados torna o processo mais simples e transparente. INSERT INTO Nome_Tabela (Col 1, ..., Col N) VALUES (Val 1, ..., Val N) Paciente Id_Paciente Idade Sexoo Cidade Pais 1 15 M São Paulo Brasil 2 28 F São Paulo Brasil 3 19 M Campinas Brasil Consulta de dados - Select SELECT <lista de atributos> FROM <lista de tabelas> [WHERE <condições>] [ ] – Indica termo opcional Consultar os dados inseridos SELECT * FROM Nome_Tabela Indica todos os campos da tabela SELECT * FROM Pacientes SELECT * FROM Amostras SELECT * FROM Sequencias Evite erros de sintaxe • Use virgulas para separar nomes de tabelas e atributos . , , Select Id_Paciente Idade Sexo Select Id_Paciente Idade Sexo • Use aspas simples para indicar valores alfanuméricos „ ‟ Where Cidade = São Paulo Where Cidade = São Paulo Diferença entre símbolos Normal = SQL Simbolos <> >= <= Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre Identificador, Idade, Sexo e Cidade de todos pacientes. SELECT <Lista_Atributos> SELECT Id_Paciente, Idade, Sexo, Cidade FROM <Lista_tabelas> FROM Pacientes [WHERE <Condições>] Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre Idade e Cidade de todos pacientes com Idade menor que 17 anos. SELECT <Lista_Atributos> FROM <Lista_tabelas> [WHERE <Condições>] SELECT Idade,Cidade FROM Pacientes WHERE Idade > 17 Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre Idade e Cidade de todos os pacientes do sexo feminino com Idade menor que 17 anos de idade. SELECT <Lista_Atributos> FROM <Lista_tabelas> [WHERE <Condições>] SELECT Idade,Cidade FROM Pacientes WHERE Idade > 17 and Sexo = ‘F’ Junção - Consultar mais de uma tabela pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • A junção de tabelas é representada na clausula Where Tabela1.Atributo_Junção= Tabela2. Atributo_Junção • Exemplo: Pacientes.Id_Paciente = Amostras.Id_Paciente Consultas básicas em SQL - Tabela Join pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre o pais do paciente que possui a amostra com identificador 2. SELECT Paciente.Pais FROM Pacientes, Amostras WHERE Amostras.Id_Amostra = 2 and Pacientes.Id_Paciente = Amostras.Id_Paciente SELECT <Lista_Atributos> FROM <Lista_tabelas> [WHERE <Condições>] Condição de junção Consultas básicas em SQL - Join pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre o sexo do paciente, pais onde a amostra foi coletada e região do Genoma das sequências que possuem tamanho menor que 200 bp SELECT Pacientes.Sexo, Amostras.Pais_Coleta, Sequencia.Regiao_Genoma FROM Pacientes, Amostras, Sequencias WHERE Sequencias.Tamanho > 200 and Pacientes.Id_Paciente = Amostras.Id_Paciente and Amostras.Id_Amostra = Sequencias.Id_Amostra Consultas básicas em SQL - Join pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre o Sexo do paciente, pais de coleta da amostra e Região do Genoma das sequências cujo tamanho seja menor que 200 bp SELECT P.Sexo, S.PaisOrigin, Se.Regiao_Genoma FROM Pacientes P, Amostras S, Sequencias Se WHERE Se.Tamanho > 200 and P.Id_Paciente = S.Id_Paciente and S.Id_Amostra = Se.Id_Amostra P, S, Se São os apelidos das tabelas Consultas básicas em SQL – Order by pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Como mudar a ordem dos dados que aparecem no resultado de uma consulta?. SELECT FROM WHERE ORDER BY Idade,Cidade Pacientes Idade > 17 Idade Ordem numérica SELECT FROM WHERE ORDER BY Idade,Cidade Pacientes Idade > 17 Cidade Ordem alfabética Exercicíos - Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre Idade e Pais de todos pacientes do sexo masculino. SELECT <Lista_Atributos> FROM <Lista_tabelas> [WHERE <Condições>] SELECT Idade,Pais FROM Pacientes WHERE Sexo = ‘M’ Exercícios - Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre todas Sequencias (FASTA ) da região do genoma chamada Pol e cujo Tamanho seja menor que 200 bp SELECT <Lista_Atributos> FROM <Lista_tabelas> [WHERE <Condições>] SELECT Fasta FROM Sequencias WHERE Tamanho > 200 and Regiao_Genoma = ‘Pol’ Exercícios - Consultas básicas em SQL pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Encontre a cidade e idade dos pacientes cujas amostras foram coletadas no dia 01/03/99, mostre o resultado em ordenado pela idade do paciente SELECT <Lista_Atributos> SELECT FROM <Lista_tabelas> FROM [WHERE <Condições>] Paciente.Cidade, Paciente.Idade Pacientes, Amostras WHERE Amostras.Date = ‘01/03/99’ and Pacientes.Id_Paciente = Amostras.Id_Paciente ORDER BY Pacientes.Idade Remover dados das tabelas pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Comando: Delete from Nome_Tabela [Where <Condições>] Atenção!! Sempre é perigoso remover dados. Preste atenção nas condições do comando “delete”. • Exemplo: Delete from Pacientes Remove todos os dados da Tabela Pacientes. Remover dados das tabelas pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) • amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT Exemplo: Delete from Sequencias where Tamanho > 200 Remove da Tabela Sequencias todas as sequências com Tamanho > 200 Remover tabela do banco de dados pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Comando: Drop Table Nome_Tabela Atenção!! Sempre é perigoso remover dados Preste atenção nas condições do comando “delete”. • Example: Drop Table Pacientes; Remove a Tabela Paciente. Remover tabela do banco de dados pacientes Id_Paciente INT8 <pk> Idade INT2 Sexo CHAR(1) Cidade VARCHAR(100) Pais varchar(100) amostras Id_Amostras Int8 <pk> Id_Paciente INT8 <fk> FK_AP Data DATE Pais_Coleta VARCHAR(100) Origem_Amostra Varchar(100) sequencias Id_Sequencia Id_Amostra FK_S_A Regiao_Genoma Tamanho Fasta INT8 <pk> Int8 <fk> VARCHAR(100) INT2 TEXT • Ordem para remover as tabelas do banco de dados: 1) Drop Table Sequencias; 2) Drop Table Amostras; 3) Drop Table Pacientes; A restrição de integridade não permite a Remoção de dados usados por outras tabelas. Data warehouse Alguns Conceitos • • • • Características dos Sistemas Analíticos OLTP X OLAP Data Warehouse e Data Marts Bancos de Dados Dimensionais • Terminologia • Formas de Armazenamento • Data Mining • Tarefas Típicas • Algoritmos • Modelos OLTP X OLAP OLTP • • • • • • • • Mais freqüentes Detalhes Poucos dados Dados atuais Baixa complexidade Consistência microscópica CRM, ERP, Supply Chain Linhas e Colunas OLAP • • • • • • • • Menos freqüentes Agrupamento Muitos dados Dados históricos Alta complexidade Consistência global MIS Dimensões e Fatos Transformando dados em conhecimento Dados Informação Network Conhecimento Transformando dados em conhecimento Data Warehouse Dados Operacionais Produção ETL Relacional Data Marts BD Especial Cubes O modelo dimensional PRODUTO LOJA produto_key produto_desc tipo categoria loja_key loja_desc loja_endereço vendedor cidade estado região FATO_VENDA TEMPO tempo_key data mês mês_desc trimestre ano produto_key data_venda_key loja_key cliente_key valor margem custo quantidade • Chaves primárias • Hierarquias • Atributos descritivos CLIENTE cliente_key código_cliente nome Online Analytical Processing (OLAP) Exemplo Dimensões: Produto, Região, Tempo O Caminhos de Sumarização Hieráquica Produto S N Suco Cola Leite Creme Cadeira Sabonete 1 2 34 5 6 7 Mês Produto Região Tempo Indústria País Ano Categoria Região Trimestre Produto Cidade Mês Semana Escritório Dia Processo de KDD Cruzamento Armazenamento e recuperação de dados Data Mining • Data Warehouses / Data Marts – Oferecem fonte de dados e um conjunto de análise. • Data mining • Busca encontrar padrões novos/desconhecidos em um conjunto de dados • Não é um DW, o Data mining pode ser usado em um DW • Usa um conjunto de ferramentas estatístiva para encontrar e avaliar os padrões. Data mining – Tipos de Análises • Classificação • • • Busca relações entre os dados Previsão Análise de Desvio • • Agrupa casos semelhantes Associação • • • Previsão de Taxas de seguros, clima/temperatura Segmentação • • Análise de riscos (Potencial bom pagador) Regressão • • Atribui elementos a classes Identificação de fraudes Visualização • Representação visual dos padrões Ciclo da informação Armazenamento Coleta de dados Distribuição e o uso de dados Eficiência Gerencial (planejamento, controle, comunicação e tomada de decisão) Recuperação de dados APLICAÇÕES DE BANCO DE DADOS EM BIOINFORMÁTICA Pesquisas em HIV/AIDS • Como modelar um vírus? • Diferentes possibilidades de representação e relacionamento entre os dados Rambaut et al., Nature Reviews Genetics, 2004 Aplicações em Bioinformática • Portais com informações genéticas e ferramentas para análise de dados Aplicações em Bioinformática • Bancos de dados especializados • Microarray, plasmoDB • Especialização dos bancos – Protein Disease Database – SWISS-2DPIdade Aplicações em Bioinformática • Integração de dados de bancos de dados públicos • Importação de dados de diferentes fontes públicas • Esquemas de integração • Limitações • Escopo de dados abrangem somente seqüências genéticas e suas anotações • Qualidade dos dados disponíveis Dicas para o seu projeto Processo integrado • Quanto maior a capacidade das tecnologias da informação e da comunicação, maior a capacidade de interrelacionamentos e a capacidade de aprender e lucrar com o compartilhamento da informação e do conhecimento. • Tecnologia: comunicação e armazenamento dos dados, das informações e dos conhecimentos como na integração ANGELONI, Maria Terezinha.2003 dos tomadores de decisão. Ciclo de Vida de um projeto Orçamentos não resistem a erros • Tecnologia ágil • cliente e desenvolvedor juntos Ser realista • Definir prioridades Coleta de dados • Definição de quais dados devem ser coletados. • Avaliar se o dado é realmente importante • Qual o objetivo da coleta desse dado? • Em que tipo de análise o dado estará envolvido? • Qual a viabilidade de coletá-lo? • Com que qualidade/confiabilidade conseguiremos esse dado? Coleta de dados • Qual a representação natural do dado? • Alfanumérica, Númerica • Qual o tamanho do campo necessário para armazenar o dado? Ex.: 100 caracteres • Existe um padrão de medida/representação? • Ex.: ºC, Km/h, m, cm, mm Coleta de dados • Quais regras envolvem esse dado? • Faixa de valores. • Ex.: 0<x < 50 • Valores específicos. • Ex.: Dias da semana, Sexo, • Dependência de outras informações • Gravidez e licença maternidade só podem ser atribuídos em caso de Sexo feminino. • Regras de negócio • O exame deve ser coletado ate 24 horas após a internação. • Existem exceções ? • Qual a importância de avaliar tais regras? • Existe uma relação custo benefício favorável a validação das regras? Coleta de dados • Qual o melhor formato para armazenar o dado? • Sexo • (0 – feminino, 1 – masculino) • (f – feminino, m – masculino) • (feminino, masculino) • Número de medicamentos em uso • [0,10] • [0,100] • (nenhum, ate 2, entre 3 a 5, acima de 5) Coleta de dados • Idade ou data de nascimento? • Endereço • • • • Campo para digitação com 200 caracteres, Campo para digitação de rua, bairro, cidade, estado, pais Uso do CEP Campo para seleção de bairro, cidade, estado, pais Áreas para pesquisa Áreas de pesquisa • Criação de banco de dados para estudos específicos • Ontologias, modelagem de dados • Banco de dados flexíveis • Acompanham as modificações • Estruturas para representação e análise de dados • Data mining • Armazenamento/integração de dados secundários • Os resultados das análises integrados ao banco. Áreas de pesquisa • Integração de bases de dados existentes • Esquemas de integração de dados • Data warehouse • Banco de dados distribuídos • Consultas distribuídas • Transações distribuídas • Procedência de dados • Acompanhamento do dado e suas transformações • Rastrear a informação Migração de dados - procedência PD BD1 PA PC PB BD2 BD3 BD5 BD4 BD6 Ambiente integrado para descoberta de conhecimento em Biologia Computação de alto-desempenho Seleção de dados: OLAP e Relacional Métricas: Vetor de características Procedimento de projeto Processos de mining Transformação de dados Mundo Real Leitura de dados Ligação entre dados primários e objetos complexos Dados primários Base de dados relacional Conceito: Subconjunto de instâncias Base de dados de Objetos complexos Núcleo do mining Conclusão • Trate com cuidado do que é essencial • Os Dados • Softwares mudam e são trocados • Os dados permanecem • Desafios • • • • Representação Armazenamento Análise Evolução • Necessita de um profissional com: • Conhecimentos sólidos • E mais algumas habilidades… Entender o que é trabalhar em equipe Com Capacidade de Gestão Construção do Euro túnel Versátil Bibliografia Básica - ELMASRI, R. e NAVATHE, S.B. Sistemas de Banco de Dados, Fundamentos e Aplicações. Rio de Janeiro: Editora LTC, 2002. - Ramakrishnan R., Database Management Systems, WCB McGraw-Hill, 2002. FERREIRA, J. E. e FINGER, M. Controle de Concorrência e Distribuição de Dados: a teoria clássica, suas limitações e extensões modernas. São Paulo: Escola Brasileira de Computação, 2001. EfraimTurban, R. Rainer Jr, Richard Potter, Introdução a Sistemas de Informação – Uma abordagem gerencial. Campus – 2007 Fiddy, S., et al., An integrated system for genetic analysis. BMC Bioinformatics, 2006. 7: p. 210. Shah, S.P., et al., Atlas - a data warehouse for integrative bioinformatics. BMC Bioinformatics, 2005. 6: p. 34. Shafer, R.W., Rationale and uses of a public HIV drug-resistance database. J Infect Dis, 2006. 194 Suppl 1: p. S51-8. Lee, T.J., et al., BioWarehouse: a bioinformatics database warehouse toolkit. BMC Bioinformatics, 2006. 7: p. 170. Buneman, P., S. Khanna, and W.-c. Tan, Why and Where: A Characterization of Data Provenance. Proc. of the Intl. Conf. on Database Theory (ICDT), 2001: p. 316-330. - - BANCO DE DADOS EM BIOINFORMÁTICA Márcio K. Oikawa UFSCar [email protected]
