Creating the data model
A database is only useful when it has a clearly defined data model. This means that you have described how the data should be structured and added any constraints that are necessary. This structure is defined as an entity-relationship model and then implemented using the structured query language or SQL for short.
Over the course of this tutorial you will develop a database to represent the order system of a small coffee shop. Imagine that you want customers to be able to order their drinks (and extras) without having to queue up - maybe they can build an order on their smartphone and then submit it whilst the sit surfing the web on their laptop at one of your benches.
We will come back to this scenario several times - so stick with it, it will make the rest of the resources easier to understand.
To construct a data model for we must have an understanding of both the entities and relationships that exist in a given system. The entities that may exist in the coffee shop database include:
In general you can think of an entity as an object or person in your system. This means that a single product or single customer is an entity. A group of products is an entity-type but most people refer to the grouping as an entity as well.
A single entity will have various attributes to represent its characteristics. Each attribute will have an attribute name (to describe the characteristic) and an attribute type (what kind of data it is).
Separate entities in the system may be related to one another. For example, a product may be purchased by customers - we can represent this as an entity-relationship diagram:
Notice the relationship between the entities has "crow's feet" at each end, this means:
- a single product may be purchased by many customers
- a single customer may purchase many products
We call this the degree of the relationship, in this instance the degree is many-to-many. There are four possible degrees of relationship:
To begin with we will ignore relationships and focus on a single entity - product. There are usually a number of different types of coffee available, as well as teas, smoothies and other hot drinks such as hot chocolate.
There are lots of different pieces of data that we could store about each product but we will keep it simple for now. We can describe product using an entity description:
The product name and price are obvious pieces of data to store about each product but why product id? This provides us with an obvious unqiue identifier for each product, making it easy to locate a particular product.
Now that we have a model of the entity we wish to create we need to consider how to implement it.
The structured query language is the standard way to interact with any database management system, so whilst we will be using SQLite the general principles apply to other systems as well. SQL can be separated into two parts:
- Data Definition Language (DDL)
- used to create the database structure
- Data Manipulation Language (DML)
- used to insert, delete, update, search and sort data in the database
We will use DDL to create the a representation of our product entity in our database.
Create table statement
To implement the product entity we must use the
create table statement. Notice, that the statement is called
create table and not
create entity - this is because entity is used to refer to the abstract representation and table to refer to the actual implementation.
The full statement that is required to create the product table is as follows:
1 2 3 4 5
Notice, that you must define a data type for each attribute of product. There are other constraints that you can add to each attribute by we will leave them to one side for the moment.
Data types in SQLite3
Earlier it was mentioned that SQLite lacks some of the features of other systems. The one area where this is particularly noticeable is in data types. SQLite supports five data types:
- NULL - used to represent 'nothing'
- REAL - used to represent floating point numbers (decimals)
- BLOB - used to store binary data of any kind
This means that your attributes should have one of these five types - most of the time integer, real and text will be enough. If you are wondering how to store dates then the easiest way to do this is store them as text strings in ISO 8601 format:
- YYYY-MM-DD HH:MM:SS
You can then use functions within Python to convert to and from actual date representations.
Creating the product table
The following video demonstrates how to add the product table to a database.
Use the above video to help you create the product table.
Recreating the product table
Sometimes when you run your code you will want to recreate your database structure, other times you will want to use the existing structure. The SQL statement to drop a table is:
Obviously you do not want to run this statement by mistake! Therefore it is important to write your database structure code in such a way that enables you to easily delete tables when you want to but doesn't just do it automatically.
The video below demonstrates how to do this:
Use the above video to improve the product create table statement.
Now that we have a table in our database we need to think about inserting data into that table.
- 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