2.4.1. Database design principle

Poor database design leads to inadequate database performance (Kamatkar et al. 2018) as it miscarries the database Atomicity, Consistency, Isolation, and Durability (ACID), which makes database lose durability, and makes it expensive when carrying out the operation. However, a good database design minimizes redundancy and anomalies, preserves known functional dependencies, and prevents spurious information from emerging (Albaraka et al. 2018). Normalization is database design method to organize data in relations or tables following specific rules to reduce data duplication and ensure referential integrity (Fong et al. 2021). A normalized database can also have effects on improving maintainability and scalability (Albaraka et al. 2018). Hence, it reduces costs associated with insertion, deletion, and changing anomalies. Even minimizing a small amount of anomalies can result in significant cost minimization (Sobol et al. 1996), which improves database performance.

According to (Albaraka et al. 2018), achieving Third Normal Form (3 NF) for all the database tables is advantageous over a non-normalized database structure. Therefore, every database should be normalized to at least 3 NF, meaning the primary keys are defined and columns are atomic; therefore, there are no repeating groups, no partial dependencies, and no transitive dependencies (Albaraka et al. 2018). Figure 3 shows the database design structure of the ECDSWC data portal. It provides mass storage to store data, such as spatial data and its attributes along with metadata and other documents.

This novel design structure is achieved based on the principle of 3 NF. As stated in (Albaraka et al. 2018), the database design based on 3 NF meets all the requirements of 2 NF and removes transitive dependency from a unit database table, that is, all of the columns must be dependent on the primary key of the table, which improves database performance like flexibility, maintainability, and stability (Sobol et al. 1996). Apart from performance improvement, this database design ensures that transactions will not be affected by any other concurrent transaction; therefore, information saved in the database is immutable until another update or deletion transaction affects it. It is also critical to keep data transactions atomicity to ensure database system’s reliability and if failure happens, it returns all the data to the state before the transaction was executed.

2.4.2. Database indexing

The maximum computation cost in the DBMS engine is fetching of information (Myalapalli et al. 2015). Indexing reduces query time in the database (Colley et al. 2017). It minimizes the number of disk accesses required when a query is processed. However, an improper or missing index is poor database index, and frequently updates database tables; therefore, it leads to increase disk input/output (I/O) and memory wastage (Kamatkar et al. 2018). However, a proper index removes duplicates that affects system resources (Kamatkar et al. 2018). It is also recommended that indexing should be created on the accurate columns of the database tables (Sultana et al. 2017), like the primary key of the table.

PostgreSQL provides several indexing techniques, such as Balanced tree (B-tree), Hash, Generalized Index Search Tree (GiST), Specially Partitioned-GiST (SP-GiST), Generalized Inverted Index (GIN) and Block Range Index (BRIN) (Sultana et al. 2017). B-tree and hash are the two most used methods (Qu et al. 2019). The ECDSWC database is intended to use the B-tree index type as suggested in (Qu et al. 2019) for the range query process, that is, lower and upper limiting query when conducting data searches from database tables. As discussed in 2.3 and 2.4.1, the ECDSWC database structure is a complex database. Therefore, when creating indexes in the ECDSWC database structure, we selected three (3) database tables that were planned to contain millions of records. Further, the CONCURRENTLY keyword is used with the index query to avoid locking between multiple database operations. Moreover, creating an index yields extra structure in the database (Qu et al. 2019), holds additional space on the database, which creates tension on the overall system. To avoid this tension, the ECDSWC database used WHERE condition to disallow index creation when the item stored in the database is less than a certain number of records.

2.4.3. Parallel query processing

Parallel query processing enables system memories and processors (CPUs) to cooperatively work together to improve system performance in a data-intensive operation of the large database environment (Mancini et al. 2022). When the query optimizer decides to execute parallel query process in PostgreSQL, it creates a query planner. Then the query planner generates a parallel query plan. The process will request a number of background worker processes, which is equal to the number of workers chosen by the planner. The background workers that the planner uses are limited to at most max_parallel_workers_per_gather, that is, it depends on both max_worker_processes and max_parallel_workers (Schönig 2019). Therefore, it is possible for a parallel query to run with fewer workers than planned. The above commands such as max_worker_process display the number of available worker process during execution; max_parallel_workers display the number of workers available for parallel query and max_parallel_workers_per_gather identifies the total number of background worker when parallelism executes.

