transforming entities and attributes to relations

8/8/2019 Transforming Entities and Attributes to Relations

1/28

* Transforming Entities and Attributes to Relations* Review of Relationship Concepts


2/28

Our ultimate aim is to transform the ER

design into a set of definitions for relational

tables in a computerized database, whichwe do through a set of transformation

rules.


3/28

Transformation Rule 1.

Each entity in an ER diagram is mapped to a single tablein a relational database; the table is named after the entity.

The table s columns represent all the single-valued simple attributes

attached to the entity (possibly through a composite attribute, although a

composite attribute itself does not become a column of the table).

An identifier for an entity is mapped to a candidate key for the table, as

illustrated in Example 2.1 , and a primary identifier is mapped to a primary

key.

Note that the primary identifier of an entity might be a composite attribute,

which therefore translates to a set of attributes in the relational table

mapping. Entity occurrences are mapped to the table s rows.


4/28

Figure 2.2


5/28

EXAMPLE 2.1

Here are the two tables, with one example row filled in, mapped from the

Students and Employees entities in the ER diagrams ofFigure 2.2 .

The primary key is underlined.


6/28


Given an entity E with primary identifierp , a multivaluedattributed attached to E in an ER diagram is mapped to a table

of its own; the table is named after the plural multivalued

attribute.

The columns of this new table are named afterp and a (either p or amight consist of several attributes), and rows of the table correspond to (

p, a ) value pairs, representing all pairings of attribute values of a

associated with entity occurrences in E.

The primary key attribute for this table is the set of columns inp and a .


7/28

EXAMPLE 2.2

Here is an example database of two tables reflecting the ER diagram for the

Employees entity and the attached multivalued attribute, hobbies , ofFigure 2.2 .


8/28

Review for clarification

relationship occurrence or relationship instance

A particular occurrence of a relationship, corresponding to a tuple of entity

occurrences.

degree of the relationship.

The number of entities m in the defining list.

binary relationship

A relationship between two entities.

For example, we define teaches to be a binary relationship between

Instructors and Course sections .

NOTE:We indicate that a relationship instance exists by saying

that a particular instructor teaches a specific course section.


9/28

Another example of a relationship is works on , defined to relate the two entities

Employees and Projects in a large company: Employees works on Projects .

A relationship can also have attached attributes. The relationship works on

might have the attribute percent , indicating the percent of work time duringeach week that the employee is assigned to work on each specific project

Note that this percent attribute attached to the works on relationship

would be multivalued if attached to either entity Employees orProjects ; the

percent attribute is only meaningful in describing a specific employee project

pair, and it is therefore a natural attribute of the binary relationship works on .


10/28

ring or recursive relationship

A binary relationship that relates an entity to itself.

For example, the Employees entity is related to itself through the relationship

manages , where we say that one employee manages another.

NOTES:

1. In the case of a ring, the connecting lines are often labeled with the names of

the roles played by the entity instances involved. The two named roles are

manager ofand reports to.

2. We often leave out attributes in an ER diagram to concentrate on

relationships between entities without losing our concentration in excessive

detail.


11/28

Cardinality ofEntity Participation in a Relationship

FIGURE 2.6

Examples of relationships R between two entities E and F.


12/28

Figure 2.6 illustrates the concepts ofminimum and maximum cardinality with

which an entity participates in a relationship.

Figure 2.6(a), (b), and (c) represent entities E and F on the left and right,

respectively, by two sets; elements of the two sets are connected by a line exactly

when a relationship R relates the two entity occurrences represented.

Thus, the connecting lines themselves represent instances of the relation R.

Note that the diagrams ofFigure 2.6 are not what we refer to as ER diagrams.


13/28

The minimum cardinality with which an entity takes part in a relationship is

the minimum number of lines that the DBA allows to be connected to each entity

instance.

NOTE:

1. The diagrams ofFigure 2.6 would normally only give examples of

relationships at a given moment, and the line connections might

change, just as the row content of a table can change, until some

entity instances have different numbers of lines connected.

2. On the other hand, the minimum and maximum cardinality

properties of an entity are meant to represent rules laid down by the

DBA for all time, rules that cannot be broken by normal databasechanges affecting the relationship.


14/28

In Figure 2.6(a) , the DBA clearly permits both entity sets E and F to take part in

relationship R with minimum cardinality 0; that is to say, the DBA does not require a

connecting line for each entity instance, since some elements of both sets have no

lines connected to them.

We symbolize this by writing min-card(E, R) = 0 and min-card(F, R) = 0.


15/28

The maximum cardinality with which E and F take part

in R is not obvious from Figure 2.6(a) , however.

No entity instance has more than one line connected

to it, but from an example as of a given moment we

have no guarantee that the line connections won t

change in the future so that some entity instances will

have more than one line connected.

However, we will assume for purposes of simple

explanation that the diagrams of this figure are meant

to represent exactly the cardinalities intended by the

DBA.

Thus, since no entity instance of E and F in Figure2.6(a) has more than one incident connecting line, we

record this fact using the notation max-card(E, R) = 1

and max-card(F, R) = 1.


16/28

In Figure 2.6(b) , assuming once again that this set of

lines is representative of the designer s intention, we

can write min-card(E, R) = 0, since not every element

