You have a large investment portfolio, and want to set up a database to keep track of your holdings. You do businesses with multiple brokerage firms, and you have more than one account with some of these firms (for example, one for your IRA and another for day trading). For each brokerage firm, you want to store a name, description, address, city, state, zip code, and phone number. For this problem, assume that you do not have access to a zip code table. For each account, you want to keep track of its account number, date opened, cash balance, and type (for example, “401(k)” or “regular”). In addition to cash, each account may contain one or more holdings of securities: for example, 1000 shares of IBM and 500 shares of Ford Motor Company. For each holding, you want to store the number of shares, the ticker symbol of the security, the date you acquired the shares, and their cost basis. Shares in some securities (such as mutual funds) may be fractional. Securities are identified by a ticker symbols consisting of up to eight characters (for example, CSCO for Cisco Systems or VZ for Verizon). Assume you have access to a master table giving a name and description for each ticker symbol that appears in your portfolio. You may hold the same security in different accounts, and have multiple holdings of the same security (acquired at different times) in the same account.
1. Design a database to hold all this information. You may create synthetic keys as necessary or convenient. Create a database design outline and an ER diagram.
2. In Access, create all the necessary tables for this database, and choose a reasonable datatype for each attribute. Assume phone numbers are in standard US format. Create all necessary relationships between the tables and enforce referential integrity. You do not have to enter any data or create any forms. Create a “Documenter” report for the database.
