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.