In this case, we will design a Database to improve a process for Specification Limit Violation (SLV) review; i.e.; the process of resolving violations of quality specifications of manufacturing materials.

The company Pretty Good Manufacturing (PGM) Inc., an equipment maker, requires that all components that are assembled into the equipment it builds and sells meet minimum specification limits. Hence, it inspects these components; either upon receiving them from its suppliers or after making them on-site.

When a part does not meet specification limits, the part inspector files a Specification Limit Violation (SLV). SLVs are recoded in an Excel file, which will be shared by all people involved in the process. For each SLV, the following data is stored:

• Employee ID of the inspector filing the SLV.
• Date and time of the filing.
• Purchase Order ID of the failed part (only when it was not made on-site).
• SKU (part ID) of the failed part.
• Number of disqualified units.
• Description of the violation.

An example of SLV filing is shown below:

Inspector	Date	Time	Purchase Order	SKU	Number of units	Description
E12034	1/13/2017	12:00:01	P02017001	P2596	10	Wrong color

Since the company is a defense contractor, its decisions on how to deal with SLVs are subject to regulatory constraints:

• SLVs can be dealt with in only one of four ways: 'use as is,' 'return to supplier,' 'rework on site,' or 'scrap.'
• Several parties must sign off on the decision: the Quality Assurance group, Purchasing (if the item was, in fact, purchased), Manufacturing Engineering (the folks running the production line) and Product Design.

Since the company typically orders or manufactures new parts shortly before they are needed in production the company tries to keep the inventory in its warehouse low SLVs should be dealt with fairly rapidly so as to not endanger upcoming production runs and hence, risk contractual obligations to deliver its goods.

To facilitate both the defense contracting decision-making constraints and the generally short periods prior to production, PGM has instituted SLV resolution meetings on Mondays and Wednesdays. During those meetings, representatives of the four involved parties discuss outstanding SLVs and try to resolve them.

This meeting-based resolution process, however, has some serious problems. To start, most SLVs can be dealt with on a party-by-party basis. For instance, Manufacturing Engineering can often make its decision independent of the other parties. Similarly, Product Design often only has to formally agree with other parties' decision because it has no real input itself. Hence, many SLVs can already be substantially addressed prior to SLV meetings.

Interestingly, PGM is currently using a shared Excel file to handle the SLV filing process. All users can read the file and revise the file to make resolution suggestions at the same time. Unfortunately, there is no suggested processing order for the SLV filings. Users may 'add' their comments to other people's comments which are already there, thereby frequently causing other people's comments to be overwritten. Please see an example below.

Inspector	Date	Time	Purchase Order	SKU	Number of units	Description	Decisions	Comments
E12034	1/13/2017	12:00:01	P02017001	P2596	10	Wrong color	use as is	Purchasing: We can reorder the parts according to the return policy with the supplier. Manufacturing: We can still use it because the color of this part cannot be seen in our product.

Another problem is that people's actions on the Excel file are not individually tracked and stored; i.e., instead of adding each SLV action separately, every new one overwrites the existing status. In the above example, only the most recent decision is shown in the file.

Two issues are particularly problematic. One is that because the company has regular SLV meetings, several of the involved parties do not even look at SLVs before they come to the meeting. This is problematic because often, once at the meeting, they do not have the needed information to resolve the issue and hence, SLV resolution must be postponed. This is particularly problematic on Wednesdays since the next SLV meeting is five days away.

The other problem is that SLVs are currently not prioritized and people have little insight into which SLVs must be resolved quickly and which ones can wait a little. This can be costly, especially if PGM misses a contractual obligation to deliver product or when it has an expensive piece of equipment idling because it did not resolve an SLV problem on time.

Finally, the folks running the SLV meetings have noted that having people sit in meetings where they are no longer (or not at all) needed is a costly exercise, especially with meetings as frequently as the SLV ones.

Suppose you are asked by PGM to help them increase the efficiency of the SLV process. Your tasks are:

8.1. Identify all the problems that can be solved by database and briefly describe solutions to the problems. E.g., what information do they need and what data are needed to generate the information, etc. Note, your solution should not violate the regulatory constraints mentioned in the case. For example, you can NOT assume the case can be resolved by one person in the company. Your submission to this question is one or two paragraphs that summarize the problems, data and information. (10 points)