of E is connected to a line,

but min-card(F, R) = 1, since at least one line is

connected to every element of F, and our assumption

implies that this won t change.


17/28

We also write max-card(E, R) = N, where N means

more than one ; this means that the designer does

not intend to limit to one the number of linesconnected to each entity instance of E.

However, we write max-card(F, R) = 1, since every

element of F has exactly one line leaving it.


18/28

Note:

The two meaningful values for min-card are 0 and 1(where 0 is not really a limitation at all, but 1 stands for the constraint at least one ),

The two meaningful values for max-card are 1 and N(N is not really a limitation, but 1 represents the constraint no more than one ).

We don t try to differentiate numbers other than 0, 1, and many. Since

max-card(E, R) = N, there are multiple entity instances ofF connected to one of

E by the relationship. * For this reason, F is called the many side and E is called

the one side in this many-to-one relationship.

*Note particularly that the

many side in a many-to-

one relationship is the side

that has max-card value 1


19/28

In Figure 2.6(c) we have

min-card(E, R) = 0,

min-card(F, R) = 0,

max-card(E,R) = N, and

max-card(F, R) = N.


20/28

What were the ASSIGNMENTS/

BUSINESS RULES and DEFINITIONS

that were used to have a diagramlike this?

PUTTING IT TO ERS.


21/28

EXAMPLE 2.5 (ASSIGNMENT OR BUSINESS RULES)

In the relationship teaches ofFigure 2.3 , Instructors teaches Course sections ,

the DBA would probably want to make a rule that each course section needs to

have at least one instructor assigned to teach it by writing min-card( Course

sections , teaches ) = 1.

!However, we need to be careful in making such a rule, since it means that we will not be able to create a

new course section, enter it in the database, assign it a room and a class period, and allow students to

register for it, while putting off the decision of who is going to teach it.

The DBA might also make the rule that at most one instructor can be assigned to

teach a course section by writing max-card( Course sections , teaches ) = 1.

On the other hand, if more than one instructor were allowed to share the teaching of

a course section, the DBA would write max-card( Course sections , teaches ) = N.

This is clearly a significant difference. We probably don t want to make the rule that

every instructor teaches some course section (written as min-card( Instructors ,

teaches ) = 1), because an instructor might be on leave, so we settle on min-card(

Instructors , teaches ) = 0.

And in most universities the course load per instructor is greater than one in any

given term, so we would set max-card( Instructors , teaches ) = N.


22/28

EXAMPLE 2.5 ( DEFINITION)

When an entity E takes part in a relationship R with

min-card(E, R) = x (x is either 0 or 1) and

max-card(E, R) = y (y is either 1 or N),

Then in the ER diagram the connecting line between E and R can be labeled with

the ordered cardinality pair (x, y).

We use a new notation to represent this minimum-maximum pair (x, y):

card(E, R) = (x, y).


23/28

According to the above definition and the assignments of Example 2.5 , the edge

connecting the entity Course sections to the relationship teaches should be

labeled with the pair (1, 1).

In Figure 2.7 we repeat the ER diagrams ofFigure 2.3 ,

with the addition of ordered pairs (x, y) labeling line connections, to show the

minimum and maximum cardinalities for all ER pairs. The cardinality pair for

the Instructors teaches Course sections diagram follows the discussion of

Example 2.5 , and other diagrams are filled in with reasonable pair values.


24/28

We make a number of decisions to arrive at the following rules:

Every employee must work on at least one project (but may work on many);

A project might have no employees assigned during some periods (waiting for

staffing),

Some projects will have a large number of employees working on them;


25/28

An employee who acts in the manager of role (see discussion below) may be

managing no other employees at a given time and still be called a manager; and an

employee reports to at most one manager, but may report to none (this possibility

exists because there must always be a highest-level employee in a hierarchy who

has no manager).

In the Employees-manages diagram shown in Figure 2.7 , the normal notation,

card(Employees , manages) , would be ambiguous. We say that there are two

different roles played by the Employees entity in the relationship: the manager ofrole and the reports to role. Each relationship instance in manages connects a

managed employee ( Employees instance in the reports to role) to a manager

employee ( Employees instance in the manager of role).

We use the cardinality

notation with entities having parenthesized roles to remove ambiguity.

card(Employees(reports to). manages) = (0. 1)

and

card(Employees(manager of). manages) = (0. N)


26/28

And from these cardinalities we see that an employee who acts in the manager of

role may be managing no other employees at a given time and still be called a

manager; and an employee reports to at most one manager, but may report to

none (because of the highest-level employee in a hierarchy who has no manager

if it weren t for that single person, we could give the label (1, 1) to the reports to

branch of the Employees-manages edge).


27/28


N N Relationships:When two entities E and F take part in amany-to-many binary relationship R, the relationship is

mapped to a representative table T in the related relational

database design.

The table contains columns for all attributes in the primary keys of bothtables transformed from entities E and F, and this set of columns forms the

primary key for the table T.

Table T also contains columns for all attributes attached to the

relationship.

Relationship occurrences are represented by rows of the table, with the

related entity instances uniquely identified by their primary key values as

rows.


28/28

TO BE CONTINUED

transforming entities and attributes to relations

Documents