Chapter 5 Building Data Model / Entity Relationships

climbmoujeanteaSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

68 views

Chapter

5 Building Data Model / Entity Relationships

We find that all business processes either consume or produce data. In order to use the data effectively
there must be robust, consistent representation of the data and their relationships to each other. Entity
Relationship Diagram is a tool within the Oracle Designer repository that enables you to model the data
needed by your business processes. It visually represents the information requirements of a system. An
ERD is a communication tool that the designer use
s to reach agreements with the users about their business
requirements. By identifying and describing the data and the relationship among them, the designer can use
the tool to effectively base the design of the underlying database. Further, the ER model i
s independent of
any data storage or access method. For example, the following diagram represents a relationship between
two entities, Order and Customer. We state that a Customer may place zero or more Orders while an Order
must be for a Customer. Name is

shown as a required attribute of the Customer whereas Phone is optional.
As we see here, the diagram not only tells us about the business information but also how that information
may be related.

ORDER
CUSTOMER
*Name
oPhone
for
places
Entity
Relationship
Attributes

Figure
1
Example of an Entity Relationship Diagram

Data modeling involves the following stages, which can be performed iteratively until you are satisfied
with the description of your data:

Identifying entities

Establishing relationships

Entering attributes

De
fining domains

Establishing unique identifiers

Performing quality checks

The entity relationship diagram that results from the above operations can be translated into a relational
database design during the design stage of your systems development. The Dat
abase Design Transformer,
accessed from the Entity Relationship Diagrammer (ERD), creates table mappings and detailed mappings
to automate the mapping of your analysis definitions. We shall see this in a later chapter.

In this chapter we examine the ERD to
ol and build the ER model for the CD Collections. But, first we shall
explore the terminology.

1

Components of ERD

An entity relationship diagram is made up of entities and their relationships. In addition we define meta
information such as domains, attribut
es, and business rules.

1.1

Entity

An entity is a thing of significance, whether real or imagined, about which we need information to be held
or known. From an object oriented point of view an entity is a class, a group of things. A specific instance
is not an

entity; it will be an object, a record. Typical examples of entity would be Order, Assignment,
Student, CD, etc.

Each entity must have a unique, meaningful name. It is also desirable to provide a short name for easy
identification. If you do not specify t
he short name the ERD will generate one by abbreviating the entity
name. Note that this may sometimes result in unsavory names! Do not provide a plural name unless you
want to override the ERD supplied name.

A well designed data model should have, for each

entity, the complete details of:



alternative names for the entity, namely, synonyms



the initial number of occurrences of this entity



the maximum number of occurrences of this entity



the average number of occurrences of this entity



the growth rate

The abov
e features are defined by editing the properties of the entity. These rules are imperative for
professional data modeling. To edit properties, select the object (entity) and chose Edit


properties.

1.2

Attribute

Attributes are details, descriptions, or proper
ties that qualify, identify, classify, quantify, or express the
state of an entity, and can be defined for existing entities only. This information is often termed meta data
by database vendors. Attributes are the lowest level of data definition included i
n the data model, and they
need a lot of detail about them, such as name, type, length, and default values. Comprehensive information
is important for later tasks in the development of your system.

Each attribute in an entity must have a unique name. The
optionality of the attribute should also be
specified. It is recommended that each entity has at least two attributes. To make sure that the data model is
complete, you should examine each entity to make sure that you have all the attributes, and ensure th
at, for
each attribute, you specify:



details of any domain that you could use



the correct optionality



for optional attributes, the number of occurrences that have values, initially and on average



both maximum and average length



a default (initial) value, i
f none is provided on creation (important for mandatory attributes)



allowable values

The types of the attributes that are displayed on an entity are indicated by the symbol displayed to the left
of the attribute name.

Symbol

Description

#

Unique Identifie
r (UID) A combination of attributes and/or relationships that serves to
identify uniquely an occurrence of an entity.

*

Mandatory Attribute

o

Optional Attribute

The Database Design Transformer uses attributes as the basis for column definitions in creat
ing a default
database design from an ER model. The Server Generator then uses these definitions to create the actual
columns in the physical database.

1.3

Domain

A domain is a set of business validation rules, format constraints, and other properties that app
ly to a group
of attributes. Attributes and columns in the same domain are subject to a common set of validation checks.
Useful domains are those things that never or rarely change, such as days of the week, currency values and
yes/no values. Domains help
you define similar attributes consistently and quickly.

