Paso do diagrama E-R ao modelo relacional

Operacións a realizar para transformar o modelo E/R en relacional

Unha vez obtido o esquema conceptual mediante o modelo E/R, debemos transformar ese diagrama E/R as correspondentes taboas do modelo relacional.

As tres reglas básicas para transformar o diagrama E/R ó modelo relacional son:

1. Toda entidad transformase en una relación
2. As interrelaciones N:M transfórmanse nunha relación
3. As interrelaciones 1:N dan lugar a unha relación ou ben a unha propagación de clave

Transformación de entidades e atributos

Entidades

Cada entidade o esquema E/R dará lugar a unha nova relación; a súa clave primaria é o Identificador Principal da Entidade.

Atributos

Cada atributo dunha entidade transfórmase nun atributo da relación para a Entidade; hai que ter en conta os seus distintos tipos de restriccións semánticas:

Atributos univaluados

Dan lugar a un atributo da relación.

Atributos multivaluados

Para un atributo multivaluado xenérase unha táboa (entidade) con ese atributo a a clave primaria da entidade á que pertence. A clave primaria da táboa estará composta polo atributo multivaluado e a clave herdada.

Exemplo
A entidade EMPRESAS dispón do atributo multivaluado DIRECCION OFICINA.
Transformando o atributo multivaluado nunha nova interrelación obteríamos o esquema relacional da dereita

• Atributos multivaluados
• 

  Entidad EMPRESAS dispón do atributo multivaluado DIRECCION OFICINA

• 

  Transformando o atributo multivaluado nunha nova interrelación obtemos o esquema relacional

Outro exemplo
A entidade EMPREGADO dispón do atributo multivaluado TELEFONO

EMPREGADO (nif,nome,data_nac,idade)

TELEFONO (nif,telefono)

Atributos compostos

É necesaria a eliminación de atributos compostos, xa que non hai forma de representar este atributo no modelo relacional; para iso hai dúas alternativas:

• Descompoñer os atributos compostos en atributos simples.
• Prescindir dos elementos individuais, considerando o atributo composto coma un único atributo.

Exemplo.
Unha entidade alumno cun atributo composto Fecha_Nacimiento, formado por Día, Mes e Año
Descompoñendo o atributo composto FECHA_NACIMIENTO en atributos simples quedaría coma na imaxe do medio.
Prescindindo dos elementos individuales quedaría a imaxe da dereita
A elección dunha ou outra opción dependerá de cómo se prevé a utilización futura da base de datos; se é necesario facer referencia os elementos individuais, será aconsellable a descomposición en atributos simples.

• Atributos compostos
• 

  Entidade alumno con atributo composto Fecha_Nacimiento

• 

  Descompoñendo o atributo composto Fecha_Nacimiento en atributos simples

• 

  Prescindindo dos elementos individuais de Fecha_Nacimiento

Atributos obrigatorios

Atributos coa restricción NOT NULL

Atributos opcionales

Atributos que poden tomar valores nulos

Atributos que forman a clave alternativa

Atributos coa restricción de UNIQUE

Atributos derivados

Atributos nos que os seus valores son obtidos coma resultado dalgún cálculo sobre outros atributos

Transformación dos tipos de entidade en relacións

Os tipos de entidade transfórmanse en relacións (tablas), elixindo unha das súas claves candidatas como clave primaria. As claves alternativas serán atributos da relación coa cláusula UNIQUE. O resto dos atributos pasan a ser atributos da relación; estos poderán tomar valores nulos a non ser que se indique o contrario (NOT NULL).

O atributo que será clave principal indicamolo mediante subraido.

Tipo de entidade empresa con clave primaria NIF

Exemplo.
O tipo de entidade EMPRESA transformarase na relación
EMPRESA (NIF, NOMBRE, DOMICILIO)

A sintaxe xeral en SQL para crear unha relación é:

CREATE TABLE < nome táboa >
( < nome columna-1 > < tipo dato-1 >,
  < nome columna-2 > < tipo dato-2 >,
  < nome columna-3 > < tipo dato-3 > NOT NULL,
  ..............................................
  < nome columna-n> <tipo dato-n>,
  PRIMARY KEY (< identificador principal >),
  UNIQUE (< identificador alternativo >) )

Cada atributo da entidad débil transfórmase nunha columna da táboa, incluindo ademáis como clave foránea os atributos que formen a clave primaria da táboa principal. Ademáis, no caso de que a entidade sexa débil en identificación, a Clave Primaria desta táboa estará formada polos atributos da clave foránea mais os atributos que formen a clave parcial da entidade débil.

Transformación dos tipos de interrelación

Interrelacións N:M

