Week 3 Extra Resources
Week 3Background reading on databases:
- A Brief History of Databases
- Good Database Design in Web Development
- Learn SQL the Hard Way: Introduction
- Visual guide to SQL JOINs
SQLite documentation:
More practice:
Work through Lessons 1-4.
Create a new, blank database, and then write SQL code that accomplishes the following:
- Create a species table. This table should have a single column for "name", which should be a string value.
- Create a breed table. This table should have a column for "name" (text) and species (foreign key to species table).
- Create a pet table. This table should include the following columns: name (text), dead (integer), breed (foreign key to breed table), adopted (integer). Note that both the dead and adopted fields are intended to use 0 and 1 to refer to boolean values.
- Create a person table with first name, last name, age, email address, and phone number.
- Insert some values for species, breed, pets and people.
- Insert some values into the person_pet table to capture relationships.
Although you can enter the commands for each of these steps into the interactive console for the database, you ultimately should end up with a single SQL file that you can run from the command line that accomplishes each of these steps.
Work through Lessons 5-11
Working with the same database you ended with in the previous assignment, write SQL statements to achieve each of the following:
- Select all the cats
- Update all cats to set adopted to False
- Select all the pets belonging to a specific person
- Update all the cats belonging to a specific person to set adopted to True.
Work through Lessons 12-15.
Database Visualizers
To look at the contents of a database in a more visual way, you can use a client like Induction. For Linux and Windows, RazorSQL, SQLite Browser (Ubuntu), and DB Browser are all (untested) options.
For Induction, the easiest way to get going is to drag and drop the database file on top of the app icon.