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
Product table entity description:
, Name, Price,
Notice, that there is now a foreign key attribute -
ProductTypeID. This attribute comes from the
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.
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:
PRAGMA Foreign_Keys = ON
Doing this will ensure that you do not get any unexpected inconsistency in your data.
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:
- 1. Introduction to databases
- 2. Creating the data model
- 3. Inserting data
- 4. Updating and deleting data
- 5. Searching and sorting data
- 6. Product Management Menu
- 7. Introduction to relational databases
- 8. Creating a data model with relationships
- 9. Referential integrity
- 10. Completing the coffee shop