Skip to content

Creating a database – part 2

 

 

 

 

 

 

 

column_name data_type PRIMARY KEY AUTOINCREMENT

In order to create relational databases, you need to be able to identify each individual entity in a table, and the way we do this is through a PRIMARY KEY.

A table must have a primary key for each entity(row)

The primary key must be unique in that table.

For example, the primary key in the table below is the ID column:

Racers Table

NameAgeTeam

IDNameAgeTeam

1 Bob Jones 25 Pirelli

 

Each racer is given an ID number and that number is unique to the driver. This means that even if there are 2 driver, who are both 25 and both work for Pirelli, they won’t get confused by the database.

Challenge 17 – Create a new database

Database name – racing2.db

 

column_name DATA_TYPE REFERENCES table_name(primary_key_column_name)

So why have we created a primary key in the first place? It’s more effort than before? Well, the answer is that we can now use the primary in other tables to refer to that racer name. It connects the two tables together and allows us to do more clever things with the database than we did previously.

Flat File Database Example

flat file tables

Here is a simple flat file database. It looks pretty sound, but as you can see there is quite a bit of duplication between the two tables ( racerName, team). The more clever way of doing things would be to use a relational database setup instead, which would save duplication of data.

Relational Database Example

relational tables

Not only does this save on duplication, but it also avoids storing some data altogether. For instance we can calculate how many times a racer has won a race by doing a clever JOIN lookup on both the racers and fixtures tables.

CREATE TABLE Pupil(id integer PRIMARY KEY,name text);

sqlite> CREATE TABLE Allergy(id integer PRIMARY KEY, name text, action text);

 

CREATE TABLE Pupil_Allergy(
id integer PRIMARY_key,
PupilID integer,
AllergyID integer,
FOREIGN KEY (PupilID) REFERENCES Pupil(id),
FOREIGN KEY (AllergyID) REFERENCES Allergy(id)
);

Challenge 18 - Racers

Challenge 18 – Racers

In your racing2.db database create the following table:

Table name

racers

Column names

  • RacerId integer, auto incrementing
  • FirstName text
  • Lastname text
  • Age integer
  • TeamID foreign key integer

Challenge 19 - Fixtures

Challenge 19 – Fixtures

In your racing2.db database create the following table:

Table name

fixtures

Column names

  • date
  • time
  • location
  • name

Challenge 20 - Teams

Challenge 20 –  Teams

In your racing2.db database create the following table:

Table name

teams

Column names

  • name
  • country
  • manager

Challenge 21 - Results

Challenge 21 – Results

In your racing2.db database create the following table:

Table name

results

Column names

  • fixture
  • racer
  • position
  • time