{"Physician": {"columns": [[0, "EmployeeID", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Position", "VARCHAR(30)", 1, null, 0], [3, "SSN", "INTEGER", 1, null, 0]], "primary_keys": ["EmployeeID"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Department": {"columns": [[0, "DepartmentID", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Head", "INTEGER", 1, null, 0]], "primary_keys": ["DepartmentID"], "foreign_keys": [[0, 0, "Physician", "Head", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Affiliated_With": {"columns": [[0, "Physician", "INTEGER", 1, null, 1], [1, "Department", "INTEGER", 1, null, 2], [2, "PrimaryAffiliation", "BOOLEAN", 1, null, 0]], "primary_keys": ["Physician", "Department"], "foreign_keys": [[0, 0, "Department", "Department", "DepartmentID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Physician", "Physician", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Physician"], [1, 1, "Department"]]], "check_constraints": []}, "Procedures": {"columns": [[0, "Code", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Cost", "REAL", 1, null, 0]], "primary_keys": ["Code"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Trained_In": {"columns": [[0, "Physician", "INTEGER", 1, null, 1], [1, "Treatment", "INTEGER", 1, null, 2], [2, "CertificationDate", "DATETIME", 1, null, 0], [3, "CertificationExpires", "DATETIME", 1, null, 0]], "primary_keys": ["Physician", "Treatment"], "foreign_keys": [[0, 0, "Procedures", "Treatment", "Code", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Physician", "Physician", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Physician"], [1, 1, "Treatment"]]], "check_constraints": []}, "Patient": {"columns": [[0, "SSN", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Address", "VARCHAR(30)", 1, null, 0], [3, "Phone", "VARCHAR(30)", 1, null, 0], [4, "InsuranceID", "INTEGER", 1, null, 0], [5, "PCP", "INTEGER", 1, null, 0]], "primary_keys": ["SSN"], "foreign_keys": [[0, 0, "Physician", "PCP", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Nurse": {"columns": [[0, "EmployeeID", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Position", "VARCHAR(30)", 1, null, 0], [3, "Registered", "BOOLEAN", 1, null, 0], [4, "SSN", "INTEGER", 1, null, 0]], "primary_keys": ["EmployeeID"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Appointment": {"columns": [[0, "AppointmentID", "INTEGER", 1, null, 1], [1, "Patient", "INTEGER", 1, null, 0], [2, "PrepNurse", "INTEGER", 0, null, 0], [3, "Physician", "INTEGER", 1, null, 0], [4, "Start", "DATETIME", 1, null, 0], [5, "End", "DATETIME", 1, null, 0], [6, "ExaminationRoom", "TEXT", 1, null, 0]], "primary_keys": ["AppointmentID"], "foreign_keys": [[0, 0, "Physician", "Physician", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Nurse", "PrepNurse", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Patient", "Patient", "SSN", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Medication": {"columns": [[0, "Code", "INTEGER", 1, null, 1], [1, "Name", "VARCHAR(30)", 1, null, 0], [2, "Brand", "VARCHAR(30)", 1, null, 0], [3, "Description", "VARCHAR(30)", 1, null, 0]], "primary_keys": ["Code"], "foreign_keys": [], "unique_constraints": [], "check_constraints": []}, "Prescribes": {"columns": [[0, "Physician", "INTEGER", 1, null, 1], [1, "Patient", "INTEGER", 1, null, 2], [2, "Medication", "INTEGER", 1, null, 3], [3, "Date", "DATETIME", 1, null, 4], [4, "Appointment", "INTEGER", 0, null, 0], [5, "Dose", "VARCHAR(30)", 1, null, 0]], "primary_keys": ["Physician", "Patient", "Medication", "Date"], "foreign_keys": [[0, 0, "Appointment", "Appointment", "AppointmentID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Medication", "Medication", "Code", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Patient", "Patient", "SSN", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Physician", "Physician", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Physician"], [1, 1, "Patient"], [2, 2, "Medication"], [3, 3, "Date"]]], "check_constraints": []}, "Block": {"columns": [[0, "BlockFloor", "INTEGER", 1, null, 1], [1, "BlockCode", "INTEGER", 1, null, 2]], "primary_keys": ["BlockFloor", "BlockCode"], "foreign_keys": [], "unique_constraints": [[[0, 0, "BlockFloor"], [1, 1, "BlockCode"]]], "check_constraints": []}, "Room": {"columns": [[0, "RoomNumber", "INTEGER", 1, null, 1], [1, "RoomType", "VARCHAR(30)", 1, null, 0], [2, "BlockFloor", "INTEGER", 1, null, 0], [3, "BlockCode", "INTEGER", 1, null, 0], [4, "Unavailable", "BOOLEAN", 1, null, 0]], "primary_keys": ["RoomNumber"], "foreign_keys": [[0, 0, "Block", "BlockFloor", "BlockFloor", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Block", "BlockCode", "BlockCode", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "On_Call": {"columns": [[0, "Nurse", "INTEGER", 1, null, 1], [1, "BlockFloor", "INTEGER", 1, null, 2], [2, "BlockCode", "INTEGER", 1, null, 3], [3, "OnCallStart", "DATETIME", 1, null, 4], [4, "OnCallEnd", "DATETIME", 1, null, 5]], "primary_keys": ["Nurse", "BlockFloor", "BlockCode", "OnCallStart", "OnCallEnd"], "foreign_keys": [[0, 0, "Block", "BlockFloor", "BlockFloor", "NO ACTION", "NO ACTION", "NONE"], [0, 1, "Block", "BlockCode", "BlockCode", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Nurse", "Nurse", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Nurse"], [1, 1, "BlockFloor"], [2, 2, "BlockCode"], [3, 3, "OnCallStart"], [4, 4, "OnCallEnd"]]], "check_constraints": []}, "Stay": {"columns": [[0, "StayID", "INTEGER", 1, null, 1], [1, "Patient", "INTEGER", 1, null, 0], [2, "Room", "INTEGER", 1, null, 0], [3, "StayStart", "DATETIME", 1, null, 0], [4, "StayEnd", "DATETIME", 1, null, 0]], "primary_keys": ["StayID"], "foreign_keys": [[0, 0, "Room", "Room", "RoomNumber", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Patient", "Patient", "SSN", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [], "check_constraints": []}, "Undergoes": {"columns": [[0, "Patient", "INTEGER", 1, null, 1], [1, "Procedures", "INTEGER", 1, null, 2], [2, "Stay", "INTEGER", 1, null, 3], [3, "DateUndergoes", "DATETIME", 1, null, 4], [4, "Physician", "INTEGER", 1, null, 0], [5, "AssistingNurse", "INTEGER", 0, null, 0]], "primary_keys": ["Patient", "Procedures", "Stay", "DateUndergoes"], "foreign_keys": [[0, 0, "Nurse", "AssistingNurse", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"], [1, 0, "Physician", "Physician", "EmployeeID", "NO ACTION", "NO ACTION", "NONE"], [2, 0, "Stay", "Stay", "StayID", "NO ACTION", "NO ACTION", "NONE"], [3, 0, "Procedures", "Procedures", "Code", "NO ACTION", "NO ACTION", "NONE"], [4, 0, "Patient", "Patient", "SSN", "NO ACTION", "NO ACTION", "NONE"]], "unique_constraints": [[[0, 0, "Patient"], [1, 1, "Procedures"], [2, 2, "Stay"], [3, 3, "DateUndergoes"]]], "check_constraints": []}}
CREATE TABLE Physician (
    EmployeeID INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Position VARCHAR(30) NOT NULL,
    SSN INTEGER NOT NULL
);

CREATE TABLE Department (
    DepartmentID INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Head INTEGER NOT NULL,
    FOREIGN KEY (Head) REFERENCES Physician(EmployeeID)
);

CREATE TABLE Affiliated_With (
    Physician INTEGER NOT NULL  PRIMARY KEY,
    Department INTEGER NOT NULL  PRIMARY KEY,
    PrimaryAffiliation BOOLEAN NOT NULL,
    PRIMARY KEY (Physician, Department),
    UNIQUE (Physician, Department),
    FOREIGN KEY (Department) REFERENCES Department(DepartmentID),
    FOREIGN KEY (Physician) REFERENCES Physician(EmployeeID)
);

CREATE TABLE Procedures (
    Code INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Cost REAL NOT NULL
);

CREATE TABLE Trained_In (
    Physician INTEGER NOT NULL  PRIMARY KEY,
    Treatment INTEGER NOT NULL  PRIMARY KEY,
    CertificationDate DATETIME NOT NULL,
    CertificationExpires DATETIME NOT NULL,
    PRIMARY KEY (Physician, Treatment),
    UNIQUE (Physician, Treatment),
    FOREIGN KEY (Treatment) REFERENCES Procedures(Code),
    FOREIGN KEY (Physician) REFERENCES Physician(EmployeeID)
);

CREATE TABLE Patient (
    SSN INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Address VARCHAR(30) NOT NULL,
    Phone VARCHAR(30) NOT NULL,
    InsuranceID INTEGER NOT NULL,
    PCP INTEGER NOT NULL,
    FOREIGN KEY (PCP) REFERENCES Physician(EmployeeID)
);

CREATE TABLE Nurse (
    EmployeeID INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Position VARCHAR(30) NOT NULL,
    Registered BOOLEAN NOT NULL,
    SSN INTEGER NOT NULL
);

CREATE TABLE Appointment (
    AppointmentID INTEGER NOT NULL  PRIMARY KEY,
    Patient INTEGER NOT NULL,
    PrepNurse INTEGER,
    Physician INTEGER NOT NULL,
    Start DATETIME NOT NULL,
    End DATETIME NOT NULL,
    ExaminationRoom TEXT NOT NULL,
    FOREIGN KEY (Physician) REFERENCES Physician(EmployeeID),
    FOREIGN KEY (PrepNurse) REFERENCES Nurse(EmployeeID),
    FOREIGN KEY (Patient) REFERENCES Patient(SSN)
);

CREATE TABLE Medication (
    Code INTEGER NOT NULL  PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
    Brand VARCHAR(30) NOT NULL,
    Description VARCHAR(30) NOT NULL
);

CREATE TABLE Prescribes (
    Physician INTEGER NOT NULL  PRIMARY KEY,
    Patient INTEGER NOT NULL  PRIMARY KEY,
    Medication INTEGER NOT NULL  PRIMARY KEY,
    Date DATETIME NOT NULL  PRIMARY KEY,
    Appointment INTEGER,
    Dose VARCHAR(30) NOT NULL,
    PRIMARY KEY (Physician, Patient, Medication, Date),
    UNIQUE (Physician, Patient, Medication, Date),
    FOREIGN KEY (Appointment) REFERENCES Appointment(AppointmentID),
    FOREIGN KEY (Medication) REFERENCES Medication(Code),
    FOREIGN KEY (Patient) REFERENCES Patient(SSN),
    FOREIGN KEY (Physician) REFERENCES Physician(EmployeeID)
);

CREATE TABLE Block (
    BlockFloor INTEGER NOT NULL  PRIMARY KEY,
    BlockCode INTEGER NOT NULL  PRIMARY KEY,
    PRIMARY KEY (BlockFloor, BlockCode),
    UNIQUE (BlockFloor, BlockCode)
);

CREATE TABLE Room (
    RoomNumber INTEGER NOT NULL  PRIMARY KEY,
    RoomType VARCHAR(30) NOT NULL,
    BlockFloor INTEGER NOT NULL,
    BlockCode INTEGER NOT NULL,
    Unavailable BOOLEAN NOT NULL,
    FOREIGN KEY (BlockFloor) REFERENCES Block(BlockFloor),
    FOREIGN KEY (BlockCode) REFERENCES Block(BlockCode)
);

CREATE TABLE On_Call (
    Nurse INTEGER NOT NULL  PRIMARY KEY,
    BlockFloor INTEGER NOT NULL  PRIMARY KEY,
    BlockCode INTEGER NOT NULL  PRIMARY KEY,
    OnCallStart DATETIME NOT NULL  PRIMARY KEY,
    OnCallEnd DATETIME NOT NULL  PRIMARY KEY,
    PRIMARY KEY (Nurse, BlockFloor, BlockCode, OnCallStart, OnCallEnd),
    UNIQUE (Nurse, BlockFloor, BlockCode, OnCallStart, OnCallEnd),
    FOREIGN KEY (BlockFloor) REFERENCES Block(BlockFloor),
    FOREIGN KEY (BlockCode) REFERENCES Block(BlockCode),
    FOREIGN KEY (Nurse) REFERENCES Nurse(EmployeeID)
);

CREATE TABLE Stay (
    StayID INTEGER NOT NULL  PRIMARY KEY,
    Patient INTEGER NOT NULL,
    Room INTEGER NOT NULL,
    StayStart DATETIME NOT NULL,
    StayEnd DATETIME NOT NULL,
    FOREIGN KEY (Room) REFERENCES Room(RoomNumber),
    FOREIGN KEY (Patient) REFERENCES Patient(SSN)
);

CREATE TABLE Undergoes (
    Patient INTEGER NOT NULL  PRIMARY KEY,
    Procedures INTEGER NOT NULL  PRIMARY KEY,
    Stay INTEGER NOT NULL  PRIMARY KEY,
    DateUndergoes DATETIME NOT NULL  PRIMARY KEY,
    Physician INTEGER NOT NULL,
    AssistingNurse INTEGER,
    PRIMARY KEY (Patient, Procedures, Stay, DateUndergoes),
    UNIQUE (Patient, Procedures, Stay, DateUndergoes),
    FOREIGN KEY (AssistingNurse) REFERENCES Nurse(EmployeeID),
    FOREIGN KEY (Physician) REFERENCES Physician(EmployeeID),
    FOREIGN KEY (Stay) REFERENCES Stay(StayID),
    FOREIGN KEY (Procedures) REFERENCES Procedures(Code),
    FOREIGN KEY (Patient) REFERENCES Patient(SSN)
);