In this project, you will design and implement an object-relational database system to support the operations of an on-line job/resume posting service. 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 Wen Xie 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 7.1.
You can order 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. Thanks to Orson Ward for finding out about this Program. You can also download a copy by clicking on the Download option of the menu on the left or from the URL http://www6.software.ibm.com/dl/db2pde/db2pde-p.
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 wenxie 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 on-line job/resume posting service is that it will allow job seekers to post their resumes on your web site, companies to post descriptions of job openings, and job seekers and companies to browse/search the contents of your database (at least that part you want them to see). In this regard, it is a lot like the web site monster.com. So visit this site to get ideas as to what your system should look like.
Job seekers can use the web site to set up an interview with a perspective employer and, likewise, companies can use the web site to set up an interview with a perspective employee. Your system will also allow job seekers to attach an image of themselves to their resumes and possibly an audio greeting as well.
Your database system must be based on the specifications and requirements that follow.
The users of your system will be the job seekers who upload their resumes to your web site; companies (recruitment personnel) who upload descriptions of job openings; and various employees of your web site: customer representatives who arrange interviews and provide other customer-related services, and the web 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.
The data items required for your database can be classified into six categories: resumes, companies, job categories, job descriptions, interviews, and employees (of the web site).
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 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:
Job seekers can post their resumes on your web site and indicate the kinds of jobs they are interested in. They can also browse your web site for job descriptions; when they find a job they are interested in, they can request an interview. Each time your web site arranges an interview for a job seeker, he or she will be charged $1.
A job seeker has the option of attaching an audio greeting to his or her resume. To play and capture audio greetings, you should use Sun's Java Media Framework 2.1 (JMF2.1). You can find the API for this package at the JMF2.1 web site, or from a link in the Project Hints Page.
The items required for this category include:
Companies may post job descriptions on your web site and browse the site for resumes of perspective employees. If they see a resume they like, they can request an interview with the job seeker. Each time your web site arranges an interview for a company, the company will be charged $10.
The items required for this category include:
The items required for this category include:
Job categories are hierarchical. For example, the category Computers might have sub-categories Hardware, Software, Database, etc. Its parent category might be Information Technology.
The items required for this category include:
An interview is between a company and a job seeker. You should assign a customer representative of your web site to arrange/oversee each interview. You can choose any scheme you like for assigning customer representatives to interviews, as long as you document the scheme clearly. For example, you can assign a customer representative to each category in your job-classification hierarchy. The revenue generated under a category is credited to its representative.
The items required for this category include:
The employees of your web-site company include a number of customer representatives and a manager.
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 job types. When a new job description is received from a company, the manager must decide into which category to place the job description, and create a new category if necessary.
Customer Representatives should be able to:
The customers of your web site, i.e. job seekers and companies, should be able to easily browse your web site and and perform downloads and request interviews. You should also equip your customers with the ability to play downloaded audio files and view downloaded document or image files. While a customer will not be permitted to access the database directly, he or she should be able to retrieve useful information. For job seekers, this includes:
Companies 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.
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.