Creating a data model with relationships

Now that there is a normalised design for the coffee shop database we can start to implement it as a SQLite database.

Creating tables with foreign keys

Consider the Product table entity description:

Product(ProductID, Name, Price,ProductTypeID)

Notice, that there is now a foreign key attribute - ProductTypeID. This attribute comes from the ProductType table:

ProductType(ProductTypeID, Description)

Generating the create table statements for each of these entity description would give:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE Product(
             ProductID Integer,
             Name text,
             Price real,
             ProductTypeID Integer,
             Primary Key(ProductID),
             Foreign Key(ProductTypeID) references
                        ProductType(ProductTypeID))

CREATE TABLE ProductType(
             ProductTypeID Integer,
             Description text,
             Primary Key(ProductTypeID))

As you can see from the above statements adding foreign keys is quite easy but it is important to note that you must add the foreign key as a regular attribute of the table before you attempt to add the foreign key statement - this is one of the most common errors that occur when implementing relational data models.


Task 11

Use the above video to recreate the coffee shop database so that it has two tables (product and product type) that have a relationship between them.


Working with a relational model

Having successfully created these two tables with ProductTypeID as the foreign key, we need to add data to our tables. It is important to keep in mind that by default SQLite does not check for foreign key constraints and therefore you must explicitly turn this feature on using the following statement:

1
PRAGMA Foreign_Keys = ON

Doing this will ensure that you do not get any unexpected inconsistency in your data.


Task 12

Use the above video to help you add data to each of the table you created in Task 10.

Once you have added the data from the video try adding the data from the tables below:

Name Price ProductTypeID
Latte 1.35 1
Mocha 2.40 1
Green Tea 1.20 2
Black Tea 1.00 2
Americano 1.50 2
Raspberry 3.50 3
Lemonade 2.85 3

|Description| |-----------| |Coffee| |Tea| |Cold Drink|