Enhanced ER Model: Specialization, Generalization, Aggregation & ODMG

TAKEAWAYS:

• EER extends ER with specialization (IS-A), generalization (subtype/supertype), and aggregation (HAS-A) to model complex real-world hierarchies.
• Specialization constraints (disjoint/overlapping, total/partial) define how entities share attributes and relationships.
• ODMG Object Model integrates OO concepts (classes, inheritance) into databases, with OQL for querying.
• Aggregation vs. Generalization: Aggregation groups entities (e.g., Department HAS-A Professor), while generalization creates IS-A hierarchies (e.g., Vehicle IS-A Car).
• EER-to-relational mapping requires careful handling of discriminators, inheritance, and composite attributes.
• Real-world applications include eSewa’s user hierarchies (e.g., Customer IS-A PremiumCustomer) and NTC’s network aggregation (e.g., Region HAS-A Exchange).

Core Concepts of EER Model

The Enhanced Entity-Relationship (EER) model builds on the classic ER model by adding:

1. Specialization/Generalization (IS-A hierarchies)
2. Aggregation (HAS-A relationships)
3. Categories (entities that can belong to multiple supertypes)
4. Attributes inheritance (shared and unique attributes in hierarchies).

Unlike ER, EER explicitly models semantic constraints (e.g., whether a subtype must belong to a supertype or can belong to multiple).

1. Specialization and Generalization

Generalization is a bottom-up process: combining entities with common attributes into a supertype (e.g., Animal → Mammal, Bird). Specialization is top-down: splitting a supertype into subtypes (e.g., Vehicle → Car, Bike).

Key Constraints

Worked Example: NTC’s Network Hierarchy Nepal Telecom’s (NTC) network can be modeled with specialization:

• Supertype: NetworkNode
  • Subtypes (disjoint, total):
    • Exchange (handles routing)
    • BaseStation (handles mobile signals)
    • DataCenter (handles cloud services)
• Attributes:
  • NetworkNode: nodeID, location
  • Exchange: capacity, connectedRegions
  • BaseStation: frequency, coverageArea

Why? This lets NTC query all nodes uniformly (e.g., SELECT * FROM NetworkNode) while optimizing for each subtype’s needs (e.g., SELECT * FROM Exchange WHERE capacity > 1000).

2. Aggregation

Aggregation models a "HAS-A" relationship where one entity is composed of others (e.g., a Department HAS-A Professor). Unlike generalization (IS-A), aggregation is structural, not hierarchical.

Aggregation vs. Generalization

Worked Example: eSewa’s User Aggregation eSewa’s system aggregates users into groups:

• Supertype: User
  • Aggregated Entities:
    • User HAS-A TransactionHistory
    • User HAS-A SubscriptionPlan (e.g., Basic, Premium)
• Why? This lets eSewa:
  • Query all transactions for a user: SELECT * FROM TransactionHistory WHERE userID = 123.
  • Apply discounts based on SubscriptionPlan: IF plan.type = 'Premium' THEN discount = 0.1.

3. ODMG Object Model

The Object Data Management Group (ODMG) standard integrates object-oriented (OO) concepts into databases:

• Objects: Encapsulate data (attributes) + behavior (methods).
• Classes: Blueprints for objects (e.g., Account class with balance attribute and deposit() method).
• Inheritance: Supports IS-A hierarchies (e.g., SavingsAccount inherits from Account).
• Object Query Language (OQL): SQL-like syntax for querying objects.

ODMG vs. Relational Model

classDiagram
  class Account {
    +String accountNumber
    +double balance
    +deposit(amount)
    +withdraw(amount)
  }
  class SavingsAccount {
    +double interestRate
    +calculateInterest()
  }
  class CurrentAccount {
    +double overdraftLimit
  }
  Account <|-- SavingsAccount : extends
  Account <|-- CurrentAccount : extends
  class Bank {
    +List~Account~ accounts
  }
  Bank "1" *-- "0..*" Account : contains
  caption ODMG inheritance hierarchy with Bank-Account relationship

Worked Example: Ncell’s Billing System Ncell’s billing system uses ODMG-like objects:

• Superclass: Customer
  • Subclasses:
    • PrepaidCustomer (methods: topUp(), checkBalance())
    • PostpaidCustomer (methods: payBill(), checkDueDate())
• Query: Find all prepaid customers with balance < 100:

  SELECT c FROM PrepaidCustomer c WHERE c.balance < 100
  

• Why? This avoids redundant code (e.g., checkBalance() is shared) and enables polymorphic queries.

4. EER to Relational Mapping

Converting EER to relational schema requires handling:

1. Specialization: Use a discriminator attribute (e.g., type) or separate tables with foreign keys.
2. Aggregation: Represent as foreign keys or separate tables.
3. Inheritance: Map to single-table (all attributes in one table) or multiple-table (one table per subtype) inheritance.

Mapping Rules

Worked Example: Daraz Order Processing Daraz’s order system uses specialization for order types:

• Supertype: Order
  • Subtypes (disjoint, total):
    • StandardOrder (attributes: shippingCost)
    • ExpressOrder (attributes: deliveryTime)
• Relational Schema:

  CREATE TABLE Order (
      orderID INT PRIMARY KEY,
      customerID INT,
      orderDate DATE,
      type VARCHAR(20)  -- Discriminator
  );
  CREATE TABLE StandardOrder (
      orderID INT PRIMARY KEY,
      shippingCost DECIMAL,
      FOREIGN KEY (orderID) REFERENCES Order(orderID)
  );
  CREATE TABLE ExpressOrder (
      orderID INT PRIMARY KEY,
      deliveryTime INT,  -- Hours
      FOREIGN KEY (orderID) REFERENCES Order(orderID)
  );
  

• Query: Find all express orders delivered in < 24 hours:

  SELECT * FROM ExpressOrder WHERE deliveryTime < 24;
  

In the Real World

1. eSewa’s User Hierarchy

   • Concept: Specialization (disjoint, total).
   • How? eSewa models users as:
     • User (supertype: userID, name, email)
     • Subtypes: IndividualUser, BusinessUser (disjoint: a user is one or the other).
   • Why? Enables role-based access control (e.g., BusinessUser can register merchants).

2. NTC’s Network Aggregation

   • Concept: Aggregation (HAS-A).
   • How? NTC’s network is modeled as:
     • Region HAS-A Exchange HAS-A BaseStation.
   • Why? Simplifies queries like "Find all base stations in Kathmandu" by traversing the aggregation hierarchy.

3. Nepal Rastra Bank’s Loan System

   • Concept: ODMG-like inheritance.
   • How? Loan types are modeled as:
     • Loan (superclass: loanID, amount, interestRate)
     • Subclasses: PersonalLoan, BusinessLoan, HomeLoan (each with unique attributes/methods).
   • Why? Enables polymorphic operations like calculateEMI() tailored to each loan type.

Exam Tip

1. Diagrams are Mandatory
   • Always draw EER diagrams for design questions. Label:
     • Specialization with △ and constraints (disjoint/overlapping, total/partial).
     • Aggregation with ◇.
   • Example: For a library system, show Member IS-A StudentMember/FacultyMember (disjoint, total) and Library HAS-A Branch (aggregation).

2. Mapping is High-Weightage

   • For EER-to-relational questions:
     • Identify specialization constraints first.
     • Use discriminator columns for disjoint total hierarchies.
     • Use separate tables + intersection tables for overlapping subtypes.
   • Example: A Vehicle hierarchy with Car/Bike (disjoint, total) maps to:

     Vehicle(vehicleID, type, wheels)
     Car(carID, vehicleID, model)
     Bike(bikeID, vehicleID, gearCount)
     

3. ODMG and OQL Tricks

   • Define ODMG’s three-layer architecture (application, object database, storage) in exams.
   • For OQL questions, emphasize:
     • Polymorphic queries: SELECT o FROM Order o WHERE o.type = 'Express'.
     • Method invocation: SELECT a.deposit(500) FROM Account a.

4. Common Pitfalls

   • Confusing aggregation with generalization: Aggregation is structural (e.g., Department HAS-A Professor), while generalization is hierarchical (e.g., Professor IS-A Faculty).
   • Forgetting discriminators: In disjoint total specialization, always include a type column or use separate tables.
   • Overlapping subtypes: Require an intersection table (e.g., StudentScholar for overlapping Student/Scholar).