DEV Community

Raksh !!
Raksh !!

Posted on

1NF , 2NF, 3NF in DBMS...

//1 NF
CREATE TABLE StudentCourses (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO StudentCourses VALUES ('S01', 'Arjun', 'C101', 'DBMS', 'Dr. Kumar', '9876543210');
INSERT INTO StudentCourses VALUES ('S01', 'Arjun', 'C102', 'Data Mining', 'Dr. Mehta', '9123456780');
INSERT INTO StudentCourses VALUES ('S02', 'Priya', 'C101', 'DBMS', 'Dr. Kumar', '9876543210');
INSERT INTO StudentCourses VALUES ('S03', 'Kiran', 'C103', 'AI', 'Dr. Rao', '9988776655');

///2 NF
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');

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

INSERT INTO Course VALUES ('C101', 'DBMS', 'Dr. Kumar', '9876543210');
INSERT INTO Course VALUES ('C102', 'Data Mining', 'Dr. Mehta', '9123456780');
INSERT INTO Course VALUES ('C103', 'AI', 'Dr. Rao', '9988776655');

CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

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


////3 NF

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

INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');

CREATE TABLE Course (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50)
);

INSERT INTO Course VALUES ('C101', 'DBMS', 'Dr. Kumar');
INSERT INTO Course VALUES ('C102', 'Data Mining', 'Dr. Mehta');
INSERT INTO Course VALUES ('C103', 'AI', 'Dr. Rao');

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

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

CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

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

Top comments (0)