QR Code

ERD(Entity Relationship Diagram)  / schema of SQL & EdgeSQL 

[Unusual Events]
|
|--[Structure]
|--[Function]
|--[Activity]
|--[Environment]
|
+--[Complaints]---[Objective Evaluation]---[Diagnosis]---[Medical Plans]---[Supplementary Plans]
                                                                                                                 |                                   |
                                                                                                                 |                                   +--[Lifestyle Plans]
                                                                                                                 +--[Actions]
                                                                                                                               |--[Referrals]
                                                                                                                               |--[Medications]
                                                                                                                               |--[Tests]
                                                                                                                               |--[Treatments]
|
+--[Encounters]
        |--[Self-care]
        |--[Consultation]
        |--[Outpatient]
        |--[Emergency]
        |--[Hospitalization]

[Activities]
|
|--[Diet]
|--[Exercise]
|--[Mental]


Schema of SQL :

-- Table: Unusual_Events
CREATE TABLE Unusual_Events (
Event_ID INT PRIMARY KEY,
Event_Type VARCHAR(255),
Date DATE,
Location VARCHAR(255),
Duration INT
);

-- Table: Structure
CREATE TABLE Structure (
Structure_ID INT PRIMARY KEY,
Structure_Type VARCHAR(255)
);

-- Table: Function
CREATE TABLE Function (
Function_ID INT PRIMARY KEY,
Function_Name VARCHAR(255)
);

-- Table: Activity
CREATE TABLE Activity (
Activity_ID INT PRIMARY KEY,
Activity_Name VARCHAR(255)
);

-- Table: Environment
CREATE TABLE Environment (
Environment_ID INT PRIMARY KEY,
Environment_Type VARCHAR(255)
);

-- Table: Complaints
CREATE TABLE Complaints (
Complaint_ID INT PRIMARY KEY,
Event_ID INT,
Description TEXT,
FOREIGN KEY (Event_ID) REFERENCES Unusual_Events(Event_ID)
);

-- Table: Objective_Evaluation
CREATE TABLE Objective_Evaluation (
Evaluation_ID INT PRIMARY KEY,
Complaint_ID INT,
Evaluation_Result TEXT,
FOREIGN KEY (Complaint_ID) REFERENCES Complaints(Complaint_ID)
);

-- Table: Diagnosis
CREATE TABLE Diagnosis (
Diagnosis_ID INT PRIMARY KEY,
Evaluation_ID INT,
Diagnosis_Result TEXT,
FOREIGN KEY (Evaluation_ID) REFERENCES Objective_Evaluation(Evaluation_ID)
);

-- Table: Medical_Plans
CREATE TABLE Medical_Plans (
Plan_ID INT PRIMARY KEY,
Diagnosis_ID INT,
Plan_Description TEXT,
FOREIGN KEY (Diagnosis_ID) REFERENCES Diagnosis(Diagnosis_ID)
);

-- Table: Supplementary_Plans
CREATE TABLE Supplementary_Plans (
Supplementary_Plan_ID INT PRIMARY KEY,
Plan_ID INT,
Plan_Description TEXT,
FOREIGN KEY (Plan_ID) REFERENCES Medical_Plans(Plan_ID)
);

-- Table: Lifestyle_Plans
CREATE TABLE Lifestyle_Plans (
Lifestyle_Plan_ID INT PRIMARY KEY,
Plan_ID INT,
Plan_Description TEXT,
FOREIGN KEY (Plan_ID) REFERENCES Medical_Plans(Plan_ID)
);

-- Table: Actions
CREATE TABLE Actions (
Action_ID INT PRIMARY KEY,
Plan_ID INT,
Action_Type VARCHAR(255),
FOREIGN KEY (Plan_ID) REFERENCES Medical_Plans(Plan_ID)
);

-- Table: Encounters
CREATE TABLE Encounters (
Encounter_ID INT PRIMARY KEY,
Encounter_Type VARCHAR(255)
);

