Introduction to DataBase and SQL

Banco de Dados

Modelo Conceitual: Representa ou descreve a realidade do ambiente do problema, construindo-se numa visão global dos principais dados e relacionamentos.  Objetivo é descrever as informações de uma realidade.  Nesse momento não se tem preocupaçao com as operações de manipulação e manutenção dos dados.

Modelo Lógico: Feito a partir do modelo conceitual.  Ex: Relacional, hierárquica e rede  Descreve as estruturas contidas no BD.  Não leva em conta o SGBD.

Modelo Físico: Parte do modelo lógico.  Descreve as estruturas físicas de armazenamento, tais como tamanho de campos, indices, etc.  Utiliza-se a DDL(Data Definition Language) e considera o SGBD.

Projeto de Banco de Dados: É a modelagem através da abordagem Entidade-Relacionamento.

 1.2 Normalização

Top-Down: Após a definição de um modelo de dados, aplica-se a normalização.

Botton-up: Aplica-se a normalização como ferramenta de projeto do modelo de dados.

Objetivos:
   - Evitar que o DB de modificações não esperadas.  (ex: 3 regra)
- Minimizar o redesign do banco de dados quando houver extensões.
- Evitar vantagem de algum padrão de acesso.  NoSql são voltados para aplicações, então são voltados para um padrão específicio.

Primeira forma Normal: Cada ocorrência da chave primária deve corresponder a uma e somente uma informação de cada atributo, a entidade não deve conter grupos repetitivos (multivalorados).  Decompõe-se as entidades não normalizadas em tantas quanto for o número de conjunto de atributos repetitivos.  Nas novas entidades, a chave primária é a concatenação da chave primária da entidade original, mais os atributos do grupo repetitivo.

Dependência Funcional

                Total (completa): Ocorre quanto a chave primária for composta por vários atributos, ou seja, em uma entidade de chave primária composta de um único atributo não ocorre este tipo de dependência.

                Transitiva: Quando um conjunto de atributos A depende de outro atributo B que não pertence à chave primária.  Nesse caso, A é dependente transitivo de B.

Segunda forma Normal: Uma entidade não pode ter atributos com dependência parcial em relação à chave primária.  Deve-se verificar se algum atributo com dependência parcial em relação a algum elemento da chave primária concatenada.

Terceira forma Normal: Nenhum atributo de uma entidade possui dependência transitiva em relação a outro que não participe da chave primária, ou seja, não deve haver nenhum atributo intermediário entre a chave primária e o próprio atributo observado.  Deve-se criar uma nova entidade com esses atributos com dependência transitiva.  Também não deve haver atributo que seja resultado de algum calculo a partir dos atributos da entidade, o que caracterizaria dependência funcional.
Ex:
   Nome            titulo             email
  Anderson        Sr               email1@gmail.com

  Anderson        Sr               email2@gmail.com

SQL

Data Model 

Criação das Tabelas

SET FOREIGN_KEY_CHECKS = 0;
drop table if exists cliente , vendedor, produto, PEDIDO;
drop table if exists item_do_pedido;
SET FOREIGN_KEY_CHECKS = 1;


CREATE TABLE CLIENTE (codigo_cliente smallint not null unique,               nome_cliente   char(20),        endereco  char(30),        cidade   char(15),
   CEP            char(8),              UF             char(2),               CGC            char(14),           IE             char(20));

CREATE TABLE VENDEDOR (codigo_vendedor smallint not null unique, nome_vendedor   char(20),   salario_fixo  decimal,    faixa_comissao  char(1));

CREATE TABLE PRODUTO (codigo_produto    smallint not null unique,          unidade           char(3),           descricao_produto char(30),   valor_unitario    decimal);

CREATE TABLE PEDIDO (num_pedido      int not null unique,      prazo_entrega   smallint not null,           codigo_cliente  smallint not null,
 codigo_vendedor smallint not null,    
FOREIGN KEY (codigo_cliente) REFERENCES CLIENTE(codigo_cliente),
 FOREIGN KEY (codigo_vendedor) REFERENCES VENDEDOR(codigo_vendedor));


CREATE TABLE ITEM_DO_PEDIDO (num_pedido     int not null unique,  codigo_produto smallint not null unique,              quantidade     decimal,        
FOREIGN KEY (num_pedido) REFERENCES PEDIDO(num_pedido),
 FOREIGN KEY (codigo_produto) REFERENCES PRODUTO(codigo_produto));

Comandos

DROP TABLE <tabela>;

Select nome_cliente, endereco, CGC From cliente;

Listar o num_pedido, o codigo_produto e a quantidade dos itens do pedido com a quantidade igual a 35.

·          Select num_pedido, codigo_produto, quantidade from item_pedido where quantidade = 35;

