Advanced Database Management Systems

Assignment

Spring 2025
Words: 1600
Weighting: 40%
Learning Outcomes assessed: 1 and 2
Submission date: 25 April 2025 (11:59pm UK time)
Module Leader: Gary Budgen
Instructions to candidates:

• Please refer to the Assignment Presentation Requirements for advice on how

to set out your assignment.

• You must familiarise yourself with the Academic Dishonesty and

Plagiarism Policy relating to this programme and ensure that you
acknowledge all the sources which you use in your work. The policy is
available to access through your programme handbook.

• You must complete the Statement and Confirmation of Own Work, which is
available in the Additional Materials section of the VLE and at the end of
this assignment.

• Please make a note of the recommended word count. You could lose marks if
you write 10% more or less than this.

• You must submit a digital copy of this online examination through the VLE
virtual learning environment. Media containing viruses, corrupted media, or
media which cannot be run directly, will result in a fail grade being awarded
for this module.

• Diagrams may be word processed or drawn by hand and then uploaded using
a scanner or a clear photograph if necessary.

• Manipulating the word count through hiding text in diagrams could be

penalised.

• All electronic media will be checked for plagiarism.

Assignment Guidance
The learning outcomes assessed in this assignment are as follows:

LO Learning Outcomes Assessment Criteria

Practical Demonstrate a practical - Shape for ER diagram
Skills 1 understanding of SQL. - Relationships for ER diagram
- Correct use of notation (UML, SQL,
Design and implement a Data Dictionary)
Practical database system for a given
- Appropriate screen dumps
Skills 2 business scenario.
- Presentation of report

• 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 Programme Manager at uclan@nccedu.com 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 UCLan’s Assessment Handbook.
You must complete the attached form ‘Statement and Confirmation of Own Work’.

Advanced Database Management Systems Page 2 of 9 © NCC Education Ltd 2025

 You must attach the completed form to your assignment.
Case Study: Robinson Commercial Services
Background

You have been asked to design and construct a database system for Robinson
Commercial Services, a provider of business services such as office cleaning,
helpdesk functions, security and catering. Robinson Commercial Service’s customers
are other companies of various types. Robinson Commercial Services is based in the
United Kingdom.

How the company organises its work:

Until now Robinson Commercial Services have kept their records on a manual system
but have realised that it is time that they moved over to a fully computerised database
system. They have employed you to build a fully working database system to handle
their core business which is concerned with the following areas:

• Customers and Contracts

• Services provided to customers
• Components of services
• Staff assigned to customers to perform services

Background on how company organises its work:

Customers will take out a contract with Robinson Commercial Services for one or more
services. Services might include office cleaning, helpdesk functions, security or
catering. Each contract is insured with a particular insurance type and a record is kept
of the policy provider. Insurance is taken out to cover any claims should a problem
arise with the service provided.

Each type of service is made up of one or more components. For example, a cleaning
service could be made up of office cleaning, bathroom facility cleaning and exterior
cleaning. Components are classified by component contract type as hourly paid, piece
work, or full-time.

Each service assigned to a contract will have several staff assigned to it. Each staff
member is listed as being skilled in one or more of the services (catering, cleaning,
security or helpdesk).

Further details of the case study are shown in the documents below.

Please note that this is a small representative sample 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.

Advanced Database Management Systems Page 3 of 9 © NCC Education Ltd 2025

Document 1 Contract List

Below is a copy of a manual record with information about contracts and the services
required for those contracts

Note: contracts 233, 533 and 733 each have more than one associated service.

Document 2 Service Types

Below is a list of some of service types and their components.

Document 3 Contracts and Insurance policies

Contracts and Insurance policies for those contracts

Advanced Database Management Systems Page 4 of 9 © NCC Education Ltd 2025

Note: each insurance type is provided by the same insurance provider e.g. ‘Full Cover’
will always be supplied by Liverpool Victoria.

Document 4 Staff

Note: some staff have more than one skill

Document 5 Invoice.

Advanced Database Management Systems Page 5 of 9 © NCC Education Ltd 2025

 End of Case Study
Assignment
You are required to produce a report that addresses all the following tasks:

Task 1 (20 marks)

Create an ER diagram (using UML notation) of the Robinson Commercial Services

system. Please state any assumptions that you make.

Task 2 (10 marks)

Advanced Database Management Systems Page 6 of 9 © NCC Education Ltd 2025

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

Task 3 (5 marks)

Create a table listing for the Robinson Commercial Services 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 Robinson
Commercial Services system in a DBMS of your choice (not MS Access). You should
provide screen dumps to show that the SQL Create statements have been run
successfully.

Task 5 (4 marks)

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

Discuss the population of the database tables for the Robinson Commercial Services
system (order of population, issues and resolutions and SQL used) supported by
appropriate screen dumps.

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.

a) Show all customers and their contracts

b) Show the contract ids for all contracts involving cleaners
c) Show all the contracts that could employ ‘Roger Mallory’
d) Show all the components of the helpdesk service.
e) Show all the services that are covered by Liverpool Victoria insurance.
f) Show all contracts that include the component ‘Food Preparation’.
g) Show all the customers that have contracts covered by insurance of type
‘Cancellation Cover’
h) Use SQL to produce the information that could be used as the basis for the
invoice shown as Document 5.

Task 7 (5 marks)

Advanced Database Management Systems Page 7 of 9 © NCC Education Ltd 2025

Create a set of screen dumps showing all 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 such as concurrency control and recovery.

Task 9 (10 marks)

Produce a reflective overview of how the database and queries you have created have
met the needs of the business as outlined in the original scenario. 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 using a small representative sample of data
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.

Statement and Confirmation of Own Work

Programme/Qualification name:
__________________________________

All NCC Education/UCLan assessed assignments submitted by students must have

this statement as the cover page or it will not be accepted for marking. Please ensure
that this statement is either firmly attached to the cover of the assignment or
electronically inserted into the front of the assignment.

Student declaration

I have read and understood both UCLan’s Assessment Handbook and NCC
Education’s Referencing and Bibliographies document (available on the main
course page of the VLE). To the best of my knowledge my work has been accurately
referenced and all sources cited correctly.

I can confirm the following details:

Student ID/Registration number:

Name:

Advanced Database Management Systems Page 8 of 9 © NCC Education Ltd 2025

Centre Name:

Module Name:

Module Leader:

Number of words:

I confirm that this is my own work and that I have not colluded or plagiarised any part
of it.
Due Date:

Student Signature:

Submitted Date:

Advanced Database Management Systems Page 9 of 9 © NCC Education Ltd 2025