Sistemas de Banco de Dados
Marcos André Gonçalves
2007-2
Introduçã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
Usuários/Programadores
Consultas/Programas
SGBD
Catálogo
(Meta-Dados)
Banco
de
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 processamento 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 multiusuá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
Regras para
estruturação dos
dados
Esquema
Regras para
verificação das
instâncias
Instância
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)


Linguagem de manipulação de dados
(LMD)


Recuperação, inserção, remoção, modificação
do BD
Linguagem de consulta


Usada para definir esquemas
LMD de alto nivel usada em modo “standalone”
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:






Quanto ao número de usuários suportados:



Relacionais
De rede
Hierárquicos
Orientados a objetos
Objeto-relacionais
Mono-usuários
Multi-usuários
Quanto à localização dos dados:


Centralizados
Distribuídos
Exemplo de um BD Relacional
Empregado
NumEmp
032
NomeEmp
J Silva
Salário
380
Dept
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
Departamento
NumDept
NomeDept
Ramal
21
Pessoal
142
25
Financeiro
143
28
Técnico
144
Exemplo de um BD de Rede
Empregado
Departamento
21
25
28
Pessoal
Financeiro
Técnico
142
143
144
032
J Silva
380
074
M Reis
400
089
C Melo
520
092
R Silva
480
112
R Pinto
390
121
V Simão
130
J Neves
905
640
Exemplo de um BD Hierárquico
Departamento
21
Pessoal
142
25
380
074
Financeiro
143
28
Técnico
144
089
C Melo
520
Empregado
032
112
J Silva
R Pinto
390
092
M Reis
R Silva
400
480
121
130
V Simão
J Neves
905
640
Modelo Entidade-Relacionamento
Processo de Projeto de
Bancos de Dados
Mini-Mundo
Análise de
Requisitos
Requisitos Funcionais
Requisitos do BD
Análise Funcional
Projeto Conceitual
Especificação das Transações
Esquema Conceitual
(em alto nível)
(em um modelo de dados de alto nível)
Independente de SGBD
Projeto Lógico
Esquema Lógico
(em um modelo de dados lógico)
Específico para um SGBD
Projeto das Aplicações
Projeto Físico
Implementação
Programas
Esquema Físico
(para um SGBD específico)
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.
M
Esquema conceitual
Modelo ER - Conceitos

Entidades:


Objetos do mundo real que são de interesse
para alguma aplicação
Atributos:

Propriedades utilizadas para descrever uma
entidade
e1
(Employee)
Name = John
Address = 2311 Kirby, Houston, TX
Age = 55
Home Phone = 713-749-2630
Modelo ER - Conceitos

Tipos (classes) de atributo:

Simples ou compostos


Monovalorados ou multivalorados


Ex. Profissão
Armazenados ou derivados


Ex. Endereço (Endereço da Rua (número, nome da rua,
número do apto), Cidade, Estado, CEP)
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)


Chave pode ser formada por vários atributos: chave
composta


Ex. Nome da companhia, identidade do empregado
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
M
Esquema conceitual
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
M
Esquema conceitual
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
Estruturais
+
Participação

Restrições
Cardinalidade 1:1
Cardinalidade M:N
M
Esquema conceitual
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
M
Esquema conceitual
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
M
Esquema conceitual
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,
OfficePhine, Age,GPA)
HomePhone,
Address,
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
ordenadas
Benjamin Bayer

de
305-61-2425
uma
373-1616
relação
2918 Bluebonnet Lane
não
null
são
19
3.21
Registros em um arquivo são ordenados de
acordo com a posição em que são
armazenados no disco
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
Restrições de Integridade

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]
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:
op
R1[FK]  R2[PK],
onde op  {b, p, n}
Exemplos de RIR
EMPLOYEE(FNAME,MINT,LNAME,SSN,BDATE,ADDRESS,SEX,
SALARY,SUPERSSN,DNO)
n
EMPLOYEE[SUPERSSN]  EMPLOYEE[SSN]
b
EMPLOYEE[DNO]  DEPARTMENT[DNUMBER]
DEPARTMENT[DNAME,DNUMBER,MGRSSN,MGRDATE]
b
DEPARTMENT[MGRSSN]  EMPLOYEE[SSN]
DEPT_LOCATIONS(DNUMBER,LOCATION)
p
DEPT_LOCATIONS[DNUMBER]
DEPARTMENT[DNUMBER]

n
b
b
p
b
b
p
b
Restrições de integridade referencial com opções de remoção
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:




LMD:


SELECT, INSERT, UPDATE, DELETE
LCD:


CREATE SCHEMA / TABLE / VIEW
DROP SCHEMA / TABLE / VIEW
ALTER TABLE
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 (MGRSSN) REFERENCES EMPLOYEE(SSN)
FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER)
ON DELETE CASCADE
FOREIGN KEY (DNUM) REFERENCES DEPARTMENT(DNUMBER)
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)
FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)
ON DELETE CASCADE
FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER)
Restrição de Integridade Referencial
em SQL

Definição de Dados em SQL
Comandos DROP SCHEMA e DROP TABLE

DROP SCHEMA COMPANY CASCADE (RESTRICT);


DROP TABLE DEPENDENT CASCADE (RESTRICT);


