School of Information Sciences

INFSCI 1022: Database Management Systems

Spring 2012

PROJECT DESCRIPTION

Purpose of the Project

You are to analyze the requirements for, design, implement, demonstrate, and document a database system for one of the below applications. You can also choose your own application provided it is approved by the instructor or GSA. You should submit your application choice together with the group roster by "Proj. Group Lists Due" date specified in the class schedule. We recommend you to do it as early as possible: the applications will be "distributed" on the first-came-first-served basis. Two groups cannot work on the same application. If the application you would like to work on has already been taken you will have to choose from what is left.

Sample Application Descriptions

1. Auto Insurance Company

The system should be able to provide price quotes for users. It should also be able to provide policy related information i.e. account balance, policy limits etc. for current policy holders. Employees of the company should have access to the same information for the purpose of answering customer inquiries and issuing new policies. Other relevant employee functions include: renewing, canceling and endorsing policies (i.e. changing limits of insurance etc.).

2. On-line Brokerage

Any user should be able to learn about services offered and their corresponding fees. The system should provide the capability to open an on-line account and buy products such as equities, bonds, mutual funds, IRA's etc. Company employees should be able to review customer accounts etc. There should be different types of employees i.e. brokers and managers. The system should provide managers with the ability to review broker performance.

3. On-line Travel Agent

Users should be able to browse available vacation packages and other products. They should also be able to open accounts and make purchases. Employee users include travel agents, managers etc.

4. Hotel

Users should have the ability to make reservations and choose room size and features i.e. suites, # and type of beds etc. The company needs to keep track of room availability.

5. University

Students should be able to register for classes, add/drop, check their grades, check their billing information etc. Employees should be provided with appropriate access based on their function.

6. On-line Pharmacy

Customers should be able to check their own prescription history, balance of payment, etc. Pharmacists and doctors must be able to check medical records regarding patient's allergies etc. Doctors can enter on the patient's prescription and the pharmacists should process the information and enter the delivery order for the patient.

7. Airline

Customers need to book tickets, check flight information etc. Employees need to check in passengers, keep track of luggage, update arrival and departure times etc.

8. Ground Shipping Company

Customers should be able to check prices based on weight, speed of delivery etc. They should also be able to check the status of packages, reschedule deliveries etc. Employees need to handle inquiries, billing etc.

9. Library

Users need to search for books, renew books etc. Employees need to keep track of checked out books, overdue books, assess fines, add new books to the system etc.

10. On-line Auction

Users should be able to browse available items by category, bid on items, open accounts with their address and billing information etc. Sellers need to be able to post items for sale. Buyers should be able to rate and comment on sellers. Employees may need to process billing information etc.

11. On-line Job Hunting

There are two types of customers: individuals and companies who have positions available. Individuals should be able to build their own profile and post their resume and browse for jobs... Companies can post positions and browse candidates.

12. Apartment Rental

The system should be able to store (create and update) and retrieve information of the tenants: name, phone no., apt no., type of apartment (studio, 1BR, 2BR etc), price, lease-term, location, facility (laundry, exercise room). The management should be able to check the availability of the apartments, update the price etc., The customers should be able to find the apartment according to their requested criteria.

13. School Admission

Admission personnel should be able to add/update/retrieve applicants' information (name, contact address, applied degree, year apply), test score (SAT, TOEFL, GRE etc), status (reject, accept, pending). The System should be able to provide statistics related to the applications (i.e. number of applications for a particular year, percent of admission, average score for undergraduate/graduate applicants).

14. Cell phone plan

The system should be able to create/update and show the available plan according to the area (i.e., city, state), carrier, monthly fee, free minutes get, additional rate per minute etc. The customers could request the information according to their criteria. It should also show the top-pick plan and allow customers to purchase plan.

15. Movie Rental

The system should be able to store (create/update) and retrieve detail of the movie (category. actor, actress, movie-rate, year release, length of the movie etc). The system should allow customers to check the availability of the specified movies, provide top rental movie etc.,

______________________________________________________________________

You must take your project through the stages of specification, design, and implementation. The result of your project should be a working implementation that use SQL to access data stored in a relational database.

All projects are required to demonstrate at least the following functionality:

Customer Browsing. The users must be able to search the database for particular items based on various attributes and must also be able to do browsing (i.e., less focused searching).
Update Transactions. The system must be able to handle payments and sales, new inventory, new users, etc. and other changes to the database that are necessary for the day-to-day running of the business.
Aggregating and grouping queries You should implement interesting queries for data analysis using grouping and aggregation (e.g., which group of customers are buying given product the most?
Error Checking. The system must be robust. For example, items should not be sold if they are not currently in stock, etc. You should implement appropriate integrity constraints.

Rules of the game

Implementation Tools: The recommended database system of choice is the ORACLE server that runs on the Unix cluster in room 819, IS Building. SQL is a requirement. A group may choose to use other database systems and front-end implementation tools after discussing with instructor.
Additional Requirements: The project must represent a fairly sophisticated database application. In particular, the database must contain multiple (e.g., at least five) relations, and the database design must include indexes, primary keys and integrity constraints.
Groups: The project is to be done in groups of 3 students. The groups are ``self-policing'' (e.g., each group is responsible for its own division of labor, scheduling, etc.). Note: If an unreconcilable problem arises in your group, it is your responsibility to contact the instructor as soon as possible. After the project is due, it will be too late.
Assumptions: In cases where the above description of the application is incomplete, it is acceptable to make assumptions about the application providing that: 1) they are explicitly stated in the final report, 2) they don't conflict with any of the requirements specified above, and 3) they are "reasonable". If you have a question about the acceptability of any of your assumptions, check with the instructor or GSA. Interesting questions should be raised in class.
Report: A final report should be handed in for grading at the end of the term. The report must be formatted in a reasonable manner (i.e., using a text processor and a decent printer). The final report is due during class on the "Project Due" date specified in the class schedule.
Implementation: The project requires a working implementation of the system to be built, tested, and demonstrated. A large part of the project grade depends on the quality of this implementation. Computer accounts with access to ORACLE will be provided for this purpose.

Report Requirements

The final report must contain:

A short overview of the system including identification of the various types of users and user groups who will be accessing the system in various ways.
A list of assumptions that you have made about the system.
A graphical schema of the database using the E-R model with a short description of each entity set, relationship set and their corresponding attributes.
A set of relational schema resulting from the E-R diagram with identification of primary and foreign keys.
The DDL statements to create the relational schema in some appropriate Normal Form, identification and justification of which Normal Form the schema is in.
A description example scenarios of how various types of users will interact with the system with example screen shots.
A description of your testing efforts and erroneous cases that your system can detect and handle.
A description of the system's limitations and the possibilities for improvements.

In addition, a demo of the working system will be required. All members of the group must attend this demo, and must be prepared to explain and demonstrate those aspects of the project for which they were responsible. The source code for the project should be available on-line during the demonstration.

Demos will be scheduled after April 16th.

Go back to 1022 Home Page.