• BANCO DE DADOS: • Conceitos e Fundamentos: • SGBD



BANCO DE DADOS:
Conceitos e Fundamentos:
 Sistemas de Arquivos armazenam registros permanentes de forma desorganizada,
espalhada, já os Sistemas de BD armazenam de forma a possibilitar:





compartilhamento de dados em entre diferentes aplicações;
controle de redundância
integridade dos dados (problema: um funcionário que trabalhou 400h na semana)
concorrência
segurança dos dados.
 Arquitetura de 3 níveis: possibilita o entendimento (ao usuário) em separado das 3
aplicações de BD:



nível de visão (conceitual): como um usuário enxerga, apenas com ideias iniciais
sobre o dado contexto;
nível lógico (externo): a representação, contendo a estrutura do BD (projeto do BD);
nível físico (interno): como o armazenamento ocorre de fato.
 Catálogo de dados servem para armazenar esquemas internos, conceituais e
externos, mapeamentos e metadados.
 Esquema de BD é o projeto geral de um BD, após as definições necessárias,
contendo todos os elementos (tabelas, restrições, visões e domínios);
 Instância é um conjunto de informações constantes em um BD, em dado momento;
 Transação é um programa em execução ou processo que inclui um ou mais acessos
ao banco de dados, que efetuam leitura ou atualizações de seus registros. tem como
propriedades:





Atomicidade: tem que ser atômica, ou executa por completo ou não executa;
Consistência: tem que agir sem bagunçar o BD, de forma consistente;
Isolamento: em 2 transações paralelas, uma tem que estar isolada da outra;
Durabilidade: sempre que um BD for mudado, não pode ser perdido. Uma vez que
uma transação tenha sido executada, suas atualizações tornam-se permanentes.
SGBD:
 Conjunto de programas com o intuito de efetuar gerenciamento de uma base de
dados, de forma a permitir: criar, manter e compartilhar um banco de dados.
 Uma das funções do SGBD é limitar dados redundantes em múltiplos sistemas.
 Na orientação a objetos, os bancos de dados projetados para trabalhar com o
"pensamento OO", considera dados como sendo de 2 formas:



Dados persistentes: são armazenados fora de uma transação e sobrevivem
às atualizações. Persistência é a capacidade de um objeto persistir por meio
de diferentes chamadas do programa;
Dados transientes: são somente válidos dentro de um programa ou
transação, que tão logo terminam, os dados são perdidos .
Modelo Relacional:
 Oriundo da teoria dos conjuntos, consideram que para os usuários não importa
muito onde os dados estão e nem como eles estão;

Os outros modelos de dados são:

Hierárquico: em forma de árvore, com navegação do topo para as folhas e da direita para a esquerda;

Em rede: o formato de árvore sai e o que ficam são grafos... relacionamentos N:M podem ocorrer, diferentemente do hier.

modelo OO: são organizados numa hierarquia de tipos, com dados armazenados como objetos, podendo conter referências a outros
objetos e só podem ser acessados por outros métodos.

 A estratégia de modelagem pode ser do tipo bottom-up, inside-out ou top-down.



bottom-up: (Baixo-Acima) A partir da análise de requisitos, por exemplo, são
levantadas necessidades e nelas se baseiam todas as ideias para o BD;
inside-out: Parte-se dos conceitos mais importantes e gradativamente se vai
adicionando conceitos periféricos. Inicia-se com uma entidade importante (por
exemplo, que tenha muitos relacionamentos) e a partir dela se encontra outras
entidades com que se relaciona;
top-down: (Cima-Abaixo) Parte-se dos conceitos mais abstratos, para depois ir
chegando nos mais detalhados, por exemplo pega-se os conceitos corporativos da
organização para ir filtrando até os de interesse prático.
 Busca descrever as "coisas" do mundo real, em tabelas/entidades, com os detalhes:
 Entidades ou Tabelas: partes de um assunto segregado de forma
organizada, que mostrará nas linhas as instâncias e nas colunas os atributos
 Relacionamento: elo de ligação entre as entidades
 Atributo: propriedades de cada "coisa", colunas de uma tabela. um atributo é
um dado associado a cada ocorrência dentro de uma entidade. Para





