2016 1 COTEMIG BD2 Aula02 (1)

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

BANCO DE DADOS II
COTEMIG
Gerson Borges
contato@gersonborges.com.br
Revisão
O que é um banco de dados?
Definições Preliminares
 [Chu, 1985]
 Um banco de dados é um conjunto de arquivos 
relacionados entre si
 [Date, 2000]
 Um banco de dados é uma coleção de dados 
operacionais armazenados usados pelas 
aplicações de uma determinada organização
Outra Definição de Banco de Dados
 [Elmasri & Navathe, 2000]
 Um banco de dados é uma coleção de dados
relacionados
 Representando algum aspecto do mundo real 
(mini-mundo ou universo de discurso)
 Logicamente coerente, com algum significado
 Projetado, construído e gerado (“povoado”) para 
uma aplicação específica
Sistema de Gerência de Banco de Dados
 Um sistema de gerência de banco de dados 
(SGBD) é um conjunto de programas que 
permite a criar e manter um banco de 
dados
 Um banco de dados juntamente com o 
SGBD que o gerência constitui um sistema 
de banco de dados
Consultas/Programas
SGBD
Banco
de
Dados
Usuários/Programadores
Catálogo
(Meta-Dados)
Sistema de Banco de Dados
Exemplo de um Banco de Dados
 Mini-mundo: parte de uma universidade
 Algumas entidades:
 Alunos
 Disciplinas
 Departamentos
 Alguns relacionamentos:
 Disciplinas são oferecidas por Departamentos
 Alunos estão matriculados em Disciplinas
Exemplo de um Banco de Dados
Exemplo de um Banco de Dados
Características da Abordagem de BD
 Auto-descrição dos dados
 Isolamento entre programas e dados: 
abstração de dados
 Suporte a múltiplas visões dos dados
 Compartilhamento de dados e processa-
mento de transações concorrentes
Usuários em um Ambiente de BD
 Administradores de banco de dados
 Projetistas de banco de dados
 Analistas de sistema e programadores
 Usuários finais:
 Usuários casuais
 Usuários leigos
 Usuários especializados
Vantagens da Utilização de um SGBD 
 Controle de redundância dos dados
 Controle de acesso (segurança)
 Armazenamento persistente dos dados
 Existência de múltiplas interfaces para os usuários
 Representação de relacionamentos complexos entre os 
dados
 Manutenção de restrições de integridade
 Recuperação de falhas
Implicações da Abordagem de BD
 Adoção/imposição de padrões
 Redução do tempo de desenvolvimento das 
aplicações
 Flexibilidade
 Atualidade da informação disponível
 Economia de escala
Quando não Utilizar um SGBD
 Aplicações simples e bem definidas onde não se 
espera mudanças
 Aplicações de tempo-real
 Aplicações onde não é necessário acesso multi-
usuário
 Motivos:
 Investimento inicial alto
 Generalidade na definição e manipulação dos dados
 Custo adicional para prover outras facilidades 
funcionais (manutenção de segurança, controle de 
concorrência, recuperação de falhas, etc.)
Modelo de Dados, Esquema e Instância
 Modelo de dados: Conjunto de conceitos usados 
para descrever a estrutura de um banco de 
dados
 Abstração de dados
 Estrutura = tipos de dados + relacionamentos + 
restrições (+operações )
 Esquema: Descrição (textual ou gráfica) da 
estrutura de um banco de dados de acordo com 
um determinado modelo de dados
 Instância: Conjunto de dados armazenados em 
um banco de dados em um determinado instante 
de tempo
Modelo de Dados, Esquema e Instância
Esquema do banco de dados de exemplo
Modelo de Dados, Esquema e Instância
Instância do banco de dados de exemplo
Relação entre Modelo de Dados, 
Esquema e Instância
Modelo de 
Dados
Esquema Instância
Regras para
estruturação dos
dados
Regras para 
verificação das
instâncias
Modelo de Dados, Esquema e Instância
 Estado do Banco
 Dados do banco em qualquer ponto do tempo
 Inicialmente vazio
 Muda freqüentemente
 Validade parcialmente guarantida pelo SGBD
 Esquema do Banco
 Armazenado no catálogo
 Mudanças muito menos freqüentes
Tipos de Modelo de Dados
 Modelos conceituais
 Utilizados para se descrever a estrutura de um 
banco de dados de uma forma mais próxima da 
percepção dos usuários (independente de 
aspectos de implementação)
 Ex. Conceitos: entidades, atributos, 
relacionamentos
 Exemplos:
 Modelo entidade-relacionamento (ER)
 Modelo funcional
 Modelo orientado a objetos (OO)
