Advanced Database Management Systems Assignment Spring 2020

Assignment Guidance

The learning outcomes assessed in this assignment are as follows:

•   It is the student’s responsibility to familiarise themselves with the Academic Dishonesty and Plagiarism policy contained in the Programme Handbook.

•   Any assignment submitted after the submission deadline, without prior approval, will be given 0% and the student will be referred.

•   The late assignment submission may be marked as a referral attempt, but only a maximum mark of 40% can be awarded for that particular assessment.

•   If a student requests an extension to the official submission date this must be done in writing to the Course Director at least five full UK working days before the official submission date. This request must be accompanied by supporting evidence.

•   This assignment is worth 40% of the overall final mark for the module.

•   Students should write no more than 1600 words (+/- 10%) for this assignment

•   Students are encouraged to read widely in preparing for the assignment, making reference to articles in academic journals and other relevant sources.

•   All references should be cited in text and included in a reference section at the end of the report using the Harvard Referencing Scheme.

•   The Statement and Confirmation of Own Work must be completed and submitted with the assignment.

•   This assignment must be submitted by the due date and time as given on the front of this assignment.

•   Please refer to the Assessment Criteria contained in the Programme Handbook which shows how the level of marking relates to your standard of work.

Submission Requirements

You must read and understand NCC Education’s policy on ‘Academic Dishonesty and   Plagiarism’ . You must complete the attached form ‘Statement and Confirmation of Own Work’ and attach your completed form to your assignment.

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

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

Document 2 Tenancies

Document 4 Property Types

Document 5 Tenant Types

Document 6 Staff

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.