1. How to assemble a car?
Ever thought of how a car is assembled? In this video, we will solve a simplified version of this problem.
2. Disassemble a car
There are many parts that make up a car. There can be up to 10000 different parts which have to be put together in the correct way and order. This means that to build up the car, a strict order of operations has to be followed and this can be described by a hierarchy.
3. List of parts of a car
This hierarchy is represented by the list of parts. In this example, we distinguish the level of the hierarchy by components. The first level describes the type of a car. This could be for example an SUV or a Cabrio. The second level describes components like the body of the car, the engine of a car, the interior decoration inside a car, or the wheels of a car. The third level describes the second level in more detail. Accordingly, the body of level 2 could be described by a door or the hood and the engine could be built up with the engine body and cylinders.
4. Create the data model
To create the data model we have to define two types of fields. The first one is used to depict the hierarchy of the data model. This is done by the definition of the fields PartID and SubPartID. The second type of fields describes the characteristics of the item. This could be the component, the title of the item, the vendor of the item, the product key, and finally, the cost and quantity needed to assemble the car.
5. Use the hierarchical data model
One possible question to answer when faced with this type of data model is: what are the levels of components that build up the car? The answer for this question could be, for example, level 1 is the SUV, type of car, level 2 is the body, and level 3 is the hood.
6. Use the hierarchical data model
Another possible question is: what is the total quantity of each component required to build the car considering all components until a wanted component level? The answer could be, for example, SUV has the quantity 1, the body quantity 1, and the wheels quantity 4.
7. Let's assemble a car!
You will answer both questions in the following exercises by querying the hierarchical data model with recursive CTEs. Let's assemble a car!