Upload
phamduong
View
212
Download
0
Embed Size (px)
Citation preview
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