1 presenting data model diagrams - dama-mn · – intuitive – the visualization directly connotes...
TRANSCRIPT
© 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
© 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:
© 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?
© 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:
© 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
© 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
© 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
© 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
∑∑
© 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
© 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
© 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[ –––●[ –––––[ ––––<[
© 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
© 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
© 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
© 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
© 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
© 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.
© 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– ...
© 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
© 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
© 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
?
© 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
© 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)
© 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
© 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
© 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
© 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
© 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