Sistemas de Banco de Dados Marcos Andr Gonalves

Sistemas de Banco de Dados Marcos André Gonçalves 2007 -2

Introdução

O que é um banco de dados?

Definições Preliminares n [Chu, 1985] n n Um banco de dados é um conjunto de arquivos relacionados entre si [Date, 2000] n 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 n [Elmasri & Navathe, 2000] n Um banco de dados é uma coleção de dados relacionados n n n 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 n n 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 n n Mini-mundo: parte de uma universidade Algumas entidades: n n Alunos Disciplinas Departamentos Alguns relacionamentos: n n 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 n n 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 n n Administradores de banco de dados Projetistas de banco de dados Analistas de sistema e programadores Usuários finais: n n n Usuários casuais Usuários leigos Usuários especializados

Vantagens da Utilização de um SGBD n Controle de redundância dos dados n Controle de acesso (segurança) n Armazenamento persistente dos dados n Existência de múltiplas interfaces para os usuários n Representação de relacionamentos complexos entre os dados n Manutenção de restrições de integridade n Recuperação de falhas

Implicações da Abordagem de BD n n Adoção/imposição de padrões Redução do tempo de desenvolvimento das aplicações n Flexibilidade n Atualidade da informação disponível n Economia de escala

Quando não Utilizar um SGBD n n 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: n n n 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 n Modelo de dados: Conjunto de conceitos usados para descrever a estrutura de um banco de dados n n 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 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 n Estado do Banco n n n Dados do banco em qualquer ponto do tempo Inicialmente vazio Muda freqüentemente Validade parcialmente guarantida pelo SGBD Esquema do Banco n n Armazenado no catálogo Mudanças muito menos freqüentes

Tipos de Modelo de Dados n Modelos conceituais n n n 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: n n n Modelo entidade-relacionamento (ER) Modelo funcional Modelo orientado a objetos (OO)

Tipos de Modelo de Dados n Modelos representacionais (lógicos) n n 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: n n n Modelo relacional Modelo de rede (CODASYL) Modelo hierárquico

Tipos de Modelo de Dados n Modelos físicos n Utilizados para descrever como os dados são fisicamente armazenados

Linguagens n Linguagem de definição de dados (LDD) n n Linguagem de manipulação de dados (LMD) n n Recuperação, inserção, remoção, modificação do BD Linguagem de consulta n n Usada para definir esquemas LMD de alto nivel usada em modo “standalone” Exemplo: SQL

Utilitários n n Carregamento Backup n n n E. g. dumps do banco de dados (Re-)Organização de arquivos Monitoramento da performance

Classificação dos SGBDs n Quanto ao modelo de dados adotado: n n n Quanto ao número de usuários suportados: n n n Relacionais De rede Hierárquicos Orientados a objetos Objeto-relacionais Mono-usuários Multi-usuários Quanto à localização dos dados: n n Centralizados Distribuídos

Exemplo de um BD Relacional Empregado Num. Emp 032 Nome. Emp 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 Num. Dept Nome. Dept 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 905 130 J Neves 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 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 n Banco de Dados de uma companhia n n 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 n Banco de Dados de uma companhia n n 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 n Entidades: n n Objetos do mundo real que são de interesse para alguma aplicação Atributos: n Propriedades utilizadas para descrever uma entidade e 1 (Employee) Name = John Address = 2311 Kirby, Houston, TX Age = 55 Home Phone = 713 -749 -2630

Modelo ER - Conceitos n Tipos (classes) de atributo: n Simples ou compostos n n Monovalorados ou multivalorados n n Ex. Profissão Armazenados ou derivados n n 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 Numero. De. Empregados Valores Null n Não aplicável n n Ex. Número do apartamento Desconhecido n Ex. Telefone de casa

Modelo ER - Conceitos n Tipo de entidade: n n n 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: n Employee, Company

Modelo ER - Conceitos n Chave de um tipo de entidade: n Atributo que possui valor único para cada entidade (instância) n n Chave pode ser formada por vários atributos: chave composta n n Ex. Nome da companhia, identidade do empregado Registro do Veiculo: Numero de Registro e Estado Domínio de um atributo: n n 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 n Relacionamentos: n n Associações entre duas ou mais entidades distintas (instâncias) com um significado Exemplo: n n n Employee John Smith Works-for Department Research Employee Fred Brown Manages Department Research Departament Research Controls Project X

Modelo ER - Conceitos n Tipo de Relacionamento: n n Define um conjunto de associações entre n tipos de entidade E 1, E 2, . . . , En Exemplo: n Works-for entre Employee e Department Employee Works-for Department

Modelo ER - Conceitos n Tipo de Relacionamento: n n Matematicamente, um tipo de relacionamento R é um conjunto de (instâncias de) relacionamentos ri, onde cada ri associa n (instâncias de) entidades (e 1, . . . , en) e cada ej pertence a um tipo de entidade Ej R Í E 1 x E 2 x. . . x En ri = (e 1, . . . , en) Grau de um Tipo de Relacionamento n 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 n Restrições sobre tipos de relacionamento: n n n 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 n n n 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 n Papéis e relacionamentos recursivos n n 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 n Tipos de Entidade Fraca n n n 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 n n n 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 n n n 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 n Esquema de relação n Descreve a relação n R(A 1, A 2, . . . , An), onde: n n n 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: n Student(Name, SSN, Office. Phine, Age, GPA) Home. Phone, Address,

Conceitos Básicos n Relação r(R) n n Conjunto de tuplas: r = {t 1, t 2, . . . , tm} Cada tupla é uma lista ordenada de valores: t = <v 1, v 2, . . . , vn>

Características de uma Relação n As tuplas ordenadas Benjamin Bayer n 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 n n Uma tupla é uma lista ordenada de valores O valor de cada atributo em uma tupla é atômico n n 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 n Restrições de domínio n n 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 n n 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 t 1 e t 2 de r(R), t 1[SK] ¹ t 2[SK] é uma super-chave de R n n 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 n {SSN, Name, Age} = super-chave; {SSN} = chave

Restrições de Integridade n Restrições de chave n n Um esquema de relação pode ter mais de uma 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 n Restrições em valores null n n 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 n Além das restrições de domínio e de chave as seguintes restrições de integridade são parte do modelo relacional: n Restrição de integridade de entidade n Nenhum componente de uma chave primária pode ser nulo

Restrições de Integridade n Restrição de integridade referencial n n 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 R 1 definido sobre o mesmo domínio dos atributos da chave primária PK de outro esquema R 2. Então, para qualquer tupla t 1 de R 1: n n t 1[FK] = t 2[PK], onde t 2 é uma tupla de R 2 ou t 1[FK] é nulo

Restrições de integridade referencial

Restrições de Integridade n A restrição de integridade referencial pode ser expressa pela notação R 1[FK] R 2[PK], n onde PK é a chave primária de R 2 e FK é a chave estrangeira de R 1 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 n n A cada RIR R 1[FK] R 2[PK] é possível associar uma opção de remoção que especifica como a remoção de uma tupla de R 2 é executada em relação a R 1 As opções de remoção possíveis são: n n bloqueio propagação substituição por nulos Notação: op R 1[FK] R 2[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 Restrições de integridade referencial com opções de remoção

Operações sobre Relações n As operações sobre um BD relacional podem ser classificadas em: n n n Operações de recuperação (consulta) Operações de atualização (sobre tuplas): n n n Inserção (insert) Remoção (delete) Modificação (modify)

Operações sobre Relações n Operações de atualização n Restrições de integridade não podem ser violadas n Inserção n n n 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 n Operações de atualização n Restrições de integridade não podem ser violadas n Remoção n n 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 n Operações de atualização n Restrições de integridade não podem ser violadas n Modificação n n n 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 n n 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: n n n SQL 1 (SQL-86) SQL 2 (SQL-92) SQL 3 (SQL: 1999)

Introdução n n SQL = LDD + LMD + LCD Principais comandos: n LDD: n n LMD: n n SELECT, INSERT, UPDATE, DELETE LCD: n n CREATE SCHEMA / TABLE / VIEW DROP SCHEMA / TABLE / VIEW ALTER TABLE GRANT, REVOKE Conceitos: n n n Table = Relação Row = tupla Column = atributo

Definição de Dados em SQL n Comando CREATE SCHEMA n n CREATE SCHEMA COMPANY AUTHORIZATION JS; Comando CREATE TABLE n 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 n Exemplo de um comando 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 n Opções de remoção (cláusula ON DELETE): n CASCADE (propagação) SET NULL (substituição por nulos) SET DEFAULT (substituição por um valor default) n Opção default: bloqueio (RESTRICT) n n n 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) ON DELETE CASCADE FOREIGN KEY (PNO) REFERENCES PROJECT(PNUMBER)

Restrição de Integridade Referencial em SQL

n Definição de Dados em SQL Comandos DROP SCHEMA e DROP TABLE n DROP SCHEMA COMPANY CASCADE (RESTRICT); n n DROP TABLE DEPENDENT CASCADE (RESTRICT); n n RESTRICT: APENAS SE NAO TEM ELEMENTOS RESTRICT: SE A TABELA NAO E’ REFERENCIADA EM QUALQUER RESTRICAO Comando ALTER TABLE n ALTER TABLE COMPANY. EMPLOYEE ADD JOB VARCHAR(12); n n Inicialmente Null para todas as tuplas ALTER TABLE COMPANY. EMPLOYEE DROP ADDRESS CASCADE (RESTRICT); n RESTRICT: SE NENHUMA VISAO OU RESTRICAO REFERENCIA A COLUNA

Consultas Básicas em SQL n Formato básico do comando SELECT: SELECT <lista de atributos> FROM <lista de tabelas> WHERE <condição>; n Exemplo: SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;

Consultas Básicas em SQL n SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME=‘Research’ AND DNO=DNUMBER; condição de seleção n 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 n 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 n 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 n 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 n 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 n 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 n 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 n 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 n 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 n 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 n Agrupamento SELECT DNO, COUNT(*), AVG(SALARY) FROM EMPLOYEE GROUP BY DNO;

Facilidades Adicionais n 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

Atualizações em SQL n Comando 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 n Comando 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 n Comando 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 n n n 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 n Caracterização n n n Complexidade Multiplicidade de tarefas Fases n n n 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 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 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 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 n Banco de Dados de uma companhia n n 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 n Banco de Dados de uma companhia n n 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 n Estratégias de representação n n 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 n n Esquema de relação n R (A 1, A , …, An), onde A 1 é a chave primária de R 2 Restrição de integridade referencial n n R 1 [X] R 2 [Y], onde X é um conjunto de atributos de R 1 que referencia a chave Y de R 2 Restrições estruturais n <expr 1> op <expr 2>, onde <expr 1> e <expr 2> são expressões da álgebra relacional e op é um dos operadores , , ou

Exemplo de um Diagrama ER NEmp Nome. Empregado 1 Salário Trabalha-para N 1 Possui NDept Gerencia Nome. Dep Data. Nasc Ramal 1 Departamento 1 1 M Controla N Dependente Nome. Dept Participa-de N Hs. Trab NProj N Projeto Nome. Proj Local

Representação de Tipos de Entidade (sem atributos multivalorados) NEmp Empregado Nome. Emp Salário Empregado (NEmp(nn), Nome. Emp, Salário)

Representação de Tipos de Entidade (com atributos multivalorados) NDept Departamento Nome. Dept Ramal Departamento (NDept(nn), Nome. Dept) Ramal-Departamento (NDept(nn), Ramal(nn)) p Ramal-Departamento [NDept]

Representação de Tipos de Entidade Fraca Empregado 1 Possui NEmp N Dependente Nome. Dep Data. Nasc Empregado (NEmp(nn), . . . ) Dependente (NEmp(nn), Nome. Dep(nn), Data. Nasc) p Dependente [NEmp] Empregado [NEmp]

Representação de Tipos de Relacionamento N: 1 (mapeamento 1 -1) Empregado NEmp N Trabalha-para 1 Departamento 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 N Hs. Trab Projeto NProj Empregado (NEmp(nn), . . . ) Projeto (NProj(nn), . . . ) Participa-de (NEmp(nn), NProj(nn), Hs. Trab) p Participa-de [NEmp] Empregado [NEmp] p Participa-de [NProj] Projeto [NProj]

Implementação usando SQL n n n 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, Nome. Emp 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 n n n 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 n n Chave primária primary key (<attribute list>) Chaves alternativas unique (<attribute list>) create table Departamento (. . . primary key (NDept), unique (Nome. Dept), . . . )

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 n n n 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, 3 rd 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.