Here's a fun way to learn about IBM Informix! Learn or teach the basics of Informix and relational databases with an interactive game called the Informix Detective Game (the game's theme is a crime investigation). The game teaches relational database concepts and shows how technology can be applied to solving real-life problems. The Informix Detective Game is based on the DB2 Detective Game created by Joanna Kubasta and Joanne Moore.

This tutorial has been updated for recent versions of Informix.

Share:

Pat Moffatt (pmoffatt@ca.ibm.com), WW Education Offering Manager, IBM

Photo of Pat MoffattPat Moffatt is the World Wide Information Management Education Offering Manager. She is a project manager in the IM Education team. She uses games like this to give students hands-on experience using databases.



14 April 2011 (First published 17 April 2008)

Also available in Chinese

Before you start

About this tutorial

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See Introduction to IBM Informix database software

This tutorial introduces new users of IBM Informix 11 to relational database concepts through a fun and interactive game called The Informix Detective Game. The recommended audience is small business owners, students, teachers and professors, or anyone wanting to gain an understanding of data management.

Objectives

This tutorial assumes that you have no prior knowledge of relational databases or SQL. By completing this tutorial, you will:

  • Gain an understanding of database concepts such as tables, rows (records), columns (fields), and values.
  • Learn about relational databases, data models, unique keys, primary keys, foreign keys, and table joins.
  • Gain a working knowledge of the primary SQL statements: select, update, insert, and delete.
  • Use the Informix command window to import a database.
  • Use dbaccess to create and execute SQL code.

We have included the solution set in a PDF, which you can download from this tutorial.

System requirements

This tutorial was written assuming you are using Informix Developers Edition running on MS Windows. To play this game, you should have IBM Informix installed, the InformixGame database loaded, and the clue file running.

Download Informix

  1. If you do not already have Informix 11 installed on your PC, download Informix from the product download Web site.
  2. Sign in with your IBM ID. Select Informix Developer Edition Windows 32 Version 11.70 xxxDE and click Continue.
  3. Provide the information requested and indicate your agreement with the license agreement. Click I confirm to continue.
  4. Verify that Informix Developer Edition Windows 32 is selected and that the file version number is 11.70. Click Download Now.

    Note: If Informix is already installed, there is no need to install another copy of the software. Proceed to the "Install the Informix Detective Game database" section of this tutorial.

Install Informix

  1. Once the download is complete, navigate to the folder containing the downloaded file.
  2. Unzip the file. Choose the option to unzip and install the file.
  3. If the unzip does not give the option to install, from the unzipped folder find and click ids_install.exe.
  4. The install wizard will walk you through the software installation process.
  5. In order to complete the install successfully, click Accept for each license agreement. Also, select Typical for each setup type, and make note of all the user Informix and passwords you are requested to create.

Install the Informix Detective Game database

  1. Ensure Informix is installed.
  2. Download the Informix Detective Game database file from the Downloads section of this tutorial.
  3. Create a directory called informixgame in drive C.
  4. Unzip the InformixDetectiveFile.zip file to that directory.
  5. From the Windows Start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170. A command prompt window will pop up.
  6. From the command prompt, type cd\informixgame to get to the informixgame directory. In order to create the database, you need to type dbaccess sysmaster createtables.sql. You should see the message Database created and Database closed.
  7. On the next line, type SET DBDATE=y4md- to set the date format.
  8. Then type dbaccess informixgame loadtables.sql to create the tables in the detective database. The tables are successfully created if you see four messages indicating rows were loaded. The rows loaded messages should reflect 151, 140, 10, and 140 rows loaded.
  9. Close the command prompt window.

Run the Informix Detective Game

