SQL assignment question 1

Unit: Database Design and Development

Assignment title: Clinton Letting and Management

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 windows

Parts Used

Staff

Task 1 – 30 Marks

• An entity relationship model for the proposed database system for Clinton Letting and Management.
• A discussion of the normalisation taken to produce a set of third normal form relations for the proposed system.
• A data dictionary for your proposed system.

Task 2 – 32 Marks

• Create the tables in SQL and show the CREATE scripts as running in the programming environment.
• Data on all the owners, portfolios and properties. Give a listing of this. 6) Data for customers and tenancies. Give a listing of this.
• Data on all staff.
• Data on property repairs including staff involved and parts used.
• Write a query that selects all the portfolios and properties for a particular owner.
• Write a query that selects the tenants and their tenancy dates.
• Write a query that selects all the staff.
• Write a query that shows all parts involved in the repair of a particular property.
• Write a query that shows all the tenants for a particular owner.
• 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.

Task 3 – 13 Marks

15) The company have said they would like to extend the database to include costing information for repair work. Data should be kept on the cost of parts and the rate of pay of staff. There should be a way of calculating how much work costs for particular properties, the portfolio they belong to and their owners. You should include an outline of how you would derive this data using SQL and specify the method of implementing it.

Task 4 – 10 marks

16) In the future Clinton Letting and Management could expand as an organisation. Describe the factors that might make them consider implementing a distributed database.

Task 5 – 15 Marks

17) Give an evaluation of how the work you have done has met the requirements of the firm. This should include data storage and applications. You should also discuss how you have standardised the company’s data.

Submission requirements

• Your submission should be in the form of a single word-processed document that includes any necessary diagrams.
• The word count for the document is 2000 words (excluding text in any diagrams). You should explain any assumptions you have made.
• A digital version must be submitted on a CD, USB flash drive or other similarly acceptable medium, along with a copy of the developed database.

Subjects List