Making an E/RD: When to Use Designer; When to Use Your Brain

Making an E/RD:When to Use Designer;When to Use Your Brain A presentation on using Oracle Designer to create an Entity/Relationship Diagram for a useful database.

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, SQL*Plus and DBA • Built Web Sites with Perl and Oracle

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

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)

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

Worked in the Same Overall Agency • APHIS: IT • Plant Protection and Quarantine • Veterinary Services • Wildlife Services Not a consultant who leaves

Overview: 3 Parts to this Talk • Why Use Users? • How to Use Users • How to Use Designer • Making an E/RD • Generating Prototype Forms

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

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

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

Best Data Entry Is Part of Job • Grocery Store Scanners • Time Cards • Keystroke Counters • Can design without users • (You have management buy-in)

Most Data Is Entered afterthe Job Is Done • Estimate how long it took • Write down your mileage • I'm focused on next task

Good Data Requires User Buy-In • Imagine the Dilbert boss wants a DBS for programmer time • Hours/form or procedure • Lines of form code

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

Get the Users to Design Their System • You're the Architect; Not the Client • You're the Builder; Not the Buyer

If Management Isn't Behind the System Put the System Behind You • Slam it out and move on

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

Get a Time Commitment • Need to train the users • Need to forge relationships • Need to build trust

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

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

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

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

Teach Relationships In-depth • Demonstrate Foreign Keys Two Parts to Relationships • Optional vs. Mandatory • One vs. Many

Employee Table ID NAME Emp Records George Nancy Lestat Job Table ID NAME FKEY Job Records Combine 2 Stir 1 Serve 2 Consume 3 Show Foreign Keys

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?

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

Show Associative Entities Songs and Authors Example Song Song_Author Author Name ID Name ID……………………SONG_ID History History AUTHOR_ID……ID

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_*

Add Entities • Name • Short Name = Prefix • Plural = Table Name

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

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

Better-Looking Diagram 1. Select Relationship 2. Choose Edit; Select Same Type 3. Click Autolayout

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

Enter Attributes • Can be done as you do Entities Entity;Properties has many tabs • Attributes: Name, Optional, Primary • Att Detail: Apply domains

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

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

Fine-tune diagram **Don't need users • Drop weak entities • Look for common data • Look for unneeded data

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

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

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

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

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

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

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

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)

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)

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

Oracle Designer is a complex toolSystem 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/

Making an E/RD: When to Use Designer; When to Use Your Brain