Criteria for considering redundant relationships in entity - relationship diagram?

[Jaime Alvarez Benayas]

Hello.

I am having problems understanding what criteria are used to determine relationship redundancy for a "circle" of relationships between 3 entities in the entity - relationship model.

I am adding the link to my stackoverflow question copied here:
https://stackoverflow.com/questions/66613891/criteria-for-considering-redundant-relationships-in-entity-relationship-diagra

I was starting with the following example: Course - department - teacher relationships.

https://i.stack.imgur.com/pMpnr.png

My criteria to declare a relationship (A -> C) as non redundant (can NOT be inferred from A -> B -> C) is either:

1) A -> B has minimum cardinality 0 (ie. 1 element of A can be associated with 0 elements of B), since then, B can't be determined (and therefore C can't be determined). OR
2) If A -> B and B -> C have a maximum cardinality of N (ie. 1 element of A can be associated with N elements of B and 1 element of B can be associated with N elements of C simultaneously). My assumption for this is that knowing the elements of B associated with A and knowing the elements of C associated with B can't be used to know one specific A -> C.
I consider the above "both ways": A -> B -> C and C -> B -> A. If going any way I get any of the above, I consider the relationship is NOT redundant since A -> C can't be inferred from A -> B -> C. I am assuming this, regardless of the cardinality between the candidate redundant relationship A -> C.

Given my assumptions, I consider the example [Course (C) - department (D) - teacher (T)]. I deduce that the only relationship which is redundant is between course and teacher since:

T -> C: T -> (1,1) D -> (1,n) C [Cardinality shown in parenthesis]. Which allows to deduce T -> C from T -> D -> C.

C -> T: C -> (1,1) D -> (1,n) T. Which allows to deduce C -> T from C -> D -> T.

However, in the example given, the redundant relationship is Teacher and Department. The reasoning is that if the courses a teacher teaches are known and the department to which each course belongs is also known, it can be deduced to what department belongs each teacher. Also, given a department, if we know its courses, and we know the teachers who teach the courses we will know the teachers associated with department.

With my criteria, I don't see how this is the case, since: D -> T: D -> (1,n) C -> T (1,n). This complies with point 2) above and therefore D -> T can't be deduced from D -> C -> T.

There is also another example with the same relationships but different cardinality (shown in red), this is where I got my criteria from:

https://i.stack.imgur.com/WcGn0.png

In this case, it is specified that there are no redundant relationships, for the following reasons:

T - D:
If we know the courses taught by a teacher, and the departments to which the courses are assigned, Do we know which department the professor belongs to? NO, since the course can be assigned to several departments. This is where I deduced my criteria: T -> D: T -> (0,n) C -> (1,n) D. We have two 1->N relationships, therefore it is non-redundant.

Also it says: what if a course was only attached to a Department? Still, a teacher may not teach any courses, then we couldn't know his/her department. Which is my other criteria:

T -> (0,n) C [minimum cardinality of 0, we can't know C in some cases, and therefore, we can't know D.

It also adds: given a department, if we know its courses, and we know the teachers who teach the courses, we will know the professors associated with the department.

1) T - C: A course can be assigned to several departments, and these may have several teachers, then you can not know the teacher concrete that the course teaches. Ie. We have two 1-N relationships: C -> (1,n) D -> (1,n) T. Therefore, it is non-redundant.

2) C - D: Given a course taught by a teacher, and he/she belonging to a department, we cannot know what other departments have associated the course. I am assuming: D -> (1,n) P -> (0,n) C. We have two 1-N relationships therefore, it is non-redundant.

Finally I have a third example with Author (A), Editorial (E) and Book (B) as follows:


https://i.stack.imgur.com/Ppj1h.png


I am told that the redundant relationship is Author - Editorial. However, I am finding no redundant relationships as per my criteria since they have two 1:N relationships:

E -> A: E -> (1,n) B -> (1,n) A

B -> E: B -> (1,n) A -> (1,n) E

A -> B: A -> (1,n) E -> (1,n) B.

Thank you for taking the time to read until here. Are my criteria wrong? If so, what criteria should I use to consider a relationship redundant?

[Derek Ignatius Asirvadem]

> On Sunday, 14 March 2021 at 00:14:06 UTC+11, jaime.alva...@gmail.com wrote:
>
> I am having problems understanding what criteria are used to determine relationship redundancy for a "circle" of relationships between 3 entities in the entity - relationship model.
>

> Are my criteria wrong? If so, what criteria should I use to consider a relationship redundant?

1. ERD diagrams are ancient (pre-relational) and obsolete, totally useless in Relational Data Modelling. The notion of "modelling" rows and their relations to other rows without understanding (a) what the entity actually is, and (b) what the relation actually is, is pure insanity.
[a] One has to understand the entity, and to do that, one has to understand the Identity of the row, ie. the Key. Specifically the Relational Key, which informs you re *what* the thing is.
[b] After understanding the entity, one can understand the relations between them.
ERD does not, and cannot, provide any of that.

The Standard for Relational Data Modelling since 1983 is IDEF1X.
https://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Introduction.pdf

Put another way, if the freaks used ERD for non-Relational data modelling, there would be no problem. But for Relational data modelling, ERD is brain-dead and obsolete. Which is why they promote it, it is consistent with promoting RecordIds on every file, ala pre-Relational technology.

