Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

According to the following articles written by Dr. Chan, we can describe high level entity relationship. (see page-10,11 of doc-A and page9 of doc-B)

•doc-A) English Sentence Structure and Entity-Relationship Diagram.

http://www.maltercorp.com/DrPeterChen/english.pdf

•doc-B) English, Chinese and ER diagrams.

http://www.csc.lsu.edu/~chen/pdf/ER_C.pdf

These articles said ERD is translated from English sentence like below.

-- The product is shipped to the customer.

-- This action “Shipping” is performed by the clerk.

Product shipping

Then think about another example.

-- The customer is identified their bank card.

-- This action “identifying” is Authenticated by the bank.

-- The bank issues the "bank card" to their "customer" with similar interpretation.

bank-customer relationship

Now my questions are:

  1. How can I express this situation with an ERD?

    Relationship “issue” has possibility to connect with two entities "Bank card" or
    "customer".

    But I don't want to use ternary relationship as a restriction.

  2. How can I translate those entities mixed with different dimensions to a database schema?

  3. Basically, is it possible to connect to the entity at another level ?

  4. Are there any good tools for describing high level entity relationship and generating DDL?

share|improve this question
    
What is the problem here? You are not allowed to make a ternary relationship? –  ypercube May 14 '12 at 6:37
    
There is a kind of issue called “binary relationship vs. ternary relationship”. I’ve taken binary relationship in this case because ternary relationship is more complicated than binary relationship. –  user1345414 May 15 '12 at 1:22
add comment

1 Answer

The key to understand this case is at the ninth page from Doc-A:

"product" and "customer" are entity types, and "shipped to" is a relationship... the verb "ship" is then converted to a gerund... in other words, the relationship... has been converted into the entity type "shipping"

Hence, the final third form will have "shipped to" converted to an entity by itself, "shipping".

model at page 10 from Doc-A on its third normal form

This is necessary because "Shipped to" is a MANY-TO-MANY relationship (although to myself Chen's text is not clear on this).

But, your example does not relate - the relationship "Identified by" is an ONE-TO-MANY (a card belongs to ONE customer / a customer have ONE OR MORE cards issued).

Hence, the model would have the relationship between "Bank" and "Bank card" identified by TWO verbs.

user model on the third normal form, being 'Authenticated by' a single relationship

Or, making the model stronger, to register several authentication events, one may turn "Authenticated by" into a entity by itself, "Authenticating" (to register event date, data shared, etc.).

user model at the third normal form, transformin 'Authenticated by' to the entity 'Authenticating'

share|improve this answer
    
I think Bank card must be in rectangle not in rounded rectangle because Shipping in 1st diagram is gerund but Bank card in 2nd diagram is noun. –  user1345414 Oct 18 '13 at 6:15
    
Hm... my bad. Actually I'd better issue 'Authentication', not 'Authenticating'. Rounded rectangles here have no information value. They came rounded just because. I will review my post as soon as I get home. –  mvaraujo Oct 24 '13 at 20:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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