circular relationships ok?
Volker Hetzer
Just in general, are circular relationships something that
can always be avoided?
Or, given a model with a circular relationship, possibly
spanning several tables, is there a way to get rid of them?
Lots of Greetings and thanks!
Volker
Bob Hairgrove
<volker...@ieee.org> wrote:
>Hi!
>Just in general, are circular relationships something that
>can always be avoided?
>Or, given a model with a circular relationship, possibly
>spanning several tables, is there a way to get rid of them?
Do you have an example?
--
Bob Hairgrove
NoSpam...@Home.com
Alexandr Savinov
> Hi!
> Just in general, are circular relationships something that
> can always be avoided?
> Or, given a model with a circular relationship, possibly
> spanning several tables, is there a way to get rid of them?
One approach consists in using the concept-oriented data model as follows.
1. Order your tables so that if table A references records from table B,
then A is positioned under B. For example, if an order has a field with
its customer then you position them as follows:
Customre
|
Order
2. After that any relationship you define in this acyclic graph of
tables will use only subtables. In other words, if table A has a
relationship with table B then there is a common subtable C which is
used to establish it (there can be more subtables for complex
relationships). For example, a relationship between a customer and its
products can be implemented via common Order table:
Customer Product
\ /
Order
This model avoids the problem of cycles in a principled manner. It
provides also many other advantages and conveniences. Instead of
modeling relationships via an arbitrary graph we use a concrete
structure of ordered tables for that. Querying, grouping/aggregation,
inference are also made easier in such an approach.
Roy Hann
news:4406ab1a$1...@news.fhg.de...
> This model avoids the problem of cycles in a principled manner. It
> provides also many other advantages and conveniences. Instead of
> modeling relationships via an arbitrary graph we use a concrete
> structure of ordered tables for that.
And this has precisely what to do with the real world?
> Querying, grouping/aggregation,
> inference are also made easier in such an approach.
I just don't believe that. Persuade me.
Roy
Alexandr Savinov
> "Alexandr Savinov" <sp...@conceptoriented.com> wrote in message
> news:4406ab1a$1...@news.fhg.de...
>
>> This model avoids the problem of cycles in a principled manner. It
>> provides also many other advantages and conveniences. Instead of
>> modeling relationships via an arbitrary graph we use a concrete
>> structure of ordered tables for that.
>
> And this has precisely what to do with the real world?
Yes. It is designed for the real world.
>> Querying, grouping/aggregation,
>> inference are also made easier in such an approach.
>
> I just don't believe that. Persuade me.
If you do not believe then nobody can persuade you (except for yourself).
The only thing I can do is to answer concrete questions.
Roy Hann
> Roy Hann schrieb:
> >> Querying, grouping/aggregation,
> >> inference are also made easier in such an approach.
> >
> > I just don't believe that. Persuade me.
>
> If you do not believe then nobody can persuade you (except for yourself).
Rubbish. I refuse to believe; I don't refuse to be persuaded.
> The only thing I can do is to answer concrete questions.
You've made an assertion that I find implausible. If I spent my time trying
to get to the bottom of all the things people insist are true yet which I
see no obvious reason to expect really are true, I'd get nowhere. Come on,
give me at least a one paragraph case for why I should take the slightest
interest in what is at first glance just a fantasy.
Roy
Alexandr Savinov
> "Alexandr Savinov" <sp...@conceptoriented.com> wrote in message
> news:4406c332$1...@news.fhg.de...
>> Roy Hann schrieb:
>
>>>> Querying, grouping/aggregation,
>>>> inference are also made easier in such an approach.
>>> I just don't believe that. Persuade me.
>> If you do not believe then nobody can persuade you (except for yourself).
>
> Rubbish. I refuse to believe; I don't refuse to be persuaded.
Nobody is going to persuade you. Only you can do it (if you want to).
>> The only thing I can do is to answer concrete questions.
>
> You've made an assertion that I find implausible. If I spent my time trying
> to get to the bottom of all the things people insist are true yet which I
I have not made any assertions (at least an assertion that can be be
evaluated to true or false). I am afraid that you have an illusion that
somebody wants to convert you to another religion. The only thing I did
consists in proposing a solution that works. If this solution does not
satisfy you then say why (again, nothing to do with true or false).
> see no obvious reason to expect really are true, I'd get nowhere. Come on,
You are already nowhere because you lost the focus of this thread. Here
is again the problem (as far as I understand it): how to get rid of
cycles in modeling relationships. Do you have a solution? Do you have
questions concerning my solution?
> give me at least a one paragraph case for why I should take the slightest
> interest in what is at first glance just a fantasy.
Whether you are interested or not is your own problem and (again) I am
not going to teach you what direction in data modeling to choose. I can
only *inform* you *if* you have an interest. If you have a problem in
data modeling then formulate it concretely and then I could propose a
possible solution (actually, a spectrum of solution neither of them
being true or false). If you have a question to the solution I proposed
for the problem of modeling relationships then just ask and I will try
to answer.
Roy Hann
> Roy Hann schrieb:
> > Rubbish. I refuse to believe; I don't refuse to be persuaded.
>
> Nobody is going to persuade you.
Well evidently nobody is going to try anyway.
> Only you can do it (if you want to).
I am very good at persuading myself of things. Mostly they are wrong or
insane. That's why I try to keep them to myself until I have formed an
argument that I hope would persuade a skeptic.
> I have not made any assertions
I believe we may be encountering a language barrier here. Since (to my
shame) I speak only English I guess we're not going to get any further with
this because I can't express what I said any differently.
> You are already nowhere because you lost the focus of this thread. Here
> is again the problem (as far as I understand it): how to get rid of
> cycles in modeling relationships. Do you have a solution? Do you have
> questions concerning my solution?
I guess I do have a question actually. Why would I ever want to get rid of
a cycle? (What fundamental purpose is served by that? What fundamental
damage is not done by that?)
> Whether you are interested or not is your own problem and (again) I am
> not going to teach you what direction in data modeling to choose. I can
> only *inform* you *if* you have an interest.
I think you are promoting an unnecessary idea (for fun or profit, I don't
care). For various reasons I dislike seeing unsubstantiated assertions
about mysteriously beneficial database design methodologies. On the other
hand, I am keenly interested in good ideas that solve real problems
properly. I am quite ready to read about them, right here, right now,
because I could be wrong. Please, get me interested. One paragraph.
Roy
Alexandr Savinov
> "Alexandr Savinov" <sp...@conceptoriented.com> wrote in message
> news:4406cd86$1...@news.fhg.de...
>> Roy Hann schrieb:
>
>>> Rubbish. I refuse to believe; I don't refuse to be persuaded.
>> Nobody is going to persuade you.
>
> Well evidently nobody is going to try anyway.
>
>> Only you can do it (if you want to).
>
> I am very good at persuading myself of things. Mostly they are wrong or
> insane. That's why I try to keep them to myself until I have formed an
> argument that I hope would persuade a skeptic.
>
>> I have not made any assertions
>
> I believe we may be encountering a language barrier here. Since (to my
> shame) I speak only English I guess we're not going to get any further with
> this because I can't express what I said any differently.
>
>> You are already nowhere because you lost the focus of this thread. Here
>> is again the problem (as far as I understand it): how to get rid of
>> cycles in modeling relationships. Do you have a solution? Do you have
>> questions concerning my solution?
>
> I guess I do have a question actually. Why would I ever want to get rid of
> a cycle? (What fundamental purpose is served by that? What fundamental
> damage is not done by that?)
The only constructive thing among all your posts. But this question is
not to me. Generally I think cycle are not a very good thing. The
presence shows that something is wrong in the theory. It is of course
too general assertion and probably the originator of this thread has his
own arguments why cycles should be avoided.
>> Whether you are interested or not is your own problem and (again) I am
>> not going to teach you what direction in data modeling to choose. I can
>> only *inform* you *if* you have an interest.
>
> I think you are promoting an unnecessary idea (for fun or profit, I don't
As I said already, here in this thread I do not promote anything
(directly). I just proposed a solution the problem. Both the solution
and the problem may well be bad but it is precisely what we want to
discuss here.
> care). For various reasons I dislike seeing unsubstantiated assertions
> about mysteriously beneficial database design methodologies. On the other
> hand, I am keenly interested in good ideas that solve real problems
I afraid that here you try to cheat yourself when you say that "I am
keenly interested in good ideas". You are probably interested in *ready*
and well substantiated ideas but that directly means that such an idea
is rather old. New ideas by definition cannot be substantiated and hence
you need to take active position and turn your intuition on. I
> properly. I am quite ready to read about them, right here, right now,
> because I could be wrong. Please, get me interested. One paragraph.
I am sorry but you should do it yourself - I do not want to force
anybody to do anything. But I am ready to help you if you need it
(answer concrete questions).
Roy Hann
> Roy Hann schrieb:
> New ideas by definition cannot be substantiated
I wonder if that language barrier is at work again?
> and hence
> you need to take active position and turn your intuition on.
Intuition and belief. Can prayer be far away?
Roy
Alexandr Savinov
If you are really interested in new ideas (although I seriously doubt)
then you might want to read a paper about a new programming paradigm,
called concept-oriented programming (COP):
HTML: http://conceptoriented.com/papers/csjm_05/csjm05.html
PS: http://conceptoriented.com/savinov/publicat/csjm_05.ps
Shortly, a new programming construct is introduced, called concept. It
is a combination of one object class and one reference class. It
generalizes conventional classes as defined in OOP. It is not directly
connected with this forum, however, it complements the concept-oriented
data model (COP) and hence can be useful for understanding how data
modeling and programming are connected. COP is formulated as a method of
programming however it describes thing which are important for data
modeling, for example, how data elements are represented and how they
are accessed.
And please, do not ask me to force you to believe in this (or any other)
theory. If you like it and want to know more then ask questions. If you
do not like it and want to say why then post your opinion or ignore it.
Volker Hetzer
> On Wed, 01 Mar 2006 18:05:33 +0100, Volker Hetzer
> <volker...@ieee.org> wrote:
>
>> Hi!
>> Just in general, are circular relationships something that
>> can always be avoided?
>> Or, given a model with a circular relationship, possibly
>> spanning several tables, is there a way to get rid of them?
>
> Do you have an example?
There are nets on a pcb. Each net can have constraints, like
length or impedance.
Now, each constraint has a structure, namely an expression.
This expression contains several tokens, like constants,
literals and *nets*.
So, for instance, I can have a constraint that net A has
to be 5mm longer than net b.
The way I have modeled this until now is:
Net(NetName)
FreeNetConstraint(NetName,ConstraintName,...)
Token(TokenName,TokenValue)
ExprElement(NetName,ConstraintName,TokenName)
Now, ExprElement can be a Token *or a net*. So I modeled Token
and Net as subtypes of ExprElement.
I'd love to have a place where I could upload an image but I don't.
Lots of Greetings and Thanks for bothering!
Volker
Volker Hetzer
> Volker Hetzer schrieb:
>> Hi!
>> Just in general, are circular relationships something that
>> can always be avoided?
>> Or, given a model with a circular relationship, possibly
>> spanning several tables, is there a way to get rid of them?
>
> One approach consists in using the concept-oriented data model as follows.
>
> 1. Order your tables so that if table A references records from table B,
> then A is positioned under B. For example, if an order has a field with
> its customer then you position them as follows:
>
> Customre
> |
> Order
Lots of Greetings!
Volker
Alexandr Savinov
Generally, that is very bad idea. In programming it is unavoidable (for
fundamental reasons). In data modeling it can be and should be avoided.
It can be substantiated from many different sides. Here are some of them
adapted for data modeling. If an element e1 references element e2 then
it is interpreted as e1 is a member of e2, where e2 is a collection
(logical collection, not physical). In other words, if a thing has an
attribute value then this value is a set where it is a member. For
example, if a product references a category then the category is a
collection while this product is a member of this collection. If a
product has a color then this color is a collection and this product is
a member of this collection. Manipulating attribute values in this case
is equivalent to manipulating collections. Now assume that you have a
cycle. This means that an element is a member of itself and it is
normally interpreted as a non-sense. A wide spread approach to data
modeling is manipulating groups of data, i.e., viewing data as
structured using collections. In this case we want these collections to
be consistent and have no cycles. Why? For example, having ordered your
data by organizing it in collections you can build levels of details.
Naturally we do not want to have a cycle somewhere in our structure of
levels because that would mean that one level is a more specific or more
general level of itself.
In most existing models this constraint is not taken into account so
that you are free to build any meaningless data structure you want.
Having this constraint (ordered elements with no cycles) you have a
number of other advantages. It allows you to carry complex analytical
operations, meaningfully query data etc.
David Portas
>
> In most existing models this constraint is not taken into account so
> that you are free to build any meaningless data structure you want.
> Having this constraint (ordered elements with no cycles) you have a
> number of other advantages. It allows you to carry complex analytical
> operations, meaningfully query data etc.
>
I'll refer to the example you gave earlier:
>
> 2. After that any relationship you define in this acyclic graph of
> tables will use only subtables. In other words, if table A has a
> relationship with table B then there is a common subtable C which is
> used to establish it (there can be more subtables for complex
> relationships). For example, a relationship between a customer and its
> products can be implemented via common Order table:
>
> Customer Product
> \ /
> Order
>
>From your narrative I'm not certain what this diagram means. I assumed
that the OP was referring to foreign key constraints in an ER model. If
so then am I correct in understanding that your diagram means that two
constraints will be enforced:
1. That an Order refers to zero, one or more customers in the Customer
table.
2. That an Order refers to zero, one or more products that are in the
Product table.
Am I right? If so, how would you draw a diagram that would add a third
constraint:
3. That a Customer has zero, one or more "favourite" products that are
in the Products table.
?
In an ER diagram this third constraint would make a circular reference
(see SQL example below).
Can "concept oriented" enforce such constraints?
If so, how do you draw diagrams of them without loops?
Finally, what is the advantage of eliminating a loop from the diagram?
CREATE TABLE Customers (customer_id INT PRIMARY KEY /* ... other
columns */)
CREATE TABLE Products (product_id INT PRIMARY KEY /* ... other columns
*/)
CREATE TABLE Orders (customer_id INT REFERENCES Customers
(customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
KEY (customer_id,product_id) /* ... other columns */ )
CREATE TABLE CustomerFavourites (customer_id INT REFERENCES Customers
(customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
KEY (customer_id,product_id))
--
David Portas
Alexandr Savinov
> Alexandr Savinov wrote:
>> In most existing models this constraint is not taken into account so
>> that you are free to build any meaningless data structure you want.
>> Having this constraint (ordered elements with no cycles) you have a
>> number of other advantages. It allows you to carry complex analytical
>> operations, meaningfully query data etc.
>>
>
>
> I'll refer to the example you gave earlier:
>
>> 2. After that any relationship you define in this acyclic graph of
>> tables will use only subtables. In other words, if table A has a
>> relationship with table B then there is a common subtable C which is
>> used to establish it (there can be more subtables for complex
>> relationships). For example, a relationship between a customer and its
>> products can be implemented via common Order table:
>>
>> Customer Product
>> \ /
>> Order
>>
>
>>From your narrative I'm not certain what this diagram means. I assumed
> that the OP was referring to foreign key constraints in an ER model. If
> so then am I correct in understanding that your diagram means that two
> constraints will be enforced:
>
> 1. That an Order refers to zero, one or more customers in the Customer
> table.
One order has one customer.
> 2. That an Order refers to zero, one or more products that are in the
> Product table.
One order has one product.
> Am I right? If so, how would you draw a diagram that would add a third
> constraint:
>
> 3. That a Customer has zero, one or more "favourite" products that are
> in the Products table.
> ?
I will slightly modify the example.
Customer Product
\ /
Order /
\ /
Item
- all connections are upward arrows interpreted as follows:
- one customer has many orders
- one order has many items
- one ordered item is one product
- one product has many (ordered) items
- one order has one customer and so on
- an upward arrow is a many-to-one relationship
- an arrow is "member of collection" relationship
The order of concepts means also multiplicity of the relationship:
- if B is below A (B references A) then B->A is many-to-one (for
example, many orders reference one customer).
- if A is above B (A is referenced by B) the A->B is one-to-many (for
example, one customer has many orders). It is also interpreted as
multi-valued attribute.
Cycles are not allowed. The next reason for that is:
- upper levels are more general and they do not know what kind of more
specific elements will use them and hence we cannot reference to what we
do not know. In particular, customers are defined *before* orders and
items are defined so we cannot reference what does not yet exist. In
other words, who said that for those customers there will be orders?
Customers are customers and they do not need orders to be customers. (If
they do need orders then they will be on the same level.)
However, the real world is more complex than the theory. For example, a
customer might well has to be characterized by favorite products. Since
both customers and products are unaware of what is a "favorite product"
we define a common subconcept:
Customer Product
\ /
Favorite(c,p)
Now we can get a list of favorite products for a customer and a list of
customer for this product as a favorite.
This method should be used always in order to avoid cycles. However,
sometimes we still need to have a direct downward reference. For
example, we might want to store the last order for each customer (say,
for performance reasons). There are two ways how to interpret this:
- It is hack or misuse. It is similar to using goto in programming. It
is possible if you know what you are doing and guarantee consistency.
- The concepts with loops (mutual references) have to be placed in one
level and interpreted as parts of one entity separated for some reason.
> In an ER diagram this third constraint would make a circular reference
> (see SQL example below).
I do not see in your SQL example circular references.
> Can "concept oriented" enforce such constraints?
> If so, how do you draw diagrams of them without loops?
A model with cycles is not a concept-oriented model (theoretically). It
is a strong constraint but it is part of the definition. Then the
question is if it is useful or not. For any model there are examples
which are non-trivial and very difficult to implement. Loops and cycles
can be always avoided by introducing a common subconcept. If A and B
mutual reference each other then we introduce a common subconcept C:
A B
\ /
C
Now A does not know B and B does not know A. However, it is C who knows
both and relates them. However this relationships between A and B is
many-to-many. If we need one-to-one then it is done as follows:
A = B (arrows in both directions)
They are considered part of one thing.
> Finally, what is the advantage of eliminating a loop from the diagram?
Technically loops and cycles are not strictly prohibited. They are not
allowed theoretically because they do not allow us to do the following
things:
- having canonical semantics for the model (in particular, a finite
number of dimensions).
- carrying analytical operations (aggregation etc.)
- carrying out logical inference
We still may use cycles as a hack but in this case we take
responsibility for their interpretation.
Thus an advantage is that having no cycles allows us to introduce
canonical semantics for our model and as a consequence the database will
be able to automatically manage and maintain it. Otherwise we have to
this ourselves.
> CREATE TABLE Customers (customer_id INT PRIMARY KEY /* ... other
> columns */)
>
> CREATE TABLE Products (product_id INT PRIMARY KEY /* ... other columns
> */)
>
> CREATE TABLE Orders (customer_id INT REFERENCES Customers
> (customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
> KEY (customer_id,product_id) /* ... other columns */ )
>
> CREATE TABLE CustomerFavourites (customer_id INT REFERENCES Customers
> (customer_id), product_id INT REFERENCES Products (product_id), PRIMARY
> KEY (customer_id,product_id))
I do not find loops here but I hope I answered your question.
The general idea is that cycles are evil because they do not allow us to
meaningfully interpret data.
David Portas
>
> Customer Product
> \ /
> Favorite(c,p)
>
> Now we can get a list of favorite products for a customer and a list of
> customer for this product as a favorite.
>
But now you've removed Order from and Item from the diagram. Are you
saying you can only avoid loops by drawing 2 diagrams instead of 1?
> I do not see in your SQL example circular references.
Then you didn't draw the ER diagram for the constraints in that schema.
>
> > Can "concept oriented" enforce such constraints?
> > If so, how do you draw diagrams of them without loops?
>
> A model with cycles is not a concept-oriented model (theoretically). It
> is a strong constraint but it is part of the definition. Then the
> question is if it is useful or not.
I think that means No. You are saying I have to change my business
rules for concept-oriented to work?
--
David Portas
Volker Hetzer
> Volker Hetzer schrieb:
>> Alexandr Savinov schrieb:
>>> Volker Hetzer schrieb:
>>>> Hi!
>>>> Just in general, are circular relationships something that
>>>> can always be avoided?
>>>> Or, given a model with a circular relationship, possibly
>>>> spanning several tables, is there a way to get rid of them?
>>>
>>> One approach consists in using the concept-oriented data model as
>>> follows.
>>>
>>> 1. Order your tables so that if table A references records from table
>>> B, then A is positioned under B. For example, if an order has a field
>>> with its customer then you position them as follows:
>>>
>>> Customre
>>> |
>>> Order
>> So, what if A references B, B references C and C references A?
what if one row (a) of Table A references a bunch of rows in B,
each row (b) in B references a bunch of rows in C and each row (c) in
C references zero or one row a' in A?
Then a doesn't references itself, it references another row in the
same table.
> Now assume that you have a
> cycle. This means that an element is a member of itself and it is
> normally interpreted as a non-sense.
In that case it isn't referencing itself, just another object in the
same table.
So, maybe it's not a circle but a spiral of an unknown number of turns.
Lots of Greetings!
Volker
Volker Hetzer
> A model with cycles is not a concept-oriented model (theoretically). It
> is a strong constraint but it is part of the definition. Then the
> question is if it is useful or not. For any model there are examples
> which are non-trivial and very difficult to implement. Loops and cycles
> can be always avoided by introducing a common subconcept. If A and B
> mutual reference each other then we introduce a common subconcept C:
>
> A B
> \ /
> C
Ok, here goes:
Net<-------\
| |
V |
Constraint*|
| |
V |
Token* |
/ \ |
| -----/
V
Constant
(* denotes "zero or more")
("Token" refers to either a constant or a Net, not both.)
How would you do it?
Maybe I'm blind but I simply don't see it.
My problem is that the relations are directed. A Nrt doesn't
refer to constraints and tokens, a net refers to constraints
and the token optionally refers to another net.
Even if I add a Token/Net table, I still have the circle.
Lots of Greetings!
Volker
David Portas
organizational hierarchy.
CREATE TABLE Employees
(employee_id INTEGER PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
manager_id INTEGER NOT NULL
REFERENCES Employees (employee_id),
CHECK (employee_id =0 OR employee_id <> manager_id));
The business rule is that each employee has a manager who is also an
employee.
--
David Portas
Alexandr Savinov
> Alexandr Savinov wrote:
>> Customer Product
>> \ /
>> Favorite(c,p)
>>
>> Now we can get a list of favorite products for a customer and a list of
>> customer for this product as a favorite.
>>
>
> But now you've removed Order from and Item from the diagram. Are you
> saying you can only avoid loops by drawing 2 diagrams instead of 1?
I simply cannot draw it. There exist only one diagram and it describes
one model:
Top
/ \
Customer Product
| \ / |
| \/ |
| /\ |
Order / \ |
| / Favorite
Item /
\ /
Bottom
(Top and Bottom are useful in theory as the most abstract and the most
specific concepts.)
>> I do not see in your SQL example circular references.
>
> Then you didn't draw the ER diagram for the constraints in that schema.
Then explain please what you mean.
>>> Can "concept oriented" enforce such constraints?
>>> If so, how do you draw diagrams of them without loops?
>> A model with cycles is not a concept-oriented model (theoretically). It
>> is a strong constraint but it is part of the definition. Then the
>> question is if it is useful or not.
>
> I think that means No. You are saying I have to change my business
> rules for concept-oriented to work?
What if your business rule says that we need to use goto in programming?
I just want to say that concept-oriented approach is able to describe
a wide spectrum of real world models. Give me an example and I will try
to model it.
Alexandr Savinov
An arrow goes from Employee up, then turns down and then comes to this
concept from down:
/\
Employee |
\/
That is very simple. Loops (self-references) can be used because they
are very convenient and frequently used. And they have clear and
unambiguous semantics. My opinion that loops should be permitted but not
cycles. Cycles do not have unambiguous semantics. If you can explain to
your database how to interpret them then cycles can also be used but I
do not know such an interpretation. And, IMHO, it is reason why
contemporary database do not manage such relationships - it is a task of
the programmer.
-CELKO-
which brings all the schema objects into being all at once.
But the practical results are generally bad. A simple A->B and B->A
cycle can prevent you from inserting or deleting from both tables ("To
get a job, you need experience; to get experience, you need a job").
The other "gotcha" is A->B, A->C and B->C with cascaded actions. I
change A, which fires actions in both B and C. The change in B fires
an action in C. But the changes to C are different on the same rows
(say SET NULL and SET DEFAULT); which one takes effect? I one early
version of DB2, the answer was whoever was the last guy to touch C --
unpredictable.
We can split up your example and get rid of the cycle:
CREATE TABLE Personnel
(employee_id INTEGER NOT NULL PRIMARY KEY,
employee_name VARCHAR(50) NOT NULL,
..);
CREATE TABLE Teams
(team_nbr INTEGER NOT NULL,
employee_id INTEGER NOT NULL
REFERENCES Personnel (employee_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (team_nbr, employee_id);
job_title CHAR(10) NOT NULL
CHECK (job_title in ('mgr', 'member', ..)),
..);
I could also add a constraint to be sure each team has a single
manager, etc.
Alexandr Savinov
> Alexandr Savinov schrieb:
>> Volker Hetzer schrieb:
>>> Alexandr Savinov schrieb:
>>>> Volker Hetzer schrieb:
>>>>> Hi!
>>>>> Just in general, are circular relationships something that
>>>>> can always be avoided?
>>>>> Or, given a model with a circular relationship, possibly
>>>>> spanning several tables, is there a way to get rid of them?
>>>>
>>>> One approach consists in using the concept-oriented data model as
>>>> follows.
>>>>
>>>> 1. Order your tables so that if table A references records from
>>>> table B, then A is positioned under B. For example, if an order has
>>>> a field with its customer then you position them as follows:
>>>>
>>>> Customre
>>>> |
>>>> Order
>>> So, what if A references B, B references C and C references A?
> Ok, I wasn't specific enough.
> what if one row (a) of Table A references a bunch of rows in B,
> each row (b) in B references a bunch of rows in C and each row (c) in
> C references zero or one row a' in A?
> Then a doesn't references itself, it references another row in the
> same table.
There is not such thing as "a row reference a bunch of rows". Any
reference is a single thing. A row can reference only one row.
Multi-valued attributes are implemented indirectly. In diagram this
means changing direction of arrows, i.e., any downward arrow means a
one-to-many relationship or multi-valued attribute.
I assume that you mean the following (maybe I am wrong):
a row from A is referenced from a bunch of rows in B,
a row from B is referenced from a bunch of rows in C,
each row (c) in C references zero or one row a' in A
Then it is modeled as follows:
A
| \
B |
| /
C
Here for each a in A we have many b in B. For each b in B we have many c
in C. In the opposite direction: each c in C references one b in B and
one a in A. Each b in B references one a in A.
>> Now assume that you have a cycle. This means that an element is a
>> member of itself and it is normally interpreted as a non-sense.
> In that case it isn't referencing itself, just another object in the
> same table.
>
> So, maybe it's not a circle but a spiral of an unknown number of turns.
If it is a loop then my opinion is that they should be permitted.
Formally, we can avoid them but the life will be too hard. In addition,
handling them is not a difficult task - loops (self-references) have
clear and unambiguous semantics. So if an employee needs to reference a
manager which is also an employee then it is not a problem.
If you have cycle then it is worse. It shows that something is wrong.
However, I cannot exclude that sometimes this hack can be unavoidable.
We need to separate theory and practice.
Marshall Spight
-CELKO- wrote:
> Technically, you can declare cycles with a CREATE SCHEMA statement
> which brings all the schema objects into being all at once.
>
> But the practical results are generally bad. A simple A->B and B->A
> cycle can prevent you from inserting or deleting from both tables ("To
> get a job, you need experience; to get experience, you need a job").
I won't comment on the deletion difficulty you describe. As for
the insertion difficulty, would it be possible to defer the foreign
key checks until the commit?
> The other "gotcha" is A->B, A->C and B->C with cascaded actions. I
> change A, which fires actions in both B and C. The change in B fires
> an action in C. But the changes to C are different on the same rows
> (say SET NULL and SET DEFAULT); which one takes effect? I one early
> version of DB2, the answer was whoever was the last guy to touch C --
> unpredictable.
Nasty.
Marshall
Alexandr Savinov
Try the following approach in order to find directions for arrows and
establish an order without cycles. Assume that you do not have any
entities and your model is empty. Then take, say, Net, and ask a question:
- Does net know what is a Constant? Can it exist without Constants? Is
it created before Constants? Is it more general thing than Constant?
I thing yes, but you should decide yourself. If so then Net is
positioned above Constant and any constant object can reference one object.
The same question should be asked about other pairs of concepts. As far
as understand your problem domain, Net is the primary object which is
able to exist without any constraints. However, constraints are not able
to exist without a net. So a net object should not reference any other
object in your model and has to be positioned at the top. (No outgoing
arrows from Nets.)
Constraint is probably the most specific concept. It has to know
(reference) a net (for which it is created), a constant and other
elements it involves. So position it at the bottom and all arrows will
originate in it. However, if we need to reference several elements, say,
a constraint can be used in many nets then we need a common
subconcept,say, NetConstraint which references both. The same is applied
to other relationships.
Volker Hetzer
A constraint always needs a net, it doesn't need tokens.
A token needs a constraint, and it needs either a net or a constant.
The tricky bit is deleting a net. If that net is referenced by other
tokens, the delete ought to fail.
I need to think that over.
>
> I thing yes, but you should decide yourself. If so then Net is
> positioned above Constant and any constant object can reference one object.
>
> The same question should be asked about other pairs of concepts. As far
> as understand your problem domain, Net is the primary object which is
> able to exist without any constraints. However, constraints are not able
> to exist without a net. So a net object should not reference any other
> object in your model and has to be positioned at the top. (No outgoing
> arrows from Nets.)
>
> Constraint is probably the most specific concept. It has to know
> (reference) a net (for which it is created), a constant and other
> elements it involves. So position it at the bottom and all arrows will
> originate in it. However, if we need to reference several elements, say,
> a constraint can be used in many nets then we need a common
> subconcept,say, NetConstraint which references both. The same is applied
> to other relationships.
>
Thanks a lot!
Volker
Roy Hann
news:1141320113.9...@v46g2000cwv.googlegroups.com...
> Technically, you can declare cycles with a CREATE SCHEMA statement
> which brings all the schema objects into being all at once.
>
> But the practical results are generally bad. A simple A->B and B->A
> cycle can prevent you from inserting or deleting from both tables ("To
> get a job, you need experience; to get experience, you need a job").
Whether that is wrong depends on the real business rule. If the constraint
wrongly imposes a restriction that doesn't exist in the real world then it
is a database design error. However if it is a real rule in the real world
but the tool won't enforce it, then the tool is inviting corruption.
> The other "gotcha" is A->B, A->C and B->C with cascaded actions. I
> change A, which fires actions in both B and C. The change in B fires
> an action in C. But the changes to C are different on the same rows
> (say SET NULL and SET DEFAULT); which one takes effect? I one early
> version of DB2, the answer was whoever was the last guy to touch C --
> unpredictable.
This is an implementation detail that can be got right or got wrong, not an
argument for refusing to support the rules of the business. (It is also,
dare I say, a glaring example of why in the 21st century we should be asking
why we are still satisfied only to be able to make one incremental update to
one row of one table at time.)
Roy
Alexandr Savinov
So net does not reference any other item.
> A constraint always needs a net, it doesn't need tokens.
So constraint item will reference (know) a net. It will not reference a
token.
> A token needs a constraint, and it needs either a net or a constant.
So a token references constraint. For OR you might define two fields, it
is a separate problem in data modeling.
> The tricky bit is deleting a net. If that net is referenced by other
> tokens, the delete ought to fail.
No, in good model it should be managed automatically. If you delete a
collection then all its elements have to be also deleted (we treat a
referenced item as a collection). In the concept-oriented model the
deletion is propagated automatically down till the bottom. However, the
deletion is not necessary a physical deletion. It is more general. When
you delete a data item, say a net object, then you actually say that it
does not exist anymore as an entity. The non-existence then is expressed
as physical deletion of this item. However, for all its subitems (items
that reference this item) we do not use physical deletion but rather we
set this reference to null. Since null is equivalent to non-existence
(in COM) then it is quite correct operation. So a token that referenced
the deleted net will have null instead of it automatically. However,
each concept where data items live should define if null is permitted,
i.e., if this item can exist with null in some position. If not then
this item is automatically deleted and, as a consequence, its subitems
are assigned null value in the corresponding field. This operation is
then propagated down to the bottom.
By the way, the deletion can propagate also upward and in this case it
is based on reference counting. If some item is referenced less than N
times (by its subitems) then it can be automatically deleted. If N=0
then it is precisely garbage collection mechanism. Items which are not
referenced are deleted.
> I need to think that over.
>
>>
>> I thing yes, but you should decide yourself. If so then Net is
>> positioned above Constant and any constant object can reference one
>> object.
>>
>> The same question should be asked about other pairs of concepts. As
>> far as understand your problem domain, Net is the primary object which
>> is able to exist without any constraints. However, constraints are not
>> able to exist without a net. So a net object should not reference any
>> other object in your model and has to be positioned at the top. (No
>> outgoing arrows from Nets.)
>>
>> Constraint is probably the most specific concept. It has to know
>> (reference) a net (for which it is created), a constant and other
>> elements it involves. So position it at the bottom and all arrows will
>> originate in it. However, if we need to reference several elements,
>> say, a constraint can be used in many nets then we need a common
>> subconcept,say, NetConstraint which references both. The same is
>> applied to other relationships.
>>
>
> Thanks a lot!
> Volker
David Portas
I understand now that you don't recognize cycles because your diagrams
are directed graphs. Does that mean you can only support common
cardinality and not N-cardinality?
For example, suppose I want to implement a constraint that each Order
must have exactly one Invoice and each Invoice must have exactly one
Order. That seems like a reasonable business rule, even if it's a
slightly unusual one.
You say that cycles should not be permitted. I'm not clear if that is a
limitation of your model or if it is just a recommendation. Do you mean
that I *cannot* enforce my business rule that invoices and orders must
match one for one?
--
David Portas
Marshall Spight
>
> For example, suppose I want to implement a constraint that each Order
> must have exactly one Invoice and each Invoice must have exactly one
> Order. That seems like a reasonable business rule, even if it's a
> slightly unusual one.
If that is the business rule, then are not Invoices and Orders
the same entity?
Marshall
Gene Wirchenko
<marshal...@gmail.com> wrote:
No. (Trying substituting "husband" for "order" and "wife" for
"invoice".)
Sincerely,
Gene Wirchenko
Marshall Spight
Okay. Let me adjust my phrasing:
If that is the business rule, then are not Invoices and Orders
the same SQL table?
Marshall
-CELKO-
The problem is logical, not implementation. In order to get a
deterministic result, you would need to deter constraints in the same
order, every time. I suppose the right answer for my A-B-C example
would be to detect the cycle and do a ROLLBACK when they conflict, but
run the changes in other cases.
>> (It is also, dare I say, a glaring example of why in the 21st century we should be asking why we are still satisfied only to be able to make one incremental update to one row of one table at time.) <<
Unh? We can update a **subset** of a single base table at a time; that
is not a row at time. We did get BEGIN ATOMIC ... END; for
transactions and defered constraints with SQL-92, so you can do a lot
of things.
.
Gene Wirchenko
<marshal...@gmail.com> wrote:
They might be, but need not be. 1-1 relationships are possible.
Again, consider husband and wife.
I think the example is somewhat artificial. In the system that I
support at work, in general, an invoice has exactly one work order,
but a work order has UP TO one invoice. (The work order has been
invoiced, or it has not been invoiced.)
Sincerely,
Gene Wirchenko
Marshall Spight
> On 2 Mar 2006 15:35:41 -0800, "Marshall Spight" wrote:
> >
> >Okay. Let me adjust my phrasing:
> >
> >If that is the business rule, then are not Invoices and Orders
> >the same SQL table?
>
> They might be, but need not be. 1-1 relationships are possible.
> Again, consider husband and wife.
I'm not getting it. If we're talking about husband and wife in
a context in which there is exactly one-for-one always,
then we are talking about a table of married people. In
which case, why wouldn't I have HusbandBirthday, WifeBirthday,
etc.? In fact, I don't really see how the particular domain
is even relevant; one can use cardinality relationships
alone to make these decisions. If we didn't, we could
just as well split every table of n attributes into n
tables; the attributes are one-for-one after all.
> I think the example is somewhat artificial.
Agreed.
> In the system that I
> support at work, in general, an invoice has exactly one work order,
> but a work order has UP TO one invoice. (The work order has been
> invoiced, or it has not been invoiced.)
Yes, that seems much more realistic. :-)
Marshall
Bob Stearns
If circular relationships are not to be allowed how do we model
something like: every set is owned by a user and every user has a last
visited set which may not be his. The ddl (DB2) is used is below. The
important FK constraints are highlighted.
N.B. The 'GO' lines ae added/needed by my front end.
CREATE TABLE IS3.SET_HEADERS (
USERID CHARACTER(8) NOT NULL,
SET_NAME CHARACTER(25) NOT NULL,
DESCRIPTION VARCHAR(250),
DATE_CREATED DATE NOT NULL DEFAULT CURRENT DATE,
DATE_LAST_USED DATE NOT NULL DEFAULT CURRENT DATE,
CREATION_LOCN INTEGER NOT NULL,
SHARED CHARACTER(1),
SQL_CODE VARCHAR(3500),
INACTIVE CHARACTER(1),
PRIMARY KEY(USERID,SET_NAME)
)
GO
ALTER TABLE IS3.SET_HEADERS
ADD CONSTRAINT INACTIVE
CHECK (INACTIVE in ('Y','N'))
GO
ALTER TABLE IS3.SET_HEADERS
ADD CONSTRAINT SHARED
CHECK (SHARED in ('Y','N'))
GO
-------------------------------------------This one-------------------
ALTER TABLE IS3.SET_HEADERS
ADD CONSTRAINT USERID_FK
FOREIGN KEY(USERID)
REFERENCES IS3.USERS(USERID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
----------------------------------------------------------------------
ALTER TABLE IS3.SET_HEADERS
ADD CONSTRAINT CREATION_LOCN_FK
FOREIGN KEY(CREATION_LOCN)
REFERENCES IS3.LOCATIONS(LOC_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
CREATE INDEX IS3.PRIMARY_SET_H
ON IS3.SET_HEADERS(USERID, SET_NAME)
GO
CREATE TABLE IS3.USERS (
USERID CHARACTER(8) NOT NULL,
PASSWD CHARACTER(8) NOT NULL,
EMPLOYER_ID INTEGER NOT NULL,
MAX_LOC INTEGER NOT NULL,
PERSONAL_ID INTEGER,
LAST_LOC INTEGER,
LAST_SET_NAME CHARACTER(25),
LAST_SET_USERID CHARACTER(8),
PRIMARY KEY(USERID)
)
GO
-----------------------------And this one------------------------------
ALTER TABLE IS3.USERS
ADD CONSTRAINT USR_LASTS_FK
FOREIGN KEY(LAST_SET_USERID, LAST_SET_NAME)
REFERENCES IS3.SET_HEADERS(USERID, SET_NAME)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
-----------------------------------------------------------------------
ALTER TABLE IS3.USERS
ADD CONSTRAINT USR_PRES_FK
FOREIGN KEY(PERSONAL_ID)
REFERENCES IS3.ENTITIES_PUB(ENTITY_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
ALTER TABLE IS3.USERS
ADD CONSTRAINT USR_EMP_FK
FOREIGN KEY(EMPLOYER_ID)
REFERENCES IS3.CONTROLLERS(CONTROLLER)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
ALTER TABLE IS3.USERS
ADD CONSTRAINT USR_LASTL_FK
FOREIGN KEY(LAST_LOC)
REFERENCES IS3.LOCATIONS(LOC_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
GO
Brian Selzer
possible to contain one in a single relation, provided the set of attributes
in the referenced relation in each segment of the circular relationship is a
candidate key, because candidate keys are logically equivalent. For
example, if A and B are distinct candidate keys for a relation R, then by
definition A --> B and B --> A hold, which means that if a and b are values
for A and B respectively in a tuple of R, a implies b and b implies a,
therefore, a iff b. So, if you have three relations, R{A, B}, S{B, C} and
T{C, A} where A is a candidate key for R, B for S, and C for T, then by
definition,
A --> B, B --> C and C --> A.
Assuming that the circular relationship is defined as follows:
R(B) --> S(B), S(C) --> T(C), T(A) --> R(A).
Then for any set of values, a, b and c for A, B, and C respectively,
a implies b, b implies c and c implies a.
Because implication is transitive,
a implies c, b implies a and c implies b.
Consequently, a iff b iff c.
Therefore, a relation U{A, B, C} is equivalent to the above three relations
in the presence of the circular relationship iff A, B, and C are each
candidate keys for U.
Note that any additional attributes in R, S, and T come along for the ride,
meaning that the heading of U is the union of the heading of R, S and T.
I must emphasize that the set of attributes in the referenced relation in
each segment of the circular relationship must be a candidate key. In other
words, each segment must be a foreign key constraint. If one or more of the
segments is an inclusion dependency, which does not require that the set of
attributes in the referenced relation be a candidate key, then it may not be
possible to contain the relationship in a single relation.
"Volker Hetzer" <volker...@ieee.org> wrote in message
news:du4k8t$te7$1...@nntp.fujitsu-siemens.com...
> Hi!
> Just in general, are circular relationships something that
> can always be avoided?
> Or, given a model with a circular relationship, possibly
> spanning several tables, is there a way to get rid of them?
>
> Lots of Greetings and thanks!
> Volker
Alexandr Savinov
What do you mean by "cardinality and not N-cardinality"?
Concept graph is a directed *acyclic* graph.
> For example, suppose I want to implement a constraint that each Order
> must have exactly one Invoice and each Invoice must have exactly one
> Order. That seems like a reasonable business rule, even if it's a
> slightly unusual one.
Theoretically any cycle can be represented indirectly by using an
additional common subconcept. In this case we can do it as follows:
Order Invoice
| |
OrderInvoice
Now for each order there as an invoice and vice verse (we need of course
a constraint to enforce one-to-one relationship). Then for the database
system (which is able to maintain and make use of such a graph) this
structure is unambiguous.
In practice however such an implementation is frequently too expensive.
In particular, loops (self-references) can be easily permitted.
Equivalent relationship such as between Orders and Invoices can also be
permitted provided that there is a concrete interpretation (operational
semantics) for them. The main problem is that the database needs to know
how to manage such relationships. In particular, what to do if you
delete an order or an invoice. How to propagate these operations?
> You say that cycles should not be permitted. I'm not clear if that is a
> limitation of your model or if it is just a recommendation. Do you mean
> that I *cannot* enforce my business rule that invoices and orders must
> match one for one?
Yes, the absence of cycles is a deliberate and one of the main
properties of the concept-oriented model. It is however can be viewed as
a recommendation or a general pattern for data modeling (cycles are evil).
You can enforce your business rule that your data items are mutually
*related*. However, you are not allowed to mutually *reference* items.
In other words, cycles cannot be implemented via references while
references are managed by the database system and they have a built-in
semantics for them. However, you can implement cycles and any other
arbitrary relationships (according to your problem domain and its
business rules) using references. (Please, notice again that loops and
some special kinds of cycles should be permitted for performance and
convenience of use reasons.)
You always say that you do not want to adapt your business rules to any
data model. And in the next moment you adapt it to SQL query language. I
think we always adapt the real world to some model or conventions. Why I
have to use classes in OOP if I could use assembly language which much
more powerful and has almost no limitations? According to your logic I
have to assembly language because I am able to directly implement
whatever an expert in the problem domain says. Having no cycles in COM
is a constraint which allows us to automate many task on data management
and analysis. When the database system knows that the model has no
cycles it can do many such tasks automatically. Otherwise we have to do
it ourselves. That is the main argument. Of course, we loose some
flexibility and freedom however we also loose the freedom of making
errors because only a small portion of specialists can write correct SQL
queries and design correct schema.
I suppose that you mix two issues:
1. a formal model, and
2. using this model to model a graph (with cycles) or any other problem
domain specific structure.
The first is what your database will manage and what it knows. The
second is what your database does not know and does not care of. It is
your responsibility to maintain consistency of your graph. In
particular, in the concept-oriented model you can model easily any
graph, hypergraph or any other structure - the database will simply not
know about that. Edges are common subconcepts for nodes. COM will manage
only its acyclic directed graph of concepts.
vc
Brian Selzer wrote:
>[...]
> if a and b are values
> for A and B respectively in a tuple of R, a implies b and b implies a,
> therefore, a iff b.
That does not make any obvious sense. What do you mean by '[value] a
implies [value] b' ? Are you considering only the Boolean domain ?
Neither does that:
[...]
>...for any set of values, a, b and c for A, B, and C respectively,
>
> a implies b, b implies c and c implies a.
>
[...]
Gene Wirchenko
<marshal...@gmail.com> wrote:
>Gene Wirchenko wrote:
>> On 2 Mar 2006 15:35:41 -0800, "Marshall Spight" wrote:
>> >
>> >Okay. Let me adjust my phrasing:
>> >
>> >If that is the business rule, then are not Invoices and Orders
>> >the same SQL table?
>>
>> They might be, but need not be. 1-1 relationships are possible.
>> Again, consider husband and wife.
>
>I'm not getting it. If we're talking about husband and wife in
>a context in which there is exactly one-for-one always,
>then we are talking about a table of married people. In
No. The husband and wife exist as separate entities and may be
referred to individually. There may *also* be a table reflecting the
marriages.
>which case, why wouldn't I have HusbandBirthday, WifeBirthday,
>etc.? In fact, I don't really see how the particular domain
Because you probably want the birthdate of the person regardless
of the role the person has in a marriage. People who are not married
have birthdates, too.
>is even relevant; one can use cardinality relationships
>alone to make these decisions. If we didn't, we could
>just as well split every table of n attributes into n
>tables; the attributes are one-for-one after all.
[snip]
Sincerely,
Gene Wirchenko
Marshall Spight
> On 2 Mar 2006 18:51:01 -0800, "Marshall Spight"
> <marshal...@gmail.com> wrote:
> >
> >I'm not getting it. If we're talking about husband and wife in
> >a context in which there is exactly one-for-one always,
> >then we are talking about a table of married people. In
>
> No. The husband and wife exist as separate entities and may be
> referred to individually. There may *also* be a table reflecting the
> marriages.
>
> >which case, why wouldn't I have HusbandBirthday, WifeBirthday,
> >etc.? In fact, I don't really see how the particular domain
>
> Because you probably want the birthdate of the person regardless
> of the role the person has in a marriage. People who are not married
> have birthdates, too.
This line of reasoning doesn't work for me at all. The question
was whether to put data items which are exactly-one-to-exactly-one
in the same table; it is not relevant to argue that you might not
want to do so because they might not be exactly-one-to-exactly-one.
Marshall
David Portas
> Now for each order there as an invoice and vice verse (we need of course
> a constraint to enforce one-to-one relationship).
So you DO allow constraints that are cyclical. This constraint (called
"N-cardinality", where N=1) applies "in both directions". In a
conventional ER diagram this would be shown as a cycle. Your diagrams
on the other hand don't seem to model constraints - they show something
else that isn't clear to me. That something else doesn't have any
cycles.
The OPs original question was specifically about cycles in referential
integrity constraints, which you do say you need in your model (you
just don't draw pictures of them).
--
David Portas
Gene Wirchenko
<marshal...@gmail.com> wrote:
If the persons table has information on both married and
unmarried persons and there is a table about marriages, where do you
think the brithdates should go?
To me, putting it in the marriages table is ludicrous. An
unmarried person has a birthdate.
Sincerely,
Gene Wirchenko
Marshall Spight
> >
> >This line of reasoning doesn't work for me at all. The question
> >was whether to put data items which are exactly-one-to-exactly-one
> >in the same table; it is not relevant to argue that you might not
> >want to do so because they might not be exactly-one-to-exactly-one.
>
> If the persons table has information on both married and
> unmarried persons and there is a table about marriages, where do you
> think the brithdates should go?
If the persons table has information on both married and
unmarried persons, then birthdate is not something that
is exactly-one-to-exactly-one, and the example is not
relevant to the question at hand.
Marshall
Gene Wirchenko
<marshal...@gmail.com> wrote:
It is one-to-one to the person table, and it is relevant where
such a piece of data should go, just as a piece of data that is 1-1
with the marriage table would go there (e.g. data of marriage).
Sincerely,
Gene Wirchenko
Jan Hidders
> Brian Selzer wrote:
>
>>[...]
>>if a and b are values
>>for A and B respectively in a tuple of R, a implies b and b implies a,
>>therefore, a iff b.
>
>
> That does not make any obvious sense. What do you mean by '[value] a
> implies [value] b' ? Are you considering only the Boolean domain ?
I think he means with "implies" something like "functionally
determines", and with "iff" something like "has a one-to-one
relationship with".
-- Jan Hidders
Brian Selzer
"vc" <bost...@hotmail.com> wrote in message
news:1141397694....@t39g2000cwt.googlegroups.com...
>
> Brian Selzer wrote:
>>[...]
>> if a and b are values
>> for A and B respectively in a tuple of R, a implies b and b implies a,
>> therefore, a iff b.
>
> That does not make any obvious sense. What do you mean by '[value] a
> implies [value] b' ? Are you considering only the Boolean domain ?
>
A key is a set of attribute values, or facts. A set of facts is a
proposition in conjuctive normal form. Therefore, a functional dependency
is essentially a logical implication. For example, assume that a tuple in a
relation with the attributes A, B, and C has values a, b, and c
respectively. Then the following statements are true: A has value a. B has
value b. C has value c. If {A, B} is a candidate key, then the statement "A
has value a, and B has value b." implies the statement "C has value c."
because by definition, the value of a candidate key determines all other
values in a tuple.
David Cressey
"Gene Wirchenko" <ge...@ucantrade.com.NOTHERE> wrote in message
news:61gh02d8g5jvvbfue...@4ax.com...
What modifications does the data model need for use in Massachusetts?
David Cressey
"Jan Hidders" <jan.h...@REMOVETHIS.pandora.be> wrote in message
news:cxdOf.293694$Hj5.9...@phobos.telenet-ops.be...
David Cressey
"Jan Hidders" <jan.h...@REMOVETHIS.pandora.be> wrote in message
news:cxdOf.293694$Hj5.9...@phobos.telenet-ops.be...
I think "A implies B" is the same as "B or not A".
>
> -- Jan Hidders
JOG
[snip]
>
> I think "A implies B" is the same as "B or not A".
>
? By "A implies B", he surely just means "if A then B". Or using
standard predicate logic notation "A -> B."
JOG
[snip]
> Then the following statements are true: A has value a. B has
> value b. C has value c. If {A, B} is a candidate key, then the statement "A
> has value a, and B has value b." implies the statement "C has value c."
> because by definition, the value of a candidate key determines all other
> values in a tuple.
or rather from his example, the proposition would be: A(a) ^ B(b) ->
C(c)
vc
Brian Selzer wrote:
> "vc" <bost...@hotmail.com> wrote in message
> news:1141397694....@t39g2000cwt.googlegroups.com...
> >
> > Brian Selzer wrote:
> >>[...]
> >> if a and b are values
> >> for A and B respectively in a tuple of R, a implies b and b implies a,
> >> therefore, a iff b.
> >
> > That does not make any obvious sense. What do you mean by '[value] a
> > implies [value] b' ? Are you considering only the Boolean domain ?
> >
>
> A key is a set of attribute values, or facts. A set of facts is a
> proposition in conjuctive normal form.
It's not, but let's pass on it.
>Therefore, a functional dependency
> is essentially a logical implication. For example, assume that a tuple in a
> relation with the attributes A, B, and C has values a, b, and c
> respectively. Then the following statements are true: A has value a. B has
> value b. C has value c. If {A, B} is a candidate key, then the statement "A
> has value a, and B has value b." implies the statement "C has value c."
> because by definition, the value of a candidate key determines all other
> values in a tuple.
So what you are trying to say is that since, e.g., X=10 and Y=10, we
can write (X=10) <--> (Y=10). So why do you use the fancy notation to
express such trivial idea ?
Now, with your three relations R, S, T you have six sets representing
columns A, B, C:
Ar, Br, As, Bs, At, Bt.
So while it's true that, say, As=Bs, it's not generally true that
Br=Bs unless you stipulate so. Therefore,
"Consequently, a iff b iff c."
still does not make any obvious sense.
David Cressey
"JOG" <j...@cs.nott.ac.uk> wrote in message
news:1141480471....@e56g2000cwe.googlegroups.com...
I don't get it. How is "A -> B" different from "B^~A" ?
>
JOG
Hi David. Bit confused by your response. Originally you were talking
about "B or not A" but then talk about "B^~A" which is "B and not A".
(I'll take the second as a typo). The distinction is:
A -> B is a proposition
B v ~A is a boolean expression
The first is a declared fact, the second just a boolean statement that
resolves to true or false, obviously a very different kettle of fish.
So I thought that you might perhaps have meant "B v ~A = True", but
substituting a couple of values in for A and B highlights there still
exists a difference:
A -> B
IF it is raining THEN I wear a coat
B OR ~A = True
EITHER I wear a coat OR it is NOT raining
These are clearly very different things. For example, in the second
statement I have declared that I will not be wearing a coat if it is
dry but very cold outside, however this would be a perfectly acceptable
state of affairs according to the first statement.
All best, Jim.
David Cressey
> David Cressey wrote:
> > "JOG" <j...@cs.nott.ac.uk> wrote in message
> > news:1141480471....@e56g2000cwe.googlegroups.com...
> > > David Cressey wrote:
> > > [snip]
> > > >
> > > > I think "A implies B" is the same as "B or not A".
> > > >
> > >
> > > ? By "A implies B", he surely just means "if A then B". Or using
> > > standard predicate logic notation "A -> B."
> >
> > I don't get it. How is "A -> B" different from "B^~A" ?
> >
>
> Hi David. Bit confused by your response. Originally you were talking
> about "B or not A" but then talk about "B^~A" which is "B and not A".
> (I'll take the second as a typo). The distinction is:
>
You are right. It was a typo. Thanks for correcting it.
> A -> B is a proposition
> B v ~A is a boolean expression
>
> The first is a declared fact, the second just a boolean statement that
> resolves to true or false, obviously a very different kettle of fish.
It may be obvious to you, but it ain't obvious to me. It seems to me that
the assertion
that A -> B is always true in a given univers of discourse, and the
assertion that
B is true or A is false in the same universe of discourse boil down to the
same thing.
> So I thought that you might perhaps have meant "B v ~A = True", but
> substituting a couple of values in for A and B highlights there still
> exists a difference:
>
> A -> B
> IF it is raining THEN I wear a coat
>
> B OR ~A = True
> EITHER I wear a coat OR it is NOT raining
>
> These are clearly very different things. For example, in the second
> statement I have declared that I will not be wearing a coat if it is
> dry but very cold outside, however this would be a perfectly acceptable
> state of affairs according to the first statement.
>
I don't think the above analysis is correct. Your analysis requires OR to
mean "exclusive or".
It doesn't. It means inclusive or, doesn't it?
In the case where B = I will wear a coat and A= it is raining
Then if I wear a coat and it's not raining we get:
B v ~A becomes True v not false becomes True v true becomes
true. Right?
> All best, Jim.
>
vc
JOG wrote:
[...]
> The distinction is:
>
> A -> B is a proposition
> B v ~A is a boolean expression
'A->B' is exactly the same as 'B or not(A)' as can easily be seen
from their truth tables.
Both can be called either 'propositions' or 'Boolean [logic]
expressions'.
The explantion below does not make any obvious sense.
Alexandr Savinov
> Alexandr Savinov wrote:
>
>> Now for each order there as an invoice and vice verse (we need of course
>> a constraint to enforce one-to-one relationship).
>
> So you DO allow constraints that are cyclical. This constraint (called
> "N-cardinality", where N=1) applies "in both directions". In a
> conventional ER diagram this would be shown as a cycle. Your diagrams
> on the other hand don't seem to model constraints - they show something
> else that isn't clear to me. That something else doesn't have any
> cycles.
Concept graph visualizes what the database will manage and nothing else.
It represents the formal model. This can be viewed as a reference
structure: if A reference B then we draw an upward arrow from A to B.
You are probably talking about informal relationships in the sense of ER
model. Since these relationships are not managed by the database they
may have any structure including cycles.
> The OPs original question was specifically about cycles in referential
> integrity constraints, which you do say you need in your model (you
> just don't draw pictures of them).
The question was how to get rid of cycles.
The problem however is that there exist different types of cycles.
Informally we can (and we should) represent all of them. We can draw
them like it is done in ER model. I am talking about very concrete type
of cycles, namely, cycles that are known to the database and implemented
via references. And for this type of relationship implemented via
references we have to prohibit cycles in order to get significant
advantages in automating data management. All other types of
relationships that are not managed by the database may have any
desirable structure and constraints. Thus we may and we need to model
cycles and even more complex relationships but we have to do it without
cycles in reference structure. Why? Because the database will not be
able to manage such data (only trivial operations are possible). With no
cycles the database is able to manage the data as a global interrelated
structure.
Brian Selzer
>
> Brian Selzer wrote:
>> "vc" <bost...@hotmail.com> wrote in message
>> news:1141397694....@t39g2000cwt.googlegroups.com...
>> >
>> > Brian Selzer wrote:
>> >>[...]
>> >> if a and b are values
>> >> for A and B respectively in a tuple of R, a implies b and b implies a,
>> >> therefore, a iff b.
>> >
>> > That does not make any obvious sense. What do you mean by '[value] a
>> > implies [value] b' ? Are you considering only the Boolean domain ?
>> >
>>
>> A key is a set of attribute values, or facts. A set of facts is a
>> proposition in conjuctive normal form.
>
> It's not, but let's pass on it.
>
An attribute value is a factual statement. For example, "Attribute A has
value 10." Perhaps you use a different definition for conjuctive normal
form, or maybe I should have reversed the order: a proposition in
conjunctive normal form is a set of facts. I guess you could quibble over
whether or not a fact is a statement. I guess that's one way to look at it,
since a proposition is a stated fact, or rather a statement of fact,
emphasizing the act of declaration. In that case, I should change the
wording: A key is a set of attribute values, or factual statements. A
proposition in conjuctive normal form is a set of factual statements.
>>Therefore, a functional dependency
>> is essentially a logical implication. For example, assume that a tuple
>> in a
>> relation with the attributes A, B, and C has values a, b, and c
>> respectively. Then the following statements are true: A has value a. B
>> has
>> value b. C has value c. If {A, B} is a candidate key, then the statement
>> "A
>> has value a, and B has value b." implies the statement "C has value c."
>> because by definition, the value of a candidate key determines all other
>> values in a tuple.
>
> So what you are trying to say is that since, e.g., X=10 and Y=10, we
> can write (X=10) <--> (Y=10). So why do you use the fancy notation to
> express such trivial idea ?
>
> Now, with your three relations R, S, T you have six sets representing
> columns A, B, C:
>
> Ar, Br, As, Bs, At, Bt.
>
> So while it's true that, say, As=Bs, it's not generally true that
> Br=Bs unless you stipulate so. Therefore,
>
> "Consequently, a iff b iff c."
>
> still does not make any obvious sense.
>
Because there are functional dependencies A --> B, B --> C and C --> A in R,
S, and T respectively and foreign key constraints R(B) --> S(B), S(C) -->
T(C) , T(A) --> R(A), the existence of a tuple in R requires that exactly
one tuple exists in S that has the same value for attribute B, the existence
of that tuple in S requires that exactly one tuple exists in T that has the
same value for attribute C, and the existence of that tuple in T requires
that exactly one tuple exists in R that has the same attribute value for A.
Therefore, since the value for A determines the value for B and the value
for B determines the value for C then the value for A determines the value
for C, so in T, both A and C must be candidate keys. It follows then that
in R, both A and B must be candidate keys, and in S, both B and C must be
candidate keys. This means that a 1:1:1 relationship must exist between
tuples in R, S, and T.
David Portas
You are so completely wrong about constraints as far as the relational
model and ERD is concerned. An ER diagram represents a set of entities
with:
A) entity integrity constraints (AKA candidate keys)
B) referential integrity constraints (AKA foreign keys)
There is NOTHING "informal" about referential constraints (in RM). They
are ALWAYS managed and enforced by the database management system
(again, in RM).
In any case you have clearly explained that your concept graphs do not
represent constraints and therefore I assume constraints with cycles
are not eliminated or forbidden by your model. You seem to be
indicating that constraints are not part of "concept oriented" AT ALL,
which has to be a massive deficit when compared to RM. But thanks for
the explanation. I think I've had the answer I was waiting for.
--
David Portas
Alexandr Savinov
And how they are managed, could you explain? The only thing it can do is
to execute queries and constraints. In other words, without this
information the database knows almost nothing about data. But it is
already another issues so let us return to our cycles.
> In any case you have clearly explained that your concept graphs do not
> represent constraints and therefore I assume constraints with cycles
> are not eliminated or forbidden by your model. You seem to be
> indicating that constraints are not part of "concept oriented" AT ALL,
> which has to be a massive deficit when compared to RM. But thanks for
> the explanation. I think I've had the answer I was waiting for.
You always try to make global conclusions like the world is unfair and
some model is bad. Let us consider the following example:
an Order references one customer (who made this order)
a Item reference one order it belongs to
a Customer reference one item he wants to be delivered first (for some
reason)
We have a cycle. There are two opinions:
1. That is quire normal and there is nothing bad here. Moreover, it is
frequently used pattern.
2. That has to be prohibited because it not only prevents us from
developing "good" formal properties but also is a rare and unusual
situation in practice.
You insist that the first option is right while I prefer the second
option. Here are my arguments:
- cycles reflect infinite containment relation
- cycles produce infinite dimensions
- cycle produce problems in cascaded operations
- cycles produce problems with grouping, aggregation and inference
Notice that it has nothing to do with constraints imposed on data and
implementing cycles indirectly. We still can impose any constraints just
as in RM. The only difference is that we avoid cycles in referencing.
> --
> David Portas
>
David Portas
>
> Notice that it has nothing to do with constraints imposed on data and
> implementing cycles indirectly. We still can impose any constraints just
> as in RM. The only difference is that we avoid cycles in referencing.
>
This is indeed the difference. In RM, foreign key references ARE
constraints. There is no other type of reference in the model. Cyclical
references are not implemented "indirectly" - In RM they are direct and
explicit and are required for reasons of integrity. You still have the
same cycles in constraints but you call them "indirect" for some
reason.
--
David Portas
vc
Brian Selzer wrote:
[...]
>the existence of a tuple in R requires that exactly one tuple exists in S
... but it does not mean at all that set Br is the same as set Bs, so
what is the biconditional 'iff' doing here ? Or if you are into fancy
notation, it is true that
forall b (b in Br --> b in Bs)
but not necessarily true that
forall b1 forall b2 (b1 in Br <--> b2 in Bs)
unless you stipulate so.
JOG
*cough* David, you (and vc) are of course completely correct. A -> B is
(just obviously) identical to A v ~B. Rereading my post, I'm left
scratching my head as to what on earth I was wittering on about. Well,
quite frankly I appear to be having a nonsense day in general,
mistaking trivial mathematics being the least of it. Note to self: Must
cut down the booze on friday nights.
Brian Selzer
forall b (b in Br --> b in Bs) is true and
forall b (b in Bs --> b in Br) is also true, and must be because of the
circular foreign key relationship.
In other words, a database schema consisting of the relation schemata,
R{A, B}, S{B, C}, and T{C, A}
and the foreign key constraints,
R(B) --> S(B), S(C) --> T(C), T(A) --> R(A)
is equivalent to a database schema consisting of the same relation schemata
and the foreign key constraints,
R(A) --> T(A), T(C) --> S(C), S(B) --> R(B)
is equivalent to a database schema consisting of the relation schema
U(A, B, C) where A, B, and C are candidate keys.
"vc" <bost...@hotmail.com> wrote in message
news:1141515292.6...@z34g2000cwc.googlegroups.com...
vc
Brian Selzer wrote:
> If I follow your notation,
>
> forall b (b in Br --> b in Bs) is true and
> forall b (b in Bs --> b in Br) is also true, and must be because of the
> circular foreign key relationship.
Right, I forgot about the relationship being circular. In this case,
you are aboslutely right -- all the sets will be of the same size.
[...]
> >
Jan Hidders
>
> In other words, a database schema consisting of the relation schemata,
>
> R{A, B}, S{B, C}, and T{C, A}
>
> and the foreign key constraints,
>
> R(B) --> S(B), S(C) --> T(C), T(A) --> R(A)
>
> is equivalent to a database schema consisting of the same relation schemata
> and the foreign key constraints,
>
> R(A) --> T(A), T(C) --> S(C), S(B) --> R(B)
>
> is equivalent to a database schema consisting of the relation schema
>
> U(A, B, C) where A, B, and C are candidate keys.
Actually, none of them are equivalent. Consider the following database
instances: (formatted assuming fixed width font)
R : A B S : B C T : C A
--- --- ---
1 2 2 3 3 1
4 3
This is an instance of the first schema, but not of the second. A
similar example shows thre is an instance of the second schema that is
not an instance of the first schema:
R : A B S : B C T : C A
--- --- ---
1 3 3 2 2 1
3 4
Finally, it is clear that these two instances cannot be represented in
the third schema since the join would lose tuples. So they are really
all three different.
What *is* true is that the following schema:
- R(A,B), with cand. keys {A} and {B}
- S(B,C), with cand. keys {B} and {C}
- T(C,A), with cand. keys {C} and {A}
- foreign keys:
R[B] -> S[B], S[B] -> R[B],
S[C] -> T[C], T[C] -> S[C],
T[A] -> R[A], R[A] -> T[A]
Is equivalent with the schema:
- U(A,B,C) with cand. keys {A}, {B} and {C}
So perhaps that is what you meant?
-- Jan Hidders
vc
Jan Hidders wrote:
> Brian Selzer wrote:
> >
> > In other words, a database schema consisting of the relation schemata,
> >
> > R{A, B}, S{B, C}, and T{C, A}
> >
> > and the foreign key constraints,
> >
> > R(B) --> S(B), S(C) --> T(C), T(A) --> R(A)
> >
> > is equivalent to a database schema consisting of the same relation schemata
> > and the foreign key constraints,
> >
> > R(A) --> T(A), T(C) --> S(C), S(B) --> R(B)
> >
> > is equivalent to a database schema consisting of the relation schema
> >
> > U(A, B, C) where A, B, and C are candidate keys.
>
> Actually, none of them are equivalent. Consider the following database
> instances: (formatted assuming fixed width font)
>
> R : A B S : B C T : C A
> --- --- ---
> 1 2 2 3 3 1
> 4 3
>
Re-reading the original message I think your objection is valid since
Brian apparently claimed that *any * set of tables with circular
foreign key constraints is equivalent to U which is only the case if
the foreign keys involved were also candidate keys as you mentioned
below.
Brian Selzer
erroneously discarded the notion that a spiral relationship could exist
because the functional dependencies can no longer be functional dependencies
if the determinant implies one value directly and a different value
transitively. In order for the relationship to be circular and not spiral,
both attributes in each relation that participates in the foreign key
constraints must also be candidate keys.
Thank you for pointing that out.
"Jan Hidders" <jan.h...@REMOVETHIS.pandora.be> wrote in message
news:gGAOf.296091$IR1.9...@phobos.telenet-ops.be...