©Mike Moxcey 2002

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

I presented this paper at the Rocky Mountain Oracle User's Group in February, 2002
This paper provides a basic introduction to system design for developers, an overview of Oracle Designer’s form and system generation capabilities, and an in-depth look at its Entity Relationship Diagrammer. We will cover the logistics and politics of building a really good system Entity/Relationship Diagram (E/RD)—the foundation for a decent database—and explain how to fill in the fields in Oracle to generate basic forms for a prototype.

A Good System to Build Good Systems

Developers who have built forms for existing systems, who may have even added a few tables for extra input or for reporting purposes, may think they can use Designer to dump a system out the door really quickly. They can, and “dump” is the operative word here. Which sort of system you build depends on how the business views your system. All major design decisions should be made by the business users. If some boss says, “Just do it and don’t ask me any more questions and don’t bother anyone else neither,” that’s a design decision. Don’t put much effort into gathering requirements or testing. If he doesn’t care about putting out a good system, you shouldn’t either. Do it quick and see if you can move on to some project that really matters (or to some other company that really cares). Dump it out of Designer, or write some table creation scripts and forms. The system isn’t going to be used. Yet even in this case, using Designer would be good because, if by chance, you get to rewrite the system, it’s far easier if you’ve got the information documented inside Designer.

Involve the Users

To design a good system, the business users need to be involved. Step one is to put together a user group with representatives from across the company: actual workers who create data (even if they don’t input it), supervisors, upper-level managers, a vice-president, and someone from every single department who receives reports out of the proposed system (or from the department creating the system). You’ll need these users for at least a week and a few of them will have to be on call for several months. If management isn’t willing to fund a user group like that, then that’s a major design decision. See earlier section. Actually, in this situation, you’d want to develop a good system because it’s going to be used by the line workers no matter what. This is how many current systems were developed. To get valid requirements, interview users on the sly.

Get Good Users

One major problem is some managers will cut corners on the user group by sending someone they can “spare.” You know the kind. Someone who can be gone for a week or month and no one will miss them. DON’T ACCEPT THOSE KINDS OF USERS. You’d be better off simply walking around interviewing people who look busy and developing the system yourself. To convince management to fund a well-selected user group, explain that the group will be making all design decisions. Call the group a System Oversight Board. Tell management that computer people can’t make good decisions about business needs. Explain that if this system is going to cost X amount of dollars and last for this many years, then investing up front in a good user group is crucial. They need to pay dollars for the travel and use political muscle to convince managers to send the selected employees. The IT group should create a list of user types: 3 line employees with skills in certain areas, 2 data entry clerks, 2 line supervisors, 1 VP, someone from the warehouse, and an executive secretary with at least 10 years experience. Then the management team ought to select the best workers to fulfill those needs. Granted, this is an ideal situation, but if you want your system to be successful, then you must aim for success at every step. You can work around poor management by pulling together your own group of users on the sly, but you can’t build a topnotch system for the company if the company doesn’t invest its own time and effort into it.

Running a User Group

Once you’ve got a user group, you must train them. If you’re doing process re-engineering or trying to figure out what it is you want to track in the first place, then you’ll be training the users in process modeling and using the Process Modeler and/or Dataflow Diagrammer in Designer. But if you’re building a database, or redesigning an existing one (which is probably the case if a developer is in charge of this effort), then you’ve got to train the users to think of entities and relationships. Explain that a database tracks information. It keeps data as rows in tables kind of like a spreadsheet. But a well-designed database keeps you from having to repeat information and also prevents information from getting mishandled. But before you go into the user group, you ought to be well-versed in entity-relationship modeling yourself. A good book for that is The Data Model Resource Book by Silverston, Inmon, and Graziano. You won’t need to explain (or use) all the details in that book, but you’d better understand them so you can explain the principles to users. Chapter 4 is particularly enlightening about how the standard purchase order model taught in computer science classes is wrong. The book may make you think you can drop the user group and create the system. Don’t. There are many decisions and you want an E/RD that reflects the specific needs of the business paying for the system.

Entities

Tell the users the first thing we want is a list of entities. These are things we want to track information about. They are nouns. An entity is a person, place, thing, event, or concept. An event could be an airplane flight or a concert. A concept can be an invoice or an account. Don’t explain at this time why an account entity won’t exist in the system. We’re trying to get an initial list of things that are important to the business. If you’ve got a good group with management backing, then you’ll probably have a professional facilitator and note-takers. During this brainstorm (standard technique of no criticizing but asking for more information is allowed), one person needs to be writing the nouns up front. One other developer ought to be writing down the verbs. These will end up as processes if you track them or in the test scenarios you’ll be writing. If it’s a small system or management isn’t willing to invest any time but you are, you can do this same thing by interviewing users. The brainstorming won’t be exhaustive, but will be useful.