-- Table: Activities
CREATE TABLE Activities (
Activity_ID INT PRIMARY KEY,
Activity_Type VARCHAR(255)
);

-- Table: Event_Structure (Relationship between Unusual_Events and Structure)
CREATE TABLE Event_Structure (
ID INT PRIMARY KEY,
Event_ID INT,
Structure_ID INT,
FOREIGN KEY (Event_ID) REFERENCES Unusual_Events(Event_ID),
FOREIGN KEY (Structure_ID) REFERENCES Structure(Structure_ID)
);

-- Table: Event_Function (Relationship between Unusual_Events and Function)
CREATE TABLE Event_Function (
ID INT PRIMARY KEY,
Event_ID INT,
Function_ID INT,
FOREIGN KEY (Event_ID) REFERENCES Unusual_Events(Event_ID),
FOREIGN KEY (Function_ID) REFERENCES Function(Function_ID)
);

-- Table: Event_Activity (Relationship between Unusual_Events and Activity)
CREATE TABLE Event_Activity (
ID INT PRIMARY KEY,
Event_ID INT,
Activity_ID INT,
FOREIGN KEY (Event_ID) REFERENCES Unusual_Events(Event_ID),
FOREIGN KEY (Activity_ID) REFERENCES Activity(Activity_ID)
);

-- Table: Event_Environment (Relationship between Unusual_Events and Environment)
CREATE TABLE Event_Environment (
ID INT PRIMARY KEY,
Event_ID INT,
Environment_ID INT,
FOREIGN KEY (Event_ID) REFERENCES Unusual_Events(Event_ID),
FOREIGN KEY (Environment_ID) REFERENCES Environment(Environment_ID)
);


Schema of EdgeSQL :

-- Define Nodes
CREATE NODE TABLE Patients (
PatientID INT PRIMARY KEY,
Name VARCHAR(255),
Age INT,
Gender VARCHAR(10)
);

CREATE NODE TABLE Events (
EventID INT PRIMARY KEY,
EventType VARCHAR(255),
Date DATE,
Location VARCHAR(255),
Duration INT
);

CREATE NODE TABLE Complaints (
ComplaintID INT PRIMARY KEY,
Description TEXT
);

CREATE NODE TABLE Diagnoses (
DiagnosisID INT PRIMARY KEY,
DiagnosisResult TEXT
);

CREATE NODE TABLE Treatments (
TreatmentID INT PRIMARY KEY,
TreatmentType VARCHAR(255)
);

-- Define Edges : Edge-Sql
CREATE EDGE TABLE Patient_Complaints (
FROM Patients REFERENCES Patients(PatientID),
TO Complaints REFERENCES Complaints(ComplaintID),
ComplaintDate DATE
);

CREATE EDGE TABLE Complaint_Diagnoses (
FROM Complaints REFERENCES Complaints(ComplaintID),
TO Diagnoses REFERENCES Diagnoses(DiagnosisID)
);

CREATE EDGE TABLE Diagnosis_Treatments (
FROM Diagnoses REFERENCES Diagnoses(DiagnosisID),
TO Treatments REFERENCES Treatments(TreatmentID)
); 

EdgeSQL query :

-- Find all treatments for a given patient
SELECT t.TreatmentType
FROM Patients p
JOIN Patient_Complaints pc ON p.PatientID = pc.PatientID
JOIN Complaint_Diagnoses cd ON pc.ComplaintID = cd.ComplaintID
JOIN Diagnosis_Treatments dt ON cd.DiagnosisID = dt.DiagnosisID
JOIN Treatments t ON dt.TreatmentID = t.TreatmentID
WHERE p.Name = 'John Doe';

-- Find the shortest path between two patients through shared complaints and diagnoses
SELECT *
FROM Patients p1, Patients p2
MATCH (p1)-[:Patient_Complaints]->(c:Complaints)<-[:Patient_Complaints]-(p2)
WHERE p1.Name = 'John Doe' AND p2.Name = 'Jane Smith';