Quick Contact
DBMS Tutorial
 DBMS Tutorial
 What is Database Management System (DBMS)?
 Components of DBMS
 Applications of DBMS
 Three Schema DBMS Architecture
 Difference between DBMS and RDBMS?
 Difference between File Oriented System and DBMS
 Types of Data Models
 DBMS Schema and Instances
 Data Independence and Data Abstraction
 Database Users and Administrator
 DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
DBMS First Normal Form (1NF)
A relation is sent to be in particular normal form if it satisfies a certain specified set of constraints.
Database Normalization is classified into the following Normal forms:
First Normal Form (1NF)
A relation is, in first normal form if and only if all underlying domains contain atomic values only.
The aim of the first normal form (1NF) is to make the structure of a relation simple by making sure that it does not have data aggregates or repeating groups. This means that none of the attribute values can contain a collection of values.
It specifies that the domains of attributes are inclusive of only atomic values and that any attribute value in a tuple must be an individual value from the domain of that attribute.
Normalization Procedure
The following table is 1NF.
A  B  C 

x  1  {a,b} 
z  2  c 
Transformation to 1NF
A  B 

x  1 
z  2 
Method1: By splitting the relation the relations into new relations.
B  C 

1  a 
1  b 
2  c 
Method2: By adding new tuples in the same relation.
A  B  C 

x  1  a 
x  1  b 
z  2  c 
Method3: By adding new attributes with NULL values.
A  B  C  C’ 

x  1  a  b 
z  2  c  NULL 
In the following example, anyone employee has more than one telephone number.
EMP
EMPCODE  EMPNAME  TELNO 

001  Mohan  9324567890 8732456210 
002  Rajesh  8234786419 9176329056 
003  Saksham  9023431784 8843874218 
This is thus not in 1NF. This can be managed by ensuring that every tuple defines a single entity by containing only atomic values. One can either reorganize into one relation as in:
EMP
EMPCODE  EMPNAME  TELNO 

001  Mohan  9324567890 
001  Mohan  8732456210 
002  Rajesh  8234786419 
002  Rajesh  9176329056 
003  Saksham  9023431784 
003  Saksham  8843874218 
Or split into multiple relations as in:
EMP
EMPCODE  EMPNAME 

001  Mohan 
002  Rajesh 
003  Saksham 
EMPTEL
EMPCODE  TELNO 

001  9324567890 
001  8732456210 
002  8234786419 
002  9176329056 
003  9023431784 
003  8843874218 
Reduction to 1NF
Example
Consider the relation FIRST (S#, STATUS, CITY, P#, QTY) as shown in table.
FIRST
S#  STATUS  CITY  P#  QTY 

S1  40  Delhi  P1  100 
S1  40  Delhi  P2  125 
S1  40  Delhi  P3  130 
S1  40  Delhi  P4  115 
S2  10  Noida  P1  200 
S2  10  Noida  P2  215 
S3  20  Karnal  P1  200 
S4  40  Delhi  P4  200 
S4  40  Delhi  P5  300 
Here we assume that each supplier has a unique supplier number (S#), precisely one status code and location. A supplier can supply different parts. Further, we assume that STATUS is functionally dependent on CITY. Therefore all Delhi suppliers must have a status of 40.
Figure display the functional dependency structure for this relation. The key to FIRST relation is the combination of (S#, P#).
It is clear that the relation FIRST is in the first normal form. The relation FIRST has the following modification anomalies.

Deletion Anomaly
If we delete the FIRST tuple with S# value S3 and P# value P1, we lose the information that S3 is located in Karnal. Hence, the relation FIRST has a deletion anomaly.

Insertion Anomaly
We cannot enter the fact that a particular supplier is located in a specific supplier city until the supplier supplies at least one part. So, we cannot enter the fact that supplier S5 is located in Rohtak until S5 supplies at least one part.

Updation Anomaly
It supplies S1 moves from Delhi to Chandigarh, and we are faced with either the problem of searching the FIRST relation to find every tuple connecting S1 and Delhi and change it or the possibility of producing an inconsistent result (the city for S1 may be given as Delhi in one place and Chandigarh in another).
The solution to these problems is to restore the relation FIRST by two relation SECOND (S#, STATUS, CITY) and SP (S#, P#, QTY). The figure shows sample tabulation corresponding to the data values.
SECOND
S#  STATUS  CITY 

S1  40  Delhi 
S2  10  Noida 
S3  20  Karnal 
S4  40  Delhi 
SP
S#  P#  CITY 

S1  P1  100 
S1  P2  125 
S1  P3  130 
S1  P4  115 
S2  P1  200 
S2  P2  215 
S3  P1  200 
S4  P4  200 
S4  P5  300 