# A6: Indexes, triggers, user functions, transactions and population

> Brief presentation of the product.
> Brief presentation of the artefact goals.

## 1. Database Workload

> A study of the predicted system load (database load), organized in subsections.

### 1.1. Tuple Estimation

> Estimate of tuples at each relation.

| **Relation reference** | **Relation Name** | **Order of magnitude** | **Estimated growth** |
| ------------------ | ------------- | ------------------------- | -------- |
| R01 | Table1 | units|dozens|hundreds|etc | order per time |
| R02 | Table2 | units|dozens|hundreds|etc | dozens per month |
| R03 | Table3 | units|dozens|hundreds|etc | hundreds per day |
| R04 | Table4 | units|dozens|hundreds|etc | no growth |

### 1.2. Frequent Queries

> Most important queries (SELECT) and their frequency.

| **Query** | SELECT01 |
| --- | --- |
| **Description** | One sentence describing the query goal |
| **Frequency** | magnitude per time |
| `SQL code` ||

### 1.3. Frequent Updates

> Most important updates (INSERT, UPDATE, DELETE) and their frequency.

| **Query** | UPDATE01 |
| --- | --- |
| **Description** | One sentence describing the query goal |
| **Frequency** | magnitude per time |
| `SQL code` ||

## 2. Proposed Indices

### 2.1. Performance Indices

> Indices proposed to improve performance of the identified queries.

| **Index** | IDX01 |
| --- | --- |
| **Related queries** | SELECT01, ... |
| **Relation** | Relation where the index is applied |
| **Attribute** | Attribute where the index is applied |
| **Type** | B-tree, Hash, GiST or GIN |
| **Cardinality** | Attribute cardinality: low/medium/high |
| **Clustering** | Clustering of the index |
| **Justification** | Justification for the proposed index |
| `SQL code` ||

### 2.2. Full-text Search Indices

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

| **Index** | IDX01 |
| --- | --- |
| **Related queries** | SELECT01, ... |
| **Relation** | Relation where the index is applied |
| **Attribute** | Attribute where the index is applied |
| **Type** | B-tree, Hash, GiST or GIN |
| **Clustering** | Clustering of the index |
| **Justification** | Justification for the proposed index |
| `SQL code` ||

## 3. Triggers

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

| **Trigger** | TRIGGER01 |
| --- | --- |
| **Description** | Trigger description, including reference to the business rules involved |
| `SQL code` ||

## 4. Transactions

> Transactions needed to assure the integrity of the data.

| SQL Reference | Transaction Name |
| --------------- | ----------------------------------- |
| Justification | Justification for the transaction. |
| Isolation level | Isolation level of the transaction. |
| `Complete SQL Code` ||

## 5. Complete SQL Code

> The database script must also include the SQL to populate a database with test data with an amount of tuples suitable for testing and with plausible values for the fields of the database.
> This code should also be included in the group's git repository as an SQL script, and a link include here.

### 5.1. Database schema

### 5.2. Database population

---

# Revision history

Changes made to the first submission:
1. Item 1
1. ..

***
GROUP21gg, DD/MM/2021

* Group member 1 name, email (Editor)
* Group member 2 name, email
* ...