Definition and Types of Normal Forms: Data Processing Lesson Note

Lesson Note

Subject: Data Processing
Topic: Normal Form

Lesson Objectives: the aim of this lesson is to equip learners with the knowledge of Database Normal Forms.

Lesson Summary/Discussion
The term database has been defined in our previous lesson. A database management system can be defined as the collection of computer software that enables users to define, create and maintain a database. Defining a database involves specifying constraints for the data stored in the database, and normalization is a logical design method which minimizes duplicate, or redundant, data and design flaws. Data Form Normalization can be defined as the process of effectively organizing data in a database. The primary aim of normalization is to enable us perform some operations such as update, insert and delete on a single database table and propagated throughout the database by means of the defined relationship.

GOALS OF NORMALIZATION

There are two goals of the normalization process:

Eliminating redundant data: E.g storing the same data in more than one table.
Ensuring data dependencies make sense i.e only storing related data in a table.

NORMAL FORMS
The normal form (NF) of relational database theory provide critical for determining a table’s degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is. Each table has a “Highest Normal Form”(HNF): by definition, a table always meets the requirements of its HNF and all normal forms lower than its HNF; also by definition, a table fails to meet the requirement of any normal form higher than its HNF.

Normal form theory deal with how to reduce the amount of redundancy of data within a given table. Each normal form represents a level. To satisfy each the requirements for certain level, the requirements for the previous level must be met. To reach the optimal normal form for the tables within a database, the creator starts with a large list of all the data that is to be held in the database, and then works through the normal forms until he can no longer break the data down into smaller table.

Types of Normal Form

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

Fourth Normal Form (4NF)

Fifth Normal Form (5NF)

These are discussed below:

1. First Normal Form (1NF): This type of Normal Form says that all column values must be atomic. 1NF dictates that, for every row by column position in a given table, there exist only one value, not an array or list of values. In other words, in 1NF, one must observe the rules below:

Eliminating repeating information.
Create separate tables for related data.

Below is an illustration of 1NF

2. Second Normal Form (2NF)
The second normal form (2NF) further addresses the concept of removing duplicate data. The rule for the second normal form is;
Remove subsets of data that apply to multiple rows of a table and place them in separate table.
Create relationships between these new tables and their predecessors through the use of foreign keys.

Below is an illustration of 2NF

3. Third Normal Form (3NF): There are two basic requirements for a database to be in Third Normal Form:

The requirements of both 1NF and 2NF must have been met.
Remove columns that are not fully dependent upon the primary key.

4. The Fourth Normal Form (4NF) and Fifth Normal Form (5NF)
The fourth and the fifth normal form are beyond the scope of this syllabus. It is important to note that both the fourth and the fifth normal form still follows the goal of normalization process as stated below:

eliminating redundant data.
ensuring data dependencies make sense.

KEY AS NORMAL FORM
A key is an attribute or field that can be used to identify a record in a database table or file.
The primary Key is a unique attribute that can be used to identify a record in a database table. For Example, in the student database table above, the attribute that is unique to identify each record is the Student-ID or RegNo.

THE CONCEPT OF FOREIGN KEY
A foreign key is a field in a relational table that matches a candidate key of another table. A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a Foreign Key constraint when you create or modify a table. The foreign key can be used to cross-reference table. The foreign key identifies a column or set of columns in one (referencing or child) table that refers to a column or set of column in another (referenced or parent) table. The columns in the child table must reference the columns of the primary key or other super key in the parent table.

Lesson Evaluation/Test