This artefact contains the physical schema of the database, the identification and characterization of the indexes, the support of data integrity rules with triggers, the definition of the database user-defined functions, and the identification and characterization of the database transactions. This artefact also includes the complete database creation script, including all SQL necessary to define all integrity constraints, indexes, triggers and transactions.
The physical schema of the database contains a study of the predicted system load (database load), including an estimate of tuples at each relation and the Physical Schema of the database. The database load includes the most important queries (SELECT) and their frequency, important changes (UPDATE, DELETE) and their frequency, and the required performance for these queries and updates.
Should it be necessary to improve the performance of the queries, indexes of the convenient type (B-tree, Hash, GiST and GIN) are introduced. As last resource, controlled redundancy may be introduced (de-normalisation). It is also needed to decide the clustering of an index.
The system being developed must provide full-text search features supported by PostgreSQL. Thus, it is necessary to specify the fields where full-text search will be available and the associated setup, namely all necessary configurations, indexes definitions and other relevant details.
To enforce integrity rules that cannot be achieved in a simpler way, the triggers are identified and described by presenting the event, the condition, and the activation code.
User-defined functions and trigger procedures that add control structures to the SQL language, or perform complex computations, are identified and described to be trusted by the database server.
Every kind of function (SQL functions, Stored procedures, Trigger procedures) can take base types, composite types, or combinations of these as arguments (parameters). In addition, every kind of function can return a base type or a composite type. Functions can also be defined to return sets of base or composite values.
The database transactions are needed to ensure the integrity of the data in the presence of concurrent accesses.
For each transaction, with a proper justification, the isolation level must be explicitly stated and read-only transactions must be identified to improve global performance. For each identified transaction, the SQL code is presented.