0

I have entities "Student" and "Course" and they are connected with relationship many-to-many called "Exam". The relationship "Exam" has an atribute "Grade". For this ER model, I have made a relational schema:

Student(StudentID, StudentName, DoB)
Course(CourseID, CourseName)
Exam(StudentID, CourseID, Grade)

Can someone explain how would you put in a relational schema the fact that a student can't get multiple grades for a single course? For example, we can't have records like (Student1, Course1, Grade1) and (Student1, Course1, Grade2).

1
  • Hi, and welcome to the forum! See here for a schema which might be applicable to your requirements - if not, let me know and I'll put together one that is. Student_1, Course_1 - Christmas exam and Summer exam? Many/Most colleges allow students to repeat for a variety of reasons so having two grades for the one course mightn't be that unusual - however YMMV... Mar 27 '21 at 20:00
1

In a relational database every table should have a Primary Key.

A Primary Key can be one column, like StudentId, or it can be a Compound Key, like (StudentID, CourseID), which is exactly what you should use for Exam.

Then Exam's Primary Key guarantees that "a student can't get multiple grades for a single course", because that would require multiple rows with the same (StudentID, CourseID) values.

1

You would create a unique constraint on exam (StudentId, CourseId) this would usually be achieved with a unique index on that table that covered the two columns.

Here's a quick demo in MySql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.