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!!
