A prompt for extracting SQL entities for use in Microsoft's graphrag implementation. Builds on the default entity extraction prompt by modifying prompt goals. Includes SQL specific extraction examples.

sql-entity-extractor-prompt.py

# !!Note: Still a work in progress

# A prompt for extracting SQL entities for use in Microsoft's graphrag implementation. Builds on the default entity extraction prompt by modifying prompt goals. Includes SQL specific extraction examples.

# Original Prompt: https://github.com/microsoft/graphrag/blob/main/graphrag/index/graph/extractors/graph/prompts.py

GRAPH_EXTRACTION_PROMPT = """
-Goal-
Given the following information regarding entities in a sql database:
 - Table & View DDL
 - Table & View Descriptions
 - Column Descriptions

Also given a list of non-sql entity types.

Identify all entities of the type Table, View, Column along with any non-sql types from the text and all relationships among the identified entities.
 
-Steps-
1. Identify all entities. For each identified entity, extract the following information:
- entity_name: Name of the entity, capitalized
- entity_type: One of the following types: [{entity_types}]
- entity_description: Comprehensive description of the entity's attributes and activities. If the entity is a table or view, include a description of how it's DDL is constructed.
Format each entity as ("entity"{tuple_delimiter}<entity_name>{tuple_delimiter}<entity_type>{tuple_delimiter}<entity_description>)
 
2. From the entities identified in step 1, identify all pairs of (source_entity, target_entity) that are *clearly related* to each other. If it's a relationship between a sql entities, such as tables to view, or table to table, or column to table, or any other combination of sql entities; base the the relationship on evidence of use in DDLs or descriptions. If it's a relationship between a sql entity and a non-sql entity, this could be a relationship based on the entity's attributes or activities.
For each pair of related entities, extract the following information:
- source_entity: name of the source entity, as identified in step 1
- target_entity: name of the target entity, as identified in step 1
- relationship_description: explanation as to why you think the source entity and the target entity are related to each other.
- relationship_strength: a numeric score indicating strength of the relationship between the source entity and target entity. The score must be between 1-10. If the relationship is based on evidence in the DDL, such as a direct reference in a DDL assign the highest score of 10.
If the relationship is between a sql entities use the following relationship types:
    - For Table Source to Column Target Relationship Descriptions:
        - HAS_COLUMN: The table has a column with the same name as the target column.
    - For Column Source to Table Target Relationship Descriptions:
        - IS_PRIMARY_KEY_OF: The column is the primary key of the target table.
        - IS_FOREIGN_KEY_IN: The column is a foreign key to the target table.
    - For Column Source to Column Target Relationship Descriptions:
        - IS_FOREIGN_KEY_REF_FOR: The column is a foreign key reference for the target column.
    - For View Source to Table or View Target Relationship Descriptions:
        - IS_CREATED_USING: The view is created using data from the target table or view.

Format each relationship as ("relationship"{tuple_delimiter}<source_entity>{tuple_delimiter}<target_entity>{tuple_delimiter}<relationship_description>{tuple_delimiter}<relationship_strength>)
 
3. Return output in English as a single list of all the entities and relationships identified in steps 1 and 2. Use **{record_delimiter}** as the list delimiter.
 
4. When finished, output {completion_delimiter}
 
######################
-Examples-
######################
Example 1:
Entity_types: SQLTABLE,SQLVIEW,SQLCOLUMN,ORGANIZATION,GROUP
Text:
    Table_Name: Orders
    Description: Orders made by users through SuperMart's online platform.
    DDL:
    CREATE TABLE Orders (
        OrderID int NOT NULL,
        OrderNumber int NOT NULL,
        CustomerID int,
        PRIMARY KEY (OrderID),
        CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    );
######################
Output:
("entity"{tuple_delimiter}ORDERS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter} Orders made by users through SuperMart's online platform. DDL: CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, CustomerID int, PRIMARY KEY (OrderID), CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) )
{record_delimiter}
("entity"{tuple_delimiter}ORDERID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}OrderID column in the Orders table)
{record_delimiter}
("entity"{tuple_delimiter}ORDERNUMBER COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}OrderNumber column in the Orders table)
{record_delimiter}
("entity"{tuple_delimiter}CUSTOMERID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}CustomerID column in the Orders table. It is a foreign key that references the CustomerID column in the Customers table)
{record_delimiter}
("entity"{tuple_delimiter}CUSTOMERS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about customers)
{record_delimiter}
("entity"{tuple_delimiter}SUPERMART{tuple_delimiter}ORGANIZATION{tuple_delimiter}SuperMart is an online platform where users can make orders)
{record_delimiter}
("entity"{tuple_delimiter}CUSTOMER{tuple_delimiter}GROUP{tuple_delimiter}People who make orders on SuperMart's online platform)
{record_delimiter}
("relationship"{tuple_delimiter}ORDERS TABLE{tuple_delimiter}ORDERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}ORDERID COLUMN{tuple_delimiter}ORDERS TABLE{tuple_delimiter}IS_PRIMARY_KEY_OF{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}ORDERS TABLE{tuple_delimiter}ORDERNUMBER COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}ORDERS TABLE{tuple_delimiter}CUSTOMERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMERID COLUMN{tuple_delimiter}ORDERS TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMERS TABLE{tuple_delimiter}CUSTOMERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SUPERMART{tuple_delimiter}ORDER TABLE{tuple_delimiter}SuperMart stores order made by users in the Orders table{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMER{tuple_delimiter}ORDERS TABLE{tuple_delimiter}A Customer's orders are tracked in the Orders table{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMER{tuple_delimiter}SUPERMART{tuple_delimiter}Customers make orders on SuperMart's online platform{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMER{tuple_delimiter}CUSTOMERS TABLE{tuple_delimiter}Customers Information is stored in the Customers table{tuple_delimiter}6)
{completion_delimiter}
######################
Example 2:
Entity_types: SQLTABLE,SQLVIEW,SQLCOLUMN,ORGANIZATION
Text:
    Table_Name: Employees
    Description: A table that stores information about employees.
    DDL:
    CREATE TABLE Employees (
        EmployeeID int NOT NULL,
        EmployeeName varchar(255),
        PRIMARY KEY (EmployeeID),
    );

    Table_Name: Shifts
    Description: A table that stores information about shifts for a BuckyBurger restaurants across the country. Each shift has a manager, a single scheduled employees, and a location.
    DDL:
    CREATE TABLE Shifts (
        ShiftID int NOT NULL,
        ShiftDate date,
        StartTime time,
        EndTime time,
        ManagerID int NOT NULL,
        ScheduledEmployee int,
        LocationID int,
        PRIMARY KEY (ShiftID),
        CONSTRAINT FK_ManagerShift FOREIGN KEY (ManagerID) REFERENCES Managers(ManagerID),
        CONSTRAINT FK_ScheduledEmployeeShift FOREIGN KEY (ScheduledEmployee) REFERENCES Employees(EmployeeID),
        CONSTRAINT FK_LocationShift FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
    );

    Table_Name: Locations
    Description: A table that stores information about BuckyBurger restaurant locations.
    DDL:
    CREATE TABLE Locations (
        LocationID int NOT NULL,
        LocationName varchar(255),
        PRIMARY KEY (LocationID)
    );

    View_Name: Scheduled_Employees_By_ShiftDate
    Description: A view that shows the employees scheduled on each shift date. And location of the shift.
    DDL:
    CREATE VIEW Scheduled_Employees_By_ShiftDate AS
    SELECT shft.ShiftDate, emp.EmployeeName, loc.LocationName, shft.StartTime, shft.EndTime, shft.ManagerID
    FROM Shifts AS shft
    JOIN Locations AS loc ON shft.LocationID = loc.LocationID
    JOIN Employees AS emp ON shft.ScheduledEmployee = emp.EmployeeID
######################
Output:
("entity"{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about employees. DDL: CREATE TABLE Employees ( EmployeeID int NOT NULL, EmployeeName varchar(255), PRIMARY KEY (EmployeeID), ); )
{record_delimiter}
("entity"{tuple_delimiter}EMPLOYEEID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}EmployeeID column in the Employees table)
{record_delimiter}
("entity"{tuple_delimiter}EMPLOYEENAME COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}EmployeeName column in the Employees table)
{record_delimiter}
("entity"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about shifts for a BuckyBurger restaurants across the country. Each shift has a manager, a single scheduled employees, and a location. DDL: CREATE TABLE Shifts ( ShiftID int NOT NULL, ShiftDate date, StartTime time, EndTime time, ManagerID int NOT NULL, ScheduledEmployee int, LocationID int, PRIMARY KEY (ShiftID), CONSTRAINT FK_ManagerShift FOREIGN KEY (ManagerID) REFERENCES Managers(ManagerID), CONSTRAINT FK_ScheduledEmployeeShift FOREIGN KEY (ScheduledEmployee) REFERENCES Employees(EmployeeID), CONSTRAINT FK_LocationShift FOREIGN KEY (LocationID) REFERENCES Locations(LocationID) )
{record_delimiter}
("entity"{tuple_delimiter}MANAGERS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about managers.)
{record_delimiter}
("entity"{tuple_delimiter}SHIFTID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}ShiftID column in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}SHIFTDATE COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}ShiftDate column in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}STARTTIME COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}StartTime column in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}ENDTIME COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}EndTime column in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}MANAGERID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}ManagerID column in the Managers table. It is a foreign key in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}SCHEDULEDEMPLOYEE COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}ScheduledEmployee column in the Shifts table. It is a foreign key that references the EmployeeID column in the Employees table)
{record_delimiter}
("entity"{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}LocationID column in the locations table. It is a foreign key in the Shifts table)
{record_delimiter}
("entity"{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about restaurant locations. DDL: CREATE TABLE Locations ( LocationID int NOT NULL, LocationName varchar(255), PRIMARY KEY (LocationID) )
{record_delimiter}
("entity"{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter} LocationID column in the Locations table)
{record_delimiter}
("entity"{tuple_delimiter}LOCATIONNAME COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter} LocationName column in the Locations table)
{record_delimiter}
("entity"{tuple_delimiter}SCHEDULED_EMPLOYEES_BY_SHIFTDATE VIEW{tuple_delimiter}SQLVIEW{tuple_delimiter} A view that shows the employees scheduled on each shift date. And location of the shift. DDL: CREATE VIEW Scheduled_Employees_By_ShiftDate AS SELECT shft.ShiftDate, emp.EmployeeName, loc.LocationName, shft.StartTime, shft.EndTime, shft.ManagerID FROM Shifts AS shft JOIN Locations AS loc ON shft.LocationID = loc.LocationID JOIN Employees AS emp ON shft.ScheduledEmployee = emp.EmployeeID)
{record_delimiter}
("entity"{tuple_delimiter}BUCKYBURGER{tuple_delimiter}ORGANIZATION{tuple_delimiter}BuckyBurger is a restaurant chain that operates across the country)
{record_delimiter}
("relationship"{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}EMPLOYEEID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}EMPLOYEEID COLUMN{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}IS_PRIMARY_KEY_OF{tuple_delimiter}10)
("relationship"{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}EMPLOYEENAME COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}SHIFTID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTID COLUMN{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}IS_PRIMARY_KEY_OF{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}SHIFTDATE COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}STARTTIME COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}ENDTIME COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}MANAGERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}MANAGERID COLUMN{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}SCHEDULEDEMPLOYEE COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SCHEDULEDEMPLOYEE COLUMN{tuple_delimiter}EMPLOYEEID COLUMN{tuple_delimiter}IS_FOREIGN_KEY_REF_FOR{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SCHEDULEDEMPLOYEE COLUMN{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}MANAGER TABLE{tuple_delimiter}MANAGERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}LOCATIONID COLUMN{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}IS_PRIMARY_KEY_OF{tuple_delimiter}10)
("relationship"{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}LOCATIONNAME COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SCHEDULED_EMPLOYEES_BY_SHIFTDATE VIEW{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}CREATED_USING{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SCHEDULED_EMPLOYEES_BY_SHIFTDATE VIEW{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}CREATED_USING{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SCHEDULED_EMPLOYEES_BY_SHIFTDATE VIEW{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}CREATED_USING{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}BUCKYBURGER{tuple_delimiter}SHIFTS TABLE{tuple_delimiter}BuckyBurger stores shift information in the Shifts table{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}BUCKYBURGER{tuple_delimiter}LOCATIONS TABLE{tuple_delimiter}BuckyBurger stores location information in the Locations table{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}BUCKYBURGER{tuple_delimiter}EMPLOYEES TABLE{tuple_delimiter}BuckyBurger stores employee information in the Employees table{tuple_delimiter}6)
{completion_delimiter}
######################
Example 3:
Entity_types: SQLTABLE,SQLVIEW,SQLCOLUMN,ORGANIZATION,GEO,GROUP,FACILITY,GENERIC
Text:
    SJ Trucking is a logistics company that specializes in B2B deliveries. They have a fleet of trucks and a team of drivers that handle deliveries across the United States. They have warehouses in major transportation hubs and offer a range of delivery services, including same-day delivery, express delivery, and scheduled deliveries.

    As of February 2019, SJ Trucking has partnered with LogiAnalytica, a logistics software company, to optimize their delivery routes and improve efficiency. The partnership has resulted in a 20% reduction in delivery times and a 15% increase in on-time deliveries. SJ Trucking has also implemented a new tracking system that allows customers to track their deliveries in real-time.

    Table_Name: Deliveries
    Description: Details of all deliveries made by SJ Trucking.
    DDL:
    CREATE TABLE Deliveries (
        DeliveryID int NOT NULL,
        DeliveryType varchar(50),
        Status varchar(50) NOT NULL,
        EstimatedDeliveryDate date,
        DeliveryDate date,
        PackageID int NOT NULL,
        DriverID int NOT NULL,
        PickupAddress varchar(255),
        DropAddress varchar(255),
        NextDeliveryID int,
        Notes text,
        PRIMARY KEY (DeliveryID),
        CONSTRAINT FK_PackageDelivery FOREIGN KEY (PackageID) REFERENCES Packages(PackageID),
        CONSTRAINT FK_DriverDelivery FOREIGN KEY (DriverID) REFERENCES Drivers(DriverID)
    );

    Additional Column Description:
    - Status: The status of the delivery. It can be one of the following options: "In Transit", "Delivered", "Delayed", "Cancelled", "Awaiting Pickup".
    - DeliveryType: The type of delivery this is. It can be one of the following options: "To Customer", "Sub-Transit", "Return", "To Warehouse".
    - EstimatedDeliveryDate: The date the delivery is expected to be completed.
    - Notes: Any additional notes about the delivery, such as details about the delivery status or special instructions.
    - DeliveryDate: The date the package was delivered. If the status is not "Delivered", this field will be NULL.
    - NextDeliveryID: The ID of the next delivery in the sequence. If a delivery is part of a chain of deliveries, this field will contain the ID of the next delivery. If it is the only or last delivery in the chain, this field will be NULL. If the delivery is a sub-transit, this field will contain the ID of the next delivery in the route.

    Additional Information:
        Status Descriptions:
        - In Transit: The package is currently being transported to its destination.
        - Delivered: The package has been successfully delivered to the recipient.
        - Delayed: The package is delayed and has not yet been delivered past the estimated delivery date.
        - Cancelled: The delivery has been cancelled and will not be completed.
        - Awaiting Pickup: The package is ready for pickup but has not yet been collected.

        Delivery Type Descriptions:
        - To Customer: The delivery is being made directly to a customer drop-off location.
        - Sub-Transit: The delivery is being made to a sub-transit location for further delivery.
        - Return: The delivery is a return of a package to the warehouse.
        - To Warehouse: The delivery is being made to the warehouse for storage or processing.

        How Sub-Transit Deliveries Work:
        A sub-transit delivery is a delivery that is part of a chain of deliveries. Each delivery in the chain is linked to the next delivery in the route. The last delivery in the chain is not flagged as sub-transit and does not have a next delivery ID. The next delivery ID is used to track the sequence of deliveries and ensure that each delivery is completed in the correct order. There are cases where a delivery may be cancelled while in transit, in which case a new delivery is created to return the package to the warehouse.

        Procedure for Handling cancelled Deliveries in transit:
        If a delivery is cancelled, either by a customer or by us, while in transit, the status is updated to "Cancelled" and the delivery date would remain NULL. The. A new delivery would be created to return the package to the warehouse and the cancelled delivery would link the new delivery as the next delivery in the sequence.

######################
Output:
("entity"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}ORGANIZATION{tuple_delimiter}SJ Trucking is a logistics company that specializes in B2B deliveries. They have a fleet of trucks and a team of drivers that handle deliveries across the United States. They have warehouses in major transportation hubs and offer a range of delivery services, including same-day delivery, express delivery, and scheduled deliveries)
{record_delimiter}
("entity"{tuple_delimiter}LOGIANALYTICA{tuple_delimiter}ORGANIZATION{tuple_delimiter}LogiAnalytica is a logistics software company that has partnered with SJ Trucking to optimize delivery routes and improve efficiency)
{record_delimiter}
("entity"{tuple_delimiter}UNITED STATES{tuple_delimiter}GEO{tuple_delimiter}Country where SJ Trucking operates and makes deliveries)
{record_delimiter}
("entity"{tuple_delimiter}DRIVERS{tuple_delimiter}GROUP{tuple_delimiter}SJ Trucking employees who drive the trucks and make deliveries)
{record_delimiter}
("entity"{tuple_delimiter}CUSTOMERS{tuple_delimiter}GROUP{tuple_delimiter}Businesses that receive deliveries from SJ Trucking)
{record_delimiter}
("entity"{tuple_delimiter}WAREHOUSES{tuple_delimiter}FACILITY{tuple_delimiter}Facilities used by SJ Trucking to store goods and packages)
{record_delimiter}
("entity"{tuple_delimiter}PACKAGES{tuple_delimiter}GENERIC{tuple_delimiter}Packages that are delivered by SJ Trucking)
{record_delimiter}
("entity"{tuple_delimiter}DELIVERIES{tuple_delimiter}GENERIC{tuple_delimiter}Scheduled deliveries made by SJ Trucking)
{record_delimiter}
("entity"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}Details of all deliveries made by SJ Trucking. DDL: CREATE TABLE Deliveries ( DeliveryID int NOT NULL, DeliveryType varchar(50), Status varchar(50) NOT NULL, EstimatedDeliveryDate date, DeliveryDate date, PackageID int NOT NULL, DriverID int NOT NULL, PickupAddress varchar(255), DropAddress varchar(255), NextDeliveryID int, Notes text, PRIMARY KEY (DeliveryID), CONSTRAINT FK_PackageDelivery FOREIGN KEY (PackageID) REFERENCES Packages(PackageID), CONSTRAINT FK_DriverDelivery FOREIGN KEY (DriverID) REFERENCES Drivers(DriverID) )
{record_delimiter}
("entity"{tuple_delimiter}DELIVERYID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}DeliveryID column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}DELIVERYTYPE COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}DeliveryType column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}STATUS COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}Status column in the Deliveries table. The status of the delivery. It can be one of the following options: "In Transit", "Delivered", "Delayed", "Cancelled", "Awaiting Pickup".)
{record_delimiter}
("entity"{tuple_delimiter}ESTIMATEDDELIVERYDATE COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}EstimatedDeliveryDate column in the Deliveries table. The date the delivery is expected to be completed.)
{record_delimiter}
("entity"{tuple_delimiter}DELIVERYDATE COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}DeliveryDate column in the Deliveries table. The date the package was delivered. If the status is not "Delivered", this field will be NULL.)
{record_delimiter}
("entity"{tuple_delimiter}PACKAGEID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}PackageID column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}DRIVERID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}DriverID column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}PICKUPADDRESS COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}PickupAddress column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}DROPADDRESS COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}DropAddress column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}NEXTDELIVERYID COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}NextDeliveryID column in the Deliveries table)
{record_delimiter}
("entity"{tuple_delimiter}NOTES COLUMN{tuple_delimiter}SQLCOLUMN{tuple_delimiter}Notes column in the Deliveries table. Any additional notes about the delivery, such as details about the delivery status or special instructions.)
{record_delimiter}
("entity"{tuple_delimiter}PACKAGES TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about packages)
{record_delimiter}
("entity"{tuple_delimiter}DRIVERS TABLE{tuple_delimiter}SQLTABLE{tuple_delimiter}A table that stores information about drivers)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}DELIVERYID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERYID COLUMN{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}IS_PRIMARY_KEY_OF{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}DELIVERYTYPE COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}STATUS COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}ESTIMATEDDELIVERYDATE COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}DELIVERYDATE COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}PACKAGEID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}PACKAGEID COLUMN{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}DRIVERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DRIVERID COLUMN{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}IS_FOREIGN_KEY_IN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}PICKUPADDRESS COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}DROPADDRESS COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}NEXTDELIVERYID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}NOTES COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}PACKAGES TABLE{tuple_delimiter}PACKAGEID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}DRIVERS TABLE{tuple_delimiter}DRIVERID COLUMN{tuple_delimiter}HAS_COLUMN{tuple_delimiter}10)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}UNITED STATES{tuple_delimiter}SJ Trucking operates and makes deliveries across the United States{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}DRIVERS{tuple_delimiter}SJ Trucking employs drivers to handle deliveries{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}WAREHOUSES{tuple_delimiter}SJ Trucking uses warehouses in major transportation hubs{tuple_delimiter}7)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}PACKAGES{tuple_delimiter}SJ Trucking delivers packages{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}DELIVERIES{tuple_delimiter}SJ Trucking makes scheduled deliveries{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}LOGIANALYTICA{tuple_delimiter}Beginning in February 2019, SJ Trucking partnered with LogiAnalytica to optimize delivery routes and improve efficiency{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}CUSTOMERS{tuple_delimiter}SJ Trucking delivers to business customers{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}DRIVERS{tuple_delimiter}SJ Trucking employs drivers to handle deliveries{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}WAREHOUSES{tuple_delimiter}SJ Trucking uses warehouses in major transportation hubs{tuple_delimiter}7)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}PACKAGES{tuple_delimiter}SJ Trucking delivers packages{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}DELIVERIES{tuple_delimiter}SJ Trucking makes scheduled deliveries{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SJ TRUCKING{tuple_delimiter}DELIVERIES TABLE{tuple_delimiter}SJ Trucking stores delivery information in the Deliveries table{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES{tuple_delimiter}PACKAGES{tuple_delimiter}A delivery is a package that is being delivered{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}DELIVERIES{tuple_delimiter}DRIVERS{tuple_delimiter}A delivery is handled by a driver{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMERS{tuple_delimiter}DELIVERIES{tuple_delimiter}Customers receive deliveries from SJ Trucking{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}CUSTOMERS{tuple_delimiter}DELIVERIES{tuple_delimiter}A customer can cancel a delivery. The delivery status would be updated to "Cancelled" and the delivery date would remain NULL. A new delivery would be created to return the package to the warehouse and the cancelled delivery would link the new delivery as the next delivery in the sequence{tuple_delimiter}6)
{record_delimiter}
("relationship"{tuple_delimiter}WAREHOUSES{tuple_delimiter}DELIVERIES{tuple_delimiter}Warehouses may be the destination of a delivery{tuple_delimiter}6)

######################
Example 4:
Entity_types: ORGANIZATION
Text:
TechGlobal's (TG) stock skyrocketed in its opening day on the Global Exchange Thursday. But IPO experts warn that the semiconductor corporation's debut on the public markets isn't indicative of how other newly listed companies may perform.

TechGlobal, a formerly public company, was taken private by Vision Holdings in 2014. The well-established chip designer says it powers 85% of premium smartphones.
######################
Output:
("entity"{tuple_delimiter}TECHGLOBAL{tuple_delimiter}ORGANIZATION{tuple_delimiter}TechGlobal is a stock now listed on the Global Exchange which powers 85% of premium smartphones)
{record_delimiter}
("entity"{tuple_delimiter}VISION HOLDINGS{tuple_delimiter}ORGANIZATION{tuple_delimiter}Vision Holdings is a firm that previously owned TechGlobal)
{record_delimiter}
("relationship"{tuple_delimiter}TECHGLOBAL{tuple_delimiter}VISION HOLDINGS{tuple_delimiter}Vision Holdings formerly owned TechGlobal from 2014 until present{tuple_delimiter}5)
{completion_delimiter}

######################
Example 5:
Entity_types: ORGANIZATION,GEO,PERSON
Text:
Five Aurelians jailed for 8 years in Firuzabad and widely regarded as hostages are on their way home to Aurelia.

The swap orchestrated by Quintara was finalized when $8bn of Firuzi funds were transferred to financial institutions in Krohaara, the capital of Quintara.

The exchange initiated in Firuzabad's capital, Tiruzia, led to the four men and one woman, who are also Firuzi nationals, boarding a chartered flight to Krohaara.

They were welcomed by senior Aurelian officials and are now on their way to Aurelia's capital, Cashion.

The Aurelians include 39-year-old businessman Samuel Namara, who has been held in Tiruzia's Alhamia Prison, as well as journalist Durke Bataglani, 59, and environmentalist Meggie Tazbah, 53, who also holds Bratinas nationality.
######################
Output:
("entity"{tuple_delimiter}FIRUZABAD{tuple_delimiter}GEO{tuple_delimiter}Firuzabad held Aurelians as hostages)
{record_delimiter}
("entity"{tuple_delimiter}AURELIA{tuple_delimiter}GEO{tuple_delimiter}Country seeking to release hostages)
{record_delimiter}
("entity"{tuple_delimiter}QUINTARA{tuple_delimiter}GEO{tuple_delimiter}Country that negotiated a swap of money in exchange for hostages)
{record_delimiter}
{record_delimiter}
("entity"{tuple_delimiter}TIRUZIA{tuple_delimiter}GEO{tuple_delimiter}Capital of Firuzabad where the Aurelians were being held)
{record_delimiter}
("entity"{tuple_delimiter}KROHAARA{tuple_delimiter}GEO{tuple_delimiter}Capital city in Quintara)
{record_delimiter}
("entity"{tuple_delimiter}CASHION{tuple_delimiter}GEO{tuple_delimiter}Capital city in Aurelia)
{record_delimiter}
("entity"{tuple_delimiter}SAMUEL NAMARA{tuple_delimiter}PERSON{tuple_delimiter}Aurelian who spent time in Tiruzia's Alhamia Prison)
{record_delimiter}
("entity"{tuple_delimiter}ALHAMIA PRISON{tuple_delimiter}GEO{tuple_delimiter}Prison in Tiruzia)
{record_delimiter}
("entity"{tuple_delimiter}DURKE BATAGLANI{tuple_delimiter}PERSON{tuple_delimiter}Aurelian journalist who was held hostage)
{record_delimiter}
("entity"{tuple_delimiter}MEGGIE TAZBAH{tuple_delimiter}PERSON{tuple_delimiter}Bratinas national and environmentalist who was held hostage)
{record_delimiter}
("relationship"{tuple_delimiter}FIRUZABAD{tuple_delimiter}AURELIA{tuple_delimiter}Firuzabad negotiated a hostage exchange with Aurelia{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}QUINTARA{tuple_delimiter}AURELIA{tuple_delimiter}Quintara brokered the hostage exchange between Firuzabad and Aurelia{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}QUINTARA{tuple_delimiter}FIRUZABAD{tuple_delimiter}Quintara brokered the hostage exchange between Firuzabad and Aurelia{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}SAMUEL NAMARA{tuple_delimiter}ALHAMIA PRISON{tuple_delimiter}Samuel Namara was a prisoner at Alhamia prison{tuple_delimiter}8)
{record_delimiter}
("relationship"{tuple_delimiter}SAMUEL NAMARA{tuple_delimiter}MEGGIE TAZBAH{tuple_delimiter}Samuel Namara and Meggie Tazbah were exchanged in the same hostage release{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}SAMUEL NAMARA{tuple_delimiter}DURKE BATAGLANI{tuple_delimiter}Samuel Namara and Durke Bataglani were exchanged in the same hostage release{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}MEGGIE TAZBAH{tuple_delimiter}DURKE BATAGLANI{tuple_delimiter}Meggie Tazbah and Durke Bataglani were exchanged in the same hostage release{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}SAMUEL NAMARA{tuple_delimiter}FIRUZABAD{tuple_delimiter}Samuel Namara was a hostage in Firuzabad{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}MEGGIE TAZBAH{tuple_delimiter}FIRUZABAD{tuple_delimiter}Meggie Tazbah was a hostage in Firuzabad{tuple_delimiter}2)
{record_delimiter}
("relationship"{tuple_delimiter}DURKE BATAGLANI{tuple_delimiter}FIRUZABAD{tuple_delimiter}Durke Bataglani was a hostage in Firuzabad{tuple_delimiter}2)
{completion_delimiter}

######################
-Real Data-
######################
Entity_types: {entity_types}
Text: {input_text}
######################
Output:"""