Building a database with Kexi

Probing Data with Queries

In addition to persisting data, a database derives information via a query. As mentioned at the outset, it's typical to write queries in SQL, but that's not particularly natural for the ordinary user. Kexi, again like Access, provides a graphical interface in which you can construct queries.

Now I'll show you how to build a query that will summarize all of the scores that have been recorded. To produce the summary, the student's name is drawn from the students table, the assignment details are drawn from the assignments table, and the score is extracted from the grades table. The ID fields scattered throughout the three tables match the corresponding records to one another.

To create the query, choose Insert | Query. In the panel that opens at center, choose a table name from the drop-down menu, and click Add. Do the same for the other two tables until all three are shown in the middle pane.

Next, click on the id field in the assignments table and drag the field to the activity_id field in the grades table. This correlates the fields. Do the same to correlate the id field in the students table with the student_id of the grades table. Your view should now resemble that in Figure 5.

Figure 5: Correlate fields between two tables to form a query.

The next step is to choose the fields you want to display. If you double-click on first_name, last_name, assignment, and score, these fields drop into the Query Columns list at the bottom. Figure 6 shows the center panel after adding the four fields.

Figure 6: Choose the fields to display.

If you switch to Data View, you should see something like Figure 7. It displays the results of the query.

Figure 7: The results of a query.

By assigning values to the Criteria field of a query column, you can limit results just to those in which you are interested. Also, you can sort results by setting the Sorting field. With a combination of tables, correlations, and query columns, you can tailor reports to your specific needs. By the way, if you prefer to write SQL code by hand, the query editor also offers a Text View in which you can author and validate your code.

Forms and More

Kexi also offers a form editor in which you can create data entry interfaces. Other tools import and export data and import database schemas. The current version of Kexi has some quirks, but the Kexi team promises a novel interface in v2.0, in addition to a report designer to produce professional-looking output. According to Kexi developer Jaroslaw Staniek, the forthcoming version of Kexi will connect to more databases – including SQL Server, Oracle, Sybase, and dBase – and feature integration with the entire KOffice 2 suite. For instance, Staniek foresees ODF document generation, without programming, and data sharing across the desktop.

Stay tuned. A future column will revisit Kexi 2 once the software is in widespread beta testing.

The Author

Martin Streicher is the former Editor in Chief of Linux Magazine and a freelance Ruby on Rails developer. When not writing prose or code, he collects art and toys and dreams of becoming a famous comic book author. You can reach Martin at mailto:martin.streicher@gmail.com.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy Linux Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Kexi vs. Glom

    Kexi and Glom aim to create databases with little overhead. We compare the power of these two tools.

  • KOffice 1.4

    The latest KOffice suite offers many new features – and even some new applications. We’ll show you what we found when we tried out KOffice 1.4.

  • OpenOffice.org Base

    If you need a quick and easy solution, try building your own database application in OpenOffice.org 2.0.

  • Knoda Workshop

    KDE’s Knoda provides an intuitive front end for an SQL database. This workshop introduces Knoda and shows how you can use Knoda to simplify common database management tasks.

  • Workspace: DAM with Glom

    Desktop databases are perfect for keeping tabs on miscellaneous data – from links and text snippets, to tasks and invoices – but building databases from scratch can be a daunting proposition, unless you use Glom for the job.

comments powered by Disqus

Direct Download

Read full article as PDF:

086-089_tools.pdf (561.33 kB)

News

njobs Europe
What:
Where:
Country:
Njobs Netherlands Njobs Deutschland Njobs United Kingdom Njobs Italia Njobs France Njobs Espana Njobs Poland
Njobs Austria Njobs Denmark Njobs Belgium Njobs Czech Republic Njobs Mexico Njobs India Njobs Colombia