Informática para Ciências e Engenharias (B) 2016/17iceb.ssdi.di.fct.unl.pt/1617/teoricas/T10/iceb_t10.pdf · Informática para Ciências e Engenharias (B) 2016/17 Teórica 10. l

Informática para Ciências e Engenharias (B)

2016/17

Teórica10

l  Introdução aos sistemas de bases de dados:

•  Interrogações mais complexas em SQL.

l  Simulação de modelos contínuos:

•  Integração de equações diferenciais.

•  Exemplo: reações químicas

17Maio2017 10:SQL(váriastabelas);SimulaçãomodelosconBnuos 2

Sumário

Queries com várias Tabelas

l  Geralmente as perguntas às base de dados envolvem mais de uma tabela.

l  Exemplo de query mais complexa

l  Quais os títulos dos filmes alugados pelo Artur Meireles?

l  Neste caso, a informação relevante está organizada em várias tabelas

•  Filmes •  Alugueres •  Clientes

17Maio2017 310:SQL(váriastabelas);SimulaçãomodelosconBnuos

l  Tabela de Filmes de uma base de dados DBvideos

Exemplo – Tabela de Filmes


l  Tabela de Clientes de uma base de dados DBvideos

Exemplo – Tabela de Clientes


l  A tabela Alugueres tem duas chaves externas:

•  FiIme_id, que a liga à tabela filmes

•  Cliente_id, que a liga à tabela clientes

•  Aluguer_id é a sua chave primária

Exemplo – Tabela de Alugueres


l  Quais os títulos dos filmes alugados pelo Artur Meireles?



Ligar 2 tabelas

l  Podemos unir tabelas usando a condição WHERE da instrução SELECT

•  O campo A da tabela1 deve ser igual ao campo B da tabela 2

•  Opcionalmente a lista de respostas pode vir ordenada pelo campo C

SELECT lista-campos FROM tabela1, tabela2 WHERE tabela1.campoA = tabela2.campoB ORDER BY campoC;



Query:

l  Quais os títulos dos filmes alugados?

•  As duas tabelas têm um campo Filme_id



Ligar 2 tabelas



db >> SELECT Filmes.Titulo ...>> FROM Filmes, Alugueres ...>> WHERE Filmes.Filme_id = Alugueres.Filme_id; Idade do Gelo ElizabethRegresso ao FuturoAliendb >>



Ligar 2 tabelas

l  Alternativa: Usar um JOIN

•  Se o nome do campo for igual nas duas tabelas basta indicar o campo, não é preciso indicar a tabela (e.g. id_tabela2)

SELECT lista-campos FROM tabela1 JOIN tabela2 USING(id_tabela2) WHERE condição extraORDER BY campoC;



Query (2 tabelas):



db >> SELECT Titulo...>> FROM Filmes JOIN Alugueres...>> USING (Filme_id); Idade do Gelo ElizabethRegresso ao FuturoAliendb >>



Query (2 tabelas):

l  Quais os títulos e datas de aluguer dos filmes alugados?


db >> SELECT Titulo, Alugueres.Data_aluguer...>> FROM Filmes JOIN Alugueres...>> USING (Filme_id); Idade do Gelo|05/10/14Elizabeth|05/12/14Regresso ao Futuro|05/13/14Alien|05/11/14 db >>



Query (2 tabelas):

l  Quais os títulos e datas de aluguer dos filmes alugados?

•  Nota: Não havendo ambiguidade pode omitir-se o nome da tabela.


db >> SELECT Titulo, Alugueres.Data_aluguer...>> FROM Filmes JOIN Alugueres...>> USING (Filme_id); Idade do Gelo|05/10/14Elizabeth|05/12/14Regresso ao Futuro|05/13/14Alien|05/11/14 db >>


Query (3 tabelas):

l  Quais os títulos dos filmes alugados e que pessoas os alugaram?



Query (3 tabelas):

