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 is a 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;