Manufacturing Process Management

A factory wants to manage its manufacturing process in terms of the raw materials used and the products being obtained in manufacturing process.

Each raw material used in the process has a unique code, name, type. The raw material can be delivered by one supplier at different dates, quantity and cost per unit. Supplier contact information must be kept in the records. Each product has a name, code and requires many processes to be fabricated, each process has a name, start and end time, and each process can use as many raw materials. The product can be produced by a single line of production which has a unique code and we always track the product produced to its corresponding line. After a product is finished a price and a date of production is given to it.

Steps to be done

Step 1: Data Modeling - Create an ER diagram,

Step 2: Map ER diagram into a relational model,

Step 3: Put the tables which you have mapped in the 3rd normal form,

Step 4: Create the tables and fill them with few records,

Step 5: SQLQueries

SQL Queries

1. List all the raw materials name and amount delivered by a certain supplier x.
2. List all the suppliers names that delivered the factory in the current year.
3. List all the raw materials name and amount required by a certain product p.
4. List the names and code of the processes required for a certain product.
5. Give the number of raw materials used of a certain product.
6. Give the number of processes required for of a certain product.
7. Give the total cost and price of all the products.
8. For each product list the name of raw materials used ordered by cost.
9. For each line list the unique name of products that was produced.

Write 5 additional queries that you find useful for this application. The queries should not be easy.