Slide 4 Modelagem de Bancos de Dados parte 02

4.1 Linguagem S.Q.L.

A linguagem SQL foi criada nos laboratórios da IBM em 1974, como interface de manipulação ao Bando de Dados Relacional System-R , atualmente denominado IBM DB2. Os criadores foram engenheiros de sistemas que sucederam o professor Edgard Frank Codd no projeto de Banco de Dados Relacional: Donald Chamberlain e Raymond Boyce.

Donald Chamberlain - Criador da linguagem SQL nos laboratórios da IBM

Raymond Boyce - Criador da linguagem SQL nos laboratórios da IBM

A lingauem SQL é a ponte com o mundo exterior para um Sistema de Gerenciamento de Banco de Dados (SGBD). Os conjunto de comandos da linguagem SQL são divididos em 3 grandes grupos:

\[ \begin{array}{c| c | c} \textbf{GRUPO} & \textbf{ Comandos} & \textbf{Finalidade} \\ \hline Grupo \ DDL & \quad \ Data \ Definion \ Language & \quad Criar \ estruturas \ de \ dados \\ Grupo \ DML & \quad \ Data \ Manipulation \ Language & \quad Manipular \ dados \ armazenados \\ Grupo \ DCL & \quad \ Data \ Control \ Language & \quad Criar \ Regras \ para \ os \ dados \\ Grupo \ TCL & \quad \ Transaction \ Control \ Language & \quad Criar \ Transações \ para \ os \ dados \\ \hline \textbf{Subconjunto SQL } & \textbf{ Significado } & \textbf{ Conjunto Completo } \end{array} \]

4.2 SQL e MODELAGEM

4.2.1 SQL - DDL (DATA DEFINION LANGUAGE) - Construindo estruturas no Banco de Dados

4.2.1.1 Comandos SQL-DDL

Comando	Função
`CREATE`	cria objetos
`ALTER`	modifica estrutura
`DROP`	remove objetos

4.2.1.1.1 Exemplo - Criando uma tabela com comando CREATE

CREATE TABLE aluno (
    id INTEGER PRIMARY KEY,
    nome VARCHAR(100),
    idade INTEGER,
    curso VARCHAR(50)
);

4.2.1.1.2 Exemplo - Criando uma tabela com comando ALTER

ALTER TABLE aluno
ADD email VARCHAR(100);

4.2.1.1.3 Exemplo - Criando uma tabela com comando DROP

DROP TABLE aluno;

4.3 SQL e TRATAMENTO DE DADOS

4.3.1 SQL - DML (DATA MANIPULATION LANGUAGE) - Inserindo dados nas estruturas do Banco de Dados

A DML é responsável por manipular os dados armazenados nas tabelas. Também é responsável por implementar as operações de Algebra Relacional propostas por Codd que vão operacionalizar os dados armazenados:

Comando	Função
`INSERT`	inserir dados
`UPDATE`	atualizar dados
`DELETE`	remover dados

4.3.1.0.1 Exemplo - Inseriondo dados em uma tabela

INSERT INTO aluno (id, nome, idade, curso) VALUES (1, 'Ana', 21, 'Computação');

4.3.1.0.2 Exemplo - Atualizando dados em uma tabela com UPDATE

UPDATE aluno SET idade = 22 WHERE id = 1;

4.3.1.0.3 Exemplo - Apagando dados com o comando DELETE

DELETE FROM aluno WHERE id = 1;

4.4 Algebra Relacional

Segundo Abraham Silberschatz no livro Database System Concepts:

Álgebra Relacional é uma coleção de operações que recebem uma ou duas relações como entrada e produzem uma nova relação como resultado.

São basicamente 8 operações herdadas da Teoria do Conjuntos:

Álgebra Relacional	Significado	SQL equivalente
`σ condição (R)`	seleção de linhas	`SELECT * FROM R WHERE condição`
`π atributos (R)`	projeção de colunas	`SELECT atributos FROM R`
`R ∪ S`	união	`SELECT ... FROM R UNION SELECT ... FROM S`
`R − S`	diferença	`SELECT ... FROM R EXCEPT SELECT ... FROM S`
`R ∩ S`	interseção	`SELECT ... FROM R INTERSECT SELECT ... FROM S`
`R × S`	produto cartesiano	`SELECT * FROM R CROSS JOIN S`
`R ⋈ condição S`	junção	`SELECT * FROM R JOIN S ON condição`
`ρ novo_nome (R)`	renomeação	`FROM R AS novo_nome`

