Making an ERD: When to Use Designer When to Use Your Brain

1
Making an E/RDWhen to Use DesignerWhen to
Use Your Brain

• A presentation on using
• Oracle Designer to create an
• Entity/Relationship Diagram
• for a useful database.

2
Mike Moxcey

• Programmed in Oracle for 14 Years.
• Built forms in Oracle 4 and 5
• Used ReportWriter 1.0 and 1.1
• Heavy experience in SQL, SQLPlus and DBA
• Built Web Sites with Perl and Oracle

3
I work for the President George Bush

• White House
• US Department of Agriculture
• Animal and Plant Health Inspection Services
• Wildlife Services
• Operations Support Staff
• Management Informations Systems
• Me

4
Both of us are often called by     our middle
initial

• He's W
• (because of his Dad)
• I'm S
• (because of another guy at work named Mike)

5
Programmer for Wildlife Services

• Providing Support for Existing Oracle 7 System
• Building Completely New System in Oracle Designer

6
Worked in the Same Overall Agency

• APHIS IT
• Plant Protection and Quarantine
• Veterinary Services
• Wildlife Services
• Not a consultant who leaves

7
Overview 3 Parts to this Talk

• Why Use Users?
• How to Use Users
• How to Use Designer
• Making an E/RD
• Generating Prototype Forms

8
Why Use Users?

• You're supposed to
• All the mgmt books say so
• It's good karma

9
Definition A successful system is one that is
used.

• Not one that works correctly
• Not one with an elegant design
• Not one that's finished

10
Everyone Hates Data Entry

• Data is good for managers.
• Workers just want to do their job.
• Painful to enter pointless data
• Less painful for useful data

11
Best Data Entry Is Part of Job

• Grocery Store Scanners
• Time Cards
• Keystroke Counters
• Can design without users
• (You have management buy-in)

12
Most Data Is Entered afterthe Job Is Done

• Estimate how long it took
• Write down your mileage
• I'm focused on next task

13
Good Data Requires User Buy-In

• Imagine the Dilbert boss wants a DBS for
  programmer time
• Hours/form or procedure
• Lines of form code

14

• What would you want to measure?
• Form size, procedure complexity
• What could you measure well?
• Time, lines of code, speed of procedure
• What can't be measured?
• Elegance, maintainability

15
Get the Users to Design Their System

• You're the Architect Not the Client
• You're the Builder Not the Buyer

16
If Management Isn't Behind the System Put the
System Behind You

• Slam it out and move on

17
Get a Variety of Users Get Good Users

• Don't accept "Spares"
• Make management assign the workers
• Otherwise forego the group and work one on one

18
Get a Time Commitment

• Need to train the users
• Need to forge relationships
• Need to build trust

19
Teaching Database Designto Users

• Tables are like a spreadsheet without any
  repeated data
• Data integrity is critical
• Links between tables make the database work

20
Know the Modeling Concepts

• Entities
• Relationships
• Foreign Keys
• The Data Model Resource Book by Silverston,
  Inmon, and Graziano

21
Brainstorm on Entities

• Entities will become tables
• (don't explain why "Account" won't exist)
• Entities are nouns
• person, place, thing, event, or concept

22
Consolidate Ideas and Terms

• Listen! Don't talk.
• Hear what's important and why.
• Take notes!
• Create list of Candidate Entities
• Some Nouns Are Attributes Now

23
Teach Relationships In-depth

• Demonstrate Foreign Keys
• Two Parts to Relationships
• Optional vs. Mandatory
• One vs. Many

24
Show Foreign Keys

• Employee Table
• ID
• NAME
• Emp Records
• George
• Nancy
• Lestat

• Job Table
• ID
• NAME
• FKEY
• Job Records
• Combine 2
• Stir 1
• Serve 2
• Consume 3

25
Ask Questions

