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

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

RELATIONAL SCHEMA

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

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

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

SUMMARY TABLE

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.

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

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.

RELATIONAL SCHEMA

Entity1 (EntityID1, EntityAttributes1)

Entity2 (EntityID2, EntityAttributes2)

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

SUMMARY TABLE

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.

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.

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.

RELATIONAL SCHEMA

Entity1 (EntityID1, Attributes1)

Role (RoleID, RoleAttributes)

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

Same as Binary N:M relationships

SUMMARY TABLE

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

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