Database Smart Web Tool

2.1 Non-functional requirements

2.2 Functional requirements

3.1 Start page: filters & recommendations

The main key of the Start Page will be the search bar and the filters that help the users choose the characteristics they are looking for.

Besides the name input, the user can select information about: implementation programming language (C, C++, etc.), programming language supported (Java, Python, etc.), operating system (Linux, Windows, etc.), project type (Commercial, Open Source, etc.), and license (Apache v2, BSD, etc.).

On the Most popular section of the Start Page will be displayed four of the most searched or used databases. Each one will be located in a different card, with some general information, so that the people that are not sure what is more suitable for their project will have the recommendation of what they can use.

Implementation outcome:

3.2 Search results, databases comparison

The user can either search after a keyword or choose some features from the dropdowns and toggles presented below the search bar:

After the user presses the Search button, a table will be shown with the results regarding the database name, programming languages used for implementation, programming languages supported, licenses, operating systems and project types.

Implementation outcome:

The user has the possibility to select a maximum of 3 databases for comparing them, by pressing the "Compare" button, after which a pop-up appears. In this pop-up, the selected databases are presented in a table format with differences between their features coloured with red.

This feature is post-MVP.

3.3 View database details

The user is able to press on a database name and a pop-up will be shown with the details of the selected database. In this pop-up, there’s also a table composed of 2 columns (the first column indicates the feature name and the second column is the particular feature description). In the right part of the pop-up is a text with some details about the database selected by the user and at the bottom of the pop-up there's a reference to various pages providing more information about the database (Homepage, Wikipedia, Source code - depending on what data we have available in the backend).

Implementation outcome:

4.1 Database management system

The relational database management system chosen is PostgreSQL[1], also known as Postgres, due to our familiarity with it over the years. It is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.

PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. It is the default database for macOS Server and is also available for Windows, Linux, FreeBSD, and OpenBSD.

4.2 Provenance

The dataset was provided directly by Associate Professor of Databaseology in the Computer Science Department at Carnegie Mellon University, Andy Pavlo, who is one of the main maintainers of dbdb.io, the Database of Databases. Permission to use the dataset for this project has been granted.

The data was sent as a Django database dump archive, which contained a 257mb one-line JSON file.

4.3 Transformations

The format in which the data was received was not fit to be used by our application, so further operations were necessary in order to clean and restructure it.

Initially, we tried to format the one-line JSON file to human-friendly JSON formatting by using jq.

                cat dbdb-dump-nov14-2021.json | jq . > formatted.json
            

That didn’t really make much of a difference since the resulting JSON[3] file was still quite big, almost impossible to open with the usual tools (Notepad, Notepad++, Visual Studio and even vi).

Further, it was clear that the JSON format was not proper for us to understand the data structure and content. The aim is to import the data into a database so it’s easy to query by any backend. Databases go well with CSV imports, so we converted the JSON file into a CSV file using a simple Python[4] script:

                
import pandas as pd
from pathlib import Path
import json

# set path to file
p = Path(r'/content/formatted.json')

# read json
with p.open('r', encoding='utf-8') as f:
    data = json.loads(f.read())

df = pd.json_normalize(data)
# write csv
df.to_csv('/content/data.csv', index=False, encoding='utf-8')
                
            

The resulting CSV file contained about 1 million rows and 53 columns, out of which many turned out to be redundant, missing dependencies and/or not useful to us (we removed about 900.000 rows which were nginx logs from the actual dbdb.io website). Importing the CSV file to a PostgreSQL database was less straight forward than expected, but, using pgfutter and pgAdmin, it finally resulted in a new table.

4.4 Structure

The table structure after importing the CSV consisted of a table with columns such as:

                
model, pk, fields_slug, fields_label, fields_multivalued, fields_feature, fields_value, fields_name, fields_url,
fields_created, fields_modified, fields_secret_key, fields_view_count, fields_ver, fields_system,
fields_user, fields_description, fields_citations, fields_options, fields_ip_address, fields_user_agent,
fields_recommendation, fields_score, fields_creator, fields_meta, fields_is_current, fields_comment,
fields_start_year, fields_end_year, fields_history, fields_acquired_by, fields_developer, fields_logo,
fields_countries, fields_former_names, fields_tech_docs, fields_source_url, fields_wikipedia_url,
fields_description_citations, fields_start_year_citations, fields_end_year_citations,
fields_history_citations, fields_acquired_by_citations, fields_project_types, fields_derived_from,
fields_embedded, fields_inspired_by, fields_compatible_with, fields_licenses, fields_oses,
fields_publications, fields_supported_languages, fields_written_in
                
            

Working with a single table with 100.000 rows and 53 columns can be cumbersome, so, after some data exploration in the remaining dataset, we worked out a possible improved data structure:

This new structure, however, needed to be created from scratch. The tables and relationships were created directly using pgAdmin UI, but the data from the initial table was copied to the new tables using a PHP script [5] which can be found in our GitLab repository here

5.1 UC1: Search a certain database

1. The user accesses the site.
2. The user types in the Search bar the key words.
3. The user selects from the presented drop-down lists the features for the database they want.
4. The system takes the selected options (from the user) to generate the proper results.
5. The system displays the results into a table format.

5.2 UC2: View details about a database

1. The user searches for a specific database.
2. The system displays a list with the databases.
3. The user presses on a database name from the generated list by system.
4. The system shows a pop-up with the details regarding a certain database.
5. The user views more details accessing the wikipedia reference from the pop-up.
6. The user accesses the database website from the presented reference.

5.3 UC3: Comparing minimum 2 databases

1. The user searches for a specific database.
2. The system displays a list with the databases.
3. The user selects a minimum of 2 databases from the generated list by system.
4. The system shows a pop-up with a comparison between the selected databases.
5. The system marks with red the differences between the databases and shows the similarities with black.