Sample Database

Sample Database
lac42
The 
Sample
 API illustrates advanced rules and database concepts. It consists of an order entry, departments database of few dozen tables, with complex logic and relationships. This API includes the Sample database.
This API is included with the single-user demonstration package of 
Layer7 Live API Creator
 that is based on Jetty and with the 
Layer7 Live API Creator
 Docker container installation. No additional steps are required to load this API.
You can also load this API sample if you have installed 
Layer7 Live API Creator
 on another container.
The following image shows the schema:
 sample_groups_diagram.png 
 The 
product_billofmaterials
 table represents that:
  • A "kit" product may have many "component" products. For example, a wing can consist of engines and bolts.
  • A "component" product may be a component of many kits. For example, bolts are used in wings and engines.
A product, such as a wing, can be a component (of a plane) and a kit.
The Sample database contains the 
products
 table, as illustrated in the following image:
 products.png 
Consider the following example:
  • A Boing 747 consists of a Fuselage, Wing, and Bolts 
  • A Wing consists of an Engine and Bolts
That is represented by rows in the 
product_billofmaterials
 table, as illustrated in the following image:
 product_billofmaterials (1).png 
Products are entered into the same products table but are differentiated, as follows:
  • Kit: A product with components. For example, Boing, Wing.
  • Component: A product that is a component. For example, Fuselage, Bolt, Wing, Engine.
Wing is a kit and a component, a recursive relationship.
The following relationships are from product to 
product_billofmaterials
:
  • The 
    inKits: product
     relationship
    The 
    product_name
     foreign key implements the 
    inKits: product
     relationship. This relationship is from product to 
    product_billofmaterials
    product.inKits
     returns the 
    product_billofmaterials
     identifying the kits in which a product is a member.The 
    bolt.inKits
     relationship returns 
    ( (Boing, Bolt) and (Wing, Bolt) )
    . The 
    product_billofMaterials.product
     relationship returns the component, 
    (Boing, Bolt)
     returns 
    Bolt
    .
  • The 
    components: kit
     relationship
    The 
    product_name_kit
     foreign key implements the 
    components: kit
     relationship. 
    product.components
     returns the 
    product_billofmaterials
     identifying the components in this kit. So, 
    boing.components
     returns 
    ( (Boing, Bolt) (Boing, Fuselage) (Boing, Wing) )
    .
    ProductBillofmaterials.kit
     returns the kit. So, 
    (Boing, Bolt)
     returns 
    Boing
    .
A Boing price calculates to $10,300:
  • 1 Fuselage @ $1300 = $1,300
  • 100 Bolts @ $10 = $1,000
  • 2 Wings @ $4,000 = $8,000, which itself is calculated as...
    • 2 Engines @ $1,500 = $3,000
    • 100 Bolts @ $10 = $1,000
Ordering a Boing 747 effects Product qtyOnHand, as follows:
  • totalQtyOrdered[Engines] increased by 4 (2 for each of 2 wings).
  • totalQtyOrdered[Bolts] increased by 300 (100 from Boing, 100 for each of 2 wings).
Ordering the Boing 747 inserts the following LineItems:
  • Boing 747
    • Bolts
    • Wing
      • Bolts
      • Engine
    • Fuselage