Project Background
Merrill Hand Tools Ltd (MHT) is a family-run business based in Sheffield that manufactures a range of hand tools, from general tooling to specialist woodworking tools and agricultural implements.
MHT was formed by Mike and Linda Merrill in 1980 who brought a vast amount of experience in Forges, both in Sheffield and in other countries including Germany.
The company’s family connection with forging goes back to two generations to Robert Merrill, who was one of Sheffield's "Little Mesters", he worked independently producing hand forged cutlery items.
MHT moved premises in 1990 and the opportunity was taken to re-organise production procedures to optimise efficiency, in order to produce high quality hand tools consistently and to the relevant applicable standards. In 2000 it was found necessary to extend the premises to accommodate increasing business and machinery needed to fulfil orders. Currently, MHT is organised into three divisions: Product Development, Manufacturing, and Marketing and Sales. Each division is headed by a vice president, who reports directly to the CEO (Mike Merrill).
Product Development Division
The Product Development division is responsible for designing and testing new and redesigned tools. The division employs tools designers. Once a design/redesign is complete, one or two prototypes are built. The prototypes are inspected for deformations (for steel based tools), visual damage, coating inspection, quality control and pre-production anomalies. Feedback from the testers is used to refine product designs and to make decisions about which designs should actually be manufactured for mass marketing.
Manufacturing Division
The Manufacturing division is responsible for producing product for market sales. Manufacturing procures its own raw materials and manages its own operations, including human resources (hiring, scheduling, etc.) and assembly line management. Manufacturing maintains the inventory of tools ready for sale.
Marketing and Sales Division
MHT sells directly to individuals and hardware shops. Marketing and Sales employs 5 members of staff who are responsible for making managing contact with customers and hardware shops, distributing catalogues, visiting and/or calling potential resellers, and taking orders. Order accounting is also handled by Marketing and Sales; they also pass on shipping information to Manufacturing, which takes care of actual product delivery.
Current Information Systems
Current information systems at MHT are a collection of computers and applications that have grown up with little planning. The Product Development division relies primarily on stand-alone design workstations. In contrast, testing records are kept and analysed manually. Product Development employs product designers and clerical support staff, but no information systems staff. Attempts to have clerical staff develop simple database applications to store data about testers of new and redesigned tools and the results of those tests have proved futile. It has become evident that Product Development needs information systems professionals, and although the division is willing to hire IT staff, corporate management has decided to centralise the IT staff, rather than add to a decentralised model.
Manufacturing uses a stand-alone server to track purchases and inventory levels of raw materials, staff scheduling, manufacturing line scheduling, and finished product inventory. Each of the applications running on the server were custom-written by outside consultants in FoxBASE, many years ago; the most significant maintenance they have had was when they were ported from the department’s original minicomputer to the server, about 15 years ago.
The data used by a Manufacturing application are contained in tables that do not share information with any of the other applications.
Manufacturing employs one programmer that knows the FoxBASE code (who will be retiring shortly) and a system administrator. Although the programmer is talented, the most he can do is fix superficial user interface issues and repair corrupted data files; he was not part of the original program development, which was poorly written and even more poorly documented. The applications no longer meet the needs of the Manufacturing division and management has determined that it is not cost effective to write new applications to access the existing data files.
Marketing and Sales, which wasn’t computerised until late 1990’s, has a local area network consisting of one server and 5 workstations. The server provides shared applications, such as word processing and spreadsheets. It also maintains a marketing and sales database that has been developed using a PC-based product. The database suffers from several problems, including a limit to the number of simultaneous users and concurrency control problems that lead to severe data inconsistencies. The marketing and sales database was developed by two IT employees in the division, both of whom have since left the company. No current staff understands the software. Regardless of the amount of time spent trying to maintain the database, inaccurate data continue to be introduced. The sales database is not connected to the Internet; therefore, salespeople must email their orders to the central office, where the orders are manually input into the database
Reengineering Project
MHT’s CEO has decided to undertake a major systems reengineering project. The overall aim of the project is to provide an information system that will better support product testing, analysis of sales patterns, control of the manufacturing process, and enhanced communications options throughout the organisation. New information systems will be based on a client/server model and include one or more databases running on an Internet-connected network of servers, workstations, and PCs. The ultimate goal is to create a self-service Web application for the company so that clients can place orders online.1
New Information Systems Division
The first step in the reengineering project is to establish an information technology division. This new division will also be in the company’s headquarters, along with the three existing divisions. To accommodate the new division, MHT will be extending their premises.
MHT is in the process of searching for a Chief Information Officer (CIO). This individual, who will report directly to the CEO, will manage the new division and be responsible for overseeing the reengineering of information systems that will handle all of the organisation’s operations, as well as the creation, implementation, and maintenance of the company’s Web app.
All current IT personnel (those who work for the Manufacturing, and Marketing and Sales divisions) will be transferred to the new IT division. The division will hire (either internally or externally) three professionals: a Lead Programmer (responsible for overseeing application development), a Database Administrator (responsible for database design and management), and a Lead Analyst (responsible for overseeing systems analysis and design efforts). The company will investigate various solutions for the development of the Web app to determine whether it should be done in-house or outsourced to a consulting firm.
Basic System Goals
The CEO has defined the following goals for the reengineering project:
Develop a company-wide data administration plan, which should include a requirements document detailing organisational functions that require technology support and the functions that the reengineered system will provide.
Provide an application roadmap that documents all application programs that will be needed to support corporate operations.
Investigate alternatives for developing and hosting a Web app that will allow online orders. Conduct a cost-benefit analysis of those alternatives.
Document all databases to be developed for the company. This documentation will include ER diagrams and data dictionaries.
Create a timeline for the development of applications and their supporting databases.
Specify hardware changes and/or acquisitions that will be necessary to support the reengineered information systems.
Plan and execute a security strategy for an expanded company network that will include both internal and external users.
Implement the planned systems.
Current Business Processes
To support the systems analyst in their assessment of MHT’s information needs, the CEO has asked all existing division heads to document the way in which information is currently processed. This documentation, which also includes some information about what an improved system should do, provides a starting point for the redesign of both IT processes.
Sales and Ordering Processes
MHT receives orders at its plant in two ways: by telephone directly from customers, or from members of the sales team who have visited customers in person. Orders from the remote sales staff usually arrive by email.
Each order is taken on a standard order form (Figure 2). If the order arrives by email, it will already be on the correct form. Telephone orders are written directly onto the form. Several times a day, a clerk enters the orders into the existing database. Unfortunately, if the sales office is particularly busy, order entry may be delayed. This backlog has a major impact on production line scheduling and thus on the company’s ability to fill orders. The new information system must streamline the order entry process, including online order entry and the direct entry of in-house orders.
The in-house sales staff has no access to the files that show the current finished-goods inventory. They are therefore unable to tell customers when their orders will ship. They can, however, tell customers how many orders are ahead of theirs to be filled and based on general manufacturing timetables, come up with an approximation of how long it will take to ship a given order. Therefore, another goal of the information systems reengineering project is to provide better company-wide knowledge of how long it will take to fulfil customer orders.
Manufacturing, Inventory, and Shipping Processes
The Manufacturing division occupies a large portion of MHT premises. The division controls the assembly lines, a storage area for finished goods, a storage area for raw materials, and offices for supervisory and clerical staff.
The manufacturing process is triggered when a batch of order forms is received each morning by the manufacturing office. The batch consists of all orders that were entered into the sales database the previous working day. A secretary takes the individual order forms, and completes a report summarising the number ordered by model (Figure 3). This report is then given to the Manufacturing Supervisor, whose responsibility it is to schedule what tools will be produced each day.
The scheduling process is rather complex, because the Manufacturing Supervisor must take into account previously placed orders, which have determined the current manufacturing schedule, and current inventory levels, as well as the new orders. The availability of raw materials also influences the scheduling decision. This is one function that MHT’s management understands will be almost impossible to automate; there is just too much human expertise involved to translate into an automatic process. However, it is vital that the Manufacturing Supervisor have access to accurate, up to date information about orders, inventory, and the current time schedule so that judgments can be made based on as much hard data as possible.
As finished tools come off the assembly line, they are packed for shipping, labelled, and sent to finished goods storage. Each shipping box contains one tool, which is marked with its model number, serial number, and date of manufacturing. The Shipping Manager, who oversees finished goods storage and shipping, ensures that newly manufactured items are entered into the shipping inventory files.
The Shipping Manager receives Customer Order Forms after the order report has been completed (photocopies of the order forms are kept in the Marketing and Sales office as backup.) The orders are placed in a box in reverse chronological order so that the oldest orders can be filled first. The Shipping Manager checks orders against inventory levels by looking at the Inventory Level report (Figure 4). If the manager sees that there is enough inventory to fill an order, then the order is given to a shipping clerk for processing. If there is not enough inventory, then the order is put back in the box, where it will be checked again the following day. Under this system, no partial orders are filled, because they would be extremely difficult to track -it is expected that the new information system should allow handling of partial shipments.
The Shipping clerk is given orders to fill. They create shipping labels for all tools that are part of a shipment. The boxes are labelled and set aside for pickup by the Parcel Delivery Company (PDC). The shipping clerk create PDC manifests, ensures that the items being shipped are removed from the inventory file, and returns the filled orders to the Shipping Manager. The orders are then marked as filled and returned to Marketing and Sales. The reengineered information system should automate the generation of pick-up lists, packing slips, and updating of finished-goods inventory.
MHT’s raw materials inventory is maintained on a just-in-time basis. The Manufacturing Supervisor checks the Line Schedule (Figure 5) and the current Raw Materials Inventory (Figure 6) daily to determine what raw materials need to be ordered. This process relies heavily on the Manufacturing Supervisor’s knowledge of which materials are needed for which tool model. Mike Merrill, MHT’s CEO, is very concerned about this process because the Manufacturing Supervisor, while accurate in scheduling the manufacturing line, is nowhere near as accurate in judging raw materials needs. The result is that occasionally manufacturing must stop because raw materials have run out. Mike would therefore like to see ordering of raw materials triggered automatically. The new information system should keep track of the raw materials needed to produce each tool and, based on the line schedule and a reorder point established for each item, generate orders for items when needed.
Raw materials are taken from inventory each morning as each manufacturing line is set up for the day’s production run. The inventory files are modified immediately after all raw materials have been removed from storage for a given manufacturing line. There is no way to automate the reduction of inventory; however, the new information system should make it very easy for nontechnical users to update inventory levels.
Product Testing and Support
Function MHT leadership team makes decisions about which tools to produce, based on data from product testing and fault reports. However, MHT would also like access to these data by model and serial number to make it easier to notify customers if a recall occurs. Management would also like summaries of the data by model sold, and customer details -e.g. region in the country where they are located. Fault reports (Figure 7) are taken by customer support representatives who work within the product testing division. These reports include the serial number and model experiencing problems, along with the date and type of problem. Currently, the problem descriptions are nonstandard, made up of whatever terms the customer support representative happens to use. It is therefore difficult to summarise problem reports to get an accurate picture of which tools are experiencing design and or manufacturing problems that should be corrected.
MHT would therefore like to introduce a standardised method for describing problems and faults, probably through a set of problem codes. The result should be regular reports on the problems reported for each tool model that can be used to help make decisions about which tools to continue, which to discontinue, which to redesign, and which to recall. When a problem report is received, the customer is directed to return the product to the store where it was purchased for an exchange (during the first 30 days after purchase).
Product test results are recorded on paper forms (Figure 8). After a testing period is completed, the forms are collated manually to produce a summary of how well a new tools or redesigned tools (e.g. when different alloys or materials are used for their manufacturing) performed. MHT would like the test results stored within an information system so that the.
Catalogue of Products
Task 1:
E-R Modelling
Requirements
Task one of the coursework aims to facilitate analytical and design practice using case studies. Using a standard notation, you are required to produce a data model for Merrill Hand Tools Ltd -the case study document is available on the module's Blackboard site.
To produce a data model that satisfies the needs of Merrill Hand Tools Ltd. you are expected to:
Analyse the case study in detail.
Apply a Top Down modelling approach to produce an Entity-Relationship (E-R) Diagram.
Apply a Bottom-Up modelling approach to identify functional dependencies and proceed to data Normalisation.
Finalise the data model by recombining the E-R Diagram and the normalised data structures.
You are expected to produce a report outlining the process followed including the outputs obtained at each stage. Assumptions should be clearly stated.
Task 2
Requirements
This is an individual assignment. In this assignment you are expected to implement the data model produced for Merrill Hand Tools Ltd.
Your database should be suitable to keep an inventory of the tools the company manufactures and sells. It should 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; deliveries of raw materials received from suppliers; orders placed by customers; test conducted on tools, and faults reported by purchasers, among others.
You are expected to determine the tables to be implemented. Create the tables, and correctly set primary keys, foreign keys, data types, and other constraints, and help make the constraints manageable. Create sample records, and create and run several 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.
You will be required to demonstrate your implementation to your tutors, who will ask you to the queries listed below.
Hire realcode4you expert to get help in any database design and implementation help. Send your request at realcode4you@gmail.com and get instant help with an affordable price.