complete all parts
BA 574 Exam (For Graduate Students) Maximum: 100 points Important! Read before you start the exam: · This is an individual exam. Please do not discuss about the exam or share your answers with other students. Similar answers from two students will be considered as cheating and the consequences will be severe. · You can use the textbook or materials on our course website to help you answer the questions. · There are seven questions in total. Some questions may have multiple subquestions. Please read the questions carefully and make sure you have answered all subquestions. · Except for Question 7.2, answers to all other questions should be combined into one word document. Your submission should be the word document and an MS Access file (for Question 7.2). · If you have any questions about the exam, please email the instructor. I can clarify the requirements of questions. However, I will not help you answer the questions in any way. · Please submit before the deadline. Late submission of the exam will not be accepted. · Good Luck! 1. Data and information (5 points): 1.1. Please use a few sentences to describe the relationship between data and information. 1.2. As a business analyst, why do we need to differentiate data and information? 2. NoSQL database (5 points): 2.1. Please describe the differences between SQL and NoSQL databases. 2.2. Please also explain how a company should make a decision about which one to choose. 3. ERD design (8 points): 3.1. Suppose you design a database to store data about movies and actors. Write down the business rules between actors and movies. Your business rules should be reasonable and precise. (2 point). 3.2. Draw an ERD based on your business rules in 3.1. Have at least four attributes for each entity. Bridge entities (you do NOT need to have four attributes for bridge entities) should be used if needed. PKs and FKs should be identified. Your attributes should follow good naming convention. (6 points). 4. Based on the following ERD, answer the three questions. (10 points) 4.1. Design SQL code to create the two tables with constraints of primary key and foreign key (if needed). Have at least four attributes (including primary and foreign keys) for each entity. Your attributes should be reasonable for the entity, have appropriate data types and constraints, and follow good naming convention. (7 points) 4.2. Please give 2-3 rows of sample data for each table. Submit the screenshots of your tables (1 point) 4.3. Use the sample data in 4.2 to explain what is referential integrity. (2 points) 5. Suppose you are hired as a business analyst for a basketball team and you are responsible for designing a database to record data that can help the team manager to improve the training schedule (e.g., what, when and how to practice) of your team. Please briefly describe the process that you plan to carry out in the project. Please provide a list of key steps and the time order of these steps. You do NOT need to give details of database design (e.g., entity, attributes, relationships, tables, etc.). Your answer to this question should be about 5-10 sentences (10 points). 6. Normalization (12 points). Download the “Q6 normalization.xls” file. This is an excel file that a clinic uses to keep track of patient visits and insurance claims. Please answer the following questions based on the file. 6.1. Use normalization techniques you learned in this class to normalize this table. Use the format provided in the example to describe your resulting tables (No SQL or MS Access is required for this question). For each table, give the name of columns, PK and FK (if any). Note, your relationship should be consistent with the data in the file. For example, patient 1001 uses different insurance plans for different appointment. (10 points) Example: Table 1: PatientID (PK), PateintName, PatientPhone, PatientAddress, PatientEmail 6.2. Please explain the advantages and disadvantages of the normalized tables. (2 points) 7. Database Design Case Study (50 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: 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. Your submission to this question should be one or two paragraphs that summarize the problems and information requirements for solving the problems. (5 points) 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. Your submission to this question includes any assumptions you made about the case (if there is any), an ERD, an MS Access File. All columns should be reasonable for the table, have appropriate data types and constraints, and follow good naming convention. All tables should be correctly implemented and each table has at least three rows of sample data. (ERD 15 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.) 7.3. Design at least Five SQL queries to show the usefulness of your database. Among the five queries, at least two should be multiple-table query and at least two should use aggregate functions. 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 7.1. Your submission will be similar to the example below. (20 points, 4 points each). Example: SQL Code 1: Select ** from *** where ***. This is a query that shows all comments for unresolved SLV cases ranked by their priorities. This query will be run by SLV meeting Coordinator one day before the meeting. The SLV