by shinzou
Last Updated May 03, 2018 15:06 PM

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?

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^{1} 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.}

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.

- ServerfaultXchanger
- SuperuserXchanger
- UbuntuXchanger
- WebappsXchanger
- WebmastersXchanger
- ProgrammersXchanger
- DbaXchanger
- DrupalXchanger
- WordpressXchanger
- MagentoXchanger
- JoomlaXchanger
- AndroidXchanger
- AppleXchanger
- GameXchanger
- GamingXchanger
- BlenderXchanger
- UxXchanger
- CookingXchanger
- PhotoXchanger
- StatsXchanger
- MathXchanger
- DiyXchanger
- GisXchanger
- TexXchanger
- MetaXchanger
- ElectronicsXchanger
- StackoverflowXchanger
- BitcoinXchanger
- EthereumXcanger