COM711 Databases.

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.
?
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.