Reference no: EM132485026

SWD406 Introduction to Databases Assignment - Solent University, UK

Assessment Title - Database development and evaluation

Introduction - This assignment requires you to undertake practical database application development work to meet specified requirements and write an evaluation report that discusses the outcomes of the work completed and the processes used.

This assignment is designed to assess the learning outcomes of the unit which are as follows:

1. Explain key issues in the development of relational databases and their role in modern IT systems.

2. Discuss the use of SQL functionality to create information from data.

3. Apply conceptual modelling techniques to the design and implementation of a simple database.

4. Apply enterprise-level database software tools in the development, implementation and testing of SQL-based database solutions.

5. Demonstrate an understanding of and the ability to use SQL for writing queries, creating and populating databases.

6. Demonstrate the ability to write simple Python code to interact with a relational database.

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 those aged under 30. Retrieve the first name, surname, email address, date joined, and the age in years of all shoppers who joined on or after 1st Jan 2020 or those aged 29 or less on the 1st Jan 2020. Print date columns in the format DD-MM-YYYY. Order results by age (highest first) and then surname (A-Z).

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. 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 (right-justified 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 prompting for the user to input a shopper account ref and produce results 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 (right-justified 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.

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. 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 and average quantity sold and show results 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 and questions and answers about 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.

Questions about products are anonymously asked by shoppers and can be answered by other shoppers or by sellers. A question can be answered many times. The date and time that the question is asked and any answers posted also need to 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 this additional functionality 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 Entity Relationship diagram to show your new tables, their primary and foreign keys and how they relate to each other and to the existing tables.

c. Implement your design by creating the new tables, insert enough rows into your new tables to facilitate testing and prove that your integrity constraints work correctly through testing. Include the SQL that you used to create, populate and test the new tables in your submission.

d. Create a view that joins your newly created tables together with existing tables and provide at least two SQL queries that select from this view.

Part 3 - Programming for Databases

Develop Python code to implement some basic text-based functionality 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. Firstly, prompt for the entry of a shopper_id which will be used to test all the menu options. If the shopper_id entered is not found on the database, print an error message and exit the program otherwise print the main menu below.

b. Provide a text-based menu as follows:

ORINOCO - SHOPPER MAIN MENU

1. Display your order history

2. Add an item to your basket

3. View your basket

4. Checkout

5. Exit

c. Implement menu options 1, 2, 3 and 5 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 your query from Part 1b as a basis for the SQL query in your Python code.

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)

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

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

ix. Get the next basket id by selecting from the sqlite_sequence table

x. If the basket is empty, insert a new row into the shopper_basket table using the next basket _id selected in stage ix.

xi. Insert a new row into the basket_contents table for the product they want to purchase again using the next basket id selected in stage ix.

xii. Commit the transaction

xiii. Print "Item added to your basket"

xiv. Return to the main menu

Option 3 - Display your basket

i. If the basket is empty, display an error message otherwise display all rows from the basket_contents table for the current basket as per the example below:

ii. Return to the main menu

Option 5 - Exit

i. Exit the program

d. Option 4 - Checkout your basket

i. If the basket is empty, display an error message otherwise display the current basket and total amount to pay (which you will need to calculate)

ii. If the shopper has more than one delivery address stored for them, display a numbered list of the delivery addresses (most recently used first) and prompt the shopper to choose the address they want to deliver the items to. If the shopper has one delivery address, display and use this address for the checkout. If they have no delivery addresses, prompt the shopper to enter a new delivery address, use this address for the checkout process and insert a new row into the shopper_delivery_addresses table.

iii. If the shopper has more than one payment card stored for them, display a numbered list of the payment cards (most recently used first) and prompt the shopper to choose the card they want to pay with. If the shopper only has one payment card, display the card number and use it for the checkout. If they have no payment card stored for them, prompt the shopper to enter new card details, use this card for the checkout process and insert a new row into the shopper_payment_cards table.

iv. Insert a new row into the shopper_order table for the basket with a status of 'Placed'

v. Insert a new row into the ordered_product table for each item in the basket with a status of 'Placed'

vi. Delete the rows from the shopper_basket and basket_contents tables for this basket

vii. Return to the main menu

You should inserts 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.

The Evaluation Report (max 1500 words) - All students must include an evaluation 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 identify: any deliverables that are not complete or fully tested, and elements of the practical work that you feel contribute to higher grade achievement

b) Your approach to design and testing of the SQL and Python elements of the assessment and what more you would have done if you'd had more time.

c) What you had changed or improved from any pre-submitted versions of parts 1, 2 or 3 based on feedback from your lecturer.

d) Bibliography and references.

Note - Guidelines on 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.

Attachment:- Introduction to Databases Assignment Files.rar