Tag: Requirements

Building 747s: A study of Traceability

Written by Anne Sams on May 5, 2021. Posted in Articles. 1 Comment on Building 747s: A study of Traceability

Early in my career I was part of a project team that handled a variety of one-off projects.

The nature of the work meant that we were usually engaged mid-project and were expected to handle the Business Analysis and Project Management roles until project close. We engaged in weekly team calls to discuss organizational goals and upcoming projects.

“We’re building 747s in flight,” my manager would say proudly each time a concern was raised about how a project was to be handled.

These conversations happened at a time before I identified as a Business Analyst and long before I had ever read the BABOK. Even so, I distinctly remember wondering why these projects had to be done in flight. Didn’t it make more sense to land the plane, figure out what we needed, start building, and then take off?

How did we get here?

How did we get 30,000 feet in the air and not know where we were going?

I wish I could say those were the only times in my career where I was dropped into a project mid-flight with no parachute and the expectation that I could help build the plane before it landed or crashed, but that’s not true. Whether it was through a new job, being brought in as an opportunity to organize what already exists, or simply realizing that the project in flight needs a BA, I have finished significantly more projects than I have started.

When I am brought in mid-flight to a project I want to know “How did we get here?” When asked that way, I usually end up with blank stares or finger pointing as answers. Instead, I’ve started asking “Do we have traceability for these requirements?” This still gets me blank stares, but at least the finger pointing decreases.

Traceability is the first task of Requirements Lifecycle Management, according to the BABOK. The implication here is that not only is Traceability important, but it is the base on which all other tasks of the Requirements Life Cycle are managed. So why does no one ever know if we have any?

Perhaps it comes from becoming a BA by circumstance instead of design. Perhaps it’s because it has never been done that way before. Perhaps it’s because the project team was so close that everyone was aware of all the requirements throughout the entirety of the project.

Most likely it’s because people don’t know what traceability is and even if they do, it seems daunting.

Most things written about traceability are scholarly and technical. They explain about numbering requirements and creating a matrix and documentation…It doesn’t need to be that complicated.

Instead, think of traceability as a list that needs to be checked at every step of the project, just like pilots have a checklist that they perform before getting in the plane each and every time. They examine the plane from all angles, make sure all the equipment is working, and familiarize themselves with the plane before they ever leave the ground. Even while in the air, the pilot is still performing checks.

Because most project I do are mid-air, I trace all the requirements backwards in order to understand where we are. Sometimes the requirements may be as simple as “we need two wings”; or they may be significantly more complicated as we discuss thrust, lift, air speed, air pressure, and molecular structure of the metal. Any project should have enough information that a BA thrown into it can use the checklist to review requirements during prototyping, solution design, User Acceptance Testing, and every other step of the project.

Why is this important?

Imagine being brought into a project in flight, perhaps during UAT, and being presented with the fuselage of an airplane. The project was to build a 747. Common sense says that airplanes have wings. Looking through the artifacts, the original requirements say that the airplane should have wings. The users were expecting wings. So why did the airplane get built without wings? How did we get here? This is where the blank looks and finger pointing start.

But the truth is it doesn’t matter how we got here. To move forward, the project needs to take some steps backward or the user is going to be presented with a half solution that requires workaround processes. Either way, the end user isn’t happy and project deadlines are stretched because, along the way, someone forgot the wings and no one checked.

In one instance, I was brought on to a project for which I was not the original BA, and was asked to sign off on test cases. After evaluation, I determined there wasn’t enough information for me to sign off, despite growing pressure to complete this stage of the project. The vendor with whom I was working could not trace the test cases to any of the requirements. Not only did I not write the original requirements, I couldn’t even find the original requirements. Yet I was being asked if it was ok to move on to the next phase. To have confidence in the next stage, I had to reinterview stakeholders, complete and organize information to provide a full picture, and review the test cases. The turbulence was manageable, but could’ve led to a crash landing.

