Informá(ca para as Ciências e Engenharias Versão : C (Engenharia Civil) Aula 8. Pedro Barahona 2016 / 17

(1)

(2)

Sumário

l  Introdução aos sistemas de bases de dados: §  Modelo relacional §  SQL (Structured Query Language) §  SQL e MATLAB §  Um exemplo prá(co

(3)

Bases de Dados

l  Base de Dados: Conjunto de dados estruturado.

§  Semelhante a vetores de estruturas vistos na aula passada.

l  Bastante comum para guardar dados.

§  Permite usar os dados em diferentes programas (até usando

diferentes linguagens).

l  Inclui:

§  Suporte ao armazenamento permanente (em ﬁcheiros).

§  Operações para criar, inserir, consultar/procurar, alterar, apagar,

(4)

(5)

(6)

(7)

l  Tabela de Filmes de uma base de dados DBvideos

Exemplo – Tabela de Filmes

(8)

Tabelas e Chaves

l  A tabela Filmes tem quatro campos §  FiIme_id §  Titulo §  Tipo §  Classiﬁcacao l  Chave primária (primary key)

§  Um campo ou combinação de campos que iden(ﬁca

inequivocamente esse registo

(9)

l  Tabela de Clientes de uma base de dados DBvideos

Exemplo – Tabela de Clientes

(10)

(11)

(12)

SQL

l  Uma linguagem para manipular dados e efetuar perguntas

(13)

Criação de Tabelas em SQL

l  Tabelas são criadas com a instrução CREATE

em que para além do nome da tabela são iden(ﬁcados os seus campos com os respec(vos (pos.

l  Tipicamente os (pos u(lizados são numéricos, textuais ou

Booleanos (BOOL).

CREATE TABLE nome-tabela ( Campo-1 tipo-1,

Campo-2 tipo-2, ...

(14)

l  Exemplo: Tabela de elementos químicos com as respec(vas massas atómicas. §  Tipos numéricos mais u(lizados: •  INTEGER: Inteiro; •  Real: Real •  DOUBLE: Real com precisão dupla §  Tipos de string: •  CHAR(N): string com exactamente N caracteres. •  VARCHAR(N): string com N caracteres no máximo •  TEXT: string mais longa Nota: em Sqlite3: é tudo tratado como TEXT

db >> CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

db >>

(15)

l  Em SQL é indiferente escrever as palavras-chave em

maiúsculas ou minúsculas.

§  No entanto, é comum escrever-se em maiúsculas.

l  Quanto aos nomes dos campos e tabelas depende do sistema.

§  À cautela, é melhor escrever estes nomes de forma

consistente.

db >> CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

db >>

(16)

l  Na criação de Tabelas deve ser especiﬁcada uma chave primária

l  A chave primária corresponde ao campo (ou combinação de

campos) que iden(ﬁcam inequivocamente cada registo.

§  Repe(ções (registos com a mesma chave primária) não são

permi(das (é reportado um erro).

db >> CREATE TABLE Massas

..>> ( Simbolo TEXT, Massa DOUBLE;

..>> PRIMARY KEY(Simbolo));

db >>

(17)

l  Exemplo: Tabela de Filmes

db >> CREATE TABLE Filmes ( Filme_id INTEGER, Titulo TEXT,

...>> Tipo TEXT, Classificacao TEXT, PRIMARY

...>> KEY(Filme_id) );

% Nota: A tabela é criada sem registos

db >>

(18)

l  Exemplo: Tabela de Clientes

db >> CREATE TABLE Clientes ( Cliente_id INTEGER, Nome

...>> TEXT, Morada TEXT, Numero_cartao_credito TEXT,

...>> PRIMARY KEY(Cliente_id) );

db >>

(19)

l  Exemplo: Tabela de Alugueres

db >> CREATE TABLE Alugueres ( Aluguer_id INTEGER,

...>> Cliente_id INTEGER, Filme_id INTEGER,

...>> Data_aluguer TEXT, Data_entrega TEXT,

...>> PRIMARY KEY(Aluger_id) );

db >>

(20)

Eliminação de Tabelas em SQL

l  Eliminar Tabela: DROP §  Após a criação de uma tabela que por algum mo(vo não nos interessa, por exemplo, ela pode ser eliminada (com todos os registos nale incluidos) através do comando DROP

db >> CREATE TABLE Errada (Num INTEGER, Nome TEXT);

(21)

Inserção de Registos em SQL

l  Inserir registos: INSERT

§  Um registo pode ser inserido numa tabela com a instrução

INSERT

§  Os valores dos campos neste registo, são indicados na mesma

ordem da deﬁnição original dos campos no CREATE TABLE.

db >> INSERT INTO Filmes VALUES (

...>> 10,"Meaning of Life","comedia","maiores de 16");

db >>

INSERT INTO tabela VALUES

(22)

Selecção de Registos em SQL

l  Selecionar registos: SELECT

§  Um ou mais registos podem ser selecionados duma ou mais

(23)

l  Alguns exemplos (uma só tabela): db >> SELECT * FROM Filmes;

% mostra todos os registos da tabela Filmes

db >> SELECT Titulo FROM Filmes WHERE Tipo = "comedia"; % mostra os títulos dos registos da tabela Filmes

% cujo tipo seja "comedia"

db >> SELECT Titulo, Tipo FROM Filmes ORDER BY Titulo;

% mostra os campos Titulo e Tipo dos registos da tabela % Filmes ordenados alfabeticamente pelo campo Titulo

(24)

Modiﬁcação de Registos em SQL

l  A instrução UPDATE permite alterar valores dos campos de uma

tabela, para os registos selecionados

db >> UPDATE Filmes SET Tipo = "drama"

...>> WHERE Titulo = "Elizabeth”;

% altera todos os registos da tabela Filmes, cujo título seja "Elizabeth", alterando o valor do campo Tipo para drama.

(25)

Eliminação de Registos em SQL

l  A instrução DELETE permite apagar todos os registos de uma

tabela, selecionados por uma condição (opcional).

l  Nota: Apagar os registos todos de uma tabela não elimina a

tabela.

§  Para o fazer deve ser usado o comando DROP

>> DELETE FROM Filmes WHERE Nome = "Avengers"; % apaga todos os registos da tabela Filmes, cujo título seja ”Avengers”.

DELETE

FROM tabela

(26)

(27)

l  Para executar: descarregar o executável do SQLite de hMps://sqlite.org/download.html

§  No Windows: descarregar um dos ﬁcheiros

sqlite-dll-win64-x32-3180000.zip

sqlite-dll-win64-x64-3180000.zip

Descomprimir e colocar o ﬁcheiro sqlite3.exe na pasta de trabalho

§  No Mac: descarregar o ﬁcheiro

sqlite-tools-osx-x86-3180000.zip

Descomprimir e colocar o ﬁcheiro sqlite3 na pasta de trabalho

(28)

l  Para criar/u(lizar em SQLITE uma base de dados, com nome

baseDados, existente na directoria corrente, (tal como o programa sqlite3) pode u(lizar-se a consola do sistema (e.g. Cmd no Windows ou Terminal no Mac) e u(lizar o comando

sqlite3 baseDados

l  A par(r da janela de comandos do OCTAVE deverá proceder-se

semelhantemente, mas especiﬁcando que se trata de uma chamada do sistema opera(vo (sqlite3 não é um ﬁcheiro .m )

l 

No ﬁnal, volta-se à janela de comandos do OCTAVE com a

comando sqlite “.quit”.

>> system(‘sqlite3 exemplo.db’)

(29)

Exemplo:

l  Criação de uma base de dados, com uma tabela de elementos

químicos, introduzindo-se os elementos H e He.

>> system(‘sqlite3 quimica.db’)

sqlite> CREATE TABLE Elementos

..>> ( Numero INTEGER, Simbolo TEXT,

..>> PRIMARY KEY(Numero));

sqlite> INSERT INTO Elementos VALUES (1, ‘H’);

sqlite> INSERT INTO Elementos VALUES (2, “He”);

sqlite> SELECT * FROM Elementos

1|H 2|He

sqlite> .quit >>

(30)

(31)

§  .prompt ﬁrst cont

•  Modiﬁca o prompt normal do sqlite para ﬁrst e o das

linhas de con(nuação para cont §  .tables •  lista as tabelas §  .separator sep •  especiﬁca sep como separador dos campos §  .import ﬁcheiro tabela

•  Importa o ﬁcheiro para a tabela assumindo os campos

separados pelo separador deﬁnido

(32)

l  Exemplo: Com a base de dados, quimica.db, já criada sqlite> .PROMPT ‘db >>’ ‘ -->> ’

db >> .schema

CREATE TABLE elementos (Numero INTEGER, Simbolo TEXT,PRIMARY KEY (Numero));

db >> INSERT INTO Elementos

-->> VALUES (3, “Li”);

db >> SEPARATOR ‘ :: ‘

db >> SELECT * FROM Elementos

(33)

(34)

MATLAB + SQLite

l  Para combinar SQL com MATLAB, usamos uma função que corre o

SQLite para executar o comando SQL que queremos.

l  Como vimos, o SQLite pode ser executado com parâmetros na

linha de comando (consola do sistema)

§  nome do ﬁcheiro com a base de dados.

§  e um comando SQL

l  A mesma operação pode ser feita a par(r da janela de comandos

(35)

Exemplo:

l  A inserção do elemento Berilio (Be) na base de dados

“quimica.db” anteriormente criada, pode ser feita a par(r da consola do sistema

l  Ou através da chamada de sistema a par(r da janela de

comandos OCTAVE

db >> INSERT INTO Elementos VALUES (4, “Be”);

db >>

>> system(‘sqlite quimica.db “INSERT INTO Elementos ...

VALUES (4, “Be”);’) >>

(36)

l  Em geral, a execução de comandos SQL a par(r de um programa

MATLAB pode ser feita com a função sqlite, com assinatura

§  Basicamente, esta função chama a função system do OCTAVE,

com o comando fornecido na string sql passada como argumento

•  Garan(ndo que as plicas são subs(tuidas por aspas, para

ser compayvel com a linguagem SQL.

§  Nota: esta função tem elementos que não fazem parte da

matéria de ICE.

(37)

function result = sqlite(sql,db)

% result=sqlite(sql,db)

% Executa o sqlite3 para processar o comando SQL

% fornecido no primeiro argumento. Devolve o resultado % como string. Opcionalmente, pode receber o nome

% do ficheiro da base de dados como segundo argumento. Se % omitido, assume ser temp.db.

if nargin < 2 % se o segundo argumento nao existe

db = 'temp.db';

end

sql = strrep(sql,'"',''''); % troca plicas por aspas

[stat,result] = system(['sqlite3 ',db,' "',sql,'"']); end

(38)

l  Testar

>> sqlite(‘sqlite3 atoms.db’)

ans =

>> sqlite(‘CREATE TABLE Atomos (Num INTEGER, Simb TEXT);’,

‘atoms.db’) ans =

>> sqlite(‘INSERT INTO Atomos VALUES (1,”H”);’,‘atoms.db’)

ans =

>> sqlite(‘INSERT INTO Atomos VALUES (2,”He”);’,‘atoms.db’)

ans =

>> sqlite('SELECT * FROM Atomos;',’atoms.db')

ans = 1|H 2|He >>

(39)

(40)

l  Sendo disponibilizado um conjunto de dados sobre praias, e a

(41)

(42)

(43)

l  A criação da tabela, que chamaremos Projetos, é feita através de

um só comando SQL, chamado do OCTAVE pela função sqlite deﬁnida atrás.

l  De notar a especiﬁcação da PRIMARY KEY, que garante uma

iden(ﬁcação única de cada registo e a não existência de repe(ções.

§  Neste caso, as obras têm um nome único.

function criaTabela(nomeBD)

% Cria a tabela Projectos na base de dados indicada.

sqlite([’CREATE TABLE Projectos ( Concelho text,',...

'Nome text, Custo float, Prioridade text,',...

'PRIMARY KEY(nome));'], nomeBD);

end

(44)

l  Porquê PRIMARY KEY?

§  Numa base de dados relacional, as chaves permitem

relacionar tabelas diferentes.

§  E mesmo só com uma tabela devemos poder iden(ﬁcar

inequivocamente cada registo •  por exemplo, para evitar a inserção de duplicados •  Neste caso assumimos que dois projectos com o mesmo nome seria um erro l  O SQLite cria sempre um atributo chamado Rowid que iden(ﬁca inequivocamente cada registo, mesmo que não se crie uma chave primária.

§  No entanto, esse não serve para evitar registos repe(dos

porque incrementa automa(camente.

(45)

l  Testes

§  Podemos começar por criar a tabela de Projectos numa BD,

que chamaremos de praias.db, para podermos testar a função

que carrega os dados.

>> criatabela(’praias.db');

% verificar que tabelas existem na BD ‘praias.db

>> sqlite('.tables',’praias.db')

ans = Projectos >>

(46)

l  Os dados podem ser carregados na tabela através de uma função

com assinatura

function carrega_praias(nomeFich, nomeBD)

(47)

function carrega_praias(nomeFich, nomeBD)

% documentação em anexo

fid = fopen(nomeFich,'r'); while ~feof(id)

linha = fgetl(fid);

pVirgs = findstr(';', linha); concelho = linha(1:pVirgs(1)-1);

nome = linha(pVirgs(1)+1:pVirgs(2)-1);

custo = str2num(linha(pVirgs(2)+1:pVirgs(3)-1)); prioridade = linha(pVirgs(3)+1:end);

sql = sprintf('insert into Projectos ... values("%s", "%s",%f,"%s");’,... concelho, nome, custo, prioridade) sqlite(sql, nomeBD);

end

fclose(fid);

end

(48)

l  Testes

§  podemos agora inserir os dados do ﬁcheiro praias.txt, e

veriﬁcar se eles estão corretamente inserido

>> carrega_praias(’praias.txt’, ‘praias.db');

% verificar que registos existem na tabela

>> sqlite(’SELECT * FROM Projectos;','praias.db’)

...

Portimao|Saneamento da arriba na praia do Vau|30000.00|Elevada >>

(49)

l  Em SQL podem ser feitas consultas (queries) às bases de dados

em que as condições WHERE não exigem igualdades estritas mas apenas “semelhanças”, em que alguns caracteres nos campos a pesquisar podem não ser iguais.

l  Para esse efeito

§  Usa-se a palavra LIKE, em vez do simbolo de igualdade

§  U(lizam-se wildcards, %, para susbs(tuirem quaisquer

caracteres.

l  Como anteriormente, estas consultas podem ser feitas

directamente do OCTAVE

(50)

l  Exemplo 1 (com LIKE e wildcards)

>> sqlite('SELECT Nome FROM Projectos

WHERE Nome LIKE "Arriba%";',’praias.db’)

% quais os registos cujo Nome comeca por Arriba ans = Arribas junto ao forte do Paimogo

Arribas em Porto das Barcas Arribas em Porto Dinheiro

Arribas da praia Grande do Rodizio Arribas da praia das Azenhas do Mar Arribas da praia Pequena

Arriba da Praia Fonte do Cortico Arribas da praia de Galapos

>>

(51)

l  Exemplo 2 (com LIKE e wildcards)

>> sqlite('SELECT Nome FROM Projectos

WHERE Nome LIKE "%praia%";',’praias.db')

% quais os registos cujo Nome contem a palavra praia

ans = Esporao da Praia da Vieira

Arribas da praia Grande do Rodizio Arribas da praia das Azenhas do Mar Arribas da praia Pequena

Arriba da Praia Fonte do Cortico

Relocalizacao de acessos das praias de Morgavel,...Farol Arribas da praia de Galapos

Alimentacao artificial da praia da Mareta Saneamento da arriba na praia do Vau

>>

(52)

(53)

l  Exemplo 3 (com DISTINCT)

>> sqlite(‘SELECT DISTINCT Concelho FROM Projectos;’,

’praias.db')

% quais os concelhos mencionados nos registos sem repeticoes ! ans = Marinha Grande

Lourinha Sintra Santiago do Cacem Sines Setubal Vila do Bispo Portimao

>> sqlite(‘SELECT DISTINCT Prioridade FROM Projectos;’,

(54)

l  As consultas SQL, feitas através da função sqlite, retornam uma string,

eventualmente com várias linhas, em que os campos dos registos retornados são delimitados pelo separador corrente (por omissão, o separador é “|”).

>> sqlite(’SELECT * FROM Projectos;','praias.db’)

...

Portimao|Saneamento da arriba na praia do Vau|30000.00|Elevada >>

(55)

l  Em geral, será conveniente estruturar melhor estas respostas,

transformando as strings retornadas num vetor de estruturas, correspondendo aos registos retornados.

l  Para esse efeito pode usar-se a função com assinatura abaixo que é

especiﬁcada de seguida

function recs = parseRecords (sqlResult,nameString)

Nota: esta função tem elementos que não fazem parte da matéria de ICE.

(56)

function recs = parseRecords(sqlResult,nameString)

% recs=parserecords(sqlResult,nameString)

% Devolve um vector de estruturas organizando os dados na string % devolvida pelo SQLite. Assume que os campos estao separados % por uma barra |. O segundo argumento, opcional, contem uma % string com os nomes dos campos das estruturas devolvidas, % tambem separados por |.

recs = [];

if nargin < 2

names = {};

else

names = strsplit(nameString,'|');

end

ca = strsplit(sqlResult,"\n");

for f = 1:length(ca)-1

... % converte a string sqlResult num vetor de estruturas

end end

(57)

for f = 1:length(ca)-1

line = deblank(ca{f});

if length(line) == 0 break end

rec = strsplit(ca{f},'|');

for g = 1:length(rec)

if g > length(names) campo = ['campo_',num2str(g)]; else campo = names{g};

end

% converte em numero, se possivel

testNum = str2double(rec{g}); if isnan(testNum)

valor = rec{g}; else

valor = testNum; end

recs(f).(campo) = valor;

end end

(58)

l  Query: §  Obter nome e custo de projetos com uma dada prioridade.

Nota: A função sprinX funciona como fprinX mas em vez de escrever num ﬁcheiro ou consola devolve a string formatada.

function projs = sel_prioridade(tabela, baseDados, prioridade)

% projs=selprioridade(tabela, baseDados, prioridade)

% selecciona da tabela da base de dados indicada os campos do % nome e custo dos projectos com a prioridade especificada.

query = sprintf('SELECT Nome, Custo FROM %s WHERE ...

Prioridade="%s";', tabela, prioridade);

result = sqlite(query, baseDados);

% obter vetor de estruturas com campos nome e custo

projs = parseRecords(result, 'nome|custo');

end

(59)

l  Testar

>> elevada = sel_prioridade('Projectos', ’praias.db', 'Elevada');

>> elevada(1)

nome = Alimentacao artificial da praia da Mareta custo = 2500000

>> elevada(2)

nome = Saneamento da arriba na praia do Vau custo = 30000

>> baixa = sel_prioridade('Projectos', ’praias.db', 'Baixa')

baixa =

nome = Arribas da praia de Galapos custo = 100000

>>

(60)

l  Query: §  Obter as obras separadas por prioridades «  Basta juntar as queries/funções anteriores

function [elevada,media,baixa] = processa_projs(fich, baseDados)

% [alta,media,baixa]=processaprojs(fich, baseDados) % Cria a tabela Projectos na base de dados indicada, % carrega os dados do ficheiro e selecciona da tabela % os campos do nome do projecto e custo, separando em % tres vectores de estruturas conforme a prioridade.

criatabela(baseDados);

carrega_praias('praias.txt', baseDados);

elevada = selprioridade('Projectos', baseDados, 'Elevada')

media = selprioridade('Projectos', baseDados, 'Media')

baixa = selprioridade('Projectos', baseDados, 'Baixa')

end

(61)

>> [elev, medi,baix] = processaprojs('praias.txt',’outra.db')

elev =

1x2 struct array containing the fields: nome custo

medi =

1x9 struct array containing the fields: nome custo

baix =

scalar structure containing the fields:

nome = Arribas da praia de Galapos % mostra o valor dos custo = 100000 % campos por so ter um elemento >> ...

l  Testar

(62)

>> elev.nome

ans = Alimentacao artificial da praia da Mareta ans = Saneamento da arriba na praia do Vau

>> med.nome

ans = Esporao da Praia da Vieira

ans = Arribas junto ao forte do Paimogo ans = Arribas em Porto das Barcas

ans = Arribas em Porto Dinheiro

ans = Arribas da praia Grande do Rodizio ans = Arribas da praia das Azenhas do Mar ans = Arribas da praia Pequena

ans = Arriba da Praia Fonte do Cortico

ans = Relocalizacao de acessos das praias de Morgavel, Franquia e Farol

>>

(63)