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, a relatively new member of the KOffice family, allows you to create simple databases without learning all the intricacies of database development.

While 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 not only help you to build better applications, but also save you from spending hours on 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 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 can also have forms and reports based upon the tables that 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: while 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 those are the queries which extract and manipulate the data in the tables, and then there are the forms which are based on the queries or directly on the tables.

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 distro, you can: a) download a package for your system from Kexi’s website, b) install Kexi using klik, c) try Kexi using one of the many Live CD distros like KNOPPIX. And, of course, you can always download the source code and compile it on your machine.

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’s three-pane interface

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

For this project, we build a simple database that you can use to track computer equipment in your company or home. Start with deciding what kind of data you want the database to store. Obviously, you’d want to keep track of the type of hardware (laptops, printers, routers, etc.), model, its serial number, purchase date, and so on. This means that 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 “primary key”. The primary key helps not only to uniquely identify each record in the database, but it also plays a crucial role in creating relations between tables in more complex databases.

Figure 2: The finished table

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’s 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 with data.

The next step is to add a form that will act as the database’s user interface. 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 of all, 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 3: Adding widgets to the form

Now, if you take a look at the main toolbar, and you will notice some additional buttons. These buttons allow you to 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 “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 easily locate it 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 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 supposed to, return to the Design View mode, and add the rest of the fields. You can also add some design elements like field labels, headers, images, and so on to spice up the form a bit.

Figure 4: The finished SQL query

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 quickly locate their info in the table. You can, of course, use the search feature, but you can also 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 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.

Thanks to Kexi, creating a database doesn’t have to be complicated. Better yet, knowing the basics of database design, you can easily 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