All we need is an easy explanation of the problem, so here it is.
Figures 3.a and 3.b, section 1.4, page 381 of Edgar Codd’s 1970 landmark paper ‘A Relational Model of Data for Large Shared Data Banks’ presents a set of relation schemas (unnormalized in figure 3.a and in 1NF in figure 3.b).
The normalized relation schema
children′ has a key dependency
KEY(man#, childname), so it allows instances like the following relation:
(1, "Thomas", 2016-03-12) and
(3, "Thomas", 2016-03-12) can refer to two different children named ’Thomas’ in the real world, or to the same child named ’Thomas’ in the real world (implying that his two parents are employed by the same company). So the relationship between tuples and children in the real world is many-to-one, i.e. a tuple can only refer to a single child, but a child can be the referent of multiple tuples.
Does the relational model mandate that the relationship between tuples of a relation and their referents be one-to-one (thereby making Codd’s example incorrect since it is many-to-one)?
In other words: is aliasing forbidden in the relational model? Must we always use globally unique references in our relations, i.e. UUID keys?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Does the relational model mandate that the relationship between tuples of a relation and their referents be one-to-one
No. The relational model only manages the tuples. The mapping of a tuple to an assertion in some real-world domain, or some tangible external thing is not governed by the rules of the relational model.
Moreover, in your example the relation
children doesn’t model actual children, it models the relationship between an employee and a child. The relevant fact being modeled is only the number and ages of each employee’s children.
You could add a separate table with one row per actual child, but that would simply be a different model, not a more correct one.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