Microsoft Word - DTS L5 Databases and SQL Assessment Brief
BPP Coursework Cover Sheet
Please use the table below as your cover sheet for the 1st page of the submission. The sheet should
e before the cove
title page of your submission.
Programme BSc (Hons) Digital and Technology Solutions
Module name Databases and SQL
Schedule Term
Student Reference Number (SRN)
Report/Assignment Title
Date of Submission
(Please attach the confirmation of any
extension received)
Declaration of Original Work:
I hereby declare that I have read and understood BPP’s regulations on plagiarism and that this is my
original work, researched, undertaken, completed and submitted in accordance with the requirements
of BPP School of Technology.
The word count, excluding a table of contents, bibliography and appendices, is ___ words.
Student Reference Number: Date:
By submitting this coursework you agree to all rules and regulations of BPP regarding assessments
and awards for programmes. Please note, submission is your declaration you are fit to sit.
BPP University reserves the right to use all submitted work for educational purposes and may
equest that work be published for a wider audience.
BPP School of Technology
BSc (Hons) Digital and Technology
Solutions
Databases and SQL
Coursework Assessment Brief
Submission deadline: Deadlines can be found on the module space
on the Hub
Submission mode: Turnitin online access
1. Assessment Brief
This assessment
ief gives you an overview of the formative and summative assessments that are
part of this module. The learning outcomes below will be tested in the assessment contained in this
ief.
1.1. Module Learning Outcomes (LOs)
1. Appraise the importance of databases within a work-based context
2. Differentiate the value of various databases for a given problem
3. Demonstrate the value of analytics (or queries) leveraging data within a work-based context
1.2. Assessment Overview
In this assessment, you will produce a portfolio to demonstrate your understanding of databases
and ability with SQL. Your portfolio will consist of two parts, where part 1 of your portfolio will be a
SQL script created within the context of a specified work-based scenario, demonstrating your ability
to define relational database schema and perform analytics/queries (1000 words equivalent, 40% of
module mark, Covering LO 3). Part 2 of your portfolio will be a written report about the use of
databases and their importance, written within the context of your organisation (1500 words, 60%
of module mark, Covering LOs 1 and 2).
Formative Submission deadline: Written assessment and SQL script to be submitted by the date
stated on the module page in to the Turnitin Dropbox available on the Module page in the Hub.
Summative Submission deadline: Written assessment and SQL script to be submitted by the date
stated on the module page in to the Turnitin Dropbox available on the Module page in the Hub.
Please note: ensure you read the general assessment guidance at the end of this document.
2. Assessment Guidance – Part 1
In this part of your assessment, envisage that you have been seconded as a database consultant to a
local takeaway business, Food2Go, who are struggling to meet their data requirements. You have
een set two aims to meet:
Introduce new tables into an existing SQL database schema to store allergen data about
their menu items’ ingredients and customers’ allergies
Write queries to perform a range of analytics on their existing database of orders and
customers to meet their business needs
The SQL you write to achieve these objectives should be clear, with appropriate use of comments
and following existing naming and style conventions throughout.
2.1. Summative Assessment
Food2Go is a local food takeaway business that has recently been informed by the Food Standards
Agency (FSA) that their existing practices around notifying customers about potential allergens on
their online ordering system does not meet their best practices and should be improved. You have
een seconded from your cu
ent role to help Food2Go achieve this by extending their existing SQL
database schema to meet these requirements. Food2Go have also requested your assistance with
improving their business intelligence and compliance capabilities and have come to you with a list of
specific requirements they require you to write database queries to achieve.
Allergen Information
Allergens are ingredients in food that certain people are likely to be sensitive to. Per the FSA (2021)
guidance, there are 14 allergens that food businesses should inform their customers about, although
that list may be subject to change (that is, new allergens may be added or existing allergens might be
emoved from this list). Food2Go have provided you with a sample of their existing database, and
equire you to introduce new tables to their existing schema to achieve two key outcomes:
A. Store information about any existing customers’ allergens, so they can be easily checked
when placing an order
B. Allow their website to convey information to customers when they purchase food about the
presence of potential allergens, including whether a dish can be modified to remove the
allergen
The first section of your SQL script should achieve the above requirements using queries to create
tables, columns, and keys. The above requirements can be achieved fully without modifying any of
the existing tables’ schema, and should be done following best SQL practices, such as avoiding the
unnecessary duplication of data.
To demonstrate how your solution will work, the second section of your SQL script should include
statements to insert sample data into all the tables you created, to demonstrate how they would be
used. You have been provided with the following sample data about Food2Go’s products and clients
to help you do this:
The mozzarella sticks and margherita pizza both contain cheese made from milk
The mozzarella sticks, garlic
ead, margherita pizza, fried chicken, and onion rings are
normally made with
ead flour that contains gluten, but the pizza and garlic
ead can be
made with gluten-free flour instead
The fried chicken is made using eggs
The coconut cu
y contains both sesame seeds and cashew nuts (a tree nut), but these can
oth be left out of the dish if required
Ivor Woodard is known to have an allergy to crustaceans
Rhonda Slater always orders gluten-free bases and also checks whether their food contains
tree nuts
Business Intelligence & Compliance
To help further improve Food2Go’s business and ability to meet regulatory standards, you have been
asked to aid them by creating SQL queries to extract data from their database to meet specific
usiness needs. These queries will only need to operate on the data stored within the original
sample database and do not require querying any data stored in the tables you created in the
previous tasks. Food2Go have come to you with 6 specific questions that they need you to write
queries to answer in the third section of your SQL script:
1. When calling a certain customer to update them about an order, it was found that the
contact number they provided was inco
ect and it was instead an 0800 number for a
national charity. Find all customer’s details whose contact numbers start with either “0800”
or “0845”.
2. Given the rules imposed by the Data Protection Act (DPA XXXXXXXXXXICO, n.d.) and the need to
ensure all data stored about a customer is accurate, it has been proposed that each
customer should be contacted to validate the accuracy of all the data stored about them.
Write a query that returns all customer’s names, their associated addresses (if applicable),
and contact number, sorting the results in ascending order by the customer’s last and then
first name.
Note: Repeating names and contact numbers for customers who have multiple
addresses in the system is acceptable, as it can be resolved by downstream
processing.
3. Another relevant DPA risk is unnecessarily storing data beyond when it is required. Hence it
has been proposed that addresses that have not been used for an order and are not
cu
ently set as a default delivery address should be removed from the database. Find all the
addresses that have never been used for an order and are not the default address for a
customer (indicated by the “is_default” field on the Address table).
4. Some products appear to be more popular than others, and it would be useful to understand
how frequently each occurs in orders, and the volume of product that is normally ordered –
including identifying those that are never ordered – so future purchases can be more data-
driven. For each product, count how many different orders they appeared in as well as the
average (mean) amount of the product that was ordered each time, and sort the results by
the count of orders (with the largest at the top).
5. Given the challenges of the year 2021, it has been proposed that the top three customers
(by total spending over that period) should be rewarded with a gift card. Identify these three
customer’s IDs, names, and their total spend.
6. Different days of the week (e.g., Monday-Sunday) appear to have different distributions of
orders from customers. One theory has been proposed that, on most weekdays, there are
normally larger orders in the early evening (e.g., between 17:00 and 19:00) than in the early
morning (e.g., between 00:00 and 02:00), whilst on certain other weekdays, the opposite is
true. Calculate the average (mean) amount of product that was ordered on each weekday
etween those two time windows to produce evidence that could help validate or disprove
this hypothesis.
For each of these questions, you should include a single query in your SQL script to return the
desired results. Columns in the results should be clearly named and you should aim to follow the
naming conventions of the database you have been provided with.
References
Food Standards Agency, 2021. Food allergy and intolerance [online]. Available from:
https:
www.food.gov.uk/safety-hygiene/food-allergy-and-intolerance [Accessed 4th May 2022].
ICO, n.d. Guide to the UK General Data Protection Regulation (UK GDPR) [online]. Available from:
https:
ico.org.uk/for-organisations/guide-to-data-protection/guide-to-the-general-data-protection-
egulation-gdp
[Accessed 4th May 2022].
2.2. Formative Assessment
You should submit a draft SQL script that demonstrates your database table design for outcome A of
Allergen