aula4 estrutura e qualidade dos dados 11

Estrutura e Qualidade dos Dados Prof° Hugo Donadon

Introdução

O que é um banco de dados?

Definições Preliminares Você já se perguntou por que recebe uma mensagem de erro ao tentar criar uma conta no Twitter com o nome de perfil que deseja, e ele já está em uso? Esse tipo de situação acontece porque existe um banco de dados por trás, armazenando milhões de informações de usuários. Os bancos de dados são responsáveis por organizar, armazenar, atualizar e permitir acesso rápido a essas informações de forma eficiente.

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

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

Relação entre Modelo de Dados, Esquema e 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) 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

Exemplo de um BD de Rede

Exemplo de um BD Hierárquico

Modelo Entidade-Relacionamento

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.

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ê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) Ex. Nome da companhia, identidade do empregado Chave pode ser formada por vários atributos: chave composta Registro do Veiculo: Numero de Registro e Estado Domínio de um atributo: Conjunto de valores que podem ser atribuídos a um atributo para cada entidade individualmente Ex. Idade do Empregado: (16,70); Nome do Empregado:String

Figura 3.5

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

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

Modelo ER - Conceitos Restrições sobre tipos de relacionamento: Limitam as possiveis combinações de entidades que podem participar no conjunto de relacionamentos Cardinalidade: Especifica o número de instâncias de um tipo de relacionamento do qual uma entidade pode participar Participação: Especifica se a existência de uma entidade depende de seu relacionamento com outra entidade através de um tipo de relacionamento  parcial ou total Ex. Todo empregado deve trabalhar para um departamento (total) Ex. Nem todo empregado gerencia um departamento (parcial) Cardinalidade + Participação  Restrições Estruturais

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

Esquema conceitual

O que é o Modelo Entidade-Relacionamento (ER)? Modelo conceitual que representa o mundo real. Utilizado para planejar a estrutura de um banco de dados. Base para gerar o modelo relacional. Permite visualizar entidades, atributos e relacionamentos.

Entidade: Exemplo com Aluno Entidade representa um objeto real (ex: Aluno). Atributos descrevem características do objeto. Atributo-chave (PK) identifica de forma única cada instância. Exemplo de atributos: - RA (PK) - Nome - Data de Nascimento

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

Relacionamentos Ligação entre duas ou mais entidades. Tipos comuns: - 1:1 (Ex: Departamento — Gerente) - 1:N (Ex: Departamento — Empregados) - N:N (Ex: Aluno — Disciplina) Podem ter atributos (ex: horas trabalhadas).

Esquema conceitual

Relacionamento Recursivo Entidade se relaciona com ela mesma. Exemplo: Funcionário supervisiona outro funcionário. Define papéis distintos dentro da mesma entidade. Supervisor → Supervisionado

Notação ER (Resumo)

Entidade Fraca Não possui chave primária própria. Identificada por meio de uma entidade forte relacionada. Utiliza chave parcial + chave do identificador. Exemplo: Dependente (de um Funcionário).

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 Conceitos Básicos

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, 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 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 Restrições de Integridade

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: 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]

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: 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

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 EM QUALQUER RESTRICAO Comando ALTER TABLE ALTER TABLE COMPANY.EMPLOYEE ADD JOB VARCHAR(12); Inicialmente Null para todas as tuplas ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE (RESTRICT); RESTRICT: SE NENHUMA VISAO OU RESTRICAO 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, ADDRESS FROM EMPLOYEE WHERE FNAME=‘John’ AND MINIT=‘B’ AND LNAME=‘Smith’;

Consultas Básicas em SQL SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DNAME=‘Research’ AND DNO=DNUMBER; SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE PLOCATION=‘Stafford’ AND DNUM=DNUMBER AND MGRSSN=SSN;

Consultas Básicas em SQL Atributos ambíguos e pseudônimos (alias) SELECT DNAME, DLOCATION FROM DEPARTMENT, DEPT_LOCATIONS WHERE DEPARTMENT.DNUMBER = DEPT_LOCATIONS.DNUMBER; SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E, EMPLOYEE AS S 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

Consultas Básicas em SQL Manipulando tabelas como conjuntos SELECT SALARY FROM EMPLOYEE;

Consultas Complexas em SQL Consultas aninhadas SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE 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;

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

Abordagem ER para Projeto Lógico de Bancos de Dados Relacionais

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.

n

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,A2,…,An), onde A1 é a chave primária de R Restrição de integridade referencial R1 [X]  R2 [Y], onde X é um conjunto de atributos de R1 que referencia a chave Y de R2 Restrições estruturais <expr1> op <expr2>, onde <expr1> e <expr2> são expressões da álgebra relacional e op é um dos operadores , ,  ou 

Exemplo de um Diagrama ER

Representação de Tipos de Entidade (sem atributos multivalorados)

Representação de Tipos de Entidade (com atributos multivalorados)

Representação de Tipos de Entidade Fraca

Representação de Tipos de Relacionamento N:1 (mapeamento 1-1)

Representação de Tipos de Relacionamento N:1 (mapeamento otimizado)

Representação de Tipos de Relacionamento 1:1 (mapeamento otimizado)

Representação de Tipos de Relacionamento M:N

Implementação usando SQL SQL Composta de três sublinguagens: LDD, LMD e LCD Objeto de padronização pelo ANSI/ISO Comando básico de definição de dados: create table <table name> (<column definitions> <primary key definition> <alternate key definitions> <foreign key definitions>)

Definição de um Esquema de Relação em SQL create table Empregado (NEmp char(3) not null, NomeEmp char(30) not null, Salario decimal(6,2), NDept char(2) not null, primary key (NEmp), foreign key (NDept) references Departamento)

Restrições de Integridade em SQL Restrições de unicidade (unique constraints) que indicam a chave primária e as chaves alternativas de uma tabela Restrições de integridade referencial (referential constraints) que especificam as chaves estrangei-ras de uma tabela Restrições de verificação (check constraints) que especificam condições que devem ser satisfeitas por coluna/linhas de uma tabela ou entre tabelas

Restrições de Unicidade Chave primária primary key (<attribute list>) Chaves alternativas unique (<attribute list>) create table Departamento ( ... primary key (NDept), unique (NomeDept), ...)

Restrições de Integridade Referencial foreign key (<attribute list>) references <table name> [(<attribute list>)] [on delete cascade | set null | set default] [on update cascade | set null | set default] create table Participa-de (... foreign key NEmp references Empregado on delete cascade)

Referências Batini, C.; Ceri, S.; Navathe, S.B. Conceptual Database Design: An Entity-Relationship Approach. Benjamin/Cummings, Redwood City, CA, 1992. Elmasri, R.; Navathe, S.B. Fundamentals of Database Systems, 3rd ed., Addison-Wesley, MA, 2000. Laender, A.H.F.; Casanova, M.A.; Carvalho, A.P.; Ridolfi, L.F. An Analysis of SQL Integrity Constraints from an Entity-Relationship Model Perspective. Information Systems 4, 3(1994), 423-464. Silva, A.S.; Laender, A.H.F.; Casanova, M.A. An Approach to Maintaining Optimizing Relational Representations of Entity-Relationship Schemas. In Thalheim, B. (ed.). Conceptual Modeling -ER’96. Springer-Verlag, Berlin, 1996, pp. 242-256. Silva, A.S.; Laender, A.H.F.; Casanova, M.A. On the Relational Representation of Specialization Structures. Information Systems 25, 6(2000), 399-415.

Complemento: Aula 4 – BD 1 Modelo Entidade Relacionamento – Parte 1 Aula 4 – BD 1 Modelo Entidade Relacionamento – Parte 1 Porf° Hugo Donadon

Complemento: Modelo Entidade-Relacionamento Modelo Entidade-Relacionamento O modelo ER Descrever os dados de aplicações do mundo real em termos de objetos (entidades) e seus relacionamentos É largamente utilizado para o desenvolvimento da fase inicial do projeto de BD Fornece conceitos para partir de uma descrição informal dos usuários obter uma descrição mais detalhada

Complemento: Modelo Entidade-Relacionamento Modelo Entidade-Relacionamento O modelo ER É a técnica de modelagem mais difundida e utilizada Foi criada em 1976 por Peter Chen É um modelo de dados conceitual de alto nível Compreensível a usuários  Diagrama Entidade-Relacionamento(DER)

Complemento: Atributos Atributos Usados para descrever um conjunto de entidades ou de relacionamentos Ex: o conjunto de entidades Empregado pode ter os seguintes atributos Nome Matrícula Sexo Idade Endereço Obs.: todas as entidades em um dado conjunto de entidades têm os mesmos atributos

Complemento: Atributos Atributos Cada atributo tem um domínio de possíveis valores Ex: domínio do atributo nome de um Empregado  conjunto de 20 caracteres Pode assumir o valor nulo Chave É um conjunto mínimo de atributos cujos valores identificam unicamente uma entidade em um conjunto de entidades Pode haver mais que uma chave candidata Escolhe-se uma delas para ser a chave primária

Complemento: Atributos Atributos Tipo de Entidades Empregado - Atributos: cpf, nome, endereço entidade e1: cpf: 11.111.111-11 nome: João da Silva endereço: Rua xx, 200

Complemento: Atributos – Classificação Segundo Elmasri & Navathe Atributos – Classificação Segundo Elmasri & Navathe Simples versus Compostos atributo simples ou atômico: não pode ser decomposto (dividido) em atributos mais básicos exemplo: sexo {M, F} atributo composto pode ser decomposto em atributos mais básicos exemplo: atributo endereço: nome_rua, nro_casa, complemento, nome_bairro, ... Baseado nos slides do Prof. Eduardo – ICMC-USP

Complemento: Atributos – Classificação Segundo Elmasri & Navathe Atributos – Classificação Segundo Elmasri & Navathe Monovalorados versus Multivalorados atributo monovalorado possui um único valor para cada entidade exemplo: idade atributo multivalorado possui múltiplos valores para cada entidade exemplo: atributo telefone pode possuir limites inferior/superior com relação à multiplicidade dos valores assumidos exemplo: nro_min = 0, nro_max = 3 Baseado nos slides do Prof. Eduardo – ICMC-USP

Complemento: Atributos – Classificação Segundo Elmasri & Navathe Atributos – Classificação Segundo Elmasri & Navathe Armazenado versus Derivado atributo armazenado está realmente armazenado no BD atributo derivado pode ser determinado através de outros atributos ou através de entidades relacionadas exemplos: idade = data_atual - data_nascimento nro_empregados = “soma de entidades” pode ou não ser armazenado no BD Baseado nos slides do Prof. Eduardo – ICMC-USP

Complemento: Atributos – Classificação Segundo Elmasri & Navathe Atributos – Classificação Segundo Elmasri & Navathe Valores NULL Usado quando uma entidade não tem um valor aplicável para um atributo Usado quando o valor de um atributo para uma entidade não é conhecido Exemplo Atributo Telefone com o valor NULL para a entidade Pessoa indica que não se sabe o telefone da Pessoa

Complemento: Relacionamento Relacionamento É uma associação entre duas ou mais entidades Ex: João trabalha no departamento farmacêutico Reuni-se um conjunto de relacionamentos similares em um conjunto de relacionamentos (Tipo-Relacionamento) Ex: trabalha-em  Empregado trabalha em um Departamento

Complemento: Relacionamento Relacionamento Empregado Departamento Trabalha-em 2/9/92 3/3/94 Formalmente Um conjunto de relacionamentos é um conjunto de n-tuplas: {(e1,...,en)|e1  E1,...,en  Em} Cada n-tupla denota um relacionamento envolvendo n entidades e1,...,em onde cada entidade está em um conjunto de entidades Ei 13/4/98 24/11/99 Imagem retirada dos slides da Profa. Sandra - UFU

Complemento: Relacionamento Relacionamento Empregado Departamento cpf nome End Tel nome Or Id-depto Trabalha-em Pode ter um conjunto de atributos descritivos Armazenam informações sobre o relacionamento Ex: João trabalha no departamento farmacêutico desde janeiro de 2000. DataIn Imagem retirada dos slides da Profa. Sandra - UFU