• Songs and Authors Entities
• Optionality
• Must a Song have an Author?
• Must an Author have a Song?
• Cardinality
• Can an Author write more than one Song?
• Can a Song have more than one Author?

26
Draw Relationships

• Discuss and verify them
• Make Associative Entities
• This is a tricky concept for users.
• Explain it well.
• Explain why the Account" entity won't exist
• Argue about Intelligent Keys

27
Show Associative Entities

• Songs and Authors Example
• Song Song_Author Author
• Name ID Name
• IDSONG_ID History
• History AUTHOR_IDID

28
Using Oracle Designer

• Repository Must Be Installed
• Requires an Account
• Open Designer
• Open the Entity Relationship Diagrammer
• Make Work Areas and Containers
• Name the Diagram ERD_

29
Add Entities

• Name
• Short Name Prefix
• Plural Table Name

30
Create Relationships

• 1. Choose correct type
• 2. Click on From Entity
• 3. Click on To Entity
• 4. Name the Relationship

31
Make Diagram Readable

• Put in about 20 Entities
• Group by Subject Area
• This Should Work
• Try Select All and AutoLayout
• Arrange/Resize Entities

32
Better-Looking Diagram

• 1. Select Relationship
• 2. Choose Edit Select Same Type
• 3. Click Autolayout

33
Domains

• Use E/R Diagrammer or RON
• Enter Name,
• Datatype, and
• Maximum Col Length

34
Enter Attributes

• Can be done as you do Entities
• EntityProperties has many tabs
• Attributes Name, Optional, Primary
• Att Detail Apply domains

35
Checking the Design

• Run Scenarios
• (capture info during meetings)
• Use Reports for Users
• Open Repository Reports
• Entity/Relationship Modelling
• 8 different reports
• Use the Previewer, not the Parameters

36
Alternate Reports

• Perl to strip/reformat
• Open .rdf in Developer
• Write own SQL scripts

37
Fine-tune diagram

• Don't need users
• Drop weak entities
• Look for common data
• Look for unneeded data

38
Categories

• These are how users organize data
• They drive the reports
• Verify groupings with users

39
Categorization Questions

• Can it only ever be one kind?
• Apple or Pear
• Are there subkinds?
• MacIntosh or Red Delicious
• Are there different kinds of kinds?
• Apple is Red, is Fruit, is Round

40
More Categorization Questions

• Can it change kinds over time?
• Can an Apple become a Pie?
• Can it be multiple similar kinds at once?
• Piano is both String and Percussion
• Trees Natural Resource, Landscaping,
  Silviculture

41
Set up Types (can only be one)

• Set up Classes, Categories, etc.
• Set up Hierarchies
• Sorting Data is aJob for Users
• This group needs to be permanent
• They are your data librarians

42
Building the System

• We know we want a database
• Don't need Process Modeller
• Don't need Dataflow Diagrammer

43
Function Hierarchy Diagrammer

• Make a Function for every form
• Assign Entity Usages
• Run Utilities Function/Attribute Matrix to
  assign IRUN for attributes

44
Create Tables

• Run Database Design Transformer
• Design Editor run Generate Generate Database
  from Server Model
• SQL Execute the Table Creation scripts

45
Create Candidate Modules

• Transform Preliminary Designs Application Design
  Transformer
• Select top function
• Design Editor
• Modules Tab
• Modules are Named, Numbered
• Rename and Uncandidate Them
• (Properties Candidate? No)

46
To Regenerate

• Design Editor Delete Unneeded Modules
• Design Editor Delete Table defs (views, seqs,
  etc.)
• SQL Drop Tables and Sequences
• (by script or by user)

47
Take Small Steps

• Make a Test Work Area
• Create Tiny Modules
• Read the Help screens

48
Oracle Designer is a complex tool System
design is a complex process

• Use Organization's collective brain power for a
  good system.
• More info at home.att.net/mike.moxcey/pgm/
• www.aphis.usda.gov/mmoxcey/