Reassessment Task 2: Implementation

Reassessment Task 2: Implementation

Requirements

This is an individual assignment. In this assignment you are expected to implement a data model for Merrill Hand Tools Ltd.

Your database should be suitable to keep an inventory of the tools the company manufactures and sells. It should also support relevant business processes in the organisation, including manufacturing and sales and fulfilment of orders. Therefore, the database should record suitable and enough data regarding raw materials used in the company, and the levels required to manufacture each tool; customers and suppliers; production lines and their scheduling; orders of raw materials received from suppliers; orders placed by customers; test conducted on tools, as well as faults reported by purchasers; among others.

An initial Entity-Relationship model is provided below. You are expected to determine the tables to be implemented, create the tables, set primary keys, foreign keys, data types and other constraints correctly, and help make the constraints manageable. Create sample records, and create and run a number of queries that address the information needs of Merrill Hand Tools Ltd.

At this stage of the development, your database is not required to consider any human resources operations (hiring, scheduling, training of staff, etc.); marketing operations (managing contact with customers, distributing catalogues, etc.); design/redesign of tools; aspects of finance and accountancy are also out of scope.

ONE file should be uploaded to Blackboard including the following:

  • Table creation statements (SQL).
  • Data dictionary information (primary keys, foreign keys, constraints).
  • Commentary – justify changes made to the original E-R Diagram, assumptions made, notes, etc.
  • SQL code for querying the database created (see list below), plus screenshots of the results.

Submission details

This is an individual assignment. Your report should be submitted electronically through the module’s Blackboard site. You will find a link under Reassessment for this submission.

The normal regulations for the submission of work apply, for further details see: http://students.shu.ac.uk/rightsrules/regs.html

Deadline: Thursday 19th May 2022 (3:00pm)

 

 

Entity – Relationship Diagram

 

 

 

Reassessment Task 2 – Marking Scheme

Report

  Not included in the report.
(0 points)
Incomplete and/or notational errors; and/or does not support the company’s needs.
(1.5 points max)
Supports the company’s operations to some extent. Opportunities missed to address all required busines processes.
(3 points max)
Company needs are clearly understood and reflected in the documentation produced.
(5 points max)
Table creation statements.        
Data dictionary.        

 

SQL queries

  Functionality not demonstrated.
(0 points)
Some understanding shown of this functionality. Implemented to some extent.
(2.5 points max)
Understood and implemented. Would comprehensively support the company’s business processes.
(5 points max)
1.      List of tools produced by the company.      
2.      List of suppliers.  

 

   
3.      List of customers.  

 

   
4.      Current inventory levels of finished tools.      
5.      Current inventory levels of raw materials.      
6.      List all the tests conducted on a given tool in the current catalogue.      
7.      List of raw materials required to produce a given tool.      
8.      Create a new order for an existing Customer.      
9.      Add 4 different items to the order created in question 8. -two of them must NOT be in existence, i.e. not part of current inventory of finished tools.      
10.  Calculate and show the cost of each line in the order updated in question 9.      
11.  Calculate and show the total cost of the order updated in question 9.      
12.  Schedule the production line for the tools requested in the order updated in question 9 that are currently out of stock.      
13.  For one of the tools scheduled in question 12, show the list of raw materials and quantities required for its manufacturing.      
14.  Assume raw materials are being delivered by one of the company’s suppliers, create a new Delivery of raw materials order/note.      
15.  Update the inventory of raw materials by adding the raw materials delivered in question 14.      
16.  Assume that the manufacturing of the tools scheduled in question 12 has finished. Add these tools to the inventory of finished tools.      
17.  Using the serial number assigned to one of the tools added to the inventory of finished tools (question 16), create a shipment for the order created in question 8.      
18.  Assume one the tools added to the inventory of finished tools (question 16), has been purchased and is now returned due to a fault. Create a new Problem Report for it.