Creating the

Rocketry Research
Assistant Database
Part 1
Practical Database Programming
Using LibreOffice Base

Robert W. Austin
NAR 47533

Copyrighted 2023

1|Page
 Creating the Rocketry Research Assistant Database
Part 1 © 2023
by Robert W. Austin is licensed under CC BY-SA 4.0.
To view a copy of this license, visit
http://creativecommons.org/licenses/by-sa/4.0/

This Project Manual was created using LibreOffice

Writer Version 7.5. The Rocketry Research Assistant
database was created in LibreOffice Base using the
embeded HSQL database.

The LibreOffice logo was created by Christoph Noack - CC BY-SA 3.0,

https://wiki.documentfoundation.org/File:LibreOffice-Initial-Artwork-Logo.svg,
https://commons.wikimedia.org/w/index.php?curid=34369081

2|Page
 The Austin Aerospace
Educational Network
The Austin Aerospace Educational Network (AAEN) is a network of sites that is designed to
provide you with the resources you need to perform a wide variety of projects using model
rocketry. Model rocketry is a wonderful hobby, a great educational tool and just a world of fun.
Model rocketry can provide a window that allows you to looking towards the future, be active in
present day events and peer back into history and learn from those who have gone before us.

If you like working with technology, model rocketry and computers are made for each other.
You can use software to design your own model rocket and make sure it is stable so it will fly
straight and true. You can create simulations of how your model rocket will perform using
different motor or fin configurations. You can conduct real research projects.

How about electronics? Thanks to small micro-controllers like the Arduino and ESP 32 as well
as single board computers like the Raspberry Pi, anyone can add real electronics and avionics to
their rocketry projects.

You can incorporate 3D printing to create parts that currently don’t exist or to bring old designs
long since removed from store shelves back to life. This offers the rocketeer a way to create
components for their rockets and support systems that couldn’t have been imagined even 10
years ago.

But perhaps the best news is that we have only begun to scratch the surface of the hobby, as there
is so much more that you can do with model rocketry. Our intention is to provide you with a full
array of information on the wide and wonderful world of model rocketry, what I consider the
most fascinating hobby on the planet!

The Rocketry Research Journal

Our main site is the Rocketry Research Journal. This blog and web site can be found at
https://rocketryjournal.wordpress.com. Below we list what you can expect to find on this site.
There is no charge for any of the information or software you find on the site. Please feel free to
download and share our reports, software, technical manuals, etc.

Here’s What Is on the Site

The web site provides a portal to a number of the resources we have available. They include:
 The Rocketry Research Journal blog features articles on recent projects, news from the
world of rocketry (both full size and miniature) and more. Check back frequently for the
latest updates.
 View our Tech Reports. At the time of this report there a total of seven reports available.
They cover the basics of model rocketry, an introduction to doing research, single station
altitude tracking, two station altitude tracking, how to adjust your electronic altimeter to
account for temperature changes, how to use a spreadsheet to calculate altitude and tips
on getting started using an Arduino micro-controller.

3|Page
  We have a section that focuses on the Arduino micro-controller and how it can be used in
model rocketry.
 We have a section set aside for 3D printing. Currently we have an article on using 3D
printing to build a Dyna-Soar Titan II model rocket.
 There is a page for Model Plans. There are two plans currently available, but more are on
the way.
 The Austin Aerospace Education Network (AAEN) has been developing the open source
Flight Logs Database Program. The software can track your rockets from initial
construction, then track all flights and record any maintenance needed or performed. It
can calculate altitude, record any 3D prints used on the model, store the plans and even
report CATOs to the MESS (Malfunctioning Engine Statistical Survey) site. If you are a
NAR member and looking at completing your NARTREK submissions for the Bronze,
Silver or Gold levels, it can help with that as well. There’s even more the software can do
for you. Read more about it on the Flight Logs Software page.

Our Sister Sites

We have a number of other sites that you can visit for specific rocketry projects or activities.

Source Forge Open Source Software Repository

 A Listing of All of Our Software
https://sourceforge.net/u/austinaerospace/profile/

3D Printing File Repositories

 Thingiverse
https://www.thingiverse.com/austin_aerospace_education/designs

 Printables
https://www.printables.com/social/173143-austin-aerospace-education/models

 Thangs
https://thangs.com/designer/Austin%20Aerospace%20Education

CAD Files
 TinkerCAD
https://www.tinkercad.com/users/kGt9Dmmc88b

Project Instruction/Tutorials
 Instructables
https://www.instructables.com/member/Austin_Aerospace_Education/instructables/

YouTube
 Rocketry Research Journal Video Channel
https://www.youtube.com/@AustinAerospace

4|Page
Table of Contents
01 Introduction to LibreOffice and Base.....................................................................................8
Additional Information................................................................................................................8
LibreOffice Base..........................................................................................................................8
HyperSQL................................................................................................................................9
Firebird.....................................................................................................................................9
Other Database Engines...........................................................................................................9
02 About the Database Project...................................................................................................11
A Note About Microsoft Access................................................................................................11
Transition to LibreOffice Base..................................................................................................12
Creating an Educational Project.............................................................................................13
03 Designing the Database...........................................................................................................15
What is the Process to Create a Database?................................................................................15
1. What is the purpose of the database?.................................................................................15
2. Information research and organization..............................................................................15
3. Information is sorted into tables........................................................................................16
4. Information details become fields......................................................................................16
5. Identify the Primary Key...................................................................................................17
6. Relationships......................................................................................................................18
7. Improve the design of the database....................................................................................18
8. Normalize the database......................................................................................................19
Ready to Start Coding? Not So Fast..........................................................................................20
Project Table..........................................................................................................................20
Task Table..............................................................................................................................21
Team Members Table............................................................................................................21
Close, But Not Yet Ready to Code............................................................................................24
Reviewing Table Fields.........................................................................................................24
Lookup Tables.......................................................................................................................25
Field Types.................................................................................................................................26
Integer Field Types................................................................................................................27
Floating-Point Fields..............................................................................................................27
Text Fields.............................................................................................................................28
Date and Time Fields.............................................................................................................28

5|Page
 Miscellaneous Fields..............................................................................................................28
Final Table Design.....................................................................................................................29
Relationships..........................................................................................................................33
04 Creating the Database Tables................................................................................................34
Create the Database....................................................................................................................34
Database Registration............................................................................................................35
Table Wizard..........................................................................................................................36
Creating Tables..........................................................................................................................37
Tasks Table............................................................................................................................37
Complete Creation of the Tables...........................................................................................40
Relationships..............................................................................................................................40
Create Relationships in Base.................................................................................................41
Edit Relationship Links..........................................................................................................42
Populate Lookup Tables with Data............................................................................................43
05 Creating Database Forms.......................................................................................................45
Creating the Members Form......................................................................................................45
Step 1 - Select Fields to Display in the Form........................................................................45
Step 5 – Arrange Form Controls............................................................................................46
Step 6 – Data Entry Mode......................................................................................................46
Step 7 – Form Style................................................................................................................47
Step 8 – Form Name..............................................................................................................47
The Wizard Result.................................................................................................................47
Modifying the Members Form...................................................................................................48
Resizing the Text Boxes........................................................................................................48
Arranging the Data Fields......................................................................................................49
Cleaning Up Labels................................................................................................................50
Updating Date Boxes.............................................................................................................50
Converting Drop-down List Boxes........................................................................................51
Formatting Phone Numbers...................................................................................................52
Form Title..............................................................................................................................54
Activation Order....................................................................................................................54
Additional Forms.......................................................................................................................55
The Projects Form..................................................................................................................55
Task Form..............................................................................................................................58

6|Page
 Finishing Up Our Forms............................................................................................................59
06 Thoughts on Improving the Rocketry Research Assistant.................................................60
Reports.......................................................................................................................................60
Additional Forms.......................................................................................................................60
Additional Data Fields...............................................................................................................60
Error Correction.........................................................................................................................61
Database Engine.........................................................................................................................61
Split the Database..................................................................................................................61
Help File.....................................................................................................................................61
07 Conclusion...............................................................................................................................62
Appendix.......................................................................................................................................63
A1 LibreOffice Base Edit Mask Key..........................................................................................64
A2 Tables......................................................................................................................................65
A3 Relationships...........................................................................................................................69
A4 Relationship Graph................................................................................................................70
A5 Sample Forms.........................................................................................................................71
A6 Data Types for the Table Editor*..........................................................................................74
A7 Properties of Controls‡..........................................................................................................76
A8 References...............................................................................................................................78

7|Page
01
Introduction to
LibreOffice and Base
This Project Manual will focus on database programming using the Base program in the
LibreOffice Office Suite. LibreOffice is a free and open source suite of office programs that
includes:
• Writer – Word processing program
• Calc – Spreadsheet program
• Impress – A presentation program
• Draw – A vector graphics editor, raster graphics editor, and diagramming tool
• Math – A program to create math formulas for use in Writer and Calc
• Base – A database management program

LibreOffice is cross-platform and available on Windows, Linux Mac OS and a variety of other
operating systems. It can also
import and export documents in
a wide variety of formats,
including those used by the
Microsoft Office Suite.
LibreOffice can be downloaded
for free through The Document Foundation at https://www.libreoffice.org/download/download-
libreoffice.

Additional Information
The Document Foundation has released a number of guides on using LibreOffice. There are
separate guides for each section of LibreOffice, including Base. These can be found at
https://documentation.libreoffice.org/en/english-documentation. They are free to download in a
number of formats. You can also purchase printed copies of the guides if desired. If you plan to
work a lot with Base (or any of the other sections of LibreOffice) it would be to your benefit to
download the guide you need.

LibreOffice Base
Creating a database program involves a number of different steps. We will need to develop a
basic design for the database, create tables that hold the data, develop forms that allow data to be
easily entered and displayed, create queries that allow us to manipulate the data to get
information out of database, and generate reports that provide the data in an easy to read format.
In this part of the development process we will focus on creating the database tables and a few
primary forms.

