BD I – Restrições de integridade básicas – parte 2

Dando sequência as restrições de integridade, neste post vamos seguir com a integridade de chave e referencial.

Dado o seguinte modelo conceitual.

Até agora aprendemos e trabalhamos com o conceito de que em relacionamento muitos para muitos (N:N)  o relacionamento vira uma entidade (tabela) e os atributos identificadores (chaves) das tabelas envolvidas se repetem na nova entidade. Mas  qual seria o identificador dessa nova entidade? Há duas saídas, a primeira é chave composta ou usar uma chave substituta.

Modelo  lógico e físico com chave composta.

Notar as chaves que ficaram cinza e preta aos mesmo tempo, mais a condata, ali temos duas chaves conhecidas como PFK pois elas são primárias e estrangeiras ao mesmo tempo IDMEDICO e IDPACIENTE.

CREATE TABLE MEDICO (
 IDMEDICO INTEGER NOT NULL PRIMARY KEY,
 MEDNOME  VARCHAR(100) NOT NULL
);

CREATE TABLE PACIENTE(
 IDPACIENTE INTEGER NOT NULL PRIMARY KEY,
 PACNOME VARCHAR(100) NOT NULL
);

CREATE TABLE CONSULTA(
 IDMEDICO INTEGER NOT NULL,
 IDPACIENTE INTEGER NOT NULL,
 CONDATA DATE NOT NULL,
/* criação da chave composta */
 PRIMARY KEY (IDMEDICO,IDPACIENTE,CONDATA),
/* declaração das chaves estrangeiras */
 FOREIGN KEY (IDMEDICO)   REFERENCES MEDICO,
 FOREIGN KEY (IDPACIENTE) REFERENCES PACIENTE
);

No modelo com chave composta somente as duas colunas não formam uma chave completa (IDMEDICO e IDPACIENTE) pelo motivo que a combinação das duas impossibilitaria a inclusão de uma nova consulta do mesmo médico com o mesmo paciente, por que agora a combinação das duas forma a chave primária, então foi adicionada a coluna CONDATA (data da consulta) como parte da chave primária. As chaves compostas são importantes pois evita problemas de redundância de dados, como por exemplo, inclusão de consultas repetidas para paciente e médico no mesmo dia.

Modelo lógico e físico com chave substituta.

CREATE TABLE MEDICO (
 IDMEDICO INTEGER NOT NULL PRIMARY KEY,
 MEDNOME  VARCHAR(100) NOT NULL
);

CREATE TABLE PACIENTE(
 IDPACIENTE INTEGER NOT NULL PRIMARY KEY,
 PACNOME VARCHAR(100) NOT NULL
);

CREATE TABLE CONSULTA(
 ID INTEGER NOT NULL PRIMARY KEY,
 IDMEDICO INTEGER NOT NULL,
 IDPACIENTE INTEGER NOT NULL,
 CONDATA DATE NOT NULL,
 FOREIGN KEY (IDMEDICO)   REFERENCES MEDICO,
 FOREIGN KEY (IDPACIENTE) REFERENCES PACIENTE
);

No modelo com chave substituta a chave primária volta a ser um identificador simples com um atributo, ou seja o velho ID, mas essa abordagem que facilita a vida do desenvolvedor traz o problema de inclusão de consultas com um médico e um paciente no mesmo dia, ou pior poderia ter vários do mesmo como se não houvesse identificador nenhum.

A saída pra esse problema depois de criar o identificador ID (rodar todos os create table) é recorrer ao uso de índices para garantir a integridade dos dados, no caso de um UNIQUE INDEX (indice único) e assim evitando a inclusão de dados repetidos. O comando abaixo cria um índice único baseado na combinação de 3 colunas, IDMEDICO, IDPACIENTE e CONDATA e se for repetido a combinação no registro da consulta ele levantará uma excessão (erro).

O uso de índices é mais complexo que isso e será tratado melhor em um outro post.

  CREATE UNIQUE INDEX CONSULTA_PACMEDDATA_IDX ON
         CONSULTA(IDPACIENTE,IDMEDICO,CONDATA);

No comando acima criou um índice único, a palavra CONSULTA_PACMEDDATA_IDX é o nome do índice no banco de dados e logo após a combinação efetiva para a formação do índice: CONSULTA(IDPACIENTE,IDMEDICO,CONDATA).

Clicar aqui para instruções de criação de tabelas com GENERALIZAÇÃO/ESPECIALIZAÇÃO.

Esse post foi publicado em BD I - Banco de Dados I. Bookmark o link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s