2. Note that circular references (as distinct from doagrams that happen to have a "circle" in them) are prohibited in the Relational Model. In terms of teaching, it is pretty stupid for a professor to teach a "circle" of related entities, and then try to remove the "redundant" relations. In order to erect that stupidity, one has to accept the falsity that all entities are somehow "equal". They aren't. Some entities are Independent, they must exist before the Dependent entities can exist, etc.

Further, trying to resolve relations between entities that are *unknown*, by virtue of the fact that not even their *identity* is known, is plain absurd. One may as well name the entities as A; B; C. It is mental masturbation, passed off as "theory", no doubt to elevate the "theory".

It is as stupid as the shell game the "theoreticians" market, for determining the unique Key. Why not just model the real relations in the first place.

The real purpose of the silly example is to get you to accept "circles" of relations, so that when they impose and teach the illegal circular reference as "relational", you will not puke, you will accept it.

3. In the third example, the ERD is invalid, it has arrows, which are foreign to an ERD. Note that the person is trying to overcome the crippling limitations of the ERD, but he has not learned about the Standard for Relational Data Modelling since 1983. Again, very brain-dead and ignorant of IT.

4. More on "redundancy". The Fact that a Person is a Teacher, is something that needs to be established, as a Fact. It doesn't matter if the Teacher can be determined via the Course, or via the Department, or via his shoelaces.

----

