Assignment Title Coursework
Component/Module INST0001 Database systems
Assignment Code AS01-INST0001
Set by D. Romano
Moderated by L. Dickens
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 student must also demonstrate the 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 40% of
the final mark.
Conditions Assignment Submission must be completed through a designated Moodle Area:
https://moodle.ucl.ac.uk/mod/turnitintooltwo/view.php?id=1633863
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 including his/her
individual reflection, the other student will submit only his/her individual refection
Marking Criteria
and Weighting
Rubric
These are described in Appendix A below.
The assignment is worth 40 % of the overall assessment for this course
This assignment must be completed: Report (in pairs)
Date work set (provisional): 07/02/2020
Date and time due in (provisional): 26th March 2020. Extended now due on 9th of April 2020
Standard lateness penalty will apply.
The target date for the return of marked work and full feedback
(provisional):
Marked work is returned on: 24/04/2019
Extended 16th of May 2020
(tentatively within 4 weeks, according to DIS policy)
A detailed description of the assignment is
available at:
https://moodle-1819.ucl.ac.uk/course/view.php?id=5253
INST0001 Coursework Assignment Brief
UCL DEPARTMENT OF
INFORMATION STUDIES
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.
In pairs you must to produce a Written Report (1500 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 both
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% 50% 50% 70% 50% Mary Rossi
John Smith 50% 50% 50% 30% 50% JohnSmith
In addition, each student must write an individual report (see point E. below).
A, B, C, D, E, elements of the Group Report are as follows (the word count starts now):
A. (10% of mark) 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 payroll system is expected to do in order to produce the data
required by the mySQL queries below (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% of mark) 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
3
C. (2% of mark) 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%) Create the database
o (1%) Create all the tables, with columns and keys
D. (60% of mark) 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 its iii) results/print out – see note
2 below):
o 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 hors)
o 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)
o 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).
o 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.
Figure 1. Sample payslip, note payslip number is not shown as we want you to work it out.
E. (3% of mark) 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 300 – not in the word count) should highlight
i) what was your contribution to the group project, and
ii) How the collaboration was conducted.
Only one student will submit the coursework for the pair including his/her individual reflection, the other
student will submit only his/her individual refection
NOTE: 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 report. Information III. and IV. should
be included in the solution of D. 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.
4
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 in Appendix A provides 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
5
INST001- Database Systems
APPENDIX A – MARKING SCHEME
REPORT Criteria Inadequate
(up to 39)
Poor/Missing
(40-49)
Adequate
(50-59)
Good
(60-69)
Very Good
(70-79)
Excellent
(80+)
COMPOSITION OF
REPORT:
Is the report readable,
well-structured with no
grammatical errors?
There is little or no
evidence of thought
about structure, and
the document's
readability and
grammar are severely
impaired
There is a structure;
however, the document
is still hard to read
and/or has severe
grammatical errors
There is some evidence
of thought towards
structure, but poor
choices impair
readability and
grammar
The report is
structured, perhaps
using sub-headings or
paragraphs to direct
the reader through the
content, has good use
of language, and figures
are readable
The report is well-
structured, probably
using sub-headings or
paragraphs to lead the
reader through the
material, has good use
of language, and the
figures are legible. The
content is well
explained
Excellent writing and
style, topics excellently
explained visualised
and structured
COMPLETENESS and
CORRECTNESS OF EACH
ELEMENT:
Does the report
contain all the items
required in the
coursework
description? Have
these items been well
executed?
(e.g. A,B,C,D, E)
Most items are
missing or are not well
executed.
The majority of items
requested are missing or
incomplete, or they have
a poor execution
The majority or all
items are present;
however, they might
not be well executed or
described
All items requested are
present and well
executed and described
All items are present,
very well executed,
very well described
The report goes well
above the task and
guidelines provided
PAIRS WORK:
Does the report explain
how the work was
apportioned?
(e.g. collaboration
table)
The report does not describe how the work was
allocated
The report
explains adequately
how the work was
distributed, however,
the collaboration
declaration is missing
or both members of the
pair have not signed
the table
The report describes how work was apportioned.
The collaboration declaration is present and
signed by all group members
The student has
explained
how work was
distributed. The
student has evaluated
the way in which work
was apportioned.