l  Quais os títulos dos filmes alugados e que pessoas os alugaram?


db >> SELECT Titulo, Nome ...>> FROM Filmes, Alugueres, Clientes ...>> WHERE (Clientes.Cliente_id = Alugueres.Cliente_id ...>> AND Filmes.Filme_id = Alugueres.Filme_id Idade do Gelo|Joana Fonseca Elizabeth|Artur MeirelesRegresso ao Futuro|Marcelo Rebelo de SousaAlien|Pedro Passos Coelhodb >>


l  Por vezes pretendemos obter como resposta não o valor dos campos mas sim uma determinada “agregação” desses campos.

l  As agregações mais comuns são

•  Contagens (quantos registos satisfazem a query?

•  Somas (qual a soma/ produto dos campos seleccionados)

•  Nota: Não há produtos que podem ser obtidos com a conversão via logaritmos:

exp(sum(ln(*)))

•  Máximos e Mínimos (qual o maior/menor campo seleccionado)

•  Em campos de texto a ordem é lexicográfica


Queries com Agregações

Query (contagem - agregação):

l  Quantos filmes foram alugados?

l  Nota: Algumas variantes

db >> SELECT Count (Aluguer_Id) FROM Filmes; 4db >>

SELECT COUNT(campo) : conta não NULL no campoSELECT COUNT(*) : conta registos (linhas)SELECT COUNT(DISTINCT campo) : conta diferentes



Query (contagem + wild cards):

l  Quantos clientes têm Sousa no nome?


db >> SELECT Count(*) FROM Clientes ...>> WHERE Nome LIKE "%Sousa%”; 2db >>


Query (contagem - sem repetições):

l  Em quantas datas foram feitos alugueres?


db >> SELECT Count(Data_Aluguer)FROM Alugueres; 4db >> SELECT Count(DISTINCT Data_Aluguer)FROM Alugueres; 3db >>


Query (outras agregações – mínimo):

l  Qual a data do primeiro aluguer

l  Notaraimportânciadaformacomoseescrevemdatasdeformaapoderusarasfunçõesmax/min


db >> SELECT Min(Data_Aluguer) FROM Alugueres; 2013/11/11db >>


l  Equações diferenciais

•  É comum (e prático) especificar sistemas dinâmicos com equações diferenciais

•  Cinética de reações

•  Crescimento de micro-organismos

•  Propagação de epidemias

•  Basta quantificar a variação de cada variável em função do estado do sistema


Sistemas de Equações Diferenciais

l  Exemplo:

•  A + B → C

•  d[C]/dt = K [A] [B]

•  d[A]/dt = -K [A] [B]

•  d[B]/dt = -K [A] [B]

l  Não podemos calcular diretamente as concentrações

•  Só temos as derivadas e as condições iniciais.


Reacção Química Irreversível

l  Sabemos os valores das variáveis em t

•  E.g. concentrações

l  Sabemos as derivadas em t

•  E.g. equações da cinética

l  Estimamos variáveis em t+dt

•  com dt suficientemente pequeno podemos desprezar a variação das derivadas e assumir que a derivada é aproximadamente constante entre t e t+dt.


Método de Euler

l  Início, t=0

•  [A]0 [B]0 [C]0

l  Procedimento:

•  Usar valores em t para calcular derivada

•  Usar derivada e valores em t para estimar os valores em t+dt:

•  [A]t+dt = [A]t + [A]'t * dt

A + B → C


ValorCorrente

PróximoValor

DerivadaCorrente


l  A simulação de uma reação química da forma A+B → C, pode ser efectuada por integração da respectiva equação diferencial.

l  Para esse efeito deverão ser dados inicialmente •  cis – vector concentrações iniciais de A, B e C •  dt – passo da simulação •  tmax – tempo final da simulação •  k – constante cinética da reacção

l  Sendo obtido •  tcs – concentrações dos componentes A, B e C ao longo do

tempo, i.e. no intervalo 0 .. Tmax.



l  A implementação genérica da simulação pode ser especificada pela função com assinatura e documentação indicadas abaixo:


function tcs = reacabc(cis,dt,tmax,k) % tcs = reacabc(cis,dt,tmax,k) % Simula a reaccao A+B -> C. Devolve uma matriz com % as concentracoes em funcao do tempo. Recebe as % concentracoes iniciais, o passo, o tempo maximo % e a constante cinetica da reaccao. ... end



function tcs = reacabc(cis,dt,tmax,k) % ... tcs = zeros(round(tmax/dt),4); % dimensiona a matriz de saída ix = 1; concs = cis; tcs(ix,:)=[0,concs]; % valores em t=0 % ciclo para calcular as concentrações ao longo do tempo (até tmax) for t = dt : dt : tmax % producao de C no intervalo resultante da reação de A com B abk = concs(1)* concs(2)* k * dt; %velocidade da reaccao * passo concs(1) = concs(1) – abk; % conc. de A no instante seguinte concs(2) = concs(2) - abk; % conc. de B no instante seguinte concs(3) = concs(3) + abk; % conc. de C no instante seguinte % as novas concentrações são guardadas na linha seguinte de tcs ix = ix + 1; tcs(ix,:) = [t, concs]; end end


l  A função pode ser testada, especificando os valores dos seus argumentos, como em:


>> V = [10, 20, 15, 35, 5]; >> cis=[1,1.5,0] >> dt = 0.1 >> tmax = 10 >> k=1 >> pontos = reacabc(cis,dt,tmax,k); >> X = pontos(:,1); >> ABC = pontos(:,2:columns(pontos))) >> id = figure; >> plot(X, ABC) >> legend ('A', 'B', 'C'); >> saveas(id,'abc.pdf');



l  resultando na figura ‘abc.pdf’


l  A simulação de uma reação química reversível da forma

A+B ⇔ C,

pode ser igualmente efectuada por integração da respectiva equação diferencial. Neste caso a velocidade da reação é modelada através da subtração das velocidades direta e inversa, dada pela equação

vel = [A] [B] kd - [C] ki

em que Kd e Ki são as constantes cinéticas das reações direta e inversa, respectivamente.


Reações Reversíveis

l  A simulação de uma reação química reversível pode ser generalizada tendo em conta as coeficientes de estequiometria dos reagentes e dos produtos. Por exemplo, para a reação

2A + 3B ⇔ C + 4D,

a velocidade da reação é modelada através da equação

vel = [A]2 [B]3 kd - [C]1 [D]4 ki

em que Kd e Ki são as constantes cinéticas da reação, direta e inversa, respectivamente.



l  Uma vez obtida a velocidade de reação a taxa de variação de cada reagente ou produto é obtida pela multiplicação dessa velocidade pelo seu coeficiente de estequiometria.

l  Por exemplo, no caso anterior 2A + 3B ⇔ C + 4D, vel = [A]2 [B]3 kd - [C]1 [D]4 ki

as variações dos reagentes são negativas (os reagentes são consumidos) e dadas por

dA/dt = -2 * vel e dB/dt = -3 * vel

enquanto que as variações dos produtos são positivas (os produtos são produzidos)

dC/dt = +1 * vel e dD/dt = +4 * vel



l  Na prática, as equações anteriores podem ser mais convenientemente implementadas através de operações vetoriais. Assim para a reação reversível

2A + 3B ⇔ C + 4D

com as concentrações dos produtos representadas pelo vetor concs = [A, B, C, D]

podemos considerar os seguintes vetores de coeficientes estequiométricos (para reagentes e produtos): eReag = [2,3,0,0] e eprod = [0,0,1, 4]

que permitem obter as velocidades e as derivadas dos vários reagentes e produtos através de operações vetoriais.



l  A velocidade da reação pode ser obtida através das concentrações dos reagentes e dos produtos elevados às suas estequiometrias. Dados

vel = [A]2 [B]3 kd - [C] [D]4 ki,

eReag = [2,3,0,0] e eprod = [0,0,1, 4]

temos

vel = kd*prod([A,B,C,D].êReag) – ki*prod([A,B,C,D].^ eProd)

= kd*prod([A,B,C,D].^[2,3,0,0]) – ki*prod([A,B,C,D].^ [0,0,1,4])

= kd*prod([A2,B3,C0,D0] – ki*prod([A0,B0,C1,D4])

= kd * A2 * B3 – ki * C1 * D4



l  Exemplo:


>> concs = [3, 2, 5, 2]; >> eReag = [2, 3, 0, 0]; >> eProd = [0, 0, 1, 4]; >> kd = 1/3; >> ki = 1/4; >> vr = concs .êReag ve = 9 8 1 1 >> vp = concs .^Prod vp = 1 1 5 16 >> prodvr = prod(vr) prodve = 72 >> prodvp = prod(vp) pve = 80 % velocidade = velocidade direta – velocidade inversa >> vel = kd * prodvr – ki * prodvp vel = 4 % 72/3 – 80/4


l  A variação (derivada) da concentração de cada reagente e produto da reação pode ser obtida através das multiplicação dos seus índices de estequiometria pela velocidade da reação obtida anteriormente. No exemplo da reação anterior

2A + 3B ⇔ C + 4D temos os índices

eReag = [2,3,0,0] e eprod = [0,0,1, 4]

e portanto as derivadas das concentrações dA/dt = - 2*vel; dB/dt = - 3*vel; dC/dt = +1*vel; e dD/dt = +4*vel

podem ser obtidas num vetor deriv através da operação vetorial deriv = vel * eProd - vel * eReag



l  Exemplo:

l  Uma vez compreendidas estas operações vetoriais a simulação de uma reação reversível pode ser implementada de forma semelhante à estudada anteriormente.


>> concs = [3, 2, 5, 2]; >> eReag = [2, 3, 0, 0]; >> eProd = [0, 0, 1, 4]; >> ... % velocidade = velocidade direta – velocidade inversa >> vel = kd * prodvr – ki * prodvp vel = 4 % 72/3 – 80/4 >> deriv = vel * eProd – vel * eReag deriv = -8 -12 4 16 % 4 *[0 0 1 4] – 4 *[2 3 0 0]


l  A simulação de uma reação reversível, efectuada pelo método de Euler, requer os seguintes dados iniciais. •  eReag – coeficientes de estequiometria dos reagentes •  eProd – coeficientes de estequiometria dos produtos •  cis – vector concentrações iniciais dos reagentes e produtos •  kd – constante cinética da reação direta •  ki – constante cinética da reação inversa •  dt – passo da simulação •  tmax – tempo final da simulação

l  Sendo obtido •  tcons – matriz de concentração dos vários reagentes e produtos,

(um por linha) ao longo do tempo, i.e. no intervalo 0 .. Tmax.



l  A implementação genérica da simulação pode ser especificada pela função com assinatura e documentação indicadas abaixo, semelhante à anteriormente estudada:


function tConcs = cinetica(eReag, eProd, cis, kd, ki, dt, tmax) % simula uma reaccao quimica a partir dos coeficientes de % estequiometria, concentracoes iniciais, constantes % cineticas, passo de integracao e tempo da simulacao. % Devolve uma matriz com o tempo e as concentracoes. ... end



function tConcs = cinetica(eReag, eProd, cis, kd, ki, dt, tmax) % ... % cria a matriz e define o ponto inicial % a primeira coluna corresponde ao vetor dos tempos tConcs = zeros(round(tmax/dt),length(cis)+1); % cria a matriz ix = 1; concs = cis; tConcs(ix,:)=[0,concs]; % concentracao inicial no tempo = 0 % ciclo da simulação pelo metodo de Euler for t = dt : dt : tmax vel = prod(concs.êReag)* kd - prod(concs.êProd) * ki; deriv = vel * eProd – vel * eReag; concs = concs + deriv * dt; % calcula os valores seguintes ix = ix + 1; tConcs(ix,:) = [t, concs]; % guarda os valores seguintes end end


l  A função pode ser testada, especificando os valores dos seus argumentos, como em:


>> kd = 1; >> ki = 0.5; >> cis = [1,2,0]; >> er = [1,1,0]; % A + B <-> C >> ep = [0,0,1]; % A + B <-> C >> dt = 0.1; >> tmax = 5; >> pontos = cinetica(er,ep,cis,kd,ki,dt,tmax); >> id = figure; >> plot(pontos(:,1),pontos(:,2:columns(pontos))); >> saveas(id,'xyz.pdf');



l  resultando na figura ‘xyz.pdf’


l  A simulação de um sistema de reações químicas reversíveis pode ser obtida por generalização do caso de uma só reação.

l  Para isso deve usar-se duas matrizes de estequiometrias, com uma linha para cada reação.

l  Por outro lado, deverá obter-se um vetor (coluna) de velocidades, uma linha por cada reação.

l  A velocidade de cada reação é obtida como anteriormente, linha a linha.

l  Igualmente se generaliza a derivada, como a soma das derivadas obtidas em cada uma das reações.


Sistema de Reações Reversíveis

l  Os parâmetros usados num sistema de reações reversíveis, requer dados iniciais semelhantes mas generalizados a matrizes e vetores com várias linhas, uma por cada reação. •  esReag – matriz de coeficientes de estequiometria dos reagentes •  esProd – matriz de coeficientes de estequiometria dos produtos •  cis – vector concentrações iniciais dos reagentes e produtos •  kds – vetor de constantes cinéticas para cada reação direta •  kis – vetor de constantes cinéticas para cada reação direta •  dt – passo da simulação •  tmax – tempo final da simulação

l  Sendo obtido •  tcons – matriz de concentração dos vários reagentes e produtos,

(um por linha) ao longo do tempo, i.e. no intervalo 0 .. Tmax.



l  A implementação genérica da simulação pode ser especificada pela função com assinatura e documentação indicadas abaixo, semelhante à anteriormente estudada.


function tConcs = cinetica(esReag, esProd, cis, kds, kis, dt, tmax) % Simula um conjunto de reaccoes quimicas. Recebe matrizes com % coeficientes estequiometricos, concentracoes iniciais, constantes % cineticas, passo de integracao e tempo da simulacao. % Devolve uma matriz com o tempo e as concentracoes. ... end


l  A função pode ser implementada a partir do código anterior, fazendo-se as adaptações abaixo indicadas:



vel = prod(concs.êReag)* kd - prod(concs.êProd) * ki; deriv = vel * eProd – vel * eReag;

deriv = zeros(1,length(concs)); for r = 1:size(esReag,1) % para cada reação obter eReag = esReag(r,:); % os coeficientes dos reagentes eProd = esProd(r,:); % os coeficientes dos produtos kd = kds(r); % a constante cinética direta ki = kis(r); % a constante cinética inversa % obtendo-se a velocidade de cada reação vel = prod(concs.êReag) * kd -prod(concs.êProd) * ki; % a derivada é obtida por soma das derivadas das reações deriv = deriv + vel * eProd – vel * eReag; end

l  A inicialização das variáveis é idêntica à feita na função anterior, para uma só reação.


function tConcs = cineticas(esReag,esProd,cis,kds,kis,dt,tmax) % ... % cria a matriz de concentrações e define o ponto inicial % a primeira coluna corresponde ao vetor dos tempos tConcs = zeros(round(tmax/dt),length(cis)+1); % cria a matriz ix = 1; concs = cis; tConcs(ix,:)=[0,concs]; % concentracao inicial no tempo = 0 for t = dt:dt:tmax % ciclo da simulação pelo metodo de Euler ... end concs = concs + deriv * dt; ix = ix + 1; tConcs(ix,:)=[t, concs]; end


l  O ciclo de simulação inclui as adaptações já analisadas.


function tConcs = cineticas(esReag,esProd,cis,kds,kis,dt,tmax) ... for t = dt:dt:tmax % ciclo da simulação pelo metodo de Euler deriv = zeros(1,length(concs)); for r = 1:size(esReag,1) % para cada reação obter: eReag = esReag(r,:); % os coeficientes dos reagentes eProd = esProd(r,:); % os coeficientes dos produtos kd = kds(r); % a constante cinética direta ki = kis(r); % a constante cinética inversa % obtendo-se a velocidade de cada reação vel = prod(concs.êReag) * kd -prod(concs.êProd) * ki; % a derivada é obtida por soma das derivadas das reações deriv = deriv + vel * eProd – vel * eReag; end ... end


l  Tal como anteriormente, esta função pode ser testada, especificando os valores dos seus argumentos, para um sistema de reações.

l  Podemos exemplificar com um oscilador químico (Lotka), simples de modelar, através das reações (não reversíveis)

A + X → 2X X + Y → 2Y Y → Q


Oscilador químico (Lotka)

A + X → 2X X + Y → 2Y Y → Q


>> ers = [1 1 0 0; 0 1 1 0 ; 0 0 1 0]; % [A, X, Y, Q] >> eps = [0 2 0 0; 0 0 2 0 ; 0 0 0 1]; >> kds = [0.05,1,1]; >> kis = [0,0,0]; % reação não reversivel >> cis =[50,1,1,0]; >> dt = 0.01; >> tmax = 50; >> res = cineticas(ers,eps,cis,kds,kis,dt,tmax); >> id = figure; >> plot(res(:,1),res(:,2:columns(res))); >> legend ('A', ’X', ’Y', ’Q' ); >> saveas(id,’lotka_1.pdf');



l  resultando na figura ‘lotka_1.pdf’ A + X → 2X X + Y → 2Y Y → Q


l  Podemos realçar os produtos oscilantes (X e Y) apenas fazendo o gráfico da sua evolução, como testado abaixo.

A + X → 2X X + Y → 2Y Y → Q


>> ers = [1 1 0 0; 0 1 1 0 ; 0 0 1 0]; % [A, X, Y, Q] >> eps = [0 2 0 0; 0 0 2 0 ; 0 0 0 1]; >> kds = [0.05,1,1]; >> kis = [0,0,0]; % reação não reversivel >> cis =[50,1,1,0]; >> dt = 0.01; >> tmax = 50; >> res = cineticas(ers,eps,cis,kds,kis,dt,tmax); >> id = figure; >> plot(res(:,1),res(:,3:4)); % apenas colunas de X e Y >> legend (’X', ’Y’ ); % legenda de X e de Y >> saveas(id,’lotka_2.pdf');



l  resultando na figura ‘lotka_2.pdf’ A + Y → 2X X + Y → 2Y Y → Q


l  SQL •  Juntar tabelas (SELECT, JOIN, WHERE) •  Agregações (MAX, COUNT)

l  Integração numérica •  Equações diferenciais

•  Método de Euler, calcular derivada em t, usar valores em t e extrapolar para t+dt.

•  Cinética, exemplo •  Estequiometria e constantes.


Resumo

Para Consultar

l  SQL •  http://www.sqlitetutorial.net/

•  Exemplos de SQL com sqlite

l  Integração numérica •  Capítulos 8 e 9 do livro

•  (bastante mais avançado do que exigimos)


Documents

Informática para Ciências e Engenharias (B) 2016/17iceb.ssdi.di.fct.unl.pt/1617/teoricas/T10/iceb_t10.pdf · Informática para Ciências e Engenharias (B) 2016/17 Teórica 10. l