Let's consider an unnormalized table named STUDENT_INFO with the following structure:
Student_I Student_Name Course_IDs Grades
D
101 Alice C1, C2, C3 90, 85, 70
102 Bob C1, C4 80, 95
1NF Normalization using Oracle SQL
To normalize this to 1NF, we need to eliminate repeating groups. In this case, the Course_IDs and
Grades are repeating groups.
Creating the Normalized Tables:
1. STUDENT table:
SQL
CREATE TABLE STUDENT (
Student_ID INT PRIMARY KEY,
Student_Name VARCHAR(50)
);
2. COURSE table:
SQL
CREATE TABLE COURSE (
Course_ID CHAR(3) PRIMARY KEY,
Course_Name VARCHAR(50)
);
3. STUDENT_COURSE table:
SQL
CREATE TABLE STUDENT_COURSE (
Student_ID INT,
Course_ID CHAR(3),
Grade INT,
PRIMARY KEY (Student_ID, Course_ID),
FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);
Inserting Sample Data:
SQL
INSERT INTO STUDENT VALUES (101, 'Alice');
INSERT INTO STUDENT VALUES (102, 'Bob');
INSERT INTO COURSE VALUES ('C1', 'Database Systems');
INSERT INTO COURSE VALUES ('C2', 'Data Structures');
INSERT INTO COURSE VALUES ('C3', 'Operating Systems');
INSERT INTO COURSE VALUES ('C4', 'Computer Networks');
INSERT INTO STUDENT_COURSE VALUES (101, 'C1', 90);
INSERT INTO STUDENT_COURSE VALUES (101, 'C2', 85);
INSERT INTO STUDENT_COURSE VALUES (101, 'C3', 70);
INSERT INTO STUDENT_COURSE VALUES (102, 'C1', 80);
INSERT INTO STUDENT_COURSE VALUES (102, 'C4', 95);