BANCO DE DADOS II Conceitos Básicos -...

View
215
Download
0
Category

Documents

Preview:

Citation preview

BANCO DE DADOS IIConceitos Básicos

COTEMIG

Gerson Borges

gerson@cotemig.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 dadosrelacionados

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

Bancode

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

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ânciaRegras para

estruturação dosdados

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 umnúmero únicos e um empregado que gerencia odepartamento. A data de quando o empregadocomeçou a gerenciar o departamento deve serregistrada. Um departamento pode ter variaslocalizações

Um departamento controla um número de projetos,cada qual com um nome e número únicos e uma únicalocalizaçã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 datade nascimento. Um empregado é assinalado a umdepartamento mas pode trabalhar em diversosprojetos, os quais não são necessariamentecontrolados pelo mesmo departamento. Nosregistramos o número de horas por semana que oempregado trabalha em cada projeto e osupervisor direto de cada empregado

Nós mantemos registro para cada empregado, donumero de dependentes (para seguro) e paracada dependente o primeiro nome, sexo, data denascimento e relacionamento com o empregado.

Esquema conceitual

Modelo ER - Conceitos

Entidades:

Objetos do mundo real que são de interessepara alguma aplicação

Atributos:

Propriedades utilizadas para descrever umaentidade

Name = John

Address = 2311 Kirby, Houston, TXAge = 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êmos mesmos atributos (propriedades)

Descreve o esquema para um conjunto deentidades que compartilham a mesmaestrutura

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: chavecomposta 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 doEmpregado:String

Figura 3.5

Tipos de entidade e suas instâncias

Esquema conceitual

Modelo ER - Conceitos

Relacionamentos:

Associações entre duas ou mais entidadesdistintas (instâncias) com um significado

Exemplo:

Employee John Smith Works-for DepartmentResearch

Employee Fred Brown Manages DepartmentResearch

Departament Research Controls Project X

Modelo ER - Conceitos

Tipo de Relacionamento:

Define um conjunto de associações entre ntipos 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 entidadeEj

R E1 x E2 x ... x En

ri = (e1,...,en)

Grau de um Tipo de Relacionamento

Número de tipos de entidade participantes de umtipo de relacionamento

Instâncias de um tipo de relacionamento binário

Instâncias de um tipo de relacionamento ternário

Esquema conceitual

Modelo ER - Conceitos Restrições sobre tipos de relacionamento:

Limitam as possiveis combinações de entidades quepodem participar no conjunto de relacionamentos

Cardinalidade: Especifica o número de instâncias deum tipo de relacionamento do qual uma entidade podeparticipar

Participação: Especifica se a existência de umaentidade depende de seu relacionamento com outraentidade 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çõesEstruturais

Cardinalidade 1:1

Cardinalidade M:N

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 derelacionamento que envolvam mais de uma vezo mesmo tipo de entidade relacionamentosrecursivos

Exemplo: Supervision, onde Employee tem ospapéis de Supervisor e Supervisee

Figure 3.11

1 – Supervisor2 - Supervisee

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 dorelacionamento com entidades de outro tipo,chamado de dono ou identificador, juntamentecom os valores de alguns atributos (chaveparcial)

Exemplo: Dependent

Esquema conceitual

Notação ER(Resumo)

Modelo de Dados Relacional

Introdução

O modelo relacional representa um banco dedados como um conjunto de relações

Informalmente, uma relação é uma tabela devalores, onde cada linha representa umacoleção de dados relacionados

Cada linha de uma tabela representa um “fato”que tipicamente corresponde a uma entidadeou 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 deatributo

O conjunto de valores que pode aparecer em cadacoluna é 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 papeldesempenhado por algum dominio D no Esquema darelaçã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 deacordo com a posição em que sãoarmazenados 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ãopermitidos

O valor especial null é utilizado para representar valoresnão conhecidos ou não aplicáveis a uma determinadatupla

Restrições de Integridade Restrições de domínio

