Knowledge – Managed

Whereas data records are typically quite manageable and simply structured, the search queries needed to manage them are quite different. Anyone who has ever formulated SQL queries manually will be familiar with this phenomenon. Management systems can help users access existing databases, but they differ considerably in terms of their options and functionality.

For smaller companies, several free software solutions make using and managing even complex databases easier. To ensure a database application's efficiency, as well as acceptance among non-IT employees, the decisive factor is how easily it can be operated in front ends. Above all, this includes how easily users can create queries that give them quick access to the data.

In this article, I look at several free front ends: Base [1], Glom [2], Kexi [3], MySQL Workbench [4], and Valentina Studio [5]. In particular, I examine how well the software helps end users create databases and queries.

Base

Base [1] is the integrated database system in LibreOffice [6] and OpenOffice [7]. It is not uncommon for Base to end up automatically on your drive when you install these office suites. If not, you can usually install manually from the package sources.

When you start Base, a wizard prompts you to select whether you want to create a new database file or load an existing one. The wizard also gives you access to external databases on request. The wizard supports a variety of servers, including Adabas, MySQL, and Oracle's JDBC, as well as veterans like dBase, which dates back to the good old days of DOS.

To access these external databases, you might need additional drivers. Look for the appropriate driver packages in the package sources.

The Base main window opens with a pane on the left displaying various options. In four steps, you can transform unstructured data into a database with queries, forms, and reports.

After a click on the Tables button, you first create a database table. It should have a structure with data fields and types. Base again offers help through a wizard (Figure 1), but users can also create tables manually.

You then enter data records in the table and close the table when you are done. There is no need to save, because Base saves records immediately after input. The table name then appears at bottom center in the main window. Double-clicking on the table name opens the table view again.

To create a query, click on the second button, Queries, on the left in the main Base window. The options on the right let you use a wizard or a DIY approach to create the queries.

If you opt for the DIY approach, Base displays a pop-up window in which you first select the database. The database fields then appear in a list view at the top of the query window.

Double-click the data fields to select the ones you need. The values appear at the bottom of the table. Press the Run Query button to retrieve the data records and visualize them in a newly opened window segment below the buttonbar.

A click on the Design View On/Off button displays the corresponding SQL command. The fields can be edited, so you can adapt the SQL command to suit your needs before updating the records by pressing the Run Query button.

If you only want to apply certain selection criteria to the database, enter the criteria in the Criterion row of the table view.

Caution is advised if you want to use OR or AND. If you use these links in the SQL view and then switch to the Design View, Base unnecessarily duplicates the parameters in the criteria line. On switching back to the SQL command view, the program automatically inserts additional duplicates of the link commands, which quickly makes the SQL command code unclear.

Although this bug in Base has been known for several years, it has not been fixed in the current versions.

Base lets you create forms to simplify queries and manage data entry. Select the Forms option in the main window on the left. This again calls a wizard or lets you create a form manually.

The wizard prompts you for the data source. Decide whether a table or a query will serve as a source for the form. You can edit the form and use templates to arrange the data fields, adjust their labels, and define colors and borders.

After you create the first form, be sure to save it. In the main window, you will see the form with the data records it contains. To edit the form, right-click on the form file and select Open in Design Mode from the context menu.

You can access the individual setting dialogs in the Form Navigator, which you will find in the buttonbar at the bottom (Figure 2).

Glom

Glom [2] is another free database management system published under the GPL. Glom, which is maintained by the Gnome community, uses the PostgreSQL format for its databases. However, it cannot open PostgreSQL files. Instead, it can only handle files that were previously created with Glom.

Because the program has its own server, there is no need for a back end. Alternatively, Glom offers the option of connecting to an external server on the intranet.

Glom is available from the software repositories of practically all common Linux derivatives.

Glom first opens a window where you can create a new database or open an existing one. Several templates are available. If you are creating a new database, a pop-up window opens. Assign a name and add a description, and then press the Open button.

Glom has two operating modes: operator and developer. The operator is a user with limited rights who is not allowed to create new tables and only has limited authorizations for modifying existing databases. This account is always preset in Glom.

A developer, on the other hand, has full access to the databases and can also create new ones. To change the mode, click on Developer button in the Developer menu, then create a structure for the new table. Enable the Fields option in the Developer menu. Enter the field names and types in an overlapping window. The Name column denotes the object name in the database, the Title column denotes the column header, and the Type column denotes the field type (Figure 3).

