Serviço Nacional de Aprendizagem Industrial - SENAI
Técnico em Informática
Banco de Dados
Me Wagner Santos de Oliveira
Curitiba, 02 de Agosto de 2016
William Shakespeare (1964 - 1616)
Poeta, dramaturgo e ator inglês, tido como o maior escritor do
idioma inglês e o mais influente dramaturgo do mundo.
2
Anteriormente...
● Modelo de BD
● Modelos Conceituais
● Modelo Relacional
● Modelo Entidade Relacionamento
● Diagrama Entidade relacionamento
● Exercícios
3
Tragédia!
4
O que vamos ver hoje?
● Atores de DB
● DER
● Níveis de Relacionamento
● Introdução SQL
● Desafio!
5
Partiu?
6
Atores de SGDB
São os tipos de usuários de um banco de dados, onde
cada perfil possuem níveis de acesso, funções e
restrições aos dados e/ou sistema
7
Organograma de resumo
8
Dois grandes lados
9
Cliente Final
10
Programador
11
Programador de Banco
12
Programador de Banco
13
Usuário mais “experiente”
14
Usuário “experiente” na camada alta
15
Guardião dos dados
16
Pai da estrutura
17
Pai da estrutura
18
Demanda do Mercado
19
Modelagem Conceitual
Modelo Entidade Relacionamento (ER)
É a técnica mais conhecida
Tem como objetivo auxiliar na especificação geral do sistema
O modelo de dados é representado graficamente através de um
Diagrama de Entidade-Relacionamento (DER).
Principais conceitos do Modelo ER são:
– Entidades
– Atributos e
– Relacionamentos
20
Entidade
É um conjunto de objetos do mundo real sobre os quais se deseja manter
informações no banco de dados
É distinguível de outros objetos
Representada através de um retângulo
Pode representar: Departamento
●
objetos concretos (uma pessoa)
●
objetos abstratos (um departamento)
Contabilidade
Empregado
Financeiro
Jurídico
João
Pessoal
Pedro
Paulo Possui propriedades
Maria Atributos e Relacionamentos
21
Entidade
●
Exemplos de Entidades:
●
Sistema Bancário
– Cliente
– Conta Corrente
– Conta Poupança
– Agência
●
Sistema de Controle de Produção de Industria
– Produto
– Empregado
– Departamento
– Estoque
– ...
22
Atributo
É um dado que é associado a cada ocorrência de uma
entidade ou de um relacionamento
Exemplos de atributos de entidades:
Nome
Empregado Endereço
Salário
Descrição
númeroDeFuncionários
Departamento
23
Exercício
●
Considerando um sistema acadêmico, envolvendo entidades
que todos conhecemos, defina pelo menos 3 atributos para cada
uma das entidades
Aluno Professor
Disciplina Turma
24
Relacionamentos
Como expressamos que João trabalha no Departamento de
Contabilidade?
Empregado Lotação Departamento
Diagrama de Ocorrências (instâncias)
João Contabilidade
Pedro Financeiro
Paulo Jurídico
Maria Pessoal
25
Relacionamentos
Relacionamento:
É uma associação entre entidades
Representado através de um losângulo e linhas que
ligam as entidades relacionadas
A nomeDoRelacionamento B
26
Relacionamentos
●
Exemplos de Relacionamentos
Medico Consulta Paciente
ContaCliente Conta
Cliente
Residência
Cidade
27
Relacionamentos com Atributos
●
Exemplo I esquema
Consulta Paciente
Médico
nome celular dataDaConsulta nome endereço
instâncias
22/10/2007 Vania
Dr. Paulo
05/02/2009
Dr. Flora
20/03/2009 José
28
Relacionamentos com Atributos
●
Exemplo II
Atuação Projeto
Empregado
nome salario função sigla
coordenador AATOM
Vania
pesquisador
Pedro
bolsista Sinpli
Gabriel
29
Identificando Entidades
Cada entidade deve ter um identificador
Identificador (também conhecido como chave):
●
É o conjunto de um ou mais atributos ou relacionamentos cujos valores
servem para distinguir uma ocorrência da entidade das demais
ocorrências da mesma entidade
●
Exemplo: os atributos CPF ou Carteira de Identidade identificam
UNICAMENTE um cidadão brasileiro
Representação no Modelo
CPF NumeroCorredor
Nome NumeroPrateleira
Endereco Cliente Prateleira
30
Identificando Relacionamentos
●
Quando parte da chave é um relacionamento
– Exemplo: CPF do Empregado e numero sequencial na entidade Dependente
Cpf Numero
Nome Empregado Dependente Nome
Endereco
Chave=CPF +
●
Entidade Fraca:
Fraca entidade sem chave numero
– A entidade é identificada por algum atributo da entidade +
algum relacionamento
●
Entidade Forte:
Forte entidade com chave
– A entidade é identificada por atributos da própria entidade
31
Cardinalidade de Relacionamentos
Uma propriedade importante dos A B
relacionamentos é a especificação
de quantas ocorrências de uma
entidade podem estar associadas a a1 b1
uma determinada ocorrência de
outra entidade a2
b2
a3
Existem 2 cardinalidades:
Máxima
a4 b3
Mínima
b4
A ocorrência a1 da entidade A
está relacionado a quantas
Ocorrências em B?
32
Relacionamento Um para Um – 1:1
Uma ocorrência de A está associada a no máximo uma ocorrência de B,
e uma ocorrência em B está associada a no máximo uma ocorrência em
A.
João Contabilidade
Pedro Financeiro
Conjunto A
(Empregado) Jurídico
Paulo
Maria Conjunto B
(Departamento)
Gerencia Departamento
Empregado 1 1
33
Relacionamento Um para Muitos – 1:N
Uma ocorrência de A está associada a várias ocorrências de B, porém uma
ocorrência de B deve estar associada a no máximo uma ocorrência em A
João
Contabilidade
Sandro
Conjunto A
(Departamento)
Financeiro Pedro
Ana Conjunto B
(Empregado)
Departamento Lotação Empregado
1 N
34
Relacionamento Muitos para Muitos – M:N ou N:N
Uma ocorrência de A está associada a qualquer número de ocorrências de B, e
uma ocorrência em B está associada a qualquer número de ocorrências em A
João Sinpli
Pedro AATOM Conjunto B
Conjunto A (Projeto)
(Empregado)
MM-TOM
Paulo
Sigmoda
Maria
Empregado N
Atua N Projeto
35
Cardinalidade Mínima
●
O modelo ER permite expressar cardinalidades mínimas e
máximas em cada relacionamento
Cardinalidade Mínima:
●
número mínimo de ocorrências de uma entidade A com
relação a uma outra entidade B
– Representação:
●
(cardinalidade mínima, cardinalidade máxima)
●
Cardinalidades Possíveis: (1,1); (1,N); (0,1);(0,N);(N,N)
– Cardinalidade mínima = 1 (relacionamento obrigatório)
– Cardinalidade mínima = 0 (relacionamento opcional)
36
Cardinalidade Mínima e Máxima
Exemplo de Relacionamento Obrigatório:
cada ocorrência de cliente está relacionado a no mínimo quantas contas e
no máximo quantas contas?
Cada ocorrência de conta está relacionada a no mínimo quantos clientes e
no máximo quantos clientes?
Cliente (1,1) ContaCliente (1,N) Conta
Exemplo de Relacionamento Opcional:
Empregado (1,1) Gerencia (0,1) Departamento
37
Atributos também podem ter
Cardinalidade
●
Monovalorado: possui um valor único em uma
entidade nome
– Exemplo: nome Empregado Endereço
Salário
●
Multivalorado: possui mais de um valor para
cada ocorrência da entidade
– Exemplo: telefone nome
Endereço
Empregado Salário
telefone (0,N)
38
Atributos também podem ter
Cardinalidade
●
Cardinalidade mínima
– 1: atributo obrigatório
– 0: atributo opcional
●
Cardinalidade máxima
– 1: atributo monovalorado
– N: atributo multivalorado
nome
Endereço (1,N)
Cliente
TELEFONE (0,N)
39
Relacionamentos e Papéis
Papel é a função que uma ocorrência da entidade cumpre
dentro de uma ocorrência do relacionamento
Não é obrigatória no Modelo ER
movimenta É movimentada
Cliente (1,1)ContaCliente (1,N) Conta
alocado envolve
Empregado (1,N)
Atua (0,N) Projeto
40
Auto-Relacionamento (Relacionamento Unário)
●
Relacionamento entre ocorrências da mesma entidade
Empregado Pessoa
supervisionado supervisor Marido Esposa
(1,1) (0,1) (0,1)
(0,N)
supervisiona casadoCom
supervisionada esposa
João Roberto
Pedro Pedro
supervisor marido
Paulo Felipe
Maria Ana
41
Relacionamento Binário e Ternário
Binário
Empregado (1,N )
trabalha
(1,1 )
Departamento
Ternário Cidade Distribuidor
(1,N) (1,1)
distribuição
(1,N)
Produto
42
Exercícios
“Wagner é um professor de Banco de Dados I na
turma do segundo módulo de Técnico de informática
do SENAI. Ele precisa de um DER para ensinar seus
alunos o conceito de bancos relacionais”
– Entidades
– Atributos
– Relacionamentos
– cardinaliade
43
Exercício - Resposta
CPF (1,1)
Nome Professor regência
Salario
(1,1)
atuação
(0,N) (0,N)
(1,N) (1,N) (1,1)
(0,N)
Aluno Matrícula Turma da Disciplina
CPF Matricula nome Numero Sala horario Codigo nome numeroCreditos
44
Exercício 2
45
Exercício 2
46
Exercício 2
47
Exercício 2
48
Exercício 2
49
Dúvidas
50
Pausa
51
SQL
Structured Query Language, ou Linguagem de Consulta
Estruturada ou SQL, é a linguagem de pesquisa
declarativa padrão para banco de dados relacional
(base de dados relacional). Muitas das características
originais do SQL foram inspiradas na álgebra
relacional.
52
HISTÓRICO
SEQUEL - Structured English QUEry Language
Projetada e implementada no Centro de Pesquisa da IBM como
interface de um banco de dados relacional experimental chamado
System R
ANSI (American National Standards Institute) e a ISO (International
Standards Organization) juntaram esforços de pesquisa, originando
a versão SQL96, ou SQL1
SQL2, aprovada em 1992 (SQL92)
SQL3 aprovada em 1999
SQL É MAIS DO QUE UMA LINGUAGEM
DE CONSULTA
DDL (Data Definition Language)
Linguagem de Definição dos dados
Permite a definição da estrutura do banco de dados
CREATE
ALTER
DROP
SQL É MAIS DO QUE UMA LINGUAGEM
DE CONSULTA
DML (Data Manipulation Language)
Linguagem de Manipulação dos dados
Recuperação de dados
Possibilita a recuperação dos dados do banco
SELECT
Atualização dos dados
Permite alterações e exclusões de dados já existentes, bem como a adição de novos
dados
INSERT
UPDATE
DELETE
Resumo
OUTROS COMANDOS DA LINGUAGEM
Controle de acesso
Fornece meios para determinar o controle de acesso de usuários aos dados do
banco
GRANT
REVOKE
Compartilhamento de dados (transações)
Coordena o acesso concorrente de usuários, assegurando que um não
interfira no trabalho do outro
COMMIT
ROLLBACK
SAVEPOINT
Integridade de dados
Permite definir restrições de integridade aos dados do banco
CONSTRAINT
Comunicando-se com um
SGBD Relacional usando SQL
A instrução SQL
é informada A instrução é enviada para
o banco de dados
SQL>
SQL> SELECT
SELECT loc
loc
22 FROM
FROM dept;
dept;
Banco de
Dados
Os dados são
exibidos
LOC
LOC
-------------
-------------
NEW
NEW YORK
YORK
DALLAS
DALLAS
CHICAGO
CHICAGO
BOSTON
BOSTON
VANTAGENS DA LINGUAGEM SQL
●
Independência de fabricante;
●
Portabilidade entre computadores;
●
Redução dos custos com treinamento;
●
Comandos estruturado de alto nível;
●
Múltiplas visões dos dados.
SQL E LINGUAGENS DE
PROGRAMAÇÃO
Declarações SQL podem ser embutidas em praticamente
todas as linguagens de programação modernas
Java, C#, Delphi
Driver de conexão ao SGBD
JDBC
ODBC
Objetos do Banco de Dados
Objeto Descrição
Tabela Unidade básica de armazenamento,
composta de linhas de uma ou mais colunas
View Representa logicamente subconjuntos de
dados de uma ou mais tabelas
Seqüência Gera valores de chave primária
Índice Melhora o desempenho de algumas
consultas
Sinônimo Atribui nomes alternativos a objetos
Convenções para Nomeação
Deve começar com uma letra
Pode ter de 1 a 30 caracteres
Deve conter somente A–Z, a–z, 0–9, _, $ e #
Não deve duplicar o nome de outro objeto de
propriedade do mesmo usuário
Não deve ser uma palavra reservada do SGBD
SQL COMO DDL
Instruções para definição do esquema da base de dados:
CREATE TABLE
Cria uma nova tabela na base de dados, especificando nome,
atributos e restrições
ALTER TABLE
Altera definições de uma tabela
DROP TABLE
Remove uma tabela, quando suas definições não são mais
necessárias
CRIAÇÃO DO BANCO DE DADOS
CREATE DATABASE
Cria um novo banco de dados
Sintaxe pode mudar a depender do SGBD
CREATE DATABASE <nome_do_BD>
CREATE DATABASE EmpresaABC
CRIAÇÃO DE TABELAS
CREATE TABLE
Colunas são especificadas primeiro, sob a forma:
<nomeCol> <domínio> <restrição>
Depois Chaves, integridade referencial e restrições de integridade
CREATE TABLE <nome_da_tabela>
( <nome_da_col1> <tipo_da_col1> NOT NULL,
<nome_da_col2> <tipo_da_col2> NOT NULL,
...
PRIMARY KEY <lista_de_nomes_de_col>,
FOREIGN KEY <nomes_de_col>
REFERENCES <nome_tab_ref>(<nome_da_col_ref>)
);
CRIAÇÃO DE TABELAS
Exemplo:
CREATE TABLE Fornecedor
( Fcod INTEGER NOT NULL,
Fnome VARCHAR(20) NOT NULL,
Status INTEGER,
Cidade VARCHAR(20));
Fornecedor
Fcod Fnome Status Cidade
TIPOS PARA A DEFINIÇÃO DE COLUNAS
SQL Padrão (ANSI) SQL2 = Padrão +
CHAR(tamanho) VARCHAR(tamanho)
CHARACTER(tamanho) CHAR VARYING(tamanho)
CHARACTER VARYING(tamanho)
INT
INTEGER NCHAR(tamanho)
SMALLINT NATIONAL CHAR(tamanho)
NATIONAL CHARACTER(tamanho)
NUMERIC(precisão,escala) VARYING(tamanho)
DECIMAL(precisão,escala)
DEC(precisão,escala) BIT(tamanho)
BIT VARYING(tamanho)
FLOAT(precisão)
REAL DATETIME
DOUBLE PRECISION TIME(precisão)
TIMESTAMP(precisão)
INTERVAL
RESTRIÇÕES E VALORES DEFAULT
Restrições:
NOT NULL
Restrição aplicadas a colunas cujos valores não podem ser nulos
Valores Default
Adiciona-se a cláusula DEFAULT <valor> logo após a restrição:
CREATE TABLE Empregado
( ...
Sexo CHAR(1) NOT NULL DEFAULT “F”,
...
);
Restrição Definição do
valor default
CRIAÇÃO DE TABELAS
Especificação de chaves:
Primária:
PRIMARY KEY(<nomeColuna>),
Estrangeira:
FOREIGN KEY(<nomeCol>) REFERENCES <NomeTabRefer>,
Alternativa:
UNIQUE KEY(CPF), (SQL2)
CRIAÇÃO DE TABELAS
Exemplo com chave primária:
CREATE TABLE Departamento
( Dcod INTEGER NOT NULL,
Dnome VARCHAR(20) NOT NULL,
Cidade VARCHAR(20),
PRIMARY KEY(Dcod));
Departamento
CRIAÇÃO DE TABELAS
Exemplo com chave primária composta
CREATE TABLE Empregado
( Ecod INTEGER NOT NULL,
Enome VARCHAR(40) NOT NULL,
CPF VARCHAR(15) NOT NULL,
Salario DECIMAL(7,2),
Cod_Dept INTEGER NOT NULL,
PRIMARY KEY(Ecod,ENome));
Empregado
CRIAÇÃO DE TABELAS
Exemplo com chave estrangeira
CREATE TABLE Empregado
( ...,
CONSTRAINT TrabalhaEm
FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod),
...
);
Empregado
Ecod Enome CPF Salario Cod_Dept
CRIAÇÃO DE TABELAS
Exemplo com chave alternativa
CREATE TABLE Empregado
( ...,
UNIQUE KEY(CPF));
Empregado
Ecod Enome CPF Salario Cod_Dept
CRIAÇÃO DE TABELAS
Assim...
CREATE TABLE Empregado
( Ecod INTEGER NOT NULL,
Enome VARCHAR(40) NOT NULL,
CPF VARCHAR(15) NOT NULL,
Salario DECIMAL(7,2),
Cod_Dept INTEGER NOT NULL,
PRIMARY KEY(Ecod,ENome);
CONSTRAINT TrabalhaEm
FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod),
UNIQUE KEY(CPF));
Empregado
Ecod Enome CPF Salario Cod_Dept
CRIAÇÃO DE TABELAS
Exemplo com chave estrangeira
CREATE TABLE Empregado
( ...,
CONSTRAINT TrabalhaEm
FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod),
...
);
REMOÇÃO DE TABELAS
●
DROP TABLE
– Elimina completamente a tabela (vazia ou não)
DROP TABLE <nome_da_tabela>;
●
Ex.:
DROP TABLE Empregado;
– Não há como recuperar a tabela removida
ALTERAÇÃO DE TABELAS
●
ALTER TABLE
ALTER TABLE <nome_da_tabela> <alteração>;
●
Adicionar uma coluna:
ALTER TABLE Empregado
ADD Telefone VARCHAR(30)
ADD sexo CHAR(1) DEFAULT ‘F’;
ALTERAÇÃO DE TABELAS
●
Excluir restrições de chave primária
ALTER TABLE Empregado
DROP PRIMARY KEY;
●
Exclusão de uma chave estrangeira
– Pode ser feita somente se ela recebeu um nome quando foi definida
(através de CONSTRAINT):
ALTER TABLE Empregado
DROP FOREIGN KEY TrabalhaEm;
ALTERAÇÃO DE TABELAS
●
Adicionar chave estrangeira:
ALTER TABLE Empregado
CONSTRAINT Supervisao
ADD FOREIGN KEY(Codesupervisor)
REFERENCES Empregado(codemp);
INTEGRIDADE REFERENCIAL
Cuidados
Quando colunas são excluídas ou alteradas
Quando o valor do atributo da chave estrangeira é modificado na
tabela referenciada
Ações disparadas quando ocorrem violações:
SET NULL
CASCADE
ON DELETE
ON UPDATE
INTEGRIDADE REFERENCIAL
Exemplo, na tabela de Empregado
CREATE TABLE Empregado
( ...
FOREIGN KEY(Cod_Dept) REFERENCES Departamento(Dcod)
ON DELETE SET NULL
ON UPDATE CASCADE,
...
);
●
Ou seja...
– Na remoção da linha que contém o valor da chave estrangeira,
colocar nulo à coluna (se a coluna admitir NULL)
– Na alteração do valor da chave estrangeira, alterar em cascata as
chaves que referenciam este valor
EXERCÍCIO
Altere as tabelas criadas anteriormente para :
Fornecedor (Fcod#, Fnome, Status, Ccod#, fone)
Cidade (Ccod#, Cnome, uf)
Peça (Pcod#, Pnome, Cor, Peso, Ccod#)
Projeto (PRcod#, Jnome, Ccod#)
Fornecimento (Fcod#, Pcod#, PRcod#,Quantidade)
Observe que a tabela Instituição não existe mais.
EXERCÍCIO
Crie as seguintes tabelas em seu BD, definindo as condições
descritas abaixo
Fornecedor (Fcod#, Fnome, Status, Cidade)
Peca (Pcod#, Pnome, Cor, Peso, Cidade)
Projeto (PRcod#, Icod#, PRnome, Cidade)
Fornecimento (Fcod#, Pcod#, PRcod#, Quantidade)
Instituição (Icod#, nome)
Nomes para restrições de chaves primária, estrangeira e alternativa
Valor default para o atributo Status, de Fornecedor
Todos os atributos de Peca não podem ser nulos
A ação de CASCADE em Fornecimento se a tabela de Projeto fosse
excluída
Qual tabela deve ser criada por último?Pq?
Resposta