Advanced Database Management Systems

RESIT Assignment
Autumn 2022

Words: 1600
Weighting: 40%
Learning Outcomes assessed: 2 and 5
Submission date: 23 December 2022 (11:59pm UK time)
Module Leader: Gary Budgen
Instructions to candidates:

• 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:

Learning Description Assessment Criteria

Outcome
LO5 Demonstrate a practical • Shape for ER diagram
understanding of advanced
SQL. • Relationships for ER diagram
LO2 Design and implement a
database system for a given • Correct use of notation (UML, SQL, Data
business scenario using Dictionary)
advanced data modelling
• Appropriate screen dumps
techniques.
• Presentation of report

• It is the student’s responsibility to familiarise themselves with the NCC Education

Academic Misconduct policy.

• 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 [email protected] 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

• 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.

Submission Requirements

Advanced Database Management Page 2 of 10 © NCC Education Ltd 2022

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

Advanced Database Management Page 3 of 10 © NCC Education Ltd 2022

Systems
Case Study: Watt Building Services

Background

You have been asked to design and construct a database system for Watt Building
Services, who are a provider of business services to their customers consisting of
other companies of various types. Watt Building Services is based in the United
Kingdom.

How the company organises its work:

Until now Watt Building Services have kept their records on a card index 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 Watt Building 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, part-
time 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
the data as represented here is not necessarily in a normalised state and it is the job
of you, the database developer, to organise the data in its most optimal state.

Advanced Database Management Page 4 of 10 © NCC Education Ltd 2022

Systems
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.

Advanced Database Management Page 5 of 10 © NCC Education Ltd 2022

Systems
Document 3 Contracts and Insurance policies

Contracts and Insurance policies for those contracts

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

Advanced Database Management Page 6 of 10 © NCC Education Ltd 2022

Systems
Document 5 Invoice.

End of Case Study

Advanced Database Management Page 7 of 10 © NCC Education Ltd 2022

Systems
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 Watt Building Services 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 in general and also
the specific way it has been applied in your work.

Task 3 (5 marks)

Create a table listing for the Watt Building 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 Watt
Building Services 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 Watt Building Services system
(order of population, issues and resolutions and SQL used) supported by appropriate
screen dumps.

Questions continue on the next page

Advanced Database Management Page 8 of 10 © NCC Education Ltd 2022

Systems
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 ID’s 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)

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.

End of paper

Advanced Database Management Page 9 of 10 © NCC Education Ltd 2022

Systems
Statement and Confirmation of Own Work

Programme/Qualification name:

All NCC Education 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 NCC Education’s Policy on Academic Dishonesty

and Plagiarism.

I can confirm the following details:

Student ID/Registration number:

Name:

Centre Name:

Module Name:

Module Leader:

Number of words:

I confirm that this is my own work and that I have not plagiarised any part of it. I
have also noted the assessment criteria and pass mark for assignments.

Due Date:

Student Signature:

Submitted Date:

Advanced Database Management Page 10 of 10 © NCC Education Ltd 2022

Systems