Entity-Relationship (E-R) Models

Entity-Relationship (E-R) Models in DBMS

The Entity-Relationship (ER) model is a high-level conceptual data model used to define the data elements and relationships for a specified system. It develops a conceptual design for the database, which is then mapped into a relational schema.

1. Core Components of ER Model

1.1 Entity and Entity Set

• Entity: A real-world object that can be distinctly identified. For example, a student, a car, an employee.
• Entity Set: A collection of entities of the same type that share the same properties or attributes (e.g., all Students in a school).
• Strong Entity: An entity that can exist independently of other entity types. It has its own primary key.
• Weak Entity: An entity that cannot be uniquely identified by its attributes alone. It depends on a strong entity (identifying entity) and has a partial key (discriminator).

1.2 Attributes

• Simple Attribute: Cannot be divided into sub-parts (e.g., Roll_No, Age).
• Composite Attribute: Can be divided into sub-parts (e.g., Address can be divided into Street, City, State).
• Single-valued Attribute: Contains a single value (e.g., Aadhar Number).
• Multi-valued Attribute: Can have multiple values for a single entity (e.g., Phone Number). Represented by a double oval.
• Derived Attribute: Its value can be derived from other attributes (e.g., Age can be derived from Date of Birth). Represented by a dashed oval.

1.3 Relationship and Relationship Set

• Degree of a Relationship: The number of participating entity sets. (Unary, Binary, Ternary, N-ary).

2. Mapping Constraints (Cardinality Ratios)

1. One-to-One (1:1): An entity in A is associated with at most one entity in B, and vice versa. (e.g., Citizen and Passport).
2. One-to-Many (1:N): An entity in A is associated with any number of entities in B. An entity in B is associated with at most one entity in A. (e.g., Department and Employee).
3. Many-to-One (N:1): Many entities in A can be associated with at most one entity in B.
4. Many-to-Many (M:N): An entity in A is associated with any number of entities in B, and vice versa. (e.g., Student and Course).

3. Keys in DBMS

• Super Key: A set of one or more attributes that can uniquely identify an entity in the entity set.
• Candidate Key: A minimal Super Key. (A Super Key with no redundant attributes).
• Primary Key: A Candidate Key chosen by the database designer to uniquely identify entities.
• Foreign Key: An attribute (or set of attributes) in one table that refers to the Primary Key in another table, used to link them.
• Partial Key: Used in weak entities to distinguish them, combined with the primary key of the strong entity.

4. E-R Diagram Symbols

• Rectangle: Represents Entity Sets.
• Double Rectangle: Represents Weak Entity Sets.
• Ellipse (Oval): Represents Attributes.
• Double Ellipse: Represents Multi-valued Attributes.
• Dashed Ellipse: Represents Derived Attributes.
• Diamond: Represents Relationship Sets.
• Double Diamond: Represents Identifying Relationship Sets (for weak entities).
• Lines: Link attributes to entity sets and entity sets to relationship sets.