Banco de Dados para Web - Páginas Pessoais

Banco de Dados para Web
Profa. Dra. Ligia Flávia Antunes Batista
http://paginapessoal.utfpr.edu.br/ligia
Apresentação
Graduação UEL – Ciência da Computação
Mestrado Informática UFPR
Área de concentração: Inteligência Computacional
Doutorado Ciências Cartográficas – UNESP
SIG, Cartografia Digital e Análise espacial
“Modelagem espaço-temporal do crescimento de
macrófitas submersas no reservatório de
Taquaruçu, rio Paranapanema”
Ementa
Modelagem
Modelo relacional
Aspectos de Projeto de Banco de Dados
Linguagem SQL
Comandos básicos (DML, DDL)
Subconsultas, agregação, triggers, índices, visões,
Transações, autorização
Segurança, performance, administração
3
Ferramentas
Mysql Community Edition
Server versão 5.5
http://www.mysql.com/downloads/mysql/
Mysql Workbench
http://www.mysql.com/downloads/workbench/
Ambiente integrado para:
Projeto e modelagem;
Consultas SQL;
Administração.
4
Referências
http://dev.mysql.com/doc/
5
Aulas
12 / maio
Projeto de banco de dados, Introdução a SQL
Trabalho: projetos
19 / maio
Consultas avançadas (sub-consulta, agregação,
visões)
Trabalho: carga de dados e consultas
26 / maio
Triggers, índices, transações, autorização
Segurança, performance, administração
6
Turma
Heterogeneidade
Mesclar princípios básicos com tópicos avançados
Conhecê-los:
GoogleForms
http://paginapessoal.utfpr.edu.br/ligia/disciplinas/pos-we
7
Andamento
Participação, questões, sejam básicas ou
avançadas
8
SGBD
Sistema Gerenciador de Banco de Dados
Ou DBMS
Database Manager System
9
SGBD (ou DBMS)
Oferece
Armazenamento e acesso …
… eficiente, confiável, conveniente, e seguro...
… a grande quantidade de dados persistentes
10
SGBD
Massivo: terabytes
Persistente: não volátil
Seguro: controle de usuários e níveis de acesso
Multiusuário: controle de concorrência,
compartilhamento
Conveniente: independência de dados e programa
Eficiente: milhares de consultas ou atualizações por
segundo
Confiável: 99.9999 %
11
Mysql e Web (Fonte: Oracle Corporation)
12
Mysql e outras corporações
(Fonte: Oracle Corporation)
13
Mysql
45 mil downloads por dia
66% usam Windows para desenvolvimento
48% usam Windows para produção
Versão Community e Enterprise
http://dev.mysql.com/downloads/mysql/5.1.html
14
Modelagem
Projeto de banco de dados
Modelo relacional
15
Projeto de BD
Fases
Especificação das necessidades do usuário
(levantamento de requisitos)
Esquema conceitual (visão detalhada da empresa)
Remoção de redundâncias e eliminação de
conflitos
Revisão do esquema em função das necessidades
funcionais, ou seja, tipos de operações que serão
realizadas nos dados (atualização, pesquisa,
relatórios)
16
Projeto de BD
Esquema ou projeto lógico: modelo conceitual será
mapeado para o modelo de implementação de
dados do SGBD que será usado
Projeto físico: estrutura interna de armazenamento
(tipos de dados)
17
Projeto de BD
Geralmente há muitos projetos possíveis
Alguns são melhores que outros
Como escolher?
Evitar redundância
Pode gerar anomalias de atualização e remoção
18
Persistência
Vários modelos de armazenamento
Modelo relacional
Modelo orientado a objeto
XML
...
19
Modelo relacional
Um BD relacional é composto de tabelas ou
relações
Tabela: conjunto de linhas (tuplas)
Linha: série de campos (atributos)
Coluna: conjunto de campos com o mesmo nome
20
Exemplo
21
Exemplo
22
Modelo conceitual
(Modelo entidade-relacionamento)
23
Modelo físico
24
Conceitos chave
Modelo de dados
Conjunto de registros (linhas)
Esquema e dados
Análogo a tipos e variáveis
Data Definition Language (DDL)
Permitem gerenciar o esquema
Data Manipulation Language (DML)
Consulta e modificação de dados
25
Modelo relacional
As linhas não estão ordenadas (não é possível
referenciar por posição)
Chave primária: 1 ou mais colunas cujos
valores distinguem uma linha das demais
Sugestão: não seja um dado, apenas um valor de
controle
Chave estrangeira: 1 ou mais colunas que são
chaves primárias em uma outra tabela
26
Modelo relacional
Depto
CodigoDepto (PK)
NomeDepto
1
Compras
2
Engenharia
3
Vendas
Empregado
CodigoEmp
(PK)
Nome
CodigoDepto
(FK)
CatFuncional
1
Souza
1
2
Santos
2
C5
3
Silva
2
C5
4
Soares
1
C2
27
Relacionamentos
Cardinalidades
1:1
1:n
n:n
Tipos
identificador
não identificador
28
1:1
Um-para-um: uma entidade em A está
associada no máximo a uma entidade em B e
uma entidade em B está associada no máximo
a uma entidade em A
Obs.: Chave estrangeira em uma das entidades.
29
1:n
FK na direção muitos
Um-para-muitos: uma entidade em A está
associada a qualquer número de entidades em
B, enquanto uma entidade em B está
associada no máximo a uma entidade em A
30
n:n
Muitos-para-muitos: Uma entidade em A está
associada a qualquer número de entidades em
B, e uma entidade em B está associada a
qualquer número de entidades em A.
Obs.: Requer tabela extra para representá-lo.
31
Workbench
1:1
1:n
n:n
32
Tipos de dados
Texto
CHAR – Conjunto de caracteres com tamanho fixo
VARCHAR – Conjunto de caracteres com tamanho
variável
TEXT
Numérico
INT - INTEGER
FLOAT - -3.402823466E+38 a 3.402823466E+38
DECIMAL[(M[,D])] – M é o total de dígitos e D o
número de casas decimais
33
Variações para inteiros
34
Tipos de dados
Data e hora
DATETIME - 'YYYY-MM-DD HH:MM:SS': de '100001-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP: de '1970-01-01 00:00:01' UTC to
'2038-01-19 03:14:07' UTC (Coordinated Universal Time)
DATE - 'YYYY-MM-DD'
Muitos outros
http://dev.mysql.com/doc/refman/5.1/en/data-type-o
35
Workbench
Front-end
Ferramenta gráfica
36
Mysql Workbench
Modelagem
Engenharia reversa
Documentação
Sincronização de
esquema
Administração
Start/stop server
Gerenciamento de
usuários
Backup
http://wb.mysql.com/
37
Exemplo 1:
Tabelas
empregado e
departamento
38
Chave estrangeira
Uma chave estrangeira pode referenciar a
chave primária da própria tabela (Autorelacionamento)
CodigoEmp
Nome
CodigoDepto
CodGerente
1
Souza
1
2
Santos
2
4
3
Silva
2
4
4
Soares
1
1
39
Exemplo
estado
regiao
cod_regiao
cod_estado
nome
cod_regiao (FK)
nome
materia
congressista
coligacao
cod_materia
cod_con
cod_coligacao
nome_col
sigla_col
partido
cod_partido
nome_partido
sigla_partido
cod_coligacao (FK)
descricao
data_votacao
resultado
tipo
qtde_favor
qtde_contra
qtde_abstencoes
qtde_ausentes
nome
data_ult_eleicao
num_vezes_eleito
cod_estado (FK)
cod_partido (FK)
votacao
cod_con (FK)
cod_materia (FK)
voto_dado
40
Exercícios
1. Construa um diagrama para um hospital com
um conjunto de pacientes e um conjunto de
médicos. Registros de diversos exames
realizados são associados a cada paciente.
2. Construa um diagrama para uma companhia
de seguros de automóveis com um conjunto de
clientes, onde cada um possui um certo
número de carros. Cada carro tem um número
de acidentes associados a ele.
41
Normalização
Diretrizes gerais
42
Diretrizes de projeto
Usar o tipo de dado apropriado
O tipo de dado errado pode levar à necessidade de
processamento adicional para conversão – string
para um valor numérico antes de realizar cálculos
Usar tamanho apropriado
Valores máximos muito altos são inadequados
quando os valores a serem armazenados são muito
menores
43
Diretrizes de projeto
Uso do not null
Uma coluna que está especificada como NULL mas
nunca conterá um valor nulo pode causar
problemas de integridade, além de adicionar
processamentos desnecessários
44
Normalização do esquema
O que é Normalização?
Introduzida por E.F. Codd
A modificação de um esquema para que esteja em
conformidade com regras (formas normais)
Assegurar que toda coluna não chave esteja
relacionada à chave
Redução de redundância
45
Benefícios da normalização
Reduz espaço de armazenamento
Menos redundância significa menos dados
Buscas melhores e mais rápidas
Menos dados para “varrer”
Buscas mais fáceis em dados que poderiam estar
misturados
Integridade de dados melhorada
Quando o dado está apenas em um lugar, a
atualização também é única
Ajuda refinar um projeto
46
As formas normais
Primeira Forma Normal (1NF)
Segunda forma normal (2FN)
Terceira forma normal (3FN)
Existem outras formas normais (Elmasri,
Silberschatz)
47
1FN
Requer uma chave primária
Requer que todos os dados sejam atômicos
(indivisíveis)
Sem dados multivalorados
Remoção de redundâncias verticais
48
2FN
Todos os campos devem estar relacionados a
toda a chave, em caso de chaves compostas
49
3FN
Requer que todos os campos dependam diretamente
da chave primária e não de outros campos não chave
50
Exemplo
Tabela não tem chave primária
Nomes não são atômicos
Toda informação em uma única
tabela
Mais de um campo para telefone e
e-mail
Company, department, city, state,
zip são verticalmente redundantes
51
1FN
Tabela tem PK
Nomes são atômicos
Telefones e e-mails
não são mais
horizontamente
redundantes
Company, department,
city, state, zip não são
mais verticalmente
redundantes
52
2FN
53
3FN
54
“OVER-normalization”
55
“Desnormalizar”
Fazer normalização e depois monitorar as
consultas que estiverem lentas (comando
EXPLAIN)
Desnormalização pode ser necessária em
certas consultas (junções e ordenações)
Integridade dos dados pode ser mantida com
triggers
56
Como desnormalizar
Unir relacionamentos 1:n: casos em que uma
única coluna seria adicionada à tabela do n
para remover a necessidade da junção
Tabela Snapshot: criar uma tabela que
armazena um instantâneo dos dados que
precisam ser consultados
57
Atualizações
Recomenda-se atualizar o esquema via
diagrama e então sincronizar com o banco de
dados
Importante testar alterações em um ambiente
de desenvolvimento
Usar a ferramenta de sincronização
58
Exercício
Com base no que foi descrito, é possível
melhorar seus projetos, do hospital e da
seguradora?
59
SQL
DDL
DML
60
DDL
Criando tabelas com SQL
Create Table Estudante(
ID integer,
nome varchar(100),
coeficiente float)
Create Table Universidade
(nome varchar(100),
estado char(2),
classificacao integer)
61
Criação de tabela
create table nome_tabela
(nome_coluna tipo_dados,
nome_coluna2 tipo_dados,
nome_coluna3 tipo_dados,
chaves);
Exemplo:
CREATE TABLE empregado(
id INT NOT NULL,
nome VARCHAR(45),
data_nascimento DATE,
PRIMARY KEY (id));
62
CREATE TABLE
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-definition.html
63
Com workbench
64
Apagar tabela
DROP table tbl_name;
Não é possível desfazer
DROP table employees;
65
Mysql – linha de comando
mysql> mysql -uroot -psenha
mysql> show databases
mysql> show tables
mysql> use mysql
mysql> select user, host, password from user;
66
Mysql
Onde ficam os dados
Padrão:
C:/Documents and Settings/All Users/Application
Data/MySQL/MySQL Server 5.1/Data/
Linux: /var/lib/mysql
Pode-se obter esta informação com:
mysql> SHOW VARIABLES like 'data%';
67
DML
DML - Data Manipulation Language
SQL used PARA manipular os dados
Add
INSERT a ROW
Update
UPDATE 1-Many columns in ROW(s)
Delete
DELETE 1 to many Rows
SELECT: consultar
68
Inserção de dados
Insert into tbl_name (colname,..) values (val1,
val2);
INSERT INTO employees (emp_id,
first_name, last_name) VALUES (1,
'John', 'Smith');
INSERT INTO employees (emp_id,
first_name, last_name) VALUES (2,
'Susan', 'Taylor');
INSERT INTO employees (emp_id,
first_name, last_name) VALUES (3,
'Jessie', 'Jones');
69
Inserção de dados
OR – MySQL SPECIFIC
INSERT INTO employees (emp_id,
first_name, last_name)
VALUES (1, 'John', 'Smith'),
(2, 'Susan', 'Taylor'),
(3, 'Jessie', 'Jones');
70
Inserção de dados
71
Colunas DATE
O formato de um valor DATE é 'YYYY-MM-DD'
De acordo com o padrão SQL, nenhum outro
formato é permitido. Você deve usar este
formato em expressões UPDATE e na cláusula
WHERE de instruções SELECT.
Exemplo:
mysql> SELECT * FROM nome_tabela WHERE
date >= '1997-05-05';
http://dev.mysql.com/doc/refman/4.1/pt/using-date.h
72
Constantes para DATE
String: 'YYYY-MM-DD' ou 'YY-MM-DD'
Qualquer caractere de pontuação pode ser usado
como delimitador. Exemplos: '2012-12-31',
'2012/12/31', '2012^12^31', '2012@12@31'
String sem delimitadores 'YYYYMMDD' ou
'YYMMDD'
desde que a string faça sentido como data.
Exemplo: '20070523' e '070523' são interpretadas
como '2007-05-23', mas '071332' é inválido
73
Constantes para DATE
Como número YYYYMMDD ou YYMMDD
desde que o número faça sentido como data.
Exemplo:19830905 e 830905 são interpretados
como '1983-09-05'.
http://dev.mysql.com/doc/refman/5.5/en/date-and-tim
74
Funções para datas
STR_TO_DATE(str,format)
Recebe string e retorna data
Exemplos:
mysql> SELECT
STR_TO_DATE('01,5,2013','%d,%m,%Y');
Resultado
-> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1,
2013','%M %d,%Y');
Resultado
-> '2013-05-01'
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-function
75
Funções para datas
DATE_FORMAT(date,format)
Formata o valor da data de acordo com a string
de formato
mysql> SELECT DATE_FORMAT('2009-10-04
22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04
22:23:00', '%H:%i:%s');
-> '22:23:00'
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-fu
76
Formatos
77
Mais funções
CURRENT_DATE(), CURRENT_DATE,
CURDATE()
NOW(): data e hora atual
LAST_INSERT_ID(): autoincrement
USER()
VERSION(): versão do MySQL
LOWER(): para minúsculas
UPPER(): para maiúsculas
FORMAT(): casas decimais
78
Atualização de dados
UPDATE tbl_name
SET col_name1={expr1}
[, col_name2={expr2}] …
[WHERE where_condition]
UPDATE employees
SET first_name = 'Ana Maria'
where id = 100;
79
Atualização com Workbench
80
Consultas
Para selecionar todas as colunas *
SELECT * FROM employees;
Para especificar apenas algumas colunas
SELECT first_name, last_name
FROM employees;
81
SELECT
Para limitar o número de linhas do resultado
> SELECT * FROM employees LIMIT 10;
Para filtrar nomes > 'M'
> SELECT * FROM
`myexample`.`employees` WHERE
last_name > 'M';
82
SELECT
AND para combinar vários filtros (last names >
'M' and < 'ST')
SELECT *
FROM `myexample`.`employees`
WHERE last_name > 'M'
AND last_name > 'ST'
83
SELECT
OR
SELECT *
FROM employees
WHERE last_name < 'M' OR last_name >
'ST'
Substring
SELECT * FROM employees WHERE
last_name like '%s%' OR first_name
like 'J%';
84
Outros operadores
IS NOT NULL
IS NULL
DIV: divisão inteira
<> ou != : diferente
% ou MOD: módulo
<=, >=
http://dev.mysql.com/doc/refman/5.5/en/non-typed-o
85
SELECT e funções
Select now();
Select user(), version();
Select count(last_name) from employees;
Max(), min(), avg(), sum()
Select COUNT(last_name) from employees
Where CHAR_LENGTH(last_name) > 5;
86
Junções
87
Junções
SELECT employees.*, dept.*
from employees, dept
WHERE employees.dept_id = dept.dept_id;
88
Exercícios consultas
Criar no workbench o projeto do congresso,
apresentando neste material
Salvar modelo do diagrama e scripts sql de
criação do esquema
Inserir dados em todas as tabelas
Realizar as seguintes consultas:
1) Buscar descrição das matérias votadas no dia
01-05-2011
2) Buscar nome dos congressistas do partido PV
3) Buscar nome da coligação de sigla MMM
89
Exercícios consultas
4) Buscar voto dado pelo congressista João na
matéria com a descrição ABCD
5) Buscar nome e data da última eleição dos
congressistas do PR
6) Buscar nome dos congressistas da coligação XYZ
que tenham sido eleitos mais de 2x
7) Buscar nome e número de vezes em que foi eleito
os congressistas da coligação XXX que são da região
nordeste
8) Alterar nome da coligação de código 1 para “Para
frente e avante”
90
Exercício projeto 1
A loja ABCXYZ comercializa produtos de limpeza. Os
produtos são catalogados em categorias. Cada
categoria tem um código, um nome e o número da
prateleira em que os produtos da categoria localizamse na loja. Cada produto pertence a apenas uma
categoria. São armazenados dados sobre os produtos
vendidos (código, descrição e preço). Também
armazena-se os dados do cliente que efetuou a
compra (CPF, nome, endereço, telefone), os dados da
compra realizada por cada cliente (número da nota
fiscal, data, valor total da nota), bem como cada
produto de uma compra, com suas respectivas
quantidades e o valor do item (preço unitário do
91
produto multiplicado pela quantidade comprada).
Exercício projeto 2
Uma delegacia de polícia precisa de um sistema
computadorizado para controlar seus dados. Este sistema
deverá catalogar os criminosos, as vítimas e as armas
utilizadas para praticar os delitos. Além de armazenar
dados pessoais dos criminosos, vítimas e armas, o banco
de dados também deve permitir saber quais crimes
determinado criminoso cometeu, que crimes determinada
vítima sofreu e quais armas foram utilizadas para atacar
cada uma das vítimas em cada crime. Estas informações
possibilitarão a emissão de relatórios e estatísticas de
acordo com as necessidades das autoridades judiciais.
Observe que em um crime podem ser utilizadas várias
armas para atacar várias vítimas. Um crime pode envolver
várias vítimas e vários criminosos.
92
Exercício projeto 3
O sistema refere-se ao controle de campeonatos de
futebol. Cada campeonato tem um nome, uma data de
início e uma data de término. Participam dos campeonatos
várias equipes. Cada equipe possui um nome, nome de
seu técnico, nome de seus jogadores, indicando se é
titular ou reserva e cor da camisa dos uniformes número 1
e 2. Deve-se relacionar com cada equipe, as informações
sobre a que país pertence. Cada país possui nome,
continente, população, tamanho em km quadrados, renda
per capita e condição (país desenvolvido, em
desenvolvimento ou subdesenvolvido). Devem ser
guardadas informações sobre as partidas realizadas.
Sobre cada partida deve-se guardar as equipes
participantes, o placar, o nome do juiz principal, a
93
localização do campo (cidade) e o nome do campo.
Trabalho – aula 1 (35% da nota da
disciplina)
Elaborar os diagramas do projeto de banco de
dados com o workbench (exercícios 1, 2 e 3)
(entregar imagem e arquivo .mwb)
Entregar scripts DDL de criação das tabelas
Entregar scripts DDL de inserção de dados
Prazo para entrega: 09 de junho
94