Database Systems

HW#$1$

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.

Question $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$.$