Tipos de Modelo de Dados
 Modelos representacionais (lógicos)
 Utilizados para se descrever a estrutura de um banco 
de dados da forma como será manipulado através de 
SGBD (mais dependente das estruturas físicas de 
armazenamento de dados)
 Exemplos:
 Modelo relacional
 Modelo de rede (CODASYL)
 Modelo hierárquico
Tipos de Modelo de Dados
 Modelos físicos
 Utilizados para descrever como os dados são 
fisicamente armazenados
Linguagens
 Linguagem de definição de dados (LDD)
 Usada para definir esquemas
 Linguagem de manipulação de dados
(LMD)
 Recuperação, inserção, remoção, modificação
do BD
 Linguagem de consulta
 LMD de alto nivel usada em modo “stand-
alone”
 Exemplo: SQL
Utilitários
 Carregamento
 Backup
 E.g. dumps do banco de dados
 (Re-)Organização de arquivos
 Monitoramento da performance
Classificação dos SGBDs
 Quanto ao modelo de dados adotado:
 Relacionais
 De rede
 Hierárquicos
 Orientados a objetos
 Objeto-relacionais
 Quanto ao número de usuários suportados:
 Mono-usuários
 Multi-usuários
 Quanto à localização dos dados:
 Centralizados
 Distribuídos
Exemplo de um BD Relacional
NumEmp NomeEmp Salário Dept
032 J Silva 380 21
074 M Reis 400 25
089 C Melo 520 28
092 R Silva 480 25
112 R Pinto 390 21
121 V Simão 905 28
130 J Neves 640 28
NumDept NomeDept Ramal
21 Pessoal 142
25 Financeiro 143
28 Técnico 144
Empregado
Departamento
Exemplo de um BD de Rede
21 Pessoal 142
25 Financeiro 143
28 Técnico 144
032 J Silva 380
112 R Pinto 390
121 V Simão 905
130 J Neves 640
092 R Silva 480
089 C Melo 520
074 M Reis 400
Departamento
Empregado
Exemplo de um BD Hierárquico
21 Pessoal 142 25 Financeiro 143 28 Técnico 144
032 J Silva 380
112 R Pinto 390 121 V Simão 905
130 J Neves 640
092 R Silva 480
089 C Melo 520074 M Reis 400
Departamento
Empregado
Modelo Entidade-Relacionamento
Processo de Projeto de 
Bancos de Dados
Mini-Mundo
Análise de
Requisitos
Requisitos do BD
Projeto Conceitual
Esquema Conceitual 
(em um modelo de dados de alto nível)
Projeto Lógico
Esquema Lógico
(em um modelo de dados lógico)
Projeto Físico
Esquema Físico
(para um SGBD específico)
Requisitos Funcionais
Análise Funcional
Especificação das Transações
(em alto nível)
Projeto das Aplicações
Implementação
Programas
Independente de SGBD
Específico para um SGBD
Aplicação exemplo
 Banco de Dados de uma companhia
 Organizada em departamentos que têm um nome e um
número únicos e um empregado que gerencia o
departamento. A data de quando o empregado
começou a gerenciar o departamento deve ser
registrada. Um departamento pode ter varias
localizações
 Um departamento controla um número de projetos,
cada qual com um nome e número únicos e uma única
localização
Aplicação exemplo
 Banco de Dados de uma companhia
 Nós armazenamos para cada empregado seu
nome, identidade, endereço, salário, sexo, e data
de nascimento. Um empregado é assinalado a um
departamento mas pode trabalhar em diversos
projetos, os quais não são necessariamente
controlados pelo mesmo departamento. Nos
registramos o número de horas por semana que o
empregado trabalha em cada projeto e o
supervisor direto de
cada empregado
 Nós mantemos registro para cada empregado, do
numero de dependentes (para seguro) e para
cada dependente o primeiro nome, sexo, data de
nascimento e relacionamento com o empregado.
Esquema conceitual
M
Modelo ER - Conceitos
 Entidades:
 Objetos do mundo real que são de interesse
para alguma aplicação
 Atributos:
 Propriedades utilizadas para descrever uma
entidade
Name = John
Address = 2311 Kirby, Houston, TX
Age = 55
Home Phone = 713-749-2630e1
(Employee)
Modelo ER - Conceitos
 Tipos (classes) de atributo:
 Simples ou compostos
 Ex. Endereço (Endereço da Rua (número, nome da rua,
número do apto), Cidade, Estado, CEP)
 Monovalorados ou multivalorados
 Ex. Profissão
 Armazenados ou derivados
 Data de Nascimento  Idade, Empregados trabalhando
