Learn SQL the Hard Way – Exercise 2 Creating a Multi-Table Database

For Exercise 2 Creating a Multi-Table Database, we create two tables and a relation (a table that links tables together).

Exercise 2 code
Exercise 2 code

The person_pet table is the relation, it is the “link” between person table and the pet table. For example, if person with id 3 (in the person table) has a pet with an id of 10 in the pet table, we would add a row to the person_pet table with person_id of 3 and pet_id of 10.
After writing this code, I ran it by typing sqlite3 ex2.db < ex2.sql on the command line. Then I opened the databse with sqlite3 ex2.db, and then used .schema to dump it. On the command line, a “dot command”, is interpreted by the sqlite3 program. (Other times, lines are just read and passed to the SQLite library for execution.) The .schema  command works as below:

.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables                             matching LIKE pattern TABLE.

Since we did not specify the table, all the CREATE statements are shown.

The CREATE statements
The CREATE statements

We can confirm the output matches what we typed in the ex2.sql file.

Extra Credit

  • 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?
    You could create a column in person_pet with the name pet_id, and have integer values for that column. This column would have the id (from the pet table) of the pet that person owns.
    An implication of this would be that each person could only have one pet. (since each row would only have one value in the pet_id column)
  • 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?
    Yes, you can have many rows in the person_pet table. If cat lady has a person_id of 5, we can just add a new row for each cat she owns. For example row 1 would be person_id 5 with pet_id 1, row 2 would be person_id 5 with pet_id 2, row 3 would be person_id 5 and pet_id 3… all the way to pet_id 50.
  • Create another table for the cars people might own, and create its corresponding relation table.
    I created a table named cars, it has 3 columns: id, type (although manufacturer might be better), color, and manufacture year. The person_car table has two columns: person_id and car_id. These values correspond to the id values in the person table and the car table.

    Three tables and two relation tables.
    Three tables and two relation tables.
  • 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.
    Here is Datatypes in SQLite3.
    Affinity is an interesting aspect, with type conversions happening. I like the name BLOB.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s