Tag: database

Install Relational Algebra Interpreter on your Windows machine in less than 30 minutes

This is a quick blog post on how to install and use the Relational Algebra Interpreter on a Windows machine.

#1 What is the Relational Algebra Interpreter?

You only need the Relational Algebra Interpreter if you have a database that you set up through SQLite or MySQL or other database software (not Microsoft Access) and you want a shortcut to writing the SQL commands like SELECT and PROJECT.

The Relational Algebra Interpreter was written Jun Yang, now of Duke Uni.  It is used by many universities, including Stanford, to teach database programming.

#2  Install the Relational Algebra Interpreter?

This notes describe my installation.  I am not an expert and I am jotting down what I did mainly so that I can do it again another day.  If they help you, good; if not, sorry.

#2.1 Do you have Java installed on your machine. I am using Windows 7 on a relatively new machine and I have a Java JRE (runtime environment) installed but not a JDK (development kit).  I didn’t want to rush a notoriously convoluted Java install and make a mess of a new machine, so I fired up my old Windows XP where there is a JDK already installed.

#2.2 Decide where to put your RA interpreter.

The Interpreter comes with everything you need to run on SQLITE databases ( I am not sure on what else) and as I am only using the RA interpreter for a few weeks, I didn’t want to change my PATH statement – so I made a working folder, e.g. C:/A_RA_demo.

I went to Dr Yang’s page and downloaded the RA Interpreter and unzipped it.  I also transferred over from my other machine the test SQLITE3 database that I made earlier (see another post).

#3 Use the Relational Algebra Interpreter

#3.1  The RA Interpreter file runs from your command prompt (Go to Start; type cmd in the box and hit enter; change directory to work in your working directory, e.g., cd c:/A_RA-demo.

#3.2  The RA Interpreter comes with a  sample.db and a sample.properties file.  So to prove everything works, type into your command line, “java –jar ra.jar” and observe the changes on your screen.

#3.3  See what is in the sample.db by typing at the ra> prompt “list;”.

#3.4  Pick any of the “relations” (tables in plain English) and type at the ra> prompt “relationname;”.

#3.5 Type at the ra> prompt “quit;”

#4 Use the Relational Algebra with your own database

#4.1  To use the Relational Algebra with your own database, you must make a corresponding .properties file.

#4.2  Copy the sample.properties file and save it as yourdatabasename.properties.

#4.3  Find the right command line to edit. Most of the file is commentary.

#4.4  I was using a database made in SQLITE3 so I picked the SQLITE command.  You don’t have to add a 3.  You must change the sample.db to the yourdatabasename.extension.  I got held up here for a while because my database was a just a file without an extension.  When I typed in the database-name-only, it worked.

#4.5  Go back to the command line and make sure you are working in the working directory.  If not, look at step #2.2

#4.6 On the command line, type “java –jar ra.jar yourdatabasename.properties”

#4.7 Confirm you have read your database by typing at the >ra prompt “list;”

#4.8  All working?  So quit for now by typing at the >ra prompt “quit;”

#5 Use the Relational Algebra Interpreter with query commands in a file.

#5.1  Put the relational algebra interpreter commands in a text file and save in the working directory (e.g., query1.txt).  [You could test “list;” for now.]

#5.2  Go back to the command prompt and confirm you are in your working directory. Type “java –jar ra.jar yourdatabasename.properties –i query1.txt”

#5.3  You will get an answer, or more likely an error message.  Debug your query, resave query1.txt and rerun the java line.  Conveniently, recall the java line with the up key and enter.

Done!  Now all you have to do is learn the Relational Algebra syntax which you can also find on Professor Yang’s site.  It is a little mind blowing and I found tracking all the brackets quite hard but you get the hang of it with practice.

Make sure you start with a little database so you can check your queries by hand.  And search the Stanford website for class notes to help you on your way.

CHECK OUT SIMILAR POSTS

Posted on by Jo Jordan 2

Build your first SQLITE3 database in Windows in less than 1 hour

Here are some quick & dirty notes on getting SQLITE3 up-and-running on a Windows machine in less than an hour.

