In this project, you will design and implement a relational database system to support the operations of an on-line stock trading system. You will use HTML for the user interface, Sybase for the database server, and Java, Javascript, and JDBC for connectivity between the user interface and database server. The database server is accessible from the PCs in both the undergraduate and graduate Transaction Processing labs (Computer Science Building, Rooms 2114 and 1239), on which you will be given an NT account and a Sybase account.
Please see the Transaction Processing Lab web site for general information about the Transaction lab and its policies, and the lab's Sybase web site for information on how to access Sybase in the Transaction lab and account information.
If you own a PC, you are encouraged to develop as much of the code as possible on your PC, to ease the congestion in the TP lab. For these purposes, I suggest you use SQL Server or MySQL Server.
SQL server is closest to Sybase, and in fact is a derivative of Sybase's SQL server. Both are derived from an earlier implementation of the ANSI SQL-92 standard. You should be able to get a copy of the SQL Server Developer Edition, for both 2000 and 2005, from the SINC site or via the Microsoft Developer Network Academic Alliance (MSDNAA) web site.
For MySQL, you can download a copy of MySQL server from the
Some of you have also asked about using Microsoft Access as the DBMS. This
should be OK as long as the server supports SQL and JDBC.
You are to work in teams of two. By now, you should already
have a partner. (Please let me know right away if you do not!) You and
your partner should now choose a name for your team/company. E-mail the names
of your team members and the name of your company to
xinzhao@cs.sunysb.edu
as soon as possible. If you are unable to find a teammate, please let me know
asap, and I will find one for you.
Here are two links to help you get started on the course project.
I have also placed a Powerpoint tutorial on blackboard on how to connect to
Sybase using Java and on how to develop the UI (User Interface) for your online
trading system course project. You can find the tutorial in the
Project Assignments subfolder of the
Assignments folder. (You can also find the tutorial via the
link associated with the announcement I recently posted on blackboard about the
tutorial.)
To further help you with the project, a former TA has developed a prototypical
database system for a University Registration system that closely resembles
the student registration system used as a running example in the course
textbook. You should consult this demo system as a guide for your project
development. You can access the demo system, and the underlying source code,
by following the links I placed in an announcement I recently posted on
blackboard. As you experiment with the demo system, you should also start to
become familiar with Java, Javascript, HTML, and JDBC on your own. I will
lecture about JDBC (Chapter 8 of the course textbook) after the midterm. You
might also want to buy the books about these languages recommended on the
course homepage. And don't forget that we have this semester two
undergraduate TAs who won the course-project competition last semester; consult
with them often and wisely.
It is recommended that you regularly check the blackboard Project
Assignments folder and the
PROJECT
ASSIGNMENTS web page for information on the course project. This page will
be updated from time to time with important information on how to proceed with
the project.
This semester there will be a CSE/ISE 305 Course Project
Competition in which the top-three finishing teams will receive award
certificates. I am trying to find a corporate sponsor for this competition and
will let you know when and if I succeed in doing so. So please do your best to
produce a professional-looking online stock trading system that offers a
full compliment of features and functionality, including at least those
specified below.
The basic idea behind your on-line trading system is that it will allow
customers to use the web to browse/search the contents of your database (at
least that part you want the customer to see) and to trade stocks over the
web. In this regard, it is a lot like the on-line trading system
E*TRADE. So visit
this site to get ideas as to what your system should look like.
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 stcok-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 be forthcoming.
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 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:
HTML and its successors provide facilities for creating pop-up and pull-down
menus, value lists, input/output forms, labels and customized reports. You
should make use of all of these capabilities, and in the process come up with a
system that caters to users with only limited computer knowledge. The
information you provide to customers should look professional and inviting.
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 weeks apart.
All documentation should be on-line. You will also be asked to hand-in
hardcopies when assignments are due.
In order to evaluate your final system, you will be asked to present a short
(15 minutes) demo to myself or the TA. This will most likely happen during
the last week of classes or shortly thereafter.
Getting Started
CSE/ISE 305 Course Project Competition
Project Specification
1 System Users
2 Required Data
2.1 Order Data
2.2 Stock Data
2.3 Customer Data
2.4 Employee Data
3 User-Level Transactions
3.1 Manager-Level Transactions
3.2 Customer-Representative-Level Transactions
3.3 Customer-Level Transactions
4 User Access Control
5 Utilities
6 User Interface
7 Documentation
8 Grading
About this document ...
Scott Smolka
Last Modified: Mon Feb 16 11:20:11 EDT 2009