PostgreSQL executes several parallel query types including parallel sequential scan, parallel index scan, B-tree creation, aggregation and append (Schönig 2019). The data portal implements parallel B-tree index formation and parallel index scanning to reduce index creation and scanning time. As discussed in section 2.4.2, indexes speed up database searching processes. However, creating an index and scanning the index within a database that stores large datasets can still be computationally expensive.

2.4.4. JSON file transaction

XML and JSON are two data serialization formats used for data transmission between web applications (Afsari et al. 2017). XML produces child nodes and parent nodes while JSON uses lightweight data based on key-value pairs, which is effective to improve system performance (Yahui 2012). Therefore, it saves execution space and time, and reduces network latency. JSON file format has been widely used in web applications (Daquino et al. 2022; Ramachandran et al. 2018; Zioti et al. 2022; Wu et al. 2022). According to Ramachandran et al. (2018) it is best practice to use JSON file format for data sharing in repositories that have large-scale data. The ECDSWC data portal thus implements the com.google.gson.Gson, maven dependency, which encodes data in a JSON file format. On the implementation, the function json.toJson<data_<param1,param2>> calls the data_<param1, param2 > method to invoke a Data Access Object (DAO) that returns value into Map. Entry<key, value> pairs within JSON file format. The return value is then passed to the client side, which is the presentation layer.

2.4.5. Thread pool to handle user’s query

The data portal is implemented thread pool with a maximum number of threads, which is available in the processor of the system hardware. In the implementation, the thread pool is created on top of SQL batch processing, which handles bulk data and other user queries in the style of the multithreaded query handling structure. Instead of processing data sequentially in the loops by using single thread, allowing high number of cores for multithread is efficient (Schönherr et al. 2011). To do that the Java class object <thread_pool> is implemented.

Thread pool implementation in multi-tasking environment provides load balancing when the thread process shares mutual queue. To balance load and jobs in a queue, this study provides balanced file handling method by implementing java list object, List<thread_pool>threads = Arrays.asList<param>, which runs in the thread pool. Sometimes there will be a high thread demand that can kill active processes. Therefore, the thread balancing is used to optimize high thread demands on such occasion, which improves performance of the existing system.

The user queries and bulk data of different file format is then passed to a prepared statement, which executes a database query. Prepared statement creates a copy of the embedded SQL statement to iteratively execute Data Manipulation Language (DML) for the multiple sets of values. When the iteration ends, the executeBatch()method is called and executed to store data in the database.

3.1.1. File upload module

Figure 4 shows user interface (GUI), which is granted for an ‘Elite User’ who has Read-Write (Wr’) privilege. The user is responsible for storing and managing files in the repository. When the User clicks on the ‘Add Project Data’ node in the GUI (Figure 4), the ‘Add Project’s Raw Data’ sub-module is displayed on the main window. In this sub-module, the user can add single or multiple files, which are then segmented and annotated with metadata. The contents of the interface shown in Figure 4 are a bulky and broadly classified dataset as well as metadata, ready to upload when the user presses the upload button. A check box and a drop-down list are used to segment and annotate files with their metadata descriptions.

Based on the descriptions of metadata, N numbers of files with multiple categories can be added to the interface. At the end, datasets and their metadata are simultaneously sent to the service layer. The service layer handles the large-sized bulk datasets and their metadata by using thread pools. Thereafter, the thread pool shares task loads to minimize computational complexity. Many data management applications create new threads redundantly to handle data and user queries, which creates performance overhead. To overcome the issue, the modular data portal implements thread pooling to share the single thread operation’s tension in between threads. In addition, the data portal avoids process deletion between threads by balancing the load and queueing jobs. Therefore, thread pooling significantly enhances computational overhead.

When discussing this use cases: Add Projects Raw Data Sub-Module, the goal of the user is to upload raw data. The raw data is collected from different projects launched by the ECDSWC and uploaded into the repository. To upload the dataset; first, the dataset or binary file shall be buffered into the <Add Project’s Raw Data> module. Then the user has to embed annotation or metadata such as project name and thematic areas from the drop-down list, and then datasets are segmented under the data type, which is controlled by a checkbox. The dataset’s thematic area and possible classes are as follows. For all categories, an ‘Add New’ option is available.

