You will create system documentation of the design process and technicalities of the final database; explaining your decisions and implementation for future developers, alongside any limitations you met or consideration for future improvements

2024-11-27 11:45:30

ASSESSMENT BRIEF

ACADEMIC YEAR 2024/25

TRIMESTER 1 LEVEL: 4

MODULE TITLE: Relational Database Development Fundamentals

MODULE CODE: MOD008905

INTRODUCTION

This assignment involves creating and documenting a database for a retail company to manage their inventory (stock) across multiple stores.The full scenario brief is at the end of this document.

You will create system documentation of the design process and technicalities of the final database; explaining your decisions and implementation for future developers, alongside any limitations you met or consideration for future improvements (Task 1).

You will also give a walkthrough of your database as if to a project team lead, including demonstrating its functionality and relevant queries (Task 2).

This assignment is worth 100% of your grade for this module. It is broken down into 2 tasks each worth 50% and covers the following Learning Outcomes:

Learning Outcomes
No.	Type	Description
1	Knowledge and understanding	Analyse system problems and requirements.
2	Knowledge and understanding	Design appropriate solutions using logical data modelling and normalisation.
3	Intellectual, practical, affective, and transferrable skills	Implement a Relational Database.
4	Intellectual, practical, affective, and transferrable skills	Query data to satisfy business requirements.
5	Intellectual, practical, affective, and transferrable skills	Create appropriate specification documentation for a given scenario.
6	Knowledge and understanding	Work independently and with creativity to deadlines.

YOUR TASKS

Task 1 (Element 010-1) – Learning Outcomes 1-6

A set of system design documentation (3000 words equivalent) – worth 50% of the final grade. The deadline for submission of the documentation is 2pm on 13 December 2024. You will submit a single PDF file to Canvas for grading and a zip file of your database for reference. A template will be provided to you however your documentation is expected to include (in order):

Title page.
A table of contents.
A table of figures (diagrams/images/screenshots).
Design
- Requirements (LO1, 5):
  - Annotated brief, highlighting elements identified.
    - e.g. users will not share email addresses; a person can only rent one movie at a time…
  - Explanation of what an ERD is.
  - Your initial ERD.
  - Explanation of what user stories are.
  - Description of how they were generated from the ERD.
  - A list of your user stories.
  - Explanation of what normalisation is and why you do it.
    - Description of optional/mandatory notation and justify your choice in applying them.
  - Your normalised ERD.
  - Explanation of what primary and foreign keys are.
  - Explanation of what a table diagram is.
  - Your Table Diagram.
- Technology:
  - Explanation of what SQLite is and its benefits.
  - Explanation of what a table schema is.
  - Your Table Schema.
  - Did you implement as expected or run into any issues, explicitly mention Appendix A as proof of implemented tables and Appendix B as proof of implemented queries.
Reference List
Appendix A: Table Screenshots (Design View) (LO3)
Appendix B: Queries (SQL Code) (LO4)

o Assumptions (LO1, 5, 6):

o Entity Relationship Diagram (LO2, 5, 6):

o User Stories (LO1, 5, 6):

o Normalised Entity Relationship Diagram (LO2, 5):

o Table Diagram (LO2, 5):

· Implementation

o Table Schema (LO5):

o Successes and Limitations (LO3, 4, 6)

Task 2 (Element 010-2) – Learning Outcomes 2, 3, 4, 6

Walkthrough and demonstration of the database of up to 20 minutes maximum – worth 50% of the final grade.

Demonstrations will be carried out either face-to-face (in person) in weeks 10 and 11 or may be submitted as a video recording (mp4) with the final deadline of 5pm on 6 December 2024.

During your walkthrough/demonstration you are expected to include:

· A quick introduction including your student ID number.

“I am student 123456, and this is the walkthrough of my database for module 008905, relational database fundamentals.”

· Show the design view then the table/data view, of each table in the database (LO2, 3, 6):

Explain what the table contains e.g. “the users table contains details that define a user account such as username and password.”
Point out any primary keys, foreign keys, mandatory fields, automatically incrementing values, or other points of interest.
Show the test data input.
- “The user ID numbers in the login table match the user ID numbers listed in the user table because…”.

· Talk through at least one set of connecting tables showing that the test data values match between tables (LO2, 3):

· Demonstrate your SQL Queries one at a time (LO2, 3, 4, 6):

Talk through the SQL code to explain what it is doing.
Run the query and show the results.
- Show the test data to manually confirm it is producing the correct results.

· Answer a small selection of questions (available week 10) about your design (LO6):

There are no right or wrong answers.
- Questions could include asking how or if you might change your design if the company changed their processes, how it would or would not handle additional data, or why you implemented things in certain ways.

SCENARIO

Stone Stores are a retail chain of shops selling a variety of items (you may choose which items Stone Stores will sell). They want you to implement a database to monitor their stock across theirs stores and track resupply requests.

The company has several stores across the country. Every store sells the same products at the same prices and minimum required quantity of stock they should keep on hand is set on a product level. The products are sourced from a range of third-party suppliers.

If a store is running low on stock, an employee will raise a request for more (known as a Purchase Order). An employee may request any quantity of any number of products on the same purchase order. The purchase order must be authorised by the store manager before it is submitted.

Once a purchase order is authorised, the company can send orders to the appropriate suppliers for the products. An order is for a single supplier and may be for more than one product at once. Stone Stores have agreed that managing the order creation would be beneficial, but not essential at this stage.

Once a product is delivered to the store, the line of the purchase order is marked as received and the quantity of stock for the store is updated. When all items on a purchase order are received, the purchase order is considered complete.

The management of Stone Stores also require certain data from the system for accounting and reporting reasons. They want you to provide queries to retrieve a minimum of 4 of the following sets of data:

A list of all stores, their address, and their managers.
The top 5 products ordered and the quantity that was ordered of them.
A list of all products costing more than £30.
The total amount (£) spent by each store across all purchase orders.
The total number of purchase orders raised each month over the last year.
1. Assuming that purchase orders are replacing stock which has been sold, they want a list of total profit made by each store in the last 6 months.
2. A list of all purchase orders raised in the 6 months, including the date raised and the employee who raised it. If possible, also display the overall state of the purchase order (outstanding or complete).
3. For a given purchase order (choose one as an example), they need to retrieve a list of each product requested, the quantity needed, the total price that would be (per product), and the supplier it needs ordering from.

100% Plagiarism Free & Custom Written, Tailored to your instructions