Object-Oriented & Object-Relational Databases: Models, OQL, and Persistence

TAKEAWAYS:

• OODBs vs ORDBs: OODBs store objects natively (e.g., WhatsApp’s message threads), while ORDBs extend SQL with object features (e.g., YouTube’s video metadata).
• ODMG Model: Defines object types, literals, and structures (e.g., Person with attributes name and address) using type constructors like SET, BAG, and LIST.
• OQL: A query language for objects, similar to SQL but with path expressions (e.g., SELECT p.name FROM Person p WHERE p.age > 30).
• Persistence: Objects become persistent via naming (e.g., oid) or reachability (e.g., linked from a root object).
• Real-World Use: Banks use ORDBs for loan hierarchies (e.g., HomeLoan inheriting from Loan), while eSewa uses OODBs for transaction graphs.
• Exam Focus: Compare models, write OQL queries, and design EER diagrams with inheritance constraints.

1. Why Traditional Relational Databases Fall Short

Relational databases (RDBMS) excel at tabular data but struggle with:

• Complex objects: A Car with parts (Engine, Wheel), nested structures, or recursive relationships (e.g., a Comment tree in YouTube replies).
• Inheritance: Modeling Employee subclasses (Manager, Intern) without redundant tables.
• Multimedia: Storing images (e.g., Daraz product photos) or geospatial data (e.g., Pathao driver locations) as binary blobs.

2. Object-Oriented Databases (OODBs): Native Object Storage

OODBs store data as objects (instances of classes) with:

• Encapsulation: Data + methods (e.g., a BankAccount object has deposit() and withdraw()).
• Inheritance: Subclasses reuse parent attributes/methods (e.g., SavingsAccount inherits from Account).
• Identity: Objects have unique oids (object identifiers), unlike RDBMS tuples identified by key values.

Key Features

Mermaid Diagram: OODB Class Hierarchy

Example: Ncell’s customer database uses OODBs to model Customer → PrepaidUser/PostpaidUser hierarchies, with methods like calculateBill().

3. Object-Relational Databases (ORDBs): SQL with Object Extensions

ORDBs (e.g., PostgreSQL, Oracle) extend SQL to handle objects while keeping relational features:

• User-Defined Types (UDTs): Custom data types (e.g., Point for GPS coordinates).
• Inheritance: Table inheritance (e.g., Animal → Dog, Cat).
• Collections: Arrays, LIST, SET (e.g., tags for a blog post).

PostgreSQL Example: Storing Geospatial Data

CREATE TYPE Point AS (x double precision, y double precision);
CREATE TABLE Location (
    id SERIAL PRIMARY KEY,
    name VARCHAR,
    coords Point
);
-- Query: Find locations near Kathmandu (27.7172° N, 85.3240° E)
SELECT name FROM Location
WHERE coords <-> '(27.7172, 85.3240)' < 10;  -- Within 10km

4. ODMG Object Model: The Standard

The Object Data Management Group (ODMG) defines a standard for OODBs with:

• Object Types: Classes with attributes and methods (e.g., Book with title and borrow()).
• Literals: Primitive values (INT, STRING, BOOLEAN).
• Type Constructors:
  • SET: Unordered collection (e.g., SET<Author> for a book).
  • BAG: Ordered with duplicates (e.g., BAG<Order> for a customer).
  • LIST: Ordered sequence (e.g., LIST<Comment> for a YouTube video).

Mermaid Diagram: ODMG Type Constructors

Example: eSewa’s transaction system uses SET<Payment> to track all methods (Khalti, credit card) for a single bill.

5. Object Query Language (OQL)

OQL is to OODBs what SQL is to RDBMS. Key features:

• Path Expressions: Navigate object graphs (e.g., book.authors.name).
• Polymorphism: Query across inheritance hierarchies.
• Collection Operations: Filter, project, and aggregate collections.

OQL Examples

1. Basic Query:

   SELECT p.name FROM Person p WHERE p.age > 30
   

2. Nested Query:

   SELECT b.title FROM Book b
   WHERE SIZE(b.authors) > 1  -- Books with multiple authors
   

3. Path Expression:

   SELECT o.customer.name FROM Order o
   WHERE o.status = "Shipped"
   

Real-World Tie-In: NEPSE’s stock data uses OQL to query:

SELECT s.company.name, s.price
FROM Stock s
WHERE s.price > AVG(s.price) AND s.company.sector = "Tech"

6. Persistence in OODBs: Naming vs. Reachability

Objects become persistent (saved to disk) via:

