Database Creation Tutorial

Author: Rocky Wenz, MAST

Introduction

Mathematica version 8 contains all the tools you need to create a live database for your own use. Included are Mathematica style commands to do many things with databases. This means that you do not need to learn database commands (SQL) in order to use databases within Mathematica. The only downside is that you will need to learn some new Mathematica commands. But don’t worry, we will teach you those commands in this tutorial.

General Information and Why a Database?

First, you need to learn some terms and some other information. Let’s define what a database is, first. A database is a collection of information that is specific in it’s subject and the scope of the information stored. In other words, what is stored about a specific subject is tightly defined. Here is an example. Say we want to store information about a lot of microbes. Now normally, we could just write this information down if there was just a few, or if there were a few hundred that we wanted information on we could store this information in a Mathematica table and import it and export it. But what if we want to store information about several thousand microbes? Wikipedia estimates the number of identified bacteria alone at just under 9000, with the possibility that there are actually as many as ten to the ninth species of bacteria. This would be very unwieldy to store in a table. A database has no problem storing a large amount of information because it is stored, used, and manipulated mostly on your hard drive.

But for a database, we must be specific in what is stored. So let’s come up with specific information that will be stored. The name of the microbe is the obvious first thing to store. It is a string of text. (aside: we will actually have to be more specific that this when we create the database!) Next is an example JPEG micrograph of the microbe. Another bit of information we will want to store is the longest dimension in microns. We will store this as just the number. Next is a description of the shape, which is text that could vary a great deal in length. Lastly, we are going to be computing some information of our own about each microbe, so we will need room to store a 5 by 100 Mathematica table of numeric data.

More About Databases

A database has a name, and to use it requires a connection (in Mathematica this is called a ‘connection object’).  In order to store information in a database, one or more tables must be created. These tables are not the same as Mathematica tables. You can think of a database table as a spreadsheet that resides on your hard drive instead of your screen. Each table within a database must have a unique name.

In our example, each row will contain information about one microbe. This is called a database record or just a record. Each column will contain specific information about each microbe, such as the name or the size. These must have unique names, and an indication of the type of data that will be stored in this column, called the datatype.  Some datatypes require a size, also. There are a number of options to the command that defines a column, we will cover some of them later.

Let’s Create a Database

To create a database in Mathematica, you first tell Mathematica that you are going to need the code that handles databases loaded. This is done with the Needs command like this:

Needs["DatabaseLink`"]

Click on the above Needs command and press Shift+Enter. That is called evaluating a cell. You will want to do that within each of the cells containing a command below, also.

Step two in creating a database is to actually create and name a database. The easy way to do this is through a thing called Database Explorer.

Steps: Evaluate the cell below this one. Wait for the new window to appear. Click on File -> Open Connection. In the new window, click New. In the Wizard, click Next. Then fill in a name (no spaces and case is significant) and (optionally) a description. Click Next. Leave User Level checked here and click Next. Choose “HSQL (Standalone)” from the drop-down menu, and click Next. Choose a file name (no spaces) and enter it in the Database name field. You can use the Browse button to specify exactly where the database will be set up. Put “sa” in the username field (This is the default and makes things faster later). Click the Test button and wait for a response. It should say “Test Successful!”, if not, change the file name. Click Next. Click Finish. Click Cancel on the “Select Data Source” window. Close the Database Explorer window.

DatabaseExplorer[];

Although you would think we could use our new database right away, we can’t. To do anything with a database we must have a “connection object”.  So step three is creating a connection to your database.

The next cell, when you evaluate it, will pop up a small window with a list of the named databases. Choose the one you want to connect to, then click "Connect".  If a small window asking for a username and password opens, just click on "OK"(a blank username defaults to “sa”).

conn=OpenSQLConnection[]

the “Needs...” and “OpenSQLConnection” commands will always need to be done before anything else is done with the database. The OpenSQLConnection command returns the connection object, so this should be stored in a variable.  Notice below that all commands that access the database require this variable.

We are not done setting up yet, however. For step 3 we need to add at least one table to contain information. Tables consist of one or more columns. For this table, I will set up five columns. I’ll put their information in five variables first, to make the SQLCreateTable command a little easier to read. The SQLColumn command defines the column, but does not create it.

colone = SQLColumn["NAME", "DataTypeName"->"VARCHAR(50)", "Nullable"->False]

Above specifies a column named “NAME” (note no spaces). I recommend you name all columns with all capital letters. “DataTypeName”->”VARCHAR(50)” is an option that specifies the type of data that will be placed in this column. You should always specify this option. VARCHAR(50) means it is a text field 50 characters long. A list of all the possible data types is in the Mathematica tutorial “Creating Tables”. The Nullable option means that this column may not be left out when Inserting data into the database.  The default is to allow this column to be blank (null) when a new record is created. Next (below) is a field that can hold a JPEG micrograph called “Pic”.

coltwo = SQLColumn["PIC", "DataTypeName"->"VARCHAR"]

Since we don’t know in advance how many bytes the picture will take, we leave off the size part of the option. The database will store any number of bytes, up to about 2 gigabytes.  Column three will be for the size of the microbe in microns. Since we are  specifying that it will always be in microns, all we will store is the number. And since this number should not ever be very large and always an integer, we will make it a small integer field.

colthree = SQLColumn["SIZE", "DataTypeName"->"SMALLINT", "Nullable"->False]

Column four will be a description of the shape of the microbe. Since this column could contain a quite variable amount of text, we will give it a data type of “CHAR”. Like the PIC column, the database will allow a wide range of sizes of this column.

colfour = SQLColumn["SHAPEDESC", "DataTypeName"->"CHAR", "Nullable"->False]

And our last column, column five will contain the data we plan to generate as a Mathematica table. It will be encoded for storage in the database as a series of characters, so it also will be a datatype of VARCHAR.

colfive = SQLColumn["VISDATA", "DataTypeName"->"VARCHAR"]

Now we create the table. The SQLCreateTable command takes these arguments: the connection object, the table’s name(no spaces and should be all uppercase), and a list of the columns. You can, of course, put the SQLColumn commands directly within this command, if you wish,. If the table (and all columns) is created successfully, the command returns 0

SQLCreateTable[conn, "MICROBES", 
{colone,coltwo,colthree, colfour, colfive}]

Now to double check that the table was created correctly, here is how to get a list of the tables you have created within a database...

SQLTableInformation[conn]//TableForm

The third column output by the above command is the table name(s). Here is how to get the columns that make up a table:

SQLColumns[conn,"MICROBES"]

As you can see, the result is a number of SQLColumn objects.  The first argument of each object is the name of the table followed by the name of the column. Next is a series of options starting with what DataTypeName was specified for this column. Next is whether it is nullable (0=false, 1=true), and lastly is the length specified. It will be null for anything that does not need a length specified.

...And that’s it. We now have a database, a table within it, and a connection to it. All that remains is to clean up after ourselves. You should always close a database connection when you are done with it.

CloseSQLConnection[conn]