Modelo Relacional Pedro Nogueira Ramos Pedro Ramosiscte pt
- Slides: 60
Modelo Relacional Pedro Nogueira Ramos (Pedro. Ramos@iscte. pt) DCTI / ISCTE Pedro Ramos, DCTI/ISCTE Índice
Relacional - Índice Relação Especificação de Atributos Produto Cartesiano Optimizações Definição de Relação Índices Chave Primária Arquivo Critérios para adopção de Chave Primária Parameterização Chave Estrangeira Transacções Integridade da Chave Estrangeira Concorrência Joins Transposição a partir do UML Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional (Codd, 1970) No Modelo relacional a informação é representada através de relações (ou tabelas). As relações correspondem a conjuntos, ou seja, são manipuladas através dos habituais operadores que operam sobre conjuntos: Intersecção, Produto Cartesiano, União, etc. . Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Relação Uma Base de Dados relacional é um conjunto de relações com um número específico de atributos (colunas) e um número variável de tuplos (linhas ou instâncias). A cada atributo é atribuído um domínio (conjunto de valores válidos) e um nome único na relação Relação: Cliente Número Nome Morada 001 João NULL 013 Ana NULL 056 Luís NULL Um valor é armazenado na intersecção entre uma linha e um atributo e diz respeito ao domínio do atributo ou tem o valor NULL (ausência de valor). INT (inteiros) Domínios Pedro Ramos, DCTI/ISCTE STR (caracteres) Índice
Modelo Relacional Produto Cartesiano O Produto Cartesiano é obtido através de todas as combinações entre os elementos dos conjuntos. Exemplo 0 1 a c b Domínio D 1={0, 1} Domínio D 2 ={A, B, C} Representação Relacional DI 0 D 2 a Produto Cartesiano (D 1 X D 2) = 0 b 0 c {(0, A), (0, B), (0, C), (1, A), (1, B), (1, C)} 1 a 1 b 1 c Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Definição de Relação Uma relação é o subconjunto do Produto Cartesiano de uma lista de domínios. A tabela de clientes é um subconjunto do produto cartesiano entre os domínios INT e STR, nomeadamente INT X STR. Número Nome Morada 001 João Lisboa 013 Ana NULL Qualquer subconjunto de INT X STR é uma tabela (inclusive INT X STR ou INT X STR). Um conjunto não é ordenado. Isto é, caso seleccione elementos de um conjunto (e. g. , linhas de uma tabela) sem indicar uma forma de ordenação, os elementos são seleccionados através de uma ordem aleatória. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Chave Primária (I) Todas as tabelas têm de possuir uma chave primária. Chave Primária (ou chave): conjunto minimal de atributos que permitem identificar univocamente uma tuplo de uma relação. O conjunto {Número} é chave porque não podem existir dois clientes com o mesmo número. Número Nome Morada 001 João NULL 013 Ana NULL O conjunto {Nome} não é chave porque podem existir dois clientes com o mesmo nome. O conjunto {Número, Nome} não é chave porque não é minimal (contém uma chave), e designa-se por Super-Chave. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Chave Primária (II) Apenas o conjunto {Fila, Lugar} garante que identificamos apenas uma linha. Cliente Número Nome Morada BI 001 João NULL 1234567 013 Ana NULL 7654321 Sala de Cinema Fila Lugar Ocupado? A 1 sim A 2 não B 1 não Chaves Candidatas (ou Alternativas) Pedro Ramos, DCTI/ISCTE É obrigatório optar por uma única chave ! Índice
Modelo Relacional Critérios para adopção de uma Chave Primária (I) • Atributos familiares ao utilizador • Domínio Numérico (por razões de eficiência) • Apenas um atributo (por razões de eficiência) • Preenchimento Obrigatório Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Critérios para adopção de uma Chave Primária (II) Livro Título Editora Edição ID Database Systems Addison Wesley 5 OO 1 Database Systems Addison Wesley 6 002 UML, User Guide Addison Wesley NULL 003 Alternativa a {Título, Editora, Edição} Apesar de familiar, é pouco eficiente e obriga ao preenchimento de Edição Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Chave Estrangeira (I) As chaves estrangeiras ocorrem quando existem dependências entre domínios. Cliente Factura Número Nome Morada Número Data Cliente 001 João NULL 001 12 -12 -1999 001 013 Ana NULL 002 01 -03 -2000 013 056 Luís NULL 0003 02 -03 -2000 001 O domínio de Factura. Cliente não é INT, mas sim: o conjunto de valores da coluna Cliente. Número. Ou seja, uma factura não pode estar associada a um cliente (Número) que não conste na tabela Cliente. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Chave Estrangeira (II) Factura. Cliente é Chave Estrangeira na tabela Factura. Cliente Factura Número Nome Morada Número Data Cliente 001 João NULL 001 12 -12 -1999 001 013 Ana NULL 002 01 -03 -2000 013 056 Luís NULL 0003 02 -03 -2000 001 1 Dependência 0. . . * Cliente. Número não é Chave Estrangeira porque podem existir clientes sem facturas. A existência de um cliente não é condicionada à existência de facturas. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Chave Estrangeira (III) Cliente Localidade Número Nome Cod. Postal Localidade 001 João 1500 Lisboa 013 Ana 2100 Porto 056 Luís NULL 3999 Évora 0. . . * 0. . . 1 Cliente. Cod. Postal é Chave Estrangeira porque aos clientes não podem ser atribuídos códigos postais que não constem na tabela de localidades. Mas não é obrigatório atribuir um Código Postal a um cliente. Cod. Postal {Localidade. Cod. Postal} NULL Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Integridade das Chaves Estrangeiras (Operação Delete) Cliente Localidade Número Nome Cod. Postal Localidade 001 João 1500 Lisboa 013 Ana 2100 Porto 056 Luís NULL 3999 Évora Hipótese: um utilizador pretende apagar a linha cujo Cod. Postal é 2100 Dado que Cliente. Cod. Postal {Localidade. Cod. Postal} NULL, três alternativas se colocam ao gestor da base de dados: 1. Não permite apagar; (Restricted) 2. Permite apagar, mas apaga o cliente 013; (Cascade) 3. Permite apagar, mas substitui o Cod. Postal do cliente 013 por NULL. (Set Null) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Integridade das Chaves Estrangeiras (Operação Update) Cliente Localidade Número Nome Cod. Postal Localidade 001 João 1500 Lisboa 013 Ana 2100 Porto 056 Luís NULL 3999 Évora Hipótese: um utilizador pretende alterar o Cod. Postal 2100 para o valor 2200 Dado que Cliente. Cod. Postal {Localidade. Cod. Postal} NULL, três alternativas se colocam ao gestor da base de dados: 1. Não permite alterar; (Restricted) 2. Permite alterar, mas altera igualmente o código postal do cliente 013 (de 2100 passa também para 2200); (Cascade) 3. Permite alterar, mas substitui o Cod. Postal do cliente 013 por NULL. (Set Null) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Cruzamento de Informação No Modelo Relacional a informação é obtida através do cruzamento entre tabelas (produtos cartesianos) através das chaves estrangeiras. Trata-se de uma forma fácil e intuitiva de obter informação, mas pouco eficiente. Exemplo: Listar informação de clientes (incluindo localidades) Cliente Número Nome Cod. Postal Localidade 001 João 1500 Lisboa 013 Ana 2100 Porto 056 Luís NULL X Localidade Cod. Postal Localidade 1500 Lisboa 2100 Porto 3999 Évora Pedro Ramos, DCTI/ISCTE Para cada Cliente. Cod. Postal procura-se um Localidade. Cod. Postal idêntico e selecciona-se a localidade respectiva. Índice
Modelo Relacional Joins A informação obtida unicamente através do produtos cartesiano entre tabelas é inconsistente, daí a necessidade de efectuar Joins. Cliente Número Nome Cliente. Cod. Postal Localidade Número Nome Cod. Postal 001 João 1500 Lisboa 001 João 1500 2100 Porto 013 Ana 2100 001 João 1500 3999 Évora 056 Luís NULL 013 Ana 2100 1500 Lisboa 013 Ana 2100 Porto Localidade X= Cod. Postal Localidade 013 Ana 2100 3999 Évora 1500 Lisboa 056 Luís NULL 1500 Lisboa 2100 Porto 056 Luís NULL 2100 Porto 3999 Évora 056 Luís NULL 3999 Évora Únicas linhas coerentes: Chave Primária = Chave Estrangeira (Key Join) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Transposição Modelo de Classes / Relacional A transposição do modelo de classes para o modelo relacional tem como objectivo final a criação de uma base de dados coerente com a modelação da fase de análise. As regras asseguram que 1) não ocorre perca de informação, i. e. , é possível aceder a toda a informação; 2) não existe informação redundante. As regras apresentadas não devem ser interpretadas como “leis” rígidas de transposição, mas uma indicação susceptível de adaptação em função da análise do problema em questão. As regras usualmente geram modelos relacionais ineficientes. Na transposição existe perca de informação semântica relativa às relações entre as classes: a partir de um modelo relacional pode não ser possível obter o Diagrama de Classes a partir do qual ele foi gerado. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição Regra 1 Todas as tabelas deverão ter uma chave primária. No caso de não existirem na tabela atributos que satisfaçam esta condição deverse-á criar um identificador único usualmente designado por id. Regra 2 As tabelas resultam exclusivamente das classes do modelo, e das associações de “muitos para muitos”. Regra 3 Todos os atributos de uma classe (ou de uma associação) são atributos da tabela que implementa a classe (ou que implementa a associação). Docente Nome Morada Telefone Pedro Ramos, DCTI/ISCTE Docente (Nome, Morada, Telefone, ID) Índice
Modelo Relacional Regras de Transposição (“um/um” I) Regra 4 Transposição de Relações de Um para Um Neste tipo de relação uma das tabelas da relação deverá herdar a chave principal da outra tabela como um atributo não chave (chave estrangeira). A determinação da tabela que herdará a chave estrangeira fica ao critério do analista e da interpretação que faz da realidade, devendo optar pelo que fizer mais sentido. Factura Data Emissão Valor Número de Factura 1 0… 1 Recibo Factura (Data, Valor, NFact) Nº Cheque Nº Recibo (NCheque, NRecibo, NFact) Factura (Data, Valor, Nfact, NRecibo) Recibo (NCheque, NRecibo) Pedro Ramos, DCTI/ISCTE Alternativa (desvantagem: o registo de um novo recibo obriga a uma alteração na tabela de facturas) Índice
Modelo Relacional Regras de Transposição (“um/um” II) Factura 1 Data Emissão Valor Número de Factura 0… 1 Recibo Factura (Data, Valor, NFact) Nº Cheque Nº Recibo (NCheque, NRecibo, NFact) Factura Recibo NFact Data Valor NRecibo NCheque NFact 001 12 -12 -99 12000 05 Null 001 013 02 -01 -00 13000 07 Null 003 Chave Estrangeira Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (“um/n” I) Regra 5: Transposição de Relações de Um para Muitos Numa relação de “um para muitos” a tabela cujos registos são susceptíveis de serem endereçados diversas vezes (lado muitos) herda a chave da tabela cuja correspondência é unitária (lado um). Funcionário Num. Contribuinte Nome Morada Departamento 0…* 1 Designação Funcionário (NCont, Nome, Morada, Designação) Departamento (Designação) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (“um/n” II) Funcionário Num. Contribuinte Nome Morada Departamento 0…* 1 Funcionário (NCont, Nome, Morada, Designação) Designação Departamento (Designação) Funcionário Departamento NCont Nome Morada Departamento Designação 001 Ana NULL Produção 013 João NULL Produção Comercial Chave Estrangeira Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (“n/n” I) Regra 6: Transposição de Relações de Muitos para Muitos A relação dá origem a uma tabela representativa da associação onde a chave primária é composta pelos atributos chave das tabelas que implementam as classes associadas. Aluno Número Nome Morada Disciplina 0…* 0. . . * Designação frequenta Aluno (NAluno, Nome, Morada) Disciplina (Designação) Frequenta (NAluno, Designação) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (“n/n” II) Aluno (NAluno, Nome, Morada) Disciplina 0…* Número Nome Morada 0. . . * Disciplina (Designação) Designação Frequenta (NAluno, Designação) frequenta Aluno Frequenta Disciplina NAluno Nome Morada NAluno Disciplina Designação 001 Ana NULL 001 Marketing 013 João NULL 001 Comunicação Chave Estrangeira Pedro Ramos, DCTI/ISCTE Chave Estrangeira Índice
Modelo Relacional Regras de Transposição (Classes Associativas) Regra 7: Transposição de Classes Associativas (desnecessária. . . ) Para as Classes Associativas aplicam-se as regras correspondentes à associação. Os atributos da classe associativa são herdados pela(s) tabela(s) que herda(m) a(s) chave(s) estrangeira(s). Licenciatura Designação Disciplina 0…* 1…* Disciplinas da Licenciatura Designação Licenciatura (Designação) Disciplina (Designação) Dis. CLic (Designação. Lic, Designação. Disc, Tipo. Aval) Tipo Avaliação Funcionário Departamento (Designação) Num. Contribuinte Nome Morada Funcionário (Ncont, Nome, Morada, Designação, Dt. Admissão) Departamento 0…* 1 Designação Disciplinas da Licenciatura Data Admissão Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Generalizações I) Regra 8: Transposição de Generalizações Duas situações distintas podem ocorrer a) As classes filhas têm entidade própria independentemente da classe pai A chave das tabelas que implementam as classes filhas é obtida através dos atributos da própria tabela. Terá que ser criado um atributo chave para a tabela que implementa a classe pai, sendo que essa tabela deverá ter uma propriedade discriminante (um atributo) que indica a qual das filhas o registo diz respeito. Todos os atributos chave da tabela pai terão que constar nas tabelas filhas como atributos não chave. Pessoa Aluno Pessoa (BI, Nome, Morada, Tipo) Nome Morada BI Número Curso Aluno (Número, Curso, BI) Docente (Número, Categoria, BI) Docente Número Categoria Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Generalizações II) Pessoa Aluno Pessoa (BI, Nome, Morada, Tipo) Nome Morada BI Número Curso Aluno (Número, Curso, BI) Docente (Número, Categoria, BI) Docente Número Categoria Aluno Docente Pessoa Número Curso BI Número Categoria BI BI Nome Morada Tipo 001 História 123456 10 Assistente 321456 123456 João NULL A 321456 Ana NULL D Chave Estrangeira Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Generalizações III) b) As classes filhas só têm identidade enquanto associadas à classe pai Neste caso, a chave da tabela que implementa a classe pai é obtida através dos atributos da própria tabela. As tabelas correspondentes às classes filhas herdarão a mesma chave da tabela pai. Sócio Individual Nome Morada Telefone Número Sócio Idade Profissão Sócio (NSócio, Nome, Morada, Telefone, tipo. Sócio) Organização (NSócio, CAE) Individual (NSócio, Idade, Profissão) CAE Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Generalizações IV) Sócio Individual Nome Morada Telefone Número Sócio Idade Profissão Sócio (NSócio, Nome, Morada, Telefone, tipo. Sócio) Organização (NSócio, CAE) Individual (NSócio, Idade, Profissão) CAE Individual Organização Sócio NSócio Idade Profissão NSócio CAE NSócio Nome Morada Telefone 11 32 Docente 10 A. 99. 8872 10 João NULL 21345676 11 Ana NULL 22456543 Chave Estrangeira Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Agregação) A transposição para o relacional obedece as regras de transposição das associações com a mesma multiplicidade. Empresa (IDEmp, Designação, Morada) Departamento (IDDep, Designação, IDEmp) Roda Automóvel 1 Matrícula Marca Modelo 4 Tipo Pneu Tipo Jante Volante 1 Pedro Ramos, DCTI/ISCTE Departamento 1 Designação Morada 0 … * Designação Automovel (Matrícula, Marca, Modelo) Roda (IDRoda, Tipo. Pneu, Tipo. Jante, Matrícula) Volante (IDVol, Material, Matrícula) Material Índice
Modelo Relacional Regras de Transposição (Composição I) Regra 9: Transposição de Composições. A tabela que implementa a classe composição tem como atributos chave a chave da tabela correspondente à classe que representa a composição e um atributo (ou mais) pertencente à classe componente (caso não exista é necessário criá-lo). Factura Número Data Linha da factura 1 1…* Produto Quantidade Preço Unitário Factura (NFact, Data) Linha (NFact, NLinha, Produto, Quantidade, Preço. Unit) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Regras de Transposição (Composição II) Factura Linha da factura 1 Número Data 1…* Factura (NFact, Data) Produto Quantidade Preço Unitário Linha (NFact, NLinha, Produto, Quantidade, Preço. Unit) Linha NFact Data NFact Nlinha Produto Quantidade Preço. Unit 11 03 -03 -00 11 1 Prod. A 3 2000 12 03 -03 -00 11 2 Prod. B 4 4000 12 1 Prod. B 3 2000 Chave Estrangeira Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Especificação de Atributos Na especificação dos atributos, para além da sua designação e tipo de dados, é possível indicar outras propriedades: • Chave primária; • Chave Estrangeira • Allow NULLS – admite o valor null • Unique – não admite valores duplicados • Validações (CHECK) – regras simples com operadores lógicos (<, >, <>, or, and) e. g. , IN (lista de valores); • Valor por omissão; • Comentários. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Optimizações do Modelo Relacional As regras de transposição, apesar de assegurarem um modelo completo (sem perca de informação) e coerente, geram usualmente modelos ineficientes. Sempre que possível (desde que não haja perca de informação relevante) dever-se-à optimizar o modelo relacional obtido, nomeadamente, no que diz respeito a: a) Número de tabelas (um elevado número de tabelas – joins – pode comprometer a eficiência do modelo; b) Número de atributos que compõem a chave das tabelas (deve ser reduzido). Ao contrário de um diagrama de classes, o modelo relacional não pretende ser descritivo, mas sim eficaz e eficiente. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Exemplos de Optimizações (I) Ano 1 Mes 1 Dia Ano 1 … 12 Mes 1 … 30 Dia 0 …* Nome Morada Dias Serviço Hora Início Hora Fim Ano (Ano) Farmácia Mes (Ano, Mes) Dia (Ano, Mes, Dia) Farmácia (IDFarmacia, Nome, Morada) Dias-Servico (IDFarmacia, Ano, Mes, Dia, Hora_Inicio, Hora_Fim) As três classes para modelar a data podem-se justificar para realçar o facto de não haver periodicidade (semanal, mensal) nos dias de serviço. Utilidade das tabelas Ano, Mês e Dia ? Dias Serviço Farmácia (IDFarmacia, Nome, Morada) Dias-Servico (IDFarmacia, Data, Hora_Inicio, Hora_Fim) Data Hora Início Hora Fim Farmácia 0 …* 1 Nome Morada Apenas um atributo Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Exemplos de Optimizações (II) Ano Licenciatura Ano Sigla Designação 0…* Disciplina Docente Sigla Designação 0…* 0…* IDDocente Nome Disciplina do Curso 0…* Ano Licenciatura (Ano) Licenciatura (Sigla, Designação) Disciplina do Curso (Sigla Disciplina, Sigla Curso, Ano) Docente (IDDocente, Nome) Lecciona (IDDocente, Sigla Disciplina, Sigla Curso, Ano) Ano Licenciatura (Ano) Licenciatura (Sigla, Designação) Chave ineficiente Disciplina (Sigla, Designação) Disciplina do Curso (Sigla Disciplina, Sigla Curso, Ano, IDDis) Docente (IDDocente, Nome) Lecciona (IDDocente, IDDis) Pedro Ramos, DCTI/ISCTE A validação da chave original terá que ser feita por programação Índice
Modelo Relacional Exemplos de Optimizações (III) Pessoa Aluno Pessoa (BI, Nome, Morada, Tipo) Nome Morada BI Número Curso Aluno (Número, Curso, BI) Docente (Número, Categoria, BI) Docente Número Categoria Aluno (Número, Curso, BI, Nome, Morada) Docente (Número, Categoria, BI, Nome, Morada) Facilita listagens de alunos (ou docentes) mas penaliza mailings. Sócio Individual Nome Morada Telefone Número Sócio Idade Profissão Individual (NSócio, Idade, Profissão) Organização (NSócio, CAE) Organização CAE Pedro Ramos, DCTI/ISCTE Sócio (NSócio, Nome, Morada, Telefone, tipo. Sócio) Sócio (NSócio, Nome, Morada, Telefone, Idade, Profissão, CAE, Tipo. Sócio {Individual, CAE}) Índice
Modelo Relacional Exemplos de Optimizações (IV) Factura Número Data Linha da factura 1 1…* Produto Quantidade Preço Unitário Factura (Número, Data) Linha (Número, Item, Produto, Quantidade, PU) Pode obrigar a re-cálculos de subtotais e totais (e. g. , para estatísticas) Factura (Número, Data, Total) Linha (Número, Item, Produto, Quantidade, PU, Sub. Total) Para além dos perigos da redundância, penaliza o cálculo de cada factura (update na tabela de factura). Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Índices (I) Uma técnica habitual de optimização de interrogações (querys) a bases de dados consiste na utilização de índices. O objectivo é acelerar o acesso a uma tabela através de um (ou vários) campo(s). Hipótese: é frequente as publicações serem consultadas pelo assunto Ficheiro de Índice Assunto Índice Tabela de Publicação ISBM Título Data Assunto Direito 1 A 1998 História 2 D 1997 Informática 3 C 1994 Sociologia Informática 4 Z 2000 Informática Sociologia 5 F 1986 Direito Ordenado por assunto Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Índices (II) Ficheiro de Índice Assunto Índice Tabela de Publicação ISBM Título Data Assunto Direito 1 A 1998 História 2 D 1997 Informática 3 C 1994 Sociologia Informática 4 Z 2000 Informática Sociologia 5 F 1986 Direito Exemplos de interrogações que beneficiam do índice 1. Quais os títulos das publicações de História ? (mesmo que a maioria das publicações sejam de história, é mais rápido percorrer sequencialmente um ficheiro mais pequeno); 2. Quantas publicações de informática existem (apenas necessita de abrir o ficheiro de índices); 3. (campo data indexado) Quais os títulos entre 1990 e 1998 ? (a ordenação do índice acelera muito a procura por intervalos). Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Índices (III) É usual criar-se um índice para a chave primária dado que a forma privilegiada de acesso às tabelas é através da chave, e. g. , joins). Pela mesma razão também se opta por vezes por criar índices para as chaves estrangeiras. Pode ser criado um índice para dois ou mais atributos. Ficheiro de Índice Assunto/Data Índice Tabela de Publicação ISBM Título Data Assunto Direito+1986 1 A 1998 História Informática+1997 2 D 1997 Informática 3 C 1994 Sociologia 4 Z 2000 Informática 5 F 1986 Direito Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Índices (IV) Apesar de acelerarem a consulta de informação, os índices penalizam a introdução e alteração de informação. A inserção de um registo obriga à introdução de um registo (ordenado) na tabela de índices. A alteração de um valor num atributo indexado obriga ao reordenamento do ficheiro de índices. A gestão de índices é fundamental mas perigosa (uma má gestão – excesso – pode degradar muito o desempenho da base de dados) e nunca é definitiva. Depende do número de registos existentes e no tipo de acessos (consultas) mais frequentes. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Índices (V) Exemplos de acessos Tabela: Publicacao (ISBN (Int), Assunto (Str), Ano (Int), Titulo (Str)) 100. 000 registos Inserção: ISBN sequencial, restantes 3 aleatórios Consulta: Número de publicações com um determinado assunto Valores em segundos Índice Inserção Consulta Nenhum 398 2 ISBN 402 2 ISBN, Assunto 547 (+37%) 0 Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Arquivo O arquivo (histórico / backup) de informação por vezes origina problemas de consistência. Factura Linha da factura 1 Número Data 1…* IDProduto Quantidade Produto 1 0…* IDProduto Designação Preço Unitário Hipótese: o preço de um produto foi alterado e é necessário imprimir segunda via de uma factura antiga. Três alternativas de backup: a) Cópia integral da base de dados – é sempre coerente; b) Cópia de algumas tabelas – pode gerar incoerência. c) Tabelas próprias para backup (alternativa a b)) Linha (Num_Fact, Linha, IDProduto, Designação, PU, Quantidade) Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Parameterização Todas as constantes de uma aplicação devem estar em uma(s) tabela, e nunca no código. Normalmente essa tabela apenas contém uma linha e uma coluna para cada parâmetro da aplicação. Exemplos de atributos: Taxa IVA; Designação e Morada da Empresa (para impressões); Dia do mês em que são automaticamente processados os salários; Prazo de devolução de uma publicação (biblioteca); etc. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Transacções (I) Exemplo: Transferências entre contas bancárias CONTA A CONTA B Débito 100 Crédito 100 BD Inconsistente Falha no sistema Necessidade de executar as duas operações como um todo. Transacção: conjunto delimitado e pré-definido de operações que exibe as seguintes características: • • Atomicidade - grupo indivisível (todas ou nenhuma); Integridade - passar de um estado de integridade da BD para outro estado de integridade; Isolamento - uma transacção deve ser executada como se fosse única. Ou seja, num ambiente concorrente não pode haver interferências entre as transacções, o resultado final é equivalente a uma execução em série (não concorrente). Persistência – após uma transacção terminar com sucesso (commit), as suas actualizações sobre a BD passam a ser efectivas. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Transacções (II) Flat Transactions Start Transaction …. Operações de escrita na BD [COMMIT, ROLLBACK] … End Transaction COMMIT – actualização permanente na BD das alterações efectuadas desde o ultimo commit ou início de transacção. ROLLBACK – desfaz todas as alterações desde o último commit ou início de transacção. O End Transaction faz automaticamente o COMMIT. Caso a transacção termine abruptamente antes do End Transaction é feito automaticamente o ROLLBACK. Nem sempre é adequado. Por exemplo, situações do tipo Update a milhares de registos (caso a transacção falhe a meio, o ROLLBACK desfaz tudo desde o início). Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Transacções (III) Chained Transactions Start Transaction …. commit point (ou check point). . commit point … End Transaction Caso haja uma falha a meio, apenas é feito o ROLLBACK até ao Commit Point anterior. Enquanto não for executado o End Transaction, nenhuma alteração é efectiva. Em relação às Flat Transactions, o ROLLBACK desfaz menos, mas a transacção exige mais recursos (memória e processamento). Quem decide os commit point é o programador (manual) ou o SGBD (automático). Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (I) O problema da concorrência coloca-se em ambientes multi utilizador: várias transacções a acederem em simultâneo aos mesmos dados (por oposição a acederem em série). Exemplos de execuções Transacção T 1 Transacção T 2 Ler saldo Saldo = Saldo - 100 Saldo = Saldo - 400 Escrever Saldo Execução em série Lost update – perdeu-se uma actualização Execução concorrente Tempo Operação Saldo 1 T 1 Ler saldo 1000 2 T 1 Escrever Saldo 900 2 T 2 Ler Saldo 1000 3 T 2 Ler Saldo 900 3 T 2 Escrever Saldo 600 4 T 2 Escrever Saldo 500 4 T 1 Escrever Saldo 900 Estado da BD coerente Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (II) Exemplo de uma situação de dirty read (ler valor inexistente) Transacção T 1 Transacção T 2 Ler movimento Ler crédito em TAB 1 Escrever crédito em TAB 2 Escrever débito O Valor de Crédito em Tab 1 e Tab 2 tem que ser idêntico no fim de T 2 Saldo = débito - crédito Escrever Saldo Tempo Operação TAB 1 TAB 2 1 T 1 Ler movimento 0 0 2 T 1 Escrever crédito TAB 1 1000 0 3 T 2 Ler crédito 1000 0 4 T 1 escrever débito 1000(*) 0 5 T 2 escrever crédito TAB 2 1000 6 T 1 Escrever Saldo 1000 Pedro Ramos, DCTI/ISCTE (*) Hipótese: erro na transacção T 1, é feito o Rollback de T 1(Tab 1. Credito = 0). O Crédito fica diferente nas duas tabelas no fim de T 2. Índice
Modelo Relacional Concorrência (III) Escalonamentos Serializados As restrições de integridade apenas garantem que cada transacção isoladamente termina de forma a deixar a base de dados coerente, nada garantem em relação às interferências entre as transacções. Dado que os escalonamentos em série não tiram partido das potencialidades multi utilizador, a solução é encontrar um escalonamento serializado, isto é, um escalonamento concorrente que, após o seu término, a base de dados fique num estado idêntico ao que teria ficado caso o escalonamento fosse em série. Em vez de tentar-mos identificar as sequências de operações que asseguram um escalonamento serializado (não é fácil), opta-se por adoptar um método de controlo de concorrência que automaticamente assegure escalonamentos serializados. Três Métodos possíveis: Mecanismos de locking (preventivo); Mecanismos de etiquetagem (preventivo); Optimistas. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (IV) Optimistas: parte do pressuposto que as interferências são raras. Deixa ocorrer as transacções até ao fim, e depois verifica se o commit não traz problemas de serialização (utiliza conjuntos write set e read set com todas as actualizações efectuadas), caso existam problemas, faz o rollback de tudo. Mecanismos de Etiquetagem: utilizam-se etiquetas que indicam a ordem de chegada das transacções. Os dados acedidos (para leitura ou escrita) ficam com ID da etiqueta que lhe acede. Existe um conflito quando uma transacção tenta aceder a um elemento de dados cujo valor de etiqueta é superior ao seu. Nesse caso é necessário desfazer e reiniciar a transacção. Existem abordagens mais elaboradas com etiquetas de leitura e escrita. Mecanismos de Locking: os mais utilizados. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (V) Mecanismos de Locking Um lock é uma variável associada a um elemento da base de dados que, de acordo com o seu valor em cada momento, vai permitir ou impedir ser acedido. Antes de aceder a um elemento da base de dados, tanto para leitura como para actualização, é necessário obter o lock desse elemento. Um elemento da base de dados pode ter um de três estados: lock para leitura; lock para escrita; unlocked. A cada lock de leitura é necessário associar um valor que traduza o número de transacções que, em cada momento, mantêm esse tipo de lock. O valor vai sendo decrementado à medida que as transacções vão libertando o elemento de dados. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (VI) Two Phase Loccking (2 PL) Método de controlo de concorrência que utiliza mecanismos de locking que garantem a serialização das transacções. Uma transacção satisfaz o 2 PL se todos os seus locks antecederem os seus unlocks. Exemplo Duas transacções: T 1 credita uma conta com valores em numerário (aumenta o saldo disponível e contabilístico); T 2 credita uma conta com valores em cheque (apenas aumenta o saldo contabilístico). Ambas as transacções satisfazem o 2 PL. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (VII) T 1 Begin transaction Rlock(saldo_disp) Ler saldo_disp = saldo_disp + credito Wlock(saldo_disp) Escrever saldo_disp Rlock(saldo_cont) Ler saldo_cont = saldo_cont + credito Wlock (saldo_cont) Escrever saldo_cont Unlock(saldo_disp) Unlock (saldo_cont) End Transaction Pedro Ramos, DCTI/ISCTE T 2 Begin Transaction Rlock(saldo_cont) Ler saldo_cont = saldo_cont + crédito Wlock (saldo_cont) Escrever saldo_cont Unlock (saldo_cont) End Transaction Só liberta no fim Índice
Modelo Relacional Concorrência (VIII) O seguinte escalonamento (não serializado) não poderia acontecer: Tempo T 1 1 Ler saldo_disp Calcular saldo_disp 2 Ler saldo_cont Calcular saldo_cont 3 Escrever saldo_disp 4 Ler saldo_cont Calcular saldo_cont 5 6 T 2 O Saldo Cont tem associado um lock de leitura, logo não pode ser facultado a T 2 um lock de escrita. Escrever saldo_cont No entanto o 2 PL não impede os dead locks. Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (IX) Tempo T 1 1 Rlock (saldo_disp) 2 Ler saldo_disp T 2 Dead lock em 2 PL 3 Rlock(saldo_cont) 4 Ler saldo_cont 5 Wlock(saldo_disp) 6 Escrever saldo_disp 7 Rlock(saldo_cont) 8 Ler saldo_cont 9 10 Situação de dead lock, T 2 e T 1 bloquedas. T 2 tem que libertar. . Wlock(saldo_cont) 11 Unlock(saldo_cont) 12 Wlock(saldo_cont) 13 Escrever saldo_cont 14 Unlock(saldo_disp) 15 Unlock(saldo_cont) T 2 pode prosseguir 16 Rlock(saldo_cont) 17 . . Pedro Ramos, DCTI/ISCTE Índice
Modelo Relacional Concorrência (X) Tempo T 1 1 Rlock (saldo_disp) 2 Ler saldo_disp T 2 Evitar o Dead lock: 3 Rlock(saldo_cont) 4 Ler saldo_cont 5 Wlock(saldo_disp) 6 Escrever saldo_disp 7 Wlock(saldo_cont) (*) 8 Wlock(saldo_cont) 9 Escrever saldo_cont 10 Unlock saldo_cont 11 Wlock(saldo_cont) 12 ler saldo_cont 13 Escreve saldo_cont 14 Unlock(saldo_disp) 15 Unlock(saldo_cont) Pedro Ramos, DCTI/ISCTE em vez de deixar acontecer o deadlock, T 1, antes de se iniciar, ficava na posse de todos os locks que necessita. T 1 fica bloqueada T 1 pode continuar (*) aguarda o unlock de saldo_cont Índice
Modelo Relacional Concorrência (XI) Os SGDB’s normalmente implementam automaticamente o 2 PL ao nível do registo (record lock). Essa opção (configurável) no entanto degrada a eficiência das transacções. A atribuição de um Read ou Write Lock é feita automaticamente pelo SGBD em função do tipo de operação que o programador (utilizador) manda executar (as operações de leitura originam apenas um read lock). Ao utilizador cabe: • Decidir o início e fim da transacção; • Configurar o nível de segurança do SGBD ou transacção (optar por 2 PL ou níveis mais tolerantes / optimistas); • Ordenar a sequência de operações da melhor forma. Mais adiante estas opções são exemplificadas recorrendo a exemplos em JAVA e Stored Procedures. Pedro Ramos, DCTI/ISCTE Índice