Order this Assignment Now: £229

The company want to do a marketing campaign to new shoppers and all female shoppers.

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

PLEASE NOTE: Students must use the tables and data supplied in the Orinoco create script (available on the Assessments tab on SOL) to answer parts 1, 2 and 3 of this assessment.

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.

The output below is shown to illustrate the format of the results that your query should produce, not the actual results

Shopper first name	Shopper surname	Email address	Gender	Date Joined	Current Age
Jane	Doe	Jane.doe@gmail.com	F	15/06/2019	56
Rachel	Doe	rdoe1565@hotmail.co.uk	M	30/11/2020	47
Kris	Doe	krisdoe@yahoo.co.uk	Not known	12/10/2020	22

b) The website requires a shopper order history page which will accept the shopper id as a parameter entered by the user at run time and will display the order history for only that one shopper. Write a query to retrieve the first name and surname for a specific shopper along with details of all the orders they’ve made, 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 by running it twice once for shopper id 10000 and a second time for shopper id 10019.

The output below is shown to illustrate the format of the results that your query should produce, not the actual results

Shopper first name	Shopper surname	Order ID	Order Date	Product Description	Seller Name	Qty ordered	Price	Order Status
Jane	Doe	1234	02-03-2020	Sony Bravia KD43 TV	ABC Ltd	1	£419.99	Delivered
Jane	Doe	1234	02-03-2020	Apple iPhone 13	ABC Ltd	2	£699.99	Delivered
Jane	Doe	1345	08-03-2020	HP Deskject 2700	Colorado	5	£57.95	Dispatched

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, number of orders, total quantity sold and total sales (calculated as the sum of quantity*price) for all sellers and the products they sell even if they have not sold any of a particular product. NULL values should be displayed as 0, display the total sales with two decimal places and prefixed by a £ sign and sort results by total quantity sold (lowest first).

The output below is shown to illustrate the format of the results that your query should produce, not the actual results

Seller Account Ref	Seller Name	Product Code	Product Description	No. of Orders	Total quantity sold	Total Value of Sales
COL0799	Colorado	304409	Sony Bravia KD43 TV	0	0	£0.00
ABC0055	ABC Ltd	305504	Apple iPhone 13	2	4	£2799.96
ABC0055	ABC Ltd	304409	Sony Bravia KD43 TV	7	7	£2939.93
COL0799	Colarado	306050	HP Deskjet 2700	6	10	£579.50

d) The head of sales wants a summary 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 sort the results by category description and then product description.

The output below is shown to illustrate the format of the results that your query should produce, not the actual results

Category Description	Product Code	Product Description	Avg Qty Sold	Avg Qty Sold for Category
Mobile Phones	378292	Nokia X20	2.56	7.55
Printers	310383	HP LaserJet 3199	4.15	4.56
TV & Audio	308893	Digihome 43552UHDH LED TV	0	4.10
TV & Audio	390292	LG 4K Ultra HD TV	3.50	4.10

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.

Your design should allow multiple reviews to be stored about a particular product or a particular seller and multiple reviews to be submitted by a specific shopper.

All students should complete questions a, b and c below and to achieve a higher grade, also complete question d.

1. 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. You should not amend the existing tables in any way such as adding new columns or foreign keys.
2. 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.
3. Implement your design for product and seller reviews by creating the new tables, insert enough rows into all of your new tables in order to test that all the functional requirements are met and carry out testing to prove that your integrity constraints (primary, foreign, unique, not null and check constraints) work correctly. Include the SQL that you used to create, populate and test the new tables.
4. Create a view that joins at least one newly created table for product and/or seller reviews with at least one existing table 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.

1. 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:

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

1. Implement menu options 1 and 7 as follows:

Option 1 – Display your order history

1. 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.
2. Sort orders by order date (most recent first)
3. If no orders are found for the shopper_id that you are testing with, print the message “No orders placed by this customer”
4. Display the data in the format shown below (which is for shopper_id 10010)

1. Return to the main menu

Option 7 – Exit

1. Exit the program
2. 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