Domains can be defined before entities and attributes are created. To make sure that the data model is
complete, you should ensure that, for each domain, you specify:



domain name



format



datatype



leng
th



valid values



what would be a null value representation



default initial value

When a domain has been created, values of attributes can be specified quickly by selecting from the
domain. A domain can also be used as a "short
-
cut" when creating column defi
nitions in the database
design.

The Designer/2000 Generators use domain values when generating applications.

You may find it useful, at the same time as you are drawing a diagram in the Entity Relationship
Diagrammer, to have a Repository Object Navigator
window open, showing the Properties Window for
domains.

1.4

UID

A unique identifier is a combination of attributes and/or relationships that uniquely identifies an occurrence
of an entity. One of the unique identifiers for each entity must be specified as that

entity's primary key. A
unique identifier may be made up of one or more attributes, one or more relationships, or a combination of
attributes and relationships. One entity can have several unique identifiers but only one can be a primary
unique identifier
. It is advisable to keep the primary unique identifier short (not the name but the list of
attributes that form this) because this will be used frequently when developing your system. The Database
Design Wizard uses each primary unique identifier to creat
e the primary key for the table definition.

1.4.1

Relationship

Relationships are named significant associations between two entities, and can be established for existing
entities only. Though many types of relationships exist, majorities of these are one to many
. The optionality
and the degree of each relationship end need to be identified. Each end of the relationship must also be
named. A recursive relationship is one in which an entity is related to itself. We use arcs to indicate mutual
exclusivity of relatio
nships (advanced topic).

A relationship model is complete when for each relationship end you have specified



whether it is transferable



a logical name



the maximum, minimum and average number of occurrences of the entity that is involved



for ‘many’ relation
ship ends, what the maximum and minimum occurrences of the entity in any one
relationship are

2

Building entity only model

2.1

Creating an Entity Relationship Diagram

You can launch the ER diagrammer either from the launch pad or from the Repository Object Navig
ator.
To work with an existing diagram, click on the open folder icon and choose a diagram from the pull down
list. You may also File


Open and select the diagram. To create a new diagram either select File


New
or click on the new document icon. An empt
y diagram window opens up. You can either enter all the
entities at once or then draw the relationships among these or you may work on groups of entities,
establishing the relationships as you progress. Click on the maximize button to expand the window to
fill
the available screen real estate.

2.1.1

Creating entities

To create an entity:

1.

In the Entity Relationship Diagrammer, click

2.

Move the mouse pointer to the area of the diagram in which you want to position your entity.

3.

To create the new entity at the defaul
t size, click the mouse button. To create the new entity larger
or smaller than the default size, drag the entity box to the desired size for the entity, and release
the mouse button.

Note:

positioning the entity box in the appropriate position can create

Sub
-
types and super
-
types. To
create a super
-
type, position and stretch the entity box over and around the entity or entities that
will become its sub
-
types, and release the mouse button. To create a sub
-
type, position the entity
box inside the entity tha
t will become its super
-
type, and release the mouse button.

4.

Enter the name of the entity.

5.

Enter a short name for the entity, then click OK.

Note:

If you do not enter a short name the tool will choose a default short name that is merely a
shortened v
ersion of the full name. Therefore, you may want to specify your own short names to
avoid the risk of any duplicate short names. Also, make sure the short names are not any Oracle
system words (e.g., CON).


Figure
2

Create Entity
Dialog

The use case for the CD Collections reveal five entities, namely, Marketing Material, CD, Vendor, Order,
and Customer. We decide to create all five entities on our canvas because we know what these are. Having
all entities on the canvas allows us to

position them as we like.

A common error that users make in creating ERDs is to use the canvas to conceptualize the entities. It
never works. You should have already defined your entities and decided what attributes these entities
would possess. You shoul
d also have clearly defined the relationship between the entities.


Figure
3

The Five Entities

At this point we have not defined the attributes nor drawn the relationships. Having these five entities on
the canvas, however, help u
s visualize the relationship a little better.

3

Building the fully attributed model

Creating a fully attributed data model requires that you define all the significant attributes of the entities.
Since many of the attributes may share similar characteristics
, it is easier if you define the domains for
these attributes. As explained earlier, a domain eases design woes and builds consistent data design. Once
again this is a step that can not be done on the fly at the machine. Forethought and discussion is warra
nted
to produce stable domains.

