데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Database

ㆍDBMS

MySQL

PostgreSQL

Community

Admin

DBMS Columns 85 게시물 읽기

No. 85

Entity Relationship Modeling from an ORM Perspective: Part 1 -- By Dr. Terry Halpin
작성자	정재익(advance)	작성일	2001-11-19 20:58	조회수	4,085

원본 출처 : http://www.inconcept.com/JCM/December1999/halpin.html <hr noshade width=100%>

Entity

Relationship Modeling from an ORM Perspective: Part 1 -- By Dr. Terry

Halpin

Introduction

Entity Relationship modeling (ER) views the

application domain in terms of entities that have attributes and participate

in relationships. For example, the fact that an employee was born on a date

is modeled by assigning a birthdate attribute to the Employee entity type,

whereas the fact that an employee works for a department is modeled as a

relationship between them. This view of the world is quite intuitive, and in

spite of the recent rise of UML for modeling object-oriented applications,

ER is still the most popular data modeling approach for database

applications.

The ER approach was originally proposed by Peter Chen

in 1976, in the very first issue of an influential ACM journal [2]. As shown

in Figure 1, Chen's original notation used rectangles for entity types,

diamonds for relationships, and ellipses for attributes. The double ellipse

indicates unique identifier attributes, and the "n and "1"

indicate the relationship is many to one (each employee works for at most

one department, but many employees may work for the same department).

Figure 1 An early ER notation used by Chen

The direction in which relationship names are to be

read is formally undecided, unless we add some additional marks (e.g.

arrows) or rules (e.g. always read from left to right and from top to

bottom). For example, does the employee work for the department, or does the

department work for the employee? Although we can use our background

knowledge to informally disambiguate this example, it is quite common

nowadays to see ER models with relationships whose intended direction can

only be guessed at by anybody other than the model's creator. For example,

consider the impact of misreading the intended direction for the following:

Person killed Animal; Person is loved by Person. This problem is exacerbated

if the verb phrase used to name the relationship is shortened to one word

(e.g. "work", "love"), unfortunately still a fairly

common practice.

Chen's notation evolved over time. His current

ER-Designer tool uses hexagons instead of diamonds, and supports n-ary

relationships. Outside academia however, Chen's notation seems to be rarely

used nowadays, so I'll say no more about it here. One of the problems with

the ER approach is that there are so many versions of it, with no single

standard. In industrial practice, the most popular versions of ER are the

Oracle and Information Engineering (IE) notations. Another popular data

modeling notation is IDEF1X, but since this is a hybrid of ER and relational

notation, I don't count it as a true ER representative. As discussed in

earlier articles [3], UML class diagrams can be regarded as an extended

version of ER. The rest of this article focuses on basic aspects of the

Oracle notation for ER. Later articles will examine IE and IDEF1X.

Oracle ER: the basics

The Oracle ER notation has been used for many years in

CASE tools from Oracle Corporation, and the discussion here is based on the

classic treatment by Richard Barker [1]. Oracle's Object Designer tool now

supports UML as an alternative to its traditional ER notation. For database

applications, many modelers still prefer Oracle's ER notation in preference

to UML, and it will be interesting to see whether this changes over time.

Dave Hay, an experienced modeler and ardent fan of the Oracle ER notation,

argues that "there is no such thing as 'object-oriented analysis'"

[5], only object-oriented design, and that "UML is ?not suitable for

analyzing business requirements in cooperation with business

people"[6].

While I agree with Dave Hay that UML class diagrams

are less than ideal for data modeling, I feel that his preferred ER notation

shares some of UML's weaknesses in being attribute-based. As I've discussed

before in a UML context [3, 4], using attributes in a base conceptual model

adds complexity and instability, while making it harder to validate models

with domain experts using verbalization and sample populations. Attributes

are great for logical design, since they allow compact diagrams that

directly represent the data structures (e.g. relations or object-relations)

used for the actual design. However when I'm performing conceptual analysis,

I just want to know what the facts and rules are about the business, and I

want to communicate this information in sentences, so that the model can be

understood by the domain experts. I sure don't want to bother about how

facts are grouped into multi-fact structures. Whether some fact will end up

in the design as an attribute is not a conceptual issue to me. As Ron Ross

says, "Sponsors of business rule projects must sign off on the

sentences-not on graphical data models. Most methodologies and CASE tools

have this more or less backwards" [6, p.15]. The ORM reporting

facilities in Visio Enterprise allow the domain expert to inspect ORM models

fully verbalized into sentences with examples, making validation much easier

and safer.

Now that I've stated my bias up front, let's examine

the Oracle ER notation itself. The basic conventions are illustrated in

Figure 2. Entity types are shown as soft rectangles (rounded corners) with

their name in capitals. Attributes are written below the entity type name.

Some constraint information may appear before an attribute name. A

"#" indicates that the attribute is the primary identifier of the

entity type, or at least a component of its primary identification scheme. A

"*" or heavy dot "?quot; indicates the attribute is

