Relational Theory – Unique Relationship Attribute for Every Pair of Entities

relational-theoryunique-constraint

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

Best Answer

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.

Related Solutions

Database Design – Managing Many One-to-Many Relationships Between Two Tables

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).

Database Relationships – Table Without Unique or Primary Key

Using surrogate IDs is one way but you don't really have to. If you want to keep your current design - which I suppose has Work (workName) as primary key and SubWork (workName, subWorkName) as a potential primary or unique key, you can define the Activity table like this (I also changed slightly the column names):

Work
--------
workName      PK


SubWork
--------
workName      PK ,  FK -> Work (workName)
subWorkName   PK


Activity 
-------- 
activityID    PK       -- or what other column you have as primary key
workName        FK1
subWorkName     FK1  
  FK1 (workName, subWorkName) -> SubWork (workName, subWorkName)

As @raumkrieger points out, to define a foreign key constraint you have to reference a unique or primary key column (or columns.) Since if we assume correctly that (workName, subWorkName) unique identifies a row in the SubWork table, you can use this combination to be referenced by the foreign key.

Note: I do not suggest that using long varchar columns is always better than using narrow surrogate integer columns. Especially if the tables are big (or plan to be) and/or the varchar columns are very wide, this will not be the most efficient design in most DBMS. But for narrow varchar columns and/or smaller tables, the difference in performance will be negligible.

Best Answer

Related Solutions

Database Design – Managing Many One-to-Many Relationships Between Two Tables

Database Relationships – Table Without Unique or Primary Key

Related Question