Composite Types and Object-Oriented Concepts

• Composite Types

  • Definition: Combine multiple properties into one type.

  • Methods, functions, or procedures are associated with each type.

  • Subtypes: Derived from supertypes and inherit all methods and properties from the supertype.

    • Can override or redefine the supertype method's behavior.

  • Class: Represents a composite type.

    • Base Class: Also known as the supertype.

    • Derived Class: Another term for a subtype.

Object-Relational Database Concepts

• Object-Relational Database:

  • Combines the features of object-oriented programming with relational databases.

  • Offers capabilities such as persistence and transaction management.

  • Supports seamless integration with object-oriented languages.

• Object-Relational Mapping (ORM):

  • Software layer that facilitates interaction between programming languages and relational databases.

  • More successful in practice than pure object databases.

Object Types and Subtables

• Object Type Subtable:

  • Extends SQL with object types.

  • Inherits columns and constraints from another table known as a supertable.

  • Inheritance applies to entire tables rather than individual columns.

Defining Object Types in SQL

• CREATE TYPE AS:

  • Used to define an object type along with its associated functions and properties.

• CREATE TABLE OF:

  • Creates a table from an object type, where each row contains a value of the object type.

subtype and NOT FINAL Specification

• CREATE TYPE UNDER:

  • Used to define subtypes.

  • The supertype must specify NOT FINAL to allow the creation of subtypes.

  • The CREATE TYPE AS OBJECT statement must be defined for the supertype.

Example of Object Types

• Address Type:

  • Defined as an object type with fields such as Street, City, State, and PostalCode.

  • Includes a member function FormatAddress that returns a string representation of the address.

  • Specification:

    CREATE TYPE AddressType AS OBJECT (
       Street VARCHAR(30),
       City VARCHAR(20),
       State CHAR(2),
       PostalCode CHAR(5),
       MEMBER FUNCTION FormatAddress RETURN VARCHAR
       NOT FINAL
    );

• Global Address Type:

  • Subtype of Address Type, adds a Country property.

  • Overrides the FormatAddress function to include the country in formatted output.

  • Specification:

    CREATE TYPE GlobalAddressType UNDER AddressType (
       Country VARCHAR(30),
       OVERRIDING MEMBER FUNCTION FormatAddress RETURN VARCHAR
    );

Functions and Total Compensation

• Employee Type:

  • Created as an object type representing an employee with properties like ID, Name, EmailAddress, and SalaryAmount.

  • Includes a function TotalCompensation to return the employee's salary.

  • Specification:

    CREATE TYPE Employee AS OBJECT (
       ID INT,
       Name VARCHAR(30),
       EmailAddress VARCHAR(20),
       SalaryAmount NUMBER(12, 2),
       MEMBER FUNCTION TotalCompensation RETURN NUMBER
       NOT FINAL
    );

• Executive Employee Type:

  • Subtype of Employee that includes a bonus field.

  • Overrides TotalCompensation to calculate total compensation including the salary and bonus.

  • Specification:

    CREATE TYPE ExecutiveEmployee UNDER Employee (
       BonusAmount NUMBER(10,2),
       MEMBER FUNCTION TotalCompensation RETURN NUMBER
    );

Key Concepts Recap:

1. Keywords:

• A: OBJECT

• B: NOT FINAL

• C: UNDER

• D: OVERRIDING

2. Supertype and Subtype:

• Employee is a supertype; ExecutiveEmployee is a subtype.

3. Function Behavior:

• OVERRIDING keyword is necessary for subtypes to redefine method behavior.