Database

Normalisation

The process of deciding the entities, attributes and keys. You need to know how to normalise data to first normal form - usually written as 1NF. The main exam in May will have a question on this process.

The Process

The process for normalising to 1NF is generally straightforward, and can be tackled in much the same way for every question.

1. Identify all the data items  (write them down in one list)
2. Identify any repeating groups (bracket the repeating groups)
3. Remove any repeating groups into separate entities (re-write into separate      entities)
4. Identify keys, (underline the primary key and asterisk the foreign key)
5. Create the E-R Diagram

Worked Example

To illustrate the application of the process for 1NF, we will work through the following example.

A company keeping paper records of staff in its shops wishes to computerise the database. Some sample data is shown below:

Shop ID	Shop Location	Telephone No	Staff ID	Staff Name	Post
7262	Edinburgh	0131 4349816	5242AB	Karen Wilson	Manager
			7262DG	Harry Jones	Supervisor
9928	Glasgow	0141 5726481	988UY	Gina Ross	Manager
			4433QW	Lesley Pugh	Supervisor
			6523GC	Fred Kinder	Sales Assistant

The company has also explained that a member of staff only ever works in one shop at a time; each Staff ID is unique to a member of staff and each Shop ID is unique to a shop.

1. Identify all the data items, (reading from the headings of the table):
   • Shop ID
   • Shop Location
   • Telephone No.
   • Staff ID
   • Staff Name
   • Post
2. Identify any repeating groups.  The repeating group is the set of data items “Staff ID”, “Staff Name” and “Post”, since these have more than one value in each instance. This can be shown on the primary entity by using brackets.

• Shop ID
• Shop Location
• Telephone No.
• ( Staff ID
• Staff Name
• Post )

3. Remove any repeating groups into separate entities

The repeating group in Staff can be removed to a new entity, which we will call Staff:

STAFF                                           SHOP
Staff ID                                         Shop ID
Staff Name                                 Shop Location
Post                                               Telephone No
                                                                                                           

The existing entity is renamed Shop.

4. Work out the relationship between the Entities. In this example we can say that one shop can employ many staff. This helps when deciding which field we use to create the link between the tables.

A general rule is the forien key comes from the one side of the relationship. In this case from the Shop side as one shop can employ many staff.

5. Indicate the primary key and foreign key, by underlining the primary key and adding an asterisk for the foreign key as follows:

STAFF                                           SHOP
Staff ID                                         Shop ID
Staff Name                                 Shop Location
Post                                               Telephone No
Shop ID*

6. Create the E-R Diagram:

Click here to download examples of the normalisation process.

Work through each example in turn and show it to your teacher before you proceed to the next example. Ask you teacher for lined paper to work on.

Download and complete Worksheet 2.