schemas.sql

------------------------------------------------------------------------------
-- Copyright 2019 IBM Corp. All Rights Reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
--  you may not use this file except in compliance with the License.
--  You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
------------------------------------------------------------------------------

SET CURRENT SCHEMA='SMHEALTH';
CREATE DATABASE SMHEALTH CCSID EBCDIC;

CREATE TABLE PATIENT (
   PATIENTID      INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   USERNAME       CHAR(10) NOT NULL,
   FIRSTNAME      CHAR(20),
   LASTNAME       CHAR(20),
   DATEOFBIRTH    DATE,
   INSCARDNUMBER  CHAR(10),
   ADDRESS        CHAR(20),
   CITY           CHAR(20),
   POSTCODE       CHAR(10),
   PHONEMOBILE    Char(20),
   EMAILADDRESS   Char(50),
 PRIMARY KEY(patientId))
 CCSID EBCDIC
 IN DATABASE SMHEALTH;

CREATE UNIQUE INDEX IPATIENT ON PATIENT (PATIENTID) CLUSTER COPY YES;

CREATE TABLE USER (
   PATIENTID       INTEGER NOT NULL,
   USERNAME        CHAR(10) NOT NULL,
   USERPASSWORD    CHAR(32),
 PRIMARY KEY(USERNAME),
 FOREIGN KEY(PATIENTID) 
 REFERENCES PATIENT (PATIENTID) ON DELETE CASCADE)
 CCSID EBCDIC
 IN DATABASE SMHEALTH;

CREATE UNIQUE INDEX IUSER ON USER (USERNAME) CLUSTER COPY YES;

CREATE TABLE MEDICATION (
   MEDICATIONID   INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   PATIENTID      INTEGER NOT NULL,
   DRUGNAME       CHAR(50),
   STRENGTH       CHAR(20),
   AMOUNT         SMALLINT,
   ROUTE          CHAR(20),
   FREQUENCY      CHAR(20),
   IDENTIFIER     CHAR(20),
   TYPE           CHAR(2),
 PRIMARY KEY(MEDICATIONID),
 FOREIGN KEY(PATIENTID) 
 REFERENCES PATIENT (PATIENTID) ON DELETE CASCADE)
 CCSID EBCDIC
 IN DATABASE SMHEALTH;

CREATE UNIQUE INDEX IMEDICATION ON MEDICATION (MEDICATIONID)
 CLUSTER COPY YES;
CREATE INDEX MEDICATION2 ON MEDICATION (PATIENTID) COPY YES;

CREATE TABLE APPOINTMENTS (
   PATIENTID INTEGER NOT NULL,
   FIRSTNAME CHAR(20) NOT NULL,
   LASTNAME CHAR(20) NOT NULL,
   APPT_DATE DATE NOT NULL,
   APPT_TIME CHAR(5) NOT NULL,
   DR_NAME CHAR(30),
   MED_FIELD CHAR(30),
   OFF_NAME CHAR(50),
   OFF_ADDR CHAR(40),
   OFF_CITY CHAR(20),
   OFF_STATE CHAR(2),
   OFF_ZIP CHAR(10),
   NOTES CHAR(40),
   FOLLOWUP CHAR(10))
 CCSID EBCDIC
 IN DATABASE SMHEALTH;

CREATE TABLE OBSERVATIONS (
   PATIENTID INTEGER NOT NULL,
   DATEOFOBSERVATION DATE,
   CODE CHAR(8),
   DESCRIPTION VARCHAR(75),
   NUMERICVALUE DECIMAL(10, 2),
   CHARACTERVALUE VARCHAR(30),
   UNITS VARCHAR(22))
 CCSID EBCDIC
 IN DATABASE SMHEALTH;

CREATE TABLE CONDITIONS (
   PATIENTID INTEGER NOT NULL,
   START DATE,
   STOP DATE,
   CODE CHAR(15),
   DESCRIPTION VARCHAR(75))
 CCSID EBCDIC
 IN DATABASE SMHEALTH;