0

I'm currently working on a data model for "Project Allocation System" and I have done most of the ER-to-SQL transformations, but I'm struggling to deal with the two ternary relationships that exist in my model.
So far, I have the following:

1 Mapping entities and their attributes

Module_schema = (module_ID*: INTEGER, module_code: VARCHAR(10), module_title:
VARCHAR(60))

Project_schema = (project_ID*: INTEGER, project_title: VARCHAR(60), project_specification: VARCHAR(MAX), project_difficulty: VARCHAR(15), user_ID*: INTEGER )

1.1 Multivalued attributes

ProjectArea_schema = (project_ID*: INTEGER, project_area: VARCHAR(60)*)

StudentPreferences_schema = (user_ID*: INTEGER, student_preference: VARCHAR(10)*)

ResearchInterests_schema = (user_ID*: INTEGER, supervisor_research_interest: VARCHAR(50)*)

3 Mapping generalization hierarchies

Student_schema = (user_ID*: INTEGER, user_username: VARCHAR(10), user_password:
VARCHAR(10), user_f_name: VARCHAR(20), user_l_name: VARCHAR(20), user_email: VARCHAR(20), student_degree: VARCHAR(20), project_ID: INTEGER*)

Supervisor_schema = (user_ID*: INTEGER, user_username: VARCHAR(10), user_password: VARCHAR(10), user_f_name: VARCHAR(20), user_l_name: VARCHAR(20), user_email: VARCHAR(20))

Coordinator_schema = (user_ID*: INTEGER, user_username: VARCHAR(10), user_password: VARCHAR(10), user_f_name: VARCHAR(20), user_l_name: VARCHAR(20), user_email: VARCHAR(20))


2 Mapping relationships

Unary relationships
Project – Student
One project is assigned to one student. One student is assigned to work on one project.
To map this relationship, project_ID is included in the Student table as a foreign key.

Binary relationships
One-to-many
Project – User
Every project is proposed by one user. A user can propose one or more projects.
User_ID will be stored in the “many side”, in the Project table as a foreign key.

Many-to-many
Module – Project
Every project can have zero or many module prerequisites. A module can be a prerequisite for zero or many projects.
Prerequisites_schema = (project_ID*: INTEGER, module_ID*: INTEGER)

Module – Student
Every student takes one or more modules. Every module is taken by one or more students.
Student_Module_schema = (user_ID*: INTEGER, module_ID*: INTEGER) // CFK


Ternary relationships

>>>>>OK, so here is where my confusion comes in:

In the first ternary relationship, I'm trying to express that every supervisor chooses a student for a particular project. This is important when there are more than one student who expressed an interest in a project proposed by a particular supervisor. In that case, the supervisor chooses one student according to their preference.

Entities involved: Student – Project – Supervisor
Cardinality: one-to-one-to-one

The second ternary relationship should express that a coordinator allocates one project to every student.

Entities: Student – Project – Coordinator
Cardinality: one-to-one-to-one

Did I get the cardinalities right? Also, I'm not sure how to go about mapping these relationships to relational schema and SQL tables.

One more concern: i read that ternary relationships should be eliminated if they could be decomposed into two or three equivalent binary relationships in order to achieve simplicity and semantic purity. Do you think it would be possible to adjust my design so that it would only use binary relationships?

I'll be very grateful for all your comments and advice.

I'm also attaching a picture of the ER model - any comments on that will be appreciated, too!

Many thanks!!

Attachments ERdiagram_PAS_copy.png 87.66 KB
3
Contributors
2
Replies
5
Views
9 Years
Discussion Span
Last Post by robertmacedonia
0

Hi Yugoza, I know this is fairly old post but i am stuck with the same ternary relationship problem.
Were you able to map the ternary relationship into sql tables?
If yes, please provide me the details.
Thanks

0

Hi Yugoza, I know this is fairly old post but i am stuck with the same ternary relationship problem.
Were you able to map the ternary relationship into sql tables?
If yes, please provide me the details.
Thanks

Hi, I do not know the exact design of your database, but the general rule about mapping ternary relationships is to create a new table about the relationship. The table's PK would be a composite one, composed of the three PKs of the involved entities. The table should contain the relationship's attributes (if there are ones) and should NOT contain any other of the entities' attributes but only their PK.

Hope this will help you.

Regards

Edited by robertmacedonia: n/a

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.