Assessment Cover Sheet And Feedback Form 2019–2020 Course Co ✓ Solved

Assessment coversheetandfeedbackform20192020coursecodeuc1pr21

Assessment Cover Sheet and Feedback Form 2019/2020 Course Code: UC1PR2101 Course Title: Programming & Databases Lecturer: Johan van Niekerk and Rayne Reid Assignment No: N/A Total number of pages, including this page: 3 + Jupyter Notebook template Maximum Word Count: Not specified Assignment Title: Assignment 3 of 3: Relational Database Implementation and Analyses Date Set: 30.04.2020 Submission Date: 22.05.:00) Feedback Date: 12.06.2020 Section 1: Submission Record of Submission and Plagiarism Declaration In submitting this work you declare that this assignment is your own work and that any collaborative work or existing material has been specifically indicated in the text. In submitting this assignment you agree that this work may be submitted for plagiarism testing. Work should be submitted: • Electronically Via Moodle in all cases. • If problems are encountered you may email the course staff for assistance. However, it remains the student’s responsibility to ensure the submission to the LMS was made. Assignments submitted in digital form MUST use the following file naming convention: COURSECODE_SURNAME_ITEMNUMBER , where ‘item number’ refers to the number order of each file begin submitted for this assessment. For example, UC1PR2101_ENGELAND_1 Digital assignments must be handed in as a Jupyter Notebook .ipynb file. Please refer to the provided tenplate. Late submissions will result in zero grade, unless agreed with course leader in advance. IT IS YOUR RESPONSIBILITY TO KEEP A COPY OF ALL SUBMITTED WORK. 2 Section 2: Assignment Details Consider the following scenario. You will be required to complete the provided Jupyter notebook file in order to produce a report to answer each of the subsequent questions. You will need to make some assumptions in order to complete your solution, please state clearly any assumptions you make in the provided reflection section after each of the tasks. Solutions must be appropriately formatted and professional looking. Only the Jupyter notebook file (.IPYNB) will be submitted for grading. Scenario “Academic Booksellers Inc is a new online book retailer. They specialise in selling academic textbooks and ship orders worldwide at no additional charge. All their books are sourced from specialised academic publishers. Customers often order multiple books, or even multiple copies of each book, in the same order.†A design team has already produced the following ERD for the business database. Primary keys of all tables should be autoincrement integers. All currency values should be stored as real numbers, quantities as integers, and other fields, including phone numbers, should by stored as text. 3 NOTE: This is an individual assignment. You are therefore required to work independently on your solutions. Group work will not be accepted. Section 3: Marking and Assessment This assignment will be marked out of: Task 1 – DDL and Database Creation DDL SQL Statements Reflection Task 2 – Database Schema Exploration Task 3 – Business Logic and DML Stored Procedure 1 Stored Procedure 2 Stored Procedure 3 Reflection and additional information Task 4 – Queries and Data Exploration Report 1 Report 2 Report 3 Report 4 Report 5 Reflection and Assumptions 100% 20% 15% 5% 10% 40% 10% 10% 15% 5% 30% 5% 5% 5% 5% 5 5 Contribution to the final course mark: 50% Estimated number of hours to be spent on this assignment: 35 The pass grade for this assignment is 40% Feedback for this assignment and a breakdown of the final grade will be provided via the LMS (Moodle). The final course grade composed of this and any other assignment(s) will be awarded in the range A-F and recorded in the primary student information system (e.g. ECS) 36 Month Sales Forecast 36 Month Sales Forecast Fiscal Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Totals Product Name Enter Product/Service Name Here Units 1,,,,,,,,,,,,,600 Selling Price $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 $5.00 Total Sales $5,000 $7,500 $5,000 $5,000 $5,000 $5,000 $5,500 $6,000 $6,500 $7,000 $7,500 $8,000 $73,000 % of Total 6.85% 10.27% 6.85% 6.85% 6.85% 6.85% 7.53% 8.22% 8.90% 9.59% 10.27% 10.96% 100.00% 2021 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Totals Units 1,,,,,,,,,,,,,800 Selling Price $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 Total Sales $6,630 $7,650 $6,120 $6,630 $7,140 $7,650 $8,160 $6,120 $6,630 $7,140 $7,650 $8,160 $85,680 % of Total 7.74% 8.93% 7.14% 7.74% 8.33% 8.93% 9.52% 7.14% 7.74% 8.33% 8.93% 9.52% 100.00% 2022 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Totals Units 1,,,,,,,,,,,,,200 Selling Price $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 $5.10 Total Sales $7,650 $8,160 $10,200 $5,100 $5,100 $5,100 $5,610 $6,120 $6,630 $7,140 $7,650 $8,160 $82,620 % of Total 9.26% 9.88% 12.35% 6.17% 6.17% 6.17% 6.79% 7.41% 8.02% 8.64% 9.26% 9.88% 100.00% Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Units 14,,,200 Total Sales $73,000 $85,680 $82,620 This worksheet forecasts 3 years of product sales. The first two key data entries that you need to make are the first fiscal year and the first month of the forecast. With the first year and month entered, the spreadsheet will automatically enter the remaining years and months. Your next step is to enter the number of units and selling price for each month. This worksheet will print Centered and in Black & White. Enter Start Month Here. 36 Month Sales Forecast Units Total Sales

Sample Paper For Above instruction

In this report, we explore the process of implementing and analyzing a relational database designed for an online academic bookseller, Academic Booksellers Inc. This case study offers insights into database creation, schema exploration, business logic implementation, and data querying. The goal is to establish a robust, professional, and well-structured relational database that supports the company's operational needs, including order processing, inventory management, and sales analysis.

Introduction

The scenario simulates a modern e-commerce platform aimed at selling academic textbooks globally. The design of an effective relational database is critical for supporting complex operations such as handling multiple books per order, multiple copies, and various customer details. The initial phase involves creating database objects using Data Definition Language (DDL), followed by schema exploration to understand structure and relationships, business logic via stored procedures, and advanced data querying for analytical reporting.

Database Creation and Schema Design

The core of the database setup involves translating the ERD into SQL DDL statements. All primary keys are set as auto-increment integers to uniquely identify records. Currency values are stored as real data types for precision, quantities as integers for accuracy, and text fields handle non-numeric data like phone numbers. For example, creating the 'Customers' table involves defining a CustomerID as an auto-increment primary key, along with name, contact details, and address fields.

Business Logic and Stored Procedures

Implementing business logic through stored procedures enhances data integrity and simplifies complex operations. For instance, stored procedures could include functions to add new orders, calculate total sales, or update inventory levels after sales. These procedures are coded systematically, with consideration for transaction management and error handling. Reflection on each implementation assesses performance and potential improvements.

Data Exploration and Querying

Once the schema and procedures are in place, data exploration through select queries provides insights into sales trends, customer behavior, and product performance. Examples include aggregating total sales per month or identifying best-selling textbooks. The queries are tailored to deliver clear, actionable reports that inform strategic decisions.

Conclusion

The systematic approach to relational database implementation demonstrates the importance of meticulous schema design, business logic coding, and data analysis techniques. By adhering to best practices, the database becomes a powerful tool for academic booksellers to optimize operations, forecast sales, and enhance customer satisfaction.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill.
  • Practical SQL: https://sqlzoo.net/
  • W3Schools SQL Tutorial: https://www.w3schools.com/sql/
  • Microsoft SQL Documentation: https://docs.microsoft.com/en-us/sql/
  • PostgreSQL Documentation: https://www.postgresql.org/docs/
  • MySQL Documentation: https://dev.mysql.com/doc/

Related Assignments