#1 Decide where to store SQLITE3 and where you will work

  • I decided to keep a copy of SQLITE3 in a sub-directory of my c:tools directory where I have stored other tools
  • I also decided to put a copy in the directory where I will keep the database. Being able to access SQLITE3 from the data directory saves me some typing later.
  • My data directory is what is sometimes called my desktop.  It is the Windows default setting.  To see the full “path” in Windows, click at the top of the screen where your browser bar usualy is.  C>USER> ….etc turns into the path looking like this C:USERUSERNAMEDocuments…  We’ll cut and paste this path later.

#2  Download SQLITE3

  • The Window binaries for SQLITE3 are here.
  • Download the command shell only.
  • Copy the zip file into both directories: c:toolssqlite and the data directory c:userusernamedocuments… [wherever you will be keeping your database]

#3 Prepare a little file to structure your database

  • As an example copy and paste this code into Wordpad
  • Save the file as “creategobble.sql”
  • Make sure it goes into the directory where you will be working (see above)
  • This file is going to make four tables in the new database.

#4 Go to your command prompt

  • Go to Start and type in “command prompt”.  You should see it come up top right
  • Select Command Prompt
  • Go back briefly to the directory holding your data and copy the path at the top (click on the >Libraries etc and the path will come up C:Users…etc
  • Go back to the Command Prompt and type in cd (for change directory) followed by the path (right click and paste).  Return
  • You will see that you are now working in your data directory

#5 Set up your database

  • Type in “sqlite3 gobble” (gobble is the name of our database)
  • You will see the sqlite come up
  • Type in this line: .read creategobble.sql <return>
  • Hey presto you have a made the little database

#6 Now get some data

  • Open Excel and label four sheets, one for each table : Person, Frequents, Eats, Serves
  • Add data to each table.  Either cut and past the information below, or open this teaching file from Stanford and cut and paste their data.

Person

1 Amy 16 female

2 Bert 20 male

3 Charles 22 male

Frequents

1 Amy PizzaHut

2 Bert Dominos

3 Charles PizzaHut

Eats

1 Amy mushroom

2 Amy cheese

3 Bert cheese

4 Bert seafood

5 Charles cheese

Serves

1 PizzaHut cheese 7.75

2 Dominos cheese 9.75

3 PizzaHut mushroom 8

4 Dominos seafood 11

  • Save each sheet as separate .csv file called persontable.csv, eatstable.csv, etc.

#7 Load your data into your table

  • Go back to your command prompt
  • If you have closed it, then check you are in the right directory.  If not look at #4.
  • Re-open your database “sqlite3 gobble”
  • Tell Sqlite3 that you are uploading .csv files.  Type in “.mode csv” <return>
  • Upload all four files one-by-one: .import persontable.csv person <enter>

#8 Prove to yourself that your data is in the database

  • Type “select * from person;” <return>
  • You should see your data.

There you are. You have made your first database in SQLITE3 and you are ready to play around with it and learn more commands.

CHECK OUT SIMILAR POSTS

Posted on by Jo Jordan 0

Who has had good results with Open Office Base?

Open Office Base vs Access

Wow, I’ve spent more than 2 weeks trying to get a database going in Base, part of Open Office.

Tonight, I retrieved an old copy of Access 2003, part of the Office suite.

I thought I had lost my touch. Access is definitely easier to use, prettier to use, and more powerful.

My attempts to use Base have been exhausting . . . and embarrassing.

Let’s see how quickly I can create a database in Access.

I’ll let you know!

But if you have created a good, working database in Base, I would love to hear from you.  I am trying to write a program where we input cost price, calculate selling prices and get them reviewed/changed, print labels with all the detailed bumpf required these days, and print labels and price lists.

UPDATE:  Access is definitely easier to use.  Easy to use.  Access 2010 is even better.

There is only one obscure thing to learn: that is how to write the look up commands.  If you follow the tab on the right of source, you can do it by numbers so to speak.

Editing a layout is also fiddly.

But it works and once you know, it is fast and stable.

Posted on by Jo Jordan 0