//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)