Informática para Ciências e Engenharias...

Informática para Ciências e Engenharias

2013/14

Teórica 10

2

Na aula de hoje...

Introdução aos sistemas de bases de dados:• Modelo relacional

• SQL (Structured Query Language)

• SQL e MATLAB

• Um exemplo prático

Alguns slides adaptados de N. Dell, J. Lewis, Computer Science Illuminated, 4th Ed

3

Sistemas de gestão debases de dados

Sistema de Gestão de Bases de Dados (SGBD) • (Database management system, DBMS)

• Combinação de software e dados:• Base de dados

• Ficheiros estruturados, com os dados.

• Motor de base de dados

• Software para consulta e modificação dos dados.

• Esquema de base de dados:

• Especificação da organização lógica dos dados.

4

Sistemas de gestão debases de dados

Ficheiros de suporte da base de dados

Utilizador/Programador

Esquema da base de dados

Motor da

base dedados

5

Modelo relacional

Registo (objeto, entidade)• Conjunto de atributos (campos) relacionados

• Análogo às estruturas em Matlab.

Tabela• Conjunto de registos. Análogo ao vector de

estruturas em Matlab.

Base de dados relacional• Conjunto de tabelas relacionadas

6

Tabela filmes da BD videos

Filme_id Titulo Tipo Classificacao

101 Sexto Sentido suspense drama maiores de 12

102 Regresso ao Futuro comedia aventura maiores de 6

103 Monstros e Cia. animacao maiores de 4

104 Alien ficcao-cientifica terror maiores de 16

105 X-Men ficcao-cientifica acao maiores de 12

106 Elizabeth drama historico maiores de 12

107 Dia da Independencia ficcao-cientifica acao maiores de 12

108 Platoon acao drama guerra maiores de 16

109 Idade do Gelo animacao maiores de 4

110 Impacto Profundo suspense policial Maiores de 16

7

Tabela filmes da BD videos

Filme_id Titulo Tipo Classificacao

101 Sexto Sentido suspense drama maiores de 12

102 Regresso ao Futuro comedia aventura maiores de 6

103 Monstros e Cia. animacao maiores de 4

104 Alien ficcao-cientifica terror maiores de 16

105 X-Men ficcao-cientifica acao maiores de 12

106 Elizabeth drama historico maiores de 12

107 Dia da Independencia ficcao-cientifica acao maiores de 12

108 Platoon acao drama guerra maiores de 16

109 Idade do Gelo animacao maiores de 4

110 Impacto Profundo suspense policial Maiores de 16

Como identificar um registo?

8

Chave primária de uma tabela

Chave primária (primary key)• Um campo ou combinação de campos que

identifica inequivocamente esse registo

A tabela filmes tem quatro campos• FiIme_id, Titulo, Tipo e Classificacao

• A chave primária é Filme_id.

9

Tabela clientes da BD videos

Cliente_id Nome Morada Numero_cartao_credito

101 Artur Meireles Rua da Paz, 20 123412341234123401

102 Joana Fonseca Rua da Guerra, 1234 123412341234123402

103 Artur Lopes da Silva Av.Liberdade, 202 123412341234123403

104 Isabel Lopes da Silva Rua do La Vem Um, 1 123412341234123404

105 Passos Coelho Av. Massama, 20 - 3 123412341234123405

106 Vitor Gaspar Rua do 5 %, 8 123412341234123406

107 Cavaco Silva Boliqueime 123412341234123407

108 Antonio Jose Seguro Largo do Rato, 2 123412341234123408

Chave primária: Cliente_id

10

Chaves externas

Aluguer_id Cliente_id Filme_id Data_aluguer Data_entrega

100 102 109 2013/11/11 2013/11/15

101 101 106 2013/11/12 2013/11/14

102 107 102 2013/11/14 2013/11/20

103 106 104 2013/11/11 2013/11/15

A tabela alugueres tem duas chaves externas:

• FiIme_id, que a liga à tabela filmes

• Cliente_id, que a à tabela clientes

11

Structured Query Language (SQL)

12

SQL

Linguagem para manipular dados e efectuar perguntas (queries) sobre os dados armazenados na base de dados.• Criar tabelas, onde se guarda os dados

• um registo por linha

• um campo por coluna

• Inserir, alterar e remover registos

• Interrogar

13

SQL

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

14

SQL

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

Nome da tabela

15

SQL

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

Campos e tipos respectivosSimbolo é do tipo texto (string)massa da tabela

16

SQL

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