In order to run the game's clue file (INFORMIXGAME.JAR), it has to be set up to open with Java Virtual Machine (JVM) software, for example: JAVAW.EXE. To set this up:

  1. Right-click the INFORMIXGAME.JAR file.
  2. Click Properties.
  3. Click the General tab.
  4. Specify Type of file to be Executable Jar File.
  5. The Opens with window should reflect a JVM that you have locally installed on your machine. If this is not the case, click Change.
  6. From the Open With pop-up window, locate and select your JVM software, for example JAVAW.EXE.
  7. Click OK.
  8. Click Apply.
  9. Click OK again.

You can now launch the INFORMIXGAME.JAR file. To do so, double-click the icon, or you can create a shortcut to the file for easier access.

To execute SQL commands

  1. From the Windows start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170.
  2. From the command prompt window, type dbaccess. A DBAccess window will open.
  3. From the main menu at the top of the interface, select Query-language.
  4. Select informixgame@ol_informix1170 from the list of databases.
  5. Select New from the Query-language menu.
  6. Type an SQL statement.
  7. Press the Esc key to exit the editor.
  8. Select Run to run the SQL statement.

Create a shortcut to the Informix Detective Game

To create a shortcut to the INFORMIXGAME.JAR file on your desktop:

  1. Right-click anywhere on the desktop.
  2. Select New > Shortcut.
  3. In the Type the location of the item field, type C:\informixgame\informixgame.jar.
  4. Click Next.
  5. In the field called Type a name for this shortcut, type informixgame (it should already appear by default).
  6. Click Finish.

Tips for using Informix

  1. To execute SQL statements, use dbaccess:
    • From the Windows start menu, choose All Programs > IBM Informix 11.70 > ol_informix1170. A command prompt window will pop up.
    • From the command prompt window, type dbaccess.
    • From the dbaccess main menu, select Query-language.
    • Select informixgame@ol_informix1170 from the list of databases.
    • Click New from the Query-language menu.
    • Type an SQL statement.
    • Press the Esc key to exit the editor.
    • Click Run to run the SQL statement.
  2. To modify your previous SQL statement:
    • From the Display menu, click Exit.
    • From the SQL menu, click Modify.
    • Edit your prior SQL statement.
    • Press the Esc key when done editing.
    • Click Run.
  3. If the width of the fields to be displayed is less than 80 characters wide, the results are displayed in a table format.
  4. If the width of the fields to be displayed is greater than 80 characters wide, the results for each record is displayed over multiple lines, as defined by the fields in the table.
  5. To see the count of the number of records for an SQL statement, click Next until the number of rows retrieved is displayed.

You are now ready to play the game!


Game scenario

You are an intern working in a security department for a large enterprise. As part of your training, you have been asked to participate in a crime investigation simulation to help locate a missing ThinkPad computer.

Learn the basics of Informix and relational database management by playing the Informix Detective Game. Uncover how database technology can be applied to business situations to help you manage your company information. This introductory-level tutorial will teach the basic SQL skills to get you to the next level of gaming, the Informix Detective Game.

Good luck, and have fun!


Introducing Informix and relational databases

What is a relational database?

A database is a collection of data stored in tables, where each table consists of rows and columns. You can think of a database as an automated replacement for traditional filing cabinets, where a database is the equivalent of the filing cabinet, a table is equivalent to a file folder within that cabinet, and the data within the file folder is equivalent to a spreadsheet of rows (records) and columns (fields), and where each cell of the spreadsheet contains a value. Because databases are automated, however, you can use them to manage and organize data so that you may quickly retrieve it, analyze it, and make key business decisions using it. Using a database, you can easily add, update, delete, and find (or query) data. Databases are used across all industries to manage everything from your credit card use, bank accounts, insurance information, taxes, and purchases.

With a relational database, such as Informix, data is organized and accessed according to relationships defined between data items within the various tables. Data in these tables can be managed using the Structured Query Language (SQL), which is a standardized language for defining and manipulating data in a relational database. This tutorial introduces you to both relational database concepts as well as the primary SQL statements.

Tables, records, fields, and values

