5.1.1.Logical Data Model

Virtuoso provides an extended Object Relational model which offers all the flexibility of relational access with inheritance, run time data typing, late binding, identity based access.

Table

A table is a uniquely named entity that has the following characteristics:

  • Zero or more columns

  • One primary key

  • Zero or more keys (indices)

  • An optional super table from which this inherits properties

  • An optional object ID key, which may or may not be the primary key

  • Various SQL table constraints, e.g. CHECK's

A table will then have zero or more rows. The relationship of a table and its rows can be thought of as a class-instance relationship.

Column

A column is always defined in one table and has a name that is unique within that table. A column may appear in more than one table as a result of inheritance but always has one place of definition. i.e. one database wide 'identity'.

A column has the following characteristics:

  • Table

  • Name inside the table

  • database wide ID

  • Data type

  • Various SQL constraints, e.g. DEFAULT, CHECK etc.

Key

A key or index is the means by which tables manifest themselves in the physical database. A key is always defined with respect to one table but may occur in several as a result of inheritance. Keys have unique names inside the table. A key has the following characteristics:

  • A database wide key ID

  • One or more 'significant' key parts, which are columns of the defining table or super tables

  • Zero or more 'trailing' key parts, columns of the defining table or supertables.

  • Whether the key is primary

  • Whether the key is unique

  • How the key is clustered

  • Whether the key is an object ID key

Subtable

A subtable is a table that inherits all columns, the primary key and all other keys from another table, called the super table.

A subtable can define its own columns and keys which add themselves to those of the super table. No primary key can be redefined, though.

The inheritance relationship between tables is manifested by a key-subkey relationship between the tables' primary and other keys.

A table has at most one supertable.

Object ID

A table does not necessarily declare a primary key. Even so, the table must have a primary key - in this case a synthetic record ID which is defined as primary key. The record ID is an autoincrementing column that is normally invisible but, if present, can be accessed by explicit reference. One should not rely on this feature being available, though.

Thus

create table nokey (a integer);

expands to

create table nokey (a integer, _IDN integer identity, primary key (_IDN));

The first unique index to be defined will become the primary key if the table is empty at the time of definition.

Thus

create unique index a on nokey (a);

will change the nokey table to be as if defined by

create table nokey (a integer, primary key (a));

Having a primary key other than _IDN is always better than the default primary key. Declaring a primary key is therefore always advisable.