The concept of the first normal form (1NF) has been explained in this tutorial by creating the tables in the Oracle database. Run the SQL Developer application and logged in with the valid username and password. Next, create a database named ‘testdb’ where the tables will be created.

Create a table:

Create a table named STD_INFO in the current database by executing the following SQL.

CREATE TABLE STD_INFO
(
ID NUMBER NOT NULL
, NAME VARCHAR2(50) NOT NULL
, DEPARTMENT VARCHAR2(10) NOT NULL
, BATCH NUMBER(3) NOT NULL
, SEMESTER NUMBER(3) NOT NULL
, CONSTRAINT STD_INFO_PK PRIMARY KEY (ID)
);

The structure of the table will be look like the following image,

Insert data into the table:

Run the following SQL statements to insert 5 records into the table.

INSERT INTO "SYSTEM"."STD_INFO" (ID, NAME, DEPARTMENT, BATCH, SEMESTER) VALUES ('1', 'Arabi', 'CSE', '57', '8');
INSERT INTO "SYSTEM"."STD_INFO" (ID, NAME, DEPARTMENT, BATCH, SEMESTER) VALUES ('2', 'Rasel', 'CSE', '57', '8');
INSERT INTO "SYSTEM"."STD_INFO" (ID, NAME, DEPARTMENT, BATCH, SEMESTER) VALUES ('3', 'Rumman', 'CSE', '55', '12');
INSERT INTO "SYSTEM"."STD_INFO" (ID, NAME, DEPARTMENT, BATCH, SEMESTER) VALUES ('4', 'Romana', 'BBA', '50', '5');
INSERT INTO "SYSTEM"."STD_INFO" (ID, NAME, DEPARTMENT, BATCH, SEMESTER) VALUES ('5', 'Janifar', 'BBA', '49', '11');

The content of the table will be looked like the following image.

First Normal Form (1NF):

According to the table data, the DEPARTMENT, BATCH, and SEMESTER fields of the table contains same data multiple times. First normal form(1NF) can be applied in the table by dividing the table structure into two tables named STD_BASIC and STD_DEPT.

Run the following SQL command to create the table named STD_BASIC.

CREATE TABLE STD_BASIC
(
  ID NUMBER NOT NULL 
, NAME VARCHAR2(50) NOT NULL 
, BATCH NUMBER(3) NOT NULL 
, SEMESTER NUMBER(3) NOT NULL 
, CONSTRAINT STD_BASIC_PK PRIMARY KEY (ID)
);

The table structure will be looked like the following image.

Run the following SQL to insert 5 records into the STD_BASIC table.

INSERT INTO "SYSTEM"."STD_BASIC" (ID, NAME, BATCH, SEMESTER) VALUES ('1', 'Arabi', '57', '8');
INSERT INTO "SYSTEM"."STD_BASIC" (ID, NAME, BATCH, SEMESTER) VALUES ('2', 'Rasel', '57', '8');
INSERT INTO "SYSTEM"."STD_BASIC" (ID, NAME, BATCH, SEMESTER) VALUES ('3', 'Rumman', '55', '12');
INSERT INTO "SYSTEM"."STD_BASIC" (ID, NAME, BATCH, SEMESTER) VALUES ('4', 'Romana', '50', '5');
INSERT INTO "SYSTEM"."STD_BASIC" (ID, NAME, BATCH, SEMESTER) VALUES ('5', 'Janifer', '49', '11');

Run the following SQL command to create the table named STD_DEPT.

CREATE TABLE STD_DEPT 
(	
  STD_ID NUMBER NOT NULL, 
  DEPARTMENT VARCHAR2(10 BYTE) NOT NULL, 
  CONSTRAINT STD_DEPT_PK PRIMARY KEY (STD_ID, DEPARTMENT),
  CONSTRAINT STD_DEPT_FK1 FOREIGN KEY (STD_ID)
  REFERENCES STD_BASIC (ID) ON DELETE CASCADE
);

The table structure will be looked like the following image.

Run the following SQL to insert 5 records into the STD_DEPT table.

INSERT INTO "SYSTEM"."STD_DEPT" (STD_ID, DEPARTMENT) VALUES ('1', 'CSE');
INSERT INTO "SYSTEM"."STD_DEPT" (STD_ID, DEPARTMENT) VALUES ('2', 'CSE');
INSERT INTO "SYSTEM"."STD_DEPT" (STD_ID, DEPARTMENT) VALUES ('3', 'CSE');
INSERT INTO "SYSTEM"."STD_DEPT" (STD_ID, DEPARTMENT) VALUES ('4', 'BBA');
INSERT INTO "SYSTEM"."STD_DEPT" (STD_ID, DEPARTMENT) VALUES ('5', 'BBA');

The content of the STD_BASIC table will be looked like the following image.

The content of the STD_DEPT table will be looked like the following image.

The redundancy of one column has been eliminated by applying first normal form (1NF).

Exercise:

Apply 1NF again in the STD_BASIC table to remove other redundancy.

Second Normal Form (2NF)