no departamento  NumeroDeEmpregados
 Valores Null
 Não aplicável
 Ex. Número do apartamento
 Desconhecido
 Ex. Telefone de casa
Modelo ER - Conceitos
 Tipo de entidade:
 Define um conjunto de entidades que têm
os mesmos atributos (propriedades)
 Descreve o esquema para um conjunto de
entidades que compartilham a mesma
estrutura
 Exemplos:
 Employee, Company
Modelo ER - Conceitos
 Chave de um tipo de entidade:
 Atributo que possui valor único para cada entidade
(instância)
 Ex. Nome da companhia, identidade do empregado
 Chave pode ser formada por vários atributos: chave
composta
 Registro do Veiculo: Numero de Registro e Estado
 Domínio de um atributo:
 Conjunto de valores que podem ser atribuídos a um
atributo para cada entidade individualmente
 Ex. Idade do Empregado: (16,70); Nome do
Empregado:String
Figura 3.5
Tipos de entidade e suas instâncias
Esquema conceitual
M
Modelo ER - Conceitos
 Relacionamentos:
 Associações entre duas ou mais entidades
distintas (instâncias) com um significado
 Exemplo:
 Employee John Smith Works-for Department
Research
 Employee Fred Brown Manages Department
Research
 Departament Research Controls Project X
Modelo ER - Conceitos
 Tipo de Relacionamento:
 Define um conjunto de associações entre n
tipos de entidade E1, E2,...,En
 Exemplo:
 Works-for entre Employee e Department
Employee Works-for Department
Modelo ER - Conceitos
 Tipo de Relacionamento:
 Matematicamente, um tipo de relacionamento R é
um conjunto de (instâncias de) relacionamentos ri,
onde cada ri associa n (instâncias de) entidades
(e1,...,en) e cada ej pertence a um tipo de entidade
Ej
 R  E1 x E2 x ... x En
 ri = (e1,...,en)
 Grau de um Tipo de Relacionamento
 Número de tipos de entidade participantes de um
tipo de relacionamento
Instâncias de um tipo de relacionamento binário
Instâncias de um tipo de relacionamento ternário
Esquema conceitual
M
Modelo ER - Conceitos
 Restrições sobre tipos de relacionamento:
 Limitam as possiveis combinações de entidades que
podem participar no conjunto de relacionamentos
 Cardinalidade: Especifica o número de instâncias de
um tipo de relacionamento do qual uma entidade pode
participar
 Participação: Especifica se a existência de uma
entidade depende de seu relacionamento com outra
entidade através de um tipo de relacionamento 
parcial ou total
 Ex. Todo empregado deve trabalhar para um departamento
(total)
 Ex. Nem todo empregado gerencia um departamento (parcial)
 Cardinalidade + Participação  Restrições
Estruturais
Cardinalidade 1:1
Cardinalidade M:N
Esquema conceitual
M
Modelo ER - Conceitos
 Papéis e relacionamentos recursivos
 Entidades atuam com um determinado papel
 Significado do papel é dado por um nome,
atribuído a cada tipo de entidade
 Nomes só são necessários em tipos de
relacionamento que envolvam mais de uma vez
o mesmo tipo de entidade  relacionamentos
recursivos
 Exemplo: Supervision, onde Employee tem os
papéis de Supervisor e Supervisee
Figure 3.11
1 – Supervisor
2 - Supervisee
Esquema conceitual
M
Modelo ER - Conceitos
 Tipos de Entidade Fraca
 Tipos de entidade que não têm chave própria
 As instâncias são identificadas através do
relacionamento com entidades de outro tipo,
chamado de dono ou identificador, juntamente
com os valores de alguns atributos (chave
parcial)
 Exemplo: Dependent
Esquema conceitual
M
Notação ER
(Resumo)
Modelo de Dados Relacional
Introdução
 O modelo relacional representa um banco de
dados como um conjunto de relações
 Informalmente, uma relação é uma tabela de
valores, onde cada linha representa uma
coleção de dados relacionados
 Cada linha de uma tabela representa um “fato”
que tipicamente corresponde a uma entidade
ou relacionamento do mundo real
Conceitos Básicos
 As linhas de uma relação (tabela) são chamadas
de tuplas
 Ao cabeçalho de cada coluna dá-se o nome de
atributo
 O conjunto de valores que pode aparecer em cada
coluna é chamado de domínio
Conceitos Básicos
 Esquema de relação
 Descreve a relação
 R(A1,A2, ...,An), onde:
 R  Nome da relação
 Ai  Nome de um atributo
 n  Grau da relação
 Cada Atributo Ai e’ o nome de um papel