As interrelacións con tipo de correspondencia N:M (varios a varios) transformaranse sempre nunha relación na que a súa clave será a combinación das claves das entidades interrelacionadas. Así os atributos que forman a clave primaria nesta nova relación son claves alleas respecto as entidades nas que estos atributos son clave primaria.

Se a interrelación ten atributos estos pasarán a formar parte da relación creada para a interrelación.

Exemplo de relación pedidos a productos (N a M), coma se ve na esquerda
A interrelación ten unha correspondencia N:M e por tanto se transforma nunha relación, coma se ve na dereita.
Podemos observar que cada un dos atributos que forman a clave primaria da relación Pedidos-Productos é clave allea respecto a cada unha das relacións onde este atributo é clave primaria (indicanse as claves alleas en cursiva).

• Interrelacións N:M
• 

  Interrelaciones n a m

• 

  A interrelación ten unha correspondencia N:M e polo tanto transfórmase nunha relación

No caso de que a interrelación conteña un atributo multivaluado que non denote unha dimensión temporal, a clave da relación deberá incluir también este atributo.

Xa que un aprendiz podería obtener iguales calificacións nun determinado curso, compre engadir un atributo "FechaEval" para distinguilas e que tamén formará parte da clave primaria.

• Interrelacións con atributos cunha dimensión temporal
• 

  Aprendiz asiste a cursos (N:M)

• 

  Cómpre engadir atributo "FechaEval" que formará parte de clave primaria

Mais no caso de atributos cunha dimensión temporal (xeneralmente atributos que denotan datas, horas o uintervalos de tempo) tanto se son multivaluados como univaluados, é preciso estudar a semántica do universo do discurso co fin de determinar cales van ser os atributos que formen a clave primaria da relación á que da lugar a interrelación.

Deberá estudiarse ó implementar a base de datos se procede establecer as cláusulas de:

• RESTRICT/ NO ACTION: rexeitar a operación de borrado ou modificación
• CASCADE: propagar a modificación (ou borrado) das tuplas da táboa que referencia
• SET NULL: poñer valor nulo na clave allea da táboa que referencia
• SET DEFAULT: poñer un valor por defecto na clave allea da táboa que referencia

Interrelacións 1:N

Nestas interrelacións existen dúas posibilidades de transformación: a) Propagar o Identificador Principal desde a Entidade que se atopa no lado 1 á Entidade que se atopa no lado N (propagación de clave); se existen atributos na interrelación, estos tamén se propagarán. En ausencia de información relativa ó volume de valores nulos que se manexarán ou de se existen posibilidades de que a interrelación evolucione ou non a unha interrelación N:M, esta solución de propagación de clave será a que se adopte na resolución dos exercicios propostos na maior parte dos casos. b) Creación dunha nova relación. Neste caso se transforma a interrelación nunha nova relación, igual que no caso N:M pero tendo como clave so a do lado N. Se a interrelación ten atributos propios, ésta é unha solución razonable.

Exemplo Clientes hacen pedidos (1:N).
No medio solución propagar clave
Na dereita solución crear nova relacion

• Interrelacións 1:N
• 

  Clientes fan pedidos (1:N)

• 

  Solución propagar clave

• 

  Solución crear nova relación

A solución máis axeitada depende das cardinalidades: 1º Se a participación da entidade do lado N é total, a solución máis axeitada (por xenerar menos táboas) é a de propagar aa clave. Se a interrelación tivera atributos, estos pasarían á táboa correspondente á entidade do lado moitos.

Exemplo. A interrelación TRABAJAN na que se indica que todos os empregados (participación total) traballan nalgún departamento

• A participación da entidade do lado N é total
• 

  Participación total de empleados

• 

  Conversión a modelo relacional propagando a clave. Dado que a participación é total a clave allea nom-departamento deberá levar a cláusula de nulos non permitidos.

2º Se a participación da entidade do lado N é parcial, a solución máis axeitada, dependendo do grao de participación, é crear unha nova relación (evitando así a existencia de valores nulos na clave allea que se crearía propagando a clave).

Exemplo. Unha universidade que edita libros. Existen moitos libros que non son editados por universidad algunha (participación parcial de libros).

• A participación da entidade do lado N é total
• 

  Participación parcial de libros

• 

  A solución máis axeitada neste caso é crear unha nova relación

Mais non debe considerarse esta solución como exclusiva, xa que se o grado de participación de Libros é elevado (moitos libros son editados por universidades) pode ser axeitado propagar a clave para xenerar menos tablas.

Aínda que se xenera unha nova táboa, ó igual que acontece coas conversións N:M, a súa clave neste caso é so a que corresponde ó lado moitos. Lóxicamente, se a interrelación Edita tivera atributos propios, estos formarían parte da relación Edita.

