This is broken into three sections: Part A, is question based, whereas Part B relies on a query, and questions based on the query. The third part is based on using a database, writing code, and explaining parts of the code. Please, no plagiarism, it must be at a university standard. See below, attached is the script to create the database for the Assignment 10 SQL portion of the assignment.Assignment Week 10Part aSuppose you are a manufacturer of product ABC, which is composed of parts A, B, and C. Each time a new product is created, it must be added to the product inventory, using the PROD_QOH in a table named PRODUCT. And each time the product ABC is created, the parts inventory, using PART_QOH in a table named PART, must be reduced by one each of parts A, B, and C. The sample database contents are shown in Table P10.1Table P10.1 The Database for Problem 1Table name: PRODUCT Table name: PARTPROD_CODEPROD_QOH PART_CODEPART_QOHABC1,205 A567 B498 C549Given that information, answer the following questions.1. How many database requests can you identify for an inventory update for both PRODUCT and PART?2. Using SQL, write each database request you identified in problem 1. 3. Write the complete transaction(s) all of the above in a batch transaction. Part b Based on the following query: SELECT P_CODE, P_QOH*P_PRICEFROM PRODUCTWHERE P_QOH*P_PRICE > (SELECT AVG(P_QOH*P_PRICE) FROM PRODUCT) 1. What is the likely data sparsity of the P_QOH and P_PRICE columns? 2. Should you create an index, what would the index column(s) be, and why should you create that index?(50 Points)Assignment Week 10 SQLAccomplish the following exercise and copy/paste the results into your week 10 assignment submission Word document. Using ch10_abc_markets database, write the SQL code to select the invoice year from inv_date with the column name invoiceYear, the invoice month from inv_date with the column name invoiceMonth, the invoice day from inv_date with the column name invoiceDay, and the sum of the invoice total with the column name invoiceTotal from the invoice table. Group the results by year, month, and day using the ROLLUP subclause. QUESTION: What does the rollup subclause provide in terms of grouping and how does it differ from the CUBE subclause? Using ch10_abc_markets database and line table, write the SQL code to select the invoice number, line number and the sum of the line price as sumLP. Group the results using grouping sets of invoice number and line number to show the sumLP by invoice number order. QUESTION: What is the purpose of the GROUPING SETS subclause and what is its primary use?Once you have accomplished that action, paste the script into your assignment submission document for this part of your assignment. (25 Points)

Assignment 2—Class and State DiagramsPurposeThe purpose of this assignment is to continue building our ITOT case study models by using the classes and their properties from Assignment 1 and creating a class diagram and Class and Relationship Analysis table for the relationships among classes. In addition, we will build a state diagram for the ShopppingCart class.Assignment InstructionsIn this assignment you will create a class diagram and the Class and Relationship Analysis table for all of the classes you identified in Assignment 1 and a state diagram for your ShoppingCart class. Make certain that you include all of the classes and any additional classes or corrections to your Assignment 1.For the Analysis Specification you will add the following completed sections:Sections 4, 4.1, 4.2 (adjustments if necessary), 4.3, 5, 5.1, 5.2Directions1. Read and review the Quick Resources at the end of this Assignment.2. Create a UML class diagram with attributes, associations, multiplicity constraints, and operations using your CASE tool. Add your name and course number directly on your diagram.2.1 Section 4.1. Use the classes you identified from Assignment 1 and create a UML class diagram that includes all of your classes. Place your class diagram in Section 4.1. Add attributes to each class and relationships between classes. Attributes are properties of classes and indicate the type of data expected like text, numbers, etc.. For example, a name attribute would be text type data. Relationships establish some linkage between classes and can include associations, generalization, aggregation, and composition . Associations are the most common relationships. See the example class diagram http://www.uml-diagrams.org/examples/online-shopping-domain-uml-diagram-example.html for an example of a ShoppingCart class diagram similar to our model. As you add a class using the CASE tool, if you right click on the class and choose the option to Add attributes with getter and setter methods. Your CASE tool will automatically add them as methods. This saves considerable time and effort.2.2 Section 4.1. Include at least 10 named relationships with multiplicity constraints on associations to other classes. Names and multiplicity constraints are required on all associations. The shape of the line used indicates the type of relationship for generalization, aggregation, and composition relationships so you do not need names here. Association names are often verb phrases like “purchases” from “A Customer purchases a ScheduledCourse”. To determine the associations, you can also use the same technique as the noun analysis, but do verb analysis instead. To help you discover the associations and multiplicity constraints complete the Class and Relationship Analysis table below and add it to Section 4.1 with your class diagram.Identify the relationships among the classes. Associations are bidirectional and so we must consider them in both directions with multiplicity constraints as well. Using a table for the initial analysis will make it easier to create a class diagram. For each class ask the question "What other classes is the class related to?" or "What is the relationship between these two classes?" Once you identify the classes, then for each class pair ask "What is the relationship?" Multiplicity constraints are needed on both ends of the association and may be zero or one (0..1), one and only (1), zero or more (0..*), or many (*) ,etc. As you can see below there are two sentences with the multiplicity constraints describing the association in both directions. In this example the two classes are Instructor and Course. The relationship is "teaches". On our diagrams we usually only show the relationship name in one direction and mentally revers the verb phrase in the other direction. See Figures 3.12 and 3.15 in your textbook for an example of a class diagram with named associations and multiplicity constraints.2.3 Add multiplicity constraints to each end of a relationship between classes. UML multiplicity constraints include the pairs 1..1, 1..*, *..*, 0..1, as well as others.2.4 Section 4.1. Add your class diagram and the Class and Relationship Analysis table to Section 4.1 of your Analysis Specification. Make certain that you continue from your Assignment 1 submission. Each week we will add to the Analysis Specification document until we finally complete it. So just take your previous assignment and add this assignment to it.2.5 Section 4. Complete Section 4 of the Analysis Specification with a brief introduction to the contents of the section. You can now include this since you have completed your class diagram.2.6 Sections 4.2 and 4.3. Make any necessary adjustments to Section 4.2 that you completed in Assignment 1 and discuss your changes in Section 4.2 which should agree with and support Section 4.1.2.7 Section 4.3. Complete Section 4.3 of the Analysis Specification with a discussion of your class diagram.3. Create a UML state diagram for the ShoppingCart class with your CASE tool. Add your name and course number directly on your diagram. Make certain that you name the transitions and add guards as necessary.3.1 Sections 5.1 and 5.2. Add your UML state diagram along with a discussion to Sections 5.1 and 5.2 of your Analysis Specification. See the article, ENTD321 Lesson 2 State How to Create a State Diagram.pdf, below on creating a state transition diagram before you start your diagram.3.2 Section 5. Complete Section 5 of your Analysis Specification with an introduction the section contents.Submission Instructions1. Submit your updated Analysis Specification as a Word file. 2. When you submit your Word file, use your name as part of the file name, e.g., ENTD321Assignment2_FirstNameLastName. Also make certain that your name and course number is at the top of your document.Please use APA formatting and in text cititation Please No Plagiarism