Database Workshop 2 - Joins and Constraints

From Juneday education
Jump to: navigation, search

The purpose of this workshop is to solve Database:Assignment_2_-_JOINs_and_Constraints (which isn't mandatory to hand in in all of our courses) together, so that you get the knowledge provided in the JOINs and Constraints assignment, without the stress of a deadline and handing it in for grading.

Getting started

Open Database:Assignment_2_-_JOINs_and_Constraints in a new tab. That is the page with the instructions for the "assignment".

Learning goals of the assignment/workshop

You will learn two database related concepts in this assignment/workshop:

  • Creating a constraint for license numbers using "globbing"
  • Normalize the cars table by moving out Color and Make to their own tables

On the way, you will as a bonus learn about two general concepts you can use also in bash:

  • Globbing (using [] and *)
  • Expansion (using {})

Learn about globbing and expansion

We'll show you (and you are welcome to copy what we do) some globbing and expansion in the bash shell. The globbing part is to get you familiar with a syntax you will use in the database assignment.

We'll use the examples on the assignment page as a starting point for this session.

Learn about the GLOB operator in SQLite3

The best way to investigate a function or operator in SQLite3 is to use it in a simple select interactively. So fire up SQLite3 (here it doesn't matter if you select a database or do in in-memory) and play around with the GLOB operator like so:

sqlite> SELECT 'ABCabc012' GLOB '[A-Z]*';
1
-- any upper case letter followed by anything

sqlite> SELECT 'ABCabc012' GLOB '[A-Z][A-Z][A-Z]*';
1
-- any three upper case letters followed by anything

sqlite> SELECT 'ABCabc012' GLOB '[A-Z][A-Z][A-Z][A-Z]*';
0
-- No match, because string didn’t start with four upper case letters

sqlite> SELECT 'ABCabc012' GLOB '[A-Z][A-Z][A-Z]*[0-9]';
1
-- Matches three A-Z followed by anything followed by a number

sqlite> SELECT 'ABCabc012' GLOB '[A-Z][A-Z][A-Z][a-z][a-z][a-z][0-9][0-9][0-9]'; 
1
-- Matches the form of the string exactly

sqlite> SELECT 'A 0' GLOB '[A-Z] [0-9]';
1
-- Matches one upper case letter, a space, and one number.

(Same example exists on the assignment page.)

We'll talk a little about using GLOB and show some examples.

Create a simple table called cars2

You may use your old many_cars database for this exercise or start from scratch in a new database.

Here we'll use GLOB as a CHECK constraint when we create a small table cars2, to enforce that License Numbers of cars always have the pattern described by a GLOB expression. A valid license number should have three capital' letters followed by a space followed by three digits.

The syntax for the CREATE TABLE with such a constraint for license numbers of the cars2 table is:

CREATE TABLE cars2(
  LicenseNumber TEXT PRIMARY KEY
                CHECK( LicenseNumber GLOB 'enter the glob pattern here!' )
);

We'll let you try yourself to come up with the correct glob pattern before we show you one way of doing it.

Test that your constraint works and rejects faulty license numbers

We shouldn't trust blindly that our constraint works, but always provide at least a few tests that show the point.

Verify that the following INSERT statements fail:

sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('abc 123');
Error: CHECK constraint failed: cars2
sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ABC123');
Error: CHECK constraint failed: cars2
sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ABC 12A');
Error: CHECK constraint failed: cars2
sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ABC 1234');
Error: CHECK constraint failed: cars2
sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ABCD 123');
Error: CHECK constraint failed: cars2

Can you come up with more clever tests that should be rejected by your constraint? Think about stuff that looks correct but aren't and stuff that easily could be entered by the user by mistake (like double spaces etc).

Now confirm that you constraint accepts perfectly valid license numbers! It is not enough to see that it rejects faulty license numbers. Why?

It might reject everything! So let's feed our table some valid license numbers!

sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ABC 123');
sqlite> INSERT INTO cars2 (LicenseNumber) VALUES('ZZZ 999');

Automate your tests ffs!

Who has the time to manually enter a lot of insert statements with faulty license numbers? Not the teachers, anyway!

Let's create a text file with faulty license numbers (one on each line) and send all of them as license numbers to our database.

If your text file is called bad_licenses.txt and you created the little cars2 table in a database called many_cars, then you can create insert statements and send to your database the following way:

$ cat bad_licenses.txt|while read license;do echo "INSERT INTO cars2 VALUES('""$license""');";done | sqlite3 many_cars
Error: near line 1: CHECK constraint failed: cars2
Error: near line 2: CHECK constraint failed: cars2
Error: near line 3: CHECK constraint failed: cars2
Error: near line 4: CHECK constraint failed: cars2
Error: near line 5: CHECK constraint failed: cars2
Error: near line 6: CHECK constraint failed: cars2
Error: near line 7: CHECK constraint failed: cars2
Error: near line 8: CHECK constraint failed: cars2

Counting the errors from using the file

Now, we might be interested to check how many errors we got. In order to do so, we'll redirect the standard error stream from SQLite to standard out using 2>&1 and pipe the result to wc -l (the expert in counting rows!):

$ cat bad_licenses.txt|while read license;do echo "INSERT INTO cars2 VALUES('""$license""');";done | sqlite3 many_cars 2>&1 | wc -l
8
$ wc -l bad_licenses.txt 
8 bad_licenses.txt

We confirmed that we got 8 errors from 8 rows in the file.

Explanation of the command line used above:

cat bad_licenses.txt       # print the contents of the file to standard out
| while read license;      # use the printout to read each line into the variable license 
do                         # loop:
echo "INSERT INTO cars2 VALUES('""$license""');";
# print the INSERT statement and use the value of the variable license inside the single quotes
done                       # end of the while-loop
| sqlite3 my_cars 2>&1     # send the result of the loop to
                           # SQLite3 for the database my_cars
                           # but let error printouts from SQLite3 go to standard out
                           # rather than standard error
| wc -l                    # pipe any error printouts to wc -l
                           # which will echo out the number or rows of errors

(Same example exist on the assignment page)

Normalize the cars table

We want to normalize the old cars table from the previous workshop by moving out Make and Color to their own tables and use make_id and color_id as columns in the cars table, referring to the two new tables. So let's start by creating those tables!

Create the table colors with the following columns:

  • color TEXT
  • color_id INTEGER PRIMARY KEY

Create the table makes with the following columns:

  • make TEXT
  • make_id INTEGER PRIMARY KEY

Expand using link to the right to get a sneak-peek of the create table syntax.

CREATE TABLE colors(color text, color_id INTEGER primary key);
CREATE TABLE makes(make text, make_id INTEGER primary key);

Download SQL statements to check that you can populate the colors and makes tables with data

Download the files insert_colors.sql and insert_makes.sql and let SQLite3 run the statements in those files for the database many_cars.

Expand using link to the right to get a sneak-peek of the syntax.

$ sqlite3 many_cars < insert_colors.sql
$ sqlite3 many_cars < insert_makes.sql

Those statements will only work correctly if you have been successful in creating the tables as detailed above.

Log into the sqlite3 interactive shell for the database many_cars and verify that you have the colors and makes inserted in your tables:

sqlite> select * from colors;
Blue|1
Red|2
Green|3
Yellow|4
Black|5
White|6
Brown|7
Silver|8
sqlite> select * from makes;
Volvo|1
Mazda|2
Honda|3
Suzuki|4
Ford|5
Chevrolet|6
Saab|7
Simca|8
Dodge|9

If that is what you see, then this task is done. There is nothing to add to the zip file from this part.

(Same example exists on the assignment page)

Create a new version of the cars table with the new, normalized, form

In this task, we'll create the cars table which you will use in task 4 below. Note that there are no files produced here in task 3 for inclusion in the zip file, but you must finish this task in order for the following tasks to work.

You should now create the cars table with two foriegn keys, color_id and make_id and the column LicenseNumber with the constaint you created in a previous task. Here's the nearly complete CREATE TABLE statement, but or course, we'll hide the constraint from you, since that is part of a previous task!

sqlite> CREATE TABLE cars(color_id INTEGER, make_id INTEGER, 
LicenseNumber text primary key check(
LicenseNumber GLOB 'your GLOB pattern here!'),
FOREIGN KEY(color_id) REFERENCES colors(color_id),
FOREIGN KEY(make_id) REFERENCES makes(make_id));

CREATE TABLE cars(color_id INTEGER, make_id INTEGER,     
LicenseNumber text primary key check(                    
LicenseNumber GLOB '[A-Z][A-Z][A-Z] [0-9][0-9][0-9]'),   
FOREIGN KEY(color_id) REFERENCES colors(color_id),       
FOREIGN KEY(make_id) REFERENCES makes(make_id));

Again, note that you must put the globbing pattern you created earlier between the single quotes of the GLOB expression above.

Put cars in the cars table!

Now we'll put data for many cars in the new table and see that it works the way we wanted.

You will want SQLite3 to enforce the foreign keys, so that you make sure that you don't enter rows referencing bad IDs in colors or makes. Do you remember the directive to SQLite3 for enabling foreign key checks?

Expand using link to the right to see the directive.

sqlite> PRAGMA FOREIGN_KEYS=ON;

Let's make sure that it works by trying to insert a car with a color id of 9 (which is illegal since there only exist 8 colors):

sqlite> insert into cars values(9,1,'ABC 123');
Error: FOREIGN KEY constraint failed

Do the same, but this time check that the constraint for make_id works. Use 99 as the value for make_id.

Expand using link to the right to see the directive.

sqlite> insert into cars values(1,99,'ABC 123');
Error: FOREIGN KEY constraint failed

Since there is no make_id 99 in the makes table, it should fail.

Now that you verified that the foreign key constraints work, it's finally time for some valid cars. Download insert_normalised_cars.sql and execute the statements in that file for the database many_cars.

Expand using link to the right to see how to create the directory and enter it.

$ sqlite3 many_cars < insert_normalised_cars.sql

Let's make queries for cars joining the three tables!

Here, we'll show you three different ways we can use for joining the tables cars, colors, and, makes.

The tables should be joined on the fact that cars.make_id is the same as make.make_id AND that cars.color_id is the same as color.color_id.

The goal is for you to try yourself first, but we'll include a solution you can expand below. We'll then explain the three versions and how they work.

Expand using link to the right to see the SELECTs and various JOINs

sqlite> SELECT make, color, LicenseNumber
        FROM cars c, colors co, makes m
        WHERE
          c.color_id=co.color_id
          AND
          c.make_id=m.make_id;

Alternative syntax using JOIN:

sqlite> SELECT make,color,LicenseNumber
        FROM cars
        JOIN makes,colors ON
          cars.make_id=makes.make_id
          AND
          cars.color_id=colors.color_id;

An alternative syntax is:

sqlite> SELECT make, color, LicenseNumber
        FROM cars
        JOIN makes ON
          cars.make_id=makes.make_id
        JOIN colors ON
          cars.color_id=colors.color_id;

An alternative syntax using NATURAL JOIN is:

sqlite> SELECT make, color, LicenseNumber
        FROM cars
        NATURAL JOIN colors
        NATURAL JOIN makes;

That's about it!