Lecture Notes: Relational Model Concepts (5.

1)

🔹 1. Introduction to Relational Model

 The Relational Model represents a database as a collection of

relations (tables).

 Each relation:

o Looks like a table of values

o Similar to a flat file (simple row structure)

📌 Key Idea:

 Each row (tuple) → represents a fact

 Each column (attribute) → represents a property

👉 Example:
STUDENT(Name, Student_number, Class, Major)

 Each row = one student

 Each column = student detail

🔹 2. Basic Terminology

Conce
Meaning
pt

Relation Table

Tuple Row

Attribut
Column
e

Data type / set of

Domain
values

🔹 5.1.1 Domains, Attributes, Tuples, Relations

🔸 3. Domain

 A domain is a set of atomic (indivisible) values.

📌 Properties:

 Has:

o Name

o Data type

o Format

o Constraints

📌 Examples:

 Phone numbers → 10-digit numbers

 GPA → Real values (0–4)

 Age → Integer (15–80)

 Department codes → {CS, ECON, PHYS}

🔸 4. Attribute

 An attribute is a column in a relation

 Represents a role played by a domain

👉 Example:

 Name → from domain Names

 Age → from domain Employee_ages

🔸 5. Relation Schema

 Defines the structure of a relation

📌 Notation:

R(A1, A2, A3, …, An)

📌 Example:

STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa)

📌 Key Point:

 Degree (Arity) = Number of attributes

👉 Here, degree = 7

🔸 6. Relation (Instance / State)

 A relation is a set of tuples

📌 Notation:

r(R) = {t1, t2, t3, …, tn}

📌 Tuple:

t = <v1, v2, v3, …, vn>

 Each value belongs to a domain or may be NULL

🔸 7. Mathematical Definition

A relation is a subset of Cartesian product:

r(R) ⊆ dom(A1) × dom(A2) × … × dom(An)

📌 Meaning:

 Contains only valid combinations

 Not all combinations are allowed

🔸 8. Cardinality

 Number of tuples in a relation

📌 Maximum possible tuples:

|dom(A1)| × |dom(A2)| × … × |dom(An)|

🔸 9. Same Domain, Different Roles

 One domain can be used for multiple attributes

👉 Example:

 Home_phone

 Office_phone
(Both use same domain but different meaning)

🔹 5.1.2 Characteristics of Relations

🔸 10. No Ordering of Tuples

 Relations are sets

 Order of rows does NOT matter

👉 Important:

 Sorting does not change relation meaning

🔸 11. Ordering of Attributes

 Attributes are ordered in schema

 But logically, order is not important

🔸 12. Alternative Definition (Mapping)

 Tuple can be represented as:

(Attribute → Value)
👉 Example:

(Name → "John", Age → 20)

✔ Advantage:

 Order not required

 Self-describing data

🔸 13. Atomic Values (First Normal Form – 1NF)

 Each value must be atomic

 No:

o Multivalued attributes

o Composite attributes

👉 Example:
❌ Not allowed:

 Phone = {12345, 67890}

✔ Allowed:

 Separate tuples or relations

🔸 14. NULL Values

📌 Used when:

 Value is unknown

 Value not applicable

 Value missing

👉 Example:

 Office_phone = NULL

⚠️Important:

 NULL ≠ 0

 NULL ≠ empty string

❗ Issues:

 Comparisons with NULL are ambiguous

  Should be minimized in design

🔸 15. Interpretation of Relation

🔹 As Facts:

 Each tuple represents a real-world fact

👉 Example:

 A student’s record

🔹 As Assertion:

 Schema defines a general statement

 Tuple = specific instance

🔹 As Predicate:

 Relation behaves like a logical statement

👉 Example:

STUDENT(Name, Age)

Means:
“Name is a student with Age”

🔹 Closed World Assumption:

 Only stored data is true

 Missing data = false

🔸 16. Entity vs Relationship Representation

 Relations can represent:

o Entities → STUDENT

o Relationships → MAJORS(Student_ssn, Dept_code)

🔹 5.1.3 Relational Model Notation

🔸 17. Standard Notation

Symb
Meaning
ol

Relation
R, S
schema

r, s Relation state

t Tuple

🔸 18. Attribute Notation

R.A

👉 Example:

[Link]

🔸 19. Tuple Representation

t = <v1, v2, v3, …>

🔸 20. Accessing Values

t[Ai] or [Link]

👉 Example:

t[Name] = "Barbara Benson"

🔸 21. Subtuple

t[Ssn, Gpa, Age]

👉 Example:

<533-69-1238, 3.25, 19>

🔹 22. Summary (Quick Revision)

✔ Relation = Table
✔ Tuple = Row
✔ Attribute = Column
✔ Domain = Allowed values
✔ Degree = Number of attributes
✔ Cardinality = Number of tuples
✔ No ordering of tuples
✔ Values must be atomic (1NF)
✔ NULL represents missing/unknown data

🎯 Exam Tips

 Define:

o Domain, Tuple, Attribute, Relation

 Write:

o Mathematical definition

 Explain:

o Characteristics of relations

 Include:

o Examples (STUDENT relation)

 Mention:

o NULL and 1NF

Lecture Notes: 5.2 Relational Model Constraints & Database

Schemas

🔹 1. Introduction

 A relational database consists of multiple relations (tables).

 Data in these relations are interrelated.

 The database state = collection of all relation states at a given

time.

👉 To maintain correctness, we apply constraints (rules) on data.

🔹 2. Types of Constraints

📌 1. Implicit (Model-Based Constraints)

 Inherent in the relational model

 Example:

o No duplicate tuples

o Atomic values (1NF)

📌 2. Explicit (Schema-Based Constraints)

 Defined using DDL (Data Definition Language)

 Enforced by DBMS

👉 Examples:

 Domain constraints

 Key constraints

 Entity integrity

 Referential integrity

