Entity-Relational Data Modeling

Introduction
An Entity-Relational Model(ERM) is an abstract and conceptual representation of data. It is a DB method used to produce a type of conceptual schema of a system.

ER Definitions
Entity(Instance)
An entity is an instance of a physical object in the world.
Attributes:
Properties of entities that describe their characteristics.
Types:
simple
Composite:
Multiple.
Relationships:
1. Relationship are bi-directional, they can be phrased both ways.
2. Degree:
• binary involving two entities.
• ternary involving three entities.
3. Cardinality: Defines how many entities are involved in a
relationship.
4. Relationships may have properties (attributes).
5. Relationships can be recursive.

Translating ER Models to Relational Schema
Use ER Models to convert into schema.

Strong Entities
Each strong entity becomes a relation.

Week Entities
Convert each weak entity into a relation with a foreign key to
its identifying entity.
• Identify the owning entities E1, E2, … , En.
• Create a relation R for the weak entity.
• The primary key of R is the primary keys of the owning entities
plus the partial key of the weak entity.
• Create foreign keys in R for the primary keys in the owning entities.
• Attributes are converted in a similar manner as strong entities.

1:1 Relationship
Convert a 1:1 relationship, R, between entities Ei and Ej into unique foreign keys references from the relations representing both entities.
• Identify the relations Ri and Rj that correspond to the entities Ei and Ej.
• Choose one of the relations, say Ri.
• Choose the entity/relation that will always participate in the relationship.
• Add the attributes of R to Ri.
• Add the primary key attributes of Rj to Ri and create a foreign key reference.
• Make sure the primary key attributes of Rj are unique

1:N Relationships
Convert a 1:N relationship (or N:1 relationship), R, between entities Ei and Ej into a foreign key references from the N-sided entity to the 1-sided entity.
• Identify the relations Ri and Rj that correspond to the entities Ei and Ej.
• Choose the N-sided relation, say Ri.
• Add the attributes of R to Ri.
• Add the primary key attributes of Rj to Ri and create a foreign key reference to Rj from Ri.

M :N Relationships
Convert a M:N relationship, R, between entities Ei and Ej into a new relation R with foreign key references to the two participating entities.
• Identify the relations Ri and Rj that correspond to the entities Ei and Ej.
• Create a new relation R.
• Add the attributes of the relationship to R.
• The primary key of R is a composite of the primary keys of Ri and Rj and create a foreign key reference from R to Rj and to Ri.

Multi-valued Attributes
Convert multi-values attributes into a relation with a composite key of the attribute value and the primary key of the attribute’s entity.
• Identify the entity’s relation, Ri.
• Create a relation R for the multi-value attribute where R contains:
• a single-value attribute; and
• Ri’s primary key, which is a foreign key from R to Ri .
• The primary key of R is a composite key of the attribute value and Ri’s primary key