representar chaves primárias, deve ser destacado um dos atributos ou mais,
que seja monovalorado, ou seja, que aceite apenas um valor por ocasião.
Registro ou Tupla: dados cadastrados, linhas de uma tabela
Domínio: conjunto de valores atômicos que um atributo pode assumir
Índice: elementos que auxiliam a localização rápida de um registro, tupla...
Chaves: podem ser do tipo PRIMARIA ou ESTRANGEIRA. A PRIMARY
KEY – PK identifica de forma única um campo em uma tabela, de forma a
não deixar cadastrar valores repetidos. Chave estrangeira, FOREING KEY –
FK, servem em uma 2ª tabela, para se referenciar a uma chave primária da
1ª tabela, efetivando-se assim a ligação entre as 2.
Dicionário de dados: estrutura física que armazena metadados sobre a estrutura das tabelas (metadados são dados sobre dados)
ENTIDADES FRACAS são as que não possuem atributos que sirvam como PK.
a cláusula CHECK faz a análise do que pode, dentro do domínio
Atributos podem ser DERIVADOS, quando se originam de outro.
Cancelar atualizações ainda não confirmadas: ROLLBACK TO SAVEPONTI
VIEW: materializadas, são aquelas armazenadas em BD
uma SUPERCHAVE pode possuir um nº maior de ATRIBUTOS do
que o mínimo necessário para formar a CHAVE CANDIDATA

Normalização
esse relacionamento indica PARTICIPAÇÃO TOTAL de um
conjunto de entidades em um conjunto de relacionamentos,
já o outro relacionamento ocorre de forma parcial
 Uma técnica de decomposição utilizada para busca de uma forma mais organizada e
consistente em tabelas de um Banco de Dados, evitando redundâncias e anomalias
 1ª Forma Normal - 1FN:
 Parte da definição formal de uma relação no modelo relacional básico;
 Não pode haver dados repetidos em sua estrutura;
 Tem que segregar em tabelas as colunas multivaloradas.
 2ª Forma Normal - 2FN:
 Deve estar na 1FN e todo atributo não chave tem que ser totalmente;
 Dependente da chave primária;
 Não pode existir uma dependência parcial da chave primária.
- Neste exemplo, há 2 chaves primárias;
- Os atributos NomeProd e VlrUnit se referem ao
CodProd, e não tem nada a ver com o
NumeroVenda;
- Os outros atributos é que tem relação com
aquela chave primária, portanto, precisa-se
aplicar a 2ª forma normal;
- Assim, foram separados os atributos em 2
tabelas, corrigindo o problema.
 3ª Forma Normal - 3FN:
 Deve estar na 2FN e nenhum atributo não chave primária pode deixar de ser
dependente da chave primária;
 Atributos não chave não podem se referir a outros atributos não chave.
 FORMA NORMAL DE BOYCE-CODD: uma tabela está assim se, e somente se, cada determinante é
uma chave candidata.

Modelo Entidade-Relacionamento:
 Um BD relacional é composto por um conjunto de relações e tuplas relacionadas. No
esquema de um banco de dados relacional, você define a estrutura por meio de um
conjunto de esquemas de relações e restrições de integridade.
 O modelo relacional compreende:
 RELAÇÃO  TABELA ; ATRIBUTO  COLUNA ; TUPLA  LINHA
 Processo de Montagem do DER:

Há casos em que o relacionamento pode receber atributos, por exemplo,
qual o relacionamento que há entre as entidades engenheiro e projeto?







Observações:
não pode haver atributo com mesmo nome de entidades;
o relacionamento tem um nome simples;
atributo é uma característica comum a todas as ocorrências da entidade;
atributo não deve ser no plural;
atributo geralmente em minúsculas;
É necessário que se defina a Cardinalidade. Cardinalidade é o número de entidades ao qual outra entidade pode estar associada via relacionamento.


Um para um:
(chave estrangeira em uma das duas)

Um para muitos:

Muitos para muitos:
(chave estrangeira fica na direção de muitos)
(requer a criação de uma nova tabela para representar tal relacionamento.
Uma nova entidade, como por exemplo: LOTAÇÃO_PROJETO)


