How can I represent the entity types depicted in an ERD as relations with attribute names?

Question

I have created the entity-relationship diagram (ERD) shown below (for brevity, each individual bubble-shaped box portrays multiple attributes):

Considering such an ERD, I have to convert each entity type or associative entity type depicted there as a relation with its corresponding attribute names, but I run into some problems.

I usually represent a relation as follows:

• X = (a, b, c)^*

_{* Where X is the name of the relation and a, b and c are the names of its attributes.}

Then, in the business domain that I am working on, a Course depends on zero-to-many (0:M) other Courses (as portrayed in the ERD). This means that a Course should be connected somehow to the Courses it is dependent on.

So, how can I represent this situation as a (mathematical) relation?

Option 1

I have tried the following:

• Course = (course_number, course_name, course_dependencies?)

Option 2

I think it is possible to depict the desired aspect in another way, e.g.:

• Depends = (course_name)

and then

• Course = (course_number, course_name, depends)

Option 3

But I thought about, maybe, arranging the elements as follows:

• Course = (course_number, course_name)

• Depends = (course_name_depender, course_name_dependee)

In this way, each pair of attribute names in the relation called Depends represents the association (or relationship) between two occurrences of the relation entitled Course.

Questions

Which of these options is the right way to do it? Perhaps there is a better approach?

Answer 1

Considering the Course entity type that you describe, I have made the following assumptions:

• A Course is primarily identified by its CourseNumber
• A Course is alternately identified by its Name
• A Course is registered at a CreatedDateTime

So I reformulated the situation as follows:

• A Course is the dependent in zero-one-or-many CourseDependences
• A Course is the determinant in zero-one-or-many CourseDependences

Then, I supposed that:

• A CourseDependence is primarily identified by its DependentCourseNumber and DeterminantCourseNumber
• A CourseDependence is registered at a CreatedDateTime

This means, yes, that there is a conceptual-level many-to-many (M:N) association (or relationship) that may involve distinct instances of the entity type called Course. Such an association implies the existence of an associative entity type that, as noted, I denominated CourseDependence.

Consequently, from the set of formulations described above, I have derived the IDEF1X¹ diagram shown in Figure 1:

In this way, employing the notation that you use to express relations along with their corresponding attribute names, I would represent (a) the Course entity type and (b) the CourseDependence associative entity type as two distinct relations in the following way:

• _{Course = (CourseNumber, Name, CreatedDateTime)}

• _{CourseDependence = (DependentCourseNumber, DeterminantCourseNumber, CreatedDateTime)}

Therefore, the Option 3 exposed in your question would be very close to an accurate possibility to express the relevant aspects.

---

Endnote

_{1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical work authored by the sole originator of the Relational Model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.}

Answer 2

In general, you need predicates (relations) and constraints.

Start with a predicate, ..

P1 Course named (C_NAME), assigned a course number (C_NUM), exists.

.. and constraints

c1.1 For each course name, exactly one course has that name.

c1.2 For each course number, exactly one course is assigned that number.

The predicate leads to relation; the constraints to -- well, constraints like: PK, AK, FK, CHECK etc.

Course {C_NUM,C_NAME} -- P1

   KEY {C_NUM}        -- c1.1
   KEY {C_NAME}       -- c1.2

P2 Course number (PRE_NUM) is prerequisite for course (C_NUM).

c2.1 For each prerequisite course that course may be prerequisite for more than one course.

c2.2 For each course, that course may have more than one prerequisite course.

c2.3 A course can not be prerequisite to itself.

c2.4 If a course has a prerequisite then that course must exist.

c2.5 If a course is a prerequisite then that course must exist.

Prerequisite {C_NUM, PRE_NUM}   -- P2

         KEY {C_NUM, PRE_NUM}   -- c2.1 & c2.2

       CHECK (C_NUM != PRE_NUM) -- c2.3

FOREIGN KEY (C_NUM)   REFERENCES Course (C_NUM)  -- c2.4
FOREIGN KEY (PRE_NUM) REFERENCES Course (C_NUM)  -- c2.5

And so on.

Hope this is what you are looking for.

---

Note: I am using RDBMS wording for constraints, in general KEY (PK, AK) is an internal uniqueness constraint, foreign key is a subset constraint (inclusion), and CHECK is an internal value-comparison constraint.

---

And here is how it looks in an ERD; note that comments (grey) are not necessary, used only to illustrate the method. You may also note that it is not easy to express all the constraints here -- if comments are removed the [c2.3] will not be obvious.