Assignment 4

Perform conceptual design for the following case. You must turn in an ER diagram developed in VISIO. You may include, in your ER Diagram, specifications that include additional constraints not shown on the diagram. The specifications should be written in English. If you believe that you need to make additional assumptions, clearly state them in the specifications.

The deliverable should be a file named YourFirstName_YourLastName.vsd. You should submit the file through the moodle assignment submission link.

TIPS:

When working with VISIO, remember to start with the normal entities and normal relationships. If you forget how to do certain things, check out the lab tutorial.

The 1-m relationship should always start from the 1 side.

NoBadStock (NBS), a modeling and simulation company, is planning to build World of Stocks, a new online multi-customer stock simulator. You are in charge of developing the back-end database that supports this simulation.

The simulator allows clients to model online trades. For each client, the simulator needs to store client information including login, password, email address, first name and last name. Logins for client should be unique, while first name and last name do not have to be.

A client can create and manage multiple accounts to play in the simulator. Each account should have a name that is unique. The system would also needs to capture the accounts current balance, Cash Available, Withdrawal Balance, Total Gain (TG), Total Cost Basis (TCB). An account could belong to one AND ONLY ONE account type. There are four account types (AT): Brokerage, Bank, Trust, and Retirement. Retirement Accounts Type (RT) must be of the type IRA, Roth, Rollover IRA, or Profit Sharing.

An account could contain securities. Each security has a symbol, quantity, price, market value, cost basis, and a gain-loss. Each security symbol has a name, a ranking and an asset class type. Ranking is a number between 1 and 6 with 6 representing the best profit potential and 1 representing the poorest profit potential. There are four types of asset classes: Equities, Mutual Funds, Bonds, and Cash. For equities, the system needs to store whether it is International with values of Y or N and if dividend reinvestment is chosen, also with a value of Y or N. For Mutual fund you must store if the fund is taxable with valid values of Y or N and its load type, Front-end, Back-end, and No-load. For Bonds you must track if the bond is taxable. For cash you must track the interest rate.

It is possible for many accounts to own the same security. Each security refers to items of the same symbol, for example, CL for Colgate Palmolive.

Each account also possesses a set of sales known as transactions. Each transaction is made unique by a system generated transaction id. For each transaction, the system records the account, symbol, date, quantity, price, fees, model_date, model_profit. Naturally, multiple accounts could have the same symbol, and one account could have many transactions. Additionally, other clients can make trades if the account is a joint tenancy or if there is a Power of Attorney (POAthat has been signed.

The system stores information in the transaction table about Account vs Model results. This feature compares total gains and losses on trades. The model predicts what the gain or loss would be if the purchase date was adjusted. The stock gains are determined by looking back over a 10 day period. The model stores the profit if they had purchased the same number of shares of CL ten days earlier or projects the profit ten days into the future. .

The system allows accounts to be managed by more than one client. Joint tenancy is indicated in the main client table.

The system uses a featured stock to help keep the game more challenging. Put your featured stock table from Assignment 3 into the diagram and connect it to the symbol table. Remember that your featured stock table uses the name, in the form YourFirstName-YourLastName-FeaturedStockSymbol. For my diagram, that table becomes Art-Hendela-CL. Entering this table correctly into your diagram will weigh heavily on the success of the game and the score received.