I did fly an actual plane once. My instructor walked me through each step as I did it. He told me how I was supposed to hold my hands, which gauges I needed to look at, and how to tilt the nose of the plane. When he felt confident that I understood everything I was supposed to do, he let me fly on my own for a while before he instructed me in the last step of the process, landing. Only when we were on the ground did he tell me that landing is the most dangerous part of flying.

With proper traceability, business analysis can be as easy as flying a plane.

Taking on Current State Analysis

Written by Anne Sams on March 18, 2021. Posted in Articles.

Business Analysts may feel pressured by the organizations in which they work to deliver requirements quicker.

It may be tempting to skip steps in order to deliver a product quicker, however Current State Analysis should never be skipped.

Current State Analysis is an integral process with which Business Analysts should become intimately familiar. The following cases illustrate the importance of a shared understanding of current state among all stakeholders in a project.

Case #1

Not too long ago, I was pulled into a discussion about a Continuous Improvement item that had been in “In Requirements” status for 3 years without being closed. “I need someone who can do use cases for this,” the Business Systems Analyst told me.

She went on to explain that the Business was having difficulty explaining their requirements to the Developers and the Developers were upset that the business kept changing their requirements. The developers outlined solutions based on the requirements given but the Business never felt satisfied with the solutions. Needless to say the Steering Committee was unhappy with the entire situation and was pushing to get answers about whether or not this was even a viable ticket. I brought out a use case template that I had used in the past and quickly discovered that there was not enough information to allow me to understand why the solutions kept failing to meet the user needs.

I started doing interviews so that I could understand the current workflow that was causing distress to the end user.

Case #2

Another time, a Continuous Improvement Item was handed off to me. The structure of the company was arranged in such a way that the development team was aware of continuous improvement items before a BA was involved. Often the developers had a solution in mind prior to requirements being written, this time included. Because of unfamiliarity with the exact process that was being modified, I indicated that I needed to understand current state prior to writing requirements.

“Everyone knows current state,” he replied. “The system isn’t working for this process. It’s right there.”

I stuck my ground. “Give me until tomorrow to get current state outlined” I pleaded. He reluctantly agreed and left me in a room with a whiteboard and a junior developer.

Case #3

There was an instance where I had spent hours interviewing stakeholders about how the integration was supposed to work. I understood the business need clearly. The information is needed to be fed into one system so that it could narrow down choices based on criteria set up on the back end of another system. I diligently documented the process in each system and discovered that changing one piece of information would cause the whole structure to collapse. I raised the risk. The team responsible for the transition told me that these weren’t risks. What had I missed?

In a world where systems evolve quickly and companies transition from Waterfall to Agile methods of project management, one thing that doesn’t change is the need for a shared understanding of the Business Problem., In the cases I illustrated, it was through Current State Analysis that the true requirements were discovered

In the first case, I created a non-traditional system diagram to show how the user input triggers combined with system functionality led to undesirable results because the developers and the end user had a different understanding of a single word. The developer used the word to define a Process (verb) while the end user saw it as an Object (noun).

The original requirements documents did not point out this difference or the relationship between them.

Outlining the current state allowed us the opportunity to redefine the ask and stop playing the blame game. The development team finally understood why they felt like the user kept changing requirements and the user finally understood why the development team couldn’t give them a solution.

Oftentimes in Business Analysis the old adage holds true that“ A picture is worth a thousand words.” A current state diagram does not have to be fancy. My simple method is to keep a notebook next to my desk where I draw sloppy pictures that help me articulate what I am seeing. When a Waterfall Project requires it, I translate my scribbles into a Visio diagram that can be added to the ticket or the Requirements Package. In a more Agile approach, my simple sketch may be enough to move us from point A to point B.

In the second case, the junior developer and I walked through the system and the code together to understand where the change needed to be made and why. We called a meeting to walk through the current state with all stakeholders in the room.

As we walked through the system and explained what the system was doing at each step, it turned out that not everyone did understand current state.

