8. Database Design Case Study (45 points): 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 Descritplon E12034 1/13/2017 12.00.01 PO2017001 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 productionthe 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. Impedar The Purchase Order SKU Number of Unit Dekan decidon Carmen Purchasing we aan ander the parts according to the natum poly with the supplier Manufacturing Wind weet bus the colorat this part cannot been an in our product 12031 1/10/2017 12000 PO2017001 10 Wrangler 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 SL Vs 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 SL V 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 SL V 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) 8.2. Design and implement a relational database, which can hold all of the necessary data to support the solutions proposed in 8.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 SLX Case. Meeting, Part, etc. Your submission to this question includes an ERD, an MS Access File. All tables should be correctly implemented and each table has at least three rows of sample data. (ERD 10 points, MS Access implementation 10 points. Paste your ERD in the word document and submit your Access file as an attachment. The font in the ERD should be readable.) 8.3. Design at least Five queries to show the usefulness of your database. Among the five queries, at least two should be multiple-table query and at least one should use an aggregate function. For each query, please provide your SQL code and a short narrative that explains the information retrieved by the query, it is going to be used by whom and when, and why it helps solve the problems identified in 8.1. Your submission will be similar to the example below. (15 points, 3 points each). 8. Database Design Case Study (45 points): 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 Descritplon E12034 1/13/2017 12.00.01 PO2017001 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 productionthe 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. Impedar The Purchase Order SKU Number of Unit Dekan decidon Carmen Purchasing we aan ander the parts according to the natum poly with the supplier Manufacturing Wind weet bus the colorat this part cannot been an in our product 12031 1/10/2017 12000 PO2017001 10 Wrangler 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 SL Vs 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 SL V 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 SL V 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) 8.2. Design and implement a relational database, which can hold all of the necessary data to support the solutions proposed in 8.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 SLX Case. Meeting, Part, etc. Your submission to this question includes an ERD, an MS Access File. All tables should be correctly implemented and each table has at least three rows of sample data. (ERD 10 points, MS Access implementation 10 points. Paste your ERD in the word document and submit your Access file as an attachment. The font in the ERD should be readable.) 8.3. Design at least Five queries to show the usefulness of your database. Among the five queries, at least two should be multiple-table query and at least one should use an aggregate function. For each query, please provide your SQL code and a short narrative that explains the information retrieved by the query, it is going to be used by whom and when, and why it helps solve the problems identified in 8.1. Your submission will be similar to the example below. (15 points, 3 points each)