CREATE TABLE PATIENTS (ID INTEGER NOT NULL, FIRSTNAME VARCHAR(15) NOT NULL, LASTNAME VARCHAR(15) NOT NULL, SEX CHAR(1) NOT NULL, BIRTHDATE DATETIME NOT NULL, CONSTRAINT PATIENTSPK PRIMARY KEY (ID))

CREATE TABLE DOCTORS (ID INTEGER NOT NULL, FIRSTNAME VARCHAR(15) NOT NULL, LASTNAME VARCHAR(15) NOT NULL, SPECIALTY VARCHAR(20) NOT NULL, CONSTRAINT DOCTORSPK PRIMARY KEY (ID))

CREATE TABLE PATIENTSDOCTORS (PATIENTID INTEGER NOT NULL, DOCTORID INTEGER NOT NULL, CONSTRAINT PATIENTSDOCTORSPK PRIMARY KEY (PATIENTID, DOCTORID), CONSTRAINT FK_PATIENTS FOREIGN KEY (PATIENTID) REFERENCES PATIENTS (ID), CONSTRAINT FK_DOCTORS FOREIGN KEY (DOCTORID) REFERENCES DOCTORS (ID))

CREATE TABLE PRESCRIPTIONS (PATIENTID INTEGER NOT NULL, DOCTORID INTEGER NOT NULL, MEDICATIONNAME VARCHAR(25) NOT NULL, DOSAGE VARCHAR(25) NOT NULL, QUANTITY VARCHAR(25) NOT NULL, DIAGNOSIS XML NOT NULL, CONSTRAINT FK_PATIENTSDOCTORS FOREIGN KEY (PATIENTID, DOCTORID) REFERENCES PATIENTSDOCTORS (PATIENTID, DOCTORID))
