Reference no: EM13936921

Realestate First Corp

A friend has a small real-estate business. He handles a small number of clients with their selling and leasing arrangements. He has a small number of clients because he concentrates his business on serious property investors, providing them a superior service. Most of his clients own multiple properties that they lease and are buying and selling properties often.

He currently has an exercise book for each client, with a list of each property owned by that client. The first page of the book has details about the client, like their address and various contact details. This is used to enable contacting the client by either mail, email or by phone.

The other pages of the client exercise books have details about each property that has been bought or sold for the client. These pages first include details about the property, including address and construction type (brick veneer, double brick, weatherboards, etc...), roofing type (steel, tiled, etc.), number of bedrooms, number of toilets, living room size, dining room size, kitchen size,etc... The other details written in the client exercise books, for each property, is the buy and sell details of the property, which includes the amount, stamp duty and settlement date.

Some properties are leased (rented). Currently a seperate exercise book is used to keep a record of the leases. This includes details of the people leasing the property, details of which property is being leased, and details about the lease (like the cost per week, and the length of the lease).

The small real estate company wants the following reports:
• List of customers and their details.
• List of properties currently owned by clients of the real-estate company.
• List of leases currently active and details of the weekly lease payments.

The small real-estate agency wants to computerise the manual system they currently have. What you have been asked to do is provide the database model for the system

To assist the students, try and come up with a small database design for their new website.
Provide the following:
• Entity Relationship Diagram.
• Relational Model (showing fields, primary keys, foreign keys and concatenated keys).
• Justify the choices you have made. Are the entities and relationships you have chosen free of problems ? What assumptions have you made ?

Create the database tables using SQL statements in your own database. Include
• CREATE TABLE queries - that create each of the tables in your design
• INSERT INTO queries - that insert values into your tables (at least 5 rows per table)

Write some queries to test out the database.
• One query should be a simple one involving one table and a selection criteria.
• The second query should involve a join (at least 2 tables)
• The third should be a group by query.
• Fourth query that involves a nested query.