Perform conceptual design for the following case. You must turn in a CROW's FOOT ER diagram developed in VISIO. You may include, in your CROWs FOOT 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.
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 account's current balance, Cash Available, Withdrawal Balance, Total Gain (TG), Total Cost Basis (TCB). An account could belong to one AND ONLY ONE account type.
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. 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. A check constraint is used to make sure that the values for load type are 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 (POA) that has been signed.
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.