Big Data & Specialization/Generalization in ER Model

TAKEAWAYS:

• Big Data is defined by 4Vs (Volume, Velocity, Variety, Veracity) and requires distributed systems (e.g., Hadoop, Spark) for processing.
• Specialization splits a superclass into subclasses (e.g., Employee → Professor, Admin), while Generalization merges subclasses into a superclass.
• Constraints like disjointness (exclusive vs. overlapping) and completeness (total vs. partial) define how subclasses relate to their superclass.
• SQL implements specialization using inheritance (via CHECK constraints or separate tables with foreign keys).
• Real-world examples: eSewa uses Big Data for fraud detection; Ncell applies specialization to classify customers (prepaid/postpaid).

1. Big Data: Definition and Characteristics

Big Data refers to extremely large datasets that traditional databases struggle to process efficiently. It is characterized by the 4Vs:

Why is Big Data Important?

• Business Intelligence: Companies like Daraz use Big Data to predict demand and optimize inventory.
• Fraud Detection: Khalti analyzes transaction patterns to flag suspicious activities.
• Personalization: YouTube recommends videos based on user behavior (clicks, watch time).

Technologies for Big Data

• Hadoop: Distributed storage (HDFS) and processing (MapReduce).
• Spark: In-memory processing for faster analytics.
• NoSQL Databases: MongoDB (document-based), Cassandra (column-family).

2. Specialization and Generalization in ER Model

These concepts help model inheritance hierarchies in databases.

