NORMALISING DATABASES CORE 2 Information systems and Databases

NORMALISING DATABASES CORE 2: Information systems and Databases

NORMALISING DATABASES Normalising is the process of normalising the design of a database to exclude redundant data. Redundant Data is unnecessary duplicate data. Reducing or preferably eliminating data redundancy is the aim of normalisation. When we normalise we re-design the database schema for a more logical organisation. It involves splitting the data into tables linked by relationships, aiming to reduce redundant data. Redundant data wastes storage space and creates maintenance problem. If duplicate data exists in different locations then changes must be made numerous times and if all copies are not altered then data integrity issues arise. Eg. A postal address changes for a supplier.

NORMALISING DATABASES The normalisation process is theoretically performed by decomposing the design into a sequence of ‘normal forms’, where each normal form is a rule with which the database must comply. Technically here are 8 forms that experienced database designers use to create ‘normal forms’. In IPT we will restrict our less-technical process down to 3 normal forms which we will call. . § 1 NF – First Normal form § 2 NF – Second Normal form § 3 NF – Third Normal form

FIRST NORMAL FORM (1 NF) The first normal form deals with the removal of repeating attributes across horizontal rows and ensures each field holds only single data items. To achieve first normal form we must ensure… 1. 2. Each field stores single data There are no multiple data items within indv. fields and no fields are repeated. Typically, to meet the above requirements, we perform the followings processes… § Splitting Fields into smaller units of data (to achieve 1) § Deleting repeated fields (to achieve 2) § Creating new records for each multiple data item and each repeated field (to achieve 2)

SECOND NORMAL FORM (2 NF) The second normal removes redundant data within vertical columns or fields. To achieve second normal form we must ensure… 1. 2. All tables must be in first normal form. Every non key attribute is functionally dependent on the table’s primary key. Typically, to meet the above requirements, we perform the followings processes… § Determine Function Dependencies by looking at data redundancies records (horizontal) or fields (verticals). § Determine a primary key for each set of functionally dependent attributes. § Create new tables for each entity – populate new tables, delete data in old tables

THIRD NORMAL FORM (3 NF) The third normal removes further redundant data within vertical columns or fields. To achieve third normal form we must ensure… 1. 2. All tables must be in second normal form. Every non key attribute is functionally dependent on the table’s primary key and not on any other attributes of the table. Typically, to meet the above requirements, we perform the followings processes… § Look for non-key attributes that are functionally dependent on another non-key attribute. § Determine a primary key for each set of functionally dependent attributes. § Create new tables for each entity – populate new tables, delete data in old tables