build_schema_fk.aql

---------------------------------------------------------------
-- Database schema used when benchmarking AQL. Based on the
-- schema used for cassandra, see build_schema.cql.
--
-- This schema version uses foreign keys and the referential
-- integrity mechanism of AQL.
--
-- AQL is an SQL-like interface for the AntidoteDB data store.
-- For more information and documentation see AQL's repository:
--   https://github.com/mrshankly/secure-aql
---------------------------------------------------------------

CREATE UPDATE-WINS TABLE FmkePatients (
    ID int PRIMARY KEY,
    Name varchar,
    Address varchar
);

CREATE UPDATE-WINS TABLE FmkePharmacies (
    ID int PRIMARY KEY,
    Name varchar,
    Address varchar
);

CREATE UPDATE-WINS TABLE FmkeMedicalStaff (
    ID int PRIMARY KEY,
    Name varchar,
    Address varchar,
    Speciality varchar
);

CREATE UPDATE-WINS TABLE FmkeTreatmentFacilities (
    ID int PRIMARY KEY,
    Name varchar,
    Address varchar,
    Type varchar
);

CREATE UPDATE-WINS TABLE FmkePrescriptions (
    ID int PRIMARY KEY,
    PatID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePatients(ID),
    DocID int FOREIGN KEY UPDATE-WINS REFERENCES FmkeMedicalStaff(ID),
    PharmID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePharmacies(ID),
    DatePrescribed varchar,
    DateProcessed varchar
);

CREATE UPDATE-WINS TABLE FmkePatientPrescriptions (
    ID int PRIMARY KEY,
    PatientID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePatients(ID),
    PrescriptionID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePrescriptions(ID)
);
CREATE INDEX FmkePatientPrescriptionsPatientIdx ON FmkePatientPrescriptions (PatientID);

CREATE UPDATE-WINS TABLE FmkePharmacyPrescriptions (
    ID int PRIMARY KEY,
    PharmacyID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePharmacies(ID),
    PrescriptionID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePrescriptions(ID)
);
CREATE INDEX FmkePharmacyPrescriptionsPharmacyIdx ON FmkePharmacyPrescriptions (PharmacyID);

CREATE UPDATE-WINS TABLE FmkeStaffPrescriptions (
    ID int PRIMARY KEY,
    StaffID int FOREIGN KEY UPDATE-WINS REFERENCES FmkeMedicalStaff(ID),
    PrescriptionID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePrescriptions(ID)
);
CREATE INDEX FmkeStaffPrescriptionsStaffIdx ON FmkeStaffPrescriptions (StaffID);

CREATE UPDATE-WINS TABLE FmkePrescriptionDrugs (
    ID int PRIMARY KEY,
    PrescriptionID int FOREIGN KEY UPDATE-WINS REFERENCES FmkePrescriptions(ID),
    Drug varchar
);
CREATE INDEX FmkePrescriptionDrugsPrescriptionIdx ON FmkePrescriptionDrugs (PrescriptionID);