0

Hi all,

I would appreciate any help on the following problem:

I have a one-to-one-to-one ternary relationship where Supervisor “chooses” Student for Project

A supervisor chooses one student for one project (This is going to be the case when there are many students applying for the same project with a particular supervisor. Then, a supervisor can express their preference over students and choose one or the project. Note that he can still choose another student for a different project if he has proposed more than one project.

So, this is the relational schema for the relationship:
Chooses_Student_schema = { user_ID*: INTEGER, project_ID*: INTEGER, user_ID*: INTEGER }

But, the question is - is this how you map it to an SQL table?

CREATE TABLE choosesStudent (
user_ID AS “student_ID” INTEGER NOT NULL AUTO_INCREMENT, 
project_ID INTEGER NOT NULL AUTO_INCREMENT, 
user_ID AS “supervisor_ID” INTEGER NOT NULL AUTO_INCREMENT, 
PRIMARY KEY (student_ID, project_ID), 
FOREIGN KEY (student_ID) REFERENCES student,
FOREIGN KEY (project_ID) REFERENCES project,
FOREIGN KEY (supervisor_ID) REFERENCES [B]supervisor OR coordinator[/B],
UNIQUE (student_ID, supervisor_ID),
UNIQUE (project_ID, supervisor_ID));

I have another problem with the bit in bold in the above table: can I reference two tables there? The coordinator entity is a subclass of the supervisor entity.

Many thanks!!

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by dickersonka
0

do supervisors and coordinators exist in different tables?

you can have a fk to two different tables

if they are users, just add a user_type_id to the users table and create a userstype table

with entries of
student
supervisor
coordinator

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.