Because the developer and I had worked through what the system was doing and why together, we were able to move to a mutually acceptable solution more quickly.

In the third case, I had not walked through current state of the full process. My logic kept failing because of one missed piece in a system that I did not have access to, instead of relying on other people to tell me what the system did. After weeks of frustration, I finally had the end user walk me through the entire process from end to end. My mistake was glaring at me. By not having a complete understanding of current state, I could not make any solution work without asking for an unnecessary platform change.

Although often dismissed as “time-consuming” or “obvious”, Current State Analysis creates a shared understanding of the process that is essential to the success of Business Analysis work. Whether it’s formalized in a presentation, written on a cocktail napkin, or communicated in Morse Code Current State Analysis is invaluable to the BA process.

Five Requirements Prioritization Methods

Written by Karl Wiegers on February 11, 2021. Posted in Articles. 73 Comments on Five Requirements Prioritization Methods

When customer expectations are high and timelines are short you need to make sure your project team delivers the most valuable functionality as early as possible.

Prioritization is the only way to deal with competing demands for limited resources.

Stakeholders on a small project often can agree on requirement priorities informally. Large or contentious projects with many stakeholders demand a more structured approach. You need to removes some of the emotion, politics, and guesswork from the process. This article discusses several techniques teams can use for prioritizing requirements and some traps to watch out for.

Two Big Traps

Be sure to watch out for “decibel prioritization,” in which the loudest voice heard gets top priority, and “threat prioritization,” in which stakeholders holding the most political power always get what they demand. These traps can skew the process away from addressing your true business objectives.

In or Out

The simplest method is for a group of stakeholders to work down a list of requirements and decide for each if it’s in or it’s out. Refer to the project’s business objectives to make this judgment, paring the list down to the bare minimum needed for the first iteration or release. When that iteration is underway, you can go back to the previously “out” requirements and repeat the process for the next cycle. This is a simple approach to managing an agile backlog of user stories, provided the list of pending requirements isn’t too enormous.

Pairwise Comparison and Rank Ordering

People sometimes try to assign a unique priority sequence number to each requirement. Rank ordering a list of requirements involves making pairwise comparisons among all of them so you can judge which member of each pair has higher priority. This becomes unwieldy for more than a few dozen requirements. It could work at the granularity level of features, but not for all the functional requirements for a good-sized system as a whole.

Rank ordering all requirements by priority is overkill, as you won’t be releasing them all individually. You’ll group them together by release or development iteration. Grouping requirements into features, or into small sets of requirements with similar priority or that otherwise must be implemented together, is sufficient.

Three-Level Scale

A common approach groups requirements into three priority categories. No matter how you label them, if you’re using three categories they boil down to high, medium, and low priority. Such prioritization scales usually are subjective and imprecise. To make the scale useful, the stakeholders must agree on what each level in their scale means.

I like to consider the two dimensions of importance and urgency. Every requirement can be considered as being either important to achieving business objectives or not so important, and as being either urgent or not so urgent. This is a relative assessment among a set of requirements, not an absolute binary distinction. These alternatives yield four possible combinations (Figure 1), which you can use to define a priority scale:

* High priority requirements are important because customers need the capability and urgent because they need it in the next release. Alternatively, there might be compelling business reasons to implement a requirement promptly, or contractual or compliance obligations might dictate early release. If a release is shippable without a particular requirement, then it is not high priority per this definition. That’s a hard-and-fast rule.

* Medium priority requirements are important (customers need the capability) but not urgent (they can wait for a later release).

* Low priority requirements are neither important (customers can live without the capability if necessary) nor urgent (customers can wait, perhaps forever).

Watch out for requirements in the fourth quadrant. They appear to be urgent to some stakeholder, perhaps for political reasons, but they really aren’t important to achieving your business objectives. Don’t waste your time implementing these—they don’t add sufficient value to the product. If they aren’t important, either set them to low priority or scrub them entirely.