• Hydrology: cascaded into classes of stream flow and water level.
• Geology: cascaded into classes of Geotechnical, Geophysics, and Geochemistry.
• Geodetic: cascaded into classes of LULC, Land Surveying, and Arial Surveying.
• Meteorology: cascaded into classes of Rainfall, Solar Radiation, Temperature, Humidity and Wind.
• Soil and Land Evaluation: cascaded into classes of Auger, Profile, Insitu Measurement, Soil Lab Data, and Land Suitability Evaluation.

Further, checkboxes are used to define data types, which are under the classification of the dataset. Therefore, the user checks the checkbox to annotate data types of the binary data before storing data in the repository.

The other use case we discuss here: Add Project Report Sub-Module. When the user clicks on this node from the parent tree of GUI (Figure 4), the sub-module is displayed on the main window as shown in the Figure 5. The user adds file or binary data to this sub-module and then metadata is annotated from a drop-down list, which is a technical definition of the project report and stored in the repository. The project report is the working document or results derived from project’s raw data. Finally, the project report and its metadata are uploaded into the file repository when the user hits an ‘Upload’ button. Note, if the project reports do not have the related raw data in the repository, the user needs to provide technical definitions and then annotate new metadata such as project name and ID. This project report is considered confidential data and managed under the centers/sectors of ECDSWC. The project reports are grouped into the following categories. Again, an ‘Add New’ option is provided for all categories.

• Design: cascaded into Design work, initiation document, and inception document.
• Supervision: cascaded into initiation document, inspection document, compilation document.
• Study: cascaded into Feasibility Study.

Within the Manage Repository Module, files are managed in repository, with respect to the integrity policy. The privileged ‘Elite User’ can update annotations and other metadata for a binary file. In this context, the privileges granted to the ‘Elite User’ include the following:

1. Grants project access: Users who created the project can share his/her projects to others.
2. Manages dataset in the repository (raw data or project report): Users are responsible and accountable for the data they stored and if necessary, they can remove the files or binary data from the repository.

3.1.2. Search engine

A search engine provides search execution that allows sophisticated filtering and discoveries. It allows users to start searches using metadata or annotation of files or binary data descriptions. When the user sends a search parameter or keywords of metadata from the GUI, the data access object is invoked. Based on the search key, the annotations of binary data are processed in the file repository and then a result is sent to the presentation layer via the controller model. The raw data is retrieved with the perspective of thematic area of the study such as geodesy, meteorology, hydrology, geology, and soil and land evaluation. We have two options to access datasets such as Global Search and Table-Based Search. The global search engine uses keywords from the project names (Figure 6). Based on the keyword given, all annotated names for the project data are displayed and then the user chooses the relevant project name of the project to display document or binary data under its thematic area.

The table-based search lists all the annotations of the binary data from the repository (Figure 7). The user can then filter to find the target binary data. Metadata, which is populated in the table header, is used to filter binary data on the table. Therefore, the user filters the table by using the project name from the metadata, then the thematic area of datasets, followed by datasets classification, as seen from Figure 7. For instance, the user selects project name by clicking on the <Project Name> drop down list, then thematic area drop-down list to select the thematic area of the project (Hydrology, Geology, Geodetic, Meteorology, and Soil and Land Evaluation). Finally, the user hits on the classes of thematic area, which display the targeted binary data. At the end, the user downloads the directed file.

3.2.1. Experiments and test cases

The modality, functionality, and structure of the data portal, as well as cardinality, confidentiality, and integrity of the data, were tested based on the experiments done. Tests were carried out from both users’ and experts’ perspectives. From a user perspective, the data portal was tested to examine the ease and multi-task supportiveness of the GUI when accessing, storing, and sharing datasets. The test revealed that the GUI can handle concurrent and simultaneous operations, is flexible and interactive, and can handle multiple types of requests and responses via multitasking. Further, it is structurally dynamic because of a complete implementation of hypermedia; as a result, the system loads data or mapping responses on the current page rather than redirecting to the new page. The tests also confirmed that the data portal is reliable and efficient, as measured by the number of failing cases from the total number of test cases performed when storing, managing, and sharing data simultaneously between users. On the failure measurement test, the system was tested via five users with different types of concurrent and simultaneous operations. The first user performed metadata annotation, uploaded raw data, project reports, and managed data and its metadata. The second user completed metadata annotation and uploaded a project’s raw data. The third user worked on searching and viewing of metadata about datasets and then downloaded datasets. The fourth and fifth users did requests for the project report and granting operation, respectively. All the operations were performed in a concurrent manner and simultaneously, which is a total of ten operations. From these operations, zero failures and ten successful operations are recorded. Therefore, it is evident that the data portal is 100 percent efficient and reliable under these test conditions.

