Determining Cardinality in a Logical Model

Question

How should one determine Cardinality in a Logical Model ?

Should it be based on how the rows of an entity relate to another entity or should we consider the natural relationship between the entities i.e. conceptual relationship between the entities ?

Example: If I have an entity Course and an entity Course Type, what would be the cardinality ? Each course can have only one course type. For example, Bachelor of Arts is a course of course type Bachelors and Master of Science is of course type Masters

If I have Course Type as part of Course entity, then Course Type would only contain list of valid course types and it would be "many-to-one" (non-identifying) as there are many courses which will could 1 course type.

On the other hand if I model it in such a way that Course Type entity has Course ID (foreign Key) and Course Type , then the relationships between Course and Course Type is "one-to-one" (identifying).

Basically what I am trying to understand is, which one the following is right ?

each course has one course type OR many courses have one course type

How should one make this decision ? Are there any guidelines ?

P.S. : I am a beginner and using Oracle Data Modeler

Answer 1

It should be based on logic. You should be able to express constraints in natural language. ERDs are great only if you already grok how to automatically verbalize diagrams. If not yet, then use plain text editor, it is better and more powerful than ERD.

For this example, starting with two predicates

1. Course type TYP exists. ctype {TYP}
2. Course CRS exists. course {CRS}

here are a few options based on constraints:

---

For each course, that course is of exactly one course type;
for each course type, more than one course may be of that type.

ctype {TYP}
   PK {TYP}


course {CRS, TYP}
    PK {CRS}
    FK {TYP} REFERENCES ctype {TYP}

---

For each course, that course may be of more than one course type;
for each course type, exactly one course is of that type.

ctype {TYP, CRS}
   PK {TYP}
   FK {CRS} REFERENCES course {CRS}


course {CRS}
    PK {CRS}

---

For each course, that course is of exactly one course type;
for each course type, exactly one course is of that type.

course {CRS, TYP}
    PK {CRS}
    AK {TYP}

---

For each course, that course is of at most one course type;
for each course type, more than one course may be of that type.

ctype {TYP}
   PK {TYP}


course {CRS}
    PK {CRS}


course_ctype {CRS, TYP}
          PK {CRS}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

---

For each course, that course may be of more than one course type;
for each course type, more than one course may be of that type.

ctype {TYP}
   PK {TYP}


course {CRS}
    PK {CRS}


course_ctype {CRS, TYP}
          PK {CRS, TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

---

For each course, that course may be of more than one course type;
for each course type, at most one course is of that type.

ctype {TYP}
   PK {TYP}


course {CRS}
    PK {CRS}


course_ctype {CRS, TYP}
          PK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

---

For each course, that course is of at most one course type;
for each course type, at most one course is of that type.

ctype {TYP}
   PK {TYP}


course {CRS}
    PK {CRS}


course_ctype {CRS, TYP}
          PK {CRS}
          AK {TYP}

         FK1 {CRS} REFERENCES course {CRS}
         FK2 {TYP} REFERENCES ctype  {TYP}

---

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
FK = Foreign Key

In general, the constraint is verbalized as:

For each A, that A {is | may be} of {exactly one | more than one | at most one} B;
for each B, {exactly one | more than one | at most one} A {is | may be} of that B.

Term                        Range
---------------------------------
is of     exactly one       [1]
is of     at most one       [0,1]
may be of more than one     [0,*]