desempenhado por algum dominio D no Esquema da
relação R
 Exemplo:
 Student(Name, SSN, HomePhone, Address,
OfficePhine, Age,GPA)
Conceitos Básicos
 Relação r(R)
 Conjunto de tuplas: r = {t1,t2, ..., tm}
 Cada tupla é uma lista ordenada de valores: t =
<v1,v2, ..., vn>
Características de uma Relação
 As tuplas de uma relação não são
ordenadas
 Registros em um arquivo são ordenados de
acordo com a posição em que são
armazenados no disco
Benjamin Bayer 305-61-2425 373-1616 2918 Bluebonnet Lane null 19 3.21
Características de uma Relação
 Uma tupla é uma lista ordenada de valores
 O valor de cada atributo em uma tupla é atômico
 Atributos compostos e multivalorados não são
permitidos
 O valor especial null é utilizado para representar valores
não conhecidos ou não aplicáveis a uma determinada
tupla
Restrições de Integridade
 Restrições de domínio
 Especificam que o valor de cada atributo A de uma
relação deve ser um valor atômico do domínio dom(A)
 Restrições de chave
 Por definição todas as tuplas sao distintas
 Um conjunto de atributos SK de um esquema de
relação R tal que, para duas tuplas quaisquer t1 e t2 de
r(R), t1[SK]  t2[SK] é uma super-chave de R
 Super-chave default: todos os atributos
 Uma chave de R é uma super-chave com a propriedade
adicional de que nenhum de seus subconjuntos
também seja uma super-chave de R
 {SSN,Name,Age} = super-chave; {SSN} = chave
Restrições de Integridade
 Restrições de chave
 Um esquema de relação pode ter mais de uma chave 
chaves candidatas
 Dentre as chaves candidatas de um esquema de
relação, uma delas é indicada como chave primária e as
demais constituem as chaves alternativas
Restrições de Integridade
 Restrições em valores null
 Especifica se a um atributo é permitido ter valores null
 Exemplo. Todo Estudante deve ter um nome válido,
não-null
Esquema de um BD Relacional
Restrições de Integridade
 Além das restrições de domínio e de
chave as seguintes restrições de
integridade são parte do modelo
relacional:
 Restrição de integridade de entidade
 Nenhum componente de uma chave primária
pode ser nulo
Restrições de Integridade
 Restrição de integridade referencial
 Usada para manter a consistencia entre tuplas de
duas relacoes
 Uma tupla em uma relação que se refere a outra
relação deve referenciar uma tupla existente nesta
outra relação
 Aparecem devido aos
relacionamentos entre
entidades
 Seja FK um conjunto de atributos de um esquema
de relação R1 definido sobre o mesmo domínio dos
atributos da chave primária PK de outro esquema R2.
Então, para qualquer tupla t1 de R1:
 t1[FK] = t2[PK], onde t2 é uma tupla de R2 ou
 t1[FK] é nulo
Restrições de integridade referencial
 A restrição de integridade referencial pode ser
expressa pela notação
R1[FK]  R2[PK],
onde PK é a chave primária de R2 e FK é a chave
estrangeira de R1
 Exemplos:
EMPLOYEE[DNO]  DEPARTMENT[DNUMBER]
WORKS_ON[ESSN]  EMPLOYEE[SSN]
WORKS_ON[PNO]  PROJECT[PNUMBER]
Restrições de Integridade
Instância de um BD Relacional
1
4
5
5
5 Houston
Instância de um BD Relacional
Opções de Remoção da RIR
 A cada RIR R1[FK]  R2[PK] é possível associar uma
opção de remoção que especifica como a remoção
de uma tupla de R2 é executada em relação a R1
 As opções de remoção possíveis são:
 bloqueio
 propagação
 substituição por nulos
 Notação:
R1[FK]  R2[PK],
onde op  {b, p, n}
op
Exemplos de RIR
EMPLOYEE(FNAME,MINT,LNAME,SSN,BDATE,ADDRESS,SEX,
SALARY,SUPERSSN,DNO)
EMPLOYEE[SUPERSSN]  EMPLOYEE[SSN]
EMPLOYEE[DNO]  DEPARTMENT[DNUMBER]
DEPARTMENT[DNAME,DNUMBER,MGRSSN,MGRDATE]
DEPARTMENT[MGRSSN]  EMPLOYEE[SSN]
DEPT_LOCATIONS(DNUMBER,LOCATION)
DEPT_LOCATIONS[DNUMBER] 
DEPARTMENT[DNUMBER]
n
b
b
p
Restrições de integridade referencial com opções de remoção
n
p
p
bb
b
b
b
Operações sobre Relações
 As operações sobre um BD relacional podem ser
