Data Modeling and Design Tools

1
Data Modeling and Design Tools
2
Overview

• Brian Geihsler
• Data models, business process models, and
  workflow models
• Research in conceptual data models
• Greig Hazell
• Design tools
• Demo
• Sharjeel Hooda
• Research in data modeling and design tools

3
What is a data model?

• Three types of data models
• High-level/Conceptual
• Describes the data in a way that is close to its
  real world counterpart
• Low-level/Physical
• Describes how that data is laid out on disk
• Representational/Implementation
• Between the high-level and low-level models
• Describes data in a way that end users can
  understand, but is closer to its actual physical
  layout

4
Types of data models

• Representation/Implementational
• Hierarchical
• Network
• Relational
• Object Model (although this could be considered
  high-level)
• High-level/conceptual
• ER/EER
• UML
• IE

5
What do we expect from a conceptual data model?

• Describe real-world objects in a meaningful way
• Entities
• Attributes
• Relationships
• Provide abstractions for the data
• Classification
• Aggregation
• Generalization
• Specialization

6
What do we expect from a conceptual data model?

• Six key characteristics
• Expressiveness
• How much can you say with the model?
• Understandability
• If you looked at the model, how easily could you
  understand the system the model represents?
• Simplicity
• How easily does the model become complicated?
• Minimality
• How much do need to put in the model to represent
  something?
• Diagrammatic Representation
• Does the model have a well-defined diagram?
• Formality
• How strict are the models rules and definitions?

7
Information Engineering (IE) Notation

• Many design tools use this notation for data
  modeling
• ERWin
• ER/Studio
• No standard, but provides the following
  functionality
• Entities
• Relationships
• Attributes (including primary key)
• Subtyping

8
IE Notation Example

• Sample IE diagram (without attributes)

9
Other models

• Business Process Model
• Workflow Model
• Data warehouse design

10
Business Process Model

• Business Process defined
• A specific event in a chain of structured
  business activities
• Business process model
• Design the processes within the Enterprise
  Business Architecture
• Define the business-related entities and
  relationships that comprise a process
• Examples
• Invoicing
• Shipping products
• Updating Employee Information

11
Business Process Model

• Sample model for a purchase order

12
Workflow Model

• Workflow defined
• The defined series of tasks within an
  organization to produce a final outcome
• Workflow model
• Define tasks, processes and activities
• Assign tasks to people
• Analyze and simulate the tasks
• Examples
• Publishing a newspaper article (write, edit,
  proofread, publish)
• Software lifecycle (design, implement, test)

13
Workflow Model

• Example from Adobe LiveCycle Workflow Designer

14
Industry Usage of Data Models

• Implementational Models
• Other than legacy systems, the relational model
  dominates this level
• High-level/Conceptual Data Models
• Database Design ER/EER, IE
• Business Process Models/Workflow Models UML,
  BPMN, or tool-specific representation
• Problems
• Although some standards are in development for
  these models, no universally accepted standards
  exist
• Automating and standardizing the validation and
  correctness of a conceptual model is difficult

15
The Role of Domain Ontologies in Database Design
An Ontology Management and Conceptual Design
Environment

• Vijayan Sugumaran, Veda C. Storey
• ACM Transactions on Database Systems, Vol. 31,
  No. 3
• September 2006
• Conceptual Data Model
• Uses domain ontologies to assist in creation and
  validation of a conceptual database design
• Developed a prototype that would use ontology
  definitions to assist the user in creating an ER
  diagram and validate how well the diagram
  reflects the ontology

16
Modeling Tools

• Data Modeling Tools (DM) define entities and
  identify relationships between these entities
• UML Process Modeling Tools (UML) define objects
  and the associations between these objects.
• Business Process Modeling Tools (BP) define
  business-centric objects and the associations
  between these objects.

17
Key Features of DB Modeling Tools

• Design Layer Architecture specification of
  logical and/or physical model.
• Forward Engineering Schema Generation
• Reverse Engineering Reverse Engineer from SQL
  Syntax or Physical Objects and Properties
• Support for Industry Standards such as XML, SOAP
• SQL Script Generation
• Model Validation
• Automatic Foreign Key Generation

18
Other Features

• Logical model to physical model transformation.
• Model / Database Synchronization
• Data warehouse / Data-mart specific modeling
• Database Documentation Generate physical entity
  relationship diagram reports, logical entity
  relationship diagram report
