Database Management System [Resolved]

Posted by Syedmasroorali under Management on 12/13/2015 | Points: 10 | Views : 789 | Status : [Member] | Replies : 1
In a conceptual model for a university, what type of relationship exists between Grade and Student entities?
? 1:1 ? 1:M ? M:M ? Ternary




Responses

Posted by: Rajnilari2015 on: 12/13/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

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

Login to post response