Re: Newbie question on cardinality in a data model

Hi Alan,

thank you so much for your answer.
The Framework Manager (which is the tool to construct the meta layer for reporting) lets you import tables (as "query subjects")from the database and establish relationships among them. These can be the same as in the relational schema, but can also be changed. I can also merge tables from the database to make new query subjects, and create query subjects with elements from various tables. So according to what you say, does this mean that I am drawing an ER Diagram based on a relational schema?

The reason why I am confused is the following: In the schema I received from the db colleagues there are several cases of so called "fan traps": this means that I have tables A,B,C drawn with the following relatioships:
A-->B 1-->N
A-->C 1-->N
B and C are not connected.
If I write an SQL query to retreive data from A,B,C through SQL*Plus I get the result I want.
If I reproduce the situation in my Framework Manager Model and execute a query with the reporting tool, it gives me a result that looks like a cartesian product. The tool splits the query in two SQL statements and combines them together.
If I then change in FM A-->B from 1-->N to 1-->1, then the result is correct. The tool writes only one SQL statement and gives the right result.

What I am really puzzled about is why would the tool write different SQL depending on the cardinality in my model? Shouldn't the SQL statement be the same? And if, to avoid the cartesian product, I set the cardinality in my model wrong, like in this case 1-->1, is it possible that I get wrong results?

Thanks again,

Anna

"Alan" <alan_at_erols.com> wrote in message news:<bnufts$15seoo$1_at_ID-114862.news.uni-berlin.de>...
> You are confusing "data model" with "relational schema". A data model can be
> expressed in a drawing known as an ERD (Entity Relationship Diagram). This
> is an abstract view of the data that is being modeled and the realtionships
> among the data. The data is modeled as entities and relationships among the
> entities. Cardinality is an important part of this model, as well as are
> participation constraints (mandatory/optional). Once this ERD is *properly*
> constructed, it is possible to convert it into a relational schema, which is
> the expression of how your tables will be layed out (sometimes called a
> "physical" model, though I find that a confusing term). Many people confuse
> the entities in an ERD with tables. They are not tables. Some may become
> tables, but others will combine with relationships and other enities to
> become tables in the "physical" model (relational scherma). Cardinality
> constraints help determine how the entities and relationships get combined
> into tables. There is actually an algorithm to follow to do this, though
> many people (even on these newsgroups) don't know about it.
>
> I am not familiar with Cognos Reportnet, but I am familiar with some of
> their other reporting tools (Impromptu, PowerPlay). I suspect Reportnet
> allows you to create reports from an ERD "view" of your data. End users are
> often comfortable with (or at least understand) ERDs, so it may be that this
> tool allows an end user to create reports from a diagram he/she may
> understand, rather than from a relational schema, which may be harder to
> understand.
>
>
> "Anna" <post_at_pallina.ch> wrote in message
> news:547b1c8.0310311130.6e68d3a4_at_posting.google.com...
> > Hi,
> >
> > could somebody please explain why it is so important in a data model
> > to specify the cardinality of a relationship?
> > From what I understand, if I query a database via SQL the statement I
> > write does not depend on the cardinality (with the exception of outer
> > joins) of a relationship. Yet my reporting tool (Cognos Reportnet)
> > requires me to set the cardinality of each relationship. Why is it
> > necessary, and what happens if I set it wrong? Any help or reference
> > to reading material is greatly appreciated!
> >
> > Thanks,
> >
> > Anna
Received on Sat Nov 01 2003 - 04:36:34 CST