In this project, you will design and implement a relational database system to support the operations of an on-line stock trading system, along the lines of E*TRADE, TD Ameritrade, and many others. You will use HTML for the user interface, for the database server, and Java, Javascript, and JDBC for connectivity between the user interface and database server. You should install a MySQL or SQL server on your laptop.
You are to work in teams of three. Many of you will form
teams of three on your own. If not, do not worry. Please
attend class on a regular basis, and I will make sure that every student
is placed in a team of three.
Here are two links to help you get started on the course project.
The second link is to an online tutorial on Conditional Orders, a concept I discuss further below.Your database system must be based on the specifications and requirements that follow.
The users of your system will be the customers that use your system to trade stocks and pay fees for doing so, customer representatives who provide customer-related services, and the site's manager. You should assume that the computer knowledge of the users is limited (say, that of a typical AOL subscriber), and thus your system must be easy to access and operate. Customers of a stock-trading system are sometimes also known as clients, so I shall use these two terms interchangeably.
The data items required for the stock-trading database can be classified into four categories: orders, stocks, customers and employees, where an order is an order to buy or sell a certain number of shares of a particular stock at a certain price.
This classification does not imply any particular table arrangement. You are responsible for arranging the data items into tables, determining the relationships among tables and identifying the key attributes. In addition, you should include indices in your tables to speed up query processing. You should base your choice of indices on the type and expected frequency of the queries outlined in Section 3. Finally, you should specify and enforce integrity constraints on the data, including referential integrity constraints.
As I mentioned in class, you will first create an E-R diagram of your online trading system before developing your relational model. Details of this assignment will soon become available on blackoard.
This category of data should include the following items:
An order is the mechanism a customer uses to buy or sell a certain number of shares of a particular stock at a certain price. A transaction fee of 5% is associated with every order.
Your online trading system will also support Conditional Orders such as a Trailing Stop or Hidden Stop order. A Trailing-Stop order uses a trailing stop to automatically decide when to place a sell order. A trailing stop is set at a percentage or dollar amount below the stock's current market price, and a sell order will be placed if and when the share price falls to the trailing stop. The trailing stop is adjusted as the share price fluctuates.
In contrast, a Hidden-Stop order uses a fixed hidden-stop price to automatically decide when to place a sell order. That is, a sell order will be placed if and when the stock price falls to the hidden stop.
To understand conditional orders better, suppose that you have just bought 1000 shares of GM at $50.00, and you decide that you only want to risk $5.00 per share on this transaction. Accordingly, you immediately place a hidden-stop order at $45.00. This means that if the price of GM should drop to $45.00, your broker will sell your 1000 shares at a market price of $45.00. The use of a hidden-stop order will therefore pre-determine the maximum loss a trader will incur.
Instead of placing a hidden-stop order on your GM shares, suppose now that you place a trailing-stop order with a trailing-stop value of $5.00 (or, equivalently, a trailing-stop percentage of 10%). Thus, your shares will be sold if the share price drops to $45.00. But instead of declining, the price of GM increases to $60.00; but so does the trailing stop. So now your shares will be sold if the share price drops to $55.00. Thus the trailing-stop technique allows an investor to set a limit on the maximum possible loss without setting a limit on the maximum possible gain, and without requiring paying attention to the investment on an ongoing basis.
To find out more about how conditional orders work, please consult the E*TRADE Conditional Orders Tutorial.
This category of data should include the following items:
An order involves the purchase or sale of a certain number of shares of a stock at a certain price. Stocks are of a certain type: GM is an automotive stock, IBM is a computer stock, etc. You can populate your database with any kind of stocks you like. We will provide you with all the stock data you need to demo your system to us at the end of the semester.
The items required for this category include:
A given customer may partake in any number of stock transaction, either as a buyer or as a seller. A customer may have one or more accounts from which to trade stocks. Associated with each account is a stock portfolio, indicating which stocks (and number of shares) are held in that account. The customer's rating should reflect how active a trader he or she is.
This category of data should include the following:
A database transaction can be viewed as a small program (written in the DML) that either updates or queries the database. Transactions that change the contents of the database must do so in a consistent manner. Moreover, transactions should not interfere with one another when running concurrently.
What follows is a breakdown of the user-level transactions that your database system should support. To make sure transactions maintain the integrity of the database, you must write them using the SQL transaction structuring capabilities (i.e., begin transaction, commit transaction, etc.).
The manager should be able to:
Customer Representatives should be thought of as stock brokers and should be able to:
Customers should be thought of as online traders and should be able to easily browse your online trading system on the web and place orders to purchase or sell stocks. In particular, they should be able to place a trailing-stop or hidden-stop conditional order, and place an order to buy or sell stocks at market or close-of-market price. While they will not be permitted to access the database directly, they should be able to retrieve the following information:
Your database system should provide controlled access to the data by distinguishing between the different types of users: manager, customer representatives, and customers.
In addition to the transactions described above, the system should provide facilities for:
For Project Assignment 3, we will provide you with a basic user interface for your online dating web site, configured to display dummy data. Your job will be to reprogram the UI so that it displays actual query results. Details to follow.
You will be required to supplement your completed database implementation with a design document that contains information concerning your design criteria and decisions. The following is a list of some of the information you should include:
You will also be required to submit a Users Guide that carefully explains how to use all aspects of the system. It should be understandable by non-computer experts. Be sure that the user interface (screen design, menu structure, etc.) is clearly explained.
You will be given three assignments: 1) produce an E-R and relational model of your system; 2) implement (in SQL) and execute all transactions described in the above project specification; and 3) implement (using Java, Javascript and JDBC) the final interactive system to support your online stock trading system. The due date for the first assignment will be announced shortly; as for the the other two assignments, the due dates for all three assiginments will be spaced roughly three to four weeks apart.
All documentation should be available on-line. You will be given instructions for online submission of your project assignments prior to their due dates.
As stated above, you will be working on the course project in in teams of three. A rough, three-way division of labor for the first project assignment is as follows: