Wiring up a database

Today's DB Objectives:

Our canvas:

GitHub Repo with appropriate files.

I have created a simple, single-table, database for you to work with. It contains all of the words in the scrabble dictionary with some pleasant features (each row is identified by a set of alphabetical letters and contains the scrabble values of those letters and all scrabble-legal words built from exactly those letters).

To achieve the first goal, connecting with sqlite3 in a prompt, we will setup a virtual machine (server even) using cloud9. First create a github account. Next send me your github username and I will add you to cisc437 our github organization. Then use your github account to create a cloud9 account. Create a workspace to play with.

From there we will have a bash shell to interact with our first db. SQLite is a great starter DB because it is just a file, that makes it portable. Use the command sqlite3 scrabble.sqlite to open a prompt interacting with the DB (first make sure you have uploaded the file).

From within the DB session we can ask SQLite for a list of tables using .tables and given a table name (in this case racks) we can see its schema using pragma table_info(racks);.

You can also evaluate SQL queries by just entering them. One of my favorite "bingo" racks is "AENORST" because the words in it tell a story. Find the words in that rack by using the SQL query: select words from racks where rack="AENORST";

Now try to find all anagrams of "RETINAS".

Basic SQL Queries

SQL stores data in "tables" which we can call "relations" which is a math-y way of saying "tuple" which is a CS-y way of describing a vector. If you've used spreadsheets then you have a decent starting model by thinking of a spreadsheet as a table, each row is an entry. It isn't the best model because each column of the table has a specified type and a specified name and we often have many tables which relate to each other.

In this case the racks table has four columns: rack a string, weight an integer, length an integer, and words a string.

To get data out of the table we use a SELECT statement which has the basic structure of: SELECT {*, (col_1, col_2, ..., col_n)} FROM table_name_here (WHERE condition_1_here (AND condition_2_here)) (ORDER BY {func(), col_1_name {ASC, DESC} (, col_2_name {ASC, DESC})}) (LIMIT (start_number,) number_of_responses)

That is a lot of parts to toss at you, but let's clarify with an example. To generate a single, random, 6 letter rack (containing at least one word) which uses only 1-point tiles (AEIOU RSTLN) I could use the following query: SELECT * FROM racks WHERE length=6 AND weight=6 ORDER BY random() LIMIT 0, 1

Documentation Links

Connecting a program to a db

Learn by example. Get each of these four running on your own cloud9 and make an alteration of some sort.