Creating a hierarchical data model
In this exercise, you will construct a simple hierarchical data model by creating the hierarchy of IT assets. An asset can be either Hardware
or Software
. A Software
asset can be split up into Application
or Tools
and so on. The hierarchy is shown in the following picture.
To model this hierarchy, a suitable data structure is needed. This structure can be accomplished by using a data model that consists of the child record ID
and the parent record ParentID
. The ID
s are consecutive values from 1 to 10.
Your task is to create the corresponding Equipment
table and to insert the assets Software
, Monitor
, and Microsoft Office
into the table. Keep in mind that you have to set the correct IDs for each asset to achieve the desired hierarchy of assets.
This exercise is part of the course
Hierarchical and Recursive Queries in SQL Server
Exercise instructions
- Define the fields
ID
andParentID
of typeINT
.ID
should not beNULL
,ParentID
can beNULL
. - Insert the equipment
Software
into the table with the correct IDs. The software is part ofAsset
. - Insert the equipment
Monitor
into the table with the correct IDs. The monitor is part ofHardware
. - Insert the equipment
Microsoft Office
into the table with the correct IDs. This software is part ofApplication
.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
CREATE TABLE Equipment (
-- Define ID and ParentID
___ ___ NOT NULL,
Equipment VARCHAR(255) NOT NULL,
___ ___
);
INSERT INTO Equipment VALUES (1,'Asset',NULL);
INSERT INTO Equipment VALUES (2,'Hardware',1);
-- Insert the type Software
INSERT INTO Equipment VALUES (___,____,1);
INSERT INTO Equipment VALUES (4,'Application',3);
INSERT INTO Equipment VALUES (5,'Tool',3);
INSERT INTO Equipment VALUES (6,'PC',2);
-- Insert the type Monitor
INSERT INTO Equipment VALUES (___,'Monitor',2);
INSERT INTO Equipment VALUES (8,'Phone',2);
INSERT INTO Equipment VALUES (9,'IPhone',8);
-- Insert the type Microsoft Office
INSERT INTO Equipment VALUES (___,___,4);
SELECT *
FROM Equipment;