ICE-B

15 - Introdução às Bases de Dados

Ludwig Krippahl

1

Bases de Dados

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

■ SQL (Structured Query Language)• Criar tabelas• Inserir dados• Consultar

■ Sqlite3: interpretador e SGBD

2

Bases de Dados

Bases de Dados

3

Bases de Dados

Base de Dados: Conjunto estruturado de dados■ Vimos exemplos: Tabelas, dicionários, objectos, ...■ Permite guardar dados usados por diferentes programasSistema 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

SGBD

Modelo relacional■ Registo (objecto, entidade)• Conjunto de valores ordenados pelos atributos• Análogo a um tuplo, corresponde a uma linha na tabela

■ Campo (ou atributo)• Um dos atributos que todos os registos da tabela têm• Uma coluna na tabela

■ Tabela (relação)• Conjunto de registos• Análogo a uma lista de tuplos ou de dicionários (campo:valor)

■ Base de dados relacional• Conjunto de tabelas relacionadas

5

Tabelas e Chaves

Exemplo: tabela Filmes da base de dados DBVideos

■ Como identificar cada registo?

6

Tabelas e Chaves

■ A tabela tem quatro campos:■ Filme_id■ Titulo■ Tipo■ Classificacao

■ Chave primária• Conjunto de campos cuja combinação de valores não se repete• Permite identificar sem ambiguidade um registo (uma linha)• Neste caso é Filme_id

7

Tabelas e Chaves

Exemplo: tabela Clientes de DBVideos

■ Chave primária: Cliente_id

8

Tabelas e Chaves

Como registar alugueres? Tabela Alugueres

■ Chave primária: Aluguer_id■ Chaves externas: Cliente_id e Filme_id■ As chaves externas permitem ligar uma tabela a outras• Desta forma registamos quem alugou qual filme, e quando

9

Bases de Dados

Structured Query Language (SQL)

10

SQL

Structured Query Language■ Linguagem para manipular e consultar BD relacionais• Query: pergunta (sobre os dados armazenados na base de dados)

■ Interpretada pelo motor do SBD• É uma linguagem independente (não é Python)

• Pode ser usada interativamente pelo utilizador, com o interpretador SQL

■ Permite:• Criar tabelas, descrevendo os seus campos (esquema).• Inserir, alterar e remover registos das tabelas.• Fazer perguntas (queries)

11

SQL

Structured Query Language■ Tabelas são criadas com a instrução CREATE

CREATE TABLE nome-tabela (Campo-1 tipo-1,Campo-2 tipo-2, ...);

■ Nome da tabela, nomes dos campos e respectivos tipos.■ Tipicamente os tipos utilizados são (Sqlite):• Números inteiros: INT, INTEGER• Números fraccionários: FLOAT, REAL• Texto: TEXT• Lógico (Booleano): BOOLEAN (0 ou 1)

■ Nota: isto para Sqlite; noutros sistemas há mais distinções

12

SQL

Structured Query Language■ Exemplo: Tabela de elementos químicos e massas atómicas.

CREATE TABLE Massas ( Simbolo TEXT, Massa FLOAT);

■ Em SQL as palavras-chave podem ser em maiúsculas ouminúsculas

• Mas é mais comum escrever-se em maiúsculas.

■ Em Sqlite os nomes dos campos e tabelas também podem sermaiúsculas ou minúsculas

■ Noutros sistemas depende• É melhor escrever de forma consistente

13

SQL

Chave Primária■ Na criação da tabelas deve-se especificar uma chave primária■ A chave primária é o campo (ou combinação de campos) que

identifica cada registo

CREATE TABLE Massas ( Simbolo TEXT, Massa DOUBLE, PRIMARY KEY(Simbolo));

■ Impõe uma restrição de integridade:• Não é permitido inserir ou modificar registos de forma a repetir a chave primária

14

SQL

Criação da tabela Filmes

■ Nota: na consola do Sqlite3

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

15

SQL

Criação da tabela Clientes

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

16

SQL

Criação da tabela Alugueres

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

17

SQL

Apagar uma tabela: DROP■ Se queremos eliminar uma tabela que não nos interessa,

CREATE TABLE Errada (Num INTEGER, Nome TEXT);

■ Podemos usar o comando DROP

DROP TABLE Errada;

■ Atenção: isto é irreversível e elimina todos os registos

18

SQL

Inserir registos: INSERT■ Um registo pode ser inserido numa tabela com INSERT

INSERT INTO Tabela VALUES ( valor1, valor2, ..., valorN);

■ Os valores são dados na mesma ordem do CREATE TABLE■ Exemplo:

sqlite> INSERT INTO Filmes VALUES (101, 'Sexto Sentido', 'suspense drama', ...> 'maiores de 12'); sqlite> INSERT INTO Filmes VALUES (102, 'Regresso ao Futuro', ...> 'comedia aventura', 'maiores de 6');

19

SQL

Selecionar registos: SELECT■ Registos podem ser selecionados de tabela(s) com SELECT

SELECT lista-campos

FROM lista-tabelas

