ITECH 5006 SEM 2 2004 - Assignment 2 - Database Implementation and Queries - Zen Chiropractic Clinic

ZEN-ass2-schm1509.sql

-- -- ITECH1006 Assignment 2 Zen Chiropractic Clinic Oracle Schema -- -- Author: ITECH1006 -- Date: September 2014 -- CREATE DATABASE IF NOT EXISTS ZEN_CC; USE ZEN_CC; -- -- Place DROP commands at head of schema file -- DROP TABLE IF EXISTS INSURANCE; DROP TABLE IF EXISTS PATIENT; DROP TABLE IF EXISTS CLASSIFICATION; DROP TABLE IF EXISTS PRODUCT; DROP TABLE IF EXISTS SERVICE; DROP TABLE IF EXISTS CONSULTATION; DROP TABLE IF EXISTS SERVICE_INSURANCE; DROP TABLE IF EXISTS CONSULTATION_SERVICE; DROP TABLE IF EXISTS CONSULTATION_PRODUCT; DROP TABLE IF EXISTS INSURANCE_REBATE; DROP TABLE IF EXISTS INSURANCE_COVER; DROP TABLE IF EXISTS CONSULTATION_ARCHIVE; DROP TABLE IF EXISTS CONSULTATION_PRODUCT_ARCHIVE; DROP TABLE IF EXISTS CONSULTATION_SERVICE_ARCHIVE; DROP TABLE IF EXISTS PRODUCT_INSURANCE; DROP VIEW IF EXISTS DISCOUNTPATIENT; -- Create INSURANCE table ------ CREATE TABLE IF NOT EXISTS INSURANCE ( InsuranceCode CHAR(5) NOT NULL , InsuranceName VARCHAR(45) , CONSTRAINT pk_insurance PRIMARY KEY(InsuranceCode))ENGINE=innodb DEFAULT CHARSET=utf8; -- insert into INSURANCE table INSERT INTO INSURANCE VALUES ('HI001', 'OZ Public'); INSERT INTO INSURANCE VALUES ('HI002', 'Medibank Private'); INSERT INTO INSURANCE VALUES ('HI003', 'HBA Private'); INSERT INTO INSURANCE VALUES ('HI004', 'NIB Private'); INSERT INTO INSURANCE VALUES ('HI005', 'NRMA Private'); -- Create PATIENT table ------ CREATE TABLE IF NOT EXISTS PATIENT ( PatientNum INT PRIMARY KEY AUTO_INCREMENT, PatientSurname VARCHAR(30) NOT NULL , PatientFirstname VARCHAR(30) NOT NULL , PatientStreet VARCHAR(30) NOT NULL , PatientCity VARCHAR(30) NOT NULL , PatientState CHAR(3) NOT NULL , PatientPostcode CHAR(4) NOT NULL , PatientContact CHAR(12) NOT NULL , CONSTRAINT patient_PatientState_CK CHECK ( PatientState IN ('VIC', 'NT', 'NSW', 'WA', 'QLD', 'SA', 'TAS', 'ACT')) )ENGINE=innodb DEFAULT CHARSET=utf8; -- insert into PATIENT table INSERT INTO PATIENT VALUES (NULL, 'Olivier', 'Jamie', '2 B Ct', 'Morwell', 'VIC', '3840', '123456789012'); INSERT INTO PATIENT VALUES (NULL, 'Pythagoras', 'Penny', '3 C Ct', 'Morwell', 'VIC', '3840', '040033442000'); INSERT INTO PATIENT VALUES (NULL, 'Williams','R.', '10 D Ct', 'Morwell', 'VIC', '3840', '040033442000'); INSERT INTO PATIENT VALUES (NULL, 'Jump','J.', '4 D Pl', 'Sale', 'QLD', '4006', '040033442123'); INSERT INTO PATIENT VALUES (NULL, 'Webber','Mark', '20 W Rd', 'Churchill', 'VIC', '3842', '040011112000'); INSERT INTO PATIENT VALUES (NULL, 'Schumacher', 'Mickey','1 A Dr', 'Moe', 'NSW', '2000', '040033442111'); INSERT INTO PATIENT VALUES (NULL, 'Schumacher', 'Ralfy', '1 A Dr', 'Moe', 'NSW', '2000', '040033442222'); INSERT INTO PATIENT VALUES (NULL, 'Button', 'Jenson', '33 H Dr', 'Berwick', 'NSW', '2000', '040055552111'); INSERT INTO PATIENT VALUES (NULL, 'Uno', 'Butt', '33 H Dr', 'North Melbourne', 'VIC', '3800', '040055552333'); INSERT INTO PATIENT VALUES (NULL, 'Hide', 'Nakata', '12 H Dr', 'North Melbourne', 'VIC', '3800', '040055552444'); INSERT INTO PATIENT VALUES (NULL, 'Butt', 'Nicky', '104 H Cr', 'North Melbourne', 'VIC', '3800', '040055552555'); -- Create CLASSIFICATION table ------ CREATE TABLE IF NOT EXISTS CLASSIFICATION ( ClassificationNum INT PRIMARY KEY AUTO_INCREMENT, ClassificationDescription VARCHAR(50) NOT NULL )ENGINE=innodb DEFAULT CHARSET=utf8; -- insert into CLASSIFICATION table INSERT INTO CLASSIFICATION VALUES (NULL, 'Basic'); INSERT INTO CLASSIFICATION VALUES (NULL, 'Intermediate'); INSERT INTO CLASSIFICATION VALUES (NULL, 'Advance'); -- Create PRODUCT table ------ CREATE TABLE IF NOT EXISTS PRODUCT ( ProductCode CHAR(4) NOT NULL , ProductName VARCHAR(60) NOT NULL , ProductUnitPrice DECIMAL(3) NOT NULL , StockInHand DECIMAL(4) NOT NULL , CONSTRAINT pk_key PRIMARY KEY(ProductCode))ENGINE=innodb DEFAULT CHARSET=utf8; -- insert into PRODUCT table INSERT INTO PRODUCT VALUES ('P001', 'Nature Back Support - 100 tablets', 25, 1000); INSERT INTO PRODUCT VALUES ('P002', 'Nature Back Support - 200 tablets', 45, 1200); INSERT INTO PRODUCT VALUES ('P003', 'Organic Relax Massage Oil - 100 mls', 12, 400); INSERT INTO PRODUCT VALUES ('P004', 'Organic Relax Massage Oil - 200 mls', 20, 800); INSERT INTO PRODUCT VALUES ('P005', 'Nature Wild Berry Herbal Tea - 30 bags', 5, 2000); INSERT INTO PRODUCT VALUES ('P006', 'Nature Strawberry Herbal Tea - 30 bags', 8, 2000); INSERT INTO PRODUCT VALUES ('P007', 'OzBee Royal Jelly - 100 tablets', 50, 1000); INSERT INTO PRODUCT VALUES ('P008', 'OzBee Royal Jelly - 200 tablets', 90, 3000); INSERT INTO PRODUCT VALUES ('P009', 'MaxNature Liquid Diatomic Oxygen Supplement - 250 mls', 60, 3000); INSERT INTO PRODUCT VALUES ('P010', 'MaxNature Liquid Diatomic Oxygen Supplement - 500 mls', 115, 3000); INSERT INTO PRODUCT VALUES ('P011', 'MaxNaturePro Liquid Diatomic Oxygen Supplement - 500 mls', 200, 3000); -- Create SERVICE table ------ CREATE TABLE IF NOT EXISTS SERVICE ( ServiceCode CHAR(4) NOT NULL , ServiceName VARCHAR(30) NOT NULL , ServiceUnitCost DECIMAL(6,2) NOT NULL , ClassificationNum INT NOT NULL , CONSTRAINT pk_service PRIMARY KEY(ServiceCode), CONSTRAINT fk_key_classification FOREIGN KEY(ClassificationNum) REFERENCES CLASSIFICATION(ClassificationNum))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create CONSULTATION table ------ CREATE TABLE IF NOT EXISTS CONSULTATION ( ConsultationNum INT PRIMARY KEY AUTO_INCREMENT, PatientNum INT NOT NULL , ConsultationDate DATE NOT NULL , ScheduledStartTime TIME NOT NULL , ActualStartTime TIME, ActualEndTime TIME, CONSTRAINT fk_cosul_patient FOREIGN KEY(PatientNum) REFERENCES PATIENT(PatientNum))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create SERVICE_INSURANCE table ------ CREATE TABLE IF NOT EXISTS SERVICE_INSURANCE ( InsuranceCode CHAR(5) NOT NULL , ServiceCode CHAR(4) NOT NULL , CONSTRAINT pk_serv_ins PRIMARY KEY(InsuranceCode, ServiceCode), CONSTRAINT fk_serv_ins_service FOREIGN KEY(ServiceCode) REFERENCES SERVICE(ServiceCode), CONSTRAINT fk_serv_ins_insurance FOREIGN KEY(InsuranceCode) REFERENCES INSURANCE(InsuranceCode))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create CONSULTATION_SERVICE table ------ CREATE TABLE IF NOT EXISTS CONSULTATION_SERVICE ( ConsultationNum INT NOT NULL , ServiceCode CHAR(4) NOT NULL , ServiceDiagnosisDesc VARCHAR(60) , CONSTRAINT pk_consul_serv PRIMARY KEY(ConsultationNum, ServiceCode), CONSTRAINT fk_consul_serv_consul FOREIGN KEY(ConsultationNum) REFERENCES CONSULTATION(ConsultationNum), CONSTRAINT fk_consul_serv_ser FOREIGN KEY(ServiceCode) REFERENCES SERVICE(ServiceCode))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create CONSULTATION_PRODUCT table ------ CREATE TABLE IF NOT EXISTS CONSULTATION_PRODUCT ( ProductCode CHAR(4) NOT NULL , ConsultationNum INT NOT NULL , Quantity SMALLINT NOT NULL , ProductDiagnosisDesc VARCHAR(60) , CONSTRAINT pk_consul_prod PRIMARY KEY(ProductCode, ConsultationNum), CONSTRAINT fk_consul_prod_consul FOREIGN KEY(ConsultationNum) REFERENCES CONSULTATION(ConsultationNum), CONSTRAINT fk_consul_prod_prod FOREIGN KEY(ProductCode) REFERENCES PRODUCT(ProductCode))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create INSURANCE_REBATE table ------ CREATE TABLE IF NOT EXISTS INSURANCE_REBATE ( ClassificationNum INT NOT NULL , InsuranceCode CHAR(5) NOT NULL , RatePercent DECIMAL(3) NOT NULL , CONSTRAINT pk_ins_reb PRIMARY KEY(ClassificationNum, InsuranceCode), CONSTRAINT fk_ins_reb_ins FOREIGN KEY(InsuranceCode) REFERENCES INSURANCE(InsuranceCode), CONSTRAINT fk_ins_reb_class FOREIGN KEY(ClassificationNum) REFERENCES CLASSIFICATION(ClassificationNum))ENGINE=innodb DEFAULT CHARSET=utf8; -- Create INSURANCE_COVER table ------ CREATE TABLE IF NOT EXISTS INSURANCE_COVER ( InsuranceCode CHAR(5) NOT NULL , PatientNum INT NOT NULL , CONSTRAINT pk_ins_cover PRIMARY KEY(InsuranceCode, PatientNum), CONSTRAINT fk_ins_cover_pat FOREIGN KEY(PatientNum) REFERENCES PATIENT(PatientNum), CONSTRAINT fk_ins_cover_ins FOREIGN KEY(InsuranceCode) REFERENCES INSURANCE(InsuranceCode))ENGINE=innodb DEFAULT CHARSET=utf8;