How to assemble a car?

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

Disassemble a car

An image about a disassembled car.

List of parts of a car

Different levels of components:

Level 1: SUV, Cabrio
Level 2: Body, Engine, Interior Decoration, Wheel
Level 3: Door, Hood, Engine Body, Cylinder, Seats

Hierarchy of car components

Create the data model

Elements to create hierarchy:

PartID & SubPartID

Elements to describe characteristics:

Component: Engine
Title: V6BiTurbo
Vendor: BMW
ProductKey: EV3891ASF
Cost: 3000
Quantity: 1

Data model description of BillOfMaterial

Use the hierarchical data model

What are the levels of components that build up the car?

+-----------------+---------------------------+
| Component       | Level                     |
|-----------------|---------------------------|
| SUV             | 1                         |
|-----------------|---------------------------|
| Body            | 2                         |
|-----------------|---------------------------|
| Hood            | 3                         |
+-----------------+---------------------------+

Use the hierarchical data model

What is the total quantity of each component required to build the car for each component level?

+-----------------+---------------------------+
| Component       | Quantity                  |
|-----------------|---------------------------|
| SUV             | 1                         |
|-----------------|---------------------------|
| Body            | 1                         |
|-----------------|---------------------------|
| Wheels          | 4                         |
+-----------------+---------------------------+

Let's assemble a car!

Hierarchical and Recursive Queries in SQL Server