1

Hi there I am beginning to try out with SQL and database design. I understand the SQL side of things, but trying to draw out diagrams is a bit confusing.

Consider these business rules:

  • A trainee has a name, trainee ID, and an email.
  • A Trainee at most belongs 1 trainee project at a time
  • Over time, Trainees may take more than one project.
  • Each project has a name and a project code. A program is run by a single section. A section may run more than one project.
  • A Project can have many trainees

If a Trainee can only do 1 project at a time, yet the business rule says over time, they may take more than one project. How would previous projects get retained?

Can somebody show me with multiplicities?

EDIT: Sorry I thought it added my screenshot : Test

Christophe
  • 68,716
  • 7
  • 72
  • 138
Aniotazero
  • 43
  • 4
  • 1
    Sorry I thought the text down the bottom was spam so I deleted it, it's my first time here. It should be there now. – Aniotazero Oct 30 '20 at 08:22

3 Answers3

1

A Trainee at most belongs 1 trainee project at a time

That can be shown through a constraint saying for a given trainee the dates of the projects never overlaps.

In OCL the constraint can be written :

context Trainee inv:
 self.ProjectHistory->forAll(h1,h2 |
                             h1<>h2 implies (h1.dateCompleted < h2.dateStarted) or
                                            (h1.dateStarted > h2.dateCompleted))

also having :

context ProjectHistory inv:
   self.dateStarted <= self.dateCompleted

supposing dateCompleted is set with the current date while the project is on going, and we know the project is on going through an other way.

If dateCompleted values 0 while a project is on going :

context ProjectHistory inv:
  (self.dateStarted > 0) and 
  ((self.dateCompleted = 0) or (self.dateStarted <= self.dateCompleted))

context Trainee inv:
  self.ProjectHistory->select(dateCompleted = 0)->size() <= 1

context Trainee inv:
  self.ProjectHistory
    ->forAll(h1,h2 | 
             h1<>h2 implies 
               if h1.dateCompleted = 0 then
                 h1.dateStarted > h2.dateCompleted
               else 
                 if h2.dateCompleted = 0 then
                   h2.dateStarted > h1.dateCompleted
                 else
                   (h1.dateCompleted < h2.dateStarted) or 
                   (h1.dateStarted > h2.dateCompleted)
                 endif
               endif
             )

Out of that to use an association-class is a right way.

Over time, Trainees may take more than one project.

so any number, the multiplicity 0..1 must be 0..* or the shortcut *.

The multiply must be 0..1 if that rule and the previous are replaced by (over time) a Trainee at most belongs 1 trainee project

A section may run more than one project.

so any number, the multiplicity 1..* must be 0..* or the shortcut *

A Project can have may trainees

may is visibly many so the high number is not 1.

Just having a project have many trainees probably implies the minimum number is ,1 but with can have the minimum number is 0 rather than 1.

So finally the multiplicity is 0..* or the shortcut *, rather than 1..1

How would previous projects get retained?

they are thanks to the multiplicity 0..* rather than 0..1, the trainee does not have a relation to only the ongoing project if exist

To summarize :

enter image description here

Example of object diagram :

enter image description here

you can check all the rules are followed.

In several case the name of your attributes contains the name of the class, for instance projectName and traineeId, this is useless and I recommend you to remove the name of the class in the name of the attributes.

bruno
  • 32,421
  • 7
  • 25
  • 37
  • 1
    Ah I see, constraints can show that only one time they can be involved, I never thought of that. I was trying to show it in the conceptual phase, rather than the implementation! – Aniotazero Oct 30 '20 at 09:50
  • @qwerty_so your project for the next hour is to write the constraint in OCL ^^ – bruno Oct 30 '20 at 14:19
  • 1
    No, no. I can hardly read it. That's your area. But this way it's pretty fine. – qwerty_so Oct 30 '20 at 14:59
  • @qwerty_so I put an other definition using 3 constraints in case an on going project as its *dateCompleted* valuing 0 – bruno Oct 30 '20 at 16:29
1

The term "project" is ambiguous. The standard meaning is that a project consists of workpackages with various activities/tasks performed by various project participants. Such a project, of course, has a start and an end time.

Another, secondary, meaning of "project" would be in education, similar to an assignment that is defined as a task type and can be started/done by any student at any time.

My model refers to the first (more common) notion.

For expressing the constraint that "A Trainee at most belongs 1 trainee project at a time" has to be expressed in the form of an invariant (box) attached to the Trainee class like so:

enter image description here

Gerd Wagner
  • 5,481
  • 1
  • 22
  • 41
  • I disagree with you, there is no rule saying the trainees have to take a same project at the same time => the dates cannot be part of the project – bruno Oct 30 '20 at 13:48
  • (your DV is not from me anyway) – bruno Oct 30 '20 at 13:57
  • I think that constraint needs to be attached to the Assoc.Class rather than `Trainee`. – qwerty_so Oct 30 '20 at 14:08
  • With the dates in `Project` you can't have a history! So I think this way your answer is wrong. – qwerty_so Oct 30 '20 at 14:09
  • @qwerty_so The term "project" is ambiguous. The standard meaning is that a project consists of workpackages with various activities/tasks performed by various project participants. Such a project, of course, has a start and an end time. Another, secondary, meaning of "project" would be in education, similar to an assignment that is defined as a task type and can be started/done by any student at any time. My model refers to the first (more common) notion. – Gerd Wagner Oct 30 '20 at 16:58
  • Ah - was writing what you now stated in your answer (saw the edit too late). This way it's fine then. (NB I don't know which definition is the more common one....) – qwerty_so Oct 30 '20 at 21:32
-1

A solution without ocl describing the desired semantics visually:

domain class diagram

Each Trainee-Project Association has a start date, but only Completed Trainee-Project Associations have an end date. This is straight forward and would be the way we would express it in every day English. So why not do it in a class diagram? It then is easy to express, that each Trainee can have at most one current project. By making its ends derived we can say, that the type of the association defines, where the ends are shown - in current project or completed projects.

The link must change its AssociationClass over time from current Trainee-Project to completed Trainee-Project. If this sounds strange to you, you probably think in terms of programming languages. Most of them don't support dynamic reclassification. But in fact this is the reality. The Trainee will one day become an employee. The classes they are a-changin', as a great poet once put it (as far as I remember ;-)

You might say, SQL does not support this. Well, yes, but neither does it support n:m relationships. So you need to map your domain model to SQL anyway. Therefore, make your domain model as close to your needs as possible, and think about the mapping to SQL later.

Axel Scheithauer
  • 2,758
  • 5
  • 13