Tipos de string:CHAR(N): string com exactamente N caracteres.VARCHAR(N): string com N caracteres no máximoTEXT: string mais longa

Sqlite3: é tudo tratado como TEXT

17

SQL

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE);

Em SQL é indiferente escrever as palavras-chave em maiúsculas ou minúsculas. No entanto, é comum escrever-se em maiúsculas.Quanto aos nomes dos campos e tabelas depende do sistema. À cautela, é melhor escrever estes de forma consistente.

18

SQL

Exemplos

CREATE TABLE Filmes ( Filme_id INTEGER, Titulo TEXT, Tipo TEXT, Classificacao TEXT, PRIMARY KEY(Filme_id) );

CREATE TABLE Clientes ( Cliente_id INTEGER, Nome TEXT, Morada TEXT, Numero_cartao_credito TEXT, PRIMARY KEY(Cliente_id) );

CREATE TABLE Alugueres ( Aluguer_id INTEGER,Cliente_id INTEGER, Filme_id INTEGER,Data_aluguer TEXT, Data_entrega TEXT,PRIMARY KEY(Aluger_id) );

19

SQL

Exemplo, criar tabela

CREATE TABLE Filmes ( Filme_id INTEGER,Titulo TEXT,Tipo TEXT,Classificacao TEXT,PRIMARY KEY(Filme_id));

Especifica o campo (ou combinação de campos) que identificam inequivocamente cada registo. Tentar criar repetições neste dá erro.

20

SQL

Exemplo, inserir registo

INSERT INTO Filmes VALUES (10,"Meaning of Life","comedia","maiores de 16");

Valores dos campos neste registo, pela mesma ordem da definição original dos campos no CREATE TABLE.

21

SQL

Exemplo, modificar ou apagar registos

UPDATE Filmes SET Tipo = "drama" WHERE Titulo = "Elizabeth";

DELETE FROM Filmes WHERE Nome = "Avengers";

22

SQL

Interrogação

SELECT lista-campos from lista-tabelas WHERE condição ORDER BY campo;

SELECT * FROM Filmes;SELECT Titulo FROM Filmes WHERE Tipo = "comedia";SELECT Titulo,Tipo FROM Filmes ORDER BY Titulo;

23

SQLite

24

SQLite

Motor de bases de dados Gratuito, código aberto

• http://www.sqlite.org/

Podemos usá-lo interactivamente como interpretador de linha de comando.

Interpreta SQL e gere a base de dados Para executar

• sqlite3 baseDados

25

SQLite

Para executar• sqlite3 baseDados

• baseDados é o nome do ficheiro onde ficam guardadas as tabelas.

• Se já existe, abre esse e dá acesso ás tabelas lá guardadas; caso contrário cria um novo.

• Se for só para experimentar pode ser algo como temp.db.

26

SQLite

Além de SQL, dispõe de mais comandos particulares do SQLite• .help

• lista comandos disponíveis (além do SQL)

• .read nomeFicheiro• executa script SQL

• .schema• lista esquemas das tabelas

• .quit• sair do interpretador

27

SQLite

• .tables• lista as tabelas

• .separator sep• especifica o separador dos campos

• .import nomeFicheiro tabela• importa o ficheiro para a tabela assumindo os campos

separados pelo separador definido

Sqlite

28

MATLAB + SQL

29

MATLAB + SQL

Para combinar SQL com MATLAB, usamos uma função que corre o SQLite para executar o comando SQL que queremos.

30

SQLite na linha de comando

O SQLite pode ser executado com parâmetros na linha de comando.• nome do ficheiro com a base de dados.

• e podemos incluir também um comando SQL

Na consola (cmd em Windows)

sqlite3 teste.db "CREATE TABLE Massas (Simbolo TEXT, Massa REAL);"

sqlite3 teste.db "INSERT INTO Massas VALUES ('H', 1);"

sqlite3 teste.db "SELECT * FROM Massas;"H|1.0

Consola

31

SQLite na linha de comando

O SQLite pode ser executado com parâmetros na linha de comando.• um, já conhecemos, é o nome do ficheiro com

a base de dados.

• podemos incluir também um comando SQL

• o que não serve de muito por si, mas pode ser útil para executar comandos SQL a partir de um programa em MATLAB.

32

SQLite na linha de comando

Executar comandos SQL a partir de um programa MATLAB

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

function result=sqlite(sql,db)

33

MATLAB + SQL

34

MATLAB + SQL

Nome da base de dados se não for indicado nenhum em argumento.

35

MATLAB + SQL