4.4.1 Operação `SELEÇÃO` ( letra sigma σ )

A *SELEÇÃO* filtra linhas da tabela de acordo com uma condição lógica. Ela não altera as colunas, apenas escolhe quais tuplas permanecem na relação. Equivale a um filtro de linhas.

$\sigma_{salario > 3000}(Funcionario)$

Exemplo 1 -

SELECT * FROM Funcionarios WHERE salario > 3000;

4.4.2 Operação `PROJEÇÃO` ( letra PI π )

A PROJEÇÃO seleciona colunas da tabela. Ela remove atributos que não são desejados. Equivale a um filtro de colunas.

Exemplo 2 -

$\pi_{nome, salario}(Funcionario)$

SELECT nome, salario FROM Funcionario;

4.4.3 Operação `UNIÃO` ( ∪ )

A *UNIÃO* combina as tuplas de duas relações. Para isso, as relações precisam ser compatíveis, ou seja:

A)mesmo número de atributos;
B)mesmos domínios (tipos) de dados;

$ R S $

Exemplo 3 -

SELECT coluna1, coluna2 FROM R
UNION
SELECT coluna1, coluna2 FROM S;

4.4.4 Operação `DIFERENÇA` ( - )

A *DIFERENÇA* entre duas relações R e S retorna as tuplas (linhas) que estão em uma relação mas não estão na outra.

$R - S$

Exemplo 4 -

SELECT coluna1, coluna2 FROM R
EXCEPT
SELECT coluna1, coluna2 FROM S;

4.4.5 Operação `INTERSECÇÃO` ( ∩ )

A *INTERSECÇÃO* entre duas relações R e S retorna apenas as tuplas que aparecem nas duas relações ao mesmo tempo.

$R \cap S$

Exemplo 5 -

SELECT coluna1, coluna2 FROM R
INTERSECT
SELECT coluna1, coluna2 FROM S;

4.4.6 Operação `PRODUTO CARTESIANO` ( × )

O *PRODUTO CARTESIANO* entre duas relações R e S combina cada tupla de uma relação com todas as tuplas de outra relação.

Exemplo 6 -

$R \times S$

  SELECT * FROM R CROSS JOIN S;

4.4.7 Operação `JUNÇÃO NATURAL` ( ⋈ )

A *JUNÇÃO NATURAL* combina duas relações com base em uma condição entre atributos de ambas;

$Funcionario \bowtie_{Funcionarios.depto\_id = Departamentos.id} Departamento$

Exemplo 7 -


SELECT * FROM 
    Funcionarios
  JOIN 
    Departamentos
  ON 
    Funcionarios.depto_id = Departamentos.id;

4.4.8 Operação `RENOMEAÇÃO` (letra rô ρ)

A *RENOMEAÇÃO* permite renomear uma relação ou atributos.

4.4.8.1 renomeação de tabela

$\rho_{F}(Funcionario)$

Exemplo 8 -


SELECT * FROM Funcionarios AS F;

4.4.8.2 renomeação de atributo

$\pi_{\ id \rightarrow cpf, nome,nascimento}(Funcionario)$


SELECT id AS cpf, nome, nascimento FROM Funcionarios;

4.5 SQL - USO AVANÇADO DE MANIPULAÇÃO DE DADOS:

4.5.1 Operação de Junção JOIN

A Operação JOIN possui alguns refinamentos:

Tipo	Resultado
INNER JOIN	apenas registros correspondentes
LEFT JOIN	todos da esquerda
RIGHT JOIN	todos da direita
FULL JOIN	todos de ambas

Para demonstra-los, vamos tomar duas Relações (tabelas) como exemplo:

4.5.1.0.1 Tabela CLIENTES

id_cliente	nome
1	Ana
2	Carlos
3	Maria

4.5.1.0.2 Tabela PEDIDOS

id_pedido	id_cliente	valor
101	1	200
102	1	150
103	2	300

4.5.1.1 JUNÇÃO SIMPLES (JOIN)

O JOIN combina dados de duas ou mais tabelas. A junção é baseada em uma condição de relacionamento.

