Conteúdo Online - Implementação de banco de dados - aulas 1 a 10

Prévia do material em texto

Aula 01 
 
 
 
 
 
 
 
 
 
 
 
 
Modelo relacional 
O modelo relacional tem se mostrado dominante nos SGBD comercial. De fato os principais SGBD como oracle, 
SqlServer, SYBase, PostgreeSQL, MySQL utilizam o modelo relacional ou sua extensão o modelo objeto relacional. O 
modelo relacional foi propost pelo Dr. Codd em 1970, baseando-se em conceitos matemáticos para proporcionar uma 
representação confiável para banco de dados. O modelo relacional se propõem a representar os dados em uma colação 
de relações. Desta forma as relações são vistas como tabelas de valores, onde cada linha representa uma coleção de 
dados relacionados. Estes valores podem ser interpretados como fatos descrevendo uma instancia de uma entidade ou 
de um relacionamento do mundo real. Os nomes da tabela e das colunas são utilizados para facilitar a interpretação dos 
valores armazenados em cada linha da tabela. 
 
AULA 2 
 
 
 
 
 
 
PROJEÇÃO 
 
 
 
 
 
 
 
 
 
PRODUTO CARTESIANO 
 
 
JUNÇÃO 
 
 
 
 
 
 
 
OPERAÇÃO DE CONJUNTOS 
 
OPERAÇÃO DE DIVISÃO 
 
 
 
AULA 3 
A Linguaqem SQL como Padrão. SQL quer dizer Structured Query Language e é o padrão mundial de acesso às bases 
de dados relacionais. 
DDL - Create, Alter, Drop, Rename, Truncate. Permitem a criação e definição de objetos como tabelas, views e outros 
objetos no banco de dados. 
DRL – Select. É o comando mais comum do SQL. Utilizado amplamente para recuperação dos dados de uma base. 
DML – Insert, Delete, Update. Comandos de manipulação dos dados. Usados nas aplicações que mantêm a base de 
informações com inserções, atualizações e deleções de dados. 
DCL – Grant, Revoke. São utilizados para atribuir ou remover direitos de acesso a objetos do banco de dados e suas 
estruturas. 
Controle de transações - Commit, Rollback, Savepoint. Faz parte do padrão SQL a efetivação total, parcial ou o 
abandono de uma transação através da utilização destes comandos. 
 
 
 
 
Adicionando campo: ALTER TABLE TABELA ADD CAMPO VARCHAR(30) NOT NULL 
Comando: Describe tabela OU desc tabela, descreve a tabela 
Alterando um campo: ALTER TABLE DEPARTAMENTO MODIFY DESCRICAO NULL 
Remover coluna: alter table <nome_tabela> drop column <nome_coluna>; 
 
