The Entity-Relationship (ER) model is a conceptual, abstract database design framework representing entities, attributes, and relationships. It is mapped to a relational schema during the implementation phase.
Entities & Entity Sets
Attributes in Detail
| Attribute Type | Visual Symbol | Rule & Mechanics | Example Description |
|---|---|---|---|
| Key Attribute | Ellipse with underlined text | Uniquely identifies each instance in the entity set. | RollNo in Student relation |
| Composite Attribute | Ellipses branching out from another ellipse | Attribute composed of multiple sub-attributes. Saves structured details. | Address branching into Street, City, Zip |
| Multivalued Attribute | Double Ellipse | Can store more than one value for a single entity instance. | Phone_No (a student can have multiple phone numbers) |
| Derived Attribute | Dashed Ellipse | Value is computed dynamically from other stored columns. Not saved directly on disk. | Age (computed dynamically from Date_Of_Birth) |
Relationships & Degree
A relationship connects two or more entities. The degree is the number of participating entity sets:
Cardinality & Structural Constraints
Generalization, Specialization, Aggregation & Inheritance
ER-to-Relational Mapping Algorithm
Step-by-step logic to map conceptual ER diagrams into database tables:
Strong Entity
Create a table. Simple attributes become columns. Composite attributes are flattened into individual columns (e.g.
Street,Citycolumns). Key attribute becomes the Primary Key.Weak Entity
Create a table. Add all attributes of the weak entity. Put the Primary Key of the owner entity as a Foreign Key. The Primary Key is a composite key:
{Owner_PK, Discriminator}.1:1 Relationships
Based on participation constraints:
• Both Total: Merge the two entity sets into a single table.
• One Total, One Partial: Take the PK from the partial side and place it as a Foreign Key in the total participation side's table.
• Both Partial: Keep separate tables and add a Foreign Key to either side (preferably the side with fewer null rows).1:N / N:1 Relationships
Do not create a new table. Take the Primary Key from the "1" side and place it as a Foreign Key in the table representing the "N" side (to avoid null values).
M:N Relationships
Must create a new table. The columns of the new table are the primary keys of both participating entities (each acting as a foreign key). The primary key of this new table is the composite key
{PK1, PK2}.Multivalued Attribute
Create a new table. Place the parent entity's Primary Key (as FK) and the multivalued attribute column in this table. The composite PK is
{Parent_PK, Attribute}.