Converting An Essential Entity Relationship Model Into A Real

Скачать презентацию Converting An Essential Entity Relationship Model Into A Real

ef594205046da522739128d6ada5856d.ppt

Количество слайдов: 41

Converting An Essential Entity/Relationship Model Into A Real Database Design David C. Hay Enterprise Data World David Hay Enterprise Data World Austin, Texas Tampa, Florida May 1, 2014 April 6, 2009 Essential Strategies International 13 Hilshire Grove Lane Houston, TX 77055 http: //essentialstrategies. com Copyright © 2009 David C. Hay 1

How to achieve flexibility and robustness. . . r Generalize entity classes, l Each describes as large a population of phenomena as possible. l For example, a Party is a Person or an Organization that is of interest to the company. Organization in this case can then have more specific sub-types, like Company, Government Agency, Household, etc. r Separate roles from the definitions of things. l For example, an “Employee” is a Person who is employed by an Organization, such as a company. A “Vendor” is a Party who is a vendor in an Order. Copyright © 2014 David C. Hay 4

How to achieve flexibility and robustness. . . r Put as much of the language of the business as instances of. . . type entity classes. l This includes categories, like Activity Type and Product Type. r Treat nearly all attributes as being multivalued, requiring a separate entity class. l For example, Party Characteristic, with an intersect entity class Party Characteristic Value each instance of which contains a “Value” of a Party Characteristic for a particular Party. r Essential Data Model – l General concepts l Super-set of user views Copyright © 2014 David C. Hay 5

The End User’s view. . . r The end user, on the other hand deals with very concrete, particular things. r The user interface must reflect the way the user deals with things today. r The behavior of the system is an extension of the user’s behavior. r Ideally ‘e participated in the modeling and agreed with the overall concepts. r But those abstractions have little to do with today’s problems. End User Copyright © 2014 David C. Hay 6

Designer’s Assignment. . . r Designers may not be experienced with models this abstract. r This paper is intended to present some of the more basic steps required to convert an essential data model into a database design. r It turns out that abstract models are implemented using the same steps as not so abstract models. Copyright © 2014 David C. Hay 7

Connections to System Users. . . r Conceptual Data Model – l General concepts l Super-set of user views r User Views – l Concrete terms l Habits and personal preferences r Database and Application Design l True to conceptual model l Accommodates technological limits l Makes user views possible Copyright © 2014 David C. Hay 8

UML Alert! r Both the Essential Data Model and the Relational Database Design shown here use constrained versions of the Unified Modeling Language. r Translation: UML Symbol Essential Symbol Design Symbol Class Entity Type Table Association Relationship Foreign Key Attribute Column Inheritance Sub-type (resolved) Copyright © 2014 David C. Hay 9

The Default Database Design. . . r Each entity class becomes a table. r Each attribute becomes a column. r Each primary identifier becomes a primary key. Each component of the identifier is a reference to a column in the table. r Each role on the “many” end of a relationship becomes a foreign key, composed of pointers to the columns in the other table’s primary key. r If a relationship from table A to table B was part of a unique identifier, the columns in table A that are the foreign key implementation of that relationship become part of the primary key for Table A. Copyright © 2014 David C. Hay 12

Resolve sub-types r V 1: One table for the super-type. l All attributes from all sub-types become columns. l Cannot meaningfully enforce mandatory columns. l Requires adding “type” column. r V 2: One table for each sub-type. l Attributes for super-type plus sub-type form columns for each sub-type table. l Foreign keys for each relationship linked to supertype in each sub-type table. r V 3: Combinations l Most complex. l Most “true”. Copyright © 2014 David C. Hay 16

Design Computed Columns. . . r Compute on input: l if values are relatively stable, or l if retrieval volume per day is significantly greater than update volume. l Maintenance is required to keep values consistent. r Compute on output: l if values are relatively dynamic, or l if retrieval volume is relatively low. l Additional maintenance is unnecessary. r Kinds of calculations: l Simple: A*B+C l Inference: INFER-THRU (, , ) l Summation: SUM-THRU (, , ) Copyright © 2014 David C. Hay 21

For example, this model. . . /Price INFER-THRU (to buy, Product Type, Unit Price) /Value = Quantity * Price /Total Sales to Date = SUM-THRU (bought via, Line Item, Value) Copyright © 2014 David C. Hay /Contract Value = SUM-THRU (composed of, Line Item, Value) 22

Could be implemented thus. . . Computed on input (and stored) Computed on query* * Note that translating the formula into, for example, a stored procedure, is left to the viewer. Copyright © 2014 David C. Hay 23

Inherit from reference tables. . . PARTY # GLOBAL IDENTIFIER * NAME PERSON buyer in seller in ORGANIZATION * DESCRIPTION COMPANY CONTRACT NUMBERfrom # * ISSUE DATE * (TOTAL VALUE) LINE ITEM # * o * part of LINE NUMBER QUANTITY COST (VALUE) buyer in to composed of seller in INTERNAL ORGANIZATION GOVERNMENT for GOVERNMENT AGENCY OTHER ORGANIZATION purchased via PHYSICAL ASSET SPECIFICATION # * * * o o * GLOBAL IDENTIFIER DEFAULT NAME DESCRIPTION EFFECTIVE DATE DISCONTINUE DATE STANDARD PRICE (TOTAL SALES VALUE) PRODUCT an example o f embodied in MATERIAL PARTY TYPE a sub-type of # NAME * DESCRIPTION This model. . . Copyright © 2014 David C. Hay a super-type of 26

Could be implemented as. . . CONTRACTS CONTRACT_NUMBER(PK) ISSUE_DATE TOTAL_VALUE BUYER_NAME SELLER_NAME LINE_ITEM CONTRACT_NUMBER(FK) PRODUCT_NAME QUANTITY COST STANDARD_PRICE VALUE Copyright © 2014 David C. Hay 27

Note: r When replicating values, recognize the maintenance required to keep them consistent. r Note that the paradigm of INFER-THRU and SUM-THRU already anticipated this. r If these are implemented as dynamic columns, maintenance is automatic. r If they are implemented as static copies, maintenance must be added. Have you heard this before? Denormalization replicates computed fields Copyright © 2014 David C. Hay 28

Split Horizontally (instances). . . r For example, l By Geographic Area m Some tables for North American customers m Some tables for European customers m Etc. l By Customer Type, etc. m Some tables for corporate customers m Some tables for individual customers m Etc. r Note the problems that will arise if a significant number of customers (for example) fall into more than one category. Copyright © 2014 David C. Hay 29

Split Vertically (columns). . . r For example, l People with customer attributes m Annual sales m Sales representative m Etc. l People with employee attributes m Social security number m Employment date r Note that people with both kinds of attributes would appear redundantly in both tables. Copyright © 2014 David C. Hay 30

NOTE. . . r De-normalization optimizes some operations at the expense of others. r Test the effects before making them permanent. r Document the rationale for the denormalization. Copyright © 2014 David C. Hay 31

About those parameters. . . r Some entity classes (Party, for example) invariably have a lot of attributes. r And they change over time. r Their definitions change over time. r We need an alternative. r Define attributes as data. r Also called: l Characteristics, l Parameters, l Variables, l Etc. r Here’s an approach for Party, for example Copyright © 2014 David C. Hay 33

Party parameters as “Characteristics”. . . PARTYCHARACTERISTIC VALUE: “Height” of “Jerry Smith” has CHARACTERISTIC VALUE of “ 6. 1” (feet)… according to “Jerry Smith”. PARTY CHARACTERISTIC: “Height” “Number of employees” “Regulatory target”, Etc. Copyright © 2014 David C. Hay 34

Party Characteristic Constraints. . . NOTE: the CONTINUOUS PARTY CHARACTERISTIC “Height” -- may only be used as a PARTY CHARACTERISTIC VALUE -- for a PARTY that is an example of the PARTY TYPE “Person”. Copyright © 2014 David C. Hay 35

Designing those parameters. . . r While it is a powerful way of dealing with the complexity of data … r …the Parameter Model makes common manipulations harder, however. r Convert parameters that are. . . l Relatively stable l Not multi-valued l (Over time? ) r Do not convert parameters that are. . . l Multi-valued l Changeable over time and this must be reported. Copyright © 2014 David C. Hay 36

Can be implemented thus. . . r These Characteristics: PARTY CHARACTERISTIC Name Birthdate Height Annual Sales Tax ID Description (Party Type) The day the person appeared Person Vertical distance Person Average sales in a year Company IRS tax identifier Company r Could be implemented as: Bad idea! PEOPLE BIRTHDATE HEIGHT COMPANIES ANNUAL SALES TAX IDENTIFICATION NUMBER Copyright © 2014 David C. Hay 38

Four Steps to Design 1. 2. 3. 4. 5. 6. Perform default database design Resolve sub-types. Design computed columns. De-normalize as necessary. About those parameters. About those user views. Copyright © 2014 David C. Hay 39

In summary: About Those User Views. . . r The database designer need only balance data model integrity with performance issues. r The application designer must take the data as organized in a database and present it reasonably to each particular end user. r This requires skill in understanding both the database and the underlying data model. Copyright © 2014 David C. Hay 40