Please enable JavaScript.
Coggle requires JavaScript to display documents.
Entity-Relationship Model - Coggle Diagram
Entity-Relationship Model
Conceptual model proposed by Peter Chen in the 1970s to visually represent entities, attributes, and relationships between them
Main components
Entity:
An object in the real world (person, place, thing).
Examples: Book, Author, Client
Attribute:
A characteristic of an entity.
Examples: Title, Publication Date
Relationship:
An association between two or more entities.
Example: Author — Writes — Book
Cardinality:
Indicates how many instances of one entity are related to another.
Example: One-to-many
Primary Key:
Unique identifier of an entity
Graphic representation
Rectangle → Entity
Ellipse → Attribute
Diamonds or lines → Relationships
Elements that make up the design
System Requirements
Data identification, uses and operations
Conceptual Model:
Represents entities, attributes, and relationships.
Used to define the initial structure.
Logical Design:
Translates the conceptual model into tables.
Defines keys, attributes, and constraints.
Applies normalization.
Physical Model:
Describes how the database is implemented in the DBMS.
Includes indexes, storage, and performance.
Standardization:
Eliminates redundancies.
Ensures integrity and consistency.
Indexes:
Improves search speed.
Requires a balance with performance.
Security and Permissions:
Access control, privileges, and data protection.
Documentation:
Describes tables, relationships, and attributes.
Facilitates maintenance and understanding.
Keys
Primary Key:
Uniquely identifies each record
Candidate Key:
Attribute that could be primary.
Foreign Key:
An attribute that references a primary key in another table.
Maintains referential integrity.
Alternative Key:
Candidate key that was not chosen as the primary.
Entity-Relationship Diagram
Graphical representation of entities, attributes, and relationships.
Elementos
Entities → Rectangles
Attributes → Ellipses
Relationships → Lines or diamonds
Cardinality → One-to-one, one-to-many, many-to-many
Primary key → Underlined attribute
Utility
Visualize the logical structure of the database.
Communicate the design clearly before implementation.
Weak Entity Sets
Strong Entity:
Exists independently.
Has its own primary key.
Weak Entity:
Depends on a strong entity.
Does not have a complete primary key.
Partial Identifier:
Part of the key used with the primary key of the strong entity.
Identifying Relationship:
Links the strong entity with the weak one.
Example: Order — Order Details.
Extended E-R Model
Additional Features
Composite attributes: These have subcomponents (e.g., Address → Street, City).
Multivalued attributes: These can have several values (e.g., Telephone).
Derived attributes: Calculated from others (e.g., age).
Relationship attributes: Add information to relationships (e.g., purchase date).
Generalization/specialization hierarchies: General and specific entities (e.g., Student → Undergraduate/Graduate student).
Attribute inheritance: Specialized entities inherit attributes.
Aggregation: Represents entities composed of others.
Entity-Relationship Database Schema Design
General Steps
1. Identify entities and attributes.
2. Define relationships and cardinalities.
3. Refine attributes and define domains.
4. Apply standardization.
5. Design tables (fields, keys).
6. Establish integrity restrictions.
7. Optimize the schema (indexes, performance).
8. Document the entire design.
Iterative process:
May require successive adjustments.
Reducing E-R diagrams to tables
Goal
Convert the conceptual model into a relational model (tables).
Steps
1. Create one table for each entity.
2. Define columns (attributes).
3. Set primary key.
4. Translate relationships:
One-to-one → merge tables or use a foreign key.
One-to-many → use a foreign key on the "many" side.
Many-to-many → create an intermediate table.
5. Define restrictions (primary, foreign, domains).
6. Apply standardization.
7. Document final design.