Reference no: EM131015376

Follow the requirements given below and referencing should be in harvard style

Assignment-1 Specification

In this assignment-1, you are to implement the data modelling for a database that is based on the following application scenario.

The application scenario

The Best Home real estate company needs to implement a database to manage properties. The real estate has delegated this task to an IT service company. As an officer of the IT service company, you are delegated to be the designer and developer of the database. To accomplish this task, you will divide the design and development tasks into two assignments. In this assignment (assignment-1), you will do the data modelling. In assignment-2, you will design and implement the database and relevant SQL queries. Consequently, a successful data modelling of this assignment is also important for assignment-2.

The application scenario of The Best Home real estate is as follows.

1. The Best Home manages two types of property, property for rent and property for sale. A property could be available at the moment, rented or sold.

2. The people who need to be managed by the database include the property managers of The Best Home, the property owners, the customers (buyers and tenants) and the solicitors.

3. A property manager can manage sale properties and/or rental properties. A property manager can supervise other property managers.

4. A customer can make an application for a rental property or give an offer to a sale property.

5. A solicitor may involve in a property sale.

6. A property manager, a property owner, a customer or a solicitor could have a phone, a mobile and/or email contact.

The above application scenario was not presented in very detail for the entities or relations because one of your tasks for this assignment is to analyse and model the data in real estates. You may need to do some research about the data in real estate management and model the data with necessary attributes under some assumptions.

The assignment specification

You are to develop an ER model for the data management of The Best Home. You will need to provide the ER model to cover the data and transactions of The Best Home. Your tasks are:

1. Identification and justification of entities. You need to identify all the necessary entities including super/sub type entities and justify your assumptions on those entities for the given application scenario.

2. Identification and justification of relationships. You need to identify all the necessary relationships and their cardinalities and justify your assumptions on those relationships/cardinalities for the given application scenario.

3. An ER diagram. You need to use the standard notations (as given in the textbook) to present the ER diagram, clearly representing entities and relationships, cardinalities and primary and foreign keys and using super types and sub types. The M:N relationship between entities is not allowed for the diagram. Thus you will need to translate an M:N relationship into multiple 1:M relationships with the aid of associative entities.

Note: use of a formal modelling tool such as Microsoft Visio is not required for the ER modelling. You can use any drawing tools to create the ER diagram and put it into the document.

Assignment 2- Specification and Marking Criteria

In this assignment, you are to design and implement The Best Home business database that you have modelled in the assignment-1 and a series of SQL queries to reflect the business logic of The Best Home.

Note: If your assignment-1 was incomplete to cover the application scenario of The Best Home or incorrect in some parts, you will need to remodel it before completing this assignment. Read the marking comments of assignment-1 carefully and talk to your marker if necessary.

The specification of this assignment

1. Database implementation

• You are to design and implement the ER model of assignment-1, including entities/associative entities, relationships and cardinalities, into a Microsoft Access database. You need to make sure that your database is compatible with MS Access 2010 for marking purpose.
• You need to ensure that relations of your database meet 3NF.

• Your database needs to include enough sample data to support the required SQL queries.

2. SQL query

As a way to demonstrate success, you are to implement a series of queries on the database to reflect the business logic of The Best Home. Note: You are to create the queries manually by using the SQL view of MS Access query builder to demonstrate your competency in SQL. Failing to do so may result in marks to be deducted.

Query-1: List all the contact details of a particular customer to show:

• The customer's name

• The contact type e.g. email

• The contact details e.g. [email protected]

Note: the customer's name is a parameter to enter when executing this query in Microsoft Access. A customer could have more than one contact e.g. email, mobile and home phone.

Query-2: List all the rental properties that the asked prices (the price on the advertising) are the same as the rent prices (the price on the rent contract) to show:
• The property type

• The property address

• The asked price

• The actual rent price

Query-3: List all the sale properties that have been available for 6 months but not sold yet to show:
• The property type

• The property address

• The property sale price

• The property available date

• The property manager's name

Query-4: List the properties that were sold in a particular suburb e.g. ‘The Range' to show:

• The buyer name

• The property manager who sold the property

• The property type

• The property address

• The asked price

• The deal price

• The date when the property was sold

Note: the suburb name can be hardcoded in your query.

Query-5: List the contact details of the solicitor who involved in a sold property of a particular address, e.g. '22 George Street' to show:
• The property type

• The property address

• Solicitor name

• All contact details of the solicitor

Note: the property address can be hardcoded in your query.

Query-6: List all rent properties which have 2 or more applications to show:

• The property ID

• The property type

• The property address

• The weekly rent

• The property manager's name

Query-7: An owner wants to complain to a higher level officer about the management of his/her rental property. Find the supervisor of the property manager, who is currently looking after the property to show:
• The property type

• The property address

• The owner name

• The property manager's name

• The supervisor's name of the property manager

Note: the property address can be hardcoded in your query.

Query-8: List all property managers in descendent order of the number of properties that they have been looking after to show:
• The property type

• The property address

• The property status

• The property manager's name

3. Query Report

Assume that a property manager will contact the owner of a sale property to talk about the current offers for the property. Create a report to sort the offers in descending order (from the highest to the lowest offered price) to show:
• The property type

• The property address

• The property sale prices

• The offered price

• The name and his/her contact details of the customer who gave the offer Note: the property address can be hardcoded in your query.

4. Implementation document

You are to write a short report to demonstrate that you have thought carefully about the issues that arise when implementing the database and the SQL queries. You need to address:

• How you populate data for multi-valued attributes to make sure that they are enough to support the required SQL queries. Concrete examples from the database are required to justify your statement.

• Whether it is necessary to implement the property address as a composite attribute for the required SQL queries. Concrete examples from the database are required to justify your statement.