Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!


Get assignment help service to meet the high expectations of your professors

GET ASSIGNMENT HELP

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

Portfolio ID

Client ID

Client

Property ID

Property Address

Property Type

201

23

Roger Picard

2431

80 Overmeer

Rd, SE15

6NQ

Residential

House

201

23

Roger Picard

8901

99a Queen

Street, N1 2ER

Residential Flat

203

23

Roger Picard

9088

23 Redding Yard,

Bromley-byBow, E2 89Y

Commercial Property

203

23

Roger Picard

1990

23 St Anne’s

Place, N1

8RR

Commercial Property

301

11

Alison Brown

3099

99 Kings

Street, N1

988

Residential

House

301

11

Alison Brown

3097

11 Kings

Street, N1 988

Residential

House

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

Document 2. Tenancies

Tenant ID

Tenant Name

Tenant Type

Property ID

Start Date

End Date

Monthly Rent

T77

Gaslight Software

Business

1990

01-MAR-

2005

01-MAR-

2018

1000.00

T99

Michell Throssell

Private

2431

01-MAR-

2017

01-MAR-

2018

2500.00

T81

Edgar Kanne

Private

8901

03-APR-

2017

01-APR-

2018

2000.00

T99

Helpline

One-Stop

Shop

Government

9088

01-MAR-

2017

01-MAR-

2021

1500.00

T100

Dewitt Julio

Private

3099

01-MAR-

2017

01-DEC-

2017

1000.00

T101

Charisse Spinello

Private

3097

01-FEB-

2017

01-FEB-

2018

5500.00

Document 3. Example of Property Repair Sheet

Property ID 2431

Address 80 Overmeer Rd, SE15 6NQ

Repair Details: Replacement Front windows

Parts Used

Part Code

Part Name

Quantity

SF

Standard Frame

4

WF

Window Fitting

4

Staff

Staff ID

Name

S78

Dave Smith

S23

Holly Leman

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.
Assignment Help Features
Assignment Help Services
  • Assignment Help
  • Homework Help
  • Writing Help
  • Academic Writing Assistance
  • Editing Services
  • Plagiarism Checker Online
  • Proofreading
  • Research Writing Help
QR Code Assignment Help
elearningfeeds