Transcript What Are Fact Types?

DAT375
Modeling Business
Requirements using Object
Role Modeling (Part 1)
LeRoy Tuttle, Jr.
Program Manager
Microsoft
Agenda – Part 1
(DAT375)
•
•
•
•
•
•
Visual Studio .NET Enterprise Architect
Object Role Modeling
Database Design Process
Set Theory Review
Object Role Modeling
Conceptual Schema Design Procedure
– Modeling fact types
– Constraining fact types
• Documenting the Model
• Implementing the Model
VSEA Database Design Tools
• Visio-based (VEA)
– Conceptual data modeling (ORM)
– Logical database modeling
(Relational, IDEF1X, “ER”)
– Physical database modeling
(SQL Server, Access, Oracle, DB2, etc.)
– Forward and reverse engineering, sync, import/export,
reports, etc.
• Non-Visio
– Online physical database design tools
– SQL query designer
3
Database
and Software
modeling
Network
diagramming
Visio for
Enterprise
Architects
(VEA)
part of
Object Role Modeling (ORM)
•
Understandable
– Express facts and rules in plain language
•
Capable
– Capture more business rules
•
Reliable
– Validate rules in English with sample data
•
Stable
– Minimize the impact of change
•
Executable
5
Fact-Orientation
•
•
•
•
Data examples are expressed as facts
How facts are grouped into structures
(tables, classes, etc.) is not a
conceptual issue
Fact-orientation is at a level
above object-orientation
Here is where domain expert and modeler
should communicate
6
Examining a Scheduling Theme
Mon
Tue
Wed
Thu
Fri
8 am
9 00
10 00
11 00
12 pm
1 00
2 00
Out of Office:
Doctor’s Appt
Manager
1:1
All-ha
Quart
Meeti
Team
meeting
Off sit
Traini
Class
Brownbag
lunch: ORM
Data
Modeling
Project
Data
Modeling
Project
3 00
4 00
5 00
7
Examining a Scheduling Theme
(Tabular Information)
ID
1
2
3
4
5
6
7
8
Appointment time
Weekday
Hour
Mon
Tue
Tue
Tue
Wed
Thu
Thu
Fri
10:30
9:30
12:00
1:30
1:30
10:00
11:00
9:00
Meeting subject
Team Meeting
Manager 1:1
Brownbag Lunch: ORM
Data Modeling Project
Data Modeling Project
All-hands Quarterly Meeting
Offsite Training Class
Out of Office: Doctor Apt
Location
Building
Room
27
27
117
62
62
117
NA
NA
Conf A
246
101
151
151
101
NA
NA
Duration
Recurring
1.0
1.0
1.0
2.5
2.5
1.5
5.0
2.5
Yes
Yes
No
Yes
Yes
No
No
No
8
Examining a Scheduling Theme
(Object Role Modeling)
Room
(nr)
Activity
(code)
...at...is booked for...
ActivityName
has / is of
HourSlot
(dh)
20
20
33
33
...
Mon 9AM
Tue 2PM
Mon 9AM
Fri 5PM
...
VSC
VSC
ORM
SP
…
VSC
SP
ORM
C#1
...
Visual Studio Class
Staff Party
ORM Demo
Intro to C#
...
9
It is impossible that
more than one Room at the same HourSlot
is booked for the same Activity
Room
(nr)
Activity
(code)
...at...is booked for...
HourSlot
(dh)
20
20
33
33
...
Mon 9AM
Tue 2PM
Mon 9AM
Fri 5PM
...
VSC
VSC
ORM
SP
…
10
It is impossible that
more than one Room at the same HourSlot
is booked for the same Activity
Room
(nr)
Activity
(code)
...at...is booked for...
HourSlot
(dh)
20
20
33
33
...
Mon 9AM
Tue 2PM
Mon 9AM
Fri 5PM
...
VSC
VSC
ORM
SP
…
11
demo
ORM -> ER -> DDL
14
The Baseline Database Design
Process
Modeling business requirements
External
Conceptual
Modeling databases
Logical
Physical
15
Business Context as a Foundation
Universe of Discourse
Record Keeping
Mon
9
1:1
11
22
33
44
55
66
77
88
Appointment
Appointment time
time
Weekday
Weekday
Hour
Hour
Mon
Mon
Tue
Tue
Tue
Tue
Tue
Tue
Wed
Wed
Thu
Thu
Thu
Thu
Fri
Fri
10:30
10:30
9:30
9:30
12:00
12:00
1:30
1:30
1:30
1:30
10:00
10:00
11:00
11:00
9:00
9:00
Meeting
Meeting subject
subject
Team
Team Meeting
Meeting
Manager
Manager 1:1
1:1
Brownbag
Brownbag Lunch:
Lunch: ORM
ORM
Data
Data Modeling
Modeling Project
Project
Data
Data Modeling
Modeling Project
Project
All-hands
All-hands Quarterly
Quarterly Meeting
Meeting
Offsite
Offsite Training
Training Class
Class
Out
Out of
of Office:
Office: Doctor
Doctor Apt
Apt
Thu
Fri
Out of Office:
Doctor’s Appt
Manager
Monetary
MonetaryValue
Valueof
ofInventory
Inventoryby
byPercentage
Percentage
Excel
ExcelMonthly
MonthlyHome
HomeMagazine
Magazine
All-han
Quaterl
Meetin Off-sit
Traini
Class
Team
Meeting
Brownbag
Lunch: ORM
12 pm
ID
ID
Wed
00
10 00
11 00
1
Tue
am
8
8%
8%
Ebony
EbonyMacintosh
MacintoshApples
Apples
17%
17%
11%
11%
Portsmouth
PortsmouthChablis
ChablisWine
Wine
00
2
00
3
00
4
00
5
00
Data
Modeling
Project
Data
Modeling
Project
11%
11%
Denny
DennyToilet
ToiletPaper
Paper
15%
15%
10%
10%
Location
Location
Building
Building
Room
Room
27
27
27
27
117
117
62
62
62
62
117
117
NA
NA
NA
NA
Conf
Conf A
A
246
246
101
101
151
151
151
151
101
101
NA
NA
NA
NA
Duration
Duration
Recurring
Recurring
1.0
1.0
1.0
1.0
1.0
1.0
2.5
2.5
2.5
2.5
1.5
1.5
5.0
5.0
2.5
2.5
Yes
Yes
Yes
Yes
No
No
Yes
Yes
Yes
Yes
No
No
No
No
No
No
Fast
FastGolden
GoldenRaisins
Raisins
Better
BetterCreamed
CreamedCorn
Corn
22%
22%
6%
6%
Bird
BirdCall
CallTartar
TartarControl
Control
Toothpaste
Toothpaste
Musial
MusialMint
MintChocolate
ChocolateBar
Bar
SQL
16
Analysis Is A Joint Activity
•
•
The domain expert
best understands the
application domain
The modeler
elicits and
formalizes this
understanding
17
Who Are Domain Experts?
• Definition
Domain experts are the people who provide
business requirements to the modeler
• Examples
–
–
–
–
–
Subject matter experts
Knowledge workers
Business analysts
System architects
Developers
18
Database Design Roles
External and
Conceptual
Logical
Physical
Principal
database design
roles
Domain experts
Modeler
Modeler
Developer
Developer
Modeler
Focus
Capture
business
requirements
Document
relational design
Implement
relational design
Modeling
methodology
ORM
ER
Transact-SQL
DDL
19
Set Theory Review
• Instances, Populations, Sets, and
Collections
• Instance Relationships
• Set Existence
• Set Forming Operations
Instances, Populations, Sets, and
Collections
•
•
•
•
•
Instance
Population
Set
Member
Collection
Population
A
C
B
E
D
Collection
C
A
D
A
E
D
21
Instance Relationships
• Cardinality
– One-to-one (1:1)
– One-to-many (1:n)
– Many-to-many (n:m)
• Mandatory vs. optional
cardinality
A
1:1
1:n
A
A
A
B
B
B
n:m
B
B
B
B
22
Set Existence
Mutual exclusion
Overlap
B
A
A
B
Identity
A
Subset
B
A
B
23
Set Forming Operations
Union of sets
X
Y
X
Y
X
Y
XUY
Intersection of sets
U
X
Y
Difference of sets
X-Y
24
Conceptual Schema Design
Procedure (CSDP)
Model
Relationships
1) Analyze External Information and Create a Conceptual Model
2) Draw a Fact Types and Apply a Population Check
3) Identify Primitive Entity Types and Reformulate Arithmetically
Derived Fact Types
4) Add Uniqueness Constraints and Check Arity of Fact Types
Constrain
Relationships
5) Add Mandatory Role Constraints and Check for Logical
Derivations
6) Add Value and Set Constraints, and Create Entity Subtypes
7) Add Frequency, Ring, and Other Constraints
CSDP Step 1
Analyze External Information and
Create a Conceptual Model
– Creates the conceptual model
– Fact-driven design
– Object types
– Predicates
– Communicating intent
Fact-Driven Design
• Goals of the modeler
– Represent semantic relationships between
objects
– Capture business rules
– Do not capture business processes
• Purpose of the data model
– Represent allowable states of data in the UoD
– Reflect the scope of the UoD
27
What Are Fact Instances?
• Definition
A fact instance is an individual observation of
the relationship between two or more data
values
• Characteristics
– Are examples of relationships between specific
data
– Are related through an action statement
– May have constraints
28
What Are Object Types?
• Definition
An object type represents the set of all
possible instances of a given object
• Characteristics
–
–
–
–
Generic representations of populations
Always named singularly
Always title case
Object kind
29
What Are Predicates?
• Definition
A predicate is a verb phrase that the domain
expert uses to relate object types
• Characteristics
– Are sentence fragments with holes in them for
object type names
– Describe unqualified relationships
– Have reversible readings
30
What Are Fact Types?
• Definition
A fact type is the set of fact instances that
share the same object types and predicate
relationships
• Characteristics
–
–
–
–
Syntax: [Object type] predicate [Object type]
Arity of a fact type
Generic representation
Reversible predicate reading
31
Practice: Verbalizing Fact Types
(Graphical Information)
London
(LHR)
UK11
UK11
US72
IT37
New York
(JFK)
ES23
US62
US62
US56
US62
Atlanta
(ATL)
US68
VE59
VE56
VE56
Caracas
(CCS)
1
2
Rome
(FCO)
Madrid
(MAD)
VE56
VE56
Dakar
(DKR)
Flight VE56 originates in
Flight
Flight UK11
US62 originates
originates in
in London
Atlanta
Caracas and terminates in
and
andterminates
terminatesininNew
Rome.
York.
Rome.
3
32
Practice: Verbalizing Fact Types
(Tabular Information)
Flight
number
UK11
US72
IT37
ES23
US56
VE59
VE56
US68
Origination
city
London
New York
Rome
Madrid
Atlanta
Caracas
Caracas
Atlanta
Destination
city
New York
London
New York
Atlanta
Rome
New York
Rome
Caracas
Departure info
Terminal
Gate
Time
3
4E
Int’l
3
T
Int’l
Int’l
T
5
19
12
5
9
2
4
8
14:25
1:55
11:12
11:12
23:50
8:03
8:48
4:54
Status
On Time
Delayed
Boarding
On Time
On Time
Canceled
On Time
Boarding
33
demo
CSDP Step 1
34
CSDP Step 2
Draw a Conceptual Model and Apply a
Population Check
– Conceptual data model vs. the diagram
– Meaningful sample population
How Objects Types Are Symbolized
Kinds of Object Types
Entity
Value
Object Type Names and Reference Mode
Name
(Reference)
Name
36
How Predicates Are Symbolized
Role
...
Arity
Binary
... ...
Ternary
... ... ...
Quaternary
... ... ... ...
Role Connector
...
Predicate Phrase
... has .../... is of ...
37
How Fact Types Are Symbolized
Location’s
Time’s
Subject’s
Role
Role
Role
in the
in
in
Object
Predicate
Types
the
Predicate
Predicate
Time
Location
Subject
...at...is used for...
1
2
3
38
Validate Fact Types with a Meaningful
Sample Population
• Data is representative
• Data has meaningful variations
• DATA IS REAL!!
39
demo
CSDP Step 2
40
CSDP Step 3
Check for entity types that should be
combined, and note any arithmetic
derivations
– Trim schema
– Coalesce like sets
– Eliminate use of value types for calculated data
What Is a Conceptual Partitioning
Scheme?
• Definition
A conceptual partitioning scheme is the
systematic separation of the UoD’s
population into meaningful object types
• Characteristics
– Group and division of UoD
– Mutually exclusive partitions
– Exhaustive partitions
42
Partitioning Object Types
Object Types in the Universe of Discourse
Entity Types
Atomic
Value Types
Nested
Strings
Atomic
Atomic
Numbers
Atomic
43
What Are Primitive Entity Types?
• Definition
Primitive entity types represent the most basic
entity types in the UoD
• Characteristics
– Atomic
– Mutually exclusive
44
Example of Primitive Entity Types
Car, Bus
Van, Boat
Vehicle
Student,
Parent,
Firefighter
Person
Location
City,
State,
County
Subject
History, Math
Literature
45
Guidelines for Coalescing Entity Types
Look for commonalities
•
•
•
•
•
Common instances
Common relationships
Common partitioning scheme
Common unit-based reference mode
Common primitive entity type
Look for need to manipulate query results
• Need to perform union on results
• Use intersection of sets to create results
46
What Are Derived Fact Types?
• Definition
A derived fact type is inferred from roles in
other fact types
• Characteristics
– Derivation rule
– Derived vs. derived and stored
– Use of role names
• How derived fact types are symbolized
47
demo
CSDP Step 3
48
Agenda – Part 2
(DAT376)
•
•
•
•
•
•
Visual Studio .NET Enterprise Architect
Object Role Modeling
Database Design Process
Set Theory Review
Object Role Modeling
Conceptual Schema Design Procedure
– Modeling fact types
– Constraining fact types
• Documenting the Model
• Implementing the Model
Concluding Remarks
•
•
•
•
Modeling Business Requirements vs.
Modeling a Database
Use ORM for information analysis
Use data use cases to seed
data models
Use Visual Studio .NET Ent. Architect for
conceptual, logical, and physical database
modeling
Further Resources
•
•
•
•
•
•
•
MOC Course 2090: Modeling Business Requirements to
Create a Database Using Visual Studio .NET Enterprise
Architect
http://msdn.microsoft.com/theshow/ (Episode 25)
http://www.msdn.microsoft.com/library/enus/dv_vstechart/html/vstchvsea_ormoverview.asp
(+ articles on use of VEA)
http://www.orm.net
http://www.inconcept.com
http://www.ormcentral.com
Halpin, T.A. 2001, Information Modeling and Relational
Databases, Morgan Kaufmann (ISBN 1-55860-672-6)
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/