Identify all possible domains. Domains could be defined to set even trivial features such as format, length,
etc. For example, all name or description fields may be set to be variable length character fields of certain
maxim
um length.

3.1.1

Creating domains

To create a domain:

1.

In the Entity Relationship Diagrammer, choose Edit


Domains.

2.

Select the Definition tab to display the mandatory and most frequently used details of a domain.

3.

Insert a new attribute row by doing one of the f
ollowing:



to insert a new attribute in the current row, click on an empty attribute row.



to insert a new attribute row before the currently selected row, click on the Insert Row button.

3.

To enter detailed information about the domain, such as the format de
tails for the attributes in a
domain, or the valid units of measurement, select the Detail tab and enter the required details.

4.

To enter information about the allowable values for elements in a domain, select the Values tab
and enter the required details.
Ranges of values, lists of values or code conversions may also be
entered in this tab.

5.

To enter free format multi
-
line text for the text types associated with the domain, select the Text
tab and enter the required text.

6.

To create the domain, click OK.




Figure
4

Entering Domain Details

3.1.1.1

Domain Values

This tabbed window is used to define the domain values that attributes, columns, program data constructs,
parameters or data structures can take. The information can be used for

deriving validation modules. Range
of values, lists of values or code conversions may be entered in this tab. The sequence numbers are also
used by the Generator to define the order of sequence of Radio Group buttons. The values with their
meanings will s
how up in a pop
-
up list on the application.

Note:

Do not include the & character in the text of the domain value. The & symbol is used by
Oracle to mean a run time variable.

Specify same
length. Text
format will
ignore any
length data.

Check if a table of values are
to
be created in Oracle


Figure
5

Defining the Values of a Domain

In the followi
ng figure we show the Domain Definition tab window. Here you see the EMAIL domain
derived from the LONG STRING domain. Such supertype dependencies can be set to reuse definitions that
are already in place. Unlike C++ or other object oriented languages, you

can not use multiple inheritance. If
you have allowable values defined for a supertype then these will become part of the allowable values you
define for the subtype.


Figure
6

Domain Definition

3.1.2

Creating attributes

To create an a
ttribute:

1.

In the Entity Relationship Diagrammer, double
-
click on the entity for which you want to create
attributes.

2.

Select the Attributes tab to display the attribute definition details. Detailed attribute information
and attribute values are defined on t
he Att Detail and Att Value tabs respectively.

3.

Insert a new attribute row by doing one of the following:



to insert a new attribute in the current row, click on an empty attribute row.



to insert a new attribute row before the currently selected row, click
on the Insert Row button.

4.

Enter the appropriate details for the attribute. You must enter at least a name and format. Use the
scroll bars to scroll to properties of the attribute which are not currently displayed.

5.

To save the new attribute, click OK.

To
delete an attribute:

1.

In the Entity Relationship Diagrammer, select the entity whose attributes you want to delete and
choose Edit


Properties. You can also double click the entity.

2.

Select the Attribute tab to display the attribute definition details. Deta
iled attribute information
and attribute values are defined on the Att Detail and Att Value tabs respectively.

3.

Select the row for the attribute you want to delete, and click Delete Row.

4.

To save the changes, click OK.

Note:

If the selected element is not
consistent with the Repository you will be asked whether you want to
requery that element. Only elements that are consistent with the Repository can be edited.


Figure
7

Creating Attributes

If, after creating the attributes,
you change the domain definitions you must update the attributes in
domains. To do this select Utility


Update Attributes in Domains. Choose selected domains or all to
update.

In the above example, the Marketing Material entity does not have any mandatory

attributes (Opt checkbox
empty). Nor have we marked any of the attributes as Primary. This is because we expect the system to
generate a primary key column with an automatic sequence number. We shall see how to do this in a later
chapter.

3.1.3

Creating initial

primary unique identifiers

Unique identifiers may consist of an attribute, a combination of attributes, a combination of relationships or
a combination of attributes and relationships.

1.

To create an initial primary unique identifier:

2.

Select the Attributes

tab of the Edit Entity dialog box or the Definition tab of the Edit Relationship
dialog box.

3.

On the Attributes tab of the Edit Entity dialog box, check the box in the Primary column for each
attribute that you want to be in the unique identifier.

4.

On the

Definition tab of the Edit Relationship dialog box, you check the Primary UID checkbox.

5.

Rename the Primary UID by appending _PK to the name.

