Billed as the open-source competitor to Microsoft Access, Kexi provides a rapid application development tool for database systems. Reprinted from TUX Magazine issue number 15.

Although for many the OpenOffice.org Base application is an obvious choice for building a desktop database application, it's not the only game in town. Kexi (http://www.kexi-project.org), a relatively new member of the KOffice family, allows you to create simple databases without learning all the intricacies of database development.

How a Database Works

Although you don't have to be a database guru to create databases with Kexi, you must have some basic knowledge of how databases work. This will help you not only to build better applications, but also will save you from spending hours fixing poorly designed databases.

Like any database system worth its salt, Kexi consists of three essential components: tables, queries and forms.

• Data within a database is stored in tables. Each table consists of rows and columns, and the data contained in one row is known as a record.
• Queries are used to extract, view and manipulate data. Queries can draw together data from many tables, and they also can have forms and reports based upon the data they produce.
• Forms allow you to view and edit the data in a table. You can think of forms as a GUI for the database; although tables are used to store data, forms are used to display and manipulate data in the table.

You can say that a database consists of three layers. At the bottom layer are the tables where the data is stored. Above the tables are the queries that extract and manipulate the data in the tables. And, finally, there are the forms that are based on the queries or directly on the tables.

Installing Kexi

Of course, before you can do anything useful with Kexi, you have to install it. As with any Linux application, you have several options here. If you're using a mainstream Linux distribution, you can install Kexi using your system's package manager. If Kexi is not available with your distribution, you can 1) download a package for your system from Kexi's Web site, 2) install Kexi using klik (http://kexi.klik.atekon.de), or 3) try Kexi using one of the many Live CD distributions, such as KNOPPIX or KANOTIX. Of course, you always can download the source code and compile it on your machine.

Getting Started with Kexi

Once Kexi is installed, launch it and choose Blank Database from the Choose Project dialog window. In the Creating New Project dialog window, select New Project Stored in File. This creates a database, where everything is stored in one file on your hard disk. Give the new database a name, and choose where you want to save it. A new blank database is now ready, but before you start working on it, let's take a closer look at Kexi's interface.

Figure 1. Kexi lets you create databases that are stored either locally or on a remote server.

The main window in Kexi is divided into three main areas (Figure 2). To the left, is the Project Navigator pane that contains database components, such as tables, queries and forms. To the right, is the Properties pane that allows you to define different properties for the currently selected element. Finally, in the middle, is the working area, where you actually build your database.

Figure 2. Kexi's Three-Pane Interface

Note: you can undock both the Project Navigator and Properties Editor panes, which can come in handy when you are working on a complex database and you want to free more space in the working area (Figure 3). To undock the pane, click on the tiny Detach arrow; to dock it back, click on the Dock arrow.

Figure 3. You can undock the Properties Editor window to free space in the working area.

For this project, let's build a simple database to use for keeping track of computer equipment in your company or home. Start with deciding what kind of data you want the database to store. Obviously, you'll want to keep track of the type of hardware (laptops, printers, routers and so forth), model, serial number, purchase date and so on. This means the table should contain fields like product for product type, maker for brand, model for model name, serialno for serial number, purchased for purchase date and notes for any additional information. Also, it's a good idea to have a unique identifier for each row in the table, which in database parlance is called a primary key. The primary key helps not only to identify each record in the database uniquely, but it also plays a crucial role in creating relationships between tables in more complex databases.

Now you are ready to add a table to your database. Left-click on the Tables icon in the Project Navigator, and select Create Object: Table. Switch to the Design View mode by pressing F7. Click on the first row in the Field caption column, and type id. From the Data type list, select Integer Number, and add an optional description in the Comments column. The id field is going to be the primary key, so set the Primary Key property in the Properties pane to Yes. This also sets other primary key properties—Unique, Required, Autonumber and Indexed—to Yes. Now, add the rest of the fields and their appropriate data types to the table as shown in Figure 4. Press the Save button to save the table, and give it a name, for example, hardware. To populate the table with records, switch to the Data View mode and add a few rows of data.

Figure 4. The Finished Table

The next step is to add a form that will act as the user interface for the database. To create a blank form, left-click on the Forms item in the Project Navigator and select Create Object: Form. This creates a blank form. First, you have to "connect" the form to the table, so you can use the form to view, add and modify data from this particular table. Click anywhere in the form, then click on the Data sources tab in the Properties pane (the tab in the middle), and select the hardware table from the Form's data source drop-down list.

Figure 5. Use the Data sources tab to "connect" the form to the table.

Now, if you take a look at the main toolbar, and you will notice some additional buttons. These buttons let you add so-called widgets to the form. The widgets include text boxes, check boxes, radio buttons and different design elements. Let's start with adding text boxes to the form. Make sure that you are in the Design View mode (press F7), click on the Text Box button on the main toolbar, and draw a text box in the form. Next, connect the text box with the appropriate field in the table. Click on the Data sources tab in the Properties pane, and select the product field from the Source field drop-down list. Switch to the Properties tab, and configure the text box's properties. At the very least, you might want to give the text box a more descriptive name, so you can locate it easily in the form using the Widgets tab in the Properties pane. Save the form, and give it a name, for example hardwareform. To see whether the form works properly, switch to the Data View mode by pressing F6. The text box should display the contents of the product field in the hardware database. If for some reason the text box is empty, try to use the Previous and Next navigation buttons at the bottom of the form to browse through the records. If everything works as it is should, return to the Design View mode, and add the rest of the fields. You also can add some design elements, such as field labels, headers, images and so on, to spice up the form a bit.

Figure 6. Adding Widgets to the Form

Your very first Kexi database is almost ready, but the introduction to database development won't be complete without taking at least a brief look at queries. Let's say you have several laptops, and you want to locate their info in the table quickly. You can, of course, use the search feature, but you also can create a simple query that allows you to find all laptops with a single mouse click. To create a query, left-click on the Queries item in the Project Navigator pane and select Create Object: Query. Switch to the Design View mode by pressing F7. In the working area, select hardware table from the Table drop-down list, and press the Add button to add the table to the query. Now, drag the fields you want onto the Columns part of the Query Columns window. Set the Criteria of the product row to Laptop. The final query should look like the one shown in Figure 7. Save the query, and give it a name, for example, findlaptops. Switch to the Data View mode to see the results of the query. That's it—your first Kexi database is ready to go.

Figure 7. The Finished SQL Query

Final Word

Thanks to Kexi, creating a database doesn't have to be complicated. Better yet, knowing the basics of database design, you easily can apply your skills to other database management applications, such as OpenOffice.org Base, Knoda, Rekall and many others. And, if you want to know how Kexi stacks up against other database systems, check the following page: http://kexi-project.org/wiki/wikiview/index.php?KexiComparisons.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British and Danish computer magazines. His articles cover open-source software, Linux, Web applications and other computer-related topics.