Introduction to data modeling l.jpg
Sponsored Links
This presentation is the property of its rightful owner.
1 / 46

Introduction to Data Modeling PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

Introduction to Data Modeling. Presented by Bert Scalzo, PhD About the Author. Oracle DBA for 18+ years, versions 4 through 10g Worked for Oracle Education & Consulting Holds several Oracle Masters (DBA & CASE) BS, MS, PhD in Computer Science and also an MBA

Download Presentation

Introduction to Data Modeling

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Introduction to data modeling l.jpg

Introduction to Data Modeling

Presented by Bert Scalzo, PhD

About the author l.jpg

About the Author

  • Oracle DBA for 18+ years, versions 4 through 10g

  • Worked for Oracle Education & Consulting

  • Holds several Oracle Masters (DBA & CASE)

  • BS, MS, PhD in Computer Science and also an MBA

  • LOMA insurance industry designations: FLMI and ACS

  • Books

    • The TOAD Handbook (Feb 2003)

    • Oracle DBA Guide to Data Warehousing and Star Schemas (Mar 2003)

  • Articles

    • Oracle Magazine

    • Oracle Technology Network (OTN)

    • Oracle Informant

    • PC Week (now E-Magazine)

    • Linux Journal


About quest software l.jpg

About Quest Software

We provide application management solutions that enable customers to Design, Develop, Deploy, Manage and Maintain enterprise applications without downtime

What is application management l.jpg

What is Application Management?

A holistic approach of managing the entire

application not individual components

How do we do it l.jpg

How Do We Do It?

We surround the application infrastructure:

Application Monitoring

High Availability

Microsoft Infrastructure Management

Database Management

Purpose and overview l.jpg

Purpose and Overview

  • Purpose

    • Introduce very basic data modeling concepts

    • Explain how data models relate to the business

    • Explain how data models relate to databases

    • Show some data modeling tools and their use

  • Overview

    • Why do we model data

    • What is data modeling

    • What are entities and attributes

    • What are unique identifiers

    • What are relationships

    • Special constructs: Inheritance & Dependencies

    • How to translate from logical to physical

    • How to generate a basic physical database

Why do we model l.jpg

Why do we Model?

Would you build an office without a blueprint?

The Architect will create the first high level drawings to validate the concept with the client and then make a more detailed plan (i.e. the blueprint ) for the Contractor …

The Contractor will take thisblueprint and optimise it basedon technical constraints. The Contractor will then create the actual office.

World of modeling l.jpg

World of Modeling …

  • Improve process efficiency

  • Define/document Bus. Processes - create correct and complete application requirements

Business Process Modeling


  • End-user

  • IT Partner/Liaison

  • Business Analyst

  • Support for all UML diagrams - Analyze requirements - Design application

  • Reverse/forward engineer code

Object-Oriented Modeling


  • System Architect

  • System Analyst

  • App Developer

  • Identify all data & relationships - E/R (Entity/Rel’ship) diagrams - DB independent view

  • Business Rules?

Conceptual Data Modeling

(CDM – E/R)

  • Bus. Analyst

  • Data Architect

  • Data Analyst

  • DB-specific model

  • Reverse engineer existing DB

  • Create/Update DB from model

  • Data Warehouse Modeling

Physical Data Modeling


  • DBA

  • DB Developer

  • DB Architect

QDesigner synchronizes models from all levels in a single tool

What is data modeling l.jpg

What is Data Modeling?

  • Conceptual (sometimes called Logical)

    • Gather data requirements for business functions being analyzed

    • Think “WHAT???” – “What data necessary to do the business function”

    • It’s all about the business, do not impose “Information System” terms or techniques

      • No such business things as tables, keys, indexes and the like

      • Probably best done by business analysts in sync with BPM

      • In some shops, data modelers can bridge the business/IT gap

What is data modeling10 l.jpg

What is Data Modeling?

  • Physical

    • Translate logical model into an implementation plan for a database

    • Think “HOW???” – “How to construct database objects to realize this“

    • It’s all about the target database or other technology being adopted

      • Now it’s time to think about tables, keys, indexes and the like

      • Probably best done by data architects, data modelers, and DBA’s

Where in development lifecycle l.jpg

Where in Development Lifecycle

Some shops just treat this as one big “Design” task











Simple erd scenario l.jpg

