Entity-Relationship Model: ER Diagrams, Attributes, Relationships & Constraints

TAKEAWAYS:

• The ER model visually represents real-world data as entities (objects), attributes (properties), and relationships (interactions) using diagrams.
• Relationships can be 1:1 (one-to-one), 1:N (one-to-many), or M:N (many-to-many), each requiring different database design approaches.
• Weak entities depend on identifying relationships and require a partial key (discriminator) to uniquely identify them.
• Cardinality (min/max participation) defines how many instances of one entity relate to another (e.g., a Customer must place 0 or more Orders).
• ER diagrams translate into relational schemas via normalization (covered in Unit 6), ensuring efficient data storage and queries.
• Real-world applications: eSewa uses ER-like structures to link users, transactions, and service providers; Daraz maps products, orders, and suppliers in M:N relationships.

1. Core Concepts of the ER Model

The Entity-Relationship (ER) model is a high-level conceptual tool for database design. It abstracts real-world data into three key components:

1.1 Entities

An entity is a real-world object that has a distinct identity and can be uniquely identified. Entities can be:

• Strong entities: Exist independently (e.g., Student, Book).
• Weak entities: Exist only through another entity (e.g., Dependent of an Employee).

Example: In a Library Management System, Book and Member are strong entities, while Loan (a record of a book borrowed by a member) could be a weak entity dependent on both.

