Create an ER diagram and database with following requirements
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: part No ... unique (PK) - partName partDescription - currentPrice 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 moneyOwed Information about purchase orders (POS) poNo... -clientComplD... ID of the client comp. datePO ... date of the purchse order - status Each 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 number and its price need to be stored together with the quantity on 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 subjects to change, the purchase price for each part on an order line must be stored in the DB 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 specifically, your task is to create a UI, 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 below Insert, retrieve, and delete clients for retrieval, either a specific part or all parts) In this assignment you need not manage orders as it will be the task for A2. Company Agent UI You may assume that there is only one agent operating at a time (one agent logged-in at a time). UI for the company agents provides for log-in (if not logged-in already) - 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. i List parts Information about parts is shown, including quantities on stock. ii. Change price of a part and/or quantity on hand (in stock) To change a price and quantity on hand the user supplies the part number, the new price, and quantity on hand. The system retrieves information about the part and displays it to the user together with the old and new prices/quantities and asks the user to confim the change - if confirmed the new price of the part and the quantity on hand are updated in the database. iii. List information about all parts The system lists info about all parts. iv. List information about all clients The system lists info about all clients. Additional Requirements MySQL DBMS should be used, either using the FCS DB server or cloud-based one. 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. 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: part No ... unique (PK) - partName partDescription - currentPrice 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 moneyOwed Information about purchase orders (POS) poNo... -clientComplD... ID of the client comp. datePO ... date of the purchse order - status Each 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 number and its price need to be stored together with the quantity on 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 subjects to change, the purchase price for each part on an order line must be stored in the DB 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 specifically, your task is to create a UI, 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 below Insert, retrieve, and delete clients for retrieval, either a specific part or all parts) In this assignment you need not manage orders as it will be the task for A2. Company Agent UI You may assume that there is only one agent operating at a time (one agent logged-in at a time). UI for the company agents provides for log-in (if not logged-in already) - 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. i List parts Information about parts is shown, including quantities on stock. ii. Change price of a part and/or quantity on hand (in stock) To change a price and quantity on hand the user supplies the part number, the new price, and quantity on hand. The system retrieves information about the part and displays it to the user together with the old and new prices/quantities and asks the user to confim the change - if confirmed the new price of the part and the quantity on hand are updated in the database. iii. List information about all parts The system lists info about all parts. iv. List information about all clients The system lists info about all clients. Additional Requirements MySQL DBMS should be used, either using the FCS DB server or cloud-based one. 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
