Faculty of Arts, Science and Technology

Department of Computing
MSc Data Science and Big Data Anlaytics Programme
Level: 7

Module: COM736 Databases Systems and Data Analytics

Assignment: 1

Issue Date:

Review Date: Tutrorila Sessions

Submission Dates:

Part 1:

Part 2:

Estimated Completion time: 60 Hours

Lecturer: Bindu Jose

Verified by: Nigel Houlden

To be completed by student:
I certify that, other than where collaboration has been
explicitly permitted, this work is the result of my individual Name: ..........................................................
effort and that all sources for materials have been
acknowledged. I also confirm that I have read and
understood the codes of practice on plagiarism contained
Student Number:.........................................
within the Glyndwr Academic Regulations and that, by
signing this printed form or typing my name on an Date Submitted: .........................................
electronically submitted version, I am agreeing to be dealt
with accordingly in any case of suspected unfair practice. I
also certify that my attendance for the module has been at
least 70% Student Signature: .....................................
Are extenuating circumstances being claimed? YES / NO

If YES, give reference number: --------------------------------------

To be completed by lecturer
Comments:

Grade / Mark
(Indicative: may change when
moderated)

If Mitigating Circumstances are being claimed then please indicate your Code Number: ..........
Help desk case study (Overall weighting 50%)

Scenario

A company with a large IT function is setting up an IT Helpdesk to handle hardware and

software problems concerning the IT systems. Whenever anyone within the company has a
problem they can contact the helpdesk. One of the helpdesk operators will attempt to deal
with the enquiry, but if an immediate answer cannot be given the problem is passed to one of
a number of specialists.

An Information System is needed to log and track the helpdesk queries. This will enable
analysts to see how the equipment is performing overall, whether the helpdesk specialists
are sufficiently resourced to solve problems in an acceptable time, and whether there are
subject areas where training should be given to employees.

Proposed System Operation

When a new call comes into the helpdesk the names of the caller and helpdesk operator are
logged along with the time of the call, the serial number of the computer and, if relevant, the
operating system and software being used. The caller’s name will be checked against a
register of all personnel to retrieve the callers' ID number, job title and department. The
equipment will also be checked against a register of equipment to find the equipment type
and make. The software will be checked to see if it is under a valid license.

Every call is logged and each problem is given a problem number and this is supplied to the
caller so it can be quoted on any subsequent calls about the same problem. The helpdesk
operator will also record notes and descriptions of the problem. A reason for each call is
always recorded even if it is, in the case of a follow up call, just a note to say how desperate
the caller is getting.

When a problem is first reported the helpdesk operator will also allocate a problem type,
selecting it from a list of problem types. It is the skill of the operator to know what problem
type is most relevant and how specific the problem is. Some problem types are refinements
of more general problem types and so it is possible that the problem type allocation may be
altered at a later time if more information becomes available.

When the problem area is identified the helpdesk operator can look up previous problems of
the same type to see if the problem has occurred before and if so how it was resolved. It is
also possible to look up previous problems with the same equipment or from the same caller
to see if there were other related problems.

2
If the problem can't be solved immediately the helpdesk operator will use the system to look
up which specialist to refer the problem to. Each specialist will be an expert in one or more
problem types. If there is no specialist listed for a more specific problem type then a
specialist from the more general problem type will be used. The system will also list how
many problems the specialist is currently working on, so that if there is more than one
specialist for a problem type, the specialist who is currently the least loaded can be allocated.

When a problem is eventually resolved, the helpdesk operator or the specialist will log the
date and time it is resolved and record some indication of how the problem is resolved and
the time taken to resolve the problem.

Given the Help Desk scenario your tasks are as follows:

This assignment is split into two parts, to be undertaken as separate tasks. Part 1 concerns
the design of a database and consists of 3 tasks. Part 1 must be submitted on 21st March
2021. In Part 2 (1 task) you will implement the Help Desk database using your design. This
part need to be submitted on 20th April 2021.

Part 1 - Database design 40%

Task 1

Create a conceptual data model to represent the data requirements of the Help Desk
scenario. Your model should include an E-R diagram and entity headings (listing the
attributes associated with entity types). List any assumptions you have made.

Task 2

