Solent University
Coursework Assessment Brief
Assessment Details
Module Title:
Module Code:
Module Leader:
Level:
Assessment Title:
Assessment Number:
Assessment Type:
Restrictions on Time/Word Count:

Consequence
of
not
time/word count limit:

Databases
COM711
Kenton Wheeler
7
Databases
1
Report containing code
Max. 2000 words for your report. Any SQL, Python code,
diagrams and explanatory text for parts 1, 2 and 3 are
not included in this word count.

meeting There is no penalty for submitting below the
word/count limit, but students should be aware that
there is a risk they may not maximise their potential
mark.
Assignments should be presented appropriately in line
with the restrictions stated above; if an assignment
exceeds the time/word count this will be taken in
account in the marks given using the assessment criteria
shown.

Individual/Group:
Assessment Weighting:
Issue Date:
Hand In Date:
Planned Feedback Date:
Mode of Submission:
Number of copies to be submitted:
Anonymous Marking

Individual
100%
30th October 2020
22nd January 2021
19th February 2021
On-line via SOL
N/A
This assessment will be marked anonymously

Assessment Task
1. Introduction
This assessment requires you to undertake practical database application development work
to meet specified requirements and write a reflective report that evaluates the quality of your
work and researches a topic relating to databases.
This assessment will enable students to demonstrate in full or in part the learning outcomes identified
in the unit descriptor.

1
Policy, Governance and Information
July 2020

2. The Practical Development Work
The practical development work is based on an online electronics shopping company where
you work as a Database Analyst/Developer. The entity-relationship diagram and SQL script
for creating and populating the database are provided on SOL. You can find these and other
resources required to complete the assessment on the Assessment tab.
Part 1 - Retrieving Data using SQL
You have been asked to write the following SQL queries for management information
purposes. All students should complete questions a, b and c below and to achieve a higher
grade, also complete question d.
a) The company want to do a marketing campaign to new shoppers and all female shoppers.
Retrieve the first name, surname, email address, gender, date joined, and the current age in
years of shoppers who joined on or after 1st Jan 2020 and all female shoppers (irrespective
of when they joined). Print date columns in the format DD-MM-YYYY and print ‘Not known’
for any NULL values. Order results by gender and then by age (highest first).
Refer to the SQLite Built-in Functions reference on SOL for how to calculate the age and
format the dates.
b) The website requires a customer account history page which will accept the shopper id as a
parameter entered by the user at run time. Write a query to retrieve the first name and
surname for a specific shopper along with details of all the orders they’ve placed, displaying
the order id, order date, product description, seller name, quantity ordered, price (with two
decimal places and prefixed by a £ sign) and ordered product status. Print date columns in
the format DD-MM-YYYY. Sort the results by order date showing the most recent order first.
Test your query for shopper ids 10000 and 10019.
c) The business relationship manager has asked you to write a summary report on the sellers
and products that they have had sold since 1st June 2019. Display the seller account ref,
seller name, product code, product description, number of orders, total quantity sold and
total value of all sales (with two decimal places and prefixed by a £ sign) for each product
they sell. You should also include products that a seller sells but has had no orders for and
show any NULL values as 0. Sort results by seller name and then product description.
d) The head of sales wants a report showing the products that have an average quantity sold
that is less than the average quantity sold for the category that the product is in. Cancelled
orders should be excluded from the calculations. Any products that haven’t sold at all should
also be displayed with an average quantity of 0. Display the category description, product
code, product description, average quantity sold for the product and average quantity sold
for the category its in. Both averages should be displayed to an accuracy of 2 decimal places
and results should be shown in category description, then product description order.

For each query, include the SQL code you have written (in a format that can be copied and
pasted i.e. not as a screenshot) along with a brief explanation of the SQL. Supply
screenshots of the query results and user input (if any) and provide proof that the results
are correct by doing thorough testing. Remember to display meaningful and user-friendly
column headings on all queries.

2
Policy, Governance and Information
July 2020