As in the illustration below, ORD_PK is the unique primary key identifier for the ORDER entity. It is
associated w
ith the ORDER NUMBER attribute.

Checked
means
attribute may
be null.

Uses same
domain


Figure
8

Setting Initial Unique Primary Key Identifier

4

Building the fully attributed and expanded ER model

The five entities are now fully described with all significant attributes. We have also ta
ken care to define
domains and use these wherever applicable. The fully attributed entities are shown below. The # to the left
of the attribute name implies a primary key attribute. An * means the attribute is mandatory (no nulls
allowed) and an o means th
e attribute is optional (nulls allowed).

There are a few items to note. Not all entities have a primary key identified. If and only if an entity has an
attribute (or attributes) that naturally yields a primary unique identifier should you show that here. I
n our
example, only CD ID and ORDER NUMBER are natural primary key attributes. Other entities may require
either artificial key attributes (called surrogates) or may be derived from relationships between entities.

We will examine surrogate keys later.


Figure
9

Fully Attributed Entity Model

4.1

Creating relationships

To create a relationship:

1.

In the Entity Relationship Diagrammer, click the button for the type of relationship you want to
create.


M:1 (Mandatory to Optional) Relatio
nship button


M:1 (Optional to Optional) Relationship button


M:1 (Optional to Mandatory) Relationship button


M:1 (Mandatory to Mandatory) Relationship button


M:M (Optional to Optional) Relationship button


M:M (Mandatory to Optional) Relationship b
utton


1:1 (Mandatory to Optional) Relationship button


1:1 (Optional to Optional) Relationship button


1:1 (Mandatory to Mandatory) Relationship button

2.

Click on the entity you want to be at the first end of the relationship.

3.

Click on the entity you wa
nt to be at the other end of the relationship.

4.

Enter a name for each end of the relationship.

Note:

To insert a dog
-
leg in the relationship, click the mouse button in free space on the diagram where
you want to create the dog
-
leg. The Relationship Names p
reference, set in the Customize dialog box,
determines whether or not relationship names are displayed on the diagram.

For example, to create a CD to VENDOR relationship that is Many
-
to
-
one (many CDs from a Vendor) we
select the

(M:1


Mandatory to Manda
tory) tool from the tool palette. Start at CD and drag towards
VENDOR. When you release the mouse, the following dialog shows. The From Name corresponds to CD,
as that is where we come from. To Name corresponds to VENDOR as that is where we are going. We c
an
now read the relationship as one or more CDs are bought from a VENDOR and a VENDOR sells one or
more CDs.


Figure
10

Creating Relationship

As seen in the Figure below, for the two relationship panels there are items that expla
in the optionality and
degree of relationship. The dotted line means zero while the solid line means exactly one. Similarly, the
crow’s foot means more than one. Thus in the example below, we say, a MARKETING MATERIAL
instance must promote one and only one

CD and a CD may have zero or more MARKETING MATERIAL
instances. In plain English we say a CD has many different promotional material but any given promotional
material can be for only one CD. This does not mean several copies of the same promotional mater
ial can
not exist!


4.1.1

Creating unique identifiers

Unique identifiers may consist of an attribute, a combination of attributes, a combination of relationships or
a combination of attributes and relationships.

This procedure is primarily for creating altern
ate unique identifiers.

To create a unique identifier:

1.

In the Entity Relationship Diagrammer, double
-
click on the entity for which you want to create a
unique identifier.

2.

Select the UIDs tab to display the unique identifier details.

Note

A UID will have
been created if any of the attributes are marked as Primary on the Attributes tab.
This UID will initially be given an automatically generated name, which you can change as
described in the following step.

3.

Click on an existing unique identifier name or on
an empty Name cell. To insert a new row before
the currently selected row, click on the Insert UID button.

4.

For each UID, say whether it is primary by checking the box in the Primary? Column.

5.

Include the required attributes and relationships in the UID by h
ighlighting them in the relevant
Candidate field and clicking on the DOWN ARROW button.

6.

Remove any unwanted attributes and relationships in the UID by highlighting them in the Unique
Identifier Contents field and clicking on the UP ARROW button.

7.

To creat
e the UID, click OK.


Figure
11

Creating a Unique Identifier

