Databases: Primary keys

      UNIT 3 - ⇑ Databases ⇑
      ← Databases Primary keys Entity relationship modelling →


      Database 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 - A primary key is an field in a table that contains unique data.


      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?

      Attribute Unique Reason
      Home town No you might have several criminals living in the same town
      Number of scars No you might have two criminals with the same number of scars
      Date of Birth No you might have two criminals born on the same day, or twins who are criminals
      Name No you might have two criminals with the same name. e.g. John Smith and John Smith
      NI Number Yes this is unique for each person

      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)
      Exercise: Primary Keys

      Spot the primary key in the following table attributes, and write out the table design:

      Table: Car
      Registration number: string
      Colour: string
      Number of doors: integer
      Convertable: boolean

      Answer :

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

      Spot the primary key in the following table attributes:

      Table: Student record
      Name: string
      Unique Learner Number: integer
      Date of Birth: date
      Mobile number: integer

      Answer :

      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?

      Attribute Unique Reason
      Name No you might have several players with the same name
      Position No you might have two goalies
      Number No you might be storing the details of multiple teams, in which case each number has several players from different teams
      Injured No several people might be injured at the same time
      Team No several players can play for the same team

      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
      Attribute Unique Reason
      Number No you might have a 61 on London Rd and a 61 on Manchester Rd
      Road No you might have multiple houses on the same road
      Colour No more than one house might be green
      post code No multiple houses might have the same post code

      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)
      Exercise: Composite keys

      Where applicable rewrite the table description with composite keys, primary keys, or add a primary key for the following tables:
      Receipt(CustomerID, DateTime, Total, StaffID)

      Answer :

      Receipt(CustomerID, DateTime, Total, StaffID)

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

      Answer :

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

      Phone(ModelID, Colour, Weight, Internet)

      Answer :

      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

      Cat(Colour, Weight, NumberofLegs, Name)

      Answer :

      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)
      Last modified on 12 June 2013, at 12:03