Figure 1. Requirements prioritization based on importance and urgency.

On a large project you might want to perform prioritization iteratively. Have the team rate requirements as high, medium, or low priority. If the number of high-priority requirements is excessive and you can’t fit them all into the next release, perform a second-level partitioning of the high-priority ones into three groups. You could call them high, higher, and highest if you like, so people don’t lose sight of the fact that they were originally designated as high.

Those requirements rated “highest” become your new group of top-priority requirements. Then, group the “high” and “higher” requirements in with your original medium-priority group (Figure 2). Taking a hard line on the criterion of “must be in the next release or that release is not shippable” helps keep the team focused on the truly high-priority capabilities.

Figure 2. Multipass prioritization keeps the focus on a manageable set of top-priority requirements.

Watch for requirement dependencies when prioritizing with the three-level scale. You’ll run into problems if a high-priority requirement depends on another that’s planned for later implementation.

MoSCoW

The four capitalized letters in the MoSCoW prioritization scheme stand for four possible priority classifications:

Must: The requirement must be satisfied for the solution to be considered a success.

Should: The requirement is important and should be included in the solution if possible, but it’s not mandatory to success.

Could: It’s a desirable capability, but one that could be deferred or eliminated. Implement it only if time and resources permit.

Won’t: This indicates a requirement that will not be implemented at this time but could be included in a future release.

The MoSCoW scheme changes the three-level scale of high, medium, and low into a four-level scale. It doesn’t offer any rationale for making the decision about how to rate the priority of a given requirement compared to others. MoSCoW is ambiguous as to timing, particularly when it comes to the “Won’t” rating: does it mean “not in the next release” or “not ever?” The three-level scale that considers importance and urgency and focuses specifically on the forthcoming release or iteration.

Agile projects often use the MoSCoW method, but I’m not a big fan of it. Here’s how one consultant described how a client company actually practiced the MoSCoW methods:

All the action centers around getting an “M” for almost every feature or requirement that is captured. If something is not an “M” it will almost certainly not get built. Although the original intent may have been to prioritize, users have long since figured out to never submit something that does not have an “M” associated with it.

Do they understand the nuanced differences between S, C, and W? I have no idea. But they have figured out the implications of these rankings. They treat them all the same and understand their meaning to be “not happening any time soon.”

$100

One way to make prioritization more tangible is to cast it in terms of an actual resource: money. In this case, it’s just play money, but it’s money nonetheless.

Give the prioritization team 100 imaginary dollars to work with. Team members allocate these dollars to “buy” items they’d like to have implemented from the set of candidate requirements. Allocating more dollars weights the higher-priority requirements more heavily. If one requirement is three times as important to a stakeholder as another, she might assign nine dollars to the first requirement and three dollars to the second.

But 100 dollars is all the prioritizers get—when they’re out of money, nothing else can be implemented, at least not in the release they’re currently focusing on. You could have different participants in the prioritization process perform their own dollar allocations, and then add up the total number of dollars assigned to each requirement. That will show which ones collectively come out as having the highest priority.

Watch out for participants who game the process to skew the results. If you really, REALLY want a particular requirement, you might give it all 100 of your dollars to try to float it to the top of the list. In reality, you’d never accept a system that possessed just that single requirement.

Nor does this scheme take into account any concern about the relative amount of effort needed to implement each of those requirements. If you could get three requirements each valued at $10 for the same effort as one valued at $15, you’re likely better off with the three. The scheme is based solely on the perceived value of certain requirements to a particular set of stakeholders, which is a limitation of many prioritization techniques.

It’s Not All Going to Fit

Sometimes customers don’t like to prioritize requirements. They’re afraid they won’t ever get the ones that are low priority. Maybe they won’t. But if you can’t deliver everything, make sure you do deliver those capabilities that are most important to achieving your business objectives. Prioritize to focus the team on delivering maximum value as quickly as possible.

