COM711 Databases Solent University

Buy this assignment for 150 Pounds now at thebestassignmenthelpuk@gmail.com

	Databases
Module Code:	COM711
Module Leader:	Kenton Wheeler
Level:	7
Assessment Title:	Databases
Assessment Number:	1
Assessment Type:	Report containing code
Restrictions on Time/Word Count:	Max. 2000 words for your report. Any SQL, Python code, diagrams, and explanatory text for parts 1, 2, and 3 are not included in this word count.
The consequence of not meeting the time/word count limit:	There is no penalty for submitting below the word/count limit, but students should be aware that there is a risk they may not maximize their potential mark. Assignments should be presented appropriately in line with the restrictions stated above; if an assignment exceeds the time/word count this will be taken into account in the marks given using the assessment criteria shown.
Individual/Group:	Individual
Assessment Weighting:	100%
Issue Date:	30th October 2020
Hand In Date:	22nd January 2021
Planned Feedback Date:	19th February 2021
Mode of Submission:	On-line via SOL
The number of copies to be submitted:	N/A
Anonymous Marking	This assessment will be marked anonymously

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.

COM711 Databases Solent University

The Best Assignment Help UK

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.

1. The company wants to do a marketing campaign for 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.

10019. The website requires a customer account history page that 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.
10020. The business relationship manager has asked you to write a summary report on the sellers and products that they have had sold since 1^st June 2019. Display the seller account ref, seller name, product code, product description, number of orders, total quantity sold, and the 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.
10021. 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. Canceled 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 it’s in. Both averages should be displayed to an accuracy of 2 decimal places and results should be shown in the category description, then product description order.
10022. The Best Assignment Help UK

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.

The Best Assignment Help UK

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.

The Best Assignment Help UK

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 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 three new tables and you must link to at least two of the existing tables.
2. 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.
3. 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.
4. 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.
5. The Best Assignment Help UK

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.

1. 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 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.
2. Provide a text-based menu as follows:
3. The Best Assignment Help UK

ORINOCO – SHOPPER MAIN MENU

1. Display your order history
2. Add an item to your basket
3. View your basket
4. Checkout
5. Exit
6. Implement menu options 1, 2, 3, and 5 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 your query from Part 1b as a basis for the SQL query in your Python code.
2. Sort orders by order date (most recent first)

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

1. Display the data in the format shown below (which is for shopper_id 10010)
2. Return to the main menu

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

• Display a numbered list of the available products in the category selected

1. Prompt the user to enter the number of the product they want to purchase and store the product_id for the selected product
2. Display a numbered list of sellers who sell the product they have selected and the price they are selling that product at
3. Prompt the user to enter the seller they wish to buy the product from and store the seller_id for the selected seller

• Prompt the user to enter the quantity of the selected product they want to order
• Get the price of the selected product from the selected supplier

1. 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_basket table using the next basket _id.
2. 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. Please note: All items added to the basket should have the same basket_id in the basket_contents table.
3. Commit the transaction

• Print “Item added to your basket”
• 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 must 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

the title is some text to put above the list of options to act as a title

type is used to customize 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 an error message otherwise display all rows from the basket_contents table for the current basket and a total basket cost as per the example below:
2. Return to the main menu

Option 5 – Exit

1. Exit the program
2. Option 4 – Checkout your basket
3. If the basket is empty, display an error message otherwise display the current basket and the basket total (the same as option 3)
4. 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.

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

1. Insert a new row into the shopper_order table for the basket with a status of ‘Placed’
2. Insert a new row into the ordered_product table for each item in the basket with a status of ‘Placed’
3. Delete the rows from the shopper_basket and basket_contents tables for this basket

• Return to the main menu

Below is an example of what should be displayed for shopper_id 10000 who has more than one delivery address and only one payment card stored on the system:

Below is an example of what should be displayed for shopper_id 10005 with more than one delivery address and more than one payment card stored on the system:

Below is an example of what should be displayed for shopper_id 10023 with no delivery addresses or payment cards stored on the system:

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.

COM711 Databases Solent University

3. Evaluation and Research Report (guideline – approx 2000 words)

All students must include a report in their submission which should cover the following:

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

1. b) Discuss ‘Why relational databases continue to be important for most enterprises despite the rise in popularity of NoSQL databases?’. Back up your arguments with research.
2. c) Bibliography and references

 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) 

COM711 Databases Solent University

Assessment criteria

The summary grid below is the basis for grading achievement. Higher levels of achievement are described on 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.

 

COM711 Databases Solent University

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 the required threshold.	At least two queries were submitted that produce the correct or mostly 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.	Has submitted queries a, b, and c and they all produce the correct or mostly correct results.   The queries meet the basic requirements and some attempt has been made to improve the presentation.   Evidence of testing has been provided for some of the queries.     Some explanation has been provided for each query demonstrating a basic understanding of the concepts.	Has submitted queries a, b, and c and they all produce the correct results     The queries meet the majority of the requirements and the output is well presented.       Some evidence of testing has been provided to ensure the results for each query are correct.   Some explanation has been provided for each query demonstrating a good understanding of the concepts.	Has submitted queries a, b,c, and d and they all produce the correct results.     The queries meet or exceed all the requirements and the output is professionally presented.     Evidence of through testing has been provided to ensure the results for each query 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 – 25%
Does not reach the required threshold.	The design has some flaws and only partially meets the requirements. The design has a minimum of three tables and links to at least two existing tables.   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.   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 incorrect.   No view has been created or used.	The design has some flaws but mostly meets the requirements The design has a minimum of three tables and links to at least two existing tables.   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 basic test data.     Evidence has been provided that some of the new constraints have been tested to ensure they work correctly.   An amended ERD diagram has been provided but it is incorrect.     No view has been created or used.	The design is correct and meets 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 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 a reasonable amount of test data.   Evidence has been provided that some of the new constraints have been tested to ensure they work correctly.   A correctly amended ERD diagram has been provided.       If a view was created, it was not fully tested with at least two correct 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 with a good amount of test data.   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 the new and existing tables and at least two queries correctly written using the view.
Part 3 – Programming Learning Outcomes K1 and T1 – 25%
Does not reach the required threshold.	Menu option 1 has been attempted but does not work correctly or does not meet the requirements.       The user interface is basic or does not function correctly.     The code retrieves data from the database       The program has 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 handling.	Menu option 1 has been fully implemented, it works correctly and menu options 2 and 3 are partially implemented or do not function correctly 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.	Menu options 1,2 and 3 have been fully implemented, work correctly and mostly meet the requirements       The user interface is 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.   The code is correctly structured and well-written.     There is evidence that the code has been tested     Clear comments are used throughout the code.   There is some evidence of error handling.	All menu options have been fully implemented, they all work correctly and fully meet the requirements.       The user interface is good 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     Clear comments are used throughout the code.   Extensive error handling has been used.
Report = 20%
Little or no evaluation of the quality of the work you have produced, your approach to design and testing, or time management aspects Very poor presentation and structure, incomplete, doesn’t satisfactorily address the research topic.	A basic description of the approach you used to produce your deliverables and an evaluation of the quality of the work you have produced.         Reasonable presentation and structure. Not fully accurate, and not consistently relevant to the research topic. Partially complete.	Some reflection on the approach you used to produce your deliverables, the quality of the work you have produced, and how well you managed your time.         Clear structure, largely accurate information generally appropriate to the topic but no evidence of research is included to substantiate it.	An honest self-assessment of the approach you used to produce the deliverables, the quality of the work you have produced stating what you did well and not so well and how well you managed your time.     A thorough presentation of information and clear structure. Evidence of research to back up the arguments presented.	An honest, reflective, and insightful evaluation of the approach you used to produce the deliverables, the quality of the work you have produced stating what you did well and not so well, and how well you managed your time.   Excellent and critical presentation of information in a well-structured way. Information complete and evidence of thorough research to back up the arguments presented.

COM711 Databases Solent University

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;

• 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

COM711 Databases Solent University

Extenuating Circumstances

The University’s Extenuating Circumstances procedure is in place if there are genuine circumstances that may prevent a student from submitting an assessment. If students are notified 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 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

COM711 Databases Solent University

The Best Assignment Help UK

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

COM711 Databases Solent University

The Best Assignment Help UK

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

COM711 Databases Solent University

Guidance for online submission through Solent Online Learning (SOL)

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

Visit At : – COM709 Computer Fundamentals

COM709 Computer Fundamentals