Overview
Suppliers and Parts
Some examples for the individual operations available in Bmg use a version of the well-known Suppliers and Parts database. It contains the following four relations:
cities
| city | country |
|---|---|
| London | England |
| Paris | France |
| Athens | Greece |
| Brussels | Belgium |
parts
| pid | name | color | weight | city |
|---|---|---|---|---|
| P1 | Nut | Red | 12.0 | London |
| P2 | Bolt | Green | 17.0 | Paris |
| P3 | Screw | Blue | 17.0 | Oslo |
| P4 | Screw | Red | 14.0 | London |
| P5 | Cam | Blue | 12.0 | Paris |
| P6 | Cog | Red | 19.0 | London |
suppliers
| sid | name | status | city |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
supplies
| sid | pid | qty |
|---|---|---|
| S1 | P1 | 300 |
| S1 | P2 | 200 |
| S1 | P3 | 400 |
| S1 | P4 | 200 |
| S1 | P5 | 100 |
| S1 | P6 | 100 |
| S2 | P1 | 300 |
| S2 | P2 | 400 |
| S3 | P2 | 200 |
| S4 | P2 | 200 |
| S4 | P4 | 300 |
| S4 | P5 | 400 |
Here’s are some examples to illustrate what the relations represent:
- Supplier S1 (Smith) supplies 6 different parts
- There are 2 types of screw, one which weighs 17.0 and the other 14.0 (grams, let’s say)
- There are 2 suppliers in London, 2 in Paris, and 1 in Athens
- Supplier S4 (Clark) has 900 parts in stock, in total
- For the part P5 (Cam), there are 500 items in stock in total, accross all suppliers
Our examples assume the four relations are referenced by variables by the same names: cities, parts, etc.
Get part numbers for all parts supplied by more than one supplier