What is the difference between an entity-relationship diagram and a relational schema?

An ER diagram is a conceptual, technology-independent description of a domain in terms of entities and relationships, meant for analysis and communication. A relational schema is the logical design of actual tables, columns, and keys. ER-to-relational mapping rules convert the former into the latter.

When should a relationship become its own table?

Many-to-many relationships always require a separate table holding the key attributes of both participating entities. One-to-many and one-to-one relationships can usually be represented by adding a foreign key to one of the entity tables rather than creating a new table.

Entity-Relationship Modeling

Entity-relationship modeling is a conceptual design technique that describes a domain as a collection of entity sets, their attributes, and the relationships among them, producing a high-level diagram that can be translated into a relational schema.

Etsi aihe työkalulla PaperMindTulossaFind papers & topics

Tools & resources

Lataa diat

Learn & explore

VideoTulossa

Definition

The entity-relationship model is a conceptual data model in which a database is described by entity sets (collections of similar real-world objects), the attributes that characterize them, and relationship sets that associate entities, annotated with cardinality and participation constraints.

Scope

This topic covers the constructs of the entity-relationship (ER) model — entity sets, attributes (simple, composite, multivalued, derived), relationship sets, cardinality and participation constraints, weak entities, and generalization/specialization hierarchies in the extended ER model — and the systematic rules for mapping an ER diagram to relational tables. It excludes the logical refinement of the resulting schema by normalization and the relational model's own structure.

Core questions

How are entities, attributes, and relationships identified from requirements?
What do cardinality and participation constraints express about a relationship?
When is an entity weak, and how is it identified through another entity?
How are generalization and specialization captured in the extended ER model?
What are the rules for translating an ER diagram into relational tables?

Key concepts

entity set and entity
attributes: simple, composite, multivalued, derived
relationship set
cardinality (mapping) constraints
participation constraints
weak entity sets
generalization and specialization
ER-to-relational mapping

Key theories

Entities, relationships, and attributes: The ER model unifies data description by distinguishing the things of interest (entities), the associations between them (relationships), and the descriptive properties (attributes) of each, giving a vocabulary for conceptual design independent of implementation.
Cardinality and participation constraints: Mapping cardinalities (one-to-one, one-to-many, many-to-many) and total versus partial participation specify how many entities can take part in a relationship and whether participation is mandatory, constraining valid database states.
ER-to-relational mapping: Systematic rules convert entity sets into tables, relationship sets into tables or foreign keys depending on cardinality, and weak entities and subclasses into appropriate relational structures, bridging conceptual and logical design.

Clinical relevance

ER modeling is the standard front end of database design across industry: analysts and developers use ER diagrams to capture and communicate requirements with stakeholders before any tables are built, reducing redundancy and design errors in enterprise, web, and information-management systems.

History

Peter Chen introduced the entity-relationship model in 1976 as a way to unify the network, relational, and entity-set views of data with a graphical conceptual notation. The model was later extended with generalization and specialization (the enhanced ER model) and influenced object and UML class modeling, remaining a staple of database design education and practice.

Key figures

Peter Chen

Seminal works

chen1976
silberschatz2019

Frequently asked questions

What is the difference between an entity-relationship diagram and a relational schema?: An ER diagram is a conceptual, technology-independent description of a domain in terms of entities and relationships, meant for analysis and communication. A relational schema is the logical design of actual tables, columns, and keys. ER-to-relational mapping rules convert the former into the latter.
When should a relationship become its own table?: Many-to-many relationships always require a separate table holding the key attributes of both participating entities. One-to-many and one-to-one relationships can usually be represented by adding a foreign key to one of the entity tables rather than creating a new table.