Modeling Data in the Organization

italiansaucyΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

86 εμφανίσεις

Chapter 3

Chapter 3:

Modeling Data in the Organization

Modern Database Management

8
th

Edition

Jeffrey A. Hoffer, Mary B. Prescott,

Fred R. McFadden

© 2007 by Prentice Hall

1

Chapter 3

Objectives


Definition of terms


Importance of data modeling


Write good names and definitions for entities, relationships,
and attributes


Distinguish unary, binary, and ternary relationships


Model different types of attributes, entities, relationships, and
cardinalities


Draw E
-
R diagrams for common business situations


Convert many
-
to
-
many relationships to associative entities


Model time
-
dependent data using time stamps

2

Chapter 3

What are business rules, what is their source, and why
are they crucial?


Business rules are precise statements, derived from a detailed description of the
organization's operations.
When written properly
, business rules define one or more of
the following modeling components:


entities


relationships


attributes


connectivities


cardinalities


constraints





Because the business rules form the basis of the data modeling process, their precise
statement is crucial to the success of the database design.

3

Chapter 3

What role does the ER diagram play in the
design process
?


A completed ER diagram is the actual blueprint of the database
.



Its composition must reflect an organization's operations accurately if the
database is to meet that organization's data requirements.


It
forms the basis for a final check on whether the included entities are
appropriate and sufficient, on the attributes found within those entities,
and on the relationships between those entities.


It
is also used as a final crosscheck against the proposed data dictionary
entries.


The
completed ER diagram also lets the designer communicate more
precisely with those who commissioned the database design.


Finally
, the completed ER diagram serves as the implementation guide to
those who create the actual database.


In
short, the ER diagram is as important to the database designer as a
blueprint is to the architect and builder.


4

Chapter 3

E
-
R Model Constructs


Three components of ER Model


Entities:


Refers to the entity sets, not a single entity occurrence


Entity instance/occurrence

person, place, object, event, concept (often
corresponds to a row in a table)


Entity sets corresponds to a table and entity instance/occurrence
corresponds to a row in the table.



Relationships:


Relationship instance

link between entities (corresponds to primary key
-
foreign key equivalencies in related tables)


Relationship type

category of relationship…link between entity types



Attribute

property or characteristic of an entity or relationship type (often
corresponds to a field in a table)




5

Chapter 3

6

Sample E
-
R Diagram (Figure 3
-
1)

Chapter 3

7

Relationship
degrees specify
number of
entity types
involved

Entity
symbols

A special entity
that is also a
relationship

Relationship
symbols

Relationship
cardinalities
specify how
many of each
entity type is
allowed

Attribute
symbols

Basic E
-
R notation (Figure 3
-
2)

Chapter 3

What Should an Entity Be?


SHOULD BE:


An object that will have many instances in the
database


An object that will be composed of multiple
attributes


An object that we are trying to model


SHOULD NOT BE:


A user of the database system


An output of the database system (e.g., a
report)

8

Chapter 3

Attributes


Attribute

property or characteristic of an entity


In Chen Model, attributes are represented by
ovals and are connected to the entity rectangle
with a line


Each oval contains the name of attributes it
represents


In the Crow’s Foot Model, the attributes are
simply written in the attribute box below the
entity rectangle

9

Chapter 3

10

Chapter 3

Identifiers (Keys)


Identifier (primary key)

An attribute (or
combination of attributes) that uniquely
identifies individual instances of an entity set


Simple/simple versus Composite Identifier


Ideally only a single attribute


Possibly a composite key



Candidate Identifier

an attribute that could
be a key…satisfies the requirements for being
an identifier

11

Chapter 3

attributes


Composite attributes: attributes that can be
further subdivided (address, tel #)


Simple attributes


Single
-
valued attributes


Multi
-
valued attributes: college degrees, tel #,
car color


Derived attributes

12

Chapter 3

13

Chapter 3

Resolving
Multivalued

Attribute
Problems


Although the conceptual model can handle
multivalued

attributes,
you should not
implement them in the relational DBMS


Within original entity, create several new
attributes, one for each of the original
multivalued

attribute’s components


Can lead to major structural problems in the table


Create a new entity composed of original
multivalued

attribute’s components


14

Chapter 3

15

Chapter 3

16

Emp_age should have been with [ ]. See next slide

Chapter 3

17

Figure 3
-
7 A
composite

attribute

An attribute
broken into
component parts

Figure 3
-
8 Entity with
multivalued

attribute (Skill)

and
derived

attribute (Years_Employed)

Multivalued

an employee can have

more than one skill


Derived

from date
employed and
current date

Chapter 3

18

Figure 3
-
9 Simple and composite identifier attributes

The identifier is boldfaced and underlined

Chapter 3

More on Relationships


Relationship Types vs. Relationship Instances


The relationship type is modeled as lines between entity
types…the instance is between specific entity instances


Relationships can have attributes


These describe features pertaining to the association between the
entities in the relationship


Two entities can have more than one type of
relationship between them (multiple relationships)


Associative Entity

combination of relationship and
entity

19

Chapter 3

20

Figure 3
-
10 Relationship types and instances

a) Relationship type

b) Relationship
instances

Chapter 3

Degree of Relationships


Degree of a relationship is the
number of entity types that
participate in it


Unary Relationship


Binary Relationship


Ternary Relationship

21

Chapter 3

22

Degree of relationships


from Figure 3
-
2

Entities of
two different
types related
to each other

Entities of three
different types
related to each
other

One entity
related to
another of
the same
entity type

Chapter 3