Part 2 – Database Design, Implementation and Integrity
The online electronics shopping database needs to be extended to store the data required to
implement shopper reviews about sellers and products and questions and answers about
products.
Seller reviews are just about the seller not about the product they sold and product reviews
are about the product and not the seller that sold it. Each review must be star-rated as *
(Poor), ** (Fair), *** (Good), **** (Very Good) and ***** (Excellent) and hold a brief textual
comment from the shopper. The date and time that the feedback was submitted should also
be stored.
Questions about products are anonymously asked by shoppers and can be answered by other
shoppers or by sellers. A question can be answered many times. The date and time that the
question is asked and any answers posted also need to be stored.
All students should complete questions a, b and c below and to achieve a higher grade, also
complete question d.
a. Produce a table design to support this additional functionality explaining the process you
used to arrive at your design, how you ensured the database integrity would be maintained
and any design assumptions that you have made. Your design should consist of at least two
new tables and you must link to at least one of the existing tables.
b. Modify the provided Entity Relationship diagram to show your new tables, their primary and
foreign keys and how they relate to each other and to the existing tables.
c. Implement your design by creating the new tables, insert enough rows into your new tables
to facilitate testing and prove that your integrity constraints work correctly through testing.
Include the SQL that you used to create, populate and test the new tables in your
submission.
d. Create a view that joins your newly created tables together with existing tables and provide
at least two SQL queries that select from this view.

Part 3 – Programming for Databases
Develop Python code to implement some basic text-based functionality to allow the user to
interact with the online electronics shopping database as outlined below. All students should
complete questions a, b and c below and to achieve a higher grade, also complete question
d.
a. Firstly, prompt for the entry of a shopper_id which will be used to test all the menu options.
If the shopper_id entered is found, print a welcome message including the name of the
shopper. If the shopper_id is not found in the database, print an error message and exit the
program otherwise print the main menu below.
b. Provide a text-based menu as follows:
ORINOCO – SHOPPER MAIN MENU
3
Policy, Governance and Information
July 2020

1.
2.
3.
4.
5.

Display your order history
Add an item to your basket
View your basket
Checkout
Exit

c. Implement menu options 1, 2, 3 and 5 as follows:
Option 1 – Display your order history
i.

iv.

For each order that the customer has placed, display the order id and order date
together with the product description, seller name, price, quantity ordered and
status of each product on that order. You can use your query from Part 1b as a basis
for the SQL query in your Python code.
Sort orders by order date (most recent first)
If no orders are found for the shopper_id that you are testing with, print the
message “No orders placed by this customer”
Display the data in the format shown below (which is for shopper_id 10010)

v.

Return to the main menu

ii.
iii.

Option 2 – Add an item to your basket
i.
ii.
iii.
iv.
v.
vi.
vii.
viii.
ix.
x.

xi.
xii.
xiii.

Display a numbered list of product categories
Prompt the user to enter the number of the product category they want to choose
from and store the category_id for the selected category
Display a numbered list of the available products in the category selected
Prompt the user to enter the number of the product they want to purchase and
store the product_id for the selected product
Display a numbered list of sellers who sell the product they have selected and the
price they are selling that product at
Prompt the user to enter the seller they wish to buy the product from and store the
seller_id for the selected seller
Prompt the user to enter the quantity of the selected product they want to order
Get the price of the selected product from the selected supplier
If the basket is empty, get the next basket id by selecting from the sqlite_sequence
table and insert a new row into the shopper_basket table using the next basket _id.
Insert a new row into the basket_contents table for the product they’ve chosen to
purchase using the basket id selected in stage ix. Please note: All items added to the
basket should have the same basket_id in the basket_contents table.
Commit the transaction
Print “Item added to your basket”
Return to the main menu
4
Policy, Governance and Information
July 2020

Below is an example of what should be displayed and what should be prompted for:

To simplify your code, the following function must be included at the top of your
program to display a numbered list of options and return the id of the selected option.
You can amend this function accordingly if you wish but a function must be included.
def _display_options(all_options,title,type):
option_num = 1
option_list = []
print("\n",title,"\n")
for option in all_options:
code = option[0]
desc = option[1]
print("{0}.\t{1}".format(option_num, desc))
option_num = option_num + 1
option_list.append(code)
selected_option = 0
while selected_option > len(option_list) or selected_option == 0:
prompt = "Enter the number against the "+type+" you want to choose: "
selected_option = int(input(prompt))
return option_list[selected_option - 1]

This function should be called in steps i, iii and v above using a command of the
following format:
id_of_selected_option = _display_options(query_rows,title,type)

query_rows must consist of two values - id and description i.e. the category_id and
category_description
5
Policy, Governance and Information
July 2020

title is some text to put above the list of options to act as a title
type is used to customise the prompt to make it appropriate for what you want the
user to select
Option 3 – Display your basket
i.

If the basket is empty, display an error message otherwise display all rows from the
basket_contents table for the current basket and a total basket cost as per the
example below:

ii.

Return to the main menu

Option 5 – Exit
i.

Exit the program

d. Option 4 – Checkout your basket
i.
ii.

iii.

iv.
v.
vi.
vii.

If the basket is empty, display an error message otherwise display the current basket
and the basket total (the same as option 3)
If the shopper has more than one delivery address stored for them, display a
numbered list of the delivery addresses (most recently used first) and prompt the
shopper to choose the address they want to deliver the items to. If the shopper has
one delivery address, display and use this address for the checkout. If they have no
delivery addresses, prompt the shopper to enter a new delivery address, use this
address for the checkout process and insert a new row into the
shopper_delivery_addresses table.
If the shopper has more than one payment card stored for them, display a
numbered list of the payment cards (most recently used first) and prompt the
shopper to choose the card they want to pay with. If the shopper only has one
payment card, display the card number and use it for the checkout. If they have no
payment card stored for them, prompt the shopper to enter new card details, use
this card for the checkout process and insert a new row into the
shopper_payment_cards table.
Insert a new row into the shopper_order table for the basket with a status of
‘Placed’
Insert a new row into the ordered_product table for each item in the basket with a
status of ‘Placed’
Delete the rows from the shopper_basket and basket_contents tables for this basket
Return to the main menu

Below is an example of what should be displayed for shopper_id 10000 who has more than
one delivery address and only one payment card stored on the system:
6
Policy, Governance and Information
July 2020

Below is an example of what should be displayed for shopper_id 10005 with more than one
delivery address and more than one payment card stored on the system:

Below is an example of what should be displayed for shopper_id 10023 with no delivery
addresses or payment cards stored on the system:

You should add comments throughout your code to make it easier for someone else to
understand.

7
Policy, Governance and Information
July 2020

With your submission, you must include all your Python code, screenshots of the output
and any user interaction together with evidence that the requirements outlined under each
menu option have been met and thoroughly tested by including screenshots of data
successfully inserted, updated and deleted from the database and errors/exceptions being
correctly handled.

3. Evaluation and Research Report (guideline - approx 2000 words)
All students must include a report in their submission which should cover the following:
a) An evaluation of the quality of the work you have produced with reference to both the
specified requirements and the assessment criteria. You should include:





any deliverables that are not complete or fully tested;
elements of the practical work that you feel contribute to higher grade achievement;
your approach to design and testing of the SQL and Python elements of the assessment;
how well you feel you managed your time between the four parts of the assessment and
what more you would have done if you’d had more time.

b) Discuss ‘Why relational databases continue to be important for most enterprises despite
the rise in popularity of NoSQL databases?’. Back up your arguments with research.
c) Bibliography and references
4. Submitting Your Work
The online submission (which should be well structured and clearly written) must be in a
single document in MS Word or PDF format and should include:
1.
2.
3.
4.

