Context for Assignments and Project Assignments and projects are structured so that they build upon each other: Assignment 1 is asking you to demonstrate that you can create a simple application that accesses a DB You can use a simple application program for this purpose, such as Javascript of Java However, it is recommended that if you have experience with web services, you should develop your application as a web application invoking web services for back-end process as Assignment 3 and project require the use of web services. Assignment 2 expands the functionality of the simple application of Assignment 1 and uses triggers and transaction management Again, although you may develop your software as a simple application, it is recommended that you use web services (assistance will be provided) Assignment 3 further expands the functionality of your software in Assignment 2 and it is requiring that you use web services for back-end processing. o Project expands functionality requirements of Assignment 3 and asks that your web services implement 2-phase commit in distributed environment in which a transaction requires updates performed on independent DBs. Al may be implemented as one application program, such as a Java or Javascript program, or it may be implemented based on web services. Considering that the last assignment will require implementation based on web services, you strive to architect your Assignments 1 and 2 based on web services also. However, if you do not have experience with web services, it is recommended that you do not attempts Al using web services as we shall be providing assistance in using web services during the first six weeks. Learning Outcomes Analyze user requirements for ER modeling Demonstrate competence in ER modeling for DB design Create a DB to support an application Create an application that accessed and manipulates a DB Specifications Data Requirements Consider a company that produces and sells products that are stored in a warehouse. The company receives orders for their products that need to be processed for its completion. The company needs to keep track of the following information about parts: partNo ... unique (PK) - partName partDescription - current Price qty... quantity of a part in stock (also referred to as Quantity On Hand (QOH)) Information that needs to be known about clients: clientCompld ... ID of the client comp clientCompName clientCity - clientComp Password 1 Money Creed Information about purchase orders (Os) Submission requirements Your submission should have the usual packaging (front page, TOC (Table of Contents), etc.) and contain the following information: falo clieni Compi - status ID of the client comp. datePO... date of the purchse order Fach Purchase Order (PO) has a number of lines, each one denoting an order for a number of units of one part. Thus, for each part, the part maamber and its price need to be stored together with the quantity in order. Lines are assigned unique numbers starting at one for the first line, 2 for the second and so on. As prices for parts are subject to change the purchase price for each part on an order line must be stored in the DD also Functional Requirements Assume that the application that you are developing is for a company that offers parts for sale. Your task is to develop an application program that will enable a company agent to manage parts and client information. More specilically, your lask is to create a Li, with supporting software components it invokes, that enables a company agent to Insert, retrieve, and delete parts (for retrieval, either a specific part or all parts); update parts as specified helow Inseri, retrieve, and delete clients (for retrieval, either a specific part or all parts) In this assignment you need no manage orders as it will be the task for A2. Section describing your ER model developed with MySQL Workbench, including CER modelliagram Sql scripts to create DB tables Sql scripts to populate the table with sample values. Section descriving your software organization Section that identifies under which situation condition the DB may be become inconsistent incorrect. For instance, if your application program execution is aborted while it is executing ic.g. when the agent is processing a PO).can the DB state become inconsistent or incomect (e.g., when qly in stock for a part becomes incorrect? Listing of DB server and software frameworks you used for development and for which parts (eg. Angular for UT) SQI. script file containing statements used to create the tables and also to input initial values into Detables information about clients and parts) SQL script files for any triggers/stored procedures (if used) If you are using web services, provide specification description that is of sufficient detail to be used to test'invoke endpoints of your web services with POSTMAN Additional instructions re FCS Git and Reaime file will be here For an appeal of your grade, see the last paragraph in the clow section ou rubric. (if not Company Agent UI You may assume that there is only one ugent operating at a time fome ageni logged-in of a time). UI for the company agents provides for log-in t logged-in already)-log >-login is by username and password - there may be a number of agents with each one having a unique username and password. Once the user is logged-in, she he is repeatedly offered the following choices Liparts Information about parts is shown, including quantities on stock Change price of a part and/or quantity on hand fin stock) To change a price and quantity en hand the user supplies the part number, the new price, and quantity on hand. The system retrieves in urmation about the part and displays it to the user together with the old and new prices quantities and asks the user to confirm the change - if confirmed the new price of the part and the quantity on hand are updated in the database. it. List information about all parts The system lists info about all parts, iv. List information about all clients The system Lists info about all clicats Additional Requirements MySQL DBMS should be used, either using the FCS DB server or cloud-based one Dr Naming Conventions - Your DB must be such that the names of your tables and of the columns must end with the last three digits of your student ID - In your software, the names of your methods/functions procedures must end with the last three digits of your student ID and the same applies about any parameter of a method procedure function Context for Assignments and Project Assignments and projects are structured so that they build upon each other: Assignment 1 is asking you to demonstrate that you can create a simple application that accesses a DB You can use a simple application program for this purpose, such as Javascript of Java However, it is recommended that if you have experience with web services, you should develop your application as a web application invoking web services for back-end process as Assignment 3 and project require the use of web services. Assignment 2 expands the functionality of the simple application of Assignment 1 and uses triggers and transaction management Again, although you may develop your software as a simple application, it is recommended that you use web services (assistance will be provided) Assignment 3 further expands the functionality of your software in Assignment 2 and it is requiring that you use web services for back-end processing. o Project expands functionality requirements of Assignment 3 and asks that your web services implement 2-phase commit in distributed environment in which a transaction requires updates performed on independent DBs. Al may be implemented as one application program, such as a Java or Javascript program, or it may be implemented based on web services. Considering that the last assignment will require implementation based on web services, you strive to architect your Assignments 1 and 2 based on web services also. However, if you do not have experience with web services, it is recommended that you do not attempts Al using web services as we shall be providing assistance in using web services during the first six weeks. Learning Outcomes Analyze user requirements for ER modeling Demonstrate competence in ER modeling for DB design Create a DB to support an application Create an application that accessed and manipulates a DB Specifications Data Requirements Consider a company that produces and sells products that are stored in a warehouse. The company receives orders for their products that need to be processed for its completion. The company needs to keep track of the following information about parts: partNo ... unique (PK) - partName partDescription - current Price qty... quantity of a part in stock (also referred to as Quantity On Hand (QOH)) Information that needs to be known about clients: clientCompld ... ID of the client comp clientCompName clientCity - clientComp Password 1 Money Creed Information about purchase orders (Os) Submission requirements Your submission should have the usual packaging (front page, TOC (Table of Contents), etc.) and contain the following information: falo clieni Compi - status ID of the client comp. datePO... date of the purchse order Fach Purchase Order (PO) has a number of lines, each one denoting an order for a number of units of one part. Thus, for each part, the part maamber and its price need to be stored together with the quantity in order. Lines are assigned unique numbers starting at one for the first line, 2 for the second and so on. As prices for parts are subject to change the purchase price for each part on an order line must be stored in the DD also Functional Requirements Assume that the application that you are developing is for a company that offers parts for sale. Your task is to develop an application program that will enable a company agent to manage parts and client information. More specilically, your lask is to create a Li, with supporting software components it invokes, that enables a company agent to Insert, retrieve, and delete parts (for retrieval, either a specific part or all parts); update parts as specified helow Inseri, retrieve, and delete clients (for retrieval, either a specific part or all parts) In this assignment you need no manage orders as it will be the task for A2. Section describing your ER model developed with MySQL Workbench, including CER modelliagram Sql scripts to create DB tables Sql scripts to populate the table with sample values. Section descriving your software organization Section that identifies under which situation condition the DB may be become inconsistent incorrect. For instance, if your application program execution is aborted while it is executing ic.g. when the agent is processing a PO).can the DB state become inconsistent or incomect (e.g., when qly in stock for a part becomes incorrect? Listing of DB server and software frameworks you used for development and for which parts (eg. Angular for UT) SQI. script file containing statements used to create the tables and also to input initial values into Detables information about clients and parts) SQL script files for any triggers/stored procedures (if used) If you are using web services, provide specification description that is of sufficient detail to be used to test'invoke endpoints of your web services with POSTMAN Additional instructions re FCS Git and Reaime file will be here For an appeal of your grade, see the last paragraph in the clow section ou rubric. (if not Company Agent UI You may assume that there is only one ugent operating at a time fome ageni logged-in of a time). UI for the company agents provides for log-in t logged-in already)-log >-login is by username and password - there may be a number of agents with each one having a unique username and password. Once the user is logged-in, she he is repeatedly offered the following choices Liparts Information about parts is shown, including quantities on stock Change price of a part and/or quantity on hand fin stock) To change a price and quantity en hand the user supplies the part number, the new price, and quantity on hand. The system retrieves in urmation about the part and displays it to the user together with the old and new prices quantities and asks the user to confirm the change - if confirmed the new price of the part and the quantity on hand are updated in the database. it. List information about all parts The system lists info about all parts, iv. List information about all clients The system Lists info about all clicats Additional Requirements MySQL DBMS should be used, either using the FCS DB server or cloud-based one Dr Naming Conventions - Your DB must be such that the names of your tables and of the columns must end with the last three digits of your student ID - In your software, the names of your methods/functions procedures must end with the last three digits of your student ID and the same applies about any parameter of a method procedure function