Entity Relationship Modeling Examples

The following are example scenarios for fictitious businesses. Read the examples and then sketch an Entity Relationship diagram that captures the data needs of the businesses.

Example 1 Investment Firm

Dear Mr. Smith:

As we discussed previously during our phone conversation, our fledgling investment firm is looking to develop a state of the art portfolio management system that will track our investor’s portfolios. This letter should serve to fill you in on more of the details of our operations.

Our firm is customer driven. We take great pride in custom tailoring investment strategies for each investor. Investors vary widely in terms of the investment requirements, sensitivity to risk and other factors. Potential investors initially meet in a one on one situation with one of our experienced analysts. We provide up to two initial meetings free of charge with different analysts to determine if an investor is comfortable with our management style. In our experience, we have only seen a few cases where an individual investor asked to meet with a second analyst and only one case out of about 400 where an individual investor failed to secure us as their investment firm. A major source of customer growth comes from recommendations. We offer various incentives to a current customer if they recommend a friend, family member or co-worker who subsequently secures us as their investment manager.

Once a potential investor has found their "match" with one of the analysts, we develop a portfolio that may include individual stocks or bonds, or mutual funds. The investor then transfers money from their bank account to our bank and the selected analyst takes over to manage the portfolio. We require the ability to store and retrieve a given investor’s portfolio at any given point and time.

We keep track of the portfolio and issue statements each quarter or every month depending on what the investor specifies. Another important point is that we do not sell our investor’s names, addresses or telephone numbers to other investment firms. We also keep track of the investors requirements in a "personal profile" which includes information about when the investor would like to be contacted and under what situations. For example, some of our investors do not want to be bothered with the day to day details of their portfolio while others do not mind receiving phone calls each time market conditions change. For example, we would run a report that lists those investors who require a phone call when the Dow Jones average falls more than 100 points in a single day.

We also issue internal reports to other analysts as a means of "checks and balances". We often find a fresh pair of eyes looking over a portfolio can lead to improvements in the portfolio. Portfolio reviews are scheduled every 4 months.

Finally, we should mention that a number of investors have expressed interest in maintaining two separate investment portfolios with us. One which is more aggressive (and, hence can lead to higher short-term gains) and another less aggressive with long term potential for steady growth. Thus we would want to accommodate them in this way.

In summary, this database should be able to track potential investors from scheduling the initial meetings with the analysts, through the creation of multiple investment portfolios and into the business of managing and reporting on their portfolios.

Thank you for your interest in developing this database system for us.

Example 2 Pet Insurance Company

February 23, 1998

Dear Mr. Smith:

We are seeking your guidance in constructing a new database for our insurance firm: PetInCo - The Pet Insurance Company. PetInCo has just opened its business in the tri-state area and offers insurance plans for pet owners and their pets. It is clear that keeping track of our customers, policies and claims will be a daunting task if we must rely on manual methods such as paper forms in filing cabinets. We are hoping you can design a database to meet all of our needs.

The basic business of PetInCo is insurance policies for pets. All pet owners are potential customers. We provide two basic types of policies:

Standard Plan - Customers pay a yearly premium to become a member. Pets may be taken to any veterinarian and the entire bill is paid for by the customer. A claim form is then submitted to PetInCo by the customer and the customer is reimbursed according to a reimbursement schedule.

VMO Plan - Customers pay a yearly premium to become a member. Pets may only be taken to a vet within the VMO plan. All visits, regardless of treatment, require a $10 co-payment payable directly to the vet. More involved treatments such as surgery are billed to the customer at a reduced rate.

The yearly premiums vary depending on the type of pet. For example:

We assign each Customer a customer number and would like to keep track of their name, address, telephone number and when they became a member. Policies include a policy number, the pet or pets that are covered under the policy, the effective dates for the policy and the policy type. For VMO policies, we also record the primary care vet for each pet. We track premium payments by the customer’s check number and date the premium was paid.

Claims include the policy number and specific pet the claim is for, the service provided, the amount charged by the vet and the amount paid by the customer. If the plan is the standard plan, then we would also keep track of the reimbursements sent to the customer for the particular claim.

Finally, we would like to keep track of the vets in our VMO network and track the payments we make to them. Vets in the VMO network are paid a flat monthly payment according to the number of pets they currently care for.