From the scenario below create a conceptual model and use it to answer the tasks that follows below it.

An estate agency wishes to create a database to maintain information about their clients and the properties they are offering for sale. The following information is to be modelled:

Each property currently on their books is uniquely identified by its address. Other information in the database about the property will include its type (house, flat etc.), the number of rooms and the status of the sale (for sale, under offer and so on).

The list of clients includes both potential purchasers and vendors; some will of course be both. Each client is given a unique reference number. Other information about the client will include their name, address (not necessarily the same as the address of any property they are putting up for sale), and status (vendor, purchaser or both). Vendor clients may have more than one property to sell. Information about which properties prospective purchasers have visited or expressed an interest in must be available in the database. The estate agency also wishes to keep track of vendors who have accepted an offer from a prospective purchaser (that is the sale is agreed subject to exchange of contracts).

Once a vendor and prospective purchaser have entered into an agreement they will each nominate a lawyer to act on their behalf. The name and address of the lawyer will be stored in the database, but it cannot be assumed that the name of the lawyer is unique.

1. Logical Design Translate the UML design into a logical schema for implementation in a Relational DBMS such as MySQL or ORACLE (or any other available DBMS that you find suitable for such an application).

You will need to consider the storage parameters offered by that particular to create a database. You also need to detail the attributes for each entity and specify their types

2. Application programs Use Java to produce application programs that interact with the database through a graphical user interface to perform the following tasks:

a). Register a property for sale and assign it to an owner (if they exist, if not the client had to be registered first).

b). Capture property visits by clients and expressions of interest in a property available for sale.

c). List properties available for sale and how many people have visited or expressed interest in the property.

d). Assign a lawyer each for the vendor and prospective purchaser for a contract/agreement of sale.

e). List the prospective purchasers who had made an offer on properties for sale and the details of the contract if the sale has been agreed on.

f). Give a summary of the vendor clients and the properties they have/had for sale and updates on the status of the sale.

You will be given credit for design of the interface, and code that ensures integrity of the database is preserved. Program tests will include both legal and illegal data.