BANCO DE DADOS II
Conceitos Básicos
COTEMIG
Gerson Borges
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
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
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)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
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
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
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
Modelo de Dados, Esquema e Instância
Relação entre Modelo de Dados,
Esquema e Instância
Modelo de
Dados Regras para Esquema Instância estruturação dos
dados
Regras para verificação das
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
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
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
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”
Utilitários
Carregamento
Backup
E.g. dumps do banco de dados
(Re-)Organização de arquivos
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
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 EmpregadoExemplo 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 520 074 M Reis 400 Departamento Empregado
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
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
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-2630
e1
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
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:
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
Figura 3.5
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
Modelo ER - Conceitos
Tipo de Relacionamento:
Define um conjunto de associações entre
n
tipos de entidade E
1, E
2,...,E
n
Exemplo:
Works-for entre Employee e 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
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 + Participação
Restrições
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
Figure 3.11
1 – Supervisor
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
)
Esquema conceitual
Notação ER
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
Conceitos Básicos
Esquema de relação
Descreve a relação
R(A
1,A
2, ...,A
n), 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,
Conceitos Básicos
Relação r(R)
Conjunto de tuplas: r = {t
1,t
2, ..., t
m}
Cada tupla é uma lista ordenada de valores: t =
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
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
çãotodas 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
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,
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
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
A restrição de integridade referencial pode ser
expressa pela notação
R1[FK] R2[PK],
onde
PKé a
chave primária
de
R2e
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 HoustonOpçõ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}
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 pRestrições de integridade referencial com opções de remoção n p p b b 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)
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
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
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
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 TABLECREATE 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,
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
ONRestriçã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
Restrição de Integridade Referencial
em SQL
Definição de Dados em SQL
Comandos
DROP SCHEMAe
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
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;
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
Consultas Básicas em SQL
Consultas sem a cláusula
WHERESELECT 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
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’);
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
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
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
EXISTSSELECT 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
CONTAINSSELECT 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
JOINSELECT 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
Facilidades Adicionais
Agrupamento com a cláusula
HAVINGSELECT 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
INSERTINSERT 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
DELETEDELETE 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
UPDATEUPDATE PROJECT
SET PLOCATION=‘Bellaire’, DNUM=5 WHERE PNUMBER=10;
UPDATE EMPLOYEE
SET SALARY=SALARY*1.1
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT
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
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
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
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.
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,
A
2,…,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 Projeto Dependente Departamento Trabalha-para Gerencia Participa-de Controla N 1 1 1 1 1 N N N MNEmp NomeEmp Salário
NomeDep DataNasc
NDept NomeDept Ramal
NProj NomeProj Local HsTrab
Representação de Tipos de Entidade
(sem atributos multivalorados)
Empregado
NEmp
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))
Representação de Tipos de
Entidade Fraca
Empregado (NEmp(nn),...)
Dependente (NEmp(nn),NomeDep(nn), DataNasc) Dependente [NEmp] p Empregado [NEmp]
Empregado 1 N Dependente
NEmp NomeDep DataNasc
Representação de Tipos de
Relacionamento N:1
(mapeamento 1-1)
Empregado N 1 Departamento NEmp NDept Trabalha-para Empregado (NEmp(nn),...) Departamento (NDept(nn),...)Trabalha-para [NEmp] p Empregado [NEmp] Trabalha-para (NEmp(nn),NDept(nn))
Trabalha-para [NDept] b Departamento [NDept]
NEmp(Empregado) = NEmp(Trabalha-para)
Representação de Tipos de
Relacionamento N:1
(mapeamento otimizado)
Empregado N 1 Departamento NEmp NDept Trabalha-para Empregado (NEmp(nn),...,NDept(nn)) Departamento (NDept(nn),...)Representação de Tipos de
Relacionamento 1:1
(mapeamento otimizado)
Empregado 1 1 Departamento NEmp NDept Gerencia Empregado (NEmp(nn),...) Departamento (NDept(nn),...,NEmp(nn))Departamento [NEmp] b Empregado [NEmp]
Representação de Tipos de
Relacionamento M:N
Empregado M N Projeto 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
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
