Exercise 2: Creating A Multi-Table Database

Creating one table isn't too useful. I want you to now make 3 tables that you can store data into:

View Source file ex2.sql Only

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);

CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);

In this file you are making tables for two types of data, and then "linking" them together with a third table. People call these "linking" tables "relations", but very pedantic people with no lives call all tables "relations" and enjoy confusing people who just want to get their jobs done. In my book, tables that have data are "tables", and tables that link tables together are called "relations".

There isn't anything new here, except when you look at person_pet you'll see that I've made two columns: person_id and pet_id. How you would link two tables together is simply insert a row into person_pet that had the values of the two row's id columns you wanted to connect. For example, if person contained a row with id=20 and pet had a row with id=98, then to say that person owned that pet, you would insert person_id=20, pet_id=98 into the person_pet relation (table).

We'll get into actually inserting data like this in the next few exercises.

What You Should See

You run this SQL script in the same way as before, but you specify ex2.db instead of ex1.db. As usual there's no output, but this time I want you to open the database and use the .schema command to dump it:

View Source file ex2.sqlite3-console Only

sqlite> .schema
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    age INTEGER
);
CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER
);
CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dead INTEGER
);
sqlite>

The "schema" should match what you typed in.

WARNING: Remember, On Windows PowerShell the < (redirect input from file) is not implemented. Every time you see me do sqlite3 ex2.db < ex2.sql you should ignore me and do sqlite3 ex2.db -init ex2.sql. This will load your file and drop you right into the sqlite3 shell. Type .quit to exit the shell after you're done playing with the results.

Study Drills

• In these tables I made a 3rd relation table to link them. How would you get rid of this relation table person_pet and put that information right into person? What's the implication of this change?

• If you can put one row into person_pet, can you put more than one? How would you record a crazy cat lady with 50 cats?

• Create another table for the cats people might own, and create its corresponding relation table.

• Search for "sqlite3 datatypes" in your favorite search engine and go read the "Datatypes In SQLite Version 3" document. Take notes on what types you can use and other things that seem important. We'll cover more later.

Portability Notes

Databases have a lot of options for specifying the keys in these relations, but for now we'll keep it simple.