"Relational Model"
You might be referring to the pig poop that is marketed as "relational" by the subverters (Date; Darwen; Fagin; and all the professors that follow them without referring to the actual Relational Model. Sorry, I can't help you there: they are propagating insanity, of which the problems are endless, and the solutions are never complete. Your question is a perfect example. It cannot occur (a) in the real world and (b) in the Relational Model, that articulates the real world. First they teach you insanity, and then they ask you to solve the insanity using their insane methods.

The real world (not the perceived world) is sane.

If your problem is from a textbook, the writers of that textbook are in that class of subverters, and the textbook is not a textbook, it is pig poop, filth that is fed to eager young minds as "science". Go and read the Relational Model by Dr E F Codd, and use IDEF1X to model data Relationally.
https://www.softwaregems.com.au/Documents/Article/Database/Relational%20Model/Codd%20E%20F/A%20Relational%20Model%20of%20Data%20for%20Large%20Shared%20Data%20Banks.pdf
----

> I am having problems understanding what criteria are used to determine relationship redundancy for a "circle" of relationships between 3 entities in the entity - relationship model.

The "circle" is contrived, irrelevant (except for the fraud as identified above).

In the context of the modelling data in the Relational paradigm, all the above issues are easy to resolve, if an IDEF1X data model is erected (instead of using pre-Relational methods). Of course it is Semantic (Logic), thus all your questions are answered logically (as well as visually). The modelled entities are required, there are no redundant relations. We don't need to play a stupid shell game, draw every possible relation, then identify the redundant ones, then remove the redundant ones ... we just model the data Relationally.

It looks like this:
https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/comp_databases_theory/Alva%20Jaime%20DM.pdf

Cheers
Derek

[Nicola]

On 2021-03-13, Jaime Alvarez Benayas <jaime.alva...@gmail.com> wrote:
> Hello.
>
> I am having problems understanding what criteria are used to determine
> relationship redundancy for a "circle" of relationships between
> 3 entities in the entity - relationship model.
>
> I am adding the link to my stackoverflow question copied here:
> https://stackoverflow.com/questions/66613891/criteria-for-considering-redundant-relationships-in-entity-relationship-diagra
>
> I was starting with the following example: Course - department
> - teacher relationships.
>
> https://i.stack.imgur.com/pMpnr.png
>
> My criteria to declare a relationship (A -> C) as non redundant (can
> NOT be inferred from A -> B -> C) is either:

There is no way you can determine whether a relationship is redundant
without knowing the *semantics* of the relationship. In general, you
cannot do that based only on cardinality constraints.

In your example, the names of the relationships are omitted. Let's say,
for the purpose of my arguments below, that they are as follows:

- each teacher TEACHES one or more courses (each course IS TAUGHT by one
ore more teachers);
- each teacher WORKS IN one department (each department EMPLOYS one or
more teachers);
- each course IS ORGANIZED BY one department (each department ORGANIZES
one or more course).

None of the above assertions is redundant (in the sense that an
assertion logically implies another), without further information.

Note that the "circularity" in that diagram is only apparent (ERD are
misleading). In fact, if you turn that diagram into a relational schema,
the result is a directed *acyclic* graph (where you take the relation
schemas as nodes and the foreign keys determine the directed edges).
Approximately:

+------------+
+--------| Department |---------+
| +------------+ |
| |
O O
+------------+ +------------+
| Course | | Teacher |
+------------+ +------------+
| |
| |
| |
| +------------+ |
+-------O| T-C |O--------+
+------------+

Now, the question here is rather whether there are some *path
restrictions*, that is, restrictions on how instances of an ancestor
entity (Department) are related to instances of the common child T-C:
must a teacher teach in courses organized by the teacher's department?
In courses that are *not* organized by the teacher's department? Any
courses? Addressing this type of question does not change the diagram
above, though; but it influences how foreign keys migrate from top to
bottom.

> Given my assumptions, I consider the example [Course (C) - department
> (D) - teacher (T)]. I deduce that the only relationship which is
> redundant is between course and teacher since:

Which is wrong. How would you know who teaches what without that?

> However, in the example given, the redundant relationship is Teacher
> and Department.

? Is this an answer given to you by someone?

> The reasoning is that if the courses a teacher teaches
> are known and the department to which each course belongs is also
> known, it can be deduced to what department belongs each teacher.

Why? Who said that a teacher teaches only courses in her own department?
And even if that were the case, the relationship between Department and
Teacher would *not* be redundant, because it represents a different
fact. What if next year Prof. John Doe goes on sabbatical? Shall we
forget that he belongs to a department because he doesn't teach that
year?

In your model, each teacher must always be assigned to at least one
course. With

(a) such an (unrealistic) constraint, *and*
(b) with the additional constraint that teachers work only in their own
department,

it is true that you can infer the department of a teacher by looking at
the courses taught by that teacher. But still, you need the
Teacher-Department relationship for at least two reasons: first, it is
semantically an important and distinct relationship in the application
domain (ask yourself: does a teacher belongs to a department because he
teaches a course in that department, or does he teach certain courses as
a consequence of belonging to a given department? This is a point also
Derek makes in a sibling post: what comes first? What depends on what?
Meaning is king); and second, it allows you to easily enforce (b) (via
a common ancestor referential constraint).

> Also, given a department, if we know its courses, and we know the
> teachers who teach the courses we will know the teachers associated
> with department.

Same comment as above applies.

I have not time now to review your other examples, but I hope that the
above will help.

Nicola

[Nicola Vitacolonna]

On 2021-03-13, Jaime Alvarez Benayas <jaime.alva...@gmail.com> wrote:

> There is also another example with the same relationships but
> different cardinality (shown in red), this is where I got my criteria
> from:
>
> https://i.stack.imgur.com/WcGn0.png
>
> In this case, it is specified that there are no redundant
> relationships, for the following reasons:

Your schema looks as follows, in "ASCII-IDEF1X" style:

+------------+
| Department |------------------------------------------+
+------------+ |

| |
| |
| |
| O
| +------------+ +------------+
| | Course | | Teacher |
| +------------+ +------------+
| | | |
| | | |

| +------------+ | | +------------+ |
+---O| T-C |O---+ +---O| C-D |O ---+
+------------+ +------------+

If it is not clear, ----O denotes a one-to-many relationship (the
O ending is the "many" side). You may think of each rectangle as
a relation schema, and each edge as the depiction of a foreign key from
the lower to the upper schema.

I have not made all the cardinalities explicit in the diagram, but that
does not matter for my point below. The labels on the relationships are
missing as well, as they are missing in your ERD—but, again, this not
strictly relevant to my point.

It is obvious from the diagram above that (a) nothing is redundant (wrt
to the limited information provided), *no matter how you fill in the
missing cardinalities*, and (b) the "circularity" in your ERD is an
artifact (the diagram above, seen as a graph, is a DAG). Besides,
compared to your first example, here there is not even a "common
ancestor" situation: for every pair of entities A and B, there is only
one path from A to B (following the edges in the direction from
- to O, or equivalently, from top to bottom). For comparison, in your
first example, you could reach T-C from Department either through Course
or through Teacher, which allowed for different possible "path
constraints".

> https://i.stack.imgur.com/Ppj1h.png
>
> I am told that the redundant relationship is Author - Editorial.
> However, I am finding no redundant relationships

Again, in general there is no redundancy, and no cycle either (this time
I have specified all the cardinalities: P means "one or more"):

+----------+ +-----------+
| Author | | Editorial |
+----------+ +-----------+
| | | |
| | P +---------+ P | |
| +----------O| A-E |O-------------+ |
| +---------+ |
| |P
| O
| +-----------+
| | Book |
| +-----------+
| |
| P +---------+ P |
+-------------O| A-B |O---------------+
+---------+

It *may* be that you can dispose of A-E, but that depends (do I need to
repeat myself) to the *meaning* you assign to your entities and
relationships—in this case, in particular, it depends on how instances
of A-E relate to instances of A-B (if they are related at all). But, as
it stands, without knowing more, this schema has nothing that can be
unconditionally removed.

Let's say that both A-E and A-B mean "Contribution", and that the
relationship between Editorial and Book is "reviews/is reviewed in".
*If* the following constraints hold "in the real world":

(1) each time an author A makes a contribution to a book B then A makes
a contribution for the editorial in which B is reviewed, and

(2) each time A makes a contribution to an editorial E then there is
a book reviewed in E to which A has contributed,

then you may remove A-E. So, you need to be a lot more specific than you
have been before you decide that something is "redundant".

Note that constraints (1) and (2) above are not implied by the
cardinalities in the diagram. To make this point clear, here is an
instance that satisfies all the cardinality constraints, but it does not
satisfy (1) or (2):

Author: A1 and A2
Book: B1 and B2
Editorial: E1 and E2
B1 is reviewed in E1
B2 is reviewed in E2
A1 contributes to E1
A1 contributes to B2
A2 contributes to E2
A2 contributes to E1

> Thank you for taking the time to read until here. Are my criteria
> wrong?

Yes.

>If so, what criteria should I use to consider a relationship
> redundant?

Reason about the meaning of what you are modeling, and design
accordingly. Your "design-by-syntax" approach ("remove this because it
satisfies that syntactic condition") is not going to bring you very far.

Nicola

[Derek Ignatius Asirvadem]

Nicola

> On Tuesday, 16 March 2021 at 23:23:28 UTC+11, Nicola wrote:
> > On 2021-03-13, Jaime Alvarez Benayas <jaime.alva...@gmail.com> wrote:

Good explanation, using terms the seeker may be more familiar with.

> Note that the "circularity" in that diagram is only apparent (ERD are
> misleading).

So, why in heavens name, do professors teach ERD ?

Why, since we have had IDEF1X for Relational Data Modelling since 1983 (established and well-known international standard since 1993), do professors suppress that, and instead “teach”; push; shove; market; propagate the broken ERD, which is a pre-Relational artefact ?

Oh, the famous cancer in academia, the not-invented-here syndrome. Imbeciles like Stonebraker and Chen are academics to be revered, even when they are proved wrong. Non-academics and anything they might come up with, without using the glorious inventions of the motherland, are to be suppressed. This is not science, it is communism. The political ideology of the pig poop eaters.

> In fact, if you turn that diagram into a relational schema,
> the result is a directed *acyclic* graph (where you take the relation
> schemas as nodes and the foreign keys determine the directed edges).

To be clear. Yes, for Relational Normalisation, the first step is to Normalise the data into the **Data Hierarchies**, and they must be “trees”, otherwise known as DAGs. But the trees or DAGs or graphs are not exposed; not visible, in a simple exercise such as this. Thus your statement, though correct, may confuse.

The Data Hierarchies are Normalised in the First Wave. Each hierarchy begins with an Independent entity, and flows into many Dependent entities.

>>>>
Now we get to the second article that is absent in ERD, that is a specific Relational requirement, as provided in IDEF1X:
__ Independent vs Dependent entities.
Trying to model data Relationally, without using Relational concepts, is guaranteed to fail. The obsession of “theoreticians”, using tools that are guaranteed to fail. How on earth is this “teaching Relational Data Modelling” ???

Now we get to the third article that is absent in ERD, that is a specific Relational requirement, as provided in IDEF1X:
__ Identifying vs Non-Identifying relations,
__ which is based on the Relational Key.
Both of which are suppressed by academia; the textbooks; the professors.

While I accept that this is a simple exercise, and that not all Relational requirements need to be exposed in each such, the resolution that is allegedly called for; the goal of the exercise, is subverted because the very fundamentals [Relational concepts] that do provide resolution are missing; not taught.

For the exercise, we can:
- Either treat it as a simple classroom one, intended to expose just one aspect, and devoid of Relational concepts, and thus a fraud because it is marketed and sold as “Relational Data Modelling”. That is broken, as per the seeker, FORTY YEARS OUT-OF-DATE, and both you and I are forced to provide the minimal Relational concepts, in order to obtain the resolution to the question.
- Or we can be honest human beings and actually teach Relational concepts under the label of “Relational Data Modelling”. Which is why I provided a Data Model that exposes the basic Relational concepts (Independent/Dependent entities; Relational Keys; Identifying/Non-Identifying relations), and thus a far greater understanding of the elements being juggled around in the exercise.

But the poor students who pay good money for what passes as “science”, as “education” these days, are not taught genuine science, and instead are force-fed outdated and obsolete concepts as “science”, force-fed anti-Relational concepts as “Relational”, and the real Relational Model is suppressed. (Nicola, you stand out as the single academic attempting to cross the great divide, to actually delve into the Relational paradigm.)
<<<<

Ok, I see that you are limiting your evaluation to Entity level (not Key or Attribute level). I will provide that as well.
https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/comp_databases_theory/Alva%20Jaime%20DM%20B.pdf

After the First Wave, after the Data Hierarchies are resolved, as trees; DAGa; graphs, which means no circular references; no Cyclic Graphs, we are ready to commence the Second Wave. Here we have entities that join two Data Hierarchies. Eg. SalesOrder identifies both a Customer (from the Party hierarchy) and a Part (from the Object hierarchy). Technically, these are Binary entities. Whereas in the First Wave, Trees; DAGs, are demanded, in the Second Wave, that constraint does not [cannot] apply, the entities being established will not conform because they join two trees.

In my Data Model (link above), the [First Wave] Data Hierarchies are not very deep, as it is a classroom exercise, but it remains that they must be understood properly:
- Department
--- Course
- Person
- Title
--- Book

And the following are [Second Wave] Binaries:
- Teacher
- CourseTeacher
- Author
- Editorial

> Now, the question here is rather whether there are some *path
> restrictions*, that is, restrictions on how instances of an ancestor
> entity (Department) are related to instances of the common child T-C:

(I would not use the term “common child” or common parent/ancestor”. Just Parent and Child, the precise meaning of which is already difficult to maintain.)

> must a teacher teach in courses organized by the teacher's department?
> In courses that are *not* organized by the teacher's department? Any
> courses? Addressing this type of question does not change the diagram
> above, though; but it influences how foreign keys migrate from top to
> bottom.
>

> > Given my assumptions, I consider the example [Course (C) - department
> > (D) - teacher (T)]. I deduce that the only relationship which is
> > redundant is between course and teacher since:

> Which is wrong. How would you know who teaches what without that?
>

> > However, in the example given, the redundant relationship is Teacher
> > and Department.

> ? Is this an answer given to you by someone?
>

> > The reasoning is that if the courses a teacher teaches
> > are known and the department to which each course belongs is also
> > known, it can be deduced to what department belongs each teacher.

> Why? Who said that a teacher teaches only courses in her own department?
> And even if that were the case, the relationship between Department and
> Teacher would *not* be redundant, because it represents a different
> fact. What if next year Prof. John Doe goes on sabbatical? Shall we
> forget that he belongs to a department because he doesn't teach that
> year?

Good discussion of the considerations. I would add two more.
1. In the real world, Teacher means someone who is accredited to teach, which means accredited **by** a Department. A Teacher is prevented from teaching in any old Department (where he is not accredited).
2. A Teacher may well have expertise in, and therefore be accredited in or qualified by, more than one Department. Joe Blogs teaches Mathematics under Department[Science], and Philosophy under Department[Humanities].
3. Sabbatical can be a CourseTeacher row, to establish that the Teacher remains with the Department (and gets paid), and must teach at least one Course (in order to be a Teacher).

> In your model, each teacher must always be assigned to at least one
> course. With
>
> (a) such an (unrealistic) constraint, *and*

Why is that constraint “unrealistic” ? In the real world, it is a common; even pedestrian, requirement. We have been implementing such in Relational databases since 1983.

> (b) with the additional constraint that teachers work only in their own
> department,

In order to avoid confusion, I would not use the term “their department”, because the teacher does not own a department, rather I would hold that which is shown in the data hierarchy:
__ each Department QUALIFIES 1-to-n Teachers.
Thus the Teacher belongs to a Department, not vice versa.
Thus a Teacher can teach only in a Department that he is qualified in.

In the same vein as [a], a Department cannot exist unless it has least one Teacher.

> it is true that you can infer the department of a teacher by looking at
> the courses taught by that teacher. But still, you need the
> Teacher-Department relationship for at least two reasons: first, it is
> semantically an important and distinct relationship in the application
> domain (ask yourself: does a teacher belongs to a department because he
> teaches a course in that department, or does he teach certain courses as
> a consequence of belonging to a given department?

That is correct. In any case, the Teacher IS QUALIFIED IN [belongs to; empowers] 1-to-n Departments, not vice versa.

> This is a point also
> Derek makes in a sibling post: what comes first? What depends on what?
> Meaning is king); and second, it allows you to easily enforce (b) (via
> a common ancestor referential constraint).

Aka [Declarative] Relational Integrity, as opposed to [Declarative] Referential Integrity.

I merely articulate the Relational Model, IDEF1X, and genuine Relational Data Modelling.

> On Wednesday, 17 March 2021 at 03:03:00 UTC+11, Nicola Vitacolonna wrote:
> > On 2021-03-13, Jaime Alvarez Benayas <jaime.alva...@gmail.com> wrote:
>
> I have not made all the cardinalities explicit in the diagram, but that
> does not matter for my point below. The labels on the relationships are
> missing as well, as they are missing in your ERD—but, again, this not
> strictly relevant to my point.

I accept that that missing info is not relevant to your point. But it is relevant to the process of Data Modelling: filling in those two categories of missing info will.
__ a. improve understanding of the data model and
__ b. improve its accuracy.
Thus when teaching Relational Data Modelling, Cardinality and Verbs that define relations must not be omitted (as they evidently are). The only allowance is, in simple exercises, the distinction between 0-to-n and 1-to-n can be overlooked, they can be considered “equal” for classroom purposes.

> It is obvious from the diagram above that (a) nothing is redundant (wrt
> to the limited information provided), *no matter how you fill in the
> missing cardinalities*, and (b) the "circularity" in your ERD is an
> artifact (the diagram above, seen as a graph, is a DAG).

What is seen; what is visual; what is semantic, is one data hierarchy (one DAG), plus two Binaries.

(And in my DM, two data hierarchies (two DAGs), plus two Binaries.)

> artefact

I categorically state, the pig poop herd purposely draw “circles” in their non-Relational ERDs in order to hammer is the falsity that “circles” are allowed. Yes, absolutely, when the DM is drawn top-to-bottom, such that the hierarchies are positioned logically, that contrived nonsense disappears. A “circle” will occur at every Binary entity, but it will not look like a visual circl when the DM is drawn properly, only when it is contrived to form a “circle”. As evidenced.

> Besides,
> compared to your first example, here there is not even a "common
> ancestor" situation

(In mine, there is. And it has meaning. And it provides Relational integrity.)

> > https://i.stack.imgur.com/Ppj1h.png
> >
> > I am told that the redundant relationship is Author - Editorial.
> > However, I am finding no redundant relationships

Yes. Well, when the data has been modelled Relationally, and erected using IDEF1X (my link above, page 2), that issue disappears:
- there is no relation between Author and Editorial
- there are no redundant relations

> Let's say that both A-E and A-B mean "Contribution", and that the
> relationship between Editorial and Book is "reviews/is reviewed in".
> *If* the following constraints hold "in the real world":
>
> (1) each time an author A makes a contribution to a book B then A makes
> a contribution for the editorial in which B is reviewed, and
>
> (2) each time A makes a contribution to an editorial E then there is
> a book reviewed in E to which A has contributed,

That is not a set of of constraints one would find in the real world, it is a set of of constraints that is contrived for classroom purposes. Just don’t call it “real world”.

You are introducing a new notion Contribution, that is not in the original question. Absurd as it is, ok, let’s model that, in order to validate it, and to perform some genuine modelling. Paper is cheap. Page 3 in the DM linked above.

To the eye, to the mind, the Book-centric Contribution is more logical than the Author-centric. That model proves [when compared with the solution, page 2) that there is no need for Contribution entity, it is redundant. Every element can be derived from the simpler solution. The notion of Contribution is merely a View (UNION DISTINCT) of Author and Editorial. Whereas Author and Editorial are each explicit facts; events, Contribution is not, it is a classification; a intellective concept only, it is a Collection, that can be derived from the facts.

> Note that constraints (1) and (2) above are not implied by the
> cardinalities in the diagram.

I wouldn’t look for considerations re [1][2] in the cardinalities, rather in the whole model,

Cheers
Derek

[Derek Ignatius Asirvadem]

Jaime

> On Sunday, 14 March 2021 at 00:14:06 UTC+11, jaime.alva...@gmail.com wrote:
>

> Finally I have a third example with Author (A), Editorial (E) and Book (B) as follows:
> > https://i.stack.imgur.com/Ppj1h.png
> >
> > I am told that the redundant relationship is Author - Editorial.

> > However, I am finding no redundant relationships as per my criteria ...

https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/comp_databases_theory/Alva%20Jaime%20DM%20B.pdf

When the entities are defined with a bit more precision, as per the Relational Model (197), and IDEF1X the Standard for Relational Data Modelling since 1983 (page 2 in th elink above), to be implemented on any Relational platform (sine 1981):
- Editorial is not a thing that exists independently, it is Dependent on the existence of a Book
- only Authors are added as Persons
--- via Person WRITES 1-to-n Book
- thus only Authors can criticise [review] Books

- there is no relation between Author and Editorial

--- other than, oh, it has the same two parents, but each are different facts

- there are no redundant relations

Cheers
Derek

[Nicola]

On 2021-03-18, Derek Ignatius Asirvadem <derek.a...@gmail.com> wrote:
> Jaime
>
>> On Sunday, 14 March 2021 at 00:14:06 UTC+11, jaime.alva...@gmail.com wrote:
>>
>> Finally I have a third example with Author (A), Editorial (E) and Book (B) as follows:
>> > https://i.stack.imgur.com/Ppj1h.png
>> >
>> > I am told that the redundant relationship is Author - Editorial.
>> > However, I am finding no redundant relationships as per my criteria ...
>
> https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/comp_databases_theory/Alva%20Jaime%20DM%20B.pdf

On page 1: note that the OP's second ERD has a many-to-many relationship
between Course and Department. You have included in that page both the
OP's first and second diagram, but your models correspond to the first
one only.

On page 2: in your model, each editorial can be associated only to one
person (the editor). That does not correspond to what the ERD aims to
convey, i.e., a many-to-many relationship (whatever that is) between
Author and Editorial.

On page 3: I have most likely reversed the interpretation of the
cardinalities of the ERD. In my ASCII art, I have drawn Editorial as
parent and Book as a child; in fact, the opposite makes more sense.
Besides, the same comment as above applies.

Which exposes one of the weaknesses of ERDs: there are so many dialects
that it is easy to get confused. In particular, it seems that in the
third ERD the cardinalities are drawn on the opposite sides compared to
the first two examples (besides, the third ERD has arrows, whose meaning
I do not know).

Nicola

[Derek Ignatius Asirvadem]

> On Thursday, 18 March 2021 at 22:17:28 UTC+11, Nicola wrote:
>
> On page 1: note that the OP's second ERD has a many-to-many relationship
> between Course and Department. You have included in that page both the
> OP's first and second diagram, but your models correspond to the first
> one only.

The first ERD was worth explaining. The second ERD [Course-n-m-Department] is too stupid to expand, it is included only to be complete. Removed.

> On page 2: in your model, each editorial can be associated only to one
> person (the editor). That does not correspond to what the ERD aims to
> convey, i.e., a many-to-many relationship (whatever that is) between
> Author and Editorial.

1. It is important to understand that I did not translate the ERD to a Data Model. No, I modelled the data. As per (a) what is most common and reasonable in the real world, and (d) what the broken ERD is intending (a real Data Model, not insanity), rather that what the ERD actually declares, which is insanity.

- That means the //concept// of each entity is somewhat different to that of the entity in the ERD of the same name. Eg. the primitive Editorial was an Independent thing, which is impossible in real life, because an Editorial is created by an Editor, and is a review of a Book, both of which must exist before the Editorial can.

- Therefore the relations are also different.

2. To the extent that there is a relation between [corrected] Author and [corrected] Editorial (there is a relation between any table t1 and any other table t2, at worst the Cartesian product), it is best understood as the relation between Person and Book.

3. To the extent that you conceive of it ala your Contribution, it is a cerebral or intellective notion, not a separate fact that needs to be established, on the basis that they share the same parent tables and thus very similar or identical Keys.

As explained in my previous response to you, it is a View (or Derived Table). The code looks like:
CREATE VIEW Contributor_V AS
____SELECT *, “”
________FROM Author
____UNION [DISTINCT]
____SELECT *
________FROM Editorial


Feel free to ask if you want more specific code.

> Which exposes one of the weaknesses of ERDs: there are so many dialects

One of the many weaknesses. Dialects prove that the method is a failure; incomplete. Same as UML.

That is on top of being pre-Relational, and that the Relational Modelling method is suppressed. As a I stated, every professor that teaches ERD is just an unthinking parrot, not even capable of finding the truth himself. Part of the pig poopery, part of the fraud. Needs to be shot.

> that it is easy to get confused. In particular, it seems that in the
> third ERD the cardinalities are drawn on the opposite sides compared to
> the first two examples

Yes.

> (besides, the third ERD has arrows, whose meaning
> I do not know).

Even the arrows are inconsistent.

I concentrated on answering his question, using Realtional methods, I did not enumerate the problems of ERD in general, or his ERDs in particular.

Cheers
Derek

[Derek Ignatius Asirvadem]

Nicola

> On Thursday, 18 March 2021 at 22:17:28 UTC+11, Nicola wrote:
>

> On page 1...
>
> On page 2...
>
> On page 3...

As a gesture of gratitude for pointing out issues that need correction, herewith a corrected set of models.
https://www.softwaregems.com.au/Documents/Student_Resolutions_2020/comp_databases_theory/Alva%20Jaime%20DM%20C.pdf

> On Thursday, 18 March 2021 at 14:38:13 UTC+11, Derek Ignatius Asirvadem wrote:
> > On Tuesday, 16 March 2021 at 23:23:28 UTC+11, Nicola wrote:
>
> __ Independent vs Dependent entities.

> __ Identifying vs Non-Identifying relations,
> __ which is based on the Relational Key.
>

> The Data Hierarchies are Normalised in the First Wave. Each hierarchy begins with an Independent entity, and flows into many Dependent entities.
>

> After the First Wave, after the Data Hierarchies are resolved, as trees; DAGs; graphs, which means no circular references; no Cyclic Graphs, we are ready to commence the Second Wave. Here we have entities that join two Data Hierarchies ... Technically, these are Binary entities. Whereas in the First Wave, Trees; DAGs, are demanded, in the Second Wave, that constraint does not [cannot] apply, the entities being established will not conform because they join two trees.

>
> In my Data Model (link above), the [First Wave] Data Hierarchies are not very deep, as it is a classroom exercise, but it remains that they must be understood properly:

> ...

>
> And the following are [Second Wave] Binaries:

> ...

>
> Thus a Teacher can teach only in a Department that he is qualified in.

All that text is now provided in the models, visually; semantically.

Cheers
Derek

[Derek Ignatius Asirvadem]

Nicola

Obviously, what we teach eager young minds is of great importance. From the mountain of consistent evidence, we know there is a large gap between:
__ science since 1970 (Codd's Relational Model and the commercial SQL platforms)
__ vs
__ pre-1970 anti-science (the anti-Relational filth and the freeware anti-SQL program suites) that are heavily marketed by the pig poop brigade as "Relational".

which you as the single academic who is genuinely interested in science, and I as the lone proponent of the RM, are attempting to close. However slowly. In that regard these questions, and there resolutions, will assist greatly. It is 2021, suppressing science, and teaching anti-science that is FIFTY YEARS out-of-date is a serious fraud.

In case you missed them. Could you please respond to these questions.

> On Thursday, 18 March 2021 at 14:38:13 UTC+11, Derek Ignatius Asirvadem wrote:

> > On Tuesday, 16 March 2021 at 23:23:28 UTC+11, Nicola wrote:
>
> > Note that the "circularity" in that diagram is only apparent (ERD are
> > misleading).
> So, why in heavens name, do professors teach ERD ?
>
> Why, since we have had IDEF1X for Relational Data Modelling since 1983 (established and well-known international standard since 1993), do professors suppress that, and instead “teach”; push; shove; market; propagate the broken ERD, which is a pre-Relational artefact ?
>

> > In your model, each teacher must always be assigned to at least one
> > course. With
> >
> > (a) such an (unrealistic) constraint, *and*
> Why is that constraint “unrealistic” ? In the real world, it is a common; even pedestrian, requirement. We have been implementing such in Relational databases since 1983.

Cheers
Derek

[Nicola]

On 2021-03-24, Derek Ignatius Asirvadem <derek.a...@gmail.com> wrote:
> Nicola

>
> In case you missed them. Could you please respond to these questions.
>
>> On Thursday, 18 March 2021 at 14:38:13 UTC+11, Derek Ignatius Asirvadem wrote:
>> > On Tuesday, 16 March 2021 at 23:23:28 UTC+11, Nicola wrote:
>>
>> > Note that the "circularity" in that diagram is only apparent (ERD are
>> > misleading).
>> So, why in heavens name, do professors teach ERD ?

I cannot speak for anyone else...

One reason is Chen's Entity-Relationship models are likely perceived as
easier to grasp for novices than something like IDEF1X. Whether that is
a correct perception, it can be debated. Another reason is probably just
historical, due to the huge influence of Chen's seminal paper.

In a general setting, ERD is fine for teaching the basics of modeling:
entities, relationships, generalizations. For someone who has never done
it, taking a description in a natural language and analyze it to distill
such concepts and put them in diagrammatic form is not a trivial task
(well, it's not trivial even for experts!). ERDs can be useful as
a gentle introduction to such topics.

ERD can be introduced without any background knowledge in databases
(although that is true about IDEF1X to some extent, when you are
teaching IDEF1X you are in fact teaching the Relational Model), and even
for purposes that are different from mapping into the Relational Model.
For instance, one may use ERDs in the context of programming—say, to
design a game.

When it comes to using ERD vs IDEF1X for designing Relational databases,
my experience is as follows:

- when teaching ERDs, one also has to explain the rules to translate
them into the RM: this is a layer of accidental complexity that IDEF1X
completely eliminates.
- On other hand, when teaching IDEF1X one has to introduce a richer
language, so it doesn't take less time to teach than ERD; but I'd say
that it doesn't take much longer either, as some people might might
expect.
- Perhaps contrary to someone's opinion, IDEF1X models can be grasped by
novices: I have successfully taught IDEF1X to people without any
background in Computer Science and high-school level of acquaintance
with logic for a few years now.
- ERDs make it difficult or impossible to reason about keys effectively:
think split keys, for instance. Or common ancestor constraints. This
is one of my biggest issues with ERDs. If you are using them to design
a video game, that might not be a problem, but for mapping them into
the RM, well, they effectively cripple your design process.
- There are some aspects of ERDs that are *far* from intuitive. If you
have ever tried to explain ERD relationships of degree higher than two
(and especially how to constrain the cardinalities of the participant
entities), then IDEF1X seems a lot simpler!

I have recommended my colleagues to switch to IDEF1X without success
(but without insisting either, to be honest). I guess this is simply
because habits are hard to change and most textbooks do not use it. New
generations pick up what they have been taught by previous generations.
Some may feel IDEF1X is "low-level" (too many details) compared to ERDs.
Or some may have criticisms along the lines of this analysis:

https://www.essentialstrategies.com/publications/modeling/idef1x.htm

Another issue may be that IDEF1X is not so popular in the industry
either, except for some sectors or in some geographical areas. E.g., it
doesn't seem to be so popular in Europe.

My main complaint with IDEF1X is the treatment of generalization
hierarchies, in particular the lack of a correct way to model a total
inclusive specialization (A must be one of B1, ..., Bn and it may be any
of B1, ..., Bn); the approach I have seen around (e.g., see Thomas
Bruce's book or ERwin's manual) does not cover such case in a sound way.
And the standard never mentions inclusive specializations, AFAICT.

Another thing: people who already know ERDs easily accept the
distinction between independent and dependent entities (which reminds
them of the distinction between ERD's strong and weak entities), but
they may not see what additional information identifying and
non-identifying relationships bring to the table ("If you drew all the
relationships without dashed lines, you would still be able to interpret
your model in the same way"). I found it difficult sometimes to convince
them that such a distinction is important.

Another criticism about IDEF1X is the lack of many-to-many relationships
(except in "Level 1"/"ER" diagrams), which sometimes makes it
awkward to read a relationship, because it is more natural to skip the
intermediate associative entity. For instance, one might want to model
the following:

- a customer BUYS zero or more products;
- a product IS BOUGHT BY zero or more customers.

In IDEF1X, you'd do it like this:

+-----------+ +-----------+
| | | |
| Customer | | Product |
| | | |
+-----------+ +-----------+
| |
| |
| +-----------+ |
| | | |
+-----O| Purchase |O-----+
| |
+-----------+

How would you label the relationships? You might use:

- a customer PERFORMS (?) zero or more purchases;
- a product IS SOLD AS (?) zero or purchases.

But the most natural reading is as above, with "buys"/"is bought by"—
which does not mention purchases, though.

IMO, this is a valid criticism. Some additional (an)notation to pick out
"pure associative entities" (for a lack of a better term) might help.

>> Why, since we have had IDEF1X for Relational Data Modelling since
>> 1983 (established and well-known international standard since 1993),
>> do professors suppress that, and instead “teach”; push; shove;
>> market; propagate the broken ERD, which is a pre-Relational artefact
>> ?

I think I have answered this above, at least by providing my own
perspective.

>> > In your model, each teacher must always be assigned to at least one
>> > course. With
>> >
>> > (a) such an (unrealistic) constraint, *and*
>> Why is that constraint “unrealistic” ? In the real world, it is
>> a common; even pedestrian, requirement. We have been implementing
>> such in Relational databases since 1983.

I was thinking more in terms of "department employees", one of whose
duties may be teaching (but they may not be teaching all the time). But
sure, you may think in terms of employees who teach (a subset of all the
employees): then they must teach something, of course.

Nicola

[Derek Ignatius Asirvadem]

> On Wednesday, 24 March 2021 at 23:27:48 UTC+11, Nicola wrote:

Thank you for your considerate, and very interesting, response.

It deserves a full answer. I am tight for time for the next few of days, please be patient.

Cheers
Derek

[Derek Ignatius Asirvadem]

Nicola

> On Saturday, 27 March 2021 at 20:34:57 UTC+11, Derek Ignatius Asirvadem wrote:
>
> Thank you for your considerate, and very interesting, response.
>
> It deserves a full answer

I opened a new thread, rather than clutter this one.

https://groups.google.com/g/comp.databases.theory/c/7OOt-44atbo

Cheers
Derek
>