Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ITLS6010 Logistics and Supply Chain Management Systems

Requirements

Part A- Design the WMS Database

The requirements are as follows:

1. Create the initial diagram in Lucidchart and consider the business needs carefully to make sure that your design is going to be useful for supporting operational activities.

2. Create the database and the tables in the SQL Server environment. All commands used must be included in a.sql file.

3. Create up to five rows of dummy data for tables for which you have no data. You may create additional rows of dummy data if you think this will help you with query outputs for Part 2. Import data for all the tables in the SQL Server environment from .csv files.

4. Set up the PK-FK relationships between tables.

5. Create the database diagram in the SQL Server. Your diagram in SQL Server may be an improvement on the one in Lucidchart if you make additional improvements to your tables after you start working in the SQL Server environment.

6. Complete Part 1 of the assignment.

Part B - Design the DQL Queries

It is time to use your database to address business needs. Design four queries that will generate results that would be useful for reporting purposes. These queries must be added to the .sql file created for Part 1. The queries must address any four of the following monitoring concerns:

● Order picking accuracy: This metric shows how accurately warehouse employees pick products for orders.

● On-time Shipments: Shipments reaching customers on time is a critical success metric for warehouses. It's important on its own because it reflects if the warehouse doing its job right. However, late shipments also create hidden costs and difficulties elsewhere in the business. They cause customer service calls and complaints. They cause package tracking and other wastes of time. Ultimately, late shipments can damage your brand and cause customers to defect.

● Receiving efficiency: Number of supplier orders received per hour over the course of a week assuming 8-hour workdays

● On-time receipts from suppliers: The percent of orders received from a supplier per on-time requirements.

● Lines put away per hour: The number of orders put away per hour during an eight-hour working day.

● Average warehouse capacity used: The number of inventory storage locations with an on-hand quantity greater than zero, is divided by the total number of inventory storage locations.

● Inventory count accuracy by location: Are the inventory counts accurate in each location? This is another stealth issue that is more important than it looks. If there are fewer items in a bin than the system says there should be, that might indicate theft or unreported damage. The results of miscounted inventory include unforeseen stockouts and fulfilment problems that negatively customer attitudes.

● Fill rate per line: The fill quantity is calculated as the difference between the individual shipment line items after the completion, compared to the original requested shipment instruction line item quantity. The difference is then divided by the requested quantity and aggregated for all orders within the defined benchmarking period.