📌 3. Application-Based Constraints

 Cannot be defined in schema

 Enforced by application programs

👉 Examples:

 Salary ≤ Manager salary

 Max working hours per week

🔹 3. Domain Constraints

 Each attribute value must belong to its domain

📌 Key Points:

 Domain defines:

o Data type (int, string, float)

o Range (Age: 15–80)

 o Format (Phone number)

👉 Example:

 GPA must be between 0 and 4

🔹 4. Key Constraints

🔸 Superkey

 A set of attributes that uniquely identifies tuples

👉 Example:

 {Ssn}, {Ssn, Name}

🔸 Key (Candidate Key)

 A minimal superkey

 No redundant attributes

✔ Properties:

1. Uniqueness

2. Minimality

🔸 Types of Keys

Type Description

Candidate
All possible keys
Key

Primary Key Selected key

May contain extra

Superkey
attributes

Unique Key Other candidate keys

📌 Example:

 STUDENT(Ssn, Name, Age)

✔ Candidate Key: Ssn
✔ Superkey: {Ssn, Name}
❌ Not a key (not minimal)

🔹 5. Constraints on NULL Values

 Attributes may be:

o NULL allowed

o NOT NULL

👉 Example:

 Name → NOT NULL

🔹 6. Relational Database Schema

📌 Definition:

A Relational Database Schema consists of:

S = {R1, R2, R3, …, Rn} + Integrity Constraints (IC)

📌 Database State:

DB = {r1, r2, r3, …, rn}

 Each ri is a relation state

 Must satisfy all constraints

✔ Valid vs Invalid State:

 Valid → satisfies all constraints

 Invalid → violates constraints

🔹 7. Naming of Attributes

 Same real-world concept → different names possible

o Example:

 Dnumber, Dno, Dnum

 Same name → different meanings possible

🔹 8. Entity Integrity Constraint

📌 Rule:

 Primary key cannot be NULL

📌 Reason:

 Primary key uniquely identifies tuples

👉 Example:

 Ssn must always have a value

🔹 9. Referential Integrity Constraint

📌 Definition:

 Ensures consistency between relations

👉 Rule:

 A foreign key value must:

o Match a primary key in another relation

o OR be NULL

🔹 10. Foreign Key

📌 Definition:

 Attribute in one relation that refers to another relation

📌 Conditions:

1. Same domain as referenced primary key

2. Value must exist in referenced relation

📌 Example:

 EMPLOYEE(Dno) → references DEPARTMENT(Dnumber)

👉 Meaning:

 Employee must belong to an existing department

🔹 Self-Referencing Foreign Key

 Foreign key referencing same relation

👉 Example:

 Super_ssn → references Employee Ssn

🔹 11. Referential Integrity Summary

Term Meaning

Referencing Contains foreign

relation key

Referenced Contains primary

relation key

🔹 12. Integrity Constraints in SQL

 Defined using DDL:

o PRIMARY KEY

o FOREIGN KEY

o UNIQUE

o NOT NULL

🔹 13. Other Constraints

🔸 Semantic Constraints (Business Rules)

 Not defined in schema

👉 Examples:

 Salary ≤ Manager salary

 Max working hours

✔ Enforced by:

 Application programs

 Triggers

 Assertions

🔸 State Constraints

 Conditions on database state

👉 Example:

 Age ≥ 18

🔸 Transition Constraints

 Conditions on state changes

👉 Example:

 Salary can only increase

🔹 14. Data Dependencies (Overview)

 Used in normalization

Types:

 Functional Dependency

 Multivalued Dependency

🔹 15. Summary (Quick Revision)

✔ Constraints ensure data correctness
✔ Three types:

 Implicit

 Explicit

 Application-based

✔ Important constraints:

 Domain constraint

 Key constraint

 Entity integrity

 Referential integrity

✔ Primary key:

 Unique

 NOT NULL

✔ Foreign key:

 Links relations

✔ Database schema:

 Set of relations + constraints

🎯 Exam Tips

 Define:

o Superkey, Key, Primary Key, Foreign Key

 Explain:

o Entity integrity

o Referential integrity

 Provide examples:

o STUDENT / EMPLOYEE relations

 Write:

o Differences between key and superkey

 Include:
 o Types of constraints

Lecture Notes: 5.3 Update Operations & Transactions

🔹 1. Introduction
  Operations in the relational model are classified into:

o Retrieval operations → Fetch data (queries)

o Update operations → Modify data

👉 Update operations change database state

📌 Types of Update Operations:

1. INSERT – Add new tuples

2. DELETE – Remove tuples

3. UPDATE (MODIFY) – Change existing values

⚠️All operations must satisfy integrity constraints

🔹 5.3.1 INSERT Operation

🔸 Definition

 Adds a new tuple to a relation

🔸 Possible Constraint Violations

❌ 1. Domain Constraint

 Value not in domain or wrong data type

👉 Example:

 Age = "ABC" → invalid

❌ 2. Key Constraint

 Duplicate primary key value

👉 Example:

 Same Ssn already exists

❌ 3. Entity Integrity Constraint

 Primary key is NULL

👉 Example:

 Ssn = NULL
❌ 4. Referential Integrity Constraint

 Foreign key references non-existing tuple

👉 Example:

 Dno = 7 but no such department exists

🔸 Actions on Violation

 Reject insertion (default)

 Ask user to:

o Correct value

o Insert missing referenced tuple

o Set value to NULL

🔸 Example Summary

Operation Result

NULL primary Rejecte

key d

Rejecte
Duplicate key
d

Invalid foreign Rejecte

key d

Accepte
Valid tuple
d

🔹 5.3.2 DELETE Operation

🔸 Definition

 Removes tuple(s) from relation

🔸 Possible Violation

❌ Referential Integrity Only

  Occurs when:

o Deleted tuple is referenced by foreign keys

🔸 Example

 Deleting employee referenced in WORKS_ON → violation

🔸 Actions on Violation

✅ 1. RESTRICT (Default)

 Reject deletion

✅ 2. CASCADE

 Delete all related tuples automatically

👉 Example:

 Delete employee → delete related WORKS_ON records

✅ 3. SET NULL / SET DEFAULT

 Set foreign key values to:

o NULL

o Default value

⚠️Cannot set NULL if attribute is part of primary key

🔸 Summary Table

Option Description

Restrict Reject operation

Cascad
Delete dependent tuples
e

Set Replace foreign key with

NULL NULL
🔹 5.3.3 UPDATE Operation

🔸 Definition

 Modifies attribute values in existing tuples

🔸 Types of Updates

✔ Safe Updates

 Non-key, non-foreign key attributes

👉 Example:

 Salary change

❌ Problematic Updates

1. Domain Violation

 Invalid data type

2. Key Violation

 Duplicate primary key

3. Referential Integrity Violation

 Foreign key refers to non-existing tuple

🔸 Special Cases

📌 Updating Primary Key

 Equivalent to:

o DELETE + INSERT

👉 May cause:

 Key violation

 Referential integrity violation

📌 Updating Foreign Key

  Must:

o Match existing primary key

o OR be NULL

🔸 Actions

 Same as DELETE:

o Restrict

o Cascade

o Set NULL

🔹 5.3.4 Transaction Concept

🔸 Definition

 A transaction is a sequence of database operations executed as a

single unit

🔸 Key Properties

✔ Atomicity

 All operations succeed or none

✔ Consistency

 Database must remain valid

✔ Isolation

 Transactions do not interfere

✔ Durability

 Changes are permanent

🔸 Example: Bank Transaction

1. Read balance

2. Check sufficient funds

3. Deduct amount

4. Update database

👉 If any step fails → rollback

🔸 Transaction Characteristics

 Contains:

o Retrieval operations

o Update operations

 Ends in:

o Commit (success)

o Rollback (failure)

🔸 Real-World Usage

 Used in:

o Banking systems

o E-commerce

o OLTP systems

👉 Handles hundreds of transactions per second

🔹 6. Constraint Handling Summary

Operati
Possible Violations
on

Domain, Key, Entity,

INSERT
Referential

DELETE Referential only

UPDATE Domain, Key, Referential

🔹 7. Quick Revision Points ⭐

✔ INSERT → may violate all constraints

✔ DELETE → affects referential integrity
✔ UPDATE → depends on attribute modified
✔ Primary key update = DELETE + INSERT
✔ Foreign key must match existing tuple
✔ Transactions ensure consistency

🎯 Exam Tips

 Define:

o Insert, Delete, Update

 Explain:

o Constraint violations for each

 Write:

o Actions (Restrict, Cascade, Set NULL)

 Define:

o Transaction with example

 Mention:

o ACID properties

Object and Object-Relational Databases

🔹 1. Introduction

 Traditional databases (relational, hierarchical, network) are effective

for business applications.

 However, they struggle with complex data applications such as:

o CAD/CAM systems

o Multimedia databases

o Geographic Information Systems (GIS)

o Scientific and biological data

👉 To address this, Object-Oriented Databases (OODB/ODB) were
developed.

🔹 2. What are Object Databases (ODB)?

 Originally called Object-Oriented Databases (OODB)

 Now commonly referred to as Object Databases (ODB)

📌 Key Idea:

 Store data as objects, similar to object-oriented programming

🔹 3. Need for Object Databases

🔸 Limitations of Traditional Databases:

 Cannot handle:

o Complex structures

o Multimedia data

o Nested objects

o Behavior (methods)

🔸 Motivation:

1. Complex applications requirement

2. Integration with Object-Oriented Programming (OOP)

languages:

o C++

o Java

👉 Traditional databases create a mismatch called:

✔ Impedance mismatch problem

🔹 4. Key Features of Object Databases

✅ 1. Object Identity (OID)

 Each object has a unique identifier

 Independent of its values

✅ 2. Complex Object Structure

 Objects can contain:

o Other objects

o Nested structures

✅ 3. Encapsulation

 Combines:

o Data + Methods (operations)

✅ 4. Methods

 Functions defined within objects

👉 Example:

 Student object → calculate GPA method

✅ 5. Persistence

 Objects can be stored permanently in database

✅ 6. Inheritance

 Classes can inherit properties from other classes

✅ 7. Type and Class Hierarchies

 Supports:

o Generalization

o Specialization

🔹 5. Object-Relational Databases (ORDBMS)

📌 Definition:

 Relational databases extended with object-oriented features

📌 Why ORDBMS?

 Combine:

o Stability of relational model

o Power of object model

📌 Features Added:

 User-defined types (UDTs)

 Inheritance

 Methods

 Complex data types

🔹 6. SQL and Object Features

 Object features introduced in:

o SQL:1999

o Enhanced in SQL:2008

📌 Includes:

 Object types

 Structured types

 Methods in SQL

🔹 7. Relationship with Other Models

🔸 NoSQL Systems

 Also adopt some object-oriented features

🔸 XML Model

 Similar to object model:

o Hierarchical structure

o Nested elements
🔹 8. Object Database Systems

📌 Experimental Systems:

 Orion (MCC)

 OpenOODB (Texas Instruments)

 Iris (HP Labs)

 Ode (AT&T Bell Labs)

 ENCORE/ObServer (Brown University)

📌 Commercial Systems:

 GemStone

 ONTOS

 Objectivity/DB

 Versant

 ObjectStore

 Ardent Database

🔹 9. ODMG Standard

📌 ODMG (Object Data Management Group)

 Developed standard for object databases

📌 Version:

 ODMG 3.0

📌 Components:

Component Description

ODL Object Definition Language

OQL Object Query Language

Language Integration with programming

Component Description

Bindings languages