erDiagram
    PERSON ||--o{ STUDENT : "is_a"
    PERSON ||--o{ EMPLOYEE : "is_a"
    EMPLOYEE ||--|| PROFESSOR : "specializes_as" {disjoint total}
    EMPLOYEE ||--|| ADMIN : "specializes_as" {disjoint total}
    STUDENT ||--o{ UG_STUDENT : "specializes_as" {disjoint partial}
    STUDENT ||--o{ PG_STUDENT : "specializes_as" {disjoint partial}
    STUDENT ||--o{ RESEARCH_STUDENT : "specializes_as" {overlapping partial}
    STUDENT {string name, int id PK}
    EMPLOYEE {string name, int id PK}
    PROFESSOR {string research_area}
    ADMIN {string department}
    UG_STUDENT {int semester}
    PG_STUDENT {string thesis_topic}
    RESEARCH_STUDENT {string supervisor}

Key Definitions

• Generalization: Bottom-up approach where subclasses (e.g., Professor, Admin) are merged into a superclass (e.g., Employee).
• Specialization: Top-down approach where a superclass is divided into subclasses.

Constraints

ER Diagram Example: University Database

erDiagram
    PERSON ||--o{ STUDENT : "is_a"
    PERSON ||--o{ EMPLOYEE : "is_a"
    EMPLOYEE ||--|| PROFESSOR : "specializes_as"
    EMPLOYEE ||--|| ADMIN : "specializes_as"
    STUDENT ||--o{ UG_STUDENT : "specializes_as"
    STUDENT ||--o{ PG_STUDENT : "specializes_as"

Explanation:

• PERSON is the superclass for STUDENT and EMPLOYEE.
• EMPLOYEE is specialized into PROFESSOR and ADMIN (disjoint, total).
• STUDENT is specialized into UG_STUDENT and PG_STUDENT (disjoint, partial if some students are not classified yet).

3. SQL Implementation of Specialization

SQL does not natively support inheritance, but we can model it using:

1. Single Table with CHECK Constraints:

   CREATE TABLE EMPLOYEE (
       EID INT PRIMARY KEY,
       Name VARCHAR(50),
       Type VARCHAR(10) CHECK (Type IN ('Professor', 'Admin')),
       Salary DECIMAL(10,2)
   );
   

   • Limitation: Mixes attributes of subclasses (e.g., Professor has Research_Area, Admin has Department).

2. Separate Tables with Foreign Key:

   CREATE TABLE EMPLOYEE (
       EID INT PRIMARY KEY,
       Name VARCHAR(50)
   );
   
   CREATE TABLE PROFESSOR (
       EID INT PRIMARY KEY REFERENCES EMPLOYEE(EID),
       Research_Area VARCHAR(50)
   );
   
   CREATE TABLE ADMIN (
       EID INT PRIMARY KEY REFERENCES EMPLOYEE(EID),
       Department VARCHAR(50)
   );
   

   • Advantage: Clean separation of attributes.

4. Real-World Applications

Example 1: eSewa Transaction Processing (Big Data)

• Problem: eSewa handles millions of transactions/day (high velocity, variety: payments, bill splits, loans).
• Solution:
  • Uses Hadoop to store raw transaction logs.
  • Spark processes data in real-time to detect fraud (e.g., sudden large transactions from a new device).
  • Specialization: Transactions are classified into:
    • Payment (subclass of Transaction with Amount, Receiver).
    • Loan (subclass with Interest_Rate, Due_Date).

Example 2: Ncell Customer Segmentation (Specialization)

• Problem: Ncell needs to classify customers for targeted promotions.
• Solution:
  • Superclass: Customer (attributes: CID, Name, Phone_Number).
  • Subclasses:
    • Prepaid_Customer (attributes: Data_Package, Validity).
    • Postpaid_Customer (attributes: Monthly_Bill, Contract_End_Date).
  • SQL Implementation:

    CREATE TABLE CUSTOMER (
        CID INT PRIMARY KEY,
        Name VARCHAR(50),
        Phone_Number VARCHAR(15)
    );
    
    CREATE TABLE PREPAID (
        CID INT PRIMARY KEY REFERENCES CUSTOMER(CID),
        Data_Package VARCHAR(20)
    );
    
    CREATE TABLE POSTPAID (
        CID INT PRIMARY KEY REFERENCES CUSTOMER(CID),
        Monthly_Bill DECIMAL(10,2)
    );
    

Example 3: Daraz Inventory Management (Big Data + Specialization)

• Problem: Daraz sells millions of products with varying demand patterns.
• Solution:
  • Big Data: Uses Apache Kafka to stream sales data and Spark for demand forecasting.
  • Specialization:
    • Product (superclass: PID, Name, Price).
    • Subclasses:
      • Electronics (attributes: Warranty, Brand).
      • Fashion (attributes: Size, Material).

5. Comparison: Specialization vs. Generalization

6. Advantages and Disadvantages

Specialization/Generalization

Big Data Technologies

Exam Tip

1. Big Data:

   • Always define the 4Vs and give one real-world example (e.g., Ncell call logs, YouTube recommendations).
   • For SQL questions, focus on NoSQL databases (MongoDB, Cassandra) as solutions for Big Data.

2. Specialization/Generalization:

   • Draw an ER diagram with constraints (disjoint/overlapping, total/partial).
   • In SQL, prefer separate tables with foreign keys over CHECK constraints for clarity.
   • Common exam pitfalls:
     • Forgetting to label constraints in ER diagrams.
     • Mixing up disjoint/overlapping or total/partial definitions.

3. Worked Example (Past Exam Style): Question: Design an ER diagram for a hospital with Doctor, Nurse, and Patient where:

   • All staff (Doctor, Nurse) are Employee.
   • A Patient can be treated by multiple Doctors.
   • A Doctor can treat multiple Patients.

   Answer:

   erDiagram
       PERSON ||--o{ EMPLOYEE : "is_a"
       PERSON ||--o{ PATIENT : "is_a"
       EMPLOYEE ||--|| DOCTOR : "specializes_as"
       EMPLOYEE ||--|| NURSE : "specializes_as"
       DOCTOR ||--o{ TREATMENT : "treats"
       PATIENT ||--o{ TREATMENT : "receives"

   Constraints:

   • DOCTOR and NURSE are disjoint (a person cannot be both).
   • DOCTOR → EMPLOYEE is total (all doctors are employees).

In the real world

• eSewa: Uses Big Data (Volume, Velocity) to process millions of transactions/day via Hadoop/Spark, while specializing transactions into Payment, Loan, and BillSplit subclasses to apply fraud detection rules (e.g., Loan checks for Interest_Rate anomalies).
• Ncell: Applies specialization to classify customers into Prepaid_Customer (with Data_Package attribute) and Postpaid_Customer (with Monthly_Bill attribute) for targeted promotions, storing data in relational tables with foreign keys.
• Daraz: Combines Big Data analytics (Apache Kafka for real-time sales streams) with generalization (all products inherit from Product superclass) to forecast demand and optimize inventory across categories like electronics, groceries, and fashion.