Foreword xiii
Acknowledgments xv
About the Author xvii
Chapter 1 Introduction 1
Why is There a Need for This Book? 1
To Integrate or Disintegrate? That is the Question 2
Approach of This Book 4
Who is the Intended Audience for This Book? 5
General Industry Models versus More Specific Industry Models 5
Industry Data Models versus Data Model Applications 7
The Volume 2 Models: Customized and New Models for Each Industry 8
Conventions and Standards Used in This Book 8
Entities 8
Subtypes and Supertypes 15
Non-Mutually Exclusive Sets of Subtypes 16
Attributes 17
Relationships 18
Relationship Optionality 18
Relationship Cardinality 19
Foreign Key Relationships 20
Foreign Key Inheritance 20
Intersection or Association Entities to Handle Many-to Many Relationships 21
Exclusive Arcs 22
Recursive Relationships 22
Physical Models 23
Conventions Used for Illustration Tables 24
Conventions Used to Reference Figures 25
The Data Model Resource Book, Volume 2, Industry Download Products 25
Chapter 2 Manufacturing 27
People and Organizations in Manufacturing 31
Product Models in Manufacturing 33
Products and Parts 33
Design Engineering 36
Part Specifications and Documentation 36
Part Specification Roles and Status 40
Engineering Change 41
Product Bill of Material, Substitutes, and Inventory Configurations 43
Product Bill of Materials 44
Part Substitutions 46
Inventory Item Configurations 50
Other Product Models 53
Orders 54
Usage of the Order Models for Manufacturers 54
Materials Requirements Planning 56
Delivery 57
Deployment and Use of Products 58
Deployments 58
Deployment Usage 60
Inventory Ownership 62
Work Effort 63
Process Plan 63
Production Runs 65
Invoicing, Accounting, Budgeting, and Human Resources Models 67
Star Schema for Manufacturing 69
Production Run Fact 70
Dimensions 71
Variation of Product Run Data Mart 72
Manufacturing Summary 73
Chapter 3 Telecommunications 75
People and Organizations in Telecommunications 80
Generic Person and Organization Roles from Volume 1 80
Party Roles and Relationships for Telecommunications 80
Example of Party Roles and Relationships for Telecommunications 83
Telecommunications Products 84
Telecommunications Modifications to the Generic Product Data Models 84
Telecommunications Services 86
Telecommunications Goods 88
Telecommunications Features 88
Product Feature Interaction 89
Telecommunications Product and Product Feature Example 89
Product Deployment 91
Telecommunications Product Associations 93
Network Data Models 98
Network Components 99
Network Assemblies 101
Circuits 104
Product, Circuit, and Network Assembly Capabilities 106
Communication IDs and Contact Mechanisms 109
Orders 111
Service Orders 111
Product Availability 113
Delivery 114
Deployment Usage 114
Invoicing 118
Work Effort, Accounting, and Human Resources Models 120
Star Schema for Telecommunications 121
Product Deployment Usage Fact 122
Customers 123
Deployment Usage Types and Unit of Measures Dimensions 123
Products 123
Facilities 124
Time by Hour 124
Telecommunications Summary 124
Chapter 4 Health Care 127
People and Organizations in Health Care 129
Person Roles 129
Organization Roles 132
Insured Party Roles 133
Party Relationships 134
Party Roles Example 134
Health Care Facilities and Contact Mechanisms 138
Patient, Practitioner, and Provider Information 138
Health Care Products 141
Health Care Offering Definition 141
Health Care “Orders” 144
Agreement Definition 145
Health Care Shipments and Delivery 148
Health Care Episodes, Incidents and Visits 148
Health Care Delivery 151
Health Care Claims 154
Invoice Data Models versus Claims Data Models 154
Health Care Claims Submission 156
Claim Header Information 157
Claim Codes 159
Insurance Policy Information 160
Payment Settlement 162
Health Care Referrals 164
Star Schema for Health Care 165
Health Care Summary 167
Chapter 5 Insurance 171
People and Organizations in Insurance 173
Person Roles 176
Organization Roles 178
Person or Organization Roles 179
Insurance Party Relationships 181
Insurance Product 184
Insurance Products and Categories 185
Insurance Product Coverage 188
Details behind Coverage Types and Coverage Levels 190
Insurance Product Features 194
Insurance Product Rules 196
Insurance Pricing 200
Community-Based Rating 200
Insurance Rate Tables 203
Experienced-Based Insurance Rating 206
Insurance Policies (Orders for Insurance) 206
Insurance Application 206
Insurance Quote 208
The Insurance Agreement or Insurance Policy 213
Insurance Policy Roles 214
Insurance Policy Items 216
Health Care Insurance Agreement 219
Casualty Insurance Agreement 224
Property Insurance Agreement 225
Life Insurance Agreement 226
Premium Schedule 227
Premium Invoicing and Payments 231
Policy Claims 231
Insurance Claim Incidents 232
Insurance Claims Submission 234
Claims Settlement 237
Delivery, Work Efforts, Accounting, and Human Resources for Insurance Enterprises 240
Star Schemas for Insurance 241
Analysis Information 241
Claim Star Schema 241
Fact Table 242
Dimensions 242
Insurance Summary 245
Chapter 6 Financial Services 247
People and Organizations in Financial Services 249
Generic Party Role Subtypes 252
Financial Service Party Roles 252
Financial Service Relationships 254
Financial Objectives, Needs, and Plans 257
Financial Services Products 261
Financial Services Product Definition 262
Product Categories 264
Product Feature and Functional Setting 264
Financial Product and Functional Setting 265
Features and Functional Settings for Product Categories 266
Features and Functional Settings for Products 267
Example of Predefined Financial Products with Product Features and Functional Settings 267
Financial Products That are Customized for the Specific Needs of a Customer 268
Product Category Roll-ups 272
Financial Product Regulations and Rules 273
Agreements 276
Financial Agreements 276
Agreement Subtypes and Roles 276
Asset Roles and Agreement Asset Usage 278
Agreement Status 279
Delivery 279
Financial Account 279
Account Product 280
Account Roles 282
Account Relationships 282
Media 283
Account Status 283
Account Transaction 284
Account Transaction Type 284
Account Transaction Status 286
Account Transaction Relationships 286
Account Transaction Tasks 288
Work Efforts 290
Account Notification 290
Notification Task Types 291
Invoicing and Statement Tasks 291
Marketing Tasks 293
Alert Tasks 294
Other Notification Tasks 294
Example of Account Notification Tasks 295
Analysis Task 295
Analysis Task Data Model 297
Example of Risk Analysis Task 299
Invoicing, Accounting, and Human Resources 299
Star Schemas for Financial Services 300
Analysis Information 300
Account Star Schema 300
Fact Table 302
Dimensions 302
Account Transaction Star Schema 304
Fact Table 304
Dimensions 304
Financial Services Summary 306
Chapter 7 Professional Services 309
People and Organizations in Professional Services 311
Person Roles 313
Organization Roles 314
Professional Services Roles and Relationships Examples 314
Professional Services Products 316
Professional Services Products Model 317
Applicability of Other Product Models 319
Professional Service Product Associations 321
Orders 321
Professional Services Requirements 322
Professional Services Requests and Quotes 325
Professional Services Requests 328
Professional Service Quotes (Proposals and Statements of Work) 330
Engagements 333
Types of Engagement Items 333
Engagement Rates 335
Placement versus Deliverables-Based Consulting 336
Engagement Example 336
Professional Services Agreements 338
Delivery 341
Professional Services Entries 341
Work Efforts 343
Invoicing 346
Accounting and Human Resources Management 347
Star Schema for Professional Services 348
Time Entry Fact 349
Dimensions 350
Professional Services Summary 350
Chapter 8 Travel 353
People and Organizations in Travel 355
Person Roles 357
Organization Roles 357
Party Relationships 359
Travel Preferences 362
Travel Products 364
Product Definition 364
Travel Orders (Reservations) 369
Order Models 370
Ticketing 373
Agreements 376
Travel Agreements 376
Pricing of Agreements and Products 376
Delivery (Travel Experience) 379
Invoicing 383
Work Efforts 383
Travel Programs and Travel Accounts 383
Travel Programs, Rules, and Factors 385
Travel Account Example 385
Star Schemas for Travel 386
Passenger Transportation Offering Star Schema 386
Fact Table 386
Dimensions 387
Non-Transportation Travel Product Star Schema 388
Fact Table 388
Dimensions 388
Travel Summary 389
Chapter 9 E-Commerce Models 393
People and Organizations in E-Commerce 395
E-Commerce Parties, Roles, and Relationships 397
Automated Agent Party Subtype 398
Generic Party Roles from Volume 1 399
E-Commerce Party Roles and Relationships 399
Is There Always a PARTY for a PARTY ROLE? 400
Example Data for E-Commerce Party Roles and Relationships 400
Party Contact Mechanisms in E-Commerce 402
Web Site Content and User Login Information 405
Web Site Content 405
Login Account 407
E-Commerce Products and Objects 408
Orders in E-Commerce 410
Party and Product Needs 412
Subscriptions 414
Web Visits 418
Web Hits and Web Logs 419
Web Visits Model 420
VISIT Information 422
SERVER HIT Information 422
Defining a Visit 423
Delivery, Work Efforts, Invoicing, Accounting, and Human Resources 426
Web Server Hits Star Schema 426
Server Hit Star Schema 427
Server Hit Fact 428
Visitors Dimension 428
ISPS Dimension 429
Referrers Dimension 429
Web Contents Dimension 429
User Agent Types Dimension 430
Products Dimension 430
Time by Hour Dimension 430
Web Visit Star Schema 431
E-Commerce Summary 433
Chapter 10 Using the Industry Models in the Real World 435
Using the Models to Build Transaction-Oriented Systems and Data Warehouses 436
Enterprises in Other Industries 437
Relevance of Models to Very Specific Industries 438
Diverse Enterprises Needing Models from Several Chapters 438
Setting Up Modularized Data Models for Use Across Industries 439
For More Information 439
Appendix A Entities and Attributes for Manufacturing Models 443
Entities and Attributes Listing for Manufacturing 445
Appendix B Entities and Attributes for Telecommunications Models 453
Entities and Attributes Listing for Telecommunications Models 454
Appendix C Entities and Attributes for Health Care Models 463
Entities and Attributes Listing for Health Care Models 464
Appendix D Entities and Attributes for Insurance Models 475
Entities and Attributes Listing for Insurance Models 476
Appendix E Entities and Attributes for Financial Services Models 487
Entities and Attributes Listing for Financial Services Models 488
Appendix F Entities and Attributes for Professional Services Models 497
Entities and Attributes Listing for Professional Services Models 498
Appendix G Entities and Attributes for Travel Models 507
Entities and Attributes Listing for Travel Models 508
Appendix H Entities and Attributes for E-Commerce Models 519
Entities and Attributes Listing for E-Commerce Models 520
Appendix I List of Entities and Their Associated Figures 525
Other Reusable Data Model and Data Warehouse Design Resources 541
Reusable Data Model Resources 541
Reusable Data Warehouse Design Resources 542
How to Use the Volume 2 Industry Electronic Products 543
Contents of the Industry Electronic Products 544
Using the Scripts 545
How to Use the Industry Electronic Products in Conjunction with the Data Model Resource CD-ROM, Volume 1 545
Platform-Specific and Other Information on the Electronic Products 545
Index 547