Attributes

After the initial entity brainstorm subsides, you can explain that attributes are things that describe entities. For example, an employee will have a name, rank and serial number. A flight might have a number, date, time, and amount of passengers. A concert may have a date, time, venue, and headline act. Go over the list of nouns on the board (or more likely on paper taped all around the room), and try consolidating them. Some nouns are probably the same thing. The discussion on what aspects are the same and what are different is a critical one to have. LISTEN carefully. The developer or designer should not be talking at this time. You are discovering what’s important to the business users and why it’s important. These discussions may take a day or more. During them, new nouns may be added to your list. Verbs and scenarios will be added to the list kept by a cohort. After awhile, the group will come to a consensus about which are entities they care about and which nouns are attributes of other entities. At this point, you’ll have a list of candidate entities. Do not put them into Designer. For one, it’s not a good on-the-fly tool. Creating an entity creates lots of stuff in the background. But the main reason is that this list will still have an Account entity on there. You could have explained earlier why it won’t exist, but that confounds the users which tends to prevent good brainstorming.

Relationships

Now is the time to explain how relationships in a database work. The users have worked with you for a day or more and are beginning to enjoy trying to think like philosopher-geeks. They trust both you and the process a little more, and are feeling comfortable with database concepts. I use an example of the entities Songs and Authors to explain these concepts (See Introduction to Database Design on my web site at http://home.att.net/~mike.moxcey/pgm/ ). The power of a database lies in the relationships between tables. You can pull up an employee and use links (relationships) to discover all the work she's done in the past year on a variety of accounts and machinery.

Foreign Keys

Starting with a Song entity and an Author entity, you explain how a link is created. You don't want to carry the Author's last name in the Song table. What if you had three Authors: Stephen Foster, Barry Foster, and Suzy Foster? What you do is create an arbitrary column called ID (or SEQ_NR) that is a number (from a sequence generator) and then each Song record will have an attribute (also called a foreign key) which holds the ID of the appropriate Author record. Show them an example with attributes containing values. Explain how relationships have two key factors: optionality and cardinality.
  1. Is the relationship optional or is it mandatory?
  2. Is there one related record or can there be more than one?
The users have to truly understand these concepts. If they think a relationship exists between two entities, then they need to answer the questions of optionality and cardinality in each direction. There are 4 questions for each relationship. The answers determine the entire functionality of the database.

Optionality

For any relationship that exists between two entities, you must answer this question: For a given record in entity 1, MUST there be a record in entity 2 or is it optional? Then you ask the same question again from the perspective of entity 2. If a record exists here, does a record have to exist in entity 1? Using the Song example, we would ask that if a Song exists, MUST it have an Author? The philosophical answer is: Yes, every Song has an Author someplace. The real answer is: There are lots of Songs I'll enter without Authors. One concept to get here is you're looking for the exception of the MUST. Will there ever be a record that doesn't need joining to the other entity? Now look at the other end. Will an Author exist without having written a Song? At first, I'd say no. The only reason to put an Author in is because he's written a Song I want to track. However, if the join is required, then I can enter an Author only if I entered his Song first. There may be ramifications for data entry that make a relationship optional when it seems at first it ought to be mandatory. The main thing we're looking at here is what does the business have the will and means to capture data about? That's why you ask users these questions instead of developing the system in a vacuum of ideal philosophies and data principles.

Cardinality

The Cardinality question is: Will there be at most one record at the other end of the relationship or could there be more than one? We only care about one or more than one so we only need to look for an example that has 2 or more joined records. For the Song entity, the question would be: Could a Song have more than one Author? Of course. Just think of the classic teams of Rogers and Hammerstein or Lennon and McCartney. This is called a MANY relationship. Now ask the question from the other side: Can an Author write more than one Song? Of course, so this is a MANY relationship in that direction, too.

Associative Entities

You cannot have a Many-to-Many relationship. These are resolved using an Associative Entity. In this example, we'd create a Song_Author entity that has only a foreign key for each of the other two tables:

           Song_Author_Association
Song              Seq_NR
Seq_NR............Song_Seq_NR                  Author
Name              Author_Seq_NR.................Seq_NR
History                                         Name
                                                History
 

Explain how these are drawn on the diagram with a crows foot representing a MANY relationship coming into that entity and a dotted line means the relationship going out from that entity is optional. The users really have to understand this because they need to verify your model. Show them the two separate relationships first:
 
 
 


 

Then show them how they will appear as one line in Oracle Designer.
 


 

Once the entities and relationships are entered into Designer, there are English language reports the users can read , but you don't want to get into Designer until the relationships are fairly stable. Use a white board or write on transparencies.

No Account

There will be much rewriting. Many bogus relationships and entity arrangements will occur until understanding jells. Explain how Account will only be an ID_NR in an associative entity and the necessary data will be kept in other entities such as Customer, Address, and Purchase. Tackle pieces of the diagram. Try to get all the Employee info tied together well. Then look at Equipment or some other area. Once you get a few areas tied down reasonably well, you can even put them into Designer (finally, the developers get to do some "real" work). These partial diagrams can be printed out on paper or transparencies to see how they tie together with the other pieces. If they can, users ought to name the relationships. This helps further define why the relationship exists and is useful when looking at the model and reading the reports later.

Intelligent Keys

Explain to the users that a Primary Key makes the record unique in the database. Because a Primary key must never be updated, we don’t use user-entered information, such as a customer name, address, or account number. We make those Unique Keys. The user group should define Unique Keys; the database should create Primary Keys. Users often want an invoice number made from the first 6 digits of the customer name followed by the employee number who took the order followed by a date. Because it contains data inside itself, these are called “intelligent keys.” Do not keep them in the database. Show the users you can output their intelligent key on reports for the use of warehouse workers, but the data it contains will be kept as separate fields.

Using Designer (finally)

You can send the users back to their day jobs now (or do this at night between meetings). Most developers want to get in and develop in Designer right away, but to build a good system, you need a good plan and the people that are going to use the system are the ones who must develop the plan. Once they’ve got a good start on the plan, then you can start putting it into action in Designer.

Making Work Areas and Containers

To create an E/RD in Oracle Designer, the first step is to open Designer. This assumes Oracle has been installed into a repository and you've got access to it. Then you create a Work Area (usually your application name even though Oracle wants that for your container name) and start there. On the initial Oracle Designer 6i screen, choose the Entity Relationship Diagrammer (in the Model System Requirements section). When the E/R Diagrammer comes up, choose File;New and a little window will pop up telling you to select a container name. Double-click on the page icon to the right of the text box and a new screen will appear. Click on the Create (new container) icon on the top toolbar and give it a name such as version one or APP(1). Click Ok until you get back to a clean new diagram tentatively labeled ERD1. Name the diagram ERD_something_or_other. This will group all the ERD diagrams together in the RON (Repository Object Navigator).

Add Entities

To create an entity, click on the Entity icon (an oval). The pointer should change to a box when you move back over the diagram. Click anywhere and a Create Entity window will appear. Put in the Name, Short Name, and Plural.
  • Short Name is used as a prefix to indexes and keys.
  • Plural is used as the table name and in the entity reports. Using an actual plural makes reading the reports a bit easier, but you're going to live with those table names for a long time so choose the table name you want. (I personally don't like plural names).
    • Add all the entities at once. If there are more than 20, you might want to group them by subject area on separate diagrams and pull them all together later. To add multiple entities easily, lock down the entity icon by pressing the Shift key before clicking on it.

    Create Relationships

    Once you've got all the entities needed for the current diagram, put in the relationships. This is trickier. To create a relationship, select a relationship icon, click on the from entity, then click on the entity it's going to. You can fix relationships later by right-clicking on one and selecting either one of the 4 options or clicking Properties and fixing it there. There are 9 different relationship icons to choose from. A couple (the Many-to-Manys) are only used for strategy E/RDs, not for ones to convert into tables. The rest must be used with the left side starting on the initial entity. Select the correct relationship, connect it from one entity to another, and a window will appear asking for the from and to names of the relationship. There are lots of issues around selecting good verbs. An easy way for most relationships is to choose a gerund (ending in "-ing") such as "assigning" or "describing" and use it for both sides of the relationship. These names are used on reports to enlighten readers; they won't affect your database.

    Finishing the Diagram

    Making the diagram readable is a chore. It seems like the Edit;Select All (Ctrl-A) and then doing Layout;Autolayout (icon) would make it all simple. Try it and see if you like it. (CTRL-Z is Undo). The way that works for me is to
  • Move and resize entities,
  • Click on a relationship,
  • Choose Edit; Select Same Type
  • Click the Autolayout icon (or Layout;Autolayout).
    • After a few iterations, the diagram becomes readable. Then you can click on the relationship names and move them individually when needed. They are automatically moved when doing an autolayout of relationships so wait until you're done to move them.

    Domains

    Enter the attributes, but before doing so, decide on their length and type and apply that information using domains. It's much easier to make changes and to document properties using domains. You can create domains as you create entities. You can also create domains by themselves from the RON. Expand your container, then right-click on Domains and choose Create Domain. The only things you have to enter are Name, Datatype, and Maximum Col Length. Use some good Domain Naming conventions or you won't be able to figure out where to go to fix something. I preface all the domains that have actual values with LKP_ followed by the attribute they are the lookup for. Attributes

    Now enter the attributes. You could also have entered them while putting in the entities, initially. You can create attributes in the RON, but for the first pass, I find it easier to enter them off the E/R Diagram.

  • Open a diagram and double-click an entity (or right-click and choose Properties).
  • Click on the Attributes tab and fill in the information.
    • I like having the same Name for similar attributes so I usually make a list of them ahead of time. You can also change them after. The Seq column is numbering for the order in which the attributes are displayed in the RON and in the form. Use them. The generators really want that data. Check Opt if the field will be optional, otherwise the field will be required. Don't worry about anything else on that tab. DO NOT ENTER IN ANY PRIMARY KEYS. I suppose if you want to you can. I like Primary keys based on generated sequence numbers. This is what you get if you do not specify any primary keys at all; don't check the Primary box at the far right under the Attributes tab nor under the UID tab. Oracle will generate a primary field called ID with an associated sequence for it and the field won't be displayed by default on your form modules. Definitely the way to go. Click on the Att Detail tab to apply domains or set the type and lengths of fields. Ignore the Derivations and On Conditions because this is for a quick build of a prototype. Before you select a Domain, click the Apply button at the bottom to apply your new attributes. The current attribute is listed in the top of the window in the Name field. There is a dropdown list to switch to other attributes. The Att Values tab allows you to put in specific values for an attribute, but I think you're better off doing that through domains. The UID tab is where you put in Unique Identifiers. Again, do not click the Primary box if you want Oracle to generate your primary keys and sequences. You can put in a Name for the Unique Identifier (I use the column/attribute name) and then click on a candidate attribute and use the down arrow to send it down to the Unique Identifier Contents block. In the Definition tab, you can change the entity names and put in values for table sizes. Under the Text tab, you can put in Descriptions or Notes (change using the text type window) about the entity or about specific attributes (change the top window to "Attribute" and an attribute selection appears). The help screens are succinct, but useful.

    E/R Reports

    Once you've got the model fairly well done, you can print out reports. I don't particularly like them; there is too much detail. However, they are useful for giving to users and filling up notebooks for managers. To run reports, get on the initial Oracle 6i screen and choose Repository Reports in the lower righthand corner. Open up the Entity/Relationship Modelling to view the 8 available reports. I use the first four about entities and attributes. The reporting, as usual, is very funky. For some reason, Oracle doesn't seem to care about getting data out. One of the best ways to use the canned reports is to run them in Previewer. You can fool around with the options on the Parameters Palette of writing directly to a file but certain options overwrite other ones. After seeing if the report looks good in Previewer, choose File; Generate to File and select the format and enter a directory and file name. If you don't like the reports, Oracle recommends you open the report file in Developer and change it yourself. For the users, the reports are good. I output them in HTML and wrote a Perl script to strip them. For short lists of info for myself, I looked inside the reports for the tables and went into SQL and wrote some quick little queries. The SQL information is on my web page.

    Testing the Diagram

    Finally, test your diagram. One way is to go over all the entity definitions and relationships with your users. This is time-consuming and boring, but very useful if the users put effort into it. The other way to verify it for yourself is to use the scenarios you've gathered.

    Scenarios

    As you're going along with your users getting nouns and naming relationships, there will be lots of discussions about how work is done and how data is entered or how managers use the data to answer questions. CAPTURE ALL THAT INFORMATION. Write it down and save it. Those scenarios of how work is accomplished will tell you whether the model you have works. If you've written them down, then you don't even need the users to test the model. If it works for the scenarios you've captured, then you can give those to the users and say, "We can get this data in and out of the system. Do you have any more scenarios that we haven't covered?"

    Tweaking the Diagram

    You can fine-tune the diagram yourself.
  • Get rid of all the weak entities--ones that have only a single relationship to another entity. Fold them into the other entity as an attribute (usually with an associated lookup domain).
  • Look for commonalities of data. The Data Model Resource Book mentioned earlier is helpful in this respect. See if you can't meld customers and businesses and employees together. Sometimes you can and should, sometimes you can't. They might be holding different types of data or have different relationships to other entities.
  • Look for data you don't really need. You'll probably have employees in there and employees have spouses and addresses and phone numbers, but is this database the place you want to keep that information? Unless it's a human resources database, the data probably resides elsewhere and putting it in your new system means double data entry.
  • Investigate the ways you're going to classify data. If a record in a table (an instance of an entity) can only be one thing, then you can make a Type or Class code for it. But if it can be classified as two or more things, then you will either need multiple Type fields, or you will need to add a set of hierarchy entities so the same record can be classified in multiple ways. For example, in a wildlife damage database, we assumed we'd only need the separate Linnaean species codes. However, there are scenarios where the users care about subspecies, need to distinguish between domestic, feral, and free-range animals, and must know if the species is Threatened or Endangered which can change from state to state or county to county.
    • Test with the scenarios again after making all these changes.

    Building the System with Designer

    With the E/RD done, you can create a system using other Designer tools to create Functions, create Tables, and turn the functions into Modules that use the tables.

    Function Hierarchy Diagrammer

    Now you're ready to build the system (or a prototype of it) in Designer. First, create a good Function Hierarchy Diagram (FHD) using the Function Hierarchy Diagrammer. A Process Model would give you a bunch of functions to work with. But for a database, you don't need a comprehensive FHD. Put in a function for every form. In the Properties for each function, you define the Entity Usages: Create, Retrieve, Update, and/or Delete (CRUD). Some forms will be large and their CRUD may include several different entities. You can also create functions for reports and batch processes if you want. The hierarchy of functions makes it easy to generate modules so you don't need to put too much time into analyzing it. After assigning Entity Usages, run the Utilities; Function/Attribute Matrix to assign IRUN (Insert, Retrieve, Update, Nullify) for each attribute of each entity.

    Make Tables

    You must have the tables in place before creating modules. Making the tables is a multi-step process:
  • Run the Database Design Transformer (in the Transform Preliminary Designs section) to create Relational Table Definitions. This will also create Sequence Definitions if you haven't designated any primary keys.
  • Get into Design Editor (in the Design and Generate section) and run the Generate; Generate Database from Server Model to create SQL scripts to create your tables, views, indexes, and sequences. You can put a table prefix in here to distinguish your tables from everyone else’s.
  • Run the table creation script you generated inside SQL*Plus.

    • Create Modules

    In the Transform Preliminary Designs section, choose the Application Design Transformer to create modules. Select the top function and it will generate modules named with the prefix you give and numbered starting with the number you enter.

    Design Editor

    To see what you've done, go into Design Editor. The Server Model tab will have your relational table definitions. The Modules tab will have those and your modules. They will be oddly named and numbered (which is one argument for generating modules a few at a time) but if you expand them, you can tell which is which. Rename them and uncandidate them (in Properties, change Candidate? to No). Then you can drag the newly changed icon onto the Design Editor's gray screen and begin making your forms—still a major task.

    Regenerating

    You'll never get the first pass right. In order to regenerate, you must delete everything or they won't regenerate.
  • You can leave old modules around but they fill up the RON and Design Editor.
  • Drop the tables in the database. You can write self-creating scripts to do this (based on the table prefix) or you can create all tables under a specific user and then drop the user.
  • Delete the definitions for the tables, indexes and sequences. If you only want to regenerate a couple, then you can leave the rest and Designer won't map them in the generator. If you don't delete definitions, then you may end up spending too much time looking at the generator options trying to figure out what's wrong.

    • Small Steps

    For the first time through in Designer, make separate workareas. One should hold your main application and the other can hold a few test tables so that dropping, changing, and generating doesn't take so long. The help screens are useful, but sometimes you need to see the whole thing through to the end to truly understand what one little change will do.

    Summary

    Oracle Designer is a complex tool. System design is a complex process. Together, they make some of the work less tedious but you must still use the organization’s collective brain power to create a good system. You must also invest quite a bit of time in learning to use the tools available in Oracle Designer. This paper and more information about system design and Oracle Designer are on my programming page at http://home.att.net/~mike.moxcey/pgm/.