Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE doctor (
- doctor_id NUMBER(4) NOT NULL,
- doctor_title VARCHAR2(2) NOT NULL,
- doctor_fname VARCHAR2(50),
- doctor_lname VARCHAR2(50),
- doctor_phone CHAR(10) NOT NULL
- );
- ALTER TABLE doctor ADD CONSTRAINT doctor_pk PRIMARY KEY ( doctor_id );
- CREATE TABLE procedure (
- proc_code NUMBER(5) NOT NULL,
- proc_name VARCHAR2(100) NOT NULL,
- proc_description VARCHAR2(300) NOT NULL,
- proc_time NUMBER(3) NOT NULL,
- proc_std_cost NUMBER(7, 2) NOT NULL
- );
- ALTER TABLE procedure ADD CONSTRAINT procedure_pk PRIMARY KEY ( proc_code );
- ALTER TABLE procedure ADD CONSTRAINT proc_name_unq UNIQUE ( proc_name );
- CREATE TABLE admission (
- adm_no NUMBER(6) NOT NULL,
- adm_date_time DATE NOT NULL,
- adm_discharge DATE,
- patient_id NUMBER(6) NOT NULL,
- doctor_id NUMBER(4) NOT NULL
- );
- ALTER TABLE admission ADD CONSTRAINT admission_pk PRIMARY KEY ( adm_no ); --surrogate key
- ALTER TABLE admission ADD CONSTRAINT admission_nk UNIQUE ( patient_id,
- adm_date_time );
- CREATE TABLE adm_prc (
- adprc_no NUMBER(7) NOT NULL,
- adprc_date_time DATE NOT NULL,
- adprc_pat_cost NUMBER(7, 2) NOT NULL,
- adprc_items_cost NUMBER(6, 2) NOT NULL,
- adm_no NUMBER(6) NOT NULL,
- proc_code NUMBER(5) NOT NULL,
- request_dr_id NUMBER(4) NOT NULL,
- perform_dr_id NUMBER(4)
- );
- ALTER TABLE adm_prc ADD CONSTRAINT adm_prc_pk PRIMARY KEY ( adprc_no ); --surrogate key
- ALTER TABLE adm_prc ADD CONSTRAINT adm_prc_nk UNIQUE ( adprc_date_time,
- adm_no );
- ALTER TABLE adm_prc
- ADD CONSTRAINT admission_admprc FOREIGN KEY ( adm_no )
- REFERENCES admission ( adm_no );
- ALTER TABLE adm_prc
- ADD CONSTRAINT doctor_performadmprc FOREIGN KEY ( perform_dr_id )
- REFERENCES doctor ( doctor_id );
- ALTER TABLE adm_prc
- ADD CONSTRAINT doctor_requestadmprc FOREIGN KEY ( request_dr_id )
- REFERENCES doctor ( doctor_id );
- CREATE TABLE item_treatment (
- adprc_no NUMBER(7) NOT NULL,
- item_code CHAR(5) NOT NULL,
- it_qty_used NUMBER(2) NOT NULL,
- it_item_total_cost NUMBER(8, 2) NOT NULL
- );
- ALTER TABLE item_treatment
- ADD CONSTRAINT item_treatment_pk PRIMARY KEY ( adprc_no,item_code);
- ALTER TABLE item_treatment
- ADD CONSTRAINT admprc_itemtreatment FOREIGN KEY ( adprc_no )
- REFERENCES adm_prc ( adprc_no );
- ALTER TABLE item_treatment
- ADD CONSTRAINT admprc_itemtreatment FOREIGN KEY ( adprc_no )
- REFERENCES adm_prc ( adprc_no );
- ALTER TABLE item_treatment
- ADD CONSTRAINT item_itemtreatment FOREIGN KEY ( item_code )
- REFERENCES item ( item_code );
- ALTER TABLE ADM_PRC ADD ANCILLARY_DR_ID NUMBER(4);
- ALTER TABLE ADM_PRC
- ADD CONSTRAINT DOCTOR_PERFORM_ANCILLARY FOREIGN KEY ( ANCILLARY_DR_ID )
- REFERENCES DOCTOR ( DOCTOR_ID );
- adprc_no adprc_date_time adprc_pat_cost adprc_items_cost adm_no proc_code
- request_dr_id perform_dr_id ancillary_dr_id
- -----------------------------------------------------------------------------------------------------------------------------
- 1 14/03/2019 100 100 1234 1234
- 10 10 12
- 1 14/03/2019 100 100 1234 1234
- 10 10 13
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement