Database Management System [Resolved]

- A particular grade may be applied to Many Students (1: M)

- One student can obtain a max of one Grade (1:1)

- Many grades can be applied to many students (M:M) e.g. (Grade A may be attributed to Student 1 and Student 2. Grade B may be applied for Student 3. Grade C may be attributed to Student 4 , student 5, Student 6 etc...)

In such a case since it goes for a M:M relationship, to solve this kind of problem , the better approach is to maintain a Junction table which allows to create the many-to-many relationship and help us to abstain from duplicate entries.

The below example will give a better picture about it.

Lets create a Student table and a Grade table.

CREATE TABLE tblStudents
(
    StudentID int IDENTITY(1,1) PRIMARY KEY,
    StudentName varchar(50) NOT NULL
)

CREATE TABLE tblGrades
(
    GradeID int IDENTITY(1,1) PRIMARY KEY,
    Grade char(1) NOT NULL
)

Now that we have our two tables created we need to create the junction table that will link them together. The junction table is created by using the primary key from the Student table abd Grade table

CREATE TABLE tblStudentGrade
(
    StudentID int NOT NULL,
    GradeID int NOT NULL,
    CONSTRAINT PK_StudentGrade PRIMARY KEY
    (
        StudentID,
        GradeID
    ),
    FOREIGN KEY (StudentID) REFERENCES tblStudents (StudentID),
    FOREIGN KEY (GradeID) REFERENCES tblGrades (GradeID)
)

To find out the what Student got what Grades we can use the junction table and the following query:

SELECT s.StudentName, g.Grade
FROM tblStudentGrade sg
JOIN tblStudents s ON s.StudentID = sg.StudentID
JOIN tblGrades g ON g.GradeID = sg.GradeID

Conclusion : From the above we can decipher that In a conceptual model for a university, M:M relationship exists between Grade and Student entities

Hope this helps

--
Thanks & Regards,
RNA Team

Syedmasroorali, if this helps please login to Mark As Answer. | Alert Moderator