Step 1: Designing the Table Schema<\/strong><\/p>\n\n\n\n1. Entities Table<\/strong><\/p>\n\n\n\nThis table will store information about the entities. Each entity has a unique id<\/code>, a name<\/code>, and a description<\/code> to explain what the entity is about. Here\u2019s the SQL code to create the entities<\/code> table:<\/p>\n\n\n\nCREATE TABLE `entities` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `name` varchar(512) DEFAULT NULL,\n `description` text DEFAULT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;<\/code><\/pre>\n\n\n\n2. Relationships Table<\/strong><\/p>\n\n\n\nThe relationships<\/code> table captures how entities are related. It includes a source_entity_id<\/code> \uadf8\ub9ac\uace0 target_entity_id<\/code> to denote the start and end of a relationship, and a relationship_desc<\/code> which describes the nature of the relationship. Here\u2019s the SQL code for this table:<\/p>\n\n\n\nCREATE TABLE `relationships` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `source_entity_id` int(11) DEFAULT NULL,\n `target_entity_id` int(11) DEFAULT NULL,\n `relationship_desc` text DEFAULT NULL,\n PRIMARY KEY (`id`),\n KEY `fk_1` (`source_entity_id`),\n KEY `fk_2` (`target_entity_id`),\n CONSTRAINT `fk_1` FOREIGN KEY (`source_entity_id`) REFERENCES `entities` (`id`),\n CONSTRAINT `fk_2` FOREIGN KEY (`target_entity_id`) REFERENCES `entities` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;<\/code><\/pre>\n\n\n\nStep 2: Inserting Data<\/strong><\/p>\n\n\n\nWith the tables created, the next step is to populate them with data.<\/p>\n\n\n\n
Insert Entities<\/strong><\/p>\n\n\n\nHere\u2019s how you can add some entities into your database:<\/p>\n\n\n\n
INSERT INTO entities (name, description) VALUES\n('Elon Reeve Musk', 'Elon Reeve Musk is a businessman and investor, born in Pretoria, South Africa.'),\n('Tesla, Inc.', 'American electric vehicle and clean energy company based in Palo Alto, California.');<\/code><\/pre>\n\n\n\nInsert Relationships<\/strong><\/p>\n\n\n\nOnce entities are inserted, we can define relationships between them:<\/p>\n\n\n\n
INSERT INTO relationships (source_entity_id, target_entity_id, relationship_desc) VALUES\n(1, 2, 'CEO of');<\/code><\/pre>\n\n\n\nStep 3: Querying the Knowledge Graph<\/strong><\/p>\n\n\n\nTo see how entities are interconnected through relationships, you can execute the following SQL query:<\/p>\n\n\n\n
SELECT e1.name AS `Entity`, r.relationship_desc AS `Relationship`, e2.name AS `Related Entity`\nFROM relationships r\nJOIN entities e1 ON r.source_entity_id = e1.id\nJOIN entities e2 ON r.target_entity_id = e2.id;<\/code><\/pre>\n\n\n\nThis query will help you visualize the relationships in a tabular format, showing how entities are linked.<\/p>\n\n\n\n