Assessment Details

Module Title: Introduction to Databases

Assessment Title: Database development and evaluation

Assessment Type: Report containing code

Restrictions on Time/Word Count: Max. 1500 words for the evaluation report. Any SQL, Python code, diagrams or explanatory text for parts 1, 2 and 3 are not included in this word count.

1. Introduction

This assessment requires you to undertake practical database application development work to meet specified requirements and write an reflective evaluation report that discusses the quality of the work completed and the approaches used.

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 1^st 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 no, 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 sales summary report. Display the seller account ref, seller name, product code, product description, total quantity sold and total sales (sum of quantity*price) for all sellers and the products they sell even if they have not sold any of a particular product (showing any NULL values as 0). Display the total sales with two decimal places and prefixed by a £ sign. Sort results by total quantity sold (lowest first).

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.

 

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.

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.

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 the new functionality for product and seller reviews outlined above 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 Orinoco entity relationship diagram to show the new entities from your design in question a, their primary and foreign keys and how they relate to each other and to the existing tables.

c) Implement your design for product and seller reviews by creating the new tables, insert at least 5 rows into each of your new tables and carry out testing to prove that your integrity constraints (primary, foreign, unique and check constraints) work correctly. Include the SQL that you used to create, populate and test the new tables.

d) Create a view that joins your newly created tables for product and/or seller reviews together at least one existing tables and provide at least two SQL queries that select from this view.

 

Part 3 – Programming for Databases

Develop Python code to implement a basic text-based application 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)

i) 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.

ii) Print a text-based menu as follows:

ORINOCO – SHOPPER MAIN MENU

---

1. 1. 1. Display your order history
      2. Add an item to your basket
      3. View your basket
      4. Change the quantity of an item in your basket
      5. Remove an item from your basket
      6. Checkout
      7. Exit

iii.  As shoppers should be able to resume a basket previously created from a previous execution of the program on the same day, check if there is a row in the shopper_baskets table created today for the selected shopper and, if so, make this the current basket otherwise create a new (empty) basket. If there is more than one basket created today for the shopper, use the most recent one.

You can use the following SQL query to return the most recent basket for the current shopper created today (if there is one):

SELECT basket_id

FROM shopper_baskets

WHERE shopper_id = ?

AND DATE(basket_created_date_time) = DATE(‘now’)

ORDER BY basket_created_date_time DESC

LIMIT 1

When you execute the query, pass the shopper_id as a parameter to replace the ? placeholder.

 

b) Implement menu options 1 and 7 as follows:

Option 1 – Display your order history

i) 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 the query you wrote for Question 1b of this assessment as a basis for the SQL query for this option.

ii) Sort orders by order date (most recent first)

iii) If no orders are found for the shopper_id that you are testing with, print the message “No orders placed by this customer”

iv)Display the data in the format shown below (which is for shopper_id 10010)

v) Return to the main menu

Option 7 – Exit

Exit the program

 

c) Implement menu options 2 and 3 as follows:

Please note: The details of the shopper’s basket should be stored in the shopper_baskets and basket_contents tables and not in a Python data structure (like a list). This will allow a shopper to continue with their last basket if they didn’t complete the checkout in a previous execution of the program.

Option 2 – Add an item to your basket

i) Display a numbered list of product categories

ii) 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.

iii) Display a numbered list of the available products in the category selected.

iv) Prompt the user to enter the number of the product they want to purchase and store the product_id for the selected product.

v) Display a numbered list of sellers who sell the product they have selected and the price they are selling that product at

vi) Prompt the user to enter the seller they wish to buy the product from and store the seller_id for the selected seller

vii) Prompt the user to enter the quantity of the selected product they want to order. Display ‘The quantity must be greater than 0’ if the quantity is <=0 and re-prompt the user to enter it again.

viii) Get the price of the selected product from the selected supplier

ix) 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_baskets table using the next basket _id.

x) 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. All items added to the basket should have the same basket_id in the basket_contents table.

xi) Commit the transaction

xii) Print “Item added to your basket”

xiii) Return to the main menu

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

 

To simplify your code, the following function can 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

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 ‘Your basket is empty’ otherwise display all rows from the basket_contents table for the current basket, labelling each item with a basket item no. starting at 1. Also display a total basket cost.

An example of how the basket should be displayed is shown below:

ii) Return to the main menu

d) Implement the remaining menu options (4, 5 and 6) as follows:

Option 4 – Change the quantity of an item in your basket

i) If the basket is empty, display ‘Your basket is empty’ and return to the main menu otherwise display the current basket and the basket total (as per option 3.

ii) If there is more than one item in the basket, prompt the user to enter the basket item no. of the item they want to update. If they enter an invalid basket item no., display ‘The basket item no. you have entered is invalid’ and re-prompt the user to enter it again.

If there is only one item in the basket, this will obviously be the one the user wants to change.

iii) Prompt the user to enter the new quantity for the item selected. If they enter a quantity <= 0, display ‘The quantity must be greater than 0’ and re-prompt the user to enter it again.

iv) Update the basket_contents table with the new quantity for the current basket and item that has been changed.

v) Display the current basket with a re-calculated total.

vi) Return to the main menu

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.

 

“You can order Database development and evaluation Assignment  from our service and can get a completely high-quality custom paper. Our service offers any of the Databases Academic paper written from scratch, according to all customers’ requirements, expectations and highest standards.”