08 Oct 2012

Normalisation. Part 2

Category:
  • Software Design & Development
Un-normalised Form (UNF)

Your list of fields is in un-normalised form (UNF). I find that normalisation is easiest to do in a spread sheet application due to its tabular nature, you can easily chop and change things as you go. The following table contains all of the attributes that will be in the database. If you want to have a go at listing them yourself and see how you do, that’s fine! Don’t worry about any of the ID fields just yet, that’s the next stage.

  • Cust_No
  • Cust_Addr
  • Cust_Town
  • Cust_County
  • Cust_Postcode
  • Cust_Name
  • Cust_Tel
  • Cust_DOB
  • Cust_Email
  • userName
  • password
  • staffID
  • staffName
  • bookingNo
  • bookingDate
  • membershipType
  • discountAmount
  • flightNo
  • flightDestination
  • flightCapacity
  • flightDate
  • flightTime
  • flightReturnDate
  • flightReturnTime
  • flightCost
  • airline
  • paymentMethod
  • quantityOfSeats
  • subTotal
  • Total
First Normalised Form (1NF)

There are a few steps to getting our data into 1NF. Firstly we need to identify all attributes which we don’t require. These are attributes which can be calculated using other fields, so anything to do with totals and subtotals. In the case of our scenario, total and subtotal can be removed.

The second stage of 1NF is identifying attributes which are going to occur time and time again. For example, membership type will be either 1,2,3, assuming there are three memberships. As there are only a finite number of possibilities these three values are going to appear all throughout the database.

The easiest way to identify these fields is to put all of your fields in a spread sheet with some sample data.

Normaliastion Spread Sheet
The fields highlighted in yellow are those that are considered repeated, the data in these fields can repeat throughout the database. Therefore these attributes need to be placed into a second table.

If you are wondering why the customer information is not considered repeated, it’s a good question! A customer may only ever appear once; they may not be regular customers.

Our first normalised form now looks like this (with the inclusion of some suitable table names):

Primary Key

Normalisation Table 1
The last stage (yes, there is more!) is to put in our unique identifies, the primary keys. These are a form of ID that is absolutely unique to every record. These are most commonly field names such as customerID. We use a numerical ID rather than the name as we may have two John Smiths.

Our customer table will just have one ID, custNo, as this value can identity every customer.

Assigning a primary key to our Bookings table is a little more tricky as these fields are all repeated fields, and therefore by their very nature are not unique. To solve this issue we bring down the primary key from our Customer table. This result in a composite key, a key made up of one or more primary keys.

Note: The combination custNo and flightNo works on the assumption that a customer can only make one booking per flight. This is fine for our simple example. After all, we can use Iron Speed to let us update bookings and increase the seat numbers of a particular booking.

Our final first normalised form looks like this:

Primary Key

Normalisation Table 2

Go Back

Please enter your details and we will aim to call you back the same day.