Database Design for a Stock Trading System
The Stock Trading System is an automated system for trading stocks and options of publicly traded companies and it has the following data requirements:
A company is uniquely determined by its name, while also having a headquarters address and an established date. Address is a composite attribute, which components street number, apartment number, city, street and zip Code. Some companies have publicly traded common stocks, and are named public companies. Each public company has only one such stock, each stock has a unique stock code and specified number of shares. Every stock trades on one or more exchanges, but the number of trading exchanges cannot exceed 9. An exchange is uniquely determined by its name. There is a stock symbol associate with a stock, which is used to trades on an exchange. The same stock may have different symbols on different exchanges. An option on a stock symbol is a security that is uniquely determined by its type, stock symbol, strike price, and expiration date. An option trades on the same exchange as its stock symbol. The type of an option is either a "put" or a "call". It can not be both, and it can not be something else. The last trading price and current daily volume for every symbol and option should be recorded.
Stocks and options are owned and traded by traders. A trader has a name and a tax id. The tax id uniquely determines the trader. The value of tax id is between "000001" and "900000". Traders do not trade directly, but via brokerages. A brokerage is uniquely determined by its name and state. Each brokerage deals with one or more exchanges and pays a fixed yearly fee to every exchange it deals with. The fee could be different for every brokerage/exchange pair. A trader owns at least one account with at least one brokerage. She/he may hold more than one account with the same brokerage and deal with more than one brokerage. An account is uniquely determined by brokerage and account number. A brokerage may have no accounts. Each account has exactly one owner. Accounts hold securities and cash. Note that a stock bought on one exchange could be sold on another, so it is stocks, not symbols, which are held. Do not forget to include options in accounts.
Traders place trading orders via their brokerages. An order specifies the account, exactly one symbol or option to trade, "bid" (buy) or "ask" (sell), number of shares to trade, and the order expiration. There are two types of orders: "market" and "limit". A limit order has the limit price in addition to the mentioned properties. The brokerage and order id uniquely determine the order. A transaction is effected in (possibly partial) fulfillment of two orders. Every transaction contains the following information: exactly one bid order, exactly one ask order, number of shares, transaction price, commissions paid by the buyer and the seller to their brokerages, and the timestamp. Exchange and transaction number uniquely determine the transaction. Note that an order could be filled by several transactions. The stocks and options are traded if their orders are fulfilled by some transactions.
Term Paper Questions
Part-1 Requirement Analysis
1. Identify the main entities of this stock trading system.
2. Can you think of more entities other than the one described in the data requirements to be added to the stock trading system?
3. Is the ability to model super-type /subtype relationships likely to be important in such environment? Why or why not?
4. Can you think of 4 more rules (other than the one explicitly described above) that are likely to be used in a stock trading system? Add your rules to the data requirements to be implemented.
5. Justify using a Relational DBMS like Oracle or SQL server for this system.
Part 2- Conceptual Design
6- Draw an EERD to accurately represent this set of requirements. This will be your Conceptual Design. Clearly specify any assumptions that you are making. You can use any tools (software) to draw the EERD.
Part 3 Logical Design
7- It has been decided to use a relational DBMS to implement the database. Perform the following steps.
a. Convert your Conceptual model (Part 2) to a Logical model that can be implemented in a relational DBMS like Oracle. During this process you replace M-N relationships and multi-valued attributes with constructs that can be implemented in the relational DBMS. Draw EERD for the logical model after your modifications. Feel free to change your conceptual model if needed.
b. Convert the EERD (item a) to a database design. Document your design in Database Schema format.
Part 4 Normalization.
Now, you are ready for implementation. Use appropriate naming conventions for all of your tables and attributes. Normalize all of your tables to third normal form. Make any necessary changes to the EERD from Part 2b. Explain why these changes needed to be made.
8 - Draw a dependency diagram for each table from Phase III a.
9 - Update data dictionary from previous delivery (part 3 b.) to add data type for each attribute in addition to specifying if it is primary key, foreign key, NULL is permitted, or its value is UNIQUE.
Part 4 Implementation.
10 - Write DDL SQL statements to create database, tables and all other structures. Primary keys and foreign keys must be defined appropriately. The quantity constraints of the relation between the entities, which should be described in EERD diagram, are not required.
11- Use the Create View statement to create the following views:
i. Stock-Symbol: This view returns the Company Name, Company Established Date, Stock Code, Number of Shares, and Exchange names of all stock symbols.
ii. High-Security: This view return stock code, last trading price and current daily volume for every symbol and option which last trading price is higher than $100.
iii. Good-Trader: This view returns all the Trades who have at least 3 accounts from at least 2 brokerages.
iv. Stock-Traded: This view returns the name for company, stock code and number of shares has been traded.
v. Popular-Trader: This view returns those traders who have traded shares more than 1% of all traded shares.
12 - Provide SQL statements for the following Queries. Feel free to use any of the views that you created in part (e):
vi. For each public company list the number of exchanges which its stock trades on.
vii. Find all Brokerages who do not have any accounts.
viii. List all Exchanges who have stock from the public company established before "Jan 01, 1980".
ix. Find each Trader who has exactly one account.
x. Find all Orders which have been fulfilled by at least 2 transactions.
xi. List all companies where the number of its traded share exceeds its total number of shares.
xii. List all the account from those Popular-Traders.
xiii. List all the stocks which have been placed orders by Good-Traders.
xiv. List all transactions wholly fulfilled its two orders.
xv. List all the accounts which have been placed "limit" order.