You are to design, implement, document, and demonstrate a database system using the Oracle installation at UMBC. Your system shall support certain activities pertaining to buying and selling homes in the Baltimore-Washington area, as described below. This document outlines the basic requirements for this project. Additional features and enhancements could be added.

System Requirements

1.     System Users

1.1   Home Owners: These are people whose homes are to be sold. Home owners sell their homes through listing agents. Home owners shall be able to view all of the data pertaining to home sales in the database. In addition, home owners shall be able to view the offers made on their homes by home buyers.
1.2   Home Buyers: These are people who will buy one of the homes available on the market through a realtor. Home buyers shall be able to view all of the data pertaining to the home sales in the database.
1.3   Realtors: Realtors are agents hired by both home buyers and sellers. Realtors shall be able to view all data in the database pertaining to the home sales in the database, and make offers on behalf of their clients on homes on the market. They shall be able to select homes to view based on several criteria specified by their customers, such as  location, number of bedrooms, and price. Realtors may see information on all the homes ever sold that is kept in the database.
1.4   Listing Agents: Any of the realtors can serve as listing agents to the home owners. Listing agents collect information from home owners about their home, determine, with possible help from your system, the asking price for a home, and put up a home for sale. They also accept or reject offers made by buyers for the homes for which they are the listing agents. Listing agents can sell the homes they list by accepting an offer on behalf of their clients. Listing agents charge the home owners a commission, which is negotiable (usually 6% of the final sale price). A home on the market can have only one listing agent, while a listing agent may have none to several homes listed on the market at any time.
1.5   Selling Agents: Any of the realtors can serve as selling agents to home buyers. Selling agents collect requirements for a home to buy from their clients, research the database to determine which homes are suitable, and with consultation from their clients, make offers on the homes on the market. Selling agents charge the listing agents a commission which is negotiable (usually 3% of final home sale price).

For each user group you must implement an authentication scheme within your implementation. Realtors have more privileges than an ordinary user browsing the system.

2.    System Functions

Following is a description of the processes your system must support. You are to make reasonable assumptions about your system's behavior in situations not covered by the specifications below, and list all such assumptions.

2.1   Listing a home: Listing a home means officially making the home available for sale on the homes database. A home can be listed only by a listing agent, and by only one listing agent. At the time of listing, all characteristics of the home are to be specified, which include the address, location, lot size, whether wooded, style (contemporary or Victorian or ranch or split-level), age of the home, area of the built-in space, number of bedrooms, number of bath rooms, number of fire places, garage size if present (one-car, two-car, or more), whether home has a basement, if so whether it is finished, and additional special features. A home listed must include an asking price, and the date and time of the listing. Finally, your listed home data must include at least one picture showing the front view of the home.

2.2   Querying listed homes: All users in the system shall be able to query the data on homes listed based on various criteria, such as the locality, price, number of bedrooms, and other home features. They should also be able to see the pictures of the homes selected. Example: "Show me all houses in Columbia, MD with two-car garages and three bedrooms costing $200,000 or less."

2.3   Making an offer: Selling agents should be able to make offers on homes on the market. Offers must include a price, which may be less than, the same as, or more than the asking price. More than one offer may be made on a home by the same selling agent. An offer once made cannot be revoked. All offers made on any home on the market must include the date and time at which the offer was made, and be kept in the database to enable future queries.

2.4   Viewing offers: The listing agent and the home owner must be able to view all offers made on the home listed. Home owners must be able to view only the offers made on their home. Listing agents must be able to view only the offers made on the homes they list.

2.5   Accepting an offer: The listing agent shall be able to accept one and only one offer among the ones made. Any of the offers made may be accepted, not necessarily the most recent or the highest. Once an offer is accepted, the home is to be considered sold, and is to be taken off the market, with no future offers accepted. The date and time of the sale are to be recorded. All data pertaining to the sale of the house, such as the agents involved, the date, time, and price of the listing, each of the offers made, and the final offer accepted, must be available for future statistical analysis.

2.6   Asking price estimation: Your system must facilitate the determination of the "fair" price by analyzing all of the data available on a home, and the data on the prices at which similar homes have "recently'' been sold in the location by any user of the homes database system. The listing price may or may not equal the "fair" price, since the home may include special features or defects not listed. In addition, your system must be able to estimate the average price of the various features of a home in a given location "recently", such as a garage, a bedroom, or a fireplace.

2.7   Listing agent recommendation: Your system must be able to recommend a listing agent to a home owner who wants to sell his/her house based on (a) the number of days on the market for homes sold by the listing agent, (b) the price of the houses sold in the location or (c) the commission. Your system should be able to rank all of the listing agents in a given location based on the above criteria with details of the number of days on the market the price of the house sold in the location of interest, and the commission charged.

2.8   Selling agent recommendation: Similarly, your system must make recommendations to the prospective home buyers on the selling agents based on (a) the number of houses sold and (b) the price at which the houses were sold. Obviously as a buyer you would want to pay the lowest possible price for a house.

2.9   Listing agent report: Each listing agent must be able to pull up a report on all homes he or she has listed currently with details on when the home was listed, what price, address, and so on.

2.10 Selling agent report: Each selling agent must be able to view details of the buyers he or she is currently representing, including their names, address, price limit, and the area they are interested in. Remember that each buyer may be interested in buying a house in more than one area.

3.   System Data

Your system must include sufficient number of both homes on the market and homes sold to make the queries meaningful. Sales must span three months. You must include at least (a) 50 houses sold (b) 20 houses on the market (c) three locations (d) 10 listing agents and (e) 10 selling agents. You must include sufficient variety on homes and their features to support the queries required in the assignment.

4.    Other Requirements

1. Java based GUIs are a requirement.
2. At least two PL/SQL packages are required.
3. You are to include at least five constraints in your design.

The final phase of the project requires a working implementation of the system. A large part of the final grade for the project depends on this implementation. You will use the Oracle 8 on the UMBC Oracle server. Accounts will be given out. PL/SQL and/or embedded SQL should be used to write stored procedures and functions and Java and JDBC for application programming.

Project Teams

• You are to form voluntary teams with each team consisting of at least three and no more than four members.
• See Dr. Mundur if you need help in team formations.
• Maintain a log of each team member's contribution for each phase and submit that along with the report for that phase.
• A final accounting of each member's contribution to the project is due at the completion of the project.

1. 9/12/00: Names and email addresses of team members
2. 10/5/00: Phase I due at the beginning of class
3. 11/2/00: Phase II due at the beginning of class
4. 12/5/00: Phase III due at the beginning of class
5. 12/7/00 through 12/12/00: Demo (schedule TBD)

1. description of the purpose of the database application
2. additional assumptions you made
3. an E-R diagram
4. list of attributes for each entity and relationship set
5. a relational schema in some appropriate normal form
6. appropriate views for each user group

Phase II -- Detailed design report with an implementation plan consisting of:

1. revisions to the design, if any
2. SQL statements to create the relational schema
3. SQL statements to create the constraints (primary, foreign, unique, check, not null)
4. pseudo-code for implementing each of the functions required for the project with embedded SQL statements and/or PL/SQL packages
5. GUI/screen design

Phase III -- Final project report consisting of:

1. final design document
2. sample output for each system function
3. self evaluation: limitations of your system and how you can overcome them
4. source code for all your programs/packages

Project demonstration:

A 15 minute demo of the system is required. All members of the team must attend the demo and explain their contribution.