classificadas em:
 Operações de recuperação (consulta)
 Operações de atualização
 Operações de atualização (sobre tuplas):
 Inserção (insert)
 Remoção (delete)
 Modificação (modify)
Operações sobre Relações
 Operações de atualização
 Restrições de integridade não podem ser
violadas
 Inserção
 Restrição de Dominio: valor fora do dominio
 Restrição de Chave: valor ja’ existe
 Restrição de integridade de entidade: se chave for null
 Restrição de integridade referencial: se chave estrangeira
referencia tupla inexistente
 Ação default: rejeitar inserção (com explicação)
Operações sobre Relações
 Operações de atualização
 Restrições de integridade não podem ser
violadas
 Remoção
 Restrição de integridade referencial: tupla deletada e’
referenciada por chaves estrangeiras
 Ação default: rejeitar inserção (com explicação)
 Segunda opção: propagar remoção de tuplas que violem
uma restrição de integridade referencial
 Terceira Opcao: Modificar o valor da chave estrangeira
para nulo
Operações sobre Relações
 Operações de atualização
 Restrições de integridade não podem ser
violadas
 Modificação
 Modificar o valor de um atributo que nao e’ chave primaria
ou estrangeira não causa problemas (se o valor for do
dominio, e, se for null, que este valor seja permitido)
 Modificar a chave primaria e’ igual a remover uma tupla e
inserir outra
 Modificar chave estrangeira: SGBD deve verificar se novo
valor do atributo referencia tupla existente
A Linguagem SQL
Introdução
 Originalmente proposta para o System R
desenvolvido nos laboratórios da IBM na década
de 70  SEQUEL (Structured English QUEry
Language)
 Objeto de um esforço de padronização
coordenado pelo ANSI/ISO:
 SQL1 (SQL-86)
 SQL2 (SQL-92)
 SQL3 (SQL:1999)
Introdução
 SQL = LDD + LMD + LCD
 Principais comandos:
 LDD:
 CREATE SCHEMA / TABLE / VIEW
 DROP SCHEMA / TABLE / VIEW
 ALTER TABLE
 LMD:
 SELECT, INSERT, UPDATE, DELETE
 LCD:
 GRANT, REVOKE
 Conceitos:
 Table = Relação
 Row = tupla
 Column = atributo
Definição de Dados em SQL
 Comando CREATE SCHEMA
 CREATE SCHEMA COMPANY AUTHORIZATION JS;
 Comando CREATE TABLE
 CREATE TABLE <nome da tabela>
(<definições de colunas>
<definição da chave primária>
<definições de chaves alternativas>
<definições de chaves estrangeiras>);
Definição de Dados em SQL
 Exemplo de um comando CREATE TABLE
CREATE TABLE EMPLOYEE
(FNAME VARCHAR(15) NOT NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
…
SUPERSSN CHAR(9),
DNO INT NOT NULL,
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE (SSN)
ON DELETE SET NULL,
FOREIGN KEY (DNO) REFERENCES DEPARTMENT (DNUMBER));
Definição de Dados em SQL
 Opções de remoção (cláusula ON DELETE):
 CASCADE (propagação)
 SET NULL (substituição por nulos)
 SET DEFAULT (substituição por um valor default)
 Opção default: bloqueio (RESTRICT)
 As mesmas opções se aplicam à cláusula ON
UPDATE
Restrição de Integridade Referencial 
em SQL
FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL
FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER)
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN) FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER)
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)
ON DELETE CASCADE
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE CASCADE
FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN)
Restrição de Integridade Referencial 
em SQL
Definição de Dados em SQL
 Comandos DROP SCHEMA e DROP TABLE
 DROP SCHEMA COMPANY CASCADE (RESTRICT);
 RESTRICT: APENAS SE NAO TEM ELEMENTOS
 DROP TABLE DEPENDENT CASCADE (RESTRICT);
 RESTRICT: SE A TABELA NAO E’ REFERENCIADA EM
QUALQUER RESTRICAO
 Comando ALTER TABLE
 ALTER TABLE COMPANY.EMPLOYEE
ADD JOB VARCHAR(12);
 Inicialmente Null para todas as tuplas
 ALTER TABLE COMPANY.EMPLOYEE
DROP ADDRESS CASCADE (RESTRICT);
 RESTRICT: SE NENHUMA VISAO OU RESTRICAO
REFERENCIA A COLUNA
Consultas Básicas em SQL
 Formato básico do comando SELECT:
SELECT <lista de atributos>
FROM <lista de tabelas>
WHERE <condição>;
 Exemplo:
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=‘John’ AND
MINIT=‘B’ AND
LNAME=‘Smith’;
Consultas Básicas em SQL
 SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=‘Research’ AND DNO=DNUMBER;
 SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE PLOCATION=‘Stafford’ AND
