Database Development

INTERNATIONAL PRODUCTION COMPANY An International Production Company (IPC) manufactures goods for customers. The IPC wants a computer based information system to store the data necessary to manage the customers, products and employees. The operations of IPC are along the following lines. IPC has many customers who are located internationally in a specific sales area. These sales areas (identified simply by an ID number) are services by a number of salespeople. Each salesperson has their name, telephone number and a special salesperson number recorded so they can be uniquely identified. The customer on the other hand is uniquely identified by a number, and also has their name, address, email address and telephone number recorded. Email address is used to send details of any special offers for loyal customers. Each customer can place many orders. Currently the salesperson records the details about the order number and the date it was created. The salesperson also writes down the number of a particular product required. The customer identifies the product by a product number. Each product has a description, and will have a particular finish and an associated price. Each product must have a minimum quantity on hand, which is decided by the manager. Whenever, the quantity on hand falls below the minimum level, it must be notified to the concerned staff so that they can start production, there by requesting for more raw materials from vendors Each product is manufactured from multiple raw material components. IPC records details of the raw material’s cost, name and the minimum unit of manufacture. It would also be appropriate to record a raw material identification number as well. IPC sources these raw materials from many vendor organisations that are often able to supply the same raw material. In this way IPC is able to select the cheapest price for the raw materials which assists them in their overall profit. Details of the vendor must be recorded including their name and address. Finally IPC wants to keep track of their employees who work on putting each particular product together from the various raw materials. Each employee has a particular skill and this is recorded against their employee number and their name. Employees are assigned to a particular work area in a particular location of the plant. These work areas are responsible for putting together the final products and typically they contain up to ten people. Every month an employee can be expected to be transferred to another work area. In addition, IPC needs the following reports to be produced. 1. List of all salesperson’s details like address, contact phone number etc 2. Report showing all the products sold by all salespersons. 3. List of all raw materials details needed for each product. 4. A report showing employee details and their history of work assigned. An XML document showing details of products and their raw materials. This XML document can be sent to raw materials vendor. In order to do this you must be familiar with XQuery and you need to have Oracle DBMS installed.