• +1-617-874-1011 (US)
  • +44-117-230-1145 (UK)
Live Chat
Follow Us:

Advanced Database Management Systems Assignment

University of Central Lancashire 
BSc (Hons) Business Computing and Information Systems
Module:Advanced Database Management Systems Assignment
Semester:Spring 2020

Assignment Guidance

The learning outcomes assessed in this assignment are as follows:

Learning Outcomes

Assessment Criteria

Demonstrate a practical understanding of advanced SQL.

Design and implement a database system for a given business scenario using advanced data modelling techniques.

Shape for ER diagram

Relationships for ER diagram

Correct use of notation (UML, SQL, Data Dictionary)

Appropriate screen dumps

Presentation of report

ADMS Assignment Paper

Case Study: Brunner Letting and Management

Background

You have been asked to design and construct a database system for Brunner Letting and Management, a property management company based in London, United Kingdom. They manage property portfolios for owners. A property portfolio is a set of property investments owned by an individual, a group or a company. A property is a building of some sort.

How the company organises its work:

Brunner Letting and Management have clients that they classify by the term “owners”. An owner will have one or more property portfolios, 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. Tenants are defined by type.

As well as managing the tenancies of properties Brunner Letting and Management maintain the properties 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 Brunner Letting and Management to carry out their business.

Further details of the case study are shown in the documents below which give a representative example of data and can be taken as representative of a much larger data set.

Please note that the data as represented here is not necessarily in a normalised state and it is your job, as the database developer, to organise the data in its most optimal state.

Document 1 Property Portfolio Records

Portfolio ID

Owner

ID

Owner

First Name

Owner Surname

Property ID

Property Address

Property Type Code

201

23

Roger

Picard

2431

80 Overmeer

Rd, SE15

6NQ

RH

201

23

Roger

Picard

8901

99a Queen

Street, N1 2ER

RF

203

23

Roger

Picard

9088

23 Redding Yard,

Bromley-byBow, E2 89Y

CP

203

23

Roger

Picard

1990

23 St Anne’s

Place, N1

8RR

CP

301

11

Alison

Brown

3099

99 Kings

Street, N1

988

RH

301

11

Alison

Brown

3097

11 Kings

Street, N1

988

RH

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

Document 2 Tenancies

Tenancy Code

Tenant

ID

Tenant Name

Tenant

Type

Code

Property ID

Start Date

End Date

Monthly Rent

TC18001

T77

Gaslight Software

BS

1990

01-MAR-

2005

01-MAR-

2018

1000.00

TC17038

T99

Michell Throssell

PR

2431

01-MAR-

2017

01-MAR-

2018

2500.00

TC17039

T81

Edgar Kanne

PR

8901

03-APR-

2017

01-APR-

2018

2000.00

TC17040

T99

Helpline

One-Stop

Shop

GV

9088

01-MAR-

2017

01-MAR-

2021

1500.00

TC17041

T100

Dewitt Julio

PR

3099

01-MAR-

2017

01-DEC-

2017

1000.00

TC17042

T101

Charisse Spinello

PR

3097

01-FEB-

2017

01-FEB-

2018

5500.00

Document 3 Repair Sheet

Repair ID R9021

Property ID 2431

Address 80 Overmeer Rd, SE15 6NQ

Repair Date 14/08/18

Repair Description: Replacement Front windows

Parts Used

Part Type Code

Part Type Name

Quantity

SF

Standard Frame

4

WF

Window Fitting

4

Staff Involved

Staff ID

Name

S78

Dave Smith

S23

Holly Leman

Document 4 Property Types

Property Type Code

Property Type Description

RH

Residential

House

RF

Residential Flat

CP

Commercial Property

Document 5 Tenant Types

Tenant

Type

Code

Tenant

Type

Description

BS

Business

PR

Private

GV

Government

CH

Charity

NG

NGO

Document 6 Staff

Staff ID

Name

Mobile No

S78

Dave Smith

0788989898

S23

Holly Leman

0712324321

S99

Lev Samuels

0765656565

S101

Ahmed Khan

0764321177

S102

Keith Kelani

0786435932

End of Case Study

Assignment

You are required to produce a report that addresses all of the following tasks:

Task 1 (20 marks)

Create an ER diagram (using UML notation) of the Brunner Letting and Management system. Please state any assumptions that you make.

Task 2 (10 marks)

Critically assess the normalisation you have undertaken to produce a set of relations for the scenario. You should discuss the process of normalisation as general and also the specific way it has been applied in your work.

Task 3 (5 marks)

Create a table listing for the Brunner Letting and Management system. You should show all the attributes and identify primary and foreign keys.

Task 4 (10 marks)

Create the relevant SQL CREATE TABLE statements for implementing the Brunner Letting and Management system in a DBMS of your choice (not MS Access). You should provide screen dumps to show that the create statements have worked.

Task 5 (4 marks)

Populate the database with the data shown in the case study.

Discuss the population of the database tables for the Brunner Letting and Management system (order of population, issues and resolutions and SQL used) supported by appropriate screen dumps.

Tasks continue on the next page

Task 6 (16 marks)

Create the following queries. Note that you should use the AS, COUNT, GROUP BY and ORDER BY clauses where you think they are appropriate. You should provide a screen dump to show the query being run and the output of the query.

  1. Write a query that selects all the portfolios and properties for a particular owner.
  2. Write a query that selects the tenants and their tenancy dates.
  3. Write a query that selects all the staff.
  4. Write a query that shows all parts involved in the repair of a particular property.
  5. Write a query that shows all the tenants for a particular owner.
  6. 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.
  7. Write a query that shows all properties with a monthly rent below £3000.
  8. Use SQL to produce the information that could be used as the basis for the repair sheet shown in document 3.

Task 7 (5 marks)

Create a set of screen dumps showing all of the data in each table – the data must be ordered by the primary key.

Task 8 (15 marks)

Critically assess the issues that will need to be addressed with regard to transaction management for the new database system. You should consider what support for transactions will be needed, concurrency control and recovery.

Task 9 (10 marks)

Produce a reflective overview of how the database and queries you have created have met the requirements of the business. Some of the issues you could discuss include how the data model reflects the structure of data used by the business; the utility of the queries you have created; and how all the parts of the assignment constitute a usable system. You should also reflect on any future improvements that could help the business.

The remaining 5 marks will be awarded for the presentation of the report. (5 marks)