Task 1:(20 marks) Create an ER diagram (using UML notation) of the Robinson Commercial Services system. Please state any assumptions that you make.

                              Advanced Database Management Systems Assignment

Spring 2025 

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

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.

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

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. 

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

• Show all customers and their contracts
• Show the contract ids for all contracts involving cleaners
• Show all the contracts that could employ ‘Roger Mallory’
• Show all the components of the helpdesk service.
• Show all the services that are covered by Liverpool Victoria insurance.
• Show all contracts that include the component ‘Food Preparation’.
• Show all the customers that have contracts covered by insurance of type ‘Cancellation Cover’
• 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 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.