REA Properties

This assignment aims to improve your design and programming skills in database systems. In particular, it requires you to exercise programming skills in SQL and PL/SQL under Oracle PL/SQL Developer or SQL*Plus tool.

You have been given the task of optimising the design and building a database using ORACLE RDBMS for a real estate agency in Australia. You are identified as the best database consultant they could hire by REA Properties to build a database to manage their property and customer details. Due to some unforeseen events the consultants who were working on this case has to halt the design and development half way through. You have been given a copy of their work including the design, schema and some scripts.

Assignment Description

The assignment is based on an imaginary database to manage information for a real estate company. The following gives an informal description of the requirements for the system:

You are dealing with an imaginary real-estate agency "REA Properties" that has branches in a number of suburbs in Australia, and deals with a range of properties, including domestic houses and units (both buying/selling and rental) and commercial properties (similarly, buying/selling and leasing). Assume that all branches are able to access the database, but don't worry about the details of how this is accomplished (treat it as if all users logged onto a single server which contains a single copy of the database).

• The database must keep track of information about REA's staff members, including their personal contact details and payroll information. Each staff member is associated with one particular branch, where they do all of their work.
• The database must maintain information about branches, including where they are located and which properties they handle (each property is entirely managed through one particular branch, but, of course, the database holds information about all properties from all branches). We also need to know who manages each branch.
• The database maintains information about properties, including location, owner and what kind of property it is (see below under constraints). REA also wants to record when a property is first listed by the company, whether it is being listed for sale or rent and which staff members (one or more) are the contacts for all matters related to this property (such staff members are called the "property managers" for that property).
• REA deals with a number of different kinds of clients: owners, buyers and renters. There are two types of property owners: private owners and business owners; in both cases we record the same kind of information, primarily their contact details. We also need this kind of information for buyers and renters, but for buyers we also wish to maintain details of the kind of property that they are looking for, and for renters we also need to maintain information about their rental agreement and payment record.
• Properties may be advertised in a newspaper. The database should record the main details for each advertisement (for example, which newspaper it appeared in, what the advertisement said, and when it appeared).
• Properties are occasionally made "Open for Inspection" (on a particular day for a particular time period), and the database must keep a record of such events, including ones scheduled for the future.
• Properties are offered for sale either by auction or privately. For auction sales, the database should record when and where the auction occurs and its result (when known). For private sales, the system should note the vendor's reserve price. For rental properties, the database should record information about the rental rate and current availability.

Constraints:

• People's names are stored in the format: first name followed by last name.
• The type of sale takes a value from {AUSD, PISD, AUHB}, where AUSD means "auction sold", PISD means "private treaty sold", and AUHB means "highest bid" (this is used for properties that are not sold at auction).
• Property types take a value from {House, Unit, TownHouse, Commercial}.
• Property addresses should be decomposed into street address, suburb, and post code.

Tasks

Task 1:Fine tune the existing Design

Produce an ER-Diagram and Relational Schema to satisfy the data requirements or REA Properties in the best possible way. You must use the standard notations used and discussed in the class.

Task 2: Build the database

Produce the script for creating the tables, make sure you have identified and defined all Primary Keys and Foreign Keys. Implement the database in the Oracle server here at Glyndwr University.

Task 3: Test your database

Make sure you can satisfy the data and application requirements of REA properties.

To test the database you have to produce the scripts and results for the following tasks.

Sample Data

In order to test the database you must have some sample data. You will be provided some sample data (as INSERT Queries devised by the consultants who were working on this project). These data/queries might not be suitable for your design, make sure you are using the given data as a starting point and it is your responsibility to make the necessary amendments to suit your design.

Task 3.1: Oracle SQL

You should use correct SQL statements in Oracle to implement the following queries and data modifications.

In all cases, names should be displayed in the format FirstNameLastName (e.g. a person with LastName='Smith' and FirstName='John' would be displayed as John Smith) in a column labelled NAME. Similarly, addresses should all be displayed in the format Street Suburb PostCode in a column labelled ADDRESS.

Each query and update should be implemented by ORACLE SQL.

Select Queries

1. Listthe properties which are not advertised nor has open inspections.

Some students raise the problem that this question could have two interpretations. We would like to clarify that this question is asking for properties that are not listed in either of advertisement and in open inspections.

2. List the owners who own a property in Kingsford or Coogee.

3. List the properties that are still on the market for lease. A property is still on the market for lease if it is for lease and there is no rental contract on this specific property.

4. List all properties which are at a suburb starts with "C", which may be recorded in the database in either capital or lower case.

5. List the staffs who manage at least one commercial property in Coogee. A staff is managing a property if he/she is the contact person for this property.

6. For each suburb, list the difference between average price of properties sold via auction and the ones sold by private sale in that suburb in the last 365 days.

7. List the features for each house whose asking- or reserve-price is less than $200,000.

8. List the properties (which are for private sale or auction) whose asking- or reserve-price is lower than at least one of the offers received for this property. 

9. List the staffs who have sold the greatest number of properties in the last 365 days. A property is sold by a staff if these staffs are the contact person of the property which is recorded in the Contact table.

10. For each property that was sold in last 365 days and have not been either advertised or open for inspection in last 365 days, list its features. We only consider those properties which are not listed in either of the advertisement and open inspections in the last 365 days.

Update Queries

1. Remove the advertisements in newspaper "Sydney Morning Herald".

2. Give renters in Maroubra a 10% reduction in rent but terminate their agreements on June 30 2007. Note: "renters in Maroubra" means the properties are in Maroubra.

3. Increase the asking price by 10% for the properties for private sale that are still on the market.