We Don’t Need No Stinkin’ Code: Testing Software Requirements

Written by Karl Wiegers on January 27, 2021. Posted in Articles. 1 Comment on We Don’t Need No Stinkin’ Code: Testing Software Requirements

Someone once asked me when you can begin testing software. “As soon as you’ve written your first requirement, you can begin testing,” I replied.

It’s hard to visualize how a system will function by reading some requirements. Tests that are based on requirements make the expected system behaviors more tangible. Even the simple act of designing tests reveals many requirements problems long before you can execute those tests on a running system.

Requirements and Tests

Tests and requirements present complementary views of the system. Creating multiple views of a system—written requirements, diagrams, tests, prototypes, and so forth—provides a much richer understanding of the system than can any single representation. Some agile development methods emphasize writing user acceptance tests from user stories in lieu of writing detailed functional requirements. Thinking about the system from a testing perspective is valuable, but that approach still leaves you with just a single representation of requirements knowledge, so you must trust it to be correct.

Writing black-box (functional) tests crystallizes your vision of how the system should behave under certain conditions. Vague and ambiguous requirements will jump out at you because you won’t be able to describe the expected system response. When business analysts (BAs), developers, and customers walk through tests together, they’ll codify a shared vision of how the product will work and increase their confidence that the requirements are correct.

A personal experience brought home to me the importance of combining test thinking with requirements specification. I once asked my group’s UNIX scripting guru, Charlie, to build a simple e-mail interface extension for a commercial defect-tracking system we had adopted. I wrote a dozen functional requirements that described how the e-mail interface should work. Charlie was thrilled. He’d written many scripts for people, but he’d never seen written requirements before.

Unfortunately, I waited a couple of weeks before I wrote the tests for this e-mail function. Sure enough, I had made an error in one of the requirements. I found the mistake because my mental image of how I expected the function to work, represented in about twenty tests, was inconsistent with one of the requirement. Chagrined, I corrected the defective requirement before Charlie had completed his implementation, and when he delivered the script, it was defect free. It was a small victory, but small victories add up.

Conceptual Tests

You can begin deriving conceptual tests from user requirements early in the development process. Use the tests to evaluate functional requirements, analysis models, and prototypes. The tests should cover the normal flow of each use case, alternative flows, and the exceptions you identified during elicitation and analysis. Similarly, agile acceptance tests should cover both expected behaviors and exceptions.

Consider an application called the Chemical Tracking System. One use case, “View a Stored Order,” let the user retrieve a particular order for a chemical from the database and view its details. Some conceptual tests are:

User enters order number to view, order exists, user placed the order. Expected result: show order details.
User enters order number to view, order doesn’t exist. Expected result: Display message “Sorry, I can’t find that order.”
User enters order number to view, order exists, user didn’t place the order. Expected result: Display message “Sorry, that’s not your order. You can’t view it.”

Ideally, a BA will write the functional requirements and a tester will write the tests from a common starting point, as shown in Figure 1. Ambiguities in the user requirements and differences of interpretation will lead to inconsistencies between the views represented by the functional requirements, models, and tests. Those inconsistencies reveal errors. As developers translate requirements into user interface and technical designs, testers can elaborate the conceptual tests into detailed test procedures.

Figure 1. Development and testing work products derive from a common source.

A Testing Example

Let’s see how the Chemical Tracking System team tied together requirements specification, analysis modeling, and early test-case generation. Here are some pieces of requirements information, all of which relate to the task of requesting a chemical.

Use Case. A use case is “Request a Chemical.” This use case includes a path that permits the user to request a chemical container that’s already available in the chemical stockroom. Here’s the use case descriptionUse Case. A use case is “Request a Chemical.” This use case includes a path that permits the user to request a chemical container that’s already available in the chemical stockroom. Here’s the use case description.

The Requester specifies the desired chemical to request by entering its name or chemical ID number. The system either offers the Requester a container of the chemical from the chemical stockroom or lets the Requester order one from a vendor.

Functional Requirement. Here’s a bit of functionality associated with this use case:

If the stockroom has containers of the chemical being requested, the system shall display a list of the available containers.⦁ If the stockroom has containers of the chemical being requested, the system shall display a list of the available containers.
The user shall either select one of the displayed containers or ask to place an order for a new container from a vendor.

Dialog Map. Figure 2 illustrates a portion of the dialog map for the “Request a Chemical” use case that pertains to this function. A dialog map is a high-level view of a user interface’s architecture, modeled as a state-transition diagram. The boxes in this dialog map represent user interface displays (dialog boxes in this case), and the arrows represent possible navigation paths from one display to another.

Figure 2. Portion of the dialog map for the “Request a Chemical” use case.Figure 2. Portion of the dialog map for the “Request a Chemical” use case.

Test. Because this use case has several possible execution paths, you can envision numerous tests to address the normal flow, alternative flows, and exceptions. The following is just one test, based on the flow that shows the user the available containers in the chemical stockroom:

At dialog box DB40, enter a valid chemical ID; the chemical stockroom has two containers of this chemical. Dialog box DB50 appears, showing the two containers. Select the second container. DB50 closes and container 2 is added to the bottom of the Current Chemical Request List in dialog box DB70.

Ramesh, the test lead for the Chemical Tracking System, wrote several tests like this one, based on his understanding of how the user might interact with the system to request a chemical. Such abstract tests are independent of implementation details. They don’t describe entering data into specific fields, clicking buttons, or other interaction techniques. As development progresses, the tester can refine these abstract tests into specific test procedures.

Now comes the fun part—testing the requirements. Ramesh first mapped the tests against the functional requirements. He checked to make certain that every test could be “executed” by going through a set of existing requirements. He also made sure that at least one test covered every functional requirement.

Next, Ramesh traced the execution path for every test on the dialog map with a highlighter pen. The yellow line in Figure 3 shows how the preceding test traces onto the dialog map.

Figure 3. Tracing a test onto the dialog map for the “Request a Chemical” use case.

By tracing the execution path for each test on the model, you can find incorrect or missing requirements, improve the user’s navigation options, and refine the tests. Suppose that after “executing” all the tests in this fashion, the navigation line in Figure 2 labeled “order new container” that goes from DB50 to DB60 hasn’t been highlighted. There are two possible interpretations:

That navigation isn’t a permitted system behavior. The BA needs to remove that arrow from the dialog map. If you have a requirement that specifies the transition, that requirement also needs to go.
The navigation is a legitimate system behavior, but the test that demonstrates the behavior is missing.

When I find such a disconnect, I don’t know which possible interpretation is correct. However, I do know that all of the views of the requirements—textual, models, and tests—must agree, so there’s clearly something wrong.

Suppose that another test states that the user can take some action to move directly from DB40 to DB70. However, the dialog map doesn’t contain such a navigation line, so the test can’t be “executed:” you can’t get there from here. Again, there are two possible interpretations:

The navigation from DB40 to DB70 is not a permitted system behavior, so the test is wrong.
The navigation from DB40 to DB70 is a legitimate function, but the requirement that allows you to execute the test is missing.

In these examples, the BA and the tester combined requirements, analysis models, and tests to detect missing, erroneous, or unnecessary requirements long before any code was written. Every time I use this technique, I find errors in all the items I’m comparing to each other, quickly and cheaply

As consultant Ross Collard pointed out, “Use cases and tests work well together in two ways: If the use cases for a system are complete, accurate, and clear, the process of deriving the tests is straightforward. And if the use cases are not in good shape, the attempt to derive tests will help to debug the use cases.” I couldn’t agree more. Conceptual testing of software requirements is a powerful technique for discovering requirement ambiguities and errors early on.