Complemento: Relacionamento Relacionamento Deve ser unicamente identificado pelas entidades participantes – Ex: cada relacionamento trabalha_em deve ser identificado pela matrícula do funcionário e código do departamento Uma instância de um conjunto de relacionamentos  ‘Retrato’ do conjunto de relacionamentos em dado momento

Complemento: Relacionamento Relacionamento Empregado Departamento cpf nome End Tel nome Or Id-depto Trabalha-em Pode envolver 2 ou mais entidades – Ex: associação entre empregado, departamento e localização (ternário) DataIn LOCAL End Capacidade Imagem retirada dos slides da Profa. Sandra - UFU

Complemento: Relacionamento Relacionamento Pode envolver duas entidades do mesmo conjunto de entidades  especificar o papel de cada uma cpf nome End Tel Empregado Reporta_a subordinado supervisor

Complemento: Grau de um conjunto de relacionamentos Grau de um conjunto de relacionamentos Unário é um relacionamento que envolve um único conjunto de entidades Indicado utilizar nome de papel

Complemento: Grau de um conjunto de relacionamentos Grau de um conjunto de relacionamentos Binário É um relacionamento que envolve dois conjuntos de entidades Ternário É um relacionamento que envolve três conjuntos de entidades

Exercício 1 – Identificação de Entidades e Atributos Contexto: Uma empresa quer informatizar o controle de seus funcionários, departamentos e projetos. Pergunta: Com base no cenário acima, identifique: - 3 possíveis entidades - 2 atributos para cada uma

Exercício 2 – Classifique os Elementos Classifique como Entidade (E), Atributo (A) ou Relacionamento (R): Cliente, Nome do Cliente, Compra, Produto, Preço, Quantidade

Exercício 3 – Tipos de Relacionamentos Classifique como 1:1, 1:N, N:N ou Ternário: - Cada funcionário tem um crachá exclusivo - Um professor leciona várias disciplinas - Um aluno se matricula em várias disciplinas - Um funcionário executa uma função em um projeto

Desafio Final – Modele Você Mesmo Contexto: Uma biblioteca precisa controlar livros, usuários e empréstimos. - Liste as entidades envolvidas - Aponte 2 atributos para cada - Descreva pelo menos 1 relacionamento

Complemento: Aula 4 – BD 1 Modelo Entidade Relacionamento – Parte 1 Aula 4 – BD 1 Modelo Entidade Relacionamento – Parte 1 Prof° Hugo Donadon

Complemento: Introdução ao Projeto de Banco de Dados Introdução ao Projeto de Banco de Dados Você já se perguntou por que recebe uma mensagem de erro ao tentar criar uma conta no Twitter com o nome de perfil que deseja, e ele já está em uso? Esse tipo de situação acontece porque existe um banco de dados por trás, armazenando milhões de informações de usuários. Os bancos de dados são responsáveis por organizar, armazenar, atualizar e permitir acesso rápido a essas informações de forma eficiente.

Complemento: Modelo Entidade-Relacionamento Modelo Entidade-Relacionamento O modelo ER Descrever os dados de aplicações do mundo real em termos de objetos (entidades) e seus relacionamentos É largamente utilizado para o desenvolvimento da fase inicial do projeto de BD Fornece conceitos para partir de uma descrição informal dos usuários obter uma descrição mais detalhada

Complemento: Modelo Entidade-Relacionamento Modelo Entidade-Relacionamento O modelo ER É a técnica de modelagem mais difundida e utilizada Foi criada em 1976 por Peter Chen É um modelo de dados conceitual de alto nível Compreensível a usuários  Diagrama Entidade-Relacionamento(DER)

Complemento: Entidade Entidade É um objeto do mundo real distinguível de outros objetos Ex: cada pessoa em uma empresa é uma entidade Ex: o gerente do departamento de vendas Pode ser um objeto com existência física ou conceitual Descrito por propriedades  atributos

Complemento: Entidade Entidade Coleção de entidades semelhantes  Conjunto de entidades (Tipo-Entidade) Ex: os empregados de uma empresa são as entidades de um conjunto de entidades denominado Empregado Entidades de um mesmo conjunto de entidades Compartilham atributos É normal referenciar todas as entidades pelo mesmo nome do tipo_entidade. Ex: Empregado Baseado nos slides do Prof. Eduardo – ICMC-USP