In this project, you will design and implement an object-relational database system to support the operations of a streaming Video on Demand (VoD) web site. 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 of the sbpub machines in Room 2206, and from the PCs in the Graduate PC lab (Computer Science Building, Room 1239).
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 Yan Rong in order to receive your DB2 account information. Also, please see the Project Hints Page for more information about accessing the DB2 Server.
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 7.1.
You can order or download a free copy of DB2 Universal Developer's Edition Version 7.1 from IBM by going to IBM's DB2 Scholars Program web site and registering for 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. Pick a partner and also choose a name for your team. Since you are expected to do a professional-looking job on this project, you can think of your team as a small hi-tech company. E-mail the names of your team members and the name of your company to yanrong 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 CGI programming, Java, JDBC, and HTML 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, for information on the term project. The homepage will be updated from time to time with important information on how to proceed with the project.
The basic idea behind your VoD web site is that it will allow customers to browse/search the contents of your database (at least that part you want the customer to see) and to "rent" videos by streaming downloads at date/time specified by customers. Available videos should be organized into a classification hierarchy much like the way the books portion of the amazon.com web site is organized. Each video-file download will be treated as a transaction by your system and the user charged accordingly. Your web site will maintain MPEG video files having the .mpg file extension. See www.mpeg.org for further information about MPEG files.
Your database system must be based on the specifications and requirements that follow.
The users of your system will be the customers that download streaming videos from your web site for viewing on their PCs or laptop computers; suppliers that capture videos as MPEG 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, navigate, and operate.
The data items required for the music-store database can be classified into five categories: videos, customers, employees, sales, suppliers, and 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 VoD 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 video has an associated MPEG file. Your database will classify videos according to the video classification type. The classification scheme you are to implement should be tree-like. See the books section of the amazon.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 Video Classification 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 video from your VoD web site as streaming video at a specified date/time. You will also want to allow suppliers to upload video files to your web site, after having captured the video as an MPEG file.
Although a streaming download of a video should accompany every rental of a video, the number of rentals and the number of copies downloaded can be different. This can happen, for example, if you allow some video to be viewed 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 possible scheme is that the store's manager assigns a customer representative to each category of videos in your classification hierarchy. The revenue generated under a category is credited to its representative.
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 video types. When a new video is received from a supplier, the manager must decide into which category to place the video, and create a new category if necessary.
Customer Representatives should be able to:
The customer should be able to easily browse the contents of your web site and make rentals and perform streaming downloads. 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 videos. That is, they should be able to download the first few seconds of any streaming video, at no charge.
Suppliers should be able to fill orders they are contracted for. To do so, they should have the capability to capture video as MPEG files and download the files 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. Demos will take place on the Friday and Saturday of the last week of classes.