This is the fifth of a multi-part case study for a company called National Investments. Part of your work over the course of the semester will be to design and build a database for this fictional company. Though fictitious, the narrative and issues discussed are very real for many companies. You must read these case studies carefully and thoroughly to fully understand their business and their needs. This is part of the requirement for building effective and efficient databases.
Case Study Part 5
It’s All Coming Together Nicely! In your previous assignment you created your database using SSMS. Now, you may have had to change your design once you started implementing the tables and the relationships, and that’s ok. Read that again—changes are OK! The process of developing a database is always an incremental one and subject to many revisions before your design goes into production.
One of the things that you need to remember is that when you encounter resistance (SSMS won’t let you do what you are trying to do, creating relationships is a struggle, etc.) this is a sign that you need to step back and think through your design decisions. Often, resistance is a sign that there might be issues with the design you are attempting to implement.
In several follow up meetings you wanted to know what the most important components of this new system are to the company—not to individual people—but to the organization. As you learn more about the most important components you also learn about some elements that might be missing from your design. Here are your findings:
Most Important Components of New Database System
Assets Under Management. This company lives and dies by this number. The way the company makes money is by charging a percentage amount of AUM from each investor. This percentage amount is called a “Management Fee.”
Oh—did we forget to mention that we need to record that % management fee in the database?
And, this % management fee can be different for different investors. For example, for company ABC who has a 401K account with National Investments, we charge a flat fee of 1.5% on the total AUM of that company. Hence, if we manage $2.3 Million in AUM ($2,300,000.00) then National Investments makes $34,500 profit for the year. But for company DEF, for example, the flat fee might be 1.02%. These fees can be different because the acquisition of new business is always a process of developing relationships that are meaningful to both parties, and these relationships never are the same from investor to investor.
The incentive is obvious, manage higher AUM and make more money. This is achieved through the investor investing more money and National Investments growing that investment over time.
So, not only do we need to track the AUM from each investor (or investing company) and the management fee, we also need to be able to know—very quickly—what the total AUM is for all investors and investing companies and the total average management fee. This will allow us to know how much money we will make each year. So, while one company’s management fee is 1.5% (.015 X AUM) another’s might be 0.7% (.007 * AUM). We would want to know the average management fee times the sum total of all AUM (avg. mgmt. fee X total AUM) = annual revenue generated. For example, if the average management fee is 0.95% (.0095) — just less than 1% — and the total AUM is $6,423,792,992.32 ($6.424 billion) then the annual revenue generated by National Investments is $61,026,033.43 ($61 million).
Basically, we are saying that National Investments makes 0.95 cents per year for every 100 dollars of AUM.
Ticker and Shares
It is critical that this database be able to distinguish the Tickers and number of shares by each investor or investing company. For example, two different investors might own shares of General Motors (Ticker: GM). One might own 108,00 shares while the other owns 72,169 shares. This means that National Investments owns a total number of 180,169 shares of GM, but we must be able to keep track of how that total number of shares breaks down by investor or investing company.
Critical to the calculation of AUM is the number of shares owned of a particular investment like GM and the current price of each of those shares.
For example, on January 2, 2020, each share of GM stock was worth $37.38. Due to the Coronavirus outbreak, on April 1, 2020, each share of GM stock was only worth $19.28. This clearly represents a threat to the overall financial well-being, not only of our investors, but also to National Investments as a company.
Oh, did we forget to mention that we need to know—at any moment—what the total AUM is for National Investments? Again, this is the total AUM for each investor or investing company calculated as the sum total of Company Shares X Share Price. Not only will this number change from day-to-day but stock prices fluctuate throughout each business day, as well.
A trading day might open down due to global issues in China or Japan but may rise through the day as investor confidence rises through the day. Or, the markets could open high in a morning but bad news: a terrorist attack somewhere in the world, a cut in oil production, an airline disaster, the start of a global pandemic, or any number of adverse events will drive the value of the stock market down as investors begin to panic, sell their stock or move their money into less volatile investment opportunities.
Note: this number/value does not need to be stored in the database. And, it probably shouldn’t be because you would have to think through the process of how it would get updated every time there is a change to a share price value. But, the design of your database should be such that we can report this value as needed.
This assignment is all about modifying the design and implementation of your database when you find further information that was not revealed earlier and getting data into your database. This will then allow you to create T-SQL queries to report on that data.
In the process of adding data to your database you often find your design either isn’t optimal—in one or several areas—or might not work at all in which case you need to go back and rework some of the design and implementation of your database.
When using SSMS, there are two ways to enter new data into your database tables: using the T-SQL INSERT statement or using the Edit Table feature in SSMS.
Inserting Data into Table via SSMS Edit:
- Right-click on the name of the table into which you wish to enter data.
- Select the “Edit Top 200 Rows” option from the resulting menu.
- You will now see the edit panel with all columns pre-filled with NULL values.
- Fill in your data on the row with the asterisk * on the left of the row.
Right-Click on Table:
Select “Edit Top 200 Rows”:
- Note 1: Just because a column is pre-filled with NULL doesn’t mean that the column will allow a null value. In my example, the email address does NOT allow NULL values—therefore I must enter an email address—in this example.
- Note 2: In my example, the Id column is an auto-incrementing column (an “identity specification” column). This means that I MUST leave the null value here. Because this is an auto-incrementing field, once I am finished entering data for the row the next available Id value will be filled in more me.
Fill in your Data:
- Note 1: To move through each column simply press the Tab key. To go back a column hold the Shift key and then press the Tab key.
- Note 2: When you reach the last column in your row simply Tab one more time and the database will attempt to add your new row.
- Note 3: If any data in your row violates any rules or constraints then the row will not be added, and you will receive an error message. If you receive any error messages you will have to spend time to diagnose the problems and fix the issues causing such problems. This may be as simple as ensuring a column is not NULL if it doesn’t allow NULL values. It may be something more severe in that the data violates a foreign key constraint in which you will need to modify the design of the impacted tables before you can add your data.
In preparing your progress report you will need to perform all the following:
1. You will need to add data to the tables in your design that matches the data in the supplied Excel documents. If your database design doesn’t accommodate a given piece of data you must modify your design and implementation to ensure that all data provided in the spreadsheet can be added.
a. You may any other data you wish and as your design requires. If your design is such that the needed data is not supplied in the spreadsheet you will need to simply “mock” up some sample data and add it into your database.
b. Important: Your instructor cannot provide you with specific information how to complete this step. Each person’s database design will be different and therefore, how data must be entered will be different from person to person.
2. Write a paragraph from you about your experiences (good or bad) adding data to your database. Did everything go smoothly? If everything was not perfect or not as smooth as you would otherwise like what went wrong and how did you fix it?
3. Write a paragraph describing any changes that you needed to make to your database design and implementation as a result of the materials provided in this
4. White a series of T-SQL SELECT queries to report the data that you have inserted into your database.
a. There is no specific “look-and-feel” for your reports/queries. We basically want to see the company information, and then the stock information.
b. One query must calculate the total AUM for each company (of which there are two on your spreadsheet). These can all be separate queries.
c. You will copy the T-SQL query statements that you write into your progress report, making sure that you carefully document what each query does and what it produces.
Much of your work in this assignment is that of adding data to your implemented database, changing the design of your database if your data needs dictate such, and then writing T-SQL queries to report on the data has been added.
- You will submit a single document in either Word of PDF format to Blackboard. This document will be your Progress Report to the partners at National Investments.
Q. I am getting errors when I try to add data to my database. What should I do?
A. You will need to figure out where the errors are coming from and fix the source problem. Your instructor cannot help you here. Each person’s database design is unique and a problem for one person may not be the same problem for another person.
- Make sure the data you are entering for a column matches the data-type for that column.
- Make sure that you do not have any NULL values for a column that requires data.
- Make sure that you are not violating a referential constraint. This usually occurs either when you are entering data for a child table before that of the parent table, or when the foreign key does not match the primary key in the parent table.
Note: In the case of an error due to something being wrong in the table relationships, you may have to delete the relationship constraint, attempt to add the data, and then recreate the relationship—this often will show you where such an error might be.
Q. My database design doesn’t have all the columns that the spreadsheet has. What should I do?
A. You will have to change the design of your database such that all the data provided in the spreadsheet can be entered into your database.
Q. Where do I get the latest stock prices from?
A. You can use https://finance.yahoo.com. Simply go to this site, enter the ticker symbol for the company you wish to find data on and report on the “Previous Close” value as shown below:
Enter the company’s ticker symbol into the site’s search bar. In this example, we are looking for Apple, Inc. whose ticker is AAPL:
The current stock price and performance will appear. The “Previous Close” price is what you are looking for as it represents the most recent value of the stock before the current day’s activity:
Q. Earlier in this project guide it says it is important to know the total AUM for National Investments and to be able to report on the Tickers and Shares for each investor or investment company, but the reporting requirements for the progress report doesn’t mention needing to report on this data. Am I responsible for writing these queries?
A. There is no need create the queries for this data—yet. The queries you are being asked to write simply show the data you have entered plus the AUM for each of the two sample investors. In Project #6 you will write some more sophisticated queries—so, stay tuned.
Q. The sample data provided (in the next section shows specific stocks from companies like Apple, JPMorgan Chase, and GM, for example. Do I have to enter these stocks into my database?
A. Yes. We want to see this specific data entered into your database. You can add any other data that you wish, but we want to see these company’s stocks in your database at a minimum.
Two Example Clients:
Example portfolio for Allegany Fire Department Pension:
Example portfolio for City of Ferguson Municipal Workers 401K:
- The share prices shown in the images are only examples. You will need to look up the latest share prices. Enter your share prices into a copy of the Excel workbook that you can download below.
- The AUM in the Clients tab will automatically be updated when you complete the Price entry in the two Holdings tabs.