Connectivity (Cardinality of
Relationships)


One
-
to
-
One


Each entity in the relationship will have exactly one related
entity


One
-
to
-
Many


An entity on one side of the relationship can have many
related entities, but an entity on the other side will have a
maximum of one related entity


Many
-
to
-
Many


Entities on both sides of the relationship can have many
related entities on the other side

23

Chapter 3

Cardinality Constraints/cardinality


Cardinality Constraints
-

the specific number of
instances of one entity that can or must be
associated with each instance of another entity


Minimum Cardinality


If zero, then optional


If one or more, then mandatory


Maximum Cardinality


The maximum number


Dbms

cannot handle the implementation of the
cardinalities at the table level. This capabilities is
provided by the application software or by triggers.


24

Chapter 3

25

Chapter 3

26

Figure 3
-
12 Examples of relationships of different degrees


a) Unary relationships

Chapter 3

27

Figure 3
-
12 Examples of relationships of different degrees (cont.)


b) Binary relationships

Chapter 3

28

Figure 3
-
12 Examples of relationships of different degrees (cont.)


c) Ternary relationship

Note: a relationship can have attributes of its own

Chapter 3

29

Figure 3
-
17 Examples of cardinality constraints




a) Mandatory cardinalities

A patient must have recorded
at least one history, and can
have many

A patient history is
recorded for one and
only one patient

Chapter 3

30

Figure 3
-
17 Examples of cardinality constraints (cont.)




b) One optional, one mandatory

An employee can be assigned
to any number of projects, or
may not be assigned to any
at all

A project must be
assigned to at least one
employee, and may be
assigned to many

Chapter 3

31

Figure 3
-
17 Examples of cardinality constraints (cont.)




a) Optional cardinalities

A person is is
married to at most
one other person,
or may not be
married at all

Chapter 3

32

Entities can be related to one another in more than one way

Figure 3
-
21 Examples of multiple relationships


a) Employees and departments

Chapter 3

33

Figure 3
-
21 Examples of multiple relationships (cont.)


b) Professors and courses (fixed lower limit constraint)

Here, min
cardinality
constraint is 2

Chapter 3

34

Figure 3
-
15a and 3
-
15b Multivalued attributes can be represented as relationships

simple

composite

Chapter 3

Strong vs. Weak Entities, and

Identifying Relationships


Strong /regular entities = existence
-
independent entity


exist independently of other types of entities


has its own unique identifier/primary key



Weak entity = existence
-
dependent entity


dependent on a strong entity (identifying owner)…cannot exist on its own


It has a primary key derived from the parent entity


35

Chapter 3

36

Chapter 3

Relationship strength

Strong entity

Strong entity

Weak

(non
-
identifying)
relationships

Strong (parent)
entity

Weak (child) entity

Strong (identifying)
relationships

Strong (parent)
Entity


Weak (child) entity


Weak

(non
-
identifying)
relationships

37

Chapter 3

38

Strong entity

Weak entity

Identifying relationship

Double line should have been used

Chapter 3

39

Chapter 3

40

Chapter 3

41

Chapter 3


In
-
class assignment p, 131. problem 4.d


Model section as a WEAK ENTITY.

42

Chapter 3

HOW ARE M:N RELATIONSHIPS HANDLED IN
THE DEVELOPMENT OF AN ER DIAGRAM?



Although M:N relationships may properly be
viewed in a relational database model at the
conceptual

level,
such relationships should not
be implemented
, because their existence
creates undesirable redundancies.


Therefore, M:N relationships must be
decomposed into 1:M relationships to fit into
the ER framework.


43

Chapter 3


The M:N Relationship


44

Chapter 3

45

Chapter 3

Associative Entities

The M:N relationship depicted in the previous figure must be
broken up into two 1:M relationships through the use of
_______ entity.



a bridge entity, also known as



a composite entity or



an associative entity




46

Chapter 3

47

Chapter 3

48

Chapter 3

49

Dashed line
-


weak relationships

solid line
-


strong relationship

Chapter 3

In
-
class exercise


Given the following business rules, create the appropriate Chen and Crow’s Foot
ER diagrams for each of the specified relationships:


A

company

operates

four

departments.


Each department employs employees.


Each of the employees may or may not have one or more dependents.


Each employee may or may not have an employment history.




50

Chapter 3

Use the following business rules to write all appropriate connectivities in the ER diagram:


A department employs many employees, but each employee is employed by one department.





Some employees, known as "rovers," are not assigned to any department
.



A division operates many departments, but each department is operated by one division





An employee may be assigned to many projects, and a project may have many employees assigned to it.





A project must have at least one employee assigned to it.





One of the employees manages each department, and each department is managed by one employee.





One of the employees runs each division, and each division is run by one employee.





51

Chapter 3

52

Figure 3
-
11a A binary relationship with an attribute

Here, the date completed attribute pertains specifically to the
employee’s completion of a course…it is an attribute of the
relationship.
Should have been connected by a dashed line

Chapter 3

53


In
-
class assignment

p. 131 problem 4a.

p. 131 problem 4e. Draw two relationships


Admit and treat

Chapter 3

54

Figure 3
-
11b An associative entity (CERTIFICATE)

Associative entity is like a relationship with an attribute, but it is
also considered to be an entity in its own right.


Note that the many
-
to
-
many cardinality between entities in Figure
3
-
11a has been replaced by two one
-
to
-
many relationships with
the associative entity. The associative entity symbol
--

incorrect

Chapter 3

55

Do page 131, problem 4.g . There are four different
situations to be modeled.

Do page 132, problem 5.