É possível ainda que sejam necessários Especialização/Generalização
Controle de Concorrência:
 Restrições no BD, significam os limites do modelo do BD. Podem ser:
 Restrições de Integridade: nenhum dos atributos da chave primária po ter
valor nulo, garantindo a unicidade de uma tupla; Podem ainda ser:



integridade de domínio: o valor de um campo é do tipo definido;
integridade do campo vazio: o valor não pode ser nulo;
integridade referencial: os valores dos campos que são chave estrangeira,
devem aparecer na tabela de origem como PK.
IMPASSE: no controle de concorrência, uma transação espera
liberação de um recurso, que foi bloqueado por outra transação, que
por sua vez aguarda por um recurso bloqueado por aquela transação.






BLOQUEIO COMPARTILHADO: transação pode ler, mas não pode escrever.
Restrições de domínio: atributo pode assumir somente um tipo de valor
Restrições de Chave: cada linha da tabela tem que ser diferente uma da
outra. Jamais posso ter 2 linhas totalmente iguais, em uma mesma tabela
Restrições semânticas: (para não quebrar regras de negócio) ex.: um
funcionário não pode trabalhar mais de 24h/dia
Restrições de Estado: determina um estado válido que um BD deve ter.
Restrições de Transição: determina tratamento de mudanças do estado do BD
Restrições em razão da cardinalidade: determina que o número máximo de
instâncias de relacionamento seja aquele que foi modelado (1:N, N:N...)



Restrições por gatilhos (triggers) ou operações de tratamento de violação de
restrições: restrições impostas por triggers ou por operações de delete,
insert ou update
Restrições em razão da participação: também chamada de restrição de
cardinalidade mínima determina se a existência de uma entidade depende
de sua existência relacionada à outra entidade por meio do tipo do
relacionamento. Ou seja, determina um número mínimo de instâncias de
relacionamento em que cada entidade pode participar.
SQL - LINGUAGEM DE CONSULTA ESTRUTURADA:
 É uma linguagem normalizada, baseada no modelo relacional (só existe no modelo
relacional), que permite a comunicação com a base de dados, podendo ser
embutida nas principais linguagens de programação.
 É declarativa, ou não procedural, ou seja, os detalhes de implementação do código
digitado ficam por conta do SGBD. As LPs que não são declarativas, são chamadas
de procedurais, em que tem se que explicar como será feito, etc...
 É amplamente utilizada, por vários SGBDs, independentemente de fabricantes,
oferecendo portabilidade entre sistemas, comandos em inglês...
 Pode ser separada em DDL (Linguagem de definição aos dados) ou DML
(linguagem de manipulação dos dados).
 DDL é um conjunto de comandos para definir a estrutura de BD:
 create
TIPOS DE INDEXAÇÃO: ordenado ou por hash
 drop
CANALIZAÇÃO: tipo de técnica que reduz o nº de arquivos temporários, nas consultas
 alter
 cláusulas: constraint, foreign key, primary key, rename, deferrable, not
deferrable, initially immediate, initially deferred
 DML é um conjunto de comandos para definir os dados manipulados no BD:
 select, insert, delete e update.
 Há entendimentos ainda sobre outras 3 siglas, dentro da DML:
 DQL: data query laguage, que compreende o SELECT;
 DTL: data transaction language, para transações (COMMIT,
ROLLBACK e SAVEPOTIN);
 DCL: data control language, para comandos relativos a permissões
ou privilégios (GRANT e REVOKE).
 Principais comandos:
 CREATE table <tabela>  cria tabela (com o nome dado)
 DROP table <tabela>  apaga toda a tabela (de cujo nome)
 ALTER table <tabela>  altera estrutura da tabela
 INSERT INTO <tabela> values <registro>  insere na tabela (de nome dado
e campos informados) os valores (tal, tal, tal e tal)
 UPDATE <tabela> SET <valor> where <condição>  altera valor informado
na tabela tal, para o novo valor tal, onde ocorra assim assim e assado;
 DELETE <tabela> where <condição>  deleta tais valores, onde...;
 SELECT <atributos> from <tabela>  faz uma consulta ao banco de dados.
 TRUNCATE table <tabela> ...  deleta todas as linhas de uma tabela
 SQL GRANT...  usado para prover acesso ou privilégios para os usuários
