NORMALIZATION

Step 1: Identify anomalies in the base table

Base table:

StudentID StudentName CourseID CourseName Instructor InstructorPhone
S01 Arjun C101 DBMS Dr. Kumar 9876543210
S01 Arjun C102 Data Mining Dr. Mehta 9123456780
S02 Priya C101 DBMS Dr. Kumar 9876543210
S03 Kiran C103 AI Dr. Rao 9988776655

Anomalies:

Insertion anomaly: Cannot insert a new instructor without assigning a student.

Update anomaly: Updating InstructorPhone requires multiple rows to be updated (e.g., Dr. Kumar’s phone).

Deletion anomaly: Deleting the last student in a course deletes course & instructor info.

Step 2: Convert to 1NF

1NF Rule: Remove repeating groups and make all attributes atomic.

Our table already has atomic values, so 1NF is essentially the same table. But for clarity, we define a primary key on StudentID + CourseID.
CREATE TABLE StudentCourse_1NF (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15),
PRIMARY KEY (StudentID, CourseID)
);

Step 3: Convert to 2NF

2NF Rule: Remove partial dependency. Here, StudentName depends only on StudentID and CourseName & Instructor info depends only on CourseID.
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID)
);

Step 4: Convert to 3NF

3NF Rule: Remove transitive dependency. InstructorPhone depends on Instructor, so create a separate Instructors table
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Step 5: Insert sample data into 3NF tables
-- Students
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

-- Instructors
INSERT INTO Instructors VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructors VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructors VALUES ('I03', 'Dr. Rao', '9988776655');

-- Courses
INSERT INTO Courses VALUES ('C101', 'DBMS', 'I01');
INSERT INTO Courses VALUES ('C102', 'Data Mining', 'I02');
INSERT INTO Courses VALUES ('C103', 'AI', 'I03');

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

Step 6: Query to list all students with courses and instructor names

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.Instruct