Assignment Title Coursework
Component/Module INST0001 Database systems
Assignment Code AS01-INST0001
Set by Kosmas Kosmopoulos
Moderated by
Learning outcomes to be assessed:
• Describe in brief how a business operates
• Utilise standard graphical notations for communicating the form of the database to the customer and the
programmer in a standard manner
• Analyse the requirements for business database, create and query it utilising the SQL language in a database
management system (this year XAMMP has been explained and MySQL)
• Elicit the database specifications from brief documents
• Perform the database conceptual requirements analysis
• Describe the logical diagrammatic model of the data and how to normalise it
Submission requirements:
Brief Assignment
Description
This assignment requires students to work in pairs towards describing a business case study,
and its database needs considering both the conceptual and normalised logical model of the
data. The students will the work individually in order to demonstrate their ability to query
the database and the data resulting from such queries.
The complete coursework will be produced in the form of a report that represents 100% of
the final mark.
Conditions Assignment Submission must be completed through a designated Moodle Area

Make sure the course code (INST0001) and your name and student number, and the name
and student number of the other person you have worked with, are clearly marked below the
report tile. Only one student will submit the coursework for the pair work in Part A
All students will submit the individual work in Part B
Two submission links would be available and clearly labeled on moodle

Marking Criteria
and Weighting
Rubric

These are described in moodle

The assignment is worth 100 % of the overall assessment for this course

This assignment must be completed: Part A in groups of 3 (50%) – Part B individually (50%)

Date work set (provisional):


Date and time due in (provisional):


Standard lateness penalty will apply.

The target date for the return of marked work and full feedback
(provisional):
(tentatively within 4 weeks, according to DIS policy)

A detailed description of the assignment is
available at:


UCL DEPARTMENT OF
INFORMATION STUDIES
INST0001 Coursework Assignment Brief

https://moodle.ucl.ac.uk/course/view.php?id=23196
Marked work is returned on: 29 April
2022

29th March 2022
10/02/2022
2

Detailed Assignment Description

You are designing and implementing a payroll system for a company of your choice. The
employees in the company are paid on hourly base, overtime is paid 1.5 of the basic rates. The
database must have the followings elements:
• A manner for the payroll assistant to login to the online system.
• The list of employees and their attributes (e.g. designation, name, surname, job title, years
in the company, hourly pay, national insurance number, contact details, etc.).
• Payment history (e.g. payment received in the first week of June)

REQUIREMENTS: The database should contain at least 15 employees. There should be 3
different job types with a different hourly pay. The database should have at least 6 weeks history,
the hours worked in a day for each day worked). Some of the employees must do overtime. For
each job title, there might be several grades, which lead to a different hourly rate. For example, an
assistant Grade 6.1 has an hourly are of £14.50, while an assistant Grade 6.2 has an hourly rate of
£16.10.





3


PART A – In groups [ 50 marks ]
As a group you must to produce a Written Report (2000 words max) the minimum font size to be
used in the report is ARIAL point 11. All screen shoots must be clearly legible.

• The report’s 1st page should contain the report title, the authors (name, surname & student ID,
of all students contributing). This is not counted in the word count.

• The 2nd page should have content list (including list of figures). This is not counted in the word count.

• On the 3rd page please include a table showing each authors breakdown of effort (as
percentage) and list the nature of their participation (e.g. what each author has contributed
to the coursework considering each). This is not counted in the word count.
An example of the effort table is shown below:

Name and Surname A. B. C. D. E. Signature
Mary Rossi 50% 30% 25% 70% 0% Mary Rossi
John Smith 25% 30% 25% 30% 50% JohnSmith
Alan Wang 25% 40% 50% 0% 50% Wang A

In addition, each student must write an individual report (see PART B. below).

A, B, C, D,E (the word count starts now):

A. (10 marks) Customer brief – this is an essential part of both the project development and
the contract with the client as it helps guarantee that the work for a project will be done
according to specific guidelines and expectations of the client. If you cite any source to
write this section, please provide it in the reference list utilizing APA style.
Your brief should include:
o An Overview of what the system is expected to do in order to produce the data
required by the mySQL queries of PART B (a short paragraph of max 500
words)
o A Concept Table (who, when, where, why, how)
o A Conceptual Model of the whole payroll system and its attributes (e.g. main
tables and columns needed in the database) displayed visually utilizing two
different techniques:
▪ Axis Technique
▪ Conceptual ER model
o A Dictionary of all the key-works utilised by the system and their meanings (e.g.
hourly pay is the amount an employee gets paid per hour).

B. (25 marks) Normalization - show with tables, and explain in writing (step by step), how the
Conceptual ER (CER) model you have produced is being normalised. In particular in the
report you need to present:
o The unnormalized model (this is the CER model, or one long list of attributes, where
you have ensured it has all the columns shown in the conceptual model, but it has no
keys)
o Logical ER model (1NF) and explain how from CER you moved to 1NF
o Logical ER model (2NF) and explain how from 1NF you moved to 2NF
o Logical ER model (3NF) and explain how from 2NF you moved to 3NF

C. (2 marks) The database - Build the database in XAMMP (in the report you should provide
both the screenshots of the database creation and the code - see note 2 below)
o (1 mark) Create the database
o (1 mark) Create all the tables, with columns and keys
o
D. (10 marks)The company has asked as consultants you to investigate newer technologies in
Databases including but not limited to NoSQL Databases, Object Oriented Databases etc. and
suggest why they may or may not be beneficial for this system. You need to pick one
technology/type describe it and assess its suitability for this business scenario. Your outcomes
should not exceed 500 words
4


E. (3 marks) Composition of report
o Include a table of content and figures
o All screen-shot and graphics must be readable, of good image quality and appropriate for the
size of the printed page
o Ensure spelling and grammar is correct throughout the report

5



PART B – Individual Work [ 50 marks ]

Individually you must to produce a Written Report (1000 words max) the minimum font size to be
used in the report is ARIAL point 11. On the 2nd page your report should have content list
(including list of figures). This is not counted in the word count. All screen shoots must be clearly
legible.

Your report should address the following tasks:

A. (40 marks) The queries –

Code the following queries in XAMMPS. In the report provide:
i. the code as text in the report,
ii. the screen-shots of both the query’ code and
iii. its results/print out – (see note 2 below)

QUERIES:
• Produce a list of employees that have worked more than 35 hours in the current week
(note the DB should still have some that have worked less than 25 hours)
• Produce a list of employees that live in specific UK city that has a particular job
designation (note there should be still employees in the DB that live at a different city
and have a different job title)
• List the employees of the company that on average have been paid the most in the last
4 weeks. (Note the DB should contain more than 4 weeks data).
• Produce a payslip for the past 4 weeks for one of the employees that has done
overtime in at least one week (see template provided, see figure 1.
The payslip number to be constructed as a unique key using date and job and grade.




B. (10 marks) Composition of report & Individual reflection
o Include a table of content and figures
o All screen-shot and graphics must be readable, of good image quality and
appropriate for the size of the printed page
o Ensure spelling and grammar is correct throughout the report

o The individual reflection (suggested 400 words max) should highlight
i) what was your contribution to the group project, and
ii) How the collaboration was conducted.


6

Submission Notes:

NOTE 1: We MUST provide the following information from XAMMP as a proof that the database has
been built. This information I. and II. should be included as an Appendix to the group report (Part A).
Information III. and IV. should be included in the solution of Part B Tak A. The information that MUST
be included is the following:

I. Data dictionary – saved as .pdf file and include it in the report appendix.
This information it can be accessed from the main page of XAMMPS. After the
database is built, a link will appear called Data dictionary, which needs to be
clicked.
II. The Designer – save the screen-shot as a .pdf file or add it as picture within the report.
The Designer can be found under the top navigation bar (or under the More tab
in some versions).
III. The SQL code – save the screen-shot as a .pdf file or add it as picture within the report.
This is the code produced in XAMMP, which can be found under the SQL tab in the
top navigation bar. Simple code is automatically generated when using the interfaces.
However, we required that you show the code that has been created by yourself, such
as for example the code to generate the invoice. Please remember to open up the
SQL windows to type the code and enlarge the window in a manner that includes the
whole code clearly before screen shoot it. For example, any table containing a
composite key should be created with code, and the code screen-shot.
IV. A screen shot of the output created by the code - save the screen-shot as a .pdf file or
add it as picture within the report.
This will be generated when running the code pressing the button ‘Go’ at the bottom
right of the SQL tab screen. The code run is shown at the top pf the screen and
should also be included.

NOTE 2: All the images used on the report should be of sufficient quality for the text depicted to be
read clearly in the printed report. Utilise the same style throughout, label the figures and provide a
brief explanation of what the picture shows.

FURTHER NOTES:
v A penalty reduction by 10 marks will be applied for every additional 500 words used.
Penalties are applied for late submissions (refer to UCL guidelines at https://www.ucl.ac.uk/academic-
manual/chapters/chapter-4-assessment-framework-taught-programmes/section-3-module-
assessment#3.12). v The Marking Scheme provided in moodle gives further details on how your work will be
marked and can be used to guide your work. Please study the marking scheme carefully and use it as a checklist
before submitting the work.
v Students that do not work in pairs as directed will be penalised unless a special exemption has been granted.
v You may consult the lecturer or ask for further advice about your coursework by posting a query on the Moodle