What is 1NF in DBMS? Explain with examples

DBMSDatabaseBig Data Analytics

A relation is in 1NF if it contains atomic values. It states that an attribute of a table cannot hold multiple values. It must hold only single-values attributes. First normal form disallows the multi-valued attributes, composite attributes, and their combinations.

Requirements

The requirements to be considered while designing 1 NF are explained below −

  • Each table has a primary key (minimal set of attributes which can uniquely identify a record).

  • The values in each column of a table are atomic (no multi0value attributes are allowed).

  • There are no repeating groups (two columns do not store similar information in the same table).

Example 1

Consider a relation student (rollno, name, branch, address, phone).

RollnoNameBranchAddressPhone
1AAACSEHyderabad3242344,4564555,3112453
2BBBECEDelhi3452245,4323245

The above relation is not in 1NF because the phone is a multivalued attribute, which is having multiple numbers for a single person.

Now we represent the above table by creating a new row for each phone number as shown below −

RollnoNameBranchAddressPhone
1AAACSEHyderabad3242344
1AAACSEHyderabad4564555
1AAACSEHyderabad3112453
2BBBECEDelhi3452245
2BBBECEDelhi4323245

The above table contains redundant data due to phone numbers, as for each phone number, we have to repeat all the information of the student. So the phone attribute should be separated from the above table.

We divide or decompose the above table R into two tables which is the concept of normalization −

R1(key, multivalued attribute), R2(R-multivalued attribute)

=>R1(rollno, phone), R2(rollno, name, branch, address).

Steps to decompose the 1NF table are as follows

  • Place all items that appear in the repeating group in a new table.

  • Find a primary key for each new table produced.

  • Duplicate in a new table the primary key of the table from which the repeating group was extracted or vice versa.

R1

RollnoPhone
13242344
14564555
13112453
23452245
24323245

R2

RollnoNameBranchAddress
1AAACSEHyderabad
2BBBECEDelhi

R1 and R2 are in 1NF.

Key of R1 = rollno

Key of R2 = (rollno, phone)

Example 2

Consider another example to check whether the given table is 1NF or not, if not try to convert into 1NF.

Un-normalized table R1

CourseContent
ProgrammingC,C++,java
Scripting languageHTML,javascript

Normalized table (1NF)

CourseContent
ProgrammingC
ProgrammingC++
ProgrammingJava
Scripting languageHTML
Scripting languagejavascript
raja
Published on 03-Jul-2021 09:38:26
Advertisements