Karl Wiegers is Principal Consultant at Process Impact. He’s the author of numerous books and articles on software development, project management, design, quality, chemistry, military history, and other topics. This article is adapted from Software Requirements, 3rd Edition by Karl Wiegers and Joy Beatty. Karl’s latest book is The Thoughtless Design of Everyday Things.

For the Love of Data: An Overview of Data Modeling for BAs

Written by Richard Larson on January 21, 2021. Posted in Articles.

I have always been a data fanatic. It started when I was a programmer analyst and learned to love such arcane data structures as ISAM (a relic), VSAM (simple, but efficient), and later DB2 (powerful and flexible).

My recent database exposure has been MySQL for websites, but only as a BA.

Mind you, I love process work, too, but maybe I just prefer the structure of database fields, columns, rows, and tables. Or maybe it is being able to query and manipulate the data to provide information useful for doing business or making decisions. Whatever the reason, I have long had an appreciation for and a love of analyzing data needs and turning them into robust logical or business data models.

So, let us get started with this overview of logical data modeling, which is divided into three basic steps.

Step 1: Entities

The central element in a logical model is an entity. It represents the people, places, things, processes, and events in an organization. All operational data fits into specific entities and if done correctly, only one. Note that physical databases might introduce redundancy for performance reasons. Take for instance a simple banking example with four entities in italics. Your money sits in accounts, which have deposits and withdrawals, and periodic statements are sent to one or more of your addresses.

Entities by convention are named with a singular, business-oriented noun. For our example, the entities might well be called Account, Transaction (an abstraction of deposit or withdrawal), Statement, and Location (instead of address). Choosing an appropriate name is harder than it may seem. I have witnessed teams toil over the best name and level of abstraction. The effort is worth it, though, to provide long-lasting data structures with meaningful names to the organization. Physical databases implement entities as tables or files.

Step 2: Relationships

Once the entities in question are identified, it is time to tie appropriate ones together into what are called relationships. Relationships are used to eventually tell the database engine which data elements belong together and should be retrieved together. In a physical database, relationships are expressed using keys, which are covered below. Once the entities in question are identified, it is time to tie appropriate ones together into what are called relationships. Relationships are used to eventually tell the database engine which data elements belong together and should be retrieved together. In a physical database, relationships are expressed using keys, which are covered below.

For our banking example, an Account has a relationship to the Transactions which identify the accounts a deposit or withdrawal applies to. A Statement is a collection of Transactions, so a relationship is needed there. Finally, Statements are sent to Locations, whether a physical address or an electronic one. For our banking example, an Account has a relationship to the Transactions which identify the accounts a deposit or withdrawal applies to. A Statement is a collection of Transactions, so a relationship is needed there. Finally, Statements are sent to Locations, whether a physical address or an electronic one.

Here are the categories of relationships in a logical model, with examples from the banking application shown in Figure 1: Here are the categories of relationships in a logical model, with examples from the banking application shown in Figure 1:

⦁ One to one (rarer than you think; maybe the bank needs a credit report entity and for some reason limits them to only one credit report per account.)

⦁ One to Many (an account can have many transactions, but a transaction belongs to only one account.)

⦁ Many to Many (statements might be sent to multiple locations and a given location might receive multiple statements.)

Our small banking example might look like the image in Figure 1 when depicted using “crow’s foot” notation, a common data modeling diagramming style. (There are other styles, but “crow’s foot” has been around a long time and is commonly used.) The “crow’s feet” represent “many” and the bars show “one.” The “O” symbols on the model designate “optional” or zero associations. Our small banking example might look like the image in Figure 1 when depicted using “crow’s foot” notation, a common data modeling diagramming style. (There are other styles, but “crow’s foot” has been around a long time and is commonly used.) The “crow’s feet” represent “many” and the bars show “one.” The “O” symbols on the model designate “optional” or zero associations.

To interpret the diagram, we can say an Account has at least one Transaction but likely will have many. A transaction applies to a single account and is not optional. A transaction appears on a single statement, but a statement can have “zero, one, or many” transactions on it. A Statement is sent to one or more Locations, while a given Location may receive no Statements, but can receive many over time. The above diagram is commonly referred to as an “Entity-Relationship Diagram” or ERD for short.

