1 presenting data model diagrams - dama-mn · – intuitive – the visualization directly connotes...

28
© Gordon C. Everest, All rights reserved. Data Model Presentation - 1 DAMA-MN, 2006 November 15 DMODPRE 1 DAMA-MN, 2006/11 Presenting Data Model Diagrams... 35START © Gordon C. Everest Professor Emeritus Carlson School of Management and CCE University of Minnesota so People can Understand them 2 Advanced Database Design Gordon C. Everest Information and Decision Sciences Carlson School of Management University of Minnesota Purpose, Criteria Validation – by users; analyzing facts Strategies to aid Human Comprehension Visualizing Graphic Elements; Layout Partitioning; Levels of Abstraction Focus in context; Navigation Example Diagrams Levels of Abstraction in ORM Verbalization of a Model Diagram (in VisioEA) 431START 8. Data Model Presentation

Upload: others

Post on 08-Sep-2019

2 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 1DAMA-MN, 2006 November 15

DMODPRE

1

DAMA-MN, 2006/11

PresentingData ModelDiagrams...

35START

©Gordon C. EverestProfessor Emeritus

Carlson School of Management and CCEUniversity of Minnesota

so People can Understand them

2

Advanced Database Design

Gordon C. EverestInformation and Decision Sciences

Carlson School of ManagementUniversity of Minnesota

• Purpose, Criteria• Validation – by users; analyzing facts• Strategies to aid Human Comprehension

– Visualizing Graphic Elements; Layout– Partitioning; Levels of Abstraction– Focus in context; Navigation

• Example Diagrams• Levels of Abstraction in ORM• Verbalization of a Model Diagram (in VisioEA)

431START

8. Data Model Presentation

Page 2: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 2DAMA-MN, 2006 November 15

DMODPRE

3

General StrategiesFOR PRESENTING DATA MODELS TO PEOPLE:

DMODPRE

• Verbalize – put into words (elementary fact sentences)– using their vocabulary

• Show some Sample Data• Graphical Diagrams

WHO IS THE DATA MODEL FOR? and WHY?• Business Users• IT / Data(base) AdministratorNeed to browse the data/definition to discover:

– vocabulary, structure, characteristics, meaning

4

Modeling: Choosing Scope

• SCOPE / Boundary- where to look

• FOCUS- what to look for

• DEPTH / Resolution- how much to look for

... based upon our PURPOSE

DMOD

REALITY is Infinite, Complex, Multidimensional, Detailed.- so we must CHOOSE:

Page 3: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 3DAMA-MN, 2006 November 15

DMODPRE

5

A Model is an Abstraction DMOD

Both realities are infinitely complex.NEED some constructs to look for and use in modeling.

AbstractConceptual View

of the Real World

Concrete SymbolsStored on some Medium

LogicalDATA MODEL

MentalModel

Physical (Storage)Model

REALIZATION

TWO PERSPECTIVESof Data:

6

ModelingMODEL = Abstract (Re).present.(ation)

Knowledgein the world

Knowledgeexternalized,formalized, shared.

Knowledgein the head(mental models)

Reality MODELMODELINGPROCESS

DMOD

pres

ent

Re.p

rese

nt

What drives or guides the process?

Page 4: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 4DAMA-MN, 2006 November 15

DMODPRE

7

The Modeling Process

Real WorldUniverse of Discourse

MODELINGPROCESS

MODELING SCHEMEContext

ConstructsCompositionConstraints

MODEL

perceptionselection/filtering

DMOD

METHODOLOGY:Steps/Tasks + Milestones + Deliverables +

REPRESENTATIONAL FORMS:Narrative, Graphical Diagram,Formal Language Statements

(the Syntax)

8

Modeling Methodology

Real WorldUniverse of Discourse

MODEL

DMOD

METHODOLOGYPROCESS PRODUCT

Steps/TasksMilestones

Deliverables

MODELING SCHEMESEMANTICS

ContextConstructs

CompositionConstraints

SYNTAX(Re)Presentation

NarrativeDiagram

Language

Two common confusions:

Page 5: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 5DAMA-MN, 2006 November 15

DMODPRE

9

Purpose of Modeling – the Why

FIRST STEP in the DESIGN phase of Systems Development (BUILDING)*To Facilitate Human Communication, Understanding, Validation.

• Capture semantics – all relevant, important details• Document – record and remember• Understand – learn, raise questions, record answers, refine

• Communicate – shared with all interested parties– Users, stakeholders, management, developers

• Validate – a complete and accurate representation– Internal validation – consistent with the modeling rules– External validation – Who can do this?

SECONDARY:• Blueprint to Build

Satzinger2e, SA&D, Fig 5.2, p.149. DMOD

* Some say that Modeling begins in the ANALYSIS phase.

10

Omitting vs. HidingDMODPRE

MODEL

Reality

Presentation

OMIT unimportant detail

HIDE detail / parts

Page 6: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 6DAMA-MN, 2006 November 15

DMODPRE

11

Data Model PresentationWHEREAS:

• The Reality / World / Universe of Discourse we are modeling is infinitely complex

• A Model Diagram is an Abstraction of Reality• Models are primarily for People• Humans have limited cognitive abilities

– limited short term memory ( 7 ± 2, chunking)– writing to long-term memory takes time & needs help

DMODPRE

THEREFORE:

• Present a data model diagramto help people understand the reality, the design,and thereby validate the model

• We need some principles andappropriate abstraction mechanismsfor presenting a data model diagram to people.

12

Criteria for a Data Modeling SchemeFOR PRIMARY PURPOSE - HUMAN COMMUNICATION

(RE)PRESENTATION CHARACTERISTICS (the syntax):• Visually meaningful graphics

– Intuitive – the visualization directly connotes intended meaning– Unambiguous – not multiple interpretations

• Language independent, i.e., not textual, thus universal

• Consistent, Uniform Notation• Scoping - VIEWING PART OF THE MODEL

• Depth - Levels of Abstraction - SUPPRESSING DETAIL

• Focus - Visual prominence– REFLECTING RELATIVE SEMANTIC IMPORTANCE– STARTING POINT(S), PATH(S) FOR VIEWING

DMODDMODPRE

Page 7: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 7DAMA-MN, 2006 November 15

DMODPRE

13

Communicating a Data ModelMaking a Data model more understandable:• Verbalize - put into words, in the users' vocabulary.• Show some sample data.• In a Diagram with successive unfolding of detail.The Business users/managers/analysts

need to understand the data and its structurein order to use it - queries and reports.

• Browsing the model/schema to discover:– vocabulary– meaning - of objects, etc.– structure– characteristics of attributes and relationships

DMODPRE

14

Presenting the Data ModelDMODPRE

Page 8: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 8DAMA-MN, 2006 November 15

DMODPRE

15

(Re)Presentation

ABSTRACTION* = “leaving something out”:• HORIZONTAL - scoping/partitioning• VERTICAL - levels of detail

DMODPRE

What?Semantics

To Whom?Person

How?

• May involve a mapping from the full model to the view if a change in the view(--see: D. Hay, “Data Model Views,” tdan, 2000 March)

*Webster Dictionary: (n) summary; short version(v) to take out, remove-tion (n) the act of taking away

16

Strategies to Aid Human Comprehension• Promote understanding of the whole, the big picture• Focus attention; Suppress detail; highlight the important;

reflect semantic importance

Help dealing with bigness and complexity:• Differentiation, Encoding (surrogates), Layout• Abstraction / Simplification

- reducing the information presented at one time

– SCOPE - PARTITIONING -- looking at part (FENCE)- grouping –> classification –> decomposition –> hierarchy

– DEPTH -- looking at less; suppress detail• Focus - display local detail in its global context (POINT)

- viewing without, and with distortion• Navigation - over a given model presentation

- windowing - single, multiple (tiled, overlapping)- scrolling, panning, zooming, searching

DMODPRE

∑∑

Page 9: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 9DAMA-MN, 2006 November 15

DMODPRE

17

Visualizing a Model DiagramGRAPHIC ELEMENTS (icons) can differ by:

• size

• value (intensity)

• angle

• texture

• shape

• position

• color

TO REFLECT: – type; real world referent; semantic importance; …

DMODPRE Jacques Bertin, Semiology of Graphics, 1983.

18

Graphic Symbols for Different Entity Types

• Base / Reference

• Dependent / Weak– Redundant with Mandatory constraint

• Value– Often a terminal entity (~attribute)

• Terminal (~attribute)

• Event

DMODPRE

SUGGESTIONS:

CUSTOMER SUPPLIER

ORDER

DISCOUNT

ORDER Discount rate

SALE

Page 10: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 10DAMA-MN, 2006 November 15

DMODPRE

19

Icons• To represent real world entities

DMODPRE

EMPLOYEE

VEHICLE

SALARY $$alaryalaryWhich do you like better?Which communicates better?Which are you more likely to remember?

20

Dan Moody's "Shark" DiagramDMODPRE

Page 11: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 11DAMA-MN, 2006 November 15

DMODPRE

21

Attribute Characteristics• In most schemes the default is OPTIONAL

with Mandatory/Required as a constraintusing some notation, such as: <attribute>R

• Except IDEF1X where the default is MANDATORYand o = optional

• ORACLE: * = mandatory o = optional (3 valued logic)

Designating the IDENTIFIER in a diagram:* # (key symbol) Underline Bold

• Problem if composite (multi-attribute) key– cannot designate alternative/secondary keys

(e.g. as in Access or SQL Server)– IDEF1X, tag secondary keys as 'AKi'

DMODPRE

22

Relationship Characteristics• Use of graphics to depict characteristics

DMODPRE

SCHEME Optional - Dependent Exclusive - Many ...(0) (at least one) (at most one) (M)

ER (Chen) (default) weak entity 1 M

