The Database Schema

The term database schema describes a collection of related objects such as tables, views, and indexes a user or application owns. In the Actian Data Platform, each user has an implicit schema created for them unless they use a CREATE SCHEMA statement.

Types of Database Schemas

The two primary types of schemas are logical and physical schemas. When designing a database for storing application data, an application developer usually starts with an entity-relationship model that maps interrelationships between business elements. The next step is to create a normalized database design that adds details about each entity, its attributes, and keys. This becomes the basis of the SQL statements that are used to create tables, with their columns, primary and foreign key relationships, and referential integrity constraints.

In addition to logical schemas that support applications, there is the notion of a schema that contains objects that a user creates. This is commonly called a user schema. For example, the object named SCOTT.DEPT is a table called DEPT in the user SCOTT schema.

A physical schema defines how the logical database design is mapped to physical devices such as solid-state storage devices (SSD) and hard disks. Many databases have the notion of a DBSPACE or TABLESPACE that is mapped to a physical or virtual storage device. The CREATE TABLE statement can optionally specify the DBSPACE or TABLESPACE to ensure that the table resides on a specific device. This is typically done to minimize IO contention.

Schema Integration Requirements

Database schema integration refers to the integration of two or more discrete database schemas into a single unified schema. Below are some important considerations for integrating schemas:

• Database objects need to be mapped to the business entity they represent so overlaps or conflicts can be identified and integrated
• Datatypes must be homogenized. One schema may use a fixed character format to optimize storage capacity, while another may have standardized on a variable format with flexibility in mind
• Keys and constraints could differ across schemas as their applications may require different access patterns
• Views may differ across schemas. Performance tuning often results in views being materialized
• Tables can be de-normalized and pre-joined, creating a design choice that must be rectified
• Redundant objects need to be removed as application evolution results in newer objects being used, leaving orphaned objects.
• It is very unlikely that two organizations use the same naming standards, so naming has to be reconciled.

Creating a Schema in the Actian Data Platform and ActianX

The CREATE SCHEMA statement creates a named collection of database objects. The CREATE SCHEMA statement has the following format:

[EXEC SQL] CREATE SCHEMA AUTHORIZATION schema_name

              [object_definition {object_definition}];

schema_name

Specifies the effective user for the session issuing the CREATE SCHEMA statement.

object_definition

Is a CREATE TABLE, CREATE VIEW, or GRANT statement. Each user has a maximum of one schema per database.

The statements within the CREATE SCHEMA statement must not be separated by semicolon delimiters; however, the CREATE SCHEMA statement must be terminated by placing a semicolon after the last object definition statement. If object definitions are omitted, an empty schema is created.

To issue grant statements in a CREATE SCHEMA statement, you must have the required privileges. Specifically, to grant a privilege on an object you do not own, you must have been granted the privilege WITH GRANT OPTION (see GRANT OPTION Clause).

If a CREATE SCHEMA is issued specifying an existing schema (schema_name), Actian Data Platform issues an error. To add objects to an existing schema, issue the required CREATE statements outside of a CREATE SCHEMA statement.

If no schema exists for the effective user identifier, one is implicitly created when any database object is created. If a CREATE SCHEMA statement is subsequently issued for the user, Actian Data Platform returns an error.

If within a CREATE SCHEMA statement, tables are created that have referential constraints, the order of CREATE TABLE statements is not significant. This differs from the requirements for creating tables with referential constraints outside of a CREATE SCHEMA statement, where the referenced table must exist before creating a constraint that references it. For more information about referential constraints, see CREATE TABLE.

Other users can reference objects in your schema if you have granted them the required permissions. To access an object in a schema other than the schema for the effective user of the session, specify the object name as follows:

schema.object

For example, user Harry can select data from the employees table of the accounting group (if accounting has granted Harry select permission). Harry can issue the following SELECT statement:

SELECT lname, fname from accounting.employees

Locking

The CREATE SCHEMA statement takes an exclusive lock on a page in the iischema catalog. Locks are acquired by the individual CREATE statements within the CREATE SCHEMA statement but released only when the CREATE SCHEMA statement itself is committed. If the CREATE SCHEMA statement contains CREATE statements that acquire locks in excess of the maximum configured for Actian Data Platform, the CREATE SCHEMA statement is terminated.

The following example shows how the creation of an Actian schema for the accounting user provides context for sub-objects:

CREATE SCHEMA AUTHORIZATION accounting
       CREATE TABLE employees (lname CHAR(30) NOT NULL,
              fname CHAR(30) NOT NULL,
              salary MONEY,
              dname CHAR(10)
                     REFERENCES dept(deptname),
              PRIMARY KEY (lname, fname))

       CREATE TABLE dept(deptname CHAR(10) NOT NULL UNIQUE,
       location CHAR(15),
       budget MONEY,
       expenses MONEY DEFAULT 0)

       CREATE VIEW mgr(mlname, mfname, mdname) AS
         SELECT lname, fname, deptname FROM employees,dept
           WHERE dname = deptname

       GRANT REFERENCES(lname, fname)
         ON TABLE employees TO harry;

You can try the Actian Data Platform for 30 days for free.