RESTRICT: APENAS SE NAO TEM ELEMENTOS
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
REFERENCIA A COLUNA
VISAO
OU
RESTRICAO
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;
condição de seleção

condição de junção
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE PLOCATION=‘Stafford’ AND
DNUM=DNUMBER AND MGRSSN=SSN;
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
WHERE DNO=DNUMBER;
Atenção! Esta consulta corresponde a um produto
cartesiano das tabelas EMPLOYEE e DEPARTMENT
Consultas Básicas em SQL

Manipulando tabelas como conjuntos
SELECT SALARY
FROM EMPLOYEE;
Não elimina linhas (tuplas) duplicatas
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’);
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 Funcionais
Requisitos do BD
Análise Funcional
Projeto Conceitual
Especificação das Transações
Esquema Conceitual
(em alto nível)
(em um modelo de dados de alto nível)
Independente de SGBD
Projeto Lógico
Esquema Lógico
(em um modelo de dados lógico)
Específico para um SGBD
Projeto das Aplicações
Projeto Físico
Implementação
Programas
Esquema Físico
(para um SGBD específico)
Abordagem ER para Projeto Lógico de
Bancos de Dados Relacionais
Mini-Mundo
Modelo ER
Análise de
Requisitos
Requisitos Funcionais
Requisitos do BD
Análise Funcional
Projeto Conceitual
Esquema Conceitual
Especificação das Transações
(em alto nível)
(em um modelo de dados de alto nível)
Independente de SGBD
Projeto Lógico
Específico para um SGBD
Projeto das Aplicações
Esquema Lógico
(em um modelo de dados
lógico)
Projeto Físico
Implementação
Programas
Modelo
Relacional
Esquema Físico
(para um SGBD específico)
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
n
EMPLOYEE[SUPERSSN]  EMPLOYEE[SSN]
b
EMPLOYEE[DNO]  DEPARTMENT[DNUMBER]
b
DEPARTMENT[MGRSSN]  EMPLOYEE[SSN]
p
DEPT_LOCATIONS[DNUMBER]  DEPARTMENT[DNUMBER]
b
PROJECT[DNUM]  DEPARTMENT[DNUMBER]
b
WORKS_ON[ESSN]  EMPLOYEE[SSN]
b
WORKS_ON[PNO]  PROJECT[PNUMBER]
p
DEPENDENT[ESSN]  EMPLOYEE[SSN]
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,A ,…,An), onde A1 é a chave primária de R
2
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
NEmp
NomeEmp
Salário
Trabalha-para
N
Empregado
1
NDept
1
Possui
Gerencia
NomeDep
DataNasc
Ramal
1
Departamento
1
1
M
Controla
N
Dependente
NomeDept
Participa-de
HsTrab
N
NProj
N
Projeto
NomeProj
Local
Representação de Tipos de Entidade
(sem atributos multivalorados)
NEmp
Empregado
NomeEmp
Salário
Empregado (NEmp(nn),NomeEmp,Salário)
Representação de Tipos de Entidade
(com atributos multivalorados)
NDept
Departamento
NomeDept
Ramal
Departamento (NDept(nn),NomeDept)
Ramal-Departamento (NDept(nn), Ramal(nn))
p
Ramal-Departamento [NDept] 
Departamento [NDept]
Representação de Tipos de
Entidade Fraca
Empregado
1
Possui
NEmp
N
Dependente
NomeDep
DataNasc
Empregado (NEmp(nn),...)
Dependente (NEmp(nn),NomeDep(nn), DataNasc)
p
Dependente [NEmp] 
Empregado [NEmp]
Representação de Tipos de
Relacionamento N:1
(mapeamento 1-1)
Empregado
N
Trabalha-para
1
Departamento
NEmp
NDept
Empregado (NEmp(nn),...)
Departamento (NDept(nn),...)
Trabalha-para (NEmp(nn),NDept(nn))
p
Trabalha-para [NEmp] 
Empregado [NEmp]
b Departamento [NDept]
Trabalha-para [NDept] 
NEmp (Empregado) = NEmp (Trabalha-para)
Representação de Tipos de
Relacionamento N:1
(mapeamento otimizado)
Empregado
N
Trabalha-para
NEmp
1
Departamento
NDept
Empregado (NEmp(nn),...,NDept(nn))
Departamento (NDept(nn),...)
b
Empregado [NDept] 
Departamento [NDept]
Representação de Tipos de
Relacionamento 1:1
(mapeamento otimizado)
Empregado
1
Gerencia
1
Departamento
NEmp
NDept
Empregado (NEmp(nn),...)
Departamento (NDept(nn),...,NEmp(nn))
Chave alternativa
b
Departamento [NEmp] 
Empregado [NEmp]
Representação de Tipos de
Relacionamento M:N
Empregado
NEmp
M
Participa-de
HsTrab
N
Projeto
NProj
Empregado (NEmp(nn),...)
Projeto (NProj(nn), ...)
Participa-de (NEmp(nn),NProj(nn), HsTrab)
p
Participa-de [NEmp] 
Empregado [NEmp]
p
Participa-de [NProj] 
Projeto [NProj]
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)
Departamento)
references
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 estrangeiras 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 EntityRelationship 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.