SELECT colunas
FROM tabela1
JOIN tabela2
ON condição;

4.5.1.2 JUNÇÃO INTERNA (INNER JOIN)

O INNER JOIN retorna apenas registros que possuem correspondência nas duas tabelas.

SELECT cliente.nome, pedido.valor
FROM cliente
INNER JOIN pedido
ON cliente.id_cliente = pedido.id_cliente;

Saída:

nome	valor
Ana	200
Ana	150
Carlos	300

4.5.1.3 JUNÇÃO A ESQUERDA (LEFT JOIN)

O LEFT JOIN retorna todos os registros da tabela da esquerda. Mesmo que não exista correspondência na outra tabela.

SELECT cliente.nome, pedido.valor
FROM cliente
LEFT JOIN pedido
ON cliente.id_cliente = pedido.id_cliente;

Saída:

nome	valor
Ana	200
Ana	150
Carlos	300
Maria	NULL

4.5.1.4 JUNÇÃO A DIREITA (RIGHT JOIN)

O RIGHT JOIN retorna todos os registros da tabela da direita.

SELECT cliente.nome, pedido.valor
FROM cliente
RIGHT JOIN pedido
ON cliente.id_cliente = pedido.id_cliente;

Saída:

nome	valor
Ana	200
Ana	150
Carlos	300

4.5.1.5 JUNÇÃO COMPLETO (FULL JOIN)

Retorna todos os registros das duas tabelas (esquerda e direita).

SELECT cliente.nome, pedido.valor
FROM cliente
FULL JOIN pedido
ON cliente.id_cliente = pedido.id_cliente;

Saída:

nome	valor
Ana	200
Ana	150
Carlos	300
Maria	NULL

4.5.2 Operação de AGRUPAMENTO de dados GROUP BY

Considere ainda as RELAÇÕES (tabelas) do exemplo anterior, CLIENTES e PEDIDOS:

Para demonstra-los, vamos tomar duas Relações (tabelas) como exemplo:

4.5.2.0.1 Tabela CLIENTES

id_cliente	nome
1	Ana
2	Carlos
3	Maria

4.5.2.0.2 Tabela PEDIDOS

id_pedido	id_cliente	valor
101	1	200
102	1	150
103	2	300

O comando GROUP BY é usado para agrupar registros.

SELECT id_cliente, COUNT(*) AS total_pedidos
FROM pedido
GROUP BY id_cliente;

Normalmente é utilizado junto com funções de agregação.

Função	Descrição
COUNT()	contar registros
SUM()	soma
AVG()	média
MAX()	maior valor
MIN()	menor valor

Saída:

id_cliente	total_pedidos
1	2
2	1

4.5.3 Operação de filtragem de grupos de dados HAVING

O comando HAVING é usado para filtrar grupos. Diferença importante com o WHERE:

Comando	Atua sobre
WHERE	linhas
HAVING	grupos

Exemplo 1 - Considerando as tabelas do pedido anterior, selecione os clientes com mais de um pedido:

SELECT id_cliente, COUNT(*) AS total
FROM pedido
GROUP BY id_cliente
HAVING COUNT(*) > 1;

Saída:

id_cliente	total_pedidos
1	2

4.6 REFRÊNCIAS:

SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S. Database System Concepts. 7. ed. New York: McGraw-Hill Education, 2019.

4.7 Exercícios RESOLVIDOS

Exercício 1 — Universidade

Considere uma Universidade que possui vários Cursos. Cada curso tem um nome, uma duração em semestres e um coordenador. A universidade possui Professores, cada um com um nome, título e CPF. Um professor pode ministrar várias disciplinas, e cada disciplina pertence a um único curso.
Com base no Diagrama Entidade-Relacionamento faça o código SQL que implementa o modelo físico em um Sistema de Gerenciamento de Banco de Dados

DIAGRAMA ENTIDADE-RELACIONAMENTO (D.E.R.) com as informações levantadas:

4.7.0.1 Criando o código SQL referente ao diagrama apresentado:

/* =========================================================
   Passo 1️- CRIAR TABELAS (somente colunas, sem PK nem FK)
   ========================================================= */
CREATE TABLE UNIVERSIDADES 
(
    nome            VARCHAR(255),
    CNPJ            VARCHAR(14)
);

