Normalization

Anomalies in the Base Table

• Insertion Anomaly: Cannot add a new course or instructor without a student enrolled.
• Update Anomaly: Changing an instructor's phone (e.g., Dr. Kumar's) requires updating multiple rows.
• Deletion Anomaly: Deleting a student's enrollment might lose course or instructor data if it's the only record for that course.

SQL CREATE TABLE Statement for 1NF

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    StudentName VARCHAR(50),
    CourseID VARCHAR(10),
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15),
    PRIMARY KEY (StudentID, CourseID)
);

Sample INSERT Statements for 1NF

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s01', 'Arjun', 'c01', 'DBMS', 'Dr. Kumar', '9876543210');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s01', 'Arjun', 'c02', 'Data Mining', 'Dr. Mehta', '9172467800');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s02', 'Priya', 'c01', 'DBMS', 'Dr. Kumar', '9876543210');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s03', 'Kiran', 'c03', 'AI', 'Dr. Rao', '9988776655');

2NF Normalization

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

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

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

3NF Normalization

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

CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    FOREIGN KEY (Instructor) REFERENCES Instructors(Instructor)
);

CREATE TABLE Instructors (
    Instructor VARCHAR(50) PRIMARY KEY,
    InstructorPhone VARCHAR(15)
);

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Sample INSERT Statements for 3NF

INSERT INTO Students (StudentID, StudentName) VALUES ('s01', 'Arjun');
INSERT INTO Students (StudentID, StudentName) VALUES ('s02', 'Priya');
INSERT INTO Students (StudentID, StudentName) VALUES ('s03', 'Kiran');

INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Kumar', '9876543210');
INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Mehta', '9172467800');
INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Rao', '9988776655');

INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c01', 'DBMS', 'Dr. Kumar');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c02', 'Data Mining', 'Dr. Mehta');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c03', 'AI', 'Dr. Rao');

INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s01', 'c01');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s01', 'c02');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s02', 'c01');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s03', 'c03');

SQL query to list all students along with their courses and instructor names using JOINs

SELECT 
    s.StudentID,
    s.StudentName,
    c.CourseID,
    c.CourseName,
    i.Instructor
FROM 
    Students s
INNER JOIN 
    Enrollment e ON s.StudentID = e.StudentID
INNER JOIN 
    Courses c ON e.CourseID = c.CourseID
INNER JOIN 
    Instructors i ON c.Instructor = i.Instructor;