Once the collection is complete, save the structure by pressing Close and select the Layout option in the Developer menu. In a separate window, enable the fields you want to appear in the form. Add field lets you conveniently select the desired fields from a list of available fields and add them to the list.

For a detailed view, which you access by pressing Details, you need to design the layout separately. The dialogs are very much like those for the list view. After pressing Close, fill the table with values. The best way to do this is to use the detailed view, where you will first want to enable operator mode in the Developer menu. Then press Add to add new records to the form and the database (Figure 4).

The Edit | Search menu or the keyboard shortcut Ctrl+F lets you search the current database for terms and values. An input line at the top of the detail view lets you call up a search term. Glom now displays the values it finds in the list view. However, the program does not allow several search terms to be linked together or placeholders to be used in the search line.

Alternatively, you can launch a query by entering the character string you want to find in the displayed table form's desired field. After you press Find, the first record found that matches the search criterion appears. You can navigate the individual datasets with the buttons First, Back, Forward, and Last.

An editor for entering SQL command sequences directly is not available in Glom, nor is a dedicated query function for saving queries for later use.

Kexi

Kexi [3] is an integrated database system that is part of Calligra Suite [8]. In the current 3.1.0 version, Kexi runs independently of KDE's Plasma desktop and Calligra Suite. The program works across platforms and is also included in the software repositories of various BSD derivatives.

In addition to its own database engine, which Kexi installs locally, the software works with external database servers, supporting MySQL and PostgreSQL servers.

When launched, Kexi asks whether you want to construct a new database or load an existing one. Note that when loading MySQL or PostgreSQL databases, you first need to install the necessary drivers from the package sources.

Clicking on the New option opens a wizard that helps the user create a database structure in just a few steps. When creating a new database, first select the Blank database option on the right side of the window. In the next step, the wizard prompts you for the storage option: Kexi saves the database either locally or on a server. In the third step, you define the project title and file name.

The main window then appears with the Create tab active. Below this is a buttonbar with which you can create tables, queries, forms, and reports. Pressing the Table button opens an empty table in the lower part of the window.

Enter the Field Name and Data Type in the table for each row (Figure 5); you can select the Data Type from a list. To the right of the table, the software displays a Properties tab that provides information about the field's properties. In this dialog box, you can adjust various attributes of the data field.

Once you have entered all the data fields, press Save. Enter a meaningful name for the table in a separate window; then, switch from design mode to data mode by pressing the Data button above the table.

An empty table with the created data fields appears in column form. Enter your data and allow it to be sorted in ascending or descending order. Additionally, a search function filters certain character strings from the database. Search criteria also let you delimit the search.

You do not have to save the individual records explicitly; Kexi does this for you automatically. Once you have finished entering the data records, switch to the Query button.

This dialog contains controls similar to those in Base. You can select the desired table in the middle of the selection field and add it to the query by pressing Insert. You now see the table's individual fields in the query area directly below. You can enter several tables in queries; drag and drop field names to create relationships among the tables.

A click on one of the field names moves it to the column view at the bottom of the program window. This window lists the columns relevant to the query in a table (Figure 6). Manually insert criteria for the field in separate columns in the table or hide individual columns.

Along with the criteria, you can also pass in conventional parameters. Press the Data button to activate the data view. You can assemble the results in ascending or descending order.

Once the query meets your requirements, save it by clicking on the button above the Query view. The SQL button provides an insight into the SQL code automatically generated by Kexi and lets you modify the code.

To check the modified SQL code's correctness, click the Check query button. Kexi outputs a warning message if the parameters contain errors.

After clicking the Form button, Kexi opens the form editor. All the existing tables and queries that serve as the basis for the form appear on the left in the program window's navigator area. The software displays numerous design elements for the form at the top horizontally, and an empty template appears in the middle of the editor.

You can insert various fields into the template, freely positioning the fields and changing their size. Once you are satisfied with the form, save it by pressing the Save button and entering a name.

MySQL Workbench

MySQL Workbench [4] is a MySQL front end developed by Oracle. Its functionality includes modeling and database design, among other things. The software is available under a proprietary license in Standard, Enterprise, and Cluster versions, each with different features. The Community Edition, on the other hand, is released under the GPL and is available from the software archives of most popular Linux distributions.

During the install, the routine creates a matching starter in the respective desktop's menu hierarchy. MySQL Workbench requires a running MySQL server, which you might also have to install.