Especificam que o valor de cada atributo A de umarelaçã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 derelação R tal que, para duas tuplas quaisquer t1 e t2 der(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 propriedadeadicional de que nenhum de seus subconjuntostambé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 derelação, uma delas é indicada como chave primária e asdemais 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 dechave as seguintes restrições deintegridade são parte do modelorelacional:

Restrição de integridade de entidade

Nenhum componente de uma chave primáriapode ser nulo

Restrições de Integridade Restrição de integridade referencial

Usada para manter a consistencia entre tuplas deduas relacoes

Uma tupla em uma relação que se refere a outrarelação deve referenciar uma tupla existente nestaoutra relação

Aparecem devido aos relacionamentos entreentidades

Seja FK um conjunto de atributos de um esquemade relação R1 definido sobre o mesmo domínio dosatributos 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 serexpressa pela notação

R1[FK] R2[PK],

onde PK é a chave primária de R2 e FK é a chaveestrangeira 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

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 umaopção de remoção que especifica como a remoçãode 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]

Restrições de integridade referencial com opções de remoção

Operações sobre Relações

As operações sobre um BD relacional podem serclassificadas 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 servioladas

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 estrangeirareferencia 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 servioladas

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 violemuma restrição de integridade referencial

Terceira Opcao: Modificar o valor da chave estrangeirapara nulo

Operações sobre Relações

Operações de atualização

Restrições de integridade não podem servioladas

Modificação

Modificar o valor de um atributo que nao e’ chave primariaou estrangeira não causa problemas (se o valor for dodominio, e, se for null, que este valor seja permitido)

Modificar a chave primaria e’ igual a remover uma tupla einserir outra

Modificar chave estrangeira: SGBD deve verificar se novovalor do atributo referencia tupla existente

A Linguagem SQL

Introdução

Originalmente proposta para o System Rdesenvolvido nos laboratórios da IBM na décadade 70 SEQUEL (Structured English QUEry

Language)

Objeto de um esforço de padronizaçãocoordenado 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 EMQUALQUER RESTRICAO

Comando ALTER TABLE

ALTER TABLE COMPANY.EMPLOYEEADD JOB VARCHAR(12);

Inicialmente Null para todas as tuplas

ALTER TABLE COMPANY.EMPLOYEEDROP 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, ADDRESSFROM EMPLOYEEWHERE FNAME=‘John’ AND

MINIT=‘B’ ANDLNAME=‘Smith’;

Consultas Básicas em SQL

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DNAME=‘Research’ AND DNO=DNUMBER;

SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATEFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE 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_LOCATIONSWHERE DEPARTMENT.DNUMBER =

DEPT_LOCATIONS.DNUMBER;

SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME

FROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.SUPERSSN=S.SSN;

Consultas Básicas em SQL

Consultas sem a cláusula WHERE

SELECT SSN, LNAME, SALARY

FROM EMPLOYEE;

SELECT LNAME, DNAMEFROM 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 SALARYFROM EMPLOYEE;

SELECT DISTINCT SALARYFROM EMPLOYEE;

(SELECT PNUMBERFROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN AND

LNAME=‘Smith’)UNION(SELECT PNUMBERFROM PROJECT, WORKS_ON, EMPLOYEEWHERE PNUMBER=PNO AND ESSN=SSN AND

LNAME=‘Smith’);

Não elimina linhas (tuplas) duplicatas

Consultas Complexas em SQL

Consultas aninhadas

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEEWHERE DNO IN (SELECT DNUMBER

FROM DEPARTMENTWHERE DNAME=‘Research’);

é equivalente à consulta

SELECT FNAME, LNAME, ADDRESSFROM EMPLOYEE, DEPARTMENTWHERE DNO=DNUMBER AND DNAME=‘Research’;

Consultas Complexas em SQL Comparação de conjuntos

SELECT DISTINCT PNUMBERFROM PROJECTWHERE PNUMBER IN (SELECT PNUMBER

FROM PROJECT, DEPARTMENT,EMPLOYEE

WHERE DNUM =DNUMEBR ANDMGRSSN=SSN ANDLNAME=‘Smith’)

ORPNUMBER IN (SELECT PNO

FROM WORKS_ON, EMPLOYEEWHERE ESSN=SSN AND

LNAME=‘Smith’);

Consultas Complexas em SQL Comparação de conjuntos

SELECT DISTINCT ESSNFROM WORKS_ONWHERE (PNO, HOURS) IN (SELECT PNO, HOURS

FROM WORKS_ONWHERE ESSN=‘123456789’);

SELECT LNAME, FNAMEFROM EMPLOYEEWHERE SALARY > ALL (SELECT SALARY

FROM EMPLOYEEWHERE DNO=5);

Consultas Complexas em SQL Uso da função EXISTS

SELECT E.FNAME, E.LNAMEFROM EMPLOYEE AS EWHERE EXISTS (SELECT *

FROM DEPENDENTWHERE E.SSN=ESSN AND

E.SEX=SEX ANDE.FNAME=DEPENDENT_NAME);

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE NOT EXISTS (SELECT *

FROM DEPENDENTWHERE SSN=ESSN);

Consultas Complexas em SQL Uso do operador CONTAINS

SELECT FNAME, LNAMEFROM EMPLOYEEWHERE ((SELECT PNO

FROM WORKS_ONWHERE SSN=ESSN)CONTAINS(SELECT PNUMBERFROM PROJECTWHERE DNUM=5));

Facilidades Adicionais

Uso do operador JOIN

SELECT FNAME, LNAME, ADDRESSFROM (EMPLOYEE JOIN DEPARTMENT

ON DNO=DNUMEBR)WHERE DNAME=‘Research’;

SELECT DNAME, DLOCATION

FROM (DEPARTMENT NATURAL JOIN DEPT_LOCATIONS);

SELECT FNAME, LNAME, DEPENDENT_NAMEFROM (EMPLOYEE LEFT OUTER JOIN DEPENDENT

ON SSN=ESSN);

Facilidades Adicionais

Agrupamento

SELECT DNO, COUNT(*), AVG(SALARY)FROM EMPLOYEEGROUP BY DNO;

Facilidades Adicionais

Agrupamento com a cláusula HAVING

SELECT PNUMBER, PNAME, COUNT(*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAMEHAVING COUNT(*) > 2;

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 EMPLOYEEWHERE LNAME=‘Brown’;

DELETE FROM EMPLOYEEWHERE DNO IN (SELECT DNUMBER

FROM DEPARTMENTWHERE DNAME=‘Research’);

DELETE FROM EMPLOYEE;

Atualizações em SQL

Comando UPDATE

UPDATE PROJECTSET PLOCATION=‘Bellaire’, DNUM=5WHERE PNUMBER=10;

UPDATE EMPLOYEESET SALARY=SALARY*1.1WHERE DNO IN (SELECT DNUMBER

FROM DEPARTMENTWHERE 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

Um departamento controla um número de projetos,cada qual com um nome e número únicos e uma únicalocalizaçã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 datade nascimento. Um empregado e’ assinalado a umdepartamento mas pode trabalhar em diversosprojetos, os quais não são necessariamentecontrolados pelo mesmo departamento. Nosregistramos o número de horas por semana que oempregado trabalha em cada projeto e osupervisor direto de cada empregado

Nós mantemos registro para cada empregado, donumero de dependentes (para seguro) e paracada dependente o primeiro nome, sexo, data denascimento 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]

Representação Relacional de Esquemas ER

Estratégias de representação

Mapeamento 1-1: cada tipo de entidade ou derelacionamento é representado por um esquema derelação separado

Mapeamento otimizado: tipos de relacionamentofuncionais (1:1 e N:1) e subtipos de entidade sãocolapsados e representados através de atributos em

outro esquema de relação

Modelo RelacionalNotaçã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 atributosde R1 que referencia a chave Y de R2

Restrições estruturais

<expr1> op <expr2>, onde <expr1> e <expr2> sãoexpressões da álgebra relacional e op é um dosoperadores , , ou

Exemplo de um Diagrama ER

Empregado

ProjetoDependente

Departamento

Trabalha-para

Gerencia

Participa-de

Controla

N 1

1 11 1

N NN

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 Departamento1N

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 Departamento1N

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 Departamento11

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 ProjetoNM

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]

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>

<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) referencesDepartamento)

Restrições de Integridade em SQL

Restrições de unicidade (unique constraints) queindicam a chave primária e as chaves alternativasde uma tabela

Restrições de integridade referencial (referentialconstraints) que especificam as chaves estrangei-ras de uma tabela

Restrições de verificação (check constraints) queespecificam condições que devem ser satisfeitaspor 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 ofSQL 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 MaintainingOptimizing Relational Representations of Entity-Relationship Schemas. InThalheim, 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 Representationof Specialization Structures. Information Systems 25, 6(2000), 399-415.

Recommended