How to convert an Entity-Relationship model to a Relational Database Schema
Generalizacion

This resource is designed to help you convert from an Entity-Relationship Model to a Relational Model, explaining each of the relationships.

Each tab explains the transformation of each relationship:

Bold: Key

Bold and Underlined: Primary Key

(fk): Foreign Key

N:M Relationship

When the cardinality of the relationship between two entities is many-to-many (N:M), the relationship is always transformed into a table. This table adopts as its primary key, and simultaneously as foreign keys, the primary keys of the two entities, and includes the relationship’s attributes as its own.

Relación N:M

Relational Schema

  • Entity 1 (Id1, attribute1)
  • Entity 2 (Id2, attribute2)
  • Relationship (Id1(fk), Id2(fk), relationshipAttribute)

Summary Table

 

Relational Schema Referential Diagram
Entity1 (Id1, Attributes1) Id1
Entity2 (Id2, Attributes2) Relationship ————–> Entity1
Relationship (Id1(fk), Id2(fk), relationshipAttributes) Id2
Relationship ————–> Entity2
1:N Relationship

One-to-Many Relationship (1:N). For these relationships, we have two distinct cases:

Case 1:1

This applies when the entity with a maximum cardinality of 1 is mandatory, that is (1,1).

1:N Relationship

RELATIONAL SCHEMA

Entity(1,1) (EntityID(1,1), Entity_Attributes(1,1))

Entity(N) (EntityID(N), Entity_Attributes(N), EntityID(1,1)(fk))

 

Case 0:1

This case applies when the entity with a maximum cardinality of 1 is not mandatory, that is (0,1).

1:N Relationship case (0,N)

RELATIONAL SCHEMA

Entity(1) (EntityID(1), EntityAttributes(1))

Entity(N) (EntityID(N), EntityAttributes(N))

Relationship (EntityID(N)(fk), RelationshipAttributes, EntityID(1)(fk))

SUMMARY TABLE

CASES

RELATIONAL SCHEMA

REFERENTIAL DIAGRAM

(1,1)

Entity(1,1) (EntityID(1,1), Entity_Attributes(1,1))

Entity(N) (EntityID(N), Entity_Attributes(N), EntityID(1,1)(fk))

EntityID(1,1)
Entity(N) —————–> Entity(1,1)
(0,1)

Entity(1) (EntityID(1), EntityAttributes(1))

Entity(N) (EntityID(N), EntityAttributes(N))

Relationship (EntityID(N)(fk), RelationshipAttributes, EntityID(1)(fk))

EntityID(1)
Relationship —————–> Entity(1)EntityID(N)
Relationship —————–> Entity(N)
1:1 Relationship

For 1:1 relationships, we can encounter three different cases:

Case (1,1)(1,1)

Both entities are joined into one table, and one of the two keys is chosen as the primary key.

1:1 Relationship Case (1,1)(1,1)

RELATIONAL SCHEMA

Entity1andEntity2 (Id1, Attributes1, Attributes2, Id2(fk))

You can choose which key will be the primary key and which will be the foreign key.

 

Case (1,1)(0,1)

The primary key of the entity with cardinality (1,1) is propagated as a foreign key in the entity with cardinality (0,1).

1:1 Relationship Case (0,1)(1,1)

RELATIONAL SCHEMA

Entity(1,1) (EntityID(1,1), EntityAttributes(1,1))

Entity(0,1) (EntityID(0,1), EntityAttributes(0,1), EntityID(1,1)(fk))

 

Case (0,1)(0,1)

The relationship is converted into a table, and its primary key will be composed of the primary keys of both entities, which will also serve as foreign keys.

1:1 Relationship Case (0,1)(0,1)

RELATIONAL SCHEMA

Entity1 (EntityID1, EntityAttributes1)

Entity2 (EntityID2, EntityAttributes2)

Relationship (EntityID1(fk), EntityID2(fk), RelationshipAttributes)

 

SUMMARY TABLE

 

CASES

RELATIONAL SCHEMA

REFERENTIAL DIAGRAM

