Skip to content

Creating a database part 1

Have a go at this quiz revise the previous lesson’s learning.

sqlite3 database_name.db

Creating a database in SQLite is easy. In fact, some of you may already have created a new database by accident!

Challenge 13

You are a massive fan of formula one racing and have decided to keep track of the race times of each competitor.

Create a new database, titled racing.db

Data Types

SQLite Data types

Before we can start creating tables, we need to first think about the data we are going to store in the table and we need to answer the following questions:

  • What kind of data are we going to store in each column?
  • Are we going to have a default value for each column?
  • Are we going to allow empty cells

Different databases support different data types, but SQLite supports the following:

INTEGER

column_name INTEGER

Used for: Whole Numbers

Example: 445

Python equivalent: Integer

REAL

column_name REAL

Used for: Numbers with decimal places

Example: 3.142

Python equivalent: Float

TEXT

column_name TEXT

Used for: Characters words or sentences

Example: “hello”

Python equivalent: String

BLOB.

column_name BLOB

Used for storing data exactly as entered.

Example: used to stored URLs, file names and objects.

Python equivalent: Objects ( We’ve not covered them)

Database design the wrong way - Flat File Databases.

CREATE TABLE table_name( col1_name COL1_DATATYPE, col1_name COL2_DATATYPE);

Before we can start creating tables, we need to think about what data we are going to store in each table and how this data is going to relate to other tables in the database.

First of all we are going to create a simple table using flat file table design.

The best way to think of flat file table design is a spreadsheet file. Data is stored in rows and columns, often on a single sheet. If you want to store different types of data, you can store the different types in a number of different sheets, but the data stored in each sheet is not linked in any way.

This type of table is very easy to create, but it causes a number of problems, including:

  • Data duplication – you end up repeatedly storing the same data in different places
  • Very difficult to create queries that combine data from different tables.

Adding Columns to tables

ALTER TABLE table_name ADD column_name data_type;

Adding a new column is easy!

deleting tables

DROP TABLE table_name;

Deleting a table is easy, just do the above.

Editing column names

This isn’t easy, it’s easier to just delete(drop) the table and start again. If you really need to, then follow this tutorial:

http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table

Challenge 14 - Race Results

Challenge 14 – Race Results

You should now create a table to store the race results.

Table Name

RaceResults

Columns

  • FixtureName – text
  • RacerName – text
  • StartPosition – Integer
  • FinishPosition – Integer
  • Time – Integer
  • TeamName – Text

Challenge 15 - Team Details

Challenge 15 – Team Details

You should now create a table to store the race team details.

Table Name

TeamDetails

Columns

  • Name – text
  • Address – text
  • SeasonWins – Integers
  • NumberOfDrivers – Integer

Challenge 16 - Fixture Details

Challenge 16

You should now create a table to store the race team details.

Table Name

FixtureDetails

Columns

  • Name – text
  • Address – text
  • RaceDateTime – Integer
  • TeamsCompeting -text

If you have finished all of the challenges, then try adding some sample data to your database, practicing the techniques that you have learned in the previous lessons.

For example:

  • Try adding to each of the tables using an INSERT query.
  • Try amending some of the data stored in each table using an UPDATE query.
  • Try deleting some data using a DELETE query.
  • Can you see some problems with this type of database?
  • What do are you having to duplicate in the database?