Martin has 20 years experience in Information Systems and Information Technology, has a PhD in Information Technology Management, and a master's degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.
First Normal Form in DBMS with Examples
First Normal Form
First Normal Form (1NF) is the first step in database normalization; it's a great start to reducing redundancy in the database. Before we get started on first normal form (1NF) let's quickly recap normalization.
Normalization is a process by which tables are organized in a database; the purpose is to reduce complexity. When you normalize tables, you often have to create additional tables in order to keep like data in like tables. For example, it doesn't make sense to put an entire personnel database into a single table. It's better to split out and join those items that should be related. So the employee table would be joined to a benefits table.
There are three forms of normalization, first normal, second normal, and third normal. This lesson will cover the first and least complex of the forms, first normal (1NF).
General Rules
First normal may be the basic or first level, but that doesn't mean that all of the data is dumped into one table.
Each type of related data should be set up in its own table. All of the tables will contain a primary key, a unique value for each record in the table. Additionally, each data element will be granular, which means it cannot be split into smaller chunks. One example would be an employee's name, which can be broken into first and last names.
The general rules for first normal form are:
- All tables have a primary key
- Fields have unique names
- Data is not repeated across fields and
- There's no redundant data, like a field that is a combination of other fields
A primary key is a requirement for any table in a relational database. It's a unique identifier that corresponds to each row of data. Typically, it's a numerical value. Database systems automatically assign the number (usually sequential) to new entries in the table.
Field and column names must be unique. For instance, you can't have three fields named Color in a Product table. Along those same lines, having a field called Zip code and another called Postal code isn't any better, because the data being stored is the same. And this violates the next rule of 1NF: don't repeat data.
This means that a table shouldn't have fields that store similar data that isn't part of the table. The Employee table, for example, may have benefit information, such as data for Benefit Plan A and Benefit Plan B. If we added a Plan C, it would violate the normalization rules by jamming all of the benefits data into the Employee table. A better approach would be to move the benefits info to another table and join the two tables.
Examples of 1NF
Let's take a look at some examples of how to implement the first normal form (1NF).
Example 1: Primary Key and Redundant Data
The following table is not in 1NF. There isn't a primary key, the Emp_Full_Name field is repetitive because we already have name fields.
Emp_First | Emp_Last | Emp_Full_Name | Job_Title | Department |
---|---|---|---|---|
Jane | Austen | Jane Austen | CFO | Administration |
Now let's convert this to 1NF. We'll get rid of the full name field (a full name can be created using a simple SQL query) and add a primary key:
Emp_ID | Emp_First | Emp_Last | Job_Title | Department |
---|---|---|---|---|
124002 | Jane | Austen | CFO | Administration |
Example 2: Redundant Field Names
This example isn't a 1NF either. Yes, there is a primary key, but the field name Benefit is repeated.
Emp_ID | Emp_First | Emp_Last | Benefit | Benefit | Job_Title |
---|---|---|---|---|---|
19994 | Poe | Edgar | Health | Dental | Author |
In order to include all of the employee benefit plans, we'll need to create another table that holds this information and then join the two tables. Instead, we'll recreate the benefit fields to only hold the ID's to the other tables:
Emp_ID | Emp_First | Emp_Last | Health_Plan | Dental_Plan |
---|---|---|---|---|
19994 | Poe | Edgar | 1024 | 2048 |
Lesson Summary
The first normal form (1NF) is the first step in normalizing a table by reducing confusion and redundancy. In 1NF, we remove the redundant columns (columns with the same name and/or data) and redundant fields (such as a full name field when we already have first and last names), and add a primary key. The primary key ensures that each record can be uniquely identified. It also helps to join it to other tables needed during the normalization process. In 1NF, each data element will be granular, which means it cannot be split into smaller chunks.
To unlock this lesson you must be a Study.com Member.
Create your account
Register to view this lesson
Unlock Your Education
See for yourself why 30 million people use Study.com
Become a Study.com member and start learning now.
Become a MemberAlready a member? Log In
Back