Project 2.0

Jan 16, 2012


Import an existing flat-file database into a SQLite table.

Create a simple Ruby program that reads and writes to the database.


Your roommate has started to acquire a pretty large collection of wine tasting notes, and to stay organized has attempted to use an Excel spreadsheet to keep track of his data.

"Maaaaaan," he says, "I don't think this excel spreadsheet thing is worth the trouble. Every time I want to add new data to the spreadsheet I have to find the right row position, insert a new blank row, and then enter in the data. Also, it's a pain to find things quickly and to make changes to certain things if I make a mistake. Maybe I should just have another glass of wine and forget about this whole spreadsheet thing."

"Dude, you should totally use a database rather than Excel," you reply. And you explain the merits of of managing data with a proper RDBMS, which you learned about in your CSCI403 class (what a coincidence!).

You ask your roommate for the Excel file that he was using, and he also provides it to you in pipe-delimited format.

"Maaaaan," he smiles, "if you can help me out I'll definitely look for your car keys... which I lost yesterday."


First you need to explore the data and get a feel for the domain (what are you modeling?).

Create a subdirectory of cs403 called project02. This is your dedicated working directory for all Project 2 files and data.

At this point your directory structure should look like this:


Step 1: Explore the Data

Navigate to your project02 directory and download the wine.xlsx file and wine.pd.txt file.

Create a README file inside your project02 directory.

In your README file, describe what Project 2 is. For example:

Fred Hubbard, CSCI403
Project 2

This is my working directory for creating a simple wine database and program for my irresponsible roommate.
Original data is in a provided wine.xlsx and wine.pd.txt file.

Next, take a look at the excel and pipe-delimited files. What problems or issues do you notice about the pipe-delimited file? Make a note in your README file; we will address these issues later.

Step 2: Import the Data into SQLite

Quickly read SQLite chapter 2 pages 32 - 46 ("The Command-Line Program" to "Summary"). Refer to page 40, "Importing Data" as a guide.

Create a database in your project02 directory called wine.db. Import the data from wine.pd.txt into this SQLite database. (This may seem like a no-brainer, but you will face some issues that will educate you about importing external data into a SQLite database. Start early, ask questions on Piazza, and feel free to share the steps you undertook with your fellow students.)

At the end of this step, you should have a table called wine that has appropriate "columns" matching what you see in the excel spreadsheet / pipe-delimited file.

Step 3: Create Your First Database-Backed Application

Some Ruby guidance will be given in class mid-week for this step. Your goal is to create a simple Ruby program that can read from and write to your wine database. When executed, it should display a list of all the wine in the database, and then prompt the user to enter new data. For example:

> ruby wine.rb
Listing all wine in the database:

Wirra Wirra McLaren Vale Shiraz
Price: $20
Purchased: 1/1/12
Drunk: 1/1/12
Rating: 5
Comment:I love Wirra Wirra, everything they produce is delicious.

Cakebread Cellars 2010 Chardonnay Napa Valley
Price: $25.99
Purchased: 1/2/12
Drunk: 1/3/12
Rating: 4
Comment: Pretty good, but not worth the money unless you are a rap star.


Enter the attributes of a new wine.
Name: Red Red Wine
Price: 2.00
Purchase Date: 1/20/12
Date Drunk: 1/20/12
Rating: 5
Comment: Make me feel so fine.

New wine saved. Smell you next time!

In order to implement this program, I recommend the following order of steps:

If some of these ideas sound totally foreign to you, then you are in the right class! We will discuss these tasks in class, and of course, when you're stuck please post a message on Piazza or drop by office hours.

Grading Criteria (200 pts)

Create a .zip archive of your project02 directory and submit to blackboard.

This is due by midnight on Monday, January 23.