A-level Computing 2009/AQA/Problem Solving, Programming, Operating Systems, Databases and Networking/Databases/Primary keys





Databases are very powerful tools that allow us to search and sort data at incredible speeds. An essential feature of a database is making each record unique. To make them unique we using Primary Keys.

Primary Key
Let's look at an example in the Criminal Table:
 * Table: Criminal
 * Attributes:
 * NI Number: String
 * Name: String
 * Date of Birth: Date
 * Number of scars: Integer
 * Home town: String

Which of these data items are unique? There is a short cut to writing out database table designs, where the underlined attribute is the primary key. Note that primary keys are normally written first. TableName( PrimaryKey, Attribute, Attribute, Attribute, Attribute) Criminal( NI Number, Name, Date of Birth, Number of scars, Home town)

Questions
Car( Registration number, Colour, Number of doors, Convertible)

Student( Unique Learner Number, Name, Date of Birth, Mobile number

NOT mobile number as they might share a phone with someone else.

Composite key
However, things may not always be so clearly cut. Take this a table of football players for example:
 * Table:Player
 * Name: string
 * Position: string
 * Number: integer
 * injured: boolean
 * Team: String

Which of these would be the primary key? So what do we do? Before we make our database tables it's best to go through this process and we come to realise that we need to introduce another attribute that is unique for each player. We might even invent one, a playerID:
 * Player( PlayerID, Name, Position, Number, Injured, Team)

Another way of solving these problems is to try and find a combination of attributes that together are unique. This is called a composite key. Take a look at this example for houses:
 * Table: House
 * Number: integer
 * Road: string
 * Colour: string
 * Post code: string

We could make a primary key up, but if you look carefully, we can use a combination of attributes. The house number and the road name combined seem to be unique. You can't have two 45 Belmont Close can you?.
 * House( Number, Road , Colour, Post code)

Does that sound ok? What about if we were storing data on all the towns in the country and there was a 5 London Road in Manchester and a 5 London Road in Winchester. This would mean that the combination was not unique. We might try using the house number and post code instead, and this combination is always unqiue, this is our composite key:
 * House( Number, Road, Colour, Post code )

Questions
Receipt( CustomerID, DateTime , Total, StaffID)

Match( TeamA, TeamB , Date , TeamAScore, TeamBScore, RefID)

Phone( ModelID, Colour, Weight, Internet)

You might argue that if this was for a particular instance of a phone instead of for a model of phone you'd need to introduce a unique value through serial number or the like

There aren't enough fields here for us to find a unique one or combination. We could have two ginger cats weighing 1 kg with three legs called Phil. We therefore need to introduce a new unique value, CatID Cat( CatID, Colour, Weight, NumberofLegs, Name)