[DB] 병원 관리 프로그램(2)

 

2021.10.20.-2021.12.14.

​

병원에 수많은 데이터가 존재하여 하나하나 확인하기에는 어려움이 있어 병원 데이터베이스를 통해 각종 의사 및 간호사의 진료과를 손쉽게 검색할 수 있다.

​

환자의 입원 정보 뿐만 아니라 환자가 의사에게 받은 진료 내용을 조회하여

한눈에 원하는 데이터를 검색하여 번거로움을 줄일 수 있다.

​

또한 환자가 예약을 하고, 예약 내역을 빠르게 확인할 수 있도록 한다.

​

관계자는 환자의 진료기록 및 예약 기록을 관리할 수 있도록 하고,

관리자는 관계자 및 환자의 데이터를 관리할 수 있도록 한다.

​

​

​

​

기존의 병원 관리 프로그램에 트리거 및 저장 프로시저 등을 추가하여 활용하였다.

​

 

---

요구사항 명세서는 다음과 같다.

​

1) 의사는 고유의 의사번호, 진료과목, 이름, 전화번호, 주소, 비밀번호를 가진다.

2) 간호사는 고유의 간호사번호, 직급, 이름, 전화번호, 주소, 진료과목, 과번호, 비밀번호를 가 진다.

3) 환자로 등록하려면 고유의 환자번호, 이름, 주민등록번호, 전화번호, 주소, 신장(키), 몸무게, 혈액형, 성별, 진료내용, 비밀번호 입력을 해야한다.

4) 입원은 고유의 입원일, 퇴원일, 입원실 정보를 갖는다.

5) 진료과는 고유의 과번호, 과이름, 전화번호 정보를 갖는다.

6) 의사는 여러 명의 환자를 진찰할 수 있고, 환자도 여러 명의 의사에게 진료받을 수 있다.

7) 진료를 받으면 진료번호, 진료일이 저장된다.

8) 간호사는 여러 명의 환자를 처치할 수 있으며 이때 처치일, 처치내용이 저장된다.

9) 진료내용은 다중값을 가질 수 있다. (ex. 약1은 해열제, 약2는 진통제..etc)

10) 의사와 간호사는 하나의 전공(진료과목)을 가진다.

11) 환자는 입원할 수 있으며 입원은 환자가 존재할 때에만 있을 수 있는 약성개체이다.

12) 환자는 여러 명 입원할 수 있다.

13) 환자는 예약할 수 있으며 예약은 환자가 존재할 때에만 있을 수 있는 약성개체이다.

14) 예약은 고유의 예약일, 예약번호, 예약내용이 있다.

15) 관리자는 고유의 ID와 비밀번호를 가진다.

16) 하나의 관리자는 여러 명의 의사, 간호사, 환자를 관리할 수 있다.

17) 3년이상 진료내역 갱신이 없는 환자는 휴면으로 전환되고, 휴면번호가 생성된다.

​

​

​

---

 

E-R 다이어그램은 다음과 같이 변경되었다.

​

ERD

 

 

​

​

---

변경된 스키마는 다음과 같다.

​

​

​

진료과 릴레이션 ( 과번호, 과이름, 전화번호 )

 

진료 릴레이션 ( 진료번호, 의사번호, 환자번호, 진료일 )

 

의사 릴레이션 ( 의사번호, 이름, 전화번호, 주소, 과번호, 비밀번호)

 

간호사 릴레이션 ( 간호사번호, 이름, 전화번호, 주소, 과번호,비밀번호 )

 

입원 릴레이션 ( 환자번호, 입원일, 퇴원일, 입원실 )

 

환자 릴레이션 (환자번호, 이름, 주소, 전화번호, 주민등록번호, 신장(키), 몸무게, 혈액형, 성별, 간호사번호, 처치일, 처치내용, 비밀번호)

 

환자-진료내용 릴레이션 (환자번호, 진료내용)

 

예약 릴레이션 (환자번호, 예약번호, 예약일, 예약내용, 담당의사)

 

관리자 릴레이션 (ID,비밀번호)

 

계정전환 릴레이션(휴면번호, 환자번호,이름, 주소, 전화번호, 주민등록번호, 신장(키), 몸무게, 혈액형, 성별, 비밀번호)

​

​

---

이 프로그램의 기능은 다음과 같다.

​

저장 프로시저를 이용하여 환자의 진료내용에서 특정 질병에 대한 환자들의 평균연령을 구하여 출력하는 기능 수행 (예, "고혈압" 진료내용을 가진 환자들의 평균 연령을 계산하여 질병내용 : 고혈압 , 평균연령 : 43.2세 처럼 출력)

​

1) 처치일이 3년 이상 갱신되지 않으면 그 환자를 휴면테이블 데이터에 삽입하는 기능 수행 (예, 환자 테이블의 처치일이 현재로부터 3년 이상 갱신되지 않은 경우, 휴면테이블에 환자테이블의 스키마가 삽입됨.)

​

2) 진료테이블이 갱신(삽입, 삭제, 변경)될 때마다 그 갱신내용을 환자-진료내용 테이블에 반영하는 기능 수행 (예, 진료 릴레이션에 새로운 진료번호가 추가될 경우, 환자-진료내용에 환자번호와 진료내용이 바로 갱신됨.)

​

 

---

​

ALTER session set "_ORACLE_SCRIPT"=true;
DROP USER HOSPITAL CASCADE; -- 기존 사용자 삭제(현재 접속되어 있으면 삭제 안 됨)
	-- CASCADE option : 관련 스키마 개체들도 함께 삭제.  Default는 No Action
CREATE USER HOSPITAL IDENTIFIED BY 1234  -- 사용자 ID : hmart, 비밀번호 : 1234
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP;
GRANT connect, resource, dba TO HOSPITAL;-- 권한 부여

 

 

각각의 테이블을 생성한다.

 

create table 진료과(
    과번호 INT NOT NULL,
    과이름 VARCHAR(20) NOT NULL,
    전화번호 CHAR(20),
    PRIMARY KEY(과번호)
);

create table 의사(
    의사번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    과번호 INT,
    비밀번호 varchar(40),
    PRIMARY KEY(의사번호),
    FOREIGN KEY(과번호) REFERENCES 진료과(과번호) 
);

create table 간호사(
    간호사번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    과번호 INT,
    비밀번호 varchar(40),
    PRIMARY KEY (간호사번호),
    FOREIGN KEY(과번호) REFERENCES 진료과(과번호)
);

create table 환자(
    환자번호 INT NOT NULL,
    이름 CHAR(10) NOT NULL,
    전화번호 CHAR(20),
    주소 CHAR(10),
    주민등록번호 CHAR(20),
    신장 NUMBER(5,1),
    몸무게 NUMBER(5,1),
    혈액형 VARCHAR(4),
    성별 CHAR(6),
    처치일 DATE,
    처치내용 VARCHAR(20),
    간호사번호 INT,
    비밀번호 varchar(40),
    PRIMARY KEY (환자번호),
    FOREIGN KEY(간호사번호) REFERENCES 간호사(간호사번호),
    CHECK (혈액형 IN ('A','B','O','AB'))
);

create table 진료(
    진료번호 INT NOT NULL,
    진료일 DATE,
    의사번호 INT,
    환자번호 INT, 
    진료내용 VARCHAR2(20),
    PRIMARY KEY(진료번호, 의사번호, 환자번호),
    FOREIGN KEY(의사번호) REFERENCES 의사(의사번호) ON DELETE CASCADE,
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호) ON DELETE CASCADE
);

create table 입원(
    입원일 DATE NOT NULL,
    퇴원일 DATE,
    입원실 INT,
    환자번호 INT,
    PRIMARY KEY(환자번호, 입원일),
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호) ON DELETE CASCADE
);

create table 진료내용(
진료내용 VARCHAR(20) NOT NULL,
환자번호 INT,
PRIMARY KEY(진료내용, 환자번호),
FOREIGN KEY(환자번호) REFERENCES 환자(환자번호) ON DELETE CASCADE
);

create table 관리자(
    아이디 varchar(20),
    비밀번호 varchar(40),
    primary key(아이디)
);

create table 예약(
    환자번호 INT,
    예약번호 INT,
    예약일 DATE,
    예약내용 varchar(20),
    의사번호 int,
    primary key(환자번호,예약번호),
    FOREIGN KEY(의사번호) REFERENCES 의사(의사번호) ON DELETE CASCADE,
    FOREIGN KEY(환자번호) REFERENCES 환자(환자번호) ON DELETE CASCADE
);

create table 휴면계정(
    환자번호 INT NOT NULL,
  이름 CHAR(10) NOT NULL,
 전화번호 CHAR(20),
  주소 CHAR(10),
  주민등록번호 CHAR(20),
신장 NUMBER(5,1),
  몸무게 NUMBER(5,1),
    혈액형 VARCHAR(4),
  성별 CHAR(6),
  처치일 DATE,
  처치내용 VARCHAR(20),
간호사번호 INT,
  비밀번호 varchar(40),
  CHECK (혈액형 IN ('A','B','O','AB')),
   휴면번호 INT NOT NULL,
  PRIMARY KEY (휴면번호)
);

 

 

 

 

계정 로그인을 위하여 비밀번호를 추가하였고,

각각의 데이터를 추가한다.

 

Insert INTO 관리자(아이디, 비밀번호) values ('admin','admin');

예약번호를 위한 sequence를 생성해준다. 1부터 시작하며, 1씩 증가된다.

CREATE SEQUENCE 예약번호 --시퀀스이름 EX_SEQ
INCREMENT BY 1 --증감숫자 1
START WITH 1;

 

 

 

---

사용된 저장 프로시저는 다음과 같다.

--주민번호로 나이구하는 열 추가하기--


alter table 환자 add 나이 NUMBER;

create or replace PROCEDURE SPC_AGE AS

    V_환자번호 NUMBER;
    V_나이 NUMBER :=0;
    V_주민등록번호 CHAR(20);
    
    CURSOR C IS select 환자번호, 나이, 주민등록번호 from 환자;
    BEGIN
        open c;
        Loop
            Fetch c into V_환자번호, V_나이, V_주민등록번호;
            EXIT when c%NOTFOUND;
            SELECT 주민등록번호 into V_주민등록번호 FROM 환자 where 환자번호=V_환자번호;
            IF SUBSTR(V_주민등록번호,8,1)='1' or SUBSTR(V_주민등록번호,8,1)='2' THEN
            BEGIN
                V_나이:=EXTRACT(YEAR FROM SYSDATE)-TO_NUMBER('19' || SUBSTR(V_주민등록번호,1,2)) +1;
                update 환자 set 나이=V_나이 Where 환자번호=V_환자번호;
            END;
            ELSE 
            BEGIN
                V_나이:=EXTRACT(YEAR FROM SYSDATE)-TO_NUMBER('20' || SUBSTR(V_주민등록번호,1,2)) +1;
                update 환자 set 나이=V_나이 Where 환자번호=V_환자번호;
            END;
        END IF;
    END LOOP;
    CLOSE C;
END;

SET SERVEROUTPUT ON;
EXECUTE SPC_AGE();

SPC_AGE 프로시저는 ‘환자’ 테이블의 주민등록번호를 이용하여 환자의 나이를 계산하는 프로시저이다.

​

alter table 환자 add 나이 NUMBER;를 이용하여 환자 테이블에 새로운 나이 컬럼을 추가하여

주민등록번호 8번째가 1 또는 2이면 2000년 이전이므로