1. Display a numbered list of product categories
2. 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
3. Display a numbered list of the available products in the category selected
4. Prompt the user to enter the number of the product they want to purchase and store the product_id for the selected product
5. Display a numbered list of sellers who sell the product they have selected and the price they are selling that product at
6. Prompt the user to enter the seller they wish to buy the product from and store the seller_id for the selected seller
7. 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.
8. Get the price of the selected product from the selected supplier
9. If there is no current basket, 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.
10. 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.
11. Commit the transaction
12. Print “Item added to your basket”
13. 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(" ",title," ")
    for option in all_options:
        code = option[0]
        desc = option[1]
        print("{0}. {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

1. 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:

 

1. Return to the main menu
2. Implement the remaining menu options (4, 5 and 6) as follows:

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

1. 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.
2. 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.

1. 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.
2. Update the basket_contents table with the new quantity for the current basket and item that has been changed.
3. Display the current basket with a re-calculated total.
4. Return to the main menu

Below is an example of what should be displayed and what should be prompted if there is more than one item in the basket:

 

Below is an example of what should be displayed and what should be prompted if there is only one item in the basket:

Option 5 – Remove an item from your basket

1. If the basket is empty, display ‘Your basket is empty’ otherwise display the current basket and the basket total as per option 3.
2. 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 remove. 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 remove.

1. Prompt the user to confirm they definitely want to remove the selected item from their basket by entering Y or N.
2. If the user confirms they definitely want to remove the selected item, delete the item from the current basket in the basket_contents table.
3. Check if the basket is now empty and if so, display ‘Your basket is empty’ otherwise display the current basket with a re-calculated total.
4. Return to the main menu

Below is an example of what should be displayed and what should be prompted if there is more than one item in the basket:

Below is an example of what should be displayed and what should be prompted if there is only one item in the basket:

Option 6 – Checkout your basket

1. If the basket is empty, display a suitable message and return to the main menu
2. Display the current basket and the basket total (the same as option 3) and ask the user if they wish to proceed with the checkout (Y or N). If they enter N, return to the main menu. If they enter Y, continue as follows:
3. Insert a new row into the shopper_order table for the basket with a status of ‘Placed’
4. Insert a new row into the ordered_product table for each item in the basket with a status of ‘Placed’
5. Delete the rows from the shopper_basket and basket_contents tables for this basket
6. Print the message ‘Checkout complete, your order has been placed’
7. Return to the main menu

Below is an example of what should be displayed for a shopper who proceeds with the checkout process:

Do you wish to proceed with the checkout (Y or N)?

Checkout complete, your order has been placed.

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

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 Report (guideline - approx 1500 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) 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. A title page

2. Contents page and page numbers.

3. Copies of all your final (versions of the deliverables for parts 1, 2 and 3.

4. Your report (see section 3 above

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	A4->A1
Part 1 - Retrieving Data using SQL – Learning Outcomes K1, C1, P1 – 30%
Does not reach required threshold.	Query a, b and c were attempted but only one query produces the correct results     The queries meet the basic requirements but no attempt has been made to improve the presentation.   No evidence of testing has been provided to ensure the results for each query are correct.     No explanation has been provided for each query.	Query a, b and c were attempted but only two queries produce the correct or mostly correct results   The queries meet the basic requirements and some attempt has been made to improve the presentation.     Some evidence of testing has been provided for some of the queries but the testing is poor.     Some explanation has been provided for each query but it is incomplete, inaccurate or uses the wrong terminology	Query a, b and c were attempted and all three produce the correct or mostly correct results     The queries meet all the requirements and the output is formatted as requested and well- presented.     Reasonable evidence of testing has been provided for all queries to ensure the results for each one are correct.   An accurate explanation has been provided for each query demonstrating a good understanding of the concepts.	Query a, b, c and d were attempted and all produce the correct results.       The queries meet or exceed all the formatting requirements and the output is professionally presented.   Evidence of through testing has been provided for all queries to ensure the results for each one are correct.   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 – 30%
Does not reach required threshold.	The design has some flaws and only partially meet the requirements.   The design has a minimum of two new tables and links to at least one existing table.   The rationale behind the design has not been provided and any design assumptions have not been outlined.   The newly-created tables have not been correctly implemented or the primary and foreign keys are missing or incorrect.     Each new table has not been populated with sufficient test data to test the functional requirements.   No evidence has been provided that the new constraints have been tested to ensure they work correctly.   No amended ERD has been provided or the one provided is very poor   No view has been created or used.	The design has some flaws but mostly meets the requirements.   The design has a minimum of two new tables and links to at least one existing table.   A basic rationale behind the design has been provided but no design assumptions have been outlined.     The newly-created tables have been correctly implemented with the correct primary and foreign keys. No other constraints have been used.       Each new table has been populated with sufficient test data to test most of the functional requirements     Evidence has been provided that a few of the new constraints have been tested to ensure they work correctly.   An amended ERD diagram has been provided but it is incorrect or incomplete   A view was created but it was incorrect, was not based on new and existing tables and/or was not fully tested with at least two queries	The design is correct, and meets the requirements.     The design has a minimum of three new tables and links to at least two existing tables.   The rationale behind the design has been explained and any design assumptions have been outlined.       The newly-created tables have been correctly implemented with the right primary and foreign keys. Some other constraints have been used.       Each new table has been populated with sufficient test data to test all the functional requirements       Evidence has been provided that at least one constraint of each type has been tested to ensure they work correctly.   An amended ERD diagram has been provided and it is mostly correct.     A view was created, it was based on new and existing tables but not tested with at least two queries	The design is correct and meets or exceeds the requirements.     The design has a minimum of three tables and links to at least two existing tables.   The rationale behind the design has been fully explained and any design assumptions have been outlined.     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 been populated to extensively test all the functional requirements       Full evidence has been provided that all new constraints have been tested to ensure they work correctly.       A correctly amended ERD diagram has been provided.       A view has been created based on new and existing tables and at least two queries correctly written using the view.
Part 3 - Programming Learning Outcomes K1 and T1 – 30%
Does not reach required threshold.	The menu and at least menu options 1 and 7 have been fully implemented, they function correctly and mostly meet the requirements. Any other menu options have only been partially implemented, do not function correctly or do not meet the requirements   The user interface is basic or does not function correctly.       The code retrieves data from the database       The program uses no functions.           There are some major issues with how the code has been written.   There is little or no evidence that the code has been tested             Comments are not used in the code     There is no evidence of error or exception handling.	The menu and at least menu options 1 and 7 have been fully implemented, they function correctly and mostly meet the requirements. Menu options 2 and 3 have been fully or partially implemented, work correctly and mostly meet the requirements.       The user interface is basic but functional.         The code retrieves data from the database       The program uses the supplied function without modification.       There are some minor issues with how the code has been written.   There is some evidence that the code has been tested               Comments are sparsely used in the code   There is some evidence of error handling but no exception handling	The menu and at least menu options 1, 2, 3 and 7 have been fully implemented, they function correctly and mostly meet the requirements. Menu options 4, 5 or 6 have been attempted but do not function correctly or only partially meet the requirements   The user interface is generally good with clear prompts and helpful error messages.   The code retrieves data from the database and inserts rows in the database.   The program uses the supplied function (or a modified version of it) and at least one other user-defined function   The code is mostly correctly structured and well-written.     There is evidence that the code has been fully tested but no evidence that the data has been successfully inserted, updated or deleted correctly from the database   Clear comments are used throughout the code.   There is some evidence of error and exception handling.	The menu and all menu options have been fully implemented, they all work correctly and fully meet the requirements.                     The user interface is excellent with clear prompts and helpful error messages.     The code retrieves data from the database and inserts, updates and deletes rows in the database.   The program makes extensive use of functions to modularise the code.       The code is correctly structured and well-written.     There is evidence that the code has been thoroughly tested and that data has been successfully inserted, updated and deleted from the database.       Extensive comments are used throughout the code.   Extensive error and exception handling has been used in the code.
Evaluation Report = 10%
Does not reach required threshold.	Little or no description of the approach you used to produce the work   No self-assessment of the quality of work has been provided         There is little or no reflection on the challenges faced or time management	Some description has been provided outlining the approaches used to produce the work.   Some self-assessment of quality of the work produced.         Some reflection on challenges faced, what could be improved and how time was managed	A good description of the approach used to produce the work e.g. design and testing.   An honest self-assessment of the quality of the work produced.         An honest reflection of the challenges faced, what areas could be improved, how time was managed.	A detailed and insightful description of the approach used to produce the work e.g. design and testing   An honest self-assessment of the quality of the work produced stating accurately what was good and not so good.   An honest reflection of the challenges faced, how these were overcome, what areas could be improved, how time was managed and could have been managed better with hindsight.

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:

1. 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;
2. 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;
3. 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/academic-handbook/2o-assessment-principles-regulations-temporary-amendments-for-covid-19-contingency-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/academic-handbook/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/academic-handbook/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.

The Ethics Policy is contained within Section 2S of the Academic Handbook:

https://staff.solent.ac.uk/official-documents/quality-management/academic-handbook/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/academic-handbook/2o-annex-3-assessment-regulations-grade-marking-scale.docx

Guidance for online submission through Solent Online Learning (SOL)

http://learn.solent.ac.uk/onlinesubmission