ER Model and Relational Model

ER Model and Relational Model

Entity-Relationship (ER) model is a conceptual data model used for database design.

ER Model Components

1. Entity

Entity is a real-world object or concept.

Types:

• Strong Entity: Has its own primary key
• Weak Entity: Depends on another entity, has partial key

Notation: Rectangle

2. Attribute

Attribute is a property of an entity.

Types:

• Simple: Atomic (cannot be divided)
• Composite: Can be divided (e.g., Name → First, Last)
• Single-valued: One value per entity
• Multi-valued: Multiple values (e.g., Phone numbers)
• Derived: Calculated from other attributes
• Key: Uniquely identifies entity

Notation:

• Simple: Oval
• Composite: Oval with connected ovals
• Multi-valued: Double oval
• Derived: Dashed oval
• Key: Underlined

3. Relationship

Relationship is an association between entities.

Types:

• One-to-One (1:1): One entity relates to one entity
• One-to-Many (1:N): One entity relates to many entities
• Many-to-Many (M:N): Many entities relate to many entities

Notation: Diamond

Cardinality:

• Minimum Cardinality: Minimum number of relationships
• 0: Optional participation
• 1: Mandatory participation
• Maximum Cardinality: Maximum number of relationships
• 1: One
• N: Many

Relationship Participation

Total Participation: Every entity must participate (double line)

Partial Participation: Some entities may not participate (single line)

ER Diagram Example

Entities:

• Student (SID, Name, Age)
• Course (CID, Name, Credits)
• Enrollment (Grade)

Relationships:

• Student enrolls in Course (M:N)
• Enrollment has attributes (Grade)

Conversion to Relational Model:

• Student(SID, Name, Age)
• Course(CID, Name, Credits)
• Enrollment(SID, CID, Grade)

Relational Model

Relation (Table)

Relation is a table with rows and columns.

Properties:

• No duplicate rows
• Order of rows doesn't matter
• Order of columns doesn't matter
• All values are atomic

Tuple (Row)

Tuple is a row in a relation.

Attribute (Column)

Attribute is a column in a relation.

Domain

Domain is the set of allowed values for an attribute.

Key

Superkey: Set of attributes that uniquely identifies a tuple

Candidate Key: Minimal superkey (no proper subset is superkey)

Primary Key: Chosen candidate key

Foreign Key: Attribute(s) that references primary key of another relation

Relational Algebra

Relational Algebra is a procedural query language.

Basic Operations

1. Selection (σ)

Selects rows satisfying condition.

Notation: σ_condition(Relation)

Example: σ_age>20(Student)

2. Projection (π)

Selects columns.

Notation: π_attributes(Relation)

Example: π_name,age(Student)

3. Union (∪)

Combines rows from two relations (must be union-compatible).

Notation: R1 ∪ R2

4. Set Difference (-)

Rows in R1 but not in R2.

Notation: R1 - R2

5. Cartesian Product (×)

All combinations of rows.

Notation: R1 × R2

6. Join

Combines related rows.

Types:

• Natural Join (⋈): Joins on common attributes
• Theta Join (⋈_θ): Join with condition
• Equijoin: Theta join with equality condition

Example: Student ⋈ Enrollment

GATE CS Important Points

1. ER Diagrams: Know all components and notations
2. Cardinality: Understand 1:1, 1:N, M:N relationships
3. Keys: Superkey, candidate key, primary key, foreign key
4. Relational Algebra: Master all operations
5. ER to Relational: Convert ER diagrams to relations

Practice Tips

1. Draw ER Diagrams: Practice drawing for given scenarios
2. Identify Relationships: Determine cardinality correctly
3. Convert ER to Relations: Practice conversion
4. Relational Algebra: Solve query problems
5. Previous Year Questions: Solve GATE ER model questions