주민등록번호 앞 두자리인 연도에 19를 붙여 나이를 현재 2021년도에서 뺀 후 +1을 더하여 구한다.

​

ELSE는 8번째가 3 또는 4인 2000년 이후를 말하므로 주민등록번호 앞 두자리를 20과 합쳐 현재 년도에서 나이를 구하는 프로시저이다.

​

CREATE OR REPLACE NONEDITIONABLE PROCEDURE sp_평균(AVGs out SYS_REFCURSOR) AS
BEGIN
        open AVGs for
            SELECT 진료내용, AVG(환자.나이) as 평균나이 FROM 환자, 진료내용 WEHRE 진료내용.환자번호=환자.환자번호 GROUP BY 진료내용;
END;

SP_평균 프로시저는 전체의 진료내용에 대한 환자들의 평균 나이를 구하는 기능을 수행한다.

​

cursor을 통하여 여러 행을 리턴해주며, JDBC에서 callablestatement를 통하여 통계를 출력할 때 사용한다.

​

group by를 이용하여 진료내용을 그룹으로 진료내용과 환자의 평균나이를 구하는 프로시저다.

 

 

---

사용된 트리거는 다음과 같다.

 

create or replace NONEDITIONABLE trigger T_treat
    after insert or delete or update
    on 진료
    for each row
BEGIN

    if inserting then
        insert into 진료내용(진료내용, 환자번호) values(:NEW.진료내용, :NEW.환자번호);

    elsif deleting then
        delete from 진료내용 where 진료내용.환자번호=:OLD.환자번호;

    elsif updating then 
        update 진료내용 set 진료내용.환자번호=:NEW.환자번호 where 진료내용.환자번호=:OLD.환자번호;
        update 진료내용 set 진료내용.진료내용=:NEW.진료내용 where 진료내용.환자번호=:OLD.환자번호;
    END IF;
END T_treat;


insert into 진료(진료번호, 진료일, 의사번호, 환자번호, 진료내용) values (700,'2021-12-02',21, 100, '뇌졸증');
delete from 진료 where 환자번호=100 and 진료내용='뇌졸증' and 진료번호=700;

   

​T_treat 트리거는 진료테이블이 갱신될 때마다 그 갱신내용을 진료내용 테이블에 반영하는

기능을 수행하는 트리거이다.

 

--휴면계정 만드는 트리거--

create sequence 휴면seq;

create or replace trigger T_휴면계정
    after delete on 환자
    for each row
    
begin
    insert into 휴면계정 values(:OLD.환자번호, :OLD.이름, :OLD.전화번호, :OLD.주소, :OLD.주민등록번호, :OLD.신장, :OLD.몸무게, :OLD.혈액형,:OLD.성별, :OLD.처치일, :OLD.처치내용, :OLD.간호사번호, :OLD.비밀번호, 휴면seq.nextval);
end T_휴면계정;



--3년 이후로 진료일 갱신 안된 환자는 휴면계정으로 전환
DELETE FROM 환자
where exists (select 진료.환자번호
              from 진료
              where 환자.환자번호=진료.진료번호 and 진료.진료일 < ADD_MONTHS(SYSDATE, -36));

T_휴면계정 트리거는 처치일이 3년 이상 갱신되지 않으면

그 환자를 휴면테이블 데이터에 삽입하는 기능을 수행한다.

​

위의 휴면계정 테이블은 아직 delete문을 실행하기 전이며,

DELETE FROM 환자 where 환자.처치일 < ADD_MONTHS(SYSDATE, -36)을 실행하면,

처치일이이 3년이상 갱신되지 않은 환자를 휴면계정으로 전환하는 트리거이다.

​

create sequence 휴면seq;를 실행하여 미리 휴면 계정의 시퀀스를 만들어둔다.

2023.01.02 - [프로젝트/DB] - [DB] 병원 관리 프로그램 (1)

[DB] 병원 관리 프로그램 (1)