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 IDs 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

View Course

Exercise instructions

Define the fields ID and ParentID of type INT. ID should not be NULL, ParentID can be NULL.
Insert the equipment Software into the table with the correct IDs. The software is part of Asset.
Insert the equipment Monitor into the table with the correct IDs. The monitor is part of Hardware.
Insert the equipment Microsoft Office into the table with the correct IDs. This software is part of Application.

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;

Edit and Run Code