Database - How To Movies
 
1. Setting up and creating tables and fields 2. Formatting Fields. 3. Creating Primary Key and Relationships 4. Searching and Creating Reports 5. Editing Reports
 
Home DatabaseUsing Information Internet NAB/Coursework Revision Legal Responsibilities

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.


 

 



 

Intermediate II Home
Database | Using Information | Internet | NAB/Coursework| Revision
Log into Glow | School Website | Contact Us