Member Avatar for yugoza

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
  • 110 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by dickersonka
Member Avatar for dickersonka

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.