🔹 10. Object Database Concepts (Core Topics)

🔸 1. Object Identity

 Unique ID for each object

🔸 2. Object Structure

 Complex/nested objects

🔸 3. Encapsulation

 Data + behavior

🔸 4. Methods

 Operations on objects

🔸 5. Persistence

 Permanent storage

🔸 6. Inheritance

 Reuse of properties

🔹 11. Advantages of Object Databases

✔ Handles complex data

✔ Better integration with OOP
✔ Supports multimedia & scientific data
✔ Reduces impedance mismatch

🔹 12. Limitations
❌ Less popular than relational DBMS
❌ Lack of standardization (initially)
❌ Complex implementation
❌ Limited commercial adoption

🔹 13. Comparison

Relational
Feature Object DB
DB

Data Structure Tables Objects

Relationships Foreign keys Object references

Not Supported
Behavior
supported (methods)

Complexity
Limited Strong
Handling

🔹 14. Summary

 Object databases extend traditional models with:

o Objects

o Methods

o Inheritance

 ORDBMS combines relational + object features

 SQL standards now include object-oriented capabilities

 ODMG provides standard for object databases

🎯 Exam Tips

 Define:

o OODB, ORDBMS

 Explain:

o Need for object databases

 List:

o Features (OID, encapsulation, inheritance)

  Compare:

o Relational vs Object DB

 Mention:

o ODMG standard

Lecture Notes: 12.1 Object Database Concepts

🔹 12.1.1 Introduction to Object-Oriented Concepts

📌 Origin of Object-Oriented (OO) Concepts

 Derived from Object-Oriented Programming Languages

(OOPLs)

 Early languages:

o SIMULA (1960s) – foundation of OO

o Smalltalk (1970s) – pure OO language

o C++ – hybrid OO language

📌 Object Definition

An object consists of:

1. State (Attributes / Values)

2. Behavior (Operations / Methods)

👉 Example:
  Object: Employee

o State → Name, Salary

o Behavior → calculateSalary(), promote()

📌 Types of Objects

 Transient Objects → Exist only during program execution

 Persistent Objects → Stored in database and reused later

📌 Key Features of OO Databases

 Store persistent objects

 Support:

o Indexing

o Concurrency control

o Recovery mechanisms

 Integrate with OO programming languages

🔹 12.1.2 Object Identity (OID)

📌 Concept

 Each object has a unique Object Identifier (OID)

📌 Properties of OID

 Unique

 Immutable (never changes)

 Independent of attribute values

📌 OID vs Primary Key

Feature OID Primary Key

Dependen Based on
Independent
cy attributes

Changeabl No Yes
Feature OID Primary Key

Usually
Visibility Visible
hidden

📌 Objects vs Literals

 Objects → Have OID

 Literals → No OID (e.g., 50, "ABC")

🔹 12.1.3 Complex Type Structures

📌 Need

 Represent real-world complex objects in a single structure

📌 Type Constructors

1. Atomic Types (Basic Types)

 Integer, String, Boolean

 Example: Age = 25

2. Struct (Tuple)

 Composite structure

Name = {FirstName, LastName}

3. Collection Types

Type Description Example

Set Unordered, unique {A, B, C}

Unordered, duplicates
Bag {A, A, B}
allowed

List Ordered [A, B, C]

Array Fixed size A[10]

Type Description Example

Dictionar {ID:
Key-value pairs
y Name}

📌 Relationship Representation

 Using Object References (OIDs)

 Example:

o EMPLOYEE → refers to DEPARTMENT using OID

🔹 12.1.4 Encapsulation & Persistence

📌 Encapsulation

 Combines:

o Data (attributes)

o Operations (methods)
📌 Operation Structure

1. Signature (Interface)

o Name + parameters

2. Method (Implementation)

📌 Benefits

 Data hiding

 Independence

 Easy maintenance

📌 Access Mechanism

 Dot notation

o Example: [Link]()
📌 Persistence Mechanisms

1. Naming

 Assign unique name to object

2. Reachability

 Object becomes persistent if reachable from another persistent

object

📌 Extent

 Collection of persistent objects of a class

 Example:
ALL_EMPLOYEES → set(Employee)

🔹 12.1.5 Type Hierarchies & Inheritance

📌 Concept

 New types can be derived from existing types

📌 Example

PERSON: Name, Age

EMPLOYEE: Salary
STUDENT: GPA

👉 Then:

 EMPLOYEE ⊂ PERSON

 STUDENT ⊂ PERSON

📌 Benefits

 Code reuse

 Easy extension

 Logical organization
📌 Example: Geometry

GEOMETRY_OBJECT
├── CIRCLE (Radius)
├── RECTANGLE (Width, Height)
└── TRIANGLE (Sides)

📌 Extent Constraint

 Subtype objects must also belong to supertype extent

🔹 12.1.6 Advanced OO Concepts

📌 1. Polymorphism (Operator Overloading)

 Same operation, different behavior

👉 Example:

 Area():

o Circle → πr²

o Rectangle → l × b

📌 Binding Types

 Early Binding → Compile-time

 Late Binding → Runtime

📌 2. Multiple Inheritance

 Subtype inherits from multiple supertypes

👉 Example:

ENGINEERING_MANAGER = ENGINEER + MANAGER

⚠️Issue: Ambiguity

📌 3. Selective Inheritance

 Inherit only selected attributes/methods

🔹 12.1.7 Summary of Key Concepts

✅ Core Features of Object Databases

1. Object Identity (OID)

2. Complex Type Constructors

3. Encapsulation

4. Persistence (Naming & Reachability)

5. Type Hierarchies & Inheritance

6. Extents (Persistent Collections)

7. Polymorphism

🧠 Quick Revision Points

 Object = State + Behavior

 OID ≠ Primary Key

 Encapsulation = Data + Methods

 Persistence via:

o Naming

