Understanding Normalization in DBMS: Oracle Live SQL Practical Guide

Understanding Normalization in DBMS: Oracle Live SQL Practical Guide
Database normalization is a key concept in designing efficient, consistent, and scalable relational databases. In this post, let's explore normalization—why it's important, the various normal forms, and how to implement normalized structures using Oracle Live SQL with real-world code samples.

What is Normalization?
Normalization is the process of organizing data and tables in a database to reduce redundancy and improve data integrity. By breaking down larger tables into smaller, related tables and defining relationships among them, we can avoid data anomalies and ensure efficient storage.

Why Normalize Your Database?
Reduces data redundancy: Prevents duplicate data.
Prevents update anomalies: Makes updates easier and more consistent.
Improves data integrity: Ensures accuracy and consistency.

Step-by-Step Example: Oracle Live SQL
Let's walk through a practical example with student-course-instructor data.

1.Unnormalized Table
Suppose all data is stored in a single table:

StudentID	StudentName	CourseID	CourseName	InstructorName	InstructorPhone
S01	Arun	C101	Cloud	Dr.sandy	9876533310
S01	Arun	C102	Business Comm	Dr.pritheem	9876555310
S02	manoj	C101	Cloud	Dr.sandy	9876533310

2.First Normal Form (1NF)
Split repeating groups into individual rows so that each field contains atomic values. This is already achieved in our sample.

3.Second Normal Form (2NF)
Remove partial dependencies—every non-key attribute must depend on the full primary key. Break the above into multiple related tables:

STUDENTS Table:

sql
CREATE TABLE STUDENTS_ (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

INSERT INTO STUDENTS_ VALUES
('S01', 'Arun'),
('S02', 'manoj'),
('S03', 'ramanas');
INSTRUCTORS Table:

sql
CREATE TABLE INSTRUCTORS (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO INSTRUCTORS VALUES
(1, 'Dr.sandy', '9876533310'),
(2, 'Dr.shanmugam', '9116533310'),
(3, 'Dr.pritheem', '9876555310');
COURSES Table:

sql
CREATE TABLE COURSES_ (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO COURSES_ VALUES
('C101', 'cloud', 'Dr.sandy', '9876533310'),
('C102', 'business communication', 'Dr.pritheem', '9876555310'),
('C103', 'data science', 'Dr.shanmugam', '9116533310');
STUDENTCOURSES Table (Associative/Mapping Table):

sql
CREATE TABLE STUDENTCOURSES (
StudentID VARCHAR(10),
CourseID VARCHAR(10)
);

INSERT INTO STUDENTCOURSES VALUES
('S01', 'C101'),
('S01', 'C102'),
('S02', 'C101'),
('S03', 'C103');

4.Third Normal Form (3NF)
Remove transitive dependencies. In this example, instructor information is best stored in the INSTRUCTORS table, referenced by course, rather than repeated in COURSES.

How to Query the Normalized Database
Retrieve complete information using JOINs:

sql
SELECT s.StudentName, c.CourseName, i.InstructorName, i.InstructorPhone
FROM StudentCourses sc
JOIN STUDENTS_ s ON sc.StudentID = s.StudentID
JOIN COURSES_ c ON sc.CourseID = c.CourseID
JOIN INSTRUCTORS i ON c.InstructorPhone = i.InstructorPhone;

Sample Result:

StudentName	CourseName	InstructorName	InstructorPhone
manoj	cloud	Dr.sandy	9876533310
Arun	cloud	Dr.sandy	9876533310
Arun	business communication	Dr.pritheem	9876555310
ramanas	data science	Dr.shanmugam	9116533310

Key Takeaways
Normalization structures your database for efficiency and integrity.
Separate data by entities (students, courses, instructors) and use relationships (foreign keys, mapping tables).
Use JOINs to re-combine related data as needed.
Try these SQL snippets in Oracle Live SQL to practice normalization hands-on!

Posted using Oracle Live SQL screenshots and real coding experience. Happy normalizing!