Download
making an e rd when to use designer when to use your brain n.
Skip this Video
Loading SlideShow in 5 Seconds..
Making an E/RD: When to Use Designer; When to Use Your Brain PowerPoint Presentation
Download Presentation
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

171 Vues Download Presentation
Télécharger la présentation

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. 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.

  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, SQL*Plus 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. 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

  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 ID……………………SONG_ID History History AUTHOR_ID……ID

  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 Entity;Properties 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 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/