Interrelacións 1:1

Unha interrelación de tipo 1:1 é un caso particular dunha N:M ou tamén dunha 1:N, polo que non hai regra fixa para a transformación: pode crearse unha nova relación (seguindo as pautas establecidas para as N:M) ou ben efectuar unha propagación da clave.

A) No caso de propagación de clave

A propagación podería realizarse en ambos sentidos; mais se unha entidad das que participan na interrelación posee cardinalidade (0,1) mentres que a outra posee (1,1), é mellor propagar a clave da entidade con cardinalidade (1,1).

Exemplo. Participación parcial de clientes cunha cardinalidade (0,1) e outra (1,1).

• Participación parcial con interrelación con cardinalidade (0,1) e outra (1,1)
• 

  Participación parcial de clientes

• 

  Modelo relacional obtido

Propágase a clave á entidade que ten cardinalidade (0,1). Se a interrelación tivera atributos engariranse á relación correspondente ó tipo de entidade de participación total.

Esta solución evita os valores nulos que se producirían de ter feito a propagación en sentido contrario.

B) No caso de realizar a transformación nunha nova relación.

Se as dúas entidades que participan na interrelación poseen cardinalidade (0,1) pode ser máis axeitado realizar a transformación nunha nova relación.

• Transformación nunha nova relación
• 

  Participación parcial con cardinalidade (0,1)

• 

  Modelo relacional obtido

Esta solución evita os valores nulos que se producirían de ter propagado a clave de Clientes a Coches ou viceversa.

C) No caso de crear dúas táboas, propagándose a clave dun dos tipos de entidade ó outro.

Se a participación dos dos tipos de entidade é total, crearanse dúas taboaas, propagándose a clave de un dos tipos de entidade ó outro.

• Participación de dous tipos de entidade total
• 

  Participación dos dous tipos de entidade total

• 

  Creanse dúas táboas , propagándose a clave dun dos tipos de entidade ó outro.

O atributo NIF, da relación Datos médicos, é a clave allea que permite a relación con Empleados e ó mesmo tempo é clave candidata, polo que debe declararse como UNIQUE e NOT NULL.

Resumo

Como resumo do descrito podemos dicir que:

1. As interrelacións N:M transfórmanse sempre nunha relación, que terá como clave a unión das claves das entidades interrelacionadas.
2. Nas interrelacións 1:1 e 1:N, en xeneral propagarase a clave; mais dependendo do grao de participación das entidades interrelacionadas e para evitar valores nulos, nalgúns casos transformarase nunha nova relación que terá como clave a de unha das entidades interrelacionadas

Interrelacións grao superior a dous

Este tipo de interrelacións, se non se puideron transformar en interrelacións de grado 2, transformaranse nunha nova relación, cos atributos da interrelación e que terá como clave a concatenación das claves dos tipos de entidade interrelacionados.

Exemplo. Conductores que en determinada data conducen autobuses a certos lugares.

• Interrelacións grao superior a dous
• 

  Interrelacións grao superior a dous

• 

  A clave de Conducen está formada polo conjunto das claves das entidades interrelacionadas, e cada unha destas, funciona como clave allea)

Pódese observar que a clave de Conducen está formada polo conjunto das claves das entidades interrelacionadas, e cada unha destas, funciona como clave allea.

Interrelacións reflexivas

Podemos considerar as interrelacións reflexivas coma un caso particular das binarias, e que funcionan por tanto de xeito similar atendendo o seu tipo de correspondencia e cardinalidades.

Consideramos varios casos:

A) Se o tipo de entidade participa en un dos seus papeis con cardinalidad máxima 1 e no outro con cardinalidad máxima 1 ou N (interrelacións 1:1 ou 1:N) e participación total (para 1:N do lado N)

Se xenera unha relación para o tipo de entidade, tendo como identificador o da entidade, xogando un dos papeles (o do lado N) e engadindo como clave allea o identificador da propia entidade xogando o outro papel.

Exemplo. Unha empresa na que un empregado pode ter subordinados (ser xefe) de outros empregados e que cada empregado é subordinado dun único xefe (ten un só xefe) desde determinada data.

Empleado (NIF-subordinado, nombre, domicilio, fecha, NIF-jefe)

B) Nas interrelacións reflexivas do tipo 1:N, con participación parcial (0) da entidade que xoga o papel do lado N

Transfórmase a interrelación nunha nova táboa, igual que o caso de interrelacións N:M, pero a clave será só a que corresponde ó papel moitos.

