Stockbroker’s office: You are setting up a system to track trades and portfolios for a small stockbroker’s office. For each broker in the office, you want to store a first name, middle name/initial, last name, phone extension number, cell phone number, e‐mail address, and date hired. For each of the firm’s clients, you want to store a first name, middle name/initial, last name, address information, home phone number, work phone number, cell phone number, and e‐mail address. Each client is assigned to a single stockbroker, and the database should remember which one. You also have a master list of securities which your firm is licensed to trade. For each security, you want to store a ticker symbol and a description. No two securities may have the same ticker symbol. Finally, you want to keep track of each client’s transactions. For each transaction, you want to store the date/time it occurred, a transaction type (“Buy,” “Sell,” “Dividend,” etc.), which security was involved, the net number of shares, and the net cash. For example, (ignoring commissions) buying 100 shares of IBM at $98.55 per share would mean a transaction type of “Buy,” net number of shares=+100, and net cash=–$9,855.00; while selling 10 shares of Cisco Systems (ticker symbol CSCO) at $27.21 per share would mean a transaction type of “Sell,” net number of shares=–10, and net cash=+$272.10.

1.       Design a database to hold all this information. You can 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 need not enter any data or create any forms. Create a “Documenter” report for the database.