For Exercise 2 Creating a Multi-Table Database, we create two tables and a relation (a table that links tables together).
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.
We can confirm the output matches what we typed in the ex2.sql file.
- 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.
- 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.