 |
|
|
|||||||||
 |
|||||||||||
|
|||||||||||
 |
|||||||||||
|
|
 |
|
Normalisation of data
1. Introduction.Most modern databases are of the relational database type. To obtain the best results requires an understanding of data relationships and methods of applying it and how to use many other productivity tools provided. A well-designed database must fulfil the following requirements: · Eliminate or minimise the duplication of data, and allow data to be entered and edited quickly and easily. · Provide fast data access time in a large database, with clear and effective reporting quickly and easily as each user requires. · Prevent invalid data from being entered, maintain data integrity, and prevent unauthorised access to confidential information. · Be User friendly, and allow for future expansion as new needs are identified. The source of your data can affect the design process. As well as manually entering data into tables you have created, data may be imported directly from an existing database, spreadsheet or other type of data file. Imported tables and data become part of your database and can be structured as required. Alternatively, the database may be linked to external data files, which remain in their original format and location, but are used as if they were part of your database. You will be able to change the data within these files but will not be able to amend the structure of them. 2. Normalisation of data.One of the purposes of using a relational database is to cut down on duplication of data. This makes it easier to enter, search for, and use the data, and takes up less disk space. A normalised database is easier to maintain and can be modified more readily as requirements change. A large database will also run significantly faster when it is normalised. Normalisation is basically a case of splitting duplicated data into separate tables and preventing data redundancy where fields will be left blank. For example, a Contacts table may look like this, where the CoCode field is the primary key.
Figure 1. Or like this:
Figure 2. Both tables contain data duplication of the company details, and the first table would need to be expanded to accommodate the company with the most contacts, and therefore wasting space (i.e. data redundancy). The first step of normalisation is to create a separate table for duplicated fields. 3. First Normal Form.First Normal Form splits the data into tables, which have no repeating groups or redundant data. If you split it into too many tables initially, some of them can be combined later.
Figure 3 - Companies Table.
Figure 4 – Contacts Table The result above does not link people in the Contact table with the companies in the Companies table, so it is necessary to duplicate the Primary key from the Companies table in the Contacts table also.
Figure 5 – revised Contacts table The Primary key in the Contacts table could not be either CoCode or Contact alone because both of these fields can correctly contain duplications, but needs to be a joint key (also known as a Composite key) – CoCode and Contact combined. CoCode in the Contacts table is a Secondary (also known as a Foreign key) linking back to the Companies table. 4. Second Normal Form.Second Normal Form occurs when all of the non-key fields are uniquely dependent on the Primary key. In the example below the Contacts table is extended to include Correspondence details - the date and type of contact made, and the name, department and position of the person making the contact.
Figure 6 – Contacts Table The new data breaks the Second Normal Form rule as the Dept and Position fields are not dependent on the joint key of CoCode and Contact. The details of the person making contact should be held in a separate Staff table.
Figure 7 – Staff Table The Staff table may be able to use Name as a primary key, or an additional field can be created containing a unique code for each individual. Either field could then be used in the Contacts table as a secondary key. The Contacts table then contains two secondary key fields, one linking to the Companies table and another linking to the Staff table. 5. Third Normal Form.This is an extension to Second Normal Form in that all of the non-key fields should also depend on the Primary key, including the whole key in a multiple key field. It is not really practical to keep correspondence type information in the Contacts table, as the contact details will be duplicated each time contact is made. It is better to create a separate table to hold correspondence data.
Figure 8 – Correspondence Table. In the Correspondence table only the Date and Type fields contain new data, the other fields are Secondary keys linking back to the parent tables – Contacts and Staff. The effect of this is that we now have four related tables – Companies linking to Contacts, linking to Correspondence, linking to Staff. 6. De-Normalisation.The effect of normalisation is to create many small tables containing just a few fields, which can in itself create unnecessary duplication of key fields. De-normalisation reverses the process until an acceptable level of duplication and redundancy is achieved. For example, a Staff database may keep a Staff History file. The current Salary details for each member of staff should rightly be kept in the Staff History file, but when the monthly payroll is run it would have to check the Staff History file for each member, slowing the process down. It is better to duplicate the current Salary in both places. Salary changes will take slightly longer to update because it needs to be changed in both places, but this is not performed as often as the monthly payroll, so it is a worthwhile trade off for speed in calculating the payroll.
|
|
|
|
[Home] [Database Creation] [Queries] [Maintenance] [Reports] [Normalisation] [Switchboards] [Macros] |