Object Oriented DB

Question 1

Define the atomic concepts of the OO System and the fundamentals of an object itself.

Answer 1

An OO System can be described as a group of objects that comunicate between themselfs though messages.

An Object itself can be described as an identity containing attributes that describe the state of the “real world” object and it associated actions.
- The attributes describe the state of the object (can be simple or complex, with collections or references).
- The methods define the behavour of the object. Used to change its state and view values inside.
- The messages are the way objects communicate.

Question 2

Explain the relation between classes and objects.

Answer 2

A Class defines the structure and behaviour that share common characterisitics. (Instances of a class).
The relation between classes can be:
- Static: (General) classes organize themself in hierarchies (super/subclasses) with the subclasses gaining the attributes of its superior. (Agregation) Allows to build complex composed/complex objects (being part of).
- Dynamic: Exchange of messages

Question 3

Describe Polimorfism and its types

Answer 3

Polimorfism is the capability of a message being understood in different ways, given the received object.
There are two types:
- Subclasse : when a service defined in a class gets redefined in its subclasses, maintaining its name.
- Overload : using the same name to different services, without any hierarchy (needs binding)

Question 4

Describe the Pros and Cons of the implementation of the OO in SQL

Answer 4

Pros:
- Extensibility : Ability to expand the type system to support new application needs. (new data types for better representation of the application domain && new operations to support the behaviour of the types)
- Expression power : support objects and complex relations.
- Reusability : Compare libraries with existing types.
- Integrations : Relational models and object in a unique language
- New powerfull querys : Recursive, multimedia, etc.

Cons:
- Higher complexity and costs.
- Only usefull in very specific use cases.
- OO purists deny any way of evolution.

Question 5

Define the recent SQL standard parts related directly with the OO extension

Answer 5

• Framework
• Foundation : new data types and constructurs, defined by the user.
• Persistent Stored Modules : Stored Procedures (SQL and other languages).
• Information and Definition Schemas : Defining struture and content of the schema (internal metadatta)

Question 6

How can data types be classified?

Answer 6

• Predefined
• User defined (Distinct, Structured)
• Constructed (Atomic or Composed)

Question 7

Explain the content related to UDT (User-Defined-Type)

Answer 7

UDT can be two different types:

Distinct
- Type based on a predefined type
- Based on the renaming of other type (same representation and values, behave different way, the distinct type is not compatible with the source one)

CREATE TYPE dni AS VARCHAR(9) FINAL;

Structured
- Named UDT composed by a list of attributes (each attribute with the data type and vaue)
- Encapsuled values inside being acessible with its observer function.
- Presence of heritage (Subtype and Supertype)
~~~
CREATE TYPE persona … NOT FINAL

CREATE TYPE universitario UNDER persona … NOT FINAL

CREATE TYPE profesor UNDER universitario … NOT FINAL

CREATE TYPE alumno UNDER universitario … NOT FINAL

CREATE TYPE becario UNDER alumno … FINAL
~~~
- Non-instantiable Structured Types (supertypes that do not allow instancies excluding some of one of its subtypes (can be abstract class)
~~~
CREATE TYPE person AS
(name VARCHAR (30),
address address,
gender CHAR (1))
NOT INSTANTIABLE NOT FINAL
~~~

Question 8

Describe the uses for Structure types

Answer 8

• Column types
  Allow to model attributes of identities which do not exist by their own (coordenates, dates,…)
• Row types
  Model identites with relations and behaviours (employee, department, …)
  A table declared with a structure type as its type is called Typed Table (the colums corrsponds to the name and type of the attributes of the given type (see example))

Question 9

Define BLOB and CLOB

Answer 9

• BLOB = BINARY LARGE OBJECT (for audio, image, video)
• CLOB = CHARACTER LARGE OBJECT (text)

Both native to the DB and can be define with Kb, Mb or Gb

Question 10

How can a Row type (built types) be declared?

Answer 10

A Row Type is a sequence of one or more fields.
~~~
nombre ROW (
n_de_pila VARCHAR2(30),
apellido VARCHAR2(30
)
~~~

Question 11

Define Reference Type and its specs

Answer 11

Data type that contains the value of the attribute REF (referenced type) from a Row of a Type Table.

3 Ways to assign values:
- Generated by the system
~~~
REF IS column SYSTEM GENERATED
~~~
- Generated by the user
~~~
CREATE TYPE properties as (….)
NOT FINAL REF USING INTEGER
~~~
- Derived from a set of attributes
~~~
CREATE TYPE person as (nss INTEGER, name CHAR(20), …)
NOT FINAL REF FROM nss
~~~

Question 12

Explain the term “Reference Types vs Referencial Integrity”

Answer 12

• Referencial integrity require dependency of inclusion.
• Referencial integrity does not support the notion of strongly typed (it can prove that the value is the same but not the type)
• Reference Types, in contrast can have references pointing to nothing (dangling references).
• Reference Types allow to search in different table, excluding the necessity to make JOINS in the relacional model.

Question 13

Define Collections and the classes avaliable

Answer 13

Collections allow to represent multivalue columns (more than 1 value in each cell of the table).
Classes:
- Array
autores VARCHAR (15) ARRAY [20],
- Multiset
actores VARCHAR (15) MULTISET

Question 14

Compare Methods with the rest of the Funtions

Answer 14

CREATE TYPE empleado AS (
id INTEGER,
nombre VARCHAR (20)
salario_base DECIMAL (9,2)
bono DECIMAL (9,2)
INSTANTIABLE NOT FINAL
METHOD salario() RETURNS DECIMAL (9,2));
CREATE METHOD salario(…) FOR empleado
BEGIN
...... RETURN (coste – gastossociales);
END;

The method can be can be original (defined in the supertype) or overriding (defined in a subtype)

CREATE TYPE manager UNDER empleado AS (
stock_option INTEGER)
INSTANTIABLE NOT FINAL
OVERRIDING METHOD salario() RETURNS DECIMAL (9,2),
METHOD empleados_a_cargo() RETURNS INTEGER

Functions are limited to a defined schema and do not have the herance properties method can have.

Question 15

What is the syntax in oracle to write collection types

Answer 15

• Arrays == VARRAY
  CREATE OR REPLACE TYPE telefono AS VARRAY (3) of varchar (10);
• Multiset == NESTED TABLE
  ~~~
  CREATE TYPE Plano AS OBJECT
  (Plano_ID NUMBER,
  Numero_Figuras NUMBER,
  Arquitectos Tipo_Nombre);

CREATE TABLE T_Plano OF Plano (PRIMARY KEY (Plano_Id));

CREATE OR REPLACE TYPE Lista_Planos AS TABLE OF REF Plano;

CREATE TYPE Proyecto AS OBJECT
(Proyecto_Id NUMBER,
Nombre VARCHAR(30),
Tiene_Plano Lista_Planos);

CREATE TABLE T_Proyecto OF Proyecto
(PRIMARY KEY (Proyecto_ID),
UNIQUE (Nombre))
NESTED TABLE Tiene_Plano STORE AS Tabla_Planos;
~~~

Question 16

Nested Tables vs Varray

Answer 16

• Max size
  VARRAY == Yes
  NESTED TABLE == No
• Delete individual elements
  VARRAY == No
  NESTED TABLE == YES
• Data storage
  VARRAY == In-line
  NESTED TABLE == Out-of-line
• Order
  VARRAY == Yes
  NESTED TABLE == No