Database Modeling with Visio for Enterprise Architects

1
Database Modeling with Visio for Enterprise
Architects

• John Pawlowski
• MVP, MCSD .NET, MCT
• Brendan Duffy
• MCSD .NET, MCT

2
ORM

• ORM is a fact oriented approach
• ORM models can be used to derive UML class
  diagrams
• Blends well with UML

3
Data Modeling

• Business rules-based database design
• Object Role Modeling ORM
• Conceptual database design using natural language
  facts and examples
• Logical and physical database models
  automatically built based on natural language
  facts
• ORM is
• Understandable
• Reliable
• Expressive
• Stable

4
Facts - verbalised

• ORM is composed of basic facts
• Taxi departs from Ramp
• Taxi company owns a Taxi
• Sometimes the inverse is worth noting
• Ramp is departed from by Taxi
• Taxi is owned by a Taxi company
• Shorthand version is
• Taxi departs from / is departed from by a Ramp
• Taxi company owns / is owned by a Taxi

5
ORM notation Objects Roles

• Objects are represented as ovals
• Roles are shown as rectangles
• Each diagram represents a Fact Type

6
ORM notation - Constraints

• Mandatory constraint
• A taxi company must own at least one taxi to be
  referred to as a taxi company
• Represented by a dot on the object
• Uniqueness constraint
• Every taxi is owned by at most one taxi company
• Represented by the arrow over the role

7
Conceptual Schema Design Procedure (CSDP)

• Assemble information sources into elementary
  facts
• Entering the Fact Types
• Add constraints
• Unique
• Mandatory role
• Value
• Others

8
Object Role Model (ORM)

• Understandable
• Express facts and rules in plain language
• Capable
• Capture more business rules
• Reliable
• Validate rules in English with sample data
• Stable
• Minimize the impact of change
• Executable

9
Fact Orientation

• Data examples are expressed as facts
• How facts are grouped into structures (tables,
  classes, etc) is not a conceptual issue
• Fact-orientation is at a level above
  object-orientation
• Here is where domain expert and modeler should
  communicate

10
Assembling Facts

• For data modeling we need DATA use cases
• ie cases of data being used such as sample
  reports, forms, etc

• A Trip has a Trip Number
• A Trip has a Departure Time
• A Trip has a Destination
• A Trip has a Fare
• A Fare has a Payment Method

11
Demo

• Entering Fact Types

12
Demo

• How do we go from a Data Use Case
• to a Data Model
• Open an ERM model, and import the ORM Model
• Adjust ORM document options for fine control over
  the DB objects created
• Build ERM
• To a Database
• Generate from ERM

13
Further Resources

• ORM official site
• http//www.orm.net
• ORM central
• http//www.ormcentral.com
• ORM in MSDN
• http//msdn.microsoft.com/library/default.asp?url
  /library/en-us/dv_vstechart/html/vstchvsea_ormover
  view.asp
• Course 2090
• Modeling Business Requirements to Create a
  Database using Microsoft Visual Studio .NET
  Enterprise Architect. (3 days)