A title page
Contents page and page numbers.
Copies of all your final (versions of the deliverables for parts 1, 2 and 3.
Your report (see section 3 above)

8
Policy, Governance and Information
July 2020

Assessment criteria
The summary grid below is the basis for grading achievement. Higher levels of achievement are described
towards the right-hand side of the grid. Each level subsumes the previous level. An indication of the %
contribution of each part and the report is indicated. Normally, an attempt must be made for each block and
the report.

S, F3-F1
D3-D1
C3->C1
B3->B1
Part 1 - Retrieving Data using SQL – Learning Outcomes K1, C1, P1 – 30%
Does not
At least two
Has submitted
Has submitted
reach
queries were
queries a, b and c
queries a, b and c
required
submitted that
and they all produce and they all produce
threshold.
produce the
the correct or mostly the correct results
correct or mostly
correct results.
correct results.

A4->A1
Has submitted
queries a, b,c and d
and they all produce
the correct results.

The queries meet
the basic
requirements but
no attempt has
been made to
improve the
presentation.

The queries meet
the basic
requirements and
some attempt has
been made to
improve the
presentation.

The queries meet
the majority of the
requirements and
the output is well
presented.

The queries meet or
exceed all the
requirements and the
output is
professionally
presented.

No evidence of
testing has been
provided to ensure
the results for each
query are correct.

Evidence of testing
has been provided
for some of the
queries.

Some evidence of
testing has been
provided to ensure
the results for each
query are correct.

Evidence of through
testing has been
provided to ensure
the results for each
query are correct.

No explanation has
been provided for
each query.

Some explanation
has been provided
for each query
demonstrating a
basic understanding
of the concepts.

Some explanation
has been provided
for each query
demonstrating a
good understanding
of the concepts.

A detailed
explanation has been
provided for each
query demonstrating
a full understanding
of the concepts.

Part 2 – Database Design Implementation and Integrity - Learning Outcomes K1, C1, P1 – 25%
Does not
The design has
The design has some The design is correct, The design is correct
reach
some flaws and
flaws but mostly
and meets the
and meets or exceeds
required
only partially meet meets the
requirements.
the requirements
threshold.
the requirements.
requirements
The design has a
minimum of two
tables and links to
at least one
existing tables.

The design has a
minimum of two
tables and links to at
least one existing
tables.

The design has a
minimum of three
tables and links to at
least two existing
tables.

The design has a
minimum of three
tables and links to at
least two existing
tables.

The rationale
behind the design
has not been
provided and any
design
assumptions have
not been outlined.

A basic rationale
behind the design
has been provided
but no design
assumptions have
been outlined.

The rationale behind
the design has been
explained and any
design assumptions
have been outlined.

The rationale behind
the design has been
fully explained and
any design
assumptions have
been outlined.

9
Policy, Governance and Information
July 2020

The newly-created
tables have not
been correctly
implemented or
the primary and
foreign keys are
missing or
incorrect.

The newly-created
tables have been
correctly
implemented with
the correct primary
and foreign keys. No
other constraints
have been used

The newly-created
tables have been
correctly
implemented with
the right primary
and foreign keys.
Some other
constraints have
been used.

The newly-created
tables have been
correctly
implemented with
the right primary and
foreign keys. Other
constraints have been
widely used and the
constraints have been
named.

Each new table has
not been
populated with
sufficient test data.

Each new table has
been populated with
basic test data.

Each new table has
been populated with
a reasonable amount
of test data.

Each new table has
been populated with
a good amount of test
data.

No evidence has
been provided that
the new
constraints have
been tested to
ensure they work
correctly.

Evidence has been
provided that some
of the new
constraints have
been tested to
ensure they work
correctly.

Evidence has been
provided that some
of the new
constraints have
been tested to
ensure they work
correctly.

Full evidence has
been provided that all
new constraints have
been tested to ensure
they work correctly.

No amended ERD
has been provided
or the one
provided is
incorrect.

An amended ERD
diagram has been
provided but it is
incorrect.

A correctly amended
ERD diagram has
been provided.

A correctly amended
ERD diagram has
been provided.

No view has been
created or used.

No view has been
created or used.

If a view was
created, it was not
fully tested with at
least two correct
queries.

A view has been
created based on the
new and existing
tables and at least
two queries correctly
written using the
view.

Menu options 1,2
and 3 have been
fully implemented,
work correctly and
mostly meet the
requirements

All menu options
have been fully
implemented, they all
work correctly and
fully meet the
requirements.

Part 3 - Programming Learning Outcomes K1 and T1 – 25%
Does not
Menu option 1 has Menu option 1 has
reach
been attempted
been fully
required
but does not work
implemented, it
threshold.
correctly or does
works correctly and
not meet the
menu options 2 and
requirements.
3 are partially
implemented or do
not function
correctly
The user interface
is basic or does not
function correctly.

The user interface is
basic but functional.

The user interface is
good with clear
prompts and helpful
error messages.

The user interface is
good with clear
prompts and helpful
error messages.

The code retrieves
data from the
database

The code retrieves
data from the
database

The code retrieves
data from the
database and inserts

The code retrieves
data from the
database and inserts,

10
Policy, Governance and Information
July 2020

Report = 20%
Little or no
evaluation of
the quality
of the work
you have
produced,
your
approach to
design and
testing or
time
management
aspects
Very poor
presentation
and
structure,
incomplete,
doesn’t
satisfactorily
address the
research
topic.

rows in the
database.

updates and deletes
rows in the database.

The program has
no functions.

The program uses
the supplied
function without
modification.

The program uses
the supplied
function or a
modified version of
it.

The program makes
extensive use of
functions to
modularise the code.

There are some
major issues with
how the code has
been written.

There are some
minor issues with
how the code has
been written.

The code is correctly
structured and wellwritten.

The code is correctly
structured and wellwritten.

There is little or no
evidence that the
code has been
tested

There is some
evidence that the
code has been
tested

There is evidence
that the code has
been tested

There is evidence that
the code has been
thoroughly tested

Comments are not
used in the code

Comments are
sparsely used in the
code

Clear comments are
used throughout the
code.

Clear comments are
used throughout the
code.

There is no
evidence of error
handling.

There is some
evidence of error
handling.

There is some
evidence of error
handling.

Extensive error
handling has been
used.

A basic description
of the approach
you used to
produce your
deliverables and
evaluation of the
quality of the work
you have
produced.

Some reflection on
the approach you
used to produce
your deliverables,
the quality of the
work you have
produced and how
well you managed
your time.

An honest selfassessment of the
approach you used
to produce the
deliverables, the
quality of the work
you have produced
stating what you did
well and not so well
and how well you
managed your time.

An honest, reflective
and insightful
evaluation of the
approach you used to
produce the
deliverables, the
quality of the work
you have produced
stating what you did
well and not so well
and how well you
managed your time.

Reasonable
presentation and
structure. Not fully
accurate, and not
consistently
relevant to the
research topic.
Partially complete.

Clear structure,
largely accurate
information
generally
appropriate to the
topic but no
evidence of research
is included to
substantiate it.

Thorough
presentation of
information and
clear structure.
Evidence of research
to back up the
arguments
presented.

Excellent and critical
presentation of
information in a wellstructured way.
Information complete
and evidence of
thorough research to
back up the
arguments presented.

11
Policy, Governance and Information
July 2020

Learning Outcomes
This assessment will enable students to demonstrate in full or in part the learning outcomes
identified in the Module descriptors.
Late Submissions
Students are reminded that:
i.
ii.
iii.

If this assessment is submitted late i.e. within 5 working days of the submission
deadline, the mark will be capped at 40% if a pass mark is achieved;
If this assessment is submitted later than 5 working days after the submission deadline,
the work will be regarded as a non-submission and will be awarded a zero;
If this assessment is being submitted as a referred piece of work then it must be
submitted by the deadline date; any Refer assessment submitted late will be regarded
as a non-submission and will be awarded a zero.

https://students.solent.ac.uk/official-documents/quality-management/academichandbook/2o-assessment-principles-regulations-temporary-amendments-for-covid-19contingency-plans.pdf
Extenuating Circumstances
The University’s Extenuating Circumstances procedure is in place if there are genuine
circumstances that may prevent a student submitting an assessment. If students are not 'fit
to study’, they can either request an extension to the submission deadline of 5 working days
or they can request to submit the assessment at the next opportunity (Defer). In both
instances students must submit an EC application with relevant evidence. If accepted by
the EC Panel there will be no academic penalty for late submission or non-submission
dependent on what is requested. Students are reminded that EC covers only short term
issues (20 working days) and that if they experience longer term matters that impact on
learning then they must contact the Student Hub for advice.
Please find a link to the EC policy below:
https://students.solent.ac.uk/official-documents/quality-management/academichandbook/2p-extenuating-circumstances.pdf
Academic Misconduct
Any submission must be students’ own work and, where facts or ideas have been used from
other sources, these sources must be appropriately referenced. The University’s Academic
Handbook includes the definitions of all practices that will be deemed to constitute
academic misconduct. Students should check this link before submitting their work.
Procedures relating to student academic misconduct are given below:
https://students.solent.ac.uk/official-documents/quality-management/academichandbook/4l-student-academic-misconduct-procedure.pdf
Ethics Policy
The work being carried out by students must be in compliance with the Ethics Policy. Where
there is an ethical issue, as specified within the Ethics Policy, then students will need an
ethics release or an ethical approval prior to the start of the project.
12
Policy, Governance and Information
July 2020

The Ethics Policy is contained within Section 2S of the Academic Handbook:
https://staff.solent.ac.uk/official-documents/quality-management/academichandbook/2s-solent-university-ethics-policy.pdf
Grade marking
The University uses a letter grade scale for the marking of assessments. Unless students
have been specifically informed otherwise their marked assignment will be awarded a letter
grade. More detailed information on grade marking and the grade scale can be found on the
portal and in the Student Handbook.
https://students.solent.ac.uk/official-documents/quality-management/academichandbook/2o-annex-3-assessment-regulations-grade-marking-scale.docx
Guidance for online submission through Solent Online Learning (SOL)
http://learn.solent.ac.uk/onlinesubmission

13
Policy, Governance and Information
July 2020