DNUM=DNUMBER AND MGRSSN=SSN;
condição de seleção condição de junção
Consultas Básicas em SQL
 Atributos ambíguos e pseudônimos (alias)
SELECT DNAME, DLOCATION
FROM DEPARTMENT, DEPT_LOCATIONS
WHERE DEPARTMENT.DNUMBER =
DEPT_LOCATIONS.DNUMBER;
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN;
Consultas Básicas em SQL
 Consultas sem a cláusula WHERE
SELECT SSN, LNAME, SALARY
FROM EMPLOYEE;
SELECT LNAME, DNAME
FROM EMPLOYEE, DEPARTMENT
Atenção! Esta consulta corresponde a um produto 
cartesiano das tabelas EMPLOYEE e DEPARTMENT
WHERE DNO=DNUMBER;
Consultas Básicas em SQL
 Manipulando tabelas como conjuntos
SELECT SALARY
FROM EMPLOYEE;
SELECT DISTINCT SALARY
FROM EMPLOYEE;
(SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND 
LNAME=‘Smith’)
UNION
(SELECT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNO AND ESSN=SSN AND 
LNAME=‘Smith’);
Não elimina linhas (tuplas) duplicatas
Consultas Complexas em SQL
 Consultas aninhadas
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=‘Research’);
é equivalente à consulta
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=‘Research’;
Consultas Complexas em SQL
 Comparação de conjuntos
SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN (SELECT PNUMBER
FROM PROJECT, DEPARTMENT,
EMPLOYEE
WHERE DNUM =DNUMEBR
AND
MGRSSN=SSN AND
LNAME=‘Smith’)
OR
PNUMBER IN (SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN=SSN AND
LNAME=‘Smith’);
Consultas Complexas em SQL
 Comparação de conjuntos
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) IN (SELECT PNO, HOURS
FROM WORKS_ON
WHERE ESSN=‘123456789’);
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY
FROM EMPLOYEE
WHERE DNO=5);
Consultas Complexas em SQL
 Uso da função EXISTS
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE EXISTS (SELECT *
FROM DEPENDENT
WHERE E.SSN=ESSN AND
E.SEX=SEX AND
E.FNAME=DEPENDENT_NAME);
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (SELECT *
FROM DEPENDENT
WHERE SSN=ESSN);
Consultas Complexas em SQL
 Uso do operador CONTAINS
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ((SELECT PNO
FROM WORKS_ON
WHERE SSN=ESSN)
CONTAINS
(SELECT PNUMBER
FROM PROJECT
WHERE DNUM=5));
Facilidades Adicionais 
 Uso do operador JOIN
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEE JOIN DEPARTMENT
ON DNO=DNUMEBR)
WHERE DNAME=‘Research’;
SELECT DNAME, DLOCATION
FROM (DEPARTMENT NATURAL JOIN DEPT_LOCATIONS);
SELECT FNAME, LNAME, DEPENDENT_NAME
FROM (EMPLOYEE LEFT OUTER JOIN DEPENDENT
ON SSN=ESSN);
Facilidades Adicionais
 Agrupamento
SELECT DNO, COUNT(*), AVG(SALARY)
FROM EMPLOYEE
GROUP BY DNO;
Facilidades Adicionais
 Agrupamento com a cláusula HAVING
SELECT PNUMBER, PNAME, COUNT(*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2;
3
3
3
3
Atualizações em SQL
 Comando INSERT
INSERT INTO EMPLOYEE
VALUES (‘Richard’,‘K’,‘Marini’,‘653258653’,‘1962-12-30’,
’98 Oak Forest, Katy, TX’,37000,’987654321’,4);
INSERT INTO EMPLOYEE(FNAME, LNAME, SSN, DNO)
VALUES (‘Richard’,‘Marini’,‘653258653’,4);
INSERT INTO EMPLOYEE(FNAME, LNAME, SSN, DNO)
SELECT * FROM INPUT;
Atualizações em SQL
 Comando DELETE
DELETE FROM EMPLOYEE
WHERE LNAME=‘Brown’;
DELETE FROM EMPLOYEE
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=‘Research’);
DELETE FROM EMPLOYEE;
Atualizações em SQL
 Comando UPDATE
UPDATE PROJECT
SET PLOCATION=‘Bellaire’, DNUM=5
WHERE PNUMBER=10;
UPDATE EMPLOYEE
SET SALARY=SALARY*1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME=‘Research’);
Projeto Lógico de Bancos de 
Dados Relacionais
Tópicos
 Processo de Projeto de Bancos de Dados
 Exemplo Preliminar
 Representação Relacional de Esquemas ER
 Implementação Usando SQL
 Referências Bibliográficas
