SIDO SME Database Management System with LibreBase

Introduction

The Small Industries Development Organization (SIDO) offices in Tanzania would like to develop a computerized SME database to better serve their clientele in their regions. As a result the Canadian Executive Service Organization (CESO) has initiated two assignments for the SIDO offices in Mara and Arusha regions with a Canadian Volunteer Advisor (VA) specialist Augusto Ribeiro.
Based on experience with copyright issues with unlicensed software applications the VA proposes the use of Free and Open Source Software (FOSS) for the project. The software is LibreOffice Base from the Document Foundation established in Germany with support globally. LibreOffice is a powerful office application that can replace Microsoft Office and it runs on multiple operating systems including Windows, Linux and Apple based systems. It supports both open document formats odt, ods, odp, and Microsoft office file formats like doc, docx, xls, xlsx, ppt, pptx, etc.
For more information on the Document Foundation visit:
https://www.documentfoundation.org/

Current Situation

SIDO National has implemented an on-line SME database which requires input by their regional offices. This system does not seem to have many transactions to date. The regional SIDO offices work with a lot of paper forms, and hard copy records. The Mara and Arusha regional offices were the ones that requested support to computerize their hard copy data into a database system. They recognize the benefit of having and using a database system for search queries, reports and decision support.
As a result of our situation analysis there is a lot of commonality between the national and regional requirements. It would be beneficial for the national and regional offices to collaborate so that they can both benefit from the synergy of the automation of the regional databases. There is a larger incentive for the regional offices to make the effort to enter data into their own system where they can receive direct benefit versus encoding data to a national system where they currently get marginal benefits from.
Because of the low bandwidth for the internet, it seems more practical for the regions to have their own in-house database rather than rely solely on the on-line system which currently provides little option to download or make queries on-line.
However the regional system is being developed in such a way that their data can be uploaded easily to the national office to be merged with their on-line system.  There is a great need to update the regional ICT infrastructure from their local network, to hardware and software upgrades, to better internet access.  Right now most regional SIDO workstations are running XP and cannot be upgraded to Windows 7.  Windows 7 is needed for the newer and more sophisticated applications like LibreOffice Base. In addition those systems with Windows 7 were generally not updated with Microsoft patches or service packs which also affects other third party software installations.

Database Management System

What is a Database?

A digital database (DB) is a collection of data organized in such a way that a computer program can quickly select desired pieces of data. It can be considered an electronic filing system organized by fields, records and files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. A simple database that can be stored in an Excel spreadsheet is commonly referred to as a flat-file comprising of a single table.

 DBMS versus RDBMS

A DBMS is a collection of programs that enables you to enter, organize, and select data in a database. RDBMS is a DBMS that stores data in the form of related tables. An RDBMS is a single database that can be spread over several tables. This differs from flat-file databases in which each database is self-contained in a single table (e.g. spreadsheet)

Relational Database (RDBMS) Design

RDBMS Design Process

The following exhibit demonstrates the RBDMS design process (life cycle):

Situational Analysis 

The first step in the design process in any project, from software programming to road construction, is 'planning'. As someone once said, 'planning without action is futile, but action without planning is fatal'. Database planning is a strategic process it requires all stakeholders input from the manager to the operator who enters the data into the computer. It is not for the database administrator (DBA) or programmer alone that decides what is needed in a database and how it is used.
All stakeholders need to be involved in the process to establish the requirements for the RDBMS. In addition all the hard and soft copy information and data need to be collected for the group to discuss and evaluate what should be included in the database, and what information, or results, the stakeholders would like to extract from the database for decision support action.

Conceptual Design 

The 'conceptual design' phase is where the hard copy data including forms and spreadsheets are reviewed and the relevant data is extracted as field attributes for the database and are listed for the establishment of specific entities (tables) with attributes (fields) with relations between them.

Logical Design

The logical design phase is where the conceptual data is organized into normalized tables using the Entity-Relation (ER) design approach. This is also where entities or tables are related to other entities keeping in mind the cardinality of their relationships, such as one to one, one to many or many to many.

Entity Relationship Model

An ER model is typically used at the logical design phase of the implementation of a database. In a simple relational database implementation, each row of a table represents one instance of an entity type, and each field in a table represents an attribute type. In a relational database a relationship between entities is implemented by storing the “primary key” of one entity as a pointer or "foreign key" in the table of another entity. The primary key of a database table must contain unique values so that no two records in the table will have the same primary key value.
Entities are represented as a rectangle such as 'artist' (see exhibit below). Relationships are represented by a rhombus shape as in 'performing' below.  Attributes or fields in a record are represented in an ellipse as in 'SSN'. The entities and relationships are usually undertaken at the conceptual design phase of the project.

Database Development and Implementation Team

In order to develop and implement a good relational database one must assemble a team of stakeholders or users whose members have clearly defined roles and responsibilities. A proposed organization chart of such a team is shown below.


The Project Manager (PM) has the overall responsibility to manage the database project from the beginning to operations after go-live.  In this project the PM could be the SIDO Regional Manager or a senior member of staff as a designate. The Project Team (PT) are the staff or users who are the key stakeholders and they will assist in the planning, data acquisition, conceptual design of the entities, selection of the attributes and normalization of the tables. The Database Administrator (DBA) has the responsibility from the physical design phase to operations and maintenance. The DBA will consult with the team and receive direction from the project manager.
Once the physical design is completed based on the selected database application, such as the LibreOffice Base for this project, the Operator will start to encode and test the system with the DBA. Desired forms, queries and reports will be discussed, developed and reviewed with the PT and approved by the PM.
At the completion of the Logical Design a database schema should be completed. An example of a schema is shown below. This is an example of the regional SIDO SME schema which was used for the physical design:

Data Dictionary

The following data dictionary will describe in more detail the ER diagram and schema for the SIDO regional database model. The dictionary is important since it describes the field attributes in more detail to the users. Without a dictionary users may encode incorrect data into the database.
The Client Table

 This is the main table of the SIDO database. It contains a number of list tables that are stored in tables for easy data entry by selection of the attribute from a value list. The lists can be added to and edited in the database.
The Contact Table

 The Financial Table

This table is used to update and monitor the progress of the business after its startup. If there is regular follow up in subsequent years it should provide SIDO with good statistics on the progress of the SME as to whether it is growing, shrinking or staying the same.

 The Service Table

The Service table is used to enter services provided by SIDO regional offices to their SME clients. The services are advisory and training services respectively. The list of advisory and training services are entered into their respective list tables.

The Log Table

The Log table is for any communication between SIDO and their SME client whether by phone or meeting that is deemed important enough to add a memo of the interaction. For example an inquiry on training or other matters related.

SIDO Database Architecture

The SIDO database is implemented with LibreOffice Base which is based on a single user architecture, or a ‘centralized’ database on a single workstation. It is not a client-server database. Based on the assessment of the SIDO regional operations and infrastructure this architecture should be suitable for the regional operations into the near future.
LibreOffice Base is a FOSS application developed and supported globally by the Open Document Foundation. It has a good user base and uses SQL scripts on its database. It also has the ability to connect to other databases like MySQL, PostreSQL, etc via ODBC and JDBC drivers. It also supports multiple operating platforms like Windows, Linux and Apple.
When the regional offices develop a working networking infrastructure, and are ready to install a server to service the users in their respective offices, the SIDO SME database can be migrated to a client-server back end like MySQL. The users can continue to use LibreOffice Base on their desktops as a front end to the database on the server.  Eventually this could be replaced by a browser based thin client front end using PHP or equivalent browser based application.

LibreOffice Download and Installation

LibreOffice is available for download from the Document Foundation at:
https://www.libreoffice.org/download/download/
Make sure you download the version that is for your workstations operating system (OS) x86 for the 32 bit and x64 for the 64 bit OS. Also download the built in help install if you have limited internet access for the on-line help.
In addition you will need to install the Java runtime from Oracle.
http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Note we have found that version 8 is more stable than 9.
Those installing on Windows 7 need to insure that your system has the update Service Pack 1 and other patches including Windows Visual C++ 2015 Redistributable. If this is missing you could get a missing dll error when you open LibreOffice.

LibreOffice Base

LibreOffice Base (LOBase) is the relational database application that is used to implement the regional SIDO SME database. The native HSQLDB database format will be used. LOBase can connect to enterprise client server databases like MySQL or Oracle, but we are only using it to create a centralized workstation based RDBMS with LOBase.

Reference documents

1. LibreOffice 4.0, The Document Foundation, Base Handbook – Managing your Data, 2013
2. Mariano Casanova, Base Tutorial: From Newbie to Advocate in a one, two... three!, 2010
3. Design Your Own Database – Concept to Implementation, or How to Design a Database Without Touching a Computer (https://www.dartmouth.edu/~bknauff/dwebd/2004-02/DB-intro.pdf )

YouTube Video Tutorial

1. The Frugal Computer Guy. (thefrugalcomputerguy.com)
A great site with short learning videos that teachers users to use LibreOffice Base to build a relational database system from start to finish.  Highly recommended to users who would like to know how to use LibreOffice Base for their RDBMS.

Appendix

Appendix A - Human Resources & Project Management

Introduction

"85% of big data projects fail, but your developers can help yours succeed", an article by Matt Asay (see link https://www.techrepublic.com/article/85-of-big-data-projects-fail-but-your-developers-can-help-yours-succeed/). In the blog article, which references Gartner research by analyst Nick Heudecker, the primary causes of failure are the difficulty of integrating with existing business processes and applications, management resistance, internal politics, lack of skills, and security and governance challenges.

We have included consideration of the need for 'change management' and 'executive buy-in' for IT related projects, however failure of large distributed database systems are not confined to the implementation phase of the projects. Many create the seeds of failure at the application development phase because poor design processes and lack of sound communication between the client and the application developer. 

In many cases the client does not spend enough time to review their own needs thoroughly, and instead rely on the developer, and their technical analysts, to determine it for them. This is convenient for the developer because it saves them time and money and, in many cases, allows them to make even more money from change orders after implementation. The Canadian government is still paying billions to IBM for their failed Phoenix pay system. Instead of saving $70 million a year it is reported that it will cost Canadian taxpayers a total of $2.2 billion by 2023.

The first step to a successful IT project is good human resources (HR) from both the client and developer. Ideally the client would appoint a Project Coordinator/Manager whose counterpart would be the developer's technical Project Manager. The client's team should also include a subject matter or domain expert (DE/SME) who understands the complete project requirement and operations, and additional user team members, such as those involved in specific transactions and data entry/encoding.

Correspondingly the development team would have its Project Manager who is in charge of the entire system and specific team leaders that would be responsible for the backend (database/server), frontend (browser/GUI) and technical analysts who would coordinate with the development team and the client. On smaller projects these functions could be undertaken by a single experienced IT person, however that person should be aware of which role the person is responsible for in the different tasks being undertaken.