(I am stuck on creating an ERD because I'm not sure about the...

(I am stuck on creating an ERD because I'm not sure about the entities and their attributes. Any other help on this process would be greatly appreciated.)

This is the problem I'm working on:

[

Database Design Case Study: 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:

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 current 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. Therefore, 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.

Another problem 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.

Also, 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.

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. Two issues related to the shared Excel file are particularly problematic. One is that 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.

The other 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.

 

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

7.1. Summarize all the problems and briefly describe how a database can be designed to solve these problems. 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.

7.2. Design and implement a relational database, which can hold all of the necessary data to support the information requirements proposed in 7.1. A reasonable complexity for this database design will be 3-8 entities. You can start with some key nouns in the case such as SLV_Case, Meeting, Part, etc. Include any assumptions you made about the case (if there is any), an ERD, and an MS Access File

]

(Tutor, I'm not asking you to make an MS Access file or even answer the questions exactly. I also removed the last question asking to write queries from the database. I am including this information here so you can understand what I'm being asked to do. I simply need help identifying the entities, attributes, and relationships so I can make a reasonable ERD.)