Database Keys Homework

For this assignment you will convert a simple database schema that consists of a single table into a database schema with 3 related tables.

See the “Constraints” section at the end of the SQLite Guide for help. I also added a link in the guide to this tutorial which also has a nice overview of using the sqlite3 tool, using statements and queries, and constraints.

Additionally, I have annotated the example I built in class and placed it in the Class Code section of the site. I also added a query at the end of the example which introduces the concept of a join, which allows you to write a single query which retrieves data across multiple tables. The tutorial also has more information on joins.

You will be writing SQL statements that are similar to the ones in the example from class, but split into separate files for creating the tables, inserting data, and querying.

Tasks

Here is the original schema, which is also in the file original_schema.sql:

CREATE TABLE shipment(product_name TEXT, destination_city TEXT,
                      quantity INTEGER);

And here are a few insert statements to populate the database. These are in original_inserts.sql:

INSERT INTO shipment VALUES('Toaster', 'Los Angeles', 40);
INSERT INTO shipment VALUES('Table', 'Houston', 2);
INSERT INTO shipment VALUES('Toaster', 'New York', 30);
INSERT INTO shipment VALUES('Table', 'Los Angeles', 100);

After constructing this database and inserting this data, we can select everything from this table like so:

sqlite> SELECT * FROM shipment;
product_name  destination_city  quantity
------------  ----------------  ----------
Toaster       Los Angeles       40        
Table         Houston           2         
Toaster       New York          30        
Table         Los Angeles       100       

A better way to organize this data would be to have 3 tables: one table for products, one for cities, and one for shipments. Create a schema for these 3 tables and put the CREATE TABLE statements in new_schema.sql. Both the product table and the city table should have only 2 attributes: an ID and the name. In both of these tables, the ID must be a primary key.

The shipment table will still store the product, city, and quantity, but instead of storing the names of the product and city, it will store their IDs. The two ID attributes must be foreign keys which reference the other two tables.

In new_inserts.sql, write insert statements that fill up these tables with the same data that was in the original database, but in this new format. You will need to insert into the product and city tables first, and then into the shipment table. When inserting into these two tables, only insert the name and let SQLite auto-generate the ID. For example, one of your inserts into the product table should look something like this:

INSERT INTO product(name) VALUES('Toaster');

SQLite does not enforce foreign keys by default. Running the following statement enables it for the current connection. For the keys to be enforced it must be run every time you open a new session, interactive or otherwise.

PRAGMA foreign_keys = ON;

You can put that statement at the top of your new_inserts.sql file so it is enabled before performing those inserts.

SQLite will always make the first ID 1, the second 2, etc. so you can infer the IDs of the different products and cities from the order that you insert them, and then use those IDs when inserting into the shipment table.

In select.sql, write a query for your new database that produces the same result as the above SELECT statement did with the original database. You will need to select from all 3 tables and use a WHERE clause to properly build the rows. It is okay if the order of the rows is different from the original order, but the contents of the rows must be the same.

Test out your SQL statements in interactive mode and place them in the .sql files when you are confident they are working correctly.

For full credit, running new_schema.sql, then new_inserts.sql, then select.sql on a database file should produce no errors and the SELECT statement should show the appropriate output. In addition, you must properly use the PRIMARY KEY and FOREIGN KEY constraints in your CREATE TABLE statements, and the primary keys must be auto-generated.

Push your submission to git-keeper. This assignment will NOT be automatically tested, so make sure to test your statements yourself.