Many to Many Relationship Example

After installing oracle database and oracle sql developer (https://rahmadya.com/2014/09/05/instal-oracle11g/), now we try to use it. Many to many relationship sometimes said M:N is happened if every member of an entity has many members related to other entity. For example we have relation Student and Course. We have relationship Student with Course that every student could take more than one courses and every course has a lot of student who take it.

Open your SQL Developer to practice how to create that kind of relation. First create the Student and Course entity respectively. Use CREATE table:

• CREATE TABLE Student(
• Student_ID CHAR(10) CONSTRAINT StudentID_PK PRIMARY KEY,
• First_Name CHAR(20) NOT NULL,
• MI CHAR(1) NULL,
• Last_Name CHAR(20) NOT NULL);

This will create a Student table with attribute/field Student_ID, First_Name, and Last_Name. The number inside the bracket “(” is number of character (look at Middle Name that only limited to one character). There some constraint such as PRIMARY KEY and NOT NULL. Add some record using INSERT statement:

• INSERT INTO Student (Student_ID, First_Name, MI, Last_Name) VALUES (‘st115995’, ‘Rahmadya’,’T’,’Handayanto’);
• INSERT INTO Student (Student_ID, First_Name, MI, Last_Name) VALUES (‘st115996′,’James’,’D’,’Brynt’);
• INSERT INTO Student (Student_ID, First_Name, MI, Last_Name) VALUES (‘st115997’, ‘John’,”,’Travis’);
• INSERT INTO Student (Student_ID, First_Name, MI, Last_Name) VALUES (‘st115998′,’Brian’,’L’,’Walk’);
• INSERT INTO Student (Student_ID, First_Name, MI, Last_Name) VALUES (‘st115999’, ‘Susan’,”,’Tarson’);

If we want to retrieve the record that we have just insert, try to use SELECT * from Student;

Use the similar SQL Command to create a Course table with attribute Course_ID, CourseName, Credit, and Department. Use Course_ID as a primary key.

• CREATE TABLE Course(
• Course_ID CHAR(10) CONSTRAINT CourseID_PK PRIMARY KEY,
• CourseName Char(50) NOT NULL,
• Credits INTEGER NOT NULL,
• Department CHAR(20) NOT NULL);

Then, Add some records e.g:

• INSERT INTO Course (Course_ID, CourseName, Credits, Department) VALUES (‘AT72.01′,’Database Management System’, 3,’CSIM’);
• INSERT INTO Course (Course_ID, CourseName, Credits, Department) VALUES (‘AT72.03′,’E-Business Development’, 3,’CSIM’);
• INSERT INTO Course (Course_ID, CourseName, Credits, Department) VALUES (‘AT72.04′,’Decision Support Technologies’, 3,’CSIM’);

Because M:N relationship, when we convert from ER-Diagram into Relational Table we have to create one relation that connect the student to course, for example we name it Student_Course table.

• CREATE TABLE Student_Course (Student_ID CHAR(10), Course_ID CHAR(10));
• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115996′,’AT72.01’);
• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115996′,’AT72.03’);
• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115997′,’AT72.01’);
• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115997′,’AT72.04’);

We have a simple Student_Course table with four students who enroll the class. This table has not used relation between student and course. Therefore we have to add foreign key from Student and Course table using ALTER statement.

• ALTER TABLE Student_Course ADD CONSTRAINT CourseID_FK FOREIGN KEY (Course_ID) REFERENCES Course (Course_ID);
• ALTER TABLE Student_Course ADD CONSTRAINT StudentID_FK FOREIGN KEY (Student_ID) REFERENCES Student (Student_ID);

Do we been allowed to insert duplicate foreign key? For example a student takes many courses? Test using this INSERT example:

• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115996′,’AT72.01’);
• INSERT INTO STUDENT_COURSE (Student_ID, Course_ID) VALUES (‘st115996′,’AT72.04’);

We need a primary key to identify the transaction of student who make an enrollment.

ALTER TABLE Student_Course ADD CONSTRAINT Student_ID_Course_ID_PK PRIMARY KEY (Student_ID, Course_ID);

The unique attribute sometimes needed if we avoid the similar record that different primary key. For example we make the course_name unique.

• ALTER TABLE Course ADD CONSTRAINT CourseName_uk UNIQUE(CourseName);

For example we add record that violate this Unique constraint.

• INSERT INTO Course (Course_ID, CourseName, Credits, Department) VALUES (‘AT72.10′,’Database Management System’, 3,’CSIM’);

You will see an error on it. That’s it.