o Reachability

 Inheritance enables reuse

 Polymorphism allows flexibility

✍️Exam-Oriented Tips

 Always differentiate OID vs Primary Key

 Use diagrams for inheritance

 Provide examples for polymorphism

 Mention type constructors clearly

Lecture Notes: Object Database Extensions to SQL

🔹 1. Introduction to SQL Extensions

 SQL was originally designed for relational databases.

 Later versions such as SQL:99 introduced object-oriented

features.

 These extensions led to the Object-Relational Model.

 Further enhancements:

o SQL:2003 & SQL:2006 → XML support

o SQL:2008 → advanced object features

🔹 2. Key Object-Oriented Features in SQL

SQL incorporates the following object database concepts:

 Type constructors → ROW, ARRAY, SET, LIST, MULTISET

  Object Identity → using REF types

 Encapsulation → via UDTs and methods

 Inheritance → using UNDER keyword

🔹 3. User-Defined Types (UDTs)

📌 Definition

 UDT allows creation of custom complex data types.

📌 Syntax

CREATE TYPE TYPE_NAME AS (

attribute_name data_type,
...
);

📌 Features

 Can be used:

o As attribute types

o As table types

 Supports nested structures

📌 Example Concept

 STREET_ADDR_TYPE inside USA_ADDR_TYPE

 USA_ADDR_TYPE inside PERSON_TYPE

👉 This enables hierarchical object modeling.

🔹 4. Complex Objects & Collection Types

📌 Collection Types in SQL

 ARRAY

 SET

 LIST

 MULTISET

📌 Example

PHONES ARRAY[4] OF USA_PHONE_TYPE

📌 Important Functions

 CARDINALITY() → returns number of elements

 Access elements using:

 o PHONES[1] → first element

🔹 5. ROW Type (Alternative to UDT)

 Used to define structured attributes directly.

📌 Example

ROW (
NUMBER VARCHAR(5),
STREET_NAME VARCHAR(25)
)

🔹 6. Object Identifiers (OID) Using REF

📌 Concept

 Each object can have a unique identifier.

📌 Syntax

REF IS SYSTEM GENERATED

📌 Options

 SYSTEM GENERATED → automatic unique ID

 DERIVED → based on primary key

📌 Benefit

 Similar to pointers → enables object referencing

🔹 7. Creating Tables from UDTs

📌 Concept

 Tables can be created from UDTs marked as INSTANTIABLE

📌 Key Point

 Non-instantiable UDTs → only used as attribute types

🔹 8. Encapsulation (Methods in UDTs)

📌 Concept

 UDTs can include functions (methods)

📌 Example

INSTANCE METHOD Age() RETURNS INTEGER;

📌 Types of Methods

 Internal (written in SQL)

 External (written in other languages)

📌 Access Levels

 PUBLIC

 PRIVATE

 PROTECTED

📌 Built-in Functions

 Constructor → TYPE_NAME()

 Observer → X.A

 Mutator → updates attribute values

🔹 9. Inheritance in SQL

📌 Concept

 Types can inherit attributes & methods

📌 Syntax

CREATE TYPE STUDENT_TYPE UNDER PERSON_TYPE;

📌 Key Rules

 All attributes are inherited

 Subtypes can override methods

 Subtype instance can act as supertype

📌 Example Hierarchy

 PERSON_TYPE
→ EMPLOYEE_TYPE
→ MANAGER_TYPE
→ STUDENT_TYPE

🔹 10. Table Inheritance

📌 Concept

 Tables can inherit from other tables

📌 Rule

 Tuple in subtable must exist in supertable

📌 Effect

 Insert in subtable → auto insert in supertable

 Supports IS-A relationship

🔹 11. Relationships Using REF

📌 Concept

 REF attribute acts like a foreign key (OID-based)

📌 SCOPE Keyword

 Restricts reference to a specific table

📌 Example Query

SELECT [Link]->NAME
FROM EMPLOYMENT E
WHERE [Link]->COMP_NAME = 'ABCXYZ';

📌 Operators

 . → access attributes

 -> → dereference reference

🔹 12. Advantages of Object Extensions

 Supports complex data modeling

 Reduces impedance mismatch with OOP

 Enables reusability via inheritance

 Improves data abstraction and encapsulation

🔹 13. Summary

Object extensions in SQL transform it from a purely relational system into

a powerful object-relational system by adding:
  UDTs

 Complex data structures

 Object identity

 Encapsulation

 Inheritance

 Reference-based relationships

12.3 The ODMG Object Model and Object Definition Language

(ODL)

🔹 1. Introduction to ODMG Standard

 Lack of standardization slowed adoption of object databases.

 A consortium called Object Data Management Group introduced:

o ODMG-93 (ODMG 1.0)

o ODMG 2.0
 o ODMG 3.0

📌 Components of ODMG Standard

 Object Model

 Object Definition Language (ODL)

 Object Query Language (OQL)

 Language Bindings (C++, Java, etc.)

🔹 2. Overview of ODMG Object Model

📌 Purpose

 Provides a standard data model for object databases

 Equivalent to SQL for relational databases

🔹 3. Objects vs Literals

📌 Objects

 Have:

o Object Identifier (OID)

o State (value)

 Can change over time

📌 Literals

 Have:

o Value only (no OID)

 Immutable (constant)

🔹 4. Characteristics of Objects

Each object has five aspects:

1. Identifier (OID) → Unique system-wide ID

2. Name (optional) → Used to locate objects

3. Lifetime

o Persistent (stored in DB)

 o Transient (temporary in program)

4. Structure

o Atomic (single object)

o Composite (collection/structured)

5. Creation

o Done using new() via factory objects

🔹 5. Types of Literals

📌 1. Atomic Literals

 Basic data types:

o integer, float, boolean, string, char

📌 2. Structured Literals

 Complex values (similar to tuples)

 Examples:

o Date, Time, Timestamp

📌 3. Collection Literals

 Collections without OID

 Types:
 o set<T>, bag<T>, list<T>, array<T>, dictionary<K,V>

🔹 6. Key ODMG Concepts

Conce
Meaning
pt

Interfac Defines behavior

e (operations)

Class Defines state + behavior

Literal Defines state only

🔹 7. Object Operations

All objects inherit basic operations:

 copy() → duplicate object

 delete() → remove object

 same_as() → compare identity

📌 Example

O.same_as(P)
P = [Link]()

🔹 8. Inheritance in ODMG

📌 Types of Inheritance

1. Behavior Inheritance (Interface Inheritance)

 Syntax: :

 Only operations inherited

 Supports multiple inheritance

2. State + Behavior Inheritance

 Syntax: extends

 Attributes + methods inherited

 Only single inheritance allowed

🔹 9. Built-in Interfaces & Collections

📌 Collection Interface Operations

For collection object O:

 [Link]() → number of elements

 O.is_empty() → check empty

 O.insert_element(E)

 O.remove_element(E)

 O.contains_element(E)

📌 Iterator Operations

 [Link]()

 I.next_position()

 I.get_element()

🔹 10. Types of Collections

📌 1. Set

 No duplicates

 Operations:

o union, intersection, difference

📌 2. Bag

 Allows duplicates

📌 3. List

 Ordered collection

 Supports positional operations

📌 4. Array

 Indexed collection

 Fixed or resizable

📌 5. Dictionary

 Key-value pairs <K, V>

 Functions:
 o bind(), lookup(), unbind()

🔹 11. Atomic (User-Defined) Objects

📌 Defined using:

class CLASS_NAME

📌 Components

1. Attributes

 Store values

 Example:

o Name, Ssn, Age

2. Relationships

 Represent links between objects

 Defined using relationship keyword

 Use inverse relationships

3. Operations

 Methods defined in class

 Example:

o add_emp(), reassign_emp()

🔹 12. Relationships in ODMG

 Only binary relationships supported

 Represented as inverse references

📌 Example Concept

 EMPLOYEE → Works_for → DEPARTMENT

 DEPARTMENT → Has_emps → EMPLOYEE

👉 Ensures automatic referential integrity

🔹 13. Extents, Keys, and Factory Objects

📌 Extent

 Collection of all objects of a class

 Example:

o ALL_EMPLOYEES

📌 Key
  Unique identifier within extent

 Can be:

o Single attribute

o Composite key

📌 Factory Object

 Used to create objects

 Method:

new()

🔹 14. Database in ODMG

 Each database:

o Has a name

o Contains persistent objects

📌 Operations

 bind(name, object)

 lookup(name)

 unbind(name)
🔹 15. Object Definition Language (ODL)

📌 Definition

 Schema definition language for object databases

📌 Features

 Language-independent

 Used to define:

o Classes

o Interfaces
👉 Not a programming language

🔹 16. Mapping Concepts in ODL

📌 Entity → Class

📌 Inheritance → extends

📌 Relationships → relationship

🔹 17. Example: UNIVERSITY Database Mapping

📌 Classes

 PERSON

 STUDENT

 FACULTY

 GRAD_STUDENT

📌 Inheritance

 STUDENT extends PERSON

 GRAD_STUDENT extends STUDENT

📌 Special Case

 M:N relationship → mapped as a class (GRADE)

🔹 18. Interfaces in ODL

📌 Example: GeometryObject

 Defines operations:

o area()

o perimeter()

📌 Key Points

 Cannot create objects directly

 Classes implement interfaces

🔹 19. Multiple Inheritance Rules

Type Allowed

Interface
✅ Multiple
inheritance
Type Allowed

❌ Single
Class inheritance
only

🔹 20. Advantages of ODMG Model

 Standardization of ODBMS

 Strong support for:

o Complex data

o Object identity

o Encapsulation

 Seamless integration with OOP languages

🔹 21. Summary

The ODMG object model provides:

 A standard object-oriented database framework

 Clear distinction between:

o Objects, literals, interfaces, and classes

 Advanced features:

o Inheritance

o Collections

o Relationships

o Object creation via factories

12.4 Object Database Conceptual Design

🔹 1. Introduction

 Object Database Design focuses on:

o Structure (data)

o Behavior (operations)

 Uses concepts from:

o Object-Oriented Database Systems

o Relational Database Systems

🔹 2. Differences Between ODB and RDB Design

📌 2.1 Relationship Representation

🟢 In Object Databases (ODB)

 Relationships represented using:

o Reference attributes (OID references)

 Can be:

o Single-valued

o Multi-valued (collections)

 Can be:

o Unidirectional

o Bidirectional (with inverse)

👉 Ensures referential integrity automatically

🔵 In Relational Databases (RDB)

 Relationships represented using:

o Foreign keys (value-based references)

 Always:

o Single-valued

 M:N relationships:

o Require separate relation (table)

⚠️Key Difference

Feature ODB RDB

Reference
OID-based Value-based
Type

Multivalued Allowed Not allowed

Direct or Separate
M:N Mapping
class table

📌 2.2 Relationships with Attributes

Problem in ODB

 Where to store relationship attributes?

o One direction → loss of symmetry

o Both directions → redundancy

Solution

 Create a separate class for the relationship

👉 Similar to relational approach

📌 2.3 Inheritance Handling

🟢 In ODB

 Built-in support:

o extends

o : (interface inheritance)

🔵 In RDB

 No direct support

 Requires:

o Mapping strategies (multiple tables, etc.)

📌 2.4 Operations (Behavior)

🟢 In ODB
  Must be defined during design phase

 Part of class definition

🔵 In RDB

 Defined later:

o Stored procedures

o Triggers

📌 2.5 Philosophical Difference

Aspect ODB RDB

Predefined
Behavior Flexible
(encapsulation)

Ad hoc
Queries Limited (controlled)
queries

Design
Data + Behavior Data only
Focus

🔹 3. Mapping EER Schema to ODB Schema

 Converts:

o EER Diagram → ODL Classes

 Focus:

o Structure first

o Then add operations

🔹 4. Mapping Algorithm (Step-by-Step)

✅ Step 1: Map Entity Types → Classes

 Create one ODL class per entity/subclass

📌 Attribute Mapping

 Single-valued → normal attributes

 Multivalued →
 o set<T> → no duplicates

o bag<T> → duplicates allowed

o list<T> → ordered

 Composite → struct

📌 Additional Tasks

 Define:

o Extent

o Keys

✅ Step 2: Map Binary Relationships

📌 Add References

 As:

o Relationship properties

o OR reference attributes

📌 Direction Options

 One-way

 Two-way (with inverse)

📌 Based on Cardinality

Relations
Mapping
hip

1:1 Single reference

N:1 Single reference

1:N Collection

Collection OR separate
M:N
class

📌 With Relationship Attributes

 Use:
struct<reference, attributes>

⚠️Limitation:

 Cannot enforce inverse constraint

 May create redundancy

✅ Step 3: Add Operations

 Not present in EER → must be added manually

📌 Types of Methods

 Constructor → object creation

 Destructor → object deletion

 Other methods → enforce constraints

✅ Step 4: Map Subclasses (Inheritance)

 Use:

extends

📌 Result

 Subclass inherits:

o Attributes

o Relationships

o Methods

✅ Step 5: Map Weak Entity Types

Option 1

 Map as normal class

Option 2 (Alternative)

 Map as:

set<struct<...>>

👉 Treated as multivalued composite attribute

✅ Step 6: Map Categories (Union Types)

📌 Challenge

 No direct mapping in ODL

📌 Solution

 Create:

o A new class

o 1:1 relationships with superclasses

✅ Step 7: Map n-ary Relationships

📌 For n > 2

 Create separate class

📌 Include:

 References to all participating classes

📌 M:N Relationships

 Two options:

1. Collection-based mapping

2. Separate class (preferred if attributes exist)

🔹 5. Summary of Mapping

EER
ODB Mapping
Component

Entity Class

Attribute Attribute / struct

Multivalued set / list / bag

Reference /
Relationship
relationship

M:N Class or collection

Weak Entity Class or struct

EER
ODB Mapping
Component

Inheritance extends

Class +
Category
relationships

🔹 6. Key Advantages of ODB Design

 Direct support for:

o Complex objects

o Inheritance

o Relationships

 Better alignment with:

o Object-oriented programming

 Integrates:

o Data + behavior

🔹 7. Final Summary

Object database conceptual design:

 Extends EER modeling with object-oriented features

 Uses:

o Classes instead of tables

o References instead of foreign keys

 Requires:

o Early definition of operations

 Provides:

o Rich modeling for real-world applications

12.5 Object Query Language (OQL)

🔹 1. Introduction to OQL

 Object Query Language is the query language for the ODMG

object model.

 Designed to work with:

o C++

o Java

o Smalltalk
  Similar to SQL, but extended for object-oriented features.

🔹 2. Features of OQL

 Supports:

o Object identity

o Complex objects

o Relationships

o Inheritance

o Polymorphism

o Methods (operations)

🔹 3. Basic OQL Query Structure

📌 Syntax

SELECT ...
FROM ...
WHERE ...

📌 Example

SELECT [Link]
FROM D IN DEPARTMENTS
WHERE [Link] = 'Engineering';

📌 Key Points

 Similar to SQL

 Returns:

o bag (default)

o set (with distinct)

🔹 4. Database Entry Points

📌 Definition

 A named persistent object used to start a query

📌 Examples
  DEPARTMENTS → set<DEPARTMENT>

 STUDENTS → set<STUDENT>

🔹 5. Iterator Variables

📌 Purpose

 Used to traverse collections

📌 Syntax Options

D IN DEPARTMENTS
DEPARTMENTS D
DEPARTMENTS AS D

🔹 6. Query Results

 Can return:

o Single object

o Collection

o Complex structures

📌 Example

DEPARTMENTS;

➡ Returns: set<DEPARTMENT>

🔹 7. Path Expressions

📌 Concept

 Used to navigate object relationships

📌 Syntax

[Link]

📌 Examples

 CS_DEPARTMENT.Chair

 CS_DEPARTMENT.[Link]

 CS_DEPARTMENT.Has_faculty
⚠️Important Rule

 Cannot directly apply attributes to collections

❌ Invalid:

CS_DEPARTMENT.Has_faculty.Rank

✔ Correct:

SELECT [Link]
FROM F IN CS_DEPARTMENT.Has_faculty;

🔹 8. Using DISTINCT

SELECT DISTINCT [Link]

FROM F IN CS_DEPARTMENT.Has_faculty;

 Removes duplicates

 Result becomes set

🔹 9. Complex Query Results (STRUCT)

📌 Example

SELECT STRUCT(
name: STRUCT(
last_name: [Link],
first_name: [Link]
),
degrees: (
SELECT STRUCT(
deg: [Link],
yr: [Link],
college: [Link]
)
FROM D IN [Link]
)
)
FROM S IN CS_DEPARTMENT.[Link];

📌 Key Idea

 Supports nested structures

 Can return multi-level objects

🔹 10. ORDER BY Clause

ORDER BY gpa DESC, last_name ASC;

 Converts result into a list

🔹 11. Named Queries (Views)

📌 Syntax

DEFINE Has_minors(Dept_name) AS
SELECT S
FROM S IN STUDENTS
WHERE S.Minors_in.Dname = Dept_name;

📌 Usage

Has_minors('Computer Science');

🔹 12. Element Operator

📌 Purpose

 Extract single value from collection

