Project 4.0

Jan 31, 2012

Objective

Project 4: Design and implement a simple relational schema given an ERD.

Scenario

*ggrrrrooowwwwwl*

"Wow, was that your stomach?" says your roomie from across the kitchen. "I'm making some chocolate-covered bacon with sprinkles, they'll be ready in about an hour."

"Thanks," you say, "I'm allergic to sprinkles. But I am hungry... if only I had some extra money to buy a breakfast burrito." Your eyes drift to the kitchen window as you ponder your money (and hunger) situation. "I've spent all my book money," you think, "and I don't have time to get a jo... whoa, what's that?"

There, up in the sky, you see an orange biplane flown by what looks like a rhinocerous with a furry tail.

You point. "Look! Look!" you exclaim to your roommate.

"Whoa, is that a... no way... that's a rhinocerous flying that bi-plane!"

"Huh? No I mean the sign!" you say, referring to the long banner flowing behind the orange plane. "Do you see that? It says 'Database application contest. Win $1000. Call (303) 653-3017 during office hours.' This is my chance! To eat!"

Your roommate leans up toward the window, staring and giggling, dumbfounded while you call the number and hear a nasal, pre-recorded voice say: "please visit the Project 4.0 page and follow the instructions in the 'requirements' section."

"Hey," says your roommate after you hang up the phone, "I happen to have an ERD for a cool database-backed application about wine I've been thinking about. Let's give this contest a shot!"

Requirements

Prepare the Project

Create a subdirectory of cs403 called project04. This is your dedicated working directory for this project.

Create a README file inside your project04 directory.

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

Horace Silver, CSCI403
Project 4

This is my working directory for some Ruby programs for creating and migrating a database schema.
This application will ________________ ...

Next, download this database, which provides us with a common starting point for this project. Let's imagine that this is the single-table database that we ended up with in Project 2. Save this database file in your project04 directory, but rename it to just wine.db.

Review the Model and Existing Database

First, open the provided wine.db database and execute a .schema command and a SELECT * FROM wines_tmp query to quickly browse the data.

Next, review the ERD your strange roommate gave you.

Implement a Schema-Creation Script

Create a file called create_schema.rb that executes some DDL (CREATE TABLE) statements against the wine database. This script should only create tables in the database. Use the following rules when choosing your table names and column names:

Use lower case names with underscores if necessary. Example: wines, manufacturers, cute_puppies.
Use the column name id for the primary key
Use lower case and underscores (if necessary) for column names. Example: name, purchase_date
For foreign keys (to be discussed in class soon) use the singular related table name and append _id. Example: manufacturer_id, country_id
For join tables (to be discussed in class soon) use the table names in alphabetical order separated by an underscore. For example, given a join table between wines and grapes, the table should be called grapes_wines
For join table column names, use the singular version of the related table names and append _id. For example, in grapes_wines the column names should be grape_id and wine_id.
Do not use a primary key in join tables.

As for datatypes, use what makes the most sense given the values you see in the wines_tmp table, what you can infer from the ERD, and from what you know about SQLite's supported datatypes.

Tip: Tinker with the database and your CREATE TABLE statements using the CLP or a gui tool first, then use those SQL statements in your program.

Requirement: I should be able to take the raw wine.db database and execute your create_schema.rb and witness the end result: that appropriate tables for your data model have been created in the database.

Implement a Schema Migration Script

Now that you have some tables created, you need to migrate the data in the table wines_tmp into their proper locations. Create a file called migrate.rb that contains a program that properly migrates date from the single-table wines_tmp and places the data in appropriate locations in your new tables. This will likely take a little trial and error. I recommend creating a simple script for SQLite that deletes all the data in your new tables, so that you can "clear out" your database and re-run your script until you are satisfied that it is correct.

Requirement: After running your create_schema.rb, I should be able to run your migrate.rb and see that the data that existed in wines_tmp is now located in appropriate locations throughout the database.

Grading Criteria (400 pts)

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