Database Design – Represent Entity Types in ERD as Relations with Attribute Names

database-designdatabase-diagramserdrelational-theoryschema

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

ERD

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?

Best Answer

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 IDEF1X1 diagram shown in Figure 1:

Figure 1 - Courses Association IDEF1X Diagram

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.

Related Question