Exemplo. Supoñemos neste caso que só algúns empregados son dirixidos por un xefe.

Se o grado de participación fose elevado, (por exemplo case todos os empregados son dirixidos por un xefe) tamén podería propagarse a clave.

C) No caso dunha relación reflexiva con cardinalidad N:M

O máis práctico é crear unha táboa para a entidade e outra táboa para a interrelación; esta terá como atributos os dous papeles que xogue o tipo de entidade (formando a clave primaria e á vez foráneas) e os seus propios.

• Relación reflexiva con cardinalidad N:M,
• 

  Relación reflexiva con cardinalidad N:M

• 

  Unha táboa para a entidade e outra para a interrelación (con atributos os dous papeles que xogue o tipo de entidade e os propios)

Modelo Extendido: Xeneralización/Especialización

A conversión ó modelo relacional das estruturas xerárquicas, depende das súas características de totalidade e solapamento. Consideramos as seguintes solucións

Eliminación do supertipo

Pódese facer só en estruturas xerárquicas totales e disxuntas (non solapadas) e ten a vantaxe de que se utilizan menos táboas. Os atributos e interrelacións do supertipo os herdan os subtipos.

Exemplo. Eliminación do supertipo clientes utilizando subtipos persoas e entidades.

Eliminando o supertipo Clientes, resultan as seguientes táboas no modelo relacional:

Personas (Nif, nombre, teléfono, estado civil)

Entidades (Nif, nombre, teléfono, nombre comercial)

Eliminación dos subtipos

Está solución pode utilizarse en calquier tipo de estruturas xerárquicas producindo un esquema simple, con poucas táboas; pero con moitos valores nulos. O supertipo adquire todos os atributos e interrelacións que teñan os subtipos (tamén se engadiráa o atributo discriminante que indica o tipo de subclase).

Esta solución é boa cando as subclases diferéncianse en moi poucos atributos e as interrelacións que os asocian co resto das entidades do esquema sexan as mesmas para todos ou case todos.

Exemplo. Modelo con persoas que poden ser profesores ou alumnos. Solución con eliminación dos subtipos Profesores e Alumnos

• Eliminación dos subtipos
• 

  Personas poden ser Profesores ou Alumnos

• 

  Eliminando subtipos

• 

  Táboas resultantes despois de eliminar subtipos

O resultado é moi simple, con mínimas táboas; pero prodúcese perda de semántica ó eliminar a estrutura xerárquica ademáis de producirse moitos valores nulos (por exemplo todas as personas que sexan alumnos terán valor nulo no atributo nº profesor)

Transformación dunha entidade nunha táboa

Esta é a solución máis xeneral, que vale para calquier tipo de estrutura xerárquica e non supón perda de semántica nin supón a aparición de moitos valores nulos (coma no caso da eliminación dos subtipo). Como inconveniente, o esquema que resulta é máis complexo (con máis taboas).

A solución para o mesmo exemplo do caso anterior:

O Nif das relacións Profesores e Alumnos, son á vez clave principal e allea para relacionarse con Personas; e nº profesor e nº matrícula son claves candidatas.

Interrelacións exclusivas

A exclusividade das interrelacións terase en conta o implantar a base de datos nun sistema xestor de bases de datos concreto. Éstas transfórmanse como xa se comentou, pero será preciso engadir unha VERIFICACIÓN ou CHECK que comprobe que se un exemplar da entidade participa xa nunha ocurrencia dunha interrelación, entón non pode participar en ninguna ocurrencia da outra interrelación.

Exemplo.
Un libro é editado exclusivamente por unha Editorial ou por unha Universidade.
Farase a comprobación ó gardar rexistros da relación Libros, nos atributos nom-editorial e nom-universidad de que un deles sexa nulo e o outro non; desta forma evítase almacenar na base de datos libros editados por unha Universidade e por unha Editorial.

• Interrelacións exclusivas
• 

  Un libro é editado exclusivamente por unha Editorial ou por unha Universidade

• 

  As táboas resultantes

Comprobarase polo tanto que se cumple a condición:

(nom-universidad=null and nom-editorial not null) or (nom-universidad not null and nom-editorial=null)

Regras para a transformación do Modelo Conceptual MERE ao Modelo Lóxico Relacional

Algunhas xa as vimos nos apartados anteriores. Pero de xeito máis detallado, podemos comprender neste artigo a Transformación do Modelo Conceptual MERE ao Modelo Lóxico Relacional.

Créditos e referencias

• Transformación Relacional
• Diseño lógico - relacional
• Apuntes de Bases de datos de la Universidad de Sevilla

Tarefas

• Tarefas de transformación do modelo Entidade-Relación a modelo Relacional