Exploring OpenOffice™ Base: an example involving rare-earth mineral data

A contribution by Axel Drefahl, axeleratio.com, September 8, 2016.


Apache OpenOffice™ 4.x versions come with a desktop database management system. OpenOffice Base, ooBase for short, is a free alternative to the Microsoft Access database [1]. I recently started to give ooBase a try. My test data were selected from the mineralogy of rare-earth elements (REE) and their compounds. 

With ooBase one can create relational databases—each application completely contained in one file. Design, testing and use of an ooBase application involves working with tables, queries, forms and reports. You typically will build and update tables, define relations between tables and then customize your application by adding queries, forms and report generating tools.

Herein, and in some following posts, I am going to share the initial development of my REE ooBase project, hoping that it will be helpful to others beginning with ooBase and that I can gain useful insight into ooBase's integrability and compatibility with other database systems—and existing chemical & mineralogical database applications and services, in particular.

I am continuing in the you and we form in present tense—also in the imperative mood—to engage the readers and followers of this project. The approach is to provide a tutorial-type experience—although the given REE ooBase example leans on my personal interest. The following typographical conventions are used to distinguish text and tools within the ooBase interface:

• Title of a window, section or panel in bold blue: Database Wizard,
• To be clicked text or button in bold black with gray background: Database,
• Text requesting a user decision in bold red: What do you want to do?,
• Text of a selection to be made in bold green: Create a new database, 
• Text to be entered in bold purple: MineralClass,
• File name in bold Courier:  RareEarthMinerals.odb,
• Menu name in Courier: Help.

 

Let's get started! 

To create a new application, start Database in the start-up menu of your OpenOffice™ suite. Under What do you want to do? in the Database Wizard window select Create a new database, then press Next>>.in the bottom row of the window. Asked how to proceed after saving the database, stay with the default options Yes, register the database for me options and Open the database for editing, as shown below.

Default settings in creating an OpenOffice Database application

Select Finish. When the Save As window appears, type the name of your database. In our example, the file name is RareEarthMinerals.odb. Press Save to get the main ooBase window with the Database, Tasks and Tables areas.

Let's make the table MineralClasses

In the Tasks panel, select Create Table in Design View… to open the Table Design window. Now you are ready to design your first table. It will contain the names of mineral classes. Type MineralClass as the first field name and select Text [ VARCHAR ] as Field Type. The second Field Name will be Notes, also with Text [ VARCHAR ] as Field Type. The field names will show up as column headings, once data are entered in spreadsheet view (data view).  At the bottom of the window, you have the choice to change default settings for the field values. Here, we keep the default settings.

Design view of an OpenOffice Database table

Under the File menu, select Save. You will be asked to name the table. Name it MineralClasses and press OK. The No primary key message box should appear, asking Should a primary key be created now? Every table requires a primary key column! Anwer with Yes to create a primary key. Now your first Field Name is ID, having Field Type Integer [ INTEGER ]. In the Field Properties panel, switch the AutoValue from No to Yes and close the Table Design window.

Next we populate table MineralClasses as shown below. Click Tables in the Database panel and then select MineralClasses in the Tables panel to open your table in spreadsheet view. We only fill in the MineralClass column.

Spreadsheet view of an OpenOffice Database table with 11 rows

Since we activated AutoValue, a primary key is automatically enter into the ID field when we finish its row. We leave the fields of the Notes column blank, but may want to come back later to enter mineral class details. 

The primary keys will serve as secondary keys or foreign keys in the next table we are going to create. This table contains the names and associated formulas, rare-earth minerals and some boolean descriptors of rare-earth minerals. Mineral names and formulas are taken from the literature [2]. The mineral class of each mineral is referenced by using the primary key in the table we just created.

Once the two-table database has been finished, we will be able to query it; for example, to look up the REEs a given mineral contains or to find all minerals that contain a given REE (or a given REE combination). Search results may then be arranged by mineral class as well as other criteria.  

Keywords: open source, ooBase, table design, design view, spreadsheet view, primary key, AutoField.

References
[1] Apache OpenOffice Base: https://www.openoffice.org/product/base.html.
[2] Appendix of the contribution “Mineralogy of the Rare-Earth Elements” by F. P. Cesborn in: P. Möller, P. Černý and F. Saupé. Lanthanides, Tantalum and Niobium. Springer-Verlag, Berlin Heidelberg, Germany, 1989.