CREATE TABLE CURSOS 
(
    nome            VARCHAR(255),
    duracao         INT,
    coordenador     VARCHAR(255),
    codigo_curso    INTEGER
);

CREATE TABLE PROFESSORES 
(
    cpf             VARCHAR(11),
    nome            VARCHAR(255),
    titulo          VARCHAR(255)
);

CREATE TABLE DISCIPLINAS 
(
    nome                VARCHAR(255),
    codigo_disciplina   INTEGER
);

/* =========================================================
   Passo 2- ADICIONAR CHAVES PRIMÁRIAS
   ========================================================= */
ALTER TABLE UNIVERSIDADES ADD PRIMARY KEY (CNPJ);

ALTER TABLE CURSOS        ADD PRIMARY KEY (codigo_curso);

ALTER TABLE PROFESSORES   ADD PRIMARY KEY (cpf);

ALTER TABLE DISCIPLINAS   ADD PRIMARY KEY (codigo_disciplina);

/* =========================================================
   Passo 3- CRIAR COLUNAS PARA FUTURAS CHAVES ESTRANGEIRAS
   -- Sempre no lado N de cada relacionamento
   ========================================================= */
-- UNIVERSIDADES (1) —— (N) CURSOS  → CURSOS precisa de CNPJ
ALTER TABLE CURSOS   ADD COLUMN CNPJ VARCHAR(14);

-- UNIVERSIDADES (1) —— (N) PROFESSORES → PROFESSORES precisa de CNPJ
ALTER TABLE PROFESSORES  ADD COLUMN CNPJ VARCHAR(14);

-- PROFESSORES (1) —— (N) DISCIPLINAS → DISCIPLINAS precisa de cpf
ALTER TABLE DISCIPLINAS ADD COLUMN cpf VARCHAR(11);

/* =========================================================
   Passo 4- CRIAR AS CHAVES ESTRANGEIRAS
   ========================================================= */
ALTER TABLE CURSOS      ADD CONSTRAINT fk_cursos_universidade      FOREIGN KEY (CNPJ) REFERENCES UNIVERSIDADES (CNPJ);

ALTER TABLE PROFESSORES ADD CONSTRAINT fk_professores_universidade FOREIGN KEY (CNPJ) REFERENCES UNIVERSIDADES (CNPJ);

ALTER TABLE DISCIPLINAS ADD CONSTRAINT fk_disciplinas_professor    FOREIGN KEY (cpf)  REFERENCES PROFESSORES (cpf);

Exercício 2 — Um Projeto de e-commerce

Um Cliente faz Pedidos em um sistema de e-commerce. Cada cliente tem um nome, endereço e telefone. Os pedidos possuem uma data, um valor total e podem conter vários Produtos. Cada produto tem um nome, uma descrição e um preço.
Com base no Diagrama Entidade-Relacionamento faça o código SQL que implementa o modelo físico em um Sistema de Gerenciamento de Banco de Dados

DIAGRAMA ENTIDADE-RELACIONAMENTO (D.E.R.) apresentado:

4.7.0.2 Criando o código SQL referente ao diagrama apresentado:


-- CRIAR AS TABELAS

CREATE TABLE Cliente 
(
    id_cliente INT,
    nome VARCHAR(255)
);

CREATE TABLE CPF 
(
    numero_cpf CHAR(11)
);

-- ADICIONAR AS CHAVES PRIMÁRIAS

ALTER TABLE Cliente ADD PRIMARY KEY (id_cliente);

ALTER TABLE CPF ADD PRIMARY KEY (numero_cpf);


-- O relacionamento é 1:1. Podemos escolher um dos lados para armazenar a chave.
-- Aqui vamos seguir o diagrama, colocando a chave estrangeira de CPF em Cliente.

ALTER TABLE Cliente ADD COLUMN numero_cpf CHAR(11);

-- CRIAR AS CHAVES ESTRANGEIRAS

ALTER TABLE Cliente ADD CONSTRAINT fk_cliente_cpf FOREIGN KEY (numero_cpf) REFERENCES CPF (numero_cpf);

Exercício 3 — Hospital

Um Hospital registra Pacientes, cada um com nome, idade, endereço e telefone. Os pacientes podem realizar várias Consultas com Médicos. Cada médico possui um CRM, um nome e uma especialidade. Durante a consulta, o médico pode prescrever Receitas, que possuem medicação e dosagem.
Identifique as entidades

DIAGRAMA ENTIDADE-RELACIONAMENTO (D.E.R.) apresentado:

4.7.0.3 Criando o código SQL referente ao diagrama apresentado:


/* =========================================================
   Passo 1- TABELAS (apenas colunas, sem PK ou FK)
   ========================================================= */
CREATE TABLE HOSPITAIS 
(
    nome    VARCHAR(255),
    cnpj    CHAR(14)
);

CREATE TABLE PACIENTES 
(
    nome        VARCHAR(255),
    idade       INT,
    endereco    VARCHAR(255),
    telefone    VARCHAR(20),
    cpf         CHAR(11)
);

CREATE TABLE MEDICOS 
(
    nome            VARCHAR(255),
    especialidade   VARCHAR(255),
    crm             CHAR(15)
);

/* As próximas tabelas representam os relacionamentos N:M
   com atributos próprios (tabelas de junção) */
CREATE TABLE REGISTRAM 
(
    -- sem colunas de FK por enquanto
);

CREATE TABLE CONSULTAM 
(
    data_consulta   DATE,
    hora            TIME,
    sala            VARCHAR(50)
);

CREATE TABLE RECEITAM 
(
    medicacao       VARCHAR(255),
    dosagem         VARCHAR(100)
);

/* =========================================================
   Passo 2- ADICIONAR CHAVES PRIMÁRIAS
   ========================================================= */
ALTER TABLE HOSPITAIS  ADD PRIMARY KEY (cnpj);

ALTER TABLE PACIENTES  ADD PRIMARY KEY (cpf);

ALTER TABLE MEDICOS    ADD PRIMARY KEY (crm);

/* Para tabelas de relacionamento com atributos, a PK será composta
   (as FKs a serem criadas formarão a chave primária). Faremos isso depois
   de adicionar as colunas de FK. */

/* =========================================================
   Passo 3- CRIAR COLUNAS PARA FUTURAS CHAVES ESTRANGEIRAS
   ========================================================= */
-- REGISTRAM: HOSPITAIS (1) —— (N) PACIENTES
ALTER TABLE REGISTRAM ADD COLUMN cnpj CHAR(14), ADD COLUMN cpf  CHAR(11);

-- CONSULTAM: MEDICOS (N) —— (N) PACIENTES
ALTER TABLE CONSULTAM ADD COLUMN crm  CHAR(15), ADD COLUMN cpf  CHAR(11);

-- RECEITAM: MEDICOS (N) —— (N) PACIENTES
ALTER TABLE RECEITAM  ADD COLUMN crm  CHAR(15), ADD COLUMN cpf  CHAR(11);

/* =========================================================
   Passo 4- CRIAR CHAVES ESTRANGEIRAS
   ========================================================= */
ALTER TABLE REGISTRAM
    ADD CONSTRAINT fk_registram_hospitais
        FOREIGN KEY (cnpj)
        REFERENCES HOSPITAIS (cnpj),
    ADD CONSTRAINT fk_registram_pacientes
        FOREIGN KEY (cpf)
        REFERENCES PACIENTES (cpf);

ALTER TABLE CONSULTAM
    ADD CONSTRAINT fk_consultam_medicos
        FOREIGN KEY (crm)
        REFERENCES MEDICOS (crm),
    ADD CONSTRAINT fk_consultam_pacientes
        FOREIGN KEY (cpf)
        REFERENCES PACIENTES (cpf);

ALTER TABLE RECEITAM
    ADD CONSTRAINT fk_receitam_medicos
        FOREIGN KEY (crm)
        REFERENCES MEDICOS (crm),
    ADD CONSTRAINT fk_receitam_pacientes
        FOREIGN KEY (cpf)
        REFERENCES PACIENTES (cpf);

/* Definir chaves primárias compostas para tabelas de relacionamento */
ALTER TABLE REGISTRAM ADD PRIMARY KEY (cnpj, cpf);

ALTER TABLE CONSULTAM ADD PRIMARY KEY (crm, cpf, data_consulta, hora);

ALTER TABLE RECEITAM  ADD PRIMARY KEY (crm, cpf, medicacao);