Simple ERD Scenario

  • We’ll use a very simple ERD scenario to explain the techniques

  • Basic business requirements for a super simple bank:

    • Customers interact with Employees to

      • Open Accounts

      • Make Transactions

    • There are two kinds of Accounts

      • Checking

      • Savings (offers ATM access)

  • Looks simple, right? Wait till you see the issues with just this …

What are entities l.jpg

What are Entities

  • According to Chen – a thing which can be distinctly identified

    • Peter Chen invented ER diagramming technique in 1976

  • Look again at the two critical aspects of an entity

  • A business pertinent or relative noun

    • Person, place, thing, or concept that has characteristics of interest to the enterprise

    • To find entities, simply look for nouns in your business analysis …

  • NOTE – An entity is not synonymous with a table

Entity meta data l.jpg

Entity Meta-Data

Notation for entities l.jpg

Notation for Entities

  • General Entity Conventions:

    • Represented by a box

    • Name inside top of box

    • Name is

      • Unique across model

      • Business meaningful

      • In its singular form

      • In all upper case

    • Displays no meta-data!!! – (cannot see comment & number)

What are attributes l.jpg

What are Attributes

  • Property or characteristic of an entity

  • Elementary piece of information about an entity

  • An entity pertinent or relative adjective (i.e. modifies noun by the use of describing, identifying, or quantifying words)

    • To find attributes, simply look for descriptive nouns that won’t be used as entities and/or adjectives in your business analysis

    • Each entity will generally have several to many attributes – and each attribute may have a little to a lot of meta-data

      • Take your time

  • NOTE – An attribute is not synonymous with a column

What are unique id s l.jpg

What are Unique ID’s

  • When defining entities and their attributes, the following question should be asked…

    • “What makes one instance or occurrence of an entity different from another?”

      • In other words, how can business people differentiate entity data?

  • For example in our bank scenario, we are told that Customers can be differentiated because no two Customers have the same:

    • Customer ID

    • Federal Tax ID

    • Concatenation of First Name + Last Name + Zip Code

  • NOTE – This has nothing to do with database keys/indexes

Attribute meta data l.jpg

Attribute Meta-Data

Failure to discover & document attribute meta-data is where many data models fail

  • Comment

  • Business Data Type

  • Optional or Mandatory

  • Part of Unique Identifier(s)

  • Checks

    • Minimum

    • Maximum

    • Default

    • Units

    • Format

    • Upper/Lowercase

    • Cannot Modify

    • List of Legal Values

    • Business Rules

  • NOTE - This represents the bulk of the work when data modeling

Failure to capture this meta-data results in ineffective databases: that’s where bad data is allowed

Next few slides show Quest’s QDesigner – other modeling tools will have similar screens

Meta data basic l.jpg

Meta-Data - Basic

Meta data uid s l.jpg

Meta-Data – UID’s

Meta data checks l.jpg

Meta-Data - Checks

Meta data rules 1 l.jpg

Meta-Data – Rules 1

Meta data rules 2 l.jpg

Meta-Data – Rules 2

Notation for attributes l.jpg

Notation for Attributes

  • General Attribute Conventions:

    • Name inside bottom of entity box

    • Name is

      • Unique across entity

      • Business meaningful

      • In its singular form

      • In Init-Cap case

      • Spaces OK (no “–” or “_”)

    • Primary unique id is underlined

    • Primary unique id marked by <pi>

    • Alternate unique id’s marked by <ai#>

    • Data type shown with length

    • Mandatory indicated by <M>

    • Displays limited meta-data!!! – (cannot see comment or any of checks)

Super and sub types l.jpg

Super and Sub Types

  • In the business world, we often find that things (i.e. entities) do not fall into simple, clear cut classifications

    • We sometimes find that things are very similar - with just a few differences

  • In data modeling, this can be referred to as:

    • Entity Inheritance

    • Super and Sub Types

    • Generalization Hierarchies

  • The key question to ask is whether these different classifications are:

    • Exclusive – must be one way or the other

    • Inclusive – can actually be all ways at once

Notation for super sub l.jpg

Notation for Super/Sub

X means Exclusive

Super sub meta data l.jpg

Super/Sub Meta-Data

This Super/Sub meta-data only important for transforming conceptual to physical model…

(next data modeling e-seminar)

Now the relationships l.jpg

Now the Relationships

  • It’s called ER diagramming

    • We did the E for “Entities” first

    • But it’s really all about the R for “Relationships” that really matters

  • Experience has shown that while many people can readily define entities and their unique identifiers, not as many are as successful modeling the business relationships between those entities

  • Definition:

    • A business pertinent or relative connection between two entities

    • A reason of relevance to the enterprise why entities may be associated

  • NOTE – A relationship is not synonymous with a foreign key