mandatory (i.e. each instance in the database population of the entity type

must have a non-null value recorded for this attribute). A "?quot;

indicates the attribute is optional. Some modelers also use a period

"." to indicate the attribute is not part of the identifier.

Figure 2 The basic ER notation used by Oracle

Relationships are restricted to binaries (no unaries,

ternaries or longer relationships), and are shown as lines with a

relationship name at the end from which that relationship name is to be

read. This name placement overcomes the ambiguous direction problem

mentioned earlier. Both forward and inverse readings may be displayed for a

binary relationship, one on either side of the line. This makes the notation

superior to UML for verbalizing relationships.

From an ORM perspective, each end (or half) of a

relationship line corresponds to a role. Like ORM, Oracle ER treats role

optionality and cardinality as distinct, orthogonal concepts, instead of

lumping them together into a single concept (e.g. multiplicity in UML). A

solid line-half denotes a mandatory role, and a dotted line-half indicates

an optional role. For cardinality, a fork or crow's foot intuitively

indicates "many", by its many "toes". The absence of a

crow's foot intuitively indicates "one". The crow's foot notation

was invented by Gordon Everest, who originally used the term "inverted

arrow" but now just calls it a "fork". The basic

correspondence with the ORM notation for uniqueness and mandatory role

constraints is shown in Figure 3.

Figure 3 The ER diagram (a) is equivalent to the

ORM diagram (b)

If each of the two roles in a binary association may

be assigned one of optional/mandatory and one of many/one, there are sixteen

patterns. The equivalent Oracle ER and ORM diagrams for the first eight of

these cases are shown in Figure 4.

Figure 4 Some equivalent cases

The other eight cases are shown in Figure 5. Although

all eight are legal in ORM, the last case where both roles of a many:many

relationship are mandatory is considered illegal in Oracle.

Figure 5 Other equivalent cases

Ring associations that are illegal in Oracle are shown

in Figure 6(a). Although rare, they sometimes occur in reality, so should be

allowed at the conceptual level, as permitted in ORM. As an exercise, you

may wish to invent satisfying populations for the ORM associations in Figure

6 (b).

Figure 6 Illegal ring associations in Oracle (a)

that are rare but allowed in ORM (b)

In Oracle ER, a bar "|" across one end of a

relationship indicates that the relationship is a component of the primary

identifier for the entity type at that end. In Figure 7 for example,

Employee and Building have simple identifiers, but Room has a composite

reference scheme, being identified partly by its room number and partly by

the building in which it is included.

Figure 7 Room is identified by combining its

room nr and its relationship to Building

The use of identification bars provides some of the

functionality afforded by external uniqueness constraints in ORM. For

example, the schemas in Figure 8 are equivalent. The other attributes of

Room and Building in ORM would be modeled in ORM as relationships. ORM's

external uniqueness notation seems to me to convey more intuitively the idea

that each RoomNr, Building combination is unique (i.e. refers to at most one

room). But maybe I'm biased. At any rate, this constraint (as well as any

other graphic constraint) can be automatically verbalized in natural

language.

Figure 8 Composite identification in Oracle ER

(a) and ORM (b)

Some people misread the bar notation for composite

identification as a "1", since this is what the symbol means in

many other ER notations. But this isn't a problem if you don't have to work

with multiple versions of ER. The main problem with the bar notation is that

it doesn't generalize, since it can't be used for declaring composite

identification schemes involving only attributes (no relationships). Because

ORM always uses relationships instead of attributes, it doesn't have this

problem. As an example, suppose we wanted to model the information shown in

Table 1, as well as other facts about rooms.

<a name="_Ref469051116"><span style="mso-bidi-font-family:

Arial">Table 1</a>

A

simple data use case for room scheduling

<tr>

<td width="23%" bgcolor="#C0C0C0">ActivityCode</td>

<td width="59%" bgcolor="#C0C0C0">ActivityName</td>

</tr>

<tr>

<td width="59%">VisioModeler class</td>

</tr>

<tr>

<td width="59%">VisioModeler class</td>

</tr>

<tr>

<td width="59%">ActiveQuery demo</td>

</tr>

<tr>

<td width="59%">Staff party</td>

</tr>

<tr>

</tr>

</table>

The table suggests that rooms can be simply identified

by room numbers, so let's accept that. One way of modeling the situation in

Oracle ER is shown in Figure 9. Here the bar notation is used to show that

RoomTimeSlot is identified by combining its time and room number.

Figure 9 An ER diagram for room scheduling

The use of attributes in this model makes it hard to

verbalize and populate the schema for validation purposes. Moreover, there

is at least one constraint missing. Compare this with the populated ORM

model for the same situation. Here the facts are naturally verbalized as a

ternary (Room at Time is used Activity) and a binary (Activity has

ActivityName). The associated fact tables include the original facts, as

well as counter-facts (italicized) to test the constraints. The first

counter-row (20, Mon 9 am, AQD) tests the uniqueness constraint that a room

at a time is used for at most one activity. The second counter-row tests the

uniqueness constraint that at most one room can be used for a given activity