Step 3 – Attributes

After relationships are identified in a model, specific data the business wants to capture and report on can be added to the entities. The data elements are called attributes and could be added as soon as entities are defined, but it is easier with relationships in place. In a physical database attributes become fields or columns of data. After relationships are identified in a model, specific data the business wants to capture and report on can be added to the entities. The data elements are called attributes and could be added as soon as entities are defined, but it is easier with relationships in place. In a physical database attributes become fields or columns of data.

Like entities, attributes are labeled with a singular noun. For our banking example, a few facts pertaining to each entity are shown in Figure 2. Like entities, attributes are labeled with a singular noun. For our banking example, a few facts pertaining to each entity are shown in Figure 2.

Primary keys

The # in the example above represents an important concept in a data model called the “primary key” or “unique identifier.” Each entity needs an attribute or attribute combination with values unique to each occurrence of the entity. To distinguish one transaction from another the “# transaction number” attribute must have unique values for each separate transaction. An arbitrary identifier (sometimes called a “one-up” due to its sequential nature) is frequently used for this purpose. The # in the example above represents an important concept in a data model called the “primary key” or “unique identifier.” Each entity needs an attribute or attribute combination with values unique to each occurrence of the entity. To distinguish one transaction from another the “# transaction number” attribute must have unique values for each separate transaction. An arbitrary identifier (sometimes called a “one-up” due to its sequential nature) is frequently used for this purpose.

Foreign Keys

To solidify relationships between entities, “foreign keys” are used. A foreign key is a primary key of one entity added to the entity on the other side of the relationship. The Account-Transaction relationship is bonded using the account number key of Account placed in the Transaction entity represented with the “(FK)” in the example. Note: the exact way of showing attributes, primary keys, and foreign keys is usually determined by your organization’s modeling software and standards. To solidify relationships between entities, “foreign keys” are used. A foreign key is a primary key of one entity added to the entity on the other side of the relationship. The Account-Transaction relationship is bonded using the account number key of Account placed in the Transaction entity represented with the “(FK)” in the example. Note: the exact way of showing attributes, primary keys, and foreign keys is usually determined by your organization’s modeling software and standards.

Attributes are meant to represent single facts and if you discover an attribute that repeats or is in the wrong entity, then it needs to be adjusted using “normalization.” The Statement-Location relationship causes repeating attributes due to its Many-Many relationship. Normalization would solve this, and the topic could be an entire article on its own. Suffice it to say that “an attribute must be dependent on the key, the whole key, and nothing but the key.” The quote is attributed to a past database guru and summarizes how attributes should be added to entities. Attributes are meant to represent single facts and if you discover an attribute that repeats or is in the wrong entity, then it needs to be adjusted using “normalization.” The Statement-Location relationship causes repeating attributes due to its Many-Many relationship. Normalization would solve this, and the topic could be an entire article on its own. Suffice it to say that “an attribute must be dependent on the key, the whole key, and nothing but the key.” The quote is attributed to a past database guru and summarizes how attributes should be added to entities.

Summary

Data modeling is fascinating to me because it helps turn the ambiguity of possible data needs into a precise depiction of business rules and requirements. I have come to realize that constructing a robust data model helps us discover the meaning of the data to the organization. Focusing on the meaning of data has helped me to resolve intricate normalization issues that inevitably come up. Hopefully this article provides a quick introduction or refresher to help you get the most out of your data work. Data modeling is fascinating to me because it helps turn the ambiguity of possible data needs into a precise depiction of business rules and requirements. I have come to realize that constructing a robust data model helps us discover the meaning of the data to the organization. Focusing on the meaning of data has helped me to resolve intricate normalization issues that inevitably come up. Hopefully this article provides a quick introduction or refresher to help you get the most out of your data work.