J. Correia Lopes


User Tools

Site Tools


II. Database Specification (EBD)

This component groups the artefacts to be made by the development team in order to support the storage requirements and retrieval of the information identified in the requirements specification.

A4: Conceptual Data Model

The Conceptual Domain Model contains the identification and description of the entities of the domain and the relationships between them.

At this stage of the project we are mainly interested in the database specification, thus the Conceptual Domain Model is simplified to include only concepts (entities and relationships) of the domain that are stored in the database. In this artefact the data requirements of the system are detailed.

The Conceptual Data Model is obtained by using a UML class diagram containing the classes, associations, multiplicity and roles. For each class, the attributes, associations and constraints are included in the class diagram. Business rules not included in the class diagram are described by words or using OCL (Object Constraint Language) included as UML notes.

A4 MediaLibrary example | A4 template

A5: Relational Schema, validation and schema refinement

This artefact contains the Relational Schema obtained by mapping from the Conceptual Data Model.

The Relational Schema includes the relation schemas, attributes, domains, primary keys, foreign keys and other integrity rules: UNIQUE, DEFAULT, NOT NULL, CHECK.

Relation schemas are specified in the compact notation:

Table1(id, attribute NN)
Table2(id, attribute → Table1 NN)
Table3(id1, id2 → Table2, attribute UK NN)
Table4((id1, id2) → Table3, id3, attribute)

UK means UNIQUE and NN means NOT NULL.

The specification of additional domains can also be made in a compact form, using the notation:

Priority ENUM ('High', 'Medium', 'Low')

To show the domains of the attributes and the CHECK constraints of attributes and tuples, in addition to this representation, the relational schema is also presented in SQL.

To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas is accomplished. Should it be necessary, in case the scheme is not in the Boyce–Codd Normal Form (BCNF), the relational schema is refined using normalization.

A5 MediaLibrary example | A5 template

A6: Indexes, triggers, user functions and population

This artefact contains the physical schema of the database, the identification and characterization of the indexes, the support of data integrity rules with triggers and the definition of the database user-defined functions. This artefact also includes the complete database creation script, including all SQL necessary to define all integrity constraints, indexes and triggers.

Physical Schema

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 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 can not 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

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 populated with data

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 from the database.

A6 MediaLibrary example | A6 template

teach/lbaw/artefacts_ebd.txt · Last modified: 29/03/2019 09:13 by Correia Lopes