Database Design

Help with mapping ternary relationships (URGENT)

Please support our Database Design advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Oct 2008

Posts: 4

Reputation: yugoza is an unknown quantity at this point

Solved Threads: 0

yugoza

Offline

Newbie Poster

Help with mapping ternary relationships (URGENT)

Nov 11th, 2008

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?

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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));
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!!

Last edited by peter_budo; Nov 12th, 2008 at 6:08 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

•

Join Date: Aug 2008

Posts: 1,162

Reputation: dickersonka will become famous soon enough

Solved Threads: 137

dickersonka dickersonka is offline

Offline

Veteran Poster

Re: Help with mapping ternary relationships (URGENT)

Nov 11th, 2008

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

Custom Application & Software Development
www.houseshark.net