In this project, you will design and implement an object-relational database system to support the operations of an on-line MP3 Music Store. You will use HTML and Java for the user interface, DB2 for the database server, and Javascript, JDBC and SQL for connectivity between the user interface and database server. The database server is accessible from any Solaris box on the department network, and from the PCs in the Graduate PC lab (Computer Science Building, Room 1239). Probably the easiest way to gain access to a department Solaris box is in the Pub Lab (Room 2206).
To use a machine in the Grad PC lab, you need to physically be sitting in front of the machine. Use your department account and password to log in. Regardless of where you do your development, you will need a separate account to access the DB2 server machine. Please e-mail the TA shiyong in order to receive your DB2 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 PC and Pub labs. As long as you use DB2 (or some similar database product supporting SQL 3 such as Oracle8i), HTML, Java/Javascript, JDBC, etc., it does not matter where your project runs. If you like, you can even bring in your own PC or laptop when it comes time to demo your project at the end of the semester. The version of DB2 running within the department is 5.0 but more recent versions of DB2 are also fine (even better!), such as 7.1.
You can download a free copy of DB2 Version 7.1 from IBM by going to IBM's DB2 Scholars Program web site. Look under "What's new". You can also order a CD-ROM containing a free 60-day trial copy of DB2 Version 6.1. Thanks to Orson Ward for finding out about the DB2 Scholars Program.
You are to work in teams of two. Teams of any other size (including one and three) will not be allowed under any circumstances. Pick a partner and also choose a name for your team/company. E-mail the names of your team members and the name of your company to shiyong AT cs DOT sunysb DOT 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.
The TA is putting together a prototypical database system on which you can base your project. We will give you access to the code, which should help you a lot. In the meanwhile, you should start to become familiar with these languages on your own. Make sure you buy the books that we have recommended -- see the course homepage.
It is recommended that you regularly check the course homepage, which can be found at http://www.cs.sunysb.edu/~sas/cse532/fall00/, for information on the term project. This homepage will be updated from time to time with important information on how to proceed with the project.
The basic idea behind your on-line music store 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 play, capture, purchase, and download MP3 files over the web. In this regard, it is a lot like the on-line music stores at emusic.com, napster.com, and mp3.com So visit these sites 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 purchase recordings from your store and subsequently download and play them; suppliers that capture recodings on MP3 files and download these to your web site; customer representatives who process customer orders and provide other customer-related services; and the store'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.
The data items required for the music-store database can be classified into five categories: recordings, customers, employees, sales, suppliers, and em>orders.
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.
However, before you begin thinking about your relational model (i.e. tables, indices, etc.), you should first create an E-R diagram of your music-store database system. The first project assignment, the details of which will be announced shortly, will ask you to create E-R, relational, and SQL models of your database system.
The items required for this category include:
Each recording has an associated MP3 file. Your database will classify recordings according to recording type. The classification scheme you are to implement should be tree-like. See mp3.com for a good example of such a classification hierarchy. To implement such a hierarchy in your database system, you should use subtyping; i.e. make Recording Type a user-defined data type, and subtype on that type.
As described in Section 3, you will want to create web pages that allow a customer to download a recording from your online music store and then listen to the recording. You will want to do something similar for suppliers; in this case, a supplier should be able to upload a recording to your web site, after having captured the recording on an MP3 file. To implement the play and capture functionality you will need, you should ues Sun's Java Media Framework 2.1 (JMF2.1). You can find the API for this package at the JMF2.1 web site.
Although a download of a recording should accompany every purchase of a recording, the number of copies sold and the number of copies downloaded (the last two items in the list above) can be different. This can happen, for example, if you allow some recordings to be downloaded for free as part of a promotional effort.
The items required for this category include:
The items required for this category include:
The items required for this category include:
You are to assign a customer representative to each transaction. This person is responsible for ensuring that the customer's order is filled properly and for answering any questions the customer may have regarding his or her transaction. You can choose any scheme you like for assigning customer representatives to transactions, as long as you document the scheme clearly. It should also be made clear during your project demo how your scheme works.
One scheme, proposed by the TA Shiyong, is that the store's manager assigns exactly one customer representative to each category of recordings in your classification hierarchy. The revenue generated under a category is credited to its representative. This scheme makes sense to me and I strongly suggest you follow it.
The items required for this category include:
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.
The manager should be able to:
The manager is responsible for creating and maintaining the classification hierarchy of recording types. When a new recording is received from a supplier, the manager must decide into which category to place the recording, and create a new category if necessary.
Customer Representatives should be able to:
The customer should be able to easily browse the music store over the web and make purchases and perform downloads. You should also equip your customers with the ability to play downloaded MP3 files. While the customer will not be permitted to access the database directly, the customer should be able to retrieve the following information.
Customers should also be able to sample recordings. That is, they should be able to download the first five seconds of any recording, at no charge. Even better, would be if your online music store "streamed" samples to customers, thereby allowing customers to listen to samples without first having to do a download.
Suppliers should be able to fill orders they are contracted for. To do so, they should have the capability to capture audio on an MP3 file and then download the file to your database in order to fill an order.
Your database system should provide controlled access to the data by distinguishing between the different types of users: manager, customer representatives, and customers.
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 assignments to produce an E-R and relational model of your system. The due dates for these assignments will be announced shortly.
All documentation should be done in HTML and be available on-line. Do not hand-in any hardcopies of anything during this course unless I explicitly ask you to.
After you have submitted the final system, you will be asked to present a short (15 minutes) demo to myself or the TA. This will most likely happen during finals week.