Quais os clientes que moram em Niterói?

·          Select nome_cliente From cliente Where cidade = ‘Niterói’;

Listar os produtos que tenham unidade igual a ‘M’ e valor unitário igual a R$1,05.

·         Select descricao_produto From produto Where unidade = ‘M’ and valor_unitario = 1.05;

Liste os clientes e seus respectivos endereços, que moram em ‘SÃO PAULO’ ou estejam na faixa de CEP entre `30077000’e ‘3007900’.

·          Select nome_cliente, endereço From cliente Where (CEP >= ‘30077000’ and CEP <= ‘30079000’) Or cidade = ‘SÃO PAULO’;

Mostrar todos os pedidos que não tenham prazo de entrega igual a 15 dias.

·          Select num_pedido From pedido Where NOT (prazo_entrega = 15);

Operadores Between e NOT Between: Valores dentro de uma faixa sem o uso de =,<,>. 

Listar o código e a descrição dos produtos que tenham o valor unitário na faixa de R$0,32 até R$2,00.

·          Select codigo_produto, descricao_produto From produto where valor_unitario between 0.32 and 2.00;

Like, Not Like: Só para CHAR. Parecido com = e < >.  Utilizam “%”(substitui uma palavra) e  “_”(substitui um caractere) como papeis de curinga.

Listar todos os produtos que tenham a sua unidade começando por K.

·          Select codigo_produto, descricao_produto From produto Where LIKE ‘K_’;

Listar os vendedores que não começam por ‘Jo’.

·         Select codigo_vendedor, nome_vendedor From vendedor Where nome_vendedor NOT LIKE ‘Jo%’;

IN e NOT IN: Procura registros que estão ou não estão contidos no conjunto de valores fornecidos.  Minimiza o uso de =, <>, AND e OR

Listar os vendedores que são da faixa de comissão A e B

·          Select nome_vendedor From vendedor Where faixa_comissao IN (‘A’, ‘B’);

IS NULL e IS NOT NULL: Problemático por causa da implementação de cada SGDB

Mostrar os clientes que não tenham inscrição estadual.

·          Select * from cliente Where IE is null;

ORDER BY: Para apresentar o resultado ordenado.  Usam ASC e DESC para mostrar a forma de ordenação.

Mostrar em ordem alfabética a lista de vendedores e seus respectivos salários fixos.

·          Select nome_vendedor, salario_fixo From vendedor Order by nome_vendedor;

Listar os nomes, cidades e estados de todos os clientes ordenados por estado e cidade de forma descendente.

·          Select nome_cliente, cidade, UF From cliente Order by UF DESC, cidade DESC;

Cálculos: Criação de campos que não pertencem às tabelas, mas que sejam resultado de cálculo sobre campos.

Mostrar o novo salário fixo dos vendedores, de faixa de comissão ‘C’, calculado com base no reajuste de 75% acrescido de R$120,00 de bonificação. Ordenar pelo nome do vendedor.

·          Select nome_vendedor, novo_salario = (salario_fixo * 1.75) + 120 From vendedor Where faixa_comissao = ‘C’ Order by nome_vendedor;

Mostrar o menor e o maior salário de vendedor

·          Select MIN(salario_fixo), MAX(salario_fixo) from vendedor;

Mostrar a quantidade total pedida para o produto de código ‘78’.

·          Select SUM(quantidade) From item_pedido Where codigo_produto = ‘78’;

Qual a média dos salários fixos dos vendedores?

·          Select AVG(salario_fixo) From vendedor;

Quantos vendedores ganham acima de R$2.500,00 de salário fixo?

·          Select count(*) From vendedor Where salario_fixo > 2500;

Cláusula DISTINCT: Vários registros em uma tabela podem ter os mesmos valores, podendo trazer informações erradas.  Distinct é usada para não permitir que algumas redundâncias causem problemas. 

Quais as unidades de produtos, diferentes, na tabela produto?

·          Select DISTINCT unidade From produto;

Agrupando informações selecionadas (GROUP BY): Para organizar os dados em grupos determinados.  Geralmente é utilizado em operações de COUNT ou AVG.

Listar o número de produtos que cada pedido contém.

·          Select  num_pedido, total_produtos = COUNT(*) From item_pedido Group by num_pedido;

Agrupando de forma condicional (HAVING):

Listar os pedidos que têm mais do que 3 produtos

·          Select num_pedido,total_produtos = COUNT(*) From item_pedido Group by num_pedido Having COUNT(*) > 3;

Recuperando dados de várias tabelas (JOINS):Acesso em várias tabelas.  JOIN faz junção de tabelas

Juntar as tabelas cliente com pedido.  Nessa consulta, poucos valores serão retornados.

·          Select nome_cliente, pedido.cod_cliente, num_pedido From cliente, pedido;

Que clientes fizeram os pedidos? Listar pelo nome de clientes.  O WHERE é chamada de EQUAÇÃO DE JUNÇÃO para obter um resultado contreto.

·          Select nome_cliente, Pedido.cod_cliente, Num_pedido From cliente, pedido Where cliente.cod_cliente = pedido.cod_pedido;

Quais clientes que têm prazo de entrega superior a 15 dias e que pertencem aos estados de São Paulo (‘SP’) ou Rio de Janeiro (‘RJ’)?

·          Select nome_cliente, UF, prazo_entrega From cliente, pedido Where cliente.cod_cliente = pedido.cod_pedido And UF in (‘SP’, ‘RJ’) and prazo_entrega > 15;

Mostrar os clientes e seus respectivos prazos de entrega, ordenados do maior para o menor.

·          Select nome_cliente, prazo_entrega From cliente, pedido Where cliente.cod_cliente = pedido.cod_cliente Order by prazo_entrega desc;

Aliases(sinônimos): Definidos na própria consulta.  Feita na cláusula From e utilizadas nas outras cláusulas (Where, order by, group by, having e select).

Apresentar os vendedores (ordenados) que emitiram pedidos com prazo de entrega superiores a 15 dias e que tenham salários fixos iguais ou superiores a R$1.000,00.

·          Select nome_vendedor, prazo_entrega From vendedor V, pedido P Where V.cod_vendedor = P.cod_vendedor and salario_fixo >= 1000 And prazo_entrega > 15 Order by nome_vendedor;

Mostre os clientes (ordenados) que têm prazo de entrega maior que 15 dias para o produto ‘QUEIJO’ e que sejam do Rio de Janeiro.

·          Select nome_cliente From cliente C, pedido P, item_pedido I, produto PR Where C.cod_cliente = P.cod_cliente And P.num_pedido = I.num_pedido And I.cod_produto = PR.cod_produto And prazo_entrega > 15 And descricao = ‘QUEIJO’ And UF = ‘RJ’ Order by C.nome_cliente;

Mostre todos os vendedores que venderam chocolate em quantidade superior a 10Kg.

·          Select distinct nome_vendedor From vendedor V, pedido P, item_pedido I, produto PR Where C.cod_vendedor = P.cod_vendedor And P.num_pedido = I.num_pedido And I.cod_produto = PR.cod_produto And quantidade > 10 And descricao = ‘CHOCOLATE’;

Quantos clientes fizeram pedido com o vendedor João?

·          Select count(cod_cliente) From cliente C, pedido P, vendedor V Where C.cod_cliente = P.cod_cliente And P.cod_vendedor = V.cod_vendedor And nome_vendedor = João;

Quantos clientes da cidade do Rio de Janeiro, e Niterói tiveram seus pedidos tirados com o vendedor João?

·          Select cidade, número = count(nome_cliente) From cliente C, pedido P, vendedor V Where nom_vendedor = ‘João’ And cidade in (‘Rio de Janeiro’, ‘Niterói’) And V.cod_vendedor = P.cod_vendedor And P.cod_cliente = V.cod_cliente Group by cidade;

Subqueries: Quando o resultado de uma query é usada por outra, de forma concatenada no mesmo comando SQL.

Que produtos participam em qualquer pedido cuja quantidade seja 10?

·          Select descrição From produto Where cod_produto in (select cod_produto From item_pedido Where quantidade = 10);

Quais vendedores ganham um salário fixo abaixo da média?

·          Select nome_vendedor from vendedor Where salário_fixo < (select AVG(salario_fixo) from vendedor);

Quais os produtos que não estão presentes em nenhum pedido?

·          Select cod_produto, descrição From produto P Where not exists (select * from item_pedido Where cod_produto = P.cod_produto);

Quais clientes estão presentes em mais de três pedidos?

·          Select nom_cliente From cliente C Where exists (select count(*) From pedido Where cod_cliente = C.cod_cliente Having count(*) > 3));

In Myqls Left join, cross join e inner join are equivalents
- Left Join
- Gives a extra consideration to the table that is on the left
- Return all rows from the left table, with the matching rows in the right table
- Every items in the left table will show up in result even if there isn't match with other table.

ex:
Return all clients and its "pedidos"
SELECT cliente.nome_cliente, pedido.num_pedido, pedido.prazo_entrega FROM cliente
LEFT JOIN pedido
ON cliente.nome_cliente = pedido.codigo_cliente;
- INNER JOIN
- Return the intersection between two tables

ex: Return all clients and its "pedidos"
SELECT cliente.nome_cliente, pedido.num_pedido, pedido.prazo_entrega FROM cliente
INNER  JOIN pedido
ON cliente.nome_cliente = pedido.codigo_cliente;


- SQL
- Inner Join
- Matches rows in one table with rows in other tables and return the intersection betwenn them.
- Ex:
SELECT column_name(s) FROM table1
INNER JOIN table2 ON table1.column_name=table2.column_name;
INNER JOIN table3 ON table1.column_name=table3.column_name;
...
where WHERE_CONDITIONS
- Criteria
- Specify the main table in the select(FROM)
- Specify the table to join.  Mane tables is bad for performance
- Specify the join conditions.

http://www.w3schools.com/sql/img_innerjoin.gif
- View
Virtual table based on a result-set of a SELECT.
- A View is a "frozen" definition at criation time.  Changes in the original table does not affects the VIEW.
- CREATE VIEW "VIEW_NAME" AS "SQL COMMAND";
- DROP VIEW view_name
- ALTER VIEW (with another select)
 ALTER
[ALGORITHM =  {MERGE | TEMPTABLE | UNDEFINED}]
 VIEW [database_name].  [view_name]
  AS
[SELECT  statement]
- Algorithms
- MERGE: The insert is combined with the SELECT.  More efficient. Only when view represent one-to-one relationship.
- TEMPLATE: First create a temporary table based on the SELECT and then the insert.
- UNDEFINED: MySql decided which algorithm to use.
Benefits
- View can hide complexity
If you have complexy select with join, complexy logic.  Ex: A View with a calculum over two columns.
- View can be used as a security mechanism
- Set permissions to the view instead of table, select a set of columns.  Ex, remove credit card column from a view.
- Views can support legacy code
- If the database changes, you can create a view based with the same schema as the legacy tables that was removed.
Drawback
- Performance specially if a view is created from another view
- The necessity to change the view whenever the database changes.
A view to be updateble
- The SELECT statement must only refer to one database table.
- The SELECT statement must not use GROUP BY or HAVING clause.
- The SELECT statement must not use DISTINCT in the column list of the SELECT clause.
- The SELECT statement must not refer to read-only views.
- The SELECT statement must not contain any expression (aggregates, functions, computed columns…)
- Index
Index is a pointer to data in a Table.
Help to find data easier.  Used speed up data retrieval without reading the whole table, but slow the input.
- Users cannot see index, they are just used by the engine.
- First goes through a index to find a data.
- How to do: Create a index with a name, table and COLUMNS to be indexed.  Also if the index is ASCENDING or DESCENDING.
- CREATE [UNIQUE] INDEX "INDEX1, INDEX2" ON "TABLE" (COLUMN1, COLUMN2);
- Unique INDEX
Does not allow duplicated values to be inserted in the TABLE.
- DROP
- DROP INDEX index_name;
When
- Use
- To improve performance
- Avoid
- In small tables
- Table with many update/insert
- In Columns with high number of NULL value
Stored Procedure
- Declarative SQL statement that encapsulates repetitive tasks.  Can be called by triggers, other stored procedure or application
- It is stored in server.
- Advantages:
- Decrease net traffic(application - database)
- Instead of sending SQL statement, app send just the name and parameters
- increase select performance.
After created it is compiled and stored in database
-  Mysql put it in a cache. Mysql maintaing its own stored procedure for each connection.
- create security mechanism
Admin can grant permissions to application that access SP without giving access to database.
- Simplify application code(no need to keep queries inside the logic)
- Disadvantages
- SP is a function, than it can overuse memory. DB is not designed for logical operations
- Difficult to debug.  MySQL doesn't have this functionaly
- difficult to develop and maintain.
- Ex:
Create
DELIMITER //
CREATE PROCEDURE Method(IN/OUT/INOUT param VARCHAR(255))
BEGIN
DECLARE total INT DEFAULT 0
select count(*) into total from clients;
SELECT * FROM clients;
  END //
DELIMITER ;
Call
CALL STORED_PROCEDURE();
Show
SHOW PROCEDURE STATUS WHERE name LIKE '%clients%'
- Triggers
It is a special type of Stored Procedure. It is not called directly, but automaticaly when some event happens.
Used to validation and data modification
Used for auditing or reflect changes.
Types of triggers:
BEFORE/AFTER  TABLE INSERT/UPDATE/DELETE
Syntax
CREATE TRIGGER Name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table
FOR EACH ROW
BEGIN
/*code*/
END
Show Trigger
SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name' AND
Trigger_name = 'trigger_name';
- NEW and OLD
NEW: Access to data already changed
OLD: Access to old data

MySQL
http://downloads.mysql.com/docs/mysql-tutorial-excerpt-5.1-en.pdf