ELEMENT(
SELECT D
FROM D IN DEPARTMENTS
WHERE [Link] = 'Computer Science'
);

🔹 13. Aggregate Functions

📌 Functions

 count()

 sum()

 avg()

 min()

 max()

📌 Example
COUNT(S IN Has_minors('Computer Science'));

🔹 14. Membership & Quantifiers

📌 Membership

'ELEMENT' IN collection

📌 Universal Quantifier

FOR ALL V IN C : condition

📌 Existential Quantifier

EXISTS V IN C : condition

📌 Example

EXISTS G IN GRAD_STUDENTS :
[Link] = 4;

🔹 15. Ordered Collection Operations

📌 Example: Highest Salary

FIRST(
SELECT STRUCT(facname: [Link], salary: [Link])
FROM F IN FACULTY
ORDER BY salary DESC
);

📌 Example: Top 3 Students

(SELECT ...
FROM S IN CS_DEPARTMENT.Has_majors
ORDER BY gpa DESC)[0:2];

🔹 16. Group By Clause

📌 Syntax

GROUP BY attr: expression

📌 Example
SELECT STRUCT(
dept_name,
number_of_majors: COUNT(partition)
)
FROM S IN STUDENTS
GROUP BY dept_name: S.Majors_in.Dname;

🔹 17. HAVING Clause

HAVING COUNT(partition) > 100;

📌 Example with AVG

SELECT dept_name,
avg_gpa: AVG(
SELECT [Link] FROM P IN partition
)
FROM S IN STUDENTS
GROUP BY dept_name: S.Majors_in.Dname
HAVING COUNT(partition) > 100;

🔹 18. Key Characteristics of OQL

 Object-oriented querying

 Supports:

o Complex/nested results

o Path traversal

o Method calls

 Strong integration with programming languages

🔹 19. Advantages of OQL

 Handles complex objects easily

 Supports inheritance and polymorphism

 More expressive than SQL for object databases

 Seamless integration with OOP

🔹 20. Summary

OQL is a powerful query language that:

 Extends SQL for object databases

 Uses:

o Path expressions

o Iterator variables

 Supports:

o Complex structures

o Aggregation

o Grouping

o Views
12.6 Overview of the C++ Language Binding in ODMG

🔹 1. Introduction

 The C++ binding in ODMG defines how:

o ODL (Object Definition Language)

o OQL concepts
are implemented in C++ programs.

 Goal:
👉 Make developers feel like they are using one unified language,
not separate DB + programming languages.

🔹 2. Key Components of C++ Binding

📌 1. Class Library

 Provides predefined classes for:

o Objects

o Collections

o Iterators

📌 2. Object Manipulation Language (OML)

 Used to:

o Retrieve objects

o Manipulate objects

 Based on C++ syntax

📌 3. Physical Pragmas

 Provide control over:

o Storage

o Indexing

o Memory management

o Object clustering

🔹 3. Naming Convention

 All ODMG-related classes use prefix:

d_

👉 Example:

 d_Object

 d_Set

🔹 4. Object Reference Handling

📌 Class:

D_Ref<T>

📌 Purpose:

 Represents reference to object of type T

📌 Features:

 Can refer to:

o Persistent objects

o Transient objects

🔹 5. Base Classes

📌 1. Object Class

D_Object<T>

 Defines operations for all objects

📌 2. Collection Class
D_Collection<T>

 Defines operations for collections

👉 Both are:

 Abstract classes

 Not instantiable

🔹 6. Collection Classes

ODMG Type C++ Class

set<T> D_Set<T>

list<T> D_List<T>

bag<T> D_Bag<T>

array<T> D_Varray<T>

dictionary<K, D_Dictionary<
V> T>

📌 Example

D_Set<D_Ref<STUDENT>>

➡ Set of references to STUDENT objects

D_Set<string>

➡ Set of strings

🔹 7. Iterator Support

📌 Class:

d_Iterator

 Used to traverse collections

🔹 8. Data Types in C++ Binding

📌 Basic Types
 C++
ODMG Type
Binding

short d_Short

unsigned
d_Ushort
short

long d_Long

float d_Float

📌 Structured Types

 d_String

 d_Date

 d_Time

 d_Timestamp

 d_Interval

🔹 9. Relationship Representation

📌 Syntax Example

d_Rel_Ref<DEPARTMENT, Has_majors> Majors_in;

d_Rel_Set<STUDENT, Majors_in> Has_majors;

📌 Explanation

 Defines:

o Bidirectional relationship

 Majors_in and Has_majors are:

o Inverse relationships

🔹 10. Object Creation (OML)

📌 Using new operator

D_Ref<STUDENT> S = new(DB1, "John_Smith") STUDENT;

📌 Meaning

 Creates:
 o Persistent object

o In database: DB1

o Name: "John_Smith"

🔹 11. Object Deletion

📌 Syntax

delete_object();

🔹 12. Object Modification

 Done using:

o Class methods (operations)

🔹 13. Extents in C++ Binding

📌 Class:

d_Extent<T>

📌 Example

D_Extent<PERSON> ALL_PERSONS(DB1);

📌 Meaning

 Creates:

o Collection of PERSON objects

 Type:

D_Set<PERSON>

🔹 14. Limitations of C++ Binding

⚠️1. No Key Constraint Support

 Must be handled manually in code

⚠️2. No Automatic Persistence via Reachability

 Objects must be explicitly declared persistent

🔹 15. Advantages of C++ Binding

 Seamless integration of:

o Database + programming

 Strong type safety

 Supports:

o Object identity

o Complex data structures

o Collections

🔹 16. Summary

The ODMG C++ binding:

 Maps ODL constructs → C++ classes

 Provides:

o Object references (D_Ref<T>)

o Collections (D_Set<T>, etc.)

o Iterators

 Enables:

o Object creation, deletion, manipulation

 Requires:

o Manual handling of constraints