Recitation 08 UNION Queries - ORIE 3120 Industrial Data and...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
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”.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 3/12/2009 The only fields we are interested in are “Head”, “Tail”, and “Qty”. Reading the table, we can see that product G-LP50141-10B (the Head) requires one unit (Qty=1) each of (Tail) products GALP50141-10B, GALP50142-10B, GALP50143-10B, and GALP50144-10B. These are nested relationships. A product that appears as a tail of one product could be the head of another. As part of my work for the company, I wrote a graphical display program to view the MRP data. One of the screens I created showed a picture of the bill of materials relationships for any selected product. Here is an example: The product over on the right of this diagram, GHOD1422893P\A, does not appear as a tail in any bill of material relationship. We will call such products, “final products”. They are the type of products the customers order. The products on the coil Hull Plug Plug coil PTFE Tube Socket Nipple Nipple Socket Welding Ring Ring pience Nipple Welding Ring Final Product
Background image of page 2
3 3/12/2009 left, such as “99.051-10.22” and “GH32695-6UN” do not appear as the head of
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 21

Recitation 08 UNION Queries - ORIE 3120 Industrial Data and...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online