4

enter image description here

I have this ER model that I am having difficulty understanding, since there is an entity set that has a relationship with itself. I am trying to understand two questions:

1) Can a child be born before the parent?
2) Can a person to be a child of themselves?

For 1) I don't think this is possible, because the parentID field would be null

For 2) Since this is a many to one relationship, I don't see why this can't be true, as long as the person isn't a child of someone else.

Improve this question

1 Answer 1

Reset to default
5

There's nothing in the ERD to constrain the model either way. Without suitable constraints, a child could be recorded as being born before his/her parent, a person could be their own child or parent, and cycles over any number of generations could exist.

For example, assuming the ERD is implemented as:

Person (personId PK, dateOfBirth, name, gender)
ChildOf (personId PK/FK, parentId FK)

we could record the following sets of rows:

Person (1, 2000-11-01, John, Male)   ChildOf (1, 2)
Person (2, 1970-05-23, Jane, Female) ChildOf (2, 4)
Person (3, 1985-01-11, Jack, Male)   ChildOf (3, 3)
Person (4, 1950-11-01, Joan, Female) ChildOf (4, 1)

There's nothing in the ERD to prevent any constraints either. As it stands, I would have to answer "yes" or "it depends on what unstated constraints you assume or implement" to both questions. If such constraints were required, I would recommend stating them explicitly.

Improve this answer
2
  • How do you apply such constraints? Does it require use of PL/SQL?
    – alphadog
    Commented Jan 29, 2017 at 11:12
  • 1
    It depends on your DBMS. Check constraints or triggers, possibly with user-defined functions, may be required. Preventing the same person from occurring in both parent/child roles in a single record is the easiest. Checking that children's birthdays succeed their parents' is a simple lookup. However, to prevent cycles requires a recursive query, or the use of a closure table.
    – reaanb
    Commented Jan 29, 2017 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.