Using your conceptual data model, develop a relational schema consisting of the definitions
of the domains and relations that represent the entity and relationship types, including
primary keys, foreign keys and any constraints.

Part 2 - Database implementation 40%

Task 3
Use your design to implement the Help Desk database using Oracle DBMS. You should use
SQL statements to create tables, insert data and queries to provide the following
functionality: (Every SQL including the CREATE and INSERT statements, must be
documented and saved using notepad or MS Word)

Task 4
Test your database by answering the following questions. You must document the SQL
statement for each question:

1. List the details of equipment and associated software.

3
 2. Produce a list of experts (support staff) for a given problem area
3. Produce a list of all unresolved problems with the assigned Specialist
4. Produce a list of problems reported by a member of staff, the details of the support
staff who attended the problem and the solution provided by the support staff
5. Find (display) the average time taken fix fault for a given problem area.
6. Display a list of the most common problems and order them in according to the
frequency of their occurrence.
7. Display the equipment with the most reported problems showing problem details
8. Produce a list of problems that have been solved helpdesk operator

Update Queries

9. Add a new Specialist to the database

10. Assign a specialist for a given problem (it must be an unresolved, unassigned and
should find a matching Specialist)
11. Update the Call-log with appropriate details when the problem has been resolved.
12. An item of Equipment which, has previously had a fault reported, has now become
redundant so Delete it from the database.

PL/SQL

1. Create a procedure to produce a report of all unresolved problems with the assigned
Specialist
2. Creater a procedure to display list of the most common problems, average time taken
to resolved them, frequency of their occurrence and order them in according to the
frequency of their occurrence.

Task 5 – Weekly exercise: Submit in accordasnce with the exercise sheet

(20%)

Part 1 Submission – 21/03/2021

Single document combining Task 1, Task 2, and Task 3 must be submitted via University
VLE (Moodle).
 Diagram must be neatly drawn.
 Sufficient sample data (5 to 10) demonstrating the data optionality and system
controlled duplication (duplicated values in the foreign key column) must be included

Part 2 Submission - 20/04/2021

4
  Single document combining Task 3.a and Task 3.b must be submitted via University
VLE (Moodle).
 Ensure the database on Oracle Server.

Learning Outcomes

1. Demonstrate an advanced understanding of the principles of the relational database

model, data integrity and functional dependency.
2. Apply knowledge of database principles to identify and design appropriate solutions
to logical data design problems.
3. Perform data manipulation and information retrieval operations using query language
and stored procedure using commercially available DBMS software e.g. ORACLE
SQL, PL/SQL

Key skills for employability

1. Written, oral and media communication skills

2. Leadership, team working and networking skills
3. Opportunity, creativity and problem solving skills
4. Information technology skills and digital literacy
5. Information management skills
6. Research skills
7. Intercultural and sustainability skills
8. Career management skills
9. Learning to learn (managing personal and professional development, self-
management)
10. Numeracy

Guidance:

The work that you submit must be your own. You will be assessed on what you are able to
do, not on your ability to copy the work of others.

You will be judged on the functionality of the system; that is your lecturer will act as the Help
Desk Operator. Your lecturer will be looking for evidence of:

a) Creation of tables, relationships and constraints. Use of data validation and input
mask properties. Existing data in all tables
b) Working queries

Assessment Criteria

A A mark in this range will be awarded to any student who demonstrates an excellent
understanding of the problem and delivers a complete and good solution to all tasks.
The student’s arguments will be supported by relevant examples and be correctly
referenced.

B+ A mark in this range will be awarded to any student who demonstrates a good
understanding of the problem and delivers a good solution to all tasks. The majority of

5
 the student’s arguments will be supported by relevant examples and be correctly
referenced.

B A mark in this range will be awarded to any student who demonstrates a reasonable
understanding of the problem and delivers a satisfactory solution to all tasks. Most of
the student’s arguments will be supported by relevant examples and be correctly
referenced.

C A mark in this range will be awarded to any student who demonstrates some
understanding of the problem and delivers a satisfactory solution to all tasks. Work of
a reasonable standard, but lacking examples and correct referencing will fall into this
category.

Refer A Refer will be given to work that is unsatisfactory or contains serious

errors/limitations.