STW220CT Data and Information Retrieval Assignment 1

Introduction

This assignment is in five parts, design, data and queries, derived data, analysis and an evaluation.

All parts of the assessment relate to the Clinton Letting and Management scenario below.

Scenario

Clinton Letting and Management is a property management company based in London, United Kingdom. They manage properties for owners. An owner will have one or more portfolios of properties, which will consist of one or more properties. Properties are defined as being of a particular type such as residential house, residential flat or commercial property.

A particular property will be rented by a tenant. The terms of that rental are defined as a tenancy. Tenant are defined by type (‘Private’, ’Business’, ’Government’, ’Other’).

As well as managing the tenancies of properties Clinton Letting and Management maintain the property by carrying out repairs when they are needed.

A property repair will involve one or more members of staff and can involve one or more parts.

The system should be capable of storing all the information needed for Clinton Letting and Management to carry out their business. A number of additional requirements are outlined as in Task 2 below, including data entry and queries.

Please state any assumptions you have made about the scenario.

Please Note: The data shown in the assignment is not necessarily normalised, and that it the candidate’s task to organise the data in the most optimal way possible. For example, the paper records shown below will not necessarily map directly to database tables. The candidate is expected to use these tables as a starting point for their own normalisation and optimisation of the Clinton Letting and Management.

Please note also that all SQL scripts should be shown along with their results.

Below are a sample of the paper records currently kept by the company

Document 1. Property Portfolio Records

NOTE: Roger Picard is shown as having two separate portfolios.

Document 2. Tenancies

Document 3. Example of Property Repair Sheet

Property ID 2431

Address 80 Overmeer Rd, SE15 6NQ

Repair Details: Replacement Front windowsarts used

Staff

Staff ID	Name
S78	Dave Smith
S23	Holly Leman

All questions are compulsory

1. Create an entity relationship model for the proposed database system for Clinton Letting and Management.

Note:

You can use Visual paradigm, Microsoft Visio or any other tool to create ER

Diagram

2. A discussion of the normalisation taken to produce a set of third normal form relations for the proposed system.

Note:

You can show the normalization steps in Excel.

3. Create the tables in SQL and show the CREATE scripts as running in the programming environment.
4. Insert data on all the tables and give a listing of this.
5. Write a query that selects all the portfolios and properties for a particular owner.
6. Write a query that selects the tenants and their tenancy dates.
7. Write a query that selects all the staff.
8. Write a query that shows all parts involved in the repair of a particular property.
9. Write a query that shows all the tenants for a particular owner.
10. Write a query that produces the output that could be used to show all the details of staff working on a repair job on a property.

Note:

1. Convert solutions into image files and save them in MS-Word file or PDF file.
2. Create an ER diagram using any tool.
3. All the output should be shown after each SQL statement.
4. Upload your assignment in Moodle (file size should not exceed 50MB).