What is a schema?

It’s the formal description used to build a database. In other words, it is the set of SQL commands used to define the table structure.

Where are some schemas?

In the Files > sqliteExamples folder, I’ve put the schemas into files suffixed with .sql but that is just a convention, not noticed by SQLite. It’s just so I can keep track of which files contain SQLite databases (I use the .db extension for that) and the corresponding schemas. (There’s actually also a file of SQL commands called scratchpad.sql which will change from time to time during the semester. It’s a safe bet that you can open .sql files in a text editor and .db files in sqlite3.)

A text editor for your SQL commands

To facilitate saving, debugging, and re-using your code, use a text editor. The most popular text editor today is VS Code but it sends telemetry back to Microsoft so, for privacy’s sake, you can substitute VSCodium at https://vscodium.com/.

Just make sure you never use a word processor such as Microsoft Word. Word and other similar word processors can introduce invisible characters that will cause your SQL commands to fail. In fact, it’s best to avoid any editor that allows for formatting like font size, type, bolded and italic text, etc. Mick uses NeoVim, for which he has a separate tutorial. NeoVim is hard to learn but powerful.

Bringing SQL results back

Sometimes you may want to take the results of a SQL query and bring it in to another document. For example, in this class you will be asked to paste query results into a text box for a couple of assignments. You may have already noticed that when you do so, all those clean ASCII-formatted lines making a table fall out of alignment.

To fix this, you need to select what’s called a “fixed-width” font. Fixed-width fonts (such as Courier and Courier New) duplicate the spacing of a terminal session where every character is the same width. In Canvas and some other web-based text interfaces you would highlight your text and select “Preformatted” as your text choice.

Testing and using schemas and searches

We will be using SQLite to test our database schemas and run SELECT statements against them. As we’ve seen in the previous module, the command line interface may look a little intimidating at first. We will walk through several examples in the accompanying video to this document. You can use any text editor to build your schemas and then copy and paste them.

Below is the code for the simple, one-table interface we used in the assignment for last module:

DROP TABLE IF EXISTS [pets];
CREATE TABLE pets (
pet_id INTEGER PRIMARY KEY,
name TINYTEXT,
kid_friendly VARCHAR(3)
);

Note that we don’t have to fill in the pet_id field because it automatically increments.

INSERT INTO pets (name, kid_friendly) VALUES
('dog', 'yes'),
('cat', 'yes'),
('parakeet', 'yes'),
('python', 'no'),
('megalodon', 'no');

Here is a SELECT statement to run in sqlite3:

SELECT * FROM pets;

Here is a three-table database schema to try. This section builds the tables:

CREATE TABLE recipe ( 
  recipe_id INTEGER PRIMARY KEY,
  name VARCHAR(200), 
  description TINYTEXT, 
  directions TEXT, 
  author TINYTEXT
);
CREATE UNIQUE INDEX recipeName ON recipe(name);

CREATE TABLE ingredient (
  ingredient_id INTEGER PRIMARY KEY, 
  name VARCHAR(100) 
);
CREATE UNIQUE INDEX ingredientName ON ingredient(name);

CREATE TABLE recipe_ingredient ( 
  ri_id INTEGER PRIMARY KEY,
  recipe_id INTEGER, 
  ingredient_id INTEGER, 
  amount TINYTEXT, 
  FOREIGN KEY (recipe_id) REFERENCES recipe(recipe_id),
  FOREIGN KEY (ingredient_id) REFERENCES ingredient(ingredient_id)
);

And this section populates them with data:

INSERT INTO ingredient(name)
  VALUES ("eggs");

INSERT INTO ingredient(name)
  VALUES ("butter"),
         ("salt"),
         ("milk"),
         ("black pepper");

INSERT INTO recipe(name, directions) 
  VALUES ("Scrambled Eggs",
          "Crack eggs into bowl, whisk, Pour into hot pan, add salt to taste. Remove when fluffy.");

INSERT INTO recipe_ingredient(recipe_id, ingredient_id, amount) 
  VALUES (1, 1, "4"),
         (1, 2, "1 tablespoon"),
         (1, 3, "a pinch");

Here is a simple SELECT statement to run against one table:

SELECT * FROM ingredient;

Here is a more complex query that looks up the ingredients for one recipe that joins all three tables:

SELECT recipe_ingredient.amount, ingredient.name 
  FROM recipe, ingredient, recipe_ingredient
  WHERE recipe.name = "Scrambled Eggs"
  AND recipe.recipe_id = recipe_ingredient.recipe_id
  AND recipe_ingredient.ingredient_id = ingredient.ingredient_id;

An entity relationship diagram for three tables

It is on the following frame. It depicts a one to many relationship between recipe and recipe_ingredient and a one to many relationship between ingredient and recipe_ingredient. This is preferable to a many to many relationship between recipe and ingredient.

The above image shows an entity relationship diagram for the preceding schema. I’ve taken the liberty of using the common abbreviations PK and FK for primary key and foreign key.

Data types in SQLite

Although the above schemas list the common data types used in commercial databases, SQLite actually only has five datatypes and will automatically convert these into one of those five. They are

For example, you can say

select distinct typeof(name) from recipe;

and the result will be text rather than varchar(200). SQLite dynamically allocates as much space as is needed.

Other keywords

SQLite allows you to use other keywords that appear in typical SELECT statements without complaining but doesn’t use them in storage. This can cause some confusion because, if you say, for instance

.schema recipe

the data type you specified will be listed. You can only get the correct data type with the typeof() function. You can find more details at https://sqlite.org/datatype3.html including a table of type affinities, listing many examples of conversions.