I recently started PostgreSQL Tutorials and downloaded their sample database. The sample database page introduces about what this database contains and also ER diagram of it. I find this helpful and want to record about what ER diagram is.
ER Diagram?
- An ER is an acronym for “Entity Relationship” and ER diagram is like a flowchart that describes the relationships between each entity of the database and attributes of the entities.
- An ER diagram is useful for desigining and debugging relational database for software engineering.
History of ER Models
- Peter Chen (a.k.a Peter Pin-Shan Chen), currently a faculty member at Carnegie-Mellon University in Pittsburg, is credited with developing ER modeling for database design in the 1970s.
- Chen published a seminal paper in 1976 titled “The Entity-Relationship Model: Toward a Unified View of Data”
- There are other predecessors of Chen’s approach: Charles Bachman, A.P.G. Brown, James Martin.
Uses of ER Eiagrams
- Database design: to model and design relational databases in terms of logic and business rules, specific technology.
- Often considered as an initial step in identifying requirements for an information systems project.
- Later used to model a particular database(s).
- Database troubleshooting: used to analyze existing databases for debugging purposes.
- Business information systems: used to design or analyze relational databases used in businesse processes.
- Business process re-engineering (BPR): helps analyze databases used in business process re-engineering and modeling a new database setup.
- Education: valuable for planning data structures of relational information for educational purposes.
- Research: a key role in setting up useful databases to analyze data.
Components and Features of an ER Diagram
ER diagrams are composed of entities, relationships and attributes. They also depict cardinality, which defines relationships in terms of numbers.
Entity
- A definable thing that can have data stored about it (ex: a customer, student, car)
- a rectangle shape
- Entity type: a group of definable things (ex: students, athletes)
- Entity set: Same as an entity type, but defined at a particular point in time (ex: students enrolled in a class on the first day)
- Entity categories: entities are categorized as strong, weak, or associative
- strong: can be defined soley by its own attributes
- weak: cannot be defined solely by its own attributes
- associative: associates entities within an entity set
- Entity keys: refers to an attributes that uniquely defines an entity in an entity set. Keys can be super, candidate, or primary
- super key: a set of attributes (one or more) that together define an entity in an entity set
- candidate key: a minimal super key (the least possible number of attributes to be a super key)
- primary key: a candidate key chosen by the database designer to uniquely identify the entity set
- foreign key: identifies the relationship between entities
Relationship
- How entities act upon each other and are associated with each other
- ex: entities ‘Student’ and ‘Course’ can have relationship ‘Enrollment’
- Typically shown as diamonds shape or labels
- Recursive relationship: the same entity participates mote than once in the relationship
Attribute
- A property or characteristic of an entity
- Typically shown as an oval or circle shape
- Descriptive attribute: a property or characteristic of a relationship
- Attribute categories:
- simple: atomic value that cannot be divided (ex: phone number)
- composite: sub-attributes from an attribute
- derived: calculated or derived from another attribute (ex: age from a birthdate)
- Multi-value: more than one attribute value is denoted (ex: multiple phone numbers for a person)
- Single-value: one attribute value
Cardinality
- Defines the numerical attributes of the relationship between two entities or entity sets.
- One-to-One: ex: one student associated with one mailing address
- One-to-Many: ex: one student registers for multiple courses, but the courses have a single line back to the student
- Many-to-Many: ex: students as a group are associated with multiple faculty members, and faculty members are associated with multiple numbers
- Cardinality constraints: the minimum or maximum numbers that apply to a relationship