WHERE condição

ORDER BY campo;

■ Os registos são filtrados pela condição WHERE■ Podem ser ordenados incluindo a opção ORDER BY.

20

SQL

Selecionar registos: SELECT■ Exemplos (Sqlite)

sqlite> SELECT * FROM Filmes; 101|Sexto Sentido|suspense|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 ... sqlite> SELECT Titulo FROM Filmes WHERE Tipo = "animacao"; Monstros e Cia. Idade do Gelo sqlite> SELECT Titulo, Tipo FROM Filmes ...> WHERE Classificacao = "maiores de 12" ORDER BY Titulo; Dia da Independencia|ficcao-cientifica acao Elizabeth|drama historico Sexto Sentido|suspense X-Men|ficcao-cientifica acao

21

SQL

Alterar registos: UPDATE■ O UPDATE altera os valores dos campos dos registos selecionados

UPDATE tabela SET campo = valor WHERE condição;

■ Exemplo:

sqlite> UPDATE Filmes SET Tipo = "drama" WHERE Titulo = "Elizabeth"; sqlite> SELECT * FROM Filmes WHERE Titulo = "Elizabeth"; 106|Elizabeth|drama|maiores de 12

22

SQL

Apagar registos: DELETE■ O DELETE apaga os registos selecionados

DELETE FROM tabela WHERE condição;

■ Exemplo:

sqlite> DELETE FROM Filmes WHERE Classificacao = "maiores de 12"; sqlite> SELECT * FROM Filmes; 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 108|Platoon|acao drama guerra|maiores de 16 109|Idade do Gelo|animacao|maiores de 4 110|Impacto Profundo|suspense policial|maiores de 16

23

Bases de Dados

Sqlite3

24

Sqlite3

Sqlite3 é um motor gratuito de bases de dados■ Podemos usá-lo como interpretador de linha de comando• Interpreta SQL e gere a base de dados

■ Pode-se obter em https://sqlite.org/download.html• Windows: descarregar sqlite-tools-win32-x86-3230100.zip

• Descomprimir e colocar o ficheiro sqlite3.exe na pasta de

• Mac: descarregar o ficheiro sqlite-tools-osx-x86-3230100.zip

• Descomprimir e colocar o ficheiro sqlite3 na pasta de trabalho

• Linux: provavelmente já está instalado, senão usar o gestor de pacotes

25

Sqlite3

Executar o Sqlite3■ Windows: duplo-click no sqlite3.exe■ Linux: sqlite3 na consola do sistema operativo

SQLite version 3.20.1 2017-08-24 16:21:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> .open teste.db

■ Se o ficheiro da BD (teste.db) não existe é criado.• Se existe é lido e temos acesso às tabelas lá criadas• O ficheiro de bases de dados é binário e gerido pelo Sqlite3

26

Sqlite3

Exemplo: tabela com massas dos elementossqlite> .open quimica.db sqlite> CREATE TABLE Elementos ( Simbolo TEXT, Nome TEXT, NumAtomico INTEGER, ...> Massa REAL, Grupo INTEGER, Periodo INTEGER, PRIMARY KEY (Simbolo)); sqlite> INSERT INTO Elementos VALUES ('H', 'Hydrogen', 1, 1.00794, 1, 1) sqlite> INSERT INTO Elementos VALUES ('He', 'Helium', 2, 4.002602, 18, 1) sqlite> SELECT * FROM Elementos; H|Hydrogen|1|1.00794|1|1 He|Helium|2|4.002602|18|1 sqlite> .quit

■ Comandos começados por . são do Sqlite e não SQL:.open

.quit

cria ou abre o ficheiro da BDsai do interpretador

27

Sqlite

Outros comandos do Sqlite (não são SQL):.help

.read nomeFicheiro

.schema

.prompt first cont

.tables

.separator sep

.import ficheiro tabela

lista comandos disponíveis no Sqliteexecuta script SQLlista esquemas das tabelasmuda prompt normal e e continuaçãolista as tabelasespecifica separador dos camposImporta ficheiro assumindo separadordefinido

28

Sqlite

Outros comandos do Sqlite (não são SQL):sqlite> .prompt 'db >> ' '-->> ' db >> .schema CREATE TABLE Elementos ( Simbolo TEXT, Nome TEXT, NumAtomico INTEGER, Massa REAL, Grupo INTEGER, Periodo INTEGER, PRIMARY KEY (Simbolo)); db >> .separator ' :: ' db >> SELECT * FROM Elementos; H :: Hydrogen :: 1 :: 1.00794 :: 1 :: 1 He :: Helium :: 2 :: 4.002602 :: 18 :: 1 db >> .tables Elementos

29

Bases de Dados

Resumo

30

Resumo

Bases de Dados■ Sistemas de Bases de Dados■ SQL e Sqlite■ Criar tabelas, inserir dados e consultar■ Comandos do SqliteLeitura adicional:■ Recomendada: Capítulo 15 dos apontamentos■ Opcional: Tutorial Sqlite: https://www.tutorialspoint.com/sqlite