Re: Relation-Entiry Diagram Question/Problem

I don't think you can express what you want just with an ER model and some constraints. This is how I'd model it:

Document                          |                                       |
Statement
    DocumentID (PK)         |                                       |
DocumentID(PK)
                                         | ------------------------<|
StatementID (PK)
                                         |
|            AmendsDocumentID(FK)
 
|            AmendsStatementID(FK)

You can have a constraint that the pair (AmendsDocumentID,AmendsStatementID) are references to the Statement table.

Then you could verify with insertion or update triggers (can you use triggers?) that:
1. No two Statments for the same DocumentID have different AmendsDocumentID 2. The are no statements for the document "AmendsDocumentID" that amends another document. (To avoid ammendments of ammendments).

To query the statement in a document and its ammendments you could do:

Select <whatever you need>
from Statement
where DocumentID = "MyDocument"
or AmendsDocumentID = "MyDocument"

Let me know if this helps,

Vicho.

david.lightstone <david.lightstone_at_prodigy.net> wrote in message news:98g9fi$2eu2$1_at_newssvr06-en0.news.prodigy.com...
> I am trying to describe/document a database
> that I have implemented, and don't quite know how to proceed
>
> The entities are
> (1) Statements
> (2) Documents
> (3) Document Amendment
> (4) Statement Corrections
>
> Relationships are
> (1) Documents contain statements
> (2) One document can amend another document,
> but a document used as an amendment itself can not be amended
> (3) Statement Corrections are made under the authority of a Document
> Amendment
> A statement correction implies a relationship between a statement of the
> amended document
> and a statement of the amending document
>
> I think the following is incorrect
>
> ............................
> ***** . Statements . ******
> * ............................ *
> * * *
> * * *
> * ............................ *
> * . Documents . *
> * ............................ *
> * * * *
> * * * *
> * ............................ *
> * . Document . *
> * . Amendments . *
> * ............................ *
> * * *
> * * *
> * ............................ *
> * . Statement . *
> ***** . Corrections . ******
> ............................
>
> because it implies any statement can amend any other statement
> (which is most definitely not the intent)
>
> In the actual implementation there is a query named
> Statements_Of_Document
>
> The query serves to identify the candidate statements for the
> amended statement and the amending statement in the
> Statement Correction Relationship
>
> How do I identify the constraint on the Statement Corrections
 relationship.
>
> I am inclined to treat Statements_Of_Document as an entity, even though
> it is redundant.
>
>
>
>
Received on Tue Mar 13 2001 - 05:38:23 CET