• Repository

19
Sample Model Tools
20
Comparison of Major Players
21
AMD Tools Market Share 2001 (www.gii.co.jp)
22
DB Design Tools Market Share 2000
(www.gii.co.jp)
23
AMD DB Design Tools Market Forecast
(www.gii.co.jp)
24
Research on data modeling and design tools

• Modeling scientific data and clinical data
• Conceptual modeling tools
• Need for international standard of quality
• Inclusion of knowledge-based system (KBS)
• Conceptual modeling
• Comparing two grammars for ERM
• Patterned versus non-patterned models
• Modeling network data
• Contains moving objects

25
Scientific Data Management in the Coming Decade

• Jim Gray et al.
• ACM SIGMOD Record
• December 2005
• Lack of standard or tool in scientific community
• Need support for data types (arrays, spatial
  text, etc.)
• Use of metadata gives physical and logical data
  independence
• Resulting in problems with data interchange

26
Oracle Life Sciences Applications

• Utilized by 20 of the top 20 pharmaceutical
  companies and 10 of the top 10 medical device
  companies
• Pre-integrated business applications, including
  specifics for medical devices and biotech
• Latest white paper talks about integration of
  clinical data with the application package a
  scalable eClinical suite

27
Oracle Life Sciences Applications

• Key benefits include
• A single-vendor solution to integrate clinical
  and non-clinical data for analysis, reporting,
  and submission
• A comprehensive portfolio of solutions for
  clinical data management, electronic data
  capture, clinical trial management, and
  adverse-event reporting
• Interoperable solutions across the healthcare and
  life sciences industry that will support
  translational medicine and efficient cohort
  identification

28
Theoretical and practical issues in evaluating
the quality of conceptual models current state
and future directions

• Daniel L. Moody
• Data and Knowledge Engineering
• 12 January 2005
• Lack of standard for evaluating quality of
  conceptual models
• Currently evaluated in an ad hoc way common
  sense, subjective opinions and experience evals
• Lack of empirical testing of conceptual models

29
Theoretical and practical issues in evaluating
the quality of conceptual models current state
and future directions
30
Theoretical and practical issues in evaluating
the quality of conceptual models current state
and future directions

• Indication that current proliferation of
  conceptual model frameworks is counterproductive
  unless evaluation standard defined
• Approaches to conceptual model quality range from
  ER models to OO models to dimensional models to
  UML class or use case models
• Future direction needs consensus on quality
  criteria and conglomeration of current research
  to create an international standard

31
The use of a knowledge-based system in conceptual
data modeling

• Solomon Antony, Dinesh Batra, and Radhika
  Santhanam
• Decision Support Systems
• 28 July 2004
• KBS designed and developed for novice database
  designers
• Performance results indicate that KBS was
  significantly better than system with no
  knowledge base
• Restrictive interface was easier to use than
  guidance interface for developers

32
Additional Research Topics
33
Complexity and clarity in conceptual modeling
Comparison of mandatory and optional properties

• Andrew Gemino, Yair Wand
• Data and Knowledge Engineering
• 10 March 2005
• Entity-Relationship Model
• Mandatory properties and subtypes grammar
  produces more complex models than optional
  grammar
• This results in improved representation and
  viewer understanding of a model

34
The Effects of Data Model Representation Method
on Task PerformanceAn Experimental Investigation

• Robert Fuller, Uday Murthy, and Brad A. Schafer
• AAAHQ Infosys Conference
• November 28, 2005
• Comparison of patterned and non-patterned data
  models on task performance
• Patterned model significantly improved task
  performance on error detection and querying
• Participants trained with patterned models showed
  increased model comprehension

35
Modeling and querying moving objects in networks

• Ralf Hartmut Güting, Victor Teixeira de Almeida,
  Zhiming Ding
• The VLDB Journal
• 20 December 2005
• Integrated approach to modeling and querying with
  expressive power
• Model of a spatially embedded network including
  routes and junctions (rather than nodes and
  edges)
• Offer abstract data types for a network and for
  static and moving network positions and regions

36
Demo

• ER/Studio Reverse Engineering

37
Questions?
38
References

• www.onjava.com
• www.webopedia.com
• www.inconcept.com
• http//www.agiledata.org/essays/dataModeling101.ht
  ml
• CS4440 Presentation slides
• Elmasri and Navathes Fundamentals of Database
  Systems