Processo de Projeto de
Bancos de Dados
 Caracterização
 Complexidade
 Multiplicidade de tarefas
 Fases
 Coleção e análise de requisitos
 Projeto conceitual
 Escolha de um sistema gerenciador de banco de dados
 Projeto lógico (ou mapeamento para o modelo de
dados do SGBD escolhido)
 Projeto físico
 Implementação e “tuning”
Fases do Processo de Projeto de 
Bancos de Dados
Mini-Mundo
Análise de
Requisitos
Requisitos do BD
Projeto Conceitual
Esquema Conceitual 
(em um modelo de dados de alto nível)
Projeto Lógico
Esquema Lógico
(em um modelo de dados lógico)
Projeto Físico
Esquema Físico
(para um SGBD específico)
Requisitos Funcionais
Análise Funcional
Especificação das Transações
(em alto nível)
Projeto das Aplicações
Implementação
Programas
Independente de SGBD
Específico para um SGBD
Abordagem ER para Projeto Lógico de 
Bancos de Dados Relacionais
Mini-Mundo
Análise de
Requisitos
Requisitos do BD
Projeto Conceitual
Esquema Conceitual 
(em um modelo de dados de alto nível)
Projeto Lógico
Esquema Lógico
(em um modelo de dados 
lógico)
Projeto Físico
Esquema Físico
(para um SGBD específico)
Requisitos Funcionais
Análise Funcional
Especificação das Transações
(em alto nível)
Projeto das Aplicações
Implementação
Programas
Independente de SGBD
Específico para um SGBD
Modelo ER
Modelo
Relacional
SGBD
Relacional
Aplicação exemplo
 Banco de Dados de uma companhia
 Organizada em departamentos que têm um nome e um
número únicos e um empregado que gerencia o
departamento. A data de quando o empregado
começou a gerenciar o departamento deve ser
registrada. Um departamento pode ter varias
localizações
 Um departamento controla um número de projetos,
cada qual com um nome e número únicos e uma única
localização
Aplicação exemplo
 Banco de Dados de uma companhia
 Nós armazenamos para cada empregado seu
nome, identidade, endereço, salário, sexo, e data
de nascimento. Um empregado e’ assinalado a um
departamento mas pode trabalhar em diversos
projetos, os quais não são necessariamente
controlados pelo mesmo departamento. Nos
registramos o número de horas por semana que o
empregado trabalha em cada projeto e o
supervisor direto de cada empregado
 Nós mantemos registro para cada empregado, do
numero de dependentes (para seguro) e para
cada dependente o primeiro nome, sexo, data de
nascimento e relacionamento com o empregado.
M
EMPLOYEE[SUPERSSN]  EMPLOYEE[SSN]
EMPLOYEE[DNO]  DEPARTMENT[DNUMBER]
DEPARTMENT[MGRSSN]  EMPLOYEE[SSN]
DEPT_LOCATIONS[DNUMBER]  DEPARTMENT[DNUMBER]
PROJECT[DNUM]  DEPARTMENT[DNUMBER]
WORKS_ON[ESSN]  EMPLOYEE[SSN]
WORKS_ON[PNO]  PROJECT[PNUMBER] 
DEPENDENT[ESSN]  EMPLOYEE[SSN]
n
b
b
b
b
b
p
p
Representação Relacional de Esquemas 
ER
 Estratégias de representação
 Mapeamento 1-1: cada tipo de entidade ou de
relacionamento é representado por um esquema de
relação separado
 Mapeamento otimizado: tipos de relacionamento
funcionais (1:1 e N:1) e subtipos de entidade são
colapsados e representados através de atributos em
outro esquema de relação
Modelo Relacional
Notação
 Esquema de relação
 R (A1,A2,…,An), onde A1 é a chave primária de R
 Restrição de integridade referencial
 R1 [X]  R2 [Y], onde X é um conjunto de atributos
de R1 que referencia a chave Y de R2
 Restrições estruturais
 <expr1> op <expr2>, onde <expr1> e <expr2> são
