Transcript Modeling Data in the Organization

Chapter 3:
Modeling Data in the
Organization
註 : 於11版為Chapter 2
楊立偉教授
台灣大學工管系
2013 Fall
1
Business Rules

Statements that define or constrain some
aspect of the business 用來定義或限制 : Ex.
每人每學期修課總學分上限為30學分

Control/influence business behavior 控制或
影響行為 Ex. 避免學生超修行為

Expressed in terms familiar to end users
以使用者熟悉的語言來表達

Automated through DBMS software Ex. 修
課系統應自動擋掉超修行為
Chapter 3
2
A Good Business Rule Is:







Declarative–what, not how 陳述清楚
Precise–clear, agreed-upon meaning 精確
Atomic–one statement 不可分割
Consistent–internally and externally 一致
Expressible–structured, natural language 可被結
構化的口語表達
Distinct–non-redundant 非贅述的
Business-oriented–understood by business
people 可被該領域的人了解
Chapter 3
3
A Good Data Name Is:







Related to business, not technical 非技術詞
Meaningful and self-documenting 看了就懂
Unique 獨一
Readable 可讀性高
Composed of words from an approved list
是可用列表中的語詞
Repeatable 適用多種狀況
Follows standard syntax 有固定命名方式
Chapter 3
4
E-R Model Constructs

Entities: 個體



Entity instance – person, place, object, event, concept
(often corresponds to a row in a table)
Entity Type – collection of entities (often corresponds to
a 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)


Chapter 3
5
Sample E-R Diagram (Figure 3-1)
Chapter 3
1
0 or 1
1…N
0…N
6
Basic E-R notation (Figure 3-2)
Entity
symbols
A special entity
that is also a
relationship
Relationship
degrees
specify number
of entity types
involved
Chapter 3
Attribute
symbols
Relationship
symbols
Relationship
cardinalities
specify how
many of each
entity type is
allowed
7
先找出 Entity
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)
Chapter 3
8
Figure 3-4 Example of inappropriate entities
System
user
X 會計出納員
(這是操作的人)
Inappropriate
entities
System
output
X 費用報告
(這是運算後的結果)
Appropriate
entities
Chapter 3
9
Attributes


Attribute–property or characteristic of an entity
or relationship type
Classifications of attributes:





Required versus Optional Attributes 是否必填
Simple versus Composite Attribute
複合屬性 Ex. 姓名 vs 姓+名
Single-Valued versus Multivalued Attribute 是否有多值
Stored versus Derived Attributes
是否為衍生屬性 Ex. 出生年 vs 年齡
Identifier Attributes 是否可供識別用 Ex. 學號
Chapter 3
10
Data Modeling Concepts: Attributes
Attribute – a descriptive property or
characteristic of an entity. Synonyms
include element, property, and field.

Just as a physical student can have
attributes, such as hair color, height,
etc., data entity has data attributes
Compound attribute – an attribute
that consists of other attributes.
Synonyms in different data modeling
languages are numerous:
concatenated attribute, composite
attribute, and data structure.
例如: 姓名屬性可由姓+名所組成
右方紅框內即為複合屬性
Chapter 3
Data Modeling Concepts: Data Type
資料型別 – 這個屬性可以裝哪種資料
Data type – a property of an attribute that identifies what
type of data can be stored in that attribute.
Representative Logical Data Types for Attributes
Data Type
Logical Business Meaning
Any number, real or integer. 實數或整數
A string of characters, inclusive of numbers. When numbers are included in a
TEXT attribute, it means that we do not expect to perform arithmetic or
comparisons with those numbers. 文字
MEMO
Same as TEXT but of an indeterminate size. Some business systems require the
ability to attach potentially lengthy notes to a give database record. 不定長度的
文字
DATE
Any date in any format.
TIME
Any time in any format.
An attribute that can assume only one of these two values. → 布林 (boolean)
YES/NO
VALUE SET A finite set of values. In most cases, a coding scheme would be established (e.g.,
FR=Freshman, SO=Sophomore, JR=Junior, SR=Senior). 有限的值集合
Chapter
IMAGE 3
Any picture or image.
NUMBER
TEXT
Data Modeling Concepts: Domains
資料範圍 – 這個屬性裝哪些值才是合法
Domain – a property of an attribute that defines what
values an attribute can legitimately take on.
Representative Logical Domains for Logical Data Types
Data Type
Domain
Examples
NUMBER
For integers, specify the range. 值域與小數位數
For real numbers, specify the range and precision.
{10-99}
{1.000-799.999}
TEXT
Maximum size of attribute. Actual values usually
infinite; however, users may specify certain narrative
restrictions. 最大文字長度
Text(30)
DATE
Variation on the MMDDYYYY format.
MMDDYYYY
MMYYYY
TIME
For AM/PM times: HHMMT
For military (24-hour times): HHMM
HHMMT
HHMM
YES/NO
{YES, NO}
{YES, NO} {ON, OFF}
VALUE SET
Chapter
3 {value#1, value#2,…value#n}
{table of codes and meanings}
{M=Male
F=Female}
Data Modeling Concepts:
Default Value 預設值
Default value – the value that will be recorded if
a value is not specified by the user.
Permissible Default Values for Attributes
Default Value
Interpretation
A legal value from
the domain
For an instance of the attribute, if the user does not specify 0
a value, then use this value.
1.00
NONE or NULL
For an instance of the attribute, if the user does not specify NONE
a value, then leave it blank. 空值或無值
NULL
Required or NOT
NULL
For an instance of the attribute, require that the user enter REQUIRED
a legal value from the domain. (This is used when no value NOT NULL
in the domain is common enough to be a default but some
value must be entered.) 必填 或 不可為無值
Chapter 3
Examples
Identifiers (Keys)



Identifier (Key)–an attribute (or
combination of attributes) that uniquely
identifies individual instances of an entity
type Ex. 學號 or 系所+姓名
Simple versus Composite Identifier
Candidate Identifier – an attribute that
could be a key…satisfies the requirements
for being an identifier
Chapter 3
15
Characteristics of Identifiers




Will not change in value 不會更改
Will not be null 不會無值
No intelligent identifiers (e.g., containing
locations or people that might change) 不
要包含可能更改的欄位 Ex. 系所+姓名
Substitute new, simple keys for long,
composite keys 簡短單一最好
Chapter 3
16
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
Chapter 3
年資
Derived
from date
employed and
current date
17
Figure 3-9 Simple and composite identifier attributes
ID欄位 (或稱 Key鍵值)
The identifier is boldfaced and underlined
Chapter 3
18
Figure 3-19 Simple example of time-stamping
歷史價格
This attribute is both
multivalued and composite
注意大括號與小括號
Chapter 3
19
More on Relationships

Relationship Types vs. Relationship Instances


Relationships can have attributes



The relationship type is modeled as lines between
entity types…the instance is between specific entity
instances
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 特殊關係所轉換成的entity
Chapter 3
20
Figure 3-10 Relationship types and instances
a) Relationship
type
b) Relationship
instances
Chapter 3
21
Degree of Relationships