erDiagram
    Member ||--o{ Loan : "borrows"
    Book ||--o{ Loan : "is_borrowed"
    Loan {
        int loan_id PK
        string date
        string return_date
        int member_id FK
        int book_id FK
    }
    Member {
        int member_id PK
        string name
    }
    Book {
        int book_id PK
        string title
    }

1.2 Attributes

Attributes describe properties of entities. They can be:

• Simple vs. Composite: A Name (composite: FirstName, LastName) vs. Age (simple).
• Single-valued vs. Multivalued: Phone (single) vs. Skills (multivalued for a JobApplicant).
• Derived: Calculated from other attributes (e.g., Salary = BasePay + Bonus).
• Key Attributes:
  • Primary Key (PK): Uniquely identifies an entity (e.g., StudentID).
  • Foreign Key (FK): Links to another entity’s PK (e.g., MemberID in Loan).

Example: For Member:

1.3 Relationships

Relationships define how entities interact. Types:

• 1:1 (One-to-One): One entity relates to exactly one other (e.g., Passport to Person).
• 1:N (One-to-Many): One entity relates to many (e.g., Author to Book).
• M:N (Many-to-Many): Many entities relate to many (e.g., Student to Course).

Cardinality specifies minimum/maximum participation:

• Total Participation (Double Line): Every entity must participate (e.g., every Book must belong to a Category).
• Partial Participation (Single Line): Optional participation (e.g., a Member may or may not have a Loan).

Example (1:N): A Professor can teach many Courses, but a Course has only one Professor.

erDiagram
    Professor ||--o{ Course : "teaches"
    Course {
        int course_id PK
        string name
        int professor_id FK
    }

2. Weak Entities and Identifying Relationships

A weak entity cannot exist without its owner entity. It requires:

1. A partial key (discriminator) to distinguish instances.
2. An identifying relationship (double diamond) with the strong entity.

Example: In a Hospital Management System:

• Patient (strong entity) has many Appointments (weak entity).
• Appointment depends on Patient and has a partial key (e.g., AppointmentID).

erDiagram
    Patient ||--o{ Appointment : "has"
    Appointment {
        int appointment_id PK
        string date
        string time
        int patient_id FK
        string status
    }
    Patient {
        int patient_id PK
        string name
    }

Real-World Tie-In:

• eSewa treats Transaction as a weak entity dependent on User and ServiceProvider. The TransactionID (partial key) + UserID uniquely identifies a transaction.

3. Converting ER Diagrams to Relational Schemas

ER diagrams are translated into tables (relations) using these rules:

1. Entities → Tables.
2. Attributes → Columns.
3. Relationships:
   • 1:1 or 1:N: Add the PK of the "1" side as a FK in the "N" side.
   • M:N: Create a junction table with PKs of both entities.
   • Weak Entities: Combine with the strong entity’s PK + partial key as PK.

Example (M:N → Junction Table): Student and Course have an M:N relationship via Enrollment.

erDiagram
    Student ||--o{ Enrollment : "takes"
    Course ||--o{ Enrollment : "offers"
    Enrollment {
        int enrollment_id PK
        int student_id FK
        int course_id FK
        string grade
    }
    Student {
        int student_id PK
        string name
    }
    Course {
        int course_id PK
        string name
    }

Relational Schema:

Student (StudentID, Name, Email)
Course (CourseID, Title, Credit)
Enrollment (StudentID, CourseID, EnrollmentID, Grade)  -- Composite PK

4. Advantages and Limitations of ER Model

5. Real-World Applications in Nepal

Example 1: eSewa (Digital Payments)

• Entities: User, ServiceProvider, Transaction.
• Relationships:
  • User 1:N Transaction (one user can make many transactions).
  • Transaction M:N ServiceProvider (via a junction table Payment).
• Weak Entity: Transaction depends on both User and ServiceProvider.

Example 2: Daraz (E-Commerce)

• Entities: Customer, Product, Order, Supplier.
• Relationships:
  • Customer 1:N Order (one customer can place many orders).
  • Order M:N Product (via OrderItem junction table).
  • Supplier 1:N Product (one supplier provides many products).

Worked Example: Daraz Order Processing

1. A Customer (PK: CustomerID) places an Order (PK: OrderID).
2. The Order contains many OrderItems (junction table with ProductID and Quantity).
3. The Product is linked to a Supplier (1:N).

erDiagram
    Customer ||--o{ Order : "places"
    Order ||--o{ OrderItem : "contains"
    OrderItem }|--|| Product : "refers_to"
    Product ||--|| Supplier : "supplied_by"
    Customer {
        int customer_id PK
        string name
    }
    Order {
        int order_id PK
        string order_date
    }
    OrderItem {
        int order_item_id PK
        int order_id FK
        int product_id FK
        int quantity
    }
    Product {
        int product_id PK
        string name
    }
    Supplier {
        int supplier_id PK
        string name
    }

Example 3: NTC (Telecom Billing)

• Entities: Customer, ServicePlan, Bill, Payment.
• Relationships:
  • Customer 1:N Bill (one customer gets many bills).
  • Bill 1:1 Payment (each bill has one payment record).
• Weak Entity: Payment depends on Bill.

6. Common Pitfalls and Best Practices

• Avoid Redundancy: Use junction tables for M:N relationships instead of duplicating data.
• Normalize Early: Design ER diagrams with minimal redundancy to ease conversion to 3NF (Unit 6).
• Document Constraints: Clearly mark cardinality (e.g., "a Loan must have exactly one Book").
• Use Real Names: Name entities/attributes based on business terms (e.g., Member instead of User in a library).

7. Step-by-Step: Designing an ER Diagram

Problem: Design a University Admissions System with:

• Student (Name, DOB, Contact)
• Department (Name, Head)
• Course (Code, Credits)
• Admission (Date, FeeStatus)

Steps:

1. Identify Entities: Student, Department, Course.
2. Define Attributes:
   • Student: StudentID (PK), Name, DOB, Contact.
   • Department: DeptID (PK), Name, Head.
3. Define Relationships:
   • Student 1:N Admission (one student can apply to many departments).
   • Department 1:N Course (one department offers many courses).
   • Admission M:N Course (via Enrollment junction table).
4. Draw the ER Diagram:

erDiagram
    Student ||--o{ Admission : "applies_to"
    Department ||--o{ Course : "offers"
    Admission ||--o{ Enrollment : "enrolls_in"
    Enrollment }|--|| Course : "takes"
    Student {
        int student_id PK
        string name
    }
    Admission {
        int admission_id PK
        int student_id FK
        int department_id FK
    }
    Department {
        int department_id PK
        string name
    }
    Course {
        int course_id PK
        string name
    }
    Enrollment {
        int enrollment_id PK
        int admission_id FK
        int course_id FK
    }

8. Exam Tip: How to Score Full Marks

1. Diagrams Are Mandatory:

   • Always draw Crow’s Foot notation for relationships (examiners deduct marks for incorrect symbols).
   • Label PKs, FKs, and cardinality clearly.

2. Answer Past Questions Precisely:

   • For Library Management System (common exam question):
     • Include entities: Book, Member, Loan.
     • Show 1:N (Member to Loan) and M:N (Book to Loan via junction table).
     • Add attributes: BookID (PK), ISBN, Title; MemberID (PK), Name.

3. Explain Conversions:

   • If asked to convert ER to relational schema, show tables + PK/FK constraints.
   • Example:

     Book (BookID, Title, AuthorID)
     Member (MemberID, Name, Email)
     Loan (LoanID, BookID, MemberID, Date)  -- Composite PK
     

4. Real-World Links:

   • Relate your answer to Nepali apps (e.g., "Like Daraz’s Order table, our junction table handles M:N relationships").
   • Use traffic routes as an analogy for 1:N (one road has many intersections).

5. Avoid Common Mistakes:

   • ❌ Drawing M:N directly without a junction table.
   • ❌ Forgetting partial keys for weak entities.
   • ❌ Using arrows instead of Crow’s Foot for relationships.