8|Page
LibreOffice Base is actually two programs. The data is stored in a database called the HyperSQL
Database, or HSQLDB (http://hsqldb.org). HSQL database is an open source relational database
that is 100% Java. The database (the tables that store the data) is often referred to as the back-end
of the database. The front-end (forms, queries and reports) is handled by LibreOffice Base. The
HSQL database is embedded into the Base program, so that it appears as a single program. This
has advantages in keeping everything together in a single file (seen as the file extension odb in
LibreOffice). It is an easy database program to use and for basic projects (such as the one in this
manual) it works very well.

HyperSQL
The version of HSQL used by LibreOffice is Version 1.8. This was released in July of 2005.
HQSL has been updated a number of times since then, and as of this writing is at version 2.7.1
released in October 2022. HSQL requires the Java Runtime environment to work. Java is also
required for other parts of Base to function properly besides HSQL.

It is possible to use the current version of HSQL in your database, but it will take some work.
For details on how to use the latest version of HSQL visit their web site at
http://hsqldb.org/web/openoffice.html.

Firebird
LibreOffice Base also allows the use of Firebird as the embedded database engine. However, it is
considered ‘experimental’ and is not available by default. To allow Firebird to be available, go to
Tools → Options → LibreOffice → Advanced → Optional Features and check the box to
“Enable experimental features” and then click OK. You will need to restart LibreOffice. After
the restart Firebird will be available to use as an embedded database.

LibreOffice uses version 3.0 of Firebird, released in 2016. It was included for the first time in
LibreOffice in version 6.1 released in 2018. Firebird is currently on Version 4.0 released in
August of 2022, while their BETA Version 5 was released in March of 2023.

There are a number of differences between HSQLDB and Firebird DB. HSQL requires Java,
while Firebird is written C++. A comparison of the two database engines can be viewed at
https://books.libreoffice.org/en/BG73/BG7312-AppendixB-CompareHSQLDBAndFirebird.html.
If you wish to migrate your database from HSQLDB to Firebird, this guide will be of help
(https://wiki.documentfoundation.org/Documentation/FirebirdMigration). To read more about
Firebird in Base visit https://wiki.documentfoundation.org/Development/Base/FirebirdSQL.

Other Database Engines

While LibreOffice Base can use HSQL or Firebird as embedded database engines, is not
restricted to just those two. It can also connect to a number of different databases, such as
MySQL, Oracle, dBase, PostgreSQL and Access. It can even connect to a LibreOffice Calc or
Microsoft Excel spreadsheet (but only in ‘Read Only’ mode). With any of these options, the
back-end database tables are kept separate from the front-end program written in Base.

9|Page
Using the default settings in Base places everything in a single file. While convenient it can lead
to problems of data corruption if the software has problems saving the file. For this reason, it is
often advisable to split the database into two separate parts; the back-end and front-end. Should a
problem occur in saving the front-end resulting in a corrupt database, the back-end is unaffected
and the data is still sound and usable. In this case our database is small and we are unlikely to
encounter this issue. However, as your database projects grow, and especially if you share the
data among multiple team members on different computers, splitting the database should be
given serious consideration.

10 | P a g e
02
About the
Database Project
Both research and engineering projects collect a lot of data. Data is collected during the
development of the launch vehicle and payload. Data is collected during testing of the system,
and data is collected during the actual flights. Working with data doesn’t stop there. Once the
data has been collected it must be reviewed to make sure that the project is performing as
expected. Data analysis is a critical part of any research or engineering project.

To help with a basic understanding of how databases are constructed and how they can be used
we have created the Rocketry Research Assistant (RRA) database project. In this project we will
take you step by step through the design, development and use process. We will start with the
design of the database and figuring out what we want the database to do (the objectives) and
what information the database should store to meet those objectives (if you haven’t read Tech
Report 11 “Introduction to Database Design” we would encourage you to do so before
proceeding with this project). Next is developing the tables that will store the data and creating
the relationships that allow the database to function effectively. Then we start to create the forms
that the users will interact with, followed by the queries that will provide the data needed for the
forms or reports. Finally we will design the reports that will help us look at the data in ways that
are helpful to the management of our research project.

The database project created here is intended to be the foundation of your future database
projects. By creating this project you will learn the basics of database creation. The next steps are
up to you. Do you want to expand the project with additional forms, tables and reports (we will
discuss this in more detail during the review process at the end of the manual)? Do you want to
create a new database that helps with the project you are currently working on? You might want
to look at how to share the database among other team members by taking the database and
splitting it into a front end and back end. There is also the option of incorporating the spreadsheet
program Calc into the project to crunch numbers from a data collection research project. Or
include it in the word processor Write to help create your research project reports.

This project is designed to get you started. To make you aware of how databases work. To help
you understand how they can be helpful in your research projects. To let you know that you can
develop basic database programs and skills that can be immensely beneficial to you and your
team.

A Note About Microsoft Access

At the Austin Aerospace Educational Network (AAEN) our previous database development
efforts had revolved around Microsoft Access. The Flight Logs database was developed using

11 | P a g e
Microsoft Access 2010. It used the Access runtime module to allow anyone to run it on their
Windows computer without having to install Access on their computer.

One of the problems with using Microsoft Access is that it is Windows only. This meant that it
could not be used on other operating systems like Linux or Mac. This became a concern and we
debated how to approach this issue while continuing development on the Flight Logs software.

Then on August 8, 2022, Microsoft announced that Windows updates would block VBA (Visual
Basic for Applications) macros that originate from the Internet (https://learn.microsoft.com/en-
gb/DeployOffice/security/internet-macros-blocked). Further, a banner would be displayed
indicating that there is a security risk involved in running the software.

This gave us pause, as Microsoft was stating any software created using VBA was unsafe and
untrusted. It didn’t matter who created it. It didn’t matter what the software did. There was no
testing to see if there was anything wrong with the software. Just a blanket condemnation of a
program simply because of the use of VBA.

There was a followup announcement by Microsoft on January 17, 2023, which stated that an
upcoming Windows update will scan a user’s computers to “identify the number of users who
are running out-of-support (or soon to be out-of-support) versions of Office, including Office
2013, Office 2010, and Office 2007.” (https://support.microsoft.com/en-us/topic/january-17-
2023-update-for-office-2013-office-2010-and-office-2007-kb5021751-f4a23c1d-4d1f-44ba-
a43a-7a5528afb4ea).

Given that Microsoft is already blocking software that uses VBA/macros and putting up scary
banners, along with the recent news of their plan to scan for old versions of the software, I firmly
believe that Microsoft will soon have formidable barriers in place to stop the software from
running.

It is very discouraging to invest the amount of time in developing a program such as Flight Logs
and see a warning banner indicating it is “security risk.” Such “warnings” can give the
impression the other items I provide through the web site are also a security risk.

Going back and reviewing how we should proceed with our database development activities, it
became very easy to answer. When we combined the limitation of Access being Windows only
along with the updates by Microsoft to restrict the use of Access (and Access 2010 in particular),
it was decided that we would begin to transition to another database management system.

Transition to LibreOffice Base

LibreOffice Base helps us address the issues that have been identified with Access and the
Windows operating system updates. LibreOffice is able to run not just on Windows (Version 7 or
greater) but Mac OS, and Linux. There are unofficial version that run on Android, iOS, Chrome,
FreeBSD, OpenBSD, NetBSD, Haiku and Solaris. It is also available on a number of platforms

12 | P a g e
including x86-64, IA-32, ARMel, ARMhf, ARM64, MIPS, MIPSel, PowerPC, ppc64le, S390x,
and VLIW (a version of LibreOffice runs on my Raspberry Pi 400). This extremely wide variety
of systems and platforms makes LibreOffice a very flexible and wide ranging program, and
makes Base available to a wider audience of users. It is also free and open source, so there is no
need to purchase a license to use the software.

LibreOffice Base also has the ability to create a database project as a single file using the
embedded HSQL database engine (it can also use the embedded Firebird database engine). For
small teams, single users and others, this has the advantage of not requiring a full database server
to be set up. If the user is working with a larger team or on a network, the LibreOffice Base
project can be split into a front end and back end for use on a database server. Much like Access,
it can be easily adapted to meet the needs of a wide range of users. All of this made the decision
to transition to LibreOffice Base from Microsoft Access not only easy, but it really is an upgrade
for the users who will be using the software.

Having resolved to transition to a new database engine, the next question was to decide if we
wanted to recreate the Flight Logs program in Base, or should we start a new database program.
We decided that given the educational direction of the AAEN that it would be better to start
development of a new database called the “Rocketry Research Assistant.” This database will
have an initial focus on tracking research project teams for various projects. Later it will be
expanded to cover other areas involved in research projects. You will no doubt be able to see
where certain parts of Flight Logs will be incorporated into future releases of the Rocketry
Research Assistant.

Creating an Educational Project

When developing an open source database program it is typically developed in sections and then
released to the public. This is the process we used during the development of Flight Logs. This
time we decided to do something different. We wanted the development of the Rocketry
Research Assistant to be used not just as a database, but as an educational project as well. This
would allow us to not just provide a new database that students can use, but also show how the
database was developed. The result is this first release of the RRA along with the accompanying
Project Manual.

Beginning with the next chapter we start with the initial design of the database. It describes how
the initial tables and their relationships are created. Then we discuss the actual creation of the
tables and how to construct their relationships in the database. We will show you step-by-step
how we created these first forms, including a number of screenshots to help you see exactly what
we did. We also discuss some options that you might want to consider in your own database.

This initial release of the Rocketry Research Assistant included this Project Manual. Each
subsequent release of the RRA will also come with its own Project Manual, showing what was
added, changed or deleted since the previous release. As database development continues we will
explain how we designed our queries, what is SQL, and more. We will create reports that allow
you to present project information in useful ways and we will look at how the RRA database can
integrate with other parts of LibreOffice, including Calc and Writer.

13 | P a g e
We hope that you agree that this transition to LibreOffice allows us to meet so many more
objectives than could ever be obtained had we continued to use Microsoft Access. We also hope
that you agree with our decision to use this database development project as an educational
project in itself. Finally, we hope that you agree with these decisions and will join us as we begin
this new series of database development projects.

14 | P a g e
03
Designing the
Database
What is the Process to Create a Database?
In Tech Report 11 “Introduction to Database Design” we describe an 8-step process to designing
databases. In the development of the Rocketry Research Assistant (RRA) we will use that same
8-step process.

1. What is the purpose of the database?

The primary purpose will be to track each project and the various tasks performed for each
project. At the project level we need to know the objectives of the project. We need to know who
is in charge of the project. What type of project are we working on and what is it supposed to
accomplish. When is the start date and end date of the project, when are all of the materials and
deliverables for the project due. How far along is the team to completing the project.

We also need to know all of the tasks needed to complete the project and the priority of each task
in the project. We need to know who is the leader of each task. When is the task due, what is the
current status of the task, and how far along the team is to completing the task.

2. Information research and organization

After going through the process of figuring out what we want our database to track, we can begin
to sort the information and put it in organized list. By working through the purpose we can see
that there are two main focus areas – the project information and the tasks needed to complete
the project. Within those two items is the need to identify who is in charge of the project and
each task. That tells us that we will need a third area that tracks the team members working on
the project. With that information in hand, we can begin to organize data that will need to be
collected in the three areas.
• Project Information
◦ Name of the project
◦ Project lead
◦ Type of project
◦ An overview of the project
◦ The objectives of the projects
◦ The date the project is to start
◦ The date the project is to be completed
◦ The percentage of the project that is completed
◦ The date the project was concluded

15 | P a g e
 • Task Information
◦ Name of the task
◦ Project the task is assigned to
◦ The priority of the task
◦ Who is assigned to the task
◦ Due date
◦ Date started
◦ Percentage of task completion
◦ Date task is completed
• Team Member Information
◦ Name
◦ Email address
◦ Mobile phone number

One thing to notice is that as we drill down through the information we begin to discover
information that we need to track that at first may not be obvious. In item 1 we just had two main
items – Projects and Tasks. Now we know that we will need at least one more area for Team
Members.

3. Information is sorted into tables

Based on the lists in section 2, we can start by creating three tables:
• Project table
• Tasks table
• Team Members table
We will start with these as our baseline.

4. Information details become fields

We now begin to take the information we have collected and start creating our tables. We will
start with the Project table and the detail information from section 2. We will also begin to think
about the type of data to be collected. Here is the first iteration of our three tables.

Project Table
Project Name Text
Project Lead Member Text
Project Type Text
Project Overview Text
Project Objectives Text
Project Start Date Date
Project Due Date Date
Project Completion Percentage Number
Project End Date Date

16 | P a g e
 Tasks Table
Task Name Text
Task Project Text
Task Priority Number
Task Assigned Member Text
Task Date Due Date
Task Date Start Date
Task Completion Percentage Number
Task End Completed Date

Team Members Table

Name Text
Email Address Text
Mobile Phone Number Number

The information is beginning to look like a database, but we still have a way to go yet before we
start creating our tables in Base. Let’s look a bit closer at our tables and see how we can improve
them.

5. Identify the Primary Key

The Primary key is a unique identifier for the data set. It is unique to a database record. Think of
it as something like a driver’s license or a social security number. You are the only one assigned
to those numbers. No other person uses those same numbers. That is the same role as the Primary
Key.

In two of our three tables we don’t have any data points that meet the requirements to be a
primary key. However, we can create an auto-increment field that will start with the number 0
and increase it by 1 for each record. There will never be any doubles. I use the identifier prefix
“ID_” to show that this is an auto-incremented field.

To use this in the Project Table we will create a field called “ID_Project” and it’s data type will
be a number that auto increments. We’ll do the same thing in the Tasks table, creating a field
called “ID_Tasks” that also auto-increments.

That leaves us with the Team Members Table. You might be considering using the Phone
Number as a Primary Key. It seems to work, as it is unique to the person. However, there is a
problem with using the phone number. What if the person gets a new phone with a new provider.
They may get a new phone number. Once we change the number the key no longer works.
Instead, lets do the same thing we did with the other two tables and create an auto-increment
field called “ID_Member”. We now have a Primary Key for all three tables.

17 | P a g e
6. Relationships
In this step we look at how the tables interact or ‘relate’ to each other. Here are the relationships
that are currently present in the database.
• Project Table relates to the Tasks Table
The Tasks Table needs to know the name of the Project each task is assigned to
• Members Table relates to the Project and Tasks Tables
Both the Project Table and the Tasks Table will need a person from the Members Table
assigned to each project and each task

While we have identified these relationships we can’t assign them yet in the database. This is due
to the fields have different data types. We’ll tackle that later in the Project Manual.

7. Improve the design of the database

At this stage of the design process we begin to look more closely at the various data fields in
each table as well as the relationships. We want to discover where some problems might lay,
how we can correct them, and if there are any other improvements we can make to our database.

Member Name
All three tables have a data field for a member’s name, and all three have it as a text field.
However, one of the problems that becomes apparent is that we could have a wide range of
entries in these fields that all identify the same person. For example, in the Team Members table
my ‘legal’ name of “Robert W. Austin” might be used. In the field for Project Lead, the user
might enter my name as “Bob Austin” as that is what I typically go by. In the Tasks table my
name might be entered as “Bobby Austin”. All three of these names identify me, but to the data
base they are three different people. It gets worse if the user misspells the name, such a “Bobby
Austn”. Or they may use a different spelling of the name, such as “Robert Alston”.

These types of data entry issues need to be identified and corrected so that every person uses the
same identifier. This will keep the data consistent. The easiest way to make this consistent is to
use an identifier for the member rather than having the user type in the members name. We can
use the ID_Members as this identifier.

Data Type
When we setup the identifier in all three tables we decided to use an auto-increment number.
That field uses a type of number call an Integer. Integer data types hold numbers that are whole,
or without a decimal point.

When you look at the Project and Task tables, both of the Member fields are listed as Text fields.
You cannot create a relationship where the fields have different value types. To allow the
Member fields in these tables to link to the Members Table we need to change their value types
to Integer as well. Do not make these fields auto-increment – only the ID_Members should be set
to auto-increment. Once the text fields are changed to integer fields, we can create these
relationships. We can see this relationship in the figure on the next page.

18 | P a g e
Relationships between Members table and the Project and Tasks tables

Relationship Types
If you look at the lines that connect the Members table to the Project and Tasks tables you will
notice there is a number and a letter at each end of the line. These identify the type of
relationship that has been created. In this case both of these relationships are classified as “One-
to-Many.”

In a “One-to-Many” relationship there is one record in a table that may be associated with many
records in another table. In our Team Members table there is just one entry for each member (the
‘one’ in these relationships). But that member may be the leader in several projects or assigned
to a number of tasks (the ‘many’ in these relationships).

There are two other common relationships that you may see in a database. They include a “One-
to-One” relationship and a “Many-to-Many” relationship. Neither of these are used in this
database at this time. We do describe these relationships in Tech Report-11 “Introduction to
Database Design.”

8. Normalize the database

Normalization is the review process that helps avoid errors and duplication in the database.
There are three levels that should be performed on every database you create.

First Normal Form Normalization

During First Normal Form (1NF) normalization we check to see if every cell within our database
tables contains a single value and not a listing of values. Additionally there should be no
repeating of groups of fields. With these standards in mind, lets begin by looking at the Project
Table.

List of Values and Project Objectives

We currently have a single field for Project Objectives in the Project Table. It is a text field so
the user can enter the project objectives. However, it is very unlikely that a project would only
have a single objective. Instead there is typically a list of objectives for any project. They may
even be categorized. As it stands now, we only have a single field which means we would have

19 | P a g e
to create a list of objectives in this single field. We need to correct this as part of our 1NF
process.

Since the objectives will be a list we can create a new and separate table for them and link it back
to the Project Table. This table can be rather small, consisting of just three fields:
• ID_Objective
• ProjectID
• Objective

To create this relationship we will simply need to link ProjectID in the Objectives table to
ID_Project in the Project table. Now we can have as many objectives as we want. We have
started our first round of database normalization.

Second Normal Form Normalization

To achieve Second Normal Form (2NF) the database design must first meet all the requirements
of 1NF. Secondly, every non-key field is fully dependent on the primary key. Looking at our
now four tables we can see that every table has a Primary Key. We are compliant here.

Third Normal Form Normalization

When we get to Third Normal Form (3NF) normalization the database must be 2NF compliant
and all fields are dependent on the primary key and no other fields in the database. We do not
have any fields that are dependent on other fields within the database so we have met this
requirement.

Ready to Start Coding? Not So Fast...

Before we start coding, lets go back and take a closer look at our database so far. Can we
improve it? Do we have every data point that we will need? Let’s go back and starting looking at
our tables again, this time with a more critical eye.

Project Table
We have already seen how we can keep our data more accurate by making a separate table for
team members and linking it to the Project table. Are there other areas in this table that we can
improve upon?

The fact that we have a Project table indicates that we believe more than one project will be
tracked. It would be reasonable to assume that you may have multiple projects running
simultaneously. If that is true then we need a way to prioritize the various projects. If one project
is required while another is just for fun, we should be able to rank them on a priority level. In this
case we can add a new field for Project Priority.

Another field that you might consider is the current status of the project. Is the project in the
planning stage? Is it in the flight test phase? Has the project been concluded? Here are some
common terms related to project status:
• Upcoming: This is a project that's still under review.
• Active: The project is approved and work is still being completed.

20 | P a g e
 • Not started: The project is approved but has not been started yet.
• Pending: The project has been approved and but an item is missing that prevents the work
from getting started.
• Overdue: The project is still in progress but that the deadline for completion has passed.
• Canceled: The project is no longer active before all work was completed.
• Completed: The project is finished.
This simple field can be very valuable later when sorting projects or getting an idea of how far
along each project has progressed. We will call this field Project Status and set it as a text field.

Another field that we might consider is a “Notes” field. We have a field that provides an
overview of the project and we are going to link to a table with our objectives, but if we want to
write a note about the project we don’t have anywhere to do that. A Note field can be used as a
type of ‘catch-all” – a place to insert comments or data that may not be captured elsewhere. If
you find it is collecting a lot of similar data it can motivate you into updating the database with a
new field or even a new table to collect that data. In this case we will simply create a new called
field called Notes as assign it as a text field.

Finally, most projects have a logo. We can include it in our database by creating an image field.
Later when we create forms and reports we can include the logo to help identify the project. In
our Project table we can add the field “Project Logo” and set it as an image field.

Task Table
Looking at the Task table there are some additional fields that we may want to add. Some of
these will be similar to the new fields added to the Project table. We probably want to add a
Status field so that we can track what tasks have been completed, which ones are being worked
on, etc. We may also want to add categories to the types of tasks. This will allow us to see where
the work is being performed, and what area dominates the workload. It can also help us see if
there is an area where things a bogged down. We should probably add a Notes field here as well.

Team Members Table

As we look at the Team Members table, we can see that there is a lot we need to break down and
supplement. We’ll start with the field “Name”.

Begin by breaking the field “Name” down into its component parts. This will include:
• Prefix (such as Dr., Mr., Miss, etc).
• First Name
• Middle Name
• Last Name
• Suffix (Jr., Sr., III, etc.)
By breaking the name down into the individual components we can more easily work with the
data. You can sort by last name or first name. You can use just the first name on an informal
email communication, or you can include the member’s full name for more formalized
documents. This is also helpful in avoiding duplicate names. So now instead of one field we now
have five fields for an individual’s name.

21 | P a g e
Not mentioned in the first draft of the table is the member’s address. You will likely want to
obtain the address if you want to send things to the physical residence of the member. An address
may be needed if you use the database to complete applications for events that require each team
member’s place of residence. Its common to add two address lines; one for the street address and
the second for things like apartment numbers.

Another item you may wish to add is the member’s birthday. This may be needed to verify the
age of a team member for an event or to purchase items. A current picture of the individual can
be stored much like the logo in the Project’s table.

You might want to track some of their team information, such a when they originally joined the
team, their current status (are they active, did they resign, etc), if they are qualified to act as a
project lead member, and when they left the team.

Like before, we may want to add a Notes field to address anything that is not covered in the
established fields.

We have made a number of changes to our original tables as we have gone back and reviewed
them. This is part of the on-going design cycle and we are not quite done. Let’s look at our tables
as they are currently designed.

Objectives Table
Key Field Name Type
 ID_Objective Auto
ProjectID Number
Objective Text

Tasks Table
Key Field Name Type
 ID_Tasks Auto
Task Project Text
Task Name Text
Task Category Text
Task Description Text
Task Priority Number
Task Assigned Member Text
Task Date Due Date
Task Date Start Date
Task Status Text
Task Completion Percentage Number

22 | P a g e
 Task Completed Date
Tasks Notes Text

Project Table
Key Field Name Type
 ID_Project Auto
Project Name Text
Project Type Text
Project Overview Text
Project ObjectivesID Number
Project Priority Number
Project Lead Member Number
Project Start Date Date
Project Due Date Date
Project Status Text
Project Completion Percentage Number
Project End Date Date
Project Notes Text
Project Logo Image

Team Members Table

Key Field Name Type
 ID_Members Auto
Name Prefix Text
First Name Text
Middle Name Text
Last Name Test
Name Suffix Text
Address Line 1 Text
Address Line 2 Text
City Text
State Text
Zip Text

23 | P a g e
 Email Address Text
Mobile Phone Number Number
Alternate Phone Number Number
Birthday Date
Joined Date Date
Current Status Text
Project Lead Qualified Y/N
Separated Date Date
High Power Certification Y/N
High Power Certification Level Number
Notes Text
Member Photo Image

Close, But Not Yet Ready to Code

We are close, but not quit ready to start coding. We have made a lot of updates to our tables. Do
they make sense? Have we created new issues as we tried to solve earlier existing issues? Can
we improve the database even more? Let’s go over the new tables and see what we find.

Reviewing Table Fields

As part of this round of updates we will take one more look at the fields in each table. We need
to be asking if there is enough information present to accurately track a project through the
database. Is there anything we need to add? Is there anything we should remove?

Objectives Table
This is now our smallest table. It’s job is to assign objectives to the correct project. We can leave
it as is, or we can modify it slightly by adding a priority to the objective. For example, lets say a
project has six objectives, but all six may not carry the same importance. Three of the objectives
may be absolute requirements. They have to be accomplished as part of the project. Two of the
objectives may be secondary, requirements that should be completed if possible. The project will
try to carry out these two objectives, but not at the expense of the three absolute requirements.
Our last objective may be one that would be nice to do, but no one is losing any sleep over it if it
never occurs. Given this is a real probability, lets add a priority to the objectives table.

Tasks Table
The Tasks table looks pretty good. With what we are attempting to accomplish with this database
project it appears we are in good shape. At this stage of our database development we don’t want
to get into things like materials and budgets. We can add that in later if desired. For now, there
are no additional adjustments needed here.

24 | P a g e
Projects Table
This table also looks good. Again, we are not going to be looking at budget days. At this stage of
the development process as we are keeping it simple. This table looks ready to go.

Members Table
The Members table had the greatest amount of changes of all the tables in our database. It went
from being one of the smallest tables to the largest. When you have big changes like this, it is
wise to go back and look them over. Will the changes you’ve made do what you expect, or did
they create bigger issues?

The changes in this table have mostly added detail to the member’s information. This detail will
be useful when completing applications and reports. There is one area that we may want to add
and this is tracking if the member is certified for High Powered Rocketry (HPR) flights. There
are three HPR certification levels. If it is possible the project you are working on will involve
HPR, we should add this to the Member’s table

With these few changes, it appears we have the fields we need in our database. Let’s look at one
more item and that is the subject of what I call “Lookup Tables”.

Lookup Tables
Lookup tables are tables that contain a “Master List”. These are typically very simple tables that
contain a primary key and a description, and no value appears more than once. For example a
lookup table of US states would contain the state name and a primary key. This types of tables
allow you to “lookup” information and connect it to another field in your database.

Lookup tables are very useful, especially when you want to make sure there is consistency in
your data. Consider one of the fields we have in three of the tables:
• Project Priority
• Objective Priority
• Task Priority

One user might enter the priority as a number, 1, 2, or 3. Another user may use terms such as
Critical, Major, Minor and Trivial. Another might like the terms High, Medium, and Low. While
there is nothing wrong with any of these terms, if all are used at the same time it will be very
difficult to determine what each priority means and if its use is correct. Is a High priority more
important than a Critical priority? We can avoid this by using a lookup table and restricting the
user to entering only the terms in the lookup table.

Another way of thinking about lookup tables is how a user will interact with that table through a
form. Going back to our lookup table that provides a list of US states, it would be rather common
to see this as a drop-down box on a form. Instead of the user typing in a state, they would simply
select the state from the options listed in a drop-down box. With this in mind, lets take another
look at our tables and see where we can use lookup tables.

25 | P a g e
Priority Lookup Table
We discussed this one in the preceding section, so let’s start with it. This would be a two column
table consisting of an ID and the priority text. This can be used for the Project Priority field, the
Objective Priority field, and the Task Priority field. These fields will link back to the Priority
Lookup Table ID.

Status Lookup Table

This is similar to the priority lookup table and can be used for the Tasks Status and Project Status
fields. Once again, a two column table consisting of an ID and the Status text.

Member Status Lookup Table

Here the lookup table is different in the descriptions provided for the member’s status. Entries
here might include “on leave”, “active” etc. This will be different from the Tasks and Project
Status descriptions. The construction of the table is the same – an ID and Member Status text.

Tasks Category
This lookup table will only be used in Tasks table and is constructed like the two previous tables
– an ID and the category text.

Project Type
This lookup table is only used in the Project table. Once again a simple ID and Type text link
back to the Project Type field.

States Lookup Table

We discussed this a bit earlier. This table provides a listing of all 50 US states along with
territories. You can adjust it to meet your needs. In our previous Lookup Tables it was a simple
design of ID and a descriptive text. In this case we might want to add an additional field where
you still have the ID but you have the state name spelled out in full and a second field that
contains the 2-letter state abbreviation.

That should do it for the lookup tables. We still have two more areas that we need to clean up
before we start coding; Field Types and Field & Table notation.

Field Types
As we started the design of our database we used very generic terms in the field type. Terms
such as “Text”, Number”, and “Date”. As we get ready to finalize our design we need to get
specific on the field types. It is very important that we get this correct now. If we try to change it
later it can lead to all types of problems including a loss of data. Time spent determining the
correct field type is time well spent.

Field types will vary depending on the underlying database. While there is some similarity and
overlap between the various databases, you must use the field types that are specific to the
database you are using. In this project we are using the built-in database engine in LibreOffice
which is the HyperSQL database management system, or HSQLDB. We can break down the
HSQLDB field types in general categories and then look closer at each specific type.

26 | P a g e
There are two general number categories – Integers and Floating Point numbers. It also has
several Date/Time fields as well as Text fields. We round out the categories with several
miscellaneous field types. Let’s start with our number field types, specifically integers.

Integer Field Types

An Integer is a whole number. It has no decimal point or fraction. It can be positive or negative.
In HSQLDB there are four different field types for integers. Each type is directly related to the
size of the integer.
• TINYINT
This is the smallest of all the integer field types (28). It has a range of 1 to 256 (if all
positive numbers) or -128 to +127 if negative numbers are needed.
• SMALLINT
This is the next size up integer field (216). It has a range of 1 to 65,536 (if all positive
numbers) or -32,768 to 32,767 if negative numbers are needed.
• INTEGER or INT
This is the most commonly used integer field type (232). It has a range of 1 to
4,294,967,296 (if all positive numbers) or -2,147,483,648 to 2,147,483,647 if negative
numbers are needed. It is also the field that can be used with the Auto Increment setting
for record numbers. As these are all positive numbers (there are no negative record
numbers) you can have over 4-billion records per table.
• BIGINT
This is the largest integer field and least likely to be used (264). It has a range of 1 to
18,446,744,073,709,551,616 (if all positive numbers) or -9,223,372,036,854,775,808 to
9,223,372,036,854,775,807 if negative numbers are needed. You may see this when
working with extremely large (such as in astronomy) or small (such as in sub-atomic
level) measurements.

Floating-Point Fields
A Floating Point is a real number – a number with decimals. It has two components – an integer
number to the left of the decimal point and a fractional number to the right of the decimal point.
The point can float anywhere to the left, right, or between the significant digits of the number.
There are two pairs of floating point fields in HSQLDB.
• DECIMAL and NUMERIC
These are very large numbers that are extremely accurate. They range from -1038 +1 to
1038 -1. You define these numbers by specifying the total numbers of digits (or places)
allowed and how many of those digits will be placed after the decimal point.
Example: DECIMAL(8, 3) indicates 8 total numbers, with 3 of the numbers to the
right of the decimal point. The largest number allowed under this definition would
be 99,999.999.
• DOUBLE or REAL
These are large numbers that are no so accurate. They range from -3.40E + 38 to 3.40E +
38. You can have a maximum of up to 15 decimal places. For more precise calculations
and accurate rounding use DECIMAL or NUMERIC.

27 | P a g e
Text Fields
Text fields are strings of characters. HSQLDB has four types of text fields.
• VARCHAR
This is a variable length field that can contain a maximum of 8,000 characters. You
define the maximum amount of characters in the field up to 8,000. With VARCHAR it
only uses the amount of space actually used by the characters. If you define a field to
have 20 characters, but one of your entries is only 6 characters, the database will only use
the 6 spaces required.
• VARCHAR_IGNORECASE
Same as VARCHAR above except letter case is ignored.
• CHAR or CHARACTER
This is a fixed size field that can contain a maximum of 8,000 characters. Like
VARCHAR you can set the size of the field. If you define a field to have 20 characters,
but one of your entries is only 6 characters, here the database will pad the empty spaces
to fill up the full 20 characters. This type of field is useful when you have fixed field
sizes that always use the same number of characters.
• LONGVARCHAR
LONGVARCHAR is an extremely long field size, up to 16MB in size. This is useful for
extremely long blocks of texts such as articles.

Date and Time Fields

There are three date and time fields that control how time is displayed
• DATE
This field type only accepts date entries. The default format is Year-Month-Day (YYYY-
MM-DD) or 2023-04-23.
• TIME
This field type only accepts time entries. By default it uses a 24-hour clock
(HH:MM:SS). So 1:41:32 pm would be entered as 13:41:32.
• TIMESTAMP or DATETIME
This is a combination of the Date and Time Fields. It combines both formats into a single
format of Year-Month-Day (YYYY-MM-DD) (HH:MM:SS). So 1:42:32 pm on April 23,
2023 would be entered as “2023-04-23 13:41:32”.

Miscellaneous Fields
There are two other fields that we want to address that don’t fit into any of the previous
categories.
• BOOLEAN or BIT
This is typically considered as a two state field – Yes/No, True/False, On/Off. However,
there is actually a third state of NULL. NULL is not a condition that is used in this setting
very often.
• LONGVARBINARY
This is used for image files up to 16MB in size.

Now that we know how the various field types are used, it is time to go back and look at our
tables and adjust the generic field types to the more correct field types. While we are looking at

28 | P a g e
the field types, we also need to determine the lengths for some of these fields. By choosing the
proper length you can maximize the efficiency of the database.

The last thing to update before we begin the actual coding is to adjust field names. Field names
should not have spaces, as that can cause issues later when trying to write out your code. You
should also use field names that make sense. Don’t use shortcuts here. Use a naming convention
that will make sense when you come back to review your work a year from now.

I try to use the table name as the first part of the field name. In our current database design we
have three tables that use a field name of “Priority”. To help alleviate confusion I would make
the field names of the three “taskPriority”, “projectPriority”, and “objPriority”. It is easy to tell
which table the field resides by using this type of naming convention.

Final Table Design

Here are the final design of the tables for our database.

tblTasks (Tasks Table)

Key Field Name Type Length Required
 ID_Tasks INTEGER (Auto) 
taskProjectID INTEGER (Link) 
taskName VARCHAR (Text) 100 
taskCategoryID INTEGER (Link) 
taskDescription VARCHAR (Text) 500 
taskPriorityID INTEGER (Link) 
taskAssignedMemberID INTEGER (Link)
taskDateDue DATE
taskDateStart DATE
taskStatusID INTEGER (Link) 
taskCompletionPercentage TINYINT (0-100)
taskDateCompleted DATE
tasksNotes LONGVARCHAR (Text)

lkupTaskCategories (Categories Table)

Key Field Name Type Length Required
 ID_TaskCategory INTEGER (Auto) 
taskCategory VARCHAR (Text) 75 

29 | P a g e
 tblMembers (Team Members Table)
Key Field Name Type Length Required
 ID_Members INTEGER (Auto) 
memberNamePrefix VARCHAR (Text) 10
memberNameFirst VARCHAR (Text) 50 
memberNameMiddle VARCHAR (Text) 50
memberNameLast VARCHAR (Text) 50 
memberNameSuffix VARCHAR (Text) 10
memberAddress1 VARCHAR (Text) 100
memberAddress2 VARCHAR (Text) 100
memberCity VARCHAR (Text) 50
memberStateID INTEGER (Link)
memberZip VARCHAR (Text) 10
memberEmail VARCHAR (Text) 150
memberPhonePrimary VARCHAR (Text) 12
memberPhoneSecondary VARCHAR (Text) 12
memberDateBirth DATE
memberDateJoined DATE
memberStatusID INTEGER (Link)
memberProjectLeadQualified BOOLEAN (Y/N)
memberDateSeparated DATE
memberHPRCertification BOOLEAN (Y/N)
memberHPRCertificationLevel TINYINT (1, 2, or 3) 3
memberNotes LONGVARCHAR (Text)
memberPhoto LONGVARBINARY (Image)

lkupMemberStatus (Status Table)

Key Field Name Type Length Required
 ID_MemberStatus INTEGER (Auto) 
memberStatus VARCHAR (Text) 75 

30 | P a g e
 tblProjects (Project Table)
Key Field Name Type Length Required
 ID_Project INTEGER (Auto) 
projectName VARCHAR (Text) 100 
projectTypeID INTEGER (Link) 
projectOverview VARCHAR (Text) 8000 
projectPriorityID INTEGER (Link) 
projectLeadMemberID INTEGER (Link)
projectDateStart DATE
projectDateDue DATE
projectStatusID INTEGER (Link) 
projectCompletionPercentage TINYINT (0-100)
projectDateEnd DATE
projectNotes LONGVARCHAR (Text)
projectLogo LONGVARBINARY (Image)

tblObjectives (Objectives Table)

Key Field Name Type Length Required
 ID_Objective INTEGER (Auto) 
objProjectID INTEGER (link) 
objObjective VARCHAR (Text) 300 
objPriorityID INTEGER (link) 

lkupProjectType (Project Type Table)

Key Field Name Type Length Required
 ID_ProjectType INTEGER (Auto) 
ProjectType VARCHAR (Text) 75 

31 | P a g e
 lkupPriorities (Priorities Table)
Key Field Name Type Length Required
 ID_Priority INTEGER (Auto) 
Priority VARCHAR (Text) 75 

lkupStatus (Status Table)

Key Field Name Type Length Required
 ID_Status INTEGER (Auto) 
Status VARCHAR (Text) 75 

lkupStates (States Table)

Key Field Name Type Length Required
 ID_States INTEGER (Auto) 
stateName VARCHAR (Text) 75 
stateAbbreviation CHAR (Text) 2 

One thing you may have noticed is that each table has a prefix to indicate its function. Tables
that start with the prefix “tbl” indicate that they are used to store data about the project and that it
often has changes and updates to that data. Tables with the prefix “lkup” indicates it is a lookup
table and the data stored here seldom changes.

32 | P a g e
Relationships
The following shows the relationships that will be used in this database design.

Relationship: 1 Relationship: Many

To
Table Field Table Field
lkupStates ID_States tblMembers memberStateID
lkupMemberStatus ID_MemberStatus tblMembers memberStatusID
tblMembers ID_Members tblMemberOrganization orgMemberID
tblMembers ID_Members tblProject projectLeadMemberID
tblMembers ID_Members tblTasks taskAssignedMemberID
lkupProjectType ID_ProjectType tblProject projectTypeID
tblProject ID_Project tblObjectives objProjectID
tblProject ID_Project tblTasks taskProjectID
lkupPriorities ID_Priority tblProject projectPriorityID
lkupPriorities ID_Priority tblObjectives objPriorityID
lkupPriorities ID_Priority tblTasks taskPriorityID
lkupStatus ID_Status tblProject projectStatusID
lkupStatus ID_Status tblTasks taskStatusID
lkupTaskCategory ID_TaskCategory tblTasks taskCategoryID

33 | P a g e
04
Creating the
Database Tables
In the last chapter we went through the preliminary design of our database. By the time we had
completed the exercise we had a total of five primary tables and six lookup tables. It is now time
to start the creation of the database and the development of the tables.

Create the Database

Begin by starting LibreOffice. The Start Center will appear and you will see a listing of all of the
various components that
comprise the LibreOffice
Suite. At the bottom of the
list is the “Base Database”
(circled in blue with the
number 1 next to it in the
graphic on the right). Click
on the Base Database link.

The Database Wizard will be

displayed on the screen.
Under Steps the “Select
Database” should be
highlighted (labeled as
number 2 in the graphic). If it
is not highlighted, select it.

On the right side of the

Database Wizard the option
to “Create a new database”
should be selected. HSQLDB
Embedded should be selected in the combo box (labeled as number 3). Make sure these items
are selected, then click on the “Next >” button (labeled as number 4).

The second screen of the Database Wizard, “Save and proceed” should be highlighted and
displayed. If it is not, click on “Save and proceed” to bring up the screen.

34 | P a g e
On the right hand side of the screen you will see
that LibreOffice is asking “Do you want the
wizard to register the database in LibreOffice?”
The default response is “Yes, register the database
for me.” The second option is “No, do not register
the database.” (see section labeled as number 1 in
the graphic to the right). For now, let’s keep the
registration option selected.

The next section (labeled a the number 2 in the

graphic) ask what you want to do after the
database file has been created and saved. The
default response is to open the database for
editing. You can also select to use the table
wizard. This we will leave unchecked.

At the bottom of the screen is a series of buttons.

Click on the “Finish” button (labeled as number
3). Despite the button saying “Finish” you aren’t quite done yet.

A Save as… dialog box will now be displayed. You need to determine where you will be storing
your database. You need to select a
location on your computer or
network that is accessible and has
adequate space to store your data
(see area circled in blue and labeled
as 1 in the graphic on the right). You
will also need to give your database
a name. I have labeled mine as
“RRA” (for Rocketry Research
Assistant) and I follow this with the
current date. As I make updates and
changes I will save copies of the
database using a current date. This
way I will always know what is the
most current version I am working on, and I can go back to a previous working version if I screw
up the current version. Once you have the name entered, click on the “Save” button (shown as
the number 3). Now the database will be created and saved.

Database Registration
So what is database registration in LibreOffice, what does it do and why would I want to register
my database? Let’s look at what does and doesn’t happen during this process.

First off, when you register your database you are not sending your data to the LibreOffice
servers. Your data is not going anywhere. The folks at LibreOffice are not getting updates on

35 | P a g e
changes you make to your database or the data within your database. Your data never leaves
your computer (unless you decide to send it somewhere).

Secondly, it is not making an entry into the Windows Registry. Remember that LibreOffice is
not just a software program that can run under Windows, it can be used on a number of operating
systems including Linux and Mac. So the Windows Registry is not used here.

So what happens when a database is registered? It makes that data (the tables and queries)
available in LibreOffice Writer and Calc.

If you open up LibreOffice Calc, click on “View” on the menu and select “Data Sources” a new
window will open at the top of the spreadsheet. If this is the first time you have used Base, you
will probably only see the Bibliography database listed (this is the example database that comes
with LibreOffice). Click on the plus sign (+) next to Bibliography and it will be expanded to
show Tables and Queries. Click on the plus sign next to Tables and it expands to show the table
“biblio”. Click on “biblio” and the table and it’s data will appear to the right (see graphic below).
These same registered databases will also be available in the Mail Merge Wizard in Writer.

By registering your database, it makes it easier to work with the data in Writer and Calc. If you
don’t plan to have these programs interact, you do not need to register the database. It will not
affect the operation of the database at all.

Table Wizard
The one option we bypassed during the creation of the database was to use the Table Wizard.
The Table Wizard is designed to help you create tables based on the particular activity you are
tracking. There are two broad categories listed; Business and Personal. Sample tables will be
listed based on the category selection.

We did not use the Table Wizard because we had went through the design process prior to
creating the database. We have already determined what fields we would need and the field
types. One thing the Table Wizard does not do is help with setting up relationships. Since we had
already determined what we would track and the necessary relationships, there was no need to
use the Table Wizard.

36 | P a g e
As you develop future databases or as you
expand this database to better meet your
needs, it is recommended that you go through
the same design process we used in Chapter 3
and not rely on the Table Wizard. However,
there is nothing wrong with looking over some
of the fields that the Table Wizard
recommends for different types of tables. You
may find something that you missed during
the design process. You might find that some,
part, or all of their recommendations might be
what you want.

The main thing is to design the database yourself and not limit yourself to the recommendations
made by LibreOffice, or to remove a field from a table because it is not listed in their
recommendations. Always take the time to design your database so that you understand exactly
what you are tracking and how the data interacts.

Creating Tables
With the database created and registered we can turn our attention to the creation of the database
components. We will start with the creation of the tables and their relationships.

Tasks Table
We will begin with one of the primary tables in the database, the Tasks table. We already know
how this table will look, as we spent the time to plan it out earlier.

We start by clicking on the “Tables” icon in the left

column. In the Tasks area on the right three options are
presented:
• Create Table in Design View…
• Use Wizard to Create Table…
• Create View…
We previously discussed the Table Wizard and won’t
be using it here. Create View is similar to creating a
query, but the tables need to be created first so we
won’t be clicking on that option. That leaves Design
View. Click on that link to begin the process.

37 | P a g e
New Table to Tasks Table
The graphic on the right is a new table ready to be
designed. You will notice that it looks similar to the
tables we created in the last chapter as we were
designing the database.

The third column is different from our design

tables. This column is used for notes or a
description of the field. I often use this to indicate
fields that are links to other tables, notes on unusual
fields, etc.

Below the fields is an area called Field Properties.

The options listed here will vary depending on the
Field Type that is selected. It is in this area where
you will select if the field is required, if the field
will Auto-Value, etc.

ID_Tasks Field
The first item entered into the table is the Field
Name “ID_Tasks”. As we enter in the Field
Name you will notice that the Field Type
automatically changes to “Text [VARCHAR]”.
In the Field Properties section is has defaulted
to “Entry Require – No” and “Length” is set to
100. No default entry is given. This is the
default for every field that is entered into the
table. As this will be an Auto-Value field we
will need to change the entries.

Begin by selecting the drop-down box in the Field Type column and change it to “Integer
[INTEGER]”. You will notice that the Field Properties box has changed. When you change
Auto-Value to “True” you will see that the Field Properties box has changed again. Base does a
pretty good job of only showing you the options that are available based on your entry.

taskProjectID and taskName

The next two fields we will enter are the
taskProjectID field and taskName field. Change
the Field Type for taskProjectID to an integer
field like we did for the ID_Tasks field. This
time leave AutoValue as “No” but change
“Entry Required” to Yes. You will notice that
in the Description column we made a note that
this field is linked to the corresponding field in
tblProject.

38 | P a g e
Next enter the field taskName. The default text field type will be displayed. We need to change
“Entry Required” to Yes, and we can leave the length at 100.

Date Field Entries

There are two Field Types that need to be
formatted so that they will display properly. The
first is the DATE field type. We have three fields
that use this field type in the Tasks table. To
adjust the formatting, click on the Format Field
button at the bottom of the Field Properties box.
The Field Format dialog box will appear (see
graphic on the right). Under Category, scroll
down and select “Date”. Under Format, select the
format you want to both enter and display the
date. In our database we used the International
Organization for Standardization which is “Year-
Month-Day”. You could select the format of US
convention of “Month/Day/Year”. Whatever
format you use, you should use it consistently
throughout the database.

Percentage Field Entries

Some of our Field Types use the Tiny Integer
[TINYINT] format, primarily to record the
percentage of the task or project that has been
completed. We can have the database display
these numbers as percentages. As we did with the
date formatting click on the Format Field button
at the bottom of the Field Properties box. The
Field Format dialog box will appear (see graphic
on the right). Under Category, scroll down and
this time select “Percent”. Here we need to go to
the Options area. In the Decimal places box,
change it to zero [0]. Change the “Leading
zeroes” box to zero [0] as well. Click the OK
button to save the formatting selections to the
database.

Finish Entering the Fields

Using the tables we created in the last chapter during the design process, complete entering in the
Field Names, Field Types and Field Properties for all of the data items in the Tasks table. Also
make sure that you properly format the necessary fields when needed. When you are done, the
table should look much like the one of the following page.

39 | P a g e
Save the Table
With all of the field information entered into the table,
make sure to save your progress. Click on the Save
icon in the upper left corner of the form (it looks like a
floppy disk). A single line dialog box will appear.
Enter in the name of the table and click the OK button.
The table is now saved as part of the database and will
appear in the Tables section on the Base main screen.

Complete Creation of the Tables

This completes the first table. You will need to create the rest of the tables that will be used in
this database. Follow the same procedure with each new table. Some of the tables are small and
so are quick to create. Others are larger with more formatting. Take your time as you create the
tables. Make sure you save your work as you go.

Relationships
Setting up relationships between tables is what actually allows the magic of relational databases
to occur. Data relationships are simply associations between data tables. In our data tables, all of
our tables currently have a ‘one-to-many’ relationship. This means that there is ‘one’ item in a
particular table that may be seen “many” times in a related table. For example, in our
membership table we will have a number of members. Each member will have an address that

40 | P a g e
includes the state they reside in. Our lkupStates table has each state listed once, while the related
State field in the Members table will have multiple occurrences of the state data. In this case the
lkupState is the ‘one’ and the tblMembers is the ‘many’. You can have other relationships as
well. They include a “one-to-one” relationship and a “many-to-many” relationship.

Create Relationships in Base

To setup the relationships in our database, we need to open the relationships screen. Go to the
menu bar and click on “Tools” then select “Relationships...” It will open the
Relationship Design screen. As we have not created any relationships yet, the
screen will be blank. There are only two active icons on the toolbar. Select the
“Add Tables” icon (see graphic on the right) to add tables to the design screen.

Relationship Design Screen Icons

The dialog box shown on the right will appear, displaying all of
the tables in the database. You will need to click on each table
then click on the “Add” button. Once all of the tables have been
added to the Relationship Design Screen, click on the “Close”
button.

When you add each table it will place a box that shows all of the
fields in the table on the Relationship Design Screen. As each
table is added it is placed next to the previous table. It is unlikely
this is the layout you will need to make sense of the relationships
you need to create. Lets start by making the screen make sense
to us.

Grab each table and move it to the right side of the screen. First
move tblProject to the center of the screen. This is our main
table and most of the other tables will interact with it. Expand the box downward so that you can
read all of the data fields.

Next move tblMembers to the left of tblProject.

Again, expand it out so that all of the data fields
are present. Highlight ID_Members in the
tblMembers box. With the field ID_Members
highlighted, keep the left mouse button down and
go to the projectLeadMemberID in the tblProject
box. When the cursor is over the
projectLeadMemberID field, the cursor will
change to a shortcut key. Release the mouse
button and you will see the relationship created.
The “1” next to ID_Members indicates that it is
the “1” in this relationship. The “n” next to
projectLeadMemberID is the “many” part of the
relationship. The line connects the two tables

41 | P a g e
together in this “One-to-Many” relationship. This ability to create relationships between tables is
a very powerful tool and it is what makes relational databases so useful.

Lets complete the rest of the relationships needed in our database. All of our relationships in this
database are “one-to-many.” The specific relationships for the data fields in the various tables is
shown in the table in Chapter 3. The table identifies all of the relationships in our database,
including the one we have just created. It identifies the “one” side of the relationship and the
“many” side of the relationship.

As you work on creating your relationships, try to arrange the tables so that it makes sense to
you. You should strive to make the relationship diagram as clear as possible. Wherever possible,
try not to cross relationship lines. When you do have to cross lines, try to make the clear which
line belongs to which pair of fields. You want your relationship table to be understandable so
that others can look at it and comprehend what you are doing. When you have completed linking
all of the relationships in the database, you should have a Relationship Design Screen that looks
similar to the one below.

Edit Relationship Links

There is one more thing we need to do as
part of setting up relationships in our
database. We must define what will occur if
the link in the relationship is modified or
deleted. To edit a link, begin by clicking on
the line that links the two fields together.
The line should become thicker and turn a
bright blue. Right click on that line and a
pop-up menu will appear with two options;
“Delete” and “Edit…” Click on the “Edit...”
option (See below).

42 | P a g e
This will bring up the Relations dialog box. At the top it shows both tables involved in the
relationship. Underneath is the fields involved. There are a series of radio buttons near the
bottom. These are used to identify what will be done if the primary key is updated or deleted.
The default action for both is no action taken.

The action taken depends on when the table is altered. First, we

will look at what to do if the primary key (ID_Members) of a
record is updated. The following options are available:
• No action
Changing the primary key is not allowed as it would break
the relationship between the tables.
• Update cascade
If the primary key is changed, the foreign key
(projectLeadMemberID) is automatically changed to its
new value.
• Set NULL
All records which contain this primary key will now have
no entry in the foreign key, but instead will be NULL.
• Set Default
If the primary key is changed, the value of the foreign key
originally linked to it is now changed to the default value.
It is best to avoid this option as it is possible to destroy the integrity of the database.

What if the record is deleted? The same four options with slightly different results.
• No action
It will delete the primary key record, but will not delete any associated records. It is best
to avoid this option as it results in error message the end user probably won’t understand.
• Delete cascade
It will delete the primary key record and all associated records.
• Set NULL
The primary key record is deleted. All associated records will have the foreign key set to
NULL.
• Set default
If the primary key is deleted, the value of the foreign key originally linked to it is now
changed to the default value.

In our database we want both the updates and deletions to cascade. Click on the Update and
Delete buttons and then click OK. Do this for all of the relations in our Club database.

Populate Lookup Tables with Data

At this point we have all of our tables set up. We can populate the lookup tables with some
information at this point. For example, we can enter all of the US state names and abbreviations
into the lkupStates table. We can populate the priority and category tables using the appropriate
terms.

43 | P a g e
This completes the creation of our database tables, both lookup tables and main tables. All of this
is what is typically referred to as the ‘back-end’ of a database program. When we “split” a
database this is the stuff that resides on the “back-end,” typically on a server. This data can be
made available to everyone who needs access to it.

In the next chapter we will begin work on the front-end of the program, starting with the creation
of the database forms. This is the interface that your users will employ to interact with the
database.

44 | P a g e
05
Creating
Database Forms
Forms are the primary method that most users will interact with a database. The first form we
will create is the Project Members form. This form is based on the Members table (tblMembers).
It includes the member’s demographic information – all of the information that was setup as part
of the Members table. We will start the creation of the form using the Form Wizard.

Creating the Members Form

From the main window in Base, click on the Forms
icon under the Database column. Then click on the
“Use Wizard to Create Form...” option in the Tasks
window (see figure to the right)

Once you click on this option you will see the

Database Form window open and then the Form
Wizard dialog box will appear. It will go through a
series of steps to help you create your form. For the
Members form, we will create the main form
using the Form Wizard.

Step 1 – Select Fields to Display in the Form

The first thing we need to do is identify the table and fields that we need to use in our form.
From the drop-down box labeled “Tables or Queries” select the table tblMembers. This will
populate the “Available fields” window with all of
the fields in the table. You can highlight each field
and click on the right pointing single arrow to
move them to the “Fields in the Form” window
one at a time (as sees in figure on right). Since we
will be using all of the fields in the form, click on
the right pointing double arrow (>>) to move all of
the fields over (If you add a wrong field, you can
remove it by highlighting the field then clicking on
the left pointing single arrow. Clicking on the left
pointing double arrow removes all of the fields).

You can also change the order of any field by first

highlighting the field in the “Fields in the Form”

45 | P a g e
window, then clicking on either the up and down arrows to the right of the window. Once you
have all of the fields, click on the “Next” button at the bottom of the dialog box.

We skip Steps 2 through 4 as we are not creating a subform to use with this form

Step 5 – Arrange Form Controls

In this step you will decide how you want
the fields to appear in the form. You need
to select how you want the fields on the
main form to appear (and a subform if
used. The form and subform do not have to
appear the same). Once the form is created
we can go in and change them. What this
step does is to give you a start on setting up
your form. For our Members form, select
the option for “In Blocks – Labels Above.”
(see Figure to the right). Then click on the
‘Next’ button.

Step 6 – Data Entry Mode

The next step allows you to decide how the
form will be used. For our Members form
we can use the default option of “The form
is to display all data” with none of the
restrictions selected (see figure on the
right). As you create other forms, you may
want the user to only enter data, so you
would select the option to enter new data
only and not to display existing data. Or you
might want to restrict how the user can
interact with the existing data, so you may
not allow them to modify, add, or delete
data. For our Members form we want to use
the form to enter new members as well as
update existing member data, so the default “The form is to display all data” is correct for our
needs.

46 | P a g e
Step 7 – Form Style
The next step in the Wizard is to select a
style for your form. Like the Form Control
Arrangement screen, this will get you
started and you can always go in and
change things around (which we will do
shortly).

For now, click on “Light Gray” under

“Apply Styles” and select “Flat” under
“Field Border” as shown in the figure on
the right. Then click the “Next” button.

Step 8 – Form Name

The last step in the process is to set the
name of the form. The Wizard prompts you
with the same name as the table. In this
case, we want to differentiate the table
from the form, so we will use the “frm”
prefix to identify it as a form. In the “Name
of the form” text box enter “frmMembers”.

Lastly, the Wizard asks if you want to

“Work with the form” or “Modify the
form”. Select “Modify the form” so that we
can make some design changes to it that
allow it to work better for us. When your
dialog box looks like the one on the right,
click on the “Finish” button.

The Wizard Result

When you click on “Finish” the Form
Wizard creates the form according to the
information we supplied. Your form will
probably look something like the one on
the right. While the form is usable in its
current state, a little bit of work on our part
can make it appear more professional and
make it easier to use. It will take some
time, but the effort will be worth it. Before
we begin modifying the Members form,
make sure you save your work.

47 | P a g e
Modifying the Members Form
If you have closed out the Member’s form you will need to open it
so we can begin making changes. From the main database window,
click on the Forms icon in the Database column like you did when
starting the Form Wizard. You should now see the form
frmMembers listed in the Forms window. Right click on the form
listing and select “Edit…” from the pop-up menu (see graphic on the
right).

Resizing the Text Boxes

One of the things you will notice about the forms generated by the
Form Wizard is that the text boxes are rather tall, much taller than
what you would normally see in a “regular” software program. This
offers the advantage of being able to use larger fonts and making the
form somewhat easier to see. However, it has the major downside of
using a lot of screen real estate to display a small amount of data. To allow our database to
appear more modern and professional, we are going to reduce the size of the text boxes on the
form.

We can do this rather easily at this stage of the process. First, make sure the “Select”
(arrow) icon is selected on the Form Controls vertical toolbar (see graphic to right)

With the Select icon is active, the mouse

cursor will remain as an arrow when you
move back over the form. Click in the
upper left corner of the form, and while
holding down the left mouse button, move
the cursor to the lower right area of the
form. You should see a blue dotted line
outlining the selected area (see graphic to
the right).

Release the mouse button. The blue line

will disappear and be replaced by small
green boxes on each corner and in the middle of the top, sides and bottom of the rectangle. Place
the cursor in any text box and right click. A popup menu should
appear. Select “Control Properties...” from the menu (see graphic to
right. Control Properties option outlined in red).

Note: If you click on an empty area of the form, the popup menu will
be different and the “Control Properties...” option will not be
available. Additionally the green boxes will disappear. If this occurs,
go back and click on the “Select” icon and highlight the text box area
as before.

48 | P a g e
The Multiselection dialog box will appear. There are
two items we want to adjust here. First, in the “Height”
entry (which should be blank), enter “0.20” (without
quotation marks). This will reduce the height of the text
boxes. Secondly, change the “Background color” from
“Default” to “White”. Your dialog box should appear as
in the figure to the right (outlined in red).

Next, click anywhere on the form and the several things

will happen. The Multiselection dialog box will
disappear, the text boxes will be reduced in height, the
text box background color will be changed and the
small green boxes will disappear.

The next thing we want to do is to change the

background color of the form from gray to white. Go
to the menu bar at the top of the form, click on
“Format” then select “Page Style...” from the menu
items. The “Page Style” dialog box will appear. Click
on the “Area” tab (1). Next click on the “Colors”
button (2) then click on the small white box at the end
of the top row of colors (3). Now click on the “Apply”
button (4) at the bottom of the dialog box. The
background of the form should become white (see
graphic to the right). If the form turns white, click on
the “OK” button (5).

Arranging the Data Fields

The next task is to arrange the data fields on the form so that they make sense to the person using
the form. We will put the member’s name
text boxes together, as well as address and
contact information. The graphic to the right
shows our initial rough layout (we have
entered some meaningless ‘test data’ to
provide some context for what the form will
look like).

You can arrange the form any way you

would like. Our example is just one way of
organizing the form. Take the time now to
organize the form as you want it. In the next
section we begin modifying the fields to
allow for easier data entry.

If you have moved the data fields around or resized any of the fields, you may have also noticed
that when you click on a data field, both the data field and the label are selected, and the label

49 | P a g e
moves and resizes with the data field. To prevent both the label and the data field from being
selected, hold down the control (CTRL) key on your keyboard when you click on the data field
or the label. You will notice that whichever one you clicked on is the only one that is highlighted
with the small blue boxes.

Cleaning Up Labels
To begin cleaning up the labels, first use the CTRL and click combination to highlight the label,
then right click on the label to bring up the menu
options. Select the “Control Properties...” option to
bring up the labels Properties dialog box.

The name and the label text both get their name
from one used in the Members table. Start near the
top of the Properties box. The “Name” entry is the
name of the label and is used to reference labels,
text boxes and other controls when we start to add
programming to our database. The “Label”
information is what the user will see over top of the
data field. You may need to clean these entries up
by removing some letters or adding spaces. If
necessary you can add information to the label to
help clarify the data field.

I would recommend that you continue to use the prefix “lbl” in front of the label name. If you
use a text box I would recommend the prefix “txt”. In this form I would also get rid of “member”
in each label name. You will need to do this for all of the labels. You should also update all the
labels at this time.

Updating Date Boxes

If you look at the Members form, there are three date fields
(the member’s birthday, the date they joined the team and the
date they left the team). When we used the Form Wizard to
create the form, it recognized these three data fields as date
fields and uses a “Date Field” instead of a regular “Text Box.”
However, we need to go in and adjust a couple of the entries
to make the date field more user friendly.

The first thing we want to update is the date format. We want

to change it from the default “Standard (short)” date format to
one that displays the date in the recognized scientific format
(year-month-day). We will do the same modifications to all three of our date data fields. Start by
selecting a date field using the Control (CTRL) click method with the mouse. Then right click
and select “Control Properties...” from the pop-up menu. When the “Properties” dialog box
appears, scroll down to the item “Date format”. There is a down arrow button on

50 | P a g e
the right. Click on the down arrow to see all of the formatting options. From
this selection, click on “YYYY-MM-DD” to display the four-digit year.

Once that is done, we want to scroll down until we come across the option
“Dropdown”. Dropdown in this case refers to the dropdown calendar that
can be displayed. There are a number of users that prefer to use the calendar
as the method to input dates instead of entering numbers. Click on the down
arrow and change the default “No” entry to “Yes.” Complete the same
changes for all of the four date data fields.

Converting Drop-down List Boxes

The next update is to convert standard text boxes into drop-down list boxes. Lets start with the
text field for Team Membership Status. In our sample database we have added three different
levels of membership status (Active, Separated, Suspended). We will add these to a drop-down
list which will allow the database user to select from the appropriate status for the member.

Start by highlighting (CTRL-click) the Membership Status text box (1),

then right-click to bring up the pop-up menu. Select “Replace with” (2)
then click on “List Box” (3) (see graphic to the right). Next right click on
the MembershipStatus box, and select “Control Properties…”.

You will notice that the Properties box

now indicates that Membership Status is
a list box. We need to update several
properties on the Membership Status
box. As we did with the labels and date
boxes begin by adjusting the name. We
also need to change the list box to a drop-down box. In the
“General” tab, scroll down to the “Dropdown” option and
change it to “Yes.” Also make sure that “Multiselection” is set
to “No” (see graphic on the left).

Next we need to tell the control where to get the data to use in
the listbox. Click on the Data tab, then scroll down to “Type
of list contents” which should have the default value of
“Valuelist”. Change this to “SQL.”

Several things occur when you do this. The Bound field

option becomes active and is set to “1” (if it doesn’t show the
number “1” go in and change it). The second thing is that two
command buttons are now visible to the right of the “List content” box. Click on the command
button that has 3 small buttons at the bottom. (see graphic above. We have added a red circle
around the command button to highlight it).

51 | P a g e
When you click on the command button it brings
up the SQL Query Design screen. This design
screen makes it simple to develop queries that
can get information from the database, without
having to learn the SQL language. Begin by
clicking on the “Tables” option button (1), select
the table “lkupMemberStatus” (2) then click on
the “Add” button (3). The table will appear at the
top of the query design screen (4). As this is the
only table we need for this list box, click “Close”
(5) to close out the “Add Table or Query” box.

We need to provide a list of the different

membership statuses, and this query is easy to
create. First click on the memberStatus field and drag it down to the
first column. It places the field “memberStatus in the field row and
lkupMembershipStatus in the table row. Since this is the column
that will be displayed in the list, we are going to change the Sort
option to ascending, so it will place the items in alphabetical order.
Now do the same thing with the field ID_MemberStatus and drag it
to the second column. This time, when you are done, it should look
like the graphic on the right. Then click on the “save” icon (the
floppy disk icon).

Repeat the same process to change the State to a drop-down list

box. Use the table lkupStates as your source for the list. You can
decide if you want to sort the list alphabetically. Also perform the
same procedure to change the HPR Certification Level to a drop-
down list box using the table lkupHprLevel. It is probably wise to sort the column.

Formatting Phone Numbers

There are two boxes that allow the user to enter phone numbers for team members. To avoid
problems associated with the numbers being enter inconsistently (for example (123) 456-7890
versus 123.456.7890) we want to update these boxes so that only phone numbers can be entered
in a consistent fashion. This will reduce the possibility of different formats, letters or numbers
being entered accidentally.

Begin by selecting one of the telephone number boxes using CTRL-click. Now right click and
from the pop-up menu select “Replace with” followed by “Pattern Field” as we did earlier with
the list box. The Pattern Field is a control that allows you to setup preformatted fields and restrict
the types of input within the field. In this case, we will set up a visual indicator of what the phone
number should look like, as well as restrict the input to only numbers.

Select the Primary Phone Number box, right click and select “Control Properties...” You will
notice that the Properties box now indicates that txtPhonePrimary is now a pattern field.

52 | P a g e
There are two items we need to change in the
properties dialog box; the “Edit Mask” and the
“Literal Mask” fields. The Literal Mask is a
representation of what the user will see in the form,
to help them complete the form properly. The Edit
Mask is where we can limit the types of characters
that the user can input. We will start with the
Literal Mask.

It is common to see phone numbers where they

have parenthesis around the area code, a space
followed by three digits, a hypen, then the last four
digits (for example (123) 456-7890). Another
method of identifying the phone number is three
digits, a period, three more digits, another period, and then four digits (for example
123.456.7890). Any of these can be created in the literal mask.

We will use the formatting that starts with the parenthesis around the area code. In the literal
mask option on the properties dialog box enter a left parenthesis, three underscore characters, a
right parenthesis, a space, three underscore characters, a hypen, and finally four underscore
characters. It should look like this (___) ___-____ (see second red circle highlighted above).

Next we will set up the Edit Mask. The edit mask uses certain letters to represent specific types
of characters.1 With our phone number example we will only need to use two characters, the
letter “L” (which represents the symbol in the literal mask) and the letter “N” (which represents
the numbers 0-9).

Our first character in the literal mask is a left parenthesis and we want to keep that as is, so our
first entry in the edit mask is “L”. The next three characters are the numbers of the area code. We
want to replace the underscore characters in the literal mask with these first three numbers. Add
the letter “N” three times after the “L” to represent the three numbers. Next is a right parenthesis
that we wish to keep, so we enter another “L”. At this stage our edit mask should look like this:
LNNNL
Continuing on, the next character in the phone number is a blank space, so we add another “L”.
This is followed by the first three numbers of the phone number so add three more “N”. We now
have a hypen, so time for another “L” and then four more numbers which means four more “N”
characters. So the second half of our edit mask should appear as LNNNLNNNN. If we place the
first half with the second half, you should have the following (we have placed the literal mask
above the edit mask so you can see how each lines up with the other):

(___) ___-____
LNNNLLNNNLNNNN
1 In Appendix 1 we have included the Edit Mask Key which includes all of the special characters and what they
represent.

53 | P a g e
Your properties dialog box should now have both the edit mask and the literal mask filled in (see
graphic above with first two fields highlighted in the red circle).

To complete this formatting, we need to change the field “Strict Format” from “No” to “Yes”
(see graphic above with the third field highlighted in the red circle). The Strict format function
checks the data entry for compliance. Once the strict format function is activated (set to “Yes”),
only the allowed characters are accepted. Continuing to use our phone example, only numbers
are accepted when Strict format is set to Yes.

With the primary phone number completed, do the same thing for the secondary phone data
field. Don’t forget to save your work.

Form Title
We need to add a title to our form so users will know what form they are working on. Go
to the side toolbar and click on the “Label” icon (see the red circled icon in the figure to
the right). Your cursor will change to a cross with a small square in the lower right – much
the same as the group tool. Place the cursor where you want the label to be located, left
click and hold the mouse button down and draw the mouse down and to the right.

Double click on label to bring up the label properties box. There

are three things you may want to change here. They include
1. The name of the label (lblTitle – item 1 in graphic)
2. What the label says (Project Members – item 2)
3. How to anchor the label (“to Page” – item 3)
4. Font information (size and style – item 4)

To change the font values, click on the command button to the

right of the Font option. This will bring up the typical Font dialog
box where you can adjust what font you wish to use, the size of
the font, etc.

Lastly, you may want to change the background color. Click on

the command button to the right of the color option to bring up the various color options. Click
on “Default” to keep the background transparent.

Activation Order
If you were to put your cursor on the form and begin hitting the tab key
on the keyboard, you would likely find that the cursor jumps around the
form. We can use the “Activation Order” tool to arrange the tab order in a
way that makes sense to the user. The toolbar button is located at the
bottom of the Form Design toolbar and is circled in red in figure to the right.

54 | P a g e
Start by clicking on the “Automatic Sort” button. This
will perform a quick sort of all of the controls on the
form, sorting them first as you go across the form from
left to right, and then secondly from top to bottom. This
will likely put most of the items in a workable order,
but there may be some controls that don’t make sense
in this sort. For example, you will notice that “Address
1” is followed by “Photo” but instead you want the tab
to go to “Address 2.” To accomplish this, click on
“Photo” and then click on “Move Down” button until it
is located under “Secondary Phone”. Review the listing and move any other controls that are out
of order. When you have all of the controls in the order you want them, click the “OK” button.

Additional Forms
You have now completed the Project Member’s form. Using the same procedures that we just
used above, you can create the additional forms needed for your database. Make sure you include
all of the fields that you need, give them proper names (including prefixes) and check the tab
order when the form is almost done. Don’t forget to save your work as you go along.

The Projects Form

When we create the Projects Form, it starts off much the same as the Members form. We start
with the Form Wizard using the tblProject as the basis for the form. We add all of the fields to
the “Fields in Form” box and click on the “Next >” button.

The Form Wizard now wants to know if we want to set up a subform. The Members form didn’t
need one, but our Projects form does. We want to add a subform that will display the objectives
of the project.

Begin by clicking on the box that says “Add Subform”

(item 1). Once checked, two options become available:
• Subform based on an existing relation
• Subform based on manual selection of fields
Click on the first option (item 2).

The list box next to “Which relation do you want to

add?” will now become active. Select “tblObjectives”
(item 3). Finally, click on the “Next >” button.

55 | P a g e
The next screen that appears will ask what fields we
want in the subform. In this case there are only two
fields that we need:
• The objective
• The priority of the objective

Highlight each field and move it from the Available

Fields” list box on the left and into the “Fields in the
form” list box on the right. We also want the priority
to appear before the objective so make sure that the
Priority filed is above the Objective field. When
everything is correct, click on the “Next >” button.

The next screen is similar to the one we saw when we

set up the Members form. What is different is that it
allows us to set up different layouts for the main form
and the subform. We will setup the main form using
the same arrangement as we did with Members form.

For the subform we will select the table arrangement.

This will let us set up a scrolling list of objectives and
their priorities. When these options have been selected,
click on the “Next >” button.

The next screen will ask if we want to display all of the data. Like with the Member’s form, we
will leave the default selection with none of the boxes checked. The next screen asked for the
style. Again, this is exactly like the Member’s form.

The final screen wants a name for the form, and we entered “frmProject.” We also selected that
we wanted to modify the form. With this, we clicked on the “Finish” button.

Objectives
The top part of the screen will appear much like the Members form when we first created it.
These fields should be formatted in the same way that we did the fields in the Members form.
The bottom half of the form contains the Objectives grid, and that is what we will focus on in
this section.

We begin by adjusting the properties for the grid itself. Right

click in the main part of the grid and select “Control
Properties…” from the pop-up menu. We need to change
several properties. The name was changed to
“subGridObjectives”. Then scroll down until you see the
property “Background color…” (1) and change it to white.
Likewise change the “Border color…” to black. Make sure
that the “Navigation bar…” and “Record marker…” are both
set to yes.

56 | P a g e
Now let’s open up the Projects form
and you will see that the grid
displays the auto generated value
for the priority number in the first
column, while the objective text is
in the second column. We want to
change this to something that is
easier for the user to understand.

Begin by right clicking on the Priority column header (1). A

pop-up menu will appear. Scroll down and click on the
“Replace with” option (2). Another pop-up will appear. Now
click on the option for the “List Box.” At this point the
menus will disappear and nothing else appears to change.
However, in the background the column’s text box has been
replace with a list box. We now need to update the properties
of the list box so that it will provide the user with a listing of
options.

In the past when we wanted to adjust the properties of a control we would right click on that
control and then select “Control Properties…” from the pop-up menu. However, doing that in the
grid brings up the properties of the grid and not the list box in the first column. When working
with a grid we need to change our method to access the column properties.

Begin by right clicking on the column header just as we did when we

changed the column from a text box to a list box. When the pop-up menu
appears scroll all the way to “Column…” and click on this option. This will
now bring up the control properties for the list box in that column (I wish
they would label this “Column properties…” as it would seem more
intuitive and in line with the “Control properties…” option).

On the “General” tab, change the Name and Label properties to

“lstPriority” and “Priority” respectively. Since we only have four
priorities we can change the “Line count...” from “5” to “4”. Next
click on the “Data” tab. Here we need to change the “Type of list
contents…” (1) to “Sql”. When you do this an additional button will
appear on the far right of the “List content…” line. Clicking on this
button will open up the Query Builder.

When the Query Builder opens, this time select the table
“lkupPriorities” from the list. Drag the field “Priority” into the first
column. Then drag the field “ID_Priority” into the second column. In
column 1 under “Priority” change the “Sort” option to “ascending”.
Also make sure both columns are listed as visible (see graphic to the
right). Make sure you save your work as you go through the process.

57 | P a g e
Now the user will see the priority text in the first column. When they click on the column, a drop
down list of all of the priorities will be available for them to select.

Task Form
The Task form is used to assign project team members to a specific task for the project. This
form is created like the other forms in the database. There is one thing that is different in the
Task form that is not in any of the other forms, and that is the drop-down list box that provides
the members names. This is similar to our other drop-down lists that we have done in our other
forms. What makes this list different is how the member’s names are displayed. The list box only
displays one column, so we can’t list the member’s first, middle and last names in separate
columns and have all three name parts appear in the list box. We could use just the member’s last
name but if you have two members with the same last name you wouldn’t know which one was
being selected. To solve this issue, we use a technique called “concatenation.”

Concatenation is the process of linking several things together into a single item. Here we want
to display the member’s full name in the single column in the list box. We need to link the first,
middle and last name so it appears to the list box as one single name. In our example, we will
have the name displayed as “Last, First Middle” or “Sheppard, Alan Bartlett”.

We need to look at how we want the name to appear so that we can format it properly in the list
box. First, we start with the member’s last name. This is followed by a comma and then a blank
space. Next is the member’s first name, then another blank space and finally the middle name.
We must take into account the blank spaces and the comma as we concatenate the member’s
name.

LibreOffice Base uses two pipe characters (||) to indicate that different fields are going to be
linked together. We can start by adding the field names and the pipe characters to put together
the single name:

"memberNameLast" || "memberNameFirst" || "memberNameMiddle"

If we run the database we will see we get the result of “SheppardAlanBartlett”. A good start, but
not what our users really want to see. We need to add the comma and blank spaces between
names. We do this by starting with a single quote, then add the characters we need, and ending
with a single quote. Since we are concatenating this to our existing fields, we need to use the
pipe characters again. Now we have:

"memberNameLast" || ', ' || "memberNameFirst" || ' ' || "memberNameMiddle"

Running this produces the result of “Sheppard, Alan Bartlett” that we want displayed in our list
box. Now we need to add this to our list box.

As we have done before, begin by right clicking on the Assigned Member’s list box and
selecting “Control Properties...” from the popup menu. This will bring up the list box properties
dialog box. Click on the data tab. In the “Type of list contents” change the value to “Sql”. A
command button will appear at alongside the “List content” property. Click on the button.

58 | P a g e
This will bring up the SQL Query Design
tool we have used before. Add the table
“tblMembers” to the tool. In the first
column we are going to add our
concatenation string that we developed
above and place it into the ‘Field’ row.
The next row is labeled “Alias” and here
we enter “FullName”. We want to make
sure that the “Visible” box is checked. In
the second column, drag the field
“ID_Members” into the Field row.
“tblMembers” should appear in the Table
row and the “Visible” box should be
checked (see graphic to the right). Click on the “Save” icon and exit out of this tool.

When you click on the dropdown box you

should see the concatenated member name
string in the “List content” field of the dialog
box (see graphic to the right).

Finishing Up Our Forms

You should now have at least three forms in your database. You can create ‘setup’ forms for
your lookup tables if you like. Doing so provides a common method of entering data. Once your
forms are completed you should go in and populate them with some data, either made-up test
data like we are using or actual data from a research project you are involved with.

59 | P a g e
06
Thoughts on Improving
the Rocketry
Research Assistant
If you have followed along from the beginning, you now have a working database that provides
some basic functionality. However, that doesn’t mean that the RRA can’t be improved. This
version of the RRA should be considered just the beginning, a foundation to build upon. Let’s
take a look at some of the changes, updates and improvements that can be made to the system.

Reports
Reports are a common item in just about any database. In this early version of our database we
haven’t added them in yet. You do not need to wait for us to create the reports, however. You
can create your own reports using the built in reporting tool in LibreOffice Base.

Additional Forms
As we noted earlier, this is just the beginning of the database. There are a lot more things that we
can add over time. Some of the items that we can look at adding are:
• A checklist tool
• Launch record/log
• Construction log
• Maintenance log
• Background research
• Materials log
• 3D printing log

This is just a small example of things that can be added. Think about your projects and what you
need to track. Then look at creating the tables and forms to meet that need.

Additional Data Fields

As you look at the data tables we have created thus far, are there other data fields you feel are
missing that should be included? While we have included HPR certification levels, there is
nothing in the database as far as being a member of other rocketry organizations (such as the
National Association of Rocketry, the Tripoli Rocketry Association, etc.). Is this something that
should be included?

Another area that you might consider is social media. Should we have data fields for a Project
web page, Facebook page or Twitter handle? Are you creating a blog or video journal on

60 | P a g e
YouTube? Is there enough commonality between the various services that you can create a single
table that lists all of these organizations/services?

Error Correction
We currently have a working database and haven’t written a single line of code. Not bad.
However, that doesn’t mean our database can’t be improved by adding in code. We may want to
look at adding code that checks the data the user has entered to make sure it makes sense.
Another area is the display of error messages when something goes wrong. These and other code
additions can go a long way to make the database more user friendly and professional

Database Engine
The current version of the RRA uses the built-in HSQLDB database engine. However, we are
not restricted to that single database engine. LibreOffice Base also offers the use of the Firebird
database engine as an embedded database.

Split the Database

Along the same lines, you can also look at splitting the database into two parts; a data table back-
end and a program front end. There are a number of advantages to doing this, including being
able to use a wider variety of database engines.

Help File
While this Project Manual will certainly help you create the Rocketry Research Assistant, it
really doesn’t tell you how to use the Assistant. If you are the developer of the software, you start
with a good idea of how you want the software to work, and as you develop the program you are
using it. This means you know how to use the software and what to expect. But what about the
person who simply just wants to use the software? They don’t have the insight the developer has,
they need guidance in how to use the software. What forms are used and where are they located?
How do they perform certain tasks? What if I run into a problem?

All of these issues can be assisted through the use of a User’s Manual and/or a Help File. There
are a number of options that can be used. Some software projects will use a written manual
similar to this Project Manual. Another option is to create a Help file. This can be in the form of
a web site that is stored on the user’s computer or on the programmer's web site. Another option
is the creation of a Wiki that is accessed through the web site.

Some programmers go to great lengths to help their users. Some create short videos that act as
tutorials on how to perform certain tasks. These take you step-by-step through the process. Some
create videos that provide overviews of the software, often much like ‘advertisements’ about the
software.

61 | P a g e
07
Conclusion
This brings to a close Part 1 of our software development series on the Rocketry Research
Assistant. We hope that you found the manual helpful in creating the very beginnings of the
RRA and has you wanting to do even more.

As we move forward with this project we will continue to release a new project manual with
each software release. As is our practice we will explain how we develop the software and how
you can recreate the database. For those that simply want a working database, we will continue
to post updates to SourceForge.

Thanks to the technology available to us today, there is just so much that rocketeers of all ages
can accomplish with model rocketry. Rocketry research has made use of databases for a long
time. The ability to create your own database to better meet the needs of your projects is a great
skill to have. It is not the only thing that has entered the hobby of model rocketry in recent years,
however.

The addition of small, single board computers and microcontrollers has added a new dimension
to what you can do with a model rocket. The aspect of 3D printing and readily available CAD
programs allow you to design the supporting hardware needed for your project. It is indeed an
exciting time for rocketeers of all ages.

We hope that you found this Program Manual useful, and that it helps spur new ideas for new
projects in model rocketry.

62 | P a g e
Appendix

63 | P a g e
A1
LibreOffice Base Edit
Mask Key
The following keys can be used when setting up a Pattern control for data input.

Literal Mask and Edit Mask

When using a Pattern control, you have both a literal mask and an edit mask. A literal mask
displays what the user sees on the database form. For example, a phone number entry may
appear as (___) ___-____.
The edit mask determines what the user can actually enter into the pattern control. Again, using
our phone example the edit mask would appear as LNNNLLNNNLNNNN. The sample is show
below
Literal Mask: (___) ___-____
Edit Mask: LNNNLLNNNLNNNN

Strict Format
For control fields that accept formatted contents (date, time, etc) you can use the Strict format
function to check the data entry for compliance. If the strict format function is activated (set to
“Yes”), only the allowed characters are accepted. Continuing to use our phone example, only
numbers are accepted when Strict format is set to Yes. All alphabet or symbol entries are
ignored.

Key Function
a Mixed case letters (a-z and A-Z)
A Upper case letters. Lower case letters will be converted to upper case (A-Z)
c Mixed case letters and numbers (a-z, A-Z and 0-9)
Upper case letters and numbers. Lower case letters will be converted to upper case(A-Z and
C
0-9)
L This position cannot be edited. It will display the literal mask value
N Numbers only. Letters and symbols will be ignored (0-9)
All printable characters can be entered. This includes upper and lower case letters, numbers
x
and symbols

All printable characters can be entered, but lower case letters will be converted to upper case.
X
Numbers and symbols can also be entered

64 | P a g e
A2
Tables
tblMembers (Team Members Table)
Key Field Name Type Length Required
 ID_Members INTEGER (Auto) 
memberNamePrefix VARCHAR (Text) 10
memberNameFirst VARCHAR (Text) 50 
memberNameMiddle VARCHAR (Text) 50
memberNameLast VARCHAR (Text) 50 
memberNameSuffix VARCHAR (Text) 10
memberAddress1 VARCHAR (Text) 100
memberAddress2 VARCHAR (Text) 100
memberCity VARCHAR (Text) 50
memberStateID INTEGER (Link)
memberZip VARCHAR (Text) 10
memberEmail VARCHAR (Text) 150
memberPhonePrimary VARCHAR (Text) 12
memberPhoneSecondary VARCHAR (Text) 12
memberDateBirth DATE
memberDateJoined DATE
memberStatusID INTEGER (Link)
memberProjectLeadQualified BOOLEAN (Y/N)
memberDateSeparated DATE
memberHPRCertification BOOLEAN (Y/N)
memberHPRCertificationLevel TINYINT (1, 2, or 3) 3
memberNotes LONGVARCHAR (Text)
memberPhoto LONGVARBINARY (Image)

65 | P a g e
 tblProjects (Project Table)
Key Field Name Type Length Required
 ID_Project INTEGER (Auto) 
projectName VARCHAR (Text) 100 
projectTypeID INTEGER (Link) 
projectOverview VARCHAR (Text) 8000 
projectPriorityID INTEGER (Link) 
projectLeadMemberID INTEGER (Link)
projectDateStart DATE
projectDateDue DATE
projectStatusID INTEGER (Link) 
projectCompletionPercentage TINYINT (0-100)
projectDateEnd DATE
projectNotes LONGVARCHAR (Text)
projectLogo LONGVARBINARY (Image)

tblTasks (Tasks Table)

Key Field Name Type Length Required
 ID_Tasks INTEGER (Auto) 
taskProjectID INTEGER (Link) 
taskName VARCHAR (Text) 100 
taskCategoryID INTEGER (Link) 
taskDescription VARCHAR (Text) 500 
taskPriorityID INTEGER (Link) 
taskAssignedMemberID INTEGER (Link)
taskDateDue DATE
taskDateStart DATE
taskStatusID INTEGER (Link) 
taskCompletionPercentage TINYINT (0-100)
taskDateCompleted DATE
tasksNotes LONGVARCHAR (Text)

66 | P a g e
 tblObjectives (Objectives Table)
Key Field Name Type Length Required
 ID_Objective INTEGER (Auto) 
objProjectID INTEGER (link) 
objObjective VARCHAR (Text) 300 
objPriorityID INTEGER (link) 

lkupHprLevel (HPR Certification Level Table)

Key Field Name Type Length Required
 ID_HprLevel INTEGER (Auto) 
HprLevel VARCHAR (Text) 20 

lkupMemberStatus (Status Table)

Key Field Name Type Length Required
 ID_MemberStatus INTEGER (Auto) 
memberStatus VARCHAR (Text) 75 

lkupPriorities (Priorities Table)

Key Field Name Type Length Required
 ID_Priority INTEGER (Auto) 
Priority VARCHAR (Text) 75 

lkupProjectType (Project Type Table)

Key Field Name Type Length Required
 ID_ProjectType INTEGER (Auto) 
ProjectType VARCHAR (Text) 75 

67 | P a g e
 lkupStates (States Table)
Key Field Name Type Length Required
 ID_States INTEGER (Auto) 
stateName VARCHAR (Text) 75 
stateAbbreviation CHAR (Text) 2 

lkupStatus (Status Table)

Key Field Name Type Length Required
 ID_Status INTEGER (Auto) 
Status VARCHAR (Text) 75 

lkupTaskCategories (Categories Table)

Key Field Name Type Length Required
 ID_TaskCategory INTEGER (Auto) 
taskCategory VARCHAR (Text) 75 

68 | P a g e
A3
Relationships
The following shows the relationships that are used in the Rocketry Research Assistant.

Relationship: 1 Relationship: Many

To
Table Field Table Field
lkupStates ID_States tblMembers memberStateID
lkupMemberStatus ID_MemberStatus tblMembers memberStatusID
tblMembers ID_Members tblMemberOrganization orgMemberID
tblMembers ID_Members tblProject projectLeadMemberID
tblMembers ID_Members tblTasks taskAssignedMemberID
lkupProjectType ID_ProjectType tblProject projectTypeID
tblProject ID_Project tblObjectives objProjectID
tblProject ID_Project tblTasks taskProjectID
lkupPriorities ID_Priority tblProject projectPriorityID
lkupPriorities ID_Priority tblObjectives objPriorityID
lkupPriorities ID_Priority tblTasks taskPriorityID
lkupStatus ID_Status tblProject projectStatusID
lkupStatus ID_Status tblTasks taskStatusID
lkupTaskCategory ID_TaskCategory tblTasks taskCategoryID

69 | P a g e
A4
Relationship Graph
Relationships for Version 0.1.0 of the RRA database

70 | P a g e
A5
Sample Forms

Project Members form – Light Theme

Project Members form – Light Theme

71 | P a g e
Project Members form – Dark Theme

Projects form – Light Theme

72 | P a g e
Tasks form – Light Theme

73 | P a g e
A6
Data Types for the
Table Editor*
Table 1: Integer data types

Type Option HSQLDB Range Storage space

Tiny Integer
TINYINT TINYINT 28 = 256 | – 128 to 1 Byte
+ 127
Small Integer SMALLINT
SMALLINT 216 = 65536 | 2 Bytes
– 32768 to + 32767
Integer 232 = 4294967296 |
INTEGER INTEGER | INT – 2147483648 to 4 Bytes
+ 2147483647
BigInt BIGINT BIGINT 264 8 Bytes
Table 2: Floating-point data types

Type Option HSQLDB Range Storage space

Decimal Unlimited, up to 50
places in the GUI,
DECIMAL DECIMAL variable
fixed decimal point,
perfect accuracy
Number NUMERIC Unlimited, up to 50
places in the GUI,
NUMERIC variable
fixed decimal point,
perfect accuracy
Float (DOUBLE used
FLOAT
instead)
Real REAL REAL
Double DOUBLE DOUBLE Adjustable, not 8 Bytes
[PRECISION] | exact, 15 decimal
FLOAT places maximum
Table 3: Text data types

Type Option HSQLDB Range Storage space

Text VARCHAR VARCHAR Adjustable variable
Text VARCHAR_ VARCHAR_ Adjustable, range
variable
IGNORECASE IGNORECASE affects sorting
Text (fix) Adjustable, rest
CHAR | of actual text
CHAR fixed
CHARACTER replaced with
spaces
Memo LONGVARCHAR LONGVARCHAR variable

74 | P a g e
Table 4: Date and time data types

Type Option HSQLDB Range Storage space

Date DATE DATE 4 Bytes
Time TIME TIME 4 Bytes
Date/Time TIMESTAMP TIMESTAMP | Adjustable (0.6 – 8 Bytes
DATETIME 6 means with
milliseconds)

Table 5: Other data types

Type Option HSQLDB Range Storage space

Yes/No BOOLEAN BOOLEAN | BIT
Binaryfield BINARY Like Integer
BINARY fixed
(fix)
Binary field VARBINARY VARBINARY Like Integer variable
Image LONGVARBINARY Like Integer variable,
LONGVARBINARY intended for
larger images
OTHER OTHER OTHER | OBJECT

In the table definitions, and when data types are changed in queries using the “convert” or “cast”
functions, some data types expect information about the number of characters (a), the precision
(g, corresponding to the total number of characters) and the number of decimal places (d). The
types are CHAR(a), VARCHAR(a), DOUBLE(g), NUMERIC(g, d), DECIMAL(g, d) and
TIMESTAMP(g).

TIMESTAMP(g) can have only two values: ‘0’ and ‘6’. ‘0’ means that no seconds will be stored
in the decimal part (tenths, hundredths...). The precision of timestamps can be given only directly
using SQL commands. So if you are storing timings from some kind of sport, you must set
TIMESTAMP(6) using Tools > SQL in advance.

* From LibreOffice Base Guide 7.3, Appendix A “Common Database Tasks” Page 521
Permission: Creative Commons Attribution License
(https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

75 | P a g e
A7
Properties of Controls ‡

Data entry and Data display

Control Use
Text field Text entry
Numeric field Entering numbers
Date field Entering dates
Time field Entering times
Currency field Numeric entry, preformated for currency
Formatted field Display and entry with additional formatting, for example
using measurement units
List box Choosing between several different possibilities, also for
transfer into the database of values other than those displayed
Combo box Similar to a list field, but with only the displayed value
transferred, or you can enter new values by hand
Check box Yes/No Field
Options button Radio button; allows you to choose from a small number of
possibilities
Image control Display of images from a database and entry of images into a
database via a path selection
Pattern field Entry into a preset mask; limits the entry possibilities to
specific character combinations
Table control Universal entry module, which can display a whole table.
Integrated into this control are many of the above controls
Design
Control Use
Label field Heading for the form, description of other controls
Group box A frame around, for example, a set of option buttons

76 | P a g e
 Interaction
Control Use
Button Button with label
Image button Like a button, but with an additional image (graphic) displayed
on it
Navigation bar Toolbar very similar to the one at the bottom edge of the screen
File selector For selecting files, for example to upload in an HTML form—
not further described
Spin box Can only be used with a macro—not further described
Scrollbar Can only be used with a macro—not further described
Hidden control Here a value can be stored using macros and then read out
again,

‡
From LibreOffice Base Guide 7.3, Chapter 4 “Forms” Page 175
Permission: Creative Commons Attribution License
(https://creativecommons.org/licenses/by/4.0/), version 4.0 or later.

77 | P a g e
A8
References
 LibreOffice Base Guide 7.3
https://nextcloud.documentfoundation.org/s/qjFkGwpEEkNrt6f

 LibreOffice Base Data Types by E E Perry

https://eeperry.wordpress.com/2013/11/08/libreoffice-base-data-types/

 HSQLDB - Data Types

https://www.tutorialspoint.com/hsqldb/hsqldb_data_types.htm

 HyperSQL User Guide, Chapter 2. “SQL Language”

http://hsqldb.org/doc/guide/sqlgeneral-chapt.html

 How Many Words Are 8,000 Characters?

https://numberofwords.com/character-count/8000-characters/

 2023 NASA Student Launch Handbook and Request for Proposal

https://www.nasa.gov/stem/studentlaunch/handbook/index.html

 NASA Sounding Rockets User Handbook

https://sites.wff.nasa.gov/code810/files/SRHB.pdf

78 | P a g e
If You Enjoy Rocketry,
Consider Joining the NAR
If you enjoy model rocketry and projects such as the Arduino Launch Control System, then
consider joining the National Association of Rocketry (NAR). The NAR is all about having fun
and learning more with and about model rockets. It is the oldest and largest sport rocketry
organization in the world. Since 1957, over 80,000 serious sport rocket modelers have joined the
NAR to take advantage of the fun and excitement of organized rocketry.

The NAR is your gateway to rocket launches, clubs,

contests, and more. Members receive the bi-monthly
magazine "Sport Rocketry" and the digital NAR Member
Guidebook—a 290 page how-to book on all aspects of
rocketry. Members are granted access to the “Member
Resources” website which includes NAR technical
reports, high-power certification, and more. Finally each
member of the NAR is cover by $5 million rocket flight
liability insurance.

For more information, visit their web site at

https://www.nar.org

79 | P a g e