How to translate high level entity relationship into schema?

What is an Entity Relationship Modeling?

• What is an Entity? and What is Entity Relationship Modeling? What are the goals of Entity Relationship Modeling?

• Answer:

  An entity is an abstraction of a database table. Put another way, an entity is the idea or concept that defines a table, but is not the same as its physical implementation in a given product or tool. Very often, 1 entity = 1 table, and to a great extent they are interchangeable in conversation. But, speaking very strictly, an entity is an abstraction while a table is a physical implementation. ER modeling is a diagramming notation for data models that uses boxes (and sometimes diamonds) for entities and lines that represent relationships. The boxes (and diamonds) have a name on them that identifies them--such as "Customer" or "Sales Order." Lines may be decorated with symbols or notation that indicate the type of relationship (one-to-one or one-to-many). Lines may also have explanatory text describing the kind of relationship. (Example: "One customer has many orders.") There are different line styles in use. Oracle (last I heard) uses "crow's foot" style which represents the many side of a one-to-many with three splayed lines that resemble a bird's foot. Martin style lines use double arrows to show many relationships. There are other line styles, too, but they all center around differentiating one-to-one and one-to-many relationships. Diamonds (or rounded edge boxes) are sometimes used for "associative" or "weak" entities. This means that they depend on other entites in order to exist. For example, a Sales Order may normally depend on a Customer to exist, so the Sales Order may be drawn as a "weak" entity. The goal of ER modeling is to communicate the intent or scope of a database design before it's implemented. The overall value of this is arguable, I think, but comes from a healthy impulse to communicate. The problem with ER modeling, in my opinion, is that it's either too abstract or it's too physical. So, ER modeling requires some knack or artistry to use effectively.