Notice that in the above example, the VENDOR NAME attribute, which is mandatory, may be used in the
unique identifier VEND UI. We do not have any attribute defined for t
he primary UID, even though VEND
PK is the Primary UID for the entity. The Database Design Transformer will create a primary key column
for the table. It is inadvisable to create unique identifiers using optional attributes, as uniqueness cannot be
ensured
.

The following figure shows the fully attributed data model with all significant relationships between
entities. Note that the relationship between CD and ORDER is many
-
to
-
many. Though it is acceptable to
define ER models with many
-
to
-
many relationships,
it is a good practice to resolve these types of
relationships prior to Database Design Transformation.

Oracle does not use any symbols to represent the cardinality of the relationships, for example, the bars or
circles (o) that you see at the relationshi
p ends in other tools. The dotted line represents optionality. The
empty diamond on the relationship end implies that the relationship is not transferable. That is, an Order
instance cannot be transferred to another Customer.


Figure
12
Unresolved Fully Attributed ER Model

You may also want to "resolve" each many
-
to
-
many relationship into two one
-
to
-
many relationships with a
new intersection entity. Alternatively you could leave them as many
-
to
-
many relationships and allow the
Datab
ase Design Transformer to create intersection tables.

Attention can be drawn to specific entities or sections of a diagram by using different colors or fonts, and
several diagrams can be created to cover the information model. For example, you could have o
ne diagram
showing all the entities, and additional diagrams for each area of data.

The layout of the whole diagram or of selected elements can be changed using the autolayout utility. Each
time the autolayout utility is run, a different layout of element
s is displayed. The process can be repeated as
many times, as you require.

5

Quality Checking

Several Repository reports are available for you to conduct quality checks of your data model. These
include

Entity Definition

Entities and their Attributes

Attribu
te Definition

Attributes in a Domain

Entity Model Reference

Entity Completeness Checks

These reports ensure the following;

For each entity:



Does the name accurately describe the data?



Are there any synonyms for this entity?



How many occurrences are there?



What is the growth rate?



Are there any sub
-
types?



Are there any other unique identifiers for this entity?



Which unique identifier is the primary key?

For each relationship:



Can you read the relationship from one entity to the other?



Is the relationship tra
nsferable?



Are you really sure of the optionality of the relationship end?



Have you resolved many
-
to
-
many relationships wherever feasible?

For each attribute:



Does it have a logical name?



Is there a domain that you can use?



Is the attribute really optional
?



For the entity relationship diagram:



Is it presentable, with legible elements and few crossing lines?



Can you argue that the diagram reflects the business area accurately?



Could you use it to describe the data to a user?

6

Laying out an entity relationship

diagram automatically

A diagram can be automatically laid out a number of times providing you with several alternative layouts.
Any of these layouts can be retrieved if no other operation is performed between the autolayout operations.

Note:

After automat
ically laying out a diagram, the utility also minimizes the number of pages that the
diagram covers. To improve the printed appearance of a diagram, you can move the elements on a
diagram so that they cover the minimum number of pages.

To use the AutoLayou
t utility more effectively:



Increase the default entity height and width in the Customize dialog box.



Reduce the size of the diagram, where possible.



Select all of the elements on the diagram before running the AutoLayout utility again. This gives
the util
ity the maximum flexibility when rearranging elements on a diagram.

There are four ways in which elements can be automatically laid out on a diagram.

I.

Reposition all elements automatically on the active diagram:

1.

In the Entity Relationship Diagrammer, ens
ure that no elements are selected on the active
diagram.

2.

Choose Layout


AutoLayout.

II.

Reposition selected elements automatically on the active diagram (one in the front):

1.

In the Entity Relationship Diagrammer, select the elements to be automatically lai
d out.

2.

Choose Layout


AutoLayout.

III.

Reposition selected elements automatically within a new defined autolayout area on the active
diagram:

1.

In the Entity Relationship Diagrammer, select the elements to be automatically laid out.

2.

Choose Layout


AutoLayout to

New Area.

3.

Position the mouse pointer at the point on the active diagram at which you want to start
defining the autolayout area and hold the mouse button down.

4.

Drag the rubber
-
band box to the size required for the autolayout area and release the
mouse but
ton.

Note:

A better autolayout result can be achieved if the autolayout area you select contains a large amount
of free space.

IV.

Reposition selected elements automatically within a previously defined autolayout area on the
active diagram:

1.

Select the elements

to be automatically laid out.

2.

Choose Layout


AutoLayout to Same Area.

The selected elements are automatically laid out within the previously defined autolayout area.