(1,1) (1,1) Entity1andEntity2 (Id1, Attributes1, Attributes2, Id2(fk))
You can choose which key will be the primary key and which will be the foreign key.
(1,1) (0,1)

Entity(1,1) (EntityID(1,1), EntityAttributes(1,1))

Entity(0,1) (EntityID(0,1), EntityAttributes(0,1), EntityID(1,1)(fk))

EntityID(1,1)
Entity(0,1) –————-> Entity(1,1)
(0,1) (0,1)

Entity1 (EntityID1, EntityAttributes1)

Entity2 (EntityID2, EntityAttributes2)

Relationship (EntityID1(fk), EntityID2(fk), RelationshipAttributes)

EntityID1
Relationship ————–> Entity1

EntityID2
Relationship ————-> Entity2

Reflexive Relationship

The following cases may occur:

Case (1:1)

In the resulting table, the same attribute will be added twice, once as the primary key and once as the foreign key to itself.

1:1 Reflexive Relationship

RELATIONAL SCHEMA

Entity1 (EntityID1, EntityAttributes1, RoleID(fk))

 

Case (1:N)

For this type, there are two cases:

  • 1:N. If it has cardinality (1,N), it is treated the same as 1:1 relationships.

RELATIONAL SCHEMA

Entity1 (EntityID1, EntityAttributes1, RoleID(fk))

Same as the previous case

  • 0:N. If it has cardinality (0,N), a new table is created whose key will be that of the many-side entity, and the key is also propagated to the new table as a foreign key.

0:N Reflexive Relationship

RELATIONAL SCHEMA

Entity1 (EntityID1, Attributes1)

Role (EntityID1, RoleID(fk))

 

Case (N:M)

It is treated the same as N:M relationships. The relationship is converted into a table, and its primary key will be composed of the primary keys of both roles, which will be both primary keys and foreign keys.

N:M Reflexive Relationship

RELATIONAL SCHEMA

Entity1 (EntityID1, Attributes1)

Role (RoleID, RoleAttributes)

Relationship (EntityID1(fk), RoleID(fk), RelationshipAttributes)

Same as Binary N:M relationships

 

SUMMARY TABLE

 

CASES

RELATIONAL SCHEMA

REFERENTIAL DIAGRAM

(1,1) Entity1 (EntityID1, EntityAttributes1, RoleID(fk)) RoleID
Entity1 ————> Entity1
(1,N) or (0,N) (1,N)
Entity1 (EntityID1, EntityAttributes1, RoleID(fk))
Same as the previous case(0,N)
Entity1 (EntityID1, Attributes1)
Role (EntityID1, RoleID(fk))
(1,N)
RoleID
Entity1 ————> Entity1(0,N)
EntityID1
Role ————————> Entity1
RoleID Role ————————> Entity1
(N,M)

Entity1 (EntityID1, Attributes1)

Role (RoleID, RoleAttributes)

Relationship (EntityID1(fk), RoleID(fk), RelationshipAttributes)
Same as Binary N:M Relationships

EntityID1
Relationship —————–> Entity1RoleID
Relationship ———————> Role
Ternary Relationship

The relationship is converted into a table, containing its attributes plus the primary keys of all the entities it associates as foreign keys.
The primary key of the resulting table will be composed of the keys of the entities, taking into account the following cases:

N:N:N Relationship

Primary key of the relationship composed of the keys of the entities, which will also be foreign keys.

RELATIONAL SCHEMA

Relationship (Id1(fk), Id2(fk), Id3(fk))

 

1:N:N Relationship

Same as the previous one, but the key of the entity with maximum cardinality 1 will only be a foreign key, not a primary key.

RELATIONAL SCHEMA

Relationship (Id1(fk), Id2(fk), Id3(fk))

 

1:1:N Relationship

The primary keys of the entities will be foreign keys in the relationship, and the key of the entity with maximum cardinality N will also be the primary key of the relationship.

RELATIONAL SCHEMA

Relationship (Id1(fk), Id2(fk), Id3(fk))

 

SUMMARY TABLE

 

CASES

RELATIONAL SCHEMA