Oracle (Barker) - - - - - ––––––– –––––[ –––<[

IE (Finkelstein) ]––o–– (at the 'other' end) ]––┼– ––┼[ –––––<[

IDEF1X(Bruce,ERwin) Z P Z P Default= 0..M

UML (lower..upper) 0.. 1.. ..1 ..M 1 (exactly)

ORM (Everest) default, or ––o[ –––●[ –––––[ ––––<[

Page 12: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 12DAMA-MN, 2006 November 15

DMODPRE

23

Diagram Layout (“Aesthetics”)RULES, PRINCIPLES, GUIDELINES:

• Different Icon Types– Avoid where the difference is not meaningful– Employ when there is a meaningful difference

• Limit the total number of symbols in a diagram• Ordering and Placement to Focus the reader• 3-valued logic provides increased semantics

– e.g. Mandatory = • optional = o else unknown/unspecified– At most one = many = unknown/unspecified =

• Lines– Avoid crossing Lines (“planar graph”)– Avoid Corners - visually another symbol but without meaning– Prefer Lines be straight, diagonal, curved, or stretch the object

DMODPRE

?

See: David Hay, “Making Data Models Readable,” ISM, 1998 Winter.

24

Planarizing a GraphMinimize or eliminate crossing lines:

DMODPRE

AB

C

D

E

AB

C

D

E

A

B

C D E

Page 13: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 13DAMA-MN, 2006 November 15

DMODPRE

25

Avoid corners – the Phantom RectangleDMODPRE

26

BIRTHDATE

Simplifying an ORM Diagram• Since terminal objects with a single functional dependency are

most common, show them in the simplest way => “attribute”

DMODPRE

EMPLOYEE was born on

• Eliminate the predicate box and the terminal object icon, and assume a many-to-one dependent relationship EMPLOYEE Birthdate

• If mandatory: EMPLOYEE EmployeeName

• If multivalued (M:N):(add Name on Relationship arc?) EMPLOYEE Skill

• If further interest in the attribute, it becomes an entity:(add a Predicate box?)

EMPLOYEE DEPT Budget

• If identifier (1:1): EMPLOYEE EmployeeID*or

SKILL!if Independent

Page 14: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 14DAMA-MN, 2006 November 15

DMODPRE

27

Sample Data Model (Excelerator 1.9)

MAINTDIST Maintenance

District

COMMISSIONCommissioner

COMMWORKCommissionerHours Worked

COMREPORT Commissioners

Report

PETITIONPetition &

Lis Pendens

TRIALSETL Trial and

Settlement

IMPROVEMENT Improvementson R/W Parcel

OTHERBIDS Other Bids

SALESACTSales Action

EMDOMACTEm Domain Action: St vs.

COMASSIGN Commissioner Assignment

LEASE Lease

EDPARCTRK EmDom Parcel

Tracking

LESSEE Lessee

CONTRACTORContractor

REMOVCONT Removal Contract

FINALCERT Final

Certificate

COUNTYCounty

Num | Code... AGREEMENT Agreement

AUTHMAP Authorization

Map

CHARGEIDCharge

Identifier

RWPROJR/W PROJECT

900's or Dash # FEDPROJ Federal Project

PMSSPROJPMSS Project

PROJECTSProject Actions

COMORDACT CommissionersOrders Action

COMMORDER Commissioners

Order

PARTY NAD Party Name & Address

PARTY INT Party toInterest

INTHOLDER InterestHolder

APPRAISER Appraiser

APPRAISAL Appraisal

APPACTION Appraisal

Action & Cert

OCCATTRNY Occupant

Attorney NAD

MEMBERS Household Members

OCCUPANTOccupant

Relocation

RELOCPMTS Relocation

Payments & Appls

DIRPURCH DirectPurchase

SUPHOUSINGSupplemental

Housing

3-5 1-4

5/yr

rare

rare10%

10%

rare

20%

rare

usually 1

rare <99

3%

0-2

?

ROADSECTRoad Section

Cty# |RS#

<.01

<- last

latestV

<3

3%

2 if EG m if 88

Minnesota DOT Right of WayDatabase StructureGordon C. Everest

LEGENDOne )----------EDependent -- --D -- --Orphan -- -- -- -- F -- --Foreign ID -- -- -- -- -->

DMODPRE

PARCELInterest in aLand Parcel

( many

FOCUS

28

SCOPE - Partitioning• Fencing off a part of the Diagram:

– Often helpful to have some overlap of the partitions

APPRAISER Appraiser

APPRAISAL Appraisal

APPACTION Appraisal

Action & Cert

DIRPURCH DirectPurchase

3%

0-2

PARCELInterest in aLand Parcel

DMODPRE

Page 15: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 15DAMA-MN, 2006 November 15

DMODPRE

29

DEPTH - Levels of Abstraction

DISTRICT

COMMISSIONERSORDERS

CONSTRUCTIONPROJECT

LEGALAGREEMENT

CONTRACTOR

AUTHORIZATIONMAP (GRAPHIC)

PARCEL OF LAND

INTERESTHOLDER"OWNER"

APPRAISAL

INTEREST IN APARCEL OF LAND

DIRECTPURCHASE

OFFER

SUPPLEMENTALHOUSING PAYMENT

RELOCATIONOCCUPANT

CONDEMNATIONACTION

IMPROVEMENTS ONLAND PARCEL

IMPROVEMENT

REMOVAL CONTRACT

CONTRACTOR

SALES ACTION

OTHER BIDSAPPRAISER

APPRAISAL ACTION

APPRAISAL

CERTIFIED APPRAISAL

APPRAISER

NAME

ADDRESS

RATINGS

FEE RATES

APPRAISER:

ID NUMNAME, PERSONADDRESS, MAILINGPHONEALTPHONENAME-COMPANY (OPT)DATE OF LAST APPRAISAL

(der)QUALIFICATION RATINGEVALUATION RATINGTESTIMONY RATINGHOURLY FEEWORK AGREEMENT NAMEXPIRATION DATE

DMODPRE

30

Abstraction in Process ModelsISBDFD

Process

Payroll

EMPLOYEESRejected

Time Sheets

TimeSheets

PayrollChecks

DFD: Context Diagram (Level 0)DMODPRE

Page 16: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 16DAMA-MN, 2006 November 15

DMODPRE

31

DFD: Payroll System - Level 1ISBDFD

1.0Edit

Time Sheets

2.0AccumulateTime Sheet

Data

3.0Computecurrent period

earnings

4.0PreparePayrollChecks

TIME SHEET File

EMPLOYEESRejected

Time Sheets

ApprovedTime SheetsTime

Sheets

PayrollChecks

Current periodEarnings

CurrentPeriod Data

Additions

32

DFD: Payroll System – Level 2Detail for Process step 1.0, Edit Time Sheets, in the Level 1 DFD:

ISBDFD

1.1Verifythat all

data elementsare entered

1.2Recalculatetotal hours

worked1.3

Verifythat time sheet is signed

EMPLOYEES

RejectedTime Sheets

ApprovedTime Sheets

Time Sheets

CompleteTime Sheet data

AccurateTime Sheet data

Page 17: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 17DAMA-MN, 2006 November 15

DMODPRE

33

DFD: Payroll System – Level 3ISBDFD

Detail for Process step 1.1, Verify that all data is entered:

1.1.1Verify

EmployeeName and Number

entered

1.1.2Verify

Departmententered

1.1.3Verify

Hours Workedentered

for each day

EMPLOYEES

RejectedTime Sheets

Time Sheets

CompleteTime Sheet data

1.1.4Verify

Total Hoursentered

VerifiedEmployee

data

VerifiedDepartment

Number

VerifiedDaily Hours Worked

34

Abstraction - Generalization• Moving UP to a Supertype

DMODPRE

Product SizeBrandProductVendorShip DateCarrier

Actual DeliverySupplierOrder Date

Can you find matches among these concepts?• Domains• Attributes• Classifications

==> When we generalize to a supertype, type becomes a value.==> Type becomes the distinguishing attribute.

• CAUTION: if you define a more generalized supertype and tables at the level of the supertype, it may be more difficultto define constraints on the subsets (subtype populations).

S. Hoberman, “The Power of Abstraction,”EWSolutions.com/newsletter.

Page 18: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 18DAMA-MN, 2006 November 15

DMODPRE

35

Issues

AGENCY

ClaimsCOLLEAGUE

Invoice

LIBRARY

Line Item

Payment

ThruOrder ?

Order

IndividualSubscriber

OrganizationalSubscriber

X

Line Itembreakdown

X

X

BASE EventOne entry addedfor each Issue

MultiLast issueFK?

CR

Subscription Database (MIS Quarterly)

Current designActive Entities:

What commonalities do you observe?

SSTYPE

36

Supertype example - SubscriptionSSTYPE

SUBSCRIPTION Subscriber

Recipient

Payer

Agency

– Time Period– No. of Copies– Amount paid– Status– History– ...

Who 'owns' the Subscription? Faculty or the Dept/School that pays? Gift Subscription? Who gets the renewal notice?

May be an Individual,Organization/School,or Library.

What is likely to be in common across these parties?

CONTACT

– Type (Indiv,Org)– Roles (S,R,P,A)– PersonName– Pos.Title– OrgnName...– Address– Phone– Email– ...

Page 19: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 19DAMA-MN, 2006 November 15

DMODPRE

37

Simplification using S/S typesDMODPRE

PROFESSOR teaches

INSTRUCTOR

SEMINAR

COURSE

gradesTEACHINGASSISTANT

teaches

teaches

teaches SEMINAR

teaches

PROFESSOR INSTRUCTOR COURSE SEMINAR

TEACHINGSTAFF COURSEteaches

gradesTEACHINGASSISTANT

38

Zooming with Multiple WindowsDMODPRE Harri Siirtola, ER'96, Cottbus, Germany

Page 20: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 20DAMA-MN, 2006 November 15

DMODPRE

39

Distortion: Bifocal ViewHarri Siirtola, ER'96, Cottbus, GermanyDMODPRE

40

Distortion: Fisheye ViewHarri Siirtola, ER'96, Cottbus, GermanyDMODPRE

Page 21: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 21DAMA-MN, 2006 November 15

DMODPRE

41

HECB Student DatabaseDMODPRE

Is this how you would first present the data modelto your users?

What entity or entities are the most important?

42

HECB Student Database

Unfolding detail from the most important:

DMODPRE

PAST, PRESENTor PROSPECTIVE

STUDENT

POST-SECONDARYEDUCATIONAL

INSTITUTION

ENROLLMENT

PROGRAM(Degree/Diploma/Certif)

COMPLETION

FINANCIALAID

?

Page 22: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 22DAMA-MN, 2006 November 15

DMODPRE

43

Student-Course High-Level Data Model Start with

major:• Entities, and

• Relationships

COURSE INSTRUCTOR

STUDENT

DMODPREDMOD

44

Student-Course Data Model

Adding Intersection Entities:• to resolve M:N Relationships• to store additional attributes

STUDENT

REGISTRATION COURSEOFFERING

COURSE INSTRUCTOR

< teaches

in >

offe

red

as >

takes >

DMODPREDMOD

Page 23: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 23DAMA-MN, 2006 November 15

DMODPRE

45

Student-Course Data Model

• Adding AttributesSTUDENT

REGISTRATION COURSEOFFERING

COURSE INSTRUCTOR

StudentIDNameAddress

Major

Course#Title

Credits

Grade

YearTerm

SectionBuildingRoomDays-of-week

TimeStartTimeEnd

SSNNameAddressPhone

Dept

< teaches

in >

offe

red

as >

takes >

DMODPREDMOD

46

Extended Student-Course Data Model B5

STUDENT

REGISTRATION COURSEOFFERING

COURSE INSTRUCTOR

StudentIDNameAddress

Major

Course#Title

Credits

YearTerm

SectionBuildingRoomDays-of-week

TimeStartTimeEnd

SSNNameAddress

PhoneDept

< teaches

in >

offe

red

as >

takes >

DMODPREDMOD

DEPTDeptNo

Name

TEXTBOOK

?AUTHOR

ISBNTitleAuthor(s)

Grade

Description

Office Number

Course# (FK)

StudentID (FK)

CourseOffID (FK)

(City, State, Zipcode)

(FK)

Page 24: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 24DAMA-MN, 2006 November 15

DMODPRE

47

Student-Course Database - Table Diagram

COURSECourse# TitleDescriptionCredits

INSTRUCTORSSNLastNameFirstNameAddressPhoneDept

STUDENTStudent IDNameAddressMajorGPA

COURSEOFFERINGCourse#YearTermSectionBuildingRoomDaysTime StartControlEnrollmentInstructor SSN

REGISTRATIONCourse IDStudent IDGrade

LEGEND:ENTITY NAME (upper case)Identifier (bold face)Attributes (not bold face)Foreign Key Identifier

M:1 relationship

Diagram of the Schema:

Move the arrow head to the other end of the arc?

DMODPREDMOD

48

Student-Course Database – PopulatedInstances ofactual data values

…2Advanced DatabaseMIS4034Intro DatabaseMIS1034Intro MISMIS1014English CompENG1014Intro AccountingACC101

COURSE:Course# Title Credits

…IDSEverest, Gordon77004IDSDavis, Gordon11248CSciCarlis, John64578ACCBoyd, Don85959EngAllen, Lillian33741

INSTRUCTOR:InstrID Name Dept

3.83.22.73.93.4

…MISEverest, Monty5555555ACCDummie, Noe4444444ACCChallenger, X3333333MISBright, Sue2222222MISAble, Emma1111111

STUDENT:StudentID Name Major GPA

276048

770045543211248

2-2072-2241-142

001002001

FallSprFall

…2000MIS10330012001MIS10110172000MIS1011004

COURSE OFFERING:CRSO# Course# Year Term Sect Room InstrID Enroll

Secondary (Composite) Key

…A-77777773001A55555553001A22222223001B11111113001

33333331017B+44444441004

REGISTRATION:CRSO# StudentID Grade

DMOD

Page 25: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 25DAMA-MN, 2006 November 15

DMODPRE

49

Levels of Abstraction in NIAM/ORM

HIDING (generally in order of importance):

1. Lexical Object Types (LOTS); Value Object Types2. “Terminal” Object types – equivalent to / become “attributes”

IF: – play only functionally dependent roles (often only one role)i.e. One:Many relationships; (disjunctive) mandatory (implied)

3. Common Object Types - generic value domains / ref. modes

4. “Event” Object Types5. Dependent (“weak”) Object Types

- Subtypes, Objectified Facts

6. User-defined priority levels on Object Types7. Constraints and Reference Modes8. Predicates

DMODPRE

50

Sample ORM Data Model – EMP-DEPT-SKILL

GIVEN THE FOLLOWING FACTS:

a. Every EMPLOYEE works in one and only one DEPARTMENT.b. Every EMPLOYEE earns an actual annual SALARY in dollars.c. Every DEPARTMENT has one and only one BOSS.d. All BOSSes are EMPLOYEEs in the same DEPARTMENT in

which they work. Conversely, an EMPLOYEE can only be a BOSS if they manage a DEPARTMENT.

e. Every DEPARTMENT reports to one other DEPARTMENT (except the top one).

f. A BOSS may also have a Spending LIMIT in dollars for each transaction, independent of the department they supervise.A Boss’s spending limit is the same across all transactions.

g. An EMPLOYEE may have zero or more (up to 5) SKILLS recorded (as a 4 digit code) with an optional PROFICIENCY RATING (expressed as a number between 1 and 10) for each SKILL (optional because a rating may not always be available).

h. Each unique SKILL code has an alphabetic DESCRIPTION.

DMODPRE

Page 26: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 26DAMA-MN, 2006 November 15

DMODPRE

51

Sample, Simple ORM Data Model

BOSS

LIMITLIMIT

SKILL(code)

RATING

EMPLOYEE(number)

DEPT(number)works in employs

supervises is headed by

reports to superior to

may spend up to of spending for

with proficiency of assigned to

possesses possessed by

"EmployeeSkill!"

{ 1 .. 10 }

{ 1000 .. 9999 }

ac

SALARY(dollars)earns paid to

DESCRIPTION(name)has is of

<=5

DMODPRE

Remove "Terminal" (M:1) Objects

A major criticism of NIAM / ORM, both by protagonists and proponents, is that it is too detailed, a bottom-up design,

BUT… ER Diagrams usually omit the details of attributes and most constraints.

So, present the model using top-down abstractions.

52

ORM Abstractions• Removing "Terminal" (M:1) Objects

BOSS

SKILL(code)

EMPLOYEE(number)

DEPT(number)works in employs

supervises is headed by

reports to superior to

possesses possessed by

"EmployeeSkill!"

{ 1000 .. 9999 }

{ 2000 .. 2999 }

ac

<=5

DMODPRE

Remove Constraints and Reference Modes

Page 27: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 27DAMA-MN, 2006 November 15

DMODPRE

53

ORM Abstractions• Removing Constraints and Reference Modes

BOSS

SKILL

EMPLOYEE DEPTworks in employs

supervises is headed by

reports to superior to

possesses possessed by

DMODPRE

Remove Less Important Objects & Predicates– Subtypes, Objectified Predicates, Reflexive Relationships

54

ORM Abstractions• Removing Less Important Objects & Predicates

– Subtypes, Objectified Predicates, Reflexive Relationships

SKILL

EMPLOYEE DEPTworks in employs

supervises is headed by

DMODPRE

Remove Predicates

Page 28: 1 Presenting Data Model Diagrams - DAMA-MN · – Intuitive – the visualization directly connotes intended meaning – Unambiguous – not multiple interpretations • Language

© Gordon C. Everest, All rights reserved.

Data Model Presentation - 28DAMA-MN, 2006 November 15

DMODPRE

55

ORM Abstractions• Removing Predicates

SKILL

EMPLOYEE DEPT

... Leaving BASE Entities!

A Top-Level Abstract Conceptual Data Modelan ER Diagram ? ! ! !

DMODPRE

56

Needed in a Data Modeling Tool• A Data Model Viewer

– to designate Partitions– to "build" Abstractions (from the most detailed)

successively hiding detail• User designed graphic elements for icons• Hover/click on an icon

to bring up a descriptionand drill down to more detail

• Allow user preferences for graphic notationfor dependency, multiplicity, identifiers, etc.

• Ability to pan/scroll/zoom over a data model

DMODPRE