at a given time. This constraint may well be wrong, but at least we can

express it and test it in ORM. With the ER model there is no way of even

specifying the constraint, much less testing it. The counter rows (SP, Sales

phonecalls) and (PTY, Staff party) are designed to check the uniqueness

constraints that each Activity has at most one Activity name and vice versa.

If these are rejected, the association really is 1:1, as its basic

population suggests. Since the ER notation being discussed doesn't include a

way of indicating that attributes other than the primary identifier are

unique, it isn't very helpful here. As a small point, the Y2K row has been

added to the original population to indicate that it is possible for some

listed activities to be unscheduled.

Figure 10 An ORM diagram for room scheduling,

with sample and counter data

In case it looks like I'm just bashing attribute-based

approaches like ER in this article, let me say again that I find

attribute-based models useful for compact overviews and for getting closer

to the implementation model. However I generate these by mapping from ORM,

which I use exclusively for conceptual analysis. This makes it easier to get

the model right in the first place, and to modify it as the underlying

domain evolves. Unlike ER (and UML for that matter), ORM was built from a

linguistic basis, and its graphic notation was carefully chosen to exploit

the potential of sample populations. To reap the benefits of verbalization

and population for communication with and validation by domain experts, it's

better to use a language that was designed with this in mind. An added

benefit of ORM is that its graphic notation can capture many more business

rules than popular ER notations.

Next issues

Later articles in this series will consider more

advanced aspects of the Oracle ER notation, including exclusion constraints,

frequency constraints, subtyping and non-transferable relationships, and

then examine the Information Engineering notation for ER, before concluding

with a discussion of IDEF1X.

References

<ol>

<li>Barker, R. 1990, CASE*Method: Tasks and

Deliverables, Addison-Wesley, Wokingham, England.</li>

<li>Chen, P.P. 1976, 'The entity-relationship

model-towards a unified view of data', ACM Transactions on Database

Systems, vol. 1, no. 1, pp. 9-36.</li>

<li>Halpin, T.A. 1998-9, 'UML data models from an ORM

perspective: Parts 1-10', Journal of Conceptual Modeling, InConcept,

Minneapolis USA.</li>

<li>Halpin, T.A. & Bloesch, A.C. 1999, 'Data

modeling in UML and ORM: a comparison', Journal of Database Management,

vol. 10, no. 4, Idea group Publishing Company, Hershey, USA, pp. 4-13.</li>

<li>Hay, D.C. 1999, 'There is no object-oriented

analysis', DataToKnowledge Newsletter, vol. 27, no. 1, Business Rule

Solutions, Inc., Houston TX, USA.</li>

<li>Hay, D.C. 1999, 'Object orientation and information

engineering: UML', The Data Administration Newsletter, no. 9, (June

1999), ed. R.S. Reiner, available online at <a href="http://www.tdan.com">www.tdan.com</a>.</li>

<li>Ross, R.G. 1998, Business Rule Concepts, Business

Rule Solutions, Inc., Houston TX, USA.</li>

</ol>

</td>

</tr>

<tr>

Dr Terry Halpin, BSc, DipEd, BA, MLitStud, PhD, is a Program Manager in

Database Modeling for the Enterprise Frameworks and Tools Unit, Microsoft Corporation, USA., Seattle WA, USA.

During a lengthy career as an academic in computer science, he also worked in industry on

database modeling technology and as a data modeling consultant. His recent positions

include head of database research at Asymetrix Corporation, and research director of

InfoModelers Inc., which was acquired by Visio Corporation. For several years, his

research has focused on conceptual modeling and conceptual query technology for

information systems, using a business rules approach. Dr Halpin has presented papers and

tutorials at many international conferences. His doctoral thesis provided the first full

formalization of Object-Role Modeling (ORM/NIAM), and his publications include over

ninety technical papers, as well as four books, including Information Modeling

and Relational Databases (Morgan Kaufmann, 2001).

Contact Information:

Dr Terry Halpin

Program Manager, Database Modeling

Enterprise Framework & Tools Unit, Microsoft Corporation

One Microsoft Way

Redmond WA 98052-6399 (USA)

<a href="mailto:terryha@microsoft.com">terryha@microsoft.com</a>

(425) 705 9190

fax: (425) 936 7329

</BODY>

</HTML>

[Top]

No.	제목	작성자	작성일	조회
116	생명 정보공학이란 무엇인가?	정재익	2001-12-03	4313
114	기지개 펴는 객체 데이터베이스	정재익	2001-12-03	5557
100	Linux May Be More Ready Than You Think	정재익	2001-11-29	3490
85	Entity Relationship Modeling from an ORM Perspective: Part 1 -- By Dr. Terry Halpin	정재익	2001-11-19	4085
54	SQLite 2.0	정재익	2001-11-03	6198
53	SHORE	정재익	2001-11-03	3461
32	Open source Database 가 충분하지 못할 때	정재익	2001-10-23	3599

작업시간: 0.060초, 이곳 서비스는
PostgreSQL v16.1로 자료를 관리합니다