REFERENTIAL DIAGRAM

N: N : N Relationship (Id1(fk), Id2(fk), Id3(fk))

Id1
Relationship ———- > Entity1

Id2
Relationship ———- > Entity2

Id3
Relationship ———- > Entity3

1 : N : N Relationship (Id1(fk), Id2(fk), Id3(fk))
1 : 1 : N Relationship (Id1(fk), Id2(fk), Id3(fk))
Generalization

Generalization

For this type of relationship, we have 3 options:

Option 1

Integrate all entities into a single table by absorbing the subtypes. A table is created that contains all the attributes of the super-entity, all of the sub-entities, and a discriminatory attribute to distinguish which sub-entity each record in the table belongs to. This rule can be applied to any type of hierarchy.

RELATIONAL SCHEMA

All sub-entities within the super-entity table

Super-entity ( SuperEntityID, EntityAttributes, Sub1Attributes, Sub2Attributes)

Option 2

Elimination of the super-entity in total and exclusive hierarchies. The attributes of the super-entity are transferred to each sub-entity, creating a table for each sub-entity. The super-entity will not have a table, and the attribute that distinguishes between sub-entities is eliminated.

RELATIONAL SCHEMA

Elimination of the super-entity in total and exclusive hierarchies

Sub1 (SuperEntityID, SuperEntityAttributes, Sub1Attributes)

Sub2 (SuperEntityID, SuperEntityAttributes, Sub2Attributes)

Sub1_Relationship (Depends on the cardinality of the relationship)

Sub2_Relationship (Depends on the cardinality of the relationship)

 

Option 3

Insert a 1:1 relationship between the super-entity and the sub-entities. The attributes are maintained, and each sub-entity is identified with a foreign key referencing the primary key of the super-entity. The super-entity will maintain a 1:1 relationship with each sub-entity.

RELATIONAL SCHEMA

Insert 1:1 relationship between the super-entity and the sub-entities

Super-entity (SuperEntityID, SuperEntityAttributes)

Sub1 (SuperEntityID, Sub1Attributes)

Sub2 (SuperEntityID, Sub2Attributes)

Relationship (Depends on the cardinality of the relationship between Supertype and the other entity).

 

SUMMARY TABLE

 

CASES

RELATIONAL SCHEMA

Case 1

All sub-entities within the super-entity table

Super-entity ( SuperEntityID, EntityAttributes, Sub1Attributes, Sub2Attributes)

Case 2

Elimination of the super-entity in total and exclusive hierarchies

Sub1 (SuperEntityID, SuperEntityAttributes, Sub1Attributes)

Sub2 (SuperEntityID, SuperEntityAttributes, Sub2Attributes)

Sub1_Relationship (Depends on the cardinality of the relationship)

Sub2_Relationship (Depends on the cardinality of the relationship)

Case 3

Insert 1:1 relationship between the super-entity and the sub-entities

Super-entity (SuperEntityID, SuperEntityAttributes)

Sub1 (SuperEntityID, Sub1Attributes)

Sub2 (SuperEntityID, Sub2Attributes)

Relationship (Depends on the cardinality of the relationship between Supertype and the other entity)

I hope this resource helps you understand and speed up the process of designing and developing a good database. Best regards!

 

Recommended book:

Learn SQL in a Weekend

Learn SQL in a Weekend

 


How to convert an Entity-Relationship Model to a Relational database schema – Platita Software
Entidad Relación Blog
Link your Data!!

Categorías

¡Descubre ‘El Viaje de los Datos: Una Aventura Relacional’!

Ilustración de un reino mágico llamado 'Relationalia', representando conceptos de bases de datos como entidades y relaciones en forma de elementos naturales como bosques, ríos y montañas.

Protégete con el mejor Antivirus

Deja tu comentario

0 Comments

Leave a Reply

No te pierdas ni un artículo

He leído y acepto las Políticas de Privacidad y el Aviso Legal

11 + 4 =

Nuestra Tienda Online

Platita Store es nuestra tienda online de productos informáticos. Envíos sólo a las Islas Canarias en 24h/48h