ssn

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 code 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 on the number and growth of tuples in each relation. ¶

Performance indexes are applied to improve the performance of select queries. At most, three performance indexes can be proposed, identifying the ones that have the biggest impact on the performance of the application. For each proposed index, it is necessary to indicate and justify the type chosen (B-tree, Hash, GiST, GIN), and also if clustering is needed. As a last resource, controlled redundancy may be introduced (de-normalisation). ¶

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 necessary 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. ¶

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. ¶

The database script must also include the SQL code to populate a database with test data with an amount of tuples suitable for testing and with plausible values for the fields from the database. ¶

Instructions on setting up and using PostgreSQL are provided at Persist data with PostgreSQL. ¶

A6 MediaLibrary example | EBD template | A6 checklist ¶