With a relational database, such as Informix, data is presented as a collection of tables:

  • A database comprises a collection of tables.
  • A table consists of data logically arranged in columns and rows.
  • Data in the table is logically related, with relationships defined between different tables in the database.
  • Data is viewed and managed using mathematical principles and operations called relations, and table data is accessed using SQL (Structured Query Language).
  • A regular table can contain up to 500 columns, any number of rows, and up to 64 GB of data (not including large objects).
  • Each cell in a table stores a specific data item called a value.
  • Each column (or field) in a table consists of values of the same data type.
  • Each row (or record) in a table consists of a sequence of values, one for each column.

The Informix Detective Game database consists of four tables: employee, hardware, lobby, and security. The following graphic shows the data model for the Informix Detective Game. A data model is a graphical depiction of the various tables in a database and the relationships between them.

Figure 1. Informix Detective Game data model
Informix Detective Game data model - This graphical model shows the relationship between the four tables used in the game: employee, security, lobby, and hardware.

The preceding data model shows you that the Informix Detective Game database consists of four tables: employee, hardware, lobby, and security. It also shows you the type of information stored in each of these tables:

  • The employee table stores information about each employee such as their employee number, name, manager, office number, phone number, and badge number.
  • The security table stores information as to when each employee or visitor badged in and out of the building (date and time).
  • The lobby table stores the badge number assigned to each visitor by name.
  • The hardware table stores the serial number and type number for every computer along with its location and the ID of the employee who is its primary user.

The example in Figure 2 shows a subset of the data stored in the employee table. Note: This output screen is the result of the following SQL statement: SELECT employee_id, first_name, last_name, location, manager_id FROM employee.

Figure 2. Subset of data stored in Employee table
This image shows the subset of data stored in the Employee table. There are five columns displayed: employee_id, first_name, last_name, location, and manager_id. Fifteen records or rows are listed under these columns.

In the employee table shown previously, the columns (or fields) are:

  • employee_id
  • first_name
  • last_name
  • location
  • manager_id
  • extension
  • gender
  • hair_colour
  • badge_number
  • restricted_access

Note that there are many more records in the table, but only a subset of the data is shown here. Click Next to see additional records.

Each cell of the table holds a value. Acceptable values, or data types for each field, are set at the time the database is created. Examples include numeric, character string, binary string, and date and time data types:

  • Numeric - integer, decimal, or floating point numbers
  • Character strings - a sequence of characters, numbers, and/or symbols or graphics
  • Binary strings - binary objects such as audio or video files
  • Datetime - data types that are used specifically to represent dates and times

So for example, in the employee table, the value of the first_name field of the 1st record is William (a character string).


SQL statements

SQL statements

The language of relational database technology is the Structured Query Language (SQL). Invented by IBM in the 1970s, the SQL language continues to evolve and is the only way to access relational database data.

The primary SQL statements used to retrieve or update data are:

  • SELECT - queries data from one or more tables
  • INSERT - adds new rows into a table
  • UPDATE - changes existing rows in a table
  • DELETE - removes rows from a table

This tutorial will describe each of these SQL statements, in turn.

The SELECT statement

Use the SELECT statement to retrieve data from a table or tables. The syntax of the SELECT statement is:

SELECT column [,n] FROM tablename {,n};

For example, if you want to select all of the rows and columns from the lobby table without specifying any conditions to be met, issue the following statement.

SELECT * FROM lobby

This statement will produce the result shown in Figure 3.

Figure 3. Results of SELECT statement with no conditions
This image shows the result of a SELECT statement with no conditions. Three columns are displayed from the lobby table: first_name, last_name, and badge_number. Ten rows are retrieved and displayed in the results. The first row reads Tuan Ong V0058.

If you want to select all of the rows and columns from the lobby table, where the visitor's badge number is greater than 62, issue the following statement:

SELECT * FROM lobby WHERE badge_number > 'V0062'

This statement will produce the result shown in Figure 4.