Troca aspas por plicas(é preciso duas dentro de uma string)

36

MATLAB + SQL

Executa o sqlite3 com o comando SQL usando a função system.

37

MATLAB + SQL

No Octave / MATLAB

> sqlite('CREATE TABLE Massas (Simbolo TEXT, Massa REAL);','teste.db')ans => sqlite('INSERT INTO Massas VALUES ("H", 1);','teste.db')ans => sqlite('SELECT * FROM Massas;','teste.db')ans = H|1.0

38

Exemplo:

projectos de recuperação

de praias

39

Exemplo: MATLAB + SQL

praias.txt• Concelho, Nome, Custo, Prioridade

Marinha Grande;Esporao da Praia da Vieira;720000.00;MediaLourinha;Arribas junto ao forte do Paimogo;591000.00;MediaLourinha;Arribas em Porto das Barcas;691000.00;MediaLourinha;Arribas em Porto Dinheiro;606500.00;MediaSintra;Arribas da praia Grande do Rodizio;39951.63;MediaSintra;Arribas da praia das Azenhas do Mar;326560.08;Media...Setubal;Arribas da praia de Galapos;100000.00;Baixa...Portimao;Saneamento da arriba na praia do Vau;30000.00;Elevada

40

Exemplo: MATLAB + SQL

Objectivo• Usar um sistema de gestão de bases de

dados (SGBD) para gerir a informação

Ficheiros de suporte da base de dados

Utilizador/Programador

Esquema da base de dados

Motor da

base dedados

41

Exemplo: MATLAB + SQL

Objectivo• Criar tabela

• Inserir dados dos projectos de recuperação

• Questionar a BD usando SQL• Exemplo: obter os projectos de prioridade alta,

média e baixa.

42

Exemplo: MATLAB + SQL

Criar tabela

function criatabela(nomeBD)

43

Exemplo: MATLAB + SQL

44

Exemplo: MATLAB + SQL

Porquê PRIMARY KEY?• Numa base de dados relacional, as chaves

permitem relacionar tabelas diferentes. • (Não vamos usar BDs com várias tabelas).

• Mas mesmo só com uma tabela é útil poder identificar 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

45

Exemplo: MATLAB + SQL

Porquê PRIMARY KEY?• O SQLite cria sempre um atributo chamado

Rowid que identifica inequivocamente cada registo, mesmo que não se crie uma chave primária.

• No entanto, esse não serve para evitar registos repetidos porque incrementa automaticamente.

46

Exemplo: MATLAB + SQL

Testes• podemos começar por criar uma tabela numa

BD de teste, para podermos testar a função que carrega os dados.

octave:23> criatabela('teste.db');octave:24> sqlite('.tables','teste.db')ans = Projectos

47

Exemplo: MATLAB + SQL

Carregar os dados

function carregapraias(nomeFich, nomeBD)

48

Exemplo: MATLAB + SQL

49

Exemplo: MATLAB + SQL

Lê cada linha do ficheiro de entrada.

50

Exemplo: MATLAB + SQL

Cria o comando SQL de inserção tabela. A função sprintf funciona como fprintf mas devolve uma string.

51

Exemplo: MATLAB + SQL

Executa o comando SQL na BD especificada.

52

Exemplo: MATLAB + SQL

Testar:• (podemos fazer tudo no MATLAB/Octave)

octave:12> carregapraias('praias.txt', 'teste.db')octave:13> sqlite('select * from Projectos;','teste.db')ans = Marinha Grande|Esporao da Praia da Vieira|720000.0|MediaLourinha|Arribas junto ao forte do Paimogo|591000.0|MediaLourinha|Arribas em Porto das Barcas|691000.0|MediaLourinha|Arribas em Porto Dinheiro|606500.0|MediaSintra|Arribas da praia Grande do Rodizio|39951.63|MediaSintra|Arribas da praia das Azenhas do Mar|326560.08|MediaSintra|Arribas da praia Pequena|428015.58|Media...

53

Exemplo: MATLAB + SQL

LIKE octave:29> sqlite('SELECT Nome FROM Projectos

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

ans = Arribas junto ao forte do PaimogoArribas em Porto das BarcasArribas em Porto DinheiroArribas da praia Grande do RodizioArribas da praia das Azenhas do MarArribas da praia PequenaArriba da Praia Fonte do CorticoArribas da praia de Galapos

54

Exemplo: MATLAB + SQL

LIKE octave:32> sqlite('SELECT Nome FROM Projectos

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

ans = Esporao da Praia da VieiraArribas da praia Grande do RodizioArribas da praia das Azenhas do MarArribas da praia PequenaArriba da Praia Fonte do CorticoRelocalizacao de acessos das praias de Morgavel, Franquia e FarolArribas da praia de GalaposAlimentacao artificial da praia da MaretaSaneamento da arriba na praia do Vau

55

Exemplo: MATLAB + SQL

DISTINCToctave:33> sqlite('SELECT Concelho FROM Projectos;','teste.db')

ans = Marinha GrandeLourinhaLourinhaLourinhaSintraSintraSintraSantiago do CacemSinesSetubalVila do BispoPortimao

56

Exemplo: MATLAB + SQL

DISTINCT> sqlite('SELECT DISTINCT Concelho FROM Projectos;','teste.db')

ans = Marinha GrandeLourinhaSintraSantiago do CacemSinesSetubalVila do BispoPortimao

57

Exemplo: MATLAB + SQL

DISTINCT> sqlite('SELECT DISTINCT Prioridade FROM Projectos;','teste.db')

ans = MediaBaixaElevada

58

Exemplo: MATLAB + SQL

Problema:• o resultado do sqlite é uma string

• mas convém estruturar melhor a informação

ans = Marinha Grande|Esporao da Praia da Vieira|720000.0|MediaLourinha|Arribas junto ao forte do Paimogo|591000.0|MediaLourinha|Arribas em Porto das Barcas|691000.0|MediaLourinha|Arribas em Porto Dinheiro|606500.0|MediaSintra|Arribas da praia Grande do Rodizio|39951.63|MediaSintra|Arribas da praia das Azenhas do Mar|326560.08|MediaSintra|Arribas da praia Pequena|428015.58|Media...

59

Exemplo: MATLAB + SQL

Converter a string do resultado num vector de estruturas

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

function recs=parseRecords(sqlResult,nameString)

60

Lista os nomes dos campos das estruturas, ou nada se nenhuma string for fornecida no segundo argumento.

61

Parte a string pelas linhas, percorre as linhas partindo pelo separador ('|').

62

Para cada atributo (coluna da tabela) gerar ou obter o nome do campo da estrutura, converter em número se possível e guardar no vector de estruturas.

63

Exemplo: MATLAB + SQL

Obter nome e custo de projectos com uma prioridade especificada.

function projs=selprioridade(tabela, baseDados, prioridade)

64

Exemplo: MATLAB + SQL

A função sprintf funciona como fprintf mas em vez de escrever num ficheiro ou consola devolve a string formatada

65

Exemplo: MATLAB + SQL

Obtido o resultado da função sqlite (numa só string), convertemos num vector de estruturas com parseRecords, indicando os nomes dos campos: nome e custo.

66

Exemplo: MATLAB + SQL

Testar:octave:19> elevada=selprioridade('Projectos', 'teste.db', 'Elevada');octave:20> elevada(1)

nome = Alimentacao artificial da praia da Mareta custo = 2500000

octave:21> elevada(2)

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

octave:22> baixa=selprioridade('Projectos', 'teste.db', 'Baixa')baixa =

nome = Arribas da praia de Galapos custo = 100000

67

Exemplo: MATLAB + SQL

Função principal:

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

68

Exemplo: MATLAB + SQL

69

Exemplo: MATLAB + SQL

Testar:octave:25> [elev,med,baix]=processaprojs('praias.txt','praias.db')elev = 1x2 struct array containing the fields: nome custo

med = 1x9 struct array containing the fields: nome custo

baix = scalar structure containing the fields: nome = Arribas da praia de Galapos custo = 100000

70

Exemplo: MATLAB + SQL

Testar:octave:26> elev.nomeans = Alimentacao artificial da praia da Maretaans = Saneamento da arriba na praia do Vauoctave:27> med.nomeans = Esporao da Praia da Vieiraans = Arribas junto ao forte do Paimogoans = Arribas em Porto das Barcasans = Arribas em Porto Dinheiroans = Arribas da praia Grande do Rodizioans = Arribas da praia das Azenhas do Marans = Arribas da praia Pequenaans = Arriba da Praia Fonte do Corticoans = Relocalizacao de acessos das praias de Morgavel, Franquia e Farol

SQL+Matlab

71

Para consultar

SQL• Muito fácil encontrar tutoriais na Web, mas

normalmente têm muito mais do que damos aqui.• Exemplo de uma cábula que pode ser útil:

http://www.zentut.com/sql-tutorial/sql-cheat-sheet/

72

Dúvidas