SIDO Database Management with LibreOffice Base

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 formed in Germany with global support. LibreOffice is a powerful office application that can replace Windows Office and it runs on Windows, Linux and Apple based systems. It supports both open document formats odt, ods, odp, and Microsoft office file doc, docx, xls, xlsx, ppt, pptx, etc. formats.

 For more information on the Document Foundation visit:

https://www.documentfoundation.org/

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 can be stored in Excel spreadsheet as a flat-file.

 DBMS versus RDBMS

A DBMS is a collection of programs that enables you to enter, organise, 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 softcopy 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 it for decision support action.

Conceptual Design

This could be considered the 'conceptual design' phase where tables and spreadsheets are reviewed and field attributes for the database are listed.

Logical Design

This phase is where the conceptual data is organized using the Entity-Relation diagram approach. This is also where entities or tables are related to other entities keeping in mind the cardinality of the relationships, such as one to one, one to many or many to many.

Entity Relationship Model

An ER model is typically implemented as 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

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 with clear 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.  The Project Team (PT) are the key stakeholders who will assist in the planning, data acquisition to conceptual design of the entities, attributes and normalization. The Database Administrator (DBA) will take over at the logical and physical design phase with consultation with the team and direction of the project manager.

Once the physical design is completed based on the selected database application which is LibreOffice Base in this project the Operator will start to encode and test the system. 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, which will be used for the physical design: