1 3/12/2009 ORIE 3120 Industrial Data and Systems Analysis Recitation 8 The UNION query and User Interface Programming Created by Peter Jackson Edited by Keerati Winyoopradist and Andrew Yongvanich Basic Concepts By this point in the course, you are familiar with nearly all of the most useful commands in SQL. The only one you haven’t seen that I use routinely is the UNION query. Materials requirements planning is a nice opportunity to show its usefulness. This tutorial is structured to use it twice, for very different purposes. The first use is to explode the bill of material relationships from the end-item level. The second use is to put together all of the data needed to produce a cumulative plot of demand and supply. Getting Started 1.Create a directory to store your work. If you use the lab machine, be aware that the directory (and all your work) will disappear when the machine is rebooted. We recommend using a USB flash-drive. 2.Download the file “GPPMRP.mdb” from the course website into the directory that you created. 3.Open the database using MS Access. Explore the tables: These data are extracted from the MRP system for a company in Germany (see the lecture earlier in the semester on “Case Study: Job Shop Manufacturing”). It has been many years since I worked with the company. I have forgotten some of the details of their system so if we get results that don’t make sense it is most likely my mistake, not theirs. We will make some educated guesses as to what the tables and fields mean. The only tables we are interested in are “Bill of Materials”, “Order List”, and “Products”. The “Job-List” table appears to be useless because it doesn’t match anything in the “Order List” table. The “Routing” and “WorkCenters” table could be interesting if you wanted a project on scheduling the factory to meet the customer orders. We will not use those tables this semester. 4.Open the table “Bill of Materials”.
This preview has intentionally blurred sections.
Sign up to view the full version.