FahmidasClassroom

Learn by easy steps

You Are Here

P1
Database

Third Normal Form (3NF) in Oracle

no Comments

The concept of the third normal form (2NF) has been explained in this tutorial by using the Oracle database tables. Run the SQL Developer application and logged in with the valid username and password. You have to complete the tutorial of the 2NF before starting this tutorial.

Run the following SQL statements to create a table named STD_PROGRAM and insert four records into the table.

Create table:

CREATE TABLE STD_PROGRAM
(
  STD_ID NUMBER NOT NULL 
, DEPT_ID NUMBER NOT NULL 
, COURSE_TYPE VARCHAR2(50) NOT NULL 
, DURATION VARCHAR2(20) NOT NULL 
, CONSTRAINT STD_PROGRAM_PK PRIMARY KEY (STD_ID , DEPT_ID , COURSE_TYPE)
);

INSERT INTO "SYSTEM"."STD_PROGRAM" (STD_ID, DEPT_ID, COURSE_TYPE, DURATION) VALUES ('1', '1', 'B.SC', '4');
INSERT INTO "SYSTEM"."STD_PROGRAM" (STD_ID, DEPT_ID, COURSE_TYPE, DURATION) VALUES ('2', '1', 'B.SC', '4');
INSERT INTO "SYSTEM"."STD_PROGRAM" (STD_ID, DEPT_ID, COURSE_TYPE, DURATION) VALUES ('3', '2', 'DIPLOMA', '3');
INSERT INTO "SYSTEM"."STD_PROGRAM" (STD_ID, DEPT_ID, COURSE_TYPE, DURATION) VALUES ('4', '2', 'B.SC', '4');

The structure of the STD_PROGRAM table will be looked like the following image.

P1 4

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

P2 4

Third Normal Form (3NF):

The redundant data exists In the above table. The same value has been repeated in the COURSE_TYPE column. The STD_ID and DEPT_ID columns are the primary keys of the STD_BASIC and the DEPRTMENT tables. This type of redundancy can be removed by third normal form (3NF). Divide the structure of the table in two tables. These are PROGRAMS and STD_PROGRAM2.

Run the following SQL statements to create a table named PROGRAMS and insert three records into the table.

CREATE TABLE PROGRAMS 
(
  ID NUMBER NOT NULL 
, DURATION NUMBER NOT NULL 
, NAME VARCHAR2(20) NOT NULL 
, CONSTRAINT PROGRAMS_PK PRIMARY KEY (ID ) 
);

INSERT INTO "SYSTEM"."PROGRAMS" (ID, DURATION, NAME) VALUES ('1', '3', 'DIPLOMA');
INSERT INTO "SYSTEM"."PROGRAMS" (ID, DURATION, NAME) VALUES ('2', '4', 'B.SC.');
INSERT INTO "SYSTEM"."PROGRAMS" (ID, DURATION, NAME) VALUES ('3', '2', 'M.SC');

The structure of the PROGRAMS table will be looked like the following image.

P3 2

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

Run the following SQL statements to create a table named STD_PROGRAM and insert three records into the table.

P4 7
CREATE TABLE STD_PROGRAM2
(
  STD_ID NUMBER NOT NULL 
, DEPT_ID NUMBER NOT NULL 
, PROGRAM_ID NUMBER NOT NULL 
, CONSTRAINT STD_PROGRAM2_PK PRIMARY KEY (STD_ID , DEPT_ID , PROGRAM_ID)
, CONSTRAINT "FK_STDID" FOREIGN KEY ("STD_ID")
	  REFERENCES "SYSTEM"."STD_BASIC" ("ID") 
, CONSTRAINT "FK_DEPTID" FOREIGN KEY ("DEPT_ID")
	  REFERENCES "SYSTEM"."DEPARTMENT" ("ID")
, CONSTRAINT "FK_PID" FOREIGN KEY ("PROGRAM_ID")
	  REFERENCES "SYSTEM"."PROGRAMS" ("ID")
);


INSERT INTO "SYSTEM"."STD_PROGRAM2" (STD_ID, DEPT_ID, PROGRAM_ID) VALUES ('1', '1', '2');
INSERT INTO "SYSTEM"."STD_PROGRAM2" (STD_ID, DEPT_ID, PROGRAM_ID) VALUES ('2', '1', '2');
INSERT INTO "SYSTEM"."STD_PROGRAM2" (STD_ID, DEPT_ID, PROGRAM_ID) VALUES ('4', '2', '3');

The structure of the STD_PROGRAM table will be looked like the following image.

P5 1

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

P6 2

Exercise:

Suppose, you have to design a simple database for a mobile company to handle customer information by creating multiple tables. Create the necessary tables with data and show the uses of 1NF, 2NF, and 3NF in the tables to remove redundant data from the table.