Degree of a relationship is the number of
entity types that participate in it

Unary Relationship
A
relationship between different instances of the
same entity is called a recursive relationship
Binary Relationship
 Ternary Relationship

Chapter 3
22
Degree of relationships – from Figure 3-2
One entity
related to
another of
the same
entity type
Ex. 朋友關係
Chapter 3
Entities of
two different
types related
to each other
Ex. 修課關係
Entities of three
different types
related to each
other
Ex. 工作分派關係
23
Cardinality of Relationships

One-to-One


One-to-Many


Each entity in the relationship will have exactly one
related entity
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
Chapter 3
24
Cardinality Constraints


Cardinality Constraints—the 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
Chapter 3
25
Figure 3-12 Examples of relationships of different degrees
a) Unary relationships
Chapter 3
26
Figure 3-12 Examples of relationships of different degrees (cont.)
b) Binary relationships
Chapter 3
27
Figure 3-12 Examples of relationships of different degrees (cont.)
c) Ternary relationship
零件
廠商
倉庫
供應出貨關係
Note: a relationship can have attributes of its own
Chapter 3
28
Figure 3-17 Examples of cardinality constraints
a) Mandatory cardinalities 必填
A patient history is
recorded for one and
only one patient
Chapter 3
A patient must have recorded
at least one history, and can
have many
29
Figure 3-17 Examples of cardinality constraints (cont.)
b) One optional, one mandatory
A project must be assigned to
at least one employee, and
may be assigned to many
Chapter 3
An employee can be assigned to
any number of projects, or may
not be assigned to any at all
30
Figure 3-17 Examples of cardinality constraints (cont.)
c) Optional cardinalities
A person is
married to at most
one other person,
or may not be
married at all
Chapter 3
31
Figure 3-21 Examples of multiple relationships
a) Employees and departments
Entities can be related to one another in more than one way
Chapter 3
32
Figure 3-21 Examples of multiple relationships (cont.)
b) Professors and courses (fixed lower limit constraint)
Here, min cardinality constraint is 2.
At least two professors must be qualified
to teach each course. Each professor must
be qualified to teach at least one course.
Chapter 3
33
Figure 3-15a and 3-15b Multivalued attributes can be
represented as relationships
simple
composite
Chapter 3
34
Strong vs. Weak Entities, and
Identifying Relationships

Strong entities




Weak entity





exist independently of other types of entities
has its own unique identifier
identifier underlined with single line
dependent on a strong entity (identifying owner)…cannot
exist on its own
does not have a unique identifier (only a partial identifier)
partial identifier underlined with double line
entity box has double line
Identifying relationship

links strong entities to weak entities
Chapter 3
35
Identifying relationship (Figure 3-5)
扶養親屬
Strong entity
Chapter 3
Weak entity
36
Associative Entities
entity–has attributes

An

A

When should a
relationship–links entities together
relationship with attributes instead be an
associative entity





All relationships for the associative entity should be many
The associative entity could have meaning independent of the
other entities 有獨立意義時
The associative entity preferably has a unique identifier, and
should also have other attributes 自己有獨立id時
The associative entity may participate in other relationships other
than the entities of the associated relationship (理由類同下條)
Ternary relationships should be converted to associative entities
Chapter 3
37
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
Chapter 3
38
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
Chapter 3
39
Figure 3-13c An associative entity – bill of materials structure
This could just be a relationship with
attributes…it’s a judgment call
Chapter 3
40
Figure 3-18 Ternary relationship as an associative entity
Chapter 3
41
Microsoft Visio
Notation for Pine
Valley Furniture
E-R diagram
(Figure 3-22)
Different modeling
software tools may
have different
notation for the
same constructs
圖例可能略有不同
Chapter 3
42