Ask lots of questions l.jpg

Ask Lots of Questions

  • The key to quality relationship analysis is to ask a zillion business questions, model the answers, and repeat the process until done …

  • Back to our banking example:

    • How are Customers related to Accounts

      • Can a Customer have one or more than one Account (yes)

      • Can more than one Customer have the same Account (yes - joint)

    • How are Employees related to Accounts

      • Can more than one Employee open an Account (let’s say no)

      • Can an Employee open more than one Account (obvious - yes)

    • How are Transactions related to Accounts

      • Woops – we somehow missed an entity earlier (that will happen)

      • Can a Transaction apply to more than one Account (obvious - no)

      • Can an Account have many Transactions over time (obvious - yes)

Relationship meta data l.jpg

Relationship Meta-Data

Read the relationships l.jpg

Sentence Format:

EACH entity1MAY2role_name31 OR MORE4entity5


Read the Relationships

  • To verify that you’ve got it right, read each relationship to the business analysts or end users to be sure it’s right. And you need to read it for both directions. Yes, that’s lot’s of work …

  • Example:

    • Each employee may open one or more account

    • Each account must be opened by one and only one employee






Dependent relationships l.jpg

Dependent Relationships

In a dependent association, one entity is partially identified by another. Each entity must have an identifier. In some cases, however, the attributes of an entity are not sufficient to identify an occurrence of the entity. For these entities, their identifiers incorporate the identifier of another entity with which they have a dependent association.

Relationships results l.jpg

Relationships Results

Conceptual physical l.jpg

Conceptual -> Physical

  • Check List:

    • Verify everything with the business analysts and end users

    • Verify everything with the business analysts and end users

    • Verify everything with the business analysts and end users

  • Use your software’s model checking utilities and/or reports

    • Every entity must have unique identifier (as per Chen)

    • Resolve many-to-many relationships (cannot be built)

    • Double check isolated entities (i.e. no relationships)

    • Look for very common modeling patterns (next slide)

  • Use your software’s generate physical model utility

  • NOTE – Generated physical model will require DBA review …

Final conceptual model l.jpg

Final Conceptual Model

Employee’s can be Customers – right?

Initial physical model l.jpg

Initial Physical Model

Physical model work l.jpg

Physical Model Work

  • Check List:

    • Verify nothing got lost in translation from Conceptual to Physical

    • Add table(s) required for implementation, but not modeled

    • Use your software’s model checking utilities and/or reports

      • Every table should have primary key

      • Add foreign key relationship meta-data (next slide)

      • Add indexes to support data access needs (lots of work)

    • Use your software’s generate SQL or DDL script utility

    • Review the script (i.e. never just run SQL without looking)

Foreign key meta data l.jpg

Foreign Key Meta-Data

Final physical model l.jpg

Final Physical Model

Physical database l.jpg

Physical -> Database

Carefully pick the DBMS generation options

Example ddl script l.jpg

Example DDL Script

Remember the Conceptual model’s meta-data?

Parting thoughts l.jpg

Parting Thoughts

  • Data Modeling or CASE tools do not automatically = good design

    • Must do complete business analysis

    • Must do adequate Conceptual -> Physical transformation

    • Must add required physical meta-data (tuning & insight)

  • Some of worst databases built result from failure to do the above

  • There are many other modeling issues – this was just a start …

    • Breaking models into sub-models

    • Conceptual-Physical Model compare and sync

    • Physical Model-Database compare and sync

    • Repository based collaborative modeling

    • Normalization and Denormalization

    • Data Warehousing (Star Schema design)

    • etc, etc, etc …

More information l.jpg

More Information

  • Free 45-Day Trial of QDesigner™


    • Compete enterprise modeling solution

      • Business Process Modeling (BPM)

      • Object-Oriented (UML) Modeling (OOM)

      • Conceptual Data Modeling (CDM)

      • Physical Data Modeling (PDM)

  • Save $$$: Upgrade your TOAD/SQL Nav license to the Suite

    • Suites include PDM!!!

  • Modeling White Papers


      • Data Modeling: Common Mistakes and Their Impact

      • Data Modeling: It's Really All About the Relationships

      • Data Modeling: Reality Requires Super and Sub Types

Questions answers l.jpg


  • Login