How to translate high level entity relationship into schema

database-designerd

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?

Best 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'

Related Question