Figure 4. Results of SELECT statement with a condition
In this image, the result of a SELECT statement with a condition is shown. Since the value of the badge number was specified to be greater than V0062, only five out of the ten rows are displayed in the results. The first row is Hong Bao V0063 and the last row is Friedman Schwenkreis V0067.

If, instead, you want to select only specific columns (for example, first_name and last_name) for each row in the lobby table, where the visitor's badge number is greater than 62, issue the following query:

SELECT first_name, last_name FROM lobby 
WHERE badge_number > 'V0062'

(Note that a comma needs to separate each column specified in the SELECT statement. Also note that the WHERE clause can be used to specify conditions that need to be met.)

The preceding statement will produce the result shown in Figure 5.

Figure 5. Results of SELECT statement with a condition and subset of columns
This image shows the results of a SELECT statement with a condition and subset of columns. In this case only two columns were specified to be displayed in the results: first_name and last_name. The first row reads Hong Bao. The second row is Zhichang Liang. Badge number is not listed due to the condition in the SELECT statement.

It is also possible to order, or sort, the rows using the ORDER BY clause. For example, if you want to display all records alphabetically by last name for those people having a visitor's badge number greater than 62, issue the following statement:

SELECT * FROM lobby 
WHERE badge_number > 'V0062' ORDER BY last_name ASC

(Note that it is possible to sort values in ascending (ASC) or descending (DESC) order.)

This statement will produce the result shown in Figure 6.

Figure 6. Sorted results of SELECT statement with a condition
In this image, the result is sorted alphabetically by using a SELECT statement with a condition. The same five rows are displayed as in the previous example, however, they are sorted by last name. The first row reads Hong Bao, while the second row is Frank Bergemann.

The SELECT statement will take any number of relational operators, including =, >, <, >=, <=, <> (in other words, not equal to), as well as a number of predicates, including LIKE, NOT LIKE, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, IN, and NOT IN. While this tutorial does not discuss all of these operators, it does introduce you to some of the operators that you will have an opportunity to use in the Informix Detective Game.

In some of the previous examples of the SELECT statement, you saw how the > (greater than) operator was used to filter certain visitors out of the results based on their badge number. The =, <, >=, <=, and <> operators can be used in the exact same way. The BETWEEN predicate, which may also come in handy when playing the Informix Detective Game, is described next.

The BETWEEN predicate compares a single value to an inclusion range of values (in other words, all values between a specified maximum value and minimum value). For example, to find the data associated with all visitor badge numbers between 59 and 66, issue the following statement:

SELECT * FROM lobby WHERE badge_number BETWEEN 'V0059' AND 'V0066'

This statement will produce the result shown in Figure 7.

Figure 7. Results of SELECT statement using BETWEEN predicate
This image shows the result of a SELECT statement using the BETWEEN predicate. The three columns shown from the lobby table are first_name, last_name, and badge_number. Eight rows have been retrieved. The first entry is Shahreyer Qadir V0059 and the last entry is Eric Herber V0066.

The INSERT statement

Use the INSERT statement to add data to a table. The syntax of the statement is:

INSERT INTO tablename [(column_name1, column_name2, ...)] 
VALUES (value1, value2, ...);

For example, to add a visitor named Informix Detective to the lobby table, issue the following statement:

INSERT INTO lobby (first_name, last_name, badge_number) 
VALUES ('Informix', 'Detective', 'V0062')

This statement will produce the result in the table, as shown in Figure 8.

Figure 8. Results of INSERT statement
This image shows the result of using an INSERT statement. Since an additional entry has been added there are now eleven rows displayed. The original ten remain the same with the eleventh entry being Informix Detective V0062.

The UPDATE statement

Use the UPDATE statement to change data in a table. With this statement, you can change the value of one or more columns for each row that satisfies the search condition of the WHERE clause. The syntax of the statement is:

UPDATE tablename SET column_name1 = expression1, column_name2 = expression2, ... 
[WHERE conditions_for_rows_to_meet_if_any];

