2. Objet-Relational Databases

1. OR Databases

In this section we will work with PostgreSQL database. As the open source relational object database management system, which has had the greatest impact, being a direct rival to the great Oracle.

We will focus on the novelties that Postgres offers versus MySQL, as an extension of first year concepts.

Important

It will be interesting to:

• Install your own PostgreSQL server. It's recommended to use it via Docker container
• Install a client to show your database. We recommend DBeaver.

We will work with this well know sample:

{width=90%}

2. Defining types

In PostgreSQL the usual types found in modern DBMS exists. In addition, special types stand out for storing Internet addresses (Network Address Types), XML and JSON for saving and processing said formats, own types and collections. With this, we can look at the great advances of PostgreSQL, which in a daring we could consider as Object-Relational and Documentary

2.1. Enumeration

Enumerated types are types that allow only specific values, usually also known as domains. In PostgreSQL we can create it in the following way:

CREATE TYPE enum_name AS ENUM
    ( [ 'valor' [, ... ] ] );

for instance:

create type basic_colors as enum('#FF0000','#00FF00','#0000FF');
create type street_kind as enum ('Calle','Avenida','Partida');

This way of defining this type will avoid the value checks (CHECK clauses) existing in some relational DBMS.

2.2. Structured types

Structured types are the precursors of the objects themselves. If we remember, in structured programming, from the basic types, we could create data structures:

• Where all the elements are the same (vectors, arrays and collections)
• Where their elements could be of different types. These structures evolved into current objects by adding behavior and other features to them.

In the relational model, since we must respect the atomicity of the data, we could not generate such structures. In Postgres we can create these new structured data types with the following syntax, much like creating a table:

CREATE TYPE type_name AS(
  attribute_name data_type
  [, ... ]-- one or more
);

for instance:

create type Punto as(
    x integer,
    Y integer 
);

or, and using previous enumeration:

create type Address as(
    Type street_kind,
    street varchar,
    number int
);

and finally create the following table:

create table person(
    idPerson serial primary key,
    name varchar,
    direction Address
);

insert into person(name) values ('joange');
insert into person(name,address) values('joange',null);
insert into person(name,address) values('joange',('Street','Calvary',1));

select direction from person;
select (address).street from person;

Note

• No NOT NULL, DEFAULT or CHECK restrictions can be applied to the fields that belong to the created types.
• Typing makes sense on data that does not exist by itself, that needs to be embedded in other structures or tables.
• When it is used inside a table and manipulating the insertion, it will be done in a block, between parentheses, since it determines a structure
• To select a subtype, we must enclose the general type in parentheses, otherwise Postgres confuses it with a table and generates an error

3. Classes (really tables)

We are going to create a Figura class, which will be the starting point of an inheritance of the model presented at the beginning of the unit. Let's look at what it incorporates regarding the implementations of the relational model. Figura contains a primary key, and will contain a Punto to locate it in the plane. In addition, it contains a collection of colors, to make possible gradients, saved as an array. Saving collections is also an added capability that does not support the relational model, given the absence of multivalued values.

A class is created with the same syntax as a table, since, for practical purposes, from a structural point of view it is the same. Subsequently, inheritance does distinguish that one table "inherits" from another

create table Figura (
    fID serial primary key, -- identifier
    Punto position,  -- position he occupies
    color TEXT[ ]    -- color(s) of the figure
);

To insert new records, keep in mind that:

• Elements of type Punto must be stored using a constructor that creates an abstract row, called ROW or the parentheses.
• For arrays, we also need a constructor called ARRAY, with a list of elements.

insert into Figura(position,color)
  values(ROW(0,0),ARRAY['#FFFFFF','#00CC00']);

From there, we'll create new classes to represent the circles, squares, and rectangles from Figura using inheritance. The syntax is as follows:

create table sub_table(
 -- definition of table attributes
) inherits (super_table);

As we can see we simply add inherits to create the inheritance relationship. For the design we had previously:

create table Rectangle(
    height int,
    width int 
) inherits (Figure);

create table Quadrat(
    side int
) inherits (Figure);

create table Cercle(
    radio int
) inherits (Figure);

We insert some rows, noting that we have to include the superclass attributes as well.

insert into Quadrat(position,color,side) values (ROW(10,10),ARRAY['#00BBCC','#BBCC00'],40);
insert into Quadrat(position,color,side) values(ROW(10,fifteen),ARRAY['#AA6633','#CCFF00'],27);
insert into Cercle(position,color,radius) values(ROW(30,25),ARRAY['#BBCC','#CCCC00'],20);
insert into Cercle(position,color,radius) values(ROW(10,-10),ARRAY['#00BBCC','#CCCC00'],20);
insert into Rectangle(position,color,height,width) values(ROW(10,5),ARRAY['#00BBCC','#CCCC00'],20,50);
insert into Rectangle(position,color,height,width) values(ROW(30,-10),ARRAY['#00BBCC','#CCCC00'],20,50);

If we run several Select * from, qe get on Dbeaver:

{width=90%} {width=90%} {width=90%} {width=90%}

As it is logical, it is possible to think that when select data from the general table (Select * from Figura) all the elements of the subclasses will appear. If we wanted to select only those that are Figura, we could do it with (Select * from ONLY Figura).

We are going to complete the example by creating a drawing with all the figures we have stored. We will store the drawing in a new class that contains the identification of the drawing, and we will save a collection with the identifiers of the figures that make up the drawing.

create table Dibujo(
    drawingid serial primary key,
    items int[]
);

insert into Drawing (elements) values(ARRAY[2,4,5,6]);
insert into Drawing (elements) values(ARRAY(select fid from Figura));

Comment that the selection of the Figura identifiers can be direct, or by selecting those that we want, through an embedded query within the ARRAY constructor. This can be done when the select returns a single column.

We obtain the drawings that we have, this collection of figures is shown. But if we want to deconstruct the vector, in order to access each of the figures there is, we can do it with the unnest function.