no BD
 REVOKE...  serve para remover permissões e privilégios de acesso ao BD
RESTRIÇÕES:
- not null: restrição imposta para não aceitar valores nulos
- default: para inserir um valor padrão
- unique: colocada para impor que naquele atributo não haja valores repetidos
Ex.: em uma tabela PESSOA havia os atributos CPF e TITULO, ambas chaves candidatas. Se CPF tiver
sido a PK, cabe colocar UNIQUE na outra, como forma de fortalecer a integridade.
PRIVILÉGIOS DE ACESSO:
- GRANT <privilegio> ON <objeto> TO <usuario>  isto concede acesso
- REVOQUE...  serve para retirar aquele acesso dado.
 Exemplos de comandos:
 Criação de tabela:
CREATE TABLE clima (cidade varchar(80), temp_min int,
temp_max int, data date );
- Não Aceitar Nulos cidade varchar(0) NOT NULL
- Valores Únicos na Coluna cidade varchar(0) UNIQUE

Inserir dados nas linhas da tabela:
INSERT INTO clima VALUES ('Marabá' , 29 , 34 ,
'2013-11-29' );
obs.: O tipo BOOLEAN, pode ser TINYINT !!
obs.: O tipo ENUM dispõe de "lista prévia"






Selecionar dados nas linhas da tabela:
- tudo da tabela SELECT * FROM clima;
- com 1 condição  SELECT * FROM clima WHERE cidade='Boa Vista';
- com 2 condições  SELECT * FROM clima WHERE cidade='RR' AND temp_min > 30;
- sem linhas duplicadas SELECT DISTINCT cidade FROM clima;
- em ordem  SELECT DISTINCT cidade FROM clima ORDER BY cidade;
- Agregação (contar - count, somar - sum, média - avg, máximo - max e mínimo - min):
SELECT MAX(temp_min) FROM clima;  37
SELECT SUM(temp_min) FROM clima;  313
(SUM soma todos os valores, diferentemente
de COUNT, que apenas conta os registros)
SELECT AVG(temp_min) FROM clima;  34,7
SELECT MIN(temp_min) FROM clima;  34
SELECT COUNT(temp_min) FROM clima;  9
Outras situações:
SELECT COUNT(DISTINCT cidade) from clima
(conta quantos campos CIDADE estão preenchidos, sem repetições, só os distintos).
SELECT * FROM clima LIMIT 0, 50  mostra tudo até o limite de 50 registros
SELECT * FROM clima LIMIT 10, 70  mostra todos, no limite, da posição 10 até a posição 70
- Usando o GROUP BY:
SELECT cidade, MAX(temp_min) FROM clima
GROUP BY cidade;
(vai indicar por exemplo, em cada cidade,
qual foi o máximo alcançado de temp_min,
agrupando-se por cidade)
(Se quisesse por exemplo, saber de cada temp_min,
qual foi a cidade, seria assim)
- Usando HAVING:
SELECT cidade, MAX(temp_min) FROM clima
GROUP BY cidade HAVING max(temp_min) < 29
(Pega a mesma consulta anterior,
"tendo" aquela condição)
(só pode haver HAVING, dentro de GROUP BY
- Usando o ORDER BY:
SELECT cidade FROM clima GROUP BY cidade
ORDER BY cidade ASC
(vai indicar em ordem crescente)
SELECT cidade FROM clima GROUP BY cidade
ORDER BY cidade DESC
(vai indicar em ordem decrescente)
- Usando LIKE:
SELECT * FROM clima
 ... where NOME LIKE 'A%'  começam com A
 ... where NOME LIKE '%A'  terminam com A
 ... where NOME LIKE '%A%'  tenham A em qualquer posição
 ... where CELULAR LIKE '8%' OR TELEFONE LIKE '8%'  todos, com um campo
CELULAR começando por 8 ou no campo TELEFONE, começando por 8
 Existe ainda o NOT LIKE, para o contrário.
- Outras formas de seleção com WHERE:
igual  SELECT * FROM clima WHERE temp_min = 37;
diferente  SELECT * FROM clima WHERE temp_min <> 37;  ou o sinal !=
ou  SELECT * FROM clima WHERE temp_min < 37 OR cidade LIKE '%M';  ou o sinal ||
<=  SELECT * FROM clima WHERE temp_min <= 37;
>=  SELECT * FROM clima WHERE temp_min >= 37;
=  SELECT * FROM clima WHERE temp_min = 37 AND cidade LIKE '%M';  ou o sinal &&
verdadeiro  SELECT * FROM clima WHERE campo IS TRUE;
falso  SELECT * FROM clima WHERE campo IS NOT TRUE;
só nulos  SELECT * FROM clima WHERE campo IS NULL;
só não nulos  SELECT * FROM clima WHERE campo IS NOT NULL;
num intervalo  SELECT * FROM clima WHERE temp_min BETWEEN 27 AND 34
fora do intervalo  SELECT * FROM clima WHERE temp_min NOT BETWEEN 27 AND 34
- SELECT dentro de SELECT:
SELECT * FROM clima WHERE temp_min > (SELECT AVG(temp_min) FROM clima;
(Serão selecionados tudo da tabela clima, desde que temp_min seja maior do que a média
entre todas as temp_min da tabela clima)

Alterar dados nas linhas da tabela:
UPDATE clima SET temp_max = temp_max - 2 WHERE data > '2001-11-28';

Excluir dados das linhas da tabela:
DELETE FROM clima WHERE temp_max = 22;

Chaves primárias na tabela:
CREATE TABLE cliente (
id INT NOT NULL,
cpf VARCHAR(20) UNIQUE,
nome VARCHAR (75),
PRIMARY KEY (cpf)

)
Chaves estrangeiras na tabela:
CREATE TABLE cliente (
id INT NOT NULL,
cpf VARCHAR(20) UNIQUE,
nome VARCHAR (75),
PRIMARY KEY (cpf)
FOREIGN KEY (id) REFERENCES usuario(id)  o "id" desta tabela, pega o "id" de uma
outra tabela, chamada "usuario"
)
- Obs.: Este caso acima trata da cardinalidade de 1:1;
- Poderia ainda ser de 1:N, e em SQL ficaria um campo "id", mas a FOREIGN KEY vem para
um outro campo criado. Exemplo: FOREIGN KEY (banco_id) REFERENCES banco(id), que
indica que no campo desta tabela chamado "banco_id" virá o que houver no campo "id" da
tabela "banco";
- Se fosse uma cardinalidade de N:N, uma 3ª tabela sendo criada, o que ocorre é que na
tabela resultante, vai uma FK de cada tabela originária. Por exemplo, as 2 tabelas "alunos" e
"turmas" tem relação de N:N e portanto é criada uma tabela chamada "alunoTurma", ficando
assim: FOREIGN KEY (aluno_matr) REFERENCES aluno(id),
FOREIGN KEY (turma_num) REFERENCES turma(id).

Joins: servem para extrair informações de 1 ou mais tabelas em um único
conjunto de resultados, com base nos relacionamentos envolvidos.
 2 Tabelas foram criadas, povoadas com dados e a JOIN agora serve para extrair dados das 2 tabelas em conjunto.
 Mas neste caso, os dados foram apenas jogados ali. Sem ordenação.
 Se for utilizada a instrução ON, fica:
 Neste caso abaixo, com o INNER JOIN é mostrado somente os dados que se quer, sem maiores preocupações.
 Neste caso ao lado, com o uso do LEFT JOIN, é possível ver todas os nome de marcas, e mesmo que em um destes não haja
qualquer registro correspondente, é automaticamente preenchido com NULL. Ele pega todos os dados da esquerda, sem se
preocupar com a parte da direita, preenchendo ali com NULL.
.
 Neste caso ao lado, com o uso do RIGHT JOIN, é possível ver todas os nome de marcas, em situação contrária. Ele pega todos
os dados da direita que existem, sem se preocupar com a parte da esquerda, preenchendo ali com NULL.
 Um último caso de JOIN, é o FULL JOIN, que considera todas as da esquerda e da direita, preenchendo o que for vazio com
NULL.

Gatilhos (triggers): definem um conjunto de ações a serem executadas quando ocorre um evento de
BD em uma determinada tabela. Pode ser uma operação de exclusão, inserção ou de atualização. Por
exemplo, se for definido um gatilho para exclusão em uma determinada tabela, a ação do gatilho
ocorre sempre que se remove uma ou mais linhas da tabela. Junto com as restrições, os gatilhos
podem ajudar a impor regras de integridade com ações como exclusões ou atualizações em cascata.
Os gatilhos também podem realizar várias funções como emitir alertas, atualizar outras tabelas, enviar
e-mail, e outras ações úteis. Pode ser definido qualquer número de gatilhos para uma única tabela,
inclusive vários gatilhos para a mesma tabela para o mesmo evento. O gatilho não precisa residir no
mesmo esquema da tabela para a qual é definido. Se for especificado um nome de gatilho qualificado,
o nome do esquema não poderá começar por SYS.
CREATE TRIGGER nome-do-gatilho
{ BEFORE | AFTER }  EM QUE OCASIÃO?ANTES OU DEPOIS?
{ INSERT | DELETE | UPDATE [ OR …] }  EM QUE EVENTO?
ON nome-da-tabela  ONDE SERÁ UTILIZADA?
[FOR EACH { ROW | STATEMENT }]
EXECUTE PROCEDURE
 COMANDOS SQL...
nome-da-função

Os gatilhos são definidos como: BEFORE (antes) ou AFTER (depois). Os gatilhos BEFORE disparam antes das
modificações da instrução serem aplicadas, e de qualquer restrição ser aplicada. Os gatilhos AFTER disparam
após todas as restrições terem sido satisfeitas e as alterações terem sido aplicadas à tabela de destino. Tanto o
gatilho BEFORE, como o AFTER pode ser tanto de linha (ROW) quanto de instrução (STATEMENT).

O gatilho é disparado por um dos seguintes eventos do Banco de Dados, dependendo de como foi definido:
INSERT, DELETE, UPDATE. O gatilho pode ser especificado para disparar antes de tentar realizar a operação
na linha ou após a operação. Se o gatilho for disparado antes do evento, o gatilho pode evitar a operação para a
linha corrente, ou modificar a linha sendo inserida (para as operações de INSERT e UPDATE somente).

Tipos de Dados:













Strings de Caracteres:
 CHAR: especifica um número, já armazenando aquele tamanho em memória,
mesmo que não seja utilizado (acrescenta espaços nos campos vazios);
 VARCHAR: especifica um tamanho, que pode variar até aquele número
máximo, de forma a não preencher com espaços os campos vazios;
 CLOB: Character Large Object, que armazena grandes grupos de caracteres,
até o valor especificado
Strings Binários:
 BLOB: usado para armazenar dados não tradicionais,
como imagens, áudio e arquivos de vídeo
Números: INTEGER; SMALLINT; NUMERIC; FLOAT; DOUBLE
Date: Composto por ano, mês e dia: 2013-12-21;
Time: Composto por hora, minuto e segundo;
DateTime: Combina data e hora em um único tipo, com intervalo de datas;
TimeStamp: Engloba os campos date e time, mais 6 posições para a fração decimal
de segundos e qualificação opcional WITH TIME ZONE;
Bancos de Dados Distribuídos:
Coleção de várias bases, logicamente inter-relacionadas, distribuídos por uma rede, distantes
geograficamente, administrados separadamente, com interconexão lenta e replicação dos dados. Podem ser:
- Homogêneo: compostos pelos mesmos BDs
- Heterogêneo: compostos por mais de uma base de dados.
A replicação pode ocorrer das seguintes formas: SÍNCRONA (cada transação é dada como concluída quando
todos os nós confirmam) ou ASSÍNCRONA (o nó principal manda a confirmação e então manda a transação
aos demais nós)
A fragmentação dos dados pode ser:
- vertical: distribui uma relação em sub-relações, pré-definidos por um conjunto das colunas da relação original;
- horizontal: distribui conjuntos de tuplas entre os bancos
Vantagens: reflete a estrutura organizacional, autonomia local, maior disponibilidade, melhor performance,
econômico e modular
Desvantagens: complexidade, alto custo, segurança e difícil integridade.