1. Naming:
   • Objects have object identifiers (oid) (e.g., oid Book#123).
   • Example: A Library object’s oid is stored in the database root.
2. Reachability:
   • Objects are persistent if reachable from a root object (e.g., a Customer linked to an Order).

Mermaid Diagram: Persistence via Reachability

graph LR
  Root["Root Object"] -->|"oid stored"| Persistent["Persistent Object"]
  Persistent -->|"linked to root"| Transient["Transient Object"]
  Transient -->|"not reachable"| Lost["Lost Object"]

Persistence via reachability (root objects anchor persistence) Example: Daraz’s order system makes an Order persistent when it’s linked to a Customer (root object).

7. OODBs vs. ORDBs: Comparison Table

8. Real-World Applications

1. eSewa: Transaction Graphs as OODBs

• Idea Used: Object graphs (transactions linked to users, merchants, and payments).
• How:
  • Each Transaction is an object with methods like verify() and refund().
  • Inheritance: OnlinePayment → KhaltiPayment, CreditCardPayment.
  • Query: SELECT t.amount FROM Transaction t WHERE t.status = "Failed".

2. Ncell: Customer Hierarchies as ORDBs

• Idea Used: Table inheritance for Customer → PrepaidUser/PostpaidUser.
• How:
  • PostgreSQL tables:

    CREATE TABLE Customer (id SERIAL PRIMARY KEY, name VARCHAR);
    CREATE TABLE PrepaidUser UNDER Customer (credit_balance DECIMAL);
    

  • Query: SELECT name FROM ONLY PrepaidUser WHERE credit_balance < 100.

3. Pathao: Geospatial ORDBs

• Idea Used: PostGIS (PostgreSQL spatial extension) for driver locations.
• How:
  • Driver table has a Point type for GPS coordinates.
  • Query: Find drivers within 500m of a pickup location using <-> distance operator.

9. Worked Example: Library Management System (EER + OQL)

Scenario: Design a system for a university library with:

• Books (fiction/non-fiction), authors, and loans.
• Constraints:
  • Fiction and NonFiction are disjoint subclasses of Book.
  • Loan has a total participation with Book (every book can be loaned).

EER Diagram (Mermaid)

erDiagram
    Person ||--o{ Loan : "borrows"
    Loan ||--|| Book : "loans"
    Book ||--|{ Author : "written_by"
    Book }|--|| Fiction : "is_a"
    Book }|--|| NonFiction : "is_a"
    Author {
        string id PK
        string name
    }
    Book {
        string isbn PK
        string title
        int year
    }
    Loan {
        string loan_id PK
        date due_date
    }

OQL Queries:

1. Find all fiction books by Nepali authors:

   SELECT b.title FROM Book b, Author a
   WHERE b IN TYPE(Fiction) AND a.name LIKE "%Nepali%" AND b.written_by IN a
   

2. List overdue loans:

   SELECT l.loan_id, p.name, b.title
   FROM Loan l, Person p, Book b
   WHERE l.due_date < CURRENT_DATE AND l.borrower = p AND l.book = b
   

10. Exam Tip: How to Score Full Marks

1. Definitions:

   • OODB: "A database that stores data as objects (instances of classes) with identity, encapsulation, and inheritance."
   • ORDB: "A relational database extended with object features like UDTs, inheritance, and collections."
   • OQL: "A query language for object databases, supporting path expressions and polymorphism."

2. Comparisons:

   • Use the table above to compare OODB vs. ORDB vs. RDBMS. Highlight inheritance, query language, and use cases.

3. Diagrams:

   • Always draw:
     • Class hierarchies (for inheritance).
     • EER diagrams (for specialization/generalization).
     • Mermaid state diagrams for persistence (naming/reachability).

4. OQL Queries:

   • Practice writing 3 types:
     • Basic SELECT-FROM-WHERE.
     • Nested queries (e.g., WHERE SIZE(collection) > X).
     • Path expressions (e.g., object.attribute.method).

5. Real-World Links:

   • eSewa: Object graphs for transactions.
   • Ncell: Table inheritance for customer types.
   • Pathao: Geospatial queries for driver locations.

6. Common Pitfalls:

   • Avoid: Saying OODBs "replace SQL." Instead, say they complement it for complex data.
   • Avoid: Forgetting disjoint/overlapping constraints in EER diagrams. Label them explicitly!

11. Quick Revision Checklist

• Can you list 3 type constructors in ODMG? (SET, BAG, LIST).
• Write an OQL query with a path expression.
• Draw an EER diagram with total/partial participation and disjoint/overlapping subclasses.
• Compare OODB vs. ORDB in a table (3 features each).
• Name 2 Nepali companies using ORDBs/OODBs (e.g., eSewa, Ncell).