Note: If you do not use the WHERE clause, then all rows will be updated.

For example, to change the first name of the visitor having badge number 62 to Joan, issue the following statement:

UPDATE lobby SET first_name = 'Joan' 
WHERE badge_number = 'V0058'

Now when you query the lobby table, you will see result as shown in Figure 9.

Figure 9. Results of UPDATE statement with a condition
In this image, the result of an UPDATE statement with a condition is displayed. Ten rows have been retrieved, however, the first entry has been changed with the UPDATE statement. It now reads as Joan Ong V0058.

If you had omitted the WHERE clause and issued the following statement:

UPDATE lobby SET first_name = 'Joan'

Then all of the visitor's first names would have been updated to Joan, as shown in Figure 10.

Figure 10. Results of UPDATE statement without a condition
This image shows the result of an UPDATE statement without a condition. The three columns shown are first_name, last_name, and badge_number. There are ten rows listed, however, with the UPDATE statement all entries for first name have been changed to Joan. The first row reads as Joan Ong V0058. The second row reads as Joan Qadir V0059.

The DELETE statement

Use the DELETE statement to remove records (rows) from a table. The syntax of the statement is:

DELETE FROM tablename [WHERE conditions_for_rows_to_meet_if_any];

For example, let's take a look at all of the data in the lobby table, as shown in Figure 11.

Figure 11. Data stored in Lobby table
This image shows the original data stored in Lobby table. There are three columns and ten rows.

To remove the record for the visitor with badge number 62 from the lobby table, issue the following statement:

DELETE FROM lobby WHERE badge_number = 'V0062'

This statement will produce the result shown in Figure 12.

Figure 12. Results of DELETE statement with a condition
In this image, the results of a DELETE statement with a condition is shown. Since the row containing badge number V0062 was deleted, there are now only nine rows listed.

The record for badge_number V0062 has been successfully deleted.


Joining tables

Unique keys, primary keys, and foreign keys

By definition, data in the various tables of a relational database is related, hence the name. In a relational database table, there are always one or more columns that uniquely identify each row. These unique identifiers are called unique keys. Let's look again at the employee table as shown in Figure 13.

Figure 13. Subset of data stored in Employee table
This image shows the subset of data stored in the Employee table. There are five columns shown here: employee_id, first_name, last_name, location, and manager_id. Fifteen rows are listed. The first row reads as 534373 William Miller B3-268 278354.

In looking at the employee table, the first_name, last_name, manager_id, gender, hair_colour and restricted_access columns can be ruled out as unique keys because they are not unique identifiers for a row. Notice that:

  • The third and fourth record both have first_name values of Oleg
  • The third and fourth record both have last_name values of Glembotsky
  • The first and second record both have manager_id values of 278354
  • The first and second record both have gender values of M
  • The second and third record both have hair_colour values of black
  • The first and second record both have restricted_access values of N

Note: The gender, hair_colour, and restricted_access fields are not shown.

A table in a relational database may also have a primary key. A primary key is used to join records in one table to related records in another table. It is important to note that each table can have only one primary key and that primary key must also be a unique key. When choosing a primary key, it is best to choose a column with values that will remain static. In looking at the employee table, the last_name, location, manager_id, and extension columns can be ruled out as possible primary keys because these values are all subject to change for an employee over time and are consequently not static. Based on the criteria that a primary key must be both a unique key and a static field, for the employee table the possible primary keys are employee_id and badge_number. When the Informix Detective Game was created, employee_id was defined as the primary key for the employee table.

The data model for the Informix Detective Game, shows the primary keys for each table, if any, and the relationships that have been defined between the various tables in the Informix Detective Game database, as shown in Figure 14.

Figure 14. Informix Detective Game data model
This graphical decpiction of the Informix Detective Game data model shows four tables and their relationship to one another. The tables listed are employee, security, lobby, and hardware. Each table is contained within a box along with a set of accompanying values. For example, the lobby table consists of a badge number, last name, and first name.

As discussed previously, employee_id is the primary key for the employee table. The employee_id is also a field in the hardware table. You can see from the data model that a relationship has been defined (as shown by the arrows) between the employee table and the hardware table using the employee_id field. The employee_id is a foreign key of the hardware table because it has been defined to be related to the primary key of the employee table. Notice that the location field is also common to both the hardware and the employee table, but is neither a primary key or a foreign key. Because an employee's location or office number is subject to change over the course of a career, location is not a static field and so to define it as a primary or foreign key would not be the best decision.

The data model also shows that badge_number is a primary key for the security table, and a foreign key for both the employee table and the lobby table (once again, see the arrows in Figure 14).

Primary keys and foreign keys define the relationships between tables in a database and can be used to link tables together. The process of combining data from two or more tables in a relational database is called joining tables. For example, let's say you need to know the name of the employee who is using the hardware asset that has serial number 78-23672. Querying all of the fields of the hardware table will only tell you the employee_id of the person using each machine as shown in Figure 15, as with the employee table, only the first set of records are shown.

Figure 15. Subset of data stored in Hardware table
This image shows the subset of data stored in the Hardware table. There are four columns: employee_id, location, hardware_tn, and hardware_sn. Fifteen rows are displayed.

In order to see the owning employee's name as well, the hardware table must be joined to the employee table. It is possible to join these two tables because a relationship was defined between them. The employee_id is the primary key for the employee table and a foreign key of the hardware table). To perform the join, issue the following statement:

SELECT * FROM employee, hardware 
WHERE employee.employee_id = hardware.employee_id

Note: In order to see the result in a table layout as shown in Figure 16, issue the following statement:

SELECT employee.employee_id, employee.last_name, employee.first_name, hardware.hardware_sn
FROM employee, hardware
WHERE employee.employee_id = hardware.employee_id
Figure 16. Results of joining Employee and Hardware tables
In this image, the results of joining the Employee and Hardware tables is shown. The four columns displayed are employee_id, last_name, first_name, and hardware_sn. Fifteen rows are listed in the result.

Now the owner name for each machine is reported.

Take another look at the Informix Detective Game data model as shown in Figure 17.

Figure 17. Informix Detective Game data model
This image shows a graphical depiction of the Informix Detective Game data model. Once again, the relationship between the four tables is displayed. These tables are employee, security, lobby, and hardware. The employee and security tables share a common badge number field. The employee and hardware tables share the employee ID field. Lastly, the security and lobby tables share the badge number field.

Notice that the following table joins are possible:

  • security (primary key = badge_number) to lobby (foreign key = badge_number)
  • security (primary key = badge_number) to employee (foreign key = badge_number)
  • employee (primary key = employee_id) to hardware (foreign key = employeee_id)

Also notice that the following tables cannot be joined because no relationship has been defined between them:

  • employee to lobby
  • hardware to lobby
  • hardware to security

Conclusion

You have used the Informix Detective Game and game database in this tutorial as a fun way to understand database concepts. You downloaded and installed Informix and then learned about relational databases, data models, unique keys, primary keys, foreign keys, and table joins. You gained a working knowledge of the primary SQL statements, and then used the Informix command window to import a database and use dbaccess to create and execute SQL code.


Downloads

DescriptionNameSize
Game filesInformixDetectivefile.zip14KB
Instructor manual (with solutions)Informix_Detective_Instructor.pdf507KB
Student manual (no solutions)Informix_Detective_Student.pdf400KB
Solutions onlyInformix_Detective_Solution.pdf45KB

Resources

Learn

Get products and technologies

  • Download Informix to try it out yourself. You can download a trial version of Informix Ultimate Edition, or a free edition of Informix Developer Edition for developer use, or a free edition of Informix Innovator-C Edition for small production workloads.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=301738
ArticleTitle=The Informix Detective Game
publish-date=04142011