In the first step, you need to connect the front end with the database server. To do this, select the Connect to Database option in the running application's Database menu. A successful connection opens the management interface (Figure 7).

To create a table for your database, first select Create a new schema in the connected server at the top of the Workbench buttonbar. A dialog opens in the center window pane. Enter a name for the new project, and define the character set. After you press Apply, Workbench displays an overlapping window showing the SQL code. You can modify the code manually before you click on Apply to save the changes.

The next step is to create the database. Go to the Schemas tab to the right of the Administration tab, where you will find the names of any existing databases.

To create a table, right-click on the Tables option and select Create Table from the context menu. MySQL Workbench displays the table editor, which lets you define the data fields, including attributes and field types (Figure 8).

Workbench is based strictly on SQL nomenclature. You won't find a Text field type; instead, you need to select the VARCHAR type in the case of text input; you can also specify the length of the text field in parentheses. After creating the table, finish by clicking Apply.

Workbench again shows you the window with the SQL commands. Check the data, make further modifications, if necessary, and complete the process by clicking Apply.

The table name appears on the left in the Administration tab. Right-click to display the context menu and select the first option, Select Rows – Limit 1000, to display the table, which appears in the center pane with all columns. Workbench displays the SQL code above the table.

You can start typing records at the bottom of the table. If you choose the Date data type, you need to enter the date in US notation. Otherwise, the routine will display errors when saving.

To start queries, you need to design an SQL script. Press the button labeled Create a new SQL tab for executing queries in the buttonbar. In the middle pane, enter the SQL commands and parameters. You are supported here by a selection function and a code check, which displays errors in the form of red crosses in front of the code line. When you are done, you can display the results by selecting Query | Execute in the lower part of the window. Save the query by selecting File | Save Script.

To replace the tabular view with a more sophisticated form, click on the Form Editor to the right of the table in the script editor. Instead of the table, Workbench now displays a ready-made form with a single view of the existing data records (Figure 9).

Valentina Studio

The Valentina Studio [5] database administrator software developed by US-based Paradigma Software is available under a proprietary license. The vendor [9] also offers the software free of charge for non-commercial use after registration.

Valentina Studio works with numerous database systems, but it can also use its own server locally [10]. Valentina Studio can also be used in heterogeneous operating system environments. Paradigma Software requires the user to activate both packages by email and activate them with a license; otherwise, they will only run in demo mode.

After the install, the server launches as a system service. You can open the graphical front end from the desktop. After the start, you are taken to a very tidy program window that automatically displays the available servers in the left column, Server.

To connect to one of the servers, press the connection and select the parameters for the connection in the selection window that appears.

To create a new database, click on the name of the connected server. Right-click to open the context menu, and select the Create | Database options.

Enter a name for the database in the configuration window. In the main window, a column view appears at the center, with the database name shown in the first column. A small triangle to the right indicates that you can now build the database from left to right in several steps.

In the second column, Tables, you can create a database structure. Right-click on the database name and select Create | Table from the context menu. Enter the table name in an overlapping window. A click on Create copies the table name into the second column from the left, and the Fields category appears in the third column.

Right-click on the table name and select Create | Field from the context menu. Enter field name, type, and attributes in the field definition dialog.

Open the input dialog by selecting Tools | Data Editor. The program window displays an empty table with individual data fields. Press the Add entry button to enter a data record.

To run queries in Valentina Studio, you need the SQL editor, which requires profound knowledge of the SQL instruction set [11]. In the Schema view, press the SQL Editor button and select the desired table. Enter the SQL commands required for the query. A click on Execute calls up the SQL command sequence and displays the results in table form with an action log (Figure 10). Press the floppy disk button to save the query for later use.

Conclusions

The five graphical database management systems discussed in this article (Table 1) focus on different target groups. Glom and Kexi are aimed at users with no previous database administration knowledge. Thanks to their user interfaces, they quickly deliver good results when creating simple databases and evaluating with queries.

In the case of Base, users already need some training to enter the SQL commands for more complex tables without errors. MySQL Workbench and Valentina Studio, on the other hand, are aimed more at professional users and administrators who want to work simultaneously with a large number of databases and also want to model and link them. The editors also support the visualization of complex relationships. These two professional packages require knowledge of SQL commands, because unlike Base and Kexi, they do not offer table wizards.

Valentina Studio also works with a variety of database servers and simplifies the use of databases in heterogeneous environments.