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 be before the...

Database and SQL assignment, Part 1 requiring addition of new tables to an existing schema and creating simple SQL scripts to query requested data from database. Part 2 writing report discussing current database set-up highlighting advantages and disadvantages, then discussing alternative database set-up discussing advantages and disadvantages against current set-up. All details provided in PDF document. Sample data and current tables provided in text files.


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 be before the cover/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 request 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 brief 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 brief. 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 been 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 been seconded from your current 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 removed from this list). Food2Go have provided you with a sample of their existing database, and require 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 bread, margherita pizza, fried chicken, and onion rings are normally made with bread flour that contains gluten, but the pizza and garlic bread can be made with gluten-free flour instead  The fried chicken is made using eggs  The coconut curry contains both sesame seeds and cashew nuts (a tree nut), but these can both 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 business 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 incorrect 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) 2018 (ICO, 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 currently 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 between 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- regulation-gdpr/ [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