expressões da álgebra relacional e op é um dos
operadores , ,  ou 
Exemplo de um Diagrama ER
Empregado
ProjetoDependente
Departamento
Trabalha-para
Gerencia
Participa-de
Controla
N 1
1 11 1
N N
N
M
NEmp NomeEmp Salário
NomeDep DataNasc
NDept NomeDept Ramal
NProj NomeProj LocalHsTrab
Possui
Representação de Tipos de Entidade 
(sem atributos multivalorados)
Empregado
NEmp
NomeEmp
Salário
Empregado (NEmp(nn),NomeEmp,Salário)
Representação de Tipos de Entidade 
(com atributos multivalorados)
Departamento
NDept
NomeDept
Ramal
Departamento (NDept(nn),NomeDept)
Ramal-Departamento (NDept(nn), Ramal(nn))
Ramal-Departamento [NDept]  Departamento [NDept]p
Representação de Tipos de 
Entidade Fraca
Empregado (NEmp(nn),...)
Dependente (NEmp(nn),NomeDep(nn), DataNasc)
Dependente [NEmp]  Empregado [NEmp]p
Empregado Dependente1
N
NEmp NomeDep DataNasc
Possui
Representação de Tipos de 
Relacionamento N:1
(mapeamento 1-1)
Empregado Departamento
1N
NEmp NDept
Trabalha-para
Empregado (NEmp(nn),...)
Departamento (NDept(nn),...)
Trabalha-para [NEmp]  Empregado [NEmp]p
Trabalha-para (NEmp(nn),NDept(nn))
Trabalha-para [NDept]  Departamento [NDept]b
NEmp(Empregado) = NEmp(Trabalha-para) 
Representação de Tipos de 
Relacionamento N:1
(mapeamento otimizado)
Empregado Departamento
1N
NEmp NDept
Trabalha-para
Empregado (NEmp(nn),...,NDept(nn))
Departamento (NDept(nn),...)
Empregado [NDept]  Departamento [NDept]b
Representação de Tipos de 
Relacionamento 1:1
(mapeamento otimizado)
Empregado Departamento
11
NEmp NDept
Gerencia
Empregado (NEmp(nn),...)
Departamento (NDept(nn),...,NEmp(nn))
Departamento [NEmp]  Empregado [NEmp]b
Chave alternativa
Representação de Tipos de 
Relacionamento M:N
Empregado Projeto
NM
NEmp NProj
Participa-de
HsTrab
Empregado (NEmp(nn),...)
Projeto (NProj(nn), ...)
Participa-de [NEmp]  Empregado [NEmp]
Participa-de (NEmp(nn),NProj(nn), HsTrab)
Participa-de [NProj]  Projeto [NProj]
p
p
Implementação usando SQL
 SQL
 Composta de três sublinguagens: LDD, LMD e LCD
 Objeto de padronização pelo ANSI/ISO
 Comando básico de definição de dados:
create table <table name>
(<column definitions>
<primary key definition>
<alternate key definitions>
<foreign key definitions>)
Definição de um Esquema de Relação 
em SQL
create table Empregado
(NEmp char(3) not null,
NomeEmp char(30) not null,
Salario decimal(6,2),
NDept char(2) not null,
primary key (NEmp),
foreign key (NDept) references
Departamento)
Restrições de Integridade em SQL
 Restrições de unicidade (unique constraints) que
indicam a chave primária e as chaves alternativas
de uma tabela
 Restrições de integridade referencial (referential
constraints) que especificam as chaves estrangei-
ras de uma tabela
 Restrições de verificação (check constraints) que
especificam condições que devem ser satisfeitas
por coluna/linhas de uma tabela ou entre tabelas
Restrições de Unicidade
 Chave primária
primary key (<attribute list>)
 Chaves alternativas
unique (<attribute list>)
create table Departamento
( ...
primary key (NDept),
unique (NomeDept),
...)
Restrições de Integridade Referencial
foreign key (<attribute list>)
references <table name> [(<attribute list>)]
[on delete cascade | set null | set default]
[on update cascade | set null | set default]
create table Participa-de
(...
foreign key NEmp references Empregado
on delete cascade)
Referências
 Batini, C.; Ceri, S.; Navathe, S.B. Conceptual Database Design: An Entity-
Relationship Approach. Benjamin/Cummings, Redwood City, CA, 1992.
 Elmasri, R.; Navathe, S.B. Fundamentals of Database Systems, 3rd ed.,
Addison-Wesley, MA, 2000.
 Laender, A.H.F.; Casanova, M.A.; Carvalho, A.P.; Ridolfi, L.F. An Analysis of
SQL Integrity Constraints from an Entity-Relationship Model Perspective.
Information Systems 4, 3(1994), 423-464.
 Silva, A.S.; Laender, A.H.F.; Casanova, M.A. An Approach to Maintaining
Optimizing Relational Representations of Entity-Relationship Schemas. In
Thalheim, B. (ed.). Conceptual Modeling -ER’96. Springer-Verlag, Berlin, 1996,
pp. 242-256.
 Silva, A.S.; Laender, A.H.F.; Casanova, M.A. On the Relational Representation
of Specialization Structures. Information Systems 25, 6(2000), 399-415.