Mais Constraints 
Até agora utilizamos apenas as constraints NOT NULL e PRIMARY KEY, vejamos agora as Constraints UNIQUE e FOREIGN 
KEY. 
Campos únicos: Esta constraint determina que uma coluna não pode ter valor repetido; não significando, entretanto, 
que deva possuir valor, ou seja, que tenho preenchimento obrigatório. Isso significa que podemos ter mais uma linha 
nula em uma coluna única. Para estabelecer essa restrição, estabelecemos UNIQUE como restrição da coluna, por 
exemplo: CPF char(11) UNIQUE, 
Reforçando a integridade referencial com chaves estrangeiras: 
Os relacionamentos entre tabelas são criados, gerando-se chaves estrangeiras (foreign key – FK) nas tabelas FILHO que 
referenciam colunas chaves nas tabelas PAI. 
Para estabelecer essa restrição, acrescentamos REFERENCES definição da coluna como exemplo: 
ID_DEPTO number(7) References Departamento(ID), 
Onde: 
Id_depto é o nome da coluna. 
Number(7) o tipo da coluna. 
References identifica a restrição de chave estrangeira. 
Departamento é o nome da tabela para onde aponta a chave estrangeira. 
(ID) é coluna da tabela departamento apontada pela chave estrangeira. 
 
 
Constraint de Colunas e Tabelas 
As constraints podem ser definidas junto com a coluna. ou separadamente (no final do comando create table ou com o 
comando alter table). As constraints not null só podem ser definidas junto com a definição da coluna. As constraints de 
tabela são utilizadas principalmente para criar constraints compostas. onde duas ou mais colunas fazem parte da 
constraint. como por exemplo, chaves primárias compostas. 
Vejamos um exemplo: 
A tabela Turmas possui uma chave primary composta pelas colunas CODIGO_TURMA e CODIGO_CURSO. O comando 
para sua criação é: 
CREATE TABLE TURMAS (CODIGO_TURMA NUMBER(6), CODIGO_CURSO NUMBER(3), CODIGO_FUNCIONARIO 
NUMBER(6), DATA_INICIO DATE, DATA_FIM DATE , SALA NUMBER(2), PRIMARY KEY (CODIGO_TURMA, 
CODIGO_CURSO) ); 
Incluindo uma Foreign Key numa Tabela Existente 
Também podemos incluir a constraint de Foreign Key após a criação da tabela, para tal, basta especificar a adição da 
constraint no comando ALTER TABLE. 
Para exemplificar, vamos primeiro criar a tabela CLIENTE, utilizando o comando abaixo: 
CREATE TABLE CLIENTE ( ID NUMBER(7) PRIMARY KEY, NOME VARCHAR2(40) NOT NULL, VENDEDOR NUMBER(7)) 
A tabela foi criada, mas a coluna Vendedor deveria ser uma chave estrangeira para a tabela Empregado na coluna ID, 
podemos dar o seguinte comando de ALTER TABLE: 
ALTER TABLE CLIENTE ADD FOREIGN KEY (VENDEDOR) REFERENCES EMPREGADO(ID) 
---------- 
Trocar nome de tabela 
RENAME TABELA TO NOVATURMA. 
Dropar tabelas 
Drop table tabela 
Se você aplicar este comando em uma tabela que possua filhos, como por exemplo, a tabela Departamento irá 
gerar um erro ( veja figura). O erro ocorre porque essa tabela é referenciada por uma foreign key de outra 
tabela, que ficaria inconsistente caso esta fosse removida; o banco de dados, muito acertadamente, não 
permite este comando DROP TABLE. Caso seja realmente necessário eliminar essa tabela, posso solicitar que 
todas as constraints a ela vinculadas sejam eliminadas também, para isso acrescentamos CASCADE 
CONSTRAINTS ao comando. 
DROP TABLE DEPARTAMENTO CASCADE CONSTRAINTS 
Resumo: 
 CREATE TABLE X_EMPR( ID NUMBER(5) PRIMARY KEY,NOME VARCHAR2(40) NOT NULL,CPF CHAR(11) NOT NULL 
NIQUE,SALARIO NUMBER(9,2),CNH CHAR(15) UNIQUE,ID_DEPTO NUMBER(5) REFERENCES 
X_DEPTO(ID),ID_GERENTE NUMBER(5)) 
 ALTER TABLE X_EMPR MODIFY (SALARIO NOT NULL) 
 ALTER TABLE X_EMPR ADD FOREIGN KEY (ID_GERENTE) REFERENCES X_EMPR(ID) 
 DROP TABLE X_EMPR 
 DROP TABLE DEPARTAMENTO CASCADE CONSTRAINTS 
 
Aula 04 
Select uma tabela 
 
 
 
OBS: 
 
 
 
Aula 05 
Ordenação: Order by. 
Funções de grupo: 
 Group by; 
 AVG(x): Retorna o valor médio das colunas, ignora nulos; 
 MAX 
 MIN 
 SUM 
 Count(x): retorna o numero de linhas de uma tabela, ignora valores nulos. 
 Count(*): Retorna o numero de linhas de uma tabela, considera valores nulos. 
Funcoes de grupo não podem estar no WHERE. Deve se usar HAVING. 
 
Aula 06 – JUNÇÃO 
 
 
Junção Cruzada – cross join 
Uma junção cruzada de tabelas, também chamada junção irrestrita de duas tabelas gera um resultado formado por 
todas as combinações possíveis de uma linha da primeira tabela com uma linha da segunda. Não existe uma condição de 
junção. Esse resultado é chamado produto cartesiano das duas tabelas. 
Por exemplo desejamos realizar a Junção Cruzada das tabelas Empregado e Departamento cujo conteúdo é mostrado na 
figura. 
Nesse caso, como a tabela Empregado possui 6 linhas e a tabela Departamento 3 linhas , o resultado final será 18 linhas 
que é 6 * 3 = 18 linhas, formadas por todas as combinações possíveis de empregado e departamento. Teríamos como 
resultado então esta tabela. 
select * from empregado cross join departamento; 
Junção interior -inner join 
A junção interior de tabelas [inner join]. conecta as duas tabelas e retorna apenas as linhas que satisfazem a condição 
de junção. Uma junção interior é chamada de equijoin quando as colunas são comparadas usando o =, e as duas colunas 
aparecem no resultado, mostrando dados redundantes, já que elas têm o mesmo valor. 
Junção exterior – outer joinou left join 
 FULL JOIN – as linhas de ambas as tabelas são incluídas, mesmo as que não estão relacionadas com a outra 
tabela. ~ 
 Auto-junção: inner join com a mesma tabela. 
dasda 
Aula 07 – Subconsulta e operadores de conjunto. 
Subconsulta é uma consulta dentro de um outro comando SQL que pode ser: 
 CREATE TABLE 
 CREATE VIEW 
 SELECT 
 INSERT 
 DELETE 
 UPDATE 
Exemplos: SELECT * FROM EMPREGADO WHERE ID_DEPTO = ( SELECT ID FROM DEPARTAMENTO 
WHERE UPPER(NOME) = ‘VENDAS’) 
 
 
Subconsultas correlatas 
SELECT * FROM EMPREGADO E WHERE SALARIO > (SELECT AVG(SALARIO) FROM EMPREGADO I WHERE ID_DEPTO = 
E.ID_DEPTO) 
 
 
 
UNION: retorna duas consultas sem repetição. 
UNION ALL: Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, mantendo todas 
as linhas duplicadas. 
INTERSECT: Resulta na interseção entre todas as linhas de duas ou mais tabelas participantes do INTERSECT, ou seja, 
apenas as linhas comuns entre ela: Não existe INTERSECT ALL 
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,30) 
INTERSECT 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR' 
MINUS: Resulta nas linhas existentes na primeira tabela, mas que não existem na segunda. As linhas comuns também 
não são resultantes. Não existe MINUS ALL 
Obs: ODER BY é apenas na ultima consulta. 
 
Aula 08 – OUTROS OBJETOS DE BANCO DE DADOS. 
 
 
 
 
 
 
 
 
 
A sintaxe do comando de criação de uma Sequence é: 
CREATE SEQUENCE sequence_name 
[ INCREMENT BY n ] 
[START WITH n] 
[MAXVALUE n I NOMAXVALUE] 
[MINVALUE n I NOMINVALUE] 
[CYCLE I NOCYCLE] 
[CACHE l NOCACHE] 
[ORDER I NOORDER ] 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
É importante observar que Índices são objetos atualizados pelo Oracle, em todas as operações de INSERT, UPDATE e 
DELETE na tabela indexada, portanto, se por um lado aceleram a pesquisa aos dados através do comando SELECT, por 
outro lado, quanto mais Índice tiver a tabela, maior é o tempo de atualização da mesma. 
 
 
 
Aula 09 - TRANSAÇÕES 
Após uma transação, o banco de dados deve continuar consistente. Para assegurar a integridade dos dados, um sistema 
de banco de dados deve ter as seguintes propriedades das transacoes. 
 Atomicidade: indivisibilidade, é a propriedade que garante que todas as operações de uma transação são 
refletidas corretamente no banco de dados ou nenhuma será. Uma transação é indivisível. 
 Consistência: a execução de uma transação isolada (ou seja, sem a execução de outra transação qualquer 
concorrentemente) preserva a consistência do banco de dados. 
 Isolamento: embora diversas transações possam ser executadas concorrentemente, o SGBD deve garantir que, 
para todo par de transações Ti, Tj, Ti tem a sensação de que Tj terminou suas operações, antes de Ti começar 
com as suas. Assim, cada transação não toma conhecimento de outras transações concorrentes no sistema. O 
sistema pode executar um pedaço de Ti parar e executar um pedaço de Tj, mas para as transações elas são 
únicas no sistema. 
 Durabilidade: depois de a transação completar-se com sucesso, as mudanças que ela faz no banco dedados 
persistem, até mesmo se houver falha no sistema. 
São as chamadas propriedades ACID das transações. 
 
 
Transações concorrentes 
Os sistemas de processamento de transação de banco de dados, normalmente permitem que diversas transações sejam 
executadas de modo concorrente. Permitir que essas transações concorram na atualização dos dados traz diversas 
complicações em relação à consistência dos bancos de dados. 
 Principal desvantagem das execuções concorrentes: 
o Assegurar a consistência de transações, exige trabalho adicional, é mais fácil assegurar as propriedades 
ACID em transações com execução seqüencial do que em execuções concorrente. 
 Principais vantagens de execuções concorrentes: 
o Uma transação consiste de diversos passos. Alguns envolvem atividades de E/S, outros, atividade de 
UCP; logo, atividades de E/S podem ser feitas em paralelo com o processamento de UCP; assim, o 
paralelismo entre atividades de E/S e atividades de UCP podem ser explorando para executar diversas 
transações em paralelo aumentando o “throughput” do sistema (um maior número de transações 
podem ser executadas num determinado tempo); 
o reduz o tempo médio de resposta para uma transação se completar após ser submetida, pois as 
transações curtas não precisam esperar que as transações longas terminem para serem iniciadas. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Aula 10 – Execução de comandos e otimização 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
JUNÇÕES – Os algoritmos classicos utilizados em juncoes são. 
 
 
 
 
Fases do processamento de comandos SQL no oracle 
 
 
 
 
O Otimizador Oracle 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
FULL TABLE SCAN: O número de blocos visitados para executar um full table scan é 80/8 = 10; 
PESQUISA NO INDICE I_COD_DEPART: Este é um índice não-único, logo a seletividade é calculada como 0,17 (6 valores 
distintos para codigo_depart). O custo total é 0,17 * 80 = 14; 
PESQUISA NO INDICE I_MATRIC: Este é um índice único porém em um intervalo limitado de valores. A seletividade é 
calculada como 0,19(intervalo da condição 250 – 100 dividido pelo intervalo do índice 800 – 1). O custo total é 0,19 * 80 
= 16; 
 
 
FULL TABLE SCAN: Este caminho está disponível para todos os comandos SQL. Seu rank é 15; 
PESQUISA NO INDICE I_COD_DEPART: Este caminho está disponível por causa da condição ‘codigo_depart = 3’. Seu 
rank é 9 (single-column index); 
PESQUISA NO INDICE I_MATRIC: Este caminho está disponível por causa da condição ‘matricula BETWEEN 100 AND 
250’. Seu rank é 10 (bounded range search); 
 
 
Opção COMPUTE STATISTCS: Você calcula estatísticas exatas com esta opção. Ela bloqueia a tabela e executa um full 
table scan. Pode levar muito tempo se a tabela é muito grande. Você jamais deverá usar esta opção enquanto usuários 
estão logados e acessando a tabela. 
Opção ESTIMATE STATISTICS: Você somente estima estatísticas com esta opção. Contudo, se você utilizá-la com uma 
proporção de dados aceitável, ela pode ser considerada tão realista quanto a opção COMPUTE STATISTICS. 
Opção DELETE STATISTICS: Limpa todas as estatísticas com esta opção. Não é necessário usá-la antes de reanalisar um 
objeto.