From the expert perspective, a test was done in order to assure functionality, availability, and accuracy of the system. A test was also done to check the integrity and privacy of the data in the repository, as well as to teste a transaction commit and rollback. These test cases used single and bulk modes of Create, Read, Update, and Delete (CRUD) concurrent operations. Then the ACID, data transaction mechanism was measured to evaluate the degree of changes that occurred to the existing transaction when other concurrent transactions took place. The test was performed by User 1 as well as User 2. The test revealed that transactions were never affected by any other concurrent transaction; therefore, data and its metadata in database are immutable until another update or deletion operation affects it.

The other test case used in the experiment was to verify the performance of the data portal. Experiments were done with a set of input variables within group of five different bulky datasets, such as Meteorology, Geology, Hydrology, Geodetic, and Soil and Land evaluation. In each group, more than five users concurrently perform their operations using CRUD operations. In this test case, file downloading speed of 12 MB/s was recorded when User 3 simultaneously downloaded two different bulky datasets. This shows that the data portal has high performance capacity, and the privacy of data is preserved. Toward this end, this modular data portal adopted the: – AJAX mechanism, REST API principle, database optimization, and thread pooling to its implementation. HTTP requests and responses are exchanged between client and server in the process of resource mapping, which means the data loading and replying to the request are done through multiple endpoints in an asynchronous manner (Lawi et al. 2021). In the REST API, there is no code compilation or page redirection computation cost; therefore, the hyperlink decoration mechanism is independent from the resource identification schema (Li et al. 2016) and defined independently from the schema. Conversely, when the system uses URL redirection, it creates new web pages to handle HTTP responses. As a result, it requires more computational time for code compilation and page creation when compared with loading or mapping of data on the current page, due to DNS lookup time, TCP connection time, SSL handshake time, HTTP header elements, page download time, number of DNS iterations, and number of HTTP redirects (Asrese et al. 2016).

These experiments and test cases were based on 352 projects data stored in the repository, which contained 3,318 binary files of different file formats (see Figure 8 (a) and (b)). Moreover, the binary files in the database add up to around 69 GB in size, as shown in Figure 9.

Generally, these experiments revealed that the data portal can support concurrent operations with acceptable performance, delay tolerance, and keeps privacy and integrity of the data. Therefore, the experiments and test cases showed that this data portal is secured and modular, and capable and robust to store, manage, and share large-sized datasets. It is also capable of controlling concurrent operations and able to manage all the users and workgroups that can access the system.

3.2.2. System evaluation

Normalization and de-normalization of databases are the two inverse activities. Some prior research argues that normalization, such as 3 NF, augments databases by adding additional tables and requiring joins in queries, making it more expensive than a non-normalized database. Even though there will be more tables after normalization, joining tables is faster and more efficient because it sets a small number of parameters; therefore, the queries will be less complicated compared to the non-normalized design (Albaraka et al. 2018). Moreover, weakly normalized or non-normalized tables can store a larger number of files compared to normalized designs due to potential for data redundancy. As a result, it may increase record size and overhead I\O computation (Sobol et al. 1996). Therefore, implanting 3 NF in this study kept all the columns atomic, and reduced repetition, partial dependencies, and transitive dependencies. As a result, the 3 NF improves the database query performance when comparing with a non-normalized database.

Regarding database indexing, B-tree minimizes computation during the disk’s I/O operation. As the analysis presented in Figure 10 and Table 1 show, the computational time of B-tree is O(𝑙𝑜𝑔𝑛) while sequential scan takes O(𝑛). The analysis showed that the running time of sequential scan grows faster than B-tree index. We carried out experiments on the data portal using the aforementioned 352 ECDSWC projects, which contains 3,318 different types of binary data (Figure 8 (a) and (b)), and measure about 69 GB in size (Figure 9).

Table 1 summarizes the query execution time of both sequential and B-tree indexes. We observed that database operation with B-tree is computationally much better than sequential operation. Other measurements adopted on the data portal were parallel query processing, accessing JSON file formats, and thread pooling